DROP TABLE C_customer;
CREATE TABLE C_customer
(
	c_id		integer not null,
	c_first		nchar(16) NULL,
	c_last		nchar(16) NULL,
	c_street1	nchar(20) NULL,	
	c_street2	nchar(20) NULL,	
	c_city		nchar(20) NULL,	
	c_state		nchar(2) NULL,	
	c_country	nchar(10) NULL,	
	c_zip		nchar(9) NULL,	
	c_phone		nchar(16) NULL,
	c_contact	nchar(25) NULL,	
	c_since		datetime NULL,
	c_balance	numeric(9,2) NULL,
	c_credit	nchar(2) NULL,
	c_credit_limit	numeric(9,2) NULL,
	c_ytd_payment	numeric(9,2) NULL
)
ON C_CUST_GROUP;

CREATE UNIQUE clustered INDEX C_c_idx ON C_customer (c_id)
ON C_CUST_GROUP;

DROP TABLE C_supplier;
CREATE TABLE C_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 C_SUPP_GROUP;


CREATE UNIQUE INDEX C_supp_idx ON C_supplier (supp_id)
ON C_SUPP_GROUP;

DROP TABLE C_site;
CREATE TABLE C_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 C_SITE_GROUP;

CREATE UNIQUE INDEX C_site_idx ON C_site (site_id)
ON C_SITE_GROUP;

DROP TABLE C_parts;
CREATE TABLE C_parts
(
	p_id			nchar(15) not null,
	p_name			nchar(10) NULL,
	p_desc			nvarchar(100) NULL,
	p_rev			nchar(6) NULL,
	p_unit			nchar(10) NULL,
	p_cost			numeric(9,2) NULL,
	p_price			numeric(9,2) NULL,
	p_planner		integer NULL,
	p_type			integer NULL,
	p_ind			integer NULL,
        p_lomark                integer NULL,
        p_himark                integer NULL
)
ON C_PARTS_GROUP;

CREATE UNIQUE INDEX C_p_idx ON C_parts (p_id)
ON C_PARTS_GROUP;


DROP TABLE C_rule;
CREATE TABLE C_rule
(
	r_id		nvarchar(20) not null,
	r_text	text NULL
)
ON C_CUST_GROUP;

CREATE UNIQUE INDEX C_r_idx on C_rule (r_id)
ON C_CUST_GROUP;


DROP TABLE C_discount;
CREATE TABLE C_discount
(	
	d_id		nvarchar(64) not null,
	d_percent	integer NULL
)
ON C_CUST_GROUP;

INSERT INTO C_discount (d_id, d_percent) VALUES
('PlatinumCustomer', 40);

INSERT INTO C_discount (d_id, d_percent) VALUES
('GoldCustomer', 30);

INSERT INTO C_discount (d_id, d_percent) VALUES
('SilverCustomer', 20);

INSERT INTO C_discount (d_id, d_percent) VALUES
('LongTimeCustomer', 10);

INSERT INTO C_discount (d_id, d_percent) VALUES
('FirstTimeCustomer', 15);

INSERT INTO C_discount (d_id, d_percent) VALUES
('JustACustomer', 0);

CREATE UNIQUE INDEX C_d_idx on C_discount (d_id)
ON C_CUST_GROUP
