bases de datos relacionales

Bases de Datos Relacionales

Universidad Autonóma de TlaxcalaDr. Alberto Portilla Flores

Modelo relacional

Notas by Dr. José Luis Zechinelli Martini

3

Introducción El almacenamiento y la manipulación de

datos ha sido una aplicación de importancia mayor desde los primeros días de las computadoras

Las aplicaciones que requieren de almacenar y recuperar datos, continúan evolucionando Presumiblemente, las tecnologías de Gestión de

Datos continuarán siendo el objeto de esfuerzos de desarrollo e innovación

4

CaracterísticasBasado en la teoría de relaciones:

Álgebra relacional, Calculo de predicados SQL.

Modelo de datos simple y formal.

Alto grado de independencia física.

Lenguaje declarativo (SQL) de manipulación de datos.

5

Conceptos (1)Un dominio es un conjunto de valores atómicos

caracterizados por un nombre:

enteros, nombres, edades, ciudades, colores_primarios, etc.

Puede ser definido en intención o en extensión:

enteros = lN+.

enteros = {0, 1, 2, 3, …, ∞ }.

6

Ejemplo colores_primarios =

{rojo, verde, azul}.

nombres = {cadenas de 20 caracteres} ó {juan, pedro, pablo}.

enteros = N+ {0,1,2,3, . . ., ∞} {numeros <= 10} {1,4,6,7}

Notas Por cada dominio es necesario precisar su nombre y el tipo de datos. Desde el punto de vista de la implementación se tienen dominios

predefinidos. (Enteros, booleanos, etc.)

7

Conceptos (2)

Una relación n-aria es un subconjunto de un producto cartesiano de n dominios.

Sean {D1, D2, ... , Dn} un conjunto de dominios y R una relación, R D1 D2 ... Dn.

Cada elemento (v1, v2, ... , vn) R es una tupla y n

es el grado de R.

8

Relación n-aria (1/2)

Intención de una relación (esquema de relación):

Dominios: D1, D2, ..., Dn.

Constituyentes: A1, A2, ..., An donde dom(Ai) = Di con i = 1, 2, ...,

n.

Nombre de relación: R

regla o predicado que indica si una tupla (v1, v2, ..., vn) pertenece o no

al producto cartesiano de dominios que definen la relación.

Extensión de una relación:

{ (v1, v2, ..., vn) D1x D2 x ... Dn ½ R(v1, v2, ..., vn) = verdadero }.

9

Relación n-aria (2/2) Un esquema de relación de grado n se denota por R(A1, A2, ..., An).

Un constituyente dentro de una relación indica el rol de un dominio en una relación.

Una asignación de un D de valores a cada atributo o constituyente:

Di = dom( Ai ).

Ai : Di.

Una tupla en R se denota por (v1, v2, ..., vn) donde vi es el valor

correspondiente al constituyente A.

Un predicado de pertenencia de tuplas a la relación:

R(v1, v2, ... , vn) = verdadero.

10

EjemploD1 = Nombre

D2 = Ciudad

D3 = Ciudad

Nombre = {juan, pedro, pablo}

Ciudad = {puebla, cholula, mexico}

Calcule D=NombrexCiudadxCiudad

La tupla (n,v1,v2) E R tiene el significado:La persona de nombre n nació en la ciudad v1 y

vive en la ciudad v2.

11

Relación n-aria: Resumen Intención: esquema de relación R(A1, A2, ..., An) que es

invariante en el tiempo.

Extensión: representa los datos presentes a un instante dado en la BD estado de la BD.

Un valor de atributo es atómico (eventualmente nulo).

La n-tupla es una secuencia ordenada de valores de atributos.

Una relación es un conjunto de n-tuplas donde no existe noción de orden.

12

Ejemplo

R( ESCUELA, DIA, HORA, SALON, PROFESOR )

Intención: El PROFESOR p da clases en la ESCUELA e el DIA d, a la HORA

h, en la SALON s.

El dominio del constituyente HORA está definido por el conjunto de enteros comprendido entre 7 y 20.

Un profesor a una hora dada en el mismo día sólo puede estar en un salón.

