Viva Answers — DBMS | 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 interface between

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

Q2. Explain the ACID Properties

A — Atomicity: A transaction is treated as a single unit. Either all operations complete successfully, or none of them are applied (all-or-nothing).

C — Consistency: A transaction takes the database from one valid state to another valid state, preserving all defined rules, constraints, and integrity conditions.

I — Isolation: Concurrent transactions execute independently. The intermediate state of a transaction is not visible to other transactions.

D — Durability: Once a transaction is committed, its changes persist even in the event of system failures (ensured via logs and stable storage).

Bank Transfer	Transfer ₹1000 from A to B
Step 1	Debit A by ₹1000
Step 2	Credit B by ₹1000
Atomicity	Both steps succeed, or both are rolled back.
Consistency	Total money before = Total money after.
Isolation	No other transaction sees A debited but B not yet credited.
Durability	After COMMIT, the transfer survives a power failure.

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

↓ Logical Independence

Conceptual Level

Logical Schema

Central database structure

↓ Physical Independence

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

Q1. What is a DBMS? How is it different from a file system?

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

Q2. Explain the ACID Properties

A — Atomicity: A transaction is treated as a single unit. Either all operations complete successfully, or none of them are applied (all-or-nothing).

C — Consistency: A transaction takes the database from one valid state to another valid state, preserving all defined rules, constraints, and integrity conditions.

I — Isolation: Concurrent transactions execute independently. The intermediate state of a transaction is not visible to other transactions.

D — Durability: Once a transaction is committed, its changes persist even in the event of system failures (ensured via logs and stable storage).

Bank Transfer	Transfer ₹1000 from A to B
Step 1	Debit A by ₹1000
Step 2	Credit B by ₹1000
Atomicity	Both steps succeed, or both are rolled back.
Consistency	Total money before = Total money after.
Isolation	No other transaction sees A debited but B not yet credited.
Durability	After COMMIT, the transfer survives a power failure.

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

↓ Logical Independence

Conceptual Level

Logical Schema

Central database structure

↓ Physical Independence

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

Viva Answers — DBMS

Q1. What is a DBMS? How is it different from a file system?

Q2. Explain the ACID Properties

Q3. What is the Three-Level Architecture?

External Level

Conceptual Level

Internal Level

Q4. What are the Types of Keys?

Q5. What is Normalization?

Q6. What is Two-Phase Locking?

Q7. What is a B+ Tree?

Structure of B+ Tree (Order 3)

Properties

Q8. Explain Transaction States

Q9. What is Functional Dependency?

Q10. What is the Difference Between Clustered and Non-Clustered Index?

Clustered Index

Non-Clustered Index

Continue learning this concept

Viva Answers — DBMS

Q1. What is a DBMS? How is it different from a file system?

Q2. Explain the ACID Properties

Q3. What is the Three-Level Architecture?

External Level

Conceptual Level

Internal Level

Q4. What are the Types of Keys?

Q5. What is Normalization?

Q6. What is Two-Phase Locking?

Q7. What is a B+ Tree?

Structure of B+ Tree (Order 3)

Properties

Q8. Explain Transaction States

Q9. What is Functional Dependency?

Q10. What is the Difference Between Clustered and Non-Clustered Index?

Clustered Index

Non-Clustered Index

Continue learning this concept