G6CSgsUZ

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