Database Normalization: 1NF to 4NF Explained

First Normal Form: Eliminating Repeating Groups

To achieve First Normal Form (1NF), you must remove repetitive groups. This process involves two main steps:

• Repeating groups must generate a new relationship that contains the primary key of the original relationship as an attribute, alongside fields that have no repeating groups.
• Form a composite key consisting of the primary key from the original relationship and the key attributes of the repeating group, including all fields that make up the repeating group.

Second Normal Form: Removing Partial Dependencies

To achieve Second Normal Form (2NF), you must remove partial dependencies. This requires:

1. Identifying a composite key and the attribute fields that depend solely on a portion of it.
2. Creating a relation whose primary key is the part of the composite key generating the partial dependence, including the attributes and fields that depend on it.

Third Normal Form: Removing Transitive Dependencies

To achieve Third Normal Form (3NF), you must remove transitive dependencies:

• Identify the primary key which corresponds to the field generated by the transitive dependency and the attributes determined by it.
• Generate a relation in 2NF where attributes are functionally dependent on the primary key. The attribute that generates the transitive dependency is known as a foreign key.

Boyce-Codd Normal Form (BCNF)

A relationship is in BCNF if it is already in 3NF; however, a relation in 3NF is not necessarily in BCNF. A relation R is in BCNF if and only if every determinant is a candidate key.

• Case 1: When an entity has a composite key, anomalies may still exist.
• Case 2: Incorporating additional restrictions to a specific situation can lead to unnecessary redundancy.

Example: Tutoring System

1. Each student may have several subjects of interest.
2. For each subject, a student has only one tutor.
3. Each course has a number of tutors.
4. Each tutor works alone in a specific oriented field.
5. Each tutor guides several students in a subject.

TUTORING (student_id, subject, tutor)
student_id-tutor, tutor-subject

Fourth Normal Form: Multi-Valued Dependencies

Fourth Normal Form (4NF) handles anomalies with respect to multi-valued dependencies. This occurs when there are three attributes (A, B, C), and for each value of A, there is a set of values for B and C, but B and C are independent of each other.

Example: Course Management

1. Each course can have several instructors.
2. Each course uses various texts.
3. The text used for a certain course is independent of the instructor.

COURSE (course, instructor, book)
course-instructor, course-book