CS145 - Introduction to Databases
Spring 2000, Prof. Widom

Personal Database Application: Part 3
Due Monday April 24

In this part of the project, you will create a relational schema for your PDA in the Oracle database system, and you will populate the tables in your database with initial data sets.

  1. Familiarize yourself with the Oracle relational DBMS by reading the handout Getting Started With Oracle, logging into Oracle, changing your password, trying some of the examples in the handout, and experimenting with the help command. You don't need to turn anything in for this part.

  2. Create relations for your PDA based on your final relational schema from PDA Part 2. In addition to creating the appropriate attributes and types, please declare keys for your relations; see Creating a Table With a Primary Key from Getting Started With Oracle (not included in the printed version, unfortunately). Many of the attribute types supported by Oracle are listed on page 286 of the textbook. If you have an attribute that represents a date and/or time, you may want to look at our help page on Oracle Dates and Times.

    Turn in a script showing an Oracle session in which your relations are created successfully. Also show, for each relation, the result of the sqlplus DESCRIBE command once the relation has been created: for a relation Foo, type "DESCRIBE Foo;".

  3. For each relation in your PDA, create a file containing a few (approximately 5-10) records of "realistic" data. Then use the Oracle bulk-loading facilities to insert those records as tuples into your relations. Refer to the handout Using the Oracle Bulk Loader for file format and how to load records into Oracle.

    Turn in a listing showing the contents of the files you created, the successful loading of the data into Oracle, and the execution of "SELECT *" commands to show the contents of each relation. (Again, information is available in Getting Started With Oracle.)

  4. Write a program in any programming language you like that creates large files of records for each of your PDA relations. If you have available real data for your PDA, then your program will need to transform the data into files of records conforming to your PDA schema and to Oracle's load format. The rest of you will need to 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 your application naturally includes relations that are expected to be relatively small (e.g., schools within a university), then 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:

    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 successful loading of your data into Oracle.

Maintaining your databases

You will be using both your small (part 3) and large (part 4) databases for the rest of the course. The idea is to use the small database to experiment on meaningful-looking data, and the large one to experiment on data of more realistic size. You can keep the two databases going in parallel either by reloading the data each time you use it, or by creating two sets of relations with slightly different names.

In Oracle, to get rid of a table called Foo, issue the command "drop table Foo;". If you want to get rid of all tuples in Foo without deleting the table itself, issue the command: "delete from Foo;".