Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

PROTOCOLOS PARA LA
CREACIÓN Y USO DE BASES
DE DATOS ONLINE
Base de datos Gollum

Programa CETAPROT: Hacía una armonización

de los protocolos de investigación en cetáceos.
Programa cofinanciado por la fundación
Biodiversidad, e incluido en el programa
Intemares.

Jazel Ouled-Cheikh Bonan

Joan Gimenez.

Renaud de Stephanis

Febrero 2019

1
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

2
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

1. Principios de las bases de datos

• ¿Qué es una base de datos relacional?
• ¿Qué es una primary key?
• ¿Qué es una foreign key?
• Parámetros de una base de datos
• Ejemplo: varias tablas relacionadas
• ¿Cómo “hablar” con la base de datos?

2. ¿Cómo crear una base de datos?

• Planteamiento sobre papel
• Planteamiento sobre mantel
• Planteamiento a limpio

3. Software: presentación, descarga y conexiones

• PostgreSQL: Introducción y descarga

• PgAdmin4: Introducción
• ElephantSQL: link, pantalla principal, detalles de la base de datos
• DbVisualizer: Introducción

4. Creación de tablas y relaciones:

• Columnas
• Tipos de datos
• Explicación de los tipos de datos que menos pesan.
• Definir una PK
• Definir una FK
• Definir restricciones (CHECK)

6. ¿Cómo visualizar la estructura creada?

• Conexión de la base de datos a DbVisualizer

7. ¿Cómo obtener datos?

• Operaciones CRUD en Pgadmin4 y ElephantSQL

8. Conexión a R

• Explicación y funciones del paquete RPostgreSQL

• Operaciones CRUD desde R

3
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

1. PRINCIPIOS DE LAS BASES DE DATOS

1.1. ¿Qué es una base de datos relacional?
Una base de datos relacional es una colección de elementos de datos organizados en un
conjunto de tablas formalmente descritas desde las que se puede acceder a los datos o
volver a montarlos de muchas maneras diferentes sin tener que reorganizar las tablas
de la base. Este tipo de base de datos permite una interacción directa con el usuario
mediante el lenguaje de programación SQL (Structured Query Language). Este lenguaje
hace que se puedan hacer consultas a la base de datos e inserción, eliminación y
actualización de datos, las conocidas como operaciones CRUD (Create, Read, Update &
Delete).
Cada tabla de la base de datos (a veces también llamada relación) contiene una o más
categorías de datos estructuradas en columnas. Cada una de las filas contiene un
identificador, que hace que cada fila sea única. Por ejemplo, una base de datos típica de
un negocio incluiría una tabla llamada clientes con columnas que indiquen nombre,
dirección y número de teléfono. Otra tabla identificaría el pedido del cliente, con
columnas que lo definan: identificador del producto, cliente, fecha del pedido y precio
de venta.
[Link]

1.2. ¿Qué es una primary key (llave primaria)?

Una primary key (llave primaria) es una columna especial de la base de datos relacional
destinada únicamente a identificar todas las observaciones de una tabla. Tiene dos
características fundamentales:

• El valor de cada fila debe ser único (no se puede repetir en otra fila).
• No puede contener valores nulos
Si no cumpliera alguna de las dos condiciones, alguna de las filas quedaría sin identificar
y esto imposibilitaría el funcionamiento de la base de datos relacional.

1.3. ¿Qué es una foreign key (llave foránea)?

Una foreign key (llave foránea) es una columna o grupo de columnas en la table de una
base de datos relacional que proporciona la relación entre dos tablas. Actúa como unión
entre tablas ya que contiene los valores de la primary key de otra tabla. En este caso no
es necesario que los valores sean únicos y además podrían ser nulos.
[Montar base de datos de dos tablas y poner recorte, indicar PK y FK]

4
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

1.4. Tipos de relaciones entre tablas

Según como sean las columnas relacionadas, tendremos diferentes tipos de relaciones
entre tablas:
1. Uno a uno/1:1/One to one
2. Uno a infinito/1:n/One to many
3. Infinito a infinito/n:n/many to many

1.5. Parámetros de una base de datos

Toda base de datos tiene una serie de parámetros que permiten acceder a ella. Con ellos
podremos tanto conectarnos a la base de datos para realizar las operaciones CRUD (1.1)
como conectar la base de datos a otros programas o servicios que nos interesen, como
software para el análisis de los datos u otros. Los parámetros son los siguientes:

• Servidor (Host/Server): la base de datos ocupa un espacio. Puede estar ubicada

tanto en un servidor local (localhost), es decir, en el mismo ordenador donde se
trabaja, como en un servidor remoto, es decir, en la nube.
• Nombre de usuario (Username)
• Nombre de la base de datos
• Contraseña (Password)
• URL

