Database e SQL

Sintesi di progettazione Db in sql – a cura di Alessandro Pollio

DEFINIZIONI

DATABASE: insieme di informazioni, di dati che vengono suddivisi per argomenti in ordine logico (tabelle) e poi tali argomenti vengono suddivisi per categorie (campi).

OBIETTIVI DI UN DB: CONSISTENZA(i dati devono essere significativi) SICUREZZA (impedire che venga danneggiato da interventi accidentali o non autorizzati) INTEGRITA’ (garantire che le operazioni effettuate da utenti autorizzati non provochino una perdita di consistenza ai dati)

DIFETTI DI UN DB (da evitare): RIDONDANZA (i dati compaiono in maniera duplicata) INCONGRUENZA (un dato viene aggiornato in un archivio e non in un altro, oppure sono presenti valori diversi per lo stesso dato) INCONSISTENZA (i dati aziendali non sono più affidabili)

DBMS (Data Base Management System) indica il software utilizzato per la gestione di un database (nel nostro caso access2007)

MODELLO E/R (Entità/Relazione): strumento per analizzare le relazioni tra le entità

ENTITA’ (TABELLA):  organizzazione e raggruppamento di dati in base ad una caratteristica o ad un principio comune

ATTRIBUTO (CAMPO): unità di base per l’immissione dei dati in un record

INFORMAZIONE (RECORD): insieme di campi che definiscono un dato

CHIAVE PRIMARIA: campo (o insieme di campi) che non ammette duplicati, permettendo di distinguere un record da un altro

CHIAVE ESTERNA: un vincolo che serve a creare relazioni tra due tabelle

NORMALIZZAZIONE: procedimento volto all’eliminazione della ridondanza e del rischio di incoerenza dal database

PRIMA FORMA NORMALE (1FN): non presenta campi che contengono informazioni multiple (per esempio NOME_COGNOME)  e c’è una chiave primaria

SECONDA FORMA NORMALE (2FN): è in prima forma normale e non ci sono campi che dipendono solo da una parte di un’eventuale chiave primaria multipla

TERZA FORMA NORMALE (3FN): è in seconda forma normale e non ci sono campi che dipendono transitivamente dalla chiave primaria

ESEMPIO FORME NORMALI: tabella PERSONE (Nome_Cognome (PK), Luogo_nascita (PK), Regione_nascita, Stato_nascita). Per renderla in 1FN dobbiamo separare il campo Nome_Cognome in due campi diversi, ovvero la tabella diventa (Nome (PK), Cognome (PK), Luogo_nascita (PK), Regione_nascita, Stato_nascita). Ma la regione di nascita dipende solo dal luogo di nascita e non da nome e cognome. Vi è quindi una dipendenza parziale dalla chiave e la tabella non è in 2FN.

Inoltre lo stato di nascita dipende dalla regione di nascita che a sua volta dipende dal comune. Vi è dunque anche una dipendenza transitiva dalla chiave, quindi non è in 3FN.

Si potrebbe aggiungere un campo tipo codice fiscale che individui univocamente ogni persona; questo campo diventerebbe chiave unica e la tabella sarebbe allora in 2FN. Anche così però la tabella non potrebbe dirsi in 3FN anzi si aggiungerebbe un’ulteriore transitività: lo stato dipende dalla regione che dipende dal comune di nascita che dipende dal codice fiscale! Per rimediare basta suddividere la tabella in più tabelle;  Tabella Persone con campi (Codice_Fiscale (PK), Nome, Cognome, Codice_Luogo_Nascita); Tabella Luoghi con campi (Codice_Luogo_Nascita, Nome, Codice_Regione); Tabella Regioni con campi (Codice_Regione, Nome, Stato).

SQL: sta per Structured Query Language, linguaggio strutturato di interrogazione, ed è il più importante e diffuso tra i linguaggi per la gestione di database.

 

REGOLE PRATICHE DI REALIZZAZIONE DI UN DB

Se non si riesce a trovare una PK adatta risolvere con un ID char(5)

La PK non può essere assegnata ai campi MEMO o IMAGE

Ricordarsi che il campo DATE può includere, oltre alla data, anche le ore, minuti e secondi, quindi spesso viene utilizzato in una chiave primaria multipla

Relazione 1-1 (uno a uno): si uniscono le due tabelle in un’unica tabella

Relazione 1-∞ (uno a molti): si copia la chiave primaria della tabella “1” nella tabella “∞” e si definisce come FK

Relazione ∞-∞ (molti-molti): si crea un’altra tabella (tabella ausiliaria o tabella di mezzo) dove si copiano le chiavi primarie delle altre due tabelle che si definiscono come FK

 

SQL

Tutti i linguaggi di programmazione vengono letti dal pc come se fosse un’unica riga, quindi si scrive mandando a capo solamente per rendere più leggibile il codice

 

CREAZIONE TABELLE

Tipi standard:

CHAR(n) caratteri (alfanumerici) dimensione fissa fino a un massimo di n caratteri (ove n è tra 1 e 255)

VARCHAR(n) come il char ma di lunghezza variabile

