UNIVERSIDAD TECNOLÓGICA NACIONAL FA C U LTA D R E G I O N A L T U C U M Á N

Base de Datos Avanzados

5º Nivel - I.S.I.

Trabajo Práctico N°1

Grupo:

Javier Alejandro CaniparoliLuis Pablo CardozoPriscilla RuedaSocci Sebastián

Comisión: 5K11) Seleccionar uno o varios motores de DB, instalarlo y hacer el paso a paso de la instalación.(L/WAMP, SQL SRV, Oracle, Sysbase, Posgre, etc.)

El motor de DB a usar para la resolución del siguiente practico es PostgreSQL.

PostgreSQL es un Sistema de Gestión de Base de Datos (SGBD) relacional orientada a objetos y libre, publicado bajo la licencia BSD

INSTALACIÓN (Ubuntu 12.04 x86)

La instalación se realizara sobre Ubuntu 12.04 x86 del motor de DB PostgreSQL 9.1.5

(1) Abrir una nueva Terminal:

(2) Tipear el siguiente comando dentro de ella “sudo apt-get install postgresql postgresql-client postgresql-contrib libpq-dev postgresql-client-common postgresql-common”

Página 2

(3) Teclear 'S' (si) a todas las preguntas que nos surjan durante la instalación

(4) Confirmemos que la instalación terminó adecuadamente tecleando “psql –version”

Página 3

(5) Cambiar la contraseña del usuario administrador. Ahora necesitamos establecer la contraseña del usuario administrador postgres.Teclea la siguiente línea en la terminal para iniciar PostgreSQL “sudo su postgres -c psql”

(6) Una vez dentro de PostgreSQL tecleamos el siguiente comoando “ALTER USER postgres WITH PASSWORD 'password' ” (cambia la palabra password por la contraseña que desees usar). Despues de esto escribimos “\q” :

Página 4

(7) Eso altera la contraseña dentro de la base de datos, ahora necesitamos hacer lo mismo para el usuario Linux postgres:

12

sudo passwd -d postgressudo su postgres -c passwd

Al ejecutar el comando “sudo su postgres -c passwd” te aparecerá un prompt, introduce la misma contraseña que pusiste antes.

(8) Cambiar el esquema de autenticación de PostgreSQL.

Al ejecutar algunos comandos de base de datos, es posible que te encuentres con un error que dice algo como:

FATAL: Peer authentication failed for user “X”

Para evitarlo necesitas editar el archivo /etc/postgresql/9.1/main/pg_hba.conf y cambiar el esquema de autenticación. Abre el archivo con privilegios de root:

1 sudo gedit /etc/postgresql/9.1/main/pg_hba.conf

Y cambia esto:

12

# Database administrative login by Unix domain socketlocal all postgres peer

Por:

Página 5

12

# Database administrative login by Unix domain socketlocal all postgres md5

Reinicia el servidor de PostgreSQL tecleando en tu terminal:

1 sudo /etc/init.d/postgresql restart

(9) Para finalizar la instalación recomendamos instalar el programa “PGADMIN3”. Para esto tecleamos dentro de la consola el siguiente comando “sudo apt-get install pgadmin3 ”

INSTALACIÓN (Windows 7 x64)

Abriremos un navegador web y accederemos a la URL:http://www.postgresql.org/download/windows

En nuestro caso descargaremos la versión aún no estable 9.1.5 para Windows de 64 bits

Una vez descargado el archivo de instalación de PostgreSQL pulsaremos con el botón derecho del ratón sobre él y seleccionaremos "Ejecutar como administrador":

Página 6

Si tenemos activado el control de cuentas de usuario nos mostrará una advertencia con el texto "¿Desea permitir que este programa realice cambios en el equipo?", pulsaremos "Sí" para continuar con la instalación de PostgreSQL. Es importante realizar este paso ya que de no ejecutarlo así, nos saldrá un error en la instalación:

Se iniciará el asistente para instalar PostgreSQL, pulsaremos "Siguiente":

