Skip to content

Artificial Intelligence, Data Analytics and Cyber Security - Laws and Practice · Database Management

Relational Model and Normalization in DBMS: 1NF to BCNF

Updated 11 October 2026 · Fact-checked

The relational model stores data as tables (relations) of rows and columns, kept correct by integrity constraints. Normalization removes redundancy by splitting tables using functional dependencies. To solve a question, find the candidate keys, list the dependencies, check each normal form from 1NF up to BCNF, then decompose where a rule is broken.

Understand Relational Model and Normalization

The relational model stores data in relations, which you see as tables. A row is a tuple. A column is an attribute. The set of allowed values for an attribute is its domain. The degree of a relation is its number of attributes. Its cardinality is its number of tuples.

Keys keep rows unique and linked. A super key is any set of attributes that identifies a row uniquely. A candidate key is a minimal super key. The primary key is the candidate key you choose, and it cannot be NULL. A foreign key is an attribute in one table that refers to the primary key of another.

Integrity constraints protect the data. Domain constraints restrict values. Entity integrity says no primary key value can be NULL. Referential integrity says a foreign key value must match an existing primary key value in the parent table, or be NULL where allowed.

A relational algebra query combines relations with operators. Select (σ) picks rows. Project (π) picks columns and removes duplicates. Union, intersection and difference need union-compatible relations. Cartesian product (×) pairs every row with every row. Join (⋈) combines related rows. Division (÷) finds values related to all rows of another relation.

Bad table design causes anomalies. If a customer's address is repeated in many rows, you must update every copy (update anomaly). You may be unable to add a fact without unrelated data (insertion anomaly). Deleting one row may lose other facts (deletion anomaly). Normalization fixes this using functional dependencies. X → Y means each value of X determines exactly one value of Y. You test the table against the normal forms, 1NF to BCNF, and split it where a rule fails. Each split must be lossless.

Key rules to remember

Functional dependency
X → Y
If two tuples agree on X, they must agree on Y. X is the determinant.
Attribute closure
X⁺ = set of all attributes determined by X
X is a candidate key if X⁺ contains all attributes and no proper subset of X does the same.
Armstrong's axioms
Reflexivity: Y ⊆ X ⇒ X → Y. Augmentation: X → Y ⇒ XZ → YZ. Transitivity: X → Y and Y → Z ⇒ X → Z
Union, decomposition and pseudo-transitivity follow from these.
1NF
All attribute values are atomic; no repeating groups
Each cell holds a single value.
2NF
1NF + no partial dependency of a non-prime attribute on part of a candidate key
Matters only when a candidate key is composite.
3NF
For every FD X → A: X is a super key, or A is a prime attribute
Removes transitive dependency of non-prime attributes on a key.
BCNF
For every non-trivial FD X → A: X is a super key
Stricter than 3NF. Every BCNF relation is in 3NF.
Lossless join test (binary)
R1 ∩ R2 → R1 or R1 ∩ R2 → R2 (as FDs in R)
A decomposition of R into R1 and R2 is lossless if the common attributes determine one of the two.
Join size bounds
Cartesian product R × S has |R| × |S| tuples and degree(R) + degree(S)
Natural join has at most |R| × |S| tuples.

How to solve Relational Model and Normalization questions

Use this order for any normalization question. Do not skip the key-finding step, because every normal form depends on it.

  1. 1Write down the attributes and all given functional dependencies clearly.
  2. 2Find the candidate keys using attribute closure. Mark the prime attributes (those in any candidate key).
  3. 3Check 1NF: look for multi-valued or repeating attributes. If any, make each value atomic.
  4. 4Check 2NF: for each composite candidate key, see if any non-prime attribute depends on part of it. If so, move it to a new relation with that part.
  5. 5Check 3NF: for each FD X → A, X must be a super key or A must be prime. If neither holds, you have a transitive dependency. Split it out.
  6. 6Check BCNF: every determinant in a non-trivial FD must be a super key. If not, decompose on that FD.
  7. 7State the final relations with their keys. Confirm the split is lossless and say whether dependencies are preserved.
  8. 8Name the highest normal form of the original relation and give the reason in one line.

Quickest way: Key first, then test every FD

When to use it: Use this when time is short and the question gives a relation with a list of FDs and asks for the highest normal form.

  1. Find the candidate key by closure. Note prime attributes.
  2. Take each FD one by one. Ask: is the left side a super key?
  3. If yes for all FDs, the relation is in BCNF.
  4. If some left side is not a super key, ask: is the right side prime? If yes for all such FDs, it is in 3NF but not BCNF.
  5. If not, check whether a non-prime attribute depends on part of a key (2NF fail) or on another non-prime attribute (3NF fail).
  6. Write the decomposition with keys and stop. Do not over-explain.

