100+ DBMS Interview Questions (Excluding SQL)

Basic Level Questions (1-35)

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

A DBMS is software that manages databases, providing an interface for users and applications to store,
retrieve, and manage data efficiently while ensuring data integrity, security, and consistency.

2. What are the advantages of using a DBMS?

Data independence

Reduced data redundancy

Data consistency and integrity

Data security

Concurrent access control

Backup and recovery

Data sharing

3. What is the difference between a database and a DBMS?

Database: Collection of related data

DBMS: Software system that manages databases

4. What are the different types of database models?

Hierarchical Model

Network Model

Relational Model

Object-Oriented Model

Object-Relational Model

NoSQL Models (Document, Key-Value, Column-family, Graph)

5. What is a relational database?

A relational database organizes data into tables (relations) with rows (tuples) and columns (attributes),
following the relational model principles.

6. What is a primary key?

A primary key is a unique identifier for each record in a table that cannot be null and must be unique
across all records.

7. What is a foreign key?

A foreign key is a field in one table that refers to the primary key of another table, establishing a
relationship between the two tables.

8. What is a composite key?

A composite key is a primary key consisting of two or more columns that together uniquely identify a
record.

9. What is a candidate key?

A candidate key is a minimal set of attributes that can uniquely identify a tuple in a relation. One
candidate key becomes the primary key.

10. What is a super key?

A super key is a set of attributes that can uniquely identify a tuple in a relation. It may contain additional
attributes beyond the minimum required.

11. What is normalization?

Normalization is the process of organizing database tables to reduce redundancy and dependency by
dividing large tables into smaller, related tables.