1.6. SQL: ¿Cómo hablar con la base de datos?

SQL significa Structured Query Language. Se trata de un lenguaje de programación que
sirve para administrar y recuperar información de sistemas de gestión de bases de datos
relacionales (RDBMS, Relational Database Management System). La comunicación con
la base de datos se hace mediante consultas o queries en este lenguaje. A continuación
se muestran algunos ejemplos de queries básicos y otros un poco más complejos.
Imaginemos que tenemos una base de datos formada por las siguientes tablas:
• SELECT*FROM sightings; à seleccionarlo todo (*) de la tabla “sightings”. Para
terminar la query debemos poner ;
• SELECT*FROM notas_publicadas WHERE proyecto_id <> 0; à seleccionarlo todo
de la tabla “notas_publicadas” donde el proyecto_id sea diferente <> de 0.
• SELECT*FROM notas_publicadas WHERE proyecto_id <> 0 AND impacto > 5; à
seleccionarlo todo de la tabla “notas_publicadas donde el “proyecto_id” sea
diferente de 0 y el impacto sea superior a 5.

5
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

• SELECT notas_enviadas.medio_id, notas_publicadas.impacto

FROM notas_enviadas

JOIN notas_publicadas
ON notas_enviadas.nota_id=notas_publicadas.nota_id
WHERE impacto > 5;
La ultima query es más compleja. En ella, cogemos datos de dos tablas diferentes
haciendo lo que se llama “JOIN”. La transcripción sería à seleccionar la columna
“medio_id” de la tabla notas_enviadas y la columna impacto de la tabla
“notas_publicadas”. Unimos las observaciones de la tabla “notas_enviadas” y la tabla
“notas_publicadas” en todos los casos donde la “nota_id” de “notas_enviadas” sea igual
a “nota_id” de la tabla “notas_publicadas”.

2. CREACIÓN DE UNA BASE DE DATOS

2.1. Planteamiento sobre papel
Una base de datos relacional requiere una estructura muy clara y eficiente, para que
permita trabajar los datos de forma sencilla y limpia.
La base de datos debe ser estructurada a priori mediante bolígrafo y papel para ordenar
todas las ideas, encontrar errores y mejorar aquellos puntos más conflictivos. Es por eso
que es altamente recomendable empezar el trabajo por plasmar todas las ideas sobre
papel.
[Un par de fotos para mostrar diferentes planteamientos sobre papel]
Lo que tiene que quedar escrito en el papel son las diferentes tablas de las que va a
constar nuestra base de datos. Además, deberíamos definir los nombres para la primary
key y las foreign key que van a relacionar las tablas y finalmente el tipo de relación
existente entre las tablas (1.5).
1. Listar las tablas que van a constar en la base de datos
2. Definir primary keys (una por tabla)
3. Insertar las primary keys de unas tablas en otras, formando así foreign keys y
creando así relaciones entre tablas.

2.2. Planteamiento sobre un mantel

Una vez los cimientos de la base de datos hayan quedado definidos sobre un papel, es
aconsejable pasar todo lo que se haya definido en la libreta a una superficie mayor,
como puede ser por ejemplo un mantel de papel para mesas. Se extiende el mantel

6
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

sobre una mesa y se pasa todo lo escrito en la libreta con rotuladores de diferentes
colores para los diferentes elementos, como, por ejemplo:
- Azul: tablas
- Rojo: relaciones
La función de este paso no es nada más que clarificar y dar espacio a la base de datos
para comprenderla mejor y poder encontrar errores. Probablemente la primera versión
de la base de datos sobre el mantel no quedará muy limpia y clara, y es posible que se
tenga que rehacer a limpio.
[Fotos de los manteles sucios y limpios].

Cuando tengamos la base de datos clara, estructurada y sin errores sobre papel,
podremos pasarla al ordenador, usando los diferentes softwares que nos permitirán
crear, visualizar y hacer cambios en la base de datos y su estructura. Esto se hace
mediante RDBMS (Relational Database Management Systems, 1.6). Los diferentes
programas que vamos a utilizar se detallan en el siguiente protocolo sobre presentación
y descarga de los mismos.

3. SOFTWARE: PRESENTACIÓN Y DESCARGA Y CONEXIONES

El objetivo de este protocolo es establecer cómo se deben descargar los programas
que vamos a utilizar y cómo realizar las conexiones entre ellos.

3.1. PostgreSQL: Introducción y descarga

