This report displays information about many different types of model objects. Click around and try the links. Notice the Diagram images at the bottom and how their tables are linked. The underlying stylesheet illustrates many ways to navigate the Model's XML data to produce a report.

Sample OE/HR

Author: Val Patterson

Company: ModelRight, Inc

Generated: Fri Jun 06 07:32:33 2008

Table of Contents

Tables

Views

Types

Tablespaces

Column Domains

Diagrams

Tables

CATEGORIES_TAB

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: USERS

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  CATEGORY_ID NUMBER(2)         SYS_C005389
 
  CATEGORY_NAME VARCHAR2(50)            
  CATEGORY_DESCRIPTION VARCHAR2(1000)            
  PARENT_CATEGORY_ID NUMBER(2)            

Indexes
Name Tablespace Associated Key Members
SYS_C005389 USERS SYS_C005389 CATEGORY_ID

Keys
Type Name Associated Index Members
SYS_C005389 SYS_C005389 CATEGORIES_TAB. CATEGORY_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: CATEGORIES_TAB

---

CREATE TABLE CATEGORIES_TAB OF CATEGORY_TYP

	(

		CATEGORY_ID NOT NULL,

		PRIMARY KEY (CATEGORY_ID)

	)

	OBJECT IDENTIFIER IS SYSTEM GENERATED

		TABLESPACE USERS

;

COUNTRIES

country table. Contains 25 rows. References with locations table.

ORGANIZATION: INDEX
Owner: HR
Generate: True

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  COUNTRY_ID CHAR(2)       LOCATIONS . COUNTRY_ID COUNTRY_C_ID_PK
<default>
  COUNTRY_NAME VARCHAR2(40)           <default>
  REGION_ID NUMBER   COUNTR_REG_FK REGIONS.REGION_ID     REGION_ID

Indexes
Name Tablespace Associated Key Members
COUNTRY_C_ID_PK EXAMPLE COUNTRY_C_ID_PK COUNTRY_ID

Keys
Type Name Associated Index Members
COUNTRY_C_ID_PK COUNTRY_C_ID_PK COUNTRIES. COUNTRY_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
COUNTR_REG_FK No Action REGIONS REGIONS.REGION_ID
REGION_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script
---

--- CREATE TABLE: COUNTRIES

---

CREATE TABLE COUNTRIES

(

	COUNTRY_ID CHAR(2) CONSTRAINT COUNTRY_ID_NN CHECK ("COUNTRY_ID" IS NOT NULL) NOT NULL,

	COUNTRY_NAME VARCHAR2(40),

	REGION_ID NUMBER NOT NULL,

	PRIMARY KEY (COUNTRY_ID)

)

	ORGANIZATION INDEX

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE COUNTRIES IS 'country table. Contains 25 rows. References with locations table.';

COMMENT ON COLUMN COUNTRIES.COUNTRY_ID IS 'Primary key of countries table.';

COMMENT ON COLUMN COUNTRIES.COUNTRY_NAME IS 'Country name';

COMMENT ON COLUMN COUNTRIES.REGION_ID IS 'Region ID for the country. Foreign key to region_id column in the departments table.';



---

--- CREATE FOREIGN KEY CONSTRAINT: COUNTR_REG_FK

---

ALTER TABLE COUNTRIES ADD 

	FOREIGN KEY (REGION_ID)

		REFERENCES REGIONS (REGION_ID)

;





CUSTOMERS

Contains customers data either entered by an employee or by the customer him/herself over the Web.

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  CUSTOMER_ID NUMBER(6)       ORDERS . CUSTOMER_ID CUSTOMERS_PK
<default>
  CUST_FIRST_NAME VARCHAR2(20)           <default>
  CUST_LAST_NAME VARCHAR2(20)           <default>
  CUST_ADDRESS CUST_ADDRESS_TYP           <default>
  PHONE_NUMBERS PHONE_LIST_TYP           <default>
  NLS_LANGUAGE VARCHAR2(3)           <default>
  NLS_TERRITORY VARCHAR2(30)           <default>
  CREDIT_LIMIT NUMBER(9,2)           <default>
  CUST_EMAIL VARCHAR2(30)           <default>
  ACCOUNT_MGR_ID NUMBER(6)   CUSTOMERS_ACCOUNT_MANAGER_FK EMPLOYEES.EMPLOYEE_ID     EMPLOYEE_ID
  CUST_GEO_LOCATION SDO_GEOMETRY           <default>
  DATE_OF_BIRTH DATE           <default>
  MARITAL_STATUS VARCHAR2(20)           <default>
  GENDER VARCHAR2(1)           <default>
  INCOME_LEVEL VARCHAR2(20)           <default>

Indexes
Name Tablespace Associated Key Members
CUST_UPPER_NAME_IX EXAMPLE
CUSTOMERS_PK EXAMPLE CUSTOMERS_PK CUSTOMER_ID
CUST_ACCOUNT_MANAGER_IX EXAMPLE CUSTOMERS_ACCOUNT_MANAGER_FK ACCOUNT_MGR_ID
CUST_LNAME_IX EXAMPLE CUST_LAST_NAME
CUST_EMAIL_IX EXAMPLE CUST_EMAIL

Keys
Type Name Associated Index Members
CUSTOMERS_PK CUSTOMERS_PK CUSTOMERS. CUSTOMER_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
CUSTOMERS_ACCOUNT_MANAGER_FK Set Null EMPLOYEES EMPLOYEES.EMPLOYEE_ID
ACCOUNT_MGR_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: CUSTOMERS

---

CREATE TABLE CUSTOMERS

