Handling many to many relationships
Download
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


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








Why decompose
Why Decompose? new key field appointment number

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. new key field appointment number

  • 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 new key field appointment number

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 new key field appointment number

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 new key field appointment number

  • 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 new key field appointment number

The orderline represents each line of the order

Orderline (product#,order#, …)


Other types of relationships
Other types of relationships new key field appointment number

  • Recursive relationships

  • An individual entity can have a relationship with an entity of the same type


Another example estate agents
Another example- new key field appointment numberEstate agents

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


Exercise2
Exercise new key field appointment number

  • Write the table types for the following ERD


Summary
Summary new key field appointment number

  • 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 new key field appointment number

  • Data Analysis for database Design By D R Howe


ad