1The Relational ModelCS 186, Spring 2006, Lecture 2R & G, Chap. 1 & 3Administrivia I• CS 186 IS MOVING!!!!• Starting TUES 1/24 (next week) we will be in 105 NORTHGATE• CS 186 IS MOVING!!!!• Starting TUES 1/24 (next week) we will be in 105 NORTHGATEAdministrivia II• Recall: Discussion Sections– W11-12 70 Evans– W 2-3 70 Evans– W 3-4 241 Cory• Section on Tuesdays is Cancelled. • Still working on approval for 3rd TA.• Web site is getting there.• Details on Projects, Grading, TA office hours, etc. available by Tuesday.• I *will* be holidng office hours today as scheduled: 1-2pm 687 Soda HallAdministrivia III - Don’t Forget• CS 186 IS MOVING!!!!• Starting TUES 1/24 (next week) we will be in 105 NORTHGATEData Models• A Database models some portion of the real world.•Data Modelis link between user’s view of the world and bits stored in computer.• Many models have been proposed.• We will concentrate on the Relational Model.1010111101Student (sid: string, name: string, login: string, age: integer, gpa:real)Describing Data: Data Models• A data modelis a collection of concepts for describing data.• A database schemais a description of a particular collection of data, using a given data model.• The relational model of datais the most widely used model today.– Main concept: relation, basically a table with rows and columns.–Every relation has a schema, which describes the columns, or fields.2Levels of Abstraction•Viewsdescribe how users see the data. • Conceptual schema defines logical structure• Physical schema describes the files and indexes used.• (sometimes called the ANSI/SPARC model)Physical SchemaConceptual SchemaView 1 View 2 View 3DBUsersData Independence:The Big Breakthrough of the Relational Model• A Simple Idea: Applications should be insulated from how data is structured and stored.Physical SchemaConceptual SchemaView 1 View 2 View 3DB• Q: Why are these particularly important for DBMS? • Physical data independence: Protection from changes in physicalstructure of data.• Logical data independence: Protection from changes in logical structure of data.Why Study the Relational Model? • Most widely used model currently.– DB2, MySQL, Oracle, PostgreSQL, SQLServer, …– Note: some “Legacy systems” use older models • e.g., IBM’s IMS• Object-oriented concepts have recently merged in–object-relational model•Informix, IBM DB2, Oracle 8i•Early work done in POSTGRES research project at Berkeley• XML (semi-structured)models emerging?Relational Database: Definitions•Relational database:a set of relations.•Relation:made up of 2 parts:–Schema : specifies name of relation, plus name and type of each column. •E.g. Students(sid: string, name: string, login: string, age: integer, gpa: real) –Instance: a table, with rows and columns. •#rows = cardinality•#fields = degree / arity• Can think of a relation as a setof rows or tuples. – i.e., all rows are distinctExample: University Database• Conceptual schema: –Students(sid: string,name: string, login: string, age: integer, gpa:real)–Courses(cid: string, cname:string, credits:integer) –Enrolled(sid:string, cid:string, grade:string)• External Schema (View): –Course_info(cid:string,enrollment:integer)• One possible Physical schema :– Relations stored as unordered files. – Index on first column of Students.Physical SchemaConceptual SchemaView 1 View 2 View 3DBEx: An Instance of Students Relationsid name login age gpa 53666 Jones jones@cs 18 3.4 53688 Smith smith@eecs 18 3.2 53650 Smith smith@math 19 3.8 Cardinality = 3, Arity = 5 All rows must be unique (set semantics)•Q: Do all values in each column of a relation instance have to be Unique?• Q: Is “Cardinality” a schema property?• Q: Is “Arity” a schema property?3SQL - A language for Relational DBs• SQL (a.k.a. “Sequel”), – “Intergalactic Standard for Data”– Stands for Structured Query LanguageTwo sub-languages:• Data DefinitionLanguage (DDL)– create, modify, delete relations– specify constraints– administer users, security, etc.• Data ManipulationLanguage (DML)–Specify queries to find tuples that satisfy criteria– add, modify, remove tuplesSQL Overview• CREATE TABLE <name> ( <field> <domain>, … )• INSERT INTO <name> (<field names>)VALUES (<field values>)• DELETE FROM <name> WHERE <condition>• UPDATE <name> SET <field name> = <value>WHERE <condition>• SELECT <fields> FROM <name>WHERE <condition>Creating Relations in SQL• Creates the Students relation.–Note: the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified. CREATE TABLE Students(sid CHAR(20), name CHAR(20), login CHAR(10),age INTEGER,gpa FLOAT) Table Creation (continued)• Another example: the Enrolled table holds information about courses students take.CREATE TABLE Enrolled(sid CHAR(20), cid CHAR(20), grade CHAR(2))Adding and Deleting Tuples• Can insert a single tuple using:INSERT INTO Students (sid, name, login, age, gpa)VALUES (‘53688’, ‘Smith’, ‘smith@ee’, 18, 3.2)• Can delete all tuples satisfying some condition (e.g., name = Smith):DELETE FROM Students SWHERE S.name = ‘Smith’Powerful variants of these commands are available; more later!Keys• Keys are a way to associate tuples in different relations• Keys are one form of integrity constraint (IC)sid name login age gpa53666 Jones jones@cs 18 3.453688 Smith smith@eecs 18 3.253650 Smith smith@math 19 3.8sid cid grade53666 Carnatic101 C53666 Reggae203 B53650 Topology112 A53666 History105 BEnrolledStudentsPRIMARY KeyFORIEGN Key4Primary Keys• A set of fields is a superkeyif:– No two distinct tuples can have same values in all key fields• A set of fields is a candidate keyfor a relation if :–It is a superkey– No subset of the fields is a superkey• what if >1 key for a relation?– one of the candidate keys is chosen (by DBA) to be the primary key. E.g.–sidis a key for Students. – What about name?–The set {sid, gpa} is a superkey.Primary and Candidate Keys in SQL• Possibly many candidate keys(specified using UNIQUE), one of which is chosen as the primary key.• Keys must be used carefully!• “For a given student and course, there is a single grade.”“Students can take only one course, and no two students in a course receive the same grade.”CREATE TABLE
View Full Document
Unlocking...