ETML - Vennes
Durée du projet : 24p
Chef de projet : Antoine MVENG
J'ai réalisé un MCD (Modèle Conceptuel de Donnée) et automatiquement le MLD (Modèle Logique de Donnée).
Le MCD présente plusieurs entités reliées entre elles par des associations. L’entité CLIENT contient les attributs client_id, nom, prénom, email et téléphone. Elle est reliée à ADRESSE par la relation Habiter, indiquant qu’un client habite à une adresse et qu’une adresse peut être partagée par plusieurs clients. L’entité ADRESSE comporte adresse_id, rue, npa, localite, latitude et longitude. Un client peut également effectuer plusieurs COMMANDES. L’entité COMMANDE contient commande_id, type, date_creation et statut. Une commande peut contenir plusieurs LIGNES_COMMANDE, décrites par ligne_id, quantité et prix_unitaire. Chaque ligne de commande correspond à un ARTICLE, défini par article_id, type, nom, prix_ttc, tva et actif. Chaque commande peut aussi être associée à un PAIEMENT, avec paiement_id, mode, montant et date_paiement. Lorsqu’une commande doit être livrée, elle est reliée à une LIVRAISON, qui comprend livraison_id, date_depart, date_arrivee et statut. Enfin, une livraison est assurée par un LIVREUR, identifié par livreur_id, nom et actif.
Voici le MLD généré automatiquement avec Looping.
J'ai fais un script de création de base de donnée MySQL se trouvant dans :
p_db/table.sql
Dans ce script il y a : La création de la base de donnée et la création des tables.
Ce script a été généré automatiquement à l'aide du logiciel Looping à partir du MCD.
Pour crée les tables il y 2 solutions :
Soit vous prenez le dossier p_db, vous le mettez dans le dossier scripts de docker et éxecutez la commmande ci-dessous :
SOURCE /scripts/p_db/table.sql;
Soit vous copiez le script dans le fichier et vous le collez dans la console.
Le fichier avec tout les LOAD DATA se trouve dans :
p_db/loaddata.sql
Pour pouvoir utiliser les load data il faut se connecter à MySQL en faisant :
mysql --local-infile -u user -ppassword
Ensuite si c'est pas déjà fait :
SET GLOBAL local_infile = 'ON';
Pour chargé les données vous pouvez soit taper la commande ci-dessous :
SOURCE /scripts/p_db/loaddata.sql;
soit vous copiez les commandes ci-dessous une par une (ne faite pas attention aux warnings)
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_client.tsv'
INTO TABLE t_client
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(client_id, nom, prenom, email, telephone);
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_adresse.tsv'
INTO TABLE t_adresse
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(adresse_id, client_fk, rue, npa, localite, latitude, longitude);
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_article.tsv'
INTO TABLE t_article
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(article_id, type, nom, prix_ttc, tva, actif);
Ici j'ai ajouté un SET pour dire que les cellules de adresse_fk qui sont vide deviennent NULL et pour supprimer les espaces indesirables dans les données de status.
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_commande.tsv'
INTO TABLE t_commande
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(commande_id, client_fk, type, @adresse_fk, date_creation, @statut)
SET adresse_fk = NULLIF(@adresse_fk, '');
statut = TRIM(@statut);
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_paiement.tsv'
INTO TABLE t_paiement
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(paiement_id, commande_fk, mode, montant, date_paiement);
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_livreur.tsv'
INTO TABLE t_livreur
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(livreur_id, nom, actif);
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_livraison.tsv'
INTO TABLE t_livraison
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(livraison_id, commande_fk, livreur_fk, statut, date_depart, date_arrivee);
Ici j'ai ajouté un SET pour dire que les cellules de parent_ligne_fk qui sont vide deviennent NULL
LOAD DATA LOCAL INFILE '/scripts/p_db/tsv/t_ligne_commande.tsv'
INTO TABLE t_ligne_commande
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ligne_id, commande_fk, article_fk, quantite, prix_unitaire, parent_ligne_fk)
SET parent_ligne_fk = NULLIF(parent_ligne_fk, '');
Ajout d'une colonne sur chaque table pour récuperer les dernieres modîfications :
ALTER TABLE t_client ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE t_article ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE t_adresse ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE t_livreur ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE t_commande ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE t_paiement ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE t_livraison ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE t_ligne_commande ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Backup complet tout les dimanches :
DATE=$(date '+%Y-%m-%d')
mysqldump -u root -p db_thanospizza > scripts/p_db/db_thanospizza_complet.sql
Backup differentiel tout les jours à miniuit :
DATE=$(date '+%Y-%m-%d')
mysqldump -u root -p db_thanospizza t_client --where="last_update >= '$DATE 00:00:00'" > scripts/p_db/db_thanospizza_diff.sql
mysqldump -u root -p db_thanospizza t_article --where="last_update >= '$DATE 00:00:00'" >> scripts/p_db/db_thanospizza_diff.sql
mysqldump -u root -p db_thanospizza t_adresse --where="last_update >= '$DATE 00:00:00'" >> scripts/p_db/db_thanospizza_diff.sql
mysqldump -u root -p db_thanospizza t_livreur --where="last_update >= '$DATE 00:00:00'" >> scripts/p_db/db_thanospizza_diff.sql
mysqldump -u root -p db_thanospizza t_commande --where="last_update >= '$DATE 00:00:00'" >> scripts/p_db/db_thanospizza_diff.sql
mysqldump -u root -p db_thanospizza t_paiement --where="last_update >= '$DATE 00:00:00'" >> scripts/p_db/db_thanospizza_diff.sql
mysqldump -u root -p db_thanospizza t_livraison --where="last_update >= '$DATE 00:00:00'" >> scripts/p_db/db_thanospizza_diff.sql
mysqldump -u root -p db_thanospizza t_ligne_commande --where="last_update >= '$DATE 00:00:00'" >> scripts/p_db/db_thanospizza_diff.sql
Faire backup complète
Liam Durand appelle la pizzeria pour commander une pizza et les informer qu'il a changé d'Email.
UPDATE t_client
SET email='durand_liam@example.ch'
WHERE id=1;
Un nouveau client vient de demenagé et commande une pizza.
INSERT INTO t_client (nom, prenom, email, telephone)
VALUES ('Braimi', 'Albert', 'albert.braimi@example.ch', '+76 395 97 93');
INSERT INTO t_adresse (rue, npa, localite, latitude, longitude, client_fk)
VALUES ('Rue du Lac 14', '1020', 'Renens VD', '46.5382', '6.5889', '26');
Faire backup differentielle
Le patron vous appelle pour vous dire qu'il y a une perte de données et qu'il faut restaurer la base de données le plus rapidement possible.
D'abord restaurer la backup complète :
mysql -u root -p db_thanospizza < scripts/p_db/db_thanospizza_complet.sql
Ensuite restaurer la backup differentiel :
mysql -u root -p db_thanospizza < scripts/p_db/db_thanospizza_diff.sql
Afficher les dix pizzas les plus vendues (sans les toppings), triés par quantités totales décroissantes.
Vous devez afficher le nom et les quantités.
SELECT a.nom, SUM(lc.quantite) AS total
FROM t_ligne_commande AS lc
JOIN t_article AS a ON lc.article_fk = a.article_id
GROUP BY a.nom
ORDER BY total DESC
LIMIT 10;
Afficher les toppings les plus ajoutés. Le résultat doit être ordonné par le nombre de toppings de manière décroissante. Vous devez afficher le nom et le nombre.
SELECT a.nom, COUNT(*) AS total
FROM t_ligne_commande AS lc
JOIN t_article AS a ON lc.article_fk = a.article_id
WHERE a.type='topping'
GROUP BY a.nom
ORDER BY total DESC;
Afficher le chiffre d’affaires par jour (commandes livrées). Vous ne devez afficher que la date et le chiffres d’affaires (arrondi à 2 chiffres après la virgule).
SELECT DATE(c.date_creation) AS date,
ROUND(SUM(lc.prix_unitaire * lc.quantite), 2) AS chiffre_affaires
FROM t_commande AS c
JOIN t_ligne_commande AS lc ON lc.commande_fk = c.commande_id
WHERE c.statut='livree'
GROUP BY DATE(c.date_creation)
ORDER BY date;
Afficher le chiffre d’affaires par NPA (adresse de livraison).
1ère colonne : npa 2ème colonne : localité 3ème colonne : chiffre d’affaires (arrondi à 2 chiffres après la virgule)
SELECT a.npa, a.localite,
ROUND(SUM(lc.prix_unitaire * lc.quantite), 2) AS chiffre_affaires
FROM t_commande AS c
JOIN t_adresse AS a ON c.adresse_fk = a.adresse_id
JOIN t_ligne_commande AS lc ON lc.commande_fk = c.commande_id
WHERE c.statut='livree'
GROUP BY a.npa, a.localite
ORDER BY chiffre_affaires DESC;
Affiche le nombre de commandes par heure. Il s’agit par cette requêtes de savoir quelles sont les heures « chaudes ». NB : les heures « chaudes » sont des heures pendant lesquelles le nombre de commandes sont les plus élevées.
SELECT HOUR(date_creation) AS heure, COUNT(*) AS nb_commandes
FROM t_commande
GROUP BY HOUR(date_creation)
ORDER BY nb_commandes DESC;
Afficher le nombre de commandes des clients les plus fidèles. Un client est fidèle si son nombre de commandes est ≥ 5 . Afficher le résultat par ordre décroissant du nombre de commandes, puis par ordre alphabétique du nom.
SELECT c.nom, c.prenom, COUNT(*) AS nb_commandes
FROM t_client AS c
JOIN t_commande AS co ON co.client_fk = c.client_id
GROUP BY c.client_id
HAVING COUNT(*) >= 5
ORDER BY nb_commandes DESC, c.nom ASC;
Afficher le total dû par commande. Afficher l’id de la commande et le montant dû (arrondi à 2 chiffres après la virgule). Ordonnez le résultat par ordre croissant des ids de commandes.
SELECT lc.commande_fk AS commande_id,
ROUND(SUM(lc.prix_unitaire * lc.quantite), 2) AS montant_du
FROM t_ligne_commande AS lc
GROUP BY lc.commande_fk
ORDER BY commande_id ASC;
Afficher le total payé par commande (commande ayant au moins un paiement). Afficher l’id de la commande et le total payé (arrondi à 2 chiffres après la virgule). Ordonnez le résultat par ordre croissant des ids de commandes.
SELECT p.commande_fk AS commande_id,
ROUND(SUM(p.montant), 2) AS total_paye
FROM t_paiement AS p
GROUP BY p.commande_fk
ORDER BY commande_id ASC;
Quelle est la répartition des types de commandes.
Ordonner le résultat par le nombre de commande de chaque type, du plus grand au plus petit. 1ère colonne : type 2ème colonne : nombre de commandes de ce type
SELECT type, COUNT(*) AS nb_commandes
FROM t_commande
GROUP BY type
ORDER BY nb_commandes DESC;
Quel est le délai moyen de livraison par livreur (en minutes). Ordonner le résultat par délai moyen en minutes du plus petit au plus grand.
Aide : l’id du livreur, son nom et le délai dans le SELECT.
SELECT l.livreur_id, l.nom,
AVG(TIMESTAMPDIFF(MINUTE, lv.date_depart, lv.date_arrivee)) AS delai_moyen
FROM t_livreur AS l
JOIN t_livraison AS lv ON lv.livreur_fk = l.livreur_id
WHERE lv.statut='livree'
GROUP BY l.livreur_id, l.nom
ORDER BY delai_moyen ASC;
SELECT c.commande_id, c.date_creation, c.statut, cl.nom AS client
FROM t_commande AS c
JOIN t_client AS cl ON c.client_fk = cl.client_id
WHERE c.statut = 'en_livraison' AND c.date_creation > '2025-01-01'
ORDER BY c.date_creation DESC;
Pour récupérer les commandes en livraison après une certaine date et obtenir le nom des clients, un index sur la colonne qui relie les commandes aux clients (client_fk) permet d’accélérer la jointure. Un index combiné sur le statut et la date (statut, date_creation) permet de filtrer rapidement les commandes concernées et de trier efficacement par date, sans que MySQL ait à parcourir toutes les lignes.
CREATE INDEX idx_commande_client_fk ON t_commande(client_fk);
CREATE INDEX idx_commande_statut_date ON t_commande(statut, date_creation);
SELECT a.npa AS zone_npa, COUNT(c.commande_id) AS nb
FROM t_commande AS c
JOIN t_adresse AS a ON c.adresse_livraison_fk = a.adresse_id
WHERE c.type = 'livraison' AND HOUR(c.date_creation) BETWEEN 18 AND 21
GROUP BY a.npa
ORDER BY nb DESC;
Pour compter les commandes par NPA entre certaines heures, un index sur la clé qui relie la commande à l’adresse (adresse_livraison_fk) permet que la jointure avec les adresses se fasse rapidement. Un autre index sur le type de commande accélère le filtrage des livraisons. Enfin, un index sur le NPA facilite le regroupement par zone, et un index sur la date permet de sélectionner rapidement les commandes dans la plage horaire, même si l’utilisation de la fonction HOUR() limite un peu l’efficacité.
CREATE INDEX idx_commande_adresse_fk ON t_commande(adresse_livraison_fk);
CREATE INDEX idx_commande_type ON t_commande(type);
CREATE INDEX idx_adresse_npa ON t_adresse(npa);
CREATE INDEX idx_commande_date_creation ON t_commande(date_creation);
Le fichier avec mes créations de rôles et utilisateurs se trouve dans :
sql/user.sql
L'Administrateur : tous droits sur le schéma, gestion utilisateurs.
/* Création du rôle admin */
CREATE ROLE 'Admin';
GRANT ALL PRIVILEGES
ON db_thanospizza.*
TO 'Admin';
/* Création de l'utilisateur admin */
CREATE USER 'david'@'localhost'
IDENTIFIED BY 'david';
GRANT 'Admin'
TO 'david'@'localhost';
SET DEFAULT ROLE 'Admin'
TO 'david'@'localhost';
Manager : créer/fermer commandes, gérer livraisons, voir paiements, gérer catalogue.
/* Création du rôle manager */
CREATE ROLE 'Manager';
GRANT UPDATE, INSERT, DELETE, SELECT
ON db_thanospizza.t_livraison, db_thanospizza.t_article, db_thanospizza.t_commande
TO 'Manager';
GRANT SELECT
ON db_thanospizza.t_paiement
TO 'Manager';
/* Création de l'utilisateur manager */
CREATE USER 'carl'@'localhost'
IDENTIFIED BY 'carl';
GRANT 'Manager'
TO 'carl'@'localhost';
SET DEFAULT ROLE 'Manager'
TO 'carl'@'localhost';
Pizzaiolo : lecture commandes en préparation
/* Création du rôle pizzaiolo */
CREATE ROLE 'Pizzaiolo';
GRANT SELECT
ON db_thanospizza.t_commande
TO 'Pizzaiolo';
/* Création de l'utilisateur pizzaiolo */
CREATE USER 'peltier'@'localhost'
IDENTIFIED BY 'peltier';
GRANT 'Pizzaiolo'
TO 'peltier'@'localhost';
SET DEFAULT ROLE 'Pizzaiolo'
TO 'peltier'@'localhost';
Livreur : lecture commandes assignées, mise à jour statut livraison (en_livraison → livrée).
/* Création du rôle Livreur */
CREATE ROLE 'Livreur';
GRANT SELECT, UPDATE
ON db_thanospizza.t_livraison, db_thanospizza.t_commande
TO 'Livreur';
/* Création de l'utilisateur livreur */
CREATE USER 'albert'@'localhost'
IDENTIFIED BY 'albert';
GRANT 'Livreur'
TO 'albert'@'localhost';
SET DEFAULT ROLE 'Livreur'
TO 'albert'@'localhost';
Agent de caisse : créer paiements, rembourser (partiel), voir commandes.
/* Création du rôle Agent de caisse */
CREATE ROLE 'Agent de caisse';
GRANT INSERT, UPDATE, DELETE, SELECT
ON db_thanospizza.t_paiement
TO 'Agent de caisse';
/* Création de l'utilisateur agent de caisse */
CREATE USER 'erdem'@'localhost'
IDENTIFIED BY 'erdem';
GRANT 'Agent de caisse'
TO 'erdem'@'localhost';
SET DEFAULT ROLE 'Agent de caisse'
TO 'erdem'@'localhost';
Analyste : lecture seule (SELECT sur tout).
/* Création du rôle analyste */
CREATE ROLE 'Analyste';
GRANT SELECT
ON db_thanospizza.*
TO 'Analyste';
/* Création de l'utilisateur analyste */
CREATE USER 'kyell'@'localhost'
IDENTIFIED BY 'kyell';
GRANT 'Analyste'
TO 'kyell'@'localhost';
SET DEFAULT ROLE 'Analyste'
TO 'kyell'@'localhost';