Being Code Expert: DBMS Interview Questions Set -1

Q1. What is DBMS used for?

DBMS, commonly known as Database Management System, is an application system whose main purpose revolves around the data. This is a system that allows its user to store the data, define it, retrieve it and update the information about the data inside the database.

Q2. What is a Database?

A Database is an organized, consistent, and logical collection of data that can easily be updated, accessed, and managed. Database mostly contains sets of tables or objects which consist of records and fields. A tuple or a row represents a single entry in a table.

Q3. Why is the use of DBMS recommended? Explain by listing some of its major advantages.

Controlled Redundancy: DBMS supports a mechanism to control the redundancy of data inside the database by integrating all the data into a single database and as data is stored in only one place, the duplicity of data does not happen.

Data Sharing: Sharing of data among multiple users simultaneously can also be done in DBMS as the same database will be shared among all the users and by different application programs.

Backup and Recovery Facility: DBMS minimizes the pain of creating the backup of data again and again by providing a feature of ‘backup and recovery which automatically creates the data backup and restores the data whenever required.

Enforcement of Integrity Constraints: Integrity Constraints are very important to be enforced on the data so that the refined data after putting some constraints are stored in the database and this is followed by DBMS.

Independence of data: It simply means that you can change the structure of the data without affecting the structure of any of the application programs.

Q4. What is the purpose of normalization in DBMS?

Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys in order to fulfill certain properties.

1) To minimize the redundancy of the data.

2) To minimize the Insert, Delete, and Update Anomalies.

Q5. What are the different types of languages that are available in the DBMS?

DDL: DDL is a Data Definition Language that is used to define the database and schema structure by using some set of SQL Queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.

DCL: DCL is a Data Control Language that is used to control the access of the users inside the database by using some set of SQL Queries like GRANT and REVOKE.

DML: DML is a Data Manipulation Language that is used to do some manipulations in the database like Insertion, Deletion, etc. by using some set of SQL Queries like SELECT, INSERT, DELETE and UPDATE.

TCL: TCL Transaction Control Language consists of commands which deal with the transaction of the database.

Q6. What is meant by ACID properties in DBMS?

ACID stands for Atomicity, Consistency, Isolation, and Durability in a DBMS these are those properties that ensure a safe and secure way of sharing data among multiple users.

Atomicity: This property reflects the concept of either executing the whole query or executing nothing at all, which implies that if an update occurs in a database then that update should either be reflected in the whole database or should not be reflected at all.

Consistency: This property ensures that the data remains consistent before and after a transaction in a database.

Isolation: This property ensures that each transaction is occurring independently of the others. This implies that the state of an ongoing transaction doesn’t affect the state of another ongoing transaction.

Durability: This property ensures that the data is not lost in cases of a system failure or restart and is present in the same state as it was before the system failure or restart.

Q7. Are NULL values in a database the same as that of blank space or zero?

No, a NULL value is very different from that of zero and blank space as it represents a value that is assigned, unknown, unavailable, or not applicable as compared to blank space which represents a character and zero represents a number.

Q8. What is Data Warehousing?

The process of collecting, extracting, transforming, and loading data from multiple sources and storing them in one database is known as data warehousing. A data warehouse can be considered as a central repository where data flows from transactional systems and other relational databases and is used for data analytics. A data warehouse comprises a wide variety of an organization’s historical data that supports the decision-making process in an organization.

Q9. Explain different levels of data abstraction in a DBMS.

Physical Level: it is the lowest level and is managed by DBMS. This level consists of data storage descriptions and the details of this level are typically hidden from system admins, developers, and users.

Conceptual or Logical level: it is the level at which developers and system admins work and it determines what data is stored in the database and what is the relationship between the data points.

External or View level: it is the level that describes only part of the database and hides the details of the table schema and its physical storage from the users. The result of a query is an example of View level data abstraction. A view is a virtual table created by selecting fields from one or more tables present in the database.

Q10. Explain Entity, Entity Type, and Entity Set in DBMS.

The Entity is an object, place, or thing which has its independent existence in the real world and about which data can be stored in a database. Entity: An entity is a real-world object having attributes, which are nothing but characteristics of that particular object. For Example, any person, book, etc.

Entity Type is a collection of entities that have the same attributes. For Example, the STUDENT table contains rows in which each row is an entity holding the attributes like name, age, and id of the students, hence STUDENT is an Entity Type that holds the entities having the same attributes.

Entity Set is a collection of entities of the same type. For Example, A collection of the employees of a firm.

Q11. What is a functional dependency in the DBMS?

This is basically a constraint that is useful in describing the relationship among the different attributes in a relation.

Q12. What is 1NF in the DBMS?

1NF is known as the First Normal Form. This is the easiest form of the normalization process which states that the domain of an attribute should have only atomic values. The objective of this is to remove the duplicate columns that are present in the table.

Q13. What is 2NF in the DBMS?

2NF is the Second Normal Form. Any table is said to have in the 2NF if it satisfies the following 2 conditions:

1). A table is in the 1NF.

2) Each non-prime attribute of a table is said to be functionally dependent in totality on the primary key.

Q14. What is 3NF in the DBMS?

3NF is the Third Normal Form.

Any table is said to have in the 3NF if it satisfies the following 2 conditions:

1). A table is in the 2NF.

2) Each non-prime attribute of a table is said to be non-transitively dependent on every key of the table.

Q15. What is BCNF in the DBMS?

BCNF is the Boyce Codd Normal Form which is stricter than the 3NF.

Any table is said to have in the BCNF if it satisfies the following 2 conditions:

1). A table is in the 3NF.

2). For each of the functional dependencies X->Y that exists, X is the super key of a table.

Q16. What is RDBMS?

RDBMS is the Relational Database Management System which contains data in the form of tables and data is accessed on the basis of the common fields among the tables.

Q17. What are the different types of relationships in the DBMS?

One-to-One: This basically states that there should be a one-to-one relationship between the tables i.e. there should be one record in both the tables.

One-to-Many: This states that there can be many relationships for one i.e. a primary key table hold only one record which can have many, one, or no records in the related table.

Many-to-Many: This states that both the tables can be related to many other tables.

Self-Referencing Relationship: This type of relationship is applied when a particular row in table X is associated with the same table.

Q18. What is concurrency control?

This is a process of managing simultaneous operations in a database so that database integrity is not compromised.

1). Optimistic approach – Involves versioning

2) Pessimistic approach – Involves locking

Q19. What is Correlated Subquery in DBMS?

Answer: A Subquery is also known as a nested query i.e. a query written inside some query. When a Subquery is executed for each of the rows of the outer query then it is termed as a Correlated Subquery.

Q20. What integrity rules exist in the DBMS?

Entity Integrity: This states a very important rule that the value of a Primary key can never have a NULL value.

Referential Integrity: This rule is related to the Foreign key which states that either the value of a Foreign key is a NULL value or it should be the primary key of any other relation.

Exception Handling Interview Questions

DBMS Interview Questions Set -1

DBMS Interview Questions Set -2

SQL Interview Question Set -1

SQL Interview Question Set -2

JPA Interview Questions Set -1

JPA Interview Question Set -2

Hibernate Interview Questions

Spring Boot Interview Questions Set 1

Spring Boot Interview Questions Set 2

GIT Interview Questions

Redis Interview Questions