Esta es una vista previa del archivo. Inicie sesión para ver el archivo original
0-presentacion-EDAT-121.pdf
Estructuras de Datos
Curso 2019 / 20
Profesores:
Teoría: Francisco de Borja Rodríguez, Pablo Castells, Roberto Marabini,
Prácticas: Julia Díaz, Álvaro del Val, Rodrigo Castro, Simone Santini,
Roberto Marabini
Escuela Politécnica Superior
Universidad Autónoma de Madrid
2
Datos generales de la asignatura
Profesor del grupo 121 – Francisco de Borja Rodríguez Ortiz
– Despacho B-328
– Tutorías: por cita a petición del estudiante
Profesores de la asignatura
– Teoría: Pablo Castells, Francisco de Borja Rodríguez, Robereto Marabini
– Prácticas: Roberto Marabini, Julia Díaz, Álvaro del Val, Rodrigo Castro, Simone Santini
– Coordinador asignatura: Pablo Castells
– Horario Teoría ( Grupo 121)
– Martes 09h a 10h
– Miércoles 09h a 11h
Horario Prácticas (Grupos EDAT-1211, EDAT-1212 y EDAT-1213)
– Viernes 09h a 11h y viernes de 11h a 13h
Prueba final
– Viernes 10 de enero 2020, 15 horas
3
Datos generales de la asignatura
Leer la guía de la asignatura
https://secretaria-
virtual.uam.es/doa/consultaPublica/look[conpub]BuscarPubGui
aDocAs?entradaPublica=true%20&idiomaPais=es.ES&_anoAc
ademico=2019&_centro=350&_planEstudio=473
https://secretaria-virtual.uam.es/doa/consultaPublica/look%5bconpub%5dBuscarPubGuiaDocAs?entradaPublica=true%20&idiomaPais=es.ES&_anoAcademico=2019&_centro=350&_planEstudio=473
4
¿Dé qué trata la asignatura?
Un primer curso de introducción a bases de datos,
tanto a un nivel lógico como a un nivel de implementación
5
¿Qué es una base de datos?
6
¿Qué es una base de datos?
Es un sistema informático que permite, organiza y administra el acceso a
datos de una forma eficaz.
Una fuente de información estructurada almacenada en memoria
secundaria
Habitualmente masiva en volumen de datos, variedad y complejidad de las
estructuras
Gestionada mediante tecnologías con un alto nivel de generalidad,
desarrolladas y estandarizadas al efecto
En la asignatura estudiaremos:
– Tecnologías de gestión de bases de datos –SQL y nociones prácticas
– Una introducción a las metodologías de diseño –modelo E/R
– La base formal sobre la que se asientan las tecnologías de bases de datos
–modelo relacional, formas normales, cálculo y álgebra relacional
– La implementación de tecnologías de gestión de bases de datos –registros,
índices, árboles B. etc..
7
Tipos de modelo de Bases de Datos
BDs Jerarquicas (estructura de árbol): Adabas, GT.M, IMS, Focus
BDs de Red (un nodo puede tener varios padres)
BDs Transacionales (envio y recepción de datos a grandes velocidades)
BDs Relacionales (uso de relaciones).
BDs Multidimensionales
BDs Orientadas a objetos
BDs Documentales
BDs Deductivas
TODAS se manejan mediante un sistema de gestión de Bases de Datos:
En e el caso de BDs Relacionles usaremos principalmente PostgreSQL que es
un SGBD relacional orientado a objetos y libre.
8
Supongamos que vamos a desarrollar una aplicación para escuchar música,
con redes sociales
Queremos manejar información sobre: canciones, artistas, álbumes, usuarios,
grupos, eventos, registro de accesos a canciones…
Esta información:
– Tiene estructura: p.e. un usuario tiene propiedades (nombre, nick, email, etc.) y
relaciones (amigos, artistas favoritos, escuchas a canciones, etc.)
– Tiene que almacenarse de forma persistente
Almacenamiento en disco
– Se tiene que poder inferir información de la almacenada y crear nueva
información
– Puede ser masiva: millones de usuarios, millones de canciones, miles de
millones de registros de escuchas
Inviable carga completa en RAM
Acceso continuo a disco en tiempo de ejecución
Un ejemplo
9
Para la aplicación necesitamos:
– Acceso (consulta) eficiente a los datos: mostrar a un usuario su lista de amigos,
la información de una canción, etc.
– Actualización eficiente de datos: añadir amigos, guardar un log de escuchas, etc.
– Acceso concurrente, robustez, seguridad…
Solución: guardar todos los datos en fichero(s) en disco y programar la
funcionalidad de acceso
– No perder la estructura de los datos
– El acceso a memoria secundaria es muy costoso
– El problema requiere soluciones elaboradas no triviales (las estudiaremos!)
En rigor esto ya se podría considerar una base de datos!
Un ejemplo
10
La dificultad y complejidad de un desarrollo desde cero son considerables,
y por otro lado…
Una buena parte del problema a resolver se repite en muchos dominios:
gestión de personal, gestión de inventarios, gestión universitaria, bibliotecas,
reserva de viajes, banca, finanzas, contabilidad, competiciones deportivas,
correo electrónico, buscadores web…
Por tanto hay ciertos aspectos comunes:
– Diseño de las estructuras: tabular
– Almacenamiento físico
– Consulta y actualización
– Integridad, robustez, concurrencia, seguridad…
Tecnología de bases de datos
– Lenguaje estándar de creación, consultas y actualización: SQL
– Motores de ejecución eficiente de las sentencias SQL
– Interfaz de usuario
– Interfaz de programación (ODBC, JDBC, PHP)
– Metodologías de diseño: modelo Entidad / Relación
– Paradigmas: modelo relacional, formas normales, cálculo y álgebra
Lo estudiaremos (implementación)
SGBDHerramientas
Método
y teoría
11
Programador
aplicación
Software
aplicación
Interfaz de usuarioUsuario
final
Lógica de la aplicación
Archivos de datos
Memoria externa
Acceso y gestión de datos
Arquitectura
ad hoc (se ha
creado especialmente
para esta situación
concreta, por tanto, no
generalizable ni
utilizable para otros
propósitos)
12
Programador
aplicación Software
aplicación
Entorno / herramientas
de administración
Navicat
SQLYog
etc.
Administrador
SGBD
Almacenamiento
Consultas
Actualización
Bases de datos
Lógica de la
aplicación
Sentencias
SQL
Interfaz de usuario
Usuario
final
ODBC, JDBC, PHP… API BD
Arquitectura
BD
13
La dificultad y complejidad de un desarrollo desde cero son considerables,
y por otro lado…
Una buena parte del problema a resolver se repite en muchos dominios:
gestión de personal, gestión de inventarios, gestión universitaria, bibliotecas,
reserva de viajes, banca, finanzas, contabilidad, competiciones deportivas,
correo electrónico, buscadores web…
– Diseño de las estructuras: tabular
– Almacenamiento físico
– Consulta y actualización
– Integridad, robustez, concurrencia, seguridad…
Tecnología de bases de datos
– Lenguaje estándar de creación, consultas y actualización: SQL
– Motores de ejecución eficiente de las sentencias SQL
– Interfaz de usuario
– Interfaz de programación (ODBC, JDBC, PHP)
– Metodologías de diseño: modelo Entidad / Relación
– Paradigmas: modelo relacional, formas normales, cálculo y álgebra
Lo estudiaremos (implementación)
SGBDHerramientas
Método
y teoría
14
La dificultad y complejidad de un desarrollo desde cero son considerables,
y por otro lado…
Una buena parte del problema a resolver se repite en muchos dominios:
gestión de personal, gestión de inventarios, gestión universitaria, bibliotecas,
reserva de viajes, banca, finanzas, contabilidad, competiciones deportivas,
correo electrónico, buscadores web…
– Diseño de las estructuras: tabular
– Almacenamiento físico
– Consulta y actualización
– Integridad, robustez, concurrencia, seguridad…
Tecnología de bases de datos
– Lenguaje estándar de creación, consultas y actualización: SQL
– Motores de ejecución eficiente de las sentencias SQL
– Interfaz de usuario
– Interfaz de programación (ODBC, JDBC, PHP)
– Metodologías de diseño: modelo Entidad / Relación
– Paradigmas: modelo relacional, formas normales, cálculo y álgebra
Lo estudiaremos (implementación)
SGBDHerramientas
Método
y teoría
15
Modelo Entidad / Relación
Modelo relacionalformas normales
SQL
Implementación
Consultas
cálculo y álgebra
Modelo Entidad / Relación
Modelo relacional formas normales
SQLConsultas
cálculo y álgebra
Implementación
Temario
Introducción y fundamentos
Introducción a SQL
Modelo Entidad / Relación
Modelo relacional
Diseño relacional: formas normales
Consultas: cálculo y álgebra relacional
Implementación de bases de datos
– Estructura física: campos y registros
– Indexación: índices simples, árboles B, hashing
– Compresión
16
Introducción y fundamentos
Introducción a SQL
Modelo Entidad / Relación
Modelo relacional
Diseño relacional: formas normales
Consultas: cálculo y álgebra relacional
Implementación de bases de datos
– Estructura física: campos y registros
– Indexación: índices simples, árboles B, hashing
– Compresión
Temario
17
Nivel práctico
Uso / administración básica de BDs
Análisis, diseño y creación de BDs
Manipulación de BDs, consultas
Manejo de SQL
Nivel conceptual
Comprensión de los principios y formalismos sobre los que se asientan
las tecnologías de BDs
Nivel técnico
Conocimiento de las técnicas de implementación interna de un SGBD
Objetivos
18
Bibliografía
Fundamentos de sistemas de bases de datos. Ramez Elmasri, Shamkant
Navathe. Pearson Addison Wesley, 2007. INF/681.31.65/ELM.
Database Management Systems. Raghu Ramakrishnan, Johannes Gehrke.
McGraw-Hill, 2003. INF/C6160/RAM.
Database Systems: The Complete Book. Hector Garcia-Molina, Jeffrey D.
Ullman, Jennifer Widom. Prentice Hall, 2008.
Fundamentos de diseño de bases de datos. Abraham Silberschatz.
McGraw-Hill, 2007. INF/681.31.65/SIL.
Estructuras de archivos: un conjunto de herramientas conceptuales.
Michael J. Folk, Bill Zoellick. Addison-Wesley, 1992. INF/681.3.01/FOL.
19
Relación con otras asignaturas
Programación I y II, Análisis de algoritmos
– Programación y desarrollo de software técnicas específicas para
almacenamiento y acceso a datos estructurados masivos en disco
– Algoritmia en RAM revisión para datos en disco
Análisis y Diseño de Software
– Modelado de datos: UML
Sistemas Informáticos I
– Optimizacíon de consultas, interfaces de programación
– Bases de datos distribuidas
– Transacciones
Ingeniería del Software
– Análisis y diseño de aplicaciones
20
60% Teoría ≥ 5 para hacer media
Examen final (10 enero) Ejer-cicios
Evaluación
Prueba intermedia liberatoria (≥ 6)
– La nota del parcial liberado se traslada a la nota del examen final,
escalada a la puntuación de la parte correspondiente
– El parcial cubrirá un 40-60% de la materia
Ejercicios
– Entrega de ~25 ejercicios (ver en Moodle)
– 2 entregas a lo largo del curso
– La entrega se realizará en pdf vía Moodle
Convalidación de prácticas: escribir a Roberto Marabini (≥ 7, ≥ 3 teoría)
Prueba intermedia (20 nov)
Liberatorio con ≥ 6
90% 10%
40% Prácticas ≥ 5 (cada práctica ≥ 4) para hacer media
Sólo si sube la nota
21
Grupos de Laboratorio
Apuntarse en Moodle (hacer una elección del grupo).
Las practicas comienzan el 20 de septiembre.
1-intro-EDAT-121.pdf
Introducción
(Cap 1 y 2 - Elmasri 5ª edición)
2
Que es una BD
Una BD es un sistema informático que permite, organiza y administra
el acceso a datos de una manera eficiente.
Es una tecnología informática que permite de una manera eficaz
manejar información estructurada y masiva en almacenamiento
persistente.
En definitiva es un
– conjunto de datos relacionados entre si y
– las técnicas para manipular los mismos.
Manipular en el sentido amplio.
Los datos de una BD, son (i)hechos con un significado que
(ii)permiten registrarse.
3
¿Qué propiedades tienen las BDs?
Las BDs representan algún aspecto del mundo real que se suele
denominar universo del discurso.
Están formadas por un conjunto de datos lógicamente coherentes y con
cierto orden (no es una colección aleatoria de datos).
Normalmente tienen un propósito específico:
– Cuando una BD se construye y rellena es siempre con un propósito
específico.
– Va dirigida a un grupo de usuarios y la BD dispone de una serie de
aplicaciones que usaran esos usuarios
Por ejemplo IMDB es una BD dirigida a personas interesadas en el cine
que tiene una serie de aplicaciones que te permite buscar información
relacionada (al menos para los usuarios finales).
4
¿Qué es un SGBD?
Que un sistema de gestión de BD.
En definitiva es el conjunto de programas que permiten a los usuarios
mantener y crear una BD (ya sea a nivel de usuario final o como
administrador)
Un SGBD facilita la definición, construcción y manipulación de una BD.
Definir es especificar ( la definición de una BD se almacena en forma de
catálogo o diccionario de la BD, son los metadatos). Se pueden definir:
– Tipos de datos
– Estructuras de datos
– Restricciones de datos
La construcción consiste en guardar los datos en un medio de
almacenamiento.
5
¿Qué es un SGBD?
La manipulación consiste en:
– Consultar la BD para obtener información o datos.
– Actualizar la BD para reflejar cambios de la misma.
– Generar informes (un informe en una BD es un documento de texto
generalmente que muestra los datos de una BD del modo que se especifique).
Otras funciones no menos importantes de un SGBD son todos protocolos
que se realizan en una BD para ser protegida:
– Respecto al mal funcionamiento de HW/SW.
– Respecto a la seguridad y privacidad de los propios datos que forman la BD.
6
¿Qué es un sistema de Bases de Datos?
Sistema de Bases de Datos =
BD
+
SGBD (o DBMS en inglés)
7
Entorno simplificado de un sistema
de base de datos
Software
DBMS Software para procesar
Consultas/Programas
Software para acceder a
Los datos almacenados
Definición de la
base de datos
Almacenada
(metadatos)
Base de datos
almacenada
(metadatos)
Programas de Aplicación/Consultas
Sistema de Bases
de Datos
Usuarios/Programadores
8
Un primer ejemplo
Base de datos que almacena la información de estudiantes y cursos
Tipos de datos: estudiantes, identificadores de estudiantes, cursos,
profesores, calificaciones,…
Estructuras:
– Los estudiantes tienen un número, nombre, departamento,…
– Los cursos tienen nombre del curso, número, horas, …
– Los informes de calificaciones tienen número de estudiante, curso, nota, …
– Relaciones: muy variadas
– Los estudiantes tienen un número asociado, clase, especialidad, los cursos
tienen título del mismo, identificador del curso, …
Funcionalidades:
– Buscar numero de matriculados en un curso, expedientes de estudiantes,
informes de calificaciones,…
– Ver / añadir estudiantes, cursos, …
9
BD que almacena estudiantes y cursos
Nombre NumEstudiante Clase Especialidad
Luis 17 1 CS
Carlos 8 2 CS
ESTUDIANTE
NombreCurso NumeroCurso Horas Departamento
Introducción a la computación CC1310 4 CC
Estructura de datos CC3320 4 CC
Matemática discreta MAT2410 3 MAT
Bases de datos CC3380 3 CC
CURSO
IDSeccion NumCurso Semestre Año Profesor
85 MAT2410 Otoño 04 Pedro
92 CC1310 Otoño 04 Ana
102 CC3320 Primavera 05 Elisa
112 MAT2410 Otoño 05 Antonio
119 CC1310 Otoño 05 Juan
135 CC3380 Otoño 05 Enrique
SECCION
10
BD que almacena estudiantes y cursos
NumEstudiante IDSeccion Nota
17 112 B
17 119 C
8 85 A
8 92 A
8 102 B
8 135 A
INFORME_CALIF
NumCurso NumPrerrequisitos
CC3380 CC3320
CC3380 MAT2410
CC3320 CC1310
PRERREQUISITO
11
Un primer ejemplo: construcción
Observar la relación entre los registros: por ejemplo Luis en estudiantes
esta relacionado con dos registros
en informe de calificaciones.
Fases para la creación del sistema de BD:
– Definición de requisitos y análisis: se documentan y transforman en un
diseño conceptual. El diseño conceptual se puede representar y manipular
mediante herramientas computarizadas. Así la implementación de la BD se
puede mantener, modificar y transformar fácilmente (por ejemplo el más
sencillo es modelo E-R).
– El diseño conceptual después se convierte en un diseño lógico que se puede
expresar en un modelo de datos implementado en el un SGBD comercial. El
más conocido y que más se utiliza es el modelo relacional que esta basado
en relaciones entre datos como su nombre indica.
– Finalmente está el modelo físico donde se proporcionan especificaciones
simplemente para el almacenamiento y acceso de la BD (nivel de
implementación).
12
Diferencias entre ficheros y una BD
Un sistema de ficheros como tal permite el acceso a los datos que se
encuentran en los ficheros. Para ser una BD el sistema necesita algo más:
– Un modelo de datos físico: La disposición de los datos en el disco que
permita la indexación y acceso rápido a todos los datos.
– Un modelo de datos lógico: nos permite el acceso a datos a nivel alto,
preocupándonos de cómo los datos tienen que estar organizados para
describir el dominio que estamos modelando, y sin preocuparnos de cómo se
organizan los datos a nivel físico.
– Lenguaje de consulta: lenguaje de alto nivel que nos permite hacer consultas
de los datos representados mediante el modelo lógico. Generalmente es un
lenguaje declarativo y no procedural. Este lenguaje utiliza de manera
automática los algoritmos óptimos y oportunos para acceder a los datos del
modelo físico.
13
Desacople del modelo lógico y físico
Un hecho muy importante que produjo el desarrollo de las BDs es que las
características del modelo lógico y físico se desacoplaran y se
comunicaran por un lenguaje de consulta.
Se dio por primera vez en 1970 con la introducción del modelo relacional
de Cood (A Relational Model of Data for Large Shared Data Banks", in
Communications of the ACM, 1970) .
Esta separación es muy importante (divide y vencerás), ya que el
problema de acceso a datos se divide en:
– Estudio de algoritmos eficientes para almacenamiento de datos en disco y su
acceso optimizado (modelo físico).
– Y el diseño de las aplicaciones se centra en generar un buen modelo de datos
a alto nivel (modelo lógico).
Esta separación de actividades asegura el gran éxito de las BDs en todos
los campos.
http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
14
Características de la metodología de BD vs
ficheros
En programación con ficheros tradicional cada usuario define e
implementa los archivos necesarios para una aplicación concreta:
– Por ejemplo un usuario de la oficina de modificación de notas y un usuario
de la oficina de contabilidad utilizan datos comunes, pero cada uno utiliza los
que tiene en ese momento.
– Así los datos de las dos oficinas están relacionados y repetidos en muchos
casos.
– Además ambas oficinas deben mantener datos comunes y se tienen que pasar
información.
En una BD esto no pasa, los datos están centralizados, hay un único
almacén de datos.
15
Características de las BDs vs ficheros
Naturaleza autodescriptiva del sistema de BD:
– Se almacena en el catálogo del SGBD (metadatos)
– El SW de SGBD puede acceder a diferentes BD’s solo mirando los catálogos.
– Así un ejemplo ilustrativo de una parte del catálogo para la BD estudiantes
está en la siguiente figura.
Aislamiento entre programas, datos y abstracción de los datos:
– Generalmente en el enfoque de ficheros si se introducen cambios en un
fichero cambia el programa que accede a los mismos (ver figura con registros
de estudiantes).
– En una BD si queremos un nuevo campo (ej. fecha de nacimiento del
estudiante), lo añadimos, se añade al catálogo, actualizamos la BD y todos los
programas de acceso a datos siguen funcionando.
16
Ejemplo de catalogo de la BD
NombreRelacion NumDeColumnas
ESTUDIANTE 4
CURSO 4
SECCIÓN 5
INFORME_CALIF 3
PRERREQUISITO 2
Relaciones
17
Ejemplo de catalogo de la BD
Columnas
NombreColumna TipoDatos PerteneceARelacion
Nombre Carácter (30) ESTUDIANTE
NumEstudiante Carácter (4) ESTUDIANTE
Clase Entero (1) ESTUDIANTE
Especialidad TipoEspecialidad ESTUDIANTE
NombreCurso Carácter (10) CURSO
NumCurso XXXXNNNN CURSO
*********************** ***************** *****************
*********************** ***************** *****************
*********************** ***************** *****************
*********************** ***************** *****************
NumPrerrequisitos XXXXNNNN PRERREQUISITO
18
Ejemplo de almacenamiento de un registro
de ESTUDIANTE basado en anterior
catalogo de BD
Nombre del elemento de datos Posición inicial en el registro Longitud en caracteres (bytes)
Nombre 1 30
NumEstudiante 31 4
Clase 35 1
Especialidad 36 4
19
Características de las BDs vs ficheros
Soporte de varias vistas de datos:
– Cada usuario puede necesitar una perspectiva de la BD.
– Una vista es un subconjunto de la BD, o puede contener datos virtuales
derivados de BD que no están explícitamente almacenados. Todo esto es
transparente al usuario.
– Así un ejemplo ilustrativo de una parte del catálogo para la BD estudiantes
está en la siguiente figura.
Compartición de datos y procesamiento de transacciones multiusuario.
– Las BDs son concurrentes y tienen control de concurrencia: que varios
usuarios que actualicen los mismos datos lo hagan de manera controlada (ej,
reserva de asientos de vuelo).
20
Soporte de varias vistas de la BD
NombreEstudiante
CertificadosEstudiante
NumCurso Nota Semestre Año IDSeccion
Luis
CC1310 C Otoño 05 119
MAT2410 B Otoño 05 112
Carlos
MAT2410 A Otoño 04 85
CC1310 A Otoño 04 92
CC3320 B Privamera 05 102
CC3380 A Otoño 05 135
Certificado
NombreCurso NumCurso Prerrequisitos
Base de
Datos CC3380
CC3320
MAR2410
Estructura de Datos CC3320 CC1310
Prerrequisito_curso
21
Roles en el uso de una base de datos
Usuarios finales
– Interactúan con aplicaciones que acceden a la BD
Usuarios avanzados
– Interactúan con la BD en SQL
Programadores de aplicación
– Interactúan con la BD escribiendo programas
Diseñadores
– Definen el diseño de la BD
Administradores
– Mantienen el diseño de la BD
– Gestionan usuarios y permisos de acceso
– Gestionan necesidades de actualización
Desarrolladores de herramientas SGBD
– Implementan la capa inferior de acceso físico a los datos
– Desarrollan el software y herramientas que dan servicio a todo lo anterior
22
Problemas de los sistemas de ficheros
Redundancia e inconsistencia de datos: la misma información puede estar
duplicada en diferentes archivos como hemos visto antes.
Dificultad en el acceso de los datos:
– Hay que escribir un programa de acceso para cada consulta.
– Los programas son difíciles de escribir ya que la información esta en varios
ficheros y si se añaden nuevos campos hay que cambiar el programa.
Problemas con la integridad: las restricciones de integridad son propiedades
que deben satisfacer los datos y si estos están distribuidos en varios ficheros se
puede violar de manera más fácil.
Problemas de atomicidad: ciertos conjuntos de operaciones tienen que ser
atómicas (ocurrir completas o no ocurrir). Esto es difícil de asegurar con
archivos.
Anomalías en acceso concurrentes: Múltiples usuarios a los mismos datos →
inconsistencia.
Problemas de seguridad: Es difícil asegurar que solo accedan unos
determinados usuarios a los ficheros.
2-sql-EDAT-121.pdf
SQL
(Cap 8 - Elmasri 5ª edición)
2
Structured
Query Language – SQL
Lenguaje de “programación” para SGBDs (DBMSs)
– DDL: Data definition language: creación del modelo de datos (diseño de tablas)
– DML: Data manipulation language: inserción, modificación, eliminación de datos
– DQL: Data query language: consultas
El SQL se puede ejecutar sobre un SGBD (DBMS).
El SQL facilita la migración entre SGBDs (DBMSs) y por eso su éxito
comercial.
Así proporciona un “interfaz” común entre los diferentes SGDBs (DBMSs).
Algebra relacional: conjunto de operaciones que describen paso a paso como
computar una respuesta sobre las relaciones en una BD (modelo relacional).
Cálculo relacional: es un lenguaje de consulta, sobre relaciones, describiendo
la respuesta deseada sobre una BD (no se especifica como obtenerla).
3
Structured Query Language – SQL
El SQL proporciona una interfaz de lenguaje declarativo (especifica lo que
debe ser el resultado) de más alto nivel que puramente una consulta en algebra
relacional.
Dejando así al SGBD (DBMS) las decisiones de optimización y de cómo se
debe realizar la consulta.
Aunque el SQL incluye algunas características de algebra relacional, está muy
basado en el cálculo relacional de tuplas.
¿Por qué no se utiliza cálculo?
La ventaja del SQL es que la sintaxis es mucho más amigable.
Leer el Capítulo 8 del libro:
– Fundamentos de sistemas de bases de datos. Ramez Elmasri, Shamkant Navathe. Pearson
Addison Wesley, 2007. INF/681.31.65/ELM.
4
Structured Query Language – SQL
El estándar más utilizado
– Creado en 1974 (D. D. Chamberlin & R. F. Boyce, IBM)
– ANSI en 1986, ISO en 1987
– Core (todos los SGBD) + packages (modulos opcionales)
Versiones
– SQL1 – SQL 86
– SQL2 – SQL 92, SQL 99
– SQL 3 – no plenamente soportado por la industria
Limitaciones
– No es puramente relacional (p.e. las vistas son multiconjuntos de tuplas)
– Importantes divergencias entre implementaciones (no es directamente portable en
general, incompletitudes, extensiones) –uno termina aprendiendo variantes de
SQL
5
Structured Query Language – SQL
Algunos SGBDs libres:
– PostgreSQL (http://www.postgresql.org Postgresql) Licencia BSD
– SQLite (http://www.sqlite.org SQLite) Licencia Dominio Público
– DB2 Express-C (http://www.ibm.com/software/data/db2/express/)
– Apache Derby (http://db.apache.org/derby/)
– MySQL (http://dev.mysql.com/)
– …….
Algunos SGBDs no libres:
– MySQL: Licencia Dual, depende del uso
– dBase
– Fox Pro
– IBM DB2: Universal Database (DB2 UDB)
– Microsoft SQL Server
– Oracle
– …….
Algunos SGBDs no libres y gratuitos:
– Microsoft SQL Server Compact Edition Basica
– Oracle Express Edition 10 (solo corre en un servidor, capacidad limitada)
– …….
http://es.wikipedia.org/wiki/PostgreSQL
http://www.postgresql.org/
http://es.wikipedia.org/wiki/SQLite
http://www.sqlite.org/
http://es.wikipedia.org/wiki/DB2_Express-C
http://www.ibm.com/software/data/db2/express/
http://es.wikipedia.org/wiki/Apache_Derby
http://db.apache.org/derby/
http://es.wikipedia.org/wiki/MySQL
http://dev.mysql.com/
http://es.wikipedia.org/wiki/MySQL
http://es.wikipedia.org/wiki/DBase
http://es.wikipedia.org/wiki/Fox_Pro
http://es.wikipedia.org/wiki/IBM
http://es.wikipedia.org/wiki/DB2
http://es.wikipedia.org/wiki/Microsoft_SQL_Server
http://es.wikipedia.org/wiki/Oracle
http://es.wikipedia.org/wiki/SQL_Server_Compact
http://es.wikipedia.org/w/index.php?title=Oracle_Express_Edition_10&action=edit&redlink=1
6
Elementos fundamentales de una base
de datos SQL
Base de datos = conjunto de tablas RELACIONADAS
Tabla (relación, entidad, esquema…) =
– Estructura fija de campos (esquema)
– Conjunto de registros con valores de campos
Campo (atributo, propiedad, “columna”), tiene un tipo de dato
Registro (tupla, “fila”)
Clave primaria
Clave secundaria
Clave externa
7
Diagramas típicos para una BD (modelo E/R)
EMPLEADO
Dirección
DNI
1
CONTROLA
PROYECTO
DEPARTAMENTO
Nombre Número
Ubicaciones
1
N
Ubicación
Número
Nombre
TRABAJA_EN
NM
Horas
TRABAJA_
PARA
ADMINISTRA
11
N
NumEmpleados
Fechainicio
FechaNac Nombre Sueldo
NombreP Apellido1 Apellido2
Sexo
CONTROL
1 N
Supervisor
Supervisado
SUBORDINA
DOS_DE
SUBORDINADO
N
1
Nombre Sexo FechaNac Relación
8
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
9
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
10
Estructura léxica del lenguaje
Operaciones SQL
DDL – Creación, diseño, eliminación de tablas
DML – Inserción, modificación, eliminación de registros
DQL – Consulta
Estructura léxica de SQL
Case-insensitive, insignificant whitespace
Sentencias, expresiones, valores, tipos de datos
Referencias
– Elmasri cap. 8
– PostgreSQL SQL ref: https://www.postgresql.org/docs/10/static/index.html
11
Esquemas y Catálogos en SQL
Versiones antiguas de SQL no incluían estos conceptos
CREATE SCHEMA nombre AUTHORIZATION propietarios
Definiciones de Dominios
Definiciones de Tablas
Definiciones de Vistas
……………………….
CATÁLOGO = conjunto de esquemas bajo un nombre
– Siempre incluye un esquema (INFORMATION_SCHEMA) que contiene
información sobre todos los elementos del catálogo
–Restricciones de Integridad: solo se pueden definir entre esquemas del mismo
catálogo
–El estándar no proporciona mecanismos para definir y eliminar catálogos,
depende de la implementación
12
Literales, Expresiones y Operadores
Valores literales
Cadenas de caracteres entre '...'
Valores numéricos similar p.e. al lenguaje C
Expresiones
Se pueden utilizar en WHERE, SELECT, SET, DEFAULT, CHECK…
Operadores Comentarios
+ – * / % ^ – –
AND OR NOT /* … */
= < > <= >= LIKE ISNULL
operaciones con strings: concatenación, like, expresiones regulares (‘%’
‘_’)
13
Tipos de Datos y Dominios en SQL
Numéricos:
–Entero de distintos tamaños (INTERGER o INT, SMALLINT)
–Reales de distinta precisión (FLOAT o REAL, DOUBLE PRECISION)
–Números con formato (NUMERIC(t,d), DECIMAL(t,d), d menor t, d=0 por
defecto, t=n dig, d= n dig a la derecha del punto)
Cadenas de caracteres:
–CHAR (n) – longitud fija con relleno a blancos
–CHAR VARYING (n) - longitud variable con límite
–TEXT - longitud variable sin límite
Cadenas de bits:
–BIT(n)
–BIT VARYING (n)
14
Tipos de Datos y Dominios en SQL
Fecha y Hora:
–DATE (10 posiciones), YYYY-MM-DD
–TIME (8 posiciones), HH:MM:MM
–TIME (i), carácter separador más i posiciones para fracciones de segundo
–WITH TIME ZONE, 6 posiciones extra para el desplazamiento respecto al uso
horario estándar universal, +-HH.MM
–TIMESTAMP YYYY-MM-DD HH.MM.SS.fracciones de segundo (6
posiciones), es opcional el calificador WITH TIME ZONE
–INTERVAL, periodo de tiempo (están cualificado para ser de dos tipos de
intervalos generalmente)
•AÑO/MES
•DIA/HORA
15
Tipos de Datos y Dominios en SQL
Dominios: Alternativamente a especificar los datos directamente se pueden
crear dominios.
Ejemplo: CREATE DOMAIN Tipo_Dni AS CHAR(9)
CREATE DOMAIN nombre [AS] tipo_datos
[Definición_por_defecto]
[Restricciones]
Definición_por_defecto
DEFAULT {literal|función|NULL}
Restricciones
[CONSTRAINT nombre ]
CHECK (expresión_condicional)
16
Tipos de Datos y Dominios en SQL
Ejemplos:
CREATE DOMAIN CIUDADES CHAR(15)
DEFAULT ´Madrid´
CONSTRAINT MirestricciondeCiudades
CHECK (VALUE IN(‘Atenas’, ‘Dublin’,……., ‘Madrid’))
CREATE DOMAIN NumEmp NUMERIC(4)
DEFAULT 0
CHECK (VALUE IN NOT NULL)
17
TABLAS
CREATE TABLE nombre (
campo1 tipo1 [restricciones1],
campo2 tipo2 [restricciones2],
…,
[restricciones ]
);
ALTER TABLE nombre ADD COLUMN
campo tipo [restricciones];
ALTER TABLE nombre ADD restricción;
ALTER TABLE nombre DROP COLUMN campo;
DROP TABLE nombre;
DROP CONSTRAINT nombre-restricción;
18
TABLAS
Las tablas pueden tener más opciones, como indicadores de comportamiento
en ciertas acciones, o relaciones con otras tablas.
Las restricciones de la tabla pueden ser de diferentes tipos:
–PRIMARY KEY (lista de columnas) (NO permite valores nulos, NULL)
–UNIQUE (lista de columnas) (permite valores nulos, NULL)
–FOREGIN KEY (lista de columnas) REFERENCES Tabla(columnas) (Indica que esa lista
de columnas son clave primaria de otra tabla)
Las restricciones referenciales de la tabla pueden ir con en el borrado o
actualización
– ON DELETE
•NO ACTION
•SET DEFAULT
•SET NULL
•CASCADE
– ON UPDATE
•NO ACTION
•SET DEFAULT
•SET NULL
•CASCADE
19
Crear Tablas: Ejemplos
20
CREATE TABLE Artista (
id int PRIMARY KEY,
nombre text NOT NULL,
nacionalidad text
);
CREATE TABLE Cancion (
id int PRIMARY KEY,
titulo text NOT NULL,
genero text,
duracion int,
fecha date,
autor int NOT NULL REFERENCES Artista (id)
);
Crear Tablas: Más Ejemplos
21
CREATE TABLE Usuario (
nick varchar(30) PRIMARY KEY,
nombre text NOT NULL,
email text NOT NULL UNIQUE
);
CREATE TABLE Contacto (
usuario1 varchar(30) REFERENCES Usuario (nick),
usuario2 varchar(30) REFERENCES Usuario (nick),
PRIMARY KEY (usuario1,usuario2)
);
Crear Tablas: Ejemplos
22
CREATE TABLE Escucha (
usuario varchar(30) ,
cancion int REFERENCES Cancion (id),
PRIMARY KEY (usuario,cancion,instante)
);
ALTER TABLE Escucha ADD instante timestamp; /* NULL’s */
ALTER TABLE Escucha DROP COLUMN instante;
ALTER TABLE Escucha ADD FOREIGN KEY (usuario)
REFERENCES Usuario (nick);
ALTER TABLE Usuario ADD PRIMARY KEY (nick);
Crear Tablas: Ejemplos
23
Especificación de Restricciones en SQL
En un campo
NOT NULL
UNIQUE
PRIMARY KEY
REFERENCES tabla (clave) [(ON DELETE | ON UPDATE)
(SET NULL | CASCADE | SET DEFAULT)]
DEFAULT valor
En una tabla
PRIMARY KEY (campo1, campo2, …)
FOREIGN KEY (campo1, campo2, …)
REFERENCES tabla (clave1, clave2, …)
UNIQUE (campo1, campo2, …)
CHECK (expresión)
Con nombre
CONSTRAINT nombre restricción
24
Especificación de Restricciones de
Atributo y Valores Predeterminados
Como SQL permite atributos NULL, se puede especificar un atributo con NOT
NULL.
También se puede utilizar una clausula DEFAULT <valor> que se incluye en
cada tupla si no se especifica ningún valor.
También se puede restringir los valores de un atributo o dominio con la clausula
CHECK
– NumeroDpto INT NOT NULL CHECK (NumeroDpto >0 AND NumeroDpto <
21)
– CREATE DOMAIN NUM_D AS INTEGER CHECK (NUM_D > 0 AND
NUM_D < 21)
25
Estas restricciones son muy importantes y clausulas especiales:
–PRIMARY KEY (lista de columnas) (restricción de clave)
–UNIQUE (lista de columnas) (restricción de clave)
–FOREIGN KEY (lista de columnas) REFERENCES Tabla(columnas) (restricción de
integridad referencial)
Una integridad referencial se puede violar por la inserción o eliminación de
tuplas de atributos de FOREIGN KEY o la clave principal.
La acción por defecto si se viola la restricción referencial es rechazar la
operación (NO ACTION), pero se pueden poner más opciones:
•SET DEFAULT
•SET NULL
•CASCADE
Estos son los valores que se cambian en la tupla que se referencian
Estas opciones deben cualificarse:
•ON DELETE
•ON UPDATE
Especificación de Restricciones de
Clave y Integridad Referencial
26
Ejemplos
– ON DELETE CASCADE: elimina las tuplas referenciadas en cascada cuando
eliminamos algo de la clave externa.
– ON UPDATE CASCADE: Actualiza la clave externa por la que se ha
cambiado, en cascada.
NOTA: Borrar solo puede violar la integridad referencial siempre que la
tupla a eliminar esté referenciada por claves externas de otras tuplas.
Ejemplo siguiente: si se elimina la tupla de un empleado supervisor, el valor
de SuperDni queda a NULL automáticamente en todas la tuplas de empleado
que hacían referencia a la tupla de empleado borrada.
Por el contrario si se actualiza el valor Dni de un empleado supervisor (se
introdujo incorrectamente), entonces el valor nuevo se actualiza en cascada
para el SuperDni de todas la tuplas de empleado que hacen referencia a la
tupla actualizada.
Ver la siguiente transparencia.
Especificación de Restricciones de
Clave y Integridad Referencial
27
Crear Tablas con Restricciones
28
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
29
Sentencias de SQL para cambiar el
Esquema
Hay comandos para evolucionar el esquema (alterar un esquema) de una BD en
SQL
Añadir o eliminar tablas, atributos, restricciones, etc….
DROP SCHEMA empresa CASCADE; Se elimina todo el esquema con todos
los elementos (…. empresa RESTRICT solo se elimina si no contiene elementos)
DROP TABLE subordinado CASCADE; (se elimina la relación y su
definición, RESTRICT solo se elimina la tabla si no hace referencia a otra tabla)
DELETE FROM tabla [WHERE …]; Para eliminar tuplas (DELETE FROM
empleado WHERE ‘dni=123456789’; DELETE FROM EMPLEADO WHERE
Apellido1=‘Cabrera’;)
ALTER TABLA EMPRESA.EMPLEADO ADD COLUMN Trabajo
VARCHAR (12); (no se introduce un valor para la nueva columna, se pone toda la
nueva columna a NULL)
ALTER TABLA EMPRESA.EMPLEADO DROP COLUMN dirección
CASCADE; (RESTRICT solo se elimina la columna si no hay vistas o
restricciones que hagan referencia a esta columna)
30
Sentencias de SQL para cambiar el
Esquema
ALTER TABLA EMPRESA.DEPARTAMENTO ALTER COLUMN
DniDirector DROP DEFAULT (se elimina la clausula predeterminada para
DniDirector)
ALTER TABLA EMPRESA.DEPARTAMENTO ALTER COLUMN
DniDirector SET DEFAULT ‘333445555’(se define la clausula predeterminada
para DniDirector)
ALTER TABLA EMPRESA.EMPLEADO DROP CONSTRAINT
SUPERFKEMP CASCADE; (se elimina la restricción SUPERFKEMP de la
relación EMPLEADO)
INSERT INTO EMPLEADO VALUES (‘Ricardo’,
Roca’,’Flores’,’653298653’,’30-12-1962’,’Los Jarales, 47’,’H’,’37000’,
‘653298653’,4); (añade una nueva tupla, al menos hay que añadir los que están
explícitamente en la definición de tabla a NOT NULL );
INSERT INTO EMPLEADO (Nombre , Apellido1, Dno, Dni) VALUES
(‘Ricardo’, ‘Roca’,’4’,’653298653’); (añade parte de una nueva tupla, los no
especificados se establecen a DEFAULT o a NULL);
31
Sentencias de SQL para cambiar el
Esquema
INSERT INTO EMPLEADO (Nombre , Apellido1, Dno, Dni) VALUES (‘Ricardo’,
‘Roca’,’2’,’653298653’); (Si el SGDB realiza la integridad referencial, se rechaza el
comando); ¿Por qué?
INSERT INTO EMPLEADO (Nombre , Apellido1, Dno) VALUES (‘Ricardo’,
‘Roca’,’4’); (Si el SGDB realiza la comprobación NOT NULL de ‘Dni’ no
proporcionada y se rechaza el comando);
UPDATE PROYECTO SET UbicaciónProyecto=‘Valencia’, NumDptoProyecto = 5
WHERE NumProyecto=10;
UPDATE Empleado SET Sueldo=Sueldo*1.1
WHERE Dno IN (SELECT NumeroDpto FROM DEPARTAMENTO
WHERE NombreDpto=‘Investigación’);
IN vamos a utilizarlo en consultas anidadas también.
TRUNCATE tabla; (quita todas las filas de una tabla, pero permanecen la
estructura y sus columnas, las restricciones, los índices, etc. Para quitar la
definición de tabla además de los datos: DROP TABLE)
32
Transacciones en SQL: BEGIN, COMMIT
Todo SGDB maneja transacciones: Conjunto de acciones sobre
una BD que altera los datos (INSERT INTO, UPDATE,
DELETE, etc.) pero que deben ser realizados de manera atómica
(para evitar concurrencia de usuarios, por ejemplo).
BEGIN, COMMIT, ROLLBACK, SAVEPOINT,
ROLLBACK TO, RELEASE SAVEPOINT.
La documentación la podéis encontrar
en el manual de
POSTGRESQL:
– http://www.postgresql.org/docs/10/static/sql-begin.html
33
Transacciones en SQL: BEGIN, COMMIT
Un ejemplo:
BEGIN;
Secuencia de comandos que alteran una BD determinada
COMMIT; (este comando finaliza las transacciones haciendo los
cambios permanentes y visibles a todos los
usuarios)
BEGIN inicia un bloque de transacción, es decir, todas las
declaraciones después de un comando BEGIN se ejecutarán en
una sola transacción hasta que de manera explícita haya un
COMMIT (plasma todos los cambios en la BD) o un
ROLLBACK (deshace todos los cambios).
34
Transacciones en SQL: ROLLBACK
La transacción puede dar un error entre medias, para ello esta el
comando ROLLBACK:
BEGIN;
Secuencia de comandos que alteran una BD determinada
y en un punto determinado se genera un error
ROLLBACK; (este comando vuelve al BEGIN deshace todos
los cambios)
Empiezas a hacer de nuevo los cambios, excepto el que te
daba error.
COMMIT;
35
Transacciones en SQL: SAVEPOINT
Con el comando SAVEPOINT, también puedes grabar puntos
interesantes intermedios, por si se produce un error inesperado y
no tengas que volver al principio y así aproveches algunos
cambios realizados:
BEGIN;
Secuencia de comandos que alteran una BD
SAVEPOINT misavepoint
Secuencia de comandos que alteran una BD
y en un punto determinado se genera un error
ROLLBACK TO misavepoint;
Secuencia nueva de comandos que alteran una BD
COMMIT;
36
Transacciones en SQL: RELEASE SAVEPOINT
Sirve para indicar que la aplicación ya no desea mantener el punto
de salvaguarda especificado. Después de invocar esta sentencia,
ya no es posible hacer una retrotracción hasta el punto de
salvaguarda.
BEGIN;
Secuencia de comandos que alteran una BD
SAVEPOINT misavepoint 1
Secuencia de comandos que alteran una BD
SAVEPOINT misavepoint 2
Secuencia de comandos que alteran una BD
RELEASE SAVEPOINT misavepoint2;
COMMIT;
37
Transacciones en SQL: ejemplos
Para establecer un punto de salvaguarda y luego deshacer los
efectos de todos los comandos ejecutados después de su creación,
la transacción insertará los valores 1 y 3, pero no 2.:
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
COMMIT;
38
Transacciones en SQL: ejemplos
Establecer y posteriormente destruir un punto de salvaguarda, la
transacción insertará tanto 3 y 4:
BEGIN;
INSERT INTO table1 VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (4);
RELEASE SAVEPOINT my_savepoint;
COMMIT;
39
Más Ejemplos
INSERT INTO Artista VALUES (1, 'The Beatles', 'UK');
INSERT INTO Artista VALUES (2, 'The Rolling Stones', 'UK');
INSERT INTO Artista (id, nombre) VALUES (3, 'David Bowie');
INSERT INTO Cancion
VALUES (1, 'Norwegian wood', 'Pop', '125', '1965-03-12', 1);
INSERT INTO Cancion
VALUES (2, 'Here, there and everywhere', 'Pop', '145', '1966-08-05', 1);
INSERT INTO Cancion
VALUES (3, 'Jumping jack flash', 'Pop', '225', '1968-04-20', 2);
INSERT INTO Usuario VALUES ('lola', 'Dolores', 'lola@gmail.com');
INSERT INTO Usuario VALUES ('pepe', 'José', 'jose@gmail.com');
INSERT INTO Usuario VALUES ('chema', 'José María', 'chema@gmail.com');
INSERT INTO Usuario VALUES ('charo', 'Rosario', 'rosario@gmail.com');
40
INSERT INTO Contacto VALUES
('pepe', 'lola'),
('charo', 'pepe'),
('chema', 'charo');
INSERT INTO Escucha VALUES ('charo', 2, '2011-09-09 16:57:54');
INSERT INTO Escucha VALUES ('pepe', 3, '2011-09-12 21:15:30');
UPDATE Artista SET nacionalidad = 'UK' WHERE nombre = 'David Bowie';
UPDATE Album SET precio = precio * 1.2;
DELETE FROM Escucha WHERE instante < '2000-01-01 00:00:00';
Más Ejemplos
41
Consultas en SQL
SELECT [DISTINCT] campos FROM tablas
[WHERE condición];
Consulta A
SELECT FechaNac, Dirección FROM EMPLEADO
WHERE Nombre=‘Jose’ AND Apellido1=‘Pérez’ AND Apellido2=‘Pérez’;
Otras consultas:
SELECT titulo, genero FROM Cancion
WHERE fecha < '1967-01-01';
SELECT DISTINCT nacionalidad FROM Artista; (solo las tuplas diferentes
permanecen en el resultado, en contraposición a SELECT ALL)
SELECT * FROM Cancion, Artista
WHERE Cancion.autor = Artista.id AND Artista.nacionalidad = 'UK';
SELECT dni, teoria * 0.6 + practicas * 0.4 FROM Notas;
42
Consultas en SQL sencillas
Ejemplo 1
SELECT "FechaNac", "Direccion"
FROM "EMPLEADO"
WHERE "Nombre"='Jose' AND "Apellido1"='Perez' AND "Apellido2"='Perez';
Ejemplo 2
SELECT *
FROM "EMPLEADO"
WHERE "Nombre"='Jose' AND "Apellido1"='Perez' AND "Apellido2"='Perez';
Ejemplo 3
SELECT *
FROM "EMPLEADO"
WHERE "Sexo"='M';
43
Consultas en SQL sencillas
Ejemplo 4
SELECT "Sexo"
FROM "EMPLEADO";
Ejemplo 5
SELECT DISTINCT "Sexo"
FROM "EMPLEADO";
Ejemplo 6
SELECT "Nombre", "Apellido1", "Direccion"
FROM "EMPLEADO", "DEPARTAMENTO"
WHERE "NombreDpto"='Investigacion' AND "Dno"="NumeroDpto";
Ejemplo 7
SELECT "EMPLEADO"."Nombre", "EMPLEADO"."Apellido1", "EMPLEADO"."Direccion"
FROM "EMPLEADO", "DEPARTAMENTO"
WHERE "EMPLEADO"."Dno"="DEPARTAMENTO"."NumeroDpto" AND
"DEPARTAMENTO"."NombreDpto"='Investigacion';
44
Consultas en SQL (INNER JOIN)
Consulta B
SELECT Nombre, Apellido1, Dirección
FROM EMPLEADO, DEPARTAMENTO (o de manera explicita FROM
EMPLEADO INNER JOIN DEPARTAMENTO ON NumeroDpto =Dno )
WHERE NombreDpto=‘Investigación’ AND NumeroDpto =Dno;
(selección-proyección-concatenación, Álgebra relacional)
Consulta C
SELECT NumProyecto, NumDptoProyecto, Apellido1, Dirección,
FechaNac
FROM PROYECTO, DEPARTAMENTO, EMPLEADO
WHERE NumDptoProyecto =NumeroDpto AND DniDirector=Dni AND
UbicacionProyecto=‘Gijon’;
(selección-proyección-2 condiciones de concatenación, Álgebra relacional)
45
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
46
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
47
Concatenación de Tablas, Join
SELECT campos
FROM tabla1 JOIN tabla2 ON condición
[WHERE condición];
(JOIN: Concepto de tabla concatenada o relación concatenada)
Consulta B
SELECT Nombre, Apellido1, Dirección
FROM (EMPLEADO JOIN DEPARTAMENTO ON Dno= NumeroDpto)
WHERE NombreDpto=‘Investigación’;
Uso típico (pero no sólo) con claves externas: ON externa = primaria
SELECT titulo FROM Cancion, Escucha WHERE usuario='lola';
SELECT titulo FROM (Cancion JOIN Escucha ON cancion = id);
SELECT * FROM (Contacto JOIN Usuario ON (usuario1 = nick OR
usuario2 = nick)) WHERE nombre = 'Rosario';
(ver tablas siguientes)
48
CREATE TABLE Usuario (
nick varchar(30) PRIMARY KEY,
nombre text NOT NULL,
email text NOT NULL UNIQUE
);
CREATE TABLE Contacto (
usuario1 varchar(30) REFERENCES Usuario (nick),
usuario2 varchar(30) REFERENCES Usuario (nick),
PRIMARY KEY (usuario1,usuario2)
);
Tablas
49
Ejemplos de Consultas en SQL: Join
Ejemplo 6
SELECT "Nombre", "Apellido1", "Direccion"
FROM "EMPLEADO", "DEPARTAMENTO"
WHERE "NombreDpto"='Investigacion' AND "Dno"="NumeroDpto";
Ejemplo 7
SELECT "EMPLEADO"."Nombre", "EMPLEADO"."Apellido1", "EMPLEADO"."Direccion"
FROM "EMPLEADO", "DEPARTAMENTO"
WHERE "EMPLEADO"."Dno"="DEPARTAMENTO"."NumeroDpto" AND
"DEPARTAMENTO"."NombreDpto"='Investigacion';
Ejemplo 8
SELECT "Nombre", "Apellido1", "Direccion"
FROM "EMPLEADO" INNER JOIN "DEPARTAMENTO" ON
"Dno"="NumeroDpto"
WHERE "NombreDpto"='Investigacion';
50
Ejemplos de Consultas en SQL: Join
Ejemplo 9
SELECT "Nombre", "Apellido1", "Direccion"
FROM "EMPLEADO" JOIN "DEPARTAMENTO" ON "Dno"="NumeroDpto";
Ejemplo 10
SELECT *
FROM "EMPLEADO" JOIN "DEPARTAMENTO" ON "Dno"="NumeroDpto";
Ejemplo 11
SELECT *
FROM "EMPLEADO" INNER JOIN "DEPARTAMENTO" ON
"Dno"="NumeroDpto";
51
Concatenación de Tablas, Join
Consulta B
SELECT Nombre, Apellido1, Dirección
FROM (EMPLEADO NATURAL JOIN
(DEPARTAMENTO AS DEPT(NombreDpto, Dno,
DniDirector,FechaIngresoDirector)))
WHERE NombreDpto=‘Investigación’;
(Antes de hacer el NATURAL JOIN si los nombres de los atributos de
concatenación no coinciden en las relaciones base, se renombran para que
coincidan, a través de AS para renombrar la relación. En este caso en la
tabla DEPARTAMENTO NumeroDpto se renombra a Dno para el NJ)
Realmente AS se puede utilizar para definir un ALIAS, lo veremos más
adelante.
OJO: En SQL se pueden utilizar el mismo nombre para dos o más atributos
siempre que se encuentren en relaciones diferentes. Para eso están los alias o
variables de tupla, (ejemplos a continuación).
52
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO Con AS pasa a Dno
53
Nombres de Atributos, Alias y
Variables de Tupla.
Consulta D
SELECT E.Nombre, E.Apellido1, S.Nombre, S.Apellido1
FROM EMPLEADO AS E, EMPLEADO AS S
WHERE E.SuperDni=S.Dni;
Por cada empleado se recupera el nombre y primer apellido del mismo y el nombre
y primer apellido de su supervisor inmediato.
La tabla E se va a utilizar como tabla para extraer la información de los empleados
que son supervisados. La tabla S se utiliza para extraer la información de los
empleados supervisores.
Darse cuenta que las tablas E y S son copias de la tabla empleado.
Consulta D (notación reducida)
SELECT E.Nombre, E.Apellido1, S.Nombre, S.Apellido1
FROM EMPLEADO E, EMPLEADO S
WHERE E.SuperDni=S.Dni;
54
Nombres de Atributos, Alias y
Variables de Tupla.
Resultado Consulta D (notación reducida)
SELECT "E"."Nombre", "E"."Apellido1", "S"."Nombre", "S"."Apellido1"
FROM "EMPLEADO" "E", "EMPLEADO" "S"
WHERE "E"."SuperDni"="S"."Dni";
55
Tipos de join
INNER Por defecto (no hace falta ponerlo)
NATURAL La condición consiste en igualdad entre la
combinación de los campos que se llamen igual entre ambas tablas
(EQUIJOIN) (no se repiten los campos)
LEFT | RIGHT | FULL Se añaden también filas que no cumplen la
condición (incompatible con INNER)(OUTER JOIN)
– R (->< Dni=DniDirector) S (LEFT OUTER JOIN, mantiene cada tupla de la
relación izquierda aunque no se encuentre ninguna tupla en S que cumple la
conexión, esos atributos se rellenan a NULL)
– R (><- Dni=DniDirector) S (RIGHT OUTER JOIN, mantiene cada tupla de la
relación derecha aunque no se encuentre ninguna tupla en R que cumple la
conexión, esos atributos se rellenan a NULL)
(nota recordatorio: explicar en la pizarra gráficamente con conjuntos)
56
Ejemplos de Consultas en SQL: Join
Ejemplo 12
SELECT *
FROM "EMPLEADO" LEFT JOIN "DEPARTAMENTO" ON
"Dno"="NumeroDpto";
Ejemplo 13
SELECT *
FROM "EMPLEADO" LEFT JOIN "DEPARTAMENTO" ON
"Dni"="DniDirector";
Ejemplo 14
SELECT *
FROM "EMPLEADO" RIGHT JOIN "DEPARTAMENTO" ON
"Dni"="DniDirector";
57
Ejemplos de Consultas en SQL: Join
Ejemplo 12
SELECT *
FROM "EMPLEADO" LEFT JOIN "DEPARTAMENTO" ON
"Dno"="NumeroDpto";
58
Ejemplos de Consultas en SQL: Join
Ejemplo 13
SELECT *
FROM "EMPLEADO" LEFT JOIN "DEPARTAMENTO" ON
"Dni"="DniDirector";
59
Ejemplos de Consultas en SQL: Join
Ejemplo 14
SELECT *
FROM "EMPLEADO" RIGHT JOIN "DEPARTAMENTO" ON
"Dni"="DniDirector";
60
Ejemplos de Consultas en SQL: Join
Ejemplo 15
SELECT "NumProyecto", "NumDptoProyecto", "Apellido1", "Direccion",
"FechaNac"
FROM "PROYECTO", "DEPARTAMENTO", "EMPLEADO"
WHERE "NumDptoProyecto"="NumeroDpto" AND "DniDirector"="Dni" AND
"UbicacionProyecto"='Gijon';
Ejemplo 16
SELECT *
FROM "PROYECTO", "DEPARTAMENTO", "EMPLEADO"
WHERE "NumDptoProyecto"="NumeroDpto" AND "DniDirector"="Dni";
61
Ejemplos de Consultas en SQL: AS
Ejemplo 18
SELECT "Nombre", "Apellido1", "Direccion"
FROM "EMPLEADO" NATURAL JOIN "DEPARTAMENTO" AS
"DEPT"("NombreDpto","Dno","DniDirector","FechaIngresoDirector")
WHERE "NombreDpto"='Investigacion';
Ejemplo 19
SELECT *
FROM "EMPLEADO" NATURAL JOIN "DEPARTAMENTO" AS
"DEPT"("NombreDpto","Dno","DniDirector","FechaIngresoDirector");
Ejemplo 20
SELECT "E"."Nombre", "E"."Apellido1", "S"."Nombre", "S"."Apellido1"
FROM "EMPLEADO" AS "E", "EMPLEADO" AS "S"
WHERE "E"."SuperDni"="S"."Dni";
62
Más ejemplos de join
CREATE TABLE Alumno (
dni VARCHAR(12) PRIMARY KEY, nombre text);
CREATE TABLE Asignatura (
codigo NUMERIC PRIMARY KEY, nombre text);
CREATE TABLE Notas (
dni VARCHAR(12) REFERENCES Alumno(dni),
codigo NUMERIC REFERENCES Asignatura(codigo),
teoria NUMERIC (4,2), practicas NUMERIC (4,2),
PRIMARY KEY (dni, codigo));
SELECT nombre, teoria FROM Notas NATURAL JOIN Asignatura;
SELECT nombre, teoria FROM Notas JOIN Asignatura
ON Notas.codigo = Asignatura.codigo;
Recordar la notación: ON externa = primaria
63
Mas Alias
SELECT campos FROM tabla AS alias [(alias-campo1, alias-campo2, …)]
[WHERE condición];
SELECT campo AS alias FROM …
Ejemplos:
SELECT dni, teoria * 0.6 + practicas * 0.4 as media
FROM Notas;
SELECT u1.nombre
FROM Usuario AS u1, Usuario AS u2
WHERE u1.nombre = u2.nombre AND u1.nick <> u2.nick;
(ver tablas anteriores y siguientes)
64
CREATE TABLE Usuario (
nick varchar(30) PRIMARY KEY,
nombre text NOT NULL,
email text NOT NULL UNIQUE
);
CREATE TABLE Contacto (
usuario1 varchar(30) REFERENCES Usuario (nick),
usuario2 varchar(30) REFERENCES Usuario (nick),
PRIMARY KEY (usuario1,usuario2)
);
Tablas
65
Consultas anidadas
Son conexiones entre consultas a través del operador de comparación
normalmente IN.
SELECT campos FROM tabla
WHERE campo1, campo2, … IN (SELECT campo1, campo2, …);
Ejemplo: (selecciona los números de proyectos que tienen Pérez como director)
SELECT DISTINCT NumProyecto
FROM PROYECTO
WHERE NumProyecto IN
(SELECT NumProyecto
FROM PROYECTO, DEPARTAMENTO, EMPLEADO
WHERE NumDptoProyecto =NumeroDpto AND DniDirector=Dni AND
Apellido1=‘Pérez’;)
(Con la palabra clave DISTINCT eliminamos las tuplas iguales, solo
permanecen en el resultado las tuplas distintas)
66
Consultas anidadas (más opciones)
SELECT campos FROM tabla
WHERE campo comparación (SOME | ALL) (SELECT …);
SELECT campos FROM tabla
WHERE EXISTS (SELECT …);
SELECT campos FROM tabla
WHERE (SELECT …) CONTAINS (SOME | ALL) (SELECT …);
67
Consultas anidadas
Ejemplo
SELECT u2.nombre FROM Usuario AS u1, Usuario as u2
WHERE
(u1.nick, u2.nick) IN
((SELECT usuario1, usuario2 FROM Contacto)
UNION
(SELECT usuario2, usuario1 FROM Contacto))
AND u1.nombre = 'Rosario';
68
Ejemplos de Consultas anidadas
Ejemplo 26 (Características de proyectos que tienen a Campos como director)
SELECT *
FROM "PROYECTO"
WHERE "NumProyecto" IN
(SELECT "NumProyecto"
FROM "PROYECTO", "DEPARTAMENTO", "EMPLEADO"
WHERE "NumDptoProyecto"="NumeroDpto" AND "DniDirector"="Dni" AND
"Apellido1"='Campos');
69
Ejemplos de Consultas anidadas
Ejemplo 27 (Todos los empleados que NO trabajen en el proyecto 2, con la
operación resta algebraica, EXCEPT)
SELECT "EMPLEADO"."Nombre", "EMPLEADO"."Apellido1",
"EMPLEADO"."Apellido2"
FROM "EMPLEADO", "TRABAJA_EN"
WHERE "EMPLEADO"."Dni"="TRABAJA_EN"."DniEmpleado" EXCEPT
(SELECT "EMPLEADO"."Nombre", "EMPLEADO"."Apellido1",
"EMPLEADO"."Apellido2"
FROM "EMPLEADO", "TRABAJA_EN"
WHERE "EMPLEADO"."Dni"="TRABAJA_EN"."DniEmpleado" AND
"TRABAJA_EN"."NumProy"='2');
Son todos los que trabajan en el proyecto 2
70
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
71
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
72
Ejemplos de Consultas anidadas
Ejemplo 29 (Enumere el nombre de todos los empleados que trabajan en algún
proyecto controlado por el departamento 5. El segundo SELECT me proporciona
los números de proyecto que controla el departamento 5)
SELECT DISTINCT "EMPLEADO"."Nombre"
FROM "TRABAJA_EN", "EMPLEADO"
WHERE "EMPLEADO"."Dni"="TRABAJA_EN"."DniEmpleado" AND
"TRABAJA_EN"."NumProy" IN
(SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO"
WHERE "PROYECTO"."NumDptoProyecto"='5');
73
Consultas anidadas (detalle IN)
Enumere el nombre de todos los empleados que trabajan en algún proyecto
controlado por el departamento 5.
El segundo SELECT me proporciona los números de proyecto que controla el
departamento 5.
74
Consultas anidadas (detalle IN)
La palabra clave IN equivale a establecer condiciones sobre un mismo campo
conectadas por el operador OR.
75
Ejemplos de Consultas anidadas
Ejemplo 30-A (Obtener una lista de los números de los proyectos que impliquen a
cualquier empleado cuyo primer apellido sea ‘Campos’, independientemente de que
sean trabajadores o directores del departamento que gestiona dicho proyecto):
SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO"
WHERE "PROYECTO"."NumProyecto" IN
(SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO", "DEPARTAMENTO", "EMPLEADO"
WHERE "PROYECTO"."NumDptoProyecto"="DEPARTAMENTO"."NumeroDpto" AND
"DEPARTAMENTO"."DniDirector"="EMPLEADO"."Dni" AND "Apellido1"='Campos')
OR
"PROYECTO"."NumProyecto" IN
(SELECT "TRABAJA_EN"."NumProy"
FROM "TRABAJA_EN", "EMPLEADO"
WHERE "TRABAJA_EN"."DniEmpleado"="EMPLEADO"."Dni" AND
"Apellido1"='Campos');
Directores
Empleados
76
Consultas anidadas
Obtener una lista de los números de los proyectos que impliquen a cualquier
empleado cuyo primer apellido sea ‘Campos’, independientemente de que sean
trabajadores o directores del departamento que gestiona dicho proyecto:
Directores
Empleados
77
Álgebra de conjuntos
consulta1 UNION consulta2
consulta1 INTERSECT consulta2
consulta1 EXCEPT consulta2
Tuplas homogéneas: los conjuntos de tuplas tienen que tener los mismos campos
Aplica un DISTINCT implícito (a menos que indiquemos ALL)
Ejemplo:
(SELECT usuario2 FROM Contacto WHERE usuario1 = 'charo'
UNION
SELECT usuario1 FROM Contacto WHERE usuario2 = 'charo')
INTERSECT
(SELECT usuario2 FROM Contacto WHERE usuario1 = 'lola'
UNION
SELECT usuario1 FROM Contacto WHERE usuario2 = 'lola')
78
Ejemplos de Consultas anidadas
Ejemplo 30-B (Obtener una lista de los números de los proyectos que impliquen a
cualquier empleado cuyo primer apellido sea ‘Campos’, independientemente de que
sean trabajadores o directores del departamento que gestiona dicho proyecto):
También se puede hacer con UNION:
SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO"
WHERE "PROYECTO"."NumProyecto" IN
((SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO", "DEPARTAMENTO", "EMPLEADO"
WHERE "PROYECTO"."NumDptoProyecto"="DEPARTAMENTO"."NumeroDpto" AND
"DEPARTAMENTO"."DniDirector"="EMPLEADO"."Dni" AND "Apellido1"='Campos')
UNION
(SELECT "TRABAJA_EN"."NumProy"
FROM "TRABAJA_EN", "EMPLEADO"
WHERE "TRABAJA_EN"."DniEmpleado"="EMPLEADO"."Dni" AND
"Apellido1"='Campos'));
79
Orden, agregación
SELECT COUNT (campos) FROM tabla …
[GROUP BY campo1, campo2, …];
SELECT SUM | MAX | MIN | AVG (campo) FROM tabla …
[GROUP BY campo1, campo2, …];
SELECT …
[ORDER BY campo1, campo2, …];
80
Orden, agregación
Ejemplos:
SELECT COUNT (*) FROM Escucha JOIN Cancion ON cancion = id
WHERE titulo = 'Norwegian Wood';
SELECT autor, COUNT (*) FROM Escucha JOIN Cancion ON cancion = id
GROUP BY autor;
SELECT * FROM Usuario
WHERE (SELECT COUNT (*) FROM Contacto
WHERE usuario1 = nick OR usuario2 = nick) > 2;
SELECT * FROM Usuario
ORDER BY (SELECT COUNT (*) FROM Contacto
WHERE usuario1 = nick OR usuario2 = nick);
81
Orden, agregación
82
Orden y agregación
Ejemplos de funciones agregadas, COUNT, SUM, MAX, MIN y AVG:
La función COUNT devuelve el número de tuplas o valores especificados en una consulta.
Las funciones SUM, MAX, MIN y AVG se aplican a un conjunto o multiconjunto de valores
numéricos.
SELECT SUM("EMPLEADO"."Sueldo"), MAX("EMPLEADO"."Sueldo"),
MIN("EMPLEADO"."Sueldo"), AVG("EMPLEADO"."Sueldo")
FROM "EMPLEADO"
83
Orden y agregación
Consulta 20
Visualizar la suma de los salarios de todos los empleados del departamento
‘Investigación’, así como el salario más alto, el salario más bajo, y el salario
medio de este departamento;
SELECT SUM("EMPLEADO"."Sueldo"), MAX("EMPLEADO"."Sueldo"),
MIN("EMPLEADO"."Sueldo"), AVG("EMPLEADO"."Sueldo")
FROM "EMPLEADO" JOIN "DEPARTAMENTO" ON "Dno" =
"NumeroDpto"
WHERE "NombreDpto"='Investigacion';
84
Orden y agregación
Consulta 21.
Recuperar el número total de empleados de la empresa:
SELECT COUNT (*)
FROM “EMPLEADO”;
85
Orden y agregación
Consulta 22.
Recuperar el número de empleados del departamento ‘investigación’:
SELECT COUNT (*)
FROM "EMPLEADO" , "DEPARTAMENTO"
WHERE "Dno" = "NumeroDpto" AND "NombreDpto"='Investigacion';
86
Orden y agregación
Consulta 23-a.
Contar el número de sueldos diferentes almacenados en la base de datos:
SELECT COUNT (DISTINCT "Sueldo")
FROM "EMPLEADO";
87
Orden y agregación
Consulta 23-b.
Consulta anidada correlacionada con la función agregada: La siguiente consulta anidada
recupera los nombres de todos los empleados que tienen dos o más subordinados:
SELECT "Apellido1", "Nombre"
FROM "EMPLEADO"
WHERE (SELECT COUNT (*)
FROM "SUBORDINADO"
WHERE "EMPLEADO"."Dni"=
"SUBORDINADO"."DniEmpleado") >= 2;
88
Orden y agregación
Consulta 23-b.
Salida del segundo SELECT, pero hay que meter “EMPLEADO” en FROM
SELECT *
FROM "SUBORDINADO", "EMPLEADO"
WHERE "EMPLEADO"."Dni"="SUBORDINADO"."DniEmpleado";
Hay solo dos empleados que tienen dos o más
subordinados, rodeados por los dos círculos rojos.
89
Orden y agregación
Consulta 24.
Consulta anidada correlacionada con la función agregada: La siguiente consulta anidada
recupera los nombres de todos los empleados que tienen dos o más subordinados:
SELECT "Dno", COUNT(*), AVG("Sueldo")
FROM "EMPLEADO"
GROUP BY "Dno";
90
Orden y agregación: GROUP BY (dos
atributos)Consulta 25.
Por cada proyecto, recuperar el número de proyecto, el nombre de proyecto y el
número de empleados que trabajan en ese proyecto:
SELECT "NumProyecto", "NombreProyecto", COUNT(*)
FROM "PROYECTO", "TRABAJA_EN"
WHERE "NumProyecto" = "NumProy"
GROUP BY "NumProyecto", "NombreProyecto";
91
Orden, agregación
92
Orden, agregación
Que sucede si no agrupo!!!!
93
Orden y agregación: GROUP BY (dos
atributos)Consulta 26.
Por cada proyecto en el que trabajan más de dos empleados, recuperar el número el
nombre y número de empleados que trabajan para el:
SELECT "NumProyecto", "NombreProyecto", COUNT(*)
FROM "PROYECTO", "TRABAJA_EN"
WHERE "NumProyecto" = "NumProy"
GROUP BY "NumProyecto", "NombreProyecto"
HAVING COUNT(*) > 2;
Estos grupos no se seccionan con HAVING,
por lo tanto el GROUP BY solo actuaría sobre
el resto de los grupos.
94
Orden y agregación
Consulta 27.
Por cada proyecto, recuperar el número, el nombre y la cantidad de empleados del
departamento 5 que trabajan en dicho proyecto:
SELECT "NumProyecto", "NombreProyecto", COUNT(*)
FROM "PROYECTO", "TRABAJA_EN" , "EMPLEADO"
WHERE "NumProyecto" = "NumProy" AND
"Dni" = "DniEmpleado" AND "Dno" = 5
GROUP BY "NumProyecto", "NombreProyecto"
95
Orden y agregación
Consulta 28.
Por cada departamento que tiene más de 2 empleados, recuperar el número de
departamento y el número de empleados que ganan mas de 40000:
SELECT "NumeroDpto", COUNT(*)
FROM "DEPARTAMENTO", "EMPLEADO"
WHERE "NumeroDpto" = "Dno" AND "Sueldo" > 40000 AND
"Dno" IN (SELECT "Dno"
FROM "EMPLEADO"
GROUP BY "Dno"
HAVING COUNT(*) > 2)
GROUP BY "NumeroDpto";
96
Ejemplos de orden y agregación
Ejemplo 29 (El empleado que más horas trabaja en un proyecto)
SELECT "EMPLEADO"."Nombre", "TRABAJA_EN"."Horas"
FROM "EMPLEADO", "TRABAJA_EN"
WHERE "EMPLEADO"."Dni"="TRABAJA_EN"."DniEmpleado" AND
"TRABAJA_EN"."Horas" IS NOT NULL
ORDER BY "TRABAJA_EN"."Horas" DESC Limit 1;
Ejemplo 32 (Por cada proyecto recuperar el número de proyecto, el nombre del
proyecto y el numero de empleados que trabajan en el proyecto)
SELECT "PROYECTO"."NumProyecto", "PROYECTO"."NombreProyecto",
COUNT(*)
FROM "PROYECTO", "TRABAJA_EN"
WHERE "TRABAJA_EN"."NumProy"="PROYECTO"."NumProyecto"
GROUP BY "PROYECTO"."NumProyecto", "PROYECTO"."NombreProyecto"
97
Vistas
CREATE VIEW nombre AS SELECT…;
Dan un nombre a una consulta, permiten usarla como tabla
Útil para reutilizar consultas y evitar ejecutarlas varias veces
Pueden configurarse para que se almacenen en disco
Ejemplos:
CREATE VIEW Contactos_Usuario AS
SELECT u1.nick, u2.nombre FROM Usuario AS u1, Usuario as u2
WHERE (u1.nick, u2.nick) IN
((SELECT usuario1, usuario2 FROM Contacto)
UNION (SELECT usuario2, usuario1 FROM Contacto));
SELECT nombre FROM Contactos_Usuario WHERE nick = 'pepe';
3-ER-EDAT-121.pdf
E/R
(Cap 3 - Elmasri 5ª edición,
Cap 4 es el EER, se aconseja lectura)
Recopilación y Análisis de Requisitos
Ejemplo - BDs - EMPRESA
Los diseñadores de la base de datos proporcionan la siguiente
descripción del minimundo (la parte de la empresa que se va a
representar en la base de datos):
1. La empresa está organizada en departamentos. Cada uno tiene un
nombre único, un número único y un empleado concreto que lo
administra. Se realizará un seguimiento de la fecha en que ese
empleado empezó a administrar el departamento. Un
departamento puede tener varias ubicaciones.
2. Un departamento controla una cierta cantidad de proyectos, cada
uno de los cuales tiene un nombre único, un número único y una
sola ubicación.
Recopilación y Análisis de Requisitos
Ejemplo - BDs - EMPRESA
Los diseñadores de la base de datos proporcionan la siguiente
descripción del minimundo (la parte de la empresa que se va a
representar en la base de datos):
3. Almacenaremos el nombre, el documento nacional de identidad, la
dirección, el sueldo, el sexo y la fecha de nacimiento de cada empleado.
Un empleado está asignado a un departamento, pero puede trabajar
en varios proyectos, que no están controlados necesariamente por el
mismo departamento. Se hará un seguimiento del número de horas por
semana que un empleado trabaja en cada proyecto. También se
realizará el seguimiento del supervisor directo de cada empleado.
4. También se desea realizar un seguimiento de las personas a cargo de
cada empleado por el tema de los seguros. Por cada persona a cargo o
subordinado, se registrará su nombre de pila, sexo, fecha de nacimiento
y relación con el empleado.
5. Mas todos los requisitos funcionales de extracción de información.
Modelo conceptual E-R
• Entidad: Objeto del mundo real con existencia
independiente
• Físico: persona, coche, casa, empleado etc.
• Conceptual: puesto de trabajo, etc.
• Relación: Es un vínculo que nos permite definir una
dependencia entre varia entidades.
• Atributos: Las diferentes propiedades que pueden tener
la entidades.
• Atómicos o compuestos
• Monoevaluados o multievaluados
• Almacenados o derivados
• NULL
• Complejos
• Etc.
Modelo E/R
EMPLEADO
Dirección
DNI
1
CONTROLA
PROYECTO
DEPARTAMENTO
Nombre Número
Ubicaciones
1
N
Ubicación
Número
Nombre
TRABAJA_EN
NM
Horas
TRABAJA_PA
RA
ADMINISTRA
11
N
NumEmpleados
FechaInicio
FechaNac Nombre Sueldo
NombreP Apellido1 Apellido2
Sexo
CONTROL
1 N
Supervisor
Supervisado
SUBORDINAD
OS_DE
SUBORDINADO
N
1
Nombre Sexo FechaNac Relación
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
TRABAJA_PARA
1:N
Numero de relaciones ri en el que puede
participar cada ei → 1
Participación completa ====
(todos los empleados trabajan en
algún departamento).
Numero de relaciones ri en el que puede
participar cada di → N
NOTA: En el esquema E/R
va al contrario N:1
1:N -> Una entidad en EMPLEADO se relaciona exclusivamente con
una entidad en DEPARTAMENTO. Pero una entidad en DEPATAMENTO
se puede relacionar con 1 o muchas entidades en EMPLEADO
(participación completa en este caso en los dos lados
NOTA: Se dice N empleados trabajan
para un departamento , por eso en el
modelo E/R se escribe al contrario N:1
EMPLEADO DEPARTAMENTO
Dado un conjunto de relaciones binarias y los conjuntos de entidades A y B, la
correspondencia de cardinalidades puede ser:
UNO a UNO:
1:1 -> Una entidad de A se relaciona únicamente con una entidad en B y viceversa.
UNO a VARIOS:
1:N -> Una entidad en A se relaciona exclusivamente con una entidad en B. Pero una
entidad en B se puede relacionar con 0 o muchas entidades en A.
VARIOS a UNO:
N:1 -> Una entidad en A se relaciona con cero o muchas entidades en B. Pero una
entidad en B se relaciona con una única entidad en A.
VARIOS a VARIOS:
N:M -> Una entidad en A se puede relacionar con 0 o muchas entidades en B y
viceversa.
Correspondencia de cardinalidades
1: Supervisor
2: Supervisado
CONTROL es una relación recursiva dentro
De la misma entidad EMPLEADO
1:1Participación parcial: NO todas las
entidades individuales (ei) de
EMPLEADO participan de las instancias
(ri) de la relación ADMINISTRA
Participación total: todas las entidades
individuales (di) de DEPARTAMENTO
participan de las instancias (ri) de la
relación ADMINISTRA
M:N
4-MR-EDAT-121.pdf
MR y su mapeo desde E-R
(Cap 5 y 7 - Elmasri 5ª edición)
Modelo Relacional
• Fue presentado por Ted Codd de IBM en
1970.
• Utiliza el concepto de relación matemática
como bloque de construcción básica.
• Su base teórica es la teoría de conjuntos de
la lógica de predicado de primer orden.
• LA BD se presenta como una colección de
relaciones (semejantes a tablas).
Modelo Relacional
• Se define TUPLA como cada fila de la tabla.
• Cada tupla representa una colección de
datos relacionados entre si.
• Cada tupla esta dividida n diferentes
ATRIBUTOS (no pueden ser ni compuestos ni
multievaluados).
• Se define DOMINIO como los tipos de
valores que pueden aparecer en una
columna.
Modelo Relacional
• El ESQUEMA de una relación sirve para
describir la relación: R(A1, …, An)
• El GRADO de una relación es el número de
atributos de su esquema.
• La RELACIÓN es la tabla de tuplas.
• Valores Nulos: se diseña el modelo para
evitar esto
Modelo Relacional: Claves
• Superclave: conjunto de atributos que identifican de
manera única cada fila de la relación.
• Clave: conjunto de atributos mínimos que identifican
de manera única cada fila de la relación.
• Clave primaria: la que se escoge de la las claves
candidatas
• Clave externa: conjunto de atributos de una relación
que forman parte de la clave de otra relación.
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
Modelo Relacional: Restricciones
Es una regla que restringe los
valores que puedan
aparecer en una BD.
Varios tipos:
• Restricciones de Dominio
• Restricciones de Clave
• Restricciones de integridad de entidades
• Restricciones de integridad referencial
• Restricciones de integridad semántica
Operaciones de Actualización en BD’s
• Las restricciones de una BD se pueden modificar en:
– Insertar:
• Se puede violar todos los tipos
• 2 opciones: rechazar la inserción o corregirla
– Eliminar:
• Se puede violar por ejemplo la integridad referencial de las FKs
• 3 opciones: rechazar, tratar de propagar la eliminación eliminando las
tuplas que hace referencia o modificar los valores del atributo
referencia
– Modificar:
• Si no es PK o FK no hay problemas
• Si es PK equivale a eliminar+insertar
• Si es FK comprobar las restricciones de integridad referencial
Modelo E/R
EMPLEADO
Dirección
DNI
1
CONTROLA
PROYECTO
DEPARTAMENTO
Nombre Número
Ubicaciones
1
N
Ubicación
Número
Nombre
TRABAJA_EN
NM
Horas
TRABAJA_PA
RA
ADMINISTRA
11
N
NumEmpleados
FechaInicio
FechaNac Nombre Sueldo
NombreP Apellido1 Apellido2
Sexo
CONTROL
1 N
Supervisor
Supervisado
SUBORDINAD
OS_DE
SUBORDINADO
N
1
Nombre Sexo FechaNac Relación
Conversión E/R a MR
• Nos centraremos en como diseñar el esquema de una BD (Modelo Relacional)
basándose en el esquema conceptual E/R.
• Las transformaciones generales son:
– Entidad -> Esquema relacional (tabla de tuplas) con la elección de una C-1º
• Atributos atómicos -> Columna de la tabla
• Atributos compuestos-> Varias Columnas de la tabla
• Atributos multievaluado -> Nueva tabla dos columnas (C-1º de la entidad y el valor del
atributo) ¿Cuál es la C-1º de la nueva tabla?
– Entidad Débil -> tabla + columna con la C-1º de la entidad fuerte de la que
depende.
– Relación binarias -> tabla con los posibles atributos de la relación
• 1:1 -> en una de las dos entidades introduces la C-1º (clave externa) de la otra (la elección
minimizará el número de NULLS). Otra opción si la participación es total en los dos sentidos se
realiza un tabla mezclada con las dos entidades participantes.
• 1:n -> Igual que 1:1 pero identificando la entidad que participa como n para añadir la C-1º de
la otra entidad (clave externa).
• n:m -> se crea una tabla con las C-1ºs (claves externas) de las entidades de la relación y con
los posibles atributos de la relación (reificación). ¿Cuál es la C-1º de la nueva tabla?
– Relaciones n-arias (n>2) -> tabla con una columna para cada atributo de las C-1ºs
(claves externas) de las entidades + posibles atributos de la relación.
Conversión E/R a MR: proceso ordenado
1. Mapeado de los tipos de entidades regulares (EMPLEADO,
DEPARTAMENTO, PROYECTO)
2. Mapeado de los tipos de entidades débiles (SUBORDINADO)
3. Mapeado de los tipos de relación 1:1 (ADMINISTRA)
– Metodología clave externa (minimizar NULLs) (siempre
utilizaremos esta)
– Metodología de relación mezclada (participación parcial en los
sentidos y se pueden mezclar las dos entidades en una misma
tabla)
– Metodología de referencia cruzada (la misma que en m:n)
4. Mapeado de los tipos de relación 1:n (CONTROL,
TRABAJA_PARA, CONTROLA) (minimizar NULLs y redundancia)
5. Mapeado de los tipos de relación m:n (TRABAJA_EN)
6. Mapeado de los atributos multivalor (LOCALIZACIONES_DPTO)
7. Mapeado de los tipos de la relación n-aria
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
5-DFyN-EDAT-121.pdf
DFyN (Diseño de BDs)
(Cap 10 - Elmasri 5ª edición y parte del Cap 11)
2
Diseño de BD
Hasta ahora solo se ha utilizado el sentido común para diseñar BDs.
Necesitamos algún tipo de medida formal que nos que nos indique
porque el agrupamiento de atributos en el esquema de relación puede
ser mejor o no.
Para ello esta lo que se denomina “Bondad” de los esquemas de
relación que se puede explicar a 2 niveles:
– Nivel lógico: se refiere a la forma en que los usuarios interpretan el
esquema y significado de los atributos y se aplica a esquemas de
relaciones base y vistas (tablas virtuales, ie. una tabla que deriva de
otras tablas, CREATE VIEW).
– Nivel de manipulación o almacenamiento: se refiere a como se
almacenan y se actualizan la tuplas de una relación. Se aplica a
esquemas de relaciones base que son los que se almacenan físicamente
como archivos.
3
Diseño de BD
El diseño de una BD puede seguir dos metodologías:
– Ascendente o diseño por sintesis (Bottom-up): parte de las relaciones básicas
entre atributos individuales hacía el esquema de relación de la BD.
– Descendente o diseño por análisis (Top-down): empieza con varios
agrupamientos de atributos de una relación que están juntos de forma natural y
luego viene la posible descomposición.
La teoría descrita aquí se aplica a los dos tipos de diseño, pero en general a la
descendente.
En general vamos a ver:
– Criterios para distinguir esquemas buenos de esquemas malos.
– Dependencias Funcionales (DFs): son la principal herramienta para medir
formalmente la idoneidad de las agrupaciones de atributos para formar esquemas
de relación.
– El uso de DFs para agrupar atributos en esquemas que estén en una determinada
Forma Normal (FN).
– Veremos que cuando un esquema está en su FN tiene ciertas características
deseables.
4
Medidas de Calidad Informales de BDs
Semántica de atributos
Reducción de valores redundantes en tuplas
Reducción de valores nulos en tuplas
Prohibición de tuplas espurias
Estas 4 medidas de calidad sobre el diseño de BDs no son
independientes entre si.
5
Semántica de los Atributos de una Relación
Especifica que relaciones hay entre los atributos de una tupla
Cuando más fácil sea especificar la semántica de la tupla, más fácil será el
diseño del esquema.
Las recomendaciones son las siguientes:
– El diseño del esquema se hará de modo que sea fácil explicar su significado.
– Por regla general no se combinarán atributos de varios tipos de entidades en una
sola relación (excepto los
extrictamente necesarios, PKs)
Este es un esquema fácil de
explicar semánticamente
6
Semántica de los Atributos de una Relación
Ejemplo del estado de la BD del esquema relacional anterior:
TRABAJA_EN
7
Semántica de los Atributos de una Relación
En este caso, aunque aquí la semántica es buena, se mezclan atributos de diferentes
entidades:
– (a) Mezcla de atributos de EMPLEADO y DEPARTAMENTO
– (b) Mezcla de atributos de EMPLEADO, PROYECTO y TRABAJA_EN (hemos quitado los
atributos Dirección, FechaNac y NumeroDpto para que se pueda visualizar la tabla bien en la pantalla)
(a)
Datos de proyecto
Datos de departamento
(b)
EMP_DEPT
EMP_PROY
8
Información Redundante en Tuplas
Uno de los objetivos de las BDs (su diseño) es minimizar el espacio de
almacenamiento BDs que ocupan las relaciones base.
La agrupación de atributos en relaciones tiene efecto significativo sobre el
espacio de almacenamiento.
Si aplicamos un NJ al estado de la BD que tenemos como ejemplo a las tablas
EMPLEADO >< DEPARTAMENTO obtenemos una tabla EMP_DEPT
Aparece una tabla con redundancia en las tuplas. Redundancia
EMP_DEPT
9
Información Redundante en Tuplas
En teoría de información la redundancia es una propiedad de los
mensajes, consiste en tener partes predictibles a partir del resto del
mensaje y que por tanto en si mismo no aportan nueva información
o repiten parte de la información ya existente.
En BD’s la redundancia hace referencia al almacenamiento de los
mismos datos varias veces.
La redundancia puede provocar varios problemas:
– Incremento de trabajo (cuando se almacena o se borra un
dato hay que almacenarlo o borrarlo de varios sitios).
– Derroche de espacio de almacenamiento.
– Inconsistencia (cuando los datos redundantes no coinciden
por una modificación parcial de los mismos).
La redundancia debe eliminarse en una BD, excepto la controlada
(para seguridad y no perdida de datos, para mejorar el rendimiento
respecto la consulta de BDs, etc.).
10
Información Redundante en Tuplas
Si ahora combinamos información de EMPLEADO, PROYECTO y
TRABAJA_EN obtenemos la tabla EMP_PROY.
Aparece una tabla con redundancia en las tuplas.
Redundancia
Redundancia
EMP_PROY
11
Información Redundante en Tuplas
De la tabla anterior hemos quitado los atributos Dirección, FechaNac y
NumeroDpto para que se pueda visualizar la tabla bien en la pantalla.
12
Información Redundante en Tuplas:
Anomalías de Actualización
Obviamente uno de los problemas que surgen cuando se usan las relaciones
anteriores formados por los NJs, como relaciones bases, son la anomalías de
actualización que comentamos anteriormente:
– Inserción:
• para insertar un nuevo empleado en la tabla EMP-DEPT debemos asegurar que los datos
del departamento al que pertenece son congruentes con los datos del departamento en
otras tuplas.
• Es difícil insertar un nuevo departamento que aún no tiene empleados (se puede hacer
poniendo NULLs, pero esto tiene problemas (DNI es C-1º ya que cada tupla representa
un empleado, además cuando se introduce el primer empleado del departamento hay que
ponerlo en los NULLs)
– Eliminación:
• Si eliminamos la tupla correspondiente al último empelado de un departamento se pierde
la información de ese departamento.
– Modificación :
• Si cambiamos el valor de uno de los atributos de un departamento, debemos actualizar
todas la tuplas de todos los empleados que pertenecían a ese departamento. Por ejemplo
investigación se cambia a I+D: hay que hacerlo en todas.
13
Información Redundante en Tuplas
Si nos basamos en las tres anomalías anteriores, las recomendaciones
serían:
– Diseñar esquemas de forma que podamos evitar las anomalía anteriores.
– Si hubiese anomalías indicarlo para que los programas de actualización del
SGDB operen correctamente.
– OJO: en ciertas ocasiones es preciso saltarse la recomendaciones para
mejorar el rendimiento de ciertas consultas :
• por ejemplo cuando se quiere consultar datos de departamento y empleado a la
vez de manera muy intensa.
• Pero para esto están las VISTAS.
– En general es altamente aconsejable utilizar relaciones base LIBRES
de anomalías y especificar VISTAS que incluyan atributos de distintas
relaciones.
14
Valores Nulos en Tuplas
Tenemos que ser conscientes que si agrupamos muchos atributos en una relación,
puede que algunos de ellos que no apliquen a todas la tuplas de la relación (NULL).
Los NULLs siempre tiene problemas:
– Desperdicio de espacio de almacenamiento.
– Como se manejan en funciones agregadas (operaciones relacionales), por ejemplo:
COUNT, SUM, MAX, MIN, AVG
– SELEC SUM(sueldo), MAX(sueldo), MIN(sueldo), AVG(sueldo) FROM
EMPLEADO;
– SELEC COUNT(*) FROM EMPLEADO;
Además los nulos pueden tener múltiples interpretaciones:
– El atributo no se aplica a esa tupla en cuestión.
– Valor desconocidos del atributo para esa tupla.
– Valor no registrado aunque se conoce.
Por lo tanto tienen la misma representación pero el significado puede ser diferente.
Se recomienda:
– Evitar incluir atributos cuyos valores puedan ser nulos.
– Si es imposible evitar nulos asegurarse que son una excepción en cierta tuplas.
15
Tuplas Espurias
Supongamos que empleamos EMP_LOCS y EMP_PROJ1 como relaciones base:
Esto nos hace no
poder recuperar la
información que
había en la tabla
EMPO_PROY desde
esas 2 nuevas tablas .
Para ello hacemos un
NJ de la estas dos
tablas (a través de
UbicacionProyecto)
16
Tuplas Espurias
Se obtienen tuplas erróneas (*) porque le NJ se hace sobre atributos que no son C-
1ºs (UbicaciónProyecto no es ni PK ni FK).
17
Tuplas Espurias
La recomendación es que se diseñen esquemas sobre los cuales se
pueden hacer NJ sin producir tuplas espurias, es decir que los
atributos del NJ sean PK y FK.
18
Superclaves, Clave Candidata, Clave
Principal, Atributos Primos
Una superclave X de un esquema de la relación R={A1,…,An} es un conjunto
de atributos S R con la propiedad de que no habrá un par de tuplas t1 y t2 en
ningún estado de la relación permitido r de R tal que t1[X] = t2[X].
Un clave K es una superclave con la propiedad adicional de que la
eliminación de cualquier atributo de K provocará que K deje de ser una
superclave. Es la superclave mínima.
Si un esquema de una relación tiene más de una clave cada una de ellas se
llama clave candidata.
Una de ellas se elige arbitrariamente como clave principal.
El resto de las claves candidatas son claves secundarias.
Cualquier atributo del esquema de la relación R que pertenece a una clave
candidata o es una clave candidata se denomina atributo primo o primario.
Un atributo no primo no es miembro de una clave candidata o no es una
clave candidata.
19
Dependencias Funcionales
Una dependencia funcional (DF) es una restricción implícita.
Si X e Y son dos conjuntos de atributos, decimos que hay un DF de X a Y o que
Y depende funcionalmente de X, si y solo si siempre que dos tuplas que
coinciden en su valor X, necesariamente deben coincidir en su valor Y.
Es decir, los atributos de Y están unívocamente determinados por los de X
Notación: X Y (dni FechaNac, pero no FechaNac dni)
20
Dependencias Funcionales
De manera más estricta: Dados dos conjuntos X e Y de atributos de un esquema
de relación R, supongamos dos tuplas t1 y t2 entonces el conjunto de atributos Y
depende funcionalmente del conjunto de atributos X si t1[X] = t2[X] t1[Y] =
t2[Y], t1, t2 r(R), donde r(R) es un estado de la relación particular del
esquema de relación.
Ojo que el tener la DF X Y no supone tener Y X en R.
En el ejemplo de la figura anterior las posibles DFs que tenemos son:
– DF1: {Dni, NumProyecto} Horas
– DF2: Dni NombreE
– DF3: NumProyecto {NombreProyecto, UbicaciónProyecto}
En general si X es una superclave de R X Y para cualquier conjunto de
atributos Y de R.
Observar que si X es una clave candidata de la relación entonces tenemos X Y
para cualquier agrupación de atributos Y (ejem: en EMP_DEPT existe la DF Dni
Número Dpto, pero no Número Dpto Dni , ver en la tabla).
Realmente un DF la podríamos ver en la tabla como una restricción entre
atributos.
21
Dependencias Funcionales: Reglas de
Inferencia
Las reglas de inferencia sirven para deducir o inferir nuevas dependencias
funcionales a partir de un conjunto dado de dependencias.
(RI1) Reflexividad: Y X |= X Y (dependencia trivial)
(RI2) Aumento: { X Y } |= XZ YZ
(RI3) Transitividad: { X Y, Y Z } |= X Z
(RI4) Proyección: { X YZ } |= X Y (descomposición)
(RI5) Aditividad: { X Y, X Z } |= X YZ (unión)
(RI6) Pseudotransitividad: { X Y, WY Z } |= WX Z
Obsérvese que: { X Y, Z W } NO|= XZ YW
XY Z NO|= X Z
F |= X Y quiere decir que la dependencia funcional X Y se infiere o se
deduce del conjunto de dependencias funcionales F.
22
Dependencias Funcionales: Reglas de
Inferencia
RI1 especifica que un conjunto de atributos siempre se determina a si mismo o
cualquiera de sus subconjuntos (obvio). Ya que RI1 genera dependencias que
siempre son verdaderas estas se llaman triviales, las no triviales son el resto.
RI2 o regla de aumento dice que añadir el mismo conjunto
de atributos a cada
lado de la dependencia genera otra dependencia válida.
RI3 o regla transitiva dice que una dependencia funcional X Z en un esquema
de relación R es una dependencia transitiva si existe un conjunto de atributos Y
que ni es clave candidata ni es un subconjunto de una clave de R, y se cumple
tanto X Y como Y Z.
RI4 (regla de eliminación de atributos del lado derecho), si aplicamos esta regla
repetidamente podemos descomponer la DF {X A1, …, An} en el conjunto de
DFs {X A1, …, X An}.
RI5(regla de unión de atributos), nos permite realizar lo contrario para combinar
DFs {X A1, …, X An} en la DF {X A1, …, An}.
Se puede comprobar que RI4, RI5 y RI6 se infieren usando las reglas RI1, RI2 y
RI3 (reglas de inferencia de Amstrong). (ver el cap 10 del libro).
23
Dependencias Funcionales: Reglas de
Inferencia de Amstrong
Clausura de F (F+): el conjunto de todas las DFs que incluyen F, junto con todas la
DFs que se pueden inferir desde F es lo que se llama clausura de F y se designa por
F+.
Amstrong (1974) demostró que las reglas RI1, RI2 y RI3 (reglas de
inferencia de Amstrong) son sólidas y completas.
Sólida: Dado un conjunto de DFs F especificados en un esquema de una
relación R, cualquier DF que podamos inferir de F usando solo RI1, RI2 y
RI3, se cumple en cada estado de la relación r de R que satisfaga las DFs de F
(a partir de una DF que se cumple en R, aplicando las reglas de
inferencia de Amstrong, obtenemos otra DF que se cumple en R).
Completa: Usando estas reglas para inferir DFs hasta que no se pueda
determinar ninguna otra entonces se genera un conjunto completo de todas
las dependencias posibles que se pueden inferir a partir de F. Así la clausura
de F se puede obtener con la reglas de inferencia de Amstrong (se puede
obtener F+ aplicando las reglas de inferencia de Amstrong).
24
Dependencias Funcionales: Conjuntos
Mínimos
Dos conjuntos de dependencias F y E son equivalentes si toda dependencia de
uno se puede inferir de las dependencias del otro y viceversa (es decir F+ = E+).
Un conjunto de DFs de F es mínimo si:
– La parte derecha de todas sus dependencias es un solo atributo.
– Si eliminamos una dependencia, obtenemos un conjunto no equivalente a F.
– Si eliminamos un atributo en la parte izquierda de una dependencia, obtenemos un
conjunto no equivalente a F.
En otras palabras: DFs en forma canónica y sin redundancias.
Una cobertura mínima de un conjunto de DFs de F es un conjunto mínimo
equivalente a F.
25
Dependencias Funcionales: Conjuntos Mínimos,
Algoritmo
Cobertura mínima (E) /* encuentra una cobertura mínima F de las DFs E */
1. Establecer F := E
/* Descomponer en dependencias sobre atributos individuales en la parte derecha
(atributos redundantes) */
2. Substituir todas las dependencias X { A1, …, An } en F
por X A1, …, X An
/* Eliminar atributos que sobren en las partes izquierdas (atributos redundantes) */
3. for X A F do
for atributo B X do
if F – { X A } { (X – {B}) A } es equivalente a F
then F := F – { X A } { (X – {B}) A } (reemplazar
X A por (X – {B}) A en F)
/* Eliminar dependencias que se infieren de otras (inferencia redundantes) */
4. for X A F do
if {F – { X A}} es equivalente a F
then F := F – { X A} (eliminamos X A)
return F
26
Ejemplo Cobertura Mínima
Supongamos las DFs de E: {DF1: B A, DF2: D A, DF3: AB D}, la cobertura mínima
sería: (paginas 511 y 512, Edi. 7)
1. Establecer F := E
/* Descomponer en dependencias sobre atributos individuales en la parte derecha */
2. Ya lo están.
/* Eliminar atributos que sobren en las partes izquierdas (atributos redundantes) */
3. De la única que podemos quitar es de AB D ¿Podemos quitar A o B de la parte
izquierda quedando B D o A D? Es decir ¿podríamos sustituir AB D por B
D o A D? Supongamos la primera DF1: B A, aumentando con B (RI2)
inferimos BB AB o lo que es lo mismo B AB. Así tenemos estas dos DFs: {B
AB, AB D} |= B D (RI3). Por lo tanto si sustituimos AB D por B D
tenemos lo mismo, ya que B D se infiere de las dos DFs.
/* Eliminar dependencias que se infieren de otras (inferencia redundantes) */
4. Hacer notar que tenemos {B A, D A, B D}, así que {B D, D A} |= B
A y esta ya está, y por lo tanto es redundante. Así F:={B D, D A}
return F (es una cobertura mínima)
27
Normalización de Datos (Basadas en PKs)
En 1972 Codd hace pasar un esquema de relación por una serie de
comprobaciones para certificar que satisface cierta forma normal (E. F.
Codd. Further Normalization of the Data Base Relational Model. IBM
Research Report, San Jose, California RJ909: (1971))
Codd propone la 1ª, 2ª y 3ª Formas Normales (FNs), posteriormente
Boyce y Codd proponen la BCNF (más estricta que la primera).
La normalización de datos es el proceso por el cual los esquemas de
relación insatisfactorios se descomponen en esquemas más pequeños
con propiedades más deseables.
Deseables???? El objetivo es evitar las diversas anomalías que hemos
comentado anteriormente.
28
Normalización de Datos (Basadas en PKs)
El proceso consiste en efectuar una serie de pruebas sobre el esquema relacional
propuesto:
– Si fallan las pruebas, la relación se descompone en relaciones más pequeñas que si
son capaces de satisfacer las pruebas.
– Por si solo las FNs no garantizan de manera absoluta un buen diseño de la BD, se
tienen que cumplir otras propiedades:
• Preservación de atributos.
• Reunión sin perdida o reunión no aditiva (se garantiza que no hay tuplas espurias),
recordar que hay que diseñar tablas sobre las que se pueda hacer un NJ sobre atributos
que sean PK o FK (ESTRICTA).
• Conservación de dependencias: todas la DFs están representadas en alguna relación
individual tras la descomposición (SE PUEDE SACRIFICAR).
Así una FN de una relación hace referencia a la FN más alta que cumple e indica
el grado en la que ha sido normalizada.
29
Formas Normales
Es un marco formal para el análisis de los esquemas de relación en claves y en
dependencias funcionales entre sus atributos.
Se pasan una serie de pruebas a los esquemas hasta normalizar al grado
deseado.
Son incrementales
– Si se cumple la forma normal n-ésima se cumple la (n-1)-ésima
30
Formas Normales
Formas normales 1ª, 2ª, 3ª, BCNF
– Involucran un solo esquema
– No eliminan totalmente la posibilidad de anomalías de
actualización, pero las reducen a casos muy excepcionales en
la práctica
Formas 4ª, 5ª y 6ª
– Eliminan sucesivamente más anomalías de actualización
Se normaliza para evitar redundancia, mantener la
integridad de los datos y mejorar el redimiendo del
SGBD.
31
1ª forma normal (1NF)
Decimos que un esquema está 1NF si:
– Los atributos son atómicos y univaluados.
– Los nombres de atributo son únicos.
– No hay tuplas duplicadas (consecuencia: todo esquema tiene alguna clave).
– El orden de tuplas y atributos es arbitrario .
Realmente estas propiedades se considera parte inherente del modelo relacional.
Aunque tenemos que hacer notar que:
– SQL sólo cumple la primera de estas condiciones (el tratamiento de NULL se sale
también del modelo relacional)
– Se estudian alternativas como el modelo relacional anidado, que admite relaciones
como valores de atributos
Esta Forma Normal elimina los valores repetidos en una BD
32
1ª forma normal (1NF)
(a) NO esta en 1FN, por ejemplo mirar (b)UbicacionesDpto no es un atributo
atómico, en cambio en (c) SI esta en 1FN (PK es {UbicacionesDpto,NumeroDpto})
La solución (c)
démonos cuenta
que introduce
redundancia.
Si seguimos
posteriormente con
el proceso de
normalización
quitaríamos esta
redundancia y nos
llevaría a otra
posible solución
que podríamos a
haber tomado:
hacer una tabla
independiente.
33
1ª forma normal (1NF)
¿Como hacemos una tabla independiente?: sería quitar el atributo UbicacionesDpto
(es el atributo que infringe 1NF) y se pone en una tabla aparte junto con el atributo
NumeroDpto (FK). En la nueva tabla la PK es {UbicacionesDpto, NumeroDpto}.
Otra solución si se conoce el número
máximo de valores para cada atributo,
digamos por ejemplo tres, se sustituye
ese atributo multievaluado por tres
atributos atómicos UbicacionDpto1,
UbicacionDpto2 y UbicacionDpto3.
Esta solución es menos deseable.
Esto tiene el consiguiente problema
de NULLs y además la consulta de
este atributo se hace más complicada
(ejemplo de consulta: departamentos
que tienen Valencia como ubicación).
34
2ª forma normal (2NF)
Está basado en el concepto de DF total (si se elimina cualquier atributo de X se
rompe la dependencia).
Una dependencia funcional X Y es plena si no le sobra ningún atributo a
X, es decir X – {A} no determina funcionalmente a Y, A X.
Dicho de otro modo, los atributos dependen de la clave completa; sólo los
atributos de una clave pueden depender de partes de éstas.
Atributo no primo o no primario = atributo que no es parte de ninguna clave.
Un esquema R está en 2FN si todo atributo no primo forma una DF total
de alguna clave candidata.
Un esquema R es 2NF si todo atributo no primario de R tiene una dependencia
funcional plena con las claves de R.
Recordar que atributos primarios son los que forman parte de alguna clave.
35
2ª forma normal (2NF)
Una dependencia funcional X Y es parcial si X – {A} Y, con A X.
– La DF {Dni, NumProyecto} Horas es completa ya que ni Dni Horas ni
NumProyecto Horas son dependencias válidas.
– Sin embargo la DF {Dni, NumProyecto} NombreE es una dependencia parcial
porque también se cumple que Dni NombreE es una DF.
Así DF1 cumple 2NF, pero DF2 y DF3 no cumplen 2NF ya que no son
dependencias funcionales completas con la clave candidata {Dni, NumProyecto}.
OJO: Lo atributos no primos tienen que depender completamente de la clave
candidata. Solo se permite depender a atributos de una clave candidata (atributos
primos) con partes de claves candidatas (esta permitido en 2NF).
36
2ª forma normal (2NF)
El proceso de normalización de 1NF 2NF se hace dividiendo la relación en
varias relaciones en las que los atributos no primos estén asociados solo a la
parte de alguna clave candidata o claves candidatas de la que dependen.
37
2ª forma normal (2NF)
La comprobación 2FN implica la comprobación de la DFs de la relación cuyos
atributos del lado izquierdo forman parte de alguna clave candidata.
Si la clave candidata contiene un solo atributo no hace falta comprobar la
verificación.
En la tabla EMP_PROY la clave primaria esta formada por {Dni, NumProyecto} y
no hay ninguna otra clave candidata.
El atributo no primo NombreE que forma parte de DF2 no cumple 2FN ya no
depende totalmente de la clave primaria o alguna clave candidata.
Los atributos no primos NombreProyecto y UbicaciónProyecto que forman parte de
DF3 incumplen 2FN por la misma razón.
La normalización 2NF se realiza dividendo la relación en relaciones en las que los
atributos no primos solo estén asociados a la parte de la clave principal de la que son
completa y funcionalmente dependientes. Ver figura anterior.
38
Ejemplo de eliminación
de redundancia con 2FN
TRABAJA_EN
39
Ejemplo de eliminación
de redundancia con 2FN
Si ahora combinamos información de EMPLEADO, PROYECTO y
TRABAJA_EN obtenemos la tabla EMP_PROY. Aparece una tabla con
redundancia en las tuplas y la eliminamos con 2FN.
DF2 DF3
DF1 no genera
redundancia
40
Ejemplo de eliminación
de redundancia con 2FN
¿Porqué hay redundancia en tablas que no cumplen 2FN?
Supongamos la DF Dni NombreE, esta dependencia genera
repeticiones:
– Recordemos que la clave primaria es: {Dni, NumProyecto}
– Por lo tanto puede haber tuplas con el mismo Dni (Dni aquí
no es clave primaria).
– Así como existe la DF Dni NombreE, hay todavía más
repeticiones (por la definición de Dependencia Funcional: si
encontramos tuplas con el mismo Dni a la fuerza NombreE
es el mismo en esas tuplas.
Para resolver este problema hacemos tablas base más pequeñas
sin esas repeticiones mediante el proceso de normalización:
– todos los atributos no primos dependen totalmente de alguna
clave.
41
Ejemplo de eliminación
de redundancia con 2FN
El proceso de normalización de 1NF 2NF se hace dividiendo la relación en
varias relaciones en las que los atributos no primos estén asociado solo a la
parte de alguna clave candidata o claves candidatas de la que dependen.
42
3ª forma normal (3NF)
Se basa en el concepto de dependencia transitiva: una dependencia funcional
X Y en un esquema de relación R es una dependencia transitiva si existe un
conjunto de atributos Z que ni es clave candidata ni es un subconjunto de una
clave de R, y se cumple tanto X Z como Z Y.
Ejemplo: en la relación EMP_DEPT la dependencia Dni DniDirector es
transitiva a través de NumeroDpto, ya que se cumplen las dependencias Dni
NumeroDpto y NumeroDpto DniDirector (nota: NumeroDpto no es una
clave por si misma ni un subconjunto de clave de EMP_DEPT).
3FN = 2FN+ningún atributo no primo depende transitivamente de la clave
principal o primaria a través de un atributo no primo.
43
3ª forma normal (3NF)
Se puede demostrar que una definición equivalente es que un esquema es
3NF si para toda dependencia X A no trivial (X A es trivial si A X),
o bien X es una superclave, o bien A es un atributo primo.
Dicho de otro modo, no puede un atributo depender de algo que no sea una
superclave, excepto acaso los atributos que forman parte de alguna clave.
44
3ª forma normal (3NF)
También las relaciones que están en 2FN pueden tener redundancia, por el
mismo motivo de antes: hay dependencias funcionales que la parte Y
depende de algo X que no es clave.
Dni NumeroDpto y NumeroDpto {NombreDpto, DniDirector}, y
NumeroDpto no es C-1ª (es un atributo no primo)
Redundancia
45
3ª forma normal (3NF)
EMP_DEPT está en 2NF ya que no existen dependencias no parciales de una clave.
No esta en 3NF ya que existen dependencias transitivas a través de NúmeroDpto:
– Dni NombreDpto y Dni DniDirector son dependencias transitivas a través del atributo
NúmeroDpto .
– La DF NúmeroDpto {NombreDpto, DniDirector} no esta en 3NF (la parte izquierda no es
una superclave, la parte derecha no es un atributo primo).
Podemos descomponerla en ED1 y ED2, de tal forma que si hacemos una NJ entre esas tablas
recuperamos la tabla original EMP_DEPT sin tuplas espurias.
46
Resumen formas normales
Intuitivamente podemos ver que cualquier dependencia funcional en la que el
lado izquierdo es parte de la clave principal (subconjunto propio) , o es un
atributo no clave, implica una DF problemática.
La normalización 2FN y 3FN eliminan esas dependencias problemáticas.
47
Forma normal Boyce-Codd (BCNF)
Un esquema R es BCNF si para toda dependencia X Y no trivial
X es una superclave de R
Dicho de otro modo, no puede haber más dependencia que con las superclaves.
Este esquema tiene por claves candidatas: IdPropiedad y {NombreMunicipio,
NúmeroParcela}, se elige como primaria IdPropiedad.
La parte izquierda de DF5
no es superclave de la
relación PARCELAS1A.
Notar que si es 3FN ya que
la parte derecha es un
atributo primo
(NombreMunicipio forma
parte de una clave
candidata).
48
Forma normal Boyce-Codd (BCNF)
Para pasar a BCNF perdemos la dependencia DF2 ya que sus atributos no
coexisten en una misma relación.
Notar que en la normalización BCNF se pueden perder dependencias, al contrario
de lo que pasa en 2FN y 3FN.
Generalmente los esquemas que están en 3FN lo están BCNF. Solo si se cumple
X A en un esquema de relación R, no siendo X un superclave y siendo A un
atributo primo estarán en 3FN pero no BCNF:
– Ya
FD1 está en 3FN ya que la parte izquierda es
una superclave y FD2 también porque la
parte derecha es un atributo primo.
FD2 no está en BCNF ya que la parte
izquierda no es superclave, pero FD1 si está
en BCNF.
49
Forma normal Boyce-Codd (BCNF)
Supongamos que tenemos la siguiente relación con las siguientes dependencias:
– DF1: {Estudiante, Curso} → Profesor
– DF2: Profesor → Curso
{Estudiante, Curso} es una clave candidata
y clave primaria en este caso.
Esta relación esta en 3FN pero no en BCNF.
DF2 es la razón de no estar en
BCNF. 3 opciones para BCNF:
1. {Estudiante, Profesor} y {Estudiante Curso}
2. {Curso, Profesor} y {Curso, Estudiante}
3. {Profesor, Curso} y {Profesor, Estudiante}
Las 3 posibilidades rompen DF1,
pero la apropiada es la (3) ya que
no genera tuplas falsas tras una
concatenación (se puede
comprobar mediante el test del
join sin perdidas).
50
Forma normal Boyce-Codd (BCNF o 3.5NF)
Vemos que al normalizar a {Profesor, Curso} y {Profesor, Estudiante}, se
elimina la redundancia de Profesor → Curso en la tabla (ya que en la tabla la
clave primaria es {Estudiante, Curso}).
Podemos ver por ejemplo que Marcos y Bases de datos esta varias veces
repetida, y con la normalización se elimina esa redundancia.
Observar la FK y PK de las nuevas relaciones base.
51
Mas Ejemplos
52
¿Normalizar o Desnormalizar?
Hay veces que en una BD, por razones de optimización de rapidez en consultas, es
conveniente desnormalizar (aumentar la redundancia de las relaciones base).
Así mantener unas redundancias controladas en las tablas en algunos casos puede
ser bueno.
En algunos casos el tener la BD en niveles altos de normalización puede no
significar más eficiencia: mientras más normalización algunas veces menor
rendimiento (muchas concatenaciones para recuperar información).
Es decisión del analista del SGDB encontrar un balance entre la normalización y
desnormalización de la BD.
Para desnormalizar se requiere que la BD este en su nivel optimo de
normalización: 3FN o 3.5FN.
Para desnormalizar por ejemplo se pueden utilizar vistas materializadas.
53
Algoritmos de normalización
Comprobación de preservar dependencias en descomposiciones
Comprobación de join sin pérdida en descomposiciones
Comprobación de que un conjunto de atributos es una superclave
Propiedad 3NF, BCNF de relaciones
Descomposición de relaciones a 3NF, BCNF
– Siempre es posible descomponer a 2NF y 3NF sin pérdida de dependencias
– BCNF puede no ser posible sin perder alguna dependencia
54
Normalización 3NF (Cap 11)
3NF (R, F) /* Esta descomposición tiene “join” sin pérdida, y preserva las dependencias */
/* R es cualquier relación universal y F un conjunto de DFs de R */
1. D :=
2. G := cobertura mínima de F /* se localiza la cobertura mínima G para F */
/* Sacar a tablas aparte los atributos de todos los conjuntos de dependencias con la misma parte
izquierda */
3. for X Y G (cobertura mínima)
Añadir a D el esquema X { A1, A2, …, An }
donde X Ai son todas las dependencias sobre X en G
/* Si no ha salido ninguna tabla con la clave original completa, crear esa tabla */
4. Si ningún esquema de D contiene una clave de R, añadir a D un esquema con
una clave de R
5. Eliminar los esquemas redundantes de D (esquemas incluidos en otros)
55
Ejemplo de Normalización 3NF (algoritmo)
R={A B C D E F G}, DF1: B ACDE, DF2: E FG
3NF (R, F) /* Esta descomposición tiene “join” sin pérdida, y preserva las dependencias */
1. D :=
2. G := cobertura mínima de R /* se localiza la cobertura mínima G para R */
– Pasamos de B ACDE a B A, B C, B D, B E
– Pasamos de E FG a E F, E G, estas 6 DFs ya tienen sus partes derechas en su forma
simple
– Las partes izquierdas también están en su forma simple y por tanto no hay redundancia de
atributos
– No hay dependencias redundantes (DFs que se infieren de otra DFs) y por tanto la cobertura
mínima G
– G:={B A, B C, B D, B E, E F, E G}
56
Ejemplo de Normalización 3NF (algoritmo)
/* Sacar a tablas aparte los atributos de todos los conjuntos de dependencias con la misma parte
izquierda */
3. for X Y G (cobertura mínima)
Añadir a D el esquema X { A1, A2, …, An }
donde X Ai son todas las dependencias sobre X en G
Así que tenemos:
D:={B {A, C, D, E}, E {F, G}}= {{B, A, C, D, E}, {E, F, G}}={R1, R2}
/* Si no ha salido ninguna tabla con la clave original completa, crear esa tabla */
4. Si ningún esquema de D contiene una clave de R, añadir a D un esquema con una
clave de R (pero si hay una clave de R que es B, así que no es el caso).
5. Eliminar los esquemas redundantes de D (esquemas incluidos en otros) que no es el
caso.
6. La clave primaria de R1 es B y la clave primaria de R2 es E. El atributo E en R1 es
clave foránea (esta descomposición tiene “join” sin pérdida, y preserva las
dependencias).
57
Normalización BCNF (Cap 11)
BCNF (R, F) /* El algoritmo genera una descomposición que tiene join sin pérdida, pero no asegura
la preservación de todas las dependencias */
D := {R}
while D contiene una relación no BCNF
Q := elegir una dependencia funcional no BCNF en D
X Y := elegir una dependencia de F en Q que no cumple BCNF
Substituir Q en D por dos esquemas (Q – Y), (X Y) (es decir en cada pasada del
while se descompone un esquema Q que no esta en BCNF en dos esquemas que si son BCNF)
En otras palabras…
1. Sacar a tablas aparte todas las dependencias no BCNF de la relación original, pero
eliminando de ésta la parte derecha de las dependencias
2. Repetir el proceso sobre las relaciones que van saliendo
NOTA: En eliminando de ésta la parte derecha se pueden perder dependencias.
58
Ejemplo Normalización BCNF (algoritmo)
Este esquema tiene por claves candidatas: IdPropiedad y {NombreMunicipio,
NúmeroParcela}, se elige como primaria IdPropiedad.
R={A B C D}, y clave candidata {BC}, la dependencias funcionales son
DF1: A BCD, DF2: BC AD, DF3: D B
Solo hay una DF que no cumple BCNF que es DF3: D B
Substituir el esquema R por dos esquemas (R – B) y (D B)
R={R1, R2}={{A C D}, {D B}}
La clave primaria de R1 es A y de R2 es D. El atributo D en R1 es clave foránea.
Este algoritmo genera una descomposición que tiene join sin pérdida, pero no asegura la
preservación de todas las dependencias (por ejemplo la dependencia DF2 ya no existe).
.
59
Ejemplo Normalización BCNF (algoritmo)
Supongamos la siguiente relación
R={Estudiante, Curso, Profesor}
con las siguientes dependencias:
– DF1: {Estudiante, Curso} → Profesor
– DF2: Profesor → Curso
R={A, B, C}, DF1: AB C, DF2: C B
Solo hay una DF que no cumple BCNF que es DF2: C B
Substituir el esquema R por dos esquemas (R – B) y (C B)
R={R1, R2}={{A C}, {C B}}
La clave primaria de R1 es AC y de R2 es C. El atributo C en R1 es clave foránea.
Este algoritmo genera una descomposición que tiene join sin pérdida, pero no asegura la
preservación de todas las dependencias (por ejemplo la dependencia DF1 ya no existe).
.
60
Test para Join sin pérdida (Cap 11)
Test sencillo para una descomposición binaria:
Una descomposición binaria {R1, R2} de una relación R tiene join sin pérdida
respecto a un conjunto de dependencias F si
y solo si:
O bien (R1 R2 R1 – R2) se infiere de F (o está en F
+)
O bien (R1 R2 R2 – R1) se infiere de F (o está en F
+)
(Esto quiere decir más o menos o que {R1 R2} es la clave
foránea que me relaciona R1 con R2 o al contrario)
61
Ejemplo de Test para Join sin pérdida
R={Estudiante, Curso, Profesor}
con las siguientes dependencias:
– DF1: {Estudiante, Curso} → Profesor
– DF2: Profesor → Curso
R={A, B, C}, DF1: AB C, DF2: C B
F={DF1, DF2}
Según el ejemplo anterior, la única descomposición sin perdidas es: R={R1, R2}=
{{A C}, {C B}}
O bien se infiere R1 R2 (= C) R1 – R2 (= A) de F
O bien se infiere R1 R2 (= C) R2 – R1 (= B) de F
En este caso la segunda C B
Luego como sabíamos R1 y R2 producen un NJ sin perdida
DIFERENCIA: R-S es
una relación que incluye
todas los aributos que
están en R pero no
están en S.
62
Ejemplo de Test para Join sin pérdida
No obstante para la descomposición binaria : R={R1, R2}={{A C}, {A B}}
O bien se infiere R1 R2 (= A) R1 – R2 (= C) de F
O bien se infiere R1 R2 (= A) R2 – R1 (= B) de F
En este caso ni A C, ni A B se infieren de F, por lo tanto R1 y R2 NO producen
un NJ sin perdida y se tendrían tuplas falsas como resultado.
Así para la descomposición binaria : R={R1, R2}={{B C}, {A B}}
O bien se infiere R1 R2 (= B) R1 – R2 (= C) de F
O bien se infiere R1 R2 (= B) R2 – R1 (= A) de F
En este caso ni B C, ni B A se infieren de F, por lo tanto R1 y R2 NO producen
tampoco un NJ sin perdida y se tendrían tuplas falsas como resultado.
6-CRyAR-EDAT-121.pdf
Cálculo y Álgebra Relacional
(Cap 6 - Elmasri 5ª edición)
2
Introducción
Vamos a tratar los dos lenguajes formales del modelo relacional.
– Cálculo relacional
– Álgebra relacional
El modelo de datos debe incluir un conjunto de operaciones para
manipular la BD.
Estás operaciones permiten al usuario especificar las peticiones
fundamentales de recuperación de información en la BD.
El resultado de una recuperación es una nueva relación, y estas
relaciones se pueden manipular posteriormente.
El conjunto de operaciones básicas del modelo relacional es lo que
se denomina álgebra relacional (AR).
El AR proporciona el fundamento formal para las operaciones del
modelo relacional, y además es la base para la implementación y
optimización de las consultas en un sistema de gestión de BD.
3
Introducción
El cálculo relacional (CR) ofrece una notación declarativa de alto
nivel para especificar las consultas relacionales.
En una expresión de cálculo no existe un orden de las operaciones
para recuperar los resultados de la consulta: la expresión solo
especifica la información que el resultado debería contener.
Por tanto el CR es un formalismo declarativo, al contrario del AR
que es procedural (especifica el conjunto de operaciones en el orden
adecuado para recuperar los resultados de la consulta).
Así tenemos dos tipos de formalismos para expresar operaciones de
consulta sobre una BD en el modelo relacional, CR y AR.
Estos dos formalismos son diferentes pero lógicamente
equivalentes:
– Toda expresión de cálculo se puede expresar en álgebra y viceversa.
– Es decir, permiten expresar las mismas consultas.
4
Introducción
Un lenguaje de consulta es relacionalmente completo si permite expresar
cualquier consulta del cálculo relacional.
La traducción de AR a SQL es relativamente inmediata.
Los motores de SQL basan su representación interna en consultas a través
de AR.
Así el AR se utiliza con fines más prácticos, en principio es más
manejable que el SQL para diseñar consultas complejas.
La traducción de CR a SQL no es trivial.
CR es más adecuado para establecer y verificar propiedades formales,
la consistencia de los modelos relacionales y sus formalismo.
Introducido por Edgar Frank Codd, para operar en el contexto del modelo
relacional.
La creación original del modelo relacional se fundamentó en el cálculo
–interesa entenderlo para una comprensión más profunda del modelo
relacional y el fundamento de la tecnología de bases de datos y otros
lenguajes de consulta de BDs como QBE (desarrollado por IBM).
5
Introducción
QBE: (Query By Example - Consulta por ejemplo).
Es un método de consulta en base de datos relacionales. Fue ideado por
Moshé M. Zloof en el IBM Research a mediados de los 70, en paralelo al
desarrollo de SQL.
Este sistema nos permite que la persona que genera la búsqueda o la
aplicación que la realice pueda proporcionar información sobre aquello
que está buscando en la base de datos.
Fue el primer lenguaje de consulta gráfico, que utiliza tablas visuales
donde el usuario puede insertar comandos, elementos de ejemplos y
condiciones.
QBE está basado en la idea de DRC (Domain relational calculus), que es
un cálculo que fue introducido por Michel Lacroix y Alain Pirotte como un
lenguaje de consultas declarativo de base de datos para el modelo
relacional.
Más información en: http://es.wikipedia.org/wiki/Búsqueda_mediante_ejemplo
Esto es interesante por Page Rank, es QBE con texto: consiste en dar al
sistema cualquier información y que éste devuelva entradas relacionas con
el tema
http://es.wikipedia.org/wiki/Búsqueda_mediante_ejemplo
https://es.wikipedia.org/wiki/PageRank
6
Cálculo Relacional
El CR es un lenguaje formal basado en una rama de la lógica
matemática llamada cálculo de predicado de primer orden.
El CR puede actuar sobre tuplas o dominios (diferentes tipos de
variables):
– CRT
– CRD
El CR de tupla opera directamente sobre las tuplas de una relación,
las variables utilizadas en la fórmulas operan sobre tuplas.
En el CR de dominio las variables usadas en las fórmulas operan
sobre valores individuales de los dominios de los atributos.
La diferencia es esencialmente de notación, pero son prácticamente
equivalentes.
7
Cálculo Relacional de Tupla
El CR de tupla está basado en la especificación de un número de
variables de tuplas.
Cada especificación suele aplicarse sobre una relación base de datos
en particular:
– Es decir la variable podría tomar su valor de cualquier tupla individual
de esa relación base
Un consulta de CR sencilla tiene la siguiente forma { t | cond(t) }:
– t representa una variable de tupla
– cond(t) es una expresión condicional
– La expresión representa (literalmente) un conjunto de tuplas que
cumplen la condición
El resultado es el conjunto de todas la tuplas t que satisfacen la
condición cond(t).
8
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
9
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
10
Cálculo Relacional de Tupla: Variables
de Tupla y Relaciones de Rango
Por ejemplo para localizar todos lo empleados cuyo salario es superior a
50000€ podemos escribir la siguiente expresión:
– { t | EMPLEADO(t) AND t.sueldo >50000 }
– La condición EMPLEADO(t) especifica que la relación de rango de la variable
de tupla t es EMPLEADO.
– Cada EMPLEADO t que satisface la condición t.sueldo>50000 será recuperada.
– t.Sueldo hace referencia al atributo Sueldo de la variable de tupla t.
La consulta anterior recupera todos los valores del atributo de cada tupla. Para
recuperar solo algunos atributos (Nombre y Apellido1):
– { t.Nombre, t.Apellido1 | EMPLEADO(t) AND t.sueldo >50000 }
Por ejemplo para localizar todos los vuelos con origen en Madrid
– { t | Vuelo(t) and t.origen = ‘MAD’ }
– La condición Vuelo(t) especifica que la relación de rango de la variable de tupla t
es Vuelo.
– Cada Vuelo t que satisface la condición t.origen=‘MAD’ será recuperada.
–
t.origen hace referencia al atributo origen de la variable de tupla t.
11
Cálculo Relacional de Tupla: Consulta
General
Informalmente tenemos que suministrar la siguiente información en
una expresión de cálculo de tupla:
– Para cada variable de tupla t, la relación de rango R de t, R(t).
– Una condición para seleccionar combinaciones de tuplas particulares.
– El conjunto de atributos a recuperar (atributos solicitados).
Por ejemplo recuperar la fecha de nacimiento y la dirección del
empleado (o empleados) cuyo nombre sea José Peréz Peréz:
– { t.FechaNac, t.Dirección | EMPLEADO(t) AND t.Nombre =‘José’
AND t.Apellido1=‘Pérez’ AND t.Apellido2=‘Pérez’}
Pero se pueden formular consultas más elaboradas
Vamos a ver la forma general de una consulta de CR:
{ variables | condición }
12
Cálculo Relacional de Tupla:
Expresiones y Fórmulas
{ t1 . Aj, t2 . Ak, …, tn . Am | COND(t1, t2, …, tn, tn+1, tn+2, …, tn+m)}
– Donde t1, t2, …, tn, tn+1, tn+2, …, tn+m,son variables de tupla.
– Aj es un atributo de la relación a la que engloba ti
– Y COND es una condición o fórmula.
Una fórmula está compuesta por alguno de los siguientes elementos de
cálculo más pequeños (llamados átomos):
1. Un átomo de la forma R(ti), donde R es el nombre de una relación y ti es
una variable de tupla. Esta fórmula identifica el ámbito de la variable tupla
ti como la relación cuyo nombre es R.
2. Un átomo de la forma ti . A op tj . B, donde op es uno de los operadores de
comparación del conjunto {=,<, ≤, >, ≥, ≠}, ti y tj son variables de la
tupla, y A y B son atributos de las relaciones ti y tj a las que,
respectivamente, engloban.
3. Un átomo de la forma anterior pero con uno de los operandos siendo una
constante y no una variable.
13
Cada uno de los átomos anteriores se evalúa como VERDADERO o
FALSO para una combinación específica de tuplas (veracidad del átomo).
Mediante estas fórmulas compuestas por átomos queremos saber si los datos
almacenados en las BDs cumplen un cierta condición o no, para recuperar
esa información.
Así por ejemplo para los átomos de la forma R(t), si t está asignada a una
tupla que es miembro de la relación R especificada, ese átomo se evalúa
como VERDADERO, en cualquier otro caso FALSO.
– Ej: EMPLEADO(t), si t es una tupla que pertenece a empleado este átomo se
evalúa como VERDADERO.
Los átomos de tipo 2 o 3, si las variables de tupla están asignadas a tuplas en
las que los valores de los atributos especificados de las mismas satisfacen la
condición entonces las fórmulas se evalúan como verdaderas.
En general una fórmula (COND) está compuesta por uno o varios átomos
conectados mediante operadores lógicos, AND, OR y NOT.
Cálculo Relacional de Tupla:
Expresiones y Fórmulas
14
Las relaciones entre fórmulas y átomos cumple las siguientes reglas:
Regla1: Cada átomo es una fórmula.
Regla2: Si F1 y F2 son fórmulas entones también lo son (F1 AND F2),
(F1 OR F2), NOT(F1) y NOT(F2). Los valores de veracidad para estas
fórmulas se derivan de los obtenidos para F1 y F2 de la siguiente forma:
– (F1 AND F2) es VERDADERO si F1 y F2 lo son. En cualquier otro caso
es FALSO.
– (F1 OR F2) es FALSO si F1 y F2 lo son. En cualquier otro caso es
VERDADERO.
– NOT(F1) es VERDADERO si F1 es FALSO. En cualquier otro caso es
FALSO.
– NOT(F2) es VERDADERO si F2 es FALSO. En cualquier otro caso es
FALSO.
Cálculo Relacional de Tupla:
Expresiones y Fórmulas
15
Existen otros símbolos especiales llamados cuantificadores que pueden aparecer en
la fórmulas: El universal () y el existencial ().
Conceptos de variables de tupla libre y tupla acotada de una fórmula: una variable
de tupla t es ligada o acotada si está cuantificada (aparece en una clausula t o t),
en cualquier otro caso es libre. Las reglas que determinan si es libre o acotada:
– Una variable de tupla en una fórmula F que es un átomo es libre en F.
– Una variable de tupla t es libre o acotada en una fórmula construida mediante
conexiones lógicas {(F1 AND F2), (F1 OR F2), NOT(F1) y NOT(F2)} dependiendo de
su estado en F1 o F2. (Nota: En la dos primeras si F1 es libre y F2 acotada, o viceversa,
el resultado del AND es acotado y el resultado del OR es libre).
– Todas las ocurrencias libres de una variable de tupla t en F son acotadas en una fórmula
F’ de la forma: F’=(t)F o F’=(t)F. La variable de tupla es acotada al cuantificador
especificado en F’ (notar que los cuantificadores anteriores solo hacen referencia a t).
• F1: d.NombreDpto=‘investigacion’
• F2: (t)(d.NumeroDpto=t.Dno)
• F3: (d)(d.DniDirector=‘333445555’)
La variable de tupla d es libre en F1 y F2, mientras que es acotada en F3 (por el
cuantificador ). La variable t es acotada respecto al cuantificador () en F2.
Cálculo Relacional de Tupla:
Cuantificadores Existencial y Universal
16
Los valores de comprobación de las fórmulas con estos cuantificadores están
descritos por las siguientes reglas:
– Regla3: Si F es una fórmula , entonces F’=(t)F también lo es, donde t es una
variable de tupla. F’ es VERDADERO si F se evalúa como tal en alguna (al
menos una) tupla asignada a las ocurrencias libres de t en F. En cualquier otro
caso F’ es FALSO.
– Regla4: Si F es una fórmula , entonces F’=( t)F también lo es, donde t es una
variable de tupla. F’ es VERDADERO si F se evalúa como tal para cada tupla
asignada a las ocurrencias libres de t en F. En cualquier otro caso F’ es FALSO.
se dice que es un cuantificador existencial porque una fórmula (t)F es
VERDADERO si existe alguna tupla que haga que F sea VERDADERO.
Para el cuantificador universal, ( t)F es VERDADERO si cada posible tupla
que puede asignarse a las ocurrencias libres de t en F es sustituida por t, y F es
VERDADERO para cada una de estas sustituciones. Recibe el nombre de
universal (o para todos los cuantificadores) porque cada tupla del universo de
tuplas debe hacer que F sea VERDADERO para la fórmula cuantificada
también lo sea.
Cálculo Relacional de Tupla:
Cuantificadores Existencial y Universal
17
Forma General: { t | F(t) } conjunto de tuplas t tal que F(t) es verdadero
Relación de intervalo (rango de la tabla A): { t | A(t)} conjunto de tuplas que
pertenecen a la relación A
Proyección: { t.A1, t.A2 | A(t) } extraer los atributos de una tabla
Selección: { t | A(t) and t.A1 >2000 } seccionar tuplas de una tabla
Unión: { t | A(t) or B(t)} (es necesario la compatibilidad de relaciones)
Intersección: { t | A(t) and B(t)} (es necesario la compatibilidad de relaciones)
Resta: { t | A(t) and not B(t)} (es necesario la compatibilidad de relaciones)
Producto cartesiano: { t, s | A(t) and B(s)}
Join: { t, s | A(t) and B(s) and t.A1<s.B1 }
Join: { t | A(t) and ((s)(B(s) and t.A1<s.B1 ))}
Equi-Join: { t, s | A(t) and B(s) and t.A1=s.B1 }
Equi-Join: { t | A(t) and ((s)(B(s) and t.A1=s.B1 ))}
Cálculo Relacional de Tupla:
Operaciones Básicas-Repaso
18
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
19
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
20
Consulta1: Liste el nombre y la dirección de todos los empleados que trabajan para
el departamento ‘Investigación’:
{t.Nombre, t. Apellido1, t.Dirección | EMPLEADO(t) AND (d)
(DEPARTAMENTO(d) AND
d.NombreDpto=‘Investigación’ AND
d.NumeroDpto=t.Dno) }
– En cálculo relacional la únicas variables de tupla que son libres aparecen a la izquierda de la barra
(|).
– En este caso, t es la única variable de tupla libre.
– Esta variable es acotada sucesivamente para cada tupla. Esto quiere decir que si la tupla satisface
las condiciones de la consulta,
esta se recupera del modo que se especifica.
– La variable de tupla d es acotada al cuatificador existencial.
– Las condiciones EMPLEADO(t) y DEPARTAMENTO(d) especifican las relaciones de rango para
las variables de tupla t y d.
– La condición d.NombreDpto=‘Investigación’ es una condición de selección (operación de
selección del Álgebra Relacional).
– La condición d.NumeroDpto=t.Dno es una condición de concatenación (operación básica del
Álgebra Relacional).
Cálculo Relacional de Tupla: Ejemplos
21
Consulta equivalente en SQL:
SELECT Nombre, Apellido1, Dirección
FROM (EMPLEADO JOIN DEPARTAMENTO ON Dno= NumeroDpto)
WHERE NombreDpto=‘Investigación’;
También podemos hacerla como:
SELECT Nombre, Apellido1, Dirección
FROM EMPLEADO, DEPARTAMENTO
WHERE NombreDpto=‘Investigación’ AND NumeroDpto =Dno;
O también de con el “join” explicito:
SELECT Nombre, Apellido1, Dirección
FROM EMPLEADO INNER JOIN DEPARTAMENTO ON NumeroDpto =Dno
WHERE NombreDpto=‘Investigación’ ;
Cálculo Relacional de Tupla: Ejemplos
Equivalente en SQL
22
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
23
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
24
Consulta 2: Por cada proyecto ubicado en ‘Gijón’, obtenga el número
de departamento que lo gestiona, los apellidos, la fecha de nacimiento y
la dirección del director del mismo:
{p.NumProyecto, p. NumDptoProyecto, e.Apellido1, e.FechaNac,
e.Dirección | PROYECTO(p) AND EMPLEADO(e) AND
p.UbicacionProyecto=‘Gijón’
AND ((d)(DEPARTAMENTO(d)
AND p.NumDptoProyecto=d.NumeroDpto AND
d.DniDirector=e.Dni))}
– Las variables de tuplas p y e son libres. La variable d es acotada al cuantificador
existencial.
– La condición de la consulta se evalúa para cada combinación de tuplas asignadas a
‘p’ y a ‘e’, seleccionando de esas combinaciones posibles las que son acotadas por
las fórmulas de la condición.
Cálculo Relacional de Tupla: Ejemplos
25
Consulta equivalente en SQL:
SELECT NumProyecto, NumDptoProyecto, Apellido1, Dirección, FechaNac
FROM PROYECTO, DEPARTAMENTO, EMPLEADO
WHERE NumDptoProyecto =NumeroDpto AND DniDirector=Dni AND
UbicacionProyecto=‘Gijon’;
Cálculo Relacional de Tupla: Ejemplos
Equivalente en SQL
26
Ojo que distintas variables de tupla de una consulta pueden alcanzar la
misma relación, por ejemplo la siguiente consulta.
Consulta 3: Por cada empleado, recuperar el nombre y primer apellido del
empleado, y el nombre y primer apellido de su supervisor inmediato.
{e.Nombre, e.Apellido1, s.Nombre, s.Apellido1 | EMPLEADO(e) AND
EMPLEADO(s) AND e.SuperDni=s.Dni}
Recordar el mismo ejemplo en SQL:
SELECT E.Nombre, E.Apellido1, S.Nombre, S.Apellido1
FROM EMPLEADO AS E, EMPLEADO AS S
WHERE E.SuperDni=S.Dni;
La tabla E se va a utilizar como tabla para extraer la información de los
empleados que son supervisados. La tabla S se utiliza para extraer la
información de los empleados supervisores.
Darse cuenta que las tablas E y S son copias de la tabla empleado.
Cálculo Relacional de Tupla: Ejemplos
27
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
28
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
29
Consulta 4: Enumere el nombre de todos los empleados que trabajan en algún
proyecto controlado por el departamento 5:
{e.Apellido1, e. Nombre | EMPLEADO(e) AND ((x) (w)
(PROYECTO(x) AND TRABAJA_EN(w) AND x.NumDptoProyecto=5
AND w.DniEmpleado=e.Dni AND x.NumProyecto=w.NumProy)))}
En SQL:
SELECT DISTINCT "EMPLEADO"."Nombre"
FROM "TRABAJA_EN", "EMPLEADO"
WHERE "EMPLEADO"."Dni"="TRABAJA_EN"."DniEmpleado" AND
"TRABAJA_EN"."NumProy" IN
(SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO"
WHERE "PROYECTO"."NumDptoProyecto"='5');
Es una consulta anidada. El segundo SELECT me proporciona los números de
proyectos que controla el departamento 5. Estos número se les pasa a la primera
consulta.
¿PODEMOS QUITAR EN ESTE CASO EL ANIDAMIENTO?
Cálculo Relacional de Tupla: Ejemplos
30
Cálculo Relacional de Tupla: Ejemplos
RECORDTORIO: Enumere el nombre de todos los empleados que trabajan
en algún proyecto controlado por el departamento 5.
El segundo SELECT me proporciona los números de proyecto que controla el
departamento 5.
31
Consulta 4: Enumere el nombre de todos los empleados que trabajan en algún
proyecto controlado por el departamento 5:
{e.Apellido1, e. Nombre | EMPLEADO(e) AND ((x) (w)
(PROYECTO(x) AND TRABAJA_EN(w) AND x.NumDptoProyecto=5
AND w.DniEmpleado=e.Dni AND x.NumProyecto=w.NumProy))}}
En SQL pero sin SELECT anidado:
SELECT DISTINCT "EMPLEADO"."Nombre"
FROM "TRABAJA_EN", "EMPLEADO", "PROYECTO"
WHERE "EMPLEADO"."Dni"="TRABAJA_EN"."DniEmpleado" AND
"TRABAJA_EN"."NumProy"="PROYECTO"."NumProyecto" AND
"PROYECTO"."NumDptoProyecto"='5'
Cálculo Relacional de Tupla: Ejemplos
32
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
33
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
34
Consulta 5: Obtener una lista de los números de los proyectos que impliquen a
cualquier empleado cuyo primer apellido sea ‘Campos’, independientemente de
que sean trabajadores o directores del departamento que gestiona dicho
proyecto:
{p.NumProyecto | PROYECTO(p) AND (((e) (w) EMPLEADO(e)
AND TRABAJA_EN(w) w.NumProy=p.NumProyecto
AND e.Apellido1=‘Campos’ AND e.Dni=w.DniEmpleado))
OR
((m) (d) EMPLEADO(m) AND DEPARTAMENTO(d)
AND p.NumDptoProyecto=d.NumeroDpto AND d.DniDirector=m.Dni
AND m.Apellido1=‘Campos’))) }
Cálculo Relacional de Tupla: Ejemplos
Trabajadores
Directores
35
En SQL la Consulta 5: Obtener una lista de los números de los proyectos que
impliquen a cualquier empleado cuyo primer apellido sea ‘Campos’,
independientemente de que sean trabajadores o directores del departamento que
gestiona dicho proyecto:
SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO"
WHERE "PROYECTO"."NumProyecto" IN
(SELECT "PROYECTO"."NumProyecto"
FROM "PROYECTO", "DEPARTAMENTO", "EMPLEADO"
WHERE "PROYECTO"."NumDptoProyecto"="DEPARTAMENTO"."NumeroDpto" AND
"DEPARTAMENTO"."DniDirector"="EMPLEADO"."Dni" AND "Apellido1"='Campos')
OR
"PROYECTO"."NumProyecto" IN
(SELECT "TRABAJA_EN"."NumProy"
FROM "TRABAJA_EN", "EMPLEADO"
WHERE "TRABAJA_EN"."DniEmpleado"="EMPLEADO"."Dni" AND
"Apellido1"='Campos');
Cálculo Relacional de Tupla: Ejemplos
36
Cálculo Relacional de Tupla: Ejemplos
RECORDATORIO: Obtener una lista de los números de los proyectos que
impliquen a cualquier empleado cuyo primer apellido sea ‘Campos’,
independientemente de que sean trabajadores o directores del departamento que
gestiona dicho proyecto:
Directores
Empleados
37
Expresiones no seguras
Expresiones no seguras
– Devuelven infinitas tuplas:
Ejemplo: { t | NOT EMPLEADO(t) }
– Recupera todas la tuplas del universo que no son tuplas empleado.
– Solución: evitarlas!
– La caracterización de consultas seguras y no seguras es compleja –no
profundizaremos en ello en este curso.
– Las equivalencias entre los diferentes formalismos (cálculo, álgebra, cálculo
de tuplas vs. de dominio) se dan con salvedad de las expresiones no seguras.
38
Álgebra Relacional
Se considera como una parte del modelo de datos relacional.
Las consultas se hacen mediante operaciones de manera procedural
(al contrario del CR que es declarativo como ya hemos visto).
Al igual que en CR una consulta en AR da como resultado el
conjuntos de tuplas que cumplen ciertas
condiciones que se desean
especificadas por la consulta.
Existen varios tipos de operaciones:
– Operaciones propias: desarrolladas propiamente para las bases de
datos relacionales (Unarias o Binarias):
• Selección, Proyección, Renombrado, Concatenación o Combinación.
– Operaciones entre conjuntos:
• Unión, Intersección, Diferencia de conjuntos, Producto Cartesiano
– Operaciones extendidas: operaciones que se necesitan además de las
anteriores:
• Funciones Agregadas y Concatenación y Unión adicionales.
39
Álgebra Relacional: Operaciones
Unarias-SELECT
La operación unaria SELECCIÓN se emplea para seccionar un conjunto
de tuplas de una relación que satisface una condición de selección.
Puede ser visto como una partición horizontal de la relación: solo la
tuplas que cumplen la condición son seleccionadas, es resto se descarta.
Ejemplos:
– Dno=4 (EMPLEADO): selecciona las tuplas de empleado cuyo
departamento es 4.
– Sueldo>30000 (EMPLEADO): selecciona las tuplas de empleado
cuyo salario es mayor de 30000 €.
En general: <condición de selección> (R)
El símbolo representa la operación de selección propiamente dicha.
La condición de selección es una expresión lógica (o booleana)
especificada sobre los atributos de la relación R.
40
Álgebra Relacional: Operaciones
Unarias-SELECT
R en general puede ser una operación de AR, cuyo resultado es una
relación (una tabla o conjunto de tuplas).
Obviamente la R más sencilla es el nombre de una relación de la
BDs, como hemos visto en los ejemplos anteriores.
El resultado de la operación selección tiene los mismos atributos de la
relación R.
La condición de selección puede estar compuesta por las diferentes
clausulas:
– A op B, o A op c, donde A y B son atributos de una relación , c una
constante y op es un operador comparación {=,<, ≤, >, ≥, ≠}
Estás clausulas pueden estar conectadas por operadores lógicos AND,
OR y NOT.
NOTA: Las condiciones son iguales que las de CR pero no se aplican a
variables de tupla, no se utilizan condiciones de esquema indicando la
relación de rango de la variable, y no se utilizan los cuantificadores
existencial y universal.
41
Álgebra Relacional: Operaciones
Unarias-SELECT
Ejemplos: (Dno=4 AND Sueldo>25000) OR (Dno=5 AND Sueldo>30000) (EMPLEADO):
secciona las tuplas de la relación empleado que trabajan en el
departamento 4 y ganan más de 25000€, o los que trabajan en el
departamento 5 y ganan más de 30000.
42
Álgebra Relacional: Operaciones
Unarias-SELECT
Toda operación de selección cumple:
– Los atributos de la selección son los mismos que los de la
relación.
– El número de tuplas resultante es siempre menor o igual que el
número de tuplas de la relación.
– La operación selección es conmutativa:
• condición1 (condición2 (R)) = concición2 (condición1 (R)) = condición1
and condición2 (R)
• Esto se puede aplicar a una cascada de condiciones
En general la operación selección es una elección de filas de las
relaciones (partición horizontal), al contrario de la operación que vamos
a estudiar a continuación de PROYECCIÓN.
Está segunda operación unaria selecciona ciertas columnas de una tabla.
Es por que la operación PROYECCIÓN se suele ver como una
partición vertical de la tabla sobre la que opera.
43
Álgebra Relacional: Operaciones
Unarias-PROJECT
Está operación selecciona ciertas columnas de una tabla.
Es por que la operación PROYECCIÓN se suele ver como una partición vertical de
la tabla sobre la que opera.
Ejemplo:
– Apellido1, Nombre, Sueldo (EMPLEADO): selecciona los atributos Apellido1,
Nombre, Sueldo de la tabla EMPLEADO.
44
Álgebra Relacional: Operaciones
Unarias-PROJECT
En general: <lista de atributos> (R)
El símbolo representa la operación de proyección propiamente dicha.
La lista de atributos contiene a la lista de campos de la relación que queremos
extraer de la relación R.
El orden de los atributos en la nueva relación es el orden especificado en la
proyección.
Si la lista de atributos no incluye ninguna clave candidata de R, entonces es posible
que se obtuviesen tuplas duplicadas.
La operación proyección elimina cualquier tupla que este duplicada, por lo
tanto se obtiene una relación válida en el modelo relacional.
Esto es lo que se conoce como eliminación de duplicados:
– Sexo, Sueldo (EMPLEADO): aunque formalmente existirían tuplas duplicadas (<‘M’,
25000>), estas se eliminan del resultado.
45
Álgebra Relacional: Operaciones Unarias-PROJECT
La eliminación de duplicados lleva implícitamente un proceso de ordenación para
detectar esos duplicados y eliminarlos (esto no sucede en SQL, DISTINCT).
En una operación de proyección siempre se cumple:
– El número de tuplas resultante es siempre menor o igual que el número de tuplas de la
relación (eliminación de duplicados). Es igual estrictamente cuando la lista tiene una
clave candidata de R.
– Propiedad de listas incluidas: lista1(lista2(R)) = lista1(R), siempre y cuando lista2
lista1, si esto no pasa la expresión es incorrecta.
– La operación proyección NO es conmutativa.
46
Álgebra Relacional: Operaciones
Unarias-RENAME
Al resultado de las operaciones anteriores no se les asigna ningún nombre.
Se pueden realizar todas las operaciones de AR una tras otra, anidando dichas
operaciones (sin tener que asignar nombres intermedios).
Podemos querer crear relaciones intermedias y asignar nombre a esas
relaciones intermedias.
Ejemplo: Recuperar el nombre, el primer apellido y el sueldo de todos los
empleados que trabajan en el departamento 5, para ello debemos aplicar
una selección y una proyección:
– Nombre, Apellido1, Sueldo (Dno=5 (EMPLEADO)): Mostrar la figura (a) siguiente.
– Podemos mostrar la secuencia de operaciones dando un nombre a cada una de
ellas:
• DEP5_EMPS Dno=5 (EMPLEADO)
• RESULTADO Nombre, Apellido1, Sueldo (DEP5_EMPS).
También podemos utilizar la técnica de RENOMBRAR los atributos en las
relaciones intermedias y resultantes.
Es muy útil con operaciones más complejas de unión y concatenación.
47
Álgebra Relacional: Operaciones
Unarias-RENAME
48
Álgebra Relacional: Operaciones
Unarias-RENAME
Para renombrar los atributos lo hacemos de la siguiente forma con el
ejemplo anterior (dos tipos de renombrado):
– TEMP Dno=5 (EMPLEADO) (operación selección)
– R(NuevoNombre, NuevoApellido, NuevoSueldo)
Nombre, Apellido1, Sueldo (TEMP) (operación proyección)
Mostrar la figura (b) anterior página.
En la operación selección no se renombran los atributos y por lo tanto
tienen el mismo nombre y en mismo orden.
En la operación proyección los atributos tienen los nombres
renombrados.
Así una operación de renombrado es un operador unario.
49
Álgebra Relacional: Operaciones
Unarias-RENAME
La forma genérica de una operación de renombrado aplicada a una
relación R de grado n puede ser:
– S(B1, B2, …, Bn) (R), S (R) , (B1, B2, …, Bn) (R)
Donde el símbolo representa la operación de renombrado.
S es el nombre de la nueva relación.
B1, B2,…, Bn son los nuevos atributos y R1, R2,…, Rn son los de R.
La primera expresión renombra tanto la relación como los atributos
(S(B1, B2, …, Bn)(R)).
La segunda expresión solo renombra la relación (S (R)).
La tercera solo los atributos ((B1, B2, …, Bn) (R)).
Si suponemos que los atributos de R son (A1, A2,…, An) por este orden
entonces cada Ai se renombra a Bi.
Para renombrar solo el atributo Ri de la relación R por Bi: Bi/Ri (R).
50
Álgebra Relacional: Operaciones Entre
Conjuntos (Binarias)
UNIÓN (UNION)
INTERSECCIÓN (INTERSECTION)
DIFERENCIA (MINUS)
PRODUCTO CARTESIANO o PRODUCTO CUZADO (CROSS
JOIN) (necesario para explicar bien la operación extendida de
concatenación posteriormente)
51
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
52
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
53
Álgebra Relacional: Operaciones Entre
Conjuntos (, , -)
Estas operaciones de AR son las correspondientes a la operativa
matemática sobre conjuntos.
Ejemplo de la operación UNIÓN: Recuperar los Documentos
Nacionales de Identidad de todos los empleados que, o bien trabajan en
el departamento 5 o bien supervisan a estos:
1. DEP5_EMPS Dno=5 (EMPLEADO)
2. RESULTADO1 Dni (DEP5_EMPS)
3. RESULTADO2(Dni) SuperDni (DEP5_EMPS)
4. RESULTADO RESULTADO1 RESULTADO2
– RESULTADO1 tiene el Dni de todos los empleados del departamento 5,
RESULTADO2 tiene el Dni de todos aquellos empleados que supervisan
directamente a los del primer grupo.
– La unión de ambos es el conjunto de las tuplas que están en
RESULTADO1 o están en RESULTADO2 o en ambas.
Mostrar siguiente figura.
54
Álgebra Relacional: Operaciones Entre
Conjuntos (, , -)
55
Álgebra Relacional: Operaciones Entre
Conjuntos (, , -)
La operaciones entre conjuntos sirven para combinar elementos entre conjuntos
como uno desee.
Todas las operaciones son binarias, ya que actúan sobre dos conjuntos de tuplas.
Concepto de compatibilidad de tuplas: dos relaciones R(A1, A2,…, An) y S(B1,
B2,…, Bn), se dice que son de unión compatible si ambas relaciones tienen el
mismo grado n y el dom(Ai)=dom(Bi) con i entre 1 y n.
Las operaciones unión, intersección y diferencia se tienen que aplicar entre
conjuntos de tuplas compatibles (en el producto cartesiano no es necesario).
Suponiendo R y S son dos conjuntos de tuplas compatibles las tres operaciones se
definen:
– UNIÓN: R S es una relación que incluye todas la tuplas que están en R o están en
S o en ambas R y S (obviamente no hay duplicados, “o”).
– INTERSECCIÓN: R S es una relación que incluye todas la tuplas que están en
R y S .
– DIFERENCIA: R-S es una relación que incluye todas la tuplas que están en R pero
no están en S.
56
Álgebra Relacional: Operaciones Entre
Conjuntos (, , -)
E P
E P E - P P - E
57
Álgebra Relacional: Operaciones Entre
Conjuntos (, , -), Propiedades
En todas las siguientes propiedades se suponen dos relaciones
compatibles o de unión compatible R(A1, A2,…, An) y S(B1, B2,…, Bn).
La unión e intersección son operaciones conmutativas y asociativas:
– R S= S R y R S = S R
– R (S T) = (R S) T y R (S T) = (R S) T
La operación menos no es conmutativa:
– R-S S-R (ver figura anterior)
La intercesión se puede poner en términos de la unión y diferencia:
– R S = R S- (R-S)-(S-R)
Siempre la salida de las operaciones tiene el mismo número de atributos
de los conjuntos.
min(|R|,|S|) |R S| |R| + |S|
|R S| min(|R|,|S|)
58
Álgebra Relacional: Operaciones Entre
Conjuntos (, , -), Propiedades
c (R) d (S) = c and d (R S)
c (R) d (R) = c or d (R)
59
Álgebra Relacional: Operaciones Entre
Conjuntos ()
Producto Cartesiano: Se trata también de una operación de conjuntos
binarios, aunque los operandos no tienen porque ser de unión compatible.
En general R(A1, A2,…, An) S(B1, B2,…, Bm) es una relación Q de grado
n+m atributos Q(A1, A2,…, An ,B1, B2,…, Bm).
En la relación Q resultante tiene una tupla por cada combinación de éstas (una
para R y otra para S).
Por tanto si R tiene nR tuplas y S tiene nS tuplas entonces R S tendrá nR * nS
tuplas.
Supongamos la siguiente secuencia de operaciones:
1. EMPLEADAS_FEMENINAS Sexo=‘M’ (EMPLEADO)
2. NOMBRE_EMPLEADOS Nombre, Apellido1, Dni(EMPLEADAS_FEMENINAS)
3. EMPLEADOS_SUBORDINADOS NOMBRE_EMPLEADOS SUBORDINADO
4. SUBORDINADOS_ACTUALES Dni=DniEmpleado (EMPLEADOS_SUBORDINADOS)
5. RESULTADO Nombre, Apellido1, NombreSubordinado(SUBORDINADOS_ACTUALES)
El producto cartesiano solo tiene sentido si se hace a continuación que
combine las relaciones componentes en la manera que deseamos.
60
Álgebra Relacional: Operaciones Entre
Conjuntos ()
61
Álgebra Relacional: Operaciones Entre
Conjuntos ()
62
Álgebra Relacional: Operaciones Entre
Conjuntos ()
El producto cartesiano crea tuplas de con los atributos combinados de ambas
relaciones.
Solo podemos hacer una selección de tuplas de las dos relaciones especificando una
condición de selección apropiada.
Esto es lo que veremos ahora como la operación de CONCATENACIÓN.
En el ejemplo anterior queremos combinar una tupla de empleada femenina solo con
la de sus subordinados particulares (es decir la tuplas de SUBORDINADO cuyos
valores de DniEmpleado coincidan con Dni de EMPLEADO).
63
Álgebra Relacional: Operaciones
Binarias-CONCATENACIÓN
El símbolo de la concatenación o JOIN es |><|, se emplea para combinar
tuplas relacionadas de dos relaciones en una sola.
Esta operación es fundamental ya que permite procesar relaciones entre
relaciones.
Supongamos que queremos recuperar el nombre del director de cada
departamento, para ello necesitamos combinar la tuplas departamento y
empleado a través de la operación concatenación:
1. DIRECTOR_DPTO DEPARTAMENTO |><| DniDirector=Dni EMPLEADO
2. RESULTADO NombreDpto, Apellido1, Nombre(DIRECTOR_DPTO)
64
Álgebra Relacional: Operaciones
Binarias-CONCATENACIÓN
La concatenación, como ya vimos puede ser enunciada como un producto cartesiano
más una selección posterior (|><| = más ).
Consideremos el ejemplo de hace unas transparencias:
1. EMPLEADOS_SUBORDINADOS NOMBRE_EMPLEADOS SUBORDINADO
2. SUBORDINADOS_ACTUALES Dni=DniEmpleado (EMPLEADOS_SUBORDINADOS)
Estas dos operaciones se pueden sustituir por la operación concatenación:
– SUBORDINADOS_ACTUALES NOMBRES_EMPLEADOS |><| Dni=DniEmpleado SUBORDINADO
La forma general de la concatenación en dos relaciones R(A1, A2,…, An) y S(B1,
B2,…, Bm) es:
R |><| <condición de conexión> S
El resultado de la concatenación es una relación Q de n+m atributos Q(A1, A2,…,
An,B1, B2,…, Bm), por este orden que tiene una tupla por cada combinación de éstas
(una para R y otra para S), siempre que dicha combinación satisfaga la condición de
combinación.
Aquí radica la diferencia principal entre PRODUCTO CARTESIANO y
CONCATENACIÓN.
65
Álgebra Relacional: Operaciones
Binarias-CONCATENACIÓN
Una condición general de conexión la podemos expresar como:
– <condición> AND <condición> AND … AND <condición>
Solo aparecen la tuplas cuyas condiciones de conexión se evalúan
como verdaderas.
Donde cada <condición > es de la forma Ai Bj, con Ai siendo un
atributo de la relación R y Bi es un atributo de la relación S y un
operador de comparación {=,<, ≤, >, ≥, ≠}.
Una concatenación con una condición de conexión de este tipo recibe
el nombre de ASOCIACIÓN (THETA JOIN).
Hay que observar que en una ASOCIACIÓN las tuplas cuyos atributos
de conexión son NULL o aquellas cuya condición de conexión se
evalúa como falsa no aparecen en el resultado (la concatenación no
preserva necesariamente toda la información de las relaciones
participantes).
66
Álgebra Relacional: CONCATENACIÓN
EQUIJOIN y NATURAL
El uso más simple de las concatenaciones supone el uso de condiciones de
concatenación con solo comparaciones de igualdad: EQUIJOIN
En EQUIJOIN siempre tenemos atributos que cuentan con valores idénticos en
cada tupla (ver en la figura anterior Dni y DniDirector).
Ya que los valores idénticos
de los atributos en las tuplas son innecesarios, se creó
una nueva operación llamada CONCATENACIÓN NATURAL (NATURAL
JOIN), cuyo símbolo es |><| (o en el libro el símbolo *).
El natural join se deshace del segundo atributo superfluo en una condición
EQUIJOIN (elimina así la redundancia que se genera por un JOIN).
La concatenación equijoin estándar precisa que los dos atributos (o dos
combinaciones de atributos idénticas en cada una de las tablas) de conexión tengan
el mismo nombre en las dos relaciones.
En el siguiente ejemplo primero renombramos un atributo NumeroDpto
NumeroDptoProyecto y luego aplicamos la concatenación natural:
– PROYECTO_DPTO PROYECTO |><| S(NombreDpto, NumDptoProyecto, DniDirector,
FechaIncresoDirector)(DEPARTAMENTO).
67
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
68
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
69
Álgebra Relacional: CONCATENACIÓN
EQUIJOIN y NATURAL
Este ejemplo anterior:
– PROYECTO_DPTO PROYECTO |><| S(NombreDpto, NumDptoProyecto, DniDirector,
FechaIncresoDirector)(DEPARTAMENTO).
Se puede realizar en dos pasos creando una tabla intermedia:
– DEPT S(NombreDpto, NumDptoProyecto, DniDirector, FechaIncresoDirector)(DEPARTAMENTO).
– PROYECTO_DPTO PROYECTO |><| DEPT.
El atributo NumDptoProyecto recibe el nombre de atributo de conexión del natural
join (notar que solo mantiene uno de los atributos de conexión en la tabla
resultante).
70
Álgebra Relacional: CONCATENACIÓN
EQUIJOIN y NATURAL
Si los atributos donde se va a realizar la concatenación natural ya tienen el mismo
nombre ya o hace falta hacer el renombramiento de los atributos, como por ejemplo
en DEPARTAMENTO y LOCALIZACIONES_DPTO el atributo NumeroDpto es
el mismo en la dos tablas:
– LOC_DPTO DEPARTAMENTO |><| LOCALIZACIONES_DPTO.
71
Álgebra Relacional: CONCATENACIÓN
EQUIJOIN y NATURAL
La concatenación se puede realizar entre diversas tablas:
– ((PROYECTO |><| NumDptoProyecto=NumeroDpto DEPARTAMENTO)
|><| DniDirector=Dni EMPLEADO).
72
Álgebra Relacional: Operaciones
Adicionales (Proyección Generalizada)
Amplia la posibilidades de proyección original: permite funciones de atributos en la
lista de proyección:
F1, F2,…..,Fn(R), donde F1, F2, …, Fn son funciones sobre los atributos de la relación
R.
Supongamos la relación EMPLEADO (Dni, Sueldo, Deducción, Antiguedad) y
supongamos que queremos sacar un informe en el cual se muestre:
– SalarioNeto= Sueldo – Deducción
– Gratificaciones = 2000 * Antigüedad
– Impuestos = 0.25 * Sueldo
Así se puede utilizar una proyección generalizada para sacar este informe:
– INFORME (Dni, SalarioNeto, Gratificaciones, Impuestos) Dni, Sueldo – Deducción, 2000 * Antigüedad , 0.25 *
Sueldo (EMPLEADO)).
73
Álgebra Relacional: Operaciones
Adicionales (Agregación y Agrupamiento)
Se pueden hacer cálculos con los atributos de las relaciones a través de funciones
matemáticas de agregación:
– SUMA (SUM)
– MEDIA (AVERAGE)
– MÁXIMO (MAXIMUM)
– MÍNIMO (MINIMUN)
– CONTAR (COUNT)
Otro tipo de función es la agrupación de atributos de una relación para luego
posteriormente aplicar alguna de las funciones de agregación anteriores.
La función agregada más general AGRUPA tuplas por el valor de ciertos atributos,
y sobre esos grupos puede aplicar operaciones de AGREGACIÓN.
Definimos la función agregada (script F) por el símbolo :
– <atributos de agrupamiento> <lista de funciones> (R)
Los <atributos de agrupamiento> es una lista de atributos de la relación R, y <lista de
funciones> es una lista de parejas (<función>, <atributo>).
En cada una de las parejas <función> puede ser cualquiera de las funciones de agregación
anteriores, y <atributo> es un atributo de la relación especificada por R.
74
Álgebra Relacional: Operaciones
Adicionales (Agregación y Agrupamiento)
Por ejemplo recuperar cada número de departamento, el número de
empleados del mismo, y la media de sueldos, renombrando los atributos
resultantes:
R(Dno, NumEmpleados, MediaSueldos) (Dno COUNT Dni, AVERAGE Sueldo (EMPLEADO)).
75
Álgebra Relacional: Operaciones
Adicionales (Agregación y Agrupamiento)
Si no se hiciese el renombrado a través de la función , el nombre de los
atributos de la relación de salida se cambia a la forma <función><atributo>,
podemos verlo en la tabla (b) de la transparencia anterior (esta notación no
es estándar, aunque es la notación que utiliza el libro).
Si no se especifican atributos de agrupamiento, las funciones de agregación
se aplican a todas la tuplas indiscriminadamente de la relación (ver tabla (c)
de la transparencia anterior).
Es importante recalcar que en general las duplicaciones no se eliminan
cuando se aplican funciones de agregación, por lo tanto hay que tener con las
interpretaciones de aplicar a estos agrupamientos las funciones de
agregación.
Si queremos eliminar duplicados antes de pasar una función de agregación
aplicamos la función DISTINCT.
76
Álgebra Relacional: Operaciones
Adicionales (Cierre Recursivo)
El cierre recursivo se aplica a una relación recursiva entre tuplas del mismo
tipo, como por ejemplo las que se establecen entre un empleado y un
supervisor.
Esta relación esta descrita por la FK SuperDni de EMPLEADO.
Un ejemplo de operación recursiva sería la recuperación de supervisiones de
un empleado e a todos sus niveles, es decir todos los empleados e’
directamente supervisados por e, los e’’ que son todos los supervisados por
e’ y así sucesivamente.
Como haríamos esto con un ejemplo, para indicar los Dni de todos los
empleado e’ supervisados directamente (a nivel 1) por el empleado e cuyo
nombre es Eduardo Ochoa:
DNI_OCHOA Dni( Nombre=‘Eduardo’ AND Apellido1=‘Ochoa’ EMPLEADO))
SUPERVISION(Dni1, Dni2) Dni, SuperDni(EMPLEADO)
RESULTADO1(Dni) Dni1(SUPERVISION |><| Dni2=Dni DNI_OCHOA)
77
Álgebra Relacional: Operaciones
Adicionales (Cierre Recursivo)
Si queremos recuperar los supervisados por Ochoa a nivel 2:
RESULTADO2(Dni) Dni1(SUPERVISION |><| Dni2=Dni RESULTADO1)
Para obtener los empleados supervisados a nivel 1 y 2 hacemos la UNIÓN:
RESULTADO RESULTADO2 RESULTADO1
Podemos seguir así indefinidamente hasta alcanzar todos los niveles, sin
emplear un mecanismo de bucle recursivo.
En el estándar SQL3 hay una sintaxis exclusiva para el cierre recursivo.
78
Álgebra Relacional: Operaciones
Adicionales (Cierre Recursivo)
79
Álgebra Relacional: Operaciones
Adicionales (Concatenación Externa)
Las concatenaciones que mantienen solo la tuplas coincidentes (a través de una
cierta condición), se llaman concatenaciones internas (son las que hemos visto
hasta ahora).
Pero podemos querer por ejemplo una lista de todos los empleados junto con los
departamentos que controlan (si no controlan que aparezca NULL).
Para esta consulta podemos utilizar la CONCATENACIÓN EXTERNA
IZQUIERDA (LEFT OUTER JOIN):
– TEMP (EMPLEADO ||><| Dni=DniDirector DEPARTAMENTO)
– RESULTADO Nombre, Apellido1, Apellido2, NombreDpto(TEMP)
Ver la siguiente transparencia.
Notar que mantiene cada tupla de la primera relación (relación IZQUIERDA), y si
no encuentra ninguna tupla en la segunda relación a través de los atributos de
concatenación especificados, coloca un NULL.
La CONCATENACIÓN EXTERNA DERECHA (RIGTH OUTER JOIN) es
similar, |><||(mantiene todas la tuplas de la relación derecha).
CONCATENACIÓN EXTERNA COMPLETA (FULL OUTER JOIN), ||><||.
80
Álgebra Relacional: Operaciones
Adicionales (Concatenación Externa)
81
Álgebra Relacional: Operaciones
Adicionales (Unión Externa)
Fue diseñada para obtener la unión de tuplas no compatibles, cuando hay
compatibilidad parcial, por ejemplo
– R(X,Y) y S(X,Z), dando la unión externa otra relación T(X,Y,Z)
– Cuando alguna parte X de R o S no se encuentran entonces habrá valores NULL
en Y o Z.
Por ejemplo: ESTUDIANTE(Nombre, Dni, Departamento, Tutor) y
PROFESOR(Nombre, Dni, Departamento, Cargo).
Solo comparten los tres primeros atributos, y la unión externa de ambas sería:
– ESTUDIANTE_O_PROFESOR(Nombre, Dni, Departamento, Tutor, Cargo)
Cuando ESTUDIANTE Y PROFESOR se combinen en una UNION
EXTERNA, los tres primeros atributos que provengan de ESTUDIANTE
tendrán NULL en el Cargo, y cuando provengan de PROFESOR tendrán
NULL en el atributo Tutor.
82
Correspondencia con SQL
atributos (condición (R)) SELECT atributos FROM R WHERE condición
A / C (A, B (condición (R)) SELECT A AS C, B FROM R WHERE condición
S (atributos (condición (R)) CREATE VIEW S AS atributos FROM R
WHERE condición
atributos (condición (R ⋈ S)) SELECT atributos FROM R JOIN S
WHERE condición
atributos (c1(R ⋈c2 S)) SELECT atributos FROM R JOIN S
WHERE c1 and c2
atributos (condición (R S)) SELECT atributos FROM R, S
WHERE condición
R S, R S, R – S R UNION S, R INTERSECT S, R EXCEPT S
atributos Count(A), Sum(B)… (R) SELECT atributos Count(A), Sum(B)… FROM R
GROUP BY atributos
83
BD Empresa
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
SUBORDINADO
TRABAJA_EN
84
Campos, Tuplas y Tablas en una BD
EMPLEADO
DEPARTAMENTO
LOCALIZACIONES_DPTO
PROYECTO
TRABAJA_EN
SUBORDINADO
85
Álgebra Relacional: Ejemplos
Esta consulta se podría expresar de otras formas: se podría invertir el
orden de las operaciones de CONCATENACIÓN y SELECCIÓN y se
podría sustituir por una concatenación natural, después por supuesto de
hacer un renombrado (ejercicio para casa).
86
Álgebra Relacional: Ejemplos
87
Álgebra Relacional: Ejemplos
88
Álgebra Relacional: Ejemplos
89
Álgebra Relacional: Ejemplos
7-RyC-EDAT-121.pdf
Registros y Campos (ejemplos de
almacenamiento en longitud fija y
longitud variable)
2
Almacenamiento de tablas:
Campos y registros
Ejemplo 1: Longitud Fija
DEPARTAMENTO
NombreDpto NumeroDpto DniDirector FechaIngre
Investigacion 5 333445555 1988-28-02
Administracion 4 987654321 1995-01-01
Sede Central 1 888665555 1981-06-19
Investigacion_43334455551988-28-02
Administracion53334455551988-28-02
Sede Central__18886655551981-06-19
0
1
2
Long. fija
Long. fija
TUPLA REGISTRO
ATRIBUTO CAMPO
NRR
El campo NombreDpto tiene tres bytes desaprovechados: uno en
Investigacion_, y otro en Sede Central__ (el símbolo “_” un byte
desprovechado). Pero no se pierde mucho espacio en este caso.
El tamaño de los registros es fijo de 34 bytes.
Representación en la memoria secundaria
3
Almacenamiento de tablas:
Campos y registros
Ejemplo 2: Longitud Variable
421133344555505Ana24C/Oña 8, 28050 Madrid
991198765432114Hermenegildo72C/ Trallera...
.
.
.
0
42
141
·
·
·
Long. variable
offset
(bytes) long.
campo
long.
registro
Long. variable
EMPLEADO
DNI Nombre Dirección
333445555 Ana C/ Oña 8, 28050 Madrid
987654321 Hermenegildo
C/ Trallera S/N, Colinas del Campo
de Martín Moro Toledano, 24313
Leon
.
.
.
.
.
.
.
.
.
(incluyendo 2 bytes por
indicador de longitud)
Representación en la memoria secundaria
9 bytes 3 bytes 22 bytes
9 bytes
12 bytes 70 bytes
TUPLA REGISTRO
ATRIBUTO CAMPO
Indicador de longitud de
REGISTRO y de CAMPO
4
Almacenamiento de tablas:
Campos y registros
Ejemplo 3: Longitud Variable
400912333445555AnaC/Oña 8, 28050 Madrid
970921987654321HermenegildoC/ Trallera...
.
.
.
0
40
137
·
·
·
Long. variable
offset
(bytes) Posición
Campo 3
long.
registro
Long. variable
EMPLEADO
DNI Nombre Dirección
333445555 Ana C/ Oña 8, 28050 Madrid
987654321 Hermenegildo
C/ Trallera S/N, Colinas del Campo
de Martín Moro Toledano, 24313
Leon
.
.
.
.
.
.
.
.
.
(incluyendo 2 bytes por
indicador de longitud)
Representación en la memoria secundaria
9 bytes 3 bytes 22 bytes
9 bytes
12 bytes 70 bytes
TUPLA REGISTRO
ATRIBUTO CAMPO
Indicador de longitud de
REGISTRO e indicador de
posición de CAMPO
Posición
Campo 2
5
Almacenamiento de tablas:
Campos y registros
Ejemplo 4: Longitud Variable
333445555|Ana|C/ Oña 8, 28050 Madrid#
987654321|Hermenegildo|C/ Trallera...
.
.
.
0
37
131
·
·
·
Long. variable
offset
(bytes)
Long. variable
EMPLEADO
DNI Nombre Dirección
333445555 Ana C/ Oña 8, 28050 Madrid
987654321 Hermenegildo
C/ Trallera S/N, Colinas del Campo
de Martín Moro Toledano, 24313
Leon
.
.
.
.
.
.
.
.
.
Representación en la memoria secundaria
9 bytes 3 bytes 22 bytes
9 bytes
12 bytes 70 bytes
TUPLA REGISTRO
ATRIBUTO CAMPO
Indicador delimitador de
CAMPO y REGISTRO
delimitador
campo p.e. '|'
delimitador
registro p.e. ‘#'
6
Almacenamiento de tablas:
Campos y registros
Ejemplo 5: Longitud Variable
38333445555|Ana|C/Oña 8, 28050 Madrid
95987654321|Hermenegildo|C/ Trallera...
.
.
.
0
38
133
·
·
·
Long. variable
offset
(bytes) long.
registro
Long. variable
EMPLEADO
DNI Nombre Dirección
333445555 Ana C/ Oña 8, 28050 Madrid
987654321 Hermenegildo
C/ Trallera S/N, Colinas del Campo
de Martín Moro Toledano, 24313
Leon
.
.
.
.
.
.
.
.
.
(incluyendo 2 bytes por
indicador de longitud)
Representación en la memoria secundaria
9 bytes 3 bytes 22 bytes
9 bytes
12 bytes 70 bytes
TUPLA REGISTRO
ATRIBUTO CAMPO
Indicador de longitud de
REGISTRO y delimitador
de CAMPO
delimitador
campo p.e. '|'
8-BINyTEXT-EDAT-121.pdf
Binario vs. Texto en
escritura/lectura de ficheros
2
Binario vs. Texto Imaginemos que queremos guardar en el disco duro el
número 425 en formato short
int, mediante sentencias del
lenguaje C.
Hay una diferencia de
almacenar ese dato en formato
binario o texto.
Cuando se almacenan datos de
una sola vez en un ordenador
de más de 1 byte, estos se
pueden escribir:
– big-endian: 0x01A9 se almacena en
memoria (disco duro por ejemplo) como
{01, A9}. Típico de Motorola.
– little-endian: 0x01A9 se almacena en
memoria (disco duro por ejemplo) como
{A9, 01}. Típico de Intel.
– Hay arquitecturas que pueden trabajar
con ambos enfoques: middle-endian.
Típico de ARM y PowerPc.
Así en binario se guardan dos
bytes a la vez (fwrite).
En texto internamente byte a
byte (fprint).
3
Binario vs. Texto
Compilo y ejecuto: $ gcc bin-text.c -o bin-text; ./bin-text
Muestro los bytes de los ficheros bin.dat y text.dat
$ hexdump -C bin.dat
00000000 a9 01 |..| (los puntos son caracteres no imprimibles)
00000002
$ hexdump -C text.dat
00000000 34 32 35 |425|
00000003
Escritura en Binario ( fwrite(&n,sizeof(n), 1, f1) ): como lo he ejecutado en un Intel
es little-endian y el valor hexadecimal A901 almacenado el disco duro representa el
número hexadecimal 01A9 que en binario es 0000 0001 1010 1001. Este número en
binario es precisamente la representación en complemento a dos del número short int
n=425. Así 42510 0116 A916 00000001101010012 (en representación
complemento a 2)
Escritura en Texto ( fprintf(f2,"%d",n) ): generalmente el modo texto ocupa más, e
implica la conversión en RAM de cada dígito del número a ASCII. 42510 “425”
‘4’ ‘2’ ’5’ 52 50 53 (códigos ASCII) 3416 3216 3516 001101002
001100102 001101012 (binario puro,
cuando hay un signo menos se codifica con su
código ASCII, 45 (-), es decir 2D en hexadcimal).
4
Binario vs. Texto
Supongamos ahora: short int n = -425; (1111 1110 0101 0111 en C-A2, 0xFE57)
Compilo y ejecuto: $ gcc bin-text.c -o bin-text; ./bin-text
Muestro los bytes de los ficheros bin.dat y text.dat
$ hexdump -C bin.dat
00000000 57 fe |W.| (el punto es un carácter no imprimible)
00000002
$ hexdump -C text.dat
00000000 2d 34 32 35 |-425|
00000003
Así la escritura en texto es legible para un editor de texto. La escritura en binario no
tiene porque ser legible para un editor de texto.
La lectura (como es lógico) debe ser coherente con la escritura: binario binario,
texto texto (es decir fwrite fread, fprintf fscanf)
