---

Introduction

The following are possible solutions to the Midterm. For all of these problems except the first one, there were multiple reasonable solutions, and I did my best to accept anything that worked, assigning partial credit as appropriate when solutions partially worked.

It's a good idea to print this document out and bring a copy of it with you when you view your exam, as it may help you to quickly see where you went wrong on a question.

---

The solutions

Problem 1

a) chain_name

b) rest_id

c) emp_ssn

d) emp_ssn, mentor_emp_ssn

e) rest_id, the_date

Problem 2

(In the solutions below, I've used NJ to stand for natural join, since the natural join symbol is not supported in the typical character sets used by some browsers, including Internet Explorer.)

I graded this problem with the understanding that there are multiple reasonable ways to solve each problem; the solutions I'm showing here provide one way to solve each problem, but these solutions are not unique and are not even necessarily the "best."

a) Π_{rest_city, rest_state} (σ_{cuisine = "Cantonese"} (restaurant NJ chain))

b) no_mentor_emp_id ← Π_{emp_id} (employee) − Π_{emp_id} (mentor)
Π_{emp_name, salary} (no_mentor_emp_id NJ employee)

c) ca_it_rest ← Π_{rest_id} (σ_{rest_state = "California" ∧ cuisine = "Italian"} (restaurant NJ chain))
march_revenue ← σ_{the_date ≥ 3/1/2005 ∧ the_date ≤ 3/31/2005} (revenue_cost)
ca_it_march_rev ← _{rest_id} G _{sum (revenue) as total_revenue} (march_revenue NJ ca_it_rest)
Π_{rest_id} (G _{max (total_revenue) as total_revenue} (ca_it_march_rev) NJ ca_it_march_rev)

d) march31_profit ← Π_{rest_id, chain_name, revenue − cost as profit} (σ_{the_date = 3/31/2006} (revenue_cost NJ restaurant))
no_revenue_on_march31 ← Π_{rest_id, chain_name, 0 as profit} ((Π_{rest_id} (restaurant) − Π_{rest_id} (march31_profit)) NJ restaurant)
march31_profit ∪ no_revenue_on_march31

Problem 3

As with the relational algebra problem, I graded this problem with the understanding that there are multiple reasonable ways to solve each part.

a)

SELECT emp_name, emp_ssn
FROM employee, restaurant
WHERE employee.rest_id = restaurant.rest_id
  AND chain_name = 'Regina\'s Bistro'

b)

SELECT emp_name
FROM employee
WHERE emp_ssn NOT IN (SELECT mentor_emp_ssn
                      FROM mentor)

c)

SELECT rest_id, SUM(revenue) AS total_revenue
FROM revenue_cost
WHERE the_date BETWEEN '2004-01-01' AND '2004-12-31'
GROUP BY rest_id
ORDER BY SUM(revenue) DESC
LIMIT 5

Problem 4

From a syntax perspective, I was somewhat lenient about grading this problem. In particular, I was open to many ways of expressing the assertion, so long as you used an assertion to solve the problem of ensuring that no more than 100 employees can work for a particular restaurant.

CREATE TABLE restaurant(
    rest_id INTEGER NOT NULL,
    chain_name VARCHAR(30) NOT NULL,
    rest_city VARCHAR(20) NOT NULL,
    rest_state VARCHAR(15) NOT NULL,
    manager_ssn INTEGER NOT NULL,
    PRIMARY KEY(rest_id),
    UNIQUE(manager_ssn),
    FOREIGN KEY(chain_name) REFERENCES chain(chain_name),
    FOREIGN KEY(manager_ssn) REFERENCES employee(emp_ssn))

CREATE TABLE employee(
    emp_ssn INTEGER NOT NULL,
    emp_name VARCHAR(30) NOT NULL,
    rest_id INTEGER NOT NULL,
    salary NUMERIC(11, 2) NOT NULL,
    PRIMARY KEY(emp_ssn),
    FOREIGN KEY(rest_id) REFERENCES restaurant(rest_id),
    CHECK(salary >= 0.00))

CREATE TABLE revenue_cost(
    rest_id INTEGER NOT NULL,
    the_date DATE NOT NULL,
    revenue NUMERIC(11, 2) NOT NULL,
    cost NUMERIC(11, 2) NOT NULL,
    PRIMARY KEY(rest_id, the_date),
    CHECK(revenue >= 0.00),
    CHECK(cost >= 0.00),
    CHECK(the_date >= '2004-01-01'),
    FOREIGN KEY(rest_id) REFERENCES restaurant(rest_id)
        ON DELETE RESTRICT)

CREATE ASSERTION restaurant_employee_limit
CHECK (100 <= ALL (SELECT COUNT(*)
                   FROM employee
                   GROUP BY rest_id))

Problem 5

a)

CREATE VIEW salesperson_count_by_state AS
    SELECT base_state AS salesperson_state, COUNT(*) AS salesperson_count
    FROM salesperson
    GROUP BY base_state

b) The statement has no effect on the database. The update is erroneous, because it's attempting to update a value derived from the result of an aggregate function. (Consider what might happen if a database engine allowed this update. Where would the extra California-based salesperson come from? Would a new one be created? What would the new salesperson's name and base city be?)