DBMS Notes
A 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...
Q1. What is a DBMS? How is it different from a file system?
A 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 interface between the application programs and the physical data stored on disk.
DBMS vs File System:
| Feature | File System | DBMS |
|---|---|---|
| Data Redundancy | High redundancy | Controlled via normalization |
| Data Sharing | Difficult | Concurrent multi-user access |
| Data Integrity | No enforcement | Integrity constraints enforced |
| Security | Limited | Fine-grained access control |
| Backup/Recovery | Manual | Automatic recovery mechanisms |
| Query Support | No querying | SQL or query language support |
Q3. What is the Three-Level Architecture?
The ANSI/SPARC three-level architecture separates user views from physical storage:
External Level
Views / Apps
- Protected by logical independence
Conceptual Level
Logical Schema
- Central database structure
Internal Level
Physical Storage
- Files, indexes, pages, storage format
- External Level: Each user or application sees only their relevant portion of data (view).
- Conceptual Level: Describes what data is stored and the relationships — independent of how it's physically stored.
- Internal Level: Describes how data is physically stored (file structures, indexes, access paths).
Q4. What are the Types of Keys?
| Key Type | Definition |
|---|---|
| Super Key | Any set of attributes that uniquely identifies a tuple |
| Candidate Key | Minimal super key (no redundant attributes) |
| Primary Key | Chosen candidate key; cannot be NULL |
| Alternate Key | Candidate keys not chosen as primary key |
| Foreign Key | Attribute(s) referencing the primary key of another table |
| Composite Key | Primary key made of two or more attributes |
| Attributes | RollNo, AadharNo, Name, Email |
| Super Keys | {RollNo}, {AadharNo}, {Email}, {RollNo, Name}, ... |
| Candidate Keys | {RollNo}, {AadharNo}, {Email} |
| Primary Key | {RollNo} ← chosen |
| Alternate Keys | {AadharNo}, {Email} |
Q5. What is Normalization?
Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity by decomposing tables into smaller, well-structured relations.
| 1NF | Eliminate repeating groups / multi-valued attributes |
| 2NF | Eliminate partial dependencies (on composite PK) |
| 3NF | Eliminate transitive dependencies |
| BCNF | Every determinant is a candidate key |
| 4NF | Eliminate non-trivial multivalued dependencies |
| 5NF | Eliminate join dependencies |
Q6. What is Two-Phase Locking?
Two-Phase Locking (2PL) is a concurrency control protocol that ensures conflict-serializability by dividing lock operations into two phases:
| Growing Phase | Shrinking Phase |
|---|---|
| (Acquire Locks) | (Release Locks) |
- Growing Phase: Transaction may acquire (shared or exclusive) locks but cannot release any.
- Shrinking Phase: Transaction may release locks but cannot acquire any new locks.
- Lock Point: The moment a transaction holds its maximum number of locks.
Strict 2PL: All exclusive locks are held until the transaction commits or aborts (prevents dirty reads and cascading rollbacks).
Q7. What is a B+ Tree?
A B+ tree is a balanced tree data structure used for indexing in databases.
Structure of B+ Tree (Order 3)
[20 | 40]
/ | \
[10|15] [25|30] [45|50]
| | | | | |
(data pointers) → Leaf nodes linked as a list
Properties
- All data records stored only in LEAF nodes
- Internal nodes store only keys (routing information)
- Leaf nodes are linked → supports efficient range queries
- Tree is always balanced (all leaves at same level)
- Search: O(log n), Insert: O(log n), Delete: O(log n)
Q8. Explain Transaction States
- Active: Transaction is executing.
- Partially Committed: Last statement executed, changes not yet written to disk.
- Committed: Transaction completed successfully; changes are permanent.
- Failed: Transaction cannot proceed due to error.
- Aborted: Transaction rolled back; database restored to prior state.
Q9. What is Functional Dependency?
A functional dependency X → Y means that for any two tuples in the relation, if they have the same value for attribute(s) X, they must have the same value for attribute(s) Y.
| EmpID | Name (EmpID determines Name) |
| EmpID | DeptID (EmpID determines Department) |
| DeptID | DeptName (DeptID determines Department Name) |
| EmpID | DeptName (Transitive via DeptID) |
Types:
- Partial Dependency: Non-key attribute depends on part of a composite primary key.
- Transitive Dependency: Non-key attribute depends on another non-key attribute.
- Trivial FD: X → Y where Y ⊆ X (e.g., {A, B} → A).
Q10. What is the Difference Between Clustered and Non-Clustered Index?
| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Storage | Data rows stored in index order | Separate structure; data stored elsewhere |
| Count per table | Only 1 per table | Multiple allowed |
| Speed (range queries) | Faster | Slower |
| Physical ordering | Changes physical row order | Does not change row order |
| Example | Primary key index | Index on Name or Email column |
Clustered Index
Index leaf nodes contain actual data rows
Data file is sorted same as index
Non-Clustered Index
Index leaf nodes contain pointers (row locators)
to actual data rows
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Viva Answers — DBMS.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), viva, answers, viva answers — dbms
Related Database Management Systems (DBMS) Topics