4 6 11 Data and Queries in the Relational Model CS 162 Guest Lecture Mike Franklin April 6 2011 A relationship I think is like a shark you know It has to constantly move forward or it dies And I think what we got on our hands is a dead shark Woody Allen from Annie Hall 1979 Steps in Database Design Requirements Analysis user needs what must the database capture Conceptual Design high level description often done w ER model Logical Design translate ER into DBMS data model Data Models Describing Data A Database design encodes some portion of the real world A Data Model is a set of concepts for thinking about this encoding Many models have been proposed Student sid string name string login string age integer gpa real We will look at two related models i Entity Relationship graphical ii Relational implementation 10101 11101 Conceptual Design using ER What are the entities and relationships What info about E s R s should be in DB What integrity constraints business rules hold ER diagram is a representation of the schema Can map an ER diagram into a relational schema Typically relational model as implemented by SQL Schema Refinement consistency normalization Physical Design indexes disk layout Conceptual design is where the SW data engineering begins Rails models Security Design who accesses what and how 1 4 6 11 name ssn since lot dname did budget ER Example Employees An employee can work in many departments a dept can have many employees In contrast each dept has at most one manager according to the key constraint on Manages Participation Constraints Does every employee work in a department If so a participation constraint participation of Employees in Works In is total vs partial What if every department has an employee working in it Basically means at least one since name ssn Employees dname did lot Manages Works In since budget Departments Manages Departments Works In since Many to 1 to Many Many Many to 1 1 to 1 Implementation The Relational Model The E R model is not directly implemented by most DBMSs Fairly easy to map an E R design to a Relational Schema The Relational Model is Ubiquitous MySQL PostgreSQL Oracle DB2 SQLServer Note some Legacy systems use older models e g IBM s IMS Object oriented concepts have been merged in Early work POSTGRES research project at Berkeley Informix IBM DB2 Oracle 8i As has support for XML semi structured data 2 4 6 11 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 Some Synonyms Formal Not so formal 1 Relation Table Not so formal 2 Tuple Row Record Attribute Column Field Domain Type Students sid string name string login string age integer gpa real Instance the actual data at a given time rows cardinality fields degree arity Ex Instance of Students Relation SQL A language for Relational DBs Say ess cue ell or sequel sid 53666 53688 53650 name login Jones jones cs Smith smith eecs Smith smith math age gpa 18 3 4 18 3 2 19 3 8 Cardinality 3 arity 5 all rows distinct Do all values in each column of a relation instance have to be distinct But spelled SQL Data Definition Language DDL create modify delete relations specify constraints administer users security etc Data Manipulation Language DML Specify queries to find tuples that satisfy criteria add modify remove tuples 3 4 6 11 Creating Relations in SQL Table Creation continued Create the Students relation Another example the Enrolled table holds information about courses students take CREATE TABLE Students sid CHAR 20 name CHAR 20 login CHAR 10 age INTEGER gpa FLOAT CREATE TABLE Enrolled sid CHAR 20 cid CHAR 20 grade CHAR 2 Constraints Keys Primary and Candidate Keys in SQL Keys are a way to associate tuples in different relations Keys are one form of integrity constraint IC Enrolled sid 53666 53666 53650 53666 cid grade Carnatic101 C Reggae203 B Topology112 A History105 B FOREIGN Key Students sid 53666 53688 53650 name login age gpa Jones jones cs 18 3 4 Smith smith eecs 18 3 2 Smith smith math 19 3 8 PRIMARY Key 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 CREATE TABLE Enrolled CREATE TABLE Enrolled sid CHAR 20 sid CHAR 20 cid CHAR 20 cid CHAR 20 vs grade CHAR 2 grade CHAR 2 PRIMARY KEY sid PRIMARY KEY sid cid UNIQUE cid grade Students can take only one course and no two students in a course receive the same grade 4 4 6 11 Foreign Keys Referential Integrity Foreign key a logical pointer Set of fields in a tuple in one relation that refer to a tuple in another relation Reference to primary key of the other relation All foreign key constraints enforced referential integrity i e no dangling references Foreign Keys in SQL E g Only students listed in the Students relation should be allowed to enroll for courses sid is a foreign key referring to Students CREATE TABLE Enrolled sid CHAR 20 cid CHAR 20 grade CHAR 2 PRIMARY KEY sid cid FOREIGN KEY sid REFERENCES Students Enrolled sid 53666 53666 53650 53666 cid grade Carnatic101 C Reggae203 B Topology112 A History105 B Students sid 53666 53688 53650 name login age gpa Jones jones cs 18 3 4 Smith smith eecs 18 3 2 Smith smith math 19 3 8 11111 English102 A Enforcing Referential Integrity sid in Enrolled foreign key referencing Students Scenarios Insert Enrolled tuple with non existent student id Delete a Students tuple Also delete Enrolled tuples that refer to it Cascade Disallow if referred to No Action Set sid in referring Enrolled tuples to a default value Set Default Set sid in referring Enrolled tuples to null denoting unknown or inapplicable Set NULL Similar issues arise if primary key of Students tuple is updated Integrity Constraints ICs IC condition that must be true for any instance of the database e g domain constraints ICs are specified when schema is defined ICs are checked when relations are modified A legal instance of a relation is one that satisfies all specified ICs DBMS should not allow illegal instances If the DBMS checks ICs stored data is more faithful to real world meaning Avoids data entry errors too 5 4 6 11 Where do ICs Come From Semantics of the real world Should be determined during Requirements Analysis and or Conceptual Design phases Note We can check IC violation in a DB instance We can NEVER infer that an IC is true by looking at an instance An IC is a statement about all
View Full Document
Unlocking...