Scrum MYSQL Script

CREATE SCHEMA `android` ;
==================

CREATE TABLE `android`.`PROJECT` (
  `PRJID` INT NOT NULL COMMENT 'Project ID for Projects',
  `PRJNAME` VARCHAR(45) NOT NULL COMMENT 'Project Name',
  `ESCALATION1` VARCHAR(45) NULL COMMENT 'Escalation 1',
  `ESCALATION2` VARCHAR(45) NULL COMMENT 'Escalation 2',
  `ESCALATION3` VARCHAR(45) NULL COMMENT 'Escalation 3',
  `SERVICEOWNERNAME` VARCHAR(45) NULL COMMENT 'Service Owner Name',
  `COUNTRY` VARCHAR(45) NULL COMMENT 'Country',
  PRIMARY KEY (`PRJID`))
COMMENT = 'PROJECT Table';

ALTER TABLE `android`.`project`
CHANGE COLUMN `PRJID` `PRJID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Project ID for Projects' ;



===============================================================================

CREATE TABLE `android`.`TEAMLEAD` (
  `TLID` INT NOT NULL COMMENT 'Team Lead Id',
  `TLNAME` VARCHAR(45) NOT NULL COMMENT 'Team Lead Name',
  `TKID` INT NOT NULL COMMENT 'Task Id PK to Task table',
  `TOTAL_REPORTIES` VARCHAR(45) NULL COMMENT 'Total Number of Reporties',
  `ESCALATION1` VARCHAR(45) NULL COMMENT 'Escalation 1',
  PRIMARY KEY (`TLID`))
COMMENT = 'TEAMLEAD Table';

ALTER TABLE `android`.`teamlead`
CHANGE COLUMN `TLID` `TLID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Team Lead Id' ;


================================================================================


CREATE TABLE `android`.`TEAMMEMBER` (
  `TMEMID` INT NOT NULL COMMENT 'TEAM Member ID',
  `TMEMNAME` VARCHAR(45) NOT NULL COMMENT 'TEAM Member NAME',
  `TMEMEMAILID` VARCHAR(45) NULL COMMENT 'TEAM Member EMAILID',
  `TMEMPHONE` VARCHAR(45) NULL COMMENT 'TEAM Member PHONE',
  `TLID` INT NOT NULL COMMENT 'Team Lead ID PK to TeamLead Table',
  PRIMARY KEY (`TMEMID`))
COMMENT = 'TEAMMEMBER Table';

ALTER TABLE `android`.`teammember`
CHANGE COLUMN `TMEMID` `TMEMID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'TEAM Member ID' ;


===============================================================================


CREATE TABLE `android`.`LOGIN` (
  `LOGINID` INT NOT NULL COMMENT 'Login ID of User',
  `USERNAME` VARCHAR(45) NOT NULL COMMENT 'Login User Name',
  `USERPASSWORD` VARCHAR(45) NULL COMMENT 'Login User Password',
  `PRJID` INT NOT NULL COMMENT 'Login User Projet Id',
  PRIMARY KEY (`LOGINID`))
COMMENT = 'LOGIN Table';

ALTER TABLE `android`.`login`
CHANGE COLUMN `LOGINID` `LOGINID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Login ID of User' ;

===============================================================================


CREATE TABLE `android`.`TASK` (
  `TKID` INT NOT NULL COMMENT 'Task ID OF USER',
  `TKNAME` VARCHAR(45) NOT NULL COMMENT 'Task NAME',
  `STATUS` VARCHAR(45) NULL COMMENT 'Task STATUS',
  `TMEMID` INT NOT NULL COMMENT 'Task Start Date',
  `SPRINTID` INT NOT NULL COMMENT 'Task End Date',
  `TASKSTARTDATE` VARCHAR(45) NULL COMMENT 'ID of Task assigned to team member',
  `TASKENDDATE` VARCHAR(45) NULL COMMENT 'Task sprint Id  PK to Scrum',
  PRIMARY KEY (`TKID`))
COMMENT = 'TASK Table';

ALTER TABLE `android`.`task`
CHANGE COLUMN `TKID` `TKID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Task ID OF USER' ;

===============================================================================

CREATE TABLE `android`.`SCRUM` (
  `SCRUMID` INT NOT NULL COMMENT 'Scrum ID OF USER',
  `SCRUMNAME` VARCHAR(45) NOT NULL COMMENT 'Scrum NAME',
  `SCRUMDESCRIPTION` VARCHAR(45) NULL COMMENT 'Scrum Descriptions',
  `PRJID` INT NOT NULL COMMENT 'Project Id PK to Project',
  PRIMARY KEY (`SCRUMID`))
COMMENT = 'SCRUM Table';

ALTER TABLE `android`.`scrum`
CHANGE COLUMN `SCRUMID` `SCRUMID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Scrum ID OF USER' ;

