Transcript
Diseño Físico ESPECIAL IZ A C IÓ N EN TECNOL O G ÍAS D E LA IN FORM AC I ÓN GEOGRÁF I CA
Ma riel a J . Quiroga Gia l droni
[email protected] –
[email protected]
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 Garantizar la integridad de los datos 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, procedimientos almacenados, etc.
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
[email protected] –
[email protected]
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
[email protected] –
[email protected]
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
PRIM ERA PARTE: CARGAR LA IN FORM AC I ÓN EN LA BAS E D E D ATOS
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
[email protected] –
[email protected]
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
SEGU ND A PARTE: RECU PERACI ÓN D E LA IN FORM AC I ÓN ALM ACEN AD A EN LA BAS E D E D ATOS
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni
[email protected] –
[email protected]
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 [email protected] – [email protected]
Ejercicio adicional
Esquema de la base de datos EMPRESA
Datos 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 de todos los empleados que trabajan en él.
Bases de datos espaciales
Especialización en Tecnologías de la Información Geográfica Junio 2015
Ing. Mariela Quiroga Gialdroni [email protected] – [email protected]
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 [email protected] – [email protected]
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 shape zonas Paso 1: Crear el script de importación
shp2pgsql –s 32721 –W Latin1 d:\BasesDatos\shapes\zonas.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
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 [email protected] – [email protected]
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
Muchas gracias!!!