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