Indicaremos la carpeta de instalación de PostgreSQL, donde se guardarán los ejecutables, librerías y ficheros de configuración de PostgreSQL:

Página 7

Indicaremos también la carpeta donde se guardarán los datos por defecto de PostgreSQL:

Introduciremos la contraseña para el superusuario "postgres" que será con el que iniciemos sesión para administrar la base de datos:

Introduciremos el puerto de escucha para la conexión con el servidor PostgreSQL, por defecto el 5432:

Seleccionaremos la configuración regional, en nuestro caso, Español - Argentina:

Página 8

Pulsaremos "Siguiente", se iniciará el asistente para instalar el motor de base de datos PostgreSQL, que creará las carpetas oportunas, copiará los ficheros necesarios y creará el servicio para iniciar de forma automática el motor de base de datos:

Una vez finalizada la instalación el asistente nos dará la posibilidad de ejecutar Stack Builder, aplicación que nos permitirá instalar otros componentes y herramientas para PostgreSQL:

Si hemos marcado la opción de Stack Builder, se iniciará, seleccionaremos "PostgreSQL 9.1.5 on port 5432" y pulsaremos "Next":

Seleccionaremos las aplicaciones, componentes y herramientas a instalar y pulsaremos "Next":

Página 9

Se abrirá una ventana donde nos mostrará el progreso de la descarga de los paquetes adicionales. No se apresure, es un proceso lento dependiendo de la velocidad de conexión:

Se iniciaran varias ventanas de instalación dependiendo de la cantidad de programas adicionales elegidos. Es importante proceder con cautela en cada una, completando los recuadros de acuerdo a los parámetros elegidos al comienzo de la instalación (host, password, usuario, puerto, etc):

También nos aparecerá una ventana donde nos permitirá crear una cuenta de postgre para validar el software instalado. Deberemos completar con nuestros datos validos en la página web que se abrirá. Luego de esto, se nos enviará un mail al correo elegido para poder validar finalmente. Esta suscripción nos asignara un password aleatorio el cual deberemos cambiar para poder recordarlo. El cambio de contraseña puede hacerse fácilmente desde el sitio web abierto luego de confirmar la cuenta presionando en el link recibido en el correo.

Al finalizar toda la instalación, el asistente para instalar el servidor PostgreSQL habrá creado un servicio que estará iniciado y en tipo de inicio automático llamado:

Página 10

"postgresql-x64-9.1 – PostgreSQL Server 9.1":

Y habrá creado la carpeta en archivos de programa "PosgreSQL" con las subcarpetas:

Con esto habremos convertido nuestro equipo Microsoft Windows 7 en un servidor de base de datos PostgreSQL.Si queremos que los equipos de nuestra red tengan acceso al servidor PostgreSQL y tenemos algún cortafuegos (firewall) deberemos abrir el puerto 5432. Y si queremos que se tenga acceso desde Internet al servidor PostgreSQL deberemos redireccionar (mapear) el puerto 5432 en el router o cortafuegos de nuestra empresa.

2) Una vez instalados determinar las herramientas (software para el usuario, para el

Página 11

Administrador, generador de consultas, etc) que trae el paquete y describir cada una deellas.

De la instalación realizada en el paso anterior podemos reconocer el siguiente conjunto de herramientas:

Ubuntu 12.04 x86:

libpq5: Libreria cliente de PostgreSQL C

postgresql-client-common: Administrador para multiples versiones de clientes PostgreSQL.

postgresql-common: PostgreSQL database-cluster manager

pgAdmin3: El  máximo  exponente  de  cliente  gráfico  de  PostgreSQL  es  el  software pgAdmin3 que tiene licencia “Artist License”, aprobada por la FSF.En pgAdmin3 podemos ver y trabajar con casi todos los objetos de la base de datos, examinar sus propiedades y realizar tareas administrativas. Tiene soporte para Agregados, Constraints, Bases de datos, Funciones, Grupos, Índices, Lenguajes (PLpgsql, PLpython, PLperl, etc.), Clases de operadores, Servidores PostgreSQL, Reglas, Esquemas, Triggers, Usuarios, Vistas, etc.Una característica interesante de pgAdmin3 es que, cada vez que realizamos alguna modificación en un objeto, escribe la/s sentencia/s SQL correspondiente/s, lo que hace que, además de una herramienta muy útil, sea a la vez didáctica.pgAdmin3 también incorpora funcionalidades para realizar consultas, examinar su ejecución (como el comando explain) y trabajar con los datos.

