1 / 21

Database Design Concepts IMAT1408

Database Design Concepts IMAT1408. Lecture 5 Handling Many to Many relationships. Aims: To demonstrate how to decompose many:many (M:M) relationships To explain why M:M relationships need to be decomposed. Handling Many:Many Relationships.

tuvya
Download Presentation

Database Design Concepts IMAT1408

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 Design ConceptsIMAT1408 Lecture 5 Handling Many to Many relationships

  2. Aims: To demonstrate how to decompose many:many (M:M) relationships To explain why M:M relationships need to be decomposed. Handling Many:Many Relationships

  3. A M:M relationship between 2 entity types must be decomposed into two 1:M relationships. M:M Relationships

  4. 1 1 M M Student Module Choice Module is for makes chooses Student M M Module Becomes

  5. The Decomposition Rule r M M B A Becomes M 1 1 M A B

  6. Or - r A M M B Becomes M 1 1 M A B

  7. Naming the new entity type and the new relationships is sometimes not easy Often the old relationship name will help you name the new entity. In our example “chooses” became “choice” If all else fails, concatenate (join) the names of the 2 original entity types (e.g. Student Module). Naming

  8. Decompose this M:M relationship to form two 1:M relationships: Assign the new entity and relationship types suitable names. examines M M Doctor Patient Exercise

  9. M 1 Doctor Patient examines Patient at Solution M 1 attends Appointment

  10. We have seen that an entity must have an identifier The new entity type created by decomposition needs an identifier Start with a composite of the identifiers of the 2 original entity types Need to consider carefully whether this will uniquely identify every occurrence of the new entity type. Identifiers

  11. For the second example: Doctor (doctor#, . . . . ) Patient (patient#, . . . ) So try Examination (doctor#, patient#,…..) Notice that doctor# and patient# are foreign keys (marked in bold type) as well as forming the new primary key Is this a suitable identifier?. Identifiers cont.

  12. To decide if an identifier is suitable: Think of some other attributes for the entity Is one pair of doctor#, patient# values associated with just one value of each of these attributes? Identifiers cont.

  13. Could a patient see the same doctor more than once? Examination (doctor#, patient#,……………………..)

  14. Could a patient see the doctor more than once in a day? Examination (doctor#, patient#, ………………………..)

  15. This is getting a little complicated - maybe we should add a new key field: examination number Examination (examination#,doctor#, patient#, date, time, ..) What does the underlining and bold type mean?

  16. Student (student#, name, . . .) Module (module#, description, . . .) How do we know which students are taking which modules? We don’t. Why Decompose? Back to the first example Look at the original M:M relationship: chooses Student Module M M

  17. Decomposing gives us a new table: Student Module (student#, module#, ...................) Is this a suitable identifier ? Now we can list which student has chosen which module. Why Decompose? cont.

  18. Actor (actor#, name, . . .) Film (film#, title, . . .) Decompose this M:M relationship Assign the new entity type an appropriate name and think of some additional attributes for it Assign the new entity type a suitable identifier. Exercise appears in Actor M M Film

  19. M 1 Actor Film Member of Solution M 1 Actors in Cast List

  20. We have looked at decomposition of M:M relationships Discussed how to identify a unique identifier. Summary

  21. Data Analysis for Database Design by D R Howe Reference

More Related