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

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

Somos los encargados de la administración y explotación de la base de datos del Gran Hipódromo de Andalucía y debemos:

Tabla Caballos

CABALLOS
CodCaballo	CADENA, tamaño 4
Nombre	CADENA, tamaño 20
Peso	NUMERICO de 3	Entre 240 y 300
Fecha Nacimiento	FECHA	Posterior al año 2000
Propietario	CADENA, tamaño 25
Nacionalidad	CADENA, tamaño 20

Tabla Carreras

CARRERAS
CodCarrera	CADENA, tamaño 4
Fecha y Hora	FECHA
Importe Premio	NUMERICO de 6
Apuesta Limite	NUMERICO de 5 más 2 decimales	Menor de 20000

Tabla Participaciones

PARTICIPACIONES
CodCaballo	CADENA, tamaño 4
CodCarrera	CADENA, tamaño 4
Dorsal	NUMERICO de 2	Obligatorio
Jockey	CADENA, tamaño 10	No Nulo
PosicionFinal	NUMERICO de 2	Numero positivo

Tabla Apuestas

APUESTAS
DNICliente	CADENA, tamaño 10
CodCaballo	CADENA, tamaño 4
CodCarrera	CADENA, tamaño 4
Importe	NUMERICO de 6	No Nulo, por defecto 300
Tantoporuno	NUMERICO de 4 más 2 decimales	Mayor que 1

Tabla Clientes

CLIENTES
DNI	CADENA, tamaño 10
Nombre	CADENA, tamaño 20

Añadir las siguientes restricciones una vez  creadas las tablas e insertado algunos registros:

●                    En Participaciones los nombres de los jockeys tienen siempre las iniciales en mayúsculas.

●                    La temporada de carreras transcurre de Marzo a Noviembre.

●                    La nacionalidad de los caballos sólo puede ser Española, Británica o Árabe.

●                    Añade la columna Nacionalidad a la tabla CLIENTES de tipo CADENA y tamaño 20.

●                    Modifica la columna Importe para que sea de tipo numérico de 5 con dos decimales.

●                    Fecha y Hora: De las 9 a las 14:30

●                    El código de la carrera ha de comenzar por una letra mayúscula, luego un  guión y a continuación dos números.

CREATE TABLE caballos (

codcaballo VARCHAR2(4),

nombre VARCHAR2(20),

peso NUMBER(3),

fecha_nac DATE,

propietario VARCHAR2(25),

nacionalidad VARCHAR2(20),

CONSTRAINT PK_caballos PRIMARY KEY (codcaballo),

CONSTRAINT CK_peso CHECK (peso BETWEEN 240 AND 300),

CONSTRAINT CK_fecha_nac CHECK (TO_NUMBER(TO_CHAR(fecha_nac,'YYYY'))>2000)

);

CREATE TABLE carreras (

codcarrera VARCHAR2(4),

fecha_hora DATE,

importe_premio NUMBER(6),

apuesta_limite NUMBER(7,2),

CONSTRAINT PK_carreras PRIMARY KEY (codcarrera),

CONSTRAINT CK_apuesta_limite CHECK (apuesta_limite < 20000)

);

CREATE TABLE participaciones (

codcaballo VARCHAR2(4),

codcarrera VARCHAR2(4),

dorsal NUMBER(2),

jockey VARCHAR2(10),

posicion_final NUMBER(2),

CONSTRAINT PK_participaciones PRIMARY KEY (codcaballo, codcarrera),

CONSTRAINT FK_codcaballo FOREIGN KEY (codcaballo) REFERENCES caballos (codcaballo),

CONSTRAINT FK_codcarrera FOREIGN KEY (codcarrera) REFERENCES carreras (codcarrera),

CONSTRAINT CK_dorsal_nnull CHECK (dorsal IS NOT NULL),

CONSTRAINT CK_jockey_nnull CHECK (jockey IS NOT NULL),

CONSTRAINT CK_posicion_final CHECK (posicion_final >= 0)

);

CREATE TABLE clientes (

dni VARCHAR2(10),

nombre VARCHAR2(10),

CONSTRAINT PK_clientes_dni PRIMARY KEY (dni)

);

CREATE TABLE apuestas (

dnicliente VARCHAR2(10),

codcaballo VARCHAR2(4),

codcarrera VARCHAR2(4),

importe NUMBER(6) DEFAULT 300,

tantoporuno NUMBER(6,2),

CONSTRAINT PK_clientes PRIMARY KEY (dnicliente, codcaballo, codcarrera),

CONSTRAINT FK_dnicliente FOREIGN KEY (dnicliente) REFERENCES clientes (dni),

CONSTRAINT FK_participaciones FOREIGN KEY (codcaballo, codcarrera) REFERENCES participaciones (codcaballo, codcarrera),

CONSTRAINT CK_importe_nnull CHECK (importe IS NOT NULL),

CONSTRAINT CK_tantoporuno CHECK (tantoporuno >1)

);

ALTER TABLE participaciones ADD CONSTRAINT CK_nombre_j CHECK (jockey=INITCAP(jockey));

       

ALTER TABLE carreras ADD CONSTRAINT CK_fecha_mar_nov CHECK (TO_NUMBER(TO_CHAR(fecha_hora,'MM')) BETWEEN 03 AND 11);

       

ALTER TABLE caballos ADD CONSTRAINT CK_caballo_nac CHECK (upper(nacionalidad) IN ('ESPANHOLA','BRITANICA','ARABE'));

       

ALTER TABLE clientes ADD nacionalidad VARCHAR2(20);

        DESCRIBE clientes;

ALTER TABLE apuestas MODIFY importe NUMBER(5,2);

        DESCRIBE apuestas;

ALTER TABLE carreras ADD CONSTRAINT CK_fecha_hora CHECK ((TO_NUMBER(TO_CHAR(fecha_hora,'hh'))>9)

AND ((TO_NUMBER(TO_CHAR(fecha_hora,'hh'))<14)

OR ((TO_NUMBER(TO_CHAR(fecha_hora,'hh'))<14) AND (TO_NUMBER(TO_CHAR(fecha_hora,'mi'))<30))));

       

ALTER TABLE carreras ADD CONSTRAINT CK_codcarrera CHECK (((substr(codcarrera,1,1) = upper(substr(codcarrera,1,1)))

AND ((substr(codcarrera,2,1)='-'))

AND (TO_NUMBER(substr(codcarrera,3,2))) BETWEEN 0 AND 99)

);