Database Management Systems | Set 10

Following questions have been asked in GATE CS 2005 exam.

1) Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ‘select A,B,C from r’ and r2 = ‘select A, D from r’. Let s = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?
(a) s is subset of r
(b) r U s = r
(c) r is a subset of s
(d) r * s = s

Answer (c)
Consider the following example with lossy decomposition of r into r1 and r2. We can see that r is a subset of s.

Table r
 A      B      C      D
 1     10     100    1000    
 1     20     200    1000    
 1     20     200    1001 

Table r1
 A      B      C
 1     10     100 
 1     20     200 

Table r2
 A     D  
 1    1000  
 1    1001

Table s (natural join of r1 and r2)
 A      B      C      D
 1     10     100    1000    
 1     20     200    1000    
 1     10     100    1001 
 1     20     200    1001 

2) Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
(a) 2
(b) 3
(c) 4
(d) 5

Answer (b)
See for explanation.

3) Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–> D, E–>C, D–>A}. What are the candidate keys of R?
(a) AE, BE
(b) AE, BE, DE

Answer (d)
A set of attributes S is candidate key of relation R if the closure of S is all attributes of R and there is no subset of S whose closure is all attributes of R.
Closure of AEH, i.e. AEH+ = {ABCDEH}
Closure of BEH, i.e. BEH+ = {ABCDEH}
Closure of DEH, i.e. DEH+ = {ABCDEH}

Please see GATE Corner for all previous year paper/solutions/explanations, syllabus, important dates, notes, etc.

Please write comments if you find any of the answers/explanations incorrect, or you want to share more information about the topics discussed above.

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

My Personal Notes arrow_drop_up
Article Tags :
Practice Tags :

Be the First to upvote.

Please write to us at to report any issue with the above content.