Database Systems
SWE · Midterm · Fall 25
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
Midterm Examination, Fall 2025
Course Code: SE 223; Course Title: Database System
Sections & Teachers: NJ(A), TRT (B, C, E, P), CP(D, O), KRA(F, I, J), SS(G), KMH(H), MMSI(K, L), AR(M, N)
Time: 1:30 Hrs
Marks: 25
Answer ALL Questions
A "Restaurant Management System" is used to manage the operations of a restaurant. The system stores data in several tables. Among these, two tables are given below for study and analysis.
Table: Order
OrderID | ItemType | Date | TotalAmount | Cid
OR001 | Chinese | 11-10-24 | 5000 tk | C101
OR002 | Bangla | 10-12-24 | 4500 tk | C103
OR003 | Thai | 05-02-25 | 5500 tk | C101
Table: Customer
CID | CustomerName | City | Phone
C101 | Rafiq Ahmed | Dhaka | 0181111111
C102 | Sadia Rahman | Dhaka | 0182222222
C103 | Kamal Hossain | Chittagong | 0183333333
Consider the given tables and answer the following Questions 1, 2, and 3.
1. a) Using Scenario 1, Identify and describe the schema of the given tables and explain what an Entity is with an example from any one of the tables. [Marks-3] [CLO-1, Level-4]
b) Analyze the components required to design a 'Restaurant Management System' and explain how they work together. [Marks-2]
c) Explain the three levels of data abstraction for the System. [Marks-3]
d) Analyze which type of data independence (logical or physical) is most relevant when adding a new column, and justify your answer. [Marks-2]
2. a) Solve relational algebra to the following questions: [Marks-5] [CLO-2, Level-3]
a) Find the names and phone numbers of all customers located in Dhaka.
b) Display customer names along with their order items.
c) Find the customer name who ordered more than 5000tk or from Chittagong.
d) Find all customers who have never placed any order.
e) List all customers from Dhaka, and ordered Thai.
3. a) Show the results of Right Join, Full Outer Join for the given tables. [Marks-2] [CLO-3, Level-3]
b) Using question 3, Solve SQL commands to express each of the following queries: [Marks-3]
I. Create both tables with appropriate key attributes.
II. Add the FavouriteFoodType column to the Order table.
III. Insert the sample data provided earlier into both these tables.
4. QuickRide is a ride-sharing platform operating across major cities in Bangladesh, connecting passengers with drivers for convenient transportation. Each driver has a unique driver ID, name, phone number, email, and joining date. Drivers provide vehicle details, including registration number, model, vehicle type (Bike, Car, CNG), and can own multiple vehicles but use only one per trip. Passengers register with a passenger ID, name, phone number, email, and can save multiple locations, like home or office, with complete addresses. Each trip has a trip ID, pickup location, drop-off location, pickup time, drop-off time, distance, total fare, and payment method. After completing a trip, both passengers and drivers rate each other on a 1-5 star scale with optional comments and a timestamp. [Marks-5] [CLO-2, Level-4]
Illustrate an ERD for the QuickRide System, specifying entities, their attributes, and the relationships between them with appropriate cardinalities.