Matoshri Education Society's

Matoshri College of Engineering and Research Centre, Eklahare, Nashik

Department of Artificial Intelligence and Data Science

Assignment -1
Course: Database Management Systems Class: T.E A. Y. 2025-2026

Code: 310241 Semester- I

Subject Incharge: Mrs. K. S. Mali

……………………………………………………………………………………………………….
Course Outcomes (CO)
CO1: Analyze and Design database management systemusing ER Model.
CO2: Implement database queries using database languages.
……………………………………………………………………………………………………….
1 2 3 4 5 6

Bloom’
Que Unit s Level
Statement of Question CO PI
No. No
1 to 6

PART A

1 Draw the neat diagram of Database System Structure and 1 1 CO1

explain its components in detail.
2 Construct an ER Diagram for Company having following 1 3 CO1
details :
• Company organized into DEPARTMENT. Each department
has
unique name and a particular employee who manages the
department.
Start date for the manager is recorded. Department may have
several
locations.
• A department controls a number of PROJECT. Projects have a
unique name, number and a single location.
• Company’s EMPLOYEE name, ssno, address, salary, sex and
birth
date are recorded. An employee is assigned to one department,
but
may work for several projects (not necessarily controlled by her
dept). Number of hours/week an employee works on each
project
is recorded; The immediate supervisor for the employee.
• Employee’s DEPENDENT are tracked for health insurance
purposes
(dependent name, birthdate, relationship to employee).
Identify the relationship among the entities along with the
 mapping cardinalities, keys in the E.R. diagram.
3 List the main characteristics of the database approach and 1 1 CO1
explain how it differs from the traditional file system.
A post office has few postmen who go every day to distribute CO1
letter. Every morning post office receives a large number of
registered letters. The post office intends to create a database to
keep track of these letters.
i) Every letter has a sender, an origin post office from where it
was sent, a destination post office to which it is to be sent, a
date of registration, date of arrival at destination post office,
receiver and a status.
ii) Every sender has a name, an address.
iii) Every receiver has a name and an address.
iv) Every postman has a designated area where he delivers
letters.
4 v) The area consists of a set of streets under the jurisdiction of 1 3
the post office.
vi) Every street consists of a set of buildings.
vii) Every building has number and may be name. It may be
housing more than one family.
viii) The status of the letter can be not yet taken for delivery,
delivered, address not available, address not known, addressee
did not accept the letter, redirected to the address of address and
sent to the sender.
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.
For the database system to be usable, it must retrieve data CO1
[Link] need of efficiency has led designers to use complex
5 data structures to represent data in the database. Developers hide 1 1,2
this complexity from the database system users through several
levels of abstraction. Explain those levels of abstraction in detail
with example..
Draw an ER diagram for the banking system. Assume the CO1
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.
6 Accounts can be held by more than one customer and a 1 3
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
Explain the concept of candidate key and primary key, foreign CO1
key. Identify above listed key for the following schema:
7 Person (driver_id, name, address, contactno) 1 1
Car(licence, model, year)
Owns (driver_id, licence)
PART B
 CO1,
1 What is view and how to create it? Can you update view? If yes, 2 1
CO2
how? If not, why not?
CO1,
2 Defined stored procedure. Explain the creating and calling stored 2 1
CO2
procedure with example.
Consider following schema.
CO1,
3 Student_fee_details (rollno, name, fee_deposited, date) 2 2
CO2
Write a trigger to preserve old values of student fee details
before updating in the table.
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) CO1,
4 for those having quantity less than 157.
2 3
CO2
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.
CO1,
5 What is an index? What are the advantages and disadvantages of 2 1
CO2
using index on a table?
CO1,
6 What is a trigger? How to create it? Discuss various types of 2 1
CO2
triggers
Consider the following schemas
Emp(Emp_no, Emp_name, Dept_no)
Dept(Dept_no, Dept_name)
Address(Dept_name, Dept_location)
Write SQL queries for the following CO1,
7 i) Display the location of department where employee ‘Ram’ is
2 3
CO2
working.
ii) Create a view to store total no of employees working in each
department in ascending order.
iii) Find the name of the department in which no employee is
working.
Consider following schema
CO1,
8 Student_fee_details (rollno, name, fee_deposited, date) 2 3
CO2
Write a trigger to preserve old values of student fee details
before updating in the table.
CO1,
9 Explain the concept of Referential and Entity Integrity 2 1
CO2
constraint with example.
Write a PL/SQL block of code which accepts the rollno from user.
CO1,
10 The attendance of rollno entered by user will be checked in 2 3
CO2
student_attendance(RollNo, Attendance) table and display on the
screen.
11 What is the importance of creating constraints on the table? 2 1 CO1,
Explain with example any 4 constraints that can be specified
 CO2
when a database table is created
Consider following schema
Hotels(hotel_no,hotel_name.city) CO1,
12 Rooms(Room_no,hotel_no,price,type)
2 3
CO2
Write a PL/SQL procedure to list the price & type of all rooms
at the hotel ‘TAJ’
Consider the following relation schema
MOVIES(Mov_Id, Mov_Title,Mov_Year, Dir_Id)
DIRECTOR(Dir_Id, Dir_Name)
RATING(MOV_Id, Rev_Stars)
Write the SQL queries for the following. CO1,
13 i) List the title of all the movies directed by ‘RAJ KAPOOR’
2 3
CO2
ii) Find the name of movies and number of stars for each
movie. Sort the results on movies title and from higher stars to
least stars.
iii) Assign the rating of all movies directed by ‘Steven
Spielberg’ to 9.
CO1,
14 What is synonym? How to create and use synonym in SQL?
2 1
CO2

Bloom’s Level of Question-- Remember: 1, Understand:2, Apply:3,Analysis: 4,

Evaluate:5,Create:6