Top DBMS Interview Questions and Answers

A Database Management System (DBMS) is software that helps us to store, manage, and organize data efficiently.

A database schema is the structure or blueprint of a database. A database schema defines how data is organized, including tables, fields, and relationships.

The main types of DBMS models are Hierarchical, Network, Relational, and Object-oriented. Each model organizes data differently based on use cases.

Hierarchical: Think of a family tree where each parent has children, like a company’s organizational chart.
Network: Imagine a web of friends where one person can have multiple connections with others.
Relational: Like a spreadsheet with tables. For example, a table of students and another table of the courses they take.
Object-oriented: Similar to real-world objects, like a “Car” object that has properties (color, model) and methods (drive, stop).

DBMS stores data but doesn’t always organize it in tables or support relationships. For example, a file system storing data as plain text files.

RDBMS (Relational DBMS) stores data in tables with defined relationships between them. We can manage and query data easily. For example, MySQL or PostgreSQL stores data in tables with primary and foreign keys.

In short, all RDBMS are DBMS, but not all DBMS are relational.

Primary Key is a unique identifier for each record in a table (like a student ID). A Foreign Key is a field in one table that links to the primary key in another table. A Foreign Key creates a relationship between the two tables.

Normalization is the process of organizing data in a database to reduce duplicates and improve data integrity. It helps us keep the data clean and makes the database more efficient to update and maintain.

There are three main types of normalization in practice.  Let’s understand with an example.

Unnormalized Table (UNF)

1) First Normal Form (1NF)
Rule: Atomic values only (no multiple values in a single field)

2) Second Normal Form (2NF)
Rule: No partial dependency on part of a composite key
Break into multiple tables:
Student Table

Course Enrollment Table (StudentID + Course is composite key)

Course Instructor Table

Still has transitive dependency: InstructorName and InstructorPhone depend on InstructorID, which depends on Course.

3) Third Normal Form (3NF)
Rule: No transitive dependency. Every non-key attribute must depend only on the primary key. Split the Course Instructor into two separate tables:

Course Table

Instructor Table

InstructorName and InstructorPhone depend only on InstructorID. The course depends on the InstructorID, and the StudentID maps to the Course through enrollments.

Higher normalization forms like BCNF, 4NF exist for advanced use cases.

DDL (Data Definition Language): Used to define or modify the structure of database objects. Examples: CREATE, ALTER, DROP

DML (Data Manipulation Language): Used to manage data within tables.
Examples: SELECT, INSERT, UPDATE, DELETE

DCL (Data Control Language): Used to control access and permissions in the database. Examples: GRANT, REVOKE

1. Data Integrity – Ensures accurate and consistent data.
2. Data Security – Controls access to data.
3. Data Redundancy Control – Avoids duplicate data.
4. Easy Data Access – Allows efficient querying with SQL.
5. Backup & Recovery – Provides automatic backup options.
6. Multi-user Access – Supports concurrent data access.

A table is a collection of related data in rows and columns. A field is a single column in a table that represents one type of data. A record is a single row in a table that represents one complete set of related data.

A Primary Key uniquely identifies each record in a table.
A Foreign Key links one table to another using the primary key.
A Composite Key is a key made of two or more columns.
A Candidate Key is a column (or set) that can qualify as a primary key.
A Surrogate Key is a system-generated unique identifier (like an ID).

An Entity-Relationship (ER) model is a diagram that shows the entities (things) in a database and how they are related to each other. It helps design the database structure.

1. INNER JOIN – Returns matching rows from both tables.
2. LEFT JOIN – Returns all rows from the left table + matching right rows.
3. RIGHT JOIN – Returns all rows from the right table + matching left rows.
4. FULL JOIN – Returns all rows from both tables, matched or not.
5. CROSS JOIN – Returns all possible combinations of rows (Cartesian product).

We can use indexing to speed up data retrieval in a database. Indexing types are, 

1.Primary Index – Based on the primary key.

2.Unique Index – Ensures all values are unique.

3.Clustered Index – Sorts and stores data rows in table order.

4.Non-Clustered Index – Stores index separately from data rows.

5.Composite Index – Uses multiple columns.

Note: Syntax may vary slightly based on the database ( e.g., MySQL vs PostgreSQL vs SQL Server).

A transaction is a sequence of database operations that are treated as a single unit. It follows ACID properties:

1. Atomicity – All or nothing
2. Consistency – Maintains valid data
3. Isolation – Transactions don’t interfere
4. Durability – Changes are permanent after commit 

Denormalization is the process of adding redundant data to a database to improve read performance. It reduces the number of joins needed in queries.

A view is a virtual table based on a SQL query. It simplifies complex queries, hides sensitive data, and provides customized data for users.

A stored procedure is a pre-written SQL code saved in the database that can be executed when needed. With stored procedures, we can reuse code, improve performance, and increase security.

We can call the stored procedure like:

A subquery is a query inside another query. It runs independently of the outer query. Correlated Subquery is a subquery that uses values from the outer query. It runs once for each row in the outer query.

1.One-to-One (1:1) – One record in a table matches one in another.
Each person has one passport.

2.One-to-Many (1:N) – One record relates to many in another table.
One teacher teaches many students.

3.Many-to-Many (M:N) – Many records relate to many in another table (uses a join table).
Students enroll in many courses. Courses have many students.

A trigger is a set of SQL actions that automatically run when a specific action (INSERT, UPDATE, or DELETE) happens on a table.

A cursor is a database object used to fetch and process rows one at a time from a query result. It is used in Loops, row-by-row operations in stored procedures.

A deadlock happens when two or more transactions wait for each other to release locks, and none can proceed. To resolve a deadlock, lock resources in the same order, use a timeout to detect deadlocks and terminate one of the conflicting transactions to break the cycle , and keep transactions short and fast.

For example, Transaction 1 locks Table A and waits for Table B. Transaction 2 locks Table B and waits for Table A. Neither can proceed, and it leads to a deadlock!

A Clustered Index sorts and stores the actual data rows. Only one clustered index per table. Non-clustered index stores pointers to data rows. Can have multiple non-clustered indices per table.

Query Optimization is the process of improving a query to run faster and use fewer resources. The Execution Plan is to do a step-by-step breakdown of how the database will run the query.

Database sharding is splitting a large database into smaller, faster, and easier-to-manage parts called shards, each stored on different servers.

 Database replication copies data from one database to another to keep them synchronized. We can implement database replication in two ways.

1. Master-slave (one source, many copies)
2. Master-master (multiple sources syncing)

Access control restricts who can see or use data in a database. It is important to protect sensitive data, prevent unauthorized actions, and ensure data security and privacy.

In a 1-tier architecture, the database and application run on the same machine. There is no separation between client and server.

Example: A desktop app with a built-in database.

In a 2-tier architecture, the client directly communicates with the database server. 

Example: A banking app on your PC queries the bank’s database server.

In a 3-tier architecture, the client talks to an application server, which then talks to the database server.
Example: An online store’s website sends requests to a backend server, which then queries the database.

To debug slow queries, first check the execution plan. Then, look for missing indexes. After that, analyze query structure (avoid unnecessary joins). Next, check for large data scans or table scans. Finally, monitor server resources (CPU, memory). We can also use query profiling tools.