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
What is an entity relationship diagram?
- An entity relationship diagram (ERDs) is a diagram that represents 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
