DB · previous year questions

All PYQs: Databases

75 real GATE CSE questions from 2011 to 2025, with the official answer key and a worked solution for each. Filter by year, topic, type, marks or keyword.

Questions belong to the GATE organising institutes; this copy is for personal study. Coverage varies by year: questions that need a figure I could not read, or whose answer I could not verify, were left out.

Showing 75 of 75 questions · 0 correct of 0 attempted · marks 0 / 117

GATE 2025 CS1 · Q15MCQ1 markTransactions & Concurrency Control
A schedule of three database transactions T1, T2 and T3 is shown. Ri(A) and Wi(A) denote read and write of data item A by transaction Ti. The transaction T1 aborts at the end. Which other transaction(s) will be required to be rolled back? Schedule: R1(X) W1(Y) R2(X) R2(Y) R3(Y) ABORT(T1).
GATE 2025 CS2 · Q27MSQ1 markTransactions & Concurrency Control
An audit of a banking transactions system found that two joint holders of account A attempted simultaneous transfers of Rs. 10000 each from account A to account B. Both transactions read the same value, Rs. 11000, as the initial balance in A and were allowed to go through. B was credited Rs. 10000 twice. A was debited only once and ended up with a balance of Rs. 1000. Which of the following properties is/are certain to have been violated by the system?

One or more options may be correct. Select all that apply, then submit.

GATE 2025 CS1 · Q39MCQ2 marksRelational Algebra & Calculus
Consider two relations describing teams and players in a sports league: teams(tid, tname) and players(pid, pname, tid), where tid, tname are team-id and team-name, and pid, pname, tid are player-id, player-name and the team-id of the player. Which ONE of the following tuple relational calculus queries returns the name of the players who play for the team having tname as 'MI'?
GATE 2025 CS2 · Q46MSQ2 marksNormal Forms & Decomposition
Consider the following relational schema along with all the functional dependencies that hold on them. R1(A, B, C, D, E): { D → E, EA → B, EB → C }. R2(A, B, C, D): { A → D, A → B, C → A }. Which of the following statement(s) is/are TRUE?

One or more options may be correct. Select all that apply, then submit.

GATE 2025 CS1 · Q47MSQ2 marksNormal Forms & Decomposition
Consider a relational schema team(name, city, owner), with functional dependencies {name → city, name → owner}. The relation team is decomposed into two relations, t1(name, city) and t2(name, owner). Which of the following statement(s) is/are TRUE?

One or more options may be correct. Select all that apply, then submit.

GATE 2025 CS2 · Q53MSQ2 marksTransactions & Concurrency Control
Consider the database transactions T1 and T2, and data items X and Y. T1: R1(X) W1(Y) R1(X) W1(X) COMMIT(T1). T2: W2(X) W2(Y) COMMIT(T2). Which of the schedule(s) is/are conflict serializable?
(A) R1(X), W2(X), W1(Y), W2(Y), R1(X), W1(X), COMMIT(T2), COMMIT(T1).
(B) W2(X), R1(X), W2(Y), W1(Y), R1(X), COMMIT(T2), W1(X), COMMIT(T1).
(C) R1(X), W1(Y), W2(X), W2(Y), R1(X), W1(X), COMMIT(T1), COMMIT(T2).
(D) W2(X), R1(X), W1(Y), W2(Y), R1(X), COMMIT(T2), W1(X), COMMIT(T1).

One or more options may be correct. Select all that apply, then submit.

GATE 2025 CS2 · Q54MSQ2 marksSQL
Consider the relational schema Students(rollno, name, age, cgpa), Courses(courseno, cname, credits), Enrolled(rollno, courseno, grade). Which of the following is/are correct SQL queries to retrieve the names of the students enrolled in course number 1470? (A)
SELECT S.name
FROM Students S
WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno)
(B) the same with WHERE SIZEOF(
SELECT *
FROM Enrolled E
WHERE E.courseno = 1470 AND E.rollno = S.rollno) > 0
(C) the same with WHERE 0 < (
SELECT COUNT(*)
FROM Enrolled E
WHERE E.courseno = 1470 AND E.rollno = S.rollno)
(D)
SELECT S.name
FROM Students S NATURAL JOIN Enrolled E
WHERE E.courseno = 1470

One or more options may be correct. Select all that apply, then submit.

