DDLStatements

DDL statements

From the worknotes:

*This should have a create table definition for each table in your design. Make certain your DDL is an implementation of your ERD. If a relationship is mandatory, the foreign key must be "NOT NULL." Please declare all primary and foreign keys. For each foreign key, indicate what should be implemented upon deletion and updating of the row referenced in the parent table. This will be similar to the ON DELETE CASCADES and ON UPDATE RESTRICT in chapter 4 in your text.*

We'll produce this in plain text below.

Produced SQL:

    CREATE TABLE CUSTOMER 
        (CUST_ID        char(5)         NOT NULL
        ,CUST_NAME      varchar(20)     NOT NULL
        ,CUST_INIT      char(3)
        ,CUST_TITLE     char(5)
        ,CUST_ADD       varchar(50)
        ,CUST_CITY      varchar(20)
        ,CUST_STATE     varchar(20)
        ,CUST_ZIP
        ,CUST_AREA_CD   char (3)
        ,CUST_PHONE     char(8)
        ,PRIMARY KEY (CUST_ID));

    CREATE TABLE PAYMENT METHODS
        (PAY_METH_ID    CHAR(1)         NOT NULL
        ,PAY_METHOD     CHAR(20)        NOT NULL
        ,PAY_METH_LIM   NUMBER(5,2)
        PRIMARY KEY (PAY_METH_ID);

    CREATE TABLE VENDOR 
        (VEND_ID        CHAR(5)         NOT NULL
        ,VEND_COMP_NAME VARCHAR(20)     NOT NULL
        ,VEND_ADDRS     VARCHAR(50)
        ,VEND_CITY      VARCHAR(20)
        ,VEND_STATE     VARCHAR(20)
        ,VENDOR_ZIP
        ,VEND_AREA_CD   CHAR(3)
        ,VEND_PHONE     CHAR(8)
        ,PRIMARY KEY (CAR_REG));

    CREATE TABLE CAR 
        (CAR_REG        CHAR(10)        not null
        ,CUST_ID        CHAR(5)         not null
        ,CAR_LAST_SERV  DATE
        ,CAR_MILEAGE    INTEGER
        ,PRIMARY KEY (CAR_REG)
        ,FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE);

    CREATE TABLE PART 
        (PART_ID        CHAR(5)         NOT NULL
        ,VEND_ID        CHAR(5)         NOT NULL
        ,PART_PRICE     CURRENCY
        ,PART_MIN_STCK  SMALLINT
        ,PRIMARY KEY (PART_ID)
        ,FOREIGN KEY (VEND_ID) REFERENCES VENDOR
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE);

    CREATE TABLE INVENTORY
        (INVENTORY_ID   CHAR(5)         NOT NULL
        ,PART_ID        CHAR(5)         NOT NULL
        ,VENDOR_ID      CHAR(5)         NOT NULL
        ,INV_IN_STOCK   SMALLINT
        ,PRIMARY KEY (INVENTORY_ID)
        ,FOREIGN KEY (PART_ID) REFERENCES PART
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE
        ,FOREIGN KEY (VENDOR_ID) REFERENCES VENDOR
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE);

    CREATE TABLE INVOICE 
        (INV_REF_NO     char(5)         NOT NULL
        ,CUST_ID        char(5)         NOT NULL
        ,INV_DATE       date
        ,INV_LAB_CHG    
        ,PAY_METHOD     char(20)        NOT NULL
        ,PRIMARY KEY (INV_REF_NO)
        ,FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE
        ,FOREIGN KEY (PAY_METHOD) REFERENCES PAYMENT_METHODS
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE);

    CREATE TABLE INVOICE LINES 
        (INV_REF_NO     CHAR(5)         NOT NULL
        ,PART_ID        CHAR(5)         NOT NULL
        ,INV_LIN_QUANT  INTEGER
        ,INV_LIN_TOTAL  INTEGER
        ,PRIMARY KEY (INV_REF_NO)
        ,FOREIGN KEY (PART_ID) REFERENCES PART
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE);

    CREATE TABLE SUPPLIES 
        (PART_ID        CHAR(5)         NOT NULL
        ,VENDOR_ID      CHAR(5)         NOT NULL
        ,COST           CURRENCY
        ,PRIMARY KEY (PART_ID)REFERENCES PART
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE
        ,PRIMARY KEY (VENDOR_ID)REFERENCES INVENTORY
        ,       ON DELETE RESTRICT
        ,       ON UPDATE CASCADE);

stephen_jackson (Sep 10, 2001 2:04 pm; Comment #1)

I will post first draft tuesday using latest data dictionary

stephen_jackson (Sep 11, 2001 5:11 am; Comment #2)

John, please note I had a problem with the foreign key in INVOICE which references PAYMENT_METHODS the column names didnt match. Look at my solution. Also I added some data types on the invoice lines

mj (Sep 12, 2001 2:58 pm; Comment #3)

I am missing ZIP code and labourcharge types; US ZIP codes are always 5 digits (so char(5) is appropriate) and labour charge should be a currency type. Also, the maximum spending limit of a payment type should be a currency to be consistent.

mj (Sep 12, 2001 3:02 pm; Comment #4)

Shouldn't we be using singular entity and table names? INVENTORY LINE, SUPPLY

mj (Sep 12, 2001 3:13 pm; Comment #5)

Shouldn't that be 'SUPLIES'; it being a composite entity?

mj (Sep 12, 2001 3:13 pm; Comment #6)

Shouldn't that be 'SUPLIES'; it being a composite entity?