-
Notifications
You must be signed in to change notification settings - Fork 0
/
CREATE.sql
186 lines (168 loc) · 7.86 KB
/
CREATE.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
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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
DROP DATABASE if exists PROJECT_BASEIS;
CREATE DATABASE PROJECT_BASEIS;
USE PROJECT_BASEIS;
CREATE TABLE IF NOT EXISTS COMPANY(
AFM CHAR(9) DEFAULT 'unknown' NOT NULL,
DOY VARCHAR(15) DEFAULT 'unknown' NOT NULL,
NAME VARCHAR(35) DEFAULT 'unknown' NOT NULL,
PHONE BIGINT(16) DEFAULT '0' NOT NULL,
STREET VARCHAR(15) DEFAULT 'unknown' NOT NULL,
NUM TINYINT(16) DEFAULT '0' NOT NULL,
CITY VARCHAR(15) DEFAULT 'unknown' NOT NULL,
COUNTRY VARCHAR(15) DEFAULT 'unknown' NOT NULL,
PRIMARY KEY(AFM)
);
CREATE TABLE IF NOT EXISTS USERS(
USERNAME VARCHAR(12) DEFAULT 'unknown' NOT NULL,
PASSWORD VARCHAR(30) DEFAULT 'unknown' NOT NULL,
NAME VARCHAR(25) DEFAULT 'unknown' NOT NULL,
SURNAME VARCHAR(35) DEFAULT 'unknown' NOT NULL,
REG_DATE DATE NOT NULL DEFAULT CURDATE(),
EMAIL VARCHAR(35) DEFAULT 'unknown' NOT NULL,
PRIMARY KEY(USERNAME)
);
CREATE TABLE IF NOT EXISTS MANAGER(
MANAGERUSERNAME VARCHAR(12) DEFAULT 'unknown' NOT NULL,
EXP_YEARS TINYINT(4) DEFAULT '0' NOT NULL,
FIRM CHAR(9) DEFAULT 'unknown' NOT NULL,
PRIMARY KEY(MANAGERUSERNAME),
CONSTRAINT FIRM_COMPANY FOREIGN KEY(FIRM) REFERENCES COMPANY(AFM)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT MAN_USER FOREIGN KEY(MANAGERUSERNAME) REFERENCES USERS(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS EMPLOYEE(
USERNAME VARCHAR(12) DEFAULT 'unknown' NOT NULL,
AM INT(10) AUTO_INCREMENT NOT NULL, /*AM εταιρείας*/
BIO TEXT DEFAULT 'unknown' NOT NULL,
SISTATIKES VARCHAR(255) DEFAULT 'unknown' NOT NULL,
CERTIFICATES VARCHAR(255) DEFAULT 'unknown' NOT NULL,
AWARDS VARCHAR(255) DEFAULT 'unknown' NOT NULL,
LANG SET('EN','FR','SP','GR'), /*Επιλογή πολλαπλών γλωσσών σαν μεταβλητή*/
FIRM CHAR(9) DEFAULT 'unknown' NOT NULL, /*ΑΦΜ εταιρείας εργασίας*/
EXP_YEARS TINYINT(4) DEFAULT '0' NOT NULL, /*Χρόνια εμπειρίας υπαλλήλου*/
PRIMARY KEY(USERNAME),
UNIQUE KEY (AM), /*Κάθε χρήστης έχει δικό του ΑΜ αλλά δεν είναι το κύριο γνωρισμά του*/
CONSTRAINT EMPL_USER FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FIRM_EMPLOYEE FOREIGN KEY(FIRM) REFERENCES COMPANY(AFM)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS PROJECT(
EMPL VARCHAR(12) DEFAULT 'unknown' NOT NULL,
NUM TINYINT(4) NOT NULL AUTO_INCREMENT,
DESCR TEXT DEFAULT 'unknown' NOT NULL,
URL VARCHAR(60) DEFAULT 'unknown' NOT NULL,
PRIMARY KEY (NUM, EMPL), /*Ξεχωριστός συνδυασμός έτσι ώστε κάθε χρήστης να έχει δικιά του απαρίθμηση των project του*/
CONSTRAINT EMPL_PROJECT FOREIGN KEY (EMPL) REFERENCES EMPLOYEE(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS EVALUATOR(
USERNAME VARCHAR(12) DEFAULT 'unknown' NOT NULL,
ID TINYINT(4) NOT NULL AUTO_INCREMENT,
EXP_YEARS TINYINT(4) DEFAULT '0' NOT NULL,
FIRM CHAR(9) DEFAULT 'unknown' NOT NULL,
PRIMARY KEY (USERNAME),
UNIQUE KEY (ID), /* Κάθε αξιολογητής έχει δικό του αριθμό αλλα δεν είναι το κύριο γνωρισμά του*/
CONSTRAINT EV_USER FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT EV_COMPANY FOREIGN KEY (FIRM) REFERENCES COMPANY(AFM)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS JOB(
ID INT(4) NOT NULL AUTO_INCREMENT,
START_DATE DATE NOT NULL,
SALARY FLOAT(6,1) DEFAULT '0' NOT NULL,
POSITION VARCHAR(40) DEFAULT 'unknown' NOT NULL,
EDRA VARCHAR(45) DEFAULT 'unknown' NOT NULL,
EVALUATOR VARCHAR(12) DEFAULT 'unknown' NOT NULL,
ANNOUNCE_DATE DATETIME DEFAULT CURRENT_TIMESTAMP,
SUBMISSION_DATE DATE NOT NULL,
PRIMARY KEY(ID),
CONSTRAINT EV_JOB FOREIGN KEY (EVALUATOR) REFERENCES EVALUATOR(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS DEGREE(
TITLE VARCHAR(50) DEFAULT 'unknown' NOT NULL,
IDRYMA VARCHAR(40) DEFAULT 'unknown' NOT NULL,
BATHMIDA ENUM('LYKEIO','UNIV','MASTER','PHD'),
PRIMARY KEY(TITLE, IDRYMA)
);
CREATE TABLE IF NOT EXISTS HAS_DEGREE(
DEGR_TITLE VARCHAR(50) DEFAULT 'unknown' NOT NULL,
DEGR_IDRYMA VARCHAR(40) DEFAULT 'unknown' NOT NULL,
EMPL_USERNAME VARCHAR(12) DEFAULT 'unknown' NOT NULL,
ETOS YEAR(4) DEFAULT 0 NOT NULL,
GRADE FLOAT(3,1) DEFAULT 0 NOT NULL,
PRIMARY KEY (DEGR_TITLE,DEGR_IDRYMA,EMPL_USERNAME),
CONSTRAINT DEG_TITLE_IDR FOREIGN KEY (DEGR_TITLE, DEGR_IDRYMA) REFERENCES DEGREE(TITLE, IDRYMA)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT EMPL_USR FOREIGN KEY (EMPL_USERNAME) REFERENCES EMPLOYEE(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS ANTIKEIM;
CREATE TABLE IF NOT EXISTS ANTIKEIM(
TITLE VARCHAR(36) DEFAULT NULL,
DESCR TINYTEXT DEFAULT 'unknown' NOT NULL,
BELONGS_TO VARCHAR(36) DEFAULT NULL,
PRIMARY KEY (TITLE),
CONSTRAINT ANT_BELONGS FOREIGN KEY(BELONGS_TO) REFERENCES ANTIKEIM(TITLE)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS NEEDS(
JOB_ID INT(4) DEFAULT '0' NOT NULL,
ANTIKEIM_TITLE VARCHAR(36) DEFAULT 'unknown' NOT NULL,
PRIMARY KEY (JOB_ID,ANTIKEIM_TITLE),
CONSTRAINT NEED_ID FOREIGN KEY (JOB_ID) REFERENCES JOB(ID)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT NEED_TITLE FOREIGN KEY (ANTIKEIM_TITLE) REFERENCES ANTIKEIM(TITLE)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS LOG(
ID INT(4) NOT NULL AUTO_INCREMENT,
USERNAME VARCHAR(40) DEFAULT 0 NOT NULL, /*To username που καταχωρείται είναι αυτό που είναι αποθηκευμένο στη mysql.user*/
PROCESS_DATE DATETIME NOT NULL,
SUCCESS BOOLEAN DEFAULT '0' NOT NULL,
PROCESS_TYPE ENUM('UPDATE','INSERT','DELETE'),
TABLE_NAME ENUM('JOB','EMPLOYEE','SUBMIT APPLICATION'),
PRIMARY KEY (ID)
);
CREATE TABLE IF NOT EXISTS EVALUATION( /*Πίνακας που αποθηκεύονται όλες οι αξιολογήσεις ανεξαρτήτως κατάστασης*/
EVALUATION_ID INT(4) NOT NULL AUTO_INCREMENT,
EMPL_USERNAME VARCHAR(12) NOT NULL,
JOB_ID INT(4) NOT NULL,
GRADE_A INT(4) DEFAULT NULL,
COMMENTS VARCHAR(255) DEFAULT 'unknown' NOT NULL,
GRADE_B INT(4) DEFAULT NULL,
GRADE_C INT(4) DEFAULT NULL,
PRIMARY KEY (EVALUATION_ID),
CONSTRAINT EV_JOBID FOREIGN KEY (JOB_ID) REFERENCES JOB(ID)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT EV_EMPL FOREIGN KEY (EMPL_USERNAME) REFERENCES EMPLOYEE(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS EVALUATIONRESULT( /*Πίνακας που αποθηκεύονται οι αξιολογήσεις που έχουν οριστικοποιηθεί*/
EVID INT(4) DEFAULT '0' NOT NULL,
EMPL_USERNAME VARCHAR(12) DEFAULT 'unknown' NOT NULL,
JOB_ID INT(4) DEFAULT '0' NOT NULL,
GRADE INT(4) DEFAULT NULL,
COMMENTS VARCHAR(255) DEFAULT 'unknown' NOT NULL,
PRIMARY KEY (EVID),
CONSTRAINT EVA_ID FOREIGN KEY (EVID) REFERENCES EVALUATION(EVALUATION_ID)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT EVA_EMPL FOREIGN KEY (EMPL_USERNAME) REFERENCES EMPLOYEE(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT EVA_JOB FOREIGN KEY (JOB_ID) REFERENCES JOB(ID)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS SUBMIT_APPLICATION( /*Αντικατάσταση της REQUESTEVALUATION, ο εργαζόμενος κάνει αίτηση για να αξιολογηθεί, αν αλλάξει γνώμη διαγράφεται η αίτηση του από τον πίνακα*/
ID INT(4) NOT NULL AUTO_INCREMENT,
EMPL_USERNAME VARCHAR(12) DEFAULT 'unknown' NOT NULL,
JOB_ID INT(4) DEFAULT '0' NOT NULL,
APPLICATION_DATETIME DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(ID),
CONSTRAINT SU_EMPL FOREIGN KEY (EMPL_USERNAME) REFERENCES EMPLOYEE(USERNAME)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT SU_JOBID FOREIGN KEY (JOB_ID) REFERENCES JOB(ID)
ON DELETE CASCADE ON UPDATE CASCADE
);