Timedora

Relational Database Normalization

How functional dependencies guide relational schema design, reduce anomalies, and help evaluate normalized decompositions.

Facts, Dependencies, and Keys

Why normalization matters

Normalization organizes relational tables so each fact is stored in an appropriate place. This reduces unnecessary duplication and helps prevent update, insertion, and deletion anomalies. It does not eliminate every kind of duplication, and it does not replace database constraints or careful application logic.

Consider a table combining student, course, instructor, and enrollment facts. Assume each student has one name, each course has one name and one instructor, and a student has one grade per course. The following two views show the same enrollment rows; together, they include the student, course, instructor, and grade facts:

Combined table: student and course facts
StudentIDStudentNameCourseIDCourseName
17MinaCS10Databases
17MinaMA20Statistics
22OmarCS10Databases
First view of the example combined table, showing student and course facts for each enrollment.
Combined table: instructor and grade facts
CourseIDInstructorIDInstructorNameGrade
CS104LeeA
MA208PatelB
CS104LeeA-
Second view of the same example rows, showing course, instructor, and grade facts for each enrollment.

The table repeats facts because each row records an enrollment alongside student, course, and instructor information. That creates practical risks:

  • Update anomaly: If Lee’s name changes, every row recording that instructor may need updating. Missing a row leaves conflicting values.

  • Insertion anomaly: A course with no students enrolled may be impossible to record without inventing an enrollment.

  • Deletion anomaly: Removing a course’s last enrollment may also erase the only stored information about that course or its instructor.

The underlying issue is that different facts are determined by different attributes. Dependencies and keys help reveal where those facts belong.

Dependencies and keys

A (FD) expresses a rule about the meaning of the data. In X → Y, any two rows that agree on X must also agree on Y; in other words, X determines Y. For example, if each student has one ID and one name, then StudentID → StudentName. This is a rule of the data model, not a conclusion to draw merely because a small sample happens to show no exceptions.

A is a set of attributes that uniquely identifies each row. A is a minimal , and a is the chosen to identify rows. Keys can be composite: (StudentID, CourseID) may identify an enrollment even though neither attribute alone identifies one. Under the assumptions above, the dependencies include CourseID → CourseName, InstructorID and (StudentID, CourseID) → Grade. These rules distinguish enrollment facts from facts about a student or course.

From 1NF to 2NF

First normal form: one value in each position

A relation is in 1NF when each row-column position contains a single value from the relevant domain, rather than a repeating group. For example, storing several course IDs as a list in one student cell makes it harder to treat each course enrollment as its own fact. Represent repeated items as separate rows or in a related table instead.

“Single value” depends on the application. A system might treat a full address as one value; another might split it into street, city, and postal code because those components need to be searched or managed independently. The design decision should reflect how the information is used, not just the appearance of the value.

Second normal form: depend on the whole key

A relation is in 2NF when it is already in 1NF and every non-key attribute depends on the whole , not merely part of a composite key. If every has just one attribute, this condition is automatically satisfied: there is no smaller part of such a key for a non-key attribute to depend on.

Apply this test to an enrollment relation whose is (StudentID, CourseID). In the source example, StudentName depends only on StudentID, while CourseName depends only on CourseID. Neither fact describes the particular pairing of student and course, so each is a on the composite key. By contrast, the grade is associated with the enrollment pairing and is determined by (StudentID, CourseID) under the stated assumption that a student has one grade per course.

The design implication is to move student-name facts and course-name facts out of the enrollment relation. The enrollment relation can then focus on the student-course pairing and its grade, rather than repeating descriptive information that belongs to one student or one course.

Third Normal Form

When one non-key fact determines another

3NF builds on 2NF and addresses facts determined indirectly through other non-key attributes. In the course example, CourseID → InstructorID and InstructorID → InstructorName. So a course row indirectly determines the instructor’s name. If that name is repeated in every course or enrollment row, changing it can require multiple updates. Store instructor details with the instructor instead of repeating them alongside course facts.

