librofbd

I.S.C. Alejandro Guzmán Zazueta Materia:Fundamentos de Bases de Datos [email protected]

1

Unida I. CONCEPTOS DE BASES DE DATOS. Objetivo El estudiante conocerá los conceptos generales de bases de datos, usos y aplicaciones, así como su estructura general.

1.1. Definición de bases de datos.

Según (Korth y Silberschatz). Un sistema de base de datos (DBMS) es básicamente un sistema computarizado para guardar registros; es decir, es un sistema computarizado cuya finalidad general es almacenar información y permitir a los usuarios recuperar y actualizar esa información con base en peticiones. La información en cuestión puede ser de cualquier cosa que sea de importancia para el individuo u organización; en otras palabras todo lo que sea necesario para auxiliarle en el proceso general de su administración. Los usuarios del sistema pueden realizar una variedad de operaciones sobre dichos archivos por ejemplo: • Agregar nuevos archivos vacíos a la base de datos; • Insertar datos dentro de los archivos existentes; • Recuperar datos de los archivos existentes; • Modificar datos en archivos existentes; • Eliminar datos de los archivos existentes; • Eliminar archivos existentes de la base de datos.


2

1.2. Objetivos de las bases de datos.

Según (Korth y Silberschatz). Una manera de guardar la información en la computadora es el almacenarla en archivos del sistema operativo. Para permitir que los usuarios manipulen la información, el sistema tiene varios programas de aplicación que gestiona los archivos, incluyendo programas para:

Efectuar cargos o bonos en cuentas.

Añadir cuentas nuevas.

Calcular saldo en las cuentas.

Generar los extractos mensuales.

Los objetivos principales de un sistema de base de datos son disminuir los siguientes aspectos.

• Redundancia de datos. Puesto que los archivos que mantienen almacenada la información son creados por diferentes tipos de programas de aplicación existe la posibilidad de que si no se controla detalladamente el almacenamiento, se pueda originar un duplicado de información. Esto aumenta los costos de almacenamiento y acceso a los datos, además de que puede originar la inconsistencia de los datos es decir diversas copias de un mismo dato no concuerdan entre sí, por ejemplo, que se actualiza la dirección de un cliente en un archivo y que en otros archivos permanezca la anterior.


3

• Dificultad en el acceso a los datos. Un sistema de base de datos debe contemplar un entorno de datos que le facilite al usuario el manejo de los mismos. Supóngase un banco, y que uno de los gerentes necesita averiguar los nombres de todos los clientes que viven dentro del código postal dado de la ciudad. El gerente pide al departamento de procesamiento de datos que genere la lista correspondiente. Puesto que esta situación no fue prevista en el diseño del sistema, no existe ninguna aplicación de consulta que permita este tipo de solicitud, esto ocasiona una deficiencia del sistema.

• Aislamiento de los datos. Como los datos están dispersos en varios archivos, y estos pueden estar en diferentes formatos, es difícil escribir nuevos programas de aplicación para recuperar los datos correspondientes.

• Problemas de integridad. Los valores de datos almacenados en la base de datos deben satisfacer cierto tipo de restricciones de consistencia. Estas restricciones se hacen cumplir en el sistema añadiendo códigos apropiados en los diversos programas de aplicación. Por ejemplo, el saldo de ciertos tipos de cuentas bancarias no pueden nunca ser inferior a una cantidad predeterminada. Los desarrolladores hacen cumplir esas restricciones en el sistema añadiendo el código correspondiente en los diversos programas de aplicación. Sin embargo, cuando se añaden nuevas restricciones, es difícil cambiar los programas para hacer que se cumplan. El problema se complica cuando las restricciones implican diferentes elementos de datos de diferentes archivos.

• Problemas de atomicidad. Los sistemas informáticos, como cualquier otro dispositivo mecánico o eléctrico, esta sujeto a fallos. En mus aplicaciones es crucial asegurar que si se produce algún fallo, los datos se restauren al estado consistente que existía antes del fallo.

• Anomalías en el acceso concurrente. Para mejorar el funcionamiento global del sistema y obtener un tiempo de respuesta más rápido, muchos sistemas permiten que múltiples usuarios actualicen los datos simultáneamente. En un entorno así la interacción de actualizaciones concurrentes puede dar por resultado datos inconsistentes. Para prevenir esta posibilidad debe mantenerse alguna forma de supervisión en el sistema.

• Problemas de seguridad. No todos los usuarios de un sistema de base de datos deben poder acceder a todos los datos. Por ejemplo, en un sistema bancario, el personal de nóminas solo necesita ver la parte de datos que contiene información acerca de los diferentes empleados del banco. No necesita tener acceso a la información acerca de cuenta de clientes.


4

1.3. Usos y aplicaciones de las bases de datos.

Según (Elmasri / Navathe). Las bases de datos se usan ampliamente. Algunas de sus aplicaciones representativas son:

• Banca: para información de los clientes, cuentas, prestamos y transacciones vacarías.

• Líneas aéreas: para reservas e información de horarios. Las líneas aéreas fueron de las primeras en usar las bases de datos de forma distribuida geográficamente.

• Universidades: para información de los estudiantes, matriculas

en las asignaturas y cursos.

• Transacciones de tarjetas de crédito: para comprar tarjetas de crédito y la generación de los extractos mensuales.

• Telecomunicaciones: para guardar un registro de llamadas realizadas, generar las facturas mensuales, mantener el saldo de las tarjetas telefónicas de prepago y para almacenar información sobre las redes de comunicaciones.

• Finanzas: para almacenar información sobre compañías tenedoras, ventas y compras de productos financieros, como acciones y bonos; también para almacenar datos del mercado en tiempo real para permitir a los clientes la compraventa y a la compañía la compraventa automática.

• Ventas: para información de clientes, productos y compras.

• Comercio en línea: para los datos de ventas ya mencionados y para el seguimiento de los pedidos Web, generación de listas de recomendaciones y mantenimiento de evaluaciones de productos en línea.

• Producción: para la gestión de la cadena de proveedores y para el seguimiento de la producción de artículos en las factorías, inventarios en los almacenes y pedidos.

• Recursos humanos: para información sobre los empleados, salarios, impuestos sobre los sueldos y prestaciones sociales, y para la generación de las nóminas.

1.4. Arquitectura de base de datos.

1.4.1. Niveles de abstracción.


5

Según (Korth/Silberschatz). La arquitectura se divide en tres niveles generales: interno, conceptual y externo.

• Nivel interno: Tiene un esquema interno, que describe

la estructura física de almacenamiento de la base de datos. El esquema interno emplea un modelo físico de los datos y describe todos los detalles para su almacenamiento, así como los caminos de acceso para la base de datos.

• Nivel conceptual: Tiene un esquema conceptual, que describe la estructura de toda la base de datos para una comunidad de usuarios. El esquema conceptual oculta los detalles de las estructuras físicas de almacenamiento y se concentra en describir entidades, tipos de datos, vínculos, operaciones de los usuarios y restricciones. En este nivel podemos usar un modelo de datos de alto nivel o uno de implementación.

• Nivel externo o de vistas: Incluye varios esquemas externos o vistas de usuario. Cada esquema externo describe la parte de la base de datos que interesa a un grupo de usuarios determinado, y oculta a ese grupo el resto de la base de datos. En este nivel podemos usar un modelo de datos de alto nivel o uno de implementación.

1.4.2. Independencia lógica y física de los datos.

Según (Korth/Silberschatz). Independencia Física de datos: Es la capacidad para modificar el esquema físico sin provocar que los programas de aplicación tengan que rescribirse. Las modificaciones en el nivel físico son ocasionalmente necesarias para mejorar el funcionamiento.

Independencia Física de datos: Es la capacidad para modificar el esquema físico sin provocar que los programas de aplicación tengan que rescribirse. Las modificaciones en el nivel físico son ocasionalmente necesarias para mejorar el funcionamiento.

1.5. Estructura general de un sistema de base de datos.

1.5.1. El gestor de base de datos.

Según (Korth/Silberschatz). Un gestor de almacenamiento es un modulo de programa que proporciona la interfaz entre los datos de bajo nivel almacenados en la base de datos y los programas de aplicación y las consultas remitidas al sistema. El gestor de almacenamiento es responsable de la interacción con el gestor de archivos. Los datos en bruto se almacenan en el disco mediante el


6

sistema de archivos que suelen proporcionar un sistema operativo convencional.

El gestor de almacenamiento traduce las diferentes instrucciones LMD a comandos de bajo nivel del sistema de archivos. Así, el gestor el gestor de almacenamiento es responsable del almacenamiento, la recuperación y la actualización de los datos de la base de datos.

Entre los componentes del gestor de almacenamiento se encuentran:

• Gestor de autorizaciones e integridad. Que

comprueba que satisfagan las restricciones de integridad y la autoridad de los usuarios para tener acceso a los datos.

• Gestor de transacciones. Que garantiza que la base de datos quede en un estado consistente (correcto) a pesar de los fallos del sistema, y que la ejecución concurrente de transacciones transcurra sin conflictos.

• Gestor de archivos. Que gestiona la asignación de

espacios de almacenamiento de discos y las estructuras de datos usadas para representar la información almacenada en el disco.

• Gestor de memoria intermedia. Que es responsable de

traer los datos desde el disco de almacenamiento a la memoria principal y decidir los datos a guardar en la memoria cache. El gestor de la memoria intermedia es una parte fundamental de los sistemas de bases de datos, ya que permite que la base de datos maneje tamaño de datos que son mucho mayores que le tamaño de la memoria principal.

El gestor de almacenamiento implementa varias estructuras de datos como parte de la implementación física del sistema.

• Archivos de dato, que almacena la base de datos en si

misma.

• Diccionario de datos, que almacena meta datos acerca de la estructura de la base de datos; en particular su esquema.

• Índices, que puede proporcionar un acceso rápido a los elementos de datos. Los índices facilitan punteros a los elementos de datos que tiene un valor concreto.


7

1.5.2. Los usuarios de la base de datos.

Según (Korth/Silberschatz). Hay cuatro tipos diferentes de usuarios de los sistemas de bases de datos, diferenciados por la forma en que esperan interactuar con el sistema. Se han diseñado deferentes tipos de interfaces de usuario para los diferentes tipos de usuarios.

• Usuarios normales: son usuarios no sofisticados que

interactúan con el sistema mediante la invocación de alguno de los programas de aplicación permanentes que se ha escrito previamente. Por ejemplo, un cajero bancario que necesita transferir una cantidad de dinero De la cuenta A la cuenta B invoca un programa llamado transferir. Este programa pide al cajero el importe de dinero a transferir, la cuenta de la que el dinero va a ser transferido y la cuenta a la que el dinero va a ser transferido.

• Usuario programadores de aplicaciones: Son profesionales informáticos que escriben programas de aplicación. Los programadores de aplicaciones pueden elegir muchos programas de desarrollo rápido de aplicaciones (DRA) son herramientas que permiten al programador de aplicaciones crear formularios e informes con mínimo esfuerzo de programación.

• Usuarios sofisticados: Interactúan con el sistema sin programas escritos. En su lugar, ellos forman sus consultas en lenguajes de consulta de bases de datos. Remiten cada una de las consultas al procesador de consultas, cuya función es dividir las instrucciones LMD en instrucciones que el gestor del almacenamiento entienda.

• Usuarios especializados: Son usuarios sofisticados que escriben aplicaciones de bases de datos especializadas que no son adecuadas en el marco de procesamiento de datos tradicional. Entre estas aplicaciones están los sistemas de diseño asistido por computadora, sistemas de bases de conocimiento y experto, sistemas que almacenan los datos con los tipos de datos completos y sistemas de modelado del entorno.

1.5.3. El administrador de la base de datos (DBA).

Según (David M. / Kroenke). El administrador de base de datos (DBA) es la persona o equipo de personas profesionales responsables del control y manejo del sistema de base de datos. Las funciones del (DBA) incluyen:


8

• Definición del esquema. El DBA crea el esquema

original de la base de datos mediante la ejecución de un conjunto de instrucciones de definición de datos en el LDD.

• Definición de la estructura de almacenamiento del método de acceso. Estructuras de almacenamiento y de acceso adecuados se crean escribiendo un conjunto de definiciones que son traducidas por el compilador del lenguaje de almacenamiento y definición de datos.

• La modificación del esquema y la organización física. El DBA realiza modificaciones en el esquema y en la organización física para reflejar las necesidades cambiantes de la organización, o para alterar la organización física a fin de mejorar el rendimiento.

• Concesión de autorización para el acceso a los datos. Mediante la concesión de diferentes tipos de autorización, el administrador de bases de datos puede regular las partes de la base de datos a las que pueda tener acceso cada usuario. La información de autorización se guarda en una estructura especial del sistema que el SGBD consulta siempre que alguien intenta tener acceso a los datos del sistema.

• Mantenimiento rutinario. Algunos ejemplos de las actividades de mantenimiento rutinario del administrador de la base de datos son:

o Copia de seguridad periódica de la base de datos, bien sobre cinta o sobre servidores remotos, para impedir la perdida de datos en caso de desastres como las inundaciones.

o Asegurarse de que se dispone de suficiente espacio libre en disco para las operaciones normales y aumentar en el espacio en disco según sea necesario.

o Supervisa los trabajos que se ejecuten en la base de datos y asegurarse de que el rendimiento no se degrade debido a que algún usuario haya remitido tareas muy costosas.

1.5.4. El sistema de comunicación entre los distintos componentes (API’s, midleware’s, etc).

Según (David M. / Kroenke). El SGBD también debe interactuar con software de comunicaciones, cuya función es permitir que los


9

usuarios situados en lugares remotos respecto al sistema de datos tengan acceso a éste a través de terminales de computador, base estaciones de trabajo o sus microcomputadores o mini computadores locales. Éstos se conectan al sitio de la base de datos por medio de equipos de comunicación de datos: líneas telefónicas, redes de larga distancia o dispositivos de comunicación por satélite. Muchos sistemas comerciales de bases de datos tienen paquetes de comunicaciones que funcionan con el SGBD. El sistema integrado de SGBD y comunicación de datos se denomina sistema BD/DC (database/datacommunications).

Por añadidura, algunos SGBD distribuidos están físicamente dispersos en varias máquinas. En este caso, se requieren redes de comunicaciones para conectar las máquinas. Con frecuencia se trata de redes de área local (LAN: local área networks), pero también pueden ser de otro tipo. El término arquitectura cliente servidor se usa para caracterizar un SGBD cuando la aplicación se ejecuta físicamente en una máquina, llamada cliente, y otra, el servidor, se encarga del almacenamiento y el acceso de los datos. Los proveedores ofrecen diversas combinaciones de clientes y servidores.

1.6. Arquitectura cliente/servidor.

Según (David M. / Kroenke). La arquitectura Cliente/Servidor agrupa conjuntos de elementos que efectúan procesos distribuidos y computo cooperativo.

La arquitectura cliente/servidor es un modelo para el desarrollo de sistemas de información, en el que las transacciones se dividen en procesos independientes que cooperan entre sí para intercambiar información, servicios o recursos. Se denomina cliente al proceso que inicia el diálogo o solicita los recursos y servidor, al proceso que responde a las solicitudes. Es el modelo de interacción más común entre aplicaciones en una red.

Beneficios:

• Mejor aprovechamiento de la potencia de cómputo (Reparte el trabajo).

• Reduce el tráfico en la red. (Viajan requerimientos). • Opera bajo sistemas abiertos.


10

• Permite el uso de interfaces gráficas variadas y versátiles.

¿Qué es el Cliente?

Conjunto de Software y Hardware que invoca los servicios de uno o varios servidores. Los clientes interactúan con el usuario, usualmente en forma gráfica. Frecuentemente se comunican con procesos auxiliares que se encargan de establecer conexión con el servidor, enviar el pedido, recibir la respuesta, manejar las fallas y realizar actividades de sincronización y de seguridad.

Características:

• El cliente oculta al servidor.

• Detecta e intercepta peticiones de otras aplicaciones y puede redireccionarlas.

• Dedicado a la decisión del usuario (inicia…termina).

• El método mas común por el que se solicitan los servicios es a través de RPC (Remote procedure calls).

Funciones Comunes del cliente:

• Mantener y procesar todo el dialogo con el usuario.

• Manejo de pantallas.

• Menús e interpretación de comandos.

• Entrada de datos y validación.

• Procesamiento de ayudas.

• Recuperación de errores.

• Generación de consultas e informes sobre las bases de datos.

¿Qué es el Servidor?

Conjunto de Hardware y Software que responde a los requerimientos de un cliente. Los servidores proporcionan un servicio al cliente y devuelven los resultados. En algunos casos existen procesos auxiliares que se encargan de recibir las solicitudes del cliente, verificar la protección, activar un proceso servidor para satisfacer el pedido, recibir su respuesta y enviarla al cliente. Además, deben manejar los interbloqueos, la recuperación ante fallas, y otros aspectos afines. Por las razones anteriores, la


11

plataforma computacional asociada con los servidores es más poderosa que la de los clientes. Por esta razón se utilizan PCs poderosas, estaciones de trabajo, mini computadores o sistemas grandes. Además deben manejar servicios como administración de la red, mensajes, control y administración de la entrada al sistema ("login"), auditoria y recuperación y contabilidad.

Tipos Comunes de Servidores:

• Servidor de Archivos (FTP, Novell).

• Servidor de Bases de Datos (SQL, CBASE, ORACLE, INFORMIX).

• Servidor de Comunicaciones.

• Servidor de Impresión.

• Servidor de Terminal.

• Servidor de Aplicaciones (Windows NT, Novell).

Funciones Comunes del Servidor:

• Acceso, almacenamiento y organización de datos.

• Actualización de datos almacenados.

• Administración de recursos compartidos.

• Ejecución de toda la lógica para procesar una transacción.

• Procesamiento común de elementos del servidor (Datos, capacidad de CPU, almacenamiento en disco, capacidad de impresión, manejo de memoria y comunicación).

• Gestión de periféricos compartidos.

• Control de accesos concurrentes a bases de datos compartidas.

• Enlaces de comunicaciones con otras redes de área local o extensa.

1.6.1 Componentes de aplicaciones.

Según (David M. / Kroenke). Una infraestructura Cliente/Servidor consta de tres componentes esenciales, todos ellos de igual importancia y estrechamente ligados:

• Plataforma Operativa. • Entorno de Desarrollo de Aplicaciones.


12

• Gestión de Sistemas. • Funciones de componentes.

1 .6.2. Funciones de componentes.

• Plataforma Operativa. La plataforma deberá soportar todos los modelos de distribución Cliente/Servidor, todos los servicios de comunicación, y deberá utilizar, preferentemente, componentes estándar de la industria para los servicios de distribución. Los desarrollos propios deben coexistir con las aplicaciones estándar y su integración deberá ser imperceptible para el usuario. Igualmente, podrán acomodarse programas escritos utilizando diferentes tecnologías y herramientas.

• Entorno de Desarrollo de Aplicaciones. Debe elegirse después de la plataforma operativa. Un entorno de aplicación incremental, debe posibilitar la coexistencia de procesos cliente y servidor desarrollados con distintos lenguajes de programación y/o herramientas, así como utilizar distintas tecnologías (por ejemplo, lenguaje procedural, lenguaje orientado a objetos, multimedia), y que han sido puestas en explotación en distintos momentos del tiempo.

