---

--- 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

;

---

--- 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)

;

---

--- 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

;

---

--- 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)

;

---

--- 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;

/

---

--- 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)

;

---

--- 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)

;

---

--- 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';

---

--- 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)

;

---

--- 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;

/

---

--- 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

;

---

--- 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)

;

---

--- 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

;

---

--- CREATE TABLE: PROMOTIONS

---

CREATE TABLE PROMOTIONS

(

	PROMO_ID NUMBER(6) NOT NULL,

	PROMO_NAME VARCHAR2(20),

	PRIMARY KEY (PROMO_ID)

)

	TABLESPACE EXAMPLE

;

---

--- 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;

/

---

--- CREATE TABLE: REGIONS

---

CREATE TABLE REGIONS

(

	REGION_ID NUMBER NOT NULL,

	REGION_NAME VARCHAR2(25),

	PRIMARY KEY (REGION_ID)

)

	TABLESPACE EXAMPLE

;

---

--- 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

;