Type of Normal Forms MCQ Quiz - Objective Question with Answer for Type of Normal Forms - Download Free PDF

The correct answer is Option 1.

Key Points

• Given a relation R with attributes {A, B, C} and a functional dependency set S = {A → B, A → C}, we can decompose the relation R into two relations.
• Option 1 suggests decomposing R into R1{A, B} and R2{A, C}.
• To verify the correctness of the decomposition:
  • We need to check if both decomposed relations are in BCNF (Boyce-Codd Normal Form) or at least 3NF (Third Normal Form).
  • In R1{A, B}, the functional dependency A → B holds, and A is a candidate key for R1, satisfying BCNF.
  • In R2{A, C}, the functional dependency A → C holds, and A is a candidate key for R2, satisfying BCNF.
• Thus, the decomposition is lossless and dependency preserving, making Option 1 the correct choice.

Additional Information

• Decomposition is a process of breaking down a relation into smaller relations to achieve normalization and eliminate redundancy.
• BCNF is a stricter version of 3NF where every determinant is a candidate key.
• Ensuring that decomposed relations are in BCNF helps maintain data integrity and avoid anomalies during database operations.

The correct answer is: option 3: D only

Concept & Statement Analysis:

Statement A: ✅ True
An all-key relation means every attribute is part of the candidate key. Hence, no non-trivial Functional Dependencies (FDs) exist. Since BCNF only cares about non-trivial FDs and their compliance with superkeys, the relation is automatically in BCNF.

Statement B: ✅ True
If a relation is not in 4NF due to the presence of a non-trivial Multivalued Dependency (MVD) that violates 4NF, it must be decomposed to eliminate redundancy and achieve 4NF compliance.

Statement C: ✅ True
Yes, the decomposition in 4NF removes redundancy that arises due to MVDs. That's the goal of 4NF: to eliminate non-trivial MVDs that are not supported by superkeys.

Statement D: ❌ False
This is incorrect. In fact, BCNF is stronger than 3NF. Every relation in BCNF is in 3NF, but not every 3NF relation is in BCNF. 3NF allows some redundancy in special cases where BCNF does not.

Explanation of options:

• Option 1 – A only: ❌ A is true.
• Option 2 – A, B only: ❌ A and B are true.
• Option 3 – D only: ✅ Correct. Only D is false.
• Option 4 – C only: ❌ C is true.

Hence, the correct answer is: option 3: D only

The correct answer is A, B, C only.

Key Points

• The INSERT command in SQL is used to add data to tables within a database.
• Specifically, the INSERT command can perform the following actions:
  • Add a single tuple to a relation (or table): This allows the addition of a single row of data to the table.
  • Add multiple tuples to a relation: This allows the addition of multiple rows of data to the table in one command.
  • Add values to specific attributes: This allows specifying particular columns in the table to insert data into, rather than all columns.
• The INSERT command does not create new tables; it only inserts data into existing tables.

Additional Information

• The basic syntax for the INSERT command is:

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• For adding multiple rows at once, the syntax is:

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value3, value4, ...), ...;

• When inserting values into specific columns, the columns must be listed in the order corresponding to the values provided.
• The INSERT command ensures data integrity and consistency within relational databases by enforcing constraints and data types defined for the table columns.

The correct answer is: Option 3

Concept:

Boyce-Codd Normal Form (BCNF) is a higher version of the Third Normal Form (3NF). A relation is in BCNF if for every non-trivial functional dependency X → Y, X is a super key.

While BCNF decomposition always guarantees a lossless join, it does not always guarantee dependency preservation.

Key Points

Option 1: R(V, W, X, Y, Z) with FDs:

• VW → X
• WX → V
• VX → W
• W → Y
• Y → Z

Observation: This is a known example from textbooks like Navathe and Korth that looks complex, but it can be decomposed into BCNF with both lossless join and dependency preservation.

Why it is NOT the correct answer: Despite multiple dependencies, by carefully selecting decompositions (e.g., starting from W → Y), we can achieve both BCNF and preserve dependencies.

Option 2: R(V, W, X, Y) with FDs:

• VW → W (trivial)
• X → Y

Observation: The FD VW → W is trivial and doesn't affect BCNF. The FD X → Y violates BCNF if X is not a superkey.

Decomposition:

