Schemi Basi di dati

Combinazione di interrogazioni con operatori insiemistici

Gli operatori insiemistici combinano i risultati di due interrogazioni tra loro indipendenti. Esiste un secondo meccanismo per combinare query che consente di usare i risultati di una prima interrogazione per guidarne una seconda: le interrogazioni nidificate.

Esempio: estrarre il nome e il cognome dell'impiegato che guadagna lo stipendio più alto.

SELECT Nome, Cognome
FROM Impiegati
WHERE Stipendio = (SELECT MAX(Stipendio) FROM Impiegati)

In questo caso, la prima interrogazione viene utilizzata per estrarre il valore massimo dello stipendio, che viene poi utilizzato come filtro nella seconda interrogazione tramite una clausola WHERE.

La query interna di un'interrogazione nidificata non deve necessariamente restituire un solo valore. Nel caso in cui restituisca più elementi, tuttavia, è necessario specificare se l'operatore di confronto (=, <>, <, >, <=, >=) si riferisce ad almeno una (ANY) o a tutte (ALL) le righe risultanti.

Esempio: estrarre...

gli impiegati che lavorano in dipartimenti con sede a Milano

SELECT Nome, Cognome
FROM Impiegati
WHERE Dipartimento = ANY(SELECT Nome FROM Dipartimento WHERE Città ='Milano')

Estrarre gli impieganti che non lavorano in dipartimenti con sede a Milano

SELECT Nome, Cognome
FROM Impiegati
WHERE Dipartimento <> ALL (SELECT Nome FROM Dipartimento WHERE Città= 'Milano')

ANY IN. <> ALL

L'operatore ha una sintassi equivalente mediante l'operatore NOT IN.

Esempio

Estrarre i dipartimento in cui non lavorano impiegati di cognome Rossi

SELECT D.Nome
FROM Dipartimenti AS D
WHERE D.Nome NOT IN (SELECT I.Dipartimento FROM Impiegati AS I WHERE I.Cognome = 'Rossi')

Oppure

SELECT DISTINCT Nome FROM Dipartimenti
EXCEPT
SELECT DISTINCT Dipartimento FROM Impiegati WHERE Cognome = 'Rossi'

INSERT.

É possibile inserire nuove righe in una tabella mediante

consente di eliminare una o più righe di una tabella

DELETE FROM Impiegati WHERE (Nome = 'Marco') AND (Cognome = 'Franco')

DATA DEFINITION LANGUAGE

CREATE TABLE

L'istruzione definisce uno schema di relazione e ne crea un'istanza vuota. All'interno dell'istruzione devono essere specificati:

• Il nome di ogni attributo
• Il dominio di ogni attributo
• I vincoli a cui sono sottoposti gli attributi

CREATE TABLE Impiegati (Nome TEXT, Cognome TEXT, Dipartimento TEXT, Ufficio INTEGER, Stipendio REAL, Città TEXT)

I domini disponibili dipendono dal DBMS in uso e possono essere di vario tipo:

• Numerici: smallint, integer, bigint, decimal, numeric, real, double, precision, smallserial, serial, bigserial
• Testuali: character(n), char(n), varchar(n), TEXT
• Istanti temporali: timestamp, date, time, interval
• Booleani: Boolean

Oltre al tipo di un attributo è possibile specificare se questo può ammettere o meno valori nulli

CREATE TABLE Impiegati

CREATE TABLE Impiegati (
    Nome TEXT NOT NULL,
    Cognome TEXT NOT NULL,
    Dipartimento TEXT,
    Ufficio INTEGER NOT NULL,
    Stipendio REAL NOT NULL,
    Città TEXT NOT NULL,
    CONSTRAINT stipendio_positivo CHECK (Stipendio >= 0),
    PRIMARY KEY (Nome, Cognome)
)

CREATE TABLE Impiegati (
    Nome TEXT NOT NULL,
    Cognome TEXT NOT NULL,
    Dipartimento TEXT,
    Ufficio INTEGER NOT NULL,
    Stipendio REAL NOT NULL,
    Città TEXT NOT NULL,
    CONSTRAINT stipendio_positivo CHECK (Stipendio >= 0),
    PRIMARY KEY (Nome, Cognome),
    FOREIGN KEY (Dipartimento) REFERENCES Dipartimenti(Nome)
)

CREATE TABLE Impiegati (
    CodiceCliente INTEGER NOT NULL PRIMARY KEY,
    Nome TEXT NOT NULL,
    PartitaIva TEXT UNIQUE
)

Il contesto lo consente, è possibile specificare un valore di default per alcuni attributi. Questo valore sarà utilizzato qualora non ne venga specificato uno esplicito in fase di INSERT.

CREATE TABLE Impiegati (CodiceCliente INTEGER NOT NULL PRIMARY KEY, Nome TEXT NOT NULL, PuntiFedeltà INTEGER NOT NULL DEFAULT 0)

Abbiamo detto che nel modello relazionale è possibile adottare due strategie per gestire automaticamente eventuali violazioni dei vincoli di integrità referenziale. Tali violazioni possono verificarsi in caso di aggiornamento o di cancellazione di una riga contenuta in una tabella esterna.

SQL offre la possibilità di specificare, per ogni attributo e per ogni casistica, un diverso comportamento:

• ON DELETE CASCADE: le righe corrispondenti della tabella interna vengono cancellate
• ON DELETE SET NULL: gli attributi corrispondenti della tabella interna vengono impostati al valore nullo
• ON DELETE SET DEFAULT: gli attributi corrispondenti della tabella interna vengono impostati al valore di default

interna vengono impostati al loro valore di default

ON DEFAULT NO ACTION: non viene intrapresa nessuna azione e viene riportato un errore

ON UPDATE CASCADE: gli attributi corrispondenti della tabella vengono impostati al nuovo valore della chiave primaria

ON UPDATE SET NULL: gli attributi corrispondenti della tabella interna vengono impostati al valore nullo

ON UPDATE SET DEFAULT: gli attributi corrispondenti della tabella interna vengono impostati al loro valore di default

ON UPDATE NO ACTION: non viene intrapresa nessuna azione e viene riportato un errore

ALTER TABLE

Esiste la possibilità di modificare le tabelle mediante l’istruzione così come è

DROP TABLE

possibile cancellarle tramite l’istruzione

Il funzionamento di questi comandi è simile a quelli già visti, si rimanda quindi alla documentazione del proprio DBMS per ulteriori dettagli.

PROGETTAZIONE DELLE BASI DI DATI

Tramite le istruzioni SQL viste finora possiamo interrogare una base di dati.

testuale.progettazione logica

Nella l'attenzione è posta su come rappresentare i dati. In questa fase si progetta la soluzione vera e propria che adotteremo, selezionando uno specifico modello logico. Partendo dallo schema concettuale si arriva a creare uno schema logico.

progettazione fisica

Infine, nella si integra lo schema logico aggiungendo informazioni sull'organizzazione fisica dei dati (e.g. indici, partizionamento dei dati, ...).

Nella fase di progettazione concettuale si desidera produrre una rappresentazione astratta (schema concettuale) del contesto di interesse a partire dalla sua descrizione, in modo simile a quanto accade nell'analisi object-oriented.

modello Entity-Relationship,

Spesso per fare questo si adotta il che fornisce una serie di costrutti utili a modellare l'informazione: entità, relazioni, attributi, identificatori, cardinalità, generalizzazioni, ...

In questo corso adotteremo un modello concettuale equivalente a quello ER,