Division relationnelle

Une solution possible :

On utilise l’opérateur de division relationnelle (\(\div\)) pour trouver dans la table des services les identifiants des bars (id_bar) liés à tous les identifiants des bières (id_biere) de la table des bieres.

Ecriture en algèbre relationnelle :

• \(\displaystyle Q_1 = {\LARGE\div}(\Pi_{(id\_bar,id\_biere)}(services),\Pi_{(id\_biere)}(bieres))\).

L’opérateur de division relationnelle entre deux tables (\(S,T\)) permettant de récupérer les éléments \(X\) de l’ensemble \(S\) qui sont liés à tous les éléments \(Y\) de l’ensemble \(T\) :

• \(R=\div(S(X,Y),T(Y))\)

Cet opérateur n’existe pas en SQL mais peut être représenté à l’aide d’opérateurs de base par une requête imbriquée (NOT EXISTS) avec une opération ensembliste de différence (EXCEPT).

Ecriture en SQL :

SELECT se.X
FROM S se
WHERE NOT EXISTS (
                  (SELECT Y FROM T)
                  EXCEPT
                  (SELECT Y FROM S si WHERE si.X=se.X)
                 );

Ce qui se traduit par :

• « récupérer les \(X\) de \(S\) pour lesquels la différence entre le nombre total des \(Y\) de \(T\) et ceux de \(S\) est nulle »

Dans le contexte des « bars qui servent des bières » :

SELECT DISTINCT se.id_bar
FROM  services se
WHERE NOT EXISTS (
                  SELECT id_biere FROM bieres
                  EXCEPT
                  SELECT id_biere FROM services si WHERE si.id_bar=se.id_bar
                 );

Ce qui se traduit par :

• « récupérer les identifiants (\(id\_bar\)) des \(services\) pour lesquels la différence entre le nombre total des identifiants (\(id\_biere\)) de \(bieres\) et ceux des \(services\) est nulle »

Une solution possible :

On utilise d’abord l’opérateur de division relationnelle (\(\div\)) pour trouver dans la table des services les identifiants des bars (id_bar) liés à tous les identifiants des bières (id_biere) de la table des bieres.

On utilise le résultat obtenu pour faire une jointure naturelle avec l’ensemble des bars.

On récupère au final le nom des bars qui servent toutes les bières en faisant une projection sur l’attribut bar du résultat précédent.

Ecriture en algèbre relationnelle :

• \(\displaystyle Q_1 = \Pi_{(bar)}(\Join_{[]}(bars,{\LARGE\div}(\Pi_{(id\_bar,id\_biere)}(services),\Pi_{(id\_biere)}(bieres))))\).

Ecriture en SQL :

SELECT bar
FROM bars ba NATURAL JOIN services se
WHERE
NOT EXISTS (
            SELECT *
            FROM bieres bi
            WHERE NOT EXISTS(
                             SELECT *
                             FROM services  si
                             WHERE si.id_biere=bi.id_biere
                               AND se.id_bar=si.id_bar
                            )
          );

En SQL la jointure entre les services et les bars peut se faire dans la requête imbriquée et permet d’éviter une auto-jointure sur la table des services.

SELECT *
FROM bars ba WHERE
NOT EXISTS(
           SELECT *
           FROM bieres bi
           WHERE NOT EXISTS(
                            SELECT *
                            FROM services s
                            WHERE s.id_bar=ba.id_bar
                              AND s.id_biere=bi.id_biere
                           )
          );

Une solution possible :

On peut aussi mettre en œuvre la division relationnelle pour récupérer les identifiants des bars qui servent toutes les bières en regroupant les services par identifiant de bar :

• GROUP BY id_bar

puis en vérifiant que le nombre de bières différentes servies par les bars en question sont égales au nombre total de bières existantes dans la base de données :

• COUNT(DISTINCT id_biere) = (SELECT COUNT(id_biere) FROM bieres)

Ecriture en algèbre relationnelle :

• \(Q = G_{[COUNT(id\_biere)=(SELECT \; COUNT(id\_biere)\; FROM \; bieres)]}^{(id\_bar)}(services)\).

Le symbole \(G\) représente l’opérateur de regroupement appliqué sur la table services :

• en exposant : attributs de regroupement (GROUP BY)
• en indice : critère de restriction (clause HAVING) sur les regroupements.

Ecriture en SQL :

SELECT id_bar
FROM services
GROUP BY id_bar
HAVING COUNT(DISTINCT id_biere) = (SELECT COUNT(id_biere) FROM bieres);

Une solution possible :

On met en œuvre la division relationnelle pour récupérer les identifiants des bars qui servent toutes les bières en regroupant les services par identifiant de bar :

• GROUP BY id_bar

puis en vérifiant que le nombre de bières différentes servies par les bars en question sont égales au nombre total de bières existantes dans la base de données :

• COUNT(DISTINCT id_biere) = (SELECT COUNT(id_biere) FROM bieres)

Ecriture en algèbre relationnelle :

• \(Q = G_{[COUNT(id\_biere)=(SELECT \; COUNT(id\_biere)\; FROM \; bieres)]}^{(id\_bar)}(services)\).

On utilise le résultat obtenu pour faire une jointure naturelle avec l’ensemble des bars.

• \(NJ = \Join_{[]}(A,bars)\).

On récupère au final le nom des bars en projetant sur l’attribut bar du résultat précédent.

• \(Q_1 = \Pi_{(bar)}(NJ)\).

Ecriture en SQL :

SELECT bar
FROM services NATURAL JOIN bars
GROUP BY bar
HAVING COUNT(DISTINCT id_biere) = (SELECT COUNT(id_biere) FROM bieres);

Une solution possible :

On met en œuvre la division relationnelle pour récupérer les identifiants des bars qui servent toutes les bières en regroupant les services par identifiant de bar :

• GROUP BY id_bar

puis en vérifiant que le nombre de bières différentes servies par les bars en question sont égales au nombre total de bières existantes dans la base de données :

• COUNT(DISTINCT id_biere) = (SELECT COUNT(id_biere) FROM bieres)

On peut alors appliquer les fonctions d’agrégats (COUNT(id_biere)) pour calculer le nombre de bières servies par ces bars.

Ecriture en algèbre relationnelle :

• \(A = G_{[COUNT(id\_biere)=(SELECT \; COUNT(id\_biere)\; FROM \; bieres)]}^{(id\_bar,COUNT(id\_biere))}(services)\).

Ecriture en SQL :

SELECT id_bar,count(id_biere)
FROM services
GROUP BY id_bar
HAVING COUNT(DISTINCT id_biere) = (SELECT COUNT(id_biere) FROM bieres);