GATE Databases (DBMS) previous year questions

D appears on no right-hand side, so every key contains D. AD+ = {A,D} → BC → CD gives E: all attributes. ED+: E → A, A → BC: all. CD+: CD → E, E → A, A → B: all. Single D alone gives only D. BD+ = {B,D}, not a key. Each of AD, ED, CD is minimal (A, E, C alone do not give D). Any other key containing D would be a superset of these. Answer: (A) AD, ED and CD.

Concept: Attributes never on a right side must be in every key. The trap is (C), forgetting that D can never be derived.

s is the referencing relation, r the referenced one. Inserting into r only adds possible targets, so no s tuple loses its match. Deleting from s removes referencing tuples, which can never leave a dangling reference. Deleting from r can remove an A value still used by some E in s, and inserting into s can add an E value absent from r - both can violate. Answer: (A), (B).

Concept: Referential integrity: only deletes/updates on the referenced table and inserts/updates on the referencing table can break it. The trap is mixing up the two directions.

A non-leaf node with p node pointers holds p - 1 keys and no record pointers. Need 10p + 11(p - 1) ≤ 4096, so 21p ≤ 4107 and p ≤ 195.57. Maximum p = 195. Answer: 195.

Concept: Internal B+ tree nodes carry only child pointers and keys. The trap is including the data record pointer size, which belongs to leaf nodes.

3 goes to leaf [1, 5], which overflows as [1, 3, 5] and splits into two leaves, pushing a new separator (3, as the largest key of the left leaf [1, 3] | [5]) into the root. The root would then hold 3, 5, 8 with four children, exceeding 3 pointers, so it splits: [3] and [8] become two internal nodes and the middle key 5 moves up into a new root. The same result follows if the leaf splits as [1] | [3, 5]. New root: 5. Answer: (A) 5.

Concept: Cascading splits in a B+ tree: leaf keys are copied up, internal keys are moved up. The trap is stopping after the leaf split and answering 3, 5 and 8.

X ⋈ Y joins on (P, Q): (P1, Q1, R1, 2) and (P3, Q3, R2, 1); (P2, Q2) has no match in Y. Selecting Q = Q3 or R = R2 keeps only (P3, Q3, R2, 1). Y ⋈ Z joins on P: (P1, Q1, 2, T1), (P1, Q2, 5, T1), (P3, Q3, 1, T2). Selecting S > 1 keeps the two P1 rows. Joining the left result (P3 only) with the right (P1 only) on P, Q, S gives no common tuple. Answer: (D) Zero rows.

Concept: Natural join matches on all common attributes. The trap is joining on P alone or forgetting that the outer join also matches on Q and S.

(A) The derived table gives (TeamID, size) per team; joining each employee to its team's row yields the required output. (B) The extra condition A.EmpID = B.EmpID pairs each employee only with itself, so every count is 1. (C) Grouping by the primary key EmpID gives 1 for everyone. (D) The derived table has no TeamID column, so B.TeamID is invalid and the query errors. Answer: (A).

Concept: Aggregate per group, then join back. The trap is (B): the self-join condition on EmpID destroys the grouping by team.

The condition ∃z ∈ X (t[P] = z[P]) is always true (take z = t). So the result is the tuples of X whose P value has some Y tuple with S > 1. P1: S = 10, 15, yes. P2: S = 20, yes. P3: only S = 1, no. TRC returns a set, so the answer is 2 tuples (P1, Q1, R1) and (P2, Q2, R2). The key also accepts 3, which matches counting P1 once per matching Y tuple (bag semantics). Answer: 2 (accepted range 2 to 3).

Concept: Existential quantifiers filter, they do not multiply tuples. The trap is counting P1 twice because two Y tuples match it.

For each account, compare (number of strictly larger balances) with (number of strictly smaller balances). A1: 3 larger, 0 smaller, kept. A2: same as A1, kept. A3 (10000): 2 larger, 2 smaller, kept (2 ≥ 2). A4: 1 larger, 3 smaller, rejected. A5: 0 larger, 4 smaller, rejected. Rows returned: A1, A2, A3. Answer: 3.

Concept: Correlated subqueries evaluated per outer row. The trap is treating the equal balances 5000 and 5000 as smaller/larger than each other; strict comparisons ignore ties.

E1 → E1(A11, A12, A13). E2 without the multi-valued attribute → E2(A21, A23). The multi-valued attribute needs its own relation → E2_A22(A21, A22) with key (A21, A22); keeping it inside E2 would repeat A23 and break normal form. The many-to-many relationship needs its own relation → R12(A11, A21). Total participation on an M:N relationship does not allow merging R12 into either entity. Total: 4. Answer: 4.

