DBMS Interview Questions and Answers

October 5, 2023

DBMS_interview_questions_and_answers
DBMS Questions

Top 50 Database Interview Questions and Answers

Are you gearing up for a DBMS interview? PrepInsta provides a wealth of commonly asked DBMS interview questions  and answers. Whether you’re a newcomer to the field or an experienced candidate, you can access the most up-to-date insights into DBMS topics. Enhance your interview readiness for DBMS with the invaluable resources available at PrepInsta and approach your interview with confidence.

Introduction to DBMS

Answer:-

A Database Management System (DBMS) is a software application or system that enables users to efficiently store, organize, retrieve, and manipulate data. It serves as an intermediary between the user and the underlying database, providing a structured and controlled environment for managing data.

USES OF Database

  1. Business: Manages customer data, products, finances, and employees.
  2. E-commerce: Handles product catalogs, inventory, orders, and customer data.
  3. Banking: Manages accounts, transactions, loans, and risk analysis.
  4. Education: Organizes student records, schedules, and administrative data.
  5. Government: Handles citizen data, tax collection, and infrastructure.
  6. Transportation: Manages schedules, reservations, and logistics.
  7. Manufacturing: Controls inventory, production, and supply chains.
  8. Telecom: Stores call records, customer data, and network info.
  9. Social Media: Manages user content, profiles, and interactions.
  10. Scientific Research: Stores and analyzes research data.

Also Check Out:-

Complete Interview Preparation
Technical Interview Questions
HR Interview Questions
Interview Puzzles

Prime Course Trailer

DBMS interview questions and answers for freshers

Ques 1: What is DBMS?

Ans.

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 easy way to users. Or in a 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.

DBMS_interview_questions_and_answers_what_is_dbms

Ques 2: Types of DBMS.

Ans.

Types of DBMS:

  1. RDBMS: Organizes data in tables using SQL. Examples: Oracle, MySQL, SQL Server.
  2. NoSQL: Handles unstructured data, scales horizontally. Types: Document, Key-Value, Column-Family, Graph. Examples: MongoDB, Cassandra.
  3. OODBMS: Stores data in object-oriented models. Examples: ObjectDB, db4o.
  4. In-Memory DBMS: Stores data in main memory for speed. Examples: Redis, SAP HANA.
  5. Distributed DBMS: Spreads data across servers for scalability. Examples: Google Bigtable, Hadoop.

Ques 3: Benefits of Database.

Ans.

  1. Data Centralization: All data is stored in one place, making it easier to manage and maintain.
  2. Data Consistency: DBMS enforces data integrity rules, ensuring that data remains accurate and consistent.
  3. Data Security: Access control mechanisms protect sensitive information from unauthorized users.
  4. Data Scalability: Many DBMS systems can scale to handle increasing data volumes and user loads.
  5. Data Recovery: Built-in backup and recovery mechanisms safeguard against data loss.
  6. Data Querying: SQL support allows users to retrieve and analyze data efficiently.

Ques 4: Key functions of DBMS.

Ans.

  1. Data Storage: Organizes data in structured formats like tables.
  2. Data Retrieval: Allows efficient data access through queries.
  3. Data Manipulation: Supports adding, updating, and deleting data.
  4. Data Security: Controls data access for authorized users.
  5. Data Integrity: Enforces data accuracy and reliability.
  6. Concurrency Control: Manages simultaneous data access.
  7. Backup and Recovery: Safeguards data and offers recovery tools.
  8. Query Optimization: Enhances query performance.

Ques 5: What is database partitioning?

Ans.

It (Database partitioning) is 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.

DBMS_interview_questions_and_answers_data_partitioning

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get Prime

Database Normalization Interview questions

Ques 6: Define Normalization?

Ans. Organizing data into a relatable Table is known as Normalization.

Ques 7: Enlist the advantages of a normalizing database?

Advantages of the normalizing database are:

  • No duplicate entries
  • Saves storage space
  • Boasts the query performances.

Ques 8: Why do you need to use functional dependencies while normalizing databases?

Ans.

Functional dependencies are essential in the process of normalizing databases because they help ensure that a database design adheres to certain rules and best practices for data organization. Normalization is the process of structuring a database schema to eliminate redundancy and improve data integrity.

Ques 9: What are the different steps involved in normalizing a database?

