DBMS Interview Questions and Answers

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

DBMS has some of the following advantages:-

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

This question has always been a center of the discussion in DBMS interview questions.

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

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.

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.

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

Point to be noted: Go through this Q&A very thoroughly as this is one of the critical DBMS interview questions for freshers.

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.

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.

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.

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.

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 as follows:-

• 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.

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

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.

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.

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.

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.