SQL Scripts to Create ACME Database Objects

-- 
-- TABLE: ADDRESS 
--
   
CREATE TABLE ADDRESS(
    ADDR_ID_N           INTEGER          NOT NULL,
    ADDR_CUSTID_FN      INTEGER,
    ADDR_SALESMANID_FN  INTEGER, 
    ADDR_ADDRESS_S      VARCHAR(60),
    ADDR_TYPE_S         VARCHAR(8),
    ADDR_CITY_S         VARCHAR(18)      NOT NULL,
    ADDR_STATE_S        CHAR(2),
    ADDR_ZIP_S          VARCHAR(10)      NOT NULL,
    ADDR_COUNTRY_S      CHAR(3),
    CONSTRAINT CHK_ADDR_TYPE 
        CHECK (ADDR_TYPE_S IN ('BILLING', 'SHIPPING')),
        CONSTRAINT PK_ADDRPRIMARY PRIMARY KEY (ADDR_ID_N)
) 
;
   
-- 
-- TABLE: CUSTOMER 
--
   
CREATE TABLE CUSTOMER(
    CUST_ID_N             INTEGER          NOT NULL,
    CUST_PAYTERMSID_FN    INTEGER,
    CUST_SALESMANID_FN    INTEGER,
    CUST_STATUS_S         VARCHAR(1)       DEFAULT 'Y' NOT NULL,
    CUST_NAME_S           VARCHAR(50)      NOT NULL,
    CUST_ALIAS_S          VARCHAR(15),
    CUST_CREDHOLD_S       VARCHAR(1)       DEFAULT 'Y' NOT NULL,
    CONSTRAINT CHK_CUST_STATUS CHECK (CUST_STATUS_S IN ('N', 'Y')),
    CONSTRAINT CHK_CUST_CREDHOLD CHECK (CUST_CREDHOLD_S IN ('N', 'Y')),
    CONSTRAINT PK_CUSTPRIMARY PRIMARY KEY (CUST_ID_N)
) 
;
   
-- 
-- TABLE: ORDER_HEADER 
--
   
CREATE TABLE ORDER_HEADER(
    ORDHDR_ID_N                INTEGER          NOT NULL,
    ORDHDR_PAYTERMS_FN         INTEGER,
    ORDHDR_STATUSID_FN         INTEGER,
    ORDHDR_CUSTID_FN           INTEGER,
    ORDHDR_SALESMANID_FN       INTEGER,
    ORDHDR_NBR_S               VARCHAR(30)      NOT NULL,
    ORDHDR_INVOICENBR_N        INTEGER,
    ORDHDR_ORDERDATE_D         DATE,
    ORDHDR_INVOICEDATE_D       DATE,
    ORDHDR_CANCELDATE_D        DATE,
    ORDHDR_CREDITHOLD_S        CHAR(1),
    ORDHDR_READYTOINVOICE_S    CHAR(1)          DEFAULT 'N',
    ORDHDR_NOTES_S             VARCHAR(60),
    ORDHDR_CREATEDBY_S         VARCHAR(10),
    ORDHDR_CREATEDATE_D        DATE,
    CONSTRAINT CHK_ORDHDR_READY CHECK 
               (ORDHDR_READYTOINVOICE_S IN ('N', 'Y')),
    CONSTRAINT CHK_ORDHDR_CREDH CHECK (ORDHDR_CREDITHOLD_S IN ('N', 'Y')),
    CONSTRAINT PK_ORDHDRPRIM PRIMARY KEY (ORDHDR_ID_N),
    CONSTRAINT IDX_ORDHDR_ORDNBR  UNIQUE (ORDHDR_NBR_S)
) 
;
   
-- 
-- TABLE: ORDER_LINE 
--
   
CREATE TABLE ORDER_LINE(
    ORDLINE_ID_N            INTEGER          NOT NULL,
    ORDLINE_ORDHDRID_FN     INTEGER          NOT NULL,
    ORDLINE_PRODID_FN       INTEGER,
    ORDLINE_ORDQTY_N        INTEGER,
    ORDLINE_SHIPQTY_N       INTEGER,
    ORDLINE_CREATEDATE_D    DATE,
    ORDLINE_CREATEDBY_S     VARCHAR(10),
    CONSTRAINT PK_ORDLINEPRIM PRIMARY KEY (ORDLINE_ID_N)
) 
;
   
-- 
-- TABLE: ORDER_SHIPMENT 
--
   
CREATE TABLE ORDER_SHIPMENT(
    ORDSHIP_ORDHDR_ID_FN      INTEGER    NOT NULL,
    ORDSHIP_SHIPMENT_ID_FN    INTEGER    NOT NULL,
    CONSTRAINT PK_ORDHDRSHIP 
         PRIMARY KEY (ORDSHIP_ORDHDR_ID_FN,ORDSHIP_SHIPMENT_ID_FN)
) 
;
   