GATE 2025 CS1 · Q55NAT2 marksSQL
Consider the following database tables of a sports league: player(pid, pname, age), coach(cid, cname), team(tid, tname, city, cid), members(pid, tid). Instance:
player = (1, Jasprit, 31), (2, Atharva, 24), (3, Ishan, 26), (4, Axar, 30);
coach = (101, Ricky), (102, Mark), (103, Trevor);
team = (10, MI, Mumbai, 102), (20, DC, Delhi, 101), (30, PK, Mohali, 103);
members = (1, 10), (2, 30), (3, 10), (4, 20). Query:
SELECT MIN(P.age)
FROM player P
WHERE P.pid IN (SELECT M.pid FROM team T, coach C, members M WHERE C.cname = 'Mark' AND T.cid = C.cid AND M.tid = T.tid)
The value returned is ______.
GATE 2025 CS2 · Q57NAT2 marksFile Organisation & Indexing
In a B+ tree where each node can hold at most four key values, a root to leaf path consists of the following nodes: A = (49, 77, 83, -), B = (7, 19, 33, 44), C = (20*, 22*, 25*, 26*). The *-marked keys are data entries in a leaf. Assume that a pointer between keys k1 and k2 points to a subtree containing keys in [k1, k2), and that when a leaf is created, the smallest key in it is copied up into its parent. A record with key value 23 is inserted into the B+ tree. The smallest key value in the parent of the leaf that contains 25* is ______.
GATE 2024 CS2 · Q19MCQ1 markTransactions & Concurrency Control
Once the DBMS informs the user that a transaction has been successfully completed, its effect should persist even if the system crashes before all its changes are reflected on disk. This property is called
GATE 2024 CS1 · Q20MCQ1 markER Model, Relational Model & Keys
Let S be the specification: "Instructors teach courses. Students register for courses. Courses are allocated classrooms. Instructors guide students." Which one of the following ER diagrams CORRECTLY represents S? (The four diagrams are described here in words.) (i) Instructor–Teaches–Course and Classroom–Allocation–Course are drawn, but Registration is drawn as a separate entity box attached to Course, and Student is joined to Course by a plain line with no Registration relationship. (ii) The entity at the bottom is Projects instead of Student: Instructor–Guides–Projects and Course–Registration–Projects. (iii) Instructor–Teaches–Course, Course–Registration–Student, Instructor–Guides–Student, but the Classroom box is joined to Course by a plain line that does not pass through the Allocation diamond. (iv) Instructor–Teaches–Course, Classroom–Allocation–Course, Course–Registration–Student, Instructor–Guides–Student, every relationship drawn as a diamond between the right pair of entity sets.
GATE 2024 CS2 · Q20MCQ1 markER Model, Relational Model & Keys
In the context of owner and weak entity sets in the ER (Entity-Relationship) data model, which one of the following statements is TRUE?
GATE 2024 CS1 · Q21MCQ1 markFile Organisation & Indexing
In a B+ tree, the requirement of at least half-full (50%) node occupancy is relaxed for which one of the following cases?
GATE 2024 CS1 · Q22MSQ1 markER Model, Relational Model & Keys
Which of the following statements about a relation R in first normal form (1NF) is/are TRUE?

One or more options may be correct. Select all that apply, then submit.

GATE 2024 CS2 · Q26MSQ1 markFile Organisation & Indexing
Which of the following file organizations is/are I/O efficient for the scan operation in DBMS?

One or more options may be correct. Select all that apply, then submit.

GATE 2024 CS2 · Q27MSQ1 markTransactions & Concurrency Control
Which of the following statements about the Two Phase Locking (2PL) protocol is/are TRUE?

One or more options may be correct. Select all that apply, then submit.

GATE 2024 CS1 · Q35NAT1 markRelational Algebra & Calculus
Consider the following two relations, R(A, B) and S(A, C). R = {(10, 20), (20, 30), (30, 40), (30, 50), (50, 95)} and S = {(10, 90), (30, 45), (40, 80)}. The total number of tuples obtained by evaluating the expression σB<C(R ⋈R.A=S.A S) is ______.
GATE 2024 CS1 · Q44MSQ2 marksFunctional Dependencies
The symbol → indicates functional dependency in the context of a relational database. Which of the following options is/are TRUE?

One or more options may be correct. Select all that apply, then submit.

GATE 2024 CS2 · Q45MCQ2 marksRelational Algebra & Calculus
The relation schema Person(pid, city) describes the city of residence for every person uniquely identified by pid. The following relational algebra operators are available: selection, projection, cross product, and rename. To find the list of cities where at least 3 persons reside, using the above operators, the minimum number of cross product operations that must be used is
GATE 2024 CS1 · Q46MSQ2 marksTransactions & Concurrency Control
Consider the following read-write schedule S over three transactions T1, T2 and T3: S: r1(z); w1(z); r2(x); r3(y); w3(y); r2(y); w2(x); w2(y). Which of the following transaction schedules is/are conflict equivalent to S?

