handling many to many relationships
Download
Skip this Video
Download Presentation
Handling Many to Many Relationships

Loading in 2 Seconds...

play fullscreen
1 / 49

Handling Many to Many Relationships - PowerPoint PPT Presentation


  • 171 Views
  • Uploaded on

Handling Many to Many Relationships. 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. Entities and Tables.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Handling Many to Many Relationships' - lara-love


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
handling many many relationships
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
entities and tables
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
row uniqueness
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
primary keys and foreign keys
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
normalisation
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
1 st normal form 1nf
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
1nf and m m relationships
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
1nf and m m relationships1
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
1nf and m m relationships2
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

1nf and m m relationships3
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

1nf and m m relationships4
1NF and M:M Relationships

PartID exists for OrderNo 10 but not for orders 11 & 12 as they are ‘sets’ of integers

1nf and m m relationships5
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
1nf and m m relationships6
1NF and M:M Relationships
  • The same problem would also exist if the data types were text i.e.
decomposition of m m relationships
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
decomposition of m m relationships1
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
decomposition of m m relationships a new entity
Decomposition of M:M Relationships – a New Entity

Order

Part

Posting from Order to Order Line

Orderline

Posting from Part to Order Line

other solutions
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
adding extra columns
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
adding extra rows
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
m m relationships
M:M Relationships
  • A M:M relationship between 2 entity types must be decomposed into two 1:M relationships.
m m relationships1

1

1

M

M

Student

Module

Choice

is

for

Module

makes

M:M Relationships

chooses

Student

M

M

Module

Becomes

the decomposition rules
The Decomposition Rules

r

A

M

M

B

Becomes

1

1

M

M

A

B

slide24
Or -

r

A

M

M

B

Becomes

1

1

M

M

A

B

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

table types
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
table types cont
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

identifiers
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.
identifiers cont
Identifiers cont.
  • For the second example:

Doctor (doctor#, . . . . )

Patient (patient#, . . . )

Appointment (Doctor#patient#, ..)

  • Is this a suitable identifier?.
identifiers cont1
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?.
slide33
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
slide35
Could a patient see the same doctor more than once? Yes – So add date

Appointment (doctor#,patient#date, …)

slide37
Could a patient see the doctor more than once in a day? Yes ( not common) so add time
  • Appointment (doctor#,patient#date,time..)
slide38
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
why decompose
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

why decompose cont
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.

exercise1
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

solution1
Solution

Actor (actorNo, name …)

Play ( playNo, name, writer, length…)

Production (actorNo, playNo, first_performance_date, director, venue/theatre_name . . . etc!)

common decomposition problem
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
common decomposition problem example
Common decomposition problem- example

The orderline represents each line of the order

Orderline (product#,order#, …)

other types of relationships
Other types of relationships
  • Recursive relationships
  • An individual entity can have a relationship with an entity of the same type
another example estate agents
Another example- Estate agents
  • It is possible to have more than one relationship between two entities
exercise2
Exercise
  • Write the table types for the following ERD
summary
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
references
References
  • Data Analysis for database Design By D R Howe
ad