-- 
-- TABLE: PAYMENT_TERMS 
--
   
CREATE TABLE PAYMENT_TERMS(
    PAYTERMS_ID_N           INTEGER          NOT NULL,
    PAYTERMS_CODE_S         VARCHAR(6),
    PAYTERMS_DESC_S         VARCHAR(60),
    PAYTERMS_DISCPCT_N      DECIMAL(5,2),
    PAYTERMS_DAYSTOPAY_N    INTEGER,
    CONSTRAINT PK_PAYTERMS PRIMARY KEY (PAYTERMS_ID_N)
) 
;
   
-- 
-- TABLE: PHONE 
--
   
CREATE TABLE PHONE(
    PHONE_ID_N          INTEGER          NOT NULL,
    PHONE_CUSTID_FN     INTEGER,
    PHONE_SALESMANID_FN INTEGER, 
    PHONE_PHONENUM_S    VARCHAR(20),
    PHONE_TYPE_S        VARCHAR(20),
    CONSTRAINT CHK_PHONE_TYPE CHECK (PHONE_TYPE_S IN ('PHONE', 'FAX')),
    CONSTRAINT PK_PHONERIMARY PRIMARY KEY (PHONE_ID_N)
) 
;
   
-- 
-- TABLE: PRODUCT 
--
   
CREATE TABLE PRODUCT(
    PROD_ID_N             INTEGER          NOT NULL,
    PROD_PRICE_N          DECIMAL(10,2),
    PROD_NUM_S            VARCHAR(10),
    PROD_DESCRIPTION_S    VARCHAR(44)      NOT NULL,
    PROD_STATUS_S         CHAR(1)          DEFAULT 'Y',
    PROD_BRAND_S          VARCHAR(20)      NOT NULL,
    PROD_PLTWID_N         DECIMAL(5,2)     NOT NULL,
    PROD_PLTLEN_N         DECIMAL(5,2)     NOT NULL,
    PROD_NETWGHT_N        DECIMAL(10,3),
    PROD_SHIPWEIGHT_N     DECIMAL(10,3),
    CONSTRAINT CHK_PRODSTATUS CHECK (PROD_STATUS_S in ('N', 'Y')),
    CONSTRAINT PK_PRODUCTPRIM PRIMARY KEY (PROD_ID_N)
) 
;
   
-- 
-- TABLE: SALESMAN 
--
   
CREATE TABLE SALESMAN(
    SALESMAN_ID_N         INTEGER          NOT NULL,
    SALESMAN_CODE_S       VARCHAR(2)       NOT NULL,
    SALESMAN_NAME_S       VARCHAR(50)      NOT NULL,
    SALESMAN_STATUS_S     CHAR(1)          DEFAULT 'Y',
    CONSTRAINT CHK_SALESSTATUS CHECK (SALESMAN_STATUS_S in ('N', 'Y')),
    CONSTRAINT PK_SALESMANPRIM PRIMARY KEY (SALESMAN_ID_N)
) 
;
   
-- 
-- TABLE: SHIPMENT 
--
   
CREATE TABLE SHIPMENT(
    SHIPMENT_ID_N             INTEGER          NOT NULL,
    SHIPMENT_BOLNUM_S         VARCHAR(6),
    SHIPMENT_SHIPDATE_D       DATE,
    SHIPMENT_ARRIVDATE_D      DATE,
    SHIPMENT_TOTALCASES_N     INTEGER,
    SHIPMENT_TRAILERNBR_S     VARCHAR(12),
    SHIPMENT_SHPMNTFRGHT_N    DECIMAL(12,2),
    SHIPMENT_FRTTERMS_S       VARCHAR(3),
    SHIPMENT_CREATEDBY_S      VARCHAR(10),
    SHIPMENT_CREATEDATE_D     TIMESTAMP,
    CONSTRAINT CHK_SHIPFRTTERMS 
           CHECK (SHIPMENT_FRTTERMS_S IN ('COL', 'PPD')),
    CONSTRAINT PK_SHIPMENTRPRIM PRIMARY KEY (SHIPMENT_ID_N)
) 
;
   
-- 
-- TABLE: STATUS 
--
   
CREATE TABLE STATUS(
    STATUS_ID_N      INTEGER          NOT NULL,
    STATUS_CODE_S    CHAR(2),
    STATUS_DESC_S    VARCHAR(30),
    CONSTRAINT PK_STATUSPRIM PRIMARY KEY (STATUS_ID_N)
) 
;
   
-- 
-- TABLE: DISCOUNT
--
   
