CS145 Project Part 4
CS145 - Introduction to Databases
Spring 2000, Prof. Widom
Personal Database Application: Part 4
Due Monday May 1
- Please see the handout Oracle
8i SQL for all kinds of useful information related to this
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.
Develop and test:
(a) At least eight SQL data retrieval (select)
(b) 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 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
- 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
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
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
- 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
- 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.)
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
(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
(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
A script must be turned in on paper (no email submissions)
demonstrating that you've performed the assignment as specified above.
It is an Honor Code violation to edit scripts before turning them in,
and as usual we will pursue aggressively all suspected cases of Honor
- 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.