Skip to main content
SingaporeComputer ScienceSyllabus dot point

How do we plan a database before building it, capturing entities and the relationships between them?

Model a problem domain with an entity-relationship diagram, identifying entities, attributes, relationships and cardinality, and map it to tables

A focused answer to the H2 Computing outcome on ER modelling. Entities, attributes and relationships, one-to-one, one-to-many and many-to-many cardinality, resolving many-to-many with a link table, and mapping an ER model to relational tables.

Generated by Claude Opus 4.88 min answer

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Jump to a section
  1. What this dot point is asking
  2. The answer
  3. Examples in context
  4. Try this

What this dot point is asking

SEAB wants you to model a problem domain with an entity-relationship (ER) diagram - identifying entities, their attributes, the relationships between them and the cardinality of those relationships - and to map the model to relational tables. The core idea is to plan the structure on paper first: entities become tables, attributes become columns, and relationships become foreign-key links.

The answer

Entities, attributes and relationships

  • An entity is a thing the system stores data about (Student, Course, Patient). Each entity becomes a table.
  • An attribute is a property of an entity (Name, DateOfBirth, Price). Each becomes a column. One attribute is chosen as the entity's identifier (its primary key).
  • A relationship is an association between entities (a student enrols in a course). Relationships become links between tables through foreign keys.

Cardinality

Cardinality says how many of one entity relate to how many of another:

  • One-to-one (1:1) - each row on one side relates to at most one on the other (a person and their unique passport).
  • One-to-many (1:N) - one row relates to many on the other side (one class, many students). This is the most common.
  • Many-to-many (M:N) - many on each side (students and courses).

Mapping cardinality to tables

The mapping rules:

  • 1:N - put a foreign key on the many side pointing to the one side. (Student gets ClassID.)
  • M:N - cannot be done with a single foreign key; introduce a link (junction) table whose rows pair the two entities, with a composite key of both foreign keys. This splits the M:N into two 1:N relationships.
  • 1:1 - put a foreign key (often unique) on either side.
Student ---< Enrolment >--- Course      (M:N resolved by Enrolment)
Class ---< Student                       (1:N, FK on Student)

Why model before building

An ER diagram catches design problems - missing entities, wrong cardinality, attributes on the wrong entity - before any SQL is written. It is the bridge between understanding the problem and creating normalised tables.

Examples in context

Example 1. A cinema booking system. Customers, screenings and seats are entities. A booking links a customer to a specific seat at a specific screening. Because one customer books many seats and one screening has many seats, a Booking link table resolves the many-to-many between customers and screenings.

Example 2. A project tracker. Employees and projects have a many-to-many relationship (an employee works on several projects, a project has several employees). The ER model introduces an Assignment junction table, which also conveniently holds attributes of the pairing itself, such as the hours allocated.

Try this

Q1. State the cardinality between a Country and its Cities (each city is in one country). [1 mark]

  • Cue. One-to-many: one country has many cities, each city belongs to one country.

Q2. How is a many-to-many relationship implemented in a relational database? [2 marks]

  • Cue. With a link (junction) table whose rows pair the two entities, using a composite key of both foreign keys, splitting M:N into two one-to-many links.

Q3. In a one-to-many relationship between Author and Book, where does the foreign key belong? [1 mark]

  • Cue. On Book (the many side), holding the AuthorID of its author.

Exam-style practice questions

Practice questions written in the style of SEAB exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

Original6 marksA school database records that a Student can enrol in many Courses and a Course can have many Students. (a) State the cardinality of the relationship between Student and Course. (b) Explain why this relationship cannot be implemented directly with a single foreign key. (c) Describe how to resolve it, giving the resulting tables and keys.
Show worked answer →

(a) The relationship is many-to-many: one student takes many courses and one course has many students.

(b) A single foreign key can only point to one row, so it can model "many on one side, one on the other". A many-to-many relationship would require an unbounded list of foreign keys in each row, which a relational table cannot hold (it would break first normal form).

(c) Resolve it with a link (junction) table whose rows pair one student with one course. Each pairing becomes a row, turning one many-to-many relationship into two one-to-many relationships:

Student(StudentID PK, Name)
Course(CourseID PK, Title)
Enrolment(StudentID FK, CourseID FK)   [composite key: StudentID, CourseID]

Markers reward identifying many-to-many, the reason a single foreign key cannot represent it, and a link table with a composite key of the two foreign keys.

Original4 marksUsing ER terminology, distinguish between an entity, an attribute and a relationship, and give an example of each for a hospital that records patients and their appointments.
Show worked answer →

An entity is a thing the database stores data about, which becomes a table. Example: Patient (and Appointment).

An attribute is a property of an entity, which becomes a column. Example: a patient's DateOfBirth, or an appointment's Time.

A relationship is an association between entities, captured by linking their tables. Example: a patient has appointments - a one-to-many relationship between Patient and Appointment, implemented by a PatientID foreign key in Appointment.

Markers reward correct definitions and a sensible hospital example of each, ideally noting that entities map to tables, attributes to columns, and relationships to foreign-key links.

Related dot points