DBMS Interview Questions

                      Top DBMS Interview Questions & Answers 

Q-1 What is DBMS? Where is DBMS used?
Ans: DBMS are software applications used for managing databases - you can use it to create a database, insert data, transform data, update data or even process it as per requirements.
DBMS facilitates data retrieval and data storage and is a better system to secure data as compared to the file-based system.
DBMS is used for:
• Controlling data redundancy
• Better protection of data
• Efficient access, manipulation of data

Q-2 What are different database languages present in DBMS?
Ans: There are 4 types of database languages:
• DDL (Data Definition Language): This language is used for creating database objects such as tables, indices, constraints which together form a basic skeleton of the database. CREATE, ALTER and DROP are some of the commands of DDL.
• DCL (Data Control Language): This language is used for controlling user access on the database. GRANT and REVOKE commands are used for granting and removing user's access to the database system.
• DML (Data Manipulation Language): Used for adding, inserting or modifying data in a database. Some examples of DML commands are INSERT, DELETE and UPDATE.
• TCL (Transaction Control Language): Used to manage transactions in a database. TCL commands are used to manage changes made in data by DML. Some examples of TCL commands are ROLLBACK, COMMIT and SAVEPOINT.

Q-3 What are the advantages of DBMS?
Ans: Advantages of DBMS are:
• Controls data redundancy
• Enforces integrity constraints
• Facilitates data extraction and processing
• Protects data from unauthorized access
• Automatically takes care of back-up and recovery of data

Q-4 What is the RDBMS?
Ans: RDBMS is a software which works upon the relational database. In a relational database, data is stored in form of tables which consists of rows and columns. The benefit of the relational database is that the data across different tables are related to each other so queries can be efficiently run over multiple tables in one go.
What RDBMS does is that it executes those queries on the database, for example, deleting, inserting or updating data stored in the table.

Q-5 What is a checkpoint in DBMS? When does it occur?
Ans: Checkpoint is a mechanism through which all the previous logs are removed from the system and are stored in a permanent storage device.
They are actually those points up to which database engine can recover after a crash by using transaction log record to recover all the committed data up to the point of crash.
Checkpoints continuously monitor DBMS and via log-based recovery system, it helps in recovering database after a system crash.

Q-6 What is Data Abstraction in DBMS? What are the three levels of abstraction in the DBMS?
Ans: Data abstraction is the approach used by developers to hide irrelevant details related to data in database so that the user interaction with the database is more efficient and the user gets the necessary information without getting involved in complex details.
Three levels of data abstraction are:
1) View level: This is the highest form of data abstraction which describes how users interact with the database.
2) Logical level: This is the intermediate level which describes what kind of data is stored in each database.
3) Physical level: Lowest level in data abstraction architecture which describes how data is stored in the database.

Q-7 What is meant by query optimization?
Ans: Query optimization is an evaluation technique to find out the most efficient query for a task. The concept of query optimization is used when there are various algorithms and methods present for the same task and we want to obtain the one with the least cost.
Query optimization reduces time and space complexity which leads to executing a greater number of queries in less amount of time and provides the user with a faster result.

Q-8 What are different types of keys?
Ans: Different types of keys in DBMS are:
• Primary key: Set of attributes used for identifying each record in the table. A table can have only one unique primary key and each row in that table is designated by a primary key value due to which it can get identified by primary key.
• Super key: Set of single keys or multiple keys which is used for identifying rows in a table.
• Candidate key: It is an attribute or set of attributes used for uniquely identifying tuples in a table.
• Foreign key: They are set of attributes in a table which keeps references to primary keys of other tables. In this way, it establishes the link between the two tables

Q-9 What are the relationships?
Ans: When a foreign key of one relational database table refers to a primary key of another table then we say that they are in a relationship.

Q-10 What are different types of relationships?
Ans: Different types of relationships in a database are:
• One-to-One Relationship: When one record of a table is associated with only one record of another table.
• One-to-Many Relationship: When a record of table A is associated with one or more than one record of table B but from table B one record is associated with only one record of A then there is one-to-many relationship between A and B.
• Many-to-Many Relationship: When one record of table A is associated with many records of table B and one of record of table B is also associated with many records of table A then there exists many-to-many relationship between table A and table B.
• Self-referencing relationship: When a record of table A is associated with records of its own table.

Q-11 What is Join?
Ans: Join operation is used to combine rows or columns from two different tables based on specified join. The combination will only take place if and only if the join condition is satisfied.

Q-12 What are different types of Join in SQL?
Ans: There are 4 types of join:
• Inner join: It returns a new result table after combining matching rows from two or more tables.
• Left join: It returns all rows from the left table and matching rows from the right table.
• Right join: It returns all rows from the right table and matching rows from the left table.
• Full join: It is a combination of both left and right join where all the matched rows of two tables are returned first followed by non-matching rows of two or more tables.

