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:

S₁ = {A → B, B → C, C → A}

A different set S₂ of functional dependencies is equivalent to S₁ if exactly the same FDs follow from S₁ and S₂. 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?

R₃(b); R₁(b); W₃(p); R₂(b); R₁(p); R₁(c); W₂(c); W₁(c); R₃(c); R2(c); W₃(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; 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].  [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

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.

Relational model in DBMS Multiple choice questions with answers

Relational model in DBMS Multiple choice questions with answers

Multiple Choice Questions 30:

1. Which one of the following is correct?

a) Each candidate key is a primary key

b) Each primary key is a foreign key

c) Each foreign key must refer a primary key of a relation.

d) Each foreign key refers a candidate key in a relation.

Answer:

d) Each foreign key refers a candidate key in a relation.

Foreign key attribute refers attribute that contains unique values. It need not be the primary key always.

2. The value of an attribute in a table may be NULL because,

a) the value is not known

b) the value does not exist

c) the value is not applicable

d) all of the above

Answer:

d) all of the above.

NULL refers to all the options a, b, and c.

3. Consider a relation office with the following schema;

Office(Cabin_no, Room_no, Phone)

Room number is unique. Each room consists of approximately 20 cabins and each cabin number is unique with respect to room number. The same cabin number may be used in different rooms. Each room has unique phone number.

Which of the following is correct?

a) Room_no is a candidate key

b) Phone is a candidate key

c) (Cabin_no, Phone) and (Cabin_no, Room_no) are the candidate keys.

d) (Room_no, Phone) and (Room_no, Cabin_no) are the candidate keys.

Answer:

c) (Cabin_no, Phone) and (Cabin_no, Room_no) are the candidate keys.

Room_no is unique and each room has 20 cabins hence, retrieving data with Room_no as key will end up in at least 20 records. The same is applicable for Phone.

Cabin_no is unique for each room[or phone]. So, (cabin_no, phone) or (cabin_no, room_no) can uniquely identify records.

4. Consider the following schema of relation R;

R (A, B, C)

Attributes A, B, and C are all unique valued attributes. Which of the following is TRUE for R?

a) A is a candidate key for R

b) B is a candidate key for R

c) (A, C) is a super key for R

d) all of the above

Answer:

d) all of the above

As all attributes are unique valued attributes, each one of them is a candidate key on its own. Also, the combination of candidate keys cannot form another candidate key but a super key.

5. Which of the following is usually chosen as a primary key for a relation.

a) A candidate key that is composite

b) A super key

c) A candidate key that is minimal

d) All of the above

Answer:

c) A candidate key that is minimal

A candidate key that is composite can also be chosen as primary key. But in practice people usually prefers a minimal candidate key, that is, a candidate key that has minimal number of attributes. For example, if in a relation R(A, B, C), A is a candidate key and (B,C) is another candidate key we prefer A as the primary key though (B, C) can also act as a primary key.

***********

Go to Multiple Choice Questions in DBMS home

Go to Normalization - MCQs page

Go to Advanced DBMS Concepts page

multiple choice questions in relational model of DBMS

MCQs in relational model

MCQ about super key, candidate key and primary key

relational data model in database

solved quiz questions with answer in relational model

Find minimal cover/canonical cover in a set of FDs

Find minimal cover/canonical cover in a set of given functional dependencies

Question:

For a relation schema R = (A, B, C, D, E), consider the following set of functional dependencies;

F = {A → BC, CD → E, B → D, E → A}

Using the functional dependencies compute the canonical cover F_c.

Solution:

Minimal cover: Definition 1: A minimal cover of a set of FDs F is a minimal set of functional dependencies Fmin that is equivalent to F. There can be many such minimal covers for a set of functional dependencies F. Definition 2: A set of FDs F is minimum if F has as few FDs as any equivalent set of FDs. [How to find minimal cover? – Refer here].

Follow these steps to find the canonical (minimal) cover;

1. Decompose each functional dependency in F to have singleton (single) attribute on the Right Hand Side (RHS).

2. Remove extraneous (redundant) attributes from Left Hand Side of each functional dependency.
[What is extraneous attribute? Refer here].

3. Find and eliminate redundant functional dependencies.

Let us apply the above said properties to F;

F = {A → BC, CD → E, B → D, E → A}

1. RHS of all functional dependencies should be singleton. To achieve this we decompose each functional dependency that has two or more attributes in their RHS. We get the following set of functional dependencies F1;

F1 = {A → B, A → C, CD → E, B → D, E → A}

2. Remove extraneous (redundant) attributes. This is applicable only for those functional dependencies that have two or more attributes on its LHS.

We need to check whether or not an attribute/set of attributes of LHS of a functional dependency is redundant or not. For checking this, we have to find the closure of each individual LHS attribute of each FD that has two or more LHS attributes.

With this property we have only one FD, CD → E. We need to check whether C alone or E alone can find E uniquely.

C+ = C by reflexivity rule

D+ = D by reflexivity rule

From this we found that either C or D alone cannot determine the value of E. Hence, the FD CD → E does not have any extraneous attributes.

Hence, our F1 is as follows;

F1 = {A → B, A → C, CD → E, B → D, E → A}

3. Eliminate redundant functional dependencies.

To find redundant FDs, we have to find whether the RHS of a FD can be determined using other FDs.

(i) Let us check first for FD, A → B. To proceed, we forget , A → B from F, and use only the remaining FDs A → C, CD → E, B → D, E → A of F. That is our F becomes F1 = {A → C, CD → E, B → D, E → A}

Now find A+ using F1. A+ = {A,C} which does not include B. Hence, A → B is not redundant.

(ii) Now check for A → C. can we find C using following FDs?

F1 = {A → B, CD → E, B → D, E → A}

A+ = ABD

B+ = BD

We are not able to determine C using F1. Hence, A → C is not redundant.

If we repeat this for each FD in F, we found that none of them are redundant.

Hence our Fc is as follows;

Fc = {A → BC, CD → E, B → D, E → A} which is same as F in the question.

***********

Similar topics

How to find closure of set of functional dependencies?

How to find closure of attributes?

How to find canonical cover for a set of functional dependencies?

How to find extraneous attribute?

Go back to Question/QUIZ page

Go back to Online Quizzes home page

find canonical cover of F

what is minimal cover? how to find minimal cover

minimal cover solved exercise

find extraneous attributes