Skip to content

Database Systems

SWE · Final · Spring 26

Paper 2 of 4By Md Siam HossainSwitch

Paper 2 of 4

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, 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 [Marks-10] [CLO-3 Level-3] 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. 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-4 Level-4] 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 [Marks-2] Explain the current normalization level of the given table. 3. 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-2 Level-4] 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-5 Level-2] 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]