AS · Practice questions · Entity-relationship diagrams

Boxes, lines and crow's feet.

Six original Cambridge-style questions on entity-relationship diagrams, with worked mark points.

Original questions All questions on this page are original work, written in the Cambridge AS & A Level style. They are not from past papers. They test the same concepts and skills the syllabus rewards.

Keep these straight

Ask it both ways.

Degree: one A has how many B, and one B has how many A.
Notation: crow's foot at every "many" end, plain line at every "one" end.
Foreign key: the "one" side's primary key goes into the "many" side.
M:M: add a link entity with a composite key made of both foreign keys.

Apply

[3 marks]

A bus company stores data about DRIVER, BUS and ROUTE. Each bus runs on one route and a route is served by many buses. Each bus has one regular driver, and each driver drives only one bus. State the relationship between (a) ROUTE and BUS, (b) DRIVER and BUS. (c) State which table holds the foreign key for relationship (a).

(a) One-to-many (one route, many buses). ✓
(b) One-to-one. ✓
(c) BUS holds RouteID as a foreign key. ✓

Apply

[4 marks]

A music school stores data about TEACHER and PUPIL. A teacher teaches many pupils and a pupil can have lessons with several teachers. The school also records the instrument taught in each teacher-pupil pairing. Describe how the design should be changed so it can be implemented, naming any new table and its primary key.

TEACHER to PUPIL is many-to-many, which cannot be implemented directly. ✓
Add a link entity, e.g. LESSON(TeacherID, PupilID, Instrument). ✓
Its primary key is the composite TeacherID + PupilID (both foreign keys). ✓
TEACHER 1:M LESSON and PUPIL 1:M LESSON; Instrument is stored in LESSON. ✓

Explain

[2 marks]

Explain why a database designer draws an E-R diagram before creating the tables.

It documents the entities to be stored and how they are related. ✓
It shows where foreign keys are needed and reveals many-to-many relationships that must be resolved before the tables are created. ✓

Apply

[3 marks]

A company database has DEPARTMENT and EMPLOYEE. Each employee works in one department and each department has many employees. Each department is managed by exactly one employee and an employee manages at most one department. Identify the two relationships and their degrees.

Works in: DEPARTMENT to EMPLOYEE is one-to-many. ✓
Manages: DEPARTMENT to EMPLOYEE (manager) is one-to-one. ✓
Two separate relationship lines are drawn between the same two entities. ✓

Analysis

[2 marks]

A student draws ORDER and PRODUCT with a one-to-many relationship, crow's foot at PRODUCT. An order can contain many products and a product can appear on many orders. Explain the error and give the corrected design.

The relationship is many-to-many, not one-to-many: a product appears on many orders. ✓
Correct design: ORDER 1:M ORDERLINE M:1 PRODUCT, with ORDERLINE(OrderID, ProductID, Quantity) and a composite primary key. ✓

Recall

[2 marks]

State what is represented by (a) a rectangle and (b) a line in an E-R diagram.

(a) An entity (which becomes a table). ✓
(b) A relationship between two entities, with its degree shown by the line ends. ✓

Mark this once you have attempted all six and checked your working. It records a Practiced badge on the topic and adds a one-time bonus. Revealing the solutions alone does not count.