• Author / Uploaded
• Marvin Alfaro

• Categories
• Modelo Cliente-servidor
• Bases de datos
• Servidor (Informática)
• Base de datos relacional
• Cliente (Informática)

Citation preview

UNIVERSIDAD DE COSTA RICA UNIVERSIDAD NACIONAL MAESTRÍA EN SIG Y TELEDETECCIÓN

Elaborado por Dra. Elzbieta Malinowski G.

El siguiente material en su mayoría está basado en el libro de R. Elmasri y S. Navathe “Fundamentos de Sistemas de Bases de Datos”, 5 edición, 2007 y de ninguna forma reemplaza este libro como libro de texto.

Universidad de Costa Rica, Universidad Nacional

PARTE I: INFORMACIÓN GENERAL DEFINICIONES VARIAS
Base de datos Colección de datos lógicamente relacionados que puede tener cualquier tamaño y complejidad. Ejemplos: la base de datos para el procesamiento de matrícula, las compras en el supermercado, compras utilizando tarjetas de crédito, sistema de inventario, figura 1.2 del libro de texto:


Dato Hecho que puede ser recopilado y que tiene un significado específico. Ejemplos: Nº telefónico, Nº cédula, dirección, nombre.
DBMS (SGBD, SABD) (Database Management System, Sistema de Gestión de Bases de Datos, Sistema de Administración de Bases de Datos) Es una colección de programas que permite: • Definir la base de datos → especificar su estructura, tipos de datos, relaciones entre datos, restricciones y otros. • Construir, poblar o cargar la base de datos → insertar los datos. • Manipular los datos → consultar, actualizar, generar reportes y otros. • Compartir los datos → permitir acceso simultáneo a varios usuarios y programas. • Proteger los datos → proteger contra el funcionamiento defectuoso del hardware y software como también contra accesos no autorizados o malintencionados. • Mantener la base de datos → permitir la evolución de la base de datos según cambian los requisitos con el tiempo. Ejemplos: Oracle, SQL Server, DB2, Postgres, MySQL.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

2

Universidad de Costa Rica, Universidad Nacional


Catálogo Conocido también como diccionario de datos, directorio de datos o metadatos. Contiene información sobre los datos, como por ejemplo el tipo y formato de almacenamiento de cada elemento, estructura de cada archivo, información sobre derechos de acceso y tipos de usuario. Ejemplo: Figura 1.3 del libro de texto.


