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

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.

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:

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

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:

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:

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

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,
  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)
);

1.2 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.

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

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
);

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)
);

1.3 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:

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

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 (
  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)
);

CREATE TABLE jornada (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  dia TINYINT UNSIGNED NOT NULL CHECK(dia >=1 AND dia<=31),
  mes TINYINT UNSIGNED NOT NULL CHECK(mes >=1 AND mes<=12),
  anyo YEAR(4) NOT NULL
);

CREATE TABLE partida (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_jugador_blancas INTEGER UNSIGNED NOT NULL,
  id_jugador_negras INTEGER UNSIGNED NOT NULL,
  id_arbitro INTEGER UNSIGNED NOT NULL,
  id_sala INTEGER UNSIGNED NOT NULL,
  id_jornada INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (id_jugador_blancas) REFERENCES jugador(id),
  FOREIGN KEY (id_jugador_negras) REFERENCES jugador(id),
  FOREIGN KEY (id_arbitro) REFERENCES arbitro(id),
  FOREIGN KEY (id_sala) REFERENCES sala(id),
  FOREIGN KEY (id_jornada) REFERENCES jornada(id)
);

CREATE TABLE movimientos (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  numero_movimiento SMALLINT UNSIGNED NOT NULL,
  posiciones VARCHAR(20) NOT NULL,
  comentario VARCHAR(50),
  id_partida INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (id_partida) REFERENCES partida(id)
);

1.4 Librería OnLine

Un cliente le ha contratado para diseñar una web que permita comprar libros por Internet. Tenga en cuenta las siguientes indicaciones para modelar cómo sería la base de datos del proyecto:

1.4.1 Modelo conceptual

1.4.1.1 Diagrama E/R

1.5 Spotify

Vamos a tratar de hacer un modelo sencillo de cómo sería la base de datos necesaria para Spotify.

1.5.1 Modelo conceptual

1.5.1.1 Diagrama E/R

1.6 YouTube Lite

Vamos a tratar de hacer un modelo sencillo de cómo sería la base de datos para una versión reducida de YouTube.

1.6.1 Modelo conceptual

1.6.1.1 Diagrama E/R

Se han omitido algunos atributos de las entidades Usuario y Vídeo con el fin de simplificar el diagrama.

Los atributos que contienen cada una de estas entidades son los siguientes:

1.7 Pizzería

Un cliente le ha contratado para diseñar una web que permita hacer pedidos de comida a domicilio por Internet. Tenga en cuenta las siguientes indicaciones para modelar cómo sería la base de datos del proyecto:

1.8 Exámenes tipo test

El alumnado de 1º ASIR y 1º DAW del IES Celia Viñas ha decidido desarrollar una web que les permita realizar exámenes tipo test para preparar los exámenes de los diferentes módulos del ciclo. El funcionamiento es muy sencillo, al entrar en la web aparece un formulario donde puedes seleccionar el módulo sobre el que quieres realizar el test, el tema, la dificultad de las preguntas y el número total de preguntas. La web generará un examen con preguntas aleatorias cada vez que se solicite. Diseñe el modelo entidad/relación necesario para la base de datos del proyecto teniendo en cuenta las siguientes indicaciones:

1.9 Vídeos

El siguiente diagrama E/R representa una base de datos sencilla de una aplicación web que permite visualizar vídeos por Internet. Realice el paso a tablas relacionales a partir del diagrama E/R.

1.9.1 Modelo conceptual

1.9.1.1 Diagrama E/R