SPOOL cr_db.log

PROMPT Dropping Tables

DROP TABLE ORDER_ITEMS
/
DROP TABLE ITEMS
/
DROP TABLE TRACKING
/
DROP TABLE ORDERS
/
DROP TABLE CUST
/
DROP TABLE COUNTRIES
/

PROMPT Dropping Sequences

DROP SEQUENCE SEQ_CUST
/
DROP SEQUENCE SEQ_ITEMS
/
DROP SEQUENCE SEQ_ORDERS
/
DROP SEQUENCE SEQ_TRK
/


PROMPT Creating Table 'COUNTRIES'

CREATE TABLE COUNTRIES ( ID       NUMBER(2)     NOT NULL, 
                         COUNTRY  VARCHAR2(30)  NOT NULL, 
                         PRIMARY KEY ( ID ))
/

PROMPT Creating Table 'CUST'

CREATE TABLE CUST ( ID          NUMBER(5)     DEFAULT NULL NOT NULL, 
                    FIRSTNAME   VARCHAR2(30)  DEFAULT NULL, 
                    LASTNAME    VARCHAR2(30)  DEFAULT NULL, 
                    ADDRESS1    VARCHAR2(30)  DEFAULT NULL, 
                    ADDRESS2    VARCHAR2(30)  DEFAULT NULL, 
                    TOWN        VARCHAR2(30)  DEFAULT NULL, 
                    REGION      VARCHAR2(30)  DEFAULT NULL, 
                    COUNTRY_ID  NUMBER(2)     DEFAULT NULL, 
                    ZIP         VARCHAR2(10)  DEFAULT NULL, 
                    HOUSENAME   VARCHAR2(30)  DEFAULT NULL, 
                    PRIMARY KEY ( ID ))
/

PROMPT Creating Table 'ITEMS'

CREATE TABLE ITEMS ( ID    NUMBER(5)     NOT NULL, 
                     TEXT  VARCHAR2(30)  NOT NULL, 
                     COST  NUMBER(5,2)   NOT NULL, 
                     PRIMARY KEY ( ID ))
/

PROMPT Creating Table 'ORDERS'

CREATE TABLE ORDERS ( ID           NUMBER(5)     NOT NULL, 
                      CUSTOMER_ID  NUMBER(5)     NOT NULL, 
                      PRIMARY KEY ( ID ))
/

PROMPT Creating Table 'ORDER_ITEMS'

CREATE TABLE ORDER_ITEMS ( ORDER_ID  NUMBER(5), 
                           ITEM_ID   NUMBER(5)     NOT NULL)
/


PROMPT Creating Table 'TRACKING'

CREATE TABLE TRACKING ( ID        NUMBER(5)     NOT NULL, 
                        ORDER_ID  NUMBER(5)     NOT NULL, 
                        NOTES     VARCHAR2(130)  NOT NULL, 
                        PRIMARY KEY ( ID ))
/


PROMPT Altering Table 'CUST'

ALTER TABLE CUST ADD  CONSTRAINT FK_COUNTRIES
FOREIGN KEY (COUNTRY_ID) 
REFERENCES COUNTRIES (ID)
/

PROMPT Altering Table 'ORDERS'

ALTER TABLE ORDERS ADD  CONSTRAINT CUST_FK
FOREIGN KEY (CUSTOMER_ID) 
REFERENCES CUST (ID) 
/

PROMPT Altering Table 'ORDER_ITEMS'

ALTER TABLE ORDER_ITEMS ADD  CONSTRAINT FK_ORD_ITEM
FOREIGN KEY (ITEM_ID) 
REFERENCES ITEMS (ID)
/

ALTER TABLE ORDER_ITEMS ADD  CONSTRAINT FK_ORD_ITEM1
FOREIGN KEY (ORDER_ID) 
REFERENCES ORDERS (ID)
/

PROMPT Altering Table 'TRACKING'

ALTER TABLE TRACKING ADD  CONSTRAINT KF_ORD_TRK
FOREIGN KEY (ORDER_ID) 
REFERENCES ORDERS (ID)
/

PROMPT Creating Sequence 'SEQ_CUST'

CREATE SEQUENCE SEQ_CUST START WITH 100
/

PROMPT Creating Sequence 'SEQ_ITEMS' 

CREATE SEQUENCE SEQ_ITEMS START WITH 100
/

PROMPT Creating Sequence 'SEQ_ORDERS'

CREATE SEQUENCE SEQ_ORDERS START WITH 100
/

PROMPT Creating Sequence 'SEQ_TRK'

CREATE SEQUENCE SEQ_TRK START WITH 100
/


spool off