Ans.
Steps in Normalizing a Database:

  1. Identify entities and attributes.
  2. Define primary keys.
  3. Apply 1NF (atomic values, no repeating groups).
  4. Apply 2NF (full key dependency).
  5. Apply 3NF (remove transitive dependencies).
  6. Apply BCNF (no partial/overlapping dependencies).
  7. Apply 4NF (handle multi-valued dependencies).
  8. Apply 5NF (address join dependencies).
  9. Evaluate and optimize for performance.
  10. Document schema, implement tables, test, and maintain.

Ques 10: Is there any difference between “normalization” and “database normalization”? If yes, then what’s the difference?

Ans.

“Normalization” is a general concept of organizing data, while “database normalization” specifically refers to structuring data in relational databases to reduce redundancy and enhance integrity.

Database Questions Asked in Interview

Ques 11: What are the different levels of abstraction in the DBMS?

Ans.

There are 3 levels of data abstraction in the DBMS.

They include:

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.

DBMS_interview_questions_and_answers_abstraction_level

Ques 12: What is a checkpoint in DBMS and when does it occur?

Ans.

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.

Ques 13: What is the Relationship?

Ans.

The Relationship is defined as an association among two or more entities. There are three types of relationships in DBMS-

  • One-To-One: Here one record of any object can be related to one record of another object.
  • One-To-Many (many-to-one): Here one record of any object can be related to many records of other objects and vice versa.
  • Many-to-many: Here more than one record of an object can be related to n number of records of another object.

Ques 14: What is RDBMS?

Ans.

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.

 

Ques 15: Enlist the disadvantages of the query?

Ans.

The disadvantages of the query are:

  • No indexes
  • Stored procedures are excessively compiled.
  • Triggers and procedures are without SET NOCOUNT ON.
  • Complicated joins making up inadequately written queries.
  • Cursors and temporary tables showcase a bad presentation.

Database Questions in Interview

Ques 16: What is the purpose of normalization in DBMS?

Ans.

Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys to fulfilling certain properties.

The properties include:

  • To minimize the redundancy of the data.
  • To minimize the Insert, Delete, and Update Anomalies.

Ques 17: What is an ER diagram in DBMS?

Ans.

An entity-relationship model or an entity-relationship diagram is a visual representation of data that is represented as entities, attributes, and relationships are set between entities.

Ques 18: What are the integrity rules in DBMS?

Ans.

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 the “Primary key cannot have a NULL value.”

Referential Integrity: It specifies that the “Foreign Key can be either a NULL value or should be the Primary Key value of other relation

Ques 19: Define Atomicity and Aggregation?

Ans.

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

Ques 20: What is an entity-relationship model?

Ans.

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 DBA’s to understand the schema easily.

DBMS Interview Questions and Answers For Experienced.

Ques 21: Popular DBMS Software?

Ans.

Here, is the list of some popular DBMS systems:

  • MySQL
  • Microsoft Access
  • Oracle
  • PostgreSQL
  • dBASE
  • FoxPro
  • SQLite
  • IBM DB2
  • LibreOffice Base
  • MariaDB
  • Microsoft SQL Server etc.

Ques 22: What is concurrency control?

Ans.

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

Ques 23: What is the purpose of SQL?

Ans.

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.

Ques 24: Disadvantage of DBMS?

Ans.

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 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 leads to the loss of some data.
  • DBMS can’t perform sophisticated calculations

Ques 25: What are the unary operations in Relational Algebra?

Ans.

Unary operations are operations that use single operands. Unary operations in relational algebra are PROJECTION and SELECTION.

-=,>=,<=,+= are the relational operators used in SELECTION.

Database Management Interview Questions and Answers

Ques 26: How many types of database languages are?

Ans.

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 of 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 user access to 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.

Ques 27: Define a Relation Schema?

Ans.

A relation schema is known as the blueprint with the help of which we can explain how the data is organized into tables.

Ques 28: What is a degree of Relation?

Ans.

 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.

Ques 29: What are the disadvantages of file processing systems?

Ans.

There are some disadvantages of file processing system :

  • Inconsistent
  • Not secure
  • Data redundancy
  • Difficult in accessing data
  • Data isolation
  • Data integrity
  • Concurrent access is not possible
  • Limited data sharing
  • Atomicity problem

Ques 30: What is data abstraction in DBMS?

Ans.

 Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interaction with the database.

