Download BDE_Practicos

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 <lista de atributos y funciones>
FROM <lista de tablas>
[WHERE <condiciones>]
[GROUP BY <atributos de agrupamiento>]
[HAVING <condiciones de agrupamiento>]
[ORDER BY <lista de atributos>]
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 <SRID> –W <Encoding> <ruta
y nombre de archivo shape> <nombre de la
tabla a crear> > <nombre del script>
 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!!!