Database Systems
SWE · Quiz · Summer 26
Paper 2 of 5Section 45 A · Rubieat Haque Rudro (1)Switch
Section 45 A
Paper 2 of 5
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
Quiz 2 (MISSING QUIZ), Summer 2026, Section: 45 A
Course Code: SE223; Course Title: Database System
Marks- 15
35 min
Question 1:
A university library management system maintains information about librarians and members. Each librarian has a Librarian ID, First Name, Last Name, Designation, and Phone Numbers. A librarian may have multiple phone numbers.Each member has a Member ID, Name, Date of Birth, Email, and Address. The address consists of House No., Street, City, and Postal Code.A member may borrow books multiple times. For each borrowing transaction, the library records a Borrow No, Borrow Date, and Due Date. A borrowing transaction cannot exist without a member. The Borrow No is unique only within a particular member. Books are managed by librarians. A librarian can manage many books, and a book can be managed by many librarians. For each management activity, the system stores the Activity Date and Remarks.
A. Draw the ER Diagram for the above scenario, including all types of attributes. (5 Marks)
B. Perform the following Relational Algebra Operations: (6 Marks)
Librarian(LibrarianID, Name, Designation)
Member(MemberID, Name, DOB, HouseNo, Street, City, PostalCode)
Borrow(MemberID, BorrowNo, BorrowDate, DueDate)
Management(LibrarianID, BookID, ActivityDate, Remarks)
Book(BookID, Title, Category)
a) Retrieve the names and designations of librarians whose designation is 'Senior Librarian'.
b) Find the member IDs and names of members living in Dhaka.
c) Identify the names of librarians who managed books.
d) Retrieve the names of members who have never borrowed any book.
C. Write SQL Queries for the Following Cases: (4 Marks)
a) Create the Member table declaring MemberID (e.g., MB-101) as the Primary Key.
b) Add a new column "FineAmount" to the Borrow table declaring it as NOT NULL
c) Rename the Borrow table as BookIssue.
d) Declare MemberID as a Foreign Key in the Borrow table.