Tema II:
El Lenguaje Estándar SQL (2.5)
El modelo relacional de datos
conocer las estructuras de datos del modelo: la tupla y la relación
conocer básicamente la forma de modelar la realidad utilizando el modelo relacional
conocer los mecanismos del modelo relacional para expresar restricciones de integridad
conocer los lenguajes de manipulación propuestos para este modelo de datos: Álgebra Relacional y Cálculo Relacional de Tuplas
conocer mecanismos de vistas
conocer el mecanismo de disparadores
Objetivos:
2.1 Modelo relacional de datos (Aproximación algebraica)
2.2 Esquema relacional: representación de la realidad
2.3 Modelo relacional de datos (Aproximación lógica)
2.4 Restricciones de integridad
2.5 El lenguaje estándar SQL
2.6 Vistas
2.7 Disparadores
2.5 El Lenguaje Estándar SQL
Lenguaje estándar actual para los SGBDs
Desarrollado por IBM a mediados de los años 70.
Es un híbrido entre el Álgebra Relacional y el Cálculo Relacional de tuplas.
Permite tanto el uso explícito de operadores algebraicos
(UNION, DIFERENCIA, etc) como el uso de expresiones lógicas de cualquier complejidad (variables-tupla, cuantificador
existencial, etc.).
Proporciona un sublenguaje de definición de datos (DDL) y un sublenguaje de manipulación de datos
(DML), así como otros componentes de control de datos.
Define el esquema de la BD
El sublenguaje DDL del SQL, proporciona sentencias para realizar las siguientes funciones:
Definición del esquema: CREATE SCHEMA
Definición de dominios: CREATE DOMAIN
Definición de relaciones (esquema de relación y restricciones):
CREATE TABLE
Definición de restricciones generales: CREATE ASSERTION.
Definición de relaciones derivadas (vistas): CREATE VIEW . (se verá en el punto 2.6.3 del tema)
Definición de permisos a usuarios: GRANT (se verá en el tema III)
Asociada a cada sentencia CREATE existe una sentencia DROP que permite eliminar el correspondiente elemento del esquema.
2.5.1 El Lenguaje de definición de Datos (DDL)
Definición del esquema:
esquema ::= CREATE SCHEMA AUTHORIZATION usuario
elemento_esquema1, elemento_esquema2, …
elemento_esquema ::= definición_dominio
| definición_tabla
| definición_restricción
| definición_vista
| definición_operación_grant
Definición de dominios:
definición_dominio ::= CREATE DOMAIN nom_dominio [AS]
tipo_dato
[DEFAULT {NULL | valor}]
[restricción_dominio1, restricción_dominio2, …]
restricción_dominio ::= [CONSTRAINT nombre_restricción]
CHECK (condición) [cuando_comprobar]
2.5.1 El Lenguaje de definición de Datos (DDL)
Definición de relaciones:
definición_tabla ::= CREATE TABLE nom_tabla
(elemento_tabla1, elemento_tabla2, … ) elemento-tabla ::=definición_columna
| restricción_tabla
definición_columna ::= nom_columna { tipo _datos | nom_dominio } [DEFAULT {valor | NULL}]
[restricción_columna1, restricción_columna2, …]
Definición de relaciones:
restricción_columna ::= [CONSTRAINT nombre_restricción]
{NOT NULL
| UNIQUE
| PRIMARY KEY
| REFERENCES nom_tabla [(nom_columna)]
[MATCH {FULL | PARTIAL | SIMPLE}]
[ON DELETE {CASCADE | SET NULL
| SET DEFAULT| NO ACTION}]
[ON UPDATE {CASCADE | SET NULL
| SET DEFAULT| NO ACTION}]
| CHECK (condición) } [cuándo_comprobar]
2.5.1 El Lenguaje de definición de Datos (DDL)
Definición de relaciones:
restricción_tabla ::= [CONSTRAINT nombre-restricción]
{ UNIQUE (nom-columna1, nom-columna2, … )
| PRIMARY KEY (nom-columna1, nom-columna2, …)
| FOREIGN KEY (nom-columna1, nom-columna2, …) REFERENCES nom-tabla [(nom-columna1,
nom-columna2, …)]
[MATCH {FULL | PARTIAL | SIMPLE}]
[ON DELETE {CASCADE | SET NULL| SET DEFAULT
| NO ACTION}]
[ON UPDATE {CASCADE| SET NULL| SET DEFAULT
| NO ACTION}]
| CHECK (condición)}
[cuando-comprobar]
Definición de restriciones (generales): adecuada para restricciones que afectan a varias tablas.
definición_restricción::= CREATE ASSERTION nombre_restricción CHECK (condición)
[cuando-comprobar]
cuando_comprobar:= [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE | DEFERRED}]
2.5.1 El Lenguaje de definición de Datos (DDL)
Modificación de una relación:
modificación_tabla::= ALTER TABLE nombre_tabla {ADD (definición_columna)
|ALTER [COLUMN] (nombre_columna) {DROP DEFAULT
| SET DEFAULT {literal
| funcion_sistema | NULL} } |DROP [COLUMN] nombre_columna
{RESTRICT | CASCADE} }
Borrado de una relación:
borrado_tabla::= DROP TABLE nombre_tabla {RESTRICT | CASCADE}
Sea el siguiente esquema relacional:
Departamento (código: tira(5), nombre: tira(100), director: tira(5), teléfono:
tira(11))
CP = {código}, VNN={nombre}, UNI={director}
Caj= {director} → Profesor f(director)=código Borrado NULOS Modificación CASCADA
Asignatura (código: tira(5), nombre: tira(100), semestre: tira(2), teo: real, prac:
real, dep: tira(5))
CP={código}, VNN={nombre, teo, prac, semestre, dep}
CAj={dep} →Departamento f(dep)=código Modificación CASCADA Profesor (código : tira(5), nombre: tira(50), teléfono: tira(11), categoría: tira(3),
dep: tira(5) )
CP={código}, VNN={nombre, categoría, dep}
CAj={dep} → Departamento f(dep)=código Modificación CASCADA Docencia ( cod_pro: tira(3), cod_asg: tira(3), gteo: entero, gprac: entero)
CP={cod_pro, cod_asg}, VNN={gteo, gprac}
CAj={cod_asg} → Asignatura f(cod_asg)=código Modificación CASCADA CAj={cod_pro} → Profesor f(cod_pro)=código Borrado CASCADA
Modificación CASCADA
2.5.1 El Lenguaje de definición de Datos (DDL)
Y las siguientes restricciones de integridad adicionales:
los créditos deben ser valores reales positivos.
los grupos de clase deben ser enteros positivos.
las categorías de un profesor son: TEU, TU, CEU y CU
Un profesor con categoría TEU no puede dar mas de
33 créditos y un profesor con categoría TU no puede
dar mas de 22 créditos.
El esquema en SQL se definiría:
CREATE SCHEMA Docencia-UPV
CREATE DOMAIN créditos AS NUMBER(1,1) CHECK (value>=0) CREATE DOMAIN grupos AS INTEGER CHECK (value>=0)
CREATE TABLE Departamento (código CHAR(5),
nombre VARCHAR(100) NOTNULL, director CHAR(5) UNIQUE
REFERENCES Profesor (codigo) ON UPDATE CASCADE ON DELETE SET NULL, teléfono CHAR(11),
CONSTRAINT CP_dpto PRIMARYKEY (código) )
2.5.1 El Lenguaje de definición de Datos (DDL)
El esquema en SQL se definiría:
CREATETABLE Profesor ( codigo CHAR(5),
nombre VARCHAR(50) NOTNULL, teléfono CHAR(11),
categoría CHAR(3) NOTNULL
CHECK categoría IN (‘TEU’, ‘TU’, ‘CEU’, ‘CU’) dep CHAR(5) NOTNULL,
CONSTRAINT CP_prof PRIMARYKEY (codigo), CONSTRAINT CAj_prof_dpto FOREIGN KEY (dep)
REFERENCES Departamento (codigo) ON UPDATE CASCADE )
El esquema en SQL se definiría:
CREATEASSERTION RI_docencia CHECK
(NOT EXISTS ( SELECT * FROM PROFESOR P WHERE (P.categoría=“TEU”
AND
(SELECT SUM (gteo*teo + gprac*prac) FROM Docencia, Asignatura WHERE cod_pro = P.cod_pro) >33)
OR (P.categoría=“TU”
AND
(SELECT SUM (gteo*teo + gprac*prac) FROM Docencia, Asignatura
WHERE cod_pro=P.cod_pro) >22 ) ) )
- Verificación (CHECK): Comprueba, en toda operación de actualización, si el predicado es cierto o falso y, en el segundo caso, rechaza la operación. La restricción de verificación se define sobre un único elemento (dentro de un CREATE TABLE) y puede o no tener nombre.
Sintaxis:
CREATE TABLE <nombre_tabla>
….
[CONSTRAINT <nombre>] CHECK (<condición>) donde:
<nombre> Nombre de la restricción
<condición> una expresión que debe ser cierta para todas las filas de la tabla. A nivel de columna sólo puede referir a dicha columna. A nivel de tabla puede referir a otras columnas, pero a los valores de la misma fila.
Restricciones (Check y Assertion)
2.5.1 El Lenguaje de definición de Datos (DDL)
Ejemplo a nivel de columna:
CREATE TABLE Empleados (
…
salario NUMBER(8,2)
CONSTRAINT CHECK (salario>0)
… );
Ejemplo a nivel de tabla:
CREATE TABLE Empleados (
…
salario NUMBER(8,2), neto NUMBER(8,2),
…
CONSTRAINT neto_max CHECK (neto<=salario*0,8) );
Ejemplo de Restricciones (Check y Assertion)
El usuario formula una condición mediante un predicado definido sobre un conjunto de atributos, tuplas o dominios, que debe ser verificado en toda operación de actualización para que el nuevo estado constituya una ocurrencia válida del esquema.
- Verificación (CHECK): Comprueba, en toda operación de actualización, si el predicado es cierto o falso y, en el segundo caso, rechaza la operación.
CHECK N_HORAS > 30 en CURSO_DOCTORADO
- Aserción (ASSERTION): Actúa de forma idéntica a la anterior, pero se diferencia de ella en que puede afectar a varios elementos (por ejemplo, a dos tablas distintas). Por tanto, su definición no va unida a la de un determinado elemento del esquema y siempre ha de tener un nombre.
CREATE ASSERTION CONCEDE_SOLICITA AS
CHECK (SELECT Cod_Estudiante, Cod_Beca FROM CONCEDE) IN (SELECT Cod_Estudiante, Cod_Beca FROM SOLICITA));
2.5.2 El Lenguaje de manipulación de Datos (DML)
Proporciona sentencias de consulta (SELECT) y sentencias de actualización (INSERT, DELETE y UPDATE)
2.5.2.1 La sentencia SELECT
Sentencia de consulta del lenguaje. Su estructura básica es:
SELECT R1X.Ai, R2X.Bj,..., RnX.Ck
FROM R1 [AS] R1X, R2 [AS] R2X, ..., Rn [AS] RnX WHERE F(R1X, R2X,..., RnX)
Donde, R1 R2, ..., Rn son relaciones, Ai Bj, ..., Ck son respectivamente atributos de los esquemas de R1 R2, ..., Rn, R1X, R2X,..., RnX son nombres alternativos (alias) de R1 R2, ..., Rn y F(R1X, R2X,..., RnX) es una condición.
Se puede interpretar desde una perspectiva lógica o algebraica.
Datos (DML)
Perspectiva Lógica
Se ha justificado la utilidad del lenguaje de la Lógica de 1er orden como lenguaje relacional (apartado 2.3.2).
En esa aproximación, una consulta (sentencia SELECT) a una base de datos relacional consiste en una fórmula lógica escrita en un lenguaje L derivado del esquema de la base de datos.
Perspectiva Algebraica
Además de la estructura básica de la sentencia SELECT, el SQL proporciona los siguientes operadores adicionales de carácter algebraico:
Operador Unión: Select1 UNION Select2
Operador Intersección: Select1 INTERSECT Select2
Operador Diferencia: Select1 EXCEPT Select2
2.5.2 El Lenguaje de manipulación de Datos (DML)
Perspectiva Algebraica (2)
Operadores adicionales de carácter algebraico:
Operador Producto Cartesiano:
SELECT ....
FROM tabla1 CROSS JOIN tabla2 WHERE ....
Operador Concatenación:
SELECT ....
FROM tabla1 NATURAL JOIN tabla2 WHERE ....
2.5. El Lenguaje Estándar SQL
Operador Álgebra Relacional
SQL
Selección R Donde F SELECT ... FROM R WHERE F Proyección R [Ai , Aj ..., Ak] SELECT Ai , Aj ..., Ak FROM R Producto
Cartesiano
R1 x R2, ... x Rn SELECT ... FROM R1, R2, ..., Rn, o SELECT...FROM R1 CROSS JOIN R2, ..., CROSS JOIN Rn
Concatenación R1 R2 SELECT... FROM R1 NATURAL JOIN R2
Unión R1 ∪ R2 SELECT * FROM R1 UNION SELECT * FROM R2
Diferencia R1 - R2 SELECT * FROM R1 EXCEPT SELECT * FROM R2
Intersección R1 ∩ R2 SELECT * FROM R1 INTERSECT SELECT * FROM R2
2.5.2.2 Sentencias de Actualización: INSERT, DELETE, UPDATE
INSERT
Permite insertar tuplas en una relación:
Inserción simple:
INSERT INTO R [(A1, A2, ... Ak)]
{DEFAULT VALUES | VALUES (v1, v2, ..., vk) }.
Ejemplo 2.8. La inserción de una nueva tupla en la relación Río, correspondiente al río Ebro, se realizaría de la forma:
INSERT INTO Río VALUES (r3, 'Ebro').
Inserción Múltiple:
INSERT INTO R [(A1, A2, ... Ak)] sentencia_SELECT
Ejemplo 2.8. Se desea insertar en la relación Río todos los ríos almacenados en la relación Otros_Ríos. La inserción se realizaría de la forma: INSERT INTO Río SELECT * FROM Otros_ríos
DELETE, UPDATE
DELETE
Permite borrar tuplas en una relación:
DELETE FROM R [WHERE condición]
Ejemplo 2.8. Se desean borrar las tuplas de la relación Pasa_por con información sobre los ríos que pasan por la provincia de código 16.
DELETE FROM Pasa_por WHERE pcod=16
2.5.2.2 Sentencias de Actualización:
INSERT, DELETE, UPDATE
UPDATE
Permite actualizar tuplas en una relación:
UPDATE R
SET A1 = {DEFAULT| NULL | expresión1}, A2 = {DEFAULT| NULL | expresión2}, ...
[WHERE condición ]
Sea el esquema del Ejemplo 2.8. Se desea actualizar el nombre del río de código 'r2', con el nuevo valor ‘Turia'.
UPDATE Río
SET nombre = ‘Turia' WHERE rcod='r2'
Requisitos de Información:
De cada mar: código, nombre, detalles y ríos que desembocan en él.
De cada río: código, nombre, longitud, mar en el que desemboca y provincias por las que pasa, indicando los kilómetros.
De cada provincia: código, nombre, extensión, y límites entre provincias.
información geográfica usando el sublenguaje de definición de datos de SQL.
CREATE TABLE Rio (
rcod CHAR(3) PRIMARY KEY , nombre VARCHAR(30), longitud CHAR(5),
mcod CHAR(3) REFERENCES Mar (mcod) ) CREATE TABLE Mar (
mcod CHAR(3) PRIMARY KEY , nombre VARCHAR(30),
detalles VARCHAR(15) ) CREATE TABLE Provincia (
pcod CHAR(3) PRIMARY KEY , nombre VARCHAR(30),
extensión integer ) CREATE TABLE Pasa_por (
rcod CHAR(3) REFERENCES Rio (mcod), pcod CHAR(3) REFERENCES Provincia (pcod), km INTEGER,
CONSTRAINT CP_pasa_por PRIMARY KEY (rcod, pcod) ) CREATE Limita_con (
pcod1 CHAR(3) REFERENCES Provincia (pcod), pcod2 CHAR(3) REFERENCES Provincia (pcod)
CONSTRAINT CP_limita_con PRIMARY KEY (pcod1, pcod2) )
Solución Ejercicio 1:
Caj= {dni} → Propietario Propietario (dni, fecha_alta)
Persona (dni, fecha_nacimiento, lugar_nacimiento, nombre, direccion) Indique cómo definiría las siguientes restricciones de integridad:
1. No puede haber dos mascotas con el mismo número de chip 2. Todo propietario es una persona
3. El campo peligrosidad sólo puede tomar los valores 0 (falso) o 1(verdadero) 4. Los propietarios pueden serlo de varias mascotas
5. Las mascotas no tienen porque vivir en la misma dirección que sus propietarios 6. Se quiere almacenar más información sobre las razas de los animales de forma que, para cada raza, se almacena un identificador (para distinguir una raza de todas las demás), el nombre de la raza, el tipo (perro, gato, reptil, equino), el color, el tipo de pelo (largo, corto), y su peligrosidad (0 para las no peligrosas y 1 para las peligrosas)
7. No todas las personas deben ser propietarias de mascotas
8. Sólo pueden ser propietarios de mascotas las personas mayores de 18 años 9. Cuando una mascota muere (se eliminan sus datos de la tabla mascota), tiene
que dejar de estar asociada a su propietario 10. Toda mascota tiene que tener un nombre
Mascota (nro_chip, nombre, raza, peligrosidad, fecha_nacimiento, dni) Caj= {dni} → Propietario
Propietario (dni, fecha_alta)
Persona (dni, fecha_nacimiento, lugar_nacimiento, nombre, direccion) Indique cómo definiría las siguientes restricciones de integridad:
1. No puede haber dos mascotas con el mismo número de chip
CP={nro_chip} / nro_chip PRIMARY KEY en Mascota 2. Todo propietario es una persona
Caj= {dni} → Persona f(dni)=dni en Propietario 3. El campo peligrosidad sólo puede tomar los valores 0 (falso) o 1(verdadero)
CHECK (peligrosidad = 0 OR peligrosidad = 1) 4. Los propietarios pueden serlo de varias mascotas
YA ESTA!
5. Las mascotas no tienen porque vivir en la misma dirección que sus propietarios Añadimos un campo dirección en la tabla Mascota:
Mascota (nro_chip, nombre, raza, peligrosidad, fecha_nacimiento, direccion, dni)
Solución Ejercicio 2:
Mascota (nro_chip, nombre, raza, peligrosidad, fecha_nacimiento, dni) Caj= {dni} → Propietario
Propietario (dni, fecha_alta)
Persona (dni, fecha_nacimiento, lugar_nacimiento, nombre, direccion)
6. Se quiere almacenar más información sobre las razas de los animales de forma que, para cada raza, se almacena un identificador (para distinguir una raza de todas las demás), el nombre de la raza, el tipo (perro, gato, reptil, equino), el color, el tipo de pelo (largo, corto), y su peligrosidad (0 para las no peligrosas y 1 para las peligrosas)
Creamos una nueva tabla raza con 3 restricciones CHECK:
Raza (id, nombre, tipo, color, pelo, peligrosidad) CHECK (tipo IN (perro, gato, reptil, equino))
CHECK (pelo=largo OR pelo=corto)
CHECK (peligrosidad = 0 OR peligrosidad = 1) Y modificamos la tabla mascota:
Mascota (nro_chip, nombre, raza, peligrosidad, fecha_nacimiento, direccion, dni)
Caj= {raza} → Raza f(raza)=id
Solución Ejercicio 2:
Mascota (nro_chip, nombre, raza, peligrosidad, fecha_nacimiento, dni) Caj= {dni} → Propietario
Propietario (dni, fecha_alta)
Persona (dni, fecha_nacimiento, lugar_nacimiento, nombre, direccion)
7. No todas las personas deben ser propietarias de mascotas YA ESTA!
8. Sólo pueden ser propietarios de mascotas las personas mayores de 18 años Añadimos una restricción (aserción) que cuando se inserte un Propietario se compruebe que ese DNI corresponde a alguién con más de 18 años.
CREATE ASSERTION comprueba edad (dni, nombre, dirección) SELECT fecha_nacimiento INTO fecha
FROM Persona, Propietario WHERE persona.dni=propietario.dni;
IF calcula_edad(sysdate,fecha) > 18 THEN
INSERT INTO Propietario (dni, nombre, direccion) ELSE
ERROR END
END comprueba_edad;
Solución Ejercicio 2:
Caj= {dni} → Propietario Propietario (dni, fecha_alta)
Persona (dni, fecha_nacimiento, lugar_nacimiento, nombre, direccion)
9. Cuando una mascota muere (se eliminan sus datos de la tabla mascota), tiene que dejar de estar asociada a su propietario
YA ESTA!
10. Toda mascota tiene que tener un nombre
columna nombre de mascota NOT NULL / nombre NOT NULL
CREATE TABLE Mascota (
nro_chip CHAR(5) PRIMARY KEY, nombre VARCHAR(30) NOT NULL, raza CHAR(5) REFERENCES Raza (id)
fecha_nacimiento DATE, dirección VARCHAR(40),
dni VARCHAR(9) REFERENCES Propietario (dni) )
CREATE TABLE Propietario (
dni VARCHAR(9) PRIMARY KEY
REFERENCES Persona (dni), fecha_alta DATE
)
Solución Ejercicio 2:
Base de datos en SQL
CREATE TABLE Persona (
dni VARCHAR(9) PRIMARY KEY, fecha_nacimiento DATE,
lugar_nacimiento VARCHAR(20) , nombre VARCHAR(30),
dirección VARCHAR(40) )
CREATE TABLE Raza (
id CHAR(5) PRIMARY KEY, nombre VARCHAR(20),
tipo VARCHAR(10) CHECK (tipo IN (‘perro’, ‘gato’, ‘reptil’,
‘equino’)),
color VARCHAR(15),
pelo VARCHAR(10) CHECK (pelo=‘largo’ OR pelo=‘corto’), peligrosidad INTEGER(1)
CHECK (peligrosidad = 0 OR peligrosidad = 1), )
Solución Ejercicio 2:
Base de datos en SQL
CREATE ASSERTION comprueba edad (dni, nombre, direccion) SELECT fecha_nacimiento INTO fecha
FROM Persona, Propietario
WHERE persona.dni=propietario.dni;
IF calcula_edad(sysdate,fecha) > 18 THEN
INSERT INTO Propietario (dni, nombre, direccion) ELSE
ERROR END
END comprueba_edad;
