-- ------------------------------------------------------------- -- Automatisch von ER2SQL 3.38 erzeugter File. -- Funktion: SQL-Code fuer Oracle 7/8 -- Erzeugungsdatum : Sat Jan 31 23:34:54 2004 -- -- ------------------------------------------------------------- -- Copyright (C) 1995-2004 by Joerg Hoppe, Goettingen, Germany. -- email: j_hoppe@t-online.de -- ------------------------------------------------------------- CREATE TABLE LOCATION (LOCATION_ID NUMBER(3) CONSTRAINT pk_LOCATION_1 PRIMARY KEY ) ; ALTER TABLE LOCATION ADD ( REGIONAL_GROUP VARCHAR2(20) ) ; CREATE SEQUENCE seq_LOCATION_ID ; CREATE TABLE ADDRESS (ADDRESS_ID NUMBER(6) CONSTRAINT pk_ADDRESS_1 PRIMARY KEY ) ; ALTER TABLE ADDRESS ADD ( STREET VARCHAR2(40) ) ; ALTER TABLE ADDRESS ADD ( CITY VARCHAR2(30) ) ; ALTER TABLE ADDRESS ADD ( STATE VARCHAR2(2) ) ; ALTER TABLE ADDRESS ADD ( ZIP_CODE VARCHAR2(9) ) ; CREATE SEQUENCE seq_ADDRESS_ID ; CREATE TABLE DEPARTMENT (DEPARTMENT_ID NUMBER(2) CONSTRAINT pk_DEPARTMENT_1 PRIMARY KEY ) ; ALTER TABLE DEPARTMENT ADD ( NAME VARCHAR2(14) ) ; ALTER TABLE DEPARTMENT ADD ( LOCATION_ID NUMBER(3) CONSTRAINT nn_DEPARTMENT_2 NOT NULL ) ; ALTER TABLE DEPARTMENT ADD ( ADDRESS_ID NUMBER(6) CONSTRAINT nn_DEPARTMENT_3 NOT NULL ) ; CREATE SEQUENCE seq_DEPARTMENT_ID ; CREATE TABLE JOB (JOB_ID NUMBER(3) CONSTRAINT pk_JOB_1 PRIMARY KEY ) ; ALTER TABLE JOB ADD ( FUNCTION VARCHAR2(30) ) ; CREATE SEQUENCE seq_JOB_ID ; CREATE TABLE EMPLOYEE (EMPLOYEE_ID NUMBER(4) CONSTRAINT pk_EMPLOYEE_1 PRIMARY KEY ) ; ALTER TABLE EMPLOYEE ADD ( LAST_NAME VARCHAR2(15) ) ; ALTER TABLE EMPLOYEE ADD ( FIRST_NAME VARCHAR2(15) ) ; ALTER TABLE EMPLOYEE ADD ( MIDDLE_INITIAL VARCHAR2(1) ) ; ALTER TABLE EMPLOYEE ADD ( JOB_ID NUMBER(3) CONSTRAINT nn_EMPLOYEE_2 NOT NULL ) ; ALTER TABLE EMPLOYEE ADD ( MANAGER_ID NUMBER(4) CONSTRAINT nn_EMPLOYEE_3 NOT NULL ) ; ALTER TABLE EMPLOYEE ADD ( HIRE_DATE DATE ) ; ALTER TABLE EMPLOYEE ADD ( SALARY NUMBER(7,2) ) ; ALTER TABLE EMPLOYEE ADD ( COMMISSION NUMBER(7,2) ) ; ALTER TABLE EMPLOYEE ADD ( DEPARTMENT_ID NUMBER(2) CONSTRAINT nn_EMPLOYEE_4 NOT NULL ) ; CREATE SEQUENCE seq_EMPLOYEE_ID ; CREATE TABLE SALARY_GRADE (GRADE_ID NUMBER(3) CONSTRAINT pk_SALARY_GRADE_1 PRIMARY KEY ) ; ALTER TABLE SALARY_GRADE ADD ( LOWER_BOUND NUMBER(7,2) ) ; ALTER TABLE SALARY_GRADE ADD ( UPPER_BOUND NUMBER(7,2) ) ; CREATE SEQUENCE seq_GRADE_ID ; CREATE TABLE PRODUCT (PRODUCT_ID NUMBER(6) CONSTRAINT pk_PRODUCT_1 PRIMARY KEY ) ; ALTER TABLE PRODUCT ADD ( DESCRIPTION VARCHAR2(30) ) ; CREATE SEQUENCE seq_PRODUCT_ID ; CREATE TABLE PRICE (price_id NUMBER(6) CONSTRAINT pk_PRICE_1 PRIMARY KEY ) ; ALTER TABLE PRICE ADD ( PRODUCT_ID NUMBER(6) CONSTRAINT nn_PRICE_2 NOT NULL ) ; ALTER TABLE PRICE ADD ( LIST_PRICE NUMBER(8,2) ) ; ALTER TABLE PRICE ADD ( MIN_PRICE NUMBER(8,2) ) ; ALTER TABLE PRICE ADD ( START_DATE DATE ) ; ALTER TABLE PRICE ADD ( END_DATE DATE ) ; CREATE SEQUENCE seq_price_id ; CREATE TABLE CUSTOMER (CUSTOMER_ID NUMBER(6) CONSTRAINT pk_CUSTOMER_1 PRIMARY KEY ) ; ALTER TABLE CUSTOMER ADD ( NAME VARCHAR2(45) ) ; ALTER TABLE CUSTOMER ADD ( ADDRESS_ID NUMBER(6) CONSTRAINT nn_CUSTOMER_2 NOT NULL ) ; ALTER TABLE CUSTOMER ADD ( AREA_CODE NUMBER(3) ) ; ALTER TABLE CUSTOMER ADD ( PHONE_NUMBER NUMBER(7) ) ; ALTER TABLE CUSTOMER ADD ( SALESPERSON_ID NUMBER(4) CONSTRAINT nn_CUSTOMER_3 NOT NULL ) ; ALTER TABLE CUSTOMER ADD ( CREDIT_LIMIT NUMBER(9,2) ) ; ALTER TABLE CUSTOMER ADD ( COMMENTS VARCHAR2(255) ) ; CREATE SEQUENCE seq_CUSTOMER_ID ; CREATE TABLE SALES_ORDER (ORDER_ID NUMBER(4) CONSTRAINT pk_SALES_ORDER_1 PRIMARY KEY ) ; ALTER TABLE SALES_ORDER ADD ( ORDER_DATE DATE ) ; ALTER TABLE SALES_ORDER ADD ( CUSTOMER_ID NUMBER(6) CONSTRAINT nn_SALES_ORDER_2 NOT NULL ) ; ALTER TABLE SALES_ORDER ADD ( SHIP_DATE DATE ) ; ALTER TABLE SALES_ORDER ADD ( TOTAL NUMBER(8,2) ) ; CREATE SEQUENCE seq_ORDER_ID ; CREATE TABLE ITEM (I_ID NUMBER(6) CONSTRAINT pk_ITEM_1 PRIMARY KEY ) ; ALTER TABLE ITEM ADD ( ORDER_ID NUMBER(4) CONSTRAINT nn_ITEM_2 NOT NULL ) ; ALTER TABLE ITEM ADD ( ITEM_ID NUMBER(4) ) ; ALTER TABLE ITEM ADD ( PRODUCT_ID NUMBER(6) CONSTRAINT nn_ITEM_3 NOT NULL ) ; ALTER TABLE ITEM ADD ( ACTUAL_PRICE NUMBER(8,2) ) ; ALTER TABLE ITEM ADD ( QUANTITY NUMBER(8) ) ; ALTER TABLE ITEM ADD ( TOTAL NUMBER(8,2) ) ; CREATE SEQUENCE seq_I_ID ; CREATE TABLE VTDEBUGOUT ( TIMESTAMP DATE, VT_NAME VARCHAR2(40), PROCNAME VARCHAR2(12), EVENT VARCHAR2(80), INFO VARCHAR2(512) ) ; /* Indexe in Table 'LOCATION' */ CREATE UNIQUE INDEX iuq_LOCATION_1 ON LOCATION(REGIONAL_GROUP) ; /* Indexe in Table 'DEPARTMENT' */ CREATE INDEX ifk_DEPARTMENT_LOCATION_ID ON DEPARTMENT(LOCATION_ID) ; /* Indexe in Table 'DEPARTMENT' */ CREATE INDEX ifk_DEPARTMENT_ADDRESS_ID ON DEPARTMENT(ADDRESS_ID) ; /* Indexe in Table 'DEPARTMENT' */ CREATE UNIQUE INDEX iuq_DEPARTMENT_1 ON DEPARTMENT(NAME,LOCATION_ID) ; /* Indexe in Table 'JOB' */ CREATE UNIQUE INDEX iuq_JOB_1 ON JOB(FUNCTION) ; /* Indexe in Table 'EMPLOYEE' */ CREATE INDEX ifk_EMPLOYEE_JOB_ID ON EMPLOYEE(JOB_ID) ; /* Indexe in Table 'EMPLOYEE' */ CREATE INDEX ifk_EMPLOYEE_MANAGER_ID ON EMPLOYEE(MANAGER_ID) ; /* Indexe in Table 'EMPLOYEE' */ CREATE INDEX ifk_EMPLOYEE_DEPARTMENT_ID ON EMPLOYEE(DEPARTMENT_ID) ; /* Indexe in Table 'EMPLOYEE' */ CREATE UNIQUE INDEX iuq_EMPLOYEE_1 ON EMPLOYEE(LAST_NAME,FIRST_NAME,MIDDLE_INITIAL) ; /* Indexe in Table 'PRICE' */ CREATE INDEX ifk_PRICE_PRODUCT_ID ON PRICE(PRODUCT_ID) ; /* Indexe in Table 'CUSTOMER' */ CREATE INDEX ifk_CUSTOMER_ADDRESS_ID ON CUSTOMER(ADDRESS_ID) ; /* Indexe in Table 'CUSTOMER' */ CREATE INDEX ifk_CUSTOMER_SALESPERSON_ID ON CUSTOMER(SALESPERSON_ID) ; /* Indexe in Table 'CUSTOMER' */ CREATE UNIQUE INDEX iuq_CUSTOMER_1 ON CUSTOMER(NAME) ; /* Indexe in Table 'SALES_ORDER' */ CREATE INDEX ifk_SALES_ORDER_CUSTOMER_ID ON SALES_ORDER(CUSTOMER_ID) ; /* Indexe in Table 'SALES_ORDER' */ CREATE UNIQUE INDEX iuq_SALES_ORDER_1 ON SALES_ORDER(ORDER_DATE,CUSTOMER_ID) ; /* Indexe in Table 'ITEM' */ CREATE INDEX ifk_ITEM_ORDER_ID ON ITEM(ORDER_ID) ; /* Indexe in Table 'ITEM' */ CREATE INDEX ifk_ITEM_PRODUCT_ID ON ITEM(PRODUCT_ID) ; /* Indexe in Table 'ITEM' */ CREATE UNIQUE INDEX iuq_ITEM_1 ON ITEM(ORDER_ID,ITEM_ID,PRODUCT_ID) ; ALTER TABLE LOCATION ADD( CONSTRAINT uq_LOCATION_11 UNIQUE(REGIONAL_GROUP)) ; /* Constraints fuer UNIQUE und FOREIGNKEYs in Table 'DEPARTMENT' */ ALTER TABLE DEPARTMENT ADD( CONSTRAINT fk_DEPARTMENT_11 FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION(LOCATION_ID) DEFERRABLE ) ; ALTER TABLE DEPARTMENT ADD( CONSTRAINT fk_DEPARTMENT_12 FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS(ADDRESS_ID) DEFERRABLE ) ; ALTER TABLE DEPARTMENT ADD( CONSTRAINT uq_DEPARTMENT_13 UNIQUE(NAME,LOCATION_ID)) ; ALTER TABLE JOB ADD( CONSTRAINT uq_JOB_11 UNIQUE(FUNCTION)) ; /* Constraints fuer UNIQUE und FOREIGNKEYs in Table 'EMPLOYEE' */ ALTER TABLE EMPLOYEE ADD( CONSTRAINT fk_EMPLOYEE_11 FOREIGN KEY (JOB_ID) REFERENCES JOB(JOB_ID) DEFERRABLE ) ; ALTER TABLE EMPLOYEE ADD( CONSTRAINT fk_EMPLOYEE_12 FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID) DEFERRABLE ) ; ALTER TABLE EMPLOYEE ADD( CONSTRAINT fk_EMPLOYEE_13 FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID) DEFERRABLE ) ; ALTER TABLE EMPLOYEE ADD( CONSTRAINT uq_EMPLOYEE_14 UNIQUE(LAST_NAME,FIRST_NAME,MIDDLE_INITIAL)) ; /* Constraints fuer UNIQUE und FOREIGNKEYs in Table 'PRICE' */ ALTER TABLE PRICE ADD( CONSTRAINT fk_PRICE_11 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID) DEFERRABLE ) ; /* Constraints fuer UNIQUE und FOREIGNKEYs in Table 'CUSTOMER' */ ALTER TABLE CUSTOMER ADD( CONSTRAINT fk_CUSTOMER_11 FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS(ADDRESS_ID) DEFERRABLE ) ; ALTER TABLE CUSTOMER ADD( CONSTRAINT fk_CUSTOMER_12 FOREIGN KEY (SALESPERSON_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID) DEFERRABLE ) ; ALTER TABLE CUSTOMER ADD( CONSTRAINT uq_CUSTOMER_13 UNIQUE(NAME)) ; /* Constraints fuer UNIQUE und FOREIGNKEYs in Table 'SALES_ORDER' */ ALTER TABLE SALES_ORDER ADD( CONSTRAINT fk_SALES_ORDER_11 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID) DEFERRABLE ) ; ALTER TABLE SALES_ORDER ADD( CONSTRAINT uq_SALES_ORDER_12 UNIQUE(ORDER_DATE,CUSTOMER_ID)) ; /* Constraints fuer UNIQUE und FOREIGNKEYs in Table 'ITEM' */ ALTER TABLE ITEM ADD( CONSTRAINT fk_ITEM_11 FOREIGN KEY (ORDER_ID) REFERENCES SALES_ORDER(ORDER_ID) DEFERRABLE ) ; ALTER TABLE ITEM ADD( CONSTRAINT fk_ITEM_12 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID) DEFERRABLE ) ; ALTER TABLE ITEM ADD( CONSTRAINT uq_ITEM_13 UNIQUE(ORDER_ID,ITEM_ID,PRODUCT_ID)) ;