DATE nel formato GG/MM/AA e HH.MM per l’inserimento di data e ora

INTEGER per i numeri interi

NUMBER per i numeri decimali

BIT sì/no, segno di spunta

MONEY per la valuta

MEMO testo lungo, oltre i 255 caratteri

IMAGE per inserire immagini o in generale allegati

COUNTER contatore, numero progressivo che viene assegnato ad ogni record

Clausole

NOT NULL: indica che il campo è obbligatorio, va sempre inserito un valore.

DEFAULT: indica il valore che sarà inserito in maniera predefinita in tutti i record

 

I nomi dei campi e delle tabelle non ammettono spazi, utilizzare l’underscore “_” o le parentesi quadre

Esempio: Data_di_nascita oppure [data di nascita]

 

p.e.

CREATE TABLE Dipendenti

(Matricola       char (5),

Cognome        char (20),

Nome              char (30),

Cod_Fisc        char (16) not null,

Data_assunzione date,

Assunto          bit,

Filiale              integer,

Stip_Base       money,

Via                  char (25),

Cap                 char (5),

Città                char (20) default (“Taranto”),

Prov                char (2) default (“TA”),

Primary key (matricola) )

 

CREATE TABLE Contratti

(Matricola       char (5),

data_inizio      date,

data_fine        date,

tipo_contratto char (20),

primary key (matricola, data_inizio),

foreign key (matricola) references dipendenti (matricola))

 

Nel caso di foreign key derivanti da chiavi primarie multiple bisogna usare la seguente sintassi:

foreign key (pk1,pk2) references tabella (pk1,pk2)

 

QUERY

Le query servono a ricercare dati all’interno di un DB

 

REGOLE PRATICHE QUERY

Cercare un dato testuale preciso: campo = “dato”

Cercare un dato numerico preciso: campo = dato

Ricerca di più dati in contemporanea utilizzare AND

Tutti i dati che iniziano o finiscono con una determinata lettera: campo = like “lettera*” “*lettera”

Ricerca su un campo di tipo bit: campo = Yes oppure campo = No

Per escludere un valore: campo <> valore

Per trovare dove manca un valore: campo Is Null

Per parametrizzare, ovvero rendere la ricerca generica in modo che appaia una finestra di input dove mettere il valore ricercato di volta in volta: campo = [inserire il valore ricercato]

Per ricercare nelle date ci sono vari metodi; ricerca di una data precisa: campo = #20/05/1973#; ricerca di un determinato anno: campo like “*/*/anno”; ricerca di un intervallo di date: campo>= #datainizio# and campo<= #datafine#; ricerca di un intervallo di date con la parametrizzazione: campo>=[inserire data inizio] and campo<=[inserire data fine]

Per ricercare un dato all’interno di qualsiasi parte di un campo: campo Like “*” & “dato” & “*”

Stessa ricerca ma parametrizzando: campo Like “*” & [inserire parte del dato] & “*”

Mettere in ordine i risultati della ricerca ORDER BY campo ASC oppure DESC (crescente o decrescente)

Raggruppare i dati a seconda di un campo: GROUP BY campo

Quando si utilizza il raggruppamento di solito viene chiesta qualche funzione, ovvero conteggio COUNT, media AVG, somma SUM, minimo MIN, massimo MAX; accanto alla funzione si può mettere AS che serve a dare un nome al campo che conterrà il risultato di quella funzione.

Per utilizzare le funzioni con record che corrispondono a determinati criteri non si utilizza il WHERE ma il comando HAVING.

Nelle query dove viene chiesta una funzione nel SELECT avremo solo i campi che compaiono nella funzione o nel GROUP BY.

 

Cercare di utilizzare una sola tabella per la ricerca, se non è possibile solo per pochi campi copiarli nella tabella di riferimento

p.e.

utilizzando le tabelle precedenti trovare tutti i contratti di un determinato dipendente visualizzando il relativo codice fiscale.

Tutti i dati che ci servono si trovano nella tabella Contratti, tranne il codice fiscale che si trova nella tabella dipendenti. Basterà copiare il campo codice fiscale nella tabella Contratti e effettuare la query su quella tabella.

 

 

 

SELECT * serve a selezionare tutti i campi presenti in quella tabella

 

Trovare i dipendenti con cognome Pollicus

SELECT Cognome, Nome, Codfisc

FROM Dipendenti

WHERE Cognome = “Pollicus”;

 

Trovare i dipendenti con stipendio 1000€

SELECT *

FROM Dipendenti

WHERE Stip_Base = 1000

 

Trovare il dipendente Alessandro Del Piero

SELECT *

FROM Dipendenti

WHERE Cognome = “Del Piero” AND Nome = “Alessandro”

 

Trovare I dipendenti il cui cognome inizia con la D

SELECT *

FROM Dipendenti

WHERE Cognome Like “D*”

 

Trovare I dipendenti assunti

SELECT *

FROM Dipendenti

WHERE assunto = YES

 

Trovare I dipendenti il cui cognome non è Pollicus

SELECT *

