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