1 / 22

Database Systems

Database Systems. Conceptual to Relational Modeling I Lecture # 9. Mapping an E-R Diagram to a Relational Schema. We cannot store data in an ER schema (There are no ER database management systems)  We have to translate our ER schema into a relational schema

caine
Download Presentation

Database Systems

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. Database Systems Conceptual to Relational Modeling I Lecture # 9

  2. Mapping an E-R Diagram to a Relational Schema We cannot store data in an ER schema (There are no ER database management systems)  We have to translate our ER schema into a relational schema  What does “translation” mean?

  3. ER Model => Relational Model • Step 1: Regular Entities (and Single-valued attributes). • Step 2: Many-to-Many Relationships • Step 3: Many-to-One Relationships • Step 4: One-to-One Relationships • Step 5: n-ary (m-way) relationships • Step 6: Weak Entities • Step 7: Multi-valued Attributes • Step 8: Entity Subtypes and Super-types • Step 9: Additional Constraints • These steps may not be followed in the above sequence. You may have to do step 6 before step 2, and so on.

  4. given family no. of equip students name STUDENT courseno COURSE subject studno Mapping Entity Types to Relations • For every entity type create a relation • Every atomic attribute of the entity type becomes a relation attribute • Composite attributes: include all the atomic attributes • Derived attributes are not included • The relation is a subset of the cross product of the domains of the attributes • Omit derived attributes

  5. given family no. of equip students name STUDENT courseno COURSE subject studno Mapping Entity Types to Relations (cntd.) STUDENT (studno, givenname, familyname) COURSE(courseno, subject, equip)

  6. courseno COURSE given family labmark subject equip studno name m n ENROL STUDENT exammark Mapping Many:many Relationship Types to Relations Create a relation with the following set of attributes: N(degree of relationship) U primary_key(Ei) U {a1…aM} i=1 primary keys of each participating entity type in the relationship attributes on the relationship type (if any)

  7. courseno COURSE given family labmark subject equip studno name m n ENROL STUDENT exammark Mapping Many:many Relationship Types to Relations (cntd.) ENROL(studno,courseno, labmark,exammark)

  8. name STAFF E1 U primary_key(E2) U {a1…an} studno given family roomno slot name relation for entity E1 primary key for E2, is now a foreign key to E2 m 1 TUTOR STUDENT Mapping One:many Relationship Types to Relations Idea: “Post the primary key” • Given E1 at ‘many’ end of relationship and E2 at ‘one’ end of relationship, add to the relation for E1 • Make the primary key of the entity at the ‘one’ end (the determined entity) a foreign key in the entity at the ‘many’ end (the determining entity). Include any relationship attributes with the foreign key entity attributes on the relationship type (if any)

  9. name STAFF studno given family roomno slot name m 1 TUTOR STUDENT Mapping One:many Relationship Types to Relations The relation STUDENT (studno, givenname, familyname) is extended to STUDENT (studno, givenname, familyname, tutor, roomno,slot) and the constraint Foreign Key STUDENT(tutor,roomno) references STAFF(name,roomno)

  10. Mapping one:many Relationship Types to Relations (cntd.)

  11. primary_key(E1) U primary_key(E2) U {a1,…,am} primary key for E1, is now a foreign key to E1; also the PK for this relation primary key for E2, is now a foreign key to E2 Mapping One:many Relationship Types to Relations(cntd.) given family name Another Idea: • If the relationship type is optional to both entity types, • and an instance of the relationship is rare, • and there are many attributes on the relationship then… Create a new relation with the set of attributes: slot roomno name m 1 TUTOR studno STAFF STUDENT attributes on the relationship type (if any)

  12. STAFF Mapping One:many Relationship Types to Relations(cntd.) TUTOR(studno, staffname, roomno, slot) and Foreign key TUTOR(studno) references STUDENT(studno) Foreign key TUTOR(staffname,roomno) references STAFF(name,roomno) given family name roomno slot name m 1 TUTOR studno STUDENT

  13. Mapping One:many Relationship Types to Relations

  14. Optional Participation of the Determined Entity (‘one end’) A school entity instance does not have to participate in a relationship instance of REG A student entity instance must participate in a relationship instance of REG SCHOOL(hons, faculty) STUDENT(studno, givenname, familyname, ??? ) given family 1 SCHOOL REG name studno hons m faculty STUDENT

  15. Optional Participation of the Determined Entity hons can’t be NULL because it is mandatory for a student to be registered for a school  “not null” constraint No student is registered for “mi”, so “mi” doesn’t occur as a foreign key value (but that’s no problem)

  16. STAFF studno Optional Participation of the Determinant Entity (‘many end’) given family name roomno slot name m 1 TUTOR STUDENT A student entity instance does not have to participate in a relationship instance of TUTOR A staff entity instance must participate in a relationship instance of TUTOR

  17. Optional Participation of the Determinant Entity (‘many end’) 1. STUDENT (studno, givenname, familyname, tutor, slot) STAFF(name, roomno) Integrity constraint: pname STAFF – ptutor STUDENT =  2. STUDENT(studno, givenname, familyname) STAFF(name, roomno) TUTOR(studno, tutor, roomno, slot)

  18. Optional Participation of the Determinant Entity (cntd.)

  19. year Mapping one:one Relationship Types to Relations • Post the primary key of one of the entity types into the other entity type as a foreign key, including any relationship attributes with it or • Merge the entity types together 1 YEAR 1 YEARTUTOR roomno name 1 STAFF

  20. studno Multi-Valued Attributes (… if they are allowed) For each multi-valued attribute of Ei, create a relation with the attributes primary_key(Ei) U multi-valued attribute The primary key comprises all attributes given family dateofbirth name STUDENT contact

  21. name STAFF roomno m 1 appraisee appraiser APPRAISAL Mapping Roles & Recursive Relationships How can the entity STAFF appear in both of its roles ? STAFF(name, roomno, ) appraiser, approomno

  22. faculty given family 1 SCHOOL REG name year studno m hons STUDENT m m n YEARREG 1 slot labmark YEAR ENROL TUTOR 1 exammark YEARTUTOR m n TEACH name 1 courseno m 1 subject roomno COURSE STAFF m 1 equip appraisee appraiser APPRAISAL Translation of the University Diagram STUDENT (studno, givenname, familyname, hons, tutor, slot, year) ENROL(studno,courseno, labmark,exammark) COURSE(courseno, subject, equip) STAFF(lecturer, roomno, appraiser) TEACH(courseno,lecturer) YEAR(year, yeartutor) SCHOOL(hons, faculty)

More Related