PostgreSQL es un RDBMS (Relational Database Management System, 2.2). Se trata de
un software libre, lo que significa que es completamente gratuito y que además hay una
comunidad de desarrolladores que funcionan mayoritariamente de forma altruista y
que trabajan para el desarrollo constante del programa y extensiones específicas de este
para adaptarlo a muchos usos diferentes. Este software nos va a permitir crear una base
de datos relacional, con todas sus tablas, primary keys, foreign keys, relaciones entre
tablas, restricciones y también hacer queries para las operaciones CRUD (1.1). El hecho
que sea programario libre es gran punto a favor para decantarse por usar este RDBMS,
así pues, procederemos a la descarga e instalación del mismo.
1. Búsqueda en google: primera entrada

7
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

2. Descargar

3. Elección del sistema operativo: disponible para Mac, Windows y Linux. Aquí
procederemos con Windows.

4. Descarga del instalador: hacer click en Download the installer.

8
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

5. Elección de la versión de PostgreSQL: actualmente la última versión es la 11.2

(PostgreSQL 11). Es recomendable descargar la última versión.

6. Cuando se inicia la descarga, debe salir el siguiente mensaje, que ya anuncia

algunas de las prestaciones de PostgreSQL: formación, documentación y
comunidad.

9
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

7. Descarga finalizada

8. Navegación por el asistente de instalación. Hacer click en Siguiente.

10
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

9. Instalación de todos los componentes: PostgreSQL Server, pgAdmin4, Stack

Builder y Command line Tools. Hacer click en Siguiente. Las siguientes pantallas
irán apareciendo:

11
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

12
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

10. Hacer click en Terminar y finalizar la instalación.

11. Automáticamente al finalizar la instalación, se abrirá Stack Builder, una parte de

PostgreSQL que servirá para instalar extensiones una vez el programa está instalado.
En la pestaña que sale debemos elegir PostgreSQL 11 (x64) on port 5432. Hacer click en
Next.

12. En esta pantalla se ofrecen los diferentes tipos de extensión a

elegir. No es necesario elegir ninguna en concreto de momento. Si

13
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

se va a trabajar con objetos espaciales y el objetivo de la base de datos a crear es

almacenar objetos espaciales, es recomendable desplegar la pestaña Spatial extensions
e instalar PostGIS, que permitirá una conexión con el software libre QGIS.

¡A continuación, se debe reiniciar el equipo y ya tendremos PostgreSQL instalado!

3.1.1. PgAdmin4: Introducción.

PostgreSQL en si es una consola negra con la que podemos interaccionar mediante
código SQL (1.6).

Esta interfaz no es muy user-friendly y por eso se usa pgAdmin4, un ambiente de trabajo
mucho más visual, y donde se puede trabajar con clicks además de con queries SQL.
PgAdmin4 ya está instalado en el ordenador después de la instalación de PostgreSQL,
así que procedemos a buscarlo en el equipo.

14
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Hacemos click en pgadmnin4 para ejecutar el programa.

Vista principal del programa: A partir de ahora nos centraremos en la parte izquierda, la
barra que recorre la pantalla de arriba abajo (barra de operaciones)

Zoom de la barra de la barra de operaciones.

Podemos ver que hay una pestaña con los
servidores (1.5). La barra de operaciones funciona
con pestañas y debemos ir desplegándolas para
trabajar. La base de datos está alojada en un
servidor, que puede ser local o remoto (online).

15
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Si desplegamos la pestaña de servidores podremos ver

aquellos servidores disponibles, conectados a nuestro
equipo. En este caso tenemos dos:

- PostgreSQL 11: servidor local (local host), servidor

que se encuentra por defecto ya en el programa,
sirve para tener las bases de datos en el equipo
donde ser trabaja.
- CIRCE_DATABASE: servidor remoto. Se trata de un
servidor online que hemos obtenido. En el siguiente
protocolo se explicará esta conexión.

Si desplegamos la pestaña de cualquiera de los dos servidores, el programa nos pedirá

la contraseña para acceder a él (1.5).

Introducimos la contraseña y ya podremos acceder al servidor, que contiene la base de

datos.

Nos aparecen todas las pestañas disponibles. Las principales

y con las que trabajaremos son las siguientes:

- Databases: bases de datos en el servidor.

- Schemas: pestaña que contiene la estructura de la
base de datos, con sus tablas.
- Tables: tablas que conforman la base de datos.

16
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Zoom de la barra de operaciones

SERVIDORES

BASES DE DATOS

ESTRUCTURA

TABLAS

3.2. ElephantSQL: Introducción:

ElephantSQL nos ofrece la posibilidad de alojar nuestra base de datos en un servidor
remoto, y, por lo tanto, de tener nuestra base de datos en la nube, online. De esta
forma, podremos acceder a la base de datos desde cualquier dispositivo y de forma
inmediata. El link a la página principal es el siguiente:
[Link]

