The Relational Model Murali Mani Why Relational Model? Currently - PDF document

The Relational Model Murali Mani Why Relational Model? � Currently the most widely used � Vendors: Oracle, Microsoft, IBM � Older models still used � IBM’s IMS (hierarchical model) � Recent competitions � Object Oriented Model: ObjectStore � Implementation standard for relational Model � SQL (Structured Query Language) � SQL 3: includes object-relational extensions Murali Mani 1

Relational Model � Structures � Relations (also called Tables ) � Attributes (also called Columns or Fields ) � Note: Every attribute is simple (not composite or multi-valued) � Constraints � Key and Foreign Key constraints (More constraints later) � Eg: Student Relation (The following 2 relations are equivalent) Student Student sNumber sName sNumber sName Cardinality = 2 Arity/Degree = 2 1 Dave 2 Greg 2 Greg 1 Dave Murali Mani Relational Model � Schema for a relation � Eg: Student (sNumber, sName) � PRIMARY KEY (Student) = <sNumber> � Schema for a database � Schemas for all relations in the database � Tuples (Rows) � The set of rows in a relation are the tuples of that relation � Note: Attribute values may be null Murali Mani 2

Primary Key Constraints � A set of attributes is a key for a relation if: � No two distinct tuples can have the same values in all key fields � A proper subset of the key attributes is not a key. � Superkey: A proper subset of a superkey may be a superkey � If multiple keys, one of them is chosen to be the primary key. � Eg: PRIMARY KEY (Student) = <sNumber> � Primary key attributes cannot take null values Murali Mani Candidate Keys (SQL: Unique) � Keys that are not primary keys are candidate keys. � Specified in SQL using UNIQUE � Attribute of unique key may have null values ! � Eg: Student (sNumber, sName) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <sName> Murali Mani 3

Violation of key constraints � A relation violates a primary key constraint if: � There is a row with null values for any attribute of primary key. � (or) There are 2 rows with same values for all attributes of primary key � Consider R (a, b) where a is unique. R violates the unique constraint if all of the following are true � 2 rows in R have the same non-null values for a Murali Mani Keys: Example Student sNumber sName address 1 Dave 144FL 2 Greg 320FL Primary Key: <sNumber> Candidate key: <sName> Some superkeys: {<sNumber, address>, <sName>, <sNumber>, <sNumber, sName> <sNumber, sName, address>} Murali Mani 4

Foreign Key Constraints � To specify an attribute (or multiple attributes) S1 of a relation R1 refers to the attribute (or attributes) S2 of another relation R2 � Eg: Professor (pName, pOffice) Student (sNumber, sName, advisor) PRIMARY KEY (Professor) = <pName> FOREIGN KEY Student (advisor) REFERENCES Professor (pName) Murali Mani Foreign Key Constraints � FOREIGN KEY R1 (S1) REFERENCES R2 (S2) � Like a logical pointer � The values of S1 for any row of R1 must be values of S2 for some row in R2 (null values are allowed) � S2 must be a key for R2 � R2 can be the same as R1 (i.e., a relation can have a foreign key referring to itself). Murali Mani 5

Foreign Keys: Examples Dept (dNumber, dName) Person (pNumber, pName, dept) PRIMARY KEY (Dept) = <dNumber> Persons working for Depts PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (dept) REFERENCES Dept (dNumber) Person (pNumber, pName, father) Person and his/her father PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (father) REFERENCES Person (pNumber) Murali Mani Violation of Foreign Key constraints � Suppose we have: FOREIGN KEY R1 (S1) REFERENCES R2 (S2) � This constraint is violated if � Consider a row in R1 with non-null values for all attributes of S1 � If there is no row in R2 which have these values for S2, then the FK constraint is violated. Murali Mani 6

Relational Model: Summary � Structures � Relations (Tables) � Attributes (Columns, Fields) � Constraints � Key � Primary key, candidate key (unique) � Super Key � Foreign Key Murali Mani ER schema → Relational schema Simple Algorithm � Entity type E → Relation E’ � Attribute of E → Attribute as E’ � Key for E → Primary Key for E’ � For relationship type R between E 1 , E 2 , …, E n � Create separate relation R’ � Attributes of R’ are primary keys of E 1 , E 2 , …, E n and attributes of R � Primary Key for R’ is defined as: If the maximum cardinality of any E i is 1, primary key for R’ = � primary key for E i Else, primary key for R’ = primary keys for E 1 , E 2 , …, E n � � Define “appropriate” foreign keys from R’ to E 1 , E 2 , …, E n Murali Mani 7

