3. Diseñando una BD#

En este curso aprenderás a:

  1. Analizar los requisitos que debe cumplir tu almacén de datos

  2. Realizar el diseño lógico de un almacén de datos usando el modelo lógico plano y el modelo relacional (Modelo ERE)

  3. Realizar el diseño físico en SQL a partir de un modelo de datos lógico

Se trata de realizar un esquema básico de una base de datos.

Esfuerzo Necesario

El curso está organizado en 8 sesiones de clase. Cada clase (sesión) implica una dedicación de entre 2 y 4 horas.

La dedicación depende del conocimiento previo, motivación y capacidad de aprendizaje del estudiante para esa sesión en concreto.

3.1. Diseñando una Base de Datos#

  1. Buscamos diseñar una base de datos para …

  2. … obtener el esquema (diseño físico) de esa BD implementado en un SGBD concreto

  3. Repasa estas dos clases ya vistas:

    1. El Esquema de una Base de Datos

    2. Diseñar una base de datos

3.2. Usando el Modelo ER#

  1. El Modelo E/R :

    1. Historia: Peter Chen (1976)

    2. Los elementos:

      1. Entidades (objetos) que …

      2. tienen una serie de características (atributos) con una serie de restricciones

      3. Cada entidad se relaciona con otra (u otras) un mínimo o máximo número de veces (cardinalidad)

    3. Las relaciones:

      1. Las entidades se relacionan entre sí (grado)

      2. Y participan (participación) un mínimo (modality) y máximo número de veces (cardinality).

    4. Algunos conceptos importantes:

      1. Dominio: conjunto de valores de un atributo

      2. Clave Primaria (PK): atributo (uno o varios) que identifican a una entidad de forma única

      3. Clave Ajena (FK): atributo (uno o varios) que dependen de otra entidad (relaciona entidades)

      4. Restricciones: de relación, de atributo, etc

  2. El modelo E/R extendido

    1. Entidades Fuertes y Débiles (por identificación y por existencia)

    2. Atributos en las relaciones

    3. Herencia, Generalización y Especialización

Ejercicios ER

Usando como ejemplo la base de datos hubway.db (desde Ejercicios para usar SQL):

  1. La tabla trips ¿cómo se representaría en el Modelo ER?

  2. id, ¿qué elemento del modelo ER sería?

  3. ¿Cual seria el dominio del atributo start_date?

  4. ¿Cual sería el dominio del atributo zip_code?

  5. ¿Cual sería la clave primaria (PK) de trips?

  6. ¿Y de stations? ¿Porqué sería la PK?

  7. ¿Qué relación hay entre trips y stations?

  8. ¿Cual es la cardinalidad de esa relación?

  9. ¿Donde se colocaría la clave ajena y cual es la FK?

  10. ¿Cómo pintarías, a tu manera, esa base de datos?

3.3. Usar un Diagrama E/R#

  1. Hay diferentes diagramas (notaciones visuales) para representar el Modelo ER

    1. Chen

      https://vertabelo.com/blog/chen-erd-notation/chen-notation-book-and-chapter-schema-3.png

    2. UML

      https://vertabelo.com/blog/uml-notation/uml_book-chapter.png

    3. Barker

      https://vertabelo.com/blog/barkers-erd-notation/barkers-erd-notation--non-transferability-of-a-relationship.png

    4. IDEF1X

      https://vertabelo.com/blog/idef1x-notation/idef1x_book-chapter.png

    5. IE o Crow’s Foot (pie de gallo)

      https://vertabelo.com/blog/crow-s-foot-notation/crows-foot-notation-many-to-many.png

    6. Bachman

    7. Arrow

  2. El objetivo final es que el diagrama sirve de mapa visual para confirmar los requisitos del cliente

  3. Vamos a centrarnos en la notación Crow’s Foot

    1. Entidades en rectángulos y nombre en la parte superior

    2. Con atributos (y sus nombres) debajo y definiendo las claves (con asterisco)

    3. Con relaciones entre entidades con líneas (con nombre tipo verbo sobre la línea) y

    4. la cardinalidad en cada punto de la línea

  4. Creando un Diagrama ER

    1. Identificamos las Entidades

    2. Definimos los atributos

    3. Identificamos las relaciones básicas entre entidades

    1. Detallamos los atributos especiales (PK y FK)

    2. Detallamos la cardinalidad de las relaciones entre entidades

    3. Confirmamos con el cliente si hay algún requisito pendiente

    4. … y seguimos mejorando (difícil realizar un diseño complejo a la primera)

  5. El diagrama es parte del proceso completo (piensa en el objetivo final que es el diseño físico):

    1. Análisis de requisitos: qué quiere el cliente

    2. Lo reflejo en un modelo visual que pueda revisar (mapa visual)

      1. A veces ya uso un modelo lógico (concreto) en el mapa visual (diseño conceptual)

    3. Lo implemento en un SGBD concreto (diseño físico)

Crear diagramas ER usando la notación Crow’s Foot

Comienza utilizando el papel, puedes usar alguno de estos ejemplos:

  1. Pedidos básicos Amazon: Clientes que hacen pedidos con diferentes productos

  2. Dibuja el Modelo ER de la base de datos hubway.db

  3. Una BD para Los bocatas de Maryline

3.4. Herramientas Gráficas de Diseño#

  1. Existe mucho software para diseño visual de modelos ER

    1. Diseño: software gráfico para modelado visual

    2. Herramientas CASE: además del diagrama, pueden generar código (casi) listo para usar

  2. Ayudan a llevar el modelo conceptual (Diagrama ER) al diseño físico (generalmente SQL) saltándose el modelo lógico (generalmente relacional)

  3. Hay mucho (pero mucho) software que sirven como ayuda digital para realizar diseños visuales:

    1. Los dos recomendados: Diagrams.net /draw.io (gratis) o LucidChart (freemium)

    2. Los usados en entorno profesional: Microsoft Visio , Visual Paradigm , etc

    3. Los de andar por casa (pero muy útiles): herramientas grafícas de ofimática (LibreOffice Draw, por ejemplo)

    4. Y bueno, hay más : 6 Visio Alternatives , 8 herramientas …

  4. Formatos para los diagramas:

    1. Imágen: PNG / JPG

    2. Documento: PDF

    3. Propio del software: ideal para colaborar y/o editar.

  5. Una opción interesante es el uso de Mermaid

    1. Generas el diagrama usando código

    2. Puede generar diferentes tipos de diagramas (por ejemplo diagramas E/R )

    3. Cómo es código fuente, puede evolucionar muy rápido (y ser supervisado con sistemas de CV)

    4. Muy útil para ir desarrollando el modelo poco a poco (sin mucho esfuerzo de diagramación)

Uso de herramienta de diagramación ER visual (diagrams.net)

Ideal usar diagrams.net con la aplicación de escritorio (pero escoge la que te sientas más cómodo).

  1. Instala la herramienta en tu entorno

  2. Configura el almacenamiento (local o en línea)

  3. Es muy sencillo de usar pero puedes echarle un vistazo al tutorial ( Getting Started with Diagrams.net )

    https://www.diagrams.net/assets/img/blog/interface-introduction.png

  4. Crea un diagrama básico :

    1. 3 o 4 elementos (objetos) en el lienzo (canvas)

    2. Conectados entre sí (con lineas, modificando su estilo y conector inicial y final)

    3. Añadiendo texto

    4. Revisa además la galería de plantillas y elementos

    5. Guarda tu trabajo y editalo (individual o en equipo)

  5. Usa el modelo IE (notación pie de gallo) para digitalizar el diseño de uno de tus modelos ERD de ejemplo (que tienes en papel)

Uso de herramienta de diagramación ER visual (lucidchart)

Si usas Lucidchart, estos dos tutoriales te enseñan cómo:

  1. Conceptos Básicos del Diagrama ER:

  2. Conceptos más avanzados:

Uso de herramienta de diagramación ER visual (mermaid)

Si quierer probar Mermaid puedes probar con su editor online o hasta usarlo en tu cuenta github

3.5. El Modelo Relacional (Diseño Lógico)#

  1. Entre el diseño conceptual y el físico estaría el diseño lógico

    1. Pero a veces el modelo conceptual ya tiene componentes del diseño lógico

    2. Y el diseño lógico, tiene componentes del diseño físico (por ejemplo en la notación CF)

    3. El diseño físico, en nuestro caso siempre en un archivo SQL (podría estar en un modelo de datos gráfico)

  2. El modelo relacional : (relación = tabla)

    1. Edgar Codd (IBM / 1970). Fuerte base matemática (álgebra y cálculo)

    2. Conceptos:

      https://upload.wikimedia.org/wikipedia/commons/9/9e/Conceptos_del_modelo_relacional.png

    3. base de datos relacional: esquema (estructura) e instancias (tuplas organizadas en relaciones)

    4. Una cosa es el modelo relacional y otra el sistema que lo gestiona (SGBD Relacional)

  3. Los SGBD Relacionales: las 12 reglas de Codd

    1. Fundamental

    2. Información

    3. Acceso garantizado

    4. Tratamiento sistemático de valores nulos

    5. Catálogo dinámico

    6. Permite Sublenguajes de datos (DML, DDL, DCL, DTC)

    7. Actualización de vistas

    8. Inserción, Actualización y borrado

    9. Independencia Física de los datos

    10. Independencia Lógica

    11. Independencia de la Integridad

    12. Independencia de la distribución

    13. No subversión

  4. Hay muchos SGBDR: Postgresql, Sqlite, Mysql/Mariadb, Oracle, Microsoft SQL Server, etc

Entendiendo el Modelo relacional

Puedes leer este material para distinguir los conceptos de la siguiente tabla:

https://bookdown.org/paranedagarcia/database/images/db-relacionterminos.jpg

  1. ¿Cual es la tupla?

  2. ¿Cuántas tuplas tiene la relación?

  3. ¿Cual sería un atributo?

  4. ¿Cual sería un valor?

  5. ¿Cual es el grado de la relación? (no confundir con grado de relación entre entidades en la notación ERD)

  6. Cual es la cardinalidad

  7. ¿Cual es la tabla?

  8. ¿Cual es el encabezado?

  9. ¿Cual es el cuerpo (las tuplas)?

  10. ¿Cual sería el esquema?

3.6. En la práctica#

  1. Diseño Conceptual: obtienes un Diagrama ER

  1. Diseño Lógico: obtienes un esquema lógico (grafo relacional)

    1. Entidad -> Tabla

    2. Atributos -> Campos (ojo a clave primaria)

    3. Relaciones 1:N -> Clave Foránea (en Relación con N)

    4. Relaciones N:N -> Dos Tablas nuevas (e incluyes FK a cada tabla)

  1. Diseño Físico: obtienes una implementación concreta en un SGBD

  1. En la práctica, para centrarnos:

    1. Diseño Conceptual: primera versión con notación Crow’s Foot

    2. Diseño Lógico: segunda versión (más detallada) del modelo con la notación CF

    3. Diseño Físico: pasas a SQL el diagrama CF (que en realidad, ya tiene detalles de diseño físico)

Ejercicios de Diseño Conceptual

Ejercicios de Diseño de Modelo de Datos

3.7. Diseño Físico (con SQL)#

  1. Es muy dependiente del SGBDR. Aunque con pequeñas modificaciones sirve en general.

  2. Una vez que tienes el diseño lógico (grafo relacional o diagrama ERD lógico), el proceso es similar:

    1. Creas la BD sql_create_db (o la eliminas sql_drop_db)

    2. Creas las tablas sql_create_table (o las eliminas sql_drop_table)

      1. nombre de la tabla

      2. nombre de los atributos (campos)

      3. tipos de datos de los atributos (sql_datatypes)

  3. Puedes modificar, una vez creadas:

    1. Tablas: sql_alter o los atributos o las restricciones

    2. Puedes borrar toda la tabla (drop) o sólo el contenido (truncate)

    3. Puedes modificar la estructura

  4. Importante revisar los tipos de datos del SGBDR. En la práctica no hay que complicarse demasiado (salvo para requisitos complejos):

    1. Números

    2. Texto

    3. Fechas

Ejercicios de Diseño Físico

Ejercicios de Diseño de Modelo de Datos

3.8. Las restricciones#

  1. Restricciones: son una herramienta fundamental para garantizar la integridad de los datos

  2. Se pueden diseñar a nivel de atributo (directamente en la definición del atributo) o a nivel de tabla (te permite diseñar restricciones más complejas)

  3. En principio, salvo casos complejos:

    1. definimos las restricciones a nivel de atributo salvo

    2. las claves ajenas (FK)

  4. Definiendo restricciones:

    1. NOT NULL sql_notnull . El valor no puede ser nulo

    2. UNIQUE sql_unique. Todos los valores de ese atributo (en esa columna) tienen que ser diferentes

    3. PRIMARY KEY sql_primarykey. Es la clave primaria (Primary Key) que implica que es único (UNIQUE) y no acepta nulos (NOT NULL)

    4. CHECK sql_check. Permite definir el conjunto de valores (dominio) que puede tener ese atributo

    5. DEFAULT sql_default - Define cual es el valor predeterminado en caso de que no se asigne uno (ideal para evitar valores nulos)

  5. Restricción en relaciones entre tablas. Claves Ajenas (Foreign Key sql_foreignkey)

    1. El valor de ese campo/atributo tiene que ser el de el campo de otra tabla con la que está relacionada

    2. El campo, por lo tanto, es una clave ajena a otra tabla

    3. Mejor definirla de forma explícita a nivel de tabla y darle un nombre

    4. Se define el comportamiento cuando se borra (Acciones sobre la clave ajena)

      1. ¿Qué pasa si se borra la clave ajena? ON DELETE

      2. ¿Qué pasa si se actualiza el valor? ON UPDATE

Ejercicios de Diseño Físico

Ejercicios de Diseño de Modelo de Datos