DUPUIS Tristan
ROBERT Arthur
LE2 TDA2
COMPTE RENDU TP n°1
CSI
Table des matières: 1
Introduction 1
Cahier des charges 1
Modèle conceptuel de données 3
Modèle logique de données 5
Modèle physique de données 7
Données de test et requêtes 9
Conclusion 13
Application WEB 13
Introduction
Pour ce premier TP, nous allons devoir suivre un cahier des charges et toute une démarche
afin de réaliser le modèle conceptuel, logique et physique afin de répondre aux questions et
au problème.
Cahier des charges
Le système d’information que vous devez développer doit permettre d’enregistrer et de
restituer les informations nécessaires à l’utilisation habituelle des horodateurs. Les
nouveaux horodateurs de la ville permettront aux usagers et usagères de déclarer et payer
leur stationnement. Lorsqu’une personne utilise un horodateur et valide son paiement, les
informations sont remontées au système d’information général de la ville. Ce système
d’information rédige un ticket de stationnement qui est ensuite renvoyé à l’horodateur pour
impression. Les informations restent stockées dans le système d’information de la ville pour
permettre d’administrer le parc d’horodateurs ou pour une analyse ultérieure par les services
d’urbanisme. De plus, cela permettra d’offrir un service de consultation et de prolongation
des tickets de stationnement par les usagers et usagères du service, à travers une
1
application web dont vous aurez aussi la charge. Dans sa démarche d’analyse du cahier des
charges, le pôle d’ingénierie des systèmes de votre entreprise vous soumet le diagramme
de la Figure 1.
1) De quel diagramme (vu en cours) s’agit-il ?
Il s’agit d’un diagramme de cas d’utilisation
2
Modèle conceptuel de données
2) Proposez un modèle entité-association permettant de répondre au cahier des
charges précédentes. Expliquez la signification et justifiez la présence de chaque
entité et de chaque association. +
3) Justifiez les valeurs de chacune des multiplicités choisies dans votre modèle.
Pour ma part j’ai décidé de faire le modèle entité-association à la main sur une feuille, voici
ce que nous avons fait :
Un ticket comprend toutes les données de la voiture (plaque d’immatriculation) ainsi que
les informations propres au stationnement (date et heure d’arrivée, durée totale, le total à
payer). Il dispose aussi d’un id pour que les utilisateurs puissent consulter leur ticket.
Chaque ticket est référencé à un seul et unique horodateur. Cela vient du fait que l’on ne
peux modifier le ticket uniquement depuis le même horodateur . Il comprend un identifiant
permettant de le différencier ainsi que son numéro de rue. Un horodateur peut référencer
plusieurs tickets. Il se situe dans un seule rue qui peut posséder plusieurs horodateurs (un
3
au minimum car sinon on ne la référence pas).
Chaque rue dépend d’une seule et unique zone qui possède son propre tarif horaire.
Une zone comprend au moins une rue.
4) Faites-vous figurer explicitement le coût total d’un ticket dans votre modèle
conceptuel ? Quelle que soit votre réponse, donnez un argument pour et un argument
contre le fait de stocker explicitement cette donnée. Expliquez pourquoi vous avez fait
ce choix.
Nous faisons apparaître explicitement le coût total d’un ticket dans notre modèle conceptuel
afin de permettre à un administrateur de les observer s’il le souhaite sans avoir besoin de
faire des calculs pour ressortir tous les prix.
Mais un argument contre le fait de stocker le prix total est que ca utilise de la place et il y a
des données “inutiles” dans la base de donnee au final, alors qu’on pourrait tout simplement
faire le calcul du prix total au moment de l’impression du ticket.
Donc d’un côté c’est bien de le faire figurer dans notre modèle conceptuel mais d’un autre
côté ça ne l’est pas.
La ville vous indique qu’elle souhaite pouvoir faire des statistiques à partir des données de
stationnement : durée moyenne de stationnement par zone, fréquentation des différentes
zones par voiture, coût moyen des tickets
5) Sans les appliquer, expliquez quelles modifications éventuelles vous devriez
apporter à votre modèle pour permettre à la ville de réaliser ces statistiques.
Justifiez.
Pour réaliser ces statistiques on pourrait ajouter une entité “Voiture” par exemple qui
contient la plaque d’immatriculation en attribut qui est associée à une autre entité qui
répertorie les différentes zones fréquentées, le prix des tickets et la durée. Donc en
enlevant les attributs qui sont déjà présents dans d’autres entités afin de les répertorier à
part.
Afin d’enrichir les statistiques, la ville souhaite aussi pouvoir conserver l’adresse de
résidence des propriétaires de chaque véhicule ayant utilisé le système de stationnement.
Cette adresse peut être retrouvée sur des bases de données municipales ou préfectorales.
6) Sans les appliquer, expliquez quelles modifications éventuelles vous devriez
apporter à votre modèle pour permettre à la ville de conserver l’adresse de résidence
des propriétaires de véhicules. Justifiez.
Il faudrait que l’on ajoute une entité utilisateur avec l’adresse de résidence. A laquelle serait
reliée une entité voiture comportant l’attribut “plaque d’immatriculation” (car un utilisateur
pourrait avoir plusieurs voitures) qui serait associé à l’entité ticket. Mais cela comporte trop
de données supplémentaires et d'entités en plus afin d’avoir juste une adresse de domicile
d’un utilisateur.
4
Modèle logique de données
7) Effectuez la traduction de votre modèle entité-association en modèle relationnel.
Pensez notamment à identifier les clés primaires et les clés étrangères.
Nous avons utilisé le logiciel dbDiagram:
8) Expliquez quelle règle de traduction vous avez choisie pour chaque association ou
héritage et justifier ces choix.
Nous avons décidé de mettre en clé primaire dans chaque table, les id(ex:idHorodateur,
idTicket etc). Pour les clés étrangères, nous avons suivi la forme du MEA.
Mais nous aurions
aussi pu mettre l’id de la zone dans lequel il est valable, l’id de la rue etc, mais ca prendrai
trop de valeur dans une table, nous verrons par la suite si nous sommes bloqués ou non.
Donc la table ticket relié à la table horodateur avec l’id horodateur, la table horodateur relié à
la table rue avec l'idRue et la table Rue reliée avec la table zone grâce à l'idZone.
Pendant la réunion d’avancement auprès du personnel de la mairie, une des personnes en
charge du suivi de votre projet vous demande s’il est possible de faire évoluer le tarif horaire
5
d’une zone avec votre solution. Cette question émerge parce que la mairie a pour projet
d’augmenter le tarif horaire de la zone B de 10 centimes d’euro à partir du 1 er janvier de
l’année prochaine.
9) Votre solution permet-elle de prendre en compte cet exemple de modification
tarifaire ? Cela risque-t-il d’avoir un impact sur les tickets déjà enregistrés ? Sans les
appliquer, expliquez quelles modifications éventuelles vous devriez apporter à votre
modèle pour prendre en compte cette demande. Justifiez.
Si on suit notre modèle, les ticket deja enregistre ne seront pas impactés car nous avons
décidé de mettre le prix total directement en donnée. Mais si jamais nous n’avions pas fait
comme cela, on pourrait mettre une condition au moment de la récupération des données ou
de l’affichage, il suffirait d’afficher les prix des tickets en faisant un calcul de 10 centimes de
+ mais seulement pour les tickets qui ont une DateArrivee supérieur à la date du 1 er janvier
de l'année voulu.
6
Modèle physique de données
10) Identifiez quel SGBD est installé sur votre machine : MySQL ou MariaDB.
Nous utilisons MariaDB sur notre machine car c’est le SGBD qui nous a été demandé
d’installer l’année dernière.
11) À partir du modèle relationnel précédent, concevez le script SQL de création de
tables, ou créez vos tables directement dans PHPMyAdmin. Pensez à identifier les
types MySQL ou MariaDB qui conviennent le mieux. Fournissez, en annexe de votre
compte-rendu, votre script SQL ou un export par PHPMyAdmin.
Le logiciel avec lequel nous avons fait le modèle relationnel permet de générer le script sql.
CREATE TABLE `Ticket` (
`idTicket` int PRIMARY KEY,
`plaqueImmat` varchar(10), //souvent de la forme XX-XXX-XX
`dateArrivee` date, //permet de mettre une date au format '2021-09-30'.
`heureArrivee` datetime,
`duree` time, //permet de definir la duree en heure minutes et seconde
`idHorodateur` int,
`totalPayer` float //le prix est en euros et centimes donc c’est un chiffre a virgule
);
CREATE TABLE `Horodateur` (
`idHorodateur` int PRIMARY KEY,
`idRue` int,
`numeroRue` varchar(10) // prévoit l’utilisation des numéros bis
);
CREATE TABLE `Rue` (
`idRue` int PRIMARY KEY,
`nomRue` varchar(30), // chaîne de caractères
`idZone` int
);
CREATE TABLE `Zone` (
`idZone` int PRIMARY KEY,
`nomZone` varchar(20) //Prévision de la question 13 pour le varchar
`tarifHoraire` float //float car le prix horaire n’est pas rond, c’est en euro et centime
);
ALTER TABLE `Horodateur` ADD FOREIGN KEY (`idRue`) REFERENCES `Rue`
(`idRue`);
ALTER TABLE `Ticket` ADD FOREIGN KEY (`idHorodateur`) REFERENCES `Horodateur`
(`idHorodateur`);
ALTER TABLE `Rue` ADD FOREIGN KEY (`idZone`) REFERENCES `Zone` (`idZone`);
7
12) Justifiez chaque choix de type ainsi que la taille/longueur associée, s’il en y a.
Voir commentaires du code de la question 11
Alors que vous venez de présenter le modèle physique de données pour exposer
l’avancement de votre travail auprès de la mairie, vous entendez parler d’un projet de
nouvelle zone tarifaire « A Hypercentre », dont le tarif horaire serait le même que la zone A
mais dont la durée de stationnement serait limitée à 2 heures.
13) Votre implémentation serait-elle en mesure d’intégrer les informations de cette
future zone tarifaire ? Si oui, insérez cette zone tarifaire dans votre base et donnez la
requête d’insertion. Si non, pour quelle(s) raison(s) ? Sans les appliquer, expliquez
quelles modifications vous devriez apporter à votre modèle physique pour que ce soit
le cas.
Dans notre modèle relationnel nous ne pouvons pas intégrer les informations de cette future
zone tarifaire mais il existe existe la possibilité d'utiliser la “CHECK (duree<=2 AND
idZone =n)” , IdZone=n correspond à la zone AHypercentre.
On devrait pour cela ajouter l’idZone dans notre table Ticket, pour voir dans quelle zone est
référencé le ticket.
8
Données de test et requêtes
14) Entrez
ces données dans la base, le plus fidèlement possible. Si vous devez modifier les
données pour pouvoir les intégrer, expliquez comment et pourquoi.
Afin de répondre à la question, on insère les données de la table ci-dessus en faisant:
INSERT INTO `Zone` (`idZone`, `tarifHoraire`, `nomZone`) VALUES ('1', '3', 'A');
INSERT INTO `Zone` (`idZone`, `tarifHoraire`, `nomZone`) VALUES ('2', '2', 'B');
INSERT INTO `Zone` (`idZone`, `tarifHoraire`, `nomZone`) VALUES ('3', '1.5', 'C');
INSERT INTO `Rue` (`idRue`, `nomRue`, `idZone`) VALUES ('1', 'rue du Chateau', '1'), ('2',
'place des Fleurs', '1'),('3', 'boulevard du Bourg', '2'), ('4', 'passage Grisaille', '3'),('5', 'allee
du Beton', '3');
INSERT INTO `Horodateur` (`idHorodateur`, `idRue`, `numeroRue`) VALUES ('1', '1', '10'),
('2', '2', '14'),('3', '3', '32'), ('4', '4', '85'),('5','5','173');
INSERT INTO `Ticket` (`idTicket`, `plaqueImmat`, `dateArrivee`, `heureArrivee`, `duree`,
`idHorodateur`, `totalPayer`) VALUES ('1', 'AA-123-BB', '2021-09-26', '2021-09-26
16:12:00', '1’, '1', NULL),('2', 'CC-456-DD', '2021-09-27', '2021-09-27 10:26:00', '3', '2',
NULL),('3', 'AA-123-BB', '2021-09-27', '2021-09-27 10:26:00', '01:30:00', '3', NULL),
('4', 'ZZ-789-ZZ', '2021-09-28', '2021-09-28 14:58:00', '00:30:00', '4', NULL),
('5', '135 XY 79', '2021-09-28', '2021-09-28 17:22:00', '00:45:00', '4', NULL),
('6', 'AA123BB', '2021-09-29', '2021-09-29 09:09:09', 01:00:00', '5', NULL),
('7', 'ZZ-789-ZZ', CURRENT_DATE, CURRENT_TIME, 06:00:00', '2', NULL);
9
Voici ci-dessus toutes les tables dans notre base de données.
Les requêtes à réaliser sont les suivantes :
(a) Liste des horodateurs. Retourner la liste des horodateurs : adresse, nom de la zone tarifaire et
coût horaire pour chacun.
(b) Liste des zones tarifaires. Retourner la liste des zones tarifaires : nom, coût horaire et nombre
d’horodateurs rattachés à chacune.
(c) Liste des tickets. Retourner les informations de tous les tickets de stationnement : plaque
d’immatriculation, date et heure du début de stationnement, durée prévue de stationnement, heure de
fin de stationnement, adresse de l’horodateur, nom et coût horaire de la zone tarifaire, et coût total du
ticket.
(d) Tickets du véhicule AA-123-BB. Reprendre la question (c) mais uniquement pour les tickets du
véhicule ayant la plaque d’immatriculation AA-123-BB.
(e) Tickets de la journée. Reprendre la question (c) mais uniquement pour les tickets qui sont datés
du jour, que le stationnement soit terminé ou non. Cette requête devra aussi fonctionner n’importe
quel autre jour.
(f) Tickets en cours du véhicule ZZ-789-ZZ. Reprendre la question (c) mais uniquement pour les
tickets en cours (stationnement non terminé) et pour le véhicule ayant la plaque d’immatriculation
ZZ-789-ZZ. 15)
Réalisez les requêtes SQL (a) à (f) ci-dessus. Pour chaque requête, rappelez son titre
dans votre compte-rendu, puis donnez la requête elle-même, son résultat, et vos
commentaires éventuels (concernant la requête et son résultat).
(a) :
SELECT [Link], [Link], [Link], [Link]
FROM `Horodateur` AS H JOIN `Rue` AS R ON
([Link]=[Link])
JOIN `Zone` AS Z ON
([Link]=[Link]);
10
(b):
SELECT [Link], [Link], COUNT(idHorodateur)
FROM `Horodateur` AS H JOIN `Rue` AS R ON
([Link]=[Link])
JOIN `Zone` AS Z ON
([Link]=[Link])
GROUP BY [Link];
(c):
SELECT [Link], [Link], [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link]
FROM `Ticket` AS T JOIN `Horodateur` AS H ON
([Link]=[Link])
JOIN `Rue` AS R ON
([Link]=[Link])
JOIN `Zone` AS Z ON
([Link]=[Link]);
11
(d):
SELECT [Link], [Link], [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link]
FROM `Ticket` AS T JOIN `Horodateur` AS H ON
([Link]=[Link])
JOIN `Rue` AS R ON
([Link]=[Link])
JOIN `Zone` AS Z ON
([Link]=[Link])
WHERE [Link] = ‘AA-123-BB’;
(e):
SELECT [Link], [Link], [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link]
FROM `Ticket` AS T JOIN `Horodateur` AS H ON
([Link]=[Link])
JOIN `Rue` AS R ON
([Link]=[Link])
JOIN `Zone` AS Z ON
([Link]=[Link])
WHERE [Link] = CURRENT_DATE; //récupère la date d’aujourd’hui
(f):
SELECT [Link], [Link], [Link], [Link], [Link], [Link],
[Link], [Link], [Link], [Link]
FROM `Ticket` AS T JOIN `Horodateur` AS H ON
([Link]=[Link])
JOIN `Rue` AS R ON
([Link]=[Link])
JOIN `Zone` AS Z ON
([Link]=[Link])
WHERE [Link] = 'ZZ-789-ZZ' AND (([Link] + TIME([Link])) >
CURRENT_TIME) //la fonction TIME() récupère l’heure de la date et heure
12
Nous n’avons pas beaucoup de commentaire sur nos requêtes actuelles étant donné que
nous avons déjà fait cela l'année dernière, nous avons mis des commentaire sur les
utilisations des nouvelles fonction (nous en avions déjà vu certaines l'année dernière comme
DATE()). Les commentaires sont en vert.
Conclusion
Ce TP nous a permis de tester nos récentes connaissances dans le modèle
entité-association. Le fait de travailler en équipe est bénéfique car on peut voir une approche
différente de nos solutions pour les modèles entités-associations. Le fait de continuer les
requêtes SQL permet de faire un rappel sur les connaissances acquises de l’année
dernière. Nous n’avons pas rencontré de difficultés particulières sur ce TP.
Nous avons juste découvert de nouvelles fonctions telles que TIME(), TIMESTAMP(), ...
L’utilisation du rpi n’était pas pénalisant cependant, un de nos 2 rpi400 a freeze dès lors que
nous utilisions dbDiagram. Ce qui a amené à utiliser votre pc personnel pour modéliser le
modèle relationnel.
Application WEB
En cours…
13