(

	CUSTOMER_ID NUMBER(6) NOT NULL,

	CUST_FIRST_NAME VARCHAR2(20) CONSTRAINT CUST_FNAME_NN NOT NULL,

	CUST_LAST_NAME VARCHAR2(20) CONSTRAINT CUST_LNAME_NN NOT NULL,

	CUST_ADDRESS CUST_ADDRESS_TYP,

	PHONE_NUMBERS PHONE_LIST_TYP,

	NLS_LANGUAGE VARCHAR2(3),

	NLS_TERRITORY VARCHAR2(30),

	CREDIT_LIMIT NUMBER(9,2),

	CUST_EMAIL VARCHAR2(30),

	ACCOUNT_MGR_ID NUMBER(6) NOT NULL,

	CUST_GEO_LOCATION SDO_GEOMETRY,

	DATE_OF_BIRTH DATE,

	MARITAL_STATUS VARCHAR2(20),

	GENDER VARCHAR2(1),

	INCOME_LEVEL VARCHAR2(20),

	PRIMARY KEY (CUSTOMER_ID),

	CONSTRAINT CUSTOMER_ID_MIN CHECK (customer_id > 0),

	CONSTRAINT CUSTOMER_CREDIT_LIMIT_MAX CHECK (credit_limit <= 5000)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE CUSTOMERS IS 'Contains customers data either entered by an employee or by the customer
him/herself over the Web.';

COMMENT ON COLUMN CUSTOMERS.CUSTOMER_ID IS 'Primary key column.';

COMMENT ON COLUMN CUSTOMERS.CUST_FIRST_NAME IS 'NOT NULL constraint.';

COMMENT ON COLUMN CUSTOMERS.CUST_LAST_NAME IS 'NOT NULL constraint.';

COMMENT ON COLUMN CUSTOMERS.CUST_ADDRESS IS 'Object column of type address_typ.';

COMMENT ON COLUMN CUSTOMERS.PHONE_NUMBERS IS 'Varray column of type phone_list_typ';

COMMENT ON COLUMN CUSTOMERS.CREDIT_LIMIT IS 'Check constraint.';

COMMENT ON COLUMN CUSTOMERS.ACCOUNT_MGR_ID IS 'References hr.employees.employee_id.';

COMMENT ON COLUMN CUSTOMERS.CUST_GEO_LOCATION IS 'SDO (spatial) column.';



---

--- CREATE INDEX: CUST_UPPER_NAME_IX

---

CREATE INDEX CUST_UPPER_NAME_IX ON CUSTOMERS

(

	UPPER("CUST_LAST_NAME"),

	UPPER("CUST_FIRST_NAME")

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: CUST_ACCOUNT_MANAGER_IX

---

CREATE INDEX CUST_ACCOUNT_MANAGER_IX ON CUSTOMERS

(

	ACCOUNT_MGR_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: CUST_LNAME_IX

---

CREATE INDEX CUST_LNAME_IX ON CUSTOMERS

(

	CUST_LAST_NAME

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: CUST_EMAIL_IX

---

CREATE INDEX CUST_EMAIL_IX ON CUSTOMERS

(

	CUST_EMAIL

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: CUSTOMERS_ACCOUNT_MANAGER_FK

---

ALTER TABLE CUSTOMERS ADD 

	FOREIGN KEY (ACCOUNT_MGR_ID)

		REFERENCES EMPLOYEES (EMPLOYEE_ID) ON DELETE SET NULL

;





DEPARTMENTS

Departments table that shows details of departments where employees work. Contains 27 rows; references with locations, employees, and job_history tables.

ORGANIZATION: HEAP
Owner: HR
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  DEPARTMENT_ID NUMBER(4)       EMPLOYEES . DEPARTMENT_ID
JOB_HISTORY . DEPARTMENT_ID
DEPT_ID_PK
<default>
  DEPARTMENT_NAME VARCHAR2(30)           <default>
  MANAGER_ID NUMBER(6)   DEPT_MGR_FK EMPLOYEES.EMPLOYEE_ID     EMPLOYEE_ID
  LOCATION_ID NUMBER(4)   DEPT_LOC_FK LOCATIONS.LOCATION_ID     LOCATION_ID

Indexes
Name Tablespace Associated Key Members
DEPT_ID_PK EXAMPLE DEPT_ID_PK DEPARTMENT_ID
DEPT_LOCATION_IX EXAMPLE DEPT_LOC_FK LOCATION_ID

Keys
Type Name Associated Index Members
DEPT_ID_PK DEPT_ID_PK DEPARTMENTS. DEPARTMENT_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
DEPT_LOC_FK No Action LOCATIONS LOCATIONS.LOCATION_ID
LOCATION_ID
DEPT_MGR_FK No Action EMPLOYEES EMPLOYEES.EMPLOYEE_ID
MANAGER_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script
---

--- CREATE TABLE: DEPARTMENTS

---

CREATE TABLE DEPARTMENTS

(

	DEPARTMENT_ID NUMBER(4) NOT NULL,

	DEPARTMENT_NAME VARCHAR2(30) CONSTRAINT DEPT_NAME_NN NOT NULL,

	MANAGER_ID NUMBER(6) NOT NULL,

	LOCATION_ID NUMBER(4) NOT NULL,

	PRIMARY KEY (DEPARTMENT_ID)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE DEPARTMENTS IS 'Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.';

COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_ID IS 'Primary key column of departments table.';

COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_NAME IS 'A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ';

COMMENT ON COLUMN DEPARTMENTS.MANAGER_ID IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';

COMMENT ON COLUMN DEPARTMENTS.LOCATION_ID IS 'Location id where a department is located. Foreign key to location_id column of locations table.';



---

--- CREATE INDEX: DEPT_LOCATION_IX

---

CREATE INDEX DEPT_LOCATION_IX ON DEPARTMENTS

(

	LOCATION_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: DEPT_LOC_FK

---

ALTER TABLE DEPARTMENTS ADD 

	FOREIGN KEY (LOCATION_ID)

		REFERENCES LOCATIONS (LOCATION_ID)

;







---

--- CREATE FOREIGN KEY CONSTRAINT: DEPT_MGR_FK

---

ALTER TABLE DEPARTMENTS ADD 

	FOREIGN KEY (MANAGER_ID)

		REFERENCES EMPLOYEES (EMPLOYEE_ID)

;





EMPLOYEES

employees table. Contains 107 rows. References with departments, jobs, job_history tables. Contains a self reference.

ORGANIZATION: HEAP
Owner: HR
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  EMPLOYEE_ID NUMBER(6)       DEPARTMENTS . MANAGER_ID
EMPLOYEES . MANAGER_ID
JOB_HISTORY . EMPLOYEE_ID
CUSTOMERS . ACCOUNT_MGR_ID
ORDERS . SALES_REP_ID
EMP_EMP_ID_PK
<default>
  FIRST_NAME VARCHAR2(20)           <default>
  LAST_NAME VARCHAR2(25)           <default>
  EMAIL VARCHAR2(25)         EMP_EMAIL_UK
<default>
  PHONE_NUMBER VARCHAR2(20)           <default>
  HIRE_DATE DATE           <default>
  JOB_ID VARCHAR2(10)   EMP_JOB_FK JOBS.JOB_ID     JOB_ID
  SALARY NUMBER(8,2)           <default>
  COMMISSION_PCT NUMBER(2,2)           <default>
  MANAGER_ID NUMBER(6)   EMP_MANAGER_FK EMPLOYEES.EMPLOYEE_ID     EMPLOYEE_ID
  DEPARTMENT_ID NUMBER(4)   EMP_DEPT_FK DEPARTMENTS.DEPARTMENT_ID     DEPARTMENT_ID

Indexes
Name Tablespace Associated Key Members
EMP_EMAIL_UK EXAMPLE EMP_EMAIL_UK EMAIL
EMP_EMP_ID_PK EXAMPLE EMP_EMP_ID_PK EMPLOYEE_ID
EMP_DEPARTMENT_IX EXAMPLE EMP_DEPT_FK DEPARTMENT_ID
EMP_JOB_IX EXAMPLE EMP_JOB_FK JOB_ID
EMP_MANAGER_IX EXAMPLE EMP_MANAGER_FK MANAGER_ID
EMP_NAME_IX EXAMPLE LAST_NAME
FIRST_NAME

Keys
Type Name Associated Index Members
EMP_EMP_ID_PK EMP_EMP_ID_PK EMPLOYEES. EMPLOYEE_ID
EMP_EMAIL_UK EMP_EMAIL_UK EMPLOYEES. EMAIL

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
EMP_DEPT_FK No Action DEPARTMENTS DEPARTMENTS.DEPARTMENT_ID
DEPARTMENT_ID
EMP_JOB_FK No Action JOBS JOBS.JOB_ID
JOB_ID
EMP_MANAGER_FK No Action EMPLOYEES EMPLOYEES.EMPLOYEE_ID
MANAGER_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script
---

--- CREATE TABLE: EMPLOYEES

---

CREATE TABLE EMPLOYEES

(

	EMPLOYEE_ID NUMBER(6) NOT NULL,

	FIRST_NAME VARCHAR2(20),

	LAST_NAME VARCHAR2(25) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,

	EMAIL VARCHAR2(25) CONSTRAINT EMP_EMAIL_NN NOT NULL,

	PHONE_NUMBER VARCHAR2(20),

	HIRE_DATE DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,

	JOB_ID VARCHAR2(10) NOT NULL,

	SALARY NUMBER(8,2),

	COMMISSION_PCT NUMBER(2,2),

	MANAGER_ID NUMBER(6) NOT NULL,

	DEPARTMENT_ID NUMBER(4) NOT NULL,

	PRIMARY KEY (EMPLOYEE_ID),

	UNIQUE (EMAIL),

	CONSTRAINT EMP_SALARY_MIN CHECK (salary > 0)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE EMPLOYEES IS 'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.';

COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS 'Primary key of employees table.';

COMMENT ON COLUMN EMPLOYEES.FIRST_NAME IS 'First name of the employee. A not null column.';

COMMENT ON COLUMN EMPLOYEES.LAST_NAME IS 'Last name of the employee. A not null column.';

COMMENT ON COLUMN EMPLOYEES.EMAIL IS 'Email id of the employee';

COMMENT ON COLUMN EMPLOYEES.PHONE_NUMBER IS 'Phone number of the employee; includes country code and area code';

COMMENT ON COLUMN EMPLOYEES.HIRE_DATE IS 'Date when the employee started on this job. A not null column.';

COMMENT ON COLUMN EMPLOYEES.JOB_ID IS 'Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.';

COMMENT ON COLUMN EMPLOYEES.SALARY IS 'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)';

COMMENT ON COLUMN EMPLOYEES.COMMISSION_PCT IS 'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage';

COMMENT ON COLUMN EMPLOYEES.MANAGER_ID IS 'Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)';

COMMENT ON COLUMN EMPLOYEES.DEPARTMENT_ID IS 'Department id where employee works; foreign key to department_id
column of the departments table';



---

--- CREATE INDEX: EMP_DEPARTMENT_IX

---

CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES

(

	DEPARTMENT_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: EMP_JOB_IX

---

CREATE INDEX EMP_JOB_IX ON EMPLOYEES

(

	JOB_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: EMP_MANAGER_IX

---

CREATE INDEX EMP_MANAGER_IX ON EMPLOYEES

(

	MANAGER_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: EMP_NAME_IX

---

CREATE INDEX EMP_NAME_IX ON EMPLOYEES

(

	LAST_NAME,

	FIRST_NAME

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: EMP_DEPT_FK

---

ALTER TABLE EMPLOYEES ADD 

	FOREIGN KEY (DEPARTMENT_ID)

		REFERENCES DEPARTMENTS (DEPARTMENT_ID)

;







---

--- CREATE FOREIGN KEY CONSTRAINT: EMP_JOB_FK

---

ALTER TABLE EMPLOYEES ADD 

	FOREIGN KEY (JOB_ID)

		REFERENCES JOBS (JOB_ID)

;







---

--- CREATE FOREIGN KEY CONSTRAINT: EMP_MANAGER_FK

---

ALTER TABLE EMPLOYEES ADD 

	FOREIGN KEY (MANAGER_ID)

		REFERENCES EMPLOYEES (EMPLOYEE_ID)

;







---

--- CREATE TRIGGER: UPDATE_JOB_HISTORY

---

CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY

	AFTER

	UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES

	FOR EACH ROW

BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;

/





---

--- CREATE TRIGGER: SECURE_EMPLOYEES

---

CREATE OR REPLACE TRIGGER SECURE_EMPLOYEES

	BEFORE

	INSERT OR DELETE OR UPDATE ON EMPLOYEES

BEGIN
  secure_dml;
END secure_employees;

/



INVENTORIES

Tracks availability of products by product_it and warehouse_id.

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  PRODUCT_ID NUMBER(6)   INVENTORIES_PRODUCT_ID_FK PRODUCT_INFORMATION.PRODUCT_ID   INVENTORY_PK
PRODUCT_ID
  WAREHOUSE_ID NUMBER(3)   INVENTORIES_WAREHOUSES_FK WAREHOUSES.WAREHOUSE_ID   INVENTORY_PK
WAREHOUSE_ID
  QUANTITY_ON_HAND NUMBER(8)           <default>

Indexes
Name Tablespace Associated Key Members
INVENTORY_IX EXAMPLE INVENTORY_PK WAREHOUSE_ID
PRODUCT_ID
INV_PRODUCT_IX EXAMPLE INVENTORIES_PRODUCT_ID_FK PRODUCT_ID

Keys
Type Name Associated Index Members
INVENTORY_PK INVENTORY_IX INVENTORIES. PRODUCT_ID
INVENTORIES. WAREHOUSE_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
INVENTORIES_WAREHOUSES_FK No Action WAREHOUSES WAREHOUSES.WAREHOUSE_ID
WAREHOUSE_ID
INVENTORIES_PRODUCT_ID_FK No Action PRODUCT_INFORMATION PRODUCT_INFORMATION.PRODUCT_ID
PRODUCT_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: INVENTORIES

---

CREATE TABLE INVENTORIES

(

	PRODUCT_ID NUMBER(6) NOT NULL,

	WAREHOUSE_ID NUMBER(3) NOT NULL,

	QUANTITY_ON_HAND NUMBER(8) CONSTRAINT INVENTORY_QOH_NN NOT NULL,

	PRIMARY KEY (PRODUCT_ID, WAREHOUSE_ID)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE INVENTORIES IS 'Tracks availability of products by product_it and warehouse_id.';

COMMENT ON COLUMN INVENTORIES.PRODUCT_ID IS 'Part of concatenated primary key, references product_information.product_id.';

COMMENT ON COLUMN INVENTORIES.WAREHOUSE_ID IS 'Part of concatenated primary key, references warehouses.warehouse_id.';



---

--- CREATE INDEX: INV_PRODUCT_IX

---

CREATE INDEX INV_PRODUCT_IX ON INVENTORIES

(

	PRODUCT_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: INVENTORIES_WAREHOUSES_FK

---

ALTER TABLE INVENTORIES ADD 

	FOREIGN KEY (WAREHOUSE_ID)

		REFERENCES WAREHOUSES (WAREHOUSE_ID) VALIDATE

;







---

--- CREATE FOREIGN KEY CONSTRAINT: INVENTORIES_PRODUCT_ID_FK

---

ALTER TABLE INVENTORIES ADD 

	FOREIGN KEY (PRODUCT_ID)

		REFERENCES PRODUCT_INFORMATION (PRODUCT_ID)

;





JOB_HISTORY

Table that stores job history of the employees. If an employee changes departments within the job or changes jobs within the department, new rows get inserted into this table with old job information of the employee. Contains a complex primary key: employee_id+start_date. Contains 25 rows. References with jobs, employees, and departments tables.

ORGANIZATION: HEAP
Owner: HR
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  EMPLOYEE_ID NUMBER(6)   JHIST_EMP_FK EMPLOYEES.EMPLOYEE_ID   JHIST_EMP_ID_ST_DATE_PK
EMPLOYEE_ID
  START_DATE DATE         JHIST_EMP_ID_ST_DATE_PK
<default>
  END_DATE DATE           <default>
  JOB_ID VARCHAR2(10)   JHIST_JOB_FK JOBS.JOB_ID     JOB_ID
  DEPARTMENT_ID NUMBER(4)   JHIST_DEPT_FK DEPARTMENTS.DEPARTMENT_ID     DEPARTMENT_ID

Indexes
Name Tablespace Associated Key Members
JHIST_EMP_ID_ST_DATE_PK EXAMPLE JHIST_EMP_ID_ST_DATE_PK EMPLOYEE_ID
START_DATE
JHIST_JOB_IX EXAMPLE JHIST_JOB_FK JOB_ID
JHIST_EMPLOYEE_IX EXAMPLE JHIST_EMP_FK EMPLOYEE_ID
JHIST_DEPARTMENT_IX EXAMPLE JHIST_DEPT_FK DEPARTMENT_ID

Keys
Type Name Associated Index Members
JHIST_EMP_ID_ST_DATE_PK JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY. EMPLOYEE_ID
JOB_HISTORY. START_DATE

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
JHIST_JOB_FK No Action JOBS JOBS.JOB_ID
JOB_ID
JHIST_EMP_FK No Action EMPLOYEES EMPLOYEES.EMPLOYEE_ID
EMPLOYEE_ID
JHIST_DEPT_FK No Action DEPARTMENTS DEPARTMENTS.DEPARTMENT_ID
DEPARTMENT_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script
---

--- CREATE TABLE: JOB_HISTORY

---

CREATE TABLE JOB_HISTORY

(

	EMPLOYEE_ID NUMBER(6) NOT NULL,

	START_DATE DATE NOT NULL,

	END_DATE DATE CONSTRAINT JHIST_END_DATE_NN NOT NULL,

	JOB_ID VARCHAR2(10) NOT NULL,

	DEPARTMENT_ID NUMBER(4) NOT NULL,

	PRIMARY KEY (EMPLOYEE_ID, START_DATE),

	CONSTRAINT JHIST_DATE_INTERVAL CHECK (end_date > start_date)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE JOB_HISTORY IS 'Table that stores job history of the employees. If an employee
changes departments within the job or changes jobs within the department,
new rows get inserted into this table with old job information of the
employee. Contains a complex primary key: employee_id+start_date.
Contains 25 rows. References with jobs, employees, and departments tables.';

COMMENT ON COLUMN JOB_HISTORY.EMPLOYEE_ID IS 'A not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table';

COMMENT ON COLUMN JOB_HISTORY.START_DATE IS 'A not null column in the complex primary key employee_id+start_date.
Must be less than the end_date of the job_history table. (enforced by
constraint jhist_date_interval)';

COMMENT ON COLUMN JOB_HISTORY.END_DATE IS 'Last day of the employee in this job role. A not null column. Must be
greater than the start_date of the job_history table.
(enforced by constraint jhist_date_interval)';

COMMENT ON COLUMN JOB_HISTORY.JOB_ID IS 'Job role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.';

COMMENT ON COLUMN JOB_HISTORY.DEPARTMENT_ID IS 'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table';



---

--- CREATE INDEX: JHIST_JOB_IX

---

CREATE INDEX JHIST_JOB_IX ON JOB_HISTORY

(

	JOB_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: JHIST_EMPLOYEE_IX

---

CREATE INDEX JHIST_EMPLOYEE_IX ON JOB_HISTORY

(

	EMPLOYEE_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: JHIST_DEPARTMENT_IX

---

CREATE INDEX JHIST_DEPARTMENT_IX ON JOB_HISTORY

(

	DEPARTMENT_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: JHIST_JOB_FK

---

ALTER TABLE JOB_HISTORY ADD 

	FOREIGN KEY (JOB_ID)

		REFERENCES JOBS (JOB_ID)

;







---

--- CREATE FOREIGN KEY CONSTRAINT: JHIST_EMP_FK

---

ALTER TABLE JOB_HISTORY ADD 

	FOREIGN KEY (EMPLOYEE_ID)

		REFERENCES EMPLOYEES (EMPLOYEE_ID)

;







---

--- CREATE FOREIGN KEY CONSTRAINT: JHIST_DEPT_FK

---

ALTER TABLE JOB_HISTORY ADD 

	FOREIGN KEY (DEPARTMENT_ID)

		REFERENCES DEPARTMENTS (DEPARTMENT_ID)

;





JOBS

jobs table with job titles and salary ranges. Contains 19 rows. References with employees and job_history table.

ORGANIZATION: HEAP
Owner: HR
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  JOB_ID VARCHAR2(10)       EMPLOYEES . JOB_ID
JOB_HISTORY . JOB_ID
JOB_ID_PK
<default>
  JOB_TITLE VARCHAR2(35)           <default>
  MIN_SALARY NUMBER(6)           <default>
  MAX_SALARY NUMBER(6)           <default>

Indexes
Name Tablespace Associated Key Members
JOB_ID_PK EXAMPLE JOB_ID_PK JOB_ID

Keys
Type Name Associated Index Members
JOB_ID_PK JOB_ID_PK JOBS. JOB_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script
---

--- CREATE TABLE: JOBS

---

CREATE TABLE JOBS

(

	JOB_ID VARCHAR2(10) NOT NULL,

	JOB_TITLE VARCHAR2(35) CONSTRAINT JOB_TITLE_NN NOT NULL,

	MIN_SALARY NUMBER(6),

	MAX_SALARY NUMBER(6),

	PRIMARY KEY (JOB_ID)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE JOBS IS 'jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.';

COMMENT ON COLUMN JOBS.JOB_ID IS 'Primary key of jobs table.';

COMMENT ON COLUMN JOBS.JOB_TITLE IS 'A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT';

COMMENT ON COLUMN JOBS.MIN_SALARY IS 'Minimum salary for a job title.';

COMMENT ON COLUMN JOBS.MAX_SALARY IS 'Maximum salary for a job title';

LOCATIONS

Locations table that contains specific address of a specific office, warehouse, and/or production site of a company. Does not store addresses / locations of customers. Contains 23 rows; references with the departments and countries tables.

ORGANIZATION: HEAP
Owner: HR
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  LOCATION_ID NUMBER(4)       DEPARTMENTS . LOCATION_ID
WAREHOUSES . LOCATION_ID
LOC_ID_PK
<default>
  STREET_ADDRESS VARCHAR2(40)           <default>
  POSTAL_CODE VARCHAR2(12)           <default>
  CITY VARCHAR2(30)           <default>
  STATE_PROVINCE VARCHAR2(25)           <default>
  COUNTRY_ID CHAR(2)   LOC_C_ID_FK COUNTRIES.COUNTRY_ID     COUNTRY_ID

Indexes
Name Tablespace Associated Key Members
LOC_ID_PK EXAMPLE LOC_ID_PK LOCATION_ID
LOC_CITY_IX EXAMPLE CITY
LOC_STATE_PROVINCE_IX EXAMPLE STATE_PROVINCE
LOC_COUNTRY_IX EXAMPLE LOC_C_ID_FK COUNTRY_ID

Keys
Type Name Associated Index Members
LOC_ID_PK LOC_ID_PK LOCATIONS. LOCATION_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
LOC_C_ID_FK No Action COUNTRIES COUNTRIES.COUNTRY_ID
COUNTRY_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script
---

--- CREATE TABLE: LOCATIONS

---

CREATE TABLE LOCATIONS

(

	LOCATION_ID NUMBER(4) NOT NULL,

	STREET_ADDRESS VARCHAR2(40),

	POSTAL_CODE VARCHAR2(12),

	CITY VARCHAR2(30) CONSTRAINT LOC_CITY_NN NOT NULL,

	STATE_PROVINCE VARCHAR2(25),

	COUNTRY_ID CHAR(2) NOT NULL,

	PRIMARY KEY (LOCATION_ID)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE LOCATIONS IS 'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables. ';

COMMENT ON COLUMN LOCATIONS.LOCATION_ID IS 'Primary key of locations table';

COMMENT ON COLUMN LOCATIONS.STREET_ADDRESS IS 'Street address of an office, warehouse, or production site of a company.
Contains building number and street name';

COMMENT ON COLUMN LOCATIONS.POSTAL_CODE IS 'Postal code of the location of an office, warehouse, or production site
of a company. ';

COMMENT ON COLUMN LOCATIONS.CITY IS 'A not null column that shows city where an office, warehouse, or
production site of a company is located. ';

COMMENT ON COLUMN LOCATIONS.STATE_PROVINCE IS 'State or Province where an office, warehouse, or production site of a
company is located.';

COMMENT ON COLUMN LOCATIONS.COUNTRY_ID IS 'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.';



---

--- CREATE INDEX: LOC_CITY_IX

---

CREATE INDEX LOC_CITY_IX ON LOCATIONS

(

	CITY

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: LOC_STATE_PROVINCE_IX

---

CREATE INDEX LOC_STATE_PROVINCE_IX ON LOCATIONS

(

	STATE_PROVINCE

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: LOC_COUNTRY_IX

---

CREATE INDEX LOC_COUNTRY_IX ON LOCATIONS

(

	COUNTRY_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: LOC_C_ID_FK

---

ALTER TABLE LOCATIONS ADD 

	FOREIGN KEY (COUNTRY_ID)

		REFERENCES COUNTRIES (COUNTRY_ID)

;





ORDER_ITEMS

Example of many-to-many resolution.

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  ORDER_ID NUMBER(12)   ORDER_ITEMS_ORDER_ID_FK ORDERS.ORDER_ID   ORDER_ITEMS_PK
ORDER_ID
  LINE_ITEM_ID NUMBER(3)         ORDER_ITEMS_PK
<default>
  PRODUCT_ID NUMBER(6)   ORDER_ITEMS_PRODUCT_ID_FK PRODUCT_INFORMATION.PRODUCT_ID     PRODUCT_ID
  UNIT_PRICE NUMBER(8,2)           <default>
  QUANTITY NUMBER(8)           <default>

Indexes
Name Tablespace Associated Key Members
ORDER_ITEMS_PK EXAMPLE ORDER_ITEMS_PK ORDER_ID
LINE_ITEM_ID
ORDER_ITEMS_UK EXAMPLE ORDER_ID
PRODUCT_ID
ITEM_ORDER_IX EXAMPLE ORDER_ITEMS_ORDER_ID_FK ORDER_ID
ITEM_PRODUCT_IX EXAMPLE ORDER_ITEMS_PRODUCT_ID_FK PRODUCT_ID

Keys
Type Name Associated Index Members
ORDER_ITEMS_PK ORDER_ITEMS_PK ORDER_ITEMS. ORDER_ID
ORDER_ITEMS. LINE_ITEM_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
ORDER_ITEMS_ORDER_ID_FK Cascade ORDERS ORDERS.ORDER_ID
ORDER_ID
ORDER_ITEMS_PRODUCT_ID_FK No Action PRODUCT_INFORMATION PRODUCT_INFORMATION.PRODUCT_ID
PRODUCT_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: ORDER_ITEMS

---

CREATE TABLE ORDER_ITEMS

(

	ORDER_ID NUMBER(12) NOT NULL,

	LINE_ITEM_ID NUMBER(3) NOT NULL,

	PRODUCT_ID NUMBER(6) NOT NULL,

	UNIT_PRICE NUMBER(8,2),

	QUANTITY NUMBER(8),

	PRIMARY KEY (ORDER_ID, LINE_ITEM_ID)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE ORDER_ITEMS IS 'Example of many-to-many resolution.';

COMMENT ON COLUMN ORDER_ITEMS.ORDER_ID IS 'Part of concatenated primary key, references orders.order_id.';

COMMENT ON COLUMN ORDER_ITEMS.LINE_ITEM_ID IS 'Part of concatenated primary key.';

COMMENT ON COLUMN ORDER_ITEMS.PRODUCT_ID IS 'References product_information.product_id.';



---

--- CREATE INDEX: ORDER_ITEMS_UK

---

CREATE UNIQUE INDEX ORDER_ITEMS_UK ON ORDER_ITEMS

(

	ORDER_ID,

	PRODUCT_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: ITEM_ORDER_IX

---

CREATE INDEX ITEM_ORDER_IX ON ORDER_ITEMS

(

	ORDER_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: ITEM_PRODUCT_IX

---

CREATE INDEX ITEM_PRODUCT_IX ON ORDER_ITEMS

(

	PRODUCT_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: ORDER_ITEMS_ORDER_ID_FK

---

ALTER TABLE ORDER_ITEMS ADD 

	FOREIGN KEY (ORDER_ID)

		REFERENCES ORDERS (ORDER_ID) ON DELETE CASCADE VALIDATE

;







---

--- CREATE FOREIGN KEY CONSTRAINT: ORDER_ITEMS_PRODUCT_ID_FK

---

ALTER TABLE ORDER_ITEMS ADD 

	FOREIGN KEY (PRODUCT_ID)

		REFERENCES PRODUCT_INFORMATION (PRODUCT_ID)

;







---

--- CREATE TRIGGER: INSERT_ORD_LINE

---

CREATE OR REPLACE TRIGGER INSERT_ORD_LINE

	BEFORE

	INSERT ON ORDER_ITEMS

	FOR EACH ROW

DECLARE
    new_line number;
  BEGIN
    SELECT (NVL(MAX(line_item_id),0)+1) INTO new_line
      FROM order_items
      WHERE order_id = :new.order_id;
    :new.line_item_id := new_line;
  END;

/



ORDERS

Contains orders entered by a salesperson as well as over the Web.

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  ORDER_ID NUMBER(12)       ORDER_ITEMS . ORDER_ID ORDER_PK
<default>
  ORDER_DATE TIMESTAMP WITH LOCAL TIME ZONE           <default>
  ORDER_MODE VARCHAR2(8)           <default>
  CUSTOMER_ID NUMBER(6)   ORDERS_CUSTOMER_ID_FK CUSTOMERS.CUSTOMER_ID     CUSTOMER_ID
  ORDER_STATUS NUMBER(2)           <default>
  ORDER_TOTAL NUMBER(8,2)           <default>
  SALES_REP_ID NUMBER(6)   ORDERS_SALES_REP_FK EMPLOYEES.EMPLOYEE_ID     EMPLOYEE_ID
  PROMOTION_ID NUMBER(6)           <default>

Indexes
Name Tablespace Associated Key Members
ORDER_PK EXAMPLE ORDER_PK ORDER_ID
ORD_SALES_REP_IX EXAMPLE ORDERS_SALES_REP_FK SALES_REP_ID
ORD_CUSTOMER_IX EXAMPLE ORDERS_CUSTOMER_ID_FK CUSTOMER_ID
ORD_ORDER_DATE_IX EXAMPLE ORDER_DATE

Keys
Type Name Associated Index Members
ORDER_PK ORDER_PK ORDERS. ORDER_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
ORDERS_SALES_REP_FK Set Null EMPLOYEES EMPLOYEES.EMPLOYEE_ID
SALES_REP_ID
ORDERS_CUSTOMER_ID_FK Set Null CUSTOMERS CUSTOMERS.CUSTOMER_ID
CUSTOMER_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: ORDERS

---

CREATE TABLE ORDERS

(

	ORDER_ID NUMBER(12) NOT NULL,

	ORDER_DATE TIMESTAMP WITH LOCAL TIME ZONE CONSTRAINT ORDER_DATE_NN NOT NULL,

	ORDER_MODE VARCHAR2(8),

	CUSTOMER_ID NUMBER(6) NOT NULL,

	ORDER_STATUS NUMBER(2),

	ORDER_TOTAL NUMBER(8,2),

	SALES_REP_ID NUMBER(6) NOT NULL,

	PROMOTION_ID NUMBER(6),

	PRIMARY KEY (ORDER_ID),

	CONSTRAINT ORDER_TOTAL_MIN CHECK (order_total >= 0),

	CONSTRAINT ORDER_MODE_LOV CHECK (order_mode in ('direct','online'))

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE ORDERS IS 'Contains orders entered by a salesperson as well as over the Web.';

COMMENT ON COLUMN ORDERS.ORDER_ID IS 'PRIMARY KEY column.';

COMMENT ON COLUMN ORDERS.ORDER_DATE IS 'TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.';

COMMENT ON COLUMN ORDERS.ORDER_MODE IS 'CHECK constraint.';

COMMENT ON COLUMN ORDERS.CUSTOMER_ID IS 'Primary key column.';

COMMENT ON COLUMN ORDERS.ORDER_STATUS IS '0: Not fully entered, 1: Entered, 2: Canceled - bad credit, -
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid';

COMMENT ON COLUMN ORDERS.ORDER_TOTAL IS 'CHECK constraint.';

COMMENT ON COLUMN ORDERS.SALES_REP_ID IS 'References hr.employees.employee_id.';

COMMENT ON COLUMN ORDERS.PROMOTION_ID IS 'Sales promotion ID. Used in SH schema';



---

--- CREATE INDEX: ORD_SALES_REP_IX

---

CREATE INDEX ORD_SALES_REP_IX ON ORDERS

(

	SALES_REP_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: ORD_CUSTOMER_IX

---

CREATE INDEX ORD_CUSTOMER_IX ON ORDERS

(

	CUSTOMER_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE INDEX: ORD_ORDER_DATE_IX

---

CREATE INDEX ORD_ORDER_DATE_IX ON ORDERS

(

	ORDER_DATE

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: ORDERS_SALES_REP_FK

---

ALTER TABLE ORDERS ADD 

	FOREIGN KEY (SALES_REP_ID)

		REFERENCES EMPLOYEES (EMPLOYEE_ID) ON DELETE SET NULL

;







---

--- CREATE FOREIGN KEY CONSTRAINT: ORDERS_CUSTOMER_ID_FK

---

ALTER TABLE ORDERS ADD 

	FOREIGN KEY (CUSTOMER_ID)

		REFERENCES CUSTOMERS (CUSTOMER_ID) ON DELETE SET NULL

;





PRODUCT_DESCRIPTIONS

Non-industry-specific design, allows selection of NLS-setting-specific data derived at runtime, for example using the products view.

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  PRODUCT_ID NUMBER(6)   PD_PRODUCT_ID_FK PRODUCT_INFORMATION.PRODUCT_ID   PRODUCT_DESCRIPTIONS_PK
PRODUCT_ID
  LANGUAGE_ID VARCHAR2(3)         PRODUCT_DESCRIPTIONS_PK
<default>
  TRANSLATED_NAME NVARCHAR2(100)           <default>
  TRANSLATED_DESCRIPTION NVARCHAR2(4000)           <default>

Indexes
Name Tablespace Associated Key Members
PRD_DESC_PK EXAMPLE PRODUCT_DESCRIPTIONS_PK PRODUCT_ID
LANGUAGE_ID
PROD_NAME_IX EXAMPLE TRANSLATED_NAME

Keys
Type Name Associated Index Members
PRODUCT_DESCRIPTIONS_PK PRD_DESC_PK PRODUCT_DESCRIPTIONS. PRODUCT_ID
PRODUCT_DESCRIPTIONS. LANGUAGE_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
PD_PRODUCT_ID_FK No Action PRODUCT_INFORMATION PRODUCT_INFORMATION.PRODUCT_ID
PRODUCT_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: PRODUCT_DESCRIPTIONS

---

CREATE TABLE PRODUCT_DESCRIPTIONS

(

	PRODUCT_ID NUMBER(6) NOT NULL,

	LANGUAGE_ID VARCHAR2(3) NOT NULL,

	TRANSLATED_NAME NVARCHAR2(100) CONSTRAINT TRANSLATED_NAME_NN NOT NULL,

	TRANSLATED_DESCRIPTION NVARCHAR2(4000) CONSTRAINT TRANSLATED_DESC_NN NOT NULL,

	PRIMARY KEY (PRODUCT_ID, LANGUAGE_ID)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE PRODUCT_DESCRIPTIONS IS 'Non-industry-specific design, allows selection of NLS-setting-specific data
derived at runtime, for example using the products view.';

COMMENT ON COLUMN PRODUCT_DESCRIPTIONS.PRODUCT_ID IS 'Primary key column.';

COMMENT ON COLUMN PRODUCT_DESCRIPTIONS.LANGUAGE_ID IS 'Primary key column.';



---

--- CREATE INDEX: PROD_NAME_IX

---

CREATE INDEX PROD_NAME_IX ON PRODUCT_DESCRIPTIONS

(

	TRANSLATED_NAME

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: PD_PRODUCT_ID_FK

---

ALTER TABLE PRODUCT_DESCRIPTIONS ADD 

	FOREIGN KEY (PRODUCT_ID)

		REFERENCES PRODUCT_INFORMATION (PRODUCT_ID)

;





PRODUCT_INFORMATION

Non-industry-specific data in various categories.

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  PRODUCT_ID NUMBER(6)       INVENTORIES . PRODUCT_ID
ORDER_ITEMS . PRODUCT_ID
PRODUCT_DESCRIPTIONS . PRODUCT_ID
PRODUCT_INFORMATION_PK
<default>
  PRODUCT_NAME VARCHAR2(50)           <default>
  PRODUCT_DESCRIPTION VARCHAR2(2000)           <default>
  CATEGORY_ID NUMBER(2)           <default>
  WEIGHT_CLASS NUMBER(1)           <default>
  WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH           <default>
  SUPPLIER_ID NUMBER(6)           <default>
  PRODUCT_STATUS VARCHAR2(20)           <default>
  LIST_PRICE NUMBER(8,2)           <default>
  MIN_PRICE NUMBER(8,2)           <default>
  CATALOG_URL VARCHAR2(50)           <default>

Indexes
Name Tablespace Associated Key Members
PRODUCT_INFORMATION_PK EXAMPLE PRODUCT_INFORMATION_PK PRODUCT_ID
PROD_SUPPLIER_IX EXAMPLE SUPPLIER_ID

Keys
Type Name Associated Index Members
PRODUCT_INFORMATION_PK PRODUCT_INFORMATION_PK PRODUCT_INFORMATION. PRODUCT_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: PRODUCT_INFORMATION

---

CREATE TABLE PRODUCT_INFORMATION

(

	PRODUCT_ID NUMBER(6) NOT NULL,

	PRODUCT_NAME VARCHAR2(50),

	PRODUCT_DESCRIPTION VARCHAR2(2000),

	CATEGORY_ID NUMBER(2),

	WEIGHT_CLASS NUMBER(1),

	WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH,

	SUPPLIER_ID NUMBER(6),

	PRODUCT_STATUS VARCHAR2(20),

	LIST_PRICE NUMBER(8,2),

	MIN_PRICE NUMBER(8,2),

	CATALOG_URL VARCHAR2(50),

	PRIMARY KEY (PRODUCT_ID),

	CONSTRAINT PRODUCT_STATUS_LOV CHECK (product_status in ('orderable'
                                                  ,'planned'
                                                  ,'under development'
                                                  ,'obsolete')
                               )

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE PRODUCT_INFORMATION IS 'Non-industry-specific data in various categories.';

COMMENT ON COLUMN PRODUCT_INFORMATION.PRODUCT_ID IS 'Primary key column.';

COMMENT ON COLUMN PRODUCT_INFORMATION.PRODUCT_DESCRIPTION IS 'Primary language description corresponding to translated_description in
oe.product_descriptions, added to provide non-NLS text columns for OC views
to accss.';

COMMENT ON COLUMN PRODUCT_INFORMATION.CATEGORY_ID IS 'Low cardinality column, can be used for bitmap index.
Schema SH uses it as foreign key';

COMMENT ON COLUMN PRODUCT_INFORMATION.WEIGHT_CLASS IS 'Low cardinality column, can be used for bitmap index.';

COMMENT ON COLUMN PRODUCT_INFORMATION.WARRANTY_PERIOD IS 'INTERVAL YEAER TO MONTH column, low cardinality, can be used for bitmap
index.';

COMMENT ON COLUMN PRODUCT_INFORMATION.SUPPLIER_ID IS 'Offers possibility of extensions outside Common Schema.';

COMMENT ON COLUMN PRODUCT_INFORMATION.PRODUCT_STATUS IS 'Check constraint. Appropriate for complex rules, such as "All products in
status PRODUCTION must have at least one inventory entry." Also appropriate
for a trigger auditing status change.';



---

--- CREATE INDEX: PROD_SUPPLIER_IX

---

CREATE INDEX PROD_SUPPLIER_IX ON PRODUCT_INFORMATION

(

	SUPPLIER_ID

)

	TABLESPACE EXAMPLE

;

PROMOTIONS

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  PROMO_ID NUMBER(6)         PROMO_ID_PK
<default>
  PROMO_NAME VARCHAR2(20)           <default>

Indexes
Name Tablespace Associated Key Members
PROMO_ID_PK EXAMPLE PROMO_ID_PK PROMO_ID

Keys
Type Name Associated Index Members
PROMO_ID_PK PROMO_ID_PK PROMOTIONS. PROMO_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: PROMOTIONS

---

CREATE TABLE PROMOTIONS

(

	PROMO_ID NUMBER(6) NOT NULL,

	PROMO_NAME VARCHAR2(20),

	PRIMARY KEY (PROMO_ID)

)

	TABLESPACE EXAMPLE

;

PURCHASEORDER

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: USERS


Indexes
Name Tablespace Associated Key Members
LINEITEM_TABLE_MEMBERS USERS
ACTION_TABLE_MEMBERS USERS

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE INDEX: LINEITEM_TABLE_MEMBERS

---

CREATE UNIQUE INDEX LINEITEM_TABLE_MEMBERS ON PURCHASEORDER

(

	"XMLDATA"."LINEITEMS"."LINEITEM"

)

	TABLESPACE USERS

;



---

--- CREATE INDEX: ACTION_TABLE_MEMBERS

---

CREATE UNIQUE INDEX ACTION_TABLE_MEMBERS ON PURCHASEORDER

(

	"XMLDATA"."ACTIONS"."ACTION"

)

	TABLESPACE USERS

;



---

--- CREATE TRIGGER: PURCHASEORDER$xd

---

CREATE OR REPLACE TRIGGER PURCHASEORDER$xd

	BEFORE

	DELETE OR UPDATE ON PURCHASEORDER

	FOR EACH ROW

BEGIN  IF (deleting) THEN xdb.xdb_pitrig_pkg.pitrig_del('OE','PURCHASEORDER', :old.sys_nc_oid$, 'C6BBA58C14B540A1AA7E833B0D6B2594' ); END IF;   IF (updating) THEN xdb.xdb_pitrig_pkg.pitrig_upd('OE','PURCHASEORDER', :old.sys_nc_oid$, 'C6BBA58C14B540A1AA7E833B0D6B2594', user ); END IF; END;

/



REGIONS

ORGANIZATION: HEAP
Owner: HR
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  REGION_ID NUMBER       COUNTRIES . REGION_ID REG_ID_PK
<default>
  REGION_NAME VARCHAR2(25)           <default>

Indexes
Name Tablespace Associated Key Members
REG_ID_PK EXAMPLE REG_ID_PK REGION_ID

Keys
Type Name Associated Index Members
REG_ID_PK REG_ID_PK REGIONS. REGION_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script
---

--- CREATE TABLE: REGIONS

---

CREATE TABLE REGIONS

(

	REGION_ID NUMBER NOT NULL,

	REGION_NAME VARCHAR2(25),

	PRIMARY KEY (REGION_ID)

)

	TABLESPACE EXAMPLE

;

WAREHOUSES

Warehouse data unspecific to any industry.

ORGANIZATION: HEAP
Owner: OE
Generate: True
TABLESPACE: EXAMPLE

Columns
Is PK Name Datatype Not Null FK Relations Parent Columns Child Columns In Keys Domain
  WAREHOUSE_ID NUMBER(3)       INVENTORIES . WAREHOUSE_ID WAREHOUSES_PK
<default>
  WAREHOUSE_SPEC XMLTYPE           <default>
  WAREHOUSE_NAME VARCHAR2(35)           <default>
  LOCATION_ID NUMBER(4)   WAREHOUSES_LOCATION_FK LOCATIONS.LOCATION_ID     LOCATION_ID
  WH_GEO_LOCATION SDO_GEOMETRY           <default>

Indexes
Name Tablespace Associated Key Members
WAREHOUSES_PK EXAMPLE WAREHOUSES_PK WAREHOUSE_ID
WHS_LOCATION_IX EXAMPLE WAREHOUSES_LOCATION_FK LOCATION_ID

Keys
Type Name Associated Index Members
WAREHOUSES_PK WAREHOUSES_PK WAREHOUSES. WAREHOUSE_ID

Relations
Name Delete Rule Parent Table Parent Columns Child Columns
WAREHOUSES_LOCATION_FK Set Null LOCATIONS LOCATIONS.LOCATION_ID
LOCATION_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script
---

--- CREATE TABLE: WAREHOUSES

---

CREATE TABLE WAREHOUSES

(

	WAREHOUSE_ID NUMBER(3) NOT NULL,

	WAREHOUSE_SPEC XMLTYPE,

	WAREHOUSE_NAME VARCHAR2(35),

	LOCATION_ID NUMBER(4) NOT NULL,

	WH_GEO_LOCATION SDO_GEOMETRY,

	PRIMARY KEY (WAREHOUSE_ID)

)

	TABLESPACE EXAMPLE

;

COMMENT ON TABLE WAREHOUSES IS 'Warehouse data unspecific to any industry.';

COMMENT ON COLUMN WAREHOUSES.WAREHOUSE_ID IS 'Primary key column.';

COMMENT ON COLUMN WAREHOUSES.LOCATION_ID IS 'Primary key of locations table';

COMMENT ON COLUMN WAREHOUSES.WH_GEO_LOCATION IS 'Primary key column, references hr.locations.location_id.';



---

--- CREATE INDEX: WHS_LOCATION_IX

---

CREATE INDEX WHS_LOCATION_IX ON WAREHOUSES

(

	LOCATION_ID

)

	TABLESPACE EXAMPLE

;



---

--- CREATE FOREIGN KEY CONSTRAINT: WAREHOUSES_LOCATION_FK

---

ALTER TABLE WAREHOUSES ADD 

	FOREIGN KEY (LOCATION_ID)

		REFERENCES LOCATIONS (LOCATION_ID) ON DELETE SET NULL

;





Views

ACCOUNT_MANAGERS

Selects
Name

ACCOUNT_MANAGERS_1


Select Columns
Name Expression Referenced Objects
ACCT_MGR c.account_mgr_id c
CUSTOMERS. ACCOUNT_MGR_ID
REGION cr.region_id cr
COUNTRIES. REGION_ID
COUNTRY c.cust_address.country_id
PROVINCE c.cust_address.state_province
NUM_CUSTOMERS count(*)

Where

c.cust_address.country_id = cr.country_id


Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: ACCOUNT_MANAGERS

---

CREATE OR REPLACE VIEW ACCOUNT_MANAGERS

(

	ACCT_MGR,

	REGION,

	COUNTRY,

	PROVINCE,

	NUM_CUSTOMERS

) AS

SELECT

	c.account_mgr_id AS ACCT_MGR,

	cr.region_id AS REGION,

	c.cust_address.country_id AS COUNTRY,

	c.cust_address.state_province AS PROVINCE,

	count(*) AS NUM_CUSTOMERS

FROM

	CUSTOMERS c,

	COUNTRIES cr

WHERE	c.cust_address.country_id = cr.country_id

GROUP BY	ROLLUP (c.account_mgr_id,
                 cr.region_id,
                 c.cust_address.country_id,
                 c.cust_address.state_province)

;



BOMBAY_INVENTORY

Selects
Name

BOMBAY_INVENTORY_1


Select Columns
Name Expression Referenced Objects
PRODUCT_ID p.product_id p
PRODUCT_ID
PRODUCT_NAME p.product_name p
PRODUCT_NAME
QUANTITY_ON_HAND i.quantity_on_hand i
INVENTORIES. QUANTITY_ON_HAND

Where

p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id AND w.warehouse_name = 'Bombay'


Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: BOMBAY_INVENTORY

---

CREATE OR REPLACE VIEW BOMBAY_INVENTORY

(

	PRODUCT_ID,

	PRODUCT_NAME,

	QUANTITY_ON_HAND

) AS

SELECT

	p.product_id,

	p.product_name,

	i.quantity_on_hand

FROM

	INVENTORIES i,

	WAREHOUSES w,

	PRODUCTS p

WHERE	p.product_id = i.product_id
AND    i.warehouse_id = w.warehouse_id
AND    w.warehouse_name = 'Bombay'

;



CUSTOMERS_VIEW

Selects
Name

CUSTOMERS_VIEW_1


Select Columns
Name Expression Referenced Objects
CUSTOMER_ID c.customer_id c
CUSTOMERS. CUSTOMER_ID
CUST_FIRST_NAME c.cust_first_name c
CUSTOMERS. CUST_FIRST_NAME
CUST_LAST_NAME c.cust_last_name c
CUSTOMERS. CUST_LAST_NAME
street_address c.cust_address.street_address
postal_code c.cust_address.postal_code
city c.cust_address.city
state_province c.cust_address.state_province
COUNTRY_ID co.country_id co
COUNTRIES. COUNTRY_ID
COUNTRY_NAME co.country_name co
COUNTRIES. COUNTRY_NAME
REGION_ID co.region_id co
COUNTRIES. REGION_ID
NLS_LANGUAGE c.nls_language c
CUSTOMERS. NLS_LANGUAGE
NLS_TERRITORY c.nls_territory c
CUSTOMERS. NLS_TERRITORY
CREDIT_LIMIT c.credit_limit c
CUSTOMERS. CREDIT_LIMIT
CUST_EMAIL c.cust_email c
CUSTOMERS. CUST_EMAIL
Primary_Phone_number substr(get_phone_number_f(1,phone_numbers),1,25) CUSTOMERS. PHONE_NUMBERS
CUSTOMERS
Phone_number_2 substr(get_phone_number_f(2,phone_numbers),1,25) CUSTOMERS. PHONE_NUMBERS
CUSTOMERS
Phone_number_3 substr(get_phone_number_f(3,phone_numbers),1,25) CUSTOMERS. PHONE_NUMBERS
CUSTOMERS
Phone_number_4 substr(get_phone_number_f(4,phone_numbers),1,25) CUSTOMERS. PHONE_NUMBERS
CUSTOMERS
Phone_number_5 substr(get_phone_number_f(5,phone_numbers),1,25) CUSTOMERS. PHONE_NUMBERS
CUSTOMERS
ACCOUNT_MGR_ID c.account_mgr_id c
CUSTOMERS. ACCOUNT_MGR_ID
location_gtype c.cust_geo_location.sdo_gtype
location_srid c.cust_geo_location.sdo_srid
location_x c.cust_geo_location.sdo_point.x
location_y c.cust_geo_location.sdo_point.y
location_z c.cust_geo_location.sdo_point.z

Where

c.cust_address.country_id = co.country_id(+)


Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: CUSTOMERS_VIEW

---

CREATE OR REPLACE VIEW CUSTOMERS_VIEW

(

	CUSTOMER_ID,

	CUST_FIRST_NAME,

	CUST_LAST_NAME,

	STREET_ADDRESS,

	POSTAL_CODE,

	CITY,

	STATE_PROVINCE,

	COUNTRY_ID,

	COUNTRY_NAME,

	REGION_ID,

	NLS_LANGUAGE,

	NLS_TERRITORY,

	CREDIT_LIMIT,

	CUST_EMAIL,

	PRIMARY_PHONE_NUMBER,

	PHONE_NUMBER_2,

	PHONE_NUMBER_3,

	PHONE_NUMBER_4,

	PHONE_NUMBER_5,

	ACCOUNT_MGR_ID,

	LOCATION_GTYPE,

	LOCATION_SRID,

	LOCATION_X,

	LOCATION_Y,

	LOCATION_Z

) AS

SELECT

	c.customer_id,

	c.cust_first_name,

	c.cust_last_name,

	c.cust_address.street_address AS street_address,

	c.cust_address.postal_code AS postal_code,

	c.cust_address.city AS city,

	c.cust_address.state_province AS state_province,

	co.country_id,

	co.country_name,

	co.region_id,

	c.nls_language,

	c.nls_territory,

	c.credit_limit,

	c.cust_email,

	substr(get_phone_number_f(1,phone_numbers),1,25) AS Primary_Phone_number,

	substr(get_phone_number_f(2,phone_numbers),1,25) AS Phone_number_2,

	substr(get_phone_number_f(3,phone_numbers),1,25) AS Phone_number_3,

	substr(get_phone_number_f(4,phone_numbers),1,25) AS Phone_number_4,

	substr(get_phone_number_f(5,phone_numbers),1,25) AS Phone_number_5,

	c.account_mgr_id,

	c.cust_geo_location.sdo_gtype AS location_gtype,

	c.cust_geo_location.sdo_srid AS location_srid,

	c.cust_geo_location.sdo_point.x AS location_x,

	c.cust_geo_location.sdo_point.y AS location_y,

	c.cust_geo_location.sdo_point.z AS location_z

FROM

	COUNTRIES co,

	CUSTOMERS c

WHERE	c.cust_address.country_id = co.country_id(+)

;



EMP_DETAILS_VIEW

Selects
Name

EMP_DETAILS_VIEW_1


Select Columns
Name Expression Referenced Objects
EMPLOYEE_ID e.employee_id e
EMPLOYEES. EMPLOYEE_ID
JOB_ID e.job_id e
EMPLOYEES. JOB_ID
MANAGER_ID e.manager_id e
EMPLOYEES. MANAGER_ID
DEPARTMENT_ID e.department_id e
EMPLOYEES. DEPARTMENT_ID
LOCATION_ID d.location_id d
DEPARTMENTS. LOCATION_ID
COUNTRY_ID l.country_id l
LOCATIONS. COUNTRY_ID
FIRST_NAME e.first_name e
EMPLOYEES. FIRST_NAME
LAST_NAME e.last_name e
EMPLOYEES. LAST_NAME
SALARY e.salary e
EMPLOYEES. SALARY
COMMISSION_PCT e.commission_pct e
EMPLOYEES. COMMISSION_PCT
DEPARTMENT_NAME d.department_name d
DEPARTMENTS. DEPARTMENT_NAME
JOB_TITLE j.job_title j
JOBS. JOB_TITLE
CITY l.city l
LOCATIONS. CITY
STATE_PROVINCE l.state_province l
LOCATIONS. STATE_PROVINCE
COUNTRY_NAME c.country_name c
COUNTRIES. COUNTRY_NAME
REGION_NAME r.region_name r
REGIONS. REGION_NAME

Where

e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id AND j.job_id = e.job_id


Appears in Diagrams
Diagram

OEHR.Diagram1

HR.Diagram 1

Create Script

---

--- CREATE VIEW: EMP_DETAILS_VIEW

---

CREATE OR REPLACE VIEW EMP_DETAILS_VIEW

(

	EMPLOYEE_ID,

	JOB_ID,

	MANAGER_ID,

	DEPARTMENT_ID,

	LOCATION_ID,

	COUNTRY_ID,

	FIRST_NAME,

	LAST_NAME,

	SALARY,

	COMMISSION_PCT,

	DEPARTMENT_NAME,

	JOB_TITLE,

	CITY,

	STATE_PROVINCE,

	COUNTRY_NAME,

	REGION_NAME

) AS

SELECT

	e.employee_id,

	e.job_id,

	e.manager_id,

	e.department_id,

	d.location_id,

	l.country_id,

	e.first_name,

	e.last_name,

	e.salary,

	e.commission_pct,

	d.department_name,

	j.job_title,

	l.city,

	l.state_province,

	c.country_name,

	r.region_name

FROM

	EMPLOYEES e,

	DEPARTMENTS d,

	JOBS j,

	LOCATIONS l,

	COUNTRIES c,

	REGIONS r

WHERE	e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id

WITH READ ONLY

;



OC_CORPORATE_CUSTOMERS

Selects
Name

OC_CORPORATE_CUSTOMERS_1


Select Columns
Name Expression Referenced Objects
CUSTOMER_ID c.customer_id c
CUSTOMERS. CUSTOMER_ID
CUST_FIRST_NAME c.cust_first_name c
CUSTOMERS. CUST_FIRST_NAME
CUST_LAST_NAME c.cust_last_name c
CUSTOMERS. CUST_LAST_NAME
CUST_ADDRESS c.cust_address c
CUSTOMERS. CUST_ADDRESS
PHONE_NUMBERS c.phone_numbers c
CUSTOMERS. PHONE_NUMBERS
NLS_LANGUAGE c.nls_language c
CUSTOMERS. NLS_LANGUAGE
NLS_TERRITORY c.nls_territory c
CUSTOMERS. NLS_TERRITORY
CREDIT_LIMIT c.credit_limit c
CUSTOMERS. CREDIT_LIMIT
CUST_EMAIL c.cust_email c
CUSTOMERS. CUST_EMAIL
CAST(MULTISET(SELECT o.order_id, o.order_mode, MAKE_REF(oc_customers,o.customer_id), o.order_status, o.order_total,o.sales_rep_id, CAST(MULTISET(SELECT l.order_id,l.line_item_id, l.unit_price,l.quantity, make_ref(oc_product_information, l.product_id) FROM order_items l WHERE o.order_id = l.order_id) AS order_item_list_typ) FROM orders o WHERE c.customer_id = o.customer_id) AS order_list_typ) CAST(MULTISET(SELECT o.order_id, o.order_mode, MAKE_REF(oc_customers,o.customer_id), o.order_status, o.order_total,o.sales_rep_id, CAST(MULTISET(SELECT l.order_id,l.line_item_id, l.unit_price,l.quantity, make_ref(oc_product_information, l.product_id) FROM order_items l WHERE o.order_id = l.order_id) AS order_item_list_typ) FROM orders o WHERE c.customer_id = o.customer_id) AS order_list_typ) c
CUSTOMERS. CUSTOMER_ID
ACCOUNT_MGR_ID c.account_mgr_id c
CUSTOMERS. ACCOUNT_MGR_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: OC_CORPORATE_CUSTOMERS

---

CREATE OR REPLACE VIEW OC_CORPORATE_CUSTOMERS

	OF CORPORATE_CUSTOMER_TYP UNDER OC_CUSTOMERS

(

	CUSTOMER_ID,

	CUST_FIRST_NAME,

	CUST_LAST_NAME,

	CUST_ADDRESS,

	PHONE_NUMBERS,

	NLS_LANGUAGE,

	NLS_TERRITORY,

	CREDIT_LIMIT,

	CUST_EMAIL,

	CUST_ORDERS,

	ACCOUNT_MGR_ID

) AS

SELECT

	c.customer_id,

	c.cust_first_name,

	c.cust_last_name,

	c.cust_address,

	c.phone_numbers,

	c.nls_language,

	c.nls_territory,

	c.credit_limit,

	c.cust_email,

	CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                         l.unit_price,l.quantity,
                                         make_ref(oc_product_information,
                                                    l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                            FROM orders o
                            WHERE c.customer_id = o.customer_id)
              AS order_list_typ),

	c.account_mgr_id

FROM

	CUSTOMERS c

;



OC_CUSTOMERS

Selects
Name

OC_CUSTOMERS_1


Select Columns
Name Expression Referenced Objects
CUSTOMER_ID c.customer_id c
CUSTOMERS. CUSTOMER_ID
CUST_FIRST_NAME c.cust_first_name c
CUSTOMERS. CUST_FIRST_NAME
CUST_LAST_NAME c.cust_last_name c
CUSTOMERS. CUST_LAST_NAME
CUST_ADDRESS c.cust_address c
CUSTOMERS. CUST_ADDRESS
PHONE_NUMBERS c.phone_numbers c
CUSTOMERS. PHONE_NUMBERS
NLS_LANGUAGE c.nls_language c
CUSTOMERS. NLS_LANGUAGE
NLS_TERRITORY c.nls_territory c
CUSTOMERS. NLS_TERRITORY
CREDIT_LIMIT c.credit_limit c
CUSTOMERS. CREDIT_LIMIT
CUST_EMAIL c.cust_email c
CUSTOMERS. CUST_EMAIL
CAST(MULTISET(SELECT o.order_id, o.order_mode, MAKE_REF(oc_customers,o.customer_id), o.order_status, o.order_total,o.sales_rep_id, CAST(MULTISET(SELECT l.order_id,l.line_item_id, l.unit_price,l.quantity, MAKE_REF(oc_product_information, l.product_id) FROM order_items l WHERE o.order_id = l.order_id) AS order_item_list_typ) FROM orders o WHERE c.customer_id = o.customer_id) AS order_list_typ) CAST(MULTISET(SELECT o.order_id, o.order_mode, MAKE_REF(oc_customers,o.customer_id), o.order_status, o.order_total,o.sales_rep_id, CAST(MULTISET(SELECT l.order_id,l.line_item_id, l.unit_price,l.quantity, MAKE_REF(oc_product_information, l.product_id) FROM order_items l WHERE o.order_id = l.order_id) AS order_item_list_typ) FROM orders o WHERE c.customer_id = o.customer_id) AS order_list_typ) c
CUSTOMERS. CUSTOMER_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: OC_CUSTOMERS

---

CREATE OR REPLACE VIEW OC_CUSTOMERS

	OF CUSTOMER_TYP WITH OBJECT IDENTIFIER CUSTOMER_ID

(

	CUSTOMER_ID,

	CUST_FIRST_NAME,

	CUST_LAST_NAME,

	CUST_ADDRESS,

	PHONE_NUMBERS,

	NLS_LANGUAGE,

	NLS_TERRITORY,

	CREDIT_LIMIT,

	CUST_EMAIL,

	CUST_ORDERS

) AS

SELECT

	c.customer_id,

	c.cust_first_name,

	c.cust_last_name,

	c.cust_address,

	c.phone_numbers,

	c.nls_language,

	c.nls_territory,

	c.credit_limit,

	c.cust_email,

	CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                                    l.unit_price,l.quantity,
                                             MAKE_REF(oc_product_information,
                                                      l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                         FROM orders o
                         WHERE c.customer_id = o.customer_id)
                AS order_list_typ)

FROM

	CUSTOMERS c

;



OC_INVENTORIES

Selects
Name

OC_INVENTORIES_1


Select Columns
Name Expression Referenced Objects
PRODUCT_ID i.product_id INVENTORIES. PRODUCT_ID
i
warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id) warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id) WAREHOUSES. LOCATION_ID
WAREHOUSES. WAREHOUSE_NAME
WAREHOUSES. WAREHOUSE_ID
w
QUANTITY_ON_HAND i.quantity_on_hand INVENTORIES. QUANTITY_ON_HAND
i

Where

i.warehouse_id=w.warehouse_id


Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: OC_INVENTORIES

---

CREATE OR REPLACE VIEW OC_INVENTORIES

	OF INVENTORY_TYP WITH OBJECT IDENTIFIER PRODUCT_ID

(

	PRODUCT_ID,

	WAREHOUSE,

	QUANTITY_ON_HAND

) AS

SELECT

	i.product_id,

	warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),

	i.quantity_on_hand

FROM

	INVENTORIES i,

	WAREHOUSES w

WHERE	i.warehouse_id=w.warehouse_id

;



OC_ORDERS

Selects
Name

OC_ORDERS_1


Select Columns
Name Expression Referenced Objects
ORDER_ID o.order_id o
ORDERS. ORDER_ID
ORDER_MODE o.order_mode o
ORDERS. ORDER_MODE
MAKE_REF(oc_customers,o.customer_id) MAKE_REF(oc_customers,o.customer_id) o
ORDERS. CUSTOMER_ID
ORDER_STATUS o.order_status o
ORDERS. ORDER_STATUS
ORDER_TOTAL o.order_total o
ORDERS. ORDER_TOTAL
SALES_REP_ID o.sales_rep_id o
ORDERS. SALES_REP_ID
CAST(MULTISET(SELECT l.order_id,l.line_item_id,l.unit_price,l.quantity, make_ref(oc_product_information,l.product_id) FROM order_items l WHERE o.order_id = l.order_id) AS order_item_list_typ) CAST(MULTISET(SELECT l.order_id,l.line_item_id,l.unit_price,l.quantity, make_ref(oc_product_information,l.product_id) FROM order_items l WHERE o.order_id = l.order_id) AS order_item_list_typ) o
ORDERS. ORDER_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: OC_ORDERS

---

CREATE OR REPLACE VIEW OC_ORDERS

	OF ORDER_TYP WITH OBJECT IDENTIFIER ORDER_ID

(

	ORDER_ID,

	ORDER_MODE,

	CUSTOMER_REF,

	ORDER_STATUS,

	ORDER_TOTAL,

	SALES_REP_ID,

	ORDER_ITEM_LIST

) AS

SELECT

	o.order_id,

	o.order_mode,

	MAKE_REF(oc_customers,o.customer_id),

	o.order_status,

	o.order_total,

	o.sales_rep_id,

	CAST(MULTISET(SELECT l.order_id,l.line_item_id,l.unit_price,l.quantity,
                       make_ref(oc_product_information,l.product_id)
                     FROM order_items l
                     WHERE o.order_id = l.order_id)
            AS order_item_list_typ)

FROM

	ORDERS o

;



OC_PRODUCT_INFORMATION

Selects
Name

OC_PRODUCT_INFORMATION_1


Select Columns
Name Expression Referenced Objects
PRODUCT_ID p.product_id p
PRODUCT_INFORMATION. PRODUCT_ID
PRODUCT_NAME p.product_name p
PRODUCT_INFORMATION. PRODUCT_NAME
PRODUCT_DESCRIPTION p.product_description p
PRODUCT_INFORMATION. PRODUCT_DESCRIPTION
CATEGORY_ID p.category_id p
PRODUCT_INFORMATION. CATEGORY_ID
WEIGHT_CLASS p.weight_class p
PRODUCT_INFORMATION. WEIGHT_CLASS
WARRANTY_PERIOD p.warranty_period p
PRODUCT_INFORMATION. WARRANTY_PERIOD
SUPPLIER_ID p.supplier_id p
PRODUCT_INFORMATION. SUPPLIER_ID
PRODUCT_STATUS p.product_status p
PRODUCT_INFORMATION. PRODUCT_STATUS
LIST_PRICE p.list_price p
PRODUCT_INFORMATION. LIST_PRICE
MIN_PRICE p.min_price p
PRODUCT_INFORMATION. MIN_PRICE
CATALOG_URL p.catalog_url p
PRODUCT_INFORMATION. CATALOG_URL
CAST(MULTISET(SELECT i.product_id,i.warehouse,i.quantity_on_hand FROM oc_inventories i WHERE p.product_id=i.product_id) AS inventory_list_typ) CAST(MULTISET(SELECT i.product_id,i.warehouse,i.quantity_on_hand FROM oc_inventories i WHERE p.product_id=i.product_id) AS inventory_list_typ) p
PRODUCT_INFORMATION. PRODUCT_ID

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: OC_PRODUCT_INFORMATION

---

CREATE OR REPLACE VIEW OC_PRODUCT_INFORMATION

	OF PRODUCT_INFORMATION_TYP WITH OBJECT IDENTIFIER PRODUCT_ID

(

	PRODUCT_ID,

	PRODUCT_NAME,

	PRODUCT_DESCRIPTION,

	CATEGORY_ID,

	WEIGHT_CLASS,

	WARRANTY_PERIOD,

	SUPPLIER_ID,

	PRODUCT_STATUS,

	LIST_PRICE,

	MIN_PRICE,

	CATALOG_URL,

	INVENTORY_LIST

) AS

SELECT

	p.product_id,

	p.product_name,

	p.product_description,

	p.category_id,

	p.weight_class,

	p.warranty_period,

	p.supplier_id,

	p.product_status,

	p.list_price,

	p.min_price,

	p.catalog_url,

	CAST(MULTISET(SELECT i.product_id,i.warehouse,i.quantity_on_hand
                         FROM oc_inventories i
                         WHERE p.product_id=i.product_id)
                AS inventory_list_typ)

FROM

	PRODUCT_INFORMATION p

;



ORDERS_VIEW

Selects
Name

ORDERS_VIEW_1


Select Columns
Name Expression Referenced Objects
ORDER_ID order_id ORDERS. ORDER_ID
ORDERS
ORDER_DATE TO_DATE(TO_CHAR(order_date,'DD-MON-YY HH:MI:SS'),'DD-MON-YY HH:MI:SS') ORDERS. ORDER_DATE
ORDERS
ORDER_MODE order_mode ORDERS. ORDER_MODE
ORDERS
CUSTOMER_ID customer_id ORDERS. CUSTOMER_ID
ORDERS
ORDER_STATUS order_status ORDERS. ORDER_STATUS
ORDERS
ORDER_TOTAL order_total ORDERS. ORDER_TOTAL
ORDERS
SALES_REP_ID sales_rep_id ORDERS. SALES_REP_ID
ORDERS
PROMOTION_ID promotion_id ORDERS. PROMOTION_ID
ORDERS

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: ORDERS_VIEW

---

CREATE OR REPLACE VIEW ORDERS_VIEW

(

	ORDER_ID,

	ORDER_DATE,

	ORDER_MODE,

	CUSTOMER_ID,

	ORDER_STATUS,

	ORDER_TOTAL,

	SALES_REP_ID,

	PROMOTION_ID

) AS

SELECT

	order_id,

	TO_DATE(TO_CHAR(order_date,'DD-MON-YY HH:MI:SS'),'DD-MON-YY HH:MI:SS') AS ORDER_DATE,

	order_mode,

	customer_id,

	order_status,

	order_total,

	sales_rep_id,

	promotion_id

FROM

	ORDERS

;



PRODUCT_PRICES

Selects
Name

PRODUCT_PRICES_1


Select Columns
Name Expression Referenced Objects
CATEGORY_ID category_id PRODUCT_INFORMATION. CATEGORY_ID
PRODUCT_INFORMATION
"#_OF_PRODUCTS" COUNT(*)
low_price MIN(list_price) PRODUCT_INFORMATION. LIST_PRICE
PRODUCT_INFORMATION
high_price MAX(list_price) PRODUCT_INFORMATION. LIST_PRICE
PRODUCT_INFORMATION

Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: PRODUCT_PRICES

---

CREATE OR REPLACE VIEW PRODUCT_PRICES

(

	CATEGORY_ID,

	#_OF_PRODUCTS,

	LOW_PRICE,

	HIGH_PRICE

) AS

SELECT

	category_id,

	COUNT(*) AS "#_OF_PRODUCTS",

	MIN(list_price) AS low_price,

	MAX(list_price) AS high_price

FROM

	PRODUCT_INFORMATION

GROUP BY	category_id

;



PRODUCTS

Selects
Name

PRODUCTS_1


Select Columns
Name Expression Referenced Objects
PRODUCT_ID i.product_id i
PRODUCT_INFORMATION. PRODUCT_ID
LANGUAGE_ID d.language_id d
PRODUCT_DESCRIPTIONS. LANGUAGE_ID
product_name CASE WHEN d.language_id IS NOT NULL THEN d.translated_name ELSE TRANSLATE(i.product_name USING NCHAR_CS) END d
i
PRODUCT_INFORMATION. PRODUCT_NAME
PRODUCT_DESCRIPTIONS. TRANSLATED_NAME
PRODUCT_DESCRIPTIONS. LANGUAGE_ID
CATEGORY_ID i.category_id i
PRODUCT_INFORMATION. CATEGORY_ID
product_description CASE WHEN d.language_id IS NOT NULL THEN d.translated_description ELSE TRANSLATE(i.product_description USING NCHAR_CS) END d
i
PRODUCT_INFORMATION. PRODUCT_DESCRIPTION
PRODUCT_DESCRIPTIONS. TRANSLATED_DESCRIPTION
PRODUCT_DESCRIPTIONS. LANGUAGE_ID
WEIGHT_CLASS i.weight_class i
PRODUCT_INFORMATION. WEIGHT_CLASS
WARRANTY_PERIOD i.warranty_period i
PRODUCT_INFORMATION. WARRANTY_PERIOD
SUPPLIER_ID i.supplier_id i
PRODUCT_INFORMATION. SUPPLIER_ID
PRODUCT_STATUS i.product_status i
PRODUCT_INFORMATION. PRODUCT_STATUS
LIST_PRICE i.list_price i
PRODUCT_INFORMATION. LIST_PRICE
MIN_PRICE i.min_price i
PRODUCT_INFORMATION. MIN_PRICE
CATALOG_URL i.catalog_url i
PRODUCT_INFORMATION. CATALOG_URL

Where

d.product_id (+) = i.product_id AND d.language_id (+) = sys_context('USERENV','LANG')


Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: PRODUCTS

---

CREATE OR REPLACE VIEW PRODUCTS

(

	PRODUCT_ID,

	LANGUAGE_ID,

	PRODUCT_NAME,

	CATEGORY_ID,

	PRODUCT_DESCRIPTION,

	WEIGHT_CLASS,

	WARRANTY_PERIOD,

	SUPPLIER_ID,

	PRODUCT_STATUS,

	LIST_PRICE,

	MIN_PRICE,

	CATALOG_URL

) AS

SELECT

	i.product_id,

	d.language_id,

	CASE WHEN d.language_id IS NOT NULL
            THEN d.translated_name
            ELSE TRANSLATE(i.product_name USING NCHAR_CS)
       END AS product_name,

	i.category_id,

	CASE WHEN d.language_id IS NOT NULL
            THEN d.translated_description
            ELSE TRANSLATE(i.product_description USING NCHAR_CS)
       END AS product_description,

	i.weight_class,

	i.warranty_period,

	i.supplier_id,

	i.product_status,

	i.list_price,

	i.min_price,

	i.catalog_url

FROM

	PRODUCT_INFORMATION i,

	PRODUCT_DESCRIPTIONS d

WHERE	d.product_id  (+) = i.product_id
AND    d.language_id (+) = sys_context('USERENV','LANG')

;



SYDNEY_INVENTORY

Selects
Name

SYDNEY_INVENTORY_1


Select Columns
Name Expression Referenced Objects
PRODUCT_ID p.product_id p
PRODUCT_ID
PRODUCT_NAME p.product_name p
PRODUCT_NAME
QUANTITY_ON_HAND i.quantity_on_hand i
INVENTORIES. QUANTITY_ON_HAND

Where

p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id AND w.warehouse_name = 'Sydney'


Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: SYDNEY_INVENTORY

---

CREATE OR REPLACE VIEW SYDNEY_INVENTORY

(

	PRODUCT_ID,

	PRODUCT_NAME,

	QUANTITY_ON_HAND

) AS

SELECT

	p.product_id,

	p.product_name,

	i.quantity_on_hand

FROM

	INVENTORIES i,

	WAREHOUSES w,

	PRODUCTS p

WHERE	p.product_id = i.product_id
AND    i.warehouse_id = w.warehouse_id
AND    w.warehouse_name = 'Sydney'

;



TORONTO_INVENTORY

Selects
Name

TORONTO_INVENTORY_1


Select Columns
Name Expression Referenced Objects
PRODUCT_ID p.product_id p
PRODUCT_ID
PRODUCT_NAME p.product_name p
PRODUCT_NAME
QUANTITY_ON_HAND i.quantity_on_hand i
INVENTORIES. QUANTITY_ON_HAND

Where

p.product_id = i.product_id AND i.warehouse_id = w.warehouse_id AND w.warehouse_name = 'Toronto'


Appears in Diagrams
Diagram

OEHR.Diagram1

OE.Diagram 1

Create Script

---

--- CREATE VIEW: TORONTO_INVENTORY

---

CREATE OR REPLACE VIEW TORONTO_INVENTORY

(

	PRODUCT_ID,

	PRODUCT_NAME,

	QUANTITY_ON_HAND

) AS

SELECT

	p.product_id,

	p.product_name,

	i.quantity_on_hand

FROM

	INVENTORIES i,

	WAREHOUSES w,

	PRODUCTS p

WHERE	p.product_id = i.product_id
AND    i.warehouse_id = w.warehouse_id
AND    w.warehouse_name = 'Toronto'

;



User-Defined Types

Name Type Used By
ACTION_TObject
ACTION_VVarray
ACTIONS_TObject
ANYDATAScaler
ANYDATASETScaler
ANYTYPEScaler
BFILEScaler
BIGINTScaler
BINARY ROWIDScaler
BINARY_DOUBLEScaler
BINARY_FLOATScaler
BLOBScaler
CANONICALScaler
CATALOG_TYPObject
CATEGORY_TYPObject
CFILEScaler
CHARScaler COUNTRIES.COUNTRY_ID
LOCATIONS.COUNTRY_ID
CHARACTERScaler
CLOBScaler
COMPOSITE_CATEGORY_TYPObject
CONTIGUOUS ARRAYScaler
CORPORATE_CUSTOMER_TYPObject
CUST_ADDRESS_TYPObject CUSTOMERS.CUST_ADDRESS
CUSTOMER_TYPObject
DATALINKScaler
DATEScaler EMPLOYEES.HIRE_DATE
JOB_HISTORY.START_DATE
JOB_HISTORY.END_DATE
CUSTOMERS.DATE_OF_BIRTH
DBCLOBScaler
DECIMALScaler
DOUBLEScaler
DOUBLE PRECISIONScaler
FLOATScaler
GRAPHICScaler
INTScaler
INTEGERScaler
INTERVAL DAY TO SECONDScaler
INTERVAL YEAR TO MONTHScaler PRODUCT_INFORMATION.WARRANTY_PERIOD
INVENTORY_LIST_TYPTable
INVENTORY_TYPObject
LEAF_CATEGORY_TYPObject
LINEITEM_TObject
LINEITEM_VVarray
LINEITEMS_TObject
LOB POINTERScaler
LONGScaler
LONG RAWScaler
LONG VARCHARScaler
LONG VARGRAPHICScaler
NAMED COLLECTIONScaler
NAMED OBJECTScaler
NCHARScaler
NCLOBScaler
NUMBERScaler COUNTRIES.REGION_ID
DEPARTMENTS.DEPARTMENT_ID
DEPARTMENTS.MANAGER_ID
DEPARTMENTS.LOCATION_ID
EMPLOYEES.EMPLOYEE_ID
EMPLOYEES.SALARY
EMPLOYEES.COMMISSION_PCT
EMPLOYEES.MANAGER_ID
EMPLOYEES.DEPARTMENT_ID
JOBS.MIN_SALARY
JOBS.MAX_SALARY
JOB_HISTORY.EMPLOYEE_ID
JOB_HISTORY.DEPARTMENT_ID
LOCATIONS.LOCATION_ID
REGIONS.REGION_ID
CATEGORIES_TAB.CATEGORY_ID
CATEGORIES_TAB.PARENT_CATEGORY_ID
CUSTOMERS.CUSTOMER_ID
CUSTOMERS.CREDIT_LIMIT
CUSTOMERS.ACCOUNT_MGR_ID
INVENTORIES.PRODUCT_ID
INVENTORIES.WAREHOUSE_ID
INVENTORIES.QUANTITY_ON_HAND
ORDERS.ORDER_ID
ORDERS.CUSTOMER_ID
ORDERS.ORDER_STATUS
ORDERS.ORDER_TOTAL
ORDERS.SALES_REP_ID
ORDERS.PROMOTION_ID
ORDER_ITEMS.ORDER_ID
ORDER_ITEMS.LINE_ITEM_ID
ORDER_ITEMS.PRODUCT_ID
ORDER_ITEMS.UNIT_PRICE
ORDER_ITEMS.QUANTITY
PRODUCT_DESCRIPTIONS.PRODUCT_ID
PRODUCT_INFORMATION.PRODUCT_ID
PRODUCT_INFORMATION.CATEGORY_ID
PRODUCT_INFORMATION.WEIGHT_CLASS
PRODUCT_INFORMATION.SUPPLIER_ID
PRODUCT_INFORMATION.LIST_PRICE
PRODUCT_INFORMATION.MIN_PRICE
PROMOTIONS.PROMO_ID
WAREHOUSES.WAREHOUSE_ID
WAREHOUSES.LOCATION_ID
NVARCHAR2Scaler PRODUCT_DESCRIPTIONS.TRANSLATED_NAME
PRODUCT_DESCRIPTIONS.TRANSLATED_DESCRIPTION
OCTETScaler
OIDScaler
ORDER_ITEM_LIST_TYPTable
ORDER_ITEM_TYPObject
ORDER_LIST_TYPTable
ORDER_TYPObject
PART_TObject
PHONE_LIST_TYPVarray CUSTOMERS.PHONE_NUMBERS
PL/SQL BINARY INTEGERScaler
PL/SQL BOOLEANScaler
PL/SQL COLLECTIONScaler
PL/SQL LONGScaler
PL/SQL LONG RAWScaler
PL/SQL NATURALScaler
PL/SQL NATURALNScaler
PL/SQL PLS INTEGERScaler
PL/SQL POSITIVEScaler
PL/SQL POSITIVENScaler
PL/SQL RECORDScaler
PL/SQL REF CURSORScaler
PL/SQL ROWIDScaler
PL/SQL STRINGScaler
POINTERScaler
PRODUCT_INFORMATION_TYPObject
PRODUCT_REF_LIST_TYPTable
PURCHASEORDER_TObject
RAWScaler
REALScaler
REFScaler
REJECTION_TObject
ROWIDScaler
SDO_ELEM_INFO_ARRAYVarray
SDO_GEOMETRYObject CUSTOMERS.CUST_GEO_LOCATION
WAREHOUSES.WH_GEO_LOCATION
SDO_ORDINATE_ARRAYVarray
SDO_POINT_TYPEObject
SHIPPING_INSTRUCTIONS_TObject
SIGNED BINARY INTEGER(16)Scaler
SIGNED BINARY INTEGER(32)Scaler
SIGNED BINARY INTEGER(8)Scaler
SMALLINTScaler
SUBCATEGORY_REF_LIST_TYPTable
TABLEScaler
TIMEScaler
TIME WITH TIME ZONEScaler
TIMESTAMPScaler
TIMESTAMP WITH LOCAL TIME ZONEScaler ORDERS.ORDER_DATE
TIMESTAMP WITH TIME ZONEScaler
UNSIGNED BINARY INTEGER(16)Scaler
UNSIGNED BINARY INTEGER(32)Scaler
UNSIGNED BINARY INTEGER(8)Scaler
UROWIDScaler
VARCHARScaler
VARCHAR2Scaler COUNTRIES.COUNTRY_NAME
DEPARTMENTS.DEPARTMENT_NAME
EMPLOYEES.FIRST_NAME
EMPLOYEES.LAST_NAME
EMPLOYEES.EMAIL
EMPLOYEES.PHONE_NUMBER
EMPLOYEES.JOB_ID
JOBS.JOB_ID
JOBS.JOB_TITLE
JOB_HISTORY.JOB_ID
LOCATIONS.STREET_ADDRESS
LOCATIONS.POSTAL_CODE
LOCATIONS.CITY
LOCATIONS.STATE_PROVINCE
REGIONS.REGION_NAME
CATEGORIES_TAB.CATEGORY_NAME
CATEGORIES_TAB.CATEGORY_DESCRIPTION
CUSTOMERS.CUST_FIRST_NAME
CUSTOMERS.CUST_LAST_NAME
CUSTOMERS.NLS_LANGUAGE
CUSTOMERS.NLS_TERRITORY
CUSTOMERS.CUST_EMAIL
CUSTOMERS.MARITAL_STATUS
CUSTOMERS.GENDER
CUSTOMERS.INCOME_LEVEL
ORDERS.ORDER_MODE
PRODUCT_DESCRIPTIONS.LANGUAGE_ID
PRODUCT_INFORMATION.PRODUCT_NAME
PRODUCT_INFORMATION.PRODUCT_DESCRIPTION
PRODUCT_INFORMATION.PRODUCT_STATUS
PRODUCT_INFORMATION.CATALOG_URL
PROMOTIONS.PROMO_NAME
WAREHOUSES.WAREHOUSE_NAME
<default>
VARGRAPHICScaler
VARYING ARRAYScaler
WAREHOUSE_TYPObject
XDB$RAW_LIST_TVarray
XMLTYPEObject WAREHOUSES.WAREHOUSE_SPEC

Tablespaces

CONSTANT_GROW_INDEXES

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_DB_1_DATABASE_C__ORACLE_PRODUCT_10_2_0_DB_2_DATABASE_REPOS_DF


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: CONSTANT_GROW_INDEXES

---

CREATE SMALLFILE TABLESPACE CONSTANT_GROW_INDEXES

	DATAFILE

		 SIZE 2M AUTOEXTEND OFF

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL UNIFORM

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: CONSTANT_GROW_INDEXES

---

DROP TABLESPACE CONSTANT_GROW_INDEXES INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

CONSTANT_GROW_TABLES

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_DB_1_DATABASE_C__ORACLE_PRODUCT_10_2_0_DB_2_DATABASE_REPOS_DF_TABLES


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: CONSTANT_GROW_TABLES

---

CREATE SMALLFILE TABLESPACE CONSTANT_GROW_TABLES

	DATAFILE

		 SIZE 2M AUTOEXTEND OFF

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL UNIFORM

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: CONSTANT_GROW_TABLES

---

DROP TABLESPACE CONSTANT_GROW_TABLES INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

DEPENDENCY_INDEXES

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_DB_1_DATABASE_C__ORACLE_PRODUCT_10_2_0_DB_2_DATABASE_REPOS_DF_DEP_INDEXES


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: DEPENDENCY_INDEXES

---

CREATE SMALLFILE TABLESPACE DEPENDENCY_INDEXES

	DATAFILE

		 SIZE 10M AUTOEXTEND OFF

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL UNIFORM

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: DEPENDENCY_INDEXES

---

DROP TABLESPACE DEPENDENCY_INDEXES INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

DEPENDENCY_TABLES

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_DB_1_DATABASE_C__ORACLE_PRODUCT_10_2_0_DB_2_DATABASE_REPOS_DF_DEP_TABLES


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: DEPENDENCY_TABLES

---

CREATE SMALLFILE TABLESPACE DEPENDENCY_TABLES

	DATAFILE

		 SIZE 2M AUTOEXTEND OFF

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL UNIFORM

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: DEPENDENCY_TABLES

---

DROP TABLESPACE DEPENDENCY_TABLES INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

DIAGRAM_INDEXES

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_DB_1_DATABASE_C__ORACLE_PRODUCT_10_2_0_DB_2_DATABASE_REPOS_DF_DIAG_IND


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: DIAGRAM_INDEXES

---

CREATE SMALLFILE TABLESPACE DIAGRAM_INDEXES

	DATAFILE

		 SIZE 4M AUTOEXTEND OFF

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL UNIFORM

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: DIAGRAM_INDEXES

---

DROP TABLESPACE DIAGRAM_INDEXES INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

EXAMPLE

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_ORADATA_ORCL_EXAMPLE01_DBF


Used By
COUNTRIES.COUNTRY_C_ID_PK
DEPARTMENTS
DEPARTMENTS.DEPT_ID_PK
DEPARTMENTS.DEPT_LOCATION_IX
EMPLOYEES
EMPLOYEES.EMP_EMAIL_UK
EMPLOYEES.EMP_EMP_ID_PK
EMPLOYEES.EMP_DEPARTMENT_IX
EMPLOYEES.EMP_JOB_IX
EMPLOYEES.EMP_MANAGER_IX
EMPLOYEES.EMP_NAME_IX
JOBS
JOBS.JOB_ID_PK
JOB_HISTORY
JOB_HISTORY.JHIST_EMP_ID_ST_DATE_PK
JOB_HISTORY.JHIST_JOB_IX
JOB_HISTORY.JHIST_EMPLOYEE_IX
JOB_HISTORY.JHIST_DEPARTMENT_IX
LOCATIONS
LOCATIONS.LOC_ID_PK
LOCATIONS.LOC_CITY_IX
LOCATIONS.LOC_STATE_PROVINCE_IX
LOCATIONS.LOC_COUNTRY_IX
REGIONS
REGIONS.REG_ID_PK
CUSTOMERS
CUSTOMERS.CUST_UPPER_NAME_IX
CUSTOMERS.CUSTOMERS_PK
CUSTOMERS.CUST_ACCOUNT_MANAGER_IX
CUSTOMERS.CUST_LNAME_IX
CUSTOMERS.CUST_EMAIL_IX
INVENTORIES
INVENTORIES.INVENTORY_IX
INVENTORIES.INV_PRODUCT_IX
ORDERS
ORDERS.ORDER_PK
ORDERS.ORD_SALES_REP_IX
ORDERS.ORD_CUSTOMER_IX
ORDERS.ORD_ORDER_DATE_IX
ORDER_ITEMS
ORDER_ITEMS.ORDER_ITEMS_PK
ORDER_ITEMS.ORDER_ITEMS_UK
ORDER_ITEMS.ITEM_ORDER_IX
ORDER_ITEMS.ITEM_PRODUCT_IX
PRODUCT_DESCRIPTIONS
PRODUCT_DESCRIPTIONS.PRD_DESC_PK
PRODUCT_DESCRIPTIONS.PROD_NAME_IX
PRODUCT_INFORMATION
PRODUCT_INFORMATION.PRODUCT_INFORMATION_PK
PRODUCT_INFORMATION.PROD_SUPPLIER_IX
PROMOTIONS
PROMOTIONS.PROMO_ID_PK
WAREHOUSES
WAREHOUSES.WAREHOUSES_PK
WAREHOUSES.WHS_LOCATION_IX

Create Script

---

--- CREATE TABLESPACE: EXAMPLE

---

CREATE SMALLFILE TABLESPACE EXAMPLE

	DATAFILE

		 SIZE 100M AUTOEXTEND ON NEXT 80 MAX -2147483648

	BLOCKSIZE 8K

	NOLOGGING

	EXTENT MANAGEMENT LOCAL AUTOALLOCATE

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: EXAMPLE

---

DROP TABLESPACE EXAMPLE INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

SYSAUX

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_ORADATA_ORCL_SYSAUX01_DBF


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: SYSAUX

---

CREATE SMALLFILE TABLESPACE SYSAUX

	DATAFILE

		 SIZE 310M AUTOEXTEND ON NEXT 1280 MAX -2147483648

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL AUTOALLOCATE

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: SYSAUX

---

DROP TABLESPACE SYSAUX INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

SYSTEM

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_ORADATA_ORCL_SYSTEM01_DBF


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: SYSTEM

---

CREATE SMALLFILE TABLESPACE SYSTEM

	DATAFILE

		 SIZE 490M AUTOEXTEND ON NEXT 1280 MAX -2147483648

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL AUTOALLOCATE

;


Drop Script

---

--- DROP TABLESPACE: SYSTEM

---

DROP TABLESPACE SYSTEM INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

TEMP

Type: TEMPORARY


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: TEMP

---

CREATE SMALLFILE TEMPORARY TABLESPACE TEMP

	EXTENT MANAGEMENT LOCAL UNIFORM

;


Drop Script

---

--- DROP TABLESPACE: TEMP

---

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

UNDOTBS1

Type: UNDO

Datafiles

C__ORACLE_PRODUCT_10_2_0_ORADATA_ORCL_UNDOTBS01_DBF


Used By
<Not Used>

Create Script

---

--- CREATE TABLESPACE: UNDOTBS1

---

CREATE SMALLFILE UNDO TABLESPACE UNDOTBS1

	DATAFILE

		 SIZE 30M AUTOEXTEND ON NEXT 640 MAX -2147483648

	EXTENT MANAGEMENT LOCAL AUTOALLOCATE

;


Drop Script

---

--- DROP TABLESPACE: UNDOTBS1

---

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

USERS

Type: PERMANENT

Datafiles

C__ORACLE_PRODUCT_10_2_0_ORADATA_ORCL_USERS01_DBF


Used By
CATEGORIES_TAB
CATEGORIES_TAB.SYS_C005389
PURCHASEORDER
PURCHASEORDER.LINEITEM_TABLE_MEMBERS
PURCHASEORDER.ACTION_TABLE_MEMBERS

Create Script

---

--- CREATE TABLESPACE: USERS

---

CREATE SMALLFILE TABLESPACE USERS

	DATAFILE

		 SIZE 5M AUTOEXTEND ON NEXT 160 MAX -2147483648

	BLOCKSIZE 8K

	LOGGING

	EXTENT MANAGEMENT LOCAL AUTOALLOCATE

	SEGMENT SPACE MANAGEMENT AUTO

;


Drop Script

---

--- DROP TABLESPACE: USERS

---

DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILE CASCADE CONSTRAINTS;

Column Domains

Name Datatype Used By
<default>VARCHAR2(20) COUNTRIES.COUNTRY_ID
COUNTRIES.COUNTRY_NAME
DEPARTMENTS.DEPARTMENT_ID
DEPARTMENTS.DEPARTMENT_NAME
EMPLOYEES.EMPLOYEE_ID
EMPLOYEES.FIRST_NAME
EMPLOYEES.LAST_NAME
EMPLOYEES.EMAIL
EMPLOYEES.PHONE_NUMBER
EMPLOYEES.HIRE_DATE
EMPLOYEES.SALARY
EMPLOYEES.COMMISSION_PCT
JOBS.JOB_ID
JOBS.JOB_TITLE
JOBS.MIN_SALARY
JOBS.MAX_SALARY
JOB_HISTORY.START_DATE
JOB_HISTORY.END_DATE
LOCATIONS.LOCATION_ID
LOCATIONS.STREET_ADDRESS
LOCATIONS.POSTAL_CODE
LOCATIONS.CITY
LOCATIONS.STATE_PROVINCE
REGIONS.REGION_ID
REGIONS.REGION_NAME
CUSTOMERS.CUSTOMER_ID
CUSTOMERS.CUST_FIRST_NAME
CUSTOMERS.CUST_LAST_NAME
CUSTOMERS.CUST_ADDRESS
CUSTOMERS.PHONE_NUMBERS
CUSTOMERS.NLS_LANGUAGE
CUSTOMERS.NLS_TERRITORY
CUSTOMERS.CREDIT_LIMIT
CUSTOMERS.CUST_EMAIL
CUSTOMERS.CUST_GEO_LOCATION
CUSTOMERS.DATE_OF_BIRTH
CUSTOMERS.MARITAL_STATUS
CUSTOMERS.GENDER
CUSTOMERS.INCOME_LEVEL
INVENTORIES.QUANTITY_ON_HAND
ORDERS.ORDER_ID
ORDERS.ORDER_DATE
ORDERS.ORDER_MODE
ORDERS.ORDER_STATUS
ORDERS.ORDER_TOTAL
ORDERS.PROMOTION_ID
ORDER_ITEMS.LINE_ITEM_ID
ORDER_ITEMS.UNIT_PRICE
ORDER_ITEMS.QUANTITY
PRODUCT_DESCRIPTIONS.LANGUAGE_ID
PRODUCT_DESCRIPTIONS.TRANSLATED_NAME
PRODUCT_DESCRIPTIONS.TRANSLATED_DESCRIPTION
PRODUCT_INFORMATION.PRODUCT_ID
PRODUCT_INFORMATION.PRODUCT_NAME
PRODUCT_INFORMATION.PRODUCT_DESCRIPTION
PRODUCT_INFORMATION.CATEGORY_ID
PRODUCT_INFORMATION.WEIGHT_CLASS
PRODUCT_INFORMATION.WARRANTY_PERIOD
PRODUCT_INFORMATION.SUPPLIER_ID
PRODUCT_INFORMATION.PRODUCT_STATUS
PRODUCT_INFORMATION.LIST_PRICE
PRODUCT_INFORMATION.MIN_PRICE
PRODUCT_INFORMATION.CATALOG_URL
PROMOTIONS.PROMO_ID
PROMOTIONS.PROMO_NAME
WAREHOUSES.WAREHOUSE_ID
WAREHOUSES.WAREHOUSE_SPEC
WAREHOUSES.WAREHOUSE_NAME
WAREHOUSES.WH_GEO_LOCATION

Diagrams

OEHR.Diagram1


OE.Diagram 1


HR.Diagram 1


IONS.POSTAL_CODE
LOCATIONS.CITY
LOCATIONS.STATE_PROVINCE
REGIONS.REGION_NAME
CATEGORIES_TAB.CATEGORY_NAME
CATEGORIES_TAB.CATEGORY_DESCRIPTION
CUSTOMERS.CUST_FIRST_NAME
CUSTOMERS.CUST_LAST_NAME
CUSTOMERS.NLS_LANGUAGE
CUSTOMERS.NLS_TERRITORY
CUSTOMERS.CUST_EMAIL
CUSTOMERS.MARITAL_STATUS
CUSTOMERS.GENDER
CUSTOMERS.INCOME_LEVEL
ORDERS.ORDER_MODE
PRODUCT_DESCRIPTIONS.LANGUAGE_ID
PRODUCT_INFORMATION.PRODUCT_NAME
PRODUCT_INFORMATION.PRODUCT_DESCRIPTION
PRODUCT_INFORMATION.PRODUCT_STATUS
PRODUCT_INFORMATION.CATALOG_URL
PROMOTIONS.PROMO_NAME
WAREHOUSES.WAREHOUSE_NAME
<default>
VARGRAPHICScaler VARYING ARRAYScaler WAREHOUSE_TYPObject XDB$RAW_LIST_TVarray XMLTYPEObject