• Author / Uploaded
• Julio Lopez

Citation preview

Unidad Didáctica 2. Diseño de bases de datos relacionales JOSÉ JUAN SÁNCHEZ HERNÁNDEZ

IES Celia Viñas (Almería) - 2019/2020

Índice general 1 Ejercicios Modelo Entidad-Relación Extendido 1.1 Proyectos de investigación . . . . . . . . . . 1.1.1 Modelo conceptual . . . . . . . . . . 1.1.1.1 Diagrama E/R . . . . . . . 1.1.2 Modelo lógico . . . . . . . . . . . . 1.1.2.1 Modelo relacional . . . . . 1.1.3 Modelo físico . . . . . . . . . . . . . 1.1.3.1 Base de datos para MySQL 1.2 Cursos de formación . . . . . . . . . . . . . 1.2.1 Modelo conceptual . . . . . . . . . . 1.2.1.1 Diagrama E/R . . . . . . . 1.2.2 Modelo lógico . . . . . . . . . . . . 1.2.2.1 Modelo relacional . . . . . 1.2.3 Modelo físico . . . . . . . . . . . . . 1.2.3.1 Base de datos para MySQL 1.3 Campeonato de Ajedrez . . . . . . . . . . . 1.3.1 Modelo conceptual . . . . . . . . . . 1.3.1.1 Diagrama E/R . . . . . . . 1.3.2 Modelo lógico . . . . . . . . . . . . 1.3.2.1 Modelo relacional . . . . . 1.3.3 Modelo físico . . . . . . . . . . . . . 1.3.3.1 Base de datos para MySQL 1.4 Librería OnLine . . . . . . . . . . . . . . . . 1.4.1 Modelo conceptual . . . . . . . . . . 1.4.1.1 Diagrama E/R . . . . . . . 1.5 Spotify . . . . . . . . . . . . . . . . . . . . 1.5.1 Modelo conceptual . . . . . . . . . . 1.5.1.1 Diagrama E/R . . . . . . . 1.6 YouTube Lite . . . . . . . . . . . . . . . . . 1.6.1 Modelo conceptual . . . . . . . . . . 1.6.1.1 Diagrama E/R . . . . . . . 1.7 Pizzería . . . . . . . . . . . . . . . . . . . . 1.8 Exámenes tipo test . . . . . . . . . . . . . . 1.9 Vídeos . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5 5 6 6 6 7 8 8 11 11 11 12 12 12 12 14 14 15 15 15 16 16 19 19 20 21 21 22 23 23 23 25 26 26

Unidad Didáctica 2. Diseño de bases de datos relacionales 1.9.1

1.10 1.11 1.12 1.13

Modelo conceptual . . . . . . 1.9.1.1 Diagrama E/R . . . Banco . . . . . . . . . . . . . . . . . Organizaciones no gubernamentales Venta de cocinas . . . . . . . . . . . Gestión de nóminas . . . . . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

IES Celia Viñas (Almería) - 2019/2020 . . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

26 27 28 29 30 31

2 Créditos

32

3 Licencia

33

José Juan Sánchez Hernández

2

Índice de cuadros

3

Índice de figuras

4

Capítulo 1

Ejercicios Modelo Entidad-Relación Extendido 1.1 Proyectos de investigación El departamento de Informática de la Universidad de Almería desea diseñar una base de datos para gestionar los profesores que participan en los proyectos de investigación. • De cada proyecto de investigación se desea almacenar un identificador único, nombre, presupuesto total, el programa de I+D que lo financia, fecha de inicio, fecha de finalización y una descripción. • En los proyectos de investigación trabajan profesores del departamento durante un periodo de tiempo, determinado por una fecha de inicio y una fecha de fin. Tenga en cuenta que un mismo profesor puede trabajar en el mismo proyecto en diferentes épocas. • De cada profesor se desea almacenar un identificador único, nombre, apellidos, despacho y teléfono. • Un profesor puede trabajar en varios proyectos a la vez y en un proyecto pueden trabajar varios profesores. • Los profesores del departamento pueden ser doctores o no doctores. • Un profesor no doctor debe ser supervisado por un profesor doctor. El tiempo de supervisión viene determinado por una fecha de inicio y una fecha de fin. Deberemos almacenar los profesores doctores que han supervisado a un profesor no doctor y durante qué periodos lo han sido. • De todos los profesores que trabajan en el proyecto hay uno que es el investigador principal, que será el encargado de coordinar el proyecto. Es necesario almacenar quién es el investigador principal de cada uno de los proyectos. Tenga en cuenta que el investigador principal no puede cambiar a lo largo de la vida del proyecto, siempre será el mismo. • El investigador principal de un proyecto tiene que ser un profesor doctor, en ningún caso podrá serlo un profesor no doctor. • Los profesores doctores y no doctores escriben publicaciones. Una publicación consta de un código único y un título. Y una publicación puede ser de dos tipos, puede ser una publicación en una revista o en un 5

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

