Aller au contenu
Bases de données

UGA - MIASHS - S7 - BDD - Pierre Blarre

Exemple

Icône Présentation
✕
1 / 1

Conception de bases de données relationelles - Examen (2h)

Cet examen est à faire seul·e. Copier le travail d’un·e autre étudiant·e entraînera des sanctions pour tous les étudiants impliqués.
Notez uniquement votre n° d’étudiant sur ces feuilles et insérez-les dans la copie anonymée, n’oubliez pas de compléter les informations de la copie anonymée.
Le barème est donné à titre indicatif.

1 - Questions générales - 4 points - 20 min

1.1 - Quelles sont les étapes de construction d’un schéma conceptuel ?

Listez brièvement les étapes, ne donnez pas de détails.

Solution

  1. Principe de décomposition d’un énoncé en propositions élémentaires
  2. Traduction de ces propositions en structures Entité-Association
  3. Intégration de ces structures dans le schéma en cours de construction
  4. Normalisation
  5. Validation
  6. Finalisation du schéma
  7. Contraintes d’intégrité

1.2 - Quels sont les 4 niveaux de conception d’une base de données ? Expliquer très brièvement chacun d’entre eux.

Solution
  • Niveau Réel : Le monde réel
  • Niveau Conceptuel : Schéma Entités-Associations. C’est le niveau réel représenté en associations, types d’entités, cardinalités (et potentiellement annotations, attributs et identifiants)
  • Niveau Logique : Le niveau conceptuel détaillé, avec tous ses attributs et relations définis pour le niveau physique
  • Niveau physique : La base de données dans le logiciel, physiquement sur le disque dur

1.3 - À quoi sert l’instruction SQL “GROUP BY” ? Comment peut-on poser des conditions sur un “GROUP BY” ?

Solution Group by permet l’extraction de données groupées. On peut poser des conditions sur un group by avec l’instruction HAVING

2 - Modélisation - 10 points - 1h

Énoncé :

Une société de commerce électronique souhaite mettre en place une base de données pour gérer ses opérations.

La société vend divers produits en ligne et souhaite suivre les informations concernant ses clients, commandes, produits, catégories de produits, fournisseurs, employés et avis clients.

La société doit pouvoir suivre les stocks de ses produits. Les commandes peuvent comporter plusieurs produits et sont gérées par des employés. Les avis sont effectués par client et par produit et sont composés d’un commentaire et d’une note allant de 1 à 5. Les fournisseurs fournissent des produits et un produit peut être fourni par plusieurs fournisseurs, avec des prix unitaires différents par fournisseur.

Vous êtes chargé de concevoir la base de données relationnelle pour cette société, en proposant des types d’entités, attributs, associations et cardinalités pertinents, présentant clairement le fonctionnement de la base de données.

2.1 - Décomposez cet énoncé. Précisez les cardinalités.

Vous pouvez, en plus de rédiger ci-dessous, souligner, surligner, entourer, etc. directement le texte de la page précédente.

Solution possible
Propositions élémentaires
  • Un client peut passer plusieurs commandes.
  • Une commande peut contenir plusieurs produits.
  • Un produit peut apparaître dans plusieurs commandes.
  • Un produit peut appartenir à plusieurs catégories.
  • Un produit peut être fourni par plusieurs fournisseurs.
  • Un fournisseur peut fournir plusieurs produits.
  • Un client peut laisser plusieurs avis sur différents produits.
  • Un produit peut avoir plusieurs avis.
  • Un employé peut gérer plusieurs commandes.
  • Un produit peut être fourni par plusieurs fournisseurs.
  • Un fournisseur peut fournir plusieurs produits.
  • Un produit peut avoir plusieurs prix unitaires différents selon le fournisseur.
Entités retenues
  • Client
  • Commande
  • Produit
  • Catégorie
  • Fournisseur
  • Employé
  • Avis
  • DétailCommande (table de jointure entre Commande et Produit)
  • FournisseurProduit (table de jointure entre Produit et Fournisseur)