Windows 7 x64:

SQL Shell (psql): Este es la consola por la cual nos podemos conectar al motor, al iniciar nos pedirá los datos de conexión. Luego de completar todo nos permitirá hacer las consultas y cambios permitidos para el usuario logueado:

pgAdmin III: La herramienta más importante instalada con el servidor de BD. Con ella

Página 12

podremos hacer las siguientes cosas:

Administración de PostgreSQL, creación de usuarios (roles), bases de datos, vistas, es-quemas, tablespace, entre otras cosas.

Para el acceso a la administración del motor de base de datos PostgreSQL accederemos al botón "Iniciar" - "PostgreSQL 9.1" - "pgAdmin III":

Desplegaremos "Server Groups", dentro desplegaremos "Servidores" y dentro de éste pulsaremos con el botón derecho del ratón sobre "PostgreSQL 9.1 (localhost:5432), en el menú emergente seleccionaremos "Conectar":

Introduciremos la contraseña para el superusuario postgres (la contraseña introducida en la instalación):

Página 13

Si todo es correcto nos conectaremos al servidor PostgreSQL, desde pgAdmin podremos configurar y administrar el servidor de PostgreSQL:

Para crear un rol de login pulsaremos con el botón derecho del ratón sobre "Roles de Lo-gin", seleccionaremos "Nueva Rol de Login":

En la pestaña "Propiedades" introduciremos los siguientes datos: Nombre del Rol: nombre del usuario, en nuestro caso "root". Contraseña: contraseña para este usuario (rol): “admin” La cuenta caduca: si queremos que la cuenta de usuario caduque en una fecha la

estableceremos en este campo.

Página 14

También podremos crear un tableSpace que es donde se guardaran las bases de datos a crear en el futuro, es decir, se puede hacer que cada usuario tenga un tablespace diferen-te. En nuestro caso tendremos el tablespace “tp_dbavanzados” donde el usuario “root” guardara sus DB:

 

phpPgAdmin: Este es otro complemento instalado con el motor, nos permite gestionar la Base de Datos a través de un navegador como Internet Explorer, Chrome, etc.

Página 15

Postgres Enterprise Manager: Similar a los anteriores solo que con mas características, con el podremos hacer BackUp o Restore, sacar estadísticas y Data Minning. La desventaja es que es de prueba:

3) Describir los servicios que se activaron con el SO

postgresql: Servidor de Base de Datos SQL objeto-relacional, version 9.1

postgresql-client: Aplicaciones front-end para PostgreSQL 9.1

Página 16

4) Diseñar una DB relacional y crearla y montarla en motor instalado (teniendo en cuenta lacreación de tablas, índices).

Script de creación de elementos de la base de datos del modelo de arriba:

CREATE TABLE "Ciudad"( idciudad integer NOT NULL DEFAULT nextval('ciudad_idciudad_seq'::regclass), nombre character varying(250) NOT NULL, idprovincia integer NOT NULL, creado timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT ciudad_pkey PRIMARY KEY (idciudad ), CONSTRAINT ciudad_idprovincia_fkey FOREIGN KEY (idprovincia) REFERENCES "Provincia" (idprovincia) MATCH SIMPLE

Página 17

ON UPDATE NO ACTION ON DELETE NO ACTION) WITH ( OIDS=FALSE);

