Diseño de Bases de Datos by Carlos Andrés Hernández

Universidad de Ibagué Facultad de Ingeniería Programa de Ingeniería de Sistemas

Diseño de Base de Datos Carlos Andrés Lugo González

Febrero 2011

NOTAS Universitarias | 1

Notas Universitarias Diseño de Bases de Datos Facultad de Ingeniería Programa de Ingeniería de Sistemas Universidad de Ibagué Ibagué, Colombia. Febrero de 2011

Presidente del Consejo Superior Eduardo Aldana Valdés Rector Alfonso Reyes Alvarado Vicerrectora Nidia Chaparro Cuervo

Correspondencia Universidad de Ibagué, Oficina de publicaciones Calle 67, Carrera 22. Tel. (57 8) 2709400 Ibagué -Tolima, Colombia. www.unibague.edu.co publicaciones@unibague.edu.co

Esta obra no puede reproducirse sin la autorización expresa y por escrito de la Universidad de Ibagué

NOTAS Universitarias | 2

ISSN 2216-0302

Carlos Andrés Lugo González es Ingeniero de Sistemas de la Universidad Distrital Francisco José de Caldas. Cuenta con una especialización en Teleinformática de la Universidad de Ibagué. Ha participado en encuentros y conferencias como “Tendencias en ingeniería de software e inteligencia artificial” y “Euro – American Conference On Telematics and Information Systems”. Ha estado vinculado a la Universidad de Ibagué por 11 años. Es docente de tiempo completo en el área de Ingeniera de sistemas.

NOTAS Universitarias | 3

NOTAS Universitarias | 4

Contenido Introducción

1. Conceptos generales de bases de datos 1.1 ¿Qué es una base de datos? 1.2 Sistema gestor de base de datos 1.3 Actores de la base de datos 1.4 Conceptos básicos 1.5 Modelo de datos 1.5.1 Característica estática 1.5.2 Característica dinámica 1.6 Restricciones de integridad 1.6.1 Clasificación de las restricciones 1.7 Proceso de diseño de una base de datos 2. Modelo entidad / interrelación 2.1 Elementos del modelo entidad / interrelación 2.2 Restricciones de integridad 2.3 Elementos de un tipo interrelación 2.3.1 Dependencia en existencia y en identificación 2.4 Control de redundancia 2.4.1 Atributos derivados 2.4.2 Interrelaciones redundantes 2.5 Interrelaciones de grado superior a 2 2.6 Restricciones sobre interrelaciones adicionales 2.7 Generalización 2.8 Esquematización con herramientas CASE 2.9 Ejercicios 3. Modelo de datos relacional 3.1 Intensión y extensión de una relación 3.2 Restricciones del modelo relacional 3.2.1 Restricciones semánticas 3.2.2 Clasificación de las restricciones según los elementos a los que afecta la condición 3.3 Paso del modelo E/R al relacional 3.3.1 Paso de una asociación muchos a muchos entre dos entidades

NOTAS Universitarias | 5

3.3.2 3.3.3 3.3.4 3.3.5 3.4 Ejercicios

Asociaciones uno a uno Paso de herencia a relaciones uno a muchos Asociaciones recursivas (a la misma tabla) Ejemplo de manejo de productos

4. Lenguaje de consulta estructurado SQL 4.1 Instrucciones DDL 4.1.1 Instrucción CREATE 4.1.2 Instrucción ALTER 4.1.3 Instrucción RENAME 4.1.4 Instrucción TRUNCATE 4.1.5 Instrucción DROP 4.2 Instrucciones DML 4.2.1 Instrucción INSERT 4.2.2 Instrucción UPDATE 4.2.3 Instrucción DELETE 4.3 Instrucciones DCL 4.3.1 Instrucción GRANT 4.3.2 Instrucción REVOKE 4.4 Instrucciones de control de transacciones 4.4.1 Instrucción COMMIT 4.4.2 Instrucción SAVEPOINT 4.4.3 Instrucción ROLLBACK 4.5 Instrucciones de recuperación de datos 4.5.1 Instrucción SELECT 4.6 Proceso de Respaldo y Recuperación 4.7 Ejercicios Bibliografía

NOTAS Universitarias | 6

Introducción En la actualidad, la gestión de bases de datos es un área fundamental para el desarrollo profesional del ingeniero de sistemas moderno, debido en gran medida a que todas las organizaciones requieren de almacenar información. Cualquier empresa, sin importar su naturaleza, debe gestionar información y, más específicamente almacenarla, con el fin de procesarla y acceder a ella en todo momento y fundamentalmente al tomar las decisiones que permiten cumplir los objetivos. El ingeniero de sistemas actual debe estar en capacidad de diseñar e implementar una base de datos que cumpla con las características de eficiencia y eficacia que garanticen el correcto almacenamiento y posterior consulta de la información para la cual se crea la base de datos. Este proceso de diseño e implementación no es tarea fácil y necesita de la experticia y conocimiento que solo la práctica puede ofrecer. Estas Notas Universitarias ofrecen al lector un punto de partida sencillo para iniciar en el proceso de diseño e implementación de las bases de datos. Brinda los conceptos básicos necesarios para que, a partir de los diferentes modelos de datos, el estudiante cree una base de datos que satisfaga las necesidades del potencial cliente y/o usuario que así lo requiera. El documento está organizado en cuatro capítulos. El primer capítulo se centra en la conceptualización inicial de las bases de datos, se presentan conceptos generales y de gran importancia para los posteriores capítulos. En el capítulo dos se muestra el modelo de datos Entidad Interrelación, de mucha importancia para el proceso de diseño y muy flexible en términos de las capacidades que ofrece. En el siguiente capítulo se ofrece el modelo de datos relacional, el cual es el más utilizado actualmente por la mayoría de motores de bases de datos comerciales; su correcta comprensión es fundamental para el ingeniero que desee realizar un buen diseño de base de datos. Por último, el capítulo cuarto se refiere al lenguaje de consulta unificado SQL, el cual es un lenguaje que permite implementar el diseño creado, utilizando el modelo de datos relacional. Este documento es el resultado del estudio de diferentes autores y de la experiencia en el desarrollo de base de datos reales; muchas de las ilustraciones y referencias están basadas en el libro Diseño de Bases de Datos Relacionales de los autores Miguel Piatitini y Marcos. A ellos, nuestros más sinceros agradecimientos y reconocimiento. Esperamos que este documento sea de buen provecho para el potencial lector y satisfaga la sed de conocimientos que lo impulsa a estudiar. El Autor.

NOTAS Universitarias | 7

NOTAS Universitarias | 8

1 Conceptos generales de bases de datos 1.1 ¿Qué es una base de datos? Muchas definiciones podemos encontrar de base de datos, entre ellas: Colección organizada de información. Conjunto de datos relacionados entre sí. Colección compartida de datos lógicamente relacionados, junto con una descripción de estos datos, que están diseñados para satisfacer las necesidades informáticas de una organización. Cualquiera de las anteriores definiciones es válida y de acuerdo a la complejidad del sistema de base de datos a desarrollar, podemos añadir nuevas características a la definición.

\ Figura 1. Representación grafica de una base de datos

Algunas características que deben poseer las bases de datos son: Las BASE DE DATOS representan algún aspecto del mundo real (Minimundo). Una BASE DE DATOS es un conjunto de datos lógicamente coherente con cierto significado inherente. Toda BASE DE DATOS se diseña, construye y puebla con datos para un propósito especifico. 1.2 Sistema gestor de base de datos SGBASE DE DATOS Un Sistema Gestor de Base de Datos o DBMS Data Base Managenment System; es un conjunto de programas (software) que permite a los usuarios definir, crear, mantener y controlar una BASE DE DATOS. Algunas de las funcionalidades que debe prestar un SGBASE DE DATOS son: Permite a los usuarios definir la Base de Datos, usualmente mediante un Lenguaje de definición de datos que permita crear modificar y eliminar los objetos de la base de datos como las tablas, vistas, etc.

NOTAS Universitarias | 9

Permite a los usuarios realizar operaciones sobre los datos como insertar, actualizar, eliminar y consultar. Proporciona un acceso controlado a la base de datos que puede incluir o Un sistema de seguridad de los datos (que evita que usuarios no autorizados accedan a la información). o Un sistema de integridad de la información (que mantiene la coherencia de los datos almacenados). o Un sistema de control de concurrencia (que permite el acceso compartido a la base de datos). o Un sistema de respaldo y recuperación de la información (que restaura la base de datos a un estado previo coherente después de cada fallo hardware o software). o Un sistema de control de redundancia (que reduzca al mínimo la redundancia de datos en la base de datos). Un SGBASE DE DATOS ofrece una representación conceptual de los datos, es decir que el software gestor de base de datos debe tener la capacidad de plasmar de alguna forma la representación conceptual de los datos. En términos informales un modelo de datos es un tipo de abstracción de los datos con que se obtiene esa representación conceptual. Dentro de cada base de datos existe una parte denominada catalogo del sistema, es aquí donde se almacenan los Metadatos o diccionario de datos (los datos acerca de los datos) y estos describen la estructura de la BASE DE DATOS primaria. 1.3 Actores de la base de datos Distintos roles y personas deben interactuar para llevar a buen término la construcción de una base de datos, la separación de tareas es fundamental en cualquier tipo de proyecto ya que especializa y organiza los procesos dentro del proceso de desarrollo; a continuación se describen los principales actores dentro del desarrollo y construcción de una base de datos: DBA: Data Base Administrator. El administrador de la BASE DE DATOS se encarga de administrar los recursos de la base de datos; el administrador es responsable de la gestión de la base de datos que incluye procesos como el control de la seguridad y la asignación de roles y privilegios a los usuarios, el control de la integridad de los datos en caso de fallas de hardware y software, el mantenimiento y fiabilidad del sistema y la certeza de garantizar el mejor rendimiento posible de las aplicaciones de la base de datos y la base de datos en sí. Diseñador de la base de datos: En general existen dos tipos de diseñadores de bases de datos; el diseñador lógico y el diseñador físico de la base de datos. El diseñador lógico se encarga de identificar los objetos de la base de datos como las

NOTAS Universitarias | 10

