Transcription
1
ADMINISTRACIÓN DE BASES DEDATOS CON POSTGRESQLIng Mariuxi Paola Zea Ordóñez, Mg.Ing. Jimmy Rolando Molina Ríos, Mg.Ing. Fausto Fabían Redrován Castillo, Mg.2
Editorial Área de Innovación y Desarrollo, S.LQuedan todos los derechos reservados. Esta publicación no puede ser reproducida, distribuida, comunicadapúblicamente o utilizada, total o parcialmente, sin previa autorización.ÁREA DE INNOVACIÓN Y DESARROLLO, S.L. del texto: Los autoresC/ Els Alzamora, 17 - 03802 - ALCOY (ALICANTE) info@3ciencias.comPrimera edición: abril 2017ISBN: 978-84-946684-6-3DOI: http://dx.doi.org/10.17993/IngyTec.2017.183
.4
ÍNDICE0.INTRODUCCIÓN . 91.BASE DE DATOS . 111.1.IMPLEMENTACIÓN DE UNA BASE DE DATOS . 122. POSTGRESQL . 123. CASO DE ESTUDIO . 134.MODELO ENTIDAD RELACIÓN . 175.MODELO RELACIONAL . 216.CREACIÓN DE LA BASE DE DATOS . 257.CREACION DE TABLAS . 258.INTEGRIDAD DE ENTIDAD . 269.CLAVE PRINCIPAL . 2610.INTEGRIDAD REFERENCIAL . 2711.CLAVE FORÁNEA . 2712.CHECK . 2813.CONSULTAS CON LA CLÁUSULA ORDER BY . 2815.CONSULTAS COMPLEJAS . 3115.1CONSULTA DE SELECCIÓN . 3215.2PROYECCIÓN. 3315.3UNIÓN . 3415.4INTERSECCIÓN . 3515.5DIFERENCIA . 3615.6COMBINACIÓN . 3715.7PRODUCTO CARTESIANO . 3715.8COMBINACIONES EXTERNAS . 3916.FUNCIONES PROCEDURALES . 4216.1FUNCIÓN SQL . 4316.2FUNCIÓN PLPGSQL . 4317.TRIGGERS. 4618.ENCRIPTACIÓN . 4719.PRIVILEGIOS Y USUARIOS . 5020.AUDITORIAS A LAS BASES DE DATOS . 535
21.21.1TRANSACCIONES . 56PROPIEDADES DE LA TRANSACCIÓN . 5722.HERRAMIENTA DE RESGUARDO . 6223.HERRAMIENTA DE RESTAURACIÓN . 7024.HERRAMIENTA DE MANTENIMIENTO . 7525.BIBLIOGRAFÍA . 796
ÍNDICE DE ILUSTRACIONES Y TABLASIlustración 1. Ficha Caso de Estudio. . 14Ilustración 2. Ejemplo. . 18Ilustración 3. Ejemplo. . 19Ilustración 4: Ejemplo Modelo Entidad Relación. . 20Ilustración 5. Modelo Relacional. 22Ilustración 6. Transformación del diagrama entidad relación al modelo relacional. . 22Ilustración 7. Transformación del diagrama entidad relación al modelo relacional. . 23Ilustración 8. Ejemplo Modelo Relacional. 24Ilustración 9. Creación de la Base de Datos. . 25Ilustración 10. Creación de Tablas. . 25Ilustración 11. Ejemplo Restricción Unique. . 26Ilustración 12. Ejemplo Clave Foránea. . 27Ilustración 13. Ejemplo Check. . 28Ilustración 14. Ejemplo Cláusula Order By. . 29Ilustración 15. Registros Tabla Empleados. . 29Ilustración 16. Ejemplo Consultas con Predicado. . 30Ilustración 17. Ejemplo Consultas con Predicado. . 31Ilustración 18. Ejemplo Consulta de Selección. . 32Ilustración 19. Consulta a Estructurar. . 32Ilustración 20. Secuencia ejecutada. . 32Ilustración 21. Ejemplo Proyección. . 33Ilustración 22. Sentencia Proyección. . 33Ilustración 23. Resultado Proyección. . 34Ilustración 24. Ejemplo Unión. . 34Ilustración 25. Resultado Unión. . 35Ilustración 26. Resultado Intersección. . 35Ilustración 27. Cláusula Intersect. . 36Ilustración 28. Estructura Intersect. . 36Ilustración 29. Ejemplo Diferencia. . 37Ilustración 30. Ejemplo Producto Cartesiano. 37Ilustración 31. Ejemplo Producto Cartesiano. 38Ilustración 32. Sentencia Producto Cartesiano. . 38Ilustración 33. Ejemplo Producto Cartesiano. 38Ilustración 34. Sentencia Combinaciones Internas. . 39Ilustración 35. Resultados Combinaciones Internas. . 39Ilustración 36. Cláusula Left Outer Join. . 40Ilustración 37. Resultados Clásula Left Outer Join. . 40Ilustración 38. Ejemplo Right Outer Join. . 40Ilustración 39. Resultados Right Outer Join. . 41Ilustración 40. Ejemplo Full Outer Join. . 41Ilustración 41. Resultado Full Outer Join. . 41Ilustración 42. Funciones Procedurales. . 42Ilustración 43. Ejemplo Función SQL. . 43Ilustración 44. Función SQl. . 43Ilustración 45. Ejemplo Función PLPGSQL. . 44Ilustración 46. Función PLPGSQL. . 44Ilustración 47. Ejemplo Función PLPGSQL. . 45Ilustración 48. Resultado Función PLPGSQL. . 45Ilustración 49. Creación de la Función. . 46Ilustración 50. Creación del Trigger. . 47Ilustración 51. Comprobación de Trigger. . 47Ilustración 52. Ejemplo Encriptación. . 48Ilustración 53. Resultados Consulta. . 49Ilustración 54. Visualización de datos. . 49Ilustración 55. Registro de Dato Encriptado. . 497
Ilustración 56. Vista con datos Encriptados. . 50Ilustración 57. Creación de Usuarios. 51Ilustración 58. Creación de Permisos a Usuarios. . 52Ilustración 59. Creación de Usuarios por Grupos. . 52Ilustración 60. Sentencia para para visualizar Usuarios. . 52Ilustración 61. Pasos para crear una Auditoría. . 53Ilustración 62. Explicación Funcionamiento de la Función. . 55Ilustración 63. Resultados Tabla Auditoría. . 56Ilustración 64. Tabla Contratos. . 56Ilustración 65. Ejemplo Transacción. . 58Ilustración 66. Resultado Consulta. . 58Ilustración 67. Propiedad de Atomicidad. . 58Ilustración 68. Resultado Consulta. . 59Ilustración 69. Comprobación de Atomicidad. . 59Ilustración 70. Resultado Consulta. . 60Ilustración 71. Propiedad de Consistencia. . 60Ilustración 72. Ejemplo Transacción. . 61Ilustración 73. Visualización de información. . 61Ilustración 74. Uso de Comando COMMIT. . 62Ilustración 75. Realización de Consulta. 62Ilustración 76. Ejemplo Herramienta de Resguardo. . 63Ilustración 77. Ejemplo Herramienta de Resguardo. . 63Ilustración 78. Campo de entrada “Format”. . 64Ilustración 79. Campo de entrada “Encoding”. . 64Ilustración 80. Campo de entrada “Rolename”. . 65Ilustración 81. Pestaña “Dump Options #1”. . 65Ilustración 82. Pestaña “Dump Options #2”. . 66Ilustración 83. Pestaña “Objects”. . 66Ilustración 84. Antes del Backup .70Ilustración 85. Después del Backup. . 67Ilustración 86. Dirección de la carpeta bin en cmd. . 67Ilustración 87. Comando pg dump. . 68Ilustración 88. Verificación de Respaldo. . 68Ilustración 89. Información comando pg dump. . 69Ilustración 90. Ejemplo Restauración. . 70Ilustración 91. Pestañas de Restauración. . 71Ilustración 92. Pestaña “Restore Options #1”. . 71Ilustración 93. Pestaña “Restore Options #2”. . 72Ilustración 94. Pestaña “Objects”. . 72Ilustración 95. Pestaña “Messages”. . 73Ilustración 96. Restauración por cmd. . 73Ilustración 97. Comando createdb. . 74Ilustración 98. Comandos psql. . 74Ilustración 99. Explorador de objectos en pgAdmin III. . 74Ilustración 100. Opción Mantenimiento. . 76Ilustración 101. Opciones para el mantenimiento. . 76Ilustración 102. Operación realizada en el mantenimiento. . 76Ilustración 103. Editor de consultas para el mantenimiento. . 77Ilustración 104. Acceso a la base datos a través del SQL Shell. . 77Ilustración 105. Informe del Vacuum. . 78Tabla 1. Componentes del modelo entidad relación. . 17Tabla 2. Transformación del modelo entidad relación al modelo relacional. . 21Tabla 3. Consultas con Predicado. . 30Tabla 4. Encriptación. . 48Tabla 5. Matriz de Trazabilidad de los Usuarios. . 51Tabla 6. Formatos. 648
0. INTRODUCCIÓNEn la actualidad, las bases de datos cumplen una función muy importante en los sistemas deinformación, la mayoría de las empresas sean públicas o privadas tiene sus procesosautomatizados y esto hace que los sistemas manuales queden obsoletos a la hora de realizarbúsquedas, modificaciones y cualquier obtención de información de dicha empresa, es por elloque la utilización de una base de datos se hace indispensable al momento de almacenar grandesvolúmenes de información con la que cuenta la organización. Una base de datos es un banco deinformación, el cual contiene datos relacionados entre sí y se encuentran agrupados oestructurados; además son manipulados por programas conocidos actualmente como Sistemade Gestión de Base de Datos (SGBD). En este caso se ha utilizado PostgreSQL como SGBD parala realización y ejecución del proyecto.Cabe mencionar que para poder desarrollar una correcta base datos es necesario realizar elModelo Entidad Relación y un buen modelado relacional englobando todos los requerimientoso acciones que cumple la empresa, una vez que esta fase se haya ejecutado con éxito se podríaproceder a la creación y a la implementación de los conceptos para la buena administración deuna base de datos.Es por ello que el presente libro presenta un caso de estudio acerca de la empresa inmobiliaria“Tierra Prometida” que servirá de plataforma para realizar la administración de la estructura dela base de datos.El documento se compone de cuatro capítulos que corresponden a: El primer Capítulo se trataacerca de la Programación Back-End, incluye scripts para crear la estructura de la base de datos,para consultas avanzadas, consultas con funciones, subqueryes, store procedure, y triggers. Enel Capítulo II, se aborda el tema de seguridad e integridad de la base de datos. A continuación,en el Capítulo III, se explica sobre Sistemas Transaccionales. Finalmente, en el Capítulo IV setrata acerca de las técnicas de recuperación.9
10
1. BASE DE DATOSTodas las bases de datos, desde las más sencillas hasta las más complejas, están compuestas porlistas de información.“Una base de datos es una colección de información organizada de tal modo que sea fácilmenteaccesible, gestionada y actualizada” (Rouse.).Una base de datos permite almacenar diferentes tipos de información. Las bases de datospermiten a sus usuarios acceder, registrar y analizar datos de una manera rápida y sencilla.“En informática, las bases de datos a veces se clasifican de acuerdo a su enfoque organizativo.El enfoque más frecuente es la base de datos relacional, una base de datos tabular en la que losdatos se definen de manera que puede ser reorganizada y se accede en un número de manerasdiferentes. Una base de datos distribuida es una que puede ser dispersada o replicada entrediferentes puntos de una red” (Rouse.).“Los sistemas gestores de bases de datos son la herramienta más adecuada para almacenar losdatos en un sistema de información debido a sus características de seguridad, recuperación antefallos, gestión centralizada, estandarización del lenguaje de consulta y funcionalidad avanzada”(Rouse.).Las ventajas de utilizar un almacenamiento estructurado se aprecian en diversos puntos, ya queafectan no solo a los datos sino también al propio uso.Según, (Bases de datos) indica algunas de las ventajas más características como lo son lassiguientes: Mayor independencia. Los datos son independientes de las aplicaciones que los usan, así comode los usuarios. Mayor disponibilidad. Se facilita el acceso a los datos desde contextos, aplicaciones y mediosdistintos, haciéndolos útiles para un mayor número de usuarios. Mayor seguridad (protección de los datos). Por ejemplo, resulta más fácil replicar una base dedatos para mantener una copia de seguridad que hacerlo con un conjunto de ficherosalmacenados de forma no estructurada. Además, al estar centralizado el acceso a los datos,existe una verdadera sincronización de todo el trabajo que se haya podido hacer sobre estos,con lo que esa copia de seguridad servirá a todos los usuarios. Menor redundancia. Un mismo dato no se encuentra almacenado en múltiples ficheros o conmúltiples esquemas distintos, sino en una única instancia en la base de datos. Esto redunda enmenor volumen de datos y mayor rapidez de acceso. Mayor eficiencia en la captura, codificación y entrada de datos.Siendo así ventajas importantes en el tratamiento de la información que se aloja dentro de unabase datos en cualquier organización.11
1.1.IMPLEMENTACIÓN DE UNA BASE DE DATOSPara la implementación de una base de datos, esta implica la definición de la estructura, másconcretamente según Fuente especificada no válida. Se puede distinguir las siguientes fases enel proceso global de desarrollo de una base de datos: Diseño lógico. Independiente del SGBD empleado, es un diseño conceptual que pretendemodelizar el contenido de la base de datos. Diseño físico. Es la adaptación del diseño conceptual a las particularidades del SGBD escogido. Implementación. Introducción de los datos en la base de datos. Mantenimiento. Monitorización de la actividad sobre la base de datos.2. POSTGRESQL“PostgreSQL es un sistema de gestión de bases de datos objeto-relacional, distribuido bajolicencia BSD y con su código fuente disponible libremente. Es el sistema de gestión de bases dedatos de código abierto más potente del mercado” (Sobre PostgreSQL).“PostgreSQL utiliza un modelo cliente/servidor y usa multiprocesos en vez de multihilos paragarantizar la estabilidad del sistema. Un fallo en uno de los procesos no afectará el resto y elsistema continuará funcionando” (Sobre PostgreSQL).Según el sitio oficial de PostgreSQL son varias las características de este software, las cuales sedetallan a continuación: Es una base de datos 100% ACIDIntegridad referencialTablespacesNested transactions (savepoints)Replicación asincrónica/sincrónica / Streaming replication - Hot StandbyTwo-phase commitPITR - point in time recoveryCopias de seguridad en caliente (Online/hot backups)UnicodeJuegos de caracteres internacionalesRegionalización por columnaMulti-Version Concurrency Control (MVCC)Multiples métodos de autentificaciónAcceso encriptado via SSLActualización in-situ integrada (pg upgrade)12
SE-postgresCompleta documentaciónLicencia BSDDisponible para Linux y UNIX en todas sus variantes (AIX, BSD, HP-UX, SGI IRIX, Mac OSX, Solaris, Tru64) y Windows 32/64bit.Otra definición sobre PostgreSQL indica que es “Un sistema de base de datos relacionales es unsistema que permite la manipulación de acuerdo con las reglas del álgebra relacional. Los datosse almacenan en tablas de columnas y renglones. Con el uso de llaves, esas tablas se puedenrelacionar unas con otras.”3. CASO DE ESTUDIOPara comenzar iniciaremos con el siguiente caso de estudio que será automatizado en una basede datos.Tierra Prometida S. A.En este apartado se describe una empresa inmobiliaria, Tierra Prometida, que está especializadaen el alquiler de pisos y casas amuebladas.Esta empresa se encarga de dar publicidad a los inmuebles que ofrece en alquiler, tanto enprensa local como nacional, entrevista a los posibles inquilinos, organiza las visitas a losinmuebles y negocia los contratos de alquiler. Una vez firmado el alquiler, la empresa asume laresponsabilidad del inmueble, realizando inspecciones periódicas para comprobar su correctomantenimiento.13
Esta información se encuentra actualmente en fichas:Ilustración 1. Ficha Caso de Estudio.Fuente: autores.La agencia posee varias oficinas. Ya que la ficha de cada inmueble se encuentra en la oficina a laque se ha dirigido el propietario para ponerlo en venta o alquiler, la forma de compartir estainformación actualmente es consultándola telefónicamente entre oficinas.A continuación, se describen los datos que se manejan en las oficinas de la empresa para llevara cabo el trabajo diario. Oficinas:La empresa tiene varias oficinas en todo el país. Cada oficina tiene un código deidentificación que es único, tiene una dirección (calle, número y ciudad), un número deteléfono y un número de fax. Cada oficina tiene su propia plantilla. Plantilla:Cada oficina tiene un director que se encarga de supervisar todas sus gestiones. Laempresa sigue muy de cerca el trabajo de los directores y tiene registrada la fecha enque cada director empezó en el cargo en su oficina. Cada director tiene un pago anualpor gastos de vehículo y una bonificación mensual que depende de los contratos dealquiler que haya realizado su oficina.En cada oficina hay varios supervisores. Cada uno es responsable del trabajo diario deun grupo de entre cinco y diez empleados que realizan las gestiones de los alquileres. Eltrabajo administrativo de cada grupo lo lleva un administrativo.Cada miembro de la plantilla tiene un código único que lo identifica en la empresa. Decada uno de ellos se quiere conocer el nombre, la dirección, el número de teléfono, lafecha de nacimiento, el número del DNI, su puest
Una base de datos permite almacenar diferentes tipos de información. Las bases de datos permiten a sus usuarios acceder, registrar y analizar datos de una manera rápida y sencilla. En informática, las bases de datos a veces se clasifican de acuerdo a su enfoque organizativo. El enfoque más frecuente es la base de datos relacional, una base de datos tabular en la que los datos se definen .
