Self-checkedTier 1 9 min readdbms.normal-forms

1NF to BCNF

Normal forms as conditions on functional dependencies: first normal form (atomic values), second normal form (no partial dependency of a non-prime attribute on a key), third normal form (every dependency has a superkey on the left or a prime attribute on the right), Boyce-Codd normal form (every determinant is a superkey); the redundancy and update, insertion and deletion anomalies they prevent; testing a schema (checking only the given dependencies is enough), the strict hierarchy BCNF, 3NF, 2NF, 1NF, and examples of schemas at each level.

Why it exists

A badly designed relation repeats facts, so changing one fact needs many updates, a new fact cannot be stored without an unrelated one, and deleting one fact can destroy another. Normal forms are conditions on the functional dependencies of a schema that rule such problems out step by step. GATE asks for the highest normal form of a schema, which dependency violates a given form, and how forms relate.

Prerequisites

You need functional dependencies, candidate keys and prime attributes (finding candidate keys).

Precise definitions

For a schema $R$ with a set $F$ of functional dependencies, with candidate keys and prime attributes found as on the previous page:

1NF: every attribute value is atomic (no repeating groups, no sets or lists as values). A relation in the relational model is in 1NF by definition; the condition separates it from nested tables.
2NF: 1NF and no partial dependency: no non-prime attribute depends on a proper subset of a candidate key. (Only schemas with a composite key can violate 2NF.)
3NF: for every non-trivial dependency $X \to A$ (single attribute $A$ ) that holds, at least one of: (a) $X$ is a superkey, or (b) $A$ is a prime attribute.
BCNF (Boyce–Codd): for every non-trivial dependency $X \to A$ , $X$ is a superkey.

The hierarchy is strict: $\text{BCNF} \subsetneq \text{3NF} \subsetneq \text{2NF} \subsetneq \text{1NF}$ . BCNF removes the escape clause "or $A$ is prime" of 3NF; 3NF allows exactly the dependencies of the form $X \to \text{prime attribute}$ with $X$ not a key.

Anomalies (the problems the forms prevent): redundancy (a fact stored many times), update anomaly (changing one fact in some copies only), insertion anomaly (a fact cannot be recorded without another unrelated fact), deletion anomaly (deleting one fact removes another).

How it works

Anomalies in a badly designed relation

A single relation that mixes students and courses, with the dependencies $\text{sid} \to \text{sname}$ and $\text{cid} \to \text{title}$ and the key $(\text{sid}, \text{cid})$ :

Redundancy and anomalies, and the decomposition that removes them: start

Computed, not typed

Enrolment(sid, sname, cid, title) with sid → sname, cid → title (key: sid, cid)

Enrolment6 tuples

sid	sname	cid	title
1	Asha	C1	DB
1	Asha	C2	OS
2	Bilal	C1	DB
3	Chen	C1	DB
3	Chen	C3	CN
4	Dara	C2	OS

Step 0

One relation holds both student data and course data. Two dependencies hold: a student id determines the name and a course id determines the title. The key is (sid, cid), and both dependencies start from only PART of the key.

0 / 5

computed by script: dependencies, closures, keys and decompositions are computed by scripts/db_fd.py. python3 scripts/gen-db3-traces.py

Each dependency starts from only part of the key, so the relation violates 2NF. Splitting along the dependencies stores every fact once.

Testing a schema

Work with the candidate keys and the dependencies in $F$ (it is enough to test the given dependencies, with their right sides split into single attributes: the level computed this way equals the level computed from all of $F^+$ , checked on 600 random schemas):

Find the candidate keys and the prime attributes.
BCNF test: is every non-trivial $X \to A$ of $F$ such that $X$ is a superkey ( $X^+ = R$ )? If yes: BCNF.
Else 3NF test: is every violating $X \to A$ such that $A$ is prime? If yes: 3NF (not BCNF).
Else 2NF test: is there a partial dependency, a non-prime $A$ with $X \subsetneq$ some key and $X \to A$ in $F^+$ ? If none: 2NF (not 3NF). The violation is then a transitive dependency: a non-key determines a non-prime attribute.
Else the schema is only in 1NF.

Normal form of some schemas: start

Computed by a script, not typed

schema	candidate keys	highest normal form	reason
R(A, B, C), F = { A → B }
R(A, B, C, D), F = { AB → C, A → D }
R(A, B, C), F = { A → B, B → C }
R(A, B, C), F = { AB → C, C → B }
R(A, B, C), F = { A → B, A → C }
R(A, B, C, D), F = { A → B, B → C, C → D, D → A }
R(A, B, C, D), F = { AB → C, B → D }