entidades y sus atributos, las relaciones entre estas y las restricciones que operan sobre los datos. El diseñador físico es el encargado de materializar físicamente el diseño lógico de la base de datos, utilizando las estructuras más adecuadas según el motor de base de datos que se utilice. En términos generales en diseñador lógico se encarga de él que mientras que el diseñador físico se encarga del cómo. Usuario final: Son las personas o sistemas que necesiten tener acceso a la base de datos para consultarla y realizar transacciones, se pueden dividir en 2 grandes tipos: o Avanzado: Están familiarizados con el diseño de la base de datos y con el motor de base de datos; usualmente utilizan un lenguaje de consulta de alto nivel como SQL para realizar las transacciones a la base de datos. o General: Usualmente no conocen la base datos y utilizan las aplicaciones de usuario para interactuar con ésta. Analista de sistemas y desarrolladores de aplicaciones: Determinan los requisitos de los usuarios finales y desarrollan aplicaciones que satisfagan dichos requerimientos, usualmente para este fin utilizan un lenguaje de programación de propósito general como Java, VB.Net, etc. 1.4 Conceptos básicos Para continuar es necesario hacer claridad respecto a algunas definiciones utilizadas a lo largo de este documento, entre las que encontramos: Dato: Unidad de información mínima, símbolo con significado limitado. Información: Conjunto organizado de datos interpretados con mayor significado que los datos. Modelar: Consiste en definir un mundo abstracto y teórico tal que las conclusiones que se puedan sacar del, coincidan con las manifestaciones aparentes del mundo real; al igual que en otras disciplinas, modelar consiste en representar el mundo real a través de de un modelo. Modelo de Datos: Es un dispositivo de abstracción que “nos permite ver el bosque en oposición a los arboles”; proporciona mecanismos de abstracción que permiten la representación de aquella parcela del mundo real cuyos datos nos interesa registrar y que comúnmente conocemos como universo en discurso o minimundo. Es realmente el conjunto de reglas y mecanismos que nos permiten aplicar una serie de abstracciones con el fin de crear un modelo a partir del problema que queremos solucionar. Abstracción: Consiste en separar por medio de una operación intelectual las cualidades de un objeto para considerarlas aisladamente o para considerar el mismo objeto en su pura esencia o noción. Este proceso oculta detalles insignificantes y se fija en lo esencial, busca propiedades comunes, reduce la

NOTAS Universitarias | 11

complejidad y ayuda a la comprensión del mundo real. Cuando se realiza un proceso de abstracción se lleva del mundo real al mundo conceptual, enfocándonos en lo esencial apara solucionar el problema informático. 1.5 Modelo de datos Un modelo de datos es el conjunto de conceptos, reglas y convenciones bien definidas que nos permiten aplicar una serie de abstracciones a fin de describir y manipular los datos del minimundo que deseamos almacenar en la bases de datos. Es equivalente al mismo modelo lógico que se entiende como cualquier tipo de modelo en el campo de las bases de datos, el modelo de datos no es igual a esquema de datos. Lenguaje de Datos = Modelo de Datos + Sintaxis SQL = Modelo de Datos Relacional + Sintaxis Se entiende como ejemplar a la base de datos en determinado momento, como una fotografía de ella. El modelo de datos debe poseer como mínimo 2 características, la característica estática y la dinámica. 1.5.1 Característica estática La característica estática es la encargada de definir la base de datos en su componente estático, es decir en los objetos y componente que no cambian (no cambian mucho) a lo largo del tiempo, está compuesta por: Elementos permitidos: Son los elementos estáticos (estructuras) que conforman el modelo, varían de acurdo al modelo, en general son: o Objetos, son las entidades, relaciones (NO confundir con asociaciones), etc. o Asociaciones, son la interrelaciones entre los objetos como las entidades. o Propiedades, son las características de un objeto como una entidad (atributos, campos, etc). o Dominios, son los posibles valores que puede tomar un atributo o propiedades, se define como un conjunto homogéneo de valores. Elementos NO permitidos o Restricciones: Las restricciones son limitaciones impuestas por el mismo modelo de datos o por el mundo real con el fin de promover la integridad de los datos; un ejemplo de restricción es que no haya registros duplicados (iguales) en una entidad o que un curso tenga máximo 20 alumnos matriculados. Existen dos tipos de restricciones: o Restricciones inherentes: Son aquellas impuestas por el modelo de datos, por ejemplo “toda entidad debe tener un nombre”.

NOTAS Universitarias | 12

Restricciones de integridad o semánticas: Son aquellas que permiten plasmar las reglas o restricciones del mundo real. Un ejemplo es una restricción que limite el número de retiros diarios que puede realizar un cliente de un banco en su cuenta. Las restricciones semánticas pueden ser:  Reconocidas por el modelo de datos: Son aquellas impuestas por el mundo real pero gestionadas por el modelo de datos, por ejemplo que el género de un empleado se “M” o “F”.  Ajenas al modelo de datos: Son aquellas impuestas por el mundo real pero no gestionables por el modelo de datos, por ejemplo asegurar que solo los mayores de edad pueden estar casado. Usualmente para este tipo de restricciones se utilizan las aplicaciones de la base de datos.

1.5.2 Característica dinámica Es la encargada de efectuar las operaciones en la base de datos; estas operaciones tienen 2 componentes: Localización: Consiste en ubicar un ejemplar (registro) o un conjunto de ejemplares (de acuerdo a una condición) en un objeto de la base de datos (usualmente una entidad). Acción: Consiste en realizar la acción especificada en él o los ejemplares (registros) previamente ubicados mediante la localización, y puede ser la recuperación de los datos o la modificación de los mismos, mediante una inserción, actualización o eliminación. LOCALIZACIÓN <condición> ACCIÓN <objetivo> Donde LOCALIZACIÓN y ACCIÓN son comandos del lenguaje del modelo de datos. 1.6 Restricciones de integridad Las restricciones de integridad son las reglas que debemos implementar basándonos en el mundo real; inicialmente las restricciones pueden ser: Restricciones propias: Son las restricciones semánticas que pueden ser especificadas en el propio modelo de datos y representadas en el esquema de datos (los diagramas).

NOTAS Universitarias | 13

Restricciones ajenas: Son las restricciones que no son soportadas por el modelo de datos y usualmente deben ser implementadas por aplicaciones independientes a la base de datos. Las restricciones de integridad tienen tres componentes principales: La operación: se refiere a la operación de inserción, actualización o eliminación cuya ejecución genera la comprobación de la restricción. Condición: que se debe cumplir, es una expresión lógica simple (que retorna verdadero o falso) y que dependiendo del resultado dispara la acción subsecuente. Acción: Que se debe hacer de pendiendo el resultado de la condición, en caso que la condición sea verdadera, lo más usual es que la acción sea de rechazo de la operación. 1.6.1 Clasificación de las restricciones Las restricciones se clasifican como se puede apreciar en la figura 2.

Figura 2. Clasificación de las Restricciones Adaptado de [1]

NOTAS Universitarias | 14

Restricciones Inherentes: Son las que el modelo de datos impone, como que cada entidad debe tener como mínimo un atributo. Restricciones Semánticas: Son aquellas que modelan el mini universo, son las restricciones que impone el mundo real que se está analizando para la creación de la base de datos. Restricciones Propias: Son definidas al definir el es esquema de la base de datos, pueden ser manejadas por el modelo de datos. Restricciones Ajenas: Son las restricciones que no puede manejar por si solo el modelo de datos, se implementan en los programas de la aplicación (capa lógica). Restricciones de Lenguaje de Propósito General: Son las restricciones ajenas implementadas mediante un lenguaje de propósito general como C++, Java, VB.Net, etc. Restricciones del Lenguaje del SGBD: Son las restricciones ajenas implementadas mediante un lenguaje asociado a un motor de base de datos como PL/SQL de Oracle o Transact-SQL de Microsoft. Restricciones de Acción General: Son las restricciones donde es preciso programar estilo método para determinar la acción que hay que llevar a cabo, son muy parecidas a las restricciones ajenas con lenguaje de programación del motor de base de datos. Procedimientos Almacenados: Se definen de forma procedimental, son las que se asemejan más a las “restricciones ajenas” al modelo. Disparadores: Se desencadenan cuando sucede cierta condición, se declaran como procedimientos y se disparan automáticamente cuando un evento de inserción, actualización o eliminación ocurre. Restricciones Acción específica: Esta implícita en la misma restricción (generalmente la negativa genera un rechazo). Restricciones de Condición General: Generan rechazo siempre y cuando sean falsas; se definen como una proposición lógica. Restricciones de verificación: Son especificas a un elemento del esquema.

NOTAS Universitarias | 15

Restricciones de Aserción: Igual que las anteriores pero afectan a varios objetos simultáneamente. Restricciones de Condición Específica: Son opciones proporcionadas por el propio modelo. No especifica los componentes relativos a la restricción. 1.7 Proceso de diseño de una base de datos Conjunto de etapas necesarias para pasar de una determinada realidad (mini universo) a la base de datos que representa. La etapas en el diseño se presentan en la figura numero 3. MUNDO REAL

(Cualquier problema informático, como el de una entidad bancaria, universidad, etc.)

UNIVERSO DEL DISCURSO (Visión del problema informático bajo el problema especifico a resolver)

MODELADO CONCEPTUAL DE DATOS (Modelo E/R de la solución del problema informático)

MODELADO LÓGICO (Modelo de la solución de la base de datos, Modelo Relacional)

MODELADO INTERNO (Modelo de estructuras internas de la base de datos como los índices, secuencias, etc.)

ALMACENAMIENTO FÍSICO (Estructuras físicas de almacenamiento, como tablas, atributos, etc.) Figura 3. Etapas del diseño de una Base de Datos. Adaptado de [1]

NOTAS Universitarias | 16

2 Modelo entidad / interrelación El modelo entidad interrelación es un modelo de datos usado como base para una vista unificada de los datos, adoptando el enfoque más natural del mundo real que consiste en entidades e interrelaciones. 2.1 Elementos del modelo entidad interrelación Los elementos encontrados en el modelo entidad interrelación son los siguientes: Entidad: Cualquier objeto real o abstracto que existe en el universo y acerca del cual queremos almacenar información en la base de datos. Haciendo un paralelo con el modelo Orientado a Objetos, podemos decir que la entidad (en términos generales) es la misma clase si omitimos las operaciones. Existen 2 tipos de entidades. o Entidad regular: Cuyos ejemplares (registros de la entidad) existen por sí mismos. La representación de este tipo de entidad es un rectángulo con el nombre de la entidad dentro de él. El ejemplo de una materia ilustra este tipo de entidad, ya que una materia (con ciertas consideraciones) tiene existencia propia.

Figura 4. Entidad Regular

Entidad débil: Cuyos ejemplares dependen de que exista cierto ejemplar en otro tipo entidad. La representación de este tipo de entidad es un rectángulo de línea doble con el nombre de la entidad dentro de él. Un curso se considera una entidad débil debido a que para poder existir, debe existir previamente una materia de la cual se cree el curso.

Figura 5. Entidad Débil

NOTAS Universitarias | 17

Reglas de las entidades: Las entidades deben cumplir ciertas reglas que hagan valida su existencia, entre ellas encontramos: Tienen que tener existencia propia, es decir que la existencia de sus ejemplares (los registros) debe tener existencia sin ninguna dependencia (aunque eso no es cierto en el caso de las entidades débiles). Cada ejemplar debe poder distinguirse de otro, es decir que no deben existir dos ejemplares exactamente iguales en la misma entidad. Todos los ejemplares de la misma entidad debe tener las mismas propiedades, es decir que todos los ejemplares de la entidad deben obligatoriamente tener valores en las propiedades de la entidad, así alguno(s) valor(es) sean NULL. Interrelación: Es una asociación, vinculación o correspondencia entre entidades; en términos generales es muy parecida a una entidad, con la connotación de que su existencia se debe a la vinculación entre entidades. Dos entidades pueden poseer más de dos interrelaciones. La representación de una interrelación es a través de un rombo. El modelo E/R tiene como restricción inherente que solo permite establecer interrelaciones entre entidades, NO están permitidas entre Entidad - Interrelación ni entre Interrelación - Interrelación.

