A Database is an organized and planned collection of our data so that it becomes easy for us to access, manipulate, and manage data. There are various types of databases available out there, depending upon the way they organize or store data.
For example, relational databases organize data in the form of tables. Databases are important for almost every industry as they store all the data, such as the personal information of users and user credentials.
Furthermore, there is an application that controls databases to which we refer DBMS. 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
Let us divide the entire list of DBMS interview questions into three categories: Basic, Intermediate, and Advanced.
Basic DBMS Interview Questions
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. The most popular database model is a relational model. This model stores data in the form of tables, which we also refer to them as tuples. The data modeling process, therefore, involves both business data modelers 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 is the connection or association of two or more tables that we create using joins. 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 in DBMS is a procedural query language that collects relations instances as inputs and provides relations occurrences as outputs. To carry out this task, it employs different operators, where an operator can either be unary or binary. The following are the most basic operations of relational algebra:
- Select
- Project
- Union
- Set different
- Cartesian product
- Rename
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?
Denormalization is a technique of introducing redundant data to avoid complicated data in a database. 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?
Joins in SQL are the clauses that we use to combine two or more tables, depending upon one related column between those tables. It corresponds to the join operation in relational algebra. There are various types of Joins in SQL that are as follows:
- Inner join
- Outer join
- Left join
- Right join
13. What are stored procedures in SQL?
Stored procedures are simply a series of SQL statements that may be stored and called for the required outcomes in a database with a name. They may contain one or more SQL queries as per our needs. Stored procedures are implemented for:
- Reusability
- 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?
The following 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. More interestingly, we can derive the primary key from the candidate key.
- 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. Let us discuss what each of these properties means.
- 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, it does not ensure the sequence of transactions.
- Durability : This property ensures that the results of a transaction or the database content should be intact even after a system crash or any other failure.
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 alternative term do we use to refer to a row?
The alternative term that we use to refer to a row is ‘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.
Intermediate DBMS Interview Questions
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 you may not require 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?
As we know that a candidate key o minimal super key is a collection of multiple columns that uniquely identifies tuples in a table. We choose one of the columns of the candidate key as the primary key of a table. And all other remaining columns in the candidate key serve as alternate keys. Although a table can have multiple keys, it has only one 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. What is the standard language for performing queries on Relational Databases?
Structured Query Language (SQL) is a standard query language for performing operations on 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 the “name” field, table with “employees” and salary with “salary”).
SQL Query: SELECT name FROM employees WHERE salary = 100
Advanced DBMS Interview Questions
36. What is 1NF?
1NF stands for the First Normal Form. It's the most basic form of normalization approach you may use in a database. We use 1NF to make sure that:
- All the values in a column are atomic.
- A table has single-valued attribute.
- 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?
Physical level
The most fundamental level of data abstraction is the physical level, which explains how the database organizes or stores the data. At this level, you get to know about the sophisticated data structure.
Logical level
The middle level of the three-level data abstraction architecture is this. It specifies what information the database holds.
View Level
The topmost level of abstraction is the VIEW level. This level describes the user's interaction with the database system. Moreover, this level is ideal 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
- Insecurity
- Limited data sharing
- Lack of Atomicity
- Data redundancy
41. How is Database different from File Processing System?
A file processing system refers to reading a file and using its contents to create something else. For example, you may create a chart depicting the data in a spreadsheet. Databases are instruments for organizing and storing data. They organize data 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?
A functional or database dependency determines the interdependence of one attribute on another. 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?
We use a hash join to join huge tables together while the merge join to join two input streams from two tables into a single output stream.
44. Which level of data abstraction is ideal for novices and non-programmers?
The users who simply require table schema details and not the database's logical structure or other mathematical features, the ideal level of data abstraction for them is the VIEW level.
45. What is a subquery?
A subquery is a query inside another query. Also, we can refer to the subquery as the nested query. We refer to the outer query as the main query, while the inner query is the subquery. The main query utilizes the result of the subquery.
46. How are DELETE and TRUNCATE commands differently.
When we require to delete a few or all the entities from a table based on a set of criteria, we use the DELETE command. Before committing the delete operation, we can undo it. TRUNCATE is a command that deletes all rows from a table. Once we perform the truncate command, we cannot undo it, and erases all the records from a table permanently.
47. What is the use of the DROP command.
We use the DROP command to delete or drop tables, views, and indexes from the database. It entirely removes any database object and its associated rows, indexes, constraints, statistics, and privileges.
48. What are constraints in databases?
A constraint is essentially a limit on the type of data that may go into 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 the process of collecting and storing a large amount of data from heterogeneous sources. Organizations and companies later use this massive volume of data 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.
Conclusion
DBMS is one of the most important technologies that organizations use 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:
Leave a Comment on this Post