Schema Dependencies

Various dependencies have been established among the schemas. So, when you create the schemas manually, you must create them in the following order: HR, OE, PM, IX, and SH.

Use this sequence to create the schemas:

1. Create the HR schema.

2. Create the OE schema: The HR schema is already present, and you must know the password for the HR schema to grant HR object privileges to OE. Some HR tables are visible to the OEuser with the use of private synonyms. In addition, some OE tables have foreign key relationships to HR tables.

3. Create the PM schema: Foreign key relationships require that the OE schema already exist when the PM schema is created. You must know the password for OE, to grant to PM the right to establish and use these foreign keys.

   Note:

   The PM schema requires the database to be enabled for the Java Virtual Machine (JVM) and  inter Media. You can accomplish this during installation or later using the DBCA.

4. Create the IX schema: The information exchange schema IX is based on order entry data in OE. Again, foreign key relationships require that the OE schema already be present when theIX schema is created. You must know the password for OE to grant to IX, the right to establish and use the foreign keys.

5. Create the SH schema. The SH schema logically depends on the OE schema, though you can create this schema without creating the other four schemas.

Guidelines for Installing Sample Schemas

All scripts necessary to install sample schemas reside in $ORACLE_HOME/demo/schema directory. Before you install sample schemas by running these scripts, follow these guidelines:

1. You must connect as a system administrator using the SYSDBA privilege.

2. When prompted to enter a password for the schema, enter a secure password that meets the requirements described in Oracle Database Security Guide.

3. When prompted for tablespace names while running scripts:

   • Enter an appropriate tablespace name, for example, users as the default tablespace for a schema

   • Enter temp as the temporary tablespace for a schema

4. When prompted for the log directory name, enter $ORACLE_HOME/demo/schema/log/ or any other existing directory name.

Installing the HR Schema

All scripts necessary to create the Human Resource (HR) schema reside in $ORACLE_HOME/demo/schema/human_resources.

You need to call only one script, hr_main.sql, to create all the objects and load the data. The following steps provide a summary of the installation process:

1. Log on to SQL*Plus as SYS and connect using the AS SYSDBA privilege.

   sqlplus connect sys as sysdba
   Enter password: password 

2. To run the hr_main.sql script, use the following command:

   SQL> @?/demo/schema/human_resources/hr_main.sql
   

3. Enter a secure password for HR

   specify password for HR as parameter 1:
   Enter value for 1:
   

   See Also:

   Oracle Database Security Guide  for the minimum password requirements

4. Enter an appropriate tablespace, for example, users as the default tablespace for HR

   specify default tablespace for HR as parameter 2:
   Enter value for 2:
   

5. Enter temp as the temporary tablespace for HR

   specify temporary tablespace for HR as parameter 3:
   Enter value for 3:
   

6. Enter your SYS password

   specify password for SYS as parameter 4:
   Enter value for 4:
   

7. Enter the directory path, for example, $ORACLE_HOME/demo/schema/log/, for your log directory

   specify log path as parameter 5:
   Enter value for 5:
   

After the hr_main.sql script runs successfully and the HR schema is installed, you are connected as the user HR. To verify that the schema was created, use the following command:

SQL> SELECT       table_name FROM user_tables;

Running hr_main.sql accomplishes the following tasks:

1. Removes any previously installed HR schema

2. Creates the user HR and grants the necessary privileges

3. Connects as HR

4. Calls the scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 5-1.

A pair of optional scripts, hr_dn_c.sql and hr_dn_d.sql, is provided as a schema extension. To prepare the HR schema for use with the directory capabilities of Oracle Internet Directory, run thehr_dn_c.sql script. If you want to return to the initial setup of the HR schema, then use the hr_dn_d.sql script to undo the effects of the hr_dn_c.sql script.

Use the hr_drop.sql script to drop the HR schema.

Installing the OE Schema and Its OC Subschema

All scripts necessary to create the Order Entry (OE) schema and its Online Catalog (OC) subschema reside in $ORACLE_HOME/demo/schema/order_entry.

You need to call only one script, oe_main.sql, to create all the objects and load the data. Running oe_main.sql accomplishes the following tasks:

1. Removes any previously installed OE schema

2. Creates the user OE and grants the necessary privileges

3. Connects as OE

4. Calls the scripts that create and populate the schema objects

After the oe_main.sql script runs successfully and the OE schema is installed, you are connected as the user OE. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 5-10.

The oe_drop.sql and oc_drop.sql scripts are used to drop the OE schema and OC subschema, respectively.

