DROP TABLE S_component;
CREATE TABLE S_component
(
        comp_id                 char(15) not null,
        comp_name               varchar(10),
        comp_desc               varchar(100),
        comp_unit               varchar(10),
        comp_cost               numeric(9,2),
        qty_on_order    integer,
        qty_demanded    integer,
        lead_time               integer,
        container_size  integer
)
in S_comp_space;

CREATE UNIQUE INDEX S_comp_idx ON S_component (comp_id);

DROP TABLE S_supp_component;
CREATE TABLE S_supp_component
(
        sc_p_id                 char(15) not null,
        sc_supp_id              integer not null,
        sc_price                numeric(9,2),
        sc_qty                  integer,
        sc_discount             float,
        sc_del_date             integer
)
in S_sc_space;

CREATE UNIQUE INDEX S_sc_idx ON S_supp_component (sc_supp_id, sc_p_id);

DROP TABLE S_supplier;
CREATE TABLE S_supplier
(
        supp_id                 integer not null,
        supp_name               varchar(16),
        supp_street1    varchar(20),
        supp_street2    varchar(20),
        supp_city               varchar(20),
        supp_state              char(2),
        supp_country    varchar(10),
        supp_zip                varchar(9),
        supp_phone              varchar(16),
        supp_contact    varchar(25)
)
in S_supp_space;

CREATE UNIQUE INDEX S_supp_idx ON S_supplier (supp_id);

DROP TABLE S_site;
CREATE TABLE S_site
(
        site_id                 integer not null,
        site_name               varchar(16),
        site_street1    varchar(20),
        site_street2    varchar(20),
        site_city               varchar(20),
        site_state              char(2),
        site_country    varchar(10),
        site_zip                varchar(9)
)
in S_site_space;

CREATE UNIQUE INDEX S_site_idx ON S_site (site_id);

DROP TABLE S_purchase_order;
CREATE TABLE S_purchase_order
(
        po_number               integer not null,
        po_supp_id              integer,
        po_site_id              integer
)
in S_po_space;

CREATE UNIQUE INDEX S_po_idx ON S_purchase_order (po_number);

DROP TABLE S_purchase_orderline;
CREATE TABLE S_purchase_orderline
(
        pol_number              integer not null,
        pol_po_id               integer not null,
        pol_p_id                char(15),
        pol_qty                 integer,
        pol_balance             numeric(9,2),
        pol_deldate             date,
        pol_message             varchar(100)
)
in S_po_space;

CREATE UNIQUE INDEX S_pol_idx ON S_purchase_orderline (pol_po_id, pol_number);

