Corso di Basi di Dati

Basi di dati
(Sistemi Informativi)
Scuola di Dottorato in Scienze Veterinarie
per la Salute Animale e la Sicurezza Alimentare
Lezione 2 B
Ing. Mauro Zaninelli – [email protected]
a.a. 2005 - 2006
1
Concetti Fondamentali




Introduzione
Creazione ed
eliminazione di bd
Creazione ed
eliminazione di
tabelle
Inserimenti di
ennuple

Interrogazioni







clausola SELECT
clausola FROM
clausola WHERE
clausola ORDER BY
metodo di scrittura
Cancellazioni
Aggiornamenti
2
Introduzione

SQL (“Structured Query Language”)



DDL (“Data Definition Language”)


creazione degli oggetti dello schema
DCL (“Data Control Language”)


linguaggio per l’interazione con il DBMS
tutte le operazioni vengono specificate in SQL
controllo degli utenti e delle autorizzazioni
DML (“Data Manipulation Language”)

manipolazione dell’istanza della base di dati
(interrogazioni e aggiornamenti)
3
Storia dello Standard

Prime implementazioni


Primi prodotti commerciali


IBM System/R 1979 (SEQUEL)
IBM SQL/DS, Oracle 1981
SQL-86

prima versione dello standard, basata sul
dialetto IBM
4
Storia dello Standard

SQL-89 (SQL-1)



vincoli di integrità
livello1 e livello2
SQL-92 (SQL-2)



entry
intermediate
full
SQL-92, full
SQL-92, intermediate
SQL-92, entry
SQL-89, livello 2
SQL-89, livello 1
5
Le Istruzioni Fondamentali

DDL



creazione della base
di dati
CREATE DATABASE
creazione delle
tabelle
CREATE TABLE
DML

inserimento delle
ennuple
INSERT INTO



interrogazioni
SELECT
eliminazione delle
ennuple
DELETE
modifica della
ennuple
UPDATE
6
Creazione ed Eliminazione di BD


Istruzioni del DDL
Sintassi


CREATE DATABASE <nome>;
DROP DATABASE <nome>;
Esempio
CREATE DATABASE universita;
DROP DATABASE universita;
7
Creazione ed Eliminazione di Tabelle

Istruzioni del DDL



CREATE TABLE
DROP TABLE
Sintassi


CREATE TABLE <nome> (<schema>);
DROP TABLE <nome>;
8
Creazione ed Eliminazione di Tabelle

Esempio: la tabella Professori
CREATE TABLE Professori (
cod char(4) PRIMARY KEY,
cognome varchar(20) NOT NULL,
nome varchar(20) NOT NULL,
qualifica char(15),
facolta char(10) );
DROP TABLE Professori;
9
Creazione ed Eliminazione di Tabelle

Esempio: la tabella Esami
CREATE TABLE Esami (
studente integer,
corso char(3),
voto integer,
lode bool,
PRIMARY KEY (studente, corso));
10
Creazione ed Eliminazione di Tabelle

<schema>



Definizione di attributo


una o più definizioni di attributo
zero o più definizioni di vincoli di tabella
<nomeattributo> <tipo> [<vincoli di
colonna>]
Definizioni di vincoli di tabella

normalmente vincoli relativi a più attributi
11
Creazione ed Eliminazione di Tabelle

<nomeattributo>


identificatore
<tipo>







INT, INTEGER
REAL, FLOAT
DECIMAL(lung,dec)
DOUBLE
PRECISION
CHAR(n),
CHARACTER(n)
VARCHAR(n)







LONG VARCHAR,
TEXT
BOOLEAN, BOOL
DATE
TIME
TIMESTAMP
BINARY(n), BIT(n)
VARBINARY(n),
VARBIT(n)
LONG VARBINARY,
BLOB
12
Creazione ed Eliminazione di Tabelle

Vincoli di colonna





PRIMARY KEY
UNIQUE
NOT NULL
REFERENCES <chiave della tabella dest.>
[ON update CASCADE]
[ON delete CASCADE]
CHECK (<espressione>)
13
Creazione ed Eliminazione di Tabelle

Vincoli di tabella (su più attributi)




