Entity Relationship Diagrams (OCR A Level Computer Science)
Revision Note
Written by: Neil Southin
Reviewed by: James Woodhouse
Entity Relationship Types
What is an Entity?
An entity in something worthy of capturing and storing data about e.g. students, orders, products, courses, customers
Entities become tables in a relational database
Relational databases store different entities in separate tables. Linking tables depends on the relationships between entities.
There are 3 types of (sometimes called degrees of) relationships:
One to one
One to many
Many to many
Imagine a company has
A table of products
A table of customers
A table of the orders the customers have made
What is the relationship between a customer and an order?
One customer can make multiple (many) orders
But each order relates to a specific (one) customer
So the relationship between customer and order is one to many
Now consider the relationship between a product and an order
An order could have more than one (many) products on it
A product could be on more than one (many) order
So the relationship between order and product is many to many
One to one relationships also exist but are not very common in databases
Drawing Entity Relationship Diagrams
Entity Relationship Diagrams (ERDs) are simple diagrams that represent the entities (tables) that will be in a database and the relationships between these entities
The entities are drawn as boxes with the entity name in
The relationships are drawn in as what is known as ‘crow’s feet notation’
This is how to draw the relationships in the exam:
The names of the entities would go inside the boxes
Examiner Tips and Tricks
These diagrams are simple but tell us some important things about the database:
The names of all the tables
Which tables will have a foreign key - when an entity has a ‘many’ relationship against it that means it will have a foreign key in it that links to the primary key of the connected entity
Worked Example
An insurance company's offices have a large number of black and white printers
The company's technicians keep accurate records of the printers in the building, and the quantity of toner cartridges in stock, in a flat file database. An extract of the database is shown:
Printer Model | Location | Notes | Cartridge Code | Quantity in stock | Re-order URL |
LasPrint LP753 | office 3 |
| LP-7XB | 12 | www.megacheapprint.com/toner / LP-7XB |
LasPrint LP710 | office 6 | drum replaced | LP-7XB | 12 | www.megacheapprint.com / toner / LP-7XB |
Zodiac ZN217 | reception |
| Zod17 | 4 | www.zodiaclaserprinting.com / shop / Z17 |
Zodiac ZN217 | conference Room 2 | had to add RAM | Zod17 | 4 | www.megacheapprint.com / toner / LP-7XB |
LasPrint LP753 | office 8 |
| LP-7XB | 12 | www.megacheapprint.com / toner / LP-7XB |
A relational database is created with three tables:
PrinterModel: this stores all the data about each model of printer
PrinterInstance: this stores the data about each individual printer in the building
Cartridge: this stores information about the toner cartridges
Draw an entity-relationship diagram to show the relationships between the three tables
[4]
Answer:
Entities and relationships drawn using standard notation [1]
Cartridge linked to PrinterModel, PrinterModel linked to PrinterInstance with no other links [1]
1:M relationship from Cartridge to Printer Model [1]
1:M relationship from PrinterModel to PrinterInstance [1]
Resolving Many to Many Relationships
Many to many relationships cannot be implemented into an actual database and need to be ‘resolved’
This involves creating a new table known as a ‘link’ table that goes between the entities
This new table links the entities that have the many to many relationships together
Last updated:
You've read 0 of your 5 free revision notes this week
Sign up now. It’s free!
Did this page help you?
