lecture 9 handling many to many relationships info 1408 database design concepts

Lecture 9Handling Many to Many Relationships

INFO 1408Database Design Concepts

2

Handling Many:Many Relationships

Aims:

To demonstrate how to decompose many:many (M:M) relationships

To explain why M:M relationships need to be decomposed.

Introduce other types of relationships

3

M:M Relationships

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

4

Student Modulechooses

M M

Becomes

ModuleStudentModuleChoicemakes

isfor

MM1 1

5

The Decomposition Rule

A Br

M M

Becomes

A B

MM1 1

6

Or -

A Br

M M

Becomes

A B

MM1 1

7

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

8

Exercise

Decompose this M:M relationship to form two 1:M relationships:

Assign the new entity and relationship types suitable names.

Doctor PatientexaminesMM

9

Identifiers

We have seen that an entity must have an IdentifierThe new entity type created by decomposition needs an identifierStart 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.

10

Identifiers cont.

For the second example:

Doctor (doctor#, . . . . )Patient (patient#, . . . )

Examination (Doctor#patient#, ..)

Is this a suitable identifier?.

11

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

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

13

Could a patient see the same doctor more than once?

14

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

15

This is getting a little complicated maybe we should add a new key field examination number

Examination (Examination# doctor#, patient#, date, time, ..)

Note patient# and doctor# now foreign keys

16

Why Decompose?

Student (student#, name, . . .)Module (module#, description, . . .)How do we know which students are taking

which modules?.

Student ModulechoosesM M

Back to the first exampleLook at the original M:M relationship:

17

Why Decompose? cont.

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.

18

Exercise

Actor (actor#, name, . . .)Play (play#, title, . . .) Decompose this M:M relationshipAssign the new entity type an appropriate name and think of some additional attributes for it

Assign the new entity type a suitable identifier.

Actor Playappears

_inM M

19

Common Decomposition problem

Many decomposition entities represent business transactions ( or pieces of paper)

E.G booking, order, They may be very difficult to name-

20

Common decomposition problem- example

Orderline (product#,order#, …)

The orderline represents each line of the order

21

Other types of relationships

Recursive relationshipsAn individual entity can have a relationship with an entity of the same type

22

Another example- Estate agents

It is possible to have more than one relationship between two entities

23

Summary

We have looked at decomposition of m:m relationships.Discussed how to identify a unique identifierIntroduced recursive relationshipsIntroduced multiple relationships between entities

24

References

Data Analysis for database Design By D R Howe