1 - Introduccion

Volver al Inicio

Bases de Datos SQL

Las Bases de datos SQL, son bases de datos del tipo Relacionales y estructuradas. Su nombre SQL proviene del término (Structured Query Language), en español: Lenguaje de Consulta Estructurado. Se operan a través de SQL, (“sícuel”), un lenguaje derivado del estándar ANSI SQL. El lenguaje con el cual se manipulan se conoce como Transact SQL aunque algunas alternativas comerciales utilizan su propia variante con mínimas diferencias sobre el lenguaje estandarizado ANSI SQL.



Bases de Datos No-SQL

Las bases de datos No-SQL (not only SQL) difieren del modelo estructurado, apoyándose en un sistema de almacenamiento del tipo {clave - valor}. No utilizan el lenguaje SQL para realizar operaciones sobre la información almacenada. Su estructura se basa en el formato de transporte de datos JSON y, además de la categoría clave-valor, el almacenamiento de información se asemeja a bb.dd. documentales, y a las bb.dd. orientadas a grafos. Mongo DB, Cassandra, Cosmos DB y Firestore, son las más conocidas y utilizadas actualmente.



Data Warehouse

Un Data Warehouse es un almacén electrónico donde generalmente una empresa u organización mantiene una gran cantidad de información. Los datos de un data warehouse deben almacenarse de forma segura, fiable, fácil de recuperar y fácil de administrar.

El concepto de data warehouse se originó en 1988 con el trabajo de los investigadores de IBM, Barry Devlin y Paul Murphy aunque el término data warehouse fue acuñado por William H. Inmon, el cual es conocido como el padre de Data Warehousing. Inmon describió un data warehouse como una colección de datos orientada a un tema específico, integrado, variante en el tiempo y no volátil, que soporta el proceso de toma de decisiones.



2 - My Sql

Volver al Inicio





Es un sistema de gestión de bases de datos relacional desarrollado bajo licencia dual: Licencia pública general/Licencia comercial por Oracle Corporation y está considerada como la base de datos de código abierto más popular del mundo, y una de las más populares en general junto a Oracle y Microsoft SQL Server, todo para entornos de desarrollo web.

A continuación, se encuentra un link con los instructivos para poder descargar e instalar My Sql:

Guia de Instalación de My Sql

Glosario My Sql



Introducción a Bases de Datos Relacionales

Las bases de datos relacionales se basan en el Modelo Relacional usando N cantidad de tablas para representar, tanto los datos, como las relaciones entre estos. Es poco frecuente encontrar casos de una bb.dd. con una sola tabla pero, en el caso de que se dé esta situación, se le denomina como Base de datos Plana. El modelo relacional, en el cual se apoyan estas bases de datos, almacenan la información en un conjunto de tablas, y a su vez, las aprovechan para representar tanto los datos como también las relaciones entre cada una de estas.



3 - Descripción del Proyecto

Volver al Inicio



Temática para el trabajo final:

Base de datos de una Empresa de Medicina Prepaga donde se va a hacer foco en un modelo de esquema copo de nieve.

La empresa de medicina prepaga tiene varios centros donde cada uno de ellos tiene una especialidad particular. La estructura de la base de datos nos ayuda a almacenar los datos de forma eficiente y de ellos podemos obtener la siguiente información:

El objetivo es introducirnos en el mundo de las bases de datos relacionales, entender las distintas funcionalidades que nos proveen para el armado de una base de datos y otras sentencias que nos ayuden al armado final de la base de datos de una empresa de medicina prepaga.



4 - Descripción de las tablas

Volver al Inicio



Tablas

Cuando nos referimos a una tabla, estamos hablando específicamente de un tipo de elemento, que cumple el paradigma de Modelado de datos, y permite guardar la información denominada registros. Cada tabla se compone de Campos y Columnas. Cada campo debe ser único además de tener un tipo de dato asociado. Cada Columna de una tabla, define el total de la información que se almacena, comúnmente llamada registro. Un registro es el dato alojado en forma de fila, dentro de una tabla. Otro nombre que se le aplica comúnmente a los registros, es tupla. Las tablas también suelen ser llamadas, mucho más formalmente: entidad. Y, en algunos textos algo más antiguos: relación. Y al igual que el nombre tupla, aplicado a los registros, los campos o columnas también son denominados más formalmente como: atributos.



Estructura de Tablas

Es una estructura de tablas, las cuales a su vez se relacionan con otras tablas. Su principal característica es no poseer información repetida de forma innecesaria, lo que permite adicionar más información sin llegar a afectar la otra almacenada.



Ventajas del modelo Relacional



Tipos de Relaciones

Logicas



Tablas, descripción y atributos

A continuación se detallaran cada una de las tablas que componen el DER con sus atributos y descripciones:



\[ \textbf{Tabla: Afiliado }\]

Atributos Descripcion
id_afiliado INT NOT NULL PRIMARY KEY AUTOINCREMENT
nombre VARCHAR(50) NOT NULL
apellido VARCHAR(50) NOT NULL
email VARCHAR(50) NOT NULL
telefono INT NOT NULL
fecha_nacimiento DATE NOT NULL
domicilio VARCHAR(50) NOT NULL
estado_civil VARCHAR(50) NOT NULL
dni INT NOT NULL
id_ocupacion INT NOT NULL FOREIGN KEY
id_plan INT NOT NULL FOREIGN KEY



Esta tabla contiene los atributos de los afiliados de la empresa de medicina prepaga, podemos observar el ID como Primary Key y por último dos FK que hacen referencia a la ocupación del empleado y el plan. Estas serán dos tablas que se crean a parte para no repetir datos.

Un ejemplo sencillo: La ocupación puede ser empleado bajo relación de dependencia, monotributista, desempleado o jubilado. Estos datos se pondrán aparte en una tabla llamada ocupacion_afiliado y en la tabla afiliado solo de pondrá el número de ID ya que al ir dando de alta a los afiliados en el sistema nos ahorraríamos espacio en la base de datos al no tener que repetir tantos caracteres por fila y solamente completar con el ID de ocupación que viene de la tabla ocupacion_afiliado.



\[ \textbf{Tabla: Ocupación Afiliado }\]

Atributos Descripción
id_ocupacion INT NOT NULL PRIMARY KEY AUTOINCREMENT
ocupación VARCHAR(50) NOT NULL



Esta tabla contiene los siguientes datos: Bajo relación de dependencia, monotributista, desempleado y jubilado.



\[ \textbf{Tabla: Plan Afiliado }\]

Atributos Descripción
id_plan INT NOT NULL PRIMARY KEY AUTOINCREMENT
nombre VARCHAR(50) NOT NULL



Esta tabla contiene los siguientes datos: Medium y Premium.



\[ \textbf{Tabla: Medico }\]

Atributos Descripción
id_medico INT NOT NULL PRIMARY KEY AUTOINCREMENT
nombre VARCHAR(50) NOT NULL
apellido VARCHAR(50) NOT NULL
fecha_nacimiento DATE NOT NULL
domicilio VARCHAR(50) NOT NULL
telefono INT NOT NULL



Esta tabla contiene los datos de los médicos que prestan su servicio a distintos centros médicos.



\[ \textbf{Tabla: Centro Médico }\]

Atributos Descripción
id_centro INT NOT NULL PRIMARY KEY AUTOINCREMENT
nombre VARCHAR(50) NOT NULL
direccion VARCHAR(50) NOT NULL
telefono INT NOT NULL
codigo_postal INT NOT NULL
email VARCHAR(50) NOT NULL
fecha_nacimiento DATE NOT NULL
especialidad VARCHAR(50) NOT NULL



\[ \textbf{Tabla: Consulta }\]

Atributos Descripción
id_consulta INT NOT NULL PRIMARY KEY AUTOINCREMENT
consulta VARCHAR(50) NOT NULL
diagnostico VARCHAR(50) NOT NULL



Esta tabla contiene los datos de las consultas de los afiliados y los diagnósticos posterior a la consulta.



\[ \textbf{Tabla: Turnos }\]

Atributos Descripción
id_turno INT NOT NULL PRIMARY KEY AUTOINCREMENT
fecha DATE NOT NULL
id_medico INT NOT NULL FOREIGN KEY
id_afiliado INT NOT NULL FOREIGN KEY
id_consulta INT NOT NULL FOREIGN KEY
id_centro INT NOT NULL FOREIGN KEY



Esta tabla contiene los datos del turno. Se conecta con varias tablas, entre ellas la de consulta. Una vez registrado el turno en la base de datos y luego de la consulta se completan los valores en la tabla consulta con el diagnostico final.



\[ \textbf{Tabla: Proveedor }\]

Atributos Descripción
id_proveedor INT NOT NULL PRIMARY KEY AUTOINCREMENT
nombre VARCHAR(50) NOT NULL
telefono INT NOT NULL
cuit INT NOT NULL
mail VARCHAR(50) NOT NULL



La tabla contiene todos los datos de los proveedores que se van dando de alta. No tiene muchos movimientos en general.



\[ \textbf{Tabla: Tipo Insumo }\]

Atributos Descripción
id_tipo_insumo INT NOT NULL PRIMARY KEY AUTOINCREMENT
tipo_insumo VARCHAR(50) NOT NULL



Tabla que contiene datos sobre el tipo de insumo que compran los centros médicos. No tiene muchos movimientos.



\[ \textbf{Tabla: Insumos }\]

Atributos Descripción
id_insumo INT NOT NULL PRIMARY KEY AUTOINCREMENT
nombre VARCHAR(50) NOT NULL
id_centro INT NOT NULL FOREIGN KEY
id_tipo_insumo INT NOT NULL FOREIGN KEY
id_proveedor INT NOT NULL FOREIGN KEY



Esta tabla contiene los datos de cada uno de los insumos.



\[ \textbf{Tabla: Centro Médico Insumos }\]

Atributos Descripcion
id_centro_medico_insumos INT NOT NULL PRIMARY KEY AUTOINCREMENT
nombre VARCHAR(50) NOT NULL
id_centro INT NOT NULL FOREIGN KEY
id_insumo INT NOT NULL FOREIGN KEY



Tabla que conecta de muchos a muchos los insumos con los centros médicos.



\[ \textbf{Tabla: Pagos }\]

Atributos Descripción
id_pagos INT NOT NULL PRIMARY KEY AUTOINCREMENT
fecha_transaccion DATE NOT NULL
importe INT NOT NULL
id_medio_operacion INT NOT NULL FOREIGN KEY
id_proveedor INT NOT NULL FOREIGN KEY
id_centro INT NOT NULL FOREIGN KEY



Tabla que registra todos los pagos de todos los centros médicos. Se puede consultar el total de pagos por cada centro médico.



\[ \textbf{Tabla: Cobros }\]

Atributos Descripción
id_cobros INT NOT NULL PRIMARY KEY AUTOINCREMENT
fecha_transaccion DATE NOT NULL
importe INT NOT NULL
id_medio_operacion INT NOT NULL FOREIGN KEY
id_afiliado INT NOT NULL FOREIGN KEY
id_centro INT NOT NULL FOREIGN KEY



Tabla que registra todos los cobros de todos los centros médicos. El id_centro que aparece en la línea del cobro de la empresa de medicina prepaga no se relaciona necesariamente con que le haya hecho un pago a ese centro médico, sino que hace relación a que con mayor frecuencia se atiende en ese centro y se termina imputando el cobro de esa manera.



\[ \textbf{Tabla: Medio Operación }\]

Atributos Descripción
id_medio_operacion INT NOT NULL PRIMARY KEY AUTOINCREMENT
tipo_medio_operacion VARCHAR(50) NOT NULL



Tabla que registra todos medios financieros de pagos: Tarjeta de Débito, Crédito, Efectivo y Transferencias.



5 - Diagrama Entidad de Relación

Volver al Inicio



Un diagrama entidad-relación, también conocido como modelo entidad relación o ERD, es un tipo de diagrama de flujo que ilustra cómo las “entidades”, como personas, objetos o conceptos, se relacionan entre sí dentro de un sistema. Los diagramas ER se usan a menudo para diseñar o depurar bases de datos relacionales en los campos de ingeniería de software, sistemas de información empresarial, educación e investigación.

En este link se puede elaborar un DER preliminar para luego trabajarlo en una base de datos.





Luego de crear las tablas en My Sql podemos ver el Reverse Engineer.

La ingeniería inversa o retroingeniería es el proceso llevado a cabo con el objetivo de obtener información o un diseño a partir de un producto, con el fin de determinar cuáles son sus componentes y de qué manera interactúan entre sí y cuál fue el proceso de fabricación.





La siguiente imagen representa el Reverse Engineer de la base de datos creada:





6 - Inserción de datos

Volver al Inicio



Metodologia utilizada: Los valores en cada una de las tablas se insertaron desde archivos .csv.

Los archivos son los siguientes:





Cada una de las tablas contiene 10 observaciones

Ejemplo tabla afiliados:





A continuación se adjuntan scripts de los pasos para importar los datos y MySql final:









7 - Incersion de Datos desde Python

Volver al Inicio



A continuación, se adjunta un breve .gif que muestra la ingesta de datos a My Sql desde Python. Se utiliza un While que finaliza cuando el usuario desea dejar de ingresar datos. Los datos se ingresan en la tabla de afiliados dándolos de alta en el sistema.



.



8 - Inner Join

Volver al Inicio



Inner Join es una sentencia de SQL para recuperar datos de varias tablas al mismo tiempo. En la siguiente imagen se muestra una consulta de todos los afiliados de la base de datos con los pagos de las cuotas acumuladas en el año.

Se tienen en cuenta las siguientes sentencias:

  • Inner Join entre todas las tablas.

  • Sumatoria total de la variable importe de la tabla cobros.

  • Se extrae el año de la variable fecha_transaccion.

  • Se agrupa por afiliado único.

  • Se ordena de mayor a menor los importes de pago acumulados.





El siguiente join es entre la tabla pagos y proveedores, se agrupa cada uno de los proveedores con el importe total sumado de los pagos hechos a cada uno de ellos:



A continuación se trabajara con el software R para crear un gráfico de torta por proveedor con los montos totales de pago a cada uno de ellos:





pie_chart = df_2 %>% 
  hchart('pie', hcaes(x = df_2$Proveedor, y = df_2$`Pago Total`, color = viridis::mako(n = length(df_2$Proveedor)))) %>% 
  hc_add_theme(hc_theme_google()) %>% 
  hc_tooltip(pointFormat = '<b>Pago Total</b>  {point.y}') %>% 
  hc_title(text = 'Pie Chart Pagos por Proveedor en ARS',
           style = list(fontSize = '20px', fontWeight = 'bold')) %>% 
  hc_subtitle(text = '<b>From MySql</b>',
              style = list(fontSize = '16px', color = "black")) %>% 
  hc_credits(enabled = TRUE, text = '@MGaloto',
             style = list(fontSize = '16px', color = "black")) 

pie_chart

Pie Chart



9 - Vistas

Volver al Inicio



Una Vista SQL es básicamente una tabla virtual que se genera a partir de la ejecución de una o más consultas SQL, aplicada sobre una o más tablas. Su estructura corresponde a una serie de filas y columnas tal como encontramos en las tablas SQL, que almacenan la vista de la información tal como la definimos al crearla. Podemos guardar las Vistas con un nombre distintivo, para poder ejecutarlas cuantas veces consideremos necesario. Su almacenamiento se da en la sección Views de los objetos de la base de datos.



Beneficios

  • Privacidad de la información: los usuarios podrán ver solo aquellos datos que creamos convenientes mostrar.

  • Rendimiento de la bb.dd.: Crear queries sobre vistas complejas nos ahorra ejecutar una query pesada para llegar a la información.

  • Protección de datos: Aquellos usuarios que no poseen un entorno de pre-producción, las vistas evitan errores de borrado o alteración.



Scripts de Vistas

A continuación se adjuntan los scripts de las vistas

El código SQL de las vistas se encuentra en el siguiente link: Github Codigo



    1) Vista 1:

    Consulta y Diagnostico por Afiliado

    Esta vista permite guardarnos en una tabla aparte todos los turnos solicitados por los afiliados dados de alta en el sistema. Nos sirve para estar actualizados de los últimos turnos ya que esta ordenada por fecha.





    2) Vista 2

    Que medico atendió a cada paciente:

    Esta vista creada nos sirve para visualizar forma rapida mediante su consulta sobre que medicos atendieron a que paciente en determinada fecha.





    3) Vista 3

    Que medico atiende en cada centro medico:

    Esta vista permite que podamos visualizar de forma rapida que medicos atienden en los centros medicos.





10 - Funciones

Volver al Inicio



Por definición es importante indicar que una función es un conjunto de instrucciones SQL que realizan una tarea específica de manera automática. Una función acepta entradas en forma de parámetros y devuelve un valor.

Las funciones personalizadas o almacenadas de Mysql permiten procesar y manipular datos de forma procedural y eficiente. Dichos datos son enviados a través de uno o más parámetros, al momento de invocar la función, y devueltos como resultado por esta misma.

Podemos crear funciones, a la medida de nuestra necesidad, combinando a las mismas con funciones ya existentes del lenguaje Mysql, para así obtener los resultados deseados tal como necesitamos.

Algunos de los beneficios a destacar:

  • Mejoran la integridad y seguridad de los datos

  • Optimizan el rendimiento de la base de datos

  • Otorgan una mejor lectura del código

A continuación se adjuntan los scripts de las funcioness

    1) Función 1:

    Función donde ingresamos un CHAR y nos devuelve un NUMERIC

    Ingresamos el diagnóstico y nos regresa el porcentaje de pacientes con el diagnostico ingresado de nuestra base de datos

    En la parte inferior de la imagen adjunta se puede ver el resultado de la función una vez ingresado un CHAR.





    2) Función 2:

    Función que no requiere un valor de entrada.

    Esta funcion nos calcula automaticamente la diferencia entre ingresos y egresos de todos los centros medicos.

    En la parte inferior de la imagen adjunta se puede ver el resultado de la función una vez ingresado un CHAR.





11 - Stored Procedures

Volver al Inicio



Un Stored Procedure o Procedimiento Almacenado es un programa almacenado físicamente en una base de datos, creado para cumplir tareas específicas. Permite también establecer niveles de seguridad y manipular operaciones complejas o extensas del lado del servidor, evitando un ida y vuelta de datos que termine sobrecargando una red o servidor.

Su estructura es similar a las Funciones SQL pero, a diferencia de éstas, un Stored Procedure esta pensado para resolver desde una operación simple, hasta operaciones complejas que requieran modificar varias tablas y/o datos almacenados en una base de datos.

Algunos Beneficios:

  • El motor de bb.dd. controla las operaciones.
  • Se ejecuta en un servidor independiente.
  • Devuelve al usuario el resultado final, evitando sobrecargar su computadora con procesos.
  • Evita programar una lógica compleja del lado del usuario.
  • Minimiza los errores concentrando las operaciones.

A continuación se adjuntan los scripts de los Stored Procedures

    1) Stored Procedure 1:

    En el siguiente Stored Procedure insertamos valores en la tabla turno con el ID creado anteriormente en la tabla consulta.





    2) Stored Procedure 2:

    El siguiente Stored Procedure ejecuta un comando que nos permite ordenar la tabla que queramos por una variable.





12 - Triggers

Volver al Inicio



Un disparador o trigger es una funcionalidad que la base de datos ejecuta de forma automática cuando se realiza una operación de tipo Insert, Update o Delete en una tabla o vista, o cuando se ejecuta una consulta SQL sobre una tabla o vista.

Un trigger no puede existir sin una tabla asignada, podemos crear muchos triggers para una tabla.

Cuando un evento ya sea de tipo Insert, Update o Delete se realiza en una tabla que contiene uno o varios triggers, en la tabla que creamos que comienza con logs se ejecuta también ese evento modificando la tabla. Hay dos comandos que podemos utilizar, el Before y el After, con el primero cuando insertamos datos en la tabla principal primero ingresaran al triger y luego a la tabla principal, con el After ocurre todo lo contrario.

La principal función de los trigger es contribuir a mejorar la gestión de la base de datos. Gracias a ellos muchas operaciones se pueden realizar de forma automática, sin necesidad de intervención humana, lo que permite ahorrar mucho tiempo. Otra de sus funciones es aumentar la seguridad e integridad de la información

A continuación se adjuntan los scripts de los Triggers

    1) Triggers 1:

    En el siguiente Trigger se crea un log para la tabla afiliados y luego de insertar los datos en la tabla afiliados van a parar al trigger creado.

    Los triggers que creamos van a contener los datos de la fecha a la cual se ingreso el valor, un id de movimiento y el usuario que realizo el insert, delete o update.





    Lo contrario sucede con la próxima imagen que es un Before.



    2) Triggers 2:

    En el siguiente Trigger se crea un log para la tabla consulta y antes de que se completen los nuevos valores para la tabla se completan en la tabla de logs con fecha, usuario, id y tipo de operación.





    Lo contrario sucede con la próxima imagen que es un Before.



13 - Permisos

Volver al Inicio



Sentencias GRANT y REVOKE

Utilizando la sentencia GRANT podemos dar permisos al usuario creado para que pueda hacer los siguientes comandos sobre la base de datos o una tabla en particular: SELECT, INSERT, UPDATE y DELETE.

Con la sentencia REVOKE le quitamos permisos al usuario.

A continuación se adjuntan los scripts de los Usuarios creados y las sentencias