Tell me about normal forms (1NF, 2NF, 3NF)

Normal forms are rules for organizing tables in a relational database that help reduce data duplication and improve integrity. The main ones are 1NF, 2NF, and 3NF.

#1NF (First Normal Form)

• The rule: every field in the table must be atomic, meaning it can't be split into parts, and every record is unique.
• What this means in practice: you can't store several values in one field, e.g. "Phones = 12345, 67890". Every value needs to be its own record.
• An example that violates 1NF:

• Correct (1NF): create a separate Phones table with EmployeeID and Phone fields.

#2NF (Second Normal Form)

• The rule: the table must be in 1NF, and every field must fully depend on the primary key, not just part of it (this matters for composite keys).
• What this means in practice: if the primary key is made of two columns, a separate column shouldn't depend on only one of them.
• An example that violates 2NF: A table with the key (StudentID, CourseID) and a StudentName field. StudentName depends only on StudentID, not on the whole key.
• The fix: move StudentID and StudentName into a separate Students table.

#3NF (Third Normal Form)

• The rule: the table must be in 2NF, and there's no transitive dependency, meaning a field shouldn't depend on another field besides the primary key.
• What this means in practice: you shouldn't store data that can be computed or looked up through another field.
• An example that violates 3NF: | EmployeeID | DepartmentID | DepartmentName | DepartmentName depends on DepartmentID, not directly on EmployeeID.
• The fix: create a Departments table with DepartmentID and DepartmentName, and keep only DepartmentID in the Employees table.

Put simply:

• 1NF: fields are atomic, no repeating groups.
• 2NF: every field fully depends on the primary key.
• 3NF: no field depends on another non-key field.