Manual_PostgreSQL

Document Sample
Manual_PostgreSQL Powered By Docstoc
					ESCUELA POLITÉCNICA NACIONAL
        BASE DE DATOS




PROYECTO FINAL DE
    BASE DE
     DATOS


      POR:
 EDISON LOGACHO

   INGENIERA:
 ROSA NAVARRATE




              1
                ESCUELA POLITÉCNICA NACIONAL
                             BASE DE DATOS




                           ÍNDICE TE TEMAS:


INTRODUCCION

BREVE HISTORIA DE POSTGRESQL………………………………………………………………………………3
QUE ES POSTGRESQL………………………………………………………………………………………………….4
CARACTERISTICAS DE POSTGRESQL…………………………………………………………………………….4
OPINION PERSONAL……………………………………………………………………………………………………5
ACERCA DE ESTA VERSION DE POSTGRESQL………………………………………………………………..5
VENTAJAS Y DESVENTAJAS………………………………………………………………………………………….7
BENCHMARKING…………………………………………………………………………………………………………7
CODIGO ABIERTO VS PRODUCTO COMERCIAL……………………………………………………………..7
INSTALACION DE POSTGRESQL…………………………………………………………………………………….8
DESCARGA DEL INSTALADOR ………………………………………………………………………………………12
INSTALACION DE POSTGRESQL EN NUESTRO COMPUTADOR……………………………………….13
INSTALACION DE STACK BUILDER………………………………………………………………………………..19
INTRODUCCION A DBMS POSTGRESQL V.902………………………………………………………………23
IDEAS BASICAS ACERCA DEL FUNCIONAMIENTO………………………………………………………….24
SGURIDAD EN POSTGRESQL…………………………………………………………………………………………24
COPIA DE SEGURIDAD………………………………………………………………………………………………….27
CARACTERISTICAS DE POSTGRESQL QUE HACEN LA MAS SEGURADEL MERCADO………..31
PRESTACIONES………………………………………………………………………………………………..…….…….32
LIMITACIONES………………………………………………………………………………………………….………….32
VENTAJAS………………………………………………………………………………………………………………..….32
COMPARANDO POSTGRESQL CON OTRAS BASE DE DATOS………………………………………….33
SENTENCIAS DDL…………………………………………………………………………………………………………34
SENTENCIAS DML………………………………………………………………………………………………………...37
ARRANQUE DEL DBMS………………………………………………………………………………………………….39
CREACION DE LA BASE DE DATOS PEDIDOS……………………………………………………………………41
CREACION DE TABLAS…………………………………………………………………………………………………..43
SENTENCIAS………………………………………………………………………………………………………………….45
IMPLEMENTACION DE LA BASE DE DATOS PEDIDOS……………………………………………………..48
CONSULTAS…………………………………………………………………………………………………………………..53
SUBCONSULTAS……………………………………………………………………………………………………………54
BIBLIOGRAFIA……………………………………………………………………………………………………………….55




                                    2
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS



BREVE HISTORIA DE POSTGRESQL

Postgres, desarrollada originalmente en el Departamento de Ciencias de la Computación de la
Universidad de California en Berkeley, fue pionera en muchos de los conceptos de bases de
datos relacionales orientadas a objetos que ahora empiezan a estar disponibles en algunas
bases de datos comerciales. Ofrece suporte al lenguaje SQL92/SQL3, integridad de
transacciones, y extensibilidad de tipos de datos.
PostgreSQL es un descendiente de dominio público y código abierto del código original de
Berkeley.
 Con cerca de una década de desarrollo tras él, PostgreSQL es el gestor de bases de datos de
código abierto más avanzado hoy en día, ofreciendo control de concurrencia multi-versión,
soportando casi toda la sintaxis SQL (incluyendo subconsultas, transacciones, y tipos y
funciones definidas por el usuario), contando también con un amplio conjunto de enlaces con
lenguajes de programación (incluyendo C, C++, Java, perl, tcl y python).

Postgres95

    En 1994, Andrew Yu y Jolly Chen añadieron un intérprete de lenguaje SQL a Postgres.
     El proyecto resultante se llamo Postgres95 (1994-1995) que fue publicado en la Web
     para que encontrara su propio hueco en el mundo como un descendiente de dominio
     público y código abierto del código original Postgres de Berkeley.
    El código de Postgres95 fue adaptado a ANSI C y su tamaño reducido en un 25%.
     Muchos cambios internos mejoraron el rendimiento y la facilidad de mantenimiento.
     Postgres95 v1.0.x se ejecutaba en torno a un 30-50% más rápido en el Wisconsin
     Benchmark comparado con Postgres v4.2. Además de corrección de errores, éstas
     fueron las principales mejoras:
    El lenguaje de consultas Postquel fue reemplazado con SQL (implementado en el
     servidor). Las subconsultas no fueron soportadas hasta PostgreSQL, pero podían ser
     emuladas en Postgres95 con funciones SQL definidas por el usuario. Las funciones
     agregadas fueron reimplementadas. También se añadió una implementación de la
     cláusula GROUP BY. La interfaz libpq permaneció disponible para programas escritos
     en C.
    Además del programa de monitorización, se incluyó un nuevo programa (psql) para
     realizar consultas SQL interactivas usando la librería GNU readline.
    Una nueva librería de interfaz, libpgtcl, soportaba clientes basados en Tcl. Un shell de
     ejemplo, pgtclsh, aportaba nuevas órdenes Tcl para interactuar con el motor
     Postgres95 desde programas tcl.
    Se eliminó también el sistema de reglas a nivel de instancia, si bien las reglas siguieron
     disponibles como reglas de reescritura.
    Se distribuyó con el código fuente un breve tutorial introduciendo las características
     comunes de SQL y de Postgres95.
    Se utilizó GNU make (en vez de BSD make) para la compilación. Postgres95 también
     podía ser compilado con un gcc sin parches (al haberse corregido el problema de
     alineación de variables de longitud doble).




                                              3
                     ESCUELA POLITÉCNICA NACIONAL
                                       BASE DE DATOS



PostgreSQL

     En 1996, se hizo evidente que el nombre "Postgres95" no resistiría el paso del tiempo.
      Se eligió un nuevo nombre, PostgreSQL, para reflejar la relación entre el Postgres
      original y las versiones más recientes con capacidades SQL. Al mismo tiempo, hicieron
      que los números de versión partieran de la 6.0, volviendo a la secuencia seguida
      originalmente por el proyecto Postgres.
     Durante el desarrollo de Postgres95 se hizo hincapié en identificar y entender los
      problemas en el código del motor de datos. Con PostgreSQL, el énfasis ha pasado a
      aumentar características y capacidades, aunque el trabajo continúa en todas las áreas.
     Los bloqueos de tabla han sido sustituidos por el control de concurrencia multi-
      versión, el cual permite a los accesos de sólo lectura continuar leyendo datos
      consistentes durante la actualización de registros, y permite copias de seguridad en
      caliente desde pg_dump mientras la base de datos permanece disponible para
      consultas.
     Se han implementado importantes características del motor de datos, incluyendo
      subconsultas, valores por defecto, restricciones a valores en los campos (constraints) y
      disparadores (triggers).
     Se han añadido funcionalidades en línea con el estándar SQL92, incluyendo claves
      primarias, identificadores entrecomillados, forzado de tipos cadena literales,
      conversión de tipos y entrada de enteros binarios y hexadecimales.
     Los tipos internos han sido mejorados, incluyendo nuevos tipos de fecha/hora de
      rango amplio y soporte para tipos geométricos adicionales.
     La velocidad del código del motor de datos ha sido incrementada aproximadamente en
      un 20-40%, y su tiempo de arranque ha bajado el 80% desde que la versión 6.0 fue
      lanzada.

¿QUÉ ES POSTGRESQL?
PostGreSQL es un sistema de gestión de bases de datos de tipo objeto-relacional (ORDBMS)
basado en el proyecto POSTGRES, de la universidad de Berkeley. El director de este proyecto
es el profesor Michael Stonebraker, y fue patrocinado por Defense Advanced Research Projects
Agency (DARPA), el Army Research Office (ARO), el National Science Foundation (NSF), y
ESL, Inc.
PostGreSQL es una derivación libre (OpenSource) de este proyecto, y utiliza el lenguaje
SQL92/SQL99, así como otras características que comentaremos más adelante.

Fue el pionero en muchos de los conceptos existentes en el sistema objeto-relacional actual,
incluido, más tarde en otros sistemas de gestión comerciales. PostGreSQL es un sistema objeto-
relacional, ya que incluye características de la orientación a objetos, como puede ser la herencia,
tipos de datos, funciones, restricciones, disparadores, reglas e integridad transaccional. A pesar
de esto, PostGreSQL no es un sistema de gestión de bases de datos puramente orientado a
objetos.

