First, Second, Third, and Boyce-Codd Normal Forms in DBMS

Introduction

A database table can contain valid records while still having a poor design. When information about different entities is stored together, the same fact may appear repeatedly, making updates harder and creating opportunities for inconsistent data.

Database normalization organizes relations according to their dependencies. First, Second, Third, and Boyce–Codd Normal Forms provide increasingly strict conditions for identifying problematic designs and separating facts into suitable tables.

Keys and Dependencies Behind Normalization

Normal forms depend on the meaning of the data and the rules that must hold for every valid database state. A few terms are essential:

• Functional dependency: X → Y means that matching values of X must have matching values of Y. Here, X and Y represent attributes or groups of attributes, and → means “determines.”

• Superkey: A set of attributes that uniquely identifies a row.

• Candidate key: A superkey with no unnecessary attributes. Removing any attribute makes it stop being a superkey.

• Composite key: A key containing more than one attribute. One candidate key is selected as the primary key.

• Prime attribute: An attribute belonging to at least one candidate key. A non-prime attribute belongs to none.

The attributes on the left side of a functional dependency are called its determinant. Normalization examines whether these determinants are appropriate for the facts stored in a relation.

An Example Dataset

Consider a college that initially stores student details and course enrollments together. The following spreadsheet-style table represents records for a single academic term.

Initial student records

Each entry in Enrollments contains a course code followed by its grade. For example, Asha has grade A in DBMS and grade B in OS.

The example follows these business rules:

• Student details: Each StudentID identifies one student with one current name and department.

• Department details: Each DeptID identifies one department with one current name.

• Enrollment details: A student can take several courses, but can enroll in each course only once during the term. Each enrollment has one recorded grade.

• Names: Student names and department names are not assumed to be unique.

These rules determine the dependencies. The displayed records illustrate them; a small sample alone cannot establish which dependencies must always hold.

First Normal Form (1NF)

A relation is in First Normal Form, or 1NF, when each attribute value is atomic: a single value of the intended kind, rather than a collection of separate values.

In the initial records, Asha’s Enrollments cell contains two course–grade pairs. Representing each enrollment as a separate row produces the following relation.

EnrollmentDetails - in 1NF

Each row now describes one student’s enrollment in one course. The candidate key is {StudentID, CourseID}, where braces group the two attributes into one key.

The main functional dependencies are:

• StudentID → Name, DeptID

• DeptID → DeptName

• {StudentID, CourseID} → Grade

A comma on the right side groups attributes determined by the same left side. For example, StudentID → Name, DeptID means that StudentID determines both Name and DeptID.

The relation satisfies 1NF, but student and department details still repeat across enrollments. Atomic values do not automatically remove redundancy.

Second Normal Form (2NF)

A relation is in Second Normal Form, or 2NF, when it is in 1NF and every non-prime attribute depends on the whole of every candidate key.

A partial dependency occurs when a non-prime attribute depends on only part of a candidate key. This condition must be checked against all candidate keys, not just the selected primary key.

In EnrollmentDetails, the key is {StudentID, CourseID}, but Name and DeptID depend on StudentID alone. DeptName also depends on StudentID through its department.

These student details do not need CourseID, so they violate 2NF. Separating them produces two relations.

StudentDepartment

Enrollment

StudentDepartment has the primary key StudentID. Enrollment has the composite primary key {StudentID, CourseID}, and its StudentID references the student record.

Both relations satisfy 2NF under the stated rules. However, StudentDepartment still repeats the fact that department 10 is named Computer Science. That remaining dependency requires another check.

Third Normal Form (3NF)

Third Normal Form, or 3NF, rules out dependencies in which a determinant that is not a superkey determines a non-prime attribute.

More precisely, a relation in 1NF satisfies 3NF if every non-trivial dependency X → A meets at least one of these conditions:

• Superkey determinant: X is a superkey.

• Prime result: A belongs to at least one candidate key.

Here, A is a single attribute. The dependency is non-trivial when A is not already included in X. This formal condition also ensures that the relation satisfies 2NF.

In StudentDepartment, the dependencies form a chain:

StudentID → DeptID → DeptName

This is a transitive dependency: StudentID determines DeptName through DeptID. It violates 3NF because DeptID is not a superkey of StudentDepartment, and DeptName is non-prime.

Separating department information gives the following relations.

