database, SQL and Interview Questions

DBMS Interview Questions and Answers

Posted in database, SQL, Interview Questions
DBMS Interview Questions and Answers

Table of Contents

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 50 DBMS Interview questions that will help you sail through. In addition, to enhance your skills you could go through SQL Tutorials and Top SQL Interview Questions as SQL is an integrated and a vital feature in a DBMS (Database Management System).

DBMS Interview Questions and Answers

Question: 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: What is a Database?

Answer: The database is a collection of ordered data, ordered and stored electronically. Such organized data can be easily accessed, aggregated and managed. The database is managed and monitored through a Database Management System or DBMS.

Question: How would you define a DBMS checkpoint?

Answer: A checkpoint is a point before which all the transactions in the DBMS were committed, and the database was in a consistent state. It is a point when a backup is taken, and all the dirty pages (in-memory modified pages) and old transaction logs are cleared from the system to release memory space and stored permanently into a storage disk. For example, a manual checkpoint can be added as follows:

CHECKPOINT 10

where 10 is the duration in seconds.

Read more.

Question: Explain the different database languages types.

Answer: There are four types:

  • DDL (Data Definition Language): is used to define the database structure or skeleton, for example, creating the schema, tables, indexes, constraints etc. Some DDL statements are created, alter, drop, rename, truncate, comment.
  • DCL (Data Control Language): used to retrieve the saved data. Permissions and access control come under DCL and are done using the grant and revoke commands.
  • DML (Data Manipulation Language): Used for accessing and manipulating data. Some DML statements are select, insert, delete, update, lock table, explain plan etc. Basically, DML statements handle user requests.
  • TCL (Transaction Control Language): used to save or rollback changes done by DML. The commands used for TCL are commit and rollback.

Question: Do you think there are any drawbacks of file processing systems?

Answer: There are several disadvantages of file processing systems:

  • Data redundancy and inconsistency.
  • Accessing data is not efficient or easy.
  • Data could be stored in different formats and hence challenging to share.
  • Issues with concurrent access.

Question: What is meant by DBMS data abstraction?

Answer: Data abstraction is the hiding of irrelevant information from the user so that the interactions with users can be smooth. There are three levels of abstraction:

  • Physical Level: It is the lowest level of abstraction and tells how data is physically stored in memory. Data can be accessed through sequential or random access. Files are organized using B+ trees and hashing methods.
  • Logical Level: This level stores information in the form of tables. The relationships between various entities are also stored as simple structures.
  • View Level: ‘View level’ is the top-most extent of abstraction. Users can view only a part of the actual database, in the form of rows and columns. It is possible to have multiple views of the same database. Storage and implementation details are hidden from the users.

Question: Explain what you know about Functional Dependency?

A functional dependency expresses relationship between the non-key attributes and the primary key of a table. A functional dependency is of two types – trivial and non-trivial. The relationship can be described as A -> B where A is the determinant, and B is the dependent column.

Question: What is Denormalization?

Answer: It is a technique for database optimization where redundant data is added to one or more tables to avoid joins. This makes retrieval of data faster and more efficient and simpler queries.

Question: Explain the difference between extension and intension?

Answer: Intention is the constant value, i.e. the description of the database or the schema, which is unlikely to change frequently and is specified during the database design phase. The extension is the actual data at a particular time, also referred to as the database snapshot. It is likely to change frequently.

Question: What is System R?

Answer: System R is a database management system that provides a high level of data independence and abstraction of the database from the users. It has data control features like triggered transactions, authorization, integrity assertions and provisions for data consistency.

Question: Explain about the normal forms BCNF, 1NF, 2NF and 3NF.

Answer: All the above are normal forms of data, i.e. organizing data in such a way as to avoid data redundancy and insert, delete & update anomalies.

  • 1NF: also known as the First Normal Form. This rule states that a column of a table can hold only atomic (single) value.
  • 2NF: is short for second normal form. To apply the rule of 2NF, a table should be in 1NF. The rule states that any non-prime attribute (i.e. attribute that is not part of any candidate key) should not be dependent on the proper subset of the table’s candidate key.
  • 3NF: or third normal form, the table should already be in 2NF. The rule states that any transitive dependency (functional) of a “non-prime” feature on a super key should be removed.
  • BCNF: is an advanced and stricter version of 3NF. It is also more popularly known as 3.5NF. For BCNF, a table should be in 3NF and follow the rule that for any functional dependency A->B, A should be the super key of the table.

Question: What are ACID properties?

Answer: ACID properties are for transactions:

  • A => Atomicity: the entire transaction happens in one go
  • C => Consistency: database must be consistent before and after the transaction
  • I => Isolation: transactions do not interfere with each other; multiple
  • transactions can happen simultaneously
  • D => Durability: A successful transaction is reflected even if a system failure happens.

Question: Explain the Difference between a DELETE command and TRUNCATE command?

Answer: 

