DBMS Gate Questions

Here, We will see DBMS GATE Questions from previous year’s papers and the syllabus of DBMS for GATE Exam.

1. DBMS in Gate CSE Exam

The Databases section in the GATE Computer Science and Information Technology (CS/IT) exam, evaluates a candidate’s understanding of database management systems (DBMS), focusing on design, implementation, and optimization. This subject tests theoretical knowledge and practical problem-solving skills through questions on relational models, SQL, transactions, and more.

2. DBMS Syllabus

The syllabus for DBMS in GATE CSE includes:

1. Data Modeling: ER diagrams, relational algebra, and tuple calculus.
2. Relational Databases: Integrity constraints, SQL queries, and normalization (1NF to BCNF).
3. Storage and Indexing: File organization, B/B+ trees, and hashing techniques.
4. Query Processing & Optimization: Execution plans, cost estimation, and indexing strategies.
5. Transaction Management: ACID properties, concurrency control (locks, timestamps), and recovery techniques (log-based, checkpoints).

DBMS Gate Questions

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

1. r₁(x);r₂(x);w₁(x);r₃(x);w₂(x)
2. r₂(x);r₁(x);w₂(x);r₃(x);w₁(x)
3. r₃(x);r₂(x);r₁(x);w₂(x);w₁(x)
4. r₂(x);w₂(x);r₃(x);r₁(x);w₁(x)

2. Consider the following schedule S of transactions T₁,T₂,T₃,T₄

Which one of the following statements is correct?

1. S is conflict-serializable but not recoverable
2. S is not conflict-serializable but is recoverable
3. S is both conflict-serializable and recoverable
4. S is neither conflict-serializable nor it is recoverable

3. 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 yshould remain constant is that of

1. Atomicity
2. Consistency
3. Isolation
4. Durability

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

1. Undo: T₃,T₁; Redo: T₂
2. Undo: T₃,T₁; Redo: T₂,T₄
3. Undo: none; Redo: T₂,T₄,T₃,T₁
4. Undo: T₃,T₁,T₄; Redo: T₂

5. Consider the following partial schedule S involving two transactions T₁ and T₂. 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

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

1. T₂ must be aborted and then both T₁ and T₂ must be re-started to ensure transaction atomicity.
2. Schedule S is non-recoverable and cannot ensure transaction atomicity.
3. Only T₂ must be aborted and then re-started to ensure transaction atomicity.
4. Schedule S is recoverable and can ensure atomicity and nothing else needs to be done.

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

1. Atomicity
2. Consistency
3. Isolation
4. Deadlock – freedom

7. Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O₁, ….. , O_k). This is done in the following manner:
Step 1. T acquires exclusive locks to O₁, ….. , O_k in increasing order of their addresses.
Step 2. The required operations are performed.
Step 3. All locks are released.
This protocol will

1. guarantee serializability and deadlock-freedom.
2. guarantee neither serializability nor deadlockfreedom.
3. guarantee serializability but not deadlock-freedom.
4. guarantee deadlock-freedom but not serializabilty.

8. Suppose a database schedule S involves transactions T₁,….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?

1. Topological order
2. Depth – first order
3. Breadth – first order
4. Ascending order of transaction indices

9. Consider the following database schedule with two transactions T₁ and T₂.
S = r₂(X); r₁(X); r₂(Y); w₁(X); r₁(Y); w₂(X); a₁;a₂
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?

1. S is non – recoverable
2. S is recoverable, but has a cascading abort
3. S does not have a cascading abort
4. S is strict.

10. In a database system, unique timestamps are assigned to each transaction using Lamport’s logical clock. Let TS(T₁) and TS(T₂) be the timestamps of transactions T₁ and T₂ respectively. Besides, T₁ holds a lock on the resource R, and T₂ 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(T₂) < TS(T₁) then
T₁ is killed
else T₂ 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 deadlocks?

1. The database system is both deadlock-free and starvation-free.
2. The database system is deadlock-free, but not starvation-free.
3. The database system is starvation-free, but not deadlock-free.
4. The database system is neither deadlock-free nor starvation-free.

