Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Appendix A. The Schema for Easy Shopping Inc.

We saw in Chapter 2 how to create our database using the GUI tools, but many readers may prefer to create the database directly from SQL. The SQL to achieve this is shown below, but it assumes that the database has already been created.

The example shown here has been created for a Windows system, a simple edit of the file specs is all that is required for a different platform. Also note that the file sizes here are very small compared to what you would use in a production environment.

Creating the Tablespaces and Data Files

The first step is to connect to the database using a powerful user name.

connect system/manager

The next step is to create the tablespaces where the data will reside and their associated data files.

-- Temporary Tablespace
CREATE TEMPORARY TABLESPACE easy_temp
 TEMPFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWeasy_temp.f'
 SIZE 10m REUSE AUTOEXTEND ON NEXT 16k ;

-- Tablespace to store Materialized Views
CREATE TABLESPACE mview
 DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWeasy_mview.f'
SIZE 6m REUSE AUTOEXTEND ON
 DEFAULT STORAGE
   (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- Tablespace for Dimensions
CREATE TABLESPACE easy_dim
 DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWdimensions.f'
SIZE 5m REUSE AUTOEXTEND ON
 DEFAULT STORAGE
   (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- Tablespace for the INDEXES
CREATE TABLESPACE easy_idx
 DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWindex.f'
SIZE 5m REUSE AUTOEXTEND ON
 DEFAULT STORAGE
   (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- Default Tablespace
CREATE TABLESPACE easydw_default
 DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDW
easydw_default.f'
SIZE 5m REUSE AUTOEXTEND ON
 DEFAULT STORAGE
   (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

Once the tablespaces have been created for the dimensions, we can now create the tablespaces for the fact table, PURCHASES. Since we will be partitioning the data, we must now create the tablespace for each partition. There will be one partition per month for the data and another partition for the indexes. Here we will create only the January partition for the data and index; simply repeat this process for the other partitions.

-- create the 3 month tablespaces for the fact partitions
CREATE TABLESPACE purchases_jan2003
 DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDW
PURCHASESJAN2003.f'
 SIZE 5m REUSE AUTOEXTEND ON
 DEFAULT STORAGE
   (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

-- create the 3 month tablespaces for the fact indexes
CREATE TABLESPACE purchases_jan2003_idx
 datafile 'D:ORACLEPRODUCT10.1.0ORADATAEASYDW
PURCHASESJAN2003_IDX.f'
SIZE 3m REUSE AUTOEXTEND ON
 DEFAULT STORAGE
   (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);

Creating the Tables, Constraints, and Indexes

Once the tablespaces have been defined, the EASYDW user can be created, which will create that schema where the data will be stored.

-- create a user called EASYDW
--  this will be the schema where the objects will reside

connect system/manager

CREATE USER easydw IDENTIFIED BY easydw
 DEFAULT TABLESPACE easydw_default
 TEMPORARY TABLESPACE temp
 PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT unlimited tablespace TO easydw ;
GRANT dba TO easydw ;
GRANT create session TO easydw;

The DBA privilege has been granted to the user so they can create and manage the tables and indexes.

Hint

Don’t forget to connect as user EASYDW before creating the tables and indexes, or the tables and indexes will be defined in the wrong schema.

-- now create the tables
CONNECT easydw/easydw

-- CUSTOMER Dimension
CREATE TABLE easydw.customer
(customer_id           varchar2(10),
 city                  varchar2(15),
 state                 varchar2(10),
 postal_code           varchar2(10),
 gender                varchar2(1),
 region                varchar2(15),
 country               varchar2(20),
 tax_rate              number,
 occupation            varchar2(15))
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;
ALTER TABLE customer
  ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id)
  USING INDEX
  PCTFREE 5
  TABLESPACE indx
  STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

We have defined the constraint here by adding it via the ALTER TABLE command. The constraint will use an index with the parameters that we have specified and the index will inherit the constraint name, PK_CUSTOMER.

-- PRODUCT Dimension
CREATE TABLE easydw.product
(product_id            varchar2(8),
 product_name          varchar2(30),
 category              varchar2(4),
 cost_price            number (6,2)
  constraint cost_price_not_null NOT NULL,
 sell_price            number (6,2)
  constraint sell_price_not_null NOT NULL,
 weight                number (6,2),
 shipping_charge       number (5,2)
  constraint shipping_charge_not_null NOT NULL,
 manufacturer          varchar2(20),
 supplier              varchar2(10))
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;
ALTER TABLE product
 ADD CONSTRAINT pk_product PRIMARY KEY (product_id)
 USING INDEX
 PCTFREE 5 TABLESPACE easy_idx
 STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

-- TIME Dimension
CREATE TABLE easydw.time
(time_key                 date,
 month                    number (6,0),
 month_name               varchar2(10),
 quarter                  number (6,0),
 year                     number (4,0),
 day_number               number (3,0),
 day_of_the_week          varchar2(9),
 week_number              number (2,0)  )
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;
ALTER TABLE time
 ADD CONSTRAINT pk_time PRIMARY KEY (time_key)
 USING INDEX
 PCTFREE 5 TABLESPACE easy_idx
 STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

-- TODAYS_SPECIAL_OFFERS Dimension
CREATE TABLE easydw.todays_special_offers
(product_id               varchar2(8),
 offer_date               date,
 special_price            number (6,2),
 offer_price              number (6,2))
PCTFREE 0 PCTUSED 99
TABLESPACE easy_dim
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

For the TODAYS_SPECIAL_OFFERS table, we have defined the primary key to include two columns rather than a single column.

ALTER TABLE todays_special_offers
 ADD CONSTRAINT pk_specials PRIMARY KEY
          (offer_date,product_id )
USING INDEX
PCTFREE 5 TABLESPACE easy_idx
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;

Now we come to creating the all-important fact table, which is called PURCHASES. This table definition is quite complex because it includes FOREIGN KEYS to several tables that are identified by the REFERENCES clause. For the column product_id, two constraints have been defined on the table, a NOT NULL and a Foreign key constraint. Provided each constraint is given a unique name, then is allowed on a column in a table.

Here we have also illustrated how to partition the table, which was discussed in detail in Chapter 4. In this SQL statement example, we are only creating the table with three partitions though the table will actually have 24 partitions for the two years of data that it contains.

-- Fact Table PURCHASES
CREATE TABLE easydw.purchases
(product_id                           varchar2(8)
   CONSTRAINT not_null_product_id  NOT NULL
   CONSTRAINT fk_product_id
REFERENCES  product(product_id),
 time_key                             date
   CONSTRAINT not_null_time  NOT NULL
   CONSTRAINT fk_time
REFERENCES time(time_key),
 customer_id                          varchar2(10)
   CONSTRAINT not_null_customer_id  NOT NULL
   CONSTRAINT fk_customer_id
REFERENCES  customer(customer_id),
 ship_date                            date,
 purchase_price                       number(6,2),
 shipping_charge                      number(5,2),
 today_special_offer                  varchar2(1)
  CONSTRAINT special_offer
  CHECK (today_special_offer IN ('Y','N'))  )
PARTITION BY RANGE (time_key )
 (
    PARTITION purchases_jan2002
       VALUES LESS THAN (TO_DATE('01-02-2002', 'DD-MM-YYYY'))
       PCTFREE 0 PCTUSED 99
       STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
       TABLESPACE purchases_jan2002 ,
    PARTITION purchases_feb2002
       VALUES LESS THAN (TO_DATE('01-03-2002', 'DD-MM-YYYY'))
       PCTFREE 0 PCTUSED 99
       STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
       TABLESPACE purchases_feb2002 ,
    PARTITION purchases_mar2002
       VALUES LESS THAN (TO_DATE('01-04-2002', 'DD-MM-YYYY'))
       PCTFREE 0 PCTUSED 99
       STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
       TABLESPACE purchases_mar2002 );

In this example, we have created the indexes immediately after the table definition. In a real data warehouse, the number of indexes created prior to loading the data is kept to an absolute minimum to ensure that the loading time is as fast as possible. Therefore, indexes exist prior to loading usually only to enable constraints to be executed as efficiently as possible.

-- Now create the indexes
-- Partition on the Time Key  Local prefixed index
CREATE BITMAP INDEX easydw.purchase_time_index
  ON purchases  (time_key ) LOCAL
  PCTFREE 5 TABLESPACE indx
  STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0);

CREATE BITMAP INDEX easydw.purchase_product_index
  ON purchases (product_id )
  LOCAL
  PCTFREE 5  TABLESPACE indx
  STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ;

CREATE INDEX easydw.purchase_customer_index
  ON purchases (customer_id )
  LOCAL
  PCTFREE 5  TABLESPACE indx
  STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ;

CREATE BITMAP INDEX easydw.purchase_special_index
  ON purchases (today_special_offer )
  LOCAL
  PCTFREE 5  TABLESPACE indx
  STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ;

Defining Security

The next step is to grant some privileges to our user, EASYDW. We will start with the following ones, which will allow us to use summary management, and, as we progress through this book, we will discuss other privileges that should be granted to users.

connect system/manager

-- Add privileges
GRANT SELECT ANY TABLE TO easydw;
GRANT EXECUTE ANY PROCEDURE TO easydw;

-- Add privileges for summary management
GRANT CREATE ANY DIMENSION TO easydw;
GRANT ALTER ANY DIMENSION TO easydw;
GRANT DROP  ANY DIMENSION TO easydw;
GRANT CREATE ANY MATERIALIZED VIEW TO easydw;
GRANT ALTER ANY MATERIALIZED VIEW TO easydw;
GRANT DROP  ANY MATERIALIZED VIEW TO easydw;
GRANT QUERY REWRITE TO easydw;
GRANT GLOBAL QUERY REWRITE TO easydw;

You will have to repeat these steps for every user that has been created and the privileges granted will, of course, vary by user.

Final Steps

The final step is actually not completed now, but after the data is loaded. However, it is included here to remind you not to forget this important step, which is to analyze the table and indexes. These statistics are gathered using the package DBMS_STATS and are used by the optimizer. Without these statistics, features like Summary Management will not operate to provide the most efficient access so query performance will suffer. The DBMS_STATS command was explained in Chapters 10 and 12.

-- Now Analyze the Tables and Indexes

EXECUTE dbms_stats.gather_table_stats
            ('EASYDW','CUSTOMER'),
EXECUTE dbms_stats.gather_table_stats
            ('EASYDW','TODAYS_SPECIAL_OFFERS'),
EXECUTE dbms_stats.gather_table_stats
            ('EASYDW','PRODUCT'),
EXECUTE dbms_stats.gather_index_stats
            ('EASYDW','PURCHASE_CUSTOMER_INDEX'),

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for A. The Schema for Easy Shopping Inc.

Create new playlist

Sign In