Normalization in DBMS - Multiple Choice Questions with Answers

Normalization process in RDBMS, multiple choice questions with answers in RDBMS, normal forms and functional dependencies MCQs.

Database Management Systems Quiz - Normalization Process in DBMS

Assume a relation R(A, B, C, D)  with set of functional dependencies F = { C → D, C → A, B → C}. Use this setup to answer the following questions;

1. Which of the following is the candidate keys of R?

a) C

b) BC

c) B

d) Both (b) and (c)

View Answer

Answer: (c) B Only left hand side (LHS) attributes of the given set of FDs are C and B. B determines C uniquely. Hence, we can find the closure of B to check whether it forms the candidate key or not as follows; B+ = BCDA through FDs B → C, C → D, and C → A. And, B+ = R. So, B is the only candidate key for R.

2. Which is the normal form that the relation R is currently complies with?

a) First Normal Form (1NF)

b) Second Normal Form (2NF)

c) Third Normal Form (3NF)

d) All of the above

View Answer

Answer: (b) Second Normal Form (2NF) C → D, and C → A are non-trivial FDs. C is not a super key and D is not part of any candidate key. Hence, R is not in 3NF. No partial key dependencies present in R since B is the only key (and is not composite key). Hence, R is currently in 2NF.

3. R is not in BCNF. Which of the following shows the correct decomposition of R into BCNF relations?

a) R₁(CDA), R₂(BC)

b) R₁(BD), R₂(CA)

c) R₁(BC), R₂(CA), R₃(CD)

d) None of the above

View Answer

Answer: (c) R1(BC), R2(CA), R3(CD) BCNF relation – “The LHS of a functional dependency should be a key for the relation if the relation is in BCNF”. The FDs C → D and C → A violates the properties of BCNF because C is not a candidate key for R. (B → C does not violate because B is the candidate key for R). Let us decompose R using one of the violating FD C → D. To decompose, let us create separate relation for violating FD. We will get R1(A, B, C) and R2(C, D). Is the decomposition in BCNF? R2 is in BCNF due to the FD C → D whereas R1 is not due to the FD C → A. So, let us further decompose R1 by creating a relation for violating FD. We will get R11(B, C) and R12(C, A). Both are in BCNF. Hence, BCNF decomposition of R is R1(B, C), R2(C, A) and R3(C, D).

4. Which among the following is the canonical cover (minimal cover F_c) of the relation R?

a) F_c = {C → DA, B → C}

b) F_c = {BC → A, BC → D}

c) F_c = {C → A, B → C, D → A}

d) All of the above

View Answer

Answer: (a) Fc = {C → DA, B → C} A canonical cover (minimal cover) of a set of FDs F is a minimal set of functional dependencies Fmin that is equivalent to F. To understand the process of finding minimal cover, please refer here. For the relation R and set of FDs F, the set of functional dependencies {C → DA, B → C} is the minimal set.

5. R can be decomposed into set of 3NF relations R₁(C, D, A) and R₂(B, C). This decomposition is a _____.

a) Lossless join decomposition

b) Lossy join decomposition

c) Dependency preserving decomposition

d) Both lossless and dependency preserving decomposition

View Answer

Answer: (d) Both lossless and dependency preserving decomposition Decomposition is lossless if either (R1 ∩ R2) → R1 or (R1 ∩ R2) → R2 holds. According to the question, (R1 ∩ R2) → R1 is TRUE [How? (CDA) ∩ (BC) → (CDA) ⇒ C → CDA]. Hence, the decomposition is lossless. Decomposition is dependency preserving if (F1 U F2 U … Fn)+ = F+. that is, if the closure of union of individual relations functional dependencies is equal to the closure of FDs of original relation. This is also true here. Hence, the decomposition is a dependency preserving decomposition.

*************************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page

Quiz questions with answers on DBMS normalization

Solved quiz questions on functional dependencies and normalization process of database management systems

MCQ with answers on normalization process of DBMS

Normalization solved exercises in MCQs.

2NF, 3NF, BCNF, lossless and depdendency preserving decomposition

Functional dependencies and normalization solved multiple choice questions

Functional dependencies and normalization solved multiple choice questions

Functional Dependencies and Normalization MCQ with Answers

