Relational Model, ER Diagrams, and Normalisation

You can read this note directly if you know what a table is. The main idea is that a relational database stores related facts in tables and uses keys to connect them.

This note answers three beginner questions:

1. How should we separate facts into suitable tables?
2. How do keys identify records and connect tables?
3. How do ER diagrams and normalisation help us design a better database?

The Beginner Problem Relational Design Solves

A spreadsheet can store data, but it does not automatically show which facts belong together.

Example fact types:

Fact	Better home
Aisha is a student	Student table
class 24S01 has tutor Ms Lim	Class table
Aisha borrowed Data Trails	Loan table

Relational design asks: what are the separate entities, and how are they connected?

A weak design stores many different kinds of facts in one table. A better relational design stores each kind of fact in a suitable table, then uses keys to reconnect the facts when needed.

Table, Record, Field, Attribute

Term	Beginner meaning
table	a grid of related data
record	one row in the table
field	one column in the table
attribute	a property represented by a field

Example Student table:

student_id	name	class_id
101	Aisha	24S01
102	Bo	24S02

student_id, name, and class_id are fields. The first row is one record. The attribute name describes a property of each student.

A table is not automatically a good database design. The fields in a table should describe the same kind of entity or relationship.

Entities and Relationships

An entity is a thing about which data is stored. In a school library example, possible entities include:

Entity	Possible attributes
Student	student_id, name, class_id
Class	class_id, class_name, tutor
Book	book_id, title, author
Loan	loan_id, student_id, book_id, loan_date

A relationship describes how entities are connected.

Examples:

A Class has Students.
A Student borrows Books.
A Book appears in Loans.

The Loan table is useful because borrowing is not just a property of a student or a book. It is a relationship between a student and a book at a particular time.

Keys

A key helps identify or link records.

Key type	Meaning	Example
primary key	uniquely identifies one record in a table	student_id in Student
foreign key	references a primary key in another table	class_id in Student
composite key	primary key made from more than one field	(student_id, book_id) in Loan
candidate key	any minimal field or field combination that can uniquely identify a record	student_id or a unique school email in Student
secondary key	a candidate key not selected as the primary key; it still uniquely identifies a record	unique school email when student_id is the primary key

Caption: Student.class_id is a foreign key because its value should match the primary key Class.class_id.

Why not use name as a primary key? Names may repeat or change. A stable ID such as student_id is usually safer because it is designed to identify exactly one record.

Do not use secondary key here to mean any convenient search field. In this syllabus and its anchor notes, a secondary key is an alternative candidate key, so uniqueness is required. A non-unique field such as class_id may still be searched or indexed, but it is not a candidate or secondary key under this definition.

A composite key must still uniquely identify one record. For example, (student_id, book_id) only works as a loan key if the design assumes the same student cannot have multiple separate loan records for the same book. Otherwise, a separate loan_id or another identifying field would be needed.

Primary Key Versus Foreign Key

A primary key belongs to the table where it uniquely identifies a record.

A foreign key is stored in another table so that a record can refer back to the primary key.

Example:

Class(class_id, class_name, tutor)
Student(student_id, name, class_id)

Here:

• Class.class_id is the primary key of Class;
• Student.student_id is the primary key of Student;
• Student.class_id is a foreign key that refers to Class.class_id.

This means that a student record does not need to repeat the tutor name. It only stores the class ID, and the class ID can be used to find the class details.

What Makes a Good Primary Key?

A good primary key should be:

Property	Meaning
unique	no two records have the same key value
stable	the value should not change often
minimal	it should not include unnecessary fields
non-null	every record must have a key value

Weak choices:

Possible key	Problem
name	names may repeat or change
phone_number	phone numbers may change or be shared
class_id in Student	many students can be in the same class

A designed ID such as student_id is usually safer.

Why Not One Big Table?

Suppose a flat table stores loan data:

loan_id	student_name	class_name	tutor	book_title
1	Aisha	24S01	Ms Lim	Networks
2	Ben	24S01	Ms Lim	Databases

The tutor Ms Lim is repeated for every student in class 24S01. This is redundancy.

Problems:

• if the tutor changes, many rows must be updated;
• if one row is missed, the database becomes inconsistent;
• if all students from a class are deleted, the class-tutor information may be lost.

These are not just storage problems. They are reliability problems.

Redundancy and Anomalies

Data redundancy means the same data is stored more than once unnecessarily.

Redundancy can lead to anomalies:

Anomaly	Meaning	Example
insertion anomaly	difficult to add one fact without another unrelated fact	cannot store a new class unless a student has borrowed a book
update anomaly	the same fact must be edited in many places	tutor name must be changed in every matching row
deletion anomaly	deleting one record accidentally removes the only copy of another fact	deleting the last loan from a class removes the only record of its tutor

