1

Práctica 3:

El lenguaje SQL (2ª parte).

(6ª sesión).

Práctica 1. Introducción al uso de una BD relacional (ACCESS).

Práctica 2. Representación de la realidad en el modelo relacional de datos.

Práctica 3. El lenguaje SQL. (ORACLE)

Práctica 4. Estudio del SGBD ORACLE.

Programa de prácticas:

2

El lenguaje SQL.

• estudio del lenguaje de definición de datos del SQL.

• definir una base de datos relacional en el sistema ORACLE.

• estudio del mecanismo de disparadores (triggers) en el sistema ORACLE.

Objetivos (2ª parte):

El lenguaje SQL.

Lenguaje SQL:

� manipulación de datos

• SELECT (consulta)

• INSERT (inserción de tuplas)

• DELETE (borrado de tuplas)

• UPDATE (modificación de tuplas)

� definición de datos

3

cod_pro

nombre

teléfono

categoría

cod_dep

Profesor

cod_dep

nombre

director

teléfono

Departamento

cod_asg

nombre

semestre

teo

prac

cod_dep

Asignatura

cod_pro

cod_asg

gteo

gprac

Docencia

Clave primaria Clave Ajena

El lenguaje SQL.

Departamento (cod_dep: tira(5), nombre: tira(100), director tira(50),teléfono : tira(11))

CP={cod_dep}, VNN={nombre}

Asignatura (cod_asg: tira(5), nombre: tira(100), semestre: tira(2), teo: real, prac: real, cod_dep: tira(5))

CP={cod_asg}, VNN={nombre, teo, prac, semestre}

CAj={cod_dep} →Departamento f(cod_dep)=cod_dep

Profesor (cod_pro : tira(5), nombre: tira(50), teléfono: tira(11),categoría: tira(3), cod_dep: tira(5) )

CP={cod_pro}, VNN={nombre, categoría}

CAj={cod_dep} → Departamento f(cod_dep)=cod_dep

Docencia ( cod_asg: tira(3), cod_pro: tira(3), gteo: entero, gprac: entero)

CP={cod_pro, cod_asg}, VNN={gteo, gprac}

CAj={cod_asg} → Asignatura f(cod_asg)=cod_asg

CAj={cod_pro} → Profesor f(cod_pro)=cod_pro

El lenguaje SQL.

4

El lenguaje SQL.

Definición de un esquema relacional en SQL.�definiciones de dominios

CREATE DOMAIN ...

�definiciones de relaciones: esquema y restricciones.

CREATE TABLE ...

�definiciones de restricciones de integridad generales

CREATE ASSERTION ...

�definiciones de vistas

CREATE VIEW ...

�definiciones de autorizaciones de acceso

GRANT ...

esquema lógico

esquema externo

seguridad

esquema ::= CREATE SCHEMA

AUTHORIZATION usuario

lista_elemento_esquema

elemento_esquema ::= definición_dominio

| definición-relación-básica

| definición-restricción

| definición-vista

| definición-operación-grant

Lenguaje de definición de datos en SQL

El lenguaje SQL.

5

Definición de dominios

definición_dominio ::= CREATE DOMAIN nom_dominio [AS] tipo_dato[DEFAULT {NULL | valor}]

[lista_restricción_dominio]

restricción_dominio ::= [CONSTRAINT nombre_restricción] CHECK (condición_búsqueda)[cuando_comprobar]

cuando_comprobar ::= [NOT] DEFERRABLE

El lenguaje SQL.

Definición de relación básica

definición_relación_básica ::= CREATE TABLE nom_relación

(lista_elemento_relación_básica )

elemento-relación-básica ::= definición-atributo

| restricción_relación

definición_atributo ::= nom_atributo { tipo _datos | nom_dominio }

[DEFAULT {valor | NULL}]

[lista_restricción_atributo]

El lenguaje SQL.

6

Restricciones sobre atributos

restricción_atributo ::=

[CONSTRAINT nombre_restricción]

{NOT NULL

| UNIQUE

| PRIMARY KEY

| REFERENCES nom_relación [(nom_atributo)]

[tipo_integridad_referencial]

[directriz_borrado]

[directriz_actualización]

| CHECK (condición_búsqueda) }

[cuándo_comprobar]

El lenguaje SQL.

Restricciones sobre relaciones

restricción_relación ::=

[CONSTRAINT nombre-restricción]

{ UNIQUE (comalista-nom-atributo)

| PRIMARY KEY (comalista-nom-atributo)

| FOREIGN KEY (comalista-nom-atributo)

REFERENCES nom-relación [(comalista-nom-atributo)]

[tipo-integridad-referencial]

[directriz-borrado]

[directriz-actualización]

| CHECK (condición-búsqueda)}

[cuando-comprobar]

El lenguaje SQL.

7

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}

El lenguaje SQL.

Restricciones sobre la base de datos

definición_restricción ::=

CREATE ASSERTION nombre-restricción

CHECK (condición-búsqueda)

[cuando-comprobar]

