BugTracking derby.txt

connect 'jdbc:derby: C:\bugtrackproject\bugtrackdb;create=true;user=hsbc;password=hsbc123';

connect 'jdbc:derby: C:\bugtrackproject\bugtrackdb;user=hsbc;password=hsbc123';

___________________________________________________(old) Creating tables_______________________________________________________

create table usertable (userid int PRIMARY KEY GENERATED ALWAYS AS IDENTITY(Start with 1, Increment by 1), name varchar(25), email varchar(30), type varchar(15));

create table logintable (email varchar(30) primary key, password varchar(20), userid int references usertable(userid));
alter table logintable add lastlogindate date;
alter table logintable add lastlogintime time;

create table projecttable(projectid int PRIMARY KEY GENERATED ALWAYS AS IDENTITY(Start with 100, Increment by 1), projectname varchar(20),description varchar(40), startdate date, status varchar(15), managerid int);

create table teamtable(projectid int references projecttable(projectid),userid int);

create table bugtable (uniqueid int PRIMARY KEY GENERATED ALWAYS AS IDENTITY(Start with 1000, Increment by 1), title varchar(20),description varchar(40), projectid int references projecttable(projectid), createdby int, opendate date,assignedto int, markedforclosing varchar(6), closedby int,closedon date, status varchar(10),severitylevel varchar(10));

create table projectmanager(managerid int primary key, noOfProjects int check (noOfProjects<=4));

create table developer(developerid int primary key, noOfProjects int check (noOfProjects<=1));

create table tester(testerid int primary key, noOfProjects int check (noOfProjects<=2));

--------------------------------------------------------------------------------- (old) insertion-------------------------------------------------------------------------------
insert into usertable values(DEFAULT,'harry','harry@gmail.com','project manager');

----------------------------------------------------------------------------------(NEW) QUERIES---------------------------------------------------------------------------------
alter table teamtable add managerid int;
alter table logintable add unique(userid);
alter table usertable add unique(email);

drop table developer;
drop table tester;
drop table projectmanager;

create table count(userid int primary key, name varchar(25), type varchar(15),noOfProjects int);

insert into usertable values(DEFAULT,'harry','harry@gmail.com','project manager')
insert into usertable values(DEFAULT,'RON','RON@gmail.com','TESTER');
insert into usertable values(default,'tom','tom@gmail.com','developer');
insert into usertable values(default,'draco','draco@gmail.com','developer');
insert into usertable values(default,'ginny','ginny@gmail.com','developer');

insert into logintable values ('harry@gmail.com','root',1,default,default);
insert into logintable values ('ron@gmail.com','root',2,default,default);
insert into logintable values ('tom@gmail.com','root',102,default,default);
insert into logintable values ('draco@gmail.com','root',103,default,default);
insert into logintable values ('ginny@gmail.com','root',301,default,default);

insert into projecttable values(default,'darkarts','defense against dark arts','10/01/2020','in-progress',1);
insert into projecttable values(default,'herbology','crying roots','10/01/2020','in-progress',1);

insert into teamtable values(100,2,1);
insert into teamtable values(100,102,1);
insert into teamtable values(100,103,1);
insert into teamtable values(101,2,1);

insert into bugtable values(default,'dementor','expecto patronum',100,1,'10/01/2020',102,'false',null,null,'open','critical');
insert into bugtable values(default,'noise','ear muffs',101,1,'10/01/2020',103,'false',null,null,'open','critical');

insert into count values(1,'harry','project manager',2);
insert into count values(2,'ron','tester',2);
insert into count values(102,'tom','developer',1);
insert into count values(103,'draco','developer',1);
insert into count values(301,'ginny','developer',1);

alter table bugtable add remarks varchar(40);