Figura 6. Interrelación “Imparte”

NOTAS Universitarias | 18

Figura 7. Interrelación “Imparte” y “Recibe”

Dominio: Es el conjunto de posibles valores que puede tomar cierta propiedad de una entidad o interrelación. Se puede definir por: o Intención: Especificando el tipo de dato. o Extensión: Declarando el valor de cada elemento, ej.: azul, rojo, verde. Los dominios tienen existencia propia independiente de cualquier otro elemento. Atributo: Cada una de las propiedades o características que tiene una entidad o interrelación. Se representa con una línea y circulo la entidad o interrelación a la que pertenece. El número de atributos de una entidad o interrelación es n, es decir cualquier número entre 1 e infinito.

Figura 8. Representación de los atributos

NOTAS Universitarias | 19

En la figura 8 se puede apreciar dos atributos pertenecientes a la entidad profesor, el primer atributo llamado nombre es un atributo simple, el segundo atributo llamado fec nac. Es un atributo compuesto (es decir que a su vez está compuesto de atributos). Los atributos a diferencia de los dominios NO tienen vida propia, es decir están ligados al tipo entidad o interrelación correspondiente. Adicionalmente es necesario aclarar que las entidades e interrelaciones poseen atributos y los ejemplares toman valores para cada atributo. 2.2 Restricciones de integridad La principal restricción inherente (es decir las restricciones propias del modelo de datos y no las que impone el mundo real) del modelo de datos entidad interrelación E/R, prohíbe que haya interrelaciones entre interrelaciones, es decir que una interrelación solo podrá existir entre dos entidades; también obliga a que todas las entidades posean un identificador o nombre único en todo el esquema de la base de datos. Otras importantes restricciones son: Restricción sobre Valores: Se establecen mediante la definición del dominio, cada valor solo puede pertenecer a un conjunto definido. Restricción sobre Atributos (IC): Entre todos los atributos de un tipo entidad existe uno o varios (simples y/o compuestos) que identifican unívocamente cada uno de los ejemplares de esta entidad. A cada uno de de estos conjuntos de atributos se les denomina Identificador Candidato (IC); todo IC debe cumplir la condición de ser unívoco y mínimo. Se dividen en dos grupos: o Identificador Principal: Es aquel que por razones ajenas al modelo de datos es considerado el atributo “principal” de la entidad. o Identificadores Alternos: El resto de atributos candidatos que no son el identificador principal.

Figura 9. Representación de los atributos Candidatos

NOTAS Universitarias | 20

Restricción sobre Atributos (otros): Otras restricciones aplican sobre los atributos, como los atributos multivaluados, que son aquellos que pueden tomar más de un solo valor; ej.: una materia se puede impartir en dos a más idiomas. Los atributos opcionales son aquellos que pueden tener valores nulos lo cual indica que el valor de ese atributo es NULL.

Figura 10. Atributos regulares, multivaluados y opcionales.

La cardinalidad es el rango de valores que puede tomar en este caso un atributo y generalmente tiene un valor mínimo y máximo. Como se puede observar, en el modelo de datos E/R un atributo puede ser multivaluados. Sin embargo, en el modelo de datos relacional (siguiente capítulo) esto no está permitido.

Figura 11. Cardinalidad de los atributos.

2.3 Elementos de un tipo interrelación Los elementos que componen a una interrelación son:

NOTAS Universitarias | 21

Nombre: Lo distingue unívocamente del resto de elementos; debe ser único y descriptivo de la entidad, usualmente es un sustantivo en singular (con contadas excepciones). Grado: Es el número de entidades que participan en un tipo interrelación. De este concepto podemos deducir que el número de entidades que asocia una interrelación no es necesariamente 2, puede ser 1, 3, etc. Tipo correspondencia: Es el número máximo de ejemplares de una entidad que pueden estar asociados en una determinada interrelación, con un ejemplar de otro tipo.

Figura 12. Ejemplos de interrelación.

Papel (“Rol”): Es la función que cada uno de los tipos entidad realiza en el tipo interrelación.

Figura 13. Roles entre la Interrelación.

NOTAS Universitarias | 22

Cardinalidad de un tipo entidad: se define como el número máximo y mínimo de ejemplares de un tipo entidad que pueden estar interrelacionados con un ejemplar de otro.

Figura 14. Cardinalidad entre la Interrelación.

Atributos de las interrelaciones Ciertas consideraciones deben tenerse en cuenta cuando se analizan los atributos de una interrelación, por ejemplo, cuando en una interrelación 1:N se tiene un atributo asociado, este atributo se lleva a la entidad cuya cardinalidad es N. Cuando la interrelación es 1:1 es muy conveniente conservar el atributo dependiendo de la interrelación. Los atributos de las Interrelaciones N:M son propios de la misma y no de las entidades vinculadas por la interrelación. Ejercicio: Graficar dos ejemplos de los posibles casos en los que un atributo esté presente en la interrelación.

2.3.1 Dependencia en existencia y en identificación Existen 2 tipos de interrelación, las regulares y las débiles, según estén asociando a dos entidades regulares, o a una entidad débil con una entidad (débil o regular), respectivamente. La interrelación débil tiene dos divisiones, la dependencia en existencia y en identificación. Dependencia en existencia: cuando los ejemplares de un tipo entidad débil no pueden existir si desaparece el ejemplar de tipo entidad regular del cual dependen.

NOTAS Universitarias | 23

Es decir que el ejemplar de la entidad débil no puede crearse si no se ha creado previamente el ejemplar de la entidad regular a la que está asociada.

Figura 15. Dependencia en existencia.

Dependencia en identificación: además de la condición anterior, los ejemplares del tipo entidad débil no se pueden identificar por sí mismos y exigen añadir el identificador principal del tipo entidad regular del cual dependen. Es decir que el identificador principal de la entidad débil está compuesto de forma total o parcial por el identificador candidato de la entidad regular a la cual está asociado.

NOTAS Universitarias | 24

Figura 16. Dependencia en identificación.

2.4 Control de redundancia La redundancia (duplicidad innecesaria de los datos) es un factor predominante de una buena base de datos; es imperativo reducir al mínimo (lo ideal sería evitarla totalmente) la redundancia en nuestra base de datos y para esto existen técnicas aplicables desde el mismo modelo de base de datos utilizado. Debemos analizar la existencia de redundancia por los problemas de inconsistencias a los que puede dar lugar; se dice que un elemento del esquema es redundante cuando puede ser eliminado sin pérdida de semántica, es decir, sin pérdida del significado de los datos en la propia base de datos. Existen dos tipos de redundancia, en los atributos (derivados) y en las interrelaciones (derivadas). 2.4.1 Atributos derivados Son aquellos que se obtienen a partir de otros ya existentes, son calculados, por lo que aunque son redundantes, no son inconsistentes siempre y cuando se indique su procedencia y la fórmula para hallarlos. Usualmente este tipo de atributos son creados con fines de eficiencia de la base de datos, como ejemplo podemos analizar una entidad bancaria con sus cuentas y las transacciones que generan estas cuentas, en la figura 17 se observa la relación entre la entidad CUENTA y la TRANSACCION; si se quisiera calcular el saldo de la cuenta sería posible hacerlo a través de la suma y resta de acuerdo a la

NOTAS Universitarias | 25

naturaleza de las transacciones, sin embrago y considerando el posible número de transacciones, esta operación podría ser muy costosa computacionalmente generando retrasos en la consulta. Lo mal útil sería crear un atributo saldo en la entidad CUENTA y actualizarlo constantemente cada vez que se haga una transacción.

Figura 17. Ejemplo entidad bancaria.

Un atributo derivado se puede calcular en 2 momentos distintos: Al actualizar: Es decir que cada vez que se genere una operación de inserción, actualización o eliminación, se actualice el atributo calculado (real). Al consultar: Es decir que se actualice solamente cuando se realice una consulta al atributo calculado (virtual). 2.4.2 Interrelaciones redundantes Se dice que una interrelación es redundante cuando su eliminación no implica la pérdida de semántica (del significado de los datos en la base de datos) porque existe la posibilidad de realizar la misma asociación de ejemplares por medio de otras interrelaciones. Se considera condición necesaria aunque no suficiente que haga parte de un ciclo. En el ejemplo de la figura 18 se puede apreciar como existe una interrelación redundante; si consideramos que un curso se encuentra adscrito a un departamento y que un profesor dicta uno o varios cursos, su puede asumir que los cursos que dicta un determinado profesor lo hacen miembro (directo o indirecto) del departamento al cual pertenece el curso, en ese caso la interrelación “pertenece” entre el profesor y el departamento se hace redundante ya que su eliminación no implica la pérdida de semántica en el esquema. En la figura 19 se puede apreciar el caso contrario, en donde no es posible eliminar la interrelación entre el docente y el departamento sin pérdida de semántica; esto ocurre porque un curso ya no está adscrito a un solo departamento sino a varios, haciendo que la interrelación entre el profesor y el departamento sea necesaria con el fin de determinar a qué departamento está adscrito un profesor.

NOTAS Universitarias | 26

Figura 18. Interrelación redundante.

Figura 19. Interrelación NO redundante.

Existen otros casos en donde la interrelación, a pesar de poder ser deducida por otras, no se puede eliminar ya que posee atributos o que por razón ajenas al modelo sea conveniente conservarlas como prever un futuro cambio en la base de datos. Se debe comprobar que si se elimina la interrelación, la semántica se conserve en ambos sentidos y que no se pierdan atributos.

NOTAS Universitarias | 27

Condiciones para eliminar una interrelación Existir un ciclo. Que las interrelaciones del ciclo sean equivalentes semánticamente, es decir que al eliminar una interrelación el significado de los datos en la base de datos sea equivalente con las demás interrelaciones. Que se puedan asociar los ejemplares de las dos entidades que estaban interrelacionadas, aun habiendo eliminado la interrelación; si esto no es posible y un ejemplar de una entidad no se puede relacionar con otro ejemplar de otra entidad, la interrelación no se puede eliminar. Que la interrelación o bien no tenga atributos, o bien estos puedan ser transferidos a fin de no perder la semántica. 2.5 Interrelaciones de grado superior a 2 Lo más conveniente en el modelo es que existan interrelaciones de grado 2, pero esto no es posible siempre. En ocasiones se puede descomponer una interrelación de grado n en una más simple, pero para esto es preciso analizar correctamente la semántica. En el ejemplo de la Figura 20 podemos apreciar como una interrelación de grado 3 no se puede eliminar ya que se perdería la semántica; en este ejemplo, un profesor imparte diferentes temas en muchos cursos. Si se elimina la interrelación de grado 3 por las tres interrelaciones de grado 2, no se podría determinar exactamente qué temas entran en un curso específico. Por el contrario, en la Figura 21 podemos apreciar una interrelación de grado 3 que es posible reemplazar por 3 interrelaciones de grado 2 sin pérdida de semántica, ya que la información de qué cursos imparte el profesor, qué alumnos tiene como asistente un curso y a qué estudiantes da clase el profesor es consistente de cualquiera de las dos formas.

