Scrum Project SQL Script

CREATE TABLE PROJECT 
(
  PRJID NUMBER NOT NULL 
, PRJNAME VARCHAR2(20) NOT NULL 
, ESCALATION1 VARCHAR2(20) 
, ESCALATION2 VARCHAR2(20) 
, ESCALATION3 VARCHAR2(20) 
, SERVICEOWNERNAME VARCHAR2(20) NOT NULL 
, COUNTRY VARCHAR2(20) NOT NULL 
, CONSTRAINT PROJECT_PK PRIMARY KEY 
  (
    PRJID 
  )
  ENABLE 
);

COMMENT ON COLUMN PROJECT.PRJID IS 'Project ID for Projects';

COMMENT ON COLUMN PROJECT.PRJNAME IS 'Project Name';

COMMENT ON COLUMN PROJECT.ESCALATION1 IS 'Escalation 1';

COMMENT ON COLUMN PROJECT.ESCALATION2 IS 'Escalation 2';

COMMENT ON COLUMN PROJECT.ESCALATION3 IS 'Escalation 3';

COMMENT ON COLUMN PROJECT.SERVICEOWNERNAME IS 'Service Owner Name';

COMMENT ON COLUMN PROJECT.COUNTRY IS 'Country';

===============================================================================
CREATE TABLE TEAMLEAD 
(
  TLID NUMBER NOT NULL 
, TLNAME VARCHAR2(20) NOT NULL 
, TKID NUMBER  NOT NULL 
, TOTAL_REPORTIES NUMBER NOT NULL 
, ESCALATION1 VARCHAR2(20) 
, CONSTRAINT TEAMLEAD_PK PRIMARY KEY 
  (
    TLID 
  )
  ENABLE 
);

COMMENT ON COLUMN TEAMLEAD.TLID IS 'Team Lead Id';

COMMENT ON COLUMN TEAMLEAD.TLNAME IS 'Team Lead Name';

COMMENT ON COLUMN TEAMLEAD.TKID IS 'Task Id PK to Task table';

COMMENT ON COLUMN TEAMLEAD.TOTAL_REPORTIES IS 'Total Number of Reporties';

COMMENT ON COLUMN TEAMLEAD.ESCALATION1 IS 'ESCALATION 1';

===============================================================================
CREATE TABLE TEAMMEMBER 
(
  TMEMID NUMBER NOT NULL 
, TMEMNAME VARCHAR2(20) NOT NULL 
, TMEMEMAILID VARCHAR2(20) NOT NULL 
, TMEMPHONE VARCHAR2(20) NOT NULL 
, TLID NUMBER  NOT NULL 
, CONSTRAINT TEAMMEMBER_PK PRIMARY KEY 
  (
    TMEMID 
  )
  ENABLE 
);

COMMENT ON COLUMN TEAMMEMBER.TMEMID IS 'TEAM Member ID';

COMMENT ON COLUMN TEAMMEMBER.TMEMNAME IS 'TEAM Member NAME';

COMMENT ON COLUMN TEAMMEMBER.TMEMEMAILID IS 'TEAM Member EMAILID';

COMMENT ON COLUMN TEAMMEMBER.TMEMPHONE IS 'TEAM Member PHONE';

COMMENT ON COLUMN TEAMMEMBER.TLID IS 'Team Lead ID PK to TeamLead Table';



===============================================================================
CREATE TABLE LOGIN 
(
  LOGINID NUMBER NOT NULL 
, USERNAME VARCHAR2(20) NOT NULL 
, USERPASSWORD VARCHAR2(20) NOT NULL 
, PRJID NUMBER  NOT NULL 
, CONSTRAINT LOGIN_PK PRIMARY KEY 
  (
    USERNAME 
  )
  ENABLE 
);

COMMENT ON COLUMN LOGIN.LOGINID IS 'Login ID of User';

COMMENT ON COLUMN LOGIN.USERNAME IS 'Login User Name';

COMMENT ON COLUMN LOGIN.USERPASSWORD IS 'Login User Password';

COMMENT ON COLUMN LOGIN.PRJID IS 'Login User Projet Id';

===============================================================================
CREATE TABLE TASK 
(
  TKID NUMBER NOT NULL 
, TKNAME VARCHAR2(20) NOT NULL 
, STATUS VARCHAR2(20) NOT NULL 
, TMEMID NUMBER  NOT NULL 
, SPRINTID NUMBER  NOT NULL 
, TASKSTARTDATE VARCHAR2(20) NOT NULL 
, TASKENDDATE VARCHAR2(20) NOT NULL 
, CONSTRAINT TASK_PK PRIMARY KEY 
  (
    TKID 
  )
  ENABLE 
);