A good relational design reduces these problems by storing each fact in the right place.

Data Dependency

Data dependency means one data item depends on another.

Example:

class_id -> tutor

Read this as:

Once the class_id is known, the tutor can be determined.

If the tutor depends on the class, then tutor should be stored with class data, not repeated inside every student or loan record.

More examples:

student_id -> student_name, class_id
book_id -> book_title, author
loan_id -> student_id, book_id, loan_date

Dependencies help us decide which fields belong together in the same table.

Normalisation to 3NF

Normalisation is a disciplined way to split data into well-designed tables.

Beginner version:

Form	Practical question
1NF	Are values atomic, with no repeated groups inside one field?
2NF	If a table has a composite key, does every non-key field depend on the whole key?
3NF	Do non-key fields depend only on the key, not on another non-key field?

The target for H2 Computing is usually third normal form, or 3NF.

How to Think During Normalisation

Ask these questions:

1. What is this table really about?
2. Which field identifies one record?
3. Which non-key fields depend on the key?
4. Does any non-key field depend on another non-key field?
5. After splitting, what foreign keys are needed to reconnect the data?

Normalisation should preserve meaning. It should not split tables randomly.

From Flat Table to 3NF: A Worked Example

Suppose the first design is one large table:

LoanRecord(loan_id, student_id, student_name, class_id, tutor, book_id, book_title, author, loan_date)

A few rows may look like this:

loan_id	student_id	student_name	class_id	tutor	book_id	book_title	author	loan_date
1	101	Aisha	24S01	Ms Lim	501	Networks	A Wong	2026-07-08
2	102	Ben	24S01	Ms Lim	502	Databases	M Tan	2026-07-08
3	101	Aisha	24S01	Ms Lim	502	Databases	M Tan	2026-07-09

This table is understandable, but it repeats facts.

Step 1: Identify Dependencies

Look for fields that determine other fields:

student_id -> student_name, class_id
class_id -> tutor
book_id -> book_title, author
loan_id -> student_id, book_id, loan_date

This means:

Dependency	Design implication
student_id -> student_name, class_id	student facts belong in Student
class_id -> tutor	class facts belong in Class
book_id -> book_title, author	book facts belong in Book
loan_id -> student_id, book_id, loan_date	loan facts belong in Loan

Caption: Each functional dependency points to a suitable table: store facts with the key they depend on, then reconnect tables using primary and foreign keys.

Step 2: Split Facts into Suitable Tables

A better design is:

Student(student_id, student_name, class_id)
Class(class_id, tutor)
Book(book_id, book_title, author)
Loan(loan_id, student_id, book_id, loan_date)

Here:

• student facts go in Student;
• class facts go in Class;
• book facts go in Book;
• loan facts go in Loan.

Step 3: Keep Foreign Keys

The split tables must still be connected:

Foreign key	Refers to	Purpose
Student.class_id	Class.class_id	find the student’s class and tutor
Loan.student_id	Student.student_id	find who borrowed the book
Loan.book_id	Book.book_id	find which book was borrowed

The data is split, but keys make it possible to reconnect it.

Step 4: Trace One Query Idea Through the Design

Question:

Which book did Aisha borrow, and on what date?

Reasoning:

Step	Table used	Reason
find Aisha’s student ID	Student	student_name is stored with student facts
find Aisha’s loan records	Loan	Loan.student_id links to Student.student_id
find book titles	Book	Loan.book_id links to Book.book_id
show dates	Loan	loan_date belongs to the borrowing event

This is why normalised data can still be useful. The facts are separated, but SQL joins can reconnect them.

1NF, 2NF, and 3NF in Beginner Language

First Normal Form: 1NF

A table is in 1NF when each field contains atomic values and there are no repeated groups inside a single field.

Weak design:

student_id	name	borrowed_books
101	Aisha	Networks, Databases

Problem: borrowed_books stores multiple values in one field.

Better design:

student_id	book_id	loan_date
101	501	2026-07-08
101	502	2026-07-09

Each loan is a separate record.

Second Normal Form: 2NF

2NF requires the table first to be in 1NF. It matters especially when a candidate key is composite: every non-key field must depend on the whole candidate key, not just part of it. The field or fields on the left of a dependency arrow are the determinant; the fields determined by them are the dependents.

Example weak design:

Enrollment(student_id, subject_id, student_name, subject_title, enrolment_date)

Assume that a student has at most one current enrolment in a subject, so (student_id, subject_id) is a candidate key:

• student_name depends only on student_id;
• subject_title depends only on subject_id;
• enrolment_date depends on the whole composite key.

The first two are partial dependencies, so student and subject details should be moved to their own tables.

Third Normal Form: 3NF

3NF requires the table first to be in 2NF. Non-key fields must then depend on a candidate key without depending transitively through another non-key field.

