Database Systems
SWE · Final · Spring 26
Paper 3 of 4By Md. Nahid Hasan DishanSwitch
Paper 3 of 4
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, Spring 2026
Course Code: SE 223; Course Title: Database System
Sections & Teachers: NJ (A), SS (B), TRT (C, D, K), KRA (E, F), CP (G, H), AUA (I), AR (J)
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) Customer
Customer_ID | Customer_Name | City | Country
C001 | Ahmed Khan | Dhaka | Bangladesh
C002 | Sarah Johnson | London | UK
C003 | Maria Garcia | Madrid | Spain
C004 | Chen Wei | Beijing | China
C005 | Priya Sharma | Mumbai | India
Orders
Order_ID | Customer_ID | Order_Date | Amount | Status
0101 | C001 | 2024-03-15 | 2500.00 | Completed
0102 | C003 | 2024-03-18 | 1800.50 | Pending
0103 | C001 | 2024-04-02 | 3200.75 | Completed
0104 | C005 | 2024-04-12 | 950.00 | Cancelled
0105 | C002 | 2024-04-12 | 5600.00 | Completed
Solve SQL queries for the given question:
a. Retrieve the total order amount for each customer.
b. Find all customers who have placed more than 2 orders.
c. Find all customers who have placed orders.
d. Count the number of orders by status category.
e. Get the details of customers who are from "Dhaka" city.
f. Find all orders that do not have a "Completed" status.
g. Get the customer information whose name contains "a" (case insensitive).
h. Find the order IDs of those orders that were placed between 15th March 2024 and 12th April 2024.
i. List all orders with customer details (show Order_ID, Order_Date, Amount, Customer_Name, and City).
j. Find the customer name whose total ordered amount is greater than any amount ordered in 2024-04-12.
[Marks-10] [CLO-3 Level-3]
2. Consider the following tables:
Topic: University Library System
Borrow_ID | Borrow_Date | Student_ID | Student_Name | Dept | Book_ID | Book_Title | Author | Price | Category
B01 | 3/10/2024 | S101 | Karim | CSE | L01, L02 | DBMS, OS | Smith, Ali | 45, 50 | Tech, Tech
B02 | 3/12/2024 | S102 | Rahim | EEE | L03 | Networks | Khan | 60 | Tech
B03 | 3/15/2024 | S101 | Karim | CSE | L04, L05 | AI, ML | Lee, Wang | 70, 80 | Tech, Tech
B04 | 3/18/2024 | S103 | Sara | BBA | L06 | Marketing | Roy | 40 | Biz
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-8] [CLO-Level]
b) Distinguish between transitive dependency and multivalued dependency. [Marks-2]
c) Outline the normalization rules and explain why we need normalization to design efficient databases based on question 2. [Marks-4]
d) Identify anomalies that may exist in the unnormalized table and explain with proper justification based on question 2. [Marks-4]
e) Project
Project_ID | Project_Name | Employee_ID | Employee_Name | Role | Hours_Worked
P01 | Website Dev | E01 | Ahmed | Developer | 40
Explain the current normalization level of the given table. [Marks-2]
3. a) Illustrate an Entity-Relationship (ER) Diagram for the normalized university library system from Question 2. with appropriate attribute types, cardinality constraints, and relationships between entities. [Marks-5] [CLO-Level]
4. a) Explain the transaction properties, and also elaborate on the three states of transaction execution (Active, Partially Committed, Aborted) based on the question Table 2 (Library System). [Marks-3] [CLO-Level]
b) Describe each component of ACID properties and explain how they ensure reliability, integrity, and concurrency control in database transactions based on the question Table 2 (Library System). [Marks-2]