COS 597A, Fall 2008 - Problem Set 3

Collaboration Policy

Late Penalties

• 20% of the earned score if submitted by 5pm on Monday 10/13/08.
• 40% of the earned score if submitted by 5pm on Wednesday 10/15/08.
• No credit if submitted later than the 40% penalty deadline.
  

Problem 1.  (left as an exercise in class 10/6/08)
Consider again Board Example 4 for the relational calculus (see slides posted for 10/1):

The problem was to express the query  "Find academic departments that have students working in all divisions" in the relational calculus.
Our board solution was:

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='April 2006'

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 TR's 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.

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.

Part 3  Show that the Oracle facility enforces FOREIGN KEY constraints by executing 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, (ii) 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 (iii) 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.

WARNING: You must follow each INSERT or DELETE or UPDATE 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 works (name, c_name, fee_rate) values ('smith', 'jones', 100);
        commit;