Before anyone creates a table, a database designer sketches the entities and how they are related. An entity-relationship diagram shows at a glance which tables are needed, which way the foreign keys go, and where a many-to-many relationship has to be broken up.
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.
Fig. 1: one-to-one, one-to-many and many-to-many relationships
Section 01
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?". The two answers together give the degree.
Degree
Ask both ways
Example
One-to-one (1:1)
One school has one headteacher; one headteacher leads one school.
SCHOOL and HEADTEACHER
One-to-many (1:M)
One customer places many orders; one order is placed by one customer.
CUSTOMER and ORDER
Many-to-many (M:M)
One student takes many courses; one course has many students.
STUDENT and COURSE
Worked example Find the degree
In a hospital, each patient is placed in one ward and a ward holds many patients. State the relationship between WARD and PATIENT.
One ward has how many patients? Many.
One patient is in how many wards? One.
So the relationship is one-to-many, with the crow's foot at the PATIENT end.
Answer: WARD to PATIENT is one-to-many (1:M)
Your turn
A doctor treats many patients and a patient can be treated by many doctors. State the relationship between DOCTOR and PATIENT.
Many-to-many (M:M): the answer is "many" in both directions
Examiner trap
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.
Section 02
Drawing the diagram.
The top of the lab's relational database tab shows an E-R diagram for STUDENT, MEMBERSHIP and CLUB. Compare it with the tables underneath: the crow's feet point at MEMBERSHIP, the table that holds the foreign keys.
Entities: one rectangle per entity, named in the singular (MEMBER, not MEMBERS).
Relationships: a line between two entities that are directly related; a crow's foot at each "many" end and a plain end at each "one" end.
Foreign keys follow the crow's foot: in a 1:M relationship, the primary key of the "one" entity goes into the "many" entity as a foreign key.
Worked example The library
A school library stores data about MEMBER, BOOK and LOAN. A member can make many loans; each loan is of one book by one member; a book can be lent many times. Describe the E-R diagram.
MEMBER to LOAN: one member has many loans, one loan has one member: 1:M, crow's foot at LOAN.
BOOK to LOAN: one book has many loans over time, one loan is for one book: 1:M, crow's foot at LOAN.
MEMBER and BOOK are not linked directly; they are linked through LOAN, which holds the foreign keys MemberID and BookID.
Answer: MEMBER 1:M LOAN and BOOK 1:M LOAN
Stage 1 · Learn
Check what the sim just showed you
Four quick checks. Each correct answer earns XP and records this skill on your profile.
Quick check+10 XP
In crow's foot notation, what does the forked end of a relationship line show?
Quick check+10 XP
One country has one capital city and one capital city belongs to one country. What is the relationship?
Quick check+10 XP
In the lab's E-R diagram, which table is at the "many" end of both relationships?
Quick check+10 XP
Why must a many-to-many relationship be resolved?
Section 03
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. The fix is a link entity in the middle.
Fig. 2: resolving STUDENT M:M COURSE with the link entity ENROLMENT
Link entity: ENROLMENT holds one record for each student on each course.
Its key: the composite primary key StudentID + CourseCode, made of two foreign keys. Data about the pairing, such as Grade, also belongs here.
Result: STUDENT 1:M ENROLMENT and COURSE 1:M ENROLMENT, both of which can be implemented with foreign keys.
Worked example Films and actors
An actor appears in many films and a film has many actors. The designer also wants to store the name of the character each actor plays. Resolve the relationship.
ACTOR to FILM is M:M, so add a link entity, for example CASTING.
CASTING(ActorID, FilmID, CharacterName): ActorID and FilmID are foreign keys and together form the composite primary key.
CharacterName depends on the actor and the film together, so it is stored in CASTING.
Answer: ACTOR 1:M CASTING M:1 FILM
Examiner trap
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.
Stage 2 · Exam
Exam-style questions
Unlocks once the four checks above are done. Worth more XP, written to AS Paper 1 and Paper 2 standard.
Finish the four checks above to unlock the exam questions
Exam style+20 XP
A club has many members and each member belongs to exactly one club. Where does the foreign key go?
Exam style+20 XP
STUDENT and COURSE have a M:M relationship, resolved with ENROLMENT. What is a suitable primary key for ENROLMENT?
Exam style+20 XP
After resolving a M:M relationship between ACTOR and FILM with CASTING, what are the relationships?
Exam style+20 XP
Each car has one registration document and each document is for one car. What does the line between CAR and DOCUMENT look like?
Skill unlocked
Entity-relationship diagrams, mastered.
This skill is now recorded on your profile. Keep the chain going.