Skip to content

Database Systems

SWE · Midterm · Spring 26

Paper 1 of 4By Md. Nahid Hasan DishanSwitch

Paper 1 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 Department of Software Engineering Faculty of Science & Information Technology Midterm 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: 1:30 Hrs Marks: 25 Answer ALL Questions 1. A Public Library Network operates multiple branches across India. Each branch includes details such as branch ID, location (comprising street, city, district, and postal code), and contact phone number. Branches are managed by librarians or assistants. The librarians or assistants handle daily operations at their assigned branch. Each, employee receives an employee ID, full name, role, and monthly salary. Every branch maintains a collection of books. Book details include ISBN, copy ID, title, genre, borrowing fee per day, purchase price, availability status, and the names of the primary authors and publisher. Typically, branches have multiple copies of popular books, distinguished by the copy ID. The status shows if a particular copy is available for borrowing. To borrow books, patrons must register as members at a local branch. Member information consists of member ID, first and last name, residential address, and registration date. Registered members can borrow up to five books simultaneously. Borrowing records include borrow ID, member's full name and ID, book copy ID, title, daily fee, borrow date, and return date. The borrower ID is unique across the entire network. [CLO-1, Level-4] a) Figure out the relational database and design the necessary tables for the above Library Management System. [Marks-2] b) Explain the concept of data abstraction in the context of this library system, including the three levels. [Marks-3] c) Analyze the given scenario and determine the most appropriate application architecture (2-tier or 3-tier) for the system with a proper explanation. [Marks-3] d) Examine the functions of a Database Administrator (DBA) by breaking down at least four key responsibilities involved in maintaining a database system. [Marks-2] 2. a) Based on the library scenario described, [Marks-4] Illustrate an Entity-Relationship Diagram (ERD). Clearly identify entities, their attributes, and the relationships between them (including cardinality and participation constraints). b) Table: Buyer | Table: Purchase [Marks-2] [CLO-2, Level-4] Buyer_ID | Buyer_Name | Location | Email | Purchase_ID | Purchase_Date | Amount | B_ID 201 | Aarav | Mumbai | ab@gmail.com | 301 | 2023-05-15 | 1500 | 202 202 | Priya | Delhi | lk@gmail.com | 302 | 2021-11-20 | 3200 | 204 203 | Rohan | Bangalore | bd@gmail.com | 303 | 2024-01-10 | 800 | 201 204 | Neha | Chennai | ac@gmail.com | 304 | 2020-08-05 | 4500 | 201 From this schema, identify and explain: Candidate keys, Alternate keys, and provide justifications for each selection. c) Analyze the Buyer and Purchase tables from Question 2(b) and formulate appropriate relational algebra expressions to derive the required query results. [Marks-4] a. Retrieve the names of all buyers who have not made any purchases yet. b. Find the buyer's name who made a purchase on 2023-09-25. c. Display all information for buyers located in Delhi. d. List all Buyer_IDs that appear in the Purchase table. a) Using the table from Question 2 (b) [Marks-2] [CLO-3, Level-3] Demonstrate the results of Inner Join and Full Outer Join between Buyer and Purchase table, and show the resulting tables with all relevant columns. b) Apply appropriate SQL queries to retrieve the required results using the Buyer and Purchase tables from Question 2(b). [Marks-3] A. Write SQL commands to create both tables, incorporating appropriate constraints. B. Write SQL commands to insert the provided sample data into both tables. C. Use the ALTER TABLE command to add a new column named "Phone_number" (with data type VARCHAR(20)) to the Buyer table.