GATE Databases 5 — Questions and Answers
Question 1: Which of the following correctly describes a 'lossless join decomposition'?
- The natural join of the decomposed relations produces extra tuples
- The natural join of the decomposed relations exactly reconstructs the original relation (Correct answer)
- Each decomposed relation has fewer attributes than the original
- The decomposition eliminates all functional dependencies
Correct answer: The natural join of the decomposed relations exactly reconstructs the original relation
A lossless join decomposition guarantees that joining the parts back on common attributes yields exactly the original relation with no spurious tuples.
Question 2: In a B-tree (not B+ tree) of order m, the maximum number of keys in a node is:
- m
- m - 1 (Correct answer)
- 2m
- 2m - 1
Correct answer: m - 1
A B-tree node of order m holds at most m children and therefore at most m-1 keys.
Question 3: Which relational algebra expression represents the SQL: SELECT A FROM R WHERE B = 5?
- π_A(σ_{B=5}(R)) (Correct answer)
- σ_{B=5}(π_A(R))
- π_B(σ_{A=5}(R))
- σ_A(π_{B=5}(R))
Correct answer: π_A(σ_{B=5}(R))
First apply selection to filter rows (σ_{B=5}), then apply projection to keep only column A (π_A).
Question 4: The concept of 'data independence' in DBMS most directly refers to:
- Encrypting data so applications cannot read raw storage
- Ability to change the schema at one level without affecting higher levels (Correct answer)
- Storing data on independent disks for redundancy
- Making data accessible without any query language
Correct answer: Ability to change the schema at one level without affecting higher levels
Data independence allows changes to physical or logical schema without requiring application code changes, enabled by the three-level ANSI/SPARC architecture.
Question 5: Which aggregate function in SQL ignores NULL values by default?
- COUNT(*)
- COUNT(*) and SUM both
- SUM, AVG, MIN, MAX but not COUNT(*) (Correct answer)
- None — all aggregate functions treat NULL as zero
Correct answer: SUM, AVG, MIN, MAX but not COUNT(*)
COUNT(*) counts all rows including NULLs; SUM, AVG, MIN, MAX, and COUNT(column) all skip NULL values.
Question 6: In the context of query optimization, a 'selectivity' value close to 0 for a predicate means:
- The predicate selects almost all tuples
- The predicate selects very few tuples (Correct answer)
- The predicate has no effect on the result
- The predicate involves a NULL comparison
Correct answer: The predicate selects very few tuples
Selectivity is the fraction of tuples satisfying the predicate; a value near 0 means very few tuples match, making the predicate highly selective.
Question 7: Which of the following statements about a 'view' in SQL is TRUE?
- A view always stores data physically like a table
- A view is a named query whose result is materialized at creation
- A view is a virtual table defined by a query, computed on access (Correct answer)
- A view cannot reference other views
Correct answer: A view is a virtual table defined by a query, computed on access
A standard SQL view is a virtual table; it stores only the query definition and computes results when queried, unless it is a materialized view.
GATE Databases 5
Which of the following correctly describes a 'lossless join decomposition'?