DBMS GATE CS PYQ QUIZ

Consider the following relations A, B, C. How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A U B is the same as that of A. 

Table A
Id   Name    Age
----------------
12   Arun    60
15   Shreya  24
99   Rohit   11

Table B
Id   Name   Age
----------------
15   Shreya  24
25   Hari    40
98   Rohit   20
99   Rohit   11

Table C
Id   Phone  Area
-----------------
10   2200   02  
99   2100   01

Suppose (

A

, B) and (

C

,D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in r2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

Table A:

Table B:

Table C:

Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains?

SELECT A.id 
FROM   A 
WHERE  A.age > ALL (SELECT B.age 
                    FROM   B 
                    WHERE  B. name = "arun") 

Consider the following transactions with data items P and Q initialized to zero:

T1: read (P) ;
    read (Q) ;
    if P = 0 then Q : = Q + 1 ;
    write (Q) ;
T2: read (Q) ;
    read (P) ;
    if Q = 0 then P : = P + 1 ;
    write (P) ;

Any non-serial interleaving of T1 and T2 for concurrent execution leads to

Given the basic ER and relational models, which of the following is INCORRECT?

Consider a database implemented using B+ tree for file indexing and installed on a disk drive with block size of 4 KB. The size of search key is 12 bytes and the size of tree/disk pointer is 8 bytes. Assume that the database has one million records. Also assume that no node of the B+ tree and no records are present initially in main memory. Consider that each record fits into one disk block.

The minimum number of disk accesses required to retrieve any record in the database is ___________ .



Note - This question was Numerical Type.

For the relation R(ABCDEFGH) with FD's= {CH->G, A->BC, B->CHF, E->A, F->EG such that F+ is exactly the set of FDs that hold for R.} Consider the FDs given in above question. The relation R is

Consider a relational table r with sufficient number of records, having attributes A1, A2,…, An and let 1 <= p <= n. Two queries Q1 and Q2 are given below.



The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the following statements is TRUE?

Database table by name Loan_Records is given below.

What is the output of the following SQL query?

SELECT Count(*) 
FROM  ( ( SELECT Borrower, Bank_Manager 
          FROM Loan_Records) AS S 
          NATURAL JOIN ( SELECT Bank_Manager, Loan_Amount 
                         FROM Loan_Records) AS T );

A table T(X, Y) initially has one record (1, 1). New records are inserted 128 times with:

X = MX + 1, Y = 2*MY + 1

where MX and MY are the current maximums of X and Y.What will MAX(X) and MAX(Y) be after all insertions?

SELECT Y FROM T WHERE X=7;