SQL (Lenguaje de Consulta estructurado) Ing. Liliana Minsero

(1)

SQL (Lenguaje de Consulta estructurado)

Ing. Liliana Minsero

(2)

MÓDULO 2

Análisis y Diseño con SQL Server

(3)

Importancia del Análisis y Diseño

con SQL Server

(4)

Importancia del análisis y diseño

El lenguaje estructurado de consultas (SQL, Structured Query Language) apoya la creación y mantenimiento de la base de datos relacional y la gestión de los datos dentro de la base de datos. El término ha sido utilizado para referirse a cualquier cosa, desde una colección de nombres y direcciones hasta un complejo sistema de recuperación y almacenamiento de datos que se basa en interfaces de usuarios y una red de computadoras y servidores.

Hay tantas definiciones para la palabra base de datos como libros sobre éstas. Por otra parte, los distintos proveedores de DBMS han desarrollado diferentes arquitecturas, por lo que no todas las bases de datos están diseñadas de la misma manera. A pesar de la falta de una definición absoluta, la mayoría de las fuentes coinciden en que una base de datos, por lo menos, es una colección de datos organizada en un formato estructurado que es definido como metadatos que describe esa estructura. Puede pensar en los metadatos como información sobre los datos almacenados, que define cómo se almacenan éstos en una base de datos

.

(5)

Importancia del análisis y diseño

El autor E. F. (Ted) Codd, a quien se hace referencia en el contexto del

modelo relacional. En 1970, Codd publicó su trabajo más importante “A

Relational Model Of Data For Large Shared Data Banks” (Un modelo relacional de

datos para grandes bancos de datos compartidos), en el diario Communications of

the ACM, volumen 13, número 6 (junio de 1970). Codd define una estructura de

datos relacional que protege los datos y permite que sean manipulados de

manera que es previsible y resistente al error. El modelo relacional, el cual se basa

principalmente en los principios matemáticos de la teoría de conjuntos y lógica de

predicados, apoya la recuperación de datos sencilla, aplica la integración de datos

(la precisión y coherencia de los datos), y proporciona una estructura de base de

datos independiente de las aplicaciones al acceder a los datos almacenados.

(6)

Importancia del análisis y diseño

Una base de datos bien diseñada brinda a los usuarios acceso a información fundamental. Se debe analizar y diseñar una base de datos que funcione bien y se adapte a las necesidades futuras de la organización. Por eso es importante considerar los aspectos básicos sobre el diseño de una base de datos y cómo perfeccionarlo para obtener resultados óptimos.

Una base de datos bien estructurada permite:

• Ahorra espacio en el disco eliminando los datos redundantes.

• Mantiene la precisión e integridad de los datos.

• Ofrece acceso a los datos de formas útiles.

Diseñar una base de datos útil y eficiente requiere seguir el proceso adecuado, incluidas las siguientes etapas:

• Análisis de los requisitos o identificación del propósito de tu base de datos.

• Organización de los datos en tablas.

• Especificación de las claves primarias y análisis de las relaciones.

• Normalización para estandarizar las tablas.

(7)

Importancia del análisis y diseño

Realicemos un análisis detallado de cada paso. Tomaremos en cuenta el modelo de base de datos relacional de Edgar Codd escrito en SQL (en lugar de modelos jerárquicos, de red).

Consultado en: https://www.lucidchart.com/pages/es/tutorial-de-estructura-y-dise%C3%B1o-de-bases-de-datos

(8)

Análisis de los requisitos: identificar

el propósito de la base de datos

(9)

Análisis de los requisitos: identificar el propósito de la base de datos

Comprender el propósito de la base de datos determinará las opciones en todo el proceso de diseño. Debemos asegurarnos de observar la base de datos desde todas las perspectivas. Por ejemplo, si estas creando una base de datos para una biblioteca pública, deberías considerar las formas en que los clientes y bibliotecarios necesitarían acceder a los datos.

Aquí te mostramos algunas formas de reunir información antes de crear la base de datos:

• Entrevistar a las personas que la usarán.

• Analizar formularios de negocio, como facturas, plantillas de horas trabajadas, encuestas.

• Examinar cualquier sistema de datos existente (incluidos archivos físicos y digitales).

Se Comienza reuniendo cualquier dato existente que se incluirá en la base de datos. Luego enumera los tipos de datos que quieres almacenar y las entidades o personas, cosas, ubicaciones y eventos que esos datos describen, del siguiente modo:

(10)

Análisis de los requisitos: identificar el propósito de la base de datos

Clientes

• Nombre

• Dirección

• Ciudad, estado, código postal

• Dirección de correo electrónico Productos

• Nombre

• Precio

• Cantidad en stock

• Cantidad en el pedido

(11)

Análisis de los requisitos: identificar el propósito de la base de datos

Pedidos

• Número del pedido

• Representante de ventas

• Fecha

• Producto(s)

• cantidad

• Precio

• Total

(12)

Análisis de los requisitos: identificar el propósito de la base de datos

Más adelante, esta información se volverá parte del directorio de datos, que describe las tablas y los campos dentro de la base de datos. Asegúrate de dividir la información en partes útiles lo más pequeñas posibles. Por ejemplo, considera separar el nombre de la calle del país para poder filtrar más adelante a los individuos según su país de residencia. Además, evita ubicar el mismo punto de datos en más de una tabla porque agregarás una complejidad innecesaria.

Cuando sepas qué tipos de datos incluirán las bases de datos, de dónde provienen esos datos y cómo se usarán, estarás listo para comenzar a planificar la base de datos real.

(13)

Estructura de la base de datos: los

bloques de creación de una base de

datos.

(14)

Estructura de la base de datos: los bloques de creación de una base de datos

El siguiente paso es organizar la representación visual de la base de datos. Para ello, se debe comprender exactamente cómo se estructuran las bases de datos relacionales.

Dentro de una base de datos, los datos relacionados se agrupan en tablas, cada una de ellas consiste en filas (también llamadas "tuplas") y columnas, como una hoja de cálculo.

Para convertir las listas de datos en tablas, comienza creando una tabla para cada tipo de entidad, como productos, ventas, clientes y pedidos. Mostramos un ejemplo a continuación:

NOMBRE APELLIDO EDAD CODIGO POSTAL

Roger Williams 43 34760

Jerrica Jorgensen 32 97453

Samantha Hopkins 56 64829

(15)

Estructura de la base de datos: los bloques de creación de una base de datos

Cada fila de una tabla se llama "registro". Los registros incluyen datos sobre algo o alguien, como un cliente específico. En cambio, las columnas (también conocidas como

"campos" o "atributos") contienen un único tipo de información que aparece en cada registro, como las direcciones de todos los clientes enumerados en la tabla.

Registro

Campo

(16)

Estructura de la base de datos: los bloques de creación de una base de datos

Con el fin de que los datos sean consistentes de un registro al siguiente, asigna el tipo de datos apropiado a cada columna. Los tipos de datos comunes incluyen:

CHAR - una longitud específica de texto.

VARCHAR - texto de longitudes variables.

TEXT - grandes cantidades de texto.

INT - número entero positivo o negativo.

FLOAT, DOUBLE - también puede almacenar números de punto flotante.

BLOB - datos binarios..

(17)

Estructura de la base de datos: los bloques de creación de una base de datos

Algunos sistemas de gestión de bases de datos también ofrecen el tipo de datos denominado "Autonumeración", que genera automáticamente un número único en cada fila.

A los efectos de crear una visión general de la base de datos, conocida como un diagrama entidad-relación, no incluiremos las tablas reales, sino que cada tabla se convertirá en un recuadro del diagrama. El título de cada recuadro debería indicar qué describen los datos en la tabla, mientras que los atributos están enumerados a continuación, del siguiente modo:

ESTUDIANTE Id de estudiante Fecha de cumpleaños

Nivel de grado

(18)

