· 6 years ago · Apr 01, 2019, 06:08 PM
1IF NOT EXISTS(select * from sys.schemas where name='HR_Sluzba')
2begin
3 execute('CREATE SCHEMA HR_Sluzba authorization dbo');
4end;
5
6
7IF OBJECT_ID('HR_Sluzba.FK_Zaposlen_Kandidat', 'F') IS NOT NULL
8 alter table [HR_Sluzba].[Zaposlen]
9 DROP CONSTRAINT FK_Zaposlen_Kandidat;
10
11IF OBJECT_ID('HR_Sluzba.FK_IMA_PARAMETAR_PARAMETAR','F') IS NOT NULL
12 alter table [HR_Sluzba].[Ima_Parametar]
13 DROP CONSTRAINT FK_IMA_PARAMETAR_PARAMETAR;
14
15
16if object_id ('HR_Sluzba.FK_IMA_PARAMETAR_TIP_TESTA', 'F') is not null
17 alter table [HR_Sluzba].[Ima_Parametar]
18 DROP CONSTRAINT FK_IMA_PARAMETAR_TIP_TESTA;
19
20if object_id ('HR_Sluzba.FK_EVALUACIONI_TEST_ZAPOSLENI', 'F') is not null
21 alter table [HR_Sluzba].[Evaluacioni_Test]
22 DROP CONSTRAINT FK_EVALUACIONI_TEST_ZAPOSLENI;
23
24
25if object_id ('HR_Sluzba.FK_PARAMETAR_PRIPADA_PRIPADA', 'F') is not null
26 alter table [HR_Sluzba].[Parametar_pripada]
27 DROP CONSTRAINT FK_PARAMETAR_PRIPADA_PRIPADA;
28
29if object_id ('HR_Sluzba.FK_PARAMETAR_PRIPADA_IMA_PARAMETAR', 'F') is not null
30 alter table [HR_Sluzba].[Parametar_pripada]
31 DROP CONSTRAINT FK_PARAMETAR_PRIPADA_IMA_PARAMETAR;
32
33if object_id ('HR_Sluzba.FK_PRIPADA_EVALUACIONI_TEST', 'F') is not null
34 alter table [HR_Sluzba].[Pripada]
35 DROP CONSTRAINT FK_PRIPADA_EVALUACIONI_TEST;
36if object_id ('HR_Sluzba.FK_PRIPADA_TIP_TESTA', 'F') is not null
37 alter table [HR_Sluzba].[Pripada]
38 DROP CONSTRAINT FK_PRIPADA_TIP_TESTA;
39
40IF OBJECT_ID('[HR_Sluzba].[Parametar]', 'U') IS NOT NULL
41DROP TABLE [HR_Sluzba].[Parametar];
42GO
43-- Create the table in the specified schema
44CREATE TABLE [HR_Sluzba].[Parametar]
45(
46 [id_parametra] NUMERIC(5),
47 [kriterijum_evaluacije] NVARCHAR(75) NOT NULL,
48 CONSTRAINT PK_Parametar_id_parametra PRIMARY KEY (id_parametra)
49);
50GO
51
52IF OBJECT_ID('[HR_Sluzba].[Parametar_Sequence]','SO') IS NOT NULL
53 DROP SEQUENCE [HR_Sluzba].[Parametar_Sequence];
54GO
55
56CREATE SEQUENCE [HR_Sluzba].[Parametar_Sequence]
57 AS int
58 start with 0
59 increment by 1
60 cycle;
61
62ALTER TABLE [HR_Sluzba].[Parametar]
63 ADD CONSTRAINT DFT_ID_PARAMETAR DEFAULT(next value for [HR_Sluzba].[Parametar_Sequence]) for id_parametra;
64
65
66-- Create a new table called '[Tip_evaluacionog_testa]' in schema '[HR_Sluzba]'
67-- DROP the table if it already exists
68IF OBJECT_ID('[HR_Sluzba].[Tip_evaluacionog_testa]', 'U') IS NOT NULL
69DROP TABLE [HR_Sluzba].[Tip_evaluacionog_testa]
70GO
71-- Create the table in the specified schema
72CREATE TABLE [HR_Sluzba].[Tip_evaluacionog_testa]
73(
74 [id_tipa_evaluacionog_testa] NUMERIC(5),
75 [vrsta_testa] NVARCHAR(50) NOT NULL,
76 CONSTRAINT PK_TIP_EVALUACIONOG_TESTA_id_tipa_evaluacionog_testa PRIMARY KEY(id_tipa_evaluacionog_testa)
77);
78GO
79
80
81if OBJECT_ID ('HR_Sluzba.Tip_Evaluacionog_testa_Sequence', 'SO') is not null
82 DROP sequence HR_Sluzba.Tip_Evaluacionog_testa_Sequence;
83
84CREATE SEQUENCE [HR_Sluzba].[Tip_Evaluacionog_testa_Sequence]
85 AS int
86 start with 0
87 increment by 1
88 cycle;
89
90ALTER TABLE [HR_Sluzba].[Tip_evaluacionog_testa]
91 ADD CONSTRAINT DFT_ID_TIP_TESTA DEFAULT(next value for [HR_Sluzba].[Tip_Evaluacionog_testa_Sequence]) for id_tipa_evaluacionog_testa;
92
93
94
95-- Create a new table called '[Ima_Parametar]' in schema '[HR_Sluzba]'
96-- DROP the table if it already exists
97IF OBJECT_ID('[HR_Sluzba].[Ima_Parametar]', 'U') IS NOT NULL
98DROP TABLE [HR_Sluzba].[Ima_Parametar]
99GO
100-- Create the table in the specified schema
101CREATE TABLE [HR_Sluzba].[Ima_Parametar]
102(
103 [id_parametra] NUMERIC(5),
104 [id_tipa_evaluacionog_testa] NUMERIC(5),
105 CONSTRAINT FK_IMA_PARAMETAR_PARAMETAR FOREIGN KEY (id_parametra)
106 REFERENCES [HR_Sluzba].[Parametar](id_parametra),
107 CONSTRAINT FK_IMA_PARAMETAR_TIP_TESTA FOREIGN KEY (id_tipa_evaluacionog_testa)
108 REFERENCES [HR_Sluzba].[Tip_Evaluacionog_testa](id_tipa_evaluacionog_testa),
109 CONSTRAINT PK_IMA_PARAMETAR PRIMARY KEY (id_parametra,id_tipa_evaluacionog_testa)
110);
111GO
112
113IF OBJECT_ID('[HR_Sluzba].[Kandidat]', 'U') IS NOT NULL
114DROP TABLE [HR_Sluzba].[Kandidat]
115GO
116
117IF OBJECT_ID('[HR_Sluzba].[Zaposlen]', 'U') IS NOT NULL
118DROP TABLE [HR_Sluzba].[Zaposlen]
119GO
120
121if OBJECT_ID ('HR_Sluzba.Kandidat_Sequence', 'SO') is not null
122 DROP sequence HR_Sluzba.Kandidat_Sequence;
123
124CREATE SEQUENCE [HR_Sluzba].[Kandidat_Sequence]
125 AS int
126 start with 1
127 increment by 1
128 minvalue 1
129 maxvalue 10000
130 cycle;
131
132CREATE TABLE [HR_Sluzba].[Kandidat] (
133 k_id NUMERIC(5) default (next value for HR_Sluzba.Kandidat_sequence),
134 k_jmbg NUMERIC(13) not null,
135 k_ime VARCHAR(15) not null,
136 k_prezime CHAR(15) not null,
137 k_datrodj DATE not null,
138 k_pol VARCHAR(1) not null,
139 k_email CHAR(50) not null,
140 k_broj CHAR(15) not null,
141 k_drzv_stal CHAR(15) not null,
142 k_grad_stal CHAR(15) not null,
143 k_adres_stal CHAR(50) not null,
144 k_drzv_preb CHAR(15),
145 k_grad_preb CHAR(15),
146 k_adres_preb CHAR(50),
147 CONSTRAINT PK_Kandidat_k_id PRIMARY KEY (k_id),
148 CONSTRAINT UQ_Kandidat_k_jmbg unique (k_jmbg),
149 CONSTRAINT UQ_Kandidat_k_email unique (k_email),
150 CONSTRAINT UQ_Kandidat_k_broj unique (k_broj),
151 CONSTRAINT CH_Kandidat_k_jmbg CHECK (len(k_jmbg) = 13),
152 CONSTRAINT CH_Kandidat_k_datrodj CHECK (DATEDIFF(day, k_datrodj, CAST(current_timestamp as DATE)) > 0),
153 CONSTRAINT CK_Kandidat_k_pol CHECK (k_pol in ('m', 'M', 'z', 'Z'))
154);
155
156CREATE TABLE [HR_Sluzba].[Zaposlen] (
157 k_id NUMERIC(5),
158 mbr NUMERIC(13),
159 z_jmbg NUMERIC(13) not null,
160 z_ime VARCHAR(15) not null,
161 z_prezime CHAR(15) not null,
162 z_datrodj DATE not null,
163 z_pol VARCHAR(1) not null,
164 z_email CHAR(50) not null,
165 z_broj CHAR(15) not null,
166 z_drzv_stal CHAR(15) not null,
167 z_grad_stal CHAR(15) not null,
168 z_adres_stal CHAR(50) not null,
169 z_drzv_preb CHAR(15),
170 z_grad_preb CHAR(15),
171 z_adres_preb CHAR(50),
172 z_datum_zap DATE not null,
173 CONSTRAINT UQ_Zaposlen_z_jmbg unique (z_jmbg),
174 CONSTRAINT UQ_Zaposlen_z_email unique (z_email),
175 CONSTRAINT UQ_Zaposlen_z_broj unique (z_broj),
176 CONSTRAINT CH_Zaposlen_z_jmbg CHECK (len(z_jmbg) = 13),
177 CONSTRAINT CH_Zaposlen_z_datrodj CHECK (DATEDIFF(day, z_datrodj, CAST(current_timestamp as DATE)) > 0),
178 CONSTRAINT CH_Zaposlen_z_datum_zap_datrodj CHECK (DATEDIFF(day, z_datrodj, z_datum_zap) > 0),
179 CONSTRAINT CH_Zaposlen_z_datum_zap CHECK (DATEDIFF(day, z_datum_zap, CAST(current_timestamp as DATE)) > 0),
180 CONSTRAINT CK_Zaposlen_z_pol CHECK (z_pol in ('m', 'M', 'z', 'Z')),
181 CONSTRAINT FK_Zaposlen_Kandidat FOREIGN KEY (k_id)
182 references HR_Sluzba.Kandidat (k_id),
183 CONSTRAINT PK_Zaposlen_mbr_k_id PRIMARY KEY (mbr, k_id)
184);
185
186 -- Create a new table called '[Evaluacioni_Test]' in schema '[HR_Sluzba]'
187 -- DROP the table if it already exists
188 IF OBJECT_ID('[HR_Sluzba].[Evaluacioni_Test]', 'U') IS NOT NULL
189 DROP TABLE [HR_Sluzba].[Evaluacioni_Test]
190 GO
191 -- Create the table in the specified schema
192 CREATE TABLE [HR_Sluzba].[Evaluacioni_Test]
193 (
194 [id_evaluacionog_testa] NUMERIC(5),
195 [mbr] NUMERIC(13),
196 [k_id] NUMERIC(5),
197 [datum_testiranja] DATE NOT NULL,
198 CONSTRAINT FK_EVALUACIONI_TEST_ZAPOSLENI FOREIGN KEY(mbr,k_id)
199 REFERENCES [HR_Sluzba].[Zaposlen](mbr,k_id),
200 CONSTRAINT PK_EvALUACIONI_TEST PRIMARY KEY (id_evaluacionog_testa,mbr,k_id)
201 );
202 GO
203
204 -- Create a new table called '[Pripada]' in schema '[HR_Sluzba]'
205 -- DROP the table if it already exists
206 IF OBJECT_ID('[HR_Sluzba].[Pripada]', 'U') IS NOT NULL
207 DROP TABLE [HR_Sluzba].[Pripada]
208 GO
209 -- Create the table in the specified schema
210 CREATE TABLE [HR_Sluzba].[Pripada]
211 (
212 [id_evaluacionog_testa] NUMERIC(5),
213 [mbr] NUMERIC(13),
214 [k_id] NUMERIC(5),
215 [id_tipa_evaluacionog_testa] NUMERIC(5),
216 CONSTRAINT FK_PRIPADA_EVALUACIONI_TEST FOREIGN KEY(id_evaluacionog_testa,mbr,k_id)
217 REFERENCES [HR_Sluzba].[Evaluacioni_Test](id_evaluacionog_testa,mbr,k_id),
218 CONSTRAINT FK_PRIPADA_TIP_TESTA FOREIGN KEY(id_tipa_evaluacionog_testa)
219 REFERENCES [HR_Sluzba].[Tip_evaluacionog_testa](id_tipa_evaluacionog_testa),
220 CONSTRAINT PK_PRIPADA PRIMARY KEY (id_evaluacionog_testa,mbr,k_id,id_tipa_evaluacionog_testa)
221 );
222 GO
223
224
225-- Create a new table called '[Parametar_pripada]' in schema '[HR_Sluzba]'
226-- DROP the table if it already exists
227IF OBJECT_ID('[HR_Sluzba].[Parametar_pripada]', 'U') IS NOT NULL
228DROP TABLE [HR_Sluzba].[Parametar_pripada]
229GO
230-- Create the table in the specified schema
231CREATE TABLE [HR_Sluzba].[Parametar_pripada]
232(
233 [rezultat_testa] NUMERIC(5) NOT NULL,
234 [id_parametra] NUMERIC(5),
235 [id_tipa_evaluacionog_testa] NUMERIC(5),
236 [id_evaluacionog_testa] NUMERIC(5),
237 [mbr] NUMERIC(13),
238 [k_id] NUMERIC(5),
239 [id_evaluacionog_tipa] NUMERIC(5),
240 CONSTRAINT FK_PARAMETAR_PRIPADA_PRIPADA FOREIGN KEY(id_evaluacionog_testa,mbr,k_id,id_tipa_evaluacionog_testa)
241 REFERENCES [HR_Sluzba].[Pripada](id_evaluacionog_testa,mbr,k_id,id_tipa_evaluacionog_testa),
242 CONSTRAINT FK_PARAMETAR_PRIPADA_IMA_PARAMETAR FOREIGN KEY(id_parametra,id_evaluacionog_tipa)
243 REFERENCES [HR_Sluzba].[Ima_Parametar](id_parametra,id_tipa_evaluacionog_testa),
244 CONSTRAINT PK_PARAMETAR_PRIPADA PRIMARY KEY(id_parametra,id_tipa_evaluacionog_testa,id_evaluacionog_testa,mbr,k_id,id_evaluacionog_tipa)
245);
246GO
247
248INSERT INTO HR_Sluzba.Kandidat
249(k_jmbg,k_ime,k_prezime,k_datrodj,k_pol,k_email,k_broj,k_drzv_stal,k_grad_stal,k_adres_stal,k_drzv_preb,k_grad_preb,k_adres_preb)
250VALUES ('5435444443326','Nikola','Nikolic','1997-01-30','M','nikolankolic1997@gmail.com','5354354543','Srbija','Pozarevac','B Stefana 8','Srbija','Novi Sad','B Slobode bb'),
251 ('5435433333326','Marko','Markovic','1999-08-26','M','markomarkovi@hotmail.com','645454546','Srbija','Loznica','L Stevica 6','Srbija','Futog','NS put'),
252 ('5435412345678','Milica','Milic','1987-04-20','Z','milicamilic@yahoo.com','132121321','Srbija','Loznica','Buliver Dositeja 1','Srbija','Beorad','Kneza Milosa 6');
253
254
255select * from HR_Sluzba.Kandidat