DBMS |Gate-2008| Previous Year Questions| Set-13

Set-15 GATE-2006 DBMS

DBMS |Gate-2008| Database Management System

  1. A clustering index is defined on the fields which are of type : [GATE – 2008]

a. non-key and ordering
b. non-key and non-ordering
c. key and ordering
d. key and non-ordering

Answer : a)


  1. A B-tree of order 4 is built from scratch by 10 successive insertions. What is the maximum number of node splitting operations that may take place? [GATE – 2008]

a. 3
b. 4
c. 5
d. 6

Answer : c)


  1. Consider the following relational schemes for a library database: Book(Title, Author, Catalog_ no, Publisher, Year, Pr ice) Collection (Title, Author, Catalog_ no) with in the following functional dependencies: I.Title Author ® Catalog_no II.Catalog_no ® Title Author Publisher Year III.Publisher Title Year ® Pr ice Assume {Author, Title} is the key for both schemes. Which of the following statements is true?  [GATE – 2008]

a. Both Book and Collection are in BCNF
b. Both Book and Collection are in 3NF only
c. Both Book and Collection are in 2NF only
d. Book is in 2NF and Collection is in 3NF

Answer : d)


  1. Consider a file of 16384 records. Each record is 32 bytes long and its key field is of size 6 bytes. The file is ordered on a non-key field, and the file organization is unspanned. The file is stored in a file system with block size 1024 bytes, and the size of a block pointer is 10 bytes. If the secondary index is built on the key field of the file, and a multi-level index scheme is used to store the secondary index, the number of first-level and second-level blocks in the multi-level index are respectively : [GATE – 2008]

a. 8 and 0
b. 128 and 6
c. 256 and 4
d. 512 and 5

Answer : c)


  1. Consider the following ER diagram :

The minimum number of tables needed to represent M, N, P, R1, R2 is : [GATE – 2008]

a. 2
b. 3
c. 4
d. 5

Answer : b)


6. Let R (A, B, C, D) be a relational schema with the following functional dependencies: [GATE – 2008]

A → B, B → C,
C → D and D → B. 

The decomposition of R into 
(A, B), (B, C), (B, D)

a. gives a lossless join, and is dependency preserving
b. gives a lossless join, but is not dependency preserving
c. does not give a lossless join, but is dependency preserving
d. does not give a lossless join and is not dependency preserving

Answer : a)


  1. Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is : [GATE – 2008]

a. in BCNF
b. in 3NF, but not in BCNF
c. in 2NF, but not in 3NF
d. not in 2NF

Answer : d)


8. Consider the following three schedules of transactions T1, T2 and T3. [Notation: In the following NYO represents the action Y (R for read, W for write) performed by transac­tion N on object O.]

(S1)     2RA     2WA     3RC     2WB     3WA     3WC     1RA     1RB     1WA     1WB
(S2)     3RC     2RA     2WA     2WB     3WA     1RA     1RB     1WA     1WB     3WC
(S3)     2RA     3RC     3WA     2WA     2WB     3WC     1RA     1RB     1WA     1WB

Which of the following statements is TRUE?  [GATE – 2008]

a. S1, S2 and S3 are all conflict equivalent to each other
b. No two of S1, S2 and S3 are conflict equivalent to each other
c. S2 is conflict equivalent to S3, but not to S1
d. S1 is conflict equivalent to S2, but not to S3

Answer : d)


9. Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name, sch-address, sch-phone) Enrolment(school-id sch-roll-no, erollno, examname) ExamResult(erollno, examname, marks) What does the following SQL query output? [GATE – 2008]

SELECT  sch-name, COUNT (*)
FROM    School C, Enrolment E, ExamResult R
WHERE   E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id
 /* Add code here. Remove these lines if not writing code */

a. for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students
b. nothing; the query has a syntax error
c. for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one
exam
d. for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students

Answer : b)
DBMS |Gate-2008|


10. Student (school-id, sch-roll-no, sname, saddress) School (school-id, sch-name, sch-address, sch-phone) Enrolment(school-id sch-roll-no, erollno, examname) ExamResult(erollno, examname, marks) What does the following SQL query output?[GATE – 2008]

SELECT  sch-name, COUNT (*)
FROM    School C, Enrolment E, ExamResult R
WHERE   E.school-id = C.school-id
AND
E.examname = R.examname AND E.erollno = R.erollno
AND
R.marks = 100 AND S.school-id IN (SELECT school-id
                                FROM student
                                GROUP BY school-id
                                 HAVING COUNT (*) > 200)
GROUP By school-id
 /* Add code here. Remove these lines if not writing 

a. schools with more than 35% of its students enrolled in some exam or the other
b. The empty set
c. schools with a pass percentage above 35% over all exams taken together
d. schools with a pass percentage above 35% over each exam

Answer : a)
DBMS |Gate-2008|


Back to GATE-HOME


Spread the love

Leave a Comment

Your email address will not be published. Required fields are marked *