NOTAS Universitarias | 28

Figura 20. Interrelaci贸n de grado 3 que no se puede reemplazar.

Figura 21. Interrelaci贸n de grado 3 que s铆 se puede reemplazar.

2.6

Restricciones sobre interrelaciones adicionales

NOTAS Universitarias | 29

Existen restricciones sobre interrelaciones adicionales a las mencionadas anteriormente que permiten al modelo plasmas mejor la realidad del mundo del problema que se está analizando, aunque lo recomendable es crear un modelo lo más simple posible. Existen ocasiones en las que este tipo de restricciones aplican de forma adecuada. Restricción de exclusividad: Este tipo de restricción aplica cuando el ejemplar del tipo entidad solo puede pertenecer a uno de los tipos de la interrelación, pero en el momento que pertenezca a uno ya no podrá formar parte del otro. Un caso especial es cuando se asocian más de dos entidades y se considera la cardinalidad. En la Figura 22 se observa cómo un empleado se relaciona con un departamento por medio de dos posibles interrelaciones. Si dirige el departamento, no puede asistirlo y caso contrario. En la Figura 23 también se observa una restricción de exclusividad con la cardinalidad respecto a las dos interrelaciones.

Figura 22. Ejemplo de Interrelación de exclusividad.

NOTAS Universitarias | 30

Figura 23. Ejemplo de Interrelación de exclusividad.

Restricción de exclusión: todo ejemplar de la primera entidad que este unido a la segunda entidad mediante la primera interrelación, no podrá estar unido al mismo ejemplar de la segunda entidad mediante la segunda interrelación. En la Figura 24 se observa el ejemplo de un empleado que o dirige o asiste a un departamento pero que al momento de dirigir un departamento no podrá asistirlo y viceversa.

Figura 24.

Ejemplo de Interrelación de exclusión.

NOTAS Universitarias | 31

Restricción de inclusividad: todo ejemplar de la primera entidad que participa en unos de los dos tipos de interrelación tiene necesariamente que participar en la otra. En la Figura 25 se observa el ejemplo en donde un empleado que dirige un departamento tiene que haber asistido a (en este caso) mínimo 3 departamentos.

Figura 25. Ejemplo de Interrelación de inclusividad.

Restricción de inclusión: es aún más fuerte que la restricción de Inclusividad, específica que para que un ejemplar exista en una interrelación previamente debería existir en la otra. En el ejemplo de la Figura 26 se especifica que para dirigir un departamento se debió asistirlo previamente.

Figura 26. Ejemplo de Interrelación de inclusión.

NOTAS Universitarias | 32

2.7 Generalización Se considera un caso especial de interrelación entre varios tipos entidad (subtipos) y un tipo más general (supertipo) cuyas características son comunes a todos los subtipos. La generalización en el modelo E/R es en esencia la misma del modelo orientado a objetos. El concepto base de la generalización consiste en observar las características generales de varias entidades y agruparlas en una sola que posee todas estas características, a esta entidad se le denomina supertipo; a partir de esta entidad se crean diferentes entidades “especializadas” que poseen sus propios atributos y heredan los atributos del supertipo, a estas entidades se les denomina subtipos. Existen diferentes características en la generalización de a cuerdo a la relación que existe o puede existir entre los ejemplares de las entidades involucradas. Estas características son: Solapamiento: ocurre si un mismo ejemplar del supertipo puede pertenecer a más de un subtipo. Exclusividad: ocurre si un ejemplar del supertipo solo puede pertenecer a uno de los subtipos. Totalidad: si todo ejemplar del supertipo tiene que pertenecer a algún subtipo Parcialidad: si un ejemplar del supertipo no tiene que pertenecer a algún subtipo. Para representar la exclusividad se utiliza un arco en la relación de la entidad supertipo y los subtipos y un círculo para representar la jerarquía total como se puede apreciar en los ejemplos de las figuras 27 y 28.

Figura 27. Ejemplo de Generalización.

NOTAS Universitarias | 33

Tanto un docente como una secretaria son empleados (Generalización). Un mismo empleado no puede ser a la vez un docente y una secretaria (exclusividad o disyunción). Todo empleado tiene que ser obligatoriamente un docente o una secretaria (totalidad).

Figura 28. Ejemplo de Generalización.

Un curso dirigido y un curso virtual son cursos (Generalización). Un mismo curso no puede ser al mismo tun curso dirigido y un curso virtual (exclusividad o disyunción). Un curso puede ser curso dirigido o curso virtual o ninguno de los dos (parcialidad).

Dimensión temporal en el modelo E/R Las bases de datos almacenan información en el tiempo y considerando que no debe borrarse información de estas, la mejor forma de modelar “el tiempo” es a través de atributos fecha que almacenen las fechas de los acontecimientos y el manejo de estados en los ejemplares de cada entidad.

NOTAS Universitarias | 34

2.8 Esquematización con herramientas CASE Una herramienta CASE (Computer Aided Software Engineering) es “un software destinadas a aumentar la productividad en el desarrollo de software reduciendo el costo de las mismas en términos de tiempo y de dinero”1. Dependiendo de la herramienta CASE utilizada, la forma de representación cambia un poco, aunque en general todas siguen el mismo patrón. Por ejemplo en Power Designer2, la representación de los atributos de una entidad o interrelación se hacen dentro el objeto no con círculos y líneas. A continuación se presenta un ejemplo de los objetos manejados en el modelo E/R de acuerdo a su representación en la herramienta CASE Power Designer; la Figura 29 representan una entidad con sus atributos, la Figura 30 una interrelación con sus atributos y la Figura 31 el uso de la generalización.

CURSO CODIGOCUR <pi> Number <M> NOMBRECUR Variable characters (50) INTHORARIACUR Number ESTADOCUR Variable characters (2) CPCURSO <pi> ...

Figura 29.

Entidad con atributos

INSCRIPCION CODIINSCRIPCION Number FECHAINSCRIPCION Date ESTADOINSCRIPCION Variable characters (2)

Figura 30.

Interrelación con atributos

http://es.wikipedia.org/wiki/Herramienta_CASE

http://www.sybase.com/products/modelingdevelopment/powerdesigner

NOTAS Universitarias | 35

CURSO CODIGOCUR <pi> Number <M> NOMBRECUR Variable characters (50) INTHORARIACUR Number ESTADOCUR Variable characters (2) CPCURSO <pi> ... ES UN

CURSOVIRTUAL DESCHERRAMIENTACV Variable characters (200) NUMMODULOSCV Number

Figura 31. Generalización

El ejemplo de la Figura 32 reúne los diferentes objetos del modelo E/R en un esquema que relaciona un curso con programa académico y alumno. ALUMNO

PROGRAMAACADEMICO CODPROGACA <pi> Number NOMBRPROGACA Variable characters (50) FECHREGCALPRGACA Date ESTAPROGACA Variable characters (2)

pertenece

PKPROGRAMAACADEMICO <pi> ...

CODIGOALUM <pi> Number NOMBREALUM Variable characters (50) DIRECCIONALUM Variable characters (50) GENEROALUM Variable characters (1) ESTADOALUM Variable characters (2) CPALUMNO <pi> ... 0,n

CURSO CODIGOCUR <pi> Number <M> NOMBRECUR Variable characters (50) INTHORARIACUR Number ESTADOCUR Variable characters (2) CPCURSO <pi> ...

0,n

INSCRIPCION CODIINSCRIPCION Number FECHAINSCRIPCION Date ESTADOINSCRIPCION Variable characters (2)

ES UN

CURSODIRIGIDO

CURSOVIRTUAL DESCHERRAMIENTACV Variable characters (200) NUMMODULOSCV Number

NOMBASESORCD Variable characters (50) NUMHORASPRES Number

Figura 32. Esquema de ejemplo E/R.

2.9

Ejercicios

NOTAS Universitarias | 36

1. Identificar entidades que posean atributos compuestos y esquematizarlos. 2. En un problema informático cualquiera, identificar las entidades, sus atributos y los identificadores candidatos de las entidades. 3. Identificar tres ejemplos de atributos calculados en diferentes entidades. 4. Investigas como se grafica la generalización “completa” y “mutualmente exclusiva” en la herramienta Power Designer. 5. Crear a partir de un ejemplo dado por el profesor, un esquema de base de datos completo en una herramienta CASE. Ejemplo: biblioteca, entidad bancaria, hospital, etc.

NOTAS Universitarias | 37

NOTAS Universitarias | 38

Modelo de datos relacional

El modelo de datos relacional es el modelo de datos más utilizado en la actualidad, fue propuesto por Codd en 1970 y supuso una revolución en las bases de datos debido a su enfoque y la separación de los datos en sí mismos de las aplicaciones utilizadas para gestionar dichos datos. La estructura u objeto básico de este modelo es la relación o tabla, cuya función es la de representar las entidades del mundo del problema así como las asociaciones o interrelaciones del modelo E/R. Los componentes básicos del modelo son: Dominio: Es el conjunto de posibles valores que puede tomar un atributo, debe ser finito y nominal. Se puede definir por extensión (nombrando sus posibles valores) o por intensión (mediante el tipo de datos), siendo este último el más utilizado. Atributo: Es una propiedad o característica de la relación, es la misma columna de una tabla en el modelo físico. Un atributo se encuentra siempre en una relación (es una propiedad de una relación), mientras que un dominio tiene existencia independiente a las relaciones del esquema; ej.: los números enteros existen así no haya relaciones (tablas) en el esquema. Un atributo debe tomar los valores de un dominio y de solo un dominio según su definición y diferentes atributos pueden tomar sus valores del mismo dominio, es decir que varios atributos pueden ser del mismo tipo. Relación: es el conjunto de elementos llamados tuplas {tj}, donde cada tupla o registro es una pareja conformada por el nombre del atributo “Ai” y el valor del atributo “Vij” sobre el dominio Di donde fue definido el atributo; se expresa: (<A1:V1j> , ... <Ai:Vnj> , ... <An:Vnj>). El esquema de relación es la representación de la relación en términos de sus atributos con sus respectivos dominios, se compone del nombre de la relación R (usualmente un sustantivo en singular), de un conjunto de atributos (el numero de atributos es cualquier entero positivo) {Ai} y de un conjunto de dominios (cada atributo debe tener un dominio, y dos o más atributos pueden tener el mismo dominio) {Di}. Se expresa: R (A1:D1 , A2:D2 , An:Dn) donde R es el nombre del esquema de relación, Ai es el nombre del atributo y Di el dominio al cual pertenece cada atributo.

Una relación se representa físicamente utilizando una tabla en donde:

NOTAS Universitarias | 39

