ER Diagrams & Normalisation to 3NF — Free HKDSE ICT Study Notes

Database design questions appear in every HKDSE ICT Paper 2 — sometimes as ER diagram construction (draw from scenario), sometimes as normalisation problems (spot the violation and fix it). This cheatsheet covers the elective’s core: ER modelling, relational keys, and the normalisation ladder to 3NF.

If you can spot every normalisation violation below and draw ER diagrams from word problems, you’re exam-ready for the database elective.

What this topic covers (2A elective + compulsory overlap)

The database elective (2A) builds on compulsory database basics from the core. In the core, you learn what a database is and basic SQL. In the elective, you learn how to design databases properly — ER modelling first, then normalisation to eliminate redundancy.

Elective-only topics: ER diagrams (Chen notation), relationship cardinality (1:1, 1:M, M:N), participation constraints, normalisation to 3NF, functional dependencies.

Compulsory overlap: Primary keys, foreign keys, basic table concepts appear in both core and elective. The elective goes deeper into why keys matter and how to choose them.

ER Modelling

An Entity-Relationship (ER) diagram is a blueprint for your database. You draw it before you build tables. Think of it as a map that shows what data you need and how things connect.

Core components

Component	Symbol	Meaning
Entity	Rectangle	An object or thing (Student, Course, Teacher)
Attribute	Oval	A property of an entity (Name, Age, StudentID)
Relationship	Diamond	How entities connect (enrolls in, teaches)

Attribute types (when examined)

• Simple (oval): Atomic, indivisible (Name, Age)
• Composite (large oval with connected ovals): Made of parts (Address → Street, City, Postcode)
• Multi-valued (double oval): Can have multiple values (Phone numbers)
• Derived (dashed oval): Calculated from others (Age from DateOfBirth)

Relationship cardinality (Chen notation)

1:1 (One-to-One): One entity in A relates to one entity in B.

Example: Person ↔ Passport. Each person has one passport; each passport belongs to one person.

Person ─────── Passport
      1:1

1:M (One-to-Many): One entity in A relates to many entities in B.

Example: Teacher → Students. One teacher teaches many students; each student has one teacher.

Teacher ─────── Student
       1:M

M:N (Many-to-Many): Many entities in A relate to many entities in B.

Example: Student ↔ Course. Each student takes many courses; each course has many students.

Student ─────── Course
       M:N

Critical pattern: M:N relationships cannot be stored directly in a relational database. You must convert them to a junction table (intersection relation). More on this below.

Participation constraints

Total participation (double line): Every entity MUST participate in the relationship.

Example: In Employee ──<dep>── Dependent, every Dependent MUST be related to an Employee. A dependent can’t exist alone.

Employee ═══════ Dependent
         (total)

Partial participation (single line): Participation is optional.

Example: In Teacher ──<teaches>── Subject, a teacher might not teach any subject (new hire). A subject might not have a teacher assigned yet.

Teacher ────── Subject
        (partial)

Converting M:N to junction tables

When your ER diagram has an M:N relationship, you create a junction table (also called intersection relation) with two foreign keys.

Example: Student ↔ Course (M:N) becomes:

Student(StudentID PK, Name, Age)
Course(CourseID PK, CourseName, Credits)
Enrollment(StudentID FK, CourseID FK, PRIMARY KEY(StudentID, CourseID), Grade)

The Enrollment table captures which student takes which course and can store relationship-specific data (Grade, Semester).

Keys in Relational Databases

Keys are how tables connect and how rows stay unique.

Key types (with examples)

Key type	Definition	Example
Superkey	Any attribute set that uniquely identifies a row	{StudentID}, {StudentID, Name}, {HKID, Email}
Candidate key	Minimal superkey (no subset is also a key)	{StudentID}, {HKID} (both minimal)
Primary key (PK)	The candidate key you choose	StudentID (chosen for simplicity)
Alternate key	Candidate key not chosen as PK	HKID (valid but not selected)
Foreign key (FK)	References PK in another table	ClassID in Student → Class(ClassID)
Composite key	Multiple attributes together form a key	(StudentID, CourseID) in Enrollment

Students–Courses schema example

Here’s a classic example showing all key types:

