SI3 - Exploitation des données
5. Gestion de clients
Compréhension
Pour chacune des interrogations, exécuter dans phpMyAdmin le script, puis reportez dans un document de traitement de texte :
la question,
le script,
le nombre d'enregistrements dans la réponse.
Continuer à faire évoluer le tutoriel SQL commencé au TP précédent.
Afficher
les informations du client 17
les clients dont le nom commence par un B ou par un C
les clients habitant la Basse-Normandie (Région + informations du client)
les clients habitant en Bretagne (Région + informations du client) sauf dans les côtes d'Armor
les clients de Normandie, et dont l'âge est inférieur à 50
La liste des clients classés par âge (croissant), et par ordre du nom (décroissant)
les clients attachés à une agence de Bretagne ou pays de la Loire (Région + informations du client)
la liste des produits commandés (date commande, libelle et pu) classé par ordre décroissant de Date de commande
la liste des produits commandés (date commande, libelle et pu) et les clients correspondants pour la région Bretagne
la liste des clients habitant en Basse-Normandie (faire le lien entre partie de gauche du CP et le numéro de département)
la référence des produits commandés en Basse-Normandie et en Bretagne
toutes les informations relatives à la commande n°17
la liste des produits n’ayant jamais été commandés
Calculer
le nombre de clients par département
le nombre de clients par région
la moyenne d'âge des clients
la moyenne d'âge des clients par département
le nombre moyen de produits par commande
le nombre de commandes par département
le montant total de la commande n°11
le montant total commandé par département
le montant total commandé par région
4. TD SIO-stats
A faire dans un document sur le Drive.
0.1. Règles de gestion
A partir de la base de données, et de son schéma :
0.2. Interrogation de données (SQL)
Pour chaque question :
Afficher la liste des Bacs (toutes les infos)
Afficher les étudiants habitant à Caen (nom et prénom)
Afficher les étudiants n'habitant pas à Caen et n'ayant pas de voiture (nom et prénom)
Afficher les étudiants et leur Bac d'origine (nom, prénom, bac)
Afficher les étudiants ayant fait un Bac général (nom, prénom, bac)
Afficher les étudiants ayant fait une poursuite d'étude après le bac (nom, prénom, libellé de la poursuite d'étude)
Afficher la liste des navigateurs utilisés par 1 étudiant précis dont on connaît le nom (afficher les noms des navigateurs)
Afficher la liste des utilisateurs de Chrome (afficher nom de l'étudiant)
Calculer et afficher le nombre d'étudiants par commune (nom de la commune et nombre)
Calculer et afficher le nombre d'étudiants par type de Bac (type de bac et nombre d'étudiant)
Calculer et afficher le nombre d'étudiants par navigateur (nom du navigateur et nombre d'étudiant)
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)
Afficher les 5 jeux les plus populaires, par ordre décroissant de leur utilisation
SQL (Structured Query Language)
0.3. SQL tutorial
Fondamentaux
Démarrage
0.4. Règles de gestion
A partir de la base de données, et de son schéma :
0.5. Intégrité des données
Lister et expliquer les contraintes d'intégrité sur la table match :
0.6. Interrogation de données (SQL)
Matchs
matchs d’une Poule (afficher toutes les infos du match : équipes, date, lieu)
matchs pour 1 journée (17 juin)
matchs du week-end (23,24 juin)
matchs entre 2 dates (du 14 au 20 juin)
matchs de poule de l’équipe de France
matchs ayant lieu au stade Rostov Arena
matchs de phase 2 ayant lieu à Nizhny Novgorod
adversaires rencontrés par la belgique avant son match contre la France
Equipes
équipes ayant joué des prolongations
équipes ayant fait au moins 1 match null
équipes ayant eu au moins une défaite
équipe ayant bénéficié au moins d'un penalty
équipes invaincues
continents représentés dans la phase 2
Joueurs
joueurs ayant marqué un but pour l’équipe d’argentine
joueurs ayant marqué plus de 3 buts (sauf o.g.)
joueurs ayant marqué contre leur camp (afficher l'équipe)
Statistiques
sur le nombre de buts…
au total
en phase 1
en phase 2
en 1ère mi-temps
en 2ème mi-temps
par poule
par jour
dans le temps supplémentaire
dans les prolongations
marqués par penalty
marqués contre son camp
par équipe, classé par ordre décroissant du nombre de buts
marqués par penalty, par équipe, classé par ordre décroissant du nombre de buts
Classement du nombre de buts par joueur
Classement du nombre de buts par joueur pour les joueurs ayant marqué plus de 3 buts
Classement du nombre de buts par joueur (sans tenir compte des penaltys)
sur le nombre de buts moyen…
par match
par match en phase1
par match en phase2
en 1ère mi-temps
en 2ème mi-temps
dans le temps supplémentaire
Par poule
Par jour
Classement selon le nombre de victoires par équipe
Classement selon le nombre de défaites par équipe
Nombre de buteurs différents par équipe
1. TD Streaming Service
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.
0.7. Règles de gestion
Votre objectif est d'établir les règles de gestion de ce nouveau système d'information (SI) :
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.
Lister les propriétés de chacune de ces entités (ex: un film a un titre, un réalisateur…)
Ajouter une propriété sur chaque entité permettant d'identifier de façon unique l'un de ses éléments.
Exprimer les relations entre les entités :
Exemple :
Chaque film peut appartenir à plusieurs genres (de 1 à plusieurs)
Ecrans du concurrent principal
Films
Series
Film
Profil utilisateur
MOCODO language
Ecriture des entités (Film, genre...) :
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
Ecriture des relations :
Chaque étudiant appartient à une seule section.
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.
0.8. Base de données
Création
Le script de création de la base de données pour Mysql sera généré à partir de Mocodo.
La création de la base dans Mysql se fera à partir de phpMyAdmin ou en mode console.
Dans votre document sur le drive :
Test
Structure
Expliquez :
Comment sont stockées les données dans la base ?
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 ?
A partir d'un ou plusieurs exemples, montrez comment se traduisent dans la base les règles de gestion.
Saisie
Dans quel ordre faut-il saisir les données dans les tables ? Pourquoi ?
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.
Problèmes/questions
Export
Exporter les données Mysql de la base Films,et copier le script dans le dossier datas du drive.
Restructuration
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
Import
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).
2. Interrogation de données
Bases SQL - LMD
SELECT
FROM
WHERE
Application
A partir de la base de données Streaming, répondre en SQL aux interrogations suivantes :
Pour chaque question :
Liste de tous les genres (toutes les informations)
Liste des films (titre et année de sortie)
Liste des films dont le titre contient le mot…
Liste des films dont le titre ne contient pas le mot…
Liste des films dont la date de sortie est comprise entre date1 et date2 (dates à préciser)
Liste des films de l'année en cours
Liste des acteurs
Liste des Films entre 2 dates et contenant le mot…