El lenguaje SQL.

8

Nota:La semántica de cada una de las versiones de la cláusula cuando_comprobar es la siguiente: - si no se utiliza esta cláusula la restricción se define como no diferible y con modo inmediato. - la versión DEFERRABLE INITIALLY IMMEDIATE (resp. DEFERRED) define una restricción como diferible y con modo por defecto inmediato (resp. diferido). - la versión NOT DEFERRABLE INITIALLY IMMEDIATE coincide con los valores por defecto.- la versión NOT DEFERRABLE INITIALLY DEFERRED está prohibida. - la versión DEFERRABLE (resp. NOT DEFERABLE) define una restricción como diferible (resp. no diferible) y con modo por defecto inmediato.- la versión INITIALLY IMMEDIATE define una restricción como no diferible y con modo inmediato.- la versión INITIALLY DEFERRED define una restricción como diferible y con modo por defecto diferido.

cuando_comprobar:=

[[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE | DEFERRED}]

El lenguaje SQL.

La instrucción SQL que permite cambiar, localmente en una transacción, el modo de una restricción definida como diferible, es:

SET CONSTRAINT {comalista_nombre_restricción | ALL}{IMMEDIATE | DEFERRED}.

Cada restricción especificada en la lista debe ser diferible y la opción ALL hace referencia a todas las restricciones diferibles del esquema de la base de datos.El alcance del cambio producido por la instrucción SET CONSTRAINT es la transacción en la que se incluye o el fragmento de transacción hasta la siguiente aparición de la misma instrucción.Si se incluye la instrucción en medio de la transacción con la opción IMMEDIATE, las restricciones afectadas por la instrucción son comprobadas cuando se ejecuta ésta, si alguna de estas restricciones falla, la instrucción SET falla y el modo de las restricciones permanece sin modificar.

El lenguaje SQL.

9

Modificación de relaciones

modificación_relación ::= ALTER TABLE nombre_relación

{ADD (definición_atributo)|ALTER [COLUMN ] (nombre_atributo)

{DROP DEFAULT| SET DEFAULT {literal

| funcion_sistema| NULL} }

| DROP [COLUMN ] nombre_atributo {RESTRICT | CASCADE} }

borrado_relación ::= DROP TABLE nombre_relación {RESTRICT | CASCADE}

El lenguaje SQL.

Definición de vistasdefinición_vista ::=

CREATE VIEW nombre_vista [(comalista_nombre_atributo)] AS sentencia_SELECT[WITH CHECK OPTION]

borrado_vista ::= DROP VIEW nombre_vista {RESTRICT | CASCADE}

El lenguaje SQL.

Definición de un esquema externo en SQL.

10

definición_privilegio ::=GRANT {ALL | SELECT | INSERT [comalista_columna ]

|DELETE | UPDATE [comalista_columna ]}ON objeto TO {PUBLIC | comalista_usuario}[WITH GRANT OPTION]

eliminación_privilegio ::=REVOKE [GRANT OPTION FOR ]

{ALL | SELECT | INSERT [comalista_columna ]|DELETE | UPDATE [comalista_columna ]}

ON objeto TO {PUBLIC | comalista_usuario}{RESTRICT | CASCADE}

Definición de privilegios de acceso en SQL

El lenguaje SQL.

Objeto es una relación o una vista del esquema.

cod_pro

nombre

teléfono

categoría

cod_dep

Profesor

cod_dep

nombre

director

teléfono

Departamento

cod_asg

nombre

semestre

teo

prac

cod_dep

Asignatura

cod_pro

cod_asg

gteo

gprac

Docencia

clave primaria Clave ajena

El lenguaje SQL.

11

CREATE SCHEMA Docencia-UPV

CREATE TABLE Departamento( cod_dep CHAR(5),

nombre VARCHAR(100) NOT NULL,director VARCHAR(50),teléfono CHAR(11),CONSTRAINT CP_dpto PRIMARY KEY (cod_dep) )

CREATE TABLE Profesor( cod_pro CHAR(5),

nombre VARCHAR(50) NOT NULL,teléfono CHAR(11),categoría CHAR(3) NOT NULLcod_dep CHAR(5),CONSTRAINT CP_prof PRIMARY KEY (cod_pro),CONSTRAINT CAj_prof_dpto FOREIGN KEY (cod_dep)

REFERENCES Departamento (cod_dep) )

El lenguaje SQL.

VERSIÓN 1

CREATE TABLE Asignatura( cod_asg CHAR(5),

nombre VARCHAR(100) NOT NULL,semestre CHAR(2) NOT NULL,teo NUMBER(3,1) NOT NULL, prac NUMBER(3,1) NOT NULL,cod_dep CHAR(5) ,CONSTRAINT CP_asg PRIMARY KEY (cod_asg),CONSTRAINT CAj_asg_dpto FOREIGN KEY (cod_dep)

REFERENCES Departamento (cod_dep) )CREATE TABLE Docencia( cod_pro CHAR(5),

cod_asg CHAR(5),gteo INTEGER NOT NULL, gprac INTEGER NOT NULL,

CONSTRAINT CP_doc PRIMARY KEY (cod_pro, cod_asg),CONSTRAINT CAj_doc_prof FOREIGN KEY (cod_pro)

REFERENCES Profesor (cod_pro),CONSTRAINT CAj_doc_asg FOREIGN KEY (cod_asg)

REFERENCES Asignatura (cod_asg) );

