01 | WHY NORMALIZE?

A robotics club keeps one spreadsheet for students, projects and mentors. It works until a mentor changes email address. The address appears on ten rows, and someone updates only nine. Which version should the club trust?

Normalization organizes relational tables according to their dependencies. The aim is to reduce unnecessary duplication and the problems it creates, while preserving the facts and relationships we need.

• Update anomaly: repeated facts must be changed in several places, risking inconsistent versions.
• Insertion anomaly: a fact cannot be recorded without an unrelated fact—for example, a new project cannot be stored until a student joins it.
• Deletion anomaly: deleting one fact accidentally removes another—for example, deleting the last enrolment also removes the only record of a project.

“Missing data” can therefore mean a fact cannot be entered or has been accidentally lost. A NULL value is not, by itself, proof that a table violates a normal form. Normalization improves structure; it does not automatically repair incomplete or inaccurate records.

02 | BEFORE WE START: KEYS AND DEPENDENCIES

A field or attribute is a column, such as StudentName. A record is a row. A key lets us identify a particular row without confusing it with another.

Why not use a name? Two students could both be called Alex. A name therefore does not reliably identify a student. The club gives each student a unique StudentID.

A candidate key is a possible choice of key: it must be unique and minimal. “Minimal” means no attribute can be removed while keeping uniqueness. If club rules guarantee that both StudentID and SchoolEmail are individually unique, each is a candidate key. StudentName is not.

We choose StudentID as the primary key (PK). SchoolEmail remains another candidate key. The combination (StudentID, SchoolEmail) is unique, but it is not minimal because StudentID alone already identifies the student. The email field above is only a key illustration; our worked normalization example uses StudentID and StudentName.

A composite key needs more than one field. In ENROLMENT, StudentID repeats because a student can join several projects. ProjectID repeats because a project can have several students. Together, (StudentID, ProjectID) identifies one student’s enrolment in one project. We assume at most one enrolment for each pair.

A foreign key (FK) refers to a key in another table. ENROLMENT.StudentID points to STUDENT.StudentID. It lets us connect the enrolment to the correct student without copying the student’s details.

What does “depends on” mean? Ask: “If I know this value, does it determine exactly one value of that field?” Knowing StudentID S01 determines the name Alex. StudentName therefore depends on StudentID. We write StudentID → StudentName and read it as “StudentID determines StudentName”.

This is a functional dependency. It describes a rule about values—not a calculation, a processing order, or which column comes first. Knowing Alex does not determine one StudentID because two students may share that name.

These dependencies follow our club’s rules, not just coincidences in the sample rows. If the rules change—for example, one project can have several mentors—the design must change too.

03 | FIRST NORMAL FORM: ONE VALUE PER CELL

1NF requires atomic values and no repeating groups. Atomicity here means that a cell contains one value from the attribute’s domain, not a list of values. It is different from transaction atomicity in ACID.

For S01, the final cell contains two project–role pairs. To search or update one enrolment, we would have to unpack the list. Move each pair into its own row and give each field one value. Add the related project and mentor fields to produce the 1NF table below.

A cell containing ProjectIDs “P10, P20” is not atomic when the database needs to treat each enrolment separately. Columns Project1, Project2 and Project3 are a repeating group. Instead, store one enrolment per row and identify it with a key.

Atomic does not mean “one word” or “one character”. A project name such as “Robot hand” can be one value. Whether an address needs separate fields depends on what the application must do with it.

Every cell is a single value, and each enrolment is uniquely identified. However, student and project details still repeat. 1NF is the starting point, not a promise that redundancy has disappeared.

04 | SECOND NORMAL FORM: DEPEND ON THE WHOLE KEY

A None-prine attribute does not belong to any candidate key.

2NF means the table is in 1NF and has no partial-key dependencies of non-key attributes. More precisely, every non-prime attribute must depend on the whole of each candidate key, not just a proper subset. A non-prime attribute is one that belongs to no candidate key.