CREATE TABLE "Cliente"( idcliente serial NOT NULL, nombre character varying(50) NOT NULL, apellido character varying(50) NOT NULL, direccion integer NOT NULL, CONSTRAINT pk_idcliente PRIMARY KEY (idcliente ), CONSTRAINT fk_direccion FOREIGN KEY (direccion) REFERENCES "Direccion" (iddireccion) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)WITH ( OIDS=FALSE);CREATE TABLE "Direccion"( iddireccion integer NOT NULL DEFAULT nextval('direccion_iddireccion_seq'::regclass), calle character varying(50) NOT NULL, numero integer, fk_ciudad integer NOT NULL DEFAULT nextval('direccion_fk_ciudad_seq'::regclass), CONSTRAINT pk_iddireccion PRIMARY KEY (iddireccion ), CONSTRAINT fk_ciudad FOREIGN KEY (fk_ciudad) REFERENCES "Ciudad" (idciudad) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)WITH ( OIDS=FALSE);

CREATE TABLE "Empleado"( idempleado serial NOT NULL, nombre character varying(50) NOT NULL, apellido character varying(30) NOT NULL, direccion integer NOT NULL, CONSTRAINT idempleado PRIMARY KEY (idempleado ), CONSTRAINT fk_direccion FOREIGN KEY (direccion) REFERENCES "Direccion" (iddireccion) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)WITH ( OIDS=FALSE);CREATE TABLE "Familiar"( idfamiliar serial NOT NULL, nombre character varying(50) NOT NULL, apellido character varying(50) NOT NULL, direccion integer NOT NULL, CONSTRAINT pk_idfamiliar PRIMARY KEY (idfamiliar ), CONSTRAINT fk_direccion FOREIGN KEY (direccion) REFERENCES "Direccion" (iddireccion) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)

Página 18

WITH ( OIDS=FALSE);

CREATE TABLE "Grupo_familiar"( idgrupo_familiar integer NOT NULL DEFAULT nextval('grupo_familiar_idgrupo_familiar_seq'::regclass), idfamiliar integer NOT NULL, relacion integer NOT NULL, idempleado integer NOT NULL, CONSTRAINT pk_idgrupo PRIMARY KEY (idgrupo_familiar , idfamiliar , idempleado ), CONSTRAINT fk_familiar FOREIGN KEY (idfamiliar) REFERENCES "Familiar" (idfamiliar) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_idempleado FOREIGN KEY (idempleado) REFERENCES "Empleado" (idempleado) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_relacion FOREIGN KEY (relacion) REFERENCES "Relacion" (idrelacion) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)WITH ( OIDS=FALSE);

CREATE TABLE "Pais"( idpais character(3) NOT NULL, nombre character varying(250) NOT NULL, creado timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT pais_pkey PRIMARY KEY (idpais ))WITH ( OIDS=FALSE);CREATE TABLE "Provincia"( idprovincia integer NOT NULL DEFAULT nextval('provincia_idprovincia_seq'::regclass), nombre character varying(250) NOT NULL, idpais character varying(3) NOT NULL, creado timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT provincia_pkey PRIMARY KEY (idprovincia ), CONSTRAINT provincia_idpais_fkey FOREIGN KEY (idpais) REFERENCES "Pais" (idpais) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION)WITH ( OIDS=FALSE);CREATE TABLE "Relacion"( idrelacion serial NOT NULL, nombre character varying(20) NOT NULL, CONSTRAINT pk_idrelacion PRIMARY KEY (idrelacion ))WITH ( OIDS=FALSE);

Página 19

5) Crear store procedures que permitan cargar datos en las tablas utilizando el leguaje deSQL.

Básicamente la estructura de un Store Procedure es la siguiente:

CREATE FUNCTION [nombre de la función] ([parámetros separados por comas]) RETURNS [el tipo de dato que regresa] AS'DECLARE aquí se definen las variables que se usarán.BEGIN indica el inicio de la función.SELECT INTO este comando permite que los resultados de las consultas sean asignados a variables. (no debe de confundirse con SELECT [columnas] INTO)RETURN Sale de la función y regresa el tipo de dato que se declaro después de la palabra RETURNS del CREATE FUNCTIONEND indica el fin de la función' LANGUAGE indica con que lenguaje esta escrita la función, puede ser un lenguaje de procedimiento (plpgsql) o de consulta (SQL).

