Lesson 21: Normal Forms: 1NF, 2NF, 3NF¶
Removing repeating groups, partial dependencies and transitive dependencies
About this lesson
Course: Database Management Systems
Unit 5: Database Design · 10 Lecture Hours
Lesson duration: 2 lectures
Reading: Elmasri & Navathe, Ch. 14.3, 14.4
Learning Outcomes
- Define prime and non-prime attributes.
- Test a relation for 1NF, 2NF and 3NF, and name the exact violating dependency.
- Distinguish partial from transitive dependencies.
- Decompose a relation to 2NF and then to 3NF.
- State both equivalent definitions of 3NF.
21.1 Why Normalise¶
Unit 3, Lesson 1 showed that a relation storing facts about more than one kind of thing suffers insertion, deletion and modification anomalies. Normalisation is the formal, mechanical procedure for detecting that situation and repairing it.
Normalisation
The process of analysing a relation schema against its functional dependencies to minimise redundancy and eliminate update anomalies, by decomposing it into smaller relations that satisfy progressively stricter conditions called normal forms.
Introduced by E. F. Codd — 1NF, 2NF and 3NF in 1971–72; BCNF with Boyce in 1974.
1NF atomic values, no repeating groups
|
| remove PARTIAL dependencies
v
2NF no non-prime attribute depends on part of a candidate key
|
| remove TRANSITIVE dependencies
v
3NF no non-prime attribute depends on another non-prime attribute
|
| remove ALL dependencies whose determinant is not a superkey
v
BCNF every determinant is a superkey (Lesson 4)
Each form is STRICTLY STRONGER: every 3NF relation is in 2NF,
every 2NF relation is in 1NF.
Prime and non-prime — learn this first
An attribute is prime (a key attribute) if it belongs to some candidate key.
An attribute is non-prime if it belongs to no candidate key.
Note "some", not "all". If the candidate keys are {A} and {B,C}, then A, B and C are all prime.
Every 2NF and 3NF test is stated in terms of non-prime attributes, so you must find all candidate keys first. This is why Lesson 2 came before this one.
21.2 First Normal Form (1NF)¶
1NF
A relation is in 1NF if the domain of every attribute contains only atomic (indivisible) values, and the value of any attribute in a tuple is a single value from that domain.
Equivalently: no composite attributes, no multivalued attributes, no nested relations, no repeating groups.
1NF is not really a normalisation step so much as the definition of a relation (Unit 3, Lesson 1, Characteristic 3). A structure violating it is not a relation at all.
Violation and repair¶
Not in 1NF — a multivalued attribute:
| Rollno | Name | Courses |
|---|---|---|
| X2019 | Ankit Sharma | CS01, MA02 |
| X2020 | Riya Verma | CS01 |
Three ways to repair, only one of which is correct.
Method 1 — a separate relation (correct). Move the multivalued attribute out, taking the key with it. This is exactly mapping step 6 of Lesson 1.
STUDENT(Rollno, Name) and STUDENT_COURSE(Rollno, SID) with key (Rollno, SID):
| Rollno | Name |
|---|---|
| X2019 | Ankit Sharma |
| X2020 | Riya Verma |
| Rollno | SID |
|---|---|
| X2019 | CS01 |
| X2019 | MA02 |
| X2020 | CS01 |
Method 2 — flatten into one relation (poor). Repeat Name on every row and make (Rollno, SID) the key. It is in 1NF but introduces exactly the redundancy that 2NF will then have to remove.
Method 3 — one column per value (wrong). Course1, Course2, Course3. This fixes the maximum number in advance, wastes NULLs, and makes "who takes CS01?" require searching every column. Never do this.
A composite attribute also violates 1NF — technically
Address(House, City, PIN) stored as one field is non-atomic if the parts are used separately. Store them as three columns.
But atomicity is relative to the application. If an address is only ever printed whole and never searched by city, a single VARCHAR is atomic for that application. Codd's point is about what the system must be able to see, not about what a human could subdivide.
21.3 Second Normal Form (2NF)¶
Full and partial functional dependency
X → Y is a full functional dependency if removing any attribute from X destroys the dependency.
X → Y is a partial functional dependency if some attribute can be removed from X and the dependency still holds — i.e. a proper subset of X already determines Y.
2NF
A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key — equivalently, no non-prime attribute is partially dependent on any candidate key.
The shortcut every examiner expects you to know
A partial dependency requires a proper subset of a candidate key to determine something. If every candidate key is a single attribute, no proper non-empty subset exists.
A relation whose candidate keys are all single attributes is automatically in 2NF. Only look for 2NF violations when there is a composite candidate key.
Worked violation¶
STUDENT_COURSE( Rollno, SID, Sname, Cname, Grade )
FDs: Rollno, SID → Grade; Rollno → Sname; SID → Cname.
Candidate key: (Rollno, SID) — its closure is everything, and neither part alone suffices.
Prime: Rollno, SID. Non-prime: Sname, Cname, Grade.
In 1NF? Yes — all values atomic.
In 2NF? No.
Rollno → Sname:Rollnois a proper subset of the key, andSnameis non-prime. Partial dependency — violation.SID → Cname: likewise. Violation.Rollno, SID → Grade: full — the whole key is needed. Fine.
The anomalies this predicts (and they are exactly those of Unit 3, Lesson 1): a student's name is repeated once per course; a new course cannot be recorded until a student enrols; deleting the last enrolment loses the course name.
Decomposition to 2NF¶
Procedure
For each partial dependency X → Y (where X is a proper subset of a candidate key):
- Create a new relation
(X, Y)withXas its primary key. - Remove
Yfrom the original relation, keepingXthere as a foreign key.
Merge new relations that share the same X.
Rollno → SnamegivesSTUDENT(Rollno, Sname)SID → CnamegivesCOURSE(SID, Cname)- What remains:
ENROLMENT(Rollno, SID, Grade)
| Relation | Key | Check |
|---|---|---|
STUDENT(Rollno, Sname) |
Rollno |
Single-attribute key → automatically 2NF |
COURSE(SID, Cname) |
SID |
Automatically 2NF |
ENROLMENT(Rollno, SID, Grade) |
(Rollno, SID) |
Grade fully depends on the whole key → 2NF |
All three anomalies have gone.
21.4 Third Normal Form (3NF)¶
Transitive dependency
A transitive dependency on a candidate key K is a chain K → X → A where:
Xis not a superkey, andAis a non-prime attribute not inX.
The essence: a non-prime attribute is determined by another non-prime attribute rather than directly by a key.
3NF — Codd's original definition
A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on any candidate key.
3NF — the general (working) definition
A relation is in 3NF if, for every non-trivial functional dependency X → A that holds, at least one of the following is true:
Xis a superkey, orAis a prime attribute.
Condition 1 alone would be BCNF; condition 2 is precisely what 3NF forgives and BCNF does not.
The general definition is easier to apply and handles overlapping candidate keys correctly. Use it.
Worked violation¶
STUDENT( Rollno, Sname, Dno, Dname, Dhead )
FDs: Rollno → Sname, Dno; Dno → Dname, Dhead.
Candidate key: Rollno. Prime: Rollno. Non-prime: Sname, Dno, Dname, Dhead.
In 2NF? Yes — the only candidate key is a single attribute, so no partial dependency is possible.
In 3NF? No. The chain Rollno → Dno → Dname is transitive: Dno is not a superkey (Dno⁺ = {Dno, Dname, Dhead} ≠ R) and Dname is non-prime. By the general definition, Dno → Dname has a non-superkey determinant and a non-prime dependent, failing both conditions.
The anomalies: the department name and head are repeated for every student in the department; a department with no students cannot be recorded; changing a department head requires updating many rows.
Decomposition to 3NF¶
Procedure
For each transitive dependency X → Y where X is not a superkey:
- Create a new relation
(X, Y)withXas its primary key. - Remove
Yfrom the original relation, keepingXas a foreign key.
STUDENT(Rollno, Sname, Dno), keyRollno,Dnoa foreign keyDEPARTMENT(Dno, Dname, Dhead), keyDno
Both are now in 3NF, and the decomposition is lossless — the common attribute Dno is the key of DEPARTMENT (Lesson 5).
21.5 A Complete Worked Example¶
INVOICE( InvNo, LineNo, CustID, CustName, CustCity, ProdID, ProdName, UnitPrice, Qty )
FDs:
InvNo, LineNo → ProdID, Qty
InvNo → CustID
CustID → CustName, CustCity
ProdID → ProdName, UnitPrice
Step 1 — candidate keys.
(InvNo, LineNo)⁺ = start {InvNo, LineNo} → ProdID, Qty → CustID → CustName, CustCity → ProdName, UnitPrice = all attributes. So (InvNo, LineNo) is a superkey; neither part alone is (InvNo⁺ lacks LineNo, LineNo⁺ = {LineNo}). Candidate key: (InvNo, LineNo).
Prime: InvNo, LineNo. Non-prime: everything else.
Step 2 — 1NF? Yes.
Step 3 — 2NF? No. InvNo → CustID is a partial dependency: InvNo is a proper subset of the key and CustID is non-prime. Decompose:
INVOICE ( InvNo, CustID ) key InvNo
LINE ( InvNo, LineNo, ProdID, Qty ) key (InvNo, LineNo)
CustName, CustCity, ProdName, UnitPrice must be placed too. They follow their determinants:
INVOICE ( InvNo, CustID, CustName, CustCity ) key InvNo
LINE ( InvNo, LineNo, ProdID, Qty, ProdName, UnitPrice ) key (InvNo, LineNo)
Step 4 — 3NF? Neither is in 3NF yet.
- In
INVOICE:CustID → CustName, CustCity—CustIDis not a superkey ofINVOICEand the dependents are non-prime. Transitive. - In
LINE:ProdID → ProdName, UnitPrice— same problem.
Decompose both:
CUSTOMER ( CustID, CustName, CustCity ) key CustID
INVOICE ( InvNo, CustID ) key InvNo, FK CustID
PRODUCT ( ProdID, ProdName, UnitPrice ) key ProdID
LINE ( InvNo, LineNo, ProdID, Qty ) key (InvNo, LineNo)
FK InvNo, FK ProdID
Step 5 — verify. Every determinant (CustID, InvNo, ProdID, (InvNo, LineNo)) is the key of its own relation, so each relation is in fact in BCNF, and hence in 3NF.
This is also exactly the schema the ER-to-relational mapping of Lesson 1 would have produced from a correct ER diagram — which is the practical argument for doing the conceptual design properly.
21.6 Solved Questions¶
Solved Question 1
Q. Define prime and non-prime attributes. For R(A,B,C,D) with candidate keys {A} and {B,C}, classify every attribute.
A. An attribute is prime if it belongs to at least one candidate key; non-prime if it belongs to none.
The word is some, not all — an attribute in any one candidate key is prime.
| Attribute | In {A}? | In {B,C}? | Classification |
|---|---|---|---|
| A | yes | no | prime |
| B | no | yes | prime |
| C | no | yes | prime |
| D | no | no | non-prime |
Prime: A, B, C. Non-prime: D.
Why it matters: both the 2NF and the 3NF tests are stated entirely in terms of non-prime attributes. Here only D can cause a violation — any dependency whose right side is A, B or C is automatically forgiven by 3NF, however odd its determinant.
This is also why the candidate keys must be computed first: misidentify them and every subsequent normal-form judgement is wrong.
Solved Question 2
Q. Define 2NF. Test STUDENT_COURSE(Rollno, SID, Sname, Cname, Grade) with Rollno,SID → Grade, Rollno → Sname, SID → Cname, and decompose if necessary.
A. A relation is in 2NF if it is in 1NF and no non-prime attribute is partially dependent on any candidate key — that is, every non-prime attribute is fully dependent on every candidate key.
Candidate key. (Rollno, SID)⁺ = {Rollno, SID} → Grade → Sname → Cname = all. Rollno⁺ = {Rollno, Sname}; SID⁺ = {SID, Cname}. Neither part alone is a superkey, so (Rollno, SID) is the sole candidate key.
Prime: Rollno, SID. Non-prime: Sname, Cname, Grade.
2NF test.
| FD | Determinant a proper subset of the key? | Dependent non-prime? | Verdict |
|---|---|---|---|
Rollno, SID → Grade |
no (it is the whole key) | yes | full — fine |
Rollno → Sname |
yes | yes | partial — violates 2NF |
SID → Cname |
yes | yes | partial — violates 2NF |
Not in 2NF.
Decomposition. One relation per partial dependency, with the remainder keeping the full key:
STUDENT ( Rollno, Sname ) key Rollno
COURSE ( SID, Cname ) key SID
ENROLMENT ( Rollno, SID, Grade ) key (Rollno, SID); both columns foreign keys
Verification. STUDENT and COURSE have single-attribute keys, so they are automatically in 2NF (no proper subset exists). In ENROLMENT, Grade depends on the whole key. All three are in 2NF — and in fact in BCNF.
Anomalies removed: a course can now be recorded with no students (insertion); deleting the last enrolment leaves the course intact (deletion); a student's name is stored once (modification).
Solved Question 3
Q. Define 3NF in both forms. Test EMPLOYEE(Eno, Ename, Dno, Dname, Dlocation) with Eno → Ename, Dno and Dno → Dname, Dlocation.
A.
Codd's original definition. In 2NF, and no non-prime attribute is transitively dependent on any candidate key — no chain K → X → A with X not a superkey and A non-prime.
General definition. For every non-trivial FD X → A: either X is a superkey, or A is prime.
Analysis. Eno⁺ = {Eno, Ename, Dno} → (via Dno) {Eno, Ename, Dno, Dname, Dlocation} = R. So Eno is the only candidate key.
Prime: Eno. Non-prime: Ename, Dno, Dname, Dlocation.
1NF: yes. 2NF: yes — the sole candidate key is a single attribute, so no partial dependency can exist.
3NF: no.
- By the original definition,
Eno → Dno → Dnameis a transitive chain:Dnois not a superkey (Dno⁺ = {Dno, Dname, Dlocation} ≠ R) andDnameis non-prime. - By the general definition, the FD
Dno → Dnamefails both conditions:Dnois not a superkey, andDnameis not prime.
Decomposition.
EMPLOYEE ( Eno, Ename, Dno ) key Eno, Dno a foreign key
DEPARTMENT ( Dno, Dname, Dlocation ) key Dno
Verification. In EMPLOYEE the only determinant is Eno, its key. In DEPARTMENT the only determinant is Dno, its key. Both are in BCNF.
The decomposition is lossless: the common attribute Dno is the primary key of DEPARTMENT, so Dno⁺ contains all of DEPARTMENT (Lesson 5). It is also dependency preserving: Eno → Ename, Dno is checkable within EMPLOYEE and Dno → Dname, Dlocation within DEPARTMENT.
Solved Question 4
Q. Distinguish partial from transitive dependency, with an example of each, and state which normal form removes each.
A.
| Partial dependency | Transitive dependency | |
|---|---|---|
| Shape | X → A where X is a proper subset of a candidate key and A is non-prime |
K → X → A where X is not a superkey and A is non-prime |
| Determinant | Part of a key | Not a key at all |
| Requires | A composite candidate key | Nothing special; occurs with single-attribute keys |
| Removed by | 2NF | 3NF |
| Example | In (Rollno, SID, Sname): Rollno → Sname |
In (Eno, Dno, Dname): Eno → Dno → Dname |
Partial — the attribute belongs with part of the key, so it is repeated once for every value of the rest of the key. Sname is repeated once per course the student takes.
Transitive — the attribute belongs with a different entity that happens to be identified inside this relation. Dname is a fact about a department, repeated once per employee in it.
Both are the same disease in the terms of Unit 3, Lesson 1: the relation stores facts about more than one kind of thing. The cure is the same in both cases — extract the determinant and its dependents into their own relation, leaving the determinant behind as a foreign key.
Note the ordering. A relation with a single-attribute candidate key cannot have a partial dependency, so it is automatically in 2NF and one need only test for 3NF.
Solved Question 5
Q. Normalise ORDER_DETAIL(OrderNo, ProdID, OrderDate, CustID, CustName, ProdName, Price, Qty) to 3NF. Show the FDs, the keys, each violation and the final schema.
A.
FDs from the semantics:
OrderNo, ProdID → Qty
OrderNo → OrderDate, CustID
CustID → CustName
ProdID → ProdName, Price
Candidate key. (OrderNo, ProdID)⁺ = {OrderNo, ProdID} → Qty → OrderDate, CustID → CustName → ProdName, Price = all attributes. Neither part alone suffices. (OrderNo, ProdID) is the sole candidate key.
Prime: OrderNo, ProdID. Non-prime: all the rest.
1NF. Satisfied — all values atomic.
2NF. Violated twice.
OrderNo → OrderDate, CustID— proper subset of the key determining non-prime attributes.ProdID → ProdName, Price— likewise.
Decompose:
ORDERS ( OrderNo, OrderDate, CustID, CustName ) key OrderNo
PRODUCT ( ProdID, ProdName, Price ) key ProdID
LINE ( OrderNo, ProdID, Qty ) key (OrderNo, ProdID)
3NF. PRODUCT and LINE are fine — every determinant is the relation's key. But ORDERS still has CustID → CustName, where CustID is not a superkey of ORDERS and CustName is non-prime. Transitive — violated.
Decompose again:
CUSTOMER ( CustID, CustName ) key CustID
ORDERS ( OrderNo, OrderDate, CustID ) key OrderNo, FK CustID
Final schema (all in BCNF, hence 3NF):
CUSTOMER ( CustID, CustName )
PRODUCT ( ProdID, ProdName, Price )
ORDERS ( OrderNo, OrderDate, CustID ) FK CustID -> CUSTOMER
LINE ( OrderNo, ProdID, Qty ) FK OrderNo -> ORDERS, FK ProdID -> PRODUCT
Check: in each relation the only determinant is its own primary key, so every FD has a superkey on the left. Every decomposition step split on a determinant that became the key of the new relation, so each is lossless; and every original FD is checkable within one relation, so the decomposition is dependency preserving.
Solved Question 6
Q. Give a relation that is in 2NF but not 3NF, and one in 1NF but not 2NF. Explain why each fails, and why the general 3NF definition is preferred to Codd's original.
A.
In 1NF but not 2NF. ENROLMENT(Rollno, SID, Sname, Grade) with Rollno, SID → Grade and Rollno → Sname.
Candidate key (Rollno, SID); Sname is non-prime. Rollno is a proper subset of the key and determines Sname, so Sname is partially dependent on the key. Values are atomic, so it is in 1NF but fails 2NF.
In 2NF but not 3NF. STUDENT(Rollno, Sname, Dno, Dname) with Rollno → Sname, Dno and Dno → Dname.
The sole candidate key Rollno is a single attribute, so no partial dependency is possible and 2NF holds. But Rollno → Dno → Dname is transitive: Dno is not a superkey and Dname is non-prime. Fails 3NF.
Why the general definition is preferred. Codd's original wording — "no non-prime attribute is transitively dependent on a candidate key" — is awkward when candidate keys overlap, because one must trace chains from each key and decide which attributes count as intermediate. The general form is a simple test applied to each dependency independently:
for every non-trivial
X → A:Xis a superkey orAis prime.
Consider R(A,B,C) with AB → C and C → B. Candidate keys are {A,B} and {A,C}, so all attributes are prime. The general definition immediately gives 3NF: C → B fails the superkey test but B is prime, so the second condition rescues it. Reasoning about transitive chains here is far more error-prone.
The general definition also makes the relationship to BCNF transparent: BCNF is the general 3NF definition with condition 2 deleted.
21.7 Exercises¶
A. Short answer
- Define prime and non-prime attributes.
- State 1NF and give two ways it can be violated.
- Define partial functional dependency.
- When is a relation automatically in 2NF?
- State both definitions of 3NF.
- Which normal form removes partial dependencies? Which removes transitive ones?
- Why must candidate keys be found before testing 2NF or 3NF?
B. Test and normalise
For each, find all candidate keys, classify the attributes, determine the highest normal form, name every violating dependency, and decompose to 3NF.
R(A,B,C,D)withAB → C,C → D.R(A,B,C,D,E)withA → BC,C → DE.R(A,B,C,D)withA → B,B → C,C → D.STUDENT(Rollno, Name, CourseID, CourseName, TeacherName, TeacherPhone)with the obvious dependencies.SALES(InvoiceNo, ItemNo, Date, CustID, CustName, ItemName, Rate, Qty).PROJECT(Eno, Ename, Pno, Pname, Ploc, Hours)withEno → Ename,Pno → Pname, Ploc,Eno,Pno → Hours.BOOK(ISBN, Title, AuthorID, AuthorName, PublisherID, PublisherName, PublisherCity).HOSPITAL(PatientID, PatientName, DoctorID, DoctorName, Speciality, VisitDate, Diagnosis).
C. Analysis
- Give a relation in 1NF but not 2NF, and prove it with an instance exhibiting an anomaly.
- Give a relation in 2NF but not 3NF, and demonstrate the update anomaly with actual rows.
- Explain why
Method 3(Course1, Course2, Course3) is a poor repair for a 1NF violation. Give three specific problems. - Show that a relation with only two attributes is always in 3NF. Is it always in BCNF?
- Explain why the general definition of 3NF handles overlapping candidate keys better than Codd's original. Use
R(A,B,C)withAB → C,C → B. - For
R(A,B,C,D)withAB → CDandD → A, find all candidate keys and determine the highest normal form. (Take care: the keys overlap.) - A relation has candidate keys
{A}and{B}, and the dependencyA → B. Is it in 3NF? In BCNF? Justify. - "Normalisation always improves a database." Argue both sides, mentioning join cost and denormalisation.
D. Objective — True or False with reason
- Every relation in 3NF is in 2NF.
- A relation with a single-attribute candidate key is always in 3NF.
- A partial dependency can exist when the candidate key is a single attribute.
- An attribute in some candidate key is prime.
- 1NF prohibits composite attributes.
- Removing a transitive dependency requires creating a new relation.
- A relation in 2NF has no redundancy.
Answers to Section D
- True. The normal forms are strictly nested: 3NF ⊂ 2NF ⊂ 1NF.
- False. It is automatically in 2NF, but a transitive dependency can still violate 3NF — as in
STUDENT(Rollno, Sname, Dno, Dname). - False. A partial dependency needs a proper subset of the key; a single attribute has no non-empty proper subset.
- True. That is the definition — membership of some candidate key, not all.
- True. A composite attribute is not atomic, so it must be stored as its separate component columns.
- True. The determinant and its dependents are extracted into a new relation, with the determinant left behind as a foreign key.
- False. 2NF removes only partial dependencies. Transitive dependencies, and hence redundancy, may remain.
Lesson Summary
- Find all candidate keys first; then classify attributes as prime or non-prime.
- 1NF: atomic values — no composite, multivalued or repeating groups. Repair with a separate relation, never with
Course1, Course2, Course3. - 2NF: no partial dependency of a non-prime attribute on a candidate key. Only possible to violate when a key is composite.
- 3NF: no transitive dependency — or, generally, for every non-trivial
X → A,Xis a superkey orAis prime. - Decompose by extracting the offending determinant together with its dependents into a new relation, leaving the determinant as a foreign key.