DELETE TRUNCATE
DML type DDL type
Supports WHERE Condition Doesn’t support WHERE condition
Can acquire a row lock Locks the entire table and page
Speed of execution is less It is faster than delete
There is a log for each row A single log that indicates deallocation of a page
Example: DELETE FROM employee WHERE emp_id = 2012; Example: TRUNCATE TABLE employee_temp

Question: Explain 2-Tier architecture?

Answer: In DBMS, 2-Tier architecture is a type of database architecture where the UI or view layer (also called the presentation) runs on a client machine (desktop, laptop, tablet, phone, etc.) and the data is stored on a server. To call the database, clients use the ODBC connectivity APIs. This architecture enhances the security of the database, as the client can’t access the database directly.

Question: Explain the different types of database keys?

Answer: There are several types of DBMS keys:

  • Primary Key: column or set of columns that identify a particular row in a table.
  • Super Key: single key or set of multiple keys that identifies rows of a table.
  • Candidate Key: A set of attributes which identify tuples of a table uniquely. It is essentially a super key without repeated attributes.
  • Alternate Key: One or more columns of a table that collectively identify each of a table uniquely.
  • Foreign Key: A common column that defines the relationship between two tables. maintains data integrity.
  • Compound Key: Consists of two more attributes that uniquely identify a record, even when the column is not unique by itself
  • Composite Key: Primary key that has two or more attributes like {customer_id + mobile_number}
  • Surrogate Key: Created when there is no naturally available primary key for a table. provides a unique identity to a row in the table.

Question: Explain the purpose of normalization in DBMS?

Answer: Normalization is an important process that removes redundancy from a set of relations. It can reduce the irregularities in the insert, delete and update by dividing huge tables into smaller tables and use keys to link them.

Question: How is the Entity, Entity Type, and Entity Set different from each other in DBMS?

Answer: 

  • Entity: An entity is an object with physical existence that is independent and can be differentiated with other objects: example, an employee, student, course, job etc.
  • Entity type: can be said as the collection of similar entities.
  • Entity set: Entity set is a combination of entities of one type at any point in time.

Question: What is a CLAUSE in terms of SQL?

Answer: CLAUSE is an optional statement in SQL that defines a condition to present data. For example,

select * from employee WHERE emp_id = 2012;

here, WHERE is the clause that identifies the row to be obtained. Some other clauses in SQL are – GROUP BY, HAVING, ORDER BY

Question: Differentiate between aggregation and atomicity?

Answer: Atomicity means that all the actions are carried out at once, or none is carried out. This means that if a transaction is incomplete or fails in between everything will be rolled back to a previous stable state. We use aggregation to express relationships between various entities.

Question: What are the different types of relationships in the DBMS

Answer: There are three types of relationships in DBMS:

  • One-to-One: Each table record is related to only one record in another table. Example, employee_id, and unique_ssn_id
  • One-to-many or vice-versa (many-to-one): A record in one table can be related to more than one record in another table. Example, employee_id and phone_number
  • Many-to-Many: A record in the first table can be related to more than one records in the second table, and vice-versa: example, customer_id and product_id.

Question: Explain correlated subqueries in DBMS?

Answer: Correlated subqueries are subqueries that are executed once for each row processed by its main statement. For example,

select student_id, student_name, marks from students outer where salary > (select avg(marks) from students where subject = outer.students);

Question: Difference between two and three-tier architectures?

Answer: The two-tier architecture follows a client-server architecture. There is a straight communication link between client and server. Two-tier architecture gives high performance as there is no middle layer. The client can be any device like mobile, desktop, laptop etc. while all the data sits in a database.

Three-tier architecture consists of:

  • Presentation layer: A web browser, java applet, WAP phone etc.,
  • A business layer: Business logic like data validation, insertions, calculations, and
  • A data access layer: This is a data source like database, mainframe system or ERP system. The business layer acts as a middle tier that separates the business logic from the presentation and data layer for better reusability, data integrity, scalability and performance.

Question: Differentiate between Trigger and Stored Procedures

Answer: Stored procedures can be invoked explicitly by the user. It can take certain input parameters and can return output values. It is just like any other program. Triggers are called spontaneously (on its own) when a specific event occurs. Triggers can’t take any input values nor return any output values.

Question: What is the difference between Hash join, Merge join and Nested loops?

Answer: 

  • Hash-join: In this type of join, the database does full scanning of the main table, builds a RAM hash table, and then searches for matching tables in the other table. Hash join is faster than the nested loop join but consumes more RAM resources.
  • Merge join: This join uses simple concatenation followed by sorting (removing duplicates) and is the most effective technique out of all the joins.
  • Nested loop join: In this type of scan, the driving table (main) accesses rows through index range scan, and the result set of the driving table is nested with the probe of the other (secondary) table using the index scan method itself.

Question: Give the distinction between Proactive, Retroactive and Simultaneous Update?

Answer: 

  • Proactive updates are those that eventuate in the database before they become useful in the real-world.
  • Retroactive updates come about after they become effective in the real-world.
  • Simultaneous updates occur at the same time as they become effective in the real-world.

Question: Differences between the clustered and non-clustered index.

Answer: 

Clustered Index Non-clustered Index
Defines the order of physical data storage even if they are inserted in random order. Does not sort the physical data. Indexes are stored in a different place than the actual data
Only one clustered index per table as data can be sorted in a single way only More than one non-clustered index in a table is possible
In many databases, primary key constraint creates a clustered index on the same column. A non-clustered index can be created as: CREATE NON-CLUSTERED INDEX IX_tblStudent_deptt ON student(deptt ASC)
Example, if there is a table student that has a primary key student_id, the data will be stored in ascending order of student_id like 1,2,3,4. The clustered index will be automatically created on student_id. An index is stored in a separate table along with its address (a reference to the row), dept row address ECE <address>
No extra space required for these indexes. Consumes storage space.

Question: Difference between specialization and generalization?

Answer: 

Generalization Specialization
Creating groupings from various entity sets is called generalization Creating subgrouping within an entity set is called specialization
The process starts with several entity sets and creates a high-level entity with some common features Starts with a single entity set and then creates a different set using different features.
Applied to a group of entities Applied to a single entity
Follows bottom-up approach Follows top-down approach

Question: Explain the differences between network and hierarchical database model?

Answer: 

Hierarchical model Network model
Relationship between records is parent-child The relationship is in the form of pointers or links
During update and delete operations, inconsistencies in data may happen No data inconsistencies
Doesn’t support many to many relationships Supports many to many relationships
Creates a tree structure and data traversal is a bit complex Creates graph structure where data traversal is easy as a node can be accessed both ways, i.e. parent-child or vice-versa

Question: What is deadlock?

Answer: Deadlock happens when a set of processes are blocked. Each process is holding up a resource that the other process requires. The other process is waiting for a resource to be released by the same or another process. Deadlock can be depicted as:deadlock

Question: 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: What is a Network Model?

Answer: The 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: 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 the information which enables data search, data organization, and reporting. An RDBMS is a subset of a DBMS.

Question: 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: 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: What are DDL, DML and DCL statements in SQL?

Answer:

  • 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.

Click here to know more about SQL Command.

Question: 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: 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: 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: 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: 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: 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: 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 make strategic business decisions for organizations.

Question: 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: 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: 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: 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
  • Troubleshooting
  • Data migration

Question: 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: 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 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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.

Conclusion

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.

So this sums up the best interview questions, which will help you crack a DBMS Interview. If you wish to cover more DBMS interview questions, give this great course a shot. 200+ SQL interview Questions.

Also, you can purchase this great book for top Database programming Interview Questions: SQL in 10 Minutes, Sams Teach Yourself: Sams Teach Your SQL 10 Minu _4 4th Edition.

People are also reading:

Sindhuja Hari

Sindhuja Hari

Sindhuja Hari, a technology geek, content producer & writer brings over 20 years of experience. She builds and delivers best in class content for global audiences. Her favorite domains/ genres are IT, Manufacturing, Logistics & Supply Chain, and Travel & Leisure. View all posts by the Author

Leave a comment

Your email will not be published
Cancel
Irma Fisher
Irma Fisher

What is a tuple in DBMS?

Mercedes Fowler
Mercedes Fowler

In DBMS, a tuple is the one single row of any table.

Ernest Berry
Ernest Berry

What is join in DBMS?

Kerry Greene
Kerry Greene

It’s a binary operation. It makes combining join product and selection in a single statement a possibility. To combine the data from multiple join tables, the most preferred method is - Join.

Miriam Hill
Miriam Hill

What are the 3 types of databases?

Kenneth Washington
Kenneth Washington

Now that we know there are seven types of databases, it’s difficult to know which database is asked about. Still, the three types of databases are:
Hierarchical databases
Network databases, and
Graph databases

Randall Cobb
Randall Cobb

What is normalization in DBMS?

Mitchell Beck
Mitchell Beck

It’s the process of organizing the data in any database management system. It is important for eliminating unnecessary updates, insertion, and deletions.

Dana Turner
Dana Turner

What is the schema in DBMS?

Mitchell Beck
Mitchell Beck

Schema is a basic structural or skeletal view of the entire database. It represents the database’s logical views. It displays the data organization in a management system. Also, it decides about the constraints that will act upon the selected data.

Ruben Thompson
Ruben Thompson

What are the types of database management system?

Daryl Chambers
Daryl Chambers

Essentially, there are seven types of database management systems (DBMS), they are listed below:
Hierarchical Databases
ER (Entity Relational) model Databases
Network Databases
Graph Databases
Relational Databases
Object oriented Databases
Documents Databases

technvrial
technvrial

Good information about DBMS . thanks for sharing this knowledge ....

janaki. M
janaki. M

Dataspace management system