Q-13 What is stored procedure?
Ans: Stored procedure is a group of SQL statements which are developed to perform certain tasks by taking some inputs as parameters. Their features are that they can be invoked whenever they are required, by explicitly calling it.

Q-14 What are the integrity rules in DBMS?
Ans: There are two integrity rules:
• Entity Integrity rule: According to this rule, there can't be any primary key with the null value.
• Referential Integrity rule: By this rule, if a foreign key of a table is related to a primary key of another table then either foreign key of the first table is null or value of foreign key matches with value of primary key available in the second table.

Q-15 What is embedded and dynamic SQL?
Ans: Embedded SQL or static SQL is the language where a set of statements are hardcoded in the application and do not change at runtime while in dynamic SQL you can run construct statements at runtime.
One benefit of embedded SQL is that you can optimize your code beforehand to perform well in applications and it provides high flexibility as well.
On the other hand, in dynamic SQL as long as an access plan is generated at run time there is no requirement for compiling code beforehand.
Hence both of them have some benefits of using them in applications.

Q-16 What is normalization in DBMS?
Ans: Normalization is the process of analysing a database to avoid data redundancy across tables. In normalization, we break down a big table into smaller tables and then create links among those small tables to analyse better. Thus, normalization is helping minimize insertion, update and detect anomalies in a database.
Different types of normalizations are:
• First Normal Form, 1NF
• Second Normal Form, 2NF
• Third Normal Form, 3NF
• Boyce & Codd Normal Form, BCNF

Q-17 What is 1NF?
Ans: 1NF is the most basic type of normalization where each column present in a table should have single-valued attribute. The values stored in a column should belong to the same domain.

Q-18 What is 2NF?
Ans: A table is said to be of 2 NF form if:
• It belongs to first Normalization form or 1NF
• Every non-prime attribute of the table is fully functionally dependent on primary keys.

Q-19 What is 3NF?
Ans: A table is in the 3NF form if:
• It belongs to 2NF
• Every non-prime attribute of the table shows non-transitive dependency on every key of the table.

Q-20 What is BCNF?
Ans: BCNF stands Boyce and Codd Normal Form. For a table to be defined under BCNF:
• It should be a 3NF table
• For every functional dependency A->B, A has to be a super key which in simple word means that A cannot be non-primary attribute if B is a primary attribute.

Q-21 What is Denormalization?
Ans: In denormalization we add redundant data to the database to fasten up the process of data retrieval as instead of getting data through complex join processes, we can just have simpler access to data command.

Q-22 What is the 3-tier architecture in DBMS?
Ans: In 3-tier architecture, DBMS is divided into 3 levels:
• Physical Level: Tells us about where the data present in the database is stored in the secondary storage device such as disks and tapes with additional storage information. Users of DBMS are unaware of the details of the storage of data objects.
• Conceptual Level: Gives the representation of what type of data is stored in the storage device but intrinsic details related to location are still unknown.
• External Level: This level is used to cater to the needs of different users who are accessing the database. For different types of users, data is presented in a user-friendly manner and in such a way, that they can interpret it conveniently.

Q-23 Explain ACID properties?
Ans: ACID properties are essential for maintaining the integrity of data. These properties are used as parameters to assess data transactions in a database. These properties are defined as the following:
 
• Atomicity: The transaction should be either fully executed or shouldn't be executed at all. Partial execution cannot happen.
• Consistency: A database should be consistent before and after the transaction.
For example, let's say a database A consists of bank accounts and from one of the accounts a1, 50 rupees is debited and credited into another account b1 which is stored in database B. if account a1 had Rs 100 initially and b1 had Rs 250 initially then before the transaction and after the transaction, the total sum of both accounts should be equal.
100 + 250 = 50 + 300
• Isolation: This property ensures that concurrent transactions which are happening on a database should not interfere with each other and occur independently. This property will prevent any inconsistency in data which may happen due to running multiple transactions simultaneously.
• Durability: By durability, we mean that once a transaction is completed whatever changes, updates which are done to the database are stored in a disk. So that whenever there is hardware or software failure, the updates remain stored and aren't lost.

Q-24 What is Data Model?
Ans: Data Model is an architecture of how different data elements or data objects are organised together and what are the attributes, set of relations through which data objects interact with each other in the model.
Different data models give us different ideas about how entities and their attributes are related to each other.

Q-25 What are different types of data models in DBMS?
Ans: Different types of data models in DBMS are:
• Relational Model
• Hierarchical Model
• Network Model
• Object-oriented Model
• Entity-Relationship Model

Q-26 What do you mean by Entity?
Ans: Entity is used to represent real-world objects. Entities which have characteristics associated with them are termed as attributes in the E-R model.
For example, A college maintains a database of students who are currently enrolled in it. So, student is an entity here and personal information such as name, roll number, date of birth are attributes associated with student.