Step 0

1NF: values are atomic. 2NF: no non-prime attribute depends on part of a key. 3NF: for every X → A, X is a superkey or A is prime. BCNF: for every X → A, X is a superkey. Each level implies the ones before it. The level is found by testing the given dependencies and confirmed by testing every dependency of F+.

0 / 7

computed by script: dependencies, closures, keys and decompositions are computed by scripts/db_fd.py. python3 scripts/gen-db3-traces.py

Reading some rows: $R(A, B, C)$ with $A \to B$ has the key $AC$ ; $B$ depends on $A$ , a proper subset of the key: partial dependency, so only 1NF. $R(A, B, C)$ with $A \to B$ , $B \to C$ has the key $A$ , no partial dependency (the key is a single attribute); $B \to C$ makes the non-prime $C$ depend on the non-key $B$ : transitive, so 2NF but not 3NF. $R(A, B, C)$ with $AB \to C$ , $C \to B$ has keys $AB$ and $AC$ : $C \to B$ has a non-key left side but $B$ is prime, so 3NF but not BCNF. $R(A, B, C)$ with $A \to B$ , $A \to C$ has the key $A$ : BCNF.

Why 3NF allows the prime exception

The classic example $R(A, B, C)$ with $AB \to C$ and $C \to B$ is in 3NF but not BCNF. It stores the fact " $C$ determines $B$ " redundantly in every tuple that repeats a $C$ value with different $A$ values. Eliminating that redundancy by BCNF decomposition costs the dependency $AB \to C$ , which can no longer be checked inside one table (see the next page). 3NF is the compromise: there is always a lossless, dependency-preserving decomposition into 3NF, but not always into BCNF.

Quick checks

A schema whose every candidate key is a single attribute cannot violate 2NF.
A schema with only two attributes is always in BCNF (the only possible non-trivial dependencies have a single attribute on the left that is either a key or determines nothing useful).
If all attributes are prime the schema is at least in 3NF (the right side of every dependency is prime).
BCNF means: the left side of every non-trivial dependency is a superkey.

Worked examples

EX 1Partial dependency

Easy

$R(\text{sid}, \text{cid}, \text{sname}, \text{grade})$ with $\text{sid}, \text{cid} \to \text{grade}$ and $\text{sid} \to \text{sname}$ . The key is $(\text{sid}, \text{cid})$ . $\text{sname}$ depends on $\text{sid}$ alone: a partial dependency. The schema is in 1NF, not in 2NF. Decompose into $(\text{sid}, \text{sname})$ and $(\text{sid}, \text{cid}, \text{grade})$ .

EX 2Transitive dependency

Easy

$R(\text{eid}, \text{dept}, \text{manager})$ with $\text{eid} \to \text{dept}$ , $\text{dept} \to \text{manager}$ . Key: $\text{eid}$ . The manager depends on the department, a non-key: a transitive dependency. In 2NF, not in 3NF. Decompose into $(\text{eid}, \text{dept})$ and $(\text{dept}, \text{manager})$ .

EX 33NF but not BCNF

Medium

$R(\text{student}, \text{course}, \text{teacher})$ with $\text{student}, \text{course} \to \text{teacher}$ and $\text{teacher} \to \text{course}$ (each teacher teaches one course). Keys: $(\text{student}, \text{course})$ and $(\text{student}, \text{teacher})$ . $\text{teacher} \to \text{course}$ has a non-superkey left side, but $\text{course}$ is prime: 3NF, not BCNF.

EX 4Highest normal form

Medium

$R(A, B, C, D)$ with $AB \to C$ , $B \to D$ : the key is $AB$ ; $B \to D$ is a partial dependency ( $D$ non-prime, $B$ a proper subset of the key): only 1NF. $R(A, B, C, D)$ with $A \to B$ , $B \to C$ , $C \to D$ , $D \to A$ : every attribute is a key, all determinants are superkeys: BCNF.

EX 5Typical questions

GATE level

(a) "A relation with a single-attribute key and no other dependencies is in:" BCNF. (b) "Every relation with two attributes is in:" BCNF. (c) "A relation is in 3NF but not BCNF only if:" it has overlapping candidate keys (a dependency whose right side is prime and whose left side is not a superkey). (d) "Which anomaly occurs when deleting a tuple loses unrelated information?" deletion anomaly. (e) "A relation in BCNF may still have redundancy due to:" multivalued dependencies (4NF deals with them). (f) "Is every 3NF relation in 2NF?" Yes.

