Northwind.Oracle.sql

-- Script was generated by Devart Entity Developer, Version 3.50.116.0
-- Script date 03.05.2011 23:38:39
-- Target Server: Oracle
-- Server Version: 11gR2

-- 
-- Creating a table SUPPLIERS 
-- 
CREATE TABLE SUPPLIERS (
   SUPPLIER_ID NUMBER(9) NOT NULL,
   COMPANY_NAME NVARCHAR2(40) NOT NULL,
   CONTACT_NAME NVARCHAR2(30) NULL,
   CONTACT_TITLE NVARCHAR2(30) NULL,
   ADDRESS NVARCHAR2(60) NULL,
   CITY NVARCHAR2(15) NULL,
   REGION NVARCHAR2(15) NULL,
   POSTAL_CODE NVARCHAR2(10) NULL,
   COUNTRY NVARCHAR2(15) NULL,
   PHONE NVARCHAR2(24) NULL,
   FAX NVARCHAR2(24) NULL,
   HOME_PAGE NCLOB NULL,
   CONSTRAINT PK_SUPPLIERS PRIMARY KEY (SUPPLIER_ID)
);

-- 
-- Creating a table EMPLOYEES 
-- 
CREATE TABLE EMPLOYEES (
   EMPLOYEE_ID NUMBER(9) NOT NULL,
   LAST_NAME NVARCHAR2(20) NOT NULL,
   FIRST_NAME NVARCHAR2(10) NOT NULL,
   TITLE NVARCHAR2(30) NULL,
   TITLE_OF_COURTESY NVARCHAR2(25) NULL,
   BIRTH_DATE DATE NULL,
   HIRE_DATE DATE NULL,
   ADDRESS NVARCHAR2(60) NULL,
   CITY NVARCHAR2(15) NULL,
   REGION NVARCHAR2(15) NULL,
   POSTAL_CODE NVARCHAR2(10) NULL,
   COUNTRY NVARCHAR2(15) NULL,
   HOME_PHONE NVARCHAR2(24) NULL,
   EXTENSION NVARCHAR2(4) NULL,
   PHOTO BLOB NULL,
   NOTES NCLOB NULL,
   REPORTS_TO NUMBER(9) NULL,
   PHOTO_PATH NVARCHAR2(255) NULL,
   CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID),
   CONSTRAINT FK_EMPLOYEES_EMPLOYEES_0 FOREIGN KEY (REPORTS_TO) REFERENCES EMPLOYEES (EMPLOYEE_ID)
);

-- 
-- Creating a table REGION 
-- 
CREATE TABLE REGION (
   REGION_ID NUMBER(9) NOT NULL,
   REGION_DESCRIPTION NCHAR(50) NOT NULL,
   CONSTRAINT PK_REGION PRIMARY KEY (REGION_ID)
);

-- 
-- Creating a table SHIPPERS 
-- 
CREATE TABLE SHIPPERS (
   SHIPPER_ID NUMBER(9) NOT NULL,
   COMPANY_NAME NVARCHAR2(40) NULL,
   PHONE NVARCHAR2(24) NULL,
   CONSTRAINT PK_SHIPPERS PRIMARY KEY (SHIPPER_ID)
);

-- 
-- Creating a table CUSTOMER_DEMOGRAPHICS 
-- 
CREATE TABLE CUSTOMER_DEMOGRAPHICS (
   CUSTOMER_TYPE_ID NCHAR(10) NOT NULL,
   CUSTOMER_DESC NCLOB NULL,
   CONSTRAINT PK_CUSTOMER_DEMOGRAPHICS PRIMARY KEY (CUSTOMER_TYPE_ID)
);

-- 
-- Creating a table CATEGORIES 
-- 
CREATE TABLE CATEGORIES (
   CATEGORY_ID NUMBER(9) NOT NULL,
   CATEGORY_NAME NVARCHAR2(15) NOT NULL,
   DESCRIPTION NCLOB NULL,
   PICTURE BLOB NULL,
   CONSTRAINT PK_CATEGORIES PRIMARY KEY (CATEGORY_ID)
);

-- 
-- Creating a table CUSTOMERS 
-- 
CREATE TABLE CUSTOMERS (
   CUSTOMER_ID NCHAR(5) NOT NULL,
   COMPANY_NAME NVARCHAR2(40) NOT NULL,
   CONTACT_NAME NVARCHAR2(30) NULL,
   CONTACT_TITLE NVARCHAR2(30) NULL,
   ADDRESS NVARCHAR2(60) NULL,
   CITY NVARCHAR2(15) NULL,
   REGION NVARCHAR2(15) NULL,
   POSTAL_CODE NVARCHAR2(10) NULL,
   COUNTRY NVARCHAR2(15) NULL,
   PHONE NVARCHAR2(24) NULL,
   FAX NVARCHAR2(24) NULL,
   CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTOMER_ID)
);