Q-27 What is an Entity type?
Ans: Entity Type is a collection of entities having the same attributes. Thus, entity type corresponds to many related tables in a database. Entity type can be considered as a set of attributes which uniquely determines an entity.

Q-28 What is an Entity set?
Ans: Entity Set specifies set of all entities which share the same entity type. For example, considering students as entities, all the students who are enrolled in course A are specified under entity type 'course_A'. Thus, the group of those students form an entity set whose entity type is 'course_A'.

Q-29 What is a Trigger?
Ans: Triggers are SQL statements that are automatically executed whenever there is modification or update in a database.
They are automatically executed whenever an update is done or data is inserted or deleted in a database. In this way, trigger maintains data integrity by allowing data changes systematically.

Q-30 What is a cursor in SQL?
Ans: Cursors are used for processing individual rows returned by database system queries. They act like a pointer to the rows and perform complex logic on a row-by-row basis.
You can think of cursors as database objects which are used in applications to perform data manipulation on a set, one row at a time.

Q-31 What is 2-Tier architecture?
Ans: 2 Tier architecture is generally known as client-server architecture where client part deals with the user interface, communication with the server while server part deals with data storage, organization of data, security and to some extent business logic as well.
In 2 tier architecture, applications running on client end can directly communicate with the database at the server end.

Q-32 What are indexes in DBMS?
Ans: Indexes are data structures which are used for recovering data from relational databases.
They are based on the concept of pointers where pointer for each record in the table is kept in the index (a separate table). Now to carry out a query for a record, instead of searching for the whole table, index can directly point to the required record. This leads to faster retrieval of data and saves time.

Q-33 What is the difference between clustered and non-clustered index?
Ans:
• Clustered indexing leads to faster access of data as compared to Non-Clustered indexing.
• Clustered index sorts the data in the table on their key values whereas the non-clustered index stores the data at one location and indices at another location.
• There can be only one clustered index per table but there can be several non-clustered indexes for one table.

Q-34 What is the difference between VIEW and materialised VIEW?
Ans: The differences between view and materialised view are as following:
• View results are not stored in the disk while materialised view results are stored in the disk. That's why view doesn't require memory space but materialised view require so.
• View is slower than materialised view.
• Materialised view needs to be updated manually with the proper mechanism while view gets automatically updated each time the virtual table is updated.

Q-35 What is functional dependency?
Ans: For example, a store manager maintains a database of customers with attributes customer_id, customer_name, and customer_adress. Now here if we know the customer_id we also get to know customer_name. So here attribute customer_id uniquely determines the attribute customer_name and we say that customer_name is functionally dependent on customer_id. That is
customer_name -> customer_id
Hence, we say that the attributes of a table are functionally dependent if one attribute uniquely determines the other attribute.

Q-36 What is Data Independence?
Ans: Data independence is a feature of DBMS where we can modify a schema at one level without affecting the schema at a higher level.

Q-37 What are Physical Data Independence and Logical Data Independence?
Ans:
• Physical Data Independence means that we can modify schema definitions at the lowest level of DBMS without affecting the schemas at the conceptual level or view level. For eg., We may want to add new files or new indexes inside a database without changing upper levels. Physical data independence allows us to do it.
• Logical Data Independence means that we can bring changes to the schema at the conceptual level without affecting the highest level which is view level. For eg. Like changing attributes of an entity or adding new attributes to improve performance of the database. Logical Data Independence allows us to do it.

Q-38 What is the difference between WHERE and HAVING clause?
Ans:
• HAVING clause is used to choose records from groups or aggregated row which satisfy given conditions while WHERE clause is used to choose records from the table which satisfies certain conditions.
• HAVING clause always require GROUP BY clause and is used after it but WHERE clause can still function without GROUP BY clause and when used with GROUP BY, is used before it.

Q-39 What is the main difference between UNION and UNION ALL?
Ans: The main difference between UNION and UNION ALL is that UNION joins the data of 2 or more tables and takes distinct rows, deleting duplicate rows. UNION ALL also combines different tables but it doesn't delete duplicate rows.

Q-40 What are aggregate and scalar functions?
Ans: Aggregate functions are used for determining a value from the set of rows collected from a table. For example, like SUM, MEAN, STDEV are some aggregate functions which determine sum, average and standard deviation of the column mentioned in the table.
Scalar functions are those functions which operate on a single value and return a single value as an output. Examples of Scalar functions are upper(), lower(), round() etc.

Q-41 What is Cardinality?
Ans: Cardinality is an indicator of how much unique are values in a column. For example, A column denoting list of patients having high sugar will have only two values, yes or no, hence it has low cardinality. But on the other hand, for the same patient list, the column of their names will have more unique values and we say that this column has high cardinality.