Riassunti esame Basi di dati e sistemi informativi - Luca Allulli (SQL)

R.

Relazione con tuple dello stesso valore ma nomi degli attributi

diversi.

Ad esempio STUDENTS

CF, MATR, SURNAME,NAME,DATE,ADDRESS

Operatori insiemistici:

Con riferimento alle relazioni Amici e Studenti determinare il nome

degli studenti che sono anche amici: Prima di tutto 2 insiemi

NOMI AMICI= Pcognome,nome (amici)

NomI STUDENTI= Pcognome,nome (studenti)

ora intersezione:

NOMIAMICISTUDENTI: NOMIAMICIÇNOMESTUDENTI

Invece determinare persone frequentano l'università docenti e

studenti

COGNOMISTUDENTI= Pcognome (studenti)

COGNOMIDOCENTI= PcognomeDocente (docenti)

con Rho rinomino cognomeDocente con COGNOMIDOCENTIOK

E unisco: COGNOMISTUDENTI U COGNOMIDOCENTIOK

In SQL uso:

UNION,INTERSECT,EXCEPT

Es. cognome e nome degli studenti che sono anche amici

Select cognome,nome

from Amici

intersect

select cognome,nome

from studenti

Per selezionare invece cognomi di docenti e studenti

Select cognome

from docenti

UNION

Select cognome

From studenti

Invece i cognomi di studenti che non siano cognomi di docenti:

except

Manipolazione di dati: Cancellazione di righe

DELETE FROM

WHERE (Se è omessa si cancella tutta la tabella) meglio prima fare

select

Aggiornamento di righe: Update

Set indirizzo = 'Via dei ciclamini'

where 'cognome' = 'bianchi '

and nome = 'ugo'

Oppure se voglio aumentare voto di esami:

Update esami

set voto = voto + 1

where voto <30

(Domini= char,varchar...)

Il modello relazionale consente di rappresentare domini articolati

attraverso basi di dati composte da numerose relazioni : i dati

possono essere ripartiti in diverse relazioni ma il modello

relazionale consente di mantenere comunque la base di dati

coerente e all'occorrenza riunire i dati.

Prendiamo in considerazione di nuovo : Rubrica telefonica

Amici (CF, COGNOME,NOME,DATA N )

TELEFONI (CF,NUMERO)

Non c'è un legame esplicito, ogni relazione ha senso

indipendentemente dall'altra. Sarà l'utente a creare un legame

logico.

Es: Il num di telefono della tupla Tt appartiene all'amico della tupla

Ta della relazione amici, il cui codice fiscale è uguale al codice

fiscale di Tt.

Tt(CF) = Ta (CF)

Quindi i legami logici sono stabiliti sulle uguaglianze dei valori

delle tuple.

Chiediamoci se il ruolo dei due CF è lo stesso: in Amici è una

chiave primaria, in telefono non lo è di per sè, anche perchè il

telefono non è un attributo elementare di un amico (può averne

tanti o nessuno) il CF non serve per identificare un telefono, ma

l'amico che lo possiede. Quindi nella prima CF per identificare le

tuple della relazione stessa, nella seconda per fare riferimento alle

tuple di una relazione esterna.

Altro esempio: SEGRETERIA STUDENTI

STUDENTI ( MATRICOLA,COGNOME,NOME,DATA NASCITA)

CORSI (NOMECORSO,COGNOMEDOCENTE,CREDITI,ANNO )

ESAMI (STUDENTE,CORSO,DATA,VOTO,LODE)

Studenti e corsi non hanno un legame diretto, studenti e esami

invece si:

Il numero di matricola dello studente che ha sostenuto l'esame:

presente in studenti come "matricola" e in esami come "studente".

Quindi le due tuple Ts e Te sono collegate se :

Ts(Matricola) = Te (Studente)

Anche qui in Studenti "matricola" è la chiave primaria che

identifica ogni studente, "studente"non è chiave primaria,usata per

far sì che ogni esame punti (si rappresenta con la freccia) allo

