Unità di apprendimento sul linguaggio SQL e i componenti delle basi di dati

Unità di apprendimento 9: Il linguaggio SQL

Unità di apprendimento 9 Lezione 1 Il linguaggio di definizione dei dati (DDL)

Obiettivi del linguaggio DDL

• Applicare il linguaggio DDL per la creazione e la modifica di database e tabelle
• Costruire relazioni tra diverse tabelle
• Applicare e riconoscere i vincoli relazionali interni ed esterni
• Definire strutture relazionali complesse basate su vincoli di integrità referenziale

Introduzione al linguaggio SQL

SQL sta per Structured Query Language · SQL supporta la definizione e la manipolazione di dati presenti nelle basi di dati relazionali · nella maggior parte dei DBMS viene usato solo per accedere ai dati

Sottolinguaggi di SQL

Si divide in 4 sottolinguaggi:

• DDL (Data Definition Language) · comprende tutti i comandi utili alla creazione di tabelle e alla definizione dei dati in esse contenuti
• DML (Data Manipulation Language) · comprende tutti i comandi per la modifica dei dati contenuti nelle tabelle · inserimento, eliminazione, modifica, ...

DCL e QL in SQL

• DCL (Data Control Language) · comprende tutti i comandi per la gestione dei permessi di accesso alle risorse del database
• QL (Query Language) . comprende tutti i comandi che consentono l'interrogazione, il raggruppamento, il conteggio e la creazione di prospetti personalizzati dei dati presenti nelle varie tabelle dei database · per inserire una query scrivendo direttamente il codice in linguaggio SQL si deve creare una nuova query e attivare il menu "Visualizzazione SQL" Visualizza Esegui v Selezione Creazione tabella Visualizzazione Foglio dati SQL Visualizzazione SQL Visualizzazione Struttura

Formato dei comandi SQL

Ogni blocco di codice si suddivide in

• comando · è una riga di codice che termina con il punto e virgola (;)
• costrutto · rappresenta la tipologia di operazione che si vuole effettuare · es. SELECT, INSERT, CREATE, ecc ...
• clausole · sono i parametri aggiuntivi di un costrutto · es. FROM, WHERE, IN, ON, ecc ...

Standard di leggibilità dei comandi SQL

Sebbene il linguaggio sia case unsensitive, lo standard ritiene più leggibile un codice con clausole e costrutti digitati in maiuscolo e il resto della stringa in minuscolo SELECT nome, cognome, telefono FROM Modelle WHERE altezza>175 and peso<55; oppure anche su più righe: INSERT INTO tmp (a,b) SELECT cognome, nome FROM Alunni;I

Componenti delle basi di dati

componenti delle basi di dati sono:

• lo schema o struttura · i campi che costituiscono la tabella
• l'istanza · il record della tabella
• la tupla · la riga della tabella risultato di un'interrogazione

Definizione delle tabelle in SQL

Una tabella in SQL è formata da:

• attributi
• domini
• vincoli intrarelazionali · opzionali
• La sua sintassi è: CREATE TABLE NomeTabella( NomeAttributo Dominio [Vincoli] NomeAttributo Dominio [Vincoli] [AltriVincoli] )

Sintassi per la creazione di una tabella

