Basi di Dati Relazionali - mat.unical.itrullo/teaching/basidati_rel/a.a.09-10/... · UNIVERSITA'...

UNIVERSITA' DELLA CALABRIA

Basi di Dati Relazionali

Corso di Laurea in Informatica

a.a. 2009-2010

Laboratorio

Corso di Basi di Dati Relazionali – 05 Giugno 09

Laboratorio

Ing. G. LaboccettaIng. G. Laboccetta

Dott.ssa.Dott.ssa. V. PolicicchioV. Policicchio

Coadiutore: Dott.ssa D. Nicotera Coadiutore: Dott.ssa D. Nicotera


PROGETTAZIONE FISICASQL-DDL


OBIETTIVO:

Rappresentare i dati della realtà di interesse

attraverso le strutture dati di uno specifico DBMS


OBIETTIVI DEL CORSO-RIEPILOGO

� Le lezioni di laboratorio del corso di Basi di Dati Relazionali prevedono la progettazioneprogettazione e la realizzazionerealizzazione di un sistema informatico basato su un DBMS relazionale (MySql 5.05.0).

• Durante le lezioni devono essere svolte tutte le fasi di sviluppo del sistema informatico. Ogni fase è composta da diversi step, ciascuno dei quali porta alla produzione di più documenti.


ciascuno dei quali porta alla produzione di più documenti.

� L'insieme di tali documenti costituisce la documentazione del sistema informatico che deve essere prodotta durante le lezioni.

� Il sistema informatico deve essere costituito da una base di dati realizzata su un DBMS relazionale (MySql 5.05.0) interrogabile mediante SQL.


FASI DELLA PROGETTAZIONE

Schema concettuale

Progettazione concettuale

Raccolta e Analisi dei requisiti


Progettazione logica

Progettazione fisica

Schema logico

Schema fisico


PROGETTAZIONE DELLA BASE DIDATI



FASI E STEP DEL PROGETTO

1.Analisi e progettazione concettuale

a. Raccolta e Analisi dei requisiti

b. Progettazione dello schema Entità-Relazione (E-R)

c. Specifica delle funzioni per operare sui dati

d. Stima del carico applicativo del sistema informatico


d. Stima del carico applicativo del sistema informatico

2.Progettazione logica della base di dati

a. Ristrutturazione dello schema E-R in uno schema E-R

semplificato orientato al modello relazionale

b. Traduzione dello schema E-R ristrutturato in schema

relazionale


FASI E STEP DEL PROGETTO

3. Progettazione fisica ed implementazione della base di

dati

a. Definizione dello schema della base di dati sul DBMS

relazionale open-source MySQL 5.0 mediante SQL-


relazionale open-source MySQL 5.0 mediante SQL-

DDL

b. Popolamento della base di dati

c. Realizzazione delle interrogazioni per operare sui dati


OBIETTIVI DELLA LEZIONE

� SQL:� Funzionalità DDL

� Sintassi� Esempi



PROGETTAZIONE FISICA

Obiettivo: Rappresentare i dati della realtà di interesse attraversole strutture dati di uno specifico DBMS• Input:� Schema logico (prodotto della progettazione logica)

� DBMS scelto (per sapere quali strutture dati fisiche utilizzare)� Previsioni del carico applicativo (per ottimizzare la base di dati

rispetto ad esso)


rispetto ad esso)

• Output:� Schema fisico (Tipicamente nei DBMS attuali consiste in uno

schema logico definito mediante SQL-DDL + alcune scelte relativeall’effettiva implementazione sul DBMS di detto schema)

• Qualità:– Efficienza rispetto al carico applicativo


MODELLI DI DATI

I prodotti della varie fasi della progettazione sono schemi basati su specifici modelli di datimodelli di dati:

� Schema concettuale � modello concettuale

� Schema logico� modello logico


� Schema logico� modello logico

� Schema fisico� modello logico + parametri fisici


MODELLI DEI DATI ADOTTATI

– Schema concettuale:• Modello Entità-Relazione (ER)

– Schema logico:• Modello Relazionale • DDL: SQL-standard


• DDL: SQL-standard

– Schema fisico:• Modello relazionale + strutture fisiche• DDL: SQL supportato dal DBMS scelto (MySql 5.0)


SQL

� Originariamente acronimo di "Structured Query Language", ora nome proprio

� Prima proposta: SEQUEL (IBM Research, 1974)

� È uno standard (1986, poi 1989, 1992, 1999 e infine 2003) ma esistono varie versioni (DBMS diversi implementano versioni diverse)