FROM Dipendenti

WHERE Cognome <> “Pollicus”

 

Trovare I dipendenti assunti dopo il 20/05/2000

SELECT *

FROM Dipendenti

WHERE data_assunzione >= #20/05/2000#

 

Trovare i dipendenti il cui cognome contenga parte del testo inserito

SELECT *

FROM Dipendenti

WHERE Cognome Like “*” & [inserire parte del cognome] & “*”

 

 

Calcolo di espressioni, p.e.

Calcolare lo stipendio nuovo moltiplicandolo per 1.05

SELECT Cognome, Nome, StipBase AS Stip_Attuale, StipBase*1.05 AS Stip_Nuovo

FROM Dipendenti

 

Utilizzo avanzato delle funzioni e del raggruppamento

 

Nome_progetto	Ore_uomo	Ambito
fax	400	Comunicazioni
mail	700	Comunicazioni
contabilità	3500	Gestionale
stipendi	7000	Gestionale
SMTP	400	Comunicazioni
POP	7000	Comunicazioni
inventario	10500	Gestionale

 

Raggruppare I dati per ambito conteggiandoli e calcolandone la media

 

SELECT Ambito, Count(*) As TOT, AVG(Ore_uomo) AS MEDIA

FROM Progetti

GROUP BY Ambito

Da come risultato:

 

Ambito	TOT	MEDIA
Comunicazioni	4	2125
Gestionale	3	7000

 

Raggruppare i dati per Ore_Uomo conteggiando solo quelli che risultano per più di una volta

 

SELECT Count(*) As NUM, Ore_uomo

FROM Progetti

GROUP BY Ore_uomo

HAVING Count(*)>1

 

Da come risultato:

NUM	Ore_uomo
2	400
2	7000

 

Quando ci sono più tabelle con campi con lo stesso nome, allora ogni campo dovrà essere scritto nella forma TABELLA.CAMPO

Se le tabelle sono legate da una relazione uno a molti i due campi coinvolti saranno una CHIAVE PRIMARIA ed una CHIAVE ESTERNA (se proprio non riusciamo ad utilizzare l’espediente spiegato in precedenza)

Una selezione da più tabelle scegliendo di mantenere solo i record che contengono gli stessi dati in comune fra due campi di due tabelle è un INNER-JOIN.

p.e.

SELECT *

FROM Dipendenti, Stipendio

WHERE Dipendenti.Cognome = Stipendio.Cognome

AND  Dipendenti.Età > 30;

 

Utilizzando le tabelle precedenti trovare tutti i contratti di un determinato dipendente visualizzando il relativo codice fiscale.

Come spiegato in precedenza basterebbe usare l’espediente per facilitare la scrittura di questa query, qui sotto la versione con l’INNER JOIN

 

SELECT Contratti.*, Dipendenti.Cod_Fisc

FROM Dipendenti INNER JOIN Contratti

ON Dipendenti.Matricola = Contratti.Matricola

WHERE Contratti.Matricola = [inserire la matricola del dipendente]

 

Altre clausole:

 

SELECT DISTINCT campo

FROM nometabella;

Serve a mostrare tutti i dati differenti presenti nella tabella (senza duplicati)

 

SELECT DISTINCT campoesistente AS nuovonomecampo

FROM nometabella;

Come precedente, ma in più da un’intestazione di colonna al risultato ottenuto.

 

ALTER TABLE nometabella

ADD nuovocampo tipo;

 

ALTER TABLE nometabella

DROP campoesistente;

 

Il comando ADD serve ad aggiungere un nuovo campo, DROP ad eliminarne uno già esistente.

 

Creare un indice, formato da vari campi, che non ammette duplicati, p.e.

CREATE UNIQUE INDEX nomeindice

ON tabella (campo1, campo2, …);

p.e.

CREATE UNIQUE INDEX Individuo

ON Dipendenti (Cognome, Nome);

fa in modo che non ci siano duplicati nell’indice Individuo, cioè persone con lo stesso nome e cognome.

 

Eliminare una tabella o un indice:

DROP TABLE nometabella;

DROP INDEX Individuo ON Dipendenti;

 

Inserire dati in una tabella

INSERT INTO nometabella

(campo1, campo2)

VALUES (“valore1”, “valore2”);

p.e.

INSERT INTO Dipendenti

(Matricola, Cognome, Nome)

VALUES (“200573”, “Pollio”, “Alessandro”);

 

 

 

Aggiornare i dati già presenti in una tabella

UPDATE nometabella

SET campo1 = “valore1”

WHERE campo2 = “valore2”;

p.e.

UPDATE Dipendenti

SET Cognome = Pollicus

WHERE Matricola = “200573”;

 

Cancellare da una tabella i record che corrispondono ad un determinato criterio

DELETE FROM nometabella

WHERE campo = “valore”;

 

Se dai dati ottenuti voglio creare un’ulteriore tabella:

SELECT campi INTO nuovatabella…

 

Se voglio aggiunger righe di una tabella in un’altra:

INSERT INTO nuovatabella

SELECT *

FROM altratabella…