Database Management System · GATE CSE
Functional Dependencies and Normalization
Q 1 (GATE CSE 2024)
Which of the following statements about a relation R in first normal form (1NF) is/are
TRUE?
A R can have a multi-attribute key
B R cannot have a foreign key
C R cannot have a composite attribute
D R cannot have more than one candidate key
Q 2 (GATE CSE 2022)
In a relational data model, which one of the following statements is TRUE?
A. A relation with only two attributes is always in BCNF.
B. If all attributes of a relation are prime attributes, then the relation is in BCNF.
C. Every relation has at least one non-prime attribute.
D. BCNF decompositions preserve functional dependencies.
Q3 (GATE CSE 2021)
Consider the following statements S1 and S2 about the relational data model:
S1: A relation scheme can have at most one foreign key.
S2: A foreign key in a relation scheme R cannot be used to refer to tuples of R.
Which one of the following choices is correct?
A. S1 is true and S2 is false.
B. Both S1 and S2 are true.
C. Both S1 and S2 are false.
D. S1 is false and S2 is true.
Q4 (GATE CSE 2016)
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?
A 1NF
B 2NF
C 3NF
D BCNF
Q5. (GATE CSE 2015)
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 ?
A {P,R}→{S,T}
B {P,R}→{R,T}
C {P,S}→{S}
D {P,S,U}→{Q}
Q6. (GATE CSE 2014)
Consider the relation schema 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?
A {E,F}
B {E,F,H}
C {E,F,H,K,L}
D {E}
Q 7 (GATE CSE 2012)
Which of the following is TRUE?
A Every relation in 3NF is also in BCNF
B A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on
every key of R
C Every relation in BCNF is also in 3NF
D No relation can be in both BCNF and 3NF
Structured Query Language
Q1 GATE CSE 2020
Consider a relational database containing the following schemas.
The primary key of each table is indicated by underlying the constituent fields.
SELECT [Link], [Link]
FROM Suppliers s, Catalogue c
WHERE [Link] = [Link] AND
Cost > (SELECT AVG (cost)
FROM Catalogue
WHERE pno = ‘P4’
GROUP BY pno);
The number of rows returned by the above SQL query is
A4
B5
C2
D0
Q2 GATE CSE 2018
Consider the following two tables and four queries in SQL.
Book (isbn, bname), Stock (isbn, copies)
Query 1: SELECT [Link], [Link]
FROM Book B INNER JOIN Stock S
ON [Link] = [Link];
Query 2:
SELECT [Link], [Link]
FROM Book B LEFT OUTER JOIN Stock S
ON [Link] = [Link];
Query 3:
SELECT [Link], [Link]
FROM Book B RIGHT OUTER JOIN Stock S
ON [Link] = [Link];
Query 4:
SELECT [Link], [Link]
FROM Book B FULL OUTER JOIN Stock S
ON [Link] = [Link];
Which one of the queries above is certain to have an output that is a superset of the outputs of
the other three queries?
A Query 1
B Query 2
C Query 3
D Query 4
Q3 GATE CSE 2015
SELECT operation in SQL is equivalent to
A the selection operation in relational algebra
B the selection operation in relational algebra, except that SELECT in SQL retains duplicates
C the projection operation in relational algebra
D the projection operation in relational algebra, except that SELECT in SQL retains
duplicates
Q4 GATE CSE 2015
Consider the following relation
Cinema(theater, address, capacity)
Which of the following options will be needed at the end of the SQL query
SELECT [Link]
FROM Cinema P1
such that it always finds the addresses of theaters with maximum capacity?
A WHERE [Link] >= All (select [Link] from Cinema P2)
B WHERE [Link] >= Any (select [Link] from Cinema P2)
C WHERE [Link] > All (select max([Link]) from Cinema P2)
D WHERE [Link] > Any (select max([Link]) from Cinema P2)
Q5 GATE CSE 2012
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
A P and R
B P and S
C Q and R
D Q and S
Transactions and Concurrency
Q GATE CSE 2024
Consider the following read-write schedule S over three transactions T1, T2, and T3, where
the subscripts in the schedule indicate transaction IDs:
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?
Consider the following read-write schedule S over three transactions T1, T2, and T3, where
the subscripts in the schedule indicate transaction IDs:
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?
A T1T2T3
B T1T3T2
C T3T2T1
D T3T1T2
Q GATE CSE 2022
Let Ri(z) and Wi(z) denote read and write operations on a data element z by a
transaction Ti, respectively. Consider the schedule S with four transactions.
S : R4(x), R2(x), R3(x), R1(y), W1(y), W2(x), W3(y), R4(y)
Which one of the following serial schedules is conflict equivalent to S?
A T1 → T3 → T4 → T2
B T1 → T4 → T3 → T2
C T4 → T1 → T3 → T2
D T3 → T1 → T4 → T2
Q GATE CSE 2022
Let S be the following schedule of operations of three transactions T1, T2 and
T3 in a relational database system:
R2(Y), R1(X), R3(Z), R1(Y), W1(X), R2(Z), W2(Y), R3(X), W3(Z)
Consider the statements P and Q below:
P: S is conflict-serializable.
Q: If T3 commits before T1 finishes, then S is recoverable.
Which one of the following choices is correct?
A P is true and Q is false.
B Both P and Q are true.
C P is false and Q is true.
D Both P and Q are false.
Q GATE CSE 2022
Let ri(z) and wi(z) denote read and write operations respectively on a data item z
by a transaction Ti. Consider the following two schedules.
S1 : r1(x) r1(y) r2(x) r2(y) w2(y) w1(x)
S2 : r1(x) r2(x) r2(y) w2(y) r1(y) w1(x)
Which one of the following options is correct?
A S1 is conflict serializable, and S2 is not conflict serializable.
B S1 is not conflict serializable, and S2 is conflict serializable.
C Both S1 and S2 are conflict serializable.
D Neither S1 nor S2 is conflict serializable.
Q GATE CSE 2020
Consider a schedule of transactions T1 and 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
B
C
Q GATE CSE 2020
Consider the following database schedule with two transactions, T1 and T2
S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2
where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z)
denotes a write operation by Ti on a variable Z and ai denotes an abort by
transaction Ti .
Which one of the following statements about the above schedule is TRUE?
A S is non-recoverable
B S is recoverable, but has a cascading abort
C S does not have a cascading abort
D S is strict
Q GATE CSE 2020
onsider 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
A guarantee serializability and deadlock-freedom
B guarantee neither serializability nor deadlock-freedom
C guarantee serializability but not deadlock-freedom
D guarantee deadlock-freedom but not serializability
Relational Algebra
Q GATE CSE 2024
The relation schema, Person(pidpid―, 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
A1
B2
C3
D4
Q GATE CSE 2020
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?
A σB<5(r ⋈ s)
B σB<5(rLOJs)
C rLOJ(σB<5(s))
D σB<5(r)LOJs
Q GATE CSE 2018
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?
A R contains a,b,e,f,g but not c,d.
B R contains all of a,b,c,d,e,f,g.
C R contains e,f,g but not a,b.
D R contains e but not f,g.