Diseño físico de base de datos. Boletín 4

Anoche en una fiesta conociste a Bernie Ecclestone y le convenciste para crear una base de datos para gestionar el campeonato del mundo de Fórmula 1.

Partiendo del siguiente esquema relacional, crea las tablas correspondientes, teniendo en cuenta el tipo y tamaño de los campos, las claves primarias (negrita y subrayado)  y ajenas (cursiva) y las restricciones indicadas. Debes incluir inserciones de registros de prueba para demostrar el funcionamiento correcto de las restricciones.

a) Tabla Pilotos

PILOTOS
Código	Cadena de caracteres, tamaño 3
Nombre	Cadena de caracteres, tamaño 15	Iniciales en mayúsculas
Nacionalidad	Cadena de caracteres, tamaño 15	Brasileña, Española, Inglesa o Alemana
FechaNacimiento	Fecha/Hora
CarreraDebut	Cadena de caracteres, tamaño 20	Los cuatro últimos caracteres son el año y están entre 1990 y 2010.
NombreEscuderia	Cadena de caracteres, tamaño 20

b) Tabla Circuitos

CIRCUITOS
Nombre	Cadena de Caracteres, tamaño 15	En mayúsculas
País	Cadena de Caracteres, tamaño 15
Tipo	Cadena de Caracteres, tamaño 15
Longitud	Numérico hasta 9999'9	Mayor de 2400
Diseñador	Cadena de Caracteres, tamaño 20	Iniciales en mayúsculas

c) Tabla Carreras

CARRERAS
NombreGP	Cadena de Caracteres, tamaño 15
Año	Cadena de Caracteres, tamaño 4	Del siglo XXI
FechaHoraInicio	Fecha/Hora	De Marzo a Noviembre
NumVueltas	Numérico hasta 99
NombreCircuito	Cadena de Caracteres, tamaño 15

d) Tabla Escuderías

ESCUDERIAS
Nombre	Cadena de Caracteres, tamaño 15
Propietario	Cadena de Caracteres, tamaño 15
FabricanteMotor	Cadena de Caracteres, tamaño 15
Sede	Cadena de Caracteres, tamaño 15	Contiene Inglaterra, Alemania o Italia

e) Tabla Resultados

RESULTADOS
NombreGP	Cadena de Caracteres, tamaño 15
Año	Cadena de Caracteres, tamaño 4
CodPiloto	Cadena de Caracteres, tamaño 4
Puesto	Numérico hasta 99
DistanciaGanador	Cadena de Caracteres, tamaño 8

Añade las restricciones siguientes, una vez creadas las tablas e insertados algunos registros:

-                      El código de piloto sólo tiene caracteres numéricos, aunque sigue siendo de tipo Cadena

-                      Distancia al Ganador: Formato '+mm:ss.cc' (siendo mm<10) o Abandono o campo vacío.

-                      FechaHoraInicio: De las 13:00 a las 21:30.

-                      Nombre del Piloto: Obligatorio.

-                      No hay más de un circuito en el mismo país.

-                      Tipo: Urbano o campo vacío.

-                      Deshabilita temporalmente la restricción referente a la sede de la escudería.

CREATE TABLE pilotos (

codigo VARCHAR2(3),

nombre VARCHAR2(15),

nacionalidad VARCHAR2(15),

fechanac DATE,

carreradeb VARCHAR2(20),

nombreesc VARCHAR2(15),

CONSTRAINT PK_pilotos PRIMARY KEY (codigo),

CONSTRAINT FK_nombreesc FOREIGN KEY (nombreesc) REFERENCES escuderias(nombre),

CONSTRAINT CK_nombre CHECK (nombre=INITCAP(nombre)),

CONSTRAINT CK_nacion CHECK (upper(nacionalidad) IN ('BRASILEÑA','ESPAÑOLA','INGLESA','ALEMANA')),

CONSTRAINT CK_carrera CHECK (TO_NUMBER(substr(carreradeb, -4, 4)) BETWEEN 1990 AND 2010)

);

INSERT INTO pilotos VALUES ('111','Nombre','inglesa','02-02-1222','1990','pepe');

INSERT INTO pilotos VALUES ('112','Nombre','española','02-02-1222','1990','pepe2');

INSERT INTO pilotos VALUES ('132','Nombre','inglesa','02-02-1222','1990','pepe3');

INSERT INTO pilotos VALUES ('232','Nombre','inglesa','02-02-1222','1990','pepe3');

INSERT INTO pilotos VALUES ('332','Nombre','inglesa','02-02-1222','1990','pepe3');

INSERT INTO pilotos VALUES ('432','Nombre','inglesa','02-02-1222','1990','pepe3');