No hace falta descargar ningún programa, ya que se trata de una página web. El
objetivo de nuestro uso de ElephantSQL es obtener los parámetros de una base de
datos online (1.5) para poderla administrar desde pgAdmin4.

17
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Página principal de ElephantSQL. Lo primero que tendremos que hacer es registrarnos:

1. Página principal de ElephantSQL. Hacer click en Log in.

2. Hacer click en sign up y aparecerá la siguiente pantalla. Ahí debemos poner

nuestra dirección de e-mail y seguir los pasos de registro estándar.

18
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

ElephantSQL funciona mediante instancias. Podemos alojar una base de datos por instancia.
Dependiendo de las prestaciones que necesitemos (espacio, velocidad), alojaremos la base de
datos usando un plan de hosting u otro, Para crear una instancia tenemos que hacer click en
Create New Instance.

A continuación, debemos elegir un nombre para la base de datos y un plan de hosting.

El que sale en la captura de pantalla, Tiny Turtle es la versión de prueba, con solo 20 MB
de capacidad. Tiene poco espacio y funciona de forma bastante lenta, pero es ideal para
aprender cómo funciona ElephantSQL. El siguiente paso es hacer click en Select Region,
para escoger la ubicación física del servidor.

19
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Aquí podemos ver todas las opciones de Región. Utilizamos la que más nos convenga y
hacemos click en Review.

Finalmente, se nos muestra el resumen de la creación de nuestra instancia, con el

nombre de la base de datos, proveedor del servicio de hosting y finalmente la región.
¡Hacemos click en Creater instance y ya tenemos nuestra instancia creada!

20
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Como podemos comprobar, la instancia DATABASE_PROTOCOLO que hemos creado,

ya aparece entre las otras instancias creadas anteriormente.

La nueva instancia es donde estará nuestra base de datos. Si hacemos click en la

instancia, obtendremos todos los parámetros (1.5) de la base de datos:

1. Server: servidor.
2. User & Default database: nombre de la base de datos y nombre de usuario.
3. Password: contraseña para acceder al servidor.
4. Current database size: tamaño actual, está vacío porque lo acabamos de crear.
5. Max database size: 20 Mb, cambia según el plan que escojamos.

21
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

3.3. Conexión PgAdmin4-ElephantSQL

Con los parámetros obtenidos en ElephantSQL, podemos conectar pgAdmin4 con el
servidor remoto que va alojar nuestra base de datos online.
1. Hacer click en DETAILS en el menú del lado izquierdo de la pantalla, para entrar en
los parámetros de la base de datos.

2. Abrir pgAdmin4:

3. Debemos crear un servidor nuevo, así que, en la barra de operaciones, hacemos

click derecho en Servers à Create à Server…

22
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

4. Aparecerá la siguiente pantalla donde debemos darle un nombre al nuevo servidor

5. Hacemos click en la segunda pestaña, Connection e introducimos los parámetros de

ElephantSQL

23
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.
ElephantSQL PgAdmin4 Comentarios
Server Host No introducir
name/adress la parte entre
paréntesis de
ElephantSQL
User & Default Maintenance
database database
User & Default Username
database
Password Password

6. Hacemos click en Save y si la conexión se ha realizado

satisfactoriamente, el nuevo servidor aparecerá en la barra de
operaciones.

24
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

3.4. DbVisualizer: Introducción

PgAdmin4 permite estructurar toda la base de datos, pero no tiene ninguna herramienta
para la visualización de la estructura creada. Visualizar la estructura es muy importante,
ya que solo así sabremos si las relaciones entre tablas se han creado realmente o no.
Además, de esta manera podremos ver lo que habíamos planteado inicialmente sobre
papel/mantel en el ordenador, viendo si nuestra base de datos está preparada para ser
utilizada o no. Para visualizarla necesitaremos usar un software aparte. Hay muchas
opciones y programas diferentes que permiten visualizar la estructura y todos funcionan
de forma similar. El que vamos a presentar aquí es DbVisualizer. La idea principal es
introducir los parámetros de la base de datos proporcionados por ElephantSQL a
DbVisualizer, de forma que este pueda leer las relaciones y plasmarlas en la pantalla.
Antes que todo, pero, vamos a proceder a descargar el programa.
1. Búsqueda en Google: se encuentra en la primera entrada.

2. Descargar programa: hacer click en Download

25
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

3. Escoger versión y sistema operativo. Está disponible para Windows, Mac y Linux.
Aquí vamos a seguir el procedimiento para Windows, pero es similar para el resto
de sistemas operativos. Hacer click en Download.

4. Instalación: justo después de la descarga, vamos haciendo click en next en el

asistente de instalación hasta que salga el botón Finish.

26
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

