cag.sql

DROP TABLE consignment_agreement PURGE;
DROP TABLE purchase_agreement PURGE;
DROP TABLE service_invoice PURGE;
DROP TABLE services PURGE;
DROP TABLE sales_invoice PURGE;
DROP TABLE artwork PURGE;
DROP TABLE vendor PURGE;
DROP TABLE customer PURGE;
DROP TABLE preference PURGE;
DROP TABLE manager PURGE;
DROP TABLE employee_service PURGE;
DROP TABLE employee_sales PURGE;
DROP TABLE employee PURGE;



CREATE TABLE employee
 (emp_id 		NUMBER(6) 
	CONSTRAINT	  emp_emp_id_pk 	PRIMARY KEY,
  emp_type 		VARCHAR2(10)
	CONSTRAINT	  emp_emp_type_ck	CHECK(emp_type IN ('Service', 'Sales', 'Manager')));



CREATE TABLE employee_sales
 (emp_id 		NUMBER(6)
	CONSTRAINT 	  saemp_emp_id_pk 	PRIMARY KEY REFERENCES employee(emp_id),
  sa_first_name 	VARCHAR2(25)		NOT NULL,
  sa_last_name		VARCHAR2(25) 		NOT NULL,
  commission_pct	NUMBER(2,2)
	CONSTRAINT	  saemp_comm_pct_ck	CHECK(commission_pct > 0));



CREATE TABLE employee_service
 (emp_id 		NUMBER(6)
	CONSTRAINT 	  seemp_emp_id_pk	PRIMARY KEY REFERENCES employee(emp_id),
  se_first_name 	VARCHAR2(25)		NOT NULL,
  se_last_name 		VARCHAR2(25)		NOT NULL);



CREATE TABLE manager
 (emp_id		NUMBER(6)
	CONSTRAINT 	  mgremp_emp_id_pk	PRIMARY KEY REFERENCES employee(emp_id),
  mgr_first_name	VARCHAR2(25)		NOT NULL,
  mgr_last_name		VARCHAR2(25)		NOT NULL);



CREATE TABLE preference
 (pref_id		NUMBER(4)
	CONSTRAINT	  preference_pref_id_pk		PRIMARY KEY,
  pref_description	VARCHAR2(100)			NOT NULL);



CREATE TABLE customer
 (cust_id 		NUMBER(6)
	CONSTRAINT	  cust_cust_id_pk	PRIMARY KEY,
  cust_first_name	VARCHAR2(25)		NOT NULL,
  cust_last_name	VARCHAR2(25)		NOT NULL,
  street		VARCHAR2(25)		NOT NULL,
  city			VARCHAR2(25)		NOT NULL,
  state			VARCHAR2(2)		NOT NULL,
  zip			NUMBER(6)		NOT NULL,
  phone			VARCHAR2(20)		NOT NULL,
  email			VARCHAR2(25)		NOT NULL,
  pref_id		NUMBER(4)
	CONSTRAINT	  cust_pref_id_fk	REFERENCES preference(pref_id));



CREATE TABLE vendor
 (vendor_id		NUMBER(6)
	CONSTRAINT	  vendor_vendor_id_pk		PRIMARY KEY,
  vendor_type		VARCHAR2(25)			NOT NULL,
  vendor_name		VARCHAR2(25)			NOT NULL,
  vendor_contact	VARCHAR2(50),
  vendor_street		VARCHAR2(25)			NOT NULL,
  vendor_city		VARCHAR2(25)			NOT NULL,
  vendor_state		VARCHAR2(2)			NOT NULL,
  vendor_zip		NUMBER(6)			NOT NULL,
  vendor_phone		VARCHAR2(20)			NOT NULL,
  vendor_fax		VARCHAR2(20),
  vendor_email		VARCHAR2(25)			NOT NULL);