CARACTERÍSTICAS DE POSTGRESQL

A continuación se enumeran las principales características de este gestor de bases de
datos:




                                                 4
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS



   1. Implementación del estándar SQL92/SQL99.
   2. Soporta distintos tipos de datos: además del soporte para los tipos base, también
      soporta datos de tipo fecha, monetarios, elementos gráficos, datos sobre redes
      (MAC, IP ...), cadenas de bits, etc. También permite la creación de tipos propios.
   3. Incorpora una estructura de datos array.
   4. Incorpora funciones de diversa índole: manejo de fechas, geométricas,
      orientadas a operaciones con redes, etc.
   5. Permite la declaración de funciones propias, así como la definición de
      disparadores.
   6. Soporta el uso de índices, reglas y vistas.
   7. Incluye herencia entre tablas (aunque no entre objetos, ya que no existen), por lo
      que a este gestor de bases de datos se le incluye entre los gestores objeto-
      relacionales.
   8. Permite la gestión de diferentes usuarios, como también los permisos asignados
      a cada uno de ellos.


OPINIÓN PERSONAL

PostGreSQL es un magnífico gestor de bases de datos, capaz de competir con muchos gestores
comerciales, aunque carezca de alguna característica casi imprescindible. Ésta es, bajo mi
punto de vista, un conjunto de herramientas que permitan una fácil gestión de los usuarios y
de las bases de datos que contenga el sistema. Por otro lado, la velocidad de respuesta que
ofrece este gestor con bases de datos relativamente pequeñas puede parecer un poco
deficiente, aunque esta misma velocidad la mantiene al gestionar bases de datos realmente
grandes, cosa que resulta loable. Tiene prácticamente todo lo que tienen los gestores
comerciales, haciendo de él una muy buena alternativa GPL.

ACERCA DE ESTA VERSIÓN POSTGRESQL

PostgreSQL está disponible sin costo. Este manual describe la version 9.02 de PostgreSQL.
Se usará Postgres para referirse a la versión distribuida como PostgreSQL. Compruebe la Guía
del Administrador para ver la lista de plataformas soportadas. En general, Postgres puede
portarse a cualquier sistema compatible Unix/Posix con soporte completo a la librería libc.

Este 14avo lanzamiento público agrega características, madurez y rendimiento
requeridos por los usuarios corporativos, entregando simplicidad de manejo comparable
a los sistemas líderes de manejo de bases de datos. Se espera que la versión 8.2
conduzca a aún más migraciones hacia PostgreSQL.

En esta nueva versión se modifica casi cada una de las órdenes y facilidades del sistema
dice Bruce Momjiam, miembro del grupo central de desarrollo y arquitecto de bases de
datos de EnterpriseDB. «Agrega sintaxis extendida y compatible, e interfaces que han
sido solicitadas por nuestra comunidad, haciendo más fácil para nuevos DBAs el uso de
todas las características avanzadas de PostgreSQL.»




                                             5
                    ESCUELA POLITÉCNICA NACIONAL
                                   BASE DE DATOS



«Unisys está complacido en observar mejoras significativas en la escalabilidad y
rendimiento de PostgreSQL en sistemas multiprocesador de gran escala», dice Ali
Shadman, vicepresidente y gerente general de Soluciones de Código Abierto, Sistemas
y Tecnología, Unisys. «La nueva versión 8.2 continúa la evolución de PostgreSQL en
un sistema gestor de bases de datos capaz de adopción empresarial».

Las nuevas herramientas y características que facilitan la gestión de bases de datos y el
desarrollo incluyen:

Mejoras de Rendimiento: la versión 8.2 mejora el rendimiento en alrededor de un 20%
en pruebas de sistemas OLTP (procesamiento en línea de transacciones) de gran escala.
Los usuarios de sistemas data warehouse pueden notar mejoras aún mayores. Los
cambios incluyen ordenamientos en memoria y en disco más rápido, mejor
escalabilidad en sistemas multi-procesador, mejor optimización de consultas sobre datos
particionados, cargas masivas más rápidas y outer joins considerablemente acelerados.

Bases de Datos Warm Standby: una extensión a nuestra característica de «recuperación
a un punto en el tiempo», introducida en la versión 8.0, permite a los administradores
crear fácilmente una copia para recuperación inmediata (failover) de su cluster de bases
de datos.

Construcción en línea de índices: la construcción de índices puede ocurrir mientras las
aplicaciones escriben a las tablas de la base de datos, permitiendo el afinamiento de
rendimiento sin afectar la disponibilidad.

Características de SQL 2003: PostgreSQL, bien conocido por su conformidad a los
estándares, ha agregado sintaxis para varias características introducidas en la
especificación ANSI SQL:2003, incluyendo funciones de agregación estadística,
sentencias VALUE con múltiples registros, UPDATE RETURNING y funciones de
agregación de múltiples columnas.

Las características avanzadas que se ofrecen con PostgreSQL 8.2 antes que ningún otro
sistema de gestor de bases de datos incluyen:

Índices Invertidos Generalizados: una forma más escalable y programable de indexar
datos semi-estructurados y texto.

DTrace: PostgreSQL ha sido instrumentado para permitir la trazabilidad a través de un
marco genérico de monitoreo, usando DTrace en Solaris y otras herramientas avanzadas
de traza.

Con instancias de data warehouse con tablas de miles de millones de registros y varios
terabytes de datos, las capacidades de construcción en línea de índices y hot-standby son
cruciales», dice Theo Schlossnagle, Director en OmniTI Computer Consulting.
«Estamos entusiasmados con el hecho de que PostgreSQL sea un ciudadano de primera
clase de DTrace. Esto permite que nuestros permanentes esfuerzos de análisis de




                                            6
                    ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



sistemas incorpore datos específicos de las bases de datos, llevando a resultados mucho
más certeros».



VENTAJAS Y DESVENTAJAS

     Limitaciones al escribir funciones y procedimientos en comparación con
      Oracle's PL/SQL o Sybase's T-SQL.
     Las tablas espaciosas, tablas particionadas, y con bloqueo altamente complicado
      siguen siendo ofrecidas por los vendedores propietarios de bases de datos.
     Carencia de herramientas de desarrollo propia
     El costo es la principal ventajas de PostgreSQL
     La habilidad para poder mirar el código fuente y entender que está sucediendo

                                    Benchmarking




CÓDIGO ABIERTO VS. PRODUCTO COMERCIAL

PostgreSQL es un proyecto de código abierto, esto quiere decir que tu puedes obtener el
código fuente, usar el programa, y modificarlo libremente sin las limitaciones de las
propiedades de autor. Código Abierto también significa que tu estas en libertad de modificar
PostgreSQL y ajustarlo a tus necesidades particulares.




                                              7
                     ESCUELA POLITÉCNICA NACIONAL
                                      BASE DE DATOS



La versión Red Hat de PostgreSQL es llamada Red Hat Database y es un producto nuevo para el
mercado de las bases de datos y actualmente soporta solo Linux, específicamente Red Hat
Linux 7.1.

La versión de Código Abierto solo incluye el sistema gestor de base de datos y las interfaces
asociadas a los programas. La versión de Red Hat incluye un instalador grafico y soporte
limitado a la instalación.

El principal factor para decidir si usar la versión de código abierto o un paquete comercial de
PostgreSQL deberían ser las necesidades del negocio.

En relación al costo Red Hat Database está en un precio de $2,295.00 y viene con
documentación impresa. En el caso de versión de código abierto es Libre y toda la
documentación se puede encontrar en la Web.

Lo mas importante para recordar cuando se decida que versión de PostgreSQL usar es esta (a
partir de una perspectiva de uso general y disponibilidad de características): No hay una
diferencia practica entre el código abierto y la versión comercial


                   INSTALACION DE POSTGRESQL
