DBMS INSEM ANSWERS

Q. Draw an ER diagram for the banking system. Assume the banking requirements are as given
below.
• The bank is organized into branches. Each branch is located in a particular city.
• The bank offers two types of accounts: saving and current. Accounts can be held by more than one
customer and a customer can have more than one account.
• A loan originates at a particular branch and can be held by one or more customers
Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R.
diagram.
Construct appropriate tables for E-R diagram designed with above requirements
Q. For the database system to be usable, it must retrieve data efficiently.The need of
efficiency has led designers to use complex data structures to represent data in the database. Developers
hide this complexity from the database system users through several levels of abstraction. Explain those
levels of abstraction in detail with example.

Levels of Abstraction in Database Systems

 Definition:
Levels of abstraction in a database system hide the complex internal data structures from users, enabling efficient data retrieval
and ease of use.

 Three Levels of Abstraction:

a. Physical Level (Internal Level):

 Describes how data is stored physically on storage devices.

 Includes file organization methods, indexes, pointers, etc.

 Complex data structures like B-trees, hash indexes are used for efficient storage and retrieval.

 Example: Data stored as blocks on a hard disk with pointers to link related records.

b. Logical Level (Conceptual Level):

 Describes what data is stored and the relationships among those data, independent of physical details.

 Defines entire database structure such as tables, views, constraints, schemas.

 Database Designer works at this level.

 Example: A table "Student" with fields Student_ID, Name, Address, and Department.

c. View Level (External Level):

 Describes how data is viewed by individual users or user groups.

 Provides specific views of the database tailored to user requirements; hides irrelevant data.

 Ensures data security and privacy by restricting access.

 Example: A report showing only student names and departments for a particular course instructor.

 Purpose of Levels:
 a. Simplifies interaction for users by hiding data storage details.

b. Supports data independence:

 Physical Data Independence: Changes at physical level do not affect logical level.

 Logical Data Independence: Changes at logical level do not affect user views.

 Conclusion:

a. Efficiency in database systems is achieved by using complex data structures internally at the physical level, while
users interact through simpler abstracted views.

b. This layered architecture enhances usability, security, and flexibility of database management.

Q. Explain the concept of candidate key and primary key, foreign key. Identify above listed key for the
following schema:
Person (driver_id, name, address, contactno)
Car(licence, model, year)
Owns (driver_id, licence)

 Candidate Key:

o A minimal set of attributes that uniquely identifies each record in a table.

o It has no unnecessary attributes; every attribute is essential to maintain uniqueness.

o A table can have multiple candidate keys.

 Primary Key:

o A selected candidate key chosen to uniquely identify records in a table.

o It cannot have null values and must contain unique values.

o Each table can have only one primary key.

 Foreign Key:

o An attribute or set of attributes in one table that refers to the primary key of another table.

o It establishes a relationship between tables and maintains referential integrity.

Identification of keys in the given schema:

Relation Candidate Key(s) Primary Key Foreign Key(s)

Person driver_id driver_id None

Car licence licence None

Owns (driver_id, licence) (driver_id, licence) driver_id (references

Person.driver_id)
licence (references Car.licence)

 In Person, driver_id uniquely identifies each person; hence, it is candidate and primary key.

 In Car, licence uniquely identifies each car; hence, it is candidate and primary key.

 In Owns, the combination of driver_id and licence uniquely identifies each ownership record (composite candidate key
and primary key).

 driver_id in Owns is a foreign key referencing driver_id in Person.

 licence in Owns is a foreign key referencing licence in Car.

Q. Draw architecture of DBMS system and explain function of following components:

i) Storage manager
ii) Query Processor

Architecture of DBMS System

Functions of Components:

i) Storage Manager:

 Manages the interaction between the DBMS and physical storage (disk).

 Handles data storage, retrieval, and update operations.

 Responsible for efficient file organization, indexing, buffering, and caching.

 Ensures data integrity and management of transactions on stored data.

 Hides the complexity of physical data storage from higher layers.

