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.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. |