Translating ER Schema to Relational Model

1. Translating ER Schema to Relational Model Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu CS3431

2. First: Check Your Oracle Account cs3431

3. Translating ER Schema to Relational Schema • Primary keys allow entity sets and relationship setsto be expressed uniformly as relational schemas • Generally, each relational schema will have • Number of columns corresponding to the number of attributes in ERD • Column names that correspond to the attribute names cs3431

4. Basic Mapping • Simple algorithm covers base the cases, Idea: • Each entity set  separate relation • Each relationship type  separate relation • Define the primary keys as discussed in the ER Model More optimizations will come … cs3431

5. Example 1 • Loan (load_number, amount) • Customer (customer_id, customer_name, customer_street, customer_city) • Borrower (customer_id, load_number) – Many-to-Many Relationship • FOREIGN KEY Borrower (customer_id) REFERENCES Customer (customer_id) • FOREIGN KEY Borrower (loan_number) REFERENCES Loan (loan_number) cs3431

6. Example 2 • Dept (dNumber, dName) • Course (cNumber, cName) • Offers (dNumber, cNumber) -- One-to-Many Relationship from Dept to Course • FOREIGN KEY Offers(dNumber) REFERENCES Dept(dNumber) • FOREIGN KEY Offers (cNumber) REFERENCES Course(cNumber) offers Course cs3431

7. Example 3 • Product (pName, pNumber) • Supplier (sName, sLoc) • Consumer(cName, cLoc) • Supply (sName, cName, pName, price, qty) • FOREIGN KEY Supply(sName) REFERENCES Supplier(sName) • FOREIGN KEY Supply (pName) REFERENCES Product(pName) • FOREIGN KEY Supply (cName) REFERENCES Consumer(cName) cs3431

8. Example 4 • Part(pNumber, pName) • Contains(super_pNumber, sub_pNumber, quantity) • FOREIGN KEY Contains (super_pNumber) REFERENCES Part (pNumber) • FOREIGN KEY Contains (sub_pNumber) REFERENCES Part (pNumber) cs3431

9. Refinement I: Weak Entity Sets • Weak entity set does not have its own key • It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set • A weak entity set is mapped to a relation with all its attributes + the key(s) of the identifying entity set(s) • Primary key of the new relation is the: • Identifying key(s) from identifying entity set(s), Plus • Discriminator of the weak entity set • Supporting relationship is not mapped in the relation model cs3431

10. Example 5 • Dept(dNumber, dName) • Course(dNumber, cNumber, cName) • FOREIGN KEY Course(dNumber) REFERENCES Dept(dNumber) cs3431

11. Refinement II: One-to-Many & Many-to-One Cardinalities • Many-to-one and one-to-many relationship sets can be represented by adding an extra attribute to the “many”side, containing the primary key of the “one”side • This transferred primary key becomes a foreign key • The relationship itself is not mapped to the relational model • Any attributes on the relationship go to the “Many” side offers Course cs3431

12. Example 6 • Dept (dNumber, dName) • Course (cNumber, dnumber, cName) • FOREIGN KEY Course(dNumber) REFERENCES Dept(dNumber) offers Course Compare this with Example 2 (this is a better representation) cs3431

13. Example 7 • Part(pNumber, pName) • SubPart(sub_pNumber, super_pNumber, quantity) • FOREIGN KEY SubPart(super_pNumber) REFERENCES Part (pNumber) • FOREIGN KEY SubPart(sub_pNumber) REFERENCES Part (pNumber) Compare this with Example 4(Here the primary key of subPart is stronger) cs3431

14. Example 8 • Dept (dNumber, dName) • Course (cNumber, dnumber, cName) • FOREIGN KEY Course(dNumber) REFERENCES Dept(dNumber) Open head (one and must be one) offers Course Compare this with Example 6 -- In Example 6: Course.dnumber can be null --In Example 8: Course.dnumber cannot be null cs3431

15. Refinement III: One-to-One Cardinalities • One-to-one relationship sets can be represented by adding the primary key of either sides to the other side • This transferred primary key becomes a foreign key • The relationship itself is not mapped to the relational model • Any attributes on the relationship go to the side receiving the transferred primary key pName Number size Storage area Player owns pID Location StartDate cs3431

16. Example 9 • Player(pID, pNumber) • StorageArea(Number, pID, startDate, Location, size) • FOREIGN KEY StorageArea(pID) REFERENCES Player(pID) StartDate pName Number size Storage area Player owns pID Location cs3431

17. Refinement IV: Composite & Derived Attributes sNum sName Student sAge address street city state Mapping strategy (Composite): Include an attribute for every primitive component of the composite attribute in the entity Mapping strategy (Derived): Mapped as is (enforced later using triggers) Student(sNum, sName, sAge, street, city, address) cs3431

18. Refinement V: Multi-valued Attributes sNum sName Student major sAge address street city state • Mapping strategy: • Represented as a relation by itself. • The primary key of the relation = Attribute + the primary key of the main entity set • Student(sNum, sName, sAge, street, city, address) • StudentMajor(sNum, major) • FOREIGN KEY StudentMajor (sNum) REFERENCES Student (sNum) cs3431

19. Refinement VI: ISA Relationships • ISA is a one-to-one relationship BUTthe sub-class entity sets inherit attributes from the super-class entity set • That is why it does not follow the one-to-one rules • Basically many ways for the mapping depending on whether it is total vs. partial and overlapping vs. disjoint • Super-class key is always the primary key

20. ISA Relationship : Method 1 (Relation for each Entity Set) 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 • A GStudentwill be represented in both Student relation as well as GStudentrelation

21. ISA Relationship : Method 2 (One Relation for All) Student (sNumber, sName, year, program) PRIMARY KEY (Student) = <sNumber> Note: There will be null values in the relation. cs3431

22. ISA Relationship : Method 3 (Relations only for SubClasses) • Any student will be represented in only one or possibly both relations as appropriate • Assumes totalrelationship UGStudent (sNumber, sName, year) GradStudent (sNumber, sName, program) PRIMARY KEY (UGStudent) = <sNumber> PRIMARY KEY (GradStudent) = <sNumber> cs3431

23. ISA Relationship : Method 4 (Relation for each combination) Any student will be represented in only one of the relations as appropriate. 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>

24. Mapping from ER model to Relational model: Summary • Basic algorithm covers the main cases • Refinement I : Weak Entity Sets • Refinement II : One-to-Many Relationships • Refinement III : One-to-One Relationships • Refinement IV : Composite & Derived Attributes • Refinement V : Multi-Valued Attributes • Refinement VI : ISA Relationships cs3431

25. What about an Exercise cs3431