• Decompose on X → Y → R1(X, Y) and R2(V, W, X)

This decomposition is:

• Lossless: Yes (X is common and a key in R1)
• Dependency preserving: Yes (both X → Y and VW → W are preserved)

Why it is NOT the correct answer: It can be decomposed into BCNF with both lossless join and dependency preservation.

Option 3: Correct Answer
R(V, W, X, Y) with FDs:

• VW → X
• X → VY

Candidate Key: VW (since VW⁺ = V, W, X → V, W, X, Y = all attributes)

Violation: FD X → VY is a violation of BCNF as X is not a superkey

Attempted BCNF Decomposition:

• Decompose on X → VY
• R1(X, V, Y), R2(X, W)

Check:

• Lossless join: Yes (X is common and a key in R1)
• Dependency preservation: ❌ No (FD VW → X is lost; we can’t enforce it without recombining the relations)

Why this IS the correct answer: This relation cannot be decomposed into BCNF while preserving both lossless join and dependencies.

Option 4: R(V, W, X, Y, Z) with FDs:

• VW → X
• X → Y
• Y → Z
• Z → V

Observation: This is a cyclic chain of dependencies. These types of chains can be decomposed incrementally in BCNF.

Possible decomposition:

• Start with Z → V → R1(Z, V)
• Then Y → Z → R2(Y, Z)
• Then X → Y → R3(X, Y)
• Then VW → X → R4(V, W, X)

Lossless Join: ✅

Dependency Preservation: ✅ All dependencies are represented in individual decomposed relations

Why it is NOT the correct answer: It can be decomposed into BCNF with both properties preserved.

Final Answer:

Option 3 is the correct answer because it cannot be decomposed into BCNF with both lossless join and dependency preservation.

Concept:-

BCNF (Boyce Codd Normal Form) is the advanced version of 3NF. A table is in BCNF if every functional dependency X→Y, X is the super key of the table. If R is found to be in BCNF, it can be safely deduced that the relation is also in 3NF, 2NF, and 1NF as the hierarchy shows.

 Important Points

• BCNF is the normal form that actually removes all transitive dependencies. 
• It is a more restricted form of normalization so that the database does not end in anomalies.
• The stage at which a table is organized is known as its normal form (or a stage of normalization).

Additional InformationNormalization:- It is the process of organizing data in a database. There are two main objectives of the normalization process: eliminate redundant data and ensure data dependencies make sense.

1NF:- First normal form (1NF) is a relation in which the intersection of each row and column contains one and only one value.

2NF:- Second Normal Form (2NF) is based on the concept of fully functional dependency.

3NF:- A relation is in the third normal form (3NF) if it is in the second normal form and no transitive dependencies exist.

4NF:- A group of tables that satisfies the first, second, and third normal forms are sufficiently well-designed.

Answer: Option 3

CONCEPT:

3NF:

A relation is in 3NF if there is no transitive dependency for non-prime attributes as well as it is in second normal form.Transitive dependency occurs when FD (non-key-> non-key) exists in relation.

BCNF:

It stands for Boyce Codd's normal form.

A relation R is in BCNF if whenever a non-trivial functional dependency X -> A holds in R, then X is a superkey of R. Any relation with two attributes is always in BCNF. Because when a relation contains only two attributes then one attribute determines another and the left side of the functional dependency will always be a candidate key in that case. BCNF is not always dependency preserving.

EXPLANATION: 

This table indicates the following functional dependency

AB → CDE

C → B

(AB)⁺ = {A, B, C, D, E}

(AC)+ = {A, C, B, D, E}

Prime attributes: A, B,C

Keys: AB and AC

Since AB and AC are the keys; but in the second functional dependency, C is not key.

Hence the table is in 3rd normal form but not in BCNF.

Additional Information

1NF

It does not contain any composite or multi-valued attribute.

2NF

A relation is in 2NF if it has No Partial Dependency i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table

Concept:

2NF: There should not be any partial dependency in the relation.

3NF: There should not be any transitive dependency and right side of functional dependency is a prime attribute

BCNF: Left side of functional dependency is a key

If a functional dependency is in BCNF, then it will also be in lower normal forms (1 NF, 2NF, 3 NF). Also, if any one functional dependency is in the weaker normal form, then relation will be in weaker normal form