El lenguaje SQL.

12

El lenguaje SQL.

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.

• todo profesor debe estar adscrito a un departamento

• toda asignatura debe estar adscrita a un departamento

• el director de un departamento debe ser un profesor y sólo se puede ser director de un departamento

•las directrices de mantenimiento de la integridad referencial son:

–clave ajena director en Departamento: borrado a nulos, actualización en cascada

–clave ajena cod_dep en Profesor: borrado restrictivo, actualización en cascada

–clave ajena cod_dep en Asignatura: borrado restrictivo, actualización en cascada

–clave ajena cod_pro en Docencia: borrado en cascada, actualización en cascada

–clave ajena cod_asg en Docencia: borrado restrictivo, actualización en cascada

El lenguaje SQL.

13

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( cod_dep CHAR(5),

nombre VARCHAR(100) NOT NULL,director CHAR(5) UNIQUE

REFERENCES Profesor (cod_pro) ON UPDATE CASCADEON DELETE SET NULL,

teléfono CHAR(11),CONSTRAINT CP_dpto PRIMARY KEY (cod_dep) )

El lenguaje SQL.

VERSIÓN 2

CREATE TABLE Profesor( cod_pro CHAR(5),

nombre VARCHAR(50) NOT NULL,teléfono CHAR(11),categoría CHAR(3) NOT NULL

CHECK categoría IN (‘TEU’, ‘TU’, ‘CEU’, ‘CU’) cod_dep CHAR(5) NOT NULL,CONSTRAINT CP_prof PRIMARY KEY (cod_pro),CONSTRAINT CAj_prof_dpto FOREIGN KEY (cod_dep)

REFERENCES Departamento (cod_dep)ON UPDATE CASCADE )

El lenguaje SQL.

14

CREATE TABLE Asignatura( cod_asg CHAR(5),

nombre VARCHAR(100) NOT NULL,semestre CHAR(2) NOT NULL,teo créditos NOT NULL,prac créditos NOT NULL,cod_dep CHAR(5) NOT NULL,CONSTRAINT CP_asg PRIMARY KEY (cod_asg),CONSTRAINT CAj_asg_dpto FOREIGN KEY (cod_dep)

REFERENCES Departamento (cod_dep)ON UPDATE CASCADE )

El lenguaje SQL.

CREATE TABLE Docencia( cod_pro CHAR(5),

cod_asg CHAR(5),gteo grupos NOT NULL,gprac grupos NOT NULL,

CONSTRAINT CP_doc PRIMARY KEY (cod_pro, cod_asg),CONSTRAINT CAj_doc_prof FOREIGN KEY (cod_pro)

REFERENCES Profesor (cod_pro)ON DELETE CASCADEON UPDATE CASCADE,

CONSTRAINT CAj_doc_asg FOREIGN KEY (cod_asg) REFERENCES Asignatura (cod_asg)

ON UPDATE CASCADE)

El lenguaje SQL.

15

CREATE ASSERTION RI_docencia

CHECK

(NOT EXISTS ( SELECT * FROM PROFESOR P

WHERE (P.categoría=“TEU”

AND

(SELECT SUM (gteo*teo + gprac*prac)

FROM Docencia NATURAL JOIN Asignatura

WHERE cod_pro = P.cod_pro) >33)

OR

(P.categoría=“TU”

AND

(SELECT SUM (gteo*teo + gprac*prac)

FROM Docencia NATURAL JOIN Asignatura

WHERE cod_pro=P.cod_pro) >22 ) ) )

El lenguaje SQL.

cod_proo nombredirecciónteléfono

o categoríacod_dep

Profesor

Relación básica

cod_proo nombredirecciónteléfono

o categoría

Prof_DSIC

Relación derivada (vista)

CREATE VIEW Prof_DSIC AS

SELECT cod_pro, nombre, dirección, teléfono, categoría

FROM Profesor

WHERE cod_dep=‘DSIC’

El lenguaje SQL.

Definición de un esquema externo en SQL (vistas).

16

El lenguaje SQL.

El lenguaje de definición de datos de ORACLE:Las principales diferencias respecto al estándar SQL son:

�no contempla el concepto de esquema de base de datos

�no permite la definición de dominios

�sólo contempla el tipo de integridad referencial débil

�sólo admite directrices de restauración de la integridad referencial frente a la operación de borrado (ON DELETE)

�las restricciones de integridad generales que se pueden definir con la cláusula CHECK son muy limitadas (no se pueden usar subconsultas, ni funciones agregadas, ....)

�no admite la sentencia CREATE ASSERTION.