COS 425, Spring 2003 - Problem Set 3

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.

Section A: writing queries for a pre-exisiting database.

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),
  ord tinyint(3) unsigned
);

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='August 2002'

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

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

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

Query b: Find the name of all authors of TR's dated in the summer of 2002 (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.

Section B: defining a database.

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 both of Parts 1 and 2. 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 2 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 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. Of course, you can use minimalist names so that you don't need to do a lot of typing.

Problem 3 Another database system to which you have access is the Princeton University Instructional Oracle Facility. Go to the Princeton University Instructional Oracle Facility Web site and follow the directions to establish an account. Then go to the Instructional Oracle SQL Editor. You will use the Web interface provided there to define tables and insert and delete values. Specifically, show that the Oracle facility does enforce FOREIGN KEY constraints by choosing two of the relations of Problem 2 to define and executing a sequence of inserts and deletes to illustrate enforcement. Show both that a tuple cannot be deleted in the referenced table if the primary key of that tuple is present as the foreign key value of a tuple in the referring table and that a tuple cannot be added in the referring table if the foreign key value in that tuple is not present as a primary key value in a tuple of the referenced table. To turn in your work, use the browser print command to print your sequence of SQL commands and the results. Note that the full sequence of commands must be executed at one time (one transaction) in the editor window to appear as a sequence in the bottom frame, where the results also appear. Also, you must have the bottom frame selected to print both the command sequence and the results.

WARNING: You must follow each INSERT or DELETE command with a COMMIT command on a separate line for the ORACLE database system to permanently store or delete the values. For example:

	insert into manages (employee_name, manager_name) values ('smith', 'jones');
	commit;