DISEÑO DE BASE DE DATOS Y LENGUAJE SQL

Lenguaje SQL y Base de Datos

Hola que tal, bienvenido a este artículo sobre Diseño de Base de Datos y Lenguaje SQL (Structured Query Language) en español Lenguaje de Consulta Estructurado. SQL es un lenguaje que permite realizar diferentes operaciones como: consultar, insertar, modificar, crear tablas, base de datos etc.. Entre los temas que voy a tratar en este artículo está el Diseño de base de datos, tipos de comandos, cláusulas, operadores y funciones de agregado y ejemplos.

Qué es una base de datos?

Una base de datos no es más que un conjunto de tablas relacionadas entre sí y que almacena información relacionada con el modelo del negocio para el que fue diseñada, cada tabla está formada por filas y columnas que a su vez forman registros, existen varios tipos de bases de datos: jerárquicas, orientadas a objetos, en red y relacionales, esta última es la que vamos a ver a profundidad, puesto que es la que se utiliza en la mayor parte de sistemas transaccionales.

Antes de crear una base de datos se debe hacer el diseño de la misma, existen muchas ventajas por la cual debemos previamente hace el diseño, por ejemplo: un buen diseño hace que una base de datos sea mantenible y escalable, la parte del diseño empieza a partir de los requerimientos del cliente.

Aunque el tema es bastante extenso, de hecho existen libros completos sobre el diseño. En este artículo sólo me enfocaré en lo más general y pondré más énfasis en el lenguaje SQL.

Por lo general en base de datos relacionales el Diseño Conceptual, Diseño Lógico y Físico son los pasos que se sigue para un buen diseño de base de datos, obviamente esto no garantiza que al seguir estos pasos tu base de datos esté bien diseñada, en gran parte también dependerá del criterio de tu análisis con respecto a los requerimientos del cliente.

Diseño Conceptual de Base de Datos

El Diseño Conceptual parte de los requerimientos del cliente, lo que se pretende en esta fase es crear un esquema gráfico que permita identificar los elementos del problema (por ejemplo: alumno, profesor, materia, curso)  y las relaciones que existen entre cada elemento (a veces se usa los atributos, en otras no), para el diseño se utiliza formas, como por ejemplo: óvalos, rectángulos,  líneas que describen las relaciones que existen entre cada elemento, un ejemplo de este diseño es el modelo entidad-relación.

Si bien es cierto que no hay reglas que estandaricen este proceso, pero podemos guiarnos de ciertas premisas, por ejemplo:

Los sustantivos por lo general hacen referencia a una entidad, revisemos el siguiente enunciado: Los clientes pueden realizar transferencias desde y hacia cualquier banco.

En este caso clientes, transferencias, banco, son entidades que se pueden representar como una tabla.

Los verbos hacen referencia a una interrelación entre entidades, por ejemplo utilizamos el mismo ejemplo anterior: Los clientes pueden realizar transferencias desde y hacia cualquier banco.

El verbo realizar es una interrelación en la que se identifica que un cliente realiza una transferencia.

Diseño Lógico de Base de Datos

En este diseño, una entidad se convierte en una tabla (también es conocida como relación), se definen los atributos concretos, las claves primarias para cada tabla, se aplica el concepto de tipos de relaciones que pueden existir entre los registros de las tablas por ejemplo: uno a uno, uno a muchos, muchos a muchos, las interrelaciones (Diseño Conceptual) se convierten claves foráneas entre tablas, se aplican las formas normales con la finalidad de depurar atributos redundantes e inconsistencias, por ejemplo:

Se asume que se tiene una relación de uno a muchos entre clientes y direcciones, un cliente puede tener varias direcciones, id es la clave primaria de la tabla CLIENTES y clientes es la clave foránea en la tabla Direcciones, la idea de las claves foráneas es relacionar los registros entre tablas para utilizarlas posteriormente en reportes.

Diseño Físico de Base de Datos

Es la implementación del Diseño Lógico en un Sistema Gestor de Base de Datos (SGBD), como por ejemplo MySQL, Oracle, PostgreSQL etc.. Este depende y está atado al propio motor de base de datos.

Revisado ya el diseño de base de datos veamos cómo utilizar los comandos SQL.

Tipos de comandos en Lenguaje SQL

En SQL se diferencian dos tipos de comandos los DLL y los DML, los DLL (Data Definition Language) o lenguaje de definición de de datos, sirven para crear, eliminar o modificar bien sea tablas, bases de datos, vistas, procedimientos almacenados, funciones etc., mientras que los comandos DML (Data Manipulation Language) trabajan únicamente sobre los datos, por lo que este tipo de comandos te sirven para hacer consultas, crear, modificar y eliminar registros.

