AQU Course (2): Database System Design

Database normalization is a series of steps to design a database schema that reduces redundancy and dependency by organizing fields and tables. These steps are known as "normal forms." Here’s a breakdown of the most commonly used normal forms with examples:

First Normal Form (1NF):

Definition: A table is in 1NF if:

• All columns contain atomic (indivisible) values.
• Each column contains values of a single type.
• Each record (row) is unique.

Example: Consider a table storing information about students and their enrolled courses:

StudentID	StudentName	Courses
1	Alice	Math, Science
2	Bob	Math, English, History

Issue: The Courses column contains multiple values, violating 1NF.

1NF Solution: Split the data so that each course is stored in a separate row.

StudentID	StudentName	Course
1	Alice	Math
1	Alice	Science
2	Bob	Math
2	Bob	English
2	Bob	History

Second Normal Form (2NF):

Definition: A table is in 2NF if:

• It is in 1NF.
• All non-key attributes are fully functionally dependent on the entire primary key (no partial dependencies).

Example: Consider the following table after applying 1NF:

StudentID	Course	Instructor
1	Math	Dr. Smith
1	Science	Dr. Jones
2	Math	Dr. Smith

Issue: The Instructor column is dependent on Course rather than the entire primary key (StudentID, Course), leading to redundancy if the same course is taught by the same instructor to multiple students.

2NF Solution: Separate the table into two tables: one for student-course relationships and another for course-instructor relationships.

Student-Course Table:

StudentID	Course
1	Math
1	Science
2	Math

Course-Instructor Table:

Course	Instructor
Math	Dr. Smith
Science	Dr. Jones

Third Normal Form (3NF):

Definition: A table is in 3NF if:

• It is in 2NF.
• All attributes are directly dependent on the primary key (no transitive dependencies).

Example: After achieving 2NF, consider the following Course table:

CourseID	CourseName	Department
1	Math	Science
2	English	Humanities
3	History	Humanities

Issue: The Department column depends on CourseName, which depends on CourseID, creating a transitive dependency.

3NF Solution: Separate the department information into its own table.

Course Table:

CourseID	CourseName
1	Math
2	English
3	History

Department Table:

CourseName	Department
Math	Science
English	Humanities
History	Humanities

Summary

• 1NF ensures atomicity by eliminating multi-valued attributes.
• 2NF removes partial dependencies, ensuring that all non-key attributes depend on the entire primary key.
• 3NF eliminates transitive dependencies, ensuring that non-key attributes depend only on the primary key.

By applying these normal forms, you create a more efficient and organized database that reduces redundancy and improves data integrity.