Normal Forms (1NF, 2NF, 3NF, BCNF)

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

Normal Forms (1NF, 2NF, 3NF, BCNF)

Removing redundancy step by step — each normal form eliminates one specific class of anomaly.

🌍 Where this lives: normalization is the reason your bank's address appears in one place. Store a customer's address on every transaction row and you have three guaranteed problems: the same address occupies a thousand rows (waste), correcting it means updating a thousand rows (update anomaly), and if one update fails you now have two contradictory addresses with no way to tell which is right (inconsistency). That third one is the real killer — the database no longer knows the truth. Analytics systems deliberately denormalize for read speed, which is a considered trade of consistency for performance, not an excuse to skip this. Search "star schema denormalization OLAP vs OLTP".

The three anomalies normalization prevents

Consider an UNNORMALIZED table: student_course(roll, sname, dept, dept_head, course, ctitle, credits, grade) roll sname dept dept_head course ctitle cr grade ──────────────────────────────────────────────────────────── 101 Ram ACtE07 Sharma ACtE0703 Database 3 B 101 Ram ACtE07 Sharma AExE0101 Circuits 4 A 102 Sita ACtE07 Sharma ACtE0703 Database 3 A 103 Hari AExE01 Karki AExE0101 Circuits 4 C 1. INSERTION ANOMALY You cannot record a new course that nobody has enrolled in yet — there is no roll number to supply, and roll is part of the key. You cannot record a new department without a student. 2. UPDATE ANOMALY Prof. Sharma is replaced as head of ACtE07. That value appears in THREE rows. Update two and miss one, and the database now says ACtE07 has two different heads. There is no way to determine which is correct. 3. DELETION ANOMALY Delete Hari's only enrolment (row 4) and you lose the fact that AExE0101 is titled "Circuits" and carries 4 credits, and that AExE01's head is Karki. Deleting an enrolment destroyed unrelated information. PLUS REDUNDANCY: "Database Systems" and "3" are stored twice; "Sharma" three times. In a real table with 50,000 enrolments across 200 courses, each course title is stored ~250 times. THE ROOT CAUSE, in every case: attributes that depend on something other than the whole primary key are stored in a table keyed by that whole key. Normalization is the procedure for detecting and fixing exactly that.

1NF — atomic values

A relation is in FIRST NORMAL FORM if: · every attribute value is ATOMIC (indivisible) · there are no repeating groups or multivalued attributes · every row has the same number of columns · each column holds values of one domain VIOLATIONS and their fixes: ✗ MULTIVALUED CELL roll name phones 101 Ram 9841000001, 9851000002 Fix: a separate table student(roll, name) student_phone(roll, phone) PK (roll, phone) ✗ REPEATING GROUP (columns numbered 1..n) roll name phone1 phone2 phone3 101 Ram 9841000001 9851000002 NULL Fix: the same separate table. Why the repeating group is worse than it looks: - what if someone has four phones? - "find the student with phone X" needs WHERE phone1=X OR phone2=X OR phone3=X - most cells are NULL, wasting space - you cannot index it usefully ✗ COMPOSITE / STRUCTURED CELL address = "Kupondole, Lalitpur, Bagmati" Fix: addr_street, addr_city, addr_province WHAT COUNTS AS ATOMIC IS DOMAIN-DEPENDENT. A full name is atomic if you never search by surname; it is non-atomic if you do. "Atomic" means "not decomposed by any query this database must answer" — not "short". MODERN NUANCE: PostgreSQL's ARRAY and JSONB columns are deliberate 1NF violations, and they are legitimate when the value is genuinely opaque to the database (a settings blob). They stop being legitimate the moment you need to query inside them.

to_1nf.sql

-- ===== NOT in 1NF: repeating group =====
CREATE TABLE student_bad (
    roll   INTEGER PRIMARY KEY,
    name   VARCHAR(60),
    phone1 VARCHAR(15),
    phone2 VARCHAR(15),
    phone3 VARCHAR(15)          -- what about a 4th?
);
INSERT INTO student_bad VALUES
    (101, 'Ram',  '9841000001', '9851000002', NULL),
    (102, 'Sita', '9841000003', NULL,         NULL);

-- finding a student by phone is painful and unindexable
SELECT roll FROM student_bad
WHERE  phone1 = '9851000002'
   OR  phone2 = '9851000002'
   OR  phone3 = '9851000002';

