Database Management System
CSE · Final · Summer 25
Batch 65
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]