COS 425, Spring 2000: Problem Sets

COS 425, Spring 2000 - Problem Set 3 - final version

Due at 11am, Wednesday March 8, 2000.
NOTICE: In recognition of midterms week, late penalties have been suspended for problem set submissions received by 5pm Friday, March 10. NO submissions will be accepted after that time.

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. In Database Management Systems by Ramakrishnan and Gehrke, Chapter 5, exercise 5.2, pgs. 170-171, Parts 1, 2, 5, 7.

Problem 2. In Database Management Systems by Ramakrishnan and Gehrke, Chapter 5, exercise 5.8 pgs. 174-175, Part 1 and Part 2 a, c, g, h, n.

Problem 3. 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)
);

The different types are extensions of the basic types and are described in the Language Reference linked from the SQL Web interface. However, all you really need to know is that varchar, char, tinytext and text are all types of strings so that one can use the SQL operator "LIKE".

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%'

Part 1.Write and submit SQL queries to find the following. Hand in a print-out of the results of each query. WARNING: MySQL does NOT support the full SQL-92 language discussed in the text. In particular, it does not appear to support nested SELECT statements (see Section 5.4.1 of the Manual), and HAVING clauses appear to need aggregation results that are named using an AS (see Section 7.12 of the Language Reference).

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 c: Find the ids, titles, and dates of the TRs with the word "memory" in their titles.

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.
HINT AND WARNING: your query may want to use a "group ... having" syntax. You need to check the reference mentioned above before trying this.

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 4 In Database Management Systems by Ramakrishnan and Gehrke, Chapter 7, exercise 7.18, pg. 227.

Problem 5 In Database Management Systems by Ramakrishnan and Gehrke, Chapter 8, exercise 8.8, pg. 245-246, Parts 4, 5, 9 and 11.