Appunti Esercitazioni (EsercitazioniLAboratorio[1])

Esercitazioni in Laboratorio
Questo file riassume le principali attività svolte durante le prime tre esercitazioni svolte in laboratorio,
relative alla creazione di uno schema relazionale in ACCESS
•
Creazione di un Database ACCESS
Aspetti principali e Note
•
•
Prima di tutto, nei PC del Laboratorio, si consiglia di creare il Database (ad esempio
pippo15102004.mdb) sul Desktop del computer, oppure in altre directory di lavoro, e non nella
directory “consigliata” da ACCESS, in quanto tale directory non è direttamente accessibile da
Windows e quindi risulta difficile individuare e gestire il file pippo15102004. Si consiglia inoltre di
copiarsi tale file su dischetto per poterlo usare a casa e averne una copia di riserva.
E’ anche possibile salvare il file access sul server del docente all’indirizzo “\\PC00\SQL Server”
oppure inviarselo per e-mail
•
L’obiettivo delle prime esercitazioni è l’introduzione al linguaggio SQL come DDL per la definizione
delle tabelle, ovvero l’uso “interattivo” di SQL. In ACCESS per poter usare SQL occorre definire una
query e l’opzione “visualizza SQL” in modo da avere una finestra di testo (nel seguito chiamata
“Finestra
SQL”)
in
cui
scrivere
ed
eseguire
le
istruzioni
SQL.
Si consiglia di usare un file di testo (ad esempio, un file word) per scrivere tutte le istruzioni SQL che
man mano vengono provate in ACCESS: tali istruzioni possono ovviamente essere copiate/incollate
nella “Finestra SQL” di ACCESS per essere eseguite. Inoltre nel file di testo si possono aggiunegere
dei commenti/note alle varie istruzioni, come ho fatto io nel seguito.
•
Creazione di Tabelle tramite SQL con chiave primaria e chiavi alternative
Nel seguito ci sono le istruzioni utilizzate. Per poter provare i vari concetti sono state definite tante tabelle
(docente, docente1, …); l’alternativa era quella di cancellare di volta in volta la tabella creata per crearne
una nuova.
Creare la tabella
create table docente (
coddoc char(19),
cf char(16),
città char(30),
età integer,
)
Aggiungere la chiave primaria CODDOC
create table docente1(
coddoc char(19),
cf char(16),
città char(30),
età integer,
constraint chiaveprimariadidocente PRIMARY KEY(coddoc)
1
)
Aggiungere la chiave alternativa CF
create table docente2(
coddoc char(19),
cf char(16),
città char(30),
età integer,
constraint chiaveprimariadidocente PRIMARY key(coddoc),
constraint chiavealternatadidocente UNIQUE(cf)
)
Aggiungere il vincolo che la chiave alternativa CF non può essere nulla
create table docente3(
coddoc char(19),
cf char(16) NOT NULL,
città char(30),
età integer,
constraint chiaveprimariadidocente PRIMARY key(coddoc),
constraint chiavealternatadidocente UNIQUE(cf)
)
•
e altri vincoli.
•
Costrutto CHECK
Aggiungere il vincolo che l’età deve essere >= 18 e <= 100:
create table docente4(
coddoc char(19),
cf char(16) NOT NULL,
città char(30),
età integer,
constraint chiaveprimariadidocente PRIMARY key(coddoc),
constraint chiavealternatadidocente UNIQUE(cf),
constraint EtaDiUNProfessore CHECK(eta>=18 AND eta<=100)
)
⇒
l’SQL di ACCESS non accetta il costrutto CHECK!!!
Si deve usare l’interfaccia grafica per imporre tale vincolo. (vedere slide “CHECK” delle dispense
su ACCESS)
•
Inserimento dei dati nelle tabelle per verificare il comportamento del DB
2
Un’altra particolarità riscontrata nel CHECK (ed in generale nella gestione dei vincoli) con ACCESS è la
seguente
In generale, nel progetto di un Database si definisce prima tutto lo schema (tabelle, vincoli, …) e poi si
inseriscono i dati; spesso però c’e’ la necessità di aggiungere qualcosa (una tabella, una colonna, un
vincolo, ..) ad uno schema di Database in cui sono già stati introdotti dei dati. Per I vincoli, la regola
generale è la seguente: data una tabella T contenente già alcune tuple un vincolo può essere aggiunto a T
SOLO SE tale vincolo è rispettato dai dati già presenti.
In questo modo, il contenuto del Database è sempre consistente con i vincoli dello schema.
Esempio in DB2:
db2 => create table p(a integer)
DB20000I The SQL command completed successfully.
db2 => insert into p values(1)
DB20000I The SQL command completed successfully.
db2 => alter table p add constraint AMAGGIORERI0 check (a >0)
DB20000I The SQL command completed successfully.
db2 => alter table p add constraint AMAGGIORERI10 check (a >10)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0544N The check constraint "AMAGGIORERI10" cannot be added because the
table contains a row that violates the constraint. SQLSTATE=23512
In ACCESS
- si puo’ scegliere di verificare o meno il vincolo sui dati esistenti
- se si effettua la verifica e si trova una tupla che non soddisfa il vincolo, c’e’ un messaggio e la
verifica può continuare
Di conseguenza in ACCESS, il contenuto del Database non è sempre consistente con i vincoli dello
schema.
Ad esempio, consideriamo la seguente tabella con due valori:
Aggiungiamo il vincolo a > 10
3
Quando si chiude la finestra, si modifica la struttura della tabella. Viene visualizzato il seguente messaggio
Selezionando “Si” si ottiene
A questo punto il contenuto del Database non è consistente con il vincolo (a>10) in quanto nella Tabella
c’è il primo record con a = 10.
4
•
Creazione di Tabelle tramite SQL con definizione dell’integrità referenziale
CREATE TABLE Docente(CodDoc CHAR(4),
CF CHAR(16),
Città CHAR(30),
CONSTRAINT PKDocente PRIMARY KEY (CodDoc),
CONSTRAINT AKDocente UNIQUE(CF))
CREATE TABLE Corso(CodCor CHAR(14),
Nome CHAR(16),
CodDoc CHAR(30),
CONSTRAINT PKCorso PRIMARY KEY (CodCor),
CONSTRAINT FKCorsoDocente FOREIGN KEY(CodDoc) REFERENCES Docente(CodDoc))
Corso : tabella dipendente o correlata
Docente : tabella riferita o primaria
I nomi degli attributi usati nella specifica di un vincolo di integrità referenziale possono essere diversi:
CREATE TABLE Corso1(CodCor CHAR(14),
Nome CHAR(16),
CodiceDelDocenteDelCorso CHAR(30),
CONSTRAINT PKCorso PRIMARY KEY (CodCor),
CONSTRAINT FKCorso1Docente FOREIGN KEY(CodiceDelDocenteDelCorso)
REFERENCES Docente(CodDoc))
Nota: in ACCESS mentre due constraints che definiscono la PRIMARY KEY (ovviamente in due
differenti tabelle ) possono avere lo stesso, per i constraints che definiscono la FOREIGN KEY occorre
usare nomi diversi!
Quando, nella specifica di un vincolo di integrità referenziale, si usa la primary key della tabella riferita, si
può indicare solo il nome della tabella riferita (in quanto la primary key è unica)
CREATE TABLE Corso2(CodCor CHAR(14),
Nome CHAR(16),
CodiceDelDocenteDelCorso CHAR(30),
CONSTRAINT PKCorso PRIMARY KEY (CodCor),
CONSTRAINT FKCorso2Docente FOREIGN KEY(CodiceDelDocenteDelCorso)
REFERENCES Docente)
Quando, nella specifica di un vincolo di integrità referenziale, si usa una chiave alternativa, occorre
esplicitamente indicarla tra parentesi tonde dopo il nome della tabella riferita:
CREATE TABLE Corso3(CodCor CHAR(14),
Nome CHAR(16),
CFDelDocenteDelCorso CHAR(30),
CONSTRAINT PKCorso PRIMARY KEY (CodCor),
5
CONSTRAINT FKCorso3Docente FOREIGN KEY(CFDelDocenteDelCorso)
REFERENCES Docente(CF))
Esempio di FOREIGN KEY riferita alla tabella stessa:
CREATE TABLE Dipendente(CodDip integer,
CodCapo integer,
stipendio integer,
CONSTRAINT PK Dipendente PRIMARY KEY (CodDip),
CONSTRAINT FKDipendente FOREIGN KEY(CodCapo) REFERENCES Dipendente)
6
•
Gestione delle violazioni dell’integrità referenziale in ACCESS: Relazioni
CREATE TABLE CorsoDiLaurea(nome char(20),
ateneo CHAR(20),
indirizzo CHAR(30),
CONSTRAINT PKCDL primary key(nome,ateneo))
CREATE TABLE Studente (matricola INTEGER,
nomefacolta CHAR(20),
nomeateneo CHAR(20),
email CHAR(30),
CONSTRAINT PKStudente PRIMARY KEY (matricola,nomefacolta,nomeateneo),
CONSTRAINT FKStudenteCDL FOREIGN KEY (nomefacolta,nomeateneo)
REFERENCES CorsoDiLaurea)
Aprendo il pannello delle Relazioni vengono visualizzate le tabelle con le Foreign Key definite:
Le Primary Key vengono evidenziate in grassetto.
La rappresentazione delle Foreign Key avviene tramite linee che collegano gli attributi interessati; in
particolare il simbolo ∞ rappresenta la tabella dipendente o correlata (Studente) mentre il simbolo 1 la
tabella riferita o primaria (CorsoDiLaurea)
Ciccando su una Foreign Key si possono visualizzare e modificarne le proprietà:
7
Si noti che il campo “Applica integrità referenziale” è già selezionato: infatti, avendo scritto in SQL
CONSTRAINT FKStudenteCDL FOREIGN KEY (nomefacolta,nomeateneo)
REFERENCES CorsoDiLaurea)
ciò implica di dover applicare appunto l’integrità referenziale.
Quello che si può aggiungere è la gestione delle violazioni di tale vincolo di integrità referenziale. In
ACCESS si hanno solo le seguenti possibilità:
ON UPDATE CASCADE
ON DELETE CASCADE
8
•
Vincoli di colonna
In generale, un vincolo relativo ad un solo attributo può essere espresso anche come vincolo di colonna:
CREATE TABLE Docente4 (
CodDoc CHAR(4) CONSTRAINT PKDocente PRIMARY KEY,
CF CHAR(16) CONSTRAINT AKDocente UNIQUE,
Città CHAR(30)
)
CREATE TABLE Corso4(
CodCor CHAR(14) CONSTRAINT PKCorso PRIMARY KEY,
Nome CHAR(16),
CodDoc CHAR(30) CONSTRAINT FKCorsoDocente REFERENCES Docente4
)
è possibile aggiungere anche più vincoli sullo stesso attributo
CREATE TABLE Esercitatore(
CodDoc CHAR(4) NOT NULL
CONSTRAINT PKEsercitatore PRIMARY KEY
CONSTRAINT FKEsDoc REFERENCES Docente4,
Titolo CHAR(30)
)
ma questa possibilità non è accettata da ACCESS.
In Db2 (c’e’ pero’ la “limitazione” che occorre dire esplicitamente not null sulla primary key …)
CREATE TABLE Docente4 (
CodDoc CHAR(4) NOT NULL CONSTRAINT PKDocente PRIMARY KEY,
CF CHAR(16) NOT NULL CONSTRAINT AKDocente UNIQUE,
Città CHAR(30)
)
CREATE TABLE Esercitatore(
CodDoc CHAR(4) NOT NULL
CONSTRAINT PKEsercitatore PRIMARY KEY
CONSTRAINT FKEsDoc REFERENCES Docente4,
Titolo CHAR(30)
)
9
•
Uso di alter table
L’alter table è indispensabile per definire riferimenti mutui tra tabelle, in quanto all’atto di creazione di
una tabella in SQL tutte le tabelle usate nelle foreign key devono già essere state definite
DOCENTE(CODDOC, CodFacoltà)
F.K. CodFacoltà REFERENCES FACOLTA
FACOLTA(CODICE, CodDocPreside)
F.K. CodDocPreside REFERENCES DOCENTE
Allora,
1) si crea una tabella, es. Facoltà, senza F.K. CodDocPreside REFERENCES DOCENTE
CREATE TABLE Facoltà(Codice integer,
CodDocPreside integer,
CONSTRAINT PKFacoltà PRIMARY KEY (Codice))
2) si crea l’altra tabella con la foreign key
CREATE TABLE Docente(CodDoc integer,
CodFacoltà integer,
CONSTRAINT PKDocente PRIMARY KEY (CodDoc),
CONSTRAINT FKDocFacolta FOREIGN KEY(CodFacoltà) REFERENCES Facoltà)
3) si aggiunge alla prima tabella la foreign key tramite alter table
ALTER TABLE Facoltà
ADD CONSTRAINT FKFAcDocPreside FOREIGn KEY(CodDocPreside) references Docente
Si ottiene
Inserimento dei dati nelle tabelle Facoltà e Docente:
1) Inserisco delle Facoltà senza specificare il CodDocPreside (questo è possibile perchè non ho
messo NOT NULL su tale campo)
2) Inserisco dei Docenti con associata la relativa Facoltà
3) Per le Facoltà inserite al punto 1) specifico il CodDocPreside
10
Esempio di prova pratica relativo alla creazione dello schema
Dato il seguente schema relazionale
CLIENTE(NOME,CITTA, REGIONE, NAZIONE, TELEFONO)
PRODOTTO(CODPROD, PREZZO, TIPO,NUMERO)
A.K. TIPO,NUMERO
ACQUISTO(NOMECLIENTE,CITTACLIENTE,CODPROD,DATA)
F.K. NOMECLIENTE,CITTACLIENTE REFERENCES CLIENTE
F.K. CODPROD REFERENCES PRODOTTO
MAGAZZINO(TIPO,NUMERO,SCAFFALE)
F.K. TIPO,NUMERO REFERENCES PRODOTTO TIPO,NUMERO
Utilizzando il DBMS Access viene richiesto di
1) Creare le tabelle (comprensive di primary key, alternative key e foreign key) utilizzando il
linguaggio SQL
2) Gestire i vincoli di integrità referenziale con le seguenti specifiche
a. Impedire la cancellazione di un prodotto se tale prodotto è presente in ACQUISTO
b. Se un cliente viene cancellato, si devono cancellare tutti gli acquisti relativi a tale cliente
c. Se un prodotto cambia CODPROD, si deve riportare la modifica anche su tutti i suoi
elementi collegati in ACQUISTO
Soluzione
1) Creazione delle tabelle in SQL
CREATE TABLE CLIENTE(
NOME CHAR(20),
CITTA CHAR(10),
REGIONE CHAR(20),
NAZIONE CHAR(20),
TELEFONO CHAR(20),
CONSTRAINT PKDICLIENTE
PRIMARY KEY(NOME,CITTA))
CREATE TABLE PRODOTTO(
CODPROD INTEGER,
PREZZO INTEGER,
TIPO CHAR(10),
NUMERO INTEGER,
CONSTRAINT PKDIPRODOTTO
PRIMARY KEY(CODPROD),
CONSTRAINT AKPRODOTTO
UNIQUE(TIPO,NUMERO) )
11
CREATE TABLE ACQUISTO(
NOMECLIENTE CHAR(20),
CITTACLIENTE CHAR(10),
CODPROD INTEGER,
DATA DATE,
CONSTRAINT PKDIacquisto
PRIMARY KEY(NOMECLIENTE, CITTACLIENTE,
CODPROD,DATA),
CONSTRAINT FK_ACQUISTO_CLIENTE
FOREIGN KEY(NOMECLIENTE,CITTACLIENTE )
REFERENCES CLIENTE,
CONSTRAINT FK_ACQUISTO_PRODOTTO
FOREIGN KEY(CODPROD)
REFERENCES PRODOTTO)
CREATE TABLE MAGAZZINO(
TIPO CHAR(10),
NUMERO INTEGER,
SCAFFALE INTEGER,
CONSTRAINT PKDImagazzino
PRIMARY KEY(TIPO,NUMERO),
CONSTRAINT FK_MAGAZZINO_PRODOTTO
FOREIGN KEY (TIPO,NUMERO )
REFERENCES PRODOTTO(TIPO,NUMERO)
)
2) Gestire i vincoli di integrità referenziale con le seguenti specifiche
a. Impedire la cancellazione di un prodotto se tale prodotto è presente in ACQUISTO
b. Se un cliente viene cancellato, si devono cancellare tutti gli acquisti relativi a tale cliente
c. Se un prodotto cambia CODPROD, si deve riportare la modifica anche su tutti i suoi
elementi collegati in ACQUISTO
a) La specifica a) è già soddisfatta dai constraint espressi in SQL
b + c) Queste specifiche si impongono tramite il pannello delle Relazioni, modificando le proprietà delle
Foreign Key.
12