1 / 24

Translating ER Schema to Relational Model

Translating ER Schema to Relational Model. Basic Mapping So Far. Simple algorithms covers base case Idea: Each entity type = separate relation Each relationship type = separate relation. Simple Algorithm: Example 3. Part (pName, pNumber) Contains (superPart, subPart, quantity).

chin
Download Presentation

Translating ER Schema to 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. Translating ER Schema to Relational Model CS3431

  2. Basic Mapping So Far • Simple algorithms covers base case • Idea: • Each entity type = separate relation • Each relationship type = separate relation cs3431

  3. Simple Algorithm: Example 3 Part (pName, pNumber) Contains (superPart, subPart, quantity) PRIMARY KEY (Part) = <pNumber> PRIMARY KEY (Contains) = <subPart,superPart> FOREIGN KEY Contains (superPart) REFERENCES Part (pNumber) FOREIGN KEY Contains (subPart) REFERENCES Part (pNumber) cs3431

  4. Next • Let’s consider constraints • Let’s reduce number of relations cs3431

  5. Refinement for Simple Mapping • Primary Key for R’ is defined as: • If the maximum cardinality of any Ei is 1, primary key for R’ = primary key for Ei cs3431

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

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

  8. Example 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. cs3431

  9. 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) Question: Will Student.advisor attribute ever be NULL ? Answer: No ! cs3431

  10. 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) What about NULL attributes ? Dept and years may be null for a person cs3431

  11. Remember the 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) cs3431

  12. Example 3 Part (pNumber, pname, superPart, quantity) PRIMARY KEY (Part) = <pNumber> FOREIGN KEY Part (superPart) REFERENCES Part (pNumber) Note: superPart indicates the superpart of a part, and it may be null cs3431

  13. Decreasing the Number of Relations Summary of 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 cardinality of E is (1, 1), then no “new nulls” added • If cardinality of E is (0, 1) then may add “new nulls” cs3431

  14. Decreasing the number of Relations Technique 2 • If relationship type R between E1 and E2 is one-to-one [1:1], • and the cardinality of E1 or of E2 is (1, 1), • then we can combine everything into 1 relation cs3431

  15. Decreasing the number of Relations Technique 2 - Method Details • Let us assume the cardinality of E1 in R is (1, 1). • Then we create one relation for entity E2 • And, we move all attributes of E1 and for R to be attributes of E2. cs3431

  16. Example 1 Student-BIG (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 cs3431

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

  18. Decreasing the Number of Relations Technique 2 • If relationship type R between E1 and E2 is one-to-one [1:1], • and the cardinality of E1 or of E2 is (1, 1), • then we can combine everything into 1 relation • Not always recommended! Thus use with care ! • While very compact, semantically may not be clearest choice ! cs3431

  19. ER Model: Complex Attributes Composite Attribute: address sumer sName Student sAge address street city state cs3431

  20. Mapping details • Composite attribute in ER • Include an attribute for every component of the composite attribute. cs3431

  21. ER Model: Complex Attributes Multivalued Attribute: major cs3431

  22. Mapping details • Multi-valued attribute in ER • We need a separate relation for any multi-valued attribute. • Identify appropriate attributes, keys and foreign key constraints. cs3431

  23. Example: Composite and Multi-valued attributes in ER Student (sNumber, sName, sAge, street, city, state) PRIMARY KEY (Student) = <sNumber> StudentMajor (sNumber, major) PRIMARY KEY (StudentMajor) = <sNumber, major> FOREIGN KEY StudentMajor (sNumber) REFERENCES Student (sNumber) cs3431

  24. Summary • Simple algorithms covers base case • Refinements : Reduce number of relations • Refinements: Consider constraints (not NULL) • Consider other ER constructs like complex and multi-valued attributes cs3431

More Related