TP db Bdd Ligue 1 foot PARTIE 1

TP_db Bdd – Ligue 1 foot – PARTIE 1

Tp1_bdd_corrige.docx page 1 / 13

OBJECTIFS : On se propose dans ce Tp :

- De constituer et d’utiliser une base de données contenant des informations sur le championnat de ligue 1 de football 2021-22

- De créer une page web qui demande de saisir le numéro d’une journée de championnat + le nom d’une équipe et qui affiche en retour le résultat du match correspondant.

Cet objectif sera atteint en 4 parties :

- Partie 1 : Constitution de la bdd en écrivant les requêtes Sql dans phpMyAdmin : o création des tables, o insertion « manuelle » d’un petit nombre d’enregistrements, o tests de l’efficacité du modèle relationnel utilisé.

- Partie 2 : On recommence le travail de la partie 1 en utilisant à présent

une communication http locale entre le navigateur web et le serveur sur lequel se trouve la bdd et les fichiers Php.

- Partie 3 : On constitue le fichier Php qui permettra d’afficher la page web présentée ci-dessus. On teste l’ensemble en local (le serveur Web virtuel Apache MySQL Php crée avec le logiciel UwAmp se trouve sur votre ordinateur).

- Partie 4 : On télécharge son travail sur le serveur Apache d’un site clone de nsibranly.fr afin de le tester sur un serveur distant. On en profite pour analyser les requêtes http échangées entre le navigateur sur votre ordinateur et le serveur distant (serveur Ovh sur la région Lilloise) .

1. Création de la table equipes :

La structure de la table equipes est donnée sur le tableau ci-contre. Elle permet de stocker des données diverses, propres à chacune des 20 équipes de ligue 1.

Lancer le logiciel UwAmp et ouvrir l’outil PHPMyAdmin. Se connecter avec l’identifiant root et le mot de passe root.

equipes

id INT(6) AUTO_INCREMENT

nom VARCHAR(40)

ville VARCHAR(40)

classement VARCHAR(20)

stade VARCHAR(50)

budget INT(4)

PRIMARY KEY(id)



Ouvrir l’onglet SQL et exécuter le commande SQL :

Créer la table equipes en exécutant la requête suivante :

Ouvrir l’onglet Structure et vérifier que les différents attributs de la table equipes ont été créés correctement.

Indique que l’on travaille dans la bdd ligue1

On sépare 2 commandes SQL par un ;

Les commandes SQL peuvent être écrites en minuscules ou majuscules (insensible à la casse)

Cette dernière commande permet d’utiliser un moteur de recherche compatible avec les clés étrangères (vrai pour les bdd MySql seulement)

Indique que les caractères utilisés seront codés en utf-8

Les attributs correspondent aux

colonnes de la tables



Insérer les enregistrements correspondants aux 2 équipes phares du championnat en exécutant la commande SQL :

Comme l’attribut id est en AUTO_INCREMENT, on aurait pu exécuter une commande équivalente qui permet de n’insérer qu’une partie des attributs. Utilisez cette technique pour insérer le club de l’OM :

Ouvrir l’onglet Afficher pour vérifier le contenu de la table equipes :

Exécuter les commandes SQL de sélection suivantes et indiquer pour chacune d’elles leur signification en français :

Commande SQL Signification

Sélectionne toutes les colonnes de la table equipes pour toutes les lignes (car la condition après WHERE est toujours vraie)

Même commande que la précédente car 1 est équivalent à TRUE

Donne le nom et le nom du stade de l’équipe de Lyon

Les différents enregistrements sont séparés par une ,



Donne l’id et le nom des équipes dont le budget est supérieur à 300 M€ et aussi de l’équipe de Marseille

Met à jour l’attribut budget des lignes de la table equipes qui comporte les caractères ‘seill’ dans l’attribut ville.

Donne le budget moyen de toutes les équipes de la table equipes

Donne le budget moyen de toutes les équipes de la table equipes avec un alias sur ce résultat

Sélectionne tous les attributs pour les lignes de la table equipes pour lesquelles le budget est inférieur à 1000 M€. Les résultats sont classés alphabétiquement selon l’attribut ville.

Donne le nombre de lignes dans la table equipes

2. Création de la table joueurs :

