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) R1(CDA),
R2(BC)
b) R1(BD),
R2(CA)
c) R1(BC),
R2(CA), R3(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 Fc) of the relation R?
a) Fc =
{C → DA, B → C}
b) Fc =
{BC → A, BC → D}
c) Fc =
{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 R1(C, D, A) and R2(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
No comments:
Post a Comment