Tema II: El modelo relacional de datos. (2.4)

(1)

Tema II:

El modelo relacional

de datos.

(2.4)

9

2.1 Modelo relacional de datos (Aproximación

algebraica).

–

2.1.1 Estructuras de datos: tupla, relación.

–

2.1.2 Operadores asociados a la estructura

relación: Álgebra Relacional.

9

2.2 Esquema relacional: representación de la

realidad.

9

2.3 Modelo relacional de datos (Aprox. lógica)

–

2.3.1 Lógica de 1

er

_orden.

–

2.3.2 Interpretación lógica de una base de datos

relacional. Lógica 1

er

_{orden como lenguaje.}

9

2.4 Restricciones de integridad.

El modelo relacional de datos.

2.4 Restricciones de Integridad.

Empleado (nro_emp: dom_emp, dni: dom_dni, nombre:dom_nom, teléfono:dom_tel, cod_dep: dom_dep) Departamento (cod_dep: dom_dep, nombre:dom_nom)

nro_emp dni nombre teléfono Empleado cod_dep nombre Departamento

nro_emp dni nombre teléfono cod_dep

1 2045021 Juan C. Ródenas 7796 d1 2 1234567 Robert Capilla ? d2 3 3567896 José V. Dualde 5760 d2 4 1432652 María Frasnedo 3560 d2 5 2045021 Cristina Guillot 7439 d8 1 3356223 ? 4590 ? cod_dep nombre d1 Ventas d2 Compras d3 Contabilidad

2.4 Restricciones de Integridad.

Empleado Departamento

?

Extensión válida

del esquema

Extensión “no adecuada”

del esquema

•Hay dos empleados con el mismo DNI.

(2)

2.4 Restricciones de Integridad.

restricción de integridad propiedad del mundo real del cual la base de

datos es una representación

para que la base de datos sea una representación fiel de la realidad debe cumplir estas restricciones en cualquier

instante de su historia

las restricciones deben ser comprobadas después de cada actualización de la base de datos

si las restricciones se definen en el esquema de la base de datos el SGBD puede velar por su cumplimiento rechazando operaciones del usuario que las violen

Restricciones de Integridad:

9 Restricciones sobre atributos: de dominio y

de

valor no nulo

9 Restricciones de

unicidad

9 Restricción de

clave primaria

9 Restricción de

clave ajena (integridad

referencial)

2.4 Restricciones de Integridad.

9 El atributo nro_emp identifica de forma únivoca

a los empleados

9 El atributo dni es único para cada empleado

9 Se debe conocer el nombre de todo empleado

9 El atributo cod_dep de

Empleado

referencia al

departamento de adscripción del empleado

Restricciones de Integridad:

2.4 Restricciones de Integridad.

9 El atributo nro_emp identifica de forma únivoca

a los empleados

9 El atributo dni es único para cada empleado

9 Se debe conocer el nombre de todo empleado

9 El atributo cod_dep de

Empleado

referencia al

departamento de adscripción del empleado

Restricciones de Integridad:

restricción de clave primaria restricción de unicidad restricción de valor no nulo restricción de clave ajena o integridad referencial

(3)

Restricciones de Integridad:

9Restricciones sobre atributos: de

dominio y de

valor no nulo

9 Restricciones de

unicidad

9 Restricción de

clave primaria

9 Restricción de

clave ajena (integridad

referencial)

Dado un conjunto de atributos K de R (K≠∅) se dice que R satisface una restricción de valor no nulo sobre Ksi se cumple la siguiente propiedad:

∀t ( t ∈R → ¬∃Ai ∈K y t(Ai) tiene valor nulo) en caso contrario R viola esta restricción.

Valor NO NULO

Empleado (nro_emp: dom_emp, dni: dom_dni, nombre:dom_nom, teléfono:dom_tel, cod_dep: dom_dep) VNN={nombre}

Restricciones de Integridad:

9 Restricciones sobre atributos: de dominio y

de

valor no nulo

9Restricciones de

unicidad

9 Restricción de

clave primaria

9 Restricción de

clave ajena (integridad

referencial)

2.4 Restricciones de Integridad.

Dado un conjunto de atributos K de R (K≠∅) se dice que R satisface una restricción de unicidad sobre Ksi se cumple la siguiente propiedad:

