CS145 Assignment #3
Due 2:45PM Thursday, October 28, 2004
On-Line Submission of PDA
You should submit your PDA
assignments from this one onward, using the on-line submit script found in
Detailed directions are in a "README" file in the same directory.
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 in your directory
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
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 2:45PM Thursday, we will make sure
we are aware of any systems problems, and extend the deadline if
That is, we want you to finish up and go to class on Thursday afternoons,
but we're not going to be overly concerned if you need to wait for
system availability before submitting the work done before 2:45PM.
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.
Write an SQL database schema for your PDA, using the CREATE
TABLE commands described in the handout
Getting Started With Oracle
or Section 6.6 of the text.
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, so
please use this type.
Choose and declare
primary keys and (if appropriate) other unique attributes or
sets of attributes.
Unless it really doesn't make sense in your chosen application, declare
at least one foreign key. You do not have to declare all possible
foreign keys; you may want to add more later when we explore constraint
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
Show the response of sqlplus to a request to describe each of your
For example, to see the schema for relation Foo type
Execute five INSERT commands to insert tuples into one of
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.
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
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 a "toy" database.
The data you generate and load should be on
the order of:
- At least two relations with a few thousand tuples each.
- At least one additional relation with (at least) hundreds of tuples.
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 make sure 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
Be sure not to generate duplicate values for keys of your relations or
for other (sets of) unique attributes.
- 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
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.