Database normalization is a systematic process of organizing data in a relational database to eliminate redundancy and improve data integrity. The goal of normalization is to minimize data duplication, reduce update anomalies, and ensure efficient data retrieval by structuring the database into smaller, well-defined tables with appropriate relationships.
Normalization follows a set of normal forms (NF), each with stricter constraints to progressively refine the database schema.
Key Objectives of Normalization
- Eliminate Data Redundancy → Avoid storing the same data in multiple places.
- Ensure Data Integrity → Maintain consistency and prevent anomalies.
- Minimize Update, Insert, and Delete Anomalies → Prevent issues where updating, inserting, or deleting data leads to unintended consequences.
- Improve Query Efficiency → Structure data in a way that allows for optimal query performance.
- Establish Clear Relationships → Define proper Primary Keys (PK) and Foreign Keys (FK) to create meaningful links between tables.
Advanced Database Normalization Challenge
Scenario: Multi-Campus University Course Management System
You have been hired as a database consultant for a university that operates multiple campuses, each with independent but overlapping course offerings. The university wants to manage student enrollments, instructors, courses, departments, classrooms, and scheduling in a single relational database.
Currently, the university stores all data in a single unnormalized table (UNF), but serious data integrity issues have surfaced due to redundancy, inconsistencies, and update anomalies. Your task is to normalize this table to Third Normal Form (3NF).
Unnormalized Data (UNF)
| RecordID | StudentID | StudentName | Major | Campus | CourseCode | CourseName | Credits | Instructor | InstructorPhone | RoomNo | RoomCapacity | ClassDay | ClassTime | EnrollmentStatus |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1001 | Alice Brown | CS | NY | CS101 | Intro to CS | 3 | Dr. Smith | 555-1234 | A101 | 50 | Monday | 10:00 AM | Enrolled |
| 2 | 1002 | Bob White | Math | NY | MATH201 | Linear Algebra | 4 | Dr. Jones | 555-5678 | B202 | 40 | Tuesday | 1:00 PM | Enrolled |
| 3 | 1001 | Alice Brown | CS | NY | MATH201 | Linear Algebra | 4 | Dr. Jones | 555-5678 | B202 | 40 | Tuesday | 1:00 PM | Enrolled |
| 4 | 1003 | Carol Smith | Physics | CA | PHYS301 | Quantum Mechanics | 4 | Dr. Taylor | 555-9876 | C303 | 30 | Thursday | 3:00 PM | Enrolled |
| 5 | 1002 | Bob White | Math | NY | CS101 | Intro to CS | 3 | Dr. Smith | 555-1234 | A101 | 50 | Monday | 10:00 AM | Waitlisted |
| 6 | 1004 | David Lee | CS | CA | CS101 | Intro to CS | 3 | Dr. Johnson | 555-3333 | D404 | 45 | Monday | 10:00 AM | Enrolled |
| 7 | 1004 | David Lee | CS | CA | PHYS301 | Quantum Mechanics | 4 | Dr. Taylor | 555-9876 | C303 | 30 | Thursday | 3:00 PM | Enrolled |
Step 1: First consider the following:
- Primary Key: What is he primary key of this table (hint: it is composite)
- Redundancies: Where do you see redundancies in this table? List them all.
- Partial Dependencies: What are some field that are depentand on only part of the PK?
- Transitive Dependencies: What are some dependancies e.g. where A determines B, and B determines C?
- Complex Relationships: can you find any additional complexities here?
Step 2: Normalize to 3NF. Show your steps, and the final tables.
Step 3: Now use AI to normalize to 3NF
Use Ai to try to do the normalization? What do you thing? Did you find any problems?
Step 4: Compar the results
- Did you miss anything that AIU got right?
- Did AI get anything wrong that you got?