1. What is a "DBMS" (Database Management System)?

A DBMS is a software system that enables users to define, create, maintain, and control access to a database. Examples: MySQL, PostgreSQL, Oracle, SQL Server.

2. What are the key features of a DBMS?

Data independence
Efficient data access
Data integrity and security
Data administration
Concurrent access and crash recovery
Reduced application development time

3. What is the difference between a "Database" and a "DBMS"?

Database: A collection of related data.
DBMS: The software that manages the database.

4. What is an "RDBMS"?

Relational Database Management System. A DBMS based on the relational model (data stored in tables).

5. What are the different types of Database Languages?

DDL (Data Definition Language): CREATE, ALTER, DROP.
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
DCL (Data Control Language): GRANT, REVOKE.
TCL (Transaction Control Language): COMMIT, ROLLBACK.

6. What is "Data Redundancy"?

A condition in which the same piece of data is stored in two or more separate locations (can lead to inconsistencies).

7. What is "Data Independence"?

The ability to modify the database schema at one level (e.g., physical) without affecting the schema at a higher level (e.g., conceptual).

8. What are the types of Data Independence?

Logical Data Independence: Changing the conceptual schema without changing the external schema (view).
Physical Data Independence: Changing the physical storage structure without changing the conceptual schema.

9. What is "Data Integrity"?

The overall accuracy, completeness, and consistency of data throughout its lifecycle.

10. What is a "Schema" in DBMS?

A skeletal structure that represents the logical view of the entire database. It defines how data is organized and how the relationships among them are associated.

11. What are the three levels of "Database Architecture" (3-Schema Architecture)?

External/View Level: Describes how users see the data.
Conceptual/Logical Level: Describes what data is stored and what the relationships are.
Internal/Physical Level: Describes how data is physically stored on disk.

12. What is "Entity" and "Attribute"?

Entity: A real-world object (e.g., a student, an employee).
Attribute: Properties of an entity (e.g., name, age, salary).

13. What is an "ER Model" (Entity-Relationship Model)?

A high-level conceptual data model that uses diagrams to represent the architecture of a database.

14. What are the different types of "Relationships"?

One-to-One (1:1)
One-to-Many (1:M)
Many-to-Many (M:M)

15. What is "Normalization"?

The process of organizing data in a database to reduce redundancy and improve data integrity.

16. What are the "Normal Forms"?

1NF: Atomic values; no repeating groups.
2NF: In 1NF + no partial dependency (all non-key attributes depend on the entire primary key).
3NF: In 2NF + no transitive dependency (no non-key attribute depends on another non-key attribute).
BCNF: Stricter version of 3NF.

17. What is "Functional Dependency"?

A relationship between two attributes where the value of one attribute (determinant) uniquely determines the value of another.

18. What is a "Key" in DBMS?

An attribute or set of attributes that uniquely identifies a record in a table.

19. What are the different types of Keys?

Primary Key: Unique and not null identifier.
Candidate Key: A set of attributes that can uniquely identify a row.
Super Key: A set of attributes that includes a candidate key.
Foreign Key: A column that refers to the primary key of another table.

20. What is "Referential Integrity"?

A rule that ensures every foreign key value in a table points to an existing primary key value in the related table.

21. What is "ACID" Properties?

Atomicity: Transactions are "all-or-nothing."
Consistency: The database remains in a valid state.
Isolation: Multiple transactions don't interfere with each other.
Durability: Once committed, changes are permanent.

22. What is a "Transaction"?

A logical unit of database processing that includes one or more database access operations (read, write).

23. What are the different "Transaction States"?

Active
Partially Committed
Committed
Failed
Aborted

24. What is "Concurrency Control"?

The process of managing simultaneous operations on a database without having them interfere with each other.

25. What is "Deadlock" in DBMS?

A situation where two or more transactions are waiting for each other to release locks on resources, leading to a standstill.

26. What is "Two-Phase Locking" (2PL)?

A concurrency control protocol that ensures serializability by dividing a transaction's lock management into two phases: Growing (obtaining locks) and Shrinking (releasing locks).

27. What is "Serializability"?

A property that ensures that the outcome of executing multiple transactions concurrently is the same as some serial (sequential) execution.

28. What is "Database Backup" and "Recovery"?

Backup: Creating copies of data for security.
Recovery: The process of restoring the database to a consistent state after a failure (e.g., Log-based, Shadow Paging).

29. What is "Indexing" and why is it used?

A technique used to retrieve data from a database table much faster. It uses an internal data structure (like B-trees) to store sorted keys.

30. What is the difference between "Clustered" and "Non-Clustered" indexes?

Clustered: Determines the physical order of data (only one per table).
Non-Clustered: Contains a separate structure pointing to the actual data.

