• Categories
• Base de datos relacional
• SQL
• Bancos
• Audi
• Árbitro

Citation preview

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

Índice general 1

Ejercicios Modelo Entidad-Relación 1.1 Proveedores . . . . . . . . . . . 1.1.1 Diagrama E/R . . . . . . . 1.1.2 Diagrama Relacional . . . 1.1.3 Paso a tablas (SQL) . . . . 1.2 Cadena editorial . . . . . . . . . 1.2.1 Diagrama E/R . . . . . . . 1.2.2 Diagrama E/R . . . . . . . 1.2.3 Diagrama Relacional . . . 1.2.4 Paso a tablas (SQL) . . . . 1.3 Empresa de material informático 1.3.1 Diagrama E/R . . . . . . . 1.3.2 Diagrama Relacional . . . 1.3.3 Paso a tablas (SQL) . . . . 1.4 Agencia de viajes . . . . . . . . . 1.4.1 Diagrama E/R . . . . . . . 1.4.2 Diagrama Relacional . . . 1.4.3 Paso a tablas (SQL) . . . . 1.5 Exámenes . . . . . . . . . . . . . 1.5.1 Diagrama E/R . . . . . . . 1.5.2 Diagrama Relacional . . . 1.5.3 Paso a tablas (SQL) . . . . 1.6 Concesionario de automóviles . . 1.7 Club Náutico . . . . . . . . . . . 1.8 Información policial . . . . . . . 1.9 Alumno, Asignatura, Profesor . . 1.9.1 Diagrama E/R . . . . . . . 1.9.2 Diagrama Relacional . . . 1.9.3 Paso a tablas (SQL) . . . .

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

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

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

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

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

2 Ejercicios Modelo Entidad-Relación Extendido 2.1 Cursos de formación . . . . . . . . . . . . 2.2 Campeonato de Ajedrez . . . . . . . . . . 2.3 Banco . . . . . . . . . . . . . . . . . . . . 2.4 Organizaciones no gubernamentales . . . 2.5 Venta de cocinas . . . . . . . . . . . . . .

José Juan Sánchez Hernández

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

. . . . .

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

3 3 3 3 3 5 5 5 6 6 9 10 11 11 14 15 15 15 18 19 19 19 22 23 24 25 25 26 26

. . . . .

28 28 29 30 31 32

1

Tema 2. Diseño de bases de datos relacionales 2.6 2.7

2.8

2.9

Gestión de nóminas . . . . Librería OnLine . . . . . . . 2.7.1 Diagrama E/R . . . . 2.7.2 Diagrama Relacional 2.7.3 Paso a tablas (SQL) . Spotify . . . . . . . . . . . 2.8.1 Diagrama E/R . . . . 2.8.2 Diagrama Relacional 2.8.3 Paso a tablas (SQL) . YouTube Lite . . . . . . . . 2.9.1 Diagrama E/R . . . . 2.9.2 Diagrama Relacional 2.9.3 Paso a tablas (SQL) .

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

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

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

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

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

IES Celia Viñas (Almería) - 2017/2018 . . . . . . . . . . . . .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

33 34 35 35 35 36 37 37 37 41 41 42 42

3 Créditos

46

4 Licencia

46

José Juan Sánchez Hernández

2

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1 Ejercicios Modelo Entidad-Relación Diseña el modelo entidad-relación para cada uno de los siguientes ejercicios.

1.1 Proveedores Tenemos que diseñar una base de datos sobre proveedores y disponemos de la siguiente información: • De cada proveedor conocemos su nombre, dirección, ciudad, provincia y un código de proveedor que será único para cada uno de ellos. • Nos interesa llevar un control de las piezas que nos suministra cada proveedor. Es importante conocer la cantidad de piezas que nos suministra de cada tipo y en qué fecha lo hace. • Una misma pieza puede ser suministrada por diferentes proveedores. • De cada pieza conocemos un código que será único, nombre, color, precio y categoría. • Pueden existir varias categorías y para cada categoría hay un nombre y un código de categoría único. • Una pieza sólo puede pertenecer a una categoría.

1.1.1 Diagrama E/R

1.1.2 Diagrama Relacional

1.1.3 Paso a tablas (SQL)