-- ===== IN 1NF: the repeating group becomes rows =====
CREATE TABLE student (
    roll INTEGER     PRIMARY KEY,
    name VARCHAR(60) NOT NULL
);
CREATE TABLE student_phone (
    roll  INTEGER     NOT NULL REFERENCES student(roll)
                      ON DELETE CASCADE,
    phone VARCHAR(15) NOT NULL,
    PRIMARY KEY (roll, phone)
);
INSERT INTO student VALUES (101,'Ram'), (102,'Sita');
INSERT INTO student_phone VALUES
    (101,'9841000001'), (101,'9851000002'), (102,'9841000003');

-- now the lookup is a single indexed predicate,
-- and any number of phones is supported
SELECT roll FROM student_phone WHERE phone = '9851000002';
--  roll
--  -----
--   101

SELECT s.name, COUNT(p.phone) AS phones
FROM   student s LEFT JOIN student_phone p ON p.roll = s.roll
GROUP  BY s.roll, s.name ORDER BY s.roll;
--  name | phones
--  -----+--------
--  Ram  |      2
--  Sita |      1

2NF — no partial dependency

A relation is in SECOND NORMAL FORM if: · it is in 1NF, AND · every NON-PRIME attribute is FULLY functionally dependent on EVERY candidate key (equivalently: no non-prime attribute depends on only PART of a candidate key) 2NF only ever matters when a candidate key is COMPOSITE. If every candidate key is a single attribute, 1NF ⇒ 2NF automatically — there is no "part of the key" to depend on. EXAMPLE VIOLATION enrolment(roll, course, sname, ctitle, credits, grade) candidate key: (roll, course) FDs: (roll, course) → grade FULL ✔ fine roll → sname PARTIAL ✗ 2NF violation course → ctitle PARTIAL ✗ 2NF violation course → credits PARTIAL ✗ 2NF violation Each partial dependency causes redundancy: sname repeats for every course the student takes; ctitle and credits repeat for every student in the course. DECOMPOSITION — one relation per determinant: student (roll, sname) key roll course (course, ctitle, credits) key course enrolment (roll, course, grade) key (roll,course) VERIFY: is each result in 2NF? student — single-attribute key, so trivially 2NF ✔ course — single-attribute key ✔ enrolment — grade depends on the full key, nothing else ✔ REDUNDANCY REMOVED, counted: before: for 50,000 enrolments over 200 courses and 5,000 students, ctitle is stored 50,000 times after : 200 times (once per course row) → a 250× reduction for that column

3NF — no transitive dependency

A relation is in THIRD NORMAL FORM if: · it is in 2NF, AND · no non-prime attribute is TRANSITIVELY dependent on a candidate key EQUIVALENT AND MORE USEFUL DEFINITION: for every non-trivial FD X → Y at least one holds: (a) X is a superkey, OR (b) every attribute of Y is PRIME (part of some candidate key) EXAMPLE VIOLATION student(roll, sname, dept_code, dept_head) candidate key: roll FDs: roll → sname ✔ (a) roll → dept_code ✔ (a) dept_code → dept_head ✗ VIOLATION dept_code is not a superkey, and dept_head is not prime The transitive chain: roll → dept_code → dept_head So dept_head depends on roll only INDIRECTLY. CONSEQUENCE: dept_head is stored once per STUDENT rather than once per DEPARTMENT. 5,000 students in 10 departments means each head's name stored ~500 times. DECOMPOSITION — split at the offending determinant: student (roll, sname, dept_code) key roll department(dept_code, dept_head) key dept_code VERIFY: student : roll → sname, dept_code. Both from the key ✔ department : dept_code → dept_head. dept_code IS the key ✔ WHY CLAUSE (b) EXISTS — a case that IS in 3NF but looks wrong: R(city, street, pincode) FDs: (city, street) → pincode pincode → city candidate keys: (city, street) and (street, pincode) prime attributes: city, street, pincode — ALL of them pincode → city violates (a): pincode is not a superkey. But city IS prime, so (b) is satisfied → R is in 3NF. It is NOT in BCNF, which is exactly the gap between them.

BCNF — every determinant is a superkey

