Normal Form MCQ Quiz - Objective Question with Answer for Normal Form - 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 X → Y 

Explanation:

• In database theory, functional dependency X → Y means that for every value of X , there is exactly one corresponding value of Y .

Given Scenario:

• Every time attribute X appears:
  • The same value of Y is matched.
  • A different value of Z is matched.

Analysis:

• X → Y : This is true because the problem explicitly states that for every X , the same value of Y is matched. This satisfies the definition of functional dependency X → Y .
• X → Z : This is false because Z can have multiple different values for the same X , violating the definition of functional dependency.
• X → (Y, Z) : This is false because while Y is uniquely determined by X , Z is not. Therefore, X does not determine the pair (Y, Z) .
• (Y, Z) → X : This is false because the question does not provide any evidence or context indicating that (Y, Z) determines X .

The functional dependency described is X → Y , making Option 1 the correct choice.

The correct answer is Second Normal Form (2NF).

Key Points

• Second Normal Form (2NF) ensures that every non-prime attribute is fully functionally dependent on the primary key.
  • A table is in 2NF if it is in First Normal Form (1NF) and all non-prime attributes are fully functionally dependent on the entire primary key.
  • In 1NF, the table has a primary key and there are no repeating groups or arrays.
  • 2NF removes partial dependency, which means non-prime attributes are dependent on the whole primary key, not just part of it.
  • This normal form is specifically important for tables with composite primary keys.
  • If a table has a single attribute as the primary key, it is automatically in 2NF if it is in 1NF.

Additional Information

• Ensuring a table is in 2NF helps to reduce redundancy and improve data integrity.
• Second Normal Form deals with the concept of full functional dependency, which is essential for maintaining the consistency of data.
• Achieving 2NF may require decomposing a table into multiple tables to eliminate partial dependencies.
• Higher normal forms, such as Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF), build upon the principles of 2NF to further reduce redundancy and anomalies.

The correct answer is option 3.

Concept:

Normalization:

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.

1NF (First Normal Form):

• Each table cell should contain a single value.
• Each record needs to be unique.

2NF (Second Normal Form):

• It should be in 1NF.
• Single Column Primary Key that does not functionally dependant on any subset of candidate key relation.

3NF (Third Normal Form):

• It should be in 2NF.
• It has no transitive functional dependencies.

Boyce-Codd Normal Form (BCNF):

• A relation R is in BCNF if R is in Third Normal Form and for every FD, LHS is super key.
• A relation is in BCNF iff in every non-trivial functional dependency X –> Y, X is a super key.

The given relation schema:

Singer(singerName, songName).

• Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF. If a relation is BCNF then it should be in 3NF, 2NF, 1NF.

Hence Singer(singerName, songName) is Boyce-Codd Normal Form (BCNF).

Hence the correct answer is BCNF.

Answer: Option 4

Concept:

Lossless Decomposition:

for a Decomposition of two Relation, R₁ and R₂ to be lossless 2 condition needs to be satisfied that is

1. R1 ∩ R2 → R1 or R₂ i.e. common attributes must be key to either of the relation.

2. attributes of R1 ∪ attributes of R2 ≡ attributes of R

Explanation:

D1 : R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)]

lets first take 2 relations R₁(P, Q, S, T )  R₂(P, T, X) 

common attributes are PT and PT → TX ( according to augmentation property )

so relation becomes R₁(P, Q, S, T, X) R₂(Q, Y) 

The common attribute is Q and Q→ Y is key to R₂ Hence (P, Q, S, T, X, Y)

 So now relation becomes R1(P, Q, S, T, X, Y) R2(Y, Z, W)

 The common attribute is Y and Y is key to R₂.

Hence all attributes get combined into one relation and hence this Decomposition is lossless.

D2 : R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)]

If you observe relation (T, X); Its attributes not common to any other relations.

even if we combined all other attributes R₁(P, Q, S, Y, Z, W)  R₂(T, X) 

still no common attributes Hence this decomposition is lossy. 

1 NF

