DBMS Interview Questions For Freshers
Top DBMS Interview Questions and Answers for Freshers
PrepInsta provides you most common DBMS Interview Question and Answer for Freshers.
What is DBMS?
Database management system is a software that controls all the different manipulation of stored or to be stored data in a database. It allows, creation, updation, manipulation, definition of a database.
To know more about DBMS Click on the button below-
Commonly Asked DBMS Interview Questions and Answers for Freshers
1. What is DBMS
Solution:- DBMS is a collection of programs that provides users to create and maintain a database. It manages incoming data, Organizes to provide a better and ease way to users. Or in Simpler way, 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. Using DBMS, we can overcome many problems such as- data redundancy, data inconsistency, easy access, more organized and understandable, and so on.
2. What are the advantages of DBMS?
Solution:- The advantages of DBMS are as follows:
- Data Independence: It allows to change the structure of the data without affecting the structure of any of the running application programs.
- Sharing of Data: Multiple users can use data from the same database simultaneously.
- Integrity constraints: These constraints allow the data to be stored in a database in a refined manner.
- Redundancy control: Supports a mechanism to control the redundancy of data by integrating all the data into a single database.
- Provide backup and recovery facility: Provides a feature of ‘backup and recovery’ to automatically create the data backup and restore the data as and when required.
3. What is a database partitioning?
Solution:- It (Database partitioning) is basically a process where a logical database is divided into different independent parts. The database objects like tables, indexes are subdivided and managed and accessed at the granular level.
5. Enlist the advantages of normalizing database?
Solution:- Advantages of normalizing database are:
- No duplicate entries
- Saves storage space
- Boasts the query performances.
6. What are the different levels of abstraction in the DBMS?
Solution:- There are 3 levels of data abstraction in the DBMS.
- Physical Level: This is the lowest level of the data abstraction which states how the data is stored in the database.
- Logical Level: This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.
- View Level: This is the highest level in the data abstraction which shows/states only a part of the database.
7. What is a checkpoint in DBMS and when does it occur?
Solution:- 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.
8. What are the three levels of data abstraction?
Solution:- 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.
9. What is RDBMS?
Solution:- RDBMS is the Relational Database Management System which contains data in the form of the tables and data is accessed on the basis of the common fields among the tables.
10. Mention the different types of DBMS.
Solution:- The different types of DBMS are as follows:
- Relational DBMS (RDBMS): This type of DBMS, uses a structure which allows the users to access data in relation to another piece of data in a database. In this type of DBMS, data is stored in the form of tables.
- Hierarchical DBMS: As the name suggests, this type of DBMS has a structure similar to that of a tree, wherein the nodes represent records and the branches of the tree represent fields.
- Network DBMS: This type of DBMS supports many-to-many relations wherein multiple member records can be linked.
- Object-oriented DBMS: Uses small individual software called object to store pieces of data and the instructions for the actions to be done with the data.
11. Enlist the disadvantages of query.
Solution:- The disadvantages of query are:
- No indexes
- Stored procedures are excessively compiled.
- Triggers and procedures are without SET NOCOUNT ON.
- Complicated joins making up inadequately written query.
- Cursors and temporary tables showcase a bad presentation.
12. What is the purpose of normalization in DBMS?
Solution:- Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys in order to fulfill certain properties.
The properties include:
- To minimize the redundancy of the data.
- To minimize the Insert, Delete and Update Anomalies.
13. What is an ER diagram in DBMS?
Solution:- An entity-relationship model or an entity-relationship diagram is a visual representation of data which is represented as entities, attributes and relationships are set between entities.
14. What are the integrity rules in DBMS?
Solution:- Data integrity is one significant aspect while maintaining the database. So, data integrity is enforced in the database system by imposing a series of rules. Those set of integrity is known as the integrity rules.
There are two integrity rules in DBMS:
Entity Integrity : It specifies that “Primary key cannot have a NULL value.”
Referential Integrity: It specifies that “Foreign Key can be either a NULL value or should be the Primary Key value of other relation
15. Define Atomicity and Aggregation.
Atomicity: It’s an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.
Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.
16. What is an entity-relationship model?
Solution:- It is a diagrammatic approach to database design, where you represent real-world objects as entities and mention relationships between them. This approach helps the team of DBAs’ to understand the schema easily.
17. Popular DBMS Software
Solution:- Here, is the list of some popular DBMS system:
- Microsoft Access
- IBM DB2
- LibreOffice Base
- Microsoft SQL Server etc.
18. What is concurrency control?
Solution:- This is a process managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control:
- Optimistic approach – Involves versioning
- Pessimistic approach – Involves locking
19. What is the purpose of SQL?
Solution:- SQL stands for Structured Query Language whose main purpose is to interact with the relational databases in the form of inserting and updating/modifying the data in the database.
20. Disadvantage of DBMS.
Solution:- DBMS may offer plenty of advantages but, it has certain flaws-
- Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization.
- Most database management systems are often complex systems, so the training for users to use the DBMS is required.
- In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media
- Use of the same program at a time by many users sometimes lead to the loss of some data.
- DBMS can’t perform sophisticated calculations
21. What are the unary operations in Relational Algebra?
Solution:- Unary operations are the operations which uses single operands. Unary operations in relational algebra are PROJECTION and SELECTION.
-=,>=,<=,+= are the relational operators used in SELECTION.
22. How many types of database languages are?
Solution:- There are four types of database languages:
- Data Definition Language (DDL) : These commands are used for updating the data. CREATE, ALTER, DROP, TRUNCATE, RENAME are some examples DDL commands.
- Data Manipulation Language (DML) : These commands are used for the manipulation of already updated data. SELECT, UPDATE, INSERT, DELETE are some examples of DML commands.
- DATA Control Language (DCL) : These commands are used for giving and removing the user access on the database. GRANT and REVOKE are the examples of DCL commands.
- Transaction Control Language (TCL) : These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML. COMMIT, ROLLBACK, and SAVEPOINT are the examples of TCL commands.
23. Define a Relation Schema.
Solution:- Relation schema is known as the blueprint with the help of which we can explain that how the data is organized into tables.
24. What is a degree of Relation?
Solution:- A degree of relation is also known as Cardinality it is defined as the number of occurrence of one entity which is connected to the number of occurrence of other entity.
25. What are the disadvantages of file processing systems?
Solution:- There are some disadvantages of file processing system :
- Not secure
- Data redundancy
- Difficult in accessing data
- Data isolation
- Data integrity
- Concurrent access is not possible
- Limited data sharing
- Atomicity problem