12. What are the different normal forms?

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce-Codd Normal Form (BCNF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

13. What is First Normal Form (1NF)?

1NF requires that each column contains atomic (indivisible) values and each record is unique.

14. What is Second Normal Form (2NF)?

2NF requires that the table is in 1NF and all non-key attributes are fully functionally dependent on the
primary key.

15. What is Third Normal Form (3NF)?

3NF requires that the table is in 2NF and has no transitive dependencies (non-key attributes depend only
on the primary key).

16. What is denormalization?

Denormalization is the process of intentionally introducing redundancy into a database to improve query
performance.

17. What is an entity?

An entity is a distinguishable object or concept about which data is stored in the database.

18. What is an attribute?

An attribute is a property or characteristic of an entity that describes it.

19. What is a relationship?

A relationship is an association between two or more entities.

20. What are the types of relationships in DBMS?

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

Many-to-Many (M:N)
21. What is an ER diagram?

An Entity-Relationship diagram is a visual representation of entities, attributes, and relationships in a

database.

22. What is data integrity?

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database.

23. What are the types of data integrity?

Entity Integrity

Referential Integrity

Domain Integrity
User-defined Integrity

24. What is a constraint?

A constraint is a rule that limits the type of data that can be stored in a table to maintain data integrity.

25. What are the types of constraints?

NOT NULL
UNIQUE

PRIMARY KEY
FOREIGN KEY
CHECK

DEFAULT

26. What is ACID properties?

ACID stands for:

Atomicity: All operations in a transaction succeed or fail together

Consistency: Database remains in a valid state

Isolation: Concurrent transactions don't interfere with each other

Durability: Committed changes are permanent

27. What is a transaction?

A transaction is a logical unit of work that contains one or more database operations that must be
executed as a single unit.

28. What are the transaction states?

Active

Partially Committed
Committed

Failed

Aborted

29. What is concurrency control?

Concurrency control manages simultaneous access to the database by multiple users to maintain data
consistency.
30. What is a deadlock?
A deadlock occurs when two or more transactions are waiting for each other to release resources,
creating a circular dependency.

31. What is data independence?

Data independence is the ability to modify database schema without affecting the applications that use
the database.

32. What are the types of data independence?

Logical Data Independence: Changes to logical schema don't affect external schema
Physical Data Independence: Changes to physical schema don't affect logical schema

33. What is a database schema?

A database schema is the logical structure that defines how data is organized, including tables, fields,
relationships, and constraints.

34. What are the levels of database schema?

External Schema (View Level)

Conceptual Schema (Logical Level)

Internal Schema (Physical Level)

35. What is data abstraction?

Data abstraction hides the complexity of data storage and presents a simplified view to users through
different levels of abstraction.

Intermediate Level Questions (36-75)

36. What is a B-tree?

A B-tree is a self-balancing tree data structure used in databases for indexing that maintains sorted data
and allows searches, insertions, and deletions in logarithmic time.

37. What is an index?

An index is a data structure that improves the speed of data retrieval operations on a database table at
the cost of additional storage space.

38. What are the types of indexes?

Clustered Index

Non-clustered Index
Unique Index

Composite Index

Partial Index

Functional Index

39. What is the difference between clustered and non-clustered indexes?

Clustered Index: Physically reorders table data, one per table

Non-clustered Index: Separate structure pointing to table rows, multiple per table
40. What is query optimization?

Query optimization is the process of selecting the most efficient execution plan for a database query to
minimize resource usage and response time.

41. What is a query execution plan?

A query execution plan is a sequence of steps that the database engine follows to execute a query,
showing how tables are accessed and joined.

42. What is cost-based optimization?

Cost-based optimization selects the execution plan with the lowest estimated cost based on factors like
I/O operations, CPU usage, and memory requirements.

43. What is rule-based optimization?

Rule-based optimization uses predefined rules and heuristics to determine the best execution plan for a
query.

44. What is database partitioning?

Database partitioning divides large tables into smaller, manageable pieces called partitions to improve
performance and manageability.

45. What are the types of partitioning?

Horizontal Partitioning (Sharding)

Vertical Partitioning

Functional Partitioning
Range Partitioning

Hash Partitioning

List Partitioning

46. What is database replication?

Database replication is the process of copying and maintaining database objects in multiple databases to
ensure consistency and availability.

47. What are the types of replication?

Master-Slave Replication
Master-Master Replication

Snapshot Replication

Transactional Replication

Merge Replication

48. What is database clustering?

Database clustering involves grouping multiple database servers to work together as a single system for
high availability and load distribution.

49. What is a data warehouse?

A data warehouse is a centralized repository that stores large amounts of historical data from multiple
sources for analytical processing and reporting.
50. What is OLTP?

OLTP (Online Transaction Processing) systems handle day-to-day operational transactions with high
concurrency and quick response times.

51. What is OLAP?

OLAP (Online Analytical Processing) systems are designed for complex analytical queries and reporting
on large datasets.

52. What is the difference between OLTP and OLAP?

OLTP: Transaction-oriented, normalized, real-time, high concurrency

OLAP: Analysis-oriented, denormalized, historical data, complex queries

53. What is a stored procedure?

A stored procedure is a precompiled collection of SQL statements stored in the database that can be
executed as a single unit.

54. What are the advantages of stored procedures?

Improved performance

Code reusability

Enhanced security

Reduced network traffic

Centralized business logic

55. What is a trigger?

A trigger is a special type of stored procedure that automatically executes in response to specific
database events.

56. What are the types of triggers?

BEFORE triggers
AFTER triggers

INSTEAD OF triggers

Row-level triggers

Statement-level triggers

57. What is a view?

A view is a virtual table based on the result of a SELECT statement that provides a way to present data
without storing it physically.

58. What are the advantages of views?

Data security

Simplified queries

Data abstraction

Logical data independence

59. What is a materialized view?

A materialized view is a physical copy of data resulting from a query that is stored and can be refreshed
periodically.

60. What is database backup?

Database backup is the process of creating copies of database data and structures to protect against data
loss.

61. What are the types of database backups?

Full Backup

Incremental Backup

Differential Backup

Transaction Log Backup

62. What is database recovery?

Database recovery is the process of restoring a database to a consistent state after a failure using backup
files and transaction logs.

63. What are the recovery techniques?

Log-based Recovery

Shadow Paging

Checkpoint Recovery

ARIES (Algorithm for Recovery and Isolation Exploiting Semantics)

64. What is a checkpoint?

A checkpoint is a point in the transaction log where all dirty pages in memory are written to disk,
providing a recovery point.

65. What is write-ahead logging?

Write-ahead logging is a technique where log records are written to stable storage before the
corresponding data pages are modified.

66. What is two-phase commit protocol?

Two-phase commit is a distributed transaction protocol that ensures all participating databases either
commit or abort a transaction.

67. What is database security?

Database security involves protecting databases from unauthorized access, data breaches, and other
security threats.

68. What are the database security measures?

Authentication
Authorization

Encryption

Auditing

Access Control

Data Masking
69. What is role-based access control?

Role-based access control assigns permissions to roles rather than individual users, simplifying security
management.

70. What is database auditing?

Database auditing involves tracking and logging database activities for security, compliance, and
monitoring purposes.

71. What is data mining?

Data mining is the process of discovering patterns, relationships, and insights from large datasets using
statistical and machine learning techniques.

72. What is metadata?

Metadata is data about data that describes the structure, content, and context of database objects.

73. What is a data dictionary?

A data dictionary is a centralized repository that contains metadata about database objects, including
tables, columns, and relationships.

74. What is database tuning?

Database tuning is the process of optimizing database performance through configuration changes,
index optimization, and query tuning.

75. What is connection pooling?

Connection pooling is a technique that maintains a cache of database connections to reduce the
overhead of establishing connections.

Advanced Level Questions (76-110)

76. What is the CAP theorem?

The CAP theorem states that a distributed database system can only guarantee two of three properties:
Consistency, Availability, and Partition tolerance.

77. What is eventual consistency?

Eventual consistency is a consistency model where the system will become consistent over time,
assuming no new updates are made.

78. What is BASE properties?

BASE stands for:

Basically Available: System remains operational

Soft state: State may change over time

Eventual consistency: System will become consistent eventually

79. What is sharding?

Sharding is a horizontal partitioning technique that distributes data across multiple database instances to
improve scalability.

80. What are the sharding strategies?

 Range-based Sharding

Hash-based Sharding

Directory-based Sharding

Geographic Sharding

81. What is database federation?

Database federation is a technique that allows multiple databases to function as a single virtual database
system.

82. What is master-slave replication?

Master-slave replication involves one master database that handles writes and multiple slave databases
that handle reads.

83. What is master-master replication?

Master-master replication allows multiple databases to accept both read and write operations with
bidirectional synchronization.

84. What is database load balancing?

Database load balancing distributes database requests across multiple servers to optimize resource
utilization and response times.

85. What is a distributed database?

A distributed database is a database that is spread across multiple locations and managed by multiple
database management systems.

86. What are the advantages of distributed databases?

Improved performance

Increased reliability

Better scalability

Local autonomy

Reduced communication costs

87. What are the challenges of distributed databases?

Data consistency

Network latency

Complexity of management

Distributed transactions
Security concerns

88. What is data fragmentation?

Data fragmentation divides a database into smaller pieces (fragments) that are stored at different
locations in a distributed system.

89. What are the types of fragmentation?

Horizontal Fragmentation

Vertical Fragmentation
 Mixed Fragmentation

90. What is data locality?

Data locality refers to storing data close to where it's most frequently accessed to reduce access time and
network overhead.

91. What is a distributed transaction?

A distributed transaction is a transaction that involves multiple databases or systems across a network.

92. What is the Byzantine Generals Problem?

The Byzantine Generals Problem is a situation in distributed systems where components may fail and
send conflicting information.

93. What is consensus in distributed systems?

Consensus is the process of achieving agreement among distributed nodes on a single data value or
decision.

94. What is the Raft consensus algorithm?

Raft is a consensus algorithm designed to be more understandable than Paxos while providing the same
guarantees.

95. What is database versioning?

Database versioning involves managing changes to database schema and data over time through version
control.

96. What is schema evolution?

Schema evolution is the process of modifying database schema while maintaining compatibility with
existing applications and data.

97. What is data lineage?

Data lineage tracks the flow of data from its origin through various transformations to its final destination.

98. What is data governance?

Data governance is a framework that ensures data quality, privacy, security, and compliance throughout
the data lifecycle.

99. What is data quality?

Data quality refers to the condition of data based on factors like accuracy, completeness, consistency, and
timeliness.

100. What is database monitoring?

Database monitoring involves continuously tracking database performance, health, and usage to identify
issues and optimize performance.

101. What is database capacity planning?

Database capacity planning involves forecasting future database resource requirements based on growth
patterns and usage trends.

102. What is database virtualization?

Database virtualization creates a virtual layer that abstracts physical database resources, allowing multiple
virtual databases to share resources.

103. What is in-memory database?

An in-memory database stores data primarily in main memory rather than disk storage for faster access
and processing.

104. What is columnar database?

A columnar database stores data by columns rather than rows, optimizing for analytical queries and data
compression.

105. What is time-series database?

A time-series database is optimized for storing and querying time-stamped data points, commonly used
for monitoring and analytics.

106. What is graph database?

A graph database uses graph structures with nodes, edges, and properties to represent and store data,
optimized for relationship queries.

107. What is document database?

A document database stores data in document format (usually JSON, BSON, or XML) and is schema-
flexible.

108. What is key-value database?

A key-value database is the simplest NoSQL database that stores data as key-value pairs with high
performance for simple operations.

109. What is polyglot persistence?

Polyglot persistence is the practice of using different database technologies for different data storage
needs within a single application.

110. What is database as a service (DBaaS)?

DBaaS is a cloud service model where database functionality is provided as a service, with the provider
managing infrastructure and maintenance.

Key Areas to Focus On

Database Design
Entity-Relationship modeling

Normalization and denormalization

Schema design principles

Performance Optimization
Indexing strategies
Query optimization

Database tuning

Transaction Management
 ACID properties

Concurrency control

Deadlock handling

Distributed Systems
CAP theorem
Sharding and partitioning

Replication strategies

Modern Database Technologies

NoSQL databases

Cloud databases

NewSQL systems

Interview Tips
1. Understand Fundamentals: Master basic concepts before advanced topics

2. Real-world Examples: Relate concepts to practical scenarios

3. Trade-offs: Discuss advantages and disadvantages of different approaches
4. Scalability: Consider how solutions scale with data growth

5. Current Trends: Stay updated with modern database technologies

6. Problem-solving: Think through database design problems systematically

Remember to explain your reasoning and provide examples when discussing these concepts during
interviews!