US14 - Create Data Definition Language (DDL) for tables
Description
As a developer, I want to script the data definitions for the creation of database tables which will contain the persistent data for the site. This will provide the structure upon which the application model will be based.
Acceptance Criteria
- Team approved the overall table structure.
- Team reviewed and approved the DDL statements.
- Scripts were run to ensure proper execution.
Tasks
- Team approve -- 30 mins -- Team
- DDL statment creations -- 4 hours -- Drew
- DDL script testing -- 30 mins -- Drew
Notes
create table zipcode
(zipcode_cd text primary key,
city text,
state text
);
create table producttypecategory
(producttypecat_id number primary key,
producttypecat_desc text
);
create table producttype
(producttype_id number primary key,
producttypecat_id number,
producttype_name text,
producttype_desc text);
create table brand
(brand_id number primary key,
brand_name text);
create table vendor
(vendor_id number primary key,
vendor_name text,
address1 text,
address2 text,
zipcode_cd text);
create table product
(product_id number primary key,
producttype_id number,
brand_id number,
vendor_id number,
prod_model text,
prod_name text,
unit_price number,
prod_desc text);
create table productimage
(product_id number);
/* This statement fails and needs to be added through the Microsoft Access administration tool.*/
alter table productimage add column product_image attachment;
create table productreview
(prodreview_id number primary key,
product_id number,
review_rating text,
review_desc text);
create table customer
(customer_id number primary key,
name_first text,
name_last text,
phone text,
address1 text,
address2 text,
zipcode_cd text,
customer_pwd text);
create table customerorder
(customerorder_id number primary key,
customer_id number,
order_date date);
create table customerorderitem
(customerorderitem_id number primary key,
customerorder_id number,
product_id number,
quantity number,
item_price number);
The following constraints may be added after development is near completion. Adding them now will complicate possible changes.
alter table producttype
add constraint fk_producttype_prodtypecat foreign key (producttypecat_id) references producttypecategory(producttypecat_id) on update cascade on delete set null;
alter table vendor
add constraint fk_vendor_zipcode_cd foreign key (zipcode_cd) references zipcode(zipcode_cd) on update cascade on delete set null;
alter table product
add constraint fk_product_producttype_id foreign key (producttype_id) references producttype(producttype_id) on update cascade on delete set null;
alter table product
add constraint fk_product_brand_id foreign key (brand_id) references brand(brand_id) on update cascade on delete set null;
alter table product
add constraint fk_product_vendor_id foreign key (vendor_id) references vendor(vendor_id) on update cascade on delete set null;
alter table productimage
add constraint fk_productimage_product_id foreign key (product_id) references product(product_id) on update cascade on delete set null;
alter table productreview
add constraint fk_productreview_product_id foreign key (product_id) references product(product_id) on update cascade on delete set null;
alter table customer
add constraint fk_customer_zipcode foreign key (zipcode_cd) references zipcode(zipcode_cd) on update cascade on delete set null;
alter table customerorder
add constraint fk_customerorder_customer_id foreign key (customer_id) references customer(customer_id) on update cascade on delete set null;
alter table customerorderitem
add constraint fk_customerorderitem_customerorder_id foreign key (customerorder_id) references customerorder(customerorder_id) on update cascade on delete set null;