CS 448/648 Assignment #2

Assignments may be done individually or in teams of two.

Due by 10:00 am on Tuesday, February 13, 2001.

To receive full marks, each solution must work properly for all valid instances of the given database. For each part, you should submit the text of the solution itself together with the output generated by DB2 when your solution is run. See the DB2 hints for CS448/648 for information about capturing output from the DB2 command line processor.

Express each of the following queries in SQL against the DB2 sample database, which includes the EMPLOYEE, DEPARTMENT, PROJECT, and EMP_ACT tables. Each query should be expressed using a single SQL statement, and produce a properly labelled table having contents more or less in the form shown in the example following the statement of the query.

a) For each employee with job title ‘MANAGER’ give the full name, current salary, and age (in years) at the time he or she was hired, ordering the output by name.

Mary	A.	Jones	75000	36
Albert	E.	Lee	68400	41

b) For each manager, give the manager’s last name, corresponding department name, and the number of employees in each job category (other than manager) in the department.

Smith	Manufacturing	Engineer	5
Smith	Manufacturing	Secretary	2
Jones	Sales	Vendor	12

c) List the project name, responsible employee, most senior person(s) on the project, and manager of the responsible department for each project for which the responsible employee is neither among the most senior persons (determined by length of employment) assigned to any activity for the project nor the manager of the responsible department for that project.

Widget	Smith	Evers	Andrews
Gadget	Jones	Wong	Wong
Gadget	Jones	Lee	Wong

d) For each project with 0 to 2 subprojects, give its name, estimated staffing, and the sum of the estimated staffing for all its subprojects, if any (where estimated staffing is estimated mean staffing * total number of complete months in the project’s estimated duration).

Widget	25	16
Gadget	18	0

All parts of this question relate to a database, similar to the one you defined on Assignment 1, having the following schema:

TIME_SLOT (ID, start_time, end_time)

NETWORK (name)

TV_CHANNEL(number, city, type, owner, network_name)

PERSON (name, date_of_birth, sex)

ACTOR (name, exclusive_network)

TV_PROGRAM (name, genre, intended_audience, rating, length)

PROGRAM_SLOT (slot, channel)

APPEARS_ON (slot, channel, program_name)

STARS_IN (program_name, actor_name)

As well as key constraints and foreign key constraints, we also have various domain constraints (i.e., whatever datatypes we would wish to associate with each attribute) plus inclusion dependencies between ACTOR.name and PERSON.name. We also have further constraints that:

o Start and end times for time slots must be at quarter-hour boundaries (e.g., 12:00, 12:15, 12:30, 12:45).

o The type of a TV_CHANNEL must be either “N” (for network) or “I” (for independent) and network_name is not null iff the type is “N”.

o A TV channel number is an integer between 2 and 99.

o A TV program rating is an integer between 1 and 5, and its length is an integral number of minutes.

o The tuples in PROGRAM_SLOT define the set of valid pairs at which programs may appear.

Any further information about other attributes can be taken from Assignment 1.

Note: In DB2 you need to start with the command “connect to sample” before you can run any of the DDL or DML commands.

a) Use DB2 to define the nine tables described above. For convenience, you may wish to type the DDL statements into a file and then execute the file from the command line. For example, create a file “define” with the lines

DROP TABLE athlete

CREATE TABLE athlete ( \

aname CHAR(32) NOT NULL PRIMARY KEY, \

age INTEGER NOT NULL, \

gender CHAR(7) NOT NULL, \

represents CHAR(32) NOT NULL REFERENCES country, \

CONSTRAINT athlete_gender CHECK ( \

UPPER(gender) IN ('MALE', 'FEMALE') \

) \

)

Then execute the command “db2 –f define” to create the table. Hint: Preceding each create with a corresponding drop allows you to execute this file repeatedly while you are debugging your code. Don’t forget to end each line in the middle of a single command with a backslash so that DB2 reads all the lines as one command.

b) Insert two to five tuples into each of the following tables: TIME_SLOT, NETWORK, TV_CHANNEL, PERSON, and TV_PROGRAM. For example, create a file “insert” with the lines

delete from athlete

insert into athlete values ('A1',1,'Male','C1'), \

('A2',2,'Female', 'C1'), \

('A3',3,'Male','C1'), \

('A4',4,'Female','C2'), \

('A5',5,'Male','C2')

Again, preceding the set of inserts with corresponding deletes simplifies re-execution of the file while debugging.

c) Create a view SLOT_LENGTHS(id,start_time,length) based on TIME_SLOT, but where the third attribute represents the duration of the time slot in minutes, and print all the tuples in this view.

d) Create another view EXCLUSIVE_SLOTS that includes a tuple from SLOT_LENGTHS iff the corresponding time slot is not contained within another tuple from SLOT_LENGTH and does not begin in the middle of the time slot for some other tuple from SLOT_LENGTHS. Print all tuples in this view.

e) Create an instance of PROGRAM_SLOT by pairing every time slot with every channel. Insert tuples into APPEARS_ON such that:

o Programs occur in program slots only if the slot is in EXCLUSIVE_SLOTS and the length of the program is at most the length of the slot and the slot is at most 29 minutes longer than the program.

o Subject to that condition, all programs not intended for children appear on all channels in all slots that start after 5:00 pm.

o Subject to the same condition, all programs that are intended for children appear on all channels in all slots that end before 8:00 p.m.

f) Insert into ACTOR all persons, setting exclusive_network to null. Update ACTOR to set exclusive_network to ‘CBC’ is the person was born before 1950. Update ACTOR to set exclusive network to ‘NBC’ is the person’s name has the letter ‘R’ anywhere in it. Insert into STARS_IN tuples that reflect that each actor who does not have an exclusive contract with ‘CBC’ appears in every program that has a rating greater than 3. List all tuples in ACTOR and STARS_IN.

g) Create a view showing which actors appear on which channels at which start times, and print all tuples sorted by actor and channel. Find which actors are in violation of their exclusive contracts by finding those who have an exclusive contract with one network but appear on a channel that belongs to some other network.

actor	exclusive	start_time	channel	violation
Donald Smith	CBC	18:00:00	12	CTV
Donald Smith	CBC	20:30:00	2	TVO
Lee Chan	CTV	18:00:00	5	CBC