Database Questions and Answers – Algorithms for Decomposition

This set of Database Multiple Choice Questions & Answers (MCQs) focuses on “Algorithms for Decomposition”.

1. A relation is in ____________ if an attribute of a composite key is dependent on an attribute of other composite key.
a) 2NF
b) 3NF
c) BCNF
d) 1NF
View Answer

Answer: b
Explanation: A relation is in 3 NF if an attribute of a composite key is dependent on an attribute of other composite key. (If an attribute of a composite key is dependent on an attribute of other composite key then the relation is not in BCNF, hence it has to be decomposed.).

2. What are the desirable properties of a decomposition
a) Partition constraint
b) Dependency preservation
c) Redundancy
d) Security
View Answer

Answer: b
Explanation: Lossless join and dependency preserving are the two goals of the decomposition.

3. R (A,B,C,D) is a relation. Which of the following does not have a lossless join dependency preserving BCNF decomposition?
a) A->B, B->CD
b) A->B, B->C, C->D
c) AB->C, C->AD
d) A->BCD
View Answer

Answer: d
Explanation: This relation gives a relation without any loss in the values.
advertisement
advertisement

4.

Class (course id, title, dept name, credits, sec id, semester, YEAR, building, room NUMBER, capacity, TIME slot id)
The SET OF functional dependencies that we require TO hold ON class are:
course id->title, dept name, credits
building, room number->capacity
course id, sec id, semester, year->building, room NUMBER, TIME slot id
A candidate KEY FOR this schema IS {course id, sec id, semester, YEAR}

Consider the above conditions. Which of the following relation holds?
a) Course id-> title, dept name, credits
b) Title-> dept name, credits
c) Dept name-> credits
d) Cannot be determined
View Answer

Answer: a
Explanation: Here course id is not a superkey. Thus, class is not in BCNF.

5. The algorithm that takes a set of dependencies and adds one schema at a time, instead of decomposing the initial schema repeatedly is
a) BCNF algorithm
b) 2NF algorithm
c) 3NF synthesis algorithm
d) 1NF algorithm
View Answer

Answer: c
Explanation: The result is not uniquely defined, since a set of functional dependencies can have more than one canonical cover, and, further, in some cases, the result of the algorithm depends on the order in which it considers the dependencies in Fc.
advertisement

6. The functional dependency can be tested easily on the materialized view, using the constraints ____________.
a) Primary key
b) Null
c) Unique
d) Both Null and Unique
View Answer

Answer: d
Explanation: Primary key contains both unique and not null constraints.

7. Which normal form is considered adequate for normal relational database design?
a) 2NF
b) 5NF
c) 4NF
d) 3NF
View Answer

Answer: d
Explanation: A relational database table is often described as “normalized” if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies.
advertisement

8. Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is
a) Zero
b) More than zero but less than that of an equivalent 3NF decomposition
c) Proportional to the size of F+
d) Indeterminate
View Answer

Answer: b
Explanation: Redundancy in BCNF is low when compared to 3NF. For more details on BCNF.

9. A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies:

F1->F3
F2->F4
(F1,F2)->F5

in terms of normalization, this table is in
a) 1NF
b) 2NF
c) 3NF
d) None of the mentioned
View Answer

Answer: a
Explanation: Since the primary key is not given we have to derive the primary key of the table. Using the closure set of attributes we get the primary key as (F1, F2). From functional dependencies, “F1->F3, F2->F4”, we can see that there is partial functional dependency therefore it is not in 1NF. Hence the table is in 1NF.

10. Let R(A,B,C,D,E,P,G) be a relational schema in which the following FDs are known to hold:

AB->CD
DE->P
C->E
P->C
B->G

The relation schema R is
a) in BCNF
b) in 3NF, but not in BCNF
c) in 2NF, but not in 3NF
d) not in 2NF
View Answer

Answer: d
Explanation: From the closure set of attributes we can see that the key for the relation is AB. The FD B->G is a partial dependency, hence it is not in 2NF.

Sanfoundry Global Education & Learning Series – Database Management System.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.