Student(StudentID PK, HKID, Name, Age, ClassID FK)
Course(CourseID PK, CourseName, Credits, TeacherID FK)
Enrollment(StudentID FK, CourseID FK, Grade, PRIMARY KEY(StudentID, CourseID))

Key analysis:

• StudentID is PK for Student; HKID is a candidate key (unique but not chosen)
• CourseID is PK for Course
• (StudentID, CourseID) is a composite PK for Enrollment
• ClassID in Student is an FK referencing Class
• TeacherID in Course is an FK referencing Teacher

Why composite PK in Enrollment?: Each student can enroll in each course only once. (StudentID, CourseID) uniquely identifies each enrollment record.

Normalisation Ladder: 1NF → 2NF → 3NF

Normalisation eliminates redundancy (storing same data multiple times) and anomalies (update, insert, delete problems). You transform tables stage-by-stage until each table satisfies a strict definition.

The definitions

First Normal Form (1NF): Every attribute contains atomic (indivisible) values. No repeating groups, no multi-valued cells.

Second Normal Form (2NF): In 1NF + no partial dependency (non-key attributes must depend on ALL parts of a composite PK, not just some).

Third Normal Form (3NF): In 2NF + no transitive dependency (non-key attributes must depend directly on the PK, not through other non-key attributes).

Running example: Student course registration

Let’s start with a badly designed table and fix it step-by-step.

Before normalisation (violates 1NF, 2NF, and 3NF):

StudentCourses(StudentID, StudentName, Class, CourseID, CourseName, Credits, Teacher, TeacherRoom)

Sample data:

StudentID	StudentName	Class	CourseID	CourseName	Credits	Teacher	TeacherRoom
001	Alice	5A	CS101	Comp Studies	5	Mr. Lee	R101
001	Alice	5A	MATH201	Algebra	5	Ms. Chan	R205
002	Bob	5B	CS101	Comp Studies	5	Mr. Lee	R101
003	Carol	5A	PHYS301	Physics	5	Mr. Wang	R310

What’s wrong:

• StudentName and Class repeat for each course a student takes → redundancy
• CourseName, Credits, Teacher, TeacherRoom repeat across students taking the same course → more redundancy
• If Mr. Lee moves rooms, we must update EVERY row with his courses → update anomaly
• If we delete the last student in CS101, we lose the course info → delete anomaly

Stage 1: 1NF (fix atomic values)

Our table already satisfies 1NF (every cell holds one value). But let’s show a violation:

1NF violation example:

StudentCourses(StudentID, StudentName, Courses)

Where Courses contains “CS101, MATH201, PHYS301” — not atomic.

Fix to 1NF: Split into separate rows:

StudentCourses(StudentID, StudentName, CourseID)

Now each course is a separate row. Our original table is already in 1NF.

Stage 2: 2NF (fix partial dependency)

Definition: No non-key attribute depends on only part of a composite PK.

Our PK is (StudentID, CourseID). Let’s check dependencies:

• StudentName depends only on StudentID (partial dependency!)
• Class depends only on StudentID (partial dependency!)
• CourseName depends only on CourseID (partial dependency!)
• Credits depends only on CourseID (partial dependency!)
• Teacher depends only on CourseID (partial dependency!)
• TeacherRoom depends only on CourseID (partial dependency!)

Everything except (StudentID, CourseID) has partial dependency → violates 2NF.

Fix to 2NF: Split into three tables (separate student data, course data, and enrollment):

Student(StudentID PK, StudentName, Class)
Course(CourseID PK, CourseName, Credits, Teacher, TeacherRoom)
Enrollment(StudentID FK, CourseID FK, PRIMARY KEY(StudentID, CourseID))

Sample data after 2NF:

Student:
| StudentID | StudentName | Class |
|-----------|-------------|-------|
| 001       | Alice       | 5A    |
| 002       | Bob         | 5B    |
| 003       | Carol       | 5A    |

Course:
| CourseID | CourseName   | Credits | Teacher  | TeacherRoom |
|----------|--------------|---------|----------|-------------|
| CS101    | Comp Studies | 5       | Mr. Lee  | R101        |
| MATH201  | Algebra      | 5       | Ms. Chan | R205        |
| PHYS301  | Physics      | 5       | Mr. Wang | R310        |