1. Consider relation R(A,B,C,D,E) with functional dependencies:

AB → C, C → D, BD → E

Which of the following attribute sets does not functionally determine E ?

a) AB

b) AC

c) BC

d) ABC

View Answer

Answer: (b) (AB)+ = ABCDE (BC)+ = BCDE (ABC)+ = ABCDE (AC)+ = AC Only the closure of AC does not include E in the result.

2. Let relation R(A,B,C,D) satisfy the following set of functional dependencies:

S₁ = {A → B, B → C, C → A}

A different set S₂ of functional dependencies is equivalent to S₁ if exactly the same FDs follow from S₁ and S₂. Which of the following sets of functional dependencies is equivalent to the set above? [Refer here: How to find whether two sets of FDs are equivalent to each other or not]

a) B → AC, C → AB

b) A → B, B → A, C → A

c) A → BC, C → AB

d) A → BC, B → AC, C → AB

View Answer

Answer: (d) Two sets of FDs are said to be equal if every FD of one of them can be inferred from the FDs of the other and vice versa. If the set of FDs of S2 can be inferred from FDs of S1, then we would say that S1 covers S2. We check this for the answer (d). The FD A → BC of S2 can be inferred from the FDs A → B and B → C of S1. The FD B → AC of S2 can be inferred from the FDs B → C and C → A of S1. The FD C → AB of S2 can be inferred from the FDs C → A and A → B of S1. If the set of FDs of S1 can be inferred from FDs of S2, then we would say that S2 covers S1. We check this for the answer (d). The FD A → B of S1 can be inferred from the FDs A → BC of S2. The FD B → C of S1 can be inferred from the FDs B → AC of S2. The FD C → A of S1 can be inferred from the FDs C → AB of S2. S1 covers S2 and S2 covers S1. Hence, we would say that S1 covers S2 and so they are equivalent.

3. Suppose relation R(A,B,C) has tuples (0,0,0) and (1,2,0) , and it satisfies the functional dependencies A → B and B → C . Which of the following tuples may be inserted into R legally?

a) (0,0,1)

b) (1,2,1)

c) (0,1,1)

d) (0,0,2)

View Answer

Answer: None are correct None of the options are correct. If the record (0,0,1) will be inserted, it will violate the FD B → C. because, alredy there exists a record with B value 0 and C value 0. Now we try to insert B value 0 and C value 1. Likewise, record (b) and (d) both will violate this FD. If the record (0,1,1) will be inserted, it will violate both FDs A → B and B → C.

4. Under what isolation level is the following schedule allowed?

R₃(b); R₁(b); W₃(p); R₂(b); R₁(p); R₁(c); W₂(c); W₁(c); R₃(c); R2(c); W₃(p); 

[Refer here: Transaction isolation level READ UNCOMMITTED]

a) Read uncommitted

b) Read committed

c) Repeatable read

d) Serializable

View Answer

Answer: (a) Given schedule; R3(b); R1(b); W3(p); R2(b); R1(p); R1(c); W2(c); W1(c); R3(c); R2(c); W3(p); In this schedule, transaction 1 reads a value (R1(p)) which was written by transaction 3 (W3(p)) before T3 commits. Hence, the read was a dirty read. The transaction isolation level that permits this type of read is READ UNCOMMITTED. [Other violating instructions also highlighted].  [Refer here: Transaction isolation level READ UNCOMMITTED]

5. Consider the following relational schema and set F of functional dependencies;

R(A,B,C,D,E,F,G), F = {E → C, G → AD, B → E, C → BF}. Which of the following is E⁺?

a) EC

b) ECG

c) BCEF

d) ABCEF

View Answer

Answer: (c) E+ is the closure of E. This can be calculated using the given FD.             E+       = EC from FD E → C                         = ECBF from FD C → BF                         = ECBF from FD B → E (no change) No more FDs with any of the attributes or combination of E, C, B, and F. Hence, closure finding algorithm stops here. [Refer here: How to find closure of a set of attributes here]

**************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page

Quiz questions with answers on DBMS normalization

Solved quiz questions on functional dependencies and normalization process of database management systems

MCQ with answers on normalization process of DBMS

Normalization solved exercises in MCQs.