2.4 Manipulación de datos - dis.um.esdis.um.es/~jfernand/0607/fbd/sql.pdf · Tema 2. Modelo...

97Tema 2. Modelo relacional de datos

Structured Query Language (lenguaje estructurado de consulta)

• Primer lenguaje de BD de alto nivel. Años 70.

–Diseñado e implementado en el IBM’s Research Laboratory (San José -California), para el SGBD Relacional experimental System R

• Definición de un lenguaje estándar para SGBDR

ANSI (American National Standards Institute)+ ISO (International Standardization Organization)

– SQL1 (ANSI 1986), extendido en 1989 (SQL-89)

– SQL-92 (SQL2), y

– SQL:1999 (extensiones de Orientación a Objetos, disparadores, …)

– SQL:2003 (incluye XML y otros conceptos recientes)

• Primeras implementaciones:

–ORACLE (finales 70) y poco después INGRES

2.4 Manipulación de datos2.4.3. El lenguaje relacional SQL-92


• Lenguaje de bases de datos completo (no sólo «de consulta»)–Definición y Manipulación de Datos (LDD + LMD)–Definición y destrucción de Vistas (LDV)–Creación y destrucción de índices (aunque en SQL-92 «ya no existen»)

– Incorporación de SQL dentro de código escrito con un Lenguaje de Programación de propósito general (Pascal, C, etc.)

• Los proveedores de SGBDR comerciales (Oracle) implementan variaciones de SQL

–Algunas incluyen características que no están estandarizadas(triggers /reglas activas incluidos en la versión SQL:1999)

• Niveles de compatibilidad con el estándar de SQL– Entry SQL– Intermediate SQL– Full SQL

2.4 Manipulación de datos: SQL-92


• Consultas o Selección de datos

• Modificación de datos

• Vistas

• Definición y Alteración de datos– Esquemas, Dominios, Tablas

• Restricciones de Integridad Generales (Asertos)

• Seguridad y Control de Acceso

2.4 Manipulación de datos: SQL-92Lo que vamos a estudiar...


• SQL-92 vs. Modelo Relacional Formal– No utiliza los términos formales relación, atributo, tupla…,

sino tabla, columna, fila…– Permite que las tablas tengan 2 o más filas idénticas en todos los

valores de sus columnas

En general, tabla SQL ≠ conjunto de filas, sino que

Tabla SQL = Multiconjunto de filas (saco, bag)Es posible forzar que las tablas SQL sean conjuntos de filas:

- con restricciones de clave o- mediante opción DISTINCT en una SELECT (*se verá*)

– Las columnas de una tabla están ordenadas, y es posible indicar un orden de visualización de las filas

– Una clave ajena puede referenciar a una clave candidata

2.4 Manipulación de datos: SQL-92Consultas básicas (4)


2.4 Manipulación de datos: SQL-92Esquema de base de datos COMPAÑÍA

NIF NDNSSJEFESALARIOSEXODIRECCIONFECHANNSSAPELLIDONOMBRE

FECHAINICDIRENSSDIRENUMERODNOMBRED

NUMERODLUGARPNUMEROPNOMBREP

OFICINANUMEROD

HORASNUMPNSSE

PARENTESCOFECHANSEXONOMBRE_FAMILIARNSSE

EMPLEADO

DEPARTAMENTO

OFICINA_DEPTO

PROYECTO

TRABAJA_EN

FAMILIAR


donde:<lista columnas> columnas cuyos valores va a obtener la consulta<lista tablas> tablas necesarias para realizar la consulta<condición> expresión booleana para identificar filas que obtendrá la consulta

(expresión de reunión y/o de selección)

Fecha de nacimiento y dirección del empleado llamado José SilvaSELECT fechan, direccionFROM EmpleadoWHERE nombre = ‘José’ AND apellido = ‘Silva’;

• La consulta selecciona las filas de <lista tablas>que satisfacen <condición> yproyecta el resultado sobre las columnas de <lista columnas>

• Orden SELECT: Instrucción básica de obtención de información

SELECT <lista columnas>FROM <lista tablas>WHERE <condición>

2.4 Manipulación de datos: SQL-92Consultas básicas


• La orden SELECT ... FROM ... WHERE...– No es igual a la operación restricción σ del Álgebra Relacional– SELECT de SQL tiene muchas más opciones y matices– En caso de una única tabla T en <lista tablas>

SELECT <lista columnas>FROM TWHERE <condición>

es equivalente a...

Π <lista columnas>(σ<condición> ( T ))* Nombre, apellido y dirección de los empleados del departamento de Investigación

SELECT nombre, apellido, direccionFROM Empleado, Departamento → reunión o join de tablasWHERE nombred=‘Investigación’ → condición de selección

AND numerod=nd; → condición de reunión entre tablas



• Cualquier nº de condiciones selección/reunión en SELECT* Para cada proyecto ubicado en Santiago, obtener el nº del proyecto, nº del departamento que lo controla y el apellido, dirección y fecha de nacimiento del gerente de ese departamento

SELECT numerop, numd, apellido, direccion, fechanFROM Proyecto, Departamento, EmpleadoWHERE numd=numerod AND nssdire=nss AND lugarp=‘Santiago’;

• Una SELECT puede obtener filas repetidas* Salario de los empleados de los departamentos de Administración y de Investigación

SELECT salarioFROM Empleado, DepartamentoWHERE (nombred = ‘Administración’ OR nombred = ‘Investigación’)

AND numerod=nd;



• Obtención de los valores de todas las columnas de las filas seleccionadas

– No es necesario listar todos los nombres tras cláusula SELECT– Uso del símbolo * (todas las columnas)

SELECT *FROM Empleado WHERE nd=5;

SELECT *FROM DepartamentoWHERE nombred=‘Investigación’;

SELECT *FROM Empleado, DepartamentoWHERE nombred=‘Investigación’ AND nd=númerod;

2.4 Manipulación de datos: SQL-92Consultas básicas (5): uso de *


• Selección incondicional– Equivale a una condición TRUE para todas las filas

selección de todas las filas de...una tabla (si la cláusula FROM sólo contiene una tabla), oel producto cartesiano entre varias tablas (si FROM incluye más de una)

* Seleccionar todos los nss de empleadosSELECT nssFROM Empleado;

* Obtener todas las combinaciones de nss de empleados y nombres de departamentosSELECT nss, nombredFROM Empleado, Departamento;

2.4 Manipulación de datos: SQL-92Consultas básicas (6): omisión de WHERE


• Operador LIKE– Comparación de cadenas de caracteres– Caracteres reservados: ‘%’ y ‘_’ (comodines)

*Nombres y apellidos de los empleados cuya dirección esté en Higueras, estado de MéxicoSELECT nombre, apellidoFROM EmpleadoWHERE direccion LIKE ‘%Higueras, MX%’ ;

• Operador ||– Concatenación de cadenas de caracteres

* Nombres completos en una sola columna de empleados con dirección en Higueras (México)SELECT nombre || ‘ ’ || apellidoFROM EmpleadoWHERE direccion LIKE ‘%Higueras, MX%’ ;

2.4 Manipulación de datos: SQL-92Consultas básicas (7): cadenas de caracteres


• Operaciones aritméticas– Aplicación de operadores aritméticos ( + - * / ) sobre valores numéricos

* Salarios de los empleados que trabajan en el proyecto ProductoX, tras un aumento del 10% SELECT apellido, nombre, 1.1*salario

FROM Empleado, Trabaja_en, ProyectoWHERE nss=nsse AND nump=numerop AND nombrep=‘ProductoX’ ;

el valor real de los salarios en la tabla EMPLEADO no cambia

• Operaciones con fechas, horas, marcas de tiempo e intervalos– Especificación del valor de un INTERVAL

como diferencia de dos valores DATE, TIME o TIMESTAMP– Incremento y Decremento de valores de columnas de tipo DATE, TIME,

TIMESTAMP en un intervalo compatible con el tipo

2.4 Manipulación de datos: SQL-92Consultas básicas (8): aritmética y tiempo


• En SQL los nombres de las columnas deben ser únicos dentro de cada tabla

• Consulta que referencia a varias columnas de igual nombre, pero de tablas distintas...

AMBIGÜEDAD --------------Solución: CALIFICACIÓN

El esquema COMPAÑÍA incluye las siguientes tablas:DEPARTAMENTO (nombred, numerod, nssdire, fechainicdire)OFICINA_DEPTO (numerod, oficina)

* Código, nombre y lugares de los departamentos de Marketing y de InvestigaciónSELECT Departamento.numerod, nombred, oficina

FROM Departamento, Oficina_deptoWHERE Departamento.nombred IN (‘Marketing’, ‘Investigación’)

AND Departamento.numerod = Oficina_depto.numerod;

2.4 Manipulación de datos: SQL-92Consultas básicas (9): calificación


• Puede utilizarse seudónimos para acortar nombres de tabla dentro de las consultas con calificación:

SELECT nombred, D.numerod, oficinaFROM Departamento AS D, Oficina_depto L ← ‘AS’ es opcionalWHERE D.nombred IN (‘Marketing’, ‘Investigación’) AND D.numerod = L.numerod;

• Consulta que se refiere dos veces a la misma tablaAMBIGÜEDAD ------------------- Solución: SEUDÓNIMOS

* Obtener nombre y apellido de cada empleado y de su supervisor inmediatoSELECT E.nombre, E.apellido, S.nombre, S.apellidoFROM Empleado E, Empleado SWHERE E.nssjefe=S.nss;

2.4 Manipulación de datos: SQL-92Consultas básicas (10): seudónimos


• En el resultado de evaluar la consulta* Nombres de cada empleado y su supervisor, cambiando al mismo tiempo los nombres de las columnas resultantes a ‘nom_empleado’ y ‘nom_supervisor’

SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisor

FROM Empleado E, Empleado SWHERE E.nssjefe = S.nss;

Nueva cabecera para la tabla resultado

• Seudónimos de columnas (y/o tablas) en cláusula FROMSELECT nom, num, oficina

