Top 20 DBMS Interview Questions

A Database Management System (DBMS) is software that stores, retrieves, and manages data securely and efficiently. Unlike traditional file systems—which suffer from data redundancy, poor security, and lack of concurrent access—a DBMS provides structured querying, concurrency control, and crash recovery mechanisms.

A DBMS stores data as files and manages data hierarchically or in a network. A Relational Database Management System (RDBMS) is a more advanced version that stores data in tabular format (rows and columns) and enforces referential integrity using constraints and keys (e.g., MySQL, PostgreSQL, Oracle).

Data independence is the ability to modify the database schema at one level without affecting the schema at a higher level. It is divided into logical data independence (changing the conceptual structure without affecting external applications) and physical data independence (changing physical storage structures without affecting the logical schema).

This architecture separates a database into three levels to abstract complexities:

A primary key uniquely identifies a record and strictly cannot accept NULL values. A table can have only one primary key. A unique key also enforces uniqueness, but it generally allows a single NULL value, and a table can have multiple unique keys.

The ER model is a high-level conceptual data model used for database design. It represents real-world objects as entities (e.g., Student), characteristics of those objects as attributes (e.g., Name, Age), and how entities relate to each other as relationships (e.g., Student enrolls in Course).

Normalization is the process of organizing database attributes to reduce data redundancy and eliminate insert, update, and delete anomalies.

Denormalization is the deliberate introduction of redundancy into a database to improve read performance. It involves combining tables so that read-heavy operations don't require expensive, complex JOIN operations, commonly used in data warehousing.

The WHERE clause filters individual rows before any groupings are made and cannot be used with aggregate functions. The HAVING clause filters groups of rows after the GROUP BY clause is applied and is specifically used with aggregate functions like SUM(), COUNT(), and AVG().

Both operators combine the result sets of two or more SELECT statements. UNION removes duplicate rows from the final result set, whereas UNION ALL includes all duplicates, making it faster.

ACID ensures database reliability during transactions:

A deadlock happens when two or more transactions indefinitely wait for each other to release resource locks, halting the entire process. It can be prevented by ensuring transactions always acquire locks on tables in the exact same order, keeping transactions brief, and using appropriate isolation levels.

Indexing is a performance optimization technique that creates specialized data structures (like B-trees) to significantly speed up the retrieval of records from a database table, much like the index at the back of a textbook.

A view is a virtual table created by a dynamically executed SQL query; it doesn't store physical data on the disk. A materialized view physically stores the query result on the disk and updates periodically, which drastically speeds up complex queries but takes up storage space.

A stored procedure is a prepared, pre-compiled SQL code block that you can save and call manually whenever you need to execute business logic. A trigger is a specialized SQL code block that executes automatically in response to specific events (like INSERT, UPDATE, or DELETE) on a particular table.

Relevant for NoSQL and distributed databases, the CAP theorem states that a distributed system can only guarantee two out of the following three properties simultaneously: Consistency (all nodes see the same data), Availability (the system always responds to requests), and Partition tolerance (the system works despite network failures).