PRIMARY KEY (<lista attributi>)
UNIQUE (<lista attributi>)
FOREIGN KEY (<lista attributi>)
REFERENCES <chiave esterna>
[ON update CASCADE]
[ON delete CASCADE]
CHECK (<espressione>)
14
CREATE TABLE Professori (
CREATE TABLE Tutorato (
cod char(4) PRIMARY KEY,
studente integer
cognome varchar(20) NOT NULL,
REFERENCES Studenti(matr),
nome varchar(20) NOT NULL,
tutor integer
qualifica char(15),
REFERENCES Studenti(matr),
facolta char(10) );
PRIMARY KEY (studente,tutor));
CREATE TABLE Esami (
CREATE TABLE Studenti (
studente integer
matr integer PRIMARY KEY,
REFERENCES Studenti(matr)
cognome varchar(20) NOT NULL,
ON DELETE cascade
nome varchar(20) NOT NULL,
ON UPDATE cascade,
ciclo char(20),
corso char(3)
anno integer,
REFERENCES Corsi(cod),
relatore char(4)
voto integer,
REFERENCES Professori(cod)
lode bool,
);
CHECK (voto>=18 and voto<=30),
CHECK (not lode or voto=30),
CREATE TABLE Corsi (
PRIMARY KEY (studente, corso));
cod char(3) PRIMARY KEY,
titolo varchar(20) NOT NULL, CREATE TABLE Numeri (
ciclo char(20),
professore char(4)
docente char(4)
REFERENCES Professori(cod),
REFERENCES Professori(cod)
numero char(9),
);
PRIMARY KEY (professore,numero));
15
Inserimenti

Istruzione del DML


Sintassi


INSERT
INSERT INTO <tabella> VALUES (<valori>);
Semantica


inserimento della ennupla nella tabella
corrispondenza ordinata tra valori e attributi
(notazione posizionale)
16
Inserimenti

Esempi:
INSERT INTO Professori VALUES
(‘FT’, ‘Totti’, ‘Francesco’, ‘ordinario’,
‘Ingegneria’);
INSERT INTO Studenti VALUES
(111, ‘Rossi’, ‘Mario’, ‘laurea tr.’, 3, null);
INSERT INTO Corsi VALUES
(‘PR1’, ‘Programmazione 1’, ‘laurea tr.’, ‘FT’);
17
Interrogazioni

Istruzione del DML



SELECT
sintassi concreta per specificare operatori
dell’algebra
Filosofia



parzialmente dichiarativa
si specificano gli operatori da applicare, non
l’ordine in cui devono essere applicati
l’ottimizzatore sceglie la strategia ottima
18
Interrogazioni

Tre “clausole” principali (nucleo)


SELECT


proiezioni, ridenominazioni, elimin. duplicati
FROM


SELECT, FROM, WHERE
tabelle, join, prodotti cartesiani, alias
WHERE

selezioni
19
Esempi

“Studenti della laurea triennale di anni
successivi al primo”
SELECT *
FROM Studenti
WHERE ciclo=‘laurea tr.’ AND anno>1;
20
Esempi

“Matricola e cognome degli studenti che hanno
sostenuto l’esame di informatica teorica”
SELECT matricola, cognome
FROM Studenti JOIN Esami ON matr=studente
JOIN Corsi ON cod=corso
WHERE titolo=‘Inform. t.’;
21
Clausola ORDER BY

Serve a specificare


Sintassi


ordinamenti finali
ORDER BY <attributi>
Dove

<attributi> è una lista di attributi di tabelle
che compaiono nella clausola FROM
22
Clausola ORDER BY

Esempio
SELECT *
FROM Studenti
ORDER BY cognome, annoCorso

Normalemente:


l’ordinamento è crescente
Ordinamento decrescente: DESC
ORDER BY cognome, annoCorso DESC
23
Cancellazioni

Istruzione del DML


Sintassi



DELETE
DELETE FROM <tabella>
[<clausola WHERE>];
<clausola WHERE>: identica a quella vista
Semantica

elimina dalla tabella tutte le ennuple (che
soddisfano la condizione se è specificata)
24
Cancellazioni

Esempi:
DELETE FROM Numeri;
DELETE FROM Studenti WHERE matr=111;
DELETE FROM Corsi
WHERE ciclo=‘laurea tr.’ AND
docente=‘FT’;
25
Aggiornamenti

Istruzione del DML


Sintassi


UPDATE
UPDATE <tabella> SET <attributo>=<espressione>
[<clausola WHERE>]
Semantica

aggiorna il valore dell’attributo di tutte le
ennuple (che soddisfano la condizione se è
specificata)
26
Aggiornamenti

