DBMS MCQ Quiz – 25 Multiple Choice Questions with Answers
Test your understanding of advanced DBMS concepts including normalization, transactions, and distributed databases. Each question includes the correct answer and brief explanation.
1. Which of the following statements best describes a super key?
A. A key that uniquely identifies a record but cannot be reduced
B. A minimal set of attributes that uniquely identifies a record
C. Any set of attributes that uniquely identifies a record
D. A subset of a candidate key
Answer: C
Explanation: A super key is any combination of attributes that can uniquely identify a tuple, not necessarily minimal.
2. Which of the following is not a property of transactions (ACID)?
A. Atomicity
B. Consistency
C. Integrity
D. Durability
Answer: C
Explanation: Integrity is not part of ACID; it’s a database design property. ACID includes Atomicity, Consistency, Isolation, and Durability.
3. A schedule that is conflict serializable must also be:
A. View serializable
B. Cascadeless
C. Recoverable
D. Serializable
Answer: D
Explanation: Conflict-serializable schedules are always serializable; however, not all serializable schedules are conflict-serializable.
4. In a B+ tree, data records are stored:
A. In internal nodes only
B. In leaf nodes only
C. In both internal and leaf nodes
D. In root nodes only
Answer: B
Explanation: All actual data records (or pointers) are stored in leaf nodes in a B+ tree; internal nodes store only keys for navigation.
5. Which of the following anomalies is not handled by normalization up to 3NF?
A. Insertion anomaly
B. Update anomaly
C. Deletion anomaly
D. Lossless join anomaly
Answer: D
Explanation: Normal forms handle insertion, update, and deletion anomalies. Lossless joins are verified through dependency preservation, not normalization alone.
6. In distributed databases, fragmentation transparency refers to:
A. Hiding data replication
B. Hiding data location and fragmentation from users
C. Automatic recovery from failure
D. Maintaining concurrency
Answer: B
Explanation: Fragmentation transparency ensures users can query data without knowing its partitioning or location.
7. The operation used to combine rows from two relations based on a common attribute is:
A. Union
B. Join
C. Projection
D. Intersection
Answer: B
Explanation: The JOIN operation combines tuples from two relations based on a related attribute.
8. Which isolation level in SQL prevents dirty reads but allows non-repeatable reads?
A. Read Uncommitted
B. Repeatable Read
C. Read Committed
D. Serializable
Answer: C
Explanation: “Read Committed” prevents dirty reads but allows non-repeatable reads.
9. The referential integrity constraint is violated when:
A. A foreign key value doesn’t match any primary key value
B. A primary key is null
C. Two rows have same primary key
D. The table is not normalized
Answer: A
Explanation: Referential integrity ensures every foreign key value matches an existing primary key in the referenced table.
10. The process of converting an ER diagram into a set of relations is called:
A. Decomposition
B. Mapping
C. Normalization
D. Denormalization
Answer: B
Explanation: ER-to-relational mapping is the process of converting conceptual schema into relational schema.
11. In SQL, the result of the query
SELECT COUNT(DISTINCT dept_id) FROM employee;
will be:
A. Total number of employees
B. Total number of unique departments
C. Total number of records with NULL dept_id
D. Total number of employees with same department
Answer: B
Explanation: DISTINCT counts unique non-null values of dept_id, giving the number of unique departments.
12. Which join returns all rows from both tables, with NULLs where no match exists?
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. FULL OUTER JOIN
Answer: D
Explanation: FULL OUTER JOIN includes all rows from both sides, filling NULLs where matches don’t exist.
13. Which index type is best for range queries (e.g., salary > 50000)?
A. Hash index
B. B-tree index
C. Bitmap index
D. Dense index
Answer: B
Explanation: B-tree indexes maintain sorted order and are optimal for range-based retrievals.
14. In distributed transactions, two-phase commit protocol (2PC) ensures:
A. Atomicity across multiple databases
B. Concurrency
C. Deadlock prevention
D. Query optimization
Answer: A
Explanation: 2PC ensures all distributed databases either commit or roll back together, preserving atomicity.
15. Which SQL constraint ensures that a column cannot take NULL values?
A. CHECK
B. DEFAULT
C. UNIQUE
D. NOT NULL
Answer: D
Explanation: NOT NULL ensures the column must always contain a non-null value.
16. A relation is in BCNF if:
A. It is in 3NF and every determinant is a candidate key
B. It is in 2NF and has no transitive dependency
C. Every attribute is prime
D. It has a single candidate key
Answer: A
Explanation: BCNF is a stricter version of 3NF where every determinant is a candidate key.
17. What is the purpose of a checkpoint in database recovery?
A. Save the log permanently
B. Flush dirty pages to disk
C. Mark a consistent database state
D. Rollback uncommitted transactions
Answer: C
Explanation: A checkpoint marks a consistent state so recovery can start from that point instead of the beginning of the log.
18. Which SQL keyword is used to rename a table or column temporarily?
A. RENAME
B. CHANGE
C. AS
D. MODIFY
Answer: C
Explanation: The keyword “AS” is used in SQL to assign an alias to a table or column.
19. Which concurrency control protocol uses timestamp ordering?
A. Two-phase locking
B. Wait-die scheme
C. Optimistic concurrency control
D. Timestamp-based protocol
Answer: D
Explanation: Timestamp-based protocols assign timestamps to transactions to maintain serializability.
20. In a relational model, NULL represents:
A. Zero value
B. Blank string
C. Missing or unknown value
D. Default value
Answer: C
Explanation: NULL indicates missing or unknown data, not zero or blank.
21. Which of the following commands removes a table and its data permanently?
A. DELETE
B. TRUNCATE
C. DROP
D. CLEAR
Answer: C
Explanation: DROP removes the table definition and its data permanently.
22. What does a dense index contain?
A. Entry for every record in the data file
B. Entry for every block in the data file
C. Only distinct key values
D. No duplicate keys
Answer: A
Explanation: Dense index stores one index entry per record, unlike sparse indexes.
23. Which of the following is not true about relational algebra?
A. Projection removes duplicates
B. Selection chooses specific rows
C. Union combines tuples from two relations
D. Join merges related tuples
Answer: A
Explanation: Projection in relational algebra doesn’t remove duplicates unless specified.
24. In SQL, GRANT SELECT ON student TO user1; means:
A. Give SELECT privilege on user1 to student
B. Give SELECT privilege on student table to user1
C. Deny access to others
D. Transfer ownership
Answer: B
Explanation: GRANT assigns privileges; here user1 can read (SELECT) from the student table.
25. The CAP theorem in distributed databases stands for:
A. Consistency, Availability, Partition Tolerance
B. Cache, Access, Performance
C. Control, Authentication, Persistence
D. Commit, Abort, Process
Answer: A
Explanation: CAP theorem states that a distributed system can only guarantee two of three: consistency, availability, or partition tolerance.
No comments:
Post a Comment