===============================================================================


CREATE TABLE `android`.`SPRINT` (
  `SPRINTID` INT NOT NULL COMMENT 'SPRINT ID OF USER',
  `SPRINTNAME` VARCHAR(45) NOT NULL COMMENT 'SPRINT NAME',
  `SCRUMID` INT NOT NULL COMMENT 'SPRINT scrum Id PK to Scrum Table',
  `SPRINTSTARTDATE` VARCHAR(45) NOT NULL COMMENT 'SPRINT start Date',
  `SPRINTENDDATE` VARCHAR(45) NOT NULL COMMENT 'SPRINT end Date',
  PRIMARY KEY (`SPRINTID`))
COMMENT = 'SPRINT Table';


ALTER TABLE `android`.`sprint`
CHANGE COLUMN `SPRINTID` `SPRINTID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SPRINT ID OF USER' ;

===============================================================================

CREATE TABLE `android`.`RETROSPECT` (
  `RETID` INT NOT NULL COMMENT 'Retrospect ID',
  `COMMENTS` VARCHAR(45) NOT NULL COMMENT 'Retrospect Comments',
  `SPRINTNAME` VARCHAR(45) NOT NULL COMMENT 'Retrospect Sprint name',
  `SPRINTID` INT NOT NULL COMMENT 'Retrospect Team Sprint Id PK to Sprint',
  PRIMARY KEY (`RETID`))
COMMENT = 'RETROSPECT Table';

ALTER TABLE `android`.`retrospect`
CHANGE COLUMN `RETID` `RETID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Retrospect ID' ;

===========================================================================================

CREATE TABLE `android`.`KPI` (
  `KPIID` INT NOT NULL COMMENT 'KPIID ID',
  `SPRINTID` VARCHAR(45) NOT NULL COMMENT 'KPI Sprint ID',
  `SPRINTNAME` VARCHAR(45) NOT NULL COMMENT 'KPI Sprint name',
  `DEFECTDENSITY` INT NOT NULL COMMENT 'KPI Defect Density',
  `SCHEDULE_SLIPPAGE` INT NOT NULL COMMENT 'KPI Schedule Sclippage',
  `DESIGN_REVIEW_COVERAGE` INT NOT NULL COMMENT 'KPI Design review Coverage',
  `CODE_REVIEW_COVERAGE` INT NOT NULL COMMENT 'KPI Code Review Coverage',
  `TEST_CASE_COVERAGE` INT NOT NULL COMMENT 'KPI Test Case Coverage',
  PRIMARY KEY (`KPIID`))
COMMENT = 'KPI Table';

ALTER TABLE `android`.`kpi`
CHANGE COLUMN `KPIID` `KPIID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'KPIID ID' ;

====================================
CREATE TABLE `android`.`SCRUMROLE` (
  `SRID` INT NOT NULL COMMENT 'SCRUMROLE ID',
  `SRNAME` VARCHAR(45) NOT NULL COMMENT 'SCRUMROLE Name',
  PRIMARY KEY (`SRID`))
COMMENT = 'SCRUMROLE Table';

ALTER TABLE `android`.`scrumrole`
CHANGE COLUMN `SRID` `SRID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SCRUMROLE ID' ;

==========================

CREATE TABLE `android`.`SCRUMROLERESPONSIBILITY` (
  `SRRID` INT NOT NULL COMMENT 'SCRUMROLERESPONSIBILITY ID',
  `SRID` INT NOT NULL COMMENT 'SCRUMROLERESPONSIBILITY Scrum Role ID',
  `RR` VARCHAR(45) NOT NULL COMMENT 'SCRUMROLERESPONSIBILITY Responsibilities',
  PRIMARY KEY (`SRRID`))
COMMENT = 'SCRUMROLERESPONSIBILITY Table';