Comandos DLL

Como te decía estos comandos definen la estructura donde se va almacenar la información, por ejemplo:

CREATE: Permite crear tablas, campos e índices.

DROP: Elimina tablas, campos e índices.

ALTER: Modifica una tabla creada, bien sea agregando nuevos campos o modificando la definición de los mismos.

Comandos DML

Este tipo de comandos permite trabajar con los registros de la base de datos, es así que el comando:

SELECT: Obtiene los registros de una o más tablas de acuerdo al criterio de la consulta, siempre debe estar acompañada de la cláusula FROM y opcionalmente cuando haya la necesidad de las cláusulas WHERE, ORDER BY, GROUP BY, HAVING.

INSERT: Se utiliza para insertar uno o varios registros en una tabla.

UPDATE: Modifica uno o varios registros de una tabla de acuerdo a un criterio, por ejemplo: cuando quieres actualizar los registros de una tabla llamada clientes donde el apellido es igual a ‘Fernández’.

DELETE: Elimina uno o varios registros de una tabla, si no se utiliza un criterio para eliminar, se eliminarán todos los registros de la tabla.

La utilidad de los comandos anteriores sin las cláusulas, operadores, funciones de agregado no tiene sentido, es por eso que antes de hacer ejercicios, vamos a ver cuál es su definición y para qué sirven.

Cláusulas para sentencias en SQL

En el caso de FROM es un comando que indica sobre que tabla o tablas voy a extraer los registros, en los otros casos modifica los criterios de búsqueda en los registros devueltos de una tabla, por ejemplo:

FROM: Especifica el nombre de qué tabla o tablas se va hacer la selección o consulta.

WHERE: Específica cuales son los criterios de búsqueda que deben reunir los registros para ser seleccionados.

GROUP BY: Separa los registros seleccionados en grupos específicos.

HAVING: Especifica una condición que deben tener los registros seleccionados pero a nivel de grupo.

ORDER BY: Se lo utiliza para ordenar los registros seleccionados de acuerdo a un criterio o campo.

Otro componente del lenguaje SQL son los operadores, estos pueden ser lógicos o relacionales.

Operadores Lógicos

AND: Evalúa dos expresiones, si ambas son ciertas devuelve TRUE.

OR: Evalúa dos expresión, basta que una sea cierta para devolver TRUE.

NOT: Niega la expresión.

Operadores de Comparación

< (menor que),> (mayor que),<> (diferente),<= (mayor o igual),>=  (menor o igual),= (igual). Aunque todos son útiles, en lo personal y los más interesantes son los siguientes:

BETWEEN: Permite seleccionar aquellos registros cuyo campo satisfaga un intervalo de valores.

LIKE: Selecciona los registros cuyo campo satisfaga el parámetro de búsqueda. LIKE permite buscar coincidencias, bien sea al inicio, al final o que estén contenidas dentro del campo que se aplica el criterio de búsqueda, por ejemplo apellido LIKE ‘A%’, selecciona todo los registros donde el campo apellido empiecen con A, ‘%A’, selecciona los registros con terminaciones en A, %A%, los registros que contengan la letra A.

IN: Selecciona los registros cuyo parámetro de búsqueda esté contenido dentro del campo por el cual se hace el filtro.

NOT IN: Lo contrario de IN.

Las funciones de agregado

Existen consultas en la que se necesita obtener un sólo valor por un grupo de registros, por ejemplo, si se quisiera obtener el número de habitantes de un país por provincias, para estos casos son útiles las funciones de agregado:

AVG: Obtiene el promedio de un grupo de registros, se aplica a campos de tipo numérico.

COUNT: Cuenta el número de registros, se debe aplicar a un campo.

SUM: Suma un número de registros, por lo general de un grupo específico, se aplica a campos numéricos.

MAX: Devuelve el valor máximo de un conjunto de registros, debe ser aplicado a un campo específico.

MIN: Devuelve el valor mínimo de un conjunto de registros, debe ser aplicado a un campo específico

Estructuras básicas de sentencias SQL

Estructura de una sentencia de consulta en la que se obtiene todos los campos y registros de una tabla.

Adicionalmente existe otra forma de realizar una consulta, cuando se quiere obtener sólo algunos campos específicos de una tabla por ejemplo:

Consultas bajo un criterio de consulta, estas consultas se obtiene bajo un criterio, por ejemplo edad, nombres, sueldo, etc.

