Importante

La traducción es un esfuerzo comunitario puede unirse. Esta página está actualmente traducida en |progreso de traducción|.

15.2. Lección: Implementación del modelo de datos

Ahora que hemos cubierto toda la teoría, pasemos a crear una nueva base de datos. Esta base de datos la vamos a utilizar para nuestros ejercicios en las lecciones que siguen a continuación.

El objetivo de esta lección: Instalar el software requerido y utilizarlo para implementar nuestra base de datos de ejemplo.

15.2.1. Instalar PostgreSQL

Nota

Puede encontrar paquetes de PostgreSQL e instrucciones de instalación para su sistema operativo en https://www.postgresql.org/download/. Tenga en cuenta que la documentación supondrá que los usuarios ejecutan QGIS en Ubuntu.

En Ubuntu:

sudo apt 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 online.

15.2.3. Crear un usuario de base de datos

En Ubuntu:

Después de que se complete la instalación, ejecute este comando para convertirse en usuario de postgres y luego crear un nuevo usuario de la base de datos:

sudo su - postgres

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

Ahora, en el indicador bash del usuario postgres, cree el usuario de la base de datos. Asegúrese de que el nombre de usuario coincida con su nombre de usuario de Unix: le facilitará la vida, ya que Postgres lo autenticará automáticamente cuando inicie sesión 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 debe abandonar el entorno de shell bash del usuario de 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 utiliza para crear una nueva base de datos. Debe ejecutarse desde el indicador de shell bash:

createdb address -O qgis

Puede verificar la existencia de su nueva base de datos mediante el uso de 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 la Psql cheat sheet.

15.2.7. Crear tablas en SQL

¡Comencemos a hacer algunas tablas! Usaremos nuestro Diagrama ER como guía. Primero, conéctese a la dirección db:

psql address

Then create a streets table:

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

serial and varchar are data types. serial tells PostgreSQL to start an integer sequence (auto-number) to populate the id automatically for every new record. varchar(50) tells PostgreSQL to create a character field of 50 characters in length.

You will notice that the command ends with a ; - all SQL commands should be terminated this way. When you press Enter, psql will report something like this:

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

That means your table was created successfully, with a primary key streets_pkey using streets.id.

Note: If you hit return without entering a ;, then you will get a prompt like this: address-#. This is because PostgreSQL is expecting you to enter more. Enter ; to run your command.

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

\d streets

Lo que debería 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 que debería mostrar algo como esto:

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

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

Ponte a prueba: ★★☆

Use la propuesta de arriba para hacer una tabla llamafa personas:

Agregue campos como número de teléfono, dirección, nombre, Etc. (estos no son nombres de campo válidos: cámbielos para hacerlos válidos). Asegúrese de incluir una columna ID con el mismo tipo de datos como la de arriba.

Respuesta

El SQL requerido para crear la tabla de personas correcta es:

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

El esquema de la tabla (ingrese \\d people) se ve 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)

Con fines ilustrativos, hemos omitido deliberadamente la restricción fkey.

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.

../../../_images/er-people-streets.png

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 ha sido creada, así que hagámoslo de la primera manera:

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

That tells the people table that its street_id fields must match a valid street id from the streets table.

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

Tras añadir la restricción, nuestro esquema de tabala aparece como este ahora:

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 en los nombres de las personas. Para proporcionar esto, podemos crear un índice en la columna de nombre de nuestra tabla de personas:

create index people_name_idx on people(name);

\d people

La 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

If you want to get rid of a table you can use the DROP command:

drop table streets;

En nuestro ejemplo actual, el comando anterior no funcionaría. ¿Por qué no?

Respuesta

The reason the DROP command would not work in this case is because the people table has a Foreign Key constraint to the streets table. This means that dropping (or deleting) the streets table would leave the people table with references to non-existent streets data.

It is possible to “force” the streets table to be deleted by using the CASCADE command, but this would also delete the people and any other table which had a relationship to the streets table. Use with caution!

If you used the same drop table command on the people table, it would be successful:

drop table people;

Nota

If you actually did enter that command and dropped the people table, now would be a good time to rebuild it, as you will need it in the next exercises.

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.

Bajo Ubuntu, puede instalarlo así:

sudo apt install pgadmin3

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

15.2.12. En conclusión

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

15.2.13. ¿Y ahora qué?

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