· 9 years ago · Nov 30, 2016, 01:10 AM
1drop table if exists CARRO;
2
3drop table if exists CORRIDAS;
4
5drop table if exists EQUIPA;
6
7drop table if exists FUNCIONARIOS;
8
9drop table if exists PENALIZACOES;
10
11drop table if exists PILOTOS;
12
13drop table if exists PISTA;
14
15drop table if exists PONTUACAO;
16
17drop table if exists RESULTADOS;
18
19drop table if exists RESULTADOSEQUIPAS;
20
21drop table if exists RESULTADOSPILOTOS;
22
23drop table if exists TIPOPENALIZACAO;
24
25drop table if exists TRABALHO;
26
27/*==============================================================*/
28/* Table: CARRO */
29/*==============================================================*/
30create table CARRO
31(
32 IDCARRO varchar(10) not null,
33 CHASI varchar(10) not null,
34 POWERUNIT varchar(25) not null,
35 constraint PK_CARRO primary key (IDCARRO)
36);
37
38/*==============================================================*/
39/* Table: PISTA */
40/*==============================================================*/
41create table PISTA
42(
43 IDPISTA varchar(5) not null,
44 NOME varchar(50) not null,
45 PAIS varchar(25) not null,
46 CIDADE varchar(25) not null,
47 DISTANCIA varchar(6) not null,
48 ANOENTRADA varchar(4) not null,
49 LAPRECORD varchar(10) not null,
50 constraint PK_PISTA primary key (IDPISTA)
51);
52
53/*==============================================================*/
54/* Table: CORRIDAS */
55/*==============================================================*/
56create table CORRIDAS
57(
58 IDCORRIDA varchar(10) not null,
59 IDPISTA varchar(5) not null,
60 NOME varchar(50) not null,
61 DATA date not null,
62 NVOLTAS varchar(4) not null,
63 DISTANCIA varchar(6) null,
64 constraint PK_CORRIDAS primary key (IDCORRIDA),
65 constraint FK_CORRIDAS_RELATIONS_PISTA foreign key (IDPISTA)
66 references PISTA (IDPISTA)
67 on update restrict
68 on delete restrict
69);
70
71/*==============================================================*/
72/* Table: EQUIPA */
73/*==============================================================*/
74create table EQUIPA
75(
76 IDEQUIPA varchar(10) not null,
77 IDMARCA varchar(10) not null,
78 IDCARRO varchar(10) null,
79 NOMECOMPLETO varchar(50) not null,
80 NOME varchar(50) not null,
81 SEDE varchar(50) not null,
82 ANOENTRADA varchar(4) not null,
83 POSICAOMALTA varchar(7) not null,
84 POLEPOSITION varchar(3) not null,
85 FASTESTLAPS varchar(3) not null,
86 constraint PK_EQUIPA primary key (IDEQUIPA),
87 constraint FK_EQUIPA_RELATIONS_CARRO foreign key (IDCARRO)
88 references CARRO (IDCARRO)
89 on update restrict
90 on delete restrict
91);
92
93/*==============================================================*/
94/* Table: FUNCIONARIOS */
95/*==============================================================*/
96create table FUNCIONARIOS
97(
98 IDFUNC varchar(10) not null,
99 IDEQUIPA varchar(10) not null,
100 NOME varchar(50) not null,
101 MORADA varchar(50) not null,
102 CC varchar(8) not null,
103 NIF varchar(9) not null,
104 TELEMOVEL varchar(9) not null,
105 TELEFONE varchar(9) null,
106 GENERO varchar(1) not null,
107 DATANASC date not null,
108 constraint PK_FUNCIONARIOS primary key (IDFUNC),
109 constraint FK_FUNCIONA_RELATIONS_EQUIPA foreign key (IDEQUIPA)
110 references EQUIPA (IDEQUIPA)
111 on update restrict
112 on delete restrict
113);
114
115/*==============================================================*/
116/* Table: TIPOPENALIZACAO */
117/*==============================================================*/
118create table TIPOPENALIZACAO
119(
120 TIPOPEN varchar(2) not null,
121 DESCRICAO varchar(50) not null,
122 constraint PK_TIPOPENALIZACAO primary key (TIPOPEN)
123);
124
125/*==============================================================*/
126/* Table: PILOTOS */
127/*==============================================================*/
128create table PILOTOS
129(
130 IDFUNC varchar(10) not null,
131 NUM varchar(2) not null,
132 ESTATUTO varchar(10) not null,
133 PODIUMS varchar(3) not null,
134 POINTS varchar(4) not null,
135 ENTRADAS varchar(3) not null,
136 WC varchar(2) not null,
137 POSICAOMALTA varchar(7) not null,
138 POLEPOSITION varchar(3) not null,
139 LOCALNASC varchar(50) not null,
140 constraint PK_PILOTOS primary key clustered (IDFUNC),
141 constraint FK_PILOTOS_INHERITAN_FUNCIONA foreign key (IDFUNC)
142 references FUNCIONARIOS (IDFUNC)
143 on update restrict
144 on delete restrict
145);
146
147/*==============================================================*/
148/* Table: PENALIZACOES */
149/*==============================================================*/
150create table PENALIZACOES
151(
152 IDPEN varchar(10) not null,
153 TIPOPEN varchar(2) not null,
154 IDCORRIDA varchar(10) not null,
155 IDFUNC varchar(10) not null,
156 constraint PK_PENALIZACOES primary key (IDPEN),
157 constraint FK_PENALIZA_RELATIONS_TIPOPENA foreign key (TIPOPEN)
158 references TIPOPENALIZACAO (TIPOPEN)
159 on update restrict
160 on delete restrict,
161 constraint FK_PENALIZA_RELATIONS_PILOTOS foreign key (IDFUNC)
162 references PILOTOS (IDFUNC)
163 on update restrict
164 on delete restrict,
165 constraint FK_PENALIZA_RELATIONS_CORRIDAS foreign key (IDCORRIDA)
166 references CORRIDAS (IDCORRIDA)
167 on update restrict
168 on delete restrict
169);
170
171/*==============================================================*/
172/* Table: PONTUACAO */
173/*==============================================================*/
174create table PONTUACAO
175(
176 IDPONT varchar(2) not null,
177 COLOCACAO varchar(2) not null,
178 PONTOS varchar(2) not null,
179 constraint PK_PONTUACAO primary key (IDPONT)
180);
181
182/*==============================================================*/
183/* Table: RESULTADOS */
184/*==============================================================*/
185create table RESULTADOS
186(
187 POSICAO varchar(2) not null,
188 IDPONT varchar(2) not null,
189 IDCORRIDA varchar(10) not null,
190 TEMPO varchar(10) not null,
191 constraint PK_RESULTADOS primary key (POSICAO),
192 constraint FK_RESULTAD_RELATIONS_PONTUACA foreign key (IDPONT)
193 references PONTUACAO (IDPONT)
194 on update restrict
195 on delete restrict,
196 constraint FK_RESULTAD_RELATIONS_CORRIDAS foreign key (IDCORRIDA)
197 references CORRIDAS (IDCORRIDA)
198 on update restrict
199 on delete restrict
200);
201
202/*==============================================================*/
203/* Table: RESULTADOSEQUIPAS */
204/*==============================================================*/
205create table RESULTADOSEQUIPAS
206(
207 POSICAO varchar(2) not null,
208 POSICAOE varchar(2) not null,
209 IDPONT varchar(2) null,
210 IDCORRIDA varchar(10) null,
211 TEMPO varchar(10) not null,
212 PONTOS varchar(2) not null,
213 constraint PK_RESULTADOSEQUIPAS primary key (POSICAO, POSICAOE),
214 constraint FK_RESULTAD_INHERITAN_RESULTAD foreign key (POSICAO)
215 references RESULTADOS (POSICAO)
216 on update restrict
217 on delete restrict
218);
219
220/*==============================================================*/
221/* Table: RESULTADOSPILOTOS */
222/*==============================================================*/
223create table RESULTADOSPILOTOS
224(
225 POSICAO varchar(2) not null,
226 POSICAOP varchar(2) not null,
227 IDPONT varchar(2) null,
228 IDCORRIDA varchar(10) null,
229 TEMPO varchar(10) not null,
230 PONTOS varchar(2) not null,
231 constraint PK_RESULTADOSPILOTOS primary key (POSICAO, POSICAOP),
232 constraint FK_RESULTAD_INHERITAN_RESULTAD foreign key (POSICAO)
233 references RESULTADOS (POSICAO)
234 on update restrict
235 on delete restrict
236);
237
238/*==============================================================*/
239/* Table: TRABALHO */
240/*==============================================================*/
241create table TRABALHO
242(
243 IDFUNC varchar(10) not null,
244 IDTRABALHO varchar(5) not null,
245 TRABALHO varchar(25) not null,
246 DESCRICAO varchar(50) not null,
247 constraint PK_TRABALHO primary key (IDFUNC, IDTRABALHO),
248 constraint FK_TRABALHO_INHERITAN_FUNCIONA foreign key (IDFUNC)
249 references FUNCIONARIOS (IDFUNC)
250 on update restrict
251 on delete restrict
252);