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
98Tema 2. Modelo relacional de datos
• 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
99Tema 2. Modelo relacional de datos
• 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...
100Tema 2. Modelo relacional de datos
• 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)
101Tema 2. Modelo relacional de datos
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
102Tema 2. Modelo relacional de datos
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
103Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92Consultas básicas (2)
104Tema 2. Modelo relacional de datos
• 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;
2.4 Manipulación de datos: SQL-92Consultas básicas (3)
105Tema 2. Modelo relacional de datos
• 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 *
106Tema 2. Modelo relacional de datos
• 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
107Tema 2. Modelo relacional de datos
• 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
108Tema 2. Modelo relacional de datos
• 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
109Tema 2. Modelo relacional de datos
• 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
110Tema 2. Modelo relacional de datos
• 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
111Tema 2. Modelo relacional de datos
• 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
112Tema 2. Modelo relacional de datos
• 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
113Tema 2. Modelo relacional de datos
• 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
114Tema 2. Modelo relacional de datos
• 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)
2.4 Manipulación de datos: SQL-92
115Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
116Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
117Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
118Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
119Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
¿”Mejor” con DISTINCTen la subconsulta?
120Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
121Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
122Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
123Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
124Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
125Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
126Tema 2. Modelo relacional de datos
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
2.4 Manipulación de datos: SQL-92
127Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
128Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
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
129Tema 2. Modelo relacional de datos
• 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)
2.4 Manipulación de datos: SQL-92
130Tema 2. Modelo relacional de datos
• 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)
2.4 Manipulación de datos: SQL-92
131Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
132Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
133Tema 2. Modelo relacional de datos
• 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)
2.4 Manipulación de datos: SQL-92
134Tema 2. Modelo relacional de datos
• 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)
2.4 Manipulación de datos: SQL-92
135Tema 2. Modelo relacional de datos
• Ú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)
2.4 Manipulación de datos: SQL-92
136Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
137Tema 2. Modelo relacional de datos
• 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)
2.4 Manipulación de datos: SQL-92
138Tema 2. Modelo relacional de datos
• 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)
2.4 Manipulación de datos: SQL-92
Recomendación (optimización de consultas):Consultas con mínimo anidamiento correlacionado
y mínimo ordenamiento implícito
139Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
140Tema 2. Modelo relacional 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)
2.4 Manipulación de datos: SQL-92
141Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
142Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
143Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
144Tema 2. Modelo relacional 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
2.4 Manipulación de datos: SQL-92
145Tema 2. Modelo relacional 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)
2.4 Manipulación de datos: SQL-92
146Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
147Tema 2. Modelo relacional 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
2.4 Manipulación de datos: SQL-92
148Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
CREATE TABLE Empleado (nombre ...apellido ...nss ...nif ...fechan ...direccion ...sexo ...salario ...nssjefe ...nd …
);
149Tema 2. Modelo relacional de datos
• 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
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
150Tema 2. Modelo relacional de datos
• 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 ... ... );
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
151Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
152Tema 2. Modelo relacional 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
2.4 Manipulación de datos: SQL-92
153Tema 2. Modelo relacional 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
2.4 Manipulación de datos: SQL-92
154Tema 2. Modelo relacional 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
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
155Tema 2. Modelo relacional de datos
• 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
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
156Tema 2. Modelo relacional de datos
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)
);
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
157Tema 2. Modelo relacional de datos
• 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 ),... );
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
158Tema 2. Modelo relacional de datos
• 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 ),... );
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
159Tema 2. Modelo relacional de datos
• 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
);
LDD: definición de tablas
2.4 Manipulación de datos: SQL-92
160Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
161Tema 2. Modelo relacional de datos
• 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 ;
LDD: definición de vistas
2.4 Manipulación de datos: SQL-92
nombres que hereda la vista
162Tema 2. Modelo relacional de datos
• Un estado de la vista EMPLEADO_EN_PROYECTO
LDD: definición de vistas
2.4 Manipulación de datos: SQL-92
Empleado_en_Proyecto
............
10.0ProductoZVizcarraFederico
20.0ProductoYBarcelóJosefa
10.0ProductoYVizcarraFederico
20.0ProductoXBarcelóJosefa
07.5ProductoYSilvaJosé
40.0ProductoZNietoRamón
32.5ProductoXSilvaJosé
horasnombrepapellidonombre
163Tema 2. Modelo relacional de datos
• 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
LDD: definición de vistas
2.4 Manipulación de datos: SQL-92
164Tema 2. Modelo relacional de datos
• 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’;
LDD: definición de vistas
2.4 Manipulación de datos: SQL-92
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%’;
165Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
166Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
167Tema 2. Modelo relacional de datos
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’
LDD: modificación a través de vistas
2.4 Manipulación de datos: SQL-92
168Tema 2. Modelo relacional de datos
- 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”
LDD: modificación a través de vistas
2.4 Manipulación de datos: SQL-92
169Tema 2. Modelo relacional de datos
• 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
LDD: modificación a través de vistas
2.4 Manipulación de datos: SQL-92
170Tema 2. Modelo relacional de datos
• 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
LDD: modificación a través de vistas
2.4 Manipulación de datos: SQL-92
171Tema 2. Modelo relacional de datos
• 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 ;
LDD: modificación a través de vistas
2.4 Manipulación de datos: SQL-92
172Tema 2. Modelo relacional de datos
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
2.4 Manipulación de datos: SQL-92
173Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
174Tema 2. Modelo relacional 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
2.4 Manipulación de datos: SQL-92
175Tema 2. Modelo relacional de datos
• 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
LDD: alteración de tablas
2.4 Manipulación de datos: SQL-92
176Tema 2. Modelo relacional de datos
• 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’;
LDD: alteración de tablas
2.4 Manipulación de datos: SQL-92
177Tema 2. Modelo relacional de datos
• 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);
LDD: alteración de tablas
2.4 Manipulación de datos: SQL-92
178Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
179Tema 2. Modelo relacional de datos
• 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
2.4 Manipulación de datos: SQL-92
180Tema 2. Modelo relacional de datos
• 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.4 Manipulación de datos: SQL-92
181Tema 2. Modelo relacional de datos
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
182Tema 2. Modelo relacional de datos
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
183Tema 2. Modelo relacional de datos
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)
184Tema 2. Modelo relacional de datos
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
185Tema 2. Modelo relacional de datos
• 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
186Tema 2. Modelo relacional de datos
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
187Tema 2. Modelo relacional de datos
• 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
188Tema 2. Modelo relacional de datos
• 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
189Tema 2. Modelo relacional de datos
• 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)
190Tema 2. Modelo relacional de datos
• 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)
191Tema 2. Modelo relacional de datos
• 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
192Tema 2. Modelo relacional de datos
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)
193Tema 2. Modelo relacional de datos
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)
2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla (3)
194Tema 2. Modelo relacional de datos
• 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
2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla (4)
195Tema 2. Modelo relacional de datos
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)
196Tema 2. Modelo relacional de datos
• 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)
197Tema 2. Modelo relacional de datos
• 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)
198Tema 2. Modelo relacional de datos
• 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)) ;
2.5.2 Reglas de integridad en SQL-92Reglas de Integridad Generales (3)
199Tema 2. Modelo relacional de datos
*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
2.5.2 Reglas de integridad en SQL-92Reglas de Integridad Generales (4)
200Tema 2. Modelo relacional de datos
• 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 (…)
2.5.2 Reglas de integridad en SQL-92Reglas de Integridad Generales (5)
201Tema 2. Modelo relacional de datos
• 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)
202Tema 2. Modelo relacional de datos
• 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
203Tema 2. Modelo relacional de datos
• 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)
204Tema 2. Modelo relacional de datos
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;
2.5.2 Reglas de integridad en SQL-92Disparadores - versión SQL:1999 (3)
205Tema 2. Modelo relacional de datos
* 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…
2.5.2 Reglas de integridad en SQL-92Disparadores - versión SQL:1999 (4)
206Tema 2. Modelo relacional de datos
* 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;
2.5.2 Reglas de integridad en SQL-92Disparadores - versión SQL:1999 (5)
En Oracle este trigger tendría el problema de la tabla mutanteSe verá en las prácticas de PL/SQL
207Tema 2. Modelo relacional de datos
• 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)
208Tema 2. Modelo relacional de datos
• 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)
209Tema 2. Modelo relacional de datos
• 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
210Tema 2. Modelo relacional de datos
• 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
211Tema 2. Modelo relacional de datos
• 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)
212Tema 2. Modelo relacional de datos
• 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)
213Tema 2. Modelo relacional de datos
• 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
2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla (5)
214Tema 2. Modelo relacional de datos
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
2.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla (6)
215Tema 2. Modelo relacional de datos
• 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.5.2 Reglas de integridad en SQL-92Reglas de Integridad de Tabla (7)