15.2. Lesson: Implementando el Modelo de Datos¶

15.2.1. Instalar PostgreSQL¶

En Ubuntu:

sudo apt-get install postgresql-9.1

Deberá recibir un mensaje como este:

[sudo] password for qgis:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
postgresql-client-9.1 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-9.1
The following NEW packages will be installed:
postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
0 upgraded, 4 newly installed, 0 to remove and 5 not upgraded.
Need to get 5,012kB of archives.
After this operation, 19.0MB of additional disk space will be used.
Do you want to continue [Y/n]?

Presione Y y Enter y espere a que la descarga e instalación termine.

15.2.2. Ayuda¶

PostgreSQL tiene muy buena documentación en línea.

15.2.3. Crear un usuario de base de datos¶

En Ubuntu:

Cuando la instalación se complete, ejecute este comando para ingresar como el usuario postgres y poder crear un nuevo usuario para la base de datos:

sudo su - postgres

Escriba su contraseña normal cuando se pida (necesita tener privilegios de sudo).

Ahora, desde el indicador de usuario postgres, cree el usuario de la base de datos. Asegúrese de que el nombre de usuario coincide con su nombre de usuario unix: esto hará su tarea más fácil, ya que postgres lo autenticará automáticamente cuando acceda como ese usuario:

createuser -d -E -i -l -P -r -s qgis

Ingrese la contraseña cuando se pida. Debería usar una contraseña diferente a la contraseña de acceso.

¿Qué significan estas opciones?

-d, --createdb     role can create new databases
-E, --encrypted    encrypt stored password
-i, --inherit      role inherits privileges of roles it is a member of (default)
-l, --login        role can login (default)
-P, --pwprompt     assign a password to new role
-r, --createrole   role can create new roles
-s, --superuser    role will be superuser

Ahora podrá dejar el entorno de ejecución del usuario postgres escribiendo:

exit

15.2.4. Verifique la nueva cuenta¶

psql -l

Debería mostrar algo como:

Name      |  Owner   | Encoding | Collation  |   Ctype    |
----------+----------+----------+------------+------------+
postgres  | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template1 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
(3 rows)

Escriba q para salir.

15.2.5. Crear una base de datos¶

El comando createdb se usa para crear una nueva base de datos. Debe ser ejecutado desde el intérprete de comandos:

createdb address -O qgis

Puede verificar la existencia de su nueva base de datos usando este comando:

psql -l

El cual mostrará algo como esto:

Name      |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges
----------+----------+----------+------------+------------+-----------------------
address   | qgis     | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
postgres  | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
template1 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
(4 rows)

Escriba q para salir.

15.2.6. Iniciando una sesión en un entorno de base de datos¶

Puede conectarse a su base de datos tan fácilmente como:

psql address

Para salir del entorno psql de la base de datos, escriba:

\q

Para obtener ayuda de cómo usar el entorno, escriba:

\?

Para obtener ayuda de cómo usar comandos sql, escriba:

\help

Para obtener ayuda de un comando específico, escriba (por ejemplo):

\help create table

Vea también Psql cheat sheet - disponible en línea aquí.

15.2.7. Crear tablas en SQL¶

¡Comencemos creando algunas tablas! Usaremos nuestro Diagrama ER como guía. Primero conecte a la bd direcciones:

psql address

Luego cree la tabla streets:

create table streets (id serial not null primary key, name varchar(50));

serial y varchar son tipos de datos. serial indica a PostgreSQL que comience una secuencia de entero (auto-numerada) para introducir el id automáticamente en cada nuevo registro. varchar(50) le indica a PostgreSQL crear un campo de caracter con un ancho de 50 caracteres.

Habrá notado que los comandos terminan con ; - todos los comandos SQL deben terminarse de este modo. Cuando presione enter, psql mostrará algo como esto:

NOTICE:  CREATE TABLE will create implicit sequence "streets_id_seq" for
         serial column "streets.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "streets_pkey"
         for table "streets"
CREATE TABLE