Paso 1:
Acceder a página de postgreSQL (http://www.postgresql.org/) para descargarnos el instalador
de la base de datos postgreSQL.




                                                8
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS



PASO 2:
Entramos a la siguiente página (DONLOADS) donde se elige la descarga de PostgreSQL
dependiendo del Sistema Operativo




PASO 3:
Ingresamos a la pagina (Windows pacckages) donde nos da las características del paqute de
postgreSQL que nos estamos descargando




                                             9
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS



PASO 4:
Nos aparece la ventana de descarga directa de PostgreSQL perteneciente a la compañía dueña
del producto. Tenemos que darle click en el signo del sistema operativo para el cual vamos a
descargar la versión de postgreSQL




ADVERTENCIA:

 Hay que tomar en cuenta que si estamos trabajando en internet Explorer antes de empezar
 con la descarga nos aparece una ventana en la cual se restringe la descarga por motivos del
 navegador con el cual estamos trabajando, únicamente se tiene que dirigir a la parte
 inferior de la ventana de menú y habilitar la descarga




                                             10
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



PASO 5:
                       DESCARGA DEL INSTALADOR

5.1 Nos aparece la ventana para poder autorizar la descarga




5.2 Esta ventana nos da la opción de elegir la ubicación de nuestro instalador




                                              11
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



PASO 6:

La de descarga se ha iniciado




PASO 7:

      INSTALANDO POSTGRESQL EN NUESTRO COMPUTADOR

Una vez descargado el instalador, nos aparece el icono en nuestro escritorio para cargar el
programa en nuestro computador




Luego ejecutamos el instalador.




                                              12
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



PASO 8:
El programa de instalación empieza a correr




PASO 9 :
Se despliega la ventana de instalación y damos click en siguiente




                                              13
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS




PASO 10:

Esta pantalla nos sirve para designar el directorio de instalación, después tenemos que
presionar siguiente




PASO 11:
Esta es la ventana que utilizamos para asignar una dirección o directorio donde se
almacenaran los datos que tenga nuestro programa




                                              14
                    ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



PASO 12:
En esta ventana nos pide que proporcionemos una contraseña para el súper–usuario
(postgres).




PASO 13:
En esta ventana el instalador nos pide que seleccionemos el número de puerto en el que el
servidor debería escuchar. El numero que aparece en la pantalla la maquina se encarga de
seleccionarlo, si existiera la posibilidad de configurar otro puerto se lo hace, caso contrario
solo hay que poner siguiente




                                              15
                      ESCUELA POLITÉCNICA NACIONAL
                                       BASE DE DATOS



PASO 14:
Se selecciona la configuración regional a ser usado por el nuevo clúster de datos. Se selecciona
Spanish, Ecuador y damos click en siguiente




PASO 15:
¡Listo! el programa está listo para la instalación




                                                 16
                     ESCUELA POLITÉCNICA NACIONAL
                                      BASE DE DATOS



PASO 16:
Ha empezado la instalación del programa PostgreSQL v 9.02




PASO 17:
En esta versión de postgre antes de concluir con la instalación, nos da la opción de instalar a
demás Stack Builder, que es un software adicional para complementos. Por fines de
complementar el manual vamos a proceder con la instalación habilitando la casilla check box
con un visto y dando click en terminar.




                                               17
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



PASO 18:
                    INSTALACION DE STACK BUILDER


Este asistente ayudará a la instalación de software adicional para complementar la instalación
de PostgreSQL. Debemos estar consientes que para que esta instalación se haga
correctamente, nuestro computador debe estar conectado a internet




PASO 19:
En esta ventana tenemos que seleccionar al menos 1 item para continuar con la instalación.
Los ítems deben ser seleccionados en base a las necesidades y utilidades que se le va a dar a
nuestro gestor de bases de datos




                                              18
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS



PASO 20:
Esta ventana indica los complementos anteriormente seleccionados que ayudara a postgreSQL
con el trabajo que se le designe




PASO 21:
Se empieza a descargar los complementos para la instalación




                                            19
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



PASO 22:
Esta ventana indica que la instalación surgirá efecto siempre y cuando se reinicie la maquina,
esto es opcional




PASO 23:
Esta ventana comprueba que nosotros nos saltamos del paso de reiniciar la maquina, y que en
la próxima reiniciada o encendida de la maquina, la instalación de los complemento surtirán
efecto




                                              20
ESCUELA POLITÉCNICA NACIONAL
        BASE DE DATOS




INTRODUCCION A DBMS
       POSTGRESQL
          V 9.02




             21
                    ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



IDEAS BÁSICAS ACERCA DEL FUNCIONAMIENTO
En la jerga de bases de datos, PosgreSQL usa el modelo cliente/servidor.
Una sesión en PostgreSQL consiste en ejecución de los siguientes procesos.

     El servidor, que maneja archivos de bases de datos, acepta conexiones a las
      aplicaciones cliente, y realiza acciones en la base de datos. El programa servidor de
      bases de datos se conoce como postmaster
     La aplicación cliente, que necesita realizar operaciones en la base de datos.

Las aplicaciones cliente pueden ser de la más diversa naturaleza: pueden ser aplicaciones de
texto en una consola, aplicaciones graficas, un servidor web que accede a la base de datos
para mostrar una página, o herramientas especializadas de mantenimiento de bases de datos.
Como es habitual en las aplicaciones cliente/servidor, el cliente y el servidor pueden estar en
diferentes maquinas. En este caso, estos se comunican sobre una conexión de red TCP/IP.

El servidor PostgreSQL puede manejar múltiples conexiones concurrentes de los clientes. Para
esto inicia un nuevo proceso (”fork”) para cada conexión llamado backend. Con esto, el cliente
y el nuevo proceso del servidor se comunican sin la intervención del proceso original del
postmaster. Así, el postmaster está siempre corriendo, esperando por conexiones de parte de
los clientes. Todo esto por supuesto es invisible para el usuario y se menciona acá solo como
un comentario.

¿Qué es una base de datos relacional?
Una base de datos relacional desde el punto de vista del usuario podemos decir que es como
una colección de tablas interrelacionadas que permiten almacenar información para que esta
pueda ser utilizada posteriormente, y se basa en el modelo de datos relacional para la
manipulación de las tablas, el que a su vez se basa en elementos de la teoría de conjuntos para
establecer las relaciones.

¿Qué es una consulta?
Una consulta es una petición de información que se hace a la base de datos, la que se
implementa de acuerdo a ciertas reglas e instrucciones que provee el lenguaje SQL y que
permite ver y manipular datos que se encuentran en el sistema.


SEGURIDAD EN POSTGRESQL




La seguridad en PostgreSQL se materializa en tres aspectos:




                                              22
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



    1. Seguridad en la manipulación de los ficheros de PostgreSQL.
    2. Seguridad en los accesos de los clientes.
    3. Definición de los privilegios para acceder a los objetos de la base de datos a los
       usuarios.

Seguridad en la manipulación de los ficheros:

Todos los ficheros almacenados en la base de datos están protegidos contra escritura por
cualquier cuenta que no sea la del súper usuario de Postgres. Y este es el único que pueda:
Leer, escribir y ejecutar sin restricción. Por lo general la información más crítica está en
$PGDATA.

Seguridad en el acceso de los clientes:

El “Postmaster” que es el responsable de aceptar las comunicaciones con el cliente y
autentificar y dar acceso. El “Postgre” se encarga de la administración de los querys y
comandos enviados por el cliente. Las conexiones de los clientes se pueden restringir por
dirección IP y/o por nombre de usuario mediante el fichero pg_hba.conf situado en PG_DATA.
PosgreSQL por lo general utiliza el puerto 5432. Este puerto no debería ser accesible desde
lugares no confiables.

La configuración de este nivel de seguridad se realiza en los ficheros pg_hba.conf y
pg_ident.conf. A cada usuario de Postgres se le asigna un nombre de usuario y
(opcionalmente) una contraseña. Por defecto, los usuarios no tienen permiso de escritura a
bases de datos que no hayan creado.

Se trata de editar una serie de reglas que se irán procesando de arriba abajo, cuando se
encuentre una regla que cumpla la conexión, se ejecutará lo que ponga en el método.
Es el proceso mediante el cual el servidor de la base de datos y el postmaster se aseguran de
que el usuario que está solicitando acceso a la base de datos es en realidad quien dice ser.

Todos los usuarios que quieren utilizar Postgres se comprueban en la tabla pg_user para
asegurarse que están autorizados a hacerlo.

Postgres proporciona mecanismos para permitir a los usuarios limitar el acceso que otros
usuarios tendrán a sus datos.

Súper usuarios de la Base de Datos:(aquellos que tienen el campo pg_user.usesuper activado)
ignoran todos los controles de acceso con dos excepciones: las actualizaciones del catálogo del
sistema no están permitidas si el usuario no tiene el campo pg_user.usecatupd activado, y
nunca se permite la destrucción del catálogo del sistema (o la modificación de sus estructuras).




                                                23
                        ESCUELA POLITÉCNICA NACIONAL
                                      BASE DE DATOS




Privilegios de acceso

El uso de los privilegios de acceso para limitar la lectura, escritura y la puesta de reglas a las
clases se trata en grant/revoke(l).

Borrado de clases y modificación de estructuras

Los comandos que borran o modifican la estructura de una clase, como alter, drop table, y
drop index, solo funcionan con el propietario de la clase. Como hemos dicho antes, estas
operaciones no están permitidas nunca en los catálogos del sistema.

Privilegios
Su administración se basa en usuarios y privilegios. Cuando se crea un objeto en PostgreSQL,
se le asigna un dueño. Por defecto, será el mismo usuario que lo ha creado. Para cambiar el
dueño de una tabla, índice, secuencia, etc., debemos usar el comando alter table. El dueño del
objeto es el único que puede hacer cambios sobre él, si queremos cambiar este
comportamiento,           deberemos        asignar      privilegios    a       otros     usuarios.
Los privilegios se asignan y eliminan mediante las sentencias grant y revoke.
PostgreSQL define los siguientes tipos de operaciones sobre las que podemos dar privilegios:
select, insert, update, delete, rule, references, trigger, create, temporary, execute, usage, y all
privileges.
Además PostgreSQL utiliza el llamado Multi-Version Concurrency Control (MVCC) para manejar
los accesos a la base de datos, lo que permite a diferentes usuarios realizar consultas
simultáneamente sin necesidad de esperar a que uno de los procesos termine para empezar el
siguiente, lo cual agiliza enormemente su ejecución sin dejar de un lado la seguridad.

Permisos
Las acciones que podemos realizar en cada momento vienen condicionadas por los permisos
del usuario con el que nos conectemos a la base de datos. Todos los objetos (tablas, vistas y
secuencias) tienen un propietario, que es la persona que lo creo. El propietario o un súper




                                                24
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



usuario, puede establecer permisos en el objeto. Los permisos se componen de un nombre de
usuario o grupo y un conjunto de derechos. Estos derechos descritos en la tabla de abajo.



      PRIVILEGIO      NOMBRE CORTO                           DESCRIPCION
    SELECT                 r                Puede leer los datos del objeto
    INSERT                 a                Puede inserta datos en el objeto
    UPDATE                 w                Puede cambiar los datos en el objeto
    DELETE                 d                Puede eliminar los datos del objeto
    RULE                   R                Puede crear una regla sobre las tablas
    REFERENCES             x                Se puede crear una clave externa a una tabla
    TRIGGER                t                Se puede crear un desencadenador en la tabla
    TEMPORARY              T                Se puede crear una tabla temporal
    EXECUTE                X                Se puede crear ejecutar la función
    USAGE                  U                Puede usar la lengua del procedimiento
    ALL                   All               Privilegios adecuados para las tablas
Registro de auditoría

El registro de auditoría es un archivo o base de datos especial en el que el sistema lleva
automáticamente la cuenta de todas las operaciones realizadas por los usuarios sobre lo datos.
Este mantiene el control de acceso a la base de datos, con el objeto de saber qué o quién
realizó una determinada modificación y en qué momento.


COPIA DE SEGURIDAD
Para este caso vamos a utilizar la base de datos llamado “paraCopiaSeguridad”




Esta tiene dos tablas llamadas continentes y países




                                              25
                     ESCUELA POLITÉCNICA NACIONAL
                                      BASE DE DATOS




Aunque existen varios métodos para hacer copias de seguridad (física, lógica, física online, …)
en este punto nos centraremos en las copias de seguridad lógicas de una instancia de base de
datos con pgAdminIII.

Para realizar la copia de seguridad, nos dirigimos a la base de datos y le damos clic derecho
sobre “Resguardo”




Luego escogemos el destino, en este caso lo vamos a guardar en el escritorio, en una carpeta
llamada respaldoSeguridad. Le damos clic en “OK” y luego en”Hecho”




                                               26
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS




Ahora digamos que se perdió la carpeta.




Por lo que debemos crear otra base de datos pero con el mismo nombre




Y nos vamos sobre la base de datos nueva llamada “paraCopiaSeguridad”, damos clic derecho
sobre “Restaurar”




                                            27
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS




Nos dirigimos a donde está el archivo de respaldo, clic en “OK”




Como nos podemos dar cuenta la base de datos ha sido restaurada con sus datos




                                              28
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



CARACTERÍSTICAS QUE HACEN DE POSTGRESQL
BASES DE DATOS MÁS SEGURAS DEL MERCADO

     Permite un manejo de actualización e inserción de registros avanzados.
     WAL (Log de transacciones para recuperación ante fallos)
      Cuando el motor reinicia luego de un fallo, se utiliza la WAL para la recuperación.
      La WAL permite una inserción más rápida al pre-asignar espacios de disco para la
      escritura.
     Posibilidad de Hot Backups.
      Esto significa que se pueden realizar backups sin detener el funcionamiento del motor.
     Posibilidad de backups incrementales.
     Recuperación a un punto en el tiempo (PITR).
      Esta técnica permite recuperar la base a un punto en el tiempo. Requiere dos
      instancias de Postgres.
     Replicación con herramientas 3rd Party.
     Seguridad de usuarios.
     Transaccionalidad.


Para conectarse a la base de datos, se debe dar doble clic donde dice servidor,




                                              29
                   ESCUELA POLITÉCNICA NACIONAL
                                   BASE DE DATOS



      PRESTACIONES
   PostgreSQL destaca por su amplísima lista de prestaciones que lo hacen capaz de
    competir con cualquier SGBD comercial:
   Está desarrollado en C, con herramientas como Yacc y Lex.
   La API de acceso al SGBD se encuentra disponible en C, C++, Java, Perl, PHP, Python y
    TCL, entre otros.
   Cuenta con un rico conjunto de tipos de datos, permitiendo además su extensión
    mediante tipos y operadores definidos y programados por el usuario.
   Su administración se basa en usuarios y privilegios.
  
    soportar completamente ODBC.
   Los mensajes de error pueden estar en español y hacer ordenaciones correctas con
    palabras acentuadas o con la letra ‘ñ’.
   Es altamente confiable en cuanto a estabilidad se refiere.
   Puede extenderse con librerías externas para soportar encriptación, búsquedas por
    similitud fonética (soundex), etc.
   Control de concurrencia multiversión, lo que mejora sensiblemente las operaciones de
    bloqueo y transacciones en sistemas multiusuario.
   Soporte para vistas, claves foráneas, integridad referencial, disparadores,
    procedimientos almacenados, subconsultas y casi todos los tipos y operadores
    soportados en SQL92 y SQL99.
   Implementación de algunas extensiones de orientación a objetos. En PostgreSQL es
    posible definir un nuevo tipo de tabla a partir de otra previamente definida.

LIMITACIONES

   Máximo de base de datos: ILIMITADO
   Máximo de tamaño de tabla: 32TB
   Máximo de tamaño de registro: 1.6TB
   Máximo de tamaño de campo: 1GB
   Máximo de registros por tabla: ILIMITADO
   Máximo de campos por tabla: 250 a 1600 (depende de los tipos usados)
   Máximo de índices por tabla: ILIMITADO
   Número de lenguajes en los que se puede programar funciones: aproximadamente 10
    (pl/pgsql, pl/java, pl/perl, pl/python, tcl, pl/php, C, C++, Ruby, etc.)
   Métodos de almacenamiento de índices : 4 (B-tree, Rtree, Hash y GisT)

VENTAJAS
     Estable
     Alto Rendimiento
     Flexibilidad, ya que funcionar la mayoría de los sistemas Unix
     Se puede extender su funcionalidad
     Gran Compatibilidad




                                            30
                  ESCUELA POLITÉCNICA NACIONAL
                                   BASE DE DATOS



      Permite crear o migrar aplicaciones desde Access, Visual Basic, Visual Fox Pro, Visual
      C/C++, Delphi para usar PostgreSQL como servidor de DB’s.
     Varias Interfaces de Programación:
      ODBC, JDBC, C/C++, SQL Embebido, Tcl/Tk, Perl, Python, PHP.
     PostgreSQL puede ser integrada al ambiente Windows permitiendo de esta manera a
      los desarrolladores, generar nuevas aplicaciones o mantener las ya existentes.
     Es gratuito
     PostgreSQL tiene muchas de las funcionalidades de las bases de datos comerciales.
     Funcionamiento: PostgreSQL se ejecuta en dos modos. El modo normal fsync, que
      guarda en el disco toda transacción completada, es mas lento que la mayoría de las
      bases de datos comerciales, en parte porque son pocas las que hacen esto. El modo
      no-fsync, que es menos fiable que el anterior, es mas rápido que las bases de datos
      comerciales, aunque en este modo un crash del sistema puede significar la perdida de
      alguna transacción.

COMPARANDO POSTGRESQL CON OTRAS BASES DE DATOS
   PostgreSQL frente a Interbase:

         o   Es gratuito (Interbase, en principio, es comercial) y esta mejor documentado.
         o   El codigo no tiene puertas traseras de entrada.
         o   Soporta un número ilimitado de filas, indices y 1Gb por campo.
         o   Tiene un campo booleano.
         o   Soporta herencia.
         o   Permite indexar textos para realizar busquedas sofisticadas a traves de
             OpenFTS.
         o   Resiste caidas del sistema y cortes de luz.
         o   Soporta funciones en varios lenguajes e indices sobre funciones.
         o   Tiene secuencias.
         o   Cumple los estandares mejor que Interbase.
         o   Tiene soporte para conexiones encriptadas SSL.

   PostgreSQL frente a MySQL:

         o   Es mas rapido y mas eficiente que MySQL.
         o   Tiene views.
         o   Soporta tamaños de filas y bases de datos ilimitados.
         o   Soporta herencia.
         o   Tiene soporte para unicode.
         o   Resiste caidas del sistema y cortes de luz.
         o   Tiene triggers y rules.
         o   Soporta subqueries, indices sobre funciones.
         o   Tiene soporte para conexiones encriptadas SSL.

   PostgreSQL frente a Oracle:

         o   Es gratuito y de codigo abierto.
         o   No tiene un esquema de licencia complidado como Oracle.



                                           31
                    ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



            o   Soporta herencia.
            o   Permite indexar textos para realizar busquedas sofisticadas a traves de
                OpenFTS.
            o   Puede ser traducido.
            o   Tiene soporte para conexiones encriptadas SSL.

SENTENCIAS DDL
El Lenguaje de Definición de Datos (Data Definition Language – DDL), sirve para modificar
la estructura de los objetos en una base de datos.
Estas sentencias básicamente son: CREATE, ALTER, DROP y TRUNCATE.
A continuación iniciaremos con la creación de una base de datos.
Creación de base de datos (DATABASE):
Sintaxis:
 CREATE DATABASE name * WITH LOCATION = ‘dbpath’ +
Ejemplo:
 CREATE DATABASE bddPedidos TEMPLATE template0;
Con la línea CREATE DATABASE bddPedidos TEMPLATE template0; , se crea una tabla a
partir de una plantilla que trae postgres llamada template0, el cual nos crea la base de
datos totalmente vacía.
Creación de esquemas (SCHEMAS):
Los esquemas son importantes para agrupar objetos según especificaciones, y al
mantener organizado la base de datos permite un mejor desempeño al momento de la
administración.
Si no se le asigna un esquema al objeto, postgres lo asignará implícitamente al esquema
“public“. Para definir que un objeto pertenezca a un esquema se pone el nombre del
esquema seguido por un ponto y el nombre del objeto.
 CREATE SCHEMA Persona;
 CREATE SCHEMA Admision;
Creación de secuencias (SEQUENCE):
CREATE SEQUENCE Persona.seq_idcontacto START 1; --empieza desde 1
CREATE SEQUENCE Admision.seq_idmodalidad START 1; --empieza desde 1

Creación de tablas (TABLES):
Sintaxis:
CREATE [ TEMPORARY | TEMP ] TABLE table ( column type [ NULL | NOT NULL ]
[ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ]
] [, ... ] [, PRIMARY KEY ( column [, ...] ) ] [, CHECK ( condition ) ] [, table_
constraint_clause ] ) [ INHERITS ( inherited_table [, ...] ) ]
Ejemplo:
  create table prueba (FechaCreacion date not null default now)