Concept: Mapping M:N relationships and multi-valued attributes. The trap is merging R12 into an entity because of total participation, which only works for 1:1 or 1:N.

(A) P → R says nothing about Q alone; counterexample: Q constant across tuples with different R. False. (B) A composite determinant need not have a single-attribute determinant (e.g. date and room determine booking). False. (C) Augment P → R with Q: PQ → RQ, so PQ → R; similarly PQ → S; union gives PQ → RS. True. (D) Augmentation (or adding attributes to the left side) preserves the FD: PQ → R. True. Answer: (C), (D).

Concept: Armstrong's axioms: you may enlarge the left side (augmentation) but never shrink it. (A) and (B) both try to shrink a determinant.

3NF synthesis always yields a lossless, dependency-preserving decomposition (A true). Any relation is in 1NF already (atomic values), so the trivial decomposition preserves all dependencies (B true). BCNF decomposition is always lossless but may lose dependencies, e.g. R(A,B,C) with AB → C, C → B (C true). Since a 3NF dependency-preserving decomposition always exists and 3NF implies 2NF, a dependency-preserving 2NF decomposition always exists, so (D) is false. Answer: (A), (B), (C).

Concept: Dependency preservation is guaranteed up to 3NF and can fail only at BCNF. (D) is false because the 3NF result is also in 2NF.

⟨u, v⟩ ∈ R binds u to P and v to Q. ⟨v, w⟩ ∈ S binds the same v to X. So we need R tuples whose Q value equals some S tuple's X value, and we output P. That is Π_P(R ⋈_(R.Q = S.X) S), and join is commutative, so Π_P(S ⋈_(S.X = R.Q) R). Answer: (B).

Concept: Translate shared variables into join conditions by position. The trap is joining on P (the projected attribute) or on S.Y (the existentially discarded w).

A superkey must contain a candidate key: it must include A and at least one of B, C. Choices for {B, C}: {B}, {C}, {B, C} (3 ways); D in or out (2 ways). Total 3 × 2 = 6: AB, AC, ABC, ABD, ACD, ABCD. Check by inclusion-exclusion: 2² + 2² - 2¹ = 6. Answer: 6.

Concept: Superkeys = supersets of some candidate key; count with inclusion-exclusion over the keys. Double-counting supersets of both AB and AC (ABC, ABCD) gives 8.

In the three-schema architecture: the logical (conceptual) schema describes the relations; the physical schema describes storage - file organization and indexes; the external schema describes the views presented to users. So I → N, II → M, III → L. Answer: (C).

Concept: Three levels of data abstraction (ANSI/SPARC). The trap is mapping the external schema to relations instead of views.

Two operations on the same item by different transactions conflict if at least one of them is a write. Read-write, write-read and write-write all conflict. Only read-read does not. Answer: (A).

Concept: Definition of conflicting operations used in conflict serializability. The trap is thinking only write-write conflicts.

Augment X → Y with X: X → XY (since XX = X). Augment X → Z with Y: XY → YZ. Transitivity on X → XY and XY → YZ gives X → YZ. Only augmentation and transitivity are used, and neither alone suffices (transitivity cannot combine right-hand sides; augmentation cannot chain). Answer: (D).

Concept: The standard derivation of the union rule. The trap is (A): all three axioms are sufficient but reflexivity is not necessary.

S1: A hash index locates a record by hashing its exact key value; there is no ordering to let one entry cover a range of keys, so every search-key value must have an entry - it is a secondary-style dense index. True. S2: On a file sorted by the search key, a B+ tree can store one entry per data block (leaf entries point to blocks), which is a sparse index. True. Answer: (A).

Concept: Sparse indexing works only when the file is ordered on the key, so the index can point to the block where a key would lie. Hashing has no such order. The trap is assuming B+ trees must always be dense.

In a dependency-preserving decomposition, every functional dependency can be checked inside a single decomposed relation. If it is not preserved, some dependency X → Y spans attributes held in different relations, so to check it on every update the relations must be recombined - that is, joined - before the dependency can be verified. Hence joins are executed more often. Answer: (C) Join.

Concept: Dependency preservation means FDs can be enforced locally. The trap is choosing projection because decomposition itself is a projection; the extra cost of enforcement comes from rejoining.

Inside out: σ_maker="ABC"(Make) gives the models made by ABC. Joining with Car on model gives the cars of those models. σ_color="red" keeps only red ones, so we have red cars made by ABC. Joining with Own on serial attaches the owners of those cars, and π_owner returns the owners. Answer: (C) All owners of a red car made by ABC.

