week 6

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