La structure de la table joueurs est donnée sur le tableau de droite ci-contre. Cette table permet de stocker des données diverses propres à chacun des joueurs de ligue 1.

equipes


nom VARCHAR(40)

ville VARCHAR(40)


stade VARCHAR(50)

budget INT(4)

PRIMARY KEY(id)

joueurs


nom VARCHAR(40)

id_equipes INT(6)

pays VARCHAR(5)

age INT(3)

taille INT(3)

poids INT(3)

PRIMARY KEY(id)

FOREIGN KEY(id_equipes) REFERENCES equipes(id) On lit ces 2 tables en créant une contrainte de CLE ETRANGERE



Ouvrir l’onglet SQL et exécuter la commande SQL suivante qui permet de créer la table joueurs :

Ecrire la commande SQL qui permettent d’insérer dans la table joueurs les 2 lignes suivantes :

Commande SQL Résultat

Compléter le tableau suivant en donnant selon les cas, soit la commande SQL , soit sa signification en français, soit tout simplement en exécutant la commande pour la comprendre :

Commande SQL Signification

Sélectionne la taille moyenne de tous les joueurs

Même commande que la précédente avec un alias et une moyenne du poids des joueurs

Sélectionne les villes des équipes qui ont des joueurs dans la table joueurs

Sélectionne les villes des équipes qui ont des joueurs dans la table joueurs et qui respectent la condition (age > 20)

Sélectionne toutes villes des équipes qui respectent la condition (budget > 10), pas seulement les villes qui ont des joueurs dans la table joueurs.



Exécuter la commande suivante. Elle affiche une erreur car il y a ambiguïté sur le nom des attributs.

En effet, l’attribut nom existe dans la table equipes (nom de l’équipe) et dans la table joueurs (nom du joueur).

Exécuter la commande équivalente suivante, qui permet de lever cette ambigüité :

Exécuter la commande équivalente suivante, qui permet de simplifier l’écriture en créant des alias. On utilisera ce type d’écriture dans la suite.

Compléter le tableau suivant en exécutant les commandes SQL suivantes, en donnant du sens à chacune d’elles et en insérant la copie d’écran du résultat affiché dans phpMyAdmin :

Commande SQL Copie d’écran du résultat

Comme les 2 colonnes affichées ont le même nom, on préfère :



3. Création des tables matchs et dates :

Les structures des tables matchs et dates sont données ci-dessous. Ces 2 tables permettent de stocker les données propres à chacun des matchs du championnat de ligue 1.

Créer la table dates en exécutant une commande SQL :

Insérer dans la table dates une seule ligne pour l’instant. Elle indique que la 1ère journée de championnat a eu lieu le 8 août 2021.

Créer la table matchs en exécutant une commande SQL :

equipes


nom VARCHAR(40)

ville VARCHAR(40)


stade VARCHAR(50)

budget INT(4)

PRIMARY KEY(id)

joueurs


nom VARCHAR(40)

id_equipes INT(6)

pays VARCHAR(5)

age INT(3)

taille INT(3)

poids INT(3)

PRIMARY KEY(id)

FOREIGN KEY(id_equipes) REFERENCES equipes(id)

matchs


id_equipes_hote INT(6)

id_equipes_visiteur INT(6)

num_journee INT(2)

PRIMARY KEY(id)

FOREIGN KEY(id_ equipes _hote) REFERENCES equipes(id)

FOREIGN KEY(id_ equipes_visiteur) REFERENCES equipes(id)

FOREIGN KEY( num_journee) REFERENCES dates( num_journee )

dates

num_journee INT(2) AUTO_INCREMENT

jour VARCHAR(40)

PRIMARY KEY( num_journee )

On lit ces 2 tables en créant une contrainte de CLE ETRANGERE

On lit ces 2 tables en créant 2 contraintes de CLE ETRANGERE



Insérer dans la table matchs , pour l’instant uniquement le match du dimanche 8 août 2021 (num_journee = 1) entre Paris qui reçoit Lyon (id_equipes_hote = 1 et id_equipes_visiteur = 2 ) :

Commande SQL Résultat

Compléter le tableau suivant en exécutant les commandes SQL suivantes, en donnant du sens à chacune d’elles et en insérant la copie d’écran du résultat affiché :

Commande SQL Copie d’écran du résultat



4. Création de la table buts :