Los atributos de la relación son las columnas de la tabla. Cada registro de la tabla es una tupla de la relación, el conjunto de todas las tuplas conforman la relación. El número de tuplas se llama cardinalidad de la relación. 3.1 Intensión y extensión de una relación La intensión de una relación es el mismo esquema de la relación, es decir la parte definitoria de la relación, los metadatos o abstracción de la relación. Se puede representar como en la Figura 33. CURSO CODIGOCUR <pi> Number <M> NOMBRECUR Variable characters (50) INTHORARIACUR Number ESTADOCUR Variable characters (2) CPCURSO <pi> ...

Figura 33. Esquema de relación.

CURSO(CODIGOCUR:NUMERICO, NOMBRECUR:CADENA, INTHORARIACUR:NUMERICO, ESTADOCUR:CADENA)

La extensión de una relación es el conjunto de tuplas (registros) que componen la relación, todas las tuplas deben ser iguales en términos de estructura (no de valores) para satisfacer la estructura de la relación, estas tuplas se almacenan en la base de datos y suelen llamarse simplemente relación. La Figura 34 muestra un ejemplo de relación que satisface el esquema de relación de la Figura 33. CODIGOCUR

NOMBRECUR

INTHORARIACUR

ESTADOCUR

2245

Programación

2215

Lógica

2219

Base de Datos

Figura 34. Relación.

Un esquema relacional es el conjunto de esquemas de relación que describen el minimundo del problema del cual se diseña la base de datos. Cuando se crea un diagrama relacional de base de datos, se está creando un esquema relacional que satisfaga la estructura informática del mundo del problema.

NOTAS Universitarias | 40

3.2 Restricciones del modelo relacional Las siguientes son las restricciones inherentes (las que son propias del modelo) del modelo relacional: No pueden existir dos tuplas (registros) idénticos en una relación. EL orden en que se encuentran las tuplas en la relación no es importante. Esto no implica que a la hora de realizar una consulta el orden no sea importante o no se pueda mostrar el resultado de forma ordenada. El orden en que se encuentran los atributos en la relación no es importante, sin embargo y por razones ajenas al modelo, se recomienda esquematizar los atributos “más relevantes” al inicio de la relación. Cada atributo de la relación solo puede tomar un solo valor en un momento determinado del dominio al cual está asociado, es decir que por ejemplo al atributo GÉNERO solo puede ser M ó F, pero no los dos al tiempo. Esto se conoce en teoría de la normalización como la primera forma normal. El único objeto del modelo relacional es la relación, así que toda entidad y / o interrelación creada en el modelo E/R debe representarse a través de este objeto. En toda entidad debe existir un atributo o conjunto de atributos que son la clave primaria, este atributo (o atributos) no puede tomar el valor nulo (null). 3.2.1 Restricciones semánticas Las restricciones semánticas deben reflejar las reglas del mundo real. En el modelo relacional una restricción de integridad es de tipo evento condición acción, donde el evento puede ser una inserción, actualización o eliminación; la condición es un predicado que se aplica sobre el conjunto de datos sobre los cuales se dispara el evento y la acción es la respuesta al evento y condición anteriores, siendo el rechazo la acción por defecto. Por ejemplo si se desea insertar un ejemplar (registro) sobre una relación (tabla) cuyo valor en la clave primaria ya se encuentre en la relación, se rechaza la inserción. La creación de las restricciones semánticas se debe poder hacer de forma simple y precisa así como permitir cierta flexibilidad en el manejo de la condición y la acción de la restricción. Es labor del motor de base de datos permitir la definición de las restricciones semánticas así como validar la consistencia de estas reglas al momento de definirlas como al momento de aplicarlas. 3.2.2 Clasificación de las restricciones según los elementos a los que afecta la condición La forma más simple de clasificar las restricciones semánticas es de acuerdo con el o los elementos que afectan la condición de la restricción.

NOTAS Universitarias | 41

Que afectan los atributos de una sola relación (clave primaria, unicidad, not null, etc.): es definido en el esquema de relación como S1 =<A,P> donde A es el conjunto de atributos (uno o varios) a los que se aplica la condición y P es un predicado (expresión que retorna un valor booleano). Entre este tipo de restricciones se encuentran: o Restricción de Unicidad (UNIQUE): indica que un atributo (o conjunto de atributos) no puede tener los mismos valores en dos tuplas o registros; esto no indica que puedan no tener valor, es decir que sean nulos o NULL. En caso de que se intente insertar o actualizar un registro que viole esta restricción, la acción es de rechazo de la inserción o actualización. o Restricción de nulidad (NOT NULL): indica que un atributo no puede ser vacio; en realidad lo que limita esta restricción es que el atributo no pueda recibir el valor NULL. En caso de que se intente insertar o actualizar un registro que viole esta restricción, la acción es de rechazo de la inserción o actualización. o Restricción de llave primaria (PK): en una relación pueden existir uno o varios atributos que identifiquen unívocamente a cada ejemplar dentro de la relación, este o estos atributos son llamados claves candidatas y entre las claves candidatas y por razones ajenas al modelo relacional se elije una y se le denomina llave primaria, la cual indica que el o los atributos que componen esta clave NO pueden tener valores iguales en dos tuplas o registros de la relación NI ser nulos. o Restricción de llave alterna: todas las claves candidatas que no son llaves primarias se denominan claves o llaves alternas. La forma de implementar las llaves alternas es utilizando la estricción de unicidad junto a la de nulidad. o Restricción de verificación (CHECK): indica que un atributo debe cumplir con determinado predicado, se declara al momento de definir la relación y en caso de realizarse una inserción o actualización y no cumplir con el predicado, la acción es de rechazo; un ejemplo es: CHECK ESTADO = „AC‟. Que afectan los atributos de más de una relación (integridad referencial): también llamada clave o llave foránea; en una relación llamada Relacion1 existe uno o varios atributos (conjunto de atributos) cuyos valores coinciden con los valores de otro u otros atributos en la Relacion2 (Relacion1 y Relacion2 pueden ser la misma) o ser nulos. La Relacion2 se denomina la relación referenciada y la Relacion1 la relación que referencia. La llave foránea asegura la integridad de los datos al validar que los valores de la Relacion1 coincidan con los de la Relacion2 o ser nulos. Las figuras 35 y 36 muestran el esquema de relación y las relaciones de una llave foránea.

NOTAS Universitarias | 42

MATERIA

PROGRAMAACADEMICO CODPROGACA <pi> Number NOMBRPROGACA Variable characters (50) FECHREGCALPRGACA Date ESTAPROGACA Variable characters (2)

FKPROGMAT

PKPROGRAMAACADEMICO <pi> ...

CODMATE <pi> Number <M> CODPROGACAD Number NOMMATE Variable characters (50) INTHORMATE Number ESTAMATE Variable characters (2) PKMATERIA <pi> ...

Figura 35. Esquema de Relación que utiliza una llave foránea.

PROGRAMACADEMICO CODPROGACA

NOMBPROGACAD

FECHCREPROGACA

ESTAPROGACA

SISTEMAS

01/01/1986

INSDUSTRIAL

01/01/1987

CIVIL

01/01/1985

FKPROGMAT MATERIA CODMATE

CODPROGACAD

NOMMATE

INTHORMATE

ESTAMATE

2054 2048 2041

22 23 22

Lógica Procesos Estructuras de Datos Comunicación de datos

4 4 6

AC AC AC

2078

Figura 36. Relaciones con llavea foránea.

En el ejemplo anterior un nuevo registro cuyo código de programa CODPROGACA sea diferente a los valores 22, 23 o 25 (que son los registros de la relación PROGRAMACADEMICO) no podría existir en la relación MATERIA. Definidas sobre las tuplas de una relación (cardinalidad): el número de tuplas o registros que puede tener una relación se puede gestionar a través de una restricción de cardinalidad; sin embargo al implementar esta restricción es necesario el uso de disparadores o procedimientos almacenados en el motor de base de datos. Un ejemplo de esta restricción es limitar el número de alumnos matriculados en un curso.

NOTAS Universitarias | 43

Sobre los dominios (verificación sobre los valores de un dominio): todos los atributos de una relación deben ser definidos sobre un dominio y deben contener valores dentro del conjunto del dominio específico. 3.3 Paso del modelo E/R al relacional El paso de un diseño realizado utilizando el modelo entidad interrelación al modelo relación (que es el que utiliza la gran mayoría de motores comerciales de bases de datos) es relativamente simple si se siguen una serie de patrones y recomendaciones. A continuación se describirán diferentes alternativas y técnicas para realizar esta transformación. 3.3.1 Paso de una asociación muchos a muchos entre dos entidades Cuando se posee en diagrama de base de datos dos entidades relacionadas con cardinalidad muchos a muchos, la mejor opción es “romper” esa relación con una tercera entidad; es usual que nuevos atributos aparezcan en la tercera entidad. Las figuras 37 y 38 muestran un ejemplo de este proceso. CURSO

ALUMNO

CODICURS <pi> Number NOMBCURS Variable characters (50) INHOCURS Number CREDCURS Number ESTACURS Variable characters (2)

relacionMuchosAMuchos

<M>

CODALUMN <pi> Number NOMBALUM Variable characters (50) GENEALUM Variable characters (1) TELEALUM Variable characters (15)

PKALUMNO <pi> ...

PKCURSO <pi> ...

Figura 37. Dos entidades relacionadas con cardinalidad muchos a muchos CURSO

ALUMNO

CODICURS <pi> Number NOMBCURS Variable characters (50) INHOCURS Number CREDCURS Number ESTACURS Variable characters (2)

CODALUMN <pi> Number NOMBALUM Variable characters (50) GENEALUM Variable characters (1) TELEALUM Variable characters (15)

<M>

PKALUMNO <pi> ...

PKCURSO <pi> ...

INSCRIPCION FKCURINSC

CONSINSC <pi> Number <M> FECHINSC Date <M> ESTAINSC Variable characters (2) <M>

FKALUMINC

PKINSCRIPCION <pi> ...

Figura 38. Tres entidades con relaciones uno a muchos

NOTAS Universitarias | 44

3.3.2 Asociaciones uno a uno Las asociaciones uno a uno entre dos entidades (o relaciones en el modelo relacional) son muy sospechosas, sin embargo son útiles y hasta necesarias en algunos casos. La forma de implementar estas asociaciones es creando una llave foránea entre las dos entidades y adicionalmente sobre los atributos que tiene asociada la llave foránea de la tabla (relación) que referencia, se implementa una restricción de unicidad y de no nulidad (esta última según sea necesaria). La Figura 39 muestra un ejemplo de una relación uno a uno. CLIENTE

TARJETAPUNTOS

CODICLIEN <pi> Number <M> NOMCLIEN Variable characters (50) <M> ESTACLIEN Variable characters (2) <M>

CODITARJ <pi> Number <M> FECHCTARJ Date <M> PUNTTARJ Number <M>

FKCLIENTARJ

PKTARJETAPUNTOS <pi> ...

PKCLIENTE <pi> ...

Figura 39. Dos entidades relacionadas por una asociación uno a uno