congreso. • Si la publicación es en una revista además del código único y el título vamos a almacenar el volumen, el número, la página de inicio y la página de fin. • Si la publicación es en un congreso además del código único y el título vamos a almacenar el tipo de congreso, ciudad, país, fecha de inicio, fecha de fin y editorial.

1.1.1 Modelo conceptual 1.1.1.1 Diagrama E/R

1.1.2 Modelo lógico Las reglas de transformación de E/R al modelo relacional nos dicen que la relación Trabaja genera una nueva tabla porque es una relación de cardinalidad N:N. Esta nueva tabla recibe las claves primarias de las dos entidades que participan en la relación y además participan como clave primaria. La solución teórica sería la siguiente: • PROFESOR_TRABAJA_PROYECTO(id_profesor, id_proyecto, fecha_inicio, fecha_fin) – id_profesor: FK (Foreign Key) de PROFESOR(id) – id_proyecto: FK (Foreign Key) de PROYECTO(id) Con esta solución podemos tener un problema en el caso de que un profesor trabaje en el mismo proyecto en fechas diferentes. En este caso no podríamos almacenar esta información en la tabla porque se producuría un error de claves duplicadas. id_profesor

id_proyecto

fecha_inicio

fecha_fin

1

1

1/01/2017

31/12/2017

1

1

1/11/2018

31/12/2018

José Juan Sánchez Hernández

6

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

Para solucionarlo podemos incluir el atributo fecha_inicio como parte de la clave primaria de la tabla, de modo que la clave primaria estaría compuesta por id_profesor, id_proyecto y fecha_inicio. La solución sería la siguiente: • PROFESOR_TRABAJA_PROYECTO(id_profesor, id_proyecto, fecha_inicio, fecha_fin) – id_profesor: FK (Foreign Key) de PROFESOR(id) – id_proyecto: FK (Foreign Key) de PROYECTO(id) En este caso ya no habría ningún problema para almacenar que un profesor participa en el mismo proyecto en fechas diferentes. id_profesor

id_proyecto

fecha_inicio

fecha_fin

1

1

1/01/2017

31/12/2017

1

1

1/11/2018

31/12/2018

Otra forma de resolver este problema puede ser creando un nuevo atributo id que sea un valor numérico autoincrementado y que éste sea la única clave primara de la tabla. La solución sería la siguiente: • PROFESOR_TRABAJA_PROYECTO(id, id_profesor, id_proyecto, fecha_inicio, fecha_fin) – id_profesor: FK (Foreign Key) de PROFESOR(id) – id_proyecto: FK (Foreign Key) de PROYECTO(id) En este caso tampoco habría ningún problema para almacenar que un profesor participa en el mismo proyecto en fechas diferentes. #id

id_profesor

id_proyecto

fecha_inicio

fecha_fin

1

1

1

1/01/2017

31/12/2017

2

1

1

1/11/2018

31/12/2018

1.1.2.1 Modelo relacional • PROFESOR(id, nombre, apellido1, apellido2, despacho, teléfono, tipo) • PROYECTO_INVESTIGACIÓN(id, nombre, programa_id, descripción, fecha_inicio, fecha_fin, presupuesto, id_doctor) – id_doctor: FK (Foreign Key) de PROFESOR(id) • PROFESOR_TRABAJA_PROYECTO(id, id_profesor, id_proyecto, fecha_inicio, fecha_fin) – id_profesor: FK (Foreign Key) de PROFESOR(id) – id_proyecto: FK (Foreign Key) de PROYECTO(id) • DOCTOR_SUPERVISA_NO_DOCTOR(id, id_doctor, id_no_doctor, fecha_inicio, fecha_fin) – id_doctor: FK (Foreign Key) de PROFESOR(id) José Juan Sánchez Hernández