Installing the PM Schema

All scripts necessary to create the Product Media (PM) schema reside in $ORACLE_HOME/demo/schema/product_media.

You need to call only one script, pm_main.sql, to create all the objects and load the data. Running pm_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts as well as datafile and log file directories

2. Removes any previously installed PM schema

3. Creates the user PM and grants the necessary privileges

4. Connects as PM

5. Calls the scripts that create and populate the schema objects

After the pm_main.sql script runs successfully and the PM schema is installed, you are connected as the user PM. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 5-19 .

The pm_drop.sql script is used to drop the PM schema.

Installing the IX Schema

All scripts necessary to create the Information Exchange (IX) schema reside in $ORACLE_HOME/demo/schema/info_exchange.

To install the Information Exchange (IX) schema, you need to call only one script, ix_main.sql, which creates all the objects and loads the data.

Running ix_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts

2. Removes any previously installed IX schema

3. Creates the user IX and grants the necessary privileges

4. Connects as IX

5. Calls the scripts that create and populate the schema objects

After the ix_main.sql script runs successfully and the IX schema is installed, you are connected as the user IX. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 5-23.

The ix_drop.sql script is used for dropping the IX schema.

Installing the SH Schema

All scripts necessary to create the Sales History (SH) schema reside in $ORACLE_HOME/demo/schema/sales_history.

You need to call only one script, sh_main.sql, to create all the objects and load the data. Running sh_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts as well as datafile and log file directories

2. Removes any previously installed SH schema

3. Creates the user SH and grants the necessary privileges

4. Connects as SH

5. Calls the scripts that create and populate the schema objects

After the sh_main.sql script runs successfully and the SH schema is installed, you are connected as the user SH. To verify that the schema was created, use the following command:

SQL> SELECT table_name FROM user_tables;

For a complete listing of the scripts and their functions, refer to Table 5-27.

A pair of optional scripts, sh_olp_c.sql and sh_olp_d.sql, is provided as a schema extension. To prepare the SH schema for use with the advanced analytical capabilities of OLAP Services, run thesh_olp_c.sql create script. If you want to return to the initial setup of the SH schema, then use the script sh_olp_d.sql to undo the effects of sh_olp_c.sql and reinstate dimensions as they were before.

The file used to drop the SH schema is sh_drop.sql.

mksample.sql

The text of the mksample.sql script follows:

Rem
Rem $Header: mksample.sql.sbs 02-apr-2003.14:55:17 $
Rem
Rem mksample.sql
Rem
Rem Copyright (c) 2001, 2003, Oracle Corporation. All rights reserved. 
Rem
Rem NAME
Rem mksample.sql - creates all 5 Sample Schemas
Rem
Rem DESCRIPTION
Rem This script rees and creates all Schemas belonging
Rem to the Oracle Database 10g Sample Schemas.
Rem If you are unsure about the prerequisites for the Sample Schemas,
Rem please use the Database Configuration Assistant DBCA to
Rem configure the Sample Schemas.
Rem
Rem NOTES
Rem - OUI instantiates this script during install and saves it
Rem as mksample.sql. The instantiated scripts matches
Rem the directory structure on your system
Rem - Tablespace EXAMPLE created with:
Rem CREATE TABLESPACE example 
Rem NOLOGGING 
Rem DATAFILE '' SIZE 150M REUSE 
Rem AUTOEXTEND ON NEXT 640k
Rem MAXSIZE UNLIMITED
Rem EXTENT MANAGEMENT LOCAL
Rem SEGMENT SPACE MANAGEMENT AUTO;
Rem 
Rem - CAUTION: This script will erase the following schemas:
Rem - HR
Rem - OE
Rem - PM
Rem - SH
Rem - IX
Rem - BI
Rem - CAUTION: Never use the preceding Sample Schemas for
Rem anything other than demos and examples
Rem - USAGE: To return the Sample Schemas to their initial 
Rem state, you can call this script and pass the passwords
Rem for SYS, SYSTEM and the schemas as parameters.
Rem Example: @?/demo/schema/mksample mgr secure h1 o2 p3 q4 s5
Rem (please choose your own passwords for security purposes)
Rem
Rem MODIFIED (MM/DD/YY)
Rem 
Rem

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 999
SET ECHO OFF
SET CONCAT '.'
SET SHOWMODE OFF

