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

2 Ejercicios Modelo Entidad-Relación

Diseña el modelo entidad-relación para cada uno de los siguientes ejercicios.

2.1 Proveedores

Tenemos que diseñar una base de datos sobre proveedores y disponemos de la siguiente información:

2.1.1 Modelo conceptual

2.1.1.1 Diagrama E/R

2.1.2 Modelo lógico

Las reglas de transformación de E/R al modelo relacional nos dicen que la relación Suministra 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 proveedor nos suministre piezas con el mismo código 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.

#código_proveedor #código_pieza fecha cantidad
1 1 1/01/2018 100
1 1 20/01/2018 100

Para solucionarlo podemos incluir el atributo fecha como parte de la clave primaria de la tabla, de modo que la clave primaria estaría compuesta por código_proveedor, código_pieza y fecha. La solución sería la siguiente:

En este caso ya no habría ningún problema para almacenar que un proveedor nos suministra piezas con el mismo código en fechas diferentes.

#código_proveedor #código_pieza #fecha cantidad
1 1 1/01/2018 100
1 1 20/01/2018 100

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 proveedor nos suministra piezas con el mismo código en fechas diferentes.

#id código_proveedor código_pieza fecha cantidad
1 1 1 1/01/2018 100
2 1 1 20/01/2018 100

2.1.2.1 Solución 1. Modelo relacional

2.1.2.2 Solución 1. Diagrama relacional

2.1.2.3 Solución 2. Modelo relacional

2.1.2.4 Solución 2. Diagrama relacional

2.1.3 Modelo físico

2.1.3.1 Solución 1. Base de datos para MySQL

DROP DATABASE IF EXISTS proveedores;
CREATE DATABASE proveedores CHARSET utf8mb4;;
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 UNSIGNED NOT NULL,
  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 proveedor_suministra_pieza (
  codigo_proveedor INT UNSIGNED,
  codigo_pieza INT UNSIGNED,
  cantidad INT UNSIGNED NOT NULL,
  fecha DATE NOT NULL,
  PRIMARY KEY (codigo_proveedor, codigo_pieza, fecha),
  FOREIGN KEY (codigo_proveedor) REFERENCES proveedor(codigo),
  FOREIGN KEY (codigo_pieza) REFERENCES pieza(codigo)
);

2.1.3.2 Solución 2. Base de datos para MySQL

DROP DATABASE IF EXISTS proveedores;
CREATE DATABASE proveedores CHARSET utf8mb4;;
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 UNSIGNED NOT NULL,
  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 proveedor_suministra_pieza (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo_proveedor INT UNSIGNED,
  codigo_pieza INT UNSIGNED,
  cantidad INT UNSIGNED NOT NULL,
  fecha DATE NOT NULL,
  FOREIGN KEY (codigo_proveedor) REFERENCES proveedor(codigo),
  FOREIGN KEY (codigo_pieza) REFERENCES pieza(codigo)
);

2.2 Cadena editorial

Tenemos esta información sobre una cadena editorial:

2.2.1 Modelo conceptual

2.2.1.1 Solución 1. Diagrama E/R

Solución con entidades débiles por identificación.

2.2.1.2 Solución 2. Diagrama E/R

Solución sin utilizar entidades débiles por identificación.

2.2.2 Modelo lógico

2.2.2.1 Modelo relacional

2.2.2.2 Diagrama relacional

2.2.3 Modelo físico

2.2.3.1 Base de datos para MySQL

DROP DATABASE IF EXISTS cadena_editorial;
CREATE DATABASE cadena_editorial CHARSET utf8mb4;
USE cadena_editorial;

CREATE TABLE sucursal (
  codigo INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  direccion VARCHAR(100) NOT NULL,
  ciudad VARCHAR(50) NOT NULL,
  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 periodista_escribe_revista (
  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 ejemplar (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  numero_registro INT UNSIGNED NOT NULL,
  fecha DATE NOT NULL,
  numero_paginas SMALLINT UNSIGNED NOT NULL,
  numero_ejemplares INT UNSIGNED NOT NULL,
  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,
  FOREIGN KEY (numero_registro) REFERENCES revista(numero_registro)
);

CREATE TABLE sucursal_publica_revista (
  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)
);

2.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:

2.3.1 Modelo conceptual

2.3.1.1 Diagrama E/R

2.3.2 Modelo lógico

2.3.2.1 Solución 1. Modelo relacional

En esta solución hemos añadido el atributo fecha como parte de la clave primaria.

2.3.2.2 Solución 1. Diagrama relacional