A database is the backbone of any IT system. As a Database Developer, I am sure you would want to prove your expertise and knowledge in your next job interview and win a role. Here is the list of top 30 DBMS Interview questions that will help you sail through. In addition, to enhance your skills you could go through SQL Tutorials and Top 50 SQL Interview Questions as SQL is an integrated and a vital feature in a DBMS (Database Management System).
DBMS Interview Questions
Question 1: Define a DBMS (Database Management System) and what is it used for?
Answer: DBMS are software applications that help you build and maintain logically related data also known as the database. Here are a few advantages of using a DBMS:
- Data Redundancy is controlled.
- Unauthorized access is restricted.
- Enforces integrity constraints.
- Data availability and sharing
- Data protection and storage
Question 2: What is a Hierarchical database Model?
Answer: In a hierarchical database model, data is organized into nodes in a tree-like structure. A node is connected to only one parent node above it. Hence data in this model has a one-to-many relationship. An example of this model is the Document Object Model (DOM) often used in web browsers.
Question 3: What is a Network Model?
Answer: Network database model is a refined version of a hierarchical model. Here too, data is organized in a tree-like structure. However, one child node can be connected to multiple parent nodes. This gives rise to a many-to-many relationship between data nodes. IDMS (Integrated Database Management System), Integrated Data Store (IDS) are examples of Network Databases.
Question 4: What is an RDBMS?
Answer: A relational database is organized into tables, records, and column and there is a well-defined relationship between database tables. A relational database management system (RDBMS) is an application that allows you to create, update, and administer a relational database. Tables communicate and share information which enables data search, data organization, and reporting. An RDBMS is a subset of a DBMS.
Question 5: What is an Object-Oriented Database Model?
Answer: In an object-oriented database model, data is represented by objects. For example, a multimedia file or record in a relational database is stored as a data object as opposed to an alphanumeric value.
Question 6: What is SQL?
Answer: SQL (Structured Query Language) is a programming language used to communicate with data stored in databases. SQL language is relatively easy to write, read, and interpret.
Question 7: What are DDL, DML and DCL statements in SQL?
- DDL: The Data Definition Language is used to define the database and schema structure by using a set of SQL Queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.
- DCL: The Data Control Language is used to control the access of the users to the database by using a set of commands like GRANT and REVOKE in the SQL Query.
- DML: The Data Manipulation Language is used for maintaining the data by using SQL Queries like SELECT, INSERT, DELETE and UPDATE.
Question 8: What do you mean by Index hunting?
Answer: A database index is a data structure that improves the speed of data retrieval operations on a database. The procedure of boosting the collection of indexes is named as Index hunting. It is done by using methods like query optimization and query distribution.
Question 9: What is a distributed database?
Answer: A distributed database is a collection of multiple interconnected databases that are spread physically across various locations. The databases can be on the same network or on multiple networks. A DDBMS (Distributed – DBMS) integrates data logically so to the user it appears as one single database.
Question 10: What is a database partitioning?
Answer: Database partitioning is a process where a logical database is divided into distinct independent parts. The database objects like tables, indexes are subdivided and managed and accessed at the granular level.
Question 11: Explain the importance of database partitioning?
Answer: Partitioning is a powerful functionality that increases performance with decreased cost. It enhances manageability and improves the availability of data.
Question 12: What is a static SQL?
Answer: In a static SQL, the SQL statements are embedded or hard-coded in the application and they do not change at runtime. How data is to be accessed is predetermined hence it is more swift and efficient. The SQL statements are compiled at compile time
Question 13: What is dynamic SQL?
Answer: In a dynamic SQL, SQL statements are constructed at runtime, for example, the application can allow the user to create the queries. Basically, you can build your query at runtime. It is comparatively slower than the static SQL as the query is compiled at runtime.
Question 14: Define Data Warehousing.
Answer: Data Warehousing is a technique that aggregates a large amount of data from one or more sources. Data analysis is performed on the data to take strategic business decisions for organizations.
Question 15: Name one open source RDBMS.
Answer: MySQL is an open-source relational database management system, which stores data in tables and maintains a relationship between the data. It uses the most powerful query language SQL (Structured Query Language) for database access and has a very powerful syntax to create simple and complex queries to retrieve and structure data. Data in MySQL is organized and conforms to a certain format, and hence it is the most popular structured database today. Its ‘free’ source code is available for study, modification, and distribution.
Question 16: What is MongoDB?
Answer: MongoDB is a non-relational unstructured open-source database. This document-oriented database stores your data in collections made out of individual documents. In MongoDB, a document is a big JSON object with no particular format or schema. MongoDB represents JSON documents in a binary encoded format named as BSON
Question 17: What is BI (Business Intelligence)?
Answer: Business intelligence (BI) is a technology-driven process for analyzing data and presenting information to help executives, managers, and other corporate end users make business decisions. There are many tools available like SAP Business Intelligence, MicroStrategy, Sisense and so on. The tools are user-friendly that helps you gather data from varied sources for analysis.
Question 18: What is the role of a DBA in DBMS?
Answer: The Database Administrator (DBA) plays some important roles in an organization. They are as follows:
- Installing and configuring databases
- Performance Monitoring
- Security planning and implementation
- Data backup and recovery
- Data migration
Question 19: What is an ER diagram in DBMS?
Answer: An entity-relationship model or an entity relationship diagram is a visual representation of data which is represented as entities, attributes and relationships are set between entities.
Question 20: What is an Entity in an ER diagram?
Answer: An entity can be a real-world object, which can be easily identifiable. For example, in a library database, books, publishers and members can be considered as entities. All these entities have some attributes or properties that give them their identity. In an ER model, the entities are related to each other.
Question 21: What is Data Mining?
Answer: Data mining is a process of sorting through a large amount of data to identify patterns and trends. It uses complex mathematical and statistical algorithms to segment data for the prediction of likely outcomes. There are many tools for Data Mining like RapidMiner, Orange, Weka and so on.
Question 22: What is meant by query optimization?
Answer: Query optimization is an important feature when it comes to the performance of a database. Identifying an efficient execution plan for evaluating and executing a query that has the least estimated cost and time is referred to as query optimization.
Question 23: What is a Catalog?
Answer: A catalog is a table that contains the information such as the structure of each file, the type, and storage format of each data item and various constraints on the data. The information stored in the catalog is called Metadata.
Question 24: How many types of relationship exist in database designing?
Answer: There are three major relationship models in database design:-
One-to-one – A row in one table (A) is related to only one row in another table (B)
One-to-many – A row in a table (A) is linked to many rows in another table (B). But a row in table (B) is linked to only one row in table (A).
Many-to-many – A row in the table (A) is linked to many rows in the table (B) and vice-versa
Question 25: What are the primitive operations common to all database management systems?
Answer: Addition, deletion, and modification are the most important primitive operations common to all DBMS.
Question 26: What is cardinality in context to a database?
Answer: In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more are the duplicated values in a column.
Question 27: What is SQL SERVER?
Answer: SQL Server is an RDBMS developed by Microsoft. It is very stable and robust hence popular. The latest version of SQL Server is SQL Server 2017.
Question 28: Under what conditions should we use indexes?
Answer: Indexes can be created to enforce uniqueness, to facilitate sorting, and to enable fast retrieval by column values. When a column is frequently used it is a good candidate for an index to be used with suitable conditions in WHERE clauses.
Question 29: What is a hashing technique in a database?
Answer: Hashing is the transformation of a string of characters into a usually shorter fixed-length value or key that represents the original string. Hashing is used to index and retrieve items in a database because it is faster to find the item using the shorter hashed key than to find it using the original value.
Question 30: Describe concurrency control?
Answer: Concurrency control is the process of managing simultaneous operations on a database so that database integrity is not compromised. There are two approaches to concurrency control.
They are Locking (controlling access to data items using locks) and Versioning (using Multi-Version Concurrency Control) respectively.
So, all the very best! Do let us know the DBMS questions you faced in the interview that are not covered here in this article so that we can add those here for the benefit of the DBMS community.