Soit le modèle relationnel suivant :

station(idStation,nomStation,altitude,pays)

competition(idCompetition,libelleCompet,dateComp,#station_id)

classement(#skieur_id,#competition_id,classement)

comporte(#competition_id, #specialite_id)

skieur(idSkieur ,nomSkieur,#specialite_id,#station_id)

specialite(idSpecialite,libelleSpecialite)

SQL, création des tables et importation des données

Écrire dans un fichier script_tp2.sql le script de création des tables correspondant au modèle ci dessus.

• Supprimer toutes les tables si elles existent.

• Créer toutes les tables si elles n'existent pas.

(Utiliser les mots clés IF EXISTS lors de la suppression de la table et IF NOT EXISTS lors de la création des tables.)

• Insérer les données des fichiers csv joints : utiliser les fichiers CLASSEMENT.csv SKIEUR.csv COMPETITION.csv SPECIALITE.csv STATION.csv COMPORTE.csv (les fichiers COMPORTEv2.csv CLASSEMENTv2.csv COMPETITIONv2.csv sont prévus pour la fin du TP)

SQL, traitement de l’information

Écrire les requêtes SQL suivantes :

1. Nombre de skieurs ayant participé à au moins une compétition.

+-------------------------+
| NbreSkieurDansUneCompet |
+-------------------------+
|                       6 |
+-------------------------+

(exemple avec les données jointes)

2. Nom de la station de chaque skieur (affichage : nom skieur + nom station)

+-----------+--------------------+
| nomSkieur | nomStation         |
+-----------+--------------------+
| robert    | Chambery           |
| bernard   | Le Ballon d'alsace |
| alberto   | Metabief           |
| jacques   | Tignes             |
| paul      | Tignes             |
| pierre    | Tignes             |
| tom       | Tignes             |
| edouard   | Verbier            |
+-----------+--------------------+

(exemple avec les données jointes)

3. Classement de la compétition de libellé ‘compet’ (affichage : nom skieur + classement)

+-----------+------------+---------------------------+
| nomSkieur | classement | libelleCompet             |
+-----------+------------+---------------------------+
| pierre    |          1 | compet cadet france       |
| tom       |          2 | compet cadet france       |
| jacques   |          3 | compet cadet france       |
| robert    |          4 | compet cadet france       |
| edouard   |          5 | compet cadet france       |
| paul      |          1 | compet junior france      |
| pierre    |          2 | compet junior france      |
| robert    |          3 | compet junior france      |
| jacques   |          4 | compet junior france      |
| tom       |          5 | compet junior france      |
| edouard   |          6 | compet junior france      |
| paul      |          1 | competition cadet europe  |
| jacques   |          2 | competition cadet europe  |
| edouard   |          3 | competition cadet europe  |
| robert    |          4 | competition cadet europe  |
| jacques   |          1 | competition junior europe |
| pierre    |          2 | competition junior europe |
| edouard   |          3 | competition junior europe |
| robert    |          4 | competition junior europe |
+-----------+------------+---------------------------+

(exemple avec les données jointes)

4. Liste des compétitions s’étant déroulées à ‘Tignes’ (nom de station) avec leur vainqueur (affichage : libellé Compétition, nom skieur vainqueur)

+--------------------------+-----------+
| libelleCompet            | nomSkieur |
+--------------------------+-----------+
| compet cadet france      | pierre    |
| compet junior france     | paul      |
| competition cadet europe | paul      |
+--------------------------+-----------+

(exemple avec les données jointes)

5. Nombre de compétitions se déroulant dans chaque station (affichage : id station + nom station + nb de compétition)

+-----------+------------+-------------+
| idStation | nomStation | nbrDeCompet |
+-----------+------------+-------------+
|         3 | Chambery   |           2 |
|         1 | Tignes     |           5 |
|         2 | Verbier    |           1 |
+-----------+------------+-------------+

(exemple avec les données jointes)

6. Nombre de victoires à ‘Tignes’ pour chaque skieur (affichage : id skieur + nom skieur + nb victoires)

+----------+-----------+-----------------+
| idSkieur | nomSkieur | NbreDeVictoires |
+----------+-----------+-----------------+
|        2 | pierre    |               1 |
|        3 | paul      |               2 |
+----------+-----------+-----------------+

(exemple avec les données jointes)

7. Noms des skieurs ayant toujours terminé premier (nom skieur)

+-----------+
| nomSkieur |
+-----------+
| paul      |
+-----------+

(exemple avec les données jointes)

Question 3 : analyse du MCD

Utiliser un des 2 logiciels suivants :

• Looping-MCD
• JMerise

Utiliser de préférence looping

Établir le modèle conceptuel des données associé à ce modèle relationnel.

requêtes avancées

Commencer par ajouter des enregistrements :

• supprimer le contenu des tables comporte classement et competition avec l’instruction DELETE
• insérer des enregistrements avec les fichiers COMPORTEv2.csv CLASSEMENTv2.csv COMPETITIONv2.csv, il devrait y avoir des erreurs car les valeurs des clés primaires continues de s’incrémenter pour compétition
• pour la table competition, utiliser l’instruction TRUNCATE pour supprimer les enregistrements. Il faudra peut être autoriser l’instruction TRUNCATE, avec mettant la variable d’environement FOREIGN_KEY_CHECKS à 0 puis à 1 : SET FOREIGN_KEY_CHECKS = 0; ... SET FOREIGN_KEY_CHECKS = 1;

Vérifier les requêtes ci-dessous avec les nouveaux enregistrements

• R8 : Noms des skieurs et leurs classements ayant toujours terminé dans les 3 premiers à “Tigne”, afficher aussi le club(la station) du skieur

+-----------+------------+-------------+----------+
| nomSkieur | classement | lieu_compet | club     |
+-----------+------------+-------------+----------+
| pierre    |          1 | Tignes      | Tignes   |
| pierre    |          2 | Tignes      | Tignes   |
| paul      |          1 | Tignes      | Tignes   |
| alberto   |          1 | Tignes      | Metabief |
+-----------+------------+-------------+----------+

• R9 : Noms des skieurs ayant toujours terminé dans les 3 premiers “à la maison” (dans leur station)

+-----------+
| nomSkieur |
+-----------+
| pierre    |
| paul      |
+-----------+

• En vous aidant du document :

[http://perso.modulonet.fr/placurie/Ressources/BTS1-ALSI/Evaluation-5%20(correction).pdf]

(http://perso.modulonet.fr/placurie/Alsi1.htm)

• R10 : Modifier le MLD : sans supprimer la table, ajouter une colonne dans la table « classement » de nom « spécialite_id », créer une clé primaire sur cette table, ajouter une colonne « temps ». Un skieur ne peut pas se classer plusieurs fois dans la même spécialité. Mais si on supposait que un skieur peut se classer plusieurs fois dans la même spécialité lors de la même compétition, il faudrait ajouter une colonne « passage » ou « heureDepart ». (2.3)

• R11 : Donner la liste des stations dont sont originaires au moins 2 skieurs

+------------+
| nomStation |
+------------+
| Tignes     |
+------------+

• R12 : Donner la liste des skieurs qui pratiquent la même spécialité que le skieur « paul »

+-----------+
| nomSkieur |
+-----------+
| tom       |
| alberto   |
+-----------+

• R13 : Insérer dans la table SKIEUR le skieur «alphand» qui a la même spécialité que le skieur «paul» et la même station que le skieur «pierre» (4.2)

• R14 : Afficher les compétitions avec plus de 2 spécialités

+--------------------------------+
| libelleCompet                  |
+--------------------------------+
| championnat cadet europe 2020  |
+--------------------------------+

• R15 : Afficher les skieurs dans le même club que “tom”

+-----------+
| nomSkieur |
+-----------+
| pierre    |
| paul      |
| jacques   |
| alphand   |
+-----------+

• R16 : Afficher les skieurs avec un classement égal ou meilleur que celui de “tom”

+-----------+
| nomSkieur |
+-----------+
| pierre    |
| paul      |
| jacques   |
| edouard   |
| alberto   |
+-----------+

• R17 : Afficher les skieurs qui ont plus ou au moins autant de victoires que “pierre”

+-----------+
| nomSkieur |
+-----------+
| jacques   |
| paul      |
+-----------+

• R18 : Créer une transaction qui supprime la station «chambery» et déplace toutes les compétions dans la station «valoire» (4.4)