CREATE TABLE circuitos (

nombre VARCHAR2(15),

pais VARCHAR2(15),

tipo VARCHAR2(15),

longitud NUMBER(5,1),

disenador VARCHAR2(20),

CONSTRAINT PK_circuitos PRIMARY KEY (nombre),

CONSTRAINT CK_nombre_cir CHECK (nombre=UPPER(nombre)),

CONSTRAINT CK_longitud CHECK (longitud > 2400),

CONSTRAINT CK_disenador CHECK (disenador=INITCAP(disenador))

);

INSERT INTO circuitos VALUES ('MADRID','alemania','urbano',2450,'Falta');

INSERT INTO circuitos VALUES ('ZARA','italia','urb',2450,'Falta');

CREATE TABLE carreras (

nombregp VARCHAR2(15),

ano VARCHAR2(4),

fechahorainicio DATE,

numvueltas NUMBER(2),

nombrecircuito VARCHAR2(15),

CONSTRAINT PK_carreras PRIMARY KEY (nombregp, ano),

CONSTRAINT FK_nomcir FOREIGN KEY (nombrecircuito) REFERENCES circuitos (nombre),

CONSTRAINT CK_ano CHECK (TO_NUMBER(ano) BETWEEN 2001 AND 2100),

CONSTRAINT CK_fechahi CHECK (TO_NUMBER(TO_CHAR(fechahorainicio,'MM')) BETWEEN 03 AND 11)

);

INSERT INTO carreras VALUES ('este','2020','01-03-1993',87,'MADRID');

CREATE TABLE escuderias (

nombre VARCHAR2(15),

propietario VARCHAR2(15),

fabricantemotor VARCHAR2(15),

sede VARCHAR2(15),

CONSTRAINT PK_escuderias PRIMARY KEY (nombre),

CONSTRAINT CK_sede CHECK (upper(sede) IN ('INGLATERRA','ALEMANIA','ITALIA'))

);

INSERT INTO escuderias VALUES ('pepe','manolo','alguien','italia');

INSERT INTO escuderias VALUES ('pepe2','manolo','alguien','italia');

INSERT INTO escuderias VALUES ('pepe3','manolo','alguien','italia');

CREATE TABLE resultados (

nombregp VARCHAR2(15),

ano VARCHAR2(4),

codpiloto VARCHAR2(3),

puesto NUMBER (2),

distancia_ganador VARCHAR2(8),

CONSTRAINT PK_resultados PRIMARY KEY(nombregp, ano, codpiloto),

CONSTRAINT FK_carreras FOREIGN KEY (nombregp, ano) REFERENCES carreras (nombregp, ano),

CONSTRAINT FK_pilotos FOREIGN KEY (codpiloto) REFERENCES pilotos (codigo)

);

INSERT INTO resultados VALUES ('este','2020','332',20,'09');

ALTER TABLE pilotos ADD CONSTRAINT CK_codigo CHECK (substr(codigo,1,1) BETWEEN '0' AND '9') AND (substr(codigo,2,1) BETWEEN '0' AND '9') AND (substr(codigo,3,1) BETWEEN '0' AND '9');

ALTER TABLE pilotos ADD CONSTRAINT CK_codigo CHECK (TO_NUMBER(codigo) BETWEEN 0 AND 999);

ALTER TABLE pilotos ADD CONSTRAINT CK_nombrenulo CHECK (nombre IS NOT NULL);

// esto no funciona!!! probablemente por parentesis

ALTER TABLE carreras ADD CONSTRAINT CK_hora_inicio CHECK (TO_NUMBER(TO_CHAR(fechahorainicio,'hh'))>13)

AND ((TO_NUMBER(TO_CHAR(fechahorainicio,'hh'))<21)

OR ((TO_NUMBER(TO_CHAR(fechahorainicio,'hh'))=21) AND (TO_NUMBER(TO_CHAR(fechahorainicio,'mi'))<=30)))

);

//

ALTER TABLE circuitos ADD CONSTRAINT UK_nombre UNIQUE (pais);

ALTER TABLE circuitos ADD CONSTRAINT CK_tipoalgo CHECK ((upper(tipo)='URBANO') OR (tipo IS NULL));

ALTER TABLE circuitos DROP CONSTRAINT CK_tipoalgo;

ALTER TABLE escuderias DISABLE CONSTRAINT CK_sede;

ALTER TABLE resultados ADD CONSTRAINT CK_distancia CHECK ((upper(distancia_ganador)='ABANDONO')

OR (distancia_ganador IS NULL)

OR (TO_NUMBER(SUBSTR(distancia_ganador,1,2)) < 10));

ALTER TABLE resultados DROP CONSTRAINT CK_distancia;