u 4,5,6

<1

Unidad 4

Principiossobre

Bases de DatosRelacionales

Autor: Jorge Sánchez (www.jo r gesanchez.ne t ) año 2004e-mail: m ailto:info@jor ges anchez.n et

Este trabajo está protegido bajo una licencia de Creative Commons del tipo Attribution-NonCommercial-ShareAlike.Para ver una copia de esta licencia visite: http://creativecommons.org/licenses/by-nc-sa/2.0/ o envíe una carta a:Creative Commons, 559 Nathan Abbott Way, Stanford, California94305, USA.

<3

Los contenidos de este documento están protegidos bajo una licencia de Creative Commonsdel tipo Attribution-Noncomercial-Share Alike. Con esta licencia:

Eres libre de: Copiar, distribuir y mostrar este trabajo Realizar modificaciones de este trabajo

Bajo las siguientes condiciones:

Attribution (Reconocimiento). Debe figurar siempre el autor original de este trabajo

Noncommercial (No comercial). No puedes utilizar este trabajo con propósitos comerciales.

Share Alike (Compartir igual). Si modificas, alteras o construyes nuevos trabajos a partir de este, debes distribuir tu trabajo con una licencia idéntica a ésta

Si estas limitaciones son incompatible con tu objetivo, puedes contactar con el autor para solicitar el permiso correspondiente

No obstante tu derecho a un uso justo y legítimo de la obra, así como derechos no se ven de manera alguna afectados por lo anteriormente expuesto.

Esta nota no es la licencia completa de la obra, sino una traducción del resumen en formato comprensible del texto legal. La licencia original completa (jurídicamente válida y pendiente de su traducción oficial al español) está disponible en http://cr e at i vecommons.org/ li censes/by-nc-sa/2.0/ l ega l code

<5>

íínnddiicceeíndice.............................................................................................. 5 modelos lógicos de datos............................................................... 7

esquema canónico .............................................................................. 7tipos de base de datos ......................................................................... 7

modelo relacional ........................................................................ 11 introducción ...................................................................................... 11 tablas ............................................................................................... 12 dominios........................................................................................... 13 claves ............................................................................................... 13 nulos ................................................................................................ 13 restricciones ...................................................................................... 14las 12 reglas de Codd ....................................................................... 14

paso del esquema ER al modelo relacional................................. 17 transformaciones de entidades fuertes ................................................. 17 transformación de relaciones .............................................................. 17 entidades débiles............................................................................... 19

generalizaciones y especificaciones ..................................................... 20 normalización del esquema relacional ....................................... 23

problemas del esquema relacional...................................................... 23 formas normales................................................................................ 23

apéndice: términos técnicos......................................................... 31

<7

mmooddeellooss llóóggiiccooss dede dadattooss

esquema canónico

Mundo real Esquema

ConceptualEsquema canónico

Esquemainterno BD

Físical

ModeloLógico Tiene en cuenta el tipo de DBMS a utilizar

Ilustración 1, Posición de esquema canónico dentro de los esquema de creación de una base de datos

El esquema canónico o lógico global, es un esquema que presenta de forma conceptual la estructura de una base de datos. Es un esquema que depende del tipo de DBMS que vayamos a utilizar.

Se crea a partir del modelo conceptual (véase el documento Diseño Conceptual de Bases de Datos en ww w .jorgesan c hez.ne t /b d ). Y serviría para cualquier base de datos comercial del tipo elegido en el esquema (hay esquemas relacionales, en red, jerárquicos,...)

tipos de base de datos

jerárquicasEn ellas se organiza la información se organiza con un jerarquía en la que la relación entre las entidades de este modelo siempre es del tipo padre / hijo. De esta forma hay una serie de nodos que contendrán atributos y que se relacionarán con nodos hijos de forma que puede haber más de un hijo para el mismo padre (pero un hijo sólo tiene un padre).

Las entidades de este modelo se llaman segmentos y los atributos campos. La forma visual de este modelo es de árbol invertido, en la parte superior están los padres y en la inferior los hijos.

Personal

Tareas

<8

Diseño conceptual de bases de datosmodelos lógicos de datos

Departamento

Documentos

Ilustración 2, Ejemplo de esquema jerárquico

en redSe trata de un modelo que se utilizó durante mucho tiempo. Organiza la información en registros y enlaces. Los registros representan las entidades del modelo entidad / relación. En los registros se almacenan los datos utilizando atributos. Los enlaces permiten relacionar los registros de la base de datos.

El modelo en red más aceptado es el llamado codasyl, que durante mucho tiempo se ha convertido en un estándar.

Las bases de datos en red son parecidas a las jerárquicas sólo que en ellas puede haber más de un padre. En este modelo se pueden representar perfectamente relaciones varios a varios. Pero su dificultad de manejo y complejidad hace que se estén abandonando completamente.

relacionalesLos datos se muestran en forma de tablas y relaciones. Este es el modelo que se comenta en el presente documento. De hecho es el claramente más popular.

orientadas a objetosDesde la aparición de la programación orientada a objetos (POO u OOP) se empezó a pensar en bases de datos adaptadas a estos lenguajes. En estos lenguajes los datos y los procedimientos se almacenan juntos. Esta es la idea de las bases de datos orientadas a objetos.

A través de esta idea se intenta que estas bases de datos consiguen arreglar las limitaciones de las relacionales. Por ejemplo el problema de la herencia, tipos definidos por el usuario, disparadores almacenables en la base de datos, soporte multimedia...

Se supone que son las bases de datos de tercera generación (la primera fue las bases de datos en red y la segunda las relacionales), lo que significa que el futuro parece estar a favor de estas bases de datos. Pero siguen sin reemplazar a las relacionales (aunque cada vez hay más).

Su modelo conceptual se suele diseñar en UML y el lógico en ODMG 3.0

objeto relacionales

<9

Tratan de ser un híbrido entre el modelo relacional y el orientado a objetos. El problema de las bases de datos orientadas a objetos es que requieren reinvertir de nuevo para convertir las bases de datos. En las bases de datos objeto relacionales se intenta conseguir una compatibilidad relacional dando la posibilidad de integrar mejoras de la orientación a objetos.

<9>

Copyright-Copyleft: © Jorge Sánchez 2004

Estas bases de datos se basan en el estándar SQL 99 que dictó las normas para estas bases de datos. En ese estándar se añade a las bases relacionales la posibilidad de almacenar procedimientos de usuario, triggers, tipos definidos por el usuario, consultas recursivas, bases de datos OLAP, tipos LOB,...

Las últimas versiones de la mayoría de las grandes bases de datos relacionales (Oracle, SQL Server, Informix, ...) son objeto relacionales.

<11

mmooddeelloo rreellaacciioonnaall

introducciónEdgar Frank Codd a finales definió las bases del modelo relacional a finales de los 60. Trabajaba para IBM empresa que tardó un poco en implementar sus bases. Pocos años después el modelo se empezó a implementar cada vez más, hasta ser el modelo de bases de datos más popular.

En las bases de Codd se definían los objetivos de este modelo:

Independencia física. La forma de almacenar los datos, no debe influir en su manipulación lógica

Independencia lógica. Las aplicaciones que utilizan la base de datos no deben ser modificadas por que se modifiquen elementos de la base de datos.

Flexibilidad. La base de datos ofrece fácilmente distintas vistas en función de los usuarios y aplicaciones.

Uniformidad. Las estructuras lógicas siempre tienen una única forma conceptual

(las tablas)

Sencillez.

En 1978, IBM desarrolla el lenguaje QBE. Que aproximaba la idea relacional a sus ficheros VSAM. En 1979 Oracle se convierte en el primer producto comercial DBMS relacional (RDBMS). En 1980 aparece Ingres que utilizaba el lenguaje Quel que implementaba el cálculo relacional.

evolución del modelo relacionalAño Hecho1970 Codd publica las bases del modelo relacional1971-72 Primeros desarrollos teóricos1973-78 Primeros prototipos1978 Aparece el lenguaje QBE1979 Aparece Oracle1980 Aparece Ingres1981 Aparece SQL1982 Aparece DB21986 ANSI normaliza el SQL (SQL/ANSI)1987 SQL de ISO1990 Versión dos del modelo relacional (RM/V2)1992 SQL 921998 SQL 3

atributo 1 atributo 2 atributo 3 .... atributo nvalor 1,1 valor 1,2 valor 1,3 .... valor 1,nvalor 2,1 valor 2,2 valor 2,3 .... valor 2,n..... ..... ...... .... .....valor m,1 valor m,2 valor m,3 .... valor m,n

<12

Diseño conceptual de bases de datosmodelo relacional

tablasLas bases de datos relacionales se basan en el uso de tablas (también se las llama relaciones). Las tablas se representan gráficamente como una estructura rectangular formada por filas y columnas. Cada columna almacena información sobre una propiedad determinada de la tabla (se le llama también atributo), nombre, dni, apellidos, edad,.... Cada fila posee una ocurrencia o ejemplar de la instancia o relación representada por la tabla (a las filas se las llama también tuplas).

NOMBRE

Å tupla 1Å tupla 2....Å tupla m

Ilustración 3, Representación de una tabla en el modelo relacional

terminología relacional

Tupla. Cada fila de la tabla (cada ejemplar que la tabla representa)

Atributo. Cada columna de la tabla

Grado. Número de atributos de la tabla

Cardinalidad. Número de tuplas de una tabla

Dominio. Conjunto válido de valores representables por un atributo.

tipos de tablas

Persistentes. Sólo pueden ser borradas por los usuarios:

Base. Independientes, se crean indicando su estructura y sus ejemplares.

Vistas. Son tablas que sólo almacenan una definición de consulta, resultado de la cual se produce una tabla cuyos datos proceden de las bases o de otras vistas e instantáneas. Si los datos de las tablas base cambian, los de la vista que utiliza esos datos también cambia.

Instantáneas. Son vistas (creadas de la misma forma) que sí que almacenan los datos que muestra, además de la consulta que dio lugar a esa vista. Sólo modifican su resultado (actualizan los datos) siendo refrescadas por el sistema cada cierto tiempo.

Temporales. Son tablas que se eliminan automáticamente por el sistema. Pueden ser de cualquiera de los tipos anterior

<13


dominiosLos dominios suponen una gran mejora en este modelo ya que permiten especificar los posibles valores válidos para un atributo. Cada dominio incorpora su nombre y una definición del mismo. Ejemplos de dominio:

Dirección: 50 caracteres

Nacionalidad: Español, Francés, Italiano,...

Los dominios pueden ser también compuestos a partir de otros (año, mes y día = fecha)

claves

clave candidataConjunto de atributos de una tabla que identifican unívocamente cada tupla de la tabla.

clave primariaClave candidata que se escoge como identificador de las tuplas.

clave alternativaCualquier clave candidata que no sea primaria

clave externa o secundariaAtributo de una tabla relacionado con una clave de otra tabla.

nulosLos valores nulos indican contenidos de atributos que no tienen ningún valor. En claves secundarias indican que el registro actual no está relacionado con ninguno. En otros atributos indica que no se puede rellenar ese valor por la razón que sea.

Las bases de datos relacionales admiten utilizar ese valor en todo tipo de operaciones. Eso significa definir un tercer valor en la lógica. Además de el valor verdadero o falso, existe el valor para los nulos.

La razón de este tercer valor ambiguo es que comparar dos atributos con valor nulo, no puede resultar ni verdadero, ni falso. De hecho necesitamos definir la lógica con este valor:

verdadero Y (AND) nulo da como resultado, nulo

falso Y (AND) nulo da como resultado, falso

<14

verdadero O (OR) nulo da como resultado, verdadero

falso O nulo da como resultado nulo

la negación de nulo, da como resultado nulo

<15

Diseño conceptual de bases de datosmodelo relacional

restriccionesSe trata de unas condiciones de obligado cumplimiento por los datos de la base de datos. Las hay de varios tipos.

inherentesSon aquellas que no son determinadas por los usuarios, sino que son definidas por el hecho de que la base de datos sea relacional. Por ejemplo:

No puede haber dos tuplas iguales

El orden de las tuplas no importa

El orden de los atributos no importa

Cada atributo sólo puede tomar un valor en el dominio en el que está inscrito

semánticasEl modelo relacional permite a los usuario incorporar restricciones personales a los datos. Las principales son:

Clave primaria. Hace que los atributos marcados como clave primaria no puedan repetir valores.

Unicidad. Impide que los valores de los atributos marcados de esa forma, puedan repetirse.

Obligatoriedad. Prohíbe que el atributo marcado de esta forma no tenga ningún valor

Integridad referencial. Prohíbe colocar valores en una clave externa que no estén reflejados en la tabla donde ese atributo es clave primaria.

Regla de validación. Condición que debe de cumplir un dato concreto para que sea actualizado.

las 12 reglas de CoddPreocupado por los productos que decían ser sistemas gestores de bases de datos relacionales (RDBMS) sin serlo, Codd publica las 12 reglas que debe cumplir todo DBMS para ser considerado relacional. Estas reglas en la práctica las cumplen pocos sistemas relacionales. Las reglas son:

1> Información. Toda la información de la base de datos debe estar representada explícitamente en el esquema lógico. Es decir, todos los datos están en lastablas.

2> Acceso garantizado. Todo dato es accesible sabiendo el valor de su clave y el nombre de la columna o atributo que contiene el dato.

<16

3> Tratamiento sistemático de los valores nulos. El DBMS debe permitir el tratamiento adecuado de estos valores

<15>


4> Catálogo en línea basado en el modelo relacional. Los metadatos deben de ser accesibles usando un esquema relacional.

5> Sublenguaje de datos completo. Al menos debe de existir un lenguaje que permita el manejo completo de la base de datos. Este lenguaje, por lo tanto, debe permitir realizar cualquier operación.

6> Actualización de vistas. El DBMS debe encargarse de que las vistas muestren la última información

7> Inserciones, modificaciones y eliminaciones de dato nivel. Cualquier operación de modificación debe actuar sobre conjuntos de filas, nunca deben actuar registro a registro.

8> Independencia física. Los datos deben de ser accesibles desde la lógica de la base de datos aún cuando se modifique el almacenamiento.

9> Independencia lógica. Los programas no deben verse afectados por cambios en las tablas

10> Independencia de integridad. Las reglas de integridad deben almacenarse en la base de datos (en el diccionario de datos), no en los programas de aplicación.

11> Independencia de la distribución. El sublenguaje de datos debe permitir que sus instrucciones funciones igualmente en una base de datos distribuida que en una que no lo es.

12> No subversión. Si el DBMS posee un lenguaje que permite el recorrido registro a registro, éste no puede utilizarse para incumplir las reglas relacionales.

<17

papassoo ddeell eessququeemama EERR alal mmooddeelloo rreellaacciioonnaall

transformaciones de entidades fuertesEn principio las entidades fuertes del modelo Entidad Relación son transformados al modelo relacional siguiendo estas instrucciones:

Entidades. Las entidades pasan a ser tablas

Atributos. Los atributos pasan a ser columnas.

Identificadores principales. Pasan a ser claves primarias

Identificadores candidatos. Pasan a ser claves candidatas.

Esto hace que la transformación sea de esta forma:

Id e n t i f i c a d o r Atributo1

Nombre Nombre(I d e n t i f i c a d o r , Atributo 1, Atributo 2, Atributo 3)

Atributo2 Atributo2

Ilustración 4,Transformación de una entidad fuerte al esquema relacional

transformación de relacionesLa idea inicial es transformar a cada relación en una tabla en el modelo relacional. Pero hay que distinguir según el tipo de relación.

relaciones varios a variosEn las relaciones varios a varios, la relación se transforma en una tabla cuyos atributos son: los atributos de la relación y las claves de las entidades relacionadas (que pasarán a ser claves externas). La clave de la tabla la forman todas las claves externas:

Atributo1

Nombre

I d e n t i f i c a d o r 1 Atributo1 I d e n t i f i c a d o r2

<18

Nombre(I d e n t i f i c a d o r 1 , I d e n t i f i c a d o r 2 ,Atributo1,Atributo2)

Ilustración 5, Transformación de una relación varios a varios

<19

Diseño conceptual de bases de datospaso del esquema ER modelo relacional

relaciones de orden nLas relaciones ternarias, cuaternarias y n-arias que unen más de dos relaciones se transforman en una tabla que contiene los atributos de la relación más los identificadores de las entidades relacionadas. La clave la forman todas las claves externas:

Id e n t i f i c a d o r 1 Atributo1

NombreI d e n t i f i c a d o r 3

I d e n t i f i c a d o r 2

Atributo1 I d e n t i f i c a d o r 4

Nombre(I d e n t i f i c a d o r 1 , I d e n t i f i c a d o r 2 , , I d e n t i f i c a d o r 3 , I d e n t i f i c a d o r 4 ,Atributo1,Atributo2)

Ilustración 6, Transformación en el modelo relacional de una entidad n-aria

relaciones uno a varios y uno a unoLas relaciones binarios de tipo uno a varios no requieren ser transformadas en una tabla en el modelo relacional. En su lugar la tabla del lado varios (tabla relacionada) incluye como clave externa1 el identificador de la entidad del lado uno (tabla principal):

Atributo2

Atributo1 Atributo3

Entidad1 Nombre Entidad2

Iden tific ador1Identific ador2

Entidad2(I d e n t i f i c a d o r 2 ,Atributo3) Entidad1(I d e n t i f i c a d o r 1 ,Atributo1,Identificador2,Atributo2)

Ilustración 7, Transformación de una relación uno a varios

<20

Así en el dibujo, el identificador2 en la tabla Entidad1 pasa a ser una clave externa. En el caso de que el número mínimo de la relación sea de cero (puede haber ejemplares de la entidad uno sin relacionar), se deberá permitir valores nulos en la clave externa

1 Clave externa, clave ajena, clave foránea, clave secundaria y foreign key son sinónimos

<19


identificador2. En otro caso no se podrán permitir (ya que siempre habrá un valor relacionado).

En el caso de las relaciones uno a uno, ocurre lo mismo: la relación no se convierte en tabla, sino que se coloca en una de las tablas (en principio daría igual cuál) el identificador de la entidad relacionada como clave externa.

En el caso de que una entidad participe opcionalmente en la relación, entonces es el identificador de ésta el que se colocará como clave externa en la tabla que representa a la otra entidad.

relaciones recursivasLas relaciones recursivas se tratan de la misma forma que las otras, sólo que un mismo atributo puede figurar dos veces en una tabla como resultado de la transformación:

I d e n t i f i c ad o r Rol2

Id e n t i f i c a d o r Rol2

Entidad Entidad

Atributo 1 Rol1Relac. Atributo 1 Rol1

Relac.

Entidad(I d e n t i f i c ad o r ,Atributo1,Identificador Rol 1)

Entidad(I d e n t i f i c a d o r ,Atributo1)

Relac(I d e n t i f i c a d o r R o l 1 , I d e n t i f i c a d o r R o l 2,Atributo1)

Ilustración 8, Transformación de relaciones recursivas en el modelo relacional

entidades débilesToda entidad débil incorpora una relación implícita con una entidad fuerte. Esta relación no necesita incorporarse como tabla en el modelo relacional. Sí se necesita incorporar la clave de la entidad fuerte como clave externa en la entidad débil. Es más, normalmente esa clave externa forma parte de la clave principal de la tabla que representa a la entidad débil. El proceso es:

Atributo1 Atributo2

Entidad Fuerte

Entidad Débil

Id Fuerte Id Débil

<20

Entidad Fuerte(Id F u e r t e , Atributo 1) Entidad1(Id D é b i l , I d F u e r t e , Atributo2)

Ilustración 9, transformación de una entidad débil en el modelo relacional

<21

Diseño conceptual de bases de datospaso del esquema ER modelo relacional

En ocasiones el identificador de la entidad débil es suficiente para identificar los ejemplares de dicha entidad, entonces ese identificador quedaría como clave principal, pero el identificador de la entidad fuerte seguiría figurando como clave externa en la entidad débil.

generalizaciones y especificacionesLas generalizaciones y/o especificaciones se convierten al modelo relacional de esta forma:

1> Las subentidades pasan a ser tablas.

2> Si la clave de la superentidad es distinta de las subentidades, entonces se coloca el identificador de la superentidad en cada subentidad como clave externa:

Id1 Atributo1

Atributo2

SuperentidadAtributo3

Subentidad1 Subentidad2

I d 2 I d 3

Superentidad(I d 1 , Atributo 1) Subentidad2(I d 3 , Atributo 3, Id1)Subentidad1(I d 2 , Atributo 2, Id1)

Ilustración 10, Proceso de transformación de relaciones ISA con clave propia

3> Si la clave es la misma, entonces todas las entidades tendrán la misma columna como identificador:

Id Atributo1

Atributo2



I d Id

Superentidad(I d , Atributo 1) Subentidad2(I d , Atributo 3)Subentidad1(I d , Atributo 2)

<22

Ilustración 11, Proceso de transformación de relaciones ISA en el modelo relacional si tienen la misma clave

<21>


4> La superentidad debe generar una tabla sólo en el caso de que haya posibilidad de que exista un ejemplar de dicha entidad que no sea ejemplar de lassubentidades. De otro modo basta con generar las tablas de las subentidades e incluir los atributos de la entidad superior:

Id Atributo1

Atributo2



Id Id

Subentidad2(I d , Atributo 3, Atributo1) Subentidad1(I d , Atributo 2, Atributo1)

Ilustración 12, Paso de relaciones ISA al modelo relacional cuando toda superentidad figura como subentidad

<23

nnoorrmmaalilizzaacciióónn ddeell eessququeemama rreellaacciioonnaall

problemas del esquema relacionalUna vez obtenido el esquema relacional resultantes del modelo entidad relación que representaba la base de datos, normalmente tendremos una buena base de datos. Pero otras veces, debido a fallos en el diseño o a problemas indetectables en esta fase del diseño, tendremos un esquema que puede producir una base de datos que incorpore estos problemas:

Redundancia. Se llama así a los datos que se repiten continua e innecesariamente por las tablas de las bases de datos.

Ambigüedades. Datos que no clarifican suficientemente el registro al que representan.

Pérdida de restricciones de integridad.

Anomalías en operaciones de modificación de datos. El hecho de que al insertar un solo elemento haya que repetir tuplas en una tabla para variar unos pocos datos. O que eliminar un elemento suponga eliminar varias tuplas.

El principio fundamental reside en que las tablas deben referirse a objetos o situaciones muy concretas. Lo que ocurre es que conceptualmente es difícil obtener ese problema.

La solución suele ser dividir la tabla con problemas en otras tablas más adecuadas.

formas normalesLas formas normales se corresponde a una teoría de normalización iniciada por el propio Codd y continuada por otros autores (entre los que destacan Boyce y Fagin). Codd definió en 1970 la primera forma normal, desde ese momento aparecieron la segunda, tercera, la Boyce-Codd, la cuarta y la quinta forma normal.

Una tabla puede encontrarse en primera forma normal y no en segunda forma normal, pero no al contrario. Es decir los números altos de formas normales son más restrictivos (la quinta forma normal cumple todas las anteriores).

La teoría de formas normales es una teoría absolutamente matemática, pero en el presente manual se describen de forma intuitiva.

primera forma normal (1FN)Una tabla se encuentra en primera forma normal si impide que un atributo de una tupla pueda tomar más de un valor. La tabla:

TRABAJADORDNI Nombre Departamento

<24

12121212A Andrés Mantenimiento12345345G Andrea Dirección

Gestión

<25

Diseño conceptual de bases de datosapéndice: términos técnicos

Visualmente es un tabla, pero no una tabla relacional (lo que en terminología de bases de datos relacionales se llama relación). No cumple la primera forma normal. Lo cumpliría si:

TRABAJADORDNI Nombre Departamento12121212A Andrés Mantenimiento12345345G Andrea Dirección12345345G Andrea Gestión

Esa tabla sí esta en primera forma normal.

dependencias funcionalesSe dice que un conjunto de atributos (Y) depende funcionalmente de otro conjunto de atributos (X) si para cada valor de X hay un único valor posible para Y. Simbólicamente se denota por X→Y.

Por ejemplo el nombre de una persona depende funcionalmente del DNI, para un DNI concreto sólo hay un nombre posible. En la tabla ejemplo anterior, el departamento no tiene dependencia funcional, ya que para un mismo DNO puede haber más de un departamento posible.

Al conjunto X del que depende funcionalmente el conjunto Y se le llama

determinante. Al conjunto Y se le llama implicado.

dependencia funcional completaUn conjunto de atributos (Y) tiene una dependencia funcional completa sobre otro conjunto de atributos (X) si Y tiene dependencia funcional de X y además no se puede obtener de X un conjunto de atributos más pequeño que consiga una dependencia funcional de Y.

Por ejemplo en una tabla de clientes, el conjunto de atributos formado por el nombre y el dni producen una dependencia funcional sobre el atributo apellidos. Pero no es plena ya que el dni sólo también produce una dependencia funcional sobre apellidos. El dni sí produce una dependencia funcional completa sobre el campo apellidos.

Una dependencia funcional completa se denota como X⇒Y

dependencia funcional elementalSe produce cuando X e Y forman una dependencia funcional completa y además Y es un único atributo.

dependencia funcional transitivaEs más compleja de explicar, pero tiene también utilidad. Se produce cuando tenemos tres conjuntos de atributos X, Y y Z. Y depende funcionalmente de X (X→Y), Z depende funcionalmente de Y (Y→Z). Además X no depende funcionalmente de Y. Entonces ocurre que X produce una dependencia funcional transitiva sobre Z. Esto se denota como:

<26

(X →Z)Por ejemplo si X es el atributo Número de Clase de un instituto, e Y es el

atributo Código Tutor. Entonces X→Y (el tutor depende funcionalmente del número de clase). Si Z representa el Código del departamento, entonces Y→Z (el código del departamento depende funcionalmente del código tutor, cada tutor sólo puede estar en un

<25


departamento). Como no ocurre que Y→X (el código de la clase no depende funcionalmente del código tutor, un código tutor se puede corresponder con varios códigos de clase). Entonces X →Z (el código del departamento depende transitivamente del código de la clase).

segunda forma normal (2FN)Ocurre si una tabla está en primera forma normal y además cada atributo que no sea clave, depende de forma funcional completa respecto de cualquiera de las claves. Toda la clave principal debe hacer dependientes al resto de atributos, si hay atributos que depende sólo de parte de la clave, entonces esa parte de la clave y esos atributos formarán otra tabla. Ejemplo:

ALUMNOSDNI Cod Cur s o Nombre Apellido1 Nota12121219A 34 Pedro Valiente 912121219A 25 Pedro Valiente 83457775G 34 Ana Fernández 65674378J 25 Sara Crespo 75674378J 34 Sara Crespo 6

Suponiendo que el DNI y el número de curso formen una clave principal para esta tabla, sólo la nota tiene dependencia funcional completa. El nombre y los apellidos dependen de forma completa del DNI. La tabla no es 2FN, para arreglarlo:

ALUMNOSDNI Nombre Apellido112121219A Pedro Valiente3457775G Ana Fernández5674378J Sara Crespo

ASISTENCIADNI Cod Cur s o Nota12121219A 34 912121219A 25 83457775G 34 65674378J 25 75674378J 34 6

tercera forma normal (3FN)Ocurre cuando una tabla está en 2FN y además ningún atributo que no sea clave depende transitivamente de las claves de la tabla. Es decir no ocurre cuando algún atributo depende funcionalmente de atributos que no son clave.

<26


Ejemplo:

ALUMNOSDNI Nombre Apellido1 Cod Provincia Provincia12121349A Salvador Velasco 34 Palencia12121219A Pedro Valiente 34 Palencia3457775G Ana Fernández 47 Valladolid5674378J Sara Crespo 47 Valladolid3456858S Marina Serrat 08 Barcelona

La Provincia depende funcionalmente del código de provincia, lo que hace que no esté en3FN. El arreglo sería:

ALUMNOSDNI Nombre Apellido1 Cod Provincia12121349A Salvador Velasco 3412121219A Pedro Valiente 343457775G Ana Fernández 475674378J Sara Crespo 473456858S Marina Serrat 08

PROVINCIACod Provincia Provincia34 Palencia47 Valladolid08 Barcelona

forma normal de Boyce-Codd (FNBC o BCFN)Ocurre si una tabla está en tercera forma normal y además todo determinante es una clave candidata. Ejemplo:

TUTORÍASDNI Asignatura Tutor12121219A Lenguaje Eva12121219A Matemáticas Andrés3457775G Lenguaje Eva5674378J Matemáticas Guillermo5674378J Lenguaje Julia5634823H Matemáticas Guillermo

Esa tabla está en tercera forma normal (no hay dependencias transitivas), pero no en forma de Boyce - Codd, ya que (DNI, Asignatura) →Tutor y Tutor→Asignatura. En este caso la redundancia ocurre por mala selección de

<27

clave. La redundancia de la asignatura es completamente evitable. La solución sería:

<27


TUTORÍASDNI Tutor12121219A Eva12121219A Andrés3457775G Eva5674378J Guillermo5674378J Julia5634823H Guillermo

ASIGNATURASTUTORAsignatura TutorLenguaje EvaMatemáticas AndrésMatemáticas GuillermoLenguaje Julia

En las formas de Boyce-Codd hay que tener cuidado al descomponer ya que se podría perder información por una mala descomposición

dependencia multivaluadaPara el resto de formas normales (las diseñadas por Fagin, mucho más complejas), es importante definir este tipo de dependencia, que es distinta de las funcionales. Si las funcionales eran la base de la segunda y tercera forma normal (y de la de Boyce-Codd), éstas son la base de la cuarta forma normal.

Una dependencia multivaluada de una tabla con atributos X, Y, Z de X sobre Z (es decir X->>Z) ocurre cuando los posibles valores de Y sobre cualquier par de valores X y Z dependen sólo del valor de X y son independientes de Z.

Ejemplo:

Nº Curso Profe s or Material17 Eva 117 Eva 217 Julia 117 Julia 225 Eva 125 Eva 225 Eva 3

La tabla cursos, profesores y materiales del curso. La tabla está en FNBC ya que no hay dependencias transitivas y todos los atributos son clave sin dependencia funcional hacia ellos. Sin embargo hay redundancia. Los materiales se van a repetir para cualquier profesor dando cualquier curso, ya que los profesores van a utilizar todos los materiales del curso (de no ser así no habría ninguna redundancia).

<28


Los materiales del curso dependen del curso y no del profesor en una dependencia multivaluada. Para el par Nº de curso y profeso podemos saber los materiales, pero por el curso y no por el profesor.

cuarta forma normal (4FN)Ocurre esta forma normal cuando una tabla está en forma normal de Boyce Codd y toda dependencia multivaluada es una dependencia funcional. Para la tabla anterior la solución serían dos tablas:

Nº Curso Material17 117 225 125 225 3

Nº Curso Profe s or 17 Eva17 Julia25 Eva

Un teorema de Fagin indica cuando hay tres pares de conjuntos de atributos X, Y y Z si ocurre X->>Y|Z (Y y Z tienen dependencia multivaluada sobre X), entonces las tablas X,Y y X,Z reproducen sin perder información lo que poseía la tabla original. Este teorema marca la forma de dividir las tablas hacia una 4FN

