/* DDL for WebBuilder CD Catalog Demo */
create database wbdemo on sqldev;

create file wbdemod0;
create file wbdemok0;

create table paytype (
    paytype_id integer primary key,
    name char(40) not null,
    image_large char(80),
    image_small char(80)
) in wbdemod0 ;

create unique index pay_key on paytype(paytype_id) in wbdemok0;

create table customer (
    cust_id integer primary key,
    fname char(30) not null,
    lname char(30) not null,
    password char(15) not null,
    authpass char(15),
    email char(40),
    mail_addr1 char(30),
    mail_addr2 char(30),
    mail_city char(30),
    mail_state char(2),
    mail_zip char(10)
) in wbdemod0 ;

create unique index cust_key on customer(cust_id) in wbdemok0;

create table custcard (
    custcard_id integer primary key,
    cust_id integer not null references customer,
    paytype_id integer not null references paytype,
    nameoncard char(30) not null,
    cardacct char(20) not null,
    cardexmo integer not null,
    cardexyr integer not null,
    statflag char(1) not null
) in wbdemod0 ;

create unique index card_key on custcard(custcard_id) in wbdemok0;

create table product (
    product_id integer primary key,
    prodnum char(40),
    price decimal(16,2),
    name char(40) not null,
    html char(40),
    image_large char(80),
    image_small char(80),
    descr varchar(4096)
) in wbdemod0 ;

create unique index prod_key on product(product_id) in wbdemok0;

create table prodvar (
    prodvar_id integer primary key,
    product_id integer not null references product,
    descr char(40)
) in wbdemod0 ;

create unique index pvar_key on prodvar(prodvar_id) in wbdemok0;

create table custorder (
    order_id integer primary key,
    cust_id integer references customer,
    order_dt date not null,
    purchased char(1) not null,
    custcard_id integer,
    shiptype char(10)
) in wbdemod0 ;

create unique index ord_key on custorder(order_id) in wbdemok0;

create table orditem (
    session char(30),
    order_id integer not null references custorder,
    product_id integer not null references product,
    prodvar_id integer,
    qty integer
) in wbdemod0 ;

create index item_ndx on orditem(order_id, product_id ) in wbdemok0;

create table keyword (
    keywd_id integer primary key,
    keywd char(20) not null
) in wbdemod0 ;

create unique index word_key on keyword(keywd_id) in wbdemok0;

create table prodkey (
    keywd_id integer not null references keyword,
    product_id integer not null references product
) in wbdemod0 ;