One or more options may be correct. Select all that apply, then submit.

GATE 2024 CS2 · Q56NAT2 marksFunctional Dependencies
A functional dependency F: X → Y is termed a useful functional dependency if and only if it satisfies all of: X is not the empty set, Y is not the empty set, and the intersection of X and Y is the empty set. For a relation R with 4 attributes, the total number of possible useful functional dependencies is ______.
GATE 2023 · Q16MCQ1 markRelational Algebra & Calculus
Which one of the options given below refers to the degree (or arity) of a relation in relational database systems?
GATE 2023 · Q61NAT2 marksSQL
Consider the following table named Student in a relational database. The primary key of this table is rollNum. Student(rollNum, name, gender, marks): (1, Naman, M, 62), (2, Aliya, F, 70), (3, Aliya, F, 80), (4, James, M, 82), (5, Swati, F, 65). The SQL query
SELECT *
FROM Student
WHERE gender = 'F' AND marks > 65
is executed. The number of rows returned by the query is ______.
GATE 2023 · Q62NAT2 marksFile Organisation & Indexing
Consider a database of fixed-length records, stored as an ordered file. The database has 25,000 records, with each record being 100 bytes, of which the primary key occupies 15 bytes. The data file is block-aligned in that each data record is fully contained within a block. The database is indexed by a primary index file, which is also stored as a block-aligned ordered file (each index entry is a primary key and a block anchor pointer). Suppose the block size of the file system is 1024 bytes, and a pointer to a block occupies 5 bytes. The system uses binary search on the index file to search for a record with a given key. You may assume that a binary search on an index file of b blocks takes ⌈log₂ b⌉ block accesses in the worst case. Given a key, the number of block accesses required to identify the block in the data file that may contain a record with the key, in the worst case, is ______.
GATE 2022 · Q14MCQ1 markNormal Forms & Decomposition
In a relational data model, which one of the following statements is TRUE?
GATE 2022 · Q25MSQ1 markRelational Algebra & Calculus
Consider the following three relations in a relational database: Employee(eId, Name), Brand(bId, bName), Own(eId, bId). Which of the following relational algebra expressions return the set of eIds who own all the brands?
(A) πeId(πeId,bId(Own) / πbId(Brand)).
(B) πeId(Own) − πeId((πeId(Own) × πbId(Brand)) − πeId,bId(Own)).
(C) πeId(πeId,bId(Own) / πbId(Own)).
(D) πeId((πeId(Own) × πbId(Own)) / πbId(Brand)).

One or more options may be correct. Select all that apply, then submit.

GATE 2022 · Q31NAT1 markFunctional Dependencies
Consider a relation R(A, B, C, D, E) with the following three functional dependencies:
AB → C
BC → D
C → E
The number of super keys in the relation R is ______.
GATE 2022 · Q56NAT2 marksSQL
Consider the relational database with schemas Student(sNo, sName, dNo), Dept(dNo, dName), Course(cNo, cName, dNo), Register(sNo, cNo). Instances: Student = (S01, James, D01), (S02, Rocky, D01), (S03, Jackson, D02), (S04, Jane, D01), (S05, Milli, D02). Dept = (D01, CSE), (D02, EEE). Course = (C11, DS, D01), (C12, OS, D01), (C21, DE, D02), (C22, PT, D02), (C23, CV, D03). Register = (S01, C11), (S01, C12), (S02, C11), (S03, C21), (S03, C22), (S03, C23), (S04, C11), (S04, C12), (S05, C11), (S05, C21). SQL Query:
SELECT *
FROM Student AS S
WHERE NOT EXISTS (SELECT cNo FROM Course WHERE dNo = "D01" EXCEPT SELECT cNo FROM Register WHERE sNo = S.sNo)
The number of rows returned by the above SQL query is ______.
GATE 2021 Set 1 · Q13MSQ1 markTransactions & Concurrency Control
Suppose a database system crashes again while recovering from a previous crash. Assume checkpointing is not done by the database either during the transactions or during recovery. Which of the following statements is/are correct?

One or more options may be correct. Select all that apply, then submit.

