Wednesday, 10 January 2018

Normalization quiz with answers

Normalization quiz, Normal form quiz in DBMS




1. We would say that {A1, A2, …, An}+ is the set of all attributes of R if and only if (A1, A2, …, An) 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 R1(A, B, C) and R2(A, D, E) resulted in a lossless join decomposition of R?

Answer: YES
A decomposition of R into R1 and R2 is said to be lossless join decomposition if either of the following two conditions hold;

R1 R2 → R1
R1 R2 → R2
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 R1(A, B, C) and R2(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







SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...