DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions (MCQs)DBMS Lab Programs
Learn about interview questions — dbms in Database Management Systems with detailed explanations and examples.
Fresher Level Questions
Q1. What is the difference between SQL and NoSQL databases?
| Feature | SQL (Relational) | NoSQL |
|---|---|---|
| Schema | Fixed, predefined | Dynamic, flexible |
| Scaling | Vertical (scale up) | Horizontal (scale out) |
| Transactions | Full ACID | BASE (eventual consistency) |
| Joins | Supported | Generally not supported |
| Examples | MySQL, PostgreSQL | MongoDB, Cassandra, Redis |
| Best for | Structured data, complex queries | Large-scale, unstructured data |
Q3. What is a stored procedure vs. a function?
| Feature | Stored Procedure | Function |
|---|---|---|
| Return value | Can return 0 or more values | Must return exactly one value |
| Usage | Called with CALL statement | Used inside SQL expressions |
| DML operations | Can execute DML (INSERT, UPDATE) | Cannot modify data (in most DBs) |
| Transaction control | Can use COMMIT/ROLLBACK | Cannot |
| Purpose | Complex business logic | Computation / calculations |
Q4. Explain different types of database relationships.
Q5. What are database transactions? Explain with an example.
A transaction is a logical unit of work that consists of one or more SQL operations that must all succeed or all fail together.
Example — Online Banking:
Code example
Transaction: Transfer ₹5000 from Savings to Current
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 5000 WHERE acct_id = 'SAV001';
UPDATE Accounts SET balance = balance + 5000 WHERE acct_id = 'CUR001';
COMMIT;
If any step fails → ROLLBACK (both operations are undone)Intermediate Level Questions
Q6. What is query optimization? How does a query optimizer work?
Query optimization is the process of selecting the most efficient execution plan for a given SQL query. The optimizer evaluates multiple possible plans and chooses the one with the lowest estimated cost (I/O, CPU, memory).
Steps:
- Parsing: SQL is parsed into an internal representation (parse tree)
- Logical Optimization: Apply algebraic transformations (push selections down, eliminate redundancy)
- Physical Plan Generation: Generate multiple candidate physical plans
- Cost Estimation: Estimate cost using statistics (row counts, index availability, data distribution)
- Plan Selection: Choose the plan with minimum estimated cost
- Execution: Execute the chosen plan
Q7. What is deadlock detection using a Wait-For Graph?
A Wait-For Graph (WFG) is used to detect deadlocks. Nodes represent transactions; a directed edge T1 → T2 means T1 is waiting for a resource held by T2.
Example
Deadlock Present (cycle detected):
T1 ──────→ T2
↑ |
| ↓
T4 ←────── T3
T1 waits for T2, T2 waits for T3, T3 waits for T4, T4 waits for T1
→ CYCLE → DEADLOCK
Resolution: Abort one of the transactions in the cycle (victim selection).
Q8. Explain MVCC (Multi-Version Concurrency Control).
MVCC maintains multiple versions of each data item so that readers never block writers and writers never block readers.
- Every write creates a new version of the row with a timestamp.
- Readers see the most recent committed version as of their transaction start time.
- Used by PostgreSQL, Oracle, MySQL InnoDB.
Advantages:
- High read concurrency
- Non-blocking reads
- Consistent snapshots
Q9. What is the CAP Theorem?
The CAP Theorem states that a distributed database can guarantee at most two of the following three properties simultaneously:
| CA | Traditional relational databases (single node) |
| CP | MongoDB, HBase (sacrifice availability during partition) |
| AP | Cassandra, CouchDB (sacrifice consistency during partition) |
- Consistency: Every read receives the most recent write
- Availability: Every request receives a response (not necessarily the latest)
- Partition Tolerance: System continues operating despite network partitions
Q10. How would you design a database for an e-commerce platform?
Core Entities:
Example
Users(UserID, Name, Email, Address, Phone)
Products(ProductID, Name, Description, Price, CategoryID, Stock)
Categories(CategoryID, CategoryName, ParentCategoryID)
Orders(OrderID, UserID, OrderDate, Status, TotalAmount)
OrderItems(OrderItemID, OrderID, ProductID, Quantity, UnitPrice)
Payments(PaymentID, OrderID, Amount, Method, Status, PaymentDate)
Reviews(ReviewID, UserID, ProductID, Rating, Comment, Date)
Key design decisions:
- Use foreign keys to enforce referential integrity
- Index on UserID (Orders), ProductID (OrderItems), Email (Users)
- Separate OrderItems to handle multiple products per order (normalization)
- Store UnitPrice in OrderItems (not just from Products) to preserve historical pricing
Advanced Level Questions
Q11. What is database sharding?
Sharding is a horizontal partitioning technique where data is distributed across multiple database servers (shards) based on a shard key.
| Shard Key | UserID |
| Shard 1: UserID 1–100,000 | Server A |
| Shard 2: UserID 100,001–200,000 | Server B |
| Shard 3: UserID 200,001–300,000 | Server C |
Advantages: Linear horizontal scalability, improved performance. Challenges: Cross-shard queries are expensive, re-sharding is complex, no cross-shard transactions.
Q12. Explain the differences between OLTP and OLAP systems.
| Feature | OLTP | OLAP |
|---|---|---|
| Purpose | Day-to-day transactions | Analytics and reporting |
| Data volume | Gigabytes | Terabytes to petabytes |
| Query type | Simple, short | Complex, aggregations |
| Response time | Milliseconds | Seconds to minutes |
| Schema | Normalized (3NF) | Denormalized (Star/Snowflake) |
| Operations | INSERT, UPDATE, DELETE | Mostly SELECT (read-heavy) |
| Examples | Banking, e-commerce | Data warehouses, BI tools |
Q13. What is eventual consistency in distributed systems?
Eventual consistency is a consistency model used in distributed databases where it is guaranteed that, if no new updates are made, all replicas will eventually converge to the same value.
It is a weaker guarantee than strong consistency but allows higher availability and partition tolerance (AP systems in CAP theorem).
Example: DNS, Amazon DynamoDB, Apache Cassandra.
Continue the lesson path
Study next in this module
01VivaImportant Viva Questions — DBMS1. What is a DBMS? How is it different from a file system? 2. What are the four main characteristics of a database? 3. What is the three-level ANSI/SPARC...02VivaViva Answers — DBMSA Database Management System (DBMS) is a software system that allows users to create, store, retrieve, and manage data in a structured way. It provides an...03VivaViva Voce — DBMSA viva voce Latin for "with living voice" is an oral examination where an examiner asks questions and you respond verbally. In engineering courses, DBMS viva examinations04Lab ProgramsDBMS Lab ProgramsDBMS lab programs help you convert database theory into working SQL practice. A good lab file should show table creation, constraints, DML operations,...