Ceci est une ancienne révision du document !
TD n°7 -2019
Phase 1
A partir des règles de gestion données à l'oral, établir le schéma de la base de données dans Mysql workbench.
Phase 2
On utilisera pour les exemples la base précédemment élaborée.
- Rédaction de mode opératoire/documentation
- Prise en main de bases de données
- SQL : LDD (Langage de Définition de Données)
A rédiger dans 2 documents différents à déposer sur le Drive
Pour chaque SGDB (MariaDb, SQLite) :
- Présenter le sommairement (caractéristiques, utilisation…)
- Documenter les manipulations suivantes
- Installation
- Création base de données (CREATE DATABASE)
- Création table (CREATE TABLE) [! clé primaire, types de données, auto-increment]
- Modification table (ALTER TABLE)
- Ajout contraintes de clé étrangère (ALTER TABLE … ADD CONSTRAINT…)
- Suppression (DROP TABLE, DATABASE, CONSTRAINT)
- Ajout d'enregistrements (INSERT INTO…)
- Création d'une vue (CREATE VIEW …)
Consignes, pour chaque SGDB :
- Utiliser l'outil en invite de commande.
- Préciser les particularités SQL (non respect des normes)
Liens :
Commandes de base de l'invite de commande
Opération | Mysql | PostGreSQL | SQLite |
---|---|---|---|
Accès à l'invite | mysql\bin\mysql -u root | sqlite dbName.sqlite | |
Lister databases | SHOW DATABASES; | ||
Sélectionner base | USE dbName; | ||
Lister tables | SHOW TABLES; | .tables | |
Infos table | DESCRIBE tableName; | .schema tableName |
TD n°1 : openfootball
A partir des données fournies par openfootball :
- Télécharger les données de la coupe du monde en Russie
Choix logiciel
Tableur, base de données…
Structurer les données
Le travail d'analyse se fera en binômes :
Stocker ces données de manière structurée sur un support informatique en utilisant un outil logiciel adapté :
- évitant les informations en double
- permettant l'établissement de listes (Groupes, équipes, matchs, buteurs…)
- permettant l'établissement de statistiques complètes (par continent, par match, par stade, par poule…)
Création et Saisie
- Créer la base de données
- Saisir les données
Statistiques
Établir la liste des statistiques à élaborer
TD n°2 : openfootball.sql
Fondamentaux
- SGDBR (Tables, champs, contraintes, relations…)
- SQL
Démarrage
A partir de Xampp, démarrer les serveurs :
- Mysql
- Apache
Depuis phpMyAdmin, importer la base de données openfootball.sql .
Saisie
- Etablir un ordre de saisie dans les tables, justifier le.
- Saisir les données
Intégrité des données
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
Données et Statistiques
Établir :
- les listes d'informations utiles à élaborer (exemples : Equipes par poule, matchs par équipe…)
- les statistiques à élaborer
openfootball SQL (LMD)
Liste à obtenir :
- 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
- matchs perdus par l’Allemagne (A ne pas faire)
- é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 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
Base de données à importer :
TD n°4
Compréhension de la Base de données
- Démarrer les serveurs Apache et MariaDb
- Créer la base de données en important le script depuis phpMyAdmin : questionnaire-2018.sql
A partir de MySQL Workbench :
- Générer le shéma de la base de données par reverse Engineering
- Expliquer la structure de la base.
Manipulations SQL
A partir de MySQL Workbench :
Créer les vues permettant de répondre aux questions suivantes :
Dans le document sur le Drive, reprendre les interrogations, et la solution SQL
- Bacs : Afficher la liste des Bacs (toutes les infos)
- EtudiantsDeCaen : Afficher les étudiants habitant à Caen (nom et prénom)
- EtudiantsAPied : Afficher les étudiants n'habitant pas à Caen et n'ayant pas de voiture (nom et prénom)
- EtudiantsBac : Afficher les étudiants et leur Bac d'origine (nom, prénom, bac)
- EtudiantsBacGeneral : Afficher les étudiants ayant fait un Bac général (nom, prénom, bac)
- EtudiantsPostBac : Afficher les étudiants ayant fait une poursuite d'étude après le bac avant d'arriver en SIO (nom, prénom, libellé de la poursuite d'étude)
- EtudiantNavigateur : Afficher la liste des navigateurs utilisés par 1 étudiant précis dont on connaît le nom (afficher les noms des navigateurs)
- ChromeUsers : Afficher la liste des utilisateurs de Chrome (afficher nom de l'étudiant)
- EtudiantsParCommune : Calculer et afficher le nombre d'étudiants par commune (nom de la commune et nombre)
- EtudiantsParBac : Calculer et afficher le nombre d'étudiants par type de Bac (type de bac et nombre d'étudiant)
- EtudiantsParNavigateur : Calculer et afficher le nombre d'étudiants par navigateur (nom du navigateur et nombre d'étudiant)
TD n°5 : Compte Google
A rédiger dans un document à déposer sur le Drive
Gestion de l'historique des recherches et visites, gestion des favoris
- Établir et rédiger les règles de gestion
- Concevoir la structure de la base de données (à partir de Mysql Workbench)
- Créer la base de données depuis Mysql Workbench
- Intégrer des données de test réalistes (compte, favoris, historique) dans la base, pour vérifier sa cohérence
- Intégrer la règle de gestion suivante :
- Les favoris sont stockés dans des dossiers (ayant un nom).
- Ces dossiers sont structurés hiérarchiquement (chaque dossier a éventuellement un parent).
- Répondez en SQL aux interrogations suivantes :
- Informations sur le compte d'un utilisateur (x)
- historique de la journée
- historique entre 2 dates définies (date1 et date2)
- Liste des recherches d'un utilisateur, commençant par un texte précis
- nombre de consultations par site (url)
- moyenne du nombre de site consultés par jour
- liste des 10 sites les plus consultés
- liste des favoris
- liste des favoris dans un dossier précis (dossierA)
- nombre de favoris par dossier
- nombre de favoris par compte, nombre de liens dans l'historique par compte
Td n°6 : Compte Google - Évolutions
A rédiger dans un document à déposer sur le Drive
Évolutions de la base de données :
On souhaite ajouter les informations permettant de sécuriser le compte :
- mot de passe
- Numéro de mobile (pour envoi de SMS de contrôle ou de récupération de password)
- Email de récupération de mot de passe
A chaque connexion dans un nouveau contexte (Lieu, Matériel, logiciels), on mémorisera :
- L'adresse IP de connexion
- Le système d'exploitation et le navigateur utilisés
L'utilisateur du compte a la possibilité de préciser si le contexte est fiable ou non.
Pour chaque contexte, on retiendra également les dates et heures de connexion.
Il est possible de rattacher un ou plusieurs comptes de messagerie externes à son compte Google mail :
Pour chaque compte, on mémorise son adresse, les identifiants de connexion (user + password), ainsi que l'adresse et le port du serveur POP pour la récupération des messages.
- Modifier le schéma relationnel de la base de données
- Mettre à jour la base existante pour intégrer les modifications
- Ajouter des données dans les nouvelles tables (min 3 enreg.)
Interrogation de données
- Lister les informations relatives à un compte (mail, tél, email de récupération)
- Afficher chaque compte, et le nombre de comptes mail associés
- Afficher chaque compte, et les comptes mail associés
- Lister les comptes (principaux) ayant un compte mail associé avec le serveur POP : pop.free.fr
- Afficher la moyenne du nombre de comptes associés par compte principal
- Afficher les contextes des comptes
- Afficher les contextes liés à votre compte
- Comptabiliser le nombre de connexion par contexte sur votre compte
- Afficher la liste des connexions, en précisant le contexte, et le compte