José Juan Sánchez Hernández

3

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

DROP DATABASE IF EXISTS proveedores; CREATE DATABASE proveedores CHARACTER SET utf8; USE proveedores; CREATE TABLE categoria ( codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL UNIQUE ); CREATE TABLE pieza ( codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, color VARCHAR(25) NOT NULL, precio FLOAT NOT NULL CHECK(precio > 0), codigo_categoria INT UNSIGNED NOT NULL, FOREIGN KEY (codigo_categoria) REFERENCES categoria(codigo) ); CREATE TABLE proveedor ( codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, direccion VARCHAR(100) NOT NULL, ciudad VARCHAR(50) NOT NULL, provincia VARCHAR(50) NOT NULL ); CREATE TABLE suministra ( codigo_proveedor INT UNSIGNED, codigo_pieza INT UNSIGNED, cantidad INT UNSIGNED NOT NULL CHECK(cantidad > 0), fecha DATE NOT NULL, PRIMARY KEY (codigo_proveedor, codigo_pieza), FOREIGN KEY (codigo_proveedor) REFERENCES proveedor(codigo), FOREIGN KEY (codigo_pieza) REFERENCES pieza(codigo) );

José Juan Sánchez Hernández

4

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.2 Cadena editorial Tenemos esta información sobre una cadena editorial: • La editorial tiene varias sucursales, con su domicilio, teléfono y un código de sucursal. • Cada sucursal tiene varios empleados, de los cuales tendremos su nombre, apellidos, NIF y teléfono. Un empleado trabaja en una única sucursal. • En cada sucursal se publican varias revistas, de las que almacenaremos su título, número de registro, periodicidad y tipo. • La editorial tiene periodistas (que no trabajan en las sucursales) que pueden escribir artículos para varias revistas. Almacenaremos los mismos datos que para los empleados, añadiendo su especialidad. Guardaremos también las secciones fijas de cada revista, que tendrán un título y una extensión. • Para cada revista, almacenaremos información de cada número, que incluirá la fecha, número de páginas y el número de ejemplares vendidos.

1.2.1 Diagrama E/R

1.2.2 Diagrama E/R Solución sin utilizar entidades débiles.

José Juan Sánchez Hernández

5

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.2.3 Diagrama Relacional

1.2.4 Paso a tablas (SQL) DROP DATABASE IF EXISTS cadena_editorial; CREATE DATABASE cadena_editorial CHARACTER SET utf8; USE cadena_editorial; CREATE TABLE sucursal ( codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, direccion VARCHAR(100) NOT NULL, ciudad VARCHAR(50) NOT NULL,

José Juan Sánchez Hernández

6

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

provincia VARCHAR(50) NOT NULL, telefono VARCHAR(9) NOT NULL ); CREATE TABLE empleado ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, codigo_sucursal INT UNSIGNED NOT NULL, nif VARCHAR(9) NOT NULL UNIQUE, nombre VARCHAR(50) NOT NULL, apellido1 VARCHAR(50) NOT NULL, apellido2 VARCHAR(50) DEFAULT NULL, telefono VARCHAR(9) DEFAULT NULL, FOREIGN KEY (codigo_sucursal) REFERENCES sucursal(codigo) ); CREATE TABLE periodista ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(50) NOT NULL, apellido1 VARCHAR(50) NOT NULL, apellido2 VARCHAR(50), telefono VARCHAR(9), especialidad VARCHAR(50) NOT NULL ); CREATE TABLE revista ( numero_registro INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, titulo VARCHAR(50) NOT NULL, periodicidad ENUM('semanal', 'mensual', 'anual') DEFAULT 'semanal', tipo VARCHAR(100) NOT NULL ); CREATE TABLE escribe ( numero_registro INT UNSIGNED, id_periodista INT UNSIGNED, PRIMARY KEY (numero_registro, id_periodista), FOREIGN KEY (numero_registro) REFERENCES revista(numero_registro), FOREIGN KEY (id_periodista) REFERENCES periodista(id) ); CREATE TABLE numero ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, numero_registro INT UNSIGNED NOT NULL, fecha DATE NOT NULL,

José Juan Sánchez Hernández

7

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

