lecture 9 handling many to many relationships info 1408 database design concepts
Post on 22-Dec-2015
217 views
TRANSCRIPT
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