week 6
| Site: | Newgate University Minna - Elearning Platform |
| Course: | Data Management |
| Book: | week 6 |
| Printed by: | Guest user |
| Date: | Sunday, 14 June 2026, 3:46 AM |
Description
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.1 Functional Dependencies
A functional dependency (FD) X → Y means that the value of attribute set X DETERMINES the value of attribute set Y. In other words, if two tuples have the same value for X, they must have the same value for Y.
EXAMPLES of functional dependencies from a Nigerian university:
1. MatricNo → StudentName (knowing the MatricNo determines the student's name)
2. CourseCode → CourseName (knowing the course code determines the course name)
3. MatricNo, CourseCode → Grade (knowing both the student AND the course determines the grade)
4. DeptCode → Dean (knowing the department code determines who the dean is)
KEY FD TYPES:
5. FULL functional dependency: Y is fully dependent on X if Y depends on X and not on any proper subset of X. E.g., Grade is FULLY dependent on {MatricNo, CourseCode} — neither alone is enough.
6. PARTIAL functional dependency: Y is partially dependent on X if Y depends on a proper subset of X. E.g., StudentName is PARTIALLY dependent on {MatricNo, CourseCode} because it depends only on MatricNo.
7. TRANSITIVE functional dependency: If X → Y and Y → Z (where Y is not a key), then X → Z transitively. E.g., MatricNo → DeptCode and DeptCode → DeptName, so MatricNo → DeptName (transitively).
6.2 Update Anomalies
A poorly designed relation with redundant data suffers from anomalies — problems that occur during data manipulation. Consider this unnormalised ENROLMENT_INFO table:
|
MatricNo | StudentName |
DeptCode | DeptName |
CourseCode | CourseName | Grade |
|
NU/20/001 | Emeka Okafor |
CSC | Computer Science |
CSC301 | Database Systems | A |
|
NU/20/001 | Emeka Okafor |
CSC | Computer Science |
CSC305 | Software Engineering | B+ |
|
NU/21/045 | Fatima Musa |
EEE | Electrical Engineering |
EEE301 | Circuit Theory | A |
|
NU/21/045 | Fatima Musa |
EEE | Electrical Engineering |
EEE305 | Signal Processing | B |
|
Anomaly Type |
Problem in ENROLMENT_INFO |
|
Update Anomaly |
If the department name 'Computer Science' is changed to 'Computing Sciences', we must update EVERY row for every CSC student — if we miss one, the data is inconsistent. |
|
Insertion Anomaly |
We cannot store information about a new course until at least one student enrols in it (because CourseCode, MatricNo is the key and both must have values). |
|
Deletion Anomaly |
If Fatima Musa withdraws from ALL courses, deleting her rows also deletes all information about EEE301 and EEE305 — losing course data inadvertently. |
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.
6.4 Impact of Normalisation on Database Efficiency
|
Aspect |
Effect of Normalisation |
|
Data Redundancy |
REDUCED — each fact stored once eliminates duplication |
|
Update Anomalies |
ELIMINATED — one update in one place propagates everywhere |
|
Data Integrity |
IMPROVED — no conflicting copies of data |
|
Query Complexity |
INCREASED — more JOINs needed to reassemble split data |
|
Query Performance |
POTENTIALLY DECREASED — JOINs are expensive on large tables |
|
Storage Requirement |
REDUCED — less duplication = less storage |
|
Denormalisation: In practice, highly normalised databases (3NF/BCNF) sometimes perform poorly for read-heavy workloads due to the cost of JOINs. DENORMALISATION is the deliberate introduction of redundancy to improve query performance. E.g., a Nigerian bank's reporting database (data warehouse) may store CustomerName in the TRANSACTION table (denormalised) to avoid a JOIN with CUSTOMER for every report query. Denormalisation is a trade-off: better read performance at the cost of more complex updates. |
Reading List / References
1. Ramakrishnan, R. & Gehrke, J. (2003). Database Management Systems, Chapter 19: Schema Refinement and Normal Forms. McGraw-Hill.
2. Date, C. J. (2004). An Introduction to Database Systems, Chapter 11: Functional Dependencies and Normalisation. Pearson.
3. Silberschatz et al. (2020). Database System Concepts, Chapter 7: Relational Database Design. McGraw-Hill.
Activities
|
Self-Assessment Quiz: 1. Define functional dependency. Give THREE examples from a banking system. 2. What is a partial functional dependency? Why does it violate 2NF? 3. Explain a transitive dependency with an example. Which normal form eliminates it? 4. Normalise the following to 3NF: ORDER(OrderID, CustomerID, CustomerName, CustomerCity, ProductID, ProductName, Quantity, UnitPrice) where the FDs are: OrderID,ProductID → Quantity; CustomerID → CustomerName, CustomerCity; ProductID → ProductName, UnitPrice. |
|
Normalisation Workshop: The following STAFF_PROJECT table is NOT in 2NF: STAFF_PROJECT(StaffID, ProjectCode, StaffName, Dept, DeptHead, HoursWorked, ProjectName, Budget) PK = {StaffID, ProjectCode} Identify all functional dependencies, list ALL anomalies, then normalise step by step to 3NF. Show all intermediate steps (UNF → 1NF → 2NF → 3NF). |