• Gestión de Sistemas. Estas funciones aumentan considerablemente el costo de una solución, pero no se pueden evitar. Siempre deben adaptarse a las necesidades de la organización, y al decidir la plataforma operativa y el entorno de desarrollo, es decir, en las primeras fases de la definición de la solución, merece la pena considerar los aspectos siguientes:

• ¿Qué necesitamos gestionar?

• ¿Dónde estarán situados los procesadores y estaciones de trabajo?

• ¿Cuántos tipos distintos se soportarán?

• ¿Qué tipo de soporte es necesario y quién lo proporciona?

1.7. Componentes de un gestor de bases de datos.

1.7.1 Estructura general.

Según (Korth y Silberschatz). Un sistema de bases de datos se divide en módulos que se encargan de cada una de las responsabilidades del sistema completo. Algunas de estas funciones del sistema de bases de datos las puede proporcionar el


13

sistema operativo de la computadora. En la mayoría de los casos los sistemas operativos de la computadora proporcionan solo los servicios más básicos y los sistemas de bases de datos deben construirse sobre esta base. Así, el diseño de un sistema de bases de datos debe incluir consideraciones de la interfaz entre el sistema de base de datos y el sistema operativo.

Los componentes funcionales de un sistema de bases de datos se pueden dividir a grandes rasgos en:

• Componentes de procesamiento de consultas.

• Componentes de gestión de almacenamiento.

1.7.2 Funciones.

Componentes de procesamiento de consultas: • Compilador de LMD: Traduce las instrucciones del

LMD en lenguaje de consultas a instrucciones a bajo nivel que entiende el motor de evaluación de consultas.

• Precompilador del LMD incorporado: Convierte las instrucciones de LMD incorporadas en un programa de aplicación en llamadas a procedimientos normales en el lenguaje anfitrión. El precompilador del LMD para generar el código apropiado.

• Interprete del LMD: Interpreta las instrucciones del LDD y las registra en un conjunto de tablas que contiene metadatos.

• Motor de evaluación de consultas: Ejecuta las instrucciones a bajo nivel generadas por el compilador del LMD.

Componentes de gestión de almacenamiento:

• Gestor de autorización e integridad: Comprueba que se satisfagan las ligaduras de integridad y la autorización de los usuarios para acceder a los datos.

• Gestor de transacciones: Asegura que la base de datos quede en un estado consistente a pesar de los fallos del sistema, y que las ejecuciones de transacciones concurrentes ocurran sin conflicto.

• Gestor de archivos: Gestiona la reserva de espacio de almacenamiento de disco, y las estructuras de


14

datos usadas para representar la información almacenada en disco.

• Gestor de memoria intermedia: Es responsable de traer los datos del disco de almacenamiento a memoria principal y decidir qué datos tratar en la memoria caché.

UNIDAD 2. MODELOS DE DATOS. Objetivo Conocerá las características principales de los modelos de datos. Seleccionará un modelo de datos para aplicarlo en el diseño de bases de datos.

2.1. Definición de modelos de datos.

Según (Kroenke). El modelo de datos es el proceso que implica crear una representación que tienen los usuarios de los datos si el modelo de


15

datos representa en forma incorrecta la visión que poseen los usuarios de los datos, encontrarán las aplicaciones difíciles de usar, incompletas y por supuesto en el desarrollo de las bases de datos y sus aplicaciones.

2.2. Modelo entidad relación.

Según. (Korth/Silberschatz). Este modelo de datos esta basado en una percepción del mundo real que consta de un conjunto de objetos básicos llamados entidades y de relaciones entre estos objetos.

2.2.1. Entidades, atributos y relaciones.

Entidad. Una entidad es una cosa u objeto en el mundo real que es distinguible de todo los demás objetos.

Una entidad puede ser concreta, como una persona o un libro, o puede ser abstracta, como un préstamo, unas vacaciones o n concepto. Ejemplo: Una persona (Korth/Silberschatz) Atributo. Son aquellos que describen propiedades que posee cada miembro de un conjunto de entidades. Cada atributo tiene un dominio. Relación. Es una asociación entre diferentes entidades.

2.2.2. Llaves. Según (asri/Navathe). Una llave candidata de una relación es un conjunto no vacío de atributos que identifican unívoca y mínimamente cada tupla. Por la propia definición de relación, siempre hay al menos una llave candidata, ya que al ser la relación un conjunto no existen tuplas repetidas y por tanto, el conjunto de todos los atributos identificará unívocamente a las


16

tuplas. Una relación puede tener más de una llave candidata, entre las cuales se debe distinguir:

• Llave primaria: es aquella llave candidata que el usuario escogerá, por consideraciones ajenas al modelo relacional, para identificar a las tuplas de una relación.

• Llave alternativa: son aquellas llaves candidatas que no an sido elegidas.

Se denomina llave ajena de una relación R2 a un conjunto no vacío de atributos cuyos valores han de coincidir con los valores de la llave primaria de otra relación R1. La llave ajena y la correspondiente llave primaria han de estar definidas sobre los mismos dominios.

2.2.3. Cardinalidad de las entidades en una relación.´

Según (Kroenke). Aunque en algunos diagramas E-R el nombre de la relación aparece dentro del diamante, esto hace que representación se vea desproporcionada. Para evitar esto, en ocasiones los nombres de relaciones se escriben arriba del diamante, cuando el nombre se coloca dentro o en la parte superior del diamante, la cardinalidad de la relación se detalla colocando patas de gallo en las líneas que conectan a la(s) entidad(es) en el lado muchos de la relación. La siguiente figura representa las relaciones DORMITORIO-OCUPANTE y ESTUDIANTE-CLUB con las mencionadas patas de gallo.

2.2.4. Dependencia de existencia y de identificación.

• Conjunto de entidades débiles. Un conjunto de entidades puede no tener suficientes atributos para formar una clave primaria. Tal conjunto de entidades se denomina conjunto de entidades débiles Korth/Silberschatz).

• Conjunto de entidades fuerte. Un conjunto de

entidades que tiene una clave primaria se denomina conjunto de entidades fuerte (Korth/Silberschatz)


17

2.2.5. Generalización y especialización. Según (Korth/Silberschatz).

• Especialización. Un conjunto de entidades puede incluir

subgrupos de entidades que se diferencian de alguna forma de las otras entidades del conjunto.

Por ejemplo, un subconjunto de entidades en un conjunto de entidades puede tener atributos que no son compartidos por todas las entidades del conjunto de entidades. El modelo E-R proporciona una forma de representación de estos grupos de entidades distintos.

• El proceso de designación de subgrupos dentro de un conjunto de entidades es la especialización.

• Un conjunto de entidades se puede especializar mediante más de una característica distintiva.

• Cuando se forma más de una especialización en un conjunto de entidades, una entidad particular puede pertenecer a ambas especializaciones.

En términos de un diagrama E-R, la especialización se representa mediante un componente triangular etiquetado La relación E-R se puede llamar también superclase relación de subclase. Los conjuntos de entidades de nivel más alto y mas bajo se representan como conjuntos de entidades regulares que contienen el nombre del conjunto de entidades.

• Generalización. Es una relación contenida que existe

entre el conjunto de entidades de nivel más alto y uno o más conjuntos de entidades de nivel mas bajo.

La generalización es una inversión simple de la especialización. La generalización procede el reconocimiento de un número de conjuntos de entidades que comparten algunas características comunes. Se usa para resaltar las similitudes entre los conjuntos de entidades en uno solo, el conjunto de entidades de nivel más alto.

2.2.6. Agregación. Según (Korth/Silberschatz). Una limitación del modelo E-R es que no es posible expresar relaciones entre relaciones.


18

La agregación es una abstracción a través de la cual las relaciones se tratan como entidades de nivel más alto. 2.2.7. Entidades recursivas. Según (Korth/Silberschatz). Una limitación del modelo E-R es que no es posible expresar relaciones entre relaciones. La agregación es una abstracción a través de la cual las relaciones se tratan como entidades de nivel más alto.

2.3. Modelo relacional. Según (Korth/Silberschatz). El modelo relacional se ha establecido como el primer modelo de datos para las aplicaciones de procesamiento de datos. Los primeros sistemas de bases de datos se basaban en el modelo de red o en el modelo jerárquico. Estos dos modelos antiguos se hallan mas ligados a la implementación subyacente de la base de datos que el modelo relacional.

2.3.1. Estructura del modelo relacional (atributo, dominio, tupla). Según (Korth/Silberschatz). Una base de datos relacional consiste en un conjunto de tabla, a cada una de las cuales se les asigna un nombre exclusivo. Cada fila de la tabla representa una relación entre un conjunto de valores. Dado que cada tabla es un conjunto de dichas relaciones, hay una fuerte correspondencia entre el concepto de tabla y el concepto matemático de relación. En la terminología del modelo relacional, una fila se denomina tupla, una cabecera de columna es el atributo y la tabla es una relación. El tipo de datos que puede aparecer en cada columna se llama dominio.

La siguiente tabla muestra la diferencia de representación con respecto a diagramas E-R y el modelo relacional.


19

2.3.2. Definición de relación. Según (Korth/Silberschatz). Matemáticamente, una relación se puede definir como un subconjunto del producto cartesiano de una lista de dominios, donde cada elemento de la relación, tupla, es una serie de n valores ordenados. En esta definición matemática de relación, al papel que tienen los dominios en la relación y, además, en ella el orden de los valores dentro o de una tupla es significativo. A fin de evitar estos inconvenientes, se puede dar otra definición de relación más adecuada al punto de vista de las bases de datos, para lo cual es preciso distinguir, dos conceptos en la noción de relación:

• Intensión o Esquema de relación, denotado R (Al:D1, 2:D2, ..., An:Dn) es un conjunto de n pares atributo- minio subyacente (Ai:Di). La intensión es la parte definitoria y estática de la relación, que se corresponde con la cabecera cuando la relación se percibe comouna tabla.

• Extensión u ocurrencia (instancia) de relación (llamada a veces simplemente relación), denotada por r(R) es un conjunto de m tuplas {t1, t2, ... tm} donde cada tupla es un conjunto de n pares atributo-valor.

Ejemplo: Intensión de una relación: AUTOR (NOMBRE:Nombres, NACIONALIDAD:Nacionalidades, INSTITUCION: Instituciones) Extensión de una relación:


20

2.3.3. Propiedades de una relación (grado, cardinalidad). Según (Korth/Silberschatz). Dependiendo del tipo de de interrelación jerárquica que se represente, y por tanto exista en el dominio del problema, los tipos de entidades que intervienen en el mismo participan o pueden participar con un numero determinado de ocurrencias. Así, se tiene que tener en cuenta las siguientes consideraciones en la representación de este tipo de interrelaciones.

• El tipo de entidad mas general o el supertipo de entidad que es especializado participa siempre con la cardinalidad mínima 1 y con la cardinalidad máxima 1, puesto que se esta representando para cada entidad de ese tipo puede especializarse en otro subtipo.

• Para cualquier clase de este tipo de interrelaciones

jerárquicas, la cardinalidad máxima con la que participan los subtipos de entidad en el tipo de interrelación es 1, puesto que se esta representando para cada entidad del supertipo una especialización o refinamiento de la misma.

• Si el tipo de interrelación es total o parcial sin solapamiento, los subtipos participan siempre con la cardinalidad mínima 0, puesto que una entidad del tipo no puede pertenecer al mismo tiempo a mas de un subtipo.

• Si el tipo de interrelación es total o parcial con

solapamiento, los subtipos pueden participar con la cardinalidad mínima 0, o 1, puesto que una entidad del supertipo puede a su vez ser especializada en cualquier de los subtipos simultáneamente.

UNIDAD 3. DISEÑO DA BASES DE DATOS RELACIONALES. Objetivo Aplicará los conocimientos y habilidades adquiridas, en el diseño de bases de datos relacionales.


21

3.1 Consideración de diseño.

Según (Korth/Silberschatz). Generar un conjunto de esquemas de relaciones que nos permitan almacenar información sin redundancia innecesaria, pero que a la vez nos permitan recuperar información fácilmente. Según (Korth/Silberschatz). Señalan tres peligros en el diseño de bases de datos relacionales los cuales son:

• Repetición de información. • Incapacidad para representar cierta información. • Pérdida de información

Es por eso que la repetición de información y reducción de valores redundantes en las tuplas tienen relación en cuanto a que uno de los objetivos en el diseño de esquemas es minimizar el espacio de almacenamiento que ocupan las relaciones base. La repetición de información desperdicia espacio. Además, la repetición de información complica la actualización de base de datos.

3.2 Normalización.

La normalización de datos puede considerarse como un proceso durante el cual los esquemas de relación insatisfactorios se descomponen repartiendo sus atributos entre esquemas de relaciones más pequeños que poseen propiedades deseables. Las formas normales proveen a los diseñadores de bases de datos lo siguiente:

• Un marco formal para analizar los esquemas de relación con

base en sus claves y en las dependencias funcionales entre sus atributos.

• Una serie de pruebas que pueden efectuarse sobre esquemas

de relación individuales de modo que la base de datos relacional pueda normalizarse hasta el grado deseado. Cuando una prueba falla, la relación que provoca el fallo debe descomponerse en relaciones que individualmente satisfagan las pruebas de normalización.

Los diseñadores de bases de datos no tienen que normalizar hasta la forma normal más alta posible. Las relaciones pueden dejarse en formas normales inferiores por razones de rendimiento.

3.2.1 Dependencias funcionales.


22

Una dependencia funcional es una relación entre uno o más atributos. Suponga que si se da el valor de un atributo se puede obtener o buscar el valor de otro. Si se conoce el valor de NúmeroDeCuentaDeCliente, se puede hallar el valor de EstadoDeCuentaDeCliente esto es cierto, se puede decir que EstadotDeCuentaDeCliente es funcionalmente dependiente de NúmeroDeCuentaDeCliente. En términos más generales, el atributo Y es dependiente del atributo X si el valor de X determina el valor de Y. O, puesto de otro modo, si se conoce el valor de X, se puede obtener el valor de Y. Las ecuaciones pueden representar dependencias funcionales. Si se sabe el precio de un artículo y la cantidad de artículos comprados, se puede calcular el precio total de esos artículos como sigue:

PrecioTotal = PrecioDelArtículo X Cantidad

En este caso se podría decir que PrecioTotal es dependiente del PrecioDelArticulo y Cantidad. A diferencia de una ecuación, tales dependencias funcionales no pueden trabajarse usando aritmética; en vez de ello se listan en una base de datos. La expresión de las dependencias funcionales es una de las razones para tener una base de datos. Las dependencias funcionales se escriben usando la siguiente notación:

La primera expresión se lee como “SID determina funcionalmente Especialidad”, “SID determina Especialidad” o “Especialidad es dependiente de SID”. Los atributos al lado izquierdo de la flecha se llaman determinantes (David M. Kroenke). Consideremos el esquema de la relación EMP_PROY; a partir de la semántica de los atributos, sabemos que deben cumplirse las siguientes dependencias funcionales:


23

Estas dependencias funcionales especifican que:

• Una combinación de valores de NSS y NÚMEROP determina de manera única el número de horas que el empleado trabaja en el proyecto cada semana (HORAS).

• El valor del número de seguro social de un empleado (NSS)

determinan de manera única el nombre de ese empleado (NOMBREE).

• El valor del número de un proyecto (NÚMEROP) determina

de manera única el nombre del proyecto (NOMBREPR) y su lugar (LUGARP).

3.2.2 Primeras formas normales.

3.2.2.1 1FN.

Una relación esta en 1FN si y solo si todos sus dominios simples subyacentes contienen solo valores atómicos (o indivisibles).

Consideremos el esquema de relación DEPARTAMENTO cuya clave primaria es NÚMEROD, y supongamos que la extendemos al incluir el atributo LUGARESD. Suponemos que cada departamento puede tener varios lugares. En la figura 2 se muestra el ejemplo de extensión. Es evidente que no está en 1FN porque LUGARESD no es un atributo atómico, como puede verse en la primera tupla.

Hay dos formas de considerar el atributo LUGARESD:


24

• El dominio de LUGARESD contiene valores atómicos, pero algunas tuplas pueden tener un conjunto de esos valores

• El dominio de LUFARESD contiene conjuntos de valores y por tanto no es atómico. En este caso, NSS->LUGARESD, porque cada conjunto se considera un miembro individual del dominio del atributo.

Una manera de normalizar a 1FN es tener una tupla en la relación DEPARTAMENTO original por cada ubicación de un departamento, como se muestra en la figura 3. En este caso, la clave primaria se convierte en la combinación {NÚMEROD, LUGARESD} pero hay redundancia en las tuplas.

La idea es eliminar el atributo LUGARESD que viola la 1FN y colocarlo en una relación aparte LUGARES_DEPTOS junto con la clave primaria NÚEROD de DEPARTAMENTO. La calve primaria de esta relación es la combinación {NÚMEROD, LUGARD}, como se aprecia en la figura 4. Hay una tupla distinta el LUGARES_DEPTOS por cada ubicación de un departamento. El atributo LUGARESD se quita de la relación DEPARTAMENTO de la figura 2, descomponiendo la relación que no es 1FN en las dos relaciones 1FN DEPARTAMENTO Y LUGARES_DEPTOS.


25

La segunda solución es mejor porque no padece el problema de redundancia. La primera forma normal también prohíbe los atributos compuestos que por sí mismos son multivaluados.

3.2.2.2 2FN.

Un esquema de relación esta en segunda forma normal 2FN si y solo si esta en 1 FN y todos los atributos no clave dependen funcionalmente de manera completa de la clave primaria. La relación EMP_PROY esta en 1FN pero no en 2FN. El atributo no clave NOMBREE viola 2FN debido a que no depende de manera completa de la clave primaria en la dependencia funcional 2 (df2), y lo mismo sucede con los atributos no clave NOMBREPR Y LUGARP debido a la df3. Las dependencias funcionales df2 y df3 hacen que NOMBRE, NOMBREPR Y LUGARP dependan parcialmente de la clave primaria {NSS, NÚMEROP} de la relación EMP_PROY, violándose así la 2FN. Si un esquema de relación no está en 2FN, se le puede normalizar a varias relaciones 2FN en las que los atributos no clave estén asociados sólo a la parte de la clave primaria de la que dependen funcionalmente de manera completa. Así, las dependencias funcionales df1, df2 y df3 de la figura 5(a) originan la descomposición de EMP_PROY en los tres esquemas de relación EP1, EP2 y EP3 que ilustra la figura 5(b), cada uno de los cuales está en 2FN.


26

3.2.2.3 3FN y FNBC (forma normal Óbice-Cood).

La tercera forma normal de basa en el concepto de dependencia transitiva. De acuerdo con la definición original de Codd, un esquema de relación R esta en 3FN si y solo si esta en 2FN y todos los atributos no clave dependen de manera no transitiva de la clave primaria.

La transitividad se da cuando un atributo no clave depende funcionalmente de un atributo que a su vez depende de la clave primaria.


27

Figura 6. El proceso de Normalización. (a) El esquema de relación EMP_DEPTO. (b) Normalización de EMP_DEPTO a relaciones 3FN.

