Databases Flashcards

Which of the following correctly describes a 'lossless join decomposition'?

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.

In a B-tree (not B+ tree) of order m, the maximum number of keys in a node is:

Answer: m - 1

A B-tree node of order m holds at most m children and therefore at most m-1 keys.

Which relational algebra expression represents the SQL: SELECT A FROM R WHERE B = 5?

Answer: π_A(σ_{B=5}(R))

First apply selection to filter rows (σ_{B=5}), then apply projection to keep only column A (π_A).

The concept of 'data independence' in DBMS most directly refers to:

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.

Which aggregate function in SQL ignores NULL values by default?

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.

In the context of query optimization, a 'selectivity' value close to 0 for a predicate means:

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.

Which of the following statements about a 'view' in SQL is TRUE?

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.