BCNF, 4NF, 5NF — Higher Normal Forms

Higher normal form — BCNF, 4NF, 5NF

Read: ~38 min Hard 10 practice problems Live SQLite runner

1. Why 3NF Is Sometimes Not Enough

3NF removes partial and transitive dependencies on the primary key. But a table can have multiple candidate keys, and 3NF turns a blind eye to dependencies whose left-hand side is not a candidate key, as long as the right-hand side is part of one. BCNF (Boyce–Codd) closes that loophole. Beyond BCNF, two newer concerns appear: independent multi-valued facts (4NF) and decompositions that need three or more pieces to reconstruct the original (5NF).

3NF কেবল primary key-এর ওপর partial ও transitive dependency দূর করে। কিন্তু একটি টেবিলে একাধিক candidate key থাকতে পারে এবং সেখানকার কিছু dependency 3NF ধরতে পারে না। BCNF সেই ফাঁক বন্ধ করে। এর পর আসে স্বাধীন multi-valued তথ্যের জন্য 4NF এবং তিন বা ততোধিক টেবিলে ভেঙে শুধু পুনর্গঠন সম্ভব এমন ক্ষেত্রের জন্য 5NF।

This module is harder than module 26. We will keep examples concrete and every claim runnable.

2. Boyce–Codd Normal Form (BCNF)

Rule: A relation is in BCNF if, for every non-trivial functional dependency X → Y, the determinant X is a superkey. In plain English: any attribute that determines another must itself be capable of identifying a whole row.

BCNF-এর নিয়ম: প্রতিটি non-trivial functional dependency X → Y-এর জন্য X অবশ্যই একটি superkey হতে হবে। সহজ কথায়, যা অন্য কোনো attribute নির্ধারণ করে তাকে নিজে একটি row identify করতে পারার মতো শক্তিশালী হতে হবে।

The classic 3NF-but-not-BCNF example

Imagine a coaching centre. A teacher teaches one specific subject; a student in a given subject is assigned to one teacher. So:

(student, subject) → teacher — given a student and a subject, the teacher is fixed.
teacher → subject — a teacher only ever teaches one subject.

The candidate keys are (student, subject) and (student, teacher). Every attribute is part of some key, so 3NF is satisfied. But the dependency teacher → subject has a determinant (teacher) that is not a superkey — that is the BCNF violation.

bcnf-bad.sql

-- 3NF holds but BCNF fails.
CREATE TABLE teaches (
    student TEXT,
    subject TEXT,
    teacher TEXT,
    PRIMARY KEY (student, subject)
);
INSERT INTO teaches VALUES
 ('Arif',  'DBMS', 'Karim'),
 ('Sadia', 'DBMS', 'Karim'),
 ('Arif',  'OS',   'Rahim');

-- Karim's subject is duplicated on every row he teaches: redundancy.
SELECT * FROM teaches;

bcnf-fixed.sql

-- BCNF-decomposition: split on the offending dependency teacher → subject.
CREATE TABLE teacher_subject (
    teacher TEXT PRIMARY KEY,
    subject TEXT
);
CREATE TABLE student_teacher (
    student TEXT,
    teacher TEXT,
    PRIMARY KEY (student, teacher),
    FOREIGN KEY (teacher) REFERENCES teacher_subject(teacher)
);

INSERT INTO teacher_subject VALUES ('Karim', 'DBMS'), ('Rahim', 'OS');
INSERT INTO student_teacher VALUES
 ('Arif', 'Karim'), ('Sadia', 'Karim'), ('Arif', 'Rahim');

SELECT st.student, ts.subject, ts.teacher
FROM student_teacher st
JOIN teacher_subject ts ON ts.teacher = st.teacher;

Cost of BCNF. The decomposition is lossless — the join always reconstructs the original table — but it does not preserve the dependency (student, subject) → teacher. To enforce that rule we now need a CHECK across two tables, which most engines cannot do declaratively.

BCNF lossless হলেও (student, subject) → teacher dependency আর একটি টেবিলে enforce করা যাচ্ছে না।

