Part 2

set echo on ------------------------------------------------------------------------------------------------------- -- CREATE ADDRESS TABLE - Common for all entities that require address example customer, shipping, etc. ------------------------------------------------------------------------------------------------------- CREATE TABLE addrss ( addrid NUMBER(7) NOT NULL, res_no VARCHAR2(255), street VARCHAR2(300) NOT NULL, city VARCHAR2(100) NOT NULL, state CHAR(2), zip CHAR(10), addrtype CHAR(1) NOT NULL, shipng_shpng_id NUMBER(7) NOT NULL, shipng_order_order_id NUMBER(7) NOT NULL, shipng_order_transctn_tran_id NUMBER(7) NOT NULL, shipng_order_promo_prm_id VARCHAR2(20) NOT NULL, constraint addrss_pk primary key (addrid, shipng_shpng_id, shipng_order_order_id, shipng_order_transctn_tran_id, shipng_order_promo_prm_id) using index storage ( initial 25k next 12k ) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10k pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE CUSTOMER TABLE -------------------------------- CREATE TABLE cust ( cust_id NUMBER(7) NOT NULL, fname VARCHAR2(100) NOT NULL, lname VARCHAR2(100) NOT NULL, constraint cust_pk primary key (cust_id) using index storage ( initial 25k next 12K ) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE EMPLOYEE TABLE -------------------------------- CREATE TABLE emply ( empid NUMBER(7) NOT NULL, fname VARCHAR2(100) NOT NULL, lname VARCHAR2(100) NOT NULL, dept VARCHAR2(100) NOT NULL, desgn VARCHAR2(100) NOT NULL, addrss_addrid NUMBER(7) NOT NULL, addrss_shpng_id NUMBER(7) NOT NULL, addrss_order_id NUMBER(7) NOT NULL, addrss_tran_id NUMBER(7) NOT NULL, addrss_prm_id NUMBER(7) NOT NULL, whrhse_whr_id NUMBER(7) NOT NULL, whrhse_invt_id NUMBER(7) NOT NULL, whrhse_sup_id VARCHAR2(10) NOT NULL, constraint emply_pk primary key (empid, addrss_addrid, addrss_shpng_id, addrss_order_id, addrss_tran_id, addrss_prm_id, whrhse_whr_id,whrhse_invt_id, whrhse_sup_id) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE INVT TABLE -------------------------------- CREATE TABLE invt ( invt_id NUMBER(7) NOT NULL, invt_desc VARCHAR2(300) NOT NULL, "SIZE" VARCHAR2(50) NOT NULL, inv_date VARCHAR2(10) NOT NULL, in_stock NUMBER(7) NOT NULL, cost VARCHAR2(50) NOT NULL, suplr_sup_id VARCHAR2(50) NOT NULL, constraint invt_pk primary key (invt_id, suplr_sup_id) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE ORDER TABLE -------------------------------- CREATE TABLE "ORDER" ( order_id NUMBER(7) NOT NULL, ord_desc VARCHAR2(300) NOT NULL, transctn_tran_id NUMBER(7) NOT NULL, promo_prm_id VARCHAR2(10) NOT NULL, constraint order_pk primary key (order_id, transctn_tran_id, promo_prm_id ) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE PROMOTIONS TABLE -------------------------------- CREATE TABLE promo ( prm_id NUMBER(7) NOT NULL, prm_desc VARCHAR2(200), prm_amt varchar2(20) NOT NULL, constraint promo_pk primary key (prm_id) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE SHIPPING TABLE -------------------------------- CREATE TABLE shipng ( shpng_id NUMBER(7) NOT NULL, shng_desc VARCHAR2(100), shpng_status CHAR(1) NOT NULL, order_order_id NUMBER(7) NOT NULL, order_transctn_tran_id NUMBER(7) NOT NULL, order_promo_prm_id VARCHAR2(10) NOT NULL, constraint shipng_pk primary key (shpng_id, order_order_id, order_transctn_tran_id, order_promo_prm_id ) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE SHOPPING CART TABLE -------------------------------- CREATE TABLE shp_crt ( shp_id NUMBER(7) NOT NULL, cost NUMBER(10,2) NOT NULL, cust_cust_id NUMBER(7) NOT NULL, invt_invt_id NUMBER(7) NOT NULL, invt_sup_id NUMBER(7) NOT NULL, transctn_tran_id varchar2(10) NOT NULL, constraint shp_crt_pk primary key (shp_id, cust_cust_id, invt_invt_id, invt_sup_id, transctn_tran_id) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE SUPPLIER TABLE -------------------------------- CREATE TABLE suplr ( sup_id NUMBER(7) NOT NULL, sup_name VARCHAR2(300) NOT NULL, addrid VARCHAR2(10), constraint suplr_pk primary key (sup_id ) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE TRANSACTION TABLE -------------------------------- CREATE TABLE transctn ( tran_id NUMBER(7) NOT NULL, card_num VARCHAR2(50), constraint transctn_pk primary key (tran_id ) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1; -------------------------------- -- CREATE WAREHOUSE TABLE -------------------------------- CREATE TABLE whrhse ( whr_id NUMBER(7) NOT NULL, whr_nm VARCHAR2(100) NOT NULL, invt_invt_id NUMBER(7) NOT NULL, invt_sup_id VARCHAR2(10) NOT NULL, constraint whrhse_pk primary key (whr_id, invt_invt_id,invt_sup_id ) using index storage ( initial 25k next 12K) tablespace USER_INDEX ) pctfree 0 pctused 90 storage ( initial 50k next 10K pctincrease 0 maxextents 5 ) tablespace USER_DATA1;