DROP TABLE S_component;
CREATE TABLE S_component
(
	comp_id		nchar(15) not null,
	comp_name	nchar(10) NULL,
	comp_desc	nvarchar(100) NULL,
	comp_unit	nchar(10) NULL,
	comp_cost	numeric(9,2) NULL,
      qty_on_order 	integer NULL,
      qty_demanded      integer NULL,
      lead_time         integer NULL,
      container_size    integer NULL
)
ON S_COMP_GROUP;

CREATE UNIQUE clustered INDEX S_comp_idx ON S_component (comp_id)
ON S_COMP_GROUP;

DROP TABLE S_supp_component;
CREATE TABLE S_supp_component
(
	sc_p_id		nchar(15) not null,
	sc_supp_id	integer not null,
	sc_price	numeric(9,2) NULL,
	sc_qty		integer NULL,
	sc_discount	float NULL,
	sc_del_date	integer NULL
)
ON S_SC_GROUP;

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

DROP TABLE S_supplier;
CREATE TABLE S_supplier
(
	supp_id		integer not null,
	supp_name	nchar(16) NULL,
	supp_street1	nchar(20) NULL,	
	supp_street2	nchar(20) NULL,	
	supp_city	nchar(20) NULL,	
	supp_state	nchar(2) NULL,	
	supp_country	nchar(10) NULL,	
	supp_zip	nchar(9) NULL,	
	supp_phone	nchar(16) NULL,
	supp_contact	nchar(25) NULL
)
ON S_SUPP_GROUP;

CREATE UNIQUE clustered INDEX S_supp_idx ON S_supplier (supp_id)
ON S_SUPP_GROUP;

DROP TABLE S_site;
CREATE TABLE S_site
(
	site_id		integer not null,
	site_name	nchar(16) NULL,
	site_street1	nchar(20) NULL,	
	site_street2	nchar(20) NULL,	
	site_city	nchar(20) NULL,	
	site_state	nchar(2) NULL,	
	site_country	nchar(10) NULL,	
	site_zip	nchar(9) NULL
)
ON S_SITE_GROUP;

CREATE UNIQUE clustered INDEX S_site_idx ON S_site (site_id)
ON S_SITE_GROUP;

DROP TABLE S_purchase_order;
CREATE TABLE S_purchase_order
(
	po_number	integer not null,
	po_supp_id	integer NULL,
	po_site_id	integer NULL
)
ON S_PO_GROUP;

CREATE UNIQUE clustered INDEX S_po_idx ON S_purchase_order (po_number)
ON S_PO_GROUP;

DROP TABLE S_purchase_orderline;
CREATE TABLE S_purchase_orderline
(
	pol_number	integer not null,
	pol_po_id	integer not null,
	pol_p_id	nchar(15) NULL,
	pol_qty		integer NULL,
	pol_balance	numeric(11,2) NULL,
	pol_deldate	datetime NULL,
	pol_message	nvarchar(100) NULL
)
ON S_PO_GROUP;

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