CREATE TABLE DISCOUNT(
    DISC_MINAMOUNT_N DECIMAL(14,4)      NOT NULL,
    DISC_MAXAMOUNT_N DECIMAL(14,4)      NOT NULL,
    DISC_PCT         DECIMAL(5,3),
    CONSTRAINT PK_DISCOUNT 
               PRIMARY KEY(DISC_MINAMOUNT_N, DISC_MAXAMOUNT_N)
)
;
   
-- 
-- TABLE: RESELLER 
--
   
CREATE TABLE RESELLER ( 
  RESELLER_ID_N         INT     NOT NULL, 
  RESELLER_NAME_S       VARCHAR(30), 
  RESELLER_SUPPLIER_ID  INT, 
  CONSTRAINT PK_RESELLER
  PRIMARY KEY (RESELLER_ID_N) 
)
;
   
-- 
-- INDEXES
--
   
CREATE INDEX IDX_ADDR_CUST ON ADDRESS(ADDR_CUSTID_FN)
;
   
CREATE INDEX IDX_CUST_PAYTERMS ON CUSTOMER(CUST_PAYTERMSID_FN)
;
   
CREATE INDEX IDX_CUST_SALESMAN ON CUSTOMER(CUST_SALESMANID_FN)
;
   
CREATE INDEX IDX_ORDHDR_CUST ON ORDER_HEADER(ORDHDR_CUSTID_FN)
;
   
CREATE INDEX IDX_ORDHDR_STATUS ON ORDER_HEADER(ORDHDR_STATUSID_FN)
;
   
CREATE INDEX IDX_ORDHDR_PAYTERM ON ORDER_HEADER(ORDHDR_PAYTERMS_FN)
;
   
CREATE INDEX IDX_ORDHDR_SALES ON ORDER_HEADER(ORDHDR_SALESMANID_FN)
;
   
CREATE INDEX IDX_ORDLINE_ORDHDR ON ORDER_LINE(ORDLINE_ORDHDRID_FN)
;
   
CREATE INDEX IDX_ORDLINE_PROD ON ORDER_LINE(ORDLINE_PRODID_FN)
;
   
CREATE INDEX IDX_ORDSHIP_ORD ON ORDER_SHIPMENT(ORDSHIP_ORDHDR_ID_FN)
;
   
CREATE INDEX IDX_ORDSHIP_SHIP ON ORDER_SHIPMENT(ORDSHIP_SHIPMENT_ID_FN)
;
   
CREATE INDEX IDX_PHONE_CUST ON PHONE(PHONE_CUSTID_FN)
;
   
CREATE INDEX IDX_RESELLER_RESSUPID ON RESELLER(RESELLER_SUPPLIER_ID)
;
   
-- 
--  FOREIGN KEYS 
--
   
ALTER TABLE ADDRESS ADD CONSTRAINT FK_ADDR_CUST 
    FOREIGN KEY (ADDR_CUSTID_FN)
    REFERENCES CUSTOMER(CUST_ID_N)
;
   
ALTER TABLE ADDRESS ADD  CONSTRAINT FK_ADDR_SALESMAN
    FOREIGN KEY (ADDR_SALESMANID_FN) 
    REFERENCES SALESMAN (SALESMAN_ID_N) ;
   
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUST_PAYTERMS 
    FOREIGN KEY (CUST_PAYTERMSID_FN)
    REFERENCES PAYMENT_TERMS(PAYTERMS_ID_N)
;
   
ALTER TABLE CUSTOMER ADD CONSTRAINT FK_CUST_SALESMAN 
    FOREIGN KEY (CUST_SALESMANID_FN)
    REFERENCES SALESMAN(SALESMAN_ID_N)
;
   
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_PAYTERMS 
    FOREIGN KEY (ORDHDR_PAYTERMS_FN)
    REFERENCES PAYMENT_TERMS(PAYTERMS_ID_N)
;
   
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_CUSTOMER 
    FOREIGN KEY (ORDHDR_CUSTID_FN)
    REFERENCES CUSTOMER(CUST_ID_N)
;
   
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_STAT 
    FOREIGN KEY (ORDHDR_STATUSID_FN)
    REFERENCES STATUS(STATUS_ID_N)
;
   
ALTER TABLE ORDER_HEADER ADD CONSTRAINT FK_ORDHDR_SALES 
    FOREIGN KEY (ORDHDR_SALESMANID_FN)
    REFERENCES SALESMAN(SALESMAN_ID_N)
;
   
ALTER TABLE ORDER_LINE ADD CONSTRAINT FK_ORDLINE_ORDHDR 
    FOREIGN KEY (ORDLINE_ORDHDRID_FN)
    REFERENCES ORDER_HEADER(ORDHDR_ID_N)
;
   
