• Author / Uploaded
• Alejandra Vidal

• Categories
• Postgre Sql
• SQL
• Bases de datos
• Base de datos relacional
• Tabla (base de datos)

Citation preview

Curso de SQL - PostgreSQL Ing. Mario Soto Cordones

Agosto de 2014

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 1 de 253

Contenido Introducción ...................................................................................................................................... 13 Lección I - Bases de datos relacionales: “El modelo relacional” ....................................................... 14 ¿Qué son las Bases de Datos (BD)? ............................................................................................... 14 El Sistema Gestor de Base de Datos (SGBD) ................................................................................. 14 El modelo Entidad-Relación (E-R).................................................................................................. 14 Entidades......................................................................................................... 15 Relaciones ....................................................................................................... 16 Base de Datos Relacionales ........................................................................................................... 16 Ventajas .......................................................................................................... 16 Características ................................................................................................. 17 Motores de Bases de Datos Relacionales ..................................................................................... 18 PostgreSQL .................................................................................................................................... 19 Un poco de historia ......................................................................................... 19 Características ................................................................................................. 19 Lección II - Bases de datos relacionales: Consulta de bases de datos relacionales .......................... 21 Utilizando una Base de Datos Relacional ...................................................................................... 21 Consultas en lenguajes de alto nivel ............................................................................................. 21 Consultas y relaciones (tablas) ...................................................................................................... 22 Lenguajes de consultas ................................................................................................................. 22 Utilizando Álgebra Relacional ....................................................................................................... 23 Utilizando SQL ............................................................................................................................... 23 Lección III - Álgebra Relacional: Select, Project, Join ........................................................................ 24 Conceptos básicos de álgebra relacional ...................................................................................... 24 Operaciones relacionales: ............................................................................................................. 25 Selección (Select) .......................................................................................................................... 25 Proyección (Project) ...................................................................................................................... 28 Notación en Álgebra Relacional ....................................................................... 28 Producto cartesiano (Cross-Product) ............................................................................................ 29 Notación en Álgebra Relacional ....................................................................... 30 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 2 de 253

Natural Join ................................................................................................................................... 32 Notación en Álgebra Relacional ....................................................................... 32 Equivalencia con operadores básicos............................................................... 32 Método ............................................................................................................ 32 ThetaJoin ....................................................................................................................................... 33 Notación en Álgebra Relacional ....................................................................... 34 Equivalencia con operadores básicos............................................................... 34 Lección IV - Álgebra Relacional: Set operators, renaming, notation ................................................ 37 Operaciones de conjunto .............................................................................................................. 37 Unión............................................................................................................... 37 Notación en álgebra relacional ........................................................................ 38 Diferencia ........................................................................................................ 38 Notación en álgebra relacional ........................................................................ 39 Intersección ..................................................................................................... 40 Notación en algebra relacional ........................................................................ 40 Equivalencia con operadores anteriores .......................................................... 40 Operaciones dependientes e independientes .............................................................................. 41 Álgebra relacional como idioma restrictor.................................................................................... 41 Ejercicios Propuestos .................................................................................................................... 42 Lección V – Introducción al Lenguaje SQL ......................................................................................... 45 Características ............................................................................................................................... 45 Lenguaje de descripción de datos (DDL) ....................................................................................... 45 Ejemplos de DDL: ............................................................................................ 46 Lenguaje de manipulación de datos (DML)................................................................................... 46 Ejemplos de DML ............................................................................................. 47 Álgebra relacional:........................................................................................... 48 Comandos SQL: ............................................................................................... 48 Clave Primaria y Foránea............................................................................................................... 52

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 3 de 253

Lección VI - Tipo de Datos ................................................................................................................. 56 Descripción .................................................................................................................................... 56 Tipo de Dato Serial ........................................................................................................................ 60 Lección VII – Sentencia SELECT Básica .............................................................................................. 61 Desde el Álgebra Relacional .......................................................................................................... 61 SELECT-FROM-WHERE................................................................................................................... 62 Resultados Repetidos .................................................................................................................... 66 SELECT-BY-ORDER ......................................................................................................................... 67 Lección VIII - Variables de tablas y operadores de conjunto ............................................................ 70 Variables de tablas ........................................................................................................................ 70 Ejemplo 1 ........................................................................................................ 71 Ejemplo 2 ........................................................................................................ 72 Establecimiento de Operadores .................................................................................................... 74 Unión............................................................................................................... 74 Intersección ..................................................................................................... 76 Excepción ........................................................................................................ 76 Lección IX- Subconsultas en la cláusula WHERE................................................................................ 78 SELECT-FROM-WHERE (SELECT) .................................................................................................... 78 Ejemplo 1 ........................................................................................................ 80 Ejemplo 2 ........................................................................................................ 82 IN AND NOT IN .............................................................................................................................. 83 Ejemplo 3 ........................................................................................................ 83 EXISTS AND NOT EXISTS ................................................................................................................ 84 Ejemplo 4 ........................................................................................................ 84 CÁLCULOS MATEMÁTICOS ............................................................................................................ 85 Ejemplo 5 ........................................................................................................ 85 Lección X - Subconsultas en FROM y SELECT .................................................................................... 87 SELECT (SELECT)-FROM-WHERE .................................................................................................... 87 Ejemplo 1 ........................................................................................................ 87

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 4 de 253

Ejemplo 2 ........................................................................................................ 88 Ejemplo 3 ........................................................................................................ 89 Ejemplo 4 ........................................................................................................ 89 SELECT-FROM (SELECT)-WHERE .................................................................................................... 89 Ejemplo 5 ........................................................................................................ 90 RECAPITULACIÓN .......................................................................................................................... 91 Ejemplo extra .................................................................................................. 91 Lección XI - La familia de operadores JOIN ....................................................................................... 93 INNER JOIN .................................................................................................................................... 93 NATURAL JOIN ............................................................................................................................... 95 INNER JOIN USING (attrs) .............................................................................................................. 96 LEFT|RIGHT|FULL OUTER JOIN ..................................................................................................... 96 LEFT OUTER JOIN ........................................................................................................................... 97 RIGHT OUTER JOIN ........................................................................................................................ 97 FULL OUTER JOIN .......................................................................................................................... 98 Lección XII - Funciones de Agregación ............................................................................................ 100 Funciones de Agregación ............................................................................................................ 100 Función AVG() ................................................................................................ 101 Función COUNT() ........................................................................................... 101 SQL COUNT (nombre_columna) ..................................................................... 101 SQL COUNT(*) ................................................................................................ 102 SQL COUNT (DISTINCT nombre_columna) ..................................................... 102 Función MAX() ............................................................................................... 103 Función MIN() ................................................................................................ 103 Función SUM()................................................................................................ 103 SQL GROUP BY............................................................................................................................. 104 SQL HAVING ................................................................................................................................ 104 Lección XIII - SQL: Valores NULL ...................................................................................................... 106 CREATE TABLE ............................................................................................................................. 106 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 5 de 253

INSERT y UPDATE ........................................................................................................................ 107 SELECT ......................................................................................................................................... 108 Seleccionar atributos NULL ............................................................................ 108 Comparaciones con NULL .............................................................................. 109 Operaciones con NULL................................................................................... 111 Operadores lógicos ......................................................................................................................... 111 Lección XIV - SQL: Declaraciones de las Modificaciones de Datos.................................................. 114 INSERT ......................................................................................................................................... 114 Contexto ....................................................................................................... 114 Ejemplo 1 ...................................................................................................... 115 Ejemplo 2 ...................................................................................................... 116 UPDATE ....................................................................................................................................... 116 Ejemplo 3 ...................................................................................................... 117 DELETE ......................................................................................................................................... 118 Ejemplo 4 ...................................................................................................... 118 Ejemplo 5 ...................................................................................................... 119 RECAPITULACIÓN ........................................................................................................................ 119 Ejemplo extra ................................................................................................ 119 Lección XV - Teoría del diseño Relacional: Información General.................................................... 122 Diseñar un esquema de base de datos ....................................................................................... 122 Ejercicio ......................................................................................................... 124 Diseño por descomposición ........................................................................................................ 124 Normalización ............................................................................................................................. 125 Formas normales ........................................................................................... 125 Lección XVI - Teoría del Diseño Relacional: Dependencia Funcional .............................................. 133 Dependencia Funcional ............................................................................................................... 133 Ejemplo 1: ..................................................................................................... 133 Ejemplo 2 ...................................................................................................... 134

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 6 de 253

Ejemplo 3 ...................................................................................................... 135 Dependencias funcionales y llaves .............................................................................................. 135 Reglas para las dependencias funcionales .................................................................................. 136 Ejemplo 4 ...................................................................................................... 137 Clausura y llaves .......................................................................................................................... 137 Ejemplo 5 ...................................................................................................... 137 Especificación funcionalmente dependiente para una relación ................................................. 138 Ejemplo 6 ...................................................................................................... 138 Lección XVII - Teoría del diseño relacional: Forma normal Boyce-Codd......................................... 139 Conceptos previos ....................................................................................................................... 139 Tipos de claves (llaves) .................................................................................. 139 Forma normal Boyce-Codd.......................................................................................................... 140 Definición ...................................................................................................... 140 Descomposición para lograr la FNBC ............................................................. 141 Lección XVIII - Teoría de Diseño Relacional: Dependencias Multivaluadas (4ta forma normal) .... 145 Dependencias multivaluadas............................................................................................... 145 Introducción .................................................................................................. 145 Atributo de independencia y redundancia ..................................................... 145 Definición formal ........................................................................................... 146 Cuarta Forma Normal ............................................................................................................ 147 Definición ...................................................................................................... 147 Lección XIX - Lenguaje de modelado unificado: UML modelado de datos ..................................... 152 Clases......................................................................................................................................... 152 Ejemplo 1 ...................................................................................................... 153 Asociaciones ............................................................................................................................ 153 Ejemplo 2 ...................................................................................................... 153 Ejemplo 3 ...................................................................................................... 154 Ejemplo 4 ...................................................................................................... 155 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 7 de 253

Clase de asociación................................................................................................................ 155 Ejemplo 5 ...................................................................................................... 155 Ejemplo 6 ...................................................................................................... 157 Subclases .................................................................................................................................. 157 Ejemplo 7 ...................................................................................................... 158 Composiciones y Agregaciones.......................................................................................... 158 Ejemplo 8 ...................................................................................................... 158 Ejemplo 9 ...................................................................................................... 159 Lección XX - Lenguaje de Modelado Unificado: UML y las bases de datos..................................... 160 Clases......................................................................................................................................... 161 Asociaciones ............................................................................................................................ 162 Claves para las relaciones de asociación ....................................................... 162 Clase de asociación................................................................................................................ 164 Subclases .................................................................................................................................. 165 Ejemplo de subclases .................................................................................... 166 Composición y Agregación .................................................................................................. 167 Composición ................................................................................................. 167 Agregación .................................................................................................... 167 Lección XXI - Restricciones y triggers: Introducción........................................................................ 168 Restricciones ............................................................................................................................ 169 Ejemplo 1 ...................................................................................................... 169 Ejemplo 2 ...................................................................................................... 169 Ejemplo 3 ...................................................................................................... 169 Declarando y forzando restricciones ............................................................. 169 triggers ................................................................................................................................... 170 Ejemplo 4 ...................................................................................................... 170 Ejemplo 5 ...................................................................................................... 170 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 8 de 253

Lección XXII - Restricciones y triggers: Restricciones de múltiples tipos ........................................ 171 Contexto ................................................................................................................................... 171 Restricciones para evitar valores NULL............................................................................. 172 Ejemplo 1 ...................................................................................................... 172 Ejemplo 2 ...................................................................................................... 172 Restricciones de clave primaria .......................................................................................... 172 Ejemplo 3 ...................................................................................................... 173 Ejemplo 4 ...................................................................................................... 173 Ejemplo 5 ...................................................................................................... 174 Ejemplo 6 ...................................................................................................... 175 Ejemplo 7 ...................................................................................................... 176 Restricciones de atributo y tupla ....................................................................................... 177 Ejemplo 8 ...................................................................................................... 177 Ejemplo 9 ...................................................................................................... 178 Ejemplo 10 .................................................................................................... 179 Ejemplo 11 .................................................................................................... 179 Restricciones generales ........................................................................................................ 180 Ejemplo 12 .................................................................................................... 180 Lección XXIII - Restricciones y triggers: Integridad Referencial .................................................. 181 Ejemplo...................................................................................................................................... 183 Aplicación de la integridad referencial (R.A a R.B).......................................... 184 Ejemplo...................................................................................................................................... 184 Acciones especiales ....................................................................................... 185 Ejemplo ON DELETE SET NULL ............................................................................................ 189 Ejemplo CASCADE ..................................................................................................................... 190 Lección XXIV - Restricciones y Triggers: Triggers introducción y demostración ........................ 192 Triggers (disparadores) ........................................................................................................ 192 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 9 de 253

Definición ...................................................................................................... 192 Algunas aplicaciones de Triggers .................................................................. 193 Creación de un disparo ................................................................................. 193 Funciones ...................................................................................................... 195 Cuándo no deben usarse los Triggers ........................................................... 200 Lección XXV - Transacciones: Introducción .................................................................................. 201 Integridad de las transacciones.......................................................................................... 202 Nivel de Inconsistencia en Atributos .............................................................. 202 Nivel de Inconsistencia en Tuplas .................................................................. 203 Nivel de Inconsistencia en Tablas .................................................................. 203 Objetivo de la Concurrencia ................................................................................................ 204 Objetivo Principal .......................................................................................... 204 Resistencia a fallos del sistema .......................................................................................... 204 Solución para la concurrencia y fallos .............................................................................. 205 Lección XXVI - Transacciones: Propiedades .................................................................................... 206 Aislamiento (I) ......................................................................................................................... 206 Ejemplo ......................................................................................................... 207 Durabilidad (D) ........................................................................................................................ 209 Atomicidad (A)......................................................................................................................... 210 Ejemplo ......................................................................................................... 210 Deshacer (Rollback) Transacción ................................................................... 211 Consistencia (C) ...................................................................................................................... 213 Lección XXVII – Vistas: Definición y usos......................................................................................... 214 Definición ................................................................................................................................. 214 Usos de las vistas ................................................................................................................... 215 Creación de una vista ............................................................................................................ 215 Ejemplo 1 ...................................................................................................... 216 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 10 de 253

Ejemplo 2 ...................................................................................................... 217 Ejemplo 3 ...................................................................................................... 218 Ejemplo 4 ...................................................................................................... 219 Lección XXVIII - Vistas: Vistas y modificaciones automáticas..................................................... 220 Reglas ........................................................................................................................................ 221 Contexto ................................................................................................................................... 221 Modificación automática de vistas y tablas..................................................................... 222 Ejemplo 1 ...................................................................................................... 223 Ejemplo 2 ...................................................................................................... 224 Ejemplo 3 ...................................................................................................... 224 Ejemplo 4 ...................................................................................................... 225 Conclusiones ........................................................................................................................... 225 Lección XXIX - Vistas: Vistas materializadas................................................................................ 226 Introducción ............................................................................................................................. 226 Definición ................................................................................................................................. 226 Ventaja .......................................................................................................... 226 Desventajas ................................................................................................... 226 Mantenimiento de las vistas ................................................................................................ 227 Creación de una vista ............................................................................................................ 227 Ejemplo ......................................................................................................... 230 Lección XXX – Mantenimiento de la Base de Datos ..................................................................... 233 Introducción ............................................................................................................................. 233 Rutinas de mantenimiento y monitoreo........................................................................... 233 Vacuum ......................................................................................................... 233 Reindexación ................................................................................................. 235 Ficheros de registro....................................................................................... 236 Explain .......................................................................................................... 236 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 11 de 253

Lección XXXI - Servicios de Respaldo y Recuperación para Bases de Datos (BD) ...................... 238 Servicios en PostgreSQL........................................................................................................ 239 SQL Dump................................................................................................................................. 239 pg_dump ....................................................................................................... 239 Ejemplo 1 ...................................................................................................... 239 Ejemplo 2 ...................................................................................................... 242 Ejercicio propuesto ........................................................................................ 243 pg_dumpall ................................................................................................... 244 Respaldo a nivel de archivos ............................................................................................... 244 Rsync ............................................................................................................. 245 Conclusiones ........................................................................................................................... 245 Lección XXXII – Querys al Catalogo de PostgreSQL ......................................................................... 246 Obtener objetos del servidor PostgreSQL. ...................................................................... 246 Lección XXXIII. Consultas útiles de monitoreo. ............................................................................... 253

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 12 de 253

Introducción Este Manual tiene como objetivo ser una Guía de Referencia durante el transcurso del curso SQLPostgreSQL.

En él podrán encontrar definiciones, notas, ejemplos practicos de casos de estudio que les servirán en sus labores cotidianas con ésta poderosa herramienta llamada PostgreSQL.

En curso no contempla administración de PostgreSQL, ni tampoco Tunning de PostgreSQL ni implementaciones de Alta Disponibilidad, pues ellas son materia de un curso de Administración Avanzada de PostgreSQL.

Espero que este este Manual y en curso en sí les sea de mucha utilidad y les resulte entretenido.

Mario Soto Cordones

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 13 de 253

Lección I - Bases de datos relacionales: “El modelo relacional” ¿Qué son las Bases de Datos (BD)? Una BD es un conjunto de datos interrelacionados, almacenados sin redundancias innecesarias, de forma independiente de los programas que acceden a ellos.

El Sistema Gestor de Base de Datos (SGBD) Un SGBD es un conjunto de programas que permiten crear y mantener una BD, asegurando su integridad, confidencialidad y seguridad. Por tanto debe permitir: 

Definir una base de datos: especificar tipos, estructuras y restricciones de datos.



Construir la base de datos: guardar los datos en algún medio controlado



por el mismo SGBD.



Manipular la base de datos: realizar consultas, actualizarla, generar informes.

Algunas de las características deseables en un SGBD son: 

Control de la redundancia: la redundancia de datos tiene varios efectos



negativos (duplica el trabajo al actualizar, desperdicia espacio en disco,



puede provocar inconsistencia de datos) aunque a veces es deseable por cuestiones de rendimiento.



Restricción de los accesos no autorizados: cada usuario ha de tener unos permisos de acceso y autorización para realizar operaciones sobre la BD.



Cumplimiento de las restricciones de integridad: el SGBD ha de ofrecer recursos para definir y garantizar el cumplimiento de las restricciones de integridad.

El modelo Entidad-Relación (E-R) Cuando se utiliza una BD para gestionar información, se está plasmando una parte del mundo real en una serie de tablas, registros y campos; creándose un modelo parcial de la realidad. Antes de crear físicamente estas tablas en la BD se debe realizar un modelo de datos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 14 de 253

El modelo de datos más utilizado es denominado Entidad-Relación (E-R). En el modelo E-R se representa una situación real a través de entidades y relaciones entre dichas entidades:

Entidades Los objetos que aparecen en la vida real, corresponden a una entidad. Por ejemplo: alumnos, empleados, aviones, coches, alojamientos, etc. Una entidad da lugar a una tabla en la BD.

Estas entidades están compuestas por varios atributos, que vienen a ser sus propiedades.

Por

ejemplo:

la

entidad

Alumnos,

tendrá

los

atributos nombre, #pasaporte, nacionalidad, fecha de nacimiento, etc.

Los atributos también reciben el nombre de columnas en la terminología de BD. De entre los atributos habrá uno o un conjunto de ellos, que no asegura la unicidad de una fila; a este atributo o conjunto de atributos se le llama clave de la entidad, en el caso de los alumnos, sería el #pasaporte. En toda entidad siempre hay al menos una clave que en el peor de los casos estará formada por todos los atributos de la tabla. Ya que pueden haber varias claves y necesitamos elegir una, lo haremos atendiendo a estas normas: 

Que sea única.



Que se tenga pleno conocimiento de ella.- ¿Por qué en las empresas se asigna a cada cliente un número de cliente?.



Que sea pequeña, ya que será muy utilizada por el SGBD.

Cada entidad tendrá un número ilimitado de elementos. Por ejemplo: un elemento de la entidad alumnos será un alumno en sí; así el alumno Juan será un elemento, José será otro. Cada uno de esos elementos también recibe el nombre de fila o tuplas en la terminología de BD. Combinando estos tres conceptos tenemos una estructura del tipo tabla, elemento esencial en una BD relacional.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 15 de 253

Nota En los textos se utilizan ciertos sinónimos:



Para referirse a una fila se puede usar el término tupla o registro.



Para referirse a una columna se puede usar el término campo o atributo.

Relaciones Las entidades no están aisladas sino que están relacionadas entre sí. Estas relaciones reflejan las interacciones lógicas existentes entre entidades. Las relaciones pueden ser de tres tipos:

Relaciones 1 - 1: Las entidades que intervienen en la relación se asocian una a una (Ej: la entidad HOMBRE, la entidad MUJER y entre ellos la relación MATRIMONIO).

Relaciones 1 - N: Una ocurrencia de una entidad está asociada con muchas (n) de otra (Ej: la entidad EMPRESA, la entidad TRABAJADOR y entre ellos la relación TRABAJAR-EN).

Relaciones M - N: Cada ocurrencia, en cualquiera de las dos entidades de la relación, puede estar asociada con muchas (n) de la otra y viceversa (Ej: la entidad ALUMNO, la entidad ASIGNATURA y entre ellos la relación MATRÍCULA).

Base de Datos Relacionales Es una BD que utiliza como estructura de almacenamiento tablas. Las interconexiones (relaciones) entre los datos (que están guardados en tablas), se generan a través de atributos comunes entre ellas llamadas claves primarias y foráneas.

Ventajas 

Sistemas de bases de datos utilizados por las empresas comerciales más importantes.



Modelo simple.



Consultas a través de lenguajes de alto nivel.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 16 de 253



Implementación eficiente.

Características 

Se compone de varias tablas o relaciones.



No existen dos o más tablas con el mismo nombre.



Una tabla es un conjunto de registros (filas y columnas).



La relación entre una tabla padre y un hijo se lleva a cabo por medio de claves primarias y foráneas.



Las claves primarias representan la clave principal de un registro dentro de una tabla y éstas deben cumplir con la integridad de los datos.



Las claves foráneas se colocan en la tabla hija, contienen el mismo valor que la clave primaria del registro padre; por medio de éstas se implementan las relaciones.

Ejemplo Se tiene una base de datos que contiene dos tablas: una denominada

Empleados, que almacena datos de los empleados de una empresa, y otra con el nombre Despachos, que almacena los datos de los despachos que tiene la empresa. Los empleados que trabajan para una empresa pueden estar vinculados con los despachos de la empresa, porque a cada empleado se le asigna un despacho concreto para trabajar.

Tabla Despachos edificioPrincessPrincessPrincessGreyDiagonalnumero120121122230110super ficie1012152010 La

tabla Despachos posee

numero−−−−−−−−−−−−,superficie) y

3

atributos (edificio,

5 registros (o filas). Esta tabla posee un

conjunto de atributos cuyos valores combinados dan la unicidad a cada fila. Se trata de los atributos edificio y numero; se les llama clave primaria compuesta.

Tabla Empleados DNI40.444.25533.567.71155.898.42577.232.144nombreAlexGeorgeDerekAri zonaapellidoKarevO'MalleyShepherdRobbinsDNIjefe40.783.15040.444.2554 0.444.25540.444.255edificiodespPrincessNULLDiagonalGreynumerodesp120 NULL110230

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 17 de 253

Empleados posee 6 atributos (DNI−−−−,nombre, apellido, DNIjefe, edificiodesp, numerodesp) y 4 registros (o filas), en el segundo registro se La

tabla

aprecia que George no posee despacho asignado por lo que se agrega el valor “unknown” o “undefined” que se define como NULL. Esta tabla posee un atributo cuyo valor es único en cada tupla que es atributo DNI y se le llama clave primaria. En Empleados,

existe

una

clave

foránea

formada

por

atributos edificiodesp y numerodesp que referencia la clave primaria de

los

Despachos.

De este modo, se cumple que todos los valores que no son nulos de los atributos edificiodesp y numerodesp son

valores

que

existen

para

los

atributos edificio y numero de Despachos. Esta clave foránea indica, para cada empleado, el despacho donde trabaja. Además, el atributo DNIjefe es otra clave foránea que referencia la clave primaria de la misma tabla

Empleados,

e indica, para cada

empleado, quien es su jefe.

Ejemplo en SQL La creación de relaciones (tablas) en SQL CREATE TABLE Despachos(edificio VARCHAR(50), numero INTEGER, superficie INTEGER, PRIMARY KEY(edificio,numero)); CREATE TABLE Empleados(DNI VARCHAR(50), nombre VARCHAR(50), apellido VARCHAR(50), DNIjefe VARCHAR(50), edificiodesp VARCHAR(50), numerodesp INTEGER, PRIMARY KEY(DNI), FOREIGN KEY(edificiodesp,numerodesp) REFERENCES Despachos(edificio,numero));

Motores de Bases de Datos Relacionales Hoy en día existen muchas empresas y sitios web que necesitan mantener de forma eficiente un gran volumen de datos. Muchos de ellos optan por soluciones comerciales (Oracle Database o IBM DB2 entre otras ), aunque muchas otras confían en el software libre optando por una solución como PostgreSQL o MySQL. Cabe mencionar que un motor de BD relacional (BDR) es equivalente a un SGBDR. Es muy común la pregunta, entre las personas que se adentran por primera vez en el mundo de las bases de datos libres, ¿Qué motor de bases de datos debo usar? ¿MySQL o PostGreSQL?. A continuación se verán algunos detalles de ambos motores.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 18 de 253

PostgreSQL PostGreSQL es un sistema de gestión de bases de datos objeto-relacional basado en el proyecto POSTGRES, de la Universidad de Berkeley. El director de este proyecto es el profesor Michael Stonebraker, patrocinado por Defense Advanced Research Projects Agency (DARPA), Army Research Office (ARO), National Science Foundation (NSF), y ESL, Inc.

Un poco de historia PostGreSQL fue derivado del proyecto Postgres, lleva más de una década de desarrollo, siendo hoy en día, el sistema libre más avanzado, soportando la gran mayoría de las transacciones SQL, control concurrente y un variado conjunto de “language bindings” como por ejemplo C, C++, Java, Python, PHP y muchos más. La implementación de Postgres DBMS comenzó en 1986, y no hubo una versión operativa hasta 1987. La versión 1.0 fue liberada en Junio de 1989 a unos pocos usuarios, tras la cual se liberó la versión 2.0 en Junio de 1990 debido a fuertes críticas sobre el sistema de integridad referencial, que obligó a su reimplementación. La versión 3.0 apareció en el año 1991. En 1994, Andrew Yu y Jolly Chen añadieron un intérprete de SQL a este gestor. Postgres95, como así se llamó fue liberado a Internet como un proyecto libre (OpenSource). Estaba escrito totalmente en C, y la primera versión fue un 25% más pequeña que Postgres, y entre un 30 y un 50% más rápida. A parte de la corrección de algunos bugs, se mejoró el motor interno, se añadió un nuevo programa monitor, y se compiló usando GNU Make y el compilador gcc. En 1996, los desarrolladores decidieron cambiar el nombre al SGDB, y lo llamaron PostGreSQL para reflejar la relación entre Postgres y las versiones recientes de SQL.

Características 

Implementación del estándar SQL92/SQL99.



Licencia BSD.



Por su arquitectura de diseño, escala muy bien al aumentar el número de CPUs y la cantidad de RAM.



Soporta transacciones y desde la versión 7.0, claves foráneas (con comprobaciones de integridad referencial).



Tiene mejor soporte para triggers y procedimientos en el servidor.



Incorpora una estructura de datos array.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 19 de 253



Incluye herencia entre tablas (aunque no entre objetos, ya que no existen), por lo que a este SGBD se le incluye entre los gestores objetorelacionales.



Implementa el uso de rollback’s, subconsultas y transacciones, haciendo su funcionamiento mucho más eficaz.



Se pueden realizar varias operaciones al mismo tiempo sobre la misma tabla sin necesidad de bloquearla.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 20 de 253

Lección II - Bases de datos relacionales: Consulta de bases de datos relacionales Utilizando una Base de Datos Relacional Los pasos necesarios a la hora de crear una Base de Datos Relacional (BDR) son: 

Diseñar el esquema, es decir, la estructura de las tablas y las relaciones, usando un DDL (Data Definition Language).



Ingresar los datos iniciales.



Ejecutar operaciones de consulta y mantención usando en DML (Data Manipulation Language).

Nota Existen las llamadas “Operaciones Básicas” de DML que se pueden realizar en una Base de Datos Relacional: 1. Consultar: SELECT 2. Almacenar: INSERT 3. Actualizar: UPDATE 4. Borrar: DELETE Nota Existen las llamadas “Operaciones Básicas” de DDL que se pueden realizar en una Base de Datos Relacional: 1. Almacenar: CREATE 2. Borrar: DROP

Por ahora sólo se nombran junto a sus funciones SQL relacionadas. A medida que el curso avance, se profundizará el contenido.

Consultas en lenguajes de alto nivel Existen lenguajes de alto nivel que permiten realizar consultas relativamente simples en la BD, sin la necesidad de escribir complejos algoritmos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 21 de 253

Una ‘consulta a la BD’, puede entenderse como una pregunta que se le realiza para obtener ‘cierta información’. Algunos ejemplos pueden ser:   

“Todos los estudiantes con nota mayor o igual a 55”. “Todas los departamentos de Ingeniería con una cantidad mayor o igual a 1000 alumnos”. “Los 5 primeros estudiantes con mejor promedio de notas en el ramo de Química”.

Independiente del lenguaje que se utiliza, se debe tener en cuenta que: 

Algunas consultas son fáciles de formular, otras son un poco más difíciles.



Algunos SGBD las ejecutan de forma eficiente, otros no.



Los 2 puntos anteriores no son dependientes uno del otro, puede existir una consulta fácil de formular, pero difícil de ejecutar de forma eficiente, dependiendo del DBMS.



El lenguaje utilizado para ejecutar consultas puede modificar/actualizar información de la BD, a esto se le llama Data Manipulation Language (DML).

Consultas y relaciones (tablas) Las consultas realizadas a las tablas de una BD al momento de ser ejecutadas producen, como resultado, tablas; las cuales pueden ser: 

Cerradas: Cuando la estructura del objeto que se obtiene de la consulta, es igual a la estructura de los objetos consultados, se tiene una tabla cerrada.



Compuestas: Cuando la consulta se hace sobre, al menos una tabla que corresponde al resultado de una consulta previa. En otras palabras, corresponde a la consulta del resultado de una consulta.

Lenguajes de consultas Algunos de los lenguajes de consultas son 

Álgebra Relacional: Lenguaje formal y matemático



SQL: Lenguaje actual e implementado que nace del Álgebra Relacional.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 22 de 253

Si bien se profundizará sobre ambos, a medida que avance el curso, se deja la siguientes tablas:

Tabla Alumnos id12345idRamo12113nombreAlumnoRobertRobertHarryJaneMaryprome dioNota4570556035 Tabla Ramos id123nombreRamoProgramacionBase de datosEstructuras de datos Consultar por el id de los alumnos con promedio de notas mayor o igual a 55 en el ramo de “Programación”:

Utilizando Álgebra Relacional πAlumnos.idσ≥ 55 AND Ramos.nombreRamo ='Programacion'(Alumnos ⊳ ⊲Ramos) Se puede decir que: 

π, realiza un PROJECT sobre una tabla, es decir selecciona una columna. Por otro lado:



σ,

selecciona una fila que cumpla con una cierta condición, en el ejemplo

dado se seleccionan las filas que cumplen con tener nota mayor a 55 y que el nombreRamo sea programación. 

⊳⊲,

realiza un JOIN entre dos relaciones en la Lección 3 se profundiza

acerca de estos operadores y sus respectivos significados.

Utilizando SQL SELECT ALUMNOS.ID FROM ALUMNOS, Ramos WHERE ALUMNOS.ID_ramo=Ramos.ID AND ALUMNOS.promedio_nota>=55 AND Ramos.nombreRamo='Programacion';

En las próximas Lecciónes, se estudiará con mayor detalle tanto el álgebra relacional, como el lenguaje SQL.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 23 de 253

Lección III - Álgebra Relacional: Select, Project, Join Conceptos básicos de álgebra relacional Algebra, en general, consiste de operadores y operandos atómicos, por ejemplo, en el álgebra de la aritmética, los operandos atómicos son variable como r, y constantes como 15. Los operadores son los usuales en la aritmética: 

Suma,



Resta,



Multiplicación,



División.

Cualquier álgebra nos permite construir expresiones mediante la aplicación de operadores a operandos atómicos y/o otras expresiones del álgebra. En general, los paréntesis son necesarios para agrupar operadores y sus operandos, por ejemplo, en aritmética tenemos expresiones tales como

(x+y)∗z ó ((x+7)/(y−3))+x.

El Álgebra Relacional es otro ejemplo del álgebra. Sus operandos atómicos son: 1. Variables que representan relaciones. 2. Constantes que son relaciones finitas. Nota En algunos textos de álgebra relacional o SQL, una relación es sinónimo de una tabla.

Como mencionamos, en el álgebra relacional clásica, todos los operandos y sus resultados de expresiones son conjuntos. Los operadores del álgebra relacional tradicional se divide en cuatro grandes categorías: a. Los

conjuntos

habituales

de

operaciones unión, intersección,

y diferencia se aplica a las relaciones. b. Las operaciones que eliminan parte de una relación: selección elimina algunas filas (o tuplas), y proyección elimina algunas columnas. c. Las operaciones que combinan las tuplas de dos relaciones, como el producto cartesiano, que empareja las tuplas de dos relaciones en todas las maneras posibles y varios tipos de operadores unión, los cuales forman parejas de tuplas de dos relaciones selectivamente. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 24 de 253

d. Una operación llama “renombrar” que no afecta las tuplas de una relación, pero que cambia el esquema de relación, es decir, lo nombres de los atributos y/o los nombres de la relación misma. Debemos por lo general referirnos a las expresiones del álgebra relacional como consultas. A pesar de que aún no tengan los símbolos necesarios para mostrar muchas de las expresiones del algebra relacional, se debería familiarizar con las operaciones degrupo (a); y por lo tanto reconocer:

R∪S como

un ejemplo de una expresión de

álgebra relacional. Donde R y S son operandos atómicos para relaciones, cuyos conjuntos de tuplas son desconocidas. Esta consulta pregunta por la unión de cualquiera tuplas que están en las relaciones nombradas R y S. Las

tres

operaciones

más

comunes

en

conjuntos

son unión, intersección,

y diferencia, que serán vistas en la Lección 4. Nota Resumiendo Álgebra Relacional se define como un conjunto de operaciones que se ejecutan sobre las relaciones (tablas) para obtener un resultado, el cual es otra relación.

Operaciones relacionales: Los operadores relacionales se utilizan para filtrar, cortar o combinar tablas.

Selección (Select) Este operador se aplica a una relación

R produciendo

una nueva relación con un

subconjunto de tuplas de R. Las tuplas de la relación resultante son las que satisfacen una condición C sobre algún atributo de R. Es decir selecciona filas (tuplas) de una tabla según un cierto criterio C. El criterio C es una expresión condicional, similar a las declaraciones del tipo “if”, es “booleana” esto quiere decir que para cada tupla de R toma el valor Verdad(true) o Falso(false). 

Valores de atributos con “NULL” no cumplirán ninguna condición.



Cada

condición

simple

o

cláusula

C

tiene

el

formato: }. donde,

el

campo Comparador es

un operador

lógico,

que

pueden