7

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

– id_no_doctor: FK (Foreign Key) de PROFESOR(id) • PUBLICACIÓN(id, título, tipo) • REVISTA(id, volumen, número, página_inicio, página_fin) – id: FK (Foreign Key) de PUBLICACIÓN(id) • CONGRESO(id, tipo, ciudad, país, fecha_inicio, fecha_fin) – id: FK (Foreign Key) de PUBLICACIÓN(id) • PROFESOR_ESCRIBE_PUBLICACIÓN(id_profesor, id_publicación) – id_profesor: FK (Foreign Key) de PROFESOR(id) – id_publicación: FK (Foreign Key) de PUBLICACIÓN(id)

1.1.3 Modelo físico 1.1.3.1 Base de datos para MySQL

DROP DATABASE IF EXISTS proyectos; CREATE DATABASE proyectos CHARACTER SET utf8mb4; USE proyectos; CREATE TABLE profesor ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, apellido1 VARCHAR(100) NOT NULL, apellido2 VARCHAR(100), despacho VARCHAR(10) NOT NULL, telefono CHAR(9) NOT NULL, tipo ENUM('Doctor', 'No Doctor') NOT NULL DEFAULT 'Doctor' ); CREATE TABLE proyecto_investigacion ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(200) NOT NULL, programa_id VARCHAR(200) NOT NULL, descripcion VARCHAR(512) NOT NULL, fecha_inicio DATE NOT NULL, fecha_fin DATE NOT NULL, presupuesto FLOAT UNSIGNED, id_doctor INTEGER UNSIGNED, FOREIGN KEY (id_doctor) REFERENCES profesor(id) ); CREATE TABLE profesor_trabaja_proyecto ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,

José Juan Sánchez Hernández

8

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

id_profesor INTEGER UNSIGNED NOT NULL, id_proyecto INTEGER UNSIGNED NOT NULL, fecha_inicio DATE NOT NULL, fecha_fin DATE NOT NULL, FOREIGN KEY (id_profesor) REFERENCES profesor(id), FOREIGN KEY (id_proyecto) REFERENCES proyecto_investigacion(id) ); CREATE TABLE doctor_supervisa_no_doctor ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_doctor INTEGER UNSIGNED NOT NULL, id_no_doctor INTEGER UNSIGNED NOT NULL, fecha_inicio DATE NOT NULL, fecha_fin DATE NOT NULL, FOREIGN KEY (id_doctor) REFERENCES profesor(id), FOREIGN KEY (id_no_doctor) REFERENCES profesor(id) ); CREATE TABLE publicacion ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, titulo VARCHAR(200) NOT NULL, tipo ENUM('revista', 'congreso') NOT NULL ); CREATE TABLE revista ( id INTEGER UNSIGNED PRIMARY KEY, volumen SMALLINT UNSIGNED NOT NULL, numero SMALLINT UNSIGNED NOT NULL, pagina_inicio SMALLINT UNSIGNED NOT NULL, pagina_fin SMALLINT UNSIGNED NOT NULL, FOREIGN KEY (id) REFERENCES publicacion(id) ); CREATE TABLE congreso ( id INTEGER UNSIGNED PRIMARY KEY, tipo VARCHAR(50) NOT NULL, ciudad VARCHAR(50) NOT NULL, pais VARCHAR(50) NOT NULL, fecha_inicio DATE NOT NULL, fecha_fin DATE NOT NULL, FOREIGN KEY (id) REFERENCES publicacion(id) ); CREATE TABLE profesor_escribe_publicacion ( id_profesor INTEGER UNSIGNED NOT NULL, id_publicacion INTEGER UNSIGNED NOT NULL, PRIMARY KEY (id_profesor, id_publicacion), FOREIGN KEY (id_profesor) REFERENCES profesor(id), FOREIGN KEY (id_publicacion) REFERENCES publicacion(id)

José Juan Sánchez Hernández

9

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

);