La dependencia NSS->NSSGTED es transitiva a través de NÚMEROD de la relación EMP_DEPTO en la figura 6(a), porque se cumplen las dos dependencias NSS->NÚMEROD y NÚMEROD->NSSGTED y NÚMEROD no es un subconjunto de la clave de EMP_DEPTO. Podemos ver que en EMP_DEPTO no es deseable la dependencia de NSSGTED con respecto a NÚMEROD porque NÚMEROD no es una clave primaria de EMP_DEPTO. El esquema de relación EMP_DEPTO de la figura 6(a) está en 2FN, pues no existen dependencias parciales de una calve. Sin embargo, no está en 3FN debido a que NSSGTED (y también NOMBRED) dependen transitivamente de NSS a través de NÚMEROD. Podemos normalizar EMP_DEPTO descomponiéndolo en los dos esquemas de relación 3FN ED1 y ED2 que aparecen en la figura 6(b). Vemos, que ED1 y ED2 representan hechos independientes acerca de las entidades empleados y departamentos. Una operación de REUNION NATURAL aplicada a ED1 y ED2 recuperará la relación original EMP_DEPTO sin generar tuplas repetidas.


28

FNBC (Forma Normal Boyce-Codd)

Un esquema de relación está en FNBC si, y solo si todo determinante es una clave candidata.

3.2.3 Normalización adicional.

3.2.3.1 Dependencia multivaluada y 4FN.

Las dependencias multivaluadas son una consecuencia de la primera forma normal, que prohíbe que un atributo de una tupla tenga un conjunto de valores. Si tenemos dos o más atributos multivaluados independientes en el mismo esquema de relación, nos enfrentamos al problema de tener que repetir todos los valores de uno de los atributos con cada valor del otro atributo para que las tuplas de la relación sigan siendo consistentes. Ejemplo: Consideremos la relación EMPLEADOS que se muestra en la figura 7 (a). Una tupla de esta relación representa el hecho de que un empleado cuyo nombre es NOMBREE trabaja en el proyecto cuyo nombre es NOMBREPR y tiene un dependiente cuyo nombre es NOMBRED. Un empleado puede trabajar en varios proyectos y tener varios dependientes, y los proyectos y dependientes de un empleado no están relacionados directamente entre sí. Para que las tuplas de la relación sean consistentes, debemos tener una tupla por cada una de las posibles combinaciones de dependiente y proyecto de un empleado como se muestra en la figura 7 (b). Esta restricción se especifica como una dependencia multivaluada sobre la relación EMP.


29

Figura 7. Ventajas de la 4FN. (a) La relación empleados con dos Dependencias multivaluadas: NOMBREE ?NOMBREPR y NOMBREE?NOMBRED. (b) Descomposición de EMPLEADOS en dos relaciones: EMP-PROY y EMP-DEP, que están en 4FN.

3.2.3.2 Dependencia de juntura y 5FN.

Una descomposición posee la propiedad de reunión sin pérdidas. Sin embargo, en algunos casos puede ser que no exista una descomposición con reunión sin pérdidas que dé dos esquemas de relación, pero sí que produzca más de dos esquemas de relación. Estos casos se manejan con la dependencia de reunión y la quinta forma normal. Es


30

importante señalar que estos se presentan muy rara vez y que es difícil detectarlos en la practica. Una dependencia de reunión (DR) , denotada por DR(R1, R2, …, Rn), especificada sobre el esquema de relación R, especifica una restricción sobre los ejemplares r de R. La restricción establece que todo ejemplar permitido r de R debe tener una descomposición con reunión sin pérdidas para dar R1, R2, …, R; Una dependencia multivaluada es un caso especial de una DR donde n = 2. Una dependencia de reunión DR (R1, R2, …, Rn), especificada sobre el esquema de relación R, es una DR trivial si uno de los esquemas de relación Ri en DR (R1, R2, …,Rn) es igual a R. Se dice que tal dependencia es trivial porque posee la propiedad de reunión sin pérdidas para cualquier ejemplar de relación r de R y, por tanto, no especifica ninguna restricción sobre R. Ahora podemos especificar la Quinta Forma Normal , que también se denomina forma normal de proyección-reunión.

Quinta Forma Normal (5FN).

Un esquema R está en quinta forma normal (5FN) o forma normal de proyección-reunión (FNPR) respecto a un conjunto F de dependencias funcionales, multivaluadas y de reunión si, para cada dependencia de reunión no trivial DR (R1, R2, …, Rn) en F+ (esto es, implicada por F), toda Ri es una superclave de R.

3.3 Integridad de bases de datos.

3.3.1 Concepto.

Según (Korth/Silberschatz). La integridad Proporciona un medio de asegurar que los cambios que se hacen en la base de datos por usuarios autorizados no resultan en una pérdida de consistencia de los datos. 3.3.2 Restricción básica (not null, llave primaria, orden, verificación y aserción). Restricción de Valores Nulos.

Según (Korth/Silberschatz). Para determinado atributos, los valores nulos pueden ser inapropiados. Considérese una tupla en la relación cliente la que nombre-cliente es un valor vació. Una tupla de este tipo da una calle y una ciudad para un cliente anónimo y, por tanto, no contiene información útil. En casos como


31

éste, deseamos prohibir los valores nulos, restringiendo el dominio de ciudad-cliente para que excluya los valores nulos.

El SQL estándar permite que la declaración del dominio de un atributo incluya la especificación not nul. Esto prohíbe la inserción de un valor nulo para este atributo. Cualquier modificación de la base de datos que causara que se insertase un valor nulo en un dominio not null genera un diagnóstico de error. Hay muchas situaciones en las que la prohibición de valores nulos es deseable. Un caso particular en el que es esencial prohibir los valores nulos es en la clave primaria de un esquema de relación. Restricción de aserción.

Una Técnica más formal para representar restricciones explícitas es con un lenguaje de especificación de restricciones, que suele basarse en alguna variación del cálculo relacional. Este enfoque declarativo establece una separación clara entre la base de restricciones (en la que las restricciones se almacenan en una forma codificada apropiada) y el subsistema de control de integridad del SGBD (que tiene acceso a la base de restricciones para aplicar estas últimas correctamente a las transacciones afectadas). Cuando se usa esta técnica, las restricciones suelen llamarse aserciones. Se ha sugerido el uso de esta estrategia con SGBD relaciónales. El subsistema de control de integridad compila las aserciones, que entonces se almacenan en el catalogo del SGBD, donde el subsistema de control de integridad puede consultarlas e imponerlas automáticamente. Esta estrategia es muy atractiva desde el punto de vista de los usuarios y programadores por su flexibilidad. Restricciones de dominio. Los límites de dominios son la forma más elemental de restricciones de integridad. Son fáciles de probar por el sistema siempre que se introduce un nuevo dato en la base de datos. Es posible que varios atributos tengan el mismo dominio. Por ejemplo, los atributos nombre-cliente y nombre empleado podrían tener el mismo dominio, el conjunto de todos los nombres de personas. Sin embargo los dominios de saldo y nombre-sucursal, por supuesto, deben ser distintos. En el nivel de implementación, los nombres de cliente y los nombres de sucursal son cadenas de caracteres. Podemos ver que una definición adecuada de restricciones de dominio no sólo nos permite probar valores insertados en la base de datos sino que también nos permite


32

probar consultas para asegurar que la comparación que se hace tiene sentido. Restricción de clave. Según (Elmasri/Navathe). Es una de las restricciones estándar que con frecuencia aparecen en las aplicaciones de bases de datos. Estas restricciones se manejan de formas ligeramente distintas en los diversos modelos de datos. En el modelo E-R, una clave es un atributo de un tipo de entidades que debe tener un valor único para cada entidad que pertenezca a dicho tipo en cualquier momento específico. Así el valor del atributo clave puede servir para identificar de manera única cada entidad. Los atributos claves deben ser monovaluados, pero pueden ser simples o compuestos. Un tipo de entidades normal puede tener una o más claves; un tipo de entidades débil no tiene clave, pero casi siempre tiene una clave parcial cuyos valores identifican de manera única las entidades débiles que están relacionadas a la misma entidad propietario a través de un vínculo identificador. En general, un esquema de relación pude tener más de una clave. En tal caso, cada una de ellas se denominan clave candidata. Por ejemplo en una relación COCHE tiene dos claves candidatas: NumMatrícula y NumSerieMotor. Es común designar a una de las claves candidata como clave primaria de la relación. Ésta es la clave candidata cuyos valores sirven para identificar las tuplas en la relación.

3.3.3 Integridad de entidad. Según (Elmasri/Navathe). La integridad de entidad define una fila como entidad única para una tabla determinada. La integridad de entidad fuerza la integridad de la columna o columnas de los identificadores o la clave principal de una tabla (mediante índices, restricciones UNIQUE, restricciones PRIMARY KEY o propiedades DENTITY). Create Table Cliente( IdCliente Int Not Null Identity(1,1), NomCliente Varchar(60), DimCliente varchar(60) RFCliente char(15), CONSTRAINT unRFCCliente UNIQUE NONCLUSTERED (RFCCliente), Constraint pkCte Primary Key (idCliente) )


33

Para eliminar un CONSTRAINT ALTER TABLE Cliente DROP CONSTRAINT unRFCCliente El siguiente ejemplo crea un índice único ya estando creada la tabla. CREATE UNIQUE NONCLUSTERED INDEX unRFCCliente ON Cliente (RFCCliente) Para eliminar un índice DROP INDEX Cliente.unRFCCliente El siguiente ejemplo crea un índice único ya estando creada la tabla. ALTER TABLE Cliente ADD CONSTRAINT un RFCCliente UNIQUE NONCLUSTERED (RFCCliente) Para eliminar un Constraint con ALTER TABLE ALTER TABLE Cliente DROP CONSTRAINT unRFCCliente

3.3.4 Integridad referencial. Según (Elmasri/Navathe). La integridad referencial protege las relaciones definidas entre las tablas cuando se crean o se eliminan registros. En Microsoft SQL Server 2000, la integridad referencial se basa en las relaciones entre las llaves foráneas y la llave primaria o entre las claves externas y las claves únicas. La integridad referencial garantiza que los valores claves son coherentes en las distintas tablas. Parar conseguir esa coherencia, es preciso que no haya referencia a valores inexistentes y que, si cambia el valor de una clave, toda las referencias a ella se cambien en consecuencia en toda la base de datos. Cuando se fuerza la integridad referencial, SQL Server impide o los usuarios.

• Agregar registros a una tabla relacionada si no hay ningún registro asociado en la tabla principal.

• Cambiar valores en una tabla principal de manera

que queden registros huérfanos en una tabla relacionada.

• Eliminar registros de una tabla principal cuando hay

registros relacionados coincidentes. Por ejemplo, con las tablas negocio y producto de la base de datos facturación, la integridad referencial se basa en la relación entre la


34

clve externa (NegocioPK) de la tabla producto y la clave principal (NegocioPK) de la tabla Negocio.

Create Table Venta( IdVenta int not null, FecVenta smalldatetime, IdCliente int not null, Constraint pkVta Primary Key (idVenta), Constraint FkCte1 Foreign Key (idCliente) References Cliente(idCliente) )

3.3.5 Integridad de Dominio La integridad de dominio viene dada por la validez de las entradas para una columna determinada. Puede forzar la integridad de dominio si restringe el tipo (mediante tipos de datos), el formato (mediante las reglas RULE y las restricciones CHECK), o el intervalo de valores posibles (mediante restricciones FOREIGN KEY, restricciones CHECK, definiciones DEFAULT, definiciones NOT NULL y reglas). Este ejemplo muestra una restricción con una restricción de modelo sobre los datos de caracteres introducidos en la columna RFCNegocio de la tabla Negocio al momento de su creación. CONSTRAINT ckRFCNegocio CHECK (RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z]' OR RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9][0-9][0-9]-[A-Z][A-Z][AZ]') Y enseguida ya estando creada la tabla de negocio ALTER TABLE Negocio WITH NOCHECK


35

ADD CONSTRAINT ckRFC CHECK (RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z]' OR RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9][0-9][0-9]-[A-Z][A-Z][AZ]') El ejemplo siguiente agrega una restricción a una columna existente de la tabla. La columna tiene un valor que infringe la restricción; por tanto, se utiliza WITH NOCHECK para impedir que la restricción se valide contra las filas existentes y para permitir que se agregue la restricción. ALTER TABLE Producto WITH NOCHECK ADD CONSTRAINT ckMinimo CHECK (Minimo > 0) Para eliminar este CONSTRAINT ALTER TABLE Producto DROP CONSTRAINT ckMinimo Regla (RULE). Objeto de base de datos enlazado a una columna o tipo de datos definido por el usuario que especifica los datos que se pueden escribir en dicha columna. Cada vez que un usuario escribe o modifica un valor (con una instrucción INSERT o UPDATE), SQL Server lo comprueba contra la regla más reciente enlazada a la columna especificada, por ejemplo, para comprobar los límites o listas. Los datos escritos antes de la creación y enlace de una regla no se comprueban. Cuando se crea un objeto llamado Regla. Cuando se enlaza una regla a una columna o un tipo de datos definido por el usuario, especifica los valores aceptables que se pueden insertar en esa columna. Las reglas, que son una característica de compatibilidad con versiones anteriores, realizan algunas de las mismas funciones que las restricciones de comprobación. Las restricciones CHECK, creadas mediante la palabra clave CHECK de ALTER o CREATE TABLE, son la forma preferida y estándar de restringir los valores de una columna (se pueden definir restricciones múltiples en una o múltiples columnas). Una columna o tipo de datos definido por el usuario sólo puede tener una regla enlazada. Sin embargo, una columna puede tener una regla y una o más restricciones de comprobación asociadas con ella. Cuando esto es así, se evalúan todas las restricciones. Sintaxis CREATE RULE regla AS expresiónCondición Argumentos Regla


36

Es el nombre de la nueva regla. Los nombres de las reglas deben cumplir las reglas de los identificadores. Especificar el nombre del propietario de la regla es opcional. Expresión Condición Son las condiciones que definen la regla. Una regla puede ser cualquier expresión que sea válida en una cláusula WHERE y puede incluir elementos como operadores aritméticos, operadores relacionales y predicados (por ejemplo, IN, LIKE, BETWEEN). Una regla no puede hacer referencia a columnas u otros objetos de base de datos. Se pueden incluir funciones integradas que no hagan referencia a objetos de base de datos. Expresión Condición Incluye una variable. El signo (@) precede a cada variable local. La expresión hace referencia al valor que se introduce con la instrucción UPDATE o INSERT. Se puede utilizar cualquier nombre o símbolo para representar el valor cuando se crea la regla, pero el primer carácter debe ser @. Observaciones La instrucción CREATE RULE no puede combinarse con otras instrucciones Transact-SQL en un único proceso por lotes. Las reglas no se aplican a los datos ya existentes en la base de datos en el momento en que se crearon las reglas y no se pueden enlazar a los tipos de datos del sistema. Una regla sólo se puede crear en la base de datos actual. Una vez creada la regla, ejecute sp_bindrule para enlazarla a una columna o a un tipo de datos definido por el usuario. La regla debe ser compatible con el tipo de datos de la columna. Una regla no se puede enlazar a una columna text, image o timestamp. Asegúrese de incluir las constantes de fecha y de caracteres entre comillas simples (‘) y de preceder las constantes binarias con 0x. Por ejemplo, “@valor LIKE A%” no se puede utilizar como regla para una columna numérica. Si la regla no es compatible con la columna a la que se ha enlazado, Microsoft SQL Server devuelve un mensaje de error cuando se inserte un valor (no cuando se enlaza la regla). Una regla enlazada a un tipo de datos definido por el usuario sólo se activa cuando se intenta insertar un valor o actualizar una columna de la base de datos del tipo de datos definido por el usuario. Como las reglas no prueban las variables, no debe asignar un valor a una variable de tipo de datos definido por el usuario que sería rechazada por una regla enlazada a una columna del mismo tipo de datos. Para obtener un informe de una regla, utilice sp_help. Para mostrar el texto de una regla, ejecute sp_helptext con el nombre


37

de la regla como parámetro. Para cambiar el nombre de una regla, utilice sp_rename. Una regla debe quitarse (mediante DROP RULE) antes de crear otra con el mismo nombre y debe cancelarse el enlace (mediante sp_unbindrule) antes de quitarla. Utilice sp_unbindrule para cancelar el enlace de una regla a una columna. Una nueva regla se puede enlazar a una columna o tipo de datos sin cancelar el enlace de la anterior; la nueva regla anula la anterior. Las reglas enlazadas a columnas tienen siempre preferencia sobre las reglas enlazadas a tipos de datos definidos por el usuario. Enlazar una regla a una columna substituye una regla ya enlazada al tipo de datos definido por el usuario de esa columna. Pero el enlace de una regla a un tipo de datos no substituye una regla enlazada a una columna de ese tipo de datos definido por el usuario. Si una columna tiene un valor predeterminado y una regla asociada a ella, el valor predeterminado debe encontrarse en el dominio definido por la regla. Un valor predeterminado que esté en conflicto con una regla no se inserta nunca. SQL Server genera un mensaje de error cada vez que intenta insertar tal valor predeterminado. Ejemplos Regla con una lista. Este ejemplo crea una regla que restringe los valores actuales que se escriben en las columnas a las que la regla está enlazada, a sólo aquellos enumerados en la regla. CREATE RULE ruLista AS @list IN ('1389', '0736', '0877') Regla con un modelo. Este ejemplo crea una regla que sigue un modelo de dos caracteres cualquiera con un guión a continuación, cualquier número de caracteres (o no caracteres) y un entero entre 0 y 9 al final. CREATE RULE ruPatron AS @value LIKE '_ _-%[0-9]' Regla con un intervalo. Este ejemplo crea una regla que restringe el intervalo de enteros que se insertan en las columnas a las que la regla está enlazada. CREATE RULE ruRango AS @range >= 1000 AND @range < 5000 Para ligar una regla a una columna de la base de datos:


38

EXEC sp_bindrule 'ruRango', 'Producto.Maximo' Para des ligar una regla: EXEC sp_unbindrule 'Producto.Maximo' Para Borrar una Regla: DROP RULE Durango 3.3.6 Integridad Definida por el Usuario La integridad definida por el usuario le permite definir reglas de la compañía específicas que no pertenecen a ninguna otra categoría de integridad. Todas las categorías de integridad son compatibles con la integridad definida por el usuario. Desencadenadores (TRIGGERS) Los desencadenadores de Microsoft SQL Server 2000 son una clase especial de procedimiento almacenado que se define para que se ejecute automáticamente cuando se emita una instrucción UPDATE, INSERT o DELETE en una tabla o una vista. Los desencadenadores son una herramienta eficaz que pueden utilizar los sitios para exigir automáticamente las reglas comerciales cuando se modifican los datos. Los desencadenadores amplían la lógica de comprobación de integridad de las restricciones, valores predeterminados y reglas de SQL Server, aunque se deben utilizar las restricciones y los valores predeterminados siempre que éstos aporten toda la funcionalidad necesaria. Las tablas pueden tener varios desencadenadores. La instrucción CREATE TRIGGER se puede definir con las cláusulas FOR UPDATE, FOR INSERT o FOR DELETE, para asignar un desencadenador a una acción específica de modificación de datos. Cuando se especifica FOR UPDATE, se puede utilizar la cláusula IF UPDATE (column_name) para asignar el desencadenador a las actualizaciones que afecten a una columna concreta. Los desencadenadores pueden automatizar el procesamiento de una empresa. En un sistema de inventario, los desencadenadores de actualización pueden detectar cuándo se alcanza el nivel mínimo de existencias y generar automáticamente un pedido al proveedor. En una base de datos que registre los procesos de una fábrica, los desencadenadores pueden enviar mensajes a los operadores, por correo electrónico o mediante servicios de localización, cuando un proceso sobrepase los límites de seguridad definidos.