quinta forma normal (5FN)Es la más compleja y polémica de todas. Polémica pues no está claro en muchas ocasiones que sea una solución mejor que el no llegar a este nivel de normalización. Fue definida también por Fagin.

Es raro encontrarse este tipo de problemas cuando la normalización llega a 4FN. Se deben a restricciones muy concretas. Ejemplo:

Prove e dor Material Proy e cto 1 1 21 2 12 1 11 1 1

Indican códigos de material suministrado por un proveedor y utilizado en un determinado proyecto.

Si ocurre una restricción especial como por ejemplo: Cuando un proveedor nos ha suministrado alguna vez un determinado material, si ese material aparece en otro proyecto, haremos que el proveedor nos suministre también ese material para ese proyecto.

<29>


Eso ocurre en los datos como el proveedor número 1 nos suministró el material número 1 para el proyecto 2 y en el proyecto 1 utilizamos el material 1, aparecerá la tupla proveedor 1, material 1 y proyecto 1.

La dependencia que produce esta restricción es lejana y se la llama de reunión. Para esa restricción esta división en tablas sería válida:

Prove e dor Material1 11 22 1

Material Proy e cto 1 22 11 1

Esa descomposición no pierde valores en este caso, sabiendo que si el proveedor nos suministra un material podremos relacionarle con todos los proyectos que utilizan ese material.

Resumiendo, una tabla no está en quinta forma normal si hay una descomposición de esa tabla que muestre la misma información que la original.

<31

apapéénnddiiccee:: ttéérrmmiinnooss ttééccnniiccooss

1FN Abreviatura de Primera Forma Normal. Normalización estándar de las tablas relacionales.

2FN Abreviatura de Segunda Forma Normal. Normalización estándar de las tablas relacionales.

3FN Abreviatura de Tercera Forma Normal. Normalización estándar de las tablas relacionales.

4FN Abreviatura de Cuarta Forma Normal. Normalización estándar de las tablas relacionales.

5FN Abreviatura de Quinta Forma Normal. Normalización estándar de las tablas relacionales.

ANSI American National Standards Institute, Instituto de estándares de Estados

Unidos. Uno de los organismos de estandarización más importantes.

ATU Área de trabajo de usuario. Parte de la memoria que utilizan los procesos de usuario para almacenar los datos recibidos de una base de datos.

BCNF Véase FNBC

BD Abreviatura de Base de Datos.

Buffer Zona de la memoria que se utiliza para almacenar temporalmente algunos datos.

Codasyl Conference on Data System Languages, Data Base Task Group. Nombre que se da al modelo de bases de datos en red que resultó de una conferencia en el año 1971 y que provocó su aceptación como estándar.

DB Abreviatura de Data Base, base de datos

DBA Data Base Administrator, nombre que recibe el administrador de la base de datos

DBMS Data Base Management System, Sistema gestor de bases de datos. El software encargado de administrar y producir bases de datos.

DCL Data Control Language, lenguaje de control de datos. Lenguaje que proporcionan las DBMS para controlar los usuarios de la base de datos.

DDL Data Definition Language, lenguaje de definición de datos. Lenguaje que proporcionan las DBMS para definir la base de datos.

DML Data Modification Language, lenguaje de modificación de datos. Lenguaje que proporcionan las DBMS para realizar operaciones de búsqueda y modificación de datos.

<32

ERE Modelo entidad relación extendido

FNBC Abreviatura de Forma Normal de Boyce Codd. Normalización estándar de las tablas relacionales.

Diseño conceptual de bases de datosapéndice: términos técnicosLOB Large Object Binary, objeto binario largo. Tipo de datos de muchas bases de datos que admiten

almacenar grandes cantidades de información en formato binario.

ODMG Object Data Management Group, grupo de administración de objetos de datos. Estándar utilizado para definir modelos lógicos de bases de datos de objetos.

OLAP On Line Analytical Process, Proceso analítico en línea. Nombre que reciben lasOOP Programación orientada a objetos

OS Véase SO

POO Programación orientada a objetos

QBE Query by Example, consultas mediante ejemplos. Lenguaje relacional utilizado en algunas de las primeras bases de datos relacionales.

RM/V2 Relational Model Version 2, Modelo relacional, versión 2. Modelo desarrollado por Codd, considerado como la segunda versión del modelo relacional.

RDBMS Relational Data Base Management System, Sistema gestor de bases de datos relacionales. El software encargado de administrar y producir bases de datos relacionales

SGBD Véase DBMS

SGBDR Véase RDBMS

SO Sistema operativo

SPARC System Planing and Repairments Comitte, comité de planificación de sistemas y reparaciones, subsección de ANSI.

UML Uniform Modeling Language, Lenguaje de modelado universal, utilizado para realizar modelos conceptuales de información orientada al objeto.

X3 Sección de ANSI encargada de los estándares de ordenadores y m

4.1 Peligros en el diseño de bases de datos relacionales

Recordemos que cada esquema de relación consiste en diversos atributos y el esquema de base de datos consta de algunos esquemas de relación.

Hasta aquí, hemos supuesto que los atributos se agrupan para formar un esquema de relación empleando el sentido común del diseñador de bases dedatos o estableciendo una transformación del modelo E-R a un esquema relacional.


En esta unidad se estudiará parte de la teoría que se ha desarrollado en un intento por elegir “buenos” esquemasde relación;

esto es, por medir formalmente las razones por las que una agrupación de

atributos en esquemas de relación es mejor que otra.

Hay dos niveles en los que podemos evaluar la“bondad” de los esquemas de relación:

Nivel lógico y Nivel de manipulación (o de almacenamiento)


El nivel lógico se refiere a la manera enque los usuarios interpretan los esquemas de relación y el significado de sus atributos.

Contar con buenos esquemas de relación en este nivel ayuda a los usuarios a comprender con claridad el significado de las tuplas de datos en las relaciones,y por tanto a

formular sus consultas correctamente.


El nivel de manipulación (o dealmacenamiento) se refiere a cómo se almacenan y actualizan las tuplas de unarelación base (relación base es la que se almacena físicamente como archivo).

En el nivel lógico nos interesan los esquemas tanto de las relaciones base como de las vistas (relaciones virtuales).


Existen cuatro medidas informales de la calidad para el diseño de esquemas de relaciones: Semántica de los atributos,

Reducción de los valores redundantes en las tuplas,

Reducción de los valores nulos en las tuplas,

Prohibición de tuplas espurias.


Semántica de los atributos.

El significado o semántica especifica cómo se han de interpretar los valores de

los atributos almacenados en una tupla de la relación; dicho de otro modo,

quérelación hay entre los valores de los atributos de una tupla.

Cuanto más fácil sea explicar la semántica de la relación mejor será el

diseño del esquema correspondiente.

4.1 Peligros en el diseño de bases de datos relacionales Semántica de los atributos.

Pauta 1: Se debe diseñar un esquema de relación de modo que sea fácil explicar su significado.

No combinar atributos de varios tipos de entidades y tipos de vínculos en una sola relación.

4.1 Peligros en el diseño de bases de datos relacionales Semántica de los atributos.

Pauta 1:

Intuitivamente, si un esquema de relación corresponde a un tipo de entidades o a un tipo de vínculos, el significado tiende a ser claro.

En caso contrario, tiende a ser una mezcla de múltiples entidades y vínculos y, por tanto, será semánticamente confuso.


Información redundante en las tuplas y anomalías de actualización

Uno de los objetivos en el diseño de esquemas es minimizar el espacio de almacenamiento que ocupan las relaciones base (archivos).

El problema generado por la información redundante son las anomalías de actualización (anomalías de inserción, anomalías de eliminación y anomalías de modificación).


Información redundante en las tuplas y anomalías de actualización

Pauta 2:

Se debe diseñar los esquemas de las relaciones base de modo que no haya anomalías de inserción, eliminación o modificación en las relaciones. Si hay anomalías, se deben señalar con claridad a fin de que los programas que actualicen la base de datos operen correctamente.


Valores nulos en las tuplas.

En algunos diseños de esquemas se agrupan muchos atributos para formar una relación “gruesa”, si muchos de los atributos no se aplican a todas las tuplas de la relación, existen un gran número de nulos en esas tuplas.

Lo anterior origina un considerable desperdicio de espacio en el nivel

de almacenamiento y dificulta elentendimiento del significado de los

atributos.



Otro problema con los nulos es cómo manejarlos cuando se aplican funciones agregadas como COUNT o SUM, además los nulos pueden tener diversas interpretaciones.



Veamos algunas interpretaciones de los nulos:

1. El atributo no se aplica a esta tupla.

2. Se desconoce el valor del atributo para esta tupla.

3. El valor se conoce pero está ausente, esto es, todavía no se ha registrado.



Pauta 3:

Hasta donde seaposible, se debe evitar incluir en una relación base atributos cuyos valores puedan sernulos. Si no

es posible evitar losnulos,debemos asegurarnos de que se

apliquen sólo en casos excepcionales y no a la mayoría de las tuplas de una relación.


Tuplas espurias.

Son tuplas que representan información errónea.

Pauta 4: Se debe diseñar los esquemas de relación de modo que puedan reunirse mediante condiciones de igualdad sobre atributos que sean claves primarias o claves externas, a fin de garantizar que no se formarán tuplas espurias.


Introducción a la normalización.

En el proceso de normalización, según la propuesta original de Codd (1972), se somete a un esquema de relación a una serie de pruebas para “certificar” si pertenece o no a una cierta forma normal.

En principio, Codd propuso tres formas normales, a las cuales llamó primera, segunda y tercera formas normales.



Posteriormente, Boyce y Codd propusieron una definición más estricta de 3FN, a la que se conoce como forma normal Boyce-Codd.

Todas estas formas se basan en las dependencias funcionales entre los atributos de una relación.



Más adelante se propusieron una cuarta forma normal (4FN) y una

quinta (5FN), con fundamento en los conceptos de dependencias multivaluadas

y dependencias de reunión respectivamente.



La normalización de los datos puede considerarse como un proceso durante elcual los esquemas de relación insatisfactorios se descomponen repartiendo sus atributos entre esquemas de relación más pequeños que poseen propiedades deseables.

El principal objetivo del proceso de normalización original es garantizar que no ocurran las anomalías de actualización.

4.1 Peligros en el diseño de bases de datos relacionales Introducción a la normalización.

Las formas normales proveen a los diseñadores de bases de datos lo siguiente:

1) Un marco formal para analizar los esquemas de relación con base en sus claves y en las dependencias funcionales entre sus atributos.

2) 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.


Cuando una prueba falla, la relación que provoca el fallo debe descomponerse en relaciones que individualmente satisfagan las pruebas de normalización.

En resumen, podemos concluir que la normalización de datos es un proceso de refinamiento de las estructuras de la base de datos para mejorar la velocidad a la que los datos puedan accederse, así como mejorar su integridad.

4.1 Peligros en el diseño de bases de datos relacionales Dependencias funcionales

El concepto más importante en el diseño de esquemas de relación es el de DEPENDENCIA FUNCIONAL.

Una df es una restricción entre dos conjuntos de atributos de la base de datos.


Supongamos el esquema de la relación

R={A1, A2, …, An}

Una dependencia funcional, denotada X→Y (X determina funcionalmente a Y o Y depende funcionalmente de X), entre dosconjuntos de atributos X e Y que son subconjuntos de R, especifica una restricción sobre las posibles tuplas que podrían formar una relación r de R.


si t1(X)=t2(X), se debe cumplir también que t1(Y)=t2(Y)

“X determina funcionalmente a Y en un esquema de relación R si y sólo si, siempre que dos tuplas de r(R) coincidan en su valor X, necesariamente deben coincidir en su valor Y”.


Observación: Si una restricción de R dice que no puede haber más de una tupla con un valor X dado en cualquier relación r(R), esto significa que X es una clave candidata de R.

Si X→Y se cumple en R, no implica que Y →Xse cumple en R también.

LAS DEPENDENCIAS FUNCIONALES SON PROPIEDADES DE LA SEMÁNTICA DE LOS ATRIBUTOS.


Siempre que la semántica de dos conjuntos de atributos de R, indique que debe cumplirse una dependencia funcional, especificamos a esa dependencia como una restricción.

La utilidad principal de las dependencias funcionales es describir mejor un esquema de relación R mediante la especificación de restricciones sobre sus atributos que deban cumplirse siempre.


Una dependencia funcional es una propiedad del esquema de relación R (intensión), y no de un ejemplar de relación permitido r de R (extensión) en particular. Por esto una dependencia funcional no puede inferirse

automáticamente de una extensión derelación r dada, sino que debe

definirla explícitamente alguien que conozca la semántica de los atributos de R.


Ejemplo,

EMP_DEPTO(NOMBREE, NSS, FECHAN, DIRECCION, NUMEROD, NOMBRED, NSSGTED)

F={ NSS → NOMBREE, FECHAN, DIRECCION

NUMEROD→ NOMBRED, NSSGTED}


Ejemplo,

EMP_PROY(NSS, NUMEROP, HORAS, NOMBREE, NOMBREP, LUGARP)

F={ NSS → NOMBREE

NSS, NUMEROP→ HORAS

NUMEROP →NOMBREP, LUGARP }

4.2 Primera y segunda forma normal Primera forma normal (1FN) Se define para prohibir los atributos

multivaluados, los atributos compuestos y sus combinaciones.

La 1FN establece que los dominios de los atributos deben incluir sólo valores atómicos, es decir, se prohíbe tener un conjunto de valores, como valor de un atributo.

4.2 Primera y segunda forma normal Segunda forma normal (2FN) Se basa en el concepto de dependencia

funcional total.

X→Y es una DEPENDENCIA FUNCIONAL TOTAL si la eliminación de cualquier atributo A de X hace que la dependencia deje de ser válida.

Es decir cualquier atributo A Є X,

(X-{A}) NO DETERMINA FUNCIONALMENTE A Y

4.2 Primera y segunda forma normal Segunda forma normal (2FN) X→Y es una DEPENDENCIA FUNCIONAL

PARCIAL si es posible eliminar un atributo A Є X de X y la dependencia

sigue siendo válida.

Es decir cualquier atributo A Є X,

(X-{A}) SI DETERMINA FUNCIONALMENTE A Y

4.2 Primera y segunda forma normal Segunda forma normal (2FN) Por ejemplo,

NSS,NUMEROP →HORAS

Es una dependencia total, porque no se cumple:

NSS →HORAS ni

NUMEROP →HORAS

4.2 Primera y segunda forma normal Segunda forma normal (2FN) Sin embargo, la dependencia

NSS, NUMEROP → NOMBREE

es una dependencia parcial, porque se cumple que:

NSS →NOMBREE

4.2 Primera y segunda forma normal Segunda forma normal (2FN)

Otro concepto a definir es el atributo primo.

Un atributo del esquema de relación R se denomina atributo primo de R si es miembro de cualquier clave de R.

Por ejemplo,

Para el esquema de relación

TRABAJA_EN (N S S, N UM E R OP , HORAS)


TRABAJA_EN (N S S, N UM E R OP , HORAS)

La clave es NSS, NUMEROP, por tanto

NSS es atributo primo y

NUMEROP es atributo primo, porque ambos son parte de la clave.

HORAS es atributo no primo, porque no participa en ninguna clave.


“Un esquema de relación R está en 2FN si todo atributo no primo A en R depende funcionalmente de manera total de la clave primaria de R”


Por ejemplo

EMP_PROY(N SS, N U M E R OP , HORAS, NOMBREE, NOMBREP, LUGARP)

F={ NSS, NUMEROP →HORAS

NSS →NOMBREE NO

NUMEROP →NOMBREP NO

NUMEROP →LUGARP NO}


Las dependencias marcadas con NO, dependen parcialmente de la clave primaria NSS, NUMEROP.

Entonces hay que descomponer el esquemaEMP_PROY en los siguientes esquemas:

4.2 Primera y segunda forma normal ESQUEMA ANTERIOR:

EMP_PROY(N SS, N U M E R OP , HORAS, NOMBREE, NOMBREP, LUGARP)

ESQUEMA EN 2FN:

EP1(N SS, N U M E R OP , HORAS)

EP2(N SS , NOMBREE)

EP3(N UM E R OP , NOMBREP, LUGARP)


Los correspondientes conjuntos de dependencias funcionales:

DFEP1={NSS,NUMEROP →HORAS}

DFEP2={NSS →NOMBREE}

DFEP3={NUMEROP →NOMBREP

NUMEROP →LUGARP}

Donde EP1, EP2 y EP3 se encuentran en 2FN

4.3 Tercera forma normal y la FN de Boyce-Codd Tercera forma normal

La tercera forma normal se basa en el concepto de dependencia transitiva.

Una dependencia funcional X →Y en un esquema de relación R es una dependencia transitiva si existe un conjunto de atributos Z que no sea un subconjunto de cualquier clave de R, y se cumplen tanto X →Z como Z →Y, y además Z →X y además Z no define funcionalmente a X.


Por ejemplo,

EMP_DEPTO(NOME, N SS , FECHAN, DIREC, NUMD, NOMD, NSSG)

F={NSS →NOME, NSS →FECHAN,

NSS →DIREC, NSS →NUMD,

NUMD →NOMD (NO), NUMD →NSSG (NO)}

NUMD no es un subconjunto de la clave


La dependencia NSS →NSSG es transitiva a través de NUMD, porque se cumplen las dos dependencias

NSS →NUMD y NUMD →NSSG

(Recordemos X →Z así como Z →Y)

y además NUMD no es un subconjunto de la clave.


“Un esquema de relación R está en 3FN, si está en 2FN y ningún atributo no primo de R depende transitivamente de la clave.”


Si tomamos el esquema EMP_DEPTO está en2FN, no existen dependencias parciales respecto a la clave primaria.

Pero no está en 3FN porque existen dependencias transitivas.


EMP_DEPTO(NOME, N SS , FECHAN, DIREC, NUMD, NOMD, NSSG)

Descomponemos el esquema EMP_DEPTOen dos esquemas que se encuentran en 3FN.

ED1(NOME, N SS , FECHAN, DIREC, NUMD)

ED2(N U M D , NOMD, NSSG)


Los correspondientes conjuntos de dependencias funcionales de ED1 y ED2.

DFED1= { NSS → NOME, NSS →FECHAN, NSS →DIREC, NSS, NUMD}

DFED2= {NUMD →NOMD, NUMD →NSSG}

4.3 Tercera forma normal y la FN de Boyce-Codd Forma normal de Boyce-Codd (FNBC)

Esta regla se deriva de 3FN, pero es más estricta.

“Un esquema de relación R está en FNBC, siempre que una dependencia funcional X→A es válida en R, entonces X es una superclave de R”.

La única diferencia entre FNBC y 3FN es que la condición de 3FN, que permite que:

A sea primo si X no es una superclave,

no es permitido en FNBC.


En la práctica, casi todos los esquemas de relación que están en 3FN también están en FNBC. Sólo si existe una dependencia X→A en un esquema de relación R, y X no es una superclave y A es un atributo primo, R estará en 3FN pero no en FNBC.


Basado en el esquema de lotes

LOTES(I D _ P R OPIED A D , NOMBRE_MUNIC, NUM_LOTE, AREA, PRECIO, TASA_FISCAL)

Supongamos que tenemos miles de lotes en la relación pero que dichos lotes pertenecen a sólo dos municipios. A y B. Supongamos también que los tamaños de los lotes en el municipio A son de sólo 0.5,0.6,0.7,0.8,0.9 y.10 hectáreas.

1

4.3 Tercera forma normal y la FN de Boyce-Codd

Forma normal de Boyce-Codd (FNBC)

y los lotes en el municipio B están restrina 1.1, 1.2, … 1.9 y 2.0 hectáreas.

En una situación así tendríamosdependencia funcAREA→NOMBRE_MUNIC.

La relación lotes sigue estando en 3FN pNOMBRE_MUNIC es un atributo primo.

2

CC42A/CC55A - BASES DE DATOS Profesor: Claudio Gutiérrez Auxiliar:

Mauricio Monsalve

Dependencias funcionales

1 El concepto de dependencia funcional

1.1 El concepto de dependencia funcionalHay veces en que los atributos están relacionados entre s´ı de manera más espec´ıfica que la de pertenecer a una misma relación. Hay veces en que es posible determinar que un atributo depende de otro f uncionalmente, como si existiera una función f en el ”mundo”, tal que t[A] = f (t[B]). La función se anotar´ıa como f : A → B, pero como f es desconocida (o sino B ser´ıa un atributo derivado), sólo nos quedamos con A → B, la dependencia funcional, que se lee ”A determina B”.

Formalmente, X → Y en R se cumple si y sólo si ∀s, t ∈ R, s[X ] = t[X ] ⇒ s[Y ] = t[Y ]. Esto es

análogo a las funciones: ∀x1, x2 ∈ X, x1 = x2 ⇒ f (x1) = f (x2), con f : X → Y .

1.2 Utilidad en el disenõ de bases de datosLas dependencias funcionales son restricciones de integridad sobre los datos. Conocer las dependencias funcionales en el momento del disenõ de la base de datos permite crear mecanismos para evitar la redundancia (y los potenciales problemas de integridad que eso conlleva) y mejorar la eficiencia.

1.3 Un ejemplo realPor ejemplo, sea la siguiente relación: Vehiculo(serie, nombre, motor, carrocer´ıa, peso, eficiencia). Aqu´ı, serie es la llave. Por ende, hay sólo un [modelo de] motor por serie, sólo una [forma de] carrocer´ıa por serie, sólo un peso por serie y sólo una eficiencia [energética] por serie: nombre = nombre(serie), motor = motor(serie), etcétera. O sea, serie → nombre, motor, carrocera, peso, ef iciencia (la relación es función de su llave; sólo hay una tupla por llave).

Otra observación, que requiere mucho más conocimiento del problema, nos indica que la eficiencia energética del veh´ıculo es una función del motor, la carrocer´ıa y el peso. Considerando esto, tenemos que motor, carrocera, peso → ef iciencia. ¿Por qué? La eficiencia energética consiste en la distancia que puede recorrer un veh´ıculo por litro, a una velocidad moderadamente alta. La potencia del veh´ıculo reside en el motor: el modelo de motor indica la fuerza que imprime el veh´ıculo. Sin embargo, esta fuerza es contrarrestada por el roce aerodinámico del veh´ıculo, que es una función del roce viscoso del aire (es un dato fijo) y de la forma

3

de la carrocer´ıa. Y el peso entrega la masa del veh´ıculo (masa = 9, 8m/s2 × peso). Si se divide la fuerza resultante del veh´ıculo por la masa, se obtiene la aceleración (y en un equilibrio de velocidades se obtiene la eficiencia). Luego, existe una función tal que ef iciencia = ef iciencia(motor, carrocera, peso). 1

1 Siendo más precisos, notemos que ef iciencia = g(motor, carrocera)/peso. Entonces, este atributo es mediana- mente derivado. Luego, es posible definir un atributo ef ic motor carro = g(motor, carrocera) a partir del cual “eficiencia” se vuelve un atributo derivado. Esta solución evita auń más redundancia, aunque es más idealizada.

4

1.4 Un ejemplo más sencilloA veces es fácil encontrar dependencias en un esquema. Esto es un indicado de un mal modelo entidad-relación o de una mala conversión a relacional.

Por ejemplo, sea Pel´ıcula(t´ıtulo, anõ, estudio, presidente, fono presidente). Digamos que ”t´ıtulo” es llave de la relación (determina todo). Sin embargo, notemos que el presidente de un estudio se puede determinar conociendo el estudio y el anõ (idealmente). Luego, estudio, anõ → presidente. Además, es claro que presidente → f ono presidente.

La relación ”Pel´ıcula” fue mal modelada desde un principio. En un modelo entidad-relación,

”Pel´ıcula”, ”Estudio” y ”Presidente” habr´ıan sido entidades distintas, luego relaciones distintas en el modelo relacional.

1.5 Un ejemplo visual¿Qué dependencias funcionales se cumplen en esta relación?

A B C D E F G H1 a eth cdr cdr 0 0x00 102 a eth car cdr 0 0xf0 103 b usb cdr car 0 0xff 104 b com car car 1 0x68 105 c lpt cddr car 1 0xa0 12

Algunas dependencias funcionales fáciles de ver:

• A determina toda la relación (A es llave).

• B determina H.

• C determina B.

• C determina F.

• D,E determina toda la relación (D,E es llave).

• G es llave.

• etcétera (hay varias más).

1.6 ¿Cómo obtener las dependencias funcionales?La mejor manera de obtenerlas es a través del conocimiento del problema. Es lo más disponible en la fase de disenõ de una base de datos. Sin embargo, esto puede tornarse bastante dif´ıcil, como en el caso del veh´ıculo (honestamente, esto puede ocurrir cuando la base de datos modela conocimiento técnico, que escapa al sentido comuń).

Otra manera, relacionada con el ejemplo anterior, es comprobar dependencias funcionales sobre una gran población de datos usando la definición.

5

2 Demostración de los axiomas de Armstrong

2.1 EjercicioLos axiomas de Armstrong son los siguientes:

1. (dependencia trivial) A → B ⊆ A.

2. (aumentación) A → B ⇒ (A ∪ C ) → (B ∪ C ). O más comodamente, A → B ⇒ AC → BC .

3. (transitividad) A → B ∧ B → C ⇒ A → C .

Demuestre los axiomas de Armstrong.

2.2 SoluciónEsto se hace mediante la definición de dependencia funcional.

1) (dependencia trivial)Sea A = A1, ..., An, y sea B ⊆ A tal que B = B1, ..., Bm. Luego, ∀i < m, ∃j < n, Bi = Aj

. Entonces, sean s, t ∈ R, tal que A ⊆ esq(R) (A es parte del esquema de R). Luego, si s[A] = t[A] ⇔∀j < n, s[Aj ] = t[Aj ] (por definición2). Esto implica, en particular, que s[Bi] = t[Bi], ∀i < m.

Entonces se concluye que A → B ⊆ A. O

2) (aumentación)Sea A → B, A, B ⊆ esq(R). Luego, ∀s, t ∈ R, s[A] = t[A] ⇒ s[B] = t[B]. Si exigimos

que s[C ] = t[C ], ∀s, t ∈ R, s[A] = t[A] ∧ s[C ] = t[C ] ⇒ s[B] = t[B] se sigue cumpliendo (un mero asunto de lógica proposicional). Y sin afectar los valores de verdad, se cumple que ∀s, t ∈ R, s[A] = t[A] ∧ s[C ] = t[C ] ⇒ s[B] = t[B] ∧ s[C ] = t[C ] (nuevamente, un mero asunto de lógica). Pero la u´ltima implicancia es lo mismo que AC → BC , con lo que se prueba el axioma. O

3) (transitividad)Sean A, B, C ⊆ esq(R) y las dependencias A → B y B → C . Por definición, se cumplen:A → B : ∀s, t ∈ R, s[A] = t[A] ⇒ s[B] = t[B]B → C : ∀s, t ∈ R, s[B] = t[B] ⇒ s[C ] = t[C ]Luego, usando la transitividad de las implicancias (⇒), se obtiene:∀s, t ∈ R, s[A] = t[A] ⇒ s[C ] = t[C ], o sea, A → C . O

3 Una manera más directa de proceder

3.1 Una pequenã propiedadSea A → BC y B → D. Entonces, A → ABC D.

Prueba:A → BC ⇒ A ∪ A → BC ∪ A ⇒ A → ABC (aumentación).B → D ⇒ B ∪ B → D ∪ B ⇒ B → BD (aumentación).B → BD ⇒ B ∪ AC → BD ∪ AC ⇒ ABC → ABC D (aumentación).A → ABC ∧ ABC → ABC D ⇒ A → ABC D (transitividad). O

6

2 Esto es igual a la comparaci´on de vectores: dos vectores son iguales si y s´olo si cada componente posee el mismo valor: X~ = Y~ ⇔ ∀j, Xj = Yj .

7

3.2 Ejemplo de uso de la regla anteriorSea R(A, B, C ) y F = {A → B, C → AB, B → BC }. Calcular F+.

(A modo de nota, F+ es la clausura de F. Es el conjunto de todas las dependencias funcionales que se pueden deducir de F.)

Solución:A+ : A → AB(A → B)A → AB → ABC (B → BC ) − llave −B+ : B → BC (B → BC − idem−)B → BC → ABC (C → AB) − llave −C + : C → ABC (C → AB) − llave −AB+ : AB → ABCAC + : AC → ABCBC + : BC → ABCABC + : ABC → ABC

Como se puede notar, resolver dependencias funcionales aumentando el lado derecho (el determinado) de las dependencias, asegura el avance de la resolución. En efecto, cada dependencia se puede usar una sola vez por la clausura de cada atributo. Además, la resolución del problema se simplifica bastante.

4 Ejercicios

4.1 ProblemaSea R(A, B, C, D, E) y DF = {A → BC, C D → E, B → D, E → A}. Determinar las llaves candidato (minimales) de R.

4.2 SoluciónCalcularemos las clausuras de todos los atributos (descartando cualquier superllave). Para dar legibilidad a la respuesta, enumero las dependencias funcionales de DF:

DF = {A → BC (1), C D → E(2), B → D(3), E → A(4)}

As´ı, cada vez que use una dependencia funcional, la mencionaré con un nu

´mero. A+: A → ABC (1) → ABC D(3) → ABC DE(2). Luego A es llave

candidato.B+: B → BD(3).C+: C → C .D+: D → D.E+: E → A(4) → ABC DE(A+). E es llave candidato.Para no considerar superllaves, no usaré A ni E. Sólo combinaré B, C y D. BC+: BC → BC D(3) → BC DE(2) → ABC DE(4). BC es llave candidato. BD+: BD → BD.CD+: C D → C DE(2) → ABC DE(E+). CD es llave candidato. No puedo considerar más combinaciones.Luego, A, E, BC y CD son llaves candidato.

8

4.3 ProblemaSea R(A, B, C, D, E, F ) y DF = {BD → E, C D → A, E → C, B → D}. ¿Cuáles son las llaves minimales?

4.4 SoluciónAqu´ı conviene hacer una observación: todo atributo que no es determinado por otro es parte de la llave minimal (¿por qué?). Observando el lado derecho de cada dependencia, vemos que ni B ni F son determinados por otros atributos (F ni siquiera es parte de alguna dependendencia funcional). Luego, empezar por BF+ puede ser una buena estrategia.

Otra vez voy a enumerar las dependencias, para facilitar la lectura:

DF = {BD → E(1), C D → A(2), E → C (3), B → D(4)}

BF+: BF → BDF (4) → BDEF (1) → BC DEF (3) → ABC DEF (4). Luego, BF es llave minimal.

Como B y F son atributos que deben ser parte de toda llave minimal, BF es la uńica llave minimal de R.

5 Formas normalesLa forma normal de una relación indica la calidad de su disenõ en cuanto a la redundancia de infor- mación evitada. Y su definición está basada en las dependencias, sean funcionales, multivaluadas, de join, etc. En el curso se trabajará sólo con dependencias funcionales (debido a una relación entre eficiencia y control de redundancia).