Con “FechaCreacion date not null default now(),” asignaremos la fecha actual por defecto
al campo FechaCreacion por medio de la función now().
Creación de restricciones (CHECK):
Los valores dentro de CHECK , por ejemplo “CHECK (Genero in (‘1’,’2’, ‘3’, ‘4’,))”, son los
únicos que serán permitidos insertar en el campo establecido, en este caso se le asigna esa
restricción al campo Genero.
Ejemplo:



                                              32
                     ESCUELA POLITÉCNICA NACIONAL
                                     BASE DE DATOS



  ALTER TABLE Empleados
  ADD CONSTRAINT ck_Reporta
  CHECK (reporta_a in (‘1’,’2’, ‘3’, ‘4’,))
  ;
Creación de restricciones (UNIQUE):
Las restricciones UNIQUE, por ejemplo “UNIQUE(nombreprov)” , aseguran que en los datos
en la misma columna, en este caso en el campo nombreprov, no sean repetidas, ya que no
puede haber registradas 2 nombres de proveedores con el mismo nombre.
Sintaxis:
CREATE [ UNIQUE ] INDEX nombre_indice ON tabla [ USING nombre_acceso ](
columna [nombre_operador][, ...] )
Ejemplo:
  ALTER TABLE Proveedores
  ADD CONSTRAINT uq_nombreprov
  UNIQUE(nombreprov)
