Skip to content

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

  1. Define prime and non-prime attributes.
  2. Test a relation for 1NF, 2NF and 3NF, and name the exact violating dependency.
  3. Distinguish partial from transitive dependencies.
  4. Decompose a relation to 2NF and then to 3NF.
  5. 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: Rollno is a proper subset of the key, and Sname is 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):

  1. Create a new relation (X, Y) with X as its primary key.
  2. Remove Y from the original relation, keeping X there as a foreign key.

Merge new relations that share the same X.

  • Rollno → Sname gives STUDENT(Rollno, Sname)
  • SID → Cname gives COURSE(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:

  • X is not a superkey, and
  • A is a non-prime attribute not in X.

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:

  1. X is a superkey, or
  2. A is 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:

  1. Create a new relation (X, Y) with X as its primary key.
  2. Remove Y from the original relation, keeping X as a foreign key.
  • STUDENT(Rollno, Sname, Dno), key Rollno, Dno a foreign key
  • DEPARTMENT(Dno, Dname, Dhead), key Dno

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)
...but 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 — CustID is not a superkey of INVOICE and 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 → Dname is a transitive chain: Dno is not a superkey (Dno⁺ = {Dno, Dname, Dlocation} ≠ R) and Dname is non-prime.
  • By the general definition, the FD Dno → Dname fails both conditions: Dno is not a superkey, and Dname is 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: X is a superkey or A is 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

  1. Define prime and non-prime attributes.
  2. State 1NF and give two ways it can be violated.
  3. Define partial functional dependency.
  4. When is a relation automatically in 2NF?
  5. State both definitions of 3NF.
  6. Which normal form removes partial dependencies? Which removes transitive ones?
  7. 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.

  1. R(A,B,C,D) with AB → C, C → D.
  2. R(A,B,C,D,E) with A → BC, C → DE.
  3. R(A,B,C,D) with A → B, B → C, C → D.
  4. STUDENT(Rollno, Name, CourseID, CourseName, TeacherName, TeacherPhone) with the obvious dependencies.
  5. SALES(InvoiceNo, ItemNo, Date, CustID, CustName, ItemName, Rate, Qty).
  6. PROJECT(Eno, Ename, Pno, Pname, Ploc, Hours) with Eno → Ename, Pno → Pname, Ploc, Eno,Pno → Hours.
  7. BOOK(ISBN, Title, AuthorID, AuthorName, PublisherID, PublisherName, PublisherCity).
  8. HOSPITAL(PatientID, PatientName, DoctorID, DoctorName, Speciality, VisitDate, Diagnosis).

C. Analysis

  1. Give a relation in 1NF but not 2NF, and prove it with an instance exhibiting an anomaly.
  2. Give a relation in 2NF but not 3NF, and demonstrate the update anomaly with actual rows.
  3. Explain why Method 3 (Course1, Course2, Course3) is a poor repair for a 1NF violation. Give three specific problems.
  4. Show that a relation with only two attributes is always in 3NF. Is it always in BCNF?
  5. Explain why the general definition of 3NF handles overlapping candidate keys better than Codd's original. Use R(A,B,C) with AB → C, C → B.
  6. For R(A,B,C,D) with AB → CD and D → A, find all candidate keys and determine the highest normal form. (Take care: the keys overlap.)
  7. A relation has candidate keys {A} and {B}, and the dependency A → B. Is it in 3NF? In BCNF? Justify.
  8. "Normalisation always improves a database." Argue both sides, mentioning join cost and denormalisation.

D. Objective — True or False with reason

  1. Every relation in 3NF is in 2NF.
  2. A relation with a single-attribute candidate key is always in 3NF.
  3. A partial dependency can exist when the candidate key is a single attribute.
  4. An attribute in some candidate key is prime.
  5. 1NF prohibits composite attributes.
  6. Removing a transitive dependency requires creating a new relation.
  7. A relation in 2NF has no redundancy.

Answers to Section D

  1. True. The normal forms are strictly nested: 3NF ⊂ 2NF ⊂ 1NF.
  2. False. It is automatically in 2NF, but a transitive dependency can still violate 3NF — as in STUDENT(Rollno, Sname, Dno, Dname).
  3. False. A partial dependency needs a proper subset of the key; a single attribute has no non-empty proper subset.
  4. True. That is the definition — membership of some candidate key, not all.
  5. True. A composite attribute is not atomic, so it must be stored as its separate component columns.
  6. True. The determinant and its dependents are extracted into a new relation, with the determinant left behind as a foreign key.
  7. 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, X is a superkey or A is prime.
  • Decompose by extracting the offending determinant together with its dependents into a new relation, leaving the determinant as a foreign key.