Example weak design:

Student(student_id, student_name, class_id, tutor)

Here:

student_id -> class_id
class_id -> tutor

So tutor depends on class_id, which is not the primary key of Student. The tutor should be stored in Class, not repeated in Student.

Better design:

Student(student_id, student_name, class_id)
Class(class_id, tutor)

ER Diagrams

An ER diagram shows entities and relationships.

Caption: Read the relationship in both directions. In this model, one Customer may place zero or many Orders (0..*), while every Order belongs to exactly one Customer (1). A non-null foreign key in Order makes that membership compulsory; allowing it to be NULL would make the Order-to-Customer link optional.

Common relationship types:

Relationship type	Meaning	Example
one-to-one	one record in A relates to one record in B	one person has one school account
one-to-many	one record in A relates to many records in B	one class has many students
many-to-many	many records in A relate to many records in B	many students can borrow many books

Cardinality also has a minimum, called optional or mandatory participation. Recognise these endpoint meanings in crow’s-foot-style ER diagrams:

Endpoint meaning	Range notation	Interpretation
exactly one	1	one related record is compulsory
zero or one	0..1	the relationship is optional, with at most one match
one or more	1..*	at least one match is compulsory
zero or more	0..*	any number of matches, including none

The symbol nearest an entity describes how many instances of that entity may participate for one instance at the opposite end. Always read a relationship in both directions.

In textual schema diagrams, this wiki labels keys as PK and FK. Anchor ER/schema diagrams may instead use a solid underline for a primary key and a dashed underline for a foreign key; students should recognise both conventions and label their own diagrams consistently.

Example relationships:

One Class has many Students.
One Student can have many Loans.
One Book can appear in many Loans over time.

A many-to-many relationship is usually resolved using a linking table. For example, Student and Book are connected through Loan.

Caption: Loan resolves the many-to-many relationship by storing one borrowing event per row, with foreign keys to both Student and Book.

From ER Diagram to Tables

A simple way to move from ER thinking to table design is:

1. Turn each main entity into a table.
2. Choose a primary key for each table.
3. Add foreign keys to represent relationships.
4. Use a linking table for many-to-many relationships.
5. Check whether repeated facts can cause anomalies.

Example:

ER idea	Table design
Class has many Students	Student stores class_id as a foreign key
Student borrows Book	Loan links student_id and book_id
Book has title and author	Book stores book_title and author once

Wrong Versus Better

Weak statement	Better statement
A database is just many tables.	A relational database stores related facts in tables and connects them using keys.
A primary key is any important field.	A primary key uniquely identifies one record in a table.
A foreign key is a key in another table.	A foreign key is a field in one table that references the primary key of another table.
Normalisation means splitting tables.	Normalisation means reducing redundancy and dependency problems while preserving relationships.
3NF means there is no repeated data at all.	3NF reduces unnecessary repetition by ensuring non-key fields depend on the key, not on other non-key fields.

How to Answer This in Exams

For a key question:

State the key type, identify the field, and explain its role.

Example:

student_id is the primary key of Student because it uniquely identifies each student record.

For a foreign key question:

State the field, the table it is in, the table it references, and why it is needed.

Example:

Student.class_id is a foreign key that references Class.class_id. It links each student to the class record without repeating class details in every student record.

For a normalisation question:

Identify repeated or dependent data, split it into suitable tables, and keep foreign keys to reconnect related records.

Common Mistakes

• Choosing a field such as name as primary key even though names may repeat.
• Storing the same tutor name in many student records.
• Splitting tables without keeping foreign keys to reconnect the data.
• Treating a many-to-many relationship as if it can be represented by one foreign key.
• Saying a table is in 3NF without checking dependencies.
• Forgetting that a linking table can represent a relationship, not just an object.
• Thinking that normalisation removes the need for joins. Normalisation often makes joins necessary.

Check Your Understanding

1. What is a primary key?
2. What is a foreign key?
3. Why is repeated tutor data a problem?
4. Why might Loan(student_id, book_id, loan_date) use a composite key?
5. Why is Student(student_id, student_name, class_id, tutor) not a good 3NF design if class_id -> tutor?
6. Why does a many-to-many relationship usually need a linking table?

Answers:

1. A field or fields that uniquely identify a record in a table.
2. A field that references a primary key in another table.
3. It can cause inconsistent updates and unnecessary duplication.
4. A loan record may be identified by the combination of student and book only if that combination is unique under the design assumptions.
5. Because tutor depends on class_id, which is a non-key field in Student; tutor should be stored in Class.
6. Because one record on each side can be related to many records on the other side. A linking table stores each specific relationship as a separate record.

Related Notes

• Databases and Data Management
• SQL CRUD, Queries, and Joins