Los atributos DIA, HORA y PROFESOR no pueden ser nulos.

13

Esquema de relación (2)Se define como una pareja R = < X, S > donde

X = { A1, A2, ..., An } un conjunto de constituyentes y S representa parte de la semántica de la relación.

S es un conjunto de restricciones de integridad.

14

Restricciones de integridad

Una restricción de integridad es una propiedad que no varía en el tiempo.

Son un medio para asegurar que los cambios que se hacen en la base de datos por usuarios autorizados no resultan en una perdida de la coherencia de los datos.

15

Tipos de restricciones Restricciones de dominio: forma más elemental de

restricción de integridad permiten probar valores insertados en la base.

Valores nulos: la inserción de tuplas incompletas puede introducir valores nulos en la base. Para ciertos atributos los valores nulos son inapropiados.

Llaves: por definición las tuplas de una relación son distintas entre si. El sistema debe asegurar esta unicidad.

16

Tipos de restricciones

Estructurales: describe la manera en que los atributos se relacionan entre si.

Restricciones referenciales: describen la manera en que se relacionan las tablas. Aseguran que un valor que aparece en una relación para un conjunto de atributos aparece para un cierto conjunto de atributos en otra relación.

17

Restricción referencial (ejemplo)

CURSO NOMBRE

IS-341IS-417

PedroJuan

CURSO MATERIA

IS-341IS-417

BDSD

ESTUDIANTE HORARIO

18

Super-llave de una relación

Existe un subconjunto de atributos de una relación que permite diferenciar a las tuplas entre si. Esto quiere decir que dos tuplas no tienen la misma combinación de valores para esos atributos.

Todo conjunto de atributos que verifica esta propiedad es una super-llave del esquema.

Existes al menos una super-llave ...

19

Llave de una relación

Una llave es un conjunto de atributos mínimo que permite identificar de manera única a una tupla.

La llave es una super-llave tal que si le quitamos un atributo entonces ya no es super-llave.

Ejemplo:

R( ID, NOMBRE, APELLIDOS, FECHA )

20

Llave extranjera: dependencias inter-relaciones

Sean R1 y R2 dos relaciones:

Una llave extranjera impone que el valor de un grupo de atributos de R1 aparece como valor de llave en R2 (dependencias inter-relaciones).

VIAJE( NUM_VIAJE, DESTINACION, SALIDA, LLEGADA, PRECIO )

CLIENTE( NUM_CLIENTE, NOMBRE, APELLIDOS, DIRECION )

RESERVACION( NUM_RESERVACION,

NUM_VIAJE, FECHA_R, NUM_CLIENTE, OFERTA )

21

Restricciones de integridad referencial

Expresada entre dos relaciones

Consiste en verificar que la información utilizada en una tupla para designar otra es válida, en particular si la tupla designada existe.

Orden de creación o destrucción de las relaciones (tablas).

22

Esquema relacional de bases de datos

Conjunto de esquemas de relación:

S = ( R1, R2, ..., Rn )

S conjunto de restricciones de integridad:

(S, S)

( R1, R2, ..., Rn ) realización de un esquema relacional.

23

CURSO NOMBRE CIUDAD FECHAN DEPORTE

IS-341IS-341IS-341IS-580

PedroMaríaJuliaJuan

CholulaPueblaPueblaCholula

11/10/8004/04/8015/03/8120/07/79

FootballNataciónEsgrimaFootball

CURSO DIAS HORA MATERIA PROFESOR SALON LIBRO

IS-341IS-580

MJ LMV

13:00-13:5005:00-05:50

BDSBD

José LuisJosé Luis

21022245

NavatheNavathe

ESTUDIANTE

HORARIO

NOMBRE MATERIA CALIFICACION

PedroMaríaJulia

BDBDBD

8.0 10.0 9.0

RESULTADO

24

Ejemplo

Dado tres esquemas de relación:

ESTUDIANTE = <{ CURSO, NOMBRE, CIUDAD, FECHAN, DEPORTE }, S1>

a) “El estudiante de NOMBRE n toma el CURSO c, habita en la CIUDAD ci, tiene por FECHA de nacimiento f y práctica el DEPORTE d”.

