Apuntes de Estructura de Datos y de la Informaci´on ´ Miguel Angel Vilela Garc´ıa 20 de abril de 2007

´Indice general

1. Introducci´ on a las bases de datos

2

1.1. Procesamiento tradicional de datos . . . . . . . . . . . . . . . . . . . . . .

2

1.2. Sistemas de Bases de Datos . . . . . . . . . . . . . . . . . . . . . . . . . .

3

1.3. Abstracci´on de la informaci´on e independencia de los datos . . . . . . . . .

4

1.4. Modelos de datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5

1.5. Lenguajes de bases de datos . . . . . . . . . . . . . . . . . . . . . . . . . .

7

1.6. Usuarios de un sistema de base de datos . . . . . . . . . . . . . . . . . . .

8

1.7. Estructura de un sistema de bases de datos . . . . . . . . . . . . . . . . . .

8

1.8. Arquitectura de aplicaciones . . . . . . . . . . . . . . . . . . . . . . . . . .

9

2. El Modelo Relacional

10

2.1. Introducci´on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.2. Estructura del modelo relacional . . . . . . . . . . . . . . . . . . . . . . . . 10 2.3. Componentes del Modelo Relacional . . . . . . . . . . . . . . . . . . . . . . 12 2.4. Valores nulos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 ´ 3. Algebra Relacional

13

3.1. Introducci´on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.2. Operadores esenciales . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.3. Operadores derivados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 3.4. Modificaci´on de la base de datos . . . . . . . . . . . . . . . . . . . . . . . . 16 3.5. Operaci´on de complemento . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 1

2 ´ 3.6. Potencia expresiva del Algebra Relacional . . . . . . . . . . . . . . . . . . 16 3.7. Propiedades de los operadores . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.8. Ampliaci´on de los operadores para valores nulos . . . . . . . . . . . . . . . 18 3.9. Vistas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.10. Relaciones de integridad . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 4. C´ alculo relacional

21

4.1. Introducci´on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 4.2. Introducci´on informal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 4.3. C´alculo restringido de tuplas . . . . . . . . . . . . . . . . . . . . . . . . . . 23 4.4. Definici´on formal del c´alculo relacional de tuplas . . . . . . . . . . . . . . . 24 4.5. C´alculo relacional de dominios . . . . . . . . . . . . . . . . . . . . . . . . . 24 5. El lenguaje SQL

26

5.1. Introducci´on . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 5.2. Recuperaci´on de datos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 5.3. Instrucciones de actualizaci´on de datos . . . . . . . . . . . . . . . . . . . . 36 5.4. Instrucciones de definici´on de datos . . . . . . . . . . . . . . . . . . . . . . 37 6. Introducci´ on a la normalizaci´ on

41

6.1. Dise˜ nos equivalentes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 6.2. Descomposici´on reversible por yunci´on . . . . . . . . . . . . . . . . . . . . 42

´ Miguel Angel Vilela Garc´ıa

ADVERTENCIA Estos apuntes son el resultado de transcribir casi textualmente las notas que he tomado durante las clases y a˜ nadir algunos recortes de otras fuentes proporcionadas por los profesores de la asignatura. En ning´ un caso deben usarse estos apuntes como substituto de ninguna otra fuente. Este documento debe considerarse de baja calidad a menos que un profesor de la asignatura lo contradiga. Estos apuntes pueden ser libremente copiados y redistribuidos en forma digital, optomec´anica o cualquier otra, siempre que se conserve esta libertad de redistribuci´on. El autor no asume responsabilidad ninguna relacionada con el uso de estos apuntes. Agradecimientos a Jacobo de Vera Hern´andez por aportar sus notas de clase del curso 2002–2003 y el texto del tema del lenguaje SQL.

TEMA 1

Introducci´on a las bases de datos

1.1.

Procesamiento tradicional de datos

El procesamiento tradicional de datos se caracteriza por una proliferaci´on de ficheros, espec´ıdifico cada uno de ellos para una determinada aplicaci´on, implementada para satis´ facer la demanda de una empresa. Este era un ambiente desordenado y heterog´eneo, en el que se encontraban diferentes programas, diferentes programadores, diferentes formatos y diferentes lenguajes; todos ellos orientados m´as a los procedimientos que a los datos. Los programas de aplicaci´on se desarrollan de frma independiente y en ocasiones los datos se duplican.

1.1.1.

Desventajas

Redundancia de informaci´ on: un mismo dato sol´ıa estar repetido varias veces, en varios ficheros. Esto aumenta el coste de almacenamiento y acceso a los datos. Existe la posibilidad de inconsistencia en los datos, ya que cada modificaci´on debe afectar a todas las copias de cada dato. Dificultaba el acceso a los datos. No permite un acceso eficiente. Ante una necesidad nueva (previamente imprevista) de acceso a los datos hab´ıan dos alternativas: implementar un nuevo programa que obtuviera los datos a partir de los ficheros, o bien extrar los datos manualmente a partir de la salida de alg´ un otro programa preexistente. El problema de la primera opci´on es que requer´ıa tiempo de implementaci´on y la mayor´ıa de las veces no val´ıa la pena. Dependencia excesiva del formato (asilamiento de datos). La informaci´on en los ficheros estaba en varios formatos, no est´andares. Estos formatos deb´ıan ser conocidos por los desarrolladores y de los programas que acced´ıan a los datos. Confidencialidad de la informaci´ on (privacidad). No era conveniente que todos los usuarios tuvieran acceso a todos los datos.

2

1.2. SISTEMAS DE BASES DE DATOS

3

Mantenimiento de la integridad. Las condiciones de integridad son restricci´ones (l´ogicas, formales) que deben satisfacer los datos. Garantizar que se respeten estas restricciones es responsabilidad del programador de aplicaciones. Problemas de atomicidad. Las transacciones son secuencias de manipulaciones que deben realizarse en su totalidad o, en caso de no ser posible, no realizarse ninguna. Reestructuraci´ on de la informaci´ on. Resultaba complicado a˜ nadir nuevos campos en tablas existentes.

1.1.2.

Ventajas

Para su implementaci´on s´olo se requiere un ordenador (PC), un programador, un compilador y capacidad de almacenamiento para los ficheros de datos.

1.2.

Sistemas de Bases de Datos

Un sistema de bases de datos es un sistema de informaci´on orientado hacia los datos, que pretende recuperar y almacenar la informaci´on de manera eficiente y c´omoda. Surge en un intento de resolver las dificultades del procesamiento tradicional de datos; teniendo en cuenta que los datos suelen ser independientes de las aplicaciones.

1.2.1.

Ventajas

Datos independientes de los procedimientos. El usuario tiene una visi´on abstracta de lso datos, sin necesidad de ning´ un conocimiento sobre la implementaci´on de los ficheros de datos, ´ındices, etc. Mejor disponibilidad de los datos: es f´acil recuperar y almacenar la informaci´on, aunque sea de un modo que no est´e previsto. Mejor eficiencia en el almacenamiento y la recuperaci´on de datos. Mayor coherencia de la informaci´ on , mayor facilidad para compartir los datos. A su vez, los datos est´an mejor normalizados. Los usuarios pueden acceder de manera sencilla y r´apida a la informaci´on. Mayor flexibilidad para atender demandas cambiantes.

1.2.2.

Desventajas

Hay que pagar por el sistema gestor de bases de datos. Aunque en la actualidad existen sistemas gestores de bases de datos de libre distribuci´on, los sistemas comerciales tienen precios elevados.

´ Miguel Angel Vilela Garc´ıa

´ DE LA INFORMACION ´ E INDEPENDENCIA DE LOS DATOS 1.3. ABSTRACCION

4

Consume muchos recursos: memoria secundaria, primaria y procesador. Se requiere personal especializado y tiempo para poner en marcha el sistema gestor de bases de datos y utilizarlo. Este personal debe tener conocimientos de programaci´on y SQL. Suelen ser poco eficientes en tiempo. Un Sistema Gestor de Bases de Datos (SGBD/DBMS) es el conjunto de herramientas software que permiten la creaci´on y acceso de los datos de manera conveniente y eficiente. Los SGBD tiene varios tipos de funciones: Funciones de definici´ on de datos: se encargan de crear, borrar y modificar objetos (tales como tablas, vistas, ´ındices) para almacenar informaci´on, acelerar el acceso mediante ´ındices, reestructurar el esquema de la base de datos, hacer consultas a la totalidad de los datos. El esquema de la base de datos representa la estructura (propiedades invariantes) seg´ un la cual se almacena la informaci´on. A una instancia del esquema (contenido de informaci´on en un momento dado) se denomina extensi´on. Funciones de manipulaci´ on de datos: para a˜ nadir (insertar), modificar (actualizar), recuperar y eliminar datos. Funciones de control de datos: para controlar el acceso concurrente, cesi´on o rescinci´on de privilegios levantamiento del sistema, altas y bajas de usuarios. Estas funciones son pesadas de implementar.

1.3.

Abstracci´ on de la informaci´ on e independencia de los datos

´ Estas son dos caracter´ısticas fundamentales de los SGBD. Los SGBD deben proporcionar un ambiente de trabajo que permite recuperar los datos de modo eficiente y c´omodo. Esto implica la utilizaci´on estructuras de datos complejas. Estas estructuras son ocultadas al usuario, de modo que se proporciona abstracci´on de la informaci´on. Cuanto mayor sea la abstracci´on de la informaci´on mejor se oculta al usuario el m´etodo de acceso y la organizaci´on de los datos. La organizaci´on de la base de datos se dividi´o en tres niveles: Nivel f´ısico: casi sin abstracci´on; se describen los datos y sus relaciones, al nivel f´ısico del almacenamiento secundario. Nivel conceptual: con bastante abstracci´on; describe todo los datos y sus relaciones, a nivel l´ogico. Nivel de visi´ on: proporciona m´ ultiples vistas de la base de datos a los usuarios.

´ Miguel Angel Vilela Garc´ıa

1.4. MODELOS DE DATOS

5

Para cada nivel, la base de datos tiene un esquema diferente: Esquema f´ısico (interno): definido por el propio SGBD, sin intervenci´on del administrador. Esquema conceptual: describe los datos independientemente del SGBD, y tambi´en c´omo almacena los datos en el SGBD. Esquema externo (vista): pueden haber varios. Subesquemas. Algunos autores sugieren dividir el esquema conceptual en “esquema can´onico” y “esquema conceptual”; ya que el primero comprende las partes independientes del SGBD y el segundo las partes dependientes del SGBD. . . . sin tener que cambiar el esquema del nivel superior. Hay dos tipos de independencia: F´ısica: es la capacidad para modificar el esquema f´ısico sin tener que cambiar el esquema conceptual. L´ ogica: es la capacidad para modificar el esquema conceptual sin tener que cambiar los subesquemas de las vistas. Las aplicaciones cliente son m´as sensibles a los cambios en el esquema conceptual que alos cambios en el esquema f´ısico. Por lo tanto es m´as f´acil para el SGDB dar independencia f´ısica que independencia l´ogica.

1.4.

Modelos de datos

