SI3 - Exploitation des données

script à télécharger et à exécuter dans phpMyAdmin : localclients.sql

• Rédiger les règles de gestion du SI.

Pour chacune des interrogations, exécuter dans phpMyAdmin le script, puis reportez dans un document de traitement de texte :

1. la question,
2. le script,
3. le nombre d'enregistrements dans la réponse.

Continuer à faire évoluer le tutoriel SQL commencé au TP précédent.

1. les informations du client 17
2. les clients dont le nom commence par un B ou par un C
3. les clients habitant la Basse-Normandie (Région + informations du client)
4. les clients habitant en Bretagne (Région + informations du client) sauf dans les côtes d'Armor
5. les clients de Normandie, et dont l'âge est inférieur à 50
6. La liste des clients classés par âge (croissant), et par ordre du nom (décroissant)
7. les clients attachés à une agence de Bretagne ou pays de la Loire (Région + informations du client)
8. la liste des produits commandés (date commande, libelle et pu) classé par ordre décroissant de Date de commande
9. la liste des produits commandés (date commande, libelle et pu) et les clients correspondants pour la région Bretagne
10. la liste des clients habitant en Basse-Normandie (faire le lien entre partie de gauche du CP et le numéro de département)
11. la référence des produits commandés en Basse-Normandie et en Bretagne
12. toutes les informations relatives à la commande n°17
13. la liste des produits n’ayant jamais été commandés

1. le nombre de clients par département
2. le nombre de clients par région
3. la moyenne d'âge des clients
4. la moyenne d'âge des clients par département
5. le nombre moyen de produits par commande
6. le nombre de commandes par département
7. le montant total de la commande n°11
8. le montant total commandé par département
9. le montant total commandé par région

A faire dans un document sur le Drive.

A partir de la base de données, et de son schéma :

• Rédigez les règles de gestion du système d'information.

Pour chaque question :

• Recopier la question
• Répondre en SQL
• Donner le nombre de réponses
• Afficher la réponse

