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. |