15.2. Lesson: Realizzazione del modello di dati

Ora dopo aver trattato tutta la teoria, crea un nuovo database. Questo database verrà utilizzato per gli esercizi delle lezioni che seguiranno.

L’obiettivo di questa lezione: Installare il software richiesto e utilizzarlo per realizzare il database di esempio.

15.2.1. Installa PostgreSQL

Nota

Puoi trovare i package PostGreSQL e le istruzioni di installazione per il tuo sistema operativo su https://www.postgresql.org/download/. Da notare che la documentazione presuppone che gli utenti eseguano QGIS sotto Ubuntu.

Sotto Ubuntu:

sudo apt install postgresql-9.1

Dovresti avere un messaggio come questo:

[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]?

Premi S e Enter e aspetta la fine dell’installazione.

15.2.2. Guida

PostgreSQL ha un’ottima documentazione online.

15.2.3. Crea un utente del database

Sotto Ubuntu:

Al termine dell’installazione, esegui questo comando per diventare utente postgres e quindi creare un nuovo utente del database:

sudo su - postgres

Digita la normale password di accesso quando richiesto (è necessario disporre dei diritti sudo).

Ora, al prompt di bash dell’utente postgres, crea l’utente del database. Assicurati che il nome utente corrisponda al tuo nome utente unix: renderà la tua vita molto più semplice, poiché postgres ti autenticherà automaticamente quando avrai effettuato l’accesso come tale utente:

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

Immetti una password quando richiesto. È necessario utilizzare una password diversa per la password di accesso.

Cosa significano queste opzioni?

-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

Ora dovresti lasciare l’ambiente shell bash dell’utente postgre digitando:

exit

15.2.4. Verifica il nuovo account

psql -l

Dovrebbe restituire qualcosa del genere:

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)

Premi Q per uscire.

15.2.5. Crea un database

Puoi usare il comando``createdb`` per creare un nuovo database. Dovrebbe essere eseguito dal prompt della shell bash:

createdb address -O qgis

Puoi verificare l’esistenza del nuovo database utilizzando questo comando:

psql -l

Che dovrebbe tornare qualcosa di simile:

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)

Premi Q per uscire.

15.2.6. Avvio di una sessione shell del database

Puoi connetterti facilmente al tuo database in questo modo:

psql address

Per uscire dalla shell del database psql, digita:

\q

Per informazioni sull’utilizzo della shell, digita:

\?

Per informazioni sull’utilizzo dei comandi SQL, digita:

\help

Per ottenere aiuto su un comando specifico, digita (ad esempio):

\help create table

Vedi anche il Psql cheat sheet.

15.2.7. Crea tabelle in SQL

Inizia a fare alcune tabelle! Usa il diagramma ER come guida. Innanzitutto, connettiti al database address:

psql address

Then create a streets table:

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

serial e varchar sono tipi di dato. serial dice a PostgreSQL di iniziare una sequenza di interi (autoincrementale) automaticamente su id per ogni nuovo record. varchar(50) dice a PostgreSQL di creare un campo di testo lungo 50 caratteri.

Noterai che il comando termina con un ; ; - tutti i comandi SQL dovrebbero essere terminati in questo modo. Quando premi Enter, psql Invio, psql riporterà qualcosa del genere:

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

Ciò significa che la tua tabella è stata creata correttamente, con una chiave primaria streets_pkey usando streets.id.

Nota: se se non iserisci un ;, otterrai una richiesta come questa: address-#. Questo perché PG si aspetta che tu inserisca altro. Inserisci ; per eseguire il tuo comando.

Per vedere lo schema della tabella:

\d streets

Dovrebbe mostrarti qualcosa di simile:

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)

Per vedere il contenuto della tabella:

select * from streets;

Dovrebbe mostrarti qualcosa di simile:

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

Come puoi vedere, la tabella è attualmente vuota

Try Yourself moderate

Usa l’approccio mostrato sopra per creare una tabella chiamata people:

Aggiungi campi come numero di telefono, indirizzo di casa, nome, ecc. (Questi non sono tutti nomi validi: cambiali per renderli validi). Assicurati di dare alla tabella una colonna ID con lo stesso tipo di dati di cui sopra.

Verifica i risultati

15.2.8. Crea chiavi in SQL

Il problema ora è che il database non sa che people e streets hanno una relazione logica. Per esprimere questa relazione, dobbiamo definire una chiave esterna che punta alla chiave primaria della tabella streets.

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

Puoi farlo in due modi:

  • Add the key after the table has been created

  • Define the key at time of table creation

La tabella è già stata creata, quindi fallo nel primo modo:

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

Questo indica alla tabella people che il campo street_id deve corrispondere a id della tabella streets.

Il modo più usuale per creare una relazione è farlo quando crei la tabella:

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

Dopo aggiunto il vincolo lo schema della tabella appare ora così:

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. Crea indici in SQL

Vuoi ricerche veloci sui nomi delle persone. Per fare ciò, puoi creare un indice sulla colonna del nome della nostra tabella people:

create index people_name_idx on people(name);

\d people

Che risulta:

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. Elimina tabelle in SQL

Se vuoi sbarazzarti di una tabella puoi usare il comando drop:

drop table streets;

Nota

Nell’esempio corrente il comando sopra non funzionerebbe. Perché Vedi perché

drop table people;

Nota

Se hai inserito quel comando ed eliminato la tabella people, ora sarebbe il momento giusto per ricostruirlo, poiché ne avrai bisogno nei prossimi esercizi.

15.2.11. Una parola su pgAdmin III

Ti mostriamo i comandi SQL dal prompt di psql perché è un modo molto utile per conoscere i database. Tuttavia, ci sono modi più rapidi e semplici per fare molto di quello che ti stiamo mostrando. Installa pgAdmin III e puoi creare, eliminare, modificare le tabelle etc usando le operazioni in una GUI.

Sotto Ubuntu, puoi installarlo così:

sudo apt install pgadmin3

pgAdmin III sarà trattato in maggior dettaglio in un altro modulo.

15.2.12. In Conclusion

Ora hai visto come creare un nuovo database, iniziando completamente da zero.

15.2.13. What’s Next?

Adesso imparerai come usare il DBMS per aggiungere nuovi dati.