studente che lo ha sostenuto .

Anche ESAMI e CORSI sono legati: Ogni esame è relativo a un

corso: Corso di esami punta a Nomecorso di corsi quindi

Te(corso) = Tc (Nomecorso)

Esercizio : Registro Automobilistico :

Automobili (Targa,Marca,Modello,Proprietario)

Persone (Cf, cognome,nome)

Ta di automobili e Tp di persone sono collegate perchè :

Proprietario punta sul CF, o sul cognome e nome

Quindi le chiavi esterne: Quindi spesso legami tra due relazioni R e

S sono stabiliti dall'uguaglianza dei valori di un attributo Ar di R e

di un attributo As di S. Uno dei due , As, è la chiave primaria della

relazione S, e l'altro Ar assume i valori che fanno riferimento ai

valori della chiave primaria di S. Quindi Ar è chiave esterna della

relazione R(relazione interna) verso la relazione S(Relazione

esterna).

Studenti ha una chiave primaria che è Matricola e nessuna chiave

esterna

Corsi ha una chiave primaria che è nomecorso e nessuna esterna

Esami ha una chiave primaria: coppia studente,corso e due chiavi

esterne: studente (fa riferimento alla relazione studenti) e corso (fa

CHIAVE ESTERNA

riferimento alla relazione corsi). In genere una

è composta da tanti attributi quanti la chiave primaria della

relazione esterna.

Esempio: Si vogliono rappresentare le prove di un esame,con nome

e voto. Creiamo relazione PROVE che dovrà contenere nome della

prova,voto ma anche riferimento all'esame a cui appartiene la

prova,quindi chiave esterna verso esami. Esami però è

caratterizzata da due attributi: studente,corso come chiave

primaria quindi anche la chiave esterna di prove dovrà avere una

coppia di attributi : studenteesame e corsoesame

Prove: (Nomeprova,Voto, Studenteesame,corsoesame)

chiave esterna: studenteesame,corsoesame

VINCOLO DI INTEGRITA' REFERENZIALE: I valori della chiave

esterna devono corrispondere a quelli della chiave primaria, non

può far riferimento ad una tupla che non esiste,non avrebbe

senso,sarebbe inconsistente. Impongono quindi la consistenza della

base di dati rispetto alle chiavi esterne, si indicano con la freccia.

E' un vincolo interrelazionale.

REGISTRO AUTOMOBILISTICO:

AUTOMOBILI: Targa,marca,modello,proprietario

Persone: cf,cognome,nome

vincoli di chiave e vincoli d'integrità

targa chiave primaria

cf chiave primaria

L'attributo proprietario è una chiave esterna verso la chiave

primaria CF di persone .

UFFICIO TIROCNI DI UN'UNIVERSITA':

Corsi di laurea: Nome, id_corso

Studenti: Matr,cogn,nome,id_corso, email

Telefonistudenti: numero,matr

Enti: nome,indirizzo, cognome,nome referente, codice

telefoni enti: numero, nome

email: email,nome

Tirocini: nome,codicente,datainizio,durata, numero

partecipazione:matr,votazione, numtirocinio

Quindi Codicecorso di Studenti punta verso Corso di CorsiLaurea

MatricolaStudente di Telefonistudenti punta verso matricola di

Studenti

Matrstudente di Partecipazione punta verso matricola di studenti

e numtirocinio di partecipazione punta verso numero di tirocini

codice ente di tirocini punta verso codice di enti .

Altro esempio: Lavanderia di un grande albergo:

Clienti: nome,cognome,numstanza, idcliente

Tipoligieabito:descrizione,prezzolavaggio

Ordini: orarioconsegna,orarioriconsegna, numabiti, cliente

(esterna),idordine

Abiti: tipologia(descrizione esterna), id_abito, note,

orariofinelavaggio, ordine(chiaveesternaverso orario ricons di

ordine)

Definizione dei vincoli in SQL: deve essere definito nel momento

della creazione della tabella interna.