ii) Query Processor:

  Translates and executes user queries (written in high-level query languages like SQL).

 Components include:

o Parser: Checks query syntax and semantics.

o Query Optimizer: Determines the most efficient execution plan.

o Execution Engine: Executes the optimized query plan.

 Converts user requests into low-level instructions understood by the storage manager.

 Ensures efficient data retrieval according to query conditions.

This architecture separates user interaction from physical data handling, enabling data abstraction, efficiency, and
security within DBMS.

Q.Define DBMS. Explain advantages of DBMS over file system.

 DBMS (Database Management System) Definition:

A DBMS is a software system that enables users to define, create, maintain, and control access to databases efficiently and
securely.

 Advantages of DBMS over File System:

Advantages of DBMS File System Limitations

Data Redundancy Control: DBMS minimizes data duplication by File systems often lead to data redundancy and inconsistency due to
integrating data into a single database. separate files for similar data.

Data Integrity: DBMS enforces constraints and rules to maintain data File systems lack mechanisms to enforce data integrity, increasing errors.
accuracy and consistency.

Data Security: DBMS provides user authentication and authorization File systems have limited security controls, making data vulnerable.
controls, securing data access.

Concurrent Access Support: DBMS manages multi-user access without File systems generally do not support concurrent access properly, leading
data conflicts via locking and transactions. to data corruption.

Data Independence: DBMS allows logical and physical data File systems tightly couple data format to applications, making changes
independence, reducing application impact from data changes. difficult.

Efficient Data Access: DBMS uses indexes, query optimization, and File systems rely on sequential file processing, which is less efficient.
sophisticated storage techniques for fast retrieval.
 Backup and Recovery: DBMS provides automated backup and recovery File systems usually require manual backup and recovery processes.
facilities to protect data.

Data Sharing: DBMS supports data sharing among multiple users and Sharing data among users/applications in file systems is complex and
applications easily. error-prone.

 Conclusion:
DBMS provides a structured, secure, and efficient environment for data management with features not available in traditional
file systems, making it essential for modern data handling needs.

Q. Different components of database management systems like query processor, storage manager,
transaction manager etc. are functional for processing the query submitted by user. Explain the functions of
each component in view of getting query output.
 Query Processor:

o Parses and interprets the user's query (e.g., SQL).

o Checks syntax and semantics of the query.

o Optimizes the query to find the most efficient execution plan.

o Converts the high-level query into low-level instructions for data retrieval.

 Storage Manager:

o Manages physical data storage and retrieval on disk.

o Handles file organization, indexing, buffering, and caching.

o Executes the low-level data operations as per the query processor's instructions.

o Ensures data consistency and integrity during access.

 Transaction Manager:

o Controls the execution of transactions to ensure ACID properties (Atomicity, Consistency, Isolation, Durability).

o Manages concurrency control to allow multiple queries to execute concurrently without errors.

o Handles commit and rollback operations in case of transaction success or failure.

o Ensures database recovery in case of system failure.

  Buffer Manager (part of Storage Manager):

o Manages main memory buffer to hold data pages read from disk.

o Reduces disk I/O by caching data pages for efficient access during query processing.

 Authorization Manager:

o Controls user access rights and permissions on the database objects.

o Enforces security policies during query execution to prevent unauthorized data access.

 Recovery Manager:

o Maintains logs and backup information to restore the database to a consistent state after crashes or failures.

o Works with transaction manager for undo and redo operations.

 Catalog Manager:

o Maintains metadata about database objects (schemas, tables, columns, indexes).

o Provides schema details to the query processor during query compilation and optimization.

Conclusion:
All these components work collaboratively to process user queries efficiently, securely, and reliably, ensuring correct
query outputs while maintaining database integrity and performance.

Q. Explain with example what is physical data independence. Also

explain its importance.
 Physical Data Independence Definition:
It is the ability to change the physical schema (how data is stored on hardware) without affecting the logical schema (how data
is viewed by users or applications).

 Example:
Suppose a database initially stores data in a sequential file. If the storage is changed to indexed files or a different file
organization technique for efficiency, the change is done at the physical level. This does not affect the logical view or the way
users query the data.

 Importance of Physical Data Independence:

 o Improves System Flexibility: Changes to storage structures, indexing methods, or hardware can be made
without impacting users or applications.

o Reduces Maintenance Effort: Database administrators can optimize performance and storage without rewriting
application programs.

o Supports Efficient Data Retrieval: Allows the use of more efficient storage mechanisms or data structures while
keeping user interactions stable.

o Ensures Application Stability: Applications remain unaffected by low-level changes, ensuring continuity and less
disruption.

 Conclusion:
Physical data independence is a crucial feature of DBMS that enables seamless evolution of database storage techniques
without affecting higher-level abstractions or user queries, thus enhancing robustness and manageability of database systems.

Q. What is view and how to create it? Can you update view? If yes, how? If not, why not?

 View Definition:
A view is a virtual table in a database that is derived from a query on one or more base tables. It does not store data physically
but provides a way to look at data from the underlying tables.

 How to Create a View:

Use the CREATE VIEW statement with a SELECT query.
Example:

CREATE VIEW View_Name AS

SELECT column1, column2
FROM Table_Name
WHERE condition;

 Can a View be Updated?

o Yes, a view can be updated if it is a simple view that:

 Is based on a single base table

 Does not include GROUP BY, DISTINCT, aggregate functions, joins, or set operations

 Includes all NOT NULL columns of the base table (or they have default values)

o Updates to such views propagate to the underlying base table.

  How to Update a View?
Use standard DML commands like INSERT, UPDATE, or DELETE on the view.
Example:

UPDATE View_Name
SET column1 = value
WHERE condition;

 Why Some Views Are Not Updatable:

o Views based on multiple tables, joins, aggregates, or complex queries cannot be updated directly because the
DBMS cannot unambiguously determine how to apply the changes to underlying tables.

o Such views are read-only by nature.

 Conclusion:
Views provide abstraction and security by presenting customized data. Updatable views allow modifications under certain
restrictions, while complex views ensure data integrity by being read-only.

Q. Define stored procedure. Explain the creating and calling stored procedure with example.

 Stored Procedure Definition:

A stored procedure is a precompiled set of one or more SQL statements stored in the database. It can be executed repeatedly
by the database engine, helping to encapsulate business logic, reduce network traffic, and improve performance.

 Creating a Stored Procedure:

Use the CREATE PROCEDURE statement followed by the procedure name and SQL statements it contains.
Example:

CREATE PROCEDURE GetEmployeeDetails

@EmployeeID INT
AS
BEGIN
SELECT * FROM Employee WHERE EmployeeID = @EmployeeID;
END;

 Calling (Executing ) a Stored Procedure:

Use the EXEC or EXECUTE statement followed by the procedure name and required parameters.
Example:

EXEC GetEmployeeDetails @EmployeeID = 101;

 Summary:
 o Stored procedures improve modularity and reusability of SQL code.

o They are stored in the database and executed by invoking their names.

o Support input parameters to customize execution based on user needs.

Q. Consider following schema.

Student_fee_details (rollno, name, fee_deposited, date)
Write a trigger to preserve old values of student fee details before updating in the table.

Definition:
A trigger is a procedural code automatically executed in response to certain events (INSERT, UPDATE, DELETE) on a
table.

SQL Example (BEFORE UPDATE Trigger):

CREATE TABLE Student_fee_audit (

rollno INT,
name VARCHAR(50),
fee_deposited DECIMAL(10,2),
date DATE,
action_date TIMESTAMP
);

CREATE TRIGGER preserve_old_fee_details

