CS 386/586 Winter 2009 – Exercise 1 Suggested Answers – 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

Based on the data in this database, can Sally teach the 161 course? Explain briefly.
No. Sally is a teacher (because her name appears in the Teacher table. And 161 is a course (because 161 appears as a Number in the Course table). But there is NO row in the Can-teach table that links Sally (Teacher with Number 3) with 161.
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.

James

O14

jterwill@whatever.edu

from the Teacher table.

386

Intro to Databases

Introduction to relational database management systems

from the Course table.

386

from the Can-teach table.

Consider the following query:
SELECT *
FROM Teacher;

List all the rows that appear in the query answer:

1	Lois	O12	lmd@whatever.edu
2	James	O14	jterwill@whatever.edu
3	Sally	O55	sally@whatever.edu

Consider the following query:
SELECT                  *
FROM                   Can-teach
WHERE                 Teacher = 1 AND Teacher = 2;

List all the rows that appear in the query answer:

This query answer is ALWAYS empty. There is no row in the Can-teach table where the Teacher attribute value is both 1 and 2.
The Can-teach table shown above has one key that consists of (Course-number, Teacher-number).

Does the current data shown above allow Course-number (by itself) to be a key for this table? Why or why not?
No because there are two rows in the current instance with the same Course.
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.)
If Course is the key for the Can-teach table, then for each course there can be only one teacher. (This is different from the current design where a course can be taught by any number of teachers.)
Does the current data shown above allow Teacher to be a key for the Can-teach table?
No because there are two rows in the current instance with the same Teacher.
If we ignore the data shown above, what is the impact of allowing the key for this table to be just Teacher-number?
If Teacher is the key for the Can-teach table, then each teacher can only teach one course. (This is different from the current design where a teacher can teach any number of courses.)

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

SELECT *
FROM Teacher, Can-teach;
This is a cross product between Teacher and Can-teach. Every attribute from Teacher and Can-teach tables appears in the query answer. Since there are three rows in the Teacher table and three rows I the Can-teach table, we have nine rows in this query answer. Every course row must appear with every possible Can-teach row. (The order that the rows appear in the query answer is NOT important. As long as you have nine rows that match the following nine rows, then you have the correct answer, even if you have them in a different order.)

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

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

In contrast to query 6, this is a join between the Teacher table and the Can-teach table. Teacher.Number = Can-teach.Teacher is the join condition. The query answer is a subset of the query answer for question 6; the query answer consists of every pair of rows from Teacher and Can-teach where the join condition (the WHERE clause) evaluates to true. (The order that these three rows appear is NOT important.)

Number	Name	Office	E-mail	Course	Teacher
1	Lois	O12	lmd@whatever.edu	386	1
2	James	O14	jterwill@whatever.edu	386	2
2	James	O14	jterwill@whatever.edu	161	2

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

This query is similar to the query in question 7; we have the same join condition in this WHERE clause. But this WHERE clause further restricts the join to include oly course numbers that are less than 300. This query answer is a subset of the answer to the query in Question 7 consisting of only the rows from the query answer in Question 7 where the Can-teach.Course is less than 300. There is only one row in this query answer.

Number	Name	Office	E-mail	Course	Teacher
2	James	O14	jterwill@whatever.edu	161	2

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

This query joins the Teacher table to the Can-teach table and also (at the same time) joins the Can-teach table to the Course table. Note that this query answer has attributes from all three tables: the Teacher table, the Can-teach table, and the Course table. (The order of these three rows in your query answer is NOT important.)

Number	Name	Office	E-mail	Course	Teacher	Number	Name	Description
1	Lois	O12	lmd@whatever.edu	386	1	386	Intro to Databases	Introduction to relational database management systems
2	James	O14	jterwill@whatever.edu	386	2	386	Intro to Databases	Introduction to relational database management systems
2	James	O14	jterwill@whatever.edu	161	2	161	Intro to Computer Science 1	Introduction to problem-solving, algorithm and program design

10. SELECT                  T.Name, C.Name
FROM                   Teacher T, Can-teach CT, Course C
WHERE                 T.Number = CT.Teacher AND CT.Course = C.Number;
This query is identical to the query in question 9 except that the final query answer includes only two attributes from the intermediate query answer, namely T.Name and C.Name. The query answer has three rows just like the query answer to the query in question 9.

T.Name	C.Name
Lois	Intro to Databases
James	Intro to Databases
James	Intro to Computer Science 1

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.

We need a table for employees. We could do something like this:

Employee (ssn, first, last, job-title, hire-date)

We can introduce a table for office as well, resulting in a database like this:
Office(o-number, building, size)

Since the relationship between Employee and Office is what we call a “many-to-many” relationship (meaning that one employee can have more than one office and one office can have more than one employee), we need to introduce an additional table to represent the assignment of one employee to one office. It would look like this:
Office-assign(ssn, o-number)

We have two foreign keys in this table. Office-assign.ssn is a foreign key that REFERENCES Employee.ssn. Office-assign.o-number is a foreign key that REFERENCES Office.o-number.

The key for this table must be ssn and o-number, together. (Any other choice of a key would not properly support the many-to-many relationship.)

Since we want to represent parking spaces with zero employees assigned, we should have a table for parking spaces. This allows the parking space to be in the parking-space table – even if it has no employees assigned. And, we need to add a parking-space attribute to Employee to represent the assignment. Something like this:

Employee (ssn, first, last, job-title, hire-date, parking-space)
where Employee.parking-space is a foreign key that references Parking-space.p-number
Office(o-number, building, size)
Parking-space (p-number, lot, handicap)

Notice that we don’t need a separate table to represent the assignment of one employee to one parking space because one employee can have at most one parking space. Thus it is appropriate to put an attribute to represent the parking space assignment directly in the Employee table. (You could have a separate table to represent the assignment of one employee to one parking space but you don’t need one. These tables accurately represent the assignment of employees to parking space.)

Notice that it is possible with this schema for one parking space to be assigned to more than one employee. It happens whenever there are two employees that have the same value for the parking-space attribute. In our application, we would like to prevent this from happening. So the programmer would have to make sure that this doesn’t happen by writing some additional code.

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.

SELECT          first, last, parking-space
FROM           Employee
WHERE         parking-space not = NULL;

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.

SELECT          O.o-number
FROM           Office O, Office-assign OA1, Office-assign OA2
WHERE         O.o-number = OA1.o-number AND O.o-number = OA2.o-number AND OA1.ssn > OA2.ssn;

Notice that we are using the Office-assign table twice in the FROM clause. That is, we are using two copies of the Office-assign table. This means that each time we evaluate the WHERE clause, we are using one row from the Office table and two rows from the Office-assign table … one using the correlation name of OA1 and the other using a correlation name of OA2. By requiring that OA1.ssn > OA2.ssn we prevent the case where the same (single) row in the Office-assign table is substituted in for OA1 and OA2. More than that, by requiring that OA1.ssn > OA2.ssn (rather than requiring that they be not equal), we prevent duplicate answers. If there are two Office-assignment rows (say with ssn of 3 and 1 represented by OA1 and OA2, respectively) that satisfy the where clause we want to prevent the other case where ssn of 1 and 3 are represented by OA1 and Oa2, respectively, from satisfying the WHERE clause.