# DBMS Multiple Choice Questions (MCQs) ## Unit 1 — Introduction & ER Model **1.** Which of the following is NOT a component of a DBMS? - A) Query Processor - B) Storage Manager - C) Compiler - D) Transaction Manager **Answer: C** — A compiler is not a DBMS component. DBMS includes query processor, storage manager, and transaction manager. --- **2.** The three-level ANSI/SPARC architecture consists of: - A) External, Internal, Physical - B) External, Conceptual, Internal - C) Logical, Physical, View - D) User, Conceptual, Storage **Answer: B** — External (View), Conceptual (Logical), and Internal (Physical) levels. --- **3.** Which key uniquely identifies each record in a table? - A) Foreign Key - B) Candidate Key - C) Primary Key - D) Super Key **Answer: C** — Primary key uniquely identifies each row and cannot be NULL. --- **4.** An ER diagram is used to represent: - A) Physical storage of data - B) Conceptual design of a database - C) SQL queries - D) Index structures **Answer: B** — ER diagrams model the conceptual/logical design. --- **5.** A weak entity set is one that: - A) Has no attributes - B) Cannot exist without a related strong entity - C) Has multiple primary keys - D) Contains only foreign keys **Answer: B** — Weak entities depend on strong entities for identification. --- **6.** In an ER diagram, a relationship between one entity from set A and many from set B is called: - A) Many-to-Many - B) One-to-One - C) One-to-Many - D) Many-to-One **Answer: C** — One-to-Many (1:N) cardinality. --- **7.** Data independence means: - A) Data is stored independently on different servers - B) Changes in one level do not affect other levels - C) Each user has their own copy of data - D) Data is not dependent on applications **Answer: B** — Data independence allows schema changes at one level without affecting others. --- **8.** Which data model uses tables to represent data? - A) Hierarchical Model - B) Network Model - C) Relational Model - D) Object-Oriented Model **Answer: C** — The relational model uses tables (relations). --- ## Unit 2 — Relational Model & SQL **9.** The SELECT operation in relational algebra is equivalent to which SQL clause? - A) SELECT - B) FROM - C) WHERE - D) GROUP BY **Answer: C** — The relational algebra SELECT filters rows, corresponding to the SQL WHERE clause. --- **10.** Which SQL command is used to remove a table permanently? - A) DELETE - B) TRUNCATE - C) DROP - D) REMOVE **Answer: C** — DROP TABLE removes the table structure and all data permanently. --- **11.** A view in SQL is: - A) A physical copy of a table - B) A virtual table based on a query - C) An index on a table - D) A stored procedure **Answer: B** — Views are virtual tables defined by a SELECT query. --- **12.** Which join returns only matching rows from both tables? - A) Left Join - B) Right Join - C) Full Outer Join - D) Inner Join **Answer: D** — INNER JOIN returns rows with matching values in both tables. --- **13.** DCL stands for: - A) Data Control Language - B) Data Creation Language - C) Database Command Language - D) Data Check Language **Answer: A** — DCL includes GRANT and REVOKE commands. --- **14.** Which aggregate function returns the number of rows? - A) SUM() - B) AVG() - C) COUNT() - D) MAX() **Answer: C** — COUNT() returns the number of rows matching a condition. --- **15.** A trigger in SQL fires: - A) Only when a SELECT is executed - B) Automatically in response to DML events - C) Only when called explicitly - D) During database startup **Answer: B** — Triggers execute automatically on INSERT, UPDATE, or DELETE events. --- ## Unit 3 — Normalization **16.** A relation is in 2NF if: - A) It is in 1NF and has no partial dependencies - B) It is in 1NF and has no transitive dependencies - C) It has a composite primary key - D) All attributes are multi-valued **Answer: A** — 2NF eliminates partial functional dependencies on composite primary keys. --- **17.** BCNF is a stricter version of: - A) 1NF - B) 2NF - C) 3NF - D) 4NF **Answer: C** — BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF. --- **18.** Armstrong's axioms include: - A) Reflexivity, Augmentation, Transitivity - B) Union, Intersection, Difference - C) Selection, Projection, Join - D) Closure, Normalization, Decomposition **Answer: A** — Reflexivity, Augmentation, and Transitivity are the three Armstrong axioms. --- **19.** A lossless decomposition ensures: - A) No data redundancy - B) Original relation can be reconstructed via natural join - C) All functional dependencies are preserved - D) No null values exist **Answer: B** — Lossless join decomposition allows reconstruction of the original relation. --- **20.** Multivalued dependency is associated with: - A) 3NF - B) BCNF - C) 4NF - D) 5NF **Answer: C** — 4NF eliminates non-trivial multivalued dependencies. --- ## Unit 4 — Transactions & Concurrency **21.** ACID properties stand for: - A) Atomicity, Consistency, Isolation, Durability - B) Accuracy, Completeness, Integrity, Dependency - C) Association, Concurrency, Independence, Distribution - D) Atomicity, Concurrency, Integrity, Distribution **Answer: A** — ACID: Atomicity, Consistency, Isolation, Durability. --- **22.** In two-phase locking, the two phases are: - A) Read and Write - B) Growing and Shrinking - C) Lock and Unlock - D) Begin and Commit **Answer: B** — 2PL has a growing phase (acquiring locks) and a shrinking phase (releasing locks). --- **23.** Which problem arises when two transactions wait for each other to release locks? - A) Starvation - B) Cascading Rollback - C) Deadlock - D) Dirty Read **Answer: C** — Deadlock occurs when transactions form a circular wait for resources. --- **24.** A dirty read occurs when: - A) A transaction reads committed data - B) A transaction reads data written by an uncommitted transaction - C) Two transactions read the same row - D) A transaction reads null values **Answer: B** — Dirty read: reading uncommitted (potentially rolled-back) data. --- **25.** Which recovery technique uses a log to undo incomplete transactions? - A) Shadow Paging - B) Checkpointing - C) Log-Based Recovery - D) ARIES **Answer: C** — Log-based recovery uses write-ahead logging to undo/redo transactions. --- ## Unit 5 — Storage & Indexing **26.** A B+ tree differs from a B-tree in that: - A) B+ tree stores data only in leaf nodes - B) B+ tree has fewer levels - C) B+ tree does not support range queries - D) B+ tree uses hashing **Answer: A** — B+ tree stores all data records in leaf nodes; internal nodes store only keys. --- **27.** Which indexing type uses a hash function to locate records? - A) Dense Index - B) Sparse Index - C) Hash Index - D) Clustered Index **Answer: C** — Hash indexing computes bucket addresses using a hash function. --- **28.** Query optimization aims to: - A) Maximize storage usage - B) Find the most efficient query execution plan - C) Eliminate SQL syntax errors - D) Reduce the number of tables **Answer: B** — Query optimization selects the lowest-cost execution plan. --- ## Unit 6 — Advanced Topics **29.** In a distributed database, fragmentation refers to: - A) Data corruption across nodes - B) Dividing data across multiple sites - C) Replicating data to multiple servers - D) Breaking indexes into smaller pieces **Answer: B** — Fragmentation divides relations into horizontal or vertical fragments stored at different sites. --- **30.** NoSQL databases are characterized by: - A) Fixed schema and ACID compliance only - B) Schema flexibility and horizontal scalability - C) Support for joins only - D) Single-server architecture **Answer: B** — NoSQL databases offer flexible schemas and horizontal scaling for large datasets. --- ## Answer Key Summary | Q | A | Q | A | Q | A | |---|---|---|---|---|---| | 1 | C | 11 | B | 21 | A | | 2 | B | 12 | D | 22 | B | | 3 | C | 13 | A | 23 | C | | 4 | B | 14 | C | 24 | B | | 5 | B | 15 | B | 25 | C | | 6 | C | 16 | A | 26 | A | | 7 | B | 17 | C | 27 | C | | 8 | C | 18 | A | 28 | B | | 9 | C | 19 | B | 29 | B | | 10 | C | 20 | C | 30 | B |