| drop table LINEITEM; |
| drop table ORDERS; |
| drop table VENDOR_PART; |
| drop table VENDOR; |
| drop table PART_DETAIL; |
| drop table PART; |
| |
| create table PART ( |
| PART_NUMBER VARCHAR(15) NOT NULL, |
| REVISION NUMERIC(2) NOT NULL, |
| DESCRIPTION VARCHAR(255), |
| REVISION_DATE TIMESTAMP NOT NULL, |
| BOM_PART_NUMBER VARCHAR(15), |
| BOM_REVISION NUMERIC(2), |
| PRIMARY KEY (PART_NUMBER, REVISION) |
| ); |
| |
| alter table PART |
| add CONSTRAINT FK_1 FOREIGN KEY (BOM_PART_NUMBER, BOM_REVISION) |
| REFERENCES PART (PART_NUMBER, REVISION); |
| |
| create table PART_DETAIL ( |
| PART_NUMBER VARCHAR(15) NOT NULL, |
| REVISION NUMERIC(2) NOT NULL, |
| SPECIFICATION CLOB(10K), |
| DRAWING BLOB(10K), |
| PRIMARY KEY (PART_NUMBER, REVISION) |
| ); |
| |
| create table VENDOR ( |
| VENDOR_ID INTEGER NOT NULL PRIMARY KEY, |
| NAME VARCHAR(30) NOT NULL, |
| ADDRESS VARCHAR(255) NOT NULL, |
| CONTACT VARCHAR(255) NOT NULL, |
| PHONE VARCHAR(30) NOT NULL |
| ); |
| |
| create table VENDOR_PART ( |
| VENDOR_PART_NUMBER BIGINT NOT NULL PRIMARY KEY, |
| DESCRIPTION VARCHAR(255), |
| PRICE DOUBLE PRECISION NOT NULL, |
| VENDOR_ID INTEGER NOT NULL, |
| PART_NUMBER VARCHAR(15) NOT NULL, |
| PART_REVISION NUMERIC(2) NOT NULL, |
| CONSTRAINT FK_2 FOREIGN KEY (VENDOR_ID) REFERENCES VENDOR (VENDOR_ID), |
| CONSTRAINT FK_3 FOREIGN KEY (PART_NUMBER, PART_REVISION) REFERENCES PART (PART_NUMBER, REVISION), |
| UNIQUE (PART_NUMBER, PART_REVISION) |
| ); |
| |
| create table ORDERS ( |
| ORDER_ID INTEGER NOT NULL PRIMARY KEY, |
| STATUS CHAR(1) NOT NULL, |
| LAST_UPDATE TIMESTAMP NOT NULL, |
| DISCOUNT NUMERIC(2) NOT NULL, |
| SHIPMENT_INFO VARCHAR(255) |
| ); |
| |
| create table LINEITEM ( |
| ORDER_ID INTEGER NOT NULL, |
| ITEM_ID NUMERIC(3) NOT NULL, |
| QUANTITY NUMERIC(3) NOT NULL, |
| VENDOR_PART_NUMBER BIGINT NOT NULL, |
| CONSTRAINT FK_4 FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID), |
| CONSTRAINT FK_5 FOREIGN KEY (VENDOR_PART_NUMBER) REFERENCES VENDOR_PART (VENDOR_PART_NUMBER), |
| PRIMARY KEY (ORDER_ID, ITEM_ID) |
| ); |
| |