CS145 Assignment #4

Due Tuesday, Oct. 30, 2001

Step 4 of Your PDA

This week we shall write and run some SQL queries. It is suggested that you begin to follow the routine of loading your database from your load file, running queries or other commands from a file that contains those SQL statements, and then deleting your data so it doesn't clutter up the database all week and (worse) you don't forget and load the same tuples several times into your relations. Remember that SQL thinks of relations as bags, and so will happily let you insert the same tuple as many times as you ask it to. To clean out a relation R without deleting its schema, use command DELETE FROM R;.

Please use the on-line submit procedure, as described in Homework 3 to turn in your PDA. Give us hardcopy of your written part, as before.

Write five queries on your PDA database, using the select-from-where construct of SQL. To receive full credit, all but perhaps one of your queries should exhibit some interesting feature of SQL: queries over more than one relation, or subqueries, for example. We suggest that you experiment with your SQL commands on a small database (e.g., your hand-created database), before running them on the large database that you loaded in PDA part 3. Initial debugging is much easier when you're operating on small amounts of data. Once you're confident that your queries are working, run them on your complete 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 Assignment #3 for generating data values that join properly. You may need to modify your data generator accordingly. Turn in a copy of all of your SQL queries, 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 thousands (or hundreds of thousands) of lines long!

Write five data modification commands on your PDA database. Most of these commands should be ``interesting,'' in the sense that they involve some complex feature, such as inserting the result of a query, updating several tuples at once, or deleting a set of tuples that is more than one but less than all the tuples in a relation. As for the queries in (1), you might want to try out your commands on small data before trying it on your full database. Hand in a script that shows your modification commands running in a convincing fashion.

Create two views on top of your database schema. Show your CREATE VIEW statements and the response of the system. Also, show a query involving each view and the system response (but truncate the response if there are more than a few tuples produced). Finally, show a script of what happens when you try to modify your view, say by inserting a new tuple into it. Are either of your views updatable? Tell why or why not? (Updatable views are discussed in Section 5.8.4 of the text. Essentially, a view is updatable if it is a selection on one base table.)

In part (1) you probably discovered that some queries run very slowly over your large database. 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 find quickly all tuples in R with a given value for attribute A. This index is useful if a value of A is specified by your query (in the where-clause). It may also be useful if A is involved in a join that equates it to some other attribute. For example, in the query
```
     SELECT Bars.address
     FROM Drinkers, Bars
     WHERE   Drinkers.name = 'joe'
         AND Drinkers.frequents = Bars.name;
```
we might use an index on Drinkers.name to help us find the tuple for drinker Joe quickly. We might also like an index on Bars.name, so we can take all the bars Joe frequents and quickly find the tuples for those bars to read their addresses.
In Oracle, you can get an index by the command:
```
     CREATE INDEX <IndexName> ON <RelName>(<Attribute List>)
         TABLESPACE INDX;
```
Note:
- The text on the second line (``tablespace indx'') is unique to the Stanford Oracle installation and is designed to get our indexes onto a second disk used only for that purpose, so one disk can be retrieving data while the other is using an index.
- Oracle creates indexes automatically when you declare an attribute(s) PRIMARY KEY or UNIQUE. Thus, some indexes may exist before you create them and may also be making certain queries run faster than than they would with no indexes at all. Be sure to take this factor into account when trying to explain differences in running times. If you are unable to discover queries whose running-time is affected by the indexes that you declare, you could try temporarily removing your key declarations, so Oracle will not create indexes ``behind the scenes.''
If the attribute list contains more than one attribute, then the index requires values for all the listed attributes to find a tuple. That situation might be helpful if the attributes together form a key, for example. An illustration of the CREATE INDEX command is
```
     CREATE INDEX DrinkerInd ON Drinkers(name)
         TABLESPACE INDX;
     CREATE INDEX BarInd ON Bars(name)
         TABLESPACE INDX;
```
which creates the two indexes mentioned above. To get rid of an index, you can say DROP INDEX followed by the name of the index. Notice that each index must have a name, even though we only refer to the name if we want to drop the index.
Create at least two useful indexes for your PDA. Run your queries from part (1) on your large database with the indexes and without the indexes. To time your commands, you may issue the following commands to sqlplus:
1. TIMING START <TimerName>; starts your timer. Give it whatever name you wish.
2. TIMING SHOW; prints the current wall-clock time of your current timer. (There is a way to switch among timers, which is why they are named, but we shall not use this feature.)
3. TIMING STOP; prints the current time of your timer and stops it.
Naturally these times may be affected by external factors such as system load, etc. Still, you should see a dramatic difference between the execution times with indexes and the times without. Turn in a script showing your commands to create indexes, and showing the relative times of query execution with and without indexes.

Note: Often, students discover that indexes appear to slow down the execution of queries. There are two issues you should consider:

A query involving several relations will not speed up unless indexes to support all of the selections and equijoins are available. A single index may only increase the number of disk I/O's needed (to get index blocks), without affecting the query as a whole.
The second time you run a query may take much less time than the first, because the second time data is cached in main memory. Data may be cached in the main memory of the machine running Oracle. If many students are using Oracle at the same time, the Oracle machine's cache will probably drop your data if you wait a few seconds. We suggest that you perform only one timing experiment in a session; at least exit from sqlplus and start it again. If you are getting strange results, you may have to wait several minutes between runs.

Problem Set

Consider a database with the following relations:
Write the following modifications in SQL.

(a)

Change the ingredients for foods so that they include no more than 16 units of butter. (If they already include more than 16 units of butter, then change it to 16 units of butter.)

(b)

Remove beef from all Indian (cuisine) dishes.

(c)

Create a summary table, ShoppingList that contains the total quantity of each ingredient used by all the dishes for each cuisine.

(d)

For each dish, set the number of calories equal to the total number of calories for the entire dish. Note that the quantity in relation Calories need not match the quantity in Ingredients, so you need to compute calories per unit.
Consider a database with the relation Employee (name, location, salary, manager). Write the following queries in SQL.

(a)

For each manager, list the employees who have the highest and lowest salaries.

(b)

List the average salary by location.

(c)

Which managers are managing employees that are collectively located in more than two locations? (Remember: name is a key, so an employee has only one location.)

(d)

Produce a table giving, for each manager, the total salary of all the employees working for that manager.
Consider a database with the following relations:
Write the following queries in SQL.
(a)

List people who own a car but do not own a motorcycle.

(b)

List the number of cars owned by each person.

(c)
Write the SQL request that populates the table
```
Ownership(owner, numberOfCars, numberOfMotorcycles)
```
with all the owners in Cars and in Motorcycles and includes the counts of their cars and motorcycles. Make sure this request works for people who own both car(s) and motorcycle(s), car(s) but not motorcycle(s), and motorcycle(s) and not car(s).
Consider a database with the following relations:
Write the following queries in SQL.

(a)

List all the courses taught by Prof. Smith.

(b)

Count the number of times that each instructor is listed as teaching a course. If an instructor has taught the same course multiple times, each time counts.

(c)

Count the number of courses taught by each instructor. If an instructor has taught the same course multiple times, that course counts only once.
Consider a database with the following relations:
Write the following queries in SQL.

(a)

List the students that have taken a course by Prof. Smith.

(b)

List the students that have taken every course taught by Prof. Smith. Note: students can take each course only once, and they need not have taken the course from Prof. Smith.

(c)

List the students who have taken courses only when taught by Prof. Smith.

(d)

List the students who have not taken any courses when they were taught by Prof. Smith. Note: assume someone is a student if they have taken at least one course.