DBMS Important Questions for Semester Exams With Answers
This guide gives you unit-wise DBMS important questions with simple model answers for semester exams. Questions are grouped into 2-mark, 5-mark and 10-mark patterns so you can understand both what to study and how much to write. The exact university paper can differ, so use these as high-priority practice questions rather than a guaranteed question paper.
Useful for B.Tech, BCA, BSc CS, MCA and other university courses that include standard DBMS topics. Always match the final preparation with your own university syllabus and previous-year papers.
DBMS Basics, Architecture & Data Independence
High-priority questions from DBMS introduction, file system, architecture, schema and data independence.
Q1 What is DBMS? Write any two advantages. 2 Marks
Model Answer
A Database Management System (DBMS) is software used to create, store, organize, retrieve, update and control data in a database.
Two advantages: it reduces unnecessary data duplication and provides better data security and consistency.
Q2 Differentiate between DBMS and file system. 5 Marks
Model Answer
| File System | DBMS |
|---|---|
| Data is stored in separate files. | Data is managed through a database system. |
| Redundancy can be high. | Redundancy can be controlled. |
| Data sharing is harder. | Multi-user data sharing is easier. |
| Security is usually limited. | Provides controlled access and authorization. |
| Recovery is more manual. | DBMS can provide logging and recovery support. |
Q3 Explain 1-tier, 2-tier and 3-tier DBMS architecture with examples. 10 Marks
Model Answer
DBMS architecture explains how users, applications and database servers communicate. The three common forms are 1-tier, 2-tier and 3-tier architecture.
- 1-Tier: user directly works with the database on the same system. It is common in local development or learning environments.
- 2-Tier: a client application directly communicates with a database server. Example: a desktop college application connected to a MySQL server.
- 3-Tier: the client communicates with an application server, and the application server communicates with the database. This structure is common in web applications.
3-tier architecture provides a separate application layer, which can make security, maintenance and scalability easier to manage.
Q4 What is the difference between schema and instance? 2 Marks
A schema is the overall structure or blueprint of a database. An instance is the actual data stored in that database at a particular time.
Schema changes rarely, while an instance changes whenever records are inserted, updated or deleted.
Revise Schema, Instance & Data ModelsQ5 Explain logical and physical data independence. 5 Marks
Data independence means changes at one database level should not unnecessarily force changes at another level.
- Physical data independence: storage-level changes can be made without changing the logical structure.
- Logical data independence: logical schema changes can be made without forcing changes in user views or application programs as far as possible.
Physical data independence is generally easier to achieve than logical data independence.
Study Data Independence & DBMS LanguagesER Model, Keys, Relational Model & Relational Operations
Important questions from ER diagrams, mapping constraints, relational model, integrity and relational query languages.
Q6 Define entity, attribute and relationship. 2 Marks
- Entity: a real-world object about which data is stored, such as Student.
- Attribute: a property of an entity, such as StudentName.
- Relationship: an association between entities, such as Student enrolls in Course.
Q7 Explain super key, candidate key, primary key and foreign key. 5 Marks
- Super key: any set of attributes that uniquely identifies a tuple.
- Candidate key: a minimal super key.
- Primary key: candidate key selected to identify tuples.
- Foreign key: attribute that references a key in another relation.
Example: In STUDENT(RollNo, Name, DeptID), RollNo may be the primary key and DeptID may reference DEPARTMENT.
Study Keys & Mapping ConstraintsQ8 Explain ER model with mapping constraints, generalization and aggregation. 10 Marks
The ER model represents entities, attributes and relationships before the database is converted into relations.
- Mapping constraints: describe 1:1, 1:M, M:1 and M:N relationships.
- Generalization: combines lower-level entity types into a higher-level entity type.
- Specialization: divides a general entity into more specific subtypes.
- Aggregation: treats a relationship set as a higher-level entity for another relationship.
Q9 What is the relational model? Define relation, tuple, attribute and domain. 5 Marks
The relational model represents data using tables called relations.
- Relation: table.
- Tuple: row of a relation.
- Attribute: column of a relation.
- Domain: set of valid values allowed for an attribute.
Q10 Explain domain, key, entity and referential integrity constraints. 5 Marks
- Domain constraint: attribute values must belong to the permitted domain.
- Key constraint: key values must satisfy uniqueness requirements.
- Entity integrity: primary key cannot be NULL.
- Referential integrity: a foreign key should reference a valid parent-key value or follow the permitted NULL rule.
Q11 Explain relational algebra operations with suitable examples. 10 Marks
Relational algebra is a procedural query language in which a result is produced by applying operations on relations.
- Selection: chooses rows satisfying a condition.
- Projection: chooses required columns.
- Union: combines compatible tuples from two relations.
- Set Difference: returns tuples present in one relation but not the other.
- Cartesian Product: pairs tuples from two relations.
- Join: combines related tuples using a condition.
In the exam, write the symbol, definition and one small example for every major operation.
Practice Relational Algebra With ExamplesQ12 Differentiate between relational algebra and relational calculus. Explain TRC and DRC. 10 Marks
Relational algebra is procedural: it specifies the operations used to obtain the answer. Relational calculus is non-procedural: it specifies what result is required.
- Tuple Relational Calculus (TRC): variables represent tuples.
- Domain Relational Calculus (DRC): variables represent domain values.
SQL Queries, Joins, Subqueries & Database Objects
Practice both theory questions and query-writing questions because SQL is often directly tested.
Q13 Differentiate DDL, DML, DCL and TCL with examples. 5 Marks
- DDL: defines database structure — CREATE, ALTER, DROP.
- DML: changes stored data — INSERT, UPDATE, DELETE.
- DCL: controls access — GRANT, REVOKE.
- TCL: controls transactions — COMMIT, ROLLBACK, SAVEPOINT.
Q14 Write SQL queries using SELECT, WHERE and ORDER BY. 5 Marks
Example: find CSE students scoring above 75 and show highest marks first.
SELECT Name, Marks
FROM Student
WHERE Department = 'CSE'
AND Marks > 75
ORDER BY Marks DESC;SELECT chooses columns, WHERE filters rows and ORDER BY sorts the result.
Practice SELECT, WHERE & ORDER BYQ15 Explain SQL joins with suitable examples. 10 Marks
SQL joins combine related rows from two or more tables.
- INNER JOIN: returns matching rows.
- LEFT JOIN: returns all left-table rows plus matches.
- RIGHT JOIN: returns all right-table rows plus matches.
- FULL OUTER JOIN: returns matching and non-matching rows from both sides where supported.
SELECT Student.Name, Department.DeptName
FROM Student
INNER JOIN Department
ON Student.DeptID = Department.DeptID;Q16 What is a subquery? Explain aggregate functions, GROUP BY and HAVING. 10 Marks
A subquery is a query placed inside another SQL query. Aggregate functions summarize multiple rows.
- COUNT() counts rows.
- SUM() adds numeric values.
- AVG() calculates average.
- MIN() and MAX() return minimum and maximum values.
- GROUP BY forms groups.
- HAVING filters grouped results.
SELECT Department, AVG(Marks) AS AvgMarks
FROM Student
GROUP BY Department
HAVING AVG(Marks) > 70;Q17 Define view, index, cursor, trigger and stored procedure. 5 Marks
- View: virtual table based on a query.
- Index: data structure that can improve lookup speed.
- Cursor: mechanism used to process query-result rows one by one in procedural SQL contexts.
- Trigger: database action automatically executed when a specified event occurs.
- Stored procedure: reusable set of database statements stored on the server.
Functional Dependency & Normalization
Normalization questions are scoring when you explain the dependency first and then show each decomposition step.
Q18 What is functional dependency? Give an example. 2 Marks
A functional dependency X → Y means the value of X determines the value of Y.
Example: if RollNo uniquely determines StudentName, then RollNo → StudentName.
Revise Functional DependencyQ19 Explain 1NF, 2NF and 3NF with examples. 10 Marks
- 1NF: every cell should contain an atomic value; repeating groups are removed.
- 2NF: relation is in 1NF and non-key attributes should not depend on only part of a composite candidate key.
- 3NF: relation is in 2NF and problematic transitive dependency of non-key attributes is removed.
In a long answer, show the original relation first, identify the dependency causing the problem and then show the decomposed relations after each normal form.
Q20 Differentiate between 3NF and BCNF. 5 Marks
BCNF is generally stricter than 3NF. In BCNF, for every non-trivial functional dependency X → Y, X should be a super key.
A relation can satisfy 3NF in some situations while still violating BCNF.
Q21 What are lossless join and dependency preservation? 5 Marks
Lossless join decomposition means the decomposed relations can be joined back without losing information or creating incorrect extra information.
Dependency preservation means important functional dependencies can still be checked on the decomposed relations without needing expensive joins.
Study Lossless Join & Dependency PreservationQ22 Explain multivalued dependency, 4NF, join dependency and 5NF. 10 Marks
Higher normal forms deal with complex dependency problems that may remain after BCNF.
- MVD: one attribute can determine multiple independent values of another attribute.
- 4NF: controls non-trivial multivalued dependencies where the determinant is not a super key.
- Join dependency: a relation can be reconstructed by joining several projections.
- 5NF: addresses certain join-dependency problems that cannot be solved by lower normal forms.
Transactions, Serializability, Recovery & Concurrency Control
Focus on ACID, schedules, recovery, deadlock, locks, 2PL, timestamp ordering and MVCC.
Q23 What is a transaction? Explain ACID properties. 10 Marks
A transaction is a logical unit of database work. A bank transfer is a common example because debit and credit operations should complete as one reliable unit.
- Atomicity: all operations complete or none are applied.
- Consistency: valid database rules remain satisfied.
- Isolation: concurrent transactions should not incorrectly interfere with each other.
- Durability: committed changes should survive failures.
Q24 Explain conflict serializability and view serializability. 10 Marks
Serializability checks whether an interleaved schedule is equivalent in effect to a correct serial execution.
- Conflict serializability: determined using conflicting operations and commonly a precedence graph.
- View serializability: based on whether reads-from relationships and final writes match a serial schedule.
For a precedence graph question, draw one node per transaction and add directed edges for conflicting operations. A cycle means the schedule is not conflict serializable.
Practice Serializability With SchedulesQ25 Explain recoverability, logs, checkpoints, UNDO and REDO. 10 Marks
Recovery techniques help restore a correct database state after transaction or system failure.
- Log: records important transaction actions and changes.
- UNDO: reverses changes that should not remain.
- REDO: reapplies required committed changes.
- Checkpoint: creates a recovery reference point and can reduce the amount of log scanning needed.
Q26 What is deadlock? Explain deadlock prevention, detection and recovery. 10 Marks
Deadlock occurs when transactions wait for resources held by each other and no transaction can proceed.
- Prevention: design rules so at least one necessary deadlock condition cannot hold.
- Detection: allow waiting and detect cycles, often using a wait-for graph.
- Recovery: abort or roll back one or more transactions to break the deadlock.
Q27 Explain lock-based concurrency control, shared/exclusive locks and two-phase locking. 10 Marks
Lock-based concurrency control restricts access to data items so conflicting operations do not execute unsafely.
- Shared lock: mainly used for reading; multiple compatible readers may share it.
- Exclusive lock: required for writing and prevents conflicting access.
- Two-Phase Locking (2PL): growing phase acquires locks; shrinking phase releases locks.
- Multiple granularity: locking can occur at different levels such as database, table, page or row, often with intention locks.
Q28 Explain timestamp ordering, validation-based protocol and MVCC. 10 Marks
- Timestamp ordering: orders conflicting operations using transaction timestamps.
- Validation-based protocol: allows work in phases and validates whether a transaction can safely commit.
- MVCC: keeps multiple versions of data so readers and writers can often work with less direct blocking.
These protocols provide alternatives to basic lock-only concurrency control and are commonly compared in theory questions.
Study Timestamp, Validation & MVCC1 Hour Before the DBMS Exam: What Should You Revise?
Do not try to relearn the complete subject in the last hour. Use the time to refresh definitions, diagrams, SQL patterns, normalization rules and transaction-control concepts.
For a fast full-syllabus recap, use DBMS Complete Revision Notes: All Units, Key Concepts & Exam Revision .
DBMS Semester Exam Questions: FAQs
Are these exact questions guaranteed to come in my university exam?
No. DBMS syllabi and question-paper patterns differ between universities. These are high-priority practice questions based on common DBMS concepts. Match them with your official syllabus and previous-year papers.
How should I prepare 2-mark DBMS questions?
Learn clean definitions, one-line differences and one small example. A 2-mark answer usually does not need a long introduction.
How should I write a 10-mark DBMS answer?
Start with the definition and purpose, explain the concept step by step, add a diagram/table/query where useful, include an example and end with a short conclusion.
Which DBMS topics need the most practice instead of memorization?
SQL queries, relational algebra, normalization, serializability schedules and concurrency-control problems are better learned through practice rather than only memorizing theory.
Where can I revise the complete DBMS syllabus quickly?
Use the complete revision hub: DBMS Complete Revision Notes: All Units, Key Concepts & Exam Revision .