COMMENT ON COLUMN TASK.TKID IS 'Task ID OF USER';

COMMENT ON COLUMN TASK.TKNAME IS 'Task NAME';

COMMENT ON COLUMN TASK.STATUS IS 'Task STATUS';

COMMENT ON COLUMN TASK.TASKSTARTDATE IS 'Task Start Date';

COMMENT ON COLUMN TASK.TASKENDDATE IS 'Task End Date';

COMMENT ON COLUMN TASK.TMEMID IS 'ID of Task assigned to team member';

COMMENT ON COLUMN TASK.SPRINTID IS 'Task sprint Id  PK to Scrum';


===============================================================================
CREATE TABLE SCRUM 
(
  SCRUMID NUMBER NOT NULL 
, SCRUMNAME VARCHAR2(20) NOT NULL 
, SCRUMDESCRIPTION VARCHAR2(20) NOT NULL 
, PRJID NUMBER  NOT NULL 
, CONSTRAINT SCRUM_PK PRIMARY KEY 
  (
    SCRUMID 
  )
  ENABLE 
);

COMMENT ON COLUMN SCRUM.SCRUMID IS 'Scrum ID OF USER';

COMMENT ON COLUMN SCRUM.SCRUMNAME IS 'Scrum NAME';

COMMENT ON COLUMN SCRUM.SCRUMDESCRIPTION IS 'Scrum Descriptions';

COMMENT ON COLUMN SCRUM.PRJID IS 'Project Id PK to Project';



===============================================================================
CREATE TABLE SPRINT 
(
  SPRINTID NUMBER NOT NULL 
, SPRINTNAME VARCHAR2(20) NOT NULL 
, SCRUMID NUMBER  NOT NULL 
, SPRINTSTARTDATE VARCHAR2(20) NOT NULL 
, SPRINTENDDATE VARCHAR2(20) NOT NULL 
, CONSTRAINT SPRINT_PK PRIMARY KEY 
  (
    SPRINTID 
  )
  ENABLE 
);

COMMENT ON COLUMN SPRINT.SPRINTID IS 'SPRINT ID OF USER';

COMMENT ON COLUMN SPRINT.SPRINTNAME IS 'SPRINT NAME';

COMMENT ON COLUMN SPRINT.SPRINTSTARTDATE IS 'SPRINT start date';

COMMENT ON COLUMN SPRINT.SPRINTENDDATE IS 'SPRINT end Date';

COMMENT ON COLUMN SPRINT.SCRUMID IS 'SPRINT scrum Id PK to Scrum Table';


===============================================================================
CREATE TABLE RETROSPECT 
(
  RETID NUMBER NOT NULL 
, COMMENTS  VARCHAR2(20) NOT NULL 
, SPRINTNAME  VARCHAR2(20) NOT NULL 
, SPRINTID NUMBER  NOT NULL 
, CONSTRAINT RETROSPECT_PK PRIMARY KEY 
  (
    RETID 
  )
  ENABLE 
);

COMMENT ON COLUMN RETROSPECT.RETID IS 'Retrospect ID';

COMMENT ON COLUMN RETROSPECT.COMMENTS IS 'Retrospect Comments';

COMMENT ON COLUMN RETROSPECT.SPRINTNAME IS 'Retrospect Sprint name';

COMMENT ON COLUMN RETROSPECT.SPRINTID IS 'Retrospect Team Sprint Id PK to Sprint';

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

CREATE TABLE KPI 
(
  KPIID NUMBER NOT NULL 
, SPRINTID  VARCHAR2(20) NOT NULL 
, SPRINTNAME  VARCHAR2(20) NOT NULL 
, DEFECTDENSITY NUMBER  NOT NULL 
, SCHEDULE_SLIPPAGE NUMBER  NOT NULL 
, DESIGN_REVIEW_COVERAGE NUMBER  NOT NULL 
, CODE_REVIEW_COVERAGE NUMBER  NOT NULL 
, TEST_CASE_COVERAGE NUMBER  NOT NULL 
, CONSTRAINT KPI_PK PRIMARY KEY 
  (
    KPIID 
  )
  ENABLE 
);

COMMENT ON COLUMN KPI.KPIID IS 'KPI ID';

COMMENT ON COLUMN KPI.SPRINTID IS 'KPI Sprint ID';

COMMENT ON COLUMN KPI.SPRINTNAME IS 'KPI Sprint name';

COMMENT ON COLUMN KPI.DEFECTDENSITY IS 'KPI Defect Density';

COMMENT ON COLUMN KPI.SCHEDULE_SLIPPAGE IS 'KPI Schedule Sclippage';

COMMENT ON COLUMN KPI.DESIGN_REVIEW_COVERAGE IS 'KPI Design Review';

