Database Systems
SWE · Midterm · Spring 24
Section All
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.