CSCI A114 / INFO I111
Midterm MakeUp Exam

First semester 2000-2001

There are two problems below. Please solve them on paper and turn them in before the Final Exam. Maximum number of points is 100. This makeup is optional and, if taken, the grade obtained for it replaces the one for the original midterm.

1. Consider the relational database of below:

employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager-name)
For 15 points: For 30 points, for each query below (10 points each), give an expression in the relational algebra (that is, an expression that involves projection, selection, and/or natural join operations) that describes the query.

2. (55 points) Design a relational database corresponding to the E-R diagram below:

Then populate the database with the following data:

Loan L-17 has been applied for at the Downtown branch, L-15 at Perryridge, L-11 at Round Hill, and L-16 at Perryridge. Loan L-16 is for $1,3000, L-11 for 900, L-15 for $1,500, and L-17 for $1,000. Jones is the borrower of L-17, Smith is the borrower of L-15, L-16 was lent to Adams, and L-15 was made by Williams and Hayes, together.

Note: Williams and Hayes are borrowers of L-11 (and not L-15). Sorry for the typo!

Then show how you obtain the answer to the following question
Find the names of all the customers that have a loan at the Perryridge branch.
using only selections, projections, and natural joins.

Show how the query is calculated, step by step, to come up with the answer.

The number of points for this problem will be evenly distributed among the right number of tables, the proper entering of the data in the tables, and application of the relational operators (selection, projection, natural join) to process the query.

Last updated: October 11, 2000 by Adrian German for A114/I111