b) CIUDAD no puede ser NULO.

c) La llave primaria de la relación ESTUDIANTE es (NOMBRE).

S1 = { a, b, c }

25

Ejemplo

HORARIO = <{ CURSO, DIAS, HORA, MATERIA,

PROFESOR, SALON, LIBRO }, S2>

d) “El PROFESOR p enseña la MATERIA s con el LIBRO l en el CURSO c los DIAS d a la HORA h en el SALON s”.

e) “El número de horas de clase para cada curso es inferior a 30”.

f) “Un profesor a una hora determinada sólo puede estar en un salón e impartiendo un solo curso”.

2S = { d, e, f }

26

Ejemplo

RESULTADO = <{ NOMBRE, MATERIA,

CALIFICACION }, 3S >

g) “El estudiante de NOMBRE n obtuvo la CALIFICACION c en la MATERIA m”.

h) La llave primaria de la relación RESULTADO es (NOMBRE, MATERIA).

3S = { g, h }

27

Ejemplo

Esquema relacional: (S, S)

i) “El CURSO de la relación ESTUDIANTE debe existir como valor en la relación HORARIO”.

j) “La MATERIA de la relación RESULTADO debe existir como valor en la relación HORARIO”.

k) “Los NOMBREs de la relación RESULTADO están contenidos en el conjunto de nombres de la relación ESTUDIANTE”.

( S = {ESTUDIANTE, HORARIO, RESULTADO}, S = { i, j, k } )

28

Ejercicio

a) Supongamos que se quiere agregar la siguiente tupla a la relación HORARIO:

(IS-417, LMV, 08:00-08:50, Pablo, 7110, Mullender )

¿Es está una relación del esquema HORARIO?

b) ¿Es posible agregar en ESTUDIANTE la siguiente tupla?

(IS-580, Alicia, México, 08/12/78, Esgrima )

Alberto Portilla Flores

Agregar otrom ejemplo.

29

Modelo dedatos

(modelo lógico)

Representación de datos

(esquema)

Lenguaje de manipulación

(necesidades del usuario)


Aqui se debera realizar la primera evaluacion.Se presenta la siguiente parte del curso.

Álgebra relacional


¿Cómo manipular las relaciones?

Álgebra relacional: colección de operadores que permiten: La selección de tuplas en una relación; La combinación de tuplas de relaciones diferentes.

Principio: La aplicación de uno o más operadores sobre una

relación da siempre como resultado una relación (cerradura).

La información a recuperar se expresa bajo la forma de una relación obtenida por aplicación sucesiva de operadores binarios o únarios cuyos operandos son las relaciones de la BD.

Agencia de viajes: RUTA DEL SOL

NOMBRE DIRECCIONJuanRosarioPedroAmaliaCelia

Cain MurrayCain MurrayIgnacio BernalJosé GaosColey Taylor

DESTINACION SALIDA PRECIOAcapulcoVeracruz

01/06/0415/08/04

2000.001000.00

CLIENTE VIAJE

NUMERO NOMBRE DESTINACION001002003004

JuanJuanCeliaAmalia

VeracruzAcapulcoAcapulcoAcapulco

RESERVACION

NOMBRE TIPOFIESTA INNCAMINO REALMIRADOR

453

HOTEL

NOMBRE TIPOCITADINESHOME LUXFIESTA INN

354

APARTAMENTOS


Motivacion de los operadores

Clientes de ruta del sol que viven en el colegio Cain Murray:

NOMBRE DIRECCIONJuanRosario

Cain MurrayCain Murray

NOMBRE DIRECCIONJuanRosarioPedroAmaliaCelia

Cain MurrayCain MurrayIgnacio BernalJosé GaosColey Taylor

CLIENTE R

Selección

Nombre de los clientes que tienen una reservación:

NOMBREJuanCeliaAmalia

NUMERO NOMBRE DESTINACION001002003

JuanCeliaAmalia

VeracruzAcapulcoAcapulco

RESERVACION R

Proyección

Hoteles ofrecidos por ruta del sol en sus viajes

