CS 4604 Course Project: Step 5

1. (60 points) Write an SQL database schema for your application, using the CREATE TABLE commands described in class. Make sure your application provides at least 4-5 different relations. Pick suitable datatypes for each attribute. Browse through the online ORACLE manual to determine the principal options that are available for types (your class notes may or may not cover the ones that you require and your class notes may or may not contain the right declaration). Do not forget to specify at least one key for each relation (using the PRIMARY KEY construct). Specify other choices for keys via the UNIQUE construct. Justify your choice of key(s) for each relation. If some attribute should not be NULL, use NOT NULL to specify this aspect. Also provide DEFAULT values wherever applicable. An example of a complete DDL for one relation is:

   CREATE TABLE Students
   (
       sid CHAR(6) PRIMARY KEY,
       ssn CHAR(9) UNIQUE,
       name CHAR(30),
       birthdate DATE,
       gender CHAR(1) NOT NULL,
       address VARCHAR(255)
   );
   

   But do not start loading them into ORACLE just yet!
   
   
2. (10 points) For each of the relations that you defined, indicate any foreign key (referential integrity) constraints that are expected to hold for that relation. Include the declaration of foreign key constraints for any appropriate relations. Also mention any domain specific constraints that you might require of your attributes, using the CHECK construct.
   
   Now, execute some sample INSERT, DELETE and UPDATE commands on every one of your relations. Convince yourself that things are working just fine. Report any interesting observations that you make.
   
   
3. (30 points) Develop a substantial amount of data for your application and load it into your relations using the bulk loading facility provided in ORACLE. Browse through the ORACLE manual, particularly the SQL*Loader section which describes this facility. There is also an "INFILE" construct that you can use in conjunction with ORACLE DDL commands. This section of the manual also describes file formats, lengths, data types etc.
   
   We are looking at data in the order of 40-50 tuples for each relation in your application. To create the data, either get it from some web source or information repository (making sure that it is public first, of course) or write a program in any scripting/programming language (like Perl, TCL or C) that creates large files of records in a format acceptable to the bulk loader. Either way, realize that you may need to transform data from one form to another to be acceptable for use in ORACLE. It is okay to "cookup" data, so feel free to write some "randomized algorithm" that generates tens of tuples for use in the database.
   Sometimes, some of your relations might require only 4-5 tuples; examples are if you are modeling the real-estate business, there might be only a few realtors in a small town like Blacksburg, in which case it is okay to have just a few tuples in that particular relation. In such cases, where you have only a few tuples, state explicitly why you have only a few tuples.
   Precautions in "Cooking up": In parts (1) and (2), notice that you would have specified key(s) (and possibly foreign keys) for each relation. Make sure that while cooking up data, your fabricated data indeed confirms to such "key restrictions". If you declare that "StudentID" is the key, then do not generate data for different students having the same ID! Also notice that relations will definitely share attributes (if there are no common attributes at all in your schema, then your project is in grave danger, see the instructor immediately!), so make sure that they "agree" on the common attributes. For example, if there are two relations that share an attribute called "name", then make sure that the names do indeed tally! Do not list the name "Mark" as "MARK" in one and "maRK" in another and "Mark" in yet another because then they would be three different names! The bottomline is be consistent. When two things are meant to be the same, make sure your data reflects this fact. If you do not ensure this at this stage, believe us, you will spend more time debugging your application than otherwise!

1. List of your relation schemas (this is just reproduced from Step 4); this is just to remind us.
2. List of your SQL CREATE TABLE commands (with PRIMARY KEY, NOT NULL, DEFAULT, FOREIGN KEY, and CHECK constraints).
3. List of the number of tuples for each relation in your application.
4. Samples of INSERT, DELETE, and UPDATE commands that you tried out and that worked.
5. Where you got the data from: Either say "We got it from this web address", "We got this from this reference book on apartment homes", "We imported it from this document", or "We cooked it up". Whatever your answer, write a short paragraph on how you transformed it from its native form to the form required by Oracle. If you wrote a special purpose program or script (in Perl/TCL/something), include that script. Explain how you took special care to make sure that key restrictions and common attribute constraints are not violated. Explain why some relations have only a few tuples (if applicable).
   
6. Sample of tuples for each relation (about 6-10 each), just to give us an idea for how they look.
7. IMPORTANT: In your writeup, include the login ids of every group member in your project. We need this information to setup permissions for the future steps of the project.

1. Pages and pages of raw data or printouts of all the data in your database. Please, there is no way we can comb this stuff manually. You will have ample opportunity to present this stuff later on in the semester.

1. This might seem obvious but it is surprising the number of people who miss it. Please *SAVE* a copy of your SQL statements (and data) in some disk or floppy or somewhere. It is just safe.
   
   

1. Feng Min's Oracle Help Page
2. Oracle Frequently Asked Questions
3. Altering Table Definitions (If for some reason you made a mistake in entering the SQL DDLs, you will need to know how to alter the definitions)