50 Best DBMS Interview Questions For Database Developers
This list of top 50 DBMS interview questions for beginners and experts will prepare you for your DBMS interview.
Every IT system relies on a database. If you want to land a database position, you will certainly need to showcase your expertise and knowledge to succeed as a database developer. Therefore, this list of top 50 DBMS interview questions will prepare you for the interview.
Learn SQL to consolidate data from multiple data sources and convert complex data sets to actionable intelligence with the best SQL tutorials for beginners.
DBMS Interview Question & Answers For Beginners and Experts
1. What is a Database?
Answer: Essentially, a database is a collection of ordered data, organized and stored electronically. Organizing data in this way makes it easier to access, aggregate, and manage it. It is managed and monitored via a Database Management System (DBMS).
2. What is DBMS? What is it used for?
Answer: DBMS are software applications that help you build and maintain logically related data, commonly called databases. A DBMS offers the following advantages:
- Controlled Data Redundancy
- Constrains integrity constraints
- Unauthorized access is prohibited
- Data Availability and Sharing
- Data Protection and Storage
3. Explain what you know about Functional Dependency?
Answer: A functional dependency describes the relationship between non-key attributes and the primary key of a table. There are two types of functional dependencies - trivial and non-trivial. We can define this relationship as A -> B, where A is the determinant, and B is the dependent column.
4. Do you think there are any drawbacks to file processing systems?
Answer: Several drawbacks exist with file processing systems:
- Concurrent access issues
- Data redundancy and inconsistency
- It is difficult and inefficient to access data
- Data may be stored in different formats, making it difficult to share
5. Explain the difference between extension and intension?
Answer: Intension and extension, logically, denote the reference of a concept or term: "intension" signifies the internal content of a term or concept that constitutes its formal definition; "extension" specifies its scope of application by naming the particular objects that it denotes. For example, the intension of “ship” as a substantive is “vehicle for transportation on water,” whereas its extension embraces such things as cargo ships, passenger ships, battleships, and sailing ships. Contrary to popular belief, the distinction between intention and extension does not apply to connotation and denotation.
6. What is Denormalization?
Answer: Denormalization is a database optimization technique where redundant data is added to multiple tables to avoid joins. It speeds up data retrieval, makes it more efficient, and simplifies queries.
7. What is a checkpoint in DBMS?
Answer: A checkpoint is a mechanism that removes all previous logs from the system and stores them permanently on the storage disk.
Maintaining shadow pages and maintaining the log of each transaction are two ways that can help the DBMS recover and maintain ACID properties. Hence, when it comes to a log-based recovery system, checkpoints exist. Checkpoints are those points to which the database engine can recover after a crash as a specified minimal point from where the transaction log record would allow you to recover all the committed data up to the point of the crash.
8. How to use checkpoints in a database ?
Answer: Follow these simple steps to use checkpoints in a database:
- Write begin_checkpoint record into the log.
- Collect checkpoint data in the stable storage.
- Write end_checkpoint record into the log.
9. What do you mean by transparent DBMS?
Answer: As the name implies, transparent DBMS do not reveal their physical structure to users. The physical structure or physical storage structure indicates the memory manager of the DBMS, which describes how the data is stored on a disk.
10. Explain ACID properties?
Answer: ACID (Atomicity, Consistency, Isolation, Durability) is a set of guidelines for ensuring database transactions even in case of errors, power outages, etc. These rules and properties are:
Atomicity: Each statement of a transaction must succeed exactly or fail exactly at all times, including power outages, errors, and crashes. Example - In a money transfer transaction, debiting and crediting must occur simultaneously or not at all.
Consistency: After any transaction, the database must remain consistent. Once a transaction has finished, it is advisable not to make any additional changes to the database.
Isolation: Isolation ensures that concurrently executed transactions leave the database in the same state as if they had been sequentially executed.
Durability: Durability guarantees that once your transaction has been committed, it will remain committed even if you have a system crash, which means you can store your completed transactions (or their consequences) in nonvolatile memory.
11. What is a Relational Database (RDBMS)?
Answer: It is a type of database that stores and provides access to data points related to one another. Relational databases are based on the relational model, which allows us to represent data via tables intuitively and straightforwardly. Each row in a relational database is a record with its unique ID, referred to as the key. It is also easy to establish relationships among data points since the columns in the table contain attributes of the data. Each record contains a value for each attribute.
12. Explain the different database languages types.
Answer: There are four different types of database languages:
DDL (Data Definition Language): It is used to define the database structure or skeleton, such as creating the schema, tables, indexes, constraints, etc. Some DDL statements are created, altered, dropped, renamed, truncate, comment.
DCL (Data Control Language): Using it, you can retrieve saved data. DCL is responsible for permissions and access control, which are managed via grant and revoke commands.
DML (Data Manipulation Language): It is used to access and manipulate data. DML statements include select, insert, delete, update, lock table, explain plan, etc. DML statements manage user requests.
TCL (Transaction Control Language): It is used to save or roll back changes made by DML. Commit and rollback are the commands used for TCL.
13. What is System R?
Answer: System R is a database management system that offers a high level of data independence and abstraction of the database from the users. In addition to data control features, it provides data integrity assertions, authorization, and triggered transactions.
14. Explain 2-Tier architecture?
Answer: The 2-Tier architecture refers to a DBMS architecture where the UI or view layer (also known as the presentation) runs on a client machine (desktop, laptop, tablet, phone, etc.) while the data is stored on the server. Moreover, clients access databases through the ODBC connectivity APIs. Since the client cannot directly access the database, this architecture enhances the security of the database.
15. What is meant by DBMS data abstraction?
Answer: Data abstraction involves hiding irrelevant information from users to facilitate smooth interactions with them. Three levels of abstraction exist:
Physical Level: The lowest level of abstraction describes how data is physically stored in memory. It is possible to access data sequentially or randomly. The files are organized using B+ trees and hashing methods.
Logical Level: Information is stored in this level in the form of tables. In addition, relationships between various entities are stored as simple structures.
View Level: It is the highest level of abstraction. Users can view only a portion of the database in the form of rows and columns. Multiple views of the same database are possible. Storage and implementation details remain hidden from users.
16. Explain the purpose of normalization in DBMS?
Answer: The process of removing redundancy from a set of relations is called normalization. It can reduce irregularities in the insert, delete, and update by dividing huge tables into smaller ones and using keys to link them.
17. Explain the normal forms BCNF, 1NF, 2NF, and 3NF.
Answer: The above are all normal forms of data, i.e. formulating data in a way that minimizes data redundancy and insert, delete, and update anomalies.
- 1NF: First normal form. This rule states that a column of a table can hold only an atomic (single) value.
- 2NF: 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: 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: 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.
18. Differentiate between aggregation and atomicity?
Answer: Atomicity means that all actions are performed at the same time, or none are performed at all. If a transaction fails or is incomplete, it will be rolled back to a previous stable state. Nonetheless, with aggregation, we can express relationships between entities.
19. How is the Entity, Entity Type, and Entity Set different from each other in DBMS?
Answer:
- Entity: It is an object with physical existence that is independent and easily distinguished from other objects. As an example, an employee, student, course, job, etc.
- Entity type: refers to the collection of similar entities.
- Entity set: It is a combination of entities of the same type at any given time.
20. Explain the different types of database keys?
Answer: DBMS keys come in several types:
Alternate Key: A column or columns of a table that uniquely identifies each table.
Candidate Key: A set of attributes that uniquely identifies tuples of a table. Essentially, it is a super key without repeated attributes.
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}
Foreign Key: A common column that describes the relationship between two tables. Additionally, it maintains data integrity.
Primary Key: A column or set of columns that uniquely identifies a row in a table.
Super Key: A key or set of keys that identify rows within a table.
Surrogate Key: Created when there is no naturally available primary key for a table. provides a unique identity to a row in the table.
21. What are the different types of relationships in the DBMS
Answer: DBMS have three types of relationships:
- One-to-One: Each record in a table is related to one record in another table. Example, employee_id, and unique_ssn_id
- One-to-many or vice-versa (many-to-one): Records in one table can be related to multiple records in another table. For Example, employee_id, and phone_number
- Many-to-Many: A record in the first table can be related to more than one record in the second table, and vice-versa: example, customer_id, and product_id.
22. Differentiate between Trigger and Stored Procedures
Answer: The user can explicitly invoke stored procedures. It can accept input parameters and return output values. Using it is just like using any other program. When a specific event occurs, triggers are called spontaneously (on their own). Triggers cannot take any input values or return any output values.
23. Difference between two and three-tier architectures?
Answer: The two-tier architecture follows a client-server architecture. Therefore, clients and servers communicate directly. In a two-tier architecture, there is no middle layer, so performance is high. The client can be any device such as a mobile phone, desktop, laptop, etc., while the data sits in a database.
The three-tier architecture includes:
- 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 a database, mainframe system, or ERP system. As a middle tier, the business layer separates the business logic from the presentation and data layers for better reusability, data integrity, scalability, and performance.
24. What are the primitive operations common to all database management systems?
Answer: DBMS's most important primitive operations are addition, deletion, and modification.
25. What is deadlock?
Answer: A deadlock occurs when a set of processes are blocked. Every process holds up a resource that the other process needs. The other process waits for a resource to be released by the same or another process.
26. What is a Network Model?
Answer: An improved version of a hierarchical database model is the network database model. Also here, data is organized in a tree-like structure. However, a child node can be connected to more than one parent node. As a result, there is a many-to-many relationship between data nodes. Examples of network databases are IDMS (Integrated Database Management System), Integrated Data Store (IDS).
27. What is an Object-Oriented Database Model?
Answer: Data is represented by objects in an object-oriented database model. For example, a multimedia file or record in a relational database is stored as a data object rather than as an alphanumeric value.
28. What is a Hierarchical database Model?
Answer: In a hierarchical database model, data is arranged into nodes in a tree-like structure. A node can't have more than one parent node. Therefore, data in this model has a one-to-many relationship. An example of such a model is the Document Object Model (DOM), often used in web browsers.
29. What is SQL?
Answer: SQL (Structured Query Language) is a programming language used to communicate with data stored in databases. The SQL language is relatively easy to read, write, and interpret.
30. 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. Enhancing the collection of indexes involves a process called index hunting. It is accomplished by using methods such as query optimization and query distribution.
31. What is 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.
32. What is 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. Since the method for accessing data is predetermined, it is more swift and efficient. Compilation takes place at compile time for SQL statements.
33. What is dynamic SQL?
Answer: Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can build your query at runtime. Also, it is slower than static SQL as the query is compiled at runtime.
34. Explain the importance of database partitioning?
Answer: Partitioning is a powerful feature that increases performance while lowering costs. Using it makes data more manageable and more accessible.
35. What is a distributed database?
Answer: A distributed database is a collection of multiple databases that are physically spread across several locations. It is possible to have databases on several networks at the same time. DDBMSs (Distributed Database Management Systems) integrate data logically, appearing to the user as one database.
36. Give the distinction between Proactive, Retroactive, and Simultaneous Update?
Answer: Here's the difference between all three updates:
- Proactive update occurs in the database before it finds practical use in the real world.
- Retroactive updates occur after they become effective in the real world.
- Simultaneous update occurs when the changes in the real world occur simultaneously.
37. Define Data Warehousing.
Answer: Data Warehousing is a technique that collects a large amount of data from one or more sources. Data analysis helps organizations make strategic business decisions based on the collected data.
38. What is MongoDB?
Answer: MongoDB is a non-relational unstructured open-source database. The document-oriented database stores your data in collections made up of individual documents. A document in MongoDB is just a big JSON object with no particular format or schema. MongoDB represents JSON documents in a binary-encoded format called BSON.
39. What is an ER diagram in DBMS?
Answer: Entity-relationship models or entity-relationship diagrams are visual representations of data in which entities, attributes, and relationships between entities are represented.
40. 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.
41. What is the role of a DBA in DBMS?
Answer: The Database Administrator (DBA) plays a crucial role in an organization. The following are among them:
- Performance Monitoring
- Security planning and implementation
- Data backup and recovery
- Troubleshooting
- Data migration
- Installing and configuring databases
42. What is an Entity in an ER diagram?
Answer: An entity can be a real-world object that is easily identifiable. For example, books, publishers, and members can be considered entities in a library database. The attributes or properties that define these entities are what give them their identity. Entities in an ER model are related to one another.
43. What is Data Mining?
Answer: The process of data mining involves sorting through large amounts of data to identify patterns and trends. For predicting likely outcomes, the segmentation of data is carried out using complex mathematical and statistical algorithms. For Data Mining, there are many tools such as RapidMiner, Orange, Weka, etc.
44. What is a Catalog?
Answer: A catalog is a table containing information about the structure of each file, the type, and storage format of each data item, and various constraints on the data. Metadata refers to the information stored in the catalog.
45. Describe concurrency control?
Answer: Managing simultaneous operations on a database such that database integrity is not compromised is called Concurrency control. Concurrency control takes place in two ways.
Locking (the use of locks to control access to data items) and Versioning (the use of multi-version concurrency control) respectively.
46. What is SQL SERVER?
Answer: SQL Server is an RDBMS developed by Microsoft. Due to its stability and robustness, it is a popular choice. The latest version of SQL Server is SQL Server 2019.
47. What is the difference between Hash join, Merge join, and Nested loops?
Answer:
Hash join: This type of join involves the database scans the main table, building a RAM hash table, and searching for matching tables in the other table. The hash join is faster than the nested loop join, but it consumes more RAM.
Merge join: This join uses simple concatenation followed by sorting (to remove duplicates) and is the most efficient one out of all.
Nested loop join: The driving table (main) accesses rows using an index range scan, and the result set is nested with the probe of the other table (secondary) using the index scan method itself.
48. Under what conditions should we use indexes?
Answer: By creating indexes, you can enforce uniqueness, facilitate sorting, and enable fast retrieval based on column values. When a column is often used, it is a great candidate for an index to be used with suitable conditions in WHERE clauses.
49. What is cardinality in context to a database?
Answer: Cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table in SQL (Structured Query Language). The lower the cardinality, the more duplicate values there are in a column.
50. What is a hashing technique in a database?
Answer: Hashing is the process of transforming a string of characters into a fixed-length value or key that represents the original string. It is a method of indexing and retrieving items in a database because it is faster to find the item using the shorter hashed key than using the original value.
If you have made it this far, then certainly you are willing to learn more about databases. Here are some more resources related to the database that we think will be useful to you.