Skip to content

Database Systems

SWE · Midterm · Spring 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.

Daffodil International University Department of Software Engineering Faculty of Science & Information Technology Midterm Examination, Spring 2024 Course Code: SE 223; Course Title: Database systems Sections & Teachers: All Time: 1:30-3:00PM Date: 20-03-2024 Marks: 25 Answer ALL Questions 1. A Video Rental Company has several branches throughout Bangladesh. Each branch has the branch address made up of a branch number, street, city, state, and zip code, and the telephone number. Each branch is allocated staff or Manager. The Manager or staff is responsible for the day-to-day running of a given branch. Each staff member is given a staff number, name, position, and salary. Each branch has a stock of videos. The data held on a video is the catalog number, video number, title, category, daily rental, cost, status, and the names of the main actors, and the director. However, in most cases, there are several copies of each video at a branch, and the individual copies are identified using the video number. The status indicates whether a specific copy of a video is available for rent. Before hiring a video from the company, a customer must first register as a member of a local branch. The data held on a member is the member number, first and last name, address, and the date that the member registered at a branch. Once registered, a member is free to rent videos, up to a maximum of ten at any time. The data held on each video rented is the rental number, the full name and number of the member, the video number, title, and daily rental, and the dates the video is rented out and date returned. The rental number is unique throughout the company. [CLO-1, Level-2] a) Describe relational database and tables for the above system. [Marks-2] b) Explain data abstraction for the above mentioned system [Marks-3] c) Distinguish between schema & instance for the above management system. [Marks-3] d) Clarify at least four functions of database administrator. [Marks-2] 2. a) Consider the question 1's scenario. Generate an ERD identifying their entity, attributes & relationship. [Marks-4] [CLO-2, Level-3] b) Table: Customer | Table: Order Cust omer_id | Customer_name | Address | Order_id | Order_date | amount | Custome r_id 101 | Rumman | Savar | 111 | 2002-02-22 | 2520 Tk | 103 102 | Anikondi | Dhanmondi | 112 | 2019-02-09 | 13478 Tk | 104 103 | Polash | Tejgaon | 113 | 2022-12-06 | 521 Tk | 102 104 | Suhaon | Agargaon | 114 | 2019-10-09 | 9200 Tk | 411 105 | Mikel | Dohar | 115 | 2017-07-29 | 1440 Tk | 600 106 | Mikae | Farmgate | 116 | 2022-09-12 | 520 Tk | 105 From this schema, choose candidate key, primary key, alternate key, foreign key, and Composite key with explanation. [Marks- 2] c) Consider the relation for Question b. Solve relational algebra for the following questions. [Marks-4] a. Find all customer name who yet not give any order, b. Find the customer name who gives the order on 2017-07-29. c. Find the customer information who lives in Savar. d. Find all customer IDs who are ordered. 3. Student | Course Student Name | Course Id | Title | Term | CourseId | Course Fee | Dept Name Shahin | 121 | Structured Programming | T1-L2 | 121 | 10000 | SWE 111 | 8000 | CIS Rupa | 122 | Structured Programming Lab | T1-L2 | 311 | 12000 | SWE 224 | 5000 | SWE Jui | 311 | Design pattern | T2-L3 | 124 | 4500 | BBA Miraj | 223 | Database | T2-L2 Bina | 333 | Null | Null [CLO-3, Level-3] a) Show the results of inner join, full outer join for the table Student and Course [Marks-2] b) Solve SQL command to express each of the following queries: [Marks-3] I. Create those two tables using constraints II. Insert data into those dates. III. Add a new column credit in the course table using the alter command.