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;
Dependency preserving decomposition If a relation R with set F of functional dependencies is decomposed into relations R_{1}, R_{2}, R_{3}, …, R_{i} then the closure of set of functional dependencies for these relations should satisfy the following;

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) Unnormalized form
b) Third Normal Form
c) BoyceCodd Normal Form
d) Domain Key Normal Form
Answer: (b) 3NF The candidate keys for R are ACD, BCD, and ECD. No nonkey dependencies found in R. Hence, relation R is in third normal form. 
 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