Cheat box

1NF atomic values. 2NF: no non-prime attribute depends on part of a key. 3NF: for $X \to A$ : $X$ superkey or $A$ prime. BCNF: $X$ superkey.
BCNF $\subset$ 3NF $\subset$ 2NF $\subset$ 1NF.
Partial dependency violates 2NF; transitive dependency (non-key $\to$ non-prime) violates 3NF; key-less determinant with prime right side violates BCNF only.
Single-attribute keys: automatically 2NF. Two attributes: BCNF. All attributes prime: at least 3NF.
Test the given dependencies after finding all candidate keys.

Traps and GATE patterns

Connections

Builds on

Finding candidate keys

Unlocks

Multivalued dependencies and 4NFPrerequisite ofplanned

ER to relational mappingimplemented by Functional dependencies and closureimplemented by Finding candidate keysimplemented by Lossless join and dependency preservationPrerequisite of Lossless join and dependency preservationuses

Across subjects

No cross-subject links yet.

Within this subject

Finding candidate keysuses Lossless join and dependency preservationimplemented-by

Multivalued dependencies and 4NFimplemented-byplanned

ER to relational mappinguses

The repair for a violated form is a decomposition that must be lossless and, if possible, dependency preserving (lossless join and dependency preservation); redundancy that survives BCNF comes from multivalued dependencies (4NF); a good ER-to-relational mapping already avoids most of these problems (ER to relational mapping).

PYQs

18 previous year questions from your book. I re-solved each one myself (11 by running or simulating, 7 by reasoning only); the book's key is shown next to my answer. All PYQs

GATE 2026 · Set 1 · MSQ · 1 markQ4 · page 369 of the book

In the context of relational database normalization, which
of the following statements is/are true?

(a)It is always possible to obtain a dependency- preserving 3NF decomposition of a relation
(b)It is always possible to obtain a dependency- preserving 1NF decomposition of a relation
(c)It is not always possible to obtain a dependency- preserving BCNF decomposition of a relation
(d)It is not always possible to obtain a dependency- preserving 2NF decomposition of a relation

GATE 2025 · Set 2 · MSQQ5 · page 369 of the book

Consider the following relational schema along with all
the functional dependencies that hold on them.
R1(A,B,C,D,E):{D→E,EA→B,EB→C}
R2(A,B,C,D):{A→D,A→B,C→A}

(a)R1 is in 3NF
(b)R2 is in 3NF
(c)R1 is NOT in 3NF
(d)R2 is NOT in 3NF

GATE 2025 · Set 1 · MSQQ6 · page 369 of the book

Consider a relational schema team(name , city , owner)
with functional dependencies {name → city , name →
owner}
.
The relation team is decomposed into two relations,

DATABASE MANAGEMENT  SYSTEM
t1(name , city) and t2(name , owner). Which of the
following statement(s) is/are TRUE?

(a)The relation team is NOT in BCNF.
(b)The relations t1 and t2 are in BCNF.
(c)The decomposition constitutes a lossless join.
(d)The relation team is NOT in 3NF.

GATE 2024 · Set 1 · MSQQ7 · page 370 of the book

Which of the following statements about a relation R in
first normal form (1NF) is/are TRUE ?

(a)R can have a multi-attribute key
(b)R cannot have a foreign key
(c)R cannot have a composite attribute
(d)R cannot have more than one candidate key

GATE 2022 · MCQ · 1 markQ12 · page 370 of the book

In a relational data model, which one of the following
statements is TRUE?

(a)A relation with only two attributes is always in BCNF.
(b)If all attributes of a relation are prime attributes, then the relation is in BCNF.
(c)Every relation has at least one non-prime attribute.
(d)BCNF decompositions preserve functional dependencies.

GATE 2020 · MCQ · 2 marksQ15 · page 371 of the book

Consider a relational table R that is in 3NF, but not in
BCNF. Which one of the following statements is TRUE?

(a)A cell in R holds a set instead of an atomic value.
(b)R has a nontrivial functional dependency X → A, where x is not a super key and A is a non-prime attribute and X is not a proper subset of any key.
(c)R has a nontrivial functional dependency X → A, where x is not a super key and A is a non-prime attribute and X is a proper subset of some key.
(d)R has a nontrivial functional dependency X → A, where X is not a super key and A is a prime attribute.

GATE 2019 · MCQ · 2 marksQ16 · page 371 of the book