numero_paginas SMALLINT UNSIGNED NOT NULL CHECK(numero_paginas > 0), numero_ejemplares INT UNSIGNED NOT NULL CHECK(numero_ejemplares > 0), FOREIGN KEY (numero_registro) REFERENCES revista(numero_registro) ); CREATE TABLE seccion ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, numero_registro INT UNSIGNED NOT NULL, titulo VARCHAR(50) NOT NULL, extension INT UNSIGNED NOT NULL CHECK(extension > 0), FOREIGN KEY (numero_registro) REFERENCES revista(numero_registro) ); CREATE TABLE vende ( codigo_sucursal INT UNSIGNED, numero_registro INT UNSIGNED, PRIMARY KEY (codigo_sucursal, numero_registro), FOREIGN KEY (codigo_sucursal) REFERENCES sucursal (codigo), FOREIGN KEY (numero_registro) REFERENCES revista (numero_registro) );

José Juan Sánchez Hernández

8

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.3 Empresa de material informático Tenemos que diseñar una base de datos para una empresa de material informático, de la que tenemos esta información: • Un equipo consta de varios componentes. Pueden ser necesarios varios componentes del mismo tipo para montar un equipo, por lo que será necesario almacenar la cantidad de componentes que se necesitan en cada caso. • Un cliente puede comprar equipos completos o componentes sueltos. Habrá que almacenar la cantidad de equipos o la cantidad de componentes de cada tipo que compra cada cliente. También habrá que guardar la fecha de la compra. • Cada equipo está etiquetado con un código de equipo, una descripción, un precio y el stock disponible. • Cada componente está etiquetado con un código de componente, una descripción, un precio y el stock disponible. • Los datos que almacenamos los clientes son el NIF, nombre, apellidos, domicilio, ciudad, provincia y teléfono. • Los datos que almacenamos de los empleados son el NIF, nombre, apellidos y la sección donde trabaja. • Un empleado trabaja en una única sección. • Una sección se identifica por un id y un nombre de sección. • En cada compra realizada por un cliente interviene un empleado y será necesario guardar qué empleado es el que ha atendido a cada cliente para cada una de las compras.

José Juan Sánchez Hernández

9

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.3.1 Diagrama E/R

José Juan Sánchez Hernández

10

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.3.2 Diagrama Relacional

1.3.3 Paso a tablas (SQL) DROP DATABASE IF EXISTS empresa_material_informatico; CREATE DATABASE empresa_material_informatico CHARACTER SET utf8; USE empresa_material_informatico; CREATE TABLE equipo ( codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, descripcion VARCHAR(128) NOT NULL, precio FLOAT UNSIGNED NOT NULL CHECK(precio > 0), stock INT UNSIGNED NOT NULL ); CREATE TABLE componente ( codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, descripcion VARCHAR(128) NOT NULL, precio FLOAT UNSIGNED NOT NULL CHECK(precio > 0), stock INT UNSIGNED NOT NULL

José Juan Sánchez Hernández

11

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

); CREATE TABLE consta ( codigo_equipo INT UNSIGNED NOT NULL, codigo_componente INT UNSIGNED NOT NULL, cantidad INT UNSIGNED NOT NULL CHECK(cantidad > 0), PRIMARY KEY (codigo_equipo, codigo_componente), FOREIGN KEY (codigo_equipo) REFERENCES equipo(codigo), FOREIGN KEY (codigo_componente) REFERENCES componente(codigo) ); CREATE TABLE cliente ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nif VARCHAR(9) NOT NULL UNIQUE, nombre VARCHAR(50) NOT NULL, apellido1 VARCHAR(50) NOT NULL, apellido2 VARCHAR(50), telefono VARCHAR(9) NOT NULL, domicilio VARCHAR(100) NOT NULL, ciudad VARCHAR(50) NOT NULL, provincia VARCHAR(50) NOT NULL ); CREATE TABLE seccion ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(100) NOT NULL UNIQUE ); CREATE TABLE empleado ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_seccion INT UNSIGNED NOT NULL, nif VARCHAR(9) NOT NULL UNIQUE, nombre VARCHAR(50) NOT NULL, apellido1 VARCHAR(50) NOT NULL, apellido2 VARCHAR(50) NOT NULL, FOREIGN KEY (id_seccion) REFERENCES seccion(id) ); CREATE TABLE compra_equipos ( codigo_equipo INT UNSIGNED NOT NULL, id_cliente INT UNSIGNED NOT NULL, id_empleado INT UNSIGNED NOT NULL, fecha DATE NOT NULL,

José Juan Sánchez Hernández

12

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

cantidad INT UNSIGNED NOT NULL CHECK(cantidad > 0), PRIMARY KEY (codigo_equipo, id_cliente, id_empleado), FOREIGN KEY (codigo_equipo) REFERENCES equipo(codigo), FOREIGN KEY (id_cliente) REFERENCES cliente(id), FOREIGN KEY (id_empleado) REFERENCES empleado(id) ); CREATE TABLE compra_componentes ( codigo_componente INT UNSIGNED NOT NULL, id_cliente INT UNSIGNED NOT NULL, id_empleado INT UNSIGNED NOT NULL, fecha DATE NOT NULL, cantidad INT UNSIGNED NOT NULL CHECK(cantidad > 0), PRIMARY KEY (codigo_componente, id_cliente, id_empleado), FOREIGN KEY (codigo_componente) REFERENCES componente(codigo), FOREIGN KEY (id_cliente) REFERENCES cliente(id), FOREIGN KEY (id_empleado) REFERENCES empleado(id) );