Concept: Reading nested selections and natural joins: selections are conjunctive (red AND ABC), not a union. Option (D) forgets the final projection is onto owner, and (B) invents a count condition.

Inner query: SR Nagar loans are 40000, 25000 and 65000, so MAX = 65000. Outer query keeps loans with amount > 65000: L11 (90000), L23 (80000), L25 (70000). L19 itself (65000) is not strictly greater. Rows returned = 3.

Concept: A scalar subquery with MAX, then a strict comparison. The trap is using >= (which would add L19) or comparing against the SR Nagar total instead of the maximum.

CUBE(D1, D2) produces groupings for every subset of {D1, D2}: (D1, D2): 3 × 2 = 6 rows (D1): 3 rows (D2 shown as NULL) (D2): 2 rows (D1 shown as NULL) (): 1 grand-total row Total = 6 + 3 + 2 + 1 = 12. Answer: (D) 12.

Concept: CUBE over k attributes gives all 2ᵏ grouping sets; ROLLUP gives only the k + 1 hierarchical ones (here 10). The trap is counting just the 6 base groups.

In Customer, aadhaar is only a foreign key with no UNIQUE constraint, so several customers (emails) may share one aadhaar; it is not a candidate key: (A) false. phone has no NOT NULL constraint, so it can be NULL: (B) true. In Person, aadhaar is the PRIMARY KEY, hence a candidate key: (C) true, and primary keys cannot be NULL: (D) false. Answer: (B), (C).

Concept: A foreign key need not be unique or non-null; a primary key is always unique and NOT NULL. The trap is assuming the referencing column inherits the key property of the referenced one.

A⁺ = A, B, C, E, F, then D, G from E: all of ABCDEFG, so A is a key. BC⁺ = B, C, A, then everything A gives: BC is a superkey; B⁺ = B and C⁺ = C, so BC is minimal - a candidate key. E⁺ = E, D, G only: not a key. D, F, G appear only on right sides, so any key must reach A; the candidate keys are exactly A and BC: (A), (C) false; (B) true. E → DG has a left side that is not a superkey, so R violates BCNF: (D) true. Answer: (B), (D).

Concept: Find keys with attribute closures and check minimality; BCNF requires every non-trivial FD's left side to be a superkey. The trap is missing BC as a key because BC → A is listed last.

The division returns customers who have a loan at every branch. Branches: Banjara Hills, Kondapur, SR Nagar, Balanagar. Anand: Banjara Hills only. Ankita: SR Nagar. Gopal: SR Nagar. Sunil: Balanagar (L23), Kondapur (L25). Karteek: Banjara Hills (L11), Kondapur (L14), SR Nagar (L22), Balanagar (L23) - all four. Result: {Karteek}, so 1 tuple.

Concept: Relational division answers 'for all' queries. The trap is counting customers with loans at many branches (Sunil) or counting loans rather than distinct branches.

T1 writes Y. Then T2 reads Y and T3 reads Y, both before T1 finishes - dirty reads of T1's uncommitted value. When T1 aborts, the value both read never existed, so both must be rolled back (cascading rollback). R2(X) reads X, which T1 only read, so it is harmless. Answer: Both T2 and T3 (C).

Concept: Cascading aborts come from reading uncommitted writes. The trap is focusing on X (read by T1 and T2) - read-read sharing causes no dependency.

23 > 19, so it goes to the rightmost leaf [20, 21, 22], which is already full (3 keys). The leaf splits, e.g. into [20, 21] and [22, 23], and a separator (22) must be pushed into the root. The root [6, 12, 19] is also full, so it splits too, and a new root is created above the two halves. Nodes go from 5 to 8 and the height grows by one level. So (A) and (C) are false; (B) and (D) are true. Answer: (B), (D).

Concept: B⁺ tree insertion: a full leaf splits and pushes a key up; a full parent splits recursively, and a root split is the only way height increases. Every node on the path here is full.

p must range over players (it supplies pname), and there must exist a team t with the same tid whose tname is 'MI'. That is exactly (A). (B) and (D) range p over teams, which has no pname. (C) drops the join p.tid = t.tid, so it returns every player whenever an MI team exists. Answer: (A).

Concept: TRC joins need an explicit equality on the linking attribute. Missing the join condition gives all tuples when the existential is satisfiable.

