All The Useful DBMS Interview Questions & Answers

What is a DBMS?
A database management system (DBMS) is a software that was designed for creating and managing the data in the databases - to create, retrieve, update and manage the data with a high amount of efficiency.

MySQL, Oracle, IBM DB2, and PostgreSQL are some popular DBMS software. 

DBMS behaves as a mediator between the user and the database. As a result, the data is organized and is easily accessible to the user.

Q 1. What is constraints and its types in DBMS?
Constraints are set of rules used to restrict the type of data that can go into a table, to preserve the accuracy and integrity of the records internal the table.

The different types of constraints in DBMS are: 
NOT NULL: it makes sure that column does not hold a NULL value.
UNIQUE: It invokes a column to have a UNIQUE value
DEFAULT: gives the default value to the column
CHECK: specify the range of values for a column

Key Constraints
PRIMARY KEY: it diagnose each and every record in a table
FOREIGN KEY: it points to the primary key of any other table.

Q 2. What is aggregate and scalar functions in DBMS?
Aggregate functions are the calculations on the set or group of value, and they return a single value.

The aggregate functions are: 
AVG-- to calculate the average of the data.
MAX-- to find the maximum value among the given data.
MIN-- to find the minimum value among the given data.
SUM-- It returns the SUM of the data.
COUNT( )--It returns the total number of value in the particular column in the table.
COUNT (*)-- It returns the number of rows in the table even the NULL value.
Scalar Functions are the calculations on the data given by the user, and they return a single value.

The scalar functions are: 
UCASE()-- it converts the data into the CAPITAL LETTERS
LCASE()-- it translates the data into the SMALL LETTERS
MID()-- it extracts the particular text from the given text
LEN() - it returns the length of the text in the text files
ROUND()-- it round of the decimal number for ex-5.5 to 6
NOW()-- it returns the date and time of the system in use
FORMAT()-- it formats the field as per the requirement.

Q 3. What are different types of keys in Database?
There are seven types of database keys: 

Super Key: This key recognizes the row in the table.
Primary Key: This key is a column in the table which recognizes each row in the same table uniquely.
Candidate Key: This key does not have any repeated attribute.
Alternate Key: the -The key which is not primary is called Alternate Key.
Foreign Key: This key a column in the table to create a relationship with another table.
Compound Key: This key has various fields which allow the user to identify a particular record uniquely.
Composite Key: This multiple key attributes to identify the rows uniquely.
Surrogate Key: This is an artificial key finds each of the records uniquely.

Q 4. What is Database management system and why it is used?
A database management system is a software which is used to manage the data by efficiently storing, managing and retrieving it along with the high-end security. Some of the database software are -MySQL, Oracle, Sybase, etc.

Uses of DBMS
Secure management of data
Easy to understand and Implement
Data Storage
Multiuser interface

Q 5. What are the advantages of DBMS?
DBMS has some of the following advantages: 

An amazing decision-making ability
Minimizes the data Redundancy
Fast searching Capability
Low maintenance cost

Q 6. Explain the difference between DBMS and RDBMS.

DBMS  Vs    RDMS

1.    DBMS store data as a file -    RDMS store data in tabular form.
2.    DBMS handle a small amount of data -    RDMS handle a large amount of data
3.    Normalization is absent in DBMS -    Normalization is present in RDMS

Q 7. What is Normalization and why it is used?
Data Redundancy means when the same data is repeated again and again at multiple locations. As a result, deletion, insertion, and updating of the data become a tedious job and also a lot of space is also wasted. Normalization solves this problem by reducing the data redundancy.

There are 3 types of Normalization: 

1st Normal Form
2nd Normal Form
3rd Normal Form

BCNF

Q 8. What do you mean by deadlock DBMS?
A deadlock condition occurs when one task is waiting for the other work to leave the resource which it has a hold. In this current situation, none of the functions gets completed, and the work is always in the waiting state.

Q 9. Explain the necessary techniques to control deadlocks.
There are 3 methods by which deadlocks can be handled: 

Deadlock Prevention: Do not allow the condition that may lead to deadlock.
Deadlock Avoidance: Does not accept the resource request if it can lead to deadlock
Deadlock Detection: Allow the resource request but periodically checks the deadlocks. If found then one of the transaction is aborted.

Q 10. What is the language used by most of the dbms?

There are 4 types of database languages: 
Data Definition Language: It includes CREATE: Create a new database or table, ALTER: Alter the existing database or table, DROP: It drops the database, RENAME: Set a new name for the current database

Data Manipulation Language: It includes: SELECT: Retrieve the data from the database, INSERT: Insert the data, UPDATE: Update the data, DELETE: Delete all the records

Data Control Language: It includes: GRANT: It gives permission to access the database, REVOKE: Take back the permission to access the database.

Transaction Control Language: It includes COMMIT: It saves the work, SAVEPOINT: It sets a point in the transaction to rollback later, ROLLBACK: It restores since the last commit
 

Q 11. What do you mean by E-R Model?
The E-R model stands for the Entity Relational Model. The E-r model is a way of representing the logical relationship between the entities or the objects in order to create a database. The ER model was developed by Peter Pin-Shan Chen in the 1970s.

Q 12. What do you mean by query optimization?
A single query can be solved or executes by writing different query plans or algorithms. Query optimization is a process in which the query optimizer chooses the most efficient algorithm to perform the given query.

Q 13. What do you mean by denormalization?
The denormalization is an optimization process to increase the data redundancy in the database. As a result, the joins are avoided, and the performance of the database structure is improved. Denormalization is done after the normalization process.

Q 14. What is data Independence and also explain its types?
Database systems are the multilayered system. Data independence refers to altering the data of one layer without the on other layers.

There are two types of Data Independence: 
Logical Data Independence: Logical schema is modified without rewriting the application programs.
Physical Data Independence: Physical schema is modified without rewriting the application programs.

Q 15. What is index and also its types in DBMS?
The database index is the data structure that is defined on the columns of the database table. Database indexing speed up the data retrieval process.

There are 3 types of indexing in DBMS: 
Primary Index
Secondary index
Clustered index

Q 16. What do you mean by DML Compiler?
DML stands for Data Manipulation Language.DML compiler translates the DML statements which are there in a query language into the low-level instructions which the query evaluation engine understands easily.

Q 17. What do you mean by checkpoints in DBMS?
When many transactions are executed at the same time, then the logs are interleaved. As a result, it becomes tough for the recovery system to recover the data.

Checkpoint acts as a bookmark that makes the inspections during the transaction execution. Each checkpoint, the previous logs are removed from the system and are stored in the storage disk. As a result recovery of the data is faster.

Other useful DBMS questions: (Just Google them)

Q 18. What is BCNF standard form in DBMS?
Q 19. What is acid property in DBMS?
Q 20. What is the difference between DELETE and TRUNCATE command in DBMS?
Q 21. What is a concurrency problem in DBMS?
Q 22. What do you mean by conflict Serializability in DBMS?
Q 23. What do you mean by starvation in DBMS?