COS 425, Fall 2006 - Problem Set 3:   Part I: SQL

Collaboration Policy

Late Penalties

• 10% of the earned score if submitted after class but by 5pm the day due.
• 30% of the earned score if submitted by email to Professor LaPaugh by 5pm on Sunday 10/22/06.
• No credit if submitted later than the 30% penalty deadline

Part I, Section A: writing queries for a preexisting database.

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

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.

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.

Part I, Section B: defining a database.

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;

Problem 3
In many applications, there is a large amount of initial data when a new database is defined. This data is bulk loaded into the database tables. The Princeton University Instructional Oracle Facility provides a simple bulk loading facility, which is documented in The SQL Loader Demo. The bulk loader uses data and control files, which must reside in your top-level directory on the OIT central file server (for Windows machines on the OIT network, this is usually drive H; this is also the home directory of your OIT unix (arizona) account). The loader loads into tables defined using the Instructional Oracle SQL Editor.

You will bulk load data into a table "stugrade" with two attributes: a student id "sid" of type NUMBER and "grade" of type CHAR(1), which holds a letter grade from A through F without + or -.

1. Define the table stugrade in the Instructional Oracle SQL Editor.
2.  Download the text files dbex.ctl and dbex.dat from the COS 425 web site and save them in your OIT Central file server top-level (home) directory. (If you prefer to work in your CS Department Unix account, you can use the Unix command "scp" (secure copy) to copy files to and from your OIT Unix home directory.)
3. Go to the Oracle SQL Loader and bulk load stugrade using files dbex.ctl and dbex.dat.
4. Construct a SQL query that will count, for each of the 6 letter grades, the number of students in the table receiving that grade. Enter this query in the Instructional  Oracle SQL Editor. Turn in your result.