The backbone of any data analytics profession is knowing your way around a Database Management System (DBMS). In order to create functional database systems, you need to be able to organize, evaluate, review and make sense of vast amounts of information. Whether you’ve already begun a career in data analytics or are just starting, here are some DBMS interview questions that will help you prove your expertise and knowledge in your next job interview and get the job you always wanted.
Let us now begin with the basic DBMS interview questions.
Basic DBMS Interview Questions
1. What is DBMS?
Database Management System or DBMS is a collection of programs that enables users to store and retrieve data from a database securely. With the help of a DBMS, you can perform a number of operations such as creating a new database, inserting and deleting data, updating the data, etc. It is a much safer way of storing data as compared to the traditional file-based system. Some common examples are MySQL, Oracle and SQL Server.
2. What are the advantages of DBMS over traditional file-based systems?
Some of the most important advantage of DBMS over traditional file-based systems are:
- Data redundancy can be controlled
- No unauthorized access of data
- Multiple user interfaces
- Easy accessibility and processing of data
- Provides backup and data recovery
3. What do you mean by normalization in DBMS?
Normalization is the process of minimizing redundancies in a database. It also helps to overcome insert, delete and update anomalies.
4. What do you mean by denormalization in DBMS?
Denormalization is the process of adding redundant data to a database. It is done after normalization to avoid costly joins. It is a common database optimization technique which helps to boost the performance of a database.
5. Explain the different types of DBMS architecture.
There are three types of DBMS architecture:
- Single tier architecture - The data is readily available on the client machine.
- Two tier architecture - The DBMS software is present on the client machine and the database is present on the server machine.
- Three tier architecture - A layer is present between the client and server machine and there is no direct communication between them. A client DBMS application on the client machine interacts with a server DBMS application on the server machine.
6. What is a checkpoint in DBMS?
A checkpoint or savepoint is used to declare a state where all the previous logs are removed from the system. In the event of a system crash, checkpoints can be used for recovery. Instead of performing the transactions from the very beginning, you can use checkpoints to get the last recorded snapshot of the database.
7. What is a database language?
A database language refers to the queries that are used for updating and modifying the data in a database. There are four main types of database languages:
- Data Definition Language (DDL) - These commands are used to update the data.
- Data Manipulation Language (DML) - These commands are used to manipulate the updated data.
- Data Control Language (DCL) - These commands are used for providing and revoking user access to a database.
- Transaction Control Language (TCL) - These commands are used to manage all the transactions in a database.
8. What do you mean by Data Model?
A Data model is a collection of tools that are used to describe data, semantics and constraints. They also help describe the relationship between the data entities and their attributes. Some of the most common data models are hierarchical data model, network model, entity relationship model and relational model. You can also check out other data modeling interview questions to learn more about data models.
9. What is the difference between an entity and an attribute?
An entity refers to a real-world object in a database. For example, in an employee database, the different entities can be employee, designation, department, and so on.
An attribute is a characteristic that describes an entity. For example, the entity “employee” can have name, ID and age as its attributes.
10. Explain the ACID properties of a DBMS.
ACID properties are the basic rules of a DBMS which needs to be followed in order to preserve data integrity. They are:
Atomicity - It is also known as the “all or nothing” rule, which implies that everything considered as one unit is either run to completion or not executed at all.
Consistency - This property implies that the data in the database remains consistent before and after every transaction.
Isolation - This property states that different numbers of transactions can be executed concurrently.
Durability - This property ensures that every transaction is stored in the non-volatile memory after the transaction is completed.
We will next look at the Technical DBMS interview questions and answers.
Technical DBMS Interview Questions
11. What do you mean by functional dependency?
Functional dependency or database dependency determines the dependence of one attribute to another. It is denoted by X -> Y, which means that Y is functionally dependent on X.
12. What is the main difference between DELETE and TRUNCATE command?
DELETE command is used to remove the rows in a table based on the condition set by the WHERE clause. The rows deleted can be rolled back.
TRUNCATE command is used to remove all the rows in a table without any conditions. The rows cannot be rolled back.
13. Explain the different normal forms for normalization.
The most common normal forms are:
- First Normal Form(1NF) - A table is said to be in 1NF if it holds only atomic values.
- Second Normal Form(2NF) - A table is said to be in 2NF if it is in 1NF and non-prime attributes are not dependent on any candidate key.
- Third Normal Form(3NF) - A table is said to be in 3NF if it is in 2NF and non-prime attributes are not dependent on any super key.
- Boyce & Codd Normal Form(BCNF) - A table is said to be in BCNF if it is in 3NF and for every functional dependency A->B, B is the super key of the table.
14. What are the differences between Hash join and Merge join?
Hash join - A hash join is used to join large tables.
Merge join - A merge join is used to join two input streams of the joined tables into a single output stream.
15. Define a relationship in DBMS and its various types.
A relationship is an association or link between two or more data entities. There are three main types of relationships in DBMS:
- One to one - A single record in one table is related to a single record in another table and vice versa.
- One to many / Many to one - A single record of one table is related to many records of other tables and vice versa.
- Many to many - Multiple records of one table are related to multiple records of another table.
- It describes only how the user interacts with the database.
16. Describe the different types of keys in a DBMS?
There are following types of keys in a DBMS:
Primary key - It is an attribute used to uniquely identify each record in a table. A primary key is compulsory for every table.
Candidate key - It is a set of attributes used to uniquely identify a table.
Super key - It is a set of attributes used to uniquely identify a tuple.
Foreign key - It is a field or a collection of fields used to link one table to a primary key from another table.
17. Explain the difference between intention and extension.
Intention - It is a constant value where the description of the database doesn’t change or is highly unlikely to change frequently. It is usually specified during the database design phase. Extension - It is the description of the actual database at a particular time, and is likely to change frequently.
We will next look at the RDBMS interview questions and answers.
RDBMS Interview Questions
18. What is RDBMS?
RDBMS stands for Relational Database Management System and is an extension of DBMS. It provides all the same features of DBMS, coupled with relational integrity. The key difference between DBMS and RDBMS is that data is stored as a file in DBMS whereas data is stored in the form of tables in RDBMS.
19. How do you communicate with an RDBMS?
SQL (Structured Query Language) is used in order to communicate with the RDBMS.
20. What is data abstraction?
Data abstraction is the process of hiding irrelevant details from the users. Since a database consists of complex data structures, data abstraction helps users to interact easily with the database. For example, a user can interact with the system using the GUI, but they may not necessarily know how the data is being stored in the backend.
21. What are the different levels of data abstraction?
There are three levels of data abstraction:
- Physical level - The physical level represents the lowest level of data abstraction. It shows complex data structures and describes exactly how the data is stored.
- Logical level - The logical level represents the middle level of data abstraction. It describes the data stored in the database and the relationship between them.
- View level - The view level represents the highest level of data abstraction
22. What is the difference between proactive, retroactive and simultaneous update?
Proactive update - This update occurs inside the database before it even becomes active in the real world.
Retroactive update - This update occurs in the database only after it occurs in the real world.
Simultaneous update - This update occurs at the same time as it does in the real world.
23. What is a deadlock?
A deadlock is a situation where a set of processes gets blocked due to a dependency on other processes. Each process holds up a resource that is required by another other process to run, and hence, cannot proceed further.
We will next look at the viva DBMS interview questions.
Get broad exposure to key technologies and skills used in data analytics and data science, including statistics with the Data Analytics Certification Program.
Viva DBMS Interview Questions
24. If an attribute doesn’t have any value, would you assign it a blank space, a ZERO or a NULL value?
All attributes that are unavailable, unknown, or have no assigned values fall under the NULL category. Whereas both ZERO represents an integer and BLANK represents an empty String value.
25. How do you know which database model to choose while creating a database?
This depends entirely on the purpose of the database, as each model has its own strengths. For example, if you want to use atomic data, a relational model works best. If you want to use text or semi-structured data, the document model works best.
26. Which abstraction level would you provide to the users who are not developers and system admins?
The VIEW level of data abstraction is typically given to users who only need the details of the table schema. This makes sure that the complex structure of the table and its physical storage are hidden from them.
27. In the event of a deadlock, what would you do?
You can take two approaches to address a deadlock:
- The lazy way - Do nothing. Simply restart the operation to get out of the deadlock.
- The proactive way - Preventing deadlocks to ever happen. Allocate the resources in such a way that a deadlock will never occur.
28. What should you do when you do not have any primary key for a table?
When there is no primary key for a table, you should create a “surrogate key” which acts as a pseudo key. It is simply a made up value which can be created by an auto incrementing ID.
29. Under what conditions should you use an index?
An index can be used when you want to enforce uniqueness in a database. You can also use it to facilitate sorting and perform fast retrieval. A column that is frequently used can be a good candidate for an index.
These are just some of the frequently asked DBMS interview questions that you might encounter during an interview. If you want to learn more answers and get a career-boosting DBMS certification, sign up for Simplilearn’s Data Analytics certification program today!