11. Two transactions T₁ and T₂ are given as
T₁ : r₁(X) w₁(X) r₁(Y) w₁(Y)
T₂ : r₂(Y) w₂(Y) r₂(Z) w₂(Z)
where r_i (V) denotes a read operation by transaction T_i on a variable V and w_i(V) denotes a write operation by transaction T_i on a variable V. The total number of conflict serializable schedules that can be formed by T₁ and T₂ is __________.

1. 46
2. 50
3. 54
4. 63

12. Which one of the following statements is false?

1. Any relation with two attributes is in BCNF
2. A relation in which every key has only one attribute is in 2NF
3. A prime attribute can be transitively dependent on a key in a 3NF relation.
4. A prime attribute can be transitively dependent on a key in a BCNF relation.

13. Consider the following relational schemas for a library database:
Book (Title, Author, Catalog_ no, Publisher, Year, Price)
Collection (Title, Author, Catalog_ no)
with the following functional dependencies:
I. Title Author → Catalog_no
II. Catalog_no → Title Author Publisher Year
III. Publisher Title Year → Price
Assume {Author, Title} is the key for both schemas. Which of the following statements is true?

1. Both Book and Collection are in BCNF
2. Both Book and Collection are in 3NF only
3. Book is in 2NF and Collection is in 3NF
4. Both Book and Collection are in 2NF only

14. The following functional dependencies hold for relations R(A, B, C) and S(B, D, E)
B → A,
A → C
The relation R contains 200 tuples and the relation Scontains 100 tuples. What is the maximum number of tuples possible in the natural join R ⋈ S?

1. 100
2. 200
3. 300
4. 2000

15. Which of the following is true?

1. Every relation in 3NF is also in BCNF
2. A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
3. Every relation in BCNF is also in 3NF
4. No relation can be in both BCNF and 3NF

16. 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?

1. 3
2. 4
3. 5
4. 6

17. 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

1. in 1NF, but not in 2NF
2. in 2NF, but not in 3NF
3. in 3NF, but not in BCNF
4. in BCNF

18. Assume that in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is true about the above schema?

1. The schema is in BCNF
2. The schema is in 3NF but not in BCNF
3. The schema is in 2NF but not in 3NF
4. The schema is not in 2NF

19. 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?

1. {E, F}
2. {E, F, H}
3. {E, F, H, K, L}
4. {E}

20. Given the following two statements:
S₁: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF
S₂: 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?

1. S₁ is true and S₂ is false
2. Both S₁ and S₂ are true
3. S₁ is false and S₂ is true
4. Both S₁ and S₂ are false

21. The maximum number of super-keys for the relation schema R (E, F, G, H) with E as the key is ___________.

1. 5
2. 6
3. 7
4. 8

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

1. in all candidate keys of R
2. in some candidate key of R
3. in a foreign key of R
4. only in the primary key of R

23. Consider an entity-Relationship (ER) model in which entity sets E₁ and E₂ are connected by an m:n relationship R₁₂. E₁ and E₃ are connected by a 1:n (1 on the side of E₁ and n on the side of E₃) relationship R₁₃. E₁ has two single-valued attributes a₁₁ and a₁₂ of which a₁₁ is the key attribute. E₂ has two single-valued attributes a₂₁ and a₂₂ of which a₂₁ is the key attribute. E₃ has two single-valued attributes a₃₁ and a₃₂ of which a₃₁ is the key attribute. The relationships do not have any attributes. If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3 NF is _________.

1. 2
2. 3
3. 4
4. 5

24. 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?

1. {P, R} → {S, T}
2. {P, R} → {R, T}
3. {P, S} → {S}
4. {P, S, U} → {Q}