DESTINACION SALIDA PRECIOAcapulcoVeracruz

01/06/0315/08/03

2000.001000.00

VIAJE


453

HOTEL

Producto cartesiano

NOMBRE TIPO DESTINACION SALIDA PRECIO

FIESTA INNFIESTA INNCAMINO REALCAMINO REALMIRADORMIRADOR

445533

AcapulcoVeracruzAcapulcoVeracruzAcapulcoVeracruz

01/06/0315/08/0301/06/0315/08/0301/06/0315/08/03

2000.001000.002000.001000.002000.001000.00

R

Alojamiento con el que trabaja ruta del sol en sus destinos:


453

HOTEL


354

APARTAMENTOS

Unión

NOMBRE TIPOFIESTA INNCAMINO REALMIRADORCITADINESHOME LUX

45335

R

Diferencia


453


354

Cadenas que trabajan con ruta del sol y que sólo ofrecen hoteles:

NOMBRE TIPOCAMINO REALMIRADOR

53

HOTEL APARTAMENTOS

R

Notación (1)Sean R(X), S(Y), T(Z) tres relaciones con:

X = {X1, X2, ..., Xn}

Y = {Y1, Y2, ..., Yp}

Z = {Z1, Z2, ..., Zq}

Las tuplas son expresadas r є R, s є S, t є T.

Notación (2)

f es una expresión de selección tal que:

Xi θ constante con θ Î { =, >, <, =, <=, >= } es una expresión de selección.

Xi θ Xj son expresiones de selección.

Si f1 y f2 son expresiones de selección, entonces f1 ^ f2, f1 v f2, ¬ f1 son expresiones de selección.

Operaciones de baseOperación Notación Resultado

Selección R : fσ f (R)

{ r(X) r Î R ^ f(r) = verdadero }

Proyección R : (A)π A (R)

{ r(A) r Î R }A Í X

Producto cartesiano

R × S { t = (r, s) r Î R ^ s Î S }Z = X È Y

Unión R È S { t t Î R v t Î S }( n = p ^ dom(Xi) = dom(Yi) )

Diferencia R – S { t t Î R ^ t Ï S }( n = p ^ dom(Xi) = dom(Yi) )

Ejemplos (1)Selección:

CLIENTE : DIRECCION = “Cain Murray”σ DIRECCION = “Cain Murray” (CLIENTE)

Proyección:RESERVACION : (NOMBRE )π NOMBRE (RESERVACION)

π NOMBRE (σ DIRECCION = “Cain Murray” (CLIENTE))

Ejemplos (2)

Producto cartesiano: π NOMBRE, DESTINACION (HOTEL × VIAJE)

σ PRECIO < 1500 (π NOMBRE, DESTINACION (HOTEL × VIAJE))

Unión: HOTEL È APARTAMENTOS

Diferencia: HOTEL – APARTAMENTOS (HOTEL È APARTAMENTOS) –

(σ ¬ TIPO = 3 (HOTEL È APARTAMENTOS))

SQL

Notas by Dr. Luciano García-Bañuelos

44© L. García-Bañuelos

Estructura básica de una consulta

SELECT [DISTINCT]

FROM

WHERE

A ( (R1R2...Rn))

Lista-de-relaciones

Predicado-de-selección

Lista-de-atributos


Algunos comentarios La parte SELECT indica la lista de atributos a proyectar en el

resultado La clausula opcional DISTINCT elimina tuplas duplicadas en

el resultadoEste es el comportamiento normal en teoría de conjuntos

El caracter * puede substituir a la lista de atributos, e indica que todos serán proyectados

La parte FROM especifica una lista de relaciones sobre las cuales se calculará un producto cartesiano

La parte WHERE especifica la condición de selección Nótese que esta parte es opcional


Nuestra primera consulta¿Cuál es el nombre de los empleados que ganan más

de 34000?

NE NOMBRE PUESTO SAL

1 J. Pérez Gerente 40000

2 M. López Analista 34000

3 A. Lira Analista 34000

4 J. Mirón Consultor 40000

5 B. Cortina Ingeniero 34000

6 L. Cosio Programador 24000

