Normalization 1NF to 3NF – SQL Server Database Design | Lesson 7
Introduction
The school office keeps one big spreadsheet called "Register". Every student is one row, and every row repeats the class teacher's name and the guardians' phone numbers.
In this lesson, you clean that spreadsheet step by step until you reach the SMS tables. The cleaning method is called normalization.
By the end of this lesson, you will be able to:
- Spot update, insert, and delete anomalies in a table.
- Apply first, second, and third normal form (1NF, 2NF, 3NF).
- Explain partial and transitive dependencies in plain words.
- Decide when copying data on purpose (denormalization) is the right choice.
Why this matters
Last term, the class teacher of 10-A changed from Anitha Rao to a new teacher. The office updated 38 rows of the register and missed 2. Now the register says 10-A has two class teachers, and nobody knows which rows are right.
That is not a typing problem. It is a design problem: the same fact was stored 40 times. Normalization makes sure each fact is stored once, in one place.
The concept
Normalization is a set of rules for splitting tables so that every fact is stored exactly once. Each rule is called a normal form. A table that passes a rule is "in" that normal form.
Think of a phone contacts list. If you type a friend's number into every group chat by hand, changing it means hunting through every chat.
If you save it once in your contacts, every chat uses that one copy. Normalization does the same for data.
Example: the flat register
| AdmissionNumber | StudentName | ClassSection | ClassTeacher | Guardians | GuardianPhones |
|---|---|---|---|---|---|
| ADM-2017-0001 | Aarav Sharma | 10-A | Anitha Rao | Rajesh Sharma, Neha Sharma | 9876540101, 9876540102 |
| ADM-2017-0002 | Diya Patel | 10-A | Anitha Rao | Suresh Patel, Kavita Patel | 9876540103, 9876540104 |
| ADM-2021-0001 | Advika Sharma | 6-A | Kavya Menon | Rajesh Sharma, Neha Sharma | 9876540101, 9876540102 |
Example: the three anomalies
An anomaly is a problem that happens because the same fact is stored in many places. There are three kinds:
| Anomaly | What goes wrong in the register |
|---|---|
| Update anomaly | Rajesh Sharma's phone changes. You must update two rows. Miss one, and the data disagrees. |
| Insert anomaly | A new section 10-C gets a class teacher. You cannot record it until a student joins 10-C. |
| Delete anomaly | Diya leaves and her row is deleted. Suresh Patel's phone number is gone too. |
Normalization removes all three.
Apply it to the SMS
Take the register through the three normal forms.
Example: first normal form (1NF)
A table is in 1NF when:
- Every cell holds one value, not a list.
- There are no repeating columns, such as
Guardian1,Guardian2,Guardian3. - Every row can be identified by a key.
The Guardians and GuardianPhones cells hold lists, so the register breaks 1NF.
Split the lists into one row per student and guardian:
| AdmissionNumber | StudentName | ClassSection | ClassTeacher | GuardianName | GuardianPhone |
|---|---|---|---|---|---|
| ADM-2017-0001 | Aarav Sharma | 10-A | Anitha Rao | Rajesh Sharma | 9876540101 |
| ADM-2017-0001 | Aarav Sharma | 10-A | Anitha Rao | Neha Sharma | 9876540102 |
| ADM-2017-0002 | Diya Patel | 10-A | Anitha Rao | Suresh Patel | 9876540103 |
| ADM-2017-0002 | Diya Patel | 10-A | Anitha Rao | Kavita Patel | 9876540104 |
| ADM-2021-0001 | Advika Sharma | 6-A | Kavya Menon | Rajesh Sharma | 9876540101 |
| ADM-2021-0001 | Advika Sharma | 6-A | Kavya Menon | Neha Sharma | 9876540102 |
Every cell now holds one value. The key is the pair (AdmissionNumber, GuardianName). A key made of two or more columns is called a composite key.
But look: there is even more repetition now. That is what 2NF fixes.
Example: second normal form (2NF)
A table is in 2NF when it is in 1NF and every non-key column depends on the whole key, not just part of it.
Ask about each column: "Does it depend on the student, the guardian, or both?"
StudentName,ClassSection,ClassTeacherdepend only onAdmissionNumber.GuardianPhonedepends only on the guardian.- Only the link itself ("Rajesh is Aarav's father") depends on both.
A column that depends on only part of a composite key is a partial dependency. Remove it by moving each group to its own table:
| Students | Guardians | StudentGuardians |
|---|---|---|
| AdmissionNumber, StudentName, ClassSection, ClassTeacher | GuardianId, GuardianName, Phone | AdmissionNumber, GuardianId, Relationship |
Rajesh Sharma's phone is now stored once, in Guardians. That fixes the update
anomaly for phones, and Diya leaving no longer deletes her parents' details.
Example: third normal form (3NF)
A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column.
Look at Students again: ClassTeacher does not describe the student; it describes
the section. The chain is: student, then section, then class teacher.
A column that depends on the key only through another column is a transitive dependency.
Move the class teacher to where it belongs. A class teacher is assigned to a
section for one year, so it gets its own table, ClassTeachers. In the same way,
the student's section changes every year, so it moves to Enrollments:
| Table | Columns | Stores |
|---|---|---|
| Students | Id, AdmissionNumber, FirstName, LastName | Who the student is |
| Guardians | Id, FirstName, LastName, Phone | Who the guardian is |
| StudentGuardians | StudentId, GuardianId, Relationship | Who cares for whom |
| Sections | Id, ClassId, Name | Which sections exist |
| Enrollments | Id, StudentId, AcademicYearId, SectionId, RollNumber | Who is in which section, each year |
| ClassTeachers | Id, AcademicYearId, SectionId, TeacherId | Who is class teacher of which section, each year |
Example: the normalized result
Run the three anomalies again on the new design:
| Anomaly | Before | After |
|---|---|---|
| Update | Change the class teacher in 40 rows | Change one row in ClassTeachers |
| Insert | Cannot record 10-C's class teacher without a student | Insert one row in ClassTeachers |
| Delete | Deleting Diya deletes her parents' phones | Guardians stay in Guardians |
A quick test for 3NF, often quoted by database teachers: every non-key column must depend on "the key, the whole key, and nothing but the key".
Example: when to denormalize
Denormalization means storing a copy of data on purpose, usually for speed. It is a deliberate choice, not a mistake, but you must keep the copy correct.
The SMS has one such copy already: FeeInvoices.TotalAmount. The total could be
calculated from FeeInvoiceItems every time. We store it because:
- Fee lists show thousands of invoices, and adding up items for each is slow.
- An invoice's total must never change after it is issued, even if fee prices change.
The rule that keeps it safe: the total is written in the same step as its items, and nothing else may edit it. You will build that step with a transaction in Module 5.
Common mistakes
- Storing lists in one cell. "ENG, MATH, SCI" cannot be searched or counted. Fix: one row per value (1NF).
- Repeating descriptive data in every row. The class teacher's name on every student row will drift apart. Fix: store it once and link to it (3NF).
- Normalizing so far that simple screens need ten joins. Fix: stop at 3NF for everyday tables; it is the standard target for business systems.
- Denormalizing without a plan to keep copies correct. Fix: write down who updates the copy and when, before you add it.
In real projects
Most production bugs I have traced to "wrong data" were really normalization problems. A customer's address was stored on every order, the customer moved, and reports showed both addresses.
The fix was never a code patch. It was moving the fact to one place and migrating years of rows.
I also denormalize, but only with a written reason and an owner. A daily summary table for a dashboard is fine if one scheduled job rebuilds it. A copied column that "everyone" updates is a bug waiting to happen.
Best practices
- Aim for 3NF in every table that users edit.
- Ask of each column: "Does it describe this table's key, and only that?"
- Model facts that change by year in their own table with an
AcademicYearId. - Denormalize only with a measured reason and a clear owner for the copy.
Recap
- Normalization stores every fact once to avoid update, insert, and delete anomalies.
- 1NF: one value per cell, no repeating columns, a key for every row.
- 2NF: no partial dependency on part of a composite key.
- 3NF: no transitive dependency through another non-key column.
- Changing facts per year, like sections and class teachers, get their own tables.
- Denormalize on purpose, for speed, with a rule that keeps the copy correct.
Homework
Library module task: The librarian's register has these columns: IssueId, BookTitle, Author, Isbn, CopyNumber, StudentName, StudentClass, IssuedOn, ReturnedOn. Normalize it to 3NF. For every column you move, write which normal form required it.
A correct result looks like: separate tables for books, copies, and issues, with
the student linked by StudentId and no class stored in the library tables.
Show the solution
| Table | Columns | Why |
|---|---|---|
| Books | Id, Isbn, Title, Author | Title and Author depend on the book, not the issue (3NF) |
| BookCopies | Id, BookId, CopyNumber | A copy is a separate thing from the book |
| BookIssues | Id, BookCopyId, StudentId, IssuedOn, ReturnedOn | One row per issue |
StudentName and StudentClass are removed: they depend on the student, not the
issue (3NF). The name lives in Students, and the class comes from Enrollments.
Storing the class here would also be wrong next year, when the student moves up.
Quiz
A guardian's phone is stored on two rows and only one is updated. Which anomaly is this?
Show answer
Answer: B. Update anomaly The same fact stored twice became inconsistent after an update.
Which rule does a cell containing 'Rajesh Sharma, Neha Sharma' break?
Show answer
Answer: A. 1NF 1NF requires one value per cell.
In a table keyed by (AdmissionNumber, GuardianName), StudentName depends only on AdmissionNumber. What is this?
Show answer
Answer: B. A partial dependency It depends on only part of the composite key, which 2NF removes.
Why is ClassTeacher on the Students table a 3NF problem?
Show answer
Answer: B. It depends on the section, not on the student It depends on the key only through another column (the section): a transitive dependency.
Why does the SMS store TotalAmount on FeeInvoices even though it can be calculated?
Show answer
Answer: B. For speed and so an issued invoice never changes It is a deliberate denormalization, written together with the items and never edited separately.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: System Design interview questions, SQL Server interview questions.
Fresher (0-2 yrs)
F What is normalization, and why do we do it? concept
Normalization is a set of rules for splitting tables so that every fact is stored once. It prevents update, insert, and delete anomalies, where copies of the same fact disagree or are lost. The usual target for business systems is third normal form.
F What is first normal form (1NF)? concept
A table is in 1NF when every cell holds a single value, there are no repeating columns
such as Guardian1 and Guardian2, and every row can be identified by a key. A cell
containing "Rajesh Sharma, Neha Sharma" breaks 1NF.
F What are update, insert, and delete anomalies? concept
An update anomaly: a fact stored twice is changed in one place only, so copies disagree. An insert anomaly: you cannot record one fact without another, such as a class teacher without a student. A delete anomaly: deleting one row removes an unrelated fact, such as a guardian's phone.
Mid (3-5 yrs)
M Explain second normal form with an example. concept
A table is in 2NF when it is in 1NF and every non-key column depends on the whole key.
In a table keyed by (AdmissionNumber, GuardianName), StudentName depends only on the
admission number: a partial dependency. Moving student details to Students and guardian
details to Guardians fixes it.
M Explain third normal form with an example. concept
A table is in 3NF when it is in 2NF and no non-key column depends on another non-key
column. ClassTeacher on Students depends on the section, which depends on the
student: a transitive dependency. Moving the class teacher to ClassTeachers (per
section and year) fixes it.
M What is the difference between a partial dependency and a transitive dependency? concept
A partial dependency is a column that depends on only part of a composite key; 2NF removes it. A transitive dependency is a column that depends on the key only through another non-key column; 3NF removes it. Short test: a column must depend on "the key, the whole key, and nothing but the key".
Senior (6-9 yrs)
S When would you denormalize, and how do you keep the copied data correct? concept
Denormalize when a measured performance or history need justifies a copy, such as an
invoice total stored on FeeInvoices. Keep it correct with one owner: write it in the
same transaction as the source rows, block other updates, or rebuild it on a schedule.
Without an owner, copies drift.
How to answer: Make clear denormalization is a deliberate trade-off, then give the SMS invoice total example with both reasons (speed, and a total that must not change after issue). Name the cost: more code paths to keep in sync, and the risk of drift if anyone forgets.
S The fee report joins six tables and is slow. A developer wants to copy the student name onto FeePayments. What do you say? scenario
First measure: most slow joins are missing indexes or return too many rows, and copying the name fixes neither. If the report is still slow after indexing, consider a reporting view or summary table rebuilt on a schedule. Copying names onto payments creates a second place to update whenever a name is corrected.
How to answer: Show you diagnose before redesigning: check the execution plan and indexes. Explain why this particular copy is risky (names get corrected and the copy goes stale). Offer the better options in order of cost: indexes, a narrower query, then a reporting table with a clear refresh owner.
Architect / Lead (10+ yrs)
A How would you separate day-to-day school operations from heavy reporting when the SMS serves 1,000 schools? design
Keep the normalized transactional database for daily work such as attendance and fees. Send reporting to a separate copy built for reading: a read replica for simple reports, or a data warehouse with denormalized tables refreshed on a schedule. Heavy reports then never slow down a teacher saving attendance.
How to answer: Name the two workloads (many small writes vs few large reads) and why one design cannot serve both well. Describe the options from cheapest to most powerful: indexed views, read replicas, then a warehouse. State the trade-off: reports become slightly delayed (eventual consistency) in exchange for fast, safe daily operations.
Resources
- Code for this lesson: git tag
lesson-7-normalization(code repository coming soon) - Video: coming soon
What's next
You know which tables and columns exist. Next, you choose the exact data type for every column. Lesson 8: Choosing data types
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.