· 8 years ago · Jun 01, 2018, 11:02 PM
1-- Change context to DBProject
2USE Projecto
3
4
5--create Regiao
6if not exists (select * from dbo.sysobjects
7 where id = object_id(N'[dbo].[Regiao]') )
8begin
9 CREATE TABLE Regiao (
10 IDRegiao int NOT NULL
11 CHECK (IDRegiao >= 1), -- constraint type: check
12 Nome nvarchar(40) NOT NULL,
13
14 CONSTRAINT PK_IDRegiao PRIMARY KEY (IDRegiao), -- constraint type: primary key
15 CONSTRAINT U_Nome UNIQUE (Nome) -- constraint type: unique
16 );
17
18end
19----------------------------------------------------------------------
20--create CentroHospitalar
21if not exists (select * from dbo.sysobjects
22 where id = object_id(N'[dbo].[CentroHospitalar]') )
23begin
24 CREATE TABLE CentroHospitalar (
25 IDCentroHospitalar int NOT NULL
26 CHECK (IDCentroHospitalar >= 1),
27 Nome nvarchar (50) NOT NULL ,
28 IDRegiao int NOT NULL,
29
30 CONSTRAINT PK_IDCentroHospitalar PRIMARY KEY (IDCentroHospitalar),
31 CONSTRAINT FK_IDRegiao FOREIGN KEY (IDRegiao)
32 REFERENCES Regiao(IDRegiao) ON UPDATE CASCADE
33 );
34end
35----------------------------------------------------------------------
36--create Hospital
37if not exists (select * from dbo.sysobjects
38 where id = object_id(N'[dbo].[Hospital]') )
39begin
40 CREATE TABLE Hospital (
41 IDHospital int NOT NULL
42 CHECK (IDHospital >= 1),
43 IDCentroHospitalar int NOT NULL,
44 Nome nvarchar (50) NOT NULL,
45 Localidade nvarchar (15) NOT NULL,
46
47 CONSTRAINT PK_IDHospital PRIMARY KEY (IDHospital),
48 CONSTRAINT FK_IDCentroHospitalar FOREIGN KEY (IDCentroHospitalar)
49 REFERENCES CentroHospitalar(IDCentroHospitalar) ON UPDATE CASCADE,
50 );
51end
52----------------------------------------------------------------------
53--create Servico
54if not exists (select * from dbo.sysobjects
55 where id = object_id(N'[dbo].[Servico]') )
56begin
57 CREATE TABLE Servico (
58 IDServico int NOT NULL
59 CHECK (IDServico >= 1),
60 Nome nvarchar (30) NOT NULL ,
61 Descricao nvarchar (100) NOT NULL,
62
63 CONSTRAINT PK_IDSTAFF PRIMARY KEY (IDServico),
64 );
65end
66----------------------------------------------------------------------
67--create HospitalServico
68if not exists (select * from dbo.sysobjects
69 where id = object_id(N'[dbo].[HospitalServico]') )
70begin
71 CREATE TABLE HospitalServico (
72 IDHospitalServico int NOT NULL
73 CHECK (IDHospitalServico >= 1),
74 IDServico int NOT NULL,
75 IDHospital int NOT NULL,
76
77 CONSTRAINT PK_IDHospitalServico PRIMARY KEY (IDHospitalServico),
78 CONSTRAINT FK_IDServio FOREIGN KEY (IDServico)
79 REFERENCES Servico(IDServico) ON UPDATE CASCADE,
80 CONSTRAINT FK_IDHospital FOREIGN KEY (IDHospital)
81 REFERENCES Hospital(IDHospital) ON UPDATE CASCADE
82 );
83end
84----------------------------------------------------------------------
85--create Colaborador
86if not exists (select * from dbo.sysobjects
87 where id = object_id(N'[dbo].[Colaborador]') )
88begin
89 CREATE TABLE Colaborador (
90 IDColaborador int NOT NULL
91 CHECK (IDColaborador >= 1),
92 Nome nvarchar (50) NOT NULL,
93 Cargo nvarchar (20),
94
95 CONSTRAINT PK_IDColaborador PRIMARY KEY (IDColaborador),
96 );
97end
98----------------------------------------------------------------------
99--Create ColaboradorHospital
100if not exists (select * from dbo.sysobjects
101 where id = object_id(N'[dbo].[ColaboradorHospital]') )
102begin
103 CREATE TABLE ColaboradorHospital (
104 IDColaboradorHospital int NOT NULL
105 CHECK (IDColaboradorHospital >= 1),
106 IDColaborador int Not NULL,
107 IDHospitalServico int NOT NULL,
108
109 CONSTRAINT PK_IDColaboradorHospital PRIMARY KEY (IDColaboradorHospital),
110 CONSTRAINT FK_IDColaborador FOREIGN KEY (IDColaborador)
111 REFERENCES Colaborador (IDColaborador) ON UPDATE CASCADE,
112 CONSTRAINT FK_IDHospitalServico FOREIGN KEY (IDHospitalServico)
113 REFERENCES HospitalServico (IDHospitalServico) ON UPDATE CASCADE
114 );
115end
116----------------------------------------------------------------------
117--create Sala
118if not exists (select * from dbo.sysobjects
119 where id = object_id(N'[dbo].[Sala]') )
120begin
121 CREATE TABLE Sala (
122 IDSala int NOT NULL
123 CHECK (IDSala >= 1),
124 IDHospitalServico int Not NULL,
125 Descricao nvarchar (300),
126
127 CONSTRAINT PK_IDSala PRIMARY KEY (IDSala),
128 CONSTRAINT FK_IDHospitalServico2 FOREIGN KEY (IDHospitalServico)
129 REFERENCES HospitalServico(IDHospitalServico) ON UPDATE CASCADE,
130 );
131end
132----------------------------------------------------------------------
133--Create Utente
134if not exists (select * from dbo.sysobjects
135 where id = object_id(N'[dbo].[Utente]') )
136begin
137 CREATE TABLE Utente (
138 IDUtente int NOT NULL
139 CHECK (IDUtente >= 1),
140 BI int Not NULL,
141 Nome nvarchar (40),
142
143 CONSTRAINT U_BI UNIQUE (BI),
144 CONSTRAINT PK_IDUtente PRIMARY KEY (IDUtente)
145 );
146end
147----------------------------------------------------------------------
148--create Equipamento
149if not exists (select * from dbo.sysobjects
150 where id = object_id(N'[dbo].[Equipamento]') )
151begin
152 CREATE TABLE Equipamento (
153 IDEquipamento int NOT NULL
154 CHECK (IDEquipamento >= 1),
155 Nome nvarchar(40) NOT NULL,
156 Uso int NOT NULL,
157 Vdia int NOT NULL,
158 Vmes int NOT NULL,
159 Vano int NOT NULL,
160
161 CONSTRAINT PK_IDEquipamento PRIMARY KEY (IDEquipamento)
162
163);
164end
165----------------------------------------------------------------------
166--create Atribuicao
167if not exists (select * from dbo.sysobjects
168 where id = object_id(N'[dbo].[Atribuicao]') )
169begin
170 CREATE TABLE Atribuicao (
171 IDAtribuicao int NOT NULL
172 CHECK (IDAtribuicao >= 1),
173 IDSala int NOT NULL,
174 IDEquipamento int NOT NULL,
175
176 CONSTRAINT PK_IDAtribuicao PRIMARY KEY (IDAtribuicao),
177 CONSTRAINT FK_IDSala FOREIGN KEY (IDSala)
178 REFERENCES Sala (IDSala) ON UPDATE CASCADE,
179 CONSTRAINT FK_IDEquipamento FOREIGN KEY (IDEquipamento)
180 REFERENCES Equipamento (IDEquipamento) ON UPDATE CASCADE,
181 );
182end
183----------------------------------------------------------------------
184--create Agenda
185if not exists (select * from dbo.sysobjects
186 where id = object_id(N'[dbo].[Agenda]') )
187begin
188 CREATE TABLE Agenda (
189 IDAgenda int NOT NULL
190 CHECK (IDAgenda >= 1),
191 IDAtribuicao int NOT NULL,
192 dia int NOT NULL,
193 mes int NOT NULL,
194 ano int NOT NULL,
195 hora int NOT NULL,
196
197 CONSTRAINT PK_IDAgendaDia PRIMARY KEY (IDAgenda),
198 CONSTRAINT FK_IDAtribuicao FOREIGN KEY (IDAtribuicao)
199 REFERENCES Atribuicao (IDAtribuicao) ON UPDATE CASCADE,
200 );
201end
202----------------------------------------------------------------------
203--Create Marcacao
204if not exists (select * from dbo.sysobjects
205 where id = object_id(N'[dbo].[Marcacao]') )
206begin
207 CREATE TABLE Marcacao (
208 IDMarcacao int NOT NULL
209 CHECK (IDMarcacao >= 1),
210 IDUtente int Not NULL,
211 IDAgenda int NOT NULL,
212 IDColaboradorHospital int NOT NULL,
213
214 CONSTRAINT PK_IDMarcacao PRIMARY KEY (IDMarcacao),
215 CONSTRAINT FK_IDUtente FOREIGN KEY (IDUtente)
216 REFERENCES Utente(IDUtente) ON UPDATE CASCADE,
217 CONSTRAINT FK_IDAgenda FOREIGN KEY (IDAgenda)
218 REFERENCES Agenda (IDAgenda) ON UPDATE CASCADE,
219 CONSTRAINT FK_IDColaboradorHospital FOREIGN KEY (IDColaboradorHospital)
220 REFERENCES ColaboradorHospital (IDColaboradorHospital)
221 );
222end
223----------------------------------------------------------------------
224--Create EpisodioClinico
225if not exists (select * from dbo.sysobjects
226 where id = object_id(N'[dbo].[EpisodioClinico]') )
227begin
228 CREATE TABLE EpisodioClinico (
229 IDEpisodioClinico int NOT NULL
230 CHECK (IDEpisodioClinico >= 1),
231 IDMarcacao int,
232 IDColaboradorHospital int NOT NULL,
233 notas nvarchar (300),
234 dia int NOT NULL,
235 mes int NOT NULL,
236 ano int NOT NULL,
237 hora int NOT NULL,
238
239 CONSTRAINT PK_IDEpisodioClinico PRIMARY KEY (IDEpisodioClinico),
240 CONSTRAINT FK_IDMarcacao2 FOREIGN KEY (IDMarcacao)
241 REFERENCES Marcacao(IDMarcacao) ON UPDATE CASCADE,
242 CONSTRAINT FK_IColaboradorHospital FOREIGN KEY (IDColaboradorHospital)
243 REFERENCES ColaboradorHospital(IDColaboradorHospital)
244 );
245end
246----------------------------------------------------------------------
247--Create Gera
248if not exists (select * from dbo.sysobjects
249 where id = object_id(N'[dbo].[Gera]') )
250begin
251 CREATE TABLE Gera (
252 IDEpisodioInicial int NOT NULL,
253 IDEpisodioGerado int,
254
255 CONSTRAINT PK_IDEpisodioGerado PRIMARY KEY (IDEpisodioGerado),
256 CONSTRAINT FK_IDEpisodioInicial FOREIGN KEY (IDEpisodioInicial)
257 REFERENCES EpisodioClinico(IDEpisodioClinico) ON UPDATE CASCADE,
258 );
259end
260----------------------------------------------------------------------