Los pasos del diseño de una Base de Datos, representados en la siguiente figura, se pueden resumir en

• Recolección y análisis de requerimientos: En este paso recogemos información del sistema para el que debemos diseñar la Base de Datos.

• Diseño conceptual: Una vez recogidos todos los requisitos y conocido el problema, realizamos un primer esquema conceptual en algún lenguaje de alto nivel como es el Modelo Entidad-Relación

• Diseño lógico: El diseño conceptual debe ser ahora transformado en un diseño lógico, que es la transformación de un modelo conceptual a un modelo de datos concreto con el fin de poder representar el problema, más adelante, en algún software concreto. En nuestro caso usaremos el Modelo Relacional.

• Diseño físico: En este punto debemos aplicar el modelo lógico de datos del punto anterior sobre un SGBD concreto. Dependiendo del diseño físico escogido, tendremos un abanico de posibilidades en cuanto al software disponible. En nuestro caso hemos optado por un modelo relacional por lo que tendremos que escoger entre los SGBD relacionales disponibles. En este curso será MySQL.

Es un modelo de datos que representa la realidad a través de entidades , que son objetos que existen y se distinguen de otros por sus características, que llamamos atributos. Además, estas entidades podrán o no estar relacionadas unas con otras a través de lo que se conoce como relación. Hay que tener en cuenta que se trata solamente de un modelo de representación, por lo que no tiene correspondencia real con ningún sistema de almacenamiento. Se utiliza en la etapa de Análisis y Diseño de una Base de Datos, por lo que habrá que convertirla a otro modelo antes de poder empezar a trabajar con ella.

El modelo Entidad-Relación es un lenguaje de modelado y como todo lenguaje tiene sus propios términos y sintaxis. A diferencia de los lenguajes textuales, el modelo Entidad-Relación utiliza un lenguaje de símbolos:

Una entidad es un objeto que existe en una realidad que queremos representar, por ejemplo, un alumno, que se distingue de otro por sus características como pueden ser: el nombre, los apellidos, el número de expediente, . . .

Las entidades se representan por el siguiente símbolo:

Esas características que hacen que unas entidades se distingan de otras, son los atributos. El nombre, los apellidos y el número de expediente serían atributos de la entidad alumno. Los atributos se representan por el siguiente símbolo:

A su vez, podemos relacionar unas entidades con otras a través de lo que se conoce como relación. Por ejemplo, dos entidades alumno y asignatura podrían estar relacionadas entre sí puesto que un alumno cursa una asignatura (o varias). Conviene resaltar que una relación entre dos entidades no expresa obligatoriedad de relación sino posibilidad de relacionarse.

En este caso, no será necesario que todos los alumnos cursen una asignatura o que una asignatura sea cursada por todos los alumnos para que la relación se establezca. Por tanto, en este caso se establece que entre esas dos entidades existe una relación a la que podríamos llamar cursa. Las relaciones se representan por el siguiente símbolo:

Existirán situaciones en las que a la hora de analizar los requisitos interpretemos que en una misma relación hay más de dos entidades involucradas. A este tipo de relaciones se les llama relaciones ternarias, cuaternarias, etc.

Hay que tener en cuenta una cosa: todo modelo E/R se transformará en el modelo Relacional solamente a tablas, independientemente del tipo de relación. Sin embargo, no tendrá la misma facilidad transformar a tablas una relación ternaria que una binaria; probablemente debamos volver a hacer un estudio a la hora de transformar el modelo E/R. Para evitar esto, trataremos de plantear siempre relaciones binarias frente a relaciones ternarias.

Relaciones binarias y ternarias entre entidades

En las situaciones en las que nuestra primera interpretación sea una relación ternaria, trataremos de descomponerla en relaciones binarias de dando sentido a la relación a través de una nueva entidad relacionada con las entidades previas. Por ejemplo: en una base de datos para una tienda, en la que necesitamos saber qué productos ha comprado cada cliente y qué trabajador los ha vendido, podemos plantear una relación terminaria entre comprador, vendedor y producto, obteniendo una relación ternaria. Sin embargo, también podemos crear una nueva entidad llamada venta con ciertas propiedades (fecha, precio, modo de pago) y relacionarla con las tres entidades previas:

Transformación de relación ternaria

Siempre intentaremos plantear relaciones binarias entre las entidades para facilitar la transformación a modelo relacional.

Si consideramos que dos entidades A y B están relacionadas a través de una relación R, deberemos determinar lo que se conoce como cardinalidad de la relación, que determina cuantas entidades de tipo A se relacionan, como máximo, con cuantas entidades de tipo B. Además, resulta conveniente, en cada caso, calcular cuántas entidades de tipo A se relacionan, cómo mínimo, con cuantas entidades de tipo B (que normalmente será 0 ó 1). De esa manera podremos indicar la obligatoriedad o no de relación entre elementos de las entidades A y B.

Cardinalidades parciales de una relación

Son las cardinalidades que nos indican con cuantos elementos de una entidad participan los elementos de la otra entidad, cómo mínimo y como máximo. Cada entidad tiene su cardinalidad parcial, y a partir de ambas se obtiene la cardinalidad completa de la relación. Se separan mediante , y se encierran entre parentesis.

Es una cardinalidad que nos hace ver rápidamente la participación de dos entidades en una relación. La cardinalidad de la relación se indica dentro del símbolo de la relación o debajo de la relación. Se obtiene a partir de los máximos de las cardinalidades parciales de la relación:

Se indica en mayúsculas y separada por :

En esta relación una entidad de tipo A sólo se puede relacionar con una entidad de tipo B, y viceversa. Por ejemplo, si suponemos dos entidades Curso y Aula, relacionadas a través de una relación Se Imparte, podremos suponer que un Curso se imparte en una Aula y en una Aula sólo se puede impartir un Curso. Se representaría como sigue:

Indica que una entidad de tipo A se puede relacionar con un número indeterminado de entidades de tipo B, pero a su vez una entidad de tipo B sólo puede relacionarse con una entidad de tipo A. Si suponemos una entidad Propietario y otra entidad Vehículo relacionadas a través de una relación Posee, podremos suponer que un Propietario puede poseer varios Vehículos, mientras que cada Vehículo sólo puede pertenecer a un Propietario.

Quedaría representado de la siguiente manera:

Significa que una entidad de tipo A sólo puede relacionarse con una entidad de tipo B, pero una entidad de tipo B puede relacionarse con un número indeterminado de entidades de tipo A. En realidad se trata como una relación uno a muchos pero el sentido de la relación es el inverso.

En este caso, tanto las entidades de tipo A y B, pueden relacionarse con un número indeterminado de entidades del otro tipo. Por ejemplo, si suponemos las entidades Alumno y Asignatura y una relación Cursa, podremos suponer que un Alumno cursa varias asignaturas mientras que una Asignatura la cursan varios Alumnos. Quedaría representado de la siguiente manera:

Existen situaciones en las que a la hora de modelar ciertos requisitos, nos encontramos con que hay ciertos datos que debe almacenar la base de datos que no son características realmente propias de ninguna de las entidades que conforman una relación. En esos caso es probable que dichas propiedades pertenezcan a la relación en sí.

Ejemplo: Un alumno puede cursar varias veces la misma asignatura por lo que debemos almacenar el año en que la cursa.

Se conoce como Diagrama Entidad/Relación (E/R) al diagrama resultante de modelar un mundo real siguiendo el modelo Entidad/Relación. Como resultado, se modelan todas las entidades con sus atributos, así como todas las relaciones existentes entre ellas, junto con su cardinalidad.

Hay algunos tipos concretos de relaciones que es interesante comentar:

También conocida como Generalización/Especialización. La relación de herencia, representada como un triángulo, expresa que un objeto es un subtipo de otro objeto. También se suele considerar al subtipo como una especialización del primero o al primero como una generalización del segundo.

En el caso del ejemplo, existen dos tipos de empleados que se relacionan de forma diferente con otros objetos del sistema, pero que a su vez pueden tener gran parte en común. Por ejemplo, trabajan de forma diferente pero muchos de los datos personales que almacenaremos de ambos son comunes. Es por eso que el objeto Empleado se puede considerar una generalización de los dos tipos de trabajadores que hay en el sistema. Todos aquellos atributos y relaciones que tengan en común serán atributos y funcionalidades del objeto Empleado y los atributos y relaciones que tengan como trabajadores especializados(Encargados o repartidores) serán representados en la correspondiente entidad.

¿Cuándo nos interesa plantear una relación de herencia?

• Si las entidades padres e hijas tienen relaciones diferentes con otras entidades.
• Si las entidades hijas tienen atributos diferentes a la entidad padre.

Es posible que la misma entidad ocupe ambos lados de una relación. En ese caso estamos frente a lo que se conoce como relaciones reflexivas. La cardinalidad de la relación indicará si todos los elementos de la relación están relacionados reflexivamente o bien sólo algunos están relacionados entre sí. En el caso de la figura podríamos suponer una empresa en la que algunos empleados hacen de supervisor de otros empleados.

Las relaciones reflexivas indican todas sus cardinalidades del mismo modo que cualquier otra relación.

Ademas de los atributos simples que hemos visto en la primera parte, también existen otros tres tipos de atributos que podemos emplear:

Los atributos multivaluados son aquellos atributos que pueden contener una cantidad indeterminada de valores. Por ejemplo, el teléfono de un empleado podría tomar varios valores para alguien que posea varios teléfonos.

Se representan del siguiente modo:

Son atributos que pueden ser divididos en subpartes; éstas constituirán otros atributos con significado propio. Por ejemplo, la dirección del empleado podría considerarse como un atributo compuesto por la calle, el número y la localidad.

Normalmente son atributos que pueden descomponerse aunque dependiendo del contexto de la aplicación puede no interesar hacer esa descomposición y tratarlo como un atributo simple.

Los atributos derivados (o calculados) son aquellos atributos cuyo valor puede ser deducido realizando algunas operaciones con otros atributos de la misma entidad o de otras entidades. En algunas situaciones se podría considerar redundante (puesto que su valor se puede deducir) pero en otras puede resultar cómodo almacenarlo ya calculado puesto que se puede ahorrar mucho tiempo de cómputo si se trata de un valor de díficil y/o recurrente cálculo.

El modelo Entidad Relación es el lenguaje más común para hacer diseños comceptuales de bases de datos. Es un modelo creado por Peter Chen en el año 1976, y ha pasado por diferentes ampliaciones, y han contribuido diferentes personalidades, por lo que a día de hoy existen diferentes notaciones para representarlo. Es por eso, que además de los elementos vistos en esta parte del tema, podemos encontrar otros elementos u otras formas de representación.

A continuación se muestran algunas. Se pueden ampliar pulsando sobre las imágenes:

La elección de los elementos expuestos en este bloque se basa en dos cuestiones:

• Elementos del lenguaje fáciles de comprender y aplicar en el diseño de una base de datos.
• Conjunto de elementos suficiente y necesario para plantear cualquier situación de diseño de una bbdd.

