Fundamentos de SQL: Ventajas, Desafíos y Técnicas de Manipulación de Datos

Ventajas de SQL

  1. Independencia de los proveedores: SQL se ofrece en todos los principales proveedores de DBMS.
  2. Portabilidad entre sistemas informáticos.
  3. Estándares SQL: el ANSI y el ISO, publicaron el estándar en 1986. El ANSI y el ISO publicaron un estándar oficial de SQL en 1986 que se extendió rápidamente en 1992.
  4. Aprobación de IBM: fue originado por investigadores de IBM, y desde entonces se ha convertido en un producto estratégico de IBM basado en su DB2.
  5. ODBC y Microsoft: el estándar patrocinado por Microsoft es el acceso a las base de datos abiertas, una facilidad basada en SQL.
  6. Fundamento relacional: es un lenguaje de bases de datos relacionales.
  7. Estructura de alto nivel parecida al inglés.
  8. Consultas interactivas ad hoc: proporciona a los usuarios acceso ad hoc a los datos almacenados.

El mito de la portabilidad

  1. Códigos de error: el estándar SQL-89 (o SQL1) no especifica los códigos de error a devolver cuando SQL detecta un error, y todas las implementaciones comerciales utilizan su propio conjunto de códigos de error. SQL2 especifica códigos de error estándar.
  2. Tipos de datos: el estándar SQL1 define un conjunto mínimo de tipos de datos, pero omite alguno de los tipos más populares y útiles, tales como las cadenas de caracteres de longitud variable, las fechas y horas y los datos monetarios. SQL2 afronta esto.
  3. Tablas del sistema: el estándar SQL1 no dice nada acerca de las tablas del sistema que proporcionan información referente de la estructura de la propia base de datos. Cada vendedor tiene su propia estructura para estas tablas. Las tablas se estandarizaron en SQL2.
  4. SQL interactivo: el estándar especifica el SQL de programa utilizado por un programa de aplicación, y no el SQL interactivo.
  5. Interfaz de programa: el estándar SQL1 especifica una técnica abstracta para utilizar SQL desde dentro de un programa de aplicaciones. Ningún producto SQL comercial utiliza esta técnica. SQL2 especifica una interfaz SQL embebida para leguajes de programación populares, pero no interfaces a nivel de llamada.
  6. SQL dinámico: el estándar SQL1 no incluye las características requeridas para desarrollar frontales de bases de datos de propósito general, tales como herramientas de consulta y generadores de informes. SQL2 lo incluye.
  7. Diferencias semánticas: es posible ejecutar la misma consulta con dos implementaciones SQL diferentes, y producir resultados diferentes.
  8. Secuencias de cotejo: el estándar SQL1 no define la secuencia de cotejo (ordenación) de los caracteres almacenados en la base de datos. SQL2 incluye una especificación detallada.
  9. Estructura de bases de datos: los detalles del nombrado de la base de datos y de cómo se establece la conexión inicial varían enormemente y no son portables. SQL2 crea más uniformidad, pero no puede enmascarar completamente estos detalles.

Arquitecturas

  1. Arquitectura centralizada: se basa en un computador central, en el cual está alojado la base de datos y el programa de aplicación. Tanto el DBMS como los datos físicos residen un mismo lugar, junto con el programa de aplicaciones que acepta entradas desde el terminal de usuario. El DBMS accede a la base de datos para extraer los registros almacenados recuperando los datos necesarios, y el programa de aplicación muestra el resultado en pantalla. El sistema es multiusuario por lo que cuando acceden varios usuarios, se degrada el rendimiento.
  2. Arquitectura servidora de archivos: una aplicación que se ejecuta en una computadora personal puede acceder de forma transparente a los datos localizados en un servidor de archivos, que almacena los archivos compartidos. Esta arquitectura proporciona un rendimiento excelente para consultas típicas, ya que cada usuario dispone de la potencia completa de una computadora personal ejecutando su propia copia del DBMS. Produce un intenso tráfico de red y un bajo rendimiento para consultas de este tipo.
  3. Arquitectura cliente/servidor: las computadoras personales están conectadas en una red junto a un servidor de bases de datos que almacena las bases de datos compartidas. Las funciones del DBMS están divididas en dos partes. El front-end de bases de datos, tales como herramientas de consulta interactiva, generadores de informe y programas de aplicación, se ejecutan en la computadora personal.

DDL

Conjunto de sentencias SQL que permiten modificar la estructura de la base de datos. Sentencias:

  • CREATE: permite crear y definir un objeto de la bd.
  • DROP: permite eliminar un objeto.
  • ALTER: permite modificar la definición de un objeto.