Uso la seguente sintassi:

FOREIGN KEY (A1,A2..) REFERENCES S (B1,B2..)

Esempio: RUBRICA TELEFONICA: Vincolo dall'attributo codice

fiscale di telefoni all'attributo codice fiscale di amici :

Create table telefoni (

codice_fiscale char(16),

numero VARCHAR (25)

Primary key (codice fiscale,numero)

Foreign key (codice_fiscale) REFERENCES Amici (codice_fiscale)

Cosa succede quando l'utente invia al DBMS comandi che violano i

vincoli? Può succedere con INSERT,UPDATE,DELETE.

Il DBMS manda messaggio di errore.

Esempio in registro automobilistico:

Persone: caronti stefania

automobili: peugeot e nissan proprietario il cf di caronti stefania

se noi facciamo con delete per eliminare stefania dalle persone

questo porta problemi di inconsistenza, il dbms quindi non esegue

l'azione impedendola o cancellando le righe delle auto di stefania

nella tabella automobili o porre a null il valore dell'attributo

proprietario.

Chiamate: DO NOTHING(far fallire comando), CASCADE,SET

NULL

JOIN: Permette di correlare tra loro parti di informazione collocate

in diverse relazioni: ricucire il dominio.

Esempio: SEGRETERIA STUDENTI : voti degli esami superati dalla

studentessa Sandra studiosi : selezione num matricola della

studentessa di nome sandra studiosi e poi selezione su esami?

Date due relazioni R e S che non abbiano in comune alcun nome di

attributo e data una condizione, il join è una relazione che ha come

attributi l'unione degli attributi R e S, le tuple si formano con le

combinazioni di una tupla R e S che rispettano la condizione, è una

verifica di uguaglianza degli attributi.

Studenti JOIN Esami matricola=studente

tabellona in cui formiamo le coppie con il prodotto cartesiano tra le

2 relazioni. Esercizio : REGISTRO AUTOMOBILISTICO

AUTOMOBILI JOIN PERSONE Proprietario= CF

CRNSTF = Caronti Stefania

PRRGLI = Perrone Giulia

Con riferimento alla base di dati segreteria studenti di

un'università determinare il cognome dei docenti che hanno

assegnato voti inferiori al 20.

ESAMI JOIN CORSI Corso= Nome corso

Proiezione sul cognome del docente

AMICI JOIN TELEFONI CF=CF (RINOMINIAMOLO CON RHO)

CF = CFAMICO

Selezione cognome = rossi and nome = mario

proiezione sul numero

select = proiezione

where = selezione

Quante tuple ha il join di due relazioni R e S: PRODOTTO

CARTESIANO

Esempio: A - C

Mela, arancia susina,banana,fragola cardinalità: 0

B: 1 1 D : 1 1 1 cardinalità: 6 (2x3)

ES = STUDENTI JOIN ESAMI : MATRICOLA=STUDENTE

ES JOIN CORSI corso=nomecorso

Proiezione cognome docente (selezione where cognome=.. and

nome=... più join)

SCHEMA REGISTRO AUTOMOBILISTICO:

AUTOMOBILI: targa,marca,modello,proprietario

PERSONE: Cf,cognome,nome

In SQL: Approccio dichiarativo : si esprime ciò che si vuole

ottenere,senza preoccuparsi di come ottenerlo. ad esempio:

segreteria studenti: join tra tabelle studenti ed esami. l'attributo

matricola di studenti deve avere lo stesso valore dell'attributo

studente di esami.

Select *

From Studenti,Esami

where matricola=studente

Altro esempio: Segreteria studenti. Voti esami di sandra studiosi

studenti: matricola,cognome,nome,data n

esami:studente,corso,data , voto

corsi: nome corso,cognome docente,crediti,anno

select voto

from Studenti,esami

where matricola=studente

and cognome = 'Studiosi'

and nome = 'Sandra'

Cognome docenti dei corsi dei quali studiosi sandra ha sostenuto

esame :

Select cognome

from : studenti,esa