ser {=,≥,>,: mayor que. 4 (once mayor que cuatro) es verdadera, se representa por el valor true del tipo básico boolean, en cambio, 1130Ingenieros

En la imagen se ve que selecciona solo las filas que cumplen con la condición que se pedía (tener una edad mayor a 30 años), la tupla de “Josefa” queda fuera de la selección por no cumplir la condición (pues 25 < 30). De esta forma la tabla queda:

Tabla Ingenieros id123234345nombreLeonTomasJoseedad393445añosTrabajados151021 Ejemplo 2 Seleccionar de la tabla

Ingenieros las personas que tienen más de 30 años y que lleven

menos de 16 años trabajando: Respuesta

σ(edad>30∧añosTrabajados30Ingenieros)

Se aprecia que las tuplas que no cumplan con la condición de selección quedan fuera del resultado, luego se realiza un Project sobre las filas del resultado, separando solo las columnas que contienen los atributos id y nombre. Finalmente la tabla queda de la siguiente manera:

Tabla Ingenieros id123234345nombreLeonTomasJose

Producto cartesiano (Cross-Product) En teoría de conjuntos, el producto cartesiano de dos conjuntos es una operación que resulta en otro conjunto cuyos elementos son todos los pares ordenados que pueden formarse tomando el primer elemento del par del primer conjunto, y el segundo elemento del segundo conjunto. En el Álgebra Relacional se mantiene esta idea con la diferencia que R y S son relaciones, entonces los miembros de

R y S son

tuplas, que

generalmente consisten de más de un componente, cuyo resultado de la vinculación de

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 29 de 253

una tupla de R con una tupla de S es una tupla más larga, con un componente para cada uno de los componentes de las tuplas constituyentes. Es decirCrossproduct define una relación que es la concatenación de cada una de las filas de la

relación R con cada una de las filas de la relación

S.

Notación en Álgebra Relacional Para

representar Cross-product en

Álgebra

Relacional

se

utiliza

la

siguiente

terminología:

R×S Por convención para la sentencia anterior, los componentes de

R preceden

a los

componentes de S en el orden de atributos para el resultado, creando así una nueva relación con todas las combinaciones posibles de tuplas de

R y S. El número de tuplas de la nueva relación resultante es la multiplicación de la cantidad de tuplas de R por la cantidad de tuplas que tenga S (producto de ambos). Si Ry S tienen algunos atributos en común, entonces se debe inventar nuevos nombres para al menos uno de cada par de atributos idénticos. Para eliminar la ambigüedad de un atributo a, que se encuentra en R y S, se usa R.a para el atributo de R y S.a para el atributo de S. Cabe mencionar que por notación que: R×S≠S×R

Ejemplo 1

Con las tablas dadas realice el Cross-product de R con S:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 30 de 253

Con azul se resaltan las tuplas que provienen de

R que preseden y se mezclan con las

de S resaltadas en verde. Con las tablas dadas realice el Cross-product de S con R:

Ejemplo 2 Dada las siguientes tablas:

Tabla Ingenieros id123234143nombreLeonTomasJosefad#393425 Tabla Proyectos proyectoACU0034USM7345duracion30060 Escriba la tabla resultante al realizar la siguiente operación:

Ingenieros×Proyectos Respuesta

Ingenieros×Proyectos

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 31 de 253

id123123234234143143nombreLeonLeonTomasTomasJosefaJosefad#393934 342525proyectoACU0034USM7345ACU0034USM7345ACU0034USM7345 duracion300603006030060

Natural Join Este operador se utiliza cuando se tiene la necesidad de unir relaciones vinculando sólo las tuplas que coinciden de alguna manera.NaturalJoin une sólo los pares de tuplas de R y S que sean comunes. Más precisamente una tupla r de

R y una tupla s de S se

emparejan correctamente si y sólo si r y s coinciden en cada uno de los valores de los atributos comunes, el resultado de la vinculación es una tupla, llamada joined tuple. Entonces, al realizar NaturalJoin se obtiene una relación con los atributos de ambas relaciones y se obtiene combinando las tuplas de ambas relaciones que tengan el mismo valor en los atributos comunes.

Notación en Álgebra Relacional Para denotar NaturalJoin se utiliza la siguiente simbología: R⊳⊲S.

Equivalencia con operadores básicos NaturalJoin puede ser escrito en términos de algunos operadores ya vistos, la

equivalencia es la siguiente:

R⊳⊲S=πR.A1,...,R.An,S.A1,...,S.An(σR.A1=S.A1∧...∧R.An=S.An(R×S)) Método 1. Se realiza el producto cartesiano R×S . 2. Se seleccionan aquellas filas del producto cartesiano para las que los atributos comunes tengan el mismo valor. 3. Se elimina del resultado una ocurrencia (columna) de cada uno de los atributos comunes.

Ejemplo 1

R a14b25c36

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 32 de 253

S c763d524 Con las tablas dadas realice el NaturalJoin de R y S:

El atributo que tienen en común R y S es el atributo c, entonces las tuplas se unen donde c tiene el mismo valor en R y S

R⊳⊲S a14b25c36d42 Ejemplo 2 Realizar NaturalJoin a las siguientes tablas:

Tabla Ingenieros id123234143090nombreLeonTomasJosefaMariad#39342534 Tabla Proyectos d#3934proyectoACU0034USM7345 Respuesta

Ingenieros⊳⊲Proyectos id123234090nombreLeonTomasMariad#393434proyectoACU0034USM7345 USM7345

ThetaJoin Define una relación que contiene las tuplas que satisfacen el predicado Product de

R×S.

tienen

una

C en el Cross-

Conecta relaciones cuando los valores de determinadas columnas interrelación

específica.

La

condición

C es

de

la

forma R.ai S.bi, esta condición es del mismo tipo que se utiliza Select . El predicado no tiene por que definirse sobre atributos comunes. El término “join” suele referirse a ThetaJoin.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 33 de 253

Notación en Álgebra Relacional La notación de ThetaJoin es el mismo símbolo que se utiliza para NaturalJoin, la diferencia radica en que ThetaJoin lleva el predicado C:

R⊳⊲CS C = Donde:

∈{=,≥,>,=e)S Respuesta

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 34 de 253

Se compara el atributo a de la primera fila de atributo e de la tabla

S.

R con

cada uno de los valores del

En este caso ninguna de las comparaciones devuelve el valor

verdadero (true).

Luego se compara el atributo a de la segunda fila de atributo e de la tabla

S.

R con cada uno de los valores del

En este caso 2 comparaciones devuelven el valor verdadero

(true), por lo que en la relación de resultado quedará la segunda fila de

R mezclada

con la primera y tercera fila de S.

De igual forma ahora se compara el valor de a de la tercera tupla de R, nuevamente 2 tuplas de S cumplen con la condición.

S R.a3322b2233R.c9955d1144S.a1313S.c5959e2222 Ejemplo 2 Con el esquema conceptual siguiente, hallar los nombres de los directores de cada departamento:

Dpto(numDpto−−−−−−−−, nombre, nIFDirector, fechaInicio) Empleado(nIF−−−, nombre, direccion, salario, dpto, nIFSupervisor) Respuesta

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 35 de 253

π(Dpto.nombre,empleado.nombre)(Dpto⊳⊲nIFDirector=NIFEmpleado) 

Tuplas con “Null” en los “Atributos de la Reunión”, no se incluyen en el resultado.

Ejercicio propuesto Considere la siguiente base de datos: 1. 2.

Persona(nombre−−−−−−, edad, genero) : nombre es la clave. Frecuenta(nombre, pizzeria−−−−−−−−−−−−−) : (nombre, pizzeria) es la clave.

3. 4.

Come(nombre, pizza−−−−−−−−−−−) : (name, pizza) es la clave. Sirve(pizzeria, pizza−−−−−−−−−−−, precio): (pizzeria, pizza) es

la

clave. Escribir expresiones en álgebra relacional para las siguientes dos preguntas: 

Seleccionar a las personas que comen pizzas con extra queso.



Seleccionar a las personas que comen pizzas con extra queso y frecuentan la pizzería X.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 36 de 253

Lección IV - Álgebra Relacional: Set operators, renaming, notation Operaciones de conjunto Unión En matemáticas, se denomina álgebra de conjuntos a las operaciones básicas que pueden realizarse con conjuntos, como la unión, intersección, etc. Un conjunto es una colección

de

objetos

considerada

como

un

objeto

en

sí.

La Unión de

conjuntos A y B es el conjunto que contiene todos los elementos de

Ay

dos

de B. El

símbolo ∪ es el utilizado para representar Unión. El operador Unión es conmutativo es decir A∪B=B∪A. Cabe recordar que una operación es conmutativa cuando el resultado de la operación es el mismo, cualquiera que sea el orden de los elementos con los que se opera.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 37 de 253

De manera análoga, la Unión de dos relaciones R y S, es otra relación que contiene las tuplas

que

están

en

R,

o

en

S,

o

en

ambas,

eliminándose

las

tuplas

duplicadas. R y S deben ser unión-compatible, es decir, definidas sobre el mismo conjunto de atributo (R yS deben tener esquemas idénticos. Deben poseer las mismas columnas y su orden debe ser el mismo).

Notación en álgebra relacional

R∪S Si se realiza

R∪S es lo mismo que S∪R , es decir se obtiene el mismo resultado. Esto

debido a la propiedad de conmutatividad derivada de la álgebra de conjuntos.

Ejemplo Dadas las siguientes relaciones:

Tabla Ingenieros id123234345143nombreLeonTomasJoseJosefaedad39344525 Tabla Jefes id123235nombreLeonMariaedad3929 Aplicar el operador Unión:

Tabla Ingenieros∪Jefes id123234345143235nombreLeonTomasJoseJosefaMariaedad3934452529 Como se mencionó anteriormente, realizar la operación

Jefes∪Ingenieros daría como

resultado la misma tabla anterior, debido a la propiedad de conmutatividad.

Diferencia Volviendo

a

la

analogía

de

álgebra

de

conjuntos,

la

diferencia

conjuntos A y B es el conjunto que contiene todos los elementos de pertenecen a

entre

A que

dos no

B. A−B

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 38 de 253

B−A

Como se aprecia en las imágenes la operación Diferencia, en conjuntos, no es conmutativa, al igual que la resta o sustracción, operador aprendido en aritmética básica. Es decir, si se cambia el orden de los conjuntos a los que se aplica la operación Diferencia, se obtendrán resultados distintos. Por lo tanto:

A−B≠B−A De la misma forma la diferencia de dos relaciones las tuplas que están en la relación

R,

pero no

R y S, es otra relación que contiene están en S. R y S deben ser unión-

compatible (deben tener esquemas idénticos).

Notación en álgebra relacional

R−S Es importante resaltar que R−S es diferente a S−R.

Ejemplo Empleando

las

mismas

tablas

dadas

en

el

ejemplo

anterior,

realice Ingenieros−Jefes y Jefes−Ingenieros:

Ingenieros - Jefes id234345143nombreTomasJoseJosefaedad344525 Jefes - Ingenieros Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 39 de 253

id235nombreMariaedad29 Como se puede apreciar, ambas operaciones dieron como resultado distintas relaciones, tal como se había mencionado anteriormente.

Intersección En álgebra de conjuntos la Intersección de dos conjuntos A y B es el conjunto que contiene

todos

los

elementos

comunes

de

A y B.

El

símbolo

∩ representa

la Intersección de dos conjuntos. Al igual que el operador Unión, Intersección es conmutativo, entonces se cumple que A∩B=B∩A .

A∩B

De forma homóloga en álgebra relacional, Intersección define una relación que contiene las tuplas que están tanto en la relación

Rcomo en S. R y S deben ser unión-

compatible (mismos atributos y mismo orden).

Notación en algebra relacional

R∩S Si se realiza

R∩S es

lo mismo que

S∩R,

es decir se obtiene el mismo resultado, se

puede decir que Intersección es conmutativa.

Equivalencia con operadores anteriores

R∩S=R−(R−S) Ejemplo Utilizando las mismas tablas del ejemplo anterior, encontrar la Intersección de la tabla de Ingenieros con la de Jefes:

Ingenieros∩Jefes

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 40 de 253

id123nombreLeonedad39 Importante Para aplicar estas operaciones a relaciones, necesitamos que

R y S sean unión-compatible:



R y S deben tener esquemas con conjuntos de atributos idénticos, y de tipos (dominios) para cada atributo deben ser las mismas en R y S.



Antes de computar el conjunto-teórico unión, intersección, o diferencia de conjuntos de tuplas, las columnas de R y S deben ser ordenadas para que el orden de los atributos sean los mismos para ambas relaciones.

Operaciones dependientes e independientes Algunas de las operaciones que se han descrito en las Leccións 3 y 4, pueden ser expresadas en términos de operadores de algebra relacional. Por ejemplo, la intersección

puede

ser

expresada

en

términos

de

conjuntos

de

diferencia: R∩S=R−(R−S). Es decir, si R y S son dos relaciones con el mismo esquema, la intersección de R y S puede ser resuelta restando primero

S de R para formar una relación T que consiste en todas aquellas tuplas en R pero no en S. Cuando se resta T de R, dejamos solo esas tuplas de R que están también en S.

Álgebra relacional como idioma restrictor Hay dos maneras en las cuales podemos usar expresiones de algebra relacional para expresar restricción: 1. Si R es una expresión de algebra relacional, entonces

R=0 es

una restricción que

dice “El valor de R debe ser vacio,” o equivalentemente “No hay tuplas en el resultado de R.” 2. Si R y S son expresiones de algebra relacional, entonces R⊂S es una restricción que dice “Cada tupla en resultado deR debe estar también en resultado de

S.” supuesto, el resultado de S puede contener tuplas adicionales no producidas en R.

Por

Estas formas para expresar restricción son de hecho equivalentes en lo que pueden expresar, pero algunas veces uno de los dos es más clara o más sucinta. Es decir, la restricción R⊂S pudo también ser escrito R−S=0. Para ver por qué, observe que si cada tupla en

R está también en S, entonces seguramente R−S es vacío. A la inversa,

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 41 de 253

si R−S no contiene tuplas, entonces cada tupla en

R debe

estar en S (o de lo que

sería R−S). Por otra parte, una restricción de la primera forma,

R=0,

también pudo haber sido

escrita como R⊂0. Técnicamente, que hay expresiones que

0 no es una expresión de algebra relacional, pero ya evalúan a 0, tal como R−R, no hay nada malo en

usar 0 como una expresión de algebra relacional. Tenga en cuenta que estas equivalencias sostienen se sostienen incluso si R y S son bolsas, dado que hacemos la interpretación convencional de R⊂S: cada tupla t aparece en

S al

menos tantas veces

como aparece en R.

Ejercicios Propuestos Ejercicio 1 Las relaciones base que forman la base de datos de un video club son las siguientes: 

Socio(codsocio−−−−−−−,nombre,direccion,telefono):

almacena los datos

de cada uno de los socios del video club: código del socio, nombre, dirección y teléfono. 

Pelicula(codpeli−−−−−−,titulo,genero):

almacena información sobre cada

una de las películas de las cuales tiene copias el vídeo club: código de la película, título y género (terror, comedia, etc.). 

Cinta(codcinta−−−−−−−,codpeli):

almacena información referente a las

copias que hay de cada película (copias distintas de una misma película tendrán distinto código de cinta). 

Prestamo(codsocio,codcinta,fecha−−−−−−−−−−−−−−−−−−,presdev): almacena información de los préstamos que se han realizado. Cada préstamo es de una cinta a un socio en una fecha. Si el préstamo aún no ha finalizado, presdev tiene el valor ‘prestada’; si no su valor es ‘devuelta’.



ListaEspera(codsocio,codpeli−−−−−−−−−−−−,fecha):

almacena

información sobre los socios que esperan a que haya copias disponibles de películas, para tomarlas prestadas. Se guarda también la fecha en que comenzó la espera para mantener el orden. Es importante tener en cuenta que cuando el socio consigue la película esperada, éste desaparece de la lista de espera.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 42 de 253

En las relaciones anteriores, son claves primarias los atributos y grupos de atributos que aparecen en negrita. Las claves ajenas se muestran en los siguientes diagramas referenciales: Resolver las siguientes consultas mediante el álgebra relacional (recuerde que en la Lección 3 también se dieron algunos operadores de álgebra relacional): 1.1. Seleccionar todos los socios que se llaman: “Charles”. Respuesta

σnombre='Charles'(Socio) 1.2. Seleccionar el código socio de todos los socios que se llaman: “Charles”.

Respuesta

πcodsocio(σnombre='Charles'(Socio)) 1.3. Seleccionar los nombres de las películas que se encuentran en lista de espera. Respuesta

πtitulo(Pelicula⊳⊲ListaEspera) 1.4. Obtener los nombres de los socios que esperan películas. Respuesta

πnombre(Socio⊳⊲ListaEspera) 1.5. Obtener los nombres de los socios que tienen actualmente prestada una película que ya tuvieron prestada con anterioridad. Respuesta

πnombre({(Prestamo⊳⊲(presdev='prestada')Cinta)∩(Prestamo⊳⊲(presdev='devuelta')Cinta )}⊳⊲Socio) 1.6. Obtener los títulos de las películas que nunca han sido prestadas. Respuesta

πtitulo{(πcodpeliPelicula−πcodpeli(Prestamo⊳⊲Cinta))⊳⊲Pelicula} (todas las películas) menos (las películas que han sido prestadas alguna vez)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 43 de 253

1.7. Obtener los nombres de los socios que han tomado prestada la película “WALL*E” alguna vez o que están esperando para tomarla prestada. Respuesta

πnombre(σtitulo='WALL*E'(Socio⊳⊲Prestamo⊳⊲Cinta⊳⊲Pelicula)∪σtitulo='WALL*E'( Socio⊳⊲ListaEspera⊳⊲Pelicula)) 1.8. Obtener los nombres de los socios que han tomado prestada la película “WALL*E” alguna vez y que además están en su lista de espera. Respuesta

πnombre(σtitulo='WALL*E'(Socio⊳⊲Prestamo⊳⊲Cinta⊳⊲Pelicula)∩σtitulo='WALL*E' (Socio⊳⊲ListaEspera⊳⊲Pelicula))

Ejercicio 2 Considere la base de datos con el siguiente esquema: 1. 2.

Persona(nombre−−−−−−, edad, genero) : nombre es la clave. Frecuenta(nombre, pizzeria−−−−−−−−−−−−−) : (nombre, pizzeria) es la clave.

3. 4.

Come(nombre, pizza−−−−−−−−−−−) : (name, pizza) es la clave. Sirve(pizzeria, pizza−−−−−−−−−−−, precio): (pizzeria, pizza) es

la

clave. Escribir las expresiones de álgebra relacional para las siguientes nueve consultas. (Precaución: algunas de las siguientes consultas son un poco desafiantes). 

Encuentre todas las pizzerías frecuentadas por al menos una persona menor de 18 años.



Encuentre los nombres de todas las mujeres que comen pizza ya sea con champiñones o salchichón (o ambas).



Encuentre los nombres de todas las mujeres que comen pizzas con los dos ingredientes, champiñones y salchichón.



Encuentre todas las pizzerías que sirven al menos una pizza que Amy come por menos de 10 dólares.



Encuentre todas las pizzerías que son frecuentadas por solo mujeres o solo hombres.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 44 de 253



Para cada persona, encuentre todas las pizzas que la persona come, que no son servidas por ninguna pizzería que la persona frecuenta. Devuelve toda dicha persona (nombre)/ pizza pares.



Encuentre los nombres de todas las personas que frecuentan solo pizzerías que sirven al menos una pizza que ellos comen.



Encuentre la pizzería que sirve la pizza más barata de salchichón. En el caso de empate, vuelve todas las pizzerías que venden las pizzas de salchichón más baratas.

Lección V – Introducción al Lenguaje SQL SQL (Lenguaje de consulta estructurado) es un tipo de lenguaje vinculado con la gestión de bases de datos de carácter relacional que permite la especificación de distintas clases de operaciones. Gracias a la utilización del álgebra y de cálculo relacional, el lenguaje SQL brinda la posibilidad de realizar consultas que ayuden a recuperar información de las bases de datos de manera sencilla.

Características Algunas de las características de este lenguaje son: 

Compatible con todos los sistemas de bases de datos comerciales importantes.



Estandarizada - nuevas características en el tiempo.



Interactiva a través de interfaz gráfica de usuario o del sistema.



Declarativa, basada en álgebra relacional.

Lenguaje de descripción de datos (DDL) DDL (Lenguaje de descripción de datos) es un lenguaje que permite definir la base de datos (su estructura o “schemas”), tiene una sintaxis similar a los lenguajes de programación.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 45 de 253

Ejemplos de DDL: CREATE TABLE nombre_tabla; DROP TABLE nombre_tabla; ALTER TABLE nombre_tabla ADD id INTEGER;

Descripción de los comandos 

CREATE:



Para crear una nueva base de datos, índice o almacenamiento de consultas.



Un

argumento CREATE en

SQL

crea

un

objeto

dentro

del

sistema

de

administración de la base de datos relacional (RDBMS). 

El tipo de objetos que se pueden crear depende de qué RDBMS esta siendo utilizado, pero la mayoría soporta la creación de tablas, índices, usuarios y bases de datos.



Algunos sistemas (tales como PostgreSQL) permiten CREATE y otros comandos DDL, dentro de transacciones, y por lo tanto pueden ser revertidos



DROP:



Para destruir una base de datos, tabla, índice o vista existente.



Un argumento DROP en SQL remueve un objeto dentro del sistema de administración de la base de datos relacional (RDBMS).



El tipo de objetos que se pueden eliminar depende de que RDBMS esta siendo utilizado, pero la mayoría soporta la eliminación de tablas, índices, usuarios y



bases de datos. Algunos sistemas (tales como PostgreSQL) permiten DROP y otros comandos DDL, dentro de transacciones, y por lo tanto pueden ser revertidos



ALTER:



Se utiliza para modificar la estructura de la tabla, como en los casos siguientes: o

Añadir una columna

o

Eliminar una columna

o

Cambiar el nombre de una columna

o

Cambiar el tipo de datos para una columna

Lenguaje de manipulación de datos (DML)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 46 de 253

DML (Lenguaje de manipulación de datos) se refiere a los comandos que permiten a un usuario manipular los datos de las tablas, es decir, consultar tablas, añadir filas, borrar filas y actualizar columnas.

Ejemplos de DML SELECT atributo FROM nombre_tabla; INSERT INTO nombre_tabla(atributo_1,...,atributo_n) (dato_1,...,dato_n); DELETE FROM nombre_tabla WHERE condicion; UPDATE nombre_tabla SET atributo = nuevo dato WHERE condicion;

VALUES

Descripción de comandos 

SELECT



Devuelve el resultado de un conjunto de registros de una o mas tablas.



Un argumento SELECT devuelve cero o más filas de una o más tablas de una base



de datos o vistas de base de datos. En la mayoría de las aplicaciones SELECT es el comando DML mas usado.



Como

SQL

es

un

lenguaje

de

programación

declarativo,

consultas SELECT especifican el conjunto de resultado, pero no como calcularlo. 

La base de datos traduce la consulta a un “plan de consulta”, que puede variar dependiendo de la ejecución, la versión de la base de datos y el software de base de datos.



Esta funcionalidad es llamada “optimizador de consulta”, puesto que es responsable de buscar el mejor plan de ejecución para la consulta, tomando en cuenta las restricciones aplicables.

Instrucción SELECT básica SELECT A1, ..., An FROM R1, ..., Rm WHERE condición

Significado:   

A1,…,An: Atributos que retorna FROM R1,…,Rm: relaciones o tablas WHERE condición: combinar, filtrar SELECT

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 47 de 253

Lo que busca esta consulta es mostrar las columnas

A1,…,An de

las tablas o

relaciones R1,…,Rm, siguiendo alguna condición.

Álgebra relacional:

πA1,…,An(σcondición(R1×…×Rm)) Comandos SQL: 

INSERT - agrega uno o más registros a una tabla de una base de datos

relacional. 

DELETE - elimina uno o más registros de una tabla. Un subconjunto de datos

puede ser eliminado si existe una condición, de lo contrario todos los registros 

serán eliminados. UPDATE - cambia los datos de uno o más registros de una tabla. Una fila o un subconjunto de filas puede ser actualizadas utilizando una condición.

Ejemplo práctico Nota Para realizar estos ejercicios, debe utilizar Postgresql mediante la conexión ssh o también puede instalarlo en su computador. Si tiene un sistema Linux puede utilizar el gestor de paquetes del sistema operativo.

 Para usuarios que utilicen Debian / Ubuntu pueden ejecutar el siguiente comando como root: sudo apt-get libpq-dev

install

postgresql

postgresql-client

postgresql-contrib

 Para usuarios que utilicen Red Hat/Scientific Linux/Fedora/CentOS yum -y install postgresql postgresql-libs postgresql-contrib postgresqlserver postgresql-docs

Después que finalice el proceso de instalación, es necesario ingresar al entorno ** psql **

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 48 de 253

 Para usuarios que utilicen Debian / Ubuntu pueden ejecutar el siguiente comando como root sudo su postgres -c psql

 Para usuarios que utilicen Red Hat/Scientific Linux/Fedora/CentOS  Iniciar el servicio. Debería decir OK si todo esta correcto service postgresql start

 Cambiamos la contraseña del usuario Postgresql passwd postgres

 Ejecutar Postgresql (ingresar la contraseña que cambiamos anteriormente) su postgres

 Comenzamos el servicio /etc/init.d/postgresql start

 Debe aparecer el mensaje “bash-4.1 $”, ahora ingresamos a Postgresql ingresando 

psql

Primero que todo debemos crear una base de datos para comenzar nuestros ejercicios. La llamaremos example: postgres=# create database example; CREATE DATABASE

Luego de haber creado nuestra base de datos, necesitamos ingresar a ella para comenzar a realizar las distintas operaciones: postgres=# \c example psql (8.4.14) Ahora está conectado a la base de datos «example».

Ahora comenzamos a crear una tabla llamada cliente con las variables id que se define como serial en que al ir agregando datos se autoincrementará automáticamente en la base de datos example: example=# CREATE TABLE cliente (id SERIAL, nombre VARCHAR(50), apellido VARCHAR(50), edad INTEGER, direccion VARCHAR(50), pais VARCHAR(25));

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 49 de 253

Y recibiremos el siguiente mensaje: NOTICE: CREATE TABLE creará una secuencia implícita «cliente_id_seq» para la columna serial «cliente.id» CREATE TABLE

Para agregar datos a la tabla cliente se realiza de la siguiente manera: example=# INSERT INTO cliente (nombre,apellido,edad,direccion,pais) ('John', 'Smith', 35, '7635 N La Cholla Blvd', 'EEUU'); INSERT 0 1

VALUES

Agregar más datos a la tabla cliente example=# INSERT INTO cliente (nombre,apellido,edad,direccion,pais) ('John', 'Smith', 35, '7635 N La Cholla Blvd', 'EEUU'); INSERT 0 1 example=# INSERT INTO cliente (nombre,apellido,edad,direccion,pais) ('Judith', 'Ford', 20, '3901 W Ina Rd', 'Inglaterra'); INSERT 0 1 example=# INSERT INTO cliente (nombre,apellido,edad,direccion,pais) ('Sergio', 'Honores', 35, '1256 San Luis', 'Chile'); INSERT 0 1 example=# INSERT INTO cliente (nombre,apellido,edad,direccion,pais) ('Ana', 'Caprile', 25, '3456 Matta', 'Chile'); INSERT 0 1

VALUES

VALUES

VALUES

VALUES

Seleccionar todos los datos de la tabla cliente example=# SELECT * FROM cliente; id | nombre | apellido | edad | direccion | pais ---+--------+----------+------+-----------------------+-----------1 | John | Smith | 35 | 7635 N La Cholla Blvd | EEUU 2 | John | Smith | 35 | 7635 N La Cholla Blvd | EEUU 3 | Judith | Ford | 20 | 3901 W Ina Rd | Inglaterra 4 | Sergio | Honores | 35 | 1256 San Luis | Chile 5 | Ana | Caprile | 25 | 3456 Matta | Chile (5 rows) Nota El asterisco (*) que está entre el SELECT y el FROM significa que se seleccionan todas las columnas de la tabla.

Si deseamos seleccionar la columna nombre con apellido la consulta debería ser SELECT nombre, apellido FROM cliente;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 50 de 253

Como cometimos el error de agregar en la segunda fila datos repetidos, pero se puede eliminar de la siguiente manera example=# DELETE FROM cliente WHERE id=2; DELETE 1

Verificamos que se haya eliminado example=# SELECT * FROM cliente; id | nombre | apellido | edad | direccion | pais ---+--------+----------+------+-----------------------+-----------1 | John | Smith | 35 | 7635 N La Cholla Blvd | EEUU 3 | Judith | Ford | 20 | 3901 W Ina Rd | Inglaterra 4 | Sergio | Honores | 35 | 1256 San Luis | Chile 5 | Ana | Caprile | 25 | 3456 Matta | Chile (4 rows)

Si se desea actualizar la dirección del cliente Sergio de la tabla cliente example=# UPDATE cliente SET direccion='1459 Patricio Lynch' WHERE id=4; UPDATE 1

Se puede seleccionar la tabla cliente para verificar que se haya actualizado la información example=# SELECT * FROM cliente; id | nombre | apellido | edad | direccion | pais ---+--------+----------+------+-----------------------+-----------1 | John | Smith | 35 | 7635 N La Cholla Blvd | EEUU 3 | Judith | Ford | 20 | 3901 W Ina Rd | Inglaterra 5 | Ana | Caprile | 25 | 3456 Matta | Chile 4 | Sergio | Honores | 35 | 1459 Patricio Lynch | Chile (4 rows)

Para borrar la tabla cliente example=# DROP TABLE cliente; DROP TABLE

Seleccionamos la tabla cliente, para verificar que se haya eliminado example=# SELECT * FROM cliente;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 51 de 253

Recibiremos el siguiente mensaje: ERROR: no existe la relación «cliente» LÍNEA 1: SELECT * FROM cliente; ^

Clave Primaria y Foránea En las bases de datos relacionales, se le llama clave primaria a un campo o a una combinación de campos que identifica de forma única a cada fila de una tabla. Por lo que no pueden existir dos filas en una tabla que tengan la misma clave primaria. Y las claves foráneas tienen por objetivo establecer una conexión con la clave primaria que referencia de otra tabla, creándose una asociación entre las dos tablas.

Ejemplo Práctico Primero crearemos la tabla profesores en que ID_profesor será la clave primaria y está definido como serial que automáticamente irá ingresando los valores 1, 2, 3 a cada registro. postgres=# CREATE TABLE profesores(ID_profesor serial, nombre VARCHAR(30), apellido VARCHAR(30), PRIMARY KEY(ID_profesor));

Recibiremos el siguiente mensaje: NOTICE: CREATE TABLE creará una secuencia implícita «profesores_id_profesor_seq» para la columna serial «profesores.id_profesor» NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «profesores_pkey» para la tabla «profesores» CREATE TABLE

Ahora vamos a crear la tabla de cursos en que ID_curso será la clave primaria de esta tabla y ID_profesor será la clave foránea, que se encargará de realizar una conexión entre estas dos tablas. postgres=# CREATE TABLE cursos(ID_curso serial, titulo VARCHAR(30), ID_profesor INTEGER, PRIMARY KEY(ID_curso), FOREIGN KEY(ID_profesor) REFERENCES profesores(ID_profesor));

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 52 de 253

Recibiremos el siguiente mensaje: NOTICE: CREATE TABLE creará una secuencia implícita «cursos_id_curso_seq» para la columna serial «cursos.id_curso» NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «cursos_pkey» para la tabla «cursos» CREATE TABLE

Se insertarán algunos datos para poder realizar una selección y poder visualizar el funcionamiento de la clave primaria y foránea postgres=# INSERT INTO profesores(nombre, apellido) VALUES('Alfred','JOHNSON'); INSERT 0 1 postgres=# INSERT INTO profesores(nombre, apellido) VALUES('Alisson','DAVIS'); INSERT 0 1 postgres=# INSERT INTO profesores(nombre, apellido) VALUES('Bob','MILLER'); INSERT 0 1 postgres=# INSERT INTO profesores(nombre, apellido) VALUES('Betty','WILSON'); INSERT 0 1 postgres=# INSERT INTO profesores(nombre, apellido) VALUES('Christin','JONES'); INSERT 0 1 postgres=# INSERT INTO profesores(nombre, apellido) VALUES('Edison','SMITH'); INSERT 0 1

Quedando la tabla de la siguiente manera si seleccionamos todas las columnas. postgres=# SELECT * FROM profesores; id_profesor | nombre | apellido -------------+----------+---------1 | Alfred | JOHNSON 2 | Alisson | DAVIS 3 | Bob | MILLER 4 | Betty | WILSON 5 | Christin | JONES 6 | Edison | SMITH (6 rows) Nota Como se puede ver en la tabla de profesores, el “id_profesor” que lo definimos como tipo de dato serial se autoincremento automáticamente sin necesidad de ingresarlo nosotros, además se definió como una clave primaria.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 53 de 253

Ahora insertamos los datos de la tabla cursos. postgres=# INSERT INTO cursos(titulo, ID_profesor) VALUES('Base de datos',2); INSERT 0 1 postgres=# INSERT INTO cursos(titulo, ID_profesor) VALUES('Estructura de datos',5); INSERT 0 1 postgres=# INSERT INTO cursos(titulo, ID_profesor) VALUES('Arquitectura de computadores',1); INSERT 0 1 postgres=# INSERT INTO cursos(titulo, ID_profesor) VALUES('Recuperacion de informacion',3); INSERT 0 1 postgres=# INSERT INTO cursos(titulo, ID_profesor) VALUES('Teoria de sistemas',4); INSERT 0 1 postgres=# INSERT INTO cursos(titulo, ID_profesor) VALUES('Sistemas de informacion',6); INSERT 0 1

Quedando la tabla de siguiente manera. postgres=# SELECT * FROM cursos; id_curso | titulo | id_profesor ----------+------------------------------+------------1 | Base de datos | 2 2 | Estructura de datos | 5 3 | Arquitectura de computadores | 1 4 | Recuperacion de informacion | 3 5 | Teoria de sistemas | 4 6 | Sistemas de informacion | 6 (6 rows) Nota Un profesor puede tener asignado más de un curso, no existe restricción.

Ahora queremos tener solo una tabla con el “nombre”, “apellido” del profesor y el “titulo” de la asignatura que dicta. Para esto realizamos una selección de la siguiente manera: postgres=# SELECT nombre, apellido, titulo FROM profesores, cursos profesores.id_profesor=cursos.id_profesor; nombre | apellido | titulo ----------+----------+-----------------------------Alisson | DAVIS | Base de datos Christin | JONES | Estructura de datos Alfred | JOHNSON | Arquitectura de computadores Bob | MILLER | Recuperacion de informacion Betty | WILSON | Teoria de sistemas

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

WHERE

Página 54 de 253

Edison (6 rows)

| SMITH

| Sistemas de informacion

Aquí es donde tiene la importancia la clave primaria y foránea, puesto que en la condición

podemos

realizar

una

igualdad

entre

los

“id_profesor”

de

la

tabla profesores y cursos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 55 de 253

Lección VI - Tipo de Datos Descripción En SQL se tienen varios tipos de datos. Cuando creamos una tabla con la instrucción create table, tenemos que especificar el tipo de dato de cada columna. [1] 1. Cadenas de caracteres de largo fijo y variable: Un atributo de una tabla de tipo CHAR(n) denota una cadena de longitud fija de n carácteres. Es decir, si un atributo es de tipo CHAR(n), entonces en cualquier tupla el componente para este atributo será una cadena de n caracteres. VARCHAR(n) denota una cadena de hasta n caracteres. 2. Cadenas de bits de longitud fija o variable: Estas cadenas son análogas a las cadenas de caracteres fijos y variables de longitud, pero sus valores son cadenas de bits en lugar de carácteres. El tipo BIT(n) denota cadenas de bits de longitud n y BIT VARYING (n) es una cadena de bits de longitud variable. 3. Tipo de dato booleanos: BOOL denota un atributo cuyo valor es lógico. Los valores posibles de este tipo de atributo son TRUE, FALSE, y ANTONIANA. 4. Tipo

de

dato

entero: INTEGER denota

típicos

valores

enteros.

El

tipo SMALLINT también denota números enteros, pero el número de bits permitidos puede ser menor. 5. Tipo de dato flotante: Podemos utilizar el tipo FLOAT para los típicos números de punto flotante. 6. Tipo de dato Fecha/Hora: Pueden ser representados por DATE y TIME. Estos valores son esencialmente cadenas de carácteres de una forma especial. Además existe un tipo de dato llamado TIMESTAMP. El formato que deben tener se muestra en la siguiente tabla:

TipoDATETIMESTAMPTIMEDescripciónFecha ANSI SQL "aaaa-mmdd".Fecha y hora "aaaa-mm-dd hh:mm:ss".Hora ANSI SQL "hh:mm:ss". Ejemplo práctico A continuación se mostrarán ejemplos realizados con PostgreSQL de los tipos de datos nombrados anteriormente. 

Este ejemplo trata del juego adivina quién donde se realizan preguntas como: tu personaje tiene gafas, es rubio, es alto. La tabla queda de la siguiente manera utilizando los valores booleanos para crear las tablas:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 56 de 253



     

postgres=# CREATE TABLE Adivina_quien(Personaje VARCHAR(30), GAFAS BOOL, RUBIO BOOL, ALTO BOOL); CREATE TABLE postgres=# INSERT INTO Adivina_quien(Personaje,GAFAS,RUBIO,ALTO) VALUES('Tomas',true,false,true); INSERT 0 1 postgres=# SELECT*FROM Adivina_quien; personaje | gafas | rubio | alto -----------+-------+-------+-----Tomas | t | f | t (1 fila)



Se mostrará a continuación un ejemplo en que se utilicen los tipos de

 

datos VARCHAR, CHAR y DATETIME.

Creamos la tabla persona con el id de tipo serial, nombre y apellido de tipo VARCHAR con un largo variable hasta 25 carácteres, genero de tipo CHAR con solo un carácter y la fecha de nacimiento que es un tipo de dato DATETIME. postgres=# CREATE TABLE persona(id serial, nombre VARCHAR(25), apellido VARCHAR(25), genero CHAR(1), fecha_nac DATE);

Retornando lo siguiente PostgreSQL NOTICE: CREATE TABLE creará una secuencia implícita «persona_id_seq» para la columna serial «persona.id» CREATE TABLE

Ahora ingresamos los datos de una persona: postgres=# INSERT INTO persona(nombre,apellido,genero,fecha_nac) VALUES('Paul','Anderson','M','1983-02-12'); INSERT 0 1

Finalmente seleccionamos la tabla para ver los datos que se ingresaron: postgres=# SELECT * FROM persona; id | nombre | apellido | genero | fecha_nac ----+--------+----------+--------+-----------1 | Paul | Anderson | M | 1983-02-12 (1 fila) 

Supongamos que en el siguiente ejemplo un alumno está registrando las notas de sus ramos de la universidad en una tabla llamada notas, ingresando el

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 57 de 253

nombre del ramo como VARCHAR de un largo de 30 carácteres, nota_1 y nota_2 del tipo INTEGER y finalmente su promedio de notas que es del tipo FLOAT. 

postgres=# CREATE TABLE notas(id serial, INTEGER, nota_2 INTEGER, promedio FLOAT);

ramo

VARCHAR(30),

nota_1

Retornando PostgreSQL NOTICE: CREATE TABLE creará una secuencia implícita «notas_id_seq» para la columna serial «notas.id» CREATE TABLE

Ingresando datos postgres=# INSERT INTO notas(ramo,nota_1,nota_2,promedio) VALUES('Base de datos', 57, 36, 46.5); INSERT 0 1 Advertencia Para ingresar un dato tipo FLOAT, el valor no lleva una “coma”, sino que un “punto”



Ahora

se

realizará

el

siguiente

ejemplo

en

el

que

se creará la

tabla test_datatype con los tipos de datos BIT(n) y BIT VARYING(n). Que en este caso será data1 con un largo fijo de 4 y data2 con un largo variable de 6.  

postgres=# CREATE VARYING(6)); CREATE TABLE

TABLE

test_datatype_bit(data1

BIT(4),

data2

BIT

Se ingresarán los datos de la siguiente manera. postgres=# INSERT VALUES(B'1010',B'10110'); INSERT 0 1 postgres=# INSERT VALUES(B'1011',B'101101'); INSERT 0 1

INTO

test_datatype_bit(data1,data2)

INTO

test_datatype_bit(data1,data2)

Los siguientes datos ingresador retornaron un error puesto que no cumplen con el largo fijo y variable definido en la creación de la tabla test_datatype_bit postgres=# INSERT INTO test_datatype_bit(data1,data2) VALUES(B'101',B'10110'); ERROR: el largo de la cadena de bits 3 no coincide con el tipo bit(4)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 58 de 253

postgres=# INSERT INTO test_datatype_bit(data1,data2) VALUES(B'1011',B'1011011'); ERROR: la cadena de bits es demasiado larga para el tipo bit varying(6) 

En este ejemplo se utilizará el tipo de dato SMALLINT y TIMESTAMP. Se mostrará una tabla en que quedará registrado el ingreso de los trabajadores a la empresa.



postgres=# CREATE TABLE registro(id_registro serial, nombre VARCHAR(30), apellido VARCHAR(30), ingreso TIMESTAMP, anos_trabajados SMALLINT);

Retornando lo siguiente NOTICE: CREATE TABLE «registro_id_registro_seq» «registro.id_registro» CREATE TABLE

creará para

una la

secuencia columna

implícita serial

Ingresamos los datos del registro de la siguiente manera. postgres=# INSERT registro(nombre,apellido,ingreso,anos_trabajados) 'ALLEN', '2012-10-23 14:05:08', 13); INSERT 0 1

Ahora

realizamos

una selección de

la

INTO VALUES('Elliott',

tabla registro para

verificar

como

quedaron los datos que ingresamos. postgres=# SELECT * FROM registro; id_registro | nombre | apellido | ingreso | anos_trabajados -------------+---------+----------+---------------------+---------------1 | Elliott | ALLEN | 2012-10-23 14:05:08 | 13 (1 fila) Nota La diferencia entre INTEGER y SMALLINT no se puede notar en este tipo de ejemplos, pero INTEGER soporta -2147483648 a +2147483647 y SMALLINT 32768 a +32767.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 59 de 253

Tipo de Dato Serial 

En el siguiente ejemplo un alumno está registrando las notas de sus ramos de la universidad en una tabla llamada notas, ingresando el nombre del ramo como VARCHAR de un largo de 30 carácteres, nota_1 y nota_2 del tipo INTEGER y finalmente su promedio de notas que es del tipo FLOAT.



El id, es de tipo serial, es decir es un numero entero que tiene una secuencia asociada. Al crear la tabla notas e indicar que el campo id es serial, se crea de forma implícita una secuencia asociada al campo id.



postgres=# CREATE TABLE notas(id serial, INTEGER, nota_2 INTEGER, promedio FLOAT);

ramo

VARCHAR(30),

nota_1

Retornando PostgreSQL NOTICE: CREATE TABLE creará una secuencia implícita «notas_id_seq» para la columna serial «notas.id» CREATE TABLE

Ingresando datos postgres=# INSERT INTO notas(ramo,nota_1,nota_2,promedio) VALUES('Base de datos', 57, 36, 46.5); INSERT 0 1

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 60 de 253

Lección VII – Sentencia SELECT Básica Corresponde a la forma más simple de hacer una consulta en SQL, la cual sirve para preguntar por aquellas tuplas de una relación que satisfagan una condición. Es análoga a la selección en álgebra relacional. Esta consulta, al igual que la mayoría de las realizadas en este lenguaje de programación, utiliza 3 palabras clave: SELECT - FROM WHERE.

En palabras simples, lo que se busca con esta consulta es seleccionar cierta información (SELECT) de alguna tabla (FROM) que satisfaga (WHERE) ciertas condiciones. Por ejemplo: Obtener los nombres de los alumnos que hayan nacido en el mes de Noviembre SELECT "los nombres" FROM "alumnos" WHERE "hayan nacido en el mes de Noviembre"

Cabe destacar que en este ejemplo, se infiere la existencia de una tabla de nombre alumnos que alberga datos personales de ciertos estudiantes.

Desde el Álgebra Relacional El operador de selección en el Álgebra relacional hace uso de la palabra clave WHERE. Por lo general, las expresiones que siguen a esta keyword incluyen expresiones condicionales. Podemos construir expresiones mediante la comparación de valores (como por ejemplo tipos de datos enteros, cadenas de caracteres, etc) utilizando los 6 operadores más comunes de comparación: 

= “igual a”



“distinto a” o “no igual a”



< “menor que”



> “mayor que”



= “mayor o igual a”

Estos operadores tienen el mismo significado que en el lenguaje C, siendo el único diferente el símbolo “” que corresponde a “distinto a”; el lenguaje C utiliza el símbolo ”!=” para este comparador. Siguiendo la comparación entre estos lenguajes, el símbolo de igualdad en SQL corresponde a “=”, mientras que en C es “==”. Estos valores pueden ser comparados incluyendo constantes y atributos de las relaciones nombradas después de la palabra clave FROM. En el ejemplo, correspondería al atributo del mes de nacimiento del individuo con el mes de Noviembre. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 61 de 253

Algunos ejemplos de comparación: StudioName = 'Ubisoft' : se compara que el atributo studioName sea 'Ubisoft' mesesVidaUtil 5 : se compara que el atributo mesesVidaUtil no sea igual a 5 mesNacimiento = 'Noviembre': se compara que el atributo mesNacimiento sea igual a 'Noviembre'

SELECT-FROM-WHERE Trabajemos bajo el siguiente ejemplo, el cual consiste en seleccionar toda la información de la relación (o tabla) Empleados cuyos atributos departamento sea ‘Informatica’ y que su atributo ano_ingreso sea mayor o igual al año 2005. Para comenzar a realizar este ejemplo, primero debemos crear la tabla Empleados de la siguiente manera. postgres=# CREATE TABLE Empleados(id_empleado serial, nombre_empleado VARCHAR(30), departamento VARCHAR(30), ano_ingreso INTEGER);

retornando lo siguiente PostgreSQL.: NOTICE: CREATE TABLE creará una secuencia implícita «empleados_id_empleado_seq» para la columna serial «empleados.id_empleado» CREATE TABLE

Ahora insertaremos algunos datos en la tabla Empleados. postgres=# INSERT INTO Empleados(nombre_empleado, VALUES('Edgar', 'Administracion', 2000); INSERT 0 1 postgres=# INSERT INTO Empleados(nombre_empleado, VALUES('Andrew', 'Comercial', 2009); INSERT 0 1 postgres=# INSERT INTO Empleados(nombre_empleado, VALUES('Valerie', 'Informatica', 2000); INSERT 0 1 postgres=# INSERT INTO Empleados(nombre_empleado, VALUES('Karl', 'Informatica', 2008); INSERT 0 1 postgres=# INSERT INTO Empleados(nombre_empleado, VALUES('Kevin', 'Finanzas', 2010); INSERT 0 1

departamento, ano_ingreso)

departamento, ano_ingreso)

departamento, ano_ingreso)

