Skip to content

Database Systems

SWE · Final · Fall 24

Paper 2 of 2By Kawsher HRidoySwitch

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 Department of Software Engineering Faculty of Science & Information Technology Final Examination, Fall 2024 Course Code: SE 223; Course Title: Database Systems Sections & Teachers: All 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. Consider the following Schema and Solve with Relational Algebra for the following questions. [Marks-5] [CLO-2, Level-4] Customer: (CustomerID, Name, Email, Phone) Order: (OrderID, CustomerID, OrderDate, TotalAmount) Product: (ProductID, Name, Price, Stock) OrderDetails: (OrderID, ProductID, Quantity) a. Retrieve the names of customers who placed an order on '2024-11-18'. b. List all products with stock less than 10. c. Find the total number of orders placed by each customer. d. List all customers who ordered the product named "Laptop". e. Retrieve the details of customers who have placed orders totaling more than $1,000. 2. Develop SQL queries for the given question and follow the schema on question 1: [Marks-6] [CLO-3, Level-3] a) i) Retrieve the average of the total amount for all orders. ii) Find all customers who placed an order with a TotalAmount greater than $500. iii) Retrieve all products whose price is higher than the average product price. iv) Find customers who placed more orders than the average number of orders per customer. v) Retrieve the details of orders placed on the same date as the highest total order. vi) Get the name of the customer who placed the largest order by total amount. 2 b) Consider the following 2 tables find the output of Inner, Left, Right and Full outer join [Marks-4] [CLO-3, Level-3] flightNo | from | to | distance | departs | aircraftId 101 | New York | Los Angeles | 4500 | 10:00 AM | A1 102 | Chicago | Miami | 2000 | 2:00 PM | A2 103 | Dallas | Seattle | 3000 | 6:00 PM | A2 104 | Boston | Houston | 2600 | 8:30 AM | A6 aircraftId | airName | range A1 | Boeing 737 | 5000 A2 | Airbus A320 | 4500 A3 | Embraer E175 | 3500 A4 | Bombardier CRJ | 4000 3. Consider the following Information Hospital Management System Data PatientID: 021222000123456789 PatientName: John Doe DepartmentName: Cardiology DepartmentNo: 10 PatientEmail: johndoe@mail.com PatientPhoneNo: 01589955454 VisitID: V001 VisitDate: 20-11-24 NextVisitDate: 04-12-24 Prescription: MedicineID: M001 MedicineName: Aspirin Dosage: 75 mg daily MedicineManufacturer: Bayer MedicineID: M002 MedicineName: Atorvastatin Dosage: 10 mg nightly MedicineManufacturer: Pfizer DoctorID | DoctorName | DoctorSpecialization | DoctorPhoneNo D001 | Dr. Jane Smith | Cardiologist | 01712233445 D001 | Dr. Jane Smith | Cardiologist | 01712233445 a) Normalize the table from 1NF up to BCNF; examining every step of the process from those tables given in question 3 with proper explanation. [Marks-8] [CLO-4, Level-4] b) List the normalization rules and explain why we need normalization to design databases. [Marks-4] [CLO-4, Level-4] c) Distinguish between full functional dependency and partial dependency and transitive dependency from the above tables. [Marks-4] [CLO-4, Level-4] d) Establish an Entity relationship diagram based on the normalized tables with appropriate attribute type, cardinality, and relationship. [Marks-4] [CLO-4, Level-4] 4. Imagine a Banking System where a user is transferring funds from Account A to Account B. The transaction consists of several steps: 1. Check Account A for sufficient funds. 2. Debit Account A by the transfer amount. 3. Credit Account B with the transfer amount. 4. Commit the transaction if all steps succeed; otherwise, rollback if any steps fails. a) Explain the states of the transaction from scenario given in question 4. [Marks-3] [CLO-5, Level-2] b) Define each component of ACID properties, and how they ensure reliability, integrity, and concurrency control in database transactions from scenario given in question 4. [Marks-2] [CLO-5, Level-2]