Un analista observa una realidad, crea en su mente un modelo conceptual y escribe un esquema conceptual. En el u ´ ltimo paso se utiliza un modelo de datos, que no es sino un conjunto de herramientas conceptuales que permiten describir los datos, su sem´antica, sus relaciones y las condiciones de integridad. Uno modelo de datos rico permitir´a escribir un esquema conceptual detallado, mientras que un modelo de datos pobre s´olo permitir´a escribir un esquema conceptual basto. G Seg´ un su expresividad los modelos de datos se pueden clasificar en modelos f´ısicos (permiten describir el esquema f´ısico) y modelos l´ogicos (permiten describir el esquema conceptual). A su vez los modelos l´ogicos se dividen en modelos orientados a objetos (entidad/relaci´on E/R, sem´antico, infol´ogico) y modelos orientados a registros (relacional, jer´arquico, en red).

1.4.1.

Modelo E/R (Entidad/Relaci´ on)

La realidad se percibe como un conjunto de entidades distintas entre las cuales es posible establecer relaciones. El concepto de entidad es equivalente al concepto de clase en programaci´on orientada a objetos. Dada una entidad, sus atributos son caracter´ısticas que permiten . . . ´ Miguel Angel Vilela Garc´ıa

1.4. MODELOS DE DATOS

DNI

N

6

CDC

CLIENTE

SLD

NC

POSEE

CUENTA

Figura 1.1: Modelo E/R

1.4.2.

Modelo relacional

La realidad se percibe como un conjunto de registros de diferentes clases, que se agrupan en tablas (tambi´en llamadas relaciones). CLIENTE

DNI N 1111 Pedro 2222 Juan 3333 Pilar

CUENTA

NC SLD 1 100000 2 1000 3 200

POSEE

CDC La Laguna Santa Cruz de Tenerife Candelaria

DNI NC 1111 1 1111 2 2222 1 3333 3 Figura 1.2: Modelo relacional

Para esta base de datos el esquema ser´ıa: CLIENTE (DNI, N, CDC) CUENTA (NC, SLD) POSEE (DNI, NC) Se podr´ıa eliminar la tabla POSEE a˜ nadiendo una columna DNI a la tabla CUENTA, o bien a˜ nadiendo una columna NC a la tabla CLIENTE. Sin embargo esto producir´ıa redundancia en la informaci´on, lo cual no es deseable. El pero dise˜ no contemplar´ıa una u ´ nica tabla, de modo que la redundancia ser´ıa mucho mayor.

´ Miguel Angel Vilela Garc´ıa

1.5. LENGUAJES DE BASES DE DATOS

1.4.3.

7

Modelo en Red

La realidad se modeliza utilizando registros que pueden estar conectados de forma arbitraria.

1.4.4.

Modelo jer´ arquico

La realidad se percibe como un conjunto de registros entre los cuales es posible establecer alg´ un tipo de dependencia jer´arquica. Es apropiado para realidades que son jer´arquicas en su propia naturaleza, aunque tambi´en es aplicable a realidades no jer´arquicas.

1.5.

Lenguajes de bases de datos

Los DBMS proporcionan a los usuarios diferentes tipos de lenguajes de bases de datos para interactuar con ellos. Estos lenguajes pueden clasificarse en: Lenguajes de definici´ on de datos , m´as conocidos por su acr´onimo anglosaj´on DDL (Data Definition Language). Permiten especificar el esquema de la base de datos, modificar la estructura del esquema, especificar las condiciones de integridad, hacer consultas a la totalidad de los datos y mejorar el acceso a la informaci´on. Con estos lenguajes se obtiene el esquema can´onico. Al ejecutar instrucciones del DDL los datos se almacenan/consultan en el diccionario de la base de datos. Este diccionario es una estructura que almacena metadatos (informaci´on acerca de los datos). Este diccionario es fundamental, ya que para cualquier operaci´on es lo primero que debe consultarse. Lenguajes de manipulaci´ on de datos , m´as conocidos por su acr´onimo anglosaj´on DML (Data Manipulation Language), o por otros nombres como lenguajes de acceso de datos, de gesti´on de datos o de consulta. Permiten realizar las operaciones de consulta, actualizaci´on insercci´on y borrado de datos. Seg´ un distintos criterios, los DML se pueden clasificar en: procedimentales y declarativos, o bien en hu´esped o independiente. Los lenguajes procedimentales permiten especificar en el momento de recuperar informaci´on no s´olo qu´e informaci´on se desea recuparar sino tambi´en c´omo se desea hacer la operaci´on, mientras que los lenguajes declarativos s´olo permiten especificar qu´e informaci´on se desea recuparar, pero no el modo de hacerlo. ´ El Algebra Relacional es un lenguaje procedimental puro, el C´alculo de T-uplas es un lenguaje declarativo puro, y el lenguaje SQL es un h´ıbrido. Un lenguaje hu´esped (host language) es aquel cuyas sentencias han de estar inmersas dentro de un lenguaje de programaci´on de prop´osito general. Hay dos formas de hacerlo: (a) ampliando la sintaxis del DML, lo cual precisa de un precompilador que traduzca el c´odigo mixto en c´odigo de lenguaje anfitri´on puro; o bien (b) usando una API implementada por una librer´ıa, como ODBC. ´ Miguel Angel Vilela Garc´ıa

1.6. USUARIOS DE UN SISTEMA DE BASE DE DATOS

8

Un lenguaje independiente o autocontenido (self-contained) es aquel cuyas instrucciones son suficientes para generar un programa ejecutable. El lenguaje SQL no es independiente, pero los DBMS proporcionan extensiones para permitir cierto grado de programabilidad. Los DML tambi´en pueden utilizarse de dos modos: conversacional y diferido. Se dice que un lenguaje se utiliza en modo conversacional si el usuario puede ejecutar las sentencias de modo interactivo a trav´es de un int´erprete, mientras que el modo diferido se refiere al uso de las sentencias postpuestas en el tiempo para ejecutarlas por lotes, como es el caso del SQL inmerso en C.

1.6.

Usuarios de un sistema de base de datos

La variedad de usuarios de un sistema de base de datos es tal que no existe una clasificaci´on exacta de los mismos, si bien se distinguen las siguientes clases: Usuarios normales: operadores de los programas de aplicaci´on, usuarios ingenuos o inexpertos. Estos usuarios por lo general poseen pocos conocimientos de inform´atica. Interaccionan con el sistema de base de datos a trav´es de una aplicaci´on previamente desarrollada para tal finalidad. Usuarios sofisticados: atienden peticiones imprevistas de informaci´on, mediante un int´erprete de consultas interactivo. Usuarios expertos: interaccionan con el sistema de una manera no tracidiona, haciendo usos muy avanzados de los DBMS que los sacan de su marco de trabajo tradicional. Administrador de la base de datos: tambi´en conocidos por su acr´onimo anglosaj´on BDA (Data Base Administrator). Se trata de un usuario con muchos conocimientos de inform´atica, generalmente un inform´atico o analista. Tiene el control centralizado de la base de datos y es el responsable de su buen funcionamiento. Es el encargado de crear del esquema de la base de datos, modificarlo, definir condiciones de integridad, mejorar el acceso a los datos (´ındices) y el mantenimiento rutinario. Desarrolladores de aplicaciones: usuarios con gran conocimiento en inform´atica, pues como su nombre indica son los encargados de desarrollar los programas de aplicaci´on, bas´andose en el esquema proporcioinado por el DBA. La interacci´on con la base de datos puede hacerse de dos modos: OLTP (On-Line Transactioin Process) y OLAP (On-Line Analitic Process). El segundo aplica herramientas estad´ısticas sobre los datos (ingenier´ıa de datos).

1.7.

Estructura de un sistema de bases de datos

Los DBMS son herramientas complejas y por ello se desarrollan dividi´endolas en m´odulos. La divisi´on m´as importante separa el gestor de almacenamiento del procesador de consultas. ´ Miguel Angel Vilela Garc´ıa

1.8. ARQUITECTURA DE APLICACIONES

9

El gestor de almacenamiento proporcionala interfaz entre los programas de aplicaci´on y los datos almacenados en el almacenamiento secundario. Controla los ficheros de datos, los ´ındices y el diccioinario. Se divide a su vez en: Gestor de transacciones: se encarga de garantizar la atomicidad de las transacciones y controla el acceso concurrente para garantizar el aislamiento de las transacciones. Gestor de autorizaci´ on e integridad: se encarga de verificar que se satisfacen las condicioines de integridad y de controlar el acceso a los datos. Gestor de archivos: gestiona el espacio en disco para guardar la informaci´on, seg´ un las estructuras de datos. Gestor de memoria intermedia: acelera la recuperaci´on de la informaci´on. El procesador de consultas se encarga de procesar las consultas recibidas a trav´es del int´erprete o de la API. Se compone, entre otras cosas, de un motor de evaluaci´on de consultas que las ejecuta, un optimizador de consultas que busca la mejor consulta equivalente y el precompilador de DML.

1.8.

Arquitectura de aplicaciones

No es habitual ejecutar la aplicaci´on cliente en la misma m´aquina que ejecuta el DBMS. Lo normal es utilizar una arquitectura cliente–servidor en la que el programa de aplicaci´on se comunica con el DBMS a trav´es de una red. Esta arquitectura tiene dos variantes: 2 capas: la m´aquina cliente ejecuta la aplicaci´on, que a atrav´es de la API hace llamadas al DBMS que se ejecuta en el servidor. 3 capas: a˜ nade un nivel intermedio a la variante anterior: hay un servidor de aplicaciones que contiene la aplicaci´on cliente, normalmente formada por formularios, que env´ıan las consultas al servidor donde se ejecuta el DBMS.

´ Miguel Angel Vilela Garc´ıa

TEMA 2

El Modelo Relacional

2.1.

Introducci´ on

A principio de la d´ecada de los 70, E. F. Cold propone un modelo te´orico con un fuerte fundamento matem´atico. Tras su art´ıculo, comienzan a desarrollarse SGBDs relacioinales. IBM desarrolla DB2, la Universidad de Berkeley desarrolla INGRES y surge tambi´en ORACLE (1979). Desde los a˜ nos 80 el modelo relacional ha desbancado a los m´odelos de datos jer´arquico y en red, que dominaban el panorama.

2.2.

Estructura del modelo relacional

Una base de datos relacional (BDR) est´a formada por un conjunto de tablas o relaciones, cada una con un nombre u ´ nico, compuestas por filas (tambi´en llamadas tuplas). Las tablas tienen atributos, cuyo orden es importante al contrario que el orden de las filas. El contenido de la tabla se denomina extensi´on o instancia y constituye la parte vol´atil. Los atributos tienen asociado un dominio, i.e. un conjunto de valores permitidos, que pueden ser infinitos (N, R). Formalmente, una tabla es un subconjunto finito del producto cartesiando de los dominios de sus atributos. R ⊆ D 1 × D2 × . . . × D n Es importante notar que por ser un conjunto, no hay t-uplas repetidas en una tabla. Sin embargo, los SGBDs comerciales s´ı permiten filas duplicadas, pero te´oricamente no se utilizan. Un atributo se dice de tipo at´omico si no se puede descomponer en unidades m´as peque˜ nas dotadas de significado. La edad es at´omica, pero no la direcci´on postal ya que se puede descomponer. En adelante asumiremos que todos los atributos son at´omicos; esto es una restricci´on inherente al modelo relacional. Cuando esto es as´ı se dice que la relaci´on est´a en primera forma normal. 10

