Database Management System
CSE · Midterm · Spring 25
Paper 3 of 4Batch 64 · Robiul Karim JesanSwitch
Batch 64
Paper 3 of 4
Read the questions as text
Read off the PDF by AI, so it may have mistakes. The PDF is the original.
Daffodil International University
Faculty of Science & Information Technology
Department of Computer Science & Engineering
Midterm Examination, Spring 2025
Course Code: CSE311, Course Title: Database Management System
Level: 3 Term: 1 Batch: 64
Time: 1:30 Hrs
Marks: 25
Answer ALL Questions
[The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.]
1. a) A university's online grading system allows professors to update student grades, such as modifying a student's final exam score. The system must ensure that the grade update is either fully saved or not saved at all, maintaining data integrity. It must also ensure that the database remains valid without conflicting data, prevent interference if multiple professors are updating simultaneously, and guarantee that the updated grade persists even in the event of a system crash. Now a database Expert now Explain how each of the ACID properties (Atomicity, Consistency, Isolation, Durability) ensures the correctness and reliability of this grading system. | [3] | CO1
b) CityCare Hospital wants to maintain a database for managing patient records, doctor schedules, room assignments, and billing information. Each patient who visits the hospital is assigned a unique Patient ID, along with details such as Name, Age, Gender, Address, and Contact Number. Patients can book multiple appointments with different doctors, but each appointment is scheduled for only one doctor. Each appointment has a unique Appointment ID, along with details like Date, Time, and Status (Scheduled/Completed/Canceled). Each doctor has a unique Doctor ID, along with details such as Name, Specialization, Contact Number, and Department. A doctor can have multiple appointments in a day. When a patient arrives for an appointment, they may be assigned a room if hospitalization is required. Each room has a unique Room Number, along with details such as Type (ICU, General, Private, etc.), Status (Occupied/Vacant), and Daily Charges. A room can accommodate multiple patients over time, but at any given moment, it is assigned to only one patient.
For each patient visit, a bill is generated, including charges for doctor consultations, room stays (if applicable), and other medical services. Each bill has a unique Bill ID, along with details such as Total Amount, Payment Status (Paid/Pending), and Payment Date. A single bill is generated for one patient per appointment, but a patient can have multiple bills for different visits. The hospital administration wants a database system to manage this information efficiently.
Analyze the scenario and identify entities and their attributes from the given hospital management system scenario, determine the relationships between these entities along with their cardinalities, and draw an ER diagram representing the system | [6] | CO1
c) Construct the Schema Diagram based on the given Entity-Relationship (ER) Diagram. | [4] | CO1
[Figure: ER diagram showing entities STUDENT, COURSE, LECTURER, SUBJECTS, and their relationships: Attends, Teaches, Has, etc.]
2. Construct Relational Algebra to retrieve the required information from the given schema.
Customers (id, name, age)
Orders (order_id, customer_id, product, quantity)
I. Find customers who have placed more than one order.
II. Find customers who have never placed an order.
III. Find the products ordered by customers under the age of 25.
IV. Find customers who have ordered at least two different products. | [4] | CO1
3. a) Construct SQL queries to retrieve the required information from the given tables and schema.
Table A. Employees
id | name | salary | dept_id | manager_id | hire_date
1 | Rahim | 60000 | 1 | NULL | 2020-05-10
2 | Karim | 75000 | 1 | 1 | 2021-06-15
3 | Salma | 80000 | 2 | 1 | 2022-07-20
4 | Jamal | 75000 | 2 | 2 | 2023-08-10
Customers (id, name, age), Orders (order_id, customer_id, product, quantity), Exam_Results (student_id,nameh,score)
I. Retrieve employees who earn more than the average salary in their own department.
II. List customers who have placed an order for a product that has been ordered by more than one distinct customer.
III. Find the top three highest scoring students (including all tied scores at the third-highest level).
IV. For each product in the Orders table, display the product name and the difference between its total ordered quantity and the overall average total quantity ordered per product. | [8] | CO2