5. Apariencia de DbVisualizer. A la izquierda tenemos la barra que nos va a permitir

administrar las conexiones a las bases de datos. Para añadir una nueva conexión
debemos hacer click en el botón con el icono de unos discos azules y un signo + de
color verde.

A continuación, aparecerá este mensaje: hacer click en No Wizard.

27
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

6. Efectuar la conexión: aparece una pantalla donde debemos introducir los

parámetros de la base de datos.

Lo primero que deberíamos hacer es introducir PostgreSQL en Driver (JDBC). Cuando lo

hagamos, los campos Database Type, Database Server, Database Port y Database se
rellenarán automáticamente. Deberemos cambiar las entradas automáticas por los
parámetros proporcionados por ElephantSQL para efectuar la conexión con la base de
datos.

28
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Introducimos los valores en los diferentes campos y hacemos click en Connect. Como
podemos comprobar, en el recuadro llamado Connection message en la parte inferior,
todavía pone Disconnected.

Después de hacer click en connect, si la conexión se ha realizado de forma satisfactoria, el

mensaje tendría que cambiar a:

29
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

4. Creación de tablas y relaciones en PostgreSQL

El objetivo de este protocolo es mostrar cómo deben crearse las tablas que configuran
nuestra base de datos y cómo establecer relaciones entre ellas para hacer que la base
de datos sea relacional.

4.1. Creación de tablas:

1. Abrir pgAdmin4

2. Seleccionar servidor. En este caso seleccionamos PROTOCOLOS_DB, previamente

creado con los parámetros ofrecidos por ElephantSQL (3.3).

30
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

3. Introducir contraseña, la sacamos de ElephantSQL. Copiar la contraseña

directamente de la pestaña DETAILS de ElephantSQL (3.3) y pegar en la pestaña
emergente que nos dará PostgreSQL al seleccionar el servidor.

Si nos hemos conectado correctamente, el icono que acompaña el nombre del servidor
va a cambiar a la cabeza de un elefante.

4. Selección de la base de datos.

4.1. Usando el plan gratuito (Tiny Turtle): tendremos que seleccionar la base de
datos que nos ha sido asignada entre muchas otras. Cuando despleguemos la
pestaña del servidor, aparecerán todas de esta forma:

31
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Aparecen muchas bases de datos, ordenadas

alfabéticamente, pero sólo tenemos acceso a una de
ellas, la que nos ha proporcionado ElephantSQL. En
este caso es la base de datos wrfgmrr (resaltado en
azul en la siguiente captura de pantalla).

Buscamos pues la base de datos entre las que

empiezan por w, como podemos ver, el icono que
acompaña a wrfgmrr es de color amarillo y marrón y
no gris, y además la cruz roja que acompaña a todas
las otras, ha desaparecido, lo que significa que
estamos conectados a ella.

32
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

4.2. Usando un plan de pago: Blissful Butterfly en este caso. En vez de encontrar
una lista muy larga y ordenada alfabéticamente, sencillamente encontraremos la
base de datos que nos ha sido asignada. Además, el funcionamiento de la base de
datos será mucho más rápido. En este caso, la base de datos asignada es oyaglupl,
y es la única que aparece. Los pasos para la conexión al servidor, utilizando un plan
de pago son exactamente los mismos que los descritos anteriormente para TIny
turtle.

5. Desplegar las pestañas hasta llegar a Tables. Cuando desplegamos la primera

pestaña de la base de datos aparecen todas las opciones que vemos a
continuación. La Pestaña importante es la de Schemas, donde estarán todas las
tablas y estructura de la base de datos.

33
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

La siguiente pestaña que

aparece es Public, la
desplegamos y aparecen
muchas otras. Para
interaccionar con las tablas
debemos desplegar la
pestaña Tables.

Al lado izquierdo de cada pestaña hay un símbolo >. Si en

este momento hacemos click en este símbolo al lado de la
pestaña Tables, veremos que el símbolo > desaparece, ya
que no tenemos tablas, la base de datos está vacía.

34
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

6. Creación de tablas: hacer click derecho sobre Tables à Create à Table…

Al centro de la pantalla aparecerá un menú para definir todas las características de la

tabla.

35
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

7. Definir las características de la tabla:

- Nombre: escribir un nombre para la tabla. Nosotros lo escogemos.

- Columnas: definir nombres para las columnas y tipo de datos que vamos a
introducir en ellas. Para añadir una columna nueva, hay que hacer click en el
signo + de la derecha de la pantalla.

36
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Cuando hacemos click sobre +, aparece una fila en el menú con varios campos:
- Name: nombre para la columna
- Data type: tipo de datos que va a alojar la columna
- Length: número de caracteres
- Precision:
- Not NULL?: sirve para indicar si todas las filas de la columna tienen que tener
un valor, o, al contrario, puede haber registros nulos.
- Primary key?: sirve para definir la condición de una columna como Primary key,
es decir, que sus valores sean únicos y no nulos (1.2).

