GATE Questions & Answers of Databases Computer Science and Information Technology

Question No. 21

In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2. Which one of the following is true about R?

Question No. 22

Consider the following two tables and four queries in SQL.

Book (isbn, bname), Stock (isbn, copies)
Query 1:       SELECT B.isbn, S.copies
                      FROM Book B INNER JOIN Stock S
                      ON B.isbn = S.isbn;
 
Query 2:        SELECT B.isbn, S.copies
                      FROM Book B LEFT OUTER JOIN Stock S
                      ON B.isbn = S.isbn;
 
Query 3:        SELECT B.isbn, S.copies
                      FROM Book B RIGHT OUTER JOIN Stock S
                      ON B.isbn = S.isbn;
 
Query 4:        SELECT B.isbn, S.copies
                      FROM Book B FULL OUTER JOIN Stock S
                      ON B.isbn = S.isbn;
Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

Question No. 51

Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query
$\style{font-family:'Times New Roman'}{\mathrm Q:\;r\;\bowtie\left(\sigma_{B<5}\left(S\right)\right)}$
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.
 
Which one of the following queries is NOT equivalent to Q?

Question No. 52

Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.
Schema I: Registration (rollno, courses)
            Field ‘courses’ is a set-valued attribute containing the set of courses a student has
            registered for.
            Non-trivial functional dependency:
             rollno → courses
 
Schema II: Registration (rollno, courseid, email)
            Non-trivial functional dependencies:
             rollno, courseid → email
             email → rollno
 
Schema III: Registration (rollno, courseid, marks, grade)
            Non-trivial functional dependencies:
             rollno, courseid → marks, grade
             marks → grade
 
Schema IV: Registration (rollno, courseid, credit)
            Non-trivial functional dependencies:
             rollno, courseid → credit
             courseid → credit

Which one of the relational schemas above is in 3NF but not in BCNF?

Question No. 16

The following functional dependencies hold true for the relational schema R {V, W, X, Y, Z}:

                                                                                $\style{font-family:'Times New Roman'}{\begin{array}{l}V\rightarrow W\\VW\rightarrow X\\Y\rightarrow VX\\Y\rightarrow Z\end{array}}$

Which of the following  is irreducible equivalent  for this  set of functional dependencies ?

Question No. 23

