← CSC 213 Database Systems
Database Normalization and Functional Dependencies
Notes
Understanding Normalization
Normalization is a formal database design technique that organizes schemas to eliminate data redundancy, maintain data consistency, and prevent modification anomalies. Without normalization, systems suffer from insertion anomalies (inability to record facts without unrelated attributes), deletion anomalies (unintended loss of data when deleting an entity), and update anomalies (inconsistent multi-row edits for a single real-world fact).
Functional Dependencies and Normal Forms
A functional dependency X -> Y means attribute X uniquely determines attribute Y. Normal forms provide sequential benchmarks for schema optimization:
First Normal Form (1NF):
Requires that table cells contain atomic values with no repeating groups or multi-valued attributes, ensuring each attribute has a fixed schema type.
Second Normal Form (2NF):
Requires meeting 1NF and ensuring all non-prime attributes are fully functionally dependent on the entire candidate key, completely eliminating partial dependencies in composite-key relations.
Third Normal Form (3NF):
Requires meeting 2NF and removing all transitive dependencies, meaning non-prime attributes must not depend on other non-prime attributes. The rule of thumb states that every non-key attribute must depend on the key, the whole key, and nothing but the key.
Practical example
Consider Kenyatta National Hospital managing outpatient clinics. An unnormalized record initially groups multiple symptoms and stores doctor details inside the patient appointment card:
Appointment(CardNo, PatientName, PhoneNumbers, DoctorID, DoctorName, Department, AppointmentDate)
Step 1: Achieving 1NF
Patients often provide multiple phone numbers (e.g., Safaricom and Airtel). 1NF eliminates this repeating group by storing one phone number per tuple or splitting phone numbers into a PatientPhone table: PatientPhone(CardNo, PhoneNumber).
Step 2: Achieving 2NF
In a composite table ClinicSchedule(CardNo, DoctorID, AppointmentDate, PatientName), PatientName depends only on CardNo (partial dependency). We decompose this into Patient(CardNo, PatientName) and Visit(CardNo, DoctorID, AppointmentDate).
Step 3: Achieving 3NF
In the Visit relation, we might have DoctorID, DoctorName, and Department. Since DoctorID determines DoctorName and Department, having these inside Visit creates a transitive dependency (CardNo -> DoctorID -> Department). If a doctor changes their department, multiple appointment records require updates. We decompose this by extracting Doctor(DoctorID, DoctorName, Department) and referencing DoctorID in Visit as a foreign key. The hospital database achieves clean 3NF without redundant administrative data.
Quiz
1. Which of the following describes a deletion anomaly in an unnormalized hospital database?
2. What condition must be satisfied for a relation to comply with Third Normal Form (3NF)?
3. What type of dependency is eliminated when a relation advances from First Normal Form (1NF) to Second Normal Form (2NF)?
Sign in to submit and securely grade your quiz.