Eso significa que su tabla fue creada satisfactoriamente, con la clave primaria calles_pkey using calles.id.

Nota: Si presiona enter sin haber escrito el ;, obtendrá unos símbolos como address-#. Esto es porque PG está esperando que introduzca más información. Ingrese ; para ejecutar su comando.

Para ver el esquema de su tabla, puede hacer esto:

\d streets

Lo cual le mostrará algo como esto:

Table "public.streets"
 Column |         Type          |            Modifiers
--------+-----------------------+--------------------------------------
 id     | integer               | not null default
        |                       | nextval('streets_id_seq'::regclass)
 name   | character varying(50) |
Indexes:
  "streets_pkey" PRIMARY KEY, btree (id)

Para ver el contenido de su tabla, puede hacer esto:

select * from streets;

Lo cual le mostrará algo como esto:

id | name
---+------
(0 rows)

Como puede ver, nuestra tabla actualmente está vacía.

15.2.8. Crear Claves en SQL¶

El problema con nuestra solución de arriba, es que la base de datos no sabe que las tablas personas y calles tienen una relación lógica. Para indicar esta relación, necesitamos definir una clave externa que apunte a la Clave Primaria de la tabla calles.

Hay dos maneras de hacer esto:

• Agregar la clave después de que la tabla fue creada

• Definir la clave al tiempo de crear la tabla

Nuestra tabla ya está creada, así que hagámoslo de la primer manera:

alter table people
  add constraint people_streets_fk foreign key (street_id) references streets(id);

Esto indica que el campo street_id de la tabla people deberá coincidir con con un id válido de la tabla streets.

El modo más usual de crear una restricción es hacerlo cuando se está creando la tabla:

create table people (id serial not null primary key,
                     name varchar(50),
                     house_no int not null,
                     street_id int references streets(id) not null,
                     phone_no varchar null);

  \d people

Después de agregar una restricción, el esquema de nuestra tabla se verá así:

Table "public.people"

  Column   |         Type          |            Modifiers
-----------+-----------------------+---------------------------------
 id        | integer               | not null default
           |                       | nextval('people_id_seq'::regclass)
 name      | character varying(50) |
 house_no  | integer               | not null
 street_id | integer               | not null
 phone_no  | character varying     |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
  "people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)

15.2.9. Crear Indices en SQL¶

Queremos búsquedas rápidas relámpago en nombres de personas. Para preparar para esto, podemos crear un índice en la columna nombre de nuestra tabla personas:

create index people_name_idx on people(name);

\d people

Lo cual resulta en:

Table "public.people"

  Column   |         Type          |                      Modifiers
-----------+-----------------------+-----------------------------------
 id        | integer               | not null default nextval
           |                       | ('people_id_seq'::regclass)
 name      | character varying(50) |
 house_no  | integer               | not null
 street_id | integer               | not null
 phone_no  | character varying     |
Indexes:
 "people_pkey" PRIMARY KEY, btree (id)
 "people_name_idx" btree (name)    <-- new index added!
Foreign-key constraints:
 "people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)

15.2.10. Eliminando Tablas en SQL¶

Si desea deshacerse de una tabla puede usar el comando drop:

drop table streets;

Si usa el mismo comando drop table en la tabla people, sería exitosa:

drop table people;

15.2.11. Una palabra sobre pgAdmin III¶

Hemos estado mostrando los comandos SQL desde la consola psql porque es un modo muy útil para aprender sobre bases de datos. Sin embargo, existen modos más rápidos y fáciles para hacer mucho de lo que hemos estado mostrando. Instale pgAdmin III y podrá crear, eliminar, modificar, Etc. tablas usando operaciones apuntar y hacer click en una GUI.

En Ubuntu, puede instalarla como aquí:

sudo apt-get install pgadmin3

pgAdmin III se tratará con más detalle en otro módulo.

15.2.12. In Conclusion¶

Ha visto hasta ahora como crear una nueva base de datos empezando completamente desde cero.

15.2.13. What’s Next?¶

A continuación verá cómo usar el DBMS para agregar datos nuevos.