Enrollment:
| StudentID | CourseID |
|-----------|----------|
| 001       | CS101    |
| 001       | MATH201  |
| 002       | CS101    |
| 003       | PHYS301  |

Improvement: Student info now stored once; course info stored once. But we still have a problem…

Stage 3: 3NF (fix transitive dependency)

Definition: No non-key attribute depends on another non-key attribute.

Check the Course table:

PK is CourseID. Dependencies:

• CourseName depends on CourseID ✓ (directly on PK)
• Credits depends on CourseID ✓ (directly on PK)
• Teacher depends on CourseID ✓ (directly on PK)
• TeacherRoom depends on Teacher → transitive dependency! Violates 3NF

Why transitive?: TeacherRoom depends on Teacher, not directly on CourseID. If a course changes teachers, the room might change too.

Fix to 3NF: Split Course into two tables:

Course(CourseID PK, CourseName, Credits, TeacherID FK)
Teacher(TeacherID PK, TeacherName, TeacherRoom)

Wait — we also need to fix Enrollment if we want to store grades. Let’s redo the full 3NF schema:

Student(StudentID PK, StudentName, ClassID FK)
Class(ClassID PK, ClassName)
Course(CourseID PK, CourseName, Credits, TeacherID FK)
Teacher(TeacherID PK, TeacherName, TeacherRoom)
Enrollment(StudentID FK, CourseID FK, Grade, PRIMARY KEY(StudentID, CourseID))

Sample data after 3NF:

Student:
| StudentID | StudentName | ClassID |
|-----------|-------------|---------|
| 001       | Alice       | 5A      |
| 002       | Bob         | 5B      |
| 003       | Carol       | 5A      |

Class:
| ClassID | ClassName |
|---------|-----------|
| 5A      | 5A        |
| 5B      | 5B        |

Course:
| CourseID | CourseName   | Credits | TeacherID |
|----------|--------------|---------|------------|
| CS101    | Comp Studies | 5       | T001       |
| MATH201  | Algebra      | 5       | T002       |
| PHYS301  | Physics      | 5       | T003       |

Teacher:
| TeacherID | TeacherName | TeacherRoom |
|-----------|-------------|-------------|
| T001      | Mr. Lee     | R101        |
| T002      | Ms. Chan    | R205        |
| T003      | Mr. Wang    | R310        |

Enrollment:
| StudentID | CourseID | Grade |
|-----------|----------|-------|
| 001       | CS101    | A     |
| 001       | MATH201  | B     |
| 002       | CS101    | C     |
| 003       | PHYS301  | A     |

Benefits:

• Zero redundancy: each fact stored once
• No update anomalies: changing Mr. Lee’s room requires updating ONE row
• No insert anomalies: can add a teacher before assigning courses
• No delete anomalies: dropping all students from CS101 doesn’t lose course info

Exam Patterns (what appears in past papers)

Pattern 1: Draw ER diagram from scenario

Question style: “Draw an ER diagram for a school system with students, teachers, and courses. Students enroll in many courses; teachers teach many courses.”

Approach:

1. Identify entities (nouns): Student, Teacher, Course
2. Identify relationships (verbs): enrolls in (Student-Course, M:N), teaches (Teacher-Course, 1:M)
3. Determine cardinality: One teacher teaches many courses; one student takes many courses
4. Add attributes: StudentID (PK), Name; CourseID (PK), CourseName; TeacherID (PK), Name
5. Show participation: Can a student exist without courses? (Partial) Can a course exist without students? (Partial)

Answer sketch:

Student ─────── Enrollment ─────── Course
       M:N                    M:N

Teacher ─────── Course
       1:M

Pattern 2: Spot the normalisation violation

Question style: “This table is in 1NF but not 2NF. Explain why and fix it.”

OrderItem(OrderID, ProductID, ProductName, Quantity, UnitPrice)

Analysis:

• PK is (OrderID, ProductID)
• ProductName depends only on ProductID (partial dependency)
• UnitPrice depends only on ProductID (partial dependency)
• Violates 2NF

Fix:

OrderItem(OrderID FK, ProductID FK, Quantity, PRIMARY KEY(OrderID, ProductID))
Product(ProductID PK, ProductName, UnitPrice)