DML

  • INSERT
  • UPDATE
  • DELETE

Condiciones de búsqueda

  1. Test de comparación: compara el valor de una expresión con el valor de otra.
  2. Test de rango: examina si el valor de una expresión cae dentro de un rango especificado de valores.
  3. Test de pertenencia a conjunto: comprueba si el valor de una expresión se corresponde con uno de un conjunto de valores.
  4. Test de correspondencia con patrón: comprueba si el valor de una columna que contiene datos de cadena de caracteres se corresponde a un patrón especificado.
  5. Test de valor nulo: comprueba si una columna tiene un valor NULL.

Reglas para procesamiento de consultas de tabla única

Los resultados producidos por una sentencia SELECT, se especifica aplicando cada una de sus cláusulas, una por una. La cláusula FROM se aplica en primer lugar, luego se aplica la cláusula WHERE, posteriormente se aplica la cláusula SELECT, finalmente se aplica la cláusula ORDER BY para ordenar los resultados.

  1. Comenzar con la tabla designada en FROM.
  2. Si hay cláusula WHERE, aplicar condición de búsqueda a cada fila de la tabla, reteniendo aquellas filas para las cuales la condición de búsqueda es TRUE.
  3. Se arma la lista de selección.
  4. Si se especifica SELECT DISTINTC, se eliminan las filas duplicadas.
  5. Si hay una cláusula ORDER BY, se ordenan los resultados de la consulta según la especificación.

Actualización de la BDA implica que

Modifica el contenido de la BD, El DBMS debe controlar la integridad de la bd y el acceso concurrente.

Reglas de procesamiento de consultas multitabla

  1. Formar el producto de las tablas indicadas en la cláusula FROM.
  2. Si hay una consulta WHERE, aplicar su condición de búsqueda a cada fila de la tabla producto, reteniendo aquellas filas para las cuales la condición de búsqueda es verdadera.
  3. Extraer las columnas seleccionadas en la cláusula SELECT.
  4. Si se especifica SELECT DISTINCT, eliminar las filas duplicadas de los resultados que se hubieran producido.
  5. Si hay una cláusula ORDER BY, ordenar los resultados de las consultas.

Reglas de procesamiento de consultas resumen

  1. Se forma el producto de las tablas indicadas en la cláusula FROM (si hay una sola tabla, se designa esa tabla).
  2. Si hay una cláusula WHERE, se aplica la condición de búsqueda a cada fila de la tabla producto, reteniendo las que cumplen con la condición de búsqueda.
  3. Se extraen las columnas de la lista de selección. Para una columna simple, se utiliza el valor de la columna. Si es una función de columna, se utiliza como argumento el conjunto entero de filas.
  4. Si se especifica SELECT DISTINCT, se eliminar las filas duplicadas.
  5. Si hay ORDER BY, se ordenan los resultados.

Qué es la integridad de datos

Para preservar la consistencia y

la corrección de los datos almacenados, un DBMS relacional impone típicamente una o más restricciones de integridad de datos. Estas restricciones restringen los valores que pueden ser insertados en la base de datos o creados mediante una actualización de la base de datos. Varios tipos diferentes de restricciones de integridad:

1. Datos requeridos: algunas columnas en una base de datos deben contener un valor de dato válido en cada fila; no se permite la ausencia de valor o que contengan valores NULL.2. Chequeo de validez: cada columna de una base de datos tiene un domino, un conjunto de valores que son legales para esa columna.3. Integridad de entidad: la clave primaria de una tabla debe contener un valor único en cada fila diferente de los valores de las filas restantes.4. Integridad referencial: una clave foránea en una BD relacional enlaza cada fula de la tabla hija que contiene la clave ajena con la fila de la tabla padre que contiene el valor de clave primaria correspondiente.5. Reglas comerciales: las actualizaciones de una base de datos pueden estar restringidas por reglas comerciales que gobiernan las transacciones en el mundo real que están representadas por las actualizaciones.6. Consistencia: muchas transacciones del mundo real producen múltiples actualizaciones a una base de datos. Hay que mantener la BD en un estado correcto y consistente.


PROPIEDADES DE LAS TRANSACCIONES

