Database Management System
ITM · Midterm · Fall 24
Section All
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.