Skip to content

Database Systems

SWE · Final · Summer 26

Paper 2 of 2By Puza BiswasSwitch

Paper 2 of 2

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 Software Engineering Final Examination, Summer 2026 Course Code: SE 223; Course Title: Database System Sections & Teachers: A, C, H(NJN), B, D, G(TRT), E(KRA), F, I, J(MKH), K(ZNM), L(FT) Time: 2: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) Table 1: Participants participant_id | participant_name | email | city | registration_date 101 | Alice Johnson | alice@gmail.com | New York | 2025-07-01 102 | Brian Smith | brian@gmail.com | Chicago | 2025-07-03 103 | Catherine Lea | catherine@gmail.com | Boston | 2025-07-05 104 | David Wilson | david@gmail.com | Seattle | 2025-07-08 105 | Emma Brown | emma@gmail.com | Miami | 2025-07-10 Table 2: Event_Registration registration_id | participant_id | event_name | registration_fee | payment_status 201 | 101 | AI Conference | 150.00 | Paid 202 | 102 | Cyber Security Workshop | 100.00 | Pending 203 | 103 | Data Science Summit | 200.00 | Paid 204 | 104 | Cloud Computing Expo | 180.00 | Paid 205 | 105 | Web Development Bootcamp | 120.00 | Pending [Marks-10] [CLO-3 Level-3] Solve SQL queries for the given question: a. Find all participants who live in New York. b. Find all participants whose names start with the letter 'A'. c. Find all event registrations where the registration fee is between 100 and 180. d. Display participant names and their event names for participants who have Paid their registration fee. e. Find the names of participants who registered for an event with a fee greater than 150. (Subquery) f. Display participant name, city, event name, and payment status by joining both tables. g. Find the total number of participants registered for each event where the number of participants is more than 1. h. Calculate the maximum registration fee for each payment status where the maximum fee is greater than 150. i. Find the participants who paid more than the average registration fee. (Subquery) j. Find the participant details who registered for the "AI Conference" event using a subquery. 2. Consider the following tables: Topic: Hospital Appointment System Patient_ID | Patient_Name | Phone | Doctor_ID | Doctor_Name | Specialty | Appointment_Date | Tests | Test_Result P101 | John Smith | 01710000001 | D201, D202 | Dr. Rahman, Dr. Karim | Cardiology, Neurology | 2026-01-10, 2026-01-15 | Blood Test, MRI | Normal, Clear P102 | Maria Juias | 01710000002 | D201 | Dr. Rahman | Cardiology | 2026-01-12 | ECG | Normal P103 | David Lee | 01710000003 | D203, D202 | Dr. Ahmed, Dr. Karim | Orthopedics, Neurology | 2026-01-18, 2026-01-20 | X-Ray, MRI | Fracture, Clear a) Analyze the given table and normalize it from 1NF to 3NF, examining every step for the tables given in question 2, with a proper explanation. [Marks-10] [CLO-4 Level-4] b) Using the given Hospital Appointment System database, explain and differentiate between partial dependency and transitive dependency with suitable examples from the tables. [Marks-5] [CLO-4 Level-4] c) Identify anomalies that may exist in the unnormalized table and explain with proper justification based on Question 2. [Marks-5] [CLO-4 Level-4] 3. a) Illustrate an Entity-Relationship (ER) Diagram for the normalized hospital appointment system from Question 2. with appropriate attribute types, cardinality constraints, and relationships between entities. [Marks-5] [CLO-2 Level-4] 4. a) Transaction #9749521 was transferring $500 from Account "Alice" to "Bob". Due to a system crash, the transaction did not complete. TRANSACTION LOG (Before Crash) LSN | Time | Transaction ID | Log Record | Details 10 | 10:15:01 | 9749521 | <START> | Transaction 9749521 started 11 | 10:15:02 | 9749521 | <UPD*T6> | Alice balance: 1000 -> 500 12 | 10:15:03 | 9749521 | <WRITE> | Alice balance written to disk 13 | 10:15:04 | 9749521 | <UPDATE> | Bob balance: 2000 -> 2500 - | --:--:-- | - | - | CRASH OCCURRED BEFORE WRITE & COMMIT 14 | --:--:-- | 9749521 | <COMMIT> | NOT WRITTEN (Crash) Describe the current state of the transaction and explain which action the system should take after recovery. [Marks-3] [CLO-5 Level-2] b) [Figure: Diagram showing account A with Before $30, Debit $10, Available $20, transferring via process to account B with Before $100, Credit $10, Available $100, resulting in Debited Successfully and Credit Failure] Describe which ACID properties is violated in the given scenario, and explain how to solve the issue. [Marks-2] [CLO-5 Level-2]