The intuitive rule is that non-key attributes should not depend on other non-key attributes through a . The formal definition is more precise: for every nontrivial X → A, either X is a or A is a prime attribute—an attribute that belongs to some . The prime-attribute alternative matters: the simple “no transitive dependencies” wording does not fully describe every case covered by the formal rule.

To use the formal test, consider each nontrivial dependency one at a time. Ask first whether its determinant X uniquely identifies rows. If it does, it is a and satisfies the condition. If it does not, the dependency still satisfies 3NF when its dependent attribute A belongs to a . This test complements, rather than replaces, the earlier checks: the relation must already satisfy 2NF.

Decomposition, Constraints, and BCNF

Give each fact a natural home

The example can be decomposed into four relations:

  • Student(StudentID, StudentName)

  • Instructor(InstructorID, InstructorName)

  • Course(CourseID, CourseName, InstructorID)

  • Enrollment(StudentID, CourseID, Grade)

Now student names are stored with students, instructor names with instructors, course names and instructor assignments with courses, and grades with enrollments. The StudentID and CourseID values in Enrollment refer to their respective relations; InstructorID in Course refers to Instructor.

Decomposition needs more than a reduction in repeated values. Check two properties:

  1. Lossless reconstruction: Joining the new relations on their shared keys should reconstruct the original information without losing facts or creating spurious rows.

  2. : Important dependencies should remain enforceable by checking individual relations, rather than requiring complex joins.

A design that reduces redundancy but loses information or makes essential rules difficult to enforce may be unsuitable. Primary-key and unique constraints can enforce key rules, while foreign-key constraints can enforce references between relations. Normalization and constraints work together: a well-organized schema still needs rules that protect row identity and references.

BCNF: a stricter determinant test

BCNF is stricter than 3NF. For every nontrivial , its determinant must be a . This can remove some redundancy that 3NF permits. However, decomposing a schema into BCNF may fail to preserve every dependency. Therefore, do not choose a decomposition solely because it has the stronger normal-form label: consider the actual data rules and whether the needed constraints can be enforced effectively.

A Practical Design Workflow

A practical normalization workflow

Use the normal forms as part of a design process, not as a substitute for understanding the domain:

  1. Identify entities and relationships. Establish the business rules from the domain; do not infer them from a small sample of rows alone.

  2. List attributes, candidate keys, and functional dependencies. For each fact, ask what determines it. These rules help reveal whether attributes describe an entity, a relationship, or something else.

  3. Check row identity and 1NF. Look for repeating groups, multi-valued fields, and unclear row identity. Define keys and ensure each row-column position holds one value appropriate to the application.

  4. Check composite keys for 2NF. Determine whether any non-key attribute depends on only part of a . Separate such partial dependencies from the relation describing the whole key.

  5. Check for 3NF violations. Ask whether non-key attributes determine other non-key facts. Separate facts that depend transitively, while remembering the formal 3NF test involving superkeys and prime attributes.

  6. Validate the decomposition. Confirm that joins reconstruct the intended information losslessly, important dependencies remain enforceable, and appropriate key and reference constraints protect the relations.

  7. Review the workload. Consider real queries and how the database is used. If performance later justifies deliberate , document which facts are duplicated and provide a reliable way to keep them consistent.

The goal is not to eliminate every repeated value or to maximize a normal-form label in isolation. A useful design places facts appropriately, preserves information, and supports the rules and workload the system must handle.

  • : A normal-form requirement that every row-column position contains one value from its relevant domain, rather than a repeating group.

  • : A normal-form requirement that every non-key attribute depends on the whole , not just part of a composite key.

  • : A normal-form condition: for every nontrivial X → A, either X is a or A is prime.

  • : A property of a decomposition where joining the new relations on shared keys reconstructs the original information correctly.

  • : A normal form in which, for every nontrivial , the determinant is a .