QUESTION

05/11/2020
EAST WEST UNIVERSITY

Department of Computer Science and Engineering
B.Sc. in Computer Science and Engineering Program
Mid Term I Assessment (Online), Fall 2020 Semester
Course: CSE 302 Database Systems
Instructor: Mohammad Rezwanul Huq, PhD, Associate Professor, CSE Department
Full Marks: 40 (15 will be counted for final grading)
Time: 1 Hour and 30 Minutes (including answer uploading time)
Note: There are 5 (FIVE) questions, answer ALL of them. Course Outcome (CO), Cognitive Level and Mark
of each question are mentioned at the right margin.
1. Assume that you are the owner of a very popular online grocery store. Every day, [CO1, C3,
your store has thousands of transactions and therefore, inserting a lot of data into Mark: 5]
your database. Among this data, some are very sensitive which must not be
accessible by all employees of your store.
How can you provide this security by hiding some part of the database? Justify
your answer briefly by using relevant terms.
2. a) Consider the following database schema. [CO1, C3,

Mark: 6]
Hotel (hotelId, hotelName, hotelCity)
Room (roomNo, hotelId, type, price)
Guest (guestNo, guestName, guestAddr)
Booking (guestId, roomNo, hotelId, checkIn, checkOut)
The Booking relation contains booking information such as guest id, room number,
hotel id, check-in date and check-out date from the hotel. A guest is not allowed to
book the same hotel room with the same check-in date, but she can book the same
hotel room with different check-in date.
Draw the schema diagram of the given database. You must identify primary keys
and foreign keys appropriately.
b) Consider the following relations r and s. [CO1, C3,

Mark: 4]
Relation r Relation s
A B C D D E F
a1 b1 c1 d1 d1 e1 f1
a2 b1 c2 d3 d2 e1 f2
a3 b3 c2 d1 d4 e2 f2
a4 b3 c3 d2
Find the output of the following expression.

r ⟖ s (right outer join)
Page 1 of 4
05/11/2020
3. Consider the following Product relation. [CO2, C3,

Product (productID, productName, supplyDate, quantity, unitPrice) Mark: 5]
Write SQL Statements to perform the following operations.

i) Add a constraint specifying that the unit price of products must be
positive values.
ii) Update the unitPrice by increasing 10% of the current price for the
products having id = ‘P-101’ which was supplied on ’02-Nov-20’.
4. Formulate Relational algebra expressions for the following queries based on the [CO1, C3,
‘MyHome’ database schema as given in Appendix. Mark: 10]
a) Find client names (both first and last) who can afford more than 500 as
maxRent and registerd in Branch ‘B003’.
b) Find the property with the minimum rent. Display propertyNo, postcode
and type. You must use RENAME operator.
c) Find client names (both first and last) who have ‘Flat’ preference (prefType)
and viewed (visited) some properties. You must use appropriate set
operator.
d) How many staffs are female?
e) Find the number of customers registered at each branch. Display branch city
as well.
5. Write SQL Statements for the following queries based on the ‘MyHome’ database [CO2, C3,
schema as given in Appendix. Mark: 10]
a) Find property details (propertyNo, street and city) which are ‘House’ type
and rent is less than 650.
b) Find client names (both first and last) and the staff names (both first and
last) who are assigned to those clients.
c) Find branchNo and city which have no registered customers. You must use
appropriate set operator.
d) Find staff names (both first and last) who are managers and their first name
starts with ‘J’ and has exactly four characters.
e) Find the number of properties and average rent of properties for each city.
Sort the result based on average rent in ascending order. Rename the column
heading of the result relation appropriately.
Page 2 of 4
05/11/2020
Appendix
MyHome Database
MyHome is a housing agent in UK like Bikroy.com in Bangladesh that publishes advertisements of

properties that can be rented. MyHome manages the following database schema which is used in
question 3 and 5.
Branch (branchNo, street, city postcode)

Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
Client (clientNo, fName, lName, telNo, prefType, maxRent, eMail)
PrivateOwner (ownerNo, fName, lName, address, telNo, eMail, password)
PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo,
branchNo)
Viewing (clientNo, propertyNo, viewDate, comment)
Registration (clientNo, branchNo, staffNo, dateJoined)
MyHome has many branches throughout the country. The relation Branch stores branch related
information.
The relation Staff holds staff related data and the branch number a staff is working on.
The relation Client holds client related data including their preferred accommodation type and the
maximum rent they can afford.
The relation PrivateOwner stores property owners’ data.
The relation PropertyForRent stores property related data about the location of the property, number
of available rooms and rent amount along with the owner number, branch number under which the
property is registered and the staff number who is the contact person for that property.
The relation Viewing stores data on each client’s visit on a property. A client can visit a property
multiple times, but not twice in a day. A client can make some comments about the property during
his/her visit.
The relation Registration contains data on clients’ registration including the client number, branch
number in which the client is registered, staff number who is the contact person for that client and the
date when the client registered. A client can register at multiple branches.
A sample dataset on the above-mentioned schema is given for better understanding.
Page 3 of 4
05/11/2020
Page 4 of 4

QUESTION

Uploaded by

Copyright:

Available Formats

QUESTION

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

QUESTION

Uploaded by

Copyright:

Available Formats

05/11/2020

EAST WEST UNIVERSITY

2. a) Consider the following database schema. [CO1, C3,

b) Consider the following relations r and s. [CO1, C3,

Find the output of the following expression.

3. Consider the following Product relation. [CO2, C3,

Write SQL Statements to perform the following operations.

MyHome is a housing agent in UK like Bikroy.com in Bangladesh that publishes advertisements of

Branch (branchNo, street, city postcode)

The relation PrivateOwner stores property owners’ data.

A sample dataset on the above-mentioned schema is given for better understanding.

You might also like