3.3.3 Paso de Herencia a relaciones uno a muchos La asociación de herencia tan común en el modelo E/R (Figura 40) no existe en el modelo relacional y aunque existen muchas técnicas para transformar esta asociación, a continuación presentamos dos posibles alternativas las cuales se representan en las figuras 41 y 42. EMPLEADO DEPARTAMENTO CODIDPTO <pi> Number <M> NOMDPTO Variable characters (50) <M> PKDEPARTAMENTO <pi> ...

FKDPTOEMPLE

NUMIDENEMPL <pi> Number <M> TIPIDENEMPL <pi> Variable characters (3) <M> NOMBEMPL Variable characters (50) <M> TELEEMPL Variable characters (15) PKEMPLEADO <pi> ... ES UN

DOCENTE TIPODOCTE Variable characters (50) CARSEMDCTE Number

SERVICIOSGENERALES NUMTURSEM Number

Figura 40. Asociación de herencia entre empleado con docente y servicios generales

NOTAS Universitarias | 45

DOCENTE NUMIDENEMPL <pi> Number <M> TIPIDENEMPL <pi> Variable characters (3) <M> NOMBEMPL Variable characters (50) <M> TELEEMPL Variable characters (15) TIPODOCTE Variable characters (50) CARSEMDCTE Number

FKDPTODOCENTE

PKDOCENTE <pi> ...

DEPARTAMENTO CODIDPTO <pi> Number <M> NOMDPTO Variable characters (50) <M> PKDEPARTAMENTO <pi> ... SERVICIOSGENERALES

FKDPTOSERVGEN

NUMIDENEMPLE <pi> Number TIPIDENEMPLE <pi> Variable characters (3) NOMBEMPL Variable characters (50) TELEEMPL Variable characters (15) NUMTURSEM Number PKSERVICIOSGENERALES <pi> ...

Figura 41. Asociación entre empleado con docente y servicios generales sin herencia – opción 1

Figura 42. Asociación entre empleado con docente y servicios generales sin herencia – opción 2

NOTAS Universitarias | 46

3.3.4 Asociaciones recursivas (a la misma tabla) La relación recursiva, es decir a la misma tabla, se utiliza cuando se desea modelar una relación entre ejemplares de la misma entidad, por ejemplo como se puede apreciar en la Figura 43 existe una relación de tipo jefe – empleado, pero el jefe es a su vez un empleado, así que se considera un ejemplar dentro de la tabla.

FKJEFE

EMPLEADO CODEMPL <pi> Number NUMDOCEMPL Number TIPODOCEMPL Variable characters (2) NOMEMPL Variable characters (50) GENEEMPL Variable characters (1)

PKEMPLEADO <pi> ...

Figura 43. Asociación recursiva

3.3.5 Ejemplo de manejo de productos Un ejemplo práctico interesante de analizar es el de empresas dedicadas a la venta de productos, y específicamente a dos tipos muy diferentes de productos; por un lado encontramos productos comestibles como arroz, frijol, etc. Por otro lado tenemos productos como un vehículo o un computador. Aunque el proceso de venta es prácticamente el mismo, la diferencia radica en el control sobre la información del producto a vender; llevar control sobre todas y cada una de las bolsas de arroz vendidas probablemente sea ineficiente e innecesaria. Por el contrario, llevar control sobre cada vehículo vendido es completamente necesario. Las figuras 44 y 45 representan dos posibles diseños para la base de datos (simplificada) del proceso de venta, el primero para la venta de productos en volumen (como los comestibles) y el segundo para artículos como vehículos.

NOTAS Universitarias | 47

TIPOPROCUTO

VENTA

CODITPPROD <pi> Number <M> NOMTIPROD Variable characters (50) <M> ESTATIPROD Variable characters (2) <M>

CODIVENT <pi> Number <M> FECHVENT Date <M> ESTAVENT Variable characters (2) <M>

PKTIPOPROCUTO <pi> ...

PKVENTA <pi> ...

FKTIPPRODPROD

FKVENTDETVENT

PRODUCTO CODPROD <pi> Number CANTPROD Number (7) VALOR Number (12,2) ESTAPROD Variable characters (2)

DETALLEVENTA <M> <M> <M> <M>

FKPRODDETVENT

PKPRODUCTO <pi> ...

NUMITEM <pi> Number CANTDETVEN Number VALORVENT Number (12,2) ESTADEVE Variable characters (2)

PKDETALLEVENTA <pi> ...

Figura 44. Diagrama relacional para venta de productos sin control individual

TIPOPROCUTO

VENTA

CODITPPROD <pi> Number <M> NOMTIPROD Variable characters (50) <M> ESTATIPROD Variable characters (2) <M>

CODIVENT <pi> Number <M> FECHVENT Date <M> ESTAVENT Variable characters (2) <M>

PKTIPOPROCUTO <pi> ...

PKVENTA <pi> ...

FKTIPPRODPROD

FKVENTDETVENT

PRODUCTO CODPROD <pi> Number PLACA Variable characters (7) VALOR Number (12,2) ESTAPROD Variable characters (2) PKPRODUCTO <pi> ...

DETALLEVENTA FKPRODDETVENT

NUMITEM <pi> Number <M> ESTADEVE Variable characters (2) <M> VALORVENT Number (12,2) <M> PKDETALLEVENTA <pi> ...

Figura 45. Diagrama relacional para venta de productos con control individual

3.4

Ejercicios Crear un ejemplo de los datos (tablas en una hoja de cálculo) para cada uno de los pasos del numeral 3.3. 2. Diseñar una base de datos relacional para el problema informático de una biblioteca. En clase se plantea un dominio de una biblioteca ficticia y a partir de este dominio se genera el diseño de la base de datos. 1.

NOTAS Universitarias | 48

3. Diseña una base de datos relacional para el problema informático de una entidad bancaria. En clase se plantea un dominio de una entidad bancaria ficticia y a partir de este dominio se genera el diseño de la base de datos. 4. Diseña una base de datos relacional para el problema informático de una cadena de supermercados. En clase se plantea un dominio de una entidad cadena de supermercados ficticia y a partir de este dominio se genera el diseño de la base de datos. 5. Diseña una base de datos relacional para el problema informático de una video tienda. En clase se plantea un dominio de una video tienda ficticia y a partir de este dominio se genera el diseño de la base de datos.

NOTAS Universitarias | 49

NOTAS Universitarias | 50

4 Lenguaje de consulta estructurado SQL Existen cinco tipos de instrucciones SQL, que son: DDL (Data Definition language) o o o o o

CREATE ALTER RENAME TRUNCATE DROP

DML (Data Manipulation Language) o INSERT o UPDATE o DELETE DCL (Data Control Language) o GRANT o REVOKE

Transaction Control o o o

COMMIT SAVEPOINT ROLLBACK

Data Retrieval o

SELECT

Antes de empezar a trabajar con las instrucciones SQL es necesario conocer otros conceptos que serán utilizados de forma constante en este documento: Operadores: Los operadores aritméticos son: o Multiplicación “*” o División “/” o Adición “+” o Substracción ” -” Los operadores Lógicos son: o o o

AND OR NOT

Los operadores Relacionales son: o Igual que “=” o Diferente que “<>” o Mayor que “>” o Menor que “<”

NOTAS Universitarias | 51

o o

Mayor o igual que “>=” Menor o igual que “<=”

Tipos de Datos en Oracle Los tipos de datos comúnmente utilizados en este documento son: VARCHAR2(n), tipo de dato tipo cadena de caracteres con una longitud máxima de 4000 bytes especificada por el parámetro n. NUMBER(p,s), tipo de dato numérico, donde p es la precisión (rango 1 a 38)y s la escala (rango -84 a 124). DATE, tipo de dato fecha, permite almacenar valores desde enero 1 de 4712 AC hasta diciembre 31 de 9999 DC. BLOB, tipo de dato “Binary Large Object” para el almacenamiento de archivos en la base de datos, el tamaño máximo es de 4 GB. 4.1 Instrucciones DDL Las instrucciones DDL (Data Definition Language) son utilizadas para crear, modificar o eliminar los objetos de la base de datos. 4.1.1 Instrucción CREATE La instrucción CREATE puede ser muy compleja o relativamente simple dependiendo de su uso; a continuación veremos varios ejemplos del uso de esta instrucción: CREATE TABLE nombreDeLaTabla( identificadorColumna1 tipoDeDato, identificadorColumna2 tipoDeDato, identificadorColumnan tipoDeDato); La forma básica de la instrucción CREATE incluye el nombre de la tabla y la creación de una o más columnas. En el siguiente ejemplo podemos apreciar la creación de una tabla con cuatro columnas: CREATE TABLE ESTUDIANTE( CEDULA NUMBER, NOMBRE VARCHAR2(100), FECHANACIMIENTO DATE, ESTADO VARCHAR2(2));

La primera columna llamada CEDULA es de tipo NUMBER. La segunda columna se denomina NOMBRE y es de tipo VARCHAR2 con una tamaño de 100 caracteres. La tercera columna es llamada FECHANACIMIENTO y es de tipo DATE lo que significa que puede almacenar una fecha que incluye las horas, minutos y segundos; por último se declara la columna ESTADO de tipo VARCHAR2 con una longitud de dos caracteres. Es importante

NOTAS Universitarias | 52

resaltar cómo el tipo de datos NUMBER puede ser utilizado si ninguna especificación de su precisión y escala. El motor asigna un valor por defecto a estos parámetros. Sin embargo, si se desea tener control sobre estos parámetros es necesario especificarlos; en el caso de tipo VARCHAR2 es necesario especificar su longitud máxima; el tipo DATE por defecto permite almacenar una fecha compuesta del día, mes, año, minutos, segundos y milisegundos y no necesita de ningún parámetro adicional. RESTRICCIONES (CONSTRAINTS)

Existen cinco restricciones (constraints) principales en SQL, ellas son: Llave Primaria (Primary Key): La llave primaria es una restricción que afecta a una o varias columnas e implica que los valores de la o las columnas no pueden ser nulos ni tener valores repetidos. Llave Foránea (Foreign Key): La llave foránea es una restricción entre dos tablas A y B (la tabla B puede ser la misma A. Este concepto será explicado con detalle más adelante), en donde una o más columnas de la tabla B referencian al mismo número de columnas de la tabla A y los valores de la o las columnas de la tabla que referencia (B) deben coincidir con los valores de la o las columnas de la tabla que es referenciada (A). Restricción de No Nulidad (Not Null): La restricción de NO nulidad indica que los valores de una columna no pueden ser nulos. Unicidad (Unique): Indica que la columna o columnas “Unique” deben poseer valores únicos aunque pueden contener valores nulos. Validación booleana (Check): Es una restricción aplicable al momento de insertar o actualizar registros en la tabla y dependiendo del resultado de la expresión (el resultado es booleano es decir verdadero o falso) se puede efectuar la actualización o inserción según sea el caso. En el siguiente ejemplo podemos observar la creación de una tabla con los mismo atributos que el ejemplo anterior y con la creación de la llave primaria para el atributo CEDULA de dos formas posibles.

CREATE TABLE ESTUDIANTE( CEDULA NUMBER PRIMARY KEY, NOMBRE VARCHAR2(100), FECHANACIMIENTO DATE, ESTADO VARCHAR2(2) );