name → city, owner, so name is a key of team. Every FD has a superkey on the left, so team is in BCNF (and hence 3NF): (A) and (D) false. t1 and t2 are two-attribute relations with name as key: both in BCNF, (B) true. The common attribute name is a key of t1 (and t2), so the join is lossless: (C) true. Answer: (B), (C).

Concept: BCNF: every non-trivial FD's determinant is a superkey. Lossless binary decomposition: the shared attributes must be a key of one side. Decomposing an already-BCNF relation is allowed, just unnecessary.

Mark has cid 102. The team with cid 102 is tid 10 (MI). Members of team 10: pids 1 and 3. Their ages: Jasprit 31, Ishan 26. MIN(age) = 26. Answer: 26.

Concept: Nested IN subquery with a three-way join. Trap: matching coach id to the wrong team or taking the minimum over all players (24).

Both transactions read A = 11000 before either wrote, and one debit overwrote the other: a lost update, so the interleaving was not equivalent to any serial order and Isolation was violated. The database ended with Rs. 20000 credited but only Rs. 10000 debited, so the invariant that transfers preserve total money broke: Consistency was violated (serially, the second transfer would have been refused for insufficient funds). Each transaction did all its own writes and the results persisted, so Atomicity and Durability are not certainly violated. Answer: (B) and (C).

Concept: Lost-update anomaly: a concurrency (isolation) failure that produces an inconsistent state. The trap is choosing atomicity because money went missing; each transaction itself completed fully.

R1: A and D appear on no right side, so both are in every key. AD+ = {A, D, E, B, C}, so AD is the only candidate key and the prime attributes are A, D. D → E: D is not a superkey and E is not prime, so R1 is not in 3NF. R2: C appears on no right side; C+ = {C, A, D, B}, so C is the only key and C is the only prime attribute. A → D: A is not a superkey and D is not prime (a transitive dependency C → A → D), so R2 is not in 3NF. Answer: (C) and (D).

Concept: 3NF requires every nontrivial X → Y to have X a superkey or Y prime. Find keys first using attributes that never appear on a right side; both schemas contain a dependency from a non-key to a non-prime attribute.

Conflicting pairs: T1's R1(X), R1(X), W1(X) with W2(X); W1(Y) with W2(Y). (A) R1(X) before W2(X): T1→T2; second R1(X) after W2(X): T2→T1. Cycle, not serializable. (B) W2(X) before both R1(X) and W1(X), W2(Y) before W1(Y): all edges T2→T1. Acyclic, equivalent to T2, T1. (C) R1(X) before W2(X): T1→T2; second R1(X) after W2(X): T2→T1. Cycle. (D) W2(X) before R1(X): T2→T1; W1(Y) before W2(Y): T1→T2. Cycle. Answer: (B).

Concept: Build the precedence graph from every conflicting pair (same item, different transactions, at least one write). The repeated R1(X) is the trap: a write by T2 between T1's two reads creates the back edge.

(A) The correlated EXISTS subquery is true exactly when the student has an Enrolled row for 1470: correct. (B) SIZEOF is not an SQL function, so the query is invalid. (C) COUNT(*) of matching Enrolled rows greater than 0: correct. (D) Students and Enrolled share only rollno, so the natural join pairs each student with their enrollments; filtering courseno = 1470 returns the right names: correct. Answer: (A), (C), (D).

Concept: Several SQL forms express "exists a matching row": EXISTS, COUNT > 0, or a join. Check that a natural join uses only the intended common column, and spot non-standard functions.

Leaf C is full, so inserting 23 splits {20, 22, 23, 25, 26} into two leaves, and the new leaf's smallest key (23 for a 2|3 split, 25 for a 3|2 split) is copied into B. B = (7, 19, 33, 44) is also full and becomes 5 keys, e.g. (7, 19, 23, 33, 44). An internal split pushes the middle key up to A: left node (7, 19), right node (33, 44), with 23 (or 25) moving to A. The leaf containing 25* covers keys from 23 (or 25) up to 33, so it hangs from the leftmost pointer of the right node (33, 44). Either split choice gives that parent, whose smallest key is 33. Answer: 33.

Concept: Leaf splits copy a key up, but internal splits move the middle key up without keeping it. The trap is assuming the separator stays in the leaf's parent, which would give 23 or 25.

'Every Team name is in Defender or Forward' means Π_name(Team) ⊆ Π_name(Defender) ∪ Π_name(Forward). A subset test A ⊆ B is written A \ B = ϕ. So Π_name(Team) \ (Π_name(Defender) ∪ Π_name(Forward)) = ϕ. Option (A) uses ∩ (would demand both), and (B), (D) test the reverse inclusion. Answer: (C).