5.1 Primera forma normal: 1FNUna relación está en primera forma normal cuando todos sus atributos son atómicos. Como se indicó en clases, siempre estamos en 1FN (por lo menos).

5.2 Segunda forma normal: 2FNTodos los atributos no primos dependen de la totalidad de la llave (y no de un subconjunto de esta). Nota: por ”la llave” se entiende de ”cualquier llave minimal” o bien ”todas las llaves minimales”.

5.3 Tercera forma normal: 3FN (importante)Un modelo de datos relacional en tercera forma normal se considera de buena calidad. 3FN garantiza un buen equilibrio entre eficiencia y control de redundancia.

Un esquema R está en 3FN si ∀X → Y no trivial:- X es superllave (contiene a alguna llave candidato)- Y es atributo primo (es parte de alguna llave candidato)Es importante destacar es necesario conocer todas las llaves candidato o minimales.

9

5.4 Forma normal de Boyce-Codd: FNBC (importante)Una forma normal ideal, el máximo control de redundancia mediante dependencias funcionales. La idea consiste en que cada atributo depende de sólo de la llave (en su totalidad, no de una parte).

Un esquema R está en FNBC si ∀X → Y no trivial, X es superllave (contiene a alguna llavecandidato).

5.5 Cuarta forma normal: 4FNEs como FNBC, pero con dependencias multivaluadas.

Un esquema R está en 4FN si X · Y no trivial, X es superllave. Pero nótese que, como X es superllave, X → Y . Luego, 4FN exige que toda dependencia multivaluada sea en realidad una dependencia funcional.

5.6 Quinta forma normal: 5FNSe trata de producto-reunión (join). Una relación tal que R = A ∗ B ∗ C 3, todas relaciones con distintos esquemas, NO está en 5FN. Una relación está en 5FN cuando no se puede dividir y volver a reconstruir sin pérdida de información.

5.7 Lo que interesaInteresa mucho el dominio de la tercera forma normal y la forma normal de Boyce-Codd. En particular, la 3FN es la forma normal más alta que asegura preservación de la información (preservación de join) y de las dependencias. Pero si se puede llegar a FNBC, mejor todav´ıa.

Ejercicio propuesto: Demuestre que: 4F N ⇒ F N BC ⇒ 3F N ⇒ 2F N (alguna versión de

esta pregunta ha aparecido en controles).

6 NormalizaciónEl proceso de normalización es un proceso de descomposición de los esquemas de relación hasta que todas las relaciones alcancen la forma normal deseada. En general, interesa:

1. Determinar las llaves candidato (minimales) de cada relación.

2. Determinar la forma normal de la relación.

3. ¿Está en FNBC? Sino, normalizar.

4. ¿La normalización no asegura la preservación de información y dependencias? Conformarse con 3FN.

Ahora, un par de algoritmos para normalizar. Estos algoritmos aseguran la preservación de la información pero no necesariamente la preservación de las dependencias (ello no siempre se puede lograr con FNBC).

10

3 El join natural se anota como * y como aa.

11

6.1 Algoritmo para normalizar en FNBCDe manera informal, el algoritmo es el siguiente:

1. (Calcular F+)

2. Sea un esquema Ri que no está en FNBC: determinar X → Y que hace que Ri no esté en

FNBC y agregar a la descomposición (Ri − Y ) ∪ (X Y ) y remover Ri.

3. Terminar cuando todos los esquemas estén en FNBC.

En resumen, por cada dependencia funcional conflictiva con FNBC, sacar el lado derecho de la relación con problemas y agregar una nueva relación que guarde la dependencia. Esta estrategia de normalización no asegura preservar dependencias, pero s´ı asegura la recuperación de la información por join.

6.2 EjemploSea R(A, B, C, D, E) y F = {A → BC (1), C → D(2), B → E(3)}.

1. R no está en FNBC. Basta ver la dependencia (2); claramente C no es llave de R. Entonces partimos R en R1(A,B,C,E) y R2(C,D). No se ha roto ninguna dependencia.

2. R1 no está en FNBC. Basta ver (3); claramente B no es llave de R. Entonces partimos R1 en

R3(A,B,C) y R4(B,E). No se ha roto ninguna dependencia.

3. R2, R3 y R4 están en FNBC, por lo que el algoritmo concluye.

6.3 FNBC infactible (ejercicio)Uno de los objetivos de disenõ es preservar la información (recuperación mediante join) y las dependencias.

Sea R(A, B, C ) y F = {AB toC, C → B}. ¿En qué forma normal se encuentra? Trate de

normalizar a FNBC. ¿Es posible lograr una descomposición de R que esté en FNBC y preserve las dependencias funcionales?

6.4 Algoritmo para normalizar en 3FN1. Calcular F m´ınimo (conjunto covertor m´ınimo, conjunto canónico, equivalente minimal,

re- cubrimiento minimal, etc.).

2. Convertir cada dependencia en una relación (X → Y ⇒ Rk (X Y )).

3. Si no está la llave en una relación, agregarla.

La dificultad estriba en obtener el conjunto convertor m´ınimo de dependencias funcionales. De todas maneras, un conjunto es m´ınimo cuando:

1. X → Y , Y es atributo atómico.

12

2. Remover X → Y altera F+.

3. Si X → Y , $W ⊂ X : W → Y .

Análisis y Diseño de Bases de Datos M.Sc. Ing. Hernando Buenaño

Un Enfoque Práctico para Aprendizaje en el Aula 75

6.5 EjemploSea R(A, B, C, D, E) y F = {A → B(1), A → C (2), C → D(3), B → E(4)}. F es m´ınimo.

1. Al notar A+ vemos que: A → AB(1) → ABC (2) → ABC D(3) → ABC DE(4). Y como A

no es determinada por ninguna dependencia funcional, A es la uńica llave.

2. Tomando las cuatro dependencias, tenemos: R1(A, B), R2(A, C ), R3(C, D), R4(B, E). Como

R1 y R2 preservan la llave, la normalización ha terminado.

OJO: Sean S y R dos relaciones en 3FN con la misma llave (y todas las dependencias están preservadas). ¿Qué ocurre con S ∪ R? (Hable de llaves candidato, forma normal, etc.)

7 Y el i



nformeLo primero es convertir el diagrama E/R en relacional. Este es un paso bastante trivial pero puede tomar una cantidad importante de tiempo. Son cruciales las decisiones sobre entidades débiles y herencia.

Luego viene lo complicado: las dependencias funcionales. Es esencial una buena revisión de dependencias funcionales. Este es un problema no trivial: sale de su conocimiento del problema. En particular, la observación deber´ıa ir sobre las relaciones n-arias, con n > 2, sobre los atributos multivaluados compuestos y sobre aquellos atributos que ”casi” son derivados. Otra fuente de dependencias funcionales reside en las cardinalidades del diagrama E/R (ojo con las (1,1) ó (0,1)). Las relaciones con muchos atributos también pueden albergar bastantes dependencias funcionales (de entre los trabajos que correg´ı, hay varios de estos casos). Y obviamente están las relaciones dela forma llave → relación y las triviales4.

Luego de obtenidas las dependencias funcionales, es menester recalcular las llaves. Conocer todas las llaves candidato es una obligación para determinar la forma normal de sus relaciones. Por supuesto, las llaves candidato son minimales.

El siguiente paso es obtener la forma normal de todas las relaciones del modelo. Especialmente cr´ıticas son las relaciones con muchos atributos, puesto que pueden albergar muchas dependencias funcionales. Rev´ısese muy bien para buscar relaciones que no cumplan con FNBC. Además es importante saber si ya cumplen 3FN.

Toda relación en FNBC deberá ser normalizada, en lo posible, para obtener una descomposición



FNBC preservando la información y las dependencias. Si esto no es posible, conformarse con lograr3FN. Naturalmente, todo esto deberá estar muy bien explicado.

Y no se conf´ıen. Recuerdo haber visto entidades laaaargas que pueden tener bastantes dependencias funcionales, relaciones ternarias y cuaternarias que ciertamente albergaban dependencias funcionales (y que llevar´ıan a reducir la llave), y muchas restricciones relacionadas con las cardinalidades. Si bien no recuerdo haber visto modelos sobre conocimiento técnico, auń es posible descubrir dependencias sobre temas más cercanos a la cultura general.

4 Omita las dependencias triviales



NORMALIZACION

Definición.

La normalización es una técnica que se utiliza para crear relaciones lógicas apropiadas entre tablas de una base de datos. La normalización se adoptó porque el viejo estilo de poner todos los datos en un solo lugar, como un archivo o una tabla de la base de datos, era ineficiente y conducía a errores de lógica cuando se trataba de manipular los datos.

El proceso de normalización parte de las formas normales definidas por Edgar Frank Codd (1970) creador de las bases de datos relacionales. Primeramente, Codd formuló las tres primeras formas normales (1FN,



2FN, 3FN); posteriormente, unas anomalías detectadas forzaron a crear una forma normal más completa que la 3FN, es la FNBC (forma normal de Boyce y Codd), después Fagin definió la 4FN y 5FN.

La normalización es el proceso mediante el cual se transforman datos complejos a un conjunto de estructuras de datos más pequeñas, que además de ser más simples y más estables, son más fáciles de mantener.

Los seres humanos tenemos la tendencia de simplificar las cosas al máximo. Lo hacemos con casi todo, desde los animales hasta con los automóviles. Vemos una imagen de gran tamaño y la hacemos más simple agrupando cosas similares juntas. Las guías que la normalización provee crean el marco de referencia para simplificar una estructura de datos compleja.

La normalización se utiliza para mejor el esquema lógico, de modo que satisfaga ciertas restricciones que eviten la duplicidad de datos.



Objetivos de la Normalización

Minimizar la redundanciaMinimizar el mantenimiento de datosMinimizar el impacto de futuros cambios (anomalías de actualización y anomalías de borrado) de de datos, e ingreso de información (anomalías de inserción).

Ventajas de la Normalización

Evita anomalías en inserciones, modificaciones y borrados. Mejora la independencia de datos.No establece restricciones artificiales en la estructura de los datos. Facilidad de uso.Flexibilidad. Precisión. Seguridad.Facilidad de implementación. Independencia de datos. Claridad.Facilidad de gestión. Mínima redundancia.Máximo rendimiento de las aplicaciones.

Existen 5 Formas Normales:

Primera Forma Normal (1FN) Segunda Forma Normal (2FN) Tercera Forma Normal (3FN)Forma Normal de Boyce Codd(FNBC)Cuarta Forma Normal (1FN) Quinta Forma Normal (1FN)

Cada una de estas formas tiene sus propias reglas.



En general, las primeras tres formas normales son suficientes para cubrir las necesidades de la mayoría de las bases de datos.

Antes de iniciar con la teoría referente a normalización es necesario conocer los conceptos de Dependencias Funcionales, los mismos que se utilizarán en la teoría de Normalización.

Dependencias Funcionales

Codd introdujo el concepto de dependencia funcional como:

"Dados dos atributos A y B de una relación R, se dice que B es funcionalmente dependiente de A, si para cada valor de A existe un valor de B, y sólo uno, asociado con él”.

En otros términos, se puede decir que si dos tuplas de una relación R tienen el mismo valor en el atributo A deben tener el mismo valor en el atributo B. O dicho de otro modo, si conocemos el valor de A podemos conocer el valor de B.

Definición. Un atributo B de un esquema de relación R depende funcionalmente de un atributo A de R, si y sólo si, cada valor de A está asociado con un único valor de B. Es decir, dado un valor de A queda unívocamente determinado el valor de B. Se dice que B depende funcionalmente de A, y que A determina funcionalmente a B. Tanto A como B pueden ser atributos simples o compuestos

Suponga que tiene R = {A1, A2, A3, … , An} , R es una relación y A es un conjunto de atributos. Sea X, Y subconjuntos de A.

Notación DF: X € YSe lee: X determina o implica Y

Y depende funcionalmente de XSi y sólo si cada valor de X tiene asociado en todo momento un

único valor de Y



Donde: X es el determinanteY es el implicado

X e Y se les dice Descriptores.Un determinante es un conjunto del que depende funcionalmente

otro conjunto de atributos llamado implicado.Ejemplo:

CI_estudiante € Nombre_estudianteLas CI del estudiante determina el nombre del estudiante.

Dos descriptores son equivalentes cuando X € Y ٨ Y € X. Ejemplo:

CI_ estudiante € Num_Carnet_estudiante yNum_Carnet _estudiante € CI_estudiante

Notación:

X € Y ó bien, X1, … , Xn € Y1,…, Ym n,m >= 1

Axiomas de Amstrong

Conjunto de dependencias, que permite encontrar otras dependencias implicadas por ellas, las cuales son consistentes y completas.Los axiomas de Armstrong son consistentes y completosLos Axiomas de Armstrong son más bien reglas de inferencia.Estas reglas permiten deducir todas las dependencias funcionales que tienen lugar a un conjunto dado de atributos que se dan a partir del conocimiento del problema.

Reflexividad

Los valores de X y Y que son un conjunto de atributos Y están incluidos o son iguales a los conjuntos de atributos X, entonces se cumplen que Y depende funcionalmente de X.



Es decir que matemáticamente es: (Y S X) S(X S Y)

Por ejemplo(Cod_Pais SCod_Cap) S( Cod_Cap SCod_Pais)

Siendo Cod_Pais el código del Pais y Cod_Cap el código de la Capital.

Aumentatividad

Si el conjunto de atributos Y depende funcionalmente de X, entonces dicha dependencia se mantiene aunque se añada un atributo a ambos conjuntos. Es decir, si:

(X S Y) S (X.Z S Y.Z) Por ejemplo si:

ci Snombreci,dirección S nombre,dirección

Si con ci se determina el nombre de una persona, entonces con ci más la dirección también se determina el nombre o su dirección.

Transitividad

Si Y depende funcionalmente de X y Z depende funcionalmente de X, entonces se verifica que Z depende funcionalmente de X. por lo tanto, si:

(X S Y) y (YS Z) S (X SZ) Por ejemplo:FechaDeNacimiento SEdadEdad SPuede_ConducirFechaDeNacimiento SEdad SPuede_Conducir

Entonces tenemos que FechaDeNacimiento determina a Edad y la Edad determina a Conducir, indirectamente. Por tanto podemos saber a través de FechaDeNacimiento si Puede_Conducir.



Aditividad

Si Y depende funcionalmente de X y también se cumple que Z depende funcionalmente de X, eso implica que Y y Z dependen funcionalmente de X. por tanto:(X SY) y( X S Z) entonces (X SY

Z) PseudoTransitividad

Si se cumple que:(X → Y) y (WY → Z) entonces (WX → Z)La demostración de esta regla se la obtiene aplicando:(X → Y), por aumentación (WX → WY) (WY → Z) y ahora tras

aplicar la transitividad (WY → Z)

Por ejemplo:

(Nombre S CI) y (CI.Nom_Empresa S Sueldo)S(Nombre.Nom_Empresa S Sueldo)

Descomposición

Si el conjunto de atributos Y depende funcionalmente de X y también se cumple que los valores del conjunto de atributos Z están incluidos en los valores de Y, entonces se tiene que cumplir que Z depende funcionalmente de X. por lo tanto:

(X → Y) y ( Z ⊆ Y) entonces (X → Z).Se puede demostrar aplicando:(X → Y)( Z ⊆ Y), por reflexividad (Y →Z)

Este conjunto de reglas nos permite abordar y resolver una serie deproblemas fundamentales que luego nos conducirán al establecimiento de algoritmos de diseño que sean sencillos y fiables.



Estos problemas fundamentales, son :• Cierre de un conjunto de dependencias.• Equivalencia lógica de esquemas .• Deducción de dependencias .• Cierre de un descriptor respecto de un conjunto de dependencias.• Cálculo de las claves de un esquema

Tipos de Dependencias Funcionales

Dependencia Funcional Completa

Un atributo B de R tiene dependencia funcional completa de un atributo A de una relación R, si tiene dependencia funcional de A pero no tiene dependencia funcional de ningún subconjunto de A.

Formato:ASB

“A determina funcionalmente a B”

Ejemplo

CI.Empleado € Sueldo

La cedula del empleado determina el sueldo.El sueldo no depende de ningún subconjunto de la cédula que es la clave primaria.

Dependencia Funcional Multivaluada

“Sea A,B,C tres subconjuntos distintos de atributos de una tabla T, se dice que A tiene una dependencia M u l t i v a l u a d a con B, que A multidetermina a B, o que B depende multivaluadamente de A.”. Existen casos de relaciones en los que un atributo puede determinar a otro restringiendo su rango de



valores validos. A este tipo de dependencias se les conoce como dependencias multivaluadas.

Formato:A→→B

“A multidetermina a B“

Dependencia Funcional Transitiva

Sean A, B y C atributos de un esquema de relación R; si C tiene dependencia funcional de B y B tiene dependencia funcional de A, entonces C tiene dependencia funcional transitiva de A.Formato:

A→B ,B→C“A determina B y si B determina C entonces A determina a C“

Ejemplo:La tablaCiudades(ciudad, población, superficie, renta, país, continente)

ciudad → país, país → continente.

ciudad →continente.

Además, país →|ciudad. Es decir, cada ciudad pertenece a un país y cada país a un continente, pero en cada país puede haber muchas ciudades. En este caso continente tiene una dependencia funcional transitiva con respecto a ciudad, a través de país. Es decir, cada ciudad está en un país, pero también en un continente.



Diagrama de Dependencias Funcionales

Los diagramas de dependencia son gráficos que representan el contexto semántico observado en un determinado universo, donde los nodos son atributos y los arcos representan dependencias entre nodos. Normalmente se representan dependencias que van de un nodo o a un solo atributo.

Es la forma grafica de representar las dependencias funcionales, así:

AA B

FFecechha_a_NNacacimiimieennttoo Edad

Comprender la Dependencia Funcional es parte importante para entender la semántica de los datos.

EjemploPara la entidad Proveedor conformada por:Proveedor (Numero_Prov, Nombre_Prov, Tipo_Prov, Ciudad).



Diagrama de Dependencia Funcional Completa

Dado los atributos A, B y C de una relación R. Si, C depende de ambos atributos A y B y lo denotamos así: [A, B] --> --> C, y si además: C, no depende de A ni tampoco depende de B exclusivamente.C, tiene una dependencia completa de [A, B] ylo denotamos:[A, B] --> --> C (también se dice: C tiene dependencia total de [A, B].

NOTA: Si existiera una dependencia funcional completa en una relación R, todos los demás atributos de R que no son llave primaria, deberán tener la misma dependencia completa de los mismos atributos, de lo contrario se presentarían ineficiencias y anomalías en R, así

DD BCC

En el ejemplo B, tiene una dependencia completa de [C,D]->B

Se dice: B tiene dependencia total de [C,D]

Segundo Ejemplo.



En el ejemplo Fecha de Terminación del Proyecto(Fech-Term), Tienet una Dependencia Funcional Completa de la Clave(Num_Emp + Num_Proy), y no depende de un subconjunto de ella.

Diagrama de Dependencia Funcional Parcial

Un atributo B de R tiene dependencia funcional parcial de un atributo C de R, si tiene dependencia funcional de C y además tiene dependencia funcional de un subconjunto propio A de C.

Diagrama de Dependencia Funcional Transitiva

Sean A, B y C atributos de un esquema de relación R; si C tiene dependencia funcional de B y B tiene dependencia funcional de A, entonces C tiene dependencia funcional transitiva de A.

NOTA: La dependencia transitiva no es buena en una relación o tabla de base de datos, porque evidencia la existencia de atributos que no dependen únicamente de la llave primaria sino de otros atributos, ocasionando lo que se llama: anomalía en los procesos de actualización, inserción o eliminación. Una vez definidos claramente los conceptos de Dependencia Funcional, se puede analizar la Normalización.



NORMALIZACION

Formas Normales

Primera Forma Normal (1FN)

La regla de la Primera Forma Normal establece que las columnas repetidas deben eliminarse y colocarse en tablas separadas.

Poner la base de datos en la Primera Forma Normal resuelve el problema de los encabezados de columna múltiples. Muy a menudo, los diseñadores de bases de datos inexpertos harán algo similar a la tabla no normalizada. Una y otra vez, crearán columnas que representen los mismos datos. La normalización ayuda a clarificar la base de datos y a organizarla en partes más pequeñas y más fáciles de entender. En lugar de tener que entender una tabla gigantesca y monolítica que tiene muchos diferentes aspectos, sólo tenemos que entender los objetos pequeños y más tangibles, así como las relaciones que guardan con otros objetos también pequeños.

Si una relación no está en 1FN, hay que eliminar de ella los grupos repetitivos. Un grupo repetitivo será el atributo o grupo de atributos que tiene múltiples valores para cada tupla de la relación.

Por Ejemplo: En la siguiente tabla se encuentran los datos del alquiler de diferentes películas. Esta tabla se encuentra en Forma Normal 0. (Totalmente Desnormalizada).



Para que esta tabla pase a 1FN se debe eliminar los grupos repetitivos de las tablas individuales. Es decir la columna que contiene múltiples valores, que es Nom_Cli contiene el nombre y el apellido del cliente, esta columna se debe dividir en columnas individuales que guarden valores indivisibles, como Nom_Cli, y Ape_Cli. Se puede hacer lo mismo con la columna Nom_Ren.

La tabla en 1FN será:

Segunda Forma Normal (2FN)

Una tabla se dice que está en 2FN si y sólo si cumple dos condiciones:1) Se encuentra en 1 FN



2) Todo atributo secundario (aquéllos que no pertenecen a la clave principal, los que se encuentran fuera de la caja) depende totalmente (tiene una dependencia funcional total) de la clave completa y, por tanto, no de una parte de ella.

Esta forma normal sólo se considera si la clave principal es compuesta y, por tanto, está formada por varios atributos. Si la clave principal de la tabla está formada por un único atributo, entonces la tabla ya se encuentra en 2FN.Si una tabla T tiene como atributos A, B, C, D y la clave es A.Bcumpliéndose las dependencias: A.B ——> CB ——> DSe observa que la tabla no se encuentra en 2FN puesto que el atributo D no tiene una dependencia funcional total con la clave entera A.B, sino con una parte de la clave (B).Para convertir una tabla que no está en segunda forma normal a 2FN, serealiza una proyección y se crea:

1) Una tabla con la clave y todas sus dependencias totales con los atributos secundarios afectados

2) Otra tabla con la parte de la clave que tiene dependencias, junto con los atributos secundarios implicados

La clave de la nueva tabla será la antigua parte de la clave.En nuestro ejemplo, tendremos que se va a crear una tabla con los datos de las Películas:

PELICULAS



Otra tabla con los datos de los préstamos de esas películas.

Tercera Forma Normal (3FN)

Una tabla se dice que está en tercera forma normal si y sólo si se cumplen dos condiciones:

1) Se encuentra en 2FN2) No existen atributos no primarios que son transitivamente

dependientes de cada posible clave de la tabla.Esto quiere decir que un atributo secundario sólo se debe conocer a través de la clave principal o claves secundarias de la tabla y no por medio de otro atributo no primario.Por tanto, para pasar una tabla que no cumple la tercera forma normal a3FN, se realiza una proyección y se genera:

1) Una tabla con la clave y todos los atributos no primarios que no son transitivos

2) Otra tabla con los atributos transitivos y el atributo no primario (que será la clave de la nueva tabla) por medio del cual mantienen la transitividad

Lógicamente, en la primera tabla TI, el atributo C es clave ajena con respecto de T2 y de ese modo todos los atributos quedan relacionados entre sí. Es lo que se denomina interrelación entre la tabla TI y T2.

En nuestro ejemplo tendremos que se van a generar las siguientes tablas:



Tabla Clientes para contener los datos de la persona que rento la película agregando en su cedula de identidad como clave.Clientes

Tabla Rentadores para contener los datos de la persona que renta la película agregando un su cedula de identidad como clave.

Rentadores

Tabla Películas con los datos de las películas, con el código de la película como clave.

Películas



Tabla Prestamos con el código de la película, cedula del cliente y la cedula de la persona que rento, la fecha devolución y fecha de rento

Prestamos

Hasta esta 3FN se considera un esquema de base de datos normalizado.

Forma Normal de Boyce Codd (FNBC)

Tras la creación de la 3FN se observó posteriormente que se encontraban algunas anomalías que no eran abordadas. Son casos de tablas que estando en 3FN mantienen una dependencia de un atributo secundario con parte de la clave. Para poder manejar esa dependencia en las aplicaciones es imprescindible manejar una gran cantidad de registros innecesarios (aquéllos donde se mantiene fija la parte de la clave que depende y va variando el resto de la clave)

La nueva definición se debe a Boyce y Codd:"Una tabla T está en FNBC si y sólo si las únicas DF elementales son aquellas en las que la clave principal (y claves secundarias) determinan un atributo".La definición engloba la 3FN puesto que las dependencias transitivas existen por medio de atributos secundarios que no eran clave.Pero esta definición realmente se creó para evitar los casos anómalos que no se evitaban con la 3FN y que aparecen cuando a partir de un atributo no primario se conoce una parte de la clave.Si la clave está formada por un sólo atributo, la tabla está en FNBC (si ya estaba en 3FN) como sucedía con la 2FN.

Normalización de bases de datos

www.mysql-hispano.org Fecha de creación: 29 May del 2003 - 12:31 pm

Por lo tanto, para que una tabla que está en 3FN y no cumple la norma de Boyce-Codd se encuentre en FNBC se realiza una proyección procediendo de la siguiente manera:

1. Se crea una tabla con la parte de la clave que es independiente (A)y todos los atributos no primarios (C)

2. Se crea otra tabla con la parte de la clave restante y el atributo secundario del que depende, y será éste último la clave de la nueva tabla

Cuarta Forma Normal (4FN)

La 4FN la generó Fagin tras el teorema que demostró y que dice:"Una tabla T con atributos A, B y C se puede descomponer sin pérdidas en sus dos proyecciones T1(A,B) y T2(A,C) si y solo si la Dependencia Multivaluada A € B | C se cumple en T"De ese modo, se define la 4FN de la siguiente forma:Una tabla T se dice que está en 4FN si cumple dos condiciones:

1) Está en FNBC.2) Las únicas Dependencias Multivaluadas existentes son las Dependencias Funcionales de la clave con

los atributos secundarios.

Quinta Forma Normal (5FN)

Para que una tabla se encuentra en 5FN se deben cumplir dos condiciones:1) Se encuentra en 4FN2) Toda Dependencia de Join viene implicada por las claves (principal o secundarias) de la tabla.

Es decir, que la tabla estará en 5FN si es posible generar unas proyecciones y al realizar su join, los atributos comunes que realizan la operación (atributos de join) están formados por claves (principal o secundarias) de la tabla.


Se explican los conceptos de la normalización de bases de datos, mismos que son necesarios para un buen diseño de una base de datos.


www.mysql-hispano.org Fecha de creación: 29 May del 2003 - 12:31 pm

www.mysql-hispano.org 1 of 5


Qué es la normalización

La normalización es el proceso mediante el cual se transforman datos complejos a un conjunto de estructuras de datos más pequeñas, que además de ser más simples y más estables, son másfáciles de mantener. También se puede entender la normalización como una serie de reglas que sirven para ayudar a los diseñadores de bases de datos a desarrollar un esquema que minimice los problemas de lógica. Cada regla está basada en la que le antecede. La normalización se adoptó porque el viejo estilo de poner todos los datos en un solo lugar, como un archivo o una tabla de la base de datos, era ineficiente y conducía a errores de lógica cuando se trataban de manipular los datos.

La normalización también hace las cosas fáciles de entender. Los seres humanos tenemos la tendencia de simplificar las cosas al máximo. Lo hacemos con casi todo, desde los animales hasta con los automóviles. Vemos una imagen de gran tamaño y la hacemos más simple agrupando cosas similares juntas. Las guías que la normalización provee crean el marco de referencia para simplificar una estructura de datos compleja.

Otra ventaja de la normalización de base de datos es el consumo de espacio. Una base de datos normalizada ocupa menos espacio en disco que una no normalizada. Hay menos repetición de datos, lo que tiene como consecuencia un mucho menor uso de espacio en disco.

El proceso de normalización tiene un nombre y una serie de reglas para cada fase. Esto puede parecer un poco confuso al principio, pero poco a poco se va entendiendo el proceso, así como las razones para hacerlo de esta manera.

Grados de normalización

Existen básicamente tres niveles de normalización: Primera Forma Normal (1NF), Segunda Forma Normal (2NF) y Tercera Forma Normal (3NF). Cada una de estas formas tiene sus propias reglas. Cuando una base de datos se conforma a un nivel, se considera normalizada a esa forma de normalización. No siempre es una buena idea tener una base de datos conformada en el nivel más alto de normalización, puede llevar a un nivel de complejidad que pudiera ser evitado si estuviera en un nivel más bajo de normalización.

En la tabla siguiente se describe brevemente en que consiste cada una de las reglas, y posteriormente se explican con más detalle.

Regla DescripciónPrimera Forma Normal (1FN) Incluye la eliminación de todos los grupos repetidos.Segunda Forma Normal (2FN) Asegura que todas las columnas que no son llave sean

completamente dependientes de la llave primaria (PK).Tercera Forma Normal (3FN) Elimina cualquier dependencia transitiva. Una dependencia

transitiva es aquella en la cual las columnas que no son llave son dependientes de otras columnas que tampoco son llave.

Primera Forma Normal




Poner la base de datos en la Primera Forma Normal resuelve el problema de los encabezados de columna múltiples. Muy a menudo, los diseñadores de bases de datos inexpertos harán algo similar ala tabla no normalizada. Una y otra vez, crearán columnas que representen los mismos datos. La normalización ayuda a clarificar la base de datos y a organizarla en partes más pequeñas y más fáciles de entender. En lugar de tener que entender una tabla gigantesca y monolítica que tiene muchos diferentes aspectos, sólo tenemos que entender los objetos pequeños y más tangibles, así como las relaciones que guardan con otros objetos también pequeños.

Segunda Forma Normal

La regla de la Segunda Forma Normal establece que todas las dependencias parciales se deben eliminar y separar dentro de sus propias tablas. Una dependencia parcial es un término que describe a aquellos datos que no dependen de la llave primaria de la tabla para identificarlos.

Una vez alcanzado el nivel de la Segunda Forma Normal, se controlan la mayoría de los problemas de lógica. Podemos insertar un registro sin un exceso de datos en la mayoría de las tablas.

Tercera Forma Normal

Una tabla está normalizada en esta forma si todas las columnas que no son llave son funcionalmente dependientes por completo de la llave primaria y no hay dependencias transitivas. Comentamos anteriormente que una dependencia transitiva es aquella en la cual existen columnas que no son llave que dependen de otras columnas que tampoco son llave.

Cuando las tablas están en la Tercera Forma Normal se previenen errores de lógica cuando se insertan o borran registros. Cada columna en una tabla está identificada de manera única por la llave primaria,y no deben haber datos repetidos. Esto provee un esquema limpio y elegante, que es fácil de trabajar y expandir.

