Base di Dati II

Appunti

It appears that you are using AdBlocking software. The cost of running this website is covered by advertisements. If you like it please feel free to a small amount of money to secure the future of this website.
​

Riassunto gerarchico delle nozioni del corso 2004-2005 di BASI DI DATI della Prof. Nicoletta Dessì - Università degli Studi di Cagliari, Facoltà di Scienze Matematiche Fisiche Naturali, Dipartimento di Matematica ed Informatica, Corso di Laurea in Informatica

Riferimenti: Raghu Ramakrishnan, Johannes Gehrke - Sistemi di basi di dati, McGraw-Hill

  • SQL (Structured Query Language)
    • è un linguaggio con varie funzionalità che contiene sia il DDL (Data Definiton Language) che il DML (Data Manipulation Language)
    • storia
      • 1974: prima proposta SEQUEL
      • 1981: prime implementazioni in SQL/DS e Oracle
      • 1983: standard di fatto
      • 1986, 1989, 1992: standard che lo recepiscono in parte
      • 1999: SQL99 standard attuale
    • qui verranno indicati solo gli aspetti più rilevanti del core SQL99
    • l'SQL può essere implementato con diversi livelli di adesione:
      • entry level (core SQL)
        • DDL e DML base
      • intermediate level (pacchetti): gestione avanzata di
        • data e ora
        • integrità e BD attive
        • interfaccia
        • OLAP e funzioni ad oggetti
      • full level (completo, SQL/MM): applicazioni speciali
        • data mining
        • dati spaziali
        • gestione documentale
    • tipi di dato
      • tipi predefiniti
        • numerici
          • esatti
            • rappresentano valori interi e valori decimali in virgola fissa (es: 75, -6.2):
              • INTEGER
              • SMALLINT
              • NUMERIC[(precisione, [scala])]
                • precisione: numero totale di cifre
                • scala: numero di cifre della parte frazionaria
                • esempio: NUMERIC(4,1) corrisponde ai valori compresi tra -999.9 e 999.9
              • DECIMAL[(precisione, [scala])]
                • è simile al NUMERIC ma la precisione qui specificata è un limite inferiore (può essere maggiore)
          • approssimati
            • rappresentano valori numerici approssimati con rappresentazione in virgola mobile (es: 1256E-4):
              • REAL
                • valori a singola precisione in virgola mobile
              • DOUBLE PRECISION
                • valori a doppia precisione in virgola mobile
              • FLOAT[(precisione)]
                • permette di specificare la necessaria precisione
        • binari
          • BIT[(lunghezza)]
            • rapresenta stringhe di bit della lunghezza specificata (default = 1)
          • BIT VARYING[(lunghezza)]
            • rapresenta stringhe di bit di lunghezza massima specificata
        • carattere
          • CHAR[(lunghezza)] o CHARACTER
            • rapresenta stringhe di caratteri della lunghezza specificata (default = 1)
          • VARCHAR[(lunghezza)] o CHARACTER VARYING
            • rapresenta stringhe di caratteri di lunghezza massima specificata
        • temporali
          • DATE
            • data in formato unix: yyyy-mm-dd
          • TIME
            • ora in formato unix: hh:mm:ss
          • TIMESTAMP
            • data/ora in formato unix: yyyy-mm-dd hh:mm:ss
        • booleani
          • BOOLEAN
            • rappresenta i valori booleani TRUE, FALSE, UNKNOWN
            • UNKNOWN viene introdotto per la getione dei confronti con i valori NULL
      • tipi strutturati
      • tipi user-defined
    • domini (DOMAIN)
      • elementari (predefiniti)
        • carattere: singoli caratteri o stringhe, anche di lunghezza variabile
        • bit: singoli booleani o stringhe
        • numerici: esatti ed approssimati
        • temporali: data ora ed intervalli di tempo
      • definiti dall'utente (semplici, ma riutilizzabili)
      • CREATE
        • definisce un dominio (semplice), utilizzabile in definizioni di relazioni, anche con vincoli e valori di default
        • CREATE DOMAIN <nome dominio> AS <tipo di dati>
          • [<valore di default>] [<vincoli di dominio>]
    • notazione per la sintassi
      • elementi TERMINALI
      • elementi <non terminali>
      • [termine-opzionale]
      • alternative: termine1 | termine2 | ... | termineN
    • schema (SCHEMA)
      • struttura che ragruppa tabelle ed altri costrutti:
        • CREATE SCHEMA [<nome schema>]
        • AUTHORIZATION [<nome proprietario>]
        • [<oggetti dello schema>]
    • relazione (TABLE)
      • CREATE
        • definisce uno schema di relazione creandone un'istanza vuota e specificando attributi, domini e vincoli
        • CREATE TABLE <nome-tabella> (
          • <nome colonna 1> <dominio 1> [<vincoli di colonna 1>],
          • <nome colonna 2> <dominio 2> [<vincoli di colonna 2>],
          • ...
          • <nome colonna n> <dominio n> [<vincoli di colonna n>],
          • [<vincoli di tabella>]
        • )
    • vincoli di integrità
      • sono regole che specificano delle condizioni sui valori
      • possono essere associati ad una tabella, ad un attributo, ad un dominio
      • si dividono in
        • intrarelazionali
          • NOT NULL
            • vincolo di obbligatorietà di attributi
            • si inserisce nella specifica di colonna per indicare che non può assumere valori nulli
          • UNIQUE
            • consente di definire una chiave (vincolo di chiave)
            • in una stessa relazione è possibile specificare più chiavi UNIQUE
            • garantisce che non esistano due tuple che condividono gli stessi valori non nulli per gli attributi
            • le colonne UNIQUE possono contenere valori nulli
            • si può far seguire alla specifica dell'attributo o alternativamente alla definizione della tabella UNIQUE(lista nomi colonne)
          • PRIMARY KEY
            • consente di definire una chiave (vincolo di chiave)
            • in una stessa relazione è possibile specificare una sola PRIMARY KEY
            • impone che per ogni tupla i valori degli attributi specificati siano non nulli e diversi da quelli di ogni altra tupla
            • si può far seguire alla specifica dell'attributo o alternativamente alla definizione della tabella PRIMARY KEY(lista nomi colonne)
            • le colonne dichiarate PRIMARY KEY non possono assumere valori nulli
          • CHECK
            • su attributi
              • alla specifica della colonna viene affiancata la parola chiave CHECK seguita da una condizione, cioè un predicato o una combinazione booleana di predicati (componente WHERE di una query SQL)
              • esempio
                • Mansione Char(10) CHECK (Mansione IN ('dirigente','ingegnere','tecnico','segretaria'))
              • tale condizione può contenere sotttointegrazioni che fanno riferimento ad altre tabelle, ma il vincolo viene controllato solo quando viene modificato il valore dell'attributo a cui è associato
            • su tuple
              • alla definizione di una tabella viene aggiunta la parola chiave CHECK seguita da una condizione, cioè un predicato o una combinazione booleana di predicati
              • esempio
                • CHECK (Stipendio > PremioP)
              • tale condizione può contenere sotttointegrazioni che fanno riferimento ad altre tabelle, ma il vincolo viene controllato solo quando viene modificato il valore dell'attributo a cui è associato
        • interrelazionali (chiavi esterne)
          • sintassi
            • [, FOREIGN KEY (ListaNomiColonne)
            • REFERENCES NomeTabellaRiferita [(ListaNomiColonneRiferite)]
            • [MATCH {FULL|PARTIAL|SIMPLE}]
            • [ON DELETE {NO ACTION|RESTRICT|CASCADE|SET NULL|SET DEFAULT}]
            • [ON UPDATE {NO ACTION|RESTRICT|CASCADE|SET NULL|SET DEFAULT}]
            • [, FOREIGN KEY ...]]);
          • FOREIGN KEY
            • consente di definire una chiave esterna (vincolo di chiave esterna)
            • clausola opzionale del comando CREATE TABLE che specifica la chiave esterna
          • REFERENCES
            • consente di definire una chiave esterna (vincolo di chiave esterna)
            • nel caso di chiave esterna costituita da un solo attributo si può far seguire la specifica colonna da REFERENCES NomeTabellaRiferita
          • MATCH
            • il tipo di match è significativo nel caso di chiavi esterne costituite da più di un attributo ed in presenza di valori nulli
            • SIMPLE
              • è l'opzione di default
              • il vincolo di integrità referenziale è soddisfatto se per ogni tupla della tabella referente si verifica almeno uno dei casi seguenti:
                • almeno una delle colonne della chiave esterna è NULL
                • nessuna delle colonne della chiave esterna è NULL ed esiste una tupla della tabella riferita la cui chiave coincide con i valori di tali colonne
            • FULL
              • il vincolo di integrità referenziale è soddisfatto se per ogni tupla della tabella referente si verifica almeno uno dei casi seguenti:
                • tutte le colonne della chiave esterna sono NULL
                • nessuna delle colonne della chiave esterna è NULL ed esiste una tupla della tabella riferita la cui chiave coincide con i valori di tali colonne
            • PARTIAL
              • il vincolo di integrità referenziale è soddisfatto se per ogni tupla della tabella referente i valori delle colonne non nulle della chiave esterna corrispondono ai valori di chiave di una tupla della tabella riferita
      • CONSTRAINT
        • consente di assegnare un nome ai vincoli
        • esempi:
          • Imp# Char(6) CONSTRAINT ChiaveImp PRIMARY KEY
          • CONSTRAINT StipOk CHECK (Stipendio > PremioP)
        • valutazione
          • un constraint è violato su una tupla se la condizione valutata sulla tupla da valore FALSE
          • un constraint la cui valutazione su una tupla da valore TRUE o UNKNOWN (a causa di valori nulli) non è violato
          • durante l'inserzione o la modifica di un insieme di tuple, la violazione di un vincolo per una delle tuple causa la non esecuzione dell'intero insieme di aggiornamenti
          • il programma continua la sua esecuzione anche in caso di violazione
      • integrità referenziale
        • si riferisce alle azioni da intraprendere nella tabella dipendente nel caso di modifica o cancellazione di una tupla nella tabella riferita
        • sintassi
          • ON (DELETE|UPDATE) [CASCADE|SET NULL|SET DEFAULT|NO ACTION]
        • NO ACTION
          • è l'impostazione di predefinita (default)
          • nessuna operazione viene compiuta sulla tabella dipendente
          • la cancellazione/modifica di una tupla dalla tabella riferita è eseguita solo se non esiste alcuna tupla nella tabella referente che ha come chiave esterna la chiave della tupla da cancellare/modificare
          • questa opzione viene considerato solo dopo che sono state esaminate tutte le specifiche relative all'integrità referenziale
        • RESTRICT
          • equivalente a NO ACTION con la differenza che questa opzione viene controllata subito
        • CASCADE
          • la cancellazione/modifica di una tupla dalla tabella riferita implica la cancellazione/modifica di tutte le tuple della tabella referente che hanno come chiave esterna la chiave della tupla da cancellare/modificare
        • SET NULL
          • la cancellazione/modifica di una tupla dalla tabella riferita implica che in tutte le tuple della tabella referente che hanno come chiave esterna la chiave della tupla da cancellare/modificare, la chiave esterna viene posta a valore NULL (se ammesso)
        • SET DEFAULT
          • la cancellazione/modifica di una tupla dalla tabella riferita implica la cancellazione/modifica di tutte le tuple della tabella referente che hanno come chiave esterna la chiave della tupla da cancellare/modificare, il valore della chiave viene posto uguale al valore di default specificato per le colonne che costituiscono la chiave esterna
        • in caso di più riferimenti, l'ordine in cui vengono considerate le opzioni è:
          • RESTRICT
          • CASCADE, SET NULL, SET DEFAULT
          • NO ACTION
        • nel caso di inserimento o modifica nella tabella referente non è possibile specificare alcuna opzione e viene applicata sempre NO ACTION
    • SQL - DDL: cancellazioni e modifiche
      • DROP TABLE <nome tabella>
        • cancella la relazione specificata
      • RENAME <vecchio nome tabella> to <nuovo nome tabella>
        • modifica il nome della relazione
      • ALTER TABLE <nome tabella>
        • modifica una relazione (tabella) le modifiche possibili sono:
          • ADD COLUMN <nome colonna> <caratteristiche nuova colonna>
            • aggiunge una nuova colonna ad una relazione
            • la specifica della colonna può anche contenere CONSTRAINT
          • ADD PRIMARY KEY <nome colonna>
            • aggiunge una chiave primaria
          • ADD CONSTRAINT <nome vincolo>
            • aggiunge il vincolo specificato alla relazione
          • MODIFY (<nome colonna> <caratteristiche colonna>)
            • modifica il tipo di una colonna
          • ALTER COLUMN <nome colonna>
            • modifica una colonna di una relazione
            • DROP DEFAULT
              • elimina il valore di default associato alla colonna
          • DROP COLUMN <nome colonna>
            • elimina la colonna specificata dalla relazione
          • DROP CONSTRAINT <nome vincolo>
            • rimuove il vincolo specificato dalla relazione
    • SQL: operazioni sui dati
      • SELECT
        • sintassi
          • SELECT [DISTINCT|ALL] <lista select>
          • FROM <lista from>
          • [WHERE <condizione>]
          • [ORDER BY <lista order>]
          • [GROUP BY <lista group> [HAVING <condizione>]]
        • traduce gli operatori di selezione [σ] (espressa dalla condizione WHERE) e proiezione [π] (espressa dalla condizione SELECT) dell'algebra relazionale
          • esempio
            • πa1,a2(σa1=cost(r1))
            • SELECT a1,a2 FROM r1 WHERE a1=cost;
      • definizioni
        • DISTINCT
          • elimina i duplicati dal result set
        • ALL
          • duplicati non eliminati
        • <lista select>
          • uno o più attributi di <lista form>
          • funzioni di aggregazione
            • COUNT
              • conta il numero di tuple
              • COUNT (*)
                • conta tutte le tuple
              • COUNT (DISTINCT <attr>)
                • conta le tuple eliminando i duplicati
            • AVG(<attr>)
              • calcola la media aritmetica
            • MIN(<attr>)
              • restituisce il valore minimo
            • MAX(<attr>)
              • restituisce il valore massimo
            • SUM(<attr>)
              • restituisce la somma
          • costanti
          • espressione matematica
          • il simbolo * indica tutti gli attributi
        • AS (ridenominazione [ρ])
          • consente la ridenominazione di un attributo
          • le ridenominazioni sono indispensabili se
            • due attributi hanno lo stesso nome in relazioni differenti
            • occorre considerare due volte la stessa relazione
          • i nomi di relazioni o di attributo dichiarati in alternativa si chiamano pseudonimi o alias
          • esempi:
            • SELECT p.nome, p.reddito AS entrate FROM persone p WHERE p.eta<30;
            • SELECT nome, p.n FROM persone AS p(n,c,e) WHERE p.e<30;
        • <lista from>
          • una o più tabelle o viste
          • operazioni di join tra tabelle o viste
        • <condizione>
          • espressione booleana di:
            • predicati semplici
              • operatori relazionali (<, <=, <>, >=, >)
                • <attr><operatore><costante>
                • esempio: SELECT * FROM Esame WHERE Voto>24 AND Voto<28;
              • BETWEEN [operatore di range - esprime condizioni su intervalli di valori)
                • permette di determinare le tuple che contengono in un dato attributo valori in un intervallo dato (estremi compresi)
                  • <attr> [NOT] BETWEEN <costante1> AND <costante2>
              • IN (operatore di set - ricerca valori in un insieme)
                • permette di determinare le tuple che contengono in un dato attributo, uno tra i valori in un insieme specificato
                  • <attr> [NOT] IN <costante1, costante2,..., costanteN>
                  • <attr> [NOT] IN <subquery>
                  • esempio: SELECT * FROM Esame WHERE Voto IN (28,29,30);
              • LIKE (confronto tra stringhe di caratteri)
                • permette di eseguire alcune semplici operazioni di pattern-matching su colonne di tipo stringa
                • <attr> [NOT] LIKE <pattern>
                • <pattern>
                  • è una stringa di caratteri che può contenere i caratteri speciali % e _
                  • il carattere % denota una sequenza di caratteri arbitrari di lunghezza qualsiasi (anche zero)
                  • il carattere _ denota esattamente un carattere
                • esempio: SELECT Nome FROM Impiegati WHERE Nome LIKE '__R%';
              • ANY, ALL (operatori quantificati)
                • <attr> <op-rel> [ANY|ALL] <costante1, costante2,..., costanteN>
                • <attr> <op-rel> [ANY|ALL] <subquery>
                  • confronta il valore di attributo con l'esito di una subquery
                  • ciò è corretto solo se la subquery produce un valore atomico
                • ANY
                  • equivale all'operatore IN
                  • restituisce TRUE se è vero il confronto tra l'attributo e almeno uno dei valori specificati o quelli restituiti dalla subquery
                  • esempio: SELECT Snome FROM S WHERE Matr = ANY(SELECT Matr FROM E WHERE (Cc='C1'))
                • ALL
                  • restituisce TRUE se è vero il confronto tra l'attributo e tutti i valori specificati o quelli restituiti dalla subquery
                  • esempio: SELECT * FROM S WHERE Acorso <= ALL(SELECT ACorso FROM S)
                • esempio: SELECT * FROM Esame WHERE Voto=ANY(28,29,30);
              • IS (operatore di confronto con valori NULL)
                • <attr> IS [NOT] NULL
              • EXIST (quantificatore esistenziale)
                • [NOT] EXISTS (<subquery>)
                • restituisce TRUE se e solo se l'insieme dei valori restituiti da <subquery> è non vuoto
            • predicati di join
              • esprime una relazione che deve essere verificata dalle tuple del risultato dell'interrogazione
              • si ottiene riportando:
                • le relazioni nella <lista form>
                • le condizioni di JOIN nella clausola WHERE
                • esempio
                  • SELECT Studente.Matricola, Studente.Citta, Docente.Codice
                  • FROM Studente, Docente
                  • WHERE Studente.Citta=Docente.Citta;
              • join
                • rappresenta un'importante operazione in quanto permette di correlare dati rappresentati da relazioni diverse
                • in SQL è tradizionalmente espresso tramite un prodotto cartesiano a cui sono applicati uno o più predicati di join
              • in SQL99 è un'operazione esplicita che fa uso di diversi operatori che possono essere usati nella clausola FROM:
                • JOIN (o INNER JOIN)
                  • vengono escluse le tuple di ognuna delle relazioni che non ha corrispondente nell'altra
                  • esempio: SELECT * FROM Impiegati JOIN Dipartimenti
                • CROSS JOIN
                  • corrisponde al prodotto cartesiano
                  • esempio: SELECT * FROM Impiegati CROSS JOIN Dipartimenti
                • JOIN ON
                  • corrisponde al theta-join
                  • esempio: SELECT * FROM Impiegati JOIN Dipartimenti ON Dip# > Imp#
                • NATURAL JOIN
                  • corrisponde al join naturale algebrico ma non completamente: non si esegue alcuna proiezione e lo schema risultante è quello del prodotto cartesiano
                  • viene richiesta l'uguaglianza dei valori degli attributi che hanno lo stesso nome nelle due relazioni
                  • esempio: SELECT * FROM Impiegati NATURAL JOIN Dipartimenti
                • JOIN USING (ListaNomiColonne)
                  • sintassi alternativa per il natural join
                  • viene richiesta l'uguaglianza dei valori degli attributi specificati nella clausola USING
                  • esempio: SELECT * FROM Impiegati JOIN Dipartimenti USING (Dip#)
                • OUTER JOIN
                  • considera le tuple che non intervengono nel join
                  • può essere utilizzato sia per il join natural che per il theta-join
                  • nella FROM sono esprimibili più operazioni di JOIN (join multipli)
                  • nel join di una tabella con se stessa occorre rinominare gli attributi (alias) per distinguerli
                  • ne esistono diverse varianti:
                    • FULL OUTER JOIN (o FULL JOIN)
                      • aggiunge al risultato del JOIN le tuple di entrambe le relazioni che non hanno partecipato al join, completandole con NULL
                    • LEFT OUTER JOIN (o LEFT JOIN)
                      • aggiunge al risultato del JOIN le tuple della relazione di sinistra che non hanno partecipato al join, completandole con NULL
                    • RIGHT OUTER JOIN (o RIGHT JOIN)
                      • aggiunge al risultato del JOIN le tuple della relazione di destra che non hanno partecipato al join, completandole con NULL
            • subquery
              • la clausola WHERE è formulata usando il risultato di un'altra interrogazione (subquery)
              • esempio:
                • SELECT Snome FROM S WHERE Matr=ANY(SELECT Matr FROM E WHERE (Cc='C1'))
              • sono riducibili a query semplici equivalenti le query annidate formulate con i seguenti operatori: IN, ANY, EXISTS
              • non sono riducibili a query semplici equivalenti le query annidate formulate con i seguenti operatori: NOT IN, ALL, NOT EXISTS
              • esempio di semplificazioni
                • SELECT Snome FROM S WHERE EXIST (SELECT * FROM E WHERE E.matr=S.Matr AND E.CC='C1');
                • SELECT Snome FROM E, S WHERE E.matr=S.Matr AND E.CC='C1';
        • <lista order>
          • uno o più attributi della <lista select>
          • può anche indicare la posizione dell'elemento nella <lista select>
          • DESC
            • specifica l'ordine discendente, l'ordine di default è quello ascendente
          • specifica l'ordinamento dei risultati
          • l'ordinamento non è limitato ad una sola colonna, ne ad un ordine crescente
          • esempio: SELECT Mansione, Stipendio, Nome FROM Impiegati ORDER BY Mansione, Stipendio DESC;
        • GROUP BY
          • crea sottogruppi di tuple sulla base dei valori di uno o più attributi che devono comparire anche nella select
          • i gruppi possono essere ulteriormente selezionati con la clausola HAVING
          • la select fornisce un unico record per ogni gruppo
          • in <lista select> possono comparire solo:
            • uno o più attributi specificati in <lista group>
            • funzioni aggregate che valutate forniscono un unico valore per ogni gruppo
          • esempio
            • SELECT Matr, Max(Voto), Min(Voto) FROM E WHERE CC<>'C1' GROUP BY Matr;
        • operatori insiemistici
          • eliminano i duplicati
          • UNION (unione)
            • esempio: SELECT padre AS genitore, figlio FROM paternita UNION SELECT madre AS genitore, figlio FROM maternita;
          • INTERSECT (intersezione)
          • EXCEPT (differenza)
      • prodotto cartesiano
        • si ottiene riportando le relazioni nella <lista form> senza la clausola WHERE
        • esempio
          • Studente x Esame
          • SELECT * FROM Studente, Esame;
      • vista in SQL
        • è una singola tabella che deriva da altre tabelle che possono essere tabelle base o altre viste
        • non esiste necessariamente in forma fisica ed è considerata una tabella virtuale
        • è un modo per specificare una tabella che si utilizza frequentemente, ma che non esiste fisicamente
        • si può salvare per fare altre interrogazioni
        • viene realizzata non al momento della definizione ma quando si specifica un'interrogazione su di essa
        • è sempre aggiornata dal DBMS e riflette i cambiamenti fatti sulle tabelle
        • sintassi
          • CREATE VIEW nome_vista AS SELECT (su tabelle di definizione);
            • crea la vista
          • DROP VIEW nome_vista
            • elimina la vista
​
 
​
© 1992-2024 – Nicola Asuni - Tecnick.com - All rights reserved.
about - disclaimer - privacy