Concept: Expressing set containment with difference. The traps are 'either...or' mapped to intersection and getting the direction of the difference backwards.

The join condition pairs each Team row with its Raider. Jaipur rows have IDs 2, 1 and 6. Their RaidPoints are 219 (Ankush), 250 (Arjun) and 215 (Gopal), all greater than 200. So 3 rows are returned. Answer: 3.

Concept: Evaluating a join with selection predicates. The trap is filtering on BidPoints instead of RaidPoints, or treating > 200 as ≥ 200 (Sunil has exactly 200 but plays for Patna anyway).

Compute closures. (VW)⁺: start {V, W}; V → X adds X; WX → Y and WX → Z add Y, Z. So (VW)⁺ = {V, W, X, Y, Z}, which contains Y and Z but not U. Hence VW → YZ (A) and VW → Y (D) hold, VW → U (C) does not (no FD has U on the right). (B) WX → YZ follows from WX → Y and WX → Z by the union rule. Answer: (A), (B), (D).

Concept: Attribute closure decides FD implication. The trap is (C): nothing determines U, so U must be in every key.

In a B+ tree every node except the root must be at least half full. The root may hold as few as one key (two children if internal), since it is created and removed as the tree grows and shrinks. Answer: (A) Only the root node.

Concept: The minimum-occupancy rule exempts only the root. The trap is thinking leaves are exempt.

1NF only requires every attribute to be atomic (single-valued, not composite or multivalued). (A) True: a key may consist of several attributes. (B) False: 1NF says nothing about foreign keys. (C) True: a composite attribute is not atomic. (D) False: any number of candidate keys is allowed. Answer: (A), (C).

Concept: 1NF is purely about atomic values. The trap is reading key-related restrictions into 1NF.

Join on A: R(10,20) with S(10,90); R(30,40) with S(30,45); R(30,50) with S(30,45). R tuples with A = 20 and 50 have no match. Apply B < C: 20 < 90 yes, 40 < 45 yes, 50 < 45 no. Result has 2 tuples. Answer: 2.

Concept: Do the equi-join first, then filter. The trap is forgetting that A = 30 appears twice in R and produces two joined rows.

(A) False: a determinant cannot be shrunk; X alone need not determine Z, W. (B) True by decomposition. (C) True by pseudo-transitivity: W → Y gives XW → XY, and XY → Z, so XW → Z. (D) True by transitivity. Answer: (B), (C), (D).

Concept: Armstrong's axioms let you split the right side or augment the left, never drop attributes from the left. The trap is accepting (A).

Conflicts on y: r3(y) before w2(y), w3(y) before r2(y), w3(y) before w2(y), all giving T3 → T2. T1 touches only z, which nobody else uses, and x is used only by T2. The precedence graph has the single edge T3 → T2, so any serial order with T3 before T2 is conflict equivalent: T1T3T2, T3T2T1, T3T1T2. Answer: (B), (C), (D).

Concept: Conflict-equivalent serial orders are the topological orders of the precedence graph. The trap is assuming T1 must come first because it starts first.

The ACID property that guarantees committed changes survive failures (via the log and redo during recovery) is durability. Atomicity is all-or-nothing execution, consistency is preserving integrity constraints, isolation is non-interference of concurrent transactions. Answer: (A) durability.

Concept: Distinguishing the ACID properties. Durability is about committed work surviving crashes; atomicity is about undoing partial work.

A weak entity cannot be identified without its owner, so every weak entity must take part in the identifying relationship: total participation on the weak side. An owner may have no weak entities at all (an employee with no dependents), so its participation need not be total. Answer: (A).

Concept: Weak entity sets depend on an identifying owner, hence total participation (double line) on the weak side only.

A full scan must read every record. In sorted and heap files the records are packed into data pages that are read once each, about B I/Os for B pages. Scanning through an unclustered index fetches records in index order, jumping between data pages, which can cost one I/O per record plus the index pages; a hash index also leaves buckets partly empty. So only sorted and heap files are I/O efficient for a scan. Answer: (A), (B).

Concept: Scan cost depends on physical layout. Unclustered indexes help point or range lookups but make full scans expensive because of random page accesses.

(A) True: every 2PL schedule is conflict serializable (equivalent to the order of lock points). (B) False: releasing right after each operation and locking again later violates the growing/shrinking rule. (C) True: that is the definition of 2PL. (D) True: two transactions each holding one lock and waiting for the other's lock deadlock under basic 2PL. Answer: (A), (C), (D).