Un dato sin normalizar no cumple con ninguna regla de normalización. Para explicar con un ejemplo en que consiste cada una de las reglas, vamos a considerar los datos de la siguiente tabla.

ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO NUM_ITEM DESC_ITEM CANT PRECIO2301 2/23/03 101 MARTI CA 3786 RED 3 352301 2/23/03 101 MARTI CA 4011 RAQUETA 6 652301 2/23/03 101 MARTI CA 9132 PAQ-3 8 4.752302 2/25/03 107 HERMAN WI 5794 PAQ-6 4 5.02303 2/27/03 110 WE-SPORTS MI 4011 RAQUETA 2 652303 2/27/03 110 WE-SPORTS MI 3141 FUNDA 2 10

Al examinar estos registros, podemos darnos cuenta que contienen un grupo repetido para NUM_ITEM, DESC_ITEM, CANT y PRECIO. La 1FN prohibe los grupos repetidos, por lo tanto tenemos que convertir a la primera forma normal. Los pasos a seguir son:

» Tenemos que eliminar los grupos repetidos.» Tenemos que crear una nueva tabla con la PK de la tabla base y el grupo repetido.



Los registros quedan ahora conformados en dos tablas que llamaemos ORDENES yARTICULOS_ORDENES

- ORDENES

ID_ORDEN FECHA ID_CLIENTE NOM_CLIENTE ESTADO2301 2/23/03 101 MARTI CA2302 2/25/03 107 HERMAN WI2303 2/27/03 110 WE-SPORTS MI

- ARTICULOS_ORDENES

ID_ORDEN NUM_ITEM DESC_ITEM CANT PRECIO2301 3786 RED 3 352301 4011 RAQUETA 6 652301 9132 PAQ-3 8 4.752302 5794 PAQ-6 4 5.02303 4011 RAQUETA 2 652303 3141 FUNDA 2 10

Ahora procederemos a aplicar la segunda formal normal, es decir, tenemos que eliminar cualquier columna no llave que no dependa de la llave primaria de la tabla. Los pasos a seguir son:

» Determinar cuáles columnas que no son llave no dependen de la llave primaria de la tabla.» Eliminar esas columnas de la tabla base.» Crear una segunda tabla con esas columnas y la(s) columna(s) de la PK de la cual dependen.

La tabla ORDENES está en 2FN. Cualquier valor único de ID_ORDEN determina un sólo valor para cada columna. Por lo tanto, todas las columnas son dependientes de la llave primaria ID_ORDEN.

Por su parte, la tabla ARTICULOS_ORDENES no se encuentra en 2FN ya que las columnas PRECIO y DESC_ITEM son dependientes de NUM_ITEM, pero no son dependientes de ID_ORDEN. Lo que haremos a continuación es eliminar estas columnas de la tabla ARTICULOS_ORDENES y crear una tabla ARTICULOS con dichas columnas y la llave primaria de la que dependen.

Las tablas quedan ahora de la siguiente manera.

- ARTICULOS_ORDENES

ID_ORDEN NUM_ITEM CANT2301 3786 32301 4011 62301 9132 82302 5794 42303 4011 22303 3141 2



- ARTICULOS

NUM_ITEM DESC_ITEM PRECIO3786 RED 354011 RAQUETA 659132 PAQ-3 4.755794 PAQ-6 5.04011 RAQUETA 653141 FUNDA 10

La tercera forma normal nos dice que tenemos que eliminar cualquier columna no llave que sea dependiente de otra columna no llave. Los pasos a seguir son:

» Determinar las columnas que son dependientes de otra columna no llave.» Eliminar esas columnas de la tabla base.» Crear una segunda tabla con esas columnas y con la columna no llave de la cual son dependientes.

Al observar las tablas que hemos creado, nos damos cuenta que tanto la tabla ARTICULOS, como la tabla ARTICULOS_ORDENES se encuentran en 3FN. Sin embargo la tabla ORDENES no lo está, ya que NOM_CLIENTE y ESTADO son dependientes de ID_CLIENTE, y esta columna no es la llave primaria.

Para normalizar esta tabla, moveremos las columnas no llave y la columna llave de la cual dependen dentro de una nueva tabla CLIENTES. Las nuevas tablas CLIENTES y ORDENES se muestran a continuación.

- ORDENES

ID_ORDEN FECHA ID_CLIENTE2301 2/23/03 1012302 2/25/03 1072303 2/27/03 110

- CLIENTES

ID_CLIENTE NOM_CLIENTE ESTADO101 MARTI CA107 HERMAN WI110 WE-SPORTS MI

¿Qué tan lejos debe llevar la normalización?

La siguiente decisión es ¿qué tan lejos debe llevar la normalización? La normalización es una ciencia subjetiva. Determinar las necesidades de simplificación depende de nosotros. Si nuestra base de datosva a proveer información a un solo usuario para un propósito simple y existen pocas posibilidades de expansión, normalizar los datos hasta la 3FN quizá sea algo exagerado. Las reglas de normalización existen como guías para crear tablas que sean fáciles de manejar, así como flexibles y eficientes. A veces puede ocurrir que normalizar los datos hasta el nivel más alto no tenga sentido.

Normalización de base de datos | Fundamentos de Bases de Datos

http://quintonivel2010.wordpress.com/2010/05/28/normalizacion-de-ba...

1 de 6 19/06/2013 10:32 p.m.

¿Se están dividiendo tablas sólo para seguir las reglas o estas divisiones son en verdad prácticas?. Éstas son el tipo de cosas que nosotros como diseñadores de la base de datos, necesitamos decidir, y la experiencia y el sentido común nos pueden auxiliar para tomar la decisión correcta. La normalización no es una ciencia exacta, más bien subjetiva.

Existen seis niveles más de normalización que no se han discutido aquí. Ellos son Forma Normal Boyce- Codd, Cuarta Forma Normal (4NF), Quinta Forma Normal (5NF) o Forma Normal de Proyección-Unión, Forma Normal de Proyección-Unión Fuerte, Forma Normal de Proyección-Unión Extra Fuerte y Forma Normal de Clave de Dominio. Estas formas de normalización pueden llevar las cosas más allá de loque necesitamos. Éstas existen para hacer una base de datos realmente relacional. Tienen que ver principalmente con dependencias múltiples y claves relacionales.

En resumen

La normalización es una técnica que se utiliza para crear relaciones lógicas apropiadas entre tablas de una base de datos. Ayuda a prevenir errores lógicos en la manipulación de datos. La normalización facilita también agregar nuevas columnas sin romper el esquema actual ni las relaciones.

Existen varios niveles de normalización: Primera Forma Normal, Segunda Forma Normal, Tercera Forma Normal, Forma Normal Boyce-Codd, Cuarta Forma Normal, Quinta Forma Normal o Forma Normal de Proyección-Unión, Forma Normal de Proyección-Unión Fuerte, Forma Normal de Proyección-Unión Extra Fuerte y Forma Normal de Clave de Dominio. Cada nuevo nivel o forma nos acerca más a hacer unabase de datos verdaderamente relacional.

Se discutieron las primeras tres formas. Éstas proveen suficiente nivel de normalización para cumplir con las necesidades de la mayoría de las bases de datos. Normalizar demasiado puede conducir a tener una base de datos ineficiente y hacer a su esquema demasiado complejo para trabajar. Un balance apropiado de sentido común y práctico puede ayudarnos a decidir cuándo normalizar.

Fundame nto s de Base s de D at os

Fundamentos BD

Normalización de base de datos

A bout t he se ads

i2 Votes

¿Qué es normalización?

La normalización nos ayuda a clasificar objetos, relaciones, diferentes formas de relación que existan, basándose en cierta característica de cada uno.



2 de 6 19/06/2013 10:32 p.m.

Dependiendo de ciertas reglas que sean identificadas se puede poner en una categoría u otra.

¿Cuáles son los beneficios de la normalización?

Nos ayudan a eliminar redundancias e inconsistencias de dependencia en el diseño de la tabla.

Formalización cero

Es cuando ninguna de las reglas formales ha sido aplicada.

Primera Forma Normal (1FN)

Estas son las características que deben cumplirse para estar en primera forma normal.

Las celdas de las tablas deben tener valore simples y no se puede tener grupos ni arreglos que se repitan como valores, un valor por celdaLa tabla contiene una clave primaria. No existen columnas vacías.La clave primaria no tiene atributos nulos.

Segunda Forma Normal (2FN)

Estas son las características que deben cumplirse para estar en segunda forma normal.



3 de 6 19/06/2013 10:32 p.m.

Una relación debe estar en 1FN.Relacionar tablas mediante claves foráneas.Los atributos que no forman parte de ninguna clave dependen de forma completa de la claveprincipal. Es decir que no existen dependencias parciales.

Tercera Forma Normal (3FN)

Estas son las características que deben cumplirse para estar en tercera forma normal.

La tabla se encuentra en 2FNSe eliminan campos que no dependan de la clave.No hay dependencias transitivas entre los atributos, queremos decir con dependencias transitivascuando existe más de una forma de llegar a referencias a un atributo de una relación.

Cuarta Forma Normal (4FN)

Estas son las características que deben cumplirse para estar en cuarta forma normal

Se encuentra con respecto a un conjunto D de dependencias funcionales y de valores múltiples sí, para todas las dependencias de valores múltiples en D de la forma X->->Y, donde X<=R y Y<=R, se cumple por lo menos una de estas condiciones:

* X->->Y es una dependencia de valores múltiples trivial.* X es una superllave del esquema R.

Quinta Forma Normal (5FN)

Estas son las características que deben cumplirse para estar en quinta forma normal.

La tabla debe estar en 4FNNo existen relaciones de dependencias no triviales que no siguen los criterios de las claves.Una tabla que se encuentra en la 4FN se dice que está en la 5FN si cada relación de dependencia seencuentra definida por las claves candidatas.

Regl as de Codd



4 de 6 19/06/2013 10:32 p.m.

Codd se dio cuenta que habían bases de datos en el mercado que decían ser relacionales, pero sologuardaban información en tablas, sin estar estas tablas literalmente normalizadas; por lo tanto dio 12reglas que se debían establecer, pero en la vida real es difícil poner en práctica.



5 de 6 19/06/2013 10:32 p.m.

Regla N1 “La Regla de la información”

Toda la inf ormac ión e n un RD BM S e st á e xplíc it ame nt e re pre se nt ada de un a so la mane ra po r valore se nuna t abla.

Cualquier dato que no esté en una tabla no existe del todo. Cualquier tipo de información debe estar siempre adentro de una tabla en una base de datos. Las tablas que contienen tal información constituyen el Diccionario de Datos.

Regla N2 – La regla del acceso garantizado

Cada ít e m de dat os de be se r lógic ame nt e ac c e sible al e j e c ut ar un a búsque da que c ombine e l no mbre dela t abla, su c lave primaria, y e l nombre de la c olumna.

Esto significa que dado un nombre de tabla, la clave primaria, y el nombre de la columna que se necesita, deberá encontrarse uno y solamente un valor. Por esta razón es importante las claves primarias para todas las tablas.

Regla N3 – Tratamiento sistemático de los valores nulos

L a info rmac ión inaplic able o falt an t e pue de se r re pre se nt ada a t ravé s de valore s nulos

Un Sistema Gestor de Bases de Datos Relacionales debe soportar el ingreso de valores nulos en el lugar de columnas cuyos valores sean desconocidos.

Regla N4 – La regla de la descripción de la base de datos

L a de sc ripc ión de la base de dat os e s almac e nada de la misma man e ra que los dat os ordinarios, e st o e s,e n t ablas y c olumn as, y de be se r acc e sible a los usuarios aut orizados.

Toda la información debe ser almacenada únicamente: En tablas. Estas tablas deben ser accesibles igual que todas las tablas, a través de sentencias de SQL.

Regla N5 – La regla del sub-lenguaje Integral

D e be habe r al me nos un le nguaj e que se a int e gral para soport ar la de fi nic ión de dat os, manipulac iónde



6 de 6 19/06/2013 10:32 p.m.

dat os, de fi nic ión de vist as, re st ricc ione s de in t e gridad, y c on t ro l de aut orizac ion e s y t ransacc ione s.

Tiene que existir al menos un único lenguaje con una sintaxis bien definida, que será usado para



7 de 6 19/06/2013 10:32 p.m.

administrar la base de datos

Regla N6 – La regla de la actualización de vistas

Todas las vist as que son t e óric ame nt e ac t ualizable s, de be n se r ac t ualizable s por e l sist e ma mismo.

La mayoría de los Sistemas Gestor de Bases de Datos Relacionales permiten actualizar vistas simples en algunos momentos, pero no se puede ver las vistas complejas.

Regla N7 – La regla de insertar y actualizar

L a c apac idad de mane j ar un a base de dato s c on ope randos simple s aplic a n o só lo para la re c upe rac ióno c on sult a de dat os, sino t ambié n para la inse rc ió n, ac t ualizac ión y borrado de dat os ’ .

Quiere decir que para leer, escribir, eliminar y agregar registros deben estar disponibles y operables siempre, independientemente del tipo de relaciones y restricciones que exista entre las tablas.

Regla N8 – La regla de independencia física

E l ac c e so de usuario s a la base de dat os a t ravé s de t e rminale s o programas de aplic ac ión, de bepe rman e c e r c onsist e nt e lógic ame nt e c uando quie ra que h aya c ambios e n lo s dat os almac e nados, ose an c ambiados los mé t odos de acc e so a lo s dat os.

El cómo se comporten los programas de aplicación y actividad de usuarios vía terminales tiene que ser predecible basados en la definición lógica de la base de datos, y éste comportamiento debería permanecer inalterado, independientemente de los cambios en la definición física de ésta.

Regla N9 – La regla de independencia lógica

L os pro gramas de aplic ac ió n y las ac t ividade s de acc e so por t e rminal de be n pe rman e c e r lógic ame n t ein alt e radas c uando quie ra que se hagan c ambios (se gún los pe rmisos asign ados) e n las t ablas de la basede dat os.



8 de 6 19/06/2013 10:32 p.m.

La independencia lógica de los datos especifica que los programas de aplicación y las actividades de terminal deben ser independientes de la estructura lógica, por lo tanto los cambios en la estructura lógica no deben alterar o modificar estos programas de aplicación.

Regla N10 – La regla de la independencia de la integridad

