MCQ on normalization and join operations in Database Mangement Systems

Multiple choice questions with answer on normalization process and join operations in database management systems for exams

MCQ on DBMS - Normalization and Join operations

1. Consider Relation R(ABCD) and functional dependencies (FDs): BD → AC; AB → D; AC → B. Which of the following is a candidate key of R?

a) AB

b) BD

c) AC

d) All of the above

View Answer

Answer: (d) All of the above To check for a candidate key, let us find the closure of these attribute sets as follows; (AB)+ is the closure of (AB). This can be calculated using the given set of FDs.             AB+    = ABD from FD AB → D                         = ABDC from FD BD → AC                         = ABCD = R As the closure of AB includes all attributes from R, AB is a candidate key.             BD+    = ABCD through FD BD → AC             AC+    = ABCD through FDs AC → B and AB → D To know more about closure, please refer here.

2. Consider two relations R(a,b,c) and S(a,d,e). T(R)=200, T(S)=100. R.a is a foreign key referencing S.a and S.a is the primary key of S. What is the estimated size of R ⨝ S?

a) 100

b) 150

c) 200

d) 300

View Answer

Answer: (c) 200 Given, S.a is the primary key and R refers it through R.a. Natural join works on equivalence of common attributes. Foreign key ensures values only from the primary key table, in our case it is S. Foreign key table has 200 records that are valid. Hence, the join will result in 200 records.

3. Consider relation R(A, B) and S(B, C), where T(R) = 200 and T(S) = 100, and B is a key for R. What is the estimate for T(R ⨝ S)?

a) 100

b) 150

c) 200

d) 300

View Answer

Answer: (a) 100 Given, Attribute B is the primary key for R. In that case, S.B must be the foreign key that refers R.B. Maximum matching number of records are the records of foreign key table. Hence, only 100 records are possible as maximum as the result of R ⨝ S.

4. Consider a relational schema R(A, B, C). Let us suppose that we decompose R into R₁(A, B), and R₂(A, C). Is this decomposition always lossless?

a) Yes

b) No

c) Depends on FDs

d) Cannot determined

View Answer

Answer: (c) Depends on FDs We cannot determine whether the decomposition is lossless or not without set of functional dependencies hold one the given relation. Refer here for How to find whether the decomposition is lossless or not? http://www.exploredatabase.com/2016/04/define-lossless-join-decomposition-with-example.html http://www.exploredatabase.com/2016/11/is-given-decomposition-is-lossless-join-decomposition.html

5. Let R(A, B, C, D, E, F) be a database schema with set F of functional dependencies F = {AB → C, BC → AD, D → E, CF → B}. What is the closure of AB?

a) ABC

b) ABCD

c) ABCDE

d) ABCDEF

View Answer

Answer: (c) ABCDE To find the closure of the given set of attributes; (AB)+ is the closure of (AB). This can be calculated using the given set of FDs.             AB+    = ABC from FD AB → C                         = ABCD from FD BC → AD                         = ABCDE from FD D → E                         = ABCDE because no more FDs that have one of these attributes on the LHS. http://www.exploredatabase.com/2016/12/closure-in-database-home-page.html

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

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.

Find the candidate keys of a relation in RDBMS

Find all the candidate keys of relation R - solved exercise

Question:

Find all the candidate keys of R given R and the set F of functional dependencies (FDs) as follows;

R = (a, b, c, d, e) and F = {a → c, c → bd, d → a}

Solution:

Let us follow the steps given in the box below to find all the candidate keys of R.

Step 1: Let A = set of attributes not present in the RHS of any FD. The set of attributes in A must be a part of any candidate key of R. Step 2: Let B = set of attributes appear on RHS but not on LHS of any FD. The set of attributes in B cannot be a part of any candidate key of R. Step 3: Find closure A+. if A+ = R, then A is the only candidate key Step 4: If A+ ≠ R, then for each attribute x in R-B, ·        Test whether A U {x} is a candidate key. ·        If not, add another attribute from R-B to A and test whether it is a candidate key ·        Repeat this step until all candidate keys found

Given,

R = (a, b, c, d, e) and F = {a → c, c → bd, d → a}

Step 1: Out of all attributes of R, only {e} is not present in the RHS of any FD. So, A = {e}. And, every candidate key of R must have the attribute e in it.

Step 2: Set of attributes appear on RHS of any FD are a, b, c, and d. Out of them the attributes not appear on the LHS of FDs is b. So, B = {b}. And, the attribute b cannot be part of any candidate key. 

Step 3: A⁺ = {e}⁺ ≠ R. So, let us move on to step 4.

Step 4: Since A⁺ ≠ R is not a candidate key, then for each attribute x in (R – B) check whether A U {x} is a candidate key or not.

R – B = {a, b, c, d, e} – {b} = {a, c, d, e}

Now, let us combine attributes of A with each attribute of (R-B) and check whether they form a candidate key or not. That is, check whether {a, e}, {c, e} and {d, e} forms candidate key or not using closure of these attribute sets. [Refer how to find closure here, one more solved exercise]

{a, e}⁺         = {a, e}

                   = {a, e, c} from FD a → c

                   = {a, e, c, b, d} from FD c → b d

                   = R

Hence, {a, e} is a super key and also a candidate key.

{c, e}⁺ = {c, e, b, d, a} = R, hence a candidate key.

{d, e}⁺ = {d, e, a, c, b} = R, hence a candidate key.

Result:

The candidate key of R are {a, e}, {c, e} and {d, e}.

**********

Go back to Normalization – solved exercises page.

Go to How to find closure page 

Go to Database Closures - Home page

Go to 2NF, 3NF and BCNF

Find all candidate keys of a relation in RDBMS

How to find candidate keys in a database relation

steps to find candidate keys

Easy way to find candidate keys

Candidate keys solved exercise

Candidate keys for normalization