Functional dependencies and normalization solved multiple choice questions
Functional Dependencies and Normalization MCQ with Answers
1. Consider
relation R(A,B,C,D,E) with functional dependencies:
AB
→ C, C → D, BD → E
Which of the
following attribute sets does not functionally determine E ?
a) AB
b) AC
c) BC
d) ABC
View Answer
Answer:
(b)
(AB)^{+}
= ABCDE
(BC)+
= BCDE
(ABC)^{+}
= ABCDE
(AC)^{+}
= AC
Only
the closure of AC does not include E in the result.

2. Let relation
R(A,B,C,D) satisfy the following set of functional dependencies:
S_{1}
= {A → B, B → C, C → A}
A different set S_{2}
of functional dependencies is equivalent to S_{1} if exactly the
same FDs follow from S_{1} and S_{2}. Which of the following
sets of functional dependencies is equivalent to the set above? [Refer here: How to find whether two sets of FDs are equivalent to each other or not]
a) B → AC, C → AB
b) A → B, B → A, C
→ A
c) A → BC, C → AB
d) A → BC, B → AC, C → AB
View Answer
Answer:
(d)
Two
sets of FDs are said to be equal if every FD of one of them can be inferred
from the FDs of the other and vice versa.
If
the set of FDs of S2 can be inferred from FDs of S1, then we would say that
S1 covers S2. We check this for the answer (d).
If
the set of FDs of S_{1} can be inferred from FDs of S_{2},
then we would say that S_{2} covers S_{1}. We check this for
the answer (d).
S_{1}
covers S_{2} and S_{2} covers S_{1}. Hence, we would
say that S_{1} covers S_{2} and so they are equivalent.

3. Suppose relation
R(A,B,C) has tuples (0,0,0) and (1,2,0) , and it satisfies the functional dependencies
A → B and B → C . Which of the following tuples may be inserted into R legally?
a) (0,0,1)
b) (1,2,1)
c) (0,1,1)
d) (0,0,2)
View Answer
Answer:
None are correct
None of the options are correct.
If
the record (0,0,1) will be inserted, it will violate the FD B → C. because,
alredy there exists a record with B value 0 and C value 0. Now we try to
insert B value 0 and C value 1. Likewise, record (b) and (d) both will violate
this FD.
If
the record (0,1,1) will be inserted, it will violate both FDs A → B and B → C.

4. Under what
isolation level is the following schedule allowed?
R_{3}(b); R_{1}(b);
W_{3}(p); R_{2}(b); R_{1}(p); R_{1}(c); W_{2}(c);
W_{1}(c); R_{3}(c); R2(c); W_{3}(p);
[Refer here: Transaction isolation level READ UNCOMMITTED]
a) Read uncommitted
b) Read committed
c) Repeatable read
d) Serializable
View Answer
Answer:
(a)
Given
schedule;
R_{3}(b);
R_{1}(b); W_{3}(p); R_{2}(b); R_{1}(p);
R_{1}(c); W_{2}(c); W_{1}(c); R_{3}(c);
R2(c); W_{3}(p);
In
this schedule, transaction 1 reads a value (R1(p)) which was written
by transaction 3 (W_{3}(p)) before T3 commits. Hence, the read was a
dirty read. The transaction isolation level that permits this type of read is
READ UNCOMMITTED. [Other violating instructions also highlighted].
[Refer here: Transaction isolation level READ UNCOMMITTED]

5. Consider the
following relational schema and set F of functional dependencies;
R(A,B,C,D,E,F,G), F
= {E → C, G → AD, B → E, C → BF}. Which of the following is E^{+}?
a) EC
b) ECG
c) BCEF
d) ABCEF
View Answer
Answer:
(c)
E+
is the closure of E. This can be calculated using the given FD.
E+ = EC from FD E → C
= ECBF from FD C → BF
= ECBF from FD B → E
(no change)
No
more FDs with any of the attributes or combination of E, C, B, and F. Hence,
closure finding algorithm stops here.
[Refer here: How to find closure of a set of attributes here]

**************
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