Un ejemplo simple de normalización de bases de datos relacionales (has...

http://cnx.org/content/m18350/latest/

1 de 5 18/06/2013 08:08 p.m.

Todas las re st ricc ion e s de int e gridad de be n se r de fi n ible s e n los dat os, y almac e n able s e n e l c at alogo, n oe n e l pro grama de aplic ac ión.

Existen algunas reglas de integridad que son:

1. Los componentes de una clave primaria no pueden tener valores en blanco o nulos.2. Para cada valor de clave foránea deberá existir un valor de clave primaria

concordante. La unión de estas reglas garantizan que haya integridad referencial.

Regla N11 – La regla de la distribución

E l sist e ma de be posee r un le nguaj e de dat os que pue da soport ar que la base de dat os e s t é dist ribuidaf ísic ame n t e e n dist into s lugare s sin que e st o af e c t e o alt e re a los programas de aplic ac ión.

El soporte para bases de datos distribuidas quiere decir que una colección arbitraria de relaciones, bases de datos corriendo en una mezcla de distintas máquinas y distintos sistemas operativos y que esté conectada por una variedad de redes, pueda funcionar como si estuviera disponible como en una única base de datos en una sola máquina.

Regla N12 – Regla de la no-subversión

S i e l sist e ma t ie ne le nguaj e s de baj o nive l, e st os le nguaj e s de n ingun a mane ra pue de n se r usados paraviolar la int e gridad de las re glas y re st ricc ione s e xpre sadas e n un le n guaj e de alt o nive l (c omo S Q L).

Ciertos productos unicamente hacen una interfaz relacional para sus bases de datos No relacionales, por lo que abre la subversión de las restricciones de integridad. Pero no es permitido.



2 de 5 18/06/2013 08:08 p.m.

Un ejemplo simple de normalización de bases de datos relacionales (hasta 3FN)Module by: M i g u e l - A n g e l S i c i l i a .

Summary: Se describe un ejemplo sencillo (de una sola tabla) de aplicación de la normalización de bases de datos relacionales.

El proceso de normalización de bases de datos relacionalesLa normalización de bases de datos relacionales toma un esquema relacional y le aplic a un conjunto de técnicas para producir un nuevo esquema que representa la misma información pero cont iene menos redundancias y evita posibles anomalías en las inserciones, actualizaciones y borrado s.

Breve recordatorio del modelo (formal) relacional



3 de 5 18/06/2013 08:08 p.m.

El modelo relacional de bases de datos se basa en un modelo formal especificado de acuerdo a la teoría de conjuntos. Una base de datos relacional puede considerarse como un conjunto de rel aciones otablas de la forma R(A1, ..., An), donde R es el nombre de la relación, que se define por una seriede atributos Ai.

Sobre las tablas relacionales se pueden definir diferentes restricciones. La integridad de entidad es una restricción que nos indica que cada entidad representada por una tupla tiene que ser diferente de las demás en su relación, es decir, debe haber algunos atributos cuyos valores identifiqu en unívocamente las tuplas. La integridad referencial indica que una clave ajena solo debe contener valores que o bien sean nulos, o bien existan en la relación referenciada por la clave ajena.

El proceso de normalización

El proceso de normalización consiste en comprobar en secuencia si el esquema original está en 1FN,2FN y 3FN, analizando las dependencias funcionales en cada paso.

Un ejemplo completo

Tenemos una empresa pública donde los puestos de trabajo están regulados por el Estado, de modo que las condiciones salariales están determinadas por el puesto. Se ha creado el sigu iente esquema relacional

EMPLEADOS(nss, nombre, puesto, salario, emails) con nss como clave primaria.



4 de 5 18/06/2013 08:08 p.m.

nss nombre Puesto salario

emails

111 Juan Pérez Jefe de Área 3000 [email protected]; [email protected]

222 José Sánchez

Administrativo

1500 [email protected]

333 Ana Díaz Administrativo

1500 [email protected]; [email protected]

... ... ... ... ...T ABLE

1

Primera forma normal (1FN)

Una tabla está en 1FN si sus atributos contienen valores atómicos. En el ejemplo, podemos ver que el atributo emails puede contener más de un valor, por lo que viola 1FN.

En general, tenemos una relación R con clave primaria K. Si un atributo M viola la condición de 1FN, tenemos dos opciones.

Solución 1: duplicar los registros con valores repetidos

En general, esta solución pasa por sustituir R por una nueva relación modificada

R', en la cual: El atributo M que violaba 1FN se elimina.

Se incluye un nuevo atributo M' que solo puede contener valores simples, de modo que si R'[M'] es uno de los valores que teníamos en R[M], entonces R'[K] = R[K]. En otras palabras, para una tupla con n valores duplicados en M, en la nueva relación habrá n tuplas, que sólo varían en que cada una de ellas guarda uno de los valores que había enM.

La clave primaria de R' es (K, M'), dado que podrá haber valores de K repetidos, paralos valores multivaluados en M.

Siguiendo el ejemplo, tendríamos el siguiente esquema para la nueva tabla EMPLEADOS'(a)con clave primaria (nss, email):

nss Nombre puesto salario

email

111 Juan Pérez Jefe de Área 3000 [email protected]

111 Juan Pérez Jefe de Área 3000 [email protected]

222 José Sánchez

Administrativo






... ... ... ... ...T ABLE

2

Solución 2: separar el atributo que viola 1FN en una tabla

En general, esta solución pasa por:

sustituir R por una nueva relación modificada R' que no contiene el atributo M.



5 de 5 18/06/2013 08:08 p.m.

Crear una nueva relación N(K, M'), es decir, una relación con una clave ajena K referenciando



6 de 5 18/06/2013 08:08 p.m.

R', junto al atributo M', que es la variante mono-valuada del

atributo M. La nueva relación N tiene como clave (K, M').

Siguiendo el ejemplo, tendríamos el siguiente esquema para la nueva tabla EMPLEADOS'(b)

nss nombre puesto salario

111 Juan Pérez Jefe de Área 3000

222 José Sánchez

Administrativo

1500


1500

... ... ... ...T ABLE

3

Y además tendríamos una nueva tabla EMAILS con clave primaria (nss, email):

nss email






... ...T ABLE

4

Segunda forma normal (2FN)

Un esquema está en

2FN si: Está en 1FN.

Todos sus atributos que no son de la clave principal tienen dependencia funcional com pleta respecto de todas las claves existentes en el esquema. En otras palabras, para determinar cada atributo no clave se necesita la clave primaria completa, no vale con una subclave.

La 2FN se aplica a las relaciones que tienen claves primarias compuestas por dos o má s atributos. Si una relación está en 1FN y su clave primaria es simple (tiene un solo atributo), ento nces también está en 2FN. Por tanto, de las soluciones anteriores, la tabla EMPLEADOS'(b) está en 1FN (y la tabla EMAILS no tiene atributos no clave), por lo que el esquema está en 2FN. Sin embargo, tenemos que examinar las dependencias funcionales de los atributos no clave de EMPLEADOS'(a). Las dependencias funcionales que tenemos son las siguientes:

nss->nombre, salario, email

puesto->salario

Como la clave es (nss, email), las dependencias de nombre, salario y email son incompletas, porlo que la relación no está en 2FN.



7 de 5 18/06/2013 08:08 p.m.

En general, tendremos que observar los atributos no clave que dependan de

parte de la clave. Para solucionar este problema, tenemos que hacer lo siguiente

para los gupos de atributos con



8 de 5 18/06/2013 08:08 p.m.

dependencia

incompleta M: Eliminar

de R el atributo M.

Crear una nueva relación N con el atributo M y la parte de la clave primaria K de la que depende, quellamaremos K'.

La clave primaria de la nueva relación será K'.

Siguiendo el ejemplo anterior, crearíamos una nueva relación con los atributos que ti enen dependencia incompleta:

nss nombre puesto salario

111 Juan Pérez Jefe de Área 3000

222 José Sánchez

Administrativo

1500


1500

... ... ... ...T ABLE

5

Y al eliminar de la tabla original estos atributos nos quedaría:

nss email






... ...T ABLE

6

Como vemos, la solución a la que llegamos es la misma que en la otra opción de soluci ón para el problema de 1FN.

Tercera forma normal (3FN)

Una relación está en tercera forma normal si, y sólo si:

está en 2FN

y, además, cada atributo que no está incluido en la clave primaria no depende transit ivamente de la clave primaria.

Por lo tanto, a partir de un esquema en 2FN, tenemos que buscar dependencias funcionales entre atributos que no estén en la clave.

En general, tenemos que buscar dependencias transitivas de la clave, es decir, secuen cias de dependencias como la siguiente: K->A y A->B, donde A y B no pertenecen a la clave. La solución a este tipo de dependencias está en separar en una tabla adicional N



9 de 5 18/06/2013 08:08 p.m.

el/los atributos B, y poner como clave primaria de N el atributo que define la transitividad A.

Siguiendo el ejemplo anterior, podemos detectar la siguiente transitividad:

Geynen Rossler Montenegro Cochas Página 1

n

s

s

-

>

p

u

e

s

t

o

p

u

e

s

t

o

-

>

s

a

l

a

r


i

o

Por lo tanto la descomposición sería la siguiente:

nss nombre puesto

111 Juan Pérez Jefe de Área

222 José Sánchez

Administrativo


... ... ...T ABLE 7

En la nueva tabla PUESTOS, la clave sería el puesto, que también queda como clave ajena referenciando la tabla EMPLEADOS. El resto de las tablas quedan como estaban.


Ejemplo 2

Normalización de Datos

Cuando trabajamos con una base de datos relacional, los esquemas de las distintas relaciones que la constituyen nos indican que “cada dato tiene su lugar”. Pero, ¿qué ocurre si se modifican estas estructuras lógicas? . Muchas veces e s tan obvio que un dato debe de almacenarse en una de las relaciones y no en otra que se nos escapa la respuesta a porqué es así.

Concepto:La teoría de la normalización es en esencia una expresión formal de ideas sencillas

con una aplicación muy práctic a en el área del diseño de bases de datos, ya que conducen a una correcta elección del esquema de la base de datos.

Es la simplificación de los datos dentro de los campos de registro, este proceso lo considero importante ya que nos ayuda a dejar datos en estado demasiado simple de una forma entendible precisa, predecible y manejable. La normalización permite estructurar datos de forma precisa para representar las relaci ones necesarias entre los campos de un registro, también permite la recuperación de dato s sencillos que se pierden al realizar consultas y reportes.

Universo de las relaciones (normalizadas y no normalizadas) Relaciones 1FN – Normalizadas Codd

Relaciones 2FN – Normalizadas CoddRelaciones 3FN – Normalizadas

Codd

Relaciones BCFN – Boyce Codd

Relaciones 4FN – Fagin

Relaciones 5FN – Fagin

Visión de la Teoría de Normalización

Las bases de datos relacionales se normalizan para:


Evitar la redundancia de los datos. Evitar problemas de actualización de los datos en las tablas. Proteger la integridad de los datos.

Hablaremos de las 3 primeras formas de normalización básic a para el diseño de una base de datos.


PRIMERA FORMA NORMAL: Una relación está en primera forma normal (1FN) si y sólo si todos los dominios simples subyacentes contienen s ólo valores atómicos.


Poner la base de datos en la Primera Forma Normal resuelve el problema de los encabezados de columna múltiples .

SEGUNDA FORMA NORMA: Una relación está en segunda forma normal (2FN) si y sólo si está en 1FN y todos los atributos no clave dependen por completo de cualquier clave candidata.

La regla de la Segunda Forma Normal establece que todas las dependencias p arciales se deben eliminar y separar dentro de sus propias tablas. Una dependencia parcial es un término que describe a aquellos datos que no dependen de la llave primaria de la tabla para identificarlos.

TERCERA FORMA NORMA : Una relación está en tercera forma normal (3FN) si y sólo si está en 2FN y todos los atributos no clave dependen de manera no transitiva de cualquier clave candidata.

Una tabla está normalizada en esta forma si todas las columnas que no son llave son funcionalmente dependientes por c ompleto de la llave primaria y no hay dependencias transitivas. Una dependencia transitiva es aquella en la cual existen columnas que no son llave que dependen de otras columnas que tampoco son llave.

EJEMPLO:A través del siguiente ejercicio se intenta afirmar los conocimientos de normalización

con un ejemplo simplificado de una base de datos para una pequeña biblioteca.

CodLibro Titulo Autor Editorial NombreLector FechaDev

1001 Variable compleja Murray Spiegel McGraw Hill Pérez Gómez, Juan 15/04/2005

1004 Visual Basic 5 E. Petroustsos Anaya Ríos Terán, Ana 17/04/2005

1005 Estadística Murray Spiegel McGraw Hill Roca, René 16/04/2005

1006 Oracle University Nancy Greenberg yPriya Nathan Oracle Corp. García Roque, Luis 20/04/2005

1007 Clipper 5.01 Ramalho McGraw Hill Pérez Gómez, Juan 18/04/2005

Esta tabla no cumple el requisito de la Primera Forma Normal (1NF) de sólo tener campos atómicos, pues el nombre del lector es un campo que puede (y conviene) descomponerse en apellido pat erno, apellido materno y nombres. Tal como se muestra en la siguiente tabla.

1NFCodLibro Titulo Autor Editorial Paterno Materno Nombres FechaDev

1001 Variable compleja Murray Spiegel McGraw Hill Pérez Gómez Juan 15/04/2005

1004 Visual Basic 5 E. Petroustsos Anaya Ríos Terán Ana 17/04/2005

1005 Estadística Murray Spiegel McGraw Hill Roca René 16/04/2005

1006 Oracle University Nancy Greenberg Oracle Corp. García Roque Luis 20/04/2005


1006 Oracle University Priya Nathan Oracle Corp. García Roque Luis 20/04/2005

1007 Clipper 5.01 Ramalho McGraw Hill Pérez Gómez Juan 18/04/2005

Como se puede ver, hay cierta redundancia característica de 1NF.

La Segunda Forma Normal (2NF) pide que no existan dependencias parciales o dicho de otra manera, todos los a tributos no clave deben depender por completo de la clave primaria.

Actualmente en nuestra tabla tenemos varias dependencias parciales si consideramos como atributo clave el código del libro.

Por ejemplo, el título es completamente id entificado por el código del libro, pero el nombre del lector en realidad no tiene dependencia de este código, por tanto estos datos deben ser trasladados a otra tabla.

2NFCodLibro Titulo Autor Editorial

1001 Variable compleja Murray Spiegel McGraw Hill

1004 Visual Basic 5 E. Petroustsos Anaya

1005 Estadística Murray Spiegel McGraw Hill

1006 Oracle University Nancy Greenberg Oracle Corp.

1006 Oracle University Priya Nathan Oracle Corp.

1007 Clipper 5.01 Ramalho McGraw Hill

La nueva tabla sólo contendrá datos del lector.

CodLector Paterno Materno Nombres

501 Pérez Gómez Juan

502 Ríos Terán Ana

503 Roca René

504 García Roque Luis

Hemos creado una tabla para contener los datos del lector y también tuvimosque crear la columna CodLector para identificar unívocamente a cada uno. Sin embargo, esta nueva disposición de la base de datos necesita que exista otra tabla para mantener la información de qué libros están prestados a qué lectores. Esta tabla se muestr a a continuación:

CodLibro CodLector FechaDev

1001 501 15/04/2005

1004 502 17/04/2005

1005 503 16/04/2005

1006 504 20/04/2005

1007 501 18/04/2005

Para la Tercera Forma Normal (3NF) la relación debe estar en 2NF y además los atributos no clave deben ser mutuamente independientes y dependientes por completo de la clave primaria. También recordemos que dijimos que esto significa que las columnas


en la tabla deben contener solamente información sobre la entidad definida por la clave primaria y, por tanto, las columnas en la tabla deben contener datos acerca de una sola cosa.

En nuestro ejemplo en 2NF, la primera tabla conserva información acerca del libro, los autores y editoriales, por lo que debemos crear nuevas tablas para satisfacer los requisitos de 3NF.

3NFCodLibro Titulo

1001 Variable compleja

1004 Visual Basic 5

1005 Estadística

1006 Oracle University

1007 Clipper 5.01

CodAutor Autor

801 Murray Spiegel

802 E. Petroustsos

803 Nancy Greenberg

804 Priya Nathan

806 Ramalho

CodEditorial Editorial

901 McGraw Hill

902 Anaya

903 Oracle Corp.

Aunque hemos creado nuevas tablas para que cada una tenga sólo información acerca de una entidad, también hemos perdido la información acerca de qué autor ha escrito qué libro y las editoriales correspondientes, por lo que debemos crear otras tablas que relacionen cada libro con sus autores y editoriales.

CodLibro codAutor

1001 801

1004 802

1005 801

1006 803

1006 804

1007 806

CodLibro codEditorial

1001 901

1004 902

1005 901

1006 903


1007 901

Y el resto de las tablas no necesitan modificación.

CodLector Paterno Materno Nombres

501 Pérez Gómez Juan

502 Ríos Terán Ana

503 Roca René

504 García Roque Luis

CodLibro CodLector FechaDev

1001 501 15/04/2005

1004 502 17/04/2005

1005 503 16/04/2005

1006 504 20/04/2005

1007 501 18/04/2005

Ing. Orlando Bettin j.

Reestructurado y Modificado por BJ System


BIBLIOGRAFIA

Libros en pantalla de SQL Server 2005.




Ejemplo de normalización de una factura de venta

En la siguiente factura de compra venta, usted debe analizar toda la información disponible y debe crear el diccionario de datos.

No. 500456

Fecha: 05/04/2011




DESARROLLO

1. Creamos el Diccionario de Datos, para ello hacemos una lista de todos los campos presentes en el documento y elegimos para ellos una llave primaria.

Clave Principal

2. Aplicamos Primera Forma Normal 1FN: Dividimos la lista de datos del diccionario de datos endos grupos: El grupo # 1 estará formado por aquellos datos que no se repiten y en grupo # 2por aquellos datos repetitivos

No. 500456

Fecha: 05/04/2011

Datos no Repetitivos

Dato s

R

E

P

E

T




Al aplicar primera forma normal debemos adicionar en el grupo repetitivo el campo que se selecciono como llave primaria al momento de elaborar el diccionario de datos para que sirva como llave secundaria y permita establecer una relación de cardinalidad 1-N desde el grupo#1(no repetitivo) al grupo#2(grupo repetitivo) y seleccionamos una llave primaria al grupo#2. Aplicando lo anteriormente expuesto nos queda el siguiente modelo relacional en primera forma normal(1FN).

Grupo repetitivo

Llave secundaria

Relación de cardinalidad 1-N

3. Aplicamos Segunda Forma Normal 2FN: Al aplicar segunda forma normal sólo se analiza el grupo repetitivo (grupo #2) y se determina que datos dependen de forma única del la llave primaria, Codigo_Producto en nuestro caso, estos datos junto con la llave primaria formarán un nuevo grupo (grupo #3) cuya llave primaria será la misma que tenía el grupo#2(Codigo_Producto) y este mismo dato se conserva en el grupo #2 pero para este grupo pasa a ser llave secundaria.

Este grupo dependen de forma única del la llave primaria y soninherentes al producto.

Este grupo No dependen de forma única del la llave primaria y soninherentes a la venta No al producto

Al aplicar segunda forma normal nos que el siguiente modelo relacional




4. Aplicamos Tercera Forma Norma 3FN. Al aplicar tercera forma normal se analiza sólo al grupo no repetitivo, grupo # 1 en nuestro caso, y se separan de él aquellos campos que no dependan directamente de la llave primaria. Para el nuevo grupo se selecciona una llave primaria y dicho campo se conserva en el primer grupo como llave secundaria. Así nos queda el siguiente modelo.

5. D OTRAS OBSERVACIONES. Se ha seguido el proceso de normalización haciendo un ARD partiendo de un diccionario de datos formado a partir del esquema de una factura, es decir nos hemos basado en uno de los múltiples documentos que puede generar una empresa para formar la lista de datos, luego se ha procedido a aplicar 1FN,2FN y3FN. No obstante haber seguido el proceso de normalización hasta 3FN en posible que aun nuestra base de datos necesite algunos ajustes. En tal sentido procederemos a analizar cada una de las tablas y a hacer los ajustes que sean necesarios.

Cambiaremos los nombres de las tablas.El cambio en los nombres de las tablas se hace para que dichos nombres guarden relación con los datos que almacenan cada tabla. Los cambios propuestos semuestran a continuación.

Nota: Ahora asignar Nombres a las tablas

NOMBRE DEL GRUPO DESCRIPCION DE LA INFORMACION QUE CONTIENE CADA GRUPO NUEVO NOMBRE PARA LA TABLA REPRESENTATIVA DE CADA GRUPO

Grupo # 1 Información de la factura TBLFactura

Grupo # 2 Detalles de la venta realizada, es la lista de productosvendido y relacionados en una factura particular.

TBLDetalleFactura

Grupo # 3 Datos de los productos. TBLProductos

Grupo # 4 Datos del cliente TBLClientes

Adicionamos datos en aquellas tablas que lo requieran.

La tabla que guarda los datos de los productos no registra el valor actual de los productos por lo que se le adicionará un nuevo campo llamado VALOR_ACTUAL. Es importante no confundir el campo VALOR_ACTUAl de la tabla de productos con el campo VALOR_UNITARIO de la tabla de detalles de la factura el VALOR_ACTUAL como su nombre lo indica es el valor presente a la fecha de un producto en particular y el VALOR_UNITARIO es el precio al cual fue vendido un producto en particular

Después de haber hecho los ajustes necesarios (cabio de nombres a las tablas y adición de nuevos datos) hemos llegado al final del proceso de normalización y podemos estar seguros de que tenemos un buen diseño de nuestra base de datos. El modelo relacional final es el siguiente

Practicas

Guía de Ejercicios

Aplicar las reglas de normalización los siguientes ejercicios.

1. Un dato sin normalizar no cumple con ninguna regla de normalización. Para explicar con un ejemplo en qué consiste cada una de las reglas, vamos a considerar los datos de la siguiente tabla.

ordenes (id_orden, fecha, id_cliente, nom_cliente, estado, num_art, nom_art, cant, precio)

OrdenesId_orde

nFecha Id_client

eNom_client

eEstado Num_ar

tnom_ar

tcan

tPreci

o2301 23/02/1

1101 Martin Caracas 3786 Red 3 35,00

2301 23/02/11

101 Martin Caracas 4011 Raqueta 6 65,00

2301 23/02/11

101 Martin Caracas 9132 Paq-3 8 4,75

2302 25/02/11

107 Herman Coro 5794 Paq-6 4 5,00

2303 27/02/11

110 Pedro Maracay

4011 Raqueta 2 65,00

2303 27/02/11

110 Pedro Maracay

3141 Funda 2 10,00

solucion

PRIMERA FORMAL NORMAL (1FN)

Al examinar estos registros, podemos darnos cuenta que contienen un grupo repetido para NUM_ART, NOM_ART, CANT y PRECIO. La 1FN prohíbe los grupos repetidos, por lo tanto tenemos que convertir a la primera forma normal. Los pasos a seguir son: Tenemos que eliminar los grupos repetidos. Tenemos que crear una nueva tabla con la PK de la tabla base y el grupo repetido.

Los registros quedan ahora conformados en dos tablas que llamaremos ORDENES y ARTICULOS_ORDENES

ordenes (id_orden, fecha, id_cliente, nom_cliente, estado)Articulos_ordenes (id_orden, num_art, nom_art, cant, precio)

OrdenesId_orden Fecha Id_client

eNom_client

eEstado

2301 23/02/11 101 Martin Caracas2302 25/02/11 107 Herman Coro2303 27/02/11 110 Pedro Maracay

Articulos_ordenesId_orden Num_art nom_art cant Precio

2301 3786 Red 3 35,002301 4011 Raqueta 6 65,002301 9132 Paq-3 8 4,752302 5794 Paq-6 4 5,002303 4011 Raqueta 2 65,002303 3141 Funda 2 10,00

SEGUNDA FORMAL NORMAL (2FN)

Ahora procederemos a aplicar la segunda formal normal, es decir, tenemos que eliminar cualquier columna no llave que no dependa de la llave primaria de la tabla. Los pasos a seguir son: Determinar cuáles columnas que no son llave no dependen de la llave primaria de la

tabla. Eliminar esas columnas de la tabla base. Crear una segunda tabla con esas columnas y la(s) columna(s) de la PK de la cual

dependen.

La tabla ORDENES está en 2FN. Cualquier valor único de ID_ORDEN determina un sólo valor para cada columna. Por lo tanto, todas las columnas son dependientes de la llave primaria ID_ORDEN.

Por su parte, la tabla ARTICULOS_ORDENES no se encuentra en 2FN ya que las columnas PRECIO y NOM_ART son dependientes de NUM_ART, pero no son dependientes de ID_ORDEN. Lo que haremos a continuación es eliminar estas columnas de la tabla ARTICULOS_ORDENES y crear una tabla ARTICULOS con dichas columnas y la llave primaria de la que dependen.

Las tablas quedan ahora de la siguiente manera.

Articulos_ordenes (id_orden, num_art, cant)

Articulos_ordenesId_orden Num_art cant

2301 3786 32301 4011 62301 9132 82302 5794 42303 4011 22303 3141 2

Articulos ( num_art, nom_art, precio)

ArticulosNum_art nom_art Precio

3786 Red 35,004011 Raqueta 65,009132 Paq-3 4,755794 Paq-6 5,003141 Funda 10,00

TERCERA FORMAL NORMAL (3FN)

La tercera forma normal nos dice que tenemos que eliminar cualquier columna no llave que sea dependiente de otra columna no llave. Los pasos a seguir son: Determinar las columnas que son dependientes de otra columna no llave. Eliminar esas columnas de la tabla base. Crear una segunda tabla con esas columnas y con la columna no llave de la cual son

dependientes.

Al observar las tablas que hemos creado, nos damos cuenta que tanto la tabla ARTICULOS, como la tabla ARTICULOS_ORDENES se encuentran en 3FN. Sin

embargo la tabla ORDENES no lo está, ya que NOM_CLIENTE y ESTADO son dependientes de ID_CLIENTE, y esta columna no es la llave primaria.

Para normalizar esta tabla, moveremos las columnas no llave y la columna llave de la cual dependen dentro de una nueva tabla CLIENTES. Las nuevas tablas CLIENTES y ORDENES se muestran a continuación.

ordenes (id_orden, fecha, id_cliente)

OrdenesId_orden Fecha Id_cliente

2301 23/02/11 1012302 25/02/11 1072303 27/02/11 110

Clientes (id_cliente, nom_cliente, estado)

OrdenesId_client

eNom_cliente Estado

101 Martin Caracas107 Herman Coro110 Pedro Maracay

Por lo tanto la base de datos queda de la siguiente manera:

ordenes (id_orden, fecha, id_cliente)Clientes (id_cliente, nom_cliente, estado)Articulos ( num_art, nom_art, precio)Articulos_ordenes (id_orden, num_art, cant)

Unidad 5

IV ÁLGEBRA RELACIONAL

Codd propuso tres lenguajes de especificación para el modelo relacional como base teórica de cualquier lenguaje que quisiera cumplir con los requisitos formales del modelo. Estos lenguajes no pueden ser explotados comercialmente, al menos tal y como los definió Codd, porque adolecen de falta de operadores: carecen de operadores aritméticos simples (sumas, restas, etc.), o de manipulación de cadenas de caracteres, por poner dos ejemplos “escandalosos”. El propósito de estos lenguajes no es el de definir y manejar bases de datos relaciones, tan sólo constituyen una declaración de los mínimos requeridos para cualquier lenguaje de manipulación de datos que se quiera etiquetar a sí mismo como “relacional”. En otras palabras, cualquier lenguaje de manipulación y definición de datos en bases de datos relacionales ha de poseer la potencia suficiente como para “hacer”, como mínimo, lo que pueden “hacer” los lenguajes de Codd.El primero de ellos, al menos por el orden en el que se van a introducir en esta asignatura, es el álgebra relacional. Recibe este nombre precisamente por su carácter algebraico: incluye un conjunto de operadores (ocho, concretamente) cuyos operandos son relaciones y el resultado de la operación es otra relación, del mismo modo que cuando sumamos dos enteros obtenemos otro número entero.En primer lugar se definirán una serie de conceptos necesarios para definir los operadores del álgebra relacional y para, finalmente, trabajar con ellos. A continuación se describirán, uno por uno, los ocho operadores propuestos por Codd:

Unión ∪ Selección dondeIntersección ∩ Proyección [ ]

Diferencia − Concatenación Natural ∞Producto Cartesiano × División ÷

61

IV1. Conceptos previos.Al describir las propiedades de cada operador se van a utilizar una serie de términos que debemos definir previamente.

En primer lugar se presentará una adaptación del concepto de relación matemática en la que se vuelve a hacer uso de la ordenación de las componentes de una tupla. El resto, son expresiones o reformulaciones de conceptos ya presentes en la definición del modelo.

Los conceptos a definir son:

• Relación• Esquema de relación• Nombres Cualificados de Atributo• Alias de una relación• Relación Nominada• Relación Derivada• Relaciones Compatibles• Operación conmutativa• Operación asociativa

relaciónEl AR hace uso del orden de las componentes de las tuplas para definir operadores y propiedades de los operadores. En realidad, se trata de retomar la definición original de la relación matemática como el subconjunto de un producto cartesiano de n dominios, de tal forma que las tuplas resultado de ese producto cumplían y cumplen que

Las tuplas son listas de valores (conjunto ordenado) tal que el i-ésimo valor pertenece al i-ésimo dominio.

Vamos a combinar la definición anterior de tupla con la adaptación que en su momento introdujimos a la relación matemática para adecuarla al objetivo final que es una base de datos. Utilizaremos al mismo tiempo los nombres de atributos y el orden de las componentes en una tupla:

El conjunto de nombres de atributos es un conjunto ordenado.

Las tuplas son listas de valores (conjunto ordenado) tal que eli-ésimo valor pertenece al i-ésimo dominio asociado al i-ésimo nombre de atributo.

A partir de ahora, los operadores pueden utilizar tanto el nombre simbólico de un atributo como su orden dentro de la tupla.

62

álgebra relacional

63

esquema de relaciónEs la descripción formal de la relación con sus atributos y dominios asociados. En realidad se aplica únicamente a las relaciones nominadas, aquellas descritas en el esquema lógico relacional.

R( A1:D1, A2:D2, ..., An:Dn ) donde: R es el nombre de la relación

Ai es el nombre del atributoDi es el nombre del dominio asociado a Ai

nombres cualificados de atributoEs, por decirlo así, el nombre completo de un atributo, por ejemplo R.Ai, el atributo Ai de la relación R. Su uso evita la ambigüedad de dos atributos en dos tablas distintas con el mismo nombre.

En general, nos referimos a los atributos por su nombre sin especificar la relación a la que pertenecen. No obstante, es habitual que en distintas relaciones, y sobre todo en las relaciones derivadas (los resultados de operar con relaciones nominadas), nos podamos encontrar nombres de atributo coincidentes en relaciones distintas. La forma de diferenciar unos de otros es utilizar los nombres cualificados: “alumno.nombre”, “asignatura.nombre”.

En definitiva, se pueden utilizar indistintamente, siempre y cuando no se produzcan ambigüedades, las dos formas ya conocidas de referirse a un atributo:

• nombre cualificado: R.Ai

• nombre no cualificado: Ai

alias de una relaciónNombre alternativo para una relación. Dada una relación R se define un alias mediante la declaración:

definir alias S para R

Entonces la relación puede referenciarse tanto por R como por S, y los nombres cualificados de atributos R.Ai o S.Ai.

relación nominadaEs toda relación definida en el esquema lógico relacional. En otras palabras, las que constituyen nuestra base de datos.

relación derivadaEs aquella que se obtiene como resultado de una expresión del Álgebra

álgebra relacional

64

Relacional.

álgebra relacional

65

Una relación derivada no tiene nombre ni alias. Así pues, los nombres de los atributos de ésta se obtendrán a partir de los nombres cualificados de atributos de las relaciones operando, y si existe ambigüedad se utilizarán los alias.

Las reglas que rigen en los operadores para la asignación de nombres a los atributos de relaciones derivadas se verán con cada uno de ellos.

relaciones compatiblesDos relaciones son compatibles si el grado de ambas es el mismo y los dominios asociados a los i-ésimos atributos de cada una son iguales.

R( A1:D1, A2:D2, ..., An:Dn ) S( B1:E1, B2:E2, ..., Bm:Em )

R y S son compatibles si y sólo si:1) n = m2) ∀i Di = Ei (1 ≤ i ≤ n)

Dicho de otra forma, el número de atributos ha de ser el mismo en ambas relaciones y, además, los dominios han de ser los mismos para atributos de la misma posición.

operación conmutativa

Un operación es conmutativa15 si se cumple queA ⊗ B = B ⊗ A

operación asociativa

Una operación es asociativa si se cumple que(A ⊗ B) ⊗ C = A ⊗ (B ⊗ C)

operadoresLos operadores del Álgebra Relacional (bajo el punto de vista de Codd) se dividen en dos grupos:

de la teoría de conjuntos relacionales UNIÓN

INTERSECCIÓN DIFERENCIA

PRODUCTO CARTESIANO

SELECCIÓN PROYECCIÓN DIVISIÓN

álgebra relacional

66

CONCATENACIÓN NATURAL

15En la descripción de los operadores, algunos se dice que cumplen la propiedad conmutativa, refiriéndonos exclusivamente al conjunto de tuplas de la relación derivada, y no al conjunto de atributos de dicha relación resultado, cuyos nombres cualificados dependen del orden de las relaciones operando.

álgebra relacional

67

Como el resultado de una expresión en Álgebra Relacional es otra relación, ésta puede participar como operando a su vez, permitiendo la construcción de expresiones anidadas.

R ⊗ S = D1

D1 ⊗ O = D2R ⊗ S ⊗ O = D2

La única regla de precedencia entre operadores es la utilización de paréntesis, en cualquier caso las expresiones se evalúan de izquierda a derecha de tal forma que el resultado de una operación es el operando de la siguiente operación a su derecha:

R ⊗ S = D1

O ⊗ D1 = D2O ⊗ ( R ⊗ S ) = D2

Otra clasificación se basa en la propia definición de los operadores: Son operadores básicos (o primitivas) la unión, diferencia, producto cartesiano, selección y proyección, y operadores derivados la intersección, la división y la concatenación natural ya se definen a partir de la combinación de varios operadores básicos.

Dicho de otra forma, los cinco operadores básicos definen la potencia expresiva del lenguaje, con ellos se pueden realizar todas las operaciones que permite el álgebra relacional, mientras que los tres derivados se pueden ver como “atajos” o “resúmenes” de varias operaciones básicas reunidas en un único operador.

IV2. definición informal de los operadores

En primer lugar, se pretende dar una visión de cómo operan y que resultado obtienen los operadores antes mencionados. Posteriormente, se dará la definición formal de todos ellos como referencia del lenguaje.

Cuando trabajamos con una base de datos relacional, si de manipulación de datos estamos hablando, la recuperación de información desde las tablas es un proceso habitual y necesario.

En álgebra relacional, si queremos recuperar el contenido completo de una relación, por ejemplo todos los alumnos almacenados en mi base de datos, una vez identificada la tabla de la que extraer los datos, la tabla alumno, la evaluación del nombre de la relación nos devuelve todas sus filas:

Alumnoalumno nombre dirección

234 LUCÍA c/A, 3321 JUAN c/C, 33221 LUISA c/E, 333

select *from alumno

álgebra relacional

68

Si a un intérprete de AR teórico le ordenamos simplemente “alumno”, el resultado es la tabla de 3 filas anterior. Por razones de claridad los ejemplos incluyen la operación, el esquema de la relación resultado y las filas obtenidas, según se detalla en el gráfico siguiente.

esquema de la tabla resultado de evaluar

dicha operación

filas obtenidas en la operación

operación que se desea evaluar



A partir de ahora, los ejemplos muestran el contenido de las tablas utilizadas y el resultado de cada uno de los operadores. También se darán las expresiones correspondientes en SQL como ayuda para la comprensión de cada operación.

selecciónEn muchas ocasiones sólo nos interesan algunos individuos de una relación, aquellos que poseen unas determinadas características: “clientes de la provincia de Alicante”, “artículos que cuestan mas de 500 euros”.

El operador selección (“donde condición”) recupera tuplas de una relación que cumplen una determinada condición.


577 YÉNIFER c/C, 33234 LUCÍA c/A, 3321 JUAN c/C, 33221 LUISA c/E, 333

Alumno donde dirección = ‘c/C, 33’alumno nombre dirección

577 YÉNIFER c/C, 33321 JUAN c/C, 33

select *from alumnowhere dirección = ‘c/C, 33’

proyecciónSi la selección recupera filas, la proyección ([columna(s)]) recupera atributos.


álgebra relacional

69

577 YÉNIFER c/C, 33

álgebra relacional

70


Alumno [alumno, nombre]alumno nombre

577 YÉNIFER234 LUCÍA321 JUAN221 LUISA

select alumno, nombre from alumno

La selección me permite recuperar aquellos individuos que me interesan y la proyección elimina aquellos datos de los individuos que no necesito. Como ya se ha dicho, se pueden realizar varias operaciones consecutivas, como se muestra a continuación:

Alumno donde dirección = ‘c/C, 33’ [alumno, nombre]alumno nombre

577 YÉNIFER321 JUAN

select alumno, nombre from alumnowhere dirección = ‘c/C, 33’

La proyección podría eliminar la o las columnas definidas como clave candidata y podría dar lugar a duplicados entre las filas (o más bien nuestra percepción de la relación nos puede inducir a pensar que se puedan mostrar filas iguales). Como el resultado de esta operación es también una tabla, ésta está sujeta a las mismas restricciones del modelo que cualquier otra. Se puede pensar que el AR realiza la proyección en dos pasos: la proyección como tal y una eliminación de duplicados, si es que se producen.

Si proyectáramos sobre la columna dirección, la tabla alumno contiene dos filas con los mismos valores. Sin embargo, el resultado de tal operación es el siguiente:

Alumno [dirección]dirección

c/C, 33c/A, 3

c/E, 333

select dirección16from alumno

16 En realidad, es habitual que los SGBD relacionales y su implementación de SQL, ante expresiones como esta devuelvan filas duplicadas. El modificador distinct realiza ese segundo paso al que hemos hecho referencia en el álgebra relacional. A partir de ahora, aunque no aparezca en los ejemplos, se entenderá que todas las órdenes select incluyen este modificador: “select distinct dirección from alumno where dirección = ‘c/C, 33’ ”

álgebra relacional

71

Obviamente, esto es aplicable a cualquier operación que hagamos puesto que el resultado siempre va a ser una tabla derivada.

uniónLa unión de dos relaciones da como resultado otra relación que contiene las tuplas de las dos. Como en una relación no existen tuplas duplicadas (por ser un conjunto, por estar definida por un producto cartesiano), sería más exacto describir la unión de dos relaciones A y B como otra relación C que contiene las tuplas de A que no están en B, las tuplas de B que no están en A, y las tuplas que están en A y B a la vez.



Profesorprofesor nombre dirección

522 JOSÉ c/F, 32778 EVA c/F, 51221 LUISA c/E, 333

Alumno ∪ Profesoralumno nombre dirección

234 LUCÍA c/A, 3321 JUAN c/C, 33221 LUISA c/E, 333522 JOSÉ c/F, 32778 EVA c/F, 51

select * from alumno unionselect * from profesor

La unión sólo se puede realizar si A y B son compatibles y, evidentemente, es conmutativa y asociativa (ver las definiciones anteriores)

álgebra relacional

72

intersecciónLa intersección de dos relaciones da como resultado otra relación que contiene las tuplas comunes a las dos primeras.





Alumno ∩ Profesoralumno nombre dirección

221 LUISA c/E, 333

select * from alumno intersectselect * from profesor

Al igual que la unión, la intersección sólo se puede realizar si A y B son compatibles y es conmutativa y asociativa.

diferenciaLa diferencia de dos relaciones se define como aquellas tuplas que están en la primera pero no en la segunda. Nótese que este operador ya no es conmutativo, importa el orden de los operandos.

álgebra relacional

73

álgebra relacional

74





Alumno - Profesoralumno nombre dirección

234 LUCÍA c/A, 3321 JUAN c/C, 33

select * from alumno minusselect * from profesor

La diferencia sólo se puede realizar si A y B son compatibles.

producto cartesianoEste operador se utiliza para generar todas las posibles combinaciones de las tuplas de una relación con todas y cada una de las tuplas de otra relación; se obtienen todas las combinaciones posibles de filas entre dos tablas.





cod departamentoLSI LENGUAJES

CCIA CIENCIASFI FILOLOGÍA

álgebra relacional

75

Alumno × Profesoralumno nombre dirección profesor nombre dirección

234 LUCÍA c/A, 3 522 JOSÉ c/F, 32234 LUCÍA c/A, 3 778 EVA c/F, 51234 LUCÍA c/A, 3 221 LUISA c/E, 333321 JUAN c/C, 33 522 JOSÉ c/F, 32321 JUAN c/C, 33 778 EVA c/F, 51321 JUAN c/C, 33 221 LUISA c/E, 333221 LUISA c/E, 333 522 JOSÉ c/F, 32221 LUISA c/E, 333 778 EVA c/F, 51221 LUISA c/E, 333 221 LUISA c/E, 333

select * from alumno, profesor

concatenación naturalEste operador se utiliza para combinar información de dos tablas que comparten un atributo común. En este caso quedará más claro un ejemplo en el que la base de datos refleje una relación entre, por ejemplo, profesores y departamentos a los que están adscritos. La relación entre los dos se representa por una columna en la tabla profesor que indica el código de departamento en el que trabaja.

La concatenación natural utiliza los atributos que tienen el mismo nombre y, por supuesto, están definidos sobre el mismo dominio.

Profesor Departamentoprofesor nombre Dirección cod

522 JOSÉ c/F, 32 LSI778 EVA c/F, 51 LSI221 LUISA c/E, 333 FI

Profesor ∞ Departamentoprofesor nombre dirección cod departamento

522 JOSÉ c/F, 32 LSI LENGUAJES778 EVA c/F, 51 LSI LENGUAJES221 LUISA c/E, 333 FI FILOLOGÍA

select p.*, d.departamentofrom profesor p, departamento d where p.cod = d.cod17

Si no existen 2 filas, una en cada tabla con ese valor idéntico que las une, el resultado de la concatenación es vacío (no devuelve ninguna tupla). En nuestro ejemplo sería el caso de que ningún profesor trabajara en ningún departamento (si la columna Profesor.cod almacenara nulos para todas las filas en un determinado estado de la base de datos).

El problema se presenta cuando más de una columna comparte tanto nombre como dominio: el operador intentará unir las filas que comparten los mismos valores en todos esos atributos. Cambiando un poco el ejemplo anterior,

17 El operador concatenación natural lleva implícita la condición de la cláusula where. En realidad, esta expresión en SQL es la descomposición en primitivas de la concatenación natural que se verá más adelante: un producto cartesiano más una selección y una proyección.

cod nombreLSI LENGUAJES

CCIA CIENCIASFI FILOLOGÍA

álgebra relacional

76

podemos pensar que el esquema de Departamento tiene como nombre de la columna de descripción nombre en vez de departamento. La concatenación natural sólo devolverá aquellos profesores y departamentos que cumplan que el profesor trabaja en él y que ambos tienen el mismo nombre.

Profesor Departamentoprofesor nombre dirección cod

522 LENGUAJES c/F, 32 LSI778 EVA c/F, 51 LSI221 LUISA c/E, 333 FI

Profesor ∞ Departamentoprofesor nombre dirección cod

522 LENGUAJES c/F, 32 LSI

select p.*,d.departamentofrom profesor p, departamento dwhere p.cod = d.cod and p.nombre =d.nombre

Cuando no se desea este comportamiento, si queremos que la unión de filas se realice únicamente por las columnas etiquetadas como cod, estamos obligados a utilizar una combinación de otros operadores. De hecho, la concatenación natural es uno de los operadores derivados, y se puede expresar en función de los operadores básicos selección, proyección y producto cartesiano.

divisiónEste operador es el de aplicación menos habitual, se utiliza para consultas del tipo “alumnos matriculados en todas las asignaturas”. Es, además, el operador con más restricciones a la hora de construir una expresión en AR. El dividendo debe tener más atributos que el divisor, dividendo y divisor han de compartir uno o varios atributos, éstos han de ser los últimos del dividendo y los únicos del divisor y estar en el mismo orden. Los atributos no comunes son la información que se obtiene de la división. En la práctica, sea necesario o no, se suele utilizar la proyección para asegurar que se cumplen todas estas reglas.

En nuestro ejemplo, “alumnos matriculados en todas las asignaturas”, usamos la tabla matriculado que es la que representa qué alumnos están matriculados en qué asignaturas. Todas la asignaturas son, evidentemente, las asignaturas almacenadas en la tabla asignatura18: el atributo común a ambas relaciones es “asignatura”, y la información que queremos obtener es “alumno” (que después se puede concatenar con la tabla Alumno para obtener su “nombre” y “dirección”).

álgebra relacional

77

18 En realidad, debemos identificar ese “todos” al qué se refiere, a qué tabla y a qué datos, puede no ser trivial: “alumnos matriculados en todas las asignaturas de menos de 6 créditos”, por ejemplo.

73

asignatura nombreBD1 Bases1BD2 Bases2FP2 Prog2

álgebra relacional

Alumno Asignaturaalumno nombre dirección


Matriculadoalumno asignatura

234 BD1234 BD2234 FP2321 BD1321 FP2

Matriculado [alumno, asignatura] % ( Asignatura [asignatura] ) alumno

234select alumnofrom matriculado mwhere not exists(select * from asignatura a

where not exists(select * from matriculado m2

where m2.alumno = m.alumno and m2.asignatura =

a.asignatura))19

La razón de que la división tenga unas reglas de composición tan estrictas está en que éste es otro de los operadores derivados, ya que se puede realizar la misma operación con una combinación de diferencias y proyecciones.

IV2.1.el problema de los nombres de los atributos en las relaciones derivadas

Cuando operamos en AR de dos tablas obtenemos una tercera tabla resultado. Esta tabla cumple todas las restricciones del modelo relacional y, por tanto, tiene un esquema de relación que es generado automáticamente. Dicho de otra forma, las relaciones derivadas también tienen nombre para sus atributos pero estos nombres se asignan directamente de los operandos y según el operador utilizado y el orden de tales operandos.

Por ejemplo, la unión, intersección y diferencia dan como resultado una tabla que tiene los mismos nombres de columna que la primera tabla operando.

Alumno (alumno, nombre, dirección) Profesor (profesor, nombre, dirección) Alumno ∪ Profesor

T(Alumno.alumno, Alumno.nombre, Alumno.dirección)

19 La expresión “seleccionar aquellos alumnos que están matriculados en todas las asignaturas” se puede resolver en SQL si cambiamos “un poco” el enunciado: “seleccionar aquellos alumnos que cumplen que no existe ninguna asignatura en la que no estén matriculados”

álgebra relacional

74

Pero para la concatenación natural los nombres se asignan de diferente forma: todos los nombres de atributo de la primera relación más los no “comunes” de la segunda.

Profesor (profesor, nombre, dirección, cod) Departamento (cod, descripción)Profesor ∞ Departamento

T(Profesor.profesor, Profesor.nombre,Profesor.dirección, Profesor.cod, Departamento.descripción)

El producto cartesiano produce tablas con todos los atributos de las dos relaciones operando, pero respetando el orden en que se ha operado:

Alumno (alumno, nombre, dirección) Profesor (profesor, nombre, dirección) Alumno × Profesor

T(Alumno.alumno, Alumno.nombre,Alumno.dirección, Profesor.profesor,Profesor.nombre, Profesor.dirección)

La división produce una tabla con los atributos no comunes de la primera relación:

Matriculado [alumno, asignatura]% ( Asignatura [asignatura] )

T(Matriculado.alumno)

Finalmente, la selección y la proyección, al ser operadores con un único operando, la tabla resultado tiene todos (si es una selección) o algunos (si es una proyección) de los atributos de la relación operando.

Alumno donde dirección=‘c/C, 33’T(Alumno.alumno, Alumno.nombre, Alumno.dirección)

Alumno [dirección] T(Alumno.dirección)

Es fundamental tener claro cuales son los atributos que resultan de cada operación ya que las expresiones del AR son una secuencia de operaciones cuyo resultado es el operando de la siguiente. La evaluación de las expresiones se realiza de izquierda a derecha salvo si se utilizan paréntesis que alteran el orden de evaluación. Las siguientes expresiones son correctas:

Alumno × Profesor [Alumno.nombre]

Matriculado [alumno, asignatura]% ( Asignatura [asignatura] )

Alumno donde dirección = ‘c/C, 33’ [alumno, nombre]

Pero las mostradas a continuación son incorrectas:

Alumno × Profesor [nombre]

álgebra relacional

75

hay dos columnas “nombre” en la relación derivada.

Matriculado [alumno, asignatura] % Asignatura [asignatura]

álgebra relacional

76

al evaluar de izquierda a derecha la aplicación de los operadores sería proyección, división y, finalmente, proyección. Los atributos del divisor serían todos los de Asignatura, y no son consistentes con las restricciones del operador. Antes de dividir, se debería proyectar sobre Asignatura.

Alumno [alumno, nombre] donde dirección = ‘c/C, 33’

si proyectamos antes de seleccionar estamos eliminando precisamente la columna dirección que es la que utiliza la selección en la condición de filtro.

IV2.2.operaciones primitivas.Las únicas operaciones consideradas como primitivas son la selección, proyección, unión, producto cartesiano, y diferencia. Así, concatenación natural, intersección y división se pueden expresar en función de las primitivas mencionadas antes:

R ∩ S = R - (R - S)R ∞ S = ((R×S) DONDE R.B1=S.B1 y... y R.Bm=S.Bm ) [R.A1, ..., R.An, R.B1, ..., R.Bm, S.C1, ...,

S.CP]donde los Bi son los atributos comunes a las dos relaciones, los Ai los no comunes de R y los Ci los no comunes de S.

R ÷ S = R[B] - ((R[B] × S) - R)[B]donde B es el conjunto de atributos no comunes de R.

IV2.3.uso del alias de una relaciónSe puede definir un alias para una relación en cualquier ocasión pero su uso está más justificado por operaciones como la siguiente

Matriculado × Matriculado

Un producto de una tabla por si misma es a veces necesario para ciertas consultas de conteo simple (“alumnos que se han matriculado de al menos 2 asignaturas”). El problema reside en que el esquema de la relación derivada sería:

T(Matriculado.alumno, Matriculado.asignatura,Matriculado.alumno, Matriculado.asignatura)

Evidentemente, en una tabla no pueden existir dos columnas distintas con el mismo nombre (aunque sí en dos tablas distintas). La solución consiste en utilizar uno o dos alias para la relación Matriculado:

definir alias M para MatriculadoMatriculado × M

T(Matriculado.alumno, Matriculado.asignatura,

77

Practicas

Tenemos el siguiente esquema relacional de base de datos:CLIENTES(Nº Cliente, Nombre, Dirección, Teléfono, Población)

PRODUCTO(Cod Producto, Descripción, Precio)

VENTA(Cod Producto, Nº Cliente, Cantidad, Id Venta)

La tabla de clientes almacena información sobre cada posible cliente de nuestra empresa.

En la tabla de productos almacenamos información sobre cada producto de la empresa.

La tabla de ventas relaciona a las dos anteriores utilizando el atributo cod Producto para indicar el producto que se venda, y el atributo Nº Cliente para indicar el cliente al que vendimos el producto.

Sobre ella se realizan estos ejercicios (las soluciones están al final):[1] Realizar una consulta que muestre el

clientes de Palencianombre de los

[2] Indicar el código y descripción de los código coincida con su descripción

productos cuyo

[3] Obtener el nombre de los clientes junto con el identificador de venta y la cantidad vendida, de aquellos productos de los que se vendieron más de 500 unidades

[4] Nombre de los clientes de la tabla Clientes que no aparecen en la tabla de ventas (Clientes que no han comprado nada)

[5] Nombre de los clientes que han comprado todos los productos de la empresa

[6] Identificador de las ventas cuya cantidad supera a la cantidad vendida en la venta número 18

[7] Productos que no se han comprado nunca en Palencia

[8] Productos que se han vendido tanto en Palencia como enValladolid

78

[9] Poblaciones a las que hemos vendido todos nuestros productos

Imaginemos que añadimos la tabla de facturas que se relaciona con la de ventas, de modo que a la tabla de ventas le añadimos el nº de Factura con la que se relaciona. En la tabla de factura indicamos la fecha, el número y si se pago o no (un 1 significa pagado, un 0 que no está pagada). Cada factura se corresponde con varias ventas y con un solo cliente, para lo cual se varía el diseño:

FACTURA(Nº Factura, Fecha, Pagada, Nº Cliente)

VENTA(Cod Producto, Nº Factura, Cantidad, Id Venta)

[10] Obtener el nombre de los clientes que tienen alguna factura sin pagar

[11] Clientes que han pagado todas sus facturas

SolucionesLo primero es renombrar las tablas para facilitar su manejo en las consultas:

Clientes → CPr oductos → PVentas → V

[1]

[2]

∏ nombre (σ población ="Palencia "C )

∏ cód Pr oducto , Descripción (σ cod Pr oducto = Descripción P)

[3] ∏ C . Nombre , P. Descripción ,V

.Cantidad

((σ cantidad >500V )∞P∞C )

[4] ∏ nombre C− ∏ nombre(C∞V )[5] Se aplica una división sobre toda la tabla de

ventas mezclada con clientes y se divide entre la tabla de productos (quedan los clientes que tienen todas las combinaciones de la tabla de productos)

79

∏ nombre ((∏ C .nombre ,C . N ºCliente ,V .codproducto (C∞V )) : (∏ codproducto

P))

[6] Dividimos la consulta en dos, primero obtenemos la fila correspondiente a la venta nº 18 y luego la combinamos con todas las demás eliminando las que tengan ventas menores

σ idVenta

=18V→ V '

V ∞ V 'V .cantidad >V '.Cantidad

[7] Se resuelve sacando primero los productos que sí se compraron en Palencia y luego restándoles del conjunto total de Productos

∏V .codproducto ((σ población="Palencia"C )∞V ) →Pale

∏ codproducto P − Pale

[8] Se trata de una intersección entre los productos dePalencia y los productos comprados en Valladolid

∏V .codproducto ((σ población="Palencia"C)∞V ) → Pale

∏V .codproducto ((σ población="Valladolid "C )∞V ) → Vall

Pale I Vall

[9] Necesitamos sacar la lista de poblaciones con los códigos de productos que se han vendido en ellas. Luego dividimos entre los códigos de la tabla de productos y quedarán las poblaciones en las que se han pedido todos los códigos

∏ poblacion ((∏C . población ,V .codproducto (C∞V )) : (∏codprodcto

P))

[10]

∏ nombre ,n º factura (σ Pagada =0 (C∞F ))

80

[11] La consulta no se puede hacer como la anterior, ya que puede haber clientes que hayan pagado algunas facturas y otras no. Se parte de la consulta anterior para hacer esto:

∏ nombre (σ Pagada =0 (C∞F )) →

Pagadores

81

∏ nombre

82

− PagadoresPractica 2

Ejercicios de álgebra relacional (2)Tenemos el siguiente esquema relacional de base de datos:EQUIPOS(Id Equipo, Nombre, Población, nº socios)

JUGADORES(Id Jugador, Nombre, Nacionalidad, Id Equipo)

PARTIDO(Id Equipo Casa, Id Equipo Fuera, Fecha, Id Partido, Goles Casa, Goles Fuera)

Sobre ella se realizan estos ejercicios (las soluciones están al final):[1] Mostrar el nombre de los jugadores del Real Madrid

[2] Partidos en el que el resultado fue un empate, se requiere el nombre del equipo que jugó en casa, el nombre del equipo que jugó fuera y los goles que marcó cada uno


Jugadores → JPartidos → PEquipos → E

[1] ∏ nombre((σ nombre ="Re alMadrid " E ) ∞ J )IdEquipo

83

[2] El problema de esta consulta es que los equipos se relacionan con los partidos dos veces, una como equipos de casa y otra como equipos foráneos. Por ello primero conseguimos el nombre del equipo que juega en casa y luego el nombre del que juega fuera. El resto es fácil

∏nombre , golescasa , ( Pgolesfuera ,idequipofu era

∞P.idequipocasa = e.idequipo

E )− > P'

84

∏ P '.nombre , golescasa , ( P'golesfuera , E .nombre

σ golescasa = golesfuera P' '

85

∞P.idequipofu era = e.idequipo

86

E ) → P' 'PRACTICA 3

Ejercicios de álgebra relacional (3)Con este esquema:SANITARIOS(Id Sanitario, Nombre, Profesión)

PACIENTES(Id Paciente, Nombre, Nº SS, Dirección, Teléfono)

CONSULTA(Id Sanitario, Id Paciente, Día, Mes, Año, Comentarios, Id Consulta)

RECETA(ID Consult a , Marca, Comentarios)

Sanitarios y sanitaria son el personal de un centro de salud, su profesión es Mecicina, Enfermería,...

Los pacientes visitan al personal sanitario y se anota la visita anotando una fila en la tabla de consultas en la que se indica la fecha (separando el día, el mes y el año) el sanitario y el paciente relacionados con la consulta. Los pacientes son los que pertenecen al centro de salud, pero no tienen porque haber hecho ni una sola consulta.

En cada consulta se pueden recetar una o más recetas.

Con este esquema realizar las siguientes consultas

[1] Mostrar el nombre y nº de la seguridad social de los pacientes que aún no han hecho ni una sola consulta

[2] Mostrar el nombre de los profesionales sanitarios que han tenido consulta con el o la paciente número 205

[3] Pacientes (nombre e identificador) que no han tenido consulta con ningún enfermero o enfermera

[4] Pacientes (nombre e identificador) que han tenido consulta con el personal sanitario número 189 y número230

[5] Pacientes (nombre e identificador) a los que nunca se les ha recetado nada

[6] Pacientes que han ido a consulta todos los meses

[7] Pacientes que no han ido a consulta en todo el año 2005

87


Consultas → CSanitarios → SPacientes → Precetas → R[1] Para sacar los pacientes que no han hecho consultas, hay

que tener en cuenta que dichos pacientes estarán en la tabla de pacientes, pero no en la de consultas. Lo que se realiza:

∏ n º SS

,nombre

P − ∏ n º SS ,nombre ( P∞C )

[2] :

∏ S .nombre ((σ idpaciente = 205 P)∞C∞S ))[3] Parecida a la primera, sólo que tenemos que sacar los

pacientes que han tenido consulta de enfermería y restarles del conjunto total de pacientes:

∏ P.id ,nombre ((σprofesión ="enfermería " S )∞C∞P)

∏ idpaciente ,nombre P − P'

88

[4] La tentación es hacer una selección en la que utilicemos una selección sobre la tabla de consultas usando una condición "OR". Pero no funcionaría ya que queremos sólo los pacientes que han tenido consulta con ambos profesionales.La solución es obtener los que tuvieron consulta con el205. Luego los que la tuvieron con el 108 y hallar la intersección

∏ P.idpaciente ,nombre ((σ 230C )∞P) →

∏ P.idpaciente ,nombre ((σ 198C )∞P) →

P230

P198

P230 IP198[5] Otra vez el mismo juego, seleccionamos los que sí han

tenido recetas y les restamos de los originales

∏ P.idpaciente ,nombre ( P∞C∞R) →

P' '

P − P' '[6] Esta es la más difícil. Necesitamos dos tablas para poder

dividirlas y así obtener este complicado resultado. Una tabla contendrá un solo atributo, los meses del año. Necesitaríamos los 12 meses, para lo cual tendríamos que rellenar esa tabla, pero suponemos que se han consultas todos los meses del año y cogiendo los meses de la tabla de consultas, tendremos todos (la proyección supondremos que nos dará resultados únicos).Luego utilizaremos una tabla en la que aparezca elnúmero de paciente, su nombre y otra columna con el mes de su consulta. Dividiendo nos saldrá el resultado deseado

[7]

∏ mes C→ meses

∏ mes , P.idpaciente ,nombre ( P∞C ) →

P' ' ': meses

P' ' '

UNIDAD 6

BASES DE DATOSTEMA 4. SQL. UN LENGUAJE DE CONSULTA COMERCIAL

PARA BASES DE DATOS RELACIONALES

Contenidos generales

* Definición de datos en SQL* Consulta de datos en SQL

- Estructura básica de una sentencia SQL- Operaciones de conjuntos- Funciones de agregación- Vistas

* Actualización de datos en SQL

Motivación

BD deben facilitar la definición y recuperación de datosLas BDR se encuentran bien establecidasSQL es un lenguaje de consulta estándar para BDR

Definición de datosConsulta de datosActualización de datos

Bases de datos. Tema 4. 2

4.1. Introducción (1)

Algebra relacional como lenguaje de consulta formal procedimental -> Especificar el cómoSGBDRs comerciales ofrecen una interfaz SQL Sentencias SQL en aplicaciones (SQL embebido)

SQL permite definir la base de datos así como consultar y modificar sus datos


4.1. Introducción (2)nombreSuc ciudadSuc Activo nombreEmp dniEmp telefono NombreSucDowntown Brooklyn 9000000 Smith 10 101010 DowntownRedwood Palo Alto 2100000 Kortz 11 111111 Downtown

Sucursales Perrydge Horseneck 1700000 Hansen 12 121212 PerrydgeMianus Horseneck 400000 Dubitzky 13 131313 Perrydge

Round Hill Horseneck 8000000 Henson 14 141414 MianusPownal Bennington 300000 Kravitz 15 151515 Brighton

North Town Rye 3700000Brighton Brooklyn 7100000 Empleados

numeroCta saldo nombreSuc nombreCli dniCli Domicilio1 10000 Downtown Johnson 1 La Reina nº72 20000 Downtown Smith 2 Fragata azul nº8

Cuentas 3 30000 Perrydge Hayes 3 Gibraltar español nº144 40000 Perrydge Turner 4 Gibraltar español nº175 50000 Mianus Williams 5 Diamante S/N6 60000 Brighton Lindsay 6 Gato negro nº13

Green 7 Perro nº1

CtaCli

dniCli numeroCta numeroCta numeroTrans fecha importe Clientes

Transacciones

1 11 22 33 44 55 56 57 6

1 1 10-10 +100002 1 10-10 +300002 2 11-10 -200003 1 12-10 +300004 1 12-10 +400005 1 13-10 +500006 1 13-10 +60000


4.2. Definición de datos en SQL

Definición de datos en SQL: Creación, modificación y eliminación de tablas (relaciones), vistas, índices, ...

Término relacional Término SQL

Tabla Table

Fila Row

Column Column

Crear: CREATE Eliminar: DROP Modificar: ALTER


4.2. Definición de datos en SQL (2)

4.2.1. Esquemas y catálogosEsquemas

* Permiten definir conjuntos de tablas y otros elementos* Disponible desde SQL2* Identificables mediante un nombre* La creación del esquema se puede hacer en un solo

paso (especificando sus elementos). También se puede declarar en esquema y luego incorporarle sus elementos EjemploCREATE SCHEMA BANCO AUTHORIZATION MLOPEZ

Catálogo: Conjunto de esquemas



4.2.2. Creación de tablas, tipos de datos y restriccionesCreación de tablas: CREATE TABLEEjemploi) CREATE TABLE BANCO.CLIENTE ii)CREATE TABLE CLIENTE Definición de una tabla