7 R. Barco Programador 24000

8 J. Aspe Gerente 40000

NOMBRE(sal>34000(EMP))EMP NOMBRE

select NOMBRE

sal>34000 EMP

from EMPwhere sal>34000

NOMBRE

J. Pérez

J. Mirón

J. Aspe


Álgebra relacional y SQL

(R)

A(R)

R S

select *from Rwhere

select Afrom R

select *from R, S


Ejemplos (1/3)




EMPNP NOMBRE PRESUPUESTO NG

1 Textil IS 2000000 1

2 e-Portal 2500000 1

PROY

EMP EMP.NE=PROY.NG PROYselect *from EMP, PROYwhere EMP.NE = PROY.NG

NE NOMBRE PUESTO SAL NP NOMBRE PRESUPUESTO NG

1 J. Pérez Gerente 40000 1 Textil IS 2000000 1

1 J. Pérez Gerente 40000 2 e-Portal 2500000 1


Ejemplos (2/3)




EMPNP NOMBRE PRESUPUESTO NG

1 Textil IS 2000000 1

2 e-Portal 2500000 1

PROY

select E.NOMBREfrom EMP E, PROY Pwhere E.NE = P.NG

NOMBRE

J. Pérez

J. Pérez

select DISTINCT EMP.NOMBREfrom EMP, PROYwhere EMP.NE = PROY.NG

NOMBRE

J. Pérez

Podemos asignar

nombres cortos a cada relación


Ejemplos (3/3)SQL provee un operador especial para buscar

cadenas con expresiones regulares










EMP

NOMBRE

J. Pérez

M. López

select NOMBREfrom EMPwhere NOMBRE like ‘%ez’


UniónBusquemos los nombres de los gerentes de

los proyectos










EMP1EMP2

NP NOMBRE PRESUPUESTO NG

1 Textil IS 2000000 1

2 e-Portal 2500000 3

PROY


Versión en SQLLa siguiente consulta es una respuesta

válida:

select EMP1.NOMBREfrom EMP1, PROYwhere EMP1.NE = PROY.NG

UNION

select EMP2.NOMBREfrom EMP2, PROYwhere EMP2.NE = PROY.NG


Intersección Busquemos ahora los nombres de los empleados que

aparecen en ambas relaciones










EMP1 EMP2

select NOMBREfrom EMP1INTERSECTselect NOMBREfrom EMP2


Diferencia Busquemos ahora los nombres de los empleados que

están en EMP1 pero no en EMP2










EMP1 EMP2

select NOMBREfrom EMP1EXCEPTselect NOMBREfrom EMP2

EXCEPT es el nombre para la diferencia. Algunos SGBDs usan MINUS


Consultas anidadas (operador IN)

¿Qué hace la siguiente consulta?

select *from EMP1where NE in (select EMP2.NE

from EMP2)






EMP2





EMP1NE NOMBRE PUESTO SAL


¡Calcula la intersección de las dos relaciones!


Operador IN (2/3) También nos sirve para calcular la semireunión

En la consulta anidada, buscamos en PROY el conjunto de valores de NG (número de empleado del gerente de proyecto) y posteriormente seleccionamos todas las tuplas de EMP que tengan un NE en el resultado

select *from EMPwhere NE in (select NG

from PROY)


Operador IN (3/3)Con el mismo principio, calculemos la

diferencia

select *from EMP1where NE NOT IN (select EMP2.NE

from EMP2)


Otros operadoresEXISTS

Regresa un valor verdadero si una subconsulta resulta en una relación no nula

Operadores de comparación con conjuntosPermiten comparar (cf. =, <, >, <=, >=, <>) un

valor con las tuplas en el resultado de una consulta:

ANY Verdadero si alguno de elloscumplen la comparación

ALL Verdadero si todos cumplen la comparación


Consultas correlacionadas En los ejemplos anteriores, la subconsulta es

independiente de la externa

Sin embargo, es posible hacer referencia a resultados en la consulta externa

select *from EMPwhere EXISTS (select *

from PROYwhere PROY.NG = EMP.NE)

Esta consulta calcula:

EMP PROY.NG = EMP.NE PROY


Operadores de agregaciónSe trata de operaciones aritméticas que se

aplican sobre los resultados en una columna COUNT ([DISTINCT] A)

Número de valores (distintos) en la columna A

SUM ([DISTINCT] A)Suma de todos los valores (distintos) en la columna A

AVG ([DISTINCT] A)Promedio de los valores (distintos) en la columna A

MAX(A)Valor máximo de la columna A

MIN(A)Valor mínimo de la columna A


Ejemplos¿Cuantos empleados están registrados en la

relación EMP?










EMP

select COUNT(NOMBRE)from EMP

select COUNT(*)from EMP


Ejemplos¿Cuales son los nombres de los empleados

con mayor salario?

select NOMBREfrom EMPwhere salario >= ALL (select salario

from EMP)

select NOMBREfrom EMPwhere salario = (select MAX(salario)

from EMP)

¡Una segunda alternativa usando operaciones de agregación!


Fórmulas GROUP BY y HAVING

Permiten agrupar subconjuntos de tuplas, y aplicar operadores de agregación

SELECT [DISTINCT] Lista-de-atributos

FROM Lista-de-relaciones

WHERE Predicado-de-selección

GROUP BY Lista-de-grupos

HAVING Calificación-sobre-el-grupo


Ejemplos (1/2)¿Cuantos empleados hay en los diferentes

puestos?










EMP select PUESTO, COUNT(*)from EMPgroup by PUESTO

PUESTO COUNT(*)

Gerente 2

Analista 2

Consultor 1

Ingeniero 1

Programador 2


Ejemplos (2/2)¿Cuantos empleados hay en los diferentes

puestos, con salario mayor a 30000?










EMP select PUESTO, COUNT(*)from EMPgroup by PUESTOhaving SAL > 30000

PUESTO COUNT(*)

Gerente 2

Analista 2

Consultor 1

Ingeniero 1

Concepción de bases de datos


Objetivos del modelado

Permitir una mejor comprensión: Sistemas reales demasiado complejos. Abstracción de los aspectos cruciales del problema. Omisión de detalles.

Permitir una concepción progresiva: Abstracción y refinamiento sucesivos. Posibilidad de implementar rápidamente. Organización en módulos o vistas. Generación de las estructuras de datos y de sus tratamientos.

Facilitar la visualización del sistema: Diagramas con notaciones simples y precisas. Comprensión visual y no solamente intelectual.

Pasos de la concepción

Cinco etapas:

Percepción del mundo real.

Elaboración del esquema conceptual.

Concepción del esquema lógico.

Afinación del esquema lógico.

Elaboración del esquema físico.

1.- Percepción del mundo real

Esta etapa consiste en:

Estudiar los problemas de los usuarios y comprender sus necesidades.

Realizar entrevistas.

Analizar la información y los procesos propios de una aplicación.

Estudiar los casos parciales (en caso de tener problemas al entender el problema completo).

Resultado: esquemas externos.

Se requiere utilizar procesos de tipo negocios, reingeniería y de modelado de problemas (ingeniería de software, economía, psicología).

2.- Elaboración del esquema conceptual

Integración de los esquemas externos de la etapa precedente en un esquema conceptual: global,

no redundante,

Coherente.

Ida y regreso con la etapa anterior.

Resultado: un esquema conceptual (ER, UML).

3.- Concepción del esquema lógico

Transformación del esquema conceptual en estructuras de datos soportados por el SGBD elegido: Relaciones (sistemas relacionales). Relaciones + tipos (sistemas objeto relacional). Clases y asociaciones (sistemas orientado a objetos).

Resultado: un esquema lógico (etapa automatizable).

4.- Refinamiento del esquema lógico

El esquema lógico es un buen esquema: Sin olvidos.

Ni redundancia de información.

Resultado: un esquema lógico en buena forma.

5.- Elaboración del esquema físico

Obtener un buen desempeño:

Transacciones (identificar los patrones de acceso y de modificaciones frecuentes).

Indexación.

Reagrupar, dividir, replicar.

bases de datos relacionales

Entertainment & Humor