Unformatted text preview:

Database Management Systems, R. Ramakrishnan and J. Gehrke 1The Relational ModelDatabase Management Systems, R. Ramakrishnan and J. Gehrke 2Why Study the Relational Model?  Most widely used model.– Vendors: IBM, Informix, Microsoft, Oracle, Sybase, etc. “Legacy systems” in older models – E.G., IBM’s IMS Recent competitor: object-oriented model – ObjectStore, Versant, Ontos– A synthesis emerging: object-relational model Informix Universal Server, Oracle, DB2Database Management Systems, R. Ramakrishnan and J. Gehrke 3Relational Database: Definitions Relational database: a set of relations Relation: made up of 2 parts:– Instance : a table, with rows and columns. #Rows = cardinality, #fields = degree / arity.– 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). Can think of a relation as a set of rows or tuples (i.e., all rows are distinct).Database Management Systems, R. Ramakrishnan and J. Gehrke 4Example 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, degree = 5, all rows distinct Do all columns in a relation instance have tobe distinct?Database Management Systems, R. Ramakrishnan and J. Gehrke 5Logical DB Design: ER to Relational Entity sets to tables.CREATE TABLE Employees (ssn CHAR(11),name CHAR(20),lot INTEGER,PRIMARY KEY (ssn))EmployeesssnnamelotDatabase Management Systems, R. Ramakrishnan and J. Gehrke 6Example Instance20Jill374392348310Jane938439248310John0983763423lotnamessnEmployeesDatabase Management Systems, R. Ramakrishnan and J. Gehrke 7Integrity Constraints (ICs) IC: condition that must be true for any instance of the database– Domain constraints– Key constraints– Foreign key constraints (later) A legal instance of a relation is one that satisfies all specified ICs. – DBMS should not allow illegal instances– Avoids data entry errors too!Database Management Systems, R. Ramakrishnan and J. Gehrke 8Primary Key Constraints A set of fields is a superkey for a relation if :1. No two distinct tuples can have same values in all fields A set of fields is a keyif:1. The set of fields is a superkey2. No proper subset of the set of fields is a superkey If there’s >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key. E.g., ssn is a key for Employees. (What about name?) The set {ssn, name} is a superkey.Database Management Systems, R. Ramakrishnan and J. Gehrke 9What does this mean?CREATE TABLE Enrolled(sid CHAR(20)cid CHAR(20),grade CHAR(2),PRIMARY KEY (sid,cid) )Database Management Systems, R. Ramakrishnan and J. Gehrke 10Candidate Keys Possibly many candidate keys (specified using UNIQUE), one of which is chosen as the primary key. Each student is enrolled in at most one course No two students in a course get the same gradeCREATE TABLE Enrolled(sid CHAR(20)cid CHAR(20),grade CHAR(2),PRIMARY KEY (sid),UNIQUE (cid, grade) )Database Management Systems, R. Ramakrishnan and J. Gehrke 11Where do ICs Come From? ICs are based upon the semantics of the real-world enterprise that is being described in the database relations.  We can check a database instance to see if an IC is violated, but we can NEVER infer that an IC is true by looking at an instance.– An IC is a statement about all possible instances!– From example, we know name is not a key, but the assertion that sid is a key is given to us. Key and foreign key ICs are the most common; more general ICs supported too.Database Management Systems, R. Ramakrishnan and J. Gehrke 12ER to Relational (contd.)lotdnamebudgetdidnameDepartmentsEmployeesssnsinceWorks_InDatabase Management Systems, R. Ramakrishnan and J. Gehrke 13Relationship Sets to TablesCREATE TABLE Works_In(ssn CHAR(11),did INTEGER,since DATE,PRIMARY KEY (ssn, did),FOREIGN KEY (ssn) REFERENCES Employees,FOREIGN KEY (did) REFERENCES Departments)CREATE TABLE Employees (ssn CHAR(11),name CHAR(20),lot INTEGER,PRIMARY KEY (ssn))CREATE TABLE Departments(did INTEGER,dname CHAR(20),budget FLOAT,PRIMARY KEY (did))Database Management Systems, R. Ramakrishnan and J. Gehrke 14Example Instance20Jill374392348310Jane938439248310John0983763423lotnamessnEmployees1000KDatabases10820KPurchasing10510KSales101budgetdnamedidDepartments1 Jun 200210893843924832 Jan 200310809837634231 Jan 20031010983763423sincedidssnWorks_InDatabase Management Systems, R. Ramakrishnan and J. Gehrke 15Foreign Keys, Referential Integrity Foreign key: Set of fields in one relation that is used to `refer’ to a tuple in another relation– Must correspond to primary key of the second relation– Like a `logical pointer’. If all foreign key constraints enforced, referential integrity is achieved, i.e., no dangling references.– Not like HTML links!Database Management Systems, R. Ramakrishnan and J. Gehrke 16Enforcing Referential Integrity What if a new “Works_In” tuple is added that references a non-existent employee?– Reject it! What if an Employee tuple is deleted?– Also delete all Works_In tuples that refer to it.– Disallow deletion of Employee tuple that is referred to.– Set ssn to some default value– Set ssn in Works_In to null, denoting `unknown’ Similar if primary key of Employee tuple is updatedDatabase Management Systems, R. Ramakrishnan and J. Gehrke 17Referential Integrity in SQL/92 SQL/92 supports all 4 options on deletes and updates.– Default is NO ACTION (delete/update is rejected)– CASCADE (delete all tuples that refer to deleted tuple)– SET NULL / SET DEFAULTCREATE TABLE Works_In(ssn CHAR(11),did INTEGER,since DATE,PRIMARY KEY (ssn, did),FOREIGN KEY (ssn) REFERENCES EmployeesON DELETE CASCADE ON UPDATE SET DEFAULT,FOREIGN KEY (did) REFERENCES DepartmentsON DELETE SET NULL ON UPDATE CASCADE)Database Management Systems, R. Ramakrishnan and J. Gehrke 18ER to Relational (contd.)lotnameEmployeesssnReports_Tosubor-dinatesuper-visorDatabase Management Systems, R. Ramakrishnan and J. Gehrke 19Relationship Sets to TablesCREATE TABLE Reports_To (supervisor_ssn CHAR(11),subordinate_ssn CHAR(11),FOREIGN KEY (supervisor_ssn) REFERENCES Employees,FOREIGN KEY (subordinate_ssn) REFERENCES Employees)CREATE TABLE Employees (ssn CHAR(11),name CHAR(20),lot INTEGER,PRIMARY KEY (ssn))Database


View Full Document

CORNELL CS 432 - The Relational Model

Download The Relational Model
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view The Relational Model and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view The Relational Model 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?