José Juan Sánchez Hernández

10

Unidad Didáctica 2. Diseño de bases de datos relacionales

1.2

IES Celia Viñas (Almería) - 2019/2020

Cursos de formación

El departamento de formación de una empresa desea diseñar una base de datos para planificar y gestionar la formación de sus empleados. • La empresa organiza cursos internos de formación de los que se desea conocer el código de curso, el nombre, una descripción, el número de horas de duración y el coste del curso. • Un curso puede tener como prerrequisito haber realizado otro(s) previamente, y a su vez la realización de un curso puede ser prerrequisito de otros. Un curso que es un prerrequisito de otro puede serlo de forma obligatoria o sólo recomendable. • Un mismo curso tiene diferentes ediciones, es decir, se imparte en diferentes lugares, fechas y con diferentes horarios (intensivo, de mañana o de tarde). En una misma fecha de inicio sólo puede impartirse una edición de un curso. • Los cursos se imparten por personal de la empresa. • De los empleados se desea almacenar su código de empleado, nombre y apellidos, dirección, teléfono, NIF, fecha de nacimiento, nacionalidad y salario, así como si está capacitado para impartir cursos. • Un mismo empleado puede ser docente en una edición de un curso y alumno en otra edición, pero nunca puede ser ambas cosas a la vez (en una misma edición de curso o lo imparte o lo recibe).

1.2.1 Modelo conceptual 1.2.1.1 Diagrama E/R

José Juan Sánchez Hernández

11

Unidad Didáctica 2. Diseño de bases de datos relacionales

1.2.2

IES Celia Viñas (Almería) - 2019/2020

Modelo lógico

1.2.2.1 Modelo relacional • EMPLEADO(id, nombre, apellido1, apellido2, teléfono, dirección, tipo) • CURSO(id, nombre, descripción, duración, coste) • CURSO_TIENE_PRERREQUISTO_CURSO(id_curso, id_curso_prerrequisito, es_obligatorio) – id_curso: FK (Foreign Key) de CURSO(id) – id_curso_prerrequisito: FK (Foreign Key) de CURSO(id) • EDICIÓN(id, fecha_inicio, fecha_fin, horario, lugar, id_curso, id_empleado_capacitado) – id_curso: FK (Foreign Key) de CURSO(id) – id_empleado_capacitado: FK (Foreign Key) de EMPLEADO(id) • EMPLEADO_RECIBE_EDICIÓN(id_empleado, id_edicion) – id_empleado: FK (Foreign Key) de EMPLEADO(id) – id_edicion: FK (Foreign Key) de EDICIÓN(id)

1.2.3 Modelo físico 1.2.3.1 Base de datos para MySQL

DROP DATABASE IF EXISTS formacion; CREATE DATABASE formacion CHARACTER SET utf8mb4; USE formacion; CREATE TABLE empleado ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, apellido1 VARCHAR(100) NOT NULL, apellido2 VARCHAR(100), telefono CHAR(9) NOT NULL UNIQUE, direccion VARCHAR(200) NOT NULL, tipo ENUM('Capacitado', 'No capacitado') NOT NULL ); CREATE TABLE curso ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, descripcion VARCHAR(512) NOT NULL, duracion SMALLINT UNSIGNED NOT NULL, coste FLOAT(6,2) NOT NULL );

José Juan Sánchez Hernández

12

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

