Database Systems
SWE · Midterm · Spring 26
Paper 3 of 4By Ovi BasuSwitch
Paper 3 of 4
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) [Marks-2] [CLO-2, Level-4]
Table: Buyer
Buyer ID | Buyer_Name | Location | Email
201 | Aarav | Mumbai | ab@gmail.com
202 | Priya | Delhi | lk@gmail.com
203 | Rohan | Bangalore | bd@gmail.com
204 | Neha | Chennai | ac@gmail.com
Table: Purchase
Purchase ID | Purchase_Date | Amount | B_ID
301 | 2023-05-15 | 1500 | 202
302 | 2021-11-20 | 3200 | 204
303 | 2024-01-10 | 800 | 201
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.
3. 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.