Atomicidad: una transacción debe ser una unidad atómica de trabajo. Se realizan todas las tareas o no se realiza ninguna.Coherencia: cuando finaliza, una transacción debe dejar todos los datos en un estado coherente. En una base de datos relacional, se deben aplicar todas las reglas a las modificaciones de la transacción para mantener la integridad de todos los datos. Todas las estructuras internas de datos, como índices de árbol B o listas doblemente vinculadas, deben estar correctas al final de la transacción.Aislamiento: las modificaciones realizadas por transacciones simultáneas se deben aislar de las modificaciones llevadas a cabo por otras transacciones simultáneas. Una transacción reconoce los datos en el estado en que estaban antes de que otra transacción simultánea los modificara o después de que la segunda transacción haya concluido, pero no reconoce un estado intermedio. Esto se conoce como seriabilidad, ya que deriva en la capacidad de volver a cargar los datos iniciales y reproducir una serie de transacciones para finalizar con los datos en el mismo estado en que estaban después de realizar las transacciones originales.Durabilidad: una vez concluida una transacción, sus efectos son permanentes en el sistema. Las modificaciones persisten aún en el caso de producirse un error del sistema.


COMMIT: señala el final correcto de una transacción. Informa al DBMS que la transacción está ahora completa, todas las sentencias que forman la transacción han sido ejecutadas y la base de datos es autoconsistente.
ROLLBACK: señala el final sin éxito de una transacción. Informa al DBMS que el usuario no desea completar la transacción, en vez de ello, el DBMS debe deshacer los cambios efectuados a la base de datos durante la transacción. El DBMS restaura la base de datos a su estado antes de que la transacción comenzara.

PROBLEMAS SI NO HUBIERA UN MANEJO ADECUADO DE LA BDA

Problema de la actualización perdida: se da cuando dos programas leen los mismos datos y utilizan los datos como base para un cálculo y luego tratan de actualizar los datos.
Problema de los datos no confirmados: se da cuando ocurre una actualización y la transacción es abortada.
Problema de los datos inconsistentes: se da con la consulta de datos con actualización confirmada posterior con otro usuario.
Problema de la inserción fantasma: se da con una consulta de BD y posterior inserción que afecta sobre la vista anterior.
SOLUCION: el usuario no debe ver actualizaciones si confirmación, mantener transacciones breves, liberar bda usando commit a menudo.


NIVELES DE CERRAMIENTO

1. Nivel de las BD: en su forma menos elaborada el DBMS puede bloquear la base entera por cada transacción. Fácil de implementar, pero solo permite un solo proceso a la vez.
2. Nivel de tablas: el DBMS bloquea aquellas tablas accedidas por una transacción.
3. Nivel de página: el DBMS bloquea los grupos individuales de datos procedentes del disco, conforme son accedidos por una transacción.
4. Nivel de fila: permite que dos transacciones diferentes y concurrentes accedan a dos filas diferentes en una misma tabla, incluso si están en el mismo bloque del disco. Es un problema para tablas pequeñas.
5. Nivel de datos: el cerramiento a nivel de datos individuales es una teoría, donde proporcionaría más paralelismo que los cerramientos a nivel de fila. Distintas transacciones pueden acceder a la misma fila siempre y cuando accedan a distintas columnas. Sería muy pesado. Ningún DBMS comercial lo utiliza.Ç

TIPOS DE CERRAMIENTO

 Cierre compartido: se utiliza en el DBMS cuando una transacción desea leer datos de la base de datos (consultas).
 Cierre exclusivo: se utiliza en el DBMS cuando una transacción desea actualizar datos en la base de datos (actualizaciones).


VISTAS: Una vista es una tabla virtual en la base de datos cuyos contenidos están definidos por una consulta.

VENTAJAS:

1. Seguridad: cada usuario puede obtener permisos para acceder a la base de datos únicamente a través de un pequeño conjunto de vistas que contienen los datos específicos que el usuario está autorizado a ver, restringiendo así el acceso del usuario a los datos almacenados. 2. Simplicidad de consulta: una vista puede extraer datos de varias tablas diferentes y presentarlos como una única tabla, haciendo que consultas multitabla se formulen como consultas de una sola tabla con respecto a la vista. 3. Simplicidad estructurada: las vistas pueden dar a un usuario una visión de la estructura dela base de datos presentando esta como un conjunto de tablas virtuales que tienen sentido para ese usuario. 4. Aislamiento frente al cambio. Una vista puede presentar una imagen consistente inalterada de la estructura de la base de datos, incluso si las tablas fuente subyacentes se dividen, reestructuran o cambien de nombre. 5. Integridad de datos. Si se accede a los datos y se introducen a través de una vista, el DBMS puede comprobar automáticamente los datos para asegurarse que satisfacen restricciones de integridad especificadas.


VISTAS:

DESVENTAJAS

1. Rendimiento: las vistas crean la apariencia de una tabla, pero el DBMS debe traducir las consultas con respecto a la vista en consultas con respecto a las tablas fuentes subyacentes. 