Concept: 2PL guarantees serializability but not freedom from deadlock or cascading aborts. Statement B describes short-duration locking, which breaks 2PL.

With no aggregation, 'at least 3 persons' needs three distinct tuples of Person in the same city. Rename three copies P1, P2, P3 and form P1 × P2 × P3 (2 cross products), select P1.city = P2.city = P3.city with P1.pid ≠ P2.pid, P2.pid ≠ P3.pid, P1.pid ≠ P3.pid, and project city. With one cross product only two persons can be compared at once, which cannot certify three. Answer: (B) 2.

Concept: Counting to k without aggregation needs k copies of the relation, i.e. k − 1 cross products, with pairwise inequality on the key.

Each attribute goes to X, to Y, or to neither: 3⁴ = 81 assignments with X and Y disjoint. Remove those with X empty (each attribute in Y or neither: 2⁴ = 16) and those with Y empty (16), adding back the one with both empty: 81 − 16 − 16 + 1 = 50. Answer: 50.

Concept: Counting ordered pairs of disjoint non-empty subsets via 3ⁿ and inclusion-exclusion. Forgetting to add back the both-empty case gives 49.

The degree (arity) of a relation is the number of attributes (columns) in its schema. The number of tuples (rows) is its cardinality. Answer: (A) Number of attributes of its relation schema.

Concept: Degree = number of attributes; cardinality = number of tuples. Swapping these two terms is the standard trap.

Female students: rollNum 2 (70), 3 (80), 5 (65). marks > 65 is strict, so Swati with 65 is excluded. Rows returned: rollNum 2 and 3. Duplicated names do not matter since SELECT * returns whole rows. Answer: 2.

Concept: Straightforward WHERE filtering; the traps are the strict inequality at 65 and wondering whether the duplicate name "Aliya" collapses rows (it does not without DISTINCT, and rows differ anyway).

Records per data block = floor(1024/100) = 10, so data blocks = 25000/10 = 2500. The primary (sparse) index has one entry per data block: 2500 entries of 15 + 5 = 20 bytes. Entries per index block = floor(1024/20) = 51, so index blocks = ceil(2500/51) = 50. Binary search on the index: ⌈log2 50⌉ = 6 block accesses, which identifies the data block. Answer: 6.

Concept: A primary index is sparse: one entry per data block, not per record. The question stops at identifying the block, so the extra data-block access (7) is not counted.

(A) True: with attributes {A, B}, any non-trivial FD is A→B or B→A, and its left side is then a key; with none, the key is AB. Either way every determinant is a superkey, so BCNF. (B) False: all-prime relations are in 3NF but can violate BCNF (e.g. R(A,B,C), AB→C, C→B). (C) False: a relation whose only key is all attributes has no non-prime attribute. (D) False: BCNF decomposition may lose dependencies. Answer: (A).

Concept: Two-attribute relations are always BCNF. The trap is (B), which guarantees only 3NF.

(A) Division of Own by all brand ids is exactly 'owns every brand'. (B) Π_eId(Own) × Π_bId(Brand) lists every (owner, brand) pair that should exist; subtracting Own leaves the missing pairs; their eIds are owners missing some brand; removing them leaves owners of all brands - the expansion of division. (C) Divides by brands that someone owns, so an unowned brand is ignored - wrong. (D) The cross product pairs every owner with every owned brand regardless of actual ownership - wrong. Answer: (A), (B).

Concept: R ÷ S = Π_X(R) − Π_X((Π_X(R) × S) − R). The trap is dividing by Π_bId(Own) instead of Brand.

A and B never appear on a right-hand side, so every key must contain both. AB⁺: AB → C, then BC → D, C → E, so AB⁺ = ABCDE and AB is the only candidate key. A superkey is any superset of AB: the remaining attributes C, D, E can each be in or out, giving 2³ = 8 superkeys. Answer: 8.

Concept: Attributes absent from every right side belong to every key. With a single candidate key of size k in n attributes, there are 2ⁿ⁻ᵏ superkeys.

Conflicting pairs (same item, different transactions, at least one write), in schedule order: on x - R₄(x) before W₂(x) gives T₄ → T₂; R₃(x) before W₂(x) gives T₃ → T₂. On y - R₁(y)/W₁(y) before W₃(y) gives T₁ → T₃; W₁(y) before R₄(y) gives T₁ → T₄; W₃(y) before R₄(y) gives T₃ → T₄. The precedence graph is acyclic with unique topological order T₁ → T₃ → T₄ → T₂. Answer: (A).

