Dbms Interview Questions
An Overview of DBMS
A database management system (DBMS) is a software which was designed for creating and managing the data in the databases. Programmers use DBMS to create, retrieve, update and manage the data with a high amount of efficiency.MySQL,Oracle,IBM DB2,PostgreSQL are the 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.
DBMS Interview Questions and Answers we are mentioning below will help you to gain enough of the knowledge about DBMS.
- DBMS offers high data security
- Minimum Data Inconsistency
- Allows the sharing of Data
- Automatically does the backup and recovery
All your questions related to DBMS will be answered in the DBMS interview questions for freshers mentioned below.
DBMS was designed by Edgar Codd in the year 1970 while he was for working for IBM. The first two database systems created between 1974 and 1977 were Ingres by UBC and System R by IBM
The latest databases are Oracle, MySQL, and DB2.
Most Frequently Asked Dbms Interview Questions And Answers With Examples:
- What is Database management system(DBMS) and why it is used?
- What are the advantages of DBMS?
- Explain the difference between DBMS and RDBMS?
- What is Normalization and why it is used?
- What do you mean by deadlock DBMS?
- Explain the necessary techniques to control deadlocks?
- Explain the different types of languages that are available in the DBMS?
- What do you mean by E-R Model?
- What do you mean by query optimization?
- What do you mean by denormalization?
- What is data Independence and also explain its types?
- Explain the different types of constraints in DBMS?
- What is index and also explain types of indexing in DBMS?
- What do you mean by aggregate and scalar functions in DBMS?
- What do you mean by DML Compiler?
- What do you mean by checkpoints in DBMS?
- What is BCNF standard form in DBMS?
- What is acid property in DBMS?
- What is the difference between DELETE and TRUNCATE command in DBMS?
- Explain the different types of database keys?
- What is a concurrency problem in DBMS?
- What do you mean by conflict Serializability in DBMS?
- What do you mean by starvation in DBMS?
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.
- Secure management of data
- Easy to understand and Implement
- Data Storage
- Multiuser interface
|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|
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
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.
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.
This particular DBMS Question and Answer explains the importance of data independence
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 which 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.
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.