COS 597A, Fall 2011 - Problem Set 3

Collaboration Policy

Late Penalties

• 5% of the earned score if submitted after class but by 5:45pm the day due.
• 20% of the earned score if submitted by 5pm on Friday 10/21/11.
• 40% of the earned score if submitted by 5pm on Monday 10/24/11.
• No credit if submitted later than the 40% penalty deadline.

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 four SQL queries, one at a time:

select *
from main M
where M.date='May 2008'

select M.entry, M.date 
from main M
where M.entry < '1994-05-01'

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

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

Query a: Find the names of all authors of TRs dated in the summer of 2002 (June, July and August).

Query b: Find the ids, titles, and dates of the TRs with the word "security" in their abstracts.

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

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

Query e:  Find the entry date(s) when the most TRs were entered and the number of TRs entered on that entry date. "Entry date" refers to the entry attribute, not the date attribute.

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.

For this problem we will use the following database for a consulting firm:

• relation consultant with attributes (name, street, city)
• relation client with attributes (c_name, street, city)
• relation works with attributes (name, c_name, fee_rate)
• name is a foreign key referencing consultant
  
•  c_name is a foreign key referencing client
  
• relation supervised_by with attributes (name, c_name, supervisor_name)
• (name, c_name)  is a foreign key referencing works
• supervisor_name is a foreign key referencing consultant

Part 1 Define tables corresponding to the 4 relations in your Oracle database. Include definitions of PRIMARY KEY constraints and FOREIGN KEY constraints.   At least one FOREIGN KEY constraint needs to be ON DELETE CASCADE and at least one FOREIGN KEY constraint needs to be the default (delete not allowed) for Part 3.
Use MySQL command "DESCRIBE table name" (e.g. DESCRIBE consultant) to document that each table exists.

Part 2 Add two consultants with at least two clients each and two consultants with no clients; at least one consultant must have a supervisor for each client with which he/she is paired. Of course, you can use minimalist names so that you don't need to do a lot of typing.  Use "SELECT * from table name"  to document the contents of each table.

Part 3  Show that the InnoDB engine of MySQL  enforces FOREIGN KEY constraints and that it supports ON DELETE CASCADE.  Execute a sequence of inserts and deletes for two of the relations (your choice of which) to illustrate enforcement.  Show (i) that a tuple cannot be deleted in the referenced table if the primary key value of that tuple is present as the foreign key value of a tuple in the referencing table and ON DELETE CASCADE is not specified, (ii) that a tuple deleted in the referenced table cascades to a tuple deleted in the referencing table if the primary key value of the first tuple is present as the foreign key value of the second tuple and ON DELETE CASCADE is specified for the foreign key constraint, (iii) that the primary key value of a tuple cannot be changed in the referenced table if that primary key value is present as the foreign key value of a tuple in the referencing table, and (iv) that a tuple cannot be added in the referencing table if the foreign key value in that tuple is not present as a primary key value in a tuple of the referenced table.