∀t1 ∀t2 ( t1∈R y t2∈R y t1≠t2→ ∃Ai∈K (t1(Ai) ≠t2(Ai))) en caso contrario R viola esta restricción.

Unicidad

Empleado (nro_emp: dom_emp, dni: dom_dni, nombre:dom_nom, teléfono:dom_tel, cod_dep: dom_dep) VNN={nombre}

(4)

Restricciones de Integridad:

9 Restricciones sobre atributos: de dominio y

de

valor no nulo

9 Restricciones de

unicidad

9Restricción de

clave primaria

9 Restricción de

clave ajena (integridad

referencial)

2.4 Restricciones de Integridad.

Si CP se define como clave primaria de R, se dice que R satisface la restricción de clave primariasi:

• R satisface la restricción de unicidad sobre CP • R satisface la restricción de valor no nulo sobre CP en caso contrario R viola esta restricción.

“Un conjunto CP de atributos de una relación R que identifican de forma unívoca sus tuplas”

Clave primaria

Nota: CP debe ser minima, es decir no debe haber un subconjunto propio de CP que a su vez pueda ser clave primaria

Empleado (nro_emp: dom_emp, dni: dom_dni, nombre:dom_nom, teléfono:dom_tel, cod_dep: dom_dep) CP={nro_emp}, VNN={nombre}, UNI={dni}

Departamento (cod_dep: dom_dep, nombre:dom_nom) CP={cod_dep}, VNN={nombre}

2.4 Restricciones de Integridad.

Restricciones de Integridad:

9 Restricciones sobre atributos: de dominio y

de

valor no nulo

9 Restricciones de

unicidad

9 Clave primaria

9 Clave ajena (integridad referencial)

(5)

Departamento (cod_dep: dom_dep, nombre:dom_nom) CP={cod_dep}, VNN={nombre}

Clave ajena (integridad referencial)

9El atributo cod_dep de Empleadoreferencia al departamento de adscripción del empleado

nro_emp dni nombre teléfono cod_dep Empleado cod_dep nombre Departamento

cod_dep de Empleado es un atributo de referencia (clave ajena)

su valor en una tupla de Empleado, si no es nulo, debe coincidir con el valor del atributo cod_dep de una tupla de Departamento.

su función es hacer referencia a las tuplas de Departamento

CAj={cod_dep} →Departamento f(cod_dep)=cod_dep Departamento (cod_dep: dom_dep, nombre: dom_nom) CP={cod_dep}, VNN={nombre}

2.4 Restricciones de Integridad.

nro_emp dni nombre teléfono cod_dep 1 2045021 Juan C. Ródenas 7796 d1 2 1234567 Robert Capilla ? d2 3 3567896 José V. Dualde 5760 d2 4 1432652 María Frasnedo 3560 d1 5 2045333 Cristina Guillot 7439 d8 cod_dep nombre d1 Ventas d2 Compras d3 Contabilidad

?

Empleado viola la restricción de clave ajena para el atributo cod_dep

Empleado

(6)

Clave Ajena

2.4 Restricciones de Integridad.

nro_emp dni nombre teléfono cod_dep 1 2045021 Juan C. Ródenas 7796 d1 2 1234567 Robert Capilla ? d2 3 3567896 José V. Dualde 5760 d2 4 1432652 María Frasnedo 3560 d1 5 2045333 Cristina Guillot 7439 d3 cod_dep nombre d1 Ventas d2 Compras d3 Contabilidad

Empleado satisface la restricción de clave ajena para el atributo cod_dep

Empleado

Departamento

Sea:

•R una relación de esquema ρ= {(A1, D1), (A2, D2),…, (An, Dn)} •S una relación de esquema σ= {(B1, E1), (B2, E2),…, (Bm, Em)} •A_ρel conjunto de nombres de atributo de ρ, A_ρ= {A1, A2, …, An} •Aσel conjunto de nombres de atributo de σ, Aσ= {B1, B2, …, Bm}

Clave Ajena

2.4 Restricciones de Integridad.

Una clave ajena CA de R que hace referencia a S se define: 1) un subconjunto K = {Ai, Aj,…, Ak} (K ⊆Aρy K ≠ ∅),

