Database Normalization

Database Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them. The goal is to ensure that data is stored efficiently and logically. Key Objectives of Normalization: 1. Eliminate redundant data (e.g., storing the same data in more than one table). 2. Ensure data dependencies make sense (i.e., only storing related data in a table). 3. Minimize anomalies in insert, update, and delete operations. SCENARIO: Student Course Enrollment Here is an Unnormalized Table (UNF): Student_ID Student_Name Courses Instructor 101 Anna Cruz Math, English Mr. Lee, Ms. Santos 102 Ben Reyes Math Mr. Lee 103 Carla David English, History Ms. Santos, Mr. Dela Cruz First Normal Form (1NF) Rule: Eliminate repeating groups; ensure atomic (single) values. Convert multiple values in a field into separate rows. 1NF Table: Student_ID Student_Name Course Instructor 101 Anna Cruz Math Mr. Lee 101 Anna Cruz English Ms. Santos 102 Ben Reyes Math 103 Carla David English Ms. Santos 103 Carla David History Mr. Dela Cruz Mr. Lee Second Normal Form (2NF) Rule: Be in 1NF + remove partial dependencies (no non-prime attribute should depend on part of a composite key). Here, the composite key is (Student_ID, Course). But Student_Name depends only on Student_ID. Solution: Break the table into two: 1. Student Table 2. Enrollment Table Enrollment Table: Student Table: Enrollment_ID Student_ID Course Instructor Student_ID Student_Name 101 Anna Cruz En1 101 Math Mr. Lee 102 Ben Reyes En2 101 English Ms. Santos 103 Carla David En3 102 Math En4 103 English Ms. Santos En5 103 History Mr. Dela Cruz Mr. Lee Third Normal Form (3NF) Rule: Be in 2NF + remove transitive dependencies (non-key attribute depends on another non-key attribute). In the Enrollment Table, Instructor depends on Course, not on Student_ID + Course. So, we need to separate Instructor into its own table. Final Tables: Student Table: Student_ID Student_Name Course Table: Course_ID Course 101 Anna Cruz C1 Math 102 Ben Reyes C2 English 103 Carla David C3 History Enrollment Table: Enrollment_ID Student_ID Course_ID Instructor_ID Instructor Table: Instructor_ID Course En1 101 C1 Ins1 Ins1 Mr. Lee En2 101 C2 Ins2 Ins2 Ms. Santos En3 102 C1 Ins1 Ins3 Mr. Dela Cruz En4 103 C2 Ins2 En5 103 C3 Ins3 Summary Table of Normalization: Normal Form Change Applied UNF Multi-valued fields 1NF Atomic values only 2NF Removed partial dependency 3NF Removed transitive dependency Another Example: Unnormalized Form (UNF) OrderID CustomerName CustomerPhone BookTitles O001 John Santos 09171234567 O002 Maria Dela Cruz 09281234567 BookPrices AuthorNames "Intro to DB", "HTML 101" 500, 300 A. Cruz, B. Reyes "Python Basics" C. Lim 450 First Normal Form (1NF) Rule: Eliminate repeating groups; ensure atomic (single) values. Convert multiple values in a field into separate rows. 1NF Table: OrderID CustomerName CustomerPhone BookTitle BookPrice AuthorName O001 John Santos 09171234567 Intro to DB 500 A. Cruz O002 John Santos 09171234567 HTML 101 300 B. Reyes O003 Maria Dela Cruz 09281234567 Python Basics 450 C. Lim Second Normal Form (2NF) Rule: Remove partial dependencies — non-key attributes should depend on the full primary key. Break into tables: Table: Customers Customer_ID CustomerName CustomerPhone Table: Orders OrderID BookTitle BookPrice AuthorName Cus1 John Santos 09171234567 O001 Intro to DB 500 A. Cruz Cus2 Maria Dela Cruz 09281234567 O002 HTML 101 300 B. Reyes O003 Python Basics 450 C. Lim Third Normal Form (3NF) Rule: Remove transitive dependencies — no non-key attribute should depend on another non-key attribute. Break into tables: Table: Customers Customer_ID CustomerName CustomerPhone Table: Orders OrderID Book_ID Customer_ID Cus1 John Santos 09171234567 O001 B001 Cus1 Cus2 Maria Dela Cruz 09281234567 O002 B002 Cus1 O003 B003 Cus2 Table: Books Book_ID BookTitle BookPrice AuthorName B001 Intro to DB 500 A. Cruz B002 HTML 101 300 B. Reyes B003 Python Basics 450 C. Lim Activity: Normalize the following table AppointmentID PatientName PatientContact DoctorName DoctorSpecialty AppointmentDate A001 Jane Santos 09123456789 Dr. Cruz Cardiology A002 Mark Reyes 09987654321 Dr. Dela Rosa Dermatology Symptoms 2025-06-15, 2025- Chest Pain, 06-20 Headache 2025-06-18 Skin Rash 1NF AppointmentID PatientName PatientContact DoctorName DoctorSpecialty AppointmentDate Symptoms A001 Jane Santos 09123456789 Dr. Cruz Cardiology 2025-06-15 Chest Pain A001 Jane Santos 09123456789 Dr. Cruz Cardiology 2025-06-20 Headache A002 Mark Reyes 09987654321 Dr. Dela Rosa Dermatology 2025-06-18 Skin Rash 2NF Patients Table PatientID PatientName PatientContact P001 Jane Santos 09123456789 P002 Mark Reyes 09987654321 Appointment Table AppointmentID DoctorName DoctorSpecialty AppointmentDate Symptoms A001 Dr. Cruz Cardiology 2025-06-15 Chest Pain A001 Dr. Cruz Cardiology 2025-06-20 Headache A002 Dr. Dela Rosa Dermatology 2025-06-18 Skin Rash 3NF Patients Table Symptoms Table PatientID PatientName PatientContact SymptomID Symptoms P001 Jane Santos 09123456789 P002 Mark Reyes 09987654321 S001 Chest Pain S002 Headache S003 Skin Rash Doctors Table DoctorID DoctorName DoctorSpecialty D001 Dr. Cruz Cardiology D002 Dr. Dela Rosa Dermatology Appointments Table AppointmentID PatientID DoctorID SymptomID AppointmentDate A001-1 P001 D001 S001 2025-06-15 A001-2 P001 D001 S002 2025-06-20 A002-1 P002 D002 S003 2025-06-18