Normalization

Student Table (Unnormalized Form – UNF)

Student_ID	Student_Name	Course	Instructor	Instructor_Office
101	John Doe	Math	Dr. Smith	Room 101
101	John Doe	Physics	Dr. Brown	Room 202
102	Alice White	Math	Dr. Smith	Room 101
103	Bob Johnson	Physics	Dr. Brown	Room 202
103	Bob Johnson	Chemistry	Dr. Green	Room 303

First Normal Form (1NF)

Rules for 1NF:

• The table should have a primary key.
• Each column should contain atomic (indivisible) values.
• No repeating groups or arrays.

Modified Table in 1NF:

Student_ID	Student_Name	Course	Instructor	Instructor_Office
101	John Doe	Math	Dr. Smith	Room 101
101	John Doe	Physics	Dr. Brown	Room 202
102	Alice White	Math	Dr. Smith	Room 101
103	Bob Johnson	Physics	Dr. Brown	Room 202
103	Bob Johnson	Chemistry	Dr. Green	Room 303

Now the table is in 1NF because each column contains atomic values, and there are no repeating groups.

---

Second Normal Form (2NF)

Rules for 2NF:

• The table must be in 1NF.
• Remove partial dependencies (a non-key column must be fully dependent on the entire primary key).

In the above table, Instructor and Instructor_Office depend only on Course, not on Student_ID. To fix this, we split the table into two:

Student_Course Table (2NF)

Student_ID	Student_Name	Course
101	John Doe	Math
101	John Doe	Physics
102	Alice White	Math
103	Bob Johnson	Physics
103	Bob Johnson	Chemistry

Course_Instructor Table (2NF)

Course	Instructor	Instructor_Office
Math	Dr. Smith	Room 101
Physics	Dr. Brown	Room 202
Chemistry	Dr. Green	Room 303

Now, all non-key attributes are fully dependent on the primary key in their respective tables.

---

Third Normal Form (3NF)

Rules for 3NF:

• The table must be in 2NF.
• Remove transitive dependencies (a non-key column must not depend on another non-key column).

In the Course_Instructor Table, Instructor_Office depends on Instructor, not on Course. To fix this, we create a separate Instructor Table:

Course_Instructor Table (3NF)

Course	Instructor
Math	Dr. Smith
Physics	Dr. Brown
Chemistry	Dr. Green

Instructor Table (3NF)

Instructor	Instructor_Office
Dr. Smith	Room 101
Dr. Brown	Room 202
Dr. Green	Room 303

Now, the database is in 3NF because all non-key attributes depend only on the primary key and not on another non-key attribute.

---

Final Tables in 3NF

1. Student_Course Table (Student_ID → Course)
2. Course_Instructor Table (Course → Instructor)
3. Instructor Table (Instructor → Instructor_Office)