Bases de Datos

1º DAM/DAW/ASIR

User Tools

Site Tools

Trace:
apuntes:diseno

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
apuntes:diseno [22/04/2017 18:50] – [Introducción al lenguaje SQL] Santiago Faciapuntes:diseno [04/03/2021 15:13] (current) – [Ejercicios] Santiago Faci
Line 93: Line 93:
 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. 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.
  
 +==== Comprobaciones sobre el Diagrama Entidad-Relación ====
 +
 +  - Resulta cómodo que las entidades estén escritas en minúscula para hacer todas estas comprobaciones
 +  - Comprobar que nuestro diagrama no se ha convertido en un diagrama de flujo y no describe procesos, sino almacenes de datos
 +  - Comprobar que las Entidades son nombres de cosas y las relaciones son verbos
 +  - Comprobar que ninguna Entidad tiene como atributo algo que existe como Entidad (si ocurre, se deberían relacionar ambas Entidades)
 +  - Comprobar que varias entidades no comparten un mismo atributo estructurado que pueda ser considerado realmente como una Entidad
 +  - Evitar los ciclos (si aparece alguno, que puede ocurrir, comprobar que es necesario)
 +  - 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, . . .)
 +  - 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
 +  - Continuará . . .
 ==== Ejemplos de diseño ==== ==== Ejemplos de diseño ====
 +  * Diseñar un modelo Entidad/Relacion (entidades, atributos y relaciones)
 +
 +{{ youtube>Z6KE3R42N7E }}
 +\\
 +  * Diseñar un modelo Entidad/Relación (ciclos y redundancia]
 +
 +{{ youtube>WqB4lYmzEtw }}
 +\\
 +  * Diseñar un modelo Entidad/Relación (atributos multivaluados, compuestos y derivados)
  
