Computer Science and Engineering Department
Motillal Nehru National Institute of Technology Allahabad
B.Tech V Semester
DBMS Lab (CSN15401)
Assignment-2
1. Consider a scenario where a university employs different types of employees: professors,
administrative staff, and maintenance staff. All employees have common attributes like
emp_id, name, address, salary. Professors additionally have subjects taught, administrative
staff have department, and maintenance staff have shift. Draw the ER-diagram identifying
entities, attributes, and relationships for the above scenario and also apply generalization for
common attributes (if any).
2. A vehicle registration department handles information about vehicles. Each vehicle has a
registration number, model, and manufacturer. Vehicles can be cars, motorcycles, or trucks,
with additional specific attributes like car: number of doors, motorcycle: type (sports, cruiser,
etc.) and truck: payload capacity. Draw an ER-diagram using specialization concept
(wherever required) to show relationships and specific attributes for each specialized type.
3. A bank records information about customers, accounts, and loans. Customers can take loans
jointly. A loan involves multiple customers and is approved by a loan officer. Design an ER-
diagram showing how a loan approval involving multiple customers and an officer is modeled
using aggregation.
4. A hospital has staff members who can be Doctors, Nurses, or Technicians. Each staff has an
id, name, and salary. Doctors have a specialization and license number. Nurses have a grade
and technicians operate equipment in the hospital. Each doctor can be assigned to surgeries,
and each surgery is scheduled in an operation theatre and includes a team of doctors and
nurses. Design an ER-diagram to model staff using generalization or specialization and also
use aggregation to model the surgery team and operation theatre for the hospital.