Consider a database that has the relation schema EMP (EmpID, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are below.

EMP
EmpId EmpName DeeptName
1 XYA AA
2 XYB AA
3 XYC AA
4 XYD AA
5 XYE AB
6 XYF AB
7 XYG AB
8 XYH AC
9 XYI AC
10 XYJ AC
11 XYK AD
12 XYL AD
13 XYM AE

 

SELECT AVG(EC.Num)
FROM EC
WHERE(DeptName,Num) IN
       (SELECT DeptName,COUNT(EmpId) AS
                                    EC(DeptName,Num)
       FROM EMP
         GROUP BY DeptName)

 

The output of executing the SQL query is_______________.

Question No. 41

Consider  a database that has the relation schems EMP (Empld, EmpName, DeptId), and DEPT (DeptName, DeptId). Note that the DeptId can be permited to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

$\style{font-family:'Times New Roman'}{\begin{array}{l}(\mathrm I)\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\forall\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack\neq\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\\(\mathrm{II})\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\exists\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack\neq\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\\(\mathrm{III})\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\exists\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack=\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\end{array}}$ 

Which of the above queries are safe?

Question No. 42

In a database system, unique timestamps are assigned to each transaction using Lamport's logical clock. Let TS(T1) and TS(T2) be the timestamps of transaction T1 and T2 respectively. Besides T1 holds a lock on the resource R, and T2 has requested a conflicting lock on the same resource R. The following algorithm is used to prevent deadlocks in the database system assuming that a killed transaction is restarted with the same timestamp.

if TS(T2)<TS(T1)then

T1 is killed

else T2 waits.

Assume any transaction that is not killed terminates eventually.Which of the following is TRUE about the database system that uses the above algorithm to prevent deadlock?

Question No. 46

Consider a database that has the relation schema CR (StudentName, CourseName).An instance of the schema CR is as given below.

CR
StudentName CourseName
SA CA
SA CB
SA CC
SB CB
SB CC
SC CA
SC CB
SC CC
SD CA
SD CB
SD CC
SD CD
SE CD
SE CA
SE CB
SF CA
SF CB
SF CC

The following query is made on the database.

$\style{font-family:'Times New Roman'}{\begin{array}{l}T1\leftarrow{\mathrm\pi}_{CourseName}\;({\mathrm\sigma}_{StudentName\mathit=\mathit'SA\mathit'}(\mathrm{CR}))\\T2\leftarrow CR\;\div T1\end{array}}$

The number of rows in T2 is__________

Question No. 117

An ER model of a database consists of entity types A and B. These are connected by a relationship R which dose not have its own attribute. Under which one of the following condiditons, can the relational table for R be merged with that of A?

Question No. 119

Consiider the following tables T1 and T2

T1
P Q
2 2
3 8
7 3
5 8
6 9
8 5
9 8
T2
R S
2 2
8 3
3 2
9 7
5 7
7 2

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascabe and on-update cascade. In table T2, R is the primary key and S is the forign key refrencing P in table T1 with on-delete set NULL and ON-update cascade. In order to delete record $ \left\langle3,8\right\rangle $ from table T1, the number of additional records that need to be deleted from table T1 is _____________________.

Question No. 144

Two transaction T1 and T2 are given as

T1 : r1(X)w1(X)r1(Y)w1(Y)

T2 : r2(Y)w2(Y)r2(Z)w2(Z)

Where ri (V) denotes a read operation by transaction Ti on a variable V and wi(V) denotes a write operation by transaction Ti on a variable V. The total number of conflict serializable schedules that can be formed by T1 and T2 is ___________.

Question No. 146

Consider the following database table named top_scorer

top_scorer
player country goals
Klose  Germany  16 
Ronaldo  Brazil  15
G Miiller Germany  14
Fontaine  France  13
Pele  Brazil  12
Klinsmam Germany  11
Kocsis  Hungary  11
Batistuta  Argentina  10
Cubillas  Peru  10
Lato  Poland  10
Lineker  England  10
T Mulle Germany  10
Rahn  Germany  10

Consider the following SQL query:

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals >ALL (SELECT tb.goals
            FROM top_scorer AS tb
            WHERE tb.country = 'Spain')
AND ta.goals >ANY (SELECT tc.goals
            FROM top_scorer AS tc
            WHERE tc.country = 'Germany')
 
The number of tuples returned by the above SQL query is _________.

Question No. 149

In a B+ tree, if the search-key value is 8 bytes long, the block size is 512 bytes and the block pointer size is 2 bytes, then the maximum order of the B+ tree is ___________.

Question No. 31

Which of the following is NOT a superkey in a relational schema with attributes $V,\;W,\;X,\;Y,\;Z$ and primary key $ V\;Y $?

Question No. 32

Which one of the following is NOT a part of the ACID properties of database transactions?

Question No. 33

A database of research articles in a journal uses the following schema.
     (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)
The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.
     (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE
     (VOLUME, NUMBER) → YEAR
     (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE
The database is redesigned to use the following schemas.
   (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
   (VOLUME, NUMBER, YEAR)
Which is the weakest normal form that the new database satisfies, but the old one does not?

Question No. 61

Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects $ \left\{O_1,\;...\;O_k\right\} $. This is done in the following manner:

Step1. T acquires exclusive locks to $ O_1,\;...\;O_k $ in increasing order of their addresses.

Step2. The required operations are performed.

Step3. All locks are released.

This protocol will

Question No. 131

B+ Trees are considered BALANCED because

Question No. 132

Suppose a database schedule $S$ involves transactions $T_1,. . . , T_n$. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If $S$ is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?

Question No. 161

Consider the following database schedule with two transactions, $T1$ and $T2$.

                                           $ S=r_2\left(X\right);\;r_1\left(X\right);\;r_2\left(Y\right);\;w_1\left(X\right);\;r_1\left(Y\right);\;w_2\left(X\right);\;a_1;\;a_2 $

where $r_i$(Z) denotes a read operation by transaction $T_i$ on a variable Z, $w_i$(Z) denotes a write operation by $T_i$ on a variable Z and $a_i$ denotes an abort by transaction $T_i$.

Which one of the following statements about the above schedule is TRUE?

Question No. 162

Consider the following database table named water_schemes :

 water_schemes
scheme_no district_name capacity
1 Ajmer 20
1 Bikaner 10
2 Bikaner 10
3 Bikaner 20
1 Churu 10
2 Churu 20
1 Dungargarh 10
The number of tuples returned by the following SQL query is ________ .

 

with total(name, capacity) as

 

     select district_name, sum(capacity)
     from water_schemes
     group by district_name
with total_avg(capacity) as
     select avg(capacity)
     from total
select name
     from total, total_avg
     where total.capacity $ \style{font-family:'Courier New'}\geq $ total_avg.capacity

Question No. 31

SELECT operation in SQL is equivalent to

Question No. 32

A file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index. Then that index is called

Question No. 48

Consider an Entity-Relationship(ER) model in which entity sets E1 and E2 are connected by an m:n relationship R12. E1 and E3 are connected by a 1: n (1 on the side of E1 and n on the side of E3) relationship R13.

E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 of which is a21 the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes.

If a relational model is the derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is _______.

Question No. 61

Consider the following relations:

Student   Performance
Roll_No Student _Name           Roll_No Course Marks
1 Raj   1 Math 80
2 Rohit   1 English 70
3 Raj   2 Math 75
      3 English 80
      2 Physics 65
      3 Math 80

Consider the following SQL query.

SELECT S. Student_Name, sum (P.Marks)
FROM Student S, Performance P
WHERE S.Roll_No = P.Roll_No
GROUP BY S.Student_Name 

The number of rows that will be returned by the SQL query is _______.

Question No. 131

Consider the following transaction involving two bank accounts x and y.
read(x); x : = x–50; write(x); read(y); y:=y+50; write(y)
The constraint that the sum of the accounts x and y should remain constant is that of

Question No. 132

With reference to the B+ tree index of order 1 shown below, the minimum number of nodes (including the Root node) that must be fetched in order to satisfy the following query: “Get all records a search key greater than or equal to 7 and less than 15” is ____.

Question No. 139

Consider a simple checkpointing protocol and the following set of operations in the log.
(start, T4); (write, T4, y, 2, 3); (start, T1); (commit, T4); (write, T1, z, 5, 7);
(checkpoint);
(start, T2); (write, T2, x, 1, 9); (commit, T2); (start, T3), (write, T3, z, 7, 2);
If a crash happens now the system tries to recover using both undo and redo operations, what are the contents of the undo list and the redo list?

Question No. 140

Consider two relations R1(A,B) with the tuples(1, 5), (3, 7) and R2(A, C) = (1, 7), (4, 9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. Consider the following tuples of the form (A, B, C): a =(1, 5, null), b =(1, null, 7), c = (3, null, 9), d = (4, 7, null), e = (1, 5, 7), f = (3, 7, null), g = (4, null, 9). Which one of the following statements is correct?

Question No. 213

Consider the relation X(P, Q, R, S, T, U) with the following set of functional dependencies

F = {
          {P, R} → {S, T},
          {P, S, U} → {Q, R}
      }

Which of the following is the trivial functional dependency in F+, where F+ is closure of F?

Question No. 229

Consider the following relation
        Cinema (theater, address, capacity)
Which of the following options will be needed at the end of the SQL query
        SELECT P1. address
        FROM Cinema P1
Such that it always finds the addresses of theaters with maximum capacity?

Question No. 239

Consider a B+ tree in which the search key is 12 bytes long, block size is 1024 bytes, record pointer is 10 bytes long and block pointer is 8 bytes long. The maximum number of keys that can be accommodated in each non – leaf node of the tree is ______.

Question No. 251

Consider the following partial Schedule S involving two transactions T1and T2. Only the read and the write operations have been shown. The read operation on data item P is denoted by read (P) and the write operation on data item P is denoted by write (P).

Time
instance

 Transaction-id
T1 T2
1 read(A)  
2 write(A)  
3   read(C)
4   write(C)
5   read(B)
6   write(B)
7   read(A)
8   commit
9 read(B)  
Schedule S

Suppose that the transaction T1 fails immediately after time instance 9. Which one of the following statements is correct?

Question No. 31

Consider the relation scheme R = (E,F, G, H, I, J, K, L, M, N) and the set of functional dependencies {{E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, {K} → {M}, {L} → {N}} on R. What is the key for R ?

Question No. 32

Given the following statements:

S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL.

S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.
CREATE TABLE S (

      a INTEGER,
      d INTEGER,
      e INTEGER,
      PRIMARY KEY (d),
      FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?

Question No. 39

Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item x, denoted by r(x) and w(x) respectively. Which one of them is conflict serializable?

Question No. 40

Given the following two statements:
S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF.
S2: AB→C, D→E, E→C is a minimal cover for the set of functional dependencies AB→C, D→E, AB→E, E→C.
Which one of the following is CORRECT?

Question No. 64

Given the following schema:
                 employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
                 departments(dept-id, dept-name, manager-id, location-id)
You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:
                 SQL>SELECT last-name, hire-date
                                FROM employees
                                WHERE (dept-id, hire-date) IN
                                (SELECT dept-id, MAX(hire-date)
                                FROM employees JOIN departments USING(dept-id)
                                WHERE location-id = 1700
                                GROUP BY dept-id);

What is the outcome?

Question No. 131

The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is _____.

Question No. 132

Given an instance of the STUDENTS relation as shown below:

StudentID        StudentName        StudentEmail         StudentAge          CPI       
2345    Shankar      shankar@math X 9.4
1287    swati      swati@ee 19 9.5
7853    shankar      shankar@cse 19 9.4
9876    swati      swati@mech 18 9.3
8765    ganesh    ganesh@civil 19 8.7

For (StudentName,StudentAge) to be a key for this instance,the value X should NOT be equal to ________.

Question No. 139

Consider the following schedule S of transactions T1, T2, T3, T4:

T1 T2 T3 T4

 

 

 

 

Writes(X)
Commit

Reads(X)

 

 

 

 

Writes(Y)
Reads(Z)
Commit

 

 

Writes(X)
Commit

 

 

 

 

 

 

 

 

Reads(X)
Reads(Y)
Commit

Which one of the following statements is CORRECT?

Question No. 140

Consider a join (relation algebra) between relations r(R) and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size (r(R))<size(s(S)), the join will have fewer number of disk block accesses if

Question No. 164

SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below:

select * from R where a in (select S.a from S)

Question No. 231

What is the optimized version of the relation algebra expression πA1πA2σF1σF2r, where A1, A2 are sets of attributes in r with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?

Question No. 232

A prime attribute of a relation scheme R is an attribute that appears

Question No. 239

Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below.

T1: r1(X); r1(Z); w1(X); w1(Z)
T2: r2(Y); r2(Z); w2(Z)
T3: r3(Y); r3(X); w3(Y)
S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)
S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)

Which one of the following statements about the schedules is TRUE?

Question No. 240

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)
Consider the following relational algebra query:

$\style{font-family:'Courier New'}{{\mathrm\pi}_\mathrm{empId}\left(\mathrm{employee}\right)-{\mathrm\pi}_\mathrm{empId}\left(\mathrm{employee}\bowtie_{\left(\mathrm{empId}=\mathrm{eID}\right)\wedge\left(\mathrm{empAge}\leq\mathrm{depAge}\right)}\mathrm{Dependent}\right)}$

The above query evaluates to the set of empIds of employees whose age is greater than that of

Question No. 264

Consider the following relational schema:

employee(empId,empName,empDept)
customer(custId,custName,salesRepId,rating)
salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
FROM employee E
WHERE NOT EXISTS (SELECT custId
                  FROM customer C
                  WHERE C.salesRepId = E.empId
                  AND C.rating <> ’GOOD’);

Question No. 15

An index is clustered, if

Question No. 35

Consider the following relational schema.
Students(rollno: integer, sname: string)
Courses(courseno: integer, cname: string)
Registration(rollno: integer, courseno: integer, percent: real)
Which of the following queries are equivalent to this query in English?
“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname
FROM Students as S, Registration as R
WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90

(II) $\prod_\mathrm{sname}({\mathrm\sigma}_{\mathrm{courseno}=107\;\wedge\;\mathrm{percent}>90}(\mathrm{Registration}\;\bowtie\;\mathrm{Students})$

(III) {T | ∃S∈ Students, ∃ R∈ Registration ( S.rollno=R.rollno ∧ R.courseno=107 ∧ R.percent>90 ∧T.sname=S.sname)}

(IV) {<SN> | ∃SR ∃RP ( <SR, SN>∈ Students ∧ <SR, 107, RP>∈ Registration ∧ RP>90)}

Question No. 54

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.
How many candidate keys does the relation R have?

Question No. 55

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.

The relation R is

Question No. 2

Which of the following is TRUE?

Question No. 14

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

Question No. 15

Which of the following statements are TRUE about an SQL query?

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause
Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

Question No. 27

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

Question No. 43

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

Question No. 50

Consider the following relations A, B and C:

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A∪B is the same as that of A.

$\style{font-family:Verdana}{\left(\mathrm A\cup\mathrm B\right)\bowtie_{\mathrm A.\mathrm{Id}>40\vee\mathrm C.\mathrm{Id}<15}\mathrm C}$

Question No. 51

Consider the following relations A, B and C:

How many tuples does the result of the following SQL query contain?

SELECT A.Id
FROM A
WHERE A.Age > ALL (SELECT B.Age
                   FROM B
                   WHERE B.Name = ‘Arun’)

Question No. 12

Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number of each registered student

2. UID: Unique identity number, unique at the national level for each citizen

3. BdnkAccount_Num: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attribute stores the primary account number.

4. Name: Name of the student

 5. Hostel_Room: Room number of the hostel

Which of the following options is INCORRECT?

Question No. 32

Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table.

Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change

What will be the output of the following SQL query after the steps mentioned above are carried out?

SELECT Y FROM T WHERE X=7;

Question No. 39

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.

Q1: π A1......ApσAp=cr  where c is a constant

Q2: π A1......Apσ c1≤Ap≤c2r where c1 and c2 are constants

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

Question No. 46

Database table by name Loan_Records is given below.

Borrower Bank_Manager Loan_Amount
Ramesh Sunderajan 10000.00
Suresh Ramgopal 5000.00
Mahesh Sunderajan 7000.00

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
);

Question No. 18

Consider a B+-tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any non-root node?

Question No. 19

A relational schema for a train reservation database is given below

Passenger (pid, pname, age)
Reservation (pid, cass, tid)

Table: Passenger
pid pname Age
0 ‘Sachin’ 65
1 ‘Rahul’ 66
2 ‘Sourav’ 67
3 ‘Anil’ 69
Table: Reservation
Pid Class Tid
0 ‘AC’ 8200
1 ‘AC’ 8201
2 ‘SC’ 8201
5 ‘AC’ 8203
1 ‘SC’ 8204
3 ‘AC’ 8202

What pids are returned by the following SQL query for the above instance of the tables?

SELECT pid
FROM Reservation
WHERE class = 'AC' AND
      EXISTS (SELECT *
              FROM Passenger
              WHERE age 65 AND
              Passenger.pid Reservation.pid)

Question No. 20

Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock?

I. 2-phase locking
II. Time-stamp ordering

Question No. 42

Consider the following schedule for transactions T1, T2 and T3:

T1 T2 T3
Read (X)    
  Read (Y)  
    Read (Y)
  Write (Y)  
Write (X)    
    Write (X)
  Read (X)  
  Write (X)  

Which one of the schedules below is the correct serialization of the above?