CREATE TABLE curso_tiene_prerrequisito_curso ( id_curso INTEGER UNSIGNED NOT NULL, id_curso_prerrequisito INTEGER UNSIGNED NOT NULL, es_obligatorio BOOLEAN NOT NULL, PRIMARY KEY (id_curso, id_curso_prerrequisito), FOREIGN KEY (id_curso) REFERENCES curso(id), FOREIGN KEY (id_curso_prerrequisito) REFERENCES curso(id) ); CREATE TABLE edicion ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, fecha_inicio DATE NOT NULL, fecha_fin DATE NOT NULL, horario TIME NOT NULL, lugar VARCHAR(100) NOT NULL, id_curso INTEGER UNSIGNED NOT NULL, id_empleado_capacitado INTEGER UNSIGNED NOT NULL, FOREIGN KEY (id_curso) REFERENCES curso(id), FOREIGN KEY (id_empleado_capacitado) REFERENCES empleado(id) ); CREATE TABLE empleado_recibe_formacion ( id_empleado INTEGER UNSIGNED NOT NULL, id_edicion INTEGER UNSIGNED NOT NULL, PRIMARY KEY (id_empleado, id_edicion), FOREIGN KEY (id_empleado) REFERENCES empleado(id), FOREIGN KEY (id_edicion) REFERENCES edicion(id) );

José Juan Sánchez Hernández

13

Unidad Didáctica 2. Diseño de bases de datos relacionales

1.3

IES Celia Viñas (Almería) - 2019/2020

Campeonato de Ajedrez

El club de Ajedrez de Huércal de Almería, ha sido encargado por la Federación Internacional de Ajedrez de la organización de los próximos campeonatos mundiales que se celebrarán en la localidad. Por este motivo, desea llevar a una base de datos toda la gestión relativa a participantes, alojamientos y partidas. Teniendo en cuenta que: • En el campeonato participan jugadores y árbitros, de ambos se requiere conocer el número de asociado, nombre, dirección y teléfono de contacto. De los jugadores se precisa además el nivel de juego en una escala de 1 a 10. Y de los árbitros guardaremos los años de experiencia. • Ningún árbitro puede participar como jugador. • Los países envían al campeonato un conjunto de jugadores y árbitros, aunque no todos los países envían participantes. Todo jugador y árbitro es enviado por un único país. Un país puede ser representado por otro país. • Cada país se identifica por un número correlativo según su orden alfabético e interesa conocer además su nombre y el número de clubes de ajedrez existentes en el mismo. • Cada partida se identifica por un número correlativo (CódigoPartida), la juegan dos jugadores y la arbitra un árbitro. Interesa registrar las partidas que juega cada jugador y el color (blancas o negras) con el que juega. Ha de tenerse en cuenta que un árbitro no puede arbitrar a jugadores enviados por el mismo país que ha enviado él. • Todo participante participa en al menos una partida. • Tanto jugadores como árbitros se alojan en uno de los hoteles en los que se desarrollan las partidas, se desea conocer en qué hotel y en qué fechas se ha alojado cada uno de los participantes. Los participantes pueden no permanecer en Huércal de Almería durante todo el campeonato, sino acudir cuando tienen que jugar alguna partida alojándose en el mismo o distinto hotel. De cada hotel, se desea conocer el nombre, la dirección y el número de teléfono. • El campeonato se desarrolla a lo largo de una serie de jornadas (año, mes, día) y cada partida tiene lugar en una de las jornadas aunque no tengan lugar partidas todas las jornadas. • Cada partida se celebra en una de las salas de las que pueden disponer los hoteles, se desea conocer el número de entradas vendidas en la sala para cada partida. De cada sala, se desea conocer la capacidad y medios de que dispone (radio, televisión, vídeo,…) para facilitar la retransmisión de los encuentros. Una sala puede disponer de varios medios distintos. • De cada partida se pretende registrar todos los movimientos que la componen, la identificación de movimiento se establece en base a un número de orden dentro de cada partida, para cada movimiento se guardan la jugada (5 posiciones) y un breve comentario realizado por un experto.

1.3.1 Modelo conceptual

José Juan Sánchez Hernández

14

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

1.3.1.1 Diagrama E/R

1.3.2

Modelo lógico

1.3.2.1 Modelo relacional • PARTICIPANTE(id, número_asociado, nombre, apellido1, apellido2, dirección, teléfono, tipo) • JUGADOR(id, nivel) – id: FK (Foreign Key) de PARTICIPANTE(id) • ÁRBITRO(id, años_experiencia) – id: FK (Foreign Key) de PARTICIPANTE(id) • PAÍS(id, nombre, número_clubs, id_país_representante) – id_país_representante: FK (Foreign Key) de PAÍS(id) • HOTEL(id, nombre, dirección, teléfono) • SALA(id, nombre, capacidad, id_hotel) – id_hotel: FK (Foreign Key) de HOTEL(id) • MEDIOS(id, nombre) • SALA_TIENE_MEDIOS(id_sala, id_medio) – id_sala: FK (Foreign Key) de SALA(id) José Juan Sánchez Hernández

