Entity Relationship Diagrams (ERDs) visually represent the structure of a database, showing entities and the relationships between them.
One-to-One (1:1)
Definition
In a one-to-one relationship, each record in Table A relates to exactly one record in Table B, and vice versa.
Example
Example: Person and Passport
• Each person has exactly one passport
• Each passport belongs to exactly one person
• Each person has exactly one passport
• Each passport belongs to exactly one person
One-to-Many (1:M)
Definition
In a one-to-many relationship, one record in Table A can relate to many records in Table B, but each record in Table B relates to only one record in Table A.
Example
Example: Doctor and Patients
• One doctor can have MANY patients
• Each patient has only ONE doctor (in this system)
Example: Customer and Orders
• One customer can place MANY orders
• Each order belongs to ONE customer
• One doctor can have MANY patients
• Each patient has only ONE doctor (in this system)
Example: Customer and Orders
• One customer can place MANY orders
• Each order belongs to ONE customer
Many-to-Many (M:M)
Definition
In a many-to-many relationship, records in Table A can relate to many records in Table B, and records in Table B can relate to many records in Table A.
Key Point
Resolving Many-to-Many:
Many-to-many relationships require a JUNCTION TABLE (also called a linking table or bridge table) to implement in a relational database.
Many-to-many relationships require a JUNCTION TABLE (also called a linking table or bridge table) to implement in a relational database.
Worked Example
Worked Example: Students and Courses (M:M)
Problem:
• One student takes MANY courses
• One course has MANY students
• This is many-to-many - can't directly link!
Solution - Junction Table:
Student Table:
Course Table:
Enrolment Table (Junction):
Result:
• Alice (S001) takes Maths AND Physics
• Bob (S002) takes only Maths
• Maths course has Alice AND Bob
• The junction table resolves the M:M relationship
Problem:
• One student takes MANY courses
• One course has MANY students
• This is many-to-many - can't directly link!
Solution - Junction Table:
Student Table:
| StudentID | Name |
|---|---|
| S001 | Alice |
| S002 | Bob |
| CourseID | Title |
|---|---|
| C001 | Maths |
| C002 | Physics |
| StudentID | CourseID | Grade |
|---|---|---|
| S001 | C001 | A |
| S001 | C002 | B |
| S002 | C001 | B |
• Alice (S001) takes Maths AND Physics
• Bob (S002) takes only Maths
• Maths course has Alice AND Bob
• The junction table resolves the M:M relationship
Drawing an Entity Relationship Diagram
Each entity is drawn as a box containing its name, written in the singular. A line joins two entities that are related, and the ends of the line show the type of relationship: a plain end means "one" and a crow's foot, three short lines that fan out, means "many". The diagrams below use a plain line end for "one" and an arrowhead to stand for the crow's foot.
text
One-to-one: Employee ---------- ParkingSpace
One-to-many: Customer ---------< Booking
Many-to-many: Student >--------< CourseWorked Example
Worked Example: A Many-to-Many Relationship in the Diagram
A many-to-many relationship cannot be implemented directly, so the link table is drawn between the two entities. It is at the "many" end of both lines:
• One student has many enrolments
• One course has many enrolments
• Each enrolment belongs to one student and one course
A many-to-many relationship cannot be implemented directly, so the link table is drawn between the two entities. It is at the "many" end of both lines:
text
Student ---------< Enrolment >--------- Course
• One course has many enrolments
• Each enrolment belongs to one student and one course
Exam Tip
Placing the crow's foot: It goes next to the entity there are many of. One customer has many bookings, so the crow's foot is drawn at the Booking end. The table at the "many" end is the one that holds the foreign key.