CREATE TABLE artwork
 (art_id		NUMBER(6)
	CONSTRAINT	  artwork_art_id_pk		PRIMARY KEY,
  art_title		VARCHAR2(25)			NOT NULL,
  artist		VARCHAR2(25)			NOT NULL,
  vendor_id		NUMBER(6)
	CONSTRAINT	  artwork_vendor_id_fk		REFERENCES vendor(vendor_id),
  art_category		VARCHAR2(15)
	CONSTRAINT	  artwork_art_category_ck	CHECK(art_category IN('Sculpture', 'Painting', 'Photograph')),
  art_period		VARCHAR2(25)			NOT NULL,
  art_height		NUMBER(2)			NOT NULL,
  art_length		NUMBER(2)			NOT NULL,
  art_width		NUMBER(2)			NOT NULL,
  art_material		VARCHAR2(50)			NOT NULL,
  region_of_origin	VARCHAR2(25)			NOT NULL,
  country_of_origin	VARCHAR2(25)			NOT NULL,
  art_type		VARCHAR2(25)			NOT NULL,
  art_status		VARCHAR2(8)			NOT NULL
	CONSTRAINT	  artwork_art_status_ck		CHECK(art_status IN ('Sold', 'Returned', 'ForSale')),
  art_weight		NUMBER(3),
  art_style		VARCHAR2(25),
  is_signed		VARCHAR2(3)
	CONSTRAINT	  artwork_is_signed_ck		CHECK(is_signed = 'Yes' OR is_signed = 'No'),
  is_unique		VARCHAR2(3)
	CONSTRAINT	  artwork_unique_ck		CHECK(is_unique = 'Yes' OR is_unique = 'No'),
  is_numbered		VARCHAR2(3)
	CONSTRAINT	  artwork_is_numbered_ck		CHECK(is_numbered = 'Yes' OR is_numbered = 'No'),
  CONSTRAINT		  artwork_yesno_ck		CHECK(
      (art_category = 'Sculpture' AND art_weight IS NOT NULL AND art_style IS NULL AND is_signed IS NULL AND is_unique IS NULL AND is_numbered IS NULL) OR
      (art_category = 'Painting' AND art_weight IS NULL AND art_style IS NOT NULL AND is_signed IS NULL AND is_unique IS NULL AND is_numbered IS NULL) OR
      (art_category = 'Photograph' AND art_weight IS NULL AND art_style IS NULL AND is_signed IS NOT NULL AND is_unique IS NOT NULL AND is_numbered IS NOT NULL)
    )
);



CREATE TABLE sales_invoice
 (inv_id		NUMBER(6)
	CONSTRAINT	  sales_invoice_inv_id_pk	PRIMARY KEY,
  inv_date		DATE				NOT NULL,
  mgr_approved		VARCHAR2(3)			NOT NULL
	CONSTRAINT	  sales_invoice_mgr_approval_ck	CHECK(mgr_approved = 'Yes' OR mgr_approved = 'No'),
  date_approved		DATE,
  inv_price		NUMBER(8)			NOT NULL
	CONSTRAINT	  sales_invoice_inv_price_ck	CHECK(inv_price > 0),
  inv_shipping		NUMBER(8)			NOT NULL
	CONSTRAINT	  sales_invoice_inv_shipping_ck	CHECK(inv_shipping > -1),
  inv_tax		NUMBER(8)			NOT NULL
	CONSTRAINT	  sales_invoice_inv_tax_ck	CHECK(inv_tax > -1),
  inv_misc		NUMBER(8)			NOT NULL
	CONSTRAINT	  sales_invoice_inv_misc_ck	CHECK(inv_misc > -1),
  inv_total		NUMBER(8)			NOT NULL
	CONSTRAINT	  sales_invoice_inv_total_ck	CHECK(inv_total > -1),
  emp_id		NUMBER(6)			NOT NULL,
  art_id		NUMBER(6)			NOT NULL,
  cust_id		NUMBER(6)			NOT NULL,
  CONSTRAINT	  	  sales_invoice_emp_id_fk 	FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
  CONSTRAINT		  sales_invoice_art_id_fk	FOREIGN KEY (art_id) REFERENCES artwork(art_id),
  CONSTRAINT		  sales_invoice_cust_id_fk	FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);



CREATE TABLE services
 (service_type_id	NUMBER(6)
	CONSTRAINT	  services_service_type_id_pk	PRIMARY KEY,
  service_type		VARCHAR2(25)			NOT NULL,
  service_price		NUMBER(4)			NOT NULL);



CREATE TABLE service_invoice
 (serv_id		NUMBER(6)
	CONSTRAINT	  srvc_inv_id_pk			PRIMARY KEY,
  serv_date		DATE					NOT NULL,
  emp_id		NUMBER(6)				NOT NULL,
  art_id		NUMBER(6)				NOT NULL,
  cust_id		NUMBER(6)				NOT NULL,
  service_type_id	NUMBER(6)				NOT NULL,
	CONSTRAINT	  srvc_inv_emp_id_fk			FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
	CONSTRAINT	  srvc_inv_art_id_fk			FOREIGN KEY (art_id) REFERENCES artwork(art_id),
	CONSTRAINT	  srvc_inv_cust_id_fk			FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
	CONSTRAINT	  srvc_inv_service_type_id_fk		FOREIGN KEY (service_type_id) REFERENCES services(service_type_id));