A relation is in BOYCE–CODD NORMAL FORM if: for EVERY non-trivial FD X → Y, X is a SUPERKEY. That is 3NF with clause (b) removed. No exception for prime dependents. BCNF ⇒ 3NF ⇒ 2NF ⇒ 1NF (each strictly stronger) THE CLASSIC EXAMPLE — R(student, subject, teacher) Business rules: · a teacher teaches exactly ONE subject · a student studies a subject with ONE teacher FDs: (student, subject) → teacher teacher → subject candidate keys: (student, subject) and (student, teacher) prime: student, subject, teacher — all of them IN 3NF? teacher → subject: teacher is not a superkey, but subject is PRIME → clause (b) satisfied → YES, it is in 3NF. IN BCNF? teacher → subject and teacher is NOT a superkey → NO. THE REDUNDANCY THIS PERMITS: student subject teacher ───────────────────────────── Ram Database Sharma Sita Database Sharma Hari Database Sharma → "Sharma teaches Database" is stored three times, and nothing prevents a fourth row saying Sharma teaches Networks. BCNF DECOMPOSITION — split on teacher → subject: teaches (teacher, subject) key teacher studies (student, teacher) key (student, teacher) Both are in BCNF. But watch what is LOST: THE FD (student, subject) → teacher IS NO LONGER ENFORCEABLE by any single table. To check it you must join. So this decomposition is: lossless-join ✔ dependency-preserving ✗ THE FUNDAMENTAL TRADE-OFF, and the standard exam answer: 3NF — always achievable with BOTH lossless join AND dependency preservation BCNF — always achievable lossless, but dependency preservation is NOT guaranteed That is why 3NF is the practical target and BCNF the ideal. When they conflict, most designers stop at 3NF and enforce the lost FD with a trigger or an application check.

The (student, subject) → teacher loss is the whole reason BCNF is not simply "better". After decomposition, two separate tables can each be internally valid while jointly violating a rule the original relation enforced automatically. You have traded a redundancy problem for an enforcement problem — and which is worse depends on the application.

Worked example — full normalization to BCNF

normalize.sql

-- ============ STAGE 0: unnormalized ============
-- One wide table with every problem at once.

-- student_course(roll, sname, phones, dept, dept_head,
--                course, ctitle, credits, grade)
--   phones is multivalued            -> not even 1NF
--   roll -> sname, dept              -> partial (2NF)
--   course -> ctitle, credits        -> partial (2NF)
--   dept -> dept_head                -> transitive (3NF)

-- ============ STAGE 1: reach 1NF ============
-- split the multivalued attribute out
CREATE TABLE sc_1nf (
    roll      INTEGER, sname VARCHAR(60),
    dept      CHAR(6), dept_head VARCHAR(60),
    course    CHAR(8), ctitle VARCHAR(60), credits SMALLINT,
    grade     CHAR(2),
    PRIMARY KEY (roll, course)
);
CREATE TABLE student_phone (
    roll INTEGER, phone VARCHAR(15),
    PRIMARY KEY (roll, phone)
);

-- ============ STAGE 2: reach 2NF ============
-- remove the partial dependencies
CREATE TABLE student_2nf (
    roll      INTEGER     PRIMARY KEY,
    sname     VARCHAR(60) NOT NULL,
    dept      CHAR(6)     NOT NULL,
    dept_head VARCHAR(60) NOT NULL   -- still transitive!
);
CREATE TABLE course_2nf (
    course  CHAR(8)     PRIMARY KEY,
    ctitle  VARCHAR(60) NOT NULL,
    credits SMALLINT    NOT NULL
);
CREATE TABLE enrolment (
    roll   INTEGER NOT NULL,
    course CHAR(8) NOT NULL,
    grade  CHAR(2),
    PRIMARY KEY (roll, course)
);

-- ============ STAGE 3: reach 3NF ============
-- remove the transitive dependency dept -> dept_head
CREATE TABLE department (
    dept      CHAR(6)     PRIMARY KEY,
    dept_head VARCHAR(60) NOT NULL
);
CREATE TABLE student (
    roll  INTEGER     PRIMARY KEY,
    sname VARCHAR(60) NOT NULL,
    dept  CHAR(6)     NOT NULL REFERENCES department(dept)
);

ALTER TABLE enrolment
    ADD FOREIGN KEY (roll)   REFERENCES student(roll),
    ADD FOREIGN KEY (course) REFERENCES course_2nf(course);
ALTER TABLE student_phone
    ADD FOREIGN KEY (roll) REFERENCES student(roll)
        ON DELETE CASCADE;

-- ============ FINAL SCHEMA — all in BCNF ============
--   department(dept, dept_head)          key: dept
--   student(roll, sname, dept)           key: roll
--   course_2nf(course, ctitle, credits)  key: course
--   enrolment(roll, course, grade)       key: (roll,course)
--   student_phone(roll, phone)           key: (roll,phone)
--
-- Every determinant in every table IS the key -> BCNF ✔

-- ============ THE ANOMALIES ARE GONE ============
INSERT INTO department VALUES ('ACtE07','Prof. Sharma'),
                              ('AExE01','Prof. Karki');
INSERT INTO student VALUES (101,'Ram','ACtE07'),
                           (102,'Sita','ACtE07'),
                           (103,'Hari','AExE01');