ALTER TABLE ORDER_LINE ADD CONSTRAINT FK_ORDLINE_PRODUCT 
    FOREIGN KEY (ORDLINE_PRODID_FN)
    REFERENCES PRODUCT(PROD_ID_N)
;
   
ALTER TABLE ORDER_SHIPMENT ADD CONSTRAINT FK_ORDSH_ORD 
    FOREIGN KEY (ORDSHIP_ORDHDR_ID_FN)
    REFERENCES ORDER_HEADER(ORDHDR_ID_N)
;
   
ALTER TABLE ORDER_SHIPMENT ADD CONSTRAINT FK_ORDSH_SHIP 
    FOREIGN KEY (ORDSHIP_SHIPMENT_ID_FN)
    REFERENCES SHIPMENT(SHIPMENT_ID_N)
;
   
ALTER TABLE PHONE ADD CONSTRAINT FK_PHONE_CUST 
    FOREIGN KEY (PHONE_CUSTID_FN)
    REFERENCES CUSTOMER(CUST_ID_N)
;
   
ALTER TABLE PHONE ADD CONSTRAINT FK_SALESMAN_CUST
    FOREIGN KEY (PHONE_SALESMANID_FN) 
    REFERENCES SALESMAN (SALESMAN_ID_N)
;
   
ALTER TABLE RESELLER ADD CONSTRAINT FK_RESELLER_SUPPLIER
    FOREIGN KEY (RESELLER_SUPPLIER_ID) 
    REFERENCES RESELLER (RESELLER_ID_N)
;
   
--
-- VIEW V_CUSTOMER_TOTALS
--
   
CREATE VIEW   v_customer_totals
(
              customer_name,
              order_number,
              total_price
)
 AS
(
 SELECT       customer.cust_name_s, 
              order_header.ordhdr_nbr_s, 
              sum(product.prod_price_n * order_line.ordline_ordqty_n) 
 FROM         customer, 
              order_header,
              order_line,
              product 
 WHERE        customer.cust_id_n = order_header.ordhdr_custid_fn
 AND          order_header.ordhdr_id_n = order_line.ordline_ordhdrid_fn
 AND          product.prod_id_n = order_line.ordline_prodid_fn
 AND          order_line.ordline_ordqty_n IS NOT NULL
 GROUP BY     customer.cust_name_s,
              order_header.ordhdr_nbr_s
)
;
--
-- CREATE VIEW V_CUSTOMER_STATUS
--
   
CREATE VIEW   v_customer_status 
( 
              name,
              status 
)
AS
SELECT        cust_name_s, 
              cust_status_s
FROM          customer
;
   
--
--CREATE VIEW V_PHONE_NUMBER
--
   
CREATE VIEW   v_phone_number
( 
              phone_id, 
              phone_number
)
AS 
SELECT        phone_id_n, 
              phone_phonenum_s
FROM          phone
WHERE         phone_type_s = 'PHONE';
   
   
--
--CREATE VIEW V_FAX NUMBER
--
   
CREATE VIEW   v_fax_number
( 
              fax_id, 
              fax_number
)
AS 
SELECT        phone_id_n, 
              phone_phonenum_s
FROM          phone
WHERE         phone_type_s = 'FAX'
WITH CHECK OPTION
;
   
   
--
-- CREATE VIEW V_CUSTOMER_TOTALS_OVER_15000
--
   
CREATE VIEW   v_customer_totals_over_15000
AS
SELECT        * 
FROM          v_customer_totals 
WHERE         total_price > 15000;
   
   
   
--
-- CREATE VIEW V_CONTACT_LIST
--
   
CREATE VIEW   v_contact_list
(
              name,
              phone_number,
              contact_type
)
AS
SELECT        cust_name_s, 
              phone_phonenum_s,
              'customer'
FROM          customer,
              phone
WHERE         cust_id_n = phone_custid_fn 
AND           phone_type_s = 'PHONE'
UNION
SELECT        salesman_name_s,
              phone_phonenum_s,
              'salesperson'
FROM          salesman,
              phone
WHERE         salesman_id_n = phone_salesmanid_fn
AND           phone_type_s = 'PHONE';
   
   
--
--CREATE VIEW V_WILE_BESS_ORDERS
--
   
CREATE VIEW   v_wile_bess_orders
(
              order_number,
              order_date
)
AS
SELECT        ordhdr_nbr_s, 
              ordhdr_orderdate_d 
FROM          order_header 
WHERE         ordhdr_custid_fn IN
          (
           SELECT   cust_id_n
           FROM     customer
           WHERE    cust_name_s = 'WILE BESS COMPANY'
          )
;
   
CREATE VIEW   v_customer_totals_wilebess
AS
SELECT        customer_name, 
              total_price
FROM          v_customer_totals
WHERE         customer_name = 'WILE BESS COMPANY'
;