Domande Orale Ghelli

[Plaszczak]

Appello maggio 2021
pretest:
Rispondere alle domande direttamente su questo foglio, senza scrivere prima altrove. 
Rispondere a ogni domanda subito sotto la domanda
Se perdete la connessione chiamatemi subito al 347 3563753
Lo ridico: scrivete la risposta sotto ciascuna domanda

Dato il seguente schema relazionale che descrive persone che appaiono in fotografie e gli autori delle fotografie stesse
Persone(IdP, Nome, Cognome, NazioneP, AnnoNascita, CittàResidenza)
PersoneFoto(IdP*, IdF*)
Foto(IdF*, IdL*, Data, Tema, CittàFoto, IdA*)
Autori(IdA, Nome, Cognome, nazione)

scrivere le interrogazioni SQL che restituiscono le seguenti informazioni, senza ripetizioni
a)   Per tutte le persone che hanno solo foto scattate nella loro città di residenza, riportare Nome e Cognome
SELECT p.Nome, p.cognome
FROM Persone p
WHERE NOT EXIST (SELECT *
FROM PersoneFoto pt JOIN Foto f USING ( IdF)
WHERE p.IdP = pt.IdP 
AND f.CittàFoto <> p.CittàResidenza

b) Per tutte le coppie di persone che non appaiono mai assieme in nessuna foto riportare il nome e il cognome di entrambi
SELECT p1.nome, p1.cognome, p2.nome, p2.cognome 
FROM Persone p1, PersoneFoto pt1, Persone P2, PersoneFoto pf2, Foto f2, Foto f1
WHERE p1.IdP = pf1.IdP AND  p2.IdP = pf2.IdP AND pf1.IdF=f1.IdF AND pf2.IdF=f2.IdF AND f1.IdF <> f2.IdF

c) Per ogni AnnoNascita riportare l’AnnoNascita e il numero totale di foto in cui appare almeno una persona nata in quell’anno
SELECT p.AnnoNascita, COUNT(Foto.IdF)
FROM Persone p JOIN PErsoneFoto pt USING (IdP) JOIN Foto f USING (IdF)
GROUP BY p.AnnoNAscita
HAVING COUNT(*) >=1

d) Per ogni AnnoNascita riportare l’AnnoNascita e il numero totale di foto in cui appaiono solo persone nate in quell’anno
SELECT p.AnnoNascita, COUNT(f.IdF)
FROM Persone p 
WHERE NOT EXISTS (SELECT *
FROM PersoneFoto fr
WHERE p.IdP=pr.IdP
AND NOT EXISTS (SELECT * 
FROM  Foto f
WHERE f.IdF=pr.IdF
 AND p.AnnoNascita <> YEAR (f.data)))

e) Riportare IdA e nome di tutti quegli autori che hanno scattato tutte le loro foto prima del 2010 e a Firenze
SELECT DISTINCT a.IdA, a.Nome
FROM  Autori a
WHERE NOT EXISTS (SELECT *
FROM Foto f
WHERE a.IdA = f.IdA AND f.Anno >2010 OR Città <> “Firenze”)

f) Indicare in italiano, senza usare termini informatici ma solo facendo riferimento ad autori e foto, cosa ritornano le seguenti interrogazioni. Cercate di essere comprensibili e non ambigui nella risposta
SELECT DISTINCT a.IdA, a.Nome
FROM  Autori a
WHERE NOT EXISTS (SELECT *
FROM Foto f
WHERE a.IdA = f.IdA AND f.Anno > 2020 AND Città <> ‘Pisa)

Per ogni autore riportare IdA e Nome che non hanno fatto foto prima del 2020 o che abbiano fatto foto solo nella città “Pisa”.

SELECT DISTINCT a.IdA, a.Nome
FROM  Autori a
WHERE NOT EXISTS (SELECT *
FROM Foto f
WHERE a.IdA = f.IdA AND f.Anno < 2020 AND Città = ‘Pisa)

Per ogni autore riportare IdA e Nome che non hanno fatto foto dopo il 2020 o non abbia scatato foto nella città “Pisa”.

SELECT DISTINCT a.IdA, a.Nome
FROM  Autori a
WHERE NOT EXISTS (SELECT *
FROM Foto f
WHERE a.IdA = f.IdA AND (f.Anno < 2020 OR Città <> ‘Pisa))

Per ogni autore riportare IdA e Nome che non hanno fatto foto dopo 2020 e che abbiano fatto foto solo nella città “Pisa”.

g)  Per tutte le persone che appaiono assieme in una foto che non è stata scattata prima del 2010 a Pisa, riportare il nome e cognome di entrambi
SELECT p1.nome, p1.cognome, p2.nome, p2.cognome
FROM Persone p1, persone p2
WHERE NOT EXISTS (SELCET *
FROM PersoneFoto pf JOIN foto f USING (Idf)
WHERE f.citta

Si consideri il seguente schema
R(ABCDE, { CA-> E, CE -> A, CD -> A, CA -> D })
Calcolare una chiave
Dare un esempio di una dipendenza che sarebbe ridondante
La decomposizione R1(ABD), R2(ABCE) preserverebbe i dati?
Applicare l’algoritmo di sintesi
Applicare l’algoritmo di analisi

la chiave è BCE
non ci sono dipendenze ridondanti, una dipendenza che crea ridondanza potrebbe essere CD->E 
 applicando l'algoritmo di sintesi ottengo :
R1(CAED), R2(CEA), R3(CDA)
noto che R2 e R3 è contenuto in R1, manca l’elemento B quindi dichiaro R4(BCE) ottenendo :
R1(CAED) R4(BCE)
prendo la prima dipendenza e vedo se rispetta la FNBC non la rispetta quindi devo decomporre e ottengo :
R1(CAED) R2 (BCA)
per CAED {CA->E, CA->D, CE->A, CD->A} mentre per (BCA) Ottengo insieme vuoto 
prendo R1 e le sue dipendenze funzionali e controllo la FNBC, e lo sono tutte quindi ottengo R1(CAED) E R2(BCA)

Domande
- Definizione di dipendenza funzionale
- indipendenza fisica e logica
- cos'è la serializzabilità
- 2pl stretto

[lorenzo_pernigoni]

Allego il mio progetto.