DBMS Interview Questions Intermediate Level

Ques 31: What is Relational Algebra?

Ans.

Relational Algebra is a Procedural Query Language that contains a set of operations that take one or two relations as input and produce a new relationship. Relational algebra is the basic set of operations for the relational model. The decisive point of relational algebra is that it is similar to the algebra which operates on the number.

There are a few fundamental operations of relational algebra:

  • select
  • project
  • set difference
  • union
  • rename, etc.

Ques 32: What is Relational Calculus?

Ans.

Relational Calculus is a Non-procedural Query Language that uses mathematical predicate calculus instead of algebra. Relational calculus doesn’t work on mathematics fundamentals such as algebra, differential, integration, etc. That’s why it is also known as predicate calculus.

There is two types of relational calculus:

  • Tuple relational calculus
  • Domain relational calculus

Ques 33: Explain ACID properties?

Ans.

ACID properties are some basic rules, which have to be satisfied by every transaction to preserve the integrity. These properties and rules are:

ATOMICITY: Atomicity is more generally known as ? all or nothing rule.’ This implies all are considered as one unit, and they either run to completion or are not executed at all.

CONSISTENCY: This property refers to the uniformity of the data. Consistency implies that the database is consistent before and after the transaction.

ISOLATION: This property states that the number of transactions can be executed concurrently without leading to the inconsistency of the database state.

DURABILITY: This property ensures that once the transaction is committed it will be stored in the non-volatile memory and a system crash can also not affect it anymore.

Ques 34: What do you understand by Data Model?

Ans.

A data model is a conceptual framework that defines how data is organized, structured, and related within a database or information system. It includes entities (objects), attributes (properties), relationships (connections), and constraints (rules). Data models are essential for designing and managing data effectively in software and database development. accessible. In this architecture, the application on the client-end interacts with an application on the server which further communicates with the database system.

Ques 35: What is Data Warehousing?

Ans.

Data warehousing is the process of collecting, storing, and managing data from various sources in a central repository. It’s used to support business intelligence and analytics by providing a unified, historical, and structured view of an organization’s data for better decision-making.

Ques 36: Explain the difference between intension and extension in a database.

Ans.

  • Intension: The conceptual design of a database, specifying its structure, rules, and relationships. Static and defines how data should be organized. Example: Defining tables like “Authors” with attributes.
  • Extension: Actual data instances in the database at a given time. Dynamic and changes as data is added, modified, or deleted. Example: Specific books and authors currently in the library database.

Ques 37: Explain the difference between the DELETE and TRUNCATE command in a DBMS.

Ans.

  • DELETE Command:
    • Granularity: Row-level deletion based on conditions.
    • Logging: Generates individual log entries for each deleted row.
    • Performance: Slower, especially for many rows.
    • Usage: Selective row removal.
    • Example: DELETE FROM employees WHERE salary < 50000;
  • TRUNCATE Command:
    • Granularity: Table-level, removes all rows.
    • Logging: Minimally logged or non-logged, faster.
    • Performance: Faster, especially for large tables.
    • Usage: Quick removal of all data in a table.
    • Example: TRUNCATE TABLE employees;

Ques 38: What is meant by normalization and denormalization?

Ans.

  • Normalization: Organizes data to eliminate redundancy and maintain integrity by dividing it into related tables.
  • Denormalization: Introduces redundancy to enhance query performance and simplify joins by combining tables or duplicating data.
  • Benefits: Normalization reduces data duplication and minimizes anomalies, while denormalization improves query speed and simplifies queries.
  • Example: Normalization separates customer and order data, while denormalization may duplicate customer info in the order table for faster queries.

Ques 39: Explain different types of Normalization forms in a DBMS.

Ans.

Normalization forms in database design:

  1. 1NF: Ensure columns have atomic values, unique names, and order independence.
  2. 2NF: Eliminate partial dependencies, ensuring non-key attributes depend on the entire primary key.
  3. 3NF: Remove transitive dependencies, so non-key attributes rely solely on the primary key.
  4. BCNF: Eliminate all non-trivial functional dependencies, ensuring tables are free from redundancy.
  5. 4NF: Address multi-valued dependencies to prevent anomalies with multiple attribute values.
  6. 5NF: Handle join dependencies, enabling further table decomposition without data loss.