departamento, ano_ingreso)

departamento, ano_ingreso)

Finalmente podemos realizar la consulta que nos interesa.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 62 de 253

postgres=# SELECT * FROM Empleados WHERE departamento='Informatica' ano_ingreso>=2005; id_empleado | nombre_empleado | departamento | ano_ingreso -------------+-----------------+--------------+------------4 | Karl | Informatica | 2008 (1 fila)

AND

Nota Podemos notar que la consulta retorna el registro que se cumplian las dos condiciones.

Podemos realizar la siguiente consulta, encontrar en la tabla Empleados el registro de la(s) personas que sean del departamento de ‘Informática’ o que su año de ingreso sea mayor o igual al año 2005. postgres=# SELECT * FROM Empleados WHERE departamento='Informatica' ano_ingreso>=2005; id_empleado | nombre_empleado | departamento | ano_ingreso -------------+-----------------+--------------+------------2 | Andrew | Comercial | 2009 3 | Valerie | Informatica | 2000 4 | Karl | Informatica | 2008 5 | Kevin | Finanzas | 2010 (4 filas)

OR

Nota Podemos observar que la consulta realizada retorna los registros que cumplen con una de las dos condiciones o cuando se cumplen las dos al mismo tiempo.

Esta consulta presentó un ejemplo básico de una consulta SELECT - FROM - WHERE de la mayoría de las consultas SQL. La palabra claveFROM selecciona la relación o relaciones de donde se obtiene la información (tablas). En estos ejemplos, se utilizaron dos comparaciones unidas por la condición “AND” y “OR”. El atributo departamento de la tabla Empleados es probada por igualdad contra la constante ‘Informática’. Esta constante corresponde a una cadena de caracteres de largo variable que en SQL como se detalló en la Lección anterior se denomina como VARCHAR(n) y que al momento del ingreso de los datos a las tablas se escribe entre comillas simples. Como se mencionó anteriormente, la consulta del tipo SELECT - FROM - WHERE busca la información de una o más relaciones que cumplan con ciertas condiciones. Hasta ahora sólo se ha visto qué pasa si se comparan atributos de las relaciones con constantes. Pero ¿cómo se pueden comparar los valores almacenados de atributos que están en varias relaciones?.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 63 de 253

El ejemplo anterior se podría realizar de otra manera para poder combinar dos relaciones (tablas) a la hora de realizar la consulta, pero primero debemos realizar la creación de la tabla Empleados y Departamentos. Advertencia Antes de realizar la creación de las tablas, hay que borrar la tabla Empleados con un DROP TABLE Empleados.

Para poder realizar el ejemplo debemos crear la tabla de Departamentos. postgres=# CREATE TABLE Departamentos(id_departamento VARCHAR(30), PRIMARY KEY(id_departamento));

serial,

departamento

retornando PostgreSQL que la tabla Departamentos ha sido correctamente creada.: NOTICE: CREATE TABLE creará una «departamentos_id_departamento_seq» para la «departamentos.id_departamento» NOTICE: CREATE TABLE / PRIMARY KEY creará «departamentos_pkey» para la tabla «departamentos» CREATE TABLE

secuencia columna

implícita serial

el

implícito

índice

Y ahora creamos la tabla Empleados. postgres=# CREATE TABLE Empleados(id_empleados serial, nombre_empleado VARCHAR(30), id_departamento INTEGER, ano_ingreso INTEGER, PRIMARY KEY(id_empleados), FOREIGN KEY(id_departamento) REFERENCES Departamentos(id_departamento));

Retornando PostgreSQL que la tabla Empleados ha sido correctamente creada.: NOTICE: CREATE TABLE creará una secuencia implícita «empleados_id_empleados_seq» para la columna serial «empleados.id_empleados» NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «empleados_pkey» para la tabla «empleados» CREATE TABLE

Ahora debemos ingresar los datos en la tabla Departamentos y Empleados. postgres=# INSERT 0 1 postgres=# INSERT 0 1 postgres=# INSERT 0 1 postgres=# INSERT 0 1

INSERT INTO Departamentos(departamento) VALUES('Administracion'); INSERT INTO Departamentos(departamento) VALUES('Informatica'); INSERT INTO Departamentos(departamento) VALUES('Finanzas'); INSERT INTO Departamentos(departamento) VALUES('Comercial');

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 64 de 253

postgres=# ano_ingreso) INSERT 0 1 postgres=# ano_ingreso) INSERT 0 1 postgres=# ano_ingreso) INSERT 0 1 postgres=# ano_ingreso) INSERT 0 1 postgres=# ano_ingreso) INSERT 0 1

