Correction Examen Base de Donnée

Exercice 01 : Dépendances fonctionnelles (3 points)

On considère l’ensemble des dépendances fonctionnelles suivantes :

e, f, g → t
a → v, b
b → c, w
c → d, z
d → c, u

1. Définir le schéma équivalent en 3e forme normale (indiquer les clés primaires et étrangères).

SOLUTION :

Pour normaliser en 3NF, nous devons nous assurer qu'il n'y a pas de dépendances transitives et que chaque attribut non clé est dépendant uniquement de la clé primaire. Voici les relations normalisées :

• R1(e, f, a) où {e, f, a} est la clé primaire.
• R2(e, f, g, t) où {e, f, g} est la clé primaire.
• R3(a, v, b) où a est la clé primaire et b est une clé étrangère référençant R4.
• R4(b, w, c) où b est la clé primaire et c est une clé étrangère référençant R5.
• R5(c, d, z) où c est la clé primaire et d est une clé étrangère référençant R6.
• R6(d, u) où d est la clé primaire.

Les clés étrangères sont définies comme suit :

• Dans R3, b est une clé étrangère faisant référence à la clé primaire de R4.
• Dans R4, c est une clé étrangère faisant référence à la clé primaire de R5.
• Dans R5, d est une clé étrangère faisant référence à la clé primaire de R6.

Exercice 02 : (5 points)

Soit la base de données relationnelle, PUF, de schéma :

• U(NU, NomU, Ville)
• P(NP, NomP, Couleur, Poids)
• F(NF, NomF, Statut, Ville)
• PUF(NP, NU, NF, Quantité)

Clés primaires et étrangères :

• NP référence P.NP
• NU référence U.NU
• NF référence F.NF

Décrivant le fait que :

• U : une usine est décrite par son numéro NU, son nom NomU, la ville Ville dans laquelle elle est située
• P : un produit est décrit par son numéro NP, son nom NomP, sa couleur Couleur, son poids Poids
• F : un fournisseur est décrit par son numéro NF, son nom NomF, son statut (fournisseur sous-traitant, fournisseur-client, etc.), la ville Ville où il est domicilié
• PUF : le produit de numéro NP a été livré à l'usine de numéro NU par le fournisseur de numéro NF dans une quantité donnée.

Écrire en langage algébrique les requêtes suivantes :

1. Donner le numéro, le nom et la ville de toutes les usines.
2. Donner le numéro, le nom et la ville de toutes les usines de Londres.
3. Donner les numéros des fournisseurs qui approvisionnent l'usine n°1 en produit n°1.
4. Donner le nom et la couleur des produits livrés par le fournisseur n°1.
5. Donner les numéros des fournisseurs qui approvisionnent l'usine n°1 en un produit rouge.

SOLUTION :

1. π_{NU, NomU, Ville}(U)
2. π_{NU, NomU, Ville}(σ_{Ville = 'Londres'}(U))
3. π_NF(σ_{NU = 1 AND NP = 1}(PUF))
4. π_{NomP, Couleur}(σ_{NF = 1}(PUF) ⨝ P)
5. π_NF(σ_{NU = 1 AND Couleur = 'rouge'}(PUF ⨝ P))

Exercice 03 : SQL (12 points)

Soit la base de données BANQUE contenant les tables suivantes :

• AGENCE(*Num_Agence, Nom, Ville, Actif)
• CLIENT(*Num_Client, Nom, Prenom, Ville)
• COMPTE(*Num_Compte, Num_Agence#, Num_Client#, Solde)
• EMPRUNT(*Num_Emprunt, Num_Agence#, Num_Client#, Montant)

1. Requêtes SQL

1. Créer un rôle pour un employé de l'agence bancaire qui peut uniquement consulter les informations des clients et des comptes sans pouvoir modifier ou supprimer des données.

   CREATE ROLE employe_banque;
   GRANT SELECT ON CLIENT, COMPTE TO employe_banque;

2. Comment restreindre l'accès à la table EMPRUNT afin que seuls les utilisateurs ayant le rôle employé banque puissent y insérer (INSERT) des données ?

   REVOKE INSERT ON EMPRUNT FROM PUBLIC;
   GRANT INSERT ON EMPRUNT TO employe_banque;

3. Créer un déclencheur sur la table COMPTE pour mettre à jour la table EMPRUNT dès que le solde d'un compte est inférieur à 1000€.

   CREATE TRIGGER solde_trigger
   AFTER UPDATE OF Solde ON COMPTE
   FOR EACH ROW
   WHEN (NEW.Solde < 1000)
   BEGIN
       UPDATE EMPRUNT
       SET Montant = Montant + 100
       WHERE Num_Client = NEW.Num_Client;
   END;

4. Écrire une requête SQL pour trouver le nombre total de clients par ville.

   SELECT Ville, COUNT(*) AS Total_Clients
   FROM CLIENT
   GROUP BY Ville;

5. Écrire une requête SQL pour obtenir la liste des agences avec le nombre total de comptes pour chaque agence.

   SELECT A.Nom, A.Ville, COUNT(C.Num_Compte) AS Total_Comptes
   FROM AGENCE A
   JOIN COMPTE C ON A.Num_Agence = C.Num_Agence
   GROUP BY A.Nom, A.Ville;