Each form enhances data integrity and reduces redundancy based on specific rules and objectives. Choice depends on database requirements and trade-offs.

Ques 40: What is the 3-Tier architecture?

Ans.

The 3-Tier architecture splits an application into three parts: user interface (presentation), logic (application), and data storage (database). It enhances scalability, maintainability, and security in software design.

The 3-Tier architecture divides an application into three layers:

  1. Presentation Tier (UI).
  2. Application Tier (Logic).
  3. Data Tier (Database).

It improves scalability, maintainability, security, and flexibility in software development.

DBMS_interview_questions_and_answers_3_tier_architect

Ques 41: What is 2-Tier architecture?

Ans.

2-Tier architecture divides an app into a client-side (UI) and server-side (logic and data) component. It’s simple but lacks scalability and flexibility seen in newer architectures.

2-Tier architecture divides an app into:
Client Tier: Handles UI and user interaction.
Server Tier: Manages logic and data processing.
It’s simple but less scalable and flexible compared to newer architectures like 3-Tier.

DBMS_interview_questions_and_answers_2_tier_architect

Ques 42: What is the difference between a shared lock and exclusive lock?

Ans.

Shared Lock vs. Exclusive Lock:

Shared Lock: For reading, allows multiple transactions to read concurrently, compatible with other shared locks, doesn’t block them.
Exclusive Lock: For writing, ensures only one transaction can write, blocks all other locks (shared and exclusive) until released.

Ques 43: How do you communicate with an RDBMS?

Ans.

To communicate with an RDBMS:

  1. Use SQL queries for data operations.
  2. Utilize programming language APIs (e.g., JDBC, psycopg2).
  3. Employ ORM frameworks for object-oriented interaction.
  4. Access via command-line interfaces, GUIs, or web-based interfaces.
  5. Middleware and REST APIs enable web services.
  6. Stored procedures and functions for predefined operations.

Ques 44: What is Weak Entity set?

Ans.

A Weak Entity Set is an entity in a database that lacks a unique identifier on its own and depends on another related entity (the owning entity) for uniqueness. It often has an existence dependency on the owning entity and uses a combination of attributes and the owning entity’s identifier to uniquely identify its instances.

Ques 45: What is an attribute?

Ans.

In a DBMS, an attribute is a characteristic or property that describes a specific aspect of an entity or object within a database. It corresponds to a column in a table and has a defined data type.

Ques 46: What is 1NF in the DBMS?

Ans.

1NF (First Normal Form) in DBMS:

  • Ensures each attribute contains atomic (indivisible) values.
  • Eliminates repeating groups by separating them into separate tables.
  • Creates separate tables for many-to-many relationships using junction tables.

Ques 47: What is 2NF in the DBMS?

Ans.
2NF (Second Normal Form) in DBMS:

  • Builds upon 1NF.
  • Ensures that non-key attributes are fully functionally dependent on the entire primary key.
  • Eliminates partial dependencies by separating them into related tables.
  • Suitable for more complex databases with composite primary keys.

Ques48: What is 3NF in the DBMS?

Ans.

3NF (Third Normal Form) in DBMS:

  • Builds upon 2NF.
  • Ensures that non-key attributes are not transitively dependent on the primary key.
  • Eliminates transitive dependencies by separating them into related tables.
  • Aims to achieve better data integrity and reduce data redundancy.

Ques 49: What is BCNF in the DBMS?

Ans.

BCNF (Boyce-Codd Normal Form) in DBMS:

  • A stricter form of normalization.
  • Ensures that for every non-trivial functional dependency X -> Y, X is a superkey.
  • Eliminates partial and transitive dependencies.
  • Reduces data redundancy and enhances data integrity.
  • Suitable for complex databases with more stringent constraints.

Ques 50: What is the main goal of RAID technology?

Ans.

The main goal of RAID (Redundant Array of Independent Disks) technology is to improve data storage reliability, availability, and performance by combining multiple physical hard drives into a single logical unit. RAID achieves this primarily through techniques like data striping, mirroring, and redundancy. The specific goals of RAID technology include:

Also Check

Join Our Interview Course Now to Get Yourself Prepared -

Join Our Interview Course Now to Get Yourself Prepared

Prepare for the interview process in both Service and Product Based companies along with Group Discussion, Puzzles, Resume Building, HR, MR and Technical Interviews.

Get Prime
All Interview
Get Prime
All Interview