DBMS QUESTION BANK SOLUTION
1. What is a Database Management System (DBMS) and explain the purpose of the database
system?
A Database Management System (DBMS) is a software application that interacts with
end-users, applications, and the database itself to capture and analyze data. It provides a
systematic and organized way of storing, managing, and retrieving large amounts of data
efficiently. The main purposes of a DBMS include:
Data Storage, Retrieval, and Management: DBMS allows for efficient storage and retrieval of
data, which is crucial for applications that need rapid data access.
Data Integrity and Security: It enforces constraints and data validation to maintain the accuracy
and consistency of data.
Data Independence: By using a DBMS, the data is separated from the applications that use it,
ensuring that changes to the structure of the data do not necessarily require changes to the
applications.
Concurrency Control: DBMS supports multiple users accessing the data simultaneously without
conflicts or inconsistencies.
Backup and Recovery: It provides mechanisms for backup and recovery of data in case of
system failures.
A DBMS is essential for businesses and applications where data management is critical, such
as banking, airlines, and telecommunications.
2. Describe the view concept in databases and explain the various levels of view.
The view concept in databases refers to a virtual table that is derived from one or more base
tables. A view does not store data physically but displays data dynamically from the base tables.
It provides an abstraction layer that can simplify complex queries, enhance security, and provide
a customized representation of data.
Three Levels of Database View:
Internal Level (Physical Level): This is the lowest level that describes how data is actually stored
in the database. It deals with the physical storage of data, such as on disks and in data files.
Conceptual Level (Logical Level): This level describes what data is stored in the database and
the relationships among them. It defines the structure of the entire database and is independent
of physical storage.
External Level (View Level): This is the highest level that involves how users interact with the
data. It defines multiple user views, each customized for different users or applications based on
their specific needs.
3. Discuss the building blocks of Data Models and type of relationship cardinality.
Data Models are abstract models that describe how data is stored, connected, accessed, and
manipulated. Key building blocks include:
Entities: An entity represents an object that exists in the real world, such as a person, place, or
thing.
Attributes: Attributes are characteristics or properties of an entity, like a person’s name or age.
Relationships: Relationships define how entities interact with each other, like a customer
purchasing a product.
Types of Relationship Cardinality:
One-to-One (1:1): Each entity in one set is associated with a maximum of one entity in another
set (e.g., a person has one passport).
One-to-Many (1:N): One entity in a set can be associated with multiple entities in another set
(e.g., a mother can have multiple children).
Many-to-One (N:1): Many entities in a set are associated with one entity in another set (e.g.,
multiple students enrolled in one course).
Many-to-Many (M:N): Entities in both sets can have multiple associations with each other (e.g.,
students and courses, where students can enroll in multiple courses and each course can have
multiple students).
4. Describe relational databases and explain the key concepts of relational databases.
A Relational Database is a type of database that stores data in tables (relations) where each
table consists of rows and columns. The structure is organized, making it efficient for data
retrieval, updating, and manipulation through SQL.
Key Concepts:
Tables: Collections of data organized into rows and columns. Each table represents an entity
type (e.g., "Employees").
Rows (Records): Each row represents a single record in a table, containing data for each
attribute defined by the columns.
Columns (Fields): Columns define attributes of the entity, such as "Name" or "Age" in the
Employees table.
Primary Key: A unique identifier for each row in a table, ensuring that each record can be
uniquely identified.
Foreign Key: A field that links one table to another, creating relationships between tables.
Normalization: The process of organizing data to reduce redundancy and improve data integrity
by dividing tables into smaller, related tables.
Relational databases follow principles such as data integrity, normalization, and enforcing
relationships, which enable consistent, efficient data storage and retrieval.
5. Explain transaction management.
Transaction Management in DBMS refers to the process of managing a sequence of operations
performed as a single unit of work. A transaction ensures that operations are completed in a
way that maintains database integrity, even in the presence of errors, power failures, or other
interruptions.
Key Properties of Transactions (ACID):
Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction
is aborted and the database is left unchanged.
Consistency: Guarantees that a transaction brings the database from one valid state to another,
maintaining data integrity.
Isolation: Ensures that the operations of one transaction are isolated from others, preventing
conflicts.
Durability: Ensures that the results of a transaction are permanently saved, even if the system
crashes.
Transaction management is crucial in multi-user databases where multiple users might be
performing operations simultaneously. DBMS handles this through mechanisms like locking,
logging, and recovery.
6. Summarize Database Architecture and discuss its components in detail.
Database Architecture refers to the design and structure of a database system. The architecture
defines how data is stored, accessed, and managed. A common database architecture model is
the three-tier architecture:
1. External Level: Also known as the user view level, it consists of multiple views created for
different users based on their requirements. It represents how data is presented to the users
and allows customization and security.
2. Conceptual Level: This middle level is the community view of the database. It defines the
structure of the whole database for a community of users, specifying what data is stored, the
relationships between data, and the constraints. This layer provides a logical view of the data
independent of how it is stored.
3. Internal Level: This is the physical storage level, describing how data is stored in the
database. It deals with the data storage structure and access methods, using data structures
like indexes to manage storage and retrieval.
7. Explain Relationships and types of Relationships in Data Model.
Relationships in data models represent the associations between entities. The main types of
relationships in databases are:
One-to-One (1:1): Each record in one entity is related to only one record in another entity (e.g.,
each employee has a unique employee ID).
One-to-Many (1:N): One entity can relate to multiple records in another entity (e.g., a
department has multiple employees).
Many-to-One (N:1): Many records in one entity are associated with a single record in another
entity (e.g., many students enrolled in one course).
Many-to-Many (M:N): Records in both entities can have multiple associations with each other
(e.g., students and courses where students enroll in multiple courses and each course has
multiple students).
8. Summarize the evolution of Data Models and their impact on database design.
Data models have evolved to address changing needs in data management:
Hierarchical Model: One of the earliest models, organizing data in a tree-like structure. Suitable
for simple, fixed applications but lacks flexibility.
Network Model: An improvement over the hierarchical model, allowing more complex
relationships. However, it was still rigid and hard to manage.
Relational Model: Introduced by E.F. Codd, it structures data in tables and enables more
flexibility, with SQL as a standard language for management.
Object-Oriented Model: Integrates object-oriented programming principles, handling complex
data types and relationships.
NoSQL and Big Data Models: Developed for unstructured and semi-structured data, supporting
large-scale distributed data processing.
Each model influenced database design by improving data management flexibility, query
optimization, and storage efficiency, adapting to different application needs.
9. Identify the components of a relational schema and provide examples.
A Relational Schema defines the structure of a relational database. Key components include:
Tables (Relations): Each table represents an entity set (e.g., Customers table).
Attributes (Columns): Columns in tables represent characteristics of the entity (e.g.,
CustomerID, Name, Address).
Primary Key: A unique identifier for each row in a table (e.g., CustomerID).
Foreign Key: A field that creates a relationship between two tables (e.g., OrderID in Orders table
linking to CustomerID in Customers table).
Constraints: Rules that ensure data integrity (e.g., NOT NULL, UNIQUE constraints).
Example: The schema for a "Customers" table might look like this:
Customers(CustomerID PRIMARY KEY, Name, Address, Phone)
10. Explain different types of attributes with examples.
Attributes are characteristics or properties of entities. Types include:
Simple Attribute: Cannot be divided further (e.g., Age).
Composite Attribute: Can be divided into sub-parts (e.g., Full Name can be split into First Name
and Last Name).
Single-Valued Attribute: Holds a single value for each entity (e.g., Social Security Number).
Multi-Valued Attribute: Can hold multiple values for an entity (e.g., Phone Numbers for a
contact).
Derived Attribute: Can be derived from other attributes (e.g., Age can be derived from Date of
Birth).
11. Explain the evolution of data models.
Data models evolved to meet the growing complexity and scale of data management
requirements:
Hierarchical and Network Models: Early models for organizing data with limited flexibility.
Relational Model: Brought in a table-based structure, simplifying data management.
Object-Oriented Model: Introduced handling of complex data types and multimedia data.
NoSQL Models: Addressed the need for unstructured data management, scalability, and
high-speed processing for big data applications.
12. Explain the component of E-R Model and also explain the notation of ER diagram.
An Entity-Relationship (ER) Model is a diagrammatic approach for modeling relationships
between entities. Key components include:
Entity: Represented by rectangles, entities are objects or concepts (e.g., Customer).
Attribute: Represented by ovals, attributes are properties of entities (e.g., Name).
Relationship: Represented by diamonds, relationships link entities (e.g., "buys" relationship
between Customer and Product).
Primary Key: Unique identifier for each entity instance, underlined in the diagram.
In an ER diagram, symbols like rectangles, ovals, and diamonds help visualize the structure of
the database and the connections between entities.
13. Illustrate mapping cardinalities and provide examples.
Mapping cardinalities define the number of relationships between entities:
One-to-One (1:1): Each entity in set A is associated with one entity in set B (e.g., one employee
has one ID).
One-to-Many (1:N): An entity in set A can relate to multiple entities in set B (e.g., a department
has multiple employees).
Many-to-One (N:1): Multiple entities in set A relate to a single entity in set B (e.g., multiple
employees report to one manager).
Many-to-Many (M:N): Entities in both sets can relate to multiple entities in the other set (e.g.,
students and courses).
14. Explain different types of keys in DBMS with examples.
In a Database Management System (DBMS), keys are attributes or sets of attributes that help
uniquely identify records within a table. Different types of keys include:
Primary Key: A unique identifier for each record in a table. Example: CustomerID in a
Customers table.
Candidate Key: An attribute or set of attributes that can uniquely identify a record. Every table
can have multiple candidate keys, and one of them is chosen as the primary key. Example: Both
CustomerID and Email might serve as candidate keys in a Customers table.
Super Key: A set of attributes that uniquely identifies each record in a table. Every primary key
is a super key, but not all super keys are primary keys.
Alternate Key: A candidate key that was not chosen as the primary key. Example: If CustomerID
is the primary key, then Email could be an alternate key.
Foreign Key: An attribute in one table that links to the primary key of another table, establishing
relationships between tables. Example: OrderID in the Orders table linking to CustomerID in the
Customers table.
15. Discuss Dr. E.F. Codd’s Rules and their relevance in modern databases.
Dr. E.F. Codd introduced a set of 12 rules (often expanded to 13) to define what a relational
database should satisfy to be considered fully relational. Some of the key rules include:
0.Foundation Rule - A system should be a relational database and use a relational model.
1.Information Rule - All data should be stored in tables.
2.Guaranteed Access - Every piece of data must be accessible via a table.
3.Systematic Treatment of NULL - NULL values should represent missing data uniformly.
4.Dynamic Online Catalog - Metadata (data about data) should be accessible like regular data.
5.Comprehensive Data Sub-language Rule - Databases should support a standard language
(like SQL).
6. View Updating Rule - Views should be updatable like tables.
7.High-Level Insert, Update, Delete - Users should manipulate data in bulk, not just one row at a
time.
8.Physical Data Independence - Changes to data storage should not affect database
operations.
9.Logical Data Independence - Changes to logical structure shouldn't disrupt applications.
10.Integrity Independence - Constraints should be part of the data model.
11.Distribution Independence - Database distribution across locations shouldn't affect
operations.
12.Non-Subversion Rule - There should be no workaround that bypasses the relational model.
These rules ensure data integrity, independence, and structure in relational databases, forming
the foundation for modern relational DBMSs like MySQL, PostgreSQL, and Oracle.
16. Explain Weak and Strong Entities with a diagram.
Strong Entity: An entity that has a primary key and can exist independently without relying on
another entity. Example: Employee with a unique EmployeeID.
Weak Entity: An entity that does not have a primary key and relies on a strong entity for its
identification. Weak entities are often identified by a composite key that includes the primary key
of the strong entity. Example: Dependent entity in an HR database, where Dependent relies on
EmployeeID from the Employee entity.
Diagram:
Employee ─── (EmployeeID) ─── Dependent
|
(DependentID)
17. Explain the functional dependency and types of functional dependencies.
A functional dependency describes a relationship between attributes in which one attribute
uniquely determines another attribute. If A and B are attributes of a relation R, then B is
functionally dependent on A (written A → B) if each value of A is associated with exactly one
value of B.
Types of functional dependencies:
Trivial Functional Dependency: If B is a subset of A, then A → B is a trivial dependency (e.g.,
EmployeeID, Name → EmployeeID).
Non-Trivial Functional Dependency: If B is not a subset of A, then A → B is non-trivial (e.g.,
EmployeeID → Name).
Transitive Dependency: If A → B and B → C, then A → C (e.g., EmployeeID → Department →
Manager).
Partial Dependency: Exists in composite keys, where an attribute is dependent on part of the
primary key (e.g., OrderID, ProductID → Quantity, where Quantity depends on both OrderID and
ProductID).
18. Define the term "Normalization" and explain the types of normalization.
Normalization is a process of organizing data in a database to reduce redundancy and improve
data integrity. The goal is to break down large tables into smaller ones and define relationships
between them to avoid anomalies.
Types of Normal Forms:
First Normal Form (1NF): Ensures each attribute contains only atomic (indivisible) values and
that each record has a unique identifier.
Second Normal Form (2NF): Achieved when 1NF is met and all non-key attributes are fully
functionally dependent on the primary key.
Third Normal Form (3NF): Achieved when 2NF is met and there is no transitive dependency
between non-key attributes.
Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a
candidate key.
Fourth Normal Form (4NF): Ensures no multi-valued dependencies except for trivial cases.
19. Discuss the components of Structured Query Language (SQL).
SQL (Structured Query Language) is the standard language used to interact with relational
databases. Key components include:
DDL (Data Definition Language): Commands that define the structure of the database, like
CREATE, ALTER, DROP.
DML (Data Manipulation Language): Commands that manage data within tables, like INSERT,
UPDATE, DELETE.
DCL (Data Control Language): Commands that control access to the data, like GRANT,
REVOKE.
TCL (Transaction Control Language): Commands that manage transactions in the database,
like COMMIT, ROLLBACK, SAVEPOINT.
20. Discuss the DDL, DML, DCL command in Structured Query Language (SQL).
DDL (Data Definition Language): Used to define and modify database structure.
CREATE: Creates a new database object, like a table or view.
ALTER: Modifies an existing database object.
DROP: Deletes a database object.
DML (Data Manipulation Language): Used to manipulate data within tables.
INSERT: Adds new data.
UPDATE: Modifies existing data.
DELETE: Removes data.
DCL (Data Control Language): Manages permissions.
GRANT: Gives user access privileges.
REVOKE: Removes user access privileges.
21. Explain the difference between:
Drop vs Delete: DROP removes the structure (e.g., table or database), while DELETE removes
data within the table.
Truncate vs Alter: TRUNCATE quickly deletes all records without logging each row individually,
while ALTER modifies the structure of an existing table.
Order by vs Group by: ORDER BY sorts the result set, while GROUP BY groups records by one
or more columns for aggregation functions.
22. Explain aggregate functions and triggers with examples.
Aggregate functions in SQL perform calculations on a set of values and return a single result.
Common aggregate functions include:
COUNT: Counts the number of rows that match a specified criterion.
Example: SELECT COUNT(*) FROM Orders WHERE Status = 'Delivered';
SUM: Calculates the total sum of a numeric column.
Example: SELECT SUM(Amount) FROM Sales WHERE Region = 'West';
AVG: Calculates the average value of a numeric column.
Example: SELECT AVG(Salary) FROM Employees;
MIN and MAX: Find the minimum and maximum values in a column.
Example: SELECT MIN(Salary), MAX(Salary) FROM Employees;
Triggers are stored procedures in a database that automatically execute in response to certain
events on a table, such as INSERT, UPDATE, or DELETE.
Example: Suppose we want to automatically update an Inventory table whenever a new order is
placed. We can create a trigger on the Orders table to update Inventory after each insertion.
CREATE TRIGGER UpdateInventory
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
UPDATE Inventory SET Quantity = Quantity - NEW.QuantityOrdered WHERE ProductID =
NEW.ProductID;
END;
23. Define the view and explain type of view and compare views and tables.
A view is a virtual table in SQL created by a query that selects data from one or more tables. It
does not store data itself; instead, it provides a way to look at data in tables.
Types of Views:
Simple View: Created from a single table without using group functions.
Complex View: Created from multiple tables and may contain group functions and joins.
Views vs. Tables:
Storage: Tables store data physically, while views are virtual and do not store data.
Data Modification: Data in tables can be directly modified, while views often restrict
modifications, especially if they are based on multiple tables.
Performance: Views may be slower as they execute queries each time they are accessed,
whereas tables provide direct access to data.
24. Discuss the join and explain the type of join.
A join is used to combine rows from two or more tables based on a related column between
them. Types of joins include:
Inner Join: Returns records that have matching values in both tables.
Example: SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID;
Left (Outer) Join: Returns all records from the left table and the matched records from the right
table. Unmatched rows in the right table result in NULL values.
Example: SELECT * FROM Customers LEFT JOIN Orders ON Customers.CustomerID =
Orders.CustomerID;
Right (Outer) Join: Returns all records from the right table and the matched records from the left
table.
Example: SELECT * FROM Customers RIGHT JOIN Orders ON Customers.CustomerID =
Orders.CustomerID;
Full (Outer) Join: Returns all records when there is a match in either left or right table.
Example: SELECT * FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID;
Cross Join: Returns the Cartesian product of both tables (all possible combinations).
Example: SELECT * FROM Customers CROSS JOIN Orders;
25. Compare between file structure, hashing, and indexing.
File Structure: Refers to the way data is organized in storage. It defines how data is accessed
and stored in physical storage (e.g., sequential, indexed, or hashed file structures).
Hashing: A technique used to map data to a location in a hash table for fast retrieval. In a
hashed file structure, a hash function generates an address for each key, allowing for efficient
searching.
Indexing: A data structure that improves data retrieval speed. Indexes are pointers to data and
are often created on columns to optimize queries.
Comparison:
Efficiency: Hashing is efficient for exact matches, while indexing is suitable for range queries.
Use Cases: Hashing is used in search applications, whereas indexing is used in databases to
speed up data retrieval.
Data Access: Hashing provides constant-time access, whereas indexing may require traversal,
especially for range-based queries.
26. Explain ACID Properties in DBMS.
ACID properties ensure reliable processing of database transactions:
Atomicity: Ensures that all operations within a transaction are completed; if one part fails, the
entire transaction fails, and changes are rolled back.
Consistency: Guarantees that a transaction will bring the database from one valid state to
another, maintaining all predefined rules.
Isolation: Ensures that transactions occur independently without interference, even if multiple
transactions are processed concurrently.
Durability: Guarantees that once a transaction is committed, it will remain permanent even in
case of a system failure.
ACID properties are essential for data integrity and reliability in DBMSs, especially for
applications requiring robust transactional support.
27. Explain the states of a transaction.
A transaction in a database can be in one of several states:
Active: The transaction is currently being executed.
Partially Committed: The transaction has completed its final operation but is not yet fully
committed.
Committed: The transaction has successfully completed all operations and changes have been
made permanent.
Failed: The transaction could not proceed due to an error.
Aborted: The transaction is rolled back, and all changes made are undone.
Terminated: The transaction has completed, either successfully or unsuccessfully.
28. Discuss schedule and compare serial schedule, non-serial schedule, and serializable.
Schedule: A schedule is a sequence of transactions' operations (read, write) as they occur in
the database.
Serial Schedule: Transactions are executed one after the other without interleaving. It ensures
consistency but is often inefficient.
Non-Serial Schedule: Transactions are interleaved, meaning operations of different transactions
are executed in parallel. This is faster but may lead to conflicts.
Serializable: A schedule is serializable if it produces the same result as a serial schedule.
Serializable schedules ensure database consistency while allowing concurrency.
Comparison:
Efficiency: Non-serial schedules are faster as they allow parallel execution.
Consistency: Serial and serializable schedules ensure consistency, while non-serial may lead to
inconsistencies if not properly managed.
Use Cases: Serializable schedules are preferred for systems requiring both efficiency and data
integrity.
29. Explain deadlock with example.
A deadlock occurs when two or more transactions are waiting for each other to release locks on
resources, resulting in a standstill.
Example:
Transaction T1 holds a lock on Resource A and waits for Resource B.
Transaction T2 holds a lock on Resource B and waits for Resource A.
Neither T1 nor T2 can proceed, leading to a deadlock. Deadlocks are resolved using techniques
like deadlock prevention (avoiding circular waiting), deadlock detection (identifying and resolving
deadlocks), and deadlock recovery (aborting one of the transactions).
30. Explain the comparison operator and pattern matching, between and in operator with
example.
Comparison Operators are used to compare values:
Examples: =, !=, >, <, >=, <=
Example usage: SELECT * FROM Employees WHERE Salary > 50000;
Pattern Matching is used to search for specific patterns in a string. In SQL, the LIKE operator is
used with wildcard characters:
% represents zero, one, or multiple characters.
_ represents a single character.
Example: SELECT * FROM Customers WHERE Name LIKE 'J%'; (finds names starting with 'J').
BETWEEN Operator is used to filter results within a range:
Example: SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND
'2023-12-31';
IN Operator allows filtering results that match any value in a list:
Example: SELECT * FROM Employees WHERE Department IN ('HR', 'Sales');