25. 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 followingschemas.
(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?

1. 1NF
2. 2NF
3. 3NF
4. BCNF

26. Consider the following database table water_ schemes:

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 ≥ total_avg. capacity

The number of tuples returned by the following SQL query is _____________ .

1. 1
2. 2
3. 3
4. None of the above

27. The following functional dependencies hold true for the relational schema R {V, W, X, Y, Z}:
V → W
VW → X
Y → VX
Y → Z
Which of the following is irreducible equivalent for this set of set of functional dependencies?

1. V → W
   V → X
   Y → V
   Y → Z
2. V → W
   W → X
   Y → V
   Y → Z
3. V → W
   V → X
   Y → V
   Y → X
   Y → Z
4. V → W
   W → X
   Y → V
   Y → X
   Y → Z

28. Consider the following tables T1 and T2.

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing P in table Tl with on-delete set NULL and on-update cascade. In order to delete record 〈3, 8〉 from table T1, the number of additional records that need to be deleted from table T1 is ________.

1. 0
2. 3
3. 2
4. 8

29. 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?

1. Schema I
2. Schema II
3. Schema III
4. Schema IV

30. Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1, ties are not broke but ranks are skipped; if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.
Query 1: select A.customer, count (B.customer) from account A, account B where A.balance <= B.balance group by A.customer
Query 2: select A.customer, 1 + count (B.balance) from account A, account B where A.balance < B.balance group by A.customer Consider these statements about Query1 and Query2.
I. Query1 will produce the same row set as Query2 for some but not all databases.
II. Both Query1 and Query2 are correct implementation of the specification.
III. Query1 is a correct implementation of the specification but Query2 is not.
IV. Neither Query1 nor Query2 is a correct implementation of the specification.
V. Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC.
Which two of the above statements are correct?

1. 2 and 5
2. 1 and 3
3. 1 and 4
4. 3 and 5

31. Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries:
Query1: select student from enrolled where student in (select student from paid)
Query2: select student from paid where student in (select student from enrolled)
Query3: select E.student from enrolled E, paid P where E.student = P.student
Query4: select student from paid where exists (select * from enrolled where enrolled.student = paid.student)
Which one of the following statement is correct?

1. All queries return identical row sets for any database
2. Query2 and Query4 return identical row sets for all databases but there exist databases for which Query1 and Query2 return different row sets
3. There exist databases for which Query3 returns strictly fewer rows than Query2
4. There exist databases for which Query4 will encounter an integrity violation at runtime

32. Consider the relation enrolled (student, course), in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts 6000, 7000, 8000, 9000 and 10000 were each paid by 20% of the students. Consider these query plans (plan 1 on left, plan 2 on right) to ‘list all courses taken by students who have paid more than x’

A disk seek takes 4 ms, disk data transfer bandwidth is 300 MB/s and checking a tuple to see if amount is greater than x takes 10 µs. Which of the following statements is correct?

1. Plan 1 and Plan 2 will not output identical row sets for all databases
2. A course may be listed more than once in the output of Plan 1 for some databases
3. For x = 5000, Plan 1 executes faster than Plan 2 for all databases
4. For x = 9000, Plan 1 executes slower than Plan 2 for all databases

33. Information about a collection of students is given by the relation studinfo (studId, name, sex). The relation enroll (studId, courseId) gives which student has enrolled for (or taken) what course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?Π_courseld((Π_studid(σ_{sex = ‘female’}(studInfo)) × Π_courseld (enroll)) – enroll)

1. Courses in which all the female students are enrolled
2. Courses in which a proper subset of female students are enrolled
3. Courses in which only male students are enrolled.
4. None of the above

34. Consider the relation employee (name, sex, supervisorName) with name as the key.supervisorNamegives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce?
e.name | employee(e) ∧ (∀x)[¬employee(x) ∨ x.supervisorName ≠ e.name ∨ x.sex = “male”]

1. Names of employees with a male supervisor.
2. Names of employees with no immediate male subordinates.
3. Names of employees with no immediate female subordinates.
4. Names of employees with a female supervisor.

35. Consider the table employee (empId, name, department, salary) and the two queries Q₁, Q₂ below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?
Q₁ : SELECT e.empId
FROM employee e
WHERE not exists
(Select * From employee s where s.department = ‘5’ and s.salary >=e.salary)
Q₂ : SELECT e.empId
FROM employee e
WHERE e.salary > Any
(Select distinct salary From employee s Where s.department = ‘5’)

1. Q₁ is the correct query
2. Q₂ is the correct query
3. Both Q₁ and Q₂ produce the same answer.
4. Neither Q₁ nor Q₂ is the correct query

36. Let R and S be two relations with the following schema R (P, Q, R1, R2, R3), S (P, Q, S1, S2) Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?
I. ΠP (R ⋈ S)
II. ΠP (R) ⋈ ΠP (S)
III. ΠP (ΠP, Q (R) ∩ ΠP, Q (S))
IV. ΠP (ΠP, Q (R) – (ΠP, Q (R) – (ΠP, Q (S)))

1. Only I and II
2. Only I and III
3. Only I, II and III
4. Only I, III and IV

37. Let R and S be relational schemes such that R ={a,b,c} and S = {c}. Now consider the following queries on the database:
I. π_R-S(r) – π_R-S(π_R-S(r)×s – π_R-S,S(r))
II. {t|t Є π_R-S(r) ∧ ∀v Є s(Ǝv Є r(u=v[s] ∧ t=v[R-S]))}
III. {t|t Є π_R-S(r) ∧ ∀v Є r(Ǝu Є s(u=v[s] ∧ t=v[R-S]))}
IV. SELECT R.a, R.b
FROM R, S
WHERE R.c = S.c
Which of the above queries are equivalent?

1. I and II
2. I and III
3. II and IV
4. III and IV

38. Consider the following relational schema: Suppliers (sid: integer, sname:string, city: string, street: string) Parts(pid: integer, pname:string, color: string) Catalog (sid: integer, pid: integer,cost: real). Consider the following relational query on the above database:

SELECT 
  S.sname 
FROM 
  Suppliers S 
WHERE 
  S.sid NOT IN (
    SELECT 
      C.sid 
    FROM 
      Catalog C 
    WHERE 
      C.pid NOT IN (
        SELECT 
          P.pid 
        FROM 
          Parts P 
        WHERE 
          P.color <> ‘blue’
      )
  )

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

1. Find the names of all suppliers who have supplied a non-blue part.
2. Find the names of all suppliers who have not supplied a non-blue part.
3. Find the names of all suppliers who have supplied only blue parts.
4. Find the names of all suppliers who have not supplied only blue parts.

39. Consider the following relational schema: Suppliers (sid: integer, sname:string, city: string, street: string) Parts(pid: integer, pname:string, color: string) Catalog (sid: integer, pid: integer,cost: real). A relational schema for a train reservation database is given below
Passenger (pid, pname, age)
Reservation (pid, cass, tid)
Table :Passenger
Table :Reservation

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
  )

1. 1, 0
2. 1, 2
3. 1, 3
4. 1, 5

40. Consider a relational table r with sufficient number of records, having attributes A₁, A₂, … A_n and let 1 ≤ p ≤ n. Two queries Q1 and Q2 are given below.
Q1: π_{A1.… An} (σ_Ap=c(r)) where c is a constant.
Q2: π_{A1.… An} (σ_c1≤Ap≤c2(r)) where c₁ and c₂ are constants.
The database can be configured to do ordered indexing on A_P or hashing on A_p. Which of the following statements is TRUE?

1. Ordered indexing will always outperform hashing for both queries
2. Hashing will always outperform ordered indexing for both queries.
3. Hashing will outperform ordered indexing on Q1, but not on Q2.
4. Hashing will outperform ordered indexing on Q2, but not on Q1.

41. 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;

1. 3
2. 9
3. 5
4. 6

42. 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;

1. 127
2. 255
3. 129
4. 257

43. 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

1. P and R
2. P and S
3. Q and R
4. Q and S

44. Suppose R₁(A, B) and R₂(C, D) are two relation schemas. Let r₁ and r₂ be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r₁ and r₂ satisfy referential integrity constraints, which of the following is always true?

1. Π_B(r₁) – Π_C(r₂) = ∅
2. Π_C(r₂) – Π_B (r₁) = ∅
3. Π_B (r₁) = Π_C (r₂)
4. Π_B (r₁) – Π_C (r₂) ≠ ∅

45. 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’
  )