A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.

2 NF

A relation R is in second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key.

Example: A → B, B → C , A → C here A is key and relation is in  2NF but A → B, B → C , A → C is transitive

3 NF

A relation R is in third normal form (3NF) if and only if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key. Hence Option 3 is the correct answer.

BCNF

A relation R is in Boyce-Codd normal form (BCNF) if and only if every determinant is a candidate key.

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

So, given relation R (a, b, c, d) is in 1NF.

Normalization is used for Eliminating redundant(useless) data and Ensuring data dependencies.

First Normal Form (1NF)

• It should only have a single value for attribute and values stored in it should be of the same domain
• All the columns in a table should have unique names.

Second Normal Form (2NF)

• It should be in the First Normal form.
• It should not have Partial Dependency.

Third Normal Form (3NF)

• It is in the Second Normal form.
• it doesn't have Transitive Dependency.

Boyce and Codd Normal Form (BCNF)

BCNF is an Advance version of the 3NF

• The relation must be in 3rd Normal Form
• and, for each functional dependency ( X → Y ), X should be a super Key.

Fourth Normal Form (4NF)

• It is in the Boyce-Codd Normal Form.
• it doesn't have Multi-Valued Dependency.

Statement 1: TRUE

BCNF (Boyce Codd Normal Form):

• A relation R is in BCNF whenever a non – trivial functional dependency X → A holds in R, where X is the super-key of R.
• A binary relation is always in BCNF. A binary relation contains only two attributes.
• Functional dependency that is possible from a binary relation is one.

Example:

Consider R(A, B), in this only one functional dependency is possible either A → B or B → A

In both the cases, left hand side will be the super key. In this way R(A, B) is always in BCNF.

If a relation is in BCNF then it is in 1NF, 2 NF and 3 NF

Statement 2: FALSE

Set 1 = {AB → C, D → E, AB → E, E→ C}

Set 2 = {AB → C, D → E, E → C}

Set 2 cannot derive   AB → E since in set 2 (AB)⁺ = {A, B, C}

The two sets of functional dependencies are not the same and hence one cannot be minimal of other.

The correct answer is option 1

Explanation:

Since attribute D is not a part of any FD, it must be a part of the candidate key.

AD+ = {ABCDEFGH}

BD+ = {ABCDEFGH}

ED+ = {ABCDEFGH}

FD+ = {ABCDEFGH}

CD+, GD+, and HD+ do not all the attributes. So, they can not be candidate keys.

Candidate keys are AD, BD, ED, and FD.

A → BC, B → CFH, and F → EG are partial dependencies. So, the relation is not in 2NF.

Hence the relation is in 1NF but not in 2NF.

X = (PQRS)

Set of functional dependencies

F = {QR → S, R → P, S → Q}

QR → {Q, R, S, P}

SR → {S, R, P, Q}

QR and SR are keys of Relation schema X

R → P …

part of key (R) → non key(P)

It is partial dependency and hence not in 2^nd normal form and therefore not in BCNF (Given)

X is decomposed into two schemas Y and Z, where Y = (PR) and Z = (QRS)

For Y = (PR)

R → P

Y is in BCNF because binary attribute (R is a key).

For Z = (QRS)

QR → S

S → Q

SR → {R, S, Q}

QR → {R, Q, S}

QR and SR are keys of Relation schema X

Z is not in BCNF because

S → Q and S is not Super key.

∴ statement I is incorrect

Dependency Preserving:

R → P is in Y

QR → S in Z

S → Q is in Z

Hence, it is dependency preserving.

Lossless:

Y ∩ Z = R which is key of Y.

Therefore, it is Lossless

Statement II is correct.

Hence, option 3 is the answer

A functional dependency is a constraint that describes the relationship between attributes in a relation. Data integrity refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or another construct. Referential integrity is a relational database concept, which states that table relationships must always be consistent. Normalization is a process of organizing the data in the database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly. 1NF, 2NF, 3NF and BCNF are types of normal forms. So, option (D) is correct.