INSERT INTO course_2nf VALUES ('ACtE0703','Database Systems',3),
                              ('AExE0101','Circuit Theory',4);
INSERT INTO enrolment VALUES (101,'ACtE0703','B'),
                             (101,'AExE0101','A'),
                             (102,'ACtE0703','A');

-- 1. INSERTION now possible without related data:
INSERT INTO course_2nf VALUES ('ACtE0705','Operating Systems',3);
-- a course with zero enrolments — impossible before

-- 2. UPDATE touches exactly ONE row:
UPDATE department SET dept_head = 'Prof. Adhikari'
WHERE  dept = 'ACtE07';
-- one row, so partial-update inconsistency CANNOT occur

-- 3. DELETION loses nothing unrelated:
DELETE FROM enrolment WHERE roll = 102;
SELECT * FROM course_2nf WHERE course = 'ACtE0703';
-- the course title and credits survive ✔

-- and the original wide view is recoverable by joining:
SELECT s.roll, s.sname, d.dept, d.dept_head,
       c.course, c.ctitle, c.credits, e.grade
FROM   student s
JOIN   department d  ON d.dept   = s.dept
JOIN   enrolment  e  ON e.roll   = s.roll
JOIN   course_2nf c  ON c.course = e.course
ORDER  BY s.roll, c.course;

Lossless join and dependency preservation

Any decomposition must be checked for two properties. LOSSLESS JOIN — joining the pieces back gives EXACTLY the original relation, with no spurious extra tuples. TEST for a binary decomposition of R into R1 and R2: the decomposition is lossless iff (R1 ∩ R2) → R1 or (R1 ∩ R2) → R2 i.e. the common attributes contain a key of at least one of the two. GOOD: R(roll, sname, dept) → R1(roll, sname), R2(roll, dept) R1 ∩ R2 = {roll}, and roll → sname, so lossless ✔ BAD: R(roll, sname, dept) → R1(roll, sname), R2(sname, dept) R1 ∩ R2 = {sname}, and sname determines nothing → LOSSY. Two students named Ram in different departments produce spurious rows on the join. DEPENDENCY PRESERVATION — every FD of the original can still be checked within a SINGLE decomposed relation, without a join. If F is the original FD set and R1..Rn the pieces, the decomposition preserves dependencies iff (F₁ ∪ F₂ ∪ … ∪ Fₙ)⁺ = F⁺ where Fᵢ is F projected onto Rᵢ. The BCNF example above FAILS this: (student, subject) → teacher spans both pieces. THE GUARANTEES, which is the exam-critical summary: lossless join dependency preserving 3NF decomposition ALWAYS ALWAYS BCNF decomposition ALWAYS NOT guaranteed So 3NF synthesis (build one relation per FD in the minimal cover, then add a relation containing a candidate key) is the algorithm of choice: it gives you both properties, always. SPURIOUS TUPLES — a concrete demonstration of lossiness: original R: roll sname dept 101 Ram ACtE07 103 Ram AExE01 R1(roll,sname): (101,Ram) (103,Ram) R2(sname,dept): (Ram,ACtE07) (Ram,AExE01) R1 ⋈ R2 on sname gives FOUR rows: (101,Ram,ACtE07) ✔ real (101,Ram,AExE01) ✗ SPURIOUS — invented (103,Ram,ACtE07) ✗ SPURIOUS — invented (103,Ram,AExE01) ✔ real The join produced information that was never in the database. That is why losslessness is not optional.

🌍 Go further: normalization does not stop at BCNF. 4NF removes multivalued dependencies (a lecturer's set of courses and set of skills are independent, so storing them in one table produces a cross-product of rows). 5NF removes join dependencies. Both are rare in practice because a good E-R design avoids them. Far more common is the deliberate opposite: denormalization for read performance, which is what a data warehouse star schema is — and the discipline there is that the denormalized copy is derived and rebuildable, never the source of truth. Search "4NF multivalued dependency example" and "Kimball star schema".

💡 Exam angle: the guaranteed question is "normalize this table to 3NF/BCNF, showing each step". Work in order: check 1NF (atomic), then find all candidate keys, then look for partial dependencies (2NF), then transitive ones (3NF), then any non-superkey determinant (BCNF). State the definitions precisely — 3NF allows X → Y when Y is prime, BCNF does not. Name the three anomalies (insertion, update, deletion) and know the lossless-join test for a binary decomposition, plus the fact that 3NF guarantees both properties while BCNF may sacrifice dependency preservation.

Syllabus points

Normalization to 1NF, 2NF, 3NF, BCNF (numerical)

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.