Let the set of functional dependencies F = {QR → S, R
→ P, S → Q} hold on a relation schema X = (PQRS).
X is not in BCNF. Suppose X is decomposed into two
schemas Y and Z, where Y = (PR) and Z = (QRS).
Consider the two statements given below.
I.
Both Y and Z are in BCNF
II.
Decomposition of X into Y and Z is dependency
preserving and lossless.

Which of the statements is/are correct ?

(a)I only
(b)II only
(c)Neither I nor II
(d)Both I and II

GATE 2018 · MCQ · 2 marksQ17 · page 371 of the book

Consider the following four relational schema. For each
schema, all non-trivial functional dependencies are
listed. The underlined attributes are the respective
primary keys.

Schema I: Registration (roll no, courses)

Field 'course' is a set-valued attribute containing the set of
courses a student has registered for.

Non-trivial functional dependency

roll no→ courses

Schema II: Registration(roll no, courseid, email)

Non-trivial functional dependencies:

roll no, course id→ email

Email →roll no

Schema III: Registration (roll no, course id, marks, grade)

Non-trivial functional dependencies:

roll no, course id→ marks ,grade

marks→ grade

Schema IV: Registration (roll no, course id, credit)

Non-trivial functional dependencies:

roll no, courseid→ credit

courseid→ credit

Which one of the relational schemas above is in 3NF but
not in BCNF?

(a)Schema I
(b)Schema II
(c)Schema III
(d)Schema IV

GATE 2016 · Set 1 · MCQQ20 · page 372 of the book

A database of research articles in a journal uses the
following schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE,
TITLE, YEAR, PRICE)

The
primary
key
is
(VOLUME,
NUMBER,
STARTPAGE,
ENDPAGE)
and
the
following
functional dependencies exist in the schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) →
TITLE

(VOLUME, NUMBER) → YEAR

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) →
PRICE

The database is redesigned to use the following schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE,
TITLE, PRICE)