Estructura de la base de datos: los bloques de creación de una base de datos

Por último, se debe decidir qué atributo o atributos funcionarán como clave primaria para cada tabla, si procede. Una clave primaria (PK) es un identificador único para una entidad determinada, esto significa que se puede seleccionar un cliente concreto incluso si solo se conoce ese valor.

Los atributos seleccionados como claves primarias deben ser únicos, inalterables y estar siempre presentes (nunca NULL o vacíos). Por este motivo, los números de pedido y los nombres de usuario son excelentes claves primarias, mientras que los números de teléfono o direcciones postales no lo son. También se puede usar múltiples campos conjuntamente como la clave primaria (esto se denomina "clave compuesta").

Cuando llegue el momento de crear la base de datos real, se ubicará la estructura de datos lógicos y la estructura de datos físicos en el lenguaje de definición de datos admitido por nuestro sistema de gestión de base de datos. En este punto, también se debería calcular el tamaño aproximado de la base de datos para asegurarte de tener el nivel de rendimiento y el espacio de almacenamiento necesarios.

(19)

Creación de relaciones entre

entidades.

(20)

Creación de relaciones entre entidades

Cuando las tablas de base de datos se conviertan en tablas, se estara listo para analizar las relaciones entre esas tablas. La cardinalidad se refiere a la cantidad de elementos que interactúan entre dos tablas relacionadas. Identificar la cardinalidad ayuda a asegurarte de que se ha dividido los datos en tablas de la forma más eficiente.

Cada entidad puede, potencialmente, tener una relación con todas las demás, pero por lo general esas relaciones pueden ser de uno de tres tipos:

Relaciones uno a uno

Si hay una única instancia de la Entidad A para cada instancia de la Entidad B, se dice que tienen una relación de uno a uno (a menudo se escribe 1:1). Puedes indicar este tipo de relación en un diagrama ER mediante una línea con un guión en cada extremo:

(21)

Creación de relaciones entre entidades

A menos que tengamos un buen motivo para no hacerlo, una relación 1:1 generalmente indica que la mejor opción sería combinar los datos de las dos tablas en una sola tabla.

Sin embargo, quizás se desee crear tablas con una relación de uno a uno en una serie particular de circunstancias. Si se tiene un campo con datos opcionales, como

"descripción", que está en blanco para muchos registros, puedes mover todas las descripciones a su propia tabla, eliminando espacio vacío y mejorando el rendimiento de la base de datos.

Para garantizar que los datos coincidan correctamente, luego tendrías que incluir al menos una columna idéntica en cada tabla, lo más probable es que sea la clave primaria.

(22)

Creación de relaciones entre entidades

Relaciones uno a muchos

Estas relaciones suceden cuando un registro de una tabla está asociado a múltiples entradas en otra tabla. Por ejemplo, un solo cliente puede haber solicitado múltiples pedidos o una persona haberse llevado muchos libros de la biblioteca a la vez. Las relaciones uno a muchos (1:M) se indican con lo que se denomina "notación patas de gallo" como en el siguiente ejemplo:

Para implementar una relación uno a muchos (1:M) mientras se prepara una base de datos, simplemente se agrega la clave primaria de "un" lado de la relación como un atributo en la otra tabla. Cuando una clave primaria se detalla en otra tabla de esta manera, se denomina "clave extranjera". La tabla en el lado "1" de la relación es considerada una tabla principal respecto de la tabla secundaria que se encuentra del otro lado.

(23)

Creación de relaciones entre entidades

Relaciones muchos a muchos

Cuando múltiples entidades de una tabla se pueden asociar a múltiples entidades de otra tabla, se dice que tienen una relación de muchos a muchos (M:N). Esto puede suceder en el caso de estudiantes y clases, ya que un estudiante puede inscribirse en muchas clases, y una clase puede tener numerosos estudiantes.

En un diagrama ER, estas relaciones se representan con estas líneas:

Lamentablemente, no es posible implementar directamente este tipo de relación en una base de datos. En cambio, debes dividirlo en dos relaciones uno a muchos.

(24)

Creación de relaciones entre entidades

Para ello, debes crear una nueva entidad entre esas dos tablas. Si la relación M:N existe entre ventas y productos, quizás llames a esa nueva entidad "productos_vendidos", ya que mostraría los contenidos de cada venta. Tanto las tablas de ventas como de productos tendrían una relación 1:M con "productos_vendidos". Esta clase de entidad intermedia se llama "tabla de enlaces", "entidad asociativa" o "tabla de unión" en diversos modelos.

Cada registro de la tabla de enlaces se correspondería con dos de las entidades de las tablas contiguas (también puede incluir información adicional). Por ejemplo, una tabla de enlaces entre estudiantes y clases podría verse así:

(25)

Creación de relaciones entre entidades

Otra forma de analizar las relaciones es considerar qué lado de la relación debe existir para que el otro lado exista. El lado no obligatorio puede marcarse con un círculo en la línea donde debería haber un guión. Por ejemplo, un país tiene que existir para tener un representante en las Naciones Unidas, pero lo opuesto no se cumple:

Dos entidades pueden ser mutuamente dependientes (una no podría existir sin la otra).

(26)

Normalización de la base de datos.

(27)

Normalización de la base de datos

Una vez que se tenga un diseño preliminar para tu base de datos, se puede aplicar reglas de normalización para asegurarte de que las tablas estén estructuradas correctamente.

Dicho esto, no todas las bases de datos son buenas candidatas para la normalización. Generalmente, las bases de datos de procesamiento de transacciones en línea (OLTP), en las que los usuarios se encargan de la creación, lectura, actualización y eliminación de los registros, deberían estar normalizadas.

Las bases de datos de procesamiento analítico en línea (OLAP) que favorecen el análisis y la generación de informes funcionarían mejor con un grado de desnormalización, ya que el énfasis está en la velocidad de cálculo. Estas incluyen aplicaciones de soporte de decisiones en las que los datos se deben analizar rápidamente, pero no deben modificarse.

Cada forma, o nivel de normalización, incluye las reglas asociadas a las formas inferiores.

(28)

La primera forma normal

La primera forma normal (abreviada como "1FN") especifica que cada celda de la tabla puede tener un solo valor, nunca una lista de valores. Por lo tanto, una tabla como esta no cumple con los requisitos:

Quizás se piense que la mejor solución sea dividir los datos en columnas adicionales, pero eso también rompería las reglas: una tabla con grupos de atributos repetidos o estrechamente relacionados entre sí no cumple con la primera forma normal.

Por ejemplo, la tabla a continuación no cumple con los requisitos:

ID del producto Color Precio $

1 marrón, amarillo 15

2 rojo, verde 13

3 azul, naranja 11

(29)

La primera forma normal

En cambio, divide los datos en múltiples tablas o registros hasta que cada celda contenga solo un valor y no halla columnas adicionales. En este punto, se dice que los datos son "atómicos", es decir que se dividen en partes útiles lo más pequeñas posibles.

Para la tabla anterior, podrías crear una tabla adicional llamada "Datos de ventas", que haría coincidir productos específicos con ventas. Así, "Ventas" tendría una relación 1:M con

"Datos de ventas".

(30)

La segunda forma normal

La segunda forma normal (2NF) establece que todos los atributos deben ser totalmente dependientes de toda la clave primaria. Eso significa que cada atributo debería depender directamente de la clave primaria, en lugar de indirectamente a través de algún otro atributo.

Por ejemplo, se considera que el atributo "edad" que depende de "fecha de nacimiento", que a su vez depende de "ID de estudiante" tiene una dependencia funcional parcial; y una tabla que contenga estos atributos no cumpliría con la segunda forma normal.

Además, una tabla con una clave primaria compuesta de múltiples campos viola la segunda forma normal si uno o más de los otros campos no dependen de cada parte de la clave.

