entity-relationship-diagrams

课 Progress

0% Complete

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

What is an entity relationship diagram?

In A Level Computer Science, 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

Computer-Science-A-level-Ocr