Some of the common questions asked by recruiters in the technical interview for the DBMS are as follows: Database Basics, SQL, Keys, Normalization, Transactions, ACID properties, Joins, Indexing, Concurrency Control, Database Optimization. It is crucial for candidates to prepare for DBMS interview questions when applying for software development, backend development, data engineering, database administration, and other IT-related positions. The questions asked on the freshers will be of the basic nature, while those for experienced students can be scenario-based, with questions related to performance, scalability, transactions and database design.
What Is DBMS?
A Database Management System (DBMS) is the software that is used to create, store, organize, retrieve, update and manage a data base. It serves as a bridge between the users or applications and the database, and it assists in keeping data secure, consistent and in a correct form. MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server and other database technologies are common database systems. DBMS concepts are applicable in various fields such as banking, e-commerce, healthcare, education, social media, enterprise application, etc.
Interviews require questions to be asked by interviewers of the DBMS, as knowledge of databases is crucial for professionals and developers related to databases.
Basic DBMS interview Questions for Freshers
1. What is the difference between database and DBMS?
A database is an organized collection of data while a DBMS is the software that manages the data. For instance, student information might include students’ names, roll numbers, courses and marks. A DBMS offers tools for adding, changing, removing, looking up, protecting and manipulating this data.
2. What is the difference between the DBMS and RDBMS?
A DBMS is a general database management system, while an RDBMS (Relational Database Management System) is an organization of data in tables and relationships amongst them.
Generally, RDBMS maintains relationship using primary key, foreign keys, and supports SQL for managing relational data.
They include such databases as MySQL, PostgreSQL, Oracle Database, and SQL Server.
3. What are the different types of DBMS?
The main categories of the DBMS are:
- Hierarchical DBMS: The DBMS that represents the data in a tree.
- Network DBMS: The structure used to represent a relationship is a network.
- Relational DBMS: It is a database management system that organizes data into tables.
- Object Oriented DBMS: Stores data in objects.
- NoSQL databases: Such as document, key-value, column-family, and graph databases.
4. What is a key in DBMS?
A key is an attribute or combination of attributes that is used to identify a record and/or to establish relationships between tables.
Common types include:
- Primary Key: Uniquely identifies each record. An attribute or combination of attributes that can be used to uniquely identify a record is called a Candidate Key.
- Super Key: Any collection of attributes which can be used to identify a record.
- Foreign Key: A key in another table that it depends on.
- Composite Key: A key comprised of a number of columns.
- Alternate Key: A key that is not chosen as the primary key.
5. What is a Primary Key?
A primary key is a key used to uniquely identify each record in a table. It may have no duplicate values, and in the relational model, it may not have a null value. For instance, the student_id column may be the primary key of a student table.
6. What is a foreign key?
A foreign key is a column or set of columns that contains a value or set of values that refer to a value or set of values in another table. Helps create relationships between tables and enforce referential integrity. For instance if you have an orders table, it might have a customer_id foreign key referencing the customer_id in a customers table.
7. What do you mean by Normalization in DBMS?
Normalisation is the technique of structuring a database so that there is no redundant data and no data anomalies. The main aims of Normalisation are to ensure data integrity and ease of maintenance of the database. One of the most common normalization forms is 1NF, 2NF, 3NF and BCNF.
8. Explain 1NF, 2NF, and 3NF.
- First Normal Form (1NF): Values in each field must be atomic and must not be part of any repeating group.
- Second Normal Form (2NF): A table should be in 1NF and each non-key attribute should be dependent on the full primary key.
- Third Normal Form (3NF): A table should be in 2NF, and should not have any transitive dependency in non-key attributes.
BCNF is a stronger version of 3NF which captures some of the functional dependency cases more strictly.
9. What is the meaning of ACID properties?
Database systems need to be reliable when processing transactions, and this is provided by the ACID properties.
- Consistency: A transaction is executed in a consistent manner.
- Consistency: A transaction takes the database from one valid state to another.
- Isolation: Concurrent transactions are appropriately isolated from each other.
- Durability: Committed changes are stored even when the system fails.
Some of these properties are particularly relevant to, for example, banking and payment systems.
10. What are the various types of Primary Keys?
A transaction is a series of database operations that must happen as a single unit. It can consist of one or more operations that are to be performed in concert.
Transferring of money to a bank account for instance, debiting one account and crediting another. The two operations must both be successful so that they stay in sync.
SQL Interview Questions
1. What Is SQL?
SQL, short for Structured Query Language, is a type of language that is interpreted by relational databases. It can be used for database design, retrieval, insertion, updating, deletion and access control.
There are generally two types of SQL statements:
| Category | Common Commands | Purpose |
| DDL | CREATE, ALTER, DROP, TRUNCATE | Defines database structures |
| DML | INSERT, UPDATE, DELETE | Modifies data |
| DQL | SELECT | Retrieves data |
| DCL | GRANT, REVOKE | Controls permissions |
| TCL | COMMIT, ROLLBACK, SAVEPOINT | Controls transactions |
2. What Is the Difference Between DELETE, TRUNCATE, and DROP?
- DELETE will delete selected rows and may include a WHERE clause.
- TRUNCATE deletes all rows from a table, and is typically used when the table structure is not to be deleted.
- DROP will delete the table and all of its data.
Some databases may behave differently with regard to rollback, logging, and identity-reset – candidates should be familiar with the DBMS they are using.
3. What are SQL JOINs?
A JOIN is a combination of two or more tables that are related by a matching column.
Common JOIN types are:
- INNER JOIN: Returns the matching rows in both tables.
- LEFT JOIN: Returns all of the rows in the left table and the matching rows of the right table.
- RIGHT JOIN: All records in Right Table + matching records in Left Table.
- FULL OUTER JOIN: Returns matching and non-matching records of both tables if available.
- CROSS JOIN: To generate a Cartesian product.
- SELF JOIN: Joins a table with itself.
JOIN questions are commonly asked in the SQL and DBMS technical interview.
4. Explain Index in DBMS?
An index is a data structure that aids the database in finding a record more quickly. Index structures can vary from B-trees to hash-based methods, depending on the workload and database engine. Indexes greatly enhance read performance, but they consume extra storage and increase the overhead of modifying data because the database must continuously maintain them.
5. What Is a View?
A view is a virtual table that is defined by a SQL statement. It can simplify complex queries, limit access to a specific number of columns or rows in a table, and offer an abstraction layer to the underlying tables. A standard view is purely virtual and does not store data, whereas a materialized view does physically store the query results on disk.
Intermediate DBMS Interview Questions
1. What is Referential Integrity?
Referential integrity guarantees that related tables in a relational database have consistent relationships.
For example, if a customer ID is included in an order, the customer should be present in the referenced customer table, according to the foreign-key constraint rules set up in the database. It can prevent invalid references and orphan records.
2. What is Data Independence?
Data independence is the characteristic of database architecture to isolate data changes to one level of the design and not to the other levels.
There are two significant ones:
- Physical Data Independence: The logical structure of the database should not change if there are changes in the physical storage.
- Logical Data Independence: Changes to the logical schema should have minimal impact on application programs and user views.
3. What is Deadlock in DBMS?
A deadlock is a situation where two or more transactions are waiting for each other’s resources in a circular waiting relationship.
For instance, Transaction A might acquire Lock 1 and wait for Lock 2, and Transaction B might acquire Lock 2 and wait for Lock 1. There are four approaches to deadlocks: deadlock prevention, deadlock detection and recovery, timeouts, and proper locking strategies.
4. What is concurrency control?
The database system uses concurrency control to coordinate simultaneous transactions and maintain data integrity. Dirty reads, lost updates, non-repeatable reads, and phantom reads are examples of write and read issues that may arise when there is no concurrency control. Locking, Timestamp ordering and MVCC (Multi-Version Concurrency Control) are typical methods.
5. What are the types of SQL transactions?
Levels of isolation determine what other transactions can see when they are observing the changes made by other transactions.
The typical levels are:
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
The higher the level of isolation, the more the level of concurrent access is restricted. But, increasing isolation will cause a decrease in concurrency and impact on performance. Actual behavior and defaults are different for different database systems.
Here are some advanced DBMS interview questions, answers, and explanations.
6. What is the difference between Normalization and Denormalization?
Normalization minimizes data duplications and enhances data integrity by organizing data into well-designed tables.
Denormalization: Adding some redundant data to minimize joins and possibly boost read performance.
Transactional systems typically use normalization, but denormalization improves high-read applications, reporting systems, and analytical workloads.
7. What is CAP Theorem?
The CAP theorem is a fundamental principle of distributed systems. It says that a distributed system can’t satisfy both strong consistency and availability during a network partition.
The three properties are:
- Consistency: Each consistent read gets the newest consistent value.
- Response to Requests: It is ensured that requests are responded to.
- Partition Tolerance: The system functions even if there is a failure in communication between nodes.
In a real distributed system, partition tolerance is typically a desired feature, and consistency and availability will be traded off in such systems during partitions.
8. What is a clustered index?
In a database system in which clustered indexes are supported, a clustered index defines the way that the records are stored in the table based on the indexing structure.
A table usually has one or more indexes or equivalent data-ordering structures that are clustered by data—usually one—therefore, a table’s data organization can typically support only one such index. How this is done varies per database engine.
9. What is Non-Clustered Index?
A non-clustered index is an index structure that is separate from any table index, and contains indexed values that are related to the table records.
There can be many non-clustered indexes on a table. But too many indexes can require more disk space and can slow INSERT, UPDATE and DELETE.
10. How Do You Optimize a Slow SQL Query?
Some of the best practices for SQL query optimization are:
- Examine the implementation strategy.
- Recognize costly scans, joins and sorts.
- Create appropriate indexes.
- Don’t include unnecessary columns and SELECT *.
- Optimize JOIN conditions.
- Review filtering conditions.
- Eliminate subqueries that you can replace with faster, more efficient alternatives.
- Ensure that statistics are kept up to date on the databases, if applicable.
- For very large tables, you may want to consider partitioning.
- Determine if schema design is causing the performance issue.
Optimization is determined by the database engine, data distribution, query workload and execution plan.
Scenario-Based DBMS Interview Questions
1. Design a database for an E-commerce website
For an ecommerce application, the tables in the database might look like this:
- Customers
- Products
- Categories
- Orders
- Order Items
- Payments
- Addresses
- Inventory
- Reviews
The primary and foreign keys should be used to establish relationships. The design should not include redundant data and should include data that can be used to support critical queries including customer order, inventory, and order totals.
2. How would you deal with millions of records?
Some of the potential solutions are proper indexing, query optimization, partitioning, caching, pagination, efficient schema design and appropriate hardware resources for a database with millions of records.
If distributed workloads are in use, consider replication or sharding as additional options, depending on the application’s needs.
3. How Duplication of Data can be prevented?
Appropriate database constraints and application logic can help control duplicate data. Data quality can be helped with primary keys, UNIQUE constraint, foreign keys and proper normalization.
An UNIQUE constraint can be used to protect an email column that must be unique, for example.
4. Difference between OLTP and OLAP?
OLTP (Online Transaction Processing) systems process many brief transactions, like purchases, payments, and account updates.
OLAP (Online Analytical Processing) systems are intended for complex queries, reporting, aggregation and analysis of large amounts of data.
In an OLTP system, transaction integrity and speed of writing is important, whereas in an OLAP system, the performance of analytical queries is important.
5. What is a Stored Procedure?
A stored procedure is a group of SQL statements and procedural logic that’s stored in the database and executed when you call it.
A stored procedure may be used to increase the amount of database operations that can be centralized, to share logic, and to be able to decrease the amount of application-database communication. These features and syntax are different in different database systems.
Tips to Crack DBMS Interviews
- Get the concepts right: Edit DBMS, RDBMS, keys, normalization, constraints and relationship.
- Learn and solve queries with JOINs, GROUP BY, subqueries, aggregate functions and window functions.
- Be familiar with transactions: ACID properties, concurrency control, locking, and isolation levels.
- Know the concept of study indexing and when it helps improve performance of queries.
- Create scenarios: Practice Database design questions based on ecommerce, bank or employee management systems.
- Know optimization: Understand how to read execution plans and know the most important things to look at for slow queries, missing indexes, inefficient joins, etc.
- Edit common differences: Create topics like DELETE vs TRUNCATE vs DROP, DBMS vs RDBMS, clustered vs non-clustered indexes etc.
Conclusion
Knowing the important DBMS interview questions and answers can help you immensely while preparing for technical interviews. The first year students should learn basics like DBMS vs RDBMS, keys, Normalisation, SQL, JOINs, ACID properties, Transactions and Constraints.
An experienced professional should avoid definitions and get to know the behaviour of databases in real world workloads. The indexing, query optimization, concurrency control, isolation level, deadlocks, replication, partitioning and distributed database concepts are particularly useful.
The key is to have an understanding of why each of the DBMS concepts is used and try to work on these in real-life scenarios. Candidates can learn and practice SQL skills in a hands-on manner and confidently answer the SQL-related questions asked in interview scenarios.
FAQs
A1. Some of the most important topics are: keys, normalisation, SQL, JOINs, ACID properties, transactions, indexing, and concurrency control.
A2. Yes. It’s an essential topic for software development, backend, testing, and data-related jobs.
A3. Use SELECT, JOIN, GROUP BY, aggregate functions, subqueries, CTEs and window functions.
A4. ACID is a bunch of conditions that make transactions reliable: Atomicity, Consistency, Isolation, Durability.
A5. Learn database indexing, execution plans, query optimization, transactions, isolation levels, database replication, partitioning, sharding and database design.
Also Read: IIT-Pulse Institute: Courses, IIT-JEE Preparation, Admission, Fees, Faculty, Results and Reviews