Estructura de una sentencia para insertar un registro.

Hay que tomar en cuenta que los valores de tipo varchar deben ir encerrados en apóstrofos.

Estructura de una sentencia para actualizar un registro, sólo se utiliza los campos que se quiere actualizar.

Por lo general campo1 es el id del registro.

Estructura de una sentencia para eliminar un registro.

Bien, con toda esta premisa que un principio es muy teórica pero muy útil a la hora de los ejemplos, empecemos con la práctica.

Ejemplo práctico sobre Diseño de Base de Datos y Lenguaje SQL

Vamos a inventarnos un problema en el que una Distribuidora de Productos de Limpieza necesita tener información sobre los pedidos que hacen su clientes, se conoce que los clientes realizan pedidos de diferentes productos, también se conoce que los productos se agrupan por categorías, finalmente para saber si un pedido ha sido entregado se toma en cuenta la fecha de entrega.

Información adicional

Un pedido tiene un número de pedido, el producto, el cliente, la cantidad de productos, valor, la fecha de pedido y la fecha de entrega, un pedido puede tener varios productos.

Un cliente tiene un número de cliente, cédula de identidad, dirección, teléfono.

Un producto tiene número de producto, nombre, precio, la categoría y stock disponible.

Las categorías contienen un número y una descripción.

Por último y de acuerdo a la descripción del problema, el despachador de la distribuidora necesita los siguientes reportes:

  • Se necesita obtener un reporte con todos los productos (Consulta simple).
  • Se necesita obtener un reporte con todos los pedidos ordenados por fecha (order by).
  • Se necesita obtener un reporte con el número de productos por categoría (count y group by).
  • Se necesita obtener un reporte con todos los productos que empiecen con la letra J (like).
  • Se necesita obtener un reporte de todos los clientes que hicieron pedidos en el mes de enero (between).
  • Se necesita obtener un reporte con todos los pedidos mayores a 10 dólares (operador >).
  • Se necesita obtener un reporte con el promedio de pedidos (avg).
  • Se necesita obtener un reporte con el mayor valor de un pedido realizado (max)
  • Se necesita obtener un reporte con los pedidos entregados (NOT).

Parte del Diseño Conceptual

El Diseño Conceptual bien lo puedes hacer en papel o utilizar algún software como por ejemplo dia (Descargar Software de modelado DIA), dia es un software para realizar diagramas UML, diagramas entidad-relación, diagrama de redes, diagrama de circuitos electrónicos etc, y si no quieres descargar nada también puedes usar Lucidchart que es un programa para realizar diagramas de flujo online, la versión gratuita es suficiente para hacer este tipos de ejemplos. 

Continuando con el ejemplo, en la descripción del problema he puesto en negrita las entidades (pedido, clientes, productos categorías) el diseño conceptual que quedaría de la siguiente manera:

Diseño Lógico

En esta parte ya se define las tablas como tal, con sus atributos y relaciones:

clientes (id, cedula, nombre, direccion, telefono).

categorias (id, descripcion)

productos (id, nombre, precio, stock, categorias_id)

pedidos (id, fecha_pedido, fecha_entrega, total, clientes_id)

detallepedidos (id, cantidad, total, pedidos_id, prodcutos_id)

Diseño Físico

El diseño físico comprende el script que generará una base de datos en MySQL.

Descargar el Script Ahora!!

Reportes utilizando sentencias SQL

Se necesita obtener un reporte con todos los productos (Consulta simple).

Se necesita obtener un reporte con todos los pedidos ordenados por fecha (order by).

Se necesita obtener un reporte con el número de productos por categoría (count y group by).

Se necesita obtener un reporte con todos los productos que empiecen con la letra J (like).

Se necesita obtener un reporte de todos los clientes que hicieron pedidos en el mes de enero (between).

Se necesita obtener un reporte con todos los pedidos mayores a 10 dólares (operador >).

Se necesita obtener un reporte con el promedio del valor de pedidos realizados (avg).

Se necesita obtener un reporte con el mayor valor de un pedido realizado (max).

Se necesita obtener un reporte con los pedidos entregados (NOT).

Espero que este artículo te haya ayudado. Nos vemos en la próxima entrada!!, saludos.

 

Full Stack Developer, JavaScript, PHP, Java, Spring, Laravel, Vuejs, Blogger, aprendiendo y compartiendo conocimientos. Cursos de Programación Web en: https://programacionfullstack.com/

Artículos Recomendados

Dejar un Comentario

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.