2. Restricciones de actualización: cuando un usuario trata de actualizar filas de una vista, el DBMS debe traducir la petición a una actualización sobre las filas de las tablas fuentes subyacentes.

Amenazas a las BD: se produce pérdida o degradación de … •Integridad: proteger ante modificaciones inadecuadas •Disponibilidad •Confidencialidad: acceso no autorizado,violar leyes de privacidad hasta seguridad nacional


Medidas de Control: Control de acceso: restringir con creación de cuentas y contraseñas •Control de inferencias: evitar acceso a información estadística de BD, por grupos de edad, de ingresos, educación, etc. •Control de flujo: evitar que información llegue a usuarios no autorizados •Cifrado de datos: algoritmos de cifrado, con clave, para disfrazar mensaje

Seguridad en las BD Seguridad y DBA

•Creación de cuentas para usuarios o grupo de acceso a DBMS •Concesión de derechos/privilegios y Grant Option •Retiro de privilegios •Asignación nivel de seguridad: Asignar las cuentas de usuario al nivel de seguridad adecuado. Auditoría de BD: registrar acciones desde usuario/terminal.

Control de acceso discrecional: Flexibles-Vulnerables-Común en DBMS •Niveles de asignación de privilegios de uso del sistema de BD: •Cuenta: para permitir crear tablas o vistas •Tabla/vistas: crea matriz de acceso con usuarios/privilegios/objeto del privilegio Control de acceso obligatorio: Rígidas-Mayor protección •Clasificar los datos y los usuarios (servicios de inteligencia, militares, gobierno) •Clases de seguridad: AltoSecreto(TS), Secreto(S), Confidencial(C),NoClasificado (U) •Sujeto: usuario, cuenta, programa •Objeto: tabla, fila, columna, vista, operación


CREACIÓN DE TABLA:

CREATE TABLE Item

(ord_id INT,

item_id INT,

Id_producto INT,

precio DECIMAL(11,2),

cantidad INT,

cantidad_enviada INT, 

        CONSTRAINT item_ordid_itemid_pk PRIMARY KEY (ord_id, item_id),

        CONSTRAINT item_ordid_prodid_uk UNIQUE (ord_id, Id_producto),

        CONSTRAINT item_cantidad CHECK (cantidad>0),

        CONSTRAINT item_fk_producto

FOREIGN KEY (id_producto) REFERENCES PRODUCTO (id)

ON UPDATE CASCADE

ON DELETE NO ACTION



Eliminación de una tabla: DROP TABLE articulos

INSERT:

INSERT INTO EMPLEADOS (Legajo, Apellido, Nombre, Fec_nac) VALUES (202, ‘ÁLVAREZ’, ,‘LILIANA‘, Convert(date,‘17/12/99′, 3))

ACTUALIZACION:

Sintaxis: UPDATE tabla SET atrib1=valor, atrib2=valor, … [WHERE condición]

1- Actualización de una tupla

UPDATE empleados SET catego = ‘B’ WHERE nroemp = 674

2- Actualización de todas las tuplas UPDATE

empleados SET catego = ‘B’

3- Actualización según condición

UPDATE empleados SET catego = ‘A‘, fecha_baja=GetDate() WHERE year(fecha_ing)

4- Actualización con subconsulta

UPDATE Cuentas_Cli SET credito=credito*1.15, fecha_act=GetDate() WHERE nrocli IN (SELECT DISTINCT nrocliente FROM facturas WHERE nrofac>932)


ELIMINACION:

Sintaxis: DELETE FROM tabla [WHERE condición]

 1- Eliminación de una tupla

DELETE FROM empleados WHERE nroemp = 12058

2- Eliminación de múltiples tuplas

DELETE FROM empleados WHERE nroemp IN (12058,10235,13099,11091)

3- Eliminación de todas las tuplas

DELETE FROM empleados

4- Eliminación con subconsulta

DELETE FROM empleados WHERE nroemp IN (SELECT nroemp FROM historico)


Eliminación de una tabla

DROP TABLE articulos

Cambiar dimensión de columna

ALTER TABLE Articulos ALTER COLUMN descripcion CHAR(20)

Agregar columna

ALTER TABLE Alumnos ADD observacion VARCHAR(20) NULL

Eliminar columna

ALTER TABLE Alumnos DROP COLUMN observacion


SELECT FROM 

WHERE IN-NOT IN   –  GROUP BY

HAVING  – ORDER BY


EXISTS – NOT EXISTS

ANY – ALL

COUNT¨() cuenta el nro de valores de una columna

COUNT(*) cuenta el nro de filas de una consulta