José Juan Sánchez Hernández

13

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.4 Agencia de viajes Una cadena de agencias de viajes desea disponer de una base de datos que almacene información sobre el hospedaje y los vuelos que contratan los turistas: • La cadena de agencias está compuesta por un conjunto de sucursales. Cada sucursal está definida por el identificador de sucursal, dirección y teléfono. • La cadena tiene contratados una serie de hoteles de forma exclusiva. Cada hotel estará definido por el identificador del hotel, nombre, dirección, ciudad, teléfono y número de plazas disponibles. • La cadena tiene contratados una serie de vuelos regulares de forma exclusiva. Cada vuelo está definido por un identificador, fecha y hora, origen y destino, plazas totales y plazas de clase turista de las que dispone. • La información que se desea almacenar por cada turista es el id de turista, nombre y apellidos, dirección y teléfono. También habrá que tener en cuenta la siguiente información: • A la cadena de agencias le interesa conocer qué sucursal ha contratado el turista. • A la hora de viajar el turista puede elegir cualquiera de los vuelos que ofrece la cadena, y en qué clase (turista o primera) desea viajar. • El turista se puede hospedar en cualquiera de los hoteles que ofrece la cadena, y elegir el régimen de hospedaje (media pensión o pensión completa). Siendo significativa la fecha de llegada y de partida.

José Juan Sánchez Hernández

14

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.4.1 Diagrama E/R

1.4.2 Diagrama Relacional

1.4.3 Paso a tablas (SQL) DROP DATABASE IF EXISTS agencia_viajes; CREATE DATABASE agencia_viajes CHARACTER SET utf8; USE agencia_viajes; CREATE TABLE agencia ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, direccion VARCHAR(50) NOT NULL,

José Juan Sánchez Hernández

15

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

telefono VARCHAR(9) NOT NULL ); CREATE TABLE turista ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(50) NOT NULL, apellido1 VARCHAR(50) NOT NULL, apellido2 VARCHAR(50), direccion VARCHAR(50) NOT NULL, telefono VARCHAR(9) NOT NULL ); CREATE TABLE contrata ( id_agencia INT UNSIGNED, id_turista INT UNSIGNED, PRIMARY KEY (id_agencia, id_turista), FOREIGN KEY (id_agencia) REFERENCES agencia(id), FOREIGN KEY (id_turista) REFERENCES turista(id) ); CREATE TABLE vuelo ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fecha_hora DATETIME NOT NULL, plazas_totales SMALLINT UNSIGNED NOT NULL, plazas_turista SMALLINT UNSIGNED NOT NULL ); CREATE TABLE toma ( id_turista INT UNSIGNED, id_vuelo INT UNSIGNED, clase ENUM('Turista', 'Primera') NOT NULL DEFAULT 'Turista', PRIMARY KEY (id_turista, id_vuelo), FOREIGN KEY (id_turista) REFERENCES turista(id), FOREIGN KEY (id_vuelo) REFERENCES vuelo(id) ); CREATE TABLE hotel ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(50) NOT NULL, direccion VARCHAR(100) NOT NULL, ciudad VARCHAR(50) NOT NULL, plazas SMALLINT UNSIGNED NOT NULL, telefono VARCHAR(9) NOT NULL

