THE PROJECT

Personal Database Application, Part 4

(a) SQL

Develop and test:

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

• For this assignment you will be invoking your SQL commands interactively through psql.. 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. We do expect that the commands you turn in work properly on both your small and large databases.

• 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 on both your small or large databases. Your script should be sufficient to convince us that your commands run successfully, but you can and should truncate query results after a few lines. Please do not turn in query results that are hundreds of lines long.

(b) Indexes

In part (a) 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 PostgreSQL is given in the document Indexes in PostgreSQL.

Create at least three useful indexes for your PDA. Run your queries from part (a) 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. Here too, please truncate any large query results.

You can use an execution script file that creates your indexes to ease your testing. Use the \i command (no semicolon) to call your queries script file both before and after you create the indexes. Surround the calls to execute the queries files with lines that execute SELECT timeofday(); so you can measure elapsed time. You can then easily calculate elapsed time manually and add it to your script log as a comment. You can note your added comments by enclosing them with /*...*/. For an added challenge you can try to get your execution script file to calculate the elapsed time and display it for you.

Please note:

• Like Oracle, PostgreSQL automatically creates indexes for attributes declared as primary keys.

• As described in the document Explain in PostgreSQL, you can use the EXPLAIN command to get the optimizers estimate of how the optimizer plans to execute a query and how long the optimizer expects it will take.

• Actual 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. The plans described by the EXPLAIN should be helpful in understanding why things take as long as they do.

What to turn in

Components (a) and (b) of this project part each tell you what should be recorded in the script log that you turn in. In this and all subsequent project parts, the material you turn in should be clearly formatted and delineated, and should include comments for any aspects that are not crystal clear. Poorly assembled or documented material will not receive full credit, even if it is correct. Other than comments, truncation, and simple formatting, it is Academic Dishonesty to edit script logs before turning them in.

For this assignment the following files should be turned in electronically using your cats account and the submit program:

README	Please give your name, project part number, course number, date, the title of your project, a list and description of the files you are submitting, and any other information that will be useful for the grader.
queries.script	An execution script file showing all of your SELECT queries.
queries-small.log	A script log illustrating the execution of your SELECT queries on your small database. Your script should be sufficient to convince us that your commands run successfully.
queries-large.log	A script log illustrating the execution of your SELECT queries on your large database. Your script should be sufficient to convince us that your commands run successfully, but you can and should truncate query results after a few lines. Please do not turn in query results that are hundreds of lines long.
indexes.log	A script log showing a record of the execution of your commands to create indexes, and showing the relative times of query execution, with and without indexes, on your large database. Here too, please truncate any large query results.
mods.script	An execution script file showing all of your UPDATE, DELETE, and INSERT commands.
mods.log	A script log illustrating the execution of your UPDATE, DELETE, and INSERT commands on either your small or large database. Your script should be sufficient to convince us that your commands run successfully, but you can and should truncate query results after a few lines. Please do not turn in query results that are hundreds of lines long.