create table supplier (
    suppid int not null,
    name varchar(80),
    status varchar(2),
    addr1 varchar(80),
    addr2 varchar(80),
    city varchar(80),
    state varchar(80),
    zip varchar(5),
    phone varchar(80),
    constraint pk_supplier primary key (suppid)
);

create table signon (
    username varchar(25),
    password varchar(25),
    constraint pk_signon primary key (username)
);

create table account (
    userid varchar(80),
    email varchar(80),
    firstname varchar(80),
    lastname varchar(80),
    status varchar(2),
    addr1 varchar(80),
    addr2 varchar(40),
    city varchar(80),
    state varchar(80),
    zip varchar(20),
    country varchar(20),
    phone varchar(80),
    constraint pk_account primary key (userid)
);

create table profile (
    userid varchar(80),
    langpref varchar(80),
    favcategory varchar(30),
    mylistopt int,
    banneropt int,
    constraint pk_profile primary key (userid)
);

create table bannerdata (
    favcategory varchar(80),
    bannername varchar(255),
    constraint pk_bannerdata primary key (favcategory)
);

create table orders (
      orderid int not null,
      userid varchar(80),
      orderdate date not null,
      shipaddr1 varchar(80),
      shipaddr2 varchar(80),
      shipcity varchar(80),
      shipstate varchar(80),
      shipzip varchar(20),
      shipcountry varchar(20),
      billaddr1 varchar(80),
      billaddr2 varchar(80),
      billcity varchar(80),
      billstate varchar(80),
      billzip varchar(20),
      billcountry varchar(20),
      courier varchar(80),
      totalprice decimal(10,2) not null,
      billtofirstname varchar(80),
      billtolastname varchar(80),
      shiptofirstname varchar(80),
      shiptolastname varchar(80),
      creditcard varchar(80),
      exprdate varchar(7),
      cardtype varchar(80),
      locale varchar(80),
      constraint pk_orders primary key (orderid)
);

create table orderstatus (
      orderid int not null,
      linenum int not null,
      timestamp date not null,
      status varchar(2),
      constraint pk_orderstatus primary key (orderid, linenum)
);

create table lineitem (
      orderid int not null,
      linenum int not null,
      itemid varchar(10),
      quantity int not null,
      unitprice decimal(10,2) not null,
      constraint pk_lineitem primary key (orderid, linenum)
);

create table category (
	catid varchar(10),
	name varchar(80),
	descn varchar(255),
	constraint pk_category primary key (catid)
);

create table product (
    productid varchar(10),
    category varchar(10),
    name varchar(80),
    descn varchar(255),
    constraint pk_product primary key (productid),
        constraint fk_product_1 foreign key (category)
        references category (catid)
);

create index productCat on product (category);
create index productName on product (name);

create table item (
    itemid varchar(10),
    productid varchar(10),
    listprice decimal(10,2),
    unitcost decimal(10,2),
    supplier int,
    status varchar(2),
    attr1 varchar(80),
    attr2 varchar(80),
    attr3 varchar(80),
    attr4 varchar(80),
    attr5 varchar(80),
    constraint pk_item primary key (itemid),
        constraint fk_item_1 foreign key (productid)
        references product (productid),
        constraint fk_item_2 foreign key (supplier)
        references supplier (suppid)
);

create index itemProd on item (productid);

create table inventory (
    itemid varchar(10),
    qty int not null,
    constraint pk_inventory primary key (itemid)
);

create table sequence (
    name varchar(30),
    nextid int not null,
    constraint pk_sequence primary key (name)
);