Università degli Studi di Cagliari Corso di Laurea in Ingegneria Elettronica BASI DI DATI http://www.diee.unica.it/~giacinto/BD Esercizi sull’algebra relazionale Docente: Giorgio Giacinto A.A. 2008/2009 Contatti Roberto Tronci e-mail: [email protected] Luca Piras e-mail: [email protected] Tel. 070 675 5776 Esercizio 3.3 (1/7) Considerate lo schema seguente: Fornitori (fid: integer, fnome: string, indirizzo: string) Pezzi (pid: integer, pnome: string, colore: string) Catalogo (fid: integer, pid: integer, costo: real) La relazione Catalogo elenca i prezzi adottati da tutti i fornitori. Scrivere le seguenti interrogazioni in algebra relazionale: Esercizio 3.3 (2/7) • Trovare i nomi dei fornitori che forniscono i pezzi rossi πfnome (πfid ((πpid σcolore =‘rosso’ Pezzi) Catalogo) Fornitori) • Trovare i fid dei fornitori che forniscono pezzi rossi o che sono al 221 di Parcker Avenue ρ (F1, πfid ((πpid σcolore =‘rosso’ Pezzi) Catalogo)) ρ (F2, πfid σindirizzo =‘221 Packer Avenue’ Fornitori) F1 ∪ F2 Esercizio 3.3 (3/7) • Trovare i fid dei fornitori che forniscono tutti i pezzi (πfid,pid Catalogo) / (πpid Pezzi) • Trovare i fid dei fornitori che forniscono tutti i pezzi rossi o verdi (πfid,pid Catalogo) / (πpid σcolore =‘rosso’ ∨ colore =‘verde’ Pezzi) Esercizio 3.3 (4/7) • Trovare coppie di fid tali che il fornitore con il primo fid applica per alcuni (ne esiste almeno uno) pezzi che entrambi forniscono un prezzo maggiore di quello del fornitore col secondo fid. ρ (C1, Catalogo) ρ (C2, Catalogo) πC1.fid,C2.fid (σC1.pid=C2.pid ∧ C1.fid≠C2.fid ∧ C1.costo>C2.costo (C1 × C2)) Esercizio 3.3 (5/7) • Trovare i pid dei pezzi più costosi forniti dai fornitori chiamati Yosemite Sham. ρ (R1 , πfid σfnome=‘Yosemite Sham’ Fornitori) ρ (R2, R1 ρ (R3, R2) Catalogo) ρ (R4 (4→fid1, 5→pid1, 6→costo1), σR3.costo<R2.costo (R3 × R2)) πpid (R2 – πfid, pid, costo R4) Esercizio 3.3 (6/7) • Trovare coppie di fid tali che il fornitore con il primo fid applica per tutti i pezzi che entrambi forniscono un prezzo maggiore di quello del fornitore col secondo fid. ρ (C1, Catalogo) ρ (C2, Catalogo) ρ(P1, πC1.fid,C2.fid (σC1.pid=C2.pid ∧ C1.fid≠C2.fid ∧ C1.costo>C2.costo (C1 × C2)) ρ(P2, πC1.fid,C2.fid (σC1.pid=C2.pid ∧ C1.fid≠C2.fid ∧ C1.costo<=C2.costo (C1 × C2)) P1 – P2 Esercizio 3.3 (7/7) ρ(P1, πC1.fid,C2.fid (σC1.pid=C2.pid ∧ C1.fid≠C2.fid ∧ C1.costo>C2.costo (C1 × C2)) P1 contiene tutte le coppie tali che il fornitore con il primo fid applica per almeno un pezzo (che entrambi forniscono) un prezzo maggiore di quello del fornitore col secondo fid. ρ(P2, πC1.fid,C2.fid (σC1.pid=C2.pid ∧ C1.fid≠C2.fid ∧ C1.costo<=C2.costo (C1 × C2)) P2 contiene tutte le coppie tali che il fornitore con il primo fid non applica per almeno un pezzo (che entrambi forniscono) un prezzo maggiore di quello del fornitore col secondo fid. P1 – P2 P1-P2 contiene tutte le coppie tali che il fornitore con il primo fid applica per tutti i pezzi (che entrambi forniscono) un prezzo maggiore di quello del fornitore col secondo fid. Esercizio 3.4 (1/3) Considerate lo schema seguente: Fornitori (fid: integer, fnome: string, indirizzo: string) Pezzi (pid: integer, pnome: string, colore: string) Catalogo (fid: integer, pid: integer, costo: real) Dire cosa calcolano le seguenti interrogazioni in algebra relazionale. Esercizio 3.4 (2/3) • πfnome ((πpid (σcolore =‘rosso’ Pezzi)) (σcosto<100 Catalogo) Fornitori) Trova tutti i nomi dei fornitori che forniscono almeno un pezzo rosso che costa meno di 100€ • (πfnome ((σcolore =‘rosso’ Pezzi) (σcosto<100 Catalogo) Fornitori)) ∩ (πfnome ((σcolore =‘verde’ Pezzi) (σcosto<100 Catalogo) Fornitori)) Trova tutti i nomi dei fornitori che forniscono almeno un pezzo rosso che costa meno di 100€ ed un pezzo verde che costa meno di 100€ Esercizio 3.4 (3/3) • πfnome (πfid, fnome ((σcolore =‘rosso’ Pezzi) (σcosto<100 Catalogo) Fornitori) ∩ πfid, fnome ((σcolore =‘verde’ Pezzi) (σcosto<100 Catalogo) Fornitori)) Trova tutti i nomi dei fornitori che forniscono almeno un pezzo rosso che costa meno di 100€ ed un pezzo verde che costa meno di 100€ Esercizio 3.5 (1/8) Considerate lo schema seguente: Voli (vno: integer, da: string, a: string, distanza: integer, partenza: time, arrivo: time) Aereo (aid: integer, anome: string, autonomia: integer) Certificato (pid: integer, aid: integer) Personale (pid: integer, pnome: string, salario: integer) La relazione Personale descrive piloti così come gli altri tipi di impiegati; ogni pilota è certificato per certi aerei e solo i piloti sono certificati per volare. Scrivete le interrogazioni seguenti in algebra relazionale. Esercizio 3.5 (2/8) • Trovare i pid dei piloti certificati per qualche aereo Boeing. πpid (σanome =‘Boeing’ (Aereo Certificato)) • Trovare gli aid di tutti gli aerei che possono essere usati per voli non-stop da Bonn a Madrid. ρ (BonnToMadrid, σda =‘Bonn’ ∧ a=‘Madrid’ (Voli)) πaid (σautonomia >= distanza (Aereo × BonnToMadrid)) πaid (σda =‘Bonn’∧ a=‘Madrid’ (Aereo Voli)) autonomia >= distanza Esercizio 3.5 (3/8) • Trovare i nomi dei piloti certificati per qualche aereo Boeing. πpnome (σanome =‘Boeing’ Aereo Certificato Personale) • Identificare i voli diretti che possono essere pilotati da piloti il cui stipendio sia superiore a 100 000€. πvno (σdistanza<=autonomia ∧ stipendio>100000 (Voli × ( Aereo Certificato Personale)) Esercizio 3.5 (4/8) • Trovare i nomi dei piloti che possono operare su aerei con un autonomia superiore a 3000 miglia, ma che non sono certificati per alcun aereo Boeing ρ (R1, πpid ( σautonomia > 3000 (Aereo πpnome (Personale (Aereo Certificato)) (R1 - πpid (σanome=‘Boeing’ Certificato)))) Esercizio 3.5 bis(?) (4/8) • Trovare i nomi dei piloti che possono operare su aerei con un autonomia superiore a 3000 miglia, ma che non sono certificati per alcun aereo Boeing ρ (R1, πpid, nome((Aereo autonomia > 3000 Certificato) Personale) ρ (R2, πpid, nome (Personale Certificato) / σanome=‘Boeing’(Aereo)) in R2 ci sono i piloti che sono πnome(R1- R2) certificati per TUTTI i Boeing E’ CORRETTA?? NO!! Esercizio 3.5 (5/8) • Trovare i pid del personale con il salario più alto ρ (P1, Personale) ρ (P2, Personale) ρ (P3, πP2.pid (P1 πpid P1 – P3 P1.stipendio>P2.stipendio P2) Esercizio 3.5 (6/8) • Trovare i pid del personale con il salario più basso ρ (P1, Personale) ρ (P2, Personale) ρ (P3, πP1.pid (P1 πpid P1 P1.stipendio>P2.stipendio P2) – P3 NOTA: a differenza dell’esercizio precedente ora la proiezione del risultato del join condizionale è fatta su P1.pid . Esercizio 3.5 (7/8) • Trovare i pid del personale con il secondo salario più alto ρ (P1, Personale) ρ (P2, Personale) ρ (P3, πP2.pid (P1 P1.stipendio>P2.stipendio P2) ρ (P4, P2 P3) ρ (P5, P2 P3) ρ (P6, πP5.pid (P4 P3 – P6 P4.stipendio>P5.stipendio P5) Esercizio 3.5 (8/8) • Trovare i pid del personale certificato esattamente per tre aerei ρ (C1, Certificato) ρ (C2, Certificato) ρ (C3, Certificato) ρ (C4, Certificato) ρ (P1,πpid (σ(C1.pid=C2.pid) ∧ (C2.pid=C3.pid) ∧ (C1.aid≠C2.aid) ∧ (C1.aid≠C3.aid) ∧ (C2.aid≠C3.aid) (C1 ρ (P2,πpid (σ (C1.pid=C2.pid) × C2 × C3)) ∧ (C2.pid=C3.pid) ∧ (C3.pid=C4.pid) ∧ (C1.aid≠C2.aid) ∧ (C1.aid≠C3.aid) ∧ (C1.aid≠C4.aid) ∧ (C2.aid≠C3.aid) ∧ (C2.aid≠C4.aid) ∧ (C3.aid≠C4.aid) (C1 P1 – P2 × C2 × C3 × C4))