DBMS Question & Answer
For competitive or written exams that have 15-mark questions on DBMS (Database
Management Systems), the focus is generally on:
Conceptual understanding
System design ability
Query formulation
Transaction & concurrency concepts
Indexing & performance
Real-world applications
Since you mentioned System Software Design came up previously, it's very likely your exam
expects practical application of DBMS concepts too — not just definitions.
✅ Here’s a list of 15-mark-style DBMS questions (with model answers) for your
preparation:
1. Design a relational database for an Online Library Management System.
Clearly define the schema, relationships, and integrity constraints.
Answer:
Entities:
1. Book(Book_ID, Title, Author_ID, Publisher_ID, ISBN, Year, Copies)
2. Author(Author_ID, Name, Bio)
3. Publisher(Publisher_ID, Name, Address)
4. Member(Member_ID, Name, Email, Phone, Address)
5. Loan(Loan_ID, Book_ID, Member_ID, Issue_Date, Return_Date, Due_Date, Fine)
Relationships:
One author can write many books (1:N)
One publisher can publish many books (1:N)
One member can borrow many books (1:N)
Constraints:
Book_ID, Member_ID, Author_ID are primary keys.
Book_ID, Member_ID in Loan are foreign keys.
Copies must be ≥ 0
Use CHECK for valid dates (e.g., Return_Date ≥ Issue_Date)
Normalization:
All relations are in 3NF (no transitive dependencies)
Design Notes:
Could add triggers for checking available copies.
Fine is calculated as DATEDIFF(Return_Date, Due_Date) * rate_per_day.
2. Explain ACID properties in DBMS with suitable examples. Why are they
important in transaction management?
Answer:
ACID = Atomicity, Consistency, Isolation, Durability
Atomicity: All steps of a transaction are treated as a single unit.
E.g., In a bank transfer, debit and credit both must happen or none.
Consistency: Database remains in a valid state after a transaction.
E.g., Transferring ₹1000 must not create or destroy money.
Isolation: Concurrent transactions do not interfere.
E.g., Two users withdrawing from an ATM must not cause race conditions.
Durability: Once a transaction is committed, changes persist.
E.g., Power failure won't rollback a successful transfer.
Importance:
Ensures data reliability
Prevents data corruption in concurrent/multi-user environments
Backbone of reliable software systems
3. What is normalization? Explain 1NF, 2NF, 3NF with examples. Also, mention
its advantages and disadvantages.
Answer:
Normalization: Process of organizing data to reduce redundancy and improve data integrity.
1NF:
No repeating groups, atomic values
Example:
Unnormalized:
Student(ID, Name, Courses)
Courses = "Math, English"
Normalized:
Student(ID, Name)
Enrollment(ID, Course)
2NF:
In 1NF + No partial dependency (on part of composite key)
Example:
OrderDetails(OrderID, ProductID, ProductName)
→ ProductName depends only on ProductID → Move to Product table.
3NF:
In 2NF + No transitive dependency
Example:
Employee(EmpID, DeptID, DeptName)
→ DeptName depends on DeptID → Move to Department table.
Advantages:
Avoids redundancy
Prevents update anomalies
Saves space
Disadvantages:
Complex queries (due to many joins)
Performance overhead
4. Explain the difference between clustered and non-clustered indexes with
examples. Why are indexes used in DBMS?
Answer:
Index: A data structure to speed up query retrieval.
Clustered Index:
Alters physical order of data
Only one per table
Faster for range queries
Example:
Table rows are physically sorted by Employee_ID.
Non-Clustered Index:
Separate structure with pointers to data
Multiple allowed per table
Faster for point queries
Usage Example:
CREATE INDEX idx_emp_name ON Employee(Name);
Advantages of Indexing:
Speeds up SELECT queries
Useful for WHERE, JOIN, ORDER BY
Disadvantages:
Slows down INSERT/UPDATE/DELETE
Takes additional space
5. Write SQL queries for the following scenario:
Given a schema:
Employee(EmpID, Name, Salary, DeptID)
Department(DeptID, DeptName)
Write queries for:
a) Get names of employees in 'HR' department.
b) Find highest paid employee.
c) Count employees in each department.
d) Increase salary by 10% for 'IT' department.
Answer:
a)
SELECT Name
FROM Employee E
JOIN Department D ON [Link] = [Link]
WHERE [Link] = 'HR';
b)
SELECT Name, Salary
FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee);
c)
SELECT [Link], COUNT([Link]) AS EmpCount
FROM Employee E
JOIN Department D ON [Link] = [Link]
GROUP BY [Link];
d)
UPDATE Employee
SET Salary = Salary * 1.10
WHERE DeptID = (SELECT DeptID FROM Department WHERE DeptName = 'IT');
✅ Tips for Writing 15-Mark DBMS Answers:
Start with a definition
Use real-world or relatable examples
Draw ER diagrams or schemas if needed
Explain pros/cons or implications
Write SQL or pseudocode where relevant
Conclude with a 1–2 line summary
If you want more questions on ER diagrams, triggers, stored procedures, normalization case
studies, or query optimization, I can create those too. Just ask.