ALTER TABLE `android`.`scrumroleresponsibility`
CHANGE COLUMN `SRRID` `SRRID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SCRUMROLERESPONSIBILITY ID' ;

=================================

CREATE TABLE `android`.`CHECKLIST` (
  `CLID` INT NOT NULL COMMENT 'CHECKLIST ID',
  `CLLID` INT NOT NULL COMMENT 'CHECKLIST Item Id',
  `CLDESCRIPTION` VARCHAR(45) NOT NULL COMMENT 'CHECKLIST Descriptions',
  PRIMARY KEY (`CLID`))
COMMENT = 'CHECKLIST Table';

ALTER TABLE `android`.`checklist`
CHANGE COLUMN `CLID` `CLID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'CHECKLIST ID' ;

=====================
CREATE TABLE `android`.`CHECKLISTITEM` (
  `CLIID` INT NOT NULL COMMENT 'CHECKLISTIteam ID',
  `CLIDESCRIPTION` VARCHAR(45) NOT NULL COMMENT 'CHECKLISTIteam Descriptions',
  PRIMARY KEY (`CLIID`))
COMMENT = 'CHECKLISTITEM Table';

ALTER TABLE `android`.`checklistitem`
CHANGE COLUMN `CLIID` `CLIID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'CHECKLISTIteam ID' ;

===========================

CREATE TABLE `android`.`SCRUMKNOWLEDGE` (
  `SKID` INT NOT NULL COMMENT 'SCRUMKNOWLEDGE ID',
  `TOPICID` INT NOT NULL COMMENT 'SCRUMKNOWLEDGE Topic ID',
  `TOPICDESCRIPTIONS` VARCHAR(45) NOT NULL COMMENT 'SCRUMKNOWLEDGE Topic Descriptions',
  PRIMARY KEY (`SKID`))
COMMENT = 'SCRUMKNOWLEDGE Table';

ALTER TABLE `android`.`scrumknowledge`
CHANGE COLUMN `SKID` `SKID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'SCRUMKNOWLEDGE ID' ;

=====================================

INSERT INTO `android`.`login` (`LOGINID`, `USERNAME`, `USERPASSWORD`, `PRJID`) VALUES ('1', 'un', 'up', '1');

INSERT INTO `ANDROID`.`PROJECT` (PRJID, PRJNAME, ESCALATION1, ESCALATION2, ESCALATION3, SERVICEOWNERNAME, COUNTRY) VALUES ('1', 'projectname1', 'eescallation1', 'escallation2', 'escallation3', 'serviceownwername1', 'c1');

INSERT INTO `ANDROID`.`RETROSPECT` (RETID, COMMENTS, SPRINTNAME, SPRINTID) VALUES ('1', 'ret1comments', 'sprint1', '1');
INSERT INTO `ANDROID`.`RETROSPECT` (RETID, COMMENTS, SPRINTNAME, SPRINTID) VALUES ('2', 'ret2comments', 'sprint2', '2');
INSERT INTO `ANDROID`.`RETROSPECT` (RETID, COMMENTS, SPRINTNAME, SPRINTID) VALUES ('3', 'ret3comments', 'sprint3', '3');


INSERT INTO `ANDROID`.`SCRUM` (SCRUMID, SCRUMNAME, SCRUMDESCRIPTION, PRJID) VALUES ('1', 'scrumname1', 'scrum1description', '1');
INSERT INTO `ANDROID`.`SCRUM` (SCRUMID, SCRUMNAME, SCRUMDESCRIPTION, PRJID) VALUES ('2', 'sscrumname2', 'scrum2description', '1');

