Menu

Earn Premium with Referrals

Invite your friends and earn Premium rewards through our referral program.

See how it works and start inviting friends.

Normalization Quiz
DBMS

Normalization Quiz

Test your skills on functional dependencies and normal forms (1NF-BCNF).

Test your understanding of normalization concepts.

A table where every attribute is atomic is in:

  • A) 1NF
  • B) 2NF
  • C) 3NF
  • D) BCNF

Correct Answer: A) 1NF

Explanation: 1NF requires that every column contains atomic (indivisible) values. No repeating groups allowed. Higher NFs require 1NF as a prerequisite plus additional conditions.

What is removed to transition from 1NF to 2NF?

  • A) Transitive Dependencies
  • B) Partial Functional Dependencies
  • C) Multi-valued attributes
  • D) All of the above

Correct Answer: B) Partial Functional Dependencies

Explanation: 2NF removes partial dependencies — where a non-prime attribute depends on only part of a composite candidate key. Transitive dependencies are removed in 3NF.

A table is in 3NF if it is in 2NF and has no:

  • A) Primary Keys
  • B) Partial Dependencies
  • C) Transitive Dependencies
  • D) Foreign Keys

Correct Answer: C) Transitive Dependencies

Explanation: 3NF removes transitive dependencies — where a non-key attribute depends on another non-key attribute. Example: Course_ID → Instructor → Instructor_Office (Instructor_Office depends transitively on Course_ID via Instructor).

Given R(A,B,C) and FDs {A → B, B → C}. What is the highest normal form?

  • A) 1NF
  • B) 2NF
  • C) 3NF
  • D) BCNF

Correct Answer: B) 2NF

Explanation: A is the Candidate Key (A⁺ = {A,B,C}). B → C is a transitive dependency (non-prime B determines non-prime C). This violates 3NF but not 2NF (no partial dependency since PK is single attribute).

Which normal form requires every determinant to be a candidate key?

  • A) 1NF
  • B) 2NF
  • C) 3NF
  • D) BCNF

Correct Answer: D) BCNF

Explanation: BCNF (Boyce-Codd Normal Form) requires that for every non-trivial FD X → Y, X must be a Super Key. This is stricter than 3NF.

A Lossless Join decomposition requires:

  • A) All tables have the same number of rows
  • B) The common attribute is a super key in at least one table
  • C) All FDs are preserved
  • D) The tables have no common attributes

Correct Answer: B) The common attribute is a super key in at least one table

Explanation: For a decomposition to be lossless, the common attribute(s) between the two resulting tables must be a super key in at least one of them. Otherwise, joining them back produces spurious rows.

What is the main reason to denormalize a database?

  • A) To improve write performance
  • B) To improve read performance by avoiding JOINs
  • C) To reduce storage space
  • D) To satisfy 3NF requirements

Correct Answer: B) To improve read performance by avoiding JOINs

Explanation: Denormalization intentionally adds redundancy to reduce the number of JOINs needed for queries. This sacrifices write performance and integrity for read speed, common in OLAP/data warehouse systems.

A table that is in 3NF but violates BCNF has:

  • A) A non-key attribute determining another non-key attribute
  • B) A determinant that is not a candidate key
  • C) A composite primary key
  • D) Multi-valued attributes

Correct Answer: B) A determinant that is not a candidate key

Explanation: BCNF violation occurs when an attribute (like Advisor) determines another attribute (Major) but Advisor is not a candidate key. The table may still be in 3NF if there are no transitive dependencies on non-prime attributes.

Quick Revision

  • 1NF: Single atomic values
  • 2NF: No partial PK dependency
  • 3NF: No transitive dependencies
  • BCNF: Every determinant is a candidate key
  • Decomposition should be lossless and dependency preserving
  • Denormalize only when read performance is critical

My Private Notes

Notes are auto-saved locally to this device.