ExploreDatabase – Your one-stop study guide for interview and semester exam preparations with solved questions, tutorials, GATE MCQs, online quizzes and notes on DBMS, Data Structures, Operating Systems, AI, Machine Learning and Natural Language Processing.
✔ Scroll down and test yourself — answers are hidden under the “View Answer” button.
41. In a hospital, each patient may have multiple appointments, and each appointment belongs to exactly one patient. How should this relationship be represented?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Recursive
Answer: B Explanation:
Each patient can have multiple appointments, making this a one-to-many relationship from Patient to Appointment.
42. In a university, a professor may supervise multiple students, and each student may have only one supervisor. How should this be modeled?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Recursive relationship
Answer: B Explanation:
This is a one-to-many relationship from Professor to Student because each professor can supervise many students, but a student has only one supervisor.
43. In an e-commerce system, a shopping cart contains multiple products, each with quantity. How should the cart be modeled?
A. Multivalued attribute
B. Weak entity
C. Associative entity
D. Derived attribute
Answer: C Explanation:
Cart contents involve a relationship-specific attribute (quantity), so an associative entity linking Cart and Product is required.
44. In a library, a book may belong to multiple genres. How should “genre” be represented?
A. Simple attribute
B. Multivalued attribute
C. Weak entity
D. Derived attribute
Answer: B Explanation:
Since a book can belong to multiple genres, “genre” is a multivalued attribute or can be represented as a separate entity.
45. In a hospital, “Total Bill” is computed from individual treatment charges. How should it be represented?
A. Simple attribute
B. Derived attribute
C. Weak entity
D. Multivalued attribute
Answer: B Explanation:
Total Bill is a derived attribute because it can be calculated from the sum of individual treatment charges.
46. In a university, “Student Enrollment” links students and courses. It also stores grades. What type of entity is this?
A. Weak entity
B. Associative entity
C. Derived attribute
D. Simple attribute
Answer: B Explanation:
Enrollment is an associative entity because it connects Student and Course and contains additional attributes like grades.
47. In a company, an employee can hold multiple positions over time. How should “position” be represented?
A. Multivalued attribute
B. Derived attribute
C. Weak entity
D. Simple attribute
Answer: A Explanation:
Position is a multivalued attribute because an employee may hold multiple positions simultaneously or sequentially.
48. In a banking system, an account may have multiple transactions, and each transaction belongs to exactly one account. How is this relationship represented?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Derived attribute
Answer: B Explanation:
This is a one-to-many relationship where an account can have multiple transactions, but each transaction belongs to a single account.
49. In a university, a “Transcript” is computed from student grades in courses. How should Transcript be represented?
A. Simple attribute
B. Multivalued attribute
C. Derived attribute
D. Weak entity
Answer: C Explanation:
Transcript is a derived attribute as it is computed from the grades of all courses taken by the student.
50. In an airline booking system, a flight may have multiple passengers, and each passenger may take multiple flights. How should this be represented?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Derived relationship
Answer: C Explanation:
This is a many-to-many relationship between Passenger and Flight, best implemented with an associative entity to store booking details.
✔ Scroll down and test yourself — answers are hidden under the “View Answer” button.
21. In a university, a course can have multiple instructors, and each instructor can teach multiple courses. How should this be modeled?
A. One-to-one relationship
B. One-to-many relationship
C. Many-to-many relationship
D. Recursive relationship
Answer: C Explanation:
This is a many-to-many relationship between Course and Instructor. An associative entity may be used to store additional attributes like teaching semester.
22. In a hospital ER model, “Room Number” is assigned based on patient type (ICU, General). How should Room Number be represented?
A. Simple attribute
B. Derived attribute
C. Multivalued attribute
D. Composite attribute
Answer: D Explanation:
Room Number can be a composite attribute consisting of building, floor, and room sequence.
23. In an e-commerce system, a product may belong to multiple categories, and each category contains multiple products. How should this relationship be represented?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Derived relationship
Answer: C Explanation:
This is a many-to-many relationship between Product and Category, typically implemented with an associative entity.
24. In a company, a project can have multiple tasks, and each task is assigned to exactly one project. How should Task be represented?
A. Strong entity
B. Weak entity
C. Derived attribute
D. Multivalued attribute
Answer: B Explanation:
Task depends on the existence of a Project, making it a weak entity identified by a partial key plus the project key.
25. In a university ER model, “Email” of a student can be computed from student ID and domain. How should it be represented?
A. Simple attribute
B. Multivalued attribute
C. Derived attribute
D. Weak entity
Answer: C Explanation:
“Email” is a derived attribute because it can be calculated from other stored attributes like student ID and domain.
26. In a library, a “Borrowing” record links a student and a book with the borrow date. What type of entity should “Borrowing” be?
A. Strong entity
B. Associative entity
C. Weak entity
D. Multivalued attribute
Answer: B Explanation:
“Borrowing” is an associative entity connecting Student and Book and storing relationship attributes like borrow date.
27. In a hospital, an “Insurance Policy” is linked to patients. A patient can have multiple policies. How should this be modeled?
A. Weak entity
B. Derived attribute
C. Multivalued attribute
D. Simple attribute
Answer: A Explanation:
Insurance Policy is a weak entity because it depends on the existence of a patient for identification.
28. In a university, each department can have multiple professors, and each professor belongs to exactly one department. What is this relationship type?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Recursive
Answer: B Explanation:
This is a one-to-many relationship where one department has many professors, but each professor belongs to only one department.
29. A product has multiple reviews, and each review is associated with exactly one product. How should reviews be represented?
A. Derived attribute
B. Multivalued attribute
C. Weak entity
D. Strong entity
Answer: C Explanation:
Reviews are weak entities because they depend on the Product for existence and identification.
30. In a company, an employee can belong to multiple teams, and each team can have multiple employees. How should this relationship be represented?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Derived relationship
Answer: C Explanation:
This is a many-to-many relationship, best implemented with an associative entity linking Employee and Team.
✔ Scroll down and test yourself — answers are hidden under the “View Answer” button.
11. In a library ER model, a book can have multiple authors. How should "authors" be represented?
A. Simple attribute
B. Multivalued attribute
C. Weak entity
D. Derived attribute
Answer: B Explanation:
Since a book can have more than one author, "authors" should be modeled as a multivalued attribute or as a separate related entity in logical design.
12. A bank wants to model that each customer may have multiple accounts, and each account may belong to multiple customers (joint accounts). How should this be modeled?
A. One-to-one relationship
B. One-to-many relationship
C. Many-to-many relationship
D. Recursive relationship
Answer: C Explanation:
This is a many-to-many relationship between Customer and Account. An associative entity may be used to store additional attributes like account balance or ownership percentage.
13. In a hospital, lab test results depend on both the patient and the test type. How should this relationship be represented?
A. Simple relationship
B. Associative entity
C. Weak entity
D. Derived attribute
Answer: B Explanation:
The result is an attribute of the relationship between Patient and Test. This requires an associative entity to store attributes like test date and result value.
14. In a university, a course can have multiple sections, and a section must belong to one course. How is “Section” classified?
A. Strong entity
B. Weak entity
C. Multivalued attribute
D. Derived attribute
Answer: B Explanation:
“Section” depends on “Course” for identification and cannot exist independently, making it a weak entity.
15. In an e-commerce system, an order can contain multiple products, each with quantity and price. How should this be modeled?
A. Add attributes to Order
B. Add attributes to Product
C. Use an associative entity Order_Product
D. Use derived attributes
Answer: C Explanation:
Since quantity and price depend on the combination of Order and Product, an associative entity is required to store these relationship attributes.
16. In a company, an employee can supervise multiple employees, but an employee can have only one supervisor. What type of relationship is this?
A. One-to-one
B. One-to-many recursive
C. Many-to-many
D. Weak entity
Answer: B Explanation:
This is a recursive one-to-many relationship where an Employee entity relates to itself as supervisor and subordinate.
17. In a hospital, a prescription is issued by a doctor to a patient. Each prescription includes multiple medicines. How should medicines be represented?
A. Multivalued attribute
B. Weak entity
C. Derived attribute
D. Recursive relationship
Answer: B Explanation:
Medicines are modeled as a weak entity related to the prescription entity, since their details exist in the context of a specific prescription.
18. In a travel booking system, a passenger may book multiple trips, and a trip may include multiple passengers. How should this relationship be represented?
A. One-to-one
B. One-to-many
C. Many-to-many
D. Recursive
Answer: C Explanation:
This is a many-to-many relationship, often implemented via an associative entity (e.g., Booking) to store additional attributes like booking date.
19. A student can be both a class representative and a club president. How would this specialization be represented?
A. Disjoint specialization
B. Overlapping specialization
C. Total specialization
D. Partial specialization
Answer: B Explanation:
Overlapping specialization allows an entity to belong to multiple subtypes simultaneously.
20. In a university ER model, "GPA" is computed from grades. How should GPA be represented?
A. Simple attribute
B. Derived attribute
C. Multivalued attribute
D. Weak entity
Answer: B Explanation:
GPA is a derived attribute because it can be calculated from the grades of the student in various courses.
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.