Creación de claves primarias (PRIMARY KEYS):
Las claves primarias, aparte de prevenir la duplicidad de datos, y servir como nexo para
relacionarse con otras tablas, tiene otro fin también importante, que es agilizar el proceso
de búsqueda, no por el hecho de ser primary key, sino que al momento de la creación de
una clave primaria se crea implícitamente un indice (index).
Sintaxis:
[ CONSTRAINT name ] { [ NULL | NOT NULL ] | PRIMARY KEY } [, ...]
Ejemplo:
  ALTER TABLE Proveedores
  ADD CONSTRAINT pk_Proveedorid
  PRIMARY KEY(Proveedorid)
  ;
Creación de claves foráneas (FOREIGN KEYS):
Las claves foráneas son campos que servirán para la relación entre 2 tablas, la clave
primaria de otra tabla se relacionará con la clave foránea de ésta.
Ejemplo:
  ALTER TABLE Productos
  ADD CONSTRAINT fk_ Proveedorid
  FOREIGN KEY(Proveedorid)
  references Proveedores (Proveedorid)




                                              33
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS



SENTENCIAS DML
El lenguaje de Manipulación de Datos (Data Manipulation Language – DML) sirve para llevar
a cabo las transacciones en las base de datos, entiéndase por transacciones los procesos
de inserción, actualización, eliminación, selección.
Para mayor explicación se expone los siguientes ejemplos usando la base de datos
PEDIDOS:
Listar datos de Clientes
Sintaxis:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] expression [ AS name ] [,
...] [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] [ FROM table [ alias ] [, ...]
] [ WHERE condition ] [ GROUP BY column [, ...] ] [ HAVING condition [, ...] ] [
{ UNION [ ALL ] | INTERSECT | EXCEPT } select ] [ ORDER BY column [ ASC | DESC |
USING operator ] [, ...] ] [ FOR UPDATE [ OF class_name [, ...] ] ] LIMIT { count |
ALL } [ { OFFSET | , } start ]
Ejemplo:
  Select * from clientes ; --Se lista todos los elementos de una tabla
  Select Clienteid, nombrecia,celular from clientes --Se lista ciertos campos de una
tabla
Insertar datos en Clientes
Sintaxis:
  INSERT INTO table [ ( column [, ...] ) ] { VALUES ( expression [, ...] ) | SELECT
query }
Ejemplo:
  INSERT INTO CLIENTES
  VALUES (10,’183445667’,’YARBANTRELLA’,’PABLO POLIT’,’AV.REPUBLICA’,NULL,NU
LL,NULL,NULL);
  SELECT * FROM clientes; --Comprobar Inserción
Actualizar datos en Clientes
Sintaxis:
UPDATE tabla SET columna = expresion [, ...] [ FROM lista ] [ WHERE condicion ]
Ejemplo:
  UPDATE clientes set direccioncli =’AV. PATRIA ‘
  WHERE clienteid =1;
  SELECT * FROM clientes; --Comprobar Actualización
Eliminar datos en Clientes
Sintaxis:
  DELETE FROM table [ WHERE condition ]
Ejemplo:
  DELETE FROM Clientes
  WHERE clienteid =’1’;
  SELECT * FROM clientes; --Comprobar Eliminación




                                            34
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS




                        ARRANQUE DEL DBMS
1 Nos dirigimos al inicio y buscamos en los programas PostgreSQL 9.0




   1. 2 Para conectar el servidor, damos clic derecho sobre PostgreSQL Dtabase Server, y
      damos clic en conectar.




                                             35
                 ESCUELA POLITÉCNICA NACIONAL
                                  BASE DE DATOS



2. A continuación, digitamos la contraseña que se digito al inicio de la instalacion de la
   base de datos PostgreSQL.




