Friday, April 17, 2020

Normalization process multiple choice questions with answers

MCQ on Database Management Systems Normalization process, Normalization quiz on 1NF, 2NF, 3NF and BCNF


Normalization Quiz Questions with Answers


Consider the attribute set R = ABCDEGH and the FD set F = {AB → C, AC → B, AD → E, B → D, BC → A, E → G} to answer the following questions.
Q1. Which of the following relations if decomposed from R satisfies BCNF?
a) ABC
b) ABCD
c) ABCEG
d) None of the above


View Answer

Answer: (a)
Let us say R1 = ABC. If decomposed from R, R1 will have the following set of functional dependencies F1;
F1 = {AB → C, AC → B, BC → A}
The candidate keys for R1 are AB, AC, and BC. As per rules governing BCNF, LHS of all the functional dependencies must be the candidate key which is true for R1. Hence, R1 in BCNF.


Q2. Which of the following relations if decomposed from R satisfies BCNF?
a) ABCD
b) ABCEG
c) AECH
d) None of the above


View Answer

Answer: (c)
Let us say R1 = AECH. If decomposed from R, R1 will have no functional dependencies. Hence, the key for R1 is AECH itself. So, R1 is in BCNF.

Q3. Which of the following relations if decomposed from R does not satisfy 2NF?
a) ABC
b) AB
c) ABCEG
d) All of the above


View Answer

Answer: (c)
Let us say R1 = ABCEG. If decomposed from R, R1 will have the following set of functional dependencies F1;
F1 = { AB → C, AC → B, BC → A, E → G}
Candidate keys;
            (AB)+ = ABC ≠ R1
            (AC)+ = ACB ≠ R1
            (BC)+ = BCA ≠ R1
            (ABE)+ = ABCEG = R1. Likewise, (ACE)+ = (BCE)+ = R.
Hence, candidate keys are ABE, ACE, and BCE.
As per the rules of 2NF, there shouldn’t be any partial key dependencies.
But, in R1, the FD E → G is a partial key dependency. That is, E is not a candidate key but a key attribute. And E alone determines another non-key attribute G uniquely (the FD E → G). Hence, R1(ABCEG) is not in 2NF.

Q4. Let us suppose that R1 = DCEGH is a relation which is decomposed from R satisfies the set F of functional dependencies F= {E → G}. What is the strongest normal form currently R1 is in?
a) 1NF
b) 2NF
c) 3NF
d) BCNF


View Answer

Answer: (a)
The key is DCEH.
R1 is not in BCNF because E on the LHS of FD E → G is not a candidate key.
R1 is not in 3NF and not even in 2NF because the FD E → G is a partial key dependency.
Hence, R1 is in 1NF.

Q5. If R1 = ABCD is decomposed from R, which of the following decomposition of R1 satisfies BCNF?
a) R11(AB), R12(CD)
b) R11(ABC), R12(BD)
c) R11(AC), R12(BD)
d) R11(BCD), R12(AB)


View Answer

Answer: (b)
If R1(ABCD) is decomposed from R, then the set of functional dependencies holds by R1 is as follows;
F1 = { AB → C, AC → B, B → D, BC → A }
We can take, for instance, the first FD AB → C to find the closure. The closure of AB = ABC. So, we can create a separate table R11(ABC) with the first FD. The remaining is the FD B → D, and B+ is BD. This can go as R12(BD).
Hence, the decomposition results in R11(ABC), R12(BD)

**************
Related posts:





Quiz questions with answers on DBMS normalization

Sample quiz questions on normalization process of database management systems

MCQ with answers on normalization process of DBMS

Normalization solved exercises in MCQs.

No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery