🔏
Base de Datos
  • Bases de Datos
  • Modelo entidad relación
  • Modelo Relacional
  • Sistema de Gestión de Bases de Datos RelacionalMySQL
  • Funciones y operaciones en MySQL
  • Operaciones para la proyección de información MySQL
  • Relación entre tablas y manejo avanzado de los datos mediante algunas cláusulas SQL avanzadas
  • Ejercicio práctico de la aplicación Media Técnica App
Powered by GitBook
On this page
  • INTRODUCCIÓN
  • Universo del discurso Media Técnica App
  • Modelo Relacional Media Técnica App
  • Explicación de las tablas
  • Script de base de datos
  • Algoritmo de encriptación SHA1
  • Diccionario de datos
  • Generar diccionario de datos con PhpMyAdmin
  • Diccionario de datos Media Técnica App
  • actores
  • asistencias_sesiones
  • clases
  • estado_actores
  • grupos
  • instituciones_educativas
  • integrantes_grupos
  • modulos
  • sesiones_clases
  • tipo_actores
  • tipo_documento
  • Creación de base de datos Media Técnica App en Clever Cloud
  • Quiero saber más …
  • Ejercicios
  • Ejercicio 1
  • Ejercicio 2
  • Ejercicio 3
  • Ejercicio 4
  • Ejercicio 5

Was this helpful?

Ejercicio práctico de la aplicación Media Técnica App

Módulo 8

PreviousRelación entre tablas y manejo avanzado de los datos mediante algunas cláusulas SQL avanzadas

Last updated 4 years ago

Was this helpful?

INTRODUCCIÓN

Hemos llegado al final de los módulos del módulo de base de datos, estas han entregado de manera muy puntual y practica muchos temas que se requieren en el día a día del desarrollo y estructuración de la capa de datos de un software. Por ello, es imprescindible que el lector no pare su aprendizaje en este punto, por el contrario, continúe profundizando en los diferentes temas vistos y aborde otras temáticas que fortalezcan los conceptos teóricos y prácticos en esta área.

Este módulo final tiene como propósito desarrollar un ejemplo práctico de una base de datos para un aplicativo que requiere almacenar los datos de una problemática en particular. El aplicativo que se ha venido estructurando desde los diferentes módulos es Media Técnica App; este aplicativo contiene diferentes módulos para la administración de la información que se maneja al interior de la coordinación del programa de media técnica.

El diseño estructural y practico del ejercicio que se trabajará en este módulo tendrá gran parte de las etapas que se han venido trabajando en todos los módulos de base de datos. El repositorio donde se almacena el ejercicio de este módulo en el siguiente enlace .

Universo del discurso Media Técnica App

El Ministerio de Educación en el Artículo 33 de la Ley 115 de 1994 pide que la educación media técnica tenga como objetivos: la capacitación básica inicial para el trabajo; la preparación para vincularse al sector productivo y a las posibilidades de formación que éste ofrece, y la formación adecuada a los objetivos de educación media académica, que permita al educando el ingreso a la educación superior.

El Politécnico Colombiano Jaime Isaza Cadavid es una institución de carácter educativo, que dentro de sus programas de formación tiene una media técnica en Programación de Sistemas de Información, dicha media técnica esta dentro de la Alianza de Futuro Digital, la cual cuenta con un conglomerado de instituciones educativas del área metropolitana que hacen parte de este proceso y que durante varios años a contado con varios procesos de éxito que han logrado integrase a un proceso propedéutico para completar su proceso en las modalidades de técnica, tecnología e ingeniería.

A través de los años, esta media técnica ha visto la necesidad de contar con un sistema de almacenamiento que le permita guardar información de los diferentes procesos que se manejan al interior del programa. Como primera medida se requiere almacenar la información de las Instituciones Educativas que se encuentran adscritas al programa; de estas es importante almacenar: nombre de la institución, dirección o ubicación, foto de la institución, una descripción del colegio, telefono de la institución, correo institucional de contacto, nombre del docente encargado y pagina web.

Del programa también es relevante, almacenar la información de los docentes de la media técnica, los cuales se encuentran en constante rotación (sea por que renuncian a su labor o porque son removidos del programa). De los docentes es importante saber en todo momento: documento de identificación, nombres, apellidos, título, dirección de residencia, telefono celular, correo y un pdf con la hoja de vida.

También es indispensable para la coordinación almacenar información de los módulos que se dictan a en las instituciones educativas. Los módulos que se imparten son:

  • Construcción de Elementos de Software 1 (CES1)

  • Taller de base de datos (BD)

  • Interpretación de Requisitos (IR)

  • Construcción de Elementos de Software Web 1 (CESW1)

Tal como se denota en la lista anterior, son 4 módulos que se imparten desde la media técnica, estos tienen un identificador o código que permiten abreviarlo y relacionarlos de manera más simple. Adicionalmente, de los módulos se debe almacenar el título del módulo y una descripción de este.

Por otro lado, se debe realizar el almacenamiento de la información de los estudiantes que perecen a las instituciones educativas y hacen parte del proceso de la media técnica. De los estudiantes es pertinente conocer la siguiente información: número de expediente, nombre completo, fecha de nacimiento (edad), documento de identificación, tipo de documento, telefono celular, correo electrónico, genero, colegio al que pertenece y el estado (el estudiante puede estar activo, inactivo o retirado). También es importante que él estudiante pueda ser vinculado a un grupo que se cree dentro de la institución (se debe tener en cuenta que un estudiante al pasar de grado, debe almacenar su información histórica).

Los docentes que se contratan para el proceso de media técnica (no es necesario tener el proceso de contratación almacenado), deben ser vinculados a los diferentes módulos que se imparte en cada una de las instituciones educativas, de esta manera se puede establecer que módulo dicta cada uno de los docentes que se contrató para el proceso en las instituciones educativas. Se debe tener en cuenta que un docente vinculado a un curso puede ser desvinculado o este puede renunciar, pero se debe guardar el seguimiento de asistencia que se pudo sacar durante su permanencia.

Una vez se tiene claridad de los módulos que imparte cada docente en las instituciones educativas, se debe almacenar la asistencia de cada uno de los estudiantes en una sesión que dicta el docente en una fecha programada.

Explicación de las tablas

Tabla

Interpretación para el almacenamiento

Tabla de los actores que interactúan dentro del aplicativo.

Tabla

Interpretación para el almacenamiento

Tabla de los estados que puede asumir un actor dentro del aplicativo.

Tabla

Interpretación para el almacenamiento

Tabla que almacena los tipos de documentos que se le pueden asignar a los actores dentro del sistema.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar los módulos que se dictan en el programa de media técnica y deben ser asignados a los docentes y a los estudiantes que se les imparte estos módulos.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar la información de las instituciones educativas adscritas al programa de media técnica y brindan este programa a sus estudiantes.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar los datos de los actores que intervienen en el sistema, tal como docentes, estudiantes, coordinadores o asesores. Algunos aspectos a resaltar en esta tabla son:

  • Como son usuarios muy diversos, no todos los campos aplicaran en todos los casos, tal como lo es el campo institución_id, este campo solo aplica para estudiantes y lo mismo puede pasar en el caso de los otros usuarios.

  • La contraseña que se codifica en esta tabla, almacena una cadena de texto que aplica el algoritmo de encriptación SHA1 (Se explica más adelante).

  • En esta tabla y en otras, existen los campos fecha_creacion y fecha_actualizacion, los cuales se conocen como campos de auditoria y permiten llevar un control exacto de la fecha de cuando se crea o actualiza un registro en la tabla. Para este fin se hace del tipo de campo timestamp, que permite controlar este tipo acciones en las tablas.

  • El campo genero se maneja mediante una enumeración, este es un buen caso en el cual es recomendable emplear este tipo de campo, ya que el genero que se maneja solo son hombre y mujer, por lo cual no hay necesidad de crear otra tabla para administrar esta información.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar los datos correspondientes a los grupos de las instituciones educativas y se matriculan en la media técnica. Se permite asociar un carácter alfanumérico que se almacena en el campo letra, esto se debe a que los grupos en las instituciones se les asocia este identificador, pero no es obligatorio. Dentro de la estructura se valida que la combinación de institución educativa, grado y año sea única, puesto que no tiene sentido que un colegio tenga un grupo del mismo grado dos veces en un mismo año, esto no es posible, por ello se crea un índice de tipo unique que agrupa estos tres campos.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar la asociación de entre el estudiante y el grupo, de esta manera se puede identificar cuales son los integrantes de cada grupo. Para esta tabla se debe crear una restricción unique entre un estudiante y un grupo, con la finalidad de evitar que en un grupo se puede matricular el mismo estudiante mas de una vez.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar la relación entre el módulo, el docente y el grupo de la institución educativa donde se va impartir la formación. Para el caso de esta tabla, también se establece un índice unique que no permite que un docente dicte mas de una vez el mismo módulo en una institución educativa por regla de negocio.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar y registrar cada una de las sesiones impartidas por parte de los docentes. En esta tabla se crea un índice de tipo unique con los campos clase_id y fecha_sesion, con el fin de evitar que para una clase un docente registre mas una sesión en una misma fecha.

Tabla

Interpretación para el almacenamiento

Tabla para almacenar y registrar la asistencia de los estudiantes a una sesión programada por parte de un docente. Esta tabla presenta las siguientes particularidades:

  • Cuenta con un índice de tipo unique para evitar que un integrante de un grupo (estudiante) se le registre mas de una vez la asistencia a una sesión de clase programada por el docente.

  • El campo asiste recibe un numero entero, donde 0 indica que el estudiante no asistió y 1 indica que el estudiante asistió a la sesión de clase.

  • La tabla no se relaciona directamente con la tabla actor, puesto que la asistencia se le debe registrar a un estudiante inscrito en un grupo, por lo cual se genera la relación con la tabla integrantes_grupos.

Script de base de datos

El script SQL de base de datos es el archivo de carga que contiene la definición de todas las estructuras mediante DDL, además los datos de carga inicial, con los que la base de datos queda poblada en sus tablas principales o tablas maestras mediante DML.

Algoritmo de encriptación SHA1

El hash SHA1 es un algoritmo de generación de firmas. Un hash es una cadena de letras y números que resulta del cálculo sobre una cadena de origen. La idea es que el algoritmo provea distintas firmas (o hashes SHA) para distintos orígenes. El cálculo es irreversible y de una sola vía, es decir que a partir de un hash es muy difícil calcular la cadena original que lo formó a través del algoritmo SHA.

Los hashes SHA1 se utilizan para almacenar las firmas de las contraseñas. De este modo, en vez de guardar las contraseñas de los usuarios, almacenamos los hashes de las contraseñas, evitando que la contraseña viaje por la red. A partir del hash no se puede calcular la contraseña original. También se utiliza SHA para firmar documentos. El algoritmo SHA1 ha sido puesto en duda ya que se pudieron calcular colisiones de firmas, o sea, hashes iguales para distintas cadenas de entrada. Sin embargo, SHA se sigue considerando seguro para la mayoría de las aplicaciones.

La función sha1() de MySQL calcula una suma de comprobación SHA-1 de 160 bits para una cadena.

La función devuelve un valor como una cadena binaria de 40 dígitos hexadecimales. Si la cadena proporcionada como argumento es NULL, la función devuelve NULL.

En el ejemplo anterior el sha1 de la cadena politecnico es 3e478ac6f2b51f65f1a45bbc6a8d51b2d5c94ad8.

Diccionario de datos

El diccionario de datos es un listado organizado de todos los datos que pertenecen a un sistema.

El objetivo de un diccionario de datos es dar precisión sobre los datos que se manejan en un sistema, evitando así malas interpretaciones o ambigüedades.

Define con precisión los datos de entrada, salida, componentes de almacenes, flujos, detalles de las relaciones entre almacenes, etc.

Los diccionarios de datos son buenos complementos a los diagramas de flujo de datos, los diagramas entidad relación, etc.

Generar diccionario de datos con PhpMyAdmin

A continuación, se generará el diccionario de datos de la base de datos media_tecnica_app, mediante PhpMyAdmin, vemos los pasos:

1. Ingrese a la base de datos de la cual desea generar el diccionario de datos.

2. Elegir la opción Diccionario de datos.

3. Una vez allí veremos el diccionario de datos correspondiente a la base de datos sobre la cual estamos ubicado.

Diccionario de datos Media Técnica App

A continuación, se anexa el diccionario de datos que se genero desde PhpMyAdmin, tenga en cuenta que lo ideal es que este diccionario se genere una vez la base de datos ya se encuentra en su ultima versión y cuenta con todos los ajustes necesarios.

actores

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(5)

No

documento

varchar(20)

No

tipo_documento

varchar(3)

Sí

NULL

tipo_documento -> codigo

nombres

varchar(255)

No

apellidos

varchar(255)

No

contrasena

varchar(80)

Sí

NULL

correo

varchar(100)

Sí

NULL

telefono_celular

varchar(30)

Sí

NULL

numero_expediente

varchar(255)

Sí

NULL

genero

enum('hombre', 'mujer')

No

fecha_nacimiento

date

Sí

NULL

estado_actor_id

int(5)

No

1

estado_actores -> id

institucion_id

int(5)

Sí

NULL

instituciones_educativas -> id

tipo_actor_id

int(5)

Sí

1

tipo_actores -> id

fecha_creacion

timestamp

Sí

current_timestamp()

fecha_actualizacion

timestamp

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

6

A

No

uq_documento

BTREE

Sí

No

documento

6

A

No

uq_numero_expediente

BTREE

Sí

No

numero_expediente

6

A

Sí

fk_tipo_actor

BTREE

No

No

tipo_actor_id

6

A

Sí

fk_institucion_estudiante

BTREE

No

No

institucion_id

6

A

Sí

fk_estado_actor

BTREE

No

No

estado_actor_id

2

A

No

fk_tipo_documento

BTREE

No

No

tipo_documento

6

A

Sí

asistencias_sesiones

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(5)

No

sesion_clase_id

int(5)

Sí

NULL

sesiones_clases -> id

integrante_grupo_id

int(5)

Sí

NULL

integrantes_grupos -> id

asiste

int(1)

Sí

0

Permite determina si el estudiante asiste o no a clase

observaciones

text

Sí

NULL

fecha_creacion

timestamp

Sí

current_timestamp()

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

8

A

No

uq_sesion_integrante

BTREE

Sí

No

sesion_clase_id

8

A

Sí

Un integrante solo puede asistir una vez por sesion

integrante_grupo_id

8

A

Sí

fk_integrante_grupo

BTREE

No

No

integrante_grupo_id

8

A

Sí

clases

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(5)

No

grupo_id

int(5)

Sí

NULL

grupos -> id

modulo_id

int(5)

Sí

NULL

modulos -> id

docente_id

int(5)

Sí

NULL

actores -> id

fecha_creacion

timestamp

Sí

current_timestamp()

fecha_actualizacion

timestamp

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

4

A

No

fk_clases_modulo

BTREE

No

No

modulo_id

4

A

Sí

fk_clases_docente

BTREE

No

No

docente_id

4

A

Sí

uq_clase

BTREE

No

No

grupo_id

4

A

Sí

modulo_id

4

A

Sí

docente_id

4

A

Sí

estado_actores

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(11)

No

estado

varchar(30)

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

2

A

No

grupos

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(5)

No

institucion_id

int(5)

No

instituciones_educativas -> id

grado

int(2)

No

letra

char(1)

Sí

NULL

a_formacion

int(4)

Sí

NULL

Año de formación

descripcion

varchar(30)

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

2

A

No

uq_ie_grado

BTREE

Sí

No

institucion_id

2

A

No

Un grado, de una institucion educativa solo puede estar presente una sola vez en un año 0 Un grado, de una institucion educativa solo puede estar presente una sola vez en un año

grado

2

A

No

a_formacion

2

A

Sí

instituciones_educativas

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(11)

No

nombre_ie

varchar(300)

No

docente_encargado_mt

varchar(300)

No

pagina_web

varchar(100)

Sí

NULL

direccion

varchar(100)

No

foto_ie

varchar(100)

Sí

NULL

descripcion_ie

text

Sí

NULL

telefono_institucional

varchar(20)

Sí

NULL

correo_institucional

varchar(100)

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

2

A

No

integrantes_grupos

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(5)

No

estudiante_id

int(5)

No

actores -> id

grupo_id

int(5)

No

grupos -> id

observaciones

text

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

4

A

No

uq_grupo_estudiante

BTREE

Sí

No

estudiante_id

4

A

No

grupo_id

4

A

No

fk_grupo_integrantes

BTREE

No

No

grupo_id

4

A

No

modulos

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(11)

No

modulo

varchar(255)

Sí

NULL

mod

varchar(10)

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

4

A

No

uq_mod

BTREE

Sí

No

mod

4

A

Sí

sesiones_clases

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(5)

No

clase_id

int(5)

No

clases -> id

fecha_sesion

date

No

observacion

text

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

4

A

No

uq_sesion_clase_fecha

BTREE

Sí

No

clase_id

4

A

No

La sesión de una clase solo puede ser una por fecha

fecha_sesion

4

A

No

tipo_actores

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

id (Primaria)

int(5)

No

perfil

varchar(100)

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

id

4

A

No

tipo_documento

Columna

Tipo

Nulo

Predeterminado

Enlaces a

Comentarios

Media (MIME) type

codigo (Primaria)

varchar(3)

No

descripcion

varchar(100)

Sí

NULL

Índices

Nombre de la clave

Tipo

Único

Empaquetado

Columna

Cardinalidad

Cotejamiento

Nulo

Comentario

PRIMARY

BTREE

Sí

No

codigo

6

A

No

Creación de base de datos Media Técnica App en Clever Cloud

Como último paso en este ejercicio práctico, será crear paso a paso en Clever Cloud (Plataforma gratuita donde podemos tener desplegadas nuestras bases de datos) la base de datos del ejercicio planteado en este módulo. Veamos:

2. Partiremos del hecho, de contar con una cuenta ya creada. Ingrese sus credenciales o inicie con algún mecanismo que nos facilita el inicio de sesión, tal como GitHub.

3. Una vez dentro la plataforma, ingrese mediante la siguiente ruta: Add an organization >> Create… >> an add-on

4. Una vez allí, seleccionamos la opción de MySQL.

5. Luego seleccionamos la opción de desarrollo (DEV), que no acarrea ningún costos. Luego vamos a la parte inferior y presionamos sobre el botón Next.

6. Damos un nombre a la instancia de la base de datos, luego seleccionamos la región y damos clic sobre el botón Next.

7. Ya tenemos lista la instancia para correr nuestro script. Vamos a la sección PHPMyAdmin

8. Damos clic sobre la base de datos que la plataforma nos asigna.

9. Nos dirigimos a la sección de SQL y copiamos el script de base de datos del ejercicio práctico.

10. Por último, procedemos a correr el script de base de datos que pegamos en el campo que se nos habilita para ejecutar los comandos SQL y damos clic sobre el botón Go.

11. Con este último paso ya tenemos nuestra base de datos, con las estructuras y datos iniciales.

Quiero saber más …

Ejercicios

Ejercicio 1

El siguiente cuestionario cuenta con preguntas que abarcan todas las temáticas que se vieron durante este módulo de base de datos.

a. ¿Qué es una Base de datos?

b. ¿Qué es un SGBD?

c. ¿Qué es un atributo?

d. ¿Qué es un Registro?

e. ¿Qué es una Entidad?

f. ¿Cuáles son los tipos de relaciones que existen?

g. ¿Cuáles son Comandos DLL?

h. ¿Cuáles son los comandos DML?

i. ¿A qué se refieren las cláusulas?

j. Nombre tres clausulas

k. Nombre tres funciones de agregado

l. ¿Qué es una clave principal?

m. ¿Qué es una clave candidata o llave foránea?

n. ¿Qué es un diagrama entidad relación?

Ejercicio 2

Investigue e indique en que consiste el algoritmo de encriptación MD5

Ejercicio 3

Asegúrese de encriptar las contraseñas (MD5 o SHA1) de la tabla de usuarios de PPI con la que debe contar el proyecto de PPI (anexe evidencia).

Ejercicio 4

Genere el diccionario de datos de la base de datos de PPI.

Ejercicio 5

Despliegue y anexe evidencia de la base de datos del PPI en Clever Cloud

Modelo Relacional Media Técnica App

El script de base de datos del ejercicio que se está trabajando en este módulo queda publicado en el siguiente enlace .

1. Ingresar a la plataforma mediante el siguiente enlace e iremos a la sección de Login.

·

·

·

·

·

https://github.com/devoscarmesa/base_datos_semana_8
https://github.com/devoscarmesa/base_datos_semana_8/blob/main/modelo_relacional.svg
https://github.com/devoscarmesa/base_datos_semana_8/blob/main/media_tecnica_app.sql
https://www.clever-cloud.com/en/
https://www.mysql.com/
https://www.clever-cloud.com/en/
https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html
https://github.com/devoscarmesa/base_datos_semana_8