A university's enrollment system stores data in a single table with columns: EnrollmentID, StudentID, StudentName, CourseID, CourseName, and Grade. Students can take multiple courses, and each course has multiple students. The team notices data redundancy: StudentName is repeated for each enrollment of the same student, and CourseName is repeated for each enrollment in the same course. They want to reduce redundancy while preserving the ability to query all enrollments with student and course details. What is the most appropriate design approach?
Normalization decomposes the unnormalized enrollment table into Student, Course, and Enrollment relations, moving StudentName into Students and CourseName into Courses so each value is stored only once. The Enrollments table then holds only foreign keys (StudentID, CourseID) plus enrollment-specific attributes, eliminating the partial dependencies on composite keys and the transitive dependency of CourseName on CourseID. This design enforces referential integrity via foreign key constraints, preventing orphaned records and reducing update anomalies to a single-row change.
Why this answer
Normalizing the schema into separate Students, Courses, and Enrollments tables eliminates data redundancy by storing each student's name and each course's name only once, while using foreign keys to maintain relationships. This preserves the ability to query all enrollments with student and course details via JOIN operations, which is the standard relational database design principle for reducing anomalies and storage overhead.
Exam trap
The trap here is that candidates confuse views with physical schema changes, thinking a view can magically eliminate redundancy without altering table structure, or they mistakenly believe compression is a substitute for proper normalization.
How to eliminate wrong answers
Option A is wrong because compression reduces storage size but does not eliminate logical data redundancy; repeated StudentName and CourseName values remain, leading to update anomalies and inconsistency risks. Option B is wrong because a view is a virtual table that does not physically store data; creating a view over a single table does not reduce redundancy, and physically storing data in separate normalized tables would require changing the underlying schema, not just adding a view. Option D is wrong because denormalization adds more columns, which increases redundancy and storage waste, contradicting the goal of reducing redundancy.