Skip to content

Database Systems

SWE · Final · Fall 24

Paper 1 of 2Section AllSwitch

Section All

Paper 1 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 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.