Cardinalités
  • Un client peut passer plusieurs commandes (1,N).
  • Une commande peut contenir plusieurs produits, et un produit peut apparaître dans plusieurs commandes (N,N via la table CommandeDetails).
  • Un produit peut appartenir à plusieurs catégories (1,N).
  • Un produit peut être fourni par plusieurs fournisseurs, et un fournisseur peut fournir plusieurs produits (N,N via une table FournisseurProduit).
  • Un client peut laisser plusieurs avis sur différents produits, et chaque produit peut avoir plusieurs avis (N,N).
  • Un employé peut gérer plusieurs commandes (1,N) (optionnel).

2.2 - Proposez un schéma EA de votre analyse

Prenez un moment pour étudier les requêtes et contraintes des parties 3 et 4 avant de finaliser votre conception. Vous pouvez utilisez votre notation préférée pour les cardinalités (1-N, 0-*, CrowFoot, etc.)

Solution possibleDiagramme ERD

2.3 - Après passage de votre schéma EA en production, combien de tables contiendrait votre base de données ?

Solution 11 tables avec les tables de jointure créées pour les relations N-N.

3 - SQL - 3 points - 20 min

En fonction de votre modélisation de la partie 2, rédigez les requêtes SQL suivantes.
Pour les entités en relation N-N qui nécessite une table de jointure, on considérera que ces tables sont nommées de la sorte : table1_table2 ( table1_id, table2_id )

3.1 - Une requête qui affiche les informations des commandes, ainsi que les clients qui y sont rattachés et les produits commandés
Solution
SELECT  * FROM  Commande c
INNER JOIN  Client cl ON c.clientId = cl.clientId
INNER JOIN  DetailCommande dc ON c.commandeId = dc.commandeId
INNER JOIN  Produit p ON dc.produitId = p.produitId;

3.2 - La liste des clients ainsi que le nombre de commandes qu’ils ont effectuées et le total qu’ils ont dépensé.

Si un client n’a pas effectué de commande, on affiche 0 dans le nombre de commandes et le total dépensé. Vous pouvez utiliser la fonction IFNULL(expression, alt_value), où alt_value sera affichée si expression renvoie null.

Solution
SELECT  cl.clientId,  cl.nom AS clientNom,  cl.email AS clientEmail,  cl.adresse AS clientAdresse,  cl.telephone AS clientTelephone,  COUNT(c.commandeId) AS nombreCommandes,  IFNULL(SUM(dc.quantite  * dc.prixUnitaire), 0) AS totalDepense
FROM  Client cl
LEFT JOIN  Commande c ON cl.clientId = c.clientId
LEFT JOIN  DetailCommande dc ON c.commandeId = dc.commandeId
GROUP BY  cl.clientId, cl.nom, cl.email, cl.adresse, cl.telephone
ORDER BY  cl.clientId;

3.3 - Une requête qui affiche les 10 produits les mieux notés

Solution
SELECT  p.produitId,  p.nomProduit,  p.description,  p.prix,  AVG(a.note) AS noteMoyenne
FROM  Produit p
INNER JOIN  Avis a ON p.produitId = a.produitId
GROUP BY  p.produitId,  p.nomProduit,  p.description,  p.prix
ORDER BY  noteMoyenne DESC
LIMIT 10;

4 - Contrainte - 3 points - 20 min

4.1 - Un client ne peut pas laisser d’avis sur un produit qu’il n’a pas commandé.

En fonction de votre modélisation de la partie 2, rédigez un trigger qui permet de vérifier cette contrainte.

Solution possible
DELIMITER //


CREATE TRIGGER before_insert_avis
BEFORE INSERT ON Avis
FOR EACH ROW
BEGIN
    DECLARE num_orders INT;


    -- Compter le nombre de commandes du produit par le client
    SELECT COUNT(*) INTO num_orders
    FROM Commande c
    INNER JOIN DetailCommande dc ON c.commandeId = dc.commandeId
    WHERE c.clientId = NEW.clientId
      AND dc.produitId = NEW.produitId;


    -- Si le client n'a pas commandé le produit, lever une erreur
    IF num_orders = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Le client ne peut pas laisser un avis sur un produit qu''il n''a pas commandé.';
    END IF;
END;


//


DELIMITER ;