-- 
-- Creating a table TERRITORIES 
-- 
CREATE TABLE TERRITORIES (
   TERRITORY_ID NVARCHAR2(20) NOT NULL,
   TERRITORY_DESCRIPTION NCHAR(50) NOT NULL,
   REGION_ID NUMBER(9) NOT NULL,
   CONSTRAINT PK_TERRITORIES PRIMARY KEY (TERRITORY_ID),
   CONSTRAINT FK_TERRITORIES_REGION_0 FOREIGN KEY (REGION_ID) REFERENCES REGION (REGION_ID)
);

-- 
-- Creating a table PRODUCTS 
-- 
CREATE TABLE PRODUCTS (
   PRODUCT_ID NUMBER(9) NOT NULL,
   PRODUCT_NAME NVARCHAR2(40) NOT NULL,
   SUPPLIER_ID NUMBER(9) NULL,
   CATEGORY_ID NUMBER(9) NULL,
   QUANTITY_PER_UNIT NVARCHAR2(20) NULL,
   UNIT_PRICE NUMBER(19,4) NULL,
   UNITS_IN_STOCK NUMBER(9) NULL,
   UNITS_ON_ORDER NUMBER(9) NULL,
   REORDER_LEVEL NUMBER(9) NULL,
   DISCONTINUED NUMBER(1) NOT NULL,
   CONSTRAINT PK_PRODUCTS PRIMARY KEY (PRODUCT_ID),
   CONSTRAINT FK_PRODUCTS_CATEGORIES_0 FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORIES (CATEGORY_ID),
   CONSTRAINT FK_PRODUCTS_SUPPLIERS_1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID)
);

-- 
-- Creating a table CUSTOMER_CUSTOMER_DEMO 
-- 
CREATE TABLE CUSTOMER_CUSTOMER_DEMO (
   CUSTOMER_ID NCHAR(5) NOT NULL,
   CUSTOMER_TYPE_ID NCHAR(10) NOT NULL,
   CONSTRAINT PK_CUSTOMER_CUSTOMER_DEMO PRIMARY KEY (CUSTOMER_ID, CUSTOMER_TYPE_ID),
   CONSTRAINT FK_CUSTO_CUSTO_00319171188_000 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID),
   CONSTRAINT FK_CUSTO_CUSTO_03503712221_001 FOREIGN KEY (CUSTOMER_TYPE_ID) REFERENCES CUSTOMER_DEMOGRAPHICS (CUSTOMER_TYPE_ID)
);

-- 
-- Creating a table ORDERS 
-- 
CREATE TABLE ORDERS (
   ORDER_ID NUMBER(9) NOT NULL,
   CUSTOMER_ID NCHAR(5) NULL,
   EMPLOYEE_ID NUMBER(9) NULL,
   ORDER_DATE DATE NULL,
   REQUIRED_DATE DATE NULL,
   SHIPPED_DATE DATE NULL,
   SHIP_VIA NUMBER(9) NULL,
   FREIGHT NUMBER(19,4) NULL,
   SHIP_NAME NVARCHAR2(40) NULL,
   SHIP_ADDRESS NVARCHAR2(60) NULL,
   SHIP_CITY NVARCHAR2(15) NULL,
   SHIP_REGION NVARCHAR2(15) NULL,
   SHIP_POSTAL_CODE NVARCHAR2(10) NULL,
   SHIP_COUNTRY NVARCHAR2(15) NULL,
   CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
   CONSTRAINT FK_ORDERS_CUSTOMERS_0 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID),
   CONSTRAINT FK_ORDERS_EMPLOYEES_1 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID),
   CONSTRAINT FK_ORDERS_SHIPPERS_2 FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPERS (SHIPPER_ID)
);

-- 
-- Creating a table EMPLOYEE_TERRITORIES 
-- 
CREATE TABLE EMPLOYEE_TERRITORIES (
   EMPLOYEE_ID NUMBER(9) NOT NULL,
   TERRITORY_ID NVARCHAR2(20) NOT NULL,
   CONSTRAINT PK_EMPLOYEE_TERRITORIES PRIMARY KEY (EMPLOYEE_ID, TERRITORY_ID),
   CONSTRAINT FK_EMPLO_EMPLO_03011181819_000 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID),
   CONSTRAINT FK_EMPLO_TERRI_03215900055_001 FOREIGN KEY (TERRITORY_ID) REFERENCES TERRITORIES (TERRITORY_ID)
);

-- 
-- Creating a table ORDER_DETAILS 
-- 
CREATE TABLE ORDER_DETAILS (
   ORDER_ID NUMBER(9) NOT NULL,
   PRODUCT_ID NUMBER(9) NOT NULL,
   UNIT_PRICE NUMBER(19,4) NOT NULL,
   QUANTITY NUMBER(9) NOT NULL,
   DISCOUNT FLOAT(126) NOT NULL,
   CONSTRAINT PK_ORDER_DETAILS PRIMARY KEY (ORDER_ID, PRODUCT_ID),
   CONSTRAINT FK_ORDER_DETAILS_ORDERS_0 FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
   CONSTRAINT FK_ORDER_DETAILS_PRODUCTS_1 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS (PRODUCT_ID)
);