Creamos una función para convertir la primera letra en mayúscula:

CREATE FUNCTION UpperCamelCase(varchar) RETURNS varchar AS'DECLARE res varchar;BEGIN SELECT INTO res UPPER(SUBSTRING($1,1,1)) || LOWER(SUBSTRING($1,2)); return res;END;'LANGUAGE 'plpgsql';

Ahora escribiremos las funciones para insertar registros en algunas de las tablas.

CREATE FUNCTION InsertarPais (varchar,varchar) RETURNS int AS'DECLARE regs record; res numeric;BEGIN SELECT INTO regs count(nombre) FROM Pais WHERE nombre = UpperCamelCase($1); IF regs.count = 0 THEN INSERT INTO Pais (idpais,nombre) VALUES(UPPER($1,$2)); SELECT INTO res idpais FROM Pais WHERE idpais = UpperCamelCase($1); RETURN res; END IF; IF regs.count > 0 THEN SELECT INTO res idpais FROM Pais WHERE idpais = UpperCamelCase($1); RETURN res; END IF;END;' LANGUAGE 'plpgsql';

CREATE FUNCTION InsertarProvincia (varchar,varchar) RETURNS int AS'DECLARE regs record;

Página 20

res numeric;BEGIN SELECT INTO regs count(nombre) FROM Provincia WHERE nombre = UpperCamelCase($1); IF regs.count = 0 THEN INSERT INTO Provincia (nombre,idpais) VALUES(UPPER($1,$2)); SELECT INTO res idprovincia FROM Provincia WHERE nombre = UpperCamelCase($1); RETURN res; END IF; IF regs.count > 0 THEN SELECT INTO res idprovincia FROM Provincia WHERE nombre = UpperCamelCase($1); RETURN res; END IF;END;' LANGUAGE 'plpgsql';

CREATE FUNCTION InsertarCiudad (varchar,numeric) RETURNS int AS'DECLARE regs record; res numeric;BEGIN SELECT INTO regs count(nombre) FROM Ciudad WHERE nombre = UpperCamelCase($1); IF regs.count = 0 THEN INSERT INTO Ciudad (nombre,idprovincia)VALUES(UPPER($1,$2)); SELECT INTO res idciudad FROM Ciudad WHERE nombre = UpperCamelCase($1); RETURN res; END IF; IF regs.count > 0 THEN SELECT INTO res idciudad FROM Ciudad WHERE nombre = UpperCamelCase($1); RETURN res; END IF;END;' LANGUAGE 'plpgsql';

Página 21

6) Identificar las DBs pertenecientes a los metadatos. Hacer una descripción de cada una yanalizar la estructuras de sus tablas (las mas importantes).

PostgreSQL posee un “catalog” (catalogo) e “information_schema” (esquema de informacion) robustos que proveen todo lo necesario para conocer sobre como una base de datos se arma.

El “information_schema” consiste de un conjunto de views (vistas) que contienen informacion sobre los objetos definidos en la base de datos actual. El “information_schema” se encuentra definido en el estandar SQL, por lo tanto le permite ser portable y altamente estable (a diferencia del “catalogs” que es especifico a PostgreSQL). Las views (vistas) del “information_schema” no continenen informacion sobre caracteristicas especificas de PostgreSQL; para esto seria necesario consultar los “catalogs” (catálogos) del sistema u otros views específicos de PostgreSQL.

Los “catalogs” del sistema son el lugar donde un sistema de bases de datos relacional almacena los metadatos de esquema, tales como información sobre las tablas y columnas, y la información de la contabilidad interna. Los “catalogs” del sistema de PostgreSQL son tablas normales. Podemos eliminar y volver a crear estas tablas, añadir columnas, insertar y actualizar valores, pero de esa manera podemos estropear nuestros sistemas. Normalmente, no hay que cambiar los catálogos del sistema a mano, siempre hay comandos SQL para hacerlo. (Por ejemplo, CREATE DATABASE inserta una fila en el catálogo pg_database - y de hecho crea la base de datos en el disco.) Hay algunas excepciones para operaciones particularmente esotéricas, como la adición de métodos de acceso de index.

