BSCS : Database Management Systems 5 — Questions and Answers
Question 1: Which technique does MVCC (Multi-Version Concurrency Control) use to improve concurrency?
- Acquiring exclusive locks before any read
- Keeping multiple versions of data so readers don't block writers (Correct answer)
- Aborting all conflicting transactions immediately
- Using timestamps to serialize all transactions
Correct answer: Keeping multiple versions of data so readers don't block writers
MVCC maintains multiple data versions so readers see a consistent snapshot without blocking concurrent writers.
Question 2: A stored procedure in SQL differs from a function primarily because a stored procedure:
- Cannot accept parameters
- Can return multiple result sets and does not have to return a value (Correct answer)
- Is always executed automatically on data change
- Can only be called from another stored procedure
Correct answer: Can return multiple result sets and does not have to return a value
Stored procedures can return multiple result sets, execute DML statements, and are not required to return a single value like functions.
Question 3: In the context of functional dependencies, Armstrong's Axioms include which three rules?
- Reflexivity, Augmentation, Transitivity (Correct answer)
- Union, Decomposition, Pseudo-transitivity
- Selection, Projection, Join
- Closure, Superkey, Candidate key
Correct answer: Reflexivity, Augmentation, Transitivity
Armstrong's Axioms (Reflexivity, Augmentation, Transitivity) form a sound and complete set of inference rules for functional dependencies.
Question 4: Which type of SQL trigger fires once for the entire DML statement rather than once per affected row?
- Row-level trigger
- Statement-level trigger (Correct answer)
- BEFORE trigger
- AFTER trigger
Correct answer: Statement-level trigger
A statement-level trigger executes once per triggering SQL statement regardless of how many rows are affected.
Question 5: In a star schema, dimension tables are connected to the fact table through:
- Composite primary keys shared with other dimension tables
- Foreign keys in the fact table referencing dimension primary keys (Correct answer)
- A junction table between each pair
- Materialized views
Correct answer: Foreign keys in the fact table referencing dimension primary keys
In a star schema, the fact table holds foreign keys that each reference the primary key of a single dimension table.
Question 6: Which property ensures that concurrent transactions produce a result equivalent to some serial execution?
- Atomicity
- Consistency
- Isolation
- Serializability (Correct answer)
Correct answer: Serializability
Serializability is the highest correctness criterion for concurrency control, guaranteeing an outcome identical to some sequential schedule.
Question 7: What is a materialized view?
- A virtual table whose data is computed on every query
- A precomputed, physically stored result of a query that can be refreshed (Correct answer)
- An index built on a single column for fast lookup
- A temporary table that exists only for the session duration
Correct answer: A precomputed, physically stored result of a query that can be refreshed
A materialized view stores query results physically on disk and must be refreshed when underlying data changes, trading staleness for speed.
BSCS : Database Management Systems 5
Which technique does MVCC (Multi-Version Concurrency Control) use to improve concurrency?