Por lo tanto, una tabla con estos campos no respetaría la segunda forma normal porque el atributo "Nombre del producto" depende del ID del producto, pero no del número de pedido:

• Número de pedido (clave primaria)

• ID de producto (clave primaria)

• Nombre del producto

(31)

SQL y UML

El lenguaje unificado de modelado (UML) es otra forma visual de expresar sistemas complejos creados en un lenguaje orientado a objetos. Muchos de los conceptos mencionados se conocen en UML con distintos nombres. Por ejemplo, una entidad se llama "clase" en UML.

En la actualidad, se emplea en entornos académicos y en las comunicaciones entre diseñadores de software y sus clientes.

Sistemas de gestión de bases de datos

Muchas de las elecciones de diseño que tomarás dependen del sistema de gestión de base de datos que elijas. Algunos de los sistemas más comunes incluyen:

Oracle DB – MySQL - Microsoft SQL Server – PostgreSQL - IBM DB2

Cuando se elige, un sistema de gestión de base de datos en función del costo, los sistemas operativos, las funciones y más.

(32)

SQL y UML

(33)

SQL

Consultado en:https://books.google.co.ve/books?id=rBBLuoW90EcC&pg=PA182&lpg=PA182&dq=UML+y+SQL&source=bl&ots=uUaEHGaBEB&sig=tzMBc4C_AUt9mG574se6Tk8wf5k&hl=es- 419&sa=X&ved=0ahUKEwiJi9juitLbAhUEyFkKHc6GCks4ChDoAQhJMAk#v=onepage&q=UML%20y%20SQL&f=false

(34)

Creación de tablas

La creación de tablas se lleva a cabo con la sentencia CREATE TABLE.

 Ejemplo: creación del siguiente esquema de BD.

CLIENTES (DNI, NOMBRE, DIR) SUCURSALES (NSUC, CIUDAD) CUENTAS (COD, DNI, NSUCURS, SALDO)

 Se empieza por las tablas más independientes:

• CREATE TABLE CLIENTES (

• DNI VARCHAR(9) NOT NULL,

• NOMBRE VARCHAR(20),

• DIR VARCHAR(30),

• PRIMARY KEY (DNI)

• );

CREATE TABLE SUCURSALES ( NSUC VARCHAR(4) NOT NULL, CIUDAD VARCHAR(30),

PRIMARY KEY (NSUC) );

(35)

Creación de tablas

El siguiente paso es crear la tabla CUENTAS, con las claves externas:

CREATE TABLE CUENTAS (

COD VARCHAR(4) NOT NULL, DNI VARCHAR(9) NOT NULL,

NSUCURS VARCHAR(4) NOT NULL, SALDO INT DEFAULT 0,

PRIMARY KEY (COD, DNI, NSUCURS),

FOREIGN KEY (DNI) REFERENCES CLIENTES (DNI),

FOREIGN KEY (NSUCURS) REFERENCES SUCURSALES (NSUC) );

Las claves candidatas, es decir, aquellos atributos no pertenecientes a la clave que no deben alojar valores repetidos, se pueden indicar con la cláusula.

NOT NULL.

(36)

Crear la base de datos en SQL

En el entorno de la estructura del entorno de SQL se hace mención de una base de datos. La razón es que en ninguna parte del estándar SQL se define el término “base de datos”. De hecho, la única mención de una base de datos, en términos de cómo podría encajar en la estructura del entorno de SQL, es que pueda considerar a los sitios como la base de datos, aunque esto se ofrece como una sugerencia más que como una definición absoluta.

Si bien el estándar utiliza la palabra para referirse a SQL como el lenguaje de una base de datos, en realidad nunca se define el término.

En SQL Server, por ejemplo, una instancia del software de DBMS puede gestionar cualquier número de bases de datos, siendo cada base de datos una colección lógica de base de datos objetos que el diseñador escoge para administrar juntos.

Sybase, MySQL y DB2 de IBM tienen arquitecturas similares. SQL Server proporciona una consola de administración llamada Microsoft SQL Server Management Studio para ver y manipular la base de datos objetos.

