SQL Scripts to Create ACME Database Objects

These statements create ACME database in DB2:

--  -- 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' ;

The same set of scripts with just a slight modification will create the ACME database with Oracle. All you would need to do is to replace the SHIPMENT_CREATEDATE_D column data type from TIMESTAMP to DATE in table SHIPMENT.

To create an MS SQL ACME database, the SHIPMENT_CREATEDATE_D column data type has to be changed to DATETIME (or SMALLDATETIME); in addition, all DATE columns need to be altered to the appropriate MS SQL datatypes.

Note 

The script above is as generic as possible; the actual SQL statements on your CD-ROM are more implementation-specific.




SQL Bible
Microsoft SQL Server 2008 Bible
ISBN: 0470257040
EAN: 2147483647
Year: 2005
Pages: 208

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net