1. Afficher la liste des Bacs (toutes les infos)
2. Afficher les étudiants habitant à Caen (nom et prénom)
3. Afficher les étudiants n'habitant pas à Caen et n'ayant pas de voiture (nom et prénom)
4. Afficher les étudiants et leur Bac d'origine (nom, prénom, bac)
5. Afficher les étudiants ayant fait un Bac général (nom, prénom, bac)
6. Afficher les étudiants ayant fait une poursuite d'étude après le bac (nom, prénom, libellé de la poursuite d'étude)
7. Afficher la liste des navigateurs utilisés par 1 étudiant précis dont on connaît le nom (afficher les noms des navigateurs)
8. Afficher la liste des utilisateurs de Chrome (afficher nom de l'étudiant)
9. Calculer et afficher le nombre d'étudiants par commune (nom de la commune et nombre)
10. Calculer et afficher le nombre d'étudiants par type de Bac (type de bac et nombre d'étudiant)
11. Calculer et afficher le nombre d'étudiants par navigateur (nom du navigateur et nombre d'étudiant)
12. Afficher les étudiants de la promo actuelle plus âgés que la moyenne des étudiants de leur promo (par ordre décroissant de l'âge)
13. Afficher les 5 jeux les plus populaires, par ordre décroissant de leur utilisation

SQL (Structured Query Language)

• Elaborer un tutoriel SQL présentant la liste des éléments syntaxiques abordés.

• SGDBR (Tables, champs, contraintes, relations…)
• SQL

A partir de Xampp, démarrer les serveurs :

• Mysql
• Apache

Depuis phpMyAdmin, importer la base de données openfootball-all.sql .

A partir de la base de données, et de son schéma :

• Rédigez les règles de gestion du système d'information.

Lister et expliquer les contraintes d'intégrité sur la table match :

• Contraintes de domaine
• Contraintes d'unicité
• Contraintes d'intégrité référentielle

Pour chaque question :

• Recopier la question
• Répondre en SQL
• Donner le nombre de réponses
• Afficher la réponse

1. matchs d’une Poule (afficher toutes les infos du match : équipes, date, lieu)
2. matchs pour 1 journée (17 juin)
3. matchs du week-end (23,24 juin)
4. matchs entre 2 dates (du 14 au 20 juin)
5. matchs de poule de l’équipe de France
6. matchs ayant lieu au stade Rostov Arena
7. matchs de phase 2 ayant lieu à Nizhny Novgorod
8. adversaires rencontrés par la belgique avant son match contre la France

1. équipes ayant joué des prolongations
2. équipes ayant fait au moins 1 match null
3. équipes ayant eu au moins une défaite
4. équipe ayant bénéficié au moins d'un penalty
5. équipes invaincues
6. continents représentés dans la phase 2

1. joueurs ayant marqué un but pour l’équipe d’argentine
2. joueurs ayant marqué plus de 3 buts (sauf o.g.)
3. joueurs ayant marqué contre leur camp (afficher l'équipe)

sur le nombre de buts…

1. au total
2. en phase 1
3. en phase 2
4. en 1ère mi-temps
5. en 2ème mi-temps
6. par poule
7. par jour
8. dans le temps supplémentaire
9. dans les prolongations
10. marqués par penalty
11. marqués contre son camp
12. par équipe, classé par ordre décroissant du nombre de buts
13. marqués par penalty, par équipe, classé par ordre décroissant du nombre de buts
14. Classement du nombre de buts par joueur
15. Classement du nombre de buts par joueur pour les joueurs ayant marqué plus de 3 buts
16. Classement du nombre de buts par joueur (sans tenir compte des penaltys)

sur le nombre de buts moyen…

1. par match
2. par match en phase1
3. par match en phase2
4. en 1ère mi-temps
5. en 2ème mi-temps
6. dans le temps supplémentaire
7. Par poule
8. Par jour

1. Classement selon le nombre de victoires par équipe
2. Classement selon le nombre de défaites par équipe
3. Nombre de buteurs différents par équipe

Vous travaillez comme stagiaire au sein d'une entreprise souhaitant diversifier son activité actuelle en créant un service de vidéo à la demande.

Votre rôle consiste dans un premier temps à élaborer un modèle pour la base de données associée à l'application web de streaming permettant aux clients de sélectionner leurs vidéos. Votre chef de projet vous demande de vous inspirer du leader sur le marché, en naviguant sur son application, et en prenant des notes.

Votre objectif est d'établir les règles de gestion de ce nouveau système d'information (SI) :

1. Identifier les différentes entités (types d'objets ou d'éléments) présents dans les interfaces et nécessaires à l'application (ex : film, genre…). Ce sont les éléments qui seront stockés dans la future base de données.
2. Lister les propriétés de chacune de ces entités (ex: un film a un titre, un réalisateur…)
3. Ajouter une propriété sur chaque entité permettant d'identifier de façon unique l'un de ses éléments.
4. Exprimer les relations entre les entités :

Exemple :

Chaque film peut appartenir à plusieurs genres (de 1 à plusieurs)

Chaque étudiant est décrit par un numéro permettant de l'identifier, un nom, un prenom et un age.

Etudiant: numero, nom, prenom, age

1. Chaque étudiant appartient à une seule section.
2. Chaque section est composée de zéro à plusieurs étudiants.

Appartenir, 0N Section, 11 Etudiant

Ecrire les règles de gestion (entités et relations) en respectant exactement ces 2 modèles.

1. Le script de création de la base de données pour Mysql sera généré à partir de Mocodo.
2. La création de la base dans Mysql se fera à partir de phpMyAdmin ou en mode console.

Dans votre document sur le drive :

• Faire un tutoriel permettant de reproduire la génération du Script à partir de Mocodo, et la génération de la base Mysql.
• Créer la base de données

• Tester la saisie de données (à partir de phpMyAdmin)

Expliquez :

1. Comment sont stockées les données dans la base ?
2. Quels éléments permettent de garantir l'intégrité des données ? Sont-ils suffisants dans la base actuelle ? Que faudrait-il ajouter comme contrôle ?
3. A partir d'un ou plusieurs exemples, montrez comment se traduisent dans la base les règles de gestion.

1. Dans quel ordre faut-il saisir les données dans les tables ? Pourquoi ?
2. A partir des données de instantwatcher, saisir 10 Films et 10 séries dans votre base, ainsi que les autres données associées.

• Notez les problèmes rencontrés, les solutions trouvées.

1. Exporter les données Mysql de la base Films,et copier le script dans le dossier datas du drive.

• Vérifier et modifier chaque table, pour prendre en compte les problèmes rencontrés lors de votre saisie initiale
• Restructurer la base pour prendre en compte les règles de gestion
• Mettre toutes les relations en UPDATE CASCADE
• Modifier la valeur de l'auto-increment des tables (valeurs différentes obligatoires)
• Exporter à nouveau votre base de données (à partir de phpMyAdmin), à mettre sur le drive
  • nom de base streaming
  • Exporter les données uniquement

• Vérifier la cohérence de la nouvelle structure en saisissant les données des films de manière complète (Film, genre, acteurs, films associés…)
• Créer un script batch (.bat) pour importer de manière automatisée les données des autres (via fichier sql).

A partir de la base de données Streaming, répondre en SQL aux interrogations suivantes :

Pour chaque question :

• Recopier la question
• Répondre en SQL
• Donner le nombre de réponses
• Afficher la réponse

1. Liste de tous les genres (toutes les informations)
2. Liste des films (titre et année de sortie)
3. Liste des films dont le titre contient le mot…
4. Liste des films dont le titre ne contient pas le mot…
5. Liste des films dont la date de sortie est comprise entre date1 et date2 (dates à préciser)
6. Liste des films de l'année en cours
7. Liste des acteurs
8. Liste des Films entre 2 dates et contenant le mot…