CREATE TABLE ESTUDIANTE( CEDULA NUMBER, NOMBRE VARCHAR2(100), FECHANACIMIENTO DATE, ESTADO VARCHAR2(2), CONSTRAINT PKESTUDIANTE PRIMARY KEY (CEDULA) );

NOTAS Universitarias | 53

El Primer ejemplo muestra la creación de la tabla utilizando la forma simple de asignar la restricción de llave primaria al atributo CEDULA. La segunda muestra la forma “larga” de hacerlo; como veremos en los siguientes apartados, es conveniente utilizar la segunda forma. Existe la posibilidad de crear una llave primaria compuesta de dos o más atributos, como se puede observar en el siguiente ejemplo: CREATE TABLE ESTUDIANTE( NUMDOC NUMBER PRIMARY KEY, TIPODOC VARCHAR2(2) PRIMARY KEY, NOMBRE VARCHAR2(100), FECHANACIMIENTO DATE, ESTADO VARCHAR2(2) );

CREATE TABLE ESTUDIANTE( NUMDOC NUMBER, TIPODOC VARCHAR2(2), NOMBRE VARCHAR2(100), FECHANACIMIENTO DATE, ESTADO VARCHAR2(2), CONSTRAINT PKESTUDIANTE KEY (NUMDOC,TIPODIC) );

PRIMARY

En el ejemplo anterior se puede observar cómo en la tabla ESTUDIANTE (utilizando cualquiera de los dos métodos), se declara una llave primaria (solo puede existir una sola llave primaria por tabla), aunque esta llave primaria este compuesta por las columnas NUMDOC Y TIPODOC. La forma general de crear una restricción tipo llave primaria dentro de la declaración de la tabla es: CONSTRAINT NombreLlavePrimaria PRIMARY KEY (columna(s))

En el siguiente ejemplo podemos observar como en la declaración de la tabla se adicionan dos nuevos tipos de restricciones, el “Unique” y el “Not Null”: CREATE TABLE ESTUDIANTE( NUMDOC NUMBER, TIPODOC VARCHAR2(2), CODIGO NUMBER NOT NULL, NOMBRE VARCHAR2(100) NOT NULL, FECHANACIMIENTO DATE NOT NULL, CELULAR VARCHAR2(20), ESTADO VARCHAR2(2) NOT NULL, CONSTRAINT PKESTUDIANTE PRIMARY KEY (NUMDOC,TIPODIC), CONSTRAINT ESTUCODIUNIQUE UNIQUE(CODIGO) );

Como se puede observar todas las columnas de la tabla a excepción de la columna CELULAR son ahora “Not Null” lo cual indica que no pueden tener valores nulos; se podría

NOTAS Universitarias | 54

considerar que las columnas NUMDOC y TIPODOC pueden recibir valores nulos (su característica por defecto es permitir valores nulos), sin embargo al ser llave primaria, automáticamente se añade la restricción de “Not Null” a estas columnas. La restricción “Unique” afecta a la columna CODIGO impidiendo que en esta columna existan valores duplicados. La forma simple de crear restricciones “Not Null” a una columna en una tabla es simplemente agregando la frase “NOT NULL” después del tipo de datos de la columna. La forma general de crear una restricción tipo “Unique” dentro de la declaración de la tabla es: CONSTRAINT NombreRestriccionUnique UNIQUE (columna(s))

Las llaves foráneas se crean de una forma similar al resto de restricciones. Sin embargo, es fundamental tener claro donde se crea. La llave foránea se crea en la tabla que referencia y NO en la tabla referenciada; la Figura 46 muestra a la izquierda la tabla que es referenciada y a la derecha la tabla que referencia, como se puede observar la terminación en tres líneas llega a la tabla que referencia. ALUMNO

PROGRAMAACADEMICO CODPROGACA <pi> Number NOMBRPROGACA Variable characters (50) FECHREGCALPRGACA Date ESTAPROGACA Variable characters (2)

FKPROGACAALUM

PKPROGRAMAACADEMICO <pi> ...

CODIGOALUM <pi> Number NOMBREALUM Variable characters (50) DIRECCIONALUM Variable characters (50) GENEROALUM Variable characters (1) ESTADOALUM Variable characters (2)

CPALUMNO <pi> ...

Figura 46. Tabla que referenciada y tabla que referencia. CREATE TABLE PROGRAMAACADEMICO( CODPROGACA NUMBER, NOMBPROGACA VARCHAR2(50) NOT NULL, FECHREGCALPRGACA DATE NOT NULL, ESTAPROGACA VARCHAR2(2) NOT NULL, CONSTRAINT PKPROGRAMAACADEMICO PRIMARY KEY (CODPROGACA) );

CREATE TABLE ALUMNO( CODIGOALUM NUMBER, NOMBREALUM VARCHAR2 (50) NOT NULL, DIRECCIONALUM VARCHAR2 (50), GENEROALUM VARCHAR2 (1) NOT NULL, ESTADOALUM VARCHAR2 (2) NOT NULL, CODPROGACA NUMBER NOT NULL, CONSTRAINT CPALUMNO PRIMARY KEY (CODIGOALUM), CONSTRAINT FKPROGACAALUM FOREIGN KEY (CODPROGACA) PROGRAMAACADEMICO(CODPROGACA) NOTAS | 55 );

Universitarias

REFERENCES

En el ejemplo anterior se puede observar la codificación del diagrama de la Figura 43; es importante resaltar nuevamente como la llave foránea se crea en la tabla que referencia a través de la instrucción: nombrellaveForanea FOREIGN KEY (atributoAsociado) REFERENCES TablaReferenciada(atributoReferenciado) CONSTRAINT

Otro objeto de la base de datos que se puede crear utilizando la instrucción CREATE es un índice (estructura que hace más eficiente la localización de los datos en las tablas) y la instrucción general de construcción es: CREATE INDEX nombreDelIndice ON nombreDelaTabla (atributo(s)

de la tabla)

El siguiente ejemplo muestra cómo crear un índice llamado indiceapellido sobre el atributo apellido de la tabla empleado: CREATE INDEX indiceapellido ON EMPLEADO(APELLIDO);

Muchas veces se utiliza un atributo auto numerado en una tabla, es decir un atributo que toma un valor automáticamente; en el caso del motor de base de datos Oracle, la forma de implementar ésto es atreves de una secuencia. Una secuencia es un objeto de la base de datos que posee un valor numérico y cada vez que realizamos una inserción en una tabla y utilizamos esta secuencia, se incrementa o decrementa (según sea el caso) automáticamente. La forma de crear una secuencia es la siguiente: CREATE SEQUENCE nombreDeLaSecuencia START WITH valorInicial;

En el siguiente ejemplo se crea una secuencia llamada secEmpleado que inicia su valor en 1000: CREATE SEQUENCE secEmpleado START WITH 1000;

4.1.2 Instrucción ALTER La instrucción ALTER es utilizada cuando se desea realizar una modificación estructural de una tabla de la base de datos, como adicionar un atributo, modificar el tamaño o tipo de un atributo existente o eliminar un atributo. Para adicionar un atributo la instrucción genérica del ALTER es: ALTER TABLE nombreDeLaTabla ADD (nombreAtributo tipoDeDato);

NOTAS Universitarias | 56

En el siguiente ejemplo se adiciona un atributo llamado telCelular de tipo varchar2 a la tabla EMPLEADO: ALTER TABLE EMPLEADO ADD (telCelular varchar2(12));

Para modificar un atributo existente dentro de una tabla se utiliza la instrucción: ALTER TABLE nombreDeLaTabla MODIFY (nombreAtributo tipoDeDato);

El nombreAtributo es un atributo ya existente y el tipoDeDato es el nuevo tipo de dato del atributo o el mismo con diferente longitud; las siguientes reglas deben tenerse en cuenta al momento de realizar la modificación: Solo se puede decrementar el tamaño de un atributo si la tabla no tiene valores en ese atributo. Solo se puede cambiar el tipo de dato del atributo si el atributo no tiene valores en la tabla. Solo se puede asignar la restricción NOT NULL al atributo si éste no posee valores NULOS. Para eliminar un atributo de la tabla se utiliza la instrucción: ALTER TABLE nombreDeLaTabla DROP COLUMN nombreAtributo;

El siguiente ejemplo elimina el atributo llamado telCelular de la tabla EMPLEADO: ALTER TABLE EMPLEADO DROP COLUMN telCelular;

4.1.3 Instrucción RENAME La instrucción RENAME renombra una tabla a un nuevo nombre, la instrucción es: RENAME nombreActual TO nuevoNombre;

En el siguiente ejemplo se renombra la tabla ESTUDIANTE por ALUMNO. RENAME ESTUDIANTE TO ALUMNO;

NOTAS Universitarias | 57

4.1.4 Instrucción TRUNCATE La instrucción TRUNCATE se utiliza cuando se desea eliminar todos los registros de una tabla sin eliminar la tabla en sí, es mucho más eficiente que la instrucción DELETE (ver más adelante) y no genera ROLLBACK. La instrucción genérica es: TRUNCATE TABLE nombreDeLaTable;

4.1.5 Instrucción DROP La instrucción DROP es utilizada para eliminar objetos de la base de datos como las tablas, la siguiente instrucción y el ejemplo a continuación demuestran el uso de la instrucción DROP. DROP TABLE nombreDeLaTabla; DROP TABLE nombreDeLaTabla CASCADE CONSTRINTS; DROP TABLE EMPLEADO; DROP TABLE EMPLEADO CASCADE CONSTRINTS;

La clausula CASCADE CONSTRINTS se utiliza cuando hay llaves foráneas relacionadas con la tabla y se deben borrar.

4.2 Instrucciones DML Las instrucciones DML (Data Manipulation Language) se encargan de manipular los datos que se encuentran en las tablas de la Base de Datos.; las tres instrucciones clave son el INSERT, UPDATE y el DELETE. 4.2.1 Instrucción INSERT La instrucción INSERT es utilizada para insertar nuevos registros en las tablas, tiene dos posibles versiones de acuerdo a la forma de utilización, las cuales son: INSERT INTO nombreDeLaTabla VALUES(valor1, valor2, valor_n);

nombreDeLaTabla(atributo1, atributo2, atributon) VALUES(valor1, valor2, valor_n); INSERT INTO

La diferencia entre las dos versiones radica en que en la segunda especificamos el orden de los atributos, mientras que en la primera se asume el orden de los atributos que en este momento posee la tabla. En los siguientes ejemplos se muestra el uso de la instrucción INSERT en la tabla CURSO, la cual tiene cuatro atributos, el primero de tipo

NOTAS Universitarias | 58

numérico, el segundo de tipo cadena de caracteres, el siguiente de tupo fecha y el último de tipo cadena pero que puede ser NULO. INSERT INTO CURSO VALUES (1102, „CALCULO I‟, „01/01/2011‟, NULL); INSERT INTO CURSO (NOMBCUR, CODCUR, ESTACUR, FECHCUR) VALUES („CALCULO I‟, 1102, NULL, „01/01/2011‟);

