DBMS Interview Questions Set 1
Q.1. What is a database?
Answer:- A Database is a logical, consistent and organized collection of data that it can easily be accessed, managed and updated. Databases, also known as electronic databases are structured to provide the facility of creation, insertion, updating of the data efficiently and are stored in the form of a file or set of files, on the magnetic disk, tapes and another sort of secondary devices.
Q.2. What is DBMS?
Answer:- DBMS is a collection of programs that facilitates users to create and maintain a database. In other words, DBMS provides us an interface or tool for performing different operations such as the creation of a database, inserting data into it, deleting data from it, updating the data, etc. DBMS is a software in which data is stored in a more secure way as compared to the file-based system.
Q.3. Mention the different languages present in DBMS
- Data Definition Language(DDL):- Consists of commands which are used to define the database.
- Data Manipulation Language(DML):- Consists of commands which are used to manipulate the data present in the database.
- Data Control Language(DCL):- Consists of commands which deal with the user permissions and controls of the database system.
- Transaction Control Language(TCL):- Consist of commands which deal with the transaction of the database.
Q.4. Do we consider NULL values the same as that of blank space or zero?
Answer:- A NULL value is not at all same as that of zero or a blank space. The NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas zero is a number and blank space is a character.
Q.5. What do you understand by the terms Entity, Entity Type, and Entity Set in DBMS?
Answer:-
- Entity: An entity is a real-world object having attributes, which are nothing but characteristics of that particular object. For example, an employee can be an entity. This particular entity can have attributes such as empid, empname, etc.
- Entity Type: Entity type is nothing but a collection of entities, having the same attributes. Generally, an entity type refers to one or more related tables in a particular database. So, you can understand, entity type as a characteristic which uniquely identifies the entity. For example, An employee can have attributes such as empid, empname, department, etc.
- Entity Set: An entity set is the collection of all the entities of a particular entity type in a database. For example, a set of employees, a set of companies, and a set of people can come under an entity set.
Q.6. What are relationships and mention different types of relationships in the DBMS
Answer:- A relationship in DBMS is the scenario where two entities are related to each other. In such a scenario, the table consisting of foreign key references to that of a primary key of the other table.
- One-to-One Relationship - Used when a single row in Table A is related to a single row in Table B.
- One-to-Many Relationship - Used when a single row in Table A is related to many rows in table B.
- Many-to-Many Relationship - Used when many rows in table A can be related to many rows in table B.
- Self -Referencing Relationship - Used when a record in table A is related to the same table itself.
Q.7. What are the ACID properties in DBMS?
- Atomicity: Atomicity refers to those transactions which are completely successful or failed. Here each transaction refers to a single logical operation of a data. So, even if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
- Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
- Isolation: The main goal of isolation is concurrency control.
- Durability: Durability means that if a transaction has been committed, it will occur whatever may be the scenario.
Q.8. What are the three levels of data abstraction?
Answer:- Following are three levels of data abstraction:
- Physical level: It is the lowest level of abstraction. It describes how data are stored.
- Logical level: It is the next higher level of abstraction. It describes what data are stored in the database and what the relationship among those data is.
- View level: It is the highest level of data abstraction. It describes only part of the entire database.
Q.9. What is normalization and what are the different types of normalization?
Answer:- The process of organizing data to avoid any duplication of data and redundancy is known as Normalization. There are many successive levels of normalization which are known as normal forms. Each consecutive normal form depends on the previous one. The following are the first three normal forms. Apart from these, you have higher normal forms such as BCNF.
- First Normal Form (1NF) – No repeating groups within rows
- Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the whole primary key.
- Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key (supporting) column value.
Q.10. What are the different types of keys in the database?
- Candidate Key – This is a set of attributes which can uniquely identify a table. Each table can have more than a candidate key. Apart from this, out of all the candidate keys, one key can be chosen as the Primary key. In the above example, since CustomerID and PanNumber can uniquely identify every tuple, they would be considered as a Candidate Key.
- Super Key – This is a set of attributes which can uniquely identify a tuple. So, a candidate key, primary key, and a unique key is a superkey, but vice-versa isn’t true.
- Primary Key – This is a set of attributes which are used to uniquely identify every tuple. In the above example, since CustomerID and PanNumber are candidate keys, any one of them can be chosen as a Primary Key. Here CustomerID is chosen as the primary key.
- Unique Key – The unique key is similar to the primary key, but allows NULL values in the column. Here the PanNumber can be considered as a unique key.
- Alternate Key – Alternate Keys are the candidate keys, which are not chosen as a Primary key. From the above example, the alternate key is PanNumber
- Foreign Key – An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers. in the above example, the CustomerID from the Customers Table is referred to the CustomerID from the Customer_Payment Table.
- Composite Key – A composite key is a combination of two or more columns that identify each tuple uniquely. Here, the CustomerID and Date_of_Payment can be grouped together to uniquely identify every tuple in the table.
Q.11. What is functional Dependency?
Answer:- Functional Dependency is the starting point of normalization. It exists when a relation between two attributes allow you to determine the corresponding attribute's value uniquely. The functional dependency is also known as database dependency and defines as the relationship which occurs when one attribute in a relation uniquely determines another attribute. It is written as A->B which means B is functionally dependent on A.
Q.12. What is the E-R model?
Answer:- E-R model is a short name for the Entity-Relationship model. This model is based on the real world. It contains necessary objects (known as entities) and the relationship among these objects. Here the primary objects are the entity, attribute of that entity, relationship set, an attribute of that relationship set can be mapped in the form of E-R diagram.
In E-R diagram, entities are represented by rectangles, relationships are represented by diamonds, attributes are the characteristics of entities and represented by ellipses, and data flow is represented through a straight line.
Q.13. Explain the terms specialization and generalization.
Answer:-
- Specialization: Specialization is a process of defining a set of sub classes of the entity type. Here, each subclass will contain all the attributes and relationships of the parent entity. Apart from this, the sub classes may contain additional attributes and relationships specific to itself.
- Generalization: Generalization is a process of finding relations, common attributes for a particular set of entities; and finally defining a common super class for them.
Q.14. What are the differences between drop, truncate and delete commands?
| Drop |
Truncate |
Delete |
| Used to delete a database, table or a view |
Used to delete all rows from a table |
Used to delete a row in the table |
| Data cannot be rollbacked |
Data cannot be rollbacked |
Data can be rollbacked |
| A DDL command |
A DDL command |
A DML command. |
| Slower than truncate |
Faster than drop and delete |
Slower than truncate. |
| Deletes the full structure of the table |
Preserves the structure of the table |
Deletes the structure of the row from a table |
Q.15. What is Join?
Answer:- The Join operation is one of the most useful activities in relational algebra. It is most commonly used way to combine information from two or more relations. A Join is always performed on the basis of the same or related column.
Inner joins: Inner join is of 3 categories. They are:
- Theta join
- Natural join
- Equi join
-
Outer joins: Outer join have three types. They are:
- Left outer join
- Right outer join
- Full outer join
Q.16. What are indexes? Mention the differences between the clustered and non-clustered index
Answer:- Indexes are data structures responsible for improving the speed of data retrieval operations on a table. This data structure uses more storage space to maintain extra copies of data by using additional writes. So, indexes are mainly used for searching algorithms, where you wish to retrieve data in a quick manner.
| Clustered Index |
Non-clustered Index |
| A clustered index is faster |
Non clustered index is relatively slower |
| One table can only have one clustered index |
One table can only have many non clustered indexes |
Q.17. What do you understand by intension and extension?
Answer:- Intension: Intension or most commonly known as Database schema defines the description of the database. This is specified during the database design and mostly remains unchanged.
Extension: Extension is the number of tuples available in the database at any instance of time. This value keeps changing as and when the tuples are created, updated and destroyed. So, the data present in the database at a specific instance of time is known as the extension of the database or most commonly known as the snapshot of the database.
Q.18. What is a checkpoint in DBMS and when does it occur?
Answer:- A checkpoint is a mechanism where all the previous logs are removed from the system and are permanently stored on the storage disk. So, basically, checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of crash.
Q.19. What is stored procedure?
Answer:- The stored procedure increases the reusability as here the code or the procedure is stored into the system and used again and again that makes the work easy, takes less time in processing and decreases the complexity of the system. So, if you have a code which you need to use again and again then save that code and call that code whenever it is required.
Q.20. Mention the differences between Unique Key and Primary Key.
| Unique Key |
Primary Key |
| Unique Key can have a NULL value |
The primary key cannot have a NULL value |
| Each table can have more than one unique key |
Each table can have only one primary key
|
For any queries or doubts refer to comment section mentioning question number with doubt
For more Technical MCQ's and Interview Questions Click here
| 
| 