(37)

Crear la base de datos en SQL

El panel Object Explorer a lo largo del margen izquierdo proporciona una estructura de directorio jerárquica que incluye una base de datos nodo, con cada base de datos mostrada debajo, y los objetos tales como tablas figuran abajo de cada base de datos. La figura a continuación muestra el SQL Server Management Studio con la base de datos INVENTARIO expandida hacia abajo de las columnas de la tabla CDS_ARTISTA.

(38)

Nombrado de objetos en un entorno SQL

Un identificador es un nombre dado a un objeto de SQL. El nombre puede ser de hasta 128 caracteres, y debe seguir los convenios definidos. Un identificador se puede asignar a cualquier objeto que se crea con instrucciones SQL, tales como dominios, tablas, columnas, vistas o esquemas. El estándar SQL:2006 define dos tipos de identificadores:

identificadores regulares e identificadores delimitados.

Los identificadores regulares son bastante restrictivos y deben seguir convenios específicos:

• Los nombres no se distinguen entre mayúsculas y minúsculas. Por ejemplo, Nombres_Artista es lo mismo que NOMBRES_ARTISTA y nombres_artista.

• Sólo se permiten letras, dígitos y guiones. Por ejemplo, se pueden crear identificadores tales como Primer_Nombre, 1erNombre o PRIMER_NOMBRE. Observe que el guión bajo es el único carácter válido que se usa como separador entre palabras. Los espacios no son aceptables ni tampoco guiones (los guiones se interpretan como operaciones de sustracción).

• No se puede utilizar palabras clave reservadas en SQL.

(39)

Nombrado de objetos en un entorno SQL

• SQL no distingue mayúsculas y minúsculas, por lo que respecta a los identificadores regulares.

• Todos los nombres se cambian a mayúsculas cuando se almacenan en SQL, que es la razón por la que 1erNombre y 1ERNOMBRE se leen como valores idénticos. Como ya se mencionó, el caso de falta de distinción a utilizar mayúsculas y minúsculas es el comportamiento predefinido en la mayoría de los RDBMS, y mientras que la predefinición se puede modificar en algunos productos, se recomienda que no se cambie, ya que no sería coherente con el estándar SQL y daría lugar a problemas de compatibilidad en caso de que utilice otros productos para acceder a sus datos.

• Los identificadores delimitados no son tan restrictivos como los identificadores regulares, pero aún deben seguir convenios específicos:

• El identificador debe estar incluido en un conjunto de comillas dobles, como el identificador “NombresArtista”.

• Las comillas no se almacenan en la base de datos, pero todos los demás caracteres se almacenan como aparecen en la instrucción SQL.

(40)

Nombrado de objetos en un entorno SQL

• Los nombres son sensibles a mayúsculas y minúsculas. Por ejemplo, “Nombres_Artista”

no es lo mismo que “nombres_artista” o “NOMBRES_ARTISTA”, pero

“NOMBRES_ARTISTA” es lo mismo que NOMBRES_ARTISTA y Nombres_Artista (porque los identificadores regulares se cambian a mayúsculas).

• La mayoría de los caracteres están permitidos, incluyendo espacios.

• Se pueden utilizar palabras clave reservadas a SQL.

• Cuando se decide cómo nombrar los objetos SQL, hay una serie de sistemas que se pueden seguir. La primera elección que tendrá que hacer es si desea utilizar los identificadores regulares o delimitados. También decidirá otras cuestiones, tales como utilizar mayúsculas o minúsculas, y el uso del guión bajo, y si los identificadores estarán en singular o plural. Por ejemplo, se puede nombrar una tabla como TitulosDiscoCompacto, titulos_disco_compacto, TITULOS_DISCO_ COMPACTO, “Titulos Disco Compacto”, o alguna otra forma de ese nombre. La parte importante para recordar es que debe elegir un nombre convencional y adherirse a él a lo largo de la codificación para una base de datos en particular.