BEFORE UPDATE ON Student_fee_details
FOR EACH ROW
BEGIN
INSERT INTO Student_fee_audit (rollno, name, fee_deposited, date, action_date)
VALUES (OLD.rollno, OLD.name, OLD.fee_deposited, OLD.date, NOW());
END;

Explanation:

 Audit Table (Student_ fee_audit)

o Stores old values before update.

o Additional column action_date stores the timestamp of when the update occurred.

 Trigger Details:

o BEFORE UPDATE: Executes before an update on Student_fee_details.

 o FOR EACH ROW: Fires for every row updated.

o OLD keyword: Refers to the existing values before modification.

 Functionality:

o Whenever fee details are updated, the existing record is copied into the audit table before changes are made.

o This ensures a history log is preserved for reference or rollback.

Q. Consider the following schemes

Supplier(SNO, Sname, Status, City)
Parts (PNO, Pname, Color, Weight, City)
Shipments(SNO,PNO,QTY)
Write SQL queries for the following:
i) Find shipment information (SNO, Sname, PNO, Pname, QTY)for those having quantity less than 157.
ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of parts whose quantity is larger than the
average quantity
iii) Find aggregate quantity of PNO 1692 of color green for which shipments made by supplier number who residing
Mumbai

Given Schema:

 Supplier(SNO, Sname, Status, City)

 Parts(PNO, Pname, Color, Weight, City)

 Shipments(SNO, PNO, QT Y )

i) Shipment information (SNO, Sname, PNO, Pname, QT Y ) with quantity < 157
SELECT s.SNO, s.Sname, p.PNO, p.Pname, sh.QTY
FROM Supplier s
JOIN Shipments sh ON s.SNO = sh.SNO
JOIN Parts p ON sh.PNO = p.PNO
WHERE sh.QTY < 157;

ii) SNO, Sname, PNO, Pname for suppliers who shipped parts with quantity > average quantity
SELECT s.SNO, s.Sname, p.PNO, p.Pname
FROM Supplier s
JOIN Shipments sh ON s.SNO = sh.SNO
JOIN Parts p ON sh.PNO = p.PNO
WHERE sh.QTY > (SELECT AVG(QTY) FROM Shipments);

iii) Aggregate quantity of part no. 1692, color 'green', shipped by suppliers residing in 'Mumbai'

SELECT SUM(sh.QTY) AS Total_Quantity

FROM Supplier s
JOIN Shipments sh ON s.SNO = sh.SNO
JOIN Parts p ON sh.PNO = p.PNO
WHERE p.PNO = 1692
AND p.Color = 'green'
AND s.City = 'Mumbai';

Q. What is a trigger? How to create it? Discuss various types of triggers.

 Trigger Definition:
A trigger is a procedural code automatically executed (fired) by the database in response to certain events (such as INSERT,
UPDATE, DELETE) on a specified table or view. It is used to enforce business rules, maintain audit trails, and ensure data
integrity.

 How to Create a Trigger:

Use the CREATE TRIGGER statement specifying:

o Trigger name

o Timing of execution (BEFORE or AFTER the event)

o Event that fires the trigger (INSERT, UPDATE, DELETE)

o Target table

o Trigger body containing the procedural SQL code to execute

 Example:

CREATE TRIGGER trigger_name

BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic (e.g., insert old values into audit table)
END;

 Types of Triggers:
Trigger Type Description

BEFORE Trigger Executes before the triggering event occurs. Used for validation or
modification before data change.

AFTER Trigger Executes after the triggering event is completed. Used for auditing or
cascading changes.

INSTEAD OF Trigger Executes instead of the triggering event, typically used on views to allow
updates.

Row-Level Trigger Executes once for each affected row in the triggering event.

Statement-Level Trigger Executes once per triggering SQL statement, regardless of the number of
rows affected.

 Summary:

o Triggers automate responses to data changes.

o Timing and scope (row or statement) define the trigger behavior.

o Types help to enforce rules, maintain consistency, and track data changes efficiently.