Skip to content

Database Management System

ITM · Midterm · Fall 24

Section All

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.

Department of Information Technology & Management Mid Examination, Fall 2024 Course Code: ITM 221; Course Title: Database Management 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] (CLO-1, Level-4) c) Examine the key responsibilities of a Database Administrator in ensuring data integrity and consistency when managing in the company's database? [Marks-3] (CLO-1, Level-4) 2. a) Define relational database [Marks-1] (CLO-2, Level-4) b) From this scenario 1, point out candidate key, primary key, alternate key, foreign key with explanation. [Marks-4] (CLO-2, Level-4) c) Consider the following Schema and Solve with Relational Algebra for the following questions. [Marks-5] (CLO-2, Level-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) Find staff name whose salary greater than 50000 b) Retrieve the street and zip code for all branches that have a branch number greater than 'B050' c) Find out the first names and last names of members who are registered at branches with a branch number 'B101' and 'B102'. d) Retrieve the title and category of all videos that have a daily rental fee greater than 30. e) Find all members who registered in the year 2023. 3. a) Solve SQL command to express each of the following queries: [Marks-5] I. Write two constraints with example II. Create one tables using key constraints III. Define data type with example IV. Add a new column Emp_salary in the employee table using the alter command and Project_start_date in the Project table.