2) una biyección f: K→J tal que: J ⊆Aσ,

J tiene restricción de unicidad, y

∀Ai(Ai ∈K→Aiy f(Ai) tienen el mismo dominio, y

3) un tipo de integridad referencialque puede ser: débil,

parcialo completa.

Clave Ajena

2.4 Restricciones de Integridad.

Tipos de integridad referencial:

1) Debil: Sien una tupla de R todos los atributos son no nulos, existe una tupla en S que tome los mismos valores. 2) Parcial: Sien una tupla de R algúnatributo que noes

nulo, existe una tupla en S que tome los mismos valores. 3) Completa: Si en una tupla de R todos los atributos deben

tener valor nuloo todos los valores no nulos. Si es así, existe una tupla en S que toma los mismos valores.

Clave Ajena

(7)

Si K = {Ai} (sólo contiene un atributo) los tres tipos de integridad

referencial coinciden.

Si K = {A_i} (sólo contiene un atributo) se dice que R satisface la restricción de integridad referencialsobre CA si se cumple: “en cualquier tupla de R o A_itiene valor nulo o existe una tupla en S que tiene en el atributo de J, f(A_i), el mismo valor”.

∀t ( t ∈R →( nulo(t(A_i)) ∨ ∃m (m ∈S y t(A_i) = m(f(A_i))) )) en caso contrario R viola esta restricción.

Clave Ajena

Empleado(nro_emp: dom_emp, dni: dom_dni, nombre:dom_nom, teléfono:dom_tel, cod_dep: dom_dep) CP={nro_emp}, VNN={nombre}, UNI={dni}

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

Departamento(cod_dep: dom_dep, nombre:dom_nom) CP={cod_dep}, VNN={nombre}

Clave Ajena (integridad referencial)

K

J

J tiene restricción de unicidad f: K →J f (cod_dep) = cod_dep

R

S

en algunos casos se puede omitir

2.4 Restricciones de Integridad.

Clave Ajena con varios atributos

la clave ajena puede ser

parcialmente nula

2.4 Restricciones de Integridad.

Ejemplo:

Gestión de la programación de cursos en un Centro de Estudios.

9se mantiene información sobre las distintas materias sobre las que se imparten cursos: código interno de la materia y descripción (Materia)

9se dispone de un registro de profesores: código interno del profesor, nombre y nivel de preparación para impartir distintas materias (Profesor, Oferta)

9se mantiene información sobre la programación de cursos realizada: código interno del curso, materia, profesor y fecha prevista (Cursos)

(8)

2.4 Restricciones de Integridad.

cod_mat des BD A Bases de datos LINU X Linux W W W W eb Materia CP={cod_mat} c o d _ p r o f n o m b r e p 2 J e s ú s P é r e z p 3 L u i s G a r c í a p 5 P e d r o S a n z Profesor CP={cod_prof} CP={cod_mat, cod_prof}

