Use an entity-relationship (E-R) diagram to document a database design

Cambridge International AS & A Level Computer Science (9618) – 8.1 Database Concepts: Using an Entity‑Relationship (E‑R) Diagram to Document a Database Design

1. Action‑oriented review checklist

Use this table to audit the lecture notes against the 2026 Cambridge AS & A Level Computer Science (9618) syllabus. Tick each item as you confirm it is covered; if a point is missing, the “Quick fix” column suggests a concise addition.

2. Syllabus mapping & assessment objectives

3. Why use an E‑R diagram?

• Visual, high‑level view: Shows the data requirements before any code is written.
• Common terminology: Forces the team to agree on entity and attribute names with stakeholders.
• Explicit relationships: Makes cardinality (1‑1, 1‑N, N‑M) and participation (total vs. optional) obvious.
• Blueprint for implementation: Directly maps to tables, primary keys, foreign keys and integrity constraints.
• Facilitates communication: Developers, DBAs and end‑users can discuss the design using a single diagram, reducing costly redesigns.

4. Core components of an E‑R diagram

1. Entity – drawn as a rectangle; represents a real‑world object of interest (e.g. Student).
2. Attribute – oval attached to an entity.
   • Key attribute (primary key) – underlined.
   • Derived attribute – dashed oval.
   • Composite or multivalued attributes – shown with sub‑ovals or double lines (optional for AS/A‑Level).
3. Relationship – diamond (or a labelled line) that connects two or more entities (e.g. Enrols).
4. Cardinality & participation – placed at each end of the relationship line as a pair (min, max).
   • (1,1) or (0,1) for “exactly one” / “zero or one”.
   • (1,N) or (0,N) for “one or many” / “zero or many”.
   • Optional participation = a “0” in the pair; total participation = a “1”.

5. Cardinality notation – quick reference

6. Systematic steps to construct an E‑R diagram

1. Gather requirements – read the specification, interview users and list every real‑world object that must be stored.
2. Identify entities – convert each noun (or noun phrase) that has an independent existence into a rectangle.
3. Determine attributes
   • Key attribute(s) – underline them.
   • Simple (atomic) attributes.
   • Composite or multivalued attributes only if the syllabus explicitly requires them.
4. Define relationships – connect related entities with a diamond (or a labelled line) and give the relationship a verb phrase.
5. Specify cardinality & participation – add the (min,max) pair at each end of the relationship line.
6. Introduce associative entities – replace any many‑to‑many relationship with a new entity that stores the foreign keys of the participating entities (plus any attributes that belong to the relationship itself, e.g. Grade, Date).
7. Validate the diagram
   • Every entity has a primary key.
   • No redundant entities or attributes.
   • Logical consistency (e.g., a student cannot enrol in a non‑existent course).

7. Example scenario – University Course Management

Requirement (excerpt): The system must store details of students, courses and the enrolment of each student in one or more courses. For each enrolment the grade obtained is recorded.

7.1 Identified entities & key attributes

• Student – StudentID, Name, DOB, Email
• Course – CourseCode, Title, Credits
• Enrolment – EnrolmentID, Grade, StudentID (FK), CourseCode (FK)

7.2 Relationships & cardinalities

• Student enrols in Course – many‑to‑many, resolved by the associative entity Enrolment.
  • Student (1,N) ↔ Enrolment (N,1)
  • Course (1,N) ↔ Enrolment (N,1)

7.3 Textual representation of the E‑R diagram

[Student]───(1,N)───⟨Enrols⟩───(N,1)───[Enrolment]───(N,1)───⟨Enrols⟩───(1,N)───[Course]

In a graphical tool the diagram would show three rectangles (Student, Course, Enrolment). The Enrolment rectangle contains its own attributes (EnrolmentID, Grade) and two diamonds labelled “Enrols”. Cardinalities (1,N) and (N,1) appear at the appropriate line ends.

8. Translating the E‑R diagram into a relational schema

9. Essential DBMS features (syllabus 8.2)

• Backup & recovery – full, incremental and point‑in‑time restore.
• Indexing – B‑tree or hash indexes to accelerate searches on key attributes.
• Concurrency control – locking (shared/exclusive), transaction isolation levels, dead‑lock detection.
• Integrity constraints – PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
• Security – user authentication, role‑based privileges, encryption of data at rest.
• Views – virtual tables defined by SELECT statements; useful for security and simplifying complex queries.

10. Basic SQL – DDL & DML (syllabus 8.3)

All examples assume the three tables defined in Section 8.

10.1 Data Definition Language (DDL)

-- create the three tables
CREATE TABLE Student (
    StudentID   CHAR(8)   PRIMARY KEY,
    Name        VARCHAR(50) NOT NULL,
    DOB         DATE,
    Email       VARCHAR(100)
);

CREATE TABLE Course (
    CourseCode  CHAR(6)   PRIMARY KEY,
    Title       VARCHAR(100) NOT NULL,
    Credits     INT CHECK (Credits > 0)
);

CREATE TABLE Enrolment (
    EnrolmentID CHAR(10) PRIMARY KEY,
    StudentID   CHAR(8)  NOT NULL,
    CourseCode  CHAR(6)  NOT NULL,
    Grade       CHAR(2),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode)
);

10.2 Data Manipulation Language (DML)

-- insert a new student
INSERT INTO Student (StudentID, Name, DOB, Email)
VALUES ('S1234567', 'Alice Brown', '2002-04-15', 'alice@example.com');

-- enrol Alice in CS101
INSERT INTO Enrolment (EnrolmentID, StudentID, CourseCode, Grade)
VALUES ('E0001', 'S1234567', 'CS101', NULL);

-- list all courses a student is taking (including grade)
SELECT c.CourseCode, c.Title, e.Grade
FROM Enrolment e
JOIN Course c ON e.CourseCode = c.CourseCode
WHERE e.StudentID = 'S1234567';

-- update a grade
UPDATE Enrolment
SET Grade = 'A'
WHERE EnrolmentID = 'E0001';

-- delete a course only if it has no enrolments
DELETE FROM Course
WHERE CourseCode = 'CS101';

11. Common pitfalls to avoid

• Missing primary key – leads to duplicate rows and ambiguous updates.
• Unresolved many‑to‑many relationships – a relational DBMS cannot store them directly; an associative entity is mandatory.
• Over‑normalising – creates unnecessary tables, increases join complexity and may degrade performance.
• Incorrect participation – using (1,1) instead of (0,1) can forbid legitimate null relationships.
• Forgotten integrity constraints – without FOREIGN KEYs the database cannot enforce referential integrity.

12. Summary

An E‑R diagram is the first, crucial step in designing a relational database. By systematically identifying entities, attributes, relationships, cardinalities and participation, and by resolving many‑to‑many relationships with associative entities, students produce a logical model that maps directly to tables, primary keys, foreign keys and integrity constraints. Coupled with an understanding of essential DBMS features and basic SQL syntax, this knowledge satisfies the full Cambridge 9618 database syllabus.

13. Practice questions

1. Identify the entities, key attributes and relationships for a library system that tracks Books, Members and Loans.
2. Draw a complete E‑R diagram for the scenario in Question 1, showing cardinalities and any associative entity required.
3. Translate your diagram into a set of relational tables. List primary keys, foreign keys and any integrity constraints.
4. Explain why a many‑to‑many relationship must be replaced by an associative entity before a relational schema can be created.
5. Write SQL statements to:
   • Create the tables you defined in (3);
   • Insert a new loan record; and
   • List all overdue books (assume a DueDate attribute).
6. Discuss two DBMS features (e.g., indexing, concurrency control) and how they improve the performance or reliability of the library system.