-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathhrproekt.sql
81 lines (71 loc) · 2.13 KB
/
hrproekt.sql
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
CREATE TABLE IF NOT EXISTS country(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Name VARCHAR(20) NOT NULL,
Language VARCHAR(20) NOT NULL,
Paysystem VARCHAR(20) NOT NULL
);
SELECT * FROM country;
CREATE TABLE IF NOT EXISTS location(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(20) NOT NULL,
Details TEXT NOT NULL,
CountryId INT NOT NULL,
FOREIGN KEY (CountryId) REFERENCES country(Id)
);
CREATE TABLE IF NOT EXISTS department(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(20) NOT NULL,
Details TEXT NOT NULL,
LocationId INT NOT NULL,
FOREIGN KEY (LocationId) REFERENCES location(Id)
);
CREATE TABLE IF NOT EXISTS employee(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Firstname VARCHAR(20) NOT NULL,
Lastname VARCHAR(20) NOT NULL,
Gender ENUM ("m", "f"),
Address VARCHAR(20) NOT NULL,
Birthdate DATE NOT NULL,
Employmentdate DATE NOT NULL,
Leavingdate DATE NOT NULL,
Details TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS workassignments(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Startdate DATE NOT NULL,
Finishdate DATE NOT NULL,
Details TEXT NOT NULL,
SupervisorId INT NOT NULL,
FOREIGN KEY (SupervisorId) REFERENCES employee(Id),
DepartmentId INT NOT NULL,
FOREIGN KEY (DepartmentId) REFERENCES department(Id)
);
CREATE TABLE IF NOT EXISTS reports(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Revisiondate DATE NOT NULL,
Revisorcomments TEXT NOT NULL,
Employeecomments TEXT NOT NULL,
RevisorId INT NOT NULL,
FOREIGN KEY (RevisorId) REFERENCES employee(Id)
);
CREATE TABLE IF NOT EXISTS trainings(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
Trainingdatestart DATE NOT NULL,
Trainingdatefinish DATE NOT NULL,
Results TEXT NOT NULL,
EmployeeId INT NOT NULL,
FOREIGN KEY (EmployeeId) REFERENCES employee(Id)
);
CREATE TABLE IF NOT EXISTS databaseadmin(
Id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
AdminId INT NOT NULL,
FOREIGN KEY (AdminId) REFERENCES employee(Id)
);
ALTER TABLE databaseadmin
ADD COLUMN Username VARCHAR(15) AFTER AdminId,
ADD COLUMN Password VARCHAR(200) AFTER Username;
ALTER TABLE databaseadmin
MODIFY Username VARCHAR(15) NOT NULL,
MODIFY Password VARCHAR(300) NOT NULL;