• Important: we need all students to change their Oracle passwords as soon as possible. On Thursday morning April 20 we will deactivate all Oracle accounts for which the password has not been changed.

• Please see Recording Your Session from the Getting Started With Oracle handout for a guide to preparing output to be handed in for this and subsequent PDA assignments.

• Note that Oracle is not backed up. Anything you need long-term should be saved in the leland file system.

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:

   • At least two relations with thousands of tuples
   • At least one relation with hundreds of tuples

   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:

   (a) You will need to make sure not to generate duplicate values for key attributes.

   (b) Your PDA almost certainly includes relations that are expected to join with each other. For example, you may have a Student relation with attribute courseNum that's expected to join with attribute number in relation Course. When generating data, be sure to generate values that actually do join - otherwise all of your interesting queries will have empty results! There are a couple of ways to properly generate joining values. One way is to generate records for multiple relations (e.g., Course and Student) at the same time. Another way is to generate the records for one relation first, and then use the joining values for the other relation. For example, you could generate records for relation Course first, then use the Course.number values when creating values for Student.courseNum.

   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

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;".