Handling Many to Many Relationships

1. Handling Many to Many Relationships

2. Handling Many:Many Relationships Aims: • To explain why M:M relationships cannot be implemented in relational database systems • To demonstrate how to decompose many to many (M:M) relationships • Introduce other types of relationships

3. Entities and Tables • Each entity will become a table in the database • Each table will have several attributes i.e. A Customer would have, as a minimum ‘forename’, ‘surname’, ‘address’ attributes • Each row in every table will be unique

4. Row Uniqueness • To ensure that each row is unique we add a primary key to each table • A primary key may be a single attribute in each table i.e. customer ID or it could be composed of several attributes in a table – this is know as a composite primary key

5. Primary Keys and Foreign keys • Relationships between entities identified in an ERD are implemented in relational databases through the primary keys • To implement the relationships we ‘post’ the primary key from one table into the other tables – these are known as foreign keys • The only data that is ever repeated in the tables is the primary key as a foreign key in another table

6. Normalisation • To maintain the integrity (the correctness) of the data we apply normalisation techniques to the database • There are several levels of normalisation but is sufficient most database applications are normalised to 3rd Normal Form • For the purposes of this module we will cover 1st , 2nd and 3rd normal forms

7. 1st Normal Form (1NF) • To be in 1NF each attribute value will contain only atomic values • The attribute could be composed of several component parts but the value is seen by the DBMS as a single value • For example, a customer’s address – 22, High Road

8. 1NF and M:M Relationships • To create a relationship between two tables we ‘post’ the primary key from one table into the other table as a foreign key • The data types in each table in the relationship must be the same i.e. customerID = Integer • Also the value of the foreign key of the ‘posted’ table must exist as a primary key value in the ‘posting’ table

9. 1NF and M:M Relationships • The problem with M:M relationships is deciding which table is the ‘provider’ and which is the ‘recipient’ • For example, the ERD below has been drawn for an ordering system

10. 1NF and M:M Relationships • The relationship reads: An Order must be for at least 1 but could be for many Parts A Part may be used on many orders

11. 1NF and M:M Relationships • If we decided that the Parts table will be the provider of the primary key and the Orders table will contain the foreign key then the relationship would be implemented using the same data type i.e. PK = PartID (integer) in the Part table FK =PartID (integer) in the Orders table

12. 1NF and M:M Relationships PartID exists for OrderNo 10 but not for orders 11 & 12 as they are ‘sets’ of integers

13. 1NF and M:M Relationships • Multiple values (or sets) cannot be entered as foreign key values as they do not exist in the same format in the Part table • It would violate the referential integrity of the data • The same problem would exist if we tried to post the orderNo from the Orders table to the Parts table as a foreign key

14. 1NF and M:M Relationships • The same problem would also exist if the data types were text i.e.

15. Decomposition of M:M Relationships • The solution to the problem is to decompose the entities by introducing an intermediary table – see below • The new tables multiplicity is now the Many end of the relationship and the original entities multiplicity becomes ‘1’ • The optionality of the new entity is mandatory but the optionality of the original entities remains as before

16. Decomposition of M:M Relationships • The new entity, which will eventually become a table in the database would not have been identified in the original systems investigation but it is required to fulfil the business needs and to maintain the referential integrity of the data • We always ‘post’ the primary keys from the ‘1’ end of the relationship to the ‘many’ end of the relationship

17. Decomposition of M:M Relationships – a New Entity Order Part Posting from Order to Order Line Orderline Posting from Part to Order Line

18. Other Solutions? • Adding the intermediary table is the only correct solution to the problem of M:M relationships • However, some database designers think that by adding extra columns is the answer

19. Adding Extra Columns? • The problem here is that the database designer does not know the maximum parts required for future orders and extra columns cannot be added by the user as and when needed • It also introduces redundant data in the form of NULL values

20. Adding Extra Rows? • Adding extra rows is not an option as we would be repeating primary key values which would violate the entity integrity rule whereby all rows are uniquely identified by the primary key • It would also introduce redundant data i.e. dates

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

22. 1 1 M M Student Module Choice is for Module makes M:M Relationships chooses Student M M Module Becomes

23. The Decomposition Rules r A M M B Becomes 1 1 M M A B

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

25. Naming • Naming the new entity type and the new relationships is sometimes not easy • Consider what it is representing • If all else fails, concatenate/ join the names of the 2 original entity types (e.g. Student Module).

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

27. Solution

28. Table Types • When we have modelled our entities we could then design the tables by adding the attributes of the proposed table • We describe the tables using table types whereby the table name is appended with an attribute list in parentheses • The primary key is shown emboldened and underlined • Foreign keys are shown in italics

29. Table Types cont. • The table types for the following ERD could be: Customer (customerNo, surname, address…) Orders (orderNo, orderDate, customerNo…) The ellipses (…) denote other possible attributes

30. Identifiers • We have seen that an entity must have an Identifier – Primary Key • 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.

31. Identifiers cont. • For the second example: Doctor (doctor#, . . . . ) Patient (patient#, . . . ) Appointment (Doctor#patient#, ..) • Is this a suitable identifier?.

32. Identifiers cont. • 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?.

33. 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?. No

34. Could a patient see the same doctor more than once?

35. Could a patient see the same doctor more than once? Yes – So add date Appointment (doctor#,patient#date, …)

36. Could a patient see the doctor more than once in a day?

37. Could a patient see the doctor more than once in a day? Yes ( not common) so add time • Appointment (doctor#,patient#date,time..)

38. This is getting a little complicated maybe we should add a new key field appointment number • Appointment (AppointmentNo doctorNo, patientNo, date, time, ..) • Note patientNo and doctorNo are now foreign keys

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

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

41. Exercise appears _in • Actor (actorNo, name, . . .) Play (playNo, 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 suitableidentifier. Actor M M Play

42. Solution Actor (actorNo, name …) Play ( playNo, name, writer, length…) Production (actorNo, playNo, first_performance_date, director, venue/theatre_name . . . etc!)

43. Common Decomposition problem • Many decomposition entities represent business transactions ( or pieces of paper) • For example, booking, order etc • They may be very difficult to name

44. Common decomposition problem- example The orderline represents each line of the order Orderline (product#,order#, …)

45. Other types of relationships • Recursive relationships • An individual entity can have a relationship with an entity of the same type

46. Another example- Estate agents • It is possible to have more than one relationship between two entities

47. Exercise • Write the table types for the following ERD

48. Summary • We have looked at decomposition of m:m relationships. • Discussed how to identify a unique identifier • Introduced recursive relationships • Introduced multiple relationships between entities

49. References • Data Analysis for database Design By D R Howe