Nombre de la tablaDeclaración de atributos: Nombre, dominio, restriccionesRestricciones: Clave, integridad referencial, y demás



4.2.2. Creación de tablas, tipos de datos y restriccionesEjemplo de creación de tablasCREATE TABLE CLIENTES

(NOMBRECLI VARCHAR(50) NOT NULL,DNICLI VARCHAR(8) NOT NULL, DOMICILIO VARCHAR(50) NOT NULL, PRIMARY KEY (DNICLI)ON DELETE CASCADE ON UPDATE CASCADE);

CREATE TABLE CUENTAS(NUMEROCTA VARCHAR(10) NOT NULL, SALDO DECIMAL(12,2) NOT NULL, NOMBRESUC VARCHAR(50) NOT NULL, PRIMARY KEY (NUMEROCTA)ON DELETE CASCADE ON UPDATE CASCADE);

Atributos

Restricciones

Atributos

Restricciones



4.2.2. Creación de tablas, tipos de datos y restriccionesEjemplo de creación de tablasCREATE TABLE CTACLI

(DNICLI VARCHAR(8) NOT NULL,NUMEROCTA VARCHAR(20) NOT NULL, PRIMARY KEY (DNICLI, CTACLI), FOREIGN KEY (DNICLI) REFERENCES CLIENTES(DNICLI),FOREIGN KEY (NUMEROCTA) REFERENCES CUENTAS(NUMEROCTA));

Atributos

Restricciones



4.2.3. Tipos de datos y dominios en SQLCaracteres, numéricos, fecha y hora

* Enteros: INTEGER o INT, SMALLINT* Reales: FLOAT, REAL, DOUBLE PRECISION* Cadena de longitud fija: CHAR(n) o CHARACTER(n)* Cadena de longitud variable: VARCHAR(n)* Fecha: DATE Componentes: YEAR, MONTH,DAY

Formato: YYYY-MM-DD* Hora: TIME Componentes: HOUR, MINUTE, SECOND

Formato: HH:MM:SS.



4.2.4. Definición de claves primarias y externas. Especificación de restriccionesDefinición de clave: PRIMARY KEYLos atributos que forman la clave deben ser NOT NULLDefinición de clave externa: FOREIGN KEY ... REFERENCESCREATE TABLE CTACLI

(DNICLI VARCHAR(8) NOT NULL, NUMEROCTA VARCHAR(20) NOT NULL, PRIMARY KEY (DNICLI, CTACLI), FOREIGN KEY (DNICLI) REFERENCES

CLIENTES(DNICLI),FOREIGN KEY (NUMEROCTA) REFERENCES

CUENTAS(NUMEROCTA));



4.2.5. Modificación de tablasModificación de tablas: ALTER TABLE

* Añadir o eliminar columnas* Modificar la definición de una columna* Añadir o eliminar restricciones de columna

Ejemplo (Añadir columna):ALTER TABLE BANCO.CLIENTES ADD CIUDAD VARCHAR(20);Ejemplo (Eliminar columna):i) Propagación en cascada (CASCADE)ii) No eliminar si hay relacionados (RESTRICT)ALTER TABLE BANCO.CLIENTES DROP CIUDADCASCADE;



4.2.6. Eliminación de tablas y esquemas* Eliminación de tablas: DROP TABLE* Eliminación de esquemas: DROP SCHEMA

Si hay elementos relacionados se puede detener el proceso (RESTRICT) o se puede propagar el efecto (CASCADE) Ejemplo:DROP TABLE CLIENTES RESTRICT; DROP SCHEMA BANCO CASCADE;


4.3. Estructura básica de una consulta SQL (1)

Componentes básicos de una sentencia SQL* Cláusula SELECT: Proyección. Atributos deseados* Cláusula FROM: Producto cartesiano. Tablas involucradas* Cláusula WHERE: Selección. Condiciones o predicados

Sentencia SQL típica SELECT A1, A2, ..., An FROM R1, R2, ..., RmWHERE P

equivalente aΠA1, A2, ..., An(σP (R1 x R2 x ... x Rm))

El uso de SELECT * devuelve todos los atributos



Ejemplo: Nombres de todos los clientesSELECT nombreCliFROM clientes;Ejemplo: Nombres de los empleados de DowntownSELECT nombreEmpFROM empleadosWHERE nombreSuc = "Downtown";Eliminación de duplicados

SELECT DISTINCTEjemplo: Nombres diferentes de empleados deDowntownSELECT DISTINCT nombreEmpFROM empleadosWHERE nombreSuc = "Downtown";



Conservación de duplicados (Predeterminado)SELECT ALL

Uso de operadores aritméticosSELECT numeroCta, saldo * 166.386FROM cuentas;


4.4. Predicados y conectores (1)

Uso en la especificación de predicados implícitos (para combinar tablas) y de predicados explícitos.SELECT empleados.nombreEmpFROM sucursales, empleadosWHERE sucursales.nombreSuc = empleados.nombreSuc;Conectores lógicos: AND, OR y NOTEjemplo: Nombres de empleados de la ciudad deBrooklynSELECT empleados.nombreEmpFROM sucursales, empleadosWHERE sucursales.nombreSuc = empleados.nombreSuc

AND sucursales.ciudadSuc = "Brooklyn";


4.4. Predicados y conectores (2)

Operador BETWEENEjemplo: Números de cuenta con saldos comprendidos entre 20000 y 50000SELECT numeroCtaFROM cuentasWHERE saldo BETWEEN 20000 AND 50000;Comparación aproximada: Operador LIKE

Uso de comodines: 1 cárácter _Varios caracteres %

Ejemplo: Nombres de clientes que vivan en cualquier casa de la calle Gibraltar españolSELECT nombreCliFROM clientesWHERE domicilio LIKE "Gibraltar español%";


4.5. Ordenación de tuplas

Ordenación mediante ORDER BYORDER BY va después del WHERE, si procede. Ordenación ascendente de forma predeterminada Ejemplo: Lista alfabética de clientes con cuenta en DowntownSELECT DISTINCT nombreCliFROM clientes, cuentas, ctacliWHERE clientes.dniCli = ctacli.dniCli ANDcuentas.numeroCta = ctacli.numeroCta AND

cuentas.nombreSuc = "Downtown" ORDER BY nombreCli;Ordenación descendente: Uso de DESC después delnombre de columna.


4.6. Creación de alias (1)

Variables de tupla: Permiten la comparación de dos filas de la misma tabla. Renombra a una tablaEjemplo: Empleados que trabajan con SmithSELECT E.nombreEmpFROM Empleados E, Empleados Ebis WHERE Ebis.nombreEmp = "Smith" AND Ebis.nombreSuc = E.nombreSuc;Ejemplo: Sucursales con activo superior al de algunasucursal de HorseneckSELECT E.nombreEmpFROM Sucursales S1, Sucursales S2WHERE S1.ciudadSuc = “Horseneck” ANDS2.Activo > S1.Activo;


4.6. Creación de alias (2)

Para renombrar atributos se usa ASSELECT nombreSuc AS nombreDeSucursalesFROM sucursales;Bastate útil cuando se tienen nombres “complicados” para una columna” o se han realizado operaciones.Ejemplo:SELECT numeroCta, saldo * 166.386 AS SaldoAPesetasFROM cuentas;


4.7. Operaciones de conjuntos (1)

Unión (UNION), Intersección (INTERSECT) y Diferencia(MINUS)Ejemplo: Nombre de todas las personasSELECT nombreEmp FROM empleados UNIONSELECT nombreCliFROM clientes;Ejemplo: Nombres de empleados que coinciden con clientesSELECT nombreEmp FROM empleados INTERSECTSELECT nombreCliFROM clientes;



4.7. Operaciones de conjuntos (2)

Ejemplo: Nombres de empleados que no coinciden con clientesSELECT nombreEmp FROM empleados MINUSSELECT nombreCliFROM clientes;

Estas 3 operaciones eliminan los duplicados de forma predeterminada. Para conservar los duplicados utilizaremos UNION ALL, INTERSECT ALL, y MINUS ALL


4.8. Consultas anidadas y pertenencia a conjuntos (1)

Para realizar una comparación con un conjunto de valores podemos utilizar conjuntos explícitos en lugar de varios OR Ejemplo: Empleados que trabajan en Brighton o DowntownSELECT nombreEmpFROM empleadosWHERE nombreSuc IN ("Downtown", "Brighton");

También podemos utilizar NOT IN para la no pertenencia Otro uso es que los valores se obtengan como resultado de una consulta -> Consultas anidadas


Nombre Apellidos NSS AñoNacimiento Sexo Salario NSSSupervPedro Márquez 1 1954 H 1200 2Isabel Fernández 2 1972 M 1150 3María Yagüe 3 1963 M 2200 nullJuan Martín 4 1971 H 1050 3

Nombre NSSP AñoNacimiento Sexo ParentescoJuana 1 1974 M HijaManuel 1 1976 H Hijo

Margarita 1 1958 M EsposaMaría 3 1993 M HijaLuis 3 1963 H Esposo


Ejemplo: Empleados que trabajan en sucursales de la ciudad de BrooklynSELECT nombreEmpFROM empleadosWHERE nombreSuc IN (SELECT nombreSuc

FROM sucursalesWHERE ciudadSuc = "Brooklyn");

Si hay ambigüedades en los nombres de las columnas se solucionan buscando en la consulta más interna



4.8.1. Consultas correlacionadasSon un tipo especial de consultas anidadasSon consultas en las que la consulta anidada se ejecuta una vez para cada fila de la consulta externaSe consigue cuando en el WHERE de la subconsulta se

hace referencia a columnas de la consulta externa

Personal

Familia



4.8.1. Consultas correlacionadasEjemplo: Nombre y apellidos de trabajadores con el mismo nombre y sexo que sus familiaresSELECT Nombre, ApellidosFROM PersonalWHERE Nombre IN

(SELECT Nombre FROM FamiliaWHERE NSS=NSSP AND

Personal.Nombre = Familia.Nombre AND Personal.Sexo = Familia.Sexo);También se podría hacer combinándolas con el NSS



4.8.2. Función EXISTSPermite comprobar si el resultado de una consulta es vacío Ejemplo: Nombre y apellidos de trabajadores para los que existan familiares con su nombre y su sexoSELECT Nombre, ApellidosFROM PersonalWHERE EXISTS

(SELECT * FROM FamiliaWHERE NSS=NSSP AND

Personal.Nombre = Familia.Nombre AND Personal.Sexo = Familia.Sexo); También se puede utilizar NOT EXISTS

4.9. Comparación de conjuntos

Se usa cuando el predicado supone la comparación con los algunos o todos los elementos de un conjuntoPara ello se utiliza SOME y ALLEjemplo: Sucursales que tienen un activo superior a cualquiera de las sucursales de la ciudad de Horseneck SELECT nombreSucFROM sucursalesWHERE activo > SOME (SELECT activo

FROM sucursalesWHERE ciudadSuc = "Horseneck");

Para comparar con todos los valores se utiliza ALL Ambos se puede combinar con los operadores de comparación, dando lugar a< SOME, <= SOME, >= SOME, <> SOME, = SOME, < ALL, <= ALL, >= ALL, = ALL y <> ALL


4.10. Funciones de agregación (1)

Son funciones aplicadas a grupos de filas•Promedio: AVG•Mínimo: MIN•Máximo: MAX•Total: SUM•Cuenta: COUNTLos grupos se establecen mediante GROUP BYLa cláusula GROUP BY va después de la cláusula WHERELas filas que se agrupan tienen el mismo valor en las columnas por las que se realiza la agrupación



Ejemplo: Activo medio por ciudades escribiríamos lo siguiente:SELECT ciudadSuc, AVG(activo) FROM sucursalesGROUP BY ciudadSuc;Es posible establecer condiciones sobre los grupos. Esto se hace mediante la cláusula HAVINGEjemplo: Activos de las sucursales por ciudades para aquellas en que la media sea superior a 2000000, escribiríamosSELECT ciudadSuc, AVG(activo) FROM sucursalesGROUP BY ciudadSucHAVING AVG(activo) > 2000000;



Ejemplo: Sucursales que tienen más de una cuenta, y luego utilizar esto para obtener en qué ciudad está SELECT ciudadSucFROM Sucursales WHERE nombreSuc IN (SELECT nombreSuc FROM CuentasGROUP BY nombreSucHAVING COUNT(numeroCta) > 1);

Ejemplo: Número de ciudades en los que el banco tiene sucursalesSELECT COUNT (DISTINCT ciudadSuc) FROM Sucursales


4.11. Vistas (1)

Permiten la personalización de la informaciónTablas derivadas a partir de otras (pueden ser vistas a su vez)Son tablas virtuales: No tienen por que estar almacenadas físicamentePresentan algunos problemas en la actualizaciónDefinidas con CREATE VIEWEjemplo: Vista que contiene el nombre, teléfono y la ciudad en la que trabajan cada uno de los empleados CREATE VIEW EmpleCiudadAS SELECT nombreEmp, telefono, ciudadSuc

FROM Empleados, SucursalesWHERE Empleados.nombreSuc = Sucursales.nombreSuc;


4.11. Vistas (2)

Vistas con especificación del nombre de las columnas Ejemplo: Vista con el DNI de cada cliente y el saldo total de sus cuentasCREATE VIEW ClientesSumaSaldo (DNICli, SaldoTotal)AS SELECT DNICli, SUM(Saldo)

FROM Clientes, CtaCli, CuentasWHERE Clientes.DNICli = CtaCli.DNICli and

CtaCli.numeroCta = Cuentas.numeroCtaGROUP BY Clientes.DNICli;

Eliminación de vistas con DROP VIEWEjemplo: Eliminación de la vista ClientesSumaSaldoDROP VIEW ClientesSumaSaldo;


4.12. Operaciones de modificación en SQL (1)

4.12.1. Inserción (INSERT) Insertar un solo registro Sintaxis:INSERT INTO nombreTabla VALUES (valor1, valor2, ...);Ejemplo:INSERT INTO EmpleadosVALUES (“Harry”, “16”, “161616”, “Brighton”);

Inserción de filas incompletasINSERT INTO Empleados (nombreEmp, dniEmp, nombreSuc) VALUES (“Mukos”, “17”, “Brighton”);

Insertar el resultado de una consultaSintaxis:INSERT INTO nombreTabla ExpresionSELECT;



4.12.2. Eliminación (DELETE)Eliminar de una tabla las filas que cumplen una condiciónSintaxis:DELETEFROM TablaWHERE Condicion;Ejemplo:DELETEFROM EmpleadosWHERE nombreEmp = “Mukos”;



4.12.3. Actualización (UPDATE)Actualizar en una tabla las filas que cumplen una condiciónSintaxis:UPDATE tablaSET ModificacionWHERE CondicionEjemplo: Incrementar en un 10 por ciento el saldo de las cuentas de sucursales de la ciudad de HorseneckUPDATE CuentasSET saldo = saldo * 1.1WHERE nombreSuc IN (SELECT nombreSuc

FROM SucursalesWHERE ciudadSuc = “Horseneck”);


4.13. Otras formas de combinación de relaciones (1)