Simple algorithm: Example 1 pNumber dNumber (1, *) Works (0, *) Person Dept For pName dName years Person (pNumber, pName) Dept (dNumber, dName) WorksFor (pNumber, dNumber, years) PRIMARY KEY (Person) = <pNumber> PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (WorksFor) = <pNumber, dNumber> FOREIGN KEY WorksFor (pNumber) REFERENCES Person (pNumber) FOREIGN KEY WorksFor (dNumber) REFERENCES Dept (dNumber) Murali Mani Simple Algorithm: Example 2 Supplier (sName, sLoc) pName pNumber Consumer (cName, cLoc) Product (pName, pNumber) Supply (supplier, consumer, Product product, price, qty) (0, *) sName cName (0, *) (1, *) Supplier Supply Consumer sLoc cLoc price qty PRIMARY Key (Supplier) = <sName> PRIMARY Key (Consumer) = <cName> PRIMARY Key (Product) = <pName> PRIMARY Key (Supply) = <supplier, consumer, product> FOREIGN KEY Supply (supplier) REFERENCES Supplier (sName) FOREIGN KEY Supply (consumer) REFERENCES Consumer (cName) FOREIGN KEY Supply (product) REFERENCES Product (pName) Murali Mani 8

Simple Algorithm: Example 3 pName pNumber Part Part (pName, pNumber) Contains (superPart, subPart, quantity) superPart subPart (0, *) (0, 1) Contains quantity PRIMARY KEY (Part) = <pNumber> PRIMARY KEY (Contains) = <subPart> FOREIGN KEY Contains (superPart) REFERENCES Part (pNumber) FOREIGN KEY Contains (subPart) REFERENCES Part (pNumber) Murali Mani Decreasing the number of Relations Technique 1 � If the relationship type R contains an entity type, say E, whose maximum cardinality is 1, then R may be represented as attributes of E. � If the cardinality of E is (1, 1), then no “new nulls” are introduced � If the cardinality of E is (0, 1) then “new nulls” may be introduced. Murali Mani 9

Example 1 sNumber pNumber (1,1) Has (0, *) Student Professor Advisor sName pName years Student (sNumber, sName, advisor, years) Professor (pNumber, pName) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (Professor) = <pNumber> FOREIGN KEY Student (advisor) REFERENCES Professor (pNumber) Note: advisor will never be null for a student Murali Mani Example 2 pNumber dNumber Works (0,1) (0, *) Person Dept For pName dName years Person (pNumber, pName, dept, years) Dept (dNumber, dName) PRIMARY KEY (Person) = <pNumber> PRIMARY KEY (Dept) = <dNumber> FOREIGN KEY Person (dept) REFERENCES Dept (dNumber) Dept and years may be null for a person Murali Mani 10

Example 3 pName pNumber Part superPart subPart (0, *) (0, 1) Contains quantity Part (pNumber, pname, superPart, quantity) PRIMARY KEY (Part) = <pNumber> FOREIGN KEY Part (superPart) REFERENCES Part (pNumber) Note: superPart gives the superpart of a part, and it may be null Murali Mani Decreasing the number of Relations Technique 2 (not recommended) � If the relationship type R between E1 and E2 is 1:1, and the cardinality of E1 or E2 is (1, 1), then we can combine everything into 1 relation. � Let us assume the cardinality of E1 is (1, 1). We have one relation for E2, and move all attributes of E1 and for R to be attributes of E2. � If the cardinality of E2 is (1, 1), no “new nulls” are introduced � If the cardinality of E2 is (0, 1) then “new nulls” may be introduced. Murali Mani 11

Example 1 sNumber pNumber (0,1) Has (1,1) Student Professor Advisor sName pName years Student (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber, pName, and years can be null for students with no advisor Murali Mani Example 2 sNumber pNumber Has (1,1) (1,1) Student Professor Advisor sName pName years Student (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber cannot be null for any student. Murali Mani 12

Other details � Composite attribute in ER � Include an attribute for every component of the composite attribute. � Multi-valued attribute in ER � We need a separate relation for any multi-valued attribute. � Identify appropriate attributes, keys and foreign key constraints. Murali Mani Composite and Multi-valued attributes in ER sNumber sName major Student sAge address street city state Student (sNumber, sName, sAge, street, city, state) StudentMajor (sNumber, major) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (StudentMajor) = <sNumber, major> FOREIGN KEY StudentMajor (sNumber) REFERENCES Student (sNumber) Murali Mani 13