Entity-Relationship Diagrams

An Entity-Relationship (E-R) diagram is a visual model of the entities in a system and the relationships between them. It is produced during the design stage of database development — before any tables are created — so that the structure can be agreed, checked against the requirements, and refined on paper, where mistakes are cheap to fix. The E-R diagram is the bridge between a written description of a problem ("a school enrols students on courses…") and a concrete relational schema of tables, keys and foreign keys. Mastering the translation in both directions — words to diagram, and diagram to relations — is one of the most reliably examined skills in the database unit.

---

Spec Mapping

This lesson covers the AQA A-Level Computer Science (7517) Fundamentals of databases requirement to produce and interpret entity-relationship modelling: identifying entities, attributes and relationships; expressing the degree of a relationship as one-to-one (1:1), one-to-many (1:M) or many-to-many (M:N); and resolving a many-to-many relationship by introducing a link (junction) entity. It connects forward to normalisation (the diagram's entities become normalised relations) and to SQL (the relationships become the join conditions you write later).

---

Core Components

1. Entities

An entity is a real-world object or event about which we store data — a class of things, not a single instance. In a diagram, an entity is a rectangle labelled with a singular noun.

Examples: Student, Course, Teacher, Order, Product. A single occurrence (e.g. the student "Alice Chen") is an entity instance or entity occurrence; the rectangle represents the whole set.

2. Attributes

Attributes are the data items recorded about an entity. In Chen notation they are drawn as ovals connected to the entity; in Crow's Foot notation (the form used in industry and expected at A-Level) they are simply listed inside the rectangle. The primary key attribute is conventionally underlined.

Example for Student: StudentID, FirstName, Surname, DateOfBirth.

3. Relationships

A relationship is a meaningful association between entities, usually named with a verb. In Crow's Foot notation it is a line between two entities, with symbols at each end showing cardinality. In Chen notation it is a diamond.

Examples: Student enrols on Course; Teacher teaches Course; Customer places Order.

---

Degree (Cardinality) of Relationships

The degree of a relationship describes how many instances of one entity may be associated with instances of another. There are three degrees:

Degree	Meaning	Example
1:1	One instance of A relates to at most one of B, and vice versa	Student ↔ LockerAssignment
1:M	One instance of A relates to many of B; each B relates to one A	Teacher → Courses
M:N	Many instances of A relate to many of B	Student ↔ Course

To determine the degree, ask the question in both directions:

• "Can one Teacher teach many Courses?" — Yes.
• "Can one Course be taught by many Teachers?" — In this school, no, just one.

One "many" and one "one" gives a 1:M relationship. If both answers are "many", the relationship is M:N.

Crow's Foot Symbols

The symbols are placed at the end of the line next to the entity they describe:

Symbol	Meaning
Single bar ( | )	"one" (mandatory)
Crow's foot (a three-pronged fork)	"many"
Circle ( O )	"zero" — optional participation
Bar + crow's foot	"one or many"
Circle + crow's foot	"zero or many"

Example: one Department employs many Teachers, and each Teacher belongs to exactly one Department.

erDiagram
    DEPARTMENT ||--|{ TEACHER : "employs"

Reading the diagram: the || end (at Department) means "exactly one department"; the |{ end (at Teacher) means "one or many teachers". So one Department has many Teachers, and each Teacher belongs to exactly one Department.

---

Drawing E-R Diagrams — Step by Step

A reliable five-step method turns any written scenario into a correct diagram.

Step 1 — Identify the entities (look for nouns)

"A library lends books to members. Each book is written by an author."

Candidate entities: Book, Member, Author. ("Library" here is the whole system, not an entity we store rows about — be careful not to over-create entities.)

Step 2 — Identify the relationships (look for verbs)

• Member borrows Book
• Author writes Book

Step 3 — Determine the degree of each relationship

• One Author writes many Books, and (in this model) each Book has one author → 1:M.
• One Member borrows many Books over time, and one Book may be borrowed by many Members over time → M:N.

Step 4 — List the attributes (mark the primary key)

• Book: ISBN, Title, Genre, PublicationYear, AuthorID (FK)
• Member: MemberID, Name, Email, JoinDate
• Author: AuthorID, Name, Nationality

Step 5 — Resolve every many-to-many relationship

A relational database cannot store an M:N relationship directly, so each one is replaced by a link (junction) entity that sits between the two originals and has a 1:M relationship to each. The Member ↔ Book relationship becomes:

erDiagram
    MEMBER ||--|{ LOAN : "makes"
    BOOK ||--|{ LOAN : "is recorded in"

The Loan link entity holds: LoanID (or the composite key MemberID + ISBN + LoanDate), MemberID (FK), ISBN (FK), LoanDate, ReturnDate. Crucially, the M:N has become two 1:M relationships, each of which a relational database can represent with a single foreign key.

---

Chen Notation vs. Crow's Foot Notation

Feature	Chen notation	Crow's Foot notation
Entities	Rectangles	Rectangles
Attributes	Ovals connected to the entity	Listed inside the rectangle
Relationships	Diamonds on the line	Symbols at the line ends
Degree	Numbers (1, M, N) written on the lines	Crow's-foot, bar and circle symbols
Typical use	Academic / theoretical	Industry standard; expected at A-Level

You should be fluent in Crow's Foot and able to read Chen. The underlying meaning is identical; only the drawing convention differs.

---

Types of Attribute

Examiners reward candidates who can classify the attributes they list, because the classification affects how the entity is later turned into a relation.

Attribute type	Meaning	Example
Simple (atomic)	Cannot be sensibly broken down further	DateOfBirth
Composite	Made of several meaningful parts	Address (split into Street, Town, Postcode)
Key	The primary-key attribute (underlined)	StudentID
Multi-valued	Could hold several values for one instance	TelephoneNumbers
Derived	Calculated from other data, not stored	Age (derived from DateOfBirth)

Two of these matter especially for database design:

• Composite attributes are usually split into their parts so that each part is atomic — this is exactly what First Normal Form demands, so an E-R diagram that already separates Street, Town and Postcode is ahead of the game.
• Multi-valued attributes signal a hidden one-to-many relationship. If a Student could have several TelephoneNumbers, that "attribute" is really a separate weak entity (or at least a separate relation) with its own one-to-many link back to Student — you should not leave a multi-valued attribute in the final design, for the same reason you cannot leave a repeating group in 1NF.
• Derived attributes (such as Age) are typically not stored at all, because storing a value that can be computed introduces redundancy and the risk of it becoming stale. They are sometimes shown on a Chen diagram with a dashed oval.

---

Translating an E-R Diagram into Relations

The E-R diagram is a design; the database is built from relations. There is a reliable mechanical procedure for turning one into the other, and exam questions frequently ask for the resulting relations (in the notation Name(PrimaryKey, attribute, …)).

1. Each strong entity becomes a relation. Its attributes become the relation's attributes; its key attribute becomes the primary key.
2. For a 1:M relationship, post the key of the "one" side into the "many" side as a foreign key. For example, in Department (one) to Teacher (many), DeptID is placed inside Teacher.
3. For an M:N relationship, create a new relation (the link entity). Its primary key is the composite of the two parent primary keys, each of which is also a foreign key; any attributes belonging to the relationship itself (e.g. Quantity, Grade, LoanDate) live here.
4. For a 1:1 relationship, post the key of one side into the other (choose the side that makes participation mandatory simplest), or merge the two entities if they always coexist.

Worked translation for Department/Teacher/Course (a Department employs many Teachers; a Teacher teaches many Courses; a Course is taught by one Teacher):

• Department(DeptID, DeptName)
• Teacher(TeacherID, Name, DeptID*) — *DeptID is a foreign key to Department (rule 2)
• Course(CourseID, CourseName, TeacherID*) — *TeacherID is a foreign key to Teacher (rule 2)

Every relationship line in the diagram has become a foreign key in a relation. This is the single most important idea to carry forward: relationships are implemented as foreign keys, and those same foreign keys become the ON conditions of the SQL joins you write later.

---

Worked Example — An Online Shop

Scenario: An online shop sells products. Customers place orders. Each order may contain several products, and each product may appear on many orders. Every product belongs to exactly one category.

Step 1 — Entities: Customer, Order, Product, Category.

Step 2/3 — Relationships and degree:

• Customer places Order — 1:M (one customer, many orders).
• Order contains Product — M:N (an order has many products; a product appears on many orders) → needs a link entity, OrderLine.
• Category contains Product — 1:M (one category, many products).

Step 4/5 — Resolved E-R diagram (Crow's Foot):

erDiagram
    CUSTOMER ||--|{ ORDER : "places"
    ORDER ||--|{ ORDER_LINE : "contains"
    PRODUCT ||--|{ ORDER_LINE : "appears on"
    CATEGORY ||--|{ PRODUCT : "classifies"

Attributes (primary keys underlined, foreign keys marked FK):

Entity	Attributes
Customer	CustomerID, Name, Email, Address
Order	OrderID, CustomerID (FK), OrderDate
OrderLine	OrderID + ProductID (composite PK), Quantity, UnitPrice
Product	ProductID, ProductName, Price, CategoryID (FK)
Category	CategoryID, CategoryName

Note how the single M:N (Order ↔ Product) became the OrderLine entity with a composite primary key (OrderID, ProductID) and two foreign keys. This is the same pattern as Enrolment, Booking and Loan — once you recognise it, every M:N resolution looks the same.

Translating this diagram to relations

Applying the four translation rules gives the full schema:

• Customer(CustomerID, Name, Email, Address)
• Order(OrderID, OrderDate, CustomerID*) — *FK to Customer (1:M rule)
• OrderLine(OrderID, ProductID, Quantity, UnitPrice) — link entity for the M:N; both columns are FKs
• Product(ProductID, ProductName, Price, CategoryID*) — *FK to Category (1:M rule)
• Category(CategoryID, CategoryName)

This schema is already in Third Normal Form, which illustrates an important point: a carefully constructed E-R diagram usually produces a normalised design "for free", because separating entities is exactly what normalisation does formally.

---

A Second Worked Example — A Hospital

Practising a fresh scenario embeds the method.

Scenario: A hospital records its wards, the patients admitted to them, and the consultants who treat patients. A ward holds many patients; a patient is on exactly one ward at a time. A patient may be treated by several consultants, and a consultant treats many patients. Each treatment has a date and notes.

Step 1 — Entities: Ward, Patient, Consultant. (Treatment will emerge as a link entity.)

Step 2/3 — Relationships and degree:

• Ward holds Patient — 1:M (one ward, many patients; each patient on one ward).
• Patient is treated by Consultant — M:N (a patient sees several consultants; a consultant sees many patients).

Step 4 — Attributes:

• Ward: WardID, WardName, Capacity
• Patient: PatientID, Name, DateOfBirth, WardID (FK)
• Consultant: ConsultantID, Name, Specialism

Step 5 — Resolve the M:N with a Treatment link entity (which conveniently holds the treatment's own attributes, Date and Notes):

erDiagram
    WARD ||--|{ PATIENT : "holds"
    PATIENT ||--|{ TREATMENT : "receives"
    CONSULTANT ||--|{ TREATMENT : "delivers"

Resulting relations:

• Ward(WardID, WardName, Capacity)
• Patient(PatientID, Name, DateOfBirth, WardID*)
• Consultant(ConsultantID, Name, Specialism)
• Treatment(PatientID, ConsultantID, TreatmentDate, Notes) — composite key; PatientID and ConsultantID are FKs

If a patient could be treated by the same consultant on more than one date, the composite key would need to extend to (PatientID, ConsultantID, TreatmentDate), or — more robustly — Treatment would gain a surrogate TreatmentID primary key. Spotting this is exactly the kind of design judgement that distinguishes a top-band answer.

---

Common Errors When Drawing Diagrams

1. Leaving a many-to-many relationship unresolved. Always introduce a link entity; an M:N in a final design is a mistake.
2. Omitting cardinality symbols. A relationship line with no symbols at its ends conveys no degree and earns no marks.
3. Not underlining (or otherwise marking) the primary key in each entity's attribute list.
4. Reading cardinality only one way. Decide the degree by asking the question in both directions.
5. Inventing entities for the system itself. "The library" or "the shop" is the whole system, not an entity you store rows about.
6. Treating a multi-valued attribute as a single attribute instead of recognising the hidden one-to-many relationship it implies.

---

Synoptic Links