39

El siguiente desencadenador genera un mensaje de Error cuando se borra un Negocio y éste aún tiene clientes asignados en la tabla de Cliente: CREATE TRIGGER trDelNegocio ON Negocio FOR Delete AS If (Select Coint(*) From Inserted A Inner Join Cliente on (A.NegocioPk= BNegocioPk)) > 0 Begin

RAISERROR ('No Puede Borrar el Negocio por tener Clientes', 16, 1) RollBack Transaction

End Los desencadenadores contienen instrucciones Transact-SQL, como los procedimientos almacenados. Los desencadenadores, como los procedimientos almacenados, devuelven el conjunto de resultados generado por las instrucciones SELECT del desencadenador. No se recomienda que incluya instrucciones SELECT en los desencadenadores, excepto en las instrucciones que sólo rellenen parámetros. Esto se debe a que los usuarios no esperan que las instrucciones UPDATE, INSERT o DELETE devuelvan ningún conjunto de resultados. Puede utilizar la cláusula FOR para especificar cuándo se tiene que ejecutar un desencadenador:

• AFTER (después de)

Los desencadenadores se ejecutan tras la finalización de la instrucción que los desencadena. Si la instrucción termina con un error, por ejemplo, la infracción de una restricción o un error de sintaxis, el desencadenador no se ejecuta. No es posible especificar desencadenadores AFTER para vistas, sólo se pueden especificar para tablas. Puede especificar varios desencadenadores AFTER para cada acción de desencadenamiento (INSERT, UPDATE o DELETE). Si tiene varios desencadenadores AFTER para una tabla, puede utilizar sp_settriggerorder para definir qué desencadenador AFTER se activa primero y cuál al final. El resto de los desencadenadores AFTER, excepto el primero y el último, se activan en un orden indefinido que no puede controlar. AFTER es el valor predeterminado en SQL Server 2000. No se puede especificar AFTER o INSTEAD OF en SQL Server versión 7.0 o anterior; todos los desencadenadores de esas versiones funcionan como desencadenadores AFTER.


40

• INSTEAD OF (en lugar de)

Se ejecuta el desencadenador en lugar de la acción desencadenadora. Es posible especificar desencadenadores INSTEAD OF en tablas y en vistas. Puede especificar sólo un desencadenador INSTEAD OF para cada acción desencadenadora (INSERT, UPDATE o DELETE). Es posible utilizar desencadenadores INSTEAD OF para realizar comprobaciones de integridad en los valores de datos especificados en instrucciones INSERT y UPDATE. Los desencadenadores INSTEAD OF también le permiten especificar acciones que posibilitan la actualización de las vistas, que normalmente no aceptan actualizaciones.

Exigir reglas de empresa con desencadenadores Los dos principales mecanismos que ofrece Microsoft SQL Server 2000 para exigir las reglas de empresa y la integridad de datos son: las restricciones y los desencadenadores. Un desencadenador es un procedimiento almacenado de tipo especial que actúa automáticamente cuando se modifican los datos de la tabla. Los desencadenadores se invocan en respuesta a las instrucciones INSERT, UPDATE y DELETE. Un desencadenador puede consultar otras tablas e incluir instrucciones de Transact-SQL complejas. El desencadenador y la instrucción que lo activa se tratan como una sola transacción que puede deshacerse desde el desencadenador. Si se detecta un error grave (por ejemplo, no hay suficiente espacio en disco), se deshace automáticamente toda la transacción.

Los desencadenadores tienen varias utilidades:

• Los desencadenadores pueden realizar cambios en

cascada por medio de tablas relacionadas de la base de datos; sin embargo, estos cambios pueden ejecutarse de manera más eficaz mediante restricciones de integridad referencial en cascada.

• Los desencadenadores pueden exigir restricciones

más complejas que las restricciones CHECK. A diferencia de éstas, los desencadenadores pueden hacer referencia a columnas de otras tablas. Por ejemplo, un desencadenador puede utilizar una


41

instrucción SELECT de otra tabla para comparar con los datos insertados o actualizados y para realizar acciones adicionales, como modificar os datos o mostrar un mensaje de error definido por el usuario.

• Los desencadenadores también pueden evaluar el estado de una tabla antes y después de realizar una modificación de datos y actuar en función de la diferencia.

• Varios desencadenadores del mismo tipo (INSERT,

UPDATE o DELETE) en una tabla permiten realizar distintas acciones en respuesta a una misma instrucción de modificación.

Comparación de los desencadenadores y las restricciones Tanto las restricciones como los desencadenadores ofrecen ventajas específicas que resultan útiles en determinadas situaciones. La principal ventaja de los desencadenadores consiste en que pueden contener una lógica de proceso compleja que utilice código de Transact-SQL. Por tanto, los desencadenadores permiten toda la funcionalidad de las restricciones; sin embargo, no son siempre el mejor método para realizar una determinada función. La integridad de entidad debe ser siempre exigida en el nivel más bajo por índices que formen parte de restricciones PRIMARY KEY y UNIQUE, o que se creen independientemente de las restricciones. La integridad de dominio debe ser exigida mediante restricciones CHECK, y la integridad referencial (RI) mediante restricciones FOREIGN KEY, siempre que sus características satisfagan las necesidades funcionales de la aplicación. Los desencadenadores resultan de especial utilidad allí donde las características de las restricciones no cubren las necesidades funcionales de la aplicación. Por ejemplo:

• Las restricciones FOREIGN KEY pueden validar un valor de columna sólo si coinciden exactamente con un valor de otra columna, a menos que la cláusula REFERENCES defina una acción referencial en cascada.


42

• Una restricción CHECK puede validar el valor de una columna únicamente con relación a una expresión lógica o a otra columna de la misma tabla. Si la aplicación necesita que se valide el valor de una columna con relación a una columna de otra tabla, deberá utilizar un desencadenador.

• Las restricciones sólo pueden comunicar la

existencia de errores mediante mensajes de error estándar del sistema. Si la aplicación necesita (o puede aprovechar) mensajes personalizados y un tratamiento de errores más complejo, deberá utilizar un desencadenador.

Los desencadenadores pueden realizar cambios en cascada por medio de tablas relacionadas de la base de datos; sin embargo, estos cambios pueden ejecutarse de manera más eficiente a través de restricciones de integridad referencial en cascada.

• Los desencadenadores pueden no permitir o

deshacer los cambios que infrinjan la integridad referencial y cancelar, de ese modo, cualquier intento de modificación de los datos. Ese tipo de desencadenador puede actuar cuando se cambia una clave externa y el nuevo valor no coincide con su clave principal. Por ejemplo, puede crear un desencadenador de inserción en DetalleFactura.ProductoPk que deshaga la inserción si el nuevo valor no coincide con ningún valor de Producto.ProductoPk. (No obstante, para estos casos suelen utilizarse restricciones FOREIGN KEY.)

• Si hay restricciones en la tabla del desencadenador,

se comprobarán después de la ejecución del desencadenador INSTEAD OF pero antes de la ejecución del desencadenador AFTER. Si se infringen las restricciones, se desharán las acciones del desencadenador INSTEAD OF y no se ejecutará el desencadenador AFTER.

CREATE TRIGGER Crea un desencadenador, que es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando un usuario intenta la instrucción especificada de modificación de datos en la tabla indicada. Microsoft SQL Server permite crear varios desencadenadores para cualquier instrucción INSERT, UPDATE o DELETE.


43

Sintaxis

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] {

{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE] }

AS instrucciónSQL [...n]

Argumentos

trigger_name Es el nombre del desencadenador. Un nombre de desencadenador debe cumplir las reglas de los identificadores y debe ser único en la base de datos. Especificar el propietario del desencadenador es opcional. Table | view Es la tabla o vista en que se ejecuta el desencadenador; algunas veces se denomina tabla del desencadenador o vista del desencadenador. Especificar el nombre del propietario de la tabla o vista es opcional. WITH ENCRYPTION Codifica las entradas syscomments que contienen el texto de CREATE TRIGGER. El uso de WITH ENCRYPTION impide que el desencadenador se publique como parte de la duplicación de SQL Server. AFTER Especifica que el desencadenador sólo se activa cuando todas las operaciones especificadas en la instrucción SQL desencadenadora se han ejecutado correctamente. Además, todas las acciones referenciales en cascada y las comprobaciones de restricciones deben ser correctas para que este desencadenador se ejecute. AFTER es el valor predeterminado, si sólo se especifica la palabra clave FOR. Los desencadenadores AFTER no se pueden definir en las vistas. INSTEAD OF Especifica que se ejecuta el desencadenador en vez de la instrucción SQL desencadenadora, por lo que se suplantan las acciones de las instrucciones desencadenadoras. Como máximo, se puede definir un desencadenador INSTEAD OF por cada instrucción INSERT, UPDATE o DELETE en cada tabla o


44

vista. No obstante, en las vistas es posible definir otras vistas que tengan su propio desencadenador INSTEAD OF. Los desencadenadores INSTEAD OF no se permiten en las vistas actualizables WITH CHECK OPTION. SQL Server emitirá un error si se agrega un desencadenador INSTEAD OF a una vista actualizable donde se ha especificado WITH CHECK OPTION. El usuario debe quitar esta opción mediante ALTER VIEW antes de definir el desencadenador INSTEAD OF. { [DELETE] [,] [INSERT] [,] [UPDATE] } Son palabras clave que especifican qué instrucciones de modificación de datos activan el desencadenador cuando se intentan en esta tabla o vista. Se debe especificar al menos una opción. En la definición del desencadenador se permite cualquier combinación de éstas, en cualquier orden. Si especifica más de una opción, sepárelas con comas. Para los desencadenadores INSTEAD OF, no se permite la opción DELETE en tablas que tengan una relación de integridad referencial que especifica una acción. ON DELETE en cascada. Igualmente, no se permite la opción UPDATE en tablas que tengan una relación de integridad referencial que especifica una acción ON UPDATE en cascada. AS Son las acciones que va a llevar a cabo el desencadenador.

sql_statement Son las condiciones y acciones del desencadenador. Las condiciones del desencadenador especifican los criterios adicionales que determinan si los intentos de las instrucciones DELETE, INSERT o UPDATE hacen que se lleven a cabo las acciones del desencadenador. Las acciones del desencadenador especificadas en las instrucciones Transact- SQL entran en efecto cuando se intenta la operación DELETE, INSERT o UPDATE. Los desencadenadores pueden incluir cualquier número y clase de instrucciones Transact-SQL. Un desencadenador está diseñado para comprobar o cambiar los datos en base a una instrucción de modificación de datos; no debe devolver datos al usuario. Las instrucciones Transact-SQL de un desencadenador incluyen a menudo lenguaje de control de flujo. En las instrucciones CREATE TRIGGER se utilizan unas cuantas tablas especiales:

• deleted e inserted son tablas lógicas (conceptuales). Son de estructura similar a la tabla en que se define el desencadenador, es decir, la tabla enque se


45

intenta la acción del usuario, y guarda los valores antiguos o nuevos de las filas que la acción del usuario puede cambiar. Por ejemplo, pararecuperar todos los valores de la tabla deleted, utilice:

SELECT * FROM deleted

Ejemplo: Manda borrar en cascada todo el detalle de una factura al eliminar esta CREATE TRIGGER trDelFactura ON Factura FOR DELETE AS DELETE FROM Detallefactura WHERE ConsecPk In (SELECT ConsecPk FROM DELETED WHERE DELETED.ConsecPk = DetalleFactura.ConsecPk) Ejemplo. Manda un mensaje cuando se agrega o modifica un producto. El mensaje 50009 es un mensaje definido por el usuario en sysmessages. CREATE TRIGGER InsUpdProducto ON Producto FOR INSERT, UPDATE AS RAISERROR (50009, 16, 10) Ejemplo. Por medio de este trigger se hace un respaldo del producto modificado en la tabla ProductoAux CREATE TRIGGER updProducto ON Producto FOR UPDATE AS SAVE TRANSACTION MiTransacc INSERT INTO ProductoAux SELECT * FROM Deleted IF (@@error <> 0) BEGIN ROLLBACK TRANSACTION MiTransacc END Ver los triggers que tiene una determinada tabla Devuelve el tipo o los tipos de desencadenadores definidos en la tabla especificada de la base de datos actual. sp_helptrigger 'Factura'


46

Para borrar un trigger DROP TRIGGER trDelFactura Para ver el código de un trigger Sp_helptext trDelFactura 3.3.7 Reglas de relación. Según (Elmasri/Navathe).

• Orden de las tuplas en una relación: una relación se define como un conjunto de tuplas matemáticamente, los elementos de un conjunto no están ordenados; por tanto, las tuplas de una relación no tienen orden específico.

• El ordenamiento de las tuplas no forma parte de la

definición de una relación, porque la relación intenta representar los hechos en un nivel lógico abstracto.

• Orden de los valores dentro de una tupla, y definición

alternativa de relación: Una tupla es una lista ordenada de n valores, así que el orden de los valores de una tupla y por tanto de los atributos en la definición de un esquema de relación es importante. No obstante, en un nivel lógico, el orden de los atributos y de sus valores en realidad no es importante en tanto se mantengas la correspondencia entre atributos y valores.

• Valores en las tuplas: Cada valor en una tupla es un

valor atómico; esto es, no es divisible en componentes en lo que respecta al modelo relacional. Por ello no se permiten valores compuestos ni multivaluados.

3.3.8 Reglas de bases de datos. Según (Elmasri/Navathe).

• Una base de datos representa algún aspecto del mundo real, en ocasiones llamado mini mundo o universo de discurso. Las modificaciones del mini mundo se reflejan en la base de datos.

• Una base de datos es un conjunto de datos lógicamente coherente, con cierto significado inherente. Una colección


47

aleatoria de datos no puede considerarse propiamente una base de datos.

• Toda base de datos se diseña, construye y prueba con datos para un propósito específico. Esta dirigida a un grupo de usuarios y tiene ciertas aplicaciones preconcebidas que interesan a dichos usuarios.

En otras palabras, una base de datos tiene una fuente de la cual se derivan los datos, cierto grado de interacción con los acontecimientos del mundo real y un público que está activamente interesado en el contenido de la base de datos.

3.3.9 Reglas de negocios. Según (David M. Kroenke). El último elemento de un esquema de base de datos son las reglas de negocios. Son restricciones en las actividades de negocios que necesitan reflejarse en la base de datos y en sus aplicaciones. Los siguientes son ejemplos de reglas de negocios para Highline Collage:

• Para pedir prestado cualquier equipo, un capitán debe tener un número de teléfono local.

• Ningún capitán puede tener prestadas a la vez más de

siete pelotas de soccer.

• Los capitanes deben regresar todo el equipo dentro de los cinco días posteriores al final de cada semestre.

• Ningún capitán puede pedir prestado más equipo si

tiene algún material vencido. Las reglas de negocios son una parte importante del esquema porque especifican las limitaciones sobre los valores de datos permitidos que deben cumplirse, sin importar la forma en la que los cambios en los datos llegan al motor DBMS. Sin tomar en cuenta si una solicitud no válida de un cambio de datos viene del usuario de una forma, de una solicitud de consulta/actualización o de un programa de aplicación, el DBMS lo debe rechazar. Los actuales productos DBMS sólo ofrecen un cumplimiento limitado de las reglas de negocios, de modo que la mayor parte de las reglas deben cumplirse mediante programas de aplicación y procedimientos llevados a cabo por el usuario. La situación está cambiando y se están desarrollando productos DBMS para cumplir las reglas de negocios.

3.4 Seguridad de bases de datos.


48

3.4.1 Concepto de seguridad.

Según (Elmasri/Navathe). La técnica empleada apara proteger la base de datos contra personas que no estén autorizadas para tener acceso a una parte de la base de datos o a toda. Es la protección contra el acceso mal intencionado. 3.4.2 Autenticación y autorización. Según (Korth/Silberschatz). Un usuario puede tener varias formas de autorización sobre partes de la base de datos. Entre ellas se encuentran las siguientes:

• Autorización de lectura, que permite leer, pero no modificar, la base de datos.

• Autorización de inserción, que permite insertar

datos nuevos, pero no modificar los ya existentes.

• Autorización de actualización, que permite modificar la información, pero no permite la eliminación de datos.

• Autorización de borrado, que permite la eliminación

de datos. Un usuario puede tener asignados todos, ninguno o una combinación de los tipos de autorización anteriores. Además de las formas de autorización de acceso de datos antes mencionadas, es posible autorizar al usuario para que modifique es esquema de la base de datos.

• Autorización de índice, que permite la creación y eliminación de índices.

• Autorización de recursos, que permite la creación

de relaciones nuevas.

• Autorización de alteración, que permite agregar o eliminar atributos de una relación.

• Autorización de eliminación, que permite eliminar

relaciones. Las autorizaciones de eliminación y borrado difieren en cuanto a que la autorización de borrado sólo permite la eliminación de tuplas. Si un usuario elimina todas las tuplas de una relación, ésta


49

quedará vacía, pero seguirá existiendo. Si se elimina la relación dejará de existir. 3.4.3 Rol y privilegios de usuarios. Según (Elmasri/Navathe). Entre las obligaciones del DBA está otorgar privilegios a los usuarios que necesitan usar el sistema y clasificar los usuarios y los datos de acuerdo con la política de la organización. El DBA tiene una cuenta privilegiada en el SGBD, a veces denominada cuenta del sistema, que confiere capacidades extraordinarias no disponibles para las cuentas y usuarios ordinarios de la base de datos. Las órdenes privilegiadas del DBA incluyen órdenes para otorgar o revocar privilegios a cuentas individuales, usuarios o grupos de usuarios, y para efectuar los siguientes tipos de acciones:

1. Creación de cuentas: Esta acción crea una nueva cuenta y contraseña para un usuario o grupo e usuarios, a fin de que puedan tener acceso al SGBD.

2. Concesión de privilegios: Esta acción permite al DBA

otorgar ciertos privilegios a ciertas cuentas.

3. Revocación de privilegios: Esta acción permite al DBA revocar (cancelar) ciertos privilegios que se habían concedido previamente a ciertas cuentas.

4. Asignación de niveles de seguridad: esta acción consistente

en asignar cuentas de usuario al nivel apropiado de clasificación de seguridad.

El DBA es responsable de la seguridad global del sistema de base de datos. La acción 1 de la lista sirve para controlar el acceso el SGBD en general, en tanto que las acciones 2 y 3 se usan para controlar las autorizaciones discrecionales, y con la acción 4 se controla la autorización obligatoria. 3.4.4 Vistas y seguridad. Según (Korth/Silberschatz). El concepto de vistas, es una forma de proporcionar al usuario un modelo personalizado de la base de datos. Una vista puede ocultar datos que el usuario no tiene necesidad de ver. Esta posibilidad sirve tanto para simplificar la utilización del sistema como para fomentar la seguridad. El uso del sistema es más sencillo porque el usuario puede restringir su