GATE 2021 Set 1 · Q23NAT1 markRelational Algebra & Calculus
A relation r(A, B) in a relational database has 1200 tuples. The attribute A has integer values ranging from 6 to 20, and the attribute B has integer values ranging from 1 to 20. Assume that the attributes A and B are independently distributed. The estimated number of tuples in the output of σ(A>10) ∨ (B=18)(r) is ______.
GATE 2021 Set 1 · Q27MCQ2 marksRelational Algebra & Calculus
The following relation records the age of 500 employees of a company, where empNo (indicating the employee number) is the key: empAge(empNo, age). Consider the relational algebra expression πempNo(empAge ⋈(age > age1) ρempNo1, age1(empAge)). What does the above expression generate?
GATE 2021 Set 1 · Q32MCQ2 marksTransactions & Concurrency Control
Let ri(z) and wi(z) denote read and write operations respectively on a data item z by a transaction Ti. Consider the two schedules S1: r1(x) r1(y) r2(x) r2(y) w2(y) w1(x) and S2: r1(x) r2(x) r2(y) w2(y) r1(y) w1(x). Which one of the following options is correct?
GATE 2021 Set 1 · Q33MCQ2 marksNormal Forms & Decomposition
Consider the relation R(P, Q, S, T, X, Y, Z, W) with the functional dependencies PQ → X; P → YX; Q → Y; Y → ZW. Consider the decomposition of R into the following two schemes. D1: R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)]. D2: R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)]. Which one of the following options is correct?
GATE 2021 Set 1 · Q47MSQ2 marksFile Organisation & Indexing
Consider a dynamic hashing approach for 4-bit integer keys: (1) There is a main hash table of size 4. (2) The 2 least significant bits of a key are used to index into the main hash table. (3) Initially, the main hash table entries are empty. (4) Thereafter, when more keys are hashed into it, to resolve collisions, the set of all keys corresponding to a main hash table entry is organised as a binary tree that grows on demand. (5) First, the 3rd least significant bit is used to divide the keys into left and right subtrees. (6) To resolve more collisions, each node of the binary tree is further subdivided into left and right subtrees based on the 4th least significant bit. (7) A split is done only if it is needed, i.e. only when there is a collision. Figure: entry 00 is empty; entry 01 has a tree whose left branch (bit 0) is one leaf and whose right branch (bit 1) is a node with two leaves; entry 10 has a node with one leaf on each side; entry 11 holds a single leaf. Which of the following sequences of key insertions can cause this state of the hash table (assume the keys are in decimal notation)?

One or more options may be correct. Select all that apply, then submit.