Explanation:

Functional dependencies are:

a → c

b → d

Candidate key for the relation R is  {ab}

Because (ab)+ = {a, b, c, d}

1) a → c

This is not in BCNF, because left side is not the candidate key. Also, not in 3NF because right side is not the prime attribute. It is in not in 2NF, because there exists partial dependency in this functional dependency, as a non-prime attribute is dependent on prime attribute. It is in 1NF.

2) b → d

Similar case as that of 1^st functional dependency. It is in 1NF

Concept:-

BCNF (Boyce Codd Normal Form) is the advanced version of 3NF. A table is in BCNF if every functional dependency X→Y, X is the super key of the table. If R is found to be in BCNF, it can be safely deduced that the relation is also in 3NF, 2NF, and 1NF as the hierarchy shows.

 Important Points

• BCNF is the normal form that actually removes all transitive dependencies. 
• It is a more restricted form of normalization so that the database does not end in anomalies.
• The stage at which a table is organized is known as its normal form (or a stage of normalization).

Additional InformationNormalization:- It is the process of organizing data in a database. There are two main objectives of the normalization process: eliminate redundant data and ensure data dependencies make sense.

1NF:- First normal form (1NF) is a relation in which the intersection of each row and column contains one and only one value.

2NF:- Second Normal Form (2NF) is based on the concept of fully functional dependency.

3NF:- A relation is in the third normal form (3NF) if it is in the second normal form and no transitive dependencies exist.

4NF:- A group of tables that satisfies the first, second, and third normal forms are sufficiently well-designed.

First relational schema:

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)

Primary key: (VOLUME, NUMBER, STARTPAGE, ENDPAGE)

Functional dependencies are:

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE   

(This dependency is in BCNF, satisfy form X → A, where X is the candidate key)

(VOLUME, NUMBER) → YEAR

(This dependency is not in 2NF, as there is partial dependency in this)

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE       (BCNF form).

First relational schema is in 1NF.

Second relational schema:

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)      - 1^st

(VOLUME, NUMBER, YEAR)   - 2^nd

1^st satisfy

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE     /BCNF form

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE      / BCNF form

2^nd satisfy

(VOLUME, NUMBER) → YEAR               / According to this, satisfy 2 NF form.

Weakest normal form that new database satisfies but old one doesn’t is 2NF.

The correct answer is option 3.

Concept:

1 Normal forms, 2 Normal forms, 3 Normal forms, and Boyce Codd Normal Forms are based on the functional dependencies among the attributes of a relation.

First Normal forms:

• There should be just one value in each table cell.
• Every record must be distinct from the others.

Second Normal Forms:

• It must be in 1NF.
• Single Column Primary Key that does not functionally dependant on any subset of candidate key relation. It means it allows only fully functional dependency.

Third Normal Forms:

• It must be in 2NF.
• There are no transitive functional dependencies.

Boyce Codd normal form:

• It must be in 3NF.
• A table is in BCNF if every functional dependency X → Y, X is the super key of the table.

Fourth normal form:

• It should be in the Boyce-Codd Normal Form.
• The table should not have any Multi-valued Dependency.

Here 4 NF is not based on the functional dependencies among the attributes of a relation.

Hence the correct answer is A, B, and C.

Concept:

A relation is in 1NF if every values in the relation are atomic.

A relation R with nontrivial functional dependency X → A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key is in called to be in 2NF.

A relation R with nontrivial functional dependency X → A, where X is not a superkey and A is a prime attribute, is called to be in 3NF.

A relation R with nontrivial functional dependency X → A, where X is a superkey is called to be in BCNF.

Explanation:

Statement I:

corresponds to a relation that is in 3NF but not in BCNF, because for BCNF, X must be a superkey.

Statement II

corresponds to a relation that is only in 2NF by definition.

Statement III

corresponds to a relation that is not even in 2NF. It is in 1NF.

Concept –

For a relation to be in 

1NF – All the values of attributes should be atomic

2NF – (Proper Subset of candidate key -> Non-key) not allowed 

3NF – (Non-key -> Non-key) derivation not allowed

In the given question, it is not mentioned that the functional dependency of non-attribute is partial or full. In 2NF, both types of dependencies are allowed.

1. (Candidate key -> Non-key) – Full dependency