La primera tabla que vamos a crear será una tabla de protocolos, donde cada protocolo
tendrá un identificador numérico (entero, integer), llamado protocolo_id_code, un
nombre (text), llamado protocolo_id, y finalmente una columna que nos identificará al
autor del protocolo con un identificador numérico (entero, integer), llamado
autor_id_code.

37
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Debemos definir que protocolo_id_code es una primary key, ya que es el identificador

de la tabla. No puede ser nulo ni tampoco repetirse ningún valor. Para definir la
condición de primary key debemos hacer click en la pestaña constraints.

38
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Hacemos click en el signo + de la parte derecha de la pantalla para añadir una primary
key. Aparecen dos columnas, una para definir el nombre de la primary key y otra para
definir qué columna, de las definidas anteriormente, va a ser la primary key. Para definir
todo esto, debemos hacer click en icono de edición (libreta con lápiz).

Definimos el nombre de la primary key, que es recomendable que sea el nombre de la

columna + _pk, para así poderla identificar como tal. Esto se hace en la pestaña
General.

39
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Para definir la columna que actuará como primary key debemos hacer click en la
pestaña Definition.

Aquí, en el desplegable de Columns, podremos seleccionar una de las columnas que se

han creado anteriormente.

40
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

En el desplegable podemos encontrar las 3 columnas que habíamos creado.

Seleccionamos protocolo_id_code.

Hacemos click en guardar.

41
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Para comprobar que la tabla y la primary key ha sido creada correctamente, tenemos
que fijarnos en la barra de operaciones. En primer lugar, la tabla tabla_test tiene que
aparecer.

Si desplegamos la pestaña table_test y Columns debemos ver las 3 columnas que

hemos creado. Además, en constraints, tiene que aparecer la primary key que hemos
creado en el paso anterior, con el nombre protocolo_id_code_pk. En pgAdmin4, la
primary key se representa como una llave dorada.

8. Relacionar tablas: para relacionar la tabla table_test con otra, primero tenemos que
crear otra tabla. El procedimiento es exactamente el mismo que el que acabamos de
seguir. La tabla que crearemos será una tabla que informe de los autores de los
protocolos, con una columna de identificadores: autor_id_code, y otra de nombres:
autor_id. Si observamos, autor_id_code es una de las columnas de la tabla
table_test. Para relacionar las tablas haremos que autor_id_code sea una foreign
key en la tabla table_test, así, solo con los identificadores numéricos podremos saber
quién es el autor de cada protocolo.

42
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Para crear la Foreign key debemos ir a la tabla table_test de nuevo y hacer click
derecho en la pestaña Constraints à Create à Foreign key

Aparece un menú para definir las características de la foreign key, en primer lugar, en
la pestaña General definimos el nombre. Es recomendable usar el nombre de la
columna + _fk, para indicar que se trata de una foreign key. En pgAdmin4, la foreign
key se representa como una llave plateada.

43
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Para definir las columnas que queremos relacionar hacemos click en la pestaña
Columns. Ahí encontramos varios desplegables a rellenar:
- Local column: columna que actuará como foreign key. Autor_id_code.
- References: tabla a la cual pertenece la columna original. Autores. Sale
[Link] porque la tabla autores está dentro del Schema public.
- Referencing: columna de la tabla original a la que nos referimos.
Autor_id_code.
Hacemos click en el signo + que sale a la derecha del menú para añadir la relación.

Así, lo que acabamos de definir aparecerá en la parte inferior de la ventana

44
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Finalmente hacemos click en Save para guardar los cambios.

Como en el caso de la primary key, podemos comprobar que la foreign key
autor_id_code_fk se ha añadido de forma satisfactoria en la barra de operaciones.

En este caso hemos creado una relación 1 – infinito, ya que un protocolo puede tener
un solo autor, pero un autor puede haber escrito más de un protocolo.

45
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

5. Visualización de la estructura de la base de datos

El objetivo de este protocolo es mostrar cómo usar la conexión creada en el protocolo
software y conexiones (3.4) entre PostgreSQL y DbVisualizer. El software DbVisualizer
nos será de gran ayuda para poder visualizar la estructura de la base de datos, con
todas sus relaciones y restricciones.
1. Abrimos DbVisualizer (previamente instalado, protocolo 3.4).

2. Aparece un mensaje: Select Look and Feel for DbVisualizer. En el botón inferior se da
una cuenta atrás, y continuará en Windows a no ser que lo cambiemos.

46
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

