Normal Form

Database normalization is a process for organizing data to reduce redundancy and avoid update anomalies. 

The truth, the whole truth, and nothing but the truth

or

The key, the whole key, and nothing but the key

1st Normal Form (1NF) - the key

Every column/attribute contains atomic (single) values, and there are no repeating groups.

Each attribute single value, must be dependent on the key. 

Atomic means that each field contains one indivisible value from the perspective of the database—not a set, list, or repeating group of values.

2nd Normal Form (2NF) - the whole key

The table must already be in 1NF, and every non-key attribute must depend on the entire primary key, not just part of it. This issue arises when the primary key consists of multiple columns (a composite key).

Suppose:

Students(StudentID, CourseID, StudentName, CourseName)

+-----------+----------+-------------+------------------+ | StudentID | CourseID | StudentName | CourseName | +-----------+----------+-------------+------------------+ | 1 | C101 | Alice | Database Systems | | 1 | C102 | Alice | Data Structures | | 2 | C101 | Bob | Database Systems | | 3 | C103 | Charlie | Operating Systems| +-----------+----------+-------------+------------------+

Primary key = (StudentID, CourseID)

I can see for each student, what course they are doing. I don't want to update every row/tuple when changing a course name eg.

Problems:

• StudentName depends only on StudentID
• CourseName depends only on CourseID

They do not depend on the whole key.

Convert to 2NF:

Student(StudentID, StudentName)

+-----------+-------------+ | StudentID | StudentName | +-----------+-------------+ | 1 | Alice | | 2 | Bob | | 3 | Charlie | +-----------+-------------+

Courses(CourseID, CourseName)

+----------+-------------------+ | CourseID | CourseName | +----------+-------------------+ | C101 | Database Systems | | C102 | Data Structures | | C103 | Operating Systems | +----------+-------------------+

But now I need to show the courses a student is enrolled in

Enrollments(StudentID, CourseID)

+-----------+----------+ | StudentID | CourseID | +-----------+----------+ | 1 | C101 | | 1 | C102 | | 2 | C101 | | 3 | C103 | +-----------+----------+

With a composite primary key:

PRIMARY KEY (StudentID, CourseID)
And foreign keys:
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
FOREIGN KEY (CourseID)  REFERENCES Course(CourseID)

3nd Normal Form (3NF) - and nothing but the key

The table must already be in 2NF, and non-key attributes cannot depend on other non-key attributes.

Non-key columns should depend only on the primary key, not on another non-key column.

Does any non-key column depend on another non-key column?

Suppose:

Courses(CourseID, CourseName, Room, Building)

+----------+-------------------+----------+----------+ | CourseID | CourseName | Location | Building | +----------+-------------------+----------+----------+ | C101 | Database Systems | NSW | Foty | | C102 | Data Structures | WA | Moss | | C103 | Operating Systems | TAS | Bert | | C104 | Computer Networks | NT | Tres | +----------+-------------------+----------+----------+

If each building has a fixed location, the non-key building attribute is dependent on another non-key attribute location. Then Location is transitively dependent on CourseID through Building. 

Convert to 3NF:

Course(CourseID, CourseName, BuildingID)

Building(BuildingID, Location)

And foreign key:

FOREIGN KEY (BuildingID) REFERENCES Building(BuildingID)

 ****************************************** keywords: 1nf 2nf 3nf codd ****************************************** rdbms version: ******************************************