Pattern 3: Convert to 3NF

Question style: “Convert this table to 3NF. Explain each step.”

StudentReport(StudentID, Name, Class, Teacher, Room, Subject, Grade)

Approach:

1. Check 1NF: All values atomic? Yes.
2. Check 2NF: PK = (StudentID, Subject). Does Name depend on both? No → partial dependency. Split:

   Student(StudentID PK, Name, Class)
   Report(StudentID FK, Subject FK, Grade, Teacher, Room)

3. Check 3NF: In Report, Room depends on Teacher (transitive). Split:

   Teacher(TeacherID PK, TeacherName, Room)
   Subject(SubjectID PK, SubjectName)
   Report(StudentID FK, SubjectID FK, TeacherID FK, Grade)

Self-Check Questions

Test yourself. Answers are in the collapsible below.

1. ER diagram: Draw an ER diagram for a library system with Books, Members, and Loans. A member can borrow many books; a book can be borrowed by many members over time. Show cardinality and participation.
2. Normalisation: This table is in 1NF but violates 2NF. Explain why and convert to 2NF:

   Enrollment(StudentID, StudentName, CourseID, CourseName, Grade)

3. Transitive dependency: This table is in 2NF but violates 3NF. Explain why and convert to 3NF:

   Product(ProductID, ProductName, SupplierName, SupplierCity, Price)

4. Schema design: Convert this scenario to 3NF schema: “A school has students identified by StudentID. Each student has a name and belongs to one class. Each class has a ClassCode and a classroom teacher. Students take subjects; each subject has a SubjectCode and is taught by one teacher. Teachers are identified by TeacherID and have a name and office room.”

Click for answers

1. ER diagram (library):

   • Entities: Book (BookID PK, Title, Author), Member (MemberID PK, Name, Address), Loan (LoanID PK, LoanDate, ReturnDate)
   • Relationships: Member borrows Book (M:N), Loan links Member and Book
   • Cardinality: One member can borrow many books (one book can be borrowed by many members over time) → M:N
   • Participation: Partial (members exist without borrowing books; books exist without being borrowed)
   • Diagram sketch:

     Member ─────── Loan ─────── Book
            M:N              M:N

2. 2NF conversion:

   • PK is (StudentID, CourseID)
   • StudentName depends only on StudentID (partial dependency)
   • CourseName depends only on CourseID (partial dependency)
   • Violates 2NF because non-key attributes depend on PART of the composite PK
   • Fix to 2NF:

     Student(StudentID PK, StudentName)
     Course(CourseID PK, CourseName)
     Enrollment(StudentID FK, CourseID FK, Grade, PRIMARY KEY(StudentID, CourseID))

3. 3NF conversion:

   • PK is ProductID (single attribute, so no partial dependency — already in 2NF)
   • SupplierCity depends on SupplierName (transitive dependency: ProductID → SupplierName → SupplierCity)
   • Violates 3NF because non-key attribute depends on another non-key attribute
   • Fix to 3NF:

     Product(ProductID PK, ProductName, SupplierID FK, Price)
     Supplier(SupplierID PK, SupplierName, SupplierCity)

4. 3NF schema (school scenario):

   Student(StudentID PK, StudentName, ClassID FK)
   Class(ClassID PK, ClassCode, TeacherID FK)
   Teacher(TeacherID PK, TeacherName, OfficeRoom)
   Subject(SubjectID PK, SubjectCode, TeacherID FK)
   Enrollment(StudentID FK, SubjectID FK, Grade, PRIMARY KEY(StudentID, SubjectID))

   Foreign keys: Student.ClassID → Class.ClassID, Class.TeacherID → Teacher.TeacherID, Subject.TeacherID → Teacher.TeacherID, Enrollment.StudentID → Student.StudentID, Enrollment.SubjectID → Subject.SubjectID

---

Want full notes and more practice?

This is a sample of our HKDSE ICT database elective notes. The full set covers ER diagram construction from scratch, every normalisation pattern from past papers, and advanced SQL joins.

Explore our complete HKDSE ICT notes — structured for revision, optimized for exam day.

Need personal feedback? Book a trial lesson — we’ll review your ER diagrams, fix your normalisation logic, and build your targeted study plan.

← All articles