CS 386/586 Winter 2009 Exercise 2 January 13, 2009

Work through the exercises
Undergraduate students, please turn in your paper (with papers from all other exercises) at the end of the term.

Teacher table:
Number	Name	Office	E-mail
1	Lois	O12	lmd@whatever.edu
2	James	O14	jterwill@whatever.edu
3	Sally	O55	sally@whatever.edu

Course table:
Number	Name	Description
386	Intro to Databases	Introduction to relational database management systems
161	Intro to Computer Science 1	Introduction to problem-solving, algorithm and program design

Can-teach table:
Course	Teacher
386	1
386	2
161	2

Write a relational algebra query that is equivalent to each of the following SQL queries:

1. SELECT         DISTINCT Name
FROM          Teacher
WHERE Number > 1;

2. SELECT          *
FROM           Teacher, Can-teach;

3. SELECT          *
FROM            Teacher, Can-teach
WHERE          Teacher.Number = Can-teach.Teacher;

4. SELECT          *
FROM           Teacher, Can-teach
WHERE         Can-teach.Course < 300 AND Teacher.Number = Can-teach.Teacher;

5. SELECT          DISTINCT T.Name, C.Name
FROM           Teacher T, Can-teach CT, Course C
WHERE         T.Number = CT.Teacher AND CT.Course = C.Number;

Consider the following table that represents products sold at several local convenience stores. For each of the following requests:

a) Figure out the answer, using the data provided

b) Write an SQL query that will compute the query answer.

Use the Store Inventory table shown below. Note that in-class students will be given a packet of “tuples” plus one “schema tuple” in a set. Out-of-class students can create the same resource by printing the table below and then cutting each row apart from the rest. Manipulating these rows, e.g., into groups, can be useful.

1. For each store, how many products are sold? (List the store name and the count of products.)

2. For each store, which product has the highest price (at that store)? Please ignore the size of the beverage, just look at price. (List the store name and the high price.)

3. Which products (identified by name) are only sold by one store? (List the products.)

4. What would it cost to buy one of each milk product sold at each store? (List the store and the total cost.)

Store-Product-Price Table
Store	Beverage	Size	Price
Plaid Pantry	Whole Milk	16oz	1.15
Plaid Pantry	2% Milk	16oz	1.05
Plaid Pantry	Whole Milk	64oz	3.20
Plaid Pantry	Pepsi	12oz	.70
Plaid Pantry	Diet Pepsi	12oz	.70
Plaid Pantry	Pepsi	20 oz	.95
7-11	Whole Milk	8oz	.65
7-11	Chocolate Milk	8oz	.65
7-11	Whole Milk	16oz	1.10
7-11	2% Milk	16oz	1.00
7-11	Coke	12oz	.65
7-11	Diet Coke	12oz	.65
7-11	Coke	20oz	1.10
7-11	Diet Coke	20oz	1.10
7-11	Diet Caffeine-Free Cherry Coke	20oz	1.10
Circle K	Whole Milk	8oz	.60
Circle K	Whole Milk	16oz	1.20
Circle K	Whole Milk	32oz	2.30
Circle K	Whole Milk	128oz	4.10
Circle K	Coke	20oz	1.15
Circle K	Diet Coke	20oz	1.15
Circle K	Coke	32oz	2.10
Circle K	Diet Coke	32oz	2.05
Safeway	Skim Milk	16oz	1.20
Safeway	Skim Milk	32oz	2.00
Safeway	2% Milk	16oz	1.25
Safeway	2% Milk	64oz	2.75
Safeway	Diet Pepsi	12oz	.60
Safeway	Diet Coke	12oz	.60