BSCS : Database Management Systems 3 — Questions and Answers
Question 1: Which join returns only rows that have matching values in both tables?
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- INNER JOIN (Correct answer)
Correct answer: INNER JOIN
INNER JOIN returns only the rows where the join condition is satisfied in both tables.
Question 2: What is a phantom read in database transactions?
- Reading a row that was deleted by another transaction
- Re-reading a row and finding its values changed
- A new row appearing in repeated queries due to another transaction's insert (Correct answer)
- Reading uncommitted data from another transaction
Correct answer: A new row appearing in repeated queries due to another transaction's insert
A phantom read occurs when a transaction re-executes a query and finds new rows inserted by a concurrent transaction.
Question 3: Which SQL command permanently removes a table and its structure from the database?
- DELETE
- TRUNCATE
- DROP (Correct answer)
- REMOVE
Correct answer: DROP
DROP TABLE removes the table definition and all its data permanently, unlike DELETE which only removes rows.
Question 4: In an ER diagram, a double rectangle represents a:
- Strong entity
- Weak entity (Correct answer)
- Associative entity
- Derived attribute
Correct answer: Weak entity
A weak entity is drawn with a double rectangle because it cannot be uniquely identified without a related strong entity.
Question 5: Which index structure is best suited for range queries?
- Hash index
- B+ tree index (Correct answer)
- Bitmap index
- Inverted index
Correct answer: B+ tree index
B+ tree indexes maintain sorted order, making them efficient for range queries by traversing leaf-node linked lists.
Question 6: What is the purpose of a checkpoint in database recovery?
- To lock all tables during a transaction
- To reduce the amount of log that must be scanned during recovery (Correct answer)
- To archive old data to secondary storage
- To enforce referential integrity
Correct answer: To reduce the amount of log that must be scanned during recovery
Checkpoints write dirty buffer pages to disk and record a log marker, so recovery only needs to replay the log from that point forward.
Question 7: Which relational algebra operation selects a subset of columns from a relation?
- Select (σ)
- Project (π) (Correct answer)
- Join (⋈)
- Union (∪)
Correct answer: Project (π)
The Project operation (π) extracts specified attributes (columns) from a relation, potentially reducing its degree.
BSCS : Database Management Systems 3
Which join returns only rows that have matching values in both tables?