• Please see the handout Oracle 8i SQL for all kinds of useful information related to this assignment.

In this part of the project, you will issue SQL queries and updates against your PDA database, and you will experiment with the use of indexes and transactions. Since you will be modifying your data as part of this assignment, you may want to establish some kind of routine that includes reloading your database from the files you created for PDA Part 3 each time you want to get a "fresh" start. Remember to delete the contents of each relation (or destroy and recreate the relations) before reloading, as Oracle will happily load your data a second time, doubling the size of your database.

1. (SQL)
   Develop and test:

   (a) At least eight SQL data retrieval (select) commands.

   (b) At least two each of the four types of SQL data modification commands: insert a single tuple, insert a subquery, delete, update.

   Please note:

   • For this assignment you will be invoking your SQL commands interactively through sqlplus, as described in the handout Getting Started With Oracle. Of course you should certainly build a script file, rather than typing in the queries each time you run them.

   • Please write "interesting" queries. You should try to use most or all of the SQL constructs discussed in class and in the textbook (subqueries, aggregates, set operators, etc.). You will not receive full credit if your queries and modifications are all extremely simple.

   • We suggest that you experiment with your SQL commands on your small hand-created database before running them on the large database for which you generated data. Initial debugging is much easier when you're operating on small amounts of data. Once you're confident that your commands are working, try them on your large database.

   • If you discover that most or all of your "interesting" queries return an empty answer on your large database, check whether you followed the instructions in PDA Part 3 for generating data values that join properly. You may need to modify your data generator.

   • Turn in a copy of all of your SQL commands, along with a script illustrating their execution. Your script should be sufficient to convince us that your commands run successfully. Please do not, however, turn in query results that are hundreds of lines long! There are some further instructions about scripts at the end of this handout.

2. (Indexes)
   In Problem #1 you may have discovered that some queries run very slowly over your large database. As discussed in class, an important technique for improving the performance of queries is to create indexes. An index on an attribute A of relation R allows the database to quickly find all tuples in R with a given value for attribute A (which is useful when evaluating selection or join conditions involving attribute A). An index can be created on any attribute of any relation, or on several attributes combined. The syntax for creating indexes in Oracle is given in the handout Oracle 8i SQL. Please pay careful attention to the information about Stanford/Oracle-specific syntax.

   Create at least three useful indexes for your PDA. Run your queries from Problem #1 on your large database with the indexes and without the indexes. Turn in a script showing your commands to create indexes, and showing the relative times of query execution with and without indexes.

   Please note:

   • As mentioned in the handout Oracle 8i SQL, Oracle automatically creates indexes for attributes declared as keys. Thus, when you created a relation in PDA Part 3 and declared a PRIMARY KEY, an index was created, and you need not attempt to create another index on your key attributes.

   • As described in the handout Oracle 8i SQL, in order to set the system to show query execution times you must issue the command "set timing on;" at the sqlplus prompt.

   • Your timings will be affected by external factors such as system load. However, for some of your queries, with appropriate indexes you should see a consistent dramatic difference between the execution times with indexes and the times without. If others of your queries do not show performance improvement even when relevant indexes are created, please include a short note suggesting why this might be the case. (If you are completely unable to find queries whose running times are improved by creating indexes, you could try temporarily removing your PRIMARY KEY declarations, so Oracle will not create any indexes automatically.)

3. (Transactions)
   This is a trivial problem to exercise transaction support in Oracle. Since it would be difficult for you to simulate multiple users operating on your database, you will simply experiment with the properties of transaction commit versus rollback.

   (a) Show a session in which you perform one or more data modification commands, then commit the transaction using "commit;". Issue queries before and after executing the transaction to demonstrate that the modification has been made on the database.

   (b) Now show a session in which you perform one or more similar data modification commands, but then abort the transaction using "rollback;". Issue queries before and after executing the transaction to demonstrate that the modification has not been made on the database.

Turning in Scripts

Additional notes:

• Don't include syntax errors in your scripts.
• Again, please do not show query results that are hundreds of lines long.
• Make sure each query result is shown directly after the query that generated it.
• Provide comments for your queries or operations unless they are crystal clear.
• For your own sake: please read the assignment carefully.