Database Systems
SWE · Final · Summer 26
Paper 2 of 2By Puza BiswasSwitch
Paper 2 of 2
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]