3. Two Properties Every Decomposition Must Care About

✅ Lossless join (তথ্য হারানো যাবে না)

Joining the decomposed pieces back together must produce exactly the original rows — no spurious rows added, none missing. This is non-negotiable.

ভাঙা টেবিলগুলো join করলে ঠিক মূল টেবিল ফিরে আসতে হবে — অতিরিক্ত সারি আসা যাবে না, বাদও যাবে না।

⚠️ Dependency preservation (নিয়ম রক্ষা)

Every original functional dependency should still be enforceable using only one decomposed table. BCNF sometimes loses this; 3NF always preserves it.

মূল প্রতিটি FD যেন decompose করার পরও কেবল একটি টেবিলেই enforce করা যায়। BCNF কখনও কখনও এটি হারায়।

Verifying losslessness empirically

verify-lossless.sql

-- Reconstruct the original via join. The result should match teaches exactly.
SELECT st.student, ts.subject, ts.teacher
FROM student_teacher st
JOIN teacher_subject ts ON ts.teacher = st.teacher
EXCEPT
SELECT student, subject, teacher FROM teaches;
-- Empty result == lossless decomposition. ✓

4. Fourth Normal Form (4NF) — Multivalued Dependencies

A multivalued dependency (MVD) X →→ Y says: for each value of X, the set of Y values is independent of every other column. The classic example: a student has many hobbies, and many languages, and the two have nothing to do with each other.

একজন ছাত্রের একাধিক hobby থাকতে পারে, একই সাথে একাধিক language জানা থাকতে পারে — এবং hobby ও language পরস্পরের সাথে সম্পর্কহীন। এই ধরনের নির্ভরশীলতাকেই বলা হয় multivalued dependency।

4nf-bad.sql

-- BCNF holds (the table is "all-key"), but 4NF fails: redundant cross-product.
CREATE TABLE student_facts (
    student  TEXT,
    hobby    TEXT,
    language TEXT,
    PRIMARY KEY (student, hobby, language)
);
INSERT INTO student_facts VALUES
 ('Arif', 'Cricket', 'Bangla'),
 ('Arif', 'Cricket', 'English'),
 ('Arif', 'Reading', 'Bangla'),
 ('Arif', 'Reading', 'English');

-- Adding "Hindi" forces 2 new rows, not 1.
SELECT * FROM student_facts;

4NF rule: for every non-trivial multivalued dependency X →→ Y, X must be a superkey. To fix, split into two binary tables.

4nf-fixed.sql

CREATE TABLE student_hobby    (student TEXT, hobby    TEXT, PRIMARY KEY(student,hobby));
CREATE TABLE student_language (student TEXT, language TEXT, PRIMARY KEY(student,language));

INSERT INTO student_hobby    VALUES ('Arif', 'Cricket'), ('Arif', 'Reading');
INSERT INTO student_language VALUES ('Arif', 'Bangla'),  ('Arif', 'English');

-- Adding Hindi now adds exactly 1 row, not 2:
INSERT INTO student_language VALUES ('Arif', 'Hindi');

SELECT * FROM student_hobby;
SELECT * FROM student_language;

Figure 27.1 — 4NF লঙ্ঘন হলে cross-product রো বাড়তে থাকে; ভাঙলে আকার কমে যায়।

5. Fifth Normal Form (5NF) — Join Dependencies

A join dependency says a relation can be losslessly split into three (or more) tables that cannot be reconstructed from any pair of them — only from all three together. 5NF (also called PJ/NF) eliminates such dependencies that are not implied by candidate keys.

Join dependency বলতে বোঝায়, একটি টেবিলকে ৩ বা তারও বেশি ভাগে এমনভাবে ভাঙা সম্ভব যে কোনো দুটি অংশের join থেকে মূল টেবিল পাওয়া যাবে না — সব অংশ একসাথে join করলেই কেবল মূল ফেরত আসবে। 5NF এই ধরনের dependency দূর করে।

