COS 425, Spring 2001 - Problem Set 5

Collaboration Policy

You may discuss problems with other students in the class. However, each student must write up his or her own solution to each problem independently. That is, while you may formulate the solutions to problems in collaboration with classmates, you must be able to articulate the solutions on your own.

Problem 1. The Department of Computer Science maintains a database of technical reports using the software MySQL. Normally, this is accessed from the department's technical reports server. However, the technical staff have prepared a SQL Web interface so that you may play with a real database. This database is somewhat different from the examples we have been using in that it has much more text and no numerical data. There are only two tables, defined as:

TABLE main (
  id varchar(10),
  entry date,
  org varchar(75),
  language varchar(20),
  title tinytext,
  date varchar(30),
  pages varchar(15),
  abstract text,
  ps enum('Y','N'),
  pdf enum('Y','N'),
  PRIMARY KEY (id),
  UNIQUE id (id)
);


TABLE authors (
  id char(10),
  author char(50)
);

Part 0 (warm-up, don't turn in) To get an understanding of what the entries in main and authors look like, go to the SQL Web interface and enter and submit these three SQL queries:

select *
from main M
where M.date='January 1999'

select *
from authors A
where A.id='TR-595-99'

select *
from authors A
where A.author LIKE '%Skadron%'

Query a: Find the name of all co-authors of Professor Appel in technical reports dated sometime in 1999.

Query b: Find the name of all authors of TR's dated in the summer of 1999 (June, July and August).

Query d: For those authors who have published more than 1 TR in 1999, find their names and the number of TRs they published in 1999.

Part 2 Why do you think the database was organized the way it was: two tables with the data recorded as indicated in the table definitions? Would you organize it differently? Be sure to consider the use of this database as represented by the technical report server.

Problem 2 Before doing this problem, read the instructions for using a MySQL server created for COS425 work. To submit this problem, create a batch file of the SQL commands you use and a dump of your database to an ASCII file. Submit printouts of both files. You may do one batch file and one dump containing all of Parts 1, 2 and 3. Save your database on the studentdb server until the assignment has been graded.

For this problem we will revisit the relational database used in Problem 4 of Problem Set 2 (and from Database System Concepts by Silberschatz, Korth and Sudarshan):

• relation employee with attributes (name, street, city)
• relation company with attributes (co_name, city)
• relation works with attributes (name, co_name, salary)
• relation manages with attributes (employee_name, manager_name)

Part 1 Define tables corresponding to the 4 relations in your MySQL database. Include definitions of FOREIGN KEY constraints as well as PRIMARY KEY constraints -- even though MySQL will not maintain foreign key constraints.

Part 2 Add a constraint to the works relation that disallows the sum of all salaries in a company to be over $50,000,000 (even though MySQL won't check the constraint).

Part 3 Add two companies with at least 3 employees each, and with at least one manager each. Include tuples that show that MySQL is not maintaining FOREIGN KEY constraints and CHECK constraints. Of course, you can use minimalist names so that you don't need to do a lot of typing.