Handling Many to Many Relationships

1

• Handling Many to Many Relationships

2
Handling ManyMany Relationships

• Aims
• To explain why MM relationships cannot be
  implemented in relational database systems
• To demonstrate how to decompose many to many
  (MM) 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 customers address 22, High Road

8
1NF and MM 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 MM Relationships

• The problem with MM 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 MM 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 MM 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 MM Relationships
PartID exists for OrderNo 10 but not for orders
11 12 as they are sets of integers
13
1NF and MM 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 MM Relationships

• The same problem would also exist if the data
  types were text i.e.

15
Decomposition of MM 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 MM 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 MM 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 MM 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
MM Relationships

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

22
MM Relationships
chooses
Student
Module
M
M

Becomes
23
The Decomposition Rules
r
A
B
M
M

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

Becomes
1
M
1
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 MM relationship to form two 1M
  relationships
• Assign the new entity and relationship types
  suitable names.

Doctor
examines
Patient
M
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 (Doctorpatient, ..)
• 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,patientdate, )

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,patientdate,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
MM relationship
chooses
Student
Module
M
M

• Student (studentNo, name, . . .)
• Module (moduleNo, description, . . .)
• How do we know which students are taking which
  modules?.
• We dont

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 MM 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.

Actor
Play
M
M
42
Solution

• Actor (actorNo, name )
• Play ( playNo, name, writer, length)
• Production (actorNo, playNo, first_performance_dat
  e, 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 mm
  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