Databases: Entity Relationships

From Wikibooks, open books for an open world
Jump to: navigation, search

UNIT 3 - ⇑ Databases ⇑

← Primary keys Entity relationship modelling Normalisation →


Databases will probably store data about a variety of things. For example, if we look at the tables required for an online store:

Staff
Products
Receipts
Customers

These tables could sit on their own, but when we start to describe each of them we notice that they are related to each other. For example:

The Receipt table records which product a customer has bought, and which date it was purchased on.

This describes the Receipt record, and looking at its structure we see that the primary keys from other tables are included in it.

Receipt(CustomerID, ProductID DateTime, Total, StaffID)

In other words we could say:

A receipt has one customer
A customer can have many receipts
A receipt has one product
A product can be part of many receipts
A receipt was created by one staff member
A staff member can create many receipts

We can then draw this diagram like so:

CPT-Databases-ExampleReceipt.svg

To link tables together and allow for us to query a database we use relationships. There are three types of relationship that you need to know. Each is shown below using 'crows foot notation' which is one of many ways to describe these relationships:

Diagram Name Description
An Author can write several Books, and a Book can be written by several Authors
Many to Many An Author can write several Books, and a Book can be written by several Authors
A Biological Mother can have many children, and a child can have only one Biological Mother
One to Many A Biological Mother can have many children, and a child can have only one Biological Mother
A Country has only Capital City, and a Capital City is in only one Country
One to One A Country has only one Capital City, and a Capital City is in only one Country
Exercise: Relationships

Describe the following relationships:
CPT-Databases-Exercise11.svg

Answer :

An animal has one genome
A genome describes only one animal

CPT-Databases-Exercise-MMStudentClass.svg

Answer :

A student has many classes
A class has many students

CPT-Databases-Example1MLeagueTeam.svg

Answer :

A league has many teams
A team is only in one league

CPT-Databases-Exercise-PlayerAnswerQuestion.svg

Answer :

  • A player answers many questions
  • A question can be answered many times
  • Each answer is only applicable to one question
  • A player can give many answers
  • Each answer has only only player contributing

Draw the following relationships:
A vehicle has many wheels, each wheel has one vehicle

Answer :

vehicle ---< wheel

The relationship between a father and a child

Answer :

Father >--< Child (unless we are talking about biological fathers: Father--<Child )

The relationship between a politician and a nose

Answer :

politician ---- nose

The relationship between an owner and a cat

Answer :

Cat >---< Owner (even though an Owner may own more than one cat, a cat might have more than one owner)

The relationship between a car and a driver

Answer :

car --- driver (a car can only be driven by one driver, a driver can only drive one car at one time)

The relationship between a unicycle and a wheel

Answer :

wheel --- unicycle

The relationship between a house and a postcode

Answer :

House >--- Postcode (a house is assign one postcode, but that same postcode might be assigned to many houses)