Database Systems
SWE · Quiz · Summer 26
Paper 3 of 5Section 45 A · Rubieat Haque Rudro (2)Switch
Section 45 A
Paper 3 of 5
Read the questions as text
Read off the PDF by AI, so it may have mistakes. The PDF is the original.
Daffodil International University
Department of Software Engineering
Quiz 2, Summer 2026, Section: 45 A
Course Code: SE223; Course Title: Database System
Marks- 15
40 min
Question 1:
A hospital management system maintains information about doctors and patients.Each doctor has a Doctor ID,
First Name, Last Name, Specialization, and Phone Numbers. A doctor may have multiple phone numbers.
Each patient's information includes a Patient ID, Name, Date of birth, age and address. The address consists of
house no., Street, City, and Postal Code. A patient may be admitted to the hospital multiple times. For each
admission, the hospital records an admission number, admission date, and ward number. An admission cannot
exist without a patient, and the admission number is unique only within a particular patient. Doctors treat
patients. A doctor can treat many patients, and a patient can be treated by many doctors. For each treatment,
the hospital stores the treatment date and diagnosis.
a) Draw the ER diagram for the above scenario, including all types of attributes. (5 marks)
b) Perform the following Relational Algebra: (6 Marks )
• Doctor (DoctorID, Name, Specialization)
• Patient (PatientID, Name, DOB, HouseNo, Street, City, PostalCode)
• Admission (PatientID, AdmissionNo, AdmissionDate, WardNo)
• Treatment (DoctorID, PatientID, TreatmentDate, Diagnosis)
i) Retrieve the names and specialisations of doctors specialising in cardiology.
ii) Find the patient IDs and names of patients living in Dhaka.
iii) Identify the names of doctors who treated patients.
iv) Retrieve the names of patients who have never been admitted.
c) Write the SQL query for following Cases ( 4 Marks)
i) Create the Patient database Declaring the PatientID (ex: pt-101) as primary key
ii) Add a new column “Status” to the Treatment table
iii) Rename the Admission table with Appointment
iv) Declare Patient ID as Foreign Key in Treatment table