1 / 22

ER-to-Relational Mapping Principles Specialization/Generalization

Outline: ER-to-Relational Mapping Chapter 9 – 3rd ed. (Chap. 7 – 4 th , 5 th ed.; Chap. 9, 6 th ed.). ER-to-Relational Mapping Principles Specialization/Generalization - Superclass/Subclass Relationship. Sec. 9.1, Sec. 4.1 and 4.2. General process

freira
Download Presentation

ER-to-Relational Mapping Principles Specialization/Generalization

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

More Related