Skip to content

Database Systems

SWE · Midterm · Summer 26

Paper 2 of 2By Rubieat Haque RudroSwitch

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 Faculty of Science & Information Technology Department of Software Engineering Midterm Examination, Summer 2026 Course Code: SE 223, Course Title: Database Systems Sections & Teachers: NJN(A, C, H), TRT(B, D, G), KRA(E), MKH(F, I, J), ZNM(K), FT(L) Time: 1:30 Hrs | Marks: 25 Answer ALL Questions 1. [CLO-1, Level-4] [Figure: A relational database schema diagram showing Customer, Category, Order, OrderDetail, and Product relations with their respective attributes, primary keys, and foreign keys.] a) Consider the following scenario: An online retail company currently manages its business data, including Customer records, Orders, Order Details, Products, and Product Categories, using a traditional File Processing System (FPS). Each department (Sales, Inventory, and Billing) maintains its own separate files. For example, the Sales department stores customer and order data in a flat file, the Inventory department keeps product and category details in a separate spreadsheet file, and the Billing department duplicates customer and order information in yet another file for invoice generation. Analyze this scenario and identify any three major problems that would arise from managing this retail data using a File Processing System. [Marks-3] b) Using the database schema as your scenario, explain the THREE levels of data abstraction. Justify how each level hides complexity from the level above it. [Marks-4] c) Identify which database architecture is most appropriate for this schema and provide a detailed justification. [Marks-3] 2. [CLO-2, Level-4] a) Illustrate a complete Entity-Relationship (ER) Diagram based on the provided schema image. [Marks-3] b) Using the schema, figure out the relational algebra expressions for each of the following operations: [Marks-7] i. Retrieve all products with a price greater than 500. ii. Display only the FirstName, LastName, and Email of all customers. iii. Retrieve order information along with corresponding customer details. iv. Join OrderDetail and Product tables where the quantity ordered is greater than 2. v. List all ProductIDs that appear in OrderDetail OR that belong to a specific category. vi. Find CustomerIDs that have placed at least two different orders on the same date. vii. Find all CustomerIDs who have registered but have NOT yet placed any order. 3. [CLO-3, Level-3] a) Show the Candidate key and Foreign key types with reference to the schema diagram. [Marks-2] b) Complete the following SQL operations. You must write and execute each SQL statement: [Marks-3] i. Create the Product and Category tables with appropriate data types and key constraints as defined in the schema. ii. Insert at least FOUR rows of realistic sample data into each table. iii. Add a new column StockQuantity (integer, NOT NULL, default 0) to the Product table using an ALTER TABLE statement.