TeamFormationAssistant/SE_TABLES.sql at master · jiangyy12/TeamFormationAssistant · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
USE teamformationassistant;

DROP TABLE IF EXISTS Member;
DROP TABLE IF EXISTS Project;
DROP TABLE IF EXISTS Team;
DROP TABLE IF EXISTS Requirements;

CREATE TABLE Member(
	MemberId				INT NOT NULL AUTO_INCREMENT,
	MemberName					VARCHAR(1000) NOT NULL,
	DOB						DATE,
	Languages				VARCHAR(1000) NOT NULL,
	IsAssigned				INT,
	HourlyRate				FLOAT,
	MemberRole				VARCHAR(1000),
	Experience				INT,
	SkillScore				INT,
	AvailableHoursPerWeek	INT,
	PRIMARY KEY (MemberId)
);

CREATE TABLE Project(
	ProjectId				INT NOT NULL AUTO_INCREMENT,
	ProjectName					VARCHAR(1000) NOT NULL,
	ProjectEndDate					DATE,
	ProjectTeamSize					INT,
	Budget					FLOAT,
	Tools					VARCHAR(1000),
	IsAssignmentComplete	INT,
	Priority				INT,
	PRIMARY KEY (ProjectId)
);

CREATE TABLE Team(
	ProjectId	INT NOT NULL REFERENCES Member(MemberId),
	ProjectName varchar(1000),
	MemberId	INT NOT NULL REFERENCES Project(ProjectId),
    	MemberName		VARCHAR(1000) NOT NULL
);

CREATE TABLE Requirements(
	JobId					INT NOT NULL AUTO_INCREMENT,
	ProjectId				INT NOT NULL REFERENCES Project(ProjectId),
	LanguagePreferred 				VARCHAR(1000) NOT NULL,
	Skill					INT NOT NULL,
	MemberRole					VARCHAR(1000),
	AvailableHoursPerWeek	INT,
	SkillWeight				INT,
	ExperienceWeight		INT,
	HoursWeight				INT,
	LanguageWeight			INT,
	BudgetWeight			INT,
	PRIMARY KEY (JobId)
);


DELIMITER //
CREATE PROCEDURE populateRequirements(
	IN vLanguagePreferred VARCHAR(1000),
    IN vSkill VARCHAR(1000),
    IN vMemberRole VARCHAR(1000),
    IN vAvailableHoursPerWeek INT,
    IN vSkillWeight INT,
    IN vExperienceWeight INT,
    IN vHoursWeight INT,
    IN vLanguageWeight INT,
    IN vBudgetWeight INT
)
BEGIN
    DECLARE pid INT DEFAULT -1;
    SELECT MAX(ProjectID) INTO pid FROM Project;

    INSERT INTO Requirements (ProjectId,LanguagePreferred,Skill,MemberRole,AvailableHoursPerWeek,SkillWeight,ExperienceWeight, HoursWeight, LanguageWeight, BudgetWeight)
	VALUES (pid, vLanguagePreferred,vSkill,vMemberRole,vAvailableHoursPerWeek,vSkillWeight,vExperienceWeight, vHoursWeight, vLanguageWeight, vBudgetWeight);
END//

DELIMITER ;


DELIMITER //
CREATE PROCEDURE reset()
BEGIN

		DROP TABLE IF EXISTS Member;
        DROP TABLE IF EXISTS Project;
        DROP TABLE IF EXISTS Team;
        DROP TABLE IF EXISTS Requirements;
        CREATE TABLE Member(
			MemberId				INT NOT NULL AUTO_INCREMENT,
			MemberName					VARCHAR(1000) NOT NULL,
			DOB						DATE,
			Languages				VARCHAR(1000) NOT NULL,
			IsAssigned				INT,
			HourlyRate				FLOAT,
			MemberRole				VARCHAR(1000),
			Experience				INT,
			SkillScore				INT,
			AvailableHoursPerWeek	INT,
			PRIMARY KEY (MemberId)
		);

		CREATE TABLE Project(
			ProjectId				INT NOT NULL AUTO_INCREMENT,
			ProjectName					VARCHAR(1000) NOT NULL,
			ProjectEndDate					DATE,
			ProjectTeamSize					INT,
			Budget					FLOAT,
			Tools					VARCHAR(1000),
			IsAssignmentComplete	INT,
			Priority				INT,
			PRIMARY KEY (ProjectId)
		);

		CREATE TABLE Team(
			ProjectId	INT NOT NULL REFERENCES Member(MemberId),
			ProjectName varchar(1000),
			MemberId	INT NOT NULL REFERENCES Project(ProjectId),
				MemberName		VARCHAR(1000) NOT NULL
		);

		CREATE TABLE Requirements(
			JobId					INT NOT NULL AUTO_INCREMENT,
			ProjectId				INT NOT NULL REFERENCES Project(ProjectId),
			LanguagePreferred 				VARCHAR(1000) NOT NULL,
			Skill					INT NOT NULL,
			MemberRole					VARCHAR(1000),
			AvailableHoursPerWeek	INT,
			SkillWeight				INT,
			ExperienceWeight		INT,
			HoursWeight				INT,
			LanguageWeight			INT,
			BudgetWeight			INT,
			PRIMARY KEY (JobId)
		);
END //
DELIMITER ;