While going to face any IT interview, Database Management System(DBMS) is an important subject, from which a candidate will surely face some questions. So we must go through the basics of Database Management System before the interview process. In this section, we have covered all the basic concepts of database management system in a question and answer format. Most of the dbms interview questions listed here are frequently asked during interview of different companies. Still if it contains any error please do let us know. Any type of suggestion will be highly appreciated.
DBMS Interview questions
What is DBMS?
DBMS is the acronym of Database management System. A system or software which keeps information of data and to perform several operations on data stored within the database.
What is database?
This is the design for storing, designing, retrieving the information and data.
What is the advantage of DBMS?
Advantages of DBMS
- Redundancy Control
- Restrict Unauthorized Access
- Providing Multiple User Interfaces
- Enforcing Integrity Constant
- Provide Backup and Recovery.
What is three level of data abstraction?
Three Level of Data Abstraction :
- a) Physical level : Lower Level
- b) Logical level : Next Higher Level
- c) View level : Highest Level
What are the types of data independence?
The independence means manipulating the one other should not be impacted. For this DATABASE these are of two types. These are as follows-
a) Physical independence: the changes occur in physical layer of DBA and due to that the logical level should not effected.
b) Logical independence: the changes occur in logical layer of DBA and due to that the view level should not effected.
What is a view??
This is the virtual table. It is virtual as because this need no space for this. Application user or developer cant modify the table by this way.
What is data model??
This concept to describing data, data relationships data semantics and constraints. There use some graphical representation to implement the concept. Such as- ER diagram.
What is an entity??
It is a ‘thing’ in the real world with an independent existence. This is the characteristics represented in er model by rectangle in ER model.
What is an attribute??
It is a particular property, which describes the entity. It is represent by oval shape in ER model.
What is weak entity??
An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set.
What are the Properties of the Relational Tables?
Relational tables have six properties which are as follows:-
- Values are atomic.
- Column values are of the same kind.
- Each row is unique.
- The sequence of columns is insignificant.
- The sequence of rows is insignificant.
- Each column have a unique name.
What is Normalization?
In relational database design, the process to organize data in order to minimize redundancy is called normalization.In normalization we generally divide a database into two or more tables and define relationships between the tables. The main motive is to isolate data so that additions, deletions, and modifications of a field can be made in just one table.
What is De-normalization?
De-normalization is the process to optimize the performance of a database by adding redundant data or a technique inorder to move from higher to lower normal forms of database modeling to speed up database access.
How is ACID property related to Database?
ACID ( Atomicity Consistency Isolation Durability) is a concept that database professionals generally look for while evaluating any databases and application architectures. For a reliable database, all this four attributes must be present.
- Atomicity is an all-or-none proposition.
- Consistency means a transaction never leaves a database in a half-finished state.
- Isolation keeps transactions separated from each other unless and until they are finished
- Durability means the database will keep track of changes that are pending in such a manner such that the server can recover from an abnormal termination.
Explain in brief the different Normalization forms?
- 1NF: Repeating groups are eliminated
Make a separate table for each set of related attributes. Each field contains only one value from its attribute domain.
- 2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, then it should be remove to a separate table.
- 3NF: Eliminate Columns which are not dependent on key
If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key.
- BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.
- 4NF: Isolate independent multiple relationships
No table will contain two or more 1:n or n:m relationships that are not directly related.
- 5NF: Isolate related multiple relationships
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
State Difference between simple file and database?
Main difference between a simple file and database that database has independent way of accessing information while simple files do not. File can store, manage and retrieve part of a database but not the independent way of accessing data. File do not provide multi-user capabilities while a DBMS can provide multi-user capabilities.
State difference between DBMS and RDBMS ?
DBMS provides a systematic and organized way to store, manage and retrieve from collection of logically related information. RDBMS provides extra feature that is relationship integrity. So in short,
RDBMS = DBMS + REFERENTIAL INTEGRITY
What are E-R diagrams?
E-R diagram is Entity-Relationship diagram which shows relationship between various tables in the database.
What are DML and DDL statements?
DML stands for Data Manipulation Language Statements. They update data values in table. Some Important DML statements are given:-
- SELECT – will get data from a database table
- UPDATE – It updates data in a table
- DELETE – It deletes data from a database table
- INSERT INTO – wil inserts new data into a database table
DDL stands for Data definition Language. They generally change structure of the database objects like table, index etc. Most important DDL statements are given below:-
- CREATE TABLE -It will create a new table in the database.
- ALTER TABLE – It will change table structure in database.
- DROP TABLE – It will delete a table from database
- CREATE INDEX – It will create an index
- DROP INDEX – It will delete an index
Can you explain Insert, Update and Delete query?
Insert statement is for inserting new rows in to table. Update is use to update existing data in the table. Delete statement to delete a record from the table.
Explain is DML (Data Manipulation Language)? DBMS Interview questions
This language enable user to access or manipulate data as organised by appropriate data model.
- Procedural DML or Low level: DML requires a user to indicate what data are needed and how to get those data.
- Non-Procedural DML or High level: DML requires a user to indicate what data are needed without the need of specifying how to get those data.
Explain Data Warehousing?
Data Warehousing is a technique in which the data is stored & accessed from central location & it will support some strategic decisions. Data Warehousing is not a requirement for data mining just to make it efficient.
What is Data mining ?
Data mining is a technique by which one can analyze the current data from different perspectives and summarize the information in an effective manner. It’s generally used to derive some valuable information from the already existing data or to predict sales to increase customer market.
There are two basic aims of Data mining:-
More Interview questions for freshers:-
Users can give their suggestions in the comment section so as to improve the article ” DBMS Interview Questions