Address
12 Rue de Cléry, 75002 Paris

Work Hours
Jeudi et Vendredi: de 9H à 18H

Facebook Twitter Instagram YouTube

MYSQL

Révision Générale 

Modélisation Méthode Merise et SQL 

Contexte : service de bagagerie à la SNCF 

Un voyageur identifié par un nom, prénom, adresse, date de naissance, mail, tél et un numéro de billet peut déposer dans le service de bagagerie de la SNCF un ou plusieurs bagages. Chaque bagage est connu par un numéro, un poids, et trois dimensions. Un casier identifié par un numéro, une lettre de l’allée où il se trouve, une capacité peut contenir un ou plusieurs bagages. Dès le dépôt d’un bagage dans un casier, on enregistre la date et l’heure du dépôt. 

Les casiers sont classés en trois catégories, petit, moyen et grand. Chaque catégorie a un prix unique, c’est celui du casier. 

Modéliser ce système d’information. Élaborer le modèle conceptuel de données et le modèle logique de données. 

Solution MCD : 

Solution MLD : 

Script de la base de données 

#------------------------------------------------------------
#        Script MySQL.
#------------------------------------------------------------
DROP DATABASE IF EXISTS Bagagerie; 
CREATE DATABASE Bagagerie; 
USE Bagagerie; 

#------------------------------------------------------------
# Table: VOYAGEUR
#------------------------------------------------------------