1. Resulta cómodo que las entidades estén escritas en minúscula para hacer todas estas comprobaciones
2. Comprobar que nuestro diagrama no se ha convertido en un diagrama de flujo y no describe procesos, sino almacenes de datos
3. Comprobar que las Entidades son nombres de cosas y las relaciones son verbos
4. Comprobar que ninguna Entidad tiene como atributo algo que existe como Entidad (si ocurre, se deberían relacionar ambas Entidades)
5. Comprobar que varias entidades no comparten un mismo atributo estructurado que pueda ser considerado realmente como una Entidad
6. Evitar los ciclos (si aparece alguno, que puede ocurrir, comprobar que es necesario)
7. Si una relación tiene varios atributos, valorar si es posible que realmente deba ser una nueva Entidad (Comprar → Pedido, Alquilar → Alquiler, Reservar → Reserva, Enviar → Envío, . . .)
8. Comprobar que no hay colocada ninguna cardinalidad al revés: Se tiene que poder leer: un Usuario Realiza de 0 a N Pedidos. Usuario y Pedido son entidades y Realizar la relación entre ambas. En este caso, (0, N) debería estar escrito en el lado Pedido para que pudiera leerse correctamente

• Diseñar un modelo Entidad/Relacion (entidades, atributos y relaciones)

• Diseñar un modelo Entidad/Relación (ciclos y redundancia]

• Diseñar un modelo Entidad/Relación (atributos multivaluados, compuestos y derivados)

El modelo relacional es otro modelo de representación en el que los datos y sus relaciones se representan a través de tablas, y en el que los atributos se traducen en campos de esas tablas. Es el modelo de representación que siguen la gran mayoría de los SGBD relacionales (MySQL, SQL Server, Oracle, Ms Access, entre otros) en la actualidad, puesto que es el modelo de datos más extendido.

Así, es necesario transformar nuestro modelo Entidad/Relación a un modelo relacional si queremos crear nuestra Base de Datos en algún SGBD relacional.

En la práctica no es necesario dibujar una tabla completa, sino que se suele indicar el nombre de los campos, y los campos claves que conforman las relaciones:

Trabajo (#codigo, nombre, posicion, salario)
Empleado(#dni, nombre, apellidos, -codigo_trabajo)
 
Profesor (#id, dni, nombre, apellidos, departamento)
Asignatura (#id, nombre, n_horas, -id_profesor)

Cuando tenemos un diseño lógico organizado en tablas y campos, las relaciones se realizan estableciendo referencias entre columnas de dos tablas. Los sistemas SGBD Relacionales hacen hincapie en el concepto de Integridad Referencial de los datos almacenados.

La Integridad Referencial consiste en mantener las relaciones entre los registros de diferentes tablas de forma consistente e inequívoca, de forma que siempre podamos conocer qué datos de una tabla están relacionados con otro dato de otra tabla.

Para mantener esta integridad de los datos es necesario es uso de dos tipos de campos clave dentro de las tablas: Claves primarias y Claves ajenas o foráneas.

Antes de explicar estos dos tipos de claves vamos a ver los distintos tipos de claves:

Una superclave es una combinación de atributos o columnas cuya combinación de valores permite identificar inequívocamente a cada fila o registro de la tabla.

Una clave candidata es una superclave que no se puede reducir. Cada clave candidata puede ser un solo atributo o una combinación de varios atributos. Una tabla puede tener varias claves candidatas.

Denominamos Clave Primaria, en inglés Primary Key (PK), al campo identificador principal de una tabla. Es la clave candidata elegida por el usuario para identificar a cada registro de la tabla.

La elección del campo que hará de clave primaria corre a nuestra cuenta, pero es habitual crear un campo id autonumérico para tal fin por motivos de simplicidad.

-- Representamos las claves primarias con la almohadilla (#)
 
Trabajo (#codigo, nombre, posicion, salario)
Profesor (#id, dni, nombre, apellidos, departamento)
Asignatura (#id, nombre, n_horas, -id_profesor)

Se suele llamar de este modo a todas las claves candidatas que no se han designado como clave primaria.

Toda tabla cuyos registros tengan relación con los registros de otra tabla, debe tener un campo cuyos valores identifiquen a los registros de otra tabla. A este campo se le conoce como Clave Ajena o Foránea, en inglés Foreign Key (FK). Contiene los valores de la clave primaria de los registros de la tabla con la que se relaciona.

Aparte, es habitual que esta tabla también tengo un campo Clave Primaria.

La tabla Propietario tiene un campo (id) que actúa como clave primaria(PK) de la tabla. No puede haber dos registros con el mismo valor (id). La tabla Vehiculo tiene un campo(id) que actúa como clave primaria de la tabla. Además la tabla Vehículo tiene un campo (id_propietario) que actúa como clave ajena(FK), y referencia cada uno de sus registros con un registro concreto de la tabla Propietario.

De este modo podemos saber quién es el propietario de cada vehículo. Como se trata de una relación 1:N un Propietario puede tener varios Vehículos, pero cada vehículo solo puede tener un propietario.

-- Representamos las claves ajenas mediante un guión (-)
-- Además indicamos añadimos a la columna el nombre de la tabla de la que procede.
Profesor (#id, dni, nombre, apellidos, departamento)
 
-- Una asignatura es impartida por un profesor
Asignatura (#id, nombre, n_horas, -id_profesor) 
 
-- Vehiculos tiene una clave ajena que referencia a peopietarios
Propietarios (#id, nombre, apellidos, . . .)
Vehiculos (#id, matricula, marca, modelo, -id_propietario)

A la hora de seleccionar la columna que ejercerá como clave primaria podemos utilizar dos enfoques:

• Clave Primaria Natural: Son claves primarias que ya existen en nuestra tabla y tienen un significado real; representan datos reales. Ejemplos de claves primarias naturales son: dni, nss, matricula, expediente, o combinaciones de columnas que tengamos en nuestra tabla.
• Clave Primaria Subrogada:Son columnas clave primaria creadas por el programador exclusivamente para el diseño de la base de datos, sin que sean campos existentes en los requisitos de la base de datos. Consiste en añadir una columna autonumérica cuyos valores son generados por el motor de la base de datos de forma automática siendo siempre únicos. El ejemplo que nosotros usamos en estos apuntes es crear una columna id autonumérica.

Ventajas e inconvenientes:

• Para poder designar claves primarias naturales necesito que mi tabla tenga campos que sean claves candidatas. Esta situación no siempre se da, o requiere de la unión de varias columnas.
• A la hora de relacionar tablas, simplifica mucho la tarea que las claves ajenas sean lo más sencillas posibles; mejor si la clave ajena es un solo campo de la tabla a que sea una clave ajena compuesta. Para ello necesito que la clave primaria sea simple, y no se componga de varios campos.
• Las claves primarias subrogadas se pueden añadir a cualquier tabla aunque no tenga claves candidatas, tanto en el momento del diseño, como en fases posteriores que requieren modificar la estructura de la bbdd.

Existen numerosos debates sobre las ventajas e inconvenientes de cada tipo de enfoque.

En este módulo, tanto en los ejemplos como en las explicaciones, se opta por uso de claves primarias subrogadas ya que permite plantear un enfoque más sencillo y escalable. Eso no quiere decir que el diseño sea mejor o peor que utilizando claves naturales.

El paso de un modelo E/R a un modelo relacional se puede llevar a cabo, en gran parte, siguiendo una serie de reglas o pautas, que se enumeran a continuación:

Entidades y atributos:

• Toda entidad se transforma en una tabla.
• Todo atributo simple o derivado se transforma en columna de una tabla.
• Los atributos estructurados desaparecen de la tabla y solamente setransforman los campos de los que se componen en nuevas columnas.
• El identificador único de la entidad se convierte en clave primaria. Siempre que pueda, añadiré un campo id como clave primaria.
• Los atributos multivaluados hacen desaparecer el atributo de la entidad origen generando una nueva tabla con tres columnas: un id, el id de la tabla de la que surgen propagado como clave ajena y el valor del campo multivaluado.

  (#id, -id_tabla_origen, atributo_multivaluado)

Relaciones:

• Toda relación N:M se transforma en una tabla que tendrá como clave primaria la concatenación de los atributos clave de las entidades que relaciona. Cada uno de los dos campos será además clave ajena.
  • Para casos particulares de relación N:M en el que una cardinalidad parcial mínima y máxima coincida (2,2), (3,3), valoraré transformarlo siguiendo el planteamiento de relación 1:N propagando la clave primaria a tantas claves ajenas como indique la cardinalidad.

Ejemplo: Un producto puede estar en varios pedidos, y un pedido puede tener varios productos.

Tablas pedidos, productos y pedido_producto

pedidos(#id, descripcion, fecha)
productos(#id, nombre, descripcion, precio_sin_iva, tipo, codigo_producto)
pedido_producto(#(-id_pedido, -id_producto), precio)

• En la transformación de relaciones 1:N se propaga el atributo clave (habitualmente el campo id) de la entidad que tiene de cardinalidad máxima 1 a la que tiene cardinalidad máxima N, haciendo desaparecer a la relación.

Ejemplo: Un pedido es realizado por un solo cliente, y un cliente puede realizar multiples pedidos.

Tablas pedidos y clientes

pedidos(#id, precio, fecha, -id_cliente)
clientes(#id, nombre, apellidos, direccion, telefono, num_pedidos)

• En la transformación de relaciones 1:1 se tienen en cuenta las cardinalidades de las entidades que participan en ellas. Existen diferentes soluciones:
  1. La transfomación tradicional consiste en que una de las tablas tenga una clave primaria que al mismo tiempo es clave ajena de la otra tabla. Solo se puede plantear si alguna de las cardinalidades parciales es (1,1). En ese caso se propaga la clave primaria de la entidad con cardinalidad (1,1) a la tabla resultante de la entidad de cardinalidad (0,1), en la que será clave primaria y ajena al mismo tiempo. Si ambas cardinalidades parciales son (1,1), no importa hacia qué lado se propaga. (Ejemplo 1)
  2. Otra solución más sencilla es transformarla como si fuera una relación 1:N. Si existe una entidad con cardinalidad parcial (0,1), su tabla tendrá una columna nueva que sería la clave primaria de la otra entidad propagada como clave ajena. Si las cardinalidades parciales son iguales da igual hacia qué tabla se propague la clave primaria. Si usamos esta opción, al crear las tablas en el SGBD, la columna clave ajena debe tener restricción UNIQUE. (Ejemplo 2)
  3. Transformarlo en una tabla. Es un caso muy específico y solo se aplica cuando ambas entidades poseen cardinalidades parciales (0,1) y si solo unos pocos registros de ambas tablas están relacionados. La relación se convierte en una tabla de la misma forma que una relación N:M.

Ejemplo: Un empleado solo puede tener un despacho, y un despacho pertenece a un solo empleado.

Tablas empleados y despachos (Ejemplo 1)

-- Ejemplo 1: Clave primaria y ajena al mismo tiempo
 
empleados(#id, nombre, apellidos, direccion, dni, telefono)
despachos(#(-id_despacho), cod_despacho, direccion, superficie)
 
-- Ejemplo 2: Clave primaria y ajena diferente (como 1:N)
 
empleados(#id, nombre, apellidos, direccion, dni, telefono)
despachos(#id, cod_despacho, direccion, superficie, -id_empleado)

• Las relaciones de herencia se transforman como relaciones 1:1 entre tablas padres e hijas. Aplicando las reglas anteriores, se ha de crear una tabla por cada entidad hija con sus propias columnas.
  1. Para relacionarlas, la forma más directa es propagar la clave primaria de la tabla padre como claves ajenas en las tablas hijas; Las tablas hijas tienen como clave primaria el campo clave de la tabla padre (Ejemplo 1).
  2. La otra forma, menos común, es transfomarla como si fueran dos relaciones 1:N: las tablas hijas tienen su propia clave primaria (id), y añadirán una columna nueva que es la clave primaria de la entidad padre, propagada como clave ajena. En este caso, al crear las tablas hijas en el SGBD, las claves ajenas deben tener las restricciones UNIQUE y NOT NULL) (Ejemplo 2)

Ejemplo: Una pista puede ser una pista_abierta o una pista_cerrada.

Tablas pistas, pistas_abiertas y pistas_cerradas

-- Ejemplo 1: Claves primarias y ajenas al mismo tiempo
 
pistas(#id, nombre, tipo, superficie)
pistas_abiertas(#(-id_pista), fecha_inscripcion, fecha_alta)
pistas_cerradas(#(-id_pista), razon_cierre, fecha_apertura)
 
-- Ejemplo 2: Claves primarias y ajenas diferentes (-id_pista es UNIQUE y NOT NULL)
 
pistas(#id, nombre, tipo, superficie)
pistas_abiertas(#id, fecha_inscripcion, fecha_alta, -id_pista)
pistas_cerradas(#id, razon_cierre, fecha_apertura, -id_pista)

• Las relaciones reflexivas, se transforman antendiendo a su cardinalidad del mismo modo que hemos planteado en los casos anteriores:
  • Si la cardinalidad es N:M, se crea una tabla nueva como el caso general de relación N:M. La clave primaria se compone de dos columnas que referencian al mismo id de la propia tabla.
  • Si la cardinalidad es 1:N, se transforma como si se tratara de una relación 1:N, se propaga la clave primaria a la misma tabla como clave ajena.
  • Si la cardinalidad es 1:1, al tratarse de la misma tabla, se transforma como si se tratara de una relación 1:N. Debemos indicar en el SGBD que la columna clave ajena no permitirá valores repetidos (restricción UNIQUE).

Un empleado puede supervisar a varios empleados

-- Relacion 1:N y 1:1
empleados(#id, nombre, apellidos, dni, -id_empleado_supervisor)
 
-- Relación N:M
empleados(#id, nombre, apellidos, dni)
empleados_supervisores(#(-id_empleado_supervisor, -id_empleado_supervisado))

• Para los atributos de las relaciones existen dos casos:
  • Si la relación es 1:N, sus atributos se propagan a la tabla de lado N, junto con la clave del lado 1
  • Si la relación es N:M, sus atributos se transforman en columnas de la tabla generada por dicha relación
  • Las relaciones 1:1 no deberían de tener atributos, ya que en principio deben pertenecer a alguna de las dos entidades.

Las situaciones más particulares habrá que estudiarlas y aplicar algún patrón de diseño conocido, si lo hay, para generar el correspondiente modelo relacional. Estos casos no siempre se podrán reflejar en el correspondiente modelo Entidad-Relación puesto que algunos tienen que ver con exigencias técnicas o de tiempo, más que con el propio modelo de datos. Algunos casos particulares pueden ser:

• Datos temporales: Cómo representar en un modelo relacional datos que tienen fecha de caducidad (los precios de un producto del que queremos tener un histórico de precios, . . .)
• Datos eliminados: Cómo representar en un modelo relacional datos que se desean eliminar, pero que por alguna razón necesitamos que sigan estando almacenados en la base de datos (productos descatalogados, alumnos que terminan sus estudios, . . .)
• Registro o auditoría: Cómo podemos registrar las acciones de los usuarios o de la aplicación durante el ciclo de vida de la Base de Datos.
• Bloqueo de registros: Cómo podemos bloquear el acceso a un registro para evitar la modificación simultánea del mismo dato por más de un usuario desde la aplicación que conecta con la Base de Datos.

En cualquier caso, aplicar correctamente al modelo relacional resultante las reglas de normalización eliminará todas las anomalías que nuestro modelo pueda contener. Así, hay que tener en cuenta que el modelo relacional que hemos obtenido en este momento todavía puede no ser el definitivo y puede sufrir transformaciones (e incluso se pueden añadir nuevas tablas) como resultado de aplicar las reglas de normalización que se pasan a explicar en el siguiente punto.

• Transformar relaciones 1 a 1 al modelo relacional

• Transformar relaciones N a M al modelo relacional

• Transformar relaciones 1 a N al modelo relacional

• Tranformar una herencia al modelo relacional

• Transformar relaciones reflexivas al modelo relacional

• Transformar atributos al modelo relacional

• Ejemplo completo de diseño y transformación de modelo Entidad/Relación a modelo relacional

• Transformar un modelo Entidad/Relación a modelo relacional

Uno de los retos en el diseño de toda Base de Datos es el obtener una estructura estable tal que:

• El sistema no sufra de anomalías de almacenamiento
• El modelo lógico pueda modificarse si aparecen nuevos requisitos

Una Base de Datos bien diseñada tiene mayor esperanza de vida, incluso en un ambiente dinámico donde puedan aparecer nuevos requisitos, que una Base de Datos con un diseño pobre. Como media, una Base de Datos puede sufrir una reorganización cada seis años, dependiendo de lo dinámico que sean sus requisitos. Si la Base de Datos se diseño bien seguirán teniendo un buen rendimiento aunque aumente el tamaño, y será lo suficientemente flexible para soportar los nuevos requisitos y/o características adicionales.

Actualmente existen diversos riegos en el diseño de Bases de Datos relacionales. Los más habituales son la redundancia de información, la inconsistencia de datos y el no aprovechamiento de espacio en disco.

La normalización es el proceso de simplificar la relación entre los campos de un registro de forma que éste se reemplaza por varios registros más simples y predecibles y, por tanto, más manejables. En definitiva, la normalización busca simplificar el diseño para que éste sea más fácil incorporar nuevas funcionalidades con el paso del tiempo y no baje su rendimiento cuando la cantidad de datos almacenados en ella aumenten considerablemente.

La teoría de la normalización se basa en lo que se conoce como Formas Normales. Cada una de estas Formas Normales establece una serie de restricciones que el diseño deberá cumplir para satisfacer dicha Forma. Se considera que una base de datos que cumple las tres primeras FN (Formas Normales) tiene un nivel suficiente de normalización.

Se dice que una tabla está en primera forma normal si una tabla posee las siguientes propiedades:

• Cada columna tiene un solo valor y un solo tipo de datos
• El orden de las filas y las columnas no importa
• Dos filas no contienen valores idénticos
• Las columnas no pueden contener valores repetidos o que representan lo mismo

Supongamos el caso más común, que un campo pueda tener más de un valor:

Es similar al caso de tablas que poseen columnas con valores repetidos (una tabla de clientes con los campos teléfono 1, teléfono 2, teléfono 3, . . .):

En el caso anterior, hay columnas con valores nulos (vacias) y si hay una gran cantidad de registros la definición de la tabla ocupará mucho espacio en desuso. Del mismo modo tendremos problemas a la hora de realizar consultas del tipo: ¿Cual es el teléfono2 de los clientes?, ¿Cuantos números de teléfono tiene el usuario 123?, ó ¿Que usuarios tienen el mismo número de teléfono?.

De cualquier modo estás tablas no cumplen la 1FN. Para normalizar debemos crear una tabla nueva para los teléfonos:

• Se localizan los atributos correspondientes a la clave principal.
• Cada una de las columnas o valores repetidos se separan en una nueva tabla, de manera que se hace la proyección de la clave primaria de la tabla de la que proceden sobre cada uno de los valores del atributo que no es atómico.

Esta formal normal sólo debe ser considerada para aquellas tablas en las que la clave principal sea compuesta. Si no fuera así, la tabla estaría, de forma directa, en segunda forma normal.

Decimos que una tabla está en segunda forma normal si se cumplen las siguientes condiciones:

• Está en 1FN
• Todo atributo secundario (que no pertenezca a la clave principal) tiene una dependencia funcional total de la clave principal, y no de una parte de ella

Se dice un atributo B depende funcionalmente de A (A→B) si cada valor de A se corresponde con un único valor de B. Visto de otra manera, si dado A puedo obtener B. Un caso típico podría ser DNI → Nombre, puesto que dado un DNI puedo obtener, de forma unívoca, el nombre de la persona

Para convertir una tabla que no está en 2FN se creará una tabla con la clave y todas sus dependencias funcionales totales y otra tabla con la parte de la clave que tiene dependencias con los atributos secundarios.

En el ejemplo podemos ver como el campo TelefonoProveedor no depende totalmente de la clave (NombreProducto, NombreProveedor), sino únicamente del campo NombreProveedor.

Clave primaria (NombreProducto, NombreProveedor). Existen dependencias funcionales

Elimino los atributos que depende de una parte de la clave primaria

Los junto en una tabla propia

Se dice que una tabla está en tercera forma normal si:

• Está en 2FN
• No existen atributos no primarios (que no pertenezcan a la clave) que son transitivamente dependientes de cada posible clave de la tabla. Es decir, un atributo secundario sólo puede ser conocido a través de la clave principal y no por medio de un atributo no primario.

Para convertir una tabla que no está en 3FN se realizará una proyección de la clave a los elementos que no tengan dependencia funcional transitiva y otra tabla con una nueva clave a los elementos que anteriormente tenían esta dependencia.

En el ejemplo, es posible conocer la edad del inscrito a través del número de licencia, y dada la edad podemos conocer su categoría, tenemos una dependencia funcional transitiva entre categoría y número de licencia. Lo importante es reconocer que la categoría depende de un atributo que no forma parte de la clave. Para normalizar, debemos descomponer esa tabla en las tablas Atletas y Categorías

Tabla con dependencias transitivas (categoría → edad)

Tabla Atletas

Tabla categorías

El lenguaje SQL (Structured Query Language) permite la comunicación con el SGBD. Actualmente es el lenguaje estándar para la gestión de Bases de Datos relacionales para ANSI (American National Standard Institute) e ISO (International Standarization Organization). Entre las principales características de este lenguaje destaca que es un lenguaje para todo tipo de usuarios ya que quién lo utiliza especifica qué quiere, pero no dónde ni cómo, de manera que permite realizar cualquier consulta de datos por muy complicada que parezca.

En este bloque vamos a trabajar con el Sistemas Gestor de Bases de Datos MySql/MariaDB. Este programa tiene un arquitectura cliente-servidor, de modo que necesitamos un cliente para conectarnos al servidor de bases de datos para poder trabajar con ellas. Para conectarnos al servidor tenemos principalmente dos opciones:

• Utilizar un terminal de comandos para usar el programa cliente mysql
• Utilizar un cliente con interfaz gráfica: (mysql workbench, heidisql, phpMyAdmin, etc)

Aunque vayamos a trabajar principalmente con un cliente gráfico, a continuación mostramos los comandos necesarios para conectarnos desde un terminal:

# Conectarnos al servidor Mysql
mysql -u miusuario -p micontraseña

# Mostrar las bases de datos del servidor
SHOW DATABASES;

# Crear una base de datos
CREATE DATABASE mi_Base_Datos;

# Conectarse a una base de datos
USE mi_Base_Datos;

# Mostrar las tablas de mi base de datos
SHOW TABLES;

# Mostrar las propiedades de una tabla
DESCRIBE mi_tabla;

En el lenguaje SQL, dependiendo de las tareas que se quieran realizar, se distinguen dos tipos de sentencias. Sentencias DDL (Data Definition Language) que sirven para especificar y gestionar estructuras de datos, y las sentencias DML (Data Manipulation Language) que sirven para trabajar con los datos almacenados en dichas estructuras.

Puesto que por ahora abordaremos aquellas sentencias que nos van a permitir crear nuestras Bases de Datos en un SGBD relacional, comenzaremos por ver el grupo de sentencias DDL, que son las que se citan a continuación:

Es la sentencia utilizada para la creación de un objeto (base de datos, tabla, usuario, vista, procedimiento, . . .) en una Base de Datos.

Para crear una Base de Datos:

CREATE DATABASE [IF NOT EXISTS] <nombre_base_de_datos>

Para crear una tabla:

CREATE TABLE [IF NOT EXISTS] <nombre_tabla>
(
   <nombre_columna1>  <tipo_dato>  <restricciones>,
   <nombre_columna2>  <tipo_dato>  <restricciones>,
   ................................
)

Permite realizar la conexión con una Base de Datos de MySQL. Es necesario conectarse a una base de datos, para crear la estructura de esa base de datos.

    USE <nombre_base_de_datos>

Ejemplo completo:

-- Crear una base de datos
CREATE DATABASE colegio;
 
-- Conectarse a la base de datos
USE colegio;
 
-- Crear una tabla
CREATE TABLE asignaturas(
   id INT PRIMARY KEY,
   nombre VARCHAR(20),
   departamento VARCHAR(20),
   id_curso INT,
   FOREIGN KEY (id_curso) REFERENCES cursos(id)
);

Es la sentencia utilizada para eliminar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de Datos.

La sintaxis para la eliminación de tablas es la siguiente:

DROP TABLE [IF EXISTS] <nombre_tabla>

Y para eliminar una Base de Datos:

DROP DATABASE [IF EXISTS] <nombre_base_de_datos>

Es la sentencia utilizada para modificar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de Datos.

La sintaxis para modificar una tabla es la siguiente:

    ALTER TABLE <nombre_tabla>
        [ ADD <definicion_columna> ]
        [ MODIFY <nombre_columna> <definicion_columna> ]
        [ DROP COLUMN <nombre_columna> ]
        [ ADD CONSTRAINT <restriccion> ]
        [ CHANGE <nombre_columna> <nuevo_nombre> <definicion_nueva_columna>]
        [ AUTO_INCREMENT = <valor> ]

Ejemplos:

-- Añadir o eliminar columna
ALTER TABLE alumnos ADD COLUMN edad INT DEFAULT 18;
ALTER TABLE alumnos DROP COLUMN edad;
 
-- Modifica la definición de la columna
ALTER TABLE alumnos MODIFY nombre VARCHAR(30) NOT NULL;
-- Modifica el nombre y la definición de la columna
ALTER TABLE alumnos CHANGE nombre nick VARCHAR(30) NOT NULL;
 
-- Añadir o quitar claves primarias o ajenas
ALTER TABLE alumnos ADD PRIMARY KEY(id);
ALTER TABLE alumnos DROP PRIMARY KEY;
 
ALTER TABLE alumnos ADD FOREIGN KEY (id_curso) REFERENCES cursos(id);
-- Igual a la anterior pero indicando el nombre de la restricción
ALTER TABLE alumnos ADD CONSTRAINT nombre_fk_1 FOREIGN KEY (id_curso) REFERENCES cursos(id);
ALTER TABLE alumnos DROP FOREIGN KEY nombre_fk;
 
-- Renombar tabla
ALTER TABLE alumnos RENAME estudiantes;

En la documentación oficial tenemos más ejemplos de uso.

Este tipo de datos permite almacenar cadenas de texto fijas (CHAR) o variables (VARCHAR).

El tipo CHAR permite almacenar cadenas de caracteres de longitud fija entre 1 y 255 caracteres. La longitud de la cadena se debe especificar entre paréntesis en el momento de la declaración (cadena CHAR(25)).

Por otro lado, el tipo VARCHAR permite almacenar cadenas de caracteres variables. La declaración del tipo VARCHAR es similar a la de un tipo CHAR (cadena VARCHAR(25)). La principal y única diferencia entre estos dos tipos, es que el tipo CHAR declara una cadena fija de la longitud que se especifica mientras que en la declaración de un tipo VARCHAR lo que se especifica es un tamaño máximo, la cadena sólo ocupará el tamaño necesario para almacenar el dato que contenga (hasta llegar al máximo). En cualquier caso, no es posible almacenar cadenas de mayor tamaño al especificado en su declaración, puesto que el SGBD truncará el valor almacenándose sólo hasta la longitud establecida.

El tipo TEXT permite almacenar cadenas de caracteres de hasta varios GB de longitud. Sólo se recomienda su uso para almacenar textos realmente grandes, puesto que presenta ciertas restricciones, aunque algunas pueden variar dependiendo del SGBD que se utiliza:

• Sólo se puede definir una columna TEXT por tabla
• No se pueden establecer restricciones en columnas de este tipo
• No se permite su utilización en ciertas cláusulas

Para la representación de tipos de datos numéricos. Los tipos más utilizados son BIT, TINYINT, INT, BIGINT, para la representación de números enteros de menor o mayor tamaño.

Para número decimales tenemos los tipos FLOAT y DOUBLE, números en coma flotante de menor o mayor precisión, respectivamente. En este caso conviene tener en cuenta los problemas de precisión ¹⁾ que existen con estos tipos de datos. Si necesitamos almacenar valores en los que es muy importante la precisión decimal, podemos utilizar DECIMAL(d,n), d representa la cantidad de digitos del valor, y n la cantidad de decimales después de la coma.

En ocasiones el rango de los valores negativos resultará prescindible (claves numéricas, valores de dinero, cantidades, . . .) por lo que será posible ampliar el rango positivo de un tipo numérico añadiendo la restricción UNSIGNED tras definir el tipo de éste.

id INT UNSIGNED

Los tipos más utilizado para almacenar valores de fechas (DATE) o fechas con hora (DATETIME). Por defecto el formato utilizado es YYYY-MM-DD y YYYY-MM-DD HH:MM:SS respectivamente.

También se puede usar el tipo TIMESTAMP para almacenar una marca de tiempo (fecha y hora YYYY-MM-DD HH:MM:SS). Además, permite el uso de la constante CURRENT_TIMESTAMP en la definición de la columna al definirle un valor por defecto cuando se crea la tabla.

La diferencia entre TIMESTAMP frente a DATETIME, es que a pesar de mostrar el mismo formato, TIMESTAMP almacena también la zona horaria, y devuelve el valor modificado si cambiamos la zona horaria del servidor.

Permite almacenar valores lógicos Verdadero/Falso o Sí/No. Cuando usamos el tipo BOOLEAN para definir un campo, MySQL define internamente la columna como del tipo TINYINT, utilizando los valores 0 y 1 para indicar los valores lógicos true y false respectivamente. Así, podremos utilizar los valores TRUE ó FALSE o directamente asignar 1 ó 0 para asignar valor.

Las restricciones se pueden establecer, o no, a las columnas de cada tabla para forzar a que los datos almacenados en ellas cumplan una serie de condiciones, con la finalidad de que la información sea más correcta. Por ejemplo, podemos obligar a que un campo donde almacenamos el DNI de una persona tenga una longitud mínima, un campo donde almacenamos la categoría de un equipo de fútbol, sólo pueda tomar unos determinados valores predefinidos (benjamín, juvenil, cadete, . . .) o bien podemos hacer que un campo no pueda repetirse, por tratarse de un valor único (DNI, NSS, teléfono, email, . . .).

Hay que tener en cuenta que, por lo general, las restricciones se definen en línea con la definición del campo (tal y como se muestra en la sintaxis de la sentencia de CREATE TABLE, pero de forma opcional también pueden ser definidas por separado justo debajo de la definición de todos los campos de la tabla.

Una clave primaria dentro de una tabla, es una columna o conjunto de columnas cuyo valor identifica unívocamente a cada fila. Debe ser única, no nula y es obligatoria. Como máximo podremos definir una clave primaria por tabla y es muy recomendable definirla.

Para definir una clave primaria utilizamos la restricción PRIMARY KEY.

CREATE TABLE personas(
   dni VARCHAR(9) PRIMARY KEY,
   ...
);

Y si lo hacemos al final de la definición de las columnas, quedaría así:

CREATE TABLE personas(
   dni VARCHAR(9),
   nombre VARCHAR(10),
   PRIMARY KEY (dni)
);

Hay que tener en cuenta que a la hora de definir claves primarias compuestas (dos ó más columnas), ésta deberá ser definida forzosamente tras la definición de los campos involucrados, siguiendo esta sintaxis

CREATE TABLE personas(
   dni VARCHAR(9),
   nombre VARCHAR(10),
   apellidos VARCHAR(20),
   PRIMARY KEY (nombre, apellidos)
);

Es una propiedad que solo se puede aplicar a claves primarias de tipo entero. Hace que en cada inserción de nuevos registros en esa tabla, la clave primaria se genere automáticamente de forma secuencial. Es realmente útil ya que nos evita tener que dar valor a esa columna, siendo el motor de la base de datos el encargado de que sea siempre distinta.

La forma de definirlo es añadiendo la restricción AUTO_INCREMENT en la definición de la columna que se ha definido como clave primaria:

id INT PRIMARY KEY AUTO_INCREMENT

Como detalle, cualquier inserción fallida en la tabla seguirá incrementando el índice autonumérico aunque no se inserten los datos. Podemos resetearlo:

ALTER TABLE mitabla AUTO_INCREMENT = 1;

Si la tabla no está vacía, debe ser mayor que el último valor de esa columna.

Una clave ajena está formada por una o varias columnas cuya finalidad es almacenar valores que existen en la columna/s clave primaria de otra tabla (o la misma) a la que hace referencia. De este modo los sistemas relacionales aseguran que las relaciones entre tablas tienen significado.

Al definir una columna como clave ajena FOREIGN KEY el motor de la base de datos nos obliga a que todos los valores que contenga esa columna existen siempre en la columna de la clave primaria de la tabla con la que se relaciona. Este concepto se conoce como Integridad Referencial.

Las claves ajenas se deben definir después de la definición de los campos de la tabla:

CREATE TABLE asignaturas(
   id INT PRIMARY KEY AUTO_INCREMENT,
   nombre VARCHAR(20),
   departamento VARCHAR(20),
   id_curso INT,
 
   FOREIGN KEY (id_curso) REFERENCES cursos (id)
 
   -- También se puede crear indicandole un nombre concreto a la restricción:
   CONSTRAINT 'fk_cursos' FOREIGN KEY (id_curso) REFERENCES cursos (id)
);

El ejemplo anterior haría referencia tabla cursos:

CREATE TABLE cursos(
   id INT PRIMARY KEY AUTO_INCREMENT,
   horario VARCHAR(20),
   . . .
);

Si una tabla tiene diferentes claves ajenas, se definen del mismo modo:

   id_curso INT,
   id_profesor INT,
 
   FOREIGN KEY (id_curso) REFERENCES cursos (id),
   FOREIGN KEY (id_profesor) REFERENCES profesores (id)

Si la clave ajena hace referencia a una clave primaria compuesta:

FOREIGN KEY (id_curso, id_aula) REFERENCES cursos(id_curso, id_aula)

Las definiciónes de la columna y de la restricción de clave ajena se hacen de forma independiente, pero debemos tener en cuenta que para crear la restricción de clave ajena sobre una columna, su tipo de datos debe ser exactamente igual al de la clave primaria a la que hace referencia.

Habrá que tener en cuenta que mientras que un campo definido como clave ajena haga referencia a un campo definido como clave primaria, la fila de la segunda tabla no podrá ser eliminada hasta que no lo haga la fila que le hace referencia (integridad referencial). Para evitar estos problemas (aunque no siempre es un problema) es posible definir la restricción de clave ajena añadiendo la cláusula ON DELETE o bien ON UPDATE para el caso de una actualización. De esa manera, cuando se vaya a eliminar o actualizar una fila a cuya clave primaria se haga referencia, podremos indicar a MySQL que operación queremos realizar con las filas que le hacen referencia:

• RESTRICT: Se rechaza la operación de eliminación/actualización
• CASCADE: Realiza la operación y se elimina o actualiza en cascada en las filas que hacen referencia
• SET NULL: Realiza la operación y fija a NULL el valor en las filas que hacen referencia
• NO ACTION: Se rechaza la operación de eliminación/actualización, como ocurre con la opción RESTRICT

Si no especifico ningun tipo de acción, se tomará NO ACTION por defecto para ambas operaciones (UPDATE y DELETE); se restringe el borrado o actualización de valores de claves primarias si tiene claves ajenas referenciando.

-- No podré eliminar cursos si hay asignaturas referenciandolos:
FOREIGN KEY (id_curso) REFERENCES cursos (id) ON DELETE RESTRICT
 
-- Si modifico el valor de la clave primaria de un curso, se actualiza la clave ajena
-- Si elimino un curso, se pondrá a NULL el valor de la clave ajena de las asignaturas
FOREIGN KEY (id_curso) REFERENCES cursos (id) ON UPDATE CASCADE ON DELETE SET NULL

Para definir claves ajenas en MySQL habrá que tener en cuenta algunas consideraciones:

• Una columna clave ajena nunca podrá ser AUTO_INCREMENT, ya que rompería el propósito de la relación.
• La columna/s clave ajena debe ser del mismo tipo de datos que la columna clave primaria a las que se referencia.
• La columna deberá ser un índice. A partir de Mysql 8 y versiones semejantes de MariaDB, toda clave ajena define un índice automáticamente.
• Si la columna se define como obligatoria (NOT NULL) no podrá contener la claúsula (SET NULL) para los casos de borrado (ON DELETE) o actualización (ON UPDATE).
• Toda restricción de clave ajena tiene un nombre. Para ver el nombre de las restricciones de una tabla: SHOW CREATE TABLE <nombre_tabla>

Esta restricción obliga a que se le tenga que dar valor obligatoriamente a una columna. Por tanto, no podrá tener el valor NULL. Se utiliza la palabra reservada NOT NULL.

    apellidos VARCHAR(250) NOT NULL

Se puede definir el valor que una columna tomará por defecto, es decir, si al introducir una fila no se especifica valor para dicha columna. Se utiliza la palabra reservada DEFAULT.

fecha  TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
nombre VARCHAR(250) DEFAULT 'Sin nombre'

De forma más genérica, podemos forzar a que los valores de determinados campos de la tabla cumplan una ciertas condiciones.

Lo habitual es definir una columna como de tipo enumeración (ENUM en MySQL) si queremos indicar que solamente una serie de valores (definidos) son válidos:

curso   ENUM ('0', '1', '2'),
horario ENUM ('mañana', 'tarde', 'noche'),

La restricción UNIQUE evita valores repetidos en una misma columna. Al contrario que ocurre con la restricción PRIMARY KEY, UNIQUE sí admite el valor NULL. Con respecto a esta última consideración, conviene saber que si una columna se define como UNIQUE, sólo una de sus filas podrá contener el valor NULL. Del mismo modo que la clave primaria, esta restricción se puede aplicar a una columna o a un conjunto de columnas.

email VARCHAR(100) UNIQUE
 
// o para varias columnas
. . .
email VARCHAR(100),
login VARCHAR(50),
UNIQUE (email, login)

Los índices se utilizan para obtener datos de las tablas de una forma más rápida. En definitiva, lo que el SGBD hace es asociar el valor de una columna (sobre la que definimos el índice) con su posición en la tabla. De esa manera será más rápido buscar sobre esa columna puesto que al encontrar el valor, el SGBD conocerá su posición en la tabla.

Se recomienda su uso en aquellas columnas sobre las que se vayan a realizar búsquedas en una tabla. Por ejemplo, si tenemos una tabla donde almacenamos información sobre Libros, nos podría interesar crear un índice en el campo autor, puesto que puede ser muy común buscar qué libros ha escrito un autor determinado. Además, será un valor que contendrá pocos valores repetidos, por lo que maximizará el beneficio de usar un índice.

CREATE TABLE libro(
id INT ...,
titulo ...,
autor VARCHAR(20),
INDEX autor_index (autor)
);
 
-- También puedo añadirlo cuando la tabla ya se ha creado
CREATE INDEX indice_autor ON libro(autor);

Por otra parte, los índices presentan algún inconveniente como puede ser el hecho de que ocupan espacio en la tabla, y dependiendo del caso podría llegar a ocupar más espacio que la propia tabla, por lo que hay que tener cuidado a la hora de escoger una columna como índice. También hay que tener en cuenta que hay que actualizar el índice cada vez que se modifica la columna en la tabla por lo que no resulta conveniente elegir como índices aquellas columnas que creamos que van a escribirse con mucha frecuencia.

Como se vió en el tema anterior, una de las funciones del SGBD es la de proporcionar seguridad en el acceso a los datos a través de mecanismos de control de acceso.

En SQL, y así lo hacen todos los SGBD relacionales, se sigue un modelo Usuario-Privilegio para otorgar acceso a los objetos de la Base de Datos. Existen una serie de privilegios predefinidos y es el administrador del SGBD el encargado de asignar o no los privilegios ²⁾ a los usuarios sobre determinados objetos (tablas, procedimientos, . . .).

Supongamos que somos los administradores de un SGBD MySQL y tenemos que proporcionar acceso a una Base de Datos para una aplicación biblioteca a un desarrollador de mi compañía:

-- Si no hemos creado la base de datos, podemos hacerlo ahora
CREATE DATABASE biblioteca;
 
-- Crea el usuario asignándole contraseña
CREATE USER 'desarrollador'@'localhost' IDENTIFIED BY 'mipassword';
 
-- Asigna todos los permisos al usuario sobre la base de datos
GRANT ALL PRIVILEGES ON biblioteca.* TO 'desarrollador'@'localhost';
 
-- Para asignar todos los permisos sobre todas las bases de datos del servidor
GRANT ALL PRIVILEGES ON *.* TO 'desarrollador'@'localhost';

Así, hemos creado la Base de Datos y el usuario, y hemos concedido todos los privilegios a dicho usuario sobre esa Base de Datos.

Para comprobar los usuarios existentes en el sistema y sus permisos:

SELECT * 
FROM mysql.user;

Permite conceder privilegios sobre un objeto a un usuario de la Base de Datos.

    GRANT <privilegio>
    ON <objeto>
    TO <usuario>
    [WITH GRANT OPTIONS]

Permite eliminar el privilegio sobre un objeto a un usuario.

    REVOKE <privilegio>
    ON <objeto>
    FROM <usuario>

La forma más habitual de trabajo a la hora de lanzar órdenes en SQL sobre un SGBD relacional como MySQL es crear ficheros por lotes de órdenes SQL, lo que se conoce como scripts SQL, donde podemos escribir todas las sentencias SQL que queremos ejecutar una detrás de otra separadas por el carácter ;.

Existe la posibilidad de añadir comentarios al código según la siguiente sintaxis:

    -- Esto es un comentario y MySQL no lo ejecuta
    /* Esto también es un comentario y 
       tampoco se ejecuta */

Por ejemplo, para la creación de una nueva Base de Datos y sus tablas podríamos preparar un script SQL como el siguiente:

CREATE DATABASE IF NOT EXISTS pagina_web;
USE pagina_web;
 
CREATE TABLE IF NOT EXISTS usuarios (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    . . .
    . . .
);
CREATE TABLE IF NOT EXISTS productos (
    . . .
    . . .
);
. . .
. . .

Una vez creado el script podremos lanzar su ejecución sobre MySQL y se ejecutarán todas las sentencias contenidas en él de forma secuencial. Es una forma muy útil de crear scripts para la creación de una Base de Datos y todas sus tablas y restricciones y también para crear scripts de actualización o parcheo de una Base de Datos existentes de forma que se incluyan todas las sentencias SQL que actualicen o arreglen los problemas que actualmente pueda haber (añadir una nueva tabla, eliminar un campo, añadir una nueva restricción, . . .).

Para estos casos a veces resulta útil desactivar las claves ajenas, realizar algunas operaciones sobre tablas que puedan tener relaciones con otras y volver a activarlas. De esa manera es posible realizar ciertas operaciones sin que las reglas de validación de la integridad referencial lancen ningún error.

-- Desactivar claves ajenas
SET FOREIGN_KEY_CHECKS = 0;
. . .
// Realizar algunos cambios en la estructura y datos de la Base de Datos
. . .
-- Activar claves ajenas
SET FOREIGN_KEY_CHECKS = 1;

• Instalación de MySql, Workbench, y exportación de script

• Crear el script a partir del modelo relacional

• Cómo crear un script en SQL

1. Utilizar notación snake_case para todos los identificadores (nombre de la base de datos, nombres de tablas, nombres de columnas, . . .). Y siempre en minúscula
2. No utilizar acentos, el caracter ñ ni otros caracteres extraños (|@#…) para nombres de bases de datos, tablas, columnas o cualquier otro elemento
3. Escribir las palabras reservadas del lenguaje SQL en mayúsculas
4. Todas las tablas tendrán un campo clave primaria cuyo nombre será id (definir como id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT)
5. Las claves ajenas indicarán la tabla a la que hacen referencia (en singular) como parte de su nombre. Por ejemplo: id_usuario si es una clave ajena de una tabla usuarios. Si en una tabla hay dos claves ajenas que apuntan a la misma tabla, añadiremos algo al nombre para distinguirla (id_usuario_emisor e id_usuario_receptor, por ejemplo)
6. Se recomienda que los nombres de las tablas sean en plural (users mejor que user, orders mejor que order)
7. Antes de definir un tipo de dato como numérico, comprobar si realmente voy a operar con él como tal
8. Cuidado con los campos contraseña. Realmente nunca se guarda tal cual sino como un hash utilizando algún algoritmo, por lo que la longitud real es mayor (la longitud de un hash creado con SHA1 es de 40 caracteres y con SHA2 hasta 128)

1. Se quiere diseñar una Base de Datos para almacenar todos los datos de un campeonato de fútbol sala que se organiza este año en la ciudad. Aquellos que quieran participar deberán formar un equipo (nombre, patrocinador, color de la 1ª camiseta, color de la 2ª camiseta, categoría, . . .) e inscribirse en el campeonato. A medida que transcurran los partidos se irán almacenando los resultados de éstos, así como qué equipos lo jugaron, en qué campo se jugó, quién lo arbitró y alguna incidencia que pudiera haber ocurrido (en caso de que no ocurran incidencias no se anotará nada). Además, los participantes deberán rellenar una ficha de inscripción con algunos datos personales (nombre, apellidos, edad, dirección, teléfono, . . .)
   
   
2. Se quiere diseñar una Base de Datos para controlar el acceso a las pistas deportivas de León. Se tendrán en cuenta los siguientes supuestos:
   • Todo aquel que quiera hacer uso de las instalaciones tendrá que registrarse y proporcionar su nombre, apellidos, email, teléfono, dni y fecha de nacimiento
   • Hay varios polideportivos en la ciudad, identificados por nombre, dirección, extensión (en m²)
   • En cada polideportivo hay varias pistas de diferentes deportes. De cada pista guardaremos un código que la identifica, el tipo de pista (tenis, fútbol, pádel, . . .), si está operativa o en mantenimiento, el precio y la última vez que se reservó.
   • Cada vez que un usuario registrado quiera utilizar una pista tendrá que realizar una reserva previa a través de la web que el ayuntamiento ha creado. De cada reserva queremos registrar la fecha en la que se reserva la pista, la fecha en la que se usará y el precio. Hay que tener en cuenta que todos los jugadores que vayan a hacer uso de la pista deberán estar registrados en el sistema y serán vinculados con la reserva, pero la reserva solo la realizará un usuario.
     
     
3. Se desea diseñar una Base de Datos para una sucursal bancaria que contenga información sobre los clientes (nombre, apellidos, dni, fecha de nacimiento), las cuentas (tipo de cuenta, numero, interés, balance), las sucursales (código, dirección [calle, número, piso], localidad, provincia) y las transacciones producidas entre sus cuentas (transferencias de dinero de una cuenta a otra). Construir el modelo E/R teniendo en cuenta las siguientes restricciones:
   • Una transacción viene determinada por su número de transacción, la fecha y la cantidad, e intervienen siempre dos cuentas.
   • Un cliente puede tener muchas cuentas.
   • Una cuenta puede tener muchos clientes.
   • Una cuenta sólo puede estar en una sucursal.
   • Dado que una cuenta puede pertenecer a varios usuarios, necesitamos saber también el usuario ordenante de la transacción.
     
     
4. Diseña el modelo E/R para una biblioteca con las siguientes restricciones:
   • Se quiere almacenar información sobre los libros de los que se dispone: titulo, editorial, número de páginas y un resumen breve)
   • Se almacenará información sobre los autores: nombre, apellidos, fecha de nacimiento, fecha de fallecimiento, nacionalidad y un breve resumen de su vida
   • De todos aquellos que se hagan socios de esta biblioteca se les hará una ficha con los siguientes datos: nombre, apellidos, fecha de nacimiento, dirección, teléfono y e-mail
   • Por último, se quiere almacenar todos los libros que cogen prestados los socios para saber quién tiene un determinado libro en cada momento y para conocer que lectores han leído (tenido) alguna vez un libro determinado. Será interesante saber si un libro está actualmente en prestamo, desde cuándo se ha prestado, cuándo se devuelve, y el tiempo permitido de prestamo.
     
     
5. Diseña el modelo E/R para la Base de Datos de una aplicación para la gestión de la secretaría de un colegio:
   • Cuando un alumno venga a matricularse se le hará rellenar una ficha con sus datos que luego se introducirá a la aplicación: DNI, número de expediente, nombre, apellidos, domicilio, teléfono y e-mail
   • Se tendrá ya almacenada información sobre todos los cursos que se imparten en el centro: un código, el nombre, aula donde se imparte y el horario
   • Se almacenarán todas las asignaturas de todos los cursos con el fin de conocer en que asignaturas se matricula cada alumno. De cada una guardaremos el nombre, el profesor que la imparte y el número de horas a la semana. Hay que tener en cuenta que todos los alumnos se matricularán al menos de una asignatura en algún curso
   • Los profesores también están dentro de la Base de Datos, con la siguiente información: nombre, apellidos, domicilio y e-mail. Un profesor podrá impartir como máximo 6 asignaturas y deberá impartir al menos una. También habrá que almacenar el curso del que un profesor es tutor, teniendo en cuenta que puede que no sea tutor de ningún curso
   • Hay que tener en cuenta que es importante almacenar las notas que cada alumno tiene en cada asignatura a lo largo del curso en las distintas evaluaciones (3), así como las observaciones que los profesores podrán anotar. Además, al principio de curso los alumnos escogerán con que compañero quieren realizar las prácticas o trabajos (será siempre con el mismo), información que almacenaremos también en la Base de Datos
     
     
6. La Base de Datos COMPAÑÍA se ocupa de los empleados, departamentos y proyectos de una empresa de desarrollo de software, de acuerdo con los siguientes requisitos:
   • La compañía está organizada en departamentos. Cada departamento tiene un nombre único, un número único, una localización y un empleado que lo dirige. Se debe almacenar la fecha en que dicho empleado comenzó a dirigir ese departamento.
   • Cada departamento controla un cierto número de proyectos, cada uno de los cuales tiene un nombre y un número únicos.
   • Se almacena el nombre, número de la Seguridad Social, dirección, salario, sexo y fecha de nacimiento de cada empleado. Todo empleado está asignado a un departamento, pero puede trabajar en varios proyectos que no tienen por que ser del mismo departamento. Nos interesa saber el número de horas que un empleado dedica a cada uno de los proyectos asignados.
   • También se quiere guardar la relación de las cargas familiares de cada empleado para administrar el seguro médico. Almacenaremos el nombre, sexo y fecha de nacimiento de cada una de las cargas familiares y su parentesco con el empleado
     
     
7. Se quiere diseñar una Base de Datos para uno de los hoteles del complejo Gran Scala:
   • En este hotel, los clientes se registran por Internet al solicitar su reserva, que podrá incluir una o varias habitaciones. En cualquier caso la fecha de entrada y salida de todas las habitaciones de una misma reserva será la misma. Además, debido a la exclusividad del hotel, cada habitación dispone de personal propio (uno o varios empleados, dependiendo del tipo de habitación). De los clientes almacenaremos nombre, apellidos, teléfono, email y fecha de nacimiento. Hay que tener en cuenta que nos interesa poder consultar un histórico de todas las reservas que un cliente vaya realizando a lo largo del tiempo, pero en un momento dado un cliente sólo puede tener una reserva vigente.
   • Es importante almacenar tanto la fecha de entrada y salida de cada una de las reservas, asi como las habitaciones que la componen. De cada habitación almacenamos el tipo, precio, extensión y el número de clientes que hasta el momento la han utilizado. Cada habitación tiene asignado un número determinado de empleados, de los que guardamos nombre, apellidos, horario y cargo que desempeñan. Hay que tener en cuenta que cada empleado sólo puede tener asignada una habitación donde trabajar.
   • A la hora de realizar el cobro de los servicios utilizados, será importante almacenar cuando se solicita un uso de los empleados de alguna de sus habitaciones, puesto que tiene un coste por tarea desempeñada. Habrá que anotar una descripción del servicio la fecha y hora en la que se hace uso de sus servicios y el precio del mismo. Asi, todos esos cargos se añadirán al precio de la factura de la propia reserva.
   • Por último, hay que tener en cuenta que las habitaciones tienen un precio pero éstas pueden ser reservadas a precios más bajos dependiendo de la oferta en vigor. Estas ofertas deberán quedar almacenadas y se aplican al precio total del coste de todas las habitaciones de la reserva completa. Por lo tanto el precio total de la reserva dependerá también de la oferta.
     
     
8. Una empresa desea crear un sitio Web de comercio electrónico al que se podrán conectar clientes para realizar sus compras. Se tiene que realizar el diseño de la Base de Datos que soporte la operativa de este sitio Web.
   • Cuando un usuario intenta entrar en este sitio, se le pedirá un login y una contraseña. El sistema comprobará si el usuario tiene cuenta y en caso negativo se le pedirán los siguientes datos de alta: NIF, correo, nombre, dirección, teléfono, login y password. Se comprobará si el usuario ya existía con distinto login para darle un mensaje de error.
   • Una vez el usuario se ha dado de alta o ha entrado con su login y password correctos, puede visitar las distintas secciones de la tienda virtual. Nuestra empresa quiere que quede constancia de las secciones visitadas por los usuario y la fecha en la que la visitaron. Hay que tener en cuenta que un usuario podrá visitar varias secciones. De cada sección se almacenará un código, nombre, descripción y fecha de creación.
   • Los usuarios pueden realizar sus compras utilizando un carrito virtual. Cuando un usuario decide utilizar el carrito, el sistema creará uno almacenando la fecha de creación. El usuario entonces puede poner productos, detallando cuantas unidades desea o bien eliminarlos. Un carrito puede contener varios productos y un producto puede aparecer en carritos de diferentes usuarios. Solo existe un carrito actual.
   • De los productos se almacenará el código de producto, el nombre, la descripción y el precio por unidad. Cuando un usuario decide finalizar su compra, el sistema le pedirá entonces los datos bancarios (si es la primera vez que paga) y dará el carrito por finalizado. El usuario puede dejar un carrito lleno y no completar la compra en esa sesión, para completarla otro día. El usuario debe poder comprobar cuál es el coste total de un carrito antes de pagarlo. Además podrá comprobar el precio total de todos sus carritos anteriores ya pagados y su contenido.
   • En este sitio Web los productos están organizados en las diferentes secciones teniendo en cuenta que un producto puede aparecer en varias secciones y una sección puede tener varios productos
     
     
9. Se quiere crear una Base de Datos para una inmobiliaria que funciona de la siguiente manera:
   • La inmobiliaria se dedica a la venta o alquiler de inmuebles: pisos, chalets, locales, plazas de garage, . . .
   • Los bienes inmuebles se identifican por un código numérico y su información es la dirección, población, tipo de inmueble, código postal, precio de venta, precio de alquiler, fecha del último movimiento y observaciones. Habrá que tener en cuenta que un mismo inmueble puede estar sólo en alquiler, en venta, o ambas. Además, hay que saber que los precios son aproximados, puesto que el precio por el que realmente se alquile o venta puede ser diferente.
   • Hay varios agentes, que se identifican por su DNI, de los que guardaremos su nombre, dirección, población, teléfono, horario (mañana/tarde), salario base y observaciones.
   • Se almacena información de cada uno de los clientes cuando éstos realizan alguna operación con la oficina, dni, dirección, población, teléfono y email.
   • Todos los movimientos, sean de venta o alquiler, se registran con toda la información necesaria, que habrá de determinarse.
   • Por último, habrá que tener en cuenta que a final de cada mes, los agentes recibirán una comisión, en función de las ventas que hayan realizado dependiendo del tipo de inmueble. Habrá que hacer las modificaciones necesarias en la Base de Datos para poder calcular y almacenar dicha información
     
     
10. Un sitio de juegos online por Internet desea contar con una base de datos para gestionar los usuarios, juegos y partidas que se desarrollan en su plataforma. El funcionamiento del sitio es el siguiente:
    • Cuando un usuario entra en este sitio, se le pide el nombre de usuario y una contraseña. El sistema comprobará si el usuario tiene cuenta y en caso negativo se le pedirán los siguientes datos de alta antes de darle acceso: nombre, email, nick, login y contraseña. Se comprobará si ya existía algún usuario con el mismo nick y login para darle un mensaje de error en ese caso.
    • Una vez el usuario ha iniciado una sesión, puede visitar los distintos salones donde se están disputando las partidas. No se desea que quede constancia de dichos salones en la base de datos. Si un usuario quiere entrar en una partida o crear una nueva tiene que crear un avatar, que será su representación en el mundo virtual. Cada usuario podrá tener diferentes avatares pero cada avatar sólo puede pertenecer a un usuario. De los avatares queremos almacenar el aspecto y el nivel. Éstos se identificarán por el nick del propietario.
    • Hay que tener en cuenta que un avatar solo sirve para un tipo de juego, mientras que en un juego puede haber registrado varios avatares de diferentes usuarios. De los tipos de juego se almacenarán un código, el nombre, una descripción y un texto con las reglas del mismo.
    • Los usuarios podrán crear partidas de ese juego para que otros se unan a la partida, o bien podrán unirse a partidas existentes, siempre utilizando el avatar correspondiente. De cada partida queremos almacenar el código, nombre, una contraseña (opcional), fecha y hora de creación, el estado (en curso o finalizada) y también es necesario conocer el avatar del usuario que la creó. Además, hay que tener en cuenta que una partida sólo puede ser para un tipo de juego, aunque un juego puede tener varias partidas.
    • Las partidas se podrán dejar a medias para continuarlas otro día. Cuando un usuario la crea, puede dar una contraseña de entrada para limitar el acceso. Aquellos usuarios que se unan a las partidas con contraseña quedarán registrados de manera que si quieren abandonarla y unirse más tarde no tengan que volver a introducir dicha contraseña. Nunca se permitirá a los usuarios volver a conectarse a partidas que han finalizado
    • Por último, para obtener las puntuaciones finales, se desean registrar los enfrentamientos que se producen en cada partida entre los diferentes avatares de los usuarios, y el resultado de los mismos
      
      
11. La empresa de comida rápida Burger King, con servicio a domicilio, desea crear una base de datos para gestionar todo su negocio. Actualmente sólo vende hamburguesas, ensaladas y bebidas, aunque también quiere registrar todas las ventas realizadas, así como la actividad de sus empleados.
    • Burger King tiene varios centros de comida rápida distribuidos por toda la comunidad de Aragón y atenderá peticiones en todas las poblaciones. De cada centro se quiere almacenar un código, nombre, dirección, población y un teléfono.
    • Aunque todos los centros pertenecen a Burger King, la empresa da libertad a cada uno para que oferten sus propias hamburguesas y ensaladas. De dichas ofertas se almacena el número, nombre, descripción y precio. Hay que tener en cuenta que una hamburguesa puede ser ofertada por más de un centro.
    • De los clientes a domicilio guardaremos su número, nombre, dirección y teléfono. Además, en cada pedido almacenaremos los productos que se han adquirido así como la fecha del mismo. También es importante, para hacer descuentos, saber la cantidad de pedidos totales por cliente.
    • Respecto a los empleados, hay que almacenar su DNI, nombre, dirección, teléfono y población. La empresa asignará empleados a los diferentes centros según las necesidades de cada uno. Un empleado sólo estará asignado un centro en un momento dado pero puede ser reasignado a otro distinto. Por ello, interesa conocer los centros en los que un empleado determinado ha trabajado y en qué fechas empezó y terminó en cada caso.
    • Además, para analizar sus ventas, se almacena información de todas las poblaciones en las que se ha abierto algún centro: nombre, provincia y número de habitantes. Hay que tener en cuenta que en una misma población podrá haber más de un centro
      
      
12. El Gobierno de Castilla y León quiere mantener una base de datos de las fiestas celebradas en todos los pueblos de la comunidad para el verano de 2020.
    • En particular se quiere almacenar la información referente a los grupos musicales que actúan en cada pueblo, los encierros que se celebran y las peñas de cada municipio. Toda esta información se utilizará para proporcionar ayudas económicas a los municipios que la soliciten.
    • De cada municipio se almacenará el nombre, el número de habitantes, la superficie de su término municipal, el presupuesto de las últimas fiestas y el número de peñas que tiene. De cada grupo musical se mantener el nombre, el año de formación, el precio por actuación y el número de componentes. Hay que tener en cuenta que un grupo puede actuar en varios pueblos en diferentes fiestas y un municipio puede tener la actuación de varios grupos. Por supuesto, un grupo puede repetir actuación en un mismo pueblo. Además, será importante mantener información sobre las fechas en las que ha actuado cada grupo en cada uno de los pueblos.
    • En cuanto a los encierros, se almacenará el pueblo en el que se realizan, la fecha, la ganadería y el número de heridos producidos.
    • Las peñas se definen por el nombre, el número de socios y el año en que se crearon. Hay que tener en cuenta que una peña solo puede pertenecer a un pueblo y un pueblo puede tener varias peñas. También interesa conocer qué peñas colaboran con la celebración de los encierros. Además, es importante conocer el número de heridos de cada peña que se producen en un encierro determinado.
    • Por último, habrá que tener en cuenta que para cada actuación de un grupo musical, es una peña de dicho municipio quién elige dicho grupo
      
      
13. El Ayuntamiento de León quiere implantar un sistema de control para las estaciones de bicicletas públicas que ha instalado recientemente. El objetivo es conocer donde están las bicicletas en cada momento y saber qué usuarios las han usado y cuando lo han hecho. Además, cada bicicleta tiene instalado un módulo de autodiagnóstico que detecta las averías para que éstas quede registradas y puedan ser reparadas por los técnicos del Ayuntamiento.
    • De cada bicicleta se almacenará su matrícula, cuantas marchas tiene, el color, si tiene o no cesta, la velocidad máxima y si está o no averiada.
    • Por otra parte, a los usuarios se les obliga a darse de alta a través de una web, aportando su DNI, nombre, apellidos, email, teléfono móvil y un número de cuenta.
    • Hay que tener en cuenta que habrá muchas estaciones repartidas por la ciudad y que los usuarios deben poder consultar información relativa a éstas desde una página web: el número asignado a la estación, dirección donde se encuentra, si está o no operativa, su capacidad máxima y el horario de apertura de dicha estación.
    • Cuando el módulo de diagnóstico detecta una avería, la envía al servidor central aportando un número de avería, una descripción breve de la misma y la fecha en la que tuvo lugar. Por supuesto, habrá que saber qué bicicleta está averiada. De esa manera los mecánicos del servicio podrán consultar el fichero de averías y repararlas. También es importante que quede guardado qué mecánico arregló una avería determinada (de ellos se guarda su código de empleado, nombre, apellidos y teléfono móvil) teniendo en cuenta que cada uno tiene asignadas dos estaciones de las que hacerse cargo, aunque las bicicletas pueden estar en cualquier estación.
    • Además, es necesario conocer en cada momento qué usuario tiene una bicicleta determinada asi como las bicicletas que han sido utilizadas por los usuarios y durante cuánto tiempo las usaron. En el caso de las averías también tienen que quedar registradas cuál fue el último usuario que usó la bicicleta averiada por si hubiera que multarlo
      
      
14. Como desarrollador de la empresa EventoBook, debes diseñar la Base de Datos de la aplicación estrella de la empresa. Se trata de una red social para que la gente se relacione con sus amigos y con eventos que tienen lugar en su ciudad de forma que puedan consultarlos, apuntarse y ver si sus amigos también van a acudir. Además, podrán opinar sobre ellos y compartir esas opiniones con el resto de los usuarios de esta red.
    • Cada nuevo usuario debe registrarse proporcionando su nombre, apellidos, email, una contraseña y su fecha de nacimiento. Por otro lado, cualquiera puede registrar eventos que podrán ser de dos tipos: deportivos y culturales, indicando en ambos casos nombre, descripción del evento, una URL, la dirección (compuesta de calle, código postal, población y provincia), la fecha de inicio, la fecha de finalización y el precio, si tienen. Además, dependiendo del tipo de evento se almacenarán algunos otros datos. De los eventos deportivos se almacena el deporte y los equipos que se enfrentan (cantidad variable) además de todos los datos anteriores. Si el evento es cultural se añaden el motivo (que tomará los valores música, pintura ó cine) y el nombre del artista que ha motivado dicho evento. En cualquier caso debe quedar constancia del usuario que creó el evento.
    • Los usuarios podrán indicar qué usuarios son conocidos suyos y el motivo (que tomará los valores trabajo, familia o amistad) y asi quedará almacenado. Además, los usuarios se podrán apuntar a los eventos de forma que será necesario conocer qué usuarios van a cada uno de ellos. Tras la celebración del evento el usuario que haya ido podrá dejar (o no) una opinión sobre el mismo donde podrá escribir una opinión libre sobre ese evento, una puntuación (de 1 a 10) y si lo recomendaría o no a sus amigos. Hay que tener en cuenta que las opiniones deben ser anónimas, de forma que no sea posible saber que usuario la ha escrito
      
      
15. Han abierto un centro comercial en Ponferrada y para su gestión se debe diseñar una Base de Datos para almacenar toda la información sobre el mismo. Es el centro comercial más grande del mundo por lo que tendrá varios tipos de establecimientos: tiendas de ropa, restaurantes y museos. De todos ellos se almacenará el nombre, número de local, capacidad máxima y su horario. Además, de las tiendas de ropa se almacenará el tipo de ropa que venden y si están de rebajas o no. Para los restaurantes se almacenará el nombre del chef y el estilo de comida que preparan; y para los museos se almacenará una lista con los pintores que exponen sus obras en cada momento.
    • Además, se almacenará la información de los clientes que asisten a estos establecimientos (si éstos lo autorizan) almacenando el nombre, apellidos, dirección, teléfono y email. También se almacenará en qué establecimientos han hecho alguna compra y la cantidad a la que ésta asciende. Se ofrecerán descuentos a aquellos clientes que vengan recomendados por otros, por lo que es interesante almacenar esta relación.
    • Puesto que también se celebran eventos de todo tipo en el centro comercial (almacenando el nombre del evento, fecha y tipo de evento, que podrá ser deportivo, festivo o cultural), se venderán entradas que los clientes podrán adquirir para asistir a los mismos. De las entradas se guardará la numeración, la fecha de compra, la butaca asignada y el tipo de entrada que podrá ser VIP, PREMIUM o NORMAL. Es importante que quede registrado a qué cliente pertenece cada entrada, ya que son personales
      
      
16. La organización de la vuelta ciclista a España 2021 ha decidido crear una web para seguir el transcurso del evento en directo. Para ello debe diseñar una Base de Datos. Se desea almacenar información de todos los ciclistas (dorsal, nombre, apellidos, fecha de nacimiento, nacionalidad y el equipo al que pertenecen). De cada equipo de la vuelta almacenaremos el nombre, la marca a la que representa, la nacionalidad y el presupuesto. Además, cada equipo tendrá un líder, que será un corredor del equipo.
    • También se quiere almacenar todas las etapas de esta edición, almacenando el número de la etapa, el origen, el destino, la distancia en kms y el tipo de etapa (montaña o contrarreloj). Si la etapa es de montaña además se almacenarán los nombres de los puertos de montaña que atraviesa y la pendiente media de toda la etapa. Si fuera una contrarreloj se añade en que kilómetros intermedios se tomarán tiempos. Una vez terminada cada etapa se almacenará qué corredores han quedado en los tres primeros puestos y el tiempo que les ha costado.
    • Por último, en esta edición de la vuelta se quiere tener registrada cada bicicleta que use cualquier corredor, teniendo en cuenta que un corredor puede usar más de una bicicleta pero que una bicicleta determinada sólo puede haberla usado un corredor. De cada bicicleta almacenaremos la marca, el modelo y el estado (en uso, rota o en reparación) y también es necesario que queden registradas las fechas entre las que el corredor la utilizó en carrera.
      
      
17. Se desea diseñar una base de datos para un centro comercial organizado por departamentos que contenga información sobre los clientes que han comprado algo, los trabajadores, el género que se oferta y las ventas realizadas. Construir el modelo ER y Relacional si:
    • De los clientes queremos almacenar un perfil en el que se incluyen sus datos principales y diferentes intereses.
    • Existen 3 tipos de trabajadores de los que almacenamos nombre y apellidos, dni y fecha de nacimiento. De los gerentes almacenamos también fecha de alta, de los jefes almacenamos su teléfono y sus titulaciones, que pueden ser varias y de los vendedores su especialidad y bonificación por volumen de ventas (10%).
    • Cada departamento se identifica por nombre y descripción y está gestionado por un gerente. Además tiene diferentes categorías.
    • De cada producto queremos conocer su nombre, codigo de barras y su precio, y solo se localiza en un departamento concreto.
    • Los jefes y vendedores pertenecen a un departamento concreto, el cual queremos conocer.
    • Cada gerente tiene a su cargo varios jefes. Éstos, a su vez, controlan a varios vendedores.
    • Una venta la realiza un vendedor a un cliente. Debe quedar constancia del artículo vendido, la cantidad, la fecha y el precio total. Cada venta es de un único producto.
      
      
18. Se pretende llevar a cabo un control sobre la energía eléctrica que se produce y consume en un determinado país. Se parte de la siguiente situación:
    • Existen productores básicos de electricidad que se identifican por un nombre, de los cuales interesa su producción media, producción máxima y fecha de entrada en funcionamiento. Estos productores básicos producen una de las siguientes energías: Hidroeléctrica, Solar, Nuclear, y Térmica.
    • De una central hidroeléctrica, interesa saber la ocupación de su presa, capacidad máxima y número de turbinas. De una central solar interesa saber la superficie total de paneles solares, la media anual de horas de sol y el tipo de instalación (Fotovoltaica o termodinámica). De una central nuclear interesa saber el número de reactores que posee, el volumen de plutonio consumido y el de residuos nucleares que produce. De una central térmica interesa saber el número de hornos que posee, el volumen de carbón consumido y el volumen de emisión de gases.
    • Por motivos de seguridad nacional, interesa conocer el plutonio que se provee a una central nuclear. Este control se refiere a la cantidad de plutonio que compra a cada uno de sus posibles suministradores (nombre y país) y que transporta un determinado transportista (nombre y matrícula). Ha de tenerse en cuenta que el mismo suministrador puede vender plutonio a distintas centrales nucleares y que cada pedido, (un único pedido por compra), puede realizarlo un transportista diferente.
    • Cada día, los productores entregan la totalidad energía producida a una o varias estaciones electricas primarias, las cuales pueden recibir diariamente una cantidad distinta de energía de cada uno de esos productores. Las estaciones primarias se identifican por su nombre, y tienen un número de transformadores de baja a alta tensión, una capacidad energética y disponen de una o varias redes cableadas de distribución para suministrar su energía.
    • Una red de distribución se identifica por un número de red, tiene una fecha de inauguración y abarca una superficie, y sólo puede tener una estación primaria como cabecera. La propiedad de una red puede ser compartida por varias compañías eléctricas. A cada compañía eléctrica se la identifica por su nombre, su CIF y también deseamos conocer su sede, capital, y su presidente.
    • La energía sobrante en una de las redes puede enviarse a otra red. Se registra el volumen total de energía intercambiada entre dos redes. Una red está compuesta por una seria de líneas, cada línea se identifica por un número secuencial dentro del número de red y tiene una determinada longitud, y fecha de mantenimiento. La menor de las líneas posibles abastecerá al menos a dos subestaciones.
    • Una subestación (capacidad_maxima y dirección) es abastecida sólo por una línea y distribuye a una o varias zonas de servicio. A estos efectos, las provincias (código, nombre y población), se encuentran divididas en tales zonas de servicio, aunque no puede haber zonas de servicio que pertenezcan a más de una provincia. Cada zona de servicio puede ser atendida por más de una subestación. En cada zona de servicio se desea registrar el consumo medio y el número de consumidores finales de cada una de las siguientes categorías: particulares, empresas e instituciones.
      
      
19. Se quiere modelar una bbdd de una clínica odontológica. La clínica está compuesta por varios locales de atención, identificados por su nombre, de los cuales se conoce además su dirección, dada por la ciudad donde se ubica, la calle y el número. En cada local existen varias salas de consulta que se identifican por un código dentro del local, una superficie y contiene cierto equipamiento. El equipamiento se identifica globalmente mediante un número de serie, se conoce el tipo(torno, laser, etc.) e interesa mantener registro de la última fecha en que se le realizó mantenimiento.
    • La clínica posee dos planes diferentes de afiliación: individual y grupal. De los afiliados se conoce el dni, el nombre y uno ó más teléfonos. Para los afiliados grupales interesa saber el nombre del convenio de afiliación y el porcentaje de rebaja que se debe aplicar a la cuota mensual. De los afiliados individuales, la fecha de afiliación, la cantidad de consultas realizadas y si viene recomendado por otro afiliado, conocer también el afiliado (del tipo que sea) que lo recomendó.
    • En la clínica se realizan tratamientos, los cuales se identifican por su nombre, duración y tienen un coste asociado. Los odontólogos que trabajan en la clínica se identifican por su nombre, apellidos y dni. De ellos se conoce su especialidad principal dentro de la odontología, las diferentes titulaciones y los diferentes tratamientos que pueden realizar. Los odontólogos trabajan en diferentes locales y cada odontólogo puede tener distintos horarios de atención en cada local. De cada horario de atención se conoce el día de la semana, la hora de comienzo y la hora de finalización. (ej. lunes de 16:00 a 18:30).
    • Los clientes afiliados se someten a intervenciones para algún tratamiento. Debo registrar el Doctor que le trata, la sala en la que se realiza, la fecha de comienzo del tratamiento. Los tratamientos de un paciente pueden llevar varias sesiones o intervenciones, por lo que es interesante conocer cuando se realizan, y cuando acaban. Aunque se realice en diferentes sesiones siempre será el mismo Doctor el que complete el tratamiento. Queremos poder consultar el historial médico de todos los tratamiento o intervenciones del paciente en nuestra clínica.
      
      
20. El club de ajedrez de la universidad ha sido encargado por la federación internacional para la organización de los proximos campeonatos internacionales. Debido a esto, se debe diseñar la base de datos para realizar la gestión del campeonato, participantes, alojamienros, partidas, etc.
    • En el campeonato participan jugadores y árbitros. De ambos se desea conocer el número de asociado, nombre, dirección y telefono. De los jugadores se desea conocer su nivel de juego (1-10) y una descripción del palmarés. De los árbitros la fecha de nacimiento y campeonatos previos en los que han participado. Los árbitros no pueden participar como jugadores, ni viceversa.
    • Los países envían al campeonato un conjunto de participantes (tanto jugadores como árbitros), aunque no todos los paises envían participantes. Además algunos países pueden estar representados por otro pais. Al inscribir un país indicamos su nombre, el número de clubes existentes en el país, y una imagen de la bandera, para incluirla en la web de las clasificaciones.
    • Cada partida se identifica por un código, fecha y hora, y participan siempre 2 jugadores y un árbitro. Es necesario registrar las partidas que juega cada jugador y el color con el que juega. Un árbitro no podrá arbitrar a jugadores proveninetes de su mismo país. Todo jugador participa al menos en una partida. Cada partida se realiza en una jornada, aunque no haya partidas todas las jornadas.
    • Tanto los jugadores como los árbitros, se alojan en alguno de los hoteles en los que se desarrollan las partidas. Es necesario conocer en qué hotel y en que fechas se ha alojado cada participante, pudiendo alojarse en diferentes hoteles, ya que no es necesario que permanezcan en la ciudad los días que no tienen partidas. De los hoteles almacenaremos la dirección, el nombre, el teléfono y el número de salones para jugar partidas.
    • Cada partida se celebra en alguna de las salas de las que disponen los hoteles. Se desea conocer el número de entradas vendidas de la sala para esa partida. De cada sala queremos conocer la capacidad de asistentes, el piso en el que se encuentra, y la cantidad de medios de que dispone (radio, tv, video, streaming, etc) para facilitar la retransmisión.
    • Por último, en la página web del campeonato se podrá hacer un seguimiento del transcurso de cada partida, por lo que necesitamos registrar todos los movimientos realizados en cada una de ellas. Cada movimiento se identificará por un número ascendente dentro de cada partida (movimiento 1, movimiento 2, etc), la jugada en sí (p.e. alfil negro A2) y un comentario realizado por un experto.

• Práctica 2.1 Diseño de una Base de Datos
• Práctica 2.2 El modelo relacional en MySQL
• Práctica 2.3 Creación de una Base de Datos en lenguaje SQL sobre MySQL

© 2024 Santiago Faci y Fernando Valdeón