3. Programa abierto, aspecto general. Para realizar la conexión tenemos que hacer click en
el icono señalado. A partir de aquí, seguir los pasos del protocolo 3.4 para conectar
PostgreSQL con DbVisualizer (Pasos 5 y 6).

4. Una vez seguidos los pasos 5 y 6 del protocolo 3.4, nuestra base de datos online estará
conectada a DbVisualizer. Cuando esto suceda, podremos desplegar la pestaña
Database Connection y nos saldrá el nombre de la base de datos que hayamos
proporcionado, que a la vez es el que nos proporcionó ElephantSQL. Hacemos click en
el signo + a la izquierda del nombre de la base de datos.

47
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

5. Se nos despliegan 3 pestañas más. Una que se llama information_schema, otra pg_catalog y
otra public. Hacemos doble click encima del nombre public.

6. Aparece una ventana. Hacemos click en Open Object

48
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

7. Se nos abre la siguiente pantalla. Cambiamos la pestaña y vamos a References.

8.

8. Las relaciones se cargarán. La barra de progreso se encuentra en la parte superior derecha

de la pantalla

49
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
 Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

9. Una vez se hayan cargado todas las tablas y relaciones veremos lo siguiente, con más o
menos tablas y relaciones según lo compleja y grande que sea la base de datos que hayamos
creado en PostgreSQL. ¡Así es como podremos ver como luce nuestra base de datos!

BONUS: tablas en DbVisualizer. Las tablas vienen representadas de la siguiente forma:

Nombre de la tabla

Primary Key

Nombre de las columnas Foreign Keys

Tipo de datos

50
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

6. Operaciones CRUD: Create, Read, Update, Delete

El objetivo de este protocolo es mostrar cómo realizar las llamadas operaciones CRUD
desde PostgreSQL. Estas operaciones sirven para rea3lizar cambios en la base de
datos:
- Create: introducir nuevas observaciones en la base de datos
- Read: leer datos en la base de datos
- Update: cambiar una observación por otra en la base de datos
- Delete: eliminar observaciones en la base de datos

1. Abrimos PgAdmin4, nos conectamos a nuestro servidor, base de datos y

buscamos las tablas de la base de datos (ver protocolo 4, hasta el paso 5). Aquí
abrimos la tabla autores creada en el protocolo 5. Hacemos click derecho en
autores. Nos aparece una pestaña con varias opciones. Ponemos el cursor ecima
de View/Edit Data y aparecerá otra pestaña. Hacemos click en All Rows.

51
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

2. Se nos abre la siguiente pantalla, que está partida en dos partes.

a. Panel SQL: aquí aparece la query SQL que pgAdmin4 ha hecho para
devolvernos la tabla autores: SELECT* FROM [Link]. Esta query
significa: seleccionar todo de la tabla autores, que se encuentra en el
esquema public.
b. Panel de datos: aquí aparece la tabla autores. Podemos hacer las
operaciones CRUD manualmente.

Panel SQL

Panel de Datos

Todas las operaciones CRUD se pueden hacer de dos formas distintas: a través del código
SQL y manualmente en el panel de datos. Aun así, para hacerlo con SQL, no se hace
desde el Panel SQL. Tenemos que seguir los siguientes pasos para abrir la q:

52
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

3. Click derecho sobre autores, como en el paso 1. Click en Query tool

4. Aparece una pantalla con un panel superior que admite entrada de código. Aquí es
donde debemos introducir el código SQL para hacer las operaciones CRUD. Para correr
el código introducido debemos seleccionarlo primero y luego hacer click en el icono del
relámpago que aparece en la parte superior.

53
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

A continuación, describiremos cómo hacer las operaciones CRUD. Cada operación tiene
su apartado, y éste está dividido en dos apartados: uno mostrando cómo hacer las
operaciones en SQL y el otro mostrando cómo hacerlo manualmente. En el caso de SQL,
deberemos introducir el código proporcionado en la query tool en el paso 3.

CREATE
SQL: INSERT INTO autores VALUES(1,'Homer');

Manual:

Hacemos click en la casilla en blanco autor_id_code e introducimos el valor que

queramos. Aquí vamos a darle un código numérico, empezando por el 1, y el nombre
del autor será Homer.

54
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

A continuación, guardamos.

Al guardar, a la parte izquierda de la pantalla tiene que aparecernos el siguiente

mensaje.

Vamos a introducir algunos registros más para poder hacer más claramente los
siguientes pasos.

READ
SQL:
SELECT*FROM [Link];//seleccionar toda la tabla autores.

SELECT*FROM [Link] WHERE autor_id_code=5;//seleccionar el

autor con código 5 de la tabla autores.

Manual: buscar datos en el panel de datos.