José Juan Sánchez Hernández

16

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

); CREATE TABLE reserva ( id_turista INT UNSIGNED, id_hotel INT UNSIGNED, fecha_entrada DATETIME NOT NULL, fecha_salida DATETIME NOT NULL, regimen ENUM('MP', 'PC') NOT NULL, PRIMARY KEY (id_turista, id_hotel), FOREIGN KEY (id_turista) REFERENCES turista(id), FOREIGN KEY (id_hotel) REFERENCES hotel(id) );

José Juan Sánchez Hernández

17

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.5 Exámenes Los profesores del módulo de Bases de Datos deciden crear una base datos que contenga la información de los resultados de las pruebas realizadas por los alumnos: • Los alumnos están definidos por un identificador único, NIF, nombre, apellidos y el grupo al que asisten a clase. Los grupos se identifican con una letra mayúscula que puede estar entre la A y la Z. • Dichos alumnos realizan dos tipos de pruebas a lo largo del curso académico: – Exámenes teóricos. Cada alumno realiza varios a lo largo del curso y se definen por un identificador único, un título, el número de preguntas y la fecha de realización (será la misma para todos los alumnos que realizan el mismo examen). Habrá que almacenar la nota de cada alumno por examen. – Prácticas. Se realiza un número indeterminado de prácticas durante el curso académico. Se definen por un identificador, un título y el grado de dificultad. Los posibles grados de dificultad que pueden aparecer son: Baja, Media y Alta. En este caso los alumnos pueden examinarse de cualquier práctica cuando lo deseen, debiéndose almacenar la fecha y la nota obtenida. • De los profesores vamos a almacenar un identificador, NIF, nombre y apellidos. • Nos interesa saber qué profesor o profesores han participado en el diseño de una práctica. Tenga en cuenta que en el diseño de una práctica puede colaborar más de un profesor y que un profesor puede diseñar más de una práctica. También interesa almacenar la fecha en la que cada profesor ha participado en el diseño de la práctica. En el caso de que un profesor participe en el diseño de una misma práctica en fechas distintas sólo guardaremos la última fecha en la que participó.

José Juan Sánchez Hernández

18

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

1.5.1 Diagrama E/R

1.5.2 Diagrama Relacional

1.5.3 Paso a tablas (SQL) DROP DATABASE IF EXISTS examenes; CREATE DATABASE examenes CHARACTER SET utf8; USE examenes; CREATE TABLE alumno ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nif VARCHAR(9) NOT NULL UNIQUE, nombre VARCHAR(50) NOT NULL, apellido1 VARCHAR(50) NOT NULL, apellido2 VARCHAR(50),

José Juan Sánchez Hernández

19

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