Esempi:
UPDATE Studenti SET anno=anno+1;
UPDATE Studenti SET matr=11111
WHERE matr=111;
UPDATE Corsi SET docente=‘VC’
WHERE ciclo=‘laurea tr.’ AND
docente=‘FT’;
27
CREATE TABLE Professori (
CREATE TABLE Tutorato (
cod char(4) PRIMARY KEY,
studente integer
cognome varchar(20) NOT NULL,
REFERENCES Studenti(matr),
nome varchar(20) NOT NULL,
tutor integer
qualifica char(15),
REFERENCES Studenti(matr),
facolta char(10) );
PRIMARY KEY (studente,tutor));
CREATE TABLE Esami (
CREATE TABLE Studenti (
studente integer
matr integer PRIMARY KEY,
REFERENCES Studenti(matr)
cognome varchar(20) NOT NULL,
ON DELETE cascade
nome varchar(20) NOT NULL,
ON UPDATE cascade,
ciclo char(20),
corso char(3)
anno integer,
REFERENCES Corsi(cod),
relatore char(4)
voto integer,
REFERENCES Professori(cod)
lode bool,
);
CHECK (voto>=18 and voto<=30),
CHECK (not lode or voto=30),
CREATE TABLE Corsi (
PRIMARY KEY (studente, corso));
cod char(3) PRIMARY KEY,
titolo varchar(20) NOT NULL, CREATE TABLE Numeri (
ciclo char(20),
professore char(4)
docente char(4)
REFERENCES Professori(cod),
REFERENCES Professori(cod)
numero char(9),
);
PRIMARY KEY (professore,numero));
28
Corsi
T
codice CHAR(3)
PK
Esami
T
titolo VARCHAR(20)
Numeri
T
corso CHAR(3)
PK, FK
ciclo CHAR(20)
numero CHAR(9)
PK
studente INTEGER
PK, FK
docente CHAR(4)
docente CHAR(4)
PK, FK
FK
voto INTEGER
lode BOOL
Professori
T
cod CHAR(4)
PK
cognome VARCHAR(20)
Studenti
T
matr INTEGER
PK
cognome VARCHAR(20)
nome VARCHAR(20)
qualifica CHAR(15)
facolta CHAR(10)
nome VARCHAR(20)
ciclo CHAR(20)
anno INTEGER
relatore CHAR(4)
FK
Tutorato
T
studente INTEGER
PK, FK
tutor INTEGER
PK, FK
29
Professori
Studenti
Corsi
cod
cognome
nome
qualifica
facolta
FT
Totti
Francesco
ordinario
Ingegneria
CV
Vieri
Christian
associato
Scienze
ADP
Del Piero
Alessandro
supplente
null
matr
cognome
nome
ciclo
anno
relatore
111
Rossi
Mario
laurea tr.
1
null
222
Neri
Paolo
laurea tr.
2
null
333
Rossi
Maria
laurea tr.
1
null
444
Pinco
Palla
laurea tr.
3
FT
77777
Bruno
Pasquale
laurea sp.
1
FT
88888
Pinco
Pietro
laurea sp.
1
CV
cod
titolo
ciclo
docente
PR1
Programmazione I
laurea tr.
FT
ASD
Algoritmi e Str. Dati
laurea tr.
CV
INFT
Informatica Teorica
laurea sp.
ADP
30
Tutorato
Esami
studente
tutor
professore
numero
111
77777
FT
0971205145
222
77777
FT
347123456
333
88888
VC
0971205227
444
88888
ADP
0971205363
ADP
338123456
Numeri
studente
corso
voto
lode
111
PR1
27
false
222
ASD
30
true
111
INFT
24
false
77777
PR1
21
false
77777
ASD
20
false
88888
ASD
28
false
88888
PR1
30
false
88888
INFT
30
true
31
Termini della Licenza

Original Work BY: G. Mecca – Università della Basilicata
[email protected]

This work is licensed under the Creative Commons AttributionShareAlike License. To view a copy of this license, visit
http://creativecommons.org/licenses/by-sa/1.0/ or send a letter to
Creative Commons, 559 Nathan Abbott Way, Stanford, California
94305, USA.

Questo lavoro viene concesso in uso secondo i termini della
licenza “Attribution-ShareAlike” di Creative Commons. Per ottenere
una copia della licenza, è possibile visitare
http://creativecommons.org/licenses/by-sa/1.0/ oppure inviare una
lettera all’indirizzo Creative Commons, 559 Nathan Abbott Way,
Stanford, California 94305, USA.
32