Real 5NF-only violations are rare. The textbook example: agent–company–product. If an agent sells a company's products, the agent has a contract with that company, and the company makes that product, then the three-way fact (agent, company, product) can be split, but only the three-way join recovers it.

5nf-example.sql

-- Three binary projections — the 5NF decomposition.
CREATE TABLE agent_company (agent TEXT, company TEXT, PRIMARY KEY(agent,company));
CREATE TABLE agent_product (agent TEXT, product TEXT, PRIMARY KEY(agent,product));
CREATE TABLE company_product (company TEXT, product TEXT, PRIMARY KEY(company,product));

INSERT INTO agent_company   VALUES ('Arif','Walton'), ('Arif','Pran');
INSERT INTO agent_product   VALUES ('Arif','TV'),     ('Arif','Juice');
INSERT INTO company_product VALUES ('Walton','TV'), ('Pran','Juice');

-- Reconstruct the valid (agent, company, product) triples — only the 3-way join works.
SELECT ac.agent, ac.company, cp.product
FROM agent_company ac
JOIN company_product cp ON cp.company = ac.company
JOIN agent_product   ap ON ap.agent   = ac.agent AND ap.product = cp.product;

Reality check. In ten years of building OLTP applications, you may never run into a genuine 5NF violation. Knowing it exists is enough; obsessing over it is not engineering.

বাস্তব OLTP-তে 5NF লঙ্ঘন প্রায় কখনোই দেখা যায় না — শব্দটি জানা যথেষ্ট, এর পিছনে অযথা সময় দেওয়া দরকার নেই।

6. The Whole Hierarchy at a Glance

Form	What it forbids	বাংলায়
1NF	Multi-valued cells.	একই cell-এ একাধিক মান।
2NF	Partial dependency on a composite key.	composite key-এর অংশের ওপর নির্ভরশীলতা।
3NF	Transitive dependency on the primary key.	indirect (transitive) নির্ভরশীলতা।
BCNF	Any dependency whose left side is not a superkey.	যেকোনো dependency যেখানে বাঁ-পাশের কলাম superkey নয়।
4NF	Independent multivalued facts in one table.	একটি টেবিলে একাধিক স্বাধীন multi-valued তথ্য।
5NF	Join dependencies not implied by keys.	key-এর বাইরে আসা join dependency।

স্মরণে রাখুন — উচ্চতর normal form-গুলো নিচের সব form-কে ধারণ করে। 4NF-এ থাকা মানে BCNF, 3NF, 2NF এবং 1NF-এ থাকা।

7. Trade-offs — How High Is High Enough?

✅ Push to BCNF when (BCNF জরুরি যখন)

Two candidate keys overlap and one functional dependency is on a non-superkey.
Audit or financial systems where every redundancy is a bug.
You can enforce the lost dependency at the application layer or with triggers.

⚠️ Stay at 3NF when (3NF যথেষ্ট যখন)

Dependency preservation matters more than the small redundancy.
The team needs simple, single-table CHECK constraints.
The non-superkey FD almost never updates in production.

Engineer's rule. Default to 3NF. Reach for BCNF only when a real, observed anomaly justifies losing dependency preservation. 4NF whenever you spot a true cross-product. 5NF you can read about and forget.

ডিফল্ট রাখুন 3NF-এ। বাস্তবে anomaly দেখা গেলে BCNF-এ যান। cross-product দেখলে 4NF। 5NF কেবল জানা থাকলেই হবে।

8. Glossary (শব্দকোষ)

Term	Meaning	বাংলায়
Superkey	Any set of columns that uniquely identifies a row.	যে কলাম-সেট প্রতিটি সারিকে unique-ভাবে identify করে।
Candidate key	A minimal superkey.	সবচেয়ে ছোট superkey।
MVD	Multivalued dependency, written X →→ Y.	multi-valued নির্ভরশীলতা, X →→ Y।
Join dependency	The relation is the join of three or more projections.	একটি relation একাধিক projection-এর join।
Lossless decomposition	Joining the parts gives back the whole.	ভাঙা টেবিলগুলো join-এ মূল টেবিল ফিরে আসে।
Dependency preservation	Every FD can be enforced on a single decomposed table.	প্রতিটি FD এক টেবিলে enforce করা সম্ভব।

