DBMS Interview Questions

A Database Management System (DBMS) is software that helps in creating, managing, and interacting with databases. It provides an interface for users and applications to store, retrieve, and manipulate data efficiently. DBMS handles tasks such as ensuring data integrity, enforcing security, managing concurrent access, and providing backup and recovery options.

Popular DBMS examples include MySQL, PostgreSQL, Oracle, and SQL Server.

Advantages of Using a DBMS:

• Data Integrity: Maintains accuracy and consistency of data over its lifecycle.
• Data Security: Controls access through user authentication and authorization.
• Efficient Data Retrieval: Supports indexing and optimized querying for fast data access.
• Reduced Redundancy: Eliminates duplicate data through normalization techniques.
• Backup and Recovery: Ensures data safety through automatic backup and recovery features.
• Concurrent Access: Allows multiple users to work with the database simultaneously without data conflicts.

DBMS (Database Management System) and RDBMS (Relational Database Management System) are both systems used to store and manage data, but they differ in how the data is organized and managed.

Key Differences Between DBMS and RDBMS:

There are four main types of Database Management Systems (DBMS), each designed to handle data differently depending on the structure and use case:

• Hierarchical DBMS: Organizes data in a tree-like structure where each record has a single parent and multiple children. Suitable for one-to-many relationships.
  Example: IBM's IMS.
• Network DBMS: Data is represented as a graph structure with multiple relationships, allowing many-to-many relationships.
  Example: Integrated Data Store (IDS).
• Relational DBMS (RDBMS): Stores data in tables (rows and columns) with support for SQL and relational operations. Highly popular and widely used.
  Examples: MySQL, PostgreSQL, Oracle.
• Object-Oriented DBMS: Stores data in the form of objects, similar to object-oriented programming. Supports complex data types and inheritance.
  Example: ObjectDB.

In DBMS, a relation refers to a table in a relational database. It is used to organize data in a structured format consisting of rows and columns.

• Rows (Tuples): Each row in the relation represents a single record or entry.
• Columns (Attributes): Each column represents a specific property or field of the data (like name, age, ID).
• Schema: The structure of a relation is defined by its schema, which outlines the column names and data types.

Relations are the foundation of relational databases and support operations like SELECT, INSERT, UPDATE, and DELETE for data manipulation.

In a DBMS, a table is the fundamental structure used to store data in a relational database. It organizes information into a grid of rows and columns.

• Rows (Records): Each row contains a unique data entry representing a specific entity.
• Columns (Attributes): Each column holds a particular type of information or property about the entity (such as name, age, or ID).

Tables allow for easy access, querying, and manipulation of structured data using SQL. They form the backbone of relational database systems.

A Database Management System (DBMS) is composed of several essential components that work together to store, retrieve, and manage data efficiently. The primary components are:

• Database Engine: The core service that handles data storage, retrieval, and manipulation.
• Database Schema: Defines the logical structure of the database, including tables, fields, relationships, and constraints.
• Query Processor: Parses, interprets, and executes SQL queries issued by users or applications.
• Transaction Manager: Ensures the ACID (Atomicity, Consistency, Isolation, Durability) properties are maintained for each transaction.
• Storage Manager: Manages how data is stored on physical media, including indexing, buffering, and data access.

Together, these components enable robust, reliable, and efficient data handling within a DBMS.

In relational database systems, Primary Keys and Foreign Keys are essential concepts used to uniquely identify records and establish relationships between tables.

Primary Key: A primary key is a column (or a set of columns) in a table that uniquely identifies each record in that table. It cannot contain duplicate or NULL values.

Foreign Key: A foreign key is a column (or set of columns) that creates a relationship between two tables by referencing the primary key in another table. It ensures referential integrity between the related tables.

Normalization is a technique used in database design to minimize data redundancy and ensure data integrity by organizing data into multiple related tables. It restructures a database in a way that every piece of data is stored only once, unless there's a specific reason to duplicate it.

Why is Normalization Important?

• Eliminates Redundancy: Avoids duplication of data across tables, which saves storage and makes updates easier.
• Prevents Anomalies: Helps prevent update, insertion, and deletion anomalies that can corrupt data consistency.
• Ensures Data Integrity: Maintains consistency and accuracy in the database.
• Efficient Querying: Organized structure leads to better performance and optimized queries.

