L'algebra relazionale è un linguaggio di interrogazione formale utilizzato nei database relazionali. Essa fornisce un insieme di operazioni che permettono di manipolare e recuperare dati da tabelle (relazioni). Comprendere queste operazioni è fondamentale per chiunque si occupi di progettazione e gestione di database, offrendo un approccio sistematico alla costruzione di query complesse. Questa trattazione si propone di esplorare diversi scenari pratici attraverso una serie di esercizi guidati, che spaziano dalla gestione delle vendite in un negozio alla programmazione di un cinema, passando per la gestione di playlist musicali.
Esercizio 1: Le Vendite del Negozio
Questo esercizio introduce concetti base di interrogazione su uno schema relazionale che modella le vendite di un negozio. Lo schema è composto dalle seguenti tabelle:
- Reparti (CodReparto, NomeReparto)
- Prodotti (CodProdotto, Descrizione, Prezzo, CodReparto*)
- Vendite (Numero, Data, Quantità, CodProdotto*)
Le query richieste permettono di affinare la comprensione delle operazioni di selezione e proiezione, nonché delle giunzioni per collegare informazioni tra tabelle correlate.
Query sull'Elenco Prodotti
Per ottenere "l’elenco dei prodotti con prezzo maggiore di 50 euro", si applica un'operazione di selezione sulla tabella Prodotti filtrando in base al Prezzo.
$\pi{Descrizione, Prezzo}(\sigma{Prezzo > 50}(Prodotti))$
Query sulle Vendite Identificate
L'interrogazione per trovare "le vendite che hanno un numero identificativo maggiore o uguale a 4" si effettua con una selezione sulla tabella Vendite.
$\sigma_{Numero \geq 4}(Vendite)$
Prodotti di un Reparto Specifico
Per elencare "l’elenco dei prodotti del reparto “Panetteria”", è necessaria una giunzione tra la tabella Reparti e Prodotti, seguita da una selezione sul NomeReparto e una proiezione sugli attributi desiderati.
$\pi{Descrizione, Prezzo}(\sigma{NomeReparto = 'Panetteria'}(Reparti \Join Prodotti))$
Vendite di un Prodotto Specifico
Per identificare "l’elenco delle vendite del prodotto “Filone”", si procede con una giunzione tra Prodotti e Vendite, selezionando in base alla Descrizione del prodotto.
$\pi{Numero, Data, Quantità}(\sigma{Descrizione = 'Filone'}(Prodotti \Join Vendite))$
Prodotti Specifici per Reparto
La query "l’elenco dei prodotti con descrizione e prezzo del reparto “Cibo per cani”" richiede di selezionare i prodotti appartenenti a un reparto specifico, utilizzando la giunzione tra Reparti e Prodotti.
$\pi{Descrizione, Prezzo}(\sigma{NomeReparto = 'Cibo per cani'}(Reparti \Join Prodotti))$
Vendite di Febbraio in un Reparto Specifico
Per ottenere "l’elenco dei prodotti (con Descrizione e Prezzo) venduti in Febbraio nel reparto “Alimenti surgelati”", si combinano selezioni basate sulla data (mese di Febbraio) e sul nome del reparto, oltre a giunzioni tra le tabelle. Si assume che la colonna Data sia in un formato che permetta l'estrazione del mese.
$\pi{P.Descrizione, P.Prezzo}(\sigma{NomeReparto = 'Alimenti surgelati' \land Mese(Data) = 'Febbraio'}(Reparti \Join Prodotti \text{ as } P \Join Vendite \text{ as } V \text{ on } P.CodProdotto = V.CodProdotto))$
Prodotti con Vendite Maggiori di 10
Infine, per "l’elenco dei prodotti (con Descrizione e NomeReparto) che hanno originato vendite con qualità venduta > 10 (quantità venduta in una singola vendita)", è necessaria una giunzione tra Reparti, Prodotti e Vendite, con una selezione sulla Quantità.
$\pi{P.Descrizione, R.NomeReparto}(\sigma{V.Quantità > 10}(Reparti \text{ as } R \Join Prodotti \text{ as } P \text{ on } R.CodReparto = P.CodReparto \Join Vendite \text{ as } V \text{ on } P.CodProdotto = V.CodProdotto))$
Esercizio 2: La Spesa al Supermercato (Attori e Film)
Questo esercizio, pur descritto come "La spesa al supermercato", presenta uno schema relazionale legato al mondo del cinema, con tabelle per film, attori e le loro interpretazioni. Questo schema è utile per comprendere come l'algebra relazionale possa interrogare database con relazioni molti-a-molti.
- Attori (idattore, nome, cognome, datanascita)
- Film (id_film, titolo, trama, durata, genere)
- Recitare (idattore, idfilm, ruolo)
La relazione "Recitare" collega attori e film, permettendo di associare a ciascun film gli attori che vi hanno recitato e il loro ruolo specifico.
Attori Nati negli Anni '80
Per ottenere "l’elenco di attori nati negli anni '80", si applica una selezione sulla tabella Attori basata sull'anno di nascita.
$\pi{nome, cognome}(\sigma{Anno(data_nascita) \geq 1980 \land Anno(data_nascita) \leq 1989}(Attori))$
Film con Durata Superiore a Due Ore
La query "Nome e trama dei film con durata maggiore di due ore" richiede una selezione sulla tabella Film, assumendo che la durata sia espressa in minuti (120 minuti).
$\pi{titolo, trama}(\sigma{durata > 120}(Film))$
Film di Genere "Poliziesco"
Per ottenere "l’elenco dei film di genere "Poliziesco"", si effettua una selezione sulla tabella Film filtrando per il campo genere.
$\pi{titolo}(\sigma{genere = 'Poliziesco'}(Film))$
Film con "Leonardo Di Caprio"
Per trovare "Nome e trama dei film in cui ha recitato "Leonardo Di Caprio"", è necessario effettuare una giunzione tra le tabelle Attori, Recitare e Film, filtrando per il nome dell'attore.
$\pi{F.titolo, F.trama}(\sigma{A.nome = 'Leonardo' \land A.cognome = 'Di Caprio'}(Attori \text{ as } A \Join Recitare \text{ as } R \text{ on } A.id_attore = R.id_attore \Join Film \text{ as } F \text{ on } R.id_film = F.id_film))$
Film "Thriller" con "Jodie Foster"
La query "Nome e trama dei film "Thriller" in cui ha recitato "Jodie Foster"" combina criteri di genere e attore, richiedendo una giunzione tra le tre tabelle e selezioni multiple.
$\pi{F.titolo, F.trama}(\sigma{F.genere = 'Thriller' \land A.nome = 'Jodie' \land A.cognome = 'Foster'}(Attori \text{ as } A \Join Recitare \text{ as } R \text{ on } A.id_attore = R.id_attore \Join Film \text{ as } F \text{ on } R.id_film = F.id_film))$
Attori Protagonisti in "Vi presento Joe Black"
Infine, per elencare "Gli attori con ruolo "protagonista" nel film "Vi presento Joe Black"", si effettua una giunzione tra Attori, Recitare e Film, con selezioni sul titolo del film e sul ruolo.
$\pi{A.nome, A.cognome}(\sigma{F.titolo = 'Vi presento Joe Black' \land R.ruolo = 'protagonista'}(Attori \text{ as } A \Join Recitare \text{ as } R \text{ on } A.id_attore = R.id_attore \Join Film \text{ as } F \text{ on } R.id_film = F.id_film))$
Esercizio Algebra Relazionale - Parte1
Esercizio 4: Canzoni, Playlist e Utenti
Questo scenario modella un sistema di gestione musicale, collegando canzoni, playlist e utenti. La struttura relazionale include tabelle per canzoni, playlist e utenti, con una tabella intermedia canzoni_playlist per gestire la relazione molti-a-molti tra canzoni e playlist.
- canzoni (idcanzone, titolo, durata, genere, datauscita, num_ascolti)
- canzoniplaylist (idcanzone, cod_playlist)
- playlist (codplaylist, nome, idutente*)
- utenti (id_utente, nome, cognome)
Canzoni con Ascolti Elevati
Per ottenere "Titolo e durata delle canzoni che hanno avuto un num_ascolto maggiore a 1 milione", si applica una selezione sulla tabella canzoni.
$\pi{titolo, durata}(\sigma{num_ascolti > 1000000}(canzoni))$
Playlist di un Utente Specifico (ID)
La query "Elenco delle playlist create dall'utente con id 134" richiede una selezione sulla tabella playlist filtrando per id_utente.
$\pi{nome}(\sigma{id_utente = 134}(playlist))$
Playlist di un Utente Specifico (Nome)
Per trovare "Elenco delle playlist create dall'utente "Luca Galli"", è necessaria una giunzione tra utenti e playlist, seguita da una selezione sul nome e cognome dell'utente.
$\pi{P.nome}(\sigma{U.nome = 'Luca' \land U.cognome = 'Galli'}(utenti \text{ as } U \Join playlist \text{ as } P \text{ on } U.id_utente = P.id_utente))$
Canzoni in Playlist "Party" Uscite Dopo il 2019
Questa query richiede di combinare informazioni da canzoni, canzoni_playlist e playlist. Si cercano "Titolo durata e genere delle canzoni uscite dopo il 2019 presenti nella playlist "Party"".
$\pi{C.titolo, C.durata, C.genere}(\sigma{C.data_uscita > '2019' \land P.nome = 'Party'}(canzoni \text{ as } C \Join canzoni_playlist \text{ as } CP \text{ on } C.id_canzone = CP.id_canzone \Join playlist \text{ as } P \text{ on } CP.cod_playlist = P.cod_playlist))$
Canzoni nelle Playlist di "Lucia Rossi"
Per ottenere "Elenco delle canzoni inserite nelle playlist create dall'utente "Lucia Rossi"", si effettuano giunzioni tra utenti, playlist, canzoni_playlist e canzoni.
$\pi{C.titolo}(\sigma{U.nome = 'Lucia' \land U.cognome = 'Rossi'}(utenti \text{ as } U \Join playlist \text{ as } P \text{ on } U.id_utente = P.id_utente \Join canzoni_playlist \text{ as } CP \text{ on } P.cod_playlist = CP.cod_playlist \Join canzoni \text{ as } C \text{ on } CP.id_canzone = C.id_canzone))$
Playlist con Almeno una Canzone
Infine, per trovare "Nomi delle playlist in cui sono presenti almeno una canzone", si può utilizzare una giunzione tra playlist e canzoni_playlist e poi rimuovere i duplicati o usare un'operazione di proiezione dopo aver filtrato le playlist che appaiono in canzoni_playlist.
$\pi{nome}(playlist \cap \pi{cod_playlist}(canzoni_playlist))$
Esercizio 5: Il Cinema Vip Plus
Questo esercizio si concentra sulla progettazione di un database per un cinema, il "Vip Plus". L'obiettivo è gestire informazioni su biglietti, spettacoli, acquirenti e tessere punti. La progettazione include l'analisi del testo, le assunzioni, lo schema E-R, le regole di lettura e lo schema relazionale, culminando in interrogazioni in algebra relazionale.
Schema Relazionale Proposto (derivato dall'analisi):
- Acquirenti (CodAcquirente, Nome, Cognome, CodTessera*)
- TesserePunti (CodTessera, SaldoPunti)
- Biglietti (CodBiglietto, CodAcquirente, CodSpettacolo, Sala, Fila, NumeroPosto)
- Spettacoli (CodSpettacolo, TitoloFilm, Regista, DataOraInizio, CodSala*)
- Sale (CodSala, NomeSala, Capacita)
Si noti l'assunzione che ogni acquirente possa avere una sola tessera punti (relazione 1:1 o 1:0..1 tra Acquirenti e TesserePunti, con CodTessera come chiave esterna in Acquirenti).
Acquirente per Codice Identificativo
Per ottenere "Nome e cognome dell'acquirente che ha codice identificativo 1232", si applica una selezione sulla tabella Acquirenti.
$\pi{Nome, Cognome}(\sigma{CodAcquirente = 1232}(Acquirenti))$
Dettagli Biglietto Specifico
La query "La sala, la fila e il numero di posto relativo al biglietto con codice identificativo 231" richiede una selezione sulla tabella Biglietti.
$\pi{Sala, Fila, NumeroPosto}(\sigma{CodBiglietto = 231}(Biglietti))$
Acquirente di un Biglietto Specifico
Per trovare "Nome e cognome dell'acquirente che ha acquistato il biglietto con codice identificativo 1245", si effettua una giunzione tra Biglietti e Acquirenti.
$\pi{A.Nome, A.Cognome}(\sigma{B.CodBiglietto = 1245}(Biglietti \text{ as } B \Join Acquirenti \text{ as } A \text{ on } B.CodAcquirente = A.CodAcquirente))$
Biglietti per Spettacolo Specifico
La query "Elenco dei biglietti che si riferiscono allo spettacolo del film "Dante" di Pupi Avati" richiede una giunzione tra Biglietti e Spettacoli, con selezioni sul titolo del film e sul regista.
$\pi{CodBiglietto, Sala, Fila, NumeroPosto}(\sigma{S.TitoloFilm = 'Dante' \land S.Regista = 'Pupi Avati'}(Biglietti \text{ as } B \Join Spettacoli \text{ as } S \text{ on } B.CodSpettacolo = S.CodSpettacolo))$
Saldo Punti Tessera Acquirente
Per ottenere "Saldo dei punti della tessera dell'acquirente che ha codice identificativo pari a 45", si effettua una giunzione tra Acquirenti e TesserePunti.
$\pi{TP.SaldoPunti}(\sigma{A.CodAcquirente = 45}(Acquirenti \text{ as } A \Join TesserePunti \text{ as } TP \text{ on } A.CodTessera = TP.CodTessera))$
Biglietti Acquistati da un Acquirente Specifico
Infine, per trovare "Elenco dei biglietti acquistati dall'acquirente "Luca Galli"", si effettua una giunzione tra Acquirenti e Biglietti, con selezioni sul nome e cognome dell'acquirente.
$\pi{B.CodBiglietto, B.Sala, B.Fila, B.NumeroPosto}(\sigma{A.Nome = 'Luca' \land A.Cognome = 'Galli'}(Acquirenti \text{ as } A \Join Biglietti \text{ as } B \text{ on } A.CodAcquirente = B.CodAcquirente))$
Esercizio Algebra Relazionale - Parte1
tags: #esercizi #algebra #relazionale #zanetti