31. What is "Hashing" in DBMS?

A technique used to map a key value to a direct memory address (bucket) for extremely fast data retrieval.

32. What is a "View"?

A virtual table derived from the result-set of an SQL query. It doesn't store data physically.

33. What is the difference between a "View" and a "Table"?

Table: Stores data physically on disk.
View: A virtual logical view of data stored in tables.

34. What is "SQL" vs "NoSQL"?

SQL: Relational; structured; follows ACID; tabular.
NoSQL: Non-relational; semi-structured; document/key-value oriented; highly scalable.

35. What is "Object-Relational Mapping" (ORM)?

A technique used to bridge the gap between object-oriented programming (like Java, Python) and relational databases (like MySQL).

36. What is "Data Warehousing"?

The process of collecting and managing data from varied sources to provide meaningful business insights (OLAP).

37. What is the difference between "OLTP" and "OLAP"?

OLTP: Online Transaction Processing; focuses on day-to-day operations (inserts, updates).
OLAP: Online Analytical Processing; focuses on complex queries and data analysis.

38. What is "Data Mining"?

The process of discovering patterns, anomalies, and correlations within large data sets to predict outcomes.

39. What is "Horizontal" vs "Vertical" Scaling?

Horizontal (Scaling Out): Adding more servers to the database cluster.
Vertical (Scaling Up): Adding more resources (CPU, RAM) to a single server.

40. What is "Sharding"?

A specialized horizontal scaling technique where data is partitioned into smaller, faster pieces (shards) across multiple servers.

41. What is "Database Denormalization"?

Strategic duplication of data to improve query performance (at the risk of data redundancy).

42. What is the "Query Optimizer"?

The internal component of the DBMS that determines the most efficient way to execute a given SQL query.

43. What is "JDBC" and "ODBC"?

Standards used to connect an application to a database (Java Database Connectivity / Open Database Connectivity).

44. What is "Trigger"?

A stored procedure that is automatically executed when a specific event occurs in the database.

45. What is "Storable Procedure"?

Pre-compiled SQL code that can be called repeatedly to perform a task.

46. What is "Cascading Delete"?

A feature where deleting a parent record automatically deletes all related child records in other tables.

47. What is "NULL" in DBMS?

Represents a missing, unknown, or non-applicable value. It is different from zero or an empty string.

48. What is "Crow's Foot Notation"?

A set of symbols used in ER Diagrams to represent the cardinality of a relationship.

49. What is "Conflict Serializability"?

A condition where a schedule of operations can be transformed into a serial schedule by swapping non-conflicting adjacent operations.

50. What is the "Database Administrator" (DBA)?

The person responsible for the installation, configuration, security, and maintenance of a database.

DBMS Interview Questions

Table of Contents

What is a "DBMS" (Database Management System)?

What are the key features of a DBMS?

What is the difference between a "Database" and a "DBMS"?

What is an "RDBMS"?

What are the different types of Database Languages?

What is "Data Redundancy"?

What is "Data Independence"?

What are the types of Data Independence?

What is "Data Integrity"?

What is a "Schema" in DBMS?

What are the three levels of "Database Architecture" (3-Schema Architecture)?

What is "Entity" and "Attribute"?

What is an "ER Model" (Entity-Relationship Model)?

What are the different types of "Relationships"?

What is "Normalization"?

What are the "Normal Forms"?

What is "Functional Dependency"?

What is a "Key" in DBMS?

What are the different types of Keys?

What is "Referential Integrity"?

What is "ACID" Properties?

What is a "Transaction"?

What are the different "Transaction States"?

What is "Concurrency Control"?

What is "Deadlock" in DBMS?

What is "Two-Phase Locking" (2PL)?

What is "Serializability"?

What is "Database Backup" and "Recovery"?

What is "Indexing" and why is it used?

What is the difference between "Clustered" and "Non-Clustered" indexes?

What is "Hashing" in DBMS?

What is a "View"?

What is the difference between a "View" and a "Table"?

What is "SQL" vs "NoSQL"?

What is "Object-Relational Mapping" (ORM)?

What is "Data Warehousing"?

What is the difference between "OLTP" and "OLAP"?

What is "Data Mining"?

What is "Horizontal" vs "Vertical" Scaling?

What is "Sharding"?

What is "Database Denormalization"?

What is the "Query Optimizer"?

What is "JDBC" and "ODBC"?

What is "Trigger"?

What is "Storable Procedure"?

What is "Cascading Delete"?

What is "NULL" in DBMS?

What is "Crow's Foot Notation"?

What is "Conflict Serializability"?

What is the "Database Administrator" (DBA)?