CSci 275 TDA Part 3: Schema Creation and Loading
Due: Tuesday, 26 March (5 March) 2013 at the beginning of class.
In this part of the project you will bring your TDA to "life," i.e., you will implement
the schema and populate it with tuples. Please make sure to read the assignment carefully
and understand exactly what to hand in for each part!
- (20 points)
Write an SQL database schema for your TDA, using the CREATE TABLE commands. Pick
suitable data types for each attribute.
Hand in a printout of the commands you use to create your database schema
(it is a good idea to keep this file for the balance of the course.
In addition, hand in a copy of the created table generated by using the ORACLE describe command.
Make sure your schema enforces all the referential integrity constraints that
should hold on the relations. Use unique, primary key, and foreign key-referencing
clauses within your create table statements so that ORACLE will enforce the uniqueness
of keys and will enforce referential integrity. You may use the default option for
handling referential integrity violations (i.e., violations are not allowed and generate errors).
In addition, you may specify other attribute value constraints that you want the
DBMS to enforce for you.
- (10 points)
Execute five INSERT commands to insert tuples into different relations.
Show the response of ORACLE and the relation that results when you issue a SELECT *
command by printing and submitting a copy of your query results.
- (20 points)
Develop a substantial amount of data for your database and load it into your relations
using the SQL Loader utility. To create the data, generate a couple of large files of
records in a format acceptable to the SQL Loader, then load the data into your TDA relations.
Alternatively, if you are using real data for your TDA, your program will need to
transform the data into files of records conforming to your TDA schema.
The rest of you may 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 that your
data values—strings especially—may 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 one relation with
thousands of tuples and at least two relations with 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 ensure that you have relations of the sizes prescribed above as well.
Hand in your program code for generating or transforming data, a VERY 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 using the SQL Loader utility.