CAj={cod_mat} →Materia f(cod_mat=cod_mat CAj={cod_prof} →Profesor f(cod_pof=cod_prof

cod_mat cod_prof nivel BDA p2 1 WWW p5 2 BDA p3 4

Oferta

Oferta satisface las restricciones de clave ajena para {cod_mat} y {cod_prof}

2.4 Restricciones de Integridad.

cod_mat des BD A Bases de datos LINU X Linux W W W W eb Materia CP={cod_mat} c o d _ p r o f n o m b r e p 2 J e s ú s P é r e z p 3 L u i s G a r c í a p 5 P e d r o S a n z Profesor CP={cod_prof} CP= {curso}, VNN={fecha} CAj= {materia, profesor} →Oferta f(materia)=cod_mat, f(profesor)=cod_prof

curso materia profesor fecha

1 BDA p2 1/6/99 2 LINUX ? 1/5/00 4 WWW ? 15/2/00 3 ? ? 15/3/01 Cursos CP={cod_mat, cod_prof} CAj={cod_mat} →Materia CAj={cod_prof} →Profesor

Oferta

¿ satisface Cursos la restricción de clave ajena para {materia, profesor} ?

satisface depende depende satisface

2.4 Restricciones de Integridad.

Tres tipos de Integridad Referencial:

9 I.R Débil

9 I.R Parcial

9 I.R Completa

2.4 Restricciones de Integridad.

cod_mat des BD A Bases de datos LINU X Linux W W W W eb Materia CP={cod_mat} c o d _ p r o f n o m b r e p 2 J e s ú s P é r e z p 3 L u i s G a r c í a p 5 P e d r o S a n z Profesor CP={cod_prof}

curso materia profesor fecha

1 BDA p2 1/6/99 2 LINU X ? 1/5/00 4 W W W ? 15/2/00 3 ? ? 15/3/01 Cursos CP={cod_mat, cod_prof} cod_mat cod_prof nivel

BDA p2 1 WWW p5 2 BDA p3 4

Oferta

I.R Débil: Cursos satisface la restricción de clave ajena para {materia, profesor}

CP= {curso}, VNN={fecha}

satisface satisface satisface satisface

(9)

Clave Ajena

•I. R. Débil:“para toda tupla de R se debe cumplir que si todos los atributos de K tienen un valor que no es el nulo, entonces debe existir una tupla en S que tome esos mismos valores en los correspondientes atributos de J”

∀t ( t ∈R →(∃A_i(A_i∈K ∧nulo(t.A_i))

∨

∃m (m ∈S ∧ ∀A_i( A_i∈K →t.A_i= m.f(A_i) )))) en caso contrario R viola esta restricción.

Si K contiene más de un atributo, R satisface la restricción de integridad referencial sobre CAsi, según el tipo elegido, se cumple la propiedad que se especifica a continuación:

cod_mat des BD A Bases de datos LINU X Linux W W W W eb Materia CP={cod_mat} c o d _ p r o f n o m b r e p 2 J e s ú s P é r e z p 3 L u i s G a r c í a p 5 P e d r o S a n z Profesor CP={cod_prof} CP= {curso}, VNN={fecha} CAj= {materia, profesor} →Oferta f(materia)=cod_mat, f(profesor)=cod_prof

Oferta

I.R. Parcial: Cursos viola la restricción de clave ajena para {materia, profesor} satisface viola satisface satisface

Clave ajena

2.4 Restricciones de Integridad.

•I. R. Parcial:“para toda tupla de R se debe cumplir que si algún atributo de K no tiene valor nulo, entonces debe existir una tupla en S que tenga en los correspondientes atributos de J los mismos valores que los atributos de K que no tienen valor nulo”

∀t ( t ∈R →(∀Ai (Ai∈K →nulo(t(Ai)))

∨

∃m(m∈S ∧∀A_i((A_i∈K ∧¬nulo(t(A_i)) )→t(A_i) = m(f(A_i))) )))) en caso contrario R viola esta restricción.

2.4 Restricciones de Integridad.

cod_mat des BD A Bases de datos LINU X Linux W W W W eb Materia CP={cod_mat} c o d _ p r o f n o m b r e p 2 J e s ú s P é r e z p 3 L u i s G a r c í a p 5 P e d r o S a n z Profesor CP={cod_prof} CP= {curso}, VNN={fecha} CAj= {materia, profesor} →Oferta

Oferta

I.R. Completa: Cursos viola la restricción de clave ajena para {materia, profesor}

satisface viola viola satisface

(10)

Clave ajena

2.4 Restricciones de Integridad.

•I. R. Completa:“para toda tupla de R se debe cumplir que o todos los atributos de K tienen valor nulo o ninguno tiene valor nulo y entonces debe existir una tupla en S que tenga en los correspondientes atributos de J los mismos valores que los atributos de K”.

∀t ( t ∈R →(∀Ai (Ai∈K →nulo(t(Ai)))

∨

∃m (m ∈S ∧∀Ai (Ai ∈K →(¬nulo(t(Ai)) ∧t.Ai= m(f(Ai))) ) ))))

en caso contrario R viola esta restricción.

2.4 Restricciones de Integridad.

Clave Ajena con varios atributos.

los tres tipos de integridad

referencial se diferencian en el

tratamiento de las claves

ajenas parcialmente nulas

2.4 Restricciones de Integridad.

