Introduction
Database Management System (DBMS) is a core subject in Computer Science competitive exams like PPSC, NTS, FPSC, and university entry tests. Concepts such as SQL queries, Normalization, ER Diagrams, Transactions, and Relational Algebra carry significant weight in screening tests. Having clear concepts allows you to score quick marks in these objective papers.
This guide from mcqsdesk.com brings you 50 handpicked DBMS MCQs with answers and concise explanations. Practice each question carefully and review the explanations to solidify your concepts.
Section 1: DBMS Basics & Architecture
Q1. What is a Database Management System (DBMS)?
A) Hardware system for storing files
B) Software used to create, manage, and retrieve databases
C) Operating system component
D) Text processing tool
Answer: B) Software used to create, manage, and retrieve databases
Explanation: DBMS is system software that serves as an interface between users/applications and the database.
Q2. Which level of the 3-schema architecture describes how data is physically stored on disk?
A) External Level
B) Conceptual Level
C) Internal (Physical) Level
D) Logical Level
Answer: C) Internal (Physical) Level
Explanation: The internal or physical level describes low-level data structures and storage paths on physical storage devices.
Q3. Data Independence means:
A) Data is independent of any system
B) Ability to modify a schema at one level without altering the schema at the next higher level
C) Storing data without security
D) Using multiple DBMS tools at once
Answer: B) Ability to modify a schema at one level without altering the schema at the next higher level
Explanation: Logical and physical data independence allow changes in lower-level schemas without breaking upper-level applications.
Q4. Who is responsible for overall control, maintenance, and security of a database system?
A) End User
B) Application Programmer
C) Database Administrator (DBA)
D) System Analyst
Answer: C) Database Administrator (DBA)
Explanation: The DBA oversees schema definition, security, user privileges, backup, and performance tuning.
Q5. Which of the following is an example of a Relational Database Management System (RDBMS)?
A) MongoDB
B) MySQL
C) Neo4j
D) Redis
Answer: B) MySQL
Explanation: MySQL organizes data into tables (relations) with rows and columns, making it an RDBMS.
Section 2: Relational Model & Keys
Q6. A tuple in the relational model refers to a:
A) Column
B) Table
C) Row
D) Attribute
Answer: C) Row
Explanation: In relational database terminology, a tuple represents a single record or row in a table.
Q7. The set of allowed values for an attribute is called its:
A) Domain
B) Schema
C) Relation
D) Degree
Answer: A) Domain
Explanation: A domain specifies the data type and valid range of values an attribute can hold.
Q8. Which key uniquely identifies a row in a table and cannot contain NULL values?
A) Foreign Key
B) Primary Key
C) Candidate Key
D) Alternate Key
Answer: B) Primary Key
Explanation: A Primary Key must be unique for every record and strictly forbids NULL values.
Q9. A key that consists of two or more attributes working together to uniquely identify a tuple is called a:
A) Simple Key
B) Composite Key
C) Foreign Key
D) Super Key
Answer: B) Composite Key
Explanation: A composite key uses multiple columns combined to form a unique identifier.
Q10. A Foreign Key is used to enforce:
A) Entity Integrity
B) Referential Integrity
C) Domain Integrity
D) User-Defined Integrity
Answer: B) Referential Integrity
Explanation: Foreign keys link tables together by ensuring references match existing primary key values in the parent table.
Q11. The number of attributes (columns) in a relation is called its:
A) Cardinality
B) Degree
C) Domain
D) Tuple count
Answer: B) Degree
Explanation: Degree represents the number of columns, whereas Cardinality represents the number of rows (tuples).
Section 3: Entity-Relationship (ER) Modeling
Q12. In an ER Diagram, an Entity is represented by a:
A) Oval
B) Rectangle
C) Diamond
D) Line
Answer: B) Rectangle
Explanation: Rectangles represent entity sets, ovals represent attributes, and diamonds represent relationship sets.
Q13. A multivalued attribute in an ER diagram is represented by a:
A) Dashed Oval
B) Double Oval
C) Double Rectangle
D) Diamond
Answer: B) Double Oval
Explanation: Double ovals symbolize attributes that can store multiple values for a single entity (e.g., phone numbers).
Q14. An entity set that does not have sufficient attributes to form a primary key is a:
A) Strong Entity Set
B) Weak Entity Set
C) Composite Entity Set
D) Derived Entity Set
Answer: B) Weak Entity Set
Explanation: A weak entity set depends on an owner (strong entity set) and uses a partial key (discriminator).
Q15. In an ER diagram, a Derived Attribute is drawn using a:
A) Solid Oval
B) Dashed Oval
C) Rectangle
D) Double Oval
Answer: B) Dashed Oval
Explanation: Derived attributes (like Age calculated from Date of Birth) are represented by dashed ovals.
Section 4: Normalization & Functional Dependencies
Q16. The main purpose of Normalization in DBMS is to:
A) Increase data redundancy
B) Reduce data redundancy and prevent insertion, update, and deletion anomalies
C) Encrypt data for security
D) Improve physical disk storage speed
Answer: B) Reduce data redundancy and prevent insertion, update, and deletion anomalies
Explanation: Normalization organizes columns and tables to ensure minimal duplication and clean logical dependencies.
Q17. A table is in First Normal Form (1NF) if:
A) All attributes contain atomic (indivisible) values
B) It has no transitive dependencies
C) It has a composite key
D) All partial dependencies are removed
Answer: A) All attributes contain atomic values
Explanation: 1NF requires values in every column to be single, atomic values with no repeating groups.
Q18. Second Normal Form (2NF) eliminates:
A) Transitive Dependencies
B) Partial Dependencies
C) Multivalued Dependencies
D) Join Dependencies
Answer: B) Partial Dependencies
Explanation: 2NF requires the table to be in 1NF and every non-prime attribute to be fully functionally dependent on the primary key.
Q19. A table is in Third Normal Form (3NF) if it is in 2NF and has no:
A) Partial Dependency
B) Transitive Dependency
C) Atomic Constraint
D) Primary Key
Answer: B) Transitive Dependency
Explanation: 3NF eliminates transitive dependencies (where a non-key attribute depends on another non-key attribute).
Q20. Boyce-Codd Normal Form (BCNF) is a stricter version of:
A) 1NF
B) 2NF
C) 3NF
D) 4NF
Answer: C) 3NF
Explanation: BCNF requires that for every functional dependency $X \rightarrow Y$, $X$ must be a super key.
Q21. Fourth Normal Form (4NF) is designed to handle:
A) Partial Dependencies
B) Transitive Dependencies
C) Multivalued Dependencies
D) Join Dependencies
Answer: C) Multivalued Dependencies
Explanation: 4NF ensures there are no independent multivalued dependencies in a relation.
Section 5: Structured Query Language (SQL)
Q22. Which SQL command category includes CREATE, ALTER, and DROP?
A) DML (Data Manipulation Language)
B) DDL (Data Definition Language)
C) DCL (Data Control Language)
D) TCL (Transaction Control Language)
Answer: B) DDL (Data Definition Language)
Explanation: DDL commands define or alter the structure of database objects like tables and indexes.
Q23. Which SQL clause is used to filter records based on aggregate functions?
A) WHERE
B) HAVING
C) GROUP BY
D) ORDER BY
Answer: B) HAVING
Explanation: WHERE filters individual rows before grouping, whereas HAVING filters group results after GROUP BY.
Q24. Which SQL statement removes all records from a table without logging individual row deletions?
A) DELETE
B) TRUNCATE
C) DROP
D) REMOVE
Answer: B) TRUNCATE
Explanation: TRUNCATE is a DDL command that quickly removes all rows from a table while preserving the table structure.
Q25. Which join returns all matching records from both tables plus non-matching rows from the left table?
A) INNER JOIN
B) LEFT OUTER JOIN
C) RIGHT OUTER JOIN
D) FULL OUTER JOIN
Answer: B) LEFT OUTER JOIN
Explanation: A LEFT JOIN retrieves all rows from the left table and matching rows from the right table (filling NULLs for non-matches).
Q26. In SQL, the wildcard % character in a LIKE operator represents:
A) Zero, one, or multiple characters
B) Exactly one character
C) Only numeric digits
D) Only uppercase letters
Answer: A) Zero, one, or multiple characters
Explanation: % matches any sequence of zero or more characters, whereas _ matches exactly one character.
Q27. Which aggregate function returns the total count of non-NULL values in a column?
A) SUM()
B) COUNT()
C) TOTAL()
D) AVG()
Answer: B) COUNT()
Explanation: COUNT(column_name) counts non-null entries in that specific column.
Q28. Which keyword eliminates duplicate rows from SQL query results?
A) UNIQUE
B) DISTINCT
C) SINGLE
D) DIFFERENT
Answer: B) DISTINCT
Explanation: SELECT DISTINCT filters out duplicate tuples from the output set.
Section 6: Transactions & ACID Properties
Q29. What does the “A” stand for in ACID properties of transactions?
A) Availability
B) Atomicity
C) Accuracy
D) Authorization
Answer: B) Atomicity
Explanation: Atomicity ensures that all operations in a transaction execute successfully or none at all (“All or Nothing”).
Q30. Which ACID property ensures that the database remains in a valid state before and after a transaction?
A) Atomicity
B) Consistency
C) Isolation
D) Durability
Answer: B) Consistency
Explanation: Consistency guarantees all integrity constraints are preserved across transaction boundaries.
Q31. Durability in ACID properties implies:
A) Transactions run concurrently without interference
B) Changes made by committed transactions persist permanently even during system failures
C) System response time is constant
D) Data is copied to external drives automatically
Answer: B) Changes made by committed transactions persist permanently even during system failures
Explanation: Once a transaction is committed, its updates are recorded in non-volatile storage and survive crashes.
Q32. A transaction state after all statements have executed successfully, but before writing to disk, is:
A) Active
B) Partially Committed
C) Committed
D) Failed
Answer: B) Partially Committed
Explanation: The transaction enters the partially committed state after the final statement completes, just prior to final commit verification.
Q33. Which command permanently saves changes made during a transaction?
A) ROLLBACK
B) COMMIT
C) SAVEPOINT
D) CHECKPOINT
Answer: B) COMMIT
Explanation: COMMIT ends the transaction and makes all updates permanent in the database.
Section 7: Concurrency Control & Indexing
Q34. A situation where two transactions wait indefinitely for each other to release locks is called:
A) Starvation
B) Deadlock
C) Livelock
D) Race Condition
Answer: B) Deadlock
Explanation: Deadlock happens when Transaction A holds a lock needed by B, while B holds a lock needed by A.
Q35. Shared Lock (S-lock) allows concurrent transactions to:
A) Read and write data
B) Read data only
C) Write data only
D) Delete records
Answer: B) Read data only
Explanation: Multiple transactions can hold shared locks simultaneously to read data, but no transaction can write.
Q36. An Exclusive Lock (X-lock) allows a transaction to:
A) Read data only
B) Read and write data
C) Execute queries without logging
D) Override administrative permissions
Answer: B) Read and write data
Explanation: Exclusive lock grants exclusive access to read and write the locked data item; no other transaction can read or write it.
Q37. Two-Phase Locking (2PL) protocol guarantees:
A) Deadlock freedom
B) Serializability
C) Minimum execution time
D) High throughput
Answer: B) Serializability
Explanation: 2PL enforces growing and shrinking phases to ensure concurrent execution is serializable.
Q38. A Clustered Index determines the:
A) Logical order of data only
B) Physical order of data stored on disk
C) Backup schedule
D) Network throughput
Answer: B) Physical order of data stored on disk
Explanation: A table can have only one clustered index because rows are physically sorted on disk based on the index key.
Q39. A Non-Clustered Index:
A) Alters physical disk ordering
B) Is stored separately from data rows and contains pointers to data locations
C) Can only be created once per table
D) Requires no extra disk storage
Answer: B) Is stored separately from data rows and contains pointers to data locations
Explanation: Non-clustered indexes create a separate structure pointing to the physical data rows; a table can have many non-clustered indexes.
Section 8: Relational Algebra & Advanced Concepts
Q40. Which operator in Relational Algebra selects rows that satisfy a given predicate?
A) Projection ($\pi$)
B) Selection ($\sigma$)
C) Cartesian Product ($\times$)
D) Join ($\bowtie$)
Answer: B) Selection ($\sigma$)
Explanation: Selection ($\sigma$) filters tuples (rows), whereas Projection ($\pi$) selects specific attributes (columns).
Q41. Projection ($\pi$) operator in Relational Algebra is used for:
A) Selecting rows
B) Choosing specific columns
C) Combining tables
D) Dividing relations
Answer: B) Choosing specific columns
Explanation: Projection extracts specified vertical columns from a relation and eliminates duplicate tuples automatically.
Q42. The Cartesian Product of relation R (3 rows) and relation S (4 rows) yields a relation with:
A) 7 rows
B) 12 rows
C) 1 row
D) 4 rows
Answer: B) 12 rows
Explanation: Cartesian product combines every row of R with every row of S ($3 \times 4 = 12$).
Q43. Which technique breaks a database into smaller, distributed pieces across multiple locations?
A) Replicating
B) Sharding / Partitioning
C) Normalizing
D) Indexing
Answer: B) Sharding / Partitioning
Explanation: Sharding distributes database partitions across separate servers to improve scalability and performance.
Q44. What is a View in SQL?
A) A physical file on disk
B) A virtual table based on the result-set of an SQL statement
C) A specialized index
D) A backup copy of a table
Answer: B) A virtual table based on the result-set of an SQL statement
Explanation: Views do not store data physically (unless materialized); they execute underlying queries dynamically.
Q45. Which constraint prevents duplicate values in a column but permits a single NULL value in SQL Server?
A) PRIMARY KEY
B) UNIQUE
C) CHECK
D) DEFAULT
Answer: B) UNIQUE
Explanation: UNIQUE enforces uniqueness across records while allowing one NULL value (in SQL Server/standard specifications).
Q46. An anomaly where updating data in one place leaves inconsistent copies elsewhere is an:
A) Insertion Anomaly
B) Update Anomaly
C) Deletion Anomaly
D) Selection Anomaly
Answer: B) Update Anomaly
Explanation: Update anomalies occur due to data redundancy when modified records aren’t updated consistently everywhere.
Q47. What is a Database Cursor?
A) A graphical point in DBMS
B) A control structure that enables traversal over records in a result set
C) A performance logging utility
D) An encryption key
Answer: B) A control structure that enables traversal over records in a result set
Explanation: Cursors process query result sets row by row in procedural programming blocks.
Q48. In Object-Oriented DBMS, objects consist of:
A) Tables and rows only
B) Variables and associated methods
C) Text files and pointers
D) Views and indexes
Answer: B) Variables and associated methods
Explanation: OODBMS stores complex data structures as objects, combining state (data) and behavior (methods).
Q49. Which SQL statement revokes permissions previously granted to users?
A) GRANT
B) REVOKE
C) REMOVE
D) CANCEL
Answer: B) REVOKE
Explanation: REVOKE is a DCL command used to withdraw granted access privileges from database users.
Q50. A Trigger in a database is:
A) A manual backup command
B) A procedural code block executed automatically in response to specified events on a table
C) An external reporting software
D) A network handshake protocol
Answer: B) A procedural code block executed automatically in response to specified events on a table
Explanation: Triggers execute automatically when DML/DDL events (like INSERT, UPDATE, DELETE) take place on specified tables.
Conclusion
DBMS is a foundational and high-scoring subject for competitive exams like PPSC, NTS, FPSC, and university entry tests. Concepts regarding SQL syntax, normalization rules, ACID properties, and relational algebra repeat regularly across different exam patterns.
Keep practicing on mcqsdesk.com for more Computer Science MCQs with detailed explanations. Share this guide with your classmates and fellow aspirants to help them succeed!
Related Subject-wise MCQs:
14 Comments