(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database
satisfies,
but
the
old
one
does
not?

(a)1NF
(b)2NF
(c)3NF
(d)BCNF

GATE 2014 · Set 1 · MCQ · 2 marksQ23 · page 372 of the book

Given the following two statements:

S1: Every table with two single-valued attributes is in 1NF,
2NF, 3NF and BCNF.

S2: AB → C, D → E, E → C is a minimal cover for the set
of functional dependencies AB → C, D → E, AB → E, E
→ C.

Which
one
of
the
following
is
CORRECT?

(a)S1 is TRUE and S2 is FALSE.
(b)Both S1 and S2 are TRUE.
(c)S1 is FALSE and S2 is TRUE.
(d)Both S1 and S2 are FALSE.

year not read yetQ28 · page 373 of the book

Relation R has eight attributes ABCDEFGH. Fields of R
contain only atomic values.

F = {CH → G, A → BC, B → CFH, E → A, F → EG} is
a set of functional dependencies (FDs) so that F+ is
exactly the set of FDs that hold for R.

The relation R is ?

(a)In 1NF, but not in 2NF.
(b)In 2NF, but not in 3NF.
(c)In 3NF, but not in BCNF.
(d)In BCNF

GATE 2012 · MCQ · 1 markQ29 · page 373 of the book

Which of the following is TRUE?

(a)Every relation in 3NF is also in BCNF
(b)A relation R is in 3NF if every non- prime attribute of R is fully functionally dependent on every key of R
(c)Every relation in BCNF is also in 3NF
(d)No relation can be in both BCNF and 3NF

GATE 2009 · MCQ · 2 marksQ31 · page 374 of the book

Consider the following relational schema:

•
Suppliers(sid:integer, sname:string,city:string
,street:string )
•
Parts(pid:integer, pname:string,
      color:string)
•
Catalog(sid:integer, pid:integer, cost:real)

Assume that, in the suppliers relation above, each
supplier and each street within a city has unique name,
and (sname, city) forms a candidate key. No other
functional dependencies are implied other than those
implied by primary and candidate keys. Which one of
the following is TRUE about the above schema?

(a)The schema is in BCNF
(b)The schema is in 3NF but not in BCNF
(c)The schema is in 2NF but not in 3NF
(d)The schema is not in 2NF

GATE 2008 · MCQ · 2 marksQ32 · page 374 of the book

Consider the following relational schemes for a library
database:

Book (Title, Author, Catalog_no, Publisher, Year, Price)
 Collection (Title, Author, Catalog_no)

with the following functional dependencies:

I.
Title
Author
→
Catalog_no
II.
Catalog_no → Title Author Publisher Year
III.
Publisher Title Year → Price

Assume {Author, Title} is the key for both schemes.
Which of the following statements is true?

(a)Both Book and Collection are in BCNF
(b)Both Book and Collection are in 3NF only
(c)Book is in 2NF and Collection in 3NF
(d)Both Book and Collection are in 2NF only

GATE 2008 · MCQ · 2 marksQ33 · page 374 of the book

Let R(A, B, C, D, E, P, G) be a relational schema in
which the following functional dependencies are
known to hold:

AB → CD , DE → P, C → E , P → C , B → G

The relational schema R is

(a)in BCNF
(b)in 3NF, but not in BCNF
(c)in 2NF, but not in 3NF
(d)not in 2NF

GATE 2007 · MCQ · 2 marksQ35 · page 374 of the book

Which one of the following statements is FALSE?

(a)Any relation with two attributes is in BCNF
(b)A relation in which every key has only one attribute is in 2NF
(c)A prime attribute can be transitively dependent on a key in a 3NF relation
(d)A prime attribute can be transitively dependent on a key in a BCNF relation

GATE 2005 · MCQ · 1 markQ39 · page 375 of the book

Which one of the following statements about normal
forms is FALSE?

(a)BCNF is stricter than 3NF
(b)Lossless, dependency-preserving decomposition into 3NF is always possible
(c)Lossless, dependency-preserving decomposition into BCNF is always possible
(d)Any relation with two attributes is in BCNF

GATE 2005 · MCQ · 1 markQ40 · page 375 of the book

A table has fields Fl, F2, F3, F4, F5 with the following
functional dependencies
F1 → F3   F2→ F4   (F1 . F2) → F5
In terms of Normalization, this table is in

(a)1 NF
(b)2 NF
(c)3 NF
(d)none

Self-check

Question 1

$R(A, B, C)$ with $AB \to C$ and $C \to A$ . Highest normal form?

Show answer

$B$ is in every key. $AB^+ = ABC$ and $BC^+ = ABC$ (because $C \to A$ ), so the keys are $AB$ and $BC$ . $C \to A$ has a left side that is not a superkey, but $A$ is prime: 3NF, not BCNF.

Question 2

What kind of dependency violates 3NF but not 2NF?

Show answer

A transitive dependency: a non-key (not a part of a key) determines a non-prime attribute.

Question 3

Is a relation with all attributes prime always in 3NF?

Show answer

Yes: the right side of every dependency is prime, so the 3NF condition holds.

Question 4

Why is a two-attribute relation always in BCNF?

Show answer

A non-trivial dependency would have a single attribute $X$ on the left determining the other; then $X$ is a superkey (it determines both attributes), so every determinant is a superkey.

Revision card

One-screen revision

1NF atomic; 2NF no partial; 3NF superkey or prime; BCNF superkey.
Hierarchy strict; test the given dependencies after finding all keys.
Partial = proper subset of key $\to$ non-prime; transitive = non-key $\to$ non-prime.
3NF always achievable losslessly and with dependency preservation; BCNF not always.

References

·Silberschatz, Korth, Sudarshan, Database System Concepts (7th edition), Chapter 7, Sections 7.1 to 7.3 (features of good relational design, atomic domains and first normal form, decomposition using functional dependencies)
·Elmasri, Navathe, Fundamentals of Database Systems (7th edition), Chapter 14, Sections 14.1, 14.4 to 14.6 (informal design guidelines and normal forms)

Verification

scripts/enum-checks/db_fd_check.py: on 600 random schemas the normal-form level computed from the GIVEN dependencies (the textbook tests) equals the level computed from EVERY dependency of the closure F+ (the definitions), and BCNF implies 3NF implies 2NF; the table of schemas on this page was computed by the same functions and the reasons (which dependency violates which form) are those found by the program; the anomaly animation is a computed relation: the three-way decomposition joins back to exactly the original relation. The definitions follow the usual textbook statements (Silberschatz et al. state 3NF with 'a candidate key contains A'; Elmasri and Navathe define 2NF and 3NF in terms of prime attributes; both give the same classification, which was checked here by the definition via F+). Textbook chapter references are from memory. SECOND PASS (2026-11-02, done by the assistant, not a human check): every Self-check question was solved before the printed answer was read and all agreed; the definitions, worked examples, cheat box and traps were read claim by claim and re-derived against the textbook definitions and the sample database. No defect found. Status raised to self-checked; verified needs a textbook or human check.

last verified: never

My notes (saved in this browser)

Finding candidate keys

Lossless join and dependency preservation