CS 386/586 Winter 2009 – Exercise 1– January 6, 2009

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

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

1. Based on the data in this database, can Sally teach the 161 course? Explain briefly.
   
   
   
   
2. Mark (or list below) the rows in the above database that allow you to know that “James” can teach the “Intro to Databases” course. DO NOT include any extra rows; mark or list ONLY the rows that you need.
   
   

3. Consider the following query:
   SELECT *
   FROM Teacher;
   
   List all the rows that appear in the query answer:
   
   
   
4. Consider the following query:
   SELECT *
   FROM Can-teach
   WHERE Teacher = 1 AND Teacher = 2;
   
   List all the rows that appear in the query answer:
   
   
   
   
5. The Can-teach table shown above has one key that consists of (Course-number, Teacher-number).

1. Does the current data shown above allow Course-number (by itself) to be a key for this table? Why or why not?
   
   
2. If we ignore the data shown above (and start with a new database), what is the impact of allowing the key for this table to be just Course-number? (Describe in English what constraints we would have in the application if Course-number is the key for this table.)
   
   
3. Does the current data shown above allow Teacher to be a key for the Can-teach table?
   
   
   
4. If we ignore the data shown above, what is the impact of allowing the key for this table to be just Teacher-number?
   
   
   

6. Show the query answer for each of the following queries:

SELECT *
FROM Teacher, Can-teach;

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

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

9. SELECT *
FROM Teacher T, Can-teach CT, Course C
WHERE T.Number = CT.Teacher AND CT.Course = C.Number;

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

11. Design the schema for a database that stores information about employees (identified uniquely by ssn), offices (identified uniquely by o-number), and parking spaces (identified by p-number). Each employee has one or more offices; an office can have multiple employees assigned. Each parking space has zero or one employees assigned; each employee may have zero or one parking space. Be sure to specify the key for each table. And be sure to specify any foreign keys in your tables. Place a few appropriate attributes in each table.

12. Write an SQL query (for the above database) that lists the name of the employee and the number of the parking space, for every employee that has an assigned parking space.

13. (Bonus) Write an SQL query for the above database that lists the office number for each office that has at least two employees assigned. Make sure that each office number (that has at least two employees assigned) is listed just once in your query answer.