CS 386/586 Winter 2012 – Practice – Basic SQL

Work through the exercises

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.
   
   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.
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.

2

James

O14

jterwill@whatever.edu

from the Teacher table.

386

Intro to Databases

Introduction to relational database management systems

from the Course table.

386

2

from the Can-teach table.

3. 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

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:
   
   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.
   
   
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?
   No because there are two rows in the current instance with the same Course.
   
   
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.)
   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.)
   
   
3. Does the current data shown above allow Teacher-number to be a key for this table?
   No because there are two rows in the current instance with the same Teacher.
   
   
4. 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. SELECT *
FROM Teacher, Can-teach;

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;

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;

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;

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 as Instructor, C.Name as CourseTitle
FROM Teacher T, Can-teach CT, Course C
WHERE T.Number = CT.Teacher AND CT.Course = C.Number;

Instructor	CourseTitle
Lois	Intro to Databases
James	Intro to Databases
James	Intro to Computer Science 1

11. Design the schema for a database that stores information about employees, offices, and parking spaces. Each employee can have just one office; an office can have multiple employees assigned. Each parking space has zero or one employees assigned. 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 (Id, First, Last, Job-title, Hire-date)
    
    Since each employee can have only one office, we can put an office attribute in the Employee table like this:
    
    Employee (Id, First, Last, Job-title, Hire-date, Office)
    
    Or we could introduce a table for office as well, resulting in a database like this:
    Employee (Id, First, Last, Job-title, Hire-date, Office)
    where Employee.Office is a foreign key that references Office.Number.
    Office(Number, Building, Size)
    
    Either of these two designs would work. The choice depends on whether or not you want to represent offices independently and whether or not you want to have additional attributes for an office.
    
    Since we want to represent parking spaces with zero employees assigned, we should have a table for parking spaces. And, we need to add a parking-space attribute to Employee. Something like this:
    
    Employee (Id, First, Last, Job-title, Hire-date, Office, Parking-space)
    where Employee.Office is a foreign key that references Office.Number.
    and Employee.Parking-space is a foreign key that references Parking-space.Number
    Office(Number, Building, Size)
    Parking-space (Number, Lot, Handicap)
    
    Finally, you could introduce a separate table to represent the assignment of an employee to an office and a separate table to represent the assignment of an employee to a parking space. It would look like this if I added one for parking.
    
    Employee (Id, First, Last, Job-title, Hire-date, Office, Parking-space)
    where Employee.Office is a foreign key that references Office.Number.
    and Employee.Parking-space is a foreign key that references Parking-space.Number
    Office(Number, Building, Size)
    Parking-space (Number, Lot, Handicap)
    Assigned-parking(Parking-number, Employee)
    where Parking-number is a foreign key that references Parking-space.Number
    and Employee is a foreign key that references Employee.Id.

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

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

π_Name(σ_{Number > 1}Teacher)

13. SELECT          *
FROM           Teacher, Can-teach;

Teacher X Can-Teach

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

σ_{Teacher.Number = Can-teach.Teacher}(Teacher X Can-teach)

or

Teacher ⋈_{Teacher.Number = Can-teach.Teacher} Can-teach

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

σ_{Can-teach.Course < 300} (σ_{Teacher.Number = Can-teach.Teacher}(Teacher X Can-teach))

or

σ_{Teacher.Number = Can-teach.Teacher Ù Can-teach.Course<300}(Teacher X Can-teach)

16. SELECT          DISTINCT T.Name, C.Name
    FROM           Teacher T, Can-teach CT, Course C
    WHERE         T.Number = CT.Teacher AND CT.Course = C.Number;
    
    π_{T.Name,C.Name}(σ_{T.Number = CT.TeacherÙCT.Course=C.Number}(Teacher T X Can-teach CT X Course C))
    
    or
    
    π_{T.Name,C.Name}(Teacher T ⋈_{T.Number = CT.Teacher} Can-teach CT) ⋈ _{CT.Course=C.Number} Course C

17. Consider the following tables to describe apartments and roommates.

Apartment(id, address, num-bedrooms, num-baths)
Person(id, name, gender, age)
Lease(apt-id, renter-id, year)
where apt-id REFERENCES Apartment.id and renter-id REFERENCES Person.id
We assume that each lease lasts for one year and that every lease starts on January 1 and ends on December 31.

a. Provide sample data that demonstrates that one person can hold a lease for more than one apartment in the same year. Make sure that your sample data upholds the keys and foreign keys shown. (You may think that this is odd or undesirable – but provide sample data that upholds the keys and foreign keys shown that allows a person to rent more than one apartment in the same year.)

b. Provide sample data that demonstrates that an apartment can have more than one person on a lease in a given year. Make sure that your sample data upholds the keys and foreign keys shown.

Apartment table:
id	address	num-bedrooms	num-baths
10	333 Main St.	2	2
11	501 North St.	2	1

Person table:
id	name	gender	age
100	John	male	21
101	Mary	female	23

Lease table:
apt-id	renter-id	year
10	100	2012
11	100	2012
10	101	2012

1. Here we see that John is a person and he is able to have two rows in the Lease table – one for apartment 10 and one for apartment 11.
2. Here we see that John and Mary are both on the least for apartment 10 for the year 2012.”
Notice that it is impossible for a given person, say John, to have a lease for the same apartment in two different years. We could not insert a row into the lease table for (10, 100, 2013), for example, because it would violate the key for the Lease table.

18. Consider these three tables; the Lease table has a different key compared to question 19.

Apartment(id, address, num-bedrooms, num-baths)
Person(id, name, gender, age)
Lease(apt-id, renter-id, year)
where apt-id REFERENCES Apartment.id and renter-id REFERENCES Person.id

a. How many leases can one person have in a given year, say 2012? (That is, how many rows can there be in the Lease table for a particular person in a particular year?)

Each person can have only one lease in a given year because (renter-id, year) is the key for the Lease table. So we must eliminate one of the rows for John for year 2012. I crossed out one below.

2. Is it possible for one apartment to have multiple people on a lease in a particular year? If so, provide sample data that demonstrates this while upholding all of the keys and foreign keys in these tables. If not, show a row that could not be inserted because it would violate a key or foreign key constraint. If yes, is there any limit (based on the keys and foreign keys only) to the number of people that can be on a lease for a particular apartment in a particular year? Explain briefly.
   
   Yes, it is possible. See that apartment 10 still has two people on the lease (Mary and John) and no keys or foreign keys are violated.
   
   
   

Apartment table:
id	address	num-bedrooms	num-baths
10	333 Main St.	2	2
11	501 North St.	2	1

Person table:
id	name	gender	age
100	John	male	21
101	Mary	female	23

Lease table:
apt-id	renter-id	year
10	100	2012
11	100	2012
10	101	2012