· 7 years ago · Dec 04, 2018, 10:12 PM
1/*==============================================================*/
2/* DBMS name: Microsoft SQL Server 2017 */
3/* Created on: 04.12.2018 22:06:04 */
4/*==============================================================*/
5
6
7if exists (select 1
8 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
9 where r.fkeyid = object_id('Oferty') and o.name = 'FK_OFERTY_RELATIONS_STANOWIS')
10alter table Oferty
11 drop constraint FK_OFERTY_RELATIONS_STANOWIS
12go
13
14if exists (select 1
15 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
16 where r.fkeyid = object_id('Podania') and o.name = 'FK_PODANIA_RELATIONS_KANDYDAC')
17alter table Podania
18 drop constraint FK_PODANIA_RELATIONS_KANDYDAC
19go
20
21if exists (select 1
22 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
23 where r.fkeyid = object_id('Podania') and o.name = 'FK_PODANIA_RELATIONS_OFERTY')
24alter table Podania
25 drop constraint FK_PODANIA_RELATIONS_OFERTY
26go
27
28if exists (select 1
29 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
30 where r.fkeyid = object_id('SDPracownik') and o.name = 'FK_SDPRACOW_RELATIONS_STANOWIS')
31alter table SDPracownik
32 drop constraint FK_SDPRACOW_RELATIONS_STANOWIS
33go
34
35if exists (select 1
36 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
37 where r.fkeyid = object_id('SDPracownik') and o.name = 'FK_SDPRACOW_RELATIONS_PRACOWNI')
38alter table SDPracownik
39 drop constraint FK_SDPRACOW_RELATIONS_PRACOWNI
40go
41
42if exists (select 1
43 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
44 where r.fkeyid = object_id('StanowiskoDzial') and o.name = 'FK_STANOWIS_RELATIONS_DZIAÅ')
45alter table StanowiskoDzial
46 drop constraint FK_STANOWIS_RELATIONS_DZIAÅ
47go
48
49if exists (select 1
50 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
51 where r.fkeyid = object_id('StanowiskoDzial') and o.name = 'FK_STANOWIS_RELATIONS_STANOWIS')
52alter table StanowiskoDzial
53 drop constraint FK_STANOWIS_RELATIONS_STANOWIS
54go
55
56if exists (select 1
57 from sysobjects
58 where id = object_id('Dział')
59 and type = 'U')
60 drop table Dział
61go
62
63if exists (select 1
64 from sysobjects
65 where id = object_id('Kandydaci')
66 and type = 'U')
67 drop table Kandydaci
68go
69
70if exists (select 1
71 from sysindexes
72 where id = object_id('Oferty')
73 and name = 'Relationship_7_FK'
74 and indid > 0
75 and indid < 255)
76 drop index Oferty.Relationship_7_FK
77go
78
79if exists (select 1
80 from sysobjects
81 where id = object_id('Oferty')
82 and type = 'U')
83 drop table Oferty
84go
85
86if exists (select 1
87 from sysindexes
88 where id = object_id('Podania')
89 and name = 'Relationship_6_FK'
90 and indid > 0
91 and indid < 255)
92 drop index Podania.Relationship_6_FK
93go
94
95if exists (select 1
96 from sysindexes
97 where id = object_id('Podania')
98 and name = 'Relationship_5_FK'
99 and indid > 0
100 and indid < 255)
101 drop index Podania.Relationship_5_FK
102go
103
104if exists (select 1
105 from sysobjects
106 where id = object_id('Podania')
107 and type = 'U')
108 drop table Podania
109go
110
111if exists (select 1
112 from sysobjects
113 where id = object_id('Pracownicy')
114 and type = 'U')
115 drop table Pracownicy
116go
117
118if exists (select 1
119 from sysindexes
120 where id = object_id('SDPracownik')
121 and name = 'Relationship_11_FK'
122 and indid > 0
123 and indid < 255)
124 drop index SDPracownik.Relationship_11_FK
125go
126
127if exists (select 1
128 from sysindexes
129 where id = object_id('SDPracownik')
130 and name = 'Relationship_8_FK'
131 and indid > 0
132 and indid < 255)
133 drop index SDPracownik.Relationship_8_FK
134go
135
136if exists (select 1
137 from sysobjects
138 where id = object_id('SDPracownik')
139 and type = 'U')
140 drop table SDPracownik
141go
142
143if exists (select 1
144 from sysobjects
145 where id = object_id('Stanowiska')
146 and type = 'U')
147 drop table Stanowiska
148go
149
150if exists (select 1
151 from sysindexes
152 where id = object_id('StanowiskoDzial')
153 and name = 'Relationship_10_FK'
154 and indid > 0
155 and indid < 255)
156 drop index StanowiskoDzial.Relationship_10_FK
157go
158
159if exists (select 1
160 from sysindexes
161 where id = object_id('StanowiskoDzial')
162 and name = 'Relationship_9_FK'
163 and indid > 0
164 and indid < 255)
165 drop index StanowiskoDzial.Relationship_9_FK
166go
167
168if exists (select 1
169 from sysobjects
170 where id = object_id('StanowiskoDzial')
171 and type = 'U')
172 drop table StanowiskoDzial
173go
174
175/*==============================================================*/
176/* Table: Dział */
177/*==============================================================*/
178create table Dział (
179 D_id int not null,
180 D_nazwa varchar(15) null,
181 constraint PK_DZIAÅ primary key (D_id)
182)
183go
184
185/*==============================================================*/
186/* Table: Kandydaci */
187/*==============================================================*/
188create table Kandydaci (
189 K_id int not null,
190 K_nazwisko varchar(32) null,
191 K_imie varchar(15) null,
192 constraint PK_KANDYDACI primary key (K_id)
193)
194go
195
196/*==============================================================*/
197/* Table: Oferty */
198/*==============================================================*/
199create table Oferty (
200 O_id int not null,
201 S_id int null,
202 O_opis varchar(255) null,
203 constraint PK_OFERTY primary key (O_id)
204)
205go
206
207/*==============================================================*/
208/* Index: Relationship_7_FK */
209/*==============================================================*/
210
211
212
213
214create nonclustered index Relationship_7_FK on Oferty (S_id ASC)
215go
216
217/*==============================================================*/
218/* Table: Podania */
219/*==============================================================*/
220create table Podania (
221 P_id int not null,
222 O_id int null,
223 K_id int null,
224 constraint PK_PODANIA primary key (P_id)
225)
226go
227
228/*==============================================================*/
229/* Index: Relationship_5_FK */
230/*==============================================================*/
231
232
233
234
235create nonclustered index Relationship_5_FK on Podania (K_id ASC)
236go
237
238/*==============================================================*/
239/* Index: Relationship_6_FK */
240/*==============================================================*/
241
242
243
244
245create nonclustered index Relationship_6_FK on Podania (O_id ASC)
246go
247
248/*==============================================================*/
249/* Table: Pracownicy */
250/*==============================================================*/
251create table Pracownicy (
252 Pr_id int not null,
253 Pr_imie varchar(15) null,
254 Pr_nazwisko varchar(32) null,
255 constraint PK_PRACOWNICY primary key (Pr_id)
256)
257go
258
259/*==============================================================*/
260/* Table: SDPracownik */
261/*==============================================================*/
262create table SDPracownik (
263 SDP_ID int not null,
264 Pr_id int null,
265 SD_ID int null,
266 constraint PK_SDPRACOWNIK primary key (SDP_ID)
267)
268go
269
270/*==============================================================*/
271/* Index: Relationship_8_FK */
272/*==============================================================*/
273
274
275
276
277create nonclustered index Relationship_8_FK on SDPracownik (Pr_id ASC)
278go
279
280/*==============================================================*/
281/* Index: Relationship_11_FK */
282/*==============================================================*/
283
284
285
286
287create nonclustered index Relationship_11_FK on SDPracownik (SD_ID ASC)
288go
289
290/*==============================================================*/
291/* Table: Stanowiska */
292/*==============================================================*/
293create table Stanowiska (
294 S_id int not null,
295 S_nazwa varchar(15) null,
296 constraint PK_STANOWISKA primary key (S_id)
297)
298go
299
300/*==============================================================*/
301/* Table: StanowiskoDzial */
302/*==============================================================*/
303create table StanowiskoDzial (
304 SD_ID int not null,
305 S_id int null,
306 D_id int null,
307 constraint PK_STANOWISKODZIAL primary key (SD_ID)
308)
309go
310
311/*==============================================================*/
312/* Index: Relationship_9_FK */
313/*==============================================================*/
314
315
316
317
318create nonclustered index Relationship_9_FK on StanowiskoDzial (S_id ASC)
319go
320
321/*==============================================================*/
322/* Index: Relationship_10_FK */
323/*==============================================================*/
324
325
326
327
328create nonclustered index Relationship_10_FK on StanowiskoDzial (D_id ASC)
329go
330
331alter table Oferty
332 add constraint FK_OFERTY_RELATIONS_STANOWIS foreign key (S_id)
333 references Stanowiska (S_id)
334go
335
336alter table Podania
337 add constraint FK_PODANIA_RELATIONS_KANDYDAC foreign key (K_id)
338 references Kandydaci (K_id)
339go
340
341alter table Podania
342 add constraint FK_PODANIA_RELATIONS_OFERTY foreign key (O_id)
343 references Oferty (O_id)
344go
345
346alter table SDPracownik
347 add constraint FK_SDPRACOW_RELATIONS_STANOWIS foreign key (SD_ID)
348 references StanowiskoDzial (SD_ID)
349go
350
351alter table SDPracownik
352 add constraint FK_SDPRACOW_RELATIONS_PRACOWNI foreign key (Pr_id)
353 references Pracownicy (Pr_id)
354go
355
356alter table StanowiskoDzial
357 add constraint FK_STANOWIS_RELATIONS_DZIAÅ foreign key (D_id)
358 references Dział (D_id)
359go
360
361alter table StanowiskoDzial
362 add constraint FK_STANOWIS_RELATIONS_STANOWIS foreign key (S_id)
363 references Stanowiska (S_id)
364go