DBMS GATE Practice Set
Mixed GATE-style questions on ER mapping, keys, relational algebra, SQL, functional dependencies, normal forms, transactions and indexing.
These questions mix every lesson of the Databases track in the three GATE formats. Work each one out on paper first: write the closures, draw the precedence graph, or trace the query row by row. Every numerical answer is recomputed by a program when the site is built, and every SQL question is run on a real SQLite database.
Design and keys
[ER model] E1 and E2 are in a 1:N relationship, E2 and E3 are in an M:N relationship, and E3 has one multivalued attribute. All entity sets are strong and all participations are partial. What is the minimum number of tables?
Show answer and explanation
Answer: 5E1, E2 and E3 need a table each. The 1:N relationship becomes a foreign key in E2. The M:N relationship needs its own table, and so does the multivalued attribute. 3 + 1 + 1 = 5.
[Superkeys] R(A, B, C, D, E, F) has exactly two candidate keys, {A, B} and {C, D}. How many superkeys does R have?
Show answer and explanation
Answer: 28Supersets of {A, B}: 2⁴ = 16. Supersets of {C, D}: 2⁴ = 16. Supersets of both, {A, B, C, D}: 2² = 4. Total 16 + 16 − 4 = 28.
[Candidate keys] R(A, B, C, D, E, F, G, H) has F = { AB → C, BC → D, D → E, E → AB, F → GH }. How many candidate keys does R have?
Show answer and explanation
Answer: 4F appears on no right side, so it is in every key, and F → GH covers G and H. The rest must determine A to E: D → E → AB and then AB → C, so D works; E → AB → C → (BC → D), so E works; AB → C → D works; BC → D → E → A works. Keys: DF, EF, ABF, BCF.
[Normal forms] R(A, B, C, D, E) has F = { AB → C, C → D, D → B }. What is the highest normal form of R?
Show answer and explanation
Answer: CE is on no right side and the keys are ABE, ACE and ADE, so A, B, C, D and E are all prime. Every FD has a prime right side, so R is in 3NF. None of AB, C, D is a superkey (none contains E), so R is not in BCNF.
[Normal forms] R(A, B, C, D, E, F) has F = { AB → C, C → D, D → E, E → F }. What is the highest normal form of R?
Show answer and explanation
Answer: BThe only key is AB. Nothing depends on A or B alone, so R is in 2NF. C → D has a non-superkey left side and a non-prime right side (a transitive dependency), so R is not in 3NF.
[Minimal cover] How many FDs are in a minimal cover of F = { AB → C, A → D, D → B, C → E, AD → E }?
Show answer and explanation
Answer: 4A⁺ = {A, D, B, C, E}, so in AB → C the B is extraneous (A → C), and in AD → E the D is extraneous (A → E). Then A → E is redundant (A → C → E). The minimal cover is { A → C, A → D, D → B, C → E }: 4 FDs.
[Decomposition] R(A, B, C, D) has F = { A → B, B → C }. Which decompositions are lossless? (Select all that apply.)
Show answer and explanation
Answer: A, BTest the common attributes. A is a key of R₁(A, B): lossless. B is a key of R₁(B, C): lossless. No common attribute: lossy. Common attribute C determines nothing: lossy.
Queries
[Relational algebra] R and S are union-compatible, with 100 and 50 tuples. What is the minimum possible number of tuples in R ∪ S?
Show answer and explanation
Answer: 100The union is smallest when every tuple of S is also in R; then R ∪ S = R, with 100 tuples. The maximum, with no common tuple, is 150.
[SQL] What does the query print?
CREATE TABLE emp (id INTEGER, dept TEXT, salary INTEGER, bonus INTEGER);
INSERT INTO emp VALUES (1, 'A', 100, 10), (2, 'A', 200, NULL), (3, 'B', 150, 20),
(4, 'B', 250, 30), (5, 'B', 300, NULL), (6, 'C', 400, NULL), (7, 'C', 100, 40);
SELECT SUM(salary) FROM emp WHERE bonus IS NULL OR bonus > 25;Show answer and explanation
Answer: 1250Rows with a NULL bonus are 2, 5 and 6 (200 + 300 + 400), and rows with bonus > 25 are 4 and 7 (250 + 100). The sum is 1250.
[SQL] For the same emp table, what does this query print?
CREATE TABLE emp (id INTEGER, dept TEXT, salary INTEGER, bonus INTEGER);
INSERT INTO emp VALUES (1, 'A', 100, 10), (2, 'A', 200, NULL), (3, 'B', 150, 20),
(4, 'B', 250, 30), (5, 'B', 300, NULL), (6, 'C', 400, NULL), (7, 'C', 100, 40);
SELECT COUNT(*) FROM (SELECT dept FROM emp GROUP BY dept HAVING AVG(salary) > 180);Show answer and explanation
Answer: 2Average salaries: A = 150, B = 700 / 3 ≈ 233.3 and C = 250. Groups B and C pass, so the count is 2.
[SQL NULLs] Table t(x) holds 3, NULL, 5. Which conditions select no rows? (Select all that apply.)
Show answer and explanation
Answer: A, BA comparison with NULL is UNKNOWN, so x = NULL never holds. x NOT IN (3, NULL) is x <> 3 AND x <> NULL, which is FALSE or UNKNOWN for every row. x IS NULL selects the NULL row, and x <> 3 selects 5.
Transactions and indexing
[Serializability] S = R1(A) R2(B) R3(C) W1(B) W2(C) W3(D). To which serial order is S conflict equivalent?
Show answer and explanation
Answer: CR2(B) before W1(B) gives T2 → T1. R3(C) before W2(C) gives T3 → T2. The graph T3 → T2 → T1 has no cycle, and its only topological order is T3, T2, T1.
[Recoverability] Which schedules are recoverable? (Select all that apply.)
Show answer and explanation
Answer: A, C, DA schedule is recoverable when every transaction that reads from another commits after it. In the first, T2 reads from T1 and commits after it. In the second, T2 commits before T1, its source. In the third, T2 reads committed data. In the fourth, nobody reads uncommitted data (T1 read before T2 wrote).
[B+ tree] Blocks are 1024 bytes, keys 14 bytes and block pointers 6 bytes. What is the maximum number of child pointers in an internal node of a B+ tree?
Show answer and explanation
Answer: 516p + 14(p − 1) ≤ 1024 gives 20p ≤ 1038, so p = 51.
[Indexing] A file of 50,000 records of 256 bytes is stored in 2048-byte blocks, unsorted on the key. A dense secondary index uses 10-byte keys and 6-byte block pointers. How many block accesses does a search through the index need (binary search on the index plus one data block)?
Show answer and explanation
Answer: 102048 / 16 = 128 entries per index block. A dense index has 50,000 entries, so ⌈50000 / 128⌉ = 391 blocks. Binary search takes ⌈log₂ 391⌉ = 9 accesses, plus 1 for the data block: 10. A linear search of the 6250-block data file would average 3125.