1. 4
2. 3
3. 0
4. 1

46. 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.(A⋃B) ⋈_{A.Id > 40 V C.Id <15}C

1. 7
2. 4
3. 5
4. 9

47. 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) π_sname(σ_{courseno=107^percent>90} Registration^⋈Students)
(III) {T |∃S∈ Students, ∃R∈ Registration (S.rollno=R.rollno ∧ R.courseno=107 ∧ R.percent>90∧T.sname=S.sname)}
(IV) {<S_N> |∃S_R∃R_P (<S_R,S_N> ∈ Students ∧ S_R, 107, R_P>∈ Registration ∧ R_P>90)}

1. I, II, III and IV
2. I, II and III only
3. I, II and IV only
4. II, III and IV only

48. Given the following statements:
S₁: A foreign key declaration can always be replaced by an equivalent check assertion in SQL.
S₂: 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 1.,
FOREIGN KEY 1. references R)

Which one of the following statements is CORRECT?

1. S₁ is TRUE and S₂ is FALSE
2. Both S₁ and S₂ are TRUE
3. S₁ is FALSE and S₂ is TRUE
4. Both S₁ and S₂ are FALSE

49. 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?

1. It executes but does not give the correct result.
2. It executes and gives the correct result.
3. It generates an error because of pair wise comparison.
4. It generates an error because the GROUP BY clause cannot be used with table joins in a subquery.

