Database Systems
SWE · Final · Fall 24
Paper 2 of 2By Kawsher HRidoySwitch
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
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]