3. Al dar clic en Ok, de la ventana anterior, el servidor ya está conectado y está listo para
   empezar a utilizar la base de datos PostgreSQL.




                                           36
                   ESCUELA POLITÉCNICA NACIONAL
                                  BASE DE DATOS




                 CREACION DE LA BDD PEDIDOS
   1. Para la creación de una nueva base de datos, se debe dar clic derecho en Databases y
      seleccionamo Nueva base de datos




-- Database: pedidos
-- DROP DATABASE pedidos;
CREATE DATABASE pedidos
 WITH OWNER = postgres
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    LC_COLLATE = 'Spanish, Ecuador'
    LC_CTYPE = 'Spanish, Ecuador'
    CONNECTION LIMIT = -1;
COMMENT ON DATABASE pedidos IS 'Creacio BDD pedidos';




                                           37
           ESCUELA POLITÉCNICA NACIONAL
                          BASE DE DATOS




                 CREACION DE TABLAS
1. Damos clic en pedidos, seleccionamos Esquemas/public, damos clic derecho en
   Tabla para crear una nueva tabla.




                                  38
                 ESCUELA POLITÉCNICA NACIONAL
                                 BASE DE DATOS



Para explicar cómo se crean las tablas vamos a basarnos en la construcción de las tablas




                                          39
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS




                                   SENTENCIAS
DROP TABLE
El comando DROP TABLE permite eliminar una tabla, incluyendo todas las tuplas
almacenadas en ella:
Sintaxis:
DROP TABLE table_name;
Ejemplo:
DROP TABLE CATEGORIAS;
INSERT INTO
Para llenar una tabla previamente creada se hace el uso del comando INSERT INTO,
que permite llenar todos los campos que la tabla tiene.
Sintaxis:
INSERT INTO table_name (name_of_attr_1
[, name_of_attr_2 [,...]])
VALUES (val_attr_1
[, val_attr_2 [, ...]]);
Ejemplo:
insert into categorias (categoriaid, nombrecat) values (100, ‘CARNICOS’);
insert into categorias (categoriaid, nombrecat) values (200, ‘LACTEOS’);
insert into categorias (categoriaid, nombrecat) values (300, ‘LIMPIEZA’);
FUNCION UPDATE
Cambia los valores de columnas especificadas en todas las filas que satisfagan cierta
condición. En la clausula SET se deben mencionar solamente las columnas a ser modificadas;
el resto de columnas no mencionadas retendrán sus valores anteriores.
Por defecto, UPDATE puede actualizar una tabla específica y todas sus subtablas. Si se
desea actualizar únicamente cierta tabla, se debe utilizar la clausula ONLY.

Existen dos maneras de modificar una tabla utilizando la información contenida en otras
tablas en la base de datos: utilizando subconsultas o especificando las tablas adicionales
en la clausula FROM. La técnica más apropiada depende de las circunstancias específicas.
Sintaxis:
 UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
    [ FROM fromlist ]
    [ WHERE condition ]
Donde:
         table: Nombre de la tabla a actualizar.
         column: Nombre de la columna de la tabla. El nombre de la columna puede calificarse
         como nombre de un subcampo o subíndice del arreglo, si es necesario.

       expression: Expresión para asignar a la columna. La expresión podría utilizar los
       antiguos valores de esa y otras columnas en la tabla.
       DEFAULT: Asigna el valor predefinido a la columna (qué será NULL si ninguna expresión
       predefinida específica se ha asignado a él).
       fromlist : Una lista de expresiones de la tabla, permitiendo la aparición de columnas de
       otras tablas en la condición WHERE y las expresiones de actualización. Esto es similar
       a la lista de tablas en que pueden especificarse en la función FROM de una sentencia
       SELECT. La tabla designada no debe aparecer en el fromlist, a menos que se inserte un



                                             40
                     ESCUELA POLITÉCNICA NACIONAL
                                      BASE DE DATOS



          self-join (en tal caso debe aparecer con un alias).
          condition: Una expresión que devuelve un valor del tipo booleano. Solamente pueden
          actualizadas las filas para las cuales devuelve el valor true.
UPDATE count
count es el número de filas actualizadas. Si el valor de count es igual a cero, ninguna fila
cumple con la condición (no quiere decir que ha ocurrido un error).
Notas: Cuando una clausula FROM está presente, lo que ocurre esencialmente es que la
tabla designada se une a las tablas mencionadas en el fromlist, y cada fila resultante en
la unión representa una operación de actualización para la tablas designadas. Cuando se
utiliza FROM se debe asegurar que la unión produce a lo mucho una fila resultante para
cada fila a ser modificada. En otras palabras, una fila designada no debe unir a más de una
fila en la otra tabla(s). Si lo hace, entonces solamente se utilizará una fila para actualizarla
cuya selección es aleatoria.
Debido a esta indeterminación, es más seguro hacer referencia a otras tablas dentro
de subconsultas de SELECT, aunque a menudo es más difícil leer y más retardado que
utilizando una unión.
Ejemplo:
 Actualizar el nombre de la categoría HIGINE PERSONAL a HIGIENE PERSONAL, en la
columna nombrecat:
UPDATE CATEGORIAS SET nombrecat = ‘HIGIENE PERSONAL’ WHERE nombrecat = ‘HIGINE
PERSONAL’;

ALTER TABLE
Cambia la definición de una tabla existente. Existen varias subformas:
ADD COLUMN
Añade una nueva columna a la tabla utilizando la misma sintaxis que CREATE TABLE.

DROP COLUMN
Elimina una columna de la tabla. Los índices y constraints que involucra la columna
deberán ser automáticamente borrados también. Si algún elemento externo depende de
la columna, será necesario utilizar la clausula CASCADE, por ejemplo, referencias a claves
foráneas o vistas.
SET/DROP DEFAULT
Coloca o remueve el valor predefinido de una columna. El valor predefinido sólo se aplica a
las órdenes INSERT subsecuentes. También pueden crearse valores predeterminados para
vistas, en el caso en que son insertadas en las declaraciones INSERT antes de que la regla
de la vista ON INSERT sea aplicada.
SET/DROP NOT NULL
Cambian si una columna es marcada para permitir valores o rechazar los valores nulos.
SET STATISTICS
Coloca un valor de estadísticas recogidas por columna para una operación ANALIZE
subsecuente. El valor puede estar en el rango de 0 a 1000; alternativamente, se usa -1
para revertir el uso del sistema de valores de estadísticas predeterminado.
SET STORAGE
Define el modo de almacenamiento de una columna. Este controla si dicha columna depende
de otra tabla suplementaria, y si los datos deben comprimirse o no. PLAIN debe utilizarse
para valores de longitud fija como el entero interno no comprimible. MAIN es para datos
ligados comprimibles. EXTERNAL es para datos externos no comprimibles, y EXTENDED es
para datos externos comprimibles. EXTENDED es el valor predeterminado para todo tipo



                                               41
                     ESCUELA POLITÉCNICA NACIONAL
                                       BASE DE DATOS



de datos que lo soportan.
SET WITHOUT OIDS
Quita el identificador de columna de la tabla. La remoción de OIDs de una tabla no ocurre
inmediatamente. El espacio que ocupa el OID se guardará cuando la fila sea actualizada.
Si no se actualiza la fila, el espacio y el valor del OID son guardados indefinidamente. Esta
semántica es similar a la del proceso DROP COLUMN.
RENAME
Cambia el nombre de una tabla (o un índice, secuencia, o vista) o el nomre de una columna
individual en la tabla. No hay efecto en el almacenamiento de datos.
ADD table_constraint
Añade un nuevo constraint a la tabla usando la misma sintaxis que CREATE TABLE.

DROP CONSTRAINT
Elimina los constraints de una tabla. Actualmente, loc constraints de las tablas no requieren
tener nombres únicos, entonces pueden haber más de un constraint identidificados los cuales
        serán eliminados.
OWNER
Cambia el propietario de una tabla, índice, secuencia, o vista de un usuario especificado.

CLUSTER
Marca una tabla para futuras operaciones CLUSTER. La clausula ALTER TABLE solo puede
ser ejecutada por el propietario de la tabla, mientras la clausula ALTER TABLE OWNER, sólo
puede ser ejecutada por un superusuario.
Sintaxis:
ALTER TABLE [ ONLY ] name [ * ]
   ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
ALTER TABLE [ ONLY ] name [ * ]
   DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER TABLE [ ONLY ] name [ * ]
   ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }
ALTER TABLE [ ONLY ] name [ * ]
   ALTER [ COLUMN ] column { SET | DROP } NOT NULL