50. Given an instance of the STUDENTS relation as shown below:

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

1. 18
2. 19
3. 20
4. 17

51. Consider a join (relation algebra) between relations (r(R)) and (s(S)) using the nested loop method. There are three 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

1. Relation r(R) is in the outer loop
2. Relation s(S) is in the outer loop
3. Join selection factor between r(R) and s(S) is more than 0.5
4. Join selection factor between r(R) and s(S) is less than 0.5

52. 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)

1. Select R.* from R, S where R. a = S. a
2. Select distinct R * from R, S where R . a = S . a
3. Select R.* from R, (select distinct a from S) as S1 where R.a = S1.a
4. Select R.* from R, S where R.a = S.a and is unique R

53. What is the optimized version of the relation algebra expression π_A1 (π_A2 (σ_F1 (σ_F2(r))), where A₁, A₂ are sets of attributes in r with A₁ ⊂ A₂ and F₁, F₂ are Boolean expressions based on the attributes in r?

1. π_A1 (σ_{(F1∧F2(^(r)})
2. π_A1 (σ_(F1VF2(^(r))
3. π_A2 (σ_{(F1∧F2(^(r)})
4. π_A2 (σ_(F1VF2(^(r))

54. Consider the relational schema given below, where eld 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. Consider the following relational algebra query:
employee (empId, empName, empAge)
dependent (depId, eId, depName, depAge)
π_empId(employee)- π_empId(employee ⋈ _{(empId = eID) ∧ (empAge ≤depAge)} dependent)
The above query evaluates to the set of empIds of employees whose age is greater than that of

1. some dependent.
2. all dependents.
3. some of his/her dependents.
4. all of his/her dependents.

55. 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’);

1. Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
2. Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
3. Names of all the employees with none of their customers having a ‘GOOD’ rating.
4. Names of all the employees with all their customers having a ‘GOOD’ rating.

56. SELECT operation in SQL is equivalent to

1. The selection operation in relational algebra
2. The selection operation in relational algebra, except that SELECT in SQL retains duplicates.
3. The projection operation in relational algebra.
4. The projection operation in relational algebra, except that SELECT in SQL retains duplicates.

57. Consider the following relations:

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 ________

1. 1
2. 2
3. 3
4. None of the above

58. Consider two relations R₁(A, B) with the tuples (1, 5),(3, 7) and R₂(A, C) = (1, 7), (4, 9). Assume that R(A, B, C) is the full natural outer join of R₁ and R₂. 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?

1. R contains a, b, e, f, g but not c, d.
2. R contains all of a, b, c, d, e, f, g.
3. R contains e, f, g but not a, b.
4. R contains e but not f, g.

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

1. WHERE P₁.capacity > = All (select P₂. Capacity from Cinema P₂)
2. WHERE P₁.capacity >= Any (select P₂. Capacity from Cinema P₂)
3. WHERE P₁.capacity > All (select max(P₂.capacity) from Cinema P₂)
4. WHERE P₁.capacity > Any (select max(P₂.capacity) from Cinema P₂)

60. Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key VY?

1. V XYZ
2. V WXZ
3. V WXY
4. V WXYZ

61. 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 given below.

SELECTIVE 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 _______.

1. 2.6
2. 2.5
3. 2
4. 1.6

62. Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId), Note that the DeptId can be permitted to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.
(I) {t | ∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∀v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(II) {t | ∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] ≠ v[DeptId]))}
(III) {t | ∃u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃v ∈ DEPT(t[DeptId] = v[DeptId]))}
Which of the above queries are safe?

1. (I) and (II) only
2. (I) and (III) only
3. (II) and (III) only
4. (I), (II) and (III)

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

The following query is made on the database.
T1 ← π_CourseName (σ_{StudentName=’SA’}(CR))
T2 ← CR ÷ T1
The number of rows in T2 is ___________ .

1. 5
2. 6
3. 7
4. 8

64. Consider the following database table named top_scorer.

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 __________.

1. 7
2. 9
3. 11
4. 13

65. 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?

1. Query 1
2. Query 2
3. Query 3
4. Query 4

66. Consider the relations r(A, B) and s(B, C), where s⋅ Bis a primary key and r⋅ B is a foreign key referencing s⋅ B. Consider the query
Q: r ⋈ (σ_B<5 (S))
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?

1. σ_B<5(r⋈s)
2. σ_B<5(r LOJ s)
3. r LOJ (σ_B<5(s))
4. σ_B<5(r) LOJ s

67. Let E₁ and E₂ be two entities in an E/R diagram, with simple single-valued attributes. R₁ and R₂ are two relationships between E₁ and E₂, where R₁ is one-to-many and R₂ is many-to-many. R₁ and R₂ do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?

1. 2
2. 3
3. 4
4. 5

68. The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2, 4) is deleted is:

1. (3, 4) and (6, 4)
2. (5, 2) and (7, 2)
3. (5, 2), (7, 2) and (9, 5)
4. (3, 4), (4, 3) and (6, 4)

69. Which of the following tuple relational calculus expression(s) is/are equivalent to ∀t ∈ r (P(t))?
I. ¬ ∃t ∈ r (P(t))
II. ∃t ∉ r (P(t))
III. ¬ ∃t ∈ r (¬P(t))
IV. ∃t ∈ r (¬P(t))

1. I only
2. II only
3. III only
4. III and IV only

70. Consider the following ER diagram:
The minimum number of tables needed to represent M, N, P, R₁, R₂ is?

1. 2
2. 3
3. 4
4. 5

71. Consider the following ER diagram:
Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?

1. {M1, M2, M3, P1}
2. {M1, P1, N1, N2}
3. {M1, P1, N1}
4. {M1, P1}

72. Consider a relational table with a single record for each registered student with the following attributes.
I. Registration_Num: Unique registration number of each registered student
II. UID: Unique identity number, unique at the national level for each citizen
III. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attribute stores the primary account number
IV. Name: Name of the student
V. Hostel_Room: Room number of the hostel

Which of the following options is incorrect?

1. BankAccount_Num is a candidate key
2. Registration_Num can be a primary key
3. UID is a candidate key if all students are from the same country
4. If S is a super key such that S ∩ UID is NULL then S ∪ UID is also super key.

73. Given the basic ER and relational models, which of the following is incorrect?

1. An attribute of an entity can have more than one value
2. An attribute of an entity can be composite
3. In a row of a relational table, an attribute can have more than one value
4. In a row of a relational table, an attribute can have exactly one value or a NULL value

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

1. Relationship R is one-to-many and the participation of A in R is total.
2. Relationship R is one-to-many and the participation of A in R is partial.
3. Relationship R is many-to-one and the participation of A in R is total.
4. Relationship R is many-to-one and the participation of A in R is partial.

75. In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entityset 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?

1. Every entity in E1 is associated with exactly one entity in E2.
2. Some entity in E1 is associated with more than one entity in E2.
3. Every entity in E2 is associated with exactly one entity in E1.
4. Every entity in E2 is associated with at most one entity in E1.