Advanced Database Management System - Tutorials and Notes: Functional dependencies and normalization solved multiple choice questions

Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

Sunday, 19 April 2020

Functional dependencies and normalization solved multiple choice questions

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:
S1 = {A → B, B → C, C → A}
A different set S2 of functional dependencies is equivalent to S1 if exactly the same FDs follow from S1 and S2. 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).
  • The FD A → BC of S2 can be inferred from the FDs A → B and B → C of S1.
  • The FD B → AC of S2 can be inferred from the FDs B → C and C → A of S1.
  • The FD C → AB of S2 can be inferred from the FDs C → A and A → B of S1.
If the set of FDs of S1 can be inferred from FDs of S2, then we would say that S2 covers S1. We check this for the answer (d).
  • The FD A → B of S1 can be inferred from the FDs A → BC of S2.
  • The FD B → C of S1 can be inferred from the FDs B → AC of S2.
  • The FD C → A of S1 can be inferred from the FDs C → AB of S2.
S1 covers S2 and S2 covers S1. Hence, we would say that S1 covers S2 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?
R3(b); R1(b); W3(p); R2(b); R1(p); R1(c); W2(c); W1(c); R3(c); R2(c); W3(p); 


a) Read uncommitted
b) Read committed
c) Repeatable read
d) Serializable

View Answer

Answer: (a)
Given schedule;
R3(b); R1(b); W3(p); R2(b); R1(p); R1(c); W2(c); W1(c); R3(c); R2(c); W3(p);
In this schedule, transaction 1 reads a value (R1(p)) which was written by transaction 3 (W3(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]. 

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.

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


Related posts:





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.




No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents