File=cmis565_lab5.htm; updated June 19, 2013

 

Oracle Database Administration – Lab #5

Tables, Indexes, Integrity Constraints,

Clusters, and Undo Tablespaces (40 points)

 

Situation: In this laboratory you will continue to develop your database.  You may elect to complete the laboratory individually, or you may team up with 1 to 2 laboratory partners.  If you team up with partners, you will select your own partners and you will be graded as a group and you will submit a single document as evidence of your team's work.  The tables and data you enter into your database during completion of this laboratory will support your work on future laboratories, so it is essential that you complete this laboratory successfully.

 

Use Notepad, Wordpad, or Microsoft Word to document your solution to the tasks in the laboratory. 

·         Label your answer to each clearly by typing the number/letter (example 2a) in bold print.

·         Show each command you execute and the computer's response (example response:  Database Altered, Tablespace Created, etc.).  If you don't show the commands you execute, then your answer will be counted as wrong.

·         Print your output using 10 point Courier or Courier New font (so the output lines up and is easy to read).

·         Format your output as necessary to make it easy to read.  Do not submit for grading anything you would not submit to your boss for review in an actual work environment, i.e. in other words, do not turn in sloppy work that is difficult for me to grade.   

 


Tasks for this Lab

You should create one or more SQL script files that contain the commands needed to create the tables, cluster, and indexes described below.  You may then execute the script file(s) once you have checked it for errors by using the start create_objects.sql command from within SQLPLUS.  Additionally, if you ever need to rebuild the database, having the script available will reduce the rebuild time required.

 

IMPORTANT NOTE:  During the creation of tables, indexes, and/or clusters you may find that you run out of data storage space in one or more tablespaces.  In this case, you need to increase the amount of space allocated to the tablespace by altering the datafile associated with that specific tablespace.   Additionally, you must name all constraints created as part of this assignment.
 

1.  Startup your instance.   Connect as the DBA user for your database – not as SYS.

 

2.    Create Tables, Indexes, and Constraints (6 points).  As a DBA, you will be responsible for creating tables.  This ensures that the DBA user is the owner of the tables. 

  1. Create a regular table named Vendor with columns as specified below. 
    • Set PCTFREE=30, and do not set PCTUSED for this table (remember, the PCTUSED parameter is not set when Automatic Segment Space Management is enabled, and this is the case for the DATA01 tablespace).
    • Store the table in the DATA01 tablespace. 
    • Ensure that your CREATE TABLE command creates the primary key index for the table. 
    • Create meaningful names for ALL constraints for the table.  Indexes created as part of constraint specifications should be stored to the INDEX01 tablespace—remember this is a locally managed tablespace.

 

VENDOR Table

 

 

 

Column Name

Data type

Characteristics

Description

Vendor_Number

CHAR(8)

Primary Key

Vendor identifying number

Vendor_Name

VARCHAR2(100)

Not Null

Vendor Name

Street

VARCHAR2(100)

Not Null

Vendor Street Address

City

VARCHAR2(50)

Not Null

Vendor City

State

CHAR(2)

Not Null

Vendor state abbreviation

Zip_Code

CHAR(9)

 

Vendor Zip Code

Account_Balance

NUMBER(8,2)

>= 0

Vendor Account Balance

Comments

VARCHAR2(200)

 

Comments about the Vendor

 

  1. Create an index-organized table named Product with columns as specified below. 

·         Store the table in the DATA01 tablespace.

·         Do not use PCTFREE or PCTUSED for the table. 

·         Specify use of the INDEX01 tablespace for overflow and allow the Oracle RDBMS to decide how to handle when to overflow.

·         Ensure that your CREATE TABLE command creates the primary key index for the table. 

·         Create meaningful names for ALL constraints for the table. 

 

PRODUCT Table

 

 

 

Column Name

Data type

Characteristics

Description

Product_Number

CHAR(10)

Primary Key

Product identifying number

Description

VARCHAR2(20)

 Not Null

Product text description

Retail_Price

NUMBER(8,2)

>= 0

Desired retail price

Wholesale_Price

NUMBER(8,2)

>=0

Desired wholesale price

 

 

3.  Create an Indexed Cluster and a Cluster Index (3 points). 

  1. Create an indexed cluster named Invoice_And_Details that will cluster rows for two tables named Invoice and Invoice_Details tables.  The tables will be created in Task #4 below.  

·         Use a SIZE parameter of 150 for the cluster. 

·         Set PCTFREE=10 for the cluster.

·         Store the cluster in the DATA01 tablespace

b.    Create a Cluster Index to be stored in the INDEX01 tablespace. 

 

4.    Create Cluster Tables and Additional Indexes (6 points). 

a.    Create the Invoice and Invoice_Details tables that comprise the Invoice_And_Details cluster.  Your Create Table commands should also create primary key indexes for the Invoice and Invoice_Details tables to enforce primary key integrity.  Your code must ensure the tables are stored in the cluster, and that the tables created enforce referential integrity for the following relationships:

·         The 1:M relationship from Vendor to Invoice.  If a Vendor row is deleted, cascade the deletion to the Invoice table.  An Invoice row cannot be inserted without a valid associated Vendor row.

·         The 1:M relationship from Product to Invoice_Details.  Do not allow a deletion of a Product row if there exists an associated Invoice_Details row (do not cascade deletions).  An Invoice_Details row cannot be inserted without a valid associated Product row.

·         The 1:M relationship from Invoice to Invoice_Details.  If an Invoice row is deleted, cascade the deletion to the Invoice_Details table.  An Invoice_Details row cannot be inserted without a valid associated Invoice row.

·         Give constraints meaningful names and ensure any indexes created are stored to the INDEX01 tablespace.

 

INVOICE Table

 

 

 

Column Name

Data type

Characteristics

Description

Invoice_Number

CHAR(6)

Primary Key

Invoice identifying number

Invoice_Date

DATE

Not Null

Date Invoice is placed

Invoice_Amount

NUMBER(10,2)

>= 0

Total amount of the Invoice

Promise_Date

DATE

 Default to TODAY + 14 days from today.

Promised Invoice delivery date with a default constraint specification.

Vendor_Number

CHAR(8)

Not Null

Vendor identifying number (Foreign Key)

 

 

INVOICE_DETAILS Table

 

 

 

Column Name

Data type

Characteristics

Description

Invoice_Number

CHAR(6)

Primary Key

Invoice identifying number

Product_Number

CHAR(10)

Primary Key

Product identifying number

Quantity_Ordered

NUMBER(8,2)

>= 0

Quantity on invoice in decimal units

Actual_Price

NUMBER(8,2)

>= 0

Selling price for this order

 

5.  Load Vendor and Product test data (5 points).   Load valid rows.  Create your own test data.  You must also load rows to test the various uniqueness, integrity, and reasonableness constraints you defined for the tables.  You will be evaluated on the extent to which your test data actually tests the referential integrity constraints specified for the tables, and by how clearly you demonstrate that the tests you conduct are comprehensive.

  1. Load the Vendor table with 2 good Vendor rows.
  2. Load the Product Table with 3 good rows.
  3. Attempt one row insertion in Vendor that violates the check constraint for the Account_Balance.
  4. Attempt one row insertion in Vendor that violates the Primary Key integrity constraint.   Show the resulting error message.
  5. Attempt row insertions in Vendor that violate the various NOT NULL constraints.  Show the resulting error messages.
  6. Attempt one row in Product that violates the check constraint for the Retail_Price.  Show the resulting error message.
  7. Attempt one row in Product that violates the check constraint for the Wholesale_Price.  Show the resulting error message.
  8. Attempt one row insertion in Product that violates the Primary Key integrity constraint.   Show the resulting error message.

 

6.  Load Invoice and Invoice_Details test data (5 points).  Design test data for the Invoice and Invoice_Details tables to test referential integrity constraints.  Your test data should represent Invoice data where the total value of the Invoice_Details rows for a specific invoice equals the Invoice_Amount in the corresponding Invoice row (do not write any triggers or procedures -- just create data where the total of the Invoice_Details rows matches the amount stored in the associated Invoice row).  The total of all Invoice_Amount values for Invoice rows for a given Vendor should equal the Account_Balance of the corresponding Vendor row (again create the data to accomplish this).

  1. Load two good Invoice rows with two good Invoice_Details rows for each Invoice.
  2. Attempt one row insertion in Invoice that violates the check constraint for the Invoice_Amount.  Show the resulting error message.
  3. Attempt one row insertion in Invoice that violates the Primary Key integrity constraint.   Show the resulting error message.
  4. Attempt one row insertion in Invoice that violates the referential integrity constraint between the Invoice and Vendor tables.  Show the resulting error messages.
  5. Attempt one row insertion in Invoice that violates the NOT NULL constraint for the Invoice_Date field.  Show the resulting error messages.
  6. Attempt one row insertion in Invoice_Details that violates the Primary Key integrity constraint.   Show the resulting error message.
  7. Attempt one row insertion in Invoice_Details that violates the referential integrity constraint between the Invoice and Invoice_Details tables.  Show the resulting error messages.
  8. Attempt one row insertion in Invoice_Details that violates the referential integrity constraint between the Product and Invoice_Details tables.  Show the resulting error messages.
  9. Attempt two row insertions in Invoice_Details that violate the check constraint for the Quantity_Ordered and Actual_Price fields.  Show the resulting error messages.

 

