Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6How to handle the identifying relationship in which a weak entity set is involved?An example showingSlide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Another perspective to see how to map E/R Diagrams to RelationsCombining Relations for binary relationshipSept. 2004 1• ER-to-Relational Mapping PrinciplesER-to-Relational MappingAnd examplesSept. 2004 2General process1. Create a relation for each strong entity type2. Create a relation for each weak entity type3. For each binary 1:1 relationship choose an entity and include the other’s PK in it as an FK4. For each binary 1:n relationship, choose the n-side entity and include an FK with respect to the other entity.5. For each binary M:N relationship, create a relation for the relationship6. For each multi-valued attribute create a new relation7. For each n-ary relationship, create a relation for the relationshipSept. 2004 31. Create a relation for each strong entity type•include all simple attributes•choose a primary keySuppose we have:course1 Noffered inSection notermmeetingcourse nonamecredit hoursdescriptionsectionSept. 2004 4We create a relation for Course - four attributes, course_no is the PK.course1 Noffered inSection notermmeetingcourse nonamecredit hoursdescriptionsectionCourseCourse_no name credit_hours descriptionSept. 2004 52. Create a relation for each weak entity type•include primary key of owner (an FK)•Owner’s PK + partial key become the PKSuppose we have:course1 Noffered inSection notermmeetingcourse nonamecredit hoursdescriptionsectionSept. 2004 6We create a relation for Sectioncourse1 Noffered inSection notermmeetingcourse nonamecredit hoursdescriptionsectionSectionCourse_no Section_no Term•PK is {course_no, section_no}.•course_no is an FK.•meeting is not a simple attribute, so it’s not included.Sept. 2004 7How to handle the identifying relationship in which a weak entity set is involved?•Relation for a weak entity set must include multiple attributes for its complete key (including those belonging to its identifying entity sets), as well as its own, partial key attributes.•An identifying (double-diamond) relationship is redundant and yields no relation.Sept. 2004 8An example showingLogins HostsAtname nameHosts(hostName) this is for the strong entity set.Logins(loginName, hostName, time)At(loginName, hostName, hostName2)Must be the sametimeAt becomes part ofLogins, so eliminated!Sept. 2004 93. For each binary 1:1 relationship choose an entity and include the other’s PK in it as an FK.department instructorchair1 1dept_nodnameinstr_no inameThere are two choices here•choose department, or •choose instructorWhich is the better choice?Sept. 2004 10Department is the better choice since it must participate in the relationship.department instructorchair1 1dept_nodnameinstr_no inameDepartmentchairdept_no dnameIf we choose department then instr_no is included as, of course, an FK. Note that instr_no must have a value.Sept. 2004 114. For each binary 1:n relationship, choose the n-side entity and include an FK w.r.t the other entity.department instructoremploys1 Ndept_nodnameinstr_no inameWe must choose instructorWe end up with:instructorinstr_no iname dept_no•PK is instr_no•dept_no is an FKNote that Step 1 would lead to the instructor relation - we have now augmented instructor with the dept_no attribute.Sept. 2004 125. 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’ PKsstudent courseenrollm ngradeEnrollstudent_no Course_no grade•PK is {student_no, course_no}•student_no is a FK•course_no is a FK•grade is an attribute of Enrollcourse_nostudent_noSept. 2004 136. 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 attributecourse1 Noffered inSection notermmeetingcourse nonamecredit hoursdescriptionsectionMeeting is a multi-valued attributeSept. 2004 14Create a relation for meetingSection was created because of Step 2 - its PK is {course_no, section_no}meetingMeetingcourse_no section_no meeting•PK is {course_no, section_no, meeting}. •Meeting is an all-key relation.Sept. 2004 157. 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 courseoffersm nroomcourse_nosemester_noinstructorinstr_nopSept. 2004 16We need one relation, offers, with PK of {semester_no, course_no, instr_no}semester courseoffersm nroom nocourse_nosemester_noinstructorinstr_nopOfferscourse_no instr_no semester_no Room_noSept. 2004 17Another perspective to see how to map E/R Diagrams to Relations•Entity sets become relations with the same set of attributes.–Regular entity set–Weak entity set – need help•Relationships –Identifying relationship •eliminated, actually merged to weak entity set relation.–Regular relationship•become relations whose attributes are only:–The keys of the connected entity sets.–Attributes of the relationship itself.Sept. 2004 18Combining Relations for binary relationship•Then to combine the relation for an entity-set E with the relation R for a many-one relationship from E to another entity set.•Of course, the combination (merging) is applicable to one-one relationship.–To avoid null values, we chose full participating entity set relation to add a foreign key referencing the opposite entity