DBMS Interview Questions & Answers

DBMS Interview Questions

Q1: What is the difference between DBMS and RDBMS?

**DBMS** (Database Management System) stores data as files. **RDBMS** (Relational DBMS) stores data in tables with relationships.

| Feature | DBMS | RDBMS |

|---------|------|-------|

Q2: Explain the ACID properties.

**Atomicity**: Transaction is all-or-nothing. If any part fails, the entire transaction rolls back.

**Consistency**: Transaction brings database from one valid state to another. All constraints and rules are maintained.

**Isolation**: Concurrent transactions don't interfere with each other. Each transaction appears to run in isolation.

**Durability**: Once committed, changes persist even after system failure.

Q3: What is normalization? Explain normal forms.

Normalization is the process of organizing data to eliminate redundancy and anomalies.

**1NF**: Atomic columns, no repeating groups

**2NF**: 1NF + no partial dependencies

**3NF**: 2NF + no transitive dependencies

**BCNF**: Every determinant is a candidate key

**4NF**: No multi-valued dependencies

**Example**:

Before normalization: Student(ID, Name, Courses)

After 3NF: Student(ID, Name), Enrollment(ID, CourseID), Course(CourseID, Name)

Q4: What is indexing? Types of indexes?

Indexing is a technique to speed up data retrieval operations.

**Types**:

**Primary Index**: On primary key (clustered)

**Secondary Index**: On non-primary key (non-clustered)

**Unique Index**: Ensures unique values

**Composite Index**: On multiple columns

**Hash Index**: For equality searches

**B-Tree Index**: For range queries

Q5: Explain SQL Joins.

**INNER JOIN**: Returns matching rows from both tables

**LEFT JOIN**: All rows from left, matching from right

**RIGHT JOIN**: All rows from right, matching from left

**FULL OUTER JOIN**: All rows from both tables

**CROSS JOIN**: Cartesian product

**SELF JOIN**: Table joined with itself

Q6: What is a transaction and its states?

A transaction is a logical unit of work that contains one or more SQL statements.

**States**: Active → Partially Committed → Committed

→ Failed → Aborted

Q7: Difference between DELETE, TRUNCATE, and DROP?

|---------|------|-------|----------|-----------|

Q8: What is a deadlock in databases?

A deadlock occurs when two or more transactions hold locks on resources that each other needs, causing them to wait indefinitely.

**Solutions**:

**Deadlock Prevention**: Resource ordering

**Deadlock Detection**: Wait-for graph

**Deadlock Avoidance**: Wound-wait, wait-die

**Timeout**: Rollback after timeout