7.  UNDO tablespace management (2 points).

  1. Connect as the user SYS and list the undo segments in the UNDO1 tablespace. 
  2. Create a second Undo tablespace named UNDO2 of size 5MB in the same location as your current UNDO1 tablespace.  List the undo segments and their status in this tablespace.
  3. Start a second SQLPLUS session (leave the first one open) and connect to the system as the DBA user.  Insert a new row in the Vendor table, but do not commit the transaction.  Also, do not rollback the transaction or exit the session.
  4. Switch to the first SQLPLUS session and use the ALTER SYSTEM command to switch the active Undo tablespace from UNDO1 to UNDO2.  Now, as the user SYS, drop the UNDO1 tablespace.  What happened?  List the undo segments and their status.  Compare this to the list you developed earlier in this task.
  5. In the second SQLPLUS session as the user DBA, rollback the transaction and exit the session.
  6. In the first SQLPLUS session as the user SYS, drop the tablespace named UNDO1.  What happened?

 

NOTE:  Remember to update the init.ora file for your database so that on the next startup the correct UNDO tablespace is used. 

 

8.  Create a Temporary Table (2 points).

a.  Connect as the DBA user for your database and create a temporary table named Temp_Vendor. 

·         Use a SELECT clause to create the table and to load all Vendor table data into the Temp_Vendor table (Note: the DBA user should be the owner of the Vendor table – if this is not the case, you will need to qualify the name of the table, e.g.: TableOwner.Vendor).

·         Specify to preserve rows on a COMMIT.

·         Insert a new row into the Temp_Vendor table.

b.  Execute a SELECT vendor_number, vendor_name FROM temp_vendor and display the output to your lab report.

 

9.    Create a Hash Cluster (4 points).

a.    Create a single table hash cluster named States_Cluster with a single table as specified below that is capability of storing approximately 70 hash keys.  Do NOT specify a HASH IS clause so that Oracle will use its internal hash function.  Store the table in the Data01 tablespace.

b.    Create a table named States that is stored to the States_Cluster cluster.  The table should have the following columns:

·         State_Code  CHAR(2)  column value for hashing.

·         State_Name  Varchar2(40).

·         Population  Number(8).

c.  Store data to the States table for two states and then execute:  SELECT * FROM States;

·         Illinois – IL  -- population 10,848,287

·         Missouri – MO – population 8,481,994

 

10.   Alter a Table (2 points).

a.    Alter the Product table to add a column named Sale_Price with data type Number(8,2). 

b.    Write SQL to update all rows in the table to store a value to Sale_Price that is the value of the Wholesale_Price column plus 15%.  Example a wholesale price of $2.00 would yield a Sale_Price of $2.30.

c.    Show the output from the following command:  SELECT * FROM Product;

 

11.  Create a Bitmap Index (2 points).

a.  Create a Bitmap index on the Vendor table Zip_Code column with PCTFREE=10% and with LOGGING on.  Store the index to the INDEX01 tablespace.

 

12.  Check the system data dictionary (4 points).   Execute the queries shown below.  Ensure you are connected as the DBA user for your database.  The output from these queries will assist me in determining whether or not you have met the assignment requirements.  Ensure that you are connected to your database as the DBA user for your database.  Format the output so that it is easy to read (use the Courier or Courier New font).  To format output you can use the COLUMN column_name FORMAT A15 command where you replace 15 with the appropriate column size for text columns or with a format of 9999.99 (or other appropriate numeric format) for numeric and currency values.

  1. SELECT Table_Name, Tablespace_Name, Pct_Free, Pct_Used FROM User_Tables;
  2. SELECT Index_Name, Table_Name, Tablespace_Name As TS_Name, Uniqueness, Status FROM User_Indexes;
  3. SELECT Index_Name, Table_Name, Column_Name FROM User_Ind_Columns;
  4. SELECT Table_Name, Constraint_Name, Constraint_Type, Search_Condition, Delete_Rule FROM User_Constraints;
  5. SELECT Cluster_Name, Tablespace_name, Function, Hashkeys, Cluster_Type FROM User_Clusters;

 

13.  Shut down the database (-3 points if you forget to shutdown). 

 

At this point you should make a new cold backup of your database.

 

Do not leave your database running between laboratory sessions.

 



END OF LABORATORY