9. Practice Problems

Ten problems on BCNF, 4NF and 5NF. Most have a runnable answer in SQLite.

BCNF, 4NF এবং 5NF নিয়ে ১০টি প্রশ্ন। বেশিরভাগেরই চালু-করার-যোগ্য SQLite উত্তর আছে।

In teaches(student, subject, teacher) with FDs (student,subject)→teacher and teacher→subject, identify all candidate keys.

উপরের teaches টেবিলে candidate key কোনগুলো?

✨ Show Answer (উত্তর দেখুন)

Answer: (student, subject) and (student, teacher). Both minimally determine every attribute. Therefore teacher is part of one key, which is why 3NF is satisfied even though BCNF is not.

Show with SQL that the BCNF decomposition of teaches can lose the FD (student, subject) → teacher: insert two rows that are individually valid but jointly violate the original FD.

BCNF-এ ভাঙার পর কীভাবে (student, subject) → teacher dependency হারিয়ে যায়, SQL দিয়ে দেখান।

✨ Show Answer (উত্তর দেখুন)

ans2.sql

CREATE TABLE teacher_subject(teacher TEXT PRIMARY KEY, subject TEXT);
CREATE TABLE student_teacher(student TEXT, teacher TEXT, PRIMARY KEY(student,teacher));

INSERT INTO teacher_subject VALUES ('Karim','DBMS'), ('Salam','DBMS');
-- Both rows are individually valid in their tables...
INSERT INTO student_teacher VALUES ('Arif','Karim'), ('Arif','Salam');
-- ...but the join now says Arif takes DBMS from TWO teachers — which the original FD forbade.
SELECT st.student, ts.subject, COUNT(*)
FROM student_teacher st JOIN teacher_subject ts ON ts.teacher=st.teacher
GROUP BY st.student, ts.subject
HAVING COUNT(*) > 1;

Is employee(emp_id, email, ssn) in BCNF if every column uniquely identifies a row?

যদি তিনটি কলামই unique হয়, তবে এটি কি BCNF-এ আছে?

✨ Show Answer (উত্তর দেখুন)

Answer: Yes. There are three candidate keys (emp_id, email, ssn) and every FD's left side is one of them — every determinant is a superkey. Trivially in BCNF.

Detect a 4NF violation in course_book_lecturer(course, book, lecturer) where books and lecturers for a course are independent. Decompose it.

উপরের টেবিলে 4NF লঙ্ঘন আছে কি? থাকলে decompose করুন।

✨ Show Answer (উত্তর দেখুন)

ans4.sql

CREATE TABLE course_book     (course TEXT, book     TEXT, PRIMARY KEY(course,book));
CREATE TABLE course_lecturer (course TEXT, lecturer TEXT, PRIMARY KEY(course,lecturer));

INSERT INTO course_book     VALUES ('DBMS','Elmasri'), ('DBMS','Korth');
INSERT INTO course_lecturer VALUES ('DBMS','Karim'),   ('DBMS','Tahmina');
SELECT * FROM course_book;
SELECT * FROM course_lecturer;

Why is (student, hobby, language) NOT a 4NF violation if the student has only one hobby and one language?

যদি কোনো ছাত্রের শুধু একটি hobby এবং একটি language থাকে, তবে কেন এটি 4NF লঙ্ঘন নয়?

✨ Show Answer (উত্তর দেখুন)

Answer: Because no genuine multivalued dependency exists. With only one hobby and one language per student, student is a key and the table is in BCNF and trivially 4NF. The multivalued nature must be present in the data semantics, not just the schema.
Verify losslessness of the agent-company-product 5NF decomposition with an EXCEPT query.

