week 6
Teaching and Learning Methods to be Employed
1. Lecture with step-by-step normalisation worked examples
2. Normalisation workshop — students normalise a given unnormalised table
3. Collection of Assignment 2
4. Discussion: Denormalisation trade-offs in real systems
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Define functional dependency and identify FDs from a given relation.
2. Explain update, insertion, and deletion anomalies.
3. Apply First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).
4. Explain Boyce-Codd Normal Form (BCNF).
5. Describe the impact of normalisation on database efficiency.
6.3 Normal Forms
6.3.1 First Normal Form (1NF)
DEFINITION: A relation is in 1NF if: (1) All attributes are atomic (no multi-valued or composite attributes). (2) All entries in a column are of the same data type. (3) Each row is uniquely identifiable (has a primary key).
VIOLATION EXAMPLE: STUDENT(MatricNo, Name, PhoneNumbers) where PhoneNumbers contains '08012345678, 07098765432' — this is NOT 1NF because PhoneNumbers is multi-valued.
FIX: Create separate STUDENT_PHONE(MatricNo, PhoneNumber) table.
6.3.2 Second Normal Form (2NF)
DEFINITION: A relation is in 2NF if: (1) It is already in 1NF. (2) Every non-key attribute is FULLY functionally dependent on the entire primary key (no partial dependencies).
2NF is only relevant for relations with COMPOSITE primary keys.
VIOLATION: ENROLMENT(MatricNo, CourseCode, StudentName, CourseName, Grade)
PK = {MatricNo, CourseCode}. Problem: StudentName depends only on MatricNo (partial FD). CourseName depends only on CourseCode (partial FD). Only Grade depends on {MatricNo, CourseCode} fully.
FIX — Decompose into:
1. STUDENT(MatricNo PK, StudentName)
2. COURSE(CourseCode PK, CourseName)
3. ENROLMENT(MatricNo FK, CourseCode FK, Grade) — PK: {MatricNo, CourseCode}
6.3.3 Third Normal Form (3NF)
DEFINITION: A relation is in 3NF if: (1) It is already in 2NF. (2) No non-key attribute is transitively dependent on the primary key.
VIOLATION: STUDENT(MatricNo, StudentName, DeptCode, DeptName, Dean)
MatricNo → DeptCode (direct), DeptCode → DeptName (direct), DeptCode → Dean (direct).
Therefore: MatricNo → DeptName and MatricNo → Dean (TRANSITIVE dependencies through DeptCode).
DeptName and Dean are determined by DeptCode, not directly by MatricNo — this is a 3NF violation.
FIX — Decompose into:
4. STUDENT(MatricNo PK, StudentName, DeptCode FK)
5. DEPARTMENT(DeptCode PK, DeptName, Dean)
6.3.4 Boyce-Codd Normal Form (BCNF)
DEFINITION: A relation is in BCNF if for every non-trivial FD X → Y, X must be a superkey. BCNF is a slightly stronger version of 3NF. Most relations in 3NF are also in BCNF, but not always.
BCNF VIOLATION EXAMPLE: TEACHING(Student, Course, Lecturer) where each course has one lecturer and each student can study each course from at most one lecturer. FDs: {Student, Course} → Lecturer (PK), Lecturer → Course. The FD 'Lecturer → Course' violates BCNF because Lecturer is not a superkey, yet it determines Course.
