· 8 years ago · Jun 11, 2018, 01:02 PM
1/*==============================================================*/
2/* DBMS name: Microsoft SQL Server 2008 */
3/* Created on: 11.06.2018 14:58:09 */
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('Relationship_3') and o.name = 'FK_RELATION_RELATIONS_RODZAJ_B')
10alter table Relationship_3
11 drop constraint FK_RELATION_RELATIONS_RODZAJ_B
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('Relationship_3') and o.name = 'FK_RELATION_RELATIONS_PRZYJECI')
17alter table Relationship_3
18 drop constraint FK_RELATION_RELATIONS_PRZYJECI
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('przyjecie') and o.name = 'FK_PRZYJECI_RELATIONS_LEKARZ')
24alter table przyjecie
25 drop constraint FK_PRZYJECI_RELATIONS_LEKARZ
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('przyjecie') and o.name = 'FK_PRZYJECI_RELATIONS_PACJENT')
31alter table przyjecie
32 drop constraint FK_PRZYJECI_RELATIONS_PACJENT
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('przyjecie') and o.name = 'FK_PRZYJECI_RELATIONS_OPERACJA')
38alter table przyjecie
39 drop constraint FK_PRZYJECI_RELATIONS_OPERACJA
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('rodzaj_badania') and o.name = 'FK_RODZAJ_B_RELATIONS_GRUPA_BA')
45alter table rodzaj_badania
46 drop constraint FK_RODZAJ_B_RELATIONS_GRUPA_BA
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('rodzaj_badania') and o.name = 'FK_RODZAJ_B_RELATIONS_JEDNOSTK')
52alter table rodzaj_badania
53 drop constraint FK_RODZAJ_B_RELATIONS_JEDNOSTK
54go
55
56if exists (select 1
57 from sysindexes
58 where id = object_id('Relationship_3')
59 and name = 'Relationship_4_FK'
60 and indid > 0
61 and indid < 255)
62 drop index Relationship_3.Relationship_4_FK
63go
64
65if exists (select 1
66 from sysindexes
67 where id = object_id('Relationship_3')
68 and name = 'Relationship_3_FK'
69 and indid > 0
70 and indid < 255)
71 drop index Relationship_3.Relationship_3_FK
72go
73
74if exists (select 1
75 from sysobjects
76 where id = object_id('Relationship_3')
77 and type = 'U')
78 drop table Relationship_3
79go
80
81if exists (select 1
82 from sysobjects
83 where id = object_id('grupa_badania')
84 and type = 'U')
85 drop table grupa_badania
86go
87
88if exists (select 1
89 from sysobjects
90 where id = object_id('jednostka')
91 and type = 'U')
92 drop table jednostka
93go
94
95if exists (select 1
96 from sysobjects
97 where id = object_id('lekarz')
98 and type = 'U')
99 drop table lekarz
100go
101
102if exists (select 1
103 from sysobjects
104 where id = object_id('operacja')
105 and type = 'U')
106 drop table operacja
107go
108
109if exists (select 1
110 from sysobjects
111 where id = object_id('pacjent')
112 and type = 'U')
113 drop table pacjent
114go
115
116if exists (select 1
117 from sysindexes
118 where id = object_id('przyjecie')
119 and name = 'Relationship_7_FK'
120 and indid > 0
121 and indid < 255)
122 drop index przyjecie.Relationship_7_FK
123go
124
125if exists (select 1
126 from sysindexes
127 where id = object_id('przyjecie')
128 and name = 'Relationship_6_FK'
129 and indid > 0
130 and indid < 255)
131 drop index przyjecie.Relationship_6_FK
132go
133
134if exists (select 1
135 from sysindexes
136 where id = object_id('przyjecie')
137 and name = 'Relationship_5_FK'
138 and indid > 0
139 and indid < 255)
140 drop index przyjecie.Relationship_5_FK
141go
142
143if exists (select 1
144 from sysobjects
145 where id = object_id('przyjecie')
146 and type = 'U')
147 drop table przyjecie
148go
149
150if exists (select 1
151 from sysindexes
152 where id = object_id('rodzaj_badania')
153 and name = 'Relationship_2_FK'
154 and indid > 0
155 and indid < 255)
156 drop index rodzaj_badania.Relationship_2_FK
157go
158
159if exists (select 1
160 from sysindexes
161 where id = object_id('rodzaj_badania')
162 and name = 'Relationship_1_FK'
163 and indid > 0
164 and indid < 255)
165 drop index rodzaj_badania.Relationship_1_FK
166go
167
168if exists (select 1
169 from sysobjects
170 where id = object_id('rodzaj_badania')
171 and type = 'U')
172 drop table rodzaj_badania
173go
174
175/*==============================================================*/
176/* Table: Relationship_3 */
177/*==============================================================*/
178create table Relationship_3 (
179 id_rodzaj_badania int not null,
180 id_przyjecie int not null,
181 constraint PK_RELATIONSHIP_3 primary key (id_rodzaj_badania, id_przyjecie)
182)
183go
184
185/*==============================================================*/
186/* Index: Relationship_3_FK */
187/*==============================================================*/
188create index Relationship_3_FK on Relationship_3 (
189id_rodzaj_badania ASC
190)
191go
192
193/*==============================================================*/
194/* Index: Relationship_4_FK */
195/*==============================================================*/
196create index Relationship_4_FK on Relationship_3 (
197id_przyjecie ASC
198)
199go
200
201/*==============================================================*/
202/* Table: grupa_badania */
203/*==============================================================*/
204create table grupa_badania (
205 id_grupa_badania int not null,
206 nazwa_grupa_badania varchar(30) null,
207 constraint PK_GRUPA_BADANIA primary key nonclustered (id_grupa_badania)
208)
209go
210
211/*==============================================================*/
212/* Table: jednostka */
213/*==============================================================*/
214create table jednostka (
215 id_jednostka int not null,
216 nazwa_jednostka varchar(30) null,
217 constraint PK_JEDNOSTKA primary key nonclustered (id_jednostka)
218)
219go
220
221/*==============================================================*/
222/* Table: lekarz */
223/*==============================================================*/
224create table lekarz (
225 id_lekarz int not null,
226 identyfikator_lekarz varchar(30) null,
227 constraint PK_LEKARZ primary key nonclustered (id_lekarz)
228)
229go
230
231/*==============================================================*/
232/* Table: operacja */
233/*==============================================================*/
234create table operacja (
235 id_operacja int not null,
236 identyfikator_operacja varchar(15) null,
237 constraint PK_OPERACJA primary key nonclustered (id_operacja)
238)
239go
240
241/*==============================================================*/
242/* Table: pacjent */
243/*==============================================================*/
244create table pacjent (
245 id_pacjent int not null,
246 nr_pacjent int null,
247 imie varchar(30) null,
248 nazwisko varchar(30) null,
249 data_urodzenia datetime null,
250 constraint PK_PACJENT primary key nonclustered (id_pacjent)
251)
252go
253
254/*==============================================================*/
255/* Table: przyjecie */
256/*==============================================================*/
257create table przyjecie (
258 id_przyjecie int not null,
259 id_pacjent int not null,
260 id_lekarz int null,
261 id_operacja int null,
262 data_przyjecia datetime null,
263 data_operacja datetime null,
264 constraint PK_PRZYJECIE primary key nonclustered (id_przyjecie)
265)
266go
267
268/*==============================================================*/
269/* Index: Relationship_5_FK */
270/*==============================================================*/
271create index Relationship_5_FK on przyjecie (
272id_lekarz ASC
273)
274go
275
276/*==============================================================*/
277/* Index: Relationship_6_FK */
278/*==============================================================*/
279create index Relationship_6_FK on przyjecie (
280id_pacjent ASC
281)
282go
283
284/*==============================================================*/
285/* Index: Relationship_7_FK */
286/*==============================================================*/
287create index Relationship_7_FK on przyjecie (
288id_operacja ASC
289)
290go
291
292/*==============================================================*/
293/* Table: rodzaj_badania */
294/*==============================================================*/
295create table rodzaj_badania (
296 id_rodzaj_badania int not null,
297 id_jednostka int not null,
298 id_grupa_badania int not null,
299 nazwa_rodzaj_badania varchar(30) null,
300 dolna_wartosc decimal(6,2) null,
301 gorna_wartosc decimal(6,2) null,
302 constraint PK_RODZAJ_BADANIA primary key nonclustered (id_rodzaj_badania)
303)
304go
305
306/*==============================================================*/
307/* Index: Relationship_1_FK */
308/*==============================================================*/
309create index Relationship_1_FK on rodzaj_badania (
310id_grupa_badania ASC
311)
312go
313
314/*==============================================================*/
315/* Index: Relationship_2_FK */
316/*==============================================================*/
317create index Relationship_2_FK on rodzaj_badania (
318id_jednostka ASC
319)
320go
321
322alter table Relationship_3
323 add constraint FK_RELATION_RELATIONS_RODZAJ_B foreign key (id_rodzaj_badania)
324 references rodzaj_badania (id_rodzaj_badania)
325go
326
327alter table Relationship_3
328 add constraint FK_RELATION_RELATIONS_PRZYJECI foreign key (id_przyjecie)
329 references przyjecie (id_przyjecie)
330go
331
332alter table przyjecie
333 add constraint FK_PRZYJECI_RELATIONS_LEKARZ foreign key (id_lekarz)
334 references lekarz (id_lekarz)
335go
336
337alter table przyjecie
338 add constraint FK_PRZYJECI_RELATIONS_PACJENT foreign key (id_pacjent)
339 references pacjent (id_pacjent)
340go
341
342alter table przyjecie
343 add constraint FK_PRZYJECI_RELATIONS_OPERACJA foreign key (id_operacja)
344 references operacja (id_operacja)
345go
346
347alter table rodzaj_badania
348 add constraint FK_RODZAJ_B_RELATIONS_GRUPA_BA foreign key (id_grupa_badania)
349 references grupa_badania (id_grupa_badania)
350go
351
352alter table rodzaj_badania
353 add constraint FK_RODZAJ_B_RELATIONS_JEDNOSTK foreign key (id_jednostka)
354 references jednostka (id_jednostka)
355go