Los tres tipos de Integridad Referencial permiten expresar distintas normas de funcionamiento de la organización: I.R Débil:permite programar cursos sin conocer temporalmente la materia o el profesor, y en estos casos no se hace ninguna comprobación sobre la oferta disponible.

I.R Parcial:permite programar cursos sin conocer temporalmente la materia (resp. profesor), y en estos casos se hace una comprobación de la oferta disponible sobre la materia (resp. profesor).

I.R Completa:no permite programar cursos en los que se conoce sólo uno de los dos atributos (materia, profesor).

2.4 Restricciones de Integridad.

Clave Ajena con varios atributos con

restricción de valor no nulo.

coinciden los tres tipos de

integridad referencial

(11)

Ejemplo:

Gestión de pedidos de material en una empresa constructora.

9se mantiene información sobre los distintas materiales con los que se trabaja: código interno del material y descripción (Material)

9se dispone de un registro de provedores: código interno del proveedor, nombre y lista de precios a los que puede suministrar el material (Poveedor, Precios)

9se mantiene información sobre los proyectos en ejecución: código interno del proyecto y título (Proyecto)

9se mantiene información sobre los pedidos realizados con destino a los distintos proyectos en ejecución: material pedido, proveedor, proyecto y cantidad (Pedidos)

cod_mat des m1 viga m3 ladrillo m5 hormigón Material CP={cod_mat} cod_prov nombre pv2 Cons SA pv3 BBT pv4 Bertolin Proveedor CP={cod_prov} cod_proy título

py2 IES Cid py6 La FE

Proyecto

CP={cod_proy}

CP= {cod_mat, cod_prov, cod_proy} CAj= {cod_mat, cod_prov} →Precios CAj={cod_proy} →Proyecto

cod_mat cod_prov cod_proy cant

m1 pv3 py2 96 m1 pv2 py6 100 m3 pv3 py2 60 m1 pv4 py2 30 Pedidos CP={cod_mat, cod_prov} CAj={cod_mat} →Material CAj={cod_prov} →Proveedor

cod_mat cod_prov precio

m1 pv3 1000 m1 pv2 1500 m3 pv3 100 m1 pv4 900 Precios

2.4 Restricciones de Integridad.

cod_mat des m1 viga m3 ladrillo m5 hormigón Material CP={cod_mat} cod_prov nombre pv2 Cons SA pv3 BBT pv4 Bertolin Proveedor CP={cod_prov} cod_proy título

Proyecto

CP={cod_proy}

CP={cod_mat, cod_prov} CAj={cod_mat} →Material

cod_mat cod_prov precio

m1 pv3 1000 m1 pv2 1500 m3 pv3 100 m1 pv4 900

Precios

Precios satisface las restricciones de clave ajena para {cod_mat}, y

las claves ajenas {cod_mat}, y {cod_prov} están formadas por un único atributo (no pueden ser parcialmente nulas). Los tres tipos de I.R coinciden.

2.4 Restricciones de Integridad.

cod_proy título

Proyecto

CP= {cod_mat, cod_prov, cod_proy}

m1 pv3 py2 96

m1 pv2 py6 100

m3 pv3 py2 60

m1 pv4 py2 30

Pedidos cod_mat cod_prov precio

m1 pv3 1000 m1 pv2 1500 m3 pv3 100 m1 pv4 900

Precios

los atributos de la clave ajena {cod_mat, cod_prov} tienen restricción de valor NO NULO por formar parte de la CP (la clave ajena no puede ser parcialmente nula). Los tres tipos de integridad referencial coinciden.

(12)

2.4 Restricciones de Integridad.

cod_proy título

Proyecto

CP= {cod_mat, cod_prov, cod_proy} CAj= {cod_mat, cod_prov} →Precios CAj={cod_proy} →Proyecto

m1 pv3 py2 96

m1 pv2 py6 100

m3 pv3 py2 60

m1 pv4 py2 30

Pedidos cod_mat cod_prov precio

m1 pv3 1000 m1 pv2 1500 m3 pv3 100 m1 pv4 900

Precios

Pedidos satisface la restricción de clave ajena para {cod_mat, cod_prov} en cualquier tipo de I.R

2.4 Restricciones de Integridad.

I.R Débil

I.R Completa

I.R Parcial

9 la clave ajena tiene un único atributo

9 todos los atributos de la clave ajena