Hasta ahora, uso de FROM para indicar producto cartesiano4.13.1. INNER JOINForma compacta de combinar relacionesSintaxis:Tabla1 INNER JOIN Tabla2 ON condicionEjemplo: Nombre de los empleados y la ciudad en la que trabajanSELECT nombreEmp, ciudadSucFROM Empleados INNER JOIN Sucursales ON Empleados.nombreSuc = Sucursales.nombreSuc;



4.13.1. INNER JOINEjemplo: Clientes y saldos de cada una de sus cuentasSELECT nombreCli, saldo FROM Cuentas INNER JOIN (Clientes INNER JOIN CtaCli ON

Clientes.dniCli = CtaCli.dniCli) ON Cuentas.numeroCta = CtaCli.numeroCta;



4.13.2. NATURAL INNER JOINEvita especificar la condición de joinRealiza la combinación basándose en columnas comunesEjemplo: Empleados y ciudad en la que trabajanSELECT nombreEmp, ciudadSucFROM Empleados NATURAL INNER JOIN Sucursales;



4.13.3. Reuniones externasRelajan la condición de join respecto al INNER JOIN Permiten combinar registros de una tabla que no tengan registros relaciones en otra•Reunión externa izquierda: LEFT OUTER JOIN

Permite registos de la tabla izquierda que no tengan registros relacionados en la tabla derecha•Reunión externa derecha: RIGHT OUTER JOIN

Permite registos de la tabla derecha que no tengan registros relacionados en la tabla izquierda•Reunión externa completa: FULL OUTER JOIN

Devuelve los registros de ambas tablas aunque no tengan registros relacionados



4.13.3. Reuniones externasEjemplo: Reunión externa izquierda de Sucursales deHorseneck con EmpleadosSELECT Sucursales.nombreSuc, nombreEmp

FROM Sucursales LEFT OUTER JOIN Empleados ON Sucursales.nombreSuc = Empleados.nombreSuc WHERE ciudadSuc = “Horseneck”;

Sucursales.nombreSuc nombreEmp Perrydge Hansen Perrydge Dubitzky

Mianus HensonRound Hill Null

Ejemplo: Reunión externa de Sucursales con EmpleadosSELECT Sucursales.nombreSuc, nombreEmpFROM Sucursales FULL OUTER JOIN Empleados ON Sucursales.nombreSuc = Empleados.nombreSuc;


CONSULTAS ANIDADAS

2.4 Manipulación de datos: SQL-92

Tema 2. Modelo relacional

Consultas anidadas Es una consulta SELECT completa, dentro de

cláusula WHERE de otra consulta (consulta exterior) Obtiene valores de la BD que se usan en la condición de

otra consulta, para obtener otros datos* Números de los proyectos en que participa el empleado de apellido ‘Silva’, sea como trabajador ocomo gerente del departamento que controla el proyecto

SELECT DISTINCT numerop FROM PROYECTOWHERE numerop IN ( SELECT nump

FROM Trabaja_en, EmpleadoWHERE nsse=nss AND apellido=‘Silva’ )

OR numerop IN ( SELECT numeropFROM Proyecto, Departamento, EmpleadoWHERE numd=númerod

AND nssdire=nss AND apellido=‘Silva’ ) ;



Es posible tener varios niveles de consultas anidadas



Consultas anidadas (2): comparar conjuntos Operador IN (otro uso del mismo

operador)t IN Sindica si la fila t pertenece al conjunto de filas S (subconsulta)

* Nombre y dirección de los empleados que trabajan en algún proyecto.SELECT nombre, dirección FROM EmpleadoWHERE nss IN ( SELECT nsse FROM TRABAJA_EN );

* Números de seguridad social de aquellos empleados que trabajan en algún proyecto en el que trabaje el empleado ‘José B. Silva’, de forma que ambos tengan la misma combinación (proyecto, horas); es decir, todo empleado que trabaje las mismas horas que ‘José B. Silva’, en cada proyecto en el que trabajen ambos. El nss de ‘José B. Silva’ es ‘123456789’.

SELECT DISTINCT nsse FROM Trabaja_en



WHERE (númp, horas) IN ( SELECT númp, horasFROM Trabaja_enWHERE nsse=‘123456789’);

2.4 Manipulación de datos: SQL-92 Consultas anidadas (3): comparar

conjuntos Operador ANY o SOME (otro uso del mismo operador)

t <op> ANY S o t <op> SOME S,, <op> { , , , , , } Compara una fila t con las filas resultado de una consultaanidada S Devuelve TRUE si alguna fila e de S cumple que t <op> e

Operador ALL (otro uso del mismo operador)t <op> ALL S,, <op> { , , , , , } Compara una fila t con filas resultado de una consulta anidada

S Devuelve TRUE si para toda fila e de S se cumple que t <op> e

2.4 Manipulación de datos: SQL-92 * Nombres y apellidos de los empleados cuyo salario es menor que el de todos los empleados del departamento 5SELECT nombre, apellido FROM EmpleadoWHERE salario < ALL ( SELECT salario

FROM EmpleadoWHTEemRaE2. Mnodd=el5o r)e;lacional de datos

¿”Mejor” con DISTINCT

en la subconsulta?


2.4 Manipulación de datos: SQL-92 Consultas anidadas (4): columnas

ambiguas Coincidencia de nombres de columnas en las

consultas exterior y anidada Ambigüedad* Nombre y apellidos de cada empleado con familiares de igual nombre y sexo que élSELECT nombre, apellido FROM EmpleadoWHERE nss IN ( SELECT nsse FROM Familiar

WHERE nsse=nss AND nombre_familiar=nombreAND sexo=sexo ); ¿cómo evitar esta ambigüedad?

Regla: Una columna no calificada se refiere a la tabla declarada

en la consulta anidada más interior Si en una consulta anidada es necesario usar columnas de tablas

declaradas en una consulta exterior calificar* Nombre y apellidos de cada empleado con familiares de igual nombre y sexo que élSELECT nombre, apellido FROM Empleado E


2.4 Manipulación de datos: SQL-92 WHERE nss IN ( SELECT nsse FROM Familiar

WHERE nss=nsse AND nombre_familiar=nombreAND sexo= E.sexo );



Consultas anidadas (5): correlación

Una consulta exterior y otra anidada están correlacionadas si una condición de la anidada contiene columnas de una tabla declarada en la consulta exteriorSELECT nombre, apellido FROM EmpleadoWHERE nss IN ( SELECT nsse FROM Familiar

WHERE nss=nsse AND sexo=‘F’ );

La consulta anidada se evalúa una vez para cada fila (o

combinación de filas) de la consulta exterior Evalúa la consulta anidada para cada fila de EMPLEADO, Si el valor de nss de la fila EMPLEADO está en el resultado de la

consulta anidada, selecciona la fila EMPLEADO para el resultado final



Una consulta anidada que use el operador = o IN siempre puede

expresarse como una reunión (join)SELECT E.nombre, E.apellido FROM Empleado, Familiar D WHERE nss=nsse AND D.sexo=‘F’;



Consultas anidadas (6): EXISTS

Operador EXISTS (S): comprobación de tablas vacías

Devuelve TRUE si la tabla S contiene al menos una filaDevuelve FALSE si S es una tabla vacía (sin filas)

S suele ser una consulta anidada correlacionada

* Nombre y apellido de cada empleado con familiares de igual nombre y sexo que élSELECT E.nombre, E.apellido FROM Empleado EWHERE EXISTS ( SELECT * FROM Familiar

WHERE nsse=nss AND nombre_familiar=nombreAND sexo=E.sexo );

* Nombres de empleados sin familiaresSELECT nombre, apellido FROM Empleado EWHERE NOT EXISTS (SELECT * FROM Familiar WHERE nsse=nss);

Base de datos II: CONSULTAS COMPLEJAS

http://base-de-datos-manueljorge.blogspot.mx/2011/07/consultas-compl...

1 de 4 29/05/2013 04:19 p.m.

Consultas anidadas (y 7): UNIQUE

Operador UNIQUE (S): Comprobación de filas duplicadas

Devuelve TRUE si NO hay filas repetidas en S S suele ser una consulta anidada correlacionada

* Nombres y apellidos de los empleados que trabajan en un único proyecto

SELECT nombre, apellido FROM Empleado

WHERE UNIQUE ( SELECT nsseFROM Trabaja_enWHERE nsse = nss );

* Nombres, apellidos y salario de los empleados con un solo familiar

SELECT nombre, apellido, salario FROM EmpleadoWHERE UNIQUE ( SELECT *

FROM FamiliarWHERE nsse = nss );



2 de 4 29/05/2013 04:19 p.m.

Contenido temático de las bases de datos, centrados en Oracle

te realizar esta operación es el operador UNION.

La composición de tablas

CONSULTAS COMPLEJAS

El SQL soporta dos grupos de consultas multitabla:

- la unión de tablas.

- la composición de tablas.La unión de tablas

Esta operación se utiliza cuando tenemos dos tablas con las mismas columnas y queremos obtener una nueva tabla con las filas de la primera y las filas de la segunda. En este caso la tabla resultante tiene las mismas columnas que la primera tabla (que son las mismas que las de la segunda tabla).

Cuando hablamos de tablas pueden ser tablas reales almacenadas en la base de datos o tablas lógicas (resultados de una consulta), esto nos permite utilizar la operación con más frecuencia ya que pocas veces tenemos en una base de datos tablas idénticas en cuanto a columnas. El resultado es siempre una tabla lógica.

Por ejemplo queremos en un sólo listado los productos cuyas existencias sean iguales a cero y también los productos que aparecen en pedidos del año 90. En este caso tenemos unos productos en la tabla de productos y los otros en la tabla de pedidos, las tablas no tienen las mismascolumnas no se puede hacer una union de ellas pero lo que interesa realmente es el identificador del producto (idfab,idproducto), luego por una parte sacamos los códigos de los productos con existencias cero (con una consulta), por otra parte los códigos de los productos que aparecen en pedidos del año 90 (con otra consulta), y luego unimos estas dos tablas lógicas.

El operador que permi

La composición de tablas consiste en concatenar filas de una tabla con filas de otra. En este caso obtenemos una tabla con las columnas de la primera tabla unidas a las columnas de la



3 de 4 29/05/2013 04:19 p.m.

n la composición permite obtener una fila con datos de la

segunda tabla, y las filas de la tabla resultante son concatenaciones de filas de la primera tabla con filas de la segunda tabla.

El ejemplo anterior qu

A diferencia de la unió s dos tablas, esto es muy útil cuando queremos visualizar filas cuyos datos se encuentran en dos tablas.



4 de 4 29/05/2013 04:19 p.m.

La sintaxis del LEFT JOIN es la siguiente:

Esta operación consiste en añadir al resultado del INNER JOIN las filas de la tabla de

La sintaxis del RIGHT JOIN es la siguiente:

*Se pueden definir varias condiciones de emparejamiento unidas por los operadores AND y ORponiendo cada condición entre paréntesis. Ejemplo:

SELECT *FROM pedidos INNER JOIN productos ON (pedidos.fab = productos.idfab) AND (pedidos.producto = productos.idproducto);

El LEFT / RIGHT JOIN

El LEFT JOIN y RIGHT JOIN son otro tipo de composición de tablas, también denominadacomposición externa. Son una extensión del INNER JOIN.

Las composiciones vistas hasta ahora (el producto cartesiano y el INNER JOIN) son composiciones internas ya que todos los valores de las filas del resultado son valores que están en las tablas que se combinan.

la izquierda que no tienen correspondencia en la otra tabla, y rellenar en esas filas los campos de la tabla de la derecha con valores nulos.

Ejemplo:

SELECT *FROM empleados LEFT JOIN oficinas ON empleados.oficina = oficinas.oficina;

Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y el empleado 110 que no tiene oficina aparece con sus datos normales y los datos de su oficina a nulos.

Esta operación consiste en añadir al resultado del INNER JOIN las filasde la tabla de la derecha que no tienen correspondencia en la otra tabla, y rellenar en esas filas los campos de la tabla de la izquierda con valores nulos.

Ejemplo:

SELECT *FROM empleados RIGHT JOIN oficinas ON empleados.oficina = oficinas.oficina;

Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y además aparece una fila por cada oficina que no está asignada a ningún empleado con los datos del empleado a nulos.

Una operación LEFT JOIN o RIGHT JOIN se puede anidar dentro de una operación INNER JOIN, pero una operación INNER JOIN no se puede anidar dentro de LEFT JOIN o RIGHT JOIN.

EJEMPLOS PRÁCTICOS SQL


INTEGRIDAD REFERENCIAL

DROP SCHEMA IF EXISTS Tablas1; CREATE SCHEMA Tablas1;USE Tablas1;

create table Cliente(Dni VARCHAR(10), Nombre VARCHAR(10), Apellido VARCHAR(10),

PRIMARY KEY (Dni))ENGINE=InnoDB;

create table Pedidos(npedido INTEGER, fecha DATE, Cantidad DOUBLE,cliente_Dni VARCHAR(10), PRIMARY KEY (npedido),FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni)

)ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez');

INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');

TABLA: Cliente

TABLA: Pedidos

Esto es un ejemplo de intento de borrado de una tupla que tiene un campo que aparece como clave foránea en otra tabla. Por omision se aplica la condiciónde restrict y no se puede borrar hasta que no borre la tupla de la tabla hija.

Esto es un ejemplo de intento de actualización de una tupla que tiene un campo que aparece como clave foránea en otra tabla. Por omision se aplica la condiciónde restrict y no se puede actualizar.

cliente Dni VARCHAR(10)




create table Pedidos(npedido INTEGER, fecha DATE, Cantidad DOUBLE,cliente_Dni VARCHAR(10), PRIMARY KEY (npedido),FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE CASCADE ON UPDATE CASCADE)ENGINE=InnoDB;



TABLA: Cliente

TABLA: Pedidos

Para probar la restricción ON UPDATE CASCADE … actualizamos el valor de un DNI. El resultado debe de ser que se actualiza la tabla padre y la hija

Se observa que se han actualizado las tablas padre e hija

Si ahora borramos una tupla de la tabla padre se borra la tupla correspondiente de la tabla hija.




create table Pedidos(npedido INTEGER, fecha DATE, Cantidad DOUBLE,cliente_Dni VARCHAR(10), PRIMARY KEY (npedido),FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB;



La restricción más adecuada en la mayoría de los casos esevitar realizar borrados en cascada y sin embargo siactualizar en cascada

Observamos que la actualización en cascada ha funcionado

A diferencia del ejemplo de la Tablas2 ahora no se puedeborrar una tupla de la tabla padre por la restricción ON DELETE RESTRICT.

Aunque es en la tabla hija donde se escribe la restricción de la clave foráneaNótese una cuestión que a veces es causa de confusión.Aunque es en la tabla hija donde se escribe la restricción de la clave foránea, es decir, que un atributo depende de la clave de otra tabla….

Se puede borrar sin ningún problema una tupla de una tabla que contiene una clave foránea sin afectar a la tupla correspondiente de la tabla padre… En la figura se ve el ejemplo de borrar una fila en la tabla de pedidos.

la tabla de clientes está intactaComo se ve en la figura la tabla de pedidos está vacía y la tabla de clientes está intacta.

)ENGINE=InnoDB;

INSERT INTO P did VALUES ('122' '2010/01/0 ' ' 211 4 ')




create table Pedidos(npedido INTEGER, fecha DATE, Cantidad DOUBLE,cliente_Dni VARCHAR(10), PRIMARY KEY (npedido),FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE RESTRICT ON UPDATE

CASCADE

)ENGINE=InnoDB;



Aquí mostramos el ejemplo de intentar incorporar una fila nueva de pedidos de un cliente que no existe en la tabla de clientes. Las reglas de integridad referencial nos lo impiden

Insertamos ahora dos nuevos clientes……..

El resultado lo observamos en la figura ……

Ahora si que podemos incorporar una tupla nueva de pedido con el cliente incorporado en la tabla de clientes


MODIFICACIÓN TABLAS

INSERT INTO Pedidos VALUES (' ' '2010/01/05' '7211545v'




create table Pedidos(npedido INTEGER, fecha DATE, Cantidad DOUBLE,cliente_Dni VARCHAR(10), PRIMARY KEY (npedido),FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE RESTRICT ON UPDATE CASCADE

)ENGINE=InnoDB;


INSERT INTO Pedidos VALUES ( 122 , 2010/01/05 ,7, 7211545v );

Agregamos una columna a la tabla con ALTER …ADD

Cambiamos el nombre de una columna a la tabla con ALTER …CHANGE

Modificamos el tipo de datos de una columna a la tabla con ALTER …MODIFY

Eliminamos una columna a la tabla con ALTER …DROP

Estamos tratando de borrar una tabla padre. Las restricciones de la BDno nos permite al tener asociada la tabla una clave ajena

Es donde se haya la clave ajenaLa tabla hija “pedidos” se puede borrar sin problemas. Es donde se haya la clave ajena


INSERCIÓN DATOS ENTABLAS

create table Cliente(


create table Cliente(Dni VARCHAR(10), Nombre VARCHAR(10), Apellido VARCHAR(40), PRIMARY KEY (Dni))ENGINE=InnoDB;

create table Pedidos(npedido INTEGER, fecha DATE, Cantidad DOUBLE,cliente_Dni VARCHAR(10), PRIMARY KEY (npedido),FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON DELETE

RESTRICT ON UPDATE CASCADE)ENGINE=InnoDB;

create table Personal(Dni VARCHAR(10), Nombre VARCHAR(10), Apellido VARCHAR(40), PRIMARY KEY (Dni))ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez'); INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v'); INSERT INTO Personal VALUES ('7211541v','Juan','Garzón Rodriguez'); INSERT INTO Personal VALUES ('7211542v','Antonio','Marina Esquivel'); INSERT INTO Personal VALUES ('7211543v','Cesar','Bernal SanJose'); INSERT INTO Personal VALUES ('7211544v','Rodrigo','Alonso Vera'); INSERT INTO Personal VALUES ('7211546v','Maria','Lopez Gomez');

TABLA: Cliente

TABLA: Pedidos

TABLA: Personal

Con este comando que es un mezcla de INSERT INTO y SELECT conseguimos meter en la tabla cliente 5 tuplas


ACTUALIZACIÓN DATOSEN TABLAS

create table Cliente(


create table Cliente(Dni VARCHAR(10), Nombre VARCHAR(10), Apellido VARCHAR(40), PRIMARY KEY (Dni))ENGINE=InnoDB;

create table Pedidos(npedido INTEGER, fecha DATE, Cantidad DOUBLE,cliente_Dni VARCHAR(10), PRIMARY KEY (npedido),FOREIGN KEY (cliente_Dni) REFERENCES Cliente(Dni) ON

DELETE RESTRICT ON UPDATE CASCADE)ENGINE=InnoDB;

create table Personal(Dni VARCHAR(10), Nombre VARCHAR(10), Apellido VARCHAR(40), PRIMARY KEY (Dni))ENGINE=InnoDB;

INSERT INTO Cliente VALUES ('7211545v','Carlos','Martinez Lopez'); INSERT INTO Pedidos VALUES ('122','2010/01/05',7,'7211545v');

INSERT INTO Cliente VALUES ('7211541v','Juan','Garzón Rodriguez'); INSERT INTO Cliente VALUES ('7211542v','Antonio','Marina Esquivel'); INSERT INTO Cliente VALUES ('7211543v','Cesar','Bernal SanJose'); INSERT INTO Cliente VALUES ('7211544v','Maria','Alonso Vera'); INSERT INTO Cliente VALUES ('7211546v','Maria','Lopez Gomez');

Vamos a probar el comando actualizar de SQL.Para ello actualizamos todas las tuplas en la que aparece Maria

l b l l C i DNIEn este ejemplo vemos como es imposible actualizar los dos registros en los que aparece en nombre el valor Carmen a un mismo DNIya que DNI tiene la restricción de clave primaria


CONSULTASBÁSICAS CON SELECT

DROP SCHEMA IF EXISTS SELECT1; CREATE SCHEMA SELECT1;USE SELECT1;

create table Información_Ventas( Tienda VARCHAR(10), Ventas INTEGER, Fecha DATE)ENGINE=InnoDB;

create table Zona_Ventas(Región VARCHAR(10), Tienda VARCHAR(10))ENGINE=InnoDB;

INSERT INTO Información_Ventas VALUES ('Madrid',1500,'2010/01/05'); INSERT INTO Información_Ventas VALUES ('Sevilla',250,'2010/01/08'); INSERT INTO Información_Ventas VALUES ('Madrid',300,'2010/01/07'); INSERT INTO Información_Ventas VALUES ('Barcelona',700,'2010/01/08');

INSERT INTO Zona_Ventas VALUES ('Centro-Sur','Madrid'); INSERT INTO Zona_Ventas VALUES ('Centro-Sur','Sevilla'); INSERT INTO Zona_Ventas VALUES ('Noreste','Zaragoza'); INSERT INTO Zona_Ventas VALUES ('Noreste','Barcelona');

TABLA: Informacion_ventas

TABLA:Zona_Ventas

Tabla: Información_Ventas

Selección Básica:

Seleccionamos el nombre de todas las tiendas


Selección Básica:

De esta forma eliminamos los duplicados que genera la consulta SELECT


Selección Condicional Simple:

Tiendas cuyas ventas son mayores de 1000

INSERT INTO Z V t VALUES ('C t




INSERT INTO Información_Ventas VALUES ('Madrid',1500,'2010/01/05'); INSERT INTO Información_Ventas VALUES ('Sevilla',250,'2010/01/08'); INSERT INTO Información_Ventas VALUES ('Cuenca',300,'2010/01/07'); INSERT INTO Información_Ventas VALUES ('Barcelona',700,'2010/01/08');



Selección Condicional Simple Compuesta:

Tiendas cuyas ventas son mayores de 1000 ó las ventas están comprendidas entre 275 y 500


Selección Tuplas en Función de Valores (IN):

Información completa de lasTiendas de “Madrid” y “Sevilla”


Selección Búsqueda de Patrones (LIKE):

Seleccion de tuplas que contengan un patrón


Selección Ordenación de Resultados (ORDER BY):

Selección de Tiendas ordenadas por el volumen de ventas enOrden descendente


Selección Ordenación de Resultados (ORDER BY):En este ejemplo se clasifican las tuplas en primer lugar en orden descendente del campo Ventas y luego si hay tuplas del mismo valor por orden descentente de fecha

S l ió F i A it éti

( i t l l h ) bt i d l i ( t bl )


Selección Funciones Aritméticas:

Seleccionar todas las tuplas de la tabla “Información_Ventas’(ya que no existe clausula when) , obteniendo una relacion(una tabla). Luego proyectamos por el campo Ventas y hacemos una operación.


Selección Funciones Aritméticas:

Número de Tuplas de una tabla

S l ió F i A it éti G


Selección Funciones Aritméticas con Grupos:

Ventas agrupadas por tiendas


Selección Funciones Aritméticas con Grupos:

Ventas agrupadas por tiendas de las tiendas cuyas ventasSon superiores a 1500


Selección con Grupos:

Esta consulta no tendría mucho sentido ya en un grupo la fecha de cada tupla es diferente

La condición que aplica HAVING tiene que ser un operadorque abarque a todos los miembros del grupo. Si se refiere a uncampo que puede ser diferente en los miembros del grupo no funciona

Tiendas y suma de ventas agrupadas por iguales valores de tienda y ventas


Suma de las ventas totales de las tiendas agrupadas por el nombre de Madrid


Máximo de ventas de las tiendas agrupadas por el nombre


JOIN NATURAL CON SELECT

V t INTEGER

INSERT INTO I f ió V VA UES ('M d id' 1 00 '2010/01/0 ')

');

DROP SCHEMA IF EXISTS JOIN1; CREATE SCHEMA JOIN1;USE JOIN1;create table Información_Ventas(

Tienda VARCHAR(10), Ventas INTEGER, Fecha DATE)ENGINE=InnoDB;




TABLA: Informacion_ventasTABLA:Zona_Ventas

Join Natural: Ventas

por Regiones


CONSULTAS ANIDADAS

Consulta Anidada:

TABLA:Zona_Ventas TABLA: Informacion_ventas

Consulta Anidada:

Ventas de todas las tiendas de la región Noreste

Consulta join equivalente a anidada:

Ventas de todas las tiendas de la región Noreste

INSERT INTO Z V VALUES ('N ' 'B l ')

DROP SCHEMA IF EXISTS CASE1; CREATE SCHEMA CASE1;USE CASE1;





Utilización comando case:

Modificación de la columna Ventas en función del valor

INSERT INTO V i d VA UES ('B l ' 1 00 '2010/01/08')

DROP SCHEMA IF EXISTS UNION1; CREATE SCHEMA UNION1;USE UNION1;

create table Ventas_tienda( Tienda VARCHAR(10), Ventas INTEGER, Fecha DATE)ENGINE=InnoDB;

create table Ventas_Internet( Fecha DATE, Ventas INTEGER)ENGINE=InnoDB;

INSERT INTO Ventas_tienda VALUES ('Madrid',1500,'2010/01/05'); INSERT INTO Ventas_tienda VALUES ('Sevilla',250,'2010/01/08'); INSERT INTO Ventas_tienda VALUES ('Madrid',300,'2010/01/07'); INSERT INTO Ventas_tienda VALUES ('Barcelona',1500,'2010/01/08');

INSERT INTO Ventas_Internet VALUES ('2010/01/07',250); INSERT INTO Ventas_Internet VALUES ('2010/01/10',535); INSERT INTO Ventas_Internet VALUES ('2010/01/11',320); INSERT INTO Ventas_Internet VALUES ('2010/01/12',750);

83 de 4

29/05/2013 04:19 p.m.

84 de 4

29/05/2013 04:19 p.m.

EJEMPLOS

Ejemplo completo: Empleados.

• Diagrama entidad relación:

nombreP

apellido1

apellido2

dni nombre

sueldonumDept

nombreDept

lugares

supervisorempleados

pertenecia n 1 departamentos

supervisado

1 n dirige1

fecha1

controlasupervisa n

n

trabaja enm

nproyectos

hijos

nombre fecha

numHorasnumP

nombre

lugar

85 de 4

29/05/2013 04:19 p.m.

• Modelo relación

empleados

dni nombre apellido1 appellido2 sueldo numD dniSupervisor

departamentos

nu m D ept nombreDept dniJefe fecha

proyectos

nu m P nombre lugar numDept

trabajaEn

dni nu m P numH

hijos

dni nombre fecha

lugaresDpto

Consultas:nu m D lugar

1. Ver todos los datos de la tabla empleados.2. Seleccionar todos los datos de los empleados del depto 5.3. Nombre y apellidos de los empleados que trabajan en el depto 5 y que tienen sueldo >100.000€.4. Nombre y apellidos de los empleados que trabajan en el depto 1, 2 ó 3.5. Nombre y apellidos empleados que trabajan en el departamento: "investigación”.6. Nombre de los empleados con al menos dos hijos.7. Para cada empleado su nombre y el nombre del supervisor.8. Para cada proyecto número de proyecto, nombre y número de empleados que trabajan en él.9. Empleados que tienen el mismo sueldo y trabajan en el mismo departamento que algún “garcia”.10. Número de proyecto en que trabaja “garcia” como jefe de proyecto.11. Nombre y apellido de los empleados con algún hijo.12. Nombre y apellido de los empleados sin hijos.13. Nombre y apellido de jefes de departamento con al menos un hijo.14. Nombre y apellido de los empleados que trabajan en todos los proyectos controlados por el

departamento 5.15. Empleados que no tiene supervisor.16. Nombre y apellido de los empleados con al menos dos hijos.17. Para cada proyecto: número de proyecto, nombre y número de empleados que trabajan en él.18. Para cada departamento con más de tres empleados número de departamento y número de empleados

con sueldo mayor a 100.000€.

86 de 4

29/05/2013 04:19 p.m.

• Pasar a SQL SQL:

Lenguaje de definición de datosLenguaje de definicion de almacenamiento Lenguaje de manipulacion de datos Lenguaje de definicion de vistas

mysql> source filenamemysql> \. filename

empleados.sql-- LENGUAJE DE DEFINICION DE DATOS:---- Tipos de datos-- Cadenas de caracteres-- longitud fija char(m) 1..255 rellena con blancos-- long varibale varchar(m)-- TINYBLOB 255-- TEXT 65,535 char string-- BLOB[(M)] 65,535 binary string-- MEDIUMBLOB 16,777,215-- MEDIUMBLOB 16,777,215-- LONGTEXT 4,294,967,295-- LONGTEXT 4,294,967,295-- ENUM('value1','value2',...)-- SET('value1','value2',...)---- Numericos-- enteros-- int o integer, unsigned-- TINYINT[(M)] [UNSIGNED] [ZEROFILL] -127 a 128-- SMALLINT[(M)] [UNSIGNED] [ZEROFILL] -32768 to 32767-- MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] -8388608 to 8388607-- INT[(M)] [UNSIGNED] [ZEROFILL] -2147483648 to 2147483647-- BIGINT[(M)] [UNSIGNED] [ZEROFILL] -9223372036854775808 to9223372036854775807-- reales-- float[(m,d)]-- (4,2) 4 espacios a lo sumo 2 son decimales-- EJ-- 42.35 bien-- 325.54 mal redondea a 325.5-- FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38-- DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] -1.7976931348623157E+308 to-2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308-- DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]-- BOOL, BOOLEAN------ DATE '1000-01-01' to '9999-12-31'

87 de 4

29/05/2013 04:19 p.m.

-- yyyy-mm-dd-- 1999-02-03 o 19990203-- DATETIME '1000-01-01 00:00:00' to '9999-12-31 23:59:59'-- 'YYYY-MM-DD HH:MM:SS'-- TIME '-838:59:59' to '838:59:59'-- hh:mm:ss-- 12:45:30-- NULL pertenece a todos los tipos de datos en fichero \N---- Modificadores:-- AUTO_INCREMENT-- DEFAULT value-- NOT NULL-- PRIMARY KEY-- UNIQUE-- ON DELETE action1 ON UPDATE action2-- action:-- SET NULL, SET DEFAULT, CASCADE-- CASCADE se eliminan todos las tuplas de empleados que hagan referencia al departamento-- y se continua en cascada.---- Databases:-- CREATE DATABASE nombre;-- USE nombreDB;-- GRANT ALL PRIVLILEGES ON nombreDB.* TO 'albertoe'@'%' IDENTIFIED BY 'clave';---- Tablas:-- CREATE TABLE nombre(listaCampos);---- Creacion de tipos de datos:-- CREATE DOMAIN tipoDNI AS char(8);---- Modificación de tablas:-- ALTER TABLE empleados ADD peso int1 DEFAULT 0;-- ALTER TABLE empleados ADD (peso int1 DEFAULT 0,años int);-- ALTER TABLE empleados ALTER peso DEFAULT 1;-- ALTER TABLE empleados DROP dniSupervisor;---- Borrado:-- DROP DATABASE empleadosDB;-- DROP TABLE departamentos RESTRICT; solo si ninguna otra hace referencia-- DROP TABLE departamentos CASCADE; en cascada todas las que hacen referencia---- Mostrar:-- SHOW DATABASES;-- SHOW TABLES;-- DESCRIBE nombreTabla;-- SHOW COLUMNS FROM nombreTabla;

88 de 4

29/05/2013 04:19 p.m.

CREATE DATABASE empleadosDB;

USE empleadosDB;

CREATE TABLE empleados(dni char(8), nombre varchar(20), apellido1 varchar(20), apellido2 varchar(20), sueldo float(12,2),numD int UNSIGNED, dniSupervisor char(8), PRIMARY KEY(dni)

-- ,FOREIGN KEY(dniSupervisor) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE CASCADE

);CREATE TABLE

departamentos( numDeptint UNSIGNED,

nombreDept varchar(20), dniJefe char(8),fecha date,PRIMARY KEY(numDept),FOREIGN KEY(dniJefe) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE

CASCADE);

CREATE TABLE proyectos( numP int UNSIGNED, nombre

varchar(20), lugarvarchar(10),

numDept int UNSIGNED, PRIMARY KEY(numP),FOREIGN KEY(numDept) REFERENCES departamentos(numDept) ON DELETE CASCADE ON

UPDATE CASCADE);

CREATE TABLE trabajaEn(dni char(8),numP int UNSIGNED, numH int UNSIGNED, PRIMARY KEY(dni,numP),FOREIGN KEY(dni) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE

CASCADE,FOREIGN KEY(numP) REFERENCES proyectos(numP) ON DELETE CASCADE ON UPDATE

CASCADE);

CREATE TABLE hijos( dnichar(8), nombre varchar(20), fecha date,PRIMARY KEY(dni,nombre),FOREIGN KEY(dni) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE CASCADE

89 de 4

29/05/2013 04:19 p.m.

);CREATE TABLE

lugaresDpto( numDint UNSIGNED, lugarvarchar(20),

PRIMARY KEY(numD,lugar),FOREIGN KEY(numD) REFERENCES departamentos(numDept) ON DELETE CASCADE ON

UPDATE CASCADE);

90 de 4

29/05/2013 04:19 p.m.

loadEmpleados.sql-- LENGUAJE DE ALMACENAMIENTO---- Carga de ficheros:-- LOAD DATA LOCAL INFILE ".\\nombreFich" INTO TABLE nombreTabla;-- LOAD DATA LOCAL INFILE ".\\nombreFich" INTO TABLE nombreTabla-- LINES TERMINATED BY '\r\n';---- Insercion en tablas:-- INSERT INTO-- INSERT INTO pet VALUES-- ('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL);-- INSERT INTO pet VALUES-- ('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL),-- ('Moo, 'Harold', 'cat', 'f', '1993-02-04', NULL);---- Actualizacion de datos:-- UPDATE test SET-- neme='busbunny'-- WHERE name='patito';-- UPDATE empleados SET-- sueldo=sueldo+100 , categoria=categoria+1-- WHERE categoria=13;-- Borrado:-- DELETE FROM empleados; --borra el contenido completo de una tabla-- DELETE FROM nombreTabla WHERE consulta;

USE empleadosDB;LOAD DATA LOCAL INFILE '.\\empleados.dat' INTO TABLE empleados;LOAD DATA LOCAL INFILE '.\\departamentos.dat' INTO TABLE departamentos; LOAD DATA LOCAL INFILE '.\\proyectos.dat' INTO TABLE proyectos;LOAD DATA LOCAL INFILE ".\\hijos.dat" INTO TABLE hijos;LOAD DATA LOCAL INFILE ".\\trabajaEn.dat" INTO TABLE trabajaEn;

INSERT INTO lugaresDpto VALUES (1,'madrid'),(1,'palencia'), (2,'sevilla'), (3,'granada'), (4,'jaen'), (5,'cordoba'), (2,'guadalajara'), (1,'almeria');

ALTER TABLE empleadosADD CONSTRAINT atributosFK FOREIGN KEY(dniSupervisor)REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE CASCADE;

91 de 4

29/05/2013 04:19 p.m.

conEmpleados.sql-- LENGUAJE DE MANIPULACIÓN DE DATOS------ SELECT what_to_select-- FROM which_table-- WHERE conditions_to_satisfy;---- SELECT * FROM pet WHERE name LIKE 'b%';---- SELECT [DISTINCT] columnas-- FROM tablas-- WHERE condiciones-- GROUP BY atributos-- HAVING condicion de seleccion de grupos-- ORDER BY <columnas [ASC|DESC]>---- SELECT *-- FROM nombreTabla-- INTO OUTFILE "C:\fichero";---- Condiciones:-- BETWEEN '1981-01-03' AND '1983-02-02'-- [NOT] IN (300,400)-- [NOT] IN ('a','z')-- IS NULL;-- IS NOT NULL;---- Operaciones:-- NOT !, AND &&, OR ||-- =, !=, <>, <=, >=, <, >---- SELECT name, birth, CURDATE(),-- (YEAR(CURDATE())-YEAR(birth))-- - (RIGHT(CURDATE(),5)<RIGHT(birth,5))-- AS age-- FROM pet;---- Declaracion de variables:-- SELECT @last := LAST_INSERT_ID();---- SELECT *-- FROM tabla1 LEFT JOIN tabla2 ON tabla1.campo1 = tabla2.campo2-- WHERE condiciones;---- SELECT *-- FROM tabla1 AS t1 LEFT JOIN tabla2 AS t2 ON t1.campo1 = t2.campo2-- WHERE condicones;---- Funciones:

92 de 4

29/05/2013 04:19 p.m.

-- Headers() pone cabeceras-- AVG(columna) calcula la media---- Union, intersección, diferencia:-- UNION, INTERSEC, EXCEPT

USE empleadosDB;

-- Ver todos los datos de la tabla empleados; SELECT * FROM empleados;

-- Seleccinar todos los datos de los empleados del depto 5SELECT *FROM empleadosWHERE numD=5;

-- Nombre y apellidos de los empleados que trabajan en el depto-- 5 y que tienen sueldo >100.000 eurosSELECT nombre, apellido1, apellido2FROM empleadosWHERE numD=5 AND sueldo>100000;

-- Nombre y apellidos de los empleados que trabajan en el depto 1, 2 ó 3. SELECT nombre, apellido1, apellido2FROM empleadosWHERE numD IN (1,2,3);

-- Nombre y apellidos empleados que trabajan en el departamento: "investigación" SELECT nombre,apellido1,apellido2FROM empleados,departamentosWHERE nombreDept="investigacion" AND numD=numDept;

-- otra formaSELECT nombre,apellido1FROM empleados JOIN departamentos ON numDept=numD WHERE nombreDept="investigacion";

-- Nombre de los empleados con al menos dos hijosSELECT empleados.nombreFROM empleados, hijos h1, hijos h2WHERE empleados.dni=h1.dni AND empleados.dni=h2.dni AND h1.nombre!=h2.nombre;

-- Para cada empleado su nombre y el nombre del supervisor. SELECT e.nombre, s.nombreFROM empleados e, empleados sWHERE e.dniSupervisor=s.dni;

93 de 4

29/05/2013 04:19 p.m.

-- Uniones-- Numero de proyectos en los que tabaja algún "garcia"-- o algún proyecto de ese departamento trabaje algún "garcia" (SELECT numPFROM empleados, trabajaEnWHERE apellido1="garcia" AND empleados.dni=trabajaEn.dni) UNION ALL {ALL: para mantener duplicados}(SELECT numPFROM empleados, departamentos, proyectosWHERE apellido1="garcia" AND proyectos.numDept=departamentos.numDept AND dniJefe=dni);

--SELECT DISTINCT numP FROM proyectosWHERE numP IN (SELECT numP

FROM empleados, trabajaEnWHERE apellido1="garcia" AND empleados.dni=trabajaEn.dni)

ORnumP IN (SELECT numP

FROM empleados,departamentos,proyectosWHERE apellido1="garcia" AND proyectos.numDept=proyectos.numDept AND dniJefe=dni);

-- Empleados que tienen el mismo sueldo y trabajan en el mismo departamento que algun "garcia" SELECT dniFROM empleadosWHERE (sueldo,numD) IN

(SELECT sueldo,numD FROM empleadosWHERE apellido1="garcia");

--es lo mismoSELECT e1.dniFROM empleados e1,empleados e2WHERE E2.apellido1="garcia" AND E1.sueldo=E2.sueldo AND E1.numD=E2.numD;

-- En vez de IN se puede colocar un operador <,<=,>,>=,!=,<>,=, ANY, SOME, ALL,

--empleados con sueldo mayor que algun lopez sueldo>ANY--empleados con sueldo mayor que todos lopez sueldo>ALL

--Numero de proyecto en que trabaja garcia como jefe de proyectoSELECT numP FROM proyectos WHERE numP IN

(SELECT numDept FROM departamentos WHERE dniJefe IN

(SELECT dniFROM empleados

94 de 4

29/05/2013 04:19 p.m.

WHERE apellido1="garcia"));

-- EXITS comprobar si existen tuplas-- Nombre y apellido de los empleados con algun hijoSELECT nombre,apellido1FROM empleadosWHERE EXISTS (SELECT *

FROM hijosWHERE empleados.dni=hijos.dni);

-- Nombre y apellido de los empleados sin hijosSELECT nombre,apellido1FROM empleadosWHERE NOT EXISTS

(SELECT * FROM hijosWHERE empleados.dni=hijos.dni);--otra forma

SELECT nombre,apellido1FROM empleadosWHERE DNI NOT IN

(SELECT dni FROM hijos);--otra forma

SELECT nombre,apellido1FROM empleadosEXCEPT

(SELECT nombre, apellido1FROM empleados, hijosWHERE empleados.dni=hijos.dni);

-- Nombre y apellido de jefes de departamento con al menos un hijoSELECT nombre, apellido1FROM empleadosWHERE dni IN

(SELECT dniJefe FROM departamentos WHERE dniJefe IN

(SELECT dniFROM hijos));

-- otra formaSELECT nombre, apellido1FROM empleadosWHERE EXISTS

(SELECT *FROM departamentos WHERE dniJefe=dni) AND EXISTS (SELECT *FROM hijos

95 de 4

29/05/2013 04:19 p.m.

WHERE empleados.dni=dni);

-- Nombre y apellido de los empleados que trabajan en todos los proyectos controlados por el departamento 5SELECT nombre,apellido1FROM empleadosWHERE NOT EXISTS

(SELECT *FROM proyectos p WHERE p.numDept=5) AND NOT EXISTS

(SELECT *FROM trabajaEn, proyectosWHERE empleados.dni=trabajaEn.dni AND proyectos.numP=trabajaEn.numP);

-- empleados que no tiene supervisorSELECT *FROM empleadosWHERE dniSupervisor IS NULL;

-- otra formaSELECT *FROM empleadosWHERE dniSupervisor NOT IN

(SELECT dniFROM empleados);

-- otra formaSELECT *FROM empleados eWHERE NOT EXISTS

(SELECT *FROM empleadosWHERE e.dniSupervisor=dni);

-- FUNCIONES AGREGADAS-- COUNT, SUM, MIN, MAX,ANG-- COUNT cuenta los valores nulos, los demas los ignoran-- cuando una tabla esta vacia COUNT devuelve 0 los demas NULL

-- Nombre y apellido de los empleados con al menos dos hijosSELECT nombre, apellido1FROM empleadosWHERE (SELECT COUNT(*)

FROM hijosWHERE empleados.dni=dni)>=2;

--AGRUPANDOSELECT numD,count(*),avg(sueldo) FROM empleadosGROUP BY numD;

96 de 4

29/05/2013 04:19 p.m.

-- Para cada proyecto numero de proyecto, nombre y numero de empleados que trabajan en elSELECT numP, nombre, count(*)FROM proyectos NATURAL JOIN trabajaEnGROUP BY numP,nombre;

-- Para cada departamento con mas de tres empleados num de departamento-- y numero de empleados con sueldo mayor a 100.000 eurosSELECT numD,count(*) FROM empleados WHERE sueldo>100000GROUP BY numD HAVING count(*)>3;

--ORDENando por defecto ascendenteSELECT numD,nombre,apellido1FROM empleadosORDER BY numD ASC, nombre DESC,apellido1

97 de 4

29/05/2013 04:19 p.m.

vistasEmpleados.sql-- LENGUAJE DE CREACION DE VISTAS:---- Creacion de vistas:-- CREATE-- VIEW view_name [(column_list)]-- AS select_statement---- Modificación de vistas:-- ALTER-- VIEW view_name [(column_list)]-- AS select_statement---- Borrado de vistas:-- DROP-- VIEW view_name-- [RESTRICT | CASCADE] USE

empleadosDB;

-- Creación de una vista:

CREATE VIEW testAS SELECT * FROM

empleados;

CREATEVIEW sueldosAnualesAS SELECT dni, sueldo*12 AS sueldoAnual

FROM empleados;

PRACTICA: Empleados.

• Diagrama entidad relación:

nombreP

apellido1

apellido2

dni nombre

sueldo

numDept nombreDept

98 de 4

29/05/2013 04:19 p.m.

lugares

supervisorempleados

pertenecia n 1 departamentos

supervisado

1 n dirige1

fecha1

controlasupervisa n

n

trabaja enm

nproyectos

hijos

nombre fecha

numHorasnumP

nombre

lugar

99 de 4

29/05/2013 04:19 p.m.

• Modelo relación

empleados

dni nombre apellido1 appellido2 sueldo numD dniSupervisor

departamentos

nu m D ept nombreDept dniJefe fecha

proyectos

nu m P nombre lugar numDept

trabajaEn

dni nu m P numH

hijos

dni nombre fecha

lugaresDpto

Consultas:nu m D lugar

1. Ver todos los datos de la tabla empleados.2. Seleccionar todos los datos de los empleados del depto 5.3. Nombre y apellidos de los empleados que trabajan en el depto 5 y que tienen sueldo >100.000€.4. Nombre y apellidos de los empleados que trabajan en el depto 1, 2 ó 3.5. Nombre y apellidos empleados que trabajan en el departamento: "investigación”.6. Nombre de los empleados con al menos dos hijos.7. Para cada empleado su nombre y el nombre del supervisor.8. Para cada proyecto número de proyecto, nombre y número de empleados que trabajan en él.9. Empleados que tienen el mismo sueldo y trabajan en el mismo departamento que algún “garcia”.10. Número de proyecto en que trabaja “garcia” como jefe de proyecto.11. Nombre y apellido de los empleados con algún hijo.12. Nombre y apellido de los empleados sin hijos.13. Nombre y apellido de jefes de departamento con al menos un hijo.14. Nombre y apellido de los empleados que trabajan en todos los proyectos controlados por el

departamento 5.15. Empleados que no tiene supervisor.16. Nombre y apellido de los empleados con al menos dos hijos.17. Para cada proyecto: número de proyecto, nombre y número de empleados que trabajan en él.18. Para cada departamento con más de tres empleados número de departamento y número de empleados

con sueldo mayor a 100.000€.

100 de 4

29/05/2013 04:19 p.m.

• Pasar a SQL SQL:

Lenguaje de definición de datosLenguaje de definicion de almacenamiento Lenguaje de manipulacion de datos Lenguaje de definicion de vistas

mysql> source filenamemysql> \. filename

empleados.sql-- LENGUAJE DE DEFINICION DE DATOS:---- Tipos de datos-- Cadenas de caracteres-- longitud fija char(m) 1..255 rellena con blancos-- long varibale varchar(m)-- TINYBLOB 255-- TEXT 65,535 char string-- BLOB[(M)] 65,535 binary string-- MEDIUMBLOB 16,777,215-- MEDIUMBLOB 16,777,215-- LONGTEXT 4,294,967,295-- LONGTEXT 4,294,967,295-- ENUM('value1','value2',...)-- SET('value1','value2',...)---- Numericos-- enteros-- int o integer, unsigned-- TINYINT[(M)] [UNSIGNED] [ZEROFILL] -127 a 128-- SMALLINT[(M)] [UNSIGNED] [ZEROFILL] -32768 to 32767-- MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] -8388608 to 8388607-- INT[(M)] [UNSIGNED] [ZEROFILL] -2147483648 to 2147483647-- BIGINT[(M)] [UNSIGNED] [ZEROFILL] -9223372036854775808 to9223372036854775807-- reales-- float[(m,d)]-- (4,2) 4 espacios a lo sumo 2 son decimales-- EJ-- 42.35 bien-- 325.54 mal redondea a 325.5-- FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38-- DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] -1.7976931348623157E+308 to-2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308-- DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]-- BOOL, BOOLEAN------ DATE '1000-01-01' to '9999-12-31'

101 de 4

29/05/2013 04:19 p.m.

-- yyyy-mm-dd-- 1999-02-03 o 19990203-- DATETIME '1000-01-01 00:00:00' to '9999-12-31 23:59:59'-- 'YYYY-MM-DD HH:MM:SS'-- TIME '-838:59:59' to '838:59:59'-- hh:mm:ss-- 12:45:30-- NULL pertenece a todos los tipos de datos en fichero \N---- Modificadores:-- AUTO_INCREMENT-- DEFAULT value-- NOT NULL-- PRIMARY KEY-- UNIQUE-- ON DELETE action1 ON UPDATE action2-- action:-- SET NULL, SET DEFAULT, CASCADE-- CASCADE se eliminan todos las tuplas de empleados que hagan referencia al departamento-- y se continua en cascada.---- Databases:-- CREATE DATABASE nombre;-- USE nombreDB;-- GRANT ALL PRIVLILEGES ON nombreDB.* TO 'albertoe'@'%' IDENTIFIED BY 'clave';---- Tablas:-- CREATE TABLE nombre(listaCampos);---- Creacion de tipos de datos:-- CREATE DOMAIN tipoDNI AS char(8);---- Modificación de tablas:-- ALTER TABLE empleados ADD peso int1 DEFAULT 0;-- ALTER TABLE empleados ADD (peso int1 DEFAULT 0,años int);-- ALTER TABLE empleados ALTER peso DEFAULT 1;-- ALTER TABLE empleados DROP dniSupervisor;---- Borrado:-- DROP DATABASE empleadosDB;-- DROP TABLE departamentos RESTRICT; solo si ninguna otra hace referencia-- DROP TABLE departamentos CASCADE; en cascada todas las que hacen referencia---- Mostrar:-- SHOW DATABASES;-- SHOW TABLES;-- DESCRIBE nombreTabla;-- SHOW COLUMNS FROM nombreTabla;

102 de 4

29/05/2013 04:19 p.m.

CREATE DATABASE empleadosDB;

USE empleadosDB;

CREATE TABLE empleados(dni char(8), nombre varchar(20), apellido1 varchar(20), apellido2 varchar(20), sueldo float(12,2),numD int UNSIGNED, dniSupervisor char(8), PRIMARY KEY(dni)

-- ,FOREIGN KEY(dniSupervisor) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE CASCADE

);CREATE TABLE

departamentos( numDeptint UNSIGNED,

nombreDept varchar(20), dniJefe char(8),fecha date,PRIMARY KEY(numDept),FOREIGN KEY(dniJefe) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE

CASCADE);

CREATE TABLE proyectos( numP int UNSIGNED, nombre

varchar(20), lugarvarchar(10),

numDept int UNSIGNED, PRIMARY KEY(numP),FOREIGN KEY(numDept) REFERENCES departamentos(numDept) ON DELETE CASCADE ON

UPDATE CASCADE);

CREATE TABLE trabajaEn(dni char(8),numP int UNSIGNED, numH int UNSIGNED, PRIMARY KEY(dni,numP),FOREIGN KEY(dni) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE

CASCADE,FOREIGN KEY(numP) REFERENCES proyectos(numP) ON DELETE CASCADE ON UPDATE

CASCADE);

CREATE TABLE hijos( dnichar(8), nombre varchar(20), fecha date,PRIMARY KEY(dni,nombre),FOREIGN KEY(dni) REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE CASCADE

103 de 4

29/05/2013 04:19 p.m.

);CREATE TABLE

lugaresDpto( numDint UNSIGNED, lugarvarchar(20),

PRIMARY KEY(numD,lugar),FOREIGN KEY(numD) REFERENCES departamentos(numDept) ON DELETE CASCADE ON

UPDATE CASCADE);

104 de 4

29/05/2013 04:19 p.m.

loadEmpleados.sql-- LENGUAJE DE ALMACENAMIENTO---- Carga de ficheros:-- LOAD DATA LOCAL INFILE ".\\nombreFich" INTO TABLE nombreTabla;-- LOAD DATA LOCAL INFILE ".\\nombreFich" INTO TABLE nombreTabla-- LINES TERMINATED BY '\r\n';---- Insercion en tablas:-- INSERT INTO-- INSERT INTO pet VALUES-- ('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL);-- INSERT INTO pet VALUES-- ('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL),-- ('Moo, 'Harold', 'cat', 'f', '1993-02-04', NULL);---- Actualizacion de datos:-- UPDATE test SET-- neme='busbunny'-- WHERE name='patito';-- UPDATE empleados SET-- sueldo=sueldo+100 , categoria=categoria+1-- WHERE categoria=13;-- Borrado:-- DELETE FROM empleados; --borra el contenido completo de una tabla-- DELETE FROM nombreTabla WHERE consulta;

USE empleadosDB;LOAD DATA LOCAL INFILE '.\\empleados.dat' INTO TABLE empleados;LOAD DATA LOCAL INFILE '.\\departamentos.dat' INTO TABLE departamentos; LOAD DATA LOCAL INFILE '.\\proyectos.dat' INTO TABLE proyectos;LOAD DATA LOCAL INFILE ".\\hijos.dat" INTO TABLE hijos;LOAD DATA LOCAL INFILE ".\\trabajaEn.dat" INTO TABLE trabajaEn;

INSERT INTO lugaresDpto VALUES (1,'madrid'),(1,'palencia'), (2,'sevilla'), (3,'granada'), (4,'jaen'), (5,'cordoba'), (2,'guadalajara'), (1,'almeria');

ALTER TABLE empleadosADD CONSTRAINT atributosFK FOREIGN KEY(dniSupervisor)REFERENCES empleados(dni) ON DELETE CASCADE ON UPDATE CASCADE;

105 de 4

29/05/2013 04:19 p.m.

conEmpleados.sql-- LENGUAJE DE MANIPULACIÓN DE DATOS------ SELECT what_to_select-- FROM which_table-- WHERE conditions_to_satisfy;---- SELECT * FROM pet WHERE name LIKE 'b%';---- SELECT [DISTINCT] columnas-- FROM tablas-- WHERE condiciones-- GROUP BY atributos-- HAVING condicion de seleccion de grupos-- ORDER BY <columnas [ASC|DESC]>---- SELECT *-- FROM nombreTabla-- INTO OUTFILE "C:\fichero";---- Condiciones:-- BETWEEN '1981-01-03' AND '1983-02-02'-- [NOT] IN (300,400)-- [NOT] IN ('a','z')-- IS NULL;-- IS NOT NULL;---- Operaciones:-- NOT !, AND &&, OR ||-- =, !=, <>, <=, >=, <, >---- SELECT name, birth, CURDATE(),-- (YEAR(CURDATE())-YEAR(birth))-- - (RIGHT(CURDATE(),5)<RIGHT(birth,5))-- AS age-- FROM pet;---- Declaracion de variables:-- SELECT @last := LAST_INSERT_ID();---- SELECT *-- FROM tabla1 LEFT JOIN tabla2 ON tabla1.campo1 = tabla2.campo2-- WHERE condiciones;---- SELECT *-- FROM tabla1 AS t1 LEFT JOIN tabla2 AS t2 ON t1.campo1 = t2.campo2-- WHERE condicones;---- Funciones:

106 de 4

29/05/2013 04:19 p.m.

-- Headers() pone cabeceras-- AVG(columna) calcula la media---- Union, intersección, diferencia:-- UNION, INTERSEC, EXCEPT

USE empleadosDB;

-- Ver todos los datos de la tabla empleados; SELECT * FROM empleados;

-- Seleccinar todos los datos de los empleados del depto 5SELECT *FROM empleadosWHERE numD=5;

-- Nombre y apellidos de los empleados que trabajan en el depto-- 5 y que tienen sueldo >100.000 eurosSELECT nombre, apellido1, apellido2FROM empleadosWHERE numD=5 AND sueldo>100000;

-- Nombre y apellidos de los empleados que trabajan en el depto 1, 2 ó 3. SELECT nombre, apellido1, apellido2FROM empleadosWHERE numD IN (1,2,3);

-- Nombre y apellidos empleados que trabajan en el departamento: "investigación" SELECT nombre,apellido1,apellido2FROM empleados,departamentosWHERE nombreDept="investigacion" AND numD=numDept;

-- otra formaSELECT nombre,apellido1FROM empleados JOIN departamentos ON numDept=numD WHERE nombreDept="investigacion";

-- Nombre de los empleados con al menos dos hijosSELECT empleados.nombreFROM empleados, hijos h1, hijos h2WHERE empleados.dni=h1.dni AND empleados.dni=h2.dni AND h1.nombre!=h2.nombre;

-- Para cada empleado su nombre y el nombre del supervisor. SELECT e.nombre, s.nombreFROM empleados e, empleados sWHERE e.dniSupervisor=s.dni;

107 de 4

29/05/2013 04:19 p.m.

-- Uniones-- Numero de proyectos en los que tabaja algún "garcia"-- o algún proyecto de ese departamento trabaje algún "garcia" (SELECT numPFROM empleados, trabajaEnWHERE apellido1="garcia" AND empleados.dni=trabajaEn.dni) UNION ALL {ALL: para mantener duplicados}(SELECT numPFROM empleados, departamentos, proyectosWHERE apellido1="garcia" AND proyectos.numDept=departamentos.numDept AND dniJefe=dni);

--SELECT DISTINCT numP FROM proyectosWHERE numP IN (SELECT numP

FROM empleados, trabajaEnWHERE apellido1="garcia" AND empleados.dni=trabajaEn.dni)

ORnumP IN (SELECT numP

FROM empleados,departamentos,proyectosWHERE apellido1="garcia" AND proyectos.numDept=proyectos.numDept AND dniJefe=dni);

-- Empleados que tienen el mismo sueldo y trabajan en el mismo departamento que algun "garcia" SELECT dniFROM empleadosWHERE (sueldo,numD) IN

(SELECT sueldo,numD FROM empleadosWHERE apellido1="garcia");

--es lo mismoSELECT e1.dniFROM empleados e1,empleados e2WHERE E2.apellido1="garcia" AND E1.sueldo=E2.sueldo AND E1.numD=E2.numD;

-- En vez de IN se puede colocar un operador <,<=,>,>=,!=,<>,=, ANY, SOME, ALL,

--empleados con sueldo mayor que algun lopez sueldo>ANY--empleados con sueldo mayor que todos lopez sueldo>ALL

--Numero de proyecto en que trabaja garcia como jefe de proyectoSELECT numP FROM proyectos WHERE numP IN

(SELECT numDept FROM departamentos WHERE dniJefe IN

(SELECT dniFROM empleados

108 de 4

29/05/2013 04:19 p.m.

WHERE apellido1="garcia"));

-- EXITS comprobar si existen tuplas-- Nombre y apellido de los empleados con algun hijoSELECT nombre,apellido1FROM empleadosWHERE EXISTS (SELECT *

FROM hijosWHERE empleados.dni=hijos.dni);

-- Nombre y apellido de los empleados sin hijosSELECT nombre,apellido1FROM empleadosWHERE NOT EXISTS

(SELECT * FROM hijosWHERE empleados.dni=hijos.dni);--otra forma

SELECT nombre,apellido1FROM empleadosWHERE DNI NOT IN

(SELECT dni FROM hijos);--otra forma

SELECT nombre,apellido1FROM empleadosEXCEPT

(SELECT nombre, apellido1FROM empleados, hijosWHERE empleados.dni=hijos.dni);

-- Nombre y apellido de jefes de departamento con al menos un hijoSELECT nombre, apellido1FROM empleadosWHERE dni IN

(SELECT dniJefe FROM departamentos WHERE dniJefe IN

(SELECT dniFROM hijos));

-- otra formaSELECT nombre, apellido1FROM empleadosWHERE EXISTS

(SELECT *FROM departamentos WHERE dniJefe=dni) AND EXISTS (SELECT *FROM hijos

109 de 4

29/05/2013 04:19 p.m.

WHERE empleados.dni=dni);

-- Nombre y apellido de los empleados que trabajan en todos los proyectos controlados por el departamento 5SELECT nombre,apellido1FROM empleadosWHERE NOT EXISTS

(SELECT *FROM proyectos p WHERE p.numDept=5) AND NOT EXISTS

(SELECT *FROM trabajaEn, proyectosWHERE empleados.dni=trabajaEn.dni AND proyectos.numP=trabajaEn.numP);

-- empleados que no tiene supervisorSELECT *FROM empleadosWHERE dniSupervisor IS NULL;

-- otra formaSELECT *FROM empleadosWHERE dniSupervisor NOT IN

(SELECT dniFROM empleados);

-- otra formaSELECT *FROM empleados eWHERE NOT EXISTS

(SELECT *FROM empleadosWHERE e.dniSupervisor=dni);

-- FUNCIONES AGREGADAS-- COUNT, SUM, MIN, MAX,ANG-- COUNT cuenta los valores nulos, los demas los ignoran-- cuando una tabla esta vacia COUNT devuelve 0 los demas NULL

-- Nombre y apellido de los empleados con al menos dos hijosSELECT nombre, apellido1FROM empleadosWHERE (SELECT COUNT(*)

FROM hijosWHERE empleados.dni=dni)>=2;

--AGRUPANDOSELECT numD,count(*),avg(sueldo) FROM empleadosGROUP BY numD;

110 de 4

29/05/2013 04:19 p.m.

-- Para cada proyecto numero de proyecto, nombre y numero de empleados que trabajan en elSELECT numP, nombre, count(*)FROM proyectos NATURAL JOIN trabajaEnGROUP BY numP,nombre;

-- Para cada departamento con mas de tres empleados num de departamento-- y numero de empleados con sueldo mayor a 100.000 eurosSELECT numD,count(*) FROM empleados WHERE sueldo>100000GROUP BY numD HAVING count(*)>3;

--ORDENando por defecto ascendenteSELECT numD,nombre,apellido1FROM empleadosORDER BY numD ASC, nombre DESC,apellido1

111 de 4

29/05/2013 04:19 p.m.

vistasEmpleados.sql-- LENGUAJE DE CREACION DE VISTAS:---- Creacion de vistas:-- CREATE-- VIEW view_name [(column_list)]-- AS select_statement---- Modificación de vistas:-- ALTER-- VIEW view_name [(column_list)]-- AS select_statement---- Borrado de vistas:-- DROP-- VIEW view_name-- [RESTRICT |

CASCADE] USE

empleadosDB;

-- Creación de una vista:

CREATE VIEW testAS

SELECT * FROM empleados;

CREATEVIEW sueldosAnualesAS SELECT dni, sueldo*12 AS sueldoAnual

112 de 4

29/05/2013 04:19 p.m.

FROM e

mpleados;

u 4,5,6

Documents