-  * [[https://www.youtube.com/watch?v=Z6KE3R42N7E|Diseñar un modelo Entidad/Relación (entidades, atributos y relaciones)]] +{{ youtube>_AG-fQ0Z5m0 }} 
-  * [[https://www.youtube.com/watch?v=WqB4lYmzEtw|Diseñar un modelo Entidad/Relación (ciclos y redundancia]] +\\
-  * [[https://www.youtube.com/watch?v=_AG-fQ0Z5m0|Diseñar un modelo Entidad/Relación (atributos multivaluados, compuestos y derivados)]]+
  
  
Line 143: Line 162:
 ==== Ejemplos de transformaciones a modelo relacional ==== ==== Ejemplos de transformaciones a modelo relacional ====
  
-  * [[http://www.youtube.com/watch?v=_BzC1HebIYA|Transformar relaciones 1 a 1 al modelo relacional]] +  * Transformar relaciones 1 a 1 al modelo relacional 
-  * [[http://www.youtube.com/watch?v=flxynkCABP0|Transformar relaciones N a M al modelo relacional]] +{{ youtube>_BzC1HebIYA }} 
-  * [[http://www.youtube.com/watch?v=cmyep2ccK8c|Transformar relaciones 1 a N al modelo relacional]] +\\ 
-  * [[http://www.youtube.com/watch?v=HMhjzALG638|Tranformar una herencia al modelo relacional]] +  * Transformar relaciones N a M al modelo relacional 
-  * [[http://www.youtube.com/watch?v=vycSagvQ6X8|Transformar relaciones reflexivas al modelo relacional]] +{{ youtube>flxynkCABP0 }} 
-  * [[http://www.youtube.com/watch?v=vycSagvQ6X8|Transformar atributos al modelo relacional]] +\\ 
-  * [[http://www.youtube.com/watch?v=c91FkQ3O--k|Ejemplo completo de diseño y transformación de modelo Entidad/Relación a modelo relacional]] +  * Transformar relaciones 1 a N al modelo relacional 
-  * [[http://www.youtube.com/watch?v=IxO4q918Gek|Transformar un modelo Entidad/Relación a modelo relacional]] +{{ youtube>cmyep2ccK8c }} 
 +\\ 
 +  * Tranformar una herencia al modelo relacional 
 +{{ youtube>HMhjzALG638 }} 
 +\\ 
 +  * Transformar relaciones reflexivas al modelo relacional 
 +{{ youtube>yWdWKdwslFE }} 
 +\\ 
 +  * Transformar atributos al modelo relacional 
 +{{ youtube>vycSagvQ6X8 }} 
 +\\ 
 +  * Ejemplo completo de diseño y transformación de modelo Entidad/Relación a modelo relacional 
 +{{ youtube>c91FkQ3O--k }} 
 +\\ 
 +  * Transformar un modelo Entidad/Relación a modelo relacional 
 +{{ youtube>IxO4q918Gek }} 
 +\\
 ==== Normalización de modelos relacionales ==== ==== Normalización de modelos relacionales ====
  
Line 225: Line 259:
 {{ 3fn2.png |Aplicando la 3ª FN. Tabla Categorías}} {{ 3fn2.png |Aplicando la 3ª FN. Tabla Categorías}}
  
-===== Bases de Datos relacionales ===== +===== Creación de tablas en lenguaje SQL =====
- +
-==== Introducción al lenguaje SQL ====+
  
 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. 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.
  
-=== Sentencias DDL ===+==== Manipulación de tablas (Sentencias DDL) ====
  
 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. 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.
Line 237: Line 269:
 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: 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:
  
-== Crear un objeto: CREATE ==+=== Crear un objeto: CREATE ===
  
 Es la sentencia utilizada para la creación de un objeto (base de datos, tabla, usuario, vista, procedimiento, . . .) en una Base de Datos. Es la sentencia utilizada para la creación de un objeto (base de datos, tabla, usuario, vista, procedimiento, . . .) en una Base de Datos.
Line 255: Line 287:
 </code> </code>
  
-== Eliminar un objeto: DROP ==+=== Eliminar un objeto: DROP ===
  
 Es la sentencia utilizada para eliminar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de            Datos. Es la sentencia utilizada para eliminar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base de            Datos.
Line 268: Line 300:
 </code> </code>
  
-== Modificar un objeto: ALTER ==+=== Modificar un objeto: ALTER ===
  
 Es la sentencia utilizada para modificar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base            de Datos. Es la sentencia utilizada para modificar objetos (tabla, usuario, vista, procedimiento, . . .) en una Base            de Datos.
Line 281: Line 313:
 </code> </code>
  
-== Conceder privilegios sobre un objeto: GRANT ==+==== Gestión de privilegios sobre objetos ==== 
 + 
 +=== Conceder privilegios sobre un objeto: GRANT ===
  
 Permite conceder privilegios sobre un objeto a un usuario de la Base de Datos. Permite conceder privilegios sobre un objeto a un usuario de la Base de Datos.
Line 291: Line 325:
 </code> </code>
  
-== Revocar privilegios sobre un objeto: REVOKE ==+=== Revocar privilegios sobre un objeto: REVOKE ===
  
 Permite eliminar el privilegio sobre un objeto a un usuario. Permite eliminar el privilegio sobre un objeto a un usuario.
Line 301: Line 335:
 </code> </code>
  
-== Conectar con una Base de Datos ==+=== Conectar con una Base de Datos ===
  
 Permite realizar la conexión con una Base de Datos de MySQL. Permite realizar la conexión con una Base de Datos de MySQL.
Line 308: Line 342:
 </code> </code>
  
-== Comentarios en lenguaje SQL == 
  
-Existe la posibilidad de añadir comentarios al código del lenguaje SQL según la siguiente sintaxis: 
-<code sql> 
-    -- Esto es un comentario y MySQL no lo ejecuta 
-    /* Esto también es un comentario y  
-       tampoco se ejecuta */ 
-</code> 
  
-=== Tipos de datos ===+==== Tipos de datos ====
  
-== Cadenas de caracteres ==+=== Cadenas de caracteres ===
  
 == Tipo CHAR, VARCHAR == == Tipo CHAR, VARCHAR ==
Line 336: Line 363:
   * No se permite su utilización en ciertas cláusulas   * No se permite su utilización en ciertas cláusulas
  
-== Tipos numéricos ==+=== Tipos numéricos ===
  
 Para la representación de tipos de datos numéricos. Los tipos más utilizados son ''BIT'', ''TINYINT'', ''INT'', ''BIGINT'', ''FLOAT'' y ''REAL'', para la representación de números enteros de menor o mayor tamaño, y para números en coma flotante de menor o mayor precisión, respectivamente. Para la representación de tipos de datos numéricos. Los tipos más utilizados son ''BIT'', ''TINYINT'', ''INT'', ''BIGINT'', ''FLOAT'' y ''REAL'', para la representación de números enteros de menor o mayor tamaño, y para números en coma flotante de menor o mayor precisión, respectivamente.
Line 348: Line 375:
 Para el caso de los números de coma flotante conviene tener en cuenta los problemas de precisión ((https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html)) que existen con estos tipos de datos. Para el caso de los números de coma flotante conviene tener en cuenta los problemas de precisión ((https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html)) que existen con estos tipos de datos.
  
-== Tipos para fechas ==+=== Tipos para fechas ===
  
 Los tipos más utilizado para almacenar valores de fechas (''DATE'') o fechas con hora (''DATETIME''). Por defecto            el formato más utilizado es ''DD/MM/YY'' ó ''DD/MM/YYYY''. Los tipos más utilizado para almacenar valores de fechas (''DATE'') o fechas con hora (''DATETIME''). Por defecto            el formato más utilizado es ''DD/MM/YY'' ó ''DD/MM/YYYY''.
Line 354: Line 381:
 También se puede usar el tipo ''TIMESTAMP'' para almacenar una marca de tiempo (fecha y hora). 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. También se puede usar el tipo ''TIMESTAMP'' para almacenar una marca de tiempo (fecha y hora). 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.
  
-== Tipo booleano ==+=== Tipo booleano ===
  
 Permite almacenar valores lógicos Verdadero/Falso o Sí/No. Realmente se define la columna como del tipo ''TINYINT'', que simplemente almacena los valores 0 y 1 para indicar los valores lógicos Verdadero y Falso, respectivamente. Así, podremos utilizar los valores ''TRUE'' ó ''FALSE'' o directamente asignar 1 ó 0 para asignar valor. Permite almacenar valores lógicos Verdadero/Falso o Sí/No. Realmente se define la columna como del tipo ''TINYINT'', que simplemente almacena los valores 0 y 1 para indicar los valores lógicos Verdadero y Falso, respectivamente. Así, podremos utilizar los valores ''TRUE'' ó ''FALSE'' o directamente asignar 1 ó 0 para asignar valor.
Line 404: Line 431:
 A continuación, un par de ejemplos que definen claves ajenas con diferentes cláusulas: A continuación, un par de ejemplos que definen claves ajenas con diferentes cláusulas:
 <code sql> <code sql>
-curso VARCHAR(25) REFERENCES Cursos  +FOREIGN KEY (id_curso) REFERENCES cursos (id) 
-curso VARCHAR(25) REFERENCES Cursos ON DELETE CASCADE +FOREIGN KEY (id_curso) REFERENCES cursos (id) ON DELETE CASCADE
-</code> +
-En el caso de que definamos la restricción de clave ajena al final de la tabla se utiliza la palabra            reservada ''FOREIGN KEY'': +
-<code sql> +
-FOREIGN KEY (curso) REFERENCES Cursos +
-FOREIGN KEY (curso) REFERENCES Cursos ON DELETE CASCADE+
 </code> </code>
 Como ocurre con las claves primarias, si las claves ajenas son compuestas, se definen forzosamente al final de las definiciones de las columnas de la tabla, de la siguiente forma: Como ocurre con las claves primarias, si las claves ajenas son compuestas, se definen forzosamente al final de las definiciones de las columnas de la tabla, de la siguiente forma:
 <code sql> <code sql>
-FOREIGN KEY (curso, aula) REFERENCES Cursos +FOREIGN KEY (id_curso, id_aula) REFERENCES cursos 
-FOREIGN KEY (curso, aula) REFERENCES Cursos ON DELETE CASCADE+FOREIGN KEY (id_curso, id_aula) REFERENCES cursos ON DELETE CASCADE
 </code> </code>
 +
 +En cualquiera de los casos hay que tener en cuenta que habrá que definir primero el campo con el tipo de dato correcto (el mismo que dicho campo en la tabla donde aparece como clave principal) y luego la propia definición de dicho campo como clave ajena.
  
 == Definir claves ajenas en MySQL == == Definir claves ajenas en MySQL ==
Line 451: Line 475:
         . . .         . . .
     );     );
 +</code>
 +
 +Actualmente, a partir de MySQL 8.0 (y versiones equivalentes de MariaDB) es suficiente definir el campo y la restricción de clave ajena para que el gestor haga el resto (definir dicho campo como índice). El caso anterior se podría reducir a:
 +
 +<code sql>
 +id_cliente INT UNSIGNED,
 +FOREIGN KEY (id_cliente) 
 +  REFERENCES Clientes (id) 
 +  ON DELETE SET NULL ON UPDATE SET NULL,
 </code> </code>
  
Line 508: Line 541:
 {{ indice.jpg |Uso de índices}} {{ indice.jpg |Uso de índices}}
  
-=== Usuarios y privilegios ===+==== Usuarios y privilegios ====
  
 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. 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.
Line 517: Line 550:
  
 <code sql> <code sql>
 +-- Si no hemos creado la base de datos, podemos hacerlo ahora
 +CREATE DATABASE biblioteca;
 +-- Crea el usuario asignándole contraseña
 CREATE USER 'desarrollador' IDENTIFIED BY 'micontraseña'; CREATE USER 'desarrollador' IDENTIFIED BY 'micontraseña';
 +-- Asigna todos los privilegios al usuario sobre la base de datos
 GRANT ALL PRIVILEGES ON biblioteca.* TO desarrollador; GRANT ALL PRIVILEGES ON biblioteca.* TO desarrollador;
 </code> </code>
Line 523: Line 560:
 Así, hemos creado la Base de Datos y el usuario, y hemos concedido todos los privilegios a dicho usuario sobre        esa Base de Datos. Así, hemos creado la Base de Datos y el usuario, y hemos concedido todos los privilegios a dicho usuario sobre        esa Base de Datos.
  
-=== Creación de scripts en MySQL ===+==== Creación de scripts en MySQL ====
  
 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 '';''. 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:
 +<code sql>
 +    -- Esto es un comentario y MySQL no lo ejecuta
 +    /* Esto también es un comentario y 
 +       tampoco se ejecuta */
 +</code>
  
 Por ejemplo, para la creación de una nueva Base de Datos y sus tablas podríamos preparar un script SQL como el siguiente: Por ejemplo, para la creación de una nueva Base de Datos y sus tablas podríamos preparar un script SQL como el siguiente:
Line 563: Line 607:
 === Ejemplos de cómo crear un script en MySQL === === Ejemplos de cómo crear un script en MySQL ===
  
-  * [[http://www.youtube.com/watch?v=T317i5DXj5s|Crear el script a partir del modelo relacional]] +  * Crear el script a partir del modelo relacional 
-  * [[http://www.youtube.com/watch?v=4FlqXfi5Hi0|Cómo crear un script en SQL]]+{{ youtube>T317i5DXj5s }} 
 +\\ 
 +  * Cómo crear un script en SQL 
 +{{ youtube>TKM90c-akXw }} 
 +\\ 
 + 
 +==== Comprobaciones sobre el script SQL ==== 
 + 
 +  - Utilizar notación [[https://en.wikipedia.org/wiki/Snake_case|snake_case]] para todos los identificadores (nombre de la base de datos, nombres de tablas, nombres de columnas, . . .). Y siempre en minúscula 
 +  - No utilizar acentos, el caracter ñ ni otros caracteres extraños (|@#...) para nombres de bases de datos, tablas, columnas o cualquier otro elemento 
 +  - Escribir las palabras reservadas del lenguaje SQL en mayúsculas 
 +  - Todas las tablas tendrán un campo clave primaria cuyo nombre será id (definir como ''id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT'') 
 +  - 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) 
 +  - Se recomienda que los nombres de las tablas sean en plural (''users'' mejor que ''user'', ''orders'' mejor que ''order'') 
 +  - Antes de definir un tipo de dato como numérico, comprobar si realmente voy a operar con él como tal 
 +  - 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) 
 +  - Continuará . . . 
  
 ---- ----
Line 570: Line 630:
 ===== Ejercicios ===== ===== Ejercicios =====
  
-  - 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 camiseta 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 suscripción con algunos datos personales (nombre, apellidos, edad, dirección, teléfono, . . .)\\ \\+{{ ejercicio.png}} 
 + 
 +  - 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 suscripción con algunos datos personales (nombre, apellidos, edad, dirección, teléfono, . . .)\\ \\
   - Se quiere diseñar una Base de Datos para controlar el acceso a las pistas deportivas de Zaragoza. Se tendrán en cuenta los siguientes supuestos:   - Se quiere diseñar una Base de Datos para controlar el acceso a las pistas deportivas de Zaragoza. 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     * 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
Line 658: Line 720:
 ---- ----
  
-(c) 2016 Santiago Faci+(c) 2016-2020 Santiago Faci
apuntes/diseno.1492887038.txt.gz · Last modified: 04/01/2019 12:59 (external edit)