Download Definición de datos

Lenguaje SQL (2ª Parte)
Definición de datos
Bases de Datos
Práctica 3
DSIC - UPV
Facultad de Informática
Escuela Técnica Superior de Informática Aplicada
Objetivos
„ Sintaxis del Lenguaje de Definición de Datos.
„ Creación de tablas.
„ Modificación de la definición de una tabla.
Visto en Teoría
„ Creación de una vista.
„ Otorgar autorizaciones.
„ Diferencias entre Oracle y SQL (estándar).
„ Creación de reglas de actividad (disparadores).
„ Realizar todo lo anterior usando la
herramienta ISQL del sistema de gestión de
bases de datos ORACLE.
Bases de Datos
DSIC-UPV
2
1
Diferencias entre
Oracle 9i / SQL ANSI
Definición
Definición de
de Relaciones
Relaciones (Oracle):
(Oracle):
„ No permite la definición de esquemas (CREATE
SQUEMA) Î Todas las relaciones forman parte de
misma BD.
„ No se permite la definir dominios (CREATE
DOMAIN) Î Los atributos sólo se pueden definir
sobre los tipos de datos proporcionados por el
sistema.
„ Solamente implementa la integridad referencial
débil para las claves ajenas. Las únicas directrices
para la restauración de la integridad referencial
disponibles son borrado en cascada y a nulos.
„ No se permite la definición de restricciones generales
sobre la base de datos (CREATE ASSERTION).
Bases de Datos
DSIC-UPV
3
Definición de reglas de
actividad (Disparadores)
„ Define el comportamiento activo del sistema.
„ Aplicaciones:
comprobación de restricciones de integridad
„ control de la seguridad
„ definición de reglas de funcionamiento de la
organización
„ mantenimiento de información derivada
„
Bases de Datos
DSIC-UPV
4
2
Definición de reglas de
actividad (Disparadores)
„ Forma de una regla de actividad:
Evento - Condición - Acción
„ acción que el sistema ejecuta cuando como
respuesta a la ocurrencia de un evento
cuando cierta condición se satisface.
„ En Oracle:
evento: operación de actualización
„ condición: expresión lógica del SQL
„ acción: procedimiento escrito en PL/SQL
(incluye instrucción de manipulación de la BD)
„
Bases de Datos
DSIC-UPV
5
Definición de reglas de
actividad (Disparadores)
definición_regla::=
{CREATE | REPLACE} TRIGGER nombre_regla
{BEFORE | AFTER | INSTEAD OF} evento
[disyunción_eventos]
ON {nombre_relación | nombre_vista}
[ [REFERENCING OLD AS nombre_referencia
[NEW AS nombre_referencia] ]
[FOR EACH {ROW | STATEMENT} [WHEN ( condición ) ] ]
bloque PL/SQL
disyunción_eventos ::= OR evento [disyunción_eventos]
evento ::=
INSERT | DELETE | UPDATE [OF comalista_nombre_atributo]
Bases de Datos
DSIC-UPV
6
3
Definición de reglas de
actividad (Disparadores)
Definición
Definición de
de Disparadores
Disparadores (Oracle):
(Oracle):
„ Un mismo TRIGGER se puede activar por más de un
evento sobre la misma tabla (se dispone de las variables
booleanas INSERTING, DELETING, UPDATING).
„ Sólo en el caso de granularidad orientada a la tupla
(FOR EACH ROW) el disparador está parametrizado.
No existen parámetros de tabla. No es necesario dar
nombre a los parámetros.
„ En la construcción de la condición de la regla (WHEN)
solamente pueden aparecer referencias a los
parámetros, sin subconsultas ni funciones agregadas.
„ La acción siempre es un boque PL/SQL. En la acción no
se puede hacer referencia a la tabla que ha disparado el
TRIGGER.
Bases de Datos
DSIC-UPV
7
Ejemplo
La
La siguiente
siguiente regla
regla define
define en
en el
el esquema
esquema de
de la
la base
base de
de
datos
el
siguiente
comportamiento
activo:
“después
de
datos el siguiente comportamiento activo: “después de
cada
cada inserción
inserción en
en la
la relación
relación RR hacer
hacer una
una copia
copia de
de la
la
tupla
insertada
en
la
relación
R_copia”.
tupla insertada en la relación R_copia”.
(El
(El esquema
esquema de
de RR es
es R(A:dom_A,
R(A:dom_A, B:dom_B))
B:dom_B))
CREATE
CREATE TRIGGER
TRIGGER T1
T1
AFTER
INSERT
ON
AFTER INSERT ON RR
FOR
FOR EACH
EACH ROW
ROW
BEGIN
BEGIN
INSERT
INSERT INTO
INTO R_copia
R_copia VALUES(:NEW.A,
VALUES(:NEW.A, :NEW.B);
:NEW.B);
END;
END;
Bases de Datos
DSIC-UPV
8
4
Ejemplo
La
La siguiente
siguiente regla
regla define
define en
en el
el esquema
esquema de
de la
la base
base de
de
datos
el
siguiente
comportamiento
activo:
“después
datos el siguiente comportamiento activo: “después
de
de cada
cada operación
operación de
de actualización
actualización de
de la
la relación
relación RR
registrar
registrar la
la información
información sobre
sobre el
el usuario
usuario yy la
la fecha
fecha
de
la
actualización
en
la
relación
R_control”
de la actualización en la relación R_control”
CREATE
CREATE TRIGGER
TRIGGER T2
T2
AFTER
INSERT
OR
AFTER INSERT OR UPDATE
UPDATE OR
OR DELETE
DELETE ON
ON RR
FOR
FOR EACH
EACH STATMENT
STATMENT
BEGIN
BEGIN
INSERT
INSERT INTO
INTO R_control
R_control VALUES(user,
VALUES(user, sysdate);
sysdate);
END;
END;
(user
(user yy sysdate
sysdate son
son funciones
funciones del
del sistema
sistema que
que
devuelven
devuelven respectivmente
respectivmente el
el usuario
usuario de
de la
la sesión
sesión yy
la
la fecha
fecha del
del sistema)
sistema)
Bases de Datos
DSIC-UPV
9
Ejemplo
La
La siguiente
siguiente regla
regla define
define en
en el
el esquema
esquema la
la
restricción:
“No
se
puede
insertar
en
la
restricción: “No se puede insertar en la relación
relación RR
una
una tupla
tupla si
si existe
existe en
en la
la relación
relación S”
S”
CREATE
CREATE TRIGGER
TRIGGER T3
T3
AFTER
INSERT
AFTER INSERT ON
ON RR
FOR
FOR EACH
EACH ROW
ROW
DECLARE
DECLARE
Aux
Aux NUMBER;
NUMBER;
BEGIN
BEGIN
SELECT
SELECT Count(*)
Count(*) INTO
INTO Aux
Aux
FROM
S
WHERE
:NEW.A=S.A;
FROM S WHERE :NEW.A=S.A;
IF
IF Aux>0
Aux>0 THEN
THEN RAISE_APPLICATION_ERROR(-20000,
RAISE_APPLICATION_ERROR(-20000,
‘No
‘No se
se puede
puede realizar
realizar esta
esta operación’);
operación’);
END
IF;
END IF;
END;
END;
Bases de Datos
DSIC-UPV
10
5
Ejercicios
Diseñar
Diseñar la
la base
base de
de datos
datos de
de una
una pequeña
pequeña
biblioteca:
biblioteca:
„ consultar los datos de un libro: código del libro, título, autor(es),
„
„
„
„
temática y en caso de estar prestado, el socio que lo tiene
actualmente en préstamo
consultar la información sobre un socio: código del socio, nombre,
dirección, teléfono y libros que actualmente tiene en préstamo así
como la fecha de préstamo
consultar los préstamos históricos de un socio: código del libro,
fecha del préstamo y fecha de la devolución
dar de alta, dar de baja y modificar los datos de un socio
gestionar los préstamos: prestar un libro a un socio y registrar la
devolución de un libro. (Se supone que un préstamo dura al menos
un día, es decir un libro no se puede prestar el mismo día más de
una vez)
Bases de Datos
DSIC-UPV
11
Ejercicios
Diseñar
Diseñar la
la base
base de
de datos
datos de
de una
una pequeña
pequeña
biblioteca:
biblioteca:
Restricciones
Restricciones::
„ el código del libro identifica unívocamente al libro
„ el código del socio identifica unívocamente al socio
„ el conjunto de temas utilizados para clasificar un libro
son: física, electricidad, mecánica y óptica
„ la fecha de devolución de un libro debe ser posterior a la
fecha de préstamo
„ el número total de libros que tiene prestados un socio es
un dato derivado que será mantenido automáticamente
por el sistema
Bases de Datos
DSIC-UPV
12
6
Ejercicios
Tareas:
Tareas:
a) definir el esquema relacional de la base datos anterior
(usando los conceptos del modelo relacional)
b) definir la base de datos en el sistema ORACLE9i
c) realizar actualizaciones y consultas sobre la base de
datos creada
Bases de Datos
DSIC-UPV
13
Diferencias entre
Oracle 9i / SQL ANSI
Tipos
Tipos de
de datos
datos proporcionados:
proporcionados:
„ Numéricos:
„ NUMBER [(precisión[, escala])]
„ NUMBER (precisión)
Æ entero
„ NUMBER (precisión, escala) Æ real
„ Alfanuméricos:
„ CHAR (longitud)
„ VARCHAR (longitud) o VARCHAR2 (longitud)
„ Fechas:
„ DATE
Bases de Datos
DSIC-UPV
14
7
Diferencias entre
Oracle 9i / SQL ANSI
Definición
Definición de
de Relaciones
Relaciones (SQL
(SQL ANSI):
ANSI):
tipo_integridad_referencial ::=
MATCH { FULL | PARTIAL}
directriz_borrado ::=
ON DELETE { CASCADE | SET NULL |
SET DEFAULT | NO ACTION}
directriz_actualización ::=
ON UPDATE { CASCADE | SET NULL |
SET DEFAULT | NO ACTION}
Bases de Datos
DSIC-UPV
15
Definición de reglas de
actividad (Disparadores)
Parametrización
Parametrización de
de eventos:
eventos:
„ Los eventos de las reglas FOR EACH ROW están
parametrizados
„ nombre de parámetros:
„
„
„
evento INSERT: NEW
evento DELETE: OLD
evento UPDATE: OLD y NEW
„ se pueden usar en la condición de la regla
„ se pueden usar en el bloque PL/SQL
Bases de Datos
DSIC-UPV
16
8
2.6. SQL: llenguatge de definició de dades (DDL)
Definició
Definició de
de taules/relacions
taules/relacions
definició_taula ::=
CREATE TABLE nom_taula
(element_taula1, element_taula2, element_taula3, ...)
element_taula ::= definició_atribut | restricció_taula
definició_atribut ::=
nom_atribut {tipus_dada | nom_domini}
[DEFAULT ({valor | funció_sistema | NULL} )]
[restricció_atribut1 restricció_atribut2 ...]
Bases de Datos
DSIC-UPV
17
2.6. SQL: llenguatge de definició de dades (DDL)
Restriccions
Restriccions sobre
sobre atributs
atributs
restricció_atribut ::=
[CONSTRAINT nom_restricció]
{NOT NULL
| UNIQUE
| PRIMARY KEY
| REFERENCES nom_taula [(nom_atribut)]
[MATCH {FULL | PARTIAL | SIMPLE}]
[Directriu_esborrat]
[Directriu_modificació]
| CHECK (condició) }
[quan_comprovar]
Bases de Datos
DSIC-UPV
18
9
2.6. SQL: llenguatge de definició de dades (DDL)
Restriccions
Restriccions sobre
sobre relacions
relacions
restricció_taula ::=
[CONSTRAINT nom_restricció]
{ UNIQUE (atribut1, atribut2, ...)
| PRIMARY KEY (atribut1, atribut2, ...)
| FOREIGN KEY (atribut1, atribut2, ...)
REFERENCES nom_taula [(atribut1, atribut2, ...)]
[MATCH {FULL | PARTIAL | SIMPLE}]
[Directriu_esborrat]
[Directriu_modificació]
| CHECK (condició)}
[quan_comprovar]
Bases de Datos
DSIC-UPV
19
2.6. SQL: llenguatge de definició de dades (DDL)
Directriu_esborrat ::= ON DELETE
{CASCADE
| SET NULL
| SET DEFAULT
| NO ACTION}
Directriu_esborrat_modificació::= ON UPDATE
{CASCADE
| SET NULL
| SET DEFAULT
| NO ACTION}
Bases de Datos
DSIC-UPV
20
10
2.6. SQL: llenguatge de definició de dades (DDL)
quan_comprovar:= [NOT] DEFERRABLE
[INITIALLY {INMEDIATE | DEFERRED}]
„ Si no s'utilitza aquesta clàusula la restricció es defineix com no
diferible i amb mode immediat.
„
„
„
NOT DEFERRABLE INITIALLY IMMEDIATE
NOT DEFERRABLE
INITIALLY IMMEDIATE
„ DEFERRABLE INITIALLY IMMEDIATE defineix una restricció
com diferible i amb mode per defecte immediat.
„
DEFERRABLE
„ INITIALLY DEFERRED defineix una restricció com diferible i amb
mode per defecte diferit.
„
DEFERRABLE INITIALLY DEFERRED
„ NOT DEFERRABLE INITIALLY DEFERRED està prohibida.
Bases de Datos
DSIC-UPV
21
11