55
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

El output en el panel de datos de la segunda query SQL es el siguiente:

UPDATE
SQL:
UPDATE [Link]
SET autor_id_code='345', autor_id='Milhouse'
WHERE student_id=5;

Con esta query estamos actualizando el autor con el código 5, que hasta ahora era
Maggie. Le cambiamos el nombre a Milhouse y el código a 345. El output es el
siguiente.

Manual: buscar dato que queremos cambiar en el panel de datos y cambiarlo,

guardando a continuación. Si se ha guardado el cambio correctamente tiene que
aparecer:

56
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

DELETE
SQL:
DELETE FROM [Link]
WHERE autor_id_code=5;

Eliminamos el autor con el código 345 (Milhouse), el output es el siguiente:

Manual: Hacemos click sobre la fila que queremos eliminar. Tiene que quedar
ressaltada en azul.

57
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

Hacemos click en el botón de eliminar, que se encuentra en la parte superior de la

pantalla.

Al eliminar tiene que aparecernos el siguiente mensaje a la parte inferior derecha de

la pantalla si todo ha funcionado correctamente:

7. Conexión entre PostgreSQL y R

El objetivo de este protocolo es mostrar cómo realizar la conexión entre nuestra base
de datos online (o local) de PostgreSQL con R. De esta manera, que podremos realizar
las operaciones CRUD (6) directamente desde R. Una vez seamos capaces de obtener
datos que se encuentran en la nube desde R, podremos realizar todos los análisis que
queramos con estos datos, sin necesidad de tenerlos almacenados en discos duros,
ficheros etc.

58
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

En la siguiente página hay el script con el que podemos realizar la conexión, las
operaciones CRUD y otras funcionalidades para trabajar y explorar nuestra base de
datos. En este protocolo se integran conceptos explicados en los otros protocolos:
base de datos online (1), parámetros de las bases de datos (1), SQL (1), CRUD (6). Si
ya disponemos de los parámetros de la base de datos, podemos copiar el código
directamente en un script de R y empezar a jugar con las diferentes funciones
explicadas en el mismo código.
##-----------------------------------##

## CONNECTION R - PostgreSQL ##

##-----------------------------------##

#######################################################################

# This script can be used to connect R to a PostgreSQL database, #

# in both remote and local environments. #

# WARNING!!! put all the table names & fields in lowercase. Otherwise #

# RpostgreSQL is not going to find them. #

#######################################################################

# install RPostgreSQL package

[Link]("RPostgreSQL")

library(RPostgreSQL)

# Here you tell R what is the driver of the db. In this case PostgreSQL

drv <- dbDriver("PostgreSQL")

# Db connection, db parameters extracted from elephantSQL. Replace xxxx for the parameters
that elephantSQL gives you.

con <- dbConnect(drv, host="xxxx ", port="5432", dbname="xxxxxxx",

user="xxxxxx", password="xxxxxxx")

# Function to know if a table exists in the database, if output = TRUE, it exists. Replace x for the
# name of the table that you want to know if exists.

dbExistsTable(con, "xxxxxx")

#List all the tables in the database

dbListTables(con)

#List fields in a specific table. . Replace x for the name of the table that you want the fields to
#be listed.

59
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.
Protocolos Gollum, hacia una armonización de protocolos de investigación en Cetáceos.

dbListFields(con,"xxxxx ")

#Read a whole table. . Replace x for the name of the table that you want to read.

sighting<-dbReadTable(con, "xxxxx")

#---------------------------------#

#### CRUD operations ####

# Create + Read + Update + Delete #

#---------------------------------#

#Insert data in the database. dbSendQuery + SQL code. CREATE. Take same order as columns

inputCREATE=dbSendQuery(con,"INSERT INTO [Link] VALUES('05', 'Sasa', 'Orcinus

orca');")

#Get data from the database. dbGetQuery + SQL code. READ. Public is written before the table
name because the table is in the public schema.

outputREAD<-dbGetQuery(con,"SELECT*FROM [Link];")

outputREAD2<-dbGetQuery(con,"SELECT*FROM [Link] WHERE sighting_id > 1")

#Update data in the database. UPDATE. dbGetQuery + SQL code. UPDATE.

outputUPDATE<-dbGetQuery(con,"UPDATE sighting SET species ='Orcinus orca'WHERE

sighting.sighting_id=1")

#Delete data in the database. DELETE. dbGetQuery + SQL code. DELETE.

outputDELETE<-dbGetQuery(con,"DELETE FROM trips WHERE trip_id=3")

#Write table from R to database.

dbWriteTable(con, "VMSgpsdata",VMSgpsdata)

60
CIRCE, Conservación, Informaciín y Estudio sobre cetáceos.