esistono varie versioni (DBMS diversi implementano versioni diverse)

� È un linguaggio dichiarativo (a differenza dell’Algebra Relazionale che è di tipo procedurale)

� Ha varie funzionalità:� DDL (Data Definition Language)� DML (Data Manipulation Language)


SQL- FUNZIONALITÀ DDL

� Data Definition Language: funzionalità che consente di definire lo schema dei dati


� L’istruzione più importante del DDL di SQL è:

CREATE TABLE� definisce uno schema di relazione, specificando attributi e vincoli)

� crea un’istanza vuota dello schema


DEFINIZIONE DI TABELLE

CREATE TABLE NomeTabella (NomeAttributo Dominio [ Vincoli ] {, NomeAttributo Dominio [ Vincoli ]}[ AltriVincoli ])


[ AltriVincoli ])


CREATE TABLE, ESEMPIO

CREATE TABLE Impiegato(

Matricola CHAR(6) PRIMARY KEY,

Nome CHAR(20) NOT NULL,

Cognome CHAR(20) NOT NULL,

Dipart CHAR(15),


Dipart CHAR(15),

Stipendio NUMERIC(9) DEFAULT 0,

FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip),

UNIQUE (Cognome,Nome) )


DOMINI

�Domini elementari (predefiniti)

�Domini definiti dall'utente (semplici, ma riutilizzabili)


riutilizzabili)


DOMINI ELEMENTARI

– Carattere

singoli caratteri o stringhe, anche di lunghezza variabile:

char(n) stringhe di lunghezza fissa


char(n)

character(n)

varchar(n)

char varying(n)

stringhe di lunghezza variabile

nchar(n)

nvarchar(n)

nchar varying(n))

come sopra ma UNICODE


DOMINI ELEMENTARI

– Numerici esatti e approssimati:

int, integer, smallint interi

numeric,numeric(p), valori numerici esatti non


numeric,numeric(p), numeric(p,s))

valori numerici esatti non negativi

decimal, decimal(p), decimal(p,s))

valori numerici esatti anche negativi

float, float(p), real, double precision

Reali


DOMINI ELEMENTARI

– Data, ora

• date / time / timestamp• time with timezone / timestamp with timezone

– Bit: singoli bit o stringhe di bit• bit(n)


• bit(n)

• bit varying(n)

– Introdotti in SQL 1999

• boolean

• BLOB, CLOB (binary/character large object): per grandi immagini e testi


DEFINIZIONE DI DOMINI

� Istruzione CREATE DOMAINCREATE DOMAIN: • definisce un dominio (semplice), utilizzabile in definizionidi relazioni, anche con vincoli e valori di default

• SINTASSI:CREATE DOMAIN NomeDominioCREATE DOMAIN NomeDominio


CREATE DOMAIN NomeDominioCREATE DOMAIN NomeDominioas DominioPreesistente [ Default ] [ Vincoli ]as DominioPreesistente [ Default ] [ Vincoli ]

• ESEMPIO:CREATE DOMAIN Voto CREATE DOMAIN Voto AS SMALLINT DEFAULT NULLAS SMALLINT DEFAULT NULLCHECK ( value >=18 AND value <= 30 )CHECK ( value >=18 AND value <= 30 )

UNIVERSITA' DELLA CALABRIA VINCOLIINTRARELAZIONALI

� NOT NULL

� UNIQUE definisce attributi che identificano una tupla

