Normalization quiz with answers

Normalization quiz, Normal form quiz in DBMS

1. We would say that {A₁, A₂, …, A_n}⁺ is the set of all attributes of R if and only if (A₁, A₂, …, A_n) is a ____________ for R.

[a] Primary key                     [b] Candidate key

[c] Super key                          [d] All of the above

Answer: [d] All of the above

If the closure of set of attributes includes all attributes then we would say that the set of attributes in question forms the key.

2. Consider a relation R(A, B, C, D, E) with FDs {A → BC, CD → E, B → D, E → A}. Are the relations R₁(A, B, C) and R₂(A, D, E) resulted in a lossless join decomposition of R?

Answer: YES

A decomposition of R into R₁ and R₂ is said to be lossless join decomposition if either of the following two conditions hold;

R₁ ⋂ R₂ → R₁

R₁ ⋂ R₂ → R₂

As per the given question, (ABC) ⋂ (ADE) = A. A is the candidate key for R1 from the FD A → BC, hence the first condition holds. So, the decomposition is lossless join decomposition.

3. Consider a relation R(A, B, C, D, E) with FDs {A → BC, CD → E, B → D, E → A}. Are the relations R₁(A, B, C) and R₂(C, D, E) resulted in a lossless join decomposition of R?

Answer: NO

Refer answer 2 above for rules

As per the given question, (ABC) ⋂ (CDE) = C. From the given FDs it is clear that C alone does not uniquely identify any attributes of R. C is not a candidate key for either of the relations. So, the decomposition is not lossless join decomposition.

4. Let us suppose that relations R and S have r and s number of tuples (records) respectively. What are the minimum and maximum numbers of tuples that R ⋈ S have?

[a] 0, rs                                   [b] 1, rs

[c] r-s, rs                                 [d] r-s, r+s

Answer: [a] 0, rs

The operation R Natural Join S results in any tuple if at least one record of R and one record of S satisfy the join condition.

Minimum – If no records satisfy the join condition, the result will be 0 (Zero).

Maximum – If all the records of R can be joined with all the records of S, then the result will be r*s.

5. Assume a relation R(A, B, C) with the records (1, 2, 3), (4, 2, 3), (5, 3, 3), (5, 3, 4). Which of the following FDs is/are true?

[a] A → B                               [b] BC → A

[c] B → C                                [d] None of the above.

Answer: [a] A → B

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

Go back to DBMS quizzes / Multiple choice questions page

Simple solution for finding the key of a relation

Find the key of a relation, How to find the candidate key of a given table. Given the set of functional dependencies, how to find the key of a relational table.

Question:

Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F as follows;

F = { AB → C, BD → EF, AD → GH, A → I, H → J}.

Find the key of relation R.

Solution:

From the F it is very clear that the attributes A, B and D are the only attributes on LHS of any FDs. That is, none of these attributes are found in the right hand side (RHS) of any of the given FDs. Hence, we can start with finding the closure of (ABD).

LHS	Result	Description
(ABD)+	= ABCD from AB → C (By reflexivity) = ABCDGH from AD → GH. (By reflexivity) = ABCDGHI from A → I. (By reflexivity) = ABCDGHIJ from H → J. (By transitivity) = ABCDEFGHIJ from BD → EF. (By reflexivity)	(ABD)+ = R. Hence, (ABD) is the candidate key.

The proper subset of (ABD) is (AB), (AD), (BD), (A), (B), (D). None of the elements in the proper subset of (ABD) forms a candidate key. Hence, we declare that the key for R is (ABD).

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

Go to previous page
 
Go to Normalization - Solved Exercises page

Go to - 1NF,    2NF,    3NF,    BCNF

Go to How to find closure? page

Calculate the key for relation R in DBMS

Find the candidate keys of a relation, How to find the candidate keys, Which is the key for the given table, concept of candidate key in dbms, candidate key examples

Question:

Consider the relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F as follows;

F = { AB → C, BD → EF, AD → GH, A → I, H → J}.

Find the key of relation R.

Solution:

We can find keys (candidate keys) for a relation by finding the closure of an/set of attributes. Checking each attribute or all subsets of the given set of attributes for a key is time consuming job. Hence, we may employ some of the following heuristics/assumptions in identifying the keys;

• We may start checking all the left hand side attributes of any/all of the given set of functional dependencies. We can start with single LHS attributes.

• If we find the closure of an attribute and that attribute is the candidate key then any superset cannot be the candidate key. For example, if A is a candidate key, then AB is not a candidate key but a super key.

LHS	Result	Description
A+	= AI from the functional dependency A → I (By reflexivity rule) No more functional dependencies that has either of the attributes AI in LHS. Hence, A+ = AI.	A+ ≠ R. So, A is not a candidate key.
H+	= HJ from H → J (By reflexivity rule) No more functional dependencies that has either of the attributes HJ in LHS. Hence, H+ = HJ. Note: We need not find H+ since H is available on RHS in AD → GH.	H+ ≠ R. So, H cannot be a candidate key.
(AB)+	= ABC from AB → C = ABCI from A → I No more functional dependencies that has either of the attributes ABCI in LHS. Hence, (AB)+ = ABCI.	(AB)+ ≠ R. So, (AB) is not a candidate key.
(AD)+	= ADGH from AD → GH (By reflexivity rule) = ADGHI from A → I (By union rule. AD → GH and A → I, so AD → GHI) = ADGHIJ from H → J No more functional dependencies that has either of the attributes ADGHIJ in LHS. Hence, (AD)+ = ADGHIJ.	(AD)+ ≠ R. So, (AD) is not a candidate key.
(BD)+	= BDEF from BD → EF No more functional dependencies that has either of the attributes BDEF in LHS. Hence, (BD)+ = BDEF.	(BD)+ ≠ R. So, (BD) is not a candidate key.
(ABD)+	= ABDGH (By reflexivity and augmentation. That is, AD → GH and if we augment B on both sides we get ABD → BGH. Hence, ABDGH.) = ABDGHIJ from (AD)+ - refer above. = ABCDGHIJ from AB → C = ABCDEFGHIJ from BD → EF.	(ABD)+ = R. Hence, (ABD) is the candidate key.

Hint: The left hand side only attribute will definitely be the key or part of the key.

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

Go to Normalization - Solved Exercises page

Go to - 1NF,    2NF,    3NF,    BCNF

Go to How to find closure? page

Find the candidate keys of a database relation

How to find the candidate keys of a relation if functional dependencies given