A Candidate Key is a set of one or more attributes that can uniquely identify a tuple in a relation. A relation can have multiple candidate keys, and one of them is chosen as the primary key.

The SELECT statement is used to query data from one or more tables. It allows you to retrieve specific columns or all columns, optionally applying filters (WHERE), sorting (ORDER BY), and joining multiple tables.

  SELECT NAME, AGE FROM STUDENT WHERE AGE > 18;
  

A View is a virtual table created by querying one or more base tables. It does not store data physically but dynamically retrieves it when queried. Unlike a table, a view does not store its own data but presents data from other tables.

• One-to-One (1:1): A record in one table is associated with a single record in another table.
• One-to-Many (1:M): A record in one table is associated with multiple records in another table.
• Many-to-Many (M:M): Multiple records in one table are associated with multiple records in another table.

A schema in DBMS is the structure that defines the organization of data in a database. It includes tables, views, relationships, and other elements. It defines the tables, their columns, constraints, keys, and relationships.

Constraints are rules in DBMS that limit the type of data that can be inserted into a table to ensure data integrity and consistency. Common types:

• NOT NULL
• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• CHECK
• DEFAULT

• DELETE: Deletes specific rows from a table based on a condition. Logs each deletion and can be rolled back.
• TRUNCATE: Removes all rows without logging individual deletions. Cannot be rolled back, faster than DELETE.

An index is a data structure that improves the speed of data retrieval operations on a table. It works like a book's table of contents, allowing the DB to quickly locate a record based on a column value.

• Database design
• Backup and recovery
• Performance tuning
• Security management
• Data integrity
• Upgrades and patches
• Troubleshooting

An ERD is a visual representation of entities and their relationships in a system. It shows entities, attributes, and relationships for database design.

A JOIN combines columns from two or more tables based on a related column between them. Types:

• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN: Returns all rows from left table and matching rows from right table, NULL for no matches.
• RIGHT JOIN: Returns all rows from right table and matching rows from left table, NULL for no matches.
• FULL JOIN: Returns all rows with matches in either table, NULL where no match exists.
• CROSS JOIN: Returns Cartesian product of the tables.
• SELF JOIN: Joins a table with itself using aliases.

• Physical Level: The lowest level of abstraction. Describes how data is stored in the database, including storage structures and access methods like indexing and hashing.
• Logical Level: The next level up, describing what data is stored and the relationships among them. Defines the schema without worrying about storage details.
• View Level: The highest level, showing data as seen by end-users. Hides complexity and presents only relevant data via views.

• First Normal Form (1NF): Each table cell contains a single value; all entries in a column are of the same type.
• Second Normal Form (2NF): In 1NF and all non-key attributes fully depend on the primary key.
• Third Normal Form (3NF): In 2NF and no transitive dependencies (non-key attributes depend only on the primary key).
• Boyce-Codd Normal Form (BCNF): Stronger 3NF ensuring every determinant is a candidate key.
• 4NF: Removes multi-valued dependencies.
• 5NF: Removes join dependencies.

They are used to reduce redundancy, prevent anomalies, and maintain integrity.

Normalization: Organizes data to reduce redundancy and improve integrity by splitting data into related tables.

Denormalization: Adds redundancy intentionally to improve read performance and reduce joins. May increase risk of anomalies but speeds up queries.

• Atomicity: Ensures all parts of a transaction succeed or none do.
• Consistency: Ensures transactions move the database from one valid state to another.
• Isolation: Ensures concurrent transactions don’t interfere with each other.
• Durability: Ensures committed transactions persist even in case of failure.

They ensure reliable transaction processing and maintain data integrity in concurrent environments.

Deadlock: A deadlock occurs when two or more transactions wait indefinitely for each other to release locks, creating a cycle of dependency and halting progress.

Deadlock Avoidance Strategies:

• Lock Timeout: Roll back a transaction if it waits longer than a set time.
• Deadlock Prevention: Enforce a strict order in acquiring locks to avoid cyclic dependencies.
• Deadlock Detection & Resolution: Monitor for deadlocks and resolve them by rolling back one transaction involved.

Stored Procedures: Precompiled SQL code stored in the database, executed by name.

Advantages:

• Improved performance due to precompilation.
• Reduced network traffic by executing multiple statements in one call.
• Enhanced security through parameterized queries.
• Centralization of business logic in the database.

  CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
  BEGIN
      SELECT * FROM employees WHERE department_id = dept_id;
  END;