Concept: Build the precedence graph from conflicting operations and topologically sort it. The trap is missing W₃(y) → R₄(y), which forces T₃ before T₄.

The subquery lists D01 courses the student has NOT registered for; NOT EXISTS keeps students for whom that list is empty, i.e. students registered for every D01 course. D01 courses: C11, C12. S01 has C11 and C12: kept. S02 has only C11: dropped. S03 has neither: dropped. S04 has C11 and C12: kept. S05 has C11 and C21 (no C12): dropped. Rows returned = 2. Answer: 2.

Concept: NOT EXISTS (required EXCEPT taken) is SQL's relational division. The student's own department does not matter here, which is the usual trap.

Many-to-one from E1 to E2 means each E1 entity relates to at most one E2 entity; total participation of E1 makes it at least one, so exactly one (A true, B false). Since |E1| > |E2| and every E1 entity maps into E2, by pigeonhole some E2 entity is associated with more than one E1 entity, so (C) and (D) are false. Answer: (A).

Concept: Combining mapping cardinality (at most one) with total participation (at least one) gives exactly one. The trap is reading "many-to-one" backwards and picking (B).

The inner join returns the matching rows. The left outer join adds Book rows with no match (copies NULL); the right outer join adds Stock rows with no match (B.isbn NULL). The full outer join contains the matching rows plus both sets of unmatched rows, so it always contains the output of each of the other three. Answer: (D) Query 4.

Concept: FULL OUTER JOIN = INNER JOIN rows plus unmatched rows from both sides, so it is a superset of LEFT, RIGHT and INNER joins. The trap is choosing LEFT or RIGHT, which each miss the other side's unmatched rows.

Q returns the r tuples with B < 5 joined with their s tuple. Because r.B is a foreign key into s, every r tuple has a matching s tuple, so r LOJ s = r ⋈ s. (A) Selection pushed past the join on the join attribute: equivalent. (B) r LOJ s = r ⋈ s, then select: equivalent. (D) σ_(B<5)(r) LOJ s = σ_(B<5)(r) ⋈ s: equivalent. (C) r LOJ σ_(B<5)(s) keeps every r tuple with B ≥ 5 too, padded with C = NULL, so it has extra rows: NOT equivalent. Answer: (C).

Concept: Foreign keys guarantee matches, so a left outer join from the referencing side behaves like a natural join, unless the referenced side is filtered first. The trap is assuming the filter position never matters in outer joins.

I: a set-valued attribute violates 1NF, so it is not in 3NF. II: candidate keys are {rollno, courseid} and {email, courseid}. email → rollno has a non-superkey left side, so not BCNF, but rollno is a prime attribute, so 3NF holds. III: marks → grade is a transitive dependency of a non-prime attribute: not 3NF. IV: courseid → credit is a partial dependency: not even 2NF. Answer: (B) Schema II.

Concept: 3NF allows X → A with X not a superkey only when A is prime; BCNF does not. The classic 3NF-not-BCNF case is a non-key attribute determining part of a key. The trap is missing the second candidate key {email, courseid}.

A superkey is any attribute set containing a key. With no other information, a set is guaranteed to be a superkey if it contains both V and Y. VXYZ, VWXY and VWXYZ all contain V and Y. VWXZ lacks Y. Answer: (B) VWXZ.

Concept: Superkey = superset of a candidate key. The trap is over-thinking about other dependencies; only containment of VY is guaranteed.

ACID stands for Atomicity, Consistency, Isolation and Durability. Deadlock-freedom is a property of some concurrency-control protocols, not one of the ACID properties. Answer: (D).

Concept: Recall of the ACID acronym; the missing D is Durability, which the distractor tries to replace with Deadlock-freedom.

Old schema: (VOLUME, NUMBER) → YEAR has a proper subset of the key determining a non-prime attribute, a partial dependency. So the old schema is in 1NF but not 2NF. New schemas: in the first, the full key determines TITLE and PRICE; in the second, (VOLUME, NUMBER) is the key and determines YEAR. Every determinant is a key, so both are in BCNF, hence also in 2NF. The weakest normal form satisfied by the new design but not by the old is 2NF. Answer: (B).

Concept: Partial dependencies violate 2NF. The trap is answering BCNF because the new design is in BCNF; the question asks for the weakest form that separates the two.

All locks are acquired before any is released, so the protocol is two-phase locking (in fact conservative/strict), which guarantees conflict serializability. All transactions acquire locks in the same global order (increasing address). A wait-for cycle would require some transaction to hold a higher-addressed lock while waiting for a lower one, which the ordering forbids. So no deadlock can occur. Answer: (A).

