Skip to content

Database Management System

CSE · Final · Summer 25

Batch 65

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

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 Final Examination, Summer 2025 Course Code: CSE311, Course Title: Database Management System Level: 3 Term: 2 Batch: 65 Time: 02:00 Hrs Marks: 40 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 retail company maintains the following table to track customer orders. Over time, they noticed data redundancy and inconsistencies, especially when updating customer or product information. C_ID | C_Name | phone | OrderID | OrderDate | ProductID | ProductName C001 | Alice Ahmed | 11111 | O1001 | 2025-07-01 | P101, P102 | Mouse, Keyboard C003 | Nadia Hasan | 33333 | O1003 | 2025-07-03 | P103 | HDMI Cable Apply the principles of database normalization to systematically transform the table step by step into Third Normal Form (3NF). Clearly demonstrate each step (1NF, 2NF, 3NF) by identifying anomalies. [Marks: 6, CO1] b) Explain the concepts of Distributed Systems and Data Warehousing. How do they work together to manage and analyze large-scale data? Provide an example. [Marks: 4, CO1] 2 a) doctors(doctor_id, name, department) treatments(treatment_id, treatment_name, doctor_id) patient_visits(visit_id, treatment_id, patient_name, status, visit_date) Based on the given Schemas, construct the SQL queries to answer the following questions: [2*5] i) List all treatments along with the name of the doctor who provides each treatment. ii) Display each doctor's name and the number of unique patients they have treated. Order the results by the number of patients in descending order. iii) Display each doctor's name and the number of unique patients they have treated. Order the results by the number of patients in descending order. iv) List doctors who have treated more than 5 patients, showing their name, department, and the total number of patient visits. v) List all patient visits where the treatment name contains "therapy", the visit status is "completed", and the doctor's department is "Cardiology". Show the patient name, treatment name, doctor name, visit date, and status. [Marks: 10, CO2] b) Based on the provided Q2(a) schemas, answer the following questions: [6+4] i) Construct a stored procedure named addPatientVisitWithTransaction that accepts treatment_id, patient_name, status, and visit_date as parameters. The procedure validate that status is either 'Completed' or 'Cancelled'. Insert the visit record into patient_visits. Use a transaction to ensure the insert is committed only if validation passes; otherwise rollback with an error. ii) Construct a BEFORE INSERT trigger named preventDuplicatePatientVisit on the patient_visits table. The trigger should prevent the insertion of a new record if a patient already has an existing visit for the same treatment on the same visit date. If such a duplicate is detected, the trigger must raise an error with the message: "Duplicate patient visit not allowed for same treatment and visit date". [Marks: 10, CO2] 3 a) A multinational e-commerce company stores customer profiles, transaction history, and product inventory in a single centralized database. Recently, they faced: - Customers from Asia experience high latency when accessing their data. - Support staff in one region were found accessing payment details of customers from other regions. - Sensitive payment details were at risk of being exposed during internal data exchange. Analyze the database challenges faced by the multinational e-commerce company, including high latency, unauthorized access, and data exposure. Based on your analysis, suggest suitable data distribution techniques, access control mechanisms, and encryption methods to address these issues. [Marks: 5, CO3] b) Illustrate how the following two relational tables can be represented in any two different formats. Then, explain which NoSQL model-NoSQL or Relational-would be more suitable for this system. Relational Tables: - doctors(doctor_id, name, department) - treatments(treatment_id, treatment_name, doctor_id, cost) doctors: doctor_id | name | department D01 | Dr. Smith | Cardiology D02 | Dr. Adams | Neurology treatments: treatment_id | treatment_name | doctor_id | cost T101 | Heart Surgery | D01 | - T102 | ECG | D01 | - T103 | Brain Scan | D02 | - [Marks: 5, CO3]