• The procedure for turning in this assignment and the late policy are exactly the same as for Assignment #1.

• The grading (and non-grading) of exercises and challenge problems will also proceed as in Assignment #1.

• Note about SQL on Oracle: If you decide to test your solutions to the SQL written exercises using Oracle, note that Oracle's implementation of SQL differs somewhat from the SQL standard as covered in the textbook and class. These differences are outlined in the document Oracle 9i SQL, available through the course Web site Project page.

Exercises

1. In the following textbook exercises we'll use computers this time instead of battleships. For a complete description of the schema please see Exercise 5.2.1 on page 207. Here are a few additional clarifications:
   • The "manufacturer" is what's stored in the attribute called maker.
   • In each of the four relations, attribute model is a key, and there are no other keys.
   • You may assume that attribute rd in relation PC is a string containing both the speed (e.g., 12x) and type (CD or DVD) of the removable disk.

   Note that altogether this problem asks for a gigantic number of SQL queries. Feel free to pick and choose - you will get full credit if you write a reasonable fraction and variety of the queries. If you do them all, you will be a true SQL expert.

   (i) Do parts (c) and (d) of Exercise 6.1.3 in the textbook (pages 252-253). Write the SQL queries only - you do not need to show the results on the sample data although you are free to do so if you wish. Note for part (c) that "any PC" should be taken to mean "some PC."

   (ii) Do parts (c) and (e) of Exercise 6.2.2 in the textbook (pages 262-263). Write the SQL queries only - you do not need to show the results on the sample data although you are free to do so if you wish.

   (iii) Do parts (b), (c), and (d) of Exercise 6.3.1 in the textbook (page 274).

   (iv) Do Exercise 6.4.3 in the textbook (page 284) for your six queries (three parts, two queries each) from part (iii). We suggest that you do the "if so" part of the problem (rewriting to eliminate duplicates) but not the "if not" part of the problem (rewriting to eliminate subqueries), unless you are so inclined. A further exercise is to rewrite the queries using aggregation.

   (v) Do parts (b), (d), and (g) of Exercise 6.4.6 in the textbook (pages 284-285). Write the SQL queries only - you do not need to show the results on the sample data although you are free to do so if you wish.

   (vi) Do parts (b), (d), and (g) of Exercise 6.5.1 in the textbook (pages 290-291). Write the SQL modifications only - you do not need to describe the effects on the sample data although you are free to do so if you wish.

2. Consider the XML data in Figure 4.23 on page 182 of the textbook. Your queries should work on any XML data conforming to the DTD given in Figure 4.22 on page 181.

   (a) Write an XPath expression to find the names of all stars whose street or city is "Hollywood."

   (b) Write an XPath expression to find the titles of all movies made after 1975.

   (c) Write an XPath expression to find all stars whose second listed movie has the word 'Strikes' in its title.

   (d) Write an XQuery expression to find all pairs of star names where the two stars acted in a movie together. Assume that a movie is uniquely identified by its title. The elements in your query result should be of the form:

     <Pair>
       <Name1>...</Name1>
       <Name2>...</Name2>
     </Pair>
   

   Stars should not be paired with themselves (you may assume that star names are unique), and each pair should occur exactly once, not once in each order.

   (e) Write an XQuery expression to find the names of all stars who made at least one movie in a year earlier than the average year in the database.

Challenge Problems

1. Specify a real-world relational schema (i.e., not just "R" and "S") and then write a SQL query Q over the relations such that:
   • Q has one EXISTS or IN subquery in its WHERE clause (in its "positive" version, i.e., do not use NOT EXISTS or NOT IN).
   • Q cannot be rewritten as an equivalent query Q' without a subquery such that Q' is guaranteed to always return the same number of duplicates in the result as Q, even when Q' includes the keyword DISTINCT.
   Your query Q does not need to use aggregation or any other "fancy" SQL constructs.

2. Consider a relational schema Student(ID,GPA), where ID is a key. Are the following two queries always equivalent? Either way, justify your answer.

     select avg(GPA) from Student
     select sum(GPA)/count(*) FROM Student
   

3. We didn't cover these features in class, but both XPath and XQuery have a special syntax for "dereferencing" IDREF attributes. In other words, when specifying an XML traversal in XPath or XQuery, there is a way to traverse through an IDREF attribute @A to the element E that A points to, and continue traversing from E. Suppose neither XPath nor XQuery provided such a feature. Is it possible to "emulate" IDREF dereferencing behavior in XPath using other constructs in the language? How about in XQuery? If it helps, you may assume there is a known DTD for the XML data being queried.

Project Part 2

Partners

Part A

  Grades(name CHAR(15), course CHAR(9), year CHAR(2), quarter CHAR(6), grade CHAR(2))

Create a transcript that shows the successful creation and loading of the Grades table, as well as the execution of at least two SQL queries involving both tables, and one SQL modification (insert, delete, or update) on each table.

Part B

In addition to the various help documents and Web sites mentioned in Assignment #1, you will find the following documents useful for this part of the project:

• Introduction to Pro*C Embedded SQL for C users
• Introduction to JDBC for Java users

You may continue to use the small database we are providing, with the Grades table in addition to the Courses table enabling somewhat more complex queries. (If you prefer to create and use a different database you are welcome to do so.) Your extended Web front-end and connection to Oracle should include at least the following features:

1. One or more input boxes, where the value typed by the user becomes a parameter to a SQL query issued to Oracle (with results displayed in the Web page as usual).

2. One or more drop-down menus, where again the value selected by the user becomes a parameter to a SQL query issued to Oracle. If you wish you may use both input boxes and menus to gather parameters for a single query.

3. The ability to modify the database in some parameterized fashion through the Web interface (inserts, deletes, or updates with input from the user).

4. N-at-a-time browsing , where the first N tuples in a query result are displayed, then the user must request the next N to be displayed, etc. (This behavior is similar to search engines, and to many other search features on the Web that return more than a few results.) It's up to you whether to support previous-N as well as next-N; the former is not required. It's also up to you whether N is hard-coded or a parameter that can be input by the user.

5. Link-based browsing, where a result page presented by your program contains hyperlinks on certain attribute values (typically join attributes). Clicking on a hyperlink should issue another SQL query related to the item being clicked on, and generate another result page in HTML as usual. Here's an example: Suppose a query returns a subset of the rows in the Courses table, displayed in HTML. The values in column courses can be displayed as hyperlinks, where clicking on one of these links issues a query to find all entries in the Grades table for that specific course.

• Hints for Project Part 2, C users (http://www.stanford.edu/class/cs145/hw/hw2hint.html)
• Hints for Project Part 2, Java users (http://www.stanford.edu/class/cs145/hw/hw2JavaNotes.html)

Note on maintaining your databases

1. Oracle is not backed up, so anything you need long-term should be saved in the leland file system.
2. As you start modifying data in a database, you will undoubtedly want the ability to get a "fresh start" easily from your original data.

  drop table T;

  delete from T;

Browser compatibility

When the preapproval process is not followed, projects that have problems on the Sweet Hall Solaris Netscape browsers may lose points, possibly all points if we cannot run your Web interface at all.

What to submit

• For C users, all source files that you created or modified, including the Makefile if you modified it and all relevant .html files.

• For Java users, your entire servletdir directory, including all static .html documents in the webpages subdirectory and the Java Servlet code in the servlets subdirectory.