Database Systems
SWE · Final · Fall 24
Paper 1 of 2Section AllSwitch
Section All
Paper 1 of 2
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
Final Examination, Fall 2024
Course Code: SE 223; Course Title: Database Systems
Sections & Teachers: All
Time: 1.30 Hrs
Marks: 25
Answer ALL Questions
[The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.]
Scenario 1: Library Management System:
A public library has several branches across different cities in Bangladesh. Each branch is identified by a unique branch number, along with its street, city, state, zip code, and phone number. The daily operations of the Branch are managed by a librarian or a team of staff each assigned a staff number, name, position, and salary. Each branch maintains a collection of books. Information stored for each book includes a catalog number, book number, title, genre, daily rental fee, cost, availability status, and the names of the authors and publisher. Multiple copies of the same book can be found at a branch, and individual copies are distinguished using the book number. Before borrowing a book, individuals must register as library members by providing their member number, first and last name, address, and the date they joined the library. Once registered, a member can borrow up to five books at a time. For each borrowed book, details such as rental number, member name and number, book number, title, rental fee, and the dates borrowed and returned are recorded. A unique rental number tracks all transactions across the library network.
1. a) Identify schema for the above mentioned scenario 1. [Marks-4] CLO-1 Level-4
b) Analyze the three levels of abstraction for the library management system [Marks-3]
c) Examine the key responsibilities of a Database Administrator in ensuring data integrity and consistency when managing in the company's database? [Marks-3]
2. a) Illustrate an Entity Relationship Diagram identifying their entity, attributes & relationship using scenario 1. [Marks-4] CLO-2 Level-4
b) From this scenario 1, point out candidate key, primary key, alternate key, foreign key, and Composite key with explanation. [Marks-2]
c) Consider the following Schema and Solve with Relational Algebra for the following questions. [Marks-4]
Branch(branch_number, street, city, state, zip_code, phone_number)
Staff(staff_number, name, position, salary, branch_number)
Video(catalog_number, video_number, title, category, daily_rental, cost, status, branch_number)
Member(member_number, first_name, last_name, address, registration_date, branch_number)
a) Retrieve the street and zip code for all branches that have a branch number greater than 'B050'
b) Find out the first names and last names of members who are registered at branches with a branch number 'B101' and 'B102'.
c) Retrieve the title and category of all videos that have a daily rental fee greater than 30.
d) Find all members who registered in the year 2023.
3. [Figure: Table of Employee and Project data]
Emp_ID | Emp_name | Emp_address
E101 | Alif | Mirpur
E102 | Raihan | Uttara
E103 | Rani | Mirpur
E104 | Mila | Savar
E105 | Alamin | Uttara
E106 | Maisha | Savar
E107 | Azmi | Farmgate
Employee
Project_ID | Emp_ID
P1 | E103
P2 | E104
P3 | E105
P4 | E103
P5 | E104
P6 | E107
Project
Solve SQL command to express each of the following queries: [Marks-5] CLO-3 Level-3
I. Create those two tables using key constraints
II. Insert data into above two tables.
III. Add a new column Emp_salary in the employee table using the alter command and Project_start_date in the Project table.
IV. Delete the column Emp_address from the employee table.
V. Rename the column name Emp_ID as Employee_Id from the employee table.