· 8 years ago · Jan 23, 2018, 08:18 AM
1/*==============================================================*/
2/* DBMS name: Microsoft SQL Server 2014 */
3/* Created on: 09.12.2017 19:38:06 */
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('Bilet') and o.name = 'FK_BILET_RODZAJU_RODZAJ')
10alter table Bilet
11 drop constraint FK_BILET_RODZAJU_RODZAJ
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('"Jest na trasie"') and o.name = 'FK_JEST NA _JEST NA T_TRASA')
17alter table "Jest na trasie"
18 drop constraint "FK_JEST NA _JEST NA T_TRASA"
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('"Jest na trasie"') and o.name = 'FK_JEST NA _JEST NA T_PRZYSTAN')
24alter table "Jest na trasie"
25 drop constraint "FK_JEST NA _JEST NA T_PRZYSTAN"
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('"Jest przypisana do"') and o.name = 'FK_JEST PRZ_JEST PRZY_KURS')
31alter table "Jest przypisana do"
32 drop constraint "FK_JEST PRZ_JEST PRZY_KURS"
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('"Jest przypisana do"') and o.name = 'FK_JEST PRZ_JEST PRZY_TRASA')
38alter table "Jest przypisana do"
39 drop constraint "FK_JEST PRZ_JEST PRZY_TRASA"
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('Kierunek') and o.name = 'FK_KIERUNEK_KOŃCZY W_MIEJSCOW')
45alter table Kierunek
46 drop constraint "FK_KIERUNEK_KOŃCZY W_MIEJSCOW"
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('Kierunek') and o.name = 'FK_KIERUNEK_STARTUJE _MIEJSCOW')
52alter table Kierunek
53 drop constraint "FK_KIERUNEK_STARTUJE _MIEJSCOW"
54go
55
56if exists (select 1
57 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
58 where r.fkeyid = object_id('Kurs') and o.name = 'FK_KURS_JEST KIER_KIEROWCA')
59alter table Kurs
60 drop constraint "FK_KURS_JEST KIER_KIEROWCA"
61go
62
63if exists (select 1
64 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
65 where r.fkeyid = object_id('Kurs') and o.name = 'FK_KURS_RELATIONS_KIERUNEK')
66alter table Kurs
67 drop constraint FK_KURS_RELATIONS_KIERUNEK
68go
69
70if exists (select 1
71 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
72 where r.fkeyid = object_id('Kurs') and o.name = 'FK_KURS_JEST UZYW_AUTOKAR')
73alter table Kurs
74 drop constraint "FK_KURS_JEST UZYW_AUTOKAR"
75go
76
77if exists (select 1
78 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
79 where r.fkeyid = object_id('Pasazer') and o.name = 'FK_PASAZER_BYL PASAR_KURS')
80alter table Pasazer
81 drop constraint "FK_PASAZER_BYL PASAR_KURS"
82go
83
84if exists (select 1
85 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
86 where r.fkeyid = object_id('Pasazer') and o.name = 'FK_PASAZER_KUPIONY P_BILET')
87alter table Pasazer
88 drop constraint "FK_PASAZER_KUPIONY P_BILET"
89go
90
91if exists (select 1
92 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
93 where r.fkeyid = object_id('Przystanek') and o.name = 'FK_PRZYSTAN_TO JEST_MIEJSCOW')
94alter table Przystanek
95 drop constraint "FK_PRZYSTAN_TO JEST_MIEJSCOW"
96go
97
98if exists (select 1
99 from sysobjects
100 where id = object_id('Autokar')
101 and type = 'U')
102 drop table Autokar
103go
104
105if exists (select 1
106 from sysindexes
107 where id = object_id('Bilet')
108 and name = 'Rodzaju_FK'
109 and indid > 0
110 and indid < 255)
111 drop index Bilet.Rodzaju_FK
112go
113
114if exists (select 1
115 from sysobjects
116 where id = object_id('Bilet')
117 and type = 'U')
118 drop table Bilet
119go
120
121if exists (select 1
122 from sysindexes
123 where id = object_id('"Jest na trasie"')
124 and name = 'Jest na trasie2_FK'
125 and indid > 0
126 and indid < 255)
127 drop index "Jest na trasie"."Jest na trasie2_FK"
128go
129
130if exists (select 1
131 from sysindexes
132 where id = object_id('"Jest na trasie"')
133 and name = 'Jest na trasie_FK'
134 and indid > 0
135 and indid < 255)
136 drop index "Jest na trasie"."Jest na trasie_FK"
137go
138
139if exists (select 1
140 from sysobjects
141 where id = object_id('"Jest na trasie"')
142 and type = 'U')
143 drop table "Jest na trasie"
144go
145
146if exists (select 1
147 from sysindexes
148 where id = object_id('"Jest przypisana do"')
149 and name = 'Jest przypisana do2_FK'
150 and indid > 0
151 and indid < 255)
152 drop index "Jest przypisana do"."Jest przypisana do2_FK"
153go
154
155if exists (select 1
156 from sysindexes
157 where id = object_id('"Jest przypisana do"')
158 and name = 'Jest przypisana do_FK'
159 and indid > 0
160 and indid < 255)
161 drop index "Jest przypisana do"."Jest przypisana do_FK"
162go
163
164if exists (select 1
165 from sysobjects
166 where id = object_id('"Jest przypisana do"')
167 and type = 'U')
168 drop table "Jest przypisana do"
169go
170
171if exists (select 1
172 from sysobjects
173 where id = object_id('Kierowca')
174 and type = 'U')
175 drop table Kierowca
176go
177
178if exists (select 1
179 from sysindexes
180 where id = object_id('Kierunek')
181 and name = 'Kończy w_FK'
182 and indid > 0
183 and indid < 255)
184 drop index Kierunek."Kończy w_FK"
185go
186
187if exists (select 1
188 from sysindexes
189 where id = object_id('Kierunek')
190 and name = 'Startuje z_FK'
191 and indid > 0
192 and indid < 255)
193 drop index Kierunek."Startuje z_FK"
194go
195
196if exists (select 1
197 from sysobjects
198 where id = object_id('Kierunek')
199 and type = 'U')
200 drop table Kierunek
201go
202
203if exists (select 1
204 from sysindexes
205 where id = object_id('Kurs')
206 and name = 'Relationship_8_FK'
207 and indid > 0
208 and indid < 255)
209 drop index Kurs.Relationship_8_FK
210go
211
212if exists (select 1
213 from sysindexes
214 where id = object_id('Kurs')
215 and name = 'jest uzywany na_FK'
216 and indid > 0
217 and indid < 255)
218 drop index Kurs."jest uzywany na_FK"
219go
220
221if exists (select 1
222 from sysindexes
223 where id = object_id('Kurs')
224 and name = 'Jest kierowca na_FK'
225 and indid > 0
226 and indid < 255)
227 drop index Kurs."Jest kierowca na_FK"
228go
229
230if exists (select 1
231 from sysobjects
232 where id = object_id('Kurs')
233 and type = 'U')
234 drop table Kurs
235go
236
237if exists (select 1
238 from sysobjects
239 where id = object_id('Miejscowosc')
240 and type = 'U')
241 drop table Miejscowosc
242go
243
244if exists (select 1
245 from sysindexes
246 where id = object_id('Pasazer')
247 and name = 'Byl pasarzerem kursu_FK'
248 and indid > 0
249 and indid < 255)
250 drop index Pasazer."Byl pasarzerem kursu_FK"
251go
252
253if exists (select 1
254 from sysindexes
255 where id = object_id('Pasazer')
256 and name = 'Kupiony przez_FK'
257 and indid > 0
258 and indid < 255)
259 drop index Pasazer."Kupiony przez_FK"
260go
261
262if exists (select 1
263 from sysobjects
264 where id = object_id('Pasazer')
265 and type = 'U')
266 drop table Pasazer
267go
268
269if exists (select 1
270 from sysindexes
271 where id = object_id('Przystanek')
272 and name = 'To jest_FK'
273 and indid > 0
274 and indid < 255)
275 drop index Przystanek."To jest_FK"
276go
277
278if exists (select 1
279 from sysobjects
280 where id = object_id('Przystanek')
281 and type = 'U')
282 drop table Przystanek
283go
284
285if exists (select 1
286 from sysobjects
287 where id = object_id('Rodzaj')
288 and type = 'U')
289 drop table Rodzaj
290go
291
292if exists (select 1
293 from sysobjects
294 where id = object_id('Trasa')
295 and type = 'U')
296 drop table Trasa
297go
298
299/*==============================================================*/
300/* Table: Autokar */
301/*==============================================================*/
302create table Autokar (
303 Autobus_ID numeric not null,
304 Model char(15) null,
305 Marka char(15) null,
306 Rejestracja char(10) null,
307 constraint PK_AUTOKAR primary key (Autobus_ID)
308)
309go
310
311/*==============================================================*/
312/* Table: Bilet */
313/*==============================================================*/
314create table Bilet (
315 Bilet_ID numeric not null,
316 NazwaBiletu char(15) not null,
317 constraint PK_BILET primary key (Bilet_ID)
318)
319go
320
321/*==============================================================*/
322/* Index: Rodzaju_FK */
323/*==============================================================*/
324
325
326
327
328create nonclustered index Rodzaju_FK on Bilet (NazwaBiletu ASC)
329go
330
331/*==============================================================*/
332/* Table: "Jest na trasie" */
333/*==============================================================*/
334create table "Jest na trasie" (
335 Trasa_ID numeric not null,
336 Przystanek_ID numeric not null,
337 constraint "PK_JEST NA TRASIE" primary key (Trasa_ID, Przystanek_ID)
338)
339go
340
341/*==============================================================*/
342/* Index: "Jest na trasie_FK" */
343/*==============================================================*/
344
345
346
347
348create nonclustered index "Jest na trasie_FK" on "Jest na trasie" (Trasa_ID ASC)
349go
350
351/*==============================================================*/
352/* Index: "Jest na trasie2_FK" */
353/*==============================================================*/
354
355
356
357
358create nonclustered index "Jest na trasie2_FK" on "Jest na trasie" (Przystanek_ID ASC)
359go
360
361/*==============================================================*/
362/* Table: "Jest przypisana do" */
363/*==============================================================*/
364create table "Jest przypisana do" (
365 Kurs_ID numeric not null,
366 Trasa_ID numeric not null,
367 constraint "PK_JEST PRZYPISANA DO" primary key (Kurs_ID, Trasa_ID)
368)
369go
370
371/*==============================================================*/
372/* Index: "Jest przypisana do_FK" */
373/*==============================================================*/
374
375
376
377
378create nonclustered index "Jest przypisana do_FK" on "Jest przypisana do" (Kurs_ID ASC)
379go
380
381/*==============================================================*/
382/* Index: "Jest przypisana do2_FK" */
383/*==============================================================*/
384
385
386
387
388create nonclustered index "Jest przypisana do2_FK" on "Jest przypisana do" (Trasa_ID ASC)
389go
390
391/*==============================================================*/
392/* Table: Kierowca */
393/*==============================================================*/
394create table Kierowca (
395 Kierowca_ID numeric not null,
396 Imie char(15) null,
397 Nazwisko char(15) null,
398 DataZatrudnienia datetime null,
399 constraint PK_KIEROWCA primary key (Kierowca_ID)
400)
401go
402
403/*==============================================================*/
404/* Table: Kierunek */
405/*==============================================================*/
406create table Kierunek (
407 Kierunek_ID numeric not null,
408 NazwaMiasta char(20) not null,
409 Mie_NazwaMiasta char(20) not null,
410 constraint PK_KIERUNEK primary key (Kierunek_ID)
411)
412go
413
414/*==============================================================*/
415/* Index: "Startuje z_FK" */
416/*==============================================================*/
417
418
419
420
421create nonclustered index "Startuje z_FK" on Kierunek (Mie_NazwaMiasta ASC)
422go
423
424/*==============================================================*/
425/* Index: "Kończy w_FK" */
426/*==============================================================*/
427
428
429
430
431create nonclustered index "Kończy w_FK" on Kierunek (NazwaMiasta ASC)
432go
433
434/*==============================================================*/
435/* Table: Kurs */
436/*==============================================================*/
437create table Kurs (
438 Kurs_ID numeric not null,
439 Kierowca_ID numeric not null,
440 Kierunek_ID numeric not null,
441 Autobus_ID numeric not null,
442 Data datetime null,
443 Odjazd datetime null,
444 Przyjazd datetime null,
445 constraint PK_KURS primary key (Kurs_ID)
446)
447go
448
449/*==============================================================*/
450/* Index: "Jest kierowca na_FK" */
451/*==============================================================*/
452
453
454
455
456create nonclustered index "Jest kierowca na_FK" on Kurs (Kierowca_ID ASC)
457go
458
459/*==============================================================*/
460/* Index: "jest uzywany na_FK" */
461/*==============================================================*/
462
463
464
465
466create nonclustered index "jest uzywany na_FK" on Kurs (Autobus_ID ASC)
467go
468
469/*==============================================================*/
470/* Index: Relationship_8_FK */
471/*==============================================================*/
472
473
474
475
476create nonclustered index Relationship_8_FK on Kurs (Kierunek_ID ASC)
477go
478
479/*==============================================================*/
480/* Table: Miejscowosc */
481/*==============================================================*/
482create table Miejscowosc (
483 NazwaMiasta char(20) not null,
484 constraint PK_MIEJSCOWOSC primary key (NazwaMiasta)
485)
486go
487
488/*==============================================================*/
489/* Table: Pasazer */
490/*==============================================================*/
491create table Pasazer (
492 Pasazer_ID numeric not null,
493 Kurs_ID numeric not null,
494 Bilet_ID numeric not null,
495 constraint PK_PASAZER primary key (Pasazer_ID)
496)
497go
498
499/*==============================================================*/
500/* Index: "Kupiony przez_FK" */
501/*==============================================================*/
502
503
504
505
506create nonclustered index "Kupiony przez_FK" on Pasazer (Bilet_ID ASC)
507go
508
509/*==============================================================*/
510/* Index: "Byl pasarzerem kursu_FK" */
511/*==============================================================*/
512
513
514
515
516create nonclustered index "Byl pasarzerem kursu_FK" on Pasazer (Kurs_ID ASC)
517go
518
519/*==============================================================*/
520/* Table: Przystanek */
521/*==============================================================*/
522create table Przystanek (
523 Przystanek_ID numeric not null,
524 NazwaMiasta char(20) not null,
525 constraint PK_PRZYSTANEK primary key (Przystanek_ID)
526)
527go
528
529/*==============================================================*/
530/* Index: "To jest_FK" */
531/*==============================================================*/
532
533
534
535
536create nonclustered index "To jest_FK" on Przystanek (NazwaMiasta ASC)
537go
538
539/*==============================================================*/
540/* Table: Rodzaj */
541/*==============================================================*/
542create table Rodzaj (
543 NazwaBiletu char(15) not null,
544 constraint PK_RODZAJ primary key (NazwaBiletu)
545)
546go
547
548/*==============================================================*/
549/* Table: Trasa */
550/*==============================================================*/
551create table Trasa (
552 Trasa_ID numeric not null,
553 constraint PK_TRASA primary key (Trasa_ID)
554)
555go
556
557alter table Bilet
558 add constraint FK_BILET_RODZAJU_RODZAJ foreign key (NazwaBiletu)
559 references Rodzaj (NazwaBiletu)
560go
561
562alter table "Jest na trasie"
563 add constraint "FK_JEST NA _JEST NA T_TRASA" foreign key (Trasa_ID)
564 references Trasa (Trasa_ID)
565go
566
567alter table "Jest na trasie"
568 add constraint "FK_JEST NA _JEST NA T_PRZYSTAN" foreign key (Przystanek_ID)
569 references Przystanek (Przystanek_ID)
570go
571
572alter table "Jest przypisana do"
573 add constraint "FK_JEST PRZ_JEST PRZY_KURS" foreign key (Kurs_ID)
574 references Kurs (Kurs_ID)
575go
576
577alter table "Jest przypisana do"
578 add constraint "FK_JEST PRZ_JEST PRZY_TRASA" foreign key (Trasa_ID)
579 references Trasa (Trasa_ID)
580go
581
582alter table Kierunek
583 add constraint "FK_KIERUNEK_KOŃCZY W_MIEJSCOW" foreign key (NazwaMiasta)
584 references Miejscowosc (NazwaMiasta)
585go
586
587alter table Kierunek
588 add constraint "FK_KIERUNEK_STARTUJE _MIEJSCOW" foreign key (Mie_NazwaMiasta)
589 references Miejscowosc (NazwaMiasta)
590go
591
592alter table Kurs
593 add constraint "FK_KURS_JEST KIER_KIEROWCA" foreign key (Kierowca_ID)
594 references Kierowca (Kierowca_ID)
595go
596
597alter table Kurs
598 add constraint FK_KURS_RELATIONS_KIERUNEK foreign key (Kierunek_ID)
599 references Kierunek (Kierunek_ID)
600go
601
602alter table Kurs
603 add constraint "FK_KURS_JEST UZYW_AUTOKAR" foreign key (Autobus_ID)
604 references Autokar (Autobus_ID)
605go
606
607alter table Pasazer
608 add constraint "FK_PASAZER_BYL PASAR_KURS" foreign key (Kurs_ID)
609 references Kurs (Kurs_ID)
610go
611
612alter table Pasazer
613 add constraint "FK_PASAZER_KUPIONY P_BILET" foreign key (Bilet_ID)
614 references Bilet (Bilet_ID)
615go
616
617alter table Przystanek
618 add constraint "FK_PRZYSTAN_TO JEST_MIEJSCOW" foreign key (NazwaMiasta)
619 references Miejscowosc (NazwaMiasta)
620go