Database Questions and Answers – 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
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
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
Answer: d
Explanation: This relation gives a relation without any loss in the values.
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}
4. 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
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
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 .
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
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
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 .
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
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
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
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.
