PostgreSQL, presentación

M a r i e l a J . Q u i r o g a G i a l d r o n i

Diseo Fsico

[email protected] [email protected]

E S P E C IA L IZ A C I N E N TE C N O L O G A S D E LA IN F O R M A C I N G E O G R F I C A

Bases de datos

Lgico

Conceptual

Diseo

Fsico

Uso

Mantenimiento

Recuperacin

SGBD

Coleccin de datos y el conjunto de infraestructura necesaria para

Acceder a los mismos

Garantizar la integridad de los datos

Establecer los mecanismos de seguridad

Diseo de una base de datos

PostgreSQL

Versin de cdigo abierto de un ORDBMS desarrollado por el Departamento de Ciencias de la Computacin de la Universidad de California.

Puede ser usado, modificado y distribuido por cualquiera sin costo alguno para cualquier propsito.

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 estndar SQL92/SQL99

Caractersticas:

Soporte a distintos tipos de datos y creacin de tipos propios.

Incorpora la estructura de datos array e incluye herencia entre tablas

Permite la realizacin de consultas complejas y la creacin de vistas.

Declaracin de funciones y definicin de disparadores (triggers)

Incluye el uso de ndices, reglas y vistas.

Gestin de diferentes usuarios y la asignacin de permisos.

Puede ser extendido mediante la incorporacin de

tipos de datos

funciones

operadores

funciones agregadas

mtodos de indexacin

lenguajes procedurales

Arquitectura de PostgreSQL

Cliente en modo texto: psql

pgAdmin III

Es una herramienta de desarrollo y administracin para versiones gratuitas y comerciales de PostgreSQL

Desarrollado desde 1997

SO: Linux, FreeBSD, Solaris, Mac OSX y Windows.

Ms de 12 idiomas

Extensin gratuita bajo licencia PostgreSQL

Ejecutar pgAdmin III

Conectar a un servidor

Interface de pgAdmin III

Panel examinador de objetos

Enfoque jerrquico 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 lgico que permite organizar grupos de objetos similares: tablas, vistas, procedimientos almacenados, etc.

Panel Propiedades

Muestra propiedades y estadsticas del objeto seleccionado en el examinador de

objetos

Panel SQL

Muestra el cdigo SQL utilizado para crear o modificar el objeto seleccionado

Barra de herramientas

Recordatorio:

Una base de datos es una coleccin de esquemas, los cuales pueden contener tablas, funciones, vistas, disparadores, etc.

Alguna pregunta?

Trabajo prctico N 3: Creacin de bases de datos

Ing. Mariela Quiroga Gialdroni


Especializacin en Tecnologas de la Informacin Geogrfica Junio 2015

Objetivo

Mostrar el proceso de creacin de una base de datos desde

Un cliente de texto, utilizaremos la ventana de comandos de Windows

Un cliente grfico: pgAdmin III

Un script desde la ventana de

Crear una BD desde un cliente de texto

Ejecutar la consola de comandos de Windows


cd\Program Files\PostgreSQL\9.2\bin


dir


createdb help


crear una BD llamada psql_newDB


conectar a la BD:

aplicacin psql.

psql help


psql U postgres W psql_NewDB


Crear una nueva BD, cuyo nombre ser psql_ seguida de sus iniciales en maysculas

Crear una BD desde un cliente grfico

crear una BD utilizando pgAdmin III

Nombre de la base de datos: pga_newDB

Descripcin: Creacin de una nueva base de datos empleando pgAdmin

Propietario: postgres

Template: postgres

Tablespace: pg_default

Encoding: UTF8

Comparacin de herramientas de creacin

Comparacin de herramientas de creacin

11


Crear una nueva BD y nombrarla como pga_ seguida de sus iniciales en maysculas.

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



Encoding: UTF8


Crear una nueva BD y nombrarla como scr_ seguida de sus iniciales en maysculas.

Por ejemplo, scr_MJQG.

Crear una BD con soporte espacial

Crear una nueva BD espacial

Nombre de la base de datos: M4_obras

Descripcin: Zonas de urbanizacin y construcciones



Encoding: UTF8

Template: postgis_21_samples

Crear una BD con soporte espacial


Crear una nueva BDE y nombrarla como aseguradora.

Trabajo prctico N 4: Creacin de tablas




Creacin de tablas

Creacin de tablas

Qu debemos tener en cuenta?

Cada rectngulo amarillo representa una tabla .

Cada elipse conectada por lnea discontina es un atributo.

La clave primaria se indica subrayada y en negrita.

El color de los crculos indica el tipo de datos a utilizar.

Las relaciones estn representadas por lneas negras con la indicacin de la cardinalidad.

La tabla del lado N agrega el atributo clave de la tabla del lado 1.

Creacin de tablas

Orden de creacin:

Ciudades Marcas

Propietarios Autos

Plizas

ActasSiniestros

Participantes

Creacin de la tabla Ciudad


Definicin de las columnas


Definicin de la clave primaria

Creacin tabla Propietarios

Creacin de las tablas Marcas y Autos

Creacin de la tabla Polizas

Empleando cdigo SQL

Creacin de la tabla Actas_Siniestros

Empleando cdigo SQL

Creacin de la tabla participantes

P R IM E R A P A R TE :

C A R G A R LA IN F O R M A C I N E N LA B A S E D E

D A TO S

Trabajo prctico N 5: Gestin de la base de datos Aseguradora




Bases de datos