PRIMARY KEY: chiave primaria (una sola, implica


� PRIMARY KEY: chiave primaria (una sola, implica NOT NULL e UNIQUE)

� CHECK, consente di verificare che una condizione venga rispettata


UNIQUE E PRIMARY KEY

Due forme:• singolo attributo:

�unique dopo la specifica del dominio�primary key dopo la specifica del dominio


• più attributi:�unique (Attributo,..., Attributo)�primary key (Attributo,..., Attributo)







Dipart CHAR(15),


Dipart CHAR(15),





PRIMARY KEY, ALTERNATIVE

CREATE TABLE Impiegato (Matricola character(6) primary key,...)

Oppure


Oppure

CREATE TABLE Impiegato (Matricola character(6),...primary key (Matricola))







Dipart CHAR(15),


Dipart CHAR(15),





CHIAVI SU PIÙ ATTRIBUTI

� Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, UNIQUE (Cognome,Nome),

� Nome CHAR(20) NOT NULL UNIQUE, Cognome CHAR(20) NOT NULL UNIQUE,


Cognome CHAR(20) NOT NULL UNIQUE,

Attenzione: non è la stessa cosa!


VINCOLI INTERRELAZIONALI

�� CHECKCHECK

�� REFERENCESREFERENCES e FOREIGN KEYFOREIGN KEY� Permettono di definire vincoli di integrità referenziale


� di nuovo due sintassi:�per singoli attributi�su più attributi

� E' possibile definire politiche di reazione alla violazione


REFERENCES E FOREIGN KEY• Permettono di definire vincoli di integrità referenziale.

� Sintassi:– per singoli attributi:referencesreferences dopo la specifica del dominio

– riferimenti su più attributi:foreignforeign key(Attributo,...,key(Attributo,...,AttributoAttributo) ) referencesreferences


foreignforeign key(Attributo,...,key(Attributo,...,AttributoAttributo) ) referencesreferencesALTRATABELLA(A1,…,AN)

� Gli attributi referenziati nella tabella di arrivo (master) devono formare una chiave (primay key o unique).

� Semantica: ogni combinazione (senza NULL) di valori per gli attributi nella tabella di partenza (slave) deve comparire nellatabella di arrivo (master).


InfrazioniCodice

34321

64521

53524

Data

1/2/95

4/3/95

5/4/96

Vigile

3987

3295

3295

Prov Numero

MI

TO

PR

39548K

E39548

839548

EsempioEsempioEsempioEsempio


Matricola

3987

3295

9345

Vigili Cognome

Rossi

Neri

Neri

Nome

Luca

Piero

Mario

Mori Gino7543

73321

64521 5/4/96

5/2/98

3295

9345

PR

PR

839548

839548


InfrazioniCodice

34321

64521

53524

Data

1/2/95

4/3/95

5/4/96

Vigile

3987

3295

3295

Prov Numero

MI

TO

PR

39548K

E39548

839548

EsempioEsempioEsempioEsempio


73321

64521 5/4/96

5/2/98

3295

9345

PR

PR

839548

839548

Auto Prov Numero

MI

TO

PR

39548K

E39548

839548

Cognome

Rossi

Rossi

Neri

Nome

Mario

Mario

Luca



CREATE TABLE Infrazioni(

Codice CHAR(6) NOT NULL PRIMARY KEY,

Data DATE NOT NULL,

Vigile INTEGER NOT NULLREFERENCES

Vigili(Matricola),


Vigili(Matricola),

Provincia CHAR(2),

Numero CHAR(6) ,

FOREIGN KEY(Provincia, Numero)REFERENCES Auto(Provincia,

Numero)

)


MODIFICHE DEGLI SCHEMI

� ALTER DOMAIN

� ALTER TABLE

� DROP DOMAIN

� DROP TABLE

...


...


MODIFICHE DEGLI SCHEMIALTER TABLE

� Permette di modificare lo schema di una tabella� Esempio:CREATE TABLE Infrazioni(Codice CHAR(6) NOT NULL PRIMARY KEY, Data DATE NOT NULL, Vigile INTEGER NOT NULL

REFERENCES Vigili(Matricola),


Vigile INTEGER NOT NULLREFERENCES Vigili(Matricola),

Provincia CHAR(2), Numero CHAR(6) )

ALTER TABLE Infrazioni ADD CONSTRAINT MioVincolo foreign key (Provincia, Numero) references Auto(Provincia,Numero)


MODIFICHE DEGLI SCHEMIDROP TABLE

� Elimina una tabella� Sintassi::

DROP TABLE NomeTabella restrict | cascade

� Esempio:Elimina la tabella solo se non ci sono riferimenti ad


� Elimina la tabella solo se non ci sono riferimenti ad essa:

1. DROP TABLE Infrazioni [restrict]

� Elimina la tabella e tutte le tabelle (o più in generale tutti gli oggetti del DB) che si riferiscono ad essa

1. DROP TABLE Infrazioni cascade


DEFINIZIONE DEGLI INDICI

Definizione di indici:• è rilevante dal punto di vista delle prestazioni• ma è a livello fisico e non logico

Istruzione create indexcreate index


Istruzione create indexcreate index• Sintassi (semplificata):CREATE [UNIQUE] INDEX NomeIndice onNomeTavola(col_1, col_2, …, col_n)

• Esempio:create index IndiceIP on Infrazioni(Provincia)


PROGETTO DIDATTICO

� Realizzazione dell'applicazione consiste nell'implementazione della base di dati secondo lo schema logico prodotto nella fase di progettazione logica (con SQL-DDL) e dall'implementazione delle operazioni previste sui dati (con SQL-DML)


operazioni previste sui dati (con SQL-DML)


PROGETTO DIDATTICO

� La documentazione relativa alla realizzazione del progetto consiste:� nel listato delle istruzioni SQL per la definizione dello schema della base di dati (SQL-DDL);

� nel listato delle istruzioni SQL la definizione delle interrogazioni per la visualizzazione dei dati (SQL-DML);


� nel listato delle istruzioni SQL la definizione delle operazioni per la manipolazione dei dati (aggiornamenti, inserimenti, cancellazioni) (SQL-DML).


PROGETTO DIDATTICO� Test dell'applicazione. La documentazione di questa parte consiste nella:

�Definizione del contenuto di test della base di dati �Stampa dei risultati delle interrogazioni su tali dati


dati


Gestione di una Farmacia -Progettazione Fisica


Progettazione Fisica


SCHEMA RISTRUTTURATO



(SOTTO) SCHEMA RISTRUTTURATO –FARMACI E PRODOTTI

TRALASCIAMO

IL SOTTOINSIEME DELLO

SCHEMA RELATIVO AI

PRODOTTI DI PROFUMERIA



SCHEMA LOGICO (1/3)� Traduzione delle entità (II PARTE):

PRODOTTO(codice, nome, descrizione, scadenza)

CASAFARMACEUTICA(nome,recapito)

CATEGORIAFT(codice,nome)

F (codiceP*, prescrivibile, brevettato, equivalente*,


FARMACO(codiceP*, prescrivibile, brevettato, equivalente*,

nomeCasa*, categoria*)

con un vincolo di integrità referenziale tra:

(1) l’attributo codiceP e la relazione PRODOTTO,

(2) l’attributo equivalente e la relazione FARMACO

(3) l’attributo nomeCasa e la relazione CASAFARMACEUTICA

(4) l’attributo categoria e la relazione CATEGORIAFT


SCHEMA LOGICO (2/3)� Traduzione delle entità (II PARTE):

SCONTRINO(data,numero)

PAZIENTE(CF, nome,cognome)

MEDICO(CF, nome,cognome)

P (data,numero,medico*,paziente*, (data_scontrino,


PRESCRIZIONE(data,numero,medico*,paziente*, (data_scontrino,

num_scontrino)*)


(1) l’attributo medico e la relazione MEDICO,

(2) l’attributo paziente e la relazione PAZIENTE

(3) gli attributi (data_scontrino,numero_scontrino) e la relazione

SCONTRINO


SCHEMA LOGICO (3/3)� Traduzione delle associazioni molti a molti:

INTERAZIONE(categoria1,categoria2)


(1) l’attributo categoria1 e la relazione CATEGORIAFT,

(2) l’attributo categoria2 e la relazione CATEGORIAFT.

DETTAGLIOSCONTRINO((data,numero)*,prodotto*,quantità)


DETTAGLIOSCONTRINO((data,numero)*,prodotto*,quantità)


(1) gli attributi (data,numero) e la relazione SCONTRINO,

(2) l’attributo prodotto e la relazione PRODOTTO

DETTAGLIOPRESCRIZIONE((data,numero,medico)*,farmaco*)


(1) gli attributi (data,numero,medico) e la relazione PRESCRIZIONE,

(2) l’attributo farmaco e la relazione FARMACO


SCHEMA FISICO –DEFINIZIONE DELLE TABELLE

RELAZIONE Prodotto(codice, nome, descrizione, scadenza)

SQL-DDL Statement

CREATE TABLE prodotto(

codice int(10) unsigned



auto_increment PRIMARY KEY,

nome varchar(20) NOT NULL,

descrizione varchar(100) NOT NULL,

scadenza date NOT NULL

) Engine=InnoDB;



RELAZIONE CasaFarmaceutica(nome, recapito)

SQL-DDL Statement

CREATE TABLE casaFarmaceutica(

nome varchar(20) PRIMARY KEY,


nome varchar(20) PRIMARY KEY,

recapito varchar(100) NOT NULL

)engine=innodb;



RELAZIONE CategoriaFT(codice,nome)

SQL-DDL Statement

CREATE TABLE categoriaFT(




auto_increment PRIMARY KEY,

nome varchar(20) NOT NULL

)engine=innodb;



RELAZIONE Farmaco(codiceP*, prescrivibile, brevettato, equivalente*,nome

Casa*,categoria*)

SQL-DDL Statement

CREATE TABLE Farmaco(

codiceP int(10) unsigned auto_increment PRIMARY KEY,

prescrivibile boolean NOT NULL,

brevettato boolean NOT NULL,


brevettato boolean NOT NULL,

equivalente int(10) unsigned,

nomeCasa varchar(20) NOT NULL,

categoria int(10) unsigned NOT NULL,

FOREIGN KEY (codiceP) REFERENCES Prodotto(codice),

FOREIGN KEY (nomeCasa) REFERENCES casaFarmaceutica(nome),

FOREIGN KEY (categoria) REFERENCES categoriaFT(codice)

) engine=innodb;



RELAZIONE Farmaco(codiceP*, prescrivibile, brevettato,

equivalente*,nome Casa*,categoria*)

Modifichiamo la tabella Farmaco per inserire il vincolo di integrità

referenziale sull’attributo equivalente.


equivalente*,nome Casa*,categoria*)

SQL-DDL Statement

ALTER TABLE Farmaco

ADD CONSTRAINT FK_farmaco_4

FOREIGN KEY (equivalente) REFERENCES Farmaco(codiceP);



RELAZIONE Scontrino(data,numero)

SQL-DDL Statement

CREATE TABLE Scontrino(

data date not null,


data date not null,

numero int(10) not null,

CONSTRAINT PK_scontrino PRIMARY KEY(data, numero)

) engine=innodb;

Note La relazione ha una chiave primaria composta da

due attributi



RELAZIONE Medico(CF, nome,cognome)

SQL-DDL Statement

CREATE TABLE Medico(

cf char(16) PRIMARY KEY,


nome varchar(20),

cognome varchar(20)

) engine=innodb;



RELAZIONE Paziente(CF, nome,cognome)

SQL-DDL Statement

CREATE TABLE Paziente(

cf char(16) PRIMARY KEY,


nome varchar(20),

cognome varchar(20)

) engine=innodb;



RELAZIONE Prescrizione(data,numero,medico*,paziente*,

(data_scontrino, num_scontrino)*)

CREATE TABLE Prescrizione(

data date NOT NULL,

numero int(10) unsigned NOT NULL,

medico char(16) NOT NULL,



paziente char(16) NOT NULL,

data_scontrino date NOT NULL,

num_scontrino int(10) NOT NULL,

PRIMARY KEY(data, numero,medico),

FOREIGN KEY (data_scontrino,num_scontrino)

REFERENCES Scontrino(data,numero),

FOREIGN KEY (medico) REFERENCES Medico(cf),

FOREIGN KEY (paziente) REFERENCES Paziente(cf) ) engine=innodb;



RELAZIONE Interazione (categoria1,categoria2)

SQL-DDL Statement

CREATE TABLE Interazione(

categoria1 int(10) unsigned,




FOREIGN KEY (categoria1) REFERENCES CategoriaFT(codice),

FOREIGN KEY (categoria2) REFERENCES CategoriaFT(codice),

PRIMARY KEY (categoria1,categoria2)

) engine=innodb;

Note La chiave primaria è formata dalla coppia

(categoria1,categoria2)



RELAZIONE DettaglioScontrino((data,numero)*,prodotto*,

quantità)

CREATE TABLE DettaglioScontrino(

data date NOT NULL,

numero int(10) NOT NULL,


numero int(10) NOT NULL,

prodotto int(10) unsigned NOT NULL,

quantità int NOT NULL DEFAULT 1,

FOREIGN KEY (prodotto) REFERENCES Prodotto(codice),

CONSTRAINT FK_prescrizione FOREIGN KEY (data,numero)

REFERENCES Scontrino(data,numero),

PRIMARY KEY(data, numero,prodotto)

) engine=innodb;



RELAZIONE DettaglioPrescrizione(codiceFarmaco*,(dataP,

numeroP,medico)*)

CREATE TABLE DettaglioPrescrizione(

codiceFarmaco int(10) unsigned NOT NULL,

data date NOT NULL,


numeroP int(10) unsigned NOT NULL,


FOREIGN KEY (codiceFarmaco) REFERENCES Farmaco(codiceP),

CONSTRAINT FK_dett_prescr FOREIGN KEY (data, numeroP,medico)

REFERENCES Prescrizione(data,numero,medico)

CONSTRAINT FK_dett_prescr

PRIMARY KEY(data, numeroP,medico,codiceFarmaco)

) engine=innodb;

Basi di Dati Relazionali - mat.unical.itrullo/teaching/basidati_rel/a.a.09-10/... · UNIVERSITA'...

Documents

Transcript of Basi di Dati Relazionali - mat.unical.itrullo/teaching/basidati_rel/a.a.09-10/... · UNIVERSITA'...