Concept: 2PL gives serializability; a global lock ordering removes circular wait. The trap is recalling that basic 2PL can deadlock and missing the ordered acquisition.

B+ tree insertion and deletion split, merge or redistribute nodes and only grow or shrink the tree at the root, so every leaf is always at the same depth. That equal root-to-leaf path length is what "balanced" means here. Node occupancy is bounded (at least half full) but not within 1 of a sibling. Answer: (A).

Concept: B and B+ trees are perfectly height-balanced: all leaves at one level. The "differ by at most 1" option describes AVL trees, which is the trap.

A schedule is conflict serializable exactly when its precedence graph is acyclic. Each edge Ti → Tj means Ti must come before Tj in an equivalent serial schedule, so any topological order of the graph gives a valid serial order. DFS or BFS order, or index order, can violate an edge. Answer: (A) Topological order.

Concept: Conflict serializability test: acyclic precedence graph, serial order = topological sort. Assuming T1, T2, ... index order is the trap.

Neither transaction reads a value written by the other: r₂(X), r₁(X), r₂(Y), r₁(Y) all read original values, since the only writes are w₁(X) and w₂(X) and no read follows them. With no dirty reads, aborting T₁ forces no other abort, so there is no cascading abort (and the schedule is trivially recoverable, ruling out A and B). It is not strict: w₂(X) overwrites X written by T₁ before T₁ commits or aborts. Answer: (C).

Concept: Recoverable, cascadeless and strict differ by what may follow an uncommitted write: a read (cascadeless forbids) or any read or write (strict forbids). A blind overwrite breaks strictness but not cascadelessness.

total: Ajmer 20, Bikaner 40, Churu 30, Dungargarh 10. total_avg = (20 + 40 + 30 + 10)/4 = 25. Districts with total ≥ 25: Bikaner and Churu. Answer: 2.

Concept: Group-wise aggregate, then an average of those aggregates. Averaging the raw capacities (100/7 ≈ 14.3) instead of the district totals gives 3, the trap.

E, F and H never appear on the right side of any FD, so every key must contain all three. {E, F, H}⁺: EF → G; F → I, J; EH → K, L; K → M; L → N. Closure = {E, F, G, H, I, J, K, L, M, N} = R. So {E, F, H} is a key, and it is minimal since none of E, F, H can be derived. {E, F} misses H; {E, F, H, K, L} is a superkey but not minimal. Answer: (B) {E, F, H}.

Concept: Attributes absent from every right-hand side must be in every candidate key; then check the closure. The trap is choosing the non-minimal superkey in option C.

S2: a foreign key must reference the whole primary (or a unique) key of the referenced table. R's primary key is (a, b), but S declares FOREIGN KEY (a) references R, a single column, so the definition is invalid. S2 is FALSE. S1: a foreign key also carries referential actions (ON DELETE / ON UPDATE CASCADE, SET NULL) and is enforced when the referenced table changes; a CHECK constraint on the referencing table cannot express that (standard CHECK cannot even refer to other tables in most systems). So it cannot always be replaced. S1 is FALSE. Answer: (D) Both S1 and S2 are FALSE.

Concept: Foreign keys must match the full referenced key, and they are more than a static check. The trap is accepting S2 because column a exists in R - a partial key is not a valid reference target.

By definition an attribute is prime if it is a member of at least one candidate key of R; otherwise it is non-prime. This is the notion 3NF uses. Answer: (B) in some candidate key of R.

Concept: Definition of prime attribute, used in 2NF and 3NF tests. The trap is option A (all candidate keys) or D (primary key only); any candidate key suffices.

The join pairs each employee with his/her own dependents (empId = eId) whose age is at least the employee's age. Its projection is the set of employees having at least one own dependent at least as old as themselves. Subtracting this from all employees leaves those for whom no own dependent has depAge ≥ empAge, i.e. the employee is older than every one of his/her dependents. Answer: (D) all of his/her dependents.

Concept: Set difference turns 'exists a counterexample' into 'for all'. The trap is option B: the join condition empId = eId restricts the comparison to the employee's own dependents, not all dependents.

For each employee E, the subquery lists E's customers whose rating is not 'GOOD'. NOT EXISTS keeps E only when that list is empty, i.e. E has no customer with a non-GOOD rating. Since every employee has at least one customer, this means all of E's customers are rated 'GOOD'. Answer: (D).

Concept: NOT EXISTS over a negated condition expresses universal quantification. The trap is option C, which ignores the <> in the subquery.