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

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_{1}(A,
B), and R_{2}(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/definelosslessjoindecompositionwithexample.html
http://www.exploredatabase.com/2016/11/isgivendecompositionislosslessjoindecomposition.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/closureindatabasehomepage.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