2. (Proper Subset of candidate key -> Non-key) – Partial dependency

However, partial dependency is strictly prohibited in 3NF. Hence, 2NF will be highest Normal Form assumed by the given statement.

Example:

Let R(ABC) = { A->B,B->C,A->C)  

A is Primary key and B,C are non-key attributes.

Also, B and C are  functionally dependant on A.  (A->B, A->C)

Also,  B->C (non-key -> non-key) and hence it is not in 3NF but in 2 NF

The correct answer is A, C and D only

Key Points

• A. A database design is in BCNF if each member of the set of relation schemas that constitutes the design is in BCNF.
  • This is true. BCNF (Boyce-Codd Normal Form) applies at the relation schema level (a set of attributes); if all relation schemas are in BCNF, the database design can be said to be in BCNF.
• B. A BCNF schema can have transitive dependency.
  • This is false. The definition of BCNF is that for every dependency X -> Y in a relation, X must be a superkey. This disallows transitive dependencies, which would imply that there's a non-superkey attribute that determines another attribute.
• C. It is always possible to obtain a 3NF design without sacrificing a lossless join.
  • This is true. The process of normalizing a database schema to 3NF can be done in such a way that it does not lose the ability to perform a lossless join. This is ensured by taking care that when a functional dependency X -> Y causes a violation, the decomposition of the relation includes X in all decomposed relations.
• D. There are multivalued dependencies in 4NF.
  • This is True. 4NF (Fourth Normal Form) is specifically designed to handled multivalued dependencies. A multivalued dependency always requires at least three attributes because it consists of at least two attributes that are dependent on a third.

Based on these explanations, we can see that only statements A, C and D are true. Therefore, the correct option using the provided choices is: A, C, and D only

Schema I
Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for.
Non-trivial functional dependency
rollno → courses
Since rollno is the primary key, so this relation is in BCNF as well as 3 NF.

Schema II
Non-trivial functional dependencies:
rollno, courseid → email
email → rollno
Since, {rollno, coursid} is the primary key so rollno and courseid are prime attributes. email is a non-prime attribute.

Functional dependency (FD) rollno, courseid → email is in BCNF and 3NF, but FD email → rollno violates the rule of BCNF because email is not superkey.

But it satisfies the rule of 3 NF because rollno is prime-attribute.
So, overall this relation is in 3 NF but not in BCNF.

Schema III
Non-trivial functional dependencies:
rollno, courseid, → marks, grade
marks → grade
Since rollno, courseid is the primary key, so rollno and courseid are prime attributes, and marks and grade are non-prime attributes.
FD rollno, courseid, → marks, grade satisfies BCNF as well as 3 NF.
FD marks → grade does not satisfy 3 NF because neither marks are superkey nor grade is prime-attribute. So, also can not be in BCNF.
So, overall this relation is not in 3 NF and not in BCNF but it does not violate the rule of 2 NF, so can be only in 2 NF.

Schema IV
Non-trivial functional dependencies:
rollno, courseid → credit
courseid → credit
Since, rollno, courseid is the primary key, so rollno and courseid are prime-attributes and credit is a non-prime attribute.
FD rollno, courseid → credit satisfies BCNF as well as 3 NF.
FD courseid → credit violates the rule of 2 NF, so can not be in 2NF.
So, overall this is not in 2 NF, 3 NF, and BCNF. But it is only in 1 NF.

Therefore only schema-II is in 3 NF but not in BCNF.

The correct answer is A, B, C only.

Key Points

• The INSERT command in SQL is used to add data to tables within a database.
• Specifically, the INSERT command can perform the following actions:
  • Add a single tuple to a relation (or table): This allows the addition of a single row of data to the table.
  • Add multiple tuples to a relation: This allows the addition of multiple rows of data to the table in one command.
  • Add values to specific attributes: This allows specifying particular columns in the table to insert data into, rather than all columns.
• The INSERT command does not create new tables; it only inserts data into existing tables.

Additional Information

• The basic syntax for the INSERT command is:

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• For adding multiple rows at once, the syntax is:

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value3, value4, ...), ...;

• When inserting values into specific columns, the columns must be listed in the order corresponding to the values provided.
• The INSERT command ensures data integrity and consistency within relational databases by enforcing constraints and data types defined for the table columns.