PRACTICE EXAM
______________________________________________________________________________
Course: CSI 2132B - Databases I
Academic year: 2019/2020
Semester: Winter
Instructor: Olubisi Runsewe
______________________________________________________________________________
Please read the following instructions carefully:
This exam contains 3 different parts (Part I, Part II and Part III). In each part you have 4 questions
available. You must select and provide the answers to exactly two questions in each part. If you
provide answers for more than two questions in any part of the exam, only the first two questions
answered will be considered. The total number of questions to be answered is 6, with 2 mandatory
questions in each part.
Part I
1. (10%) A worldwide package delivery company named WorldDelivery wants to store information
in a database. The database must be able to keep track of:
Customers who ship items and customers who receive items; some customers may do
both. The customer ID, name and address must be stored.
Each package must be identifiable and trackable, so the database must be able to store
the location of the package and its history of locations. The package weight must also be
stored.
Locations include trucks, planes, airports, and warehouses. For each location the
database should store the corresponding ID, city, country and address.
Draw an ER diagram for the database described.
2. (10%) Consider the ER diagram below for modeling an online bookstore.
a. (6%) Translate the ER diagram into a relational model.
b. (2%) Provide the Tuple Relational Calculus (TRC) expression for the following query:
a. “List the names of all books published in the year 2020, by ‘Wiley’ publisher.”
c. (2%) Provide the Relational Algebra (RA) expression for the following query:
a. “Find the names of all books with price lower than $50 that are stored in the
warehouse with code ‘556677’.”
3. (10%) Consider a relation with schema R (A, B, C, D) and the set F of functional dependencies:
F= {AB→C, C→D, D→A}
a. (2%) Find all the candidate keys of R.
b. (4%) Indicate all BCNF violations for R and decompose the relations into collections of
relations that are in BCNF.
c. (4%) Indicate which dependencies, if any, are not preserved by the BCNF decomposition.
4. (10%) An agency called InstantCover supplies part-time/temporary staff to hotels throughout
Scotland. The following table lists the time spent by agency staff working at two hotels.
The National Insurance Number (NIN) is unique for each employee. Knowing the NIN and the
contractNo the company is able to determine the hoursPerWeek that the employee is doing.
The NIN also allows the company to know the employee name (eName). Using the hotelNo one
can know the hotelLocation. Each contractNo is associated with a particular hotel, which means
that by knowing the contractNo the company also knows the hotelNo.
a. (3%) Based on the information above, identify the four functional dependencies
described.
b. (3%) List all candidate keys.
c. (4%) Normalize the relation to the third Normal Form and show the resulting relations.
Part II
5. (10%) Consider the following relational schema:
professor (profname, deptname)
department (deptname, building)
committee (commname, profname)
a. Explain what data the following queries retrieve:
i. (2%) select distinct B.profname
from committee A, committee B
where A.profname = 'Piper' and B.commname = A.commname
ii. (3%) select distinct B.profname
from committee B
where not exists ((select commname
from committee A
where profname = 'Piper')
except (select commname
from committee A
where A.profname = B.profname))
b. Write the following queries in SQL:
i) (2%) “Find all the committees that integrate both professor ‘Piper’ and professor
‘John’.”
ii) (3%) “Find the names of all professors who have not offices in any of those buildings
that Professor Piper has offices in.”
6. (10%) The following relations keep track of airline flight information:
Flights(flno: integer, origin: string, destination: string, distance: integer, departs: timestamp,
arrives: timestamp, price: integer)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Write the SQL queries necessary to implement the following:
a. (2%) Add a new attribute to the relation Flights that stores the currency of price.
b. (2%) Define CAD as the default value for the attribute currency created.
c. (2%) flno, in relation Flights, should be the primary key
d. (2%) aid, in relation Certified, should be a foreign key to Aircraft relation
e. (2%) Check that all employees have a salary higher than 2000.
7. Consider the following relations for a database that keeps track of orders for a company.
Customer (CID, CompanyName, ContactName, Address, City, State, PostalCode, Phone#)
Order (OID, CID, OrderDate)
Order_Details (OID, PID, UnitPrice, Quantity, Discount)
Product (PID, ProductName, MID, Color, QuantityPerUnit, UnitPrice, ManufacturedDate)
Manufacturer (MID, ManufacturerName, Address, City, State, Country, PostalCode)
a) Create the Order_Details table using the integrity enhancement features of SQL to check
that “discount is less than 15%”.
b) Retrieve the company names, contact names, city, and phone numbers of customers with
orders greater than 500, in ascending order of company names.
c) Find all the products manufactured by Excel industries using the EXISTS clause.
d) Provide the customer names, PID and average quantity of orders for each customer.
e) Find all products under $50 manufactured in Canada, return their names, manufacturer,
prices and date manufactured.
8. (10%) Consider the following relations:
Sailors (sid, sname, rating, age)
Reserves (sid, bid, day)
Boat (bid, bname, bcolor)
Write expressions in Relational Algebra (RA), Tuple Relational Calculus (TRC) or Domain
Relational Calculus (DRC) as indicated for the following queries:
a. (2%) (RA) List the colors of boats reserved by Albert.
b. (2%) (RA) List the id’s of all sailors who have a rating of at least 8 or reserved boat 103.
c. (2%) (TRC) List the names and age of all sailors who have a rating lower than 3
d. (2%) (DRC) List the id’s of all boats that were reserved on 2019-04-28.
e. (2%) (DRC) List the colors of all boats reserved by Lubber.
Part III
9. Select the correct correspondences (2% each correct answer):
10. (10%) Consider the following B+-tree with n=4.
a. (5%) Show the B + -tree that results after inserting (in the given order)
56, 50, 75, 87, 48.
b. (5%) Using the B + -tree previously obtained in (a.) show the B + -
tree that results after delete (in the given order) 50, 24, 65, 93, 75.
11. (10%) Consider the following instance of the relation cars:
a. (4%) Construct a bitmap index for the attributes Brand and Color for this table.
b. Show how bitmap indices can be used to answer the queries:
i. (3%) “Show the Brand of all cars that are not black”
ii. (3%) “Give the total number of red Opel cars with a medium risk score.”
12. (10%) Consider the following hash function h(x) = x mod 4 for building a hash index. This
function allows you to use 4 different buckets. Assume that each bucket can only contain 3
index entries.
a. (5%) Use this function to build the hash index of the following search key values: 2, 4, 6,
12, 13, 16, 20, 24, 28, 40
b. (5%) Given the search key values, is this function a good hash function? Explain your
answer.