50

atención a los datos que le interesan. La seguridad se logra si se cuenta con un mecanismo que limite a los usuarios a su vista o vistas personales. Lo normal es que las bases de datos relacionales cuenten con dos niveles de seguridad:

• Relación. Puede permitirse o impedirse que el usuario tenga acceso directo a una relación.

• Vista. Puede permitirse o impedirse que el usuario

tenga acceso a la información que aparece en una vista.

Aunque es posible impedir que un usuario tenga acceso directo a una relación, puede permitírsele acceso a una parte de esa relación por medio de una vista. De tal manera, es posible utilizar una combinación de seguridad al nivel relacional y al nivel de vistas para limitar el acceso del usuario exclusivamente a los datos que necesita.

3.5 Recuperación de bases de datos.

3.5.1 Transacciones.

3.5.1.1 Definición de transacción.

Según (Korth/Silberschat). Una transacción es una unidad de programa cuya ejecución conserva la consistencia de una base de datos.

Otra definición.

Según (Elmasri/Navathe). Una transacción es la ejecución de un programa que incluye operaciones de acceso a la base de datos.

3.5.1.2 Propiedades de atomicidad, consistencia, aislamiento y durabilidad (ACID).

Según (Elmasri/Navathe). Las transacciones atómicas deben poseer varias propiedades. Éstas se conocen como propiedades ACID y deben ser impuestas por los métodos de control de concurrencia y de recuperación del SGBD. Las propiedades ACID son:

1. Atomicidad: Una transacción es una unidad atómica

de procesamiento; o bien se realiza por completo o no se realiza en absoluto. }


51

2. Conservación de la consistencia: Una ejecución correcta de la transacción debe llevar a la base de datos de un estado consistente a otro.

3. Aislamiento: Una transacción no debe dejar que

otras transacciones puedan ver sus actualizaciones antes de que sea confirmada; esta propiedad, cuando se hace cumplir estrictamente, resuelve el problema de la actualización temporal y hace innecesarias las reversiones en cascada de las transacciones.

4. Durabilidad o permanencia: Unas ves que una

transacción ha modificado la base de datos y las modificaciones se han confirmado, éstas nunca deben perderse por un fallo subsecuente.

3.5.1.3. Estados de las transacciones.

Según (Korth/Silberschatz). El coordinador de transacciones se encarga de coordinar todas las transacciones que se inicien en esa localidad. Para cada una de estas transacciones, el coordinador debe:

• Iniciar la ejecución de la transacción.

• Dividir la transacción en varias subtransacciones, las

cuales ha de distribuir en las localidades apropiadas para su ejecución.

• Coordinar la terminación de la transacción, ya sea

que quede ejecutada o abortada en todas las localidades.

Otra definición de estados de las transacciones.

Según (Elmasri/Navathe). Para fines de recuperación, el sistema necesita mantenerse al tanto de cuándo la transacción se inicia, termina y se confirma o aborta. Así pues, el gestor de recuperación se mantiene al tanto de las siguientes operaciones:

• INICIO_DE_TRANSACCIÓN: Ésta marca el

principio de la ejecución de la transacción. • LEER o ESCRIBIR: Éstas especifican

operaciones de lectura o escritura de elementos de la base de datos que se efectúan como parte de una transacción.


52

• FIN_DE_TRANSACCIÓN: Ésta especifica que las

operaciones de LEER y ESCRIBIR de la transacción han terminado y marca el límite de la ejecución de la transacción. Sin embargo, en este punto puede ser necesario verificar si los cambios introducidos por la transacción se pueden aplicar permanentemente a la base de datos (confirmar) o si la transacción debe abortarse porque viola el control de concurrencia o por alguna otra razón.

• CONFIRMAR _ TRANSACCIÓN: Ésta señala que

la transacción terminó con éxito y que cualesquier cambios (actualizaciones) ejecutadas por ella se pueden confirmar sin peligro en la base de datos y que no se cancelarán.

• REVERTIR o (ABORTAR): Ésta indica que la

transacción terminó sin éxito y que cualesquier cambios o efectos que pueda haber aplicado a la base de datos se deben cancelar.

Además de las anteriores, algunas técnicas de recuperación requieren operaciones adicionales como las siguientes:

• DESHACER: Similar a revertir, pero se aplica

a una sola operación y no a una transacción completa.

• REHACER: Ésta especifica que ciertas

operaciones de transacción de deben repetir (rehacer) para asegurar que todas las operaciones de una transacción confirmada se hayan aplicado con éxito a la base de datos.


53

La figura 9. Muestra el diagrama de transición de estados que describe el paso de una transacción por sus estados de ejecución. Una transacción entra en un estado activo inmediatamente después de que inicia su ejecución, y ahí puede emitir operaciones LEER y ESCRIBIR. Cuando la transacción termina, para al estado parcialmente confirmado. En este punto, algunas técnicas de control de concurrencia requieren que se efectúen ciertas verificaciones para garantizar que la transacción no interfiera otras transacciones en ejecución. Una vez realizadas con éxito las verificaciones, se dice que la transacción ha llegado a su punto a su punto de confirmación y pasa al estado confirmado. Una vez que una transacción esta en el estado confirmado, ha concluido con éxito su ejecución. Sin embargo, una transacción puede pasar al estado fallido si una de las verificaciones falla o si la transacción se aborta mientras está en el estado activo. El estado terminado corresponde al abandono del sistema por parte de la transacción. Las transacciones fallidas o abortadas pueden reiniciarse posteriormente, ya sea en forma automática o después de reintroducirse como transacciones nuevas.

3.5.2 Bitácora.

Según (Elmasri/Navathe). Para poderse recuperar los fallos de transacciones, el sistema mantiene una bitácora (a veces llamada diario) que sigue la pista a todas las operaciones de transacciones que afecta los valores de elementos de la base de datos. La bitácora se mantiene en disco, de modo que no la afecta ningún tipo de fallo, más que los de disco o lo catastróficos. Además, la bitácora se respalda periódicamente en cinta para protegerse contra fallos catastróficos.

3.5.2.1 Tipos de bitácora.

Según (Elmasri/Navathe). A continuación mencionaremos los tipos de entradas que se escriben en la bitácora y la acción que realiza cada una de ellas- T se refiere a un identificador de transacción único que el sistema genera automáticamente y que sirve para identificar cada transacción:

1. [inicio_de_transacción, T]: Asienta que se ha iniciado

la ejecución de la transacción T.

2. [escribir_elemento,T,X, Valor_anterior,nuevo_valor]: Asienta que la transacción T cambió el valor del


54

elemento de base de datos X de valor_anterior a nuevo_valor.

3. [leer_elemento,T,X]: Asienta que la transacción T leyó

el valor del elemento de base de datos X.

4. [confirmar,T]: Asienta que la transacción T terminó con éxito y establece que su efecto se puede confirmar (asentar permanentemente) en la base de datos.

5. [abortar,T]: Asienta que se abortó la transacción T.

Los protocolos de recuperación que evitan las reversiones en cascada no requieren que las operaciones LEER se asienten en la bitácora del sistema, pero otros protocolos sí necesitan estas entradas para la recuperación. En el primer casi, el costo extra de asentar las operaciones en la bitácora se reduce, porque en ella se registran menos operaciones, sólo las de ESCRIBIR. Además, los protocolos estrictos requieren entradas ESCRIBIR más simples que no incluyen nuevo _ valor.

Si el sistema se cae, podemos recuperar la base de datos a un estado consistente examinando la bitácora y usando una de las técnicas de recuperación. Dado que la bitácora contiene un regusto de cada operación ESCRIBIR que altera el valor de algún elemento de la base de datos, es posible deshacer (cancelar) el efecto de estas operaciones ESCRIBIR de una transacción T rastreando hacia atrás en la bitácora y restableciendo todos los elementos alterados con una operación ESCRIBIR T a su valor _ anterior. También podemos rehacer el efecto de las operaciones ESCRIBIR de una transacción t rastreando hacia delante en la bitácora y cambiando todos los elementos modificados por una operación ESCRIBIR de T a su nuevo _ valor.

Una transacción T llega a su punto de confirmación cuando todas sus operaciones que tienen acceso a la base de datos se han ejecutado con éxito y el efecto de todas estas operaciones se ha asentado en la bitácora.

3.5.2.2 Contenido de la Bitácora.

Según (David M. Kroenke). La bitácora contiene un registro de las modificaciones de los datos en orden cronológico. Las transacciones se escriben en la bitácora, antes de su aplicación a la base de datos. Si el sistema se cae antes de que la transacción haya sido registrada y el momento en que se aplica, en el peor de los casos, existe un registro de una


55

transacción no aplicada. Si las transacciones fueron aplicadas antes de su inclusión en la bitácora, seria posible modificar la base de datos, pero no tener registro del cambio.

3.5.2.3 Respaldo.

Según (Elmasri/Navathe). Las utilerías de respaldo crean una copia de seguridad de la base de datos, casi siempre vertiendo toda la base de datos en cinta. La copia de seguridad puede servir para restaurar la base de datos en caso de un fallo catastrófico.

3.5.2.4 Como se hace un respaldo con Backup en sql Server

Realiza la copia de seguridad de una base de datos completa, del registro de transacciones o de uno o más archivos o grupos de archivos, Sintaxis para la copia de seguridad de una base de datos completa:

BACKUP DATABASE { database_name | @database_name_var } TO < backup_device > [ ,...n ] [ WITH [ BLOCKSIZE = { blocksize | @blocksize_variable } ] [ [ , ] DESCRIPTION = { 'text' | @text_variable } ] [ [ , ] DIFFERENTIAL ] [ [ ,] EXPIREDATE = { date | @date_var } | RETAINDAYS = { days | @days_var } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] FORMAT | NOFORMAT ] [ [ , ] { INIT | NOINIT } ] [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ] [ [ , ] { NOSKIP | SKIP } ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] ]

El componente para la realización de copias de seguridad y restauración de Microsoft® SQL Server™ 2000 proporciona una importante protección para los datos decisivos almacenados en bases de datos de SQL Server.


56

Con una planificación adecuada, se pueden recuperar muchos errores, incluidos:

• Errores de medios. • Errores de usuarios. • Pérdida permanente de un servidor.

Además, la realización de copias de seguridad y la restauración de bases de datos resulta útil para otros fines, como copiar una base de datos de un servidor a otro. Se puede hacer una copia de una base de datos rápida y fácilmente realizando una copia de seguridad de la base de datos de un equipo y restaurándolo en otro. Es necesario realizar copias de seguridad de las bases de datos del sistema de la misma manera que se realizan copias de seguridad de las bases de datos del usuario. Esto permite volver a generar el sistema en caso de producirse un error en el sistema o la base de datos como, por ejemplo, si deja de funcionar el disco duro. Resulta importante disponer de copias de seguridad periódicas de las siguientes bases de datos del sistema:

• master

• msdb

• distribution (cuando el servidor está configurado como distribuidor de duplicación)

• model (si se modifica)

3.5.2.5 Como se restaura una base de datos con Restore en SQL Server

Restaura copias de seguridad realizadas con el comando BACKUP. Sintaxis restaurar una base de datos completa:

RESTORE DATABASE { database_name | @database_name_var } [ FROM < backup_device > [ ,...n ] ] [ WITH [ RESTRICTED_USER ] [ [ , ] FILE = { file_number | @file_number } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [,...n]


57

[ [ , ] KEEP_REPLICATION ] [ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] REPLACE] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] ]

3.6 Diccionario de datos.

3.6.1 Concepto. Según (Elmasri/Navathe). Con el termino de diccionario de datos suele designarse una utilería de software más general que un catalogo. Los sistemas de diccionario de datos sirven para mantener información relativa al hardware y software, la documentación y los usuarios del sistema, así como otra información pertinente para la administración del sistema. 3.6.2 Contenido y función.

Según (Korth/Silberschatz). El diccionario de datos almacena información acerca de la estructura de la base de datos, y la información de autorización, y datos acerca de las relaciones. Tipos de información que el sistema debe almacenar están:

• Los nombres de las relaciones. • Los nombres de los atributos de cada relación. • Los dominios de los atributos. • Los nombres de las vistas definidas en la base de datos

y la definición de esas vistas. • Las restricciones de integridad de cada relación (por

ejemplo, las restricciones e clave). Además de esto, muchos sistemas conservan los datos siguientes de los usuarios del sistema:

• Nombres de los usuarios autorizados. • Información contable acerca de los usuarios.

En los sistemas que utilizan estructuras altamente sofisticadas para almacenar relaciones, pueden conservarse datos estadísticos y descriptivos acerca de las relaciones:

• Número de tuplas en cada relación.


58

• Método de almacenamiento utilizado para cada relación (por ejemplo, agrupado o sin agrupar).

3.6.3 Tipos.

Según (Elmasi/Navathe). Diccionario de datos Activo: Es un diccionario cuyas entradas son modificadas en forma automática por el software, siempre que ocurran modificaciones en la escritura de la base de datos. Diccionario de datos Pasivo: Necesitan ser actualizados en forma separada, para hacer modificaciones en la base de datos, de lo contrario no reflejarán con exactitud el estado de la base de datos. Los diccionarios de datos Activos cuestan más, pero aseguran se actualicen; no están disponibles con todos los productos DBMS. Los diccionarios de datos pasivos son menos costosos que los activos, pero se requiere de mayor esfuerzo para mantenerlos actualizados. Cualquiera de ellos es una gran ayuda al DBA para registrar y rastrear nombres, formatos, relaciones y referencias cruzadas de los datos.

5.6.4. Ejemplo de un diccionario.

Diccionario de datos de Clasificación. NOMBRE DESCRIPCIÓN TIPO

DE DATO

LONGITUD

(BYTES)

NULO DOCUMENTO ORIGEN

idCla Inclasificación Int si Abarrotera

Nomcla Nombre de la clasificación

varchar

60 no Abarrotera

Diccionario de datos Artículos

NOMBRE DESCRIPCIÓN TIPO DE DATO

LONGITUD

(BYTES)


idArt Id artículo Int Si Abarrotera

NomArt Nombre artículo Varchar 60 No Abarrotera

PreArt Precio artículo Numeric (9,2) No Abarrotera

ExiArt Existencia artículo

Int No Abarrotera


59

idCla id clasificación Int No Abarrotera

Diccionario de datos de venta artículo

NOMBRE DESCRIPCIÓN TIPO DE

DATO

LONGITUD

(BYTES)


FolVen Folio venta Int Si Abarrotera

IdArt Id Artículo Int Si Abarrotera

CantVen Cantidad Venta Int No Abarrotera

ImpLet Importe letra Varchar 255 No Abarrotera

UNIDAD 4. LENGUAJES DE BASES DE DATOS. Objetivo


60

Conocerá y aplicara los lenguajes de bases de datos.

4.1. Concepto y clasificación de lenguajes.

Un lenguaje de programación es un conjunto de símbolos y reglas sintácticas y semánticas que definen su estructura y el significado de sus elementos y expresiones, y es utilizado para controlar el comportamiento físico y lógico de una máquina. Un lenguaje de programación permite a uno o más programadores especificar de manera precisa sobre qué datos debe operar una computadora, cómo estos datos deben ser almacenados o transmitidos y qué acciones debe tomar bajo una variada gama de circunstancias. Todo esto, a través de un lenguaje que intenta estar relativamente próximo al lenguaje humano o natural. Una característica relevante de los lenguajes de programación es precisamente que más de un programador puedan tener un conjunto común de instrucciones que puedan ser comprendidas entre ellos para realizar la construcción del programa de forma colaborativa. Los procesadores usados en las computadoras son capaces de entender y actuar según lo indican programas escritos en un lenguaje fijo llamado lenguaje de máquina. Todo programa escrito en otro lenguaje puede ser ejecutado de dos maneras:

• Mediante un programa que va adaptando las instrucciones conforme son encontradas. A este proceso se le llama interpretar y a los programas que lo hacen se los conoce como intérpretes.

• Traduciendo este programa al programa equivalente escrito en

lenguaje de máquina. A ese proceso se lo llama compilar y al programa traductor se le denomina compilador.

Los lenguajes de programación se pueden clasificar atendiendo a varios criterios:

• Según el nivel de abstracción. • Según la forma de ejecución. • Según el paradigma de programación que poseen cada uno de

ellos.

Según su nivel de abstracción

Lenguajes Maquina


61

Están escritos en lenguajes directamente inteligibles por la maquina (computadora), ya que sus instrucciones son cadenas binarias (0 y 1). Da la posibilidad de cargar (transferir un programa a la memoria) sin necesidad de traducción posterior lo que supone una velocidad de ejecución superior, solo que con poca fiabilidad y dificultad de verificar y poner a punto los programas.

Lenguajes de bajo nivel Los lenguajes de bajo nivel son lenguajes de programación que se acercan al funcionamiento de una computadora. El lenguaje de más bajo nivel por excelencia es el código máquina. A éste le sigue el lenguaje ensamblador, ya que al programar en ensamblador se trabajan con los registros de memoria de la computadora de forma directa.

Lenguajes de medio nivel Hay lenguajes de programación que son considerados por algunos expertos como lenguajes de medio nivel (como es el caso del lenguaje C) al tener ciertas características que los acercan a los lenguajes de bajo nivel pero teniendo, al mismo tiempo, ciertas cualidades que lo hacen un lenguaje más cercano al humano y, por tanto, de alto nivel.

Lenguajes de alto nivel Los lenguajes de alto nivel son normalmente fáciles de aprender porque están formados por elementos de lenguajes naturales, como el inglés. En BASIC, uno de los lenguajes de alto nivel más conocidos, los comandos como "IF CONTADOR = 10 THEN STOP" pueden utilizarse para pedir a la computadora que pare si el CONTADOR es igual a 10. Esta forma de trabajar puede dar la sensación de que las computadoras parecen comprender un lenguaje natural; en realidad lo hacen de una forma rígida y sistemática, sin que haya cabida, por ejemplo, para ambigüedades o dobles sentidos.

Según la forma de ejecución

Lenguajes compilados Naturalmente, un programa que se escribe en un lenguaje de alto nivel también tiene que traducirse a un código que pueda utilizar la máquina. Los programas traductores que pueden realizar esta operación se llaman compiladores. Éstos, como los programas ensambladores avanzados, pueden generar muchas líneas de código de máquina por cada proposición del programa fuente. Se requiere una compilación antes de ejecutar las instrucciones de un problema.


62

Los compiladores son aquellos cuya función es traducir un programa escrito en un determinado lenguaje a un idioma que la computadora entienda (lenguaje máquina con código binario). Al usar un lenguaje compilado (como lo son, por ejemplo, los lenguajes del popular Visual Studio de Microsoft), el programa desarrollado nunca se podrá ejecutar mientras haya errores, sino hasta que tras haber compilado el programa, ya no aparecen errores en el código. Lenguajes interpretados Se puede también utilizar una alternativa diferente de los compiladores para traducir lenguajes de alto nivel. En vez de traducir el programa fuente y grabar en forma permanente el código objeto que se produce durante la compilación para utilizarlo en una ejecución futura, el programador sólo carga el programa fuente en la computadora junto con los datos que se van a procesar.

