Database Systems

Daffodil International University Faculty of Science & Information Technology Department of Software Engineering Midterm Examination, Fall 2026 Course Code: SE 223; Course Title: Database Systems Sections & Teachers: 46- SS(A),KRA(B,F,G),TRT(C,D,E),AR(H,I),NJN(J,K,O),IBN(L,M,N),RRB(N) Time: 1:30 Hrs Marks: 25 Answer ALL Questions 1 [CLO-1, Level-4] CarePulse is a digital healthcare platform that connects patients with doctors for online consultations and medicine deliveries. Patients register with a unique Patient_ID and provide their Name, Email, Phone_No, Date of Birth and Age. Doctors registered on the platform have a unique Doctor_ID, Doctor Name (First Name, Last Name), Specialization, Contact_No, and Consultation Fee. When a patient books an appointment, the system generates an Appointment_ID and records the Appointment_Date, Time_Slot, Consultation_Status, and Fee_Paid. Prescriptions issued by doctors store a unique Prescription_ID, Doctor_ID, Patient_ID, Prescription_Date, and Instructions, which list multiple prescribed medicines. Each medicine has a unique Medicine_ID, Medicine_Name, Manufacturer, and Unit_Price. During a high-traffic morning, the database server suddenly crashes, and the team discovers that several newly created prescription and appointment records were lost because an unauthorized staff member accidentally deleted table rows while attempting manual database cleanup. a) CarePulse is planning to improve its database system after a sudden server crash. Explain the major database components required for the effective operation of the CarePulse database system. [Marks-3] b) Will the server crash impact the logical level of the database, or will deleting the rows impact the physical level? Analyze it in terms of data independence. [Marks-4] c) Identify the key, composite, multivalued, and derived attributes for each entity in the CarePulse system if exists, providing suitable examples from the given scenario. [Marks-3] 2 a) Consider the Following Ticket booking system [Marks-5] [CLO-2, Level-4] Customer Customer_ID | Name | Phone_No | Address C101 | Sarah Ahmed | 01711000111 | Dhaka C102 | Tanvir Hossain | 01811000222 | Chittagong C103 | Ayesha Rahman | 01911000333 | Sylhet C104 | Rafiq Islam | 01511000444 | Khulna Ticket Ticket_ID | Customer_ID | Tech_ID | Status | Total_Cost T501 | C101 | T601 | Completed | 1500 T502 | C101 | T602 | In Progress | 3000 T503 | C101 | T603 | Pending | 800 T504 | C103 | T601 | Completed | 2200 i) Figure out the output for the following algebra expressions: A. π Customer_ID, Ticket_ID, Total_Cost (Customer ⨝ Ticket) B. π Customer_ID, Name, Ticket_ID, Status (Customer ⨝ Ticket) ii) Figure out the relational algebra expressions for each of the following cases: A. Find the tickets with prices over 1000 and which booking process is completed. B. Find the Customer Name and their ticket details. C. Find the Customers details who are from Dhaka and Khulna. b) Illustrate an Entity-Relationship (ER) diagram for the CarePulse healthcare platform showing all entities, their attributes, primary keys, and relationships with appropriate cardinalities. [Marks-5] 3 a) Complete the following SQL operations. You must write and execute each SQL statement (consider the CarePulse System): [Marks-3] [CLO-3, Level-3] i. Create Patient table with appropriate primary Key, data type and no attribute should contain any NULL value ii. Insert two relavant record in Patient table iv. Add Patient_NID column in appropriate table where it should contain unique value for each individual record. b) Show the Candidate Key, Unique Key and alternate key from patient and Doctor Table of CarePulse System [Marks-2]