CREATE TABLE purchase_agreement
 (pa_id			NUMBER(6)
	CONSTRAINT	  purchase_agrmnt_pa_id_pk		PRIMARY KEY,
  pa_date		DATE					NOT NULL,
  pa_price		NUMBER(8)				NOT NULL
	CONSTRAINT	  purchase_agrmnt_pa_price_ck		CHECK(pa_price > 0),
  pa_shipping		NUMBER(8)				NOT NULL
	CONSTRAINT	  purchase_agrmnt_pa_shipping_ck	CHECK(pa_shipping > -1),
  pa_tax		NUMBER(8)				NOT NULL
	CONSTRAINT	  purchase_agrmnt_pa_tax_ck		CHECK(pa_tax > -1),
  pa_misc		NUMBER(8)				NOT NULL
	CONSTRAINT	  purchase_agrmnt_pa_misc_ck		CHECK(pa_misc > -1),
  pa_total		NUMBER(8)				NOT NULL
	CONSTRAINT	  purchase_agrmnt_pa_total_ck		CHECK(pa_total > -1),
  emp_id		NUMBER(6)				NOT NULL,
  art_id		NUMBER(6)				NOT NULL,
	CONSTRAINT	  purchase_agrmnt_emp_id_fk		FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
	CONSTRAINT	  purchase_agrmnt_art_id_fk		FOREIGN KEY (art_id) REFERENCES artwork(art_id));



CREATE TABLE consignment_agreement
 (ca_id				NUMBER(6)
	CONSTRAINT	  	  ca_id_pk			PRIMARY KEY,
  ca_date			DATE				NOT NULL,
  proposed_selling_price	NUMBER(8)			NOT NULL
	CONSTRAINT		  ca_proposed_selling_price_ck	CHECK(proposed_selling_price > 0),
  min_selling_price		NUMBER(8)			NOT NULL
	CONSTRAINT		  ca_min_selling_price_ck	CHECK(min_selling_price > 0),
  ca_pct			NUMBER(2,2)			NOT NULL
	CONSTRAINT		  ca_pct_ck			CHECK(ca_pct > 0),
  ca_comm_pct			NUMBER(2,2)			NOT NULL
	CONSTRAINT		  ca_comm_pct_ck		CHECK(ca_comm_pct > 0),
  start_date			DATE				NOT NULL,
  end_date			DATE				NOT NULL,
	CONSTRAINT		  ca_end_date_ck		CHECK(end_date > start_date),
  emp_id			NUMBER(6)			NOT NULL,
  art_id			NUMBER(6)			NOT NULL,
	CONSTRAINT	 	  ca_emp_id_fk			FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
	CONSTRAINT	  	  ca_art_id_fk			FOREIGN KEY (art_id) REFERENCES artwork(art_id));



INSERT INTO services (service_type_id, service_type, service_price)
VALUES (000001, 'Painting Framing', 0.45)
/

INSERT INTO services (service_type_id, service_type, service_price)
VALUES (000002, 'Sculpture Framing', 0.55)
/

INSERT INTO services (service_type_id, service_type, service_price)
VALUES (000003, 'Photograph Framing', 0.35)
/

INSERT INTO services (service_type_id, service_type, service_price)
VALUES (000004, 'Cleaning', 3.00)
/

INSERT INTO services (service_type_id, service_type, service_price)
VALUES (000005, 'Restoration', 5.00)
/


INSERT INTO preference
VALUES (0001, 'European Styles')
/

INSERT INTO preference
VALUES (0002, 'Sculptures')
/

INSERT INTO preference
VALUES (0003, 'Photographs of the Central Coast')
/


INSERT INTO employee
VALUES (000001, 'Manager)
/

INSERT INTO employee
VALUES (000002, 'Manager')
/

INSERT INTO employee
VALUES (000003, 'Sales')
/

INSERT INTO employee
VALUES (000004, 'Sales')
/

INSERT INTO employee
VALUES (000005, 'Service')
/

INSERT INTO employee
VALUES (000006, 'Service')
/

INSERT INTO employee
VALUES (000007, 'Service')
/


INSERT INTO employee_sales
VALUES (000003, 'Joe', 'Monet')
/

INSERT INTO employee_sales
VALUES (000004, 'Art', 'VanGogh')
/


INSERT INTO employee_service
VALUES (000005, 'Ms', 'Rambelson')
/

INSERT INTO employee_service
VALUES (000006, 'Alan', 'Wrench')
/

INSERT INTO employee_service
VALUES (000007, 'Woody', 'Apple')
/


INSERT INTO manager
VALUES (000001, 'Leonard', 'Martini')
/

INSERT INTO manager
VALUES (000002, 'Mary', 'Long')
/


INSERT INTO customer
VALUES (000001, 'Luke', 'Adams', '123 Java St.', 'MorroBay', 'CA', 93442, '805-111-1111', 'ladams@gmail.com', 0001)
/

INSERT INTO customer
VALUES (000002, 'Charlie', 'Burns', '2222 13th St.', 'LosOsos', 'CA', 93405, '805-222-2222', 'cburns@gmail.com', 0003)
/

INSERT INTO customer
VALUES (000003, 'Sharon', 'Bisso', '333 Crabby Ave.', 'Cayucos', 'CA', 93430, '805-333-3333', 'sbisso@att.net', NULL)
/

INSERT INTO customer
VALUES (000004, 'Sally', 'Fields', '4444 Grain Ave.', 'Hollywood', 'CA', 90046, '818-444-4444', 'fields@yahoo.com', NULL)
/

INSERT INTO customer
VALUES (000005, 'Dustin', 'Brown', '23 Captain Ln.', 'LosAngeles', 'CA', 90015, '818-555-5555', 'dbrown@gmail.com', 0002)
/

INSERT INTO customer
VALUES (000006, 'Lonzo', 'Ball', '2 Staples Ct.', 'LosAngeles', 'CA', 90015, '818-666-6666', 'lball@gmail.com', NULL)
/

INSERT INTO customer
VALUES (000007, 'Neil', 'Tyson', '777 Starway Ln.', 'NewYork', 'NY', 10012, '212-777-7777', 'blackhole@gmail.com', NULL)
/


INSERT INTO vendor (vendor_id, vendor_type, vendor_name, vendor_contact, vendor_street, vendor_city, vendor_state, vendor_zip,
		    vendor_phone, vendor_fax, vendor_email)
VALUES (000001, 'Gallery', 'Los Angeles Art Gallery', 'Joe Mama', '123 Figueroa St.', 'Los Angeles', 'CA', 90015, '818-123-4567', '818-765-4321', 'LArt@yahoo.com')
/

INSERT INTO vendor (vendor_id, vendor_type, vendor_name, vendor_contact, vendor_street, vendor_city, vendor_state, vendor_zip,
		    vendor_phone, vendor_fax, vendor_email)
VALUES (000002, 'Gallery', 'San Francisco Art Gallery', 'Jane Doe', '987 Lombard St.', 'San Francisco', 'CA', 94109, '415-987-6543', '415-345-6789', 'SFArt@gmail.com')
/

INSERT INTO vendor (vendor_id, vendor_type, vendor_name, vendor_contact, vendor_street, vendor_city, vendor_state, vendor_zip,
		    vendor_phone, vendor_fax, vendor_email)
VALUES (000003, 'Agent', 'SLO Art Club', 'John Smith', '11 Grand Ave.', 'San Luis Obispo', 'CA', 93401, '805-111-2222', NULL, 'SLOAC@gmail.com')
/

INSERT INTO vendor (vendor_id, vendor_type, vendor_name, vendor_contact, vendor_street, vendor_city, vendor_state, vendor_zip,
		    vendor_phone, vendor_fax, vendor_email)
VALUES (000004, 'Artist', 'Jack Johnson', 'Jack Johnson', '333 Union Ave.', 'Cambria', 'CA', 93428, '805-333-4444', '805-444-3333', 'JackJ@gmail.com')
/


INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000001, 'Morro Rock', 'Chris VanWinkle', 000003, 'Painting', 'Modern', 16, 20, 2, 'Watercolor Painting w/ Watercolor Canvas', 'California',
	'United States', 'Watercolor', 'Sold', NULL, 'Abstract', NULL, NULL, NULL)  
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000002, 'Cayucos Pier', 'Jack Johnson', 000004, 'Painting', 'Modern', 8, 10, 2, 'Oil Painting w/ Stretched Linen Canvas', 'California',
	'United States', 'Oil', 'Sold', NULL, 'Modernism', NULL, NULL, NULL)
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000003, 'American Gothic', 'Grant Wood', 000001, 'Painting', 'Modern', 31, 26, 3, 'Oil Painting w/ Beaverboard', 'California', 
	'United States', 'Oil', 'ForSale', NULL, 'Modernism', NULL, NULL, NULL)
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000004, 'Girl with a Pearl Earring', 'Johannes Vermeer', 000002, 'Painting', '17th Century', 18, 15, 2, 'Oil Painting w/ Stretched Linen Canvas', 'Delft',
	'Netherlands', 'Oil', 'Returned', NULL, 'Baroque', NULL, NULL, NULL)
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000005, 'Nefertiti Bust', 'Thusmose', 000001, 'Sculpture', 'Ancient Egypt', 10, 12, 19, 'Limestone Core w/ Painted Stucco layers', 'Amarna',
	'Egypt', 'Free Standing', 'Sold', 44, NULL, NULL, NULL, NULL)
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000006, 'Fountain', 'Marcel DuChamp', 000002, 'Sculpture', 'Dadaism', 24, 14, 19, 'Glazed Ceramic', 'New York', 
	'United States', 'Free Standing', 'ForSale', 88, NULL, NULL, NULL, NULL)
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000007, 'Venus de Milo', 'Alexandros of Antioch', 000001, 'Sculpture', 'Ancient Greece', 30, 24, 80, 'Marble', 'Milos',
	'Greece', 'Free Standing', 'ForSale', 200, NULL, NULL, NULL, NULL)
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000008, 'Earthrise', 'William Anders', 000003, 'Photograph', 'Modern', 10, 12, 1, 'Color Photograph', 'Outer Space', 
	'United States', 'Color', 'Sold', NULL, NULL, 'Yes', 'Yes', 'No')
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000009, 'Migrant Mother', 'Dorothea Lange', 000003, 'Photograph', 'Social Realism', 18, 10, 1, 'B&W Photograph' 'California',
	'United States', 'Black & White Photograph', 'Sold', NULL, NULL, 'Yes', 'Yes', 'No')
/

INSERT INTO artwork (art_id, art_title, artist, vendor_id, art_category, art_period, art_height, art_length, art_width, art_material, region_of_origin,
		     country_of_origin, art_type, art_status, art_weight, art_style, is_signed, is_unique, is_numbered)
VALUES (000010, 'Lunch Atop A Skyscraper', 'Charles Ebbets', 000003, 'Photograph', 'Modern', 8, 12, 1, 'B&W Photograph', 'New York', 
	'United States', 'Black & White Photograph', 'ForSale', NULL, NULL, 'No', 'No', 'No')
/


INSERT INTO sales_invoice (inv_id, inv_date, mgr_approved, date_approved, inv_price, inv_shipping, inv_tax, inv_misc, inv_total, emp_id, art_id, cust_id)
VALUES (000001, 1/20/2017, 'No', NULL, 2000.00, 100.00, 200.00, 0.00, 2300.00, 000003, 000008, 000007)
/

INSERT INTO sales_invoice (inv_id, inv_date, mgr_approved, date_approved, inv_price, inv_shipping, inv_tax, inv_misc, inv_total, emp_id, art_id, cust_id)
VALUES (000002, 6/6/2017, 'Yes', 6/6/2017, 10000.00, 1000.00, 1500.00, 500.00, 13000.00, 000001, 000005, 000005)
/

INSERT INTO sales_invoice (inv_id, inv_date, mgr_approved, date_approved, inv_price, inv_shipping, inv_tax, inv_misc, inv_total, emp_id, art_id, cust_id)
VALUES (000003, 11/3/2017, 'Yes', 11/6/2017, 5500.00, 150.00, 450.00, 100.00, 6200.00, 000004, 000002, 000002)
/

INSERT INTO sales_invoice (inv_id, inv_date, mgr_approved, date_approved, inv_price, inv_shipping, inv_tax, inv_misc, inv_total, emp_id, art_id, cust_id)
VALUES (000004, 11/6/2017, 'No', NULL, 500.00, 0.00, 150.00, 0.00, 650.00, 000004, 000009, 000002)
/

INSERT INTO sales_invoice (inv_id, inv_date, mgr_approved, date_approved, inv_price, inv_shipping, inv_tax, inv_misc, inv_total, emp_id, art_id, cust_id)
VALUES (000005, 11/15/2017, 'Yes', 11/15/2017, 6000.00, 200.00, 500.00, 0.00, 6700.00, 000003, 000001, 000004)
/


INSERT INTO service_invoice (serv_id, serv_date, emp_id, art_id, cust_id, service_type_id)
VALUES (000001, 6/10/2017, 000007, 000005, 000005, 000004)
/

INSERT INTO service_invoice (serv_id, serv_date, emp_id, art_id, cust_id, service_type_id)
VALUES (000002, 7/1/2017, 000007, 000005, 000005, 000005)
/

INSERT INTO service_invoice (serv_id, serv_date, emp_id, art_id, cust_id, service_type_id)
VALUES (000003, 11/11/2017, 000006, 000002, 000002, 000001)
/

INSERT INTO service_invoice (serv_id, serv_date, emp_id, art_id, cust_id, service_type_id)
VALUES (000004, 11/12/2017, 000006, 000009, 000002, 000003)
/

INSERT INTO service_invoice (serv_id, serv_date, emp_id, art_id, cust_id, service_type_id)
VALUES (000005, 11/16/2017, 000006, 000001, 000004, 000001)
/


INSERT INTO purchase_agreement (pa_id, pa_date, pa_price, pa_shipping, pa_tax, pa_misc, pa_total, emp_id, art_id)
VALUES (000001, 12/1/2016, 800.00, 150.00, 150.00, 0.00, 1100.00, 000001, 000008)
/

INSERT INTO purchase_agreement (pa_id, pa_date, pa_price, pa_shipping, pa_tax, pa_misc, pa_total, emp_id, art_id)
VALUES (000002, 12/1/2016, 200.00, 0.00, 50.00, 0.00, 250.00, 000001, 000009)
/

INSERT INTO purchase_agreement (pa_id, pa_date, pa_price, pa_shipping, pa_tax, pa_misc, pa_total, emp_id, art_id)
VALUES (000003, 12/1/2016, 1000.00, 0.00, 200.00, 0.00, 1200.00, 000001, 000010)
/

INSERT INTO purchase_agreement (pa_id, pa_date, pa_price, pa_shipping, pa_tax, pa_misc, pa_total, emp_id, art_id)
VALUES (000004, 2/27/2017, 4000.00, 0.00, 400.00, 0.00, 4400.00, 000001, 000001) 
/

INSERT INTO purchase_agreement (pa_id, pa_date, pa_price, pa_shipping, pa_tax, pa_misc, pa_total, emp_id, art_id)
VALUES (000005, 3/9/2017, 7500.00, 750.00, 750.00, 0.00, 9000.00, 000001, 000003)
/

INSERT INTO purchase_agreement (pa_id, pa_date, pa_price, pa_shipping, pa_tax, pa_misc, pa_total, emp_id, art_id)
VALUES (000006, 4/20/2017, 6500.00, 0.00, 0.00, 500.00, 7500.00, 000001, 000005)
/


INSERT INTO consignment_agreement(ca_id, ca_date, proposed_selling_price, min_selling_price, ca_pct, ca_comm_pct, start_date, end_date, emp_id, art_id)
VALUES (000001, 4/24/2017, 12000.00, 11500.00, .50, .02, 5/1/2017, 10/31/2017, 000001, 000004)
/

INSERT INTO consignment_agreement(ca_id, ca_date, proposed_selling_price, min_selling_price, ca_pct, ca_comm_pct, start_date, end_date, emp_id, art_id)
VALUES (000002, 9/29/2017, 6000.00, 5000.00, .50, .02, 10/1/2017, 3/31/2018, 000001, 000002)
/

INSERT INTO consignment_agreement(ca_id, ca_date, proposed_selling_price, min_selling_price, ca_pct, ca_comm_pct, start_date, end_date, emp_id, art_id)
VALUES (000003, 10/20/2017, 15000.00, 12000.00, .38, .02, 11/1/2017, 4/30/2018, 000001, 000006)
/

INSERT INTO consignment_agreement(ca_id, ca_date, proposed_selling_price, min_selling_price, ca_pct, ca_comm_pct, start_date, end_date, emp_id, art_id)
VALUES (000004, 10/20/2017, 15000.00, 12000.00, .38, .02, 11/1/2017, 4/30/2018, 000001, 000007)
/