1 / 31

The Relational Model

The Relational Model. 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

kiara
Download Presentation

The Relational Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Relational Model Murali Mani

  2. 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

  3. 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 Cardinality = 2 Arity/Degree = 2 Murali Mani

  4. 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

  5. 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

  6. 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

  7. 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

  8. Keys: Example Student Primary Key: <sNumber> Candidate key: <sName> Some superkeys: {<sNumber, address>, <sName>, <sNumber>, <sNumber, sName> <sNumber, sName, address>} Murali Mani

  9. 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

  10. 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

  11. Foreign Keys: Examples Dept (dNumber, dName) Person (pNumber, pName, dept) PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (dept) REFERENCES Dept (dNumber) Persons working for Depts Person (pNumber, pName, father) PRIMARY KEY (Person) = <pNumber> FOREIGN KEY Person (father) REFERENCES Person (pNumber) Person and his/her father Murali Mani

  12. 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

  13. Relational Model: Summary • Structures • Relations (Tables) • Attributes (Columns, Fields) • Constraints • Key • Primary key, candidate key (unique) • Super Key • Foreign Key Murali Mani

  14. 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 E1, E2, …, En • Create separate relation R’ • Attributes of R’ are primary keys of E1, E2, …, En and attributes of R • Primary Key for R’ is defined as: • If the maximum cardinality of any Ei is 1, primary key for R’ = primary key for Ei • Else, primary key for R’ = primary keys for E1, E2, …, En • Define “appropriate” foreign keys from R’ to E1, E2, …, En Murali Mani

  15. Simple algorithm: Example 1 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

  16. Simple Algorithm: Example 2 Supplier (sName, sLoc) Consumer (cName, cLoc) Product (pName, pNumber) Supply (supplier, consumer, product, 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

  17. Simple Algorithm: Example 3 Part (pName, pNumber) Contains (superPart, subPart, 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

  18. 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

  19. Example 1 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

  20. Example 2 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

  21. Example 3 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

  22. 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

  23. Example 1 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

  24. Example 2 Student (sNumber, sName, pNumber, pName, years) PRIMARY KEY (Student) = <sNumber> CANDIDATE KEY (Student) = <pNumber> Note: pNumber cannot be null for any student. Murali Mani

  25. 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

  26. Composite and Multi-valued attributes in ER 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

  27. Weak entity types • Consider weak entity type E • A relation for E, say E’ • Attributes of E’ = attributes of E in ER + keys for all indentifying entity types. • Key for E’ = the key for E in ER + keys for all the identifying entity types. • Identify appropriate FKs from E’ to the identifying entity types. Murali Mani

  28. Weak entity types: Example Dept (dNumber, dName) Course (cNumber, dNumber, cName) PRIMARY KEY (Dept) = <dNumber> PRIMARY KEY (Course) = <cNumber, dNumber> FOREIGN KEY Course (dNumber) REFERENCES Dept (dNumber) Murali Mani

  29. ISA Relationship types: Method 1 Student (sNumber, sName) UGStudent (sNumber, year) GradStudent (sNumber, program) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (UGStudent) = <sNumber> PRIMARY KEY (GradStudent) = <sNumber> FOREIGN KEY UGStudent (sNumber) REFERENCES Student (sNumber) FOREIGN KEY UGStudent (sNumber) REFERENCES Student (sNumber) An UGStudent will be represented in both Student relation as well as UGStudent relation (similarly GradStudent) Murali Mani

  30. ISA Relationship types: Method 2 Student (sNumber, sName, year, program) PRIMARY KEY (Student) = <sNumber> Note: There will be null values in the relation. Murali Mani

  31. ISA Relationship types: Method 3 Student (sNumber, sName) UGStudent (sNumber, sName, year) GradStudent (sNumber, sName, program) UGGradStudent (sNumber, sName, year, program) PRIMARY KEY (Student) = <sNumber> PRIMARY KEY (UGStudent) = <sNumber> PRIMARY KEY (GradStudent) = <sNumber> PRIMARY KEY (UGGradStudent) = <sNumber> Any student will be represented in only one of the relations as appropriate. Murali Mani

More Related