CREATE TABLE nome tabella ( {nome_campo tipo [ ( lunghezza) ] [ NOT NULL] [ PRIMARY KEY] [UNIQUE]}[, . ] [ PRIMARY KEY ( nome campo, . . . ) ] [UNIQUE [ nome indice] ( { nome campo} [ . . . . ] ) ] [ CONSTRAINT [ nome vincolo] REFERENCES nome tabella( nome campo) ] Esempio: CREATE TABLE Alunni ( cognome CHAR (20) , nome CHAR (20) , matricola INT, telefono CHAR (12) , data nascita DATE NOT NULL id classe INT) ;

Definizione di una nuova tabella e significato degli elementi SQL

Istruzione per la creazione della tabella CREATE TABLE Alunni Nome della tabella ( matricola CHAR (4) PRIMARY KEY L Vincoli relazionali nome CHAR (40), Nomi degli attributi "indirizzo CHAR (80)o Domini, cioè insieme dei valori ammessi per il corrispondente attributo

Tipi di campo in SQL

BIT un bit memorizzato in 1 byte 0 (false) OPPURE 1 (true) TINYINT intero ridotto da 0 a 255 SMALLINT intero INTERI da -32.768 a 32.767 MONEY intero lungo 8 Byte ±922.337.203.685.477,5808 INTEGER intero lungo da-2.147.483.648 a2.147.483.647 FLOAT reale a doppia precisione (8 Byte) da -1,79308 a -4,94-324 REAL reale a singola precisione (4 Byte) REALI da -3,438 a -1,4-45 DECIMAL reale a precisione variabile (17 Byte) CHAR stringa a lunghezza fissa da 0 a 255 caratteri STRINGA TEXT campo testo a lunghezza fissa da 0 a 2,14 GigaByte DATE data Una data in formato gg/mm/aaaa TIME ora Un orario in formato hh:mm:ss IMAGE oggetti (OLE - Object Linked and Embedded - Microsoft) OGGETTI Da 0 a 2,14 GigaByte AUTOINCREMENT Numerico autoincrementante DATA

Domini: tipi numerici

I tipi numerici sono:

• numeric o decimal
• integer
• smallint
• real
• double precision
• float
• La sintassi permette di specificare · precisione (numero totale di cifre, da 1 a 38) · scala (numero di cifre a destra del separatore decimale)

Domini: tipi temporali

I tipi numerici permettono di gestire date e ore:

• date · rappresenta le date espresse come AAAA-MM-GG
• time · rappresenta il tempo espresso ora (2 cifre), minuti (2 cifre), secondi (2 cifre) e centesimi di secondo (2 cifre)
• timestamp · rappresenta una concatenazione dei due tipi precedenti · AAAA-MM-GG:ore:minuti:secondi:microsecondi

Domini: tipi carattere

tipi character vengono usati per I rappresentare sequenze di caratteri

• character o char [(lunghezza)] · permette di definire stringhe di caratteri di lunghezza tra 1 e 2000 caratteri · se si immette un dato di lunghezza inferiore a quella indicata, si usa il blank padding
• riempimento automatico con spazi bianchi
• character varying o varchar [(lunghezza)] · permette di definire stringhe di caratteri di lunghezza tra 1 e 4000 caratteri · se si immette un dato di lunghezza inferiore a quella indicata, non si usa il blank padding

Domini: tipo booleano

Il dominio boolean permette di rappresentare valori di verità true/false · generalmente occupa un solo bit · in Access si indica con il tipo bit

Esempio: Creazione tabelle di un database scolastico

CREATE TABLE Materie ID materia INT NOT NULL materia CHAR(20) NOT NULL ) CREATE TABLE Voti ( data date NOT NULL id materia INT NOT NULL id alunno INT NOT NULL voto INT NOT NULL, ID voto INT NOT NULL); CREATE TABLE Docenti ID docente INT NOT NULL cognome CHAR(25) NOT NULL nome CHAR (25) NOT NULL telefono CHAR(12) );

Vincoli intrarelazionali

I vincoli intrarelazionali verificano l'input dei dati di una tabella · se i dati non corrispondono allo schema di immissione non vengono accettati . Sono chiamati anche constraints (costrizioni) · sono condizioni che devono essere verificate da ogni istanza della base di dati · Coinvolgono una sola relazione

Principali vincoli intrarelazionali

• NOT NULL · l'attributo non può essere lasciato vuoto
• UNIQUE · permette di definire chiavi candidate · può essere definito su un solo campo, indicando dopo di esso questa parola chiave · per definire più campi dobbiamo indicarlo alla fine della definizione degli attributi
• PRIMARY KEY · indica una chiave primaria univoca per tutta la tabella, che deve sempre essere di tipo NOT NULL

Esempio di vincoli intrarelazionali

