Database Normalization MCQs: 60 Practice Questions & Answers (2026)
PerfectNotesUpdated: April 2026~15 min practice 60 Questions · 3 Levels 60 Questions · 3 Levels
Database Normalization MCQ practice questions are essential for preparing for university DBMS finals, GATE Computer Science, and software developer interviews. This comprehensive MCQ platform provides 60 carefully structured practice questionscovering First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce-Codd Normal Form (BCNF), Armstrong's Axioms, minimal covers, and lossless decomposition.
These questions are organized into three progressive levels of 20 questions each: Basics (covering atomic values, anomaly types, and foundational rules of each normal form), Concepts (covering schema scenarios, composite keys, and functional dependency mapping), and Advanced(covering BCNF vs 3NF trade-offs, Armstrong's Axioms, minimal covers, and Star Schema design). Each question includes a thorough explanation.
Use Study Mode to build conceptual understanding question-by-question, or use Exam Mode to simulate exam conditions with full scoring and detailed explanations revealed upon submission.
Contents
- 1.Basics (20 Questions)1NF rules · atomic values · anomaly types · candidate keys · functional dependencies
- 2.Concepts (20 Questions)Schema scenarios · composite keys · partial/transitive FDs · Armstrong's Axioms · closures
- 3.Advanced (20 Questions)BCNF vs 3NF · minimal cover · lossless join · Star Schema · 4NF
- 4.Conclusionsummary · next steps · study tips
- 5.Key Takeawaysquick-fire bullet recap of essential facts
- 6.Quick Review Summaryconcept · definition · key fact table
- 7.FAQcommon questions answered
Database Normalization — Basics
1What is the primary goal of database normalization?
CorrectA: To minimize data redundancy and eliminate insertion, update, and deletion anomalies
Normalization is the process of organizing a relational database to reduce data redundancy and prevent data anomalies (insertion, update, and deletion). It decomposes larger tables into smaller, well-structured ones linked by foreign keys.
IncorrectA: To minimize data redundancy and eliminate insertion, update, and deletion anomalies
Normalization is the process of organizing a relational database to reduce data redundancy and prevent data anomalies (insertion, update, and deletion). It decomposes larger tables into smaller, well-structured ones linked by foreign keys.
2Which rule strictly dictates that every attribute must contain only atomic (indivisible) values in First Normal Form?
CorrectB: Every attribute must contain only atomic (indivisible) values
First Normal Form (1NF) requires every cell to hold a single, atomic (indivisible) value. No column may store arrays, comma-separated lists, or repeating groups. Violating 1NF — e.g., storing multiple phone numbers in one cell — breaks the relational model.
IncorrectB: Every attribute must contain only atomic (indivisible) values
First Normal Form (1NF) requires every cell to hold a single, atomic (indivisible) value. No column may store arrays, comma-separated lists, or repeating groups. Violating 1NF — e.g., storing multiple phone numbers in one cell — breaks the relational model.
3What is a "Functional Dependency" in the context of relational databases?
CorrectC: A constraint where the value of one set of attributes uniquely determines the value of another set of attributes
A Functional Dependency (FD) X -> Y means that for every valid tuple, the value(s) of X uniquely determine the value(s) of Y. FDs are the mathematical foundation on which all normal forms are built.
IncorrectC: A constraint where the value of one set of attributes uniquely determines the value of another set of attributes
A Functional Dependency (FD) X -> Y means that for every valid tuple, the value(s) of X uniquely determine the value(s) of Y. FDs are the mathematical foundation on which all normal forms are built.
4Which anomaly occurs when you are forced to enter dummy data just to add a new record because a portion of the primary key is currently unknown?
CorrectD: Being forced to enter dummy data to add a new record because a portion of the primary key is unknown (Insertion Anomaly)
An Insertion Anomaly occurs when new data cannot be recorded without also supplying otherwise irrelevant data — typically because the new fact shares a table with a composite primary key whose other parts are unknown.
IncorrectD: Being forced to enter dummy data to add a new record because a portion of the primary key is unknown (Insertion Anomaly)
An Insertion Anomaly occurs when new data cannot be recorded without also supplying otherwise irrelevant data — typically because the new fact shares a table with a composite primary key whose other parts are unknown.
5In order for a table to be in Second Normal Form (2NF), it must first be in 1NF and completely free of which specific dependency?
CorrectA: It must be in 1NF and completely free of partial dependencies
2NF requires the table to already be in 1NF, and additionally demands that every non-prime attribute is fully functionally dependent on the entire primary key — not just a part of it (no partial dependencies).
IncorrectA: It must be in 1NF and completely free of partial dependencies
2NF requires the table to already be in 1NF, and additionally demands that every non-prime attribute is fully functionally dependent on the entire primary key — not just a part of it (no partial dependencies).
6What defines a "Partial Dependency" in a relational table?
CorrectB: A non-prime attribute depends on only a part of a composite primary key, rather than the entire key
A Partial Dependency occurs in a table with a composite primary key when a non-prime attribute is determined by only a subset of that key. This is the defining violation that prevents a table from reaching 2NF.
IncorrectB: A non-prime attribute depends on only a part of a composite primary key, rather than the entire key
A Partial Dependency occurs in a table with a composite primary key when a non-prime attribute is determined by only a subset of that key. This is the defining violation that prevents a table from reaching 2NF.
7Third Normal Form (3NF) builds upon 2NF by strictly prohibiting which type of dependency?
CorrectC: Transitive dependencies
3NF eliminates transitive dependencies: a non-prime attribute must not determine another non-prime attribute. Every non-prime attribute must depend directly and only on the primary key, nothing else.
IncorrectC: Transitive dependencies
3NF eliminates transitive dependencies: a non-prime attribute must not determine another non-prime attribute. Every non-prime attribute must depend directly and only on the primary key, nothing else.
8What is a "Transitive Dependency"?
CorrectD: When a non-prime attribute functionally determines another non-prime attribute
A Transitive Dependency exists when a non-prime attribute A determines another non-prime attribute B (A -> B). Because B is determined indirectly through A rather than directly through the primary key, it violates 3NF.
IncorrectD: When a non-prime attribute functionally determines another non-prime attribute
A Transitive Dependency exists when a non-prime attribute A determines another non-prime attribute B (A -> B). Because B is determined indirectly through A rather than directly through the primary key, it violates 3NF.
9Boyce-Codd Normal Form (BCNF) is often described as a slightly stronger, stricter version of which other normal form?
CorrectB: Third Normal Form (3NF)
BCNF (Boyce-Codd Normal Form) is a stricter refinement of 3NF. While 3NF allows a non-superkey determinant if the dependent is a prime attribute, BCNF removes that exception entirely — every determinant must be a superkey.
IncorrectB: Third Normal Form (3NF)
BCNF (Boyce-Codd Normal Form) is a stricter refinement of 3NF. While 3NF allows a non-superkey determinant if the dependent is a prime attribute, BCNF removes that exception entirely — every determinant must be a superkey.
10What is the defining rule of BCNF regarding functional dependencies (X -> Y)?
CorrectC: X must be a superkey or candidate key
BCNF's single defining rule: for every non-trivial functional dependency X -> Y in the relation, X must be a superkey (or candidate key). If any determinant is not a superkey, the table violates BCNF.
IncorrectC: X must be a superkey or candidate key
BCNF's single defining rule: for every non-trivial functional dependency X -> Y in the relation, X must be a superkey (or candidate key). If any determinant is not a superkey, the table violates BCNF.
11What is a "Prime Attribute"?
CorrectD: An attribute that is a part of any candidate key in the table
A prime attribute is any attribute that participates in at least one candidate key of the relation. Non-prime (non-key) attributes are all other attributes. The distinction is critical for defining 2NF and 3NF violations.
IncorrectD: An attribute that is a part of any candidate key in the table
A prime attribute is any attribute that participates in at least one candidate key of the relation. Non-prime (non-key) attributes are all other attributes. The distinction is critical for defining 2NF and 3NF violations.
12If a table has a single-column primary key and is currently in 1NF, what is its automatic status regarding 2NF?
CorrectA: It is automatically in 2NF, because partial dependencies require a composite primary key
Partial dependencies can only occur when the primary key is composite (two or more attributes). A single-column primary key cannot be partially determined, so any 1NF table with a simple key is automatically in 2NF.
IncorrectA: It is automatically in 2NF, because partial dependencies require a composite primary key
Partial dependencies can only occur when the primary key is composite (two or more attributes). A single-column primary key cannot be partially determined, so any 1NF table with a simple key is automatically in 2NF.
13An "Update Anomaly" occurs when:
CorrectB: Changing a single piece of logical data requires modifying multiple rows, risking data inconsistency if one is missed
An Update Anomaly arises when the same logical fact is stored in multiple rows. Updating one copy without updating all copies leaves the database in an inconsistent state — a direct consequence of unnormalized redundancy.
IncorrectB: Changing a single piece of logical data requires modifying multiple rows, risking data inconsistency if one is missed
An Update Anomaly arises when the same logical fact is stored in multiple rows. Updating one copy without updating all copies leaves the database in an inconsistent state — a direct consequence of unnormalized redundancy.
14What does "Denormalization" typically involve?
CorrectC: Intentionally introducing redundancy into a normalized database to optimize read performance for specific queries
Denormalization deliberately reintroduces controlled redundancy (violating strict NF rules) to reduce costly JOIN operations and improve read performance for analytical or reporting workloads.
IncorrectC: Intentionally introducing redundancy into a normalized database to optimize read performance for specific queries
Denormalization deliberately reintroduces controlled redundancy (violating strict NF rules) to reduce costly JOIN operations and improve read performance for analytical or reporting workloads.
15Which of the following examples directly violates First Normal Form (1NF)?
CorrectD: A table with a "Phone_Numbers" column storing comma-separated lists of numbers for each user
1NF requires atomic values. A column like "Phone_Numbers" that stores "555-1234, 555-5678" for a single row is a non-atomic, repeating group — a direct violation of 1NF.
IncorrectD: A table with a "Phone_Numbers" column storing comma-separated lists of numbers for each user
1NF requires atomic values. A column like "Phone_Numbers" that stores "555-1234, 555-5678" for a single row is a non-atomic, repeating group — a direct violation of 1NF.
16What is a "Candidate Key"?
CorrectA: A minimal set of attributes that can uniquely identify every tuple in a relation
A Candidate Key is a minimal superkey: a set of attributes that uniquely identifies every tuple, with no attributes that can be removed while preserving uniqueness. A table may have multiple candidate keys.
IncorrectA: A minimal set of attributes that can uniquely identify every tuple in a relation
A Candidate Key is a minimal superkey: a set of attributes that uniquely identifies every tuple, with no attributes that can be removed while preserving uniqueness. A table may have multiple candidate keys.
17When designing a relational schema, what is a "Surrogate Key"?
CorrectC: An artificial, system-generated identifier (like an auto-incrementing integer) used as the primary key
A Surrogate Key is a synthetic, system-generated identifier (e.g., AUTO_INCREMENT integer or UUID) with no business meaning. It is used when natural keys are complex, volatile, or non-existent.
IncorrectC: An artificial, system-generated identifier (like an auto-incrementing integer) used as the primary key
A Surrogate Key is a synthetic, system-generated identifier (e.g., AUTO_INCREMENT integer or UUID) with no business meaning. It is used when natural keys are complex, volatile, or non-existent.
18In the functional dependency A -> B, how is "A" formally classified?
CorrectD: The determinant
In the FD notation X -> Y, X is called the determinant (it does the determining) and Y is called the dependent. The determinant uniquely identifies the value of the dependent for every tuple in the relation.
IncorrectD: The determinant
In the FD notation X -> Y, X is called the determinant (it does the determining) and Y is called the dependent. The determinant uniquely identifies the value of the dependent for every tuple in the relation.
19Which of the following best describes a "Deletion Anomaly"?
CorrectA: Removing a specific record inadvertently results in the loss of completely separate, unrelated factual data
A Deletion Anomaly occurs when deleting a row to remove one fact unintentionally destroys other, unrelated facts that were stored in the same row — typically because unrelated entities are conflated in a single table.
IncorrectA: Removing a specific record inadvertently results in the loss of completely separate, unrelated factual data
A Deletion Anomaly occurs when deleting a row to remove one fact unintentionally destroys other, unrelated facts that were stored in the same row — typically because unrelated entities are conflated in a single table.
20To achieve true normalization, what conceptual model is typically analyzed before establishing functional dependencies?
CorrectB: An Entity-Relationship (ER) Diagram
The Entity-Relationship (ER) Diagram is the standard starting point for normalization. It maps entities, attributes, and relationships before tables are created, allowing functional dependencies to be identified systematically.
IncorrectB: An Entity-Relationship (ER) Diagram
The Entity-Relationship (ER) Diagram is the standard starting point for normalization. It maps entities, attributes, and relationships before tables are created, allowing functional dependencies to be identified systematically.
Database Normalization — Concepts
1Consider a table Orders (OrderID, ProductID, ProductName, Quantity). The composite primary key is (OrderID, ProductID). You observe the dependency ProductID -> ProductName. What normalization rule does this violate?
CorrectC: Second Normal Form (2NF)
ProductID -> ProductName is a partial dependency: ProductName is determined by only part of the composite primary key (OrderID, ProductID). A non-prime attribute depending on a key subset violates 2NF.
IncorrectC: Second Normal Form (2NF)
ProductID -> ProductName is a partial dependency: ProductName is determined by only part of the composite primary key (OrderID, ProductID). A non-prime attribute depending on a key subset violates 2NF.
2To resolve the 2NF violation in the Orders table, what structural change is required?
CorrectD: Create a separate Products table with ProductID and ProductName, and remove ProductName from the Orders table
To resolve a partial dependency, move the partially dependent attribute into its own table keyed by the partial determinant. Here, a Products(ProductID, ProductName) table is created, and ProductName is removed from Orders.
IncorrectD: Create a separate Products table with ProductID and ProductName, and remove ProductName from the Orders table
To resolve a partial dependency, move the partially dependent attribute into its own table keyed by the partial determinant. Here, a Products(ProductID, ProductName) table is created, and ProductName is removed from Orders.
3Consider a table Employees (EmpID, DeptID, DeptName, DeptLocation). The primary key is EmpID. You map the dependencies: EmpID -> DeptID and DeptID -> DeptName. What specific type of dependency exists between EmpID and DeptName?
CorrectA: Transitive Dependency
EmpID -> DeptID and DeptID -> DeptName together create a transitive dependency: EmpID determines DeptName only indirectly, via DeptID. DeptName is not directly dependent on the primary key EmpID.
IncorrectA: Transitive Dependency
EmpID -> DeptID and DeptID -> DeptName together create a transitive dependency: EmpID determines DeptName only indirectly, via DeptID. DeptName is not directly dependent on the primary key EmpID.
4Because of the dependencies in the Employees table (EmpID -> DeptID and DeptID -> DeptName), which normal form is currently being violated?
CorrectB: Third Normal Form (3NF)
The transitive dependency EmpID -> DeptID -> DeptName violates 3NF. In 3NF, every non-prime attribute must depend directly on the primary key — not transitively through another non-prime attribute like DeptID.
IncorrectB: Third Normal Form (3NF)
The transitive dependency EmpID -> DeptID -> DeptName violates 3NF. In 3NF, every non-prime attribute must depend directly on the primary key — not transitively through another non-prime attribute like DeptID.
5What is a "Trivial Functional Dependency"?
CorrectD: A dependency X -> Y where Y is a subset of X
A trivial FD X -> Y is one where Y is a subset of X (e.g., {A, B} -> A). It is called trivial because it is always true by definition and expresses no meaningful constraint — every set of attributes determines its own subsets.
IncorrectD: A dependency X -> Y where Y is a subset of X
A trivial FD X -> Y is one where Y is a subset of X (e.g., {A, B} -> A). It is called trivial because it is always true by definition and expresses no meaningful constraint — every set of attributes determines its own subsets.
6A table is structured as Courses (CourseCode, Instructor, TextBook). The primary key is (CourseCode, Instructor). An instructor teaches exactly one course, so Instructor -> CourseCode. The table is in 3NF. Does it satisfy BCNF?
CorrectA: No, because Instructor is a determinant but it is not a candidate key
Even though the table is in 3NF, it violates BCNF. BCNF requires every determinant to be a superkey. Here, Instructor -> CourseCode exists, but Instructor is not a superkey of the relation — it's only a prime attribute.
IncorrectA: No, because Instructor is a determinant but it is not a candidate key
Even though the table is in 3NF, it violates BCNF. BCNF requires every determinant to be a superkey. Here, Instructor -> CourseCode exists, but Instructor is not a superkey of the relation — it's only a prime attribute.
7When converting an unnormalized table to 1NF, what is the most robust method for handling a column that contains repeating groups (like multiple phone numbers)?
CorrectB: Create a new separate table for the repeating attribute and link it back to the original table using a foreign key
The correct approach to achieving 1NF with repeating groups is to extract the repeating attribute into a new child table, linked back to the parent via a foreign key. This avoids both the comma-separated anti-pattern and the multiple-column anti-pattern.
IncorrectB: Create a new separate table for the repeating attribute and link it back to the original table using a foreign key
The correct approach to achieving 1NF with repeating groups is to extract the repeating attribute into a new child table, linked back to the parent via a foreign key. This avoids both the comma-separated anti-pattern and the multiple-column anti-pattern.
8In relational algebra, Armstrong's Axiom of "Augmentation" states that if X -> Y is true, then which of the following is also true for any set of attributes Z?
CorrectC: XZ -> YZ
Armstrong's Augmentation Axiom: if X -> Y holds, then adding the same attribute set Z to both sides preserves the dependency: XZ -> YZ. This rule is used in closure and canonical cover algorithms.
IncorrectC: XZ -> YZ
Armstrong's Augmentation Axiom: if X -> Y holds, then adding the same attribute set Z to both sides preserves the dependency: XZ -> YZ. This rule is used in closure and canonical cover algorithms.
9Which of the following table designs explicitly prevents an Insertion Anomaly?
CorrectA: Designing the schema so that independent facts are stored in their own tables, independent of transactional data
Storing each independent fact in its own dedicated table (separation of concerns) prevents Insertion Anomalies. Transactional tables like Orders should not store master data like product or customer details.
IncorrectA: Designing the schema so that independent facts are stored in their own tables, independent of transactional data
Storing each independent fact in its own dedicated table (separation of concerns) prevents Insertion Anomalies. Transactional tables like Orders should not store master data like product or customer details.
10You have a table Patient (PatientID, DoctorID, DoctorPhone). Assuming DoctorID uniquely determines DoctorPhone. If a doctor currently has no patients, you cannot record their phone number. This is an example of what?
CorrectB: An Insertion Anomaly
Because DoctorPhone is stored only in the Patient table, a doctor with no current patients cannot be recorded at all — a classic Insertion Anomaly. The fix is a separate Doctors(DoctorID, DoctorPhone) table.
IncorrectB: An Insertion Anomaly
Because DoctorPhone is stored only in the Patient table, a doctor with no current patients cannot be recorded at all — a classic Insertion Anomaly. The fix is a separate Doctors(DoctorID, DoctorPhone) table.
11According to normalization theory, which of the following dependencies strictly violates 3NF but is completely acceptable in 2NF?
CorrectC: Zip_Code -> City_Name
Zip_Code -> City_Name is a transitive dependency: City_Name is determined by Zip_Code, not by the primary key. This is acceptable in 2NF (no partial key dependency), but violates 3NF (non-prime determines non-prime).
IncorrectC: Zip_Code -> City_Name
Zip_Code -> City_Name is a transitive dependency: City_Name is determined by Zip_Code, not by the primary key. This is acceptable in 2NF (no partial key dependency), but violates 3NF (non-prime determines non-prime).
12If X is a candidate key, and Y is a non-prime attribute, the dependency X -> Y is classified as:
CorrectD: A fully functional dependency
When the determinant X is a candidate key, the dependency X -> Y is called a fully functional dependency (or full functional dependency). The non-prime attribute Y is completely determined by the minimal unique identifier.
IncorrectD: A fully functional dependency
When the determinant X is a candidate key, the dependency X -> Y is called a fully functional dependency (or full functional dependency). The non-prime attribute Y is completely determined by the minimal unique identifier.
13A table Vehicle (VIN, EngineType, FuelType) has the primary key VIN. The dependency EngineType -> FuelType exists. To normalize to 3NF, what action must be taken?
CorrectA: Remove FuelType from the Vehicle table and place it in a new table keyed by EngineType
The dependency EngineType -> FuelType is a transitive dependency (non-prime determines non-prime through VIN -> EngineType -> FuelType). To reach 3NF, decompose into Vehicle(VIN, EngineType) and EngineSpecs(EngineType, FuelType).
IncorrectA: Remove FuelType from the Vehicle table and place it in a new table keyed by EngineType
The dependency EngineType -> FuelType is a transitive dependency (non-prime determines non-prime through VIN -> EngineType -> FuelType). To reach 3NF, decompose into Vehicle(VIN, EngineType) and EngineSpecs(EngineType, FuelType).
14When is a database schema considered to be completely "lossless" during decomposition?
CorrectB: When the original table can be perfectly reconstructed by performing a natural join on the decomposed tables
A lossless decomposition guarantees that no information is lost: performing a natural join on all decomposed tables perfectly reconstructs the original relation with no spurious (extra/false) tuples.
IncorrectB: When the original table can be perfectly reconstructed by performing a natural join on the decomposed tables
A lossless decomposition guarantees that no information is lost: performing a natural join on all decomposed tables perfectly reconstructs the original relation with no spurious (extra/false) tuples.
15What is the critical distinction between a Superkey and a Candidate Key?
CorrectD: A Superkey is any set of attributes that uniquely identifies a row; a Candidate Key is a minimal Superkey with no unnecessary attributes
A Superkey is any attribute set that uniquely identifies tuples — it may contain redundant attributes. A Candidate Key is a minimal superkey, meaning removing any attribute from it would break the uniqueness property.
IncorrectD: A Superkey is any set of attributes that uniquely identifies a row; a Candidate Key is a minimal Superkey with no unnecessary attributes
A Superkey is any attribute set that uniquely identifies tuples — it may contain redundant attributes. A Candidate Key is a minimal superkey, meaning removing any attribute from it would break the uniqueness property.
16Consider the relation R(A, B, C, D) with functional dependencies {A -> B, B -> C}. Which of the following is a valid closure of A (denoted as A+)?
CorrectC: {A, B, C}
Starting with A+: apply A -> B (add B), then B -> C (add C). Final closure A+ = {A, B, C}. D is not reachable from A given only {A -> B, B -> C}, so it is not in the closure.
IncorrectC: {A, B, C}
Starting with A+: apply A -> B (add B), then B -> C (add C). Final closure A+ = {A, B, C}. D is not reachable from A given only {A -> B, B -> C}, so it is not in the closure.
17In a perfectly normalized 3NF schema, where should derived or calculated attributes (like LineItemTotal from Quantity * UnitPrice) generally be stored?
CorrectA: They should not be stored physically in the database at all; they should be calculated on-the-fly during a query
Derived attributes (Quantity * UnitPrice = LineItemTotal) should not be stored physically in a normalized schema — they introduce redundancy and risk inconsistency if base values change. Compute them in SELECT queries or views instead.
IncorrectA: They should not be stored physically in the database at all; they should be calculated on-the-fly during a query
Derived attributes (Quantity * UnitPrice = LineItemTotal) should not be stored physically in a normalized schema — they introduce redundancy and risk inconsistency if base values change. Compute them in SELECT queries or views instead.
18A table containing AuthorID, BookID, and PublisherName is analyzed. The key is (AuthorID, BookID). A book is published by only one publisher, so BookID -> PublisherName. What is the highest normal form this table currently satisfies?
CorrectB: First Normal Form (1NF)
The dependency BookID -> PublisherName is a partial dependency (BookID is only part of the composite key {AuthorID, BookID}). The table is in 1NF (atomic values), but violates 2NF, so the highest form it satisfies is 1NF.
IncorrectB: First Normal Form (1NF)
The dependency BookID -> PublisherName is a partial dependency (BookID is only part of the composite key {AuthorID, BookID}). The table is in 1NF (atomic values), but violates 2NF, so the highest form it satisfies is 1NF.
19How does the concept of "Dependency Preservation" impact the decision to decompose a table into BCNF?
CorrectD: Decomposing to BCNF can sometimes fail to preserve all functional dependencies, forcing a designer to accept a 3NF design instead
BCNF decomposition can break functional dependencies that span across the resulting tables. When a dependency cannot be checked without a join, dependency preservation is sacrificed. In such cases, designers often settle for 3NF.
IncorrectD: Decomposing to BCNF can sometimes fail to preserve all functional dependencies, forcing a designer to accept a 3NF design instead
BCNF decomposition can break functional dependencies that span across the resulting tables. When a dependency cannot be checked without a join, dependency preservation is sacrificed. In such cases, designers often settle for 3NF.
20If a table possesses multiple overlapping candidate keys, which normal form is most likely to expose anomalies that previous normal forms missed?
CorrectC: Boyce-Codd Normal Form (BCNF)
When a table has multiple overlapping candidate keys, 3NF may still permit anomalies because it allows prime-attribute dependents of non-superkey determinants. BCNF removes this exception and catches these leftover anomalies.
IncorrectC: Boyce-Codd Normal Form (BCNF)
When a table has multiple overlapping candidate keys, 3NF may still permit anomalies because it allows prime-attribute dependents of non-superkey determinants. BCNF removes this exception and catches these leftover anomalies.
Database Normalization — Advanced
1Under standard definitions, which exact condition allows a functional dependency X -> Y to exist legally within Third Normal Form (3NF)?
CorrectB: X is a superkey, OR Y is a prime attribute
3NF allows a FD X -> Y if: (1) X is a superkey, OR (2) Y is a prime attribute (part of a candidate key). This second condition is the key difference between 3NF and the stricter BCNF.
IncorrectB: X is a superkey, OR Y is a prime attribute
3NF allows a FD X -> Y if: (1) X is a superkey, OR (2) Y is a prime attribute (part of a candidate key). This second condition is the key difference between 3NF and the stricter BCNF.
2Why does BCNF explicitly drop the "Y is a prime attribute" exception found in the 3NF definition?
CorrectA: Because allowing a non-superkey determinant to dictate a prime attribute can still lead to redundancy if candidate keys overlap
When candidate keys overlap, a non-superkey determinant can determine a prime attribute of a different candidate key. This still creates redundancy. BCNF eliminates this by requiring every determinant to be a superkey, period.
IncorrectA: Because allowing a non-superkey determinant to dictate a prime attribute can still lead to redundancy if candidate keys overlap
When candidate keys overlap, a non-superkey determinant can determine a prime attribute of a different candidate key. This still creates redundancy. BCNF eliminates this by requiring every determinant to be a superkey, period.
3Consider the relation R(X, Y, Z) with functional dependencies {XY -> Z, Z -> Y}. The candidate keys are XY and XZ. What normal form does this relation achieve, and what does it fail?
CorrectD: It achieves 3NF, but fails BCNF because Z is a determinant but not a superkey
The candidates keys are XY (from XY -> Z) and XZ (from Z -> Y, giving XZ -> XY -> Z). Every non-prime attribute dependency is on a superkey, satisfying 3NF. However, Z -> Y violates BCNF because Z is not a superkey.
IncorrectD: It achieves 3NF, but fails BCNF because Z is a determinant but not a superkey
The candidates keys are XY (from XY -> Z) and XZ (from Z -> Y, giving XZ -> XY -> Z). Every non-prime attribute dependency is on a superkey, satisfying 3NF. However, Z -> Y violates BCNF because Z is not a superkey.
4If we decompose the relation R(X, Y, Z) into R1(X, Z) and R2(Z, Y) to satisfy BCNF, what is the consequence?
CorrectC: The decomposition is lossless, but the original dependency XY -> Z is lost and cannot be enforced without a join
The join R1(X,Z) ⋈ R2(Z,Y) is lossless because Z (the shared attribute) is a key for R2. However, the original dependency XY -> Z is split across both tables and cannot be enforced without performing a join.
IncorrectC: The decomposition is lossless, but the original dependency XY -> Z is lost and cannot be enforced without a join
The join R1(X,Z) ⋈ R2(Z,Y) is lossless because Z (the shared attribute) is a key for R2. However, the original dependency XY -> Z is split across both tables and cannot be enforced without performing a join.
5According to Armstrong's Axioms, the rule of Transitivity states that if X -> Y and Y -> Z, then X -> Z. Which of the following is the "Pseudotransitivity" rule?
CorrectB: If X -> Y and WY -> Z, then WX -> Z
Pseudotransitivity (derived from Armstrong's Axioms): if X -> Y and WY -> Z, then we can augment the first to get WX -> WY, then apply transitivity to get WX -> Z. This is a derived rule used in closure computations.
IncorrectB: If X -> Y and WY -> Z, then WX -> Z
Pseudotransitivity (derived from Armstrong's Axioms): if X -> Y and WY -> Z, then we can augment the first to get WX -> WY, then apply transitivity to get WX -> Z. This is a derived rule used in closure computations.
6Which normalization concept directly addresses the problem where a single record contains multiple independent, multi-valued attributes, leading to a combinatorial explosion of rows?
CorrectA: Fourth Normal Form (4NF)
4NF addresses multi-valued dependencies (MVDs): when one attribute independently determines multiple values of two other attributes, rows multiply combinatorially. 4NF requires that for every non-trivial MVD X ->> Y, X must be a superkey.
IncorrectA: Fourth Normal Form (4NF)
4NF addresses multi-valued dependencies (MVDs): when one attribute independently determines multiple values of two other attributes, rows multiply combinatorially. 4NF requires that for every non-trivial MVD X ->> Y, X must be a superkey.
7In the context of database decomposition algorithms, what constitutes a "Lossless-Join Decomposition" for relation R decomposing into R1 and R2?
CorrectD: The natural join of R1 and R2 exactly reconstructs the original relation R without generating any spurious tuples
A Lossless-Join Decomposition guarantees that the natural join of all decomposed relations exactly reproduces the original relation — no rows are added (spurious tuples) and no rows are missing.
IncorrectD: The natural join of R1 and R2 exactly reconstructs the original relation R without generating any spurious tuples
A Lossless-Join Decomposition guarantees that the natural join of all decomposed relations exactly reproduces the original relation — no rows are added (spurious tuples) and no rows are missing.
8For a decomposition into R1 and R2 to be mathematically guaranteed as lossless, the intersection of their attributes (R1 intersect R2) must structurally act as what?
CorrectC: A superkey for either R1 or R2
The Heath theorem guarantees a lossless decomposition of R into R1 and R2 if and only if the attributes common to both (R1 ∩ R2) form a superkey of at least one of the two resulting relations.
IncorrectC: A superkey for either R1 or R2
The Heath theorem guarantees a lossless decomposition of R into R1 and R2 if and only if the attributes common to both (R1 ∩ R2) form a superkey of at least one of the two resulting relations.
9When performing a "Closure of a Set of Attributes" algorithm to determine candidate keys, if the closure of attribute set A contains every attribute in the schema, what can be definitively stated about A?
CorrectB: A is a superkey for the schema
If A+ (the closure of attribute set A under all functional dependencies) equals the full set of attributes in the schema, then A functionally determines every attribute — meaning A is a superkey for the schema.
IncorrectB: A is a superkey for the schema
If A+ (the closure of attribute set A under all functional dependencies) equals the full set of attributes in the schema, then A functionally determines every attribute — meaning A is a superkey for the schema.
10Given a relation R(A, B, C, D, E) and functional dependencies {A -> BC, C -> D, B -> E}, what is the minimal candidate key?
CorrectA: {A}
Computing A+: A -> BC (add B, C), then C -> D (add D), then B -> E (add E). A+ = {A, B, C, D, E} = all attributes. Since A alone determines everything, {A} is the minimal candidate key.
IncorrectA: {A}
Computing A+: A -> BC (add B, C), then C -> D (add D), then B -> E (add E). A+ = {A, B, C, D, E} = all attributes. Since A alone determines everything, {A} is the minimal candidate key.
11In heavily normalized databases (3NF/BCNF), what is the most significant performance drawback encountered by applications running complex analytical queries?
CorrectD: High CPU and I/O overhead due to the necessity of executing massive, multi-table SQL JOIN operations to reconstruct data
In a fully normalized schema, data about a single business entity is spread across many tables. Analytical queries must JOIN all these tables to reconstruct the full picture, consuming significant CPU and I/O resources.
IncorrectD: High CPU and I/O overhead due to the necessity of executing massive, multi-table SQL JOIN operations to reconstruct data
In a fully normalized schema, data about a single business entity is spread across many tables. Analytical queries must JOIN all these tables to reconstruct the full picture, consuming significant CPU and I/O resources.
12Which architectural pattern deliberately violates 3NF to optimize for heavy, read-centric analytical workloads, often organizing data into central Fact tables and surrounding Dimension tables?
CorrectC: Star Schema (Data Warehousing)
The Star Schema (used in data warehouses) places a central denormalized Fact table surrounded by Dimension tables. This intentionally violates 3NF to eliminate JOINs and maximize read performance for analytical (OLAP) workloads.
IncorrectC: Star Schema (Data Warehousing)
The Star Schema (used in data warehouses) places a central denormalized Fact table surrounded by Dimension tables. This intentionally violates 3NF to eliminate JOINs and maximize read performance for analytical (OLAP) workloads.
13A relation R contains attributes (A, B, C, D, E). The primary key is (A, B). Dependencies exist: A -> C, B -> D, and (A, B) -> E. To decompose this strictly into 2NF, what is the correct optimal set of relations?
CorrectA: R1(A, B, E), R2(A, C), R3(B, D)
A -> C (partial dependency on A alone) and B -> D (partial dependency on B alone) both violate 2NF. Removing them gives: R1(A, B, E) for the full-key dependency, R2(A, C) for A's dependency, and R3(B, D) for B's dependency.
IncorrectA: R1(A, B, E), R2(A, C), R3(B, D)
A -> C (partial dependency on A alone) and B -> D (partial dependency on B alone) both violate 2NF. Removing them gives: R1(A, B, E) for the full-key dependency, R2(A, C) for A's dependency, and R3(B, D) for B's dependency.
14Consider a table Consultant (ConsultantID, ClientID, ContractType). Assume ConsultantID -> ContractType, and (ClientID, ContractType) -> ConsultantID. What is the BCNF status?
CorrectD: It fails BCNF because ContractType is partially determined by ConsultantID, which is a determinant but not a candidate key
Both ConsultantID and (ClientID, ContractType) are candidate keys, so all attributes are prime. The table is in 3NF. However, ConsultantID -> ContractType exists and ConsultantID is not a superkey, violating BCNF.
IncorrectD: It fails BCNF because ContractType is partially determined by ConsultantID, which is a determinant but not a candidate key
Both ConsultantID and (ClientID, ContractType) are candidate keys, so all attributes are prime. The table is in 3NF. However, ConsultantID -> ContractType exists and ConsultantID is not a superkey, violating BCNF.
15In enterprise database tuning, what is a "Materialized View"?
CorrectB: A pre-computed, physically stored query result that acts as a performance-enhancing denormalization technique
A Materialized View physically stores the precomputed result of a query on disk. Unlike a standard view (computed at query time), a materialized view trades storage space for dramatically faster read performance — a controlled denormalization.
IncorrectB: A pre-computed, physically stored query result that acts as a performance-enhancing denormalization technique
A Materialized View physically stores the precomputed result of a query on disk. Unlike a standard view (computed at query time), a materialized view trades storage space for dramatically faster read performance — a controlled denormalization.
16Why is Fourth Normal Form (4NF) rarely required in standard OLTP (Online Transaction Processing) database designs?
CorrectC: Multi-valued dependencies are usually resolved naturally during the ER modeling phase by establishing distinct many-to-many relationship tables prior to formal normalization
ER modeling naturally separates independent many-to-many relationships into their own link tables, which resolves multi-valued dependencies before the formal normalization process even begins. This makes explicit 4NF decomposition unnecessary in most OLTP designs.
IncorrectC: Multi-valued dependencies are usually resolved naturally during the ER modeling phase by establishing distinct many-to-many relationship tables prior to formal normalization
ER modeling naturally separates independent many-to-many relationships into their own link tables, which resolves multi-valued dependencies before the formal normalization process even begins. This makes explicit 4NF decomposition unnecessary in most OLTP designs.
17You have decomposed a schema into 3NF. You verify that joining the resulting tables perfectly reconstructs the original data. However, you notice that verifying a specific functional dependency now requires joining two tables. What trade-off have you made?
CorrectB: You achieved Lossless-Join decomposition but sacrificed Dependency Preservation
If the natural join of the decomposed tables perfectly reconstructs the original data, the decomposition is lossless. If a functional dependency can no longer be verified without joining tables, dependency preservation has been sacrificed.
IncorrectB: You achieved Lossless-Join decomposition but sacrificed Dependency Preservation
If the natural join of the decomposed tables perfectly reconstructs the original data, the decomposition is lossless. If a functional dependency can no longer be verified without joining tables, dependency preservation has been sacrificed.
18Which set of rules forms the mathematical foundation for algorithmically computing the minimal cover of a set of functional dependencies?
CorrectD: Armstrong's Axioms
Armstrong's Axioms (Reflexivity, Augmentation, Transitivity) are a sound and complete set of inference rules for functional dependencies. All derived rules (Union, Decomposition, Pseudotransitivity) follow from these three axioms.
IncorrectD: Armstrong's Axioms
Armstrong's Axioms (Reflexivity, Augmentation, Transitivity) are a sound and complete set of inference rules for functional dependencies. All derived rules (Union, Decomposition, Pseudotransitivity) follow from these three axioms.
19What does computing the "Minimal Cover" (or Canonical Cover) of a functional dependency set achieve?
CorrectA: It eliminates redundant and extraneous functional dependencies, producing the simplest equivalent set of rules
The Minimal Cover (Canonical Cover) is the smallest equivalent set of FDs: all right-hand sides are single attributes, all left-hand sides are minimal (no extraneous attributes), and no FD in the set is redundant.
IncorrectA: It eliminates redundant and extraneous functional dependencies, producing the simplest equivalent set of rules
The Minimal Cover (Canonical Cover) is the smallest equivalent set of FDs: all right-hand sides are single attributes, all left-hand sides are minimal (no extraneous attributes), and no FD in the set is redundant.
20If a relational schema is currently in 3NF, but an insertion anomaly is still mathematically possible under highly specific, rare conditions, what structural characteristic must the schema possess?
CorrectC: Two or more overlapping composite candidate keys
When a 3NF schema has two or more overlapping composite candidate keys, certain edge-case insertion anomalies can still occur — precisely the scenario that motivates BCNF. BCNF eliminates these residual anomalies by requiring every determinant to be a superkey.
IncorrectC: Two or more overlapping composite candidate keys
When a 3NF schema has two or more overlapping composite candidate keys, certain edge-case insertion anomalies can still occur — precisely the scenario that motivates BCNF. BCNF eliminates these residual anomalies by requiring every determinant to be a superkey.
Conclusion: Mastering Database Normalization
These 60 MCQs cover the complete normalization spectrum — from spotting a 1NF violation caused by a comma-separated column, to proving why a 3NF table still fails BCNF due to overlapping candidate keys, to understanding why BCNF decompositions sometimes sacrifice dependency preservation.
The key mental model: 1NF = atomic values; 2NF = no partial dependencies; 3NF = no transitive dependencies; BCNF = every determinant is a superkey. Each level builds on the previous and removes one more class of anomaly.
After completing this set, deepen your knowledge with the full Database Normalization theory notes and explore how normalization principles apply in real DBMS design projects.
📌 Key Takeaways — Database Normalization
- 1NF: All attribute values must be atomic. Repeating groups or multi-valued attributes are strictly forbidden.
- 2NF: Must be in 1NF + no partial dependencies. Non-prime attributes must depend fully on the primary key (applies only to composite keys).
- 3NF: Must be in 2NF + no transitive dependencies. Non-prime attributes cannot determine other non-prime attributes.
- BCNF: Stricter than 3NF. For every non-trivial functional dependency X → Y, X must be a superkey. Resolves overlapping candidate key anomalies.
- 4NF: Eliminates multi-valued dependencies (MVDs) which cause combinatorial row expansion.
- Anomalies: Insertion, Update, and Deletion anomalies are side effects of data redundancy.
- Lossless Join: Decomposition is lossless if joining the split relations perfectly reconstructs the original relation (R1 ∩ R2 is a superkey of R1 or R2).
- Dependency Preservation: All functional dependencies should be enforceable within individual relations without joining them.
- Minimal Cover: The absolute smallest equivalent set of functional dependencies with all redundant/extraneous elements removed.
- Denormalization: Intentionally introducing redundancy (like Star Schema) to optimize read speeds in reporting/OLAP environments.
Quick Review & Summary
| Normal Form / Concept | Key Requirement | Anomaly Eliminated / Goal |
|---|---|---|
| 1NF | All attribute values must be atomic | Repeating groups / non-atomic data |
| 2NF | 1NF + no partial dependencies | Partial dependency redundancy |
| 3NF | 2NF + no transitive dependencies | Transitive dependency redundancy |
| BCNF | Every determinant is a superkey | Overlapping candidate key anomalies |
| 4NF | BCNF + no non-trivial multi-valued dependencies | Combinatorial row explosion from MVDs |
| Insertion Anomaly | Cannot add fact without unrelated data | Resolved by 2NF / 3NF decomposition |
| Update Anomaly | Same fact stored in multiple rows | Resolved by removing redundancy |
| Deletion Anomaly | Deleting one fact destroys another | Resolved by separating independent facts |
| Lossless Join | R1 ∩ R2 is superkey of R1 or R2 | Guarantees original data is reconstructable |
| Minimal Cover | Smallest equivalent FD set | Removes redundant/extraneous dependencies |
Frequently Asked Questions
Q. How many Database Normalization MCQs are available on this page?
Q. What specific topics do these Database Normalization MCQs cover?
Q. Are these MCQs suitable for GATE (CS) and university DBMS exam preparation?
Q. What is the key difference between 3NF and BCNF?
Q. Why is BCNF decomposition not always preferred over 3NF?
Q. What is the difference between Study Mode and Exam Mode?
Q. How do I identify which normal form a table violates in an exam scenario?
Struggling with some questions? Re-read the full Theory Guide: Database Normalization