BSCS : Database Management Systems 4 — Questions and Answers
Question 1: Which isolation level prevents dirty reads but still allows non-repeatable reads?
- Read Uncommitted
- Read Committed (Correct answer)
- Repeatable Read
- Serializable
Correct answer: Read Committed
Read Committed allows a transaction to only see committed data, preventing dirty reads but permitting non-repeatable reads between queries.
Question 2: In a data warehouse, a fact table typically contains:
- Descriptive attributes of dimensions
- Quantitative measures and foreign keys to dimensions (Correct answer)
- User authentication data
- Index structures for fast lookup
Correct answer: Quantitative measures and foreign keys to dimensions
Fact tables store measurable business events (metrics) along with foreign keys that link to dimension tables.
Question 3: What does a FOREIGN KEY constraint enforce?
- Uniqueness of values in the referencing table
- Referential integrity between two tables (Correct answer)
- That a column cannot contain NULL values
- That a column value must satisfy a condition
Correct answer: Referential integrity between two tables
A FOREIGN KEY constraint ensures that a value in the referencing column must exist as a primary or unique key in the referenced table.
Question 4: Which SQL aggregate function returns the number of non-NULL values in a column?
- SUM(col)
- COUNT(col) (Correct answer)
- COUNT(*)
- TOTAL(col)
Correct answer: COUNT(col)
COUNT(col) counts only non-NULL values in the specified column, while COUNT(*) counts all rows including those with NULLs.
Question 5: Deadlock in a DBMS occurs when:
- A transaction holds too many locks
- Two or more transactions wait for each other's locked resources indefinitely (Correct answer)
- A transaction is rolled back due to a constraint violation
- A query takes longer than the timeout threshold
Correct answer: Two or more transactions wait for each other's locked resources indefinitely
Deadlock is a cycle of waiting transactions, each holding a resource the next one needs, so none can proceed.
Question 6: In NoSQL databases, which type stores data as key-value pairs with flexible schemas?
- Column-family store
- Document store
- Key-value store (Correct answer)
- Graph database
Correct answer: Key-value store
Key-value stores (e.g., Redis, DynamoDB) map unique keys to values and offer the simplest data model among NoSQL types.
Question 7: What is the role of the query optimizer in a DBMS?
- To parse SQL syntax for errors
- To choose the most efficient execution plan for a query (Correct answer)
- To enforce data integrity constraints
- To manage user authentication and authorization
Correct answer: To choose the most efficient execution plan for a query
The query optimizer evaluates multiple execution plans using cost estimates and selects the one with the lowest estimated cost.
BSCS : Database Management Systems 4
Which isolation level prevents dirty reads but still allows non-repeatable reads?