History for DDLStatements

??changed:
-
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);




<hr solid id=comments_below>


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?