Sistema de Base de Datos Es la combinación de los datos y el DBMS (Figura 1.1 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

3

Universidad de Costa Rica, Universidad Nacional


Propiedades de una Base de Datos • Una base de datos representa un subconjunto de aspectos del mundo real (llamado minimundo o universo de discurso). Por ejemplo, una base de datos para manejo de inventario, matrícula u operaciones bancarias. • Una base de datos se diseña, construye y manipula para un propósito específico y un grupo de usuarios particular, con algunas aplicaciones preconcebidas. Por ejemplo, en un banco se puede requerir diferentes bases de datos: algunas que manejan las transacciones de cajeros automáticos, otros que guardan los datos referentes a los préstamos, salarios de los empleados, etc. PARTICIPANTES DEL SISTEMA DE BASE DE DATOS
DBA (Database administrator), Administrador de la base de datos Encargado de la administración de los recursos del sistema de base de datos. Responsable de la seguridad de la base de datos, y del monitoreo de su uso.
Diseñadores de bases de datos Identifican los datos a almacenar, especifican la estructura de la base de datos, entienden los requerimientos de los usuarios y definen las vistas, entre otras cosas.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

4

Universidad de Costa Rica, Universidad Nacional


Usuarios finales • Casuales o esporádicos: Necesitan una información diferente cada vez que acceden la base de datos. Ejemplo: estudiantes del curso de base de datos. • Paramétricos: Usan casi las mismas consultas cada vez que acceden la BD (transacciones enlatadas). Ejemplo: empleados bancarios, de aerolíneas, hoteles, etc. • Avanzados: Conocen muy bien el DBMS e implementan aplicaciones que les permitan satisfacer sus complejos requerimientos. Ejemplo: ingenieros, científicos, analistas. • Autónomos: mantienen bases de datos personales mediante la utilización de paquetes de programas comerciales que cuentan con interfaces de fácil uso. Ejemplo: los usuarios de paquetes de impuestos que guardan sus datos financieros para declarar la renta.
Analistas de sistemas y programadores de las aplicaciones Los analistas de sistemas recopilan los requerimientos de los usuarios finales (causales y paramétricos, especialmente) y desarrollan las especificaciones para las transacciones enlatadas que satisfagan dichos requerimientos. Los programadores de aplicaciones implementan estas especificaciones como programas, los depuran (debug), documentan y mantienen.

HISTORIA DE LOS SISTEMAS DE BASES DE DATOS EVENTO

CONSECUENCIA

Se desarrollan las cintas magnéticas. Se instala la primera computadora comercial McGree propone la noción de acceso generalizado a los datos almacenados electrónicamente IBM introduce el sistema Ramac

Se reemplazan las tarjetas perforadas y las cintas de papel

ANTES DE LOS 60’s 1945 1957 1959

1959

Los datos son leídos de manera no secuencial y el acceso a los archivos es factible.

LOS 60’S 1961

Bachman diseña el primer DBMS generalizado -GE’s Integrated Data Store (IDS)- Se distribuye en 1964. Se popularizan los diagramas de estructura de datos.

Se forman las bases del modelo de datos para red desarrollado por CODASYL DBTG (Conference on Data Systems Languages Database Task Group)

1965-1970

Se generaliza el sistema de manejo de archivos desarrollado por numerosos vendedores.

Se proveen dos sistemas conceptuales/ vista al usuario de la organización de los datos.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

5

Universidad de Costa Rica, Universidad Nacional

Sistema de manejo de información (IMS) desarrollado por IBM.

Se forman las bases del Modelo de Datos Jerárquicos.

IMS DB/DC (database/data communication). Fue el primer sistema DB/DC a gran escala.

Se da soporte a las vistas de la jerarquía más alta.

SABRE. Desarrollado por IBM y American Airlines.

Múltiples accesos de usuarios a datos, desarrollaron una comunicación de red.

1970

El modelo relacional es desarrollado por Tedd Codd, una investigación de IBM.

Nace la Fundación para la teoría de bases de datos.

1971

CODASYL Database Task Group Report

1975

El Grupo Especial de Interés de la ACM sobre Manejo de Datos organizó la primera conferencia internacional SIGMOD.

Dio pie la creación de un foro de discernimiento sobre la investigación de base de datos.

1975

La Very Large Data Base Foundation organizó la primera conferencia internacional de VLDB.

Dio pie a la creación de otro foro de discernimiento sobre la investigación de base de datos.

1976

El modelo Entidad-Relación (ER) fue introducido por Chen.

Se introduce la necesidad de modelado conceptual

LOS 70’ s

• Proyectos de Investigación en los 70’s: System R (IBM), INGRES (University of California, Berkeley), System 2000 (University of Texas, Austin). • Lenguajes de Consultas desarrollados en los 70’s: SQUARE, SEQUEL(SQL), QBE, QUEL. Los 80’s

1985

Se desarrollaron DBMS para computadoras personales (DBASE, PARADOX, etc.)

Permitió a los usuarios de PC definir y manipular los datos. Carecían de multivistas /multiacceso y existía aislamiento entre programas y datos.

Se publicó el estándar de SQL. El mundo de negocios es influenciado por los lenguajes de “Cuarta Generación”. Se propone

Generated complete application programs comenzaron desde un alto nivel de lenguaje de interfaz.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

6

Universidad de Costa Rica, Universidad Nacional

el Network Definition Language (NDL) hecho por ANSI. • Tendencias en los 80’s: Sistemas de Bases de Datos Expertos, DBMS orientados a objetos, Arquitectura cliente / servidor para bases de datos distribuidas.

Permitieron nuevas aplicaciones de bases de datos, trabajo de red y manejo de datos distribuidos.

Demanda de las capacidades de los DBMS para usarlas en nuevas aplicaciones.

DBMS con características para datos espaciales, temporales y multimedia, incorporando capacidades activas y deductivas. Aparecen los DBMS comerciales orientados a objetos.

Demanda del desarrollo de aplicaciones utilizando datos de fuentes variadas.

Desarrollo de software que permite la conectividad abierta con bases de datos (Open Databa Connectivity)

Demanda de la explotación de Massively Parallel Processors (MPPs)

Diferentes sistemas de DBMS incluyen componentes que permiten procesamiento paralelo.

Demanda de usar los datos para convertirlos en la información útil para la empresa.

Aparecen nuevos componentes en DBMS capaces de manejar en forma especial los almacenes de datos.

Nuevas formas de acceder la información desde Internet y su uso en el comercio electrónico.

Las DBMS tienen incorporada las herramientas de acceso desde Internet, manejo de formato libre por medio de XML.

Crecimiento de volumen de datos para la manipulación y análisis

Nuevos tipos de bases de datos (de grafos, no-sql, new-sql y otros). Aparición de término “Big data” y “Big Data Anlaysis”.

Los 90’s

Los 2000’

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

7

Universidad de Costa Rica, Universidad Nacional

CONCEPTOS BÁSICOS Y ARQUITECTURA DE LOS SISTEMAS DE BASES DE DATOS
Modelo de Datos Conjunto de conceptos usados para describir la estructura de la BD (tipos de datos, relaciones, y restricciones). Además contiene un conjunto de operaciones básicas para especificar consultas y actualizaciones sobre la base de datos.
Esquema de la BD (intensión) Descripción de la BD que incluye su estructura (elementos, grupos de elementos y relaciones entre ellos), tipos de datos y restricciones. Ejemplo: Figura 2.1 del libro de texto:


Estado de la BD (snapshot, instancia, extensión) Conjunto de datos que se encuentra en la BD en un momento específico. Ejemplo: Figura 1.2 del libro de texto:

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

8

Universidad de Costa Rica, Universidad Nacional

NIVELES DE ABSTRACCIÓN La arquitectura de las BDs es conocida como arquitectura de tres niveles (o esquemas). Esta arquitectura fue propuesta por ANSI (American National Standards Institute, Instituto Americano Nacional de Estándares) y SPARC (Standard Planning and Requirements Committee, Comité de Requisitos y Planificación de Estándares) (ANSI/SPARC). Arquitectura de tres niveles o esquemas Sirve para separar las aplicaciones de usuario de la base de datos física.

1. Nivel interno (físico) Contiene la descripción del almacenamiento físico de los datos y los métodos de acceso. Usa un modelo de datos físico. Ejemplo: Figura 1.4 del libro de texto:

2. Nivel conceptual Describe la estructura (elementos, relaciones, operaciones de usuarios y restricciones) de la BD para una comunidad de usuarios, omitiendo los detalles físicos de almacenamiento. Se usan los modelos conceptuales (por ejemplo, modelo entidad-relación, orientado a objetos, semántico) o modelos Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

9

Universidad de Costa Rica, Universidad Nacional

de representación/implementación (por ejemplo, relacional, objeto-relacional, orientado a objetos, jerárquico). Ejemplo: Figura 2.1 del libro de texto:

3. Nivel externo (conjunto de vistas de usuario) Una vista describe la parte de la BD en la cual un grupo de usuarios está interesado, y oculta los demás componentes de la base de datos. Ejemplo: Figura 1.5 del libro de texto:

Los tres niveles representan distintas descripciones de los datos. Los datos realmente sólo existen en el nivel físico de la BD. La mayoría de los DBMS comerciales no tiene esta separación de niveles tan clara.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

10

Universidad de Costa Rica, Universidad Nacional

LENGUAJES DE

BASES DE DATOS

Sirven para diferentes propósitos y pueden ser agrupados de la siguiente manera:
DDL (Data Definition Language) Es el Lenguaje de definición de datos que permite especificar el esquema lógico de la BD y guardar esta descripción en el catálogo. Ejemplo: CREATE TABLE EMPLEADO ( CEDULA CHAR(9) NOMBRE VARCHAR(30) NACIM DATE, DIRECCION VARCHAR(50), SALARIO DECIMAL(10,2) SUPERCED CHAR(9), NUMDEP INT PRIMARY KEY (CEDULA), FOREIGN KEY (NUM_DEP)

NOT NULL, NOT NULL,

NOT NULL,

); ALTER TABLE EMPLEADO ADD SEX CHAR;

Otros comandos: CREATE INDEX, DROP TABLE, DROP INDEX.
VDL (View Definition Language) Es el lenguaje de definición de vistas que permite especificar las vistas de usuario. En la mayoría de los DBMSs se usa el DDL como VDL. Algunos comandos: CREATE VIEW, DROP VIEW. Ejemplo: CREATE VIEW EMPMUSIC (NOM, PAGO) AS SELECT NOMBRE, SALARIO FROM EMPLEADO WHERE NUMDEP = 5;

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

11

Universidad de Costa Rica, Universidad Nacional


DML (Data Manipulation Language) Es el lenguaje de manipulación de datos que permite recuperar, insertar, borrar y modificar los datos almacenados. Ejemplos: 1. SELECT NOMBRE, NUMDEP FROM EMPLEADO WHERE DIRECCION = ‘SAN PEDRO’ AND SALARIO < 600000 2. INSERT INTO EMPLEADO VALUES (‘1-000-2222’,’JUAN PEREZ’, 12-MAY-1934, ‘SAN PEDRO’, 78000, ‘2-345-678’, 5); 3. DELETE FROM EMPLEADO WHERE DIRECCION = ‘ZAPOTE’; 4. UPDATE EMPLEADO SET SALARIO = 100000 WHERE CEDULA = ‘8-876-543’;


DCL (Data Control Language) Lenguaje de control de datos que permite controlar el acceso a los recursos de sistema. Algunos comandos: ALTER PASSWORD, GRANT, REVOKE. Ejemplos: GRANT ALL PRIVILEDGES ON price TO manager WITH GRANT OPTION; GRANT SELECT ON direcciones TO Juan; REVOKE SELECT ON direcciones FROM Juan;

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

12

Universidad de Costa Rica, Universidad Nacional

ARQUITECTURAS PARA LOS DBMS Arquitectura centralizada Los primeros sistemas de base de datos fueron sistemas centralizados, donde la base de datos, el DBMS y las aplicaciones de usuario estaban en una misma computadora (típicamente mainframe). Los usuarios accedían a estos sistemas por medio de terminales con muy baja capacidad de procesamiento que prácticamente sólo servían para visualizar la información enviada por el computador central o mainframe. Un ejemplo de arquitectura centralizada se presenta en la siguiente figura (figura 2.4 del libro de texto):

Posteriormente, las terminales fueron reemplazadas por PCs y estaciones de trabajo, sin embargo, eran usadas de modo similar (es decir, sólo para visualización). Gradualmente los DBMSs tomaron ventaja de la capacidad de procesamiento del lado del usuario, dando paso a las arquitecturas cliente/servidor.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

13

Universidad de Costa Rica, Universidad Nacional

Arquitectura cliente/servidor de dos capas La arquitectura cliente/servidor se desarrolló para entornos de redes de computadoras donde existían máquinas cliente con cierta capacidad de procesamiento y máquinas servidor poderosas que ofrecían diferentes tipos de servicios. Un ejemplo de la arquitectura cliente/servidor al nivel lógico (figura 2.5 del libro de texto).

Servidores: máquinas con funcionalidades específicas. Pueden ser de diferentes tipos, por ejemplo: servidores de impresión, de archivos, de e-mail, y de DBMS. Los últimos se usan para todas las tareas relacionadas con la creación, manipulación y mantenimiento de bases de datos. Clientes: máquinas con capacidad de procesamiento local para ejecutar las aplicaciones locales y usar los servicios ofrecidos por las computadoras servidor. En la siguiente figura se presenta la arquitectura física y algunas variaciones que se pueden dar en el uso de las computadoras como servidores, clientes, o ambos.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

14

Universidad de Costa Rica, Universidad Nacional

Arquitectura cliente/servidor de dos capas para los DBMS En la arquitectura cliente/servidor de dos capas para los DBMS, los programas de interfaz de usuario y los programas de aplicación se ejecutan del lado del cliente. El cliente puede comunicarse con el DBMS siempre y cuando tenga instalado el software necesario y obtenga la conexión requerida. El servidor ejecuta la funcionalidad de consulta y transacción, relacionada con el procesamiento de comandos SQL. Los componentes de software están, por ende, distribuidos en dos sistemas: cliente y servidor. Ventajas de tener aplicaciones (o algunos de sus componentes) en el servidor: • Se pueden compartir entre diferentes usuarios. • Es más fácil optimizarlas y modificarlas. • Se puede restringir su uso a personas autorizadas con más facilidad. • Se puede guardar su versión ya compilada disminuyendo el tiempo de respuesta a la consulta. Desventajas: • Sobrecarga del servidor. • Demasiado tráfico en la red. Arquitectura cliente/servidor de tres capas para aplicaciones web La arquitectura de tres capas contiene una capa intermedia entre el cliente y el servidor de la base de datos, como se muestra en la siguiente figura (figura 2.7 (a) del libro de texto). Esta capa intermedia almacena las reglas de comercio (procedimientos o restricciones) que se usan para acceder a los datos del servidor de BD. La opción (a) presenta las tres capas usadas en el ambiente Web y la opción (b) de la figura muestra nomenclatura general. La capa de presentación es la interfaz de usuario y permite la entrada de datos. La capa lógica comercial maneja las reglas y restricciones comerciales. La capa de servicios incluye todo lo referente a la administración de los datos.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

15

Universidad de Costa Rica, Universidad Nacional

CLASIFICACIÓN DE LOS DBMS Criterio Modelo de datos

Clasificación relacional, objeto-relacional, de objetos, de red 1 y jerárquico2. Actualmente otros modelos, por ejemplo, Non-SQL, NewSQL, grafos, entre otros.

Número de usuarios

monousuario y multiusuario.

Número de sitios

centralizado (un solo sitio) y distribuido (muchos sitios). • Dist. homogéneo: el mismo software DBMS es usado en todos los sitios. • Dist. heterogéneo: múltiples DBMSs autónomas se acoplan en un DBMS federado (loosely coupled, highly coupled).

Propósito de uso

general, específico (multimedia, espaciales, OLTP y OLAP).

1

El modelo de red representa los datos como “tipos de registros” y sólo tiene un tipo de relación, 1:N, llamada “tipo conjunto”. Este modelo tiene un lenguaje record-at-a-time que debe incluirse en un lenguaje de programación huésped (e.g., COBOL). Ejemplos de DBMSs basados en modelos de datos de red: IDMS, DMS 1100, IMAGE, VAX-DBMS y SUPRA. 2 El modelo jerárquico representa los datos en estructuras jerárquicas tipo árbol. Cada jerarquía representa registros relacionados. El DML jerárquico más popular fue DL/1 de IMS (IBM). Ejemplos de DBMS basados en modelos de datos jerárquicos: IMS, System 2K y TDMS.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

16

Universidad de Costa Rica, Universidad Nacional

PARTE II: ETAPAS DE DISEÑO Y MODELO ENTIDADRELACION DISEÑO DE UNA BASE DE DATOS El diseño de una BD incluye varias etapas, como se presenta en la siguiente figura (figura 3.1 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

17

Universidad de Costa Rica, Universidad Nacional

1. Recopilación y análisis de requerimientos Se entrevista a los usuarios para entender y documentar sus requerimientos de datos. Además se especifican los requerimientos funcionales que la aplicación debe implementar (operaciones o transacciones definidas por el usuario). 2. Diseño conceptual Se crear el esquema conceptual usando un modelo de datos de alto nivel. Dicho esquema describe detalladamente tipos de entidades, relaciones entre ellas y restricciones. Paralelamente se especifican las operaciones de usuario como transacciones de alto nivel. 3. Diseño lógico (mapeo de modelo de datos) Se implementa la BD en algún DBMS comercial, usando el modelo de datos específico del DBMS (modelo de datos de implementación), e.g., relacional u orientado a objetos. 4. Diseño físico Se especifica la estructura de almacenamiento de los archivos, su organización, índices, y rutas de acceso. Paralelamente se diseñan e implementan los programas de aplicación correspondientes a las transacciones de alto nivel. Ejemplo La base de datos para una compañía, que mantiene información sobre los empleados, los departamentos y los proyectos de la compañía. Recopilación y análisis de requerimientos La descripción del mini-mundo después de entrevistar a los usuarios es: • La compañía está dividida en departamentos. Cada departamento tiene un nombre único, un número único que lo identifica y un empleado concreto que es el gerente del departamento. Es necesario registrar la fecha en que el gerente comenzó a dirigir el departamento. Un departamento puede estar ubicado en varios lugares. • Un departamento controla varios proyectos, cada uno de los cuales tiene un nombre único, un número único y una sola ubicación. • Para cada empleado se necesita guardar su nombre, número de cédula, dirección, salario, sexo y fecha de nacimiento. Cada empleado está asignado a un departamento pero puede trabajar en varios proyectos que no necesariamente están controlados por el mismo departamento. Es necesario registrar el número de horas que cada empleado trabaja en un proyecto. Además, hay que mantener información sobre el supervisor directo de cada empleado. • Se requiere mantener información sobre los dependientes de cada empleado para propósitos de seguro social. Para cada dependiente se

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

18

Universidad de Costa Rica, Universidad Nacional

almacenará su nombre, sexo, fecha de nacimiento y relación con el empleado. Diseño conceptual El esquema conceptual se puede visualizar por medio del diagrama EntidadRelación (ER), como se observa en la siguiente figura (figura 3.2 del libro de texto). Esta figura se explicará gradualmente, conforme se vayan presentando los conceptos del modelo ER.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

19

Universidad de Costa Rica, Universidad Nacional

DEFINICIONES SOBRE ENTIDADES
Entidad Es el objeto básico que representa el modelo ER. Es un objeto que existe en el mundo real y que puede distinguirse de otros objetos. Puede ser un objeto con existencia física o conceptual. Toda entidad posee características llamadas atributos. Ejemplos (Figura 3.3 del libro de texto: empleado e1 y compañía c1):


Tipo de entidad Define una colección de entidades que tienen las mismas características. Se representa en el diagrama ER como un rectángulo con el nombre del tipo de entidad en su interior. Ejemplos: • Empleado, Departamento y Proyecto de la BD COMPAÑÍA. • Automóvil, Estudiante, Curso, Supermercado.
Atributo Propiedad que describe a la entidad (o relación). Los atributos se representan en el diagrama ER como óvalos con el nombre del atributo en su interior. Ejemplos: • número de cédula • nombre • número de teléfono
Dominio Tipo y rango de los valores que se pueden asignar a cada atributo. Es un tipo de restricción. Ejemplos: • Dominio de salario: número real entre 230,000 y 950,000. • Dominio de edad: número entero entre 18 y 65 años.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

20

Universidad de Costa Rica, Universidad Nacional


Esquema (Intención) Es la descripción, por medio de un tipo de entidad, de un conjunto de entidades que comparten su estructura.
Conjunto de entidades (Extensión) Colección de todas las entidades de un tipo de entidad de la BD en un instante del tiempo. Ejemplos: figura 3.6 del libro de texto • Empleados particulares: e1, e2, e3. • Compañías particulares: c1, c2.

TIPOS DE ATRIBUTOS
Atributos simples (atómicos) Son aquellos que no pueden ser divididos en atributos más simples (o subpartes con significado propio). Ejemplos: • Atributos Sexo y Salario de la entidad EMPLEADO en el esquema ER de la BD COMPAÑIA . • Número de teléfono, número de hijos de una persona.
Atributos compuestos Son aquellos que pueden ser divididos en subpartes que representan atributos más simples (o atómicos) con significado independiente. Un atributo se define compuesto cuando el usuario a veces se refiere al atributo como una unidad y a veces se refiere a los componentes individuales del atributo.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

21

Universidad de Costa Rica, Universidad Nacional

Se representan en el diagrama ER como óvalo con el nombre general del atributo en su interior y óvalos para representar cada atributo simple que forma el atributo compuesto Ejemplos: • El atributo Nombre de la entidad EMPLEADO en el esquema ER de la BD COMPAÑIA; compuesto por NombreP, Apellido1, Apellido2. • Un atributo Dirección puede estar compuesto por el nombre y número de calle, la ciudad, la provincia, y el código postal como presentado en la figura 3.4 del libro de texto


Atributos monovalor Tienen un solo valor para una entidad específica. Ejemplos: • Sexo y salario en el modelo ER de la BD COMPAÑIA.
Atributos multivalor Pueden tener un conjunto de valores para la misma entidad en la misma instancia del tiempo. Pueden tener límites inferior y superior para el número de valores permitidos por entidad. Se representan en el diagrama ER como óvalos de doble línea. Ejemplos: • Ubicación de la entidad DEPARTAMENTO en el esquema ER de la BD COMPAÑIA (atributo LUGARES), donde pueden existen varios lugares para el mismo departamento. • Grado universitario si la persona tiene varios. • Color de un auto pintado de rojo, azul y blanco (figura 3.7 del libro del texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

22

Universidad de Costa Rica, Universidad Nacional

La condición que define si un atributo puede tener uno o más valores se llama cardinalidad del atributo. Razones para definir un atributo multivalor: 1. El atributo tiene varios valores al mismo tiempo para una entidad. 2. Se requiere consultar los valores individuales que forman el atributo.
Atributos derivados Su valor puede determinarse a partir de otro atributo o derivarse de entidades relacionadas. Ejemplos: • Número de empleados de la entidad DEPARTAMENTO en el esquema ER de la BD COMPAÑIA (se deriva de la relación con la entidad EMPLEADO). • Edad (se calcula a partir del atributo Fecha de nacimiento).
Atributos base (o almacenados) Sirven para derivar otros atributos. Ejemplo: Fecha de nacimiento (del cual se deriva el atributo Edad).
Atributos nulos (NULL) No tienen valor para una entidad particular. Razones para definir un atributo nulo: 1. Su valor existe pero es desconocido. 2. Su valor no existe (atributo no es aplicable para una entidad). 3. No se sabe si el valor existe.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

23

Universidad de Costa Rica, Universidad Nacional

Ejemplos: • Altura y peso de una persona (valor existe pero lo desconocemos) • Número de apartamento en una dirección (no aplica para personas que viven en casas). • Número de teléfono de una persona (no sabemos si existe o no).
Atributo llave o clave Es un atributo o conjunto de atributos cuyo valor es distinto para cada entidad del conjunto de entidades. Identifican de forma única a cada entidad. Es una restricción de unicidad, la cual prohíbe que dos entidades tengan el mismo valor para el atributo llave al mismo tiempo. Ejemplos: • Nombre + dirección de una persona • Número de cédula de una persona • Número de placa de un vehículo Un tipo de entidad puede tener más de un atributo llave.
Atributo llave candidata Es un atributo llave con la mínima cantidad de atributos que lo conforman. Si atributos llave contiene más que un atributo, lo modelamos como un atributo compuesto. Se representa en el diagrama ER subrayando el nombre del atributo llave dentro del óvalo. Ejemplos: • Número de cédula de una persona. • Número de placa de un carro. • Número de seguridad social

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

24

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASE – IDENTIFICACIÓN DE TIPOS DE ENTIDADES: 1. ¿Cuáles tipos de entidades se pueden distinguir para modelar las siguientes situaciones? ¿Cuáles atributos se podrían asociar y cuáles de ellos pueden formar la llave candidata? a. Profesores ofreciendo cursos. b. Médicos atendiendo a sus pacientes c. Ríos pertenecientes a las diferentes cuencas. d. Los recursos naturales, las rutas de acceso a ellos y zonas de riesgo donde se pueden ubicar. e. Otros ejemplos referentes a objetos espaciales. 2. El Instituto Costarricense de Turismo desea crear una base de datos que contiene los datos referentes a diferentes hoteles, sus gerentes y servicios que ofrecen. También se necesita guardar los datos relacionados con la ubicación geográfica de estos hoteles y las rutas oficinales que se usan para accederlos. Usando la notación ER presenta los tipos de entidades con sus atributos que se podría usar para crear esta base de datos incluyendo las llaves candidatas. 3. ¿Cuándo se justifica usar en el esquema ER un atributo multivaluado? Por ejemplo, si una persona tiene varios números de teléfonos, ¿Qué decisión tomaría? Dé dos ejemplos donde en uno de ellos se justifica tener un atributo multivaluado y en el otro no existe esta necesidad. Justifique su respuesta. 4. Describa algún ejemplo relacionado con su profesión con el propósito de identificar los tipos de entidades.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

25

Universidad de Costa Rica, Universidad Nacional

DEFINICIONES SOBRE RELACIONES
Relación Es una asociación entre dos o más entidades. Ejemplo: • Juan trabaja para el Departamento de Contabilidad.
Tipo de relación Describe una colección de relaciones del mismo tipo. Ejemplo: • PERTENECE_A (un Empleado pertenece a un Departamento) en el esquema ER de la BD de COMAÑÍA.
Conjunto de relaciones Es la colección de todas las relaciones (instancias) de un tipo de relación de la BD en un instante dado del tiempo. Ejemplo: Figura 3.9 del libro de texto.


Grado de la relación Es el número de tipos de entidades que participan en la relación. • Relación binaria (de grado 2): participan dos tipos de entidades. Ejemplo: tipo de relación PERTENECE_A en la BD COMAÑÍA •

Relación ternaria (de grado 3): participan tres tipos de entidades Ejemplo: tipo de relación SUPPLY (figura 3.17 del libro de texto)

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

26

Universidad de Costa Rica, Universidad Nacional

•

Relación n-aria (de grado n): participan más de tres tipos de entidades (poco usada en bases de datos operacionales/transaccionales; muy común en almacenes de datos).


Relación recursiva Es un tipo de relación donde el mismo tipo de entidad participa más de una vez con distintos roles. Un rol es el papel que juega la entidad en la relación, y es esencial para distinguir cada participación. Ejemplo: La relación SUPERVISA, donde la entidad EMPLEADO participa dos veces: una vez bajo con rol de Supervisor y otra vez con rol de Supervisado.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

27

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – IDENTIFICACIÓN DE TIPOS DE RELACIONES 1. Para los ejercicios anteriores indique los tipos de relaciones que puedan existir entre los tipos de entidades identificados anteriormente: a. Profesores ofreciendo cursos. b. Médicos atendiendo a sus pacientes c. Ríos pertenecientes a las diferentes cuencas. d. Los recursos naturales, las rutas de acceso a ellos y zonas de riesgo donde se pueden ubicar. e. Otros ejemplos referentes a objetos espaciales. 2. La Instituto Costarricense de Turismo desea crear una base de datos que contiene los datos referentes a diferentes hoteles, sus gerentes y servicios que ofrecen. También se necesita guardar los datos relacionados con la ubicación geográfica de estos hoteles y las rutas oficinales que se usan para accederlos. Usando la notación ER incorpora los tipos de relacionales que asocian los tipos de entidades identificados anteriormente. 3. ¿Cómo modelarías los siguientes ejemplos?: a. Ríos principales con otros ríos que los alimentan. b. Compañías que son parte de otras compañías, por ejemplo, Protect & Gamble es dueño de muchas compañías muy conocidas. c. Productos que tienen otros productos como substitutos. d. Registro de hijos con sus madres y padres. 4. Menciona algunos ejemplos de tipos de relaciones recursivas que pueden visualizar en su profesión.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

28

Universidad de Costa Rica, Universidad Nacional

MÁS SOBRE LAS ENTIDADES Y RELACIONES
Tipo de entidad débil 3 Es un tipo de entidad que no tiene ningún atributo llave. Depende de la existencia de otro tipo de entidad llamado dominante. Un tipo de entidad débil posee una llave parcial (conjunto de atributos que pueden identificar a las entidades débiles que están relacionadas con la misma entidad dominante), la cual se representa en el diagrama ER con subrayado de línea discontinua. Ejemplo: DEPENDIENTES en el esquema ER de la BD de COMPAÑÍA.
Tipo de entidad subordinada Es un tipo que depende de la existencia de otro tipo de entidad dominante, pero tiene su propio identificador. Se representa en el diagrama ER de la misma forma como tipo de entidad débil Ejemplo: El tipo de entidad Licencia de Conducir (con varios atributos) que tiene su propio identificador (su número), pero su existencia puede depender del otro tipo de entidad, por ejemplo, Persona.
Tipo de entidad dominante (fuerte) Es un tipo de entidad del cual depende lasún tipo de entidad subordinada. También se conoce como entidad fuerte. Ejemplo: EMPLEADO en el esquema ER de la BD de COMPAÑÍA.
Relación identificadora Es un tipo de relación que asocia un tipo de entidad débil con su respectivo tipo de entidad fuerte. También se conoce como relación débil. Se representa en el diagrama ER como un rombo de doble línea. Ejemplo: El tipo de relación DEPENDIENTES_DE en el esquema ER de la BD COMPAÑÍA.

3

Esta definición y la siguiente difieren de las definiciones que da el libro de texto.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

29

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – TIPOS DE ENTIDADES DÉBILES 1.

2. 3.

4. 5.

Considere la división territorial de Costa Rica en distritos, cantones y provincias con los atributos de Código, Nombre y Población. Represente los tipos de entidades con atributos llaves y tipos de relaciones para modelar esta distribución. Modele la situación donde se representa las sub-cuencas, cuencas y vertientes. Incluye las suposiciones necesarias que justifiquen su diseño. En la UCR cada curso se maneja incluyendo su versión (que contiene algunas modificaciones con respecto a la propuesta original del curso). Modele la situación para representar los estudiantes que están matriculados en un grupo que pertenece a una versión específica del curso. Proponga algún esquema ER que incluye el tipo de entidad débil o subordinada referente a los ejemplos de su profesión. Interpreta el siguiente esquema en términos de sus tipos de entidades y tipos de relaciones

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

30

Universidad de Costa Rica, Universidad Nacional

RESTRICCIONES ESTRUCTURALES Los tipos de relaciones típicamente tienen ciertas restricciones que limitan las posibles combinaciones de entidades que pueden participar en las instancias de relación. Existen dos tipos de restricciones: • Razones de cardinalidad • Participación
Razones de cardinalidad (Cardinality ratio) Especifica el número máximo de instancias de relación en las que una entidad puede participar. Es decir, limita la cantidad de entidades (de un tipo de entidad participante) que se pueden asociar o relacionar con una entidad (del otro tipo de entidad participante). Las razones de cardinalidad que existen para tipos de relación binarias son 1:1, 1:N, N:1 y M:N. Una a Una (1:1)

Una entidad de A puede asociarse sólo con una entidad de B y viceversa

Ejemplo: Tipo de relación DIRIGE entre EMPLEADO y DEPARTAMENTO en el esquema ER de la BD de COMPAÑÍA: un empleado puede dirigir/administrar sólo un departamento y un departamento puede tener sólo un gerente (figura 3.12 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

31

Universidad de Costa Rica, Universidad Nacional

Una-a-Muchas (1:N)

Una entidad de A puede estar asociada con varias entidades de B, pero una entidad de B puede estar asociada con sólo una entidad de A.

Ejemplo: Tipo de relación DEPENDIENTES_DE entre EMPLEADO y DEPENDIENTE en el esquema ER de la BD de COMPAÑÍA: un empleado puede tener varios (uno o más) dependientes pero un dependiente depende de sólo un empleado.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

32

Universidad de Costa Rica, Universidad Nacional

Muchas-a-Una (N:1)

Una entidad de A puede estar asociada con una sola entidad de B, pero una entidad de B puede estar asociada con varias entidades de A.

Ejemplo: Tipo de relación PERTENECE_A entre EMPLEADO y DEPARTAMENTO en el esquema ER de la BD de COMPAÑÍA: un empleado trabaja para sólo un departamento pero un departamento puede tener muchos empleados (figura 3.9 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

33

Universidad de Costa Rica, Universidad Nacional

Muchas-a-Muchas (N:M) Una entidad de A puede estar asociada con varias entidades de B y una entidad de B puede estar asociada con varias entidades de A.

Ejemplo: Tipo de relación TRABAJA_EN entre EMPLEADO y PROYECTO en el esquema ER de la BD de COMPAÑÍA: un empleado puede trabajar en varios proyectos y en un proyecto pueden trabajar varios empleados (figura 3.13 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

34

Universidad de Costa Rica, Universidad Nacional


Participación (Existence Dependency Constraint) Especifica el número mínimo de instancias de relación en las que una entidad debe participar. Existen dos tipos de restricciones de participación: Total:

todas las entidades del conjunto de entidades tienen que participar en la relación. También se conoce como dependencia de existencia. Se representa en el diagrama ER con una doble línea.

Ejemplo: En el esquema ER de COMPAÑÍA, el tipo de entidad EMPLEADO tiene participación total en el tipo de relación PERTENECE_A, lo que significa que todo empleado debe trabajar para un departamento.

Parcial: sólo un subconjunto del conjunto de entidades participa en la relación. Ejemplo: En el esquema ER de COMPAÑÍA, el tipo de entidad EMPLEADO tiene participación parcial en el tipo de relación DIRIGE, lo que significa que algunos empleados son gerentes de departamento (lo dirigen), pero no todos. Las razones de cardinalidad junto con las restricciones de participación conforman las restricciones estructurales.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

35

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – RESTRICCIONES ESTRUCUTRALES: 1. ¿Cuál es la razón de cardinalidad y participaciones entre los siguientes tipos de entidades? • Estudiante y cursos. • Profesor y estudiante. • Esposo y esposa en nuestra sociedad. • Casa y dueño. • Cliente y factura. • Cliente y cuenta de ahorro. • Rio y distrito. • Distrito y cantón. • Propiedad y dueño. • Reserva indígena y parque nacional. • Otros ejemplos? 2. Cada una de los estudiantes debe escoger algún ejemplo visto anteriormente con los tipos de entidades y tipos de relaciones y especificar las restricciones estructurales en él. 3. Interprete las restricciones estructurales en el siguiente esquema:

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

36

Universidad de Costa Rica, Universidad Nacional

4. Especifique las suposiciones e indíquelas en la forma de restricciones estructurales

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

37

Universidad de Costa Rica, Universidad Nacional

ATRIBUTOS DE LAS RELACIONES Los tipos de relación, al igual que los tipos de entidad, pueden tener atributos. Sin embargo, podemos notar que:  Los atributos de los tipos de relación 1:1 pueden migrar hacia uno de los tipos de entidad participantes, preferiblemente a la que tiene la participación total. Ejemplo: En el esquema ER de COMPAÑÍA, la relación DIRIGE con su atributo FechaInic.  Los atributos de los tipos de relación 1:N pueden migrar sólo hacia la entidad que participa en el lado N de la relación. Ejemplo: En el esquema ER de COMPAÑÍA, si la relación PERTENECE_A tuviera un atributo FechaInicio, este atributo podría migrar hacia la relación EMPLEADO.  Los atributos de los tipos de relación N:M tienen que ser especificados como atributos de la relación y no pueden migrar hacia ninguna de las entidades participantes. Ejemplo: En el esquema ER de COMPAÑÍA, la relación TRABAJA_EN con su atributo Horas.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

38

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – ATRIBUTOS DE LOS TIPOS DE RELACIONES 1. ¿Cómo se podría modelar la situación para guardar los datos referentes a los estudiantes y los cursos que llevaron incluyendo el semestre y la nota que obtuvieron? 2. ¿Cuándo en los siguientes ejemplos se pude incluir atributo para el tipo de relación?: a. Profesores ofreciendo cursos en diferentes semestres. b. Médicos atendiendo a sus pacientes en diferentes consultorios en diferentes fechas. 3. Suponga que se está representando los datos referentes a las agencias turísticas y el distrito al cuál pertenecen. De acuerdo a la ley en Costa Rica, un nuevo distrito puede aparecer cuando 10 mil habitantes lo solicitan. Esto indica que la misma agencia puede cambiar de distrito. ¿Cómo modelarías esta situación? 4. Se tiene 100 estaciones que recopilan los datos referentes a nivel de precipitación, temperatura y brillo solar. Estos datos se recopilan cada día. ¿Cómo se podría representar estos datos usando el modelo ER? 5. Interprete los requisitos de los datos que llevaron a construir el siguientes esquema conceptual:

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

39

Universidad de Costa Rica, Universidad Nacional

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

40

Universidad de Costa Rica, Universidad Nacional

RESUMEN DE NOTACIÓN BÁSICA PARA LOS DIAGRAMAS ER

o identificadora

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

41

Universidad de Costa Rica, Universidad Nacional

NOTACIONES ALTERNATIVAS PARA LOS DIAGRAMAS ER (Figura A.1 del libro de texto)

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

42

Universidad de Costa Rica, Universidad Nacional

La BD COMPAÑÍA puede ser representada usando la notación alternativa de la siguiente forma:

a.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

43

Universidad de Costa Rica, Universidad Nacional

RELACIONES TERNARIAS Las relaciones ternarias representan situaciones donde es indispensable la aparición simultánea de las tres entidades en la relación. A menudo resulta difícil decidir si una cierta relación se debe representar como una relación ternaria o si debe descomponer en varias relaciones binarias. Esta decisión debe basarse en la semántica de la situación que se está modelando. En general, un tipo de relación ternaria representa distinta información que tres tipos de relación binaria. Por ejemplo, el siguiente tipo de relación ternaria

no es equivalente a tres tipos de relaciones binarias:

Suponga que cada proyecto sólo usa un proveedor por componente. Esto se puede representar fácilmente usando el tipo de relación ternaria SUMINISTRAR. Por ejemplo, las instancias de relación {d1, p1, c1}, {d2, p1, c2} y {d2, p2, c1} indican que los dos proveedores d1 y d2 suministran el componente c1, pero lo hacen a diferentes proyectos (p1 y p2) y aunque pueden suministrar al mismo proyecto (p1), ofrecen diferentes componentes (c1 y c2). De esta manera la restricción semántica de un solo proveedor por componente por proyecto se satisface.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

44

Universidad de Costa Rica, Universidad Nacional

Si en lugar de usar un tipo de relación ternaria, se usaran tres tipos de relaciones binarias, podríamos incurrir en pérdida de información. Por ejemplo, las instancias {d1, p1, c1} {d2, p1, c2} y {d2, p2, c1} de la relación ternaria se representarían en las relaciones binarias así: {d1, p1}, {d2, p1} y {d2, p2} en la relación SUMINISTRA, {p1, c1}, {p1, c2} y {p2, c1} en la relación UTILIZA, y {d1, c1}, {d2, c2} y {d2, c1} en la relación PUEDE_SUMINISTRAR. Para saber cuál proveedor suministra qué componente a cuál proyecto, hay que unir la información proveniente de las tres tipos de relaciones. Esta unión produce las siguientes instancias: {d1, p1, c1}, {d2, p1, c1}, {d2, p1, c2} y {d2, p2, c1}. El esquema con tres tipos de relaciones binarias produce pérdida de información porque indica que existen dos proveedores d1 y d2 que suministran el componente c1 al proyecto p1, lo cual viola la restricción semántica original. Consecuentemente, al tomar la decisión de descomponer la relación ternaria en varias binarias, debe considerarse si hay pérdida de información. Si no la hay, debe analizarse la semántica de la situación específica, la flexibilidad y la eficiencia que ofrece el esquema para realizar las consultas de los usuarios. Algunas herramientas para el diseño de bases de datos se fundamentan en variantes del modelo ER que sólo permiten utilizar relaciones binarias. En tales casos, una relación ternaria debe ser representada como un tipo de entidad débil sin llave parcial y con tres relaciones identificadoras:

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

45

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – RELACIONES TERNARIAS 1. ¿Cómo cambiaría el diseño considerando los ejemplos del punto 1 con los requerimientos ligeramente modificados?: a. Profesores ofreciendo cursos en diferentes semestres. b. Médicos atendiendo a sus pacientes en diferentes consultorios. c. Los recursos naturales con las rutas de acceso a ellos y zonas de riesgo donde se encuentran tanto los recursos naturales como las rutas de acceso. 2. ¿Cómo modelaría las siguientes situaciones? 3. a. Datos de parcelas, sus dueños y de los responsable de su registración. b. Entrevistas de trabajo para diferentes departamentos de la compañía con la información sobre cuál trabajo se ofreció. 4. Interpreta los tipos de las relaciones ternarias e indica si se puede cambiarlas por una serie de tipos de relaciones binarias.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

46

Universidad de Costa Rica, Universidad Nacional

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

47

Universidad de Costa Rica, Universidad Nacional

ESPECIALIZACION Y GENERALIZACION Con frecuencia un tipo de entidad tiene subgrupos de entidades significativos que deben ser modelados explícitamente (por ejemplo, las entidades Empleado pueden agruparse en secretaria, técnico, ingeniero, gerente, entre otros. Estos subgrupos se llaman subclases y el tipo de entidad se llama superclase. A la relación entre la superclase y una de sus subclases se le llama superclase/subclase o simplemente clase/subclase (también conocida como ISA o ES-UNA). Una entidad que es miembro de una subclase también debe pertenecer a la superclase (por ejemplo, una secretaria también es una empleada). Es decir, una entidad no puede existir sólo como miembro de una subclase. Sin embargo, no es necesario que una entidad de la superclase sea miembro de alguna subclase.Una entidad miembro de una subclase hereda todos los atributos y relaciones de la superclase.
Especialización Proceso de definir un conjunto de subclases de un tipo de entidad (top-down). Hay dos razones para crear especialización: 1. Ciertos atributos aplican a algunas de las entidades de la superclase pero no a todas. Ejemplo: rapidez de tecleo en secretaria. 2. En algunos tipos de relación sólo pueden participar entidades que sean miembros de la subclase. Ejemplo: técnico se puede relacionar con las computadoras que repara. El proceso de especialización: • • •

Define el conjunto de subclases de una entidad. Asocia atributos específicos con cada subclase. Establece relaciones específicas adicionales entre cada subclase y otras entidades.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

48

Universidad de Costa Rica, Universidad Nacional

Ejemplo de diferentes especializaciones (figura 4.1 del libro de texto con las modificaciones de representación):

ST

DP DT


Generalización El proceso inverso de la especialización es la generalización. Es el proceso de definir una clase generalizada (superclase) a partir de tipos de entidades dadas (bottom-up). La generalización se realiza eliminando las diferencias entre varios tipos de entidades e identificando sus rasgos comunes. Existen dos razones para crear una generalización: 1. Algunos atributos son comunes a varias entidades. 2. Las mismas relaciones se aplican a entidades similares.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

49

Universidad de Costa Rica, Universidad Nacional

Ejemplo de diferentes especializaciones (figura 4.3 del libro de texto con las modificaciones de representación):

DT


Restricciones de especialización y generalización 1. Restricción de disyunción (disjointness): especifica que las subclases son disjuntas, es decir, que una entidad miembro de una subclase no puede pertenecer a otra subclase de la especialización. Cuando esta restricción no existe (que es el caso por omisión o default), los conjuntos de entidades de las subclases pueden solaparse, de manera que una misma entidad puede ser miembro de más de una subclase de la especialización. Ejemplo: La relación ISA entre EMPLEADO, EMPLEADO ASALARIADO y EMPLEADO POR HORAS es disjunta: los empleados sólo pueden pertenecer a una de las subclases. 2. Restricción de integridad total o parcial: cuando es total especifica que cada entidad de la superclase es también miembro de al menos una subclase. Cuando es parcial, se permite que una entidad de la superclase no pertenezca a ninguna subclase. Ejemplo: La relación ISA entre EMPLEADO, EMPLEADO ASALARIADO y EMPLEADO POR HORAS es total: no existen empleados a quienes se les pague de otra forma que no sea por horas o por salario fijo.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

50

Universidad de Costa Rica, Universidad Nacional

Las dos restricciones son independientes y dan origen a cuatro combinaciones que debe considerarse para cada relación ISA: 1. Disyunción, total (DT) 2. Disyunción, parcial (DP) 3. Solapamiento, total (ST) 4. Solapamiento, parcial (SP) Estas restricciones se deben marcar en el esquema con las letras correspondientes en la línea que une la clase con sus subclases. .

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

51

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – ESPECIALIZACIÓN Y GENERALIZACIÓN 1. ¿Cómo modelarías las siguientes situaciones, incluyendo las restricciones especificadas anteriormente? a. Centros educativos que pueden ser nocturnos o diurnos, privados o públicos. b. El personal de una unidad académica incluyendo el profesorado, los estudiantes y personal administrativo, donde los profesores pueden ser estudiantes del nivel de maestría. c. Parcelas que pueden ser para la construcción comercial, industrial, habitacional o escolar. 2. ¿Cuáles ejemplos especialización?

de

su

profesión

podría

modelarse

usando

la

3. Interprete el significado de las relaciones ISA presentes en las siguientes figuras (la primera es una parte de la figura 3.20 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

52

Universidad de Costa Rica, Universidad Nacional

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

53

Universidad de Costa Rica, Universidad Nacional

RECOMENDACIONES GENERALES PARA EL DISEÑO DEL ESQUEMA USANDO EL MODELO ER 1. Determine primero las entidades y sus atributos, distinguiendo bien entre estos dos elementos. 2. Especifique las relaciones basándose en las consultas requeridas por el usuario. Comience con las relaciones que responden a la consulta más frecuente del usuario. 3. Revise que no existan relaciones redundantes. Si se encuentran, elimine las que no se utilizan en las consultas más frecuentes y/o las que “pasan” por muchas entidades cuando existe otra forma de responder la consulta abarcando menos entidades. Se debe tener cuidado de no eliminar las relaciones que parecen redundantes pero cuya eliminación provoca pérdida de información. 4. Analice la posibilidad de convertir las relaciones ternarias en binarias sin incurrir en pérdida de información. 5. Si existen relaciones 1:1 entre dos entidades, analice la posibilidad de que una de ellas se convierta en atributo de la otra. 6. Especifique los atributos de las relaciones y revise si éstos pueden migrar hacia alguna de las entidades participantes. 7. Determine las razones de cardinalidad para las relaciones binarias. 8. Determine la participación para las relaciones binarias. 9. Cuando confeccione el diagrama, ponga en la esquina superior izquierda o en el centro las entidades que tengan más relaciones. 10. Especifique las relaciones basándose en las consultas requeridas por el usuario. Comience con las relaciones que responden a la consulta más frecuente del usuario.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

54

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS DE DISEÑO PENDIENTE: 1. Se está construyendo una base de datos para llevar la organización de los equipos y los juegos de una liga deportiva. Cada equipo tiene varios jugadores, aunque no todos participan en un juego dado. Se desea llevar el control de los jugadores que participan en cada juego por parte de cada equipo, de las posiciones que ocuparon en el juego y del resultado del mismo. Diseñe un esquema ER para esta aplicación, describiendo las suposiciones que haga (si necesarios). 2. Diseñe un esquema de ER para mantener información sobre los votos emitidos en la Cámara de Representantes de EE.UU. La base de datos necesita mantener el nombre de cada estado de EE.UU., por ejemplo, Texas, New York, Calofornia e incluye la Región del estado, cuyo dominio es {Noreste, Medioeste, Sudeste, Sudoeste, Oeste}. Cada congresista de la Cámara de Representantes se describe por su nombre, se incluye el distrito representado, la fecha de inicio de su primera elección, y el partido político al cual pertenece, cuyo dominio es {Republicano, Demócrata, Independiente, Otros}. La base de datos mantiene cada proyecto de ley e incluye el nombre del proyecto, la fecha de votación del proyecto de ley, si el proyecto de ley se aprobó o no (dominio {Si, No}) y el proponente (congresista(s) que presentó el proyecto de ley). La base de datos mantiene que voto emitió cada congresista en cada proyecto (dominio del atributo voto es {Si, No, Abstencion, Ausente}). Anote claramente cualquier suposición adicional que se haga. 3. Diseñe un esquema ER para mantener los datos referentes a la colección de hongos, sus características y localización. Cada hongo se describe por medio de su nombre común, especie, género, edad y tamaño. Estos datos se recopilan considerando diferentes troncos en diferentes épocas de tiempo. Cada tronco está caracterizado por su diámetro, longitud, circunferencia mínima y máxima como también la condición en general. Los troncos se localizan en diferentes bosques identificados por su nombre y área. En caso necesario, anote suposiciones adicionales que fueron necesarios para realizar este diseño. 4. Describa algunos escenarios de tu ámbito de trabajo que te gustaría modelar para guardar los datos necesarios. 5. Aunque en este curso no vamos a utilizar UML, basándose en su conocimiento de dos diferentes notaciones usadas para el modelo ER, ¿se pueden entender los siguientes esquemas representados en UML?:

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

55

Universidad de Costa Rica, Universidad Nacional

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

56

Universidad de Costa Rica, Universidad Nacional

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

57

Universidad de Costa Rica, Universidad Nacional

PARTE III: EL MODELO RELACIONAL El modelo relacional, propuesto por Ted Codd (de IBM Research) en 1970, se basa en el concepto de relación matemática, y utiliza la teoría de conjuntos y la lógica de predicado de primer orden. Informalmente una relación es una tabla de valores con columnas y filas. Cada fila de la tabla representa una colección de datos o valores relacionados entre sí, como los datos que describen una entidad o una relación. El nombre de la tabla y de las columnas se usa para ayudar a interpretar el significado de los datos contenidos en las filas de la tabla. Ejemplo (figura 5.2 del libro de texto):

DEFINICIONES
Atributo (columna o campo) Es un encabezado de columna, que describe el significado de los valores de la columna.
Dominio Es un conjunto de valores atómicos definido para cada atributo. Se denota D o dom(A) donde A es un atributo. Un dominio tiene una definición lógica (que describe su rango) y un tipo de datos o un formato. Ejemplos: • dom (EDAD) = [0,100], número entero (int) • dom(SALARIO) = [10000.00,5000000.00], número real (float) • dom(NOMBRE) = cadena de caracteres (string) • dom(TELEF) = cadena de 8 dígitos, formato: dddd-dddd
Tupla (registro o fila) Informalmente, es una fila de la relación. Formalmente es una lista ordenada de n valores t = , donde cada valor vi pertenece al dominio dom(Ai) del atributo Ai, para 1 ≤ i ≤ n.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

58

Universidad de Costa Rica, Universidad Nacional


Esquema de relación (o intensión) Es la descripción de la relación que especifica el nombre de la relación y sus atributos. Se denota R(A1, A2,..., An) donde R es el nombre de la relación y Ai es el nombre del i-ésimo atributo.
Relación o estado de relación (tabla o extensión) Es un conjunto de tuplas r(R) = { t1, t2, t3, ..., tm } del esquema R.
Grado de una relación Es el número de atributos de su esquema de relación. Ejemplo: El grado de la relación ESTUDIANTE en la figura anterior es 7 porque su esquema contiene siete atributos.

MÁS FORMALMENTE La definición formal de relación (o estado de relación) se basa en el producto cartesiano de conjuntos. En particular, el producto cartesiano D1 x D2 de dos conjuntos de valores D1 y D2 se define como el conjunto de todos los pares ordenados (v1, v2) donde v1 ∈ D1 y v2 ∈ D2. Ejemplo: Si D1 = {2,4} y D2 = {1,3,5}, entonces D1 x D2 = {(2,1), (2,3), (2,5), (4,1), (4,3), (4,5)} Esta definición de producto cartesiano se puede generalizar a n conjuntos de valores D1,D2, ..., Dn, de la siguiente forma: D1 x D2 x ... x Dn = {(v1,v2, ...,vn) | v1 ∈ D1, v2 ∈ D2, …, vn ∈ Dn} Una relación r(R) se define entonces como un subconjunto del producto cartesiano de los dominios de los atributos de R: r(R) ⊆ (dom(A1) x dom(A2) x … x dom(An)) Ejemplo: r(R) = {(2,1), (4,3)} es una relación sobre los dominios de atributo D1 y D2 definidos en el ejemplo anterior. Resumen Dado el esquema de relación R (A1, A2, ..., An) con dominios de atributo dom(A1), dom(A2),..., dom(An): • • •

R(A1, A2, …, An) es el esquema de la relación R de grado n. R es el nombre de la relación. A1, A2, …, An son los atributos de la relación.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

59

Universidad de Costa Rica, Universidad Nacional

•

r(R) es un estado específico (extensión) de la relación R y se especifica como un conjunto de tuplas (filas o registros) o r(R) = { t1, t2,..., tm } ⊆ (dom(A1) x dom(A2) x … x dom(An)) donde cada ti es una n-tupla con vj ∈ dom(Aj) para 1 ≤ j ≤ n.

•

Un atributo A puede calificarse con el nombre de la relación R a la cual pertenece usando la notación R.A. Esto se usa para distinguir atributos que tienen el mismo nombre pero pertenecen a distintas relaciones.

CARACTERÍSTICAS DE LAS RELACIONES  Ordenamiento de las tuplas: Una relación está definida como un conjunto de tuplas, por lo que las tuplas de una relación no tienen ningún orden entre sí. Sin embargo, al guardarse físicamente en un archivo, las tuplas tienen un orden.  Ordenamiento de los valores dentro de las tuplas: Una tupla se define como una lista ordenada de valores, lo que significa que el orden es importante. Sin embargo, a un nivel lógico este ordenamiento no es necesario siempre que mantenga una correspondencia entre atributos y valores; por ejemplo, una tupla podría definirse como un conjunto de parejas (, ). Cuando una relación se implementa como archivo, los atributos quedan ordenados físicamente como campos dentro de un registro.  Valores atómicos: Todo valor en una tupla es atómico. Esto significa que los atributos multivalor y compuestos no son permitidos.  Valores nulos: Existe un valor especial llamado null que puede usarse cuando el valor del atributo no se conoce o no aplica para una tupla.

MÁS DEFINICIONES
Llave primaria de una relación Es la mínima cantidad de atributos que permite en forma única cada tupla de la relación, o sea, dos tuplas cualesquiera tendrán una combinación de valores distintos para esos atributos. La llave elige el diseñador de la BD como forma principal de identificar a las tuplas de la relación.
Esquema de una base de datos relacional Es el conjunto de todos los esquemas de las relaciones que conforman la base de datos S = {R1, R2,..., Rp }.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

60

Universidad de Costa Rica, Universidad Nacional

Ejemplo (la figura 5.5 del libro de texto):


Estado de la base de datos Es el conjunto de todos las tuplas de las relaciones que conforman la base de datos DB = {r(R1), r(R2),..., r(Rp)}. Ejemplo (la figura 5.6 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

61

Universidad de Costa Rica, Universidad Nacional

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

62

Universidad de Costa Rica, Universidad Nacional

RESTRICCIONES DEL MODELO RELACIONAL  Restricción del dominio Especifica que para cada tupla, el valor de su atributo A debe ser un valor atómico del dominio dom(A).  Restricción de llave Todas las tuplas de una relación deben ser distintas (por la definición de relación como conjunto). Esto implica que ninguna combinación de dos tuplas tendrá los mismos valores para todos los atributos.  Restricción de integridad de entidad Ninguno de los valores correspondientes a los atributos de la llave primaria puede ser nulo en una tupla.  Restricción de integridad referencial Se especifica entre dos relaciones y se usa para mantener la consistencia entre las tuplas de ambas relaciones. Informalmente, esta restricción establece que una tupla de una relación que haga referencia a otra relación deberá referirse a una tupla existente de esa relación. La integridad referencial se basa en el uso de llaves externas (foreign keys). Una llave externa FK de una relación R1 que referencia a otra relación R2 es un conjunto de atributos de R1 cuyo dominio es el mismo que el de los atributos de la llave primaria de R2, y cuyo valor para toda tupla del estado de relación r(R1) es igual al valor de la llave primaria de alguna tupla del estado de relación r(R2). Ejemplo (la figura 5.7 del libro de texto).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

63

Universidad de Costa Rica, Universidad Nacional

 Restricciones de la integridad semántica Están basadas en la semántica de la realidad modelada y se deben implementar dentro de las aplicaciones ya que no pueden ser expresadas por el modelo relacional. Estas restricciones se pueden especificar usando disparadores (triggers) y aserciones (assertions) en SQL-99. Ejemplos: • El salario de un empleado no debe exceder el salario de su supervisor • El número máximo de horas que un empleado puede trabajar a la semana es 40.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

64

Universidad de Costa Rica, Universidad Nacional

ALGORITMO DE MAPEO DEL MODELO EER AL MODELO RELACIONAL PASO 1: Mapeo de tipos de entidad regulares Para cada tipo de entidad regular (no débil) E en el esquema ER, crear una relación R que incluya todos los atributos simples de E. Si E tiene atributos compuestos, incluir en R sólo los atributos componentes simples de cada atributo compuesto. Elegir uno de los atributos llave (llave candidata) de E como la llave primaria de R. Si la llave que se eligió es compuesta, el conjunto de los atributos simples que la forman deben ir juntos como llave primaria de R. Ejemplo: la figura 7.3a del libro de texto.

PASO 2: Mapeo de tipos de entidad débiles Para cada tipo de entidad débil W en el esquema ER, crear una relación R que incluya todos los atributos simples (o los componentes simples de un atributo compuesto) de W. Incluir como llave externa en R los atributos que son llave primaria de la(s) relación(es) correspondientes a la(s) entidad(es) fuerte(s) de la(s) cual(es) depende W. La llave primaria de R estará compuesta por la combinación de la(s) llave(s) primaria(s) de la(s) entidad(es) fuerte(s) de la(s) que depende W y la llave parcial de W, si existe. Ejemplo: la figura 7.3b del libro de texto.

PASO 3: Mapeo de tipos de relación binaria 1:1 Para cada tipo de relación binaria 1:1 R del esquema ER, identificar las relaciones S y T (creadas en Paso 1 o 2) correspondientes a los tipos de entidad participantes en R. Elegir la relación que corresponda al tipo de entidad con participación total en R, por ejemplo S (si ambos tipos de entidad tienen participación total en R, elegir cualquiera). Incluir como llave externa en S la llave primaria de T. Incluir todos los atributos simples (o los componentes simples de los atributos compuestos) del tipo de relación R como atributos de S. Ejemplo: la figura 5.5 del libro de texto

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

65

Universidad de Costa Rica, Universidad Nacional

PASO 4: Mapeo de tipos de relación binaria 1:N Para cada tipo de relación binaria 1:N R (no identificadora) del esquema ER, identificar la relación S correspondiente al tipo de entidad el lado N de R. Incluir como llave externa en S la llave primaria de la relación T, que representa el otro tipo de entidad participante en R. Incluir los atributos simples (o los componentes simples de los atributos compuestos) del tipo de relación R como atributos de S. Ejemplo: la figura 5.5 del libro de texto

PASO 5: Mapeo de tipos de relación binaria M:N Para cada tipo de relación binaria M:N R del esquema ER, crear una nueva relación S. Incluir como llaves externas en S las llaves primarias de las relaciones correspondientes a tipos de entidad participantes en R. La combinación de estas llaves externas forma la llave primaria de S. Incluir los atributos simples (o los componentes simples de los atributos compuestos) de la relación R como atributos de S. Ejemplo: la figura 7.3c del libro de texto.

PASO 6: Mapeo de atributos multivalor Para cada atributo multivalor A, crear una nueva relación R que incluya el atributo A, e incluir como llave externa en R la llave primaria K de la relación correspondiente al tipo de entidad que tiene el atributo multivalor A. La llave primaria de R es la combinación de A y K. Si el atributo multivalor es compuesto, se deben incluir sus componentes simples y todos formarían parte de la llave, junto con K. Ejemplo: la figura 7.3d del libro de texto

PASO 7: Mapeo de los tipos de relación n-arios Para cada tipo de relación n-aria R (con n>2) en el esquema ER, crear una nueva relación S. Incluir como llave externa en S las llaves primarias de las relaciones que representan los tipos de entidad participantes en R. Incluir los atributos simples (o los componentes simples de los atributos compuestos) de la relación R como atributos de S. La llave primaria de S es usualmente la combinación de todas las llaves externas. Ejemplo: la figura 7.4 del libro de texto.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

66

Universidad de Costa Rica, Universidad Nacional

PASO 8: Mapeo de la especialización o generalización Convierta cada especialización de m subclases {S1,S2,…,Sm} y una superclase C con atributos {k,a1,…,an} (donde k es la llave primaria), en esquemas de relación de acuerdo a una de las siguientes opciones: •

Opción 8A - Varias relaciones de superclase y subclase: Crear una relación L para la superclase C con atributos Attrs(L) = {k, a1, …, an} donde k es la llave primaria. Crear una relación Li para cada subclase Si (1 ≤ i ≤ m), con atributos Attrs(Li) = {k} ∪ Attrs(Si) donde k es la llave primaria de Li. Esta opción funciona bien para cualquiera de las restricciones de especialización: disjunta, solapada, total o parcial. Ejemplo: las figuras 4.1 (modificada) y 7.5 a del libro de texto

•

Opción 8B - Varias relaciones de subclase: Crear una relación Li para cada subclase Si (1 ≤ i ≤ m), con atributos Attrs(Li) = Attrs(Si) ∪ {k, a1, …, an} donde k es la llave primaria de Li. Esta opción sólo funciona bien cuando la especialización es disjunta y total.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

67

Universidad de Costa Rica, Universidad Nacional

Ejemplo: las figuras 4.3 (modificada) y 7.5b del libro de texto

•

Opción 8C - Una sola relación con un atributo de tipo: Crear una relación simple L con atributos Attrs(L) = {k, a1, …, an} ∪ Attrs(S1) ∪ … ∪ Attrs(Sm) ∪ {t} donde k es la llave primaria de L, y t es un tipo de atributo que indica la subclase a la cual cada tupla pertenece. Funciona bien para especializaciones cuyas subclases son disjuntas. Puede potencialmente generar un gran número de valores nulos. Ejemplo: la figura 7.5c del libro de texto

•

Opción 8D – Una sola relación con varios atributos de tipo: Crear una relación simple L con atributos Attrs(L) = {k, a1, …, an} ∪ Attrs(S1) ∪ … ∪ Attrs(Sm) ∪ {t1, t2, …, tm} donde k es la llave primaria de L y cada ti (con 1 ≤ i ≤ m) es un atributo booleano que indica si una tupla pertenece a la subclase Si. Funciona bien para especializaciones cuyas subclases son solapadas. Ejemplo: las figuras 4.5 (modificada) y 7.5d del libro de texto

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

68

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – CREACIÓN DE ESQUEMAS RELACIONALES 1. Pase los siguientes esquemas ER a esquemas relacionales:

Diagrama ER para la base de datos Banco.

Diagrama ER para base de datos de cine.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

69

Universidad de Costa Rica, Universidad Nacional

Diagrama ER para la base de datos de los movimientos de barcos.

Diagrama ER para una parte de la base de datos de control de aviones

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

70

Universidad de Costa Rica, Universidad Nacional

Diagrama ER para la base de datos Conferencia.

2. Aplique ingeniería inversa y construya el esquema ER a partir del siguiente esquema relacional:

Esquema relacional para la BD BIBLIOTECA.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

71

Universidad de Costa Rica, Universidad Nacional

PARTE IV: DEPENDENCIAS FUNCIONALES Y NORMALIZACIÓN DE BASES DE DATOS RELACIONALES Hasta este momento asumimos que los atributos se agrupan en las relaciones usando el sentido común del diseñador de la base de datos o usando el mapeo del modelo ER a modelo relacional. Sin embargo, no tenemos ninguna herramienta que permita medir cuál agrupamiento de los atributos es mejor y porqué. Una guía informal de verificación de la calidad de diseño de una base de datos relacional incluye los siguientes elementos:  Semántica de los atributos de la relación La semántica indica cómo se interpretan los atributos guardados en la tupla, es decir, cómo los valores de la tupla se relacionan entre sí. Si es posible explicar con facilidad la semántica, es probable que el diseño sea bueno. Así pues, cada vez que agrupamos atributos, tenemos que revisar que tengan sentido común o alguna relación entre sí. Ejemplo: Los siguientes son ejemplos de diseños pobres con atributos combinados de varios tipos de entidades o relaciones (la figura 10.3 del libro de texto). EMP_DPTO NOMBREE

CED

FECHAN

DIRECCION

NUMEROD

NOMBRED

CEDGTED

EMP_PROY CED

NUMEROP

HORAS

NOMBREE

NOMBREP

LUGARP

GUIA 1: Diseñe un esquema relacional cuyo significado sea fácil de explicar; en particular no combine atributos de diferentes tipos de entidades o de tipos de relaciones.  Información redundante en tuplas y anomalías de actualización Una de las metas del diseño es minimizar el espacio para guardar las relaciones. El agrupamiento de atributos en esquemas de relación influye directamente en la cantidad de espacio necesario para guardar la información (ver ejemplo anterior de la relación EMP_DEPT y las relaciones EMPLEADO y DEPARTAMENTO de la BD COMPAÑIA con respecto a la repetición de la información).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

72

Universidad de Costa Rica, Universidad Nacional

La repetición de información da origen a las siguientes anomalías: • Anomalía de inserción: o Al insertar un empleado en la relación EMP_DEPT tenemos que insertar información sobre el departamento, aunque el empleado no haya sido asignado aún a algún departamento. Además, se necesita insertar la información sobre el departamento correctamente para asegurar la consistencia de los datos. o Al insertar un nuevo departamento se necesita asignarle por lo menos un empleado porque el campo de CED es la llave primaria y por tanto no puede tener un valor nulo. •

Anomalías de borrado: Al borrar el último empleado de un departamento también borraramos el departamento porque no podemos mantener la información del departamento con un valor nulo (de empleado) en la llave primaria.

•

Anomalías de modificación: Si cambiamos el valor de algunos de los atributos de un departamento, tenemos que cambiarlo en todas las tuplas que representan empleados de ese departamento, para asegurarnos la consistencia de los datos.

GUIA 2: Diseñe la base de datos de forma que no ocurran anomalías de actualización, esto es, de inserción, borrado y modificación.  Valores nulos en tuplas Si en un diseño agrupamos muchos atributos que no tienen una relación clara entre ellos, podemos terminar con muchos valores nulos en las tuplas de esa relación. Esto conlleva a desperdicio de espacio de almacenamiento y problemas a la hora de entender/interpretar el significado de los atributos en diferentes operaciones aplicadas a estos datos. Además, como ya vimos anteriormente los valores nulos pueden tener múltiples interpretaciones:
El atributo no se aplica a esta tupla.
El valor del atributo para esta tupla es desconocido.
El valor del atributo es conocido pero todavía no se ha recopilado. Ejemplo Si sólo el 10% de los empleados tienen oficinas individuales, no se justificará incluir un atributo NUM_OFICINA en la relación EMPLEADO, más bien, podríamos crear una relación OFICINAS_EMPS (CED, NUM_OFICINA) que contenga exclusivamente tuplas para los empleados con oficina individual.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

73

Universidad de Costa Rica, Universidad Nacional

GUIA 3: Hasta donde sea posible evite poner atributos cuyos valores puedan ser nulos. Si los valores nulos son inevitables, asegúrese que sean usados sólo en casos excepcionales y no sean aplicados a la mayoría de las tuplas de la relación.  Tuplas falsas (descomposición con pérdida de información o reunión aditiva, spurious tuples) Ejemplo Considere las siguientes extensiones de relación: EMP_PLOC:

NOMBRE Juan Juan María María Ana

LUGARP Zapote San Pedro Desamparados Zapote Zapote

EMP_PROY1:

CED 1-111-111 1-111-111 2-222-222 2-222-222 3-333-333

NUMEROP 1 2 3 1 1

HORAS 12 15 10 11 18

NOMBREP Invest1 Invest2 Invest3 Invest1 Invest1

LUGARP Zapote San Pedro Desamparados Zapote Zapote

Si quisiéramos unir (join) las dos tablas, tendríamos que usar el único atributo común a las dos, que es LUGARP (ubicación del proyecto). La tabla resultante sería: CED NUMEROP 1-111-111 1 2-222-222 1 3-333-333 1 1-111-111 2 2-222-222 3 1-111-111 1 2-222-222 1 3-333-333 1 1-111-111 1 2-222-222 1 3-333-333 1

HORAS 12 11 18 15 10 12 11 18 12 11 18

NOMBREP Invest1 Invest1 Invest1 Invest2 Invest3 Invest1 Invest1 Invest1 Invest1 Invest1 Invest1

LUGARP Zapote Zapote Zapote San Pedro Desamparados Zapote Zapote Zapote Zapote Zapote Zapote

NOMBRE Juan Juan * Juan * Juan María María * María María * Ana * Ana * Ana

(*) tuplas incorrectas

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

74

Universidad de Costa Rica, Universidad Nacional

El problema radica en que el atributo común a las dos tablas (LUGARP) no es ni la llave primaria ni la llave externa para ninguna de las dos relaciones. Aunque en este caso se produjeron más tuplas de lo necesario, se perdió información válida.

GUIA 4: Diseñe los esquemas de relación de manera que la operación “join” entre las relaciones se realice sobre parejas de llave primaria y llave externa. Así se garantiza que no se produzcan nuevas tuplas incorrectas o se pierdan algunas correctas. Las anteriores son guías informales para un buen diseño y pueden detectarse sin herramientas adicionales de análisis. Existe una guía más formal para el diseño de bases de datos relacionales: el proceso de normalización, que usa como base las dependencias funcionales. Este proceso define con mayor precisión qué tan bueno o malo es un esquema de relación.

DEPENDENCIAS FUNCIONALES Dependencias funcionales (DF) es el concepto más importante en el diseño de esquemas relacionales y sirve de base al proceso de normalización, es cual construye BDs relacionales sin anomalías de actualización. Una dependencia funcional es una restricción entre dos conjuntos de atributos de la BD. Se dice que Y es función de X, Y = f(X), si el valor de Y está siempre determinado por el valor de X. Aplicando esta terminología a una relación: el atributo B es funcionalmente dependiente del atributo A si el valor de B está determinado por el valor de A (AB). Es decir, dada una relación universal (con todos los atributos) R (A1, A2,..., An), el atributo Ak es funcionalmente dependiente del atributo Ai (AiAk), si y solo si cada valor de Ak en R tiene asociado exactamente un valor de Ai en R (en cualquier instancia). En otras palabras: Dada una relación R, el atributo Ak es funcionalmente dependiente del atributo Ai si para todas las parejas t1 y t2 de las tuplas donde t1[Ai] = t2[Ai], también es cierto que t1[Ak] = t2[Ak]. Esto implica que si existe la dependencia funcional AiAk y si dos tuplas en R tienen el mismo valor del atributo Ai, también tienen que tener el mismo valor para el atributo Ak. Se dice que Ai determina de manera única los valores de Ak o que hay una dependencia funcional de Ai a Ak, o que Ak depende funcionalmente de Ai. Ejemplo

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

75

Universidad de Costa Rica, Universidad Nacional

A a1 a1 a2 a2 a3

B b1 b2 b2 b3 b3

C c1 c1 c2 c2 c2

D d1 d2 d2 d3 d3

Defina cuáles DF existen: A ? B ? D ? BC  ? CD  ?

Podemos notar que: 1. Si las restricciones dicen que no podemos tener más de una tupla con el valor Ai en una instancia de R (llave candidata), esto implica que AiAk para cualquier subconjunto de valores de Ak, o sea, la llave candidata es el mínimo número de atributos que en forma directa o indirecta determinan todos los demás atributos de la relación. 2. AiAk no nos dice nada acerca de AkAi. Una DF es una propiedad de la semántica de la relación. Se necesita comprensión del sistema a modelar (o su extensión no la intensión - esquema) para especificar las DF. Siempre que la semántica de dos conjuntos de atributos de R indique que debe mantenerse una DF, la especificamos como una restricción. Ejemplos: la figura 10.3 del libro de texto (a) EMP_DPTO NOBREE

CED

FECHAN

DIRECCION

NUMEROD





df1



NOMBRED



NSSGTED

df2

(b) EMP_PROY CED

NUMEROP

HORAS

NOMBREE

NOMBREP

LUGARP

df1 df2

Diseño y explotación de bases de datos

df3

Dra. Elzbieta Malinowski

Material de apoyo

76

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – DEFINICIÓN DE DEPENDENCIAS FUNCIONALES 1. Interpreta para un usuario no experto las siguientes dependencias funcionales. • Provincia→ Región organizacional • Clinica→Provincia • Codigo de cantón → Nombre de cantón 2. Indique si existen dependencias funcionales y en el caso afirmativo cuáles. • Un río puede pasas por varios cantones y por un cantón pueden pasar varios ríos. • Distrito pertenece a solo un cantón y un cantón puede tener muchos distritos. • Una propiedad puede pertenecer a una municipalidad (cierto en nuestro país?) y una municipalidad puede tener registradas muchas propiedades. • Un pueblo indígena puede vivir en varias reservas indígenas. • Un río puede estar ubicado solo en una cuenca y una cuenca puede tener varios ríos. • Un cliente puede tener varias cuentas y una cuenta puede pertenecer a varios clientes • Una persona puede tener varios teléfonos, pero un teléfono sólo puede ser registrado a nombre de una persona . 3. Basándose en los siguientes requerimientos, determine las dependencias funcionales existentes. • La universidad guarda un registro de cada nombre de estudiante, carnet, dirección actual y número telefónico actual, dirección permanente y número de teléfono permanente, fecha de nacimiento, sexo, departamento al cual pertenece. El carné es valor único para cada estudiante. • Cada departamento es descrito por su nombre, código, número de oficina, teléfono y escuela. Ambos, el nombre y el código son únicos para cada departamento. • Cada curso tiene un nombre, descripción, sigla, número de horas por semestre, nivel. La sigla es única para cada curso y cada curso se imparte solo en un departamento. .

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

77

Universidad de Costa Rica, Universidad Nacional

EL PROCESO DE NORMALIZACION El proceso de normalización fue propuesto por E. Codd (1972) y consiste en pasar el esquema de relación por una serie de transformaciones para llegar a alguna de las formas normales. Durante este proceso la relación se descompone en varias relaciones más pequeñas que poseen las propiedades deseadas, según la forma normal. En general, el objetivo de la normalización es minimizar la redundancia (duplicación innecesaria de datos) y evitar las anomalías de actualización.

PRIMERA FORMA NORMAL (1FN) Definición El dominio de los atributos tiene que incluir sólo valores atómicos y el valor de cualquier atributo en la tupla tiene que ser un elemento del dominio de ese atributo. La primera formal normal no permite que una relación tenga atributos compuestos ni multivalor ni relaciones anidadas. Ejemplo 1 (la figura 10.8 del libro de texto) (a) DEPARTAMENTO (no está en 1FN) NOMBRED

NUMEROD

CEDGTE

LUGARESD







(b) DEPARTAMENTO (archivo en forma no plana) NOMBRED Contabilidad Administración Cómputo

NUMEROD 5 4 1

CEDGTE 1234567 1558214 4534536

LUGARESD {Bellaire, Sugarland, Houston} {Stafford} {Houston}

(c) DEPARTAMENTO (archivo en forma plana, 1FN) NOMBRED Contabilidad Contabilidad Contabilidad Administración Cómputo

NUMEROD 5 5 5 4 1

LUGARESD {Bellaire} {Sugarland} {Houston} {Stafford} {Houston}

CEDGTE 1234567 1234567 1234567 1558214 4534536

Ejemplo 2 (la figura 10.9 del libro de texto) (a) EMP_PROY CED

NUMEROP

HORAS

NOMBREE

NOMBREP

LUGARP

fd1 fd2

Diseño y explotación de bases de datos

fd3

Dra. Elzbieta Malinowski

Material de apoyo

78

Universidad de Costa Rica, Universidad Nacional

(b) EMP_PROY (archivo en forma no plana) CED 6668844 4534536 2876532 3334455

1234567 2877772 1558214 3876543

NUMEROP 1 2 3 1 2 2 3 10 20 2 10 10 30 2 20 30

HORAS 32.5 7.5 40 20 20 10 10 10 10 20 10 35 5 7.5 15 20

NOMBREE Narayan, Ramesh English, Joyce Jabbar, Ahmad Wong, Frank

Smith, John B. Hernandez, Luis Bong, James Wallace, Jennifer

NOMBREP ProyectoX ProyectoY ProyectoZ ProyectoX ProyectoY ProyectoY ProyectoZ Automatización Reorganización PruyectoY Automatización Automatización Nuevasprestaciones ProyectoY Reorganización Nuevasprestaciones

LUGARP Belén Sacramento Higueras Belén Sacramento Sacramento Higueras Santiago Higueras Sacramento Santiago Santiago Santiago Sacramento Sacramento Santiago

NOMBREP ProyectoX ProyectoY ProyectoZ ProyectoX ProyectoY ProyectoY ProyectoZ Automatización Reorganización PruyectoY Automatización Automatización Nuevasprestaciones ProyectoY Reorganización Nuevasprestaciones

LUGARP Belén Sacramento Higueras Belén Sacramento Sacramento Higueras Santiago Higueras Sacramento Santiago Santiago Santiago Sacramento Sacramento Santiago

(c) EMP_PROY (archivo en forma plana, 1FN), CED 6668844 6668844 4534536 2876532 2876532 3334455 3334455 3334455 3334455 1234567 1234567 2877772 2877772 1558214 1558214 3876543

NUMEROP 1 2 3 1 2 2 3 10 20 2 10 10 30 2 20 30

HORAS 32.5 7.5 40 20 20 10 10 10 10 20 10 35 5 7.5 15 20

NOMBREE Narayan, Ramesh Narayan, Ramesh English, Joyce Jabbar, Ahmad Jabbar, Ahmed Wong, Frank Wong, Frank Wong, Frank Wong, Frank Smith, John B. Smith, John B. Hernandez, Luis Hernandez, Luis Bong, James Bong, James Wallace, Jennifer

Solución para obtener 1FN: Cuando los atributos de las relaciones contienen valores no atómicos (i.e., conjunto de valores), para obtener la 1FN se necesita “aplanar” los archivos. ¿Qué anomalías pueden existir si la tabla del ejemplo 3 se presenta en 1FN? Ejemplo 3 CLIENTE NumCliente

NumInv

Fecha

NomCliente

CiudadCliente

PrecioEntrega

PrecioUnitario

Cantidad

fd1

fd2 fd3 |___________ fd4

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

79

Universidad de Costa Rica, Universidad Nacional

Problemas:

• •

Redundancia de los datos Anomalías de inserción, borrado y modificación (especifique cuáles)

SEGUNDA FORMA NORMAL (2FN) Para definir la 2FN es necesario conocer dos conceptos: • Atributo primo: atributo que forma parte de alguna llave candidata. (atributo no-primo es aquel que no forma parte de ninguna llave candidata). • DF total: dependencia X → Y en la cual la eliminación de cualquier atributo de X invalida la dependencia (una DF parcial es aquella que no es total). Ejemplo En la tabla EMP_PROY en Ejemplo 2 anterior {CED, NUMEROP} → HORAS es una DF total porque ni {CED} → HORAS ni {NUMEROP} → HORAS son válidas. Sin embargo, la DF {CED, NUMEROP} → NOMBREE es parcial porque {CED} → NOMBREE. Definición Una relación está en 2FN si está en 1FN y cada atributo no-primo de la relación es total y funcionalmente dependiente de cada llave candidata (cuando sólo hay una llave candidata, ésta se convierte en la llave primaria y entonces los atributos no-primos deben depender total y funcionalmente de la llave primaria). Ejemplos (los anteriores de CLIENTE y EMP_PROY) Ninguna de estas relaciones está en 2FN porque existen las dependencias parciales de la llave. ¿Cuáles? Solución para obtener 2FN: 1. Quitar de la relación en 1FN los campos no-primos que no dependen totalmente y funcionalmente de alguna llave candidata. 2. Colocar cada campo no-primo removido en el paso 1, en una nueva relación que contenga también el subconjunto de atributos de la llave candidata original del cual dependa total y funcionalmente el campo no-primo. EMP_PROY (figura 10.10a del libro de texto):

tabla 1: { CED, NUMEROP }  HORAS

tabla 2: CED  NOMBREE

tabla 3: NUMEROP  NOMBREP, LUGARP

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

80

Universidad de Costa Rica, Universidad Nacional

CLIENTE:

tabla 1: NumCliente  NomCliente, CiudadCliente, PrecioEntrega |__________ tabla 2: NumInv  PrecioUnitario tabla 3: { NumCliente, NumInv, Fecha }  Cantidad Problemas:

•

Todavía pueden existir problemas de redundancia de datos y actualizaciones. Ejemplos • La relación EMP_PROY no tiene problemas. • La relación CLIENTE tiene problemas de redundancia, anomalías de borrar, insertar y modificar. Por ejemplo, no se puede insertar el precio de entrega si no hay un cliente que compre algo o si se borra el último cliente desaparece la información de precio de entrega.

TERCERA FORMA NORMAL (3FN) Definición Una relación R está en 3FN si está en 2 FN y (informalmente) ningún atributo no-llave es funcionalmente dependiente de algún otro atributo no-llave. 3FN es más estricta que 2FN, es decir, si una relación está en 3FN también está en 2FN, pero no viceversa. Solución para obtener 3FN: Descomponga las relaciones donde no exista la dependencia de no-llave. Ejemplos (la figura 10.10b del libro de texto): (a) EMP_DPTO NOMBREE

CED



FECHAN



DIRECCION



NUMEROD

NOMBRED

NSSGTED







tabla a1: EMP_DPTO1 NOMBREE

CED



FECHAN

DIRECCION





NUMEROD



tabla a2: EMP_DPTO2 NUMEROD

NOMBRED



NSSGTED



Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

81

Universidad de Costa Rica, Universidad Nacional

(b) CLIENTE

tabla b1: NumCliente  NomCliente, CiudadCliente tabla b2: CiudadCliente  PrecioEntrega

PROCEDIMIENTO GENERAL (INFORMAL) PARA LA NORMALIZACION Relación R1 (A, B, C, E, F, G, H, I) PASO1: Construir un diagrama de dependencias de acuerdo con la semántica de los datos R1

PASO 2: Separar no-llaves que no son totalmente dependientes de la llave principal R2

R3

R4

PASO 4: Eliminar dependencia transitiva en R5 R2

R5

Diseño y explotación de bases de datos

R6

R4

Dra. Elzbieta Malinowski

Material de apoyo

82

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – PROCESO DE NORMALIZACIÓN Verifique en qué forma normal están las tablas de datos de diferentes shapefiles. Si no están en 3FN proponga adecuado diseño siguiendo los pasos de normalización. •

Shapefile de distritos

•

Shapefile de cantones

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

83

Universidad de Costa Rica, Universidad Nacional

•

Shapefile de salud

•

Shapefile de referendum 2007

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

84

Universidad de Costa Rica, Universidad Nacional

•

•

Shapefile de hoteles (con que se relaciona, se necesita relaciones topológicas para saber en qué distrito está)

Shapefile de colegios

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

85

Universidad de Costa Rica, Universidad Nacional

PARTE V: LENGUAJE COMERCIAL – SQL SQL - Structured Query Language (Lenguaje de Consultas Estructurado) es un lenguaje estándar de las DBs relacionales. La estandarización comenzó en el año 1986 y se ha venido efectuando periódicamente, con la última revisión hecha en el 2008. El nombre original de SQL es SEQUEL y fue diseñado y desarrollado por IBM Reseach en el sistema “System R”. SQL es un lenguaje que cuenta con sentencias para crear, consultar y actualizar las BDs relacionales, por lo tanto se comporta como DDL y como DML. Además, tiene sentencias para definir vistas en la BD, especificar aspectos de seguridad y autorización, definir las restricciones de integridad referencial, entre otros. SQL usa las siguientes convenciones para referirse a elementos del modelo relacional: Tabla: Columna: Fila:

se refiere a la relación se refiere a un atributo se refiere a una tupla

Aunque las tablas son la unidad básica de manejo de datos, existen otras estructuras en la cuales las tablas están “incrustadas” como se presenta en la siguiente figura. Base de datos Catálogo Esquema Tablas y vistas Tuplas y columnas

La creación de base de datos y esquemas es responsabilidad de administrador de la BD.

Data Manipulation Language (Lenguaje de Manipulación de Datos) de SQL Data Manipulation language (DML) de SQL tiene una instrucción básica para recuperar la información desde la base de datos: SELECT.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

86

Universidad de Costa Rica, Universidad Nacional

La opción más simple de SELECT es: SELECT < lista de atributos > FROM < lista de tablas > Ejemplo SELECT FROM

NOMBRE EMPLEADO;

A esta opción se puede añadir la condición WHERE que indica la condición quen deben cumplir algunos valores en los atributos para recuperar las tuplas: SELECT FROM WHERE

< lista de atributos > < lista de tablas > < condición >

< Lista de atributos> Lista de nombres de los atributos cuyos valores se necesitan recuperar por la consulta < Lista de tablas > Lista de nombres de las relaciones que participan en la consulta < Condición > La expresión condicional (booleana) que identifica las tuplas para su recuperación. Ejemplo SELECT NOMBRE FROM EMPLEADO E WHERE E.SEXO = ‘M’;

SELECT FORMA GENERAL SELECT FROM WHERE GROUP BY HAVING ORDER BY

< lista de atributos > < lista de tablas > < condición > < atributos de la agrupación > < condición de grupo > < lista de atributos >

SELECT FROM

Especifica los atributos o funciones para el despliegue Todas las relaciones necesarias para la consulta, incluyendo relaciones para join. Especifica la condición para la selección de tuplas individuales Especifica el (los) atributos de agrupación Especifica la (s) condiciones para el grupo seleccionado, no para las tuplas individuales. Sólo se usa cuando existe “Group by” Especifica en qué orden se despliegan las tuplas

WHERE GROUP BY HAVING

ORDER BY

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

 opcionales

Material de apoyo

87

Universidad de Costa Rica, Universidad Nacional

El orden en su uso es: 1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. ORDER BY

CONSULTAS SQL Y SUS DIFERENTES OPCIONES: EJEMPLOS Para escribir las consultas se debe analizar primero cuáles tablas contiente los atributos que usuario necesita desplegar. 1. Seleccione todos los NSS de los empleados SELECT NSS FROM EMPLEADO; Respuesta de acuerdo a la base de datos COMPAÑIA

2. Recupere la fecha de nacimiento y la dirección del empleado “José Silva” SELECT FECHAN, DIRECCION FROM EMPLEADO WHERE NOMBRE = ’José’ AND APELLIDO=’Silva’ El sistema recorre todas las tuplas de la tabla EMPLEADO y verifica si la condición en la cláusula WHERE se cumple. Si la respuesta es verdadera (true) se despliega la información solicitada en la cláusula SELECT. Si es falsa (false) se pasa a verificar la siguiente tupla. Respuesta de acuerdo a la base de datos COMPAÑIA

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

88

Universidad de Costa Rica, Universidad Nacional

3. Selecciones todos los atributos de la relación Empleado para el departamento #5 SELECT * FROM EMPLEADO WHERE ND=5; Respuesta de acuerdo a la base de datos COMPAÑIA EMPLEADO NOMBRE

APELLIDO

NSS

FECHAN

DIRECCION

José

IN IC B

Silva

123456789

09-ENE-55

Federico

T

Vizcarr

333445555

08-DIC-45

Ramón

K

Nieto

666884444

15-SEP-52

Josefa

A

Esparza

453453453

31-JUL-62

Fresnos 731, Higueras, MX Valle 638, Higueras, MX Espiga 875, Heras, MX Rosas 5631, Higueras, MX

SE XO M

SALA RIO 30000

NSS SUPER 333445555

ND

M

40000

888665555

5

M

38000

333445555

5

F

25000

333445555

5

5

4. Seleccione los salarios de los empleados SELECT SALARIO FROM EMPLEADO; Respuesta de acuerdo a la base de datos COMPAÑÍA Salario 30000 40000 25000 43000 38000 25000 25000 55000

SQL no elimina las tuplas repetidas en el resultado de una consulta. Para eliminarlas se debe usar la opción DISTINCT. SELECT DISTINCT SALARIO FROM EMPLEADO; Respuesta de acuerdo a la base de datos COMPAÑIA Salario 30000 40000 25000 43000 38000 55000

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

89

Universidad de Costa Rica, Universidad Nacional

Si se requiere obtener los resultados ordenados, se puede incluir la cláusula ORDER BY: SELECT DISTINCT SALARIO FROM EMPLEADO; ORDER BY SALARIO 5. Recuperar todos los empleados cuya dirección se encuentra en Higueras. SELECT NOMBRE, APELLIDO FROM EMPLEADO WHERE DIRECCION LIKE ‘%Higueras%’; El operador LIKE permite comparar partes de las cadenas de caracteres. Las otras partes que no son de interés se puede representar por medio de: • % -reemplaza cualquier número de caracteres • _ - reemplaza un carácter • [a-i] - reemplaza un carácter en el rango de a hasta i.

6. Recuperar todos los empleados del departamento 5 cuyo salario está entre 30000 y 40000. SELECT * FROM EMPLEADO WHERE (SALARIO BETWEEN 30000 AND 40000) AND ND = 5 La condición SALARIO BETWEEN 30000 AND 40000 es equivalente a (SALARIO >= 30000) AND (SALARIO < = 40000) Considerando las instancias, indique cuáles tuplas se recuperan en esta consulta. 7. Presente todas las combinaciones entra las tablas de empleado y departamento desplegando los números de cédula de los empleados y los nombres de los departamentos SELECT E.NSS, D.NOMBRED FROM EMPLEADO E, DEPARTAMENTO D;

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

90

Universidad de Costa Rica, Universidad Nacional

Note el uso de alias (E para Empleado y D para Departamento) que facilita la distinción a cuales tablas pertenecen los atributos. En el caso que las tablas tienen atributos con el mismo nombre, el uso de los alias es obligatorio. Respuesta de acuerdo a la base de datos COMPAÑIA NSS 123456789 333445555 999887777 987654321 666884444 453453453 987987987 888665555 123456789 333445555 999887777 987654321 666884444 453453453 987987987 888665555 123456789 333445555 999887777 987654321 666884444 453453453 987987987 888665555

NOMBRED Investigación Investigación Investigación Investigación Investigación Investigación Investigación Investigación Administración Administración Administración Administración Administración Administración Administración Administración Dirección Dirección Dirección Dirección Dirección Dirección Dirección Dirección

Se puede notar que se obtuvo todas las combinaciones posibles entre las tuplas de las dos tablas (producto cartesiano), pero se perdió la información en cual departamento trabaja cada empleado. Para obtener la respuesta correcta la cláusula WHERE debe tener la condición de igualdad entre la llave primaria de la tabla padre y su correspondiente llave foránea en la tabla hija. Esta operación tiene nombre de JOIN y es la más importante operación en bases de datos relacionales. SELECT E.NSS, D.NOMBRED FROM EMPLEADO E, DEPARTAMENTO D WHERE E.ND = D.NUMEROD; 8. Recupere los nombres y direcciones de todos los empleados que trabajan para el departamento “Investigación” Analice primero que tablas se necesita para responder a esta consulta. Después especifica las condiciones requeridas por el usuario (departamento "Investigación") y al final, si es necesario usar más que una tabla, aplicar la operación join sobre los atributos correspondientes. SELECT E.NOMBRE, E.APELLIDO, E.DIRECCION FROM EMPLEADO E, DEPARTAMENTO D WHERE D.NOMBRED= ’Investigación’ AND D.NUMEROD=E.ND;

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

91

Universidad de Costa Rica, Universidad Nacional

Respuesta de acuerdo a la base de datos COMPAÑIA NOMBRE José Federico Ramón Josefa

APELLIDO Silva Vizcarr Nieto Esparza

DIRECCION Fresnos 731, Higueras, MX Valle 638, Higueras, MX Espiga 875, Heras, MX Rosas 5631, Higueras, MX

Nota que la cláusula WHERE contiene condiciones de join y adicionalmente cualquier otra condición necesaria para la consulta. 9. Mostrar los salarios aumentados un 10% de todos los empleados que trabajan en el proyecto Producto X Antes de verificar como se escribe esta consulta, revise el esquema relacional de la base de datos COMPAÑÍA y observe que es necesario usar la información de las tablas de Empleado y Proyecto. Para unir estas tablas por medio de las operaciones join se podría usar dos opciones si solo se considera la existencia de llaves foráneas necesarias para la operación join de tablas correspondientes: a. Empleado, Departamento, Proyecto b. Empleado, Trabaja_en, Proyecto ¿Cuál de estas opciones es correcta y por qué?

SELECT FROM WHERE AND

E.NOMBRE, E.APELLIDO, 1.1*SALARIO AS NUEV_SUELDO EMPLEADO E, TRABAJA_EN T, PROYECTO P E.NSS = T.NSSE AND T.NUMP = P.NUMEROP P.NOMBREP = ‘PRODUCTO X’

Se puede aplicar los operadores aritméticos estándar (+, -, *, /) a valores o atributos con dominios numéricos. 10. Para cada proyecto localizado en “Santiago” especifique el número de proyecto, el número de departamento que lo controla, el nombre, la dirección y fecha de nacimiento del gerente de ese departamento. SELECT FROM WHERE AND AND

P.NUMEROP, P.NUMD, E.APELLIDO, E.DIRECCION, E.FECHAN PROYECTO P, DEPARTAMENTO D, EMPLEADO E P.NUMD=D.NUMEROD D.NSSGTE=E.NSS P.LUGARP = ‘Santiago’

No existe límite de tablas para hacer las operaciones de join. Sin embargo, más tablas para unirlos, mayor tiempo de ejecución de la consulta

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

92

Universidad de Costa Rica, Universidad Nacional

11. Para cada empleado especifique su nombre y el del inmediato supervisor. Para responder a esta consulta la misma tabla se considera jugando dos roles: una vez para obtener la información de los empleados y la segunda vez para obtener la información de los supervisores. Por esta razón, esta tabla aparece dos veces en la cláusula where con diferentes alias (E de empleados y S de supervisores) SELECT E.NOMBRE, E.APELLIDO, S.NOMBRE, S.APELLIDO FROM EMPLEADO E, EMPLEADO S WHERE E.NSSSUPER = S.NSS En general, alias son necesarios cuando: • Se accede dos o más veces la misma tabla jugando diferentes papeles • Cuando se accede atributos con el mismo nombre pertenecientes a diferentes tablas • En consultas correlacionadas (vemos más adelante) • En cualquier situación de ambigüedad con respecto a cual tabla pertenecen los atributos. 12. Preparar una lista con todos los nombres de los proyectos en los que participa el empleado ‘Silva’ como trabajador SELECT FROM WHERE AND AND

P.NOMBREP TRABAJA_EN T, EMPLEADO E, PROYECTO P E.APELLIDO = ‘Silva’ T.NSSE = E.NSS T.NUMP = P.NUMEROP

La misma consulta se puede escribir de otra forma usando los SELECT anidados, o sea, un SELECT donde la condición WHERE contiene otro SELECT. Además, se usa el operador IN que compara un valor v con un conjunto de valores y devuelve verdadero si el valor v está incluido en el conjunto de valores. SELECT P.NOMBREP FROM PROYECTO P WHERE P.NUMEROP IN (SELECT T.NUMP FROM TRABAJA_EN T, EMPLEADO E WHERE T.NSSE=E.NSS AND E.APELLIDO= ´Silva´) Esta consulta obtiene valores de números de proyectos donde trabaja Silva (los valores de1,2) en la ejecución de SELECT interno, transformando posteriormente la consulta en algo como:

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

93

Universidad de Costa Rica, Universidad Nacional

SELECT P.NOMBREP FROM PROYECTO P WHERE P.NUMEROP IN (1,2) El SELECT externo recorre la tabla de PROYECTO y verifica número por número si está dentro de conjunto de valores 1 o 2. Si la condición es verdadera devuelve el nombre, en este caso ProductoX y ProductoY 13. Preparar una lista con todos los nombres de los proyectos en los que participa el empleado ‘Silva’, sea como trabajador o como gerente del departamento que controla ese proyecto. Antes de considerar la consulta presente a continuación analice cuáles tablas necesitas usar para obtener los datos solicitados. SELECT P.NOMBREP FROM TRABAJA_EN T, EMPLEADO E, PROYECTO P WHERE E.APELLIDO = ‘Silva’ AND T.NSSE = E.NSS AND T.NUMP = P.NUMEROP UNION (SELECT P.NOMBREP FROM PROYECTO P, DEPARTAMENTO D, EMPLEADO E WHERE P.NUMD = D.NUMEROD AND D.NSSGTE = E.NSS AND E.APELLIDO = ´Silva`) La consulta anterior utiliza operación de conjunto por medio de UNION. Otras operaciones de conjuntos son INTERSECT (la intersección) y EXCEPT (la diferencia) 14. Recupere los nombres de cada empleado que tiene una persona dependiente de él con el mismo nombre y sexo. SELECT FROM WHERE

E.NOMBRE, E. APELLIDO EMPLEADO E E.NSS IN (SELECT D.NSSE FROM DEPENDIENTE D WHERE D.NSSE = E.NSS E.NOMBRE = AND D.NOMBRE_DEPENDIENTE AND D.SEXO = E.SEXO )

Este tipo de consultas se llama consultas correlacionadas debido a que una condición de la cláusula WHERE se refiere a algún atributo de una tabla (relación) declarada en la consulta exterior. En el ejemplo anterior, la Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

94

Universidad de Costa Rica, Universidad Nacional

condición WHERE del SELECT interno se refiere al atributo NSS de la relación EMPLEADO declarada en el SELECT externo. En este ejemplo, se recorre la tabla de EMPLEADO y para cada empleado se verifica en la tabla de DEPENDENTE si la condición de WHERE se cumple. En caso verdadera, se despliega el nombre de este empleado. En caso falso, se procede con el siguiente empleado. Esta misma consulta puede escribirse usando sólo operaciones de concatenación (join): SELECT FROM WHERE AND

E.NOMBRE, E. APELLIDO EMPLEADO E, DEPENDIENTE D E.NSS = D.NSSE AND E.SEXO = D.SEXO E.NOMBRE = D. NOMBRE_DEPENDIENTE;

También se puede usar el operador EXISTS, que permite comprobar si el resultado de una consulta anidada correlacionada es vacío (no contiene tuplas) o no. El resultado de EXISTS es un valor booleano: TRUE si hay por lo menos una tupla en el resultado de la consulta y FALSE en el caso contrario. SELECT FROM WHERE

E.NOMBRE, E.APELLIDO EMPLEADO E EXISTS (SELECT * FROM DEPENDIENTE D WHERE E.NSS = D.NSSE AND D.SEXO = E.SEXO AND E.NOMBRE = D.NOMBRE_DEPENDIENTE);

15. Recupere los nombres de los empleados que no tienen dependientes. Esta consulta requiere el uso de NOT EXIST. SELECT FROM WHERE

E.NOMBRE, E.APELLIDO EMPLEADO E NOT EXISTS (SELECT * FROM DEPENDIENTE D WHERE E.NSS = D.NSSE);

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

95

Universidad de Costa Rica, Universidad Nacional

16. Recupere el nombre de los gerentes que tienen al menos un dependiente. SELECT FROM WHERE

E.NOMBREP, E.APELLIDO EMPLEADO E EXISTS (SELECT * FROM DEPENDIENTE D WHERE E.NSS = D.NSSE) AND EXISTS (SELECT * FROM DEPARTAMENTO DE WHERE E.NSS = DE.NSSGTE)

Como el operador EXISTS devuelve el resultado booleano se puede combinar con los operadores AND y OR. 17. Recupere los nombres de los empleados que no tienen supervisor SELECT FROM WHERE

NOMBRE, APELLIDO EMPLEADO NSSSUPER IS NULL

SQL permite consultas que comprueban si el valor de un atributo es NULL por medio de los operaciones IS NULL o IS NOT NULL. Si esta comparación se hace dentro de una condición compuesta, se debe conocer las reglas para AND, OR y NOT con los tres valores TRUE, FALSE y UNKNOWN (NULL), como se presenta a continuación: AND TRUE FALSE UNKNOWN

TRUE TRUE FALSE UNKNOWN

FALSE FALSE FALSE FALSE

UNKNOWN UNKNOWN FALSE UNKNOWN

OR TRUE FALSE UNKNOWN

TRUE TRUE TRUE TRUE

FALSE TRUE FALSE UNKNOWN

UNKNOWN TRUE UNKNOWN UNKNOWN

NOT TRUE FALSE UNKNOWN

TRUE FALSE TRUE UNKNOWN

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

96

Universidad de Costa Rica, Universidad Nacional

DIFERENTES FORMAS DE JOIN El concepto de tabla concatenada (relación concatenada) se introdujo en SQL para poder especificar la operación join en la cláusula FROM y otras condiciones en el cláusula WHERE. 18. Recupere los nombres y direcciones de todos los empleados que trabajan para el departamento “Investigación”. Es la misma consulta que presentada en el punto 8, escrita ahora usando tablas concatenadas: SELECT E.NOMBRE, E.APELLIDO, E.DIRECCION FROM EMPLEADO E JOIN DEPARTAMENTO D ON D.NUMEROD=E.ND WHERE D.NOMBRED = ’Investigación’ En lugar de JOIN se puede especificar INNER JOIN. Si los nombres de los atributos de join son iguales, se puede especificar NATURAL JOIN sin incluir la condición ON.

19. Para cada proyecto localizado en “Santiago” especifique el número de proyecto, el número de departamento que lo controla, el nombre, la dirección y fecha de nacimiento del gerente de ese departamento. Es la misma consulta que presentada en el punto 8, escrita ahora usando tablas concatenadas (más que dos). SELECT P.NUMEROP, P.NUMD, E.APELLIDO, E.DIRECCION, E.FECHAN FROM PROYECTO P JOIN DEPARTAMENTO D ON P.NUMD=D.NUMEROD JOIN EMPLEADO E ON D.NSSGTE=E.NSS WHERE P.LUGARP = ‘Santiago’

FUNCIONES AGREGADAS Y AGRUPACIONES 20. Encuentre la suma y el promedio de los salarios de los empleados, así como su valor máximo y mínimo. SELECT FROM

SUM (SALARIO), MAX(SALARIO), MIN(SALARIO), AVG(SALARIO) EMPLEADO;

SQL permite usar las funciones de agrupación de valores: SUM, MAX, MIN, AVG y COUNT. La última devuelve el número de tuplas o valores específicos en una consulta. Estas funciones se pueden usar en las cláusulas SELECT y HAVING (la veremos más tarde).

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

97

Universidad de Costa Rica, Universidad Nacional

21. Recupere el número total de empleados y el número de empleados que trabajan en el departamento de “Investigación”. SELECT FROM

COUNT (*) EMPLEADO;

SELECT FROM WHERE

COUNT (*) EMPLEADO E, DEPARTAMENTO D E.ND=D.NUMEROD AND D.NOMBRED = ‘Investigación’;

22. Seleccione el número de salarios diferentes de la relación empleado. SELECT FROM

COUNT (DISTINCT SALARIO) EMPLEADO;

23. Recupere los nombres de los empleados que tienen dos o más dependientes. SELECT FROM WHERE

E.NOMBRE, E.APELLIDO EMPLEADO E (SELECT COUNT(*) FROM DEPENDIENTE D WHERE E.NSS = D.NSSE ) ≥ 2;

24. Para cada departamento recupere el número de departamento, el número de empleados y el salario promedio. SELECT ND, COUNT (*), AVG(SALARIO) FROM EMPLEADO GROUP BY ND; La cláusula GROUP BY permite crear subgrupos de tuplas para las cuales se aplican las funciones de agregación especificadas en la cláusula SELECT. Muchos sistemas obligan poner en la cláusula SELECT el atributo de agrupamiento usado en la cláusula GROUP BY. Si existen valores nulos para los atributos de agrupamiento, se crea un grupo separado para este valor. 25. Para cada proyecto recupere el número del proyecto, el nombre del proyecto y el número de empleados que trabajan en el proyecto. SELECT FROM WHERE GROUP BY

P.NUMEROP, P.NOMBREP, COUNT (*) PROYECTO P, TRABAJA_EN T P.NUMEROP = T.NUMP P.NUMEROP, P.NOMBREP

La cláusula GROUP BY puede incluir más de un atributo de agrupamiento.

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

98

Universidad de Costa Rica, Universidad Nacional

26. Para cada proyecto en el que trabajan más de dos empleados recupere el número del proyecto, el nombre del proyecto y el número de empleados que trabajan en él. SELECT FROM WHERE GROUP BY HAVING

P.NUMEROP, P.NOMBREP, COUNT (*) PROYECTO P, TRABAJA_EN T P.NUMEROP = T.NUMP P.NUMEROP, P.NOMBREP COUNT(*) > 2;

La cláusula HAVING se usa para especificar las condiciones de grupos (creados por medio de GROUP BY), o sea, primero agrupa por cada proyecto y después verifica si hay más que dos empleados en este proyecto. En caso verdadero, despliega la información, en el caso contrario, verifica otro grupo creado. Note que la cláusula HAVING es la condición para el grupo en contraste a WHERE que se aplica a tuplas individuales. La cláusula HAVING no puede existir sin su correspondiente cláusula GROUP BY.

ORDENAMIENTO 27. Recupere los nombres de los empleados y los proyectos en los cuales trabajan, ordenados por departamento y dentro de cada departamento ordenados alfabéticamente por apellido y nombre SELECT FROM

D.NOMBRED, E.APELLIDO, E.NOMBRE, P.NOMBREP DEPARTAMETO D, EMPLEADO E, PROYECTO P, TRABAJA_EN T WHERE D.NUMEROD = E.ND AND E.NSS=T.NSSE AND T.NUMP=P.NUMEROP ORDER BY D.NOMBRED, E.APELLIDO, E.NOMBRE El orden de las tuplas especificadas en la cláusula ORDER BY se presenta en forma ascendente, por defecto. Si se necesita cambiar este comportamiento se puede incluir la palabra DESC. Una cláusula ORDER BY puede combinar ordenamiento ascendente y descendente, por ejemplo: ORDER BY NOMBRED DESC, APELLIDO ASC, NOMBRE ASC

Diseño y explotación de bases de datos

Dra. Elzbieta Malinowski

Material de apoyo

99

Universidad de Costa Rica, Universidad Nacional

PARTE VI: LENGUAJE COMERCIAL – SQL (cont.) Data Definition Language (DDL, Lenguaje de Definición Datos) de SQL La sentencia de creación de base de datos incluye su nombre y diferentes aspectos de almacenamiento físico que dependen del DBMS particular. La versión más simple es crear la base de datos por medio de una de las sentencias: CREATE DATABASE nombre CREATE DATABASE nombre AUTHORIZATION nombre y aceptar la configuración física dada por defecto. Esto permite tener la base de datos al nivel conceptual y archivos físicos que guardan los datos tanto para las consultas de los usuarios como para recuperar el sistema en caso de falla. Al crear la base de datos también se crean tablas (o esquemas de) en el catálogo. Las tablas del catálogo contienen la información sobre todos los elementos que forman la base de datos, eso es, tablas, columnas, índices, vistas, usuarios, privilegios, entre otros. El esquema (o BD) se considera como un contenedor donde las tablas y objetos relacionados pueden estar juntos bajo un nombre asignado por el administrador de la BD. La relación entre el esquema y las tablas que contiene puede ser vista como la relación entre los directorios y los archivos que contiene en algún sistema operativo. Los esquemas son creados usando la sentencia CREATE SCHEMA nombre AUTHORIZATION nombre seguido por las definiciones de las tablas, vistas, dominios y otros objetos. No todos los DBMSs incluyen la opción de crear esquemas (ejemplos de esquemas Anexo 1). Cuando el usuario de la BD no es administrador, normalmente trabaja con la BD previamente creada. Cada vez que accede a la BD desde la máquina de cliente, necesita una conexión, que se puede hacer por medio de uno de los siguientes comandos: use, start, connect, init. Tanto la BD como el esquema pueden ser borrados con el comando DROP y modificados con el comando ALTER.

Diseño y explotación de bases de datos 100

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

CREAR LAS TABLAS La creación de las tablas incluye la especificación de su nombre, el nombre y tipo de sus atributos, y las restricciones. Requerimientos para crear las tablas de la BD: • La base de datos debe existir. • La base de datos para guardar las tablas debe estar activa (el administrador inició todos los procesos requeridos para su funcionamiento). • Se debe tener la autorización debida para hacerlo. La sintaxis simplificada es: CREATE TABLE NombreTabla (atributo1 tipo, tipo, atributo2 … atributon tipo) Los atributos se especifican de primero y a cada uno se le da un nombre, tipo de datos para especificar su dominio de valores, y si es del caso, algunas restricciones. Luego se incluyen las restricciones a nivel de la tabla (por ejemplo, llave primaria), las de integridad de entidades y las de integridad referencial. Los atributos pueden ser de diferentes tipos, por ejemplo: •

Numérico o Números exactos: INTEGER, SMALLINTEGER, NUMERIC(n,m), DECIMAL(n,m) o Números aproximados: FLOAT, REAL, DOUBLE PRECISION

•

Cadena de caracteres o De longitud fija: CHAR(n) o De longitud variable: VARCHAR (n) o CHAR VARYING(n) o Largos: CLOB – character large object

•

Cadena de bits o De longitud fija: BIT(n) o De longitud variable: VARBIT(n) o VARYING BIT (n) o Largos: BLOB – binary large objects

•

Booleano o TRUE, FALSE, UNKOWN

•

Fecha y hora o DATE de 10 posiciones con el formato AAAA-MM-DD y TIME de 8 posiciones con el formato HH:MM:SS

Diseño y explotación de bases de datos 101

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

•

Marca de tiempo o TIMESTAMP incluye los campos de DATE y TIME y adicionalmente 5 posiciones para las fracciones decimales de segundos.

Definición de las tablas de la base de datos COMPAÑÍA: CREATE TABLE EMPLEADO ( NOMBRE VARCHAR(15) NOT NULL, INIC CHAR, APELLIDO VARCHAR(15) NOT NULL NSS NSS_type PRIMARY KEY, FECHAN DATE, DIRECCION VARCHAR(30), SEXO CHAR, SALARIO DECIMAL(10,2), NSSSUPER CHAR(9) FOREIGN KEY REFERENCES EMPLEADO(NSS) ND INT NOT NULL, FOREIGN KEY REFERENCES DEPARTAMENTO(NUMEROD) ); CREATE TABLE DEPARTAMENTO ( NOMBRED VARCHAR(15) NOT NULL, NUMEROD INT NOT NULL CHECK (NUMEROD > 0 AND NUMEROD < 21), NSSGTE CHAR(9) NOT NULL, FECHAINICGTE DATE, PRIMARY KEY (NUMEROD), UNIQUE (NOMBRED), FOREIGN KEY (NSSGTE) REFERENCES EMPLEADO(NSS) ); CREATE TABLE LUGARES_DEPTOS ( NUMEROD INT NOT NULL, LUGARD VARCHAR(15) NOT NULL, PRIMARY KEY (NUMEROD, LUGARD), FOREIGN KEY (NUMEROD) REFERENCES DEPARTAMENTO(NUMEROD) ); CREATE TABLE PROYECTO ( NOMBREP VARCHAR(15) NOT NULL, NUMEROP INT NOT NULL, LUGARP VARCHAR(15), NUMD INT NOT NULL, PRIMARY KEY (NUMEROP), UNIQUE (NOMBREPR) FOREIGN KEY (NUMD) REFERENCES DEPARTAMENTO(NUMEROD) );

Diseño y explotación de bases de datos 102

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

CREATE TABLE TRABAJA_EN ( NSSE CHAR(9) NOT NULL, NUMP INT NOT NULL, HORAS DECIMAL(3,1) NOT NULL, PRIMARY KEY (NSSE, NUMP), FOREIGN KEY (NSSE) REFERENCES EMPLEADO(NSS), FOREIGN KEY (NUMP) REFERENCES PROYECTO (NUMEROP) ); CREATE TABLE DEPENDIENTE ( NSSE CHAR(9) NOT NULL, NOMBRE_DEPENDIENTE VARCHAR(15) NOT NULL, SEXO CHAR, FECHAN DATE, PARENTESCO VARCHAR(8), PRIMARY KEY (NSSE, NOMBRE_DEPENDIENTE), FOREIGN KEY (NSSE) REFERENCES EMPLEADO(NSS) );

Restricciones de tabla En las tablas anteriores vimos cómo se incluyen diferentes tipos de restricciones, tales como NOT NULL, CHECK, UNIQUE, PRIMARY KEY, y FOREIGN KEY. Estas restricciones se pueden especificar para cada atributo de la tabla (como en ejemplo anterior de la tabla EMPLEADO) o se pueden incluir al final de la tabla (como en ejemplo anterior de la tabla TRABAJA_EN). También es posible declararlas como “constraint” y darles un nombre, como se muestra a continuación. CREATE TABLE TRABAJA_EN ( NSSE CHAR(9) NOT NULL, NUMP INT NOT NULL, HORAS DECIMAL(3,1) NOT NULL, CONSTRAINT PKTrabajaEn PRIMARY KEY (NSSE, NUMP), CONSTRAINT FKaEmp FOREIGN KEY (NSSE) REFERENCES EMPLEADO(NSS), CONSTRAINT FKaProy FOREIGN KEY (NUMP) REFERENCES PROYECTO (NUMEROP) );

Además, la restricción de llave foránea puede especificar la acción requerida para las operaciones DELETE y UPDATE. Esto se refiere a la acción que se va a ejecutar sobre la tabla hijo (que tiene la llave foránea) cuando se realiza una operación DELETE o UPDATE sobre la tabla padre (que tiene la llave primaria que es referenciada por la llave externa de la tabla hijo). SQL:99 define cuatro posibles acciones: SET NULL, SET DEFAULT, CASCADE, NO ACTION (REJECT).

Diseño y explotación de bases de datos 103

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

CREATE TABLE EMPLEADO ( ..., ND INT NOT NULL DEFAULT 1, CONSTRAINT CLP_EMP PRIMARY KEY (NSS), CONSTRAINT CLE_SUPEREMP FOREIGN KEY (NSSSUPER) REFERENCES EMPLEADO (NSS) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT CLE_DEPTOEMP FOREIGN KEY (ND) REFERENCES DEPARTAMENTO (NUMEROD) ON DELETE SET DEFAULT ON UPDATE CASCADE ) CREATE TABLE DEPARTAMENTO (..., NSSGTE CHAR(9) NOT NULL DEFAULT “888665555” ..., CONSTRAINT CLP_DEPTO PRIMARY KEY (NUMEROD), CONSTRAINT CLS_DEPTO UNIQUE (NOMBRED), CONSTRAINT CLE_GTEDEPTO FOREIGN KEY (NSSGTE) REFERENCES EMPLEADO(NSS) ON DELETE SET DEFAULT ON UPDATE CASCADE ) CREATE TABLE LUGARES_DEPTOS (..., PRIMARY KEY (NUMEROD, LUGARD), FOREIGN KEY (NUMEROD) REFERENCES DEPARTAMENTO(NUMEROD) ON DELETE CASCADE ON UPDATE CASCADE); CREATE TABLE TRABAJA_EN (…, PRIMARY KEY (NSSE, NUMP), FOREIGN KEY (NSSE) REFERENCES EMPLEADO(NSS) ON DELETE NO ACTION ON UPDATE CASCADE), FOREIGN KEY (NUMP) REFERENCES PROYECTO (NUMEROP) ON DELETE NO ACTION ON UPDATE CASCADE); );

Diseño y explotación de bases de datos 104

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

BORRAR LA TABLA DROP TABLE nombreTabla CASCADE RESTRICT CASCADE: RESTRICT:

Borra la tabla y todas las restricciones y vistas que referencian esta tabla, automáticamente. Solo borrará la tabla si no es referenciada en ninguna restricción (como la llave externa).

Ejemplo DROP TABLE dependientes RESTRICT

MODIFICAR LA TABLA Las posibles acciones de alteración incluyen adición o eliminación de una columna, modificación de la definición de una columna y la adición o eliminación de restricciones de la tabla. ALTER TABLE nombreTabla ADD atributo DROP atributo CASCADE RESTRICT Ejemplos 1. ALTER TABLE empleado ADD job varchar(12) Aparece un atributo nuevo “job” con nulos en él para todas las tuplas. . 2. ALTER TABLE empleado DROP direccion CASCADE Todas las restricciones o vistas que hacían la referencia a la columna dirección se borran. Con la opción RESTRICT sólo se borra la columna si no hay restricciones o vistas que la referencian.

Diseño y explotación de bases de datos 105

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

OPERACIONES DE ACTUALIZACIÓN INSERT 1. Inserte todos los datos de un empleado específico INSERT INTO VALUES

EMPLEADO (‘Ricardo’, ‘C’, ‘Martinez’, ‘653298653’, ‘30-DIC-52, ‘Olmo 98, Cedros, MX’, ‘M’, 37000, ‘987654321’, 4);

Si se insertan valores para todos los atributos, la especificación de los atributos puede omitirse. 2. Inserte los datos específicos de un empleado INSERT INTO VALUES

EMPLEADO (NOMBRE, APELLIDO, NSS) (‘Ricardo’, ‘Martinez’, ‘653298653’);

Cuando se insertan valores para sólo algunos atributos, los atributos deben especificarse. INSERT INTO VALUES

EMPLEADO (NOMBRE, APELLIDO, NSS, ND) (‘RicardO’, ‘Martinez’, ‘653298653’, 2);

La siguiente inserción no es aceptada (falta valor para NSS, declarado como campo NOT NULL): INSERT INTO EMPLEADO (NOMBRE, APELLIDO, ND) VALUES (‘Ricardo’, ‘Martinez’, 2); 3. Creación de una tabla nueva y carga con los valores de otra tabla: CREATE TABLE

INFO_DEPTS (NOMBRE_DEPTO VARCHAR(15), NUM_EMP INTEGER, SAL_TOT INTEGER);

INSERT INTO INFO_DEPTS (SELECT NOMBRED, COUNT(*), SUM(SALARIO) FROM DEPARTAMENTO, EMPLEADO WHERE NUMEROD = ND GROUP BY NOMBRED);

Diseño y explotación de bases de datos 106

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

DELETE 1. DELETE FROM WHERE

EMPLEADO APELLIDO = ’Martinez’

2. DELETE FROM WHERE

EMPLEADO NSS = ‘123456789’;

3. DELETE FROM WHERE

EMPLEADO ND IN (SELECT FROM WHERE

4. DELETE FROM

EMPLEADO

NUMEROD DEPARTAMENTO NOMBRED = ‘Investigación’);

UPDATE 1. Cambie el lugar y el número del departamento que controla proyecto Nº 10 UPDATE SET WHERE

PROYECTO LUGARP = ‘Belén’, NUMD = 5 NUMEROP = 10;

2. Asigne un aumento salarial de 10% a todos los empleados del departamento ‘Investigación’ UPDATE SET WHERE

EMPLEADO SALARIO = SALARIO * 1.1 ND IN (SELECT NUMEROD FROM DEPARTAMENTO WHERE NOMBRED = ‘Investigación’);

Diseño y explotación de bases de datos 107

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

VISTAS Una vista es una tabla que se deriva de otras tablas. Estas otras tablas pueden ser tablas o vistas definidas previamente. Una vista puede representar la tabla virtual, eso es no existe en formato físico o puede ser representada por medios de tablas físicas cuando se crean vistas materializadas. CREAR VISTA: 1. CREATE VIEW AS SELECT FROM

TRABAJADORES NOMBRE, APELLIDO EMPLEADO

2. CREATE VIEW AS SELECT FROM WHERE

TRABAJA_EN_PROYE NOMBRE, APELLIDO, NOMBREP, HORAS EMPLEADO, PROYECTO, TRABAJA_EN NSS = NSSE AND NUMP = NUMEROP;

3. CREATE VIEW

INFO_DEPTO(NOMBRE_DEPTO, NUM_EMP, SAL_TOT) NOMBRED, COUNT(*), SUM(SALARIO) DEPARTAMENTO, EMPLEADO NUMEROD = ND DNOMBRE;

AS SELECT FROM WHERE GROUP BY CONSULTAR VISTA: SELECT FROM WHERE

NOMBREP, APELLIDO TRABAJA_EN_PROYE NOMBREP = ‘ProyectoX’

Cuando se consulta la vista virtual se ejecuta la sentencia SELECT que sirvió de base para crearla y se acceden las tablas físicas que aparecen en la cláusula FROM. De esta forma una vista virtual siempre tiene los datos actualizados de acuerdo al contenido de las tablas bases ACTUALIZAR VISTA: No se puede garantizar que las vistas virtuales se pueden actualizar. En general se puede actualizar una vista virtual cuando: 1. Invoca sólo una posible actualización de la relación base. 2. Si los atributos de la vista contienen la llave primaria de la relación o algún otro atributo definido como no nulo (esto hace mapeo uno a uno de la vista a relación base) Diseño y explotación de bases de datos 108

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Las vistas virtuales definidas usando múltiples tablas (join), agrupando tuplas o usando las funciones normalmente no se pueden actualizar. Las vistas se crean para: • Facilitar las expresión de las consultas debido a que tienen sólo los atributos (de una o varias tablas) que se acceden con mucha frecuencia en aplicaciones. • Reforzar la seguridad debido a que el usuario sólo ve los atributos que se presentan en la vista, no todos los que están en las tablas. • Mejorar desempeño de consultas cuando se requiere agrupaciones y agregaciones de datos (solo para vistas materializadas).

BORRAR VISTA: DROP VIEW

TRABAJA_EN_PROYE;

Diseño y explotación de bases de datos 109

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

EJERCICIOS EN CLASES – PROCESO DE NORMALIZACIÓN Para los siguientes esquemas expresa las restricciones de integridad referencias con respectivas acciones justificando su respuesta

Diseño y explotación de bases de datos 110

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Diseño y explotación de bases de datos 111

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

PARTE VII: ORGANIZACIÓN FÍSICA DE LOS ARCHIVOS El nivel lógico (conceptual) de base de datos es un nivel adecuado para los usuarios. Sin embargo, los sistemas de bases de datos también deben ser diseñados en el nivel físico para mejorar su desempeño.

MEDIOS FÍSICOS DE ALMACENAMIENTO Los medios físicos usados en sistemas informáticos se clasifican en tres categorías principales: • Almacenamiento primario: incluye los medios de almacenamiento muy rápido donde la CPU puede operar. Ejemplos: caché y memoria principal. • Almacenamiento secundario: incluye los medios de almacenamiento que la CPU no puede procesar directamente (los datos deben copiarse en el almacenamiento principal) como se puede ver en la siguiente figura. Son los dispositivos más lentos, por ejemplo, los discos magnéticos. Almacenamiento secundario

Almacenamiento primario - memoria principal Read/Write

BD permanente

•

Manejador de búferes

Read/Write

Búferes de la BD volátil

Almacenamiento terciario: son el medio más lento, por ejemplo, discos ópticos y cintas magnéticas, generalmente usados para los datos más viejos, no requeridos para las consultas actuales.

Estos medios físicos se pueden organizar en la siguiente jerarquía de acuerdo a su velocidad de acceso:

Diseño y explotación de bases de datos 112

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Además de la velocidad de acceso, estos tres tipos de dispositivos se diferencian por el precio, donde el más rápido es más caro. Como las bases de datos en general guardan los datos que exceden las capacidades que ofrece el almacenamiento primario, se requiere la inclusión de estos dos tipos adicionales (o por lo menos un tipo de almacenamiento secundario). Los discos magnéticos soportan el acceso directo a una posición específica y son ampliamente usados en las aplicaciones de bases de datos. DBMS provee un acceso al disco sin que la aplicación o usuario sepan de donde se leen los datos: desde la memoria primaria o secundaria. La siguiente figura se representa un disco en la forma simplificada:

Los datos se guardan en el disco en las unidades llamados bloques (o páginas) que son la unidad básica de transferencia de datos.

Los bloques se establecen durante el formateo del sistema operativo y pueden ser modificados para el uso de BD. Normalmente el bloque oscila en tamaño entre 512 y 8192 bytes. Estos bloques están colocados en los círculos llamados pistas (tracks) en uno o más platos (platters). Las pistas pueden ser grabadas por un lado de plato o por los dos lados. El conjunto de todas pistas con el mismo diámetro se llama cilindro (cylinder). Cada pista está dividida entre sectores, el tamaño de los cuáles es la característica de disco y no puede ser modificada. Para leer o escribir un bloque, es necesario mover el disco a la posición donde el encabezado del disco (disk head) esté colocado encima del bloque Diseño y explotación de bases de datos 113

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

correspondiente. El tiempo para acceder el disco para leer (o escribir) un bloque depende de tres componentes: • Tiempo de búsqueda (seek time): Tiempo requerido para poner la cabeza de lectura/escritura en la pista adecuada. • Tiempo de rotación o latencia (rotational delay): Tiempo de rotación necesario para que el principio del bloque gire hasta posicionarse debajo de la cabeza de lectura/escritura. Más cerca del centro del disco, este tiempo disminuye. • Tiempo de transferencia del bloque (block transfer time): Tiempo necesario para transferir el bloque de la memoria secundaria a la memoria principal. Debido a que todo el dato solicitado por la aplicación debe ser transferido desde el disco a la memoria principal, este tiempo debería ser minimizado. Para lograrlo, se debe considerar como los datos están guardados en el disco, en otras palabras, la organización física de los registros. Si dos registros son frecuentemente accedidos juntos, deberían estar colocados cerca en el disco. Otro aspecto a considerar es contar con los elementos necesarios para disminuir el tiempo de búsqueda de registro de interés, en otras palabras, contar con la existencia de los índices.

ORGANIZACIÓN DE ARCHIVOS Los archivos pueden ser organizados de diferente forma. Para el propósito de este curso, nos enfocamos a dos tipos: archivo secuencial y ordenado. 1. HEAP – ARCHIVO SECUENCIAL Es el tipo de organización más sencillo, en el cual los registros se guardan en el archivo en el mismo orden en que se insertan. Cada nuevo registro se inserta al final del archivo. Características: Insertar Operación muy eficiente. Se copia el último bloque del archivo en el buffer, se inserta el nuevo registro y se reescribe el bloque de nuevo al disco. La dirección del último bloque se guarda en el encabezado del archivo (file header). Buscar Operación muy costosa pues requiere una búsqueda lineal del archivo (scan). En promedio se necesitan leer b/2 bloques para un archivo de b bloques.

Diseño y explotación de bases de datos 114

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Borrar Operación muy costosa. Primero se necesita encontrar el registro a borrar. Una vez encontrado, se pone una marca en el registro para indicar que fue borrado. Es necesario reorganizar el archivo periódicamente para reutilizar el espacio ocupado por los registros borrados. Ordenar Operación muy costosa para archivos grandes. En estos casos se aplican técnicas de ordenamiento externo, como por ejemplo merge externo. Modificar Operación costosa debido a la necesidad de buscar el elemento para su modificación. 2. ARCHIVO ORDENADO Los registros se ordenan físicamente en el archivo usando como base uno de sus campos, llamado “campo de ordenamiento”. Características: Insertar Operación muy costosa pues debe mantener el archivo ordenado, y en promedio se deben mover (leer y reescribir) b/2 bloques para un archivo de b bloques cada vez que se inserta un nuevo registro. Algunas soluciones posibles son: • Reservar espacios sin usar en cada bloque. • Crear un archivo temporal no ordenado de desbordamiento (overflow). Las búsquedas se hacen usando búsqueda binaria en el archivo “master” (ordenado) y búsqueda lineal en el archivo de desbordamiento. Periódicamente se necesita mezclar el archivo de overflow con el master, durante una reorganización. Buscar • Buscar un registro basado en la llave de ordenamiento es muy eficiente (por ejemplo, usando búsqueda binaria solo se requieren leer log2 b bloques para un archivo que contiene b bloques). • Buscar con las condiciones de , =, ≤, ≥, son también muy eficientes ya que el ordenamiento físico de los registros implica que todos los registros que satisfacen la condición están contiguos en el archivo. • Encontrar el siguiente registro en el orden no requiere normalmente accesar otros bloques (a menos que sea el último registro del bloque). • Buscar por un campo que no es el campo de ordenamiento se convierte en acceso secuencial. Borrar Requiere reorganizaciones periódicas. Diseño y explotación de bases de datos 115

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Modificar Operación muy costosa por razones de reorganización. Este tipo de archivo se usa normalmente en el caso de ordenamiento físico por la llave primaria.

ÍNDICES Y SUS TIPOS Independiente de la organización particular de los archivos, en las BDs se usa una estructura de datos adicional llamada índice. El índice permite mejorar el tiempo de consulta sobre una tabla con respecto a alguna condición específica de búsqueda. Las estructuras de índice pueden variar, pero en general siempre requieren dos tipos de datos: el campo de indexación que sirve para el ordenamiento y las direcciones de los bloques de disco donde están los registros con valores Índice primario Representa un archivo ordenado que contiene dos campos: el campo de ordenamiento (o indexación) y una dirección a un bloque del disco. Este índice requiere que el archivo de datos esté físicamente ordenado con respecto al campo de indexación. Normalmente se usa este índice para la llave primaria de la relación. El índice primario es ralo (escaso o sparse) debido a que contiene valores para el primer registro de cada bloque pero no para todos los registros como se puede ver en la siguiente figura:

Diseño y explotación de bases de datos 116

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

El archivo de índice requiere menos bloques para su almacenamiento que el archivo de datos por dos razones: 1. Existen menos entradas del índice que registros de datos. 2. Cada entrada del índice contiene solo dos campos en lugar de tener varios como lo tiene normalmente el archivo de datos. Por esta razón usando el índice para recuperar registros de acuerdo a la condición de búsqueda requiere leer menos bloques que hacer esta búsqueda sin la presencia del índice. Como consecuencia, la consulta se puede ejecutar en menos tiempo.

Diseño y explotación de bases de datos 117

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Índice agrupado (clustered) Se usa cuando los registros están físicamente ordenados por un campo no llave (que no tiene valores distintos para cada registro), llamado campo agrupado. El índice agrupado también es un archivo ordenado con dos campos: el campo de agrupado y un puntero a un bloque del disco. Este índice es ralo (escaso) ya que tiene una entrada para cada valor distinto del campo agrupado como se puede ver en la siguiente figura:

Un archivo de datos puede tener un índice primario o un índice agrupado pero NO ambos. ¿Por qué?

Diseño y explotación de bases de datos 118

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Índice secundario Este índice se usa para acceso ordenado a los registros de un archivo con respecto a un campo que no fue el que usó para ordenar físicamente el archivo. El campo de indexación puede ser un campo llave o no. Puede haber muchos índices secundarios (sobre distintos campos de indexación) para un mismo archivo. La estructura del índice secundario tiene una entrada por cada registro que se encuentra en el archivo de datos, por lo tanto este índice es denso:

Un índice secundario necesita más espacio de almacenamiento y tiempo de búsqueda que un índice primario. Sin embargo, la mejora en el tiempo de búsqueda usando un índice secundario es mayor que en caso de usar un índice

Diseño y explotación de bases de datos 119

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

primario debido a que el archivo de datos no es ordenado y sin índice secundario se necesitan leer TODOS los bloques del archivo. ÍNDICES MULTINIVELES Los índices presentados anteriormente se llaman de un nivel ya que existe solo una tabla que los guarda. Para este tipo de índice la búsqueda de un registro requiere más accesos a bloques de índices que usando índice de multinivel como se muestra en la siguiente figura.

El árbol B+ es una estructura de datos actualmente más usada en los DBMS para almacenar el índice. Este índice aprovecha el tamaño de bloque para disminuir el número de niveles y de esta forma acceder con más rapidez a la dirección del registro donde se encuentra el dato buscado. Árbol B+ tiene dos tipos de elementos (nodos): • Interno (nodos ramas): guarda el valor del campo de indexación y la dirección del siguientes nivel Diseño y explotación de bases de datos 120

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

•

Hoja (nodos de datos): guarda el campo de indexación y la dirección del bloque de datos en el disco donde se encuentra registro buscado.

Ejemplo:

Diseño y explotación de bases de datos 121

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

PARTE VIII: INTRODUCCIÓN A LOS CONCEPTOS DE LA TRANSACCIÓN Procesamiento de las transacciones
Transacción Ejecución de una o varias operaciones que accedan o cambian el contenido de la base de datos dejándola en estado consistente. Por ejemplo, el retiro o depósito de dinero en un cajero automático, modificación de salario de los empleados, actualización de la geometría de una parcela, entre otros. Los límites de una transacción se establecen por medio de las sentencias de BEGIN_TRANSACTION y END_TRANSACTION. Las transacciones pueden formar parte de una aplicación o especificarse implícitamente (por DBMS) como por ejemplo durante las operaciones SQL Insert, Delete o Update. Aunque las transacciones pueden incluir varias operaciones sobre los datos, de nuestro interés son READ y WRITE, que permiten, respectivamente, leer los datos desde la BD y escribirlos. Ejemplos: Transferencia de fondos de la cuenta A a la cuenta B

Depósito a la cuenta A

READ(A); A = A - N; WRITE(A); READ(B); B = B + N; WRITE(B);

READ(A); A = A + M; WRITE(A);

El funcionamiento simplificado de las operaciones de READ y WRITE es el siguiente: read(X) 1. Encuentra la dirección de X en la base de datos. 2. Copia del disco a a memoria principal el bloque que contiene X.

Diseño y explotación de bases de datos 122

write(X) 1. Encuentra la dirección del bloque que contiene X. 2. Si el bloque que contiene X no se encuentra en la memoria principal, lo copia desde el disco a la memoria (o sea, ejecuta una especie de operación read internamente). 3. Modifica su valor. 4. Guarda el bloque modificado al disco (actualiza la BD en disco).

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional


Estados de una transacción Una transacción es una unidad atómica que se completa en su totalidad o no se lleva a cabo en absoluto. Por ejemplo, una transacción de transferencia de fondos de una cuenta a otra, debe ejecutarse en su totalidad para evitar que se haga retiro de una cuenta y no se deposita el dinero a la otra cuenta.

Para fines de recuperación y para asegurar la consistencia de los datos, el sistema debe hacer seguimiento de los diferentes estados de la transacción: • • • • • •

begin_transaction: marca el inicio de la transacción. end_transaction: especifica que las operaciones de read y write se terminaron y marca el límite de la ejecución de la transacción. read(x):los datos x desde la bd y lo pone en una variable x en la memoria. write(x): escribe el valor de la variable x de la memoria principal a la base de datos. commit_transaction: señala la ejecución exitosa de la transacción. rollback (abort): señala que la transacción se terminó sin éxito.

Podemos representar estos diferentes estados de la transacción por medio del siguiente grafo (ver la figura 2.1):


Ejecución concurrente de las transacciones Las transacciones pueden ejecutarse en un ambiente de un solo usuario (monousuario) simple, por ejemplo, en una base de datos de su computadora personal. También, pueden tener acceso de múltiples usuarios, por ejemplo, cuando se trabaja en un ambiente cliente-servidor. Cuando se utiliza una DBMS en el ambiente de múltiples usuarios, la ejecución de las transacciones puede ser interpolada si solo se tiene un procesador (las transacciones A y B en la figura 2.2) o paralela si se tiene más procesadores (las transacciones C y D en la figura 2.2).

Diseño y explotación de bases de datos 123

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Figura 2.2. Concurrencia en ambiente multiusuario. La ejecución concurrente de las transacciones permite que varias de ellas accedan simultáneamente la base de datos. Esto incrementa el número total de transacciones en ejecución (throughput), permite utilizar en forma más eficiente el procesador y disminuye el tiempo de respuesta para las transacciones individuales. Sin embargo, se pueden presentar varios problemas si esta ejecución concurrente se realiza en forma no controlada. Algunos de estos problemas son: 1. The lost update problem (pérdida de actualizaciones): una transacción sobrescribe el valor de la otra; existe la dependencia write – write entre las dos transacciones. Ejemplo:

T i e m p o

T1 READ(X); X = X - N;

T2 READ(X); X = X + M;

WRITE(X); READ(Y); WRITE(X); Y = Y + N; WRITE(Y);

Diseño y explotación de bases de datos 124

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

2. Dirty read (lectura sucia): una transacción lee el valor de la otra mientras la última aborta su ejecución; existe la dependencia write – read entre las dos transacciones. Ejemplo:

T i e m p o

T1 READ(X); X = X - N; WRITE(X);

T2

READ(X); X = X + M; WRITE(X); READ(Y); abort

3. Unrepeatable read (lectura no repetida): una transacción lee el valor varias veces mientras otra transacción lo modifica; existe la dependencia read-write entre las dos transacciones.

T i e m p o

T1 READ(X); PRINT(X);

T2 READ(X); X = X + M; WRITE(X);

READ(X); PRINT(X);

4. Phantom tuples (tuplas fantasma): una transacción lee el valor de varias tuplas mientras otra transacción inserta o borra los registros. Ejemplo: T1 SELECT * FROM Table1 WHERE column1 = 5

T2 UPDATE Table1 SET column2 = 10 SET column1 = 5 WHERE column1 = 8

La posibilidad de que estos problemas existan hace necesario el desarrollo de sistemas de control de concurrencia.

Control de concurrencia basado en candados El sistema de control de concurrencia asegura la ejecución correcta (llamada serializable – equivalente a la ejecución en serie) de las transacciones que se Diseño y explotación de bases de datos 125

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

ejecutan en un ambiente concurrente. Existen varias técnicas de control de concurrencia, donde la más conocida se refiere al protocolo basado en candados. Candados (bloqueos, locks) es una variable asociada al dato en la base de datos que describe el estatus de este dato con respecto a las posibles operaciones de read y write. Tenemos dos tipos de candados. • •

read_lock(A) (Slock(A)): write_lock(A) (Xlock(A)):

candado compartido (share, S) o de lectura candado exclusivo (exclusive, X) o de escritura

Cada transacción que ocupa leer un dato o escribir sobre él debe solicitar al sistema de control de concurrencia de la DBMS un candado adecuado. Solo los candados S son compatibles, o sea, dos o más transacciones pueden tener el candado S sobre el mismo dato (pueden leer el mismo dato). Si una de ellas tiene candado X (escribe sobre el dato), ningún otro candado se puede adquirir sobre este dato La siguiente tabla resume la compatibilidad de los candados: ADQUIRIDO

MODO

Ninguno

S

X

+ +

+ -

-

SOLICITADO S X

+ compatible

Sin embargo, tener los candados y utilizarlos no asegura la ejecución correcta de las transacciones. Ejemplo: Tenemos las dos siguientes transacciones: T1 SLOCK(Y); READ(Y); UNLOCK(Y); XLOCK(X); READ(X); X = X + Y; WRITE(X); UNLOCK(X);

T2 SLOCK(X); READ(X); UNLOCK(X); XLOCK(Y); READ(Y); Y = Y + X; WRITE(Y); UNLOCK(Y);

Suponiendo los valores iniciales son: X = 20 y Y = 30 si las transacciones se ejecutan en serie (que siempre da resultado correcto) T1 y después T2 Diseño y explotación de bases de datos 126

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

obtenemos: X = 50 y Y = 80; si se ejecutan T2 y después T1 obtenemos X = 70 y Y = 50. Sin embargo la ejecución concurrente de la siguiente forma: T1 SLOCK(Y); READ(Y); UNLOCK(Y);

;

T2

SLOCK(X); READ(X); UNLOCK(X); XLOCK(Y); READ(Y); Y = Y + X; WRITE(Y); UNLOCK(Y);

XLOCK(X); READ(X); X = X +Y; WRITE(X); UNLOCK(X); da resultados X = 50 y Y = 50 que no corresponden a ningún resultado de ejecución de estas transacciones en serie. Para que el uso de los candados asegure la ejecución serializable se necesita algún protocolo que establezca las reglas sobre la utilización de los candados. Uno de los protocolos más conocidos se llama Two Phase Locking (2PL) (el protocolo de dos fases de bloqueo). Su nombre indica que este protocolo divide la transacción en dos fases: •

Fase de crecimiento (expanding): los nuevos candados pueden ser solicitados, pero ningún candado puede ser liberado. Si se permite la conversión de los candados, en esta fase solo se puede usar la promoción de los candados (de lectura a escritura).

•

Fase de encogimiento (shrinking): los candados existentes pueden ser liberados pero nuevos candados no pueden ser solicitados. Si se permite la conversión de los candados, en esta fase solo se puede usar la degradación de los candados (de escritura a lectura).

Diseño y explotación de bases de datos 127

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

Sistema de recuperación El control de concurrencia no es suficiente para asegurar la consistencia en la base de datos debido a que en un sistema real pueden ocurrir diferentes tipos de fallos: 1. Fallos de la computadora: Ocurre un error de hardware o software durante la ejecución de la transacción. Así el contenido de la memoria principal se puede perder. 2. El error en la transacción o en el sistema: Errores en la transacción (división entre cero, overflow de enteros, etc). 3. Errores locales o condiciones de excepción: Algunas condiciones pueden ocurrir durante la ejecución de la transacción, por ejemplo, el dato para la transacción no se encuentra, el dinero no es suficiente en la cuenta bancaria para satisfacer el retiro. 4. Fallo del disco: Partes del disco donde se pierden datos por causa de las fallas físicas. 5. Problemas físicos y catastróficos: Descarga eléctrica, terremotos, inundaciones, etc. Los errores tipo 1, 2 y 3 ocurren más a menudo que los problemas 5 y 6. La idea principal es mantener suficiente información para recuperar el sistema de los fallos tipo 1-3. De esto se encarga el sistema de recuperación. Si durante alguna de las fallas la transacción no logró ejecutarse con éxito en su totalidad, el sistema de recuperación debería anular las operaciones que se realizaron. Para poder recuperarse después de las fallas el sistema mantiene la bitácora (log). La bitácora incluye la información sobre todas las transacciones que afectan los datos en la base de datos, tipo de operación realizada y estado de la transacción. La bitácora se guarda en el disco y de esta forma se asegura la recuperación de todas las fallas, excepto fallas del disco. Existen diferentes tipos de bitácoras que pueden tener estructuras de datos variadas. En general, la bitácora debe incluir la información de: • El identificador de la transacción, por ejemplo, T1. • La operación ejecutada, por ejemplo read o write. • El identificador del elemento de datos, por ejemplo, salario. • El valor anterior antes de la modificación llamado BFIM (before image). • En valor nuevo después de la modificación llamado AFIM (after image). Así, si la transacción T1 ejecuta la escritura sobre un dato A y lo modifica del valor 5 a 10, la entrada a la bitácora tendría el siguiente registro: < T1, write, A, 5, 10> Diseño y explotación de bases de datos 128

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

La bitácora contiene los registros con la información de cada una de las transacciones que están en ejecución. Ejemplo: Suponga que tenemos las siguientes transacciones: T1 T2 READ(D); READ(B); D = D + 15; B = B + 8; WRITE(D); WRITE(B); READ(D); D = D + 5; WRITE(D); Si las transacciones se ejecutan en serie, se crea la siguiente bitácora: [T1, begin] [T1, read, D, 5] [T1, write, D, 5, 20] [T1, commit] [T2, begin] [T2, read, B, 2] [T2, write, B, 2, 10] [T2, read, D, 20] [T2, write, D, 20, 25] [T2, commit] En la ejecución concurrente la diferencia consiste que las operaciones de las transacciones se intercalan entre sí. Además, en muchos sistemas reales no se guarda la información sobre la operación de lectura. Después de la falla, el sistema de recuperación lee la bitácora que se encuentra en el disco y realiza diferentes pasos de recuperación de acuerdo al protocolo usado por la DBMS. Transacciones que no lograron su ejecución con éxito se anulan, mientras se confirman las que terminaron su ejecución con éxito.

Diseño y explotación de bases de datos 129

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

ANEXO 1 EJEMPLOS DE ESQUEMAS ESQUEMA DE ADVENTURE WORKS LT

El esquema de AdventureWorksLT se puede bajar desde http://www.codeplex.com/MSFTDBProdSamples/Wiki/View.aspx?title=%20Adve ntureWorksLTDiagram&referringTitle=AWSchemaDiag

Diseño y explotación de bases de datos 130

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

ESQUEMA DE ADVENTURE WORKS El esquema de AdventureWorks (incluida en la siguiente página) se puede bajar desde http://www.microsoft.com/downloads/details.aspx?familyid=0f6e0bcf-a1b5-47608d79-67970f93d5ff&displaylang=en o desde http://www.codeplex.com/MSFTDBProdSamples/Wiki/View.aspx?title=%20AdvW orks2005Diagram&referringTitle=AWSchemaDiag Descripción de tablas se encuentra en http://msdn2.microsoft.com/enus/library/ms124438(SQL.100).aspx

Diseño y explotación de bases de datos 131

Dra. Elzbieta Malinowski

Material de apoyo

Universidad de Costa Rica, Universidad Nacional

7

Diseño y explotación de bases de datos 132

Dra. Elzbieta Malinowski

Material de apoyo