• Esempio · nella tabella Alunni creata in precedenza si vuole evitare che due alunni possano avere lo stesso codice identificativo · vincolando a UNIQUE il campo matricola questo non può accadere CREATE TABLE Alunni matricola INT UNIQUE, ( cognome CHAR(20), nome CHAR(20), telefono CHAR(12), data nascita DATE NOT NULL)

Vincoli intrarelazionali su più chiavi

• Esempio · per creare un vincolo su più chiavi, la clausola UNIQUE va indicata alla fine della tabella nome CHAR(20) NOT NULL, cognome CHAR(20) NOT NULL, UNIQUE (nome, cognome) nome CHAR(20) NOT NULL UNIQUE, cognome CHAR(20) NOT NULL UNIQUE . Nel primo ca caso si crea un indice unico per la coppia cognome+nome · più alunni possono avere lo stesso cognome oppure lo stesso nome ma non entrambi · Nel secondo caso i due campi sono entrambi unici · non si possono avere due alunni con lo stesso nome oppure con lo stesso cognome

Vincolo di CHECK

Permette di esprimere un vincolo che viene valutato ogni volta che si inseriscono o modificano dei dati · in caso di violazione dei vincoli, viene mostrato un messaggio di errore CREATE TABLE Studenti ( Matricola smallint CHECK(Matricola > 0), Nome character (20) NOT NULL UNIQUE, Cognome character (30) NOT NULL UNIQUE, Eta smallint CHECK( (Eta >= 18) AND (Eta <= 70) ) ) . Non è implementato in Access

Vincoli interrelazionali

Permettono di creare un legame permanente tra tabelle diverse attraverso due dati presenti nelle rispettive tabelle · chiamati chiave primaria (PRIMARY KEY) e chiave esterna (FOREIGN KEY) · Esempio · vogliamo mettere in relazione le tabelle Voti e Alunni in modo tale che ad ogni record della tabella Voti corrisponda un allievo della tabella Alunni · si deve usare un vincolo interrelazionale che permette di verificare che la tabella Alunni possieda effettivamente quel particolare alunno • vincolo di integrità referenziale

Integrità referenziale

Per integrità referenziale si intende l'insieme di vincoli interrelazionali utili a verificare che le relazioni tra record di tabelle correlate siano valide e che i dati collegati tra loro da vincoli vengono eliminati o modificati per sbaglio

Esempio di vincoli interrelazionali

CREATE TABLE Mansioni (nome CHAR(20) NOT NULL, tariffa oraria INT) ; CREATE TABLE Dipendenti ( matricola CHAR(6) PRIMARY KEY, nome CHAR(20) NOT NULL, cognome CHAR(20) NOT NULL, mansione INT NOT NULL CONSTRAINT m REFERENCES Mansioni (ID_mansione) , stipendio NUMERIC) ) ; Mansioni Dipendenti ID_mansione nome tariffa_oraria ID matricola nome cognome ₼ ensione stipe indio

Esempio di vincoli interrelazionali con Debiti e Materie

Debiti Materie Studente CodMateria Voto Codice Nome 5245 1121 4 1121 Latino 5245 1213 5 1213 Fisica 6565 1213 4 1455 Matematica CREATE TABLE Debiti ( CodMateria integer (4) REFERENCES MATERIE (CODICE) Studente varchar (4) , PRIMARY KEY (Studente, CodMateria)

Esercizio: Definizione tabelle per campionato di calcio

Definisci in SQL le istruzioni per la creazione delle tabelle corrispondenti allo schema rela- zionale descritto di seguito, riguardante i giocatori del campionato di calcio e la classifica marcatori, definendo i vincoli di tupla e di dominio ritenuti opportuni ed esprimendo even- tuali vincoli di integrità referenziale relativi a tutte le tabelle dello schema. Calciatori(CodiceFICG(pk), cognome, nome, squadra, nazione, ruolo) ClassificaMarcatori(Codice(pk), posizione, giocatore(fk), goal_fatti, rigori)