GATE 2020 · Q13MCQ1 markSQL
Consider a relational database containing the schemas Catalogue(sno, pno, cost) with rows (S1,P1,150), (S1,P2,50), (S1,P3,100), (S2,P4,200), (S2,P5,250), (S3,P1,250), (S3,P2,150), (S3,P5,300), (S3,P4,250); Suppliers(sno, sname, location) with (S1, M/s Royal furniture, Delhi), (S2, M/s Balaji furniture, Bangalore), (S3, M/s Premium furniture, Chennai); Parts(pno, pname, part_spec).
SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND cost > (SELECT AVG(cost) FROM Catalogue WHERE pno = 'P4' GROUP BY pno)
The number of rows returned by the above SQL query is
GATE 2020 · Q14MCQ1 markER Model, Relational Model & Keys
Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?
GATE 2020 · Q36MCQ2 marksNormal Forms & Decomposition
Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?
GATE 2020 · Q37MCQ2 marksTransactions & Concurrency Control
Consider a schedule of transactions T1 and T2, in time order: RA(T1), RB(T2), WB(T2), RC(T1), RD(T2), WD(T1), WC(T2), WB(T1), Commit(T1), Commit(T2). Here RX stands for Read(X) and WX stands for Write(X). Which one of the following schedules is conflict equivalent to the above schedule?
(A) RB2 WB2 RD2 RA1 RC1 WD1 WB1 WC2 Commit1 Commit2.
(B) RA1 RC1 WD1 WB1 RB2 WB2 RD2 WC2 Commit1 Commit2.
(C) RA1 RC1 WD1 RB2 WB2 RD2 WB1 WC2 Commit1 Commit2.
(D) RB2 WB2 RD2 WC2 RA1 RC1 WD1 WB1 Commit1 Commit2.
GATE 2020 · Q54NAT2 marksFile Organisation & Indexing
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 ______.
GATE 2019 Set 2 · Q11MCQ1 markTransactions & Concurrency Control
Consider the following two statements about database transaction schedules: I. Strict two-phase locking protocol generates conflict serializable schedules that are also recoverable. II. Timestamp-ordering concurrency control protocol with Thomas' Write Rule can generate view serializable schedules that are not conflict serializable. Which of the above statements is/are TRUE?
GATE 2019 Set 2 · Q14MCQ1 markFile Organisation & Indexing
Which one of the following statements is NOT correct about the B+ tree data structure used for creating an index of a relational database table?
GATE 2019 Set 2 · Q32MCQ2 marksNormal Forms & Decomposition
Let the set of functional dependencies F = {QR → S, R → P, S → Q} hold on a relation schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas Y and Z, where Y = (PR) and Z = (QRS). Consider the two statements: I. Both Y and Z are in BCNF. II. Decomposition of X into Y and Z is dependency preserving and lossless. Which of the above statements is/are correct?
GATE 2019 Set 2 · Q51NAT2 marksSQL
A relational database contains two tables Student and Performance. Student(Roll_no, Student_name) = (1, Amit), (2, Priya), (3, Vinit), (4, Rohan), (5, Smita). Performance(Roll_no, Subject_code, Marks) = (1, A, 86), (1, B, 95), (1, C, 90), (2, A, 89), (2, C, 92), (3, C, 80). The primary key of the Student table is Roll_no. For the Performance table, Roll_no and Subject_code together form the primary key.
SELECT S.Student_name, sum(P.Marks)
FROM Student S, Performance P
WHERE P.Marks > 84
GROUP BY S.Student_name
The number of rows returned by the above SQL query is ______.
GATE 2019 Set 2 · Q55NAT2 marksRelational Algebra & Calculus
Consider the relations P(X, Y, Z), Q(X, Y, T) and R(Y, V). P = {(X1,Y1,Z1), (X1,Y1,Z2), (X2,Y2,Z2), (X2,Y4,Z4)}; Q = {(X2,Y1,2), (X1,Y2,5), (X1,Y1,6), (X3,Y3,1)}; R = {(Y1,V1), (Y3,V2), (Y2,V3), (Y2,V2)}. How many tuples will be returned by the relational algebra query πX(σP.Y=R.Y ∧ R.V=V2(P × R)) − πX(σQ.Y=R.Y ∧ Q.T>2(Q × R))?
GATE 2018 · Q11MCQ1 markER Model, Relational Model & Keys
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?
GATE 2018 · Q12MCQ1 markSQL
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: the same with LEFT OUTER JOIN; Query 3: the same with RIGHT OUTER JOIN; Query 4: the same with FULL OUTER JOIN. Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?
GATE 2018 · Q41MCQ2 marksRelational Algebra & Calculus
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 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?
GATE 2018 · Q42MCQ2 marksNormal Forms & Decomposition
Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. Schema I: Registration(rollno, courses), where courses is a set-valued attribute; rollno → courses. Schema II: Registration(rollno, courseid, email); {rollno, courseid} → email; email → rollno. Schema III: Registration(rollno, courseid, marks, grade); {rollno, courseid} → {marks, grade}; marks → grade. Schema IV: Registration(rollno, courseid, credit); {rollno, courseid} → credit; courseid → credit. Which one of the relational schemas above is in 3NF but not in BCNF?
GATE 2017 Set 1 · Q16MCQ1 markFunctional Dependencies
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 an irreducible equivalent for this set of functional dependencies?
GATE 2017 Set 1 · Q23NAT1 markSQL
Consider a database that has the relation schema EMP(EmpId, EmpName, DeptName). EMP = (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). The query is:
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 ______.
GATE 2017 Set 1 · Q41MCQ2 marksRelational Algebra & Calculus
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?
GATE 2017 Set 1 · Q42MCQ2 marksTransactions & Concurrency Control
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 transactions 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 deadlocks?
GATE 2017 Set 1 · Q46NAT2 marksSQL
Consider a database that has the relation schema CR(StudentName, CourseName). The instance is: SA: CA, CB, CC; SB: CB, CC; SC: CA, CB, CC; SD: CA, CB, CC, CD; SE: CD, CA, CB; SF: CA, CB, CC. The following query is made: T1 ← πCourseName(σStudentName = 'SA'(CR)); T2 ← CR ÷ T1. The number of rows in T2 is ______.
GATE 2016 Set 1 · Q21MCQ1 markER Model, Relational Model & Keys
Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key VY?
GATE 2016 Set 1 · Q22MCQ1 markTransactions & Concurrency Control
Which one of the following is NOT a part of the ACID properties of database transactions?
GATE 2016 Set 1 · Q23MCQ1 markNormal Forms & Decomposition
A database of research articles in a journal uses the schema (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE). The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist: (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE; (VOLUME, NUMBER) → YEAR; (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE. The database is redesigned to use the schemas (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE) and (VOLUME, NUMBER, YEAR). Which is the weakest normal form that the new database satisfies, but the old one does not?
GATE 2016 Set 1 · Q51MCQ2 marksTransactions & Concurrency Control
Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O1, ..., Ok}. This is done in the following manner. Step 1: T acquires exclusive locks to O1, ..., Ok in increasing order of their addresses. Step 2: The required operations are performed. Step 3: All locks are released. This protocol will
GATE 2014 Set 1 · Q21MCQ1 markFunctional Dependencies
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?
GATE 2014 Set 1 · Q22MCQ1 markSQL
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?
GATE 2014 Set 1 · Q29MCQ2 marksTransactions & Concurrency Control
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?
(A) r1(x); r2(x); w1(x); r3(x); w2(x).
(B) r2(x); r1(x); w2(x); r3(x); w1(x).
(C) r3(x); r2(x); r1(x); w2(x); w1(x).
(D) r2(x); w2(x); r3(x); r1(x); w1(x).
GATE 2014 Set 1 · Q30MCQ2 marksNormal Forms & Decomposition
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?
GATE 2014 Set 1 · Q54MCQ2 marksSQL
Given the schema employees(emp-id, first-name, last-name, hire-date, dept-id, salary) and 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 query:
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?
GATE 2013 Code A · Q15MCQ1 markFile Organisation & Indexing
An index is clustered, if
GATE 2013 Code A · Q35MCQ2 marksRelational Algebra & Calculus
Consider the relational schema Students(rollno, sname), Courses(courseno, cname), Registration(rollno, courseno, percent). Which of the following queries are equivalent to the English query "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) {<SN> | ∃SR ∃RP (<SR, SN> ∈ Students ∧ <SR, 107, RP> ∈ Registration ∧ RP > 90)}.
GATE 2013 Code A · Q54MCQ2 marksFunctional Dependencies
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?
GATE 2013 Code A · Q55MCQ2 marksNormal Forms & Decomposition
Relation R has eight attributes ABCDEFGH, with the functional dependencies F = {CH → G, A → BC, B → CFH, E → A, F → EG} (linked with the previous question; the candidate keys are AD, BD, ED and FD). The relation R is
GATE 2012 Code A · Q2MCQ1 markNormal Forms & Decomposition
Which of the following is TRUE?
GATE 2012 Code A · Q14MCQ1 markER Model, Relational Model & Keys
Given the basic ER and relational models, which of the following is INCORRECT?
GATE 2012 Code A · Q27MCQ2 marksTransactions & Concurrency Control
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
GATE 2012 Code A · Q43MCQ2 marksER Model, Relational Model & Keys
Suppose R1(A, B) and R2(C, D) are two relation schemas (A and C are the keys). 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?
GATE 2012 Code A · Q50MCQ2 marksRelational Algebra & Calculus
Consider the following relations. A = {(12, Arun, 60), (15, Shreya, 24), (99, Rohit, 11)}, B = {(15, Shreya, 24), (25, Hari, 40), (98, Rohit, 20), (99, Rohit, 11)} (both with schema Id, Name, Age), and C(Id, Phone, Area) = {(10, 2200, 02), (99, 2100, 01)}. How many tuples does the result of the relational algebra expression (A ∪ B) ⋈A.Id > 40 ∨ C.Id < 15 C contain? Assume that the schema of A ∪ B is the same as that of A.
GATE 2012 Code A · Q51MCQ2 marksSQL
Using the relations A = {(12, Arun, 60), (15, Shreya, 24), (99, Rohit, 11)} and B = {(15, Shreya, 24), (25, Hari, 40), (98, Rohit, 20), (99, Rohit, 11)} (Id, Name, Age), 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')
GATE 2011 Code A · Q12MCQ1 markER Model, Relational Model & Keys
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. BankAccount_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?
GATE 2011 Code A · Q39MCQ2 marksFile Organisation & Indexing
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 = c(r)) where c is a constant. Q2: πA1..Ap(σc1 ≤ Ap ≤ c2(r)) 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?
GATE 2011 Code A · Q46MCQ2 marksSQL
The database table Loan_Records(Borrower, Bank_Manager, Loan_Amount) is: (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)