La structure de la table buts est donnée ci-dessous. Cette table permet de stocker les données propres à chacun des buts marqués durant le championnat :

Les tables equipes, joueurs, matchs contiennent pour l’instant :

equipes


nom VARCHAR(40)

ville VARCHAR(40)


stade VARCHAR(50)

budget INT(4)

PRIMARY KEY(id)

joueurs


nom VARCHAR(40)

id_equipes INT(6)

pays VARCHAR(5)

age INT(3)

taille INT(3)

poids INT(3)

PRIMARY KEY(id)


matchs




num_journee INT(2)

PRIMARY KEY(id)




dates


jour VARCHAR(40)


buts


id_joueurs INT(6)

id_matchs INT(6)

temps INT(3)

csc INT(1)

PRIMARY KEY(id)

FOREIGN KEY(id_ joueurs ) REFERENCES joueurs(id)

FOREIGN KEY(id_ matchs) REFERENCES matchs(id) csc = 1 si le joueur marque contre son camp csc = 0 sinon

temps : le but a été marqué « temps » minutes après le début du match.



Créer la table buts en exécutant la commande SQL :

Kylian Mbappé a marqué un but (csc=0) le dimanche 8 août 2021 à la 32ième minute. Insérer l’enregistrement correspondant dans la table buts :

Kylian Mbappé marque 2 autre buts (csc=0) à la 42ième minute , puis à la 92ième minute. Insérer les enregistrements correspondant dans la table buts :

Moussa Dembélé marque 4 buts (csc=0, 53ième + 61ième + 87ième minute) . Insérer les enregistrements correspondant dans la table buts :



Exécuter les commandes SQL suivantes, en donnant du sens à chacune d’elles et en insérant la copie d’écran du résultat affiché :

Commande SQL :

Copie d’écran du résultat :

Commande SQL :


Commande SQL :




On constate ainsi que l’on lie les 5 tables en utilisant les liaisons ci-dessous :

Ce qui entraine la syntaxe SQL :

et la table ci-dessous qui donne les attributs de toutes les tables :

equipes e


nom VARCHAR(40)

ville VARCHAR(40)


stade VARCHAR(50)

budget INT(4)

PRIMARY KEY(id)

joueurs j


nom VARCHAR(40)

id_equipes INT(6)

pays VARCHAR(5)

age INT(3)

taille INT(3)

poids INT(3)

PRIMARY KEY(id)


matchs m




num_journee INT(2)

PRIMARY KEY(id)




dates d


jour VARCHAR(40)


buts b


id_joueurs INT(6)

id_matchs INT(6)

temps INT(3)

csc INT(1)

PRIMARY KEY(id)

FOREIGN KEY(id_ joueurs ) REFERENCES joueurs(id)

FOREIGN KEY(id_ matchs) REFERENCES matchs(id)



Compléter la commande ci-dessous qui permet de sélectionner l’age et le nom des buteurs de l’équipe visiteur de la première journée de championnat :

Cette première partie est terminée. Ses objectifs étaient :

o de découvrir et créer la Bdd qui permet de stocker toutes les informations liées au championnat de ligue 1 de football,

o de pratiquer le Sql en tapant au clavier des lignes de commande d’insertion, de mise à jour et de sélection,

o d’avancer dans l’objectif

principal que l’on s’est fixé au départ et qui est de créer une page web qui affiche le résultat d’un match quelconque du championnat.

On continue à présent sur la seconde partie de ce travail. Son objectif sera de créer la Bdd complète avec toutes les équipes, tous les joueurs, tous les matchs mais avec uniquement les buts marqués au cours des 8 premières journées …. et oui ce championnat n’est pas terminé à ce jour.

On n’utilisera plus phpMyAdmin pour écrire les requêtes Sql. Elles seront écrites dans un fichier php qui sera exécuté via votre navigateur de la façon suivante :

o On écrit dans l’Url du navigateur (Client) l’adresse du fichier php,

o Ce fichier se trouve sur le serveur Web qui accueille aussi la Bdd. Il soumettra la requête Sql à cette Bdd. Si des données sont retournées par un SELECT par exemple, celles-ci sont redirigées vers le navigateur (Client). La communication Client <--> Serveur se fait en utilisant le protocole internet http.

Documents

TP db Bdd Ligue 1 foot PARTIE 1