A Database is an organized and planned collection of our data. The data is arranged into rows, columns, and tables (relational DBMS) or represented using some data structure. Also, the data is indexed to make it simpler to discover particular data. To add, update, or delete data, database queries are utilized.
Databases are important for almost every industry as they store all the data such as the personal information of users and user credentials. DBMS stands for Database Management System. This refers to efficiently organizing your data and performing CRUD (Create, Read, Update and Delete) operations on it. Database management requires a good understanding of relational algebra to make your system efficient and secure.
In this article, we will be discussing a few of the most popular and important interview questions related to DBMS.
Top 50 DBMS Interview Questions and Answers
1. Why do we use DBMS?
Below are some of the most important reasons to use DBMS:
- Data security
- Data management
- Multiple User interfaces
- Data integration
2. What is RDBMS?
Relational implies a database system to store data in an Excel sheet format that employs rows and columns. This usually makes it easy to understand the structure of the data. An example of RDBMS is MySQL.
3. What are the different types of database languages?
There are 4 major types of database languages that are:
Data Definition Language (DLL): To define and update data.
Data Manipulation Language (DML): To manipulate already existing data.
Transaction Control Language (TCL): To manage transactions in the database.
Data Control Language (DCL): To manage user access.
4. What is a model in a database?
A database model identifies and specifies the logical structure of a database and how it may store, organize, and modify data. A database model is most frequently used as a relational model with a table-based structure. The data modelling process, therefore, involves both business data modellers and users of the company working together to decide the logical structure of the database.
5. What is the relationship in DBMS? Describe different kinds of relationships in databases?
The relationship in DBMS identifies how two or more entities are connected.
There are three kinds of relationships:
One-to-one: One entity of one class or collection can be related to at most one entity of another.
One-to-many: One entity of one class can be related to multiple entities of another class.
Many-to-many: More than one entity of one class can be related to more than one entity of another class.
6. What is data abstraction?
Data abstraction in DBMS is a technique for the disclosure of irrelevant information from users and only showing them what they need to know to interact with the database.
7. What do you mean by Relational Algebra?
Relational Algebra is commonly used in DBMS. It collects relations instances as inputs and provides relations occurrences as outputs. To carry out this task, it employs different operations.
Relational Algebra operations are carried out on a relationship recursively. The outcome of these procedures is a new relation.
8. What are the most common relational algebra operations?
Five common operations in relational algebra are Selection, Projection, Cartesian product, Union, and Set Difference.
9. What is normalization?
Normalization is a collection of principles to guarantee that when you transfer data, you send the least amount of data to ensure optimal use of the database and eliminate data redundancy and anomaly.
The main aim is to avoid the collection of irrelevant data stored together, as this is the main source of these anomalies.
Commonly used normalization techniques are:
- First Normal Form(1NF)
- Second Normal Form(2NF)
- Third Normal Form(3NF)
- Boyce & Codd Normal Form(BCNF)
10. What is denormalization?
The technique of introducing redundant data to avoid complicated data in the database is called denormalization. It is part of the database approach of optimization. This technique prevents the usage of complicated and expensive joins.
11. What is an ER model?
The ER model specifies a database’s conceptual perspective. It works with real-world entities and their relationships. At the database level, the creation of an ER model is a suitable choice. ER model stands for Entity-Relationship model where Entity is any real-world object that has some properties and attributes associated with it. In terms of RDBMS, a row is a single entity or object.
12. What are Joins in SQL?
SQL Join is used to collect data from two or more tables and is combined into a single data set. It is used to combine columns with values that are common to both tables from two or more tables. There are various types of Joins in SQL namely: inner join, outer join, left and right join, etc.
13. What are stored procedures in SQL?
Stored procedures are simply a series of SQL states which may be stored and called for the required outcomes in a database with a name. One or more SQL queries might be found in stored procedures depending on our needs.
Stored procedures are implemented for:
- Database performance optimization as the query time reduces.
14. What are checkpoints?
In most RDBMS, checkpoints are helpful to restore data. If there is an unexpected database crash, a checkpoint is being used for recovery. Checkpoints work at certain times and put all dirty pages into a log file relay.
15. What are the different types of keys available in DBMS?
Primary key: This uniquely identifies each entity in the DBMS.
Foreign key: This is a primary key of another table that is present in a table to create a relationship between two tables.
Candidate key: The candidate key is a set of candidate attributes that can uniquely identify a tuple. The Primary key can be selected from these attributes.
Super key: The super key is a set of attributes that can act like primary keys. The super key is a superset of the candidate key.
Composite key: A composite key refers to a combination of two or more columns that can uniquely identify each tuple in a table.
16. What are ACID properties in DBMS?
ACID stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity: A database follows a rule of all or nothing, i.e. all transaction activities are regarded as one unit or atomic. This means that a transaction is either finished or not executed at all when a database executes the transaction.
Consistency: This property ensures that only valid data is written into the database according to all rules and restrictions. If a transaction results in invalid data, the database returns to its preceding condition.
Isolation: This property ensures secure and independent processing of transactions simultaneously without interfering. However, the sequence of transactions is not ensured.
Durability: This property ensures that the results of a transaction or the database contents after a system crash or any other failure may not be lost.
17. What are transactions in DBMS?
A transaction is a collection of commands that alter the database. These declarations should be atomic, which implies that instructions are either fully executed or do not get executed at all. For example, if someone transfers money from one account to another, the money from one account must be debited and credited to the other account under any circumstances.
18. What are distributed database systems
A distributed database management system is a collection of multiple databases or servers that are physically scattered across many places and communicate over a computer network. This is a really useful method since if one server fails, our data is still available on the other servers. Homogenously distributed systems use the same hardware and operating system, whereas heterogeneous distributed systems use diverse hardware and operating systems.
19. What is the alternative term that is used to refer to a row?
We can refer to a row by the name ‘tuple’.
20. What do we refer to when we use the term “attribute” of the database?
Attribute refers to a column of the database. Columns are the properties of each entity in our database.
21. What is a transparent DBMS?
This database hides its design and structure from the users, as well as how it is stored in the storage.
22. What do you call the attribute that identifies each entity in the database with a unique value?
We call the attribute the primary key. The primary key can be an integer, string or even the attributes belonging to the users like email, username, etc.
23. What is entity type?
The entity type refers to a group of entities with comparable attributes. For example, each student in a table named STUDENT has the same attributes, such as name, roll number, phone number, and so on. Because an entity type is defined by a name and a set of attributes, the table named STUDENT can be regarded as an entity type.
24. What is an entity set?
The collection of entities of the same entity type, i.e. those with the same types of properties or attributes, is referred to as an entity set. Because all entities are included in the Entity Set, Entity Type is a superset of Entity Set. For example, a table named STUDENT has a similar collection of entities that are only connected to students, and each student is represented by several elements in the STUDENT entity set.
25. What is a superkey?
A superkey is a collection of one or more single or multiple keys that identify rows in a database. Additional properties may be present in a Super key that isn’t required for unique identification. For example, we may combine an employee’s name with his or her employee ID to create a superkey for this object.
26. What is an alternate key?
A column or set of columns in a table that has not been chosen as primary keys but is candidate keys is known as an alternate key. Although a table can have many primary keys, only one can be used as the primary key.
27. What are Value Sets?
A value set is associated with each attribute of an entity type. The domain is another name for this data collection. The domain of a field or an attribute refers to the set of all potential values for that attribute. Each particular entity’s value set describes the range of values that can be assigned to it.
28. Which query language is widely used for performing queries from the Relational Databases?
Structured Query Language (SQL) is used to query the Relational Databases.
29. Which operator takes the rows from two or more queries and only returns the rows that are common to both?
The Intersection Operator returns the rows which are common to multiple tables. Below is the Venn Diagram that shows how the intersection works:
30. What is a trigger in SQL?
In SQL, a trigger is an automatic action that is executed in response to the occurrence of an event. For example, if you work for a company and your income is deposited into your bank account, a trigger is set, and you are notified when your salary arrives.
31. What are indexes in databases?
The fundamental goal of a database is to get information from it via a query system. As a result, one of the database’s main goals is to reduce reaction time to the absolute minimum. Indexes are a mechanism for a database engine to improve search performance by locating a specific row using a key, which can be achieved using B/B++ trees, Bitmap, and other techniques. An index can be a primary key, but you can add any other that fits queries that you expect to be often used.
32. What is Shared Lock in DBMS?
When transactions are given read access to shared data, a shared lock exists between the two processes. A shared lock on data is supplied to one transaction, and a shared lock is given to the second transaction when it demands the same data. Because both processes are in read-only mode, neither will update any data, and they can both access it. No data can be updated until the shared lock is released.
33. What is an exclusive lock in DBMS?
When a transaction performs a read operation on a database, this lock is required. To avoid a race problem, only one transaction is allowed the authority to update the data.
34. What is 2-Tier Architecture?
In this architecture, the clients can directly communicate with the database for all their operations with no Graphical User Interface acting as an abstraction.
35. Write an SQL query to select the names of employees of a company whose salary is equal to 100$. (Names are denoted by “name” field, table with “employees” and salary with “salary”).
SQL Query: SELECT name FROM employees WHERE salary = 100
36. What is 1NF?
The First Normal Form is referred to as 1NF.
It’s the most basic form of normalization approach you may use in a database. We use 1NF to make sure that:
- Atomicity is maintained in every column.
- Duplicate columns in the same table should be removed.
- For each group of linked data, create a distinct table.
37. What is 2NF?
2NF stands for Second Normal Form. This must satisfy the following conditions:
- It’s 1NF.
- For sets of values that apply to multiple records, we create different tables.
- We use a foreign key to connect the tables.
38. What are the different levels of data abstraction?
The most fundamental level of data abstraction is this. It explains how the data is saved in the database. At this level, you get to know about the sophisticated data structure.
The middle level of the three-level data abstraction architecture is this. It specifies what information is kept in the database.
The topmost level of abstraction is the VIEW level. The user’s interaction with the database system is described at this level. It is usually preferred for novices and non-programmers.
39. What are the integrity rules of DBMS
- Entity Integrity: This defines that the table’s Primary Key cannot be a NULL value.
- Referential Integrity: Foreign Key can be NULL or should be the Primary Key value of another table or relation, according to this rule.
40. What are the disadvantages of the File Processing System (FPS)
- Slow Access Time
- Limited data sharing
- Lack of Atomicity
- Data redundancy
41. How is Database different from File Processing System?
Reading a file and using its contents to create something else is known as a file processing system. For example, you may create a chart depicting the data in a spreadsheet.
Databases are instruments for organizing and storing data. They are organized into tables having one or more columns, with each column representing a field and each row representing an object with a value for each field. Modern databases, on the other hand, may operate on entirely different principles.
42. What is Functional Dependency?
The interdependence of one attribute on another is determined by functional or database dependency. For instance, the symbol A -> B indicates that B is functionally dependent on A.
43. What is the difference between hash join and merge join?
A hash join is used to join huge tables together while a merge join joins two input streams from two tables into a single output stream.
44. Which level of data abstraction is preferred for the novices and non-programmers?
The VIEW level of abstraction is often preferred by users who simply require table schema details and not the database’s logical structure or other mathematical features.
45. What is a subquery?
A subquery is a query that is contained within an outer query. The outer query is known as the main query, while the inner query is referred to as the subquery. The result of the subquery is sent on to the main query, which is always processed first.
46. How are DELETE and TRUNCATE commands differently.
The DELETE command is used to delete a few or all the entities from a table based on a set of criteria. The DELETE operation can be undone before the commit operation.
TRUNCATE is a command that deletes all rows from a table. Once performed, the truncate action cannot be undone, and the record will be permanently erased.
47. What is the use of the DROP command.
A table is dropped from the database using the DROP command. Rows, indexes, and privileges for all tables will be eliminated as well. There will be no DML triggers fired. There is no way to undo the surgery.
48. What are constraints in databases?
A constraint is essentially a limit on the data that may be stored in a table to ensure integrity and security. Essentially, a set of rules that the data in the tables must follow. For example, the NOT NULL constraint requires a field to always have a value, which implies you can’t add a new row without adding anything to this field.
49. What is data integrity?
Data integrity is the preservation of data over its entire lifecycle, as well as the assurance of its accuracy and consistency. It is an important part of the design, implementation, and use of any system that stores, processes, or retrieves data. This entails setting various limitations on databases to ensure that no data is introduced that would compromise the data’s consistency.
50. What is Data Warehousing?
Data warehousing is a data warehouse that can hold a large amount of data. This massive volume of data is then used for analytics and reporting to study it more thoroughly or, to put it another way, to make sense of it. This aids in the integration of several data sources to alleviate stress on the production system.
DBMS is one of the most important technologies used by businesses to store a wide variety of data, including confidential user data. DBMS supports various techniques to create and manipulate data and also ensures the safety of the database. Failure in data management can introduce multiple issues in the data. Therefore, it is necessary to go through all the concepts of DBMS before creating a database for your use case. The DBMS interview questions discussed above cover several important aspects of DBMS that you should know.
People are also reading: