Begin with the tables you created in Homework 4. Create a query that displays the following information for the project with PROJECT_ID=4. Pretend that the current date is 2017-01-20

1.       Activity Name

2. Planned Start Date (Start)
3. Planned Finish Date (Start plus Plan Duration)
4. Percent Complete
5. Planned Percent Complete (percentage of duration that has passed, assuming the current date is 2017-01-20)
6. Earned Value (Estimated Cost multiplied by Percent Complete)
7. Actual Cost (sum of quantity in Resource Consume multiplied by Cost Per Unit in Resource)
8. Planned Value (Planned Percent Complete multiplied by Estimated Cost)
9. Cost Variance (Earned Value minus Actual Cost)
10. Schedule Variance (Earned Value minus Planned Value)
11. Cost Performance Index (Earned Value divided by Actual Cost)
12. Schedule Performance Index (Earned Value divided by Planned Value)

 

HOMEWORK 4:

 

CREATETABLE PROJECT

(

  PROJECT_ID SMALLINTNOTNULL,

  PROJECT_NAME VARCHAR(35)NOTNULL,

  WBS_FINALIZED BOOLEANNOTNULL,

  WP_ID SMALLINTNOTNULL,

PRIMARYKEY(PROJECT_ID),

UNIQUEKEY(PROJECT_ID));

 

CREATETABLE ACTIVITY

(

  ACTIVITY_ID SMALLINTNOTNULLUNIQUE,

  PROJECT_ID SMALLINTNOTNULL,

  ACTIVITY_NAME VARCHAR(50)NOTNULL,

  START_DATE DATENOTNULL,

  PLAN_DURATION SMALLINTNOTNULL,

  WBS_ID SMALLINTNOTNULL,

  PARENT_ID SMALLINT,

  DISPLAY_ORDER INT,

  ACTUAL_START DATE,

  ACTUAL_END DATE,

  PERCENT_COMPLETE DECIMAL(3,1),

PRIMARYKEY(ACTIVITY_ID),

FOREIGNKEY(PROJECT_ID)REFERENCES PROJECT(PROJECT_ID)ONUPDATECASCADE);

 

CREATETABLERESOURCE

(

  RESOURCE_ID SMALLINTNOTNULLUNIQUE,

  PROJECT_ID SMALLINTNOTNULL,

  RESROUCE_NAME VARCHAR(20)NOTNULL,

  MEASUREMENT VARCHAR(10),

  COST_PER_UNIT INTNOTNULL,

PRIMARYKEY(RESOURCE_ID),

FOREIGNKEY(PROJECT_ID)REFERENCES PROJECT(PROJECT_ID)ONUPDATECASCADE);

 

CREATETABLE ACTIVITY_RESOURCE

(

  AR_ID SMALLINTNOTNULLUNIQUE,

  ACTIVITY_ID SMALLINTNOTNULL,

  RESOURCE_ID SMALLINTNOTNULL,

  DESCRIPTION VARCHAR(35),

  PLAN_QUANTITY SMALLINTNOTNULL,

PRIMARYKEY(AR_ID),

FOREIGNKEY(ACTIVITY_ID)REFERENCES ACTIVITY(ACTIVITY_ID)ONUPDATECASCADE,

FOREIGNKEY(RESOURCE_ID)REFERENCESRESOURCE(RESOURCE_ID)ONUPDATECASCADE);

 

CREATETABLE RESOURCE_CONSUME

(

  RC_ID SMALLINTNOTNULLUNIQUE,

  AR_ID SMALLINTNOTNULL,

  QUANTITY DECIMAL(9,1)NOTNULL,

  CONSUME_DATE DATENOTNULL,

PRIMARYKEY(RC_ID),

FOREIGNKEY(AR_ID)REFERENCES ACTIVITY_RESOURCE(AR_ID)ONUPDATECASCADE);

 

/* PROJECT ROWS               */

INSERTINTO PROJECT VALUES(4,'Process Improvement',1,2);

INSERTINTO PROJECT VALUES(7,'NM Fashion Migration',1,2);

INSERTINTO PROJECT VALUES(9,'Apple TV Project',1,15);

 

/* ACTIVITY ROWS              */

INSERTINTO ACTIVITY VALUES(7,4,'Create Procedures','2017-01-01',7,8,0,NULL,'2017-01-01','2017-01-11',1);

INSERTINTO ACTIVITY VALUES(8,4,'Create Software','2017-01-08',7,9,0,NULL,'2017-01-09','2017-01-16',1);

INSERTINTO ACTIVITY VALUES(9,4,'Implement Procedures and Software','2017-01-15',14,10,0,NULL,'2017-01-16','0000-00-00',0.1);

INSERTINTO ACTIVITY VALUES(14,7,'Create Test Cases','2017-01-26',7,15,0,NULL,'2017-01-26','2017-02-12',1);

INSERTINTO ACTIVITY VALUES(15,7,'Move to live site','2017-03-02',7,16,0,NULL,NULL,NULL,NULL);

INSERTINTO ACTIVITY VALUES(16,7,'Fix bugs as they appear','2017-03-09',28,17,0,NULL,NULL,NULL,NULL);

INSERTINTO ACTIVITY VALUES(17,7,'Identify Errors','2017-02-02',7,15,0,1,'2017-02-09','0000-00-00',0.5);

INSERTINTO ACTIVITY VALUES(18,7,'Fix Errors','2017-02-09',21,15,0,2,NULL,NULL,NULL);

INSERTINTO ACTIVITY VALUES(28,9,'Create Completed Project','2017-01-24',63,27,0,NULL,'2017-01-24','0000-00-00',0.2);

INSERTINTO ACTIVITY VALUES(29,9,'Template Dashboard Ready','2017-02-09',1,27,0,1,NULL,NULL,NULL);

 

/* RESOURCE ROWS              */

INSERTINTORESOURCEVALUES(3,4,'Programming','Hour',100);

INSERTINTORESOURCEVALUES(4,4,'Consulting','Hour',200);

INSERTINTORESOURCEVALUES(10,9,'Programming','Hour',15);

INSERTINTORESOURCEVALUES(12,7,'Development','Hour',15);

 

/* ACTIVITY RESROUCE ROWS   */

INSERTINTO ACTIVITY_RESOURCE VALUES(9,8,3,NULL,40);

INSERTINTO ACTIVITY_RESOURCE VALUES(10,9,3,NULL,40);

INSERTINTO ACTIVITY_RESOURCE VALUES(8,7,4,NULL,40);

INSERTINTO ACTIVITY_RESOURCE VALUES(11,9,4,NULL,40);

INSERTINTO ACTIVITY_RESOURCE VALUES(25,28,10,NULL,86);

INSERTINTO ACTIVITY_RESOURCE VALUES(26,14,12,NULL,5);

INSERTINTO ACTIVITY_RESOURCE VALUES(27,17,12,NULL,5);

INSERTINTO ACTIVITY_RESOURCE VALUES(28,18,12,NULL,15);

INSERTINTO ACTIVITY_RESOURCE VALUES(29,15,12,NULL,5);

INSERTINTO ACTIVITY_RESOURCE VALUES(30,16,12,NULL,20);

 

/* RESROUCE_CONSUME ROWS    */

INSERTINTO RESOURCE_CONSUME VALUES(13,9,9,'2017-01-05');

INSERTINTO RESOURCE_CONSUME VALUES(12,8,1,'2017-01-08');

INSERTINTO RESOURCE_CONSUME VALUES(15,8,9,'2017-01-09');

INSERTINTO RESOURCE_CONSUME VALUES(14,11,1,'2017-01-09');

INSERTINTO RESOURCE_CONSUME VALUES(19,25,16,'2017-02-02');

INSERTINTO RESOURCE_CONSUME VALUES(20,25,0.5,'2017-02-02');

INSERTINTO RESOURCE_CONSUME VALUES(35,25,3,'2017-02-09');

INSERTINTO RESOURCE_CONSUME VALUES(36,25,3,'2017-02-09');

INSERTINTO RESOURCE_CONSUME VALUES(21,26,1,'2017-01-26');

INSERTINTO RESOURCE_CONSUME VALUES(22,26,1,'2017-01-26');

INSERTINTO 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 DAYAS'PLANNED FINISH DATE'

FROM PROJECT, ACTIVITY,RESOURCE, ACTIVITY_RESOURCE

WHERE PROJECT.PROJECT_ID = ACTIVITY.PROJECT_ID

AND ACTIVITY.ACTIVITY_ID = ACTIVITY_RESOURCE.ACTIVITY_ID

ANDRESOURCE.RESOURCE_ID = ACTIVITY_RESOURCE.RESOURCE_ID

GROUPBY ACTIVITY.ACTIVITY_ID;