Student

Department

The Enrollment relation remains unchanged. Department names are now stored once per department, so changing a department’s name requires updating one department record.

Under the stated dependencies, Student, Department, and Enrollment satisfy both 3NF and BCNF. A separate decomposition is not always necessary for every normal form.

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form, or BCNF, requires the determinant of every non-trivial functional dependency to be a superkey.

Unlike 3NF, BCNF has no exception for a prime attribute on the right side. A separate example makes this difference visible.

Consider a relation named TeachingAssignment for one academic term.

TeachingAssignment

The business rules are:

• Student assignment: Each student has exactly one instructor for each course they take.

• Instructor assignment: Each instructor teaches exactly one course during this term.

• Multiple instructors: A course may have several instructors, and each instructor may teach several students.

These rules give the dependencies:

• {StudentID, CourseID} → InstructorID

• InstructorID → CourseID

There are two candidate keys: {StudentID, CourseID} and {StudentID, InstructorID}. The second works because an instructor identifies the course, while the student identifies whose assignment is being recorded.

Every attribute belongs to a candidate key, so every attribute is prime. Consequently, the relation satisfies 3NF.

However, InstructorID → CourseID violates BCNF. InstructorID does not identify a complete assignment because the same instructor can teach several students.

Decomposing on this dependency produces:

InstructorCourse

StudentInstructor

InstructorCourse has the key InstructorID. StudentInstructor has the composite key {StudentID, InstructorID}. Both relations satisfy BCNF under the stated dependencies.

The relationship between the normal forms is:

BCNF => 3NF => 2NF => 1NF

Here, => means “also satisfies.” The reverse implications do not hold in general.

Lossless Joins and Dependency Preservation

Splitting a relation is useful only when its information and rules remain manageable. Two properties guide the choice of decomposition:

• Lossless join: Joining the decomposed relations reconstructs exactly the original relation, without missing rows or introducing false combinations.

• Dependency preservation: The original functional dependencies can be enforced through constraints on the individual relations, without joining them to check those dependencies.

The TeachingAssignment decomposition is lossless: joining its two relations on InstructorID reconstructs the original assignments.

However, the rule that a student has only one instructor per course now spans both relations. Their individual keys alone cannot enforce it. This decomposition therefore does not preserve every original dependency.

A lossless, dependency-preserving decomposition into 3NF is always possible for a set of functional dependencies. A lossless BCNF decomposition is also possible, but it may sacrifice dependency preservation.

A Compact Example

The following SQL implements the normalized enrollment design. The original 1NF data is repeated here for reference.

EnrollmentDetails - reference data

Each of the following versions for SQL and PostgreSQL creates Department, Student, and Enrollment, inserts the same facts, and joins them to reconstruct the displayed records.

Expected Result

Both the queries return the following four rows, ordered by StudentID and then CourseID.

The aliases s, d, and e represent Student, Department, and Enrollment, respectively. The joins match each enrollment with its student record and then match that student with the corresponding department.

Asha appears twice because she has two enrollments: DBMS and OS. These rows represent different student–course combinations, so they are not duplicate records.

The primary keys uniquely identify each student and department. The composite primary key in Enrollment allows only one record for each student–course pair. The foreign keys ensure that every enrollment references an existing student and every student references an existing department.

The joined result repeats department names for display, but the stored design keeps each department name in one place. Producing repeated values in a query result does not undo normalization.

Practical Points to Remember

• Check every candidate key: Choosing a different primary key does not change the underlying functional dependencies.

• Do not rely on an added ID column: A new artificial identifier does not automatically remove partial dependencies or problematic dependencies between other attributes.

• Keep meaningful relationships: Repeated foreign-key values are expected when several records refer to the same entity.

• Evaluate performance separately: Normalization improves the organization of facts, but it does not guarantee that every query becomes faster. Any deliberate denormalization should have a measured benefit and a plan for maintaining consistency.

Summary

First Normal Form establishes atomic attribute values. Second Normal Form removes partial dependencies of non-prime attributes on candidate keys, while Third Normal Form further restricts dependencies that can cause unnecessary repetition.

Boyce-Codd Normal Form requires every non-trivial determinant to be a superkey. Good normalization combines these conditions with lossless decomposition and attention to dependency preservation, so that a cleaner design retains both its information and its business rules.