Remote DBA Plans
Remote DBA Service

Oracle Tips

by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design

Choose-one And Finite Value Attributes

To illustrate the concept of Oracle constraints for choose-one and finite value attributes, consider the following Oracle table definition:

CREATE TABLE STUDENT (
   student_nbr               number
      CONSTRAINT stud_ukey
      PRIMARY KEY (student_nbr),
   major_name             char(10)
      CONSTRAINT major_fk REFERENCES DEPT ON DELETE CASCADE,

   region_name            char(1)
      CONSTRAINT region_check
       CHECK (region_name in (‘N’, S’, ‘E’, ‘W’);

Here, we see the following attribute validation:

* The student number is defined as unique, such that no two customer rows may have the same student_nbr. Oracle uses a unique index to enforce this primary key constraint.

* The major name is validated against the existing values in the MAJOR table when a row is inserted. Hence, only valid department names are allowed to be stored into the major_name column.

* The region_name column may only have the values of N, S, E, or W. Oracle will not allow any other values to be stored in this column.

Returning to our example from Guttbaum’s Grocery, we remember that while the customer entity has many attributes, each attribute is distinct and can be represented independently of other attributes. The Oracle table definition for Guttbaum's fact table might include the following customer attributes:

This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson. You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm