Normalization MCQ with answers in DBMS 19

Normalization in DBMS, solved exercises in DBMS, lossless join decomposition, dependency preserving decomposition, bcnf decomposition

RDBMS MCQ solved quiz and answers

1. Let us assume that a relation R (A, B, C, D, E) with set of functional dependencies F = {A → BC, C → D} is decomposed into relations R1 (A, B, C) and R2 (A, D, E). This decomposition is ___________.

a) Lossless join decomposition

b) Dependency preserving decomposition

c) Not a dependency preserving decomposition

d) Lossy decomposition

Answer: (a) and (c) Common attribute between R1 and R2 is A, and, attribute A determines all attributes of R1. Hence, it is a lossless decomposition. It is not a dependency preserving decomposition because the FD C → D is lost. Lossless join decomposition Decomposition of relation R into R1 and R2 is said to be lossless join decomposition if one of the following holds; (R1 ∩ R2) → R1 (R1 ∩ R2) → R2 Dependency preserving decomposition If a relation R with set F of functional dependencies is decomposed into relations R1, R2, R3, …, Ri then the closure of set of functional dependencies for these relations should satisfy the following;  (F1 U F2 U F3 U … U Fi)+ = F+  That is the closure of union of set of functional dependencies of relations R1, R2, …, Ri should be equal to the closure of set of functional dependencies F of R. In other words, all the functional dependencies in (F1 U F2 U F3 U … U Fi)+ should be in F+ also.

 

2. Let us assume that a relation R (A, B, C, D, E, F, G, H) with set of functional dependencies F = {AB → E, C → D, D → E, FG → A} is decomposed into relations R1(ABE), R2(CD), R3(FGA) and R4(BCFGH). The decomposition _____.

a) is resulted in all BCNF relations

b) is dependency preserving decomposition

c) is not a dependency preserving decomposition

d) is lossless decomposition

Answer: (a) and (c) R1(ABE), R2(CD), R3(FGA) and R4(BCFGH). Keys are underlined. All relations are in BCNF. The functional dependency D → E is lost. Hence, decomposition of R into R1, R2, R3 and R4 is not a dependency preserving decomposition.

 

3. Consider a relation R(A, B, C, D, E) with the set of functional dependencies F = {A → B, B → E, E → A}. Relation R is in ____.

a) Un-normalized form

b) Third Normal Form

c) Boyce-Codd Normal Form

d) Domain Key Normal Form

Answer: (b) 3NF The candidate keys for R are ACD, BCD, and ECD. No non-key dependencies found in R. Hence, relation R is in third normal form.

 

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

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 introduction concepts

How many keys a leaf node can have in a B+ tree in DBMS

Find the keys of relation in DBMS

Check whether the decomposition is dependency preserving or not

Is the decomposition a lossless join decomposition

List down rules for dependency preserving and lossless join decomposition