December 2010 – Question No 17
The dependency preservation decomposition is a property to decompose database schema D, in which each functional dependency X → Y specified in F,
(A) appeared directly in one of the relation schemas Ri in the decomposed D.
(B) could be inferred from dependencies that appear in some Ri.
(C) both (A) and (B)
(D) None of these
Explanation:- The question itself requires a bit of explanation. It is not enough if you just know what is the right answer but you must also know why it is the right answer. The explanation would be a bit lengthy. Let us first dissect the question and explain some terms in terms of DBMS.
Decomposition – This means replacing a relation with a collection of smaller relations.
Relation – Relation is known as Table.
Relation Schema – This is known as Table definition. Relation Schema for a “student” relation can be shown in the following way:
Definition of Dependency preservation decomposition:–
Each FD specified in F either appears directly in one of the relations in the decomposition, or be inferred from FDs that appear in some relation.
Let us consider an example for Dependency preservation
Let R be a relation R(A B C D)
Let there be 3 functional dependencies.
Let the relation R be decomposed into two more relations.
R1(A B C) : R2(C D)
Let us first consider the relation R1(A B C). Here between A and B the functional dependency FD1 is preserved. Between B and C, FD2 is preserved.
Let us now consider the second relation R2(C D). Between C and D the FD, FD3 is preserved. So in the two relations R1 and R2, all the 3 functional dependencies are preserved.
Let us consider an example for Non-dependency preservation
Let R be a relation R(A B C D) Let there be again 3 functional dependencies.
Let the relation be decomposed into two more relations>
R1(A C D) R2(B C)
Let us first consider the relation R1(A C D). There is no FD between A and C. There is a FD3 between C and D.
Now let us consider the second relation R2(B C). There is FD2 between B and C.
So, the two relations only support only FD’s FD2 and FD3. FD1 is not supported. So these relations does not preserve dependency.
Generally there are three desirable properties of a decomposition.
- Dependency preservation
- Minimal redundancy
The above question was based on dependency preservation decomposition. This example has been taken from the dependency preservation presentation by Jason Allen. The explanation is quite good there.
The dependency preservation decomposition is a property to be considered for decomposing a relation into two or more smaller relations. The functional dependency X->Y specified in F can appear directly in one of the relation schemas Ri in the decomposed D or it could be inferred from dependencies that appear in some Ri. So the answer for this question is C.
In DBMS, there could be a question on different steps in normalization and what is achieved at the end of every step in it.You need to be knowing the following things very clearly and without any ambiguity.The question would be based on these terms.
A table is in 1NF if there and no duplicate rows in the table. Each cell is single-valued.
A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key. A table is in 2NF if it is in 1NF and if it has no partial dependencies
A table is in 3NF if it is in 2NF and if it has no transitive dependencies
A table is in BCNF if it is in 3NF and if every determinant is a candiate key.
A table is in 4NF if it is in BCNF and it it has no multi-valued dependencies.
A table is in 5NF if it is in 4NF and it has no join dependency.
Superkey,Candidate key,Primary key
A superkey is any set of attributes such that the values of the attributes(taken together)uniquely identify one entity in the entity set.
A candidate key is a minimal superkey.
A primary key is one of the candidate keys, designated by the Database designer.