Common mistakes in Relational Model and Normalization

  • Choosing the wrong candidate key, or only one when there are several.

    Students guess the key from column names instead of computing closure.

    Fix: Compute the closure of the likely key. Also check whether other attributes on the right sides of FDs can act as keys.

  • Applying 2NF to a relation whose key is a single attribute.

    Students forget that partial dependency needs a composite key.

    Fix: If every candidate key is a single attribute, the relation is automatically in 2NF once it is in 1NF.

  • Saying 3NF and BCNF are the same.

    Both deal with non-key dependencies and most simple examples satisfy both.

    Fix: 3NF allows X → A where A is prime. BCNF does not. A relation can be in 3NF but not BCNF when a non-key attribute determines part of a key.

  • Decomposing without checking lossless join.

    Students focus on removing the violating FD and stop there.

    Fix: Check that the common attributes of the two new relations form a key of at least one of them.

  • Using π to mean row selection and σ to mean column selection in relational algebra.

    The symbols are easy to swap under pressure.

    Fix: Remember: σ selects rows by condition; π projects columns.

  • Ignoring trivial dependencies and declaring a violation.

    Students test every FD including ones like AB → A.

    Fix: A dependency X → Y is trivial if Y ⊆ X. Trivial FDs never violate any normal form.

Worked examples

Example 1

A relation R(A, B, C, D) has the FDs: A → B, B → C, and C → D. Find the candidate key and the highest normal form. Decompose it into 3NF.

Show the solution
  1. Closure of A: A → B, so B is added. B → C adds C. C → D adds D. A⁺ = {A, B, C, D}, so A is a candidate key.
  2. No other attribute appears only on the left side, and A is on no right side, so A is the only candidate key. Prime attribute: A. Non-prime: B, C, D.
  3. 1NF: assume atomic values, so it holds.
  4. 2NF: the key A is a single attribute, so no partial dependency exists. It is in 2NF.
  5. 3NF: take B → C. B is not a super key and C is not prime. This fails 3NF. Similarly C → D fails. The relation has transitive dependencies: A → B → C → D.
  6. Decompose on B → C and C → D: R1(A, B), R2(B, C), R3(C, D).
  7. Keys: A in R1, B in R2, C in R3. Each FD now has a key on its left side, so all are in BCNF.
  8. Lossless check: R1 ∩ R2 = B, which is the key of R2. R2 ∩ R3 = C, which is the key of R3. The join is lossless and all FDs are preserved.

Answer: The candidate key is A. The relation is in 2NF but not 3NF. The 3NF decomposition is R1(A, B), R2(B, C), R3(C, D), which is lossless and dependency preserving.

Example 2

A relation CourseTeach(Student, Course, Teacher) has the FDs: (Student, Course) → Teacher and Teacher → Course. Find the candidate keys. Is it in 3NF? Is it in BCNF? Decompose if needed.

Show the solution
  1. Closure of (Student, Course): Teacher is added by the first FD. The result is {Student, Course, Teacher}, so it is a candidate key.
  2. Closure of (Student, Teacher): Teacher → Course adds Course. The result is {Student, Teacher, Course}, so (Student, Teacher) is also a candidate key.
  3. Prime attributes: Student, Course, Teacher. All three are prime.
  4. 3NF check: the FD Teacher → Course has a left side that is not a super key, since Teacher⁺ = {Teacher, Course}. But Course is prime. So the 3NF condition holds. The other FD has a key on its left side. The relation is in 3NF.
  5. BCNF check: Teacher → Course is non-trivial and Teacher is not a super key. So it is not in BCNF.
  6. Decompose on Teacher → Course: R1(Teacher, Course) and R2(Student, Teacher).
  7. Lossless check: R1 ∩ R2 = Teacher, which is the key of R1. So the join is lossless.
  8. Dependency check: (Student, Course) → Teacher cannot be checked within either relation alone. It is not preserved.

Answer: Candidate keys: (Student, Course) and (Student, Teacher). The relation is in 3NF but not BCNF. The BCNF decomposition is R1(Teacher, Course) and R2(Student, Teacher). It is lossless, but the FD (Student, Course) → Teacher is not preserved.

Exam tips

  • Start every normalization answer with the candidate keys. Examiners look for this first, and marks follow from it.
  • Write the FD list and closure steps visibly. Even if the final form is wrong, working earns credit.
  • For 3NF versus BCNF questions, state the exact test: prime attribute on the right for 3NF, super key on the left for BCNF.
  • In relational algebra questions, write the expression in order and name the operators (σ, π, ⋈). Add a one-line plain-English meaning.
  • Link the answer to practice where you can: normalized tables reduce duplicate records, which helps data accuracy and compliance records.

Practice questions from Database Management

Relational Model and Normalization: frequently asked questions

What is the difference between 3NF and BCNF?

In 3NF, for every FD X → A, either X is a super key or A is a prime attribute. In BCNF, X must always be a super key. So BCNF is stricter, and every BCNF relation is also in 3NF.

What is a functional dependency in DBMS?

A functional dependency X → Y means that each value of X is linked to exactly one value of Y. If two rows have the same X, they must have the same Y. For example, EmployeeID → EmployeeName.

Does normalization always preserve dependencies?

No. Decomposition into 3NF can always be both lossless and dependency preserving. Decomposition into BCNF is always lossless, but it may not preserve all dependencies.

Which relational algebra operators need union-compatible relations?

Union, intersection and set difference need union-compatible relations. They must have the same number of attributes, with matching domains. Select, project, product and join do not need this.

Is a relation with a single-attribute key always in 2NF?

Yes, if it is in 1NF. Partial dependency needs a composite key, so a single-attribute candidate key cannot have one.