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 
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.
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.
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é
Se hai usato lo stesso comando drop table sulla tabella people, sarebbe riuscito:
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.