INSERT INTO `ANDROID`.`SPRINT` (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('1', 'sprint1', '1', '18-04-2016', '6-05-2016')
INSERT INTO `ANDROID`.`SPRINT` (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('2', 'sprint2', '1', '18-04-2016', '6-05-2016')
INSERT INTO `ANDROID`.`SPRINT` (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('3', 'sprint3', '1', '18-04-2016', '6-05-2016')

INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('1', 'taskname1', 'complete', '1', '1', '18-04-2016', '19-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('2', 'taskname1', 'inprogress', '2', '1', '27-04-2016', '30-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('3', 'taskname1', 'open', '3', '1', '3-05-2016', '5-05-2016')

INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('4', 'taskname2', 'complete', '1', '2', '18-04-2016', '19-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('5', 'taskname2', 'inprogress', '2', '2', '27-04-2016', '30-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('6', 'taskname2', 'open', '3', '2', '3-05-2016', '5-05-2016')

INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('7', 'taskname3', 'complete', '1', '3', '18-04-2016', '19-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('8', 'taskname3', 'inprogress', '2', '3', '27-04-2016', '30-04-2016')
INSERT INTO `ANDROID`.`TASK` (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('9', 'taskname3', 'open', '3', '3', '3-05-2016', '5-05-2016')

INSERT INTO `ANDROID`.`TEAMLEAD` (TLID, TLNAME, TKID, TOTAL_REPORTIES, ESCALATION1) VALUES ('1', 'teamlead1', '1', '5', 'escallationteamlead1');
INSERT INTO `ANDROID`.`TEAMLEAD` (TLID, TLNAME, TKID, TOTAL_REPORTIES, ESCALATION1) VALUES ('2', 'teamlead2', '2', '5', 'escallationteamlead1');
INSERT INTO `ANDROID`.`TEAMLEAD` (TLID, TLNAME, TKID, TOTAL_REPORTIES, ESCALATION1) VALUES ('3', 'teamlead3', '3', '5', 'escallationteamlead1');



INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('1', 'teammember1', 'teammember1@test.com', '123456', '1')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('2', 'teammember2', 'teammember2@test.com', '5678902', '1')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('3', 'teammember3', 'teammember3@test.com', '22345', '2')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('4', 'teammember4', 'teammember4@test.com', '33222', '2')
INSERT INTO `ANDROID`.`TEAMMEMBER` (TMEMID, TMEMNAME, TMEMEMAILID, TMEMPHONE, TLID) VALUES ('5', 'teammember5', 'teammember5@test.com', '44433', '3')


INSERT INTO `ANDROID`.`KPI` (KPIID, SPRINTID, SPRINTNAME, DEFECTDENSITY, SCHEDULE_SLIPPAGE, DESIGN_REVIEW_COVERAGE, CODE_REVIEW_COVERAGE, TEST_CASE_COVERAGE) VALUES ('1', '1', 'sprint1', '1', '1', '1', '1', '1')
INSERT INTO `ANDROID`.`KPI` (KPIID, SPRINTID, SPRINTNAME, DEFECTDENSITY, SCHEDULE_SLIPPAGE, DESIGN_REVIEW_COVERAGE, CODE_REVIEW_COVERAGE, TEST_CASE_COVERAGE) VALUES ('2', '2', 'sprint2', '22', '21', '21', '21', '2')
INSERT INTO `ANDROID`.`KPI` (KPIID, SPRINTID, SPRINTNAME, DEFECTDENSITY, SCHEDULE_SLIPPAGE, DESIGN_REVIEW_COVERAGE, CODE_REVIEW_COVERAGE, TEST_CASE_COVERAGE) VALUES ('3', '3', 'sprint3', '3', '33', '3', '31', '32')
INSERT INTO `ANDROID`.`SCRUMROLE` (SRID, SRNAME) VALUES ('1', 'PRODUCT OWNER')
INSERT INTO `ANDROID`.`SCRUMROLE` (SRID, SRNAME) VALUES ('2', 'SCRUMMASTER')
INSERT INTO `ANDROID`.`SCRUMROLE` (SRID, SRNAME) VALUES ('3', 'DEVELOPMENTTEAM')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('1', '1', 'RR1')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('2', '1', 'RR2')

INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('3', '2', 'RR1')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('4', '2', 'RR2')

INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('5', '3', 'RR1')
INSERT INTO `ANDROID`.`SCRUMROLERESPONSIBILITY` (SRRID, SRID, RR) VALUES ('6', '3', 'RR2')


INSERT INTO `ANDROID`.`CHECKLIST` (CLID, CLLID, CLDESCRIPTION) VALUES ('1', '1', 'CL1')
INSERT INTO `ANDROID`.`CHECKLIST` (CLID, CLLID, CLDESCRIPTION) VALUES ('2', '1', 'CL2')

INSERT INTO `ANDROID`.`SCRUMKNOWLEDGE` (SKID, TOPICID, TOPICDESCRIPTIONS) VALUES ('1', '1', 'topic1')
INSERT INTO `ANDROID`.`SCRUMKNOWLEDGE` (SKID, TOPICID, TOPICDESCRIPTIONS) VALUES ('2', '1', 'topic2')

INSERT INTO `ANDROID`.`CHECKLISTITEM` (CLIID, CLIDESCRIPTION) VALUES ('1', 'Item 1')
INSERT INTO `ANDROID`.`CHECKLISTITEM` (CLIID, CLIDESCRIPTION) VALUES ('2', 'Item 2')