Diseño Físico E S P E CI C I A L I Z A C I Ó N E N T EC EC N O L O G Í A S D E L A I N F OR O R M A C I Ó N G E OG OG R Á F I C A
Mariela J. Quiroga Gi ald roni
Bases de datos Conceptual
Diseño
Lógico Físico
Mantenimiento
Uso Recuperación
Bases de datos Conceptual
Diseño
Lógico Físico
Mantenimiento
Uso Recuperación
SGBD Colección de datos y el conjunto de infraestructura necesaria para Acceder a los mismos mism os Garantizar la integridad integridad de los datos Establecer Establecer los mecanismos de seguridad
Diseño de una base de datos
PostgreSQL
Versión de código abierto de un ORDBMS desarrollado por el Departamento de Ciencias de la Computación de la Universidad de California. Puede ser usado, modificado y distribuido por cualquiera sin costo alguno para cualquier propósito.
PostgreSQL
Operating Systems: Linux (all recent distributions), Windows (Win2000 SP4 and later), FreeBSD, OpenBSD, NetBSD, Mac OS X, AIX, HP/UX, IRIX, Solaris, Tru64 Unix, UnixWare.
CPU architectures:
x86, x86_64, IA64, PowerPC, PowerPC 64, S/390, S/390x,
Sparc, Sparc 64, Alpha, ARM, MIPS, MIPSEL, M68K, and PA-RISC.
PostgreSQL
Compatible con el estándar SQL92/SQL99 Características:
Soporte a distintos tipos de datos y creación de tipos propios. Incorpora la estructura de datos array e incluye herencia entre tablas Permite la realización de consultas complejas y la creación de vistas. Declaración de funciones y definición de disparadores (triggers) Incluye el uso de índices, reglas y vistas. Gestión de diferentes usuarios y la asignación de permisos.
Puede ser extendido mediante la incorporación de
tipos de datos funciones operadores
funciones agregadas métodos de indexación lenguajes procedurales
Arquitectura de PostgreSQL
Cliente en modo texto: psql
pgAdmin III
Es una herramienta de desarrollo y administración para versiones gratuitas y comerciales de PostgreSQL Desarrollado desde 1997 SO: Linux, FreeBSD, Solaris, Mac OSX y Windows.
Más de 12 idiomas
Extensión gratuita bajo licencia PostgreSQL
Ejecutar pgAdmin III
Conectar a un servidor
Interface de pgAdmin III
Panel examinador de objetos
Enfoque jerárquico
Server groups: lista cada uno de los servidores instalados Tablespaces, Group Roles y Login Roles Databases: contiene cada una de las BD creadas en el servidor. Un nombre de BD es un contenedor que agrupa colecciones de elementos como Extensiones, Esquemas, etc.
Un esquema es un contenedor lógico que permite organizar grupos de objetos similares: tablas, vistas,
Panel Propiedades
Muestra propiedades y estadísticas del objeto seleccionado en el examinador de objetos
Panel SQL Muestra el código SQL utilizado para crear o modificar el objeto seleccionado
Barra de herramientas
Recordatorio: Una base de datos es una colección de esquemas, los cuales pueden contener tablas, funciones, vistas, disparadores, etc.
¿Alguna pregunta?
Trabajo práctico N° 3: Creación de bases de datos
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
Objetivo
Mostrar el proceso de creación de una base de datos desde
Un cliente de texto, utilizaremos la ventana de comandos de Windows
Un cliente gráfico: pgAdmin III
Un script desde la ventana de
Crear una BD desde un cliente de texto
Ejecutar la consola de comandos de Windows
Crear una BD desde un cliente de texto cd\Program Files\PostgreSQL\9.2\bin
Crear una BD desde un cliente de texto
dir
Crear una BD desde un cliente de texto createdb –help
Crear una BD desde un cliente de texto crear una BD llamada psql_newDB
Crear una BD desde un cliente de texto conectar a la BD: aplicación psql.
psql –help
Crear una BD desde un cliente de texto psql –U postgres – W psql_NewDB
Crear una BD desde un cliente de texto Crear una nueva BD, cuyo nombre será psql_ seguida de sus iniciales en mayúsculas
Crear una BD desde un cliente gráfico crear una BD utilizando pgAdmin III
Nombre de la base de datos: pga_newDB Descripción: Creación de una nueva base de datos empleando pgAdmin Propietario: postgres Template: postgres Tablespace: pg_default Encoding: UTF8
Crear una BD desde un cliente gráfico
Crear una BD desde un cliente gráfico
Comparación de herramientas de creación
Comparación de herramientas de creación
11
Crear una BD desde un cliente gráfico Crear una nueva BD y nombrarla como pga_ seguida de sus iniciales en mayúsculas. Por ejemplo, pga_MJQG.
Crear una BD desde un script Crear una base de datos usando la ventana de scripting usando los siguientes valores:
Nombre de la BD: scr_newDB Propietario: postgres Tablespace: pg_default Encoding: UTF8
Crear una BD desde un script Crear una nueva BD y nombrarla como scr_ seguida de sus iniciales en mayúsculas. Por ejemplo, scr_MJQG.
Crear una BD con soporte espacial Crear una nueva BD espacial
Nombre de la base de datos: M4_obras Descripción: Zonas de urbanización y construcciones Propietario: postgres Tablespace: pg_default Encoding: UTF8 Template: postgis_21_samples
Crear una BD con soporte espacial
Crear una BD con soporte espacial
Crear una BD desde un script Crear una nueva BDE y nombrarla como aseguradora.
Trabajo práctico N° 4: Creación de tablas
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
Creación de tablas
Creación de tablas
¿Qué debemos tener en cuenta?
Cada rectángulo amarillo representa una tabla . Cada elipse conectada por línea discontinúa es un atributo. La clave primaria se indica subrayada y en negrita. El color de los círculos indica el tipo de datos a utilizar. Las relaciones están representadas por líneas negras con la indicación de la cardinalidad. La tabla del lado N agrega el atributo clave de la tabla del lado 1.
Creación de tablas
Orden de creación: Ciudades – Marcas Propietarios – Autos Pólizas ActasSiniestros
Participantes
Creación de la tabla Ciudad
Creación de la tabla Ciudad •
Definición de las columnas
Creación de la tabla Ciudad •
Definición de la clave primaria
Creación de la tabla Ciudad •
Definición de la clave primaria
Creación de la tabla Ciudad
Creación tabla Propietarios
Creación tabla Propietarios
Creación de las tablas Marcas y Autos
Creación de la tabla Polizas
Empleando código SQL
Creación de la tabla Polizas
Empleando código SQL
Creación de la tabla Actas_Siniestros
Empleando código SQL
Creación de la tabla participantes
Trabajo práctico N° 5: Gestión de la base de datos Aseguradora
PRIMERA PARTE: CARGAR LA INFORMACIÓN EN LA BASE DE DATOS
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
Bases de datos Conceptual
Diseño
Lógico Físico
Mantenimiento
Uso Recuperación
Registrar datos en la BD
Formas de ingresar datos a la base de datos View Data Insert into Copy from
View Data
Ingresar contenido a la tabla Marcas
Edit Data en pgAdmin
Agregar datos mediante script
Agregar datos a la tabla ciudad
Agregar datos mediante COPY FROM
Agregar datos a la tabla PROPIETARIOS
Cargar datos en la tabla AUTOS
Utilizando cualquiera de los métodos, ingrese los datos siguientes a la tabla AUTOS.
Cargar datos en la tabla POLIZAS
Utilizando cualquiera de los métodos, ingrese los datos siguientes a la tabla POLIZAS.
Cargar datos en la tabla ACTAS_SINIESTROS
Utilizando cualquiera de los métodos, ingrese los datos siguientes a la tabla ACTAS_SINIESTROS.
Cargar datos en la tabla PARTICIPANTES
Utilizando cualquiera de los métodos, ingrese los datos siguientes a la tabla PARTICIPANTES.
Trabajo práctico N° 6: Gestión de la base de datos Aseguradora
SEGUNDA PARTE: RECUPERACIÓN DE LA INFORMACIÓN A L M A C E N A D A E N L A B A S E D E D A T O S
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
Consulta 1: Lista de todos los autos asegurados cuyos propietarios residan en la ciudad de Resistencia.
Pasos:
1) Qué campos necesitamos? En qué tabla se encuentran?
Consulta 1: Lista de todos los autos asegurados cuyos propietarios residan en la ciudad de Resistencia.
Pasos:
2) Qué relaciones podemos encontrar entre esas tablas?
Consulta 1: Lista de todos los autos asegurados cuyos propietarios residan en la ciudad de Resistencia.
Pasos:
3) Definir las restricciones
Consulta 1: Lista de todos los autos asegurados cuyos propietarios residan en la ciudad de Resistencia.
Pasos:
3) Definir las restricciones
Consulta 1: Lista de todos los autos asegurados cuyos propietarios residan en la ciudad de Resistencia.
Pasos:
4) Pensar cómo queremos ordenar los resultados
Consulta 1: Lista de todos los autos asegurados cuyos propietarios residan en la ciudad de Resistencia. Vista de código
Consulta 1: Lista de todos los autos asegurados cuyos propietarios residan en la ciudad de Resistencia.
Resultados:
Consulta 2: ¿ cuáles son los autos participantes en siniestros viales con montos por daños superiores a $ 5.000.?
Consulta 2: ¿ cuáles son los autos participantes en siniestros viales con montos por daños superiores a $ 5.000.?
Consulta 2: ¿ cuáles son los autos participantes en siniestros viales con montos por daños superiores a $ 5.000.?
Consulta 3: ¿Quiénes son los asegurados que han contratado pólizas para vehículos anteriores al año 2000?
Select
SELECT
FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ]
Consulta 4: cantidad de siniestros por tipo de siniestro ordenadas de manera decreciente según la cantidad.
Consulta 5: Retornar una lista que contenga los números de los informes en los cuales han participado más de un asegurado
Consulta 6: Modificar la consulta anterior, incluyendo los datos de los asegurados que han participado del mismo siniestro.
Consulta 7: Modificar la consulta anterior, incluyendo los datos de los asegurados que han participado del mismo siniestro.
Ejercicio adicional
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni Gialdroni
Ejercicio adicional
Esquema de la base de datos EMPRESA
Datos para para alimentar a las tablas
Datos para alimentar las tablas
Consultas Recuperar el salario y la dirección del empleado (o empleados) de nombre ‘Alberto Campos’ Recuperar el nombre y la dirección de todos los empleados que trabajan en el departamento ‘Investigación’ Recuperar los nombres de todos los empleados del departamento 5 que trabajan más de 10 horas por semana en el proyecto Producto X Localizar los nombres de todos los empleados supervisados directamente por ‘Alberto Campos’ Recuperar los nombres de todos los empleados que trabajan en cada proyecto Por cada departamento, recuperar su nombre y el salario promedio
Bases de datos espaciales
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
Base de datos espacial
Permite describir los objetos espaciales a través de tres características básicas: Atributos Localización
Topología
características de los objetos representada por la geometría del objeto ¿dónde está? ¿qué espacio ocupa? relaciones conceptuales y espaciales entre objetos
Bases de datos
SGBD deben incluir
Nuevos tipos de datos para permitir almacenar la geometría Funciones y operadores que se adapten a los datos espaciales Extensión del lenguaje SQL para la manipulación de los datos espaciales, incluyendo funciones como distancia, intersección, etc.
Ejemplos de bases de datos geográficas
Oracle - Oracle Spatial SQL Server SpatialLite MySQL Postgre - PostGIS
PostGIS Es software libre Es compatible con los estándares de OGC Es estable y rápido Soporta datos Geometry y Geography Disponibilidad de herramientas conversoras (shp2pgsql) Visualización en varios clientes de escritorio (QGIS)
Trabajo práctico N° 7: Importación de datos geográficos
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
Herramienta shp2pgsql
Importar un archivo shape
Crear el script de importación empleando shp2pgsql. La forma del comando es
shp2pgsql –s – W > Ejecutar el script. Visualizar el archivo importado
Importar el shape AMGR_calles
Paso 1: Crear el script de importación
shp2pgsql –s 32721 – W Latin1 d:\BasesDatos\shapes\amgr_calles.shp calles > d:\basesdatos\calles.sql
Paso 2: Ejecutar el script de importación
Paso 3: Visualizar el resultado en QGIS
Importar el shape AMGR_calles
Paso 1: Crear el script de importación
shp2pgsql –s 32721 – W Latin1 d:\BasesDatos\shapes\amgr_calles.shp calles > d:\basesdatos\calles.sql
Paso 2: Ejecutar el script de importación
Paso 3: Visualizar el resultado en QGIS
Importar el shape areaint
Paso 1: Crear el script de importación
shp2pgsql –s 32721 – W Latin1 d:\BasesDatos\shapes\areaint.shp municipios > d:\basesdatos\municipios.sql
Paso 2: Ejecutar el script de importación
Paso 3: Visualizar el resultado en QGIS
Importar el shape comisarias
Paso 1: Crear el script de importación
shp2pgsql –s 32721 – W Latin1 d:\BasesDatos\shapes\comisarias.shp comisarias > d:\basesdatos\comisarias.sql
Paso 2: Ejecutar el script de importación
Paso 3: Visualizar el resultado en QGIS
Importar el shape siniestrosL
Paso 1: Crear el script de importación
shp2pgsql –s 32721 – W Latin1 d:\BasesDatos\shapes\siniestrosL.shp siniestros > d:\basesdatos\siniestros.sql
Paso 2: Ejecutar el script de importación
Paso 3: Visualizar el resultado en QGIS
Importar el el shape zonas
Paso 1: Crear el script s cript de importación
shp2pgsql –s 32721 – W Latin1 d:\BasesDatos\shapes\zo d:\BasesDatos\shapes\zonas.shp nas.shp zonas > d:\basesdatos\zonas.sql
Paso 2: Ejecutar el script de importación
Paso 3: Visualizar el resultado en QGIS
Paso 2: Ejecución del script
Ejecutar el script scrip t
Paso 3: Visualizar en QGIS
Crear una conexión entre QGIS y PostGIS
Paso 3: Visualizar en QGIS
Marcar las capas que se desean conectar y hacer clic en añadir
Paso 3: Visualización en QGIS
Trabajo práctico N° 8: Realización de consultas geográficas
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
Consulta geográfica N° 1
Informar la cantidad de siniestros registrados para cada zona y visualizar los resultados en QGIS.
Consulta geográfica N° 1
Crear una vista para la visualización
Crear una vista
Visualización consulta geográfica 1
Consulta geográfica N° 2
Informar la cantidad de siniestros registrados para cada municipio y visualizar los resultados en QGIS
Visualización consulta geográfica 2
Consulta geográfica N° 3
Mostrar los siniestros ocurridos en Fontana y visualizar el resultado en QGIS
Visualización consulta geográfica 3
Consulta geográfica N° 4
Calcular la distancia de cada siniestros ocurridos en Fontana a la comisaría de Fontana y visualizar el resultado en QGIS
Visualización consulta geográfica 4
Visualización consulta geográfica 4
Consulta geográfica N° 5
Mostrar los siniestros viales ocurridos en la jurisdicción de la zona 4
Visualización consulta geográfica 5
Consulta geográfica N° 6
Desplegar las comisarías ubicadas en los municipios de Barranqueras y Puerto Vilelas.
Visualización consulta geográfica 6