agent-company-product decomposition lossless কিনা EXCEPT দিয়ে যাচাই করুন।
✨ Show Answer (উত্তর দেখুন)
ans6.sql
```
SELECT ac.agent, ac.company, cp.product
FROM agent_company ac
JOIN company_product cp ON cp.company=ac.company
JOIN agent_product   ap ON ap.agent=ac.agent AND ap.product=cp.product
EXCEPT
SELECT agent, company, product FROM acp;
-- Empty set ⇒ lossless.
```
Two-table decomposition is enough for 4NF but not for some 5NF cases. Why?

4NF-এর জন্য দুই টেবিল যথেষ্ট, কিন্তু কিছু 5NF case-এ নয় কেন?

✨ Show Answer (উত্তর দেখুন)

Answer: A multivalued dependency creates a Cartesian-product redundancy that splits cleanly into two binary tables. A genuine join dependency creates a triangular constraint where any pair of binary projections is missing some information; you need all three to reconstruct the original. That is precisely the difference between 4NF and 5NF.
Suppose flight(flight_no, day, pilot) with FDs (flight_no, day) → pilot and pilot → flight_no. What is the highest normal form it is in?

উপরের টেবিল কোন highest normal form-এ আছে?

✨ Show Answer (উত্তর দেখুন)

Answer: Candidate keys are (flight_no, day) and (pilot, day). Every attribute is part of some key, so 3NF holds. But pilot → flight_no has a non-superkey determinant — BCNF fails. Highest form: 3NF.
Decompose the BCNF-violating flight table and discuss whether dependency preservation is lost.

উপরের flight টেবিলকে BCNF-এ আনুন এবং dependency preservation আলোচনা করুন।
✨ Show Answer (উত্তর দেখুন)
ans9.sql
```
CREATE TABLE pilot_flight (pilot TEXT PRIMARY KEY, flight_no TEXT);
CREATE TABLE pilot_day    (pilot TEXT, day TEXT, PRIMARY KEY(pilot,day));

INSERT INTO pilot_flight VALUES ('Captain Arif', 'BG-101');
INSERT INTO pilot_day    VALUES ('Captain Arif', 'Mon');
SELECT pd.pilot, pd.day, pf.flight_no
FROM pilot_day pd JOIN pilot_flight pf ON pf.pilot=pd.pilot;
```
The original FD (flight_no, day) → pilot now spans two tables, so it is no longer enforceable in a single table. Dependency preservation is lost — a known cost of BCNF.
Give one production heuristic for choosing between 3NF and BCNF.

3NF এবং BCNF-এর মধ্যে প্রোডাকশনে কোনটি বেছে নেবেন — একটি practical heuristic দিন।

✨ Show Answer (উত্তর দেখুন)

Answer: "Default to 3NF; switch to BCNF only when (a) the redundancy you would otherwise carry is large and frequently updated, AND (b) you can enforce the lost FD with a deferred trigger or an application-side invariant test." If both conditions are not met, the small redundancy of 3NF is cheaper than the architectural cost of BCNF.

Summary — Module 27

Above 3NF, three more steps exist. BCNF requires every functional dependency's determinant to be a superkey — sometimes at the cost of dependency preservation. 4NF removes Cartesian-product redundancy from independent multivalued facts. 5NF handles rare join dependencies that decompose into three or more pieces. In practice, 3NF or BCNF cover almost everything; 4NF is occasional; 5NF is largely theoretical.

3NF-এর পরে আরও তিনটি স্তর — BCNF, 4NF, 5NF। BCNF প্রতিটি FD-এর বাঁ-পাশ superkey দাবি করে, কখনও কখনও dependency preservation হারায়। 4NF independent multivalued তথ্যের cross-product দূর করে। 5NF বিরল join dependency সামলায়। বাস্তবে 3NF বা BCNF-ই অধিকাংশ system-এর জন্য যথেষ্ট।

Next Module → Denormalization — কখন এবং কেন নিয়ম ভাঙবেন।

Previous (পূর্ববর্তী) Next: Denormalization