INSERT INTO Empleados(nombre_empleado, VALUES('Edgar', 1, 2000);

id_departamento,

INSERT INTO Empleados(nombre_empleado, VALUES('Andrew', 4, 2009);

id_departamento,

INSERT INTO Empleados(nombre_empleado, VALUES('Valerie', 2, 2000);

id_departamento,

INSERT INTO Empleados(nombre_empleado, VALUES('Karl', 2, 2008);

id_departamento,

INSERT INTO Empleados(nombre_empleado, VALUES('Kevin', 3, 2010);

id_departamento,

Ahora realizamos la siguiente consulta, encontrar en la tabla Empleados el registro de la(s) personas que sean del departamento de ‘Informatica’ y que su año de ingreso sea mayor o igual al año 2005. postgres=# SELECT * FROM Empleados, Departamentos WHERE Empleados.id_departamento=Departamentos.id_departamento AND Empleados.ano_ingreso>=2005 AND Departamentos.departamento='Informatica'; id_empleados | nombre_empleado | id_departamento | ano_ingreso | id_departamento | departamento --------------+-----------------+-----------------+-------------+----------------+-------------4 | Karl | 2 | 2008 | 2 | Informatica (1 fila) Nota Es posible dar referencia a un atributo de cada tabla con nombre_tabla.atributo, para realizar las condiciones.

Independientemente del tipo de consulta, el resultado de una comparación es un valor booleano, es decir retorna valores TRUE o FALSE, los cuales se pueden combinar con sus operadores AND, OR y NOT, con sus respectivos significados. A modo de repaso, los operadores lógicos mencionados son: 

AND: Retorna TRUE siempre y cuando TODOS los atributos a comparar

sean TRUE. Si hay AL MENOS UN valor FALSE, retornará FALSE. Su tabla de verdad es:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 65 de 253

PTrueTrueFalseFalseQTrueFalseTrueFalseANDTrueFalseFal seFalse 

OR: Retorna TRUE siempre y cuando AL MENOS UNO de los atributos a

comparar sea TRUE. Si TODOS los valores son FALSE, retornará FALSE. Su tabla de verdad es:

PTrueTrueFalseFalseQTrueFalseTrueFalseORTrueTrueTrue False 

NOT: Retorna el valor cont



rario al valor actual, es decir que si el valor es TRUE, retorna FALSE y vice versa. Su tabla de verdad es

PTrueFalseNOT PFalseTrue Nota SQL no distingue entre mayúsculas y minúsculas. Por ejemplo, FROM (palabra reservada) es equivalente a from, inclusive a From. Para los nombres de atributos, relaciones, etc., también ocurre lo mismo. El único caso en que se distingue entre mayúsculas y minúsculas es al momento de encerrar un string entre ‘ ‘. Por ejemplo ‘PALABRA’ es diferente a ‘palabra’.

Resultados Repetidos Al realizar una consulta SELECT, no hay omisión de resultados repetidos, este “problema” se soluciona agregando DISTINCT a la consulta. SELECT FROM WHERE SELECT DISTINCT FROM WHERE

En el ejemplo anterior también es posible eliminar los resultados repetidos, puesto que existen muchas personas que trabajan en el mismo departamento, pero si eliminamos las repeticiones solo nos retornaran los departamentos que existen. Primero mostraremos un resultado con una consulta con repeticiones. postgres=# SELECT Departamentos.departamento, Empleados.id_departamento FROM Empleados, Departamentos WHERE

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 66 de 253

Empleados.id_departamento=Departamentos.id_departamento; id_departamento ----------------+----------------Administracion | 1 Comercial | 4 Informatica | 2 Informatica | 2 Finanzas | 3 (5 filas)

departamento

|

Nota Según los datos que se ingresaron en la tabla Empleados existe más de una persona en el departamento de ‘Informática’.

Y ahora realizamos una consulta sin repeticiones. postgres=# SELECT DISTINCT Departamentos.departamento, Empleados.id_departamento FROM Empleados, Departamentos WHERE Empleados.id_departamento=Departamentos.id_departamento; departamento | id_departamento ----------------+----------------Administracion | 1 Informatica | 2 Comercial | 4 Finanzas | 3 (4 filas)

Nota Se puede notar que solo nos retorna los departamentos que existen.

SELECT-BY-ORDER Hasta este momento, es posible obtener datos de una tabla utilizando los comandos SELECT y WHERE. Sin embargo, muchas veces es necesario enumerar el resultado en un orden particular. Esto podría ser en orden ascendente, en orden descendente, o podría basarse en valores numéricos o de texto. En tales casos, podemos utilizar la palabra clave ORDER BY para lograr esto.

SELECT "L" FROM "R" WHERE "C" ORDER BY "O" [ASC, DESC];

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 67 de 253

donde: 

“L” corresponde a la lista de atributos que se requieren, por lo general se asocia a una(s) columna(s).



“R” corresponde al nombre de la relación, que por lo general se asocia a una tabla.



“C” corresponde a la condición de la selección.



“O” corresponde a cómo será ordenada la lista “L”.



ASC corresponde a un orden ascendente (corresponde a la opción por defecto)



DESC corresponde a uno descendente.

Estrictamente, su sintaxis corresponde a ORDER BY y luego una lista de atributos que definirán los campos a ordenar: SELECT atributo1, atributo2 ... FROM Clientes ORDER BY atributo_ordenar_primero, atributo_ordenar_segundo...

Como se puede apreciar, con la sentencia ORDER BY se pueden ordenar las consultas a través de múltiples atributos. En este caso todos los campos estarían ordenados de forma ascendente (ASC). Podemos utilizar los mismos ejemplos que creamos anteriormente ordenando los nombres de los empleados de la tabla Empleados. postgres=# SELECT * FROM Empleados ORDER BY nombre_empleado; id_empleados | nombre_empleado | id_departamento | ano_ingreso --------------+-----------------+-----------------+------------2 | Andrew | 4 | 2009 1 | Edgar | 1 | 2000 4 | Karl | 2 | 2008 5 | Kevin | 3 | 2010 3 | Valerie | 2 | 2000 (5 filas)

Que es lo mismo que escribir. postgres=# SELECT * FROM Empleados ORDER BY nombre_empleado ASC; id_empleados | nombre_empleado | id_departamento | ano_ingreso --------------+-----------------+-----------------+------------2 | Andrew | 4 | 2009 1 | Edgar | 1 | 2000 4 | Karl | 2 | 2008 5 | Kevin | 3 | 2010 3 | Valerie | 2 | 2000

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 68 de 253

(5 filas)

Y de forma descendiente sería de la siguiente manera. postgres=# SELECT * FROM Empleados ORDER BY nombre_empleado DESC; id_empleados | nombre_empleado | id_departamento | ano_ingreso --------------+-----------------+-----------------+------------3 | Valerie | 2 | 2000 5 | Kevin | 3 | 2010 4 | Karl | 2 | 2008 1 | Edgar | 1 | 2000 2 | Andrew | 4 | 2009 (5 filas)

También es posible realizarlo con números o fechas. postgres=# SELECT * FROM Empleados ORDER BY ano_ingreso DESC; id_empleados | nombre_empleado | id_departamento | ano_ingreso --------------+-----------------+-----------------+------------5 | Kevin | 3 | 2010 2 | Andrew | 4 | 2009 4 | Karl | 2 | 2008 1 | Edgar | 1 | 2000 3 | Valerie | 2 | 2000 (5 filas)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 69 de 253

Lección VIII - Variables de tablas y operadores de conjunto Variables de tablas Consideremos las siguientes tablas: College (cName, state, enrollment) Student (sID, sName, Average) Apply (sID, cName, major, decision)

las

cuales

representar

un

sistema

simple

de

postulación

de

estudiantes

a

establecimientos educacionales, y son creadas mediante: CREATE TABLE College(id serial, cName VARCHAR(20), state VARCHAR(30), enrollment INTEGER, PRIMARY KEY(id)); CREATE TABLE Student(sID serial, sName VARCHAR(20), Average INTEGER, PRIMARY kEY(sID)); CREATE TABLE Apply(sID INTEGER, cName VARCHAR(20), major VARCHAR(30), decision BOOLEAN, PRIMARY kEY(sID, cName, major));

Se utilizarán 4 establecimientos educacionales: INSERT INTO College (cName, state, enrollment) VALUES ('Stanford','CA',15000); INSERT INTO College (cName, state, enrollment) VALUES ('Berkeley','CA',36000); INSERT INTO College (cName, state, enrollment) VALUES ('MIT','MA',10000); INSERT INTO College (cName, state, enrollment) VALUES ('Harvard','CM',23000);

4 estudiantes: INSERT INSERT INSERT INSERT

INTO INTO INTO INTO

Student Student Student Student

(sName, (sName, (sName, (sName,

Average) Average) Average) Average)

Values Values Values Values

('Amy', 60); ('Edward', 65); ('Craig', 50); ('Irene', 49);

y 6 postulaciones: INSERT INTO Apply (sID, cName, 'science', True); INSERT INTO Apply (sID, cName, 'engineering', False); INSERT INTO Apply (sID, cName, 'natural hostory', False);

Curso SQL PostgreSQL –

major,

decision)

VALUES

(1,

'Stanford',

major,

decision)

VALUES

(1,

'Stanford',

major,

decision)

VALUES

(2,

'Berkeley',

Profesor: Ing. Mario Soto Cordones –

Página 70 de 253

INSERT INTO Apply (sID, cName, major, decision) VALUES (3, 'MIT', 'math', True); INSERT INTO Apply (sID, cName, major, decision) VALUES (3, 'Harvard', 'science', False); INSERT INTO Apply (sID, cName, major, decision) VALUES (4, 'Stanford', 'marine biology', True);

Ejemplo 1 En este ejemplo se busca la información del nombre e id de los alumnos, que postulan a uno o más establecimientos educacionales con determinado Average: SELECT Student.sID, sName, Apply.cName, Average FROM Student, Apply WHERE Apply.sID = Student.sID;

cuya salida es: sid | sname | cname | Average ----+--------+----------+----1 | Amy | Stanford | 60 1 | Amy | Stanford | 60 2 | Edward | Berkeley | 65 3 | Craig | MIT | 50 3 | Craig | Harvard | 50 4 | Irene | Stanford | 49 Nota Existe un supuesto duplicado en las primeras filas. Esto es debido a que Amy postuló a “science” y a “engineering” en Stanford. Esto puede evitarse utilizando SELECT DISTINCT en lugar de SELECT.

también es posible realizarla como: SELECT S.sID, sName, A.cName, Average FROM Student S, Apply A WHERE A.sID = S.sID;

cuya salida es: sid | sname | cname | Average ----+---------+----------+----1 | Amy | Stanford | 60 1 | Amy | Stanford | 60 2 | Edward | Berkeley | 65 3 | Craig | MIT | 50 3 | Craig | Harvard | 50

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 71 de 253

4 | Irene

| Stanford |

49

Nota Al igual que en la consulta anterior, es posible evitar el valor duplicado utilizando SELECT DISTINCT en lugar de SELECT.

Como se aprecia, es posible asignar variables a las relaciones “R” y utilizar dichas variables tanto en la lista “L” como en la condición “C”. ¿Cuál es la utilidad de esto?, más allá de escribir menos (dependiendo del nombre de la variable utilizada); en los casos en que se deben comparar múltiples instancias de la misma relación, como se verá en el ejemplo 2. Nota El por qué de la nomenclatura “L”, “R” y “C” y su significado están explicados en la Lección 7

Ejemplo 2 Cuidado con los duplicados!! Si el lector se fija en la situación descrita, los nombres de algunos atributos de diferentes relaciones y/o tablas se repiten, lo cual podría plantear la interrogante ¿a que tabla se refiere el atributo en cuestión?. Para resolver este problema, se escribe de la siguiente manera: "NombreTabla.atributo"

Concretamente en el ejemplo anterior, el alcance de nombres lo protagonizan sID de la tabla Student y sID de la tabla Apply. La diferencia se realiza a través de: Student.sID o S.sID Apply.sID o A.sID

Para la realización de este ejemplo, suponga que llegan los papeles de un postulante más, por lo que el administrador de la base de datos deberá agregar la información necesaria, es decir: INSERT INTO Student (sName, Average) Values ('Tim', 60);

En variadas ocasiones, los nombres de los atributos se repiten, dado que se comparan dos instancias de una tabla. En el este ejemplo, se buscan todos los pares de estudiantes con el mismo Average: SELECT S1.sID, S1.sName, S1.Average, S2.sID, S2.sName, S2.Average

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 72 de 253

FROM Student S1, Student S2 WHERE S1.Average = S2.Average;

Al momento de realizar esta consulta (dos instancias de una tabla), el resultado contendrá uno o varios duplicados; por ejemplo, consideremos a los 5 estudiantes: sid | sname | Average ----+--------+----1 | Amy | 60 2 | Edward | 65 3 | Craig | 50 4 | Irene | 49 5 | Tim | 60 Nota La tabla de arriba se obtuvo realizando la consulta :SQL: ‘SELECT * FROM Student;’

Los pares de estudiantes serán: Amy

-

Tim

pero la salida muestra: sid | sname | Average | sid | sname | Average ----+--------+-----+-----+--------+----1 | Amy | 60 | 5 | Tim | 60 1 | Amy | 60 | 1 | Amy | 60 2 | Edward | 65 | 2 | Edward | 65 3 | Craig | 50 | 3 | Craig | 50 4 | Irene | 49 | 4 | Irene | 49 5 | Tim | 60 | 5 | Tim | 60 5 | Tim | 60 | 5 | Amy | 60

lo cual se puede evitar modificando la consulta SELECT S1.sID, S1.sName, S1.Average, S2.sID, S2.sName, S2.Average FROM Student S1, Student S2 WHERE S1.Average = S2.Average and S1.sID S2.sID;

es decir, que el id del estudiante S1 sea diferente al id del estudiante S2; en cuyo caso la salida de la consulta es: sid | sname | Average | sid | sname | Average ----+--------+-----+-----+--------+----1 | Amy | 60 | 5 | Tim | 60 5 | Tim | 60 | 1 | Amy | 60

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 73 de 253

Establecimiento de Operadores Los Operadores de conjunto son 3: 

Unión



Intersección



Excepción

A continuación se explicará cada uno con un ejemplo:

Unión El operador “UNION”, permite combinar el resultado de dos o más sentencias SELECT. Es necesario que estas tengan el mismo número de columnas, y que, además tengan los mismos tipos de datos, por ejemplo, si se tienen las siguientes tablas: Employees_Norway: E_ID E_Name 1 Hansen, Ola 2 Svendson, Tove 3 Svendson, Stephen 4 Pettersen, Kari Employees_USA: E_ID E_Name 1 Turner, Sally 2 Kent, Clark 3 Svendson, Stephen 4 Scott, Stephen

Que se pueden crear mediante el comando CREATE TABLE: CREATE TABLE KEY(E_ID));

Employees_Norway

(E_ID

serial,

E_Name

varchar(50),

PRIMARY

CREATE TABLE KEY(E_ID));

Employees_USA

E_ID

serial,

E_Name

varchar(50),

PRIMARY

(

y pobladas con los datos mostrados a continuación: INSERT INTO Employees_Norway (E_Name) VALUES ('Hansen, Ola'), ('Svendson, Tove'), ('Svendson, Stephen'), ('Pettersen, Kari');

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 74 de 253

INSERT INTO Employees_USA (E_Name) VALUES ('Turner, Sally'), ('Kent, Clark'), ('Svendson, Stephen'), ('Scott, Stephen');

El resultado de la siguiente consulta que incluye el operador UNION: SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA;

es: e_name -------------Turner, Sally Svendson, Tove Svendson, Stephen Pettersen, Kari Hansen, Ola Kent, Clark Scott, Stephen

Hay que tener en cuenta que existe en ambas tablas un empleado con el mismo nombre “Svendson, Stephen”. Sin embargo en la salida sólo se nombra uno. Si se desea que aparezcan “UNION ALL”: SELECT E_Name as name FROM Employees_Norway UNION ALL SELECT E_Name as name FROM Employees_USA;

Utilizando “as” es posible cambiar el nombre de la columna donde quedará resultado: name --------------Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Svendson, Stephen Scott, Stephen

se aprecia que la salida contiene los nombres de los empleados duplicados:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 75 de 253

Nota En el ejemplo anterior, se utiliza “as name” en ambos SELECT. Es un hecho curioso que, si se utilizan diferentes nombres junto al “as”, digamos “as nombre1” y “as nombre2”, queda como nombre de la tabla UNION el primero en ser declarado, en este caso nombre1.

Intersección Muy similar al operador UNION, INTERSECT también opera con dos sentencias SELECT. La diferencia consiste en que UNION actúa como un OR, e INTERSECT lo hace como AND. Nota Las tablas de verdad de estos OR y AND se encuentran en la Lección 7.

Es decir que INTERSECT devuelve los valores repetidos. Utilizando el ejemplo de los empleados, y ejecutando la consulta: SELECT E_Name as name FROM Employees_Norway INTERSECT SELECT E_Name as name FROM Employees_USA;

su salida es: e_name ---------Svendson, Stephen

Excepción Similar a los operadores anteriores, su estructura se compone de dos o mas sentencias SELECT, y el operador EXCEPT. Es equivalente a la diferencia en el álgebra relacional. Utilizando las mismas tablas de los empleados, y realizando la siguiente consulta: SELECT E_Name as name FROM Employees_Norway EXCEPT SELECT E_Name as name FROM Employees_USA;

Su salida es: e-name ----------Pettersen, Kari Svedson, Tove Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 76 de 253

Hansen, Ola

Es decir, devuelve los resultados no repetidos en ambas tablas. Hay que tener en cuenta que, a diferencia de los operadores anteriores, la salida de este no es conmutativa, pues si se ejecuta la consulta de forma inversa,es decir: SELECT E_Name as name FROM Employees_USA EXCEPT SELECT E_Name as name FROM Employees_Norway;

su salida será: e-name -----------Turner, Sally Kent, Clark Scott, Stephen

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 77 de 253

Lección IX- Subconsultas en la cláusula WHERE Una subconsulta es una consulta que retorna un único valor y que está anidada dentro de un SELECT, INSERT. UPDATE o DELETE, o incluso otra subconsulta. Una subconsulta puede ser utilizada en cualquier lugar donde una expresión está permitida.

SELECT-FROM-WHERE (SELECT) Hasta ahora se han visto las consultas del tipo SELECT, WHERE, FROM, y algunos de sus derivados, como lo son el uso de JOIN y operadores lógicos AND, OR, NOT, que son normalmente utilizados para filtrar resultados mediante la manipulación de una serie de condiciones. Sin embargo Las subconsultas son otra manera de filtrar consultas. Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT y que normalmente son utilizadas para filtrar una cláusula WHERE con el conjunto de resultados de la subconsulta. Una

subconsulta

tiene

la

misma

sintaxis

que

una

sentencia SELECT normal

exceptuando que aparece encerrada entre paréntesis. Como es usual, se utilizará el ejemplo de la base de datos “Admisión a la Universidad”. College (cName, state, enrollment) Student (sID, sName, Average) Apply (sID, cName, major, decision)

cuyas tablas son creadas mediante: CREATE TABLE College(id serial, cName VARCHAR(20), state VARCHAR(30), enrollment INTEGER, PRIMARY KEY(id)); CREATE TABLE Student(sID serial, sName VARCHAR(20), Average INTEGER, PRIMARY kEY(sID)); CREATE TABLE Apply(sID INTEGER, cName VARCHAR(20), major VARCHAR(30), decision BOOLEAN, PRIMARY kEY(sID, cName, major));

Se utilizarán 4 establecimientos educacionales: INSERT INTO College ('Stanford','CA',15000); INSERT INTO College ('Berkeley','CA',36000);

Curso SQL PostgreSQL –

(cName,

state,

enrollment)

VALUES

(cName,

state,

enrollment)

VALUES

Profesor: Ing. Mario Soto Cordones –

Página 78 de 253

INSERT INTO 'MA',10000); INSERT INTO 'CM',23000);

College College

(cName, (cName,

state, state,

enrollment) enrollment)

VALUES VALUES

('MIT',

('Harvard',

8 estudiantes: INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO

Student Student Student Student Student Student Student Student

(sName, (sName, (sName, (sName, (sName, (sName, (sName, (sName,

Average) Average) Average) Average) Average) Average) Average) Average)

Values Values Values Values Values Values Values Values

('Amy', ('Edward', ('Craig', ('Irene', ('Doris', ('Gary', ('Doris', ('Tim',

60); 65); 50); 49); 45); 53); 70); 60);

y 21 postulaciones: INSERT INTO Apply (sID, cName, 'science' , True); INSERT INTO Apply (sID, cName, 'engineering' , False); INSERT INTO Apply (sID, cName, 'science' , True); INSERT INTO Apply (sID, cName, 'engineering' , False); INSERT INTO Apply (sID, cName, 'natural history', False); INSERT INTO Apply (sID, cName, 'math' , True); INSERT INTO Apply (sID, cName, 'math' , False); INSERT INTO Apply (sID, cName, , 'science' , False); INSERT INTO Apply (sID, cName, 'engineering' , True); INSERT INTO Apply (sID, cName, 'marine biology' , True); INSERT INTO Apply (sID, cName, 'natural history', False); INSERT INTO Apply (sID, cName, 'science' , False); INSERT INTO Apply (sID, cName, 'psychology' , True); INSERT INTO Apply (sID, cName, 'math' , True); INSERT INTO Apply (sID, cName, 'science' , False); INSERT INTO Apply (sID, cName, 'psychology' , True);

Curso SQL PostgreSQL –

major, decision) VALUES (1, 'Stanford', major, decision) VALUES (1, 'Stanford', major, decision) VALUES (1, 'Berkeley', major, decision) VALUES (1, 'Berkeley', major, decision) VALUES (2, 'Berkeley', major, decision) VALUES (3, 'MIT'

,

major, decision) VALUES (3, 'Harvard' , major, decision) VALUES (3, 'Harvard' major, decision) VALUES (3, 'Harvard' , major, decision) VALUES (4, 'Stanford', major, decision) VALUES (4, 'Stanford', major, decision) VALUES (5, 'Harvard' , major, decision) VALUES (5, 'Berkeley', major, decision) VALUES (5, 'MIT'

,

major, decision) VALUES (6, 'MIT'

,

major, decision) VALUES (7, 'Stanford',

Profesor: Ing. Mario Soto Cordones –

Página 79 de 253

INSERT INTO Apply (sID, cName, 'science' , True); INSERT INTO Apply (sID, cName, 'math' , True); INSERT INTO Apply (sID, cName, 'science' , True); INSERT INTO Apply (sID, cName, 'science' , False); INSERT INTO Apply (sID, cName, 'engineering' , True);

major, decision) VALUES (7, 'Stanford', major, decision) VALUES (7, 'MIT'

,

major, decision) VALUES (7, 'MIT'

,

major, decision) VALUES (7, 'Harvard' , major, decision) VALUES (8, 'MIT'

,

La situación que se pretende describir con estas tablas de ejemplo es la postulación de estudiantes a centros educacionales. En concreto la postulación del estudiante sID a la mención académica major impartida en el centro educacional cName, cuya aprobación, o decisión, será “True o False”.

Ejemplo 1 El primer ejemplo de subconsulta corresponderá al listado de IDs y Nombres de los estudiantes que han postulado para estudiar “science” en algún centro educacional. SELECT sID, sName FROM Student WHERE sID in (SELECT sID FROM Apply WHERE major = 'science');

cuya salida es: sid | sname ----+------6 | Gary 1 | Amy 3 | Craig 7 | Doris 5 | Doris (5 rows) Nota En el ejemplo existen dos personas distintas llamadas Doris.

Como se mencionó anteriormente, tanto las subconsultas como el uso de JOIN y operadores lógicos en la clausula WHERE son formas de filtrar resultados, por tanto, la consulta se puede reformular como:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 80 de 253

SELECT Student.sID, sName FROM Student, Apply WHERE Student.sID = Apply.sID AND major = 'science';

Advertencia En la consulta se debe especificar que el atributo sID corresponde al de la tabla Student, pues la tabla Apply también cuenta con dicho atributo. Si no se toma en cuenta este detalle, es probable que la consulta termine en un error con resultados no deseados.

en cuyo caso la salida será: sid | sname ----+------1 | Amy 1 | Amy 3 | Craig 6 | Gary 7 | Doris 7 | Doris 7 | Doris 5 | Doris (8 rows)

Las 3 filas “extra” se deben, a que al utilizar join y operadores lógicos, se toman en cuenta todos los resultados, por ejemplo Amy postuló en dos ocasiones a science. Al utilizar la subconsulta, se eliminan estos resultados duplicados, haciendo la consulta más fiel a la realidad pues se pregunta por aquellos estudiantes que han postulado a “science”, no cuántas veces postuló cada uno. No obstante si se agrega la clausula DISTINCT, se obtiene la misma respuesta que al utilizar una subconsulta. Es decir que para la consulta: SELECT DISTINCT Student.sID, sName FROM Student, Apply WHERE Student.sID = Apply.sID AND major = 'science';

su salida será: sid | sname ----+------6 | Gary 1 | Amy 3 | Craig 7 | Doris 5 | Doris

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 81 de 253

(5 rows)

Ejemplo 2 Este ejemplo corresponderá sólo al listado de Nombres de los estudiantes que han quedado seleccionados para estudiar ciencias en algún centro educacional. SELECT sName FROM Student WHERE sID in (SELECT sID FROM Apply WHERE major = 'science' and decision ='t' );

cuya salida es: sname ------Amy Doris (2 rows) Nota Ambas Doris no corresponden a un duplicado, ya que el atributo sID de una es 5 y de la otra es 7.

Y se obtienen los mismos 5 estudiantes. De forma análoga al ejemplo anterior, se realizará el equivalente a la subconsulta utilizandoJOIN y operadores lógicos: SELECT sName FROM Student, Apply WHERE Student.sID = Apply.sID AND major = 'science';

cuya salida es: sname ------Amy Amy Craig Gary Doris Doris Doris Doris (8 rows)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 82 de 253

Por tanto, y al igual que el ejemplo anterior, se utilizará DISTINCT, es decir: SELECT DISTINCT sName FROM Student, Apply WHERE Student.sID = Apply.sID AND major = 'science';

cuya salida es: sname ------Amy Craig Doris Gary (4 rows)

Pero solo hay 4 estudiantes. Esto se debe a que en ejemplo anterior, se utilizó tanto el sID como el sName, como ambas Doris cuentan con un sID diferente, no se tomaba en cuenta como duplicado, pero en esta consulta, al solo contar con sName, ambas Doris se toman como 2 instancias de la misma y se elimina una. En este caso, la única forma de obtener el “número correcto de duplicados” es utilizando subconsultas.

IN AND NOT IN IN y NOT IN permiten realizar filtros de forma más específica, que permiten responder preguntas como la del ejemplo 3

Ejemplo 3 En el siguiente ejemplo se quiere saber el sID y el sName de aquellos estudiantes que postularon a “science”, pero no a “engineering”: SELECT sID, sName FROM Student WHERE sID in (SELECT sID FROM Apply WHERE major = 'science') and sID not in (SELECT sID FROM Apply WHERE major = 'engineering');

cuya salida corresponde precisamente a: sid | sname -----+------5 | Doris 6 | Gary 7 | Doris Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 83 de 253

(3 rows) Nota Es posible corroborar el resultado ejecutando :sql:´SELECT * FROM Apply;´ y verificar manualmente.

La consulta realizada en este ejemplo es posible realizarla de otra manera: SELECT sID, sName FROM Student WHERE sID in (SELECT sID FROM Apply WHERE major = 'science') and not sID in (SELECT sID FROM Apply WHERE major = 'engineering');

cuya salida es equivalente a la anterior.

EXISTS AND NOT EXISTS EXISTS es una función SQL que devuelve verdadero cuando una subconsulta retorna al menos una fila.

Ejemplo 4 En este ejemplo se busca el nombre de todos los establecimientos educacionales que están en el mismo estado. Si se ejecuta: SELECT cName, state FROM College;

cuya salida es: cname | state ---------+------Stanford | CA Berkeley | CA MIT | MA Harvard | CM (4 rows)

el resultado esperado debiese contener el par Stanford - Berkeley La consulta que pretende resolver esta pregunta es: SELECT cName, state FROM College C1 WHERE exists

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 84 de 253

(SELECT * FROM College C2 WHERE C2.state = C1.state); Nota Lo que realiza esta consulta es verificar que por cada resultado obtenido en C1, lo compara con todos los resultados en C2.

cuya salida es: cname | state ---------+------Stanford | CA Berkeley | CA MIT | MA Harvard | CM (4 rows)

Esto pasa debido a que C1 y C2 pueden ser el mismo establecimiento. Por ende, es necesario dejar en claro que C1 y C2 son diferentes. SELECT cName, state FROM College C1 WHERE exists (SELECT * FROM College C2.cName);

C2

WHERE

C2.state

=

C1.state

and

C1.cName

en cuyo caso la salida corresponde a la correcta, es decir: cname | state ---------+------Stanford | CA Berkeley | CA (2 rows)

CÁLCULOS MATEMÁTICOS Es posible realizar cálculos matemáticos (valor más alto, valor más bajo) utilizando subconsultas:

Ejemplo 5 Se busca el establecimiento con mayor cantidad de alumnos. La consulta que se realizará corresponde a buscar todos los establecimientos donde no exista otro establecimiento que su cantidad de alumnos sea mayor que la primera. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 85 de 253

SELECT cName, state FROM College C1 WHERE not exists (SELECT * FROM College C2 WHERE C2.enrollment > C1.enrollment);

Donde el resultado corresponde a Berkeley. Nota De forma análoga es posible calcular el establecimiento con menor cantidad de alumnos, cambiando el signo matemático > por
(SELECT AVG(precio_ordenes) FROM Ordenes); id | fecha_ordenes | precio_ordenes | cliente ----+---------------+----------------+--------1 | 2010-09-23 | 1120 | Alison 2 | 2007-02-21 | 1990 | Alicia 5 | 2005-03-30 | 2120 | Brad (3 filas)

Función COUNT() La función COUNT() retorna el número de filas según los criterios que especificaron. En SQL la sintaxis que se utiliza para realizar la consulta es:

SQL COUNT (nombre_columna) COUNT(nombre_columna) retorna el número de valores que se encuentran en la columna

especificada. Los valores NULL no se cuentan.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 101 de 253

SELECT COUNT(nombre_columna) FROM nombre_tabla;

Realizaremos la consulta COUNT(clientes) para retornar la cantidad de cliente que tengan el nombre de Alison existen en la tablaOrdenes. postgres=# SELECT cliente='Alison'; cliente_alison ---------------3 (1 fila)

COUNT(cliente)

AS

cliente_Alison

FROM

Ordenes

WHERE

SQL COUNT(*) COUNT(*) retorna el número de registros de una tabla. SELECT COUNT(*) FROM nombre_tabla;

Se realizará la consulta COUNT(*), que retornara el número de ordenes de la tabla Ordenes. postgres=# SELECT COUNT(*) AS numero_ordenes FROM Ordenes; numero_ordenes ---------------6 (1 fila)

SQL COUNT (DISTINCT nombre_columna) COUNT(DISTINCT nombre_columna) retorna el número de valores distintos a la columna

especificada. SELECT COUNT(DISTINCT nombre_columna) FROM nombre_tabla;

Se

realizará

la

consulta COUNT(DISTINCT cliente),

que

retornará

la

cantidad

de clientes distintos que existen en la tabla Ordenes, que son Alison, Alicia y Brad. postgres=# SELECT COUNT(DISTINCT cliente) AS numero_de_clientes FROM Ordenes; numero_de_clientes -------------------3 (1 fila)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 102 de 253

Función MAX() La función MAX() retorna el máximo valor de la columna seleccionada. En SQL la sintaxis utilizada es de la siguiente manera: SELECT MAX(nombre_columna) FROM nombre_tabla;

Se realizará la consulta MAX(precio_ordenes) que retornará el mayor precio de las ordenes en la tabla Ordenes. postgres=# SELECT MAX(precio_ordenes) AS mayor_precio FROM Ordenes; mayor_precio -------------2120 (1 fila)

Función MIN() La función MIN() retorna el mínimo valor de la columna seleccionada. En SQL la sintaxis utilizada es de la siguiente manera: SELECT MIN(nombre_columna) FROM nombre_tabla;

Se realizará la consulta MIN(precio_ordenes) que retornará el menor precio de las ordenes en la tabla Ordenes. postgres=# SELECT MIN(precio_ordenes) AS menor_precio FROM Ordenes; menor_precio -------------160 (1 fila)

Función SUM() La función SUM() retorna la suma total de una columna numérica. En SQL la sintaxis utilizada es de la siguiente manera: SELECT SUM(nombre_columna) FROM nombre_tabla;

Se realizará la consulta SUM(precio_ordenes) que retornará el precio total de las ordenes que se encuentran en la tabla Ordenes.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 103 de 253

postgres=# SELECT SUM(precio_ordenes) AS precio_total FROM Ordenes; precio_total -------------6490 (1 fila)

SQL GROUP BY La instrucción GROUP BY se utiliza en conjunción con las funciones de agregado para agrupar el conjunto de resultados de una o más columnas. SELECT nombre_columna, funcion_de_agregacion(nombre_columna) nombre_tabla WHERE condicion GROUP BY nombre_columna;

La

siguiente

consulta

utilizará

la

instrucción GROUP BY,

para

FROM

realizar

la suma por cliente de los precios de ordenes en la tabla Ordenes. postgres=# SELECT cliente, SUM(precio_ordenes) FROM Ordenes GROUP BY cliente; cliente | sum ---------+-----Alison | 2220 Brad | 2120 Alicia | 2150 (3 filas)

SQL HAVING La cláusula HAVING se utiliza en SQL, puesto que la palabra clave WHERE no puede utilizarse con las funciones de agregado en sus condiciones. En SQL la sintaxis que se utiliza es de la siguiente manera: SELECT nombre_columna, funcion_de_agregacion(nombre_columna) nombre_tabla WHERE condicion GROUP BY nombre_columna funcion_de_agregacion(nombre_columna) operador valor;

FROM HAVING

Ahora queremos saber si alguno de los clientes tiene un precio total de ordenes mayor a 2130. postgres=# SELECT cliente, SUM(precio_ordenes) FROM Ordenes GROUP BY cliente HAVING SUM(precio_ordenes)>2130; cliente | sum ---------+------

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 104 de 253

Alison | 2220 Alicia | 2150 (2 filas)

Realizaremos la consulta anterior, agregando la cláusula WHERE con la condición que el cliente se igual a “Alison”. postgres=# SELECT cliente, SUM(precio_ordenes) FROM Ordenes cliente='Alicia' GROUP BY cliente HAVING SUM(precio_ordenes)>2130; cliente | sum ---------+-----Alicia | 2150 (1 fila)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

WHERE

Página 105 de 253

Lección XIII - SQL: Valores NULL NULL indica que un valor es desconocido o que aún no existe un valor asignado dentro de una base de datos. NULL no pertenece a ningún dominio de datos (no pertenece a los enteros, ni a los booleanos, ni a los flotantes, etc), se puede considerar como un marcador que indica la ausencia de un valor. Nota NULL no debe confundirse con un valor 0, ya que el valor 0 pertenece a algún tipo de dato

(entero o flotante) mientras que, como ya se mencionó, NULL es la falta de un dato.

CREATE TABLE En forma predeterminada, una columna puede ser NULL. Si se desea no permitir un valor NULL en una columna, se debe colocar una restricción en esta columna especificando que NULL no es ahora un valor permitido. Forma general: CREATE TABLE nombreTabla (atributo1 tipoAtributo NOT NULL, atributo2 tipoAtributo);

La consulta anterior crea una tabla llamada nombreTabla, con dos atributos. El primero atributo1 no acepta valores nulos (NULL), esto debido a que es acompañado de la instrucción NOT NULL, atributo2 puede no tener valores, es decir se puede dar que atributo2 contenga, en alguna de sus filas, valores desconocidos. Para ilustrar las particularidades y utilidad de NULL se utilizará el siguiente ejemplo: Una

tabla

de

clientes

que

almacena

dirección Cliente(rut−−−,nombre,apellido,

el

rut,

apellido,

nombre,

deuda

y

deuda,direccion) .

Se crea la tabla Cliente donde las columnas “rut”, “nombre” y “apellido” no incluyen NULL, mientras que “direccion” y “deuda” puede incluir NULL. Es decir, se podría desconocer la dirección del usuario sin que esto traiga problemas a la base de datos. La consulta SQL que realiza esta acción es la siguiente: postgres=# CREATE TABLE Cliente (rut int NOT NULL, nombre varchar (30) NOT NULL, apellido varchar(30)NOT NULL, deuda int, direccion varchar (30)); Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 106 de 253

CREATE TABLE

INSERT y UPDATE Los

valores NULL se

pueden

insertar

en

una

columna

si

se

indica

explícitamente NULL en una instrucción INSERT. De igual forma se puede actualizar un valor con UPDATE especificando que es NULL en la consulta. Forma general: INSERT INTO nombreTabla (atributo1,atributo2) values(valorValido, null); UPDATE nombreTabla SET atributo2= null WHERE condición;

Continuando con el ejemplo anterior, se inserta un cliente: postgres=# INSERT INTO Cliente (rut,nombre,apellido,deuda,direccion) values(123,'Tom', 'Hofstadter', 456, null); INSERT 0 1

Al insertar los valores del cliente ‘Tom Hofstadter’, se almacenó el atributo dirección como NULL, es decir sin valor asignado. Antes de exponer cómo funciona UPDATE, se agregan nuevos clientes para mostrar de mejor manera las siguientes consultas: postgres=# INSERT INTO Cliente (rut, nombre, apellido, deuda, direccion) values (412,'Greg', 'Hanks',33, 'Cooper'), (132,'Mayim ', 'Bialik',null, 'Barnett 34'), (823,'Jim', 'Parsons',93, null),(193,'Johnny', 'Galecki',201, 'Helberg 11'), (453,'Leslie', 'Abbott',303,null), (583,'Hermione', 'Weasley',47, 'Leakey 24'), (176,'Ron', 'Granger',92,'Connor 891'), (235,'Hannah', 'Winkle',104, null), (733,'Howard', 'Brown',null, null); INSERT 0 9

Realizando una consulta SELECT, para ver todos los clientes que se insertaron, se puede apreciar un espacio vacío en los valores que llevaban NULL al momento de hacer INSERT. Tal es el caso de la dirección de ‘Tom Hofstadter’ o la deuda ‘Mayim Bialik’ . postgres=# SELECT * FROM Cliente; rut | nombre | apellido | deuda | direccion -----+----------+------------+-------+-----------123 | Tom | Hofstadter | 456 | 412 | Greg | Hanks | 33 | Cooper

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 107 de 253

132 | Mayim 823 | Jim 193 | Johnny 453 | Leslie 583 | Hermione 176 | Ron 235 | Hannah 733 | Howard (10 filas)

| | | | | | | |

Bialik Parsons Galecki Abbott Weasley Granger Winkle Brown

| | | | | | | |

93 201 303 47 92 104

| | | | | | | |

Barnett 34 Helberg 11 Leakey 24 Connor 891

Ahora se puede actualizar un cliente: postgres=# UPDATE Cliente SET direccion=null WHERE rut=412; UPDATE 1

Se actualiza el cliente de rut 412, dejando su dirección sin valor conocido. Realizando nuevamente un SELECT para visualizar la tabla cliente, se puede apreciar que el cliente con rut 412, ‘Greg Hanks’, ahora aparece con una dirección sin un valor asignado. postgres=# SELECT * FROM Cliente; rut | nombre | apellido | deuda | direccion -----+----------+------------+-------+-----------123 | Tom | Hofstadter | 456 | 132 | Mayim | Bialik | | Barnett 34 823 | Jim | Parsons | 93 | 193 | Johnny | Galecki | 201 | Helberg 11 453 | Leslie | Abbott | 303 | 583 | Hermione | Weasley | 47 | Leakey 24 176 | Ron | Granger | 92 | Connor 891 235 | Hannah | Winkle | 104 | 733 | Howard | Brown | | 412 | Greg | Hanks | 33 | (10 filas)

SELECT Seleccionar atributos NULL 

Para comprobar si hay valores NULL, se usa IS NULL o



IS NOT NULL en la cláusula WHERE.

Forma general: SELECT atributo1 FROM nombreTabla WHERE atributo2 IS NULL

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 108 de 253

Utilizando el mismo ejemplo, Seleccionar todos los nombres y apellidos de los clientes donde la dirección es NULL: postgres=# SELECT nombre,apellido FROM Cliente WHERE direccion IS NULL; nombre | apellido --------+-----------Tom | Hofstadter Jim | Parsons Leslie | Abbott Hannah | Winkle Howard | Brown Greg | Hanks (6 filas)

Seleccionar todos los nombres y apellidos de los clientes donde la dirección es distinta a NULL: postgres=# SELECT nombre,apellido FROM Cliente WHERE direccion IS NOT NULL; nombre | apellido ----------+---------Mayim | Bialik Johnny | Galecki Hermione | Weasley Ron | Granger (4 filas)

Al utilizar la instrucción IS NOT NULL se seleccionan todos los clientes que tienen una dirección conocida, es decir que poseen algún valor designado en la base de datos.

Comparaciones con NULL 

La comparación entre dos NULL o entre cualquier valor y un NULL tiene un resultado desconocido pues el valor de cada NULL es desconocido. También se puede decir que no existen dos NULL que sean iguales.

La siguiente consulta selecciona el nombre y apellido de los clientes que poseen una deuda mayor a 100 o menor/igual a 100. Se puede apreciar que esta consulta abarcaría a todos los clientes, pues cualquier número entero es mayor, menor o igual a 100. postgres=# SELECT nombre,apellido FROM Cliente WHERE deuda > 100 or deuda 100 or deuda 100 or nombre= 'Howard'; nombre | apellido --------+-----------Tom | Hofstadter Johnny | Galecki Leslie | Abbott Hannah | Winkle Howard | Brown (5 filas)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 110 de 253

‘Howard’ tiene deuda NULL, anteriormente se demostró que NULL no se puede comparar, entonces no cumple con: deuda > 100. A pesar de esto, aparece en el resultado de la consulta, pues cumple con la segunda condición: nombre= ‘Howard’. Con esto se quiere explicar que no necesariamente, por tener un valor NULL dentro de sus atributos, pasa a ser completamente “invisible”, es decir mientras no se compare solamente el atributo NULL puede estar en el resultado. A modo de resumen se puede decir que: 

A = NULL no se puede decir que A tenga el mismo valor que NULL.



A NULL no se puede decir que A tenga distinto valor a NULL.



NULL = NULL es imposible saber si ambos NULL son iguales.

Operaciones con NULL 

Recordar que NULL significa desconocido. Al realizar suma donde uno de



los datos es desconocido, la suma también es desconocida:

postgres=# SELECT (SELECT deuda FROM cliente WHERE rut=132)+( SELECT deuda FROM cliente WHERE rut=583) as suma; suma -----(1 fila)

La sentencia suma la deuda del cliente 132 que es NULL con la deuda del cliente 583 que es 47, NULL + 47 arroja como resultado NULL. Lo mismo ocurre con la resta, multiplicación y división.

Operadores lógicos 

Cuando hay valores NULL en los datos, los operadores lógicos y de comparación pueden devolver un tercer resultado UNKNOWN(desconocido) en lugar de simplemente TRUE (verdadero) o FALSE (falso). Esta necesidad de una lógica de tres valores es el origen de muchos errores de la aplicación.

Se agrega una nueva columna que contenga valores booleanos: postgres=# ALTER table Cliente add actual bool; ALTER TABLE

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 111 de 253

Se insertan algunos valores para la nueva columna actual. Esta columna describe si un cliente es actual o dejó de ser cliente de la compañía. postgres=# UPDATE 1 postgres=# UPDATE 1 postgres=# UPDATE 1 postgres=# UPDATE 1 postgres=# UPDATE 1 postgres=# UPDATE 1 postgres=#

UPDATE Cliente SET actual=true WHERE rut=412; UPDATE Cliente SET actual=true WHERE rut=123; UPDATE Cliente SET actual=true WHERE rut=193; UPDATE Cliente SET actual=false WHERE rut=733; UPDATE Cliente SET actual=false WHERE rut=823; UPDATE Cliente SET actual=false WHERE rut=453; SELECT * FROM Cliente;

rut | nombre | apellido | deuda | direccion | actual -----+----------+------------+-------+------------+-------132 | Mayim | Bialik | | Barnett 34 | 583 | Hermione | Weasley | 47 | Leakey 24 | 176 | Ron | Granger | 92 | Connor 891 | 235 | Hannah | Winkle | 104 | | 412 | Greg | Hanks | 33 | | t 123 | Tom | Hofstadter | 456 | | t 193 | Johnny | Galecki | 201 | Helberg 11 | t 733 | Howard | Brown | | | f 823 | Jim | Parsons | 93 | | f 453 | Leslie | Abbott | 303 | | f (10 filas)

IS UNKNOWN retorna los valores que no son false ni true. A continuación se muestra su uso, seleccionando de la tabla cliente todos los nombres que en su atributo actual, no poseen valor. postgres=#

SELECT nombre FROM cliente WHERE actual IS UNKNOWN;

nombre ---------Mayim Hermione Ron Hannah (4 filas)

IS NOT UNKNOWN funciona de la misma forma solo que retorna los valores que poseen algún valor asignado, ya sea true o false. Para los operadores and y or que involucran NULL, de manera general se puede decir: Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 112 de 253



NULL or false = NULL



NULL or true = true



NULL or NULL = NULL



NULL and false = false



NULL and true = NULL



NULL and NULL = NULL



not (NULL) El inverso de NULL también es NULL.

Nota Para minimizar las Prácticas de mantenimiento y los posibles efectos en las consultas o informes existentes, debería minimizarse el uso de los valores desconocidos. Es una buena práctica plantear las consultas e instrucciones de modificación de datos de forma que los datos NULLtengan un efecto mínimo.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 113 de 253

Lección XIV - SQL: Declaraciones de las Modificaciones de Datos Como ya se ha dicho en algunas de las Leccións anteriores, existen 4 operaciones básicas relacionadas con la manipulación de datos en una tabla SQL: Selección Inserción Actualización Eliminación

-> -> -> ->

SELECT INSERT UPDATE DELETE

En esta Lección se verán en profundidad, las operaciones que permiten mantener una base de datos, es decir, INSERT, UPDATE y DELETE.

INSERT Para insertar datos, existen al menos dos formas. Una se ha visto desde las primeras Leccións (INSERT INTO): INSERT INTO table VALUES (atributo1, atributo2 ...);

Es decir que se insertará en la tabla, los valores correspondientes a sus atributos de la. Para poder utilizar esta forma, es necesario que la cantidad de valores asociados a los atributos, sea igual a la cantidad de atributos de la tabla, y que estén en el mismo orden respecto a los tipos de datos y los datos que se quieran insertar. El ejemplo 1 aclarará posibles dudas:

Contexto Utilicemos la tabla “Student”, que ya se ha utilizado en Leccións anteriores: Student (sID, sName, Average)

y creemos una nueva tabla llamada Student_new, con una estructura similar, pero vacía: CREATE TABLE Student_new (sID serial, sName VARCHAR(20), Average INTEGER, PRIMARY KEY(sID));

Es decir, cuenta con 3 atributos, los cuales son: el identificador o sID de carácter entero y serial, lo cual significa que si no se especifica un valor, tomará un valor entero; el

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 114 de 253

nombre o sName que corresponde a una cadena de caracteres, y el promedio o Average, es decir un número entero.

Ejemplo 1 Supongamos que planilla de estudiantes albergada en la tabla Student ya fue enviada y no se puede modificar, es por ello que se necesita crear una nueva planilla (otra tabla student), y agregar a los nuevos alumnos postulantes. Por lo tanto es posible agregar un estudiante mediante: INSERT INTO Student_new VALUES (1,'Betty', 78);

cuya salida, después de ejecutar el : SELECT * FROM Student_new;

es sid | sname | average -----+-------+--------1 | Betty | 78 (1 row)

Al utilizar el atributo sID como serial, es posible omitir el valor de este atributo a la hora de insertar un nuevo estudiante: INSERT INTO Student_new (sName, Average) VALUES ('Wilma', 81);

Pero, esto resulta en el siguiente error: ERROR: duplicate key value violates unique constraint "student_new_pkey" DETAIL: Key(sid)=(1) already exists.

Esto se debe a que sID es clave primaria, y serial tiene su propio contador, que parte de 1 (el cual no está ligado necesariamente a los valores de las diversas filas que puedan existir en la tabla). Hasta este punto, sólo se pueden seguir añadiendo alumnos agregado de forma explícita todos y cada uno de los atributos de la tabla, sin poder prescindir en este caso de sID y su característica de ser serial, pues la tupla atributovalor (sID)=(1) está bloqueada. Nota Es posible eliminar directamente la fila que corresponde a ‘Betty’, pero ese paso se reserva a la subsección de DELETE, presentada más adelante en esta Lección

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 115 de 253

Ejemplo 2 Es posible modificar la inserción de ‘Betty’ para que sea similar a la de ‘Wilma’. Nota A continuación se usará el comando SQL DROP TABLE, que permite eliminar una tabla entera.

DROP TABLE Student_new; CREATE TABLE Student_new(sID serial, sName VARCHAR(20), Average INTEGER, PRIMARY kEY(sID)); INSERT INTO Student_new (sName, Average) VALUES ('Betty', 78); INSERT INTO Student_new (sName, Average) VALUES ('Wilma', 81);

Como se ha modificado la consulta de ‘Betty’, se utiliza el contador propio del atributo serial, por lo que no hay conflictos. Si se selecciona toda la información de la tabla: SELECT * FROM Student_new;

la salida es: sid | sname | average -----+-------+--------1 | Betty | 78 2 | Wilma | 81 (2 rows)

UPDATE Es posible modificar o “actualizar” datos a través del comando UPDATE, cuya sintaxis es: UPDATE table SET Attr = Expression

WHERE Condition;

Es decir que se actualiza de la tabla el atributo Attr (el valor anterior, por el valor “Expression”), bajo una cierta condición “Condition” Nota Es importante destacar que la condición puede variar, puede ser de carácter sumamente complejo, una sub-consulta, una sentencia que involucre otras tablas. “Expression” también puede ser un valor que involucre otras tablas, no necesariamente corresponde a un valor de comparación directa. Se aplica lo mismo para la condición.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 116 de 253

Es necesario destacar que, si bien se puede actualizar un atributo, también se pueden actualizar varios a la vez: UPDATE table SET Attr1 = Expression1, Attr2 = Expression2,..., AttrN = ExpressionN WHERE Condition;

Ejemplo 3 Bajo el contexto del ejemplo 2, supongamos que la nota de ‘Wilma’ corresponde a un 91 en lugar de 81. Se desea corregir este error de tipéo, a través del comando UPDATE. Es necesario recordar que dependiendo de la cantidad de atributos de la tabla, es posible realizar de muchas formas la actualización: UPDATE Student_new SET Average = 91 WHERE sName = 'Wilma';

o UPDATE Student_new SET Average = 91 WHERE Average = 81;

Ambos casos no son erróneos, pues realizan el cambio pedido. No obstante, es

necesario tener la costumbre de trabajar con atributos que sean únicos, es decir la clave primaria (en este caso el atributo sID). La razón corresponde a que en caso de haber más de una Wilma se cambiaría el promedio de ambas, lo mismo para el caso de que varias personas cuenten con un promedio igual a 81. Por lo tanto la consulta ideal corresponde a: UPDATE Student_new SET Average = 91 WHERE sID = 2;

Verificando a través de la ejecución de un select: SELECT * FROM Student_new;

la salida es: sid | sname | average -----+-------+--------1 | Betty | 78 2 | Wilma | 91 Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 117 de 253

(2 rows)

Es decir, se actualizó correctamente la nota de ‘Wilma’.

DELETE Es posible eliminar filas de información, que cumplan una determinada condición. Esto es especialmente útil en casos donde se desee borrar filas específicas en lugar de tener que borrar toda una tabla. La sintaxis del comando DELETE es: DELETE FROM table WHERE Condition;

Es decir que de la tabla, se elimine el(los) valor(es) que cumpla(n) con la condición “Condition”. Nota Es importante destacar que la condición puede variar, puede ser de carácter sumamente complejo, una sub-consulta, una sentencia que involucre otras tablas.

Ejemplo 4 Si nos situamos temporalmente al final del ejemplo 1, con el error: ERROR: duplicate key value violates unique constraint "student2_pkey" DETAIL: Key(sid)=(1) already exists.

Al querer insertar a ‘Wilma’, es posible eliminar la fila correspondiente a ‘Betty’ y volver insertar ambas como se hizo en el ejemplo 2, sin la necesidad de borrar la tabla, crearla y agregar todo de nuevo: DELETE FROM Student_new WHERE sID = 1;

Si verificamos: SELECT * FROM Student_new;

la salida es: sid | sname | average ----+--------+---------

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 118 de 253

Lo cual permite eliminar la fila correspondiente a ‘Betty’ y dejar la tabla vacía. Posteriormente es posible comenzar a llenarla de nuevo mediante las últimas 2 consultas del ejemplo 2, es decir: INSERT INTO Student_new (sName, Average) VALUES ('Betty', 78); INSERT INTO Student_new (sName, Average) VALUES ('Wilma', 81);

Y verificando: SELECT * FROM Student_new;

la salida es: sid | sname | average ----+--------+--------1 | Betty | 78 2 | Wilma | 81

Ejemplo 5 Supongamos que ‘Wilma’ se enoja por el error de tipéo y desea salir del proceso de postulación. Es por ello que debe ser eliminada de la nueva planilla de estudiantes: DELETE FROM Student_new WHERE sID = 2;

RECAPITULACIÓN A continuación se expondrá un ejemplo que implique el uso de todos los comandos aprendidos en esta Lección.

Ejemplo extra Tomando en cuenta el ejemplo 5, supongamos que ‘Betty’ pasa a la etapa de postulaciones y decide postular a 2 Establecimientos educacionales. Postula a Ciencias e Ingeniería en Stanford y a Historia Natural en Berkeley, es aceptada en todo lo que ha postulado. La tabla Apply igual que la tabla Student: ya se había enviado sin posibilidad de modificar. Es por ello que se crea la tabla Apply_new, con las mismas características que Apply: CREATE TABLE Apply_new(sID INTEGER, cName VARCHAR(20), major VARCHAR(30), decision BOOLEAN, PRIMARY kEY(sID, cName, major));

INSERT INTO Apply_new (sID, cName, major, decision) VALUES (1, 'Stanford', Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 119 de 253

'science' , True); INSERT INTO Apply_new (sID, cName, major, decision) VALUES (1, 'Stanford', 'engineering' , True); INSERT INTO Apply_new (sID, cName, major, decision) VALUES (1, 'Berkeley', 'natural history' , True);

Verificando la salida: SELECT * FROM Apply_new;

se tiene que: sid | cname | major | decision -----+----------+-----------------+---------1 | Stanford | science | t 1 | Stanford | engineering | t 1 | Berkeley | natural history | t (3 rows)

Supongamos ahora que hubo un error en la gestión de papeles respecto a la postulación a ingeniería: Básicamente ‘Betty’ no quedó aceptada en dicha mención, por lo tanto se debe modificar UPDATE Apply_new SET decision = false WHERE sid = 1 and cname = 'Stanford' and major = 'engineering';

Lo que resulta en el cambio en la tabla: sid | cname | major | decision -----+----------+-----------------+---------1 | Stanford | science | t 1 | Berkeley | natural history | t 1 | Stanford | engineering | f (3 rows)

Supongamos ahora que ‘Betty’, por suerte, es una persona distraída y debido a sus enormes ganas de entrar a ciencias no se percata del error. El responsable de error, por temor a poner en juego su reputación, decide eliminar el registro de la postulación, en lo que considera un plan maestro, pues la tabla Apply_new no cuenta con un contador serial que pudiese causar algún conflicto. DELETE FROM Apply_new WHERE sid = 1 and cname = 'Stanford' and major = 'engineering';

Lo que resulta en el cambio en la tabla:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 120 de 253

sid | cname | major | decision -----+----------+-----------------+---------1 | Stanford | science | t 1 | Berkeley | natural history | t (2 rows)

y en la impunidad del responsable.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 121 de 253

Lección XV - Teoría del diseño Relacional: Información General Diseñar un esquema de base de datos 

Por lo general existen muchos diseños posibles.



Algunos son (mucho) mejor que otros.



¿Cómo elegir?.

El diseño de una base de datos relacional puede abordarse de dos formas: 

Obteniendo el esquema relacional directamente: Objetos y reglas captadas del análisis del mundo real, representadas por un conjunto de esquemas de relación, sus atributos y restricciones de integridad.



Diseño del esquema conceptual: realizando el diseño del esquema “conceptual” de la BD (modelo E/R) y transformándolo a esquema relacional.

En los esquemas de bases de datos es posible encontrar anomalías que serán eliminadas gracias al proceso de normalización. Estas anomalías son: 

La redundancia de los datos: repetición de datos en un sistema.



Anomalías de actualización: inconsistencias de los datos como resultado de datos redundantes y actualizaciones parciales.



Anomalías de eliminación: pérdidas no intencionadas de datos debido a que se han borrado otros datos.



Anomalías de inserción: imposibilidad de adicionar datos en la base de datos debido a la ausencia de otros datos.

A continuación se muestra una tabla y luego el detalle de los problemas que presenta:

Nombre_autorCortázar, JulioRosasco, José LuisRosasco, José LuisColoane, FranciscoPaísArgChiChiChiCod_libro97860711107259789561224056956131 36699789563473308Titulo_libroCuentos Completos 1 Julio CortazarDonde Estas, ConstanzaHoy Día es MañanaGolfo De PenasEditorAlfaguaraZigZagAndrés BelloAlfaguaraDirección_editorialPadre Mariano 82Los Conquistadores 1700Ahumada 131Padre Mariano 82

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 122 de 253



Redundancia: cuando un autor tiene varios libros, se repite su país de origen.



Anomalías de modificación: Si se cambia la dirección de la editorial “Alfaguara”, se deben modificar dos filas. A priori no se puede saber cuántos autores tiene un libro. Los errores son frecuentes al olvidar la modificación de un autor.



Anomalías de inserción: Se desea ingresar a un autor sin libros. “Nombre_autor” y “Cod_libro” son campos claves, por lo que las claves no pueden ser valores nulos.

Al eliminar estas anomalías se asegura: 

Integridad entre los datos: consistencia de la información.

Otro ejemplo se muestra en la siguiente tabla: Aplicar(SSN, sNombre, cNombre, HS, HSciudad, hobby) Nota La notación que se utiliza en la tabla es: HS = high school (escuela secundaria).

123 Ann de PAHS (P.A) y GHS (P.A) juega tenis y toca la trompeta y postuló a Stanford, Berkeley y al MIT Los datos ingresados en la tabla podrían ser los que se muestran a continuación:

123123123..AnnAnnAnn..StanfordBerkeleyBerkeley..PAHSPAHSPAHSGHS .P.AP.AP.A..tenistenistrompeta.. 

Redundancia: captura información muchas veces como por ejemplo “123 Ann”, “PAHS”, “tenis” o “MIT”.



Anomalía de actualización: actualizar datos de diferente manera como “corneta” por “trompeta”.



Anomalía de eliminación: eliminación inadvertida de datos.

Una correcta forma de realizar la tabla anterior sin anomalías es: 

Estudiante(SSN, sNombre);



Aplicar(SSN, cNombre);



Escuela_secundaria(SSN, HS);



Ubicado(HS, HSciudad);



Aficiones(SSN, hobby);

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 123 de 253

Ejercicio Considere la posibilidad de una base de datos que contiene información sobre los cursos tomados por los estudiantes. Los estudiantes tienen un ID único de estudiante y nombre; cursos tienen un número único de curso y título, los estudiantes toman un curso de un año determinado y reciben una calificación. ¿Cuál de los siguientes esquemas recomiendan? a. Tomo(SID, nombre, cursoNum, título, año, calificación) b. Curso(cursoNum, título, año), Tomó(SID, cursoNum, calificación) c. Estudiante(SID, nombre), Curso(cursoNum, título), Tomo(SID, cursoNum, año, calificación) d. Estudiante(SID, nombre), Curso(cursoNum, título), Tomo(nombre, título, año, calificación) La alternativa correcta es la letra (c), puesto que en el enunciado se dice que existen estudiantes con un ID único, que en este caso será “SID” y un “nombre”; los cursos tienen un ID único que es “cursoNum” y un “titulo”, además que los estudiantes toman un curso en un año determinado “año” y reciben una calificación “grado”, pero el atributo “cursoNum” actúa como clave foránea de la tabla Curso con la cual se podrá obtener el titulo del curso y también debe poseer una clave primaria para poder identificar el curso tomado que será “SID”.

Diseño por descomposición 

Comienza con las “mega” relaciones que contienen todo.



Descomponer en partes más pequeñas, se obtienen mejores relaciones con la misma información.



¿Se puede descomponer automáticamente?

Descomposición automática: 

“Mega” relaciones + propiedades de los datos.



El sistema descompone basándose en las propiedades.



Conjunto final de relaciones satisface la forma normal. o

no hay anomalías, hay pérdida de información.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 124 de 253

Normalización Proceso que analiza las dependencias entre los atributos de una relación de tal manera de combinar los atributos, en entidades y asociaciones menos complejas y más pequeñas. Consiste en un conjunto de reglas denominadas Formas Normales (FN), las cuales establecen las propiedades que deben cumplir los datos para alcanzar una representación normalizada. En este paso se toma cada relación, se convierte en una entidad (relación o tabla) no normalizada y se aplican las reglas definidas para 1FN, 2FN, 3FN, Boyce Codd y 4FN.

Formas normales La siguiente imagen muestra los tres principales niveles que se utilizan en el diseño de esquemas de bases de datos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 125 de 253

El proceso de normalización es fundamental para obtener un diseño de base de datos eficiente. En una entidad no normalizada generalmente expresada en forma plana (como una tabla), es muy probable que existan uno o más grupos repetitivos, no pudiendo en ese caso ser un atributo simple su clave primaria. A continuación se dará una definición y un ejemplo de las formas normales:

Primera formal normal (1FN) Una tabla está normalizada o en 1FN, si contiene sólo valores atómicos en la intersección de cada fila y columna, es decir, no posee grupos repetitivos. Para poder cumplir con esto, se deben pasar a otra tabla aquellos grupos repetitivos generándose dos tablas a partir de la tabla original. Las tablas resultantes deben tener algún atributo en común, en general una de las tablas queda con una clave primaria compuesta. Esta forma normal genera tablas con problemas de redundancia, y por ende, anomalías de inserción, eliminación o modificación; la razón de esto es la existencia de lo que se denomina dependencias parciales.

Ejemplo Se dice que una tabla está encuentra en primera forma normal (1FN) si y solo si cada uno de los campos contiene un único valor para un registro determinado. Supongamos que deseamos realizar una tabla para guardar los cursos que están realizando los estudiantes de informática de la USM, podríamos considerar el siguiente diseño.

Código123NombrePatriciaMargaritaJoaoCursosEstructura de datosBases de datos, Teoría de sistemasEstructura de datos, Bases de datos Se puede observar que el registro 1 cumple con la primera forma normal, puesto que cada campo cumple con la condición de tener solo un dato, pero esta condición no se cumple con el registro 2 y 3, en el campo de Cursos, ya que en ambos existen dos datos. La solución a este problema es crear dos tablas del siguiente modo.

Tabla 1 Código123NombrePatriciaMargaritaJoao Tabla 2 Código12233CursosEstructura de datosBases de datosTeoría de sistemasEstructura de datosBases de datos

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 126 de 253

Como se puede comprobar, ahora todos los registros de las dos tablas cumplen con la condición de tener en todos sus campos un solo dato, por lo tanto la Tabla 1 y Tabla

2 están en primera forma normal.

Segunda forma normal (2FN) Una tabla está en 2FN, si está en 1FN y se han eliminado las dependencias parciales entre sus atributos. Una dependencia parcial se da cuando uno o más atributos que no son clave primaria, son sólo dependientes de parte de la clave primaria compuesta, o en otras palabras, cuando parte de la clave primaria determina a un atributo no clave. Este tipo de dependencia se elimina creando varias tablas a partir de la tabla con problemas: una con los atributos que son dependientes de la clave primaria completa y otras con aquellos que son dependientes sólo de una parte. Las tablas generadas deben quedar con algún atributo en común para representar la asociación entre ellas. Al aplicar esta forma normal, aún se siguen teniendo problemas de anomalías pues existen dependencias transitivas.

Ejemplo La segunda forma normal compara todos y cada uno de los campos de la tabla con la clave definida. Si todos los campos dependen directamente de la clave se dice que la tabla está en segunda forma normal. Se construye una tabla con los años que cada profesor ha estado trabajando en cada departamento de la USM.

Código_profesor12342Código_departamento63236NombreJavierLuisCeciliaN oraLuisDepartamentoElectrónicaEléctricaInformáticaEléctricaElectrónicaAños _trabajados3158220 La clave de esta tabla está conformada por el Código_profesor y Código_departamento, además se puede decir que está en primera forma normal, por lo que ahora la transformaremos a segunda forma normal. 

El campo Nombre no depende funcionalmente de toda la clave, solo depende de la clave Código_profesor.



El campo Departamento no depende funcionalmente de toda la clave, solo depende de la clave Código_departamento.



El

campo Años_trabajados si

depende

funcionalmente

de

las

claves Código_profesor y Código_departamento (representa los años trabajados de cada profesor en el departamento de la universidad).

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 127 de 253

Por lo tanto al no depender funcionalmente todos los campos de la tabla anterior no está en segunda forma normal, entonces la solución es la siguiente:

Tabla A Código_profesor1234NombreJavierLuisCeciliaNora Tabla B Código_departamento236DepartamentoInformáticaEléctricaElectrónica Tabla C Código_empleado12342Código_departamento63236Años_trabajados3158220 Se puede observar que la Tabla A tiene como índice la clave Código_empleado, Tabla

B tiene

como

clave Código_departamento y

laTabla

C que

tiene

como

clave

compuesta Código_empleado y Código_departamento, encontrándose finalmente estas tablas en segunda forma normal.

Tercera forma normal (3FN) Una tabla está en 3FN, si está en 2FN y no contiene dependencias transitivas. Es decir, cada atributo no primario depende solo de la clave primaria, no existiendo dependencias entre atributos que no son clave primaria. Este tipo de dependencia se elimina creando una nueva tabla con el o los atributo(s) no clave que depende(n) de otro atributo no clave, y con la tabla inicial, la cual además de sus propios atributos, debe contener el atributo que hace de clave primaria en la nueva tabla generada; a este atributo se le denomina clave foránea dentro de la tabla inicial (por clave foránea se entiende entonces, a aquel atributo que en una tabla no es clave primaria, pero sí lo es en otra tabla).

Ejemplo Se dice que una tabla está en tercera forma normal si y solo si los campos de la tabla dependen únicamente de la clave, dicho en otras palabras los campos de las tablas no dependen unos de otros. Tomando como referencia el ejemplo de la primera forma normal, un alumno solo puede tomar un curso a la vez y se desea guardar en que sala se imparte el curso.

Código123NombrePatriciaMargaritaJoaoCursoEstructura de datosTeoría de sistemasBases de datosSalaABC Veamos las dependencias de cada campo respecto a la clave: Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 128 de 253

  

Nombre depende directamente del Código. Curso depende de igual manera del Código. La Sala depende del Código, pero está más ligado al Curso que el alumno está realizando.

Es por este último punto que se dice que la tabla no está en 3FN, pero a continuación se muestra la solución:

Tabla A Código123NombrePatriciaMargaritaJoaoCursoEstructura de datosTeoría de sistemasBases de datos Tabla B CursoEstructura de datosTeoría de sistemasBases de datosSalaABC

Boyce-Codd forma normal (FNBC) Es una versión ligeramente más fuerte de la Tercera forma normal (3FN). La forma normal de Boyce-Codd requiere que no existan dependencias funcionales no triviales de los atributos que no sean un conjunto de la clave candidata. En una tabla en 3FN, todos los atributos dependen de una clave. Se dice que una tabla está en FNBC si y solo si está en 3FN y cada dependencia funcional no trivial tiene una clave candidata como determinante.

Dependencias funcionales y FNBC Aplicar(SSN, sNombre, cNombre) 

Redundancia, anomalías de actualización y eliminación.



Almacenamiento del SSN-sNombre para una vez por cada universidad.

Dependencia funcional SSN-> sNombre 

SSN siempre tiene el mismo sNombre



En caso de almacenar sNombre cada SSN sólo una vez

Boyce-Codd forma normal si a-> b entonces a es una clave Descomponer: Estudiante(SSN, sNombre) Aplicar(SSN, cNombre) Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 129 de 253

siendo finalmente SSN una clave primaria.

Ejemplo Tenga en cuenta la relación Tomo(SID, nombre, cursoNum, título). Los estudiantes tienen el carné de estudiante y un nombre único, los cursos tienen un número único curso y título. Cada tupla de la relación codifica el hecho de que un estudiante dado tomó el curso. ¿Cuáles son todas las dependencias funcionales para la relación tomó? a. sID → cursoNum b. sID → nombre, cursoNum → titulo c. nombre → sID, titulo → cursoNum d. cursoNum → sID La respuesta correcta es la alternativa (b), puesto que un id de estudiante que único “sID”, está asignado a solo un estudiante y un id del curso que es único “cursoNum” tiene asignado un título. Las otras alternativas no son porque, la alternativa (a) dice un estudiante sólo puede tomar un curso, la alternativa (c) dice que los nombres de los estudiantes y los títulos de los cursos son únicos y la alternativa (d) dice que los cursos sólo pueden ser tomados por un estudiante.

Cuarta forma normal (4FN) La 4NF se asegura de que las dependencias multivaluadas independientes estén correcta y eficientemente representadas en un diseño de base de datos. La 4NF es el siguiente nivel de normalización después de la forma normal de Boyce-Codd (BCNF). Una tabla está en 4NF si y solo si esta en Tercera forma normal o en BCNF y no posee dependencias multivaluadas no triviales. La definición de la 4NF confía en la noción de una dependencia multivaluada. Una tabla con una dependencia multivaluada es donde hay una existencia de dos o más relaciones independientes de muchos a muchos que causa redundancia; que es suprimida por la cuarta forma normal.

Dependencias multivaluadas y 4FN Aplicar(SSN, cNombre, HS) 

Redundancia, anomalías de actualización y eliminación.



Efecto multiplicativo: C colegios o H escuelas secundarias, por lo que se generarán “C * H” ó “C + H” tuplas.



No es dirigida por BCNF: No hay dependencias funcionales.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 130 de 253

La dependencia multivalor SSN->>cNombre ó SSN->>HS 

SSN cuenta todas las combinaciones de cNombre con HS.



En caso de almacenar cada cName y HS, para obtener una vez un SSN.

Nota La flecha ->> significa muchos

Cuarta Forma Normal si A->>B entonces A es una clave Descomponer: Aplicar(SSN, cNombre) Escuela_secundaria(SSN, HS)

Ejemplo 1 Tenga en cuenta la relación Informacion_estudiante(SID, dormitorio, cursoNum). Los estudiantes suelen vivir en varios dormitorios y tomar muchos cursos en la universidad. Supongamos que los datos no capta en que dormitorio(s) un estudiante estaba en la hora de tomar un curso específico, es decir, todas las combinaciones de cursos dormitorio se registran para cada estudiante. ¿Cuáles son todas las dependencias para la relación Informacion_estudiante? a. sID->>dormitorio b. sID->>cursoNum c. sID->>dormitorio, sID->>cursoNum d. sID->>dormitorio, sID->>cursoNum, dormitorio->>cursoNum La alternativa correcta es (c), puesto que para un estudiante hay muchos dormitorios y un estudiante puede tomar muchos cursos. La alternativa (a) y (b) ambos omiten una dependencia, la alternativa (d) dice que todos los estudiantes de cada dormitorio toman el mismo conjunto de cursos.

Ejemplo 2 Una tabla está en cuarta forma normal si y sólo si para cualquier combinación clavecampo no existen valores duplicados.

Geometría FiguraCuadradoCuadradoCuadradoCírculoCírculoCírculoColorRojoAzulAzul BlancoAzulAzulTamañoGrandeGrandeMedianoMedianoPequeñoMediano

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 131 de 253

Vamos a comparar el atributo clave Figura con Tamaño, se puede notar que Cuadrado Grande está repetido; de igual manera Círculo Azul, entre otros registros. Son estas repeticiones que se deben evitar para tener una tabla en 4FN. La solución a la tabla anterior es la siguiente:

Tamaño FiguraCuadradoCuadradoCírculoCírculoTamañoGrandeMedianoMedianoPequ eñ o Color FiguraCuadradoCuadradoCírculoCírculoColorRojoAzulBlancoAzul

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 132 de 253

Lección XVI - Teoría del Diseño Relacional: Dependencia Funcional Dependencia Funcional Dados dos atributos A y B de una relación R, se dice que B depende funcionalmente de A, si cada valor de A tiene asociado un único valor de B. En otras palabras: si en cualquier instante, conocido el valor de A podemos conocer el valor de B. Tanto A como B pueden ser conjuntos de atributos. La dependencia funcional se simboliza del siguiente modo:

R.A→R.B Por

ejemplo

en

la

relación

R(Nif−−−−,

Nombre,

Dirección),

los

atributos Nombre y Dirección dependen funcionalmente de Nif. Nif→(Nombre,Dirección) Las dependencias funcionales son generalmente útiles para: 

Almacenamiento de datos - compresión



Razonamiento acerca de las consultas - Optimización

Ejemplo 1: Estudiante(SSN, sNombre, dirección, HScodigo, HSnombre, HSciudad, GPA, prioridad) Aplicar(SSN, cNombre, estado, fecha, principal) Supongamos que la prioridad es determinada por GPA

GPA > 3,8 prioridad = 1 3,3 < GPA C como máximo 9 valores diferentes para C con un máximo de 3 valores diferentes para D, por C,D -> E hay en la mayoría de 9 * 3 = 27 valores diferentes para E. Las dependencias funcionales para las tablas son: Student(SSN, sNombre, dirección, HScodigo, HSnombre, HSciudad, GPA, prioridad)

SSN→sNombre SSN→dirección HScodigo→HSnombre,HSciudad Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 134 de 253

HSnombre,HSciudad→HScodigo SSN→GPA GPA→prioridad SSN→prioridad Apply(SSN, cNombre, estado, fecha, principal)

cNombre→fecha SSN,cNombre→principal SSN→estado Ejemplo 3 Para la relación Aplicar(SSN, cNombre, estado, fecha, principal), lo que en el mundo real es capturado por restricción SSN,fecha -> cNombre? a. Un estudiante sólo puede aplicar a un colegio. b. Un estudiante puede aplicar a cada colegio una sola vez. c. Un estudiante debe aplicar a todos los colegios en la misma fecha. d. Toda solicitud de un estudiante a un colegio específico debe estar en la misma fecha. La alternativa correcta es (d), puesto que cualquiera de las dos tuplas con el mismo SSN-cNombre combinación también deben tener la misma fecha. Así que si un estudiante (SSN) se aplica a una universidad (cNombre) más de una vez, deben estar en la misma fecha.

Dependencias funcionales y llaves 

Relación sin duplicados



Supongamos A¯¯¯ todos los atributos

Dependencia funcional Trivial

A¯¯¯→B¯¯¯B¯¯¯⊆A Dependencia funcional no Trivial

A¯¯¯→B¯¯¯B¯¯¯⊈A

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 135 de 253

Dependencia funcional completamente Trivial

A¯¯¯→B¯¯¯A¯¯¯∩B¯¯¯=⊘

Reglas para las dependencias funcionales 

Regla de la división

A¯¯¯→B1,B2,…,Bn A¯¯¯→B1A¯¯¯→B2… 

¿Se puede también dividir a la izquierda?

A1,A2,…,An→B¯¯¯ A1→B¯¯¯A2→B¯¯¯… No se puede realizar una división a la izquierda 

Combinación de las reglas

A¯¯¯→B1 A¯¯¯→B2 A¯¯¯→B… A¯¯¯→Bn ⇒A¯¯¯→B1,B2,…,Bn 

Reglas de dependencia trivial

A¯¯¯→B¯¯¯B¯¯¯⊆A A¯¯¯→B¯¯¯entoncesA¯¯¯→A¯¯¯∪B¯¯¯ A¯¯¯→B¯¯¯entoncesA¯¯¯→A¯¯¯∩B¯¯¯ 

Regla transitiva

A¯¯¯→B¯¯¯B¯¯¯→A¯¯¯thenA¯¯¯→C¯¯¯ Cierre de atributos Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 136 de 253



Dada una relación, dependientemente funcional, un conjunto de atributos



Encuentre todos los B de forma que A¯¯¯→B

A¯¯¯

Ejemplo 4 Un ejemplo de cierre de atributos es: Estudiante(SSN, sNombre, dirección, HScodigo, HSnombre, HSciudad, GPA, prioridad)

SSN→sNombre, dirección, GPA GPA→prioridad HScodigo→HSnombre, HSciudad {SSN, HScodigo}+→(todos los atributos)(llave) {SSN, HScodigo, sNombre, dirección, GPA, prioridad, HSnombre, HSciudad}

Clausura y llaves 

¿Es A¯¯¯ una llave para R?

Calcular A+¯¯¯¯¯ Si = todos atributos, entonces A¯¯¯ es una llave. 

¿Cómo podemos encontrar todas las llaves dado un conjunto de dependencias funcionales?

Considerar cada subconjunto A¯¯¯ de los atributos.

A+→ todos los atributos es llave

Ejemplo 5 Tenga en cuenta la relación R (A, B, C, D, E) y supongamos que tenemos las dependencias funcionales:

AB→C AE→D D→B

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 137 de 253

¿Cuál de los siguientes pares de atributos es una clave para R? a. AB b. AC c. AD d. AE La alternativa correcta es (d), puesto que {AB}+ = {ABC}; {AC}+ = {AC}; {AD}+ = {ABCD}; {AE}+ = {ABCDE}.

Especificación funcionalmente dependiente para una relación S1 y S2 conjunto funcionalmente dependiente. S2 “sigue de” S1 si cada instancia de relación satisfacer S1 si también satisface S2 S2: {SSN, prioridad} S1: {SSN → GPA, GPA → prioridad} Nota Se observa que S1 satisface S2

Ejemplo 6 Consideremos la relación R (A, B, C, D, E) y el conjunto de dependencias funcionales S1 = {AB

→ C, AE → D, D → B}.

¿Cuál de los siguientes conjuntos de S2 FD NO se deduce de S1? a. S2 = {AD → C} b. S2 = {AD → C, AE c. d.

→ B} S2 = {ABC → D, D → B} S2 = {ADE → BC}

La alternativa correcta es (c), puesto que el uso de las FDs en S1: {AD}+ = {ABCD}; {AE}+ = {ABCDE}; {ABC}+ = {ABC}; {D}+ = {B}; {ADE}+ = {ABCDE}

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 138 de 253

Lección XVII - Teoría del diseño relacional: Forma normal Boyce-Codd Conceptos previos Tipos de claves (llaves) Llave primaria, clave candidata y superclave se refieren a un atributo o un conjunto de atributos que permiten identificar unívocamente un registro. Es decir que no existe en esa relación un registro que tenga el mismo valor en ese (esos) atributo(s). 

Superclave: (llave compuesta) es un conjunto de uno o más atributos que, tomados colectivamente, permiten identificar unívocamente cada registro de la tabla. Es un subconjunto de atributos que permite distinguir de forma única cada una de las tuplas. Si se une otro atributo al subconjunto anterior, el resultado seguirá siendo una superclave.

Ejemplo El atributo idCliente de la relación Cliente es suficiente para distinguir una fila de un Cliente de las otras. Así, idCliente es una superclave. Análogamente, la combinación de nombre y idCliente es una superclave del conjunto de relación Cliente. El atributonombre de cliente no es una superclave, porque varias personas podrían tener el mismo nombre. El concepto de una superclave no es suficiente para lo que aquí se propone, ya que, como se ha visto, una superclave puede contener atributos innecesarios. Si K es una superclave, entonces también lo es cualquier superconjunto de K. A menudo interesan las superclaves tales que los subconjuntos propios de ellas no son superclave. Tales superclaves mínimas se llaman claves candidatas. 

Clave candidata: Cuando una superclave, se reduce al mínimo de atributos que la componen, pero aún así sirve para identificar la tupla, entonces ésta pasa a ser una clave candidata. La clave (o llave) candidata es solo a nivel conceptual. En una relación más de un atributo podría llegar a ser llave primaria, pues pueden identificar a cada tupla, es decir que no existen dos valores para ese atributo que sean iguales. Dichos atributos que se proponen a reconocer una tupla, se denominan clave candidata porque son candidatos a ser clave primaria.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 139 de 253

Ejemplo Es posible que conjuntos distintos de atributos pudieran servir como clave candidata. Supóngase que una combinación de nombre ydirección es suficiente para distinguir entre

los

miembros

del

conjunto

conjuntos {idCliente} y{nombre,

de

calle} son

atributos idCliente y nombre juntos

puedan

la

relación Cliente.

claves distinguir

candidatas. las

tuplas

Entonces,

los

Aunque

los

de Cliente,

su

combinación no forma una clave candidata, ya que el atributo idCliente por sí solo es una clave candidata. 

Clave primaria: Una vez que se elige cual de los atributos de la clave candidata será el que permitirá identificar cada registro en una tabla, dicho atributo se pasa a llamar llave primaria. Se puede decir que la clave primaria es una clave candidata, elegida por el diseñador de la base de datos, para identificar unívocamente las tuplas.

Nota Otro concepto que se utilizará es el de dependencia funcional(DF) que se puede repasar en la Lección16

Forma normal Boyce-Codd La definición original de la 3FN no trataba satisfactoriamente el caso de una relación que tenía dos o más claves candidatas compuestas y que tenían al menos un atributo en común. Es por esto que se crea la forma normal de Boyce-codd(FNBC), que es una forma normal estrictamente más sólida que la 3FN, la cual atiende los caso que no cubre correctamente la 3FN. Nota No es tan frecuente encontrar relaciones con más de una claves candidatas compuestas y con al menos un atributo en común. Para una relación en donde no suceden, las 3FN y FNBC son equivalentes.

Definición Un esquema de relación R está en FNBC si, para todas las dependencias funcionales de la forma A->B, donde A y B son subconjuntos de R, se cumplen las siguientes condiciones:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 140 de 253





A->B es una dependencia funcional (DF) trivial: (B es subconjunto de A). Entonces se mantiene la condición de FNBC, debido a que sólo una DF no trivial puede violar esta condición. A es una superclave del esquema R: Si se tiene A -> B, pero no B-> A, entonces A es la superclave y cada dependencia no trivial contiene A en la izquierda, por tanto no hay violación a la condición FNBC.



Toda dependencia funcional trivial tiene una clave candidata como su determinante.

Un diseño de base de datos está en FNBC si cada miembro del conjunto de esquemas de relación que constituye el diseño está en FNBC. Vale la pena señalar que la definición FNBC es conceptualmente más simple que la definición anterior de la 3FN, ya que no hace referencias explícitas a la primera y segunda formas normales como tales, ni al concepto de dependencia transitiva. Además, aunque (como ya indicamos) la FNBC es estrictamente más sólida que la 3FN, se sigue dando el caso que cualquier relación dada puede descomponerse sin pérdida en una colección equivalente de relaciones FNBC.

Descomposición para lograr la FNBC En algunas ocasiones con la elección de descomposiciones adecuadas, se puede romper cualquier esquema de relación en una colección de subconjuntos de sus atributos con las siguientes propiedades importantes: 1. Estos subconjuntos son los esquemas de relaciones en FNBC. 2. Los datos de la relación original se representa fielmente por los datos en las relaciones resultantes de la descomposición. A grandes rasgos, tenemos que ser capaces de reconstruir la relación original con exactitud a partir de las relaciones descompuestas. Lo anterior sugiere que tal vez lo único que se debe hacer es romper un esquema de relación en subconjuntos de dos atributos, y el resultado estará en FNBC. Sin embargo, tal descomposición arbitraria puede no satisfacer la condición (2). De hecho, se debe ser más cuidadosos y utilizar el DF debido a guiar la descomposición. La estrategia de descomposición

que

vamos

a

seguir

es

trivial A1A2...An−>B1B2...Bm que viola FNBC, es decir,

buscar

un

A1,A2,...,An no

DF

es una

superclave. Vamos a añadir a la derecha tantos atributos como son funcionalmente determinado por A1,A2,...,An . Este paso no es obligatorio, pero a menudo se reduce la cantidad de trabajo realizado, y lo vamos a incluir en nuestro algoritmo. La Figura

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 141 de 253

que se muestra a continuación ilustra cómo los atributos se dividen en dos esquemas de relación que se superponen. Uno es de todos los atributos involucrados en la violación de DF, y el otro es el lado izquierdo de la DF además de todos los atributos que no participan en la DF, es decir, todos los atributos excepto los B’s que no son A’s.

Ejemplo 1 Película: título | año | duración | género | director | actor --------------+------+----------+--------+-----------------+------------Forrest Gump | 1994 | 142 | Drama | Robert Zemeckis | Tom Hanks Forrest Gump | 1994 | 142 | Drama | Robert Zemeckis | Robin Wright Forrest Gump | 1994 | 142 | Drama | Robert Zemeckis | Gary Sinise The Godfather | 1972 | 175 | Crime | Mario Puzo | Marlon Brando Matrix | 1999 | 136 | Action | Wachowski | Keanu Reeves Matrix | 1999 | 136 | Action | Wachowski | Laurence Fishburne

La Relación Películas no es en FNBC. Para ver por qué, primero tenemos que determinar qué conjuntos de atributos son claves. Nuestra hipótesis será que {título, año,

actor} son en conjunto una clave, para demostrar que es una clave en primer lugar, tenemos que verificar que identifica inequívocamente una tupla. Para ello supongamos que dos tuplas tienen igual valor en estos tres atributos:{título, año, actor}. Al ser la misma película, los otros atributos {duración, género, director} serán iguales también. Así, dos tuplas diferentes no pueden concordar en {título, año, actor} pues en realidad sería la misma tupla. Ahora,

debemos

argumentar que ningún subconjunto propio de {título,

año,

actor} determina funcionalmente a todos los demás atributos. Primero se observa que el título y el año no determinan a actor, porque muchas películas tienen más de un actor. Por lo tanto, {título, año} no es una clave. {año, actor} no es una clave, porque podríamos tener un actor en dos películas en el mismo año, por lo tanto: actor año -

> título no es un DF. Asimismo, sostenemos que {título, actor} no es una clave,

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 142 de 253

porque dos películas con el mismo título, realizada en diferentes años, de vez en cuando tienen un actor en común. Como {título, año, actor} es una clave, cualquier conjunto de atributos que contienen estos tres es una superclave. Los mismos argumentos anteriores se pueden utilizar para explicar por qué no existe un conjunto de atributos que no incluya a los tres atributos{título, año, actor} que pudiera ser una superclave. Por lo tanto, afirmamos que {título, año, actor} es la única clave para Películas. Sin embargo, tenga en cuenta:

título año-> duración género actor Desafortunadamente, el lado izquierdo de la anterior DF no es una superclave. En particular, se sabe que el título y el año no determinan funcionalmente el atributo actor. Por lo tanto, la existencia de esta DF viola la condición FNBC y nos dice que Películas no está en FNBC. Por otro lado: Películas2: título | año | duración | género | director --------------+------+----------+--------+----------------Forrest Gump | 1994 | 142 | Drama | Robert Zemeckis The Godfather | 1972 | 175 | Crime | Mario Puzo Matrix | 1999 | 136 | Action | Wachowski

año título -> duración género director La única clave para Películas2 es {título, año}. Por otra parte, la única DF no trivial debe tener por lo menos título y año en el lado izquierdo, y por lo tanto su lado izquierdo debe ser superclave. Por lo tanto, Películas2 está en FNBC.

Ejemplo 2 Se tiene un esquema de relación y sus respectivas dependencias funcionales: 

cliente = (nombreC, dirección, ciudadC)

nombreC -> dirección ciudad 

sucursal = (nombreS, activo,ciudadS)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 143 de 253

nombreS -> activo ciudadS Puede afirmarse que cliente está en FNBC. Obsérvese que una clave candidata para la relación es nombreC. Las únicas dependencias funcionales no triviales que se cumplen en cliente tienen a nombreC a la izquierda de la flecha. Dado que nombreC es una clave candidata, las dependencias funcionales con nombreC en la parte izquierda no violan la definición de FNBC. De manera parecida, se puede demostrar fácilmente que relación sucursal está en FNBC.

Ejemplo 3 Clases: ID | asignatura | profesor ----+------------+---------121 | Lenguaje | Paul 121 | Matemáticas| David 345 | Lenguaje | Paul 567 | Matemáticas| Robert 567 | Lenguaje | Julia 563 | Matemáticas| Robert

La tabla está en 3FN pues no posee dependencias transitivas, pero no está en forma de Boyce - Codd, ya que (ID, asignatura)->profesor y profesor->asignatura. En este caso la redundancia ocurre por mala selección de clave. La redundancia de la asignatura es completamente evitable. La solución sería: ID | profesor ----+---------121 | Paul 121 | David 345 | Paul 567 | Robert 567 | Julia 563 | Robert asignatura | profesor -----------+---------Lenguaje | Paul Matemáticas| David Matemáticas| Robert Lenguaje | Julia

En las formas de Boyce-Codd hay que tener cuidado al descomponer ya que se podría perder información por una mala descomposición.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 144 de 253

Lección XVIII - Teoría de Diseño Relacional: Dependencias Multivaluadas (4ta forma normal) Dependencias multivaluadas Introducción Una dependencia multivaluada es una afirmación donde dos atributos o conjuntos de atributos son independientes uno de otro. Si A implica B, las dependencias funcionales prohíben que haya dos tuplas con igual valor de A y distinto valores de B, es decir A tiene asociado un único valor de B. Al contrario las dependencias multivaluadas permiten que un mismo valor de A tenga asociado diferente valor de B, pero exige que estén presentes en la relación de una forma determinada. Por este motivo, las dependencias funcionales se conocen también como dependencias de generación de igualdad y las dependencias multivaluadas se denominandependencias de generación de tuplas.

Atributo de independencia y redundancia En bases de datos, la redundancia hace referencia al almacenamiento de los mismos datos varias veces en diferentes lugares. Esto puede traer problemas como incremento en el procesamiento, desperdicio de espacio de almacenamiento e inconsistencia de datos. Si una base de datos está bien diseñada, debería haber mínima redundancia de datos, es decir una redundancia de datos controlada, que se emplea para mejorar el rendimiento en las consultas a las bases de datos.

Ejemplo Suponga que se tiene información acerca de nombres de cursos, profesores y textos. La tupla indica que dicho curso puede ser enseñado por cualquiera de los profesores descritos y que utiliza como referencias todos los textos especificados. Para un curso dado, puede existir cualquier número de profesores y cualquier cantidad de textos correspondientes. Los profesores y los textos son independientes entre sí; es decir, independientemente de quién imparta el curso, se utilizan los mismos textos. curso | profesor | texto ---------------+----------+---------------------------Base de datos | Ullman | A First Course in Database

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 145 de 253

Base de datos Base de datos Base de datos Programación Programación Programación

| | | | | |

Ullman Widom Widom Ullman Ullman Ullman

| | | | | |

Database System Concepts A First Course in Database Database System Concepts Rapid GUI Programming Learning Python Python Algorithms

Se puede observar que el ejemplo involucra una buena cantidad de redundancia, la cual conduce a algunas anomalías de actualización. Por ejemplo, para agregar la información que el curso de Base de datos puede ser impartido por un nuevo profesor, llamado Hetland, es necesario insertar dos nuevas tuplas; una para cada uno de los textos. curso | profesor | texto ---------------+----------+---------------------------Base de datos | Ullman | A First Course in Database Base de datos | Ullman | Database System Concepts Base de datos | Widom | A First Course in Database Base de datos | Widom | Database System Concepts Base de datos | Hetland | A First Course in Database Base de datos | Hetland | Database System Concepts Programación | Ullman | Rapid GUI Programming Programación | Ullman | Learning Python Programación | Ullman | Python Algorithms

Los problemas en cuestión son generados por el hecho que los profesores y los textos son completamente independientes entre sí. La existencia de relaciones con la problemática de la Forma Normal de Boyce-Codd (FNBC) como la del ejemplo llevaron a presentar la noción de las dependencias multivaluadas. Las dependencias multivaluadas son una generalización de las dependencias funcionales, en el sentido de que toda dependencia funcional(DF) es una dependencia multivaluada (DMV), aunque lo opuesto no es cierto (es decir, existen DMVs que no son DFs).

Definición formal Sea R una relación y sean A, B y C subconjuntos de los atributos de R. Entonces decimos que B es multidependiente de A, si y solamente si en todo valor válido posible de R, el conjunto de valores B que coinciden con un determinado par (valor A, valor C) depende sólo del valor de A y es independiente del valor de C.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 146 de 253

Es fácil mostrar que dado R{A,B,C}, A->->B es válida si y solamente si también es válida A->->C. Las dependencias multivaluadas siempre van en pares. Por esta razón, es común representar ambas en un solo enunciado de esta manera: A->->B|C

A partir de la definición de dependencia multivaluada se puede obtener la regla siguiente: Si A->B, entonces A->->B. En otras palabras, cada dependencia funcional es también una dependencia multivaluada. Las dependencias multivaluadas se utilizan de dos maneras: 1. Para verificar las relaciones y determinar si son legales bajo un conjunto dado de dependencias funcionales y multivaluadas. 2. Para especificar restricciones del conjunto de relaciones legales; de este modo, sólo habrá que preocuparse de las relaciones que satisfagan un conjunto dado de dependencias funcionales y multivaluadas.

Cuarta Forma Normal La Cuarta Forma Normal (4FN) tiene por objetivo eliminar las dependencias multivaluadas. La 4FN asegura que las dependencias multivaluadas independientes estén correcta y eficientemente representadas en un diseño de base de datos. La 4FN es el siguiente nivel de normalización después de la Forma Normal de Boyce-Codd (FNBC).

Definición 

Una relación está en 4FN si y sólo si, en cada dependencia multivaluada A->>B no trivial, A es clave candidata. Una dependencia multivaluada A->->B es

trivial cuando B es parte de A. Esto sucede cuando A es un conjunto de atributos, y B es un subconjunto de A. Nota Si una relación tiene más de una clave, cada una es una clave candidata. Una de ellas es arbitrariamente designada como clave primaria, el resto son secundarias.

Es otras palabras una relación está en 4FN si esta en Tercera Forma Normal o en FNBC y no posee dependencias multivaluadas no triviales. Como se mencionó, una relación Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 147 de 253

posee una dependencia multivaluada cuando la existencia de dos o más relaciones independientes muchos a muchos que causan redundancia; y es esta redundancia la que es suprimida por la Cuarta Forma Normal.

Ejemplo 1 Consideremos nuevamente el ejemplo anterior de cursos, profesores y textos. Se consigue una mejora si se descompusiera en sus dos proyecciones: Profesores (curso,profesor) y Textos (curso,texto). Profesores: curso | profesor ---------------+---------Base de datos | Ullman Base de datos | Widom Programación | Ullman Textos: curso | texto ---------------+----------------------------Base de datos | A First Course in Database Base de datos | Database System Concepts Programación | Rapid GUI Programming Programación | Learning Python Programación | Python Algorithms

Para agregar la información que el curso de Base de datos puede ser impartido por un nuevo profesor, sólo tenemos que insertar una tupla en la relación Profesores: Profesores: curso | profesor ---------------+---------Base de datos | Ullman Base de datos | Widom Base de datos | Hetland Programación | Ullman

También se observa que se puede recuperar la relación inicial al juntar nuevamente Profesores y Textos, de manera que la descomposición es sin pérdida. En este ejemplo hay dos DMVs válidas: CURSO ->-> PROFESOR

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 148 de 253

CURSO ->-> TEXTO

La primera DMV se lee como “Profesor es multidependiente de Curso” o manera equivalente, “Curso multidetermina a Profesor”.

Ejemplo 2 Se tiene una relación entre estudiantes, ramo y deporte. Los estudiantes pueden inscribirse en varios ramos y participar en diversos deportes. Esto quiere decir que el atributo sid no será único, de esta forma la única clave candidata posible es la combinación de los atributos (sid, ramo, deporte). El estudiante 1 tiene los ramos física y programación, participa en natación y tenis. El estudiante 2 sólo tiene el ramo matemáticas y participa en voleibol. sid | ramo | deporte ----+--------------+-----------1 | física | natación 1 | programación | natación 1 | física | tenis 1 | programación | tenis 2 | matemáticas | voleibol

La relación entre sid y ramo no es una dependencia funcional porque los estudiantes pueden tener distintos ramos. Un valor único de sid puede poseer muchos valores de ramo. Esto también se aplica a la relación entre sid y deporte. Se puede notar entonces que tal dependencia por atributos es una dependencia multivaluada. Se aprecia la redundancia en el ejemplo pues el estudiante 1 tiene cuatros registros. Cada uno de los cuales muestra uno de sus ramos junto con uno de sus deportes. Si los datos se almacenaran con menos filas: si hubiera sólo dos tuplas, uno para física y natación y uno para programación y tenis, las implicaciones serían engañosas. Parecería que el estudiante 1 sólo nadó cuando tenía física como ramo y jugó tenis sólo cuando tenía programación como ramo. Esa interpretación no es lógica. Sus ramos y sus deportes son independientes entre sí. Para prevenir tales engañosas conclusiones se almacenan todas las combinaciones de ramos y deportes. Si el estudiante 1 decide que quiere inscribirse en fútbol, se deben agregar dos tuplas con el fin de mantener la consistencia en los datos, se debe agregar una fila para cada uno de sus ramos, como en se muestra a continuación: sid | ramo | deporte ----+--------------+-----------1 | física | fútbol 1 | programación | fútbol Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 149 de 253

1 1 1 1 2

| física | natación | programación | natación | física | tenis | programación | tenis | matemáticas | voleibol

Esta relación está en FNBC (2FN porque todo es clave primaria; 3FN porque no tiene dependencias transitivas; y FNBC porque no tiene determinantes que no son claves). A pesar de esto se aprecia esta anomalía de actualización, pues hay que hacer demasiadas actualizaciones para realizar un cambio en los datos. Lo mismo ocurre si un estudiante se desea inscribir un nuevo ramo. También existe anomalía si un estudiante des-inscribe un ramo pues se deben eliminar cada uno de los registros que contienen tal materia. Si participa en cuatro deportes, habrá cuatro tuplas que contengan el ramo que ha dejado y deberán borrarse las cuatro tuplas. Para evitar tales anomalías se construyen dos relaciones, donde cada una almacena datos para solamente uno de los atributos multivaluados. Las relaciones resultantes no tienen anomalías: Ramos: sid | ramo ----+------------1 | física 1 | programación 2 | matemáticas Deportes: sid | deporte ----+---------1 | fútbol 1 | natación 1 | tenis 2 | voleibol

A partir de estas observaciones, se define la 4FN: Una relación está en 4FN si está en FNBC y no tiene dependencias multivaluadas.

Ejemplo 3 Se tiene una tabla de Agenda con atributos multivaluados: Agenda(nombre, teléfono, correo)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 150 de 253

Se buscan las claves y las dependencias. Las claves candidatas deben identificar de forma unívoca cada tupla. De modo los tres atributos deben formar la clave candidata. Pero las dependencias que se tienen son: nombre ->-> teléfono nombre ->-> correo

Y nombre no es clave candidata de esta relación, por lo que se debe separar esta relación en 2 relaciones:

Teléfonos(nombre,teléfono) Correos(nombre,correo) Ahora en las dos relaciones se cumple la 4FN. Nota De manera general una relación se separa en tantas relaciones como atributos multivaluados tenga.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 151 de 253

Lección XIX - Lenguaje de modelado unificado: UML modelado de datos Dentro del modelado de BD Relacionales, los métodos más conocidos son los diagramas de Entidad-Relación (ER), vistos en la primera semana, y el Lenguaje de Modelado Unificado (UML, por sus siglas en inglés). Ambos comparten la característica de ser gráficos; es decir que UML, al igual que ER está compuesto por “símbolos” bajo una serie de reglas. Además, ambos comparten la cualidad de que pueden ser traspasados a lenguaje de BD de forma relativamente autónoma. Por otro lado, cabe destacar que ER es mucho más antiguo que UML, superándole en edad en el orden de décadas. UML es un lenguaje más amplio, es decir, no solo se utiliza para modelar BD, sino que es utilizado para modelar software también. En lugar de crear las relaciones de forma directa en la BD, el diseñador realiza un modelado de alto nivel, de modo que la situación que se está enfrentando con la BD pueda verse en su totalidad. Posteriormente el diseñador, una vez que valida su modelo, procede a su traducción al lenguaje de la BD. Esta situación no presenta trabajo innecesario (correspondiente al modelado y a la posterior creación de relaciones en la BD), pues afortunadamente la gran mayoría de estas herramientas permiten realizar una traducción al lenguaje de la BD.

Nota Existen variadas herramientas a la hora de graficar diagramas UML. Algunas de ellas son: DIA, StarUML o Umbrello entre otras. Existen 5 conceptos claves en UML: 1. Clases 2. Asociaciones 3. Clases de asociación 4. Subclases 5. Composiciones y agregaciones

Clases Las clases se componen de: nombre, atributos y métodos. Para quienes hayan experimentado alguna vez con la programación orientada a objetos probablemente se sientan algo familiarizados. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 152 de 253

A la hora de realizar modelos de BD, es necesario agregar un identificador para el atributo corresponde que a la clave primaria, además de un método de eliminación. No obstante en está Lección se pondrá más énfasis en los atributos, pues está enfocada más al modelado de datos que a su operación a través de sus métodos.

Ejemplo 1 Retomemos el caso de los Estudiantes y Establecimientos Educacionales. Dibujemos ambas relaciones como clases en UML:

Asociaciones Las Asociaciones corresponden a como se relacionan 2 clases.

Ejemplo 2 El ejemplo 1 terminó con 2 clases separadas, es decir, Estudiantes y Establecimientos Educacionales. Sin embargo, y como ya se ha visto en ejemplos de Leccións anteriores, los estudiantes postulan a estos establecimientos, por lo tanto la relación es postular:

Es decir que el Estudiante postula a un Establecimiento. Es posible direccionar esta relación para lograr mayor claridad a la hora de ver los diagramas:

Sin embargo no marca la diferencia a la hora de traducir a relaciones, es necesario cuantificar la relación

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 153 de 253

Multiplicidad Es necesario determinar cuántas veces un objeto de una clase puede relacionarse con objetos de otra clase. Supongamos que se han creado las clases C1 y C2, la multiplicidad apunta a: "Cada objeto de la clase C1 está asociado (a través de la relación A)a al menos 'm' y a lo más 'n' objetos de la clase C2"

La notación para ello corresponde a m..n, es decir que el valor mínimo es m y el máximo n. Ambos valores van separados por .. (dos puntos).

Cabe mencionar que estas relaciones pueden ser bidireccionales Algunos casos especiales son: m..* -> a lo menos 'm' a lo más cualquier valor superior a 'm' 0..n -> a lo menos '0' a lo más 'n' 0..* -> a lo menos '0' a lo más cualquier valor superior a '0', es decir , sin restricción. 1..1 -> sólo 1 valor.

Existen varios tipos de multiplicidad, con su respectiva notación. Ellos son: 1. uno a uno: 0..1 - 0..1 2. muchos a uno: 0..* - 0..1 3. muchos a muchos: 0..* - 0..* 4. completa: 1..* - 1..1 o 1..1 - 1..* o 1..* - 1..* Nota En la multiplicidad completa, no deben quedar objetos sin relacionarse.

Ejemplo 3 Supongamos que cada Estudiante debe postular a lo menos a 1 Establecimientos y a lo más a 3. Por otro lado, cada establecimiento puede recibir a lo más 50000 postulaciones.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 154 de 253

Ejemplo 4 Con el fin de diversificar y bajo el siguiente contexto, supongamos que tenemos personas que realizan giros en bancos. Dependiendo del tipo de cuenta, supongamos que existe una cuenta que permite a lo más 3 giros por mes. Por su parte, el banco no tiene restricción de giros que puede recibir.

Clase de asociación Esto se produce cuando la multiplicidad de las relaciones impide definir con exactitud qué objeto de la clase C1 está asociado a qué objeto de la clase C2.

Ejemplo 5 Supongamos que tenemos a varios Estudiantes que desean postular a diferentes Establecimientos Educacionales.

No obstante no hay información que permita definir qué estudiante realiza la postulación, es por ello que se crea una clase de asociación, en este caso postulación (Apply).

Nota

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 155 de 253

Cabe recordar que si no se especifica la multiplicidad de la relación, se define 1..1 por defecto.

Sin embargo en este modelo no se permite el caso de que un Estudiante postule múltiples veces a un mismo Establecimiento Educacional. Es por ello que es una buena práctica que, en caso de utilizar este tipo de clases, se utilice como Clave Primaria (PK), las PK de las clases que están relacionadas. El siguiente diagrama clarificará la idea:

Eliminar clases de asociación innecesarias Usando las clases genéricas C1, C2 de atributos A1, A2 y A3, A4 respectivamente. Supongamos que la relación entre ellas es de multiplicidad (* - 1..1) o (* - 0..1). Supongamos que existe una clase de asociación AC de atributos B1 y B2. Todo ordenado de acuerdo a la siguiente imagen:

Es posible mover los atributos B1 y B2 a la clase C1, pues dada la multiplicidad un objeto de la clase C1 está asociado a 1 objeto de la clase C2. Por lo tanto, la clase de asociación se puede eliminar.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 156 de 253

Nota La clase de asociación se puede eliminar cuando hay multiplicidad (* - 1..1) o (* - 0..1). De hecho está pensada para dejar en claro que la asociación entre objetos en caso de que la multiplicidad sea m, n o * en ambos lados de la relación.

Auto asociaciones Corresponden a asociaciones entre una clase y si misma.

Ejemplo 6 Supongamos que se desea modelar en UML a la Universidad Técnica Federico Santa María

(UCHILE),

su

Casa

Central

y

Campus.

Supongamos

que

existen

los

atributos NumAlumnos, Dirección, Nombre, Campus. Existe una sola Casa Central, pero varios Campus, supongamos que por temas de presupuesto, solo existen 7 campus.

En UML, es posible etiquetar la relación.

Subclases Las clases se dividen: 1. Superclase/ Clase Padre: De carácter general, contiene información que heredarán las diversas subclases. 2. Subclases/ Clases Hijas: De carácter específico, contiene información extra a la que hereda de la superclase. Estos conceptos nacen de la programación orientada a objetos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 157 de 253

Ejemplo 7 Supongamos que dentro de la clase Estudiantes, se desea diferenciar a los estudiantes extranjeros de los estudiantes nacionales. Se podría pensar en crear dos clases nuevas, llamadas Estudiantes Nacionales y Estudiantes Extranjeros:

Sin embargo, hay atributos que se repiten en ambas, ellos son: sID, sName, Average. Es por ello que se pueden separar en una superclase llamada Estudiante (la misma utilizada en las otras Leccións), y crear 2 subclases llamadas Extranjeros y Nacionales.

Como se puede observar, los atributos mencionados son heredados por ambas subclases. Ambas además agregan información más específica, como lo son el país y pasaporte en el caso de los Extranjeros; la región y RUN en el caso de los Nacionales. Esta técnica es muy útil para la reutilización. Nota Las Subclases heredan propiedades de las superclases / clase padre, es decir no solo atributos, sino que también asociaciones u operaciones están disponibles en las subclases / clases hijas.

Composiciones y Agregaciones Ambas corresponden a la forma de representar que un objeto tiene como contenido a otro, esto quiere decir que un objeto de un tipo, puede contener a otro.

Ejemplo 8 Supongamos que un objeto de tipo ciudad tiene una lista de objetos de tipo aeropuerto, esto quiere decir, que una ciudad, tiene un número de aeropuertos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 158 de 253

Nota Hay que destacar, que la cardinalidad del extremo que lleva el rombo, es siempre uno.

Otro ejemplo puede ser que un medio de transporte tiene varias ruedas. Nos está diciendo que los objetos rueda forman parte del objeto medio de transporte. Pero, su ciclo de vida no está atado al del objeto medio de transporte. Es decir, si el automóvil se destruye las ruedas pueden seguir existiendo independientemente. En la misma linea, la composición, es una relación más fuerte de los objetos, así como la agregación, es el hecho de que un objeto posea a otro, la composición es cuando la relación entre ambos objetos es tal, que el primero no tiene sentido solo, y el segundo, necesita definir al primero para ampliar su significado

Ejemplo 9

El avión tiene sentido por si solo. Está claro que está compuesto de 2 alas, esta relación es de mucha fuerza, mucho más que el caso de los aeropuertos, y está claro, que un avión siempre tendrá sus dos alas, y estas siempre serán del mismo avión. La composición corresponde a aquellos objetos de los que depende un objeto para que este llegue a funcionar, en éste caso el avión no puede funcionar sin las 2 alas.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 159 de 253

Lección XX - Lenguaje de Modelado Unificado: UML y las bases de datos Este lenguaje de modelado se caracteriza por: 

Fácil de usar, se apoya en muchos diagramas (gráficos).



Es fácil traducirlo al modelo de DBMS.

En siguiente imagen anterior se observa que el lenguaje UML, puede ser traducido en relaciones (o tablas) de una base de datos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 160 de 253



Son cinco los conceptos claves: 1. Clases 2. Asociaciones 3. Clases de asociación 4. Subclases 5. Composición y Agregación



Los diseños pueden ser traducidos automáticamente a relaciones o tablas

Clases Cada clase se convierte en una relación (o tabla) con su clave primaria

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 161 de 253

Según las clases descritas anteriormente tenemos las siguientes relaciones:

Estudiante(sID−−−−,sNombre,GPA) Universidad(cNombre−−−−−−−−,estado,inscripción) Asociaciones Relación con la clave de cada lado.

Se obtendrán las mismas relaciones del ejemplo anterior, pero se agregará una nueva relación con las claves primarias de ambas clases.

Aplicado(sID,cNombre) Claves para las relaciones de asociación 

Depende de la multiplicidad 

Multiplicidad 1-1 

Cada objeto de A está asociado con un objeto de B, y cada objeto de B está asociado con un objeto de A.



Cualquiera de las dos tablas relacionadas implementará una columna con el ID de la otra tabla.

 

Esta columna será la clave foránea para relacionarlas.

Multiplicidad 1-n 

Cada objeto A está asociado con más objetos B, pero cada objeto B está asociado con un objeto A.



Implementando la clave foránea ID en la tabla “muchos” a la tabla “uno”.

Por ejemplo:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 162 de 253

Ahora al tener una multiplicidad 1-n, se debe agregar la clave primaria Atr1_clase1 de la Clase1 a la Clase2(que es la que posee la multiplicidad “muchos”), quedando como clave foránea de la Clase2. Finalmente, las relaciones quedan de la siguiente manera:

Clase1(Atr1_clase1−−−−−−−−−,Atr2_clase1) Clase2(Atr1_clase2−−−−−−−−−,Atr2_clase2,Atr1_clase1) Si la relación hubiese sido de 0..1-n, el atributo Atr1_clase1 sería NULL. Ahora se mostrará otro ejemplo:

Las relaciones para este ejemplo serían:

Estudiante(sID−−−−,sNombre,GPA,cNombre) Universidad(cNombre−−−−−−−−,estado,inscripción) Al poseer este tipo de multiplicidad (1-n), se agrega a la clase Estudiante (muchos) la clave primaria cNombrede la clase Universidad. 

Multiplicidad n-m 

Cada objeto A está asociado con más objetos B, y a su vez, cada objeto B está asociado a más objetos A.



En el modelo relacional se usa una tabla auxiliar asociativa para representar la relación.



Dicha

tabla

tendrá

al

menos

dos

columnas,

cada

una

representando la clave foránea a las dos tablas que relaciona. 

Con lo anterior se transforma la relación n-m a dos relaciones (1n, 1-m).

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 163 de 253

Ejemplo Supongamos que tenemos 0..2 en el lado derecho, por lo que los estudiantes pueden solicitar hasta un máximo de 2 universidades. ¿Existe todavía una forma de “plegarse” la

relación

de

asociación

en

este

caso,

o

que

tenemos

una

relación

independiente Aplicado?

a. Sí, hay una manera. b. No, si no es 0..1 ó 1..1 aplicado entonces se requiere. La alternativa correcta es (a), puesto que se debería crear la relación Estudiante(sID, sNombre, GPA, cNombre1, cNombre2), suponiendo que se permiten valores nulos.

Clase de asociación Las clases de asociación permiten añadir atributos, operaciones y otras características a las asociaciones.

Las relaciones de estas clases queda de la siguiente manera:

Estudiante(sID−−−−,sNombre,GPA) Universidad(cNombre−−−−−−−−,estado,inscripción) Aplicado(sID,cNombre,Fecha,Decisión) Otro ejemplo que detalla más claramente las clases de asociación Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 164 de 253

El diagrama permite apreciar que una Persona puede trabajar para una sola Compañía. Necesitamos conservar la información sobre el período de tiempo que trabaja cada empleado para cada Compañía. Para lograrlo, añadimos un atributo Período a la asociación Empleo.

Subclases Si la clase “A” hereda de la clase “B”, entonces “B” es la superclase de “A”. “A” es subclase de “B”. Los objetos de una subclase pueden ser usados en las circunstancias donde son usados los objetos de la superclase correspondiente. Esto se debe al hecho que los objetos de la subclase comparten el mismo comportamiento que los objetos de la superclase.

1. Las relaciones de las subclases contienen una clave de la superclase más atributos especializados.

S(K−−,A)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 165 de 253

S1(K−−,B) S2(K−−,C) 2. Las relaciones de las subclases contienen todos los atributos.

S(K−−,A) S1(K−−,A,B) S2(K−−,A,C) 3. Una relación que contiene todos los atributos de la superclase y la subclase.

S(K−−,A,B,C)

Ejemplo de subclases

Las relaciones de este ejemplo son:

Estudiante(sID−−−−,sNombre) Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 166 de 253

Est_extranjeros(sID−−−−,País) Est_nacionales(sID−−−−,Estado,SS#) AP_Estudiante(sID−−−−) AP_Curso(Curso#−−−−−−−,Titulo) Tomó(sID,Curso#,Año,Nota) Composición y Agregación Composición La composición es un tipo de relación estática, en donde el tiempo de vida del objeto incluido está condicionado por el tiempo de vida del que lo incluye (el objeto base se construye a partir del objeto incluido, es decir, es parte/todo).

Ejemplo

Las relaciones se definen de la siguiente manera:

Universidad(cNombre−−−−−−−−,Estado) Departamento(dNombre−−−−−−−−,Edificio,cNombre) Agregación La agregación es un tipo de relación dinámica, en donde el tiempo de vida del objeto incluido es independiente del que lo incluye (el objeto base utiliza al incluido para su funcionamiento).

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 167 de 253

Ejemplo

Las relaciones son de la misma manera que el ejemplo anterior pero al poseer una diferente multiplicidad el valor del atributocNombre de la clase Departamento, puede tómar el valor NULL. Nota El software utilizado en esta Lección para realizar los diagramas es “Umbrello”.

Lección XXI - Restricciones y triggers: Introducción Ambos están orientados a Bases de Datos Relacionales o RDB por sus siglas en inglés. Si bien SQL no cuenta con ellos, en las diversas implementaciones del lenguaje se ha corregido este “error”. Lamentablemente no cuenta con un estándar, por lo que existe gran cantidad de variaciones. Las restricciones, también llamadas restricciones de integridad, permiten definir los estados permitidos dentro de la Base de Datos (BD). Los triggers, en cambio, monitorean los cambios en la BD, chequean condiciones, e inician acciones de forma automática. Es por ello que se les considera de naturaleza dinámica, a diferencia de las restricciones de integridad, que son de naturaleza estática. Ambos se analizarán en detalle en las próximas Leccións.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 168 de 253

Restricciones Imponen restricciones de datos permitidos, más allá de aquellos impuestos por la estructura y los tipos de datos en la BD. Supongamos que estamos bajo el contexto del sistema de selección de estudiantes, visto en algunas Leccións anteriores:

Ejemplo 1 Para que el estudiante sea aceptado, su promedio debe ser mayor a 50: Average > 50

Ejemplo 2 El establecimiento X no puede tener más de 45000 alumnos: Enrollment < 45000

Ejemplo 3 El criterio para la decisión es Verdadero, Falso o NULL: Decisión: 'T', 'F', **NULL**

Las restricciones se utilizan para: 1. Evitar errores a la hora de ingresar datos (INSERT). 2. Evitar errores a la hora de modificar datos (UPDATE). 3. Forzar consistencia de datos. Existen diversos tipos de restricciones. Ellas se clasifican en: 1. NOT NULL : No permiten valores nulos. 2. Key : Permiten sólo valores únicos, asociados a la llave primaria. 3. Integridad Referencial: Relacionados con la llave foránea y múltiples tablas. 4. Basado en atributos : Restringe el valor de un atributo. 5. Basado en tupla : Restringe el valor de una tupla. Más especifico que el anterior. 6. Generales : Restringen toda la BD.

Declarando y forzando restricciones Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 169 de 253

Se puede forzar el chequeo después de cada modificación peligrosa, es decir aquellas que violan una restricción. No es necesario modificar después de un cambio de promedio en la tabla de Estudiantes, eso sólo ralentizaría el sistema. Otra forma de forzar chequeos es después de cada transacción. Este concepto, se verá más adelante, pero es posible adelantar que una tras acciones corresponden a un conjunto de operaciones que al finalizar modifican la BD.

triggers La lógica del trigger es: "Cuando pasa algo, se chequea una condición. Si es cierta se realiza una acción"

Como ya se mencionó, a diferencia de las restricciones, un trigger detecta un evento, verifica alguna condición de activación y en caso de ser cierta, realiza una acción. Contextualizándonos en el sistema de admisión de Estudiantes:

Ejemplo 4 Si la capacidad de un Establecimiento X, sobrepasa los 30000, el sistema debe comenzar a rechazar a los nuevos postulantes: Enrollment > 30000 -> rechazar nuevos postulantes

Ejemplo 5 Si un alumno tiene promedio mayor a 49.5, queda aceptado: Student with

Average > 49.5 -> Decision='True'

Los triggers se utilizan para: 1. Mover la lógica desde la aplicación a Sistema Administrador de la Base de Datos (BDMS), lo cual permite un sistema más modular y automatizado. 2. Forzar

restricciones.

Ningún

sistema

implementado

soporta

todas

las

restricciones de otro, es decir no existe un estándar actual. Un caso es el ejemplo 5, en el cual algún DBMS podría redondear hacía abajo en lugar de hacia arriba; con el trigger esto se podría resolver. Además existen restricciones que no se pueden escribir de forma directa, pero si utilizando triggers.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 170 de 253

3. Forzar restricciones utilizando lógica reparadora. Un error se puede detectar y realizar una acción, que puede ser por ejemplo, si existe la restricción 0 2; DELETE 1

Quedando como resultado las tablas Student y Apply SELECT * FROM Student; sid | sname | gpa | sizehs -----+-------+-----+--------

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 189 de 253

1 | Tom 2 | Brad (2 rows)

| 3.4 | | 3.8 |

1000 1500

SELECT * FROM Apply; sid | cname | major | decision -----+-------+---------+---------1 | UCHILE | CS | Y 2 | PUC | biology | N 1 | PUC | CS | Y | UCHILE | history | Y | UCHILE | CS | Y (5 rows)

Se observa que se eliminó a la estudiante “Lucy”, que tenía un sID = 3, de la tabla Student y la tabla Apply dejando en esta última el valor del atributo sID = “null”, cumpliéndose

la

restricción

que

se

detalló

en

la

creación

de

la

tabla ON DELETE SET NULL.

Ejemplo CASCADE Se desea actualizar el cName de la tabla College el valor de “UCHILE” por “USM”. UPDATE College SET cName='USM' WHERE cName='UCHILE'; UPDATE 1

Ahora no retornó ningún error como en el ejemplo anterior, esto se debe a la restricción que se agregó en la creación de la tablaApply, en que se maneja automáticamente las violaciones a la integración referencial. Las tablas después de ejecutar el comando de actualización quedaron de la siguiente manera: SELECT * FROM College; cname | state | enrollment -------+-------+-----------PUC | CA | 36000 USM | CA | 15000 (2 rows) SELECT * FROM Apply; sid | cname | major | decision -----+-------+---------+---------2 | PUC | biology | N 1 | PUC | CS | Y 1 | USM | CS | Y | USM | history | Y

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 190 de 253

| USM (5 rows)

| CS

| Y

Observándose en las tablas College y Apply que se actualizó cName = ‘USM’, en ambas tablas. A continuación se mostrarán otras características que no se han visto en los ejemplos anteriores: CREATE TABLE T(A INT, B INT, C INT, PRIMARY REFERENCES T(A,B) ON DELETE CASCADE); INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO

T T T T T T T T

KEY(A,B), FOREIGN

KEY(B,C)

VALUES(1,1,1); VALUES(2,1,1); VALUES(3,2,1); VALUES(4,3,2); VALUES(5,4,3); VALUES(6,5,4); VALUES(7,6,5); VALUES(8,7,6);

Este ejemplo es para demostrar la integridad referencial dentro de una sola tabla T. SELECT * FROM T; a | b | c ---+---+--1 | 1 | 1 2 | 1 | 1 3 | 2 | 1 4 | 3 | 2 5 | 4 | 3 6 | 5 | 4 7 | 6 | 5 8 | 7 | 6 (8 rows)

Si queremos borrar de la tabla T, cuando A=1. DELETE FROM T WHERE A=1;

Quedando la tabla como: SELECT * FROM T; a | b | c ---+---+--(0 rows)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 191 de 253

Podemos observar que al dar la condición de borrar A=1, se eliminan todos los demás valores, esto sucede por la definición que se dio como clave foránea de B y C en la creación de la tabla T. La integración referencial es muy común en las implementaciones de las bases de datos relacionales. La forma natural de diseñar un esquema relacional suelen tener valores en columnas de una tabla que se refieren a los valores de las columnas de otra tabla, y el establecimiento de restricciones de integridad referencial, este sistema controlará la base de datos y se asegurará de que se mantenga siempre constante.

Lección XXIV - Restricciones y Triggers: Triggers introducción y demostración Triggers (disparadores) Durante la ejecución de una aplicación de base de datos, hay ocasiones que se requiere realizar una o más acciones de forma automática, si se produce un evento en específico. Es decir, que la primera acción provoca la ejecución de las siguientes acciones. Los denominados Triggers o disparadores son el mecanismo de activación, que posee SQL para entregar esta capacidad.

Definición Un Trigger es una orden que el sistema ejecuta de manera automática como efecto secundario de alguna modificación de la base de datos. Entonces los Triggers siguen en

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 192 de 253

orden

la

secuencia: evento->condición->acción.

Se

ejecutan

mediante

los

comandosINSERT, DELETE y UPDATE. Para diseñar un mecanismo Trigger hay que cumplir algunos requisitos: 1. Procedimiento almacenado: Se debe crear la base de datos y las relaciones, antes de definir el Trigger. 2. Especificar condiciones

para

ejecutar el Trigger: Se debe definir

un evento que causa la comprobación del Trigger y una condición que se debe cumplir para ejecutar el Trigger. 3. Especificar las acciones: Se debe precisar qué acciones se van a realizar cuando se ejecute el Trigger. La base de datos almacena Trigger como si fuesen datos normales, por lo que son persistentes y accesibles para todas las operaciones de la base de datos. Una vez se almacena un Trigger en la base de datos, el sistema de base de datos asume la responsabilidad de ejecutarlo cada vez que ocurra el evento especificado y se satisfaga la condición correspondiente.

Algunas aplicaciones de Triggers El Trigger es útil en una serie de situaciones diferentes. Un ejemplo es realizar una función de registro. Algunas acciones críticas para la integridad de una base de datos, tales como insertar, editar o eliminar una fila de la tabla, podrían desencadenar una inscripción en un registro que documente la acción realizada. El registro podrían grabar no sólo lo que se modificó, sino también cuando fue modificada y por quién. Los Triggers también pueden ser usados para mantener una base de datos consistente. Por ejemplo si se tiene una relación dePedidos, el pedido de un producto especifico puede activar una sentencia que cambie el estado de ese producto en la tabla Inventario, y pase de disponible a reservado. Del mismo modo, la eliminación de una fila en la tabla de pedidos puede activar la acción para cambia el estado del producto de reservado a disponible. Los Triggers ofrecen una flexibilidad aún mayor que la que se ilustra en los ejemplos anteriores.

Creación de un disparo 1. Un Trigger se crea con la sentencia CREATE TRIGGER. 2. Después de que se crea, el Trigger se encuentra a la espera de que se produzca el evento de activación. 3. Cuando el evento se produce desencadena una acción.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 193 de 253

Forma general: (1)CREATE TRIGGER nombreTrigger (2)BEFORE|AFTER|INSTEAD OF AlgúnEvento ON nombreTabla (3)WHEN (condición) (3)Action

Variables especiales Existen algunas palabras reservadas, que están disponibles para ser utilizadas por Triggers. Algunas de estas variables especiales disponibles son las siguientes: 

NEW:

Variable

que

contiene

la

nueva

la

antigua

fila

de

la

tabla

para

las

de

la

tabla

para

las

operaciones INSERT/UPDATE. 

OLD:

Variable

que

contiene

fila

operaciones UPDATE/DELETE. 

TG_NAME: Variable que contiene el nombre del Trigger que está usando la función



actualmente. TG_RELID: identificador de objeto de la tabla que ha activado el Trigger.



TG_TABLE_NAME: nombre de la tabla que ha activado el Trigger.

Ejemplo : Nota El ejemplo a continuación es para explicar el funcionamiento de un Trigger, si se desea probar en postgreSQL se debe crear la base de datos y la tabla Employee, más adelante se verán un ejemplo práctico que podrá copiarse directamente en la consola.

El siguiente Trigger se “dispara” por cambios en el atributo salary. El efecto de este Trigger es para frustrar cualquier intento de disminuir el valor de salary en la tabla Employee.

Employee(cert−−−,name, address, salary) (1) (2) (3) (4) (5) (6) (7)

CREATE TRIGGER salaryTrigger AFTER UPDATE OF salary ON Employee REFERENCING OLD ROW AS OldTuple, NEW ROW AS NewTuple FOR EACH ROW WHEN (OldTuple.salary > NewTuple.salary)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 194 de 253

(8) UPDATE Employee (9) SET salary = OldTuple.salary (10) WHERE cert = NewTuple.cert ; 

(1) Se crea el Trigger: con las palabras claves CREATE TRIGGER y el nombre del Trigger salaryTrigger.



(2) Evento de activación:, en este caso es la actualización del atributo salary de la relación Employee.



(3) (4) y (5) Camino para la condición: a la tupla antigua (tupla antes de la actualización) se le asigna el nombre OldTupley la nueva tupla (tupla después de la actualización), se asigna como NewTuple. En la condición y la acción, estos nombres se pueden utilizar como si fueran variables declaradas en la cláusula FROM de una consulta SQL.



(6), La frase FOR EACH ROW, expresa la exigencia de que este Trigger se ejecute una vez por cada tupla actualizada.



(7) Condición del Trigger: Se dice que sólo se realiza la acción cuando el nuevo salary es menor que el salary antiguo.



(8) (9) y (10) Acción del Trigger Esta acción es una instrucción SQL de actualización que tiene el efecto de restaurar el salarya lo que era antes de la actualización. Tenga en cuenta que, en principio, cada tupla de Employee se considera para la actualización, pero la cláusula WHERE de la línea (10) garantiza que sólo la tupla actualizada (con el correcto cert) se verán afectados.

Funciones Existe una forma de separar las acciones y las condiciones de un Trigger. Esto se logra mediante el uso de funciones. Uno de los motivos de utilizar funciones es mantener la lógica lejos de la aplicación, con esto se consigue consistencia entre aplicaciones y reducción de funcionalidad duplicada. Además un acceso predefinido a objetos restringidos. SQL es un lenguaje declarativo, pero en ocasiones se requiere de otro tipo de lenguajes. El manejo de funciones permite utilizar distintos de lenguajes, de manera que se puede escoger la herramienta adecuada a cada caso. Para efecto de este curso se usará unlenguaje imperativo, llamado PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language). Nota Un Lenguaje imperativo le ordena a la computadora cómo realizar una Práctica siguiendo una serie de pasos o instrucciones. La ejecución de estos comandos se realiza, en la mayor

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 195 de 253

parte de ellos, secuencialmente, es decir, hasta que un comando no ha sido ejecutado no se lee el siguiente. Aunque también existen los bucles controlados que se repiten hasta la ocurrencia de algún evento.

PL/pgSQL dispone de estructuras condicionales y repetitivas. Se pueden realizar cálculos complejos y crear nuevos tipos de datos de usuario. En PL/pgSQL se pueden crear funciones. En esta sección se verá como dichas funciones pueden ser ejecutadas en eventos de tipo Trigger.

Ejemplo Práctico 1: Se crea una base de datos y se instala el lenguaje plpgsql. postgres=# create database trggr2; CREATE DATABASE postgres=# \c trggr2 psql (8.4.11) Ahora está conectado a la base de datos «trggr2». trggr2=# CREATE PROCEDURAL LANGUAGE plpgsql; CREATE LANGUAGE

Se crea la relación numbers CREATE TABLE numbers( number int NOT NULL, square int, squareroot real, PRIMARY KEY (number) );

Se define una función llamada save_data(), que será la encargada de llenar los datos, al final del ejemplo se explica detalladamente su funcionamiento: CREATE OR REPLACE FUNCTION save_data() RETURNS Trigger AS $save_data$ DECLARE BEGIN NEW.square := power(NEW.number,2); NEW.squareroot := sqrt(NEW.number); RETURN NEW; END; $save_data$ LANGUAGE plpgsql;

PostgreSQL retorna:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 196 de 253

CREATE FUNCTION

Ahora

ya

se

puede

definir

el

Trigger

que

llamará

a

la

función save_data() automáticamente, cada vez que se inserte o actualice un dato. CREATE TRIGGER save_data BEFORE INSERT OR UPDATE ON numbers FOR EACH ROW EXECUTE PROCEDURE save_data();

PostgreSQL retorna: CREATE TRIGGER

Para ver cómo funciona el Trigger se insertan los números 4, 9 y 6. trggr2=# INSERT INTO numbers (number) VALUES (4),(9),(6); INSERT 0 3

Y se realiza un select para ver los datos almacenados. trggr2=#

SELECT * FROM numbers;

number | square | squareroot --------+--------+-----------4 | 16 | 2 9 | 81 | 3 6 | 36 | 2.44949 (3 rows)

También se puede actualizar trggr2=# UPDATE numbers SET number = 7 WHERE number = 6; UPDATE 1 trggr2=# SELECT * FROM numbers; number | square | squareroot --------+--------+-----------4 | 16 | 2 9 | 81 | 3 7 | 49 | 2.64575 (3 rows)

Como se puede apreciar, solo se ha insertado o actualizado el valor number pero al hacerlo automáticamente se llenaron los valores para los atributos square y squareroot. Esto es debido a que el Trigger estaba definido para activarse al realizar un INSERT o UPDATE. Por cada uno de estos comandos el Trigger ordenó la ejecución de la función save_data(), una vez por cada fila involucrada. Es decir cuando realizamos el Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 197 de 253

primer INSERT (number = 4), el Trigger save_data llama a la función save_data() una vez. 

El

valor

de

la

variable NEW al

empezar

ejecutarse save_data() es: number=4, square=NULL, squareroot=NULL .

a La

tabla numbers aún está vacía. 

A continuación se calcula el cuadrado y la raíz cuadrada de 4, estos valores se asignan a NEW.square y NEW.squareroot respectivamente. Ahora la variable NEW contiene number=4, square=16, squareroot=2 .

Para calcular el cuadrado de un número se utiliza la instrucción power, que recibe como parámetros el número que se ingrese y el número al cual se eleva. Para calcular la raíz cuadrara de un número se utiliza la instrucción sqrt que recibe como parámetro el nuevo número. 

Con la sentencia RETURN NEW, se retorna la fila RECORD almacenada en la variable NEW, el sistema almacena entonces NEW en la tablanumbers.

Ejemplo Práctico 2: Para este ejemplo se utiliza la misma relación numbers creada anteriormente, con los valores ya insertados. La función protect_dataes usada para proteger datos en una tabla. No se permitirá el borrado de filas, pues retorna NULL que, como se vio en Leccións anteriores, es la inexistencia de valor. CREATE OR REPLACE FUNCTION protect_data() RETURNS Trigger AS $Tprotect$ DECLARE BEGIN RETURN NULL; END; $Tprotect$ LANGUAGE plpgsql;

El siguiente Trigger llamado Tprotect se activa antes de realizar una eliminación de datos de la tabla numbers, la acción que realiza es llamar a la función protect_data. CREATE Trigger Tprotect BEFORE DELETE ON numbers FOR EACH ROW EXECUTE PROCEDURE protect_data();

Se intenta eliminar todos los datos de la tabla numbers con la siguiente sentencia: trggr2=# DELETE FROM numbers; DELETE 0

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 198 de 253

Sin

embargo

no

es

posible

borrar

datos

pues

el

Trigger

acciona

la

función protect_data, y ningún dato es eliminado. trggr2=# SELECT * FROM numbers; number | square | squareroot --------+--------+-----------4 | 16 | 2 9 | 81 | 3 7 | 49 | 2.64575 (3 rows)

Ejemplo Práctico 3: Nuevamente se utiliza la relación numbers, las funciones y los Triggers ya creados. La función que se verá a continuación busca evitar errores al calcular la raíz cuadrada de un número negativo. Observe que ocurre al intentar insertar el valor -4: trggr2=# INSERT INTO numbers (number) VALUES (-4); ERROR: cannot take square root of a negative number CONTEXTO: PL/pgSQL function "save_data" line 5 at assignment

La consola arroja un error en la función save_data, pues no puede calcular la raíz de un número negativo. La función invalid_root ocupa la sentencia IF para validar que el número sea mayor a 0. La construcción IF sirve para ejecutar código sólo si una condición es cierta, dicha condición debe ser una expresión booleana. La sentencia IF tiene la forma: si (condición es cierta) entonces realizar sentencia, si la condición no se cumple la línea o líneas se saltan y no son ejecutadas, se evalúan entonces sucesivamente las condiciones ELSIF, que son una condición alternativa al IF, en este caso se especifica que el nuevo número sea mayor o igual a 0. Al ingresar a la sentencia IF se ejecuta la misma acción de la función protect_data , es decir retorna NULL y no realiza ninguna acción sobre numbers. Si es mayor o igual a 0 se ejecuta la sentencia que está al interior de la instrucción ELSIF, esta sentencia es la misma que emplea la función sabe_data, esto es, calcular el cuadrado y la raíz. CREATE OR REPLACE FUNCTION invalid_root() RETURNS Trigger AS $invalid_root$ DECLARE BEGIN IF (NEW.number < 0) THEN RETURN NULL; ELSIF (NEW.number >= 0) THEN NEW.square := power(NEW.number,2);

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 199 de 253

NEW.squareroot := sqrt(NEW.number); RETURN NEW; END IF; END; $invalid_root$ LANGUAGE plpgsql;

Luego de tener la función se define el Trigger que detona la función. El Trigger invalid_root se activa cuando se realiza una inserción o actualización de datos en numbers. CREATE TRIGGER invalid_root BEFORE INSERT OR UPDATE ON numbers FOR EACH ROW EXECUTE PROCEDURE invalid_root();

Ahora se vuelve a probar la inserción de un número negativo: trggr2=# INSERT INTO numbers (number) VALUES (-4); INSERT 0 0

Esta vez no arroja error pues ingresa al IF que restringe valores negativos, y simplemente no inserta el valor. Y si se intenta ingresar un numero positivo, se consigue sin problemas: trggr2=# INSERT INTO numbers (number) VALUES (5);INSERT 0 1 trggr2=# SELECT * FROM numbers; number | square | squareroot --------+--------+-----------4 | 16 | 2 9 | 81 | 3 7 | 49 | 2.64575 5 | 25 | 2.23607 (4 filas)

Para borrar un Trigger y una función primero se elimina el Trigger: trggr2=# DROP Trigger invalid_root ON numbers; DROP Trigger

Y luego se puede eliminar la función: trggr2=# DROP FUNCTION invalid_root(); DROP FUNCTION

Cuándo no deben usarse los Triggers Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 200 de 253

Existen algunos casos que pueden ser manejados de mejor forma con otras técnicas: 

Realizar resúmenes de datos: Muchos sistemas de bases de datos actuales soportan las vistas materializadas, que proporcionan una forma mucho más sencilla de mantener los datos de resumen.



Respaldo de las bases de datos: Anteriormente los diseñadores de sistemas, usaban Triggers con la inserción, eliminación o actualización de las relaciones para registrar los cambios. Un proceso separado copiaba los cambios al respaldo de la base de datos, y el sistema ejecutaba los cambios sobre la réplica. Sin embargo, los sistemas de bases de datos modernos proporcionan características incorporadas para el respaldo de bases de datos, haciendo innecesarios a los Triggers para la réplica en la mayoría de los casos.

Los Triggers se deberían escribir con sumo cuidado, dado que un error de un Trigger detectado en tiempo de ejecución causa el fallo de la instrucción de inserción, borrado o actualización que inició el Trigger. En el peor de los casos esto podría dar lugar a una cadena infinita de Triggers. Generalmente, los sistemas de bases de datos limitan la longitud de las cadenas de Triggers.

Lección XXV Introducción

-

Transacciones:

En esta Lección se presentarán los conceptos de transacciones y acciones de interacción con los sistemas de base de datos. El

concepto

de transacciones está

identificado

por

dos

acepciones

totalmente

independientes. Uno, tiene que ver con el acceso concurrente de varios clientes a la base de datos; y el otro, tiene relación con tener un sistema resistente a los fallos de sistema. Primero, veremos como funciona la estructura de los sistemas de bases de datos y la interacción con los clientes. En la siguiente imagen podemos observar que los datos son almacenados en el disco, quien se comunica con el sistema de gestión de base de datos, o DBMS, controlador de las interacciones con los datos. A menudo hay software adicional por encima del DBMS; tal vez un servidor de aplicaciones o servidor web, que luego interactúan con los que Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 201 de 253

podrían ser usuarios a través de comandos de selección, actualización, creación de tablas, comandos de borrado, etc. Y es aquí finalmente donde ocurre el problema, que es la interacción concurrente de múltiples usuarios.

Integridad de las transacciones 

Una transacción es un conjunto de operaciones (u órdenes) que se ejecutan en forma indivisible (atómica) sobre una base de datos.



El

DBMS

debe

mantener

la

integridad

de

los

datos,

haciendo

que

estas transacciones no puedan finalizar en un estado intermedio. 

Si por algún motivo se debe cancelar la transacción, el DBMS empieza a deshacer las órdenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de integridad), como si la orden de la transacción nunca se hubiese realizado.

Ahora se mostrarán ejemplos de dificultades que pueden ocurrir cuando múltiples clientes están interactuando con la base de datos.

Nivel de Inconsistencia en Atributos

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 202 de 253

UPDATE College SET enrollment = enrollment + 500 WHERE cName = 'UCHILE';

concurrente con UPDATE College SET enrollment = enrollment + 1000 WHERE cName = 'UCHILE';

En el ejemplo anterior se puede observar que tenemos 2 clientes; uno esta emitiendo una declaración que aumenta las matriculas deUCHILE en 500, el segundo cliente, en el mismo instante, está emitiendo una declaración que aumenta las matriculas en 1000. El problema que se genera en este caso es que si el primer cliente modifica el valor de la matricula de la universidad UCHILE en 500 y ahora el segundo cliente va a modificar el valor de la matricula, sobre lo que ya modificó el primer cliente. A la base de datos se le modificó dos veces el valor de la matricula. Supongamos que el valor de la matricula está en 3000 y estas dos sentencias anteriores se ejecutan al mismo tiempo, el valor final de la matricula será 4500 (500 + 1000 + 3000).

Nivel de Inconsistencia en Tuplas UPDATE Apply SET major = 'history' WHERE sID = 1;

concurrente con UPDATE Apply SET decision = 'Y' WHERE sID = 1;

En este ejemplo, al haber dos clientes que están realizando una modificación en una tupla (o fila) sID = 1; el primero actualizando la especialidad a history y el segundo a una decision con valor Y. Es posible que se vean ambas modificaciones reflejadas en la base de datos, pero también existe la posibilidad de que sólo se visualice una.

Nivel de Inconsistencia en Tablas UPDATE Apply SET decision = 'Y' WHERE sID IN (SELECT sID FROM Student WHERE GPA > 3.9);

concurrente con UPDATE Student SET GPA = (1.1) * GPA WHERE sizeHS > 2500;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 203 de 253

Tenemos el primer cliente trabajando en la tabla Apply, pero las condiciones que se detallan en la tabla Apply, dependen de la tablaStudent. Mientras tanto el segundo cliente esta modificando la tabla Student. Así que lo que sucede en la tabla Apply, puede depender de si ocurre antes, después o durante la modificación de la tabla Student. Entonces los GPA se modifican y luego las aceptaciones se hacen o viceversa.

Objetivo de la Concurrencia Tenemos múltiples clientes interactuando con la base de datos al mismo tiempo, y si en realidad los comandos que se ejecutan en la base de datos fuesen intercalados, a menudo los comandos de actualización e incluso los de selección, pueden generar un comportamiento inconsistente o inesperado. Lo que idealmente se busca es que el cliente ejecute comandos a las bases de datos y no preocuparse de lo que están realizando otros clientes en ese mismo instante.

Objetivo Principal Ejecutar secuencias de instrucciones SQL que parecen estar funcionando de manera aislada. 

Solución Simple: ejecutarlos de forma aislada.

Pero se desea habilitar la concurrencia siempre que sea seguro hacerlo. Por lo general para poder hacer funcionar un ambiente con concurrencia es posible con un sistema que posea: 

Sistema multi-procesador.



Sistema multi-threads

A continuación se explicarán las fallas de sistema.

Resistencia a fallos del sistema Una vez más, tenemos nuestro sistema de base de datos con los datos en el disco. Supongamos que estamos en el proceso de una carga masiva de datos en la base de datos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 204 de 253

Quizás una gran cantidad de datos de una fuente externa, por ejemplo un conjunto de archivos. Precisamente en el momento de una carga masiva de datos, tenemos una caída del sistema o un fallo del sistema. Esto podría deberse a un fallo de software o hardware, o algo tan simple como la salida de energía. Por lo tanto, si se cargaron la mitad de los datos que se debían cargar en la base de datos ¿Qué sucede cuando el sistema vuelve? Se queda en un estado inconsistente bastante desagradable. Como ejemplo, vamos a suponer que estábamos realizando un montón de cambios en la base de datos. Entonces los datos que se quieren actualizar son modificados en la memoria y luego se escriben nuevamente en el disco. Así que, suponemos que en medio de este proceso tenemos una caída del sistema. Esto volvería a dejar a la base de datos en un estado inconsistente. Así que el objetivo general frente a los fallos del sistema es que cuando queremos hacer algo en la base de datos es indicarle al sistema que queremos garantizar la ejecución de todo o nada para ese conjunto particular de operaciones en la base de datos independientemente de los fallos que pudieran ocurrir durante la ejecución.

Solución para la concurrencia y fallos Una transacción es una secuencia de una o más operaciones de SQL tratados como una unidad. 

Transacciones parecen funcionar de forma aislada.



Si el sistema falla, los cambios de cada transacción se reflejan en su totalidad.

Estándar SQL: 

Una transacción comienza automáticamente en la primera sentencia SQL.



Cuando el comando “commit” (una palabra clave especial) se ejecuta, la transacción actual termina y comienza una nueva.



La transacción actual, también termina cuando su período de sesiones termina con la base de datos.



“Autocommit” cada sentencia SQL se ejecuta como una transacción.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 205 de 253

Lección XXVI Propiedades

-

Transacciones:

En esta Lección se va a profundizar sobre las propiedades de las transacciones. Como recordatorio, las transacciones son un concepto que ha sido introducido como una solución tanto para el problema de control de concurrencia y fallos de sistemas en las bases de datos. Todo conocedor de bases de datos, sabe que las transacciones soportan lo que se conoce como las propiedades ACID. 

A: Atomicidad.



C: Consistencia.



I: Aislamiento.



D: Durabilidad.

A continuación se detallarán estas cuatro propiedades; primero aislamiento, segundo durabilidad, tercero atomicidad y finalmente consistencia.

Aislamiento (I) “Esta propiedad asegura que no sean afectadas entre sí las transacciones, en otras palabras esto asegura que la realización de dos o más transacciones sobre la misma información sean independientes y no generen ningún tipo de error” [1] Podemos tener una gran cantidad de clientes que operan en una base de datos (como se muestra en la imagen), en que cada cliente piensa que está operando por su cuenta. Así como se comentó en la Lección anterior, cada cliente emite en el sistema de base de datos una secuencia de transacciones. Así que un primer cliente podría emitir primero la transacción T1, a continuación, T2, T3, y así sucesivamente. Un segundo cliente podría emitir una transacción T9, T10, T11.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 206 de 253

Como recordatorio, cada transacción en sí puede ser una secuencia de instrucciones. Por lo tanto estas transacciones, podrían ser una instrucción, dos instrucciones, tres instrucciones y así sucesivamente, y finalmente estas instrucciones serán tratadas como una unidad. De manera que la propiedad aislante es implementada de una muy específica forma normal llamada secuenciación. La secuenciación quiere decir que las operaciones pueden ser intercaladas entre los clientes, pero la ejecución debe ser equivalente a un orden secuencial (serial) de todas las transacciones. En base a la imagen anterior, el propio sistema puede ejecutar todas las instrucciones dentro de cada operación y al mismo tiempo por cada cliente, pero tiene que garantizar que el comportamiento de la base de datos es equivalente a una secuencia en orden.

Ejemplo Supongamos cliente C1 emite T1, T2 transacciones y el cliente C2 T3, T4 transacciones simultáneamente. ¿Cuántas “ordenes secuenciales equivalentes” distintas hay en estas 4 transacciones? a. 2

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 207 de 253

b. 4 c. 6 d. 24 La alternativa correcta es (c), puesto que las combinaciones posibles son las siguientes: T1,T2,T3,T4; T1,T3,T2,T4; T1,T3,T4,T2;T3,T1,T2,T4; T3,T1,T4,T2; T3,T4,T1 ,T2.

Ahora, podemos preguntarnos cómo el sistema de base de datos podría garantizar este nivel de coherencia manteniendo la “intercomunicación” de las operaciones. Esto se logra con la utilización de protocolos que se basan en bloquear partes de la base de datos. Volveremos a un ejemplo de la Lección anterior. UPDATE College SET enrollment = enrollment + 500 WHERE cName = 'UCHILE';

concurrente con UPDATE College SET enrollment = enrollment + 1000 WHERE cName = 'UCHILE';

En este ejemplo dos clientes modifican la matricula a la universidad UCHILE. Llamaremos a la primera instrucción T1 y a la segunda T2, así que cuando realizamos estas transacciones en el sistema, la secuenciación esta garantizada, desde ahí tendremos un comportamiento equivalente ya sea para T1 seguido por T2, o T2 seguido por T1. Así que, en este caso, cuando empezamos con nuestra matrícula en 15.000, bien la ejecución correcta tendrá una matricula final de 16.500, resolviendo nuestros problemas de concurrencia. Ahora se explicará otro ejemplo de la Lección anterior. UPDATE Apply SET major = 'history' WHERE sID = 1;

concurrente con UPDATE Apply SET decision = 'Y' WHERE sID = 1;

En este ejemplo, el primer cliente realizó la modificación de major del estudiante sID =

1 en la tabla Apply y el segundo fue la modificación de decision del estudiante sID = 1. Y hemos visto que si permitimos que estas instrucciones se ejecuten de manera intercalada, sería posible que sólo uno de las dos modificaciones se realicen. Una vez Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 208 de 253

más, con la secuenciación vamos a obtener un comportamiento que garantiza que es equivalente a T1 seguido por T2 ó T2 seguido por T1. Y en ambos casos, tanto los cambios se reflejarán en la base de datos que es lo que nos gustaría.

Durabilidad (D) “Es la propiedad de las transacciones que asegura que una vez finalizada su ejecución, sus resultados son permanentes a pesar de otras consecuencias, como por ejemplo, si falla el disco duro el sistema aún será capaz de recordar todas las transacciones que han sido realizadas en el sistema” [1] Aquí sólo debemos mirar un cliente y lo que está sucediendo. Así que digamos que tenemos a nuestro cliente (siguiente imagen), que está emitiendo una secuencia de instrucciones (S1, S2, ..., Sn) a la base de datos. Y cada transacción (T1, T2, ..., Tn) que realiza el cliente, es una secuencia de instrucciones (S) y que al finalizar cada instrucción (S) recibe un “commit” confirmación.

Si el sistema deja de funcionar después de las transacciones “commits”, todos los efectos de las transacciones quedan en la base de datos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 209 de 253

Entonces, específicamente, si en algún momento esto ocurre, si hay una falla por cualquier razón, el cliente puede asegurarse que la base de datos ha sido afectada por la transacción, y cuando el sistema vuelva a funcionar, los efectos seguirán ahí. ¿Es posible garantizar esto, siendo que los sistemas de base de datos mueven información entre el disco duro y la memoria y una falla puede ocurrir en cualquier momento?. Son protocolos no tan complicados que son usados y están basados en el concepto de logging.

Atomicidad (A) “Cualquier cambio de estado que produce una transacción es atómico, es decir, ocurren todos o no ocurre ninguno. En otras palabras, esta propiedad asegura que todas las acciones de la transacción se realizan o ninguna de ellas se lleva a cabo; la atomicidad requiere que si una transacción se interrumpe por una falla, sus resultados parciales deben ser deshechos” [1] De nuevo, sólo veremos un cliente que haya dado a conocer una serie de transacciones a la base de datos. Y vamos a ver la transacción T2 que a su vez es una secuencia de instrucciones seguidas por una confirmación (commit). El caso que la atomicidad trabaja, es donde existe una falla durante la ejecución de la transacción, después que se ha sido “enviado”. Lo que la propiedad quiere decir que, incluso en presencia de fallos del sistema, cada transacción se ejecuta todo o nada en la base de datos. También se utiliza un mecanismo de log-in, específicamente, cuando el sistema se recupera de un accidente y hay un proceso por el cual los efectos parciales de las transacciones

que

se

estaban

ejecutando

al

momento

de

la

falla,

se

“deshacen/descartan”.

Ejemplo Considere la posibilidad de una relación R (A) que contiene {(5), (6)} y dos transacciones: T1: UPDATE R SET A = A + 1; T2: UPDATE R SET A = 2 * A. Supongamos que ambas transacciones se presentan bajo la propiedad de aislamiento y atomicidad. ¿Cuál de los siguientes NO es un posible estado final de R? a. {(10,12)}

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 210 de 253

b. {(11,13)} c. {(11,12)} d. {(12,14)} La respuesta correcta es (c), puesto que la alternativa (a) se produce si no se completa T1. La alternativa (b) se produce si T2 se realiza antes que T1. La alternativa (d) se produce cuando T1 se realiza después que T2.

Deshacer (Rollback) Transacción 

Deshace los efectos parciales de una transacción.



Puede ser iniciada por el sistema o por el cliente.

Ahora realizaremos un ejemplo práctico en postgreSQL.

Ejemplo Tenemos la tabla colors, con sus respectivos atributos id y color, pero antes de comenzar debemos definir algunos conceptos: 

begin: Inicio de una transacción. Al ingresar esta clausula es posible recuperar errores que puedan ocurrir.



savepoint: Con esta sentencia se realiza un commit hasta el punto que se está seguro que no posee errores. La diferencia concommit es que no se finaliza la transacción.



rollback: Deshace todos los cambios que se hayan realizado desde la sentencia begin ó hasta donde se haya confirmado consavepoint.



commit: Confirma y termina la transacción con los cambios establecidos.

SELECT * FROM colors; id | color ----+-------1 | yellow 2 | blue 3 | red 4 | green (4 rows)

Ahora comenzamos la transacción con commit. begin;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 211 de 253

Retornando postgreSQL como resultado BEGIN. Realizamos una modificación en los colores yellow por black. UPDATE colors SET color='black' WHERE color='yellow'; SELECT * FROM colors; id | color ----+------2 | blue 3 | red 4 | green 1 | black (4 rows)

Ahora confirmaremos que hasta aquí está todo bien. savepoint b;

Retornando postgreSQL como resultado SAVEPOINT. Volvemos a modificar un color de la tabla blue por orange. UPDATE colors SET color='orange' WHERE color='blue'; SELECT * FROM colors; id | color ----+-------3 | red 4 | green 1 | black 2 | orange (4 rows)

Pero nos damos cuenta nos equivocamos y no era orange el color que deseábamos, entonces volvemos al punto que guardamos anteriormente. rollback TO b;

Retornando postgreSQL como resultado ROLLBACK. Volviendo al punto anterior. SELECT * FROM colors; id | color ----+------2 | blue 3 | red

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 212 de 253

4 | green 1 | black (4 rows)

Consistencia (C) “Esta propiedad establece que solo los valores o datos válidos serán escritos en la base de datos; si por algún motivo una transacción que es ejecutada viola esta propiedad, se aplicará un rollback a toda transacción dejando a las bases de datos en su estado de consistencia anterior. En caso de que la transacción sea ejecutada con éxito, la base de datos pasará de su estado de consistencia anterior a un nuevo estado de consistencia.” La propiedad de consistencia habla de cómo las transacciones interactúan con las restricciones de integridad que pueden existir en una base de datos. En concreto, cuando tenemos varios clientes que interactúan con la base de datos de manera concurrente, podemos tener una configuración en la que cada cliente puede asumir que cuando comienza a operar sobre una base de datos, satisfaga todas las restricciones de integridad.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 213 de 253

Lección XXVII – Vistas: Definición y usos Las vistas se basan en una visión bases de datos de tres niveles, que lo componen: 

Capa física: En el nivel inferior, se encuentran los datos reales almacenados en un disco.



Capa conceptual: Es la abstracción de las relaciones (o tabla) de los datos almacenados en un disco.



Capa de lógica: la última capa es una abstracción por encima de las relaciones es lo que se conoce como vistas (views).

Definición Una vista es una tabla virtual derivada de las tablas reales de una base de datos. Las vistas no se almacenan en la base de datos, sólo se almacena una definición de consulta, es decir una vista contiene la instrucción SELECT necesaria para crearla. Resultado de la cual se produce una tabla cuyos datos proceden de la base de datos o de otras vistas. Eso asegura que los datos sean coherentes al utilizar los datos almacenados en las tablas. Si los datos de las relaciones cambian, los de la vista que utiliza esos datos también cambia. Por todo ello, las vistas gastan muy poco espacio de disco. Como una vista se define como una consulta sobre las relaciones, aún pertenecen en el modelo de datos relacional.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 214 de 253

Para definir una vista V, se especifica una consulta de Vista en SQL, a través de un conjunto de tablas existentes (R1, R2,…Rn). Vista V= ConsultaSQL(R1, R2, …, Rn)

La vista V, entonces, se puede pensar como una tabla de los resultados de la consulta. Ahora supongamos que se desea ejecutar una consulta Q en la base de datos. Esta no es una consulta de vista, es sólo una consulta como las vistas anteriormente en el curso. La consulta Q hace referencia a V. V := ViewQuery(R1,R2,…,Rn) Evaluate Q

Lo que realmente hace Q es consultar o editar las relaciones R1, R2,…, Rn instanciadas por V. El DBMS realiza automáticamente el proceso de rescritura sobre las relaciones.

Usos de las vistas Las vistas se emplean para: 

Realizar consultas complejas más fácilmente: Las vistas permiten dividir la consulta en varias partes.



Proporcionar tablas con datos específicos: Las vistas permiten ser utilizadas como tablas que resumen todos los datos, así como también permiten ocultar ciertos datos. Cuando ese se requiere un detalle que no corresponde precisamente a las relaciones.



Modularidad de acceso a base de datos: las vistas se pueden pensar en forma de módulos que nos da acceso a partes de la base de datos. Cuando ese detalle que se requiere no corresponde precisamente a las relaciones.

Las aplicaciones reales tienden a usar un muchas vistas, por lo que cuanto más grande es la aplicación, más necesario es que haya modularidad, para facilitar determinadas consultas o para ocultar los datos. Las vistas entonces son el mecanismo para alcanzar dichos objetivos.

Creación de una vista 

CREATE VIEW : Define una tabla lógica a partir de una o más tablas físicas o de



otras vistas. DROP VIEW : Elimina una definición de vista (y cualquier vista definida a partir de ella).

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 215 de 253

Create View Vname(A1,A2,…,An) As

Vname es el nombre que se le asigna a la vista, A1, A2,…, An son los nuevos nombres de los atributos que tendrá la vista.

Ejemplo 1 Se utiliza una base de datos con las siguientes relaciones:

Specie(sName−−−−−−,comName,family) Esta tabla almacena los datos que caracterizan las especies animales. Almacena el nombre científico en sName , el nombre común con el que se le conoce es guardado en comName y la familia family a la que pertenece la especie.

Zoo(zID−−−,zooName,size, budget) La relación Zoo almacena los datos de los zoológicos. Un zID que es la primary key, el nombre en zooName, size es el tamaño en hectáreas y presupuesto budget en unidades monetarias.

Animal(zID, sName, aName−−−−−−−−−−−−−−−,country) La tabla animal guarda los datos de los animales que habitan cada zoológico. El atributo zID es clave foránea a Zoo, se refiere al zoológico en el que se encuentra un animal, sName es clave foránea a la Specie que pertenece, country es el país de procedencia. La creación de las relaciones y los valores que se utilizarán en este ejemplo se encuentran en el siguiente archivo Se crea una vista: CREATE VIEW View1 AS SELECT zID, sName FROM Animal WHERE aName = 'Tony' and country = 'China';

Como ya se mencionó para crear una vista se usan las palabras clave CREATE VIEW especificando el nombre de la vista view1 . Luego se declara la consulta en SQL estándar. Dicha consulta selecciona zID y sName de los animales que se llamen ‘Tony’ y procedan de ‘China’ . PostgreSQL retorna:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 216 de 253

CREATE VIEW

Al realizar un SELECT de la vista, PostgreSQL la despliega como si fuera una relación cualquiera. DBviews=# SELECT * FROM View1; zid | sname -----+-----------------------5 | Ailuropoda melanoleuca 1 | Panthera leo 3 | Panthera tigris (3 rows)

Sin embargo la vista no almacena los datos, sino que estos siguen almacenados en la relación Animal. Observe que ocurre cuando se insertan más datos en Animal INSERT INTO Animal (zID, sName, aName, country) VALUES (4,'Ailuropoda melanoleuca', 'Tony', 'China'), (3,'Panthera leo', 'Tony', 'China'), (1,'Loxodonta africana', 'Tony', 'China');

La View1 se actualiza automáticamente: DBviews=# SELECT * FROM View1; zid | sname -----+-----------------------5 | Ailuropoda melanoleuca 1 | Panthera leo 3 | Panthera tigris 4 | Ailuropoda melanoleuca 3 | Panthera leo 1 | Loxodonta africana (6 rows)

Ejemplo 2 Si se desea renombrar los atributos de una vista, la sentencia debe ser: CREATE VIEW Viewt(IDzoo,specieName) as SELECT zID, sName FROM Animal WHERE aName = 'Tony' and country = 'China';

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 217 de 253

PostgreSQL retorna: CREATE VIEW

La vista Viewt fue definida igual que View1, pero esta vez los atributos que selecciona son renombrados, zID se despliega como IDzooy sName como specieName DBviews=# SELECT * FROM Viewt; idzoo | speciename -------+-----------------------5 | Ailuropoda melanoleuca 1 | Panthera leo 3 | Panthera tigris 4 | Ailuropoda melanoleuca 3 | Panthera leo 1 | Loxodonta africana (6 rows)

Para seleccionar un atributo de Viewt debe hacerse con el nuevo nombre asignado: DBviews=# SELECT zID FROM viewt; ERROR: column "zid" does not exist LÍNEA 1: select zid from viewt; DBviews=# SELECT idzoo FROM viewt; idzoo ------5 1 3 4 3 1 (6 rows)

Ejemplo 3 A pesar que la vista no almacena valores, solo los referencia, se puede trabajar como si fuera una relación real. La siguiente consulta selecciona Zoo.zID, zooName y size de la tabla Zoo y de la vista View1, donde zID de la tabla Zoo sea igual al zID de View1, recordar

que View1 y sName de View1 sea

‘Ailuropoda

melanoleuca’

y

que size de Zoo sea menor a 10. SELECT Zoo.zID, zooName, size FROM Zoo, View1 WHERE Zoo.zID = View1.zID and sName = 'Ailuropoda melanoleuca' and size < 10;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 218 de 253

zid | zooname | size -----+------------+-----4 | London Zoo | 9 (1 row)

Ejemplo 4 Una vista también puede referenciar a otra vista. Para ello se crea una vista llamada View2 que referencia a la tabla Zoo y a la vista View1. CREATE view View2 as SELECT Zoo.zID, zooName, size FROM Zoo, View1 WHERE Zoo.zID = View1.zID and sName = 'Panthera leo' and

budget > 80;

La sentencia crea una vista que almacena datos de Zoo que poseen animales ‘Panthera leo’,

la

búsqueda

la

hace

dentro

de

los

datos

que

posee View1,

además

el budget de Zoo debe ser mayor a 80. Cabe mencionar que al ejecutar este comando no muestra el resultado, sólo crea la vista. Luego View2 puede ser utilizada en sentencias SELECT de la misma forma que las tablas: DBviews=# SELECT * FROM View2; zid | zooname | size -----+---------------+-----1 | Metropolitano | 4 3 | San Diego | 14 (2 rows) DBviews=# SELECT * FROM View2 WHERE size > 5; zid | zooname | size -----+-----------+-----3 | San Diego | 14 (1 row)

Finalmente se eliminan las vistas: DROP VIEW View1; DROP VIEW View2; DROP VIEW View3;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 219 de 253

Lección XXVIII - Vistas: modificaciones automáticas

Vistas

y

Por lo general, las tablas son constituidas por un conjunto de definiciones y almacenan datos físicos. Por otra parte las vistas se encuentran en un nivel superior, es decir son constituidas por un conjunto de definiciones, pero no almacenan datos, pues utilizan los datos que están almacenadas en las tablas. Es por ello que, podemos decir que las vistas son por un conjunto de definiciones, pero no almacenan datos, pues utilizan los datos que están almacenadas en las tablas. Es decir que las vistas se consideran “tablas virtuales”. Su sintaxis es: CREATE VIEW "view_name" AS "sql_instruction";

Donde: 1. “view_name” : corresponde al nombre de la vista. 2. “sql_instruction”: corresponde a alguna instrucción SQL vista hasta ahora, es decir, operaciones de inserción y/o modificación. Toda Base de Datos (BD), puede ser vista como un árbol de 3 niveles: 1. La raíz, compuesta por la parte física de la BD, es decir el(los) disco(s) duro(s). 2. El tronco, compuesto por las relaciones de dentro de la BD, es decir, su parte conceptual. 3. Las ramas, que corresponde a la parte lógica, se refiere a las relaciones que nacen desde las relaciones del tronco (tablas) y/o relaciones desde las ramas (otras vistas). Realizar modificaciones en una vista no tiene mucho sentido, pues al no almacenar información,estos cambios simplemente se perderían. No obstante, si el objetivo de estos cambios corresponde a modificar la(s) tabla(s), dicha modificación adquiere sentido.

Nota Por modificación se entienden operaciones de tipo INSERT, UPDATE y DELETE. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 220 de 253

Estas modificaciones en las vistas deben ser traducidas a modificaciones que afecten a las tablas involucradas. Esta Lección está orientada a profundizar como se automatiza este proceso.

Reglas Dentro del estándar de SQL, existen 4 reglas para que una “vista sea modificable”, es decir si se modifica la vista en cuestión, se modifica la relación/tabla desde donde nace la vista. Estas reglas son: 1. Hacer un SELECT. de una tabla, no de una unión (JOIN). Además la tabla no puede ser DISTINCT. 2. Si un atributo no esta en la vista, debe soportar valores NULL o uno por defecto. 3. Si la vista está sobre la relación/tabla T, las subconsultas no pueden referirse a T, pero si a otras relaciones/tablas. 4. En una vista, no se puede usar GROUP BY o AGREGGATION.

Contexto Supongamos que durante el primer semestre de clases, específicamente a un mes de que se implementara el sistema de postulación a Establecimientos Educacionales, postulan 4 estudiantes más. Es por ello que se decide realizar una mejora utilizando vistas, debido a sus propiedades y simplificar complejas consultas sobre tablas, ya sea seleccionando (SELECT.) y/o modificando(INSERT, UPDATE, DELETE) datos. Además se utilizarán criterios más estrictos, pues en la versión del sistema, se permitió la entrada a alumnos que no tenían promedio arriba de 50: se le denominará sistema de postulación 2.0 (BETA). Es por ello que, para esta Lección, se utilizará el sistema de Postulación de Estudiantes a Establecimientos Educacionales: CREATE TABLE College(cName VARCHAR(20), state VARCHAR(30), enrollment INTEGER, PRIMARY KEY(cName)); CREATE TABLE Student(sID SERIAL, sName VARCHAR(20), Average INTEGER, PRIMARY KEY(sID)); CREATE TABLE Apply(sID INTEGER, cName VARCHAR(20), major VARCHAR(30), decision BOOLEAN, PRIMARY KEY(sID, cName, major));

con los siguientes datos para la tabla College, Student y Apply respectivamente: 4 establecimientos:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 221 de 253

INSERT INSERT INSERT INSERT

INTO INTO INTO INTO

College College College College

VALUES VALUES VALUES VALUES

('Stanford','CA',15000); ('Berkeley','CA',36000); ('MIT', 'MA',10000); ('Harvard', 'CM',23000);

Nota Estos datos no son necesariamente reales, ni se hicieron investigaciones para corroborar su veracidad (estado o capacidad), pues se escapa al alcance de este curso. Sólo buscan ser meras herramientas para el desarrollo de los ejemplos de esta Lección.

3 estudiantes: INSERT INTO Student (sName, Average) VALUES ('Clark', INSERT INTO Student (sName, Average) VALUES ('Marge', INSERT INTO Student (sName, Average) VALUES ('Homer',

70); 85); 50);

8 postulaciones: INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO

Apply Apply Apply Apply Apply Apply Apply Apply

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1, (1, (2, (2, (2, (3, (3, (3,

'Stanford', 'Berkeley', 'Harvard' , 'MIT' , 'Berkeley', 'MIT' , 'Harvard' , 'Harvard' ,

'science' 'science' 'science' 'engineering' 'science' 'science' 'engineering' 'natural history'

, , , , , , , ,

True); False; False; True); True); True); True); True);

Nota Estos datos no son necesariamente reales, ni se hicieron investigaciones para corroborar su veracidad (mención académica), pues se escapa al alcance de este curso. Sólo buscan ser meras herramientas para el desarrollo de los ejemplos de esta Lección.

Modificación automática de vistas y tablas Supongamos que deseamos seleccionar a los Estudiantes que postularon y fueron aceptados en en Ciencias, en cualquier Establecimiento Educacional, pero utilizando vistas: CREATE VIEW scAccepted as SELECT sid, sname FROM Apply WHERE major='science' and decision = true;

Esta vista cuenta con las 4 restricciones impuestas por el estándar SQL para que sea considerada como “vista modificable”:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 222 de 253

1. Se selecionan datos solamente de la tabla Apply. 2. Los atributos de dicha tabla no contienen alguna restricción de tipo NOT NULL. 3. No hay subconsultas que se refieran a la tabla Apply. 4. No se utiliza GROUP BY o AGREGGATION. Si se seleccionan los datos de la vista: SELECT * FROM scAccepted;

su salida es: sid | cname ----+---------1 | Stanford 2 | Berkeley 3 | MIT

Ejemplo 1 Supongamos que se desea eliminar de la vista al estudiante con sID = 3 (Homer), pues realizó trampa en esta prueba. La idea es eliminarlo de la vista y a la vez, de la tabla Apply, para no tener que realizar 2 operaciones: DELETE FROM scAccepted WHERE sid = 3;

No obstante: ERROR: you cannot delete from view "scaccepted" HINT: You need a unconditional ON DELETE DO INSTEAD rule or INSTEAD OF DELETE trigger.

Pues MySQL es el único sistema, en relación a PostgreSQL o SQLite que permite un manejo de datos de este tipo. Estos últimos permiten la modificación en base a reglas y/o triggers solamente. Advertencia Si bien el motor de Base de Datos utilizado para este curso, no soporta el tópico de esta Lección, se verán casos y consejos para utilizarlos en sistemas que funcionen. De todos modos, los ejemplos se construyen utilizando PostgreSQL.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 223 de 253

Ejemplo 2 Supongamos que deseamos crear una vista que contenga a los Estudiantes que postularon a Ciencias o Ingeniería. CREATE VIEW sceng as SELECT sid, cname, major FROM Apply WHERE major = 'science' or major = 'engineering';

Verificando a través de una selección: SELECT * FROM sceng;

la salida es: sid | cname | major ----+----------+------------1 | Stanford | science 1 | Berkeley | science 2 | Harvard | science 2 | MIT | engineering 2 | Berkeley | science 3 | MIT | science 3 | Harvard | engineering

Si deseamos agregar una fila, digamos: INSERT INTO sceng VALUES (1, 'MIT', 'science');

No hay problemas, pues cuenta con las 4 reglas de “vistas modificables”. El ejemplo funciona en MySQL y en la teoría.

Ejemplo 3 Supongamos que deseamos agregar una fila a la vista scAccepted, INSERT INTO scAccepted VALUES (2, 'MIT');

Si bien podría pensarse que, como la vista contiene valores determinados para el atributo major y decision, bastaría con agregar sólo los restantes, es decir sID y cName. Al momento de seleccionar todos los datos de la vista, no se verá esta nueva fila, debido a que: 1. El hecho de que la vista cuente con valores de selección no quiere decir que ellos sean de inserción. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 224 de 253

2. Al no tener los atributos major y decision con valores ‘science’ y ‘true’ respectivamente no pasan el filtro de la vista. Sin embargo en la tabla (Apply en este caso), la nueva fila se agrega. Pero claro, no tiene sentido, pues los campos major y decisionson NULL.

Ejemplo 4 En los sistemas que se permite el cambio automático, es posible evitar inconsistencias como la que se generó en el ejemplo 3, agregando al final de la vista: CREATE VIEW scAccepted2 as SELECT sid, sname FROM Apply WHERE major='science' and decision = true; WITH CHECK OPTION;

No obstante esta opción no está implementada en PostgreSQL, por lo que el siguiente error aparece al ejecutar la consulta que está arriba: ERROR: WITH CHECK OPTION is not implemented.

Conclusiones 1. Los cambios automáticos sólo se pueden realizar a “tablas modificables”, es decir que cumplan con las 4 reglas. 2. PostgreSQL no soporta este tipo de modificación, sólo la permite a través de reglas y/o triggers.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 225 de 253

Lección XXIX materializadas

-

Vistas:

Vistas

Introducción En las Leccións anteriores se vio la vista virtual que es el tipo usual de vista que se define como una consulta de la base de datos. En esta Lección se verán las vistas materializadas, que almacena el resultado de la consulta en una tabla caché real. Es una solución muy utilizada en entornos de almacenes de datos (datawarehousing), donde el acceso frecuente a las tablas básicas resulta demasiado costoso.

Definición Una vista materializada almacena físicamente los datos resultantes de ejecutar la consulta definida en la vista. Inicialmente se almacenan los datos de las tablas base al ejecutar la consulta y se actualiza periódicamente a partir de las tablas originales. Las vistas materializadas constituyen datos redundantes, en el sentido de que su contenido se puede deducir de la definición de la vista y del resto del contenido de la base de datos. Se define una vista especificando una consulta de Vista en SQL, a través de un conjunto de tablas existentes (R1, R2,…Rn). Vista V= ConsultaSQL(R1, R2, …, Rn)

De esta forma se crea en realidad una tabla física V con el esquema del resultado de la consulta. Puede referirse a V como si fuese una relación, ya que en realidad es una tabla almacenada en una base de datos.

Ventaja Poseen las mismas ventajas de visitas virtuales. La diferencia radica en que las vistas materializadas mejoran el rendimiento de consultas sobre la base de datos, pues proporciona un acceso mucho más eficiente. Con la utilización de vistas materializadas se logra aumentar el rendimiento de las consultas SQL además de ser un método de optimización a nivel físico en modelos de datos muy complejos y/o con muchos datos.

Desventajas 

Incrementa el tamaño de la base de datos

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 226 de 253



Posible falta de sincronía, es decir, que los datos de la vista pueden estar potencialmente desfasados con respecto a los datos reales. Al contener físicamente los datos de las tablas base, si cambian los datos de estas tablas no se reflejarán en la vista materializada.

Mantenimiento de las vistas Como se mencionó anteriormente un problema que poseen las vistas materializadas es que hay que mantenerlas actualizadas cuando se modifican los datos de las tablas bases

que

emplea

la

vista.

La

Práctica

de

actualizar

una

vista

se

denomina mantenimiento de la vista materializada. También hay que tener presente que al modificar una vista debe actualizarse la(s) tabla(s) de origen(es), pues las vistas y las relaciones deben estar sincronizadas. Una manera de realizar mantenimiento es utilizando Triggers para la inserción, la eliminación y la actualización de cada relación de la definición de la vista. Los Triggers deben modificar todo el contenido de la vista materializada. Una mejor opción es editar sólo la parte modificada de la vista materializada, lo que se conoce como mantenimiento incremental de la vista. Los sistemas modernos de bases de datos proporcionan más soporte directo para el mantenimiento incremental de las vistas. Los programadores de bases de datos ya no necesitan definir Triggers para el mantenimiento de las vistas. Por el contrario, una vez que se ha declarado materializada una vista, el sistema de bases de datos calcula su contenido y actualiza de manera incremental el contenido cuando se modifican los datos subyacentes.

Creación de una vista En algunos sistemas de gestión de bases de datos poseen las palabras reservadas CREATE MATERIALIZED VIEW que define una vista materializada a partir de una o más tablas físicas. Forma general: CREATE MATERIALIZED VIEW viewName AS SELECT ... FROM ... WHERE ...

Sin embargo en esta Lección se utilizará otro método para tratar las vistas materializada. Primero se crea la tabla matviews para guardar la información de una vista materializada. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 227 de 253

CREATE TABLE matviews ( mv_name NAME NOT NULL PRIMARY KEY , v_name NAME NOT NULL , last_refresh TIMESTAMP WITH TIME ZONE );

Donde:   

mv_name: es el nombre de la vista materializada representada por esta fila. v_Name: es el nombre de la vista que se basa la vista materializada. last_refresh: La hora de la última actualización de la vista materializada.

Ahora se crea una función create_matview escrita en PL/pgSQL. Dicha función inserta una fila en la tabla matviews y crea la vista materializada. Recibe el nombre de la vista materializada, y el nombre de la vista que se basa. Tenga en cuenta que se debe crear la vista virtual en la que se basa, más adelante se explica cómo se crea y utiliza esta vista. Esta función ve si una vista materializada con el nombre que recibe ya está creada. Si es así, se produce una excepción. De lo contrario, se crea una nueva tabla con la vista, e inserta una fila en la tabla matviews. CREATE OR REPLACE FUNCTION create_matview(NAME, NAME) RETURNS VOID SECURITY DEFINER LANGUAGE plpgsql AS ' DECLARE matview ALIAS FOR $1; view_name ALIAS FOR $2; entry matviews%ROWTYPE; BEGIN SELECT * INTO entry FROM matviews WHERE mv_name = matview; IF FOUND THEN RAISE EXCEPTION ''Materialized view ''''%'''' already exists.'', matview; END IF; EXECUTE ''REVOKE ALL ON '' || view_name || '' FROM PUBLIC''; EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC''; EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM '' || view_name; EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC''; EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 228 de 253

INSERT INTO matviews (mv_name, v_name, last_refresh) VALUES (matview, view_name, CURRENT_TIMESTAMP); RETURN; END ';

La función drop_matview elimina la vista materializada y la entrada de matviews , Dejando la vista virtual sola. CREATE OR REPLACE FUNCTION drop_matview(NAME) RETURNS VOID SECURITY DEFINER LANGUAGE plpgsql AS ' DECLARE matview ALIAS FOR $1; entry matviews%ROWTYPE; BEGIN SELECT * INTO entry FROM matviews WHERE mv_name = matview; IF NOT FOUND THEN RAISE EXCEPTION ''Materialized view % does not exist.'', matview; END IF; EXECUTE ''DROP TABLE '' || matview; DELETE FROM matviews WHERE mv_name=matview; RETURN; END ';

La función refresh_matview actualiza las vistas materializadas de manera que los datos no se convierten en obsoletos. Esta función sólo necesita el nombre de la matview. Se utiliza un algoritmo que elimina todas las filas y vuelve a colocarlas en la vista. CREATE OR REPLACE FUNCTION refresh_matview(name) RETURNS VOID SECURITY DEFINER LANGUAGE plpgsql AS ' DECLARE matview ALIAS FOR $1; entry matviews%ROWTYPE; BEGIN SELECT * INTO entry FROM matviews WHERE mv_name = matview; IF NOT FOUND THEN RAISE EXCEPTION ''Materialized view % does not exist.'', matview; END IF;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 229 de 253

EXECUTE ''DELETE FROM '' || matview; EXECUTE ''INSERT INTO '' || matview || '' SELECT * FROM '' || entry.v_name; UPDATE matviews SET last_refresh=CURRENT_TIMESTAMP WHERE mv_name=matview; RETURN; END ';

Ejemplo Para este ejemplo se utilizarán las funciones mostradas anteriormente, primero se instala el lenguaje plpgsql: viewm=# CREATE PROCEDURAL LANGUAGE plpgsql; CREATE LANGUAGE

Con el lenguaje ya instalado se crea la relación matviews y se agregar las funciones create_matview, drop_matview y refresh_matview a la base de datos. Se crea la relación game_score, también se crea la vista virtual player_total_score_v CREATE TABLE game_score ( pname VARCHAR(255) NOT NULL, score INTEGER NOT NULL); CREATE VIEW player_total_score_v AS SELECT pname, sum(score) AS total_score FROM game_score GROUP BY pname;

Dado que muchos de los equipos juegan todos los días, y correr la vista es un poco caro, se decide implementar una vista materializada en player_total_score_v . Para ello se crea la vista invocando la función create_matview pasándole por parámetros el nombre de la vista materializada player_total_score_mv y el nombre de la vista virtual player_total_score_v. viewm=# SELECT 'player_total_score_v');

create_matview('player_total_score_mv',

create_matview ---------------(1 row)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 230 de 253

Al ejecutar un SELECT sobre la vista materializada se observa que está creada y vacía: viewm=# SELECT * FROM player_total_score_mv; pname | total_score -------+------------(0 row)

Los datos de la vista se almacena en la relación matviews SELECT * FROM matviews; mv_name | v_name | last_refresh -----------------------+----------------------+-----------------------------player_total_score_mv | player_total_score_v | 2013-02-11 10:54:56.08571-03 (1 row)

Se insertan valores en la relación game_score: viewm=# INSERT INTO game_score ( pname, score) INSERT 0 2

VALUES ('UCH',2), ('SW',4);

Al ejecutar un SELECT se observa que la vista se mantiene vacía: viewm=# SELECT * FROM player_total_score_mv; pname | total_score -------+------------(0 row)

Para actualizar la vista materializada se debe ocupar la función refresh_matview: viewm=# SELECT refresh_matview('player_total_score_mv'); refresh_matview ----------------(1 row)

Esta vez al volver a seleccionar la vista materializada aparecen los valores insertados en la relación game_score viewm=# SELECT * FROM player_total_score_mv; pname | total_score -------+------------SW | 4 UCH | 2 (2 rows)

Para eliminar la vista materializada se usa la función drop_matview:

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 231 de 253

viewm=# SELECT drop_matview('player_total_score_mv'); drop_matview -------------(1 row)

Al hacer un SELECT a la vista materializada, aparece un error pues ya no existe. viewm2=# SELECT * FROM player_total_score_mv; ERROR: relation "player_total_score_mv" does not exist LÍNEA 1: SELECT * from player_total_score_mv;

Se revisa la relación matviews y aquí también se eliminó la vista materializada: viewm=# SELECT * from matviews; mv_name | v_name | last_refresh ---------+--------+-------------(0 row)

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 232 de 253

Lección XXX – Mantenimiento de la Base de Datos Introducción Una base de datos requiere de una mantención periódica, de lo contrario puede desarrollar problemas en una o más áreas, lo cual a largo plazo puede provocar un mal rendimiento de la aplicación, llegando incluso a perdidas de datos. Existen actividades que el administrador del sistema gestor de bases de datos debe realizar habitualmente. En el caso de PostgreSQL, mantenimiento de los identificadores internos y de las estadísticas de planificación de las consultas, a una reindexación periódica de las tablas, y al tratamiento de los ficheros de registro.

Rutinas de mantenimiento y monitoreo Vacuum VACUUM es el proceso que realiza una limpieza a la base de datos en PostgreSQL. Se

eliminan definitivamente tuplas marcadas para borrar y se efectúa una reorganización de datos a nivel físico. El VACUUM se realiza periódicamente se para: 

Recuperar espacio en disco perdido por datos borrados o actualizados. En operaciones normales de PostgreSQL, las tuplas que se eliminan o quedan obsoletas por una actualización no se eliminan físicamente de su tabla, sino que permanecen hasta se ejecuta un VACUUM. Por lo tanto es necesario hacer VACUUM periódicamente, especialmente en las tablas frecuentemente actualizadas.



Actualizar las estadísticas de datos utilizados por el planificador de consultas SQL.



Protegerse ante la pérdida de datos por reutilización de identificadores de transacción.

sintaxis VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 233 de 253

VACUUM puede recibir ciertos

parámetros para realizar los diferentes tipos de

vaciamiento: 

FREEZE La opción FREEZE esta depreciada y será removida en liberaciones

futuras. 

FULL Selecciona el vaciamiento “completo”, el cual recupera más espacio, pero

toma mucho más tiempo y bloquea exclusivamente la tabla. 

VERBOSE Imprime un reporte detallado de la actividad de vaciamiento para cada

tabla. 

ANALYZE Actualiza las estadísticas usadas por el planeador para determinar la

forma más eficiente de ejecutar una consulta. 

tabla: El nombre de una tabla específica a vaciar. Por defecto se toman todas las tablas de la base de datos actual.



columna: El nombre de una columna específica a analizar. Por defecto se toman todas las columnas. Este comando es muy útil para automatizar vaciamientos a través de cualquier sincronizador de Prácticas.

Asimismo, existe la opción del Autovacuum, cuya funcionalidad es ir realizando de manera paulatina la mantención de nuestra base. Previamente y antes de activar esta funcionalidad, es recomendable leer sobre las consideraciones a tener en cuenta, para no degradar la performance de nuestro servidor.

Ejemplo: Se ejecuta VACUUM sobre una base de datos existente. demo=# VACUUM; VACUUM

Se ejecuta VACUUM con parámetro FULL sobre una base de datos existente demo=# VACUUM FULL; VACUUM

Se realiza VACUUM sobre la relación game_score. demo=# VACUUM game_score; VACUUM

En caso de que haya algún problema o acción adicional a realizar, el sistema lo indicará: demo=# VACUUM;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 234 de 253

WARNING: some databases have not been vacuumed in 1613770184 transactions HINT: Better vacuum them within 533713463 transactions, or you may have a wraparound failure.

Reindexación Para facilitas la obtención de información de una tabla se utilizan índices. El índice de una tabla permite encontrar datos rápidamente. Sin índice se debería recorrer secuencialmente toda la tabla para encontrar un registro. Es muy útil para bases de datos que posee mucha información. Una tabla se indexa por un campo o varios. Es importante identificar el o los datos por lo que sería útil crear un índice, aquellos campos por los cuales se realizan operaciones de búsqueda con frecuencia. Hay distintos tipos de índice: 

primary key: como ya se explicó anteriormente es la clave primaria, los valores deben ser únicos y además no pueden ser nulos.



index: crea un índice común, los valores no necesariamente son únicos y aceptan valores nulos. Se le puede asignar un nombre, por defecto se coloca el nombre “key”. Pueden ser varios por tabla.



unique: crea un índice para los cuales los valores deben ser únicos y diferentes, aparece un mensaje de error si intentamos agregar un registro con un valor ya existente. Permite valores nulos y pueden definirse varios por tabla.

La reindexación completa de la base de datos no es una Práctica muy habitual, pero puede mejorar sustancialmente la velocidad de las consultas complejas en tablas con mucha actividad.

Ejemplo: Se ejecuta el comando sobre la base de datos utilizada en la Lección 29: demo=# reindex NOTICE: table NOTICE: table NOTICE: table NOTICE: table NOTICE: table NOTICE: table NOTICE: table

database demo; "pg_class" was reindexed "pg_type" was reindexed "pg_statistic" was reindexed "sql_features" was reindexed "sql_implementation_info" was reindexed "sql_languages" was reindexed "sql_packages" was reindexed

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 235 de 253

Se utiliza las palabras reservadas reindex database agregando el nombre de la base de datos “demo”.

Ficheros de registro Una buena práctica es mantener archivos de registro de la actividad del servidor, al menos de los errores que origina. Durante el desarrollo de aplicaciones se puede disponer de un registro de las consultas efectuadas, aunque disminuye el rendimiento del gestor en bases de datos de mucha actividad, y puede no ser de mucha utilidad. De igual modo es conveniente disponer de mecanismos de rotación de los ficheros de registro; es decir, que periódicamente se mantenga un respaldo de estos ficheros y se empiecen unos nuevos, lo que permite tener un historial. PostgreSQL no proporciona directamente utilidades para realizar esta rotación, pero en la mayoría de sistemas Unix vienen incluidas utilidades como logrotate que realizan esta Práctica a partir de una planificación temporal. VACUUM demo=# VACUUM VERBOSE ANALYZE; INFO: analyzing "pg_catalog.pg_operator" INFO: "pg_operator": scanned 13 of 13 pages, containing 704 live rows and 0 dead rows; 704 rows in sample, 704 estimated total rows INFO: vacuuming "pg_catalog.pg_opfamily" INFO: index "pg_opfamily_am_name_nsp_index" now contains 68 row versions in 2 pages DETALLE: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM

Explain Este comando muestra el plan de ejecución que el administrador del sistema gestor de bases de datos Postgres genera para una consulta dada. El plan de ejecución muestra la manera en que serán escaneadas las tablas referenciadas; ya sea escaneo secuencial plano, escaneo por índice, etc. En el caso que se referencian varias tablas, los algoritmos de unión que serán utilizados para agrupar las tuplas requeridas de cada tabla de entrada. Sintaxis EXPLAIN [ VERBOSE ] consulta

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 236 de 253

La opción VERBOSE emite la representación interna completa del plan. Usualmente esta opción es útil para la corrección de errores de Postgres.

Ejemplo 1. Se emplea la misma tabla utilizada en la Lección 29: demo=# SELECT * FROM game_score; pname | score -------+------UCH | 2 SW | 4 (2 rows)

Para mostrar un plan de consulta para una consulta simple sobre una tabla con dos columnas una del tipo INT y la otra VARCHAR : EXPLAIN SELECT * FROM game_score; QUERY PLAN ---------------------------------------------------------Seq Scan on game_score (cost=0.00..1.02 rows=2 width=7) (1 row)

Ejemplo 2. Se crea una tabla con un índice INT y se insertan 4 valores: demo=# CREATE TABLE score (num int); CREATE TABLE demo=# INSERT INTO score VALUES(1),(2),(5),(4); INSERT 0 4 EXPLAIN SELECT * FROM foo WHERE num = 4; QUERY PLAN ----------------------------------------------------Seq Scan on foo (cost=0.00..40.00 rows=12 width=4) Filter: (num = 4) (2 rows) EXPLAIN es la presentación del costo estimado de ejecución de la consulta, que es la

suposición del planificador sobre el tiempo que tomará correr la consulta (medido en unidades de captura de páginas de disco). Se Muestra dos números: el tiempo inicial que toma devolverse la primer tupla, y el tiempo total para devolver todas las tuplas.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 237 de 253

Lección XXXI - Servicios de Respaldo y Recuperación para Bases de Datos (BD) Vamos a comenzar esta Lección con algunas preguntas que a medida que avance la Lección, serán respondidas: 1. ¿Por qué debemos respaldar una BD? ¿Es posible recuperar información? ¿Cuál es la importancia de este tipo de servicios? 2. ¿Como funcionan? 3. ¿Son soportadas por los principales Sistemas de BD? ¿Cuál es el caso de PostgreSQL? Es de suma importancia tener algún sistema de respaldo/recuperación de datos, pues esto permite: 1. Tener sistemas con cierto nivel de seguridad y estabilidad ante posibles fallos. 2. Poder volver a un punto seguro en el estado de la BD, debido a cambios peligrosos. Su funcionamiento está basado en estados. En cada momento la BD se encuentra en un estado definido. Cuando realizamos operaciones de modificación, es decir: 1. INSERT 2. UPDATE 3. DELETE Cambiamos su estado, llevándolo a uno nuevo.

Nota No se considera SELECT, pues no provoca cambios. Recordemos que es una operación de selección. Al momento de realizar un respaldo, se guarda el estado en que se encuentra la BD al momento de realizar dicha operación de respaldo. Al momento de realizar la operación de recuperación, puede ser de varias formas, ya sea a través de las operaciones (en orden) que han dejado la BD en el estado actual u otras formas.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 238 de 253

La gran mayoría de Motores de BD cuentan con funciones de este tipo y PostgreSQL no es la excepción.

Servicios en PostgreSQL De forma nativa PostgreSQL cuenta con las siguientes funciones: Advertencia Algunos de los comandos que se verán a continuación, son ejecutados en terminales bajo sistemas UNIX. No necesariamente dentro del entorno de PostgreSQL (psql).

SQL Dump pg_dump Esta función genera un archivo de texto con comandos SQL que, cuando son reintroducidos (bajo cierto contexto ) al servidor, se deja a la BD en el mismo estado en el que se encontraba al momento de ejecutar este comando. Nota Esto ocurre siempre y cuando la BD esté vacía, es decir, en el mismo estado inicial. pg_dump guarda los comandos introducidos hasta el punto de control. El ejemplo 1 permitirá aclarar dudas.

su sintaxis es: pg_dump dbname > archivo_salida

y se usa desde la linea de comandos. Para realizar la restauración se utiliza: psql dbname < archivo_entrada

Donde archivo_entrada corresponde al archivo_salida de la instrucción pg_dump.

Ejemplo 1 Supongamos que tenemos una BD llamada lecture31 y dentro de ella una única tabla llamada Numbers con atributos Number YName, con datos: 1 One 2 Two Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 239 de 253

3 Three

Es decir: CREATE DATABASE lecture31;

conectándose: \c lecture31 CREATE TABLE Numbers(Number INTEGER, Name VARCHAR(20)); INSERT INTO Numbers VALUES (1, 'One' ); INSERT INTO Numbers VALUES (2, 'Two' ); INSERT INTO Numbers VALUES (3, 'Three' );

A través de un select: number | name -------+------1 | One 2 | Two 3 | Three

Para realizar el respaldo, se utiliza pg_dump: pg_dump lecture31 > resp.sql

Un posible problema a la hora de ejecutar pg_dump es: pg_dump lecture31 > resp.sql (bash: permission denied)

Para evitar esto, es necesario considerar que el usuario de la BD debe tener permisos de escritura en la carpeta donde se alojará el archivo. Nota Para los usuarios locales, basta con hacer “cd” en la linea de comandos (como usuario postgres), para acceder a la carpeta de postgres. Si desea realizar pruebas desde el servidor dedicado, puede crear BDs desde su sesión y alojar los archivos de respaldo en su capeta home. Nota Es posible cambiar los permisos de Lección y escritura de las carpetas, dar accesos a usuarios que no son dueños de las BD. No se profundiza esto, pues escapa a los alcances de este curso.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 240 de 253

Supongamos que se comete un error, se borra información de seguridad nacional, digamos la tupla “1, One”. Utilizando el archivo de respaldo es posible volver al estado anterior: psql lecture31 < resp.sql Nota Nótese que dentro de la salida del comando aparece: ERROR: relation “numbers” already exists

Revisando la tabla a través de: \c lecture31 SELECT * FROM Numbers;

La salida es: number | name -------+------2 | Two 3 | Three 1 | One 2 | Two 3 | Three

Lo cual, claramente, no corresponde a la información inicial. Antes de restaurar, es necesario recrear el contexto que tenía la BD. Específicamente usuarios que poseían ciertos objetos o permisos. Si esto no calza con la BD, original, es posible que la restauración no se realice correctamente. En este caso el contexto inicial corresponde a una BD vacía, dentro de la cual se crea una tabla y se agregan algunos datos Se invita al lector a borrar la tabla y realizar la restauración. Es necesario aclarar que se necesita una BD existente para hacer la restauración. Si ésta no existe, por ejemplo utilizar lecture32 en lugar de 31, el siguiente error aparecerá: psql: FATAL: database "lecture32" does not exist

Pero ¿Qué ocurre si utilizamos el atributo number como PK?, es decir modificar sólo la linea (y seguir el resto de los pasos de la misma forma): CREATE TABLE Numbers(Number INTEGER, Name VARCHAR(20), PRIMARY KEY (Number));

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 241 de 253

Al momento de borrar la tupla, digamos (3, ‘Three’), e intentar restaurar, dentro de la salida del comando aparece: ERROR: relation "numbers" already exists ERROR: duplicate key violates unique constraint "numbers_pkey" CONTEXT: COPY numbers, line 1: "1 One" ERROR: multiple primary keys for table "numbers" are not allowed

¿Qué ocurre si se elimina la primera tupla antes de restaurar?

Ejemplo 2 Este ejemplo es muy similar al anterior, sólo que, en lugar de trabajar con atributos INTEGER, se trabajará con atributo serial es decir: \c lecture31 DROP TABLE Numbers; CREATE TABLE Numbers2(Number SERIAL, Name VARCHAR(20)); INSERT INTO Numbers2 (name) VALUES ('One' ); INSERT INTO Numbers2 (name) VALUES ('Two' ); INSERT INTO Numbers2 (name) VALUES ('Three' );

Es decir que si se hace un select, se podrá ver: number | name -------+------1 | One 2 | Two 3 | Three

Para poder realizar el respaldo, utilizando pg_dump: pg_dump lecture31 > resp2.sql

Digamos que se agrega la tupla (4, ‘Four’) y borra la tupla (3, ‘Three’). Después de realizar el respaldo: number | name -------+------1 | One 2 | Two 4 | Four

Posteriormente se realiza la restauración: psql lecture31 < resp.sql

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 242 de 253

Nota Nótese que en la salida, es posible ver: setval 3

Revisando la tabla a través de: \c lecture31 SELECT * FROM Numbers2;

La salida es: number | name -------+------1 | One 2 | Two 4 | Four 1 | One 2 | Two 3 | Three

Lo cual es un problema, pues se trabaja con valores seriales. De hecho si en este estado se agrega la tupla (4, Four) y se revisan los contenidos de la tabla, la salida es: number | name -------+------1 | One 2 | Two 4 | Four 1 | One 2 | Two 3 | Three 4 | Four

Esto ocurre debido a que el contador serial vuelve a 3.

Ejercicio propuesto Se deja en manos del lector ver que ocurre en caso de trabajar con atributo serial PK, es decir: CREATE TABLE Numbers2(Number SERIAL, Name VARCHAR(20), PRIMARY KEY (number));

y luego seguir los mismos pasos, es decir agregar las tuplas (1, ‘One’), (2, ‘Two’) y (3, ‘Three’). Luego realizar un respaldo, acceder a la BD, eliminar la última tupla, agregar (4, ‘Four’), realizar la restauración, intentar agregar más tuplas (conectándose a la BD primero) y los que desee hacer el lector.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 243 de 253

A modo de pista, si al agregar una tupla, aparece: ERROR: duplicate key value violates unique constraint "numbers2_pkey"

Siga intentando, verá que es posible agregar más tuplas. Fíjese en el valor de la llave primaria. ¿Cuántas veces tuvo que intentar? ¿Qué ocurre si en lugar de eliminar la última tupla, se elimina la primera?

pg_dumpall Un pequeño inconveniente con pg_dump es que sólo puede hacer respaldos de una BD a la vez. Además no respalda información acerca de roles de usuario e información por el estilo Para realizar un respaldo de la BD y el cluster de datos, existe el comando pg_dumpall. su sintaxis es: pg_dumpall > archivo_salida

y para realizar la restauración (utilizar el comando unix) psql -f archivo_entrada postgres

Que trabaja emitiendo las consultas y comandos para recrear roles, tablespaces y Bases de Datos vacíos. Posteriormente se invoca pg_dump por cada BD para corroborar consistencia interna. Advertencia Es posible que el servidor dedicado no le permita restaurar, si se utiliza con el usuario postgres. Por favor, utilice este comando sólo de manera local. Pruebe utilizando su propio usuario.

Respaldo a nivel de archivos Otra forma de realizar respaldos es a través del manejo directo de archivos, en lugar de las sentencias utilizadas. No obstante, existen 2 restricciones que hacen que este método sea menos práctico que utilizar pg_dump: 1. El servidor debe ser apagado para poder obtener un respaldo utilizable.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 244 de 253

2. Cada vez que se realice un respaldo, el servidor debe estar apagado, para que los cambios se guarden en su totalidad. Advertencia La mayor parte de las veces, se necesita acceso root, para poder realizar este tipo de operación, pues es necesario configurar archivos de configuración de postgres. Es de suma importancia que se realicen de forma correcta, pues ante algún fallo es posible destruir la base de datos de forma completa. Por lo tanto, no se abordará de forma extensa este apartado. No obstante es posible obtener información en internet.

Rsync Rsync corresponde a un programa que sincroniza dos directorios a través de distintos sistemas de archivos, incluso si están en distinto computadores, físicamente hablando. A través del uso de SSH o Secure SHell por sus siglas en inglés, se pueden realizar transferencias seguras y basadas en llaves de autenticación. La principal ventaja de utilizar rsync a diferencia de otros comandos similares, como scp, es que si el archivo que se encuentra en la fuente, es el mismo que, el que se encuentra en el objetivo, no hay transmisión de datos; si el archivo que se encuentra en el objetivo difiere del que se encuentra en la fuente, sólo aquellas partes que difieren son transmitidas, en lugar de transmitir todo, por lo que el downtime de la BD, es decir, el tiempo que debe permanecer apagada, es mucho menor. Cabe destacar que es de suma importancia realizar una adecuada preparación de la BD, para evitar posibles desastres. En [1] se explican con gran nivel de detalle. No obstante, los cambios realizados son bajo su propio riesgo, y se recomienda fuertemente realizar pruebas de manera local.

Conclusiones Para finalizar, por lo general, los respaldos realizados a través de SQL Dump suelen ser más livianos, en tamaño, que los realizados a través de respaldo de archivos, ya que, por ejemplo en el caso de pg_dump no es necesario copiar índices de tablas o cosas por el estilo; sino que sólo los comandos que los crean. Es por ello que, generalmente estos últimos, son más rápidos.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 245 de 253

Lección XXXII – Querys al Catalogo de PostgreSQL Obtener objetos del servidor PostgreSQL. En esta sección se especifican los objetos generales del servidor y se presentan Algunas consultas útiles para el trabajo con PostgreSQL.

- Obtener las bases de datos disponibles select datname from pg_database where datallowconn=true

- Obtener los Roles que se puede conectar al servidor select rolname from pg_roles where rolcanlogin=true

- Obtener los Grupos que existes en el servidor select rolname from pg_roles where rolcanlogin=false

- Obtener los tablespace del servidor select spcname from pg_tablespace

- Obtener los lenguajes del servidor

select lanname from pg_language

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 246 de 253

Obtener objetos de las bases de datos. En esta sección se especifican los objetos dentro de una base de datos del servidor y se presentan algunas consultas útiles para el trabajo con PostgreSQL.

- Obtener los esquemas.

Select * from information_schema.schemata where catalog_name='basedato' and schema_name not like 'pg_%' and schema_name'information_schema'

- Obtener las tablas.

Select * from information_schema.tables where table_catalog='basedato' and table_schema not like 'pg_%' and table_schema'information_schema' and table_type='BASE TABLE'

- Obtener las vistas. select* from information_schema.tables where table_catalog='basedato' and table_schema not like 'pg_%' and table_schema'information_schema' and table_type='VIEW'

- Obtener las funciones. select specific_schema,routine_name from information_schema.routines where specific_catalog='basedato' and specific_schema not like 'pg_%' and specific_schema'information_schema' and routine_type='FUNCTION'and dat a_type'trigger'

o SELECT n.nspname as "Schema", p.proname as "Name", u.rolname as "Propietario" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 247 de 253

join pg_roles u on u.oid=p.proowner WHERE pg_cat alog.pg_function_is_visible(p.oid) AND n.nspname 'pg_catalog' AND n.nspname 'information_schema' AND p.prorettype'pg_catalog.trigger'::pg_catalog.regtype

- Obtener los trigger. select specific_schema,routine_name from informatio n_schema.routines where specific_catalog='basedato' and specific_schema not like 'pg_%' and specific_schema'information_schema' and routine_type='FUNCTION'and data_type='trigger'

o SELECT n.nspname as "Schema", p.proname as "Name", u.rolname as "Prop ietario" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace join pg_roles u on u.oid=p.proowner WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname 'pg_catalog' AND n.nspname 'information_schema' AND p.prorettype='pg_catalog.trigger'::pg_catalog.regtype

- Obtener las secuencias. SELECT sequence_schema,sequence_name from information_schema.sequences where sequence_catalog='basedato'

o SELECT n.nspname as "Schema", c.relname as "Name", pg_catalog.pg_get_u serbyid(c.relowner) as "propietario" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind ='S' AND n.nspname 'pg_catalog' AND n.nspname 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid)

- Obtener los dominios.

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 248 de 253

select * from information_schema.domains where domain_catalog='basedato' and domain_schema not like 'pg_%' and domain_schema'information_schema'

- Obtener las reglas. select * from pg_rules where schemaname schemaname'information_schema'

not

like

'pg_%'

and

- Obtener los tipos de datos. select pg_type.typname,pg_namespace.nspname, case typcategory when 'N' then 'numerico' when 'B' then 'booleano' when 'C' then 'compuesto' when 'D' th en 'fecha/hora' when 'S' then 'string' when 'G' then 'geometrico' when 'E' then 'enumerativo' when 'I' then 'IP' when 'V' then 'string bit' ELSE 'otro' end as tipodedato from pg_type left join pg_namespace on (pg_type.typnamespace = pg_namespace.oid) where pg_type.typcategory'A' order by pg_namespace.nspname

- Obtener índices. select * from pg_indexes where and schemaname'information_schema'

schemaname

not

like

'pg_%'

- Obtener detalles de los esquemas. select pg_namespace.nspname,pg_roles.rolname,pg_namespace.nspacl from pg_namespace,pg_roles where pg_namespace.nspowner=pg_roles.oid and pg_namespace.nspname='equema'

- Obtener detalles de las tablas. Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 249 de 253

select ordinal_position, column_name,data_type from information_schema.columns where table_schema='equema' and table_name='tabla'

o SELECT a.attnum,a.attname as campos, pg_catalog.format_type(a.atttypid, a.atttypmod) as tipo, (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as pordefecto,a.attnotnull as NULO FROM pg_catalog.pg_attribute a WHERE a.attrelid = (select oid from pg_class where relname='tabla' ) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum SELECT pg_get_constraintdef(pg_constraint.oid), pg_class.relname AS tabla, pg_constraint.conname FROM pg_constraint, pg_class WHERE ((pg_constraint.contype = 'p'::"char") AND (pg_constraint.conrelid = pg_class.oid)) and relname='tabla' SELECT pg_constraint.conname as nombrellave , pg_get_constraintdef(pg_constraint.oid) as definicion, pg_class.relname AS tabla,(select a.relname from pg_class a where a.oid=pg_constraint.confrelid) as tablaforanea, case confmatchtype when 'u' then 'MATCH simple ' when 'f' then 'MATCH full' when 'p' then 'MATCH partial' end as tipo, case confupdtype when 'a' then 'no action ' when 'r' then 'restrict' when 'c' the n 'cascade' when 'n' then 'set null' else 'default' end as onupdate , case confdeltype when 'a' then 'no action ' Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 250 de 253

when 'r' then 'restrict' when 'c' then 'cascade' when 'n' then 'set null' else 'default' end as ondelete FROM pg_constraint, pg_class WHERE ((pg_constraint.contype = 'f'::"char") AND (pg_constraint.conrelid = pg_class.oid) and relname='tabla')

- Obtener detalles de las vistas. select viewname as nombre ,viewowner as propietario,definition as definicion from pg_views where viewname='vista' and schemaname='esquema' o SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'v'::"char" and n.nspname 'pg_catalog' AND n.nspname 'information_schema';

- Obtener detalles de las funciones. SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_functiondef(p.oid), pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE WHEN p.proisagg THEN 'agg' WHEN p.proiswindow THEN 'window' ELS E 'normal' END as "Type", pg_language.lanname as lenguaje , p.prosecdef FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace join pg_language on ( p.prolang=pg_language.oid) WHERE pg_catalog.pg_function_is_ visible(p.oid) AND n.nspname 'pg_catalog'

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 251 de 253

AND n.nspname 'information_schema' AND p.prorettype'pg_catalog.trigger'::pg_catalog.regtype

- Obtener detalles de los trigger. SELECT n.nspname as "Schema",p.proname as "Name",pg_catalog.pg_get_functiondef(p.oid), pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", pg_language.lanname as lenguaje ,p.prosecdef FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace join pg_language on ( p.prolang=pg_language.oid) WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname 'pg_catalog' AND n.nspname 'information_schema' AND p.pro rettype='pg_catalog.trigger'::pg_catalog.regtype ORDER BY 1, 2, 4;

Y select distinct trigger_name , array_to_string( array (select event_manipulation::"text" from information_schema.triggers where trigger_name='cons' and trigger_schema='public' ),',') a s evento , event_object_table as tabla, action_orientation as modo,condition_timing as momento,action_statement as accion from information_schema.triggers where trigger_name='cons' and trigger_schema='public'

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 252 de 253

Lección XXXIII. Consultas útiles de monitoreo. Usuarios conectados, IP y consulta select datname,usename,current_query,client_addr from pg_stat_activity

Peso de las base de datos del servidor SELECT distinct datname as "nombre de la base",pg_size_pretty(pg_database_size(datname)) as "Tamaño de la base" from pg_stat_activity ;

Peso de las tablas SELECT nspname, relname, pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize FROM (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize, COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint FROM pg_index WHERE cl.oid=indrelid), 0) AS i ndexsize, CASE WHEN reltoastrelid=0 THEN 0 ELSE pg_relation_size(reltoastrelid) END AS toastsize, CASE WHEN reltoastrelid=0 THEN 0 ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct WHERE ct.oid = cl.reltoastrelid)) END AS toastindexsize FROM pg_ class cl, pg_namespace ns WHERE cl.relnamespace = ns.oid AND ns.nspname NOT IN ('pg_catalog', 'information_schema') AND cl.relname IN (SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE')) ss ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;

Curso SQL PostgreSQL –

Profesor: Ing. Mario Soto Cordones –

Página 253 de 253