COMMENT ON COLUMN KPI.CODE_REVIEW_COVERAGE IS 'KPI Code Review Coverage';

COMMENT ON COLUMN KPI.TEST_CASE_COVERAGE IS 'KPI Test Case Coverage';

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

CREATE TABLE SCRUMROLE 
(
  SRID NUMBER NOT NULL 
, SRNAME  VARCHAR2(20) NOT NULL 
, CONSTRAINT SCRUMROLE_PK PRIMARY KEY 
  (
    SRID 
  )
  ENABLE 
);

COMMENT ON COLUMN SCRUMROLE.SRID IS 'SCRUMROLE ID';

COMMENT ON COLUMN SCRUMROLE.SRNAME IS 'SCRUMROLE Name';

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

CREATE TABLE SCRUMROLERESPONSIBILITY 
(
  SRRID NUMBER NOT NULL
, SRID NUMBER NOT NULL   
, RR VARCHAR2(20) NOT NULL 
, CONSTRAINT SCRUMROLERESPONSIBILITY_PK PRIMARY KEY 
  (
    SRRID 
  )
  ENABLE 
);

COMMENT ON COLUMN SCRUMROLERESPONSIBILITY.SRRID IS 'SCRUMROLERESPONSIBILITY ID';

COMMENT ON COLUMN SCRUMROLERESPONSIBILITY.SRID IS 'SCRUMROLERESPONSIBILITY Scrum Role ID';

COMMENT ON COLUMN SCRUMROLERESPONSIBILITY.RR IS 'SCRUMROLERESPONSIBILITY Responsibilities';
=================================

CREATE TABLE CHECKLIST 
(
  CLID NUMBER NOT NULL
, CLLID NUMBER NOT NULL   
, CLDESCRIPTION VARCHAR2(20) NOT NULL 
, CONSTRAINT CHECKLIST_PK PRIMARY KEY 
  (
    CLID 
  )
  ENABLE 
);

COMMENT ON COLUMN CHECKLIST.CLID IS 'CHECKLIST ID';

COMMENT ON COLUMN CHECKLIST.CLLID IS 'CHECKLIST Item Id';

COMMENT ON COLUMN CHECKLIST.CLDESCRIPTION IS 'CHECKLIST Descriptions';


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


CREATE TABLE CHECKLISTITEM 
(
  CLIID NUMBER NOT NULL
, CLIDESCRIPTION VARCHAR2(20) NOT NULL 
, CONSTRAINT CHECKLISTITEM_PK PRIMARY KEY 
  (
    CLIID 
  )
  ENABLE 
);

COMMENT ON COLUMN CHECKLISTITEM.CLIID IS 'CHECKLISTIteam ID';


COMMENT ON COLUMN CHECKLISTITEM.CLIDESCRIPTION IS 'CHECKLISTIteam Descriptions';

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

CREATE TABLE SCRUMKNOWLEDGE 
(
  SKID NUMBER NOT NULL
, TOPICID NUMBER NOT NULL   
, TOPICDESCRIPTIONS VARCHAR2(20) NOT NULL 
, CONSTRAINT SCRUMKNOWLEDGE_PK PRIMARY KEY 
  (
    SKID 
  )
  ENABLE 
);

COMMENT ON COLUMN SCRUMKNOWLEDGE.SKID IS 'SCRUMKNOWLEDGE ID';

COMMENT ON COLUMN SCRUMKNOWLEDGE.TOPICID IS 'SCRUMKNOWLEDGE Topic ID';

COMMENT ON COLUMN SCRUMKNOWLEDGE.TOPICDESCRIPTIONS IS 'SCRUMKNOWLEDGE Topic Descriptions';
====================================



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', 'sserviceownwername1', '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-April-2016', '6-May-2016')
INSERT INTO "ANDROID"."SPRINT" (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('2', 'sprint2', '1', '18-April-2016', '6-May-2016')
INSERT INTO "ANDROID"."SPRINT" (SPRINTID, SPRINTNAME, SCRUMID, SPRINTSTARTDATE, SPRINTENDDATE) VALUES ('3', 'sprint3', '1', '18-April-2016', '6-May-2016')

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

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

INSERT INTO "ANDROID"."TASK" (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('7', 'taskname3', 'complete', '1', '3', '18-April-2016', '19-April-2016')
INSERT INTO "ANDROID"."TASK" (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('8', 'taskname3', 'inprogress', '2', '3', '27-April-2016', '30-April-2016')
INSERT INTO "ANDROID"."TASK" (TKID, TKNAME, STATUS, TMEMID, SPRINTID, TASKSTARTDATE, TASKENDDATE) VALUES ('9', 'taskname3', 'open', '3', '3', '3-May-2016', '5-May-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"."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')