4.10.1 — Entities, attributes, relationships; ER diagrams; one-to-one, one-to-many, many-to-many
Key Terms
Entities & Attributes
Entity
A thing or object about which we store data. Represented as a table in a relational database. Examples: Student, Course, Teacher, Order, Product
Attribute
A property or characteristic of an entity. Becomes a column in the table. Example: Student has attributes: StudentID, Name, DOB, Email
Primary Key
An attribute that uniquely identifies each record in an entity. Must be unique and not null. Example: StudentID uniquely identifies each student.
Relationships
Relationships Between Entities
One-to-One (1:1) — one record in Entity A is linked to exactly one record in Entity B. Example: one Person has one Passport.
One-to-Many (1:M) — one record in Entity A is linked to many records in Entity B. Example: one Teacher teaches many Students.
Many-to-Many (M:M) — many records in Entity A are linked to many in Entity B. Example: Students enrol on many Courses; each Course has many Students enrolled.
Many-to-many relationships are resolved using a link/junction table
ER Diagram
Entity Relationship (ER) Diagram
An ER diagram shows entities, their attributes, and the relationships between them using standardised notation.
Student
enrols on
⟨
M:M
Course
Crow's foot notation: a single line = "one"; a crow's foot (three lines) = "many"
Entities drawn as rectangles; relationships as diamonds or lines; attributes as ovals
Link Tables
Resolving Many-to-Many Relationships
Many-to-many relationships cannot be directly stored in a relational database. They are resolved by creating a link/junction table that holds foreign keys from both entities.
Student
StudentID Name, DOB
↔
Enrolment
EnrolID StudentID (FK) CourseID (FK)
↔
Course
CourseID Title, Credits
Cardinality
Cardinality & Participation
Cardinality — the maximum number of times an entity can participate in a relationship (1 or many)
Participation — whether participation is mandatory (total) or optional (partial)
Total participation (mandatory): every instance must be in the relationship. E.g. every Order must belong to a Customer.
Partial participation (optional): instances may or may not be in the relationship. E.g. a Customer may or may not have placed an Order.
Real World Example
School Database ER Example
Teacher — Class: One teacher teaches many classes (1:M). The foreign key (TeacherID) goes in the Class table.
Class — Student: Many students are in many classes (M:M). Resolved by a link table: ClassEnrolment(ClassID, StudentID).
Student — Address: One student has one address (1:1). Can be combined into the Student table or kept separate.
Entity Notation
Writing Entity Definitions
Entities are often written in the form: EntityName(PrimaryKey, Attribute1, Attribute2, ForeignKey*)
(a) A library stocks many Books. A Member can borrow many Books, and a Book can be borrowed by many Members. Draw an ER diagram showing these entities and their relationships. [4] (b) Explain why a link table is needed and state its attributes. [3]
[7 marks]
4 marks
(a) Three entities: Member, Book, Loan(link table) [1]; Member—Loan: 1:M [1]; Book—Loan: 1:M [1]; appropriate notation with crow's foot or 1/M labels [1]
3 marks
(b) Many-to-many relationships cannot be directly represented in a relational database [1]; the link table (Loan) stores foreign keys from both entities [1]; e.g. Loan(LoanID, MemberID*, BookID*, BorrowDate, ReturnDate) [1]
Summary
Key Points to Remember
Entity — thing we store data about (table); Attribute — property of entity (column)
Primary key — uniquely identifies each record; must be unique and not null
Relationships: 1:1, 1:M, M:M; M:M resolved with a link/junction table
ER diagram — rectangles for entities, lines with crow's foot notation for cardinality