2.2. ESTRUCTURA DEL MODELO RELACIONAL

11

Se llama grado de una tupla al n´ umero de componentes que la componen. An´alogamente el grado de una tabla es el grado de sus tuplas. El esquema de la tabla se denomina tambi´en intensi´on y al contenido de la tabla instancia o extensi´on. El contenido de una base de datos el vol´atil. Un esquema de base de datos est´a compuesto por (al menos) los esquemas de una o m´as tablas. Adem´as de los esquemas de las tablas, el esquema de la base de datos debe contener las condiciones de integridad. En adelante utilizaremos la siguiente base de datos: CLIENTE DEPOSITO PRESTAMO SUCURSAL

(DNI, N, CDC) (DNI, CS, NC, SLD) (DNI, CS, NP, I) (CS, NS, CDS)

CLAVE CLAVE CLAVE CLAVE

= = = =

(DNI) (DNI, CS, NC) (DNI, CS, NP) (CS)

Si se utiliza un atributo que est´a en varias tablas, se debe eliminar la ambiguedad anteponiendo el nombre de la tabla (CLIENTE.DNI). Si a´ un as´ı existiera ambiguedad por tratarse de la misma tabla perteneciente a distintos usuarios, se antepone el nombre del usuario creador. Una superclave es un conjunto de atributos que permite identificar de forma u ´nica los registros de un fichero o las filas de una tabla. Una clave es una superclave minimal; i.e.: k es una clave si y s´olo si k es superclave y adem´as ∀ k ′ ⊂ k k no es superclave. A la clave que se usa habitualmente se la denomina clave primaria, las dem´as son claves alternativas. Si se eliminara DNI de la clave de la tabla DEPOSITO se eliminar´ıa la posibilidad de que varios usuarios compartan una cuenta, ya que si lo hicieraon (CS,NC) no ser´ıa una clave. Para matematizar el modelo recurrimos a variables y notaci´on de conjuntos: t∈R t[DNI] t1 t = (t1 , t2 , . . . , tn ) t1 , t2

la tupla t est´a en la relaci´on R atributo DNI de la tupla t primer atributo de la tupla t dos tuplas diferentes con nombres similares

Sea una relaci´on R(r), donde r es el conjunto de sus atributos (i.e. el esquema de R), entonces k ⊆ r es una superclave de R sii (a) t1 [k] 6= t2 [k] ∀ t1 6= t2

t1 , t2 ∈ R

(b) ∀ t1 , t2 ∈ R t1 [k] = t2 [k] =⇒ t1 = t2 (c) 6 ∃ t1 , t2 ∈ R : t1 [k] = t2 [k] ∧ t1 6= t2 Un conjunto de atrigutos se dice que es una clave ajena si referencia la clave primaria de otra relaci´on. Por ejemplo, DNI es clave ajena en DEPOSITO porque es clave primaria de CLIENTE.

´ Miguel Angel Vilela Garc´ıa

2.3. COMPONENTES DEL MODELO RELACIONAL

2.3.

12

Componentes del Modelo Relacional

El Modelo Relacional est´a formado por operadores que permiten manipular las estructuras de datos y condiciones de integridad : reglas de integridad de clave primaria, relgas de integridad referencial, restricciones de dominio, posibilidad de valores nulos.

2.4.

Valores nulos

´ Los valores nulos representan informaci´on desconocido. En el Algebra Relacional se denotan por el signo de interrogante (?). La posibilidad de utilizar valores nulos a˜ nade flexibilidad al modelo. En valor nulo se a˜ nade al dominio del atributo y por ello es preciso indicar su precedencia; normalmente al primer valor y al u ´ ltimo. Se dice que dos tuplas son iguales si los elementos no nulos coinciden entre s´ı y los nulos son nulos en ambas. El resultado de una operaci´on aritm´etica si alguno de los operandos es nulo, es tambi´en nulo. Igualmente para las operaciones de comparaci´on. Hay que extender la l´ogica binaria a una l´ogica ternaria, trivaluada, o triestada. OR T F ?

T T T T

F ? T T F ? ? ?

AND T F T T F F F F ? ? F

? ? F ?

Adem´as se necesita una funci´on l´ogica (binaria) que permita determinar si un atributo (de una tupla) es nulo o no.

´ Miguel Angel Vilela Garc´ıa

TEMA 3

´ Algebra Relacional

3.1.

Introducci´ on

´ El Algebra Relacional se define como un sistema cerrado de operaciones definidas sobre relaciones; sus operandos y resultados son relaciones. Es un lenguaje de procedimiento puro, en el que se especifican las consultas diciendo qu´e se quiere y c´omo se quiere. Se suele usar como lenguaje patr´on para los lenguajes comerciales. Tiene una potencia expresiva finita, no puede resolver todas las posibles consultas del mundo real.

3.2.

Operadores esenciales

Los operadores pueden ser unarios (act´ uan sobre una relaci´on) o binarios (act´ uan sobre dos relaciones). Un operador se dice que es esencial si no se puede poner en funci´on de otros, se dice que es derivado si s´ı se puede. La suma es un operador esencal, el producto no. Los operadres derivados no a˜ naden potencia expresiva al lenguaje.

3.2.1.

Selecci´ on

Sea una relaci´on R con esquema r. S(F )(R) se lee “selecci´on de la tabla R seg´ un el ′ predicado F ”. El resultado es una nueva relaci´on R ⊆ R, con el mismo esquema, formada por las filas (tuplas) de R que satisfacen el predicado l´ogico F . El predicado puede estar formado por constantes, atributos de R operadores l´ogicos, comparacionales y aritm´eticos. Ejemplo: listar los clientes de residentes en La Laguna: {S (CDC == "La Laguna") (CLIENTES)

13

3.2. OPERADORES ESENCIALES

3.2.2.

14

Proyecci´ on

Sea una relaci´on R con esquema r. P (K)(R) se lee “proyecci´on sobre K de R, donde K ⊆ r. El resultado es una nueva relaci´on R′ con esquema K que se obtiene seleccionando de la tabla R las columnas correspondientes a los atributos de K y eliminando posteriormente una de cada dos filas repetidas en R′ . Se puede concatenar (componer) con una selecci´on, por ejemplo: P (DNI) (S (CDC == "La Laguna") (CLIENTES)) Esta concatenaci´on no es conmutativa, puesto que se trata de una composici´on de funciones.

3.2.3.

Producto cartesiano

Sean las relaciones R con esquema r y S con esquema s. R × S se lee “producto cartesiano de R por S”. El resultado es una nueva relaci´on cuyo esquema es r + s la concatenaci´on de los esquemas de R y S, cuyo contenido se obtiene a˜ nadiendo a cada fila de R todas y cada una de las filas de S. Si r y s tienen atributos en com´ un se elimina la ambig¨ uedad anteponiendo al nombre del atributo el nombre de la tabla. El cardinal de R × S es |R × S| = |R| · |S|, por lo que hay que tener cuidado al utilizar esta operaci´on no sea que se vaya a sobrepasar la capacidad del sistema. Tomemos la siguiente consulta: “DNIs de los clientes residentes en La Laguna con cuenta en la sucursal #1 con al menos 1000 e. Una primera aproximaci´on ser´ıa la versi´on m´as f´acil y pedag´ogica pero poco eficiente: P (DNI) (S ((CLIENTE.DNI == DEPOSITO.DNI) && (CDC == "La Laguna") && (CS == 1) && (SLD >= 1000)) (CLIENTE x DEPOSITO)) Esta consulta puede mejorarse haciendo la selecci´on antes que el producto: P (CLIENTE.DNI) (S (CLIENTE.DNI == DEPOSITO.DNI) (P (DNI) (S (CDC == "La Laguna") (CLIENTE)) x P (DNI) (S ((CS == 1) && (SLD >= 1000)) (DEPOSITO))))

3.2.4.

Uni´ on

Sean las relaciones R y S, con igual grado y tales que los dominios de sus atributos i-´esimos iguales. En general, R y S pueden tener esquemas distintos, pero impondremos que tengan esquemas iguales para no tener que decidir los nombres de los atributos de la uni´on. R∪S se lee “uni´on de R y S”. El resultado es una nueva relaci´on con el mismo esquema que R y S

´ Miguel Angel Vilela Garc´ıa

3.3. OPERADORES DERIVADOS

3.2.5.

15

Diferencia

Sean las relaciones R y S, esigiendo las mismas condiciones que para la uni´on, la diferencia R − S (se lee “R menos S”) es una relaci´on con el esquema de R formada por las filas de R que no se encuentran en S. No es necesario que S ⊆ R.

3.3. 3.3.1.

Operadores derivados Diferencia

Sean las relaciones R y S, esigiendo las mismas condiciones que para la uni´on, la intersecci´on R ∩ S tiene el mismo esquema y est´a formada por las filas que est´an en ambas tablas.

3.3.2.

Yunci´ on (joint)

Sean las relaciones R con esquema r y S con esquema s. R Y (F )S se lee “yunci´on entre R y S seg´ un el predicado F ”. El resultado es una relaci´on con esquema r + s que contiene las filas de R×S que verifican el predicado l´ogico F , i.e. R Y (F )S = S(F )(R×S) Cuando se relacionan dos o m´as tablas es com´ un imponer la igualdad de los atributos hom´onimos, de los cuales s´olo interesa quedarse con uno de ellos.

3.3.3.

Yunci´ on natural (natural joint)

Es una especializaci´on de la yunci´on. Se realiza el producto cartesiano R × S, se seleccionan las tuplas imponiendo la igualdad de los atributos hom´onimos y se eliminan las columnas repetidas. Sean las relaciones R con esquema r y S con esquema s. R ∗ S(r ∪ s) = P (r ∪ s)(S(R.r ∪ s == S.r ∪ s)(R × S))

3.3.4.

Cociente

Sean las relaciones R con esquema r y S con esquema s, tales que s r. El cociente R/S tiene esquema r − s y est´a formado por las tuplas de P (r − s)(R) tales que al concatenarles todas y cada una de las tuplas de S se obtienen tuplas de R, i.e. (R/S)×S ⊆ R Consideremos la consulta “DNI de las personas que tienen una cuenta en todas las sucursales de La Laguna”. Esta consulta se resuelve con R/S donde R = P (DNI, CS) (DEPOSITO) S = P (CS) (S (CDS == "La Laguna") (SUCURSAL))

´ Miguel Angel Vilela Garc´ıa

´ DE LA BASE DE DATOS 3.4. MODIFICACION

3.4.

16

Modificaci´ on de la base de datos

´ El Algebra Relacional es un lenguaje de manipulaci´on de datos (DML), por lo que permite insertar, eliminar y actualizar las tuplas de las relaciones. Estas operaciones se expresan algebr´aicamente: Insercci´ on : R = R ∪ t o bien R = R ∪ E, donde t es la tupla que se desea insertar y E es una expresi´on que resulta en una relaci´on de tuplas que se desean insertar. Borrado : R = R − t o bien R = R − E, donde t es la tupla que se desea eliminar y E es una expresi´on que resulta en una relaci´on de tuplas que se desean eliminar. Update : R = (R − t1 ) ∪ t2 cambia t1 por t2 La expresi´on R = (R ∪ t2 ) − t1 no es equivalente, puesto que si los atributos modificados no forman parte de la clave primaria, R ∪ t2 podr´ıa no aumentar el n´ umero de filas porque habr´ıan dos filas con la misma clave primaria.

3.5.

Operaci´ on de complemento

Data una relaci´on R ⊆ D1 × D2 × · · · × Dn , se podr´ıa pensar en definir el complemento un atributo cuyo como R = D(D1 × D2 × · · · × Dn ) − R. Sin embargo, si existiera alg´ dominio fuera infinito, este complemento tendr´ıa una cantidad infinita de filas y por tanto no ser´ıa una relaci´on.

3.6.

´ Potencia expresiva del Algebra Relacional

La potencia expresiva de un lenguaje de consulta es el conjunto de consultas que pueden ser resueltas a trav´es del lenguaje. Esta potencia depende de los operadores esenciales ´ que est´en definidos sobre ese lenguaje. El Algebra Relacional tiene s´olo cinco operadores esenciales, por lo que su potencia expresiva es finita. Esto implica que no todas las con´ sultas del mundo real se pueden resolver a trav´es del Algebra Relacional, por ejemplo no se pueden hacer bucles, sumas ni ordenaciones.

3.7.

Propiedades de los operadores

´ Conociendo las propiedades de los operadores del Algebra Relacional podemos transformar una consulta en otras equivalentes, con objeto (tal vez) de mejorar su rendimiento. Se dice que dos consultas son equivalentes si proporcionan los mismos resultados para todos los valores de las variables. (a) La uni´on y la intersecci´on son conmutativas, asociativas y distributivas entre s´ı. R∪S =S ∪R ´ Miguel Angel Vilela Garc´ıa

3.7. PROPIEDADES DE LOS OPERADORES

17

R∩S =S ∩R (R ∪ S) ∪ T = R ∪ (S ∪ T ) (R ∩ S) ∩ T = R ∩ (S ∩ T ) R ∪ (S ∩ T ) = (R ∪ S) ∩ (R ∪ T ) R ∩ (S ∪ T ) = (R ∩ S) ∪ (R ∩ T ) (b) Si no nos importa el orden de los atributos en las tablas resultantes, tambi´en el producto cartesiano y la yunci´on natural tienen las propiedades conmutatia y asociativa. R×S =S ×R (R × S) × T = R × (S × T ) R∗S =S∗R (R ∗ S) ∗ T = R ∗ (S ∗ T ) (c) Sean R, S relaciones, F1 , F2 predicados tales que en F1 s´olo intervienen atributos de de R y en F2 s´olo intervienen atributos de S. En estas condiciones: S(F1 ∧ F2 )(R × S) = (S(F1 )(R)) × (S(F2 )(S)) S(F1 ∧ F2 )(R ∗ S) = (S(F1 )(R)) ∗ (S(F2 )(S)) (d) “Cascada de selecciones”: Sea R una relaci´on y F1 , F2 , . . . Fn predicados S(F1 )(S(F2 )(. . . S(Fn )(R) . . .)) = S(F1 ∧ F2 ∧ . . . ∧ Fn )(R) (e) “Cascada de proyecciones”: Sea R una relaci´on y L1 ⊆ L2 ⊆ ldots ⊆ Ln listas de atributos de R P (L1 )(P (L2 )(. . . P (Ln )(R) . . .)) = P (L1 )(R) (f) “Conmutatividad entre la proyecci´on y la selecci´on”: Sea R una relaci´on, F un predicado l´ogico y L una lista de atributos tal que el conjunto de atributos que intervienen en F esta contenido en L. Entonces P (L)(S(F )(R)) = S(F )(P (L)(R)) (g) “Conmutatividad entre la proyecci´on y el producto cartesiano”: Sean R, S relaciones con esquemas r y s respectiamente, sea L ⊆ r ∪ s una lista de atributos. Entonces P (L)(R × S) = (P (L ∩ r)(R)) × (P (L ∩ s)(S))

´ Miguel Angel Vilela Garc´ıa

´ DE LOS OPERADORES PARA VALORES NULOS 3.8. AMPLIACION

18

(h) “Conmutatividad entre la proyecci´on y la yunci´on natural”: Sean R, S relaciones con esquemas r y s respectiamente, sea L ⊆ r ∪ s una lista de atributos. Entonces P (L)(R ∗ S) = (P (L ∩ r)(R)) ∗ (P (L ∩ s)(S)) (i) “Conmutatividad entre la selecci´on y los operadores de conjuntos” S(F )(R ∪ S) = (S(F )(R)) ∪ (S(F )(S)) S(F )(R ∩ S) = (S(F )(R)) ∩ (S(F )(S)) S(F )(R − S) = (S(F )(R)) − (S(F )(S)) (j) “Conmutatividad entre la proyecci´on y la uni´on”: S(F )(R ∪ S) = (S(F )(R)) ∪ (S(F )(S))

3.8. 3.8.1.

Ampliaci´ on de los operadores para valores nulos Uni´ on Externa (UE)

Generaliza la uni´on (∪) y se denota por UE . S´olo se impone que los atributos hom´onimos tengan igual dominio, sin necesidad de que est´en en la misma posici´on dentro del esquema ni que las tablas tengan el mismo esquema. El resultado es una nueva tabla cuyo esquema es r ∪s y que contiene todas las tuplas de R y todas las tuplas de S, completadas adecuadamente con valores nulos. Si R y S tienen el mismo esquema R UE S = R ∪ S.

3.8.2.

Yunci´ on natural externa (natural outer joint)

Generaliza la yuncion natural y de denota por YE . Contiene las tuplas de la yuncion natural m´as aquellas que tengan nulo en los atributos comunes o las que tengan un valor que no existan en la otra tabla. Los huecos se rellenan con nulos. La variante “yunci´on natural a izquierda” s´olo a˜ nade a la yunci´on natural las filas generadas por el operando de la izquierda, y la “yunci´on natural a derecha” s´olo a˜ nade a la yunci´on natural las filas generadas por el operando de la derecha.

3.8.3.

Yunci´ on posibilista

Generaliza la yunci´on y se denota por YP . Funciona como una yunci´on, seleccion del producto cartesiano seg´ un el mismo predicando que la yunci´on natural, pero considera que un valor nulo puede igualarse a otro valor nulo o a cualquier otra cosa.

´ Miguel Angel Vilela Garc´ıa

3.9. VISTAS

3.9.

19

Vistas

Una vista es una relaci´on algebraica a la que se asigna un nombre. Las vistas no pueden ´ ser recursivas, y s´olo pueden aparecer en el lado izquierdo. Unicamente se almacena su definici´on, que queda en el diccionario de la base de datos. Tambi´en se las denomina “relaciones virtuales”, debido a que su contenido no se almacena f´ısicamente sino que se calcula en el momento de acceder a la vista. Las vistas simplifican la escritura de consultas en ´algebra relacional. Desempe˜ nan un papel an´alogo a las funciones en los lenguajes de programaci´on: reutilizar c´odigo y simplificar las expresiones de las consultas, pero no pueden ser recursivas. Tambi´en son u ´ tiles para proteger la confidencialidad de los datos, creado vistas para usos espec´ıficos y dando permisos de lectura a las vistas pero no a las tablas de las que las vistas obtienen su contenido. Tambi´en protegen a las aplicaciones de los cambios en el dise˜ no de la base de datos. Por contra, las vistas requieren m´as tiempo de procesamiento que las tablas (los sistemas gestores comerciales suelen crear una tabla temporal). Adem´as no son actualizables, pues si se modificase un atributo de la vista no se podr´ıa determinar que tabla actualizar.

3.10.

Relaciones de integridad

Restricciones sobre los atributos, sus dominios, la posibilidad de tomar valores nulos. Las restricciones de unicidad imponen que un atributo (o un conjunto de atributos) no tengan valores duplicados. La regla de integridad de clave primaria impone que las claves primarias no pueden tomas valores nulos. La regla de integridad referencial se basa en el concepto de clave ajena: es un conjunto de atributos de una relaci´on que hace referencia a la clave primara de otra relaci´on (o la misma). Esta regla crea una relaci´on de dependencia entre las dos relaciones del siguiente modo: “Cualquier valor no nulo de la clave ajena debe existir en la clave primera a la que referencia”. Consideremos una nueva relaci´on EMPLEADOS (NE, NBE, ND, NS) cuyos atributos son el n´ umero de empleado (NE), el nombre (NBE), en n´ umero de departamento (ND) y el n´ umero de supervisor (NS). El atributo NS es una clave ajena porque referencia a la clave primaria NS. La tabla que contiene la clave primaria se denomina “tabla padre” y la que contiene la clave ajena “tabla hija”. Sean R y S relaciones son esquemas r y s respectivamente, K ⊆ r clave primaria de R y K ′ ⊆ s clave ajena de S, la regla de integridad referencial impone que ∀ t1 ∈ S

∃ t2 ∈ R : t1 [K ′ ] = t2 [K]

´ Miguel Angel Vilela Garc´ıa

3.10. RELACIONES DE INTEGRIDAD

20

Cuando hay una jerarqu´ıa entre las tablas el analista debe in dicar qu´e tipo de acciones (compensatorias, de restauraci´on de la consistencia) deben realizarse para mantener la integridad de la base de datos. Se distinguen tres tipos de acciones: Rechazar la modificaci´on Propagar los cambios Rellenar adecuadamente con valores nulos Si se quisiera borrar una tuple de la tabla CLIENTE habr´ıa que comprobar primero que no hubieran tuplas en la tabla DEPOSITO con el mismo DNI. Si las hubieran se contemplar´ıan las siguientes opciones: Rechazar el borrado de la tupla Eliminar la tupla de CLIENTE y luego • propagar el borrado en cascada, eliminando todas las tuplas dependientes en la tabla DEPOSITO • propagar a nulo, rellenando con un valor nulo el DNI de todas las tuplas dependientes en la tabla DEPOSITO El borrado propagado es una opci´on peligrosa, ya que podr´ıa eliminiar muchas tuplas a partir de la operaci´on de borrado de unas pocas tuplas. Para modificar el DNI de una tupla en la tabla CLIENTE habr´ıa que propagar los cambios a la tabla DEPOSITO, o bien rechazar la modificaci´on. Sin embargo, se pueden eliminar tuplas en la tabla hija sin riesgo de quebrantar la regla de integridad referencial. Por otro lado, para aceptar una insercci´on en la tabla DEPOSITO habr´a que exigir que el DNI exista en la tabla CLIENTE e igualmente para modificar el DNI en la tabla DEPOSITO.

´ Miguel Angel Vilela Garc´ıa

TEMA 4

C´alculo relacional

El c´alculo relacional de tuplas y el c´alculo relacional de dominios son dos lenguajes de consultas te´oricos declarativos puros, en los que s´olo se especifica lo que se desea obtener pero no el modo de obtenerlo. Estos dos lenguajes se encuentran ´ıntimamente relacionados. Ambos se basan en l´ogica de predicado. En el c´alculo relacional de tuplas se utilizan variables de tipo fila (tuplas) y en el c´alculo relacional de dominios se utilizan variables de tipo dominio que representan atributos. Existen algunos lenguajes comerciales basados exclusivamente en c´alculo relacional de tuplas o c´alculo relacional de dominios, como el Quel (Ingres) y el QBE (Query By Example).

4.1.

Introducci´ on

Una consulta en c´alculo relacional de tuplas tiene la siguiente forma: {tn / P (t)} y se lee “conjunto de las tuplas tn de grado n que satisfacen el predicado l´ogico P (t). El predicado puede estar formado por constantes, variables, operadores (aritm´eticos, l´ogicos y comparacionales) y cuantificadores (existencial, universal y de pertenencia). Recordemos las equivalencias de predicados l´ogicos debidas a las leyes de Morgan y las equivalencias de las negaciones de los cuantificadores existencial y universal: ¬ (P1 ∧ P2 )

↔

¬P1 ∨ ¬P2

¬ (P1 ∨ P2 )

↔

¬P1 ∧ ¬P2

¬ (∃x) (P (x))

↔

(∀x) ¬ (P (x))

¬ (∀x) (P (x))

↔

(∃x) ¬ (P (x))

En c´alculo relacional de tuplas no se puede utilizar el operador de implicaci´on “⇒”, pero en su lugar podemos hacer uso de su equivalencia l´ogica. 21

´ INFORMAL 4.2. INTRODUCCION

22

P ⇒Q

≡

¬P ∨ Q

Cuando se anidan varios predicados con cuantificadores, si ´estos son todos existencias o todos universales es posible reducirlos a un u ´ nico predicado, sin que importe el orden en el que aparecen los cuantificadores: (∃x) (∃y) (P (x, y)) ≡ (∃x, y) (P (x, y)) (∀x) (∀y) (P (x, y)) ≡ (∀x, y) (P (x, y)) Sin embargo al anidar varios predicados con cuantificadores de ambos tipos no es posible hacer la misma reducci´on, ya que el orden en estos casos s´ı es importante. En una consulta de c´alculo relacional de tuplas, de la forma {tn / P (t, x1 , . . . , xr )}, a tn se la denomina “variable libre” (free variable) y a x1 , . . . , xr “variables acotadas” (bounded variables). Estas u ´ ltiimas deben estar cuantificadas y tener declarado su dominio antes de utilizarlas. dom(x1 ) = T1 dom(x2 ) = T2

4.2.

Introducci´ on informal

Veamos a continuaci´on c´omo resolver mediante c´alculo relacional de tuplas algunas consultas que ya sab´ıamos resolver mediante ´algebra relacional. Ejemplo: DNI de los clientes que tienen un pr´estamo de m´as de 6000 e Al crear una variable en c´alculo relacional de tuplas se la suele denominar con la letra inicial de la tabla que constituye su dominio. El esquema del resultado se deduce del predicado. dom(p) = PRESTAMO  t(1 / (∃p) ((p[I] > 6000) ∧ (p[DNI] = t[DNI]))

Ejemplo: DNI de los clientes que tienen un pr´estamo en la sucursal #1 y cuidad en la que viven. dom(p) = PRESTAMO dom(c) = CLIENTE  t(2 / (∃p, c) ((p[CS] = 1) ∧ (p[DNI] = c[DNI]) ∧ (p[DNI] = t[DNI])) ∧ (p[CDC] = t[CDC])

Ejemplo: DNI de los clientes que tienen un pr´estamo, una cuenta o ambos en la sucursal #1

dom(p) = PRESTAMO dom(d) = DEPOSITO  t(1 / (∃p) ((p[DNI] = t[DNI]) ∧ (p[CS] = 1)) ∨ (∃p) ((p[DNI] = t[DNI]) ∧ (p[CS] = 1))

Ejemplo: Clientes con cuenta en todas las sucursales de La Laguna

´ Miguel Angel Vilela Garc´ıa

´ 4.3. CALCULO RESTRINGIDO DE TUPLAS

23

dom(d) = DEPOSITO dom(s) = SUCURSAL  t(1 / (∀s) ((s[CDC] 6= ’La Laguna’) ∨ (∃d) ((d[CS] = s[CS]) ∧ (d[DNI] = t[DNI])))

Las consultas que en c´alculo relacional de tuplas comienzan con el cuantificador universal (∀) se resuelven en ´algebra relacional con el operador cociente. Se considera de mal estilo utilizar variables con nombres que no se refieran a sus dominios (tablas). Tampoco se recomienda utilizar x ∈ TABLA porque el usuario podr´ıa negarla (x 6∈ TABLA) y no ser´ıa trivial definir el resultado.

4.3.

C´ alculo restringido de tuplas

Parece claro que no todos los predicados deben ser v´alidos, pues algunas expresiones no son evaluables de forma finita. Por ejemplo, una negaci´on (complementaci´on) podr´ıa ser infinita. Se restringen los predicados a predicados seguros (sanos) que pueden calcularse en un n´ umero finito de pasos. Un predicado se dice que es seguro si tiene equivalente en ´algebra relacional. Esto implica que al ´algebra relacional tiene al menos la misma potencia experesiva que el c´alculo relacional de tuplas. Para probar la relaci´on inversa vamos a ver que todos los operadores fundamentales del ´algebra relacional se pueden expresar en c´alculo relacional de tuplas:  R ∪ S ≡ t(n / (t ∈ R) ∨ (t ∈ S) R ∩ S ≡ t(n / (t ∈ R) ∧ (t ∈ S) R − S ≡ t(n / (t ∈ R) ∧ (t 6∈ S) No es necesario definir el domino de t porque no hay variables acotadadas. Tampoco hay problemas con t 6∈ S porque por la condici´on t ∈ R hay un n´ umero finito de filas. Consideremos ahora las relaciones R(r) de grado nr y S(s) de grado ns  / (∃x)(t[r] = x[r]) ∧ (∃y)(t[s] = y[s] R × S ≡ t(n +n r s S(F )(R) ≡  t(n / (t ∈ R) ∧ (F ) P (k)(R) ≡ t(nk / (∃x)(t[k] = x[k]) donde k⊆ r, nk ≤ nr y dom(x) = R R/S ≡ t(nr −ns / (∃x1 )(t[r − s] = x[r − s]) ∧ (∀y)(∃x2 ) ((x2 [r − s] = t[r − s]) ∧ (x2 [s] = y[s])) donde r 6⊆ s y dom(x1 ) = dom(x2 ) = R, dom(y) = S

Por lo tanto, el c´alculo restringido de tuplas y el ´algebra relacional son equipotente, i.e. tienen la misma potencia expresiva. Un lenguaje con al menos la misma potencia expresiva que el ´algebra relacional se dice que es relacionalmente completo. Ejemplo: DNI del cliente que tiene la cuenta con el menor saldo de la sucursal #1 dom(d1 ) = dom(d2 ) = D  t(1 / (∃d1 ) ((t[DNI] = d1 [DNI]) ∧ (d1 [cs] = 1)) ∧ (∀d2 ) ((d2 [CS] 6= 1) ∨ (d2 [SLD] ≥ d1 [SLD]))

´ Miguel Angel Vilela Garc´ıa

´ FORMAL DEL CALCULO ´ 4.4. DEFINICION RELACIONAL DE TUPLAS

4.4.

24

Definici´ on formal del c´ alculo relacional de tuplas

Se define una consulta como  t(n / P (t)

el conjunto de las tuplas t(n que satisfacen la condici´on o predicado l´ogico P (t). Los predicados se construyen utilizando ´atomos y operadores, y deben ser seguros. Se distinguen tres tipos de ´atomos: (i) s ∈ R; s es una tupla, R una relaci´on (ii) s[x] θ u[y]; s, u tuplas, x, y atributos, θ operador (iii) s[x] θ c; como (ii) pero c es una constante Para construir un predicado l´ogico deben seguirse las siguiente reglas: (i) un ´atomo es un predicado (ii) si P1 , P2 son predicados, entonces P1 ∧ P2 , P1 ∨ P2 y ¬P1 tambi´en son predicados (iii) si Px es un predicado en funci´on de x, entonces (∃x)(Px ) y (∀x)(Px ) son tambi´en predicados. (iv) Toda variable no libre debe estar cuantificada, por lo general se declara su dominio antes de usarla. (v) Los predicados deben ser seguros. (vi) Ninguna otra cosa es un predicado.

4.5.

C´ alculo relacional de dominios

Se trata de un lenguaje de consulta te´orico declarativo con variables de tipo dominio. Las consultas tienen la siguiente forma: {< x1 , x2 , . . . , xn > / P (x1 , x2 , . . . , xn )} y se lee “conjunto de tuplas < x1 , x2 , . . . , xn > tales que verifican el predicado P (x1 , x2 , . . . , xn ) (que depende de x1 , x2 , . . . , xn )”. Los predicados se construyen con ´atomos y operadores siguiendo las mismas reglas de construcci´on de predicados que en el c´alculo relacional de tuplas. Se distinguen los siguiente ´atomos: (i) < y1 , . . . , yn >∈ R (ii) x θ y ´ Miguel Angel Vilela Garc´ıa

´ 4.5. CALCULO RELACIONAL DE DOMINIOS

25

(iii) x θ c Ejemplo: DNI de clientes que hayan solicitado un pr´estamo por m´as de 6000 e {< dni > / (∃cs, i, np) ((< dni, cs, np, i >∈ PRESTAMO) ∧ (i > 6000))} Siempre se necesita como m´ınimo tantas variables como tributos tenga la tabla. Ejemplo: DNI de los clientes con pr´estamo en la sucursal #1 y ciudad en la que viven {< dni, cdc > / (∃cs, i, np) ((< dni, cs, np, i >∈ PRESTAMO) ∧(cs = 1) ∧ (∃n)(< dni, n, cdc >∈ CLIENTE))} Tambi´en es posible prescindir de la varaible cs y substituirla por la constante a la que se est´a igualando, pero s´olo es posible porque se trata de una constante y adem´as la comparaci´on es de igualdad.

{< dni, cdc > / (∃i, np) ((< dni, 1, np, i >∈ PRESTAMO) ∧ (∃n)(< dni, n, cdc >∈ CLIENTE))} Ejemplo: DNI de los clientes que tienen una cuenta en la sucursal #1 y un pr´estamo en dicha sucursal. {< dni > / (∃i, np)(< dni, 1, np, i >∈ PRESTAMO) ∧ (∃nc, sld)(< dni, 1, nc, sld >∈ DEPOSITO} En este ejemplo se podr´ıan reutilizar las variables nombr´andolas x, y, pero esto restar´ıa legibilidad al c´odigo. Se considera que esto constituye una mala pr´actica y no es deseable. {< dni > / (∃x, y)(< dni, 1, x, y >∈ PRESTAMO) ∧ (∃x, y)(< dni, 1, x, y >∈ DEPOSITO} Ejemplo: DNI de los clientes que tienen cuenta en todas las sucursales de La Laguna

{< dni > / (∀cs, ns, cdc) ((< cs, ns, cdc >∈ SUCURSAL) ∨ (cds 6= ’La Laguna’) ∨ (∃sld, nc)(< dni, cs, nc Esta es la forma larga, utilizando la t´ecnica de cortocircuito para que el sistema tenga que evaluar el n´ umero m´ınimo posible de condiciones. Otra forma, m´as simplificada:

{< dni > / (∀cs, nc) ((< cs, nc, ’La Laguna’ >6∈ SUCURSAL) ∨ (∃nc, sld)(< dni, cs, nc, sld >∈ DEPOSIT

´ Miguel Angel Vilela Garc´ıa

TEMA 5

El lenguaje SQL

5.1.

Introducci´ on

El lenguaje SQL (Structured Query Language) es un lenguaje comercial de consultas para bases de datos realcionales que hoy en d´ıa es un est´andar. Apareci´o en 1979 de la empresa Relational Corporation que hoy se llama ORACLE. En los 70 sale el Modelo Relacional, en el 74 IBM lanza el lenguaje SEQUEL, detras el SQUARE y se convierte en SEQUEL2 (76). El SQL tal como es nace en 1979; los primeros en sacar el SQL como lenguaje comercial fu´e ORACLE. Ha tenido varias normalizaciones: ANSI SQL (86), SQL2 (92), SQL3 (99); pero siguen habiendo partes importantos sin normalizar, como la parte de definici´on de datos. SQL3 a˜ nade disparadores (triggers) y orientaci´on a objetos.

5.2. 5.2.1.

Recuperaci´ on de datos Cl´ ausulas SELECT y FROM

Una consulta en SQL empieza por la cl´ausula SELECT: SELECT atributos, o, expresiones FROM lista, de, tablas; ´ La cl´ausula SELECT es equivalente al operador proyecci´on del Algebra Relacional (se considera un error hist´orico haberle dado el nombre SELECT). La cl´ausula FROM equivale al producto cartesiano de las tablas. SELECT DNI FROM CLIENTE;

26

´ DE DATOS 5.2. RECUPERACION

27

Cuando hay ambig¨ uedad se antepone al atributo la tabla a la que pertenece SELECT CLIENTE.DNI FROM CLIENTE, DEPOSITO; ´ A diferencia del operador proyecci´on del Algebra Relacional, la cl´ausula SELECT permite la aparici´on de filas duplicadas en el resultado de la consulta. El resultado de una consulta en SQL es otra tabla en la que no se han eliminado las t-uplas duplicadas por una cuestion de ahorro de tiempo. Para eliminar las filas duplicadas se utiliza la opci´on DISTINCT en la cl´ausula SELECT. SELECT DISTINCT DNI FROM CLIENTE; La opci´on contraria a DISTINCT es ALL, que es la que el sistema toma por defecto, manteniendo en el resultado las filas duplicadas. A las tablas se les pueden asignar alias para simplificar su nomenclatura: SELECT C.DNI FROM CLIENTE C, DEPOSITO D; Para renombrar los atributos se siguen del alias entre dobles comillas: SELECT DISTINCT NBC "Nombre de cliente" FROM CLIENTE; La siguiente consulta devuelve el 10 % del saldo de todas las cuentas: SELECT SLD * 0.1 FROM DEPOSITO; Para listar todos los atributos de una tabla se pueden enumerar todos o bien usar * SELECT * FROM DEPOSITO; Tambi´en se pueden usar constantes en las consultas de manera que SELECT DNI, 1 FROM DEPOSITO; devuelve una tabla con todos los DNI’s de DEPOSITO en la primera columna y una segunda columna llena de 1’s Tambi´en es posible anidar sentencias SELECT para construir vistas impl´ıcitas. ´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

5.2.2.

28

La cl´ ausula WHERE

Esta cl´ausula es opcional, en caso de utilizarse ha de estar justo despu´es de la cl´ausula FROM. Es equivalante al operador selecci´on del ´algebra relacional. Debe ir seguida de una condici´on (predicado l´ogico) admite los operadores aritm´eticos ( ( ) + - * / ), comparacionales ( < > = = != )1 y l´ogicos (AND OR NOT). Adem´as a˜ nade operadores de rango (BETWEEN), pertenencia a un conjunto (IN), cualificadores (ANY, ALL), comparaciones para patrones (LIKE), existencia (EXISTS) y nulidad (IS NULL). SELECT atributos, o, expresiones FROM lista, de, tablas; WHERE condici´ on l´ ogica; Ejemplo: DNIs de los clientes que viven en La Laguna SELECT DNI FROM CLIENTE WHERE CDC = ’La Laguna’; Ejemplo: DNI de los clientes que tengan alguna cuenta con saldo superior a 1000 SELECT DNI FROM DEPOSITO WHERE SLD > 1000; Ejemplo: Los que cumplen la condicion anterior y adem´as sus cuentas est´an en la sucursal 1 SELECT DNI FROM DEPOSITO WHERE (SLD > 1000) AND (CS = 1); Ejemplo: DNIs de clientes con cuentas en las sucursales 100 ´o 200 SELECT DNI FROM CLIENTE WHERE CS IN (100, 200); El operador de rango BETWEEN requiere dos valores para la condici´on, debe especificarse primero el menor de los dos valores: SELECT DNI FROM DEPOSITO WHERE NC BETWEEN 100 AND 500 AND SLD NOT BETWEEN 1000 AND 5000; 1

El comparador de igualdad es =, no ==

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

29

La anterior consulta es equivalente a: SELECT DNI FROM DEPOSITO WHERE (NC >= 100) AND (NC 5000); El operador de pertenencia IN comprueba si la expresion pertenece a un conjunto de valores. El conjunto se puede dar de forma expl´ıcita o impl´ıcita. La forma expl´ıcita consiste en una lista de valores separados por comas entre par´entesis. Ejemplo: N´ umero de cuenta 100, 200 ´o 500. SELECT DNI FROM DEPOSITO WHERE NC IN (100,200,500); La forma impl´ıcita consiste en obtener el conjunto de valores como resultado de una consulta: Ejemplo: Ejemplo: Cotitulares de las cuentas que tiene la persona con DNI 1111. SELECT DNI FROM DEPOSITO WHERE NC IN (SELECT NC FROM DEPOSITO WHERE DNI = 1111); Los cualificadores ANY y ALL se aplican a los operadores comparacionales de forma an´aloga a los cualificadores ∀ y ∃. La condici´on expresion op ANY valores ser´a cierta en cuanto exista alg´ un valor en valores que verifique la comparaci´on con la expresi´ on La condici´on expresion op ALL valores ser´a cierta si para todos los valor en valores se verifica la comparaci´on con la expresi´ on Ejemplo: Clientes (DNI) con saldo mayor, en alguna de sus cuentas, que alguno de los saldos de las cuentas del cliente 1111 SELECT DNI FROM DEPOSITO WHERE SLD > ANY (SELECT SLD FROM DEPOSITO WHERE DNI = 1111); Ejemplo: DNI del titular de la cuenta con mayor saldo de la sucursal 1

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

30

SELECT DNI FROM DEPOSITO WHERE (CS = 1) AND (SLD >= ALL (SELECT SLD FROM DEPOSITO WHERE CS = 1)); El operador de nulidad IS NULL devuelve verdadero si la expresion toma valor nulo, falso en caso contrario Ejemplo: DNI de clientes cuya ciudad de residencia sea conocida SELECT DNI FROM CLIENTE WHERE CDC IS NOT NULL; El operador LIKE compara una expresi´on de tipo cadena de caracteres con un patron. Devuelve verdadero si se ajusta a la plantilla, falso en caso contrario. La sintaxis es expresi´ on LIKE ’patron’. En el patr´on se pueden utilizar los comodines % (cualquier cadena, incluso vac´ıa) y _ (un caracter). Ejemplo: DNI de clientes cuyo nombre empiece por J, su tercer caracter sea a y acaben en n SELECT DNI FROM CLIENTE WHERE N LIKE ’J_a%n’; Ejemplo: DNI de clientes cuyo nombre tenga mas de 3 caracteres de logitud SELECT DNI FROM CLIENTE WHERE N LIKE ’___%’; Debe tenerse presente que los LIKE distingue may´ usculas y min´ usculas El operador de existencia EXISTS devuelve verdadero si la subconsulta no es vac´ıa (si tiene alguna fila), falso en caso contrario. Ejemplo: Sucursales que aun no han concedido ning´ un pr´estamo SELECT CS FROM SUCURSAL S WHERE NOT EXISTS (SELECT CS FROM PRESTAMO P WHERE P.CS = S.CS);

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

31

Ejemplo: Clientes que tienen cuenta en todas las sucursales de La Laguna. El cliente debe tener cuenta en cualquier sucursal de La Laguna, o equivalentemente no debe existir ninguna sucursal en La Laguna en la que el cliente no tenga cuenta. As´ı podemos transformar este ∀ en un 6 ∃ SELECT DNI FROM DEPOSITO D1 WHERE NOT EXISTS (SELECT * FROM SUCURSAL S WHERE CDS = ’La Laguna’ AND NOT EXISTS (SELECT * FROM DEPOSITO D2 WHERE (D1.DNI = D2.DNI) AND (D2.CS = S.CS))); Esto es hacer una consulta relativamente dura de la forma m´as dura posible. El cuantificador universal (∀) se puede hacer negando “dos veces” el cuantificador existencia (∃).

5.2.3.

Cl´ ausulas GROUP BY y HAVING

La cl´ausula GROUP BY es opcional. En el caso de usarlo va detr´as de WHERE, si lo ´ hubiera (si no, detr´as de FROM). No tiene equivalente en el Algebra Relacional, por lo ´ que aumenta la capacidad expresiva del lenguaje SQL respecto del Algebra Relacional. Divide la tabla de resultado en grupos atendiendo a la igualdad de valores de los atributos por los que se agrupa. No suele usarse sola, sino en combinaci´on de funciones de grupo. Al usar esta cl´ausula no se puede proyectar por cualquier atributo, s´olo sobre los que han sido usados para agrupar y sobre funciones de grupo. Ejemplo: N´ umero de cuentas que tiene cada sucursal del banco SELECT COUNT(DISTINCT NC) FROM DEPOSITO GROUP BY CS; La cl´ausula HAVING (opcional) ir´a detr´as de GROUP BY, seguida de una condicion l´ogica. HAVING es a los grupos lo que el WHERE a las t-uplas. Selecciona los grupos que satisfagan la condici´on. S´olo se pueden usar atributos por los que se haya agrupado. Ejemplo: N´ umero de cuentas distintas que tienen las sucursales 1 y 2 SELECT COUNT(DISTINCT NC), CS FROM DEPOSITO GROUP BY CS HAVING CS IN (1, 2); Se pueden usar funciones grupo sustituyendo una constante. Ejemplo: DNI de clientes con m´as de 2 cuentas en la misma sucursal ´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

32

SELECT DNI FROM DEPOSITO GROUP BY DNI,CS HAVING COUNT(*) > 2; Ejemplo: Obtener sucursales con saldo medio en sus cuentas superior al saldo medio de las cuentas de la sucursal 1 SELECT CS FROM DEPOSITO GHROUP BY CS HAVING AVG(SLD) > (SELECT AVG(SLD) FROM DEPOSITO WHERE CS = 1); En este caso se est´a contando 2 veces las cuentas del mismo propietario, pues ambas consultas son sobre DEPOSITO. Deber´ıamos usar una proyecci´on (vista). 5.2.3.1.

Funciones de agregaci´ on

Las funciones agregadas (de grupo) intentan resumir las caracter´ısticas de los grupos a valores escalares. Se trata de funciones que se pueden invocar en el SQL y que retornan un u ´ nico valor como resumen de un conjunto de valores. COUNT ([DISTINCT] expresion | *) devuelve el n´ umero de valores no nulos en la expresi´on (con DISTINCT elimina duplicados), o bien el n´ umero de fila de la tabla (al usar *). Ejemplo: N´ umero de pr´estamos en la sucursal con c´odigo 1 SELECT COUNT(DISTINCT NP) FROM PRESTAMO WHERE CS = 1; Ejemplo: N´ umer de clientes que tiene el banco SELECT COUNT (*) FROM CLIENTE; MIN Devuelve el elemento m´ınimo de un conjunto. MAX Devuelve el elemento m´aximo de un conjunto. AVG ([DISTINCT] expresion) devuelve la media aritm´etica de los valores de un conjunto (con DISTINCT elimina duplicados), eliminando previamente los valores nulos. Ejemplo: Saldo medio de las cuentas de cada sucursal ´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

33

SELECT CS, AVG(SLD) FROM DEPOSITO GROUP BY CS; En realidad este ejemplo no es del todo correcto, puesto que el saldo de una cuenta puede estar repetido tantas veces como titulares tenga la cuenta. La forma correcta ser´ıa: SELECT AVG(SLD) FROM (SELECT CS, NC, SLD FROM DEPOSITO) A WHERE CS = 1; SUM Devuelve la suma de los valores de un conjunto. STDDEV Devuelve la desviaci´on t´ıpica de un conjunto. VAR Devuelve la varianza de un conjunto.

5.2.4.

Cl´ ausula ORDER BY

Es una cl´ausula opcional, en caso de utilizarse ha de ser la u ´ltima de la consulta, que ordena las filas de la consulta seg´ un una lista de atributos. Cada atributo puede ordenarse de forma ascendente (por omisi´on) o descendente. Su sintaxis es la siguiente: ORDER

BY atrib1 [ASC|DESC] [, atrib2 [ASC|DESC], ...]

Su ejecucion es costosa y suele usarse cuando se va a proyectar. tiene el efecto visual de GROUP BY pero no se ha agrupado. Ejemplo: Listar para cada sucursal las cuentas que hay abiertas en ella, ordenando las sucursales ascendentemente y las cuentas en orden descendente. SELECT DISTINCT CS, NC FROM DEPOSITO ORDER BY CS, NC DESC;

5.2.5.

Operadores sobre Conjuntos

Estos operadores realizan las operaciones usuales entre conjuntos: uni´on, intersecci´on y diferencia; los operadores son respectivamente UNION, INTERSECTION y MINUS. A diferencia del ´algebra relacional, estos operadores eliminan las filas duplicadas, a menos que se indique lo contrario con la cl´ausula opcional ALL (salvo para la diferencia). Ejemplo: Sucursales que aun no han concedido ning´ un pr´estamo

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

34

SELECT CS FROM SUCURSAL MINUS SELECT CS FROM PRESTAMO; Ejemplo: Personas con una cuenta o un prestamo, o ambas SELECT DNI FROM PRESTAMO UNION SELECT DNI FROM DEPOSITO; Ejemplo: DNI de los clientes que tienen cuentas tanto en la sucursal 1 como en la 2 SELECT DNI FROM DEPOSITO WHERE CS = 1 UNION SELECT DNI FROM DEPOSITO WHERE CS = 2;

5.2.6.

Subconsultas

Permiten usar el resultado de una consulta como tabla de origen para otra consulta o bien como conjunto de valores para pertenencia o comparaci´on en otra consulta. Se distinguen cinco tipos: 1. Retornan una u ´ nica columna y fila (una cte.) como en el ejemplo anterior. Ejemplo: Clientes con que residen en la misma ciudad que el cliente con DNI 1111 SELECT DNI FROM CLIENTE WHERE CDC = (SELECT CDC FROM CLIENTE WHERE DNI = 1111); La consulta devuelve un u ´ nico valor porque la b´ usqueda se realiza comparando con una clave primaria. 2. Devuelven m´ ultiples filas proyectadas sobre una u ´ nica columna (un conjunto de valores escalares) Ejemplo: DNI del mejor cliente del banco (con mayor saldo en sus cuentas) ´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.2. RECUPERACION

35

SELECT DNI FROM DEPOSITO WHERE SLD >= ALL (SELECT SLD FROM DEPOSITO); Si no se sabe cu´antas filas devolver´a la subconsulta, hay que cualificar los comparadores. 3. Devuelven multiples columnas y m´ ultiples filas (un conjunto de vectores). No soportados por todos los sistemas gestores. Ejemplo: DNI del mejor cliente de cada sucursal SELECT DNI FROM DEPOSITO WHERE SLD (CS, SLD) IN (SELECT CS, MAX(SLD) FROM DEPOSITO GROUP BY CS); Ejemplo: Cotitulares de alguna de las cuentas del cliente con DNI 1111 SELECT DNI FROM DEPOSITO WHERE (CS, NC) IN (SELECT CS, NC FROM DEPOSITO WHERE DNI = 1111); Cuidado: x ∈ X ∧ y ∈ Y 6≡ (x, y) ∈ X × Y Si comparamos un vector se ponen los valores separados por comas dentro de sendos parentesis 4. Subconsultas m´ ultiples En una misma consulta puede aparecer mas de una subconsulta subconsulta AND subconsulta AND ... 5. Vistas temporales SELECT DNI FROM (SELECT CS, NC, SLD FROM DEPOSITO) A WHERE CS = 1;

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.3. INSTRUCCIONES DE ACTUALIZACION

5.3. 5.3.1.

36

Instrucciones de actualizaci´ on de datos Inserci´ on

Para insertar entradas en una tabla se usa INSERT INSERT INTO nombre_tabla [(lista, de, atributos)] VALUES (lista, de, valores); Cuando se omite la lista de atributos, el n´ umero de3 valores a insertar tiene que ser igual al n´ umero de atributos de la tabla. INSERT INTO BIBLIOTECA VALUES (4, ’FARMACIA’); Otra opci´on es escribir la constante NULL en el puesto de los atributos para los que no se tenga un valor. Podemos insertar tambi´en el resultado de una consulta, para ello este debe tener tantos atributos como haya en la tabla sobre la que se va a insertar. INSERT INTO nombre_tabla SELECT atributos FROM ...

5.3.2.

Modificaci´ on

Para modificar las entradas de una tabla se usa UPDATE. UPDATE nombre_tabla SET atributo = valor, atributo2 = valor2, ... [WHERE condici´ on]; Si no se indica ninguna condici´on con WHERE, UPDATE modificar´a todas las tuplas de la tabla. Ejemplo: Nos equivocamos al introducir el nombre de un libro UPDATE LIBRO SET T = ’nuevo nombre’ WHERE II = 1010; Algunos SGBD admiten una subconsulta en el SET Ejemplo: Modificar el saldo de la cuenta numero 100 de la sucursal 1 para que tenga el valor del promedio de los saldos de las cuentas de la sucursal con codigo 2 ´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.4. INSTRUCCIONES DE DEFINICION

37

UPDATE DEPOSITO SET SLD = (SELECT AVG(SLD) FROM DEPOSITO WHERE CS = 2) WHERE (CS = 1) AND (NC = 100); Las modificaciones pueden tener efectos colaterales si las condiciones o los nuevos valores dependen de los valores que se modifican. Ejemplo: Incrementar en un 10 % el saldo de aquellos clientes cuyo saldo sea superior a la media UPDATE DEPOSITO SET SLD = SLD * 1.1 WHERE SLD > (SELECT AVG(SLD) FROM DEPOSITO); Esta orden presenta algunos problemas. Cuando la cuenta tiene m´as de un titular, la bonificaci´on se realizar´a varias veces. Lo m´as importante, sin embargo, es que al ir cambiando el saldo la media tambi´en var´ıa. Por esto es necesario tener especial cuidado con las ordenes en las que se modifica un atributo que esta incluido en una condicion de WHERE.

5.3.3.

Eliminaci´ on

Para eliminar entradas de una tabla se usa DELETE. DELETE FROM nombre_table [WHERE condici´ on] Elimina las filas que satisfagan la condicion del WHERE.

5.4.

Instrucciones de definici´ on de datos

Corresponde al DDL y permite crear y borrar objetos.

5.4.1.

Creaci´ on de tablas

CREATE TABLE nombre_tabla ( nombre_columna1 tipo_columna1 nombre_columna2 tipo_columna2 ... nombre_columnaN tipo_columnaN );

[valor_por_omision], [valor_por_omision], [valor_por_omision]

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.4. INSTRUCCIONES DE DEFINICION

38

El nombre de la tabla debe comenzar por una letra y no puede tener m´as de 18 caracteres para asegurar portabilidad. La definici´on de los campos consta del nombre de la columna, el tipo y opcionalmente el valor que toma por omisi´on y las condiciones de integridad a nivel de columna, tales como especificar si permite valores nulos o duplicados, si es clave primaria, etc. Despu´es de las definiciones se pueden poner condiciones de integridad de la tabla. Si no se especifica lo contrario se admite que cualquier columna tome valores nulos. Para evitarlo basta a˜ nadir a la definici´on de la columna la condici´on NOT NULL. CREATE TABLE REGISTRO ( CB NUMBER(2), R NUMBER(5), I NUMBER(4) NOT NULL, PRIMARY KEY (CB,R), FOREIGN KEY I REFERENCE LIBRO ON DELTE CASCADE ); Tambi´en es posible insertar los datos en el momento de crear la tabla utilizando una subconsulta: CREATE TABLE nombre_tabla [(lista, atributos)] AS (SELECT ... FROM ... ); ´ Si no es necesario almacenar la tabla, una alternativa es crear vistas. Estas se calcula cada vez que se realiza una consulta sobre ellas. Algunas son actualizables, de modo que permiten ser modificadas y los cambios realizados sobre ellas se repercuten en las tablas implicadas. CREATE VIEW nombre_vista [(lista, atributos)] AS (SELECT ... FROM ... ); Las vistas son u ´ tiles para controlar el acceso a los datos, otorgando a unos usuarios privilegios sobre las vistas pero no sobre las tablas implicadas.

5.4.2.

Tipos de datos (dominios)

Para cadenas de caracteres de longitud fija entre 1 y 255 se utiliza el tipo CHAR, que opcionalmente se puede limiar a n caracteres usando CHAR(n). Sus equivalentes en el ANSI SQL son CHAR(n) y CHARACTER(n). Para cadenas de caracteres de longitud variable entre 1 y 2000 se utiliza el tipo VARCHAR, que opcionalmente se puede limiar a n caracteres usando VARCHAR(n). Tambi´en se le puede llamar VARCHAR2. Su equivalente en el ANSI SQL es CHAR VARYING(n) Para valores num´ericos se utiliza el tipo NUMBER, que si no se especifica la cantidad de d´ıgitos ni decimales representa valores de tipo float. Los par´ametros opcionales de

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.4. INSTRUCCIONES DE DEFINICION

39

este tipo con el n´ umero de d´ıgitos p y el n´ umero de d´ıgitos decimales s, de modo que 0 ≤ s ≤ p ≤ 38, siempre que p ≥ 1. En estos par´ametros no se consideran como d´ıgitos el punto decimal ni el signo. Sus equivalentes en el ANSI SQL son NUMERIC(p,s) y DECIMAL(p,s). El ANSI SQL tambi´en contempla INT, INTEGER y SMALLINT para n´ umeros enteros; FLOAT REAL y DOUBLE PRECISION para los n´ umeros reales. Las fechas y horas se almacenan mediante el tipo DATE, que contempla siglo, a˜ no, mes, d´ıa, hora, minutos y segundos. El rango de fechas abarca desde el 1 de enero de 4712 AC hasta el 31 de diciembre de 4712 DC. Con frecuencia se utiliza como valor por defecto la fecha y hora actual del sistema, accesible mediante SYSDATE.

5.4.3.

´Indices

La creaci´on de ´ındices permite acelerar las b´ usquedas, cre´andolos sobre los atributos que con m´as frecuencia se utilizan en las cl´ausulas WHERE. Como contrapartida, se ralentizan las modificaciones de la tabla posteriores a la creaci´on del ´ındice. La t´actica recomendad es crear el ´ındice despu´es de haber insertado todas las filas en la tabla. Para considerar que se hace necesario un ´ındice las tablas deber´ıan tener m´as de 1000 filas. CREATE INDEX nombre_indice ON nombre_tabla (lista, columnas); Los valores repetidos se almacenan en el ´ındice, a menos que se indique lo contrario utilizando CREATE UNIQ INDEX. Adem´as as´ı forcar´ıa la tabla a no tener duplicados en los ´ındices. Los valores nulos no se almacenan en los ´ındices. Pueden existir dos o m´as ´ındices con el mismo nombre asociados a distintas tablas.

5.4.4.

Modificaci´ on de objetos

La cl´ausula ALTER permite modificar tablas, ´ındice y otros objetos. ALTER TABLE nombre_tabla MODIFY (definicion de columna); Al modificar las columnas ´estas pueden ensancharse, pero no estrecharse. Tampoco se pueden dejar de admitir valores nulos en una columna si ´esta ya contiene alg´ un valor nulo.

5.4.5.

Eliminaci´ on de objetos

La cl´ausula DROP permite eliminar tablas, ´ındice y otros objetos.

´ Miguel Angel Vilela Garc´ıa

´ DE DATOS 5.4. INSTRUCCIONES DE DEFINICION

40

DROP TABLE nombre_tabla; DROP VIEW nombre_vista; DROP INDEX nombre_indice [ON tabla]; Al eliminar una tabla se eliminan tanto su contenido (instancia) como su definici´on (esquema). Si se elimina una tabla que est´a implicada en una vista, ´esta queda inutilizables, pero no se elimina. La cl´ausula RENAME permite renombrar tablas e ´ındices. La cl´ausula SYNONYN permite crear alias para tablas e ´ındices. CREATE SYNONYN un_alias FOR una_tabla;

´ Miguel Angel Vilela Garc´ıa

TEMA 6

Introducci´on a la normalizaci´on

La teor´ıa de la normalizaci´on es una formalizaci´on del dise˜ no de bases de datos que intenta obtener de manera algor´ıtmica esquemas de bases de datos que se encuentren en una forma normal adecuada. Supongamos que tenemos un esquema de bases de datos que modeliza una empresa del mundo real B = (R1 , . . . , Rn ). Sabemos que no todas las tuplas son formalmente v´alidas puesto que deben respetar las condiciones de integridad CINT que intentan ajustar el modelo al mundo real. Una extensi´on de un esquema de bases de datos es v´alida si y s´olo si las tuplas satisfacen las condiciones de integridad. El objetivo del dise˜ no de bases de datos es obtener un par (B, CINT ) que satisfaga las siguientes propiedades: Reducir al m´ınimo las redundancias. Permitir la recuperaci´on de la informaci´on de manera eficaz. Simplificar la verificaci´on de las condiciones de integridad.

6.1.

Dise˜ nos equivalentes

Dado un dise˜ no inicial querr´ıamos encontrar un dise˜ no equivalente mejor. Dados dos esuqemas B, B ′ se dice que el dise˜ no B ′ tiene al menos tanta informaci´on como B si cualquier extensi´on v´alida de B se puede obtener a partir de una extensi´on v´alida de B ′ a trav´es de un proceso finito. Ejemplo: B

= BANCO (DNI, N, CDC, CS, NC, SLD, S, CDS)

B’ = CLIENTE (DNI, N, CDC) SUCURSAL (CS, S, CDS) 41

´ REVERSIBLE POR YUNCION ´ 6.2. DESCOMPOSICION

42

DEPOSITO (DNI, CS, NC, SLD) BANCO = CLIENTE * SUCURSAL * DEPOSITO CLIENTE = S (DNI, N, CDC) (BANCO) Los dise˜ nos B y B ′ permiten almacenar la misma cantidad de informaci´on, pero B ′ es mejor porque tiene mucha menos redundancia. Adem´as, el dise˜ no B no permite sucursales sin cuentas abiertas ni clientes que no tengan cuentas abiertas. Las claves primarias de B ′ son: CP_CLIENTE (DNI) CP_SUCURSAL (CS) CP_DEPOSITO (DNI, CS, NC) y las condiciones de integridad: (∀d)(∃s)(d[CS] = s[CS]) (∀d)(∃c)(d[DNI] = c[DNI]) En el dise˜ no B: CP_BANCO (DNI, CS, NC) dom(b) = dom(b′ ) = BANCO (∀b, b′ ) ((b[DNI] 6= b′ [DNI]) ∨ ((b[N] = b′ [N]) ∧ (b[CDC] = b′ [CDC]))) (∀b, b′ ) ((b[CS] 6= b′ [CS]) ∨ ((b[S] = b′ [S]) ∧ (b[CDS] = b′ [CDS]))) (∀b, b′ ) ((b[NC] 6= b′ [NC]) ∨ ((b[CS] = b′ [CS]) ∧ (b[SLD] = b′ [SLD])))

6.2.

Descomposici´ on reversible por yunci´ on

Dada una tabla R se denomina descompsici´on de R al conjunto de tablas R1 , R2 , . . . , Rn donde Ri son proyecciones de R tales que R1 ∗ R2 ∗ · · · ∗ Rn tiene igual esquema que R. Ejemplo: Esquema original: BANCO (DNI, N, CDC, NC, SLD) 1a descompsici´on:

´ Miguel Angel Vilela Garc´ıa

´ REVERSIBLE POR YUNCION ´ 6.2. DESCOMPOSICION

43

CLIENTE (DNI, N, CDC) CUENTA (NC, SLD) DEPOSITO (DNI, NC) 2a descompsici´on: CLIENTE (DNI, N, CDC) DEPOSITO (DNI, NC) 3a descompsici´on: CUENTA (NC, SLD) TITULARES (CNI, N, CDC, NC) 4a descompsici´on: no permite obtener la tabla BANCO con yunciones, pues contendr´ıa demasiadas tuplas. V1 (DNI, N, CDC, SLD) V2 (NC, SLD) Es necesario que las descomposiciones sean reversibles por yunci´on, i.e. que al hacer la yunci´on natural de sus tablas se obtiene la tabla original. En el ejemplo anterior la u ´ ltima no es reversible por yunci´on, i.e. BANCO 6= V 1 ∗ V 2 Teorema: Sea una relaci´on R(U), donde U es el conjuto universal de todos los atributos de R, sea A, B, C una partici´on de U, entonces S = P (A, B)(R) T = P (A, C)(R)



=⇒ R ( S ∗ T

Veamos por qu´e R ⊆ S ∗ T Sean < a, b, c >∈ R ⇒ < a, b >∈ S∧ < a, c >∈ T ⇒ < a, b, c >∈ S ∗ T ⇒ R ⊆ S ∗ T Veamos por qu´e R 6= S ∗ T Sean < a, b, c >6∈ R, < a, b, d >∈ R, < a, e, c >∈ R ⇒< a, b >∈ S∧ < a, c >∈ T ⇒< a, b, c >∈ S∗T Se dice que una tupla es esp´ urea si t ∈ R1 ∗ · · · ∗ Rn ∧ t 6∈ R. Las tuplas esp´ ureas representan informaci´on mal relacionada y no interesan. Teorema: Para que la descomposici´on de R(U) en S(A), T (B) sea reversible por yunci´on es condici´on necesaria y suficiente que para cualesquiera tuplas de R con el mismo valor en A tambi´en existan en R las que resultan de intercambiar los valores de B. ´ Miguel Angel Vilela Garc´ıa

´ REVERSIBLE POR YUNCION ´ 6.2. DESCOMPOSICION

44

R = S ∗ T ⇔ (∀ t1 , t2 ∈ R) ((t1 [A] 6= t2 [A])∨ (∃ t1 , t2 ∈ R) (((t1 [A] = t1 [A]) ∧ (t1 [B] = t2 [B]) ∧ (t1 [C] = t2 [C])) ∧ ((t2 [A] = t2 [A]) ∧ (t2 [B] = t1 [B]) ∧ (t2 [C] = t2 [C])))) Se denomina a esto una dependencia plural entre A y B y se denota por A ։ B. Con esta notaci´on se puede reescribir el anterior teorema como: R=S∗T ⇔ A։B ⇔ A։C Veamos que la condici´on es necesaria (⇐): Sean < a, b, c >∈ S ∗ T ⇒ < a, c >∈ S∧ < a, c >∈ T ⇒ (∃ x, y)((< a, b, x >∈ R) ∧ (< a, y, c >∈ R)) ⇒ < a, b, c >∈ R Veamos que la condici´on es suficiente (⇒): Sean < a, x1 , y1 >, < a, x2 , y2 >∈ R, tenemos que ver que < a, x2 , y1 >, < a, x1 , y2 >∈ R < a, x1 >, < a, x2 >∈ S < a, y1 >, < a, y2 >∈ T



⇒ < a, x1 , y2 >, < a, x2 , y1 >∈ S ∗ T = R

Un tipo de dependencia plural importante son las dependencias funcionales. Se dice que hay una dependencia funcional entre A y B si cuando se repite A tambi´en se repite B; se denota por A → B. Es un tipo de condici´on de integridad a´ un m´as importante que la dependencia plural. A →B ⇒ R = S ∗T

(6⇐)

Al cambiar el dise˜ no no es necesario mantener la dependencia plural, pero s´ı es necesario mantener la dependencia funcional.

´ Miguel Angel Vilela Garc´ıa