Donde:
        name: Nombre de una tabla existente para alterarla. Si se desea alterar una sola tabla,
        se debe utilizar la clausula ONLY. Caso contrario, se alterarán la tabla y todas las tablas
        que desciendan de ésta.
        column: Nombre de una columna ya existente o nueva.
        type: Tipo de dato para la nueva columna.
        new_column: Nombre nuevo para una columna existente.
          new_name: Nombre nuevo para la tabla.
        table_constraint: Nuevo constraint para la tabla.
          constraint_name: Nombre de un constraint existente para eliminarlo.
        new_owner: Nombre del usuario o del nuevo propietario de la tabla.
        index_name: Nombre del índice que indica la tabla marcada para CLUSTER.
CASCADE
Elimina automáticamente objetos que dependen de una columna o constraint eliminado.
RESTRICT
Negación a eliminar la columna o constraint si hay objetos dependientes. Esta es una
conducta predeterminada.



                                                42
                 ESCUELA POLITÉCNICA NACIONAL
                              BASE DE DATOS




IMPLEMENTACIÓN DE LA BDD PEDIDOS
create database PEDIDOS;
CREATE TABLE EMPLEADOS(
 EMPLEADOID int NOT NULL,
 NOMBRE char(30) NULL,
 APELLIDO char(30) NULL,
 FECHA_NAC date NULL,
 REPORTA_A int NULL,
 EXTENSION int NULL,
 CONSTRAINT PK_EMPLEADOS PRIMARY KEY (EMPLEADOID));
CREATE TABLE PROVEEDORES(
 PROVEEDORID int NOT NULL,
 NOMBREPROV char(50) NOT NULL,
 CONTACTO char(50) NOT NULL,
 CELUPROV char(12) NULL,
 FIJOPROV char(12) NULL,
 CONSTRAINT PK_PROVEEDORES PRIMARY KEY
(PROVEEDORID ) );
CREATE TABLE CATEGORIAS(
 CATEGORIAID int NOT NULL,
 NOMBRECAT char(50) NOT NULL,
 CONSTRAINT PK_CATEGORIAS PRIMARY KEY
(CATEGORIAID) ) ;
CREATE TABLE CLIENTES(
 CLIENTEID int NOT NULL,
 CEDULA_RUC char(10) NOT NULL,
 NOMBRECIA char(30) NOT NULL,
 NOMBRECONTACTO char(50) NOT NULL,
 DIRECCIONCLI char(50) NOT NULL,
 FAX char(12) NULL,
 EMAIL char(50) NULL,
 CELULAR char(12) NULL,
 FIJO char(12) NULL,
 CONSTRAINT PK_CLIENTES PRIMARY KEY
(CLIENTEID) );
CREATE TABLE ORDENES(
 ORDENID int NOT NULL,
 EMPLEADOID int NOT NULL,
 CLIENTEID int NOT NULL,
 FECHAORDEN date NOT NULL,
 DESCUENTO int NULL,



                                      43
                ESCUELA POLITÉCNICA NACIONAL
                             BASE DE DATOS



 CONSTRAINT PK_ORDENES PRIMARY KEY
(ORDENID) );
CREATE TABLE DETALLE_ORDENES(
 ORDENID int NOT NULL,
 DETALLEID int NOT NULL,
 PRODUCTOID int NOT NULL,
 CANTIDAD int NOT NULL,
 CONSTRAINT PK_DETALLE_ORDENES PRIMARY KEY
(ORDENID,DETALLEID ) );
CREATE TABLE PRODUCTOS(
 PRODUCTOID int NOT NULL,
 PROVEEDORID int NOT NULL,
 CATEGORIAID int NOT NULL,
 DESCRIPCION char(50) NULL,
 PRECIOUNIT numeric NOT NULL,
 EXISTENCIA int NOT NULL,
 CONSTRAINT PK_PRODUCTOS PRIMARY KEY
(PRODUCTOID )) ;
ALTER TABLE ORDENES
ADD CONSTRAINT FK_ORDENES_CLIEN_ORD_CLIENTES FOREIGN KEY(CLIENTEID)
REFERENCES CLIENTES (CLIENTEID)
on delete restrict on update restrict;
ALTER TABLE ORDENES ADD CONSTRAINT FK_ORDENES_EMPLE_ORD_EMPLEADO FOREIGN
KEY(EMPLEADOID)
REFERENCES EMPLEADOS (EMPLEADOID)
on delete restrict on update restrict;
ALTER TABLE DETALLE_ORDENES ADD CONSTRAINT FK_DETALLE__ORDEN_DET_ORDENES
        FOREIGN
KEY(ORDENID)
REFERENCES ORDENES (ORDENID)
on delete restrict on update restrict;
ALTER TABLE DETALLE_ORDENES ADD CONSTRAINT FK_DETALLE__PROD_DETA_PRODUCTO
        FOREIGN
KEY(PRODUCTOID)
REFERENCES PRODUCTOS (PRODUCTOID)
on delete restrict on update restrict;
ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTO_CATE_PROD_CATEGORI
        FOREIGN
KEY(CATEGORIAID)
REFERENCES CATEGORIAS (CATEGORIAID)
on delete restrict on update restrict;




                                    44
                    ESCUELA POLITÉCNICA NACIONAL
                                    BASE DE DATOS



ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTO_PROV_PROD_PROVEEDO
         FOREIGN
KEY(PROVEEDORID)
REFERENCES PROVEEDORES (PROVEEDORID)
on delete restrict on update restrict;
ALTER TABLE EMPLEADOS ADD CONSTRAINT FK_EMPLEADO_REPORTA FOREIGN
         KEY(REPORTA_A)
