Interviews are the most important step that will help you land your dream job. Preparing for your interview will help you appear calm, cool and collected. Here are a few DBMS Interview Questions that will help you prepare for your interview.

DBMS Interview Questions

Q1. Explain the terms database and DBMS. Also, mention the different types of DBMS.

Ans 1. A database could be defined as a prearranged collection of figures known as data.

DBMS short for Database Management Systems refers to the applications that are designed which enable user interaction with other applications. In simpler words, it could also be defined as a software application which interacts with databases, applications, and users to capture and analyze the required data. The data stored in the database could be retrieved, deleted and modified based on the client’s needs.

The different types of DBMS are:

• Relational DBMS (RDBMS): RDBMS uses a structure that allows the users to, access data with another piece of data in a database and data is stored in the form of tables in RDBMS
• Hierarchical DBMS: Its structure resembles that of a tree, where the nodes represent records and the branches of the tree represent fields.
• Network DBMS: Network DBMS supports many-to-many relations where multiple member records can be linked.
• Object-oriented DBMS: An object is a small individual software that is used to store pieces of data and the instructions for the actions to be done with the data

Q2. What are the advantages of DBMS?

Ans 2. The advantages of DBMS are:

• Data Independence: This feature helps enable to change the structure of the data without affecting the structure of any of the running application programs.
• Sharing of Data: Multiple users can use data from the same database simultaneously which proves to be useful for larger organizations.
• Integrity constraints: This allows the data to be stored in a database in a refined manner.
• Redundancy control: DBMS has a mechanism that helps control the redundancy of data by integrating all the data into a single database.
• Provide backup and recovery facility: DBMS has this feature of ‘backup and recovery’ that automatically creates the data backup and restore the data as and when required.

Q3. What are the various kinds of interactions catered by DBMS?

Ans 3. DBMS caters various kinds of interactions, they are:

• Data definition
• Update
• Retrieval
• Administration

Q4. What is SQL?

Ans 4. SQL stands for Structured Query Language is an ANSI standard language updates database and commands for accessing.

Q5. What do you understand by query optimization?

Ans 5. Query optimization could be defined as the phase which identifies a plan for evaluation query that has the least estimated cost and it comes into the picture when there are a lot of algorithms and methods to execute the same task.

The advantages of query optimization are:

• Faster output
• A larger number of queries can be executed in comparatively less time
• Reduction in time and space complexity

Q6. What are the different levels of abstraction in the DBMS?

Ans 6. There are 3 levels of data abstraction, namely

• Physical Level: This is the lowest level of the data abstraction and this shows how the data is stored in the database.
• Logical Level: This is the next level of the data abstraction and this shows the type of the data and the relationship among the data that is stored in the database.
• View Level: The view level is the highest in the data abstraction and this shows or states only a part of the database.

Q7. Define Normalization.

Ans 7. Normalization is the process of analyzing relational schemas which are based on their respective functional dependencies and the primary keys so that they fulfil certain properties.

Properties:

• To minimize data redundancy.
• To minimize the anomalies of Insert, Delete and Update.

Q8. Define Denormalization.

Ans 8. Denormalization could be defined by boosting up database performance, adding redundant data which in turn helps rid of complex data.

Q9. What do you understand by the terms Entity, Entity Type, and Entity Set in DBMS?

Ans 9.

• Entity: An entity could be defined as a real-world object that has attributes, which are nothing but characteristics of that particular object. Consider, an employee can be an entity and this can have attributes such as empid, empname, etc.
• Entity Type: Entity type is nothing but a collection of entities, having the same attributes. Generally, an entity type refers to one or more related tables in a particular database. Consider this, An employee can have attributes such as empid, empname, department, etc.
• Entity Set: An entity set is the collection of all the entities of a particular entity type in a database. For example, a set of employees, a set of companies, and a set of people can come under an entity set.

 Q10. What are the different types of normalization?

Ans 10. There are many successive levels of normalization and they are known as normal forms. Each consecutive normal form is dependent on the previous one. Given below are the first three normal forms.

• First Normal Form: In 1NF there are no repeating groups within rows
• Second Normal Form: In 2NF every non-key (supporting) column value is dependent on the whole primary key.
• Third Normal Form: In the 3NF, dependencies solely on the primary key and no other non-key (supporting) column value.

Q11. Explain the concepts of a Primary key and Foreign Key.

Ans 11. Primary Key uniquely identifies the records in a database table while Foreign Key, on the other hand, is used to link two or more tables together.

Example: Consider 2 tables – Employee and Department. Both have one common field/column as ‘ID’ where ID is the primary key of the Employee table while this happens to be the foreign key for the Department table.

 

Q12. Explain the concept of ACID properties in DBMS?

Ans 12. ACID properties are a combination of Atomicity, Consistency, Isolation, and Durability properties. These properties prove to be very helpful in allowing a safe and secure way of sharing the data amongst multiple users.

• Atomicity: When changes are being done to the data it feels as though a single operation is performed. In other words, either all the changes are performed, or none of them is performed.
• Consistency: Data must be in a consistent state at the beginning of the transaction as well as the end of the transaction.
• Isolation: As the name itself suggests, this ensures that each transaction that occurs is in isolation with others. Simply put a transaction which has started but not yet completed should be in isolation with others, this is done so that the other transaction does not get impacted with this transaction.
• Durability: In the event of system failure, after the transaction is completed, changes to the data persist and are not undone. Hence, due to this property data is always in a durable state.

 

Q13. What are the different types of joins in SQL?

 

Ans 13. There are 4 types of Joins in SQL. They are

• Inner Join: The inner join is used to fetch the data among the tables which are common in both the tables.
• Left Join: The left join returns all the rows from the table which is on the left side of the join but only the matching rows from the table which is on the right side of the join.
• Right Join: The right join returns all the rows from the table which is on the right side of the join but only the matching rows from the table which is on the left side of the join.
• Full Join: The full join returns the rows from all the tables on which the join condition has put and the rows which do not match hold null values.