A continuación, un programa intérprete, almacenado en el sistema operativo del disco, o incluido de manera permanente dentro de la máquina, convierte cada proposición del programa fuente en lenguaje de máquina conforme vaya siendo necesario durante el procesamiento de los datos. El código objeto no se graba para utilizarlo posteriormente.

La siguiente vez que se utilice una instrucción, se la deberá interpretar otra vez y traducir a lenguaje máquina. Por ejemplo, durante el procesamiento repetitivo de los pasos de un ciclo o bucle, cada instrucción del bucle tendrá que volver a ser interpretada en cada ejecución repetida del ciclo, lo cual hace que el programa sea más lento en tiempo de ejecución (porque se va revisando el código en tiempo de ejecución) pero más rápido en tiempo de diseño (porque no se tiene que estar compilando a cada momento el código completo). El intérprete elimina la necesidad de realizar una de compilación después de cada modificación del programa cuando se quiere agregar funciones o corregir errores; pero es obvio que un programa objeto compilado con antelación deberá ejecutarse con mucha mayor rapidez que uno que se debe interpretar a cada paso durante una ejecución del código.

Según el paradigma de programación Un paradigma de programación representa un enfoque particular o filosofía para la construcción del software. No es mejor uno que otro, sino que cada uno tiene ventajas y desventajas. Dependiendo de la situación un paradigma resulta más apropiado que otro.


63

Atendiendo al paradigma de programación, se pueden clasificar los lenguajes en:

• El paradigma imperativo o por procedimientos es considerado el más común y está representado, por ejemplo, por el C o por BASIC.

• El paradigma funcional está representado por la familia de

lenguajes LISP (en particular Scheme), ML o Haskell.

• El paradigma lógico, un ejemplo es PROLOG.

• El paradigma orientado a objetos. Un lenguaje completamente orientado a objetos es Smalltalk.

4.2. Lenguajes formales de consulta.

Un lenguaje de consulta es un lenguaje en el que un usuario solicita información de la base de datos. Estos lenguajes son normalmente de más alto nivel que los lenguajes comunes de programación.

Los lenguajes de consulta se pueden clasificar de la siguiente manera:

1. Procedimentales 2. No procedimentales.

Lenguajes procedimentales. En este tipo de lenguaje el usuario da instrucciones la sistema para que realice una serie de procedimientos u operaciones en la base de datos para calcular un resultado final.

En los lenguajes no procedimentales el usuario describe la información deseada sin un procedimiento específico para obtener esa información.

4.2.1. Álgebra relacional.

El álgebra relacional es un lenguaje de consulta procedimental, consta de un conjunto de operaciones que toma una o dos relaciones como entrada y producen una nueva relación como resultado. Las operaciones fundamentales del álgebra relacional son.

• Selección • Proyección • Unión • Diferencia

Para demostrar con un ejemplo las operaciones relacionales usaremos la siguiente tabla:


64

Tabla: Préstamo

NomSucursal NumPrestamo NomCliente Cantidad Somex 17 Honorato 100 Bancomert 10 Horacio 150 Bancamext 12 Ramon 300 Bancamext 20 Julian 125

Tabla: Deposito

NumCuenta Abono Saldo

17 1000 9000

10 500 1000

17 6000 3000

20 250 1000

Operación Selección.

Selección: Consiste en la obtención de una nueva tabla formada por algunas de las filas (registros) seleccionadas de otra tabla previamente existente. La selección utiliza algún criterio que permita decidir que registros de la tabla se incluyen en la nueva tabla. En el ejemplo de la tabla Préstamo podemos seleccionar en el campo de NomSucursal todos los que sean igual a Bancamex.

Select * From Préstamo Where NomSucursal Like ‘Bancamex’

Como resultado obtenemos la siguiente tabla:

NomSucursal NumPrestamo NomCliente Cantidad

Bancamex 12 Ramon 300

Bancamex 20 Julian 125


65

En general en las operaciones del álgebra relacional son permitidos las comparaciones usando los operadores de relación =,�� Además de aplicarse conectores lógicos del tipo AND(��, OR(�). Asi por ejemplo, podemos encontrar las tuplas en las que la cantidad prestada sea más de 150 pesos para esto escribimos: Select * From Préstamo Where cantidad > 150 Esto daría como resultado la siguiente relación: NomSucursal NumPrestamo NomCliente Cantidad Bancamext 12 Ramon 300

Operación Proyección. Proyección: Consiste en la obtención de una nueva tabla formada por algunas columnas (campos) seleccionadas de otra tabla previamente existente. En el caso de que al elegir determinados campos el resultado produzca registros idénticos en la nueva tabla sólo figurará uno de los registros repetidos. Normalmente pueden efectuarse simultáneamente la selección y la proyección. En el ejemplo de tabla podemos tomar los campos de NomCliente y Cantidad para proyectarlos en una sola tabla.

Select NomCliente,Cantidad From Préstamo Where Cantidad >=150 Dando como resultado la siguiente tabla:

NomCliente Cantidad Horacio 150 Ramon 300

Operación unión. Concatenación: Consiste en la obtención de una nueva tabla uniendo dos tablas ya existentes. Por lo general la unión de registros se efectúa si en ambas tablas coincide el contenido de un campo prefijado de cada una de ellas. Cuando se produce la coincidencia, se crea un registro en la nueva tabla, añadiendo a los campos de la primera tabla los de la segunda. (Pérez López; C. 2003)


66

NomSucursal NumCuenta NomCliente Cantidad Somex 17 Honorato 10000 Bancomert 10 Horacio 1500 Bancamext 12 Ramon 3000 Bancamext 20 Julian 1250

Relación Préstamo

NumCuenta Abono Saldo

17 1000 9000

10 500 1000

17 6000 3000

20 250 1000

Relación Depósito

Obtener el nombre de los clientes, Cantidad, Abono y Saldo de los clientes que ya efectuaron algún depósito:

Select NomCliente, Cantidad, Abono, Saldo From Prestamo A Join Deposito B On (A.numCuenta = B.NumCuenta)

NomCliente Cantidad Abono Saldo

Honorato 10000 1000 9000 Honorato 10000 6000 3000 Horacio 1500 500 1000 Julian 1250 250 1000

Operación diferencia de conjuntos La operación diferencia de conjuntos, representada por el signo menos ( -), nos permite encontrara las tuplas que están en una relación pero no en otra.


67

Por ejemplo. "Encontrar a todos los clientes de la sucursal "Bancamex" que tienen un Préstamo pero que no tienen ningún abono" Select NomCliente From Préstamo Where NumCuenta Not in (Select NumCuenta From Deposito)

NomCliente

Ramon

4.2.2. Cálculo relacional.

Al escribir una expresión en álgebra relacional, damos una secuencia de procedimientos que genera una respuesta a la consulta. El cálculo relacional de tuplas, en cambio, es un lenguaje de consultas no procedimental. Describe la información deseada sin dar un procedimiento específico para obtener esa información. Una consulta en el cálculo relacional de tuplas se expresa como

{t/P(t)}

Es decir, el conjunto de todas las tuplas t, tal que el predicado P, es verdadero para t. Siguiendo la notación anterior, se usa t[A] para representar el valor de tupla t en el atributo A. y se aplica t ��r para indicar que la tupla t está en la relacion r. Consultas Encontrar el nombre -sucursal, numero-prestamo y cantidad para prestamos mayores de 1200

{ t / t ��r prestamo ��[cantidad] > 1200 }

4.2.3. Optimización de consultas. Puede ser tentador resolver un problema de rendimiento únicamente mediante la optimización del rendimiento del servidor a nivel del sistema, por ejemplo, optimizando el tamaño de la memoria, el tipo de sistema de archivos, el número y tipo de procesadores y otros elementos del sistema. La experiencia ha demostrado que la mayoría de los problemas de rendimiento no se pueden resolver de esta forma. Se deben solucionar mediante el


68

análisis de la aplicación, las consultas y las actualizaciones que la aplicación envía a la base de datos, y la forma en que estas consultas y actualizaciones interactúan con el esquema de la base de datos. Una duración inesperadamente larga de las consultas puede deberse a:

• Comunicaciones de red lentas.

• Memoria inadecuada en el equipo servidor o falta de memoria disponible para Microsoft® SQL Server™ 2000.

• Falta de estadísticas útiles.

• Estadísticas no actualizadas.

• Falta de índices útiles.

• Falta de creación de bandas de datos útiles.

4.3. SQL.

El Lenguaje de consulta estructurado (Structured Query Language) es un lenguaje declarativo de acceso a bases de datos relacionales que permite especificar diversos tipos de operaciones sobre las mismas. Una de sus características es el manejo del álgebra y el cálculo relacional permitiendo lanzar consultas con el fin de recuperar -de una forma sencilla- información de interés de una base de datos, así como también hacer cambios sobre la misma. Es un lenguaje de cuarta generación (4GL).

Características generales El SQL es un lenguaje de acceso a bases de datos que explota la flexibilidad y potencia de los sistemas relacionales permitiendo gran variedad de operaciones sobre los mismos. Es un lenguaje declarativo de "alto nivel" o "de no procedimiento", que gracias a su fuerte base teórica y su orientación al manejo de conjuntos de registros, y no a registros individuales, permite una alta productividad en codificación y la orientación a objetos. De esta forma una sola sentencia puede equivaler a uno o más programas que utilizas en un lenguaje de bajo nivel orientado a registro.

4.3.1. Lenguaje de definición de datos.

Un lenguaje de definición de datos (Data Definition Language, DDL por sus siglas en inglés) es un lenguaje proporcionado por el


69

sistema de gestión de base de datos que permite a los usuarios de la misma llevar a cabo las tareas de definición de las estructuras que almacenarán los datos así como de los procedimientos o funciones que permitan consultarlos. El lenguaje de programación SQL, el más difundido entre los gestores de bases de datos, admite las siguientes sentencias de definición: CREATE, DROP y ALTER, cada una de las cuales se puede aplicar a las tablas, vistas, procedimientos almacenados y triggers de la base de datos. Otras que se incluyen dentro del DDL, pero que su existencia depende de la implementación del estándar SQL que lleve a cabo el gestor de base de datos son GRANT y REVOKE, los cuales sirven para otorgar permisos o quitarlos, ya sea a usuarios específicos o a un rol creado dentro de la base de datos. Ejemplo de creación de tablas Create Table Clasificacion ( idClas int not null, Materia varchar (30), constraint pkidclas primary key(idClas) )

Create table Donador( idDon int not null, NomDon varchar (40), telDon int, constraint pkiddon primary key(idDon) ) Create table Adquisicion( idAdqui int not null, idDon int, total int, fechDon varchar (8), constraint fkidDon foreign key (idDon)references Donador(idDon), constraint pkidAdqui primary key (idAdqui) )

4.3.2. Lenguaje de manipulación de datos. Un Lenguaje de Manipulación de Datos (Data Manipulation Language) (DML) es un lenguaje proporcionado por el sistema de gestión de base de datos que permite a los usuarios de la misma llevar a cabo las tareas de consulta o manipulación de los datos, organizados por el modelo de datos adecuado.


70

El lenguaje de manipulación de datos más popular hoy día es SQL, usado para recuperar y manipular datos en una base de datos relacional. Otros ejemplos de DML son los usados por bases de datos IMS/DL1, CODASYL u otras. Ejemplo de manipulación de datos (insert). Insert into Clasificacion(idClas,Materia)values(001,'Ingles') Insert into Clasificacion(idClas,Materia)values(002,'Quimica') Ejemplo de manipulación de datos (update). Update Clasificacion set Materia='bases de dato'where idClas=001 Update Clasificacion set Materia='sistemas operativos'where Materia='Quimica'

Ejemplo de manipulación de datos (delete).

Delete from Clasificacion where Materia='sistemas operativos' Delete from Clasificacion where idClas=001

4.3.3 Consultando Información. Los ejemplos que a continuación se presentaran se basarán en la siguiente base de datos, la cual almacena la facturación de varios negocios, así como el control de su catálogo de productos y clientes.

Consultas de Múltiples Tablas


71

Joins (Combinando) Datos desde Múltiples Tablas Los Joins (combinaciones) permiten recuperar datos de dos tablas o más según las relaciones lógicas entre ellas. Los Joins (combinaciones) indican cómo debería usar Microsoft SQL Server los datos de una tabla para seleccionar las filas de otra tabla. Una condición de Join define la forma en la que dos tablas se relacionan en una consulta al especificar:

• La columna de cada tabla que debe usarse para la

combinación. Una condición de combinación típica especifica una clave externa de una tabla y su clave asociada en otra tabla.

• Un operador lógico (=, <>, etc.) para usarlo en los

valores de comparación de las columnas.

Los Joins se pueden especificar en las cláusulas FROM o WHERE. Las condiciones de Join se combinan con las condiciones de búsqueda de WHERE y HAVING para controlar cuáles son las filas seleccionadas de las tablas base a lasque se hace referencia en la cláusula FROM. Especificar las condiciones del Join en la cláusula FROM ayuda a separarlas de cualquier otra condición de búsqueda que se pueda especificar en una cláusula WHERE, y es el método recomendado para especificar combinaciones. La sintaxis simplificada de la combinación de la cláusula FROM de SQL-92 es:

FROM primeraTabla tipoJoin segundaTabla [ON (condiciónJoin)]

tipoJoin especifica qué clase de Join(combinación) se realiza: un Join(combinación) interno, externo o cruzado. condiciónJoin define el predicado que se va a evaluar por cada par de fila combinadas. Un ejemplo de la especificación de una combinación de la cláusula FROM es: FROM Negocio JOIN Producto ON (Negocio.NegocioPk = Producto.NegocioPk) Un ejemplo de instrucción SELECT simple que usa esta combinación es:

SELECT NomNegocio, NomProducto, Existencia FROM Negocio JOIN Producto ON (Negocio.NegocioPk = Producto.NegocioPk) WHERE PrecioVenta > 10 AND NomNegocio LIKE 'F%'


72

GO

La selección devuelve la información de los productos y proveedores de cualquier combinación de partes suministrada por una empresa cuyo nombre empieza con la letra F y el precio del producto es superior a 10 pesos. Cuando en una consulta simple se hace referencia a múltiples tablas, ninguna de las referencias a las columnas debe ser ambigua. En el ejemplo anterior, ambas tablas, Producto y Negocio, tienen una columna llamada NegocioPk. Cualquier nombre de columna que esté duplicado en varias tablas a las que se hace referencia en la consulta debe ser calificado con el nombre de la tabla. Todas las referencias a las columnas NegocioPk del ejemplo se califican.

Cuando el nombre de una columna no está duplicado en varias de las tablas usadas en la consulta, las referencias al mismo no tienen que calificarse con el nombre de la tabla. Esto se muestra en el ejemplo anterior. Algunas veces, una instrucción SELECT es difícil de entender porque no hay nada que indique qué tabla ha suministrado cada columna. Se puede mejorar la comprensión de la consulta si todas las columnas se califican con sus nombres de tabla. La comprensión puede aumentarse más si se usan alias de tablas, especialmente cuando los propios nombres de las tablas se deben calificar con los nombres de las bases de datos y de los propietarios. Éste es el mismo ejemplo, con la salvedad de que se han asignado alias a las tablas y las columnas se han calificado con los alias de las tablas para mejorar su comprensión:

SELECT A.NomNegocio, B.NomProducto, B.Existencia FROM Negocio as A JOIN Producto AS B ON (A.NegocioPk = B.NegocioPk) WHERE B.PrecioVenta > 10 AND A.NomNegocio LIKE 'F%' GO Los ejemplos anteriores han especificado las condiciones de combinación en la cláusula FROM, lo que constituye el método recomendado. Esta consulta contiene la misma condición de combinación especificada en la cláusula WHERE: SELECT A.NomNegocio, B.NomProducto, B.Existencia FROM Negocio as A, Producto AS B WHERE A.NegocioPk = B.NegocioPk AND B.PrecioVenta > 10 AND A.NomNegocio LIKE 'F%' GO

La lista de selección de una combinación puede hacer referencia a todas las columnas de las tablas combinadas o a cualquier subconjunto de las columnas. No es necesario que la lista de selección contenga columnas de cada tabla de la combinación. Por ejemplo, en una combinación de tres tablas, sólo se puede


73

usar una tabla para pasar de una de las dos tablas a la tercera y no es necesario que se haga referencia a ninguna columna de la tabla intermedia en la lista de selección. Aunque las condiciones de combinación suelen tener comparaciones de igualdad (=), también se puede especificar otros operadores de comparación o relacionales, así como otros predicados. Cuando SQL Server procesa Joins, el motor de consultas elige el método más eficiente entre varias posibilidades para procesar la combinación. Aunque la ejecución física de varias combinaciones utiliza muchas optimizaciones distintas, la secuencia lógica es:

• Se aplican las condiciones de combinación de la cláusula

FROM. • Las condiciones de combinación y de búsqueda de la

cláusula WHERE. • Se aplican las condiciones de búsqueda de la cláusula

HAVING.

La secuencia lógica puede, en ocasiones, influir en los resultados de la consulta si se cambian las condiciones entre las cláusulas FROM y WHERE. La mayor parte de las consultas que usan un Join (combinación) se pueden volver a escribir con una subconsulta (una consulta anidada dentro de otra consulta), y la mayor parte de las subconsultas se pueden escribir como Joins. Nota Las tablas no se pueden combinar directamente en columnas ntext, text o image. Sin embargo, las tablas se pueden combinar indirectamente en columnas ntext, text o image mediante la utilización de SUBSTRING. Por ejemplo, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.columnaTexto, 1, 20) = SUBSTRING(t2.columnaTexto, 1, 20) realiza una combinación interna de dos tablas en los primeros 20 caracteres de cada columna de texto de las tablas t1 y t2.

Utilizar combinaciones internas (INNER JOIN) Una combinación interna es aquella en la que los valores de las columnas que se están combinando se comparan mediante un operador de comparación. En el estándar SQL-92, las combinaciones internas se pueden especificar en las cláusulas FROM o WHERE. Éste es el único tipo de combinación que SQL-92 admite en la cláusula WHERE. Las combinaciones internas especificadas en la cláusula WHERE se conocen como combinaciones internas al estilo antiguo.


74

Esta consulta de Transact-SQL es un ejemplo de una combinación interna. SELECT A.NomNegocio, P.NomProducto, P.PrecioVenta FROM Negocio AS a INNER JOIN Producto AS p ON a.NegocioPk = p.NegocioPk ORDER BY a.NomProducto DESC Esta combinación interna se conoce como una combinación equivalente. Devuelve todas las columnas seleccionadas de ambas tablas y sólo devuelve las filas en las que haya un valor igual en la columna de la combinación.

A.NomNegocio P.NomProducto P.Precio --------------------- ------------------------ -------------- Ferretera Papalo Tornillo 0.50 Ferretera Papalo Taladros 300 Ferretera Abraham Arena 200 Ferretera Abraham Alambron 550