Los “catalogs” del sistema los podemos encontrar dentro de la base de datos “pg_catalog”, dentro de ella las tablas que podemos encontrar son:

Nombre del Catalog Proposito

pg_aggregate Agregacion de funciones

pg_am metodos de acceso a indexs

pg_amop operadores de metodos de acceso

pg_amproc procedimientos de soporte a metodos de acceso

pg_attrdef valores por defecto de columnas

pg_attribute columnas de tablas (“atributos”)

pg_authid identificadores de autorizacion (roles)

pg_auth_members Relaciones de Identificadores de autorizacion de miembros

pg_cast casts (conversion de tipos de datos)

pg_class tablas, indexes, sequencias, views ("relaciones")

pg_constraintConstantes check, constantes unique, constantes primary key, constantes foreign key.

pg_conversion informacion de conversion encriptada

pg_database bases de datos dentro de este cluster de base de datos

pg_db_role_setting configuraciones por rol y por base de datos

pg_default_acl privilegios por defecto para tipos de objetos

pg_depend dependencias entre objetos de la base de datos

pg_description descripciones o comentarios sobre los objetos de base de datos

Página 22

Nombre del Catalog Proposito

pg_enum enumeración de etiquetas y valores de definicio

pg_foreign_data_wrapper Definiciones de foreign-data wrapper

pg_foreign_server Definiciones de foreign servers

pg_index Informacion adicional de indexs

pg_inherits table inheritance hierarchy

pg_language Lenguajes para escribir funciones

pg_largeobject Paginas de datos para objetos grandes

pg_largeobject_metadata metadata para grandes objetos

pg_namespace schemas

pg_opclass clases de operadores de metodos de acceso

pg_operator operadores

pg_opfamily Familias de operadores de metodos de acceso

pg_pltemplate template de datos para lenguajes procedurales

pg_proc Funciones y procedimientos

pg_rewrite Reglas de reescritura de querys

pg_shdepend Dependencias en objetos compartidos

pg_shdescription Comentarios en objetos compartidos

pg_statistic planner statistics

pg_tablespace tablespaces del cluster de databases

pg_trigger triggers

pg_ts_config Configuraciones de busqueda de texto

pg_ts_config_map Configuraciones de busqueda de texto (token mappings)

pg_ts_dict Diccionarios de busqueda de texto

pg_ts_parser Parsers de busqueda de texto

pg_ts_template Templates de busqueda de texto

pg_type data types

pg_user_mapping mappings de usuarios para foreign servers

Página 23

Referencias:

http://www.postgresql.org

http://www.ajpdsoft.com/modules.php?name=News&file=article&sid=489#.UE1p2vJq3Ld

http://xomalli.blogspot.com.ar/2010/09/trabajando-con-store-procedures-con.html

http://phenobarbital.wordpress.com/2008/02/25/descubriendo-la-metadata-de-una-tabla-postgresql/

http://grover.open2space.com/content/postgresql-meta-data

http://www.postgresql.org/docs/9.0/interactive/information-schema.html

http://www.postgresql.org/docs/9.0/interactive/catalogs-overview.html

http://www.postgresql.org.es/node/297

http://www.postgresqlya.com.ar/

http://www.dataprix.com/8-cliente-grafico-pgadmin3

http://www.pgadmin.org/

http://www.arpug.com.ar/trac/wiki/PgAdmin

http://es.wikipedia.org/wiki/PostgreSQL

http://lobotuerto.com/blog/2012/08/30/como-instalar-postgresql-en-ubuntu/

http://socrateos.blogspot.com.ar/2012/08/installing-postgresql-91-on-ubuntu-1204.html

http://www.guia-ubuntu.org/index.php?title=PostgreSQL

http://stackoverflow.com/questions/8181604/postgres-9-1-vs-mysql-5-6-innodb

http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

http://www.aprendeinformaticaconmigo.com/postgresql-vs-mysql

Página 24