3. Diseñando una BD

3.1. ¿De qué va este tema?

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.

3.2. Clases

Está organizado en 8 talleres de unas 3 sesiones de clase. En total unas 24 sesiones de clase más tu trabajo en casa.

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

3.2.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.2. Usando el Modelo ER

1. El Modelo E/R :

   1. Herramienta para diseñar el modelo de datos en entidades y sus relaciones

   2. Historia: Peter Chen (1976)

   3. 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)

   4. 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).

   5. 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

3. Es la herramienta más usada en la fase de diseño conceptual (no es el objetivo final)

Ejercicios ER

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

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.2.3. Usar un Diagrama E/R

1. Hay diferentes diagramas (notaciones visuales) para representar el Modelo ER (misma idea, pero diferentes formas de representar los elementos)

   1. Chen

      

   2. UML

      

   3. Barker

      

   4. IDEF1X

      

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

      

   6. Bachman

   7. Arrow

2. El objetivo final, en esta fase del diseño, es que el diagrama sirva de mapa visual para confirmar los requisitos del cliente

3. Vamos a usar la notación Crow’s Foot (aunque es habitual ver una mezcla de diagramas Chen y Bachman)

   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

   4. Detallamos los atributos especiales (sobre todo PK y FK)

   5. Detallamos la cardinalidad de las relaciones entre entidades

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

   7. … 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)

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

   4. 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. Una BD para Los bocatas de Maryline (ya esbozado en temas anteriores)

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

3. Base de datos hubway.db (ya trabajado en las clases anteriores)

3.2.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

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

2. Hay mucho software (pero mucho, incluido IA) que sirve como ayuda digital para realizar diseños visuales:

   1. Los dos recomendados: Draw.io /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 …

3. Formatos para los diagramas:

   1. Imágen: PNG / JPG

   2. Documento: PDF

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

4. Una opción (muy) interesante es el uso de Mermaid

   1. Es un editor gráfico del tipo WYSIWYM : Generas el diagrama a partir de código (texto).

   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)

   5. Por ejemplo:

   erDiagram
       CUSTOMER }|..|{ DELIVERY-ADDRESS : has
       CUSTOMER ||--o{ ORDER : places
       CUSTOMER ||--o{ INVOICE : "liable for"
       DELIVERY-ADDRESS ||--o{ ORDER : receives
       INVOICE ||--|{ ORDER : covers
       ORDER ||--|{ ORDER-ITEM : includes
       PRODUCT-CATEGORY ||--|{ PRODUCT : contains
       PRODUCT ||--o{ ORDER-ITEM : "ordered in"
   

Uso de herramienta de diagramación ER visual

Si no tienes una de referencia, puedes usar draw.io 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 Draw.io )

   

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

      

   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:

1. ¿Cual es la tupla? ¿Cuántas tuplas tiene la relación?

2. ¿Cual sería un atributo?

3. ¿Cual sería un valor?

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

5. ¿Cual es la cardinalidad?

6. ¿Cual es la tabla?

7. ¿Cual es el encabezado?

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

9. ¿Cual sería el esquema?

10. ¿Porqué la clave primaria es pasaporte?

3.2.6. En la práctica

1. Diseño Conceptual: obtienes un Diagrama ER

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

3. 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

Usamos en las últimas clases diferentes Ejercicios de Diseño de Modelo de Datos

3.2.7. Diseño Físico (con SQL)

1. Es muy dependiente del SGBDR (con pequeñas diferencias salvo funcionalidad avanzada)

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

Usamos en las últimas clases diferentes Ejercicios de Diseño de Modelo de Datos

3.2.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

Usamos en las últimas clases diferentes Ejercicios de Diseño de Modelo de Datos

---