Utilizar combinaciones externas (OUTER JOIN) Las combinaciones internas sólo devuelven filas cuando hay una fila de ambas tablas, como mínimo, que coincide con la condición de la combinación. Las combinaciones internas eliminan las filas que no coinciden con alguna fila de la otra tabla. Sin embargo, las combinaciones externas devuelven todas las filas de una de las tablas o vistas mencionadas en la cláusula FROM, como mínimo, siempre que tales filas cumplan con alguna de las condiciones de búsqueda de WHERE o HAVING. Todas las filas se recuperarán de la tabla izquierda a la que se haya hecho referencia con una combinación externa izquierda, y de la tabla derecha a la que se haya hecho referencia con una combinación externa derecha. En una combinación externa completa, se devuelven todas las filas de ambas tablas. Microsoft SQL Server usa las siguientes palabras clave de SQL-92 para las combinaciones externas especificadas en una cláusula FROM:

• LEFT OUTER JOIN o LEFT JOIN • RIGHT OUTER JOIN o RIGHT JOIN • FULL OUTER JOIN o FULL JOIN

SQL Server admite tanto la sintaxis de combinaciones externas de SQL-92 como la sintaxis heredada para especificar las combinaciones externas que se basan en la utilización de los operadores *= y =* en la cláusula WHERE. Se recomienda utilizar la sintaxis de SQL-92 porque no está sujeta a las ambigüedades


75

que se producen, en ocasiones, en las combinaciones externas heredadas de Transact-SQL. Utilizar combinaciones exteriores izquierdas Imagine una combinación de la tabla Negocio y de la tabla Clientes en sus columnas ciudad. Los resultados sólo muestran los Clientes que viven en ciudades donde haya algún Negocio. Para incluir todos los Clientes en el resultado, con independencia de que un Negocio se encuentre o no en la misma ciudad, use una combinación exterior izquierda de SQL-92. A continuación se muestra la consulta y los resultados de la combinación externa izquierda de Transact-SQL: SELECT a.NomCliente, p.NomNegocio FROM Cliente a LEFT OUTER JOIN Negocio p ON a.ciudad = p.ciudad and a.NegocioPk = p.NegocioPk ORDER BY p.NomNegocio ASC, a.NomCliente ASC Este es el conjunto de resultados:

NomCliente NomNegocio ------------------------------- ----------------------------------------

Erick Guzmán NULL Luis Eduardo Medina NULL Ivan Cruz Guzmán Ferretera Abraham Ios Cruz Ferretera Papalo

LEFT OUTER JOIN incluye en el resultado todas las filas de la tabla Cliente, tanto si hay una coincidencia en la columna ciudad de la tabla Negocio como si no la hay. Observe que, en los resultados, no hay datos coincidentes con la mayor parte de los Clientes enumerados, con lo que estas filas contienen valores NULL en la columna NomNegocio.

Utilizar combinaciones exteriores derechas Imagine una combinación de la tabla Cliente y de la tabla Negocio en sus columnas ciudad. Los resultados sólo muestran los Clientes que viven en ciudades donde haya algún Negocio. El operador de combinación exterior derecha de SQL-92, RIGHT OUTER JOIN, indica que todas las filas de la segunda tabla se deben incluir en los resultados, con independencia de si hay datos coincidentes en la primera tabla. Para incluir todos los Negocios en los resultados, sin tener en cuenta si hay unNegocio en la ciudad, use una combinación externa derecha de SQL-92. A continuación se muestra la consulta de Transact-SQL y los resultados de la combinación externa derecha:

SELECT a.NomCliente, p.NomNegocio


76

FROM Cliente AS a RIGHT OUTER JOIN Negocio AS p ON a.ciudad = p.ciudad and a.NegocioPk = p.NegocioPk ORDER BY p.NomNegocio ASC, a.NomCliente ASC

NomCliente NomNegocio ----------------------------------------- ---------------------------------------- Ivan Cruz Guzmán Ferretera Abraham NULL Ferretera la Nueva Ios Cruz Ferretera Papalo

Para retener la información que no coincida al incluir las filas no coincidentes en los resultados de una combinación, utilice una combinación externa completa. Microsoft SQL Server proporciona el operador de combinación externa completa, FULL OUTER JOIN, que incluye todas las filas de ambas tablas, con independencia de que la otra tabla tenga un valor coincidente o no. Imagine una combinación de la tabla Cliente y de la tabla Negocio en sus columnas ciudad. Los resultados sólo muestran los Clientes que viven en ciudades donde hay algún Negocio. El operador de combinación externa completa de SQL-92, FULL OUTER JOIN, indica que todas las filas de ambas tablas se van a incluir en los resultados, con independencia de que haya datos coincidentes en las tablas. Para incluir todos los Negocios y todos los Clientes en el resultado, sin tener en cuenta si un Cliente se encuentra en la misma ciudad, use una combinación externa completa. A continuación se muestran la consulta y los resultados de la combinación externa completa de Transact-SQL:

SELECT a.NomCliente, p.NomCiudad FROM Cliente a FULL OUTER JOIN Negocio p ON a.ciudad = p.ciudad ORDER BY p.NomNegocio ASC, a.NomCliente ASC

NomCliente NomNegocio -------------------------------------------- ------------------------------------- Erick Guzmán NULL Luis Eduardo Medina NULL Ivan Cruz Guzmán Ferretera Abraham NULL Ferretera la Nueva Ios Cruz Ferretera Papalo

Utilizar Combinaciones Cruzadas(Cross Join) Una combinación cruzada que no tenga una cláusula WHERE da como resultado el producto cartesiano de las tablas involucradas en la combinación. El tamaño del conjunto de resultados de un producto cartesiano es igual al número de filas de la primera tabla multiplicado por el número de filas de la segunda tabla. A


77

continuación se muestra un ejemplo de una combinación cruzada de Transact-SQL: SELECT a.NomCliente, p.NomNegocio FROM Cliente a CROSS JOIN Negocio p ORDER BY a.NomCliente DESC

El conjunto de resultados contiene 12 filas (Cliente tiene 4 filas y Negocio tiene 3; 4 multiplicado por 3 es igual a 12).

NomCliente NomNegocio ------------------------------------- ------------------------------------------ Luis Eduardo Medina Ferretera Abraham Luis Eduardo Medina Ferretera Papalo Luis Eduardo Medina Ferretera la Nueva Ivan Cruz Guzmán Ferretera la Nueva Ivan Cruz Guzmán Ferretera Papalo Ivan Cruz Guzmán Ferretera Abraham Ios Cruz Ferretera Abraham Ios Cruz Ferretera Papalo Ios Cruz Ferretera la Nueva Erick Guzmán Ferretera la Nueva Erick Guzmán Ferretera Papalo Erick Guzmán Ferretera Abraham

Sin embargo, si se agrega una cláusula WHERE, la combinación cruzada se comporta como una combinación interna. Por ejemplo, las siguientes consultas de Transact-SQL producen el mismo conjunto de resultados: SELECT a.NomCliente, p.NomNegocio FROM Cliente a CROSS JOIN Negocio p WHERE a.ciudad = p.ciudad and A.NegocioPk = p.NegocioPk ORDER BY a.NomCliente DESC -- o SELECT a.NomCliente, p.NomNegocio FROM Cliente a INNER JOIN Negocio p ON A.ciudad = p.ciudad and A.NegocioPk = p.NegocioPk ORDER BY a.NomCliente DESC

Combinar Tres Tablas o Más Si bien cada especificación de combinación sólo combina dos tablas, las cláusulas FROM pueden contener múltiples especificaciones de combinaciones. Esto permite que se combinen muchas tablas en una consulta única.

La tabla DetalleFactura de la base de datos Facturacion ofrece un buen ejemplo de una situación en la que resulta útil combinar más de dos tablas. En la consulta siguiente de Transact-SQL se


78

buscan los nombres de todos los productos que se vendieron en la Factura 100 del Negocio 1 USE Facturación

SELECT A.FolFactura, A.FecFactura, C.NomProducto, B.Cantidad, B.Importe FROM (Factura A Inner Join DetalleFactura B on (A.ConsecPk = B.ConsecPk)) Inner Join Producto C On (B.NegocioPk = C.NegocioPk And

B.ProductoPk = C.ProductoPk)

Where A.NegocioPk = 1 and A.FolFactura = 100 Order By A.FolFactura

Éste es el conjunto de resultados: FolFactura FecFactura NomProducto Cantidad Importe --------------- --------------- ---------------------- -------------- ----------- 100 2001-01-01 Escoba 2 13 100 2001-01-01 Mechudo 2 20

Cuando hay más de un operador de combinación en la misma instrucción, bien para combinar más de dos tablas o bien para combinar más de dos pares de columnas, las expresiones de combinación se pueden conectar con AND o con OR.

Consultas Avanzadas

Conceptos Básicos de Subconsultas Una subconsulta es una consulta SELECT que devuelve un valor único y está anidada en una instrucción SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. Una subconsulta se puede utilizar en cualquier parte en la que se permita una expresión. En este ejemplo se utiliza una subconsulta como una expresión de columna llamada MaxImporte en una instrucción SELECT.

SELECT Factura.FolFactura, Factura.FecFactura, (SELECT MAX(A.Importe)

FROM DetalleFactura A WHERE Factura.ConsecPk = A.ConsecPk)

AS MaxImporte FROM Factura

Se llama también subconsulta a una consulta o selección interna, mientras que la instrucción que contiene una subconsulta también es conocida como consulta o selección externa.


79

Muchas de las instrucciones de Transact-SQL que incluyen subconsultas se pueden formular también como combinaciones. Otras preguntas se pueden formular sólo con subconsultas. En Transact-SQL, normalmente no hay diferencias de rendimiento entre una instrucción que incluya una subconsulta y una versión semánticamente equivalente que no lo haga. Sin embargo, en algunos casos en los que se debe comprobar la existencia de algo, una combinación produce mejores resultados. De lo contrario, la consulta anidada debe ser procesada para cada resultado de la consulta externa con el fin de asegurar la eliminación de los duplicados. En tales casos, la utilización de combinaciones produciría los mejores resultados. Esto es un ejemplo que muestra una subconsulta SELECT y una combinación SELECT que devuelve el mismo conjunto de resultados:

SELECT Producto.NomProducto FROM Producto WHERE Producto.ProductoPk = (SELECT Distinct A.ProductoPk FROM DetalleFactura A Where Producto.NegocioPk = A.NegocioPk and Producto.ProductoPk = A.ProductoPk )

Select NomProducto From Producto A Inner Join DetalleFactura B On A.NegocioPk = B.NegocioPk And A.ProductoPk = B.ProductoPk

Una subconsulta anidada en la instrucción externa SELECT tiene los siguientes componentes:

• Una consulta SELECT normal, que incluye los

componentes normales de la lista de selección. • Una cláusula normal FROM que incluye uno o más

nombres de tablas o vistas. • Una cláusula opcional WHERE. • Una cláusula opcional GROUP BY. • Una cláusula opcional HAVING.

La consulta SELECT de una subconsulta se incluye siempre entre paréntesis. Una subconsulta puede anidarse dentro de la cláusula WHERE o HAVING de una instrucción externa SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. Se puede disponer de hasta 32 niveles de anidamiento, aunque el límite varía dependiendo de la memoria disponible y de la complejidad del resto de las expresiones de la consulta. Las consultas individuales puede que no admitan anidamientos por encima de 32


80

niveles. Una subconsulta puede aparecer en cualquier parte en la que se pueda usar una expresión, si devuelve un valor individual.

Si una tabla aparece sólo en una subconsulta y no en la consulta externa, las columnas de esa tabla no se pueden incluir en el resultado (la lista de selección de la consulta externa). Las instrucciones que incluyen una subconsulta normalmente tienen uno de estos formatos:

• WHERE expresión [NOT] IN (subconsulta) • WHERE expresión operadorComparación [ANY | ALL]

(subconsulta) • WHERE [NOT] EXISTS (subconsulta)

En algunas instrucciones de Transact-SQL, la subconsulta se puede evaluar como si fuera una consulta independiente. Conceptualmente, los resultados de la subconsulta se sustituyen en la consulta externa (aunque, en realidad, ésta no es la forma en que Microsoft SQL Server procesa las instrucciones de Transact-SQL con subconsultas). Hay tres tipos básicos de subconsultas, que son las que:

• Operan en listas, introducidas con IN o aquellas que

modificó un operador de comparación mediante ANY o ALL.

• Se introducen con un operador de comparación sin modificar y deben devolver un valor individual.

• Son pruebas de existencia que se introducen con EXISTS.

Subconsultas con IN El resultado de una subconsulta presentada con IN (o con NOT IN) es una lista de cero o más valores. Una vez que la consulta devuelve los resultados, la consulta externa hace uso de ellos. En esta consulta se buscan los nombres de los Clientes que tienen alguna factura: SELECT A.NomCliente FROM Cliente A WHERE ClientePk IN (SELECT Distinct B.ClientePk FROM Factura B WHERE A.NegocioPk = B.NegocioPk) Este es el conjunto de resultados: NomCliente


81

------------------------------------------------------------ Ivan Cruz Guzmán Erick Guzmán Esta instrucción se evalúa en dos pasos. Primero, la consulta interna devuelve los números de identificación de los clientes en el que el negocio donde efectuó la compra coincida. Segundo, estos valores se sustituyen en la consulta externa, que busca los nombres de los clientes asociados con los números de identificación en la tabla de Clientes.

Subconsultas con NOT IN Una subconsulta utilizando NOT IN regresa todas las ocurrencias que no se correspondan con el resultado de la subConsulta: Para este ejemplo serian todos los clientes que no tienen ninguna factura o remisión. Este caso se da cuando se presenta el proceso de depuración de las tablas de factura de detallefactura:

SELECT A.NomCliente FROM Cliente A WHERE ClientePk NOT IN (SELECT Distinct B.ClientePk FROM Factura B WHERE A.NegocioPk = B.NegocioPk)

Este es el conjunto de resultados: NomCliente ------------------------------------------------------------ Luis Eduardo Medina Ios Cruz Subconsultas en las instrucciones UPDATE, DELETE e INSERT Las subconsultas se pueden anidar en las instrucciones UPDATE, DELETE e INSERT, así como en las instrucciones SELECT. La siguiente consulta dobla el PrecioVenta de todos los Productos del Negocio Ferretera Papalo. La consulta actualiza la tabla Producto; su subconsulta hace referencia a la tabla Negocio. UPDATE Producto SET PrecioVenta = PrecioVenta * 2 WHERE ProductoPk IN (SELECT ProductoPk FROM Negocio WHERE NomNegocio = 'Ferretera Papalo' AND Negocio.NegocioPk = Producto.NegocioPk)


82

Puede quitar todos los registros de ventas del 05/Abril/2001 con esta consulta anidada: DELETE DetalleFactura WHERE ConsecPk IN (SELECT ConsecPk FROM Factura WHERE FecFactura = '2001/04/05')

Esta es una instrucción DELETE equivalente que usa una combinación: DELETE DetalleFactura FROM DetalleFactura INNER JOIN Factura ON DetalleFactura.ConsecPk = Factura.ConsecPk AND Factura.FecFactura = '2001/02/02' Para crear una tabla alterna con el resultado de una consulta: 1er Paso. Crear la Tabla Vacía SELECT A.NomNegocio, b.NomProducto INTO TablaAux FROM Negocio A INNER JOIN Producto B ON A.NegocioPk =

B.NegocioPk AND B.ProductoPk = NULL

2o Paso. Llenar la Tabla Insert Into TablaAux SELECT A.NomNegocio, b.NomProducto FROM Negocio A INNER JOIN Producto B ON A.NegocioPk = B.NegocioPk

Se preguntarán el por qué en dos pasos y no en uno solo, la causa es que si se efectúa en un solo paso ocurre un bloqueo en tablas del TempDb de SQL Server y genera que ninguna otra base de datos pueda trabajar de manera concurrente. Subconsultas con Operadores de Comparación Las subconsultas se pueden presentar con uno de los operadores de comparación (=, < >, >, > =, <, ! >, ! < o < =). Una subconsulta presentada con un operador de comparación sin modificar (un operador de comparación no seguido de ANY o ALL) debe devolver un valor individual en lugar de una lista de valores, como las subconsultas presentadas con IN. Si tal subconsulta devuelve más de un valor, Microsoft SQL Server muestra un mensaje de error. Para usar una subconsulta presentada con un operador de comparación sin modificar, debe estar suficientemente familiarizado con los datos y con la naturaleza del problema para saber que la subconsulta devolverá exactamente un valor.


83

Por ejemplo, quiere determinar qué producto se facturó a un precio de venta más alto que el especificado en la tabla de producto:

SELECT ProductoPk,ConsecPk, Cantidad, Importe FROM DetalleFactura WHERE Importe/Cantidad > (SELECT PrecioVenta FROM Producto WHERE DetalleFactura.NegocioPk = Producto.NegocioPk AND DetalleFactura.ProductoPk = Producto.ProductoPk)

Este es el conjunto de resultados: ProductoPk ConsecPk Cantidad Importe -------------- -------------- -------------------- -------------------- 3 2 10 252

Subconsultas con EXISTS Cuando se presenta una subconsulta con la palabra clave EXISTS, funciona como una prueba de existencia. La cláusula WHERE de la consulta externa comprueba la existencia de las filas devueltas por la subconsulta. La subconsulta en realidad no produce ningún dato, devuelve el valor TRUE o FALSE. Una subconsulta presentada con EXISTS tiene la siguiente sintaxis:

WHERE [NOT] EXISTS (subconsulta) En esta consulta se busca el nombre de los Clientes a quienes se les facturó. SELECT NomCliente FROM Cliente WHERE EXISTS (SELECT * FROM Factura WHERE Factura.ClientePk = Cliente.ClientePk AND Factura.NegocioPk = Cliente.NegocioPk AND Factura.FolFactura <> 0) Este es el conjunto de resultados: NomCliente ------------------------------------------------------------ Ivan Cruz Guzmán Erick Guzmán


84

Subconsultas con NOT EXISTS NOT EXISTS funciona igual que EXISTS, salvo por que la cláusula WHERE en la que se utiliza se cumple si la subconsulta no devuelve ninguna fila. En el ejemplo siguiente se buscan nombres de Clientes que no tengan factura. SELECT NomCliente FROM Cliente WHERE NOT EXISTS (SELECT * FROM Factura WHERE Factura.ClientePk = Cliente.ClientePk AND Factura.NegocioPk = Cliente.NegocioPk AND Factura.FolFactura <> 0) Este es el conjunto de resultados: NomCliente ------------------------------------------------------------ Luis Eduardo Medina Ios Cruz

Sumando Datos

Usar funciones de agregado en la lista de selección Las funciones de agregado (SUM, AVG, COUNT, COUNT(*), MAX y MIN) generan valores de resumen en los conjuntos de resultados de las consultas. Una función de agregado (con la excepción de COUNT(*)) procesa todos los valores seleccionados en una única columna para generar un único resultado. Las funciones de agregado se pueden aplicar a todas las filas de una tabla, a un subconjunto de la tabla especificado por una cláusula WHERE o a uno o varios grupos de filas de la tabla. Cuando se aplica una función de agregado, se genera un valor individual por cada conjunto de filas. En este ejemplo se calcula la suma el valor de las existencias de todos los productos de un negocio en particular.

SELECT SUM(Existencia * Costo) FROM Producto WHERE NegocioPk = 1 ----------------------------------------------------- 4071.0000


85

Con esta consulta, puede averiguar el precio promedio de todos los productos si se doblaran los precios. SELECT avg(PrecioVenta * 2) ‘Precio de Promedio’ FROM Producto WHERE NegocioPk = 1 Éste es el conjunto de resultados: ‘Precio de Venta’ -----------------------------------------------------

32.750000

En la tabla se muestra la sintaxis de las funciones de agregado y sus resultados (expresión es casi siempre el nombre de una columna).

Funciones de agregado Resultado

SUM([ALL | DISTINCT] expresión) Total de los valores de la expresión numérica.

AVG([ALL | DISTINCT] expresión) Promedio de los valores de la expresión numérica.

COUNT([ALL | DISTINCT] expresión) Número de valores en la expresión.

COUNT(*) Número de filas seleccionadas.

MAX(expresión) Valor mayor de la expresión.

MIN(expresión) Valor menor de la expresión.

SUM, AVG, COUNT, MAX y MIN pasan por alto los valores NULL; COUNT(*) no lo hace. La palabra clave opcional DISTINCT se puede usar con SUM, AVG y COUNT para eliminar los valores duplicados antes de que se establezca la función de agregado (el valor predeterminado es ALL). SUM y AVG se pueden usar sólo con columnas numéricas, como, por ejemplo, las de los tipos de datos int, smallint, tinyint, decimal, numeric, float, real, Money y smallmoney. MIN y MAX no se pueden usar con tipos de datos bit. El resto de las funciones


86

de agregado que no sean COUNT(*) no se puede usar con los tipos de datos text e image. Con estas excepciones, las funciones de agregado se pueden usar con cualquier tipo de columna. Por ejemplo, en una columna del tipo de datos character, use MIN (mínimo) para buscar el valor inferior, el más cercano al principio del alfabeto: SELECT MIN(NomProducto) FROM Producto Éste es el conjunto de resultados: -------------------- Cable No 12

El tipo de resultado que devuelve una función de agregado puede tener una precisión mayor que las entradas, siempre que sea suficientemente grande para contener el valor del resultado de agregado. Por ejemplo, las funciones SUM o AVG devuelven un valor int cuando el tipo de datos de entrada es smallint o tinyint.

IMPORTANTE. Cuando en una lista de selección se usan funciones de agregado, la lista de selección sólo puede contener:

• Funciones de agregado • Columnas de agrupamiento de una cláusula GROUP BY • Una expresión que devuelve el mismo valor para cada

fila del conjunto de resultados, como, por ejemplo, una constante

Las funciones de agregado no se pueden usar en una cláusula WHERE. Sin embargo, una instrucción SELECT con funciones de agregado en su lista de selección incluye, a menudo, una cláusula WHERE que restringe las filas a las que se aplica la función de agregado. Si una instrucción SELECT incluye una cláusula WHERE (aunque no una cláusula GROUP BY), una función de agregado produce un valor individual para el subconjunto de filas especificado por la cláusula WHERE. Esto es cierto tanto si está operando en todas las filas de una tabla o sólo en un subconjunto de filas definido por una cláusula WHERE.

Agrupar filas con GROUP BY Cuando se usan sin una cláusula GROUP BY, las funciones de agregado sólo devuelven un valor de agregado para la instrucción SELECT. La cláusula GROUP BY se usa para producir valores de agregado para cada fila del conjunto de resultados. En este ejemplo se devuelve el costo de las existencias por negocio:


87

SELECT NomNegocio as ‘Nombre del Negocio’, SUM(Costo * Existencia) AS ‘Valor de las Existencias’ FROM Negocio INNER JOIN Producto ON Negocio.NegocioPk = Producto.NegocioPk AND Negocio.NegocioPk = 1 GROUP BY NomNegocio Este es el conjunto de resultados:

Nombre del Negocio Valor de las Exsitencias ------------------------------------------------ --------------------------------- Ferretera Abraham 4071.0000 A continuación de las palabras clave GROUP BY se coloca una lista de columnas, conocidas como las columnas de agrupamiento. La cláusula GROUP BY restringe las filas del conjunto de resultados y sólo hay una fila por cada valor distinto de las columnas o columna de agrupamiento. Cada fila del conjunto de resultados contiene los datos de resumen relacionados con el valor específico de sus columnas de agrupamiento. Hay restricciones en los elementos que se pueden especificar en la lista de selección cuando una instrucción SELECT contiene una cláusula GROUP BY. Los únicos elementos permitidos en la lista de selección son:

• Las columnas de agrupamiento. • Expresiones que devuelven un solo valor por cada valor

de las columnas de agrupamiento, como por ejemplo, las funciones de agregado que tienen un nombre de columna como uno de sus parámetros. Se conocen como agregados vectoriales.

Por ejemplo, TableX contiene:

ColumnA ColumnB ColumnC

1 Abc

5

1 Def 4


88

1 Ghi 9

2 Jkl 8

2 Mno 3

Si ColumnA se usa como la columna de agrupamiento, habrá dos filas en el conjunto de resultados, una que resuma la información del valor 1 y la otra que resuma la información del valor 2. Cuando la ColumnA es la columna de agrupamiento, la única forma en la que se podría hacer referencia a la ColumnB o ColumnC es en el caso de que fueran parámetros de una función de agregado que pudiera devolver un valor único por cada valor de ColumnA. La lista de selección puede incluir expresiones como, por ejemplo, MAX(ColumnB), SUM(ColumnC) o AVG(ColumnC):

SELECT ColumnA, MAX(ColumnB) AS MaxB, SUM(ColumnC) AS SumC FROM TableX GROUP BY ColumnA

Esta selección devuelve dos filas, una por cada valor exclusivo de ColumnA:

ColumnA MaxB SumC ----------- -------- ----------- 1 ghi 18 2 mno 11 (2 row(s) affected)

Sin embargo, no se permite tener sólo la expresión ColumnB en la lista de selección: SELECT ColumnA, ColumnB, SUM(ColumnC) AS SumC FROM TableX GROUP BY ColumnA

Puesto que GROUP BY sólo puede devolver una fila con el valor 1 en ColumnA, no hay forma de devolver los tres valores de ColumnB (abc, def y ghi) asociados con el valor 1 de ColumnA.


89

Nota: No es posible utilizar GROUP BY o HAVING en columnas ntext, text, image o bit a menos que se encuentren en una función que devuelva un valor que tiene otro tipo de datos, como SUBSTRING o CAST.

Utilizar DISTINCT con funciones de agregado La palabra clave DISTINCT es opcional con SUM, AVG y COUNT. Cuando se usa DISTINCT, los valores duplicados se eliminan antes de que se calcule la suma, promedio o recuento. Si usa DISTINCT, la expresión sólo debe constar del nombre de una columna. No puede incluir una expresión aritmética. En la consulta siguiente se devuelven los precios promedio de los libros del negocio especificado (sin los valores duplicados). SELECT AVG(DISTINCT PrecioVenta) FROM Producto WHERE NegocioPk = 10

----------------------------------------------------- 150.25

Sin DISTINCT, la función AVG busca el precio promedio de todos los títulos del negocio especificado. SELECT AVG(PrecioVenta) FROM Producto WHERE NegocioPk = 10

Este es el conjunto de resultados: ------------------------------------------- 100.33333333333333

Nota: Los valores NULL de una columna se pasan por alto mientras opera una función de agregado.

Utilizar COUNT(*) COUNT(*) no requiere un parámetro expresión porque, por definición, no usa información de ninguna columna en particular. Cuenta el número total de filas que cumplen las calificaciones de la consulta. COUNT(*) devuelve el número de filas que coinciden con las condiciones de búsqueda especificadas en la consulta, sin eliminar los duplicados. Cuenta cada fila por separado, incluidas las filas que contienen valores NULL.

En esta consulta se busca el número total de productos de la tabla Producto: SELECT COUNT(*)


90

FROM Producto

Este es el conjunto de resultados: ----------- 5 (1 row(s) affected)

COUNT(*) se puede combinar con otras funciones de agregado. Esta consulta muestra COUNT(*) combinado con una función AVG en la que ambas funciones de agregado sólo agregan los datos de las filas que cumplen la condición de búsqueda de la cláusula WHERE. SELECT COUNT(*), AVG(PrecioVenta) FROM Producto WHERE PrecioVenta > 100 Este es el conjunto de resultados:

----------- ----------------------------------------------- 3 350.0 (1 row(s) affected)

Elegir filas con la cláusula HAVING La cláusula HAVING establece las condiciones de la cláusula GROUP BY de la misma forma que WHERE interactúa con SELECT. Mientras que las condiciones de búsqueda de WHERE se aplican antes de que se produzca la operación de agrupamiento, las condiciones de búsqueda de HAVING se aplican después. La sintaxis de la cláusula HAVING es exactamente igual que la sintaxis de la cláusula WHERE, salvo que en HAVING puede contener funciones de agregado. Las cláusulas HAVING pueden hacer referencia a cualquiera de los elementos que aparecen en la lista de selección. En esta consulta se buscan los productos cuyas ventas sean superiores a $200 pesos:

SELECT ProductoPk, SUM(Importe) Total FROM DetalleFactura GROUP BY ProductoPk HAVING SUM(Importe) > 200 Este es el conjunto de resultados:

ProductoPk Total ---------------- ----------------------------------------------------- 2 300.0 (1 row(s) affected)


91

• La comprensión de la secuencia correcta en la que se

aplican las cláusulas WHERE, GROUP BY y HAVING ayuda a codificar consultas que sean eficientes: La cláusula WHERE se utiliza para filtrar las filas que resultan de las operaciones especificadas en la cláusula FROM.

• La cláusula GROUP BY se usa para agrupar el resultado de la cláusula WHERE.

• La cláusula HAVING se usa para filtrar las filas del resultado agrupado.

Es más eficiente especificar en la cláusula WHERE las condiciones de búsqueda que se pueden establecer antes o después de la operación de agrupamiento. Esto reduce el número de filas que tienen que agruparse. Las únicas condiciones de búsqueda que se deben especificar en la cláusula HAVING son aquellas que se deben aplicar una vez que se hayan realizado las operaciones de agrupamiento. A continuación se muestra un ejemplo de una cláusula HAVING sin funciones de agregado. Agrupa por Unidad de Medida las filas de la tabla Producto y elimina los tipos que no empiezan con la letra "p".

SELECT UnidadMedida FROM Producto GROUP BY UnidadMedida HAVING UnidadMedida LIKE 'p%' Este es el conjunto de resultados:

UnidadMedida -------------------- Pieza

Cuando en HAVING se incluyen varias condiciones, se combinan mediante AND, OR o NOT.

Resumir Datos con COMPUTE y COMPUTE BY Genera totales que aparecen como columnas de resumen adicionales al final del conjunto de resultados. Cuando se utiliza con BY, la cláusula COMPUTE genera interrupciones de control y subtotales en el conjunto de resultados. Puede especificar COMPUTE BY y COMPUTE en la misma consulta. Los valores de resumen generados por COMPUTE aparecen como conjuntos separados de resultados en los resultados de la consulta. Los resultados de una consulta que incluya una cláusula COMPUTE son como un informe de ruptura de control, que es un informe cuyos valores de resumen se controlan mediante las agrupaciones, o rupturas, que se especifique. Puede producir


92

valores de resumen para grupos, y puede calcular más de una función de agregado para el mismo grupo. Cuando se especifica COMPUTE con la cláusula opcional BY, hay dos conjuntos de resultados por cada grupo que califica la instrucción SELECT:

• El primer conjunto de resultados de cada grupo tiene el

conjunto de filas de detalles que contienen la información de la lista de selección para ese grupo.

• El segundo conjunto de resultados de cada grupo tiene una fila que contiene los subtotales de las funciones de agregado especificadas en la cláusula COMPUTE para ese grupo.

Cuando se especifica COMPUTE sin la cláusula opcional BY, hay dos conjuntos de resultados para la instrucción SELECT:

Función de agregado de filas

Resultado

AVG

Promedio de los valores de la expresión numérica.

COUNT

Número de filas seleccionadas.

MAX

Valor mayor de la expresión.

MIN

Valor menor de la expresión.

STDEV

Desviación típica estadística de todos los valores de la expresión.

STDEVP

Desviación típica estadística del llenado para todos los valores de la expresión.

SUM

Total de los valores de la expresión numérica.

VAR

Varianza estadística de todos los valores de la expresión.

VARP

Varianza estadística del llenado para todos los valores de la expresión.


93

• El primer conjunto de resultados de cada grupo tiene todas las filas de detalles que contienen información de la lista de selección.

• El segundo conjunto de resultados tiene una fila que contiene los totales de las funciones de agregado especificadas en la cláusula COMPUTE.

Sintaxis

[ COMPUTE

{ { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } ( expression ) } [ ,...n ]

[ BY expression [ ,...n ] ] ]

Argumentos

AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM Especifica la función de agregado que se va a realizar. Con la cláusula COMPUTE se utilizan estas funciones de agregado de filas.

No existe ninguna función equivalente a COUNT(*). Para localizar la información de resumen que producen GROUP BY y COUNT(*), utilice una cláusula COMPUTE sin BY. Estas funciones omiten los valores NULL. No se permite utilizar la palabra clave DISTINCT en funciones de agregado de filas cuando se especifican con la cláusula COMPUTE.

( expression ) Una expresión, por ejemplo, el nombre de una columna en la que se realiza el cálculo. El argumento expression debe aparecer en la lista de selección y se debe especificar exactamente igual que una de las expresiones de la lista de selección. En expression no se puede utilizar un alias de columna especificado en la lista de selección.

Nota No se puede especificar los tipos de datos ntext, text o image en una cláusula COMPUTE o en una cláusula COMPUTE BY. BY expression Genera interrupciones de control y subtotales en el conjunto de resultados.


94

expression es una copia exacta de una order_by_expression de la cláusula ORDER BY asociada. Normalmente se trata de un nombre de columna o de un alias de columna. Se pueden especificar varias expresiones. Si se enumeran varias expresiones después de BY, se divide un grupo en subgrupos y se aplica la función de agregado en cada nivel del agrupamiento. Si utiliza COMPUTE BY, también debe utilizar una cláusula ORDER BY. Las expresiones deben ser idénticas a las enumeradas después de ORDER BY o un subconjunto de las mismas, y deben estar en la misma secuencia. Ejemplo: La instrucción SELECT siguiente usa una cláusula COMPUTE simple para producir el total de la suma del PrecioVenta y las Existencias de la tabla de Productos:

SELECT FamiliaPk, PrecioVenta, Existencia FROM Producto ORDER BY FamiliaPk COMPUTE SUM(PrecioVenta), SUM(Existencia)

En esta consulta se agrega la palabra clave opcional BY a la cláusula COMPUTE para producir subtotales para cada grupo:

SELECT FamiliaPk, PrecioVenta, Existencia FROM Producto ORDER BY FamiliaPk COMPUTE SUM(PrecioVenta), SUM(Existencia) BY FamiliaPk Recomendaciones para Optimizar Consultas Algunas consultas utilizan intrínsecamente recursos de forma intensiva. Esto está relacionado con aspectos fundamentales de bases de datos e índices. Estas consultas no son ineficaces, debido a que el optimizador implementará las consultas de la forma más eficaz posible. Sin embargo, utilizan recursos de forma intensiva y la naturaleza orientada a conjuntos de Transact-SQL puede hacer que parezcan ineficaces. Ningún grado de inteligencia del optimizador de consultas puede eliminar el costo intrínseco de recursos de estas construcciones. Son costosas en comparación con una consulta menos compleja. Aunque Microsoft SQL Server utilizará un plan de acceso óptimo, está limitado por lo que es fundamentalmente posible. Por ejemplo, los siguientes tipos de consultas pueden utilizar recursos de forma intensiva:

• Consultas que devuelven conjuntos de resultados de gran

tamaño. • Cláusulas WHERE no únicas

Sin embargo, las recomendaciones para optimizar consultas y mejorar su rendimiento incluyen:


95

• Agregar más memoria (especialmente si el servidor ejecuta muchas consultas complejas y varias consultas se ejecutan lentamente).

• Ejecutar SQL Server en un equipo con más de un procesador. Varios procesadores permiten que SQL Server utilice consultas en paralelo.

• Considere la posibilidad de volver a escribir la consulta. o Si la consulta utiliza cursores, determine si se puede

escribir la consulta de cursor de forma más eficaz con un tipo de cursor más eficaz, como un cursor de desplazamiento rápido hacia delante, o una única consulta. Las consultas únicas normalmente mejoran las operaciones de cursor. Debido a que un conjunto de instrucciones de cursor suele constituir una operación de ciclo externo, en la que cada fila del ciclo externo se procesa una vez con una instrucción interna, considere la posibilidad de utilizar en su lugar una instrucción GROUP BY o CASE, o una subconsulta.

o Si una aplicación utiliza un ciclo, considere la

posibilidad de colocar el ciclo en la consulta. A menudo, una aplicación contendrá un ciclo que, a su vez, contiene una consulta parametrizada que se ejecuta muchas veces y será necesario realizar un viaje de ida y vuelta en la red entre el equipo que ejecuta la aplicación y SQL Server. En su lugar, cree una sola consulta más compleja con una tabla temporal. Sólo necesita un viaje de ida y vuelta en la red, y el optimizador de consultas puede optimizar mejor la consulta única.

o No utilice varios alias para una sola tabla en la

misma consulta para simular la intersección de índices. Ya no es necesario debido a que SQL Server tiene en cuenta automáticamente la intersección de índices y puede utilizar varios índices en la misma tabla de la misma consulta. Por ejemplo, dada la consulta:

SELECT * FROM Factura WHERE ClientePk BETWEEN 1 AND 250 AND FecFactura BETWEEN '2001/01/01' AND '2001/01/02'

SQL Server puede utilizar índices sobre las columnas ClientePk y FecFactura, y después, realizar una coincidencia hash entre los dos subconjuntos para obtener la intersección de índices.

4.3.4. Lenguaje de control de datos.


96

Un Lenguaje de Control de Datos (DCL por sus siglas en inglés: Data Control Language) es un lenguaje proporcionado por el sistema de gestión de base de datos que incluye una serie de comandos SQL que permiten al administrador controlar el acceso a los datos contenidos en la base de datos.

Bibliografía.

1. Henry F. Korth, Abraham Silbertchatz. Fundamentos de Bases de datos, 4/E. Mc Graw Hill.


97

2. C.J. date. Introducción a los Sistemas de Bases de Datos, 7ª. Ed. Prentice Hall.

3. David M. Kroenk. Procesamiento de bases de datos, fundamentos,

diseño e instrumentación, 5ª. Ed. Prentice Hall.

4. james R. Groft, Paul N. Weinberg. LAN TIMES, guia SQL. Osborne, Mc Graw Hill.

5. Adoración de Miguel, Mario Piattini. Fundamentos y modelos de

datos. Alfa-Omega Ramma.

6. Adoración de Miguel, Paloma Martínez. Diseño de base de datos, problemas resueltos. Alfa-Omega Ramma.

7. Silberschtz, Korth. Fundamentos de Bases de Datos. Mc Graw Hill.

Referencias en Internet http://www.itapizaco.edu.mx/paginas/base_de_datos/algebrarelacional.html http://es.wikipedia.org/wiki/Lenguaje_de_programaci%C3%B3n http://es.wikipedia.org/wiki/Lenguaje_de_Manipulaci%C3%B3n_de_Datos www.bivitec.org.mx ww.javasun.com www.microsoft .com

librofbd

Documents