The shape of the lesson
By the end of the lesson, learners can
- Three kinds of relationship: To find the degree of a relationship, ask the question both ways round: "one A has how many B?" and "one B has how many A?".
- Drawing the diagram: The top of the lab's relational database tab shows an E-R diagram for STUDENT, MEMBERSHIP and CLUB.
- Resolving many-to-many: A M:M relationship cannot be stored with a single foreign key: a student would need a field holding a list of courses, which a relational table does not allow.
- 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.
One picture every idea returns to
An entity-relationship (E-R) diagram documents a database design. Each entity is a box, and each relationship is a line. The degree of a relationship is one-to-one (1:1), one-to-many (1:M) or many-to-many (M:M). In crow's foot notation the forked end marks the many side. A 1:M relationship is implemented by putting the primary key of the "one" table into the "many" table as a foreign key. A M:M relationship cannot be implemented directly: it is resolved with a link entity that has a 1:M relationship with each original entity.
Forty-five minutes, phase by phase
| Time | Phase | What happens in the room | Grouping |
|---|---|---|---|
| 0 to 5 min | Hook: predict | Open with the question in the title, Draw the links first.. Before anyone creates a table, a database designer sketches the entities and how they are related. Put the figure on the board: it is the one picture every idea in the lesson returns to. | Whole class, then pairs |
| 5 to 13 min | Teach and check: Three kinds of relationship | To find the degree of a relationship, ask the question both ways round: "one A has how many B?" and "one B has how many A?". Model the worked example Find the degree on the board, one line at a time. Your turn: A doctor treats many patients and a patient can be treated by many doctors. State the relationship between DOCTOR and PATIENT. | Whole class, then pairs |
| 13 to 21 min | Teach and check: Drawing the diagram | The top of the lab's relational database tab shows an E-R diagram for STUDENT, MEMBERSHIP and CLUB. Project tab 4 of the lab (E-R diagram and linked tables) and let a student drive it while the class predicts. Model the worked example The library on the board, one line at a time. | Pairs |
| 21 to 30 min | Teach and check: Resolving many-to-many | A M:M relationship cannot be stored with a single foreign key: a student would need a field holding a list of courses, which a relational table does not allow. Model the worked example Films and actors on the board, one line at a time. | Mix-Pair-Share |
| 30 to 36 min | Check | The four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong. | Individual, then whole class |
| 36 to 43 min | Plenary | The exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording. | Individual, random call |
| 43 to 45 min | Exit and homework | Set the worksheet below (6 questions, 16 marks). Learners self-assess against the objectives before they leave. | Individual |
Protect the parts that carry the learning
The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.
Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.
Cut first if the build overruns: the last check in Resolving many-to-many, never the check or the plenary.
In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.
Mix-Pair-Share
Students mix around the room until you call pair; they pair with the nearest classmate, discuss the prompt, then mix again. Three prompts of rising difficulty, three different partners.
The full structure, with its variations and the other twelve, is on the cooperative-learning page.
What to head off, and how
| Trap learners fall into | Teaching move that pre-empts it |
|---|---|
| Always ask the question in both directions. "A customer places many orders" on its own does not tell you whether the relationship is 1:M or M:M. | Return to Three kinds of relationship: ask a pair to state the correct version and show it on the lab before moving on. |
| The crow's feet of the new relationships both point at the link entity. | Drawing them pointing at ACTOR or FILM loses the marks for the resolved diagram. |
Entity-relationship diagrams: worksheet
6 questions, 16 marks. Written in the style of the papers; no past paper question is reproduced.
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 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.
Explain why a database designer draws an E-R diagram before creating the tables.
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.
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.
State what is represented by (a) a rectangle and (b) a line in an E-R diagram.
Answer key and marking notes
- (a) One-to-many (one route, many buses).
- (b) One-to-one.
- (c) BUS holds RouteID as a foreign 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.
- 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.
- 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.
- 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.
- (a) An entity (which becomes a table).
- (b) A relationship between two entities, with its degree shown by the line ends.
6 multiple-choice questions on this lesson
From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.
A small part of an E-R diagram is shown. Which statement describes the relationship?
- AOne team has many players, and each player is in one team
- BOne player plays for many teams, and each team has one player
- CEach team has one player, and each player is in one team
- DMany teams have many players, so a link entity is needed
In a publisher's database, each book is written by one author and an author can write many books. How is this relationship implemented?
- AAuthorID is stored in BOOK as a foreign key
- BBookID is stored in AUTHOR as a foreign key
- CA link table holds AuthorID and BookID
- DAUTHOR and BOOK are merged into one table
A pharmacy database stores PATIENT and MEDICINE. A patient can be prescribed several medicines, and each medicine is prescribed to many patients. What is the degree of the relationship?
- AMany-to-many (M:M)
- BOne-to-many (1:M)
- CMany-to-one (M:1)
- DOne-to-one (1:1)
The many-to-many relationship between STUDENT and COURSE is resolved with a link entity ENROLMENT. What is the most suitable primary key for ENROLMENT?
- AA composite key made of the foreign keys StudentID and CourseCode
- BStudentID on its own, because it identifies the student
- CCourseCode on its own, because it identifies the course
- DThe student's surname combined with the course title
A wholesaler stores SUPPLIER, PRODUCT and CATEGORY. A supplier supplies many products and a product can come from many suppliers. Each product is in one category and a category holds many products. How many tables are needed to implement this design?
- A4
- B3
- C5
- D2
An orchestra database resolves MUSICIAN M:M CONCERT with a link entity PERFORMANCE. The designer also wants to store the fee paid to each musician for each concert. Where should Fee be stored?
- AIn PERFORMANCE, because the fee depends on the musician and the concert together
- BIn MUSICIAN, because the fee is paid to the musician
- CIn CONCERT, because the fee is part of the cost of the concert
- DIn a new FEE table linked one-to-one to MUSICIAN
Quiz key
The crow's foot is at the PLAYER end, so PLAYER is the "many" side: one team has many players and each player belongs to one team (1:M). Reading it the other way round puts the many at the wrong end. A plain line at both ends would be 1:1, and M:M would need crow's feet at both ends.
AUTHOR to BOOK is one-to-many, so the primary key of the "one" side (AuthorID) goes into the "many" side (BOOK). Putting BookID in AUTHOR would need a list of books in one field. A link table is only needed for a many-to-many relationship, and merging the tables would repeat the author's details for every book.
The answer is "many" in both directions, so the relationship is many-to-many. 1:M and M:1 each describe a "one" answer in one direction, which would only be right if a medicine were prescribed to a single patient or a patient had a single medicine. 1:1 is "one" in both directions.
Neither StudentID nor CourseCode is unique in ENROLMENT, since each appears in many records; the pair is unique, so the two foreign keys together form a composite primary key. A surname and title are not keys of the original tables and are not guaranteed unique.
SUPPLIER to PRODUCT is M:M, so it needs a link table (for example SUPPLY with SupplierID and ProductID); CATEGORY to PRODUCT is 1:M, done by putting CategoryID in PRODUCT. That gives SUPPLIER, PRODUCT, CATEGORY and SUPPLY: 4 tables. 3 forgets the link table, 5 wrongly adds a link table for the 1:M relationship, and 2 merges entities that must stay separate.
A musician is paid a different fee for each concert, so the fee belongs to the pairing and is stored in the link entity PERFORMANCE alongside its composite key. In MUSICIAN there is room for only one fee per musician, and in CONCERT only one per concert. A FEE table linked 1:1 to MUSICIAN still allows just one fee per musician.
What to project
The student lesson embeds these tabs. Open them full screen on the projector; every slider has a drag handle on the stage.
Slides for this lesson
A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.