CS145 Assignment #3

Due Tuesday, October 23, 2001

On-Line Submission of PDA

We believe it is feasible for you to submit your PDA portions from this assignment onward using the on-line submit script found in /usr/class/cs145/bin/submit. If you execute this script, it will interact with you and allow you to submit the contents of one directory, in which you should have placed all the files you want to submit. Please include a README file telling us everything that is in the directory and what role the files play in answering the various parts of the assignment. The submit script knows your login name and will label the copy of your directory accordingly. If your login name doesn't make it obvious who you are, please put your name in the README file.

As you may know, the submit script is "one-way." You cannot change submitted files, but you may submit the directory again. It will then appear to the course staff in a new directory with your login name and a higher number than your previous submission. To avoid things getting out of hand, there is a limit of five submissions per week. Also, to avoid a situation where people are tempted to submit early in case the system goes down just before 9:30AM Tuesday, we will make sure we are aware of any systems problems, and extend the deadline if necessary. That is, we want you to finish up and go to class on Tuesday mornings, but we're not going to be overly concerned if you need to wait for system availability before submitting the work done before 9:30AM.

At this time, your written (non-PDA) part of the assignment must be submitted in the usual manner: hardcopy in class or to Ms. Weden (or to SITN if you are a TV student).

Step 3 of Your PDA (Personal Database Application)

Note1: see Recording Your Session in the on-line Getting Started With Oracle document for a guide to preparing output to hand in with your assignment. It will be useful for this and subsequent PDA parts.

Note2: Oracle is not being backed up. You need to save anything you need long-term in the leland file system.

(a)
Write an SQL database schema for your PDA, using the CREATE TABLE commands described in the handout Getting Started With Oracle. Pick suitable datatypes for each attribute. Page 292-293 of the text gives you the principal options regarding types, but Oracle likes you to use VARCHAR2 for character strings. Hand in a file with the commands you use to create your database schema (it is a good idea to keep this file for the balance of the course). Show the response of sqlplus to a request to describe each of your relation schemas. For example, to see the schema for relation Foo type 
     DESCRIBE Foo;
(b)
Execute five INSERT commands to insert tuples into one of your relations. Show the response of sqlplus and the relation that results when you issue a SELECT * command. Again, the information on how to do this step is in Getting Started With Oracle.

(c)
Develop a substantial amount of data for your database and load it into your relations using the SQL load command. See The Oracle Bulk Loader for information on how to bulk-load data. To create the data, write a program in any programming language you like that creates large files of records in a format acceptable to the Oracle bulk loader, then load the data into your PDA relations. If you are using real data for your PDA, your program will need to transform the data into files of records conforming to your PDA schema. The rest of you will write a program to fabricate data: your program will generate either random or nonrandom (e.g., sequential) records conforming to your schema. Note that it is both fine and expected for your data values--strings especially--to be meaningless gibberish. The point of generating large amounts of data is so that you can experiment with a database of realistic size, rather than the small "toy" databases often used in classes. The data you generate and load should be on the order of:

If the semantics of your application includes relations that are expected to be relatively small (e.g., schools within a university), it is fine to use some small relations, but please ensure that you have relations of the sizes prescribed above as well. When writing a program to fabricate data, there are two important points to keep in mind:

(1)
Although you have not (yet) declared keys in your relations, in many cases you probably know that an attribute or set of attributes in a relation will serve as a key. If so, be sure not to generate duplicate values for these attributes.

(2)
Your PDA almost certainly includes relations that are expected to join with each other. For example, you may have a Student relation with attribute courseNo that's expected to join with attribute number in relation Course. In generating data, be sure to generate values that actually do join--otherwise all of your interesting queries will have empty results! One way to guarantee joinability is to generate the values in one relation, then use the generated values in one relation to select joining values for the other relation. For example, you could generate course numbers first (either sequentially or randomly), then use these numbers to fill in the courseNo values in the student relation.

Turn in your program code for generating or transforming data, a small sample of the records generated for each relation (5 or so records per relation), and a script showing the loading of your data into Oracle.

Problem Set

Remember: this part is submitted the "old-fashioned way," in hardcopy.

  1. Consider a database with the following relations: Write the following queries in relational algebra.

    (a)
    Find the foods that include more than 16 units of butter.

    (b)
    Find the cuisines that use cumin as an ingredient (in at least one dish).

  2. Consider a database with the relation Employee (name, location, salary, manager). Write the following queries in relational algebra.

    (a)
    Which employees have a higher salary than their own manager?

    (b)
    Which employees have a higher salary than any manager?

    (c)
    Which managers are managing employees that are collectively located in more than one location? (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.

  3. Consider a database with the following relations: Write the following queries in relational algebra.

    (a)
    List all the houses that were sold in 2000 with more bathrooms than bedrooms. List the address, city, numbers of bedrooms and bathrooms, and date sold. You may assume dates can be compared by "<" meaning "earlier," and you may represent specific dates in any appropriate notation, such as 12/31/2000.

    (b)
    List all houses that were sold and then later sold again for less money. Include the address, city, and both dates sold and both prices.

  4. Consider a database with the following relations: Write the following queries in relational algebra.

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

    (b)
    List people who own a car and also own a motorcycle.

    (c)
    Can you list people who own neither a car nor a motorcycle? If yes, show how. If not, explain why not.

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

    (a)
    List all the houses that were sold in Santa Cruz in the year 2000. Include the address, city, bedrooms, bathrooms, and date sold.

    (b)
    List the price per sqft of all sales in Santa Cruz since January 2000. Include the address, city, date sold, and price per sqft.

    (c)
    List all houses that were sold and then later sold again for less money. Include the address, city, and both dates sold and both prices.

  6. Does the following law, which holds for sets, also hold for bags? R intersect S = R - (R - S).