El tipo fecha se puede ingresar como una cadena de caracteres, ya que el motor realiza la transformación al tipo de dato adecuado siempre y cuando el formato de la fecha coincida con el formato establecido en el motor de la base de datos. 4.2.2 Instrucción UPDATE La instrucción UPDATE es utilizada con el fin de actualizar uno o varios datos existentes en una tabla. La forma general de utilizar la instrucción es: UPDATE nombreDeLaTabla SET atributoX = valorX WHERE atributoY = valorY;

Donde atributoX es el atributo de la tabla al que queremos cambiar su valor por el valorX y atributoY es el atributo sobre el cual se evaluara la condición para cambiar el valor en ese registro siempre y cuando sea igual al valorY. El siguiente ejemplo ilustra el uso de la instrucción UPDATE para actualizar el estado del curso a „AC‟ siempre y cuando el código del curso sea 1102. UPDATE CURSO SET ESTACUR = ‘AC’ WHERE CODICUR = 1102;

La clausula WHERE puede ser más compleja que la comparación entre un atributo y un valor, pueden ser expresiones booleanas que incluyan operadores lógicos y relacionales. 4.2.3 Instrucción DELETE La instrucción DELETE se utiliza para eliminar registros de una tabla, posee dos versiones tanto para eliminar todos los registros de la tabla como para eliminar solo alguno(s). DELETE FROM nombreDeLaTabla; DELETE FROM nombreDeLaTabla WHERE condición;

NOTAS Universitarias | 59

En los siguientes ejemplos se eliminan todos los registros (primer ejemplo) de la tabla y solo los que cumplan con la condición de tener estado „AC‟ (segundo ejemplo). DELETE FROM CURSO; DELETE FROM CURSO WHERE ESTACUR = ‘AC’;

4.3 Instrucciones DCL Las instrucciones DCL (Data Control Language) son utilizadas para dar privilegios a los usuarios de la base de datos, es decir dar permisos como poder insertar, actualizar, etc sobre los datos. 4.3.1 Instrucción GRANT La instrucción GRANT otorga privilegios a los usuarios, la estructura general de la instrucción es: GRANT nombreProvilegio TO nombreUsuario;

Donde nombreProvilegio es el nombre del privilegio que se quiere otorgar y nombreUsuario es el usuario al cual se le quiere otorgar el privilegio. Es válido en lugar de otorgar un privilegio, otorgar un rol (conjunto de privilegios); en el siguiente ejemplo se otorga el rol de DBA (Data Base Administrator) al usuario ESTUDIANTE. GRANT DBA TO ESTUDIANTE;

4.3.2 Instrucción REVOKE La instrucción REVOKE a diferencia de GRANT, remueve privilegios de un usuario; la estructura general de la instrucción es: REVOKE nombreProvilegio FROM nombreUsuario;

El siguiente ejemplo revoca los privilegios (rol en este caso) de DBA del usuario ESTUDIANTE. REVOKE DBA FROM ESTUDIANTE;

Para otorgar y revocar privilegios se debe ser un usuario de la base de datos con el privilegio de otorgar / revocar privilegios, es muy común que sea el administrador de la base de datos quien se encargue de esta tarea.

NOTAS Universitarias | 60

4.4 Instrucciones de Control de Transacciones Las instrucciones de control de transacciones permiten confirmar una transacción (o conjunto de transacciones) en la base de datos como un INSERT, UPDATE ó DELETE. Cuando varios usuarios de forma simultánea acceden a la base de datos, el motor gestiona una parte de la memoria para las transacciones que realiza cada usuario, esta memoria es temporal mientras dura la sesión del usuario y permite mantener separadas las transacciones de diferentes usuarios; sin embargo cuando un usuario desee confirmar o deshacer las transacciones realizadas tiene que utilizar un comando especifico. 4.4.1 Instrucción COMMIT La instrucción COMMIT permite confirmar el conjunto de transacciones realizadas por un usuario desde la última confirmación; la forma de utilizarlo es simplemente digitando el comando en la consola de la base de datos. COMMIT;

Cuando se realiza una instrucción DDL se hace implícitamente un COMMIT. 4.4.2 Instrucción SAVEPOINT La instrucción SAVEPOINT permite crear un “punto de salvado” entre transacciones, es común utilizarlo cuando queremos realizar diferentes transacciones por lotes sin mezclarlas ni confirmar por cada lote o al terminar. La instrucción general es: SAVEPOINT nombreDelSavePoint; En el siguiente ejemplo se realizan 5 transacciones y dos SAVEPOINT. INSERT INTO EMPLEADO VALUES(1,‟CARLOS‟,‟AC‟); INSERT INTO EMPLEADO VALUES(2,‟ANDRES‟,‟AC‟); SAVEPOINT SAV1; INSERT INTO EMPLEADO VALUES(3,‟ANGELA‟,‟AC‟); INSERT INTO EMPLEADO VALUES(4,‟PILAR‟,‟AC‟);

SAVEPOINT SAV2; INSERT INTO EMPLEADO VALUES(5,LUIS,‟AC‟);

Esta instrucción es usada comúnmente en conjunto a la instrucción ROLLBACK la cual se especifica a continuación. 4.4.3 Instrucción ROLLBACK La instrucción ROLLBACK es utilizada para “deshacer” las transacciones realizadas hasta el último punto de confirmación (COMMIT); esta instrucción tiene dos posibles versiones, en la primera simplemente se utiliza ROLLBACK hasta la última confirmación, en la segunda se

NOTAS Universitarias | 61

deshacen las transacciones hasta el punto de salvado que se desee. Las instrucciones genéricas de ROLLBACK son las siguientes: ROLLBACK; ROLLBACK nombreDelSavePoint;

Si se realiza un ROLLBACK hasta un SAVEPOINT, éste debe haberse creado con anterioridad. Al realizarse un ROLLBACK, todos los SAVEPOINT creados son eliminados automáticamente. 4.5

Instrucciones de recuperación de datos La instrucción de recuperación de datos SELECT permite mostrar al usuario los datos almacenados en las tablas de la base de datos. 4.5.1 Instrucción SELECT La instrucción SELECT se compone de varias partes, dos obligatorias y las demás opcionales. La instrucción general de consulta simple es: SELECT columna1, columna2, columnaN FROM nombreDelaTabla;

Donde columna1, columna2, etc. Son los nombres de las columnas que queremos mostrar y el nombreDeLaTabla es la tabla de donde se encuentran los datos que queremos mostrar. Por ejemplo si queremos mostrar las columnas CODICUR y NOMBCUR de la tabla CURSO, podemos utilizar la siguiente instrucción: SELECT CODICUR, NOMBCUR FROM CURSO;

Esta consulta mostraría todos los registros de la tabla CURSO, específicamente en las columnas CODICUR, NOMBCUR; si se desea limitar el número de registros, se debe utilizar la clausula WHERE. En el siguiente ejemplo se muestra como realizar la misma consulta del ejemplo anterior, pero limitando el numero de registros solo a los cursos que tengan estado ‟AC‟. SELECT CODICUR, NOMBCUR FROM CURSO WHERE ESTACUR = ‘AC’;

La expresión asociada a la clausula WHERE debe arrojar un resultado booleano y en esta expresión se pueden utilizar los operadores aritméticos, lógicos y relacionales que

NOTAS Universitarias | 62

se consideren útiles para la condición. En general la estructura de la instrucción SELECT con condición es: SELECT columna1, columna2, columnaN

FROM nombreDelaTabla WHERE expresionDeCondicion;

4.6 Proceso de respaldo y recuperación Para realizar el proceso de importación de una base de datos ejemplo en el motor Oracle, como la base de datos DEMO, es necesario seguir los siguientes pasos: Debe existir un usuario a donde importar como "seminario" en este caso. 1. Abrir una ventana de comandos y dirigirse al directorio donde fue instalado Oracle, específicamente a la carpeta BIN y el programa IMP.exe ej: C:\Oracle\DataBase\product\11.1.0\db_2\BIN 2. Digitar en la ventana de comandos: IMP system/oracle FROMUSER=DEMO TOUSER=SEMINARIO FILE='G:\backup\Semestre A de 2010\Seminarios\BASE DE DATOS DEMO\backcal.dmp' Donde system/Oracle es la pareja nombre de usuario / contraseña que posee privilegios de importación. FROMUSER= DEMO, es el usuario de donde se realizo el backup. TOUSER=CURSO, es el usuario donde quedara restablecido el backup. FILE = „…‟ es la ruta donde se encuentra el archivo con el backup. Para realizar la exportación, es decir el respaldo de la base de datos, se realizan los pasos anteriores, pero en lugar de utilizar el comando IMP, se utiliza el comando EXP. 4.7

Ejercicios

1. Investigar sobre diferentes opciones adicionales a las instrucciones SQL de este capítulo. 2. Codificar la base de datos basada en el diseño creado en el capitulo anterior. 3. Codificar la base de datos del punto anterior en por lo menos tres motores diferentes de base de datos. 4. Realizar 5 consultas simples a la base de datos DEMO. 5. Investigar y realizar 5 consultas de tipo join a la base de datos DEMO. 6. Utilizar el comando EXP para exportar una base de datos.

NOTAS Universitarias | 63

Bibliografía De Miguel, Adoración; Piattini, Mario y Marcos, Esperanza. Diseño de Bases de Datos Relacionales. España. Alfaomega. 2000. 549 p. Connolly, Thomas y Begg, Carolyn. Sistemas de bases de datos. 4 ed. España. Pearson. 2005. 1320 p. Silberschatz, Abraham; Korth, Henry y Sudarshan. Fundamentos de bases de datos. 5 ed. España. McGraw-Hill. 2006. 953 p. Elmasri, Ramez y Navathe, Shamkant. Sistemas de bases de datos : Conceptos gundamentales. 2 ed. Estados Unidos de America. Addison Wesley. 1997. 887 p. Urman, Scott. Oracle 8 : Programación PL/SQL. España. Osborne. 1999. 778 p.

Cibergrafía http://es.wikipedia.org/wiki/Base_de_datos (Definicion de base de datos, consultado: 2011) http://www.alegsa.com.ar/Dic/sgbase de datos.php (Sistema gestor de base de datos, consultado: 2011) http://www3.uji.es/~mmarques/f47/apun/node83.html consultado: 2011)

(Modelo

Entidad

Interrelación,

http://www.monografias.com/trabajos24/herramientas-case/herramientas-case.shtml (Herramientas CASE para bases de datos, consultado: 2011) www.uazuay.edu.ec/analisis/El%20modelo%20relacional.pdf (EL modelo relacional, consultado: 2011) http://ora.u440.com/ddl/ (Instrucciones DDL, consultado: 2011) http://ora.u440.com/dml/ (Instrucciones DML, consultado: 2011) http://psoug.org/reference/dcl.html (Instrucciones DCL, consultado: 2011) http://ist.marshall.edu/ist480adbp/plsql_trnctl.html consultado: 2011)

(Instrucciones

Transaction

http://psoug.org/reference/select.html (Instrucciones Data Retrieval, consultado: 2011)

NOTAS Universitarias | 64

Control,