Requêtes Relationnelles (10 points)

2 Requêtes Relationnelles (10 points)

Le réseau de téléphones portables SmallPhone gère ses abonnements avec Oracle. Voici le schéma qui a été défini :

Numéros(NoT, NomC, AdrC) : NoT est le numéro du téléphone abonné par le client NomC qui habite à l'adresse AdrC.
Appels(NoT, Zone, Date, Durée) : NoT est le numéro de téléphone auquel s'est abonné le client, Zone est la zone dans laquelle se trouve le numéro appelé à la date Date et Durée est la durée de communication (en minutes).
Tarifs(Zone, Prix) : Prix est le prix de communication par minute dans la zone Zone¹.

Écrire les requêtes suivantes en Algèbre relationnelle et en SQL :

Les noms des personnes qui ont fait des appels dans la zone 5? (1 point)

Solution :
pi_NomC(Numeros |>to -.45em< sigma_{Zone = 5}(Appels))
```
SELECT NomC
  FROM Numéros, Appels
 WHERE Numéros.NoT = Appels.NoT
   AND Appels.Zone = 5
```
Les noms des personnes qui ont fait des appels qui ont duré plus que 2 heures dans une zone où le prix de communication dépasse 10FF/Minute? (2 points)

Solution :

pi_NomC(Numeros |>to -.45em< sigma_{Duree > 120}(Appels) |>to -.45em< sigma_{Prix > 10}(Tarifs))
```
SELECT NomC
  FROM Numéros, Appels, Tarifs
 WHERE Numéros.NoT = Appels.NoT
   AND Appels.Zone = Tarifs.Zone
   AND Appels.Duree > 120
   AND Tarifs.Prix > 10
```
Exprimer la requête précédente en calcul domaine. (1.5 point)

Solution :

{N | il existe C,A,Z,D,P: (Numeros(N,C,A) /\ Appels(N,Z,D) /\ Tarifs(Z,P) /\ D > 120 /\ P > 10) }
Noms d'abonnés qui n'ont jamais fait des appels de plus de 3 minutes. (2 points)

Solution :
pi_NomCNumeros - pi_NomC( Numeros |>to -.45em< sigma_{Duree > 3} Appels)
```
SELECT NomC
  FROM Numéros
 WHERE NoT NOT IN (SELECT NoT
                     FROM Appels
                    WHERE Durée > 3);
```
Exprimer la requête précédente dans le calcul nuplet. (1 point)

Solution :

{ N.NomC | Numeros(N) /\ ¬ il existeA (Appels(A) /\ A.Duree > 3 /\ N.NoT = A.NoT)}

Écrire les requêtes suivantes seulement en SQL :

Nombre d'appels et durée totale des appels par zone. (1.5 point)

Solution :
```
SELECT Zone, COUNT(*), SUM(Durée)
  FROM Appels
GROUP BY Zone;
```

Numéros des personnes qui ont appelé dans la zone la plus chère pendant plus que 3 heures au total (plus de 3 heures pour tous les appels dans cette zone). (1 point)

Solution :

SELECT A.NoT
  FROM Numéros N, Appels A, Tarifs T
 WHERE N.NoT = A.NoT
   AND A.Zone = T.Zone
   AND T.Prix >= ALL (SELECT Prix FROM Tarifs)
GROUP BY N.NoT
HAVING SUM(A.Durée) > 180;