REFERENCES EMPLEADOS (EMPLEADOID)
on delete restrict on update restrict;
insert into categorias (categoriaid, nombrecat) values (100, ‘CARNICOS’);
insert into categorias (categoriaid, nombrecat) values (200, ‘LACTEOS’);
insert into categorias (categoriaid, nombrecat) values (300, ‘LIMPIEZA’);
insert into categorias (categoriaid, nombrecat) values (400, ‘HIGINE PERSONAL’);
insert into categorias (categoriaid, nombrecat) values (500, ‘MEDICINAS’);
insert into categorias (categoriaid, nombrecat) values (600, ‘COSMETICOS’);
insert into categorias (categoriaid, nombrecat) values (700, ‘REVISTAS’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(10, ‘DON DIEGO’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(20, ‘PRONACA’, ‘JUAN PEREZ’, ‘0923434467’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(30, ‘TONY’, ‘JORGE BRITO’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(40, ‘MIRAFLORES’, ‘MARIA PAZ’, ‘098124498’,’2458799’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(50, ‘ALMAY’, ‘PEDRO GONZALEZ’, ‘097654567’,’2507190’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(60, ‘REVLON’, ‘MONICA SALAS’, ‘099245678’,’2609876’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(70, ‘YANBAL’, ‘BETY ARIAS’, ‘098124458’,’2450887’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(120, ‘JURIS’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(80, ‘CLEANER’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(90, ‘BAYER’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov) values
(110, ‘PALMOLIVE’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
INSERT INTO PRODUCTOS VALUES (1,10,100,’SALCHICHAS VIENESAS’,2.60,200);
INSERT INTO PRODUCTOS VALUES (2,10,100,’SALAMI DE AJO’,3.60,300);
INSERT INTO PRODUCTOS VALUES (3,10,100,’BOTON PARA ASADO’,4.70,400);
INSERT INTO PRODUCTOS VALUES (4,20,100,’SALCHICHAS DE POLLO’,2.90,200);




                                            45
                  ESCUELA POLITÉCNICA NACIONAL
                                 BASE DE DATOS



INSERT INTO PRODUCTOS VALUES (5,20,100,’JAMON DE POLLO’,2.80,100);
INSERT INTO PRODUCTOS VALUES (6,30,200,’YOGURT NATURAL’,4.30,80);
INSERT INTO PRODUCTOS VALUES (7,30,200,’LECHE CHOCOLATE’,1.60,90);
INSERT INTO PRODUCTOS VALUES (8,40,200,’YOGURT DE SABORES’,1.60,200);
INSERT INTO PRODUCTOS VALUES (9,40,200,’CREMA DE LECHE’,3.60,30);
INSERT INTO PRODUCTOS VALUES (10,50,600,’BASE DE MAQUILLAJE’,14.70,40);
INSERT INTO PRODUCTOS VALUES (11,50,600,’RIMMEL’,12.90,20);
INSERT INTO PRODUCTOS VALUES (13,60,600,’SOMBRA DE OJOS’,9.80,100);
set datestyle to dmy;
INSERT INTO EMPLEADOS VALUES (1,’JUAN’, ‘CRUZ’, ‘18/01/67’,null, 231);
INSERT INTO EMPLEADOS VALUES (2,’MARIO’, ‘SANCHEZ’, ‘01/03/79’,1,144);
INSERT INTO EMPLEADOS VALUES (3,’VERONICA’, ‘ARIAS’, ‘23/06/77’,1, 234);
INSERT INTO EMPLEADOS VALUES (4,’PABLO’, ‘CELY’, ‘28/01/77’,2, 567);
INSERT INTO EMPLEADOS VALUES (5,’DIEGO’, ‘ANDRADE’, ‘15/05/70’,2, 890);
INSERT INTO EMPLEADOS VALUES (6,’JUAN’, ‘ANDRADE’, ‘17/11/76’,3, 230);
INSERT INTO EMPLEADOS VALUES (7,’MARIA’, ‘NOBOA’, ‘21/12/79’,3, 261);
INSERT INTO CLIENTES VALUES (1,’1890786576’,’SUPERMERCADO ESTRELLA’,’JUAN
ALBAN’,’AV.AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (2,’1298765477’,’EL ROSADO’,’MARIA CORDERO’,’AV.AEL
INCA’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (3,’1009876567’,’DISTRIBUIDORA PRENSA’,’PEDRO
PINTO’,’EL PINAR’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (4,’1876090006’,’SU TIENDA’,’PABLO PONCE’,’AV.
AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (5,’1893456776’,’SUPERMERCADO DORADO’,’LORENA
PAZ’,’AV.6 DICIEMBRE’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (6,’1678999891’,’MI COMISARIATO’,’ROSARIO UTRERAS’,’AV.
AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (7,’1244567888’,’SUPERMERCADO DESCUENTO’,’LETICIA
ORTEGA’,’AV.LA PRENSA’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (8,’1456799022’,’EL DESCUENTO’,’JUAN TORRES’,’AV.
PATRIA’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (9,’1845677777’,’DE LUISE’,’JORGE PARRA’,’AV.
AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (10,’183445667’,’YARBANTRELLA’,’PABLO POLIT’,’AV.REPUBL
ICA’,NULL,NULL,NULL,NULL);
INSERT INTO ORDENES VALUES(1,3,4,’17/06/07’, 5);
INSERT INTO ORDENES VALUES(2,3,4,’02/06/07’, 10);
INSERT INTO ORDENES VALUES(3,4,5,’05/06/07’, 6);
INSERT INTO ORDENES VALUES(4,2,6,’06/06/07’, 2);
INSERT INTO ORDENES VALUES(5,2,7,’09/06/07’, NULL);
INSERT INTO ORDENES VALUES(6,4,5,’12/06/07’, 10);




                                         46
                   ESCUELA POLITÉCNICA NACIONAL
                                  BASE DE DATOS



INSERT INTO ORDENES VALUES(7,2,5,’14/06/07’, 10);
INSERT INTO ORDENES VALUES(8,3,2,’13/06/07’, 10);
INSERT INTO ORDENES VALUES(9,3,2,’17/06/07’, 3);
INSERT INTO ORDENES VALUES(10,2,2,’18/06/07’, 2);
INSERT INTO detalle_ordenes VALUES(1,1,1,2);
INSERT INTO detalle_ordenes VALUES(1,2,4,1);
INSERT INTO detalle_ordenes VALUES(1,3,6,1);
INSERT INTO detalle_ordenes VALUES(1,4,9,1);
INSERT INTO detalle_ordenes VALUES(2,1,10,10);
INSERT INTO detalle_ordenes VALUES(2,2,13,20);
INSERT INTO detalle_ordenes VALUES(3,1,3,10);
INSERT INTO detalle_ordenes VALUES(4,1,9,12);
INSERT INTO detalle_ordenes VALUES(5,1,1,14);
INSERT INTO detalle_ordenes VALUES(5,2,4,20);
INSERT INTO detalle_ordenes VALUES(6,1,3,12);
INSERT INTO detalle_ordenes VALUES(7,1,11,10);
INSERT INTO detalle_ordenes VALUES(8,1,2,10);
INSERT INTO detalle_ordenes VALUES(8,2,5,14);
INSERT INTO detalle_ordenes VALUES(8,3,7,10);
INSERT INTO detalle_ordenes VALUES(9,1,11,10);
INSERT INTO detalle_ordenes VALUES(10,1,1,5);

                              CONSULTAS
-- Muestre los productos que tengan un precio mayor a 2.60
SELECT descripcion, preciounit AS “Precio unitario”
FROM productos
WHERE preciounit >2.60
Intervalos (BETWEEN y NOT BETWEEN).
-- Muestre los productos con precios entre 9 y 15
SELECT descripcion, preciounit AS “Precio unitario”
FROM productos
WHERE preciounit BETWEEN 9 AND 15
ORDER BY preciounit
-- Muestre los productos con precios fuera de 9 y 15
SELECT descripcion, preciounit AS “Precio unitario”
FROM productos
WHERE preciounit NOT BETWEEN 9 AND 15
ORDER BY preciounit
Listas (IN, NOT IN).
-- Muestre las categorías que sean CARNICOS, LACTEOS o MEDICINAS
SELECT *
FROM categorias



                                          47
                     ESCUELA POLITÉCNICA NACIONAL
                                      BASE DE DATOS



WHERE nombrecat IN (‘CARNICOS’,’LACTEOS’,’MEDICINAS’)
Coincidencia de modelos (LIKE y NOT LIKE).
-- Muestre los empleados cuyo nombre empiece con J
SELECT *
FROM empleados
WHERE nombre LIKE ‘J%’
-- Muestre los empleados cuyo nombre no empiece con J
SELECT *
FROM empleados
WHERE nombre NOT LIKE ‘J%’


                               SUBCONSULTAS

Ejemplo:
Si queremos conocer los artículos que tienen mayor precio que el producto llamado
’Tornillos’, utilizaremos la consulta:
SELECT * FROM productos
WHERE preciounit > (SELECT preciounit FROM productos
WHERE descripcion =’BOTON PARA ASADO’);
Cuando revisamos la consulta anterior, podemos ver la palabra clave SELECT dos veces. La
         primera
al principio de la consulta - a la que nos referiremos como la SELECT externa - y la segunda en
la clausula WHERE, donde empieza una consulta anidada - nos referiremos a ella como la
         SELECT
interna. Para cada tupla de la SELECT externa, la SELECT interna deberá ser evaluada. Tras cada
evaluación, conoceremos el precio de la tupla llamada ‘BOTON PARA ASADO’, y podremos
         chequear
si el precio de la tupla actual es mayor.
Si queremos conocer todos los proveedores que no venden ningún producto (por ejemplo,
         para
poderlos eliminar de la base de datos), utilizaremos:
SELECT * FROM proveedores p
WHERE NOT EXISTS
(SELECT * FROM productos pr WHERE pr.proveedorid =
p.proveedorid);
Unión, Intersección, Excepción
Estas operaciones calculan la unión, la intersección y la diferencia de la teoría de conjuntos de
         las
tuplas derivadas de dos subconsultas.
Ejemplo: Union, Intersect, Except




                                               48
                    ESCUELA POLITÉCNICA NACIONAL
                                   BASE DE DATOS



La siguiente consulta es un ejemplo de UNION:
SELECT p.nombreprov, p.contacto
FROM proveedores p
WHERE p.nombreprov= ‘TONY’ UNION
SELECT p.nombreprov, p.contacto
FROM proveedores p
WHERE p.nombreprov= ‘JURIS’
Aquí se tiene un ejemplo para INTERSECT:
SELECT p.nombreprov, p.contacto
FROM proveedores p
WHERE p.nombreprov= ‘TONY’ INTERSECT
SELECT p.nombreprov, p.contacto
FROM proveedores p
WHERE p.nombreprov= ‘JURIS’
Finalmente, un ejemplo de EXCEPT:
SELECT p.nombreprov, p.contacto
FROM proveedores p
WHERE p.nombreprov= ‘TONY’ EXCEPT
SELECT p.nombreprov, p.contacto
FROM proveedores p
WHERE p.nombreprov= ‘JURIS




BIBLIOGRAFÍA:
      http://postgresql.org.pe/articles/introduccion_a_postgresql.pdf
      http://www.linux-es.org/node/536
      http://alvherre.cl/pgsql/charlas/10-Introduccion.pdf
      http://profesores.elo.utfsm.cl/~agv/elo330/2s02/projects/denzer/informe.pdf
      http://preguntaslinux.org/postgresql-8-2-t-2603.html
      http://www.vivalinux.com.ar/software/postgresql-9.0
      http://www.postgresql.org/about/press/presskit82.html.es




                                            49

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:324
posted:1/15/2011
language:Spanish
pages:49
Description: Escuela politecnica Nacional Manual PostgreSQL