DBMS Question Bank -Unit Test -1
2025-2026
Unit-I & II
Sr. No Question
1 List five responsibilities of a database-management system. Draw and
explain the database system structure along with components with a
suitable diagram.
2 Consider following schema
student_fee_details(rollno,name,fee_deposited,date) write a trigger to
preserve the old values of student fee details before updating in the table.
3 Explain how the storage manager of the database system interacts with the
database system. Draw a suitable database structure and describe the role
of the following components of the storage manager:(Any 2 components)
1. Authorization and Integrity Manager
2. Transaction Manager
3. File Manager
4. Buffer Manager
4
Consider the following Schema and Write the SQL queries for the
following.
employee (person name, street, city ) works (person name, company name,
salary) company (company name, city) manages (person name, manager
name)
1. Find the names of all employees who live in the same city and on
the same street as do their managers.
2. Find the names of all employees in this database who do not work
for “First Bank Corporation”.
3. Find the names of all employees who earn more than every
employee of “Small Bank Corporation”.
4
What are the major components of a Database System Structure? Explain
with a neat diagram.
(Expected points: Components such as storage manager, query processor,
transaction manager, and their functions.)
5
Draw an ER diagram for an Online Library Management System. Clearly
indicate entities, attributes, relationships, and keys.
(Expected points: Entities like Book, Student, Librarian, Issue; attributes;
1:Many or Many:Many relationships; keys highlighted.)
6
Design an Entity Relationship Model for the following scenario,
considering the following requirements
1. Identification of entities, their attributes
2. Relationship along with the mapping cardinalities.
3. Design a generalization-specialization hierarchy for the vehicle.
4. Convert the ER model into tables and write the schema for the tables
along with the keys.
Construct an E-R diagram for a vehicle insurance company whose
customers own one or more vehicles each. Each vehicle has associated
with it zero to any number of recorded accidents. Each insurance policy
covers one or more vehicles and has one or more premium payments
associated with it. Each payment is for a particular period of time, and has
an associated due date, and the date when the payment was received
7 Consider the following MOVIE schema
MOVIES(movid,mtitile,movyear,dirid)
Director(dirid, dirname)
rating(movid, revstars)
Write SQL queries for the following
i. Retrieve all movies directed by ‘Raj Kapoor’.
ii. Find the names of movies and ratings for the movie. Sort the results
as per the movie title and from highest rating to lowest rating.
iii. Assign the rating of all movies directed by ‘Stevan’ to 9.
8 Consider the following schema
Hotels(hotelno,hotelname,city)
Rooms(roomno,hotelno,price,type)
Write a PL/SQL procedure to list the price and type of all rooms at the
hotel ‘Greenpark’.
An educational institute maintains a student table with the following
attributes:
(Rollno, fname, lname, cgpa, email, branch) .The institute frequently runs
queries to find students based on their Rollno and CGPA, and ensures that
each email is unique.
As a database designer, Identify indexes to be created on this table with
examples and justify the type of indexing.
10
Write an SQL query using JOINs and aggregate functions to display the
department-wise total salary of employees. Also explain the JOIN used.
11 1. Design an E.R diagram for the Bank Database. Bank has several
customers and branches located in different cities. Consider the
following entities and identify the relationship among the entities
along with the mapping cardinalities.
branch (branch name, branch city, assets)
customer (customer name, customer street, customer city)
loan (loan number, branch name, amount)
borrower (customer name, loan number)
account (account number, branch name, balance)
depositor (customer name, account number)
Document all the assumptions and keys in the E.R.
diagram.
12 Explain the concept of data independence and how it is achieved in a
DBMS architecture.
a. Logical Data Independence
b. Physical Data Independence
13
Consider the following database schema:
Physician(reg_no,name,tel_no,city)
Patient(p_name,street,city)
visit(p_name,reg_no,date_of_visit,fee)
write SQL queries for the following requirements.(any two)
A. Find the name ,city of patients who visited a physician on 15 July
2017.
B. Get the name of the physician and the total no. of patients who
visited him.
C. Get the details of the date wise fees collected at the clinic.
14 Write separate PL/SQL functions to insert, delete, update records from
student(roll, name, marks, dept_id, date_of_birth):
i. For insert- pass the values to be inserted to the function.
ii. For delete- pass the value for delete condition.
iii. For update- pass the values to be updated.
The student table must be displayed automatically after each operation.
15 Create a stored procedure that inserts records into the Products(product_id,
product_name, product_quantity, product_purchase_date) table. Terminate
from the stored procedure whenever a duplicate key error occurs. In
addition, display an error message.
16 Explain the use of cursor in PL/SQL with a suitable example.
17 Consider following schema: OR
account (acct_no, branch_name, balance)
Depositor (cust_name, acct_no)
borrower (cust_name, loan_no)
loan (loan_no, branch_name, amount)
Write following queries using SQL
i) Find names of all customers who have a loan at the ‘SBI branch’.
ii) Find all customers who are having an account and loan or both.
iii) Find average account balance at each branch.
iv) Find the names of customers having the highest loan in each branch.
18 Differentiate between cardinality mapping and participation constraints in
ER diagram.
19 How do you handle inheritance in ERDs? Explain with the concepts of
generalization and specialization.
20 Describe the process of converting an ERD into a relational database
schema with suitable ER diagram.