CREATE TABLE VOYAGEUR(
        ID_VOYAGEUR    Int  Auto_increment  NOT NULL ,
        NOM            Varchar (250) NOT NULL ,
        PRENOM         Varchar (250) NOT NULL ,
        ADRESSE        Varchar (250) NOT NULL ,
        DATE_NAISSANCE Date NOT NULL ,
        MAIL           Varchar (250) NOT NULL ,
        TEL            Varchar (250) NOT NULL ,
        NUMERO_BILLET  Varchar (250) NOT NULL
,CONSTRAINT VOYAGEUR_PK PRIMARY KEY (ID_VOYAGEUR)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: BAGAGE
#------------------------------------------------------------

CREATE TABLE BAGAGE(
        NUMERO      Int  Auto_increment  NOT NULL ,
        POIDS       Varchar (250) NOT NULL ,
        HAUTEUR     Float NOT NULL ,
        LONGUEUR    Float NOT NULL ,
        LARGEUR     Float NOT NULL ,
        ID_VOYAGEUR Int NOT NULL
,CONSTRAINT BAGAGE_PK PRIMARY KEY (NUMERO)

,CONSTRAINT BAGAGE_VOYAGEUR_FK FOREIGN KEY (ID_VOYAGEUR) REFERENCES VOYAGEUR(ID_VOYAGEUR)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: CAT_CASIER
#------------------------------------------------------------

CREATE TABLE CAT_CASIER(
        ID_CAT    Int  Auto_increment  NOT NULL ,
        CATEGORIE Varchar (50) NOT NULL ,
        PRIX      Float NOT NULL
,CONSTRAINT CAT_CASIER_PK PRIMARY KEY (ID_CAT)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: CASIER
#------------------------------------------------------------

CREATE TABLE CASIER(
        NUMERO   Int  Auto_increment  NOT NULL ,
        ALLEE    Char (2) NOT NULL ,
        CAPACITE Int NOT NULL ,
        ID_CAT   Int NOT NULL
,CONSTRAINT CASIER_PK PRIMARY KEY (NUMERO)

,CONSTRAINT CASIER_CAT_CASIER_FK FOREIGN KEY (ID_CAT) REFERENCES CAT_CASIER(ID_CAT)
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: DEPOSER
#------------------------------------------------------------

CREATE TABLE DEPOSER(
        NUMERO        Int NOT NULL ,
        NUMERO_BAGAGE Int NOT NULL ,
        DATEDEPOT     Date NOT NULL ,
        HEUREDEPOT    Time NOT NULL
,CONSTRAINT DEPOSER_PK PRIMARY KEY (NUMERO,NUMERO_BAGAGE)

,CONSTRAINT DEPOSER_CASIER_FK FOREIGN KEY (NUMERO) REFERENCES CASIER(NUMERO)
,CONSTRAINT DEPOSER_BAGAGE0_FK FOREIGN KEY (NUMERO_BAGAGE) REFERENCES BAGAGE(NUMERO)
)ENGINE=InnoDB;

Insertion des données : 

Table Catégorie : 

INSERT INTO CAT_CASIER VALUES (null, "Large", 17), (null, "Moyen", 10), (null, "Petit", 5);

Table Casier : 

INSERT INTO CASIER VALUES (null, "A", 120, 3),(null,  "B", 120,3),(null, "C", 500,2),(null,"D", 700, 1);

Table Voyageur : 

INSERT INTO VOYAGEUR VALUES (null, « Wassim », « Dan », « 20 rue de Cléry Paris », « 2000-12-12 », « d@mail.com », « 0612561406 », « 123 »), 

INSERT INTO VOYAGEUR VALUES (null, « Wassim », « Dan », « 20 rue de Cléry Paris », « 2000-12-12 », « d@mail.com », « 0612561406 », « 123 »), 
(null, « Julia », « Garance », « 25 rue de Limoges Paris », « 2001-11-11 », « j@mail.com », « 0612561406 », « 456 »),
(null, « Tom », « Clara », « 34 rue de Lyon Paris », « 2002-10-10 », « t@mail.com », « 0612561406 », « 789 »);

Table Bagage : 

INSERT INTO BAGAGE VALUES (null, 20, 10, 10, 10, 1), (null, 30, 20,20, 20, 2), 
(null, 40, 13,13, 13, 2), (null, 50, 20,10, 10, 3), (null, 25, 12, 12,12, 1);

Table Déposer : 

INSERT INTO DEPOSER VALUES (1, 1, « 2022-09-09 », « 10:40 »), 
(2, 1, « 2022-10-09 », « 15:00 »),( 2, 3, « 2022-08-10 », « 12:30 »),
( 3, 2, « 2022-11-09 », « 12:00 »),( 4, 2, « 2022-12-09 », « 08:40 »);

Série de Requêtes :

  1. Lister les casiers avec leur catégorie (numéro, allée, capacité, catégorie, prix)
SELECT c.numero, c.allee, c.capacite, cat.categorie, cat.prix 
FROM casier c, cat_casier cat 
WHERE c.id_cat = cat.id_cat ;
  1. Quel est le prix moyen des casiers présents dans la Gare. 
SELECT avg(prix) AS prix_moyen FROM cat_casier ;
  1. Quel est le nombre de bagages enregistrés dans la base de données
SELECT count(numero) AS nb_bagages FROM bagage ;
  1. Quel est le nombre de bagages qui ont été déposés par le voyageur “wassim”
SELECT count(numero) AS nb_bagages 
FROM bagage b, voyageur v 
WHERE b.id_voyageur = v.id_voyageur AND v.nom = “wassim”;
  1. Quel est le nombre de bagages déposés dans le casier numéro 2 durant l’année 2022 
SELECT count(b.numero) AS nb_bagages 
FROM bagage b, casier c, deposer d 
WHERE b.numero = d.numero_bagage AND c.numero = d.numero AND c.numero = 2 AND year(d.datedepot) = « 2022 »;
  1. Quel est le nombre de bagages déposés dans les casiers Large durant le mois de septembre 2022. 
SELECT count(b.numero) AS nb_bagages 
FROM bagage b, casier c, deposer d , cat_casier cat
WHERE b.numero = d.numero_bagage and c.numero = d.numero AND c.id_cat = cat.id_cat AND cat.categorie = « large » AND  d.datedepot like  « 2022-09% »;
  1. Quel est le nombre de casier classés dans la catégorie “Moyen” 
SELECT count(c.numero) AS nb_casiers 
FROM casier c , cat_casier cat 
WHERE c.id_cat = cat.id_cat AND cat.categorie = « Moyen »;

Comparaison deux date en tenant compte des heures (HOUR et TIMEDIFF)

//ne compte pas les minutes
SELECT *
FROM Demande
WHERE HOUR(TIMEDIFF(dateConfirmation, dateDemande)) > 24;

//compte tout
SELECT * FROM Demande WHERE ADDDATE(dateDemande, INTERVAL 1 DAY) < dateConfirmation;

//plus les valeur null
SELECT * FROM Demande WHERE (dateConfirmation is not null AND ADDDATE(dateDemande, INTERVAL 1 DAY) < dateConfirmation) OR (dateConfirmation is null AND ADDDATE(dateDemande, INTERVAL 1 DAY) < NOW());

//version court
SELECT * FROM Demande WHERE ADDDATE(dateDemande, INTERVAL 1 DAY) < IFNULL(dateConfirmation, NOW());