CREATE TABLE PROJECT
(
  PROJECT_ID SMALLINT NOT NULL,
  PROJECT_NAME VARCHAR(35) NOT NULL, 
  WBS_FINALIZED BOOLEAN NOT NULL,
  WP_ID SMALLINT NOT NULL,
  PRIMARY KEY (PROJECT_ID),
  UNIQUE KEY (PROJECT_ID));

CREATE TABLE ACTIVITY
(
  ACTIVITY_ID SMALLINT NOT NULL UNIQUE,
  PROJECT_ID SMALLINT NOT NULL,
  ACTIVITY_NAME VARCHAR(50) NOT NULL,
  START_DATE DATE NOT NULL,
  PLAN_DURATION SMALLINT NOT NULL,
  WBS_ID SMALLINT NOT NULL, 
  PARENT_ID SMALLINT,
  DISPLAY_ORDER INT,
  ACTUAL_START DATE,
  ACTUAL_END DATE,
  PERCENT_COMPLETE DECIMAL(3,1),
  PRIMARY KEY (ACTIVITY_ID),
  FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON UPDATE CASCADE);

CREATE TABLE RESOURCE
(
  RESOURCE_ID SMALLINT NOT NULL UNIQUE,
  PROJECT_ID SMALLINT NOT NULL,
  RESROUCE_NAME VARCHAR(20) NOT NULL,
  MEASUREMENT VARCHAR(10),
  COST_PER_UNIT INT NOT NULL,
  PRIMARY KEY (RESOURCE_ID),
  FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON UPDATE CASCADE );
 
CREATE TABLE ACTIVITY_RESOURCE
(
  AR_ID SMALLINT NOT NULL UNIQUE,
  ACTIVITY_ID SMALLINT NOT NULL,
  RESOURCE_ID SMALLINT NOT NULL,
  DESCRIPTION VARCHAR(35),
  PLAN_QUANTITY SMALLINT NOT NULL,
  PRIMARY KEY (AR_ID),  
  FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITY(ACTIVITY_ID) ON UPDATE CASCADE,
  FOREIGN KEY (RESOURCE_ID) REFERENCES RESOURCE(RESOURCE_ID) ON UPDATE CASCADE );
 
CREATE TABLE RESOURCE_CONSUME
(
  RC_ID SMALLINT NOT NULL UNIQUE,
  AR_ID SMALLINT NOT NULL,
  QUANTITY DECIMAL(9,1) NOT NULL,
  CONSUME_DATE DATE NOT NULL,
  PRIMARY KEY (RC_ID),
  FOREIGN KEY (AR_ID) REFERENCES ACTIVITY_RESOURCE(AR_ID) ON UPDATE CASCADE );

/* PROJECT ROWS 			*/  
INSERT INTO PROJECT VALUES (4,'Process Improvement',1,2);
INSERT INTO PROJECT VALUES (7,'NM Fashion Migration',1,2);
INSERT INTO PROJECT VALUES (9,'Apple TV Project',1,15);

/* ACTIVITY ROWS			*/
INSERT INTO ACTIVITY VALUES (7,4,'Create Procedures','2017-01-01',7,8,0,NULL,'2017-01-01','2017-01-11',1);
INSERT INTO ACTIVITY VALUES (8,4,'Create Software','2017-01-08',7,9,0,NULL,'2017-01-09','2017-01-16',1);
INSERT INTO ACTIVITY VALUES (9,4,'Implement Procedures and Software','2017-01-15',14,10,0,NULL,'2017-01-16','0000-00-00',0.1);
INSERT INTO ACTIVITY VALUES (14,7,'Create Test Cases','2017-01-26',7,15,0,NULL,'2017-01-26','2017-02-12',1);
INSERT INTO ACTIVITY VALUES (15,7,'Move to live site','2017-03-02',7,16,0,NULL,NULL,NULL,NULL);
INSERT INTO ACTIVITY VALUES (16,7,'Fix bugs as they appear','2017-03-09',28,17,0,NULL,NULL,NULL,NULL);
INSERT INTO ACTIVITY VALUES (17,7,'Identify Errors','2017-02-02',7,15,0,1,'2017-02-09','0000-00-00',0.5);
INSERT INTO ACTIVITY VALUES (18,7,'Fix Errors','2017-02-09',21,15,0,2,NULL,NULL,NULL);
INSERT INTO ACTIVITY VALUES (28,9,'Create Completed Project','2017-01-24',63,27,0,NULL,'2017-01-24','0000-00-00',0.2);
INSERT INTO ACTIVITY VALUES (29,9,'Template Dashboard Ready','2017-02-09',1,27,0,1,NULL,NULL,NULL);

/* RESOURCE ROWS 			*/
INSERT INTO RESOURCE VALUES (3,4,'Programming','Hour',100);
INSERT INTO RESOURCE VALUES (4,4,'Consulting','Hour',200);
INSERT INTO RESOURCE VALUES (10,9,'Programming','Hour',15);
INSERT INTO RESOURCE VALUES (12,7,'Development','Hour',15);

/* ACTIVITY RESROUCE ROWS   */
INSERT INTO ACTIVITY_RESOURCE VALUES (9,8,3,NULL,40);
INSERT INTO ACTIVITY_RESOURCE VALUES (10,9,3,NULL,40);
INSERT INTO ACTIVITY_RESOURCE VALUES (8,7,4,NULL,40);
INSERT INTO ACTIVITY_RESOURCE VALUES (11,9,4,NULL,40);
INSERT INTO ACTIVITY_RESOURCE VALUES (25,28,10,NULL,86);
INSERT INTO ACTIVITY_RESOURCE VALUES (26,14,12,NULL,5);
INSERT INTO ACTIVITY_RESOURCE VALUES (27,17,12,NULL,5);
INSERT INTO ACTIVITY_RESOURCE VALUES (28,18,12,NULL,15);
INSERT INTO ACTIVITY_RESOURCE VALUES (29,15,12,NULL,5);
INSERT INTO ACTIVITY_RESOURCE VALUES (30,16,12,NULL,20);

/* RESROUCE_CONSUME ROWS    */
INSERT INTO RESOURCE_CONSUME VALUES  (13,9,9,'2017-01-05');
INSERT INTO RESOURCE_CONSUME VALUES  (12,8,1,'2017-01-08');
INSERT INTO RESOURCE_CONSUME VALUES  (15,8,9,'2017-01-09');
INSERT INTO RESOURCE_CONSUME VALUES  (14,11,1,'2017-01-09');
INSERT INTO RESOURCE_CONSUME VALUES  (19,25,16,'2017-02-02');
INSERT INTO RESOURCE_CONSUME VALUES  (20,25,0.5,'2017-02-02');
INSERT INTO RESOURCE_CONSUME VALUES  (35,25,3,'2017-02-09');
INSERT INTO RESOURCE_CONSUME VALUES  (36,25,3,'2017-02-09');
INSERT INTO RESOURCE_CONSUME VALUES  (21,26,1,'2017-01-26');
INSERT INTO RESOURCE_CONSUME VALUES  (22,26,1,'2017-01-26');
INSERT INTO RESOURCE_CONSUME VALUES  (37,28,0.5,'2017-02-16');


/* QUERY AS REQUESTED	*/
SELECT PROJECT_NAME, ACTIVITY_NAME, SUM(COST_PER_UNIT * PLAN_QUANTITY) AS 'ESTIMATED COST', START_DATE AS 'PLANNED START DATE', START_DATE + INTERVAL PLAN_DURATION DAY AS 'PLANNED FINISH DATE'
FROM PROJECT, ACTIVITY, RESOURCE, ACTIVITY_RESOURCE
WHERE PROJECT.PROJECT_ID = ACTIVITY.PROJECT_ID
AND ACTIVITY.ACTIVITY_ID = ACTIVITY_RESOURCE.ACTIVITY_ID
AND RESOURCE.RESOURCE_ID = ACTIVITY_RESOURCE.RESOURCE_ID
GROUP BY ACTIVITY.ACTIVITY_ID;