In our table, the key is (StudentID, ProjectID), but StudentName depends on StudentID alone. ProjectName and MentorID depend on ProjectID alone. These are partial dependencies. Role needs both identifiers, so it is a full dependency on the composite key.

Separate the student facts, project facts and enrolment facts:

Follow what moved: take StudentID and StudentName into STUDENT because a name depends only on the student. Take ProjectID, ProjectName, MentorID and MentorEmail into PROJECT because these details depend on the project. Keep both IDs and Role in ENROLMENT because a role belongs to a particular student–project pair.

Why is PROJECT not yet 3NF? The sample happens to show a different mentor for each project. But club rules allow one mentor to supervise several projects. Their email would then repeat in PROJECT. Normal forms depend on the rules, not whether today’s sample contains repetition.

The two ENROLMENT fields jointly form its primary key, and each is also a foreign key linking to its parent table. Now a student’s name is stored once, and an unassigned project can be recorded without inventing an enrolment.

05 | THIRD NORMAL FORM: REMOVE TRANSITIVE DEPENDENCIES

3NF builds on 2NF. In the straightforward designs used here, non-key attributes must not depend on the key through another non-key attribute. This is a transitive dependency.

The PROJECT table still stores MentorEmail. ProjectID determines MentorID, and MentorID determines MentorEmail. Thus the email depends indirectly on ProjectID:

If one mentor supports several projects, their email repeats. Move the mentor’s details into a MENTOR table, keeping MentorID in PROJECT as a foreign key:

Follow the final move: Student and Enrolment stay as they were. Remove MentorEmail from PROJECT and store it with MentorID in MENTOR. Keep MentorID in PROJECT so we still know which mentor supervises each project.

Can we still find everything? Start with ENROLMENT (S01, P10, Coder). S01 links to Alex in STUDENT. P10 links to Robot hand and mentor M01 in PROJECT. M01 links to lee@example.org in MENTOR. The facts are separated, but the identifiers let us put the information together again.

If M01 later supervises another project, that project stores M01 as its foreign key. We do not copy the email. A changed email needs one update in MENTOR.

A mentor’s email now has one home. Tables remain connected through keys, so joins can reconstruct the needed student–project–mentor information. Splitting tables should preserve information, not break relationships.

06 | COMPARE THE NORMAL FORMS

A useful question sequence is: “One value? Whole key? No indirect non-key dependency?” Always identify the keys and dependencies before deciding the highest normal form. A small sample may hide repetition that will appear later.

Normalization does not remove every repeated value: foreign keys legitimately appear in several rows. It removes unnecessary repetition of facts and the dependencies that cause anomalies.

07 | MULTI-VALUED DATA AND OTHER DEPENDENCY CONCERNS

A multi-valued cell contains a list, such as Skills = “Python, CAD”. When those skills need separate handling, a related STUDENT_SKILL table with one skill per row supports 1NF.

A multi-valued dependency is a different issue. Suppose a student has several skills and several hobbies, and the two sets are independent. A table (StudentID, Skill, Hobby) may require every skill–hobby combination, creating redundant rows even though each cell is atomic.

Separate STUDENT_SKILL(StudentID, Skill) and STUDENT_HOBBY(StudentID, Hobby) when the sets are genuinely independent. This is associated with 4NF, beyond the three normal forms being compared here. Do not claim that 1NF–3NF automatically remove every multi-valued dependency.

Likewise, adding an artificial ID does not remove the original dependencies. You must examine all candidate keys and the data’s meaning, not simply rename the primary key.

08 | EXPLAIN, DON’T JUST LABEL

To explain a normal form, name its requirements, show the dependency or structural problem, and describe the change that resolves it. “Split the table” is not enough: identify which attributes move, what their key is, and how the tables remain linked.

For example: “StudentName depends only on StudentID, part of the composite key, so the table violates 2NF. Move StudentID and StudentName into STUDENT and retain StudentID in ENROLMENT as a foreign key.”

Further reading: Microsoft’s database design guide and IBM’s normalization overview.