15

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

– id_medio: FK (Foreign Key) de MEDIOS(id) • PARTICIPANTE_SE_ALOJA_HOTEL(id, id_participante, id_hotel, fecha_entrada, fecha_salida) – id_participante: FK (Foreign Key) de PARTICIPANTE(id) – id_hotel: FK (Foreign Key) de HOTEL(id) • JORNADA(id, día, mes, año) • PARTIDA(id, id_jugador_blancas, id_jugador_negras, id_árbitro, id_sala, id_jornada) – id_jugador_blancas: FK (Foreign Key) de JUGADOR(id) – id_jugador_negras: FK (Foreign Key) de JUGADOR(id) – id_árbitro: FK (Foreign Key) de ÁRBITRO(id) – id_sala: FK (Foreign Key) de SALA(id) – id_jornada: FK (Foreign Key) de JORNADA(id) • MOVIMIENTOS(id, número_movimiento, posiciones, comentario, id_partida) – id_partida: FK (Foreign Key) de PARTIDA(id)

1.3.3

Modelo físico

1.3.3.1 Base de datos para MySQL

DROP DATABASE IF EXISTS ajedrez; CREATE DATABASE ajedrez CHARACTER SET utf8mb4; USE ajedrez; CREATE TABLE participante ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, numero_asociado MEDIUMINT UNSIGNED NOT NULL UNIQUE, nombre VARCHAR(100) NOT NULL, apellido1 VARCHAR(100) NOT NULL, apellido2 VARCHAR(100) NOT NULL, direccion VARCHAR(150) NOT NULL, telefono CHAR(9) NOT NULL UNIQUE, tipo ENUM('Jugador', 'Árbitro') NOT NULL ); CREATE TABLE jugador ( id INTEGER UNSIGNED PRIMARY KEY, nivel TINYINT UNSIGNED NOT NULL, FOREIGN KEY (id) REFERENCES participante(id) ); CREATE TABLE arbitro (

José Juan Sánchez Hernández

16

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

id INTEGER UNSIGNED PRIMARY KEY, anyos_experiencia TINYINT UNSIGNED, FOREIGN KEY (id) REFERENCES participante(id) ); CREATE TABLE pais ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(50) NOT NULL, numero_clubs SMALLINT UNSIGNED NOT NULL, id_pais_representante INTEGER UNSIGNED, FOREIGN KEY (id_pais_representante) REFERENCES pais(id) ); CREATE TABLE hotel ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, direccion VARCHAR(150) NOT NULL, telefono CHAR(9) NOT NULL UNIQUE ); CREATE TABLE sala ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, capacidad SMALLINT UNSIGNED NOT NULL, id_hotel INTEGER UNSIGNED, FOREIGN KEY (id_hotel) REFERENCES hotel(id) ); CREATE TABLE medios ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL ); CREATE TABLE sala_tiene_medios ( id_sala INTEGER UNSIGNED NOT NULL, id_medio INTEGER UNSIGNED NOT NULL, PRIMARY KEY (id_sala, id_medio), FOREIGN KEY (id_sala) REFERENCES sala(id), FOREIGN KEY (id_medio) REFERENCES medios(id) ); CREATE TABLE participante_se_aloja_hotel ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_participante INTEGER UNSIGNED NOT NULL, id_hotel INTEGER UNSIGNED NOT NULL, fecha_entrada DATE NOT NULL, fecha_salida DATE NOT NULL, FOREIGN KEY (id_participante) REFERENCES participante(id), FOREIGN KEY (id_hotel) REFERENCES hotel(id)

José Juan Sánchez Hernández

17

Unidad Didáctica 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2019/2020

); CREATE TABLE jornada ( id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, dia TINYINT UNSIGNED NOT NULL CHECK(dia >=1 AND dia=1 AND mes