Normalization MCQ

Normalization MCQ / Verify whether the given functional dependencies are valid or not / what is not a functional dependency / relationship between LHS and RHS attributes in a FD.

Question:

Given the instance of a relation X above, which of the following functional dependencies are TRUE with respect to X?

a) startpage à endpage

b) TR-ID à ID

c) ID à startpage endpage journal issue

d) journal issue à year

Answer: (c) ID à startpage endpage journal issue and (d) journal issue à year   Functional dependency: In simpler terms, a functional dependency is a constraint (condition) where the Left Hand Side (LHS) attribute(s) uniquely determine the value of Right Hand Side (RHS) attribute(s). In other words, for a given LHS attribute (or combination of attributes) value, there should be only one RHS value.   Let us check each of these options; a)     Startpage à endpage. This is not a valid functional dependency (FD) because the constraint does not hold for the given instance of data (refer table). For example, in the given table, the startpage values for 5th and 6th records are same, ie., 1. As per the definition of FD, the RHS attribute cannot have more than one value. Contrast to this, we have two different values, namely 3 and 5 as endpage values. If the constraint is violated for at least one record, we cannot accept that as a valid FD. Refer to 2nd and 3rd records where startpage is 69 and endpage is 85. That is, wherever the LHS repeats, the RHS must repeat as well. If this is TRUE for all records, then we can say that the FD holds, otherwise not. b)     TR-ID à ID. This is not a valid FD. For an TR-ID value 98, we have two ID values 77 and 78. Hence violated. c)      ID à startpage endpage journal issue. This is a valid functional dependency. For a given ID value, the combinations of RHS attributes values are unique. d)     Journal issue à year. This is a valid FD. Here, for every given combination of values of attributes journal and issue, there is only one year value.

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

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 concepts

Valid functional dependencies

Find the keys of relation in DBMS

Check whether the Functional dependency is valid or not

Find the valid FDs of given relation

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

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