Saturday, December 5, 2009

Temporary Tables and Data Types Exercises


I N D I V I D U A L     E X E R C I S E S

HANDS-ON #1: Temporary tables


Your project is complete and now you are ready to start testing. Your application reads data from one table, processes portions of the data and makes some changes, then inserts the changed data into two additional tables before committing the work. Errors are captured in an external SPOOL file for evaluation and aid in debugging after each test of the application. The process of checking the errors, recording the data, and then deleting the records before the next test run of the application is somewhat time-consuming. The notion of creating a set of temporary tables for the output tables seems to be a good solution.
For this part of the exercise, write and execute the SQL commands to create two temporary tables.
  • The first table should be named CUSTORD_TEMP and should have columns for customer name and total order amount.
  • The second table should be named CUSTORDTOT_TEMP and should have columns for customer order number, total state tax, and total shipping charges.
In your own words, explain how you might use these tables for testing your application.
Place and save your answers in a Word document named week5_exercise.doc. You will also have a script and output file that you will include with your other files to turn in.
HANDS-ON #2: New data types
You are going to create a new table to be housed in the USERS01 tablespace. The table name will be PRECIOUS_METAL_PRICE and it will be used to store a history of the price of various precious metals. New rows are added to the table on a one-every-two-hours ratio. The table needs to have a relationship back to a parent table named METALS, which lists various precious metals like Gold, Silver, Zinc, etc.  The table has a four-byte primary key ID number (METAL_ID) and a description of the metal. As you write the CREATE TABLE statement for this new table, you need to address the following points: The columns are named PRICE, PRICE_DATETIME, and TIME_BETWEEN. The following offers some additional direction on how the table should be created.
  • The PRICE can contain fractions of a penny down to thousandths of a penny, but the whole price will always be under 1000 dollars. A column named TIME_BETWEEN will store the number of days (up to 99 days), hours, minutes, and seconds (to the hundredth of a second) between the current record and the previous record. The table is never updated; only new data is inserted.  Therefore, you want to minimize the storage space saved for updates.
  • Taking an average row length as 24 bytes, and an average of 12 inserted records per day, you want the table to be created with enough storage space for approximately 6 months; but at the same time, you do not want to waste unused space (try to be as exact as you can).
You do not need to run this part of the exercise in your database, unless you want to create the METALS table as well.
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)