Différences

Ci-dessous, les différences entre deux révisions de la page.

--- si3 [2018/11/12 14:08] – [Manipulations SQL] jcheron
+++ si3 [2019/08/31 14:20] (Version actuelle) – modification externe 127.0.0.1
@@ Ligne 1: / Ligne 1: @@
+===== 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.
+=== Objectifs : ===
+  * Rédaction de mode opératoire/documentation
+  * Prise en main de bases de données
+  * SQL : LDD (Langage de Définition de Données)
+<wrap round info>A rédiger dans 2 documents différents à déposer sur le Drive</wrap>
+=== Travail à faire ===
+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 :**__
+  * https://sqlite.org/download.html
+  * https://mariadb.com/kb/en/library/sql-statements-structure/
+==== Commandes de base de l'invite de commande ====
+|<100%>|
+^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 =====
@@ Ligne 130: / Ligne 179: @@
 A partir de MySQL Workbench :\\
-Ecrire les requêtes permettant de répondre aux questions suivantes :
+Créer les vues permettant de répondre aux questions suivantes :
 Dans le document sur le Drive, reprendre les interrogations, et la solution SQL
-  - Afficher la liste des Bacs (toutes les infos)
+  - Bacs : Afficher la liste des Bacs (toutes les infos)
-  - Afficher les étudiants habitant à Caen (nom et prénom)
+  - EtudiantsDeCaen : 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)
+  - EtudiantsAPied : 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)
+  - EtudiantsBac : 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)
+  - EtudiantsBacGeneral : 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 avant d'arriver en SIO (nom, prénom, libellé de la poursuite d'étude)
+  - 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)
-  - Afficher la liste des navigateurs utilisés par 1 étudiant précis dont on connaît le nom (afficher les noms des navigateurs)
+  - EtudiantNavigateur : 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)
+  - ChromeUsers : 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)
+  - EtudiantsParCommune : 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)
+  - EtudiantsParBac : 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)
+  - EtudiantsParNavigateur : Calculer et afficher le nombre d'étudiants par navigateur (nom du navigateur et nombre d'étudiant)
+===== TD n°5 : Compte Google =====
+<wrap round info>A rédiger dans un document à déposer sur le Drive</wrap>
+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
+{{::comptegoogle.zip|}}
+  * 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 =====
+<wrap round info>A rédiger dans un document à déposer sur le Drive</wrap>
+==== É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.
+<html>
+<div class="todo">
+<ul>
+<li>Modifier le schéma relationnel de la base de données</li>
+<li>Mettre à jour la base existante pour intégrer les modifications</li>
+<li>Ajouter des données dans les nouvelles tables (min 3 enreg.)</li>
+</ul>
+</div>
+</html>
+==== 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