Lgico

Conceptual

Diseo

Fsico

Uso

Mantenimiento

Recuperacin

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 mtodos, ingrese los datos siguientes a la tabla AUTOS.

Cargar datos en la tabla POLIZAS

Utilizando cualquiera de los mtodos, ingrese los datos siguientes a la tabla POLIZAS.

Cargar datos en la tabla ACTAS_SINIESTROS

Utilizando cualquiera de los mtodos, ingrese los datos siguientes a la tabla ACTAS_SINIESTROS.

Cargar datos en la tabla PARTICIPANTES

Utilizando cualquiera de los mtodos, ingrese los datos siguientes a la tabla PARTICIPANTES.

S E G U N D A P A R TE :

R E C U P E R A C I N D E LA IN F O R M A C I N

A LM A C E N A D A E N LA B A S E D E D A TO S

Trabajo prctico N 6: Gestin de la base de datos Aseguradora




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?


Pasos:

2) Qu

relaciones podemos encontrar entre esas tablas?


Pasos:

3) Definir las

restricciones


Pasos:

4) Pensar

cmo queremos ordenar los resultados


Vista de cdigo


Resultados:

Consulta 2: cules son los autos participantes en siniestros viales con montos por daos superiores a $ 5.000.?

Consulta 3: Quines son los asegurados que han contratado plizas para vehculos anteriores al ao 2000?

Select

SELECT

FROM

[WHERE ]

[GROUP BY ]

[HAVING ]

[ORDER BY ]

Consulta 4: cantidad de siniestros por tipo de siniestro ordenadas de manera decreciente segn la cantidad.

Consulta 5: Retornar una lista que contenga los nmeros de los informes en los cuales han participado ms 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




Esquema de la base de datos EMPRESA

Datos para alimentar a las tablas

Datos para alimentar las tablas

Consultas

Recuperar el salario y la direccin del empleado (o empleados) de nombre Alberto Campos

Recuperar los nombres de todos los empleados del departamento 5 que trabajan ms de 10 horas por semana en el proyecto Producto X

Recuperar el nombre y la direccin de todos los empleados que trabajan en el departamento Investigacin

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




Base de datos espacial

Permite describir los objetos espaciales a travs de tres caractersticas bsicas:

Atributos caractersticas de los objetos

Localizacin representada por la geometra del objeto

dnde est? qu espacio ocupa?

Topologa relaciones conceptuales y espaciales entre objetos

Bases de datos

SGBD deben incluir

Nuevos tipos de datos para permitir almacenar la geometra

Funciones y operadores que se adapten a los datos espaciales

Extensin del lenguaje SQL para la manipulacin de los datos espaciales, incluyendo funciones como distancia, interseccin,

etc.

Ejemplos de bases de datos geogrficas

Oracle - Oracle Spatial

SQL Server

SpatialLite

MySQL

Postgre - PostGIS

PostGIS

Es software libre

Es compatible con los estndares de OGC

Es estable y rpido

Soporta datos Geometry y Geography

Disponibilidad de herramientas conversoras (shp2pgsql)

Visualizacin en varios clientes de escritorio (QGIS)

Trabajo prctico N 7: Importacin de datos geogrficos




Herramienta shp2pgsql

Importar un archivo shape

Crear el script de importacin 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 importacin

shp2pgsql s 32721 W Latin1 d:\BasesDatos\shapes\amgr_calles.shp calles > d:\basesdatos\calles.sql

Paso 2: Ejecutar el script de importacin

Paso 3: Visualizar el resultado en QGIS

Importar el shape areaint


shp2pgsql s 32721 W Latin1 d:\BasesDatos\shapes\areaint.shp municipios > d:\basesdatos\municipios.sql



Importar el shape comisarias


shp2pgsql s 32721 W Latin1 d:\BasesDatos\shapes\comisarias.shp comisarias > d:\basesdatos\comisarias.sql



Importar el shape siniestrosL


shp2pgsql s 32721 W Latin1 d:\BasesDatos\shapes\siniestrosL.shp siniestros > d:\basesdatos\siniestros.sql



Importar el shape zonas


shp2pgsql s 32721 W Latin1 d:\BasesDatos\shapes\zonas.shp zonas > d:\basesdatos\zonas.sql



Paso 2: Ejecucin del script

Ejecutar el script

Paso 3: Visualizar en QGIS

Crear una conexin entre QGIS y PostGIS

Paso 3: Visualizar en QGIS

Marcar las capas que se desean conectar y

hacer clic en aadir

Paso 3: Visualizacin en QGIS

Trabajo prctico N 8: Realizacin de consultas geogrficas




Consulta geogrfica N 1

Informar la cantidad de siniestros registrados para cada zona y visualizar los resultados en QGIS.

Crear una vista para la visualizacin

Crear una vista

Visualizacin consulta geogrfica 1


Informar la cantidad de siniestros registrados para cada municipio y visualizar los resultados en QGIS


Mostrar los siniestros ocurridos en Fontana y visualizar el resultado en QGIS


Calcular la distancia de cada siniestros ocurridos en Fontana a la comisara de Fontana y visualizar el resultado en QGIS


Mostrar los siniestros viales ocurridos en la jurisdiccin de la zona 4


Desplegar las comisaras ubicadas en los municipios de Barranqueras y Puerto Vilelas.

Muchas gracias!!!

PostgreSQL, presentación

Documents

Transcript of PostgreSQL, presentación