FROM Departamento D(nom, num, dire, inidire), Oficina_deptoWHERE nom IN (‘Marketing’, ‘Investigación’) AND num = numerod;

2.4 Manipulación de datos: SQL-92Consultas básicas (11): renombrar columnas


• SQL permite presentar las filas resultado de una consulta de forma ordenada: Cláusula ORDER BY

– Ordenación según valores de una o varias columnas– Ascendente ASC (por defecto) o Descendente DESC– Suele ser una operación muy costosa

las filas no se ordenan en disco: se ven ordenadas, pero no lo están

*Nombre y apellido de los empleados, y proyectos en los que trabajan, en orden descendente por departamentos y, dentro de cada departamento, en orden alfabético ascendente por apellido y nombre

SELECT nombred, apellido, nombre, nombreFROM Departamento, Empleado, Trabaja_en, ProyectoWHERE numerod=nd AND nss=nsse AND nump=numeropORDER BY nombred DESC, apellido ASC, nombre ASC;

2.4 Manipulación de datos: SQL-92Consultas básicas (y 12): orden de presentación


• SQL no elimina filas repetidas del resultado de una consulta, porque...

– Eliminación de duplicados costosa (ordenar+recorrer+eliminar)– El usuario puede desear ver las filas repetidas en el resultado – Si se aplica una función agregada a filas, rara vez deben eliminarse las

duplicadas

• Operador DISTINCT:– Para eliminar duplicados del resultado de una consulta SQL»Resultado = Relación del Modelo Relacional Formal (conjunto de filas)

* Salarios de todos los empleadosSELECT salario FROM Empleado;

* Salarios distintos de empleados, sin importar cuántos perciban cada cantidadSELECT DISTINCT salarioFROM Empleado;

2.4 Manipulación de datos: SQL-92Tablas como conjuntos


• Operaciones de conjuntos– UNION( ∪ ), INTERSECT( ∩ ), EXCEPT ( — ) (minus en ORACLE)– Resultado: conjunto de filas las filas repetidas se eliminan– Las tablas operando han de ser compatibles en tipo:

– igual nº de columnas, y – columnas “correspondientes” con el mismo dominio

* Nombres de los proyectos en que participa el empleado de apellido ‘Silva’, ya sea como trabajador o como gerente del departamento que controla el proyecto

( SELECT nombrep FROM Proyecto, Trabaja_en, EmpleadoWHERE numerop=nump AND nsse=nss AND apellido=‘Silva’ )

UNION( SELECT nombrep FROM Proyecto, Departamento, Empleado

WHERE numd=numerod AND nssdire=nss AND apellido=‘Silva’ );

• Para no eliminar duplicados... – UNION ALL, INTERSECT ALL, EXCEPT ALL

Tablas como conjuntos (2)



• Un conjunto explícito de valores es una lista de valores encerrada entre paréntesis

• Puede aparecer en la cláusula WHERE

* nss de los empleados que trabajan en los proyectos 1, 2 ó 3SELECT DISTINCT nsse FROM Trabaja_enWHERE nump IN (1, 2, 3);

• Operador INv IN V– Indica si el valor v pertenece al conjunto de valores V– Devuelve TRUE si algún elemento e de V cumple que v = e

* nss de los empleados que trabajan en algún proyecto que no sea el 4 ni el 6SELECT DISTINCT nsse FROM Trabaja_enWHERE nump NOT IN (4, 6);

Tablas como conjuntos (3): conjuntos explícitos



• Operador ANY (o SOME)v <op> ANY V o v <op> SOME V ,, <op> ∈ { >, ≥, <, ≤, <>, = }– Compara un valor individual v con los elementos de un conjunto V– Devuelve TRUE si algún elemento e de V cumple que v <op> e

* nss de los empleados que trabajan en alguno de los proyectos 1, 2 ó 3SELECT DISTINCT nsse FROM Trabaja_enWHERE nump = ANY (1, 2, 3);

• Operador ALLv <op> ALL V,, <op> ∈ { >, ≥, <, ≤, <>, = }– Compara un valor v con los elementos de un conjunto V– Devuelve TRUE si para todo elemento e de V se cumple v <op> e

* nss de los empleados que no trabajan en ninguno de los proyectos 1, 2 y 3SELECT DISTINCT nsse FROM Trabaja_enWHERE nump <> ALL (1, 2, 3);

Tablas como conjuntos (4): conjuntos explícitos



• 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 o como 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



• Operador IN (otro uso del mismo operador)

t IN S– indica 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_enWHERE (númp, horas) IN ( SELECT númp, horas

FROM Trabaja_enWHERE nsse=‘123456789’);

Consultas anidadas (2): 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 consulta anidada 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

* Nombres y apellidos de los empleados cuyo salario es menor que el de todos los empleados del departamento 5

SELECT nombre, apellido FROM EmpleadoWHERE salario < ALL ( SELECT salario

FROM EmpleadoWHERE nd=5 );

Consultas anidadas (3): comparar conjuntos


¿”Mejor” con DISTINCTen la subconsulta?


• 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 él

SELECT 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 interiorSi 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 él

SELECT nombre, apellido FROM Empleado EWHERE nss IN ( SELECT nsse FROM Familiar

WHERE nss=nsse AND nombre_familiar=nombreAND sexo= E.sexo );

Consultas anidadas (4): columnas ambiguas



• Una consulta exterior y otra anidada están correlacionadassi una condición de la anidada contiene columnas de una tabla declarada en la consulta exterior

SELECT 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 DWHERE nss=nsse AND D.sexo=‘F’;

Consultas anidadas (5): correlación



• Operador EXISTS (S): comprobación de tablas vacías – Devuelve TRUE si la tabla S contiene al menos una fila– Devuelve 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 él

SELECT 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);

Consultas anidadas (6): EXISTS



• 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 proyectoSELECT nombre, apellido FROM EmpleadoWHERE UNIQUE ( SELECT nsse

FROM Trabaja_enWHERE nsse = nss );

* Nombres, apellidos y salario de los empleados con un solo familiarSELECT nombre, apellido, salario FROM EmpleadoWHERE UNIQUE ( SELECT *

FROM FamiliarWHERE nsse = nss );

Consultas anidadas (y 7): UNIQUE



• Null– Ausencia o desconocimiento de información– Comparar NULL con cualquier cosa da FALSE

• Operador IS NULL ,, IS NOT NULLv IS NULL– es TRUE si v es NULLv IS NOT NULL– es TRUE si v es un valor no NULL

* Nombres de empleados sin supervisoresSELECT nombre, apellido FROM EmpleadoWHERE nssjefe IS NULL;

Nulos



• Función COUNT( ) – Cuenta el número de filas o de valores especificados en una consulta

• Funciones SUM( ), MAX( ), MIN( ), AVG( )– Suma, máximo, mínimo y media aritmética (promedio)– Aplicadas a un multiconjunto (saco, bag) de valores numéricos

Pueden aparecer en cláusula SELECT* Suma de los salarios y salario máximo, mínimo y medio de los empleados

SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario)FROM EMPLEADO;

* Suma de los salarios y salario máximo, mínimo y medio de empleados del depto. de InvestigaciónSELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario)FROM Empleado, DepartamentoWHERE nd=númerod AND nombred=‘Investigación’;

También pueden aparecer en cláusula HAVING (*se verá*)

Funciones agregadas



FUNCIONES AGREGADAS

• Uso de ** Número total de empleados de la compañía

SELECT COUNT(*) FROM Empleado ( cuenta filas)* Contar el número de empleados de la compañía que tienen un jefe

SELECT COUNT(nssjefe) FROM Empleado; ( cuenta filas con nssjefe no NULL)

* Número de empleados en el departamento de InvestigaciónSELECT COUNT(*) FROM Empleado, DepartamentoWHERE nd=númerod AND nombred=‘Investigación’;

• Uso de DISTINCT* Contar el nº de valores distintos de salario que pueden cobrar los empleados

SELECT COUNT(salario) FROM Empleado;Error: NO se eliminan duplicados, así que COUNT(salario) ≡ COUNT(*)

SELECT COUNT(DISTINCT salario) FROM Empleado; OK !!

Funciones agregadas (2): uso de * y de DISTINCT



• Es posible que una consulta anidada y correlacionada con otra exterior, incluya una función agregada

* Nombres de los empleados con 2 o más familiaresSELECT apellido, nombre FROM EmpleadoWHERE 2 ≤ ( SELECT COUNT(*)

FROM FamiliarWHERE nss=nsse );

Funciones agregadas (y 3) y correlación



• Cláusula GROUP BY– Para formar subgrupos de filas dentro de una tabla– Los grupos se forman según el valor de las columnas de agrupación– Las filas de cada grupo tendrán el mismo valor en las columnas

de agrupación

• Aplicación de funciones agregadas a grupos de filas

* Para cada departamento, obtener su número, cuántos empleados tiene dicho departamento y el salario medio de los empleados del mismo

SELECT nd, COUNT(*), AVG(salario)FROM EmpleadoGROUP BY nd ; ← una columna de agrupación

Agrupación


Las columnas de agrupación deben aparecer en la cláusula SELECT,antes de cualquier función agregada, para que su valor (único para cada grupo) aparezca junto al resultado de aplicar la función al grupo


• Cláusula HAVING– Siempre junto a GROUP BY– Condición que deben cumplir los grupos de filas asociados a

cada valor de las columnas de agrupación– Un grupo que no cumple la condición, no es seleccionado para el

resultado

* Para cada proyecto en el que trabajen más de dos empleados, obtener el número y nombre del proyecto, y el nº de empleados que trabajan en él

SELECT numerop, nombrep, COUNT(*)FROM Proyecto, Trabaja_enWHERE numerop=numpGROUP BY numerop, nombrepHAVING COUNT(*) > 2 ;

Agrupación (2)



• WHERE... se aplica a filas individuales• HAVING... se aplica a grupos de filas

* Nº de empleados cuyos salarios superan los 1.800€ en cada departamento, pero sólo en el caso de departamentos en los que trabajen más de 5 empleados

(* Consulta incorrecta ¿por qué? *)

SELECT nombred, COUNT(*)FROM Departamento, EmpleadoWHERE númerod=nd

AND salario>1800GROUP BY nombredHAVING COUNT(*) > 5 ;

(* pista: orden de ejecución *)

(* Consulta correcta *)

SELECT nombred, COUNT(*)FROM Departamento, EmpleadoWHERE númerod=nd

AND salario>1800AND nd IN (SELECT nd

FROM EmpleadoGROUP BY ndHAVING COUNT(*) > 5)

GROUP BY nombred ;

Agrupación (y 3)



• Reunión especificada en la cláusula FROM de una consulta• Hasta ahora la hemos especificado en cláusulas FROM y WHERE

* Nombres y dirección de empleados del departamento de Investigación SELECT nombre, apellido, direccion

FROM Empleado, Departamento ← reunión de tablasWHERE nombred=‘Investigacion’

AND nd=numerod; ← condición de reunión

• Consultas más comprensibles: separa condiciones de reunión y de selección* Nombres y dirección de empleados del departamento de Investigación

SELECT nombre, apellido, direccionFROM (Empleado JOIN Departamento ON nd=numerod) ← tabla reunidaWHERE nombred=‘Investigacion’;

Tablas reunidas



• Es posible anidar varias especificaciones de reunión de tablas

* Para cada proyecto ubicado en ‘Santiago’, obtener el nº de proyecto, el nº del departamento que lo controla y el apellido, dirección y fecha de nacimiento del gerente de ese departamento

SELECT númerop, númd, apellido, dirección, fechanFROM ( ( Proyecto JOIN Departamento ON númd=númerod )

JOIN Empleado ON nssdire=nss )WHERE lugarp=‘Santiago’;

Tablas reunidas (2): anidamiento



• Es el tipo de reunión “por defecto”SELECT ... FROM ( R1 JOIN R2 ON <condición_reunión> )WHERE ...

• Si existe una fila t1 en R1 y otra fila t2 en R2, tales que cumplen la condición de reunión, la tabla resultado (reunida) incluirá la fila obtenida al combinar t1 y t2

SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisorFROM (Empleado E JOIN Empleado S ON E.nssjefe = S.nss);– Son excluidas las filas EMPLEADO con NULL en nssjefe

• También puede especificarse comoR1 INNER JOIN R2 ON <condición_reunión>

Reunión Interna de tablas (inner join)



• Sin condición de reunión explícitaSELECT ... FROM ( R1 NATURAL JOIN R2 )WHERE ...

• Equi-reunión implícita para cada par de columnas con igual nombre en una y otra tabla Sólo se incluye una de estas columnas en el resultado Si no coinciden los nombres de las columnas, es necesario

RENOMBRAR una de ellas mediante AS en la cláusula FROM

SELECT nombre, apellido, direccionFROM ( Empleado NATURAL JOIN (Departamento AS DEP(nombred, nd, dire, fech)) )WHERE nombred=‘Investigacion’;

Reunión Natural de tablas (natural join)



• Útil si en una reunión se necesita obtener las filas… – con valor NULL en las columnas de reunión, o– sin correspondencia en la otra tabla

• Tipos de reunión externa:– LEFT [OUTER] JOIN Reunión externa izquierda– RIGHT [OUTER] JOIN Reunión externa derecha– FULL [OUTER] JOIN Reunión externa completa o total

SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisorFROM (Empleado E LEFT OUTER JOIN Empleado S ON E.nssjefe=S.nss);

Obtiene también los empleados sin supervisor (con NULL en nssjefe)

Reunión Externa de tablas (outer join)



• En una consulta SQL hay un máximo de 6 cláusulas• Sólo son obligatorias SELECT y FROM

• Orden de especificación de las cláusulas:

SELECT <lista columnas> columnas o funciones que se van a obtener

FROM <lista tablas> tablas necesarias (incluso las reunidas)

WHERE <condición para filas> condiciones para selección de filas

GROUP BY <lista columnas agrupación> especificación del agrupamiento de filas

HAVING <condición para grupos> condición para selección de grupos de filas

ORDER BY <lista columnas ordenación> orden de presentación del resultado

Evaluación de consultas



• Orden de evaluación de las cláusulas:1) FROM (es decir, la reunión o join de tablas, si se especifica más de una)2) WHERE3) GROUP BY4) HAVING5) SELECT6) ORDER BY

• Diversas formas de especificar una misma consultaEjemplo: es posible expresar una consulta utilizando...

a) condiciones de reunión en cláusula WHERE, ob) tablas reunidas en la cláusula FROM, oc) consultas anidadas y el operador de comparación IN ...

Flexibilidad

Evaluación de consultas (2)



• Ventajas e inconvenientes de esta flexibilidad:☺ – el usuario elige la técnica o enfoque más cómodo

– Confusión del usuario: ¿qué técnica uso?– Algunas técnicas son más eficientes que otras

el usuario debe determinar cuál

• En condiciones ideales...– Usuario: se preocupa sólo de especificar la consulta correctamente– SGBD: se ocupa de ejecutar la consulta de manera eficiente

• Pero en la práctica no suele ser así... conviene saber qué tipos de consulta son más y menos costosos

Evaluación de consultas (y 3)


Recomendación (optimización de consultas):Consultas con mínimo anidamiento correlacionado

y mínimo ordenamiento implícito


• Orden INSERT– Añade una fila completa a una tabla– Incluye nombre de la tabla y lista de valores para las columnas,

escritos en igual orden al especificado en la orden CREATE TABLE

INSERT INTO Empleado VALUES ( 'Ricardo', ‘C’, 'Martínez', '653298653123', ‘987654321’, '30-DIC-52',

'Olmo 98, Cedros, MX', ‘M’, 37000, '987654321321', 4 ) ;

– Si se desea poner los valores de las columnas en cualquier orden, hay que especificar los nombres de las columnas en dicho orden

INSERT INTO Empleado ( nombre, apellido, nss, nif, nd, salario,nssjefe, direccion, fechan, sexo )

VALUES ( 'Ricardo', ‘C’, 'Martínez', '653298653123', ‘987654321’, 4, 37000,'987654321321', 'Olmo 98, Cedros, MX', '30-DIC-52', ‘M’ ) ;

Inserción de datos



• Inserción de varias filas en una sola orden INSERT– Filas separadas por comas– Cada fila se encierra entre paréntesis

• Especificación explícita de algunas columnas (y no todas)– Omisión de columnas cuyo valor se desconoce– Cada columna no especificada tomará el...

· valor por omisión: valor tomado de su cláusula DEFAULT, o· NULL: si la columna permite nulos y no se definió cláusula

DEFAULT para la misma* Inserción de un empleado del que sólo se conoce su nombre, apellidos, nss y nif

INSERT INTO Empleado (nombre, apellido, nss, nif)VALUES ( 'Rubén', 'Ripoll', '553298653111‘, ‘11222333R’ ) ;

Inserción (2)



• Si SGBD con implementación total de SQL-92- El SGBD maneja e impone toda RI definida en esquema de BD (LDD)

• Si SGBD con implementación de algunas RI- Menor complejidad, mayor eficiencia- SGBD implementa comprobaciones para imponer RI que sí maneja

INSERT INTO Empleado (nombre, apellido, nd) VALUES ( 'Roberto', 'Huertas', 2 ) ;Inserción rechazada: no se incluye valor para nss, que debe ser NOT NULL

- Programador debe asegurar la no violación de las RI no manejadas por el SGBD

Supongamos que no existe departamento con numerod=8INSERT INTO Empleado (nombre, apellido, nss, nif, nd)

VALUES ( 'Roberto', 'Huertas', '980760540222', ‘22333444H’, 8 ) ;Si el SGBD sí maneja la Integridad ReferencialInserción rechazadaSi el SGBD NO soporta la Integridad ReferencialInserción permitida ¡el programador debe asegurar que esto no pase!

Inserción (3): Restricciones de Integridad



• Carga de una tabla con información sinóptica de la BDSea una tabla INFO_DEPTOS vacía. En ella queremos almacenar los nombres de cada departamento, su nº de empleados y el salario conjunto de los empleados del mismo.

INFO_DEPTOS ( nombre_depto, num_emps, sal_total)INSERT INTO Info_deptos ( nombre_depto, num_emps, sal_total )

SELECT nombred, COUNT(*), SUM(salario)FROM Departamento, EmpleadoWHERE númerod=ndGROUP BY nombred ;

– Es posible hacer SELECT ... FROM Info_deptos ...– INFO_DEPTOS puede contener información no actualizada

Si se modifica información en EMPLEADO y/o DEPARTAMENTO, los cambios no se reflejarán en la tabla INFO_DEPTOS

Una vista sí “contiene” siempre los datos más actuales (*se verá*)

Inserción (y 4): filas resultado de una consulta



• Orden DELETE– Elimina filas completas de una tabla– Sólo una tabla en cláusula FROM– Cláusula WHERE para seleccionar las filas que eliminar

Si no hay WHERE, se eliminan todas las filas La tabla permanece, pero queda vacía

DELETE FROM Empleado ; todas las filasDELETE FROM Empleado WHERE apellido=‘Bojórquez’; 0 filas

DELETE FROM Empleado WHERE nss=‘123456789012’ ; 1 fila

DELETE FROM EmpleadoWHERE nd IN ( SELECT numerod FROM Departamento

WHERE nombre=‘Investigación’) ; 4 filas

• Propagación de eliminaciones – Según acciones de mantenimiento de la Integridad Referencial especificadas

con LDD en los CREATE TABLE (esquema de BD)

Eliminación de datos



• Orden UPDATE– Modifica valores de columnas en una o más filas de una tabla– Se modifican filas de una sola tabla a la vez– Cláusula SET especifica columnas que modificar y nuevos valores– Cláusula WHERE para seleccionar filas que actualizar

Si no hay WHERE, se aplica la modificación a todas las filas

* Para el proyecto 10, cambiar el lugar a Belén y el nº de depto controlador al 5UPDATE Proyecto SET lugarp = ‘Belen’, númd = 5WHERE numerop=10 ;

• Propagación de modificaciones– Si cambia un valor de clave candidata, este cambio se propaga a

valores de clave ajena de filas de otras tablas, si así se especificó en las acciones de mantenimiento de la Integridad Referencial en la definición de la tabla con CREATE TABLE

Actualización de datos



• Modificación de varias filas a la vez con UPDATE

* Conceder a todo empleado del departamento de Investigación un aumento salarial del 10% UPDATE Empleado SET salario = salario*1.1WHERE nd IN (SELECT númerod

FROM DepartamentoWHERE nombred=‘Investigación’) ;

• NULL o DEFAULT como nuevo valor de una columnaUPDATE Empleado SET salario = DEFAULT;UPDATE Empleado SET nssjefe = NULLWHERE ... ;

Actualización (y 2)



• Esquema de Base de Datos Relacional- Agrupa tablas y otros elementos, de una misma aplicación- 1as versiones de SQL: todas las tablas dentro de un esquema único y

global a todas las aplicaciones que accedían a la BD

• Orden CREATE SCHEMA: definición/creación de esquemasCREATE SCHEMA <nombre de esquema>

AUTHORIZATION <identificador de autorización><nombre de esquema> identifica el esquema<identificador de autorización> usuario/cuenta propietaria del esquema

CREATE SCHEMA Compañía AUTHORIZATION JSILVA ;–A continuación puede especificarse las definiciones de los elementos

contenidos en dicho esquema

• Elementos del esquema:Tablas, Vistas, Dominios, Autorizaciones, Restricciones, etc.

LDD: Definición de datos



• Conjunto nombrado de esquemas de BD en un entorno SQL

Contiene un esquema especial, INFORMATION_SCHEMA, que almacena datos sobre la definición de todos los elementos de todos los esquemas existentes en el catálogoEl Diccionario de Datos (Data Dictionary) de ORACLE se corresponde con el INFORMATION_SCHEMA del estándar SQL-92

- Es posible compartir elementos (dominios, etc.) entre diferentes esquemas del mismo catálogo

- Sólo pueden definirse restricciones de integridad referencial entre tablas que existan en esquemas dentro del mismo catálogo

• Concepto incorporado en la versión SQL-92 del estándar

Catálogo de base de datos relacional



• Orden CREATE TABLE– Define (crea) una tabla: nombre, columnas y restricciones

– Nombre único dentro del esquema

– Para cada Columna...nombre,tipo de datos (dominio)restricciones de columna

– Restricciones de tabla...de clave candidata,de integridad de entidad, de integridad referencial, o restricciones de otro tipo

LDD: definición de tablas


CREATE TABLE Empleado (nombre ...apellido ...nss ...nif ...fechan ...direccion ...sexo ...salario ...nssjefe ...nd …

);


• Indicación del esquema al que pertenece una tabla– Esquema Explícito

CREATE TABLE Compañia.Empleado ...– Esquema Implícito en el contexto

CREATE TABLE Empleado ...

• Ordenamiento de columnas y filas – Columnas ordenadas tal como aparecen en CREATE TABLE– Las filas no están ordenadas

• Las tablas creadas con CREATE TABLE son tablas BASE– El SGBD las almacena físicamente en algún fichero de la BD




• Especificación del tipo de datos de una columna1. Especificar directamente el tipo de datos tras nombre de la columna

CREATE TABLE Empleado (nombre VARCHAR(15) ... ... );

2. Definir un dominio y usar su nombre como tipo de datos– Facilita cambio del tipo de datos usado por muchas columnas – Esquema más comprensible

CREATE DOMAIN Nombres VARCHAR(15);...CREATE TABLE Empleado (

nombre NOMBRES ... ... );




• Numéricos– Enteros y Reales

· INTEGER (también INT), SMALLINT,· FLOAT, REAL, DOUBLE PRECISION

– Con formato· DECIMAL(p,e) ( también DEC(p,e) ó NUMERIC(p,e) )

p: precisión, e: escala. El valor por omisión de la escala es e = 0

• Cadena de caracteres– Longitud fija CHAR(n) ( n: nº caracteres ) – Longitud variable VARCHAR(n) ( n: máximo nº caracteres )

• Cadena de Bits– Longitud fija BIT(n) (n: nº bits)– Longitud variable BIT VARYING(n) n:máx nº bits. Por omisión n=1

LDD: tipos de datos



• Temporales· DATE (10 posiciones) = YEAR, MONTH, DAY (yyyy-mm-dd)

· TIME (8 posiciones) = HOUR, MINUTE, SECOND (hh:mm:ss)

– Sólo permitidas fechas y horas válidas· TIMESTAMP (marca de tiempo)

– DATE, TIME, fracciones de segundo y desplazamiento respecto al huso horario estándar (WITH TIME ZONE)

· INTERVAL– Período de tiempo, para incrementar/decrementar el valor actual

de una fecha, hora o marca de tiempo– Se califica con YEAR/MONTH ó DAY/TIME para indicar su naturaleza

LDD: tipos de datos



CREATE DOMAIN <nombre dominio> <tipo de datos>[ DEFAULT <valor defecto> ][ <lista de definición de restricciones de dominio> ] ;

- <tipo de datos>: uno de los proporcionados por el SGBD (built-in)- <valor defecto>: (opcional)

- Especifica el valor por omisión para columnas definidas de este dominio- Será asignado a cada columna con dicho dominio, si no tiene ya su propia

cláusula DEFAULT- <lista de definición de restricciones de dominio>: (opcional)

- Restric. Integridad que se aplican a toda columna definida sobre el dominio - Cada RI puede tener un nombre: cláusula CONSTRAINT <nombre_RI>

* Ejemplo: enumeración de posibles valores componentes del dominioCREATE DOMAIN Color VARCHAR(8) DEFAULT ‘sinColor’CONSTRAINT color_validoCHECK (VALUE IN ( ‘rojo’, ‘amarillo’, ‘azul’, ‘verde’, ‘sinColor’ ) ) ;

LDD: definición de dominios de datos



• Especificación de restricciones de columna–Cláusula NULL o NOT NULL

Opción de nulo: indica si una columna puede contener o no NULLCREATE TABLE Empleado (...

nombre VARCHAR(15) NOT NULL, ... );Por omisión, se asume NULLLa restricción NOT NULL es obligatoria para columnas componentes de una clave primaria

–Cláusula DEFAULT <valor>Valor por omisión (o por defecto)

CREATE TABLE Empleado ( ...salario DECIMAL(5,2) DEFAULT 1000 NULL,... );

Si una columna no tiene DEFAULT, su valor por defecto es...– El de su dominio, si su tipo es un dominio que incluye DEFAULT– NULL en cualquier otro caso, siempre que la columna permita NULL




• Especificación de restricciones de tabla–Cláusula PRIMARY KEY (<lista_columnas>)

Columnas que componen la clave primaria

–Cláusula UNIQUE (<lista_columnas>)Columnas que forman una clave alternativa

–Cláusula FOREIGN KEY (<lista_columnas>) REFERENCES <tabla>(<lista_columnas>)

Columnas clave externa (Integridad Referencial)SQL-92 permite que una clave externa se refiera a una clave primaria o una clave alternativa

–Cláusula CHECK (<expresión>)Condición sobre los valores de las columnas que debe cumplir toda fila de la tabla




CREATE TABLE Empleado (nombre VARCHAR(15) NOT NULL, apellido VARCHAR(15) NOT NULL, nss CHAR(12) NOT NULL,nif CHAR(9) NOT NULL,fechan DATE NULL,direccion VARCHAR(30) ,sexo CHAR(1) ,salario DECIMAL(5,2) DEFAULT 1000 NULL,nssjefe CHAR(12) ,nd NUMERIC(2) NOT NULL,PRIMARY KEY ( nss ),UNIQUE ( nif ),CHECK ( nssjefe <> nss ),CHECK ( sexo IN (‘H’, ‘M’) ),FOREIGN KEY (nssjefe) REFERENCES Empleado(nss),FOREIGN KEY (nd) REFERENCES Departamento(numerod)

);




• Especificación de restricciones de tabla (cont.)

- Dar nombre a una restricción es opcional, pero muy conveniente

CONSTRAINT <nombre_RI> <restricción>

- El nombre de restricción debe ser único dentro del mismo esquema- Identifica una restricción, por si después debe ser eliminada o

sustituida por otra

CREATE TABLE Empleado (...,CONSTRAINT pk_empleado PRIMARY KEY ( nss ),CONSTRAINT nif_unico UNIQUE ( nif ),CONSTRAINT jefe_ok CHECK ( nssjefe <> nss ),... );




• Especificación de restricciones de tabla (cont.)- Si la restricción afecta sólo a una columna, puede especificarse en

la definición de dicha columna (en la misma línea)- Por ejemplo, si una clave externa no es compuesta, no se necesita la

cláusula FOREIGN KEY

CREATE TABLE Empleado (nombre VARCHAR(15) NOT NULL, nss CHAR(12) PRIMARY KEY,nif CHAR(9) NOT NULL UNIQUE,nssjefe CHAR(12) NULL REFERENCES Empleado(nss),nd NUMERIC(2) NOT NULL REFERENCES Departamento(numerod),...,CONSTRAINT jefe_ok CHECK ( nssjefe <> nss ),... );




• Acciones de mantenimiento de la integridad referencialCláusulas ON DELETE <acción>

y ON UPDATE <acción>

<acción> ∈ { NO ACTION, CASCADE, SET NULL, SET DEFAULT }

CREATE TABLE Empleado (...,CONSTRAINT jefe_emp FOREIGN KEY (nssjefe)

REFERENCES Empleado(nss)ON DELETE SET NULLON UPDATE CASCADE,

CONSTRAINT dep_emp FOREIGN KEY (nd)REFERENCES Departamento(numerod)

ON DELETE NO ACTION ON UPDATE CASCADE

);




• Una vista es una tabla derivada de otras tablas• Son tablas virtuales, pues no necesariamente existen en forma física

• Sentencia de definición o creación de una vistaCREATE VIEW <nombre_vista> [ (<lista_nombres_columnas>) ]

AS <consulta_de_definición>• La consulta de definición…

– determina el contenido de la vista– contiene las tablas base: tablas o vistas de las que se deriva la

vista (también llamadas tablas de definición)

CREATE VIEW Familiar_de_Empleado (empleado, familiar, parentesco)AS SELECT nombre, nombre_familiar, parentesco

FROM Empleado, FamiliarWHERE nss = nsse;

LDD: definición de vistas



• Por defecto, la vista ‘hereda’ los nombres de las columnas...- seleccionadas desde las tablas base- siempre que ninguna columna sea el resultado de una operación

aritmética o función de agregadosCREATE VIEW Empleado_en_Proyecto

AS SELECT nombre, apellido, nombrep, horasFROM Empleado, Proyecto, Trabaja_enWHERE nss = nsse AND nump = numerop ;

• Definición de nuevos nombres para columnas de la vistaCREATE VIEW Info_Depto (nombre_depto, num_de_emps, sal_total)

AS SELECT nombred, COUNT(*), SUM(salario)FROM Departamento, EmpleadoWHERE numerod = ndGROUP BY nombred ;



nombres que hereda la vista


• Un estado de la vista EMPLEADO_EN_PROYECTO



Empleado_en_Proyecto

............

10.0ProductoZVizcarraFederico

20.0ProductoYBarcelóJosefa

10.0ProductoYVizcarraFederico

20.0ProductoXBarcelóJosefa

07.5ProductoYSilvaJosé

40.0ProductoZNietoRamón

32.5ProductoXSilvaJosé

horasnombrepapellidonombre


• Las vistas pueden utilizarse como mecanismo de...- Simplificación de consultas- Seguridad (*se verá en el tema de seguridad*)

- Adaptación de la información a las necesidades de cada usuario o grupo de usuarios

• Característica fundamental de las vistasActualización Permanente- El responsable de esta característica es el SGBD

La vista no se crea cuando se define, sino cuando se consultaUna vista no “contiene información”, sino que “deja ver

información” almacenada en sus tablas base




• El SGBD traduce cualquier sentencia SQL sobre la vista a una expresión equivalente sobre sus tablas base: reemplaza el nombre de la vista por su consulta de definición y ejecuta

CREATE VIEW Veterano AS SELECT nombre, nif, nss, fechan, nd FROM Empleado WHERE fechan<’01/01/1970’;



TraducciónSentencia de usuario

INSERT INTO EMPLEADO (nombre, nif, nss, fechan, nd)VALUES (‘Eva’ ‘12345678E’, ‘123456789012’, ‘14/11/1947’, 4);

INSERT INTO VETERANO VALUES (‘Eva’, ‘12345678E’, ‘123456789012’, ‘14/11/1947’, 4);

DELETE FROM EMPLEADO WHERE fechan < ‘01/01/1970’ AND nif = ‘12345678E’;

DELETE FROM VETERANOWHERE nif = ‘12345678E’;

UPDATE EMPLEADO SET nd=1WHERE fechan < ‘01/01/1970’ AND nd=2;

UPDATE VETERANO SET nd=1WHERE nd=2;

SELECT nombre, nif, nss, fechan, nd FROM EMPLEADOWHERE fechan < ‘01/01/1970’ AND nombre LIKE ‘G%’;

SELECT * FROM VETERANOWHERE nombre LIKE ‘G%’;


• Las vistas no tienen ninguna limitación en operaciones deconsulta

• El usuario no distingue si el elemento al que accede es una tabla base o una vista

* Nombres de los empleados y de sus hijos/asSELECT empleado, familiar

FROM Familiar_de_empleado WHERE parentesco LIKE ‘Hij_’ ;* Datos del departamento ‘Investigación’

SELECT * FROM Info_Depto WHERE nombre_depto=‘Investigación’ ;* Nombres y apellidos de los empleados que trabajan en el proyecto 'ProductoX'

SELECT nombre, apellido, nombrepFROM Empleado_en_ProyectoWHERE nombrep=‘ProductoX’ ;

LDD: consulta a través de vistas



• La actualización de datos a través de vistas tiene algunas limitaciones

- Por un lado, actualizar a través de una vista definida sobre varias tablas base suele dar problemas, pues puede haber ambigüedad

UPDATE Empleado_en_Proyecto SET nombrep = ‘ProductoZ’WHERE apellido=‘Silva’ AND nombre=‘José’

AND nombrep=‘ProductoX’;

Esta modificación puede traducirse a dos actualizaciones distintasde las tablas base de la vista (EMPLEADO, PROYECTO y TRABAJA_EN), como se muestra en la siguiente diapositiva…

LDD: modificación a través de vistas



UPDATE Trabaja_en SET nump = (SELECT numerop FROM ProyectoWHERE nombrep = ‘ProductoZ’)

WHERE nsse = (SELECT nss FROM EmpleadoWHERE apellido = ‘Silva’ AND nombre = ‘José’)

AND númp = (SELECT numerop FROM ProyectoWHERE nombrep = ‘ProductoX’) ;

☺ Modifica los vínculos en TRABAJA_EN: cada fila que relacionaba lasfilas de ‘José Silva’ en EMPLEADO y de ‘ProductoX’ en PROYECTO, pasaa relacionar tal empleado con la fila ‘ProductoZ’ de PROYECTO

UPDATE Proyecto SET nombrep = ‘ProductoZ’WHERE nombrep = ‘ProductoX’ ;

Produce igual efecto que pero modifica nombrep en PROYECTO: al calcular la vista, mostrará ‘ProductoZ’ para todos los que antesaparecían con ‘ProductoX’




- Por otro lado, algunas actualizaciones a través de vistas carecen de sentido

UPDATE Info_deptoSET sal_total = 100000WHERE nombred=‘Investigación’ ;

sal_total se define como la suma de salarios individuales de los empleados y muchas actualizaciones de las tablas base satisfarían esta actualización

Así que no se garantiza que “toda vista sea actualizable”




• Una vista sería actualizable si...- Implicara una única actualización posible de las tablas

base, o bien- Hubiera varias actualizaciones posibles, pero

existiera un procedimiento específico de actualización de tablas base, tal que...· El usuario pudiera elegir el procedimiento, especificándolo en la definición de la vista, o bien

· El SGBD pudiera elegir el procedimiento, según la actualización más probable




• En general...- Una vista con una sola tabla base

SÍ es actualizable si sus columnas contienen la claveprimaria u otra clave candidata de la tabla base

– Pues se establece una correspondencia entre cada fila de la vista y una única fila de la tabla base

- Una vista definida sobre varias tablas mediante reuniones

NO es actualizable

- Una vista definida mediante agrupación y funciones agregadas

NO es actualizable




• Opción de verificación de vistasCREATE VIEW Emp_Precario AS SELECT nombre, apellido, nss, nif, salario, nd

FROM Empleado WHERE salario < 900 ;*¿Qué pasaría al ejecutar estas sentencias?INSERT INTO Emp_Precario VALUES (‘Dimas’, ‘Pi', ‘111222333444’, ‘12121212D’, 1025, 1);UPDATE Emp_Precario SET salario = 950 WHERE nif=‘12345678E’;

• Cláusula WITH CHECK OPTION- En la definición de toda vista actualizable que se vaya a utilizar

para la modificación de datos- Indica al SGBD que debe comprobar cada INSERT y UPDATE sobre la

vista, y rechazarlo si su realización implicara que la fila nueva o modificada no cumpliera la condición de definiciónCREATE VIEW Emp_Precario AS SELECT nombre, apellido, nss, salario, nd

FROM Empleado WHERE salario < 900WITH CHECK OPTION ;




1. Estrategia de actualización de consultas de definición- Cada consulta sobre la vista se traduce a una consulta sobre las

tablas base- La vista se rellena de filas a partir de la ejecución de la consulta- Poco eficiente cuando la <consulta_de_definición> es compleja, con

tiempo de ejecución apreciable, y se aplican muchas consultassobre la vista en poco tiempo

2. Estrategia de materialización de vistas- 1ª consulta sobre la vista creación de tabla temporal física- Se conserva la tabla para posteriores consultas sobre la vista

- Necesaria estrategia para actualización incremental de la tabla temporal tras cualquier modificación sobre las tablas baseactualización permanente

- Si no se hace referencia a la vista tras un tiempo, el sistema la eliminará (y la recalculará en una consulta futura)

LDD: implementación de vistas



• Alteración de tablas:ALTER TABLE <nombre_tabla> ... ;

– Adición y Eliminación de Columnas– Modificación de la Definición de Columnas– Adición y Eliminación de Restricciones de Tabla

• Alteración de dominios:ALTER DOMAIN <nombre_dominio> ... ;

– Eliminación y Adición de valor por defecto– Eliminación y Adición de Restricciones de Dominio

LDD: Modificación de la estructura (alteración)de los elementos del esquema de base de datos



• Adición de una columna a una tabla ya existenteALTER TABLE <nombre_tabla> ADD <definición_columna> ;

– No está permitido NOT NULL en la definición de una nueva columna(si es necesaria esta restricción, podrá establecerse después)

* Añadir una columna a EMPLEADO para contener el puesto de trabajoALTER TABLE Empleado ADD puesto VARCHAR(12);

Todas las filas de EMPLEADO tendrán puesto a NULL

– Para introducir un valor para la columna, en cada fila existente:Especificar la cláusula DEFAULT al añadir la columna:

ALTER TABLE Empleado ADD puesto VARCHAR(12) DEFAULT ‘aprendiz’;Utilizar después una orden UPDATE

LDD: alteración de tablas



• Eliminación de una columna de una tablaALTER TABLE <nombre_tabla> DROP <nombre_columna> <opción>;

<opción> puede ser...CASCADE: elimina la columna y toda restricción o vista que le hace referencia

RESTRICT: sólo elimina la columna si ninguna vista ni restricción le referencia

* Eliminación de la columna dirección de la tabla EMPLEADOALTER TABLE Empleado DROP direccion CASCADE;ALTER TABLE Departamento DROP numerod <opción>;– Si <opción> = RESTRICT: no elimina la columna ‘numerod’, pues existe una columna

‘EMPLEADO.nd’ que le hace referencia– Si <opción> = CASCADE: elimina la columna y la restricción de integridad referencial que vincula

‘EMPLEADO.nd’ con DEPARTAMENTO. La columna ‘EMPLEADO.nd’ no es eliminada, pero deja de ser clave ajena




• Modificación de la definición de una columnaALTER TABLE <nombre_tabla> ALTER <nombre_columna> <acción> ;

<acción> indica la modificación que se desea realizar...

– Eliminación de la cláusula DEFAULT existenteALTER TABLE Departamento ALTER nssdire DROP DEFAULT;

– Definición de un nuevo valor por omisiónALTER TABLE Departamento ALTER nssdire SET DEFAULT ‘123456789012’;




• Modificación de una restricción de tabla– La restricción que se desea modificar debe tener un nombre

– Eliminación de una restricción de tabla

ALTER TABLE <nombre_tabla> DROP CONSTRAINT <nombre_RI> <opción>;ALTER TABLE Empleado DROP CONSTRAINT jefe_emp CASCADE;

– Adición de una restricción de tabla

ALTER TABLE <nombre_tabla> ADD CONSTRAINT <nombre_RI> <definición_RI>;ALTER TABLE Empleado

ADD CONSTRAINT salario_ok CHECK (salario > 0);ALTER TABLE Empleado

ADD CONSTRAINT puesto_ok CHECK (puesto IS NOT NULL);




• Orden ALTER DOMAIN <nombre_dominio> <acción>;

<acción> indica la modificación que se desea realizar...

– Eliminación y Reemplazo del valor por omisiónALTER DOMAIN <nombre_dominio> DROP DEFAULT;ALTER DOMAIN <nombre_dominio> SET DEFAULT <valor>;

– Eliminación y Definición de nuevas restricciones de dominioALTER DOMAIN <nombre_dominio>

DROP CONSTRAINT <nombre_RI_dominio>;ALTER DOMAIN <nombre_dominio>

ADD [ CONSTRAINT <nombre_RI_dominio> ] <restricción>;

LDD: alteración de dominios



• Eliminación de una vista. Orden DROP VIEW– Destruye una tabla derivada, junto con su definición en el

INFORMATION_SCHEMA del catálogoDROP VIEW <nombre_vista> ;

• Eliminación de un dominio. Orden DROP DOMAIN – Destruye un dominio de datos, junto con su definición en el

INFORMATION_SCHEMA del catálogo

DROP DOMAIN <nombre_dominio> <opción> ;<opción> puede ser...

RESTRICT: destruye el dominio si no hay ninguna columna definida sobre élCASCADE: se elimina el dominio y toda columna definida sobre él pasa a

tener el tipo de datos sobre el que se había definido el dominio (este aspecto es ampliado en el tema “Integridad en sistemas de bases de

datos relacionales”)

LDD: eliminación de elementos del esquema



• Eliminación de una tabla. Orden DROP TABLE– Destruye una tabla base, junto con su definición en el

INFORMATION_SCHEMA del catálogoDROP TABLE <nombre_tabla> <opción>;

<opción> puede ser...RESTRICT: Destruye la tabla sólo si no se le hace referencia desde ninguna

otra tabla (clave ajena), ni es tabla base de una vistaCASCADE: Elimina la tabla junto con restricciones y vistas que la referencian

• Eliminación de un esquema. Orden DROP SCHEMA– Destruye un esquema de BD, junto con su definición en el

INFORMATION_SCHEMA del catálogoDROP SCHEMA <nombre_esquema> <opción>;

<opción> puede ser...RESTRICT: Destruye el esquema sólo si no contiene ningún elementoCASCADE: Elimina el esquema y las tablas, dominios y demás elementos

contenidos en el esquema

LDD: eliminación de elementos del esquema



2.5. Integridad en Sistemas de Bases de Datos Relacionales

Tipos de restricciones

2.5.1 Reglas de integridad: consideraciones generales y componentes

2.5.2 Reglas de integridad en SQL-92Reglas de integridad de dominioReglas de integridad de tablaReglas de integridad generales y Disparadores

2.5.3 Comprobación de restricciones


1. Nos interesan las reglas de integridad específicas de una BD (reglas del negocio), además de RI Entidad, RI Referencial...

2. Veremos las RI definidas sobre tablas base- Por estar restringidas a contener datos correctos (reflejar la realidad)

La regla “los títulos de las películas son únicos” se aplica a la tabla basePELICULA, y también a cualquier vista definida sobre ésta

- ¿Podemos definir RI sobre una vista (tabla derivada)?• Sería deseable• La vista heredaría toda RI de sus tablas base y podría añadir

nuevas (ejemplo: clave primaria o alternativa nueva para la vista) Sólo consideraremos RI sobre tablas base (por simplicidad)

2.5.1 Reglas de integridad

Consideraciones generales

• Integridad: consistencia o corrección de datos en la base de datos• Las reglas de integridad (RI) no son específicas para cada usuario


3. Nos interesa soporte de RI declarativoNo nos centraremos en...- Procedimientos o funciones almacenados,- Disparadores (triggers)

4. Una BD en un estado de integridad es correcta: • No viola ninguna RI conocida por el SGBD, es decir,• Satisface AND lógico de todas las RI definidas en su esquema

5. La integridad es importante en...• DISEÑO (estructuras de datos y reglas de integridad adecuadas)• EJECUCIÓN (corrección de la información)

6. RI son mantenidas en el INFORMATION_SCHEMA del catálogoSubsistema de Integridad del SGBD:

• controla operaciones de usuario (INSERT,UPDATE,DELETE...)• para asegurar que NO violan las reglas de integridad

2.5.1 Reglas de integridadConsideraciones generales (y 2)


Nombre actor_cache_ok- Regla almacenada en INFORMATION_SCHEMA del catálogo con ese nombre- Aparecerá en diagnósticos, producidos por el sistema como respuesta a

intentos de violación de la regla (mensajes de error al usuario)

Restricción NOT EXISTS ( SELECT * FROM ACTOR WHERE cache ≤ 0 )- Expresión booleana Restricción de Integridad ⊆ Regla de Integridad- La regla ... se satisface ⇔ la restricción es TRUE

es violada ⇔ la restricción es FALSE

Respuesta a un intento de violación de la regla- Indica al SGBD qué hacer si se intenta una operación que viola la RI- Por defecto RECHAZAR, que implica...

· Deshacer los posibles daños causados por la operación· Mostrar información de diagnóstico (mensaje)

- Podría ser un procedimiento de complejidad arbitraria: tratarErr(...)

2.5.1 Reglas de integridadComponentes de una RI


• Creación de una regla de integridad... (en cualquier momento)

SGBD comprueba: ¿el estado actual de la BD satisface RI?- No RI rechazada- Sí RI aceptada

- Es almacenada en el INFORMATION_SCHEMA del catálogo- La regla es activada (entra en vigor)

* Para la RI del ejemplo, actor_cache_ok , el SGBD controlará todo INSERT INTO ACTOR… y UPDATE ACTOR SET cache =…

• Destrucción de reglas de integridad- el sistema elimina su definición del INFORMATION_SCHEMA

• Las RIs pueden restringir los valores legales de...- Dominio- Tabla, Columna- Base de datos

2.5.1 Reglas de integridadCreación, destrucción y tipos


1. Reglas de integridad de Dominio– Asociadas a un dominio de datos específico– Es una expresión de complejidad arbitraria que define un dominio

2. Reglas de integridad de Tabla– RIs de complejidad arbitraria incluidas en la definición de una tabla– Pueden ser restricciones de Columna

restricciones de Clave Candidatarestricciones de Clave Externarestricciones de Comprobación

Una tabla vacía cumple cualquier RI de tabla,aunque esa RI sea “esta tabla no puede estar vacía”

3. Reglas de integridad Generales– RIs de complejidad arbitraria no incluidas en la definición de ninguna tabla– Son otro elemento más de la BD, al mismo nivel que una tabla o vista

2.5.2 Reglas de integridad en SQL-92Categorías de reglas de integridad


• Es útil ver la base de datos sujeta a una “RI gigante”...resultado del AND de todas las RI...- generales- de tabla- de dominio aplicadas a cada columna de las tablas

Significado formal de la base de datos

• Una regla de integridad es independiente de cualquier aplicación específica que acceda a la base de datos

- No contiene parámetros ni variables host(referencias a variables de los programas de aplicación)

2.5.2 Reglas de integridad en SQL-92Otras consideraciones


• Definición del conjunto de valores componentes de un dominio:

- Enumeración de valores posibles: (‘marron’,‘gris’, ‘azul’, ‘verde’, ‘negro’)- Expresión de definición: edad ≥ 0 AND edad ≤ 120

• RI como parte de la sentencia de definición del dominioCREATE DOMAIN <nombre dominio> [ AS ] <tipo de datos>

[ DEFAULT <valor defecto> ][ [ CONSTRAINT <nombre restricción> ] CHECK (<condición>) ]+ ;

- <valor defecto> suele contener un literal (perteneciente al dominio) o NULL

• Aplicada a cada columna (de cualquier tabla) definida sobre el dominio

CREATE DOMAIN Color_ojos AS VARCHAR(10) DEFAULT ‘marron’CONSTRAINT color_valido CHECK ( VALUE IN (‘marron’,‘gris’,‘azul’,‘verde’,‘negro’) );

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Dominio


• NOT NULL no es una restricción de dominio válidaCREATE DOMAIN Estado_civil AS CHAR(1) NOT NULL IncorrectoCONSTRAINT estado_civil_ok CHECK ( VALUE IN (‘S’, ‘C’, ‘V’, ‘D’) ) ;CREATE DOMAIN Estado_civil AS CHAR(1)CHECK (VALUE IS NOT NULL) ☺ CorrectoCONSTRAINT estado_civil_ok CHECK ( VALUE IN (‘S’, ‘C’, ‘V’, ‘D’) ) ;

• Alteración de un dominioALTER DOMAIN <nombre dominio> <acción>... ;– Permite añadir y eliminar restricciones de integridad de dominio y

valor por defecto– Explicado en el tema anterior

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Dominio (2)


• Eliminación de un dominioDROP DOMAIN <nombre dominio> { RESTRICT | CASCADE } ;

Opción RESTRICT- La eliminación falla si el dominio es referenciado en cualquier definición de

columna en una tabla, de vista o restricción de integridad- En otro caso, éxito: el descriptor del dominio es eliminado del

INFORMATION_SCHEMA del catálogoOpción CASCADE

- El dominio es eliminado del INFORMATION_SCHEMA, junto con toda vista y RI cuya definición hace referencia al dominio

- Las RI de dominio asociadas no son eliminadas, sino que cada columna definida sobre el dominio...

·Es definida directamente sobre el tipo de datos subyacente al dominio·Si no tiene DEFAULT explícito, toma el del dominio (si éste lo tenía)·Hereda toda restricción de integridad asociada al dominio,

- convertida en una restricción de tabla,- sustituyendo VALUE por el nombre de la columna

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Dominio (y 3)


• Restricción asociada a una tabla específica- No existe si la tabla no existe y- Eliminar la tabla implica eliminar la RI

• RI especificada dentro de CREATE TABLECREATE TABLE <nombre tabla> ( <lista de elemento de tabla> ) ;donde elemento puede ser:- Definición de columna, que puede incluir RIs de columna- Definición de... (precedida o no de CONSTRAINT <nombre restricción>)

· Restricción de clave candidata· Restricción de clave externa· Restricción de comprobación (CHECK)

• RI añadida/eliminada con ALTER TABLE <nombre tabla>...

• Toda RI de tabla es comprobada inmediatamente:• Una operación de modificación sobre la tabla incluye el chequeo de

todas sus RI (como paso final de la operación)+(una posible) acción

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla

puede hacer referencia a otras tablas,

además de a la que la incluye


1. Definición de Columna - RI de Columna• Especificación del tipo de datos o dominio y otras RI de columna• No necesita sentencia de creación explícita: es parte de la definición

de columna, dentro de la sentencia de creación de la tablaCREATE TABLE Actor ( nombre VARCHAR(30) NOT NULL,

cache INT(9) DEFAULT 2000 NOT NULL,ojos Color_ojos NOT NULL,agencia CHAR(4), ...) ;

• Si se especifica un dominio para una columna, la comprobación es derivada hacia la comprobación de la restricción de dominio

• Una RI de columna se destruye al eliminar la columna de la tabla

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla (2)


2. Definición de Restricción de Clave Candidata• Clave Primaria PRIMARY KEY (<lista columnas>) incluye RI Entidad• Clave Alternativa UNIQUE (<lista columnas>)

3. Definición de Restricción de Clave ExternaFOREIGN KEY (<lista columnas>) REFERENCES <tabla> (<lista columnas>)

[ ON DELETE { NO ACTION | CASCADE | SET DEFAULT | SET NULL } ][ ON UPDATE { NO ACTION | CASCADE | SET DEFAULT | SET NULL } ]

Posibles Acciones de Mantenimiento de la Integridad Referencial(explicadas en el tema anterior)



• Recordemos sobre las claves externas que…• Cualquier (combinación de) columna(s) puede ser clave externa• SQL-92 permite que una clave externa (FK) se refiera a una clave

candidata (CK): clave primaria o clave alternativa• Una clave externa y la clave candidata a la que referencia…

– Deben contener el mismo nº de componentes (columnas) y– Las columnas “correspondientes” deben estar definidas sobre el

mismo dominio o tipo de datos• Referencia desde una FK de la tabla T2 a una CK de la tabla T1...

» Asegurar que cada T2.FK contiene un valor existente en T1.CKes el problema de la integridad referencial

• Pueden existir ciclos referenciales y auto-referencias• SQL-92 permite (¡por supuesto!) que una FK pueda contener NULL

– salvo si se especifica NOT NULL para dicha FK en el CREATE TABLE



4. Definición de Restricción de Comprobación (CHECK)• Regla que se refiere únicamente a la tabla que la contiene• Puede especificar restricciones adicionales para una columna

*El caché de un actor siempre está entre 300 y 1200€CREATE TABLE ACTOR ( ...,CONSTRAINT actor_cache_ok

CHECK ( cache ≥ 300 AND cache ≤ 1200 ),... );

• Puede definir restricciones que involucran varias columnas*Toda película se estrena después de finalizar su rodajeCREATE TABLE PELICULA ( ...,CONSTRAINT pelicula_fechas_ok

CHECK ( fecha_fin_rodaje < fecha_estreno ),... );

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla (y 5)


• Predicado que expresa una condición que la BD debe satisfacer siempre

• Puede involucrar cualquier número de columnas de cualquier cantidad de tablas

• Es un elemento de BD, independiente de tablas y vistas existentes

• Especifica restricciones de integridad que pueden noser...

de clave (primaria o alternativa)de integridad referencial (clave externa)

• Tiene un nombre y consta de una condición (CHECK)

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad Generales (Asertos)


• Satisfacción y violación de una RI general– Si alguna fila de la BD hace falsa la condición, el aserto es violado– Un estado de la BD satisface un aserto si ninguna (combinación

de) fila(s) de dicho estado viola la condición que incluye

• Creación de una RI generalCREATE ASSERTION <nombre restricción> nombre obligatorio

CHECK ( <condición> ) ;

• Eliminación de una RI generalDROP ASSERTION <nomRestricción> ; Sin opción RESTRICT o CASCADE– Elimina el aserto del INFORMATION_SCHEMA del catálogo

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad Generales (2)


• Normalmente, la <condición> se expresa en negativo:todo X satisface Y ≡ ningún X satisface NO( Y )

*Todo actor representado por la agencia 1 debe cobrar 300€ o másCREATE ASSERTION RI1_age1_cache

CHECK (NOT EXISTS (SELECT * FROM Actor WHERE agencia=1 AND cache<300)) ;*La paga mínima de los actores que actúan en una película es de 15.000€CREATE ASSERTION RI2_paga_minima

CHECK 15000 ≤ (SELECT MIN(paga) FROM Actua_en) ;*Toda agencia representa a un máximo de 40 actoresCREATE ASSERTION RI3_num_actores_age

CHECK (NOT EXISTS (SELECT * FROM ActorGROUP BY codAgeHAVING COUNT(*) > 40)) ;



*Todo actor debe haber participado al menos en una películaCREATE ASSERTION RI4_actor_en_pelicula

CHECK (NOT EXISTS (SELECT * FROM ActorWHERE codA NOT IN (SELECT actor FROM Actua_en)));

*Todo actor no protagonista de una película cobra menos que cualquier protagonistaCREATE ASSERTION RI5_paga_actores

CHECK (NOT EXISTS (SELECT * FROM Actua_en ACT WHERE papel<>‘protagonista’ AND paga >= ANY (SELECT paga FROM Actua_en PROTA

WHERE ACT.film=PROTA.filmAND PROTA.papel=‘protagonista’));

*Debe de existir al menos una distribuidora de películasCREATE ASSERTION RI6_existe_distribuidora

CHECK ( 0 < SELECT COUNT (*) FROM Distribuidora ) ;este aserto...

- debe crearse una vez que ya exista alguna fila en DISTRIBUIDORA- una operación DELETE puede violarlo, pero nunca lo hará un INSERT



• Algunas RI generales pueden ser expresadas como RI de tabla

*El código de los guiones es único (≡ si hay 2 guiones con igual código, son el mismo)CREATE ASSERTION RI7_guion_codigo_unicoCHECK ( NOT EXISTS (SELECT * FROM Guion G1WHERE G1.codG IN (SELECT codG FROM Guion G2

WHERE G1.titulo<>G2.titulo OR G1.resumen<>G2.resumen OR G1.nomAutorPpal<>G2.nomAutorPpal OR G1.fechaFin<>G2.fechaFinOR G1.fechaEntrega<>G2.fechaEntrega) ) );

este aserto...- Equivale a especificar UNIQUE( codG ) en el CREATE TABLE Guion (…)



• Y viceversa: algunas RI de tabla pueden ser expresadas como RI generales

- Excepto la parte de una RI de clave externa que indica la acción de mantenimiento de la integridad referencial (ON DELETE… ON UPDATE…)

*Los actores y películas anotados en la tabla ACTUA_EN deben existirCREATE ASERTION RI8_actua_en_okCHECK (NOT EXISTS (SELECT * FROM Actua_en

WHERE actor NOT IN (SELECT codA FROM Actor)OR film NOT IN (SELECT codP FROM Pelicula)));

este aserto...- Equivale a especificar...

FOREIGN KEY (actor) REFERENCES Actor(codA)...y FOREIGN KEY (film) REFERENCES Pelicula(codP)...

... dentro del CREATE TABLE Actor (...)

2.5.2 Reglas de integridad en SQL-92Reglas de Integridad Generales (y 6)


• En muchos casos conviene especificar una acción que ejecutar tras la violación de una restricción:

- Abortar la transacción que provoca la violación, o- Informar de ello al usuario (mensaje), o - Ejecutar cierto procedimiento, o- Realizar otras actualizaciones en la base de datos...

Esto se consigue mediante los disparadores o triggers

• Un disparador se ejecuta de forma automática como efecto secundario de cierta modificación de la BD

• Los SGBDR usan ampliamente los disparadores, pero no formaron parte del estándar hasta la versión SQL:1999

cada SGBDR los implementó con su propia sintaxis

Los disparadores SQL:1999 son similares a los de Oracle

2.5.2 Reglas de integridad en SQL-92Disparadores - versión SQL:1999


• Para diseñar un disparador, se debe especificar :- Las condiciones en las que se debe ejecutar:

Evento que causa la comprobación del disparadorCondición que se debe cumplir para ejecutarlo

- La Acción que se realizará cuando se ejecute• Es el modelo de disparadores evento-condición-acción• La BD almacena los disparadores, por lo que...

- son persistentes y- están accesibles para todas las operaciones de BD

• El SGBD ejecuta automáticamente un disparador cada vez que ocurre el evento especificado y se cumple la condición correspondiente

• La ejecución del disparador se considera parte de la ejecución de la operación que provoca su activación

2.5.2 Reglas de integridad en SQL-92Disparadores - versión SQL:1999 (2)


Granularidad

Parametrización

Condición

Acción

Evento

• Definición de un disparadorCREATE TRIGGER <nombre_disparador>{ BEFORE | AFTER } { INSERT | UPDATE [OF <lista columnas>] | DELETE }ON <nombre_tabla>

[ REFERENCING OLD [ ROW | TABLE ] [ AS ] <nombre>[ NEW [ ROW | TABLE ] [ AS ] <nombre> ] ]

[ FOR EACH ROW | FOR EACH STATEMENT ]

[ WHEN <condición> ]

BEGIN ATOMIC ...END;



* Antes de que un usuario elimine una o varias filas de la tabla PELICULA, el sistema debe anotar dicha acción en una tabla DIARIO_BORRADOS, indicando el usuario y el momento concreto en el que se realiza dicha operación.

CREATE TRIGGER anotacion_borrado_peliculaBEFORE DELETE ON PELICULAFOR EACH STATEMENTINSERT INTO Diario_Borrados

VALUES( ‘PELICULA’, CURRENT_USER, CURRENT_TIMESTAMP);

• Si la acción del trigger sólo consiste en una sentencia SQL, no es necesario utilizar las palabras reservadas BEGIN ATOMIC ni END

Este trigger está escrito en SQL:1999, y no en SQL de Oracle…



* Si un actor no protagonista de una película percibe una paga no inferior que la de un protagonista, asignarle el mínimo cobrado por un protagonista menos 1000€.

CREATE TRIGGER PagaNoProtaAFTER UPDATE OF paga ON ACTUA_ENREFERENCING NEW ROW AS nuevaFOR EACH ROW

WHEN nueva.papel <> ‘protagonista’ AND nueva.paga ≥ (SELECT MAX ( paga ) FROM ACTUA_EN

WHERE film = nueva.film AND papel = ‘protagonista’ )BEGIN ATOMIC

UPDATE ACTUA_EN SET paga = (SELECT MIN( paga ) - 1000

FROM ACTUA_EN WHERE film = nueva.film AND papel = ‘protagonista’ )

WHERE actor = nueva.actor AND film = nueva.filmEND;


En Oracle este trigger tendría el problema de la tabla mutanteSe verá en las prácticas de PL/SQL


• Los disparadores combinan los enfoques declarativo y procedimental

- El evento y la condición del disparador son declarativos- Su acción opera por procedimientos

• Comparación ASSERTION vs. TRIGGER- ASSERTION prohibe realizar una actualización que viola el aserto

(es decir, que hace FALSE la condición)

- TRIGGER puede permitir la actualización que cumple la condición (es decir, que viola una RI), pero ejecuta una acción (que puedereparar la violación, dejando consistente la BD)

Las condiciones especificadas en una y otro son inversas

2.5.2 Reglas de integridad en SQL-92Disparadores - versión SQL:1999 (y 6)


• SQL rechaza todo intento de INSERT o UPDATE que viole una especificación de tipo de datos

Ejemplo: introducción de valor CHAR en columna definida como INTEGER

- Una especificación de tipo de datos puede verse como una “forma primitiva” de restricción de integridad de dominio

- Una violación de una RI de dominio o de tipo de datos se detecta en tiempo de ejecución

• SQL rechaza todo intento de INSERT o UPDATE sobre una vista, si viola la condición de definición de la vista

- Siempre que se haya especificado la “opción de verificación” en la definición de la vista (WITH CHECK OPTION)

2.5.2 Reglas de integridad en SQL-92Características adicionales (pseudo-RIs)


• En general, el SGBD comprueba una RI de inmediato, como último paso de la ejecución de una sentencia SQL

– Si la RI es violada por la sentencia, ésta es cancelada y no tiene efecto sobre la base de datos

• A veces es necesario que ciertas restricciones no sean comprobadas hasta pasado un tiempo, pues si se hiciera de inmediato siempre fallarían

Ciclo referencial EMP DEP Inicialmente, EMP y DEP están vacíasCREATE TABLE EMP CREATE TABLE DEP( cod_emp ... ( cod_dep ... ,

depto ... , jefe ... ,FOREIGN KEY ( depto ) FOREIGN KEY ( jefe )

REFERENCES DEP ( cod_dep ) ... , REFERENCES EMP ( cod_emp )... ,... ) ; ... ) ;

- Con chequeo inmediato de las RI de clave externa (RI referencial), todo INSERT de una fila en EMP o en DEP fallaría, pues nunca encontraría la fila destino (referenciada) en la otra tabla…

2.5.3 Comprobación de restricciones


• En un momento dado, dentro de cierta transacción SQL, toda restricción de integridad debe estar en modo...

- INMEDIATE: será comprobada inmediatamente, o

- DEFERRED: será chequeada al final de la transacción (diferida)

• Para algunas restricciones de integridad, la comprobación diferida no tiene sentido:

• Restricciones de dominio y tipo de datos

• Restricción de columna NOT NULL y

• Restricciones de clave candidata (UNIQUE, PRIMARY KEY)

2.5.3 Comprobación de restriccionesModos de comprobación


• Una definición de RI puede incluir estas dos cláusulas…[ INITIALLY {IMMEDIATE | DEFERRED} ][ [ NOT ] DEFERRABLE ]

- Modo inicial de la RI: INITIALLY DEFERRED o INITIALLY IMMEDIATE– Especifica el modo en el que está la RI tras de ser definida (creada) y al

comienzo de cada transacción SQL

- Opción de cambio de modo: DEFERRABLE o NOT DEFERRABLE– Indica si la RI puede pasar a modo DEFERRED

- Valores asumidos por omisión:

Si no se indica ningún modo inicial, se asume INITIALLY IMMEDIATESi se especifica INITIALLY IMMEDIATE (o se asume)...

- Si no se indica DEFERRABLE ni NOT DEFERRABLE, asume NOT DEFERRABLESi se especifica INITIALLY DEFERRED, no puede indicarse NOT DEFERRABLE

- Puede ponerse DEFERRABLE, aunque se supone

2.5.3 Comprobación de restriccionesModos de comprobación (2)


• Sentencia SET CONSTRAINTSSET CONSTRAINTS {<lista restricciones> | ALL} { DEFERRED | IMMEDIATE}

- Establece el modo para varias RIs para la transacción actual- Toda RI mencionada debe ser DEFERRABLE

De hecho ALL ≡ todas las RIs diferibles

- DEFERRED: toda RI mencionada pasa a modo diferido- IMMEDIATE: cada RI pasa a modo inmediato y es comprobada

- si falla la comprobación de alguna RI, falla SET CONSTRAINTS y ninguna RI cambia de modo

2.5.3 Comprobación de restriccionesModos de comprobación (y 3)

• Ejecutar COMMIT implica realizar SET CONSTRAINTS ALL IMMEDIATE- Si la comprobación de alguna RI falla

COMMIT fallala transacción completa falla (rollback)


• Claves externas: acciones referenciales (recordatorio)– Mantenimiento de integridad referencial ante intentos de violación– Ejecución de operaciones adicionales que dejan la BD consistente

ON DELETE...– Indica la regla de borrado para filas de T1 respecto de T2.FK

Qué ocurre si se intenta eliminar una fila r1 de T1 y existe alguna fila r2 en T2 que le hace referencia (e.d. contiene un valor r2.FK = r1.CK)

– Acciones referenciales posibles:a. NO ACTION (opción por omisión) Rechazar la operación de eliminación sobre T1b. CASCADE Eliminar junto con r1 toda fila r2 de T2 que se refiera a r1 (propagación)

c. SET DEFAULTAsignar su valor por defecto a cada componente de la FK en toda fila r2, y eliminar r1

» Debe existir una fila en T1 con cada componente de CK a su valor por defecto

d. SET NULLAsignar NULL a cada componente de la FK en todas las filas r2, y eliminar r1» Cada componente de la FK debe tener nulos permitidos



ON UPDATE...– Indica la regla de actualización para T1.CK respecto de T2.FK

Qué ocurre si se intenta modificar la clave candidata dentro de una fila r1 de T1 y existe alguna fila r2 en T2 que le hace referencia (contiene valor r2.FK = r1.CK)

– Acciones referenciales posibles:a. NO ACTION (opción por omisión) Rechazar la actualización sobre T1.CKb. CASCADE Propagar la actualización de CK a toda fila r2 de T2 que se refiera a r1c. SET DEFAULT

Asignar su valor por defecto a los componentes de FK que corresponden a componentes modificados de T1.CK, en todas las filas r2, y actualizar r1

» Debe existir una fila en T1 con cada componente de CK a su valor por defecto

d. SET NULLAsignar NULL a los componentes de la FK correspondientes a componentes modificados en T1.CK, en todas las filas r2, y actualizar r1

» Tales componentes de la FK debe tener nulos permitidos



• Comportamiento ante una operación de manipulación de datos, en función de la acción referencial especificada

Sea T2.FK una clave externa hacia la clave candidata T1.CK ...

La definición de FK no incluye ON DELETE ni ON UPDATE...INSERT en T2 o UPDATE de T2.FK

si el valor de T2.FK no existe en T1.CK Rechazar (NO ACTION)DELETE en T1 o UPDATE de T1.CK

si alguna fila de T2 le hace referencia Rechazar (NO ACTION)

La definición de FK incluye ON DELETE o bien ON UPDATE...INSERT en T2 o UPDATE de T2.FK

si el valor de T2.FK no existe en T1.CK Rechazar (NO ACTION)DELETE en T1 o UPDATE de T1.CK

si alguna fila de T2 le hace referencia ejecución de la acción referencialespecificada en las cláusulas


2.4 Manipulación de datos - dis.um.esdis.um.es/~jfernand/0607/fbd/sql.pdf · Tema 2. Modelo...

Documents

Transcript of 2.4 Manipulación de datos - dis.um.esdis.um.es/~jfernand/0607/fbd/sql.pdf · Tema 2. Modelo...