PROMPT 
PROMPT specify password for SYSTEM as parameter 1:
DEFINE password_system = &1
PROMPT 
PROMPT specify password for SYS as parameter 2:
DEFINE password_sys = &2
PROMPT 
PROMPT specify password for HR as parameter 3:
DEFINE password_hr = &3
PROMPT
PROMPT specify password for OE as parameter 4:
DEFINE password_oe = &4
PROMPT
PROMPT specify password for PM as parameter 5:
DEFINE password_pm = &5
PROMPT
PROMPT specify password for IX as parameter 6:
DEFINE password_ix = &6
PROMPT
PROMPT specify password for SH as parameter 7:
DEFINE password_sh = &7
PROMPT 
PROMPT specify password for BI as parameter 8:
DEFINE password_bi = &8
PROMPT 
PROMPT specify default tablespace as parameter 9:
DEFINE default_ts = &9
PROMPT
PROMPT specify temporary tablespace as parameter 10:
DEFINE temp_ts = &10
PROMPT 
PROMPT specify log file directory (including trailing delimiter) as parameter
 11:
DEFINE logfile_dir = &11
PROMPT 
PROMPT Sample Schemas are being created ...
PROMPT
DEFINE vrs = v3

CONNECT system/&&password_system

DROP USER hr CASCADE;
DROP USER oe CASCADE;
DROP USER pm CASCADE;
DROP USER ix CASCADE;
DROP USER sh CASCADE;
DROP USER bi CASCADE;

CONNECT system/&&password_system

SET SHOWMODE OFF

@?/demo/schema/human_resources/hr_main.sql &&password_hr &&default_ts &&temp_ts
 &&password_sys &&logfile_dir

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts
 &&password_hr &&password_sys %s_oePath% &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/product_media/pm_main.sql &&password_pm &&default_ts &&temp_ts
 &&password_oe &&password_sys %s_pmPath% &&logfile_dir %s_pmPath%

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/info_exchange/ix_main.sql &&password_ix &&default_ts &&temp_ts
 &&password_sys &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/sales_history/sh_main &&password_sh &&default_ts &&temp_ts
 &&password_sys %s_shPath% &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/bus_intelligence/bi_main &&password_bi &&default_ts &&temp_ts
 &&password_sys &&password_oe &&password_sh &&logfile_dir &vrs

CONNECT system/&&password_system

SPOOL OFF

DEFINE veri_spool = &&logfile_dir.mkverify_&vrs..log

@?/demo/schema/mkverify &&password_system &veri_spool 

EXIT

HR Table Descriptions

This section describes the columns of each table of the HR schema, as follows:

• Table 5-3, "HR.COUNTRIES Table Description"

• Table 5-4, "HR.DEPARTMENTS Table Description"

• Table 5-5, "HR.EMPLOYEES Table Description"

• Table 5-6, "HR.JOBS Table Description"

• Table 5-7, "HR.JOB_HISTORY Table Description"

• Table 5-8, "HR.LOCATIONS Table Description"

• Table 5-9, "HR.REGIONS Table Description"

OE Table Descriptions

This section describes the columns of each table of the OE schema, as follows:

• Table 5-12, "OE.CUSTOMERS Table Description"

• Table 5-13, "OE.INVENTORIES Table Description"

• Table 5-14, "OE.ORDERS Table Description"

• Table 5-15, "OE.ORDER_ITEMS Table Description"

• Table 5-16, "OE.PRODUCT_DESCRIPTIONS Table Description"

• Table 5-17, "OE.PRODUCT_INFORMATION Table Description"

• Table 5-18, "OE.WAREHOUSES Table Description"

PM Table Descriptions

This section describes the columns of each table of the PM schema, as follows:

• Table 5-21, "PM.ONLINE_MEDIA Table Description"

• Table 5-21, "PM.ONLINE_MEDIA Table Description"

IX Table Descriptions

This section describes the columns of each table of the IX schema, as follows:

• Table 5-25, "IX.ORDERS_QUEUETABLE Table Description"

• Table 5-26, "IX.STREAMS_QUEUE_TABLE Table Description"

SH Table Descriptions

This section describes the columns of each table of the IX schema, as follows:

• Table 5-29, "SH.CHANNELS Table Description"

• Table 5-30, "SH.COSTS Table Description"

• Table 5-31, "SH.COUNTRIES Table Description"

• Table 5-32, "SH.CUSTOMERS Table Description"

• Table 5-33, "SH.PRODUCTS Table Description"

• Table 5-34, "SH.PROMOTIONS Table Description"

• Table 5-35, "SH.SALES Table Description"

• Table 5-36, "SH.TIMES Table Description"