Database Design Concepts IMAT1408

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