grupo CHAR(1) NOT NULL CHECK(grupo >= 'A' AND grupo 0), fecha DATE NOT NULL ); CREATE TABLE hace ( id_alumno INT UNSIGNED,

José Juan Sánchez Hernández

20

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

id_examen_teorico INT UNSIGNED, nota FLOAT NOT NULL CHECK(nota >= 0 AND nota =1 AND mes_caducidad=2017), codigo_seguridad SMALLINT(3) UNSIGNED ZEROFILL CHECK(codigo_seguridad>=0 AND codigo_seguridad 0), nombre_archivo VARCHAR(50) NOT NULL, duracion TIME NOT NULL, thumbnail VARCHAR(512) NOT NULL, reproducciones INT UNSIGNED NOT NULL DEFAULT 0, likes INT UNSIGNED NOT NULL DEFAULT 0, dislikes INT UNSIGNED NOT NULL DEFAULT 0, estado ENUM('Publico', 'Privado', 'Oculto') NOT NULL DEFAULT 'Publico', fecha_publicacion DATETIME NOT NULL, FOREIGN KEY (id_usuario) REFERENCES usuario(id) ); CREATE TABLE etiqueta ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(50) NOT NULL UNIQUE ); CREATE TABLE video_tiene_etiqueta ( id_video INT UNSIGNED, id_etiqueta INT UNSIGNED, PRIMARY KEY (id_video, id_etiqueta), FOREIGN KEY (id_video) REFERENCES video(id), FOREIGN KEY (id_etiqueta) REFERENCES etiqueta(id) ); CREATE TABLE canal (

José Juan Sánchez Hernández

43

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_usuario INT UNSIGNED, nombre VARCHAR(100) NOT NULL, descripcion VARCHAR(1024) NOT NULL, fecha_creacion DATETIME NOT NULL, FOREIGN KEY (id_usuario) REFERENCES usuario(id) ); CREATE TABLE usuario_suscribe_canal ( id_usuario INT UNSIGNED, id_canal INT UNSIGNED, PRIMARY KEY (id_usuario, id_canal), FOREIGN KEY (id_usuario) REFERENCES usuario(id), FOREIGN KEY (id_canal) REFERENCES canal(id) ); CREATE TABLE usuario_valora_video ( id_usuario INT UNSIGNED, id_video INT UNSIGNED, valoracion ENUM('Like', 'Dislike') NOT NULL, fecha_hora DATETIME NOT NULL, PRIMARY KEY (id_usuario, id_video), FOREIGN KEY (id_usuario) REFERENCES usuario(id), FOREIGN KEY (id_video) REFERENCES video(id) ); CREATE TABLE playlist ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_usuario INT UNSIGNED NOT NULL, nombre VARCHAR(50) NOT NULL, fecha_creacion DATE NOT NULL, estado ENUM('Publica', 'Privada') NOT NULL DEFAULT 'Publica', FOREIGN KEY (id_usuario) REFERENCES usuario(id) ); CREATE TABLE playlist_contiene_video ( id_playlist INT UNSIGNED, id_video INT UNSIGNED, PRIMARY KEY (id_playlist, id_video), FOREIGN KEY (id_playlist) REFERENCES playlist(id), FOREIGN KEY (id_video) REFERENCES video(id) );

José Juan Sánchez Hernández

44

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

CREATE TABLE video_relacionado ( id_video INT UNSIGNED, id_video_relacionado INT UNSIGNED, PRIMARY KEY (id_video, id_video_relacionado), FOREIGN KEY (id_video) REFERENCES video(id), FOREIGN KEY (id_video_relacionado) REFERENCES video(id) ); CREATE TABLE comentario ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, id_usuario INT UNSIGNED, id_video INT UNSIGNED, texto VARCHAR(2048) NOT NULL, fecha_hora DATETIME NOT NULL, FOREIGN KEY (id_usuario) REFERENCES usuario(id), FOREIGN KEY (id_video) REFERENCES video(id) ); CREATE TABLE usuario_valora_comentario ( id_usuario INT UNSIGNED, id_comentario INT UNSIGNED, valoracion ENUM('Like', 'Dislike') NOT NULL, fecha_hora DATETIME NOT NULL, PRIMARY KEY (id_usuario, id_comentario), FOREIGN KEY (id_usuario) REFERENCES usuario(id), FOREIGN KEY (id_comentario) REFERENCES comentario(id) );

José Juan Sánchez Hernández

45

Tema 2. Diseño de bases de datos relacionales

IES Celia Viñas (Almería) - 2017/2018

3 Créditos Muchos de los ejercicios y diagramas que aparecen en este texto han sido extraídos de las siguientes referencias: • Diseño de Bases de Datos. Problemas resueltos. Ra-Ma. Adoración de Miguel, Paloma Martínez, Elena Castro. • Introducción a la Informática. Licenciado en ADE. UPV. Miguel Rebollo, Javier Martín, Álvaro Hermida y Mario González. • Gestión de Bases de Datos. José Antonio Muñoz Jiménez. ¡Gracias por compartir vuestro trabajo! :)

4 Licencia Esta obra está bajo una licencia de Creative Commons Reconocimiento-CompartirIgual 4.0 Internacional.

José Juan Sánchez Hernández

46