drop table construction;
drop table dressmaker;
drop table order_line;
drop table dress_order;
drop table quantities;
drop table material;
drop table garment;
drop table jmcust;

CREATE TABLE jmcust (
  c_no		INTEGER	PRIMARY KEY
 ,c_name	CHAR(20)	NOT NULL
 ,c_house_no	INTEGER	NOT NULL
 ,c_post_code	CHAR(9)	NOT NULL
 );

CREATE TABLE garment (
  style_no	INTEGER	PRIMARY KEY
 ,description	CHAR(20)	NOT NULL
 ,labour_cost	REAL		NOT NULL
 ,notions	CHAR(50)
 );

CREATE TABLE material (
  material_no	INTEGER	PRIMARY KEY
 ,fabric	CHAR(20)	NOT NULL
 ,colour	CHAR(20)	NOT NULL
 ,pattern	CHAR(20)	NOT NULL
 ,cost		REAL		NOT NULL
 );

CREATE TABLE quantities (
  -- size_q is the UK dress size for the item in question. For example
  --   size 8,10,12,14,16 are all valid dress sizes.
  -- quantity is how many linear feet are needed to make the particular
  --   item. Material comes in a roll, and you get that many feet off the
  --   roll to make the item.
  style_q	INTEGER NOT NULL REFERENCES garment
 ,size_q	INTEGER	NOT NULL
 ,quantity	REAL		NOT NULL
 ,PRIMARY KEY ( style_q, size_q )
	);

CREATE TABLE dress_order (
  order_no	INTEGER	PRIMARY KEY
 ,cust_no	INTEGER REFERENCES jmcust
 ,order_date	DATE NOT NULL 
 ,completed	CHAR(1)
 );

CREATE TABLE order_line (
  order_ref	INTEGER	NOT NULL REFERENCES dress_order
 ,line_no	INTEGER	NOT NULL
 ,ol_style	INTEGER	NOT NULL
 ,ol_size	INTEGER	NOT NULL
 ,ol_material	INTEGER	REFERENCES material
 ,PRIMARY KEY (order_ref, line_no)
 ,FOREIGN KEY (ol_style, ol_size) REFERENCES quantities
 );

CREATE TABLE dressmaker (
  d_no		INTEGER	PRIMARY KEY
 ,d_name	CHAR(20)	NOT NULL
 ,d_house_no	INTEGER	NOT NULL
 ,d_post_code	CHAR(8)	NOT NULL
 );

CREATE TABLE construction (
  maker		INTEGER	NOT NULL REFERENCES dressmaker
 ,order_ref	INTEGER	NOT NULL
 ,line_ref	INTEGER	NOT NULL
 ,start_date DATE NOT NULL 
 ,finish_date DATE 
 ,PRIMARY KEY ( maker, order_ref, line_ref )
 ,FOREIGN KEY ( order_ref, line_ref ) REFERENCES order_line
 );

--- tabjmcust, 1, 50
insert into jmcust values (  1,'Ms Black',12,'A21 4XY ');
insert into jmcust values (  2,'Ms Brown',24,'B2 5AB  ');
insert into jmcust values (  3,'Ms Gray',78,'CD31 4GH ');
insert into jmcust values (  4,'Ms White',25,'E24 8PQ ');
insert into jmcust values (  5,'Mr Brass',34,'FG24 9NM ');
insert into jmcust values (  6,'Ms Muir',31,'H2 7CV ');
insert into jmcust values (  7,'Dr Green',3,'SJ4 4WE ');
insert into jmcust values (  8,'Mrs Peacock',100,'DD6 9NM ');
--- tabgarment, 1, 50
insert into garment values (  1,'Trousers',18.00,'Zip/2 off 1.5cm buttons/ hem tape/ waist band  ');
insert into garment values (  2,'Long Skirt',15.00,'Zip/ 1 off button 3cm/ hemtape ');
insert into garment values (  3,'Shorts',10.00,'Zip/ Clip ');
insert into garment values (  4,'Short Skirt',14.25,'1 off 2cm button ');
insert into garment values (  5,'Sundress',20.00 ,NULL);
insert into garment values (  6,'Suntop',6.50 ,NULL);
--- tabmaterial, 1, 50
insert into material values (  1,'Silk','Black','Plain',7.00 );
insert into material values (  2,'Silk','Red Abstract','Printed',10.00 );
insert into material values (  3,'Cotton','Yellow Stripe','Woven',3.00 );
insert into material values (  4,'Cotton','Green Stripe','Woven',3.00 );
insert into material values (  5,'Cotton','Black Dotted','Woven',3.00 );
insert into material values (  6,'Cotton','Red Stripe','Woven',3.00 );
insert into material values (  7,'Polyester','Pale Yellow','Printed',2.55 );
insert into material values (  8,'Cotton','Blue Stripe','Woven',3.00 );
insert into material values (  9,'Cotton','Pink Check','Woven',3.00 );
insert into material values (  10,'Silk','Green Abstract','Printed',15.00 );
insert into material values (  11,'Rayon','Red/Orange','Printed',4.00 );
insert into material values (  12,'Serge','Navy Blue','Woven',5.50 );
insert into material values (  13,'Cotton','Blue Abstract','Printed',3.50 );
insert into material values (  14,'Cotton','Green Abstract','Printed',3.50 );
--- tabquantities, 1, 50
insert into quantities values (  1,8,2.7 );
insert into quantities values (  1,10,2.7 );
insert into quantities values (  1,12,2.8 );
insert into quantities values (  1,14,2.8 );
insert into quantities values (  1,16,3.0 );
insert into quantities values (  1,18,3.0 );
insert into quantities values (  2,8,3.4 );
insert into quantities values (  2,10,3.4 );
insert into quantities values (  2,12,3.8 );
insert into quantities values (  2,14,3.8 );
insert into quantities values (  2,16,4.2 );
insert into quantities values (  2,18,4.5 );
insert into quantities values (  3,8,1.3 );
insert into quantities values (  3,10,1.3 );
insert into quantities values (  3,12,1.3 );
insert into quantities values (  3,14,1.5 );
insert into quantities values (  3,16,1.6 );
insert into quantities values (  3,18,1.8 );
insert into quantities values (  4,8,1.2 );
insert into quantities values (  4,10,1.2 );
insert into quantities values (  4,12,1.2 );
insert into quantities values (  4,14,1.4 );
insert into quantities values (  4,16,1.5 );
insert into quantities values (  4,18,1.9 );
insert into quantities values (  5,8,3.2 );
insert into quantities values (  5,10,3.2 );
insert into quantities values (  5,12,3.2 );
insert into quantities values (  5,14,3.4 );
insert into quantities values (  5,16,5.2 );
insert into quantities values (  5,18,5.2);
insert into quantities values (  6,8,1.0 );
insert into quantities values (  6,10,1.0 );
insert into quantities values (  6,12,1.0 );
insert into quantities values (  6,14,1.5 );
insert into quantities values (  6,16,1.5 );
insert into quantities values (  6,18,1.8 );
--- tabdress_order, 1, 50
insert into dress_order values (  1,8,'10 jan 2002','Y');
insert into dress_order values (  2,7,'11 jan 2002','Y');
insert into dress_order values (  3,6,'20 jan 2002','Y');
insert into dress_order values (  4,5,'02 feb 2002','Y');
insert into dress_order values (  5,4,'03 feb 2002','Y');
insert into dress_order values (  6,3,'20 feb 2002','N');
insert into dress_order values (  7,2,'21 feb 2002','N');
insert into dress_order values (  8,1,'27 feb 2002','N');
insert into dress_order values (  9,2,'27 feb 2002','N');
insert into dress_order values (  10,3,'28 feb 2002','N');
insert into dress_order values (  11,4,'01 mar 2002','N');
insert into dress_order values (  12,5,'03 mar 2002','N');
--- taborder_line, 1, 50
insert into order_line values (  1,1,1,8,1);
insert into order_line values (  1,2,2,8,2);
insert into order_line values (  2,1,3,10,3);
insert into order_line values (  2,2,4,10,4);
insert into order_line values (  2,3,5,10,5);
insert into order_line values (  3,1,6,12,6);
insert into order_line values (  4,1,1,14,7);
insert into order_line values (  4,2,2,14,10);
insert into order_line values (  5,1,3,16,9);
insert into order_line values (  5,2,4,16,10);
insert into order_line values (  5,3,5,16,11);
insert into order_line values (  6,1,1,8,12);
insert into order_line values (  6,2,2,8,13);
insert into order_line values (  6,3,3,8,14);
insert into order_line values (  7,1,4,10,1);
insert into order_line values (  7,2,5,10,2);
insert into order_line values (  7,3,6,10,3);
insert into order_line values (  8,1,6,12,4);
insert into order_line values (  8,2,5,12,5);
insert into order_line values (  8,3,4,12,6);
insert into order_line values (  9,1,3,14,7);
insert into order_line values (  10,1,2,16,8 );
insert into order_line values (  10,2,1,16,9 );
insert into order_line values (  11,1,1,18,10);
insert into order_line values (  11,2,2,18,11);
insert into order_line values (  11,3,3,18,12);
insert into order_line values (  12,1,4,8,13);
insert into order_line values (  12,2,5,8,14);
insert into order_line values (  12,3,6,8,1);
insert into order_line values (  12,4,1,8,2);
insert into order_line values (  12,5,2,8,3);
--- tabdressmaker, 1, 50
insert into dressmaker values (  1,'Mrs Hem',2,'A12 6BC');
insert into dressmaker values (  2,'Miss Stitch',4,'DF4 7HJ');
insert into dressmaker values (  3,'Mr Needles',56,'E12 6LG');
insert into dressmaker values (  4,'Ms Sew',27,'EF7 9KL');
insert into dressmaker values (  5,'Mr Seam',31,'H45 7YH');
insert into dressmaker values (  6,'Mr Taylor',3,'SH6 9RT');
insert into dressmaker values (  7,'Miss Pins',7,'B4 9BL');
--- tabconstruction, 1, 50
insert into construction values (  1,1,1,'10 jan 2002','05 mar 2002');
insert into construction values (  2,1,2,'10 jan 2002','15 mar 2002');
insert into construction values (  3,2,1,'11 jan 2002','05 mar 2002');
insert into construction values (  4,2,2,'11 jan 2002','25 mar 2002');
insert into construction values (  5,2,3,'11 jan 2002','05 mar 2002');
insert into construction values (  6,3,1,'20 jan 2002','25 mar 2002');
insert into construction values (  7,4,1,'02 feb 2002','05 mar 2002');
insert into construction values (  1,4,2,'02 feb 2002','25 mar 2002');
insert into construction values (  2,5,1,'03 feb 2002','15 mar 2002');
insert into construction values (  3,5,2,'03 feb 2002','25 mar 2002');
insert into construction values (  4,5,3,'03 feb 2002','27 mar 2002');
insert into construction values (  5,6,1,'20 feb 2002',NULL);
insert into construction values (  6,6,2,'20 feb 2002','28 mar 2002');
insert into construction values (  7,6,3,'20 feb 2002',NULL);
insert into construction values (  1,7,1,'21 feb 2002',NULL);
insert into construction values (  2,7,2,'21 feb 2002',NULL);
insert into construction values (  3,7,3,'21 feb 2002','28 mar 2002');
insert into construction values (  4,8,1,'27 feb 2002','03 mar 2002');
insert into construction values (  5,8,2,'27 feb 2002',NULL);
insert into construction values (  6,8,3,'27 feb 2002',NULL);
insert into construction values (  7,9,1,'27 feb 2002',NULL);
insert into construction values (  1,10,1,'28 feb 2002',NULL);
insert into construction values (  2,10,2,'28 mar 2002',NULL );
insert into construction values (  3,11,1,'01 mar 2002','04 mar 2002');
insert into construction values (  4,11,2,'01 mar 2002',NULL);
insert into construction values (  5,11,3,'01 mar 2002',NULL);
insert into construction values (  7,12,2,'03 mar 2002',NULL);
insert into construction values (  1,12,3,'03 mar 2002',NULL);
insert into construction values (  2,12,4,'03 mar 2002',NULL);
insert into construction values (  2,12,5,'03 mar 2002',NULL);