tienen restricción de valor no nulo

Violación de la Integridad Referencial.

(Operaciones relevantes)

2.4 Restricciones de Integridad.

K

R

S

J

Modificar K Borrar en S Modificar J Clave Ajena

Dadas dos relaciones R y S tal que R tiene una clave ajena CA que hace referencia a S, las operaciones que pueden violar la integridad referencial son:

• Operaciones sobre R:

–Insertaruna tupla en R

–Modificarel valor de CA en una tupla de R

• Operaciones sobre S:

–Borrar una tupla de S

–Modificarel valor de J en una tupla de S

Violación de la Integridad Referencial.

2.4 Restricciones de Integridad.

(13)

Mantenimiento de la Integridad Referencial.

(Operaciones relevantes sobre R).

K

R

S

J

Insertar en R

Modificar K Si la operación relevante sobre

R viola la integridad referencia el SGBD rechaza la operación .

Clave Ajena

Mantenimiento de la Integridad Referencial.

(Operaciones relevantes sobre S).

K

R

_S

J

Si la operación relevante sobre S viola la integridad referencia el SGBD tiene tres opciones: 9rechazar la operación

9transmitir en cascada la operación 9poner a nulos CA en las tuplas afectadas de R

Borrar en S Modificar J Clave Ajena

Mantenimiento de la

Integridad Referencial

2.4 Restricciones de Integridad.

nro_emp dni nombre teléfono cod_dep 1 2045021 Juan C. Ródenas 7796 d1 2 1234567 Robert Capilla ? d2 3 3567896 José V. Dualde 5760 d2 4 1432652 María Frasnedo 3560 d1 5 2045333 Cristina Guillot 7439 d3 cod_dep nombre d1 Ventas d2 Compras d3 Contabilidad Empleado Departamento

BORRADO en CASCADA

Mantenimiento de la

Integridad Referencial

2.4 Restricciones de Integridad.

BORRADO a NULOS

?

(14)

Mantenimiento de la

Integridad Referencial

2.4 Restricciones de Integridad.

CAj={cod_dep} →Departamento, f(cod_dep)=cod_dep, Modificación a NULOS Modificación a NULOS d8 ? ?

Mantenimiento de la

Integridad Referencial

2.4 Restricciones de Integridad.

CAj={cod_dep} →Departamento, f(cod_dep)=cod_dep, Modificación en CASCADA Modificación en

CASCADA

d8

Mantenimiento de la Integridad Referencial.

(Operaciones relevantes sobre S).

K

R

_S

J

Modificar J en S

2.4 Restricciones de Integridad.

Borrar en S Modificar J Clave Ajena a Nulos en CASCADA

2.4 Otras Restricciones de Integridad.

Restricciones de integridad generales

son

aquéllas que no se pueden expresar con las

propiedades anteriores. Estas pueden ser:

–

Restricciones de integridad estáticas

(CREATE ASSERTION ...).

–

Restricciones de integridad de transición

(Disparadores).

Para la definición de estas propiedades se

puede emplear un lenguaje de tipo lógico:

(15)

Para que una base de datos sea

válida

, se

han de cumplir todas las restricciones de

integridad que ésta tenga definidas.

La comprobación de las restricciones

generales y todos los otros tipos de

restricción (valor no nulo, unicidad, restricción

de dominio, ...) es competencia del SGBD

que debe asegurar que toda actualización de

la base de datos genera una nueva extensión

que satisface todas las restricciones.

La definición de una relación se puede

enriquecer con las siguientes

restricciones:

• VNN: restricción de valor no nulo,

• Uni: restricción de unicidad,

• CP: clave primaria,

• CAj: clave ajena (incluyendo directriz de

comportamiento),

• Restricciones de integridad generales.

2.4 Resumen

EJEMPLO:

(Esquema relacional genérico)

»S(B1:E1, B2:E2,…, Bt:Et) CP: {Bj,…, Bn} Uni: {Bq,…, Br} VNN: {Bs,…, Bt} »R(A1:D1, A2:D2,…, Ar:Dr) CP: {Ai,…, Am} CAj: {Ao,…, Ap} →S f: Ao→Bj … Ap→Bn borrado en cascada modificación a nulo