ER-to-Relational Mapping Principles Specialization/Generalization

1. Outline: ER-to-Relational Mapping Chapter 9 – 3rd ed. (Chap. 7 – 4th, 5th ed.; Chap. 9, 6th ed.) • ER-to-Relational Mapping Principles • Specialization/Generalization • - Superclass/Subclass Relationship ACS-3902 Yangjun Chen

2. Sec. 9.1, Sec. 4.1 and 4.2. • General process • 1. Create a relation for each strong entity type • 2. Create a relation for each weak entity type • include primary key of owner (an FK - foreign key) • owner’s PK + partial key becomes PK • 3. For each binary 1:1 relationship choose an entity and include the other’s PK in it as an FK. Include any attributes of the relationship • 4. For each binary 1:n relationship, choose the n-side entity and include an FK w.r.t the other entity. Include any attributes of the relationship ACS-3902 Yangjun Chen

3. 5. For each binary M:N relationship, create a relation for the • relationship • include PKs of both participating entities and any attributes • of the relationship • PK is the concatenation of the participating entity PKs • 6. For each multivalued attribute create a new relation • include the PK attributes of the entity type • PK is the PK of the entity type and the multivalued attribute • 7. For each n-ary relationship, create a relation for the relationship • include PKs of all participating entities and any attributes of • the relationship • PK may be the concatenation of the participating entity PKs ACS-3902 Yangjun Chen

4. 1. Create a relation for each strong entity type • include all simple attributes • choose a primary key Suppose we have: name term course no Section no offered in 1 N course section description credit hours meeting ACS-3902 Yangjun Chen

5. We create a relation for Course - four attributes, course_no is the PK. name term course no Section no offered in 1 N course section description credit hours meeting Course Course_no name credit_hours description ACS-3902 Yangjun Chen

6. 2. Create a relation for each weak entity type • include primary key of owner (an FK) • Owner’s PK + partial key become the PK Suppose we have: name term course no Section no offered in 1 N course section description credit hours meeting ACS-3902 Yangjun Chen

7. We create a relation for Section name term course no Section no offered in 1 N course section description credit hours meeting Section • PK is {course_no, section_no}. • course_no is an FK. • meeting is not a simple attribute, so it’s not included. Course_no Section_no Term ACS-3902 Yangjun Chen

8. instr_no iname dname dept_no chair 1 1 department instructor 3. For each binary 1:1 relationship choose an entity and include the other’s PK in it as an FK. • There are two choices here • choose department, or • choose instructor • Which is the better choice? ACS-3902 Yangjun Chen

9. Department is the better choice since it must participate in the relationship. instr_no iname dname dept_no chair 1 1 department instructor If we choose department then instr_no is included as, of course, an FK. Note that instr_no must have a value. Department dept_no dname chair ACS-3902 Yangjun Chen

10. 4. For each binary 1:n relationship, choose the n-side entity and include an FK w.r.t the other entity. instr_no iname dname dept_no employs 1 N department instructor We must choose instructor We end up with: Note that Step 1 would lead to the instructor relation - we have now augmented instructor with the dept_no attribute. instructor • PK is instr_no • dept_no is an FK instr_no iname dept_no ACS-3902 Yangjun Chen

11. 5. For each binary M:N relationship, create a relation for the relationship • include PKs of both participating entities and any attributes of the relationship • PK is the catenation of the participating entities’ PKs student_no course_no grade m n student course enroll Enroll • PK is {student_no, course_no} • student_no is a FK • course_no is a FK • grade is an attribute of Enroll student_no Course_no grade ACS-3902 Yangjun Chen

12. 6. For each multi-valued attribute create a new relation • include the PK attributes of the entity type • PK is the PK of the entity type and the multi-valued attribute name term course no Section no offered in 1 N course section description credit hours meeting Meeting is a multi-valued attribute ACS-3902 Yangjun Chen

13. Create a relation for meeting Section was created because of Step 2 - its PK is {course_no, section_no} meeting Meeting • PK is {course_no, section_no, meeting}. • Meeting is an all-key relation. course_no section_no meeting ACS-3902 Yangjun Chen

14. 7. For each n-ary relationship, create a relation for the relationship • include PKs of all participating entities and any attributes of the relationship • PK may be the catenation of the participating entity PKs (depends on cardinalities) semester_no course_no room m n semester course offers p instructor instr_no ACS-3902 Yangjun Chen

15. We need one relation, offers, with PK of {semester_no, course_no, instr_no} semester_no course_no room no m n semester course offers p instructor instr_no Offers course_no instr_no semester_no Room_no ACS-3902 Yangjun Chen

16. Return to Entity-Relationship Modeling • Consider Section 4.2 on Specialization and Generalization • Specialization is the process of defining a set of sub-entities of some entity type. Generalization is the opposite approach/process of determining a supertype based on certain entities having common characteristics. • e.g. employees may be paid by the hour or a salary (part vs full-time) • e.g. students may be part-time or full-time; graduate or undergraduate • these are similar to 1:1 relationships, but they always involve entities of one (super)type • these are ‘is-a’ relationships student d graduate undergraduate ACS-3902 Yangjun Chen

17. Subtype is determined by the student_class attribute student A student must be a graduate or undergraduate Student_class The bubble and the d imply disjoint subtypes (o - overlap subtypes) d The arc implies graduate and undergraduate are subtypes of student graduate undergraduate • Participation of supertype may be mandatory or optional • Participation of subtype is always mandatory • Subtypes may be disjoint or overlapping • a predicate (on an attribute) determines the subtype: e.g. attribute Student_class • Student_class = ‘graduate’; Student_class = ‘undergraduate’ ACS-3902 Yangjun Chen

18. Mapping to a relational database - Section 9.2.1 (Step 8) • 4 choices: • 1. Create separate relations for the supertype and each of the subtypes. • 2. Create relations for the subtypes only - each contains attributes from the supertype. • 3. (disjoint subtypes) Create only one relation - includes all of the attributes for the supertype and all for the subtypes, and one discriminator attribute. • 4. (overlapping subtypes) Create only one relation - includes all of the attributes for the supertype and all for the subtypes, and one logical discriminator attribute per subtype. • PK is always the same - determined from the supertype ACS-3902 Yangjun Chen

19. Example for super- & sub-types: choice 1 fname lname minit Address bDates JobType name Ssn EMPLOYEE TypingSpeed d EngType TGrade SECRETARY TECHNICIAN ENGINEER EMPLOYEE fname, minit, lname, ssn, bdate, address, JobType SECRETARY TECHNICIAN ENGINEER Essn, TypingSpeed Essn, TGrade Essn, EngType ACS-3902 Yangjun Chen

20. Example for super- & sub-types: choice 2 Price LicensePlate VehicleId Vehicle TNoOfPassengers d NoOfAxles CAR TRUCK Tonnage MaxSpeed CAR VehicleId, LicensePlate, Price, MaxSpeed, NoOfPassenger TRUCK VehicleId, LicensePlate, Price, NoOfAxles, Tonnage ACS-3902 Yangjun Chen

21. Example for super- & sub-types: choice 3 fname lname minit Address bDates JobType name Ssn EMPLOYEE d TypingSpeed EngType TGrade SECRETARY TECHNICIAN ENGINEER EMPLOYEE fname, minit, lname, ssn, bdate, address, JobType,TypingSpeed, Tgrade, EngType ACS-3902 Yangjun Chen

22. Example for super- & sub-types: choice 4 Description PartNo Part manufactureDate o Supplier DrawingNo ListPrice Manufacture_Part Purchased_Part BatchNo Part PartNo, Desription, MFlag, Drawing, ManufactureDate, BatchNo, Pflag, Supplier, ListPrice ACS-3902 Yangjun Chen