Table des matières

SI3

Exploitation des données

TD n°1 : Sensibilisation à l'automatisation d'un SI

-- Remplissage

  1. Compléter le questionnaire à partir d'un client pdf (acrobat reader par exemple)
  2. Enregistrer le fichier modifié en le renommant nom.prenom.pdf

-- Structuration et Saisie

La saisie se fera en binôme (l'un pour la saisie, l'autre pour le contrôle).

-- Préparation analyse

L'analyse se fera en binômes :

  1. Lister les exploitations de données à effectuer, préciser pour chacune d'entre elles :
    1. l'objectif général (ce que l'on cherche à montrer)
    2. les données à utiliser (préciser les éventuels calculs)
    3. donner la forme (tableau, chiffre, graphique (type))
  2. Réaliser les calculs nécessaires avec formules (sur une autre feuille que les données)
  3. Pour chaque exploitation de question, préciser le titre, afficher le résultat (graphique, tableau…) et faire un commentaire
  4. Réunir dans un document de type Traitement de texte les exploitations réalisées
  5. Déposer les documents sur le drive

Exemple d'analyses pour les premières données :

Objectif Présentation
Répartition de l’effectif par commune Graphique secteurs
Répartition de l’effectif entre étudiants habitant Caen et autres Graphique secteurs
Répartition de l’effectif entre étudiants habitant Caen et autres en tenant compte de la possession d’un véhicule 2 Graphiques secteurs
Répartition de l’effectif par Bac d’origine Graphique secteurs
Répartion de l’effectif par Type de Bac (Techno, général, professionnel) Graphique secteurs
Répartition de l’effectif entre post-Bac et non Graphique secteurs
Répartion de l’effectif par type de cursus post Bac Graphique secteurs

TD n°2 : Base de données...

Manipulations

Démarrage des serveurs (Apache et MariaDb) :

Importation de la base de donnée à partir d'un fichier de script SQL :

Questions

Pour mieux comprendre la structure des données et la notion d'intégrité…

Pour répondre aux questions, il est nécessaire de savoir :

A rédiger dans un document à déposer sur le Drive

  1. Rédiger un tutoriel des manipulations effectuées
  2. De quelle façon sont stockées les informations dans la base de données ?
    Pistes… Table, champs, relations…
  3. Dans un tableau, mentionner les différents types des champs de la table Etudiant, préciser pour chacun d'eux ce à quoi il correspond (limite des valeurs)
  4. Dans la structure d'une table, préciser le sens des attributs suivants, appliqués à un champ : clé primaire, Null, Default, AutoIncrement
  5. Dans la table étudiant :
    1. quels éléments garantissent l'unicité d'un étudiant ?
    2. quels éléments garantissent la cohérence de la saisie du bac d'origine
    3. de quel manière sont reliés l'étudiant et le Bac qu'il a obtenu ?
  6. Quels éléments garantissent la cohérence de la saisie des navigateurs utilisés
  7. De quel manière sont reliés l'étudiant et les navigateurs qu'il utilise ?
  8. De quel manière sont reliés l'étudiant et son rapport à l'informatique (rapportInformatique)
  9. De quel manière sont reliés l'étudiant et sa formation (post-bac et post-bts) ?
  10. Quelles sont les règles à respecter pour la saisie des données ?

TD n°3 : SQL

A rédiger dans un document à déposer sur le Drive

Notions SQL

A rédiger sous forme de cours/tutoriel

Manipulations SQL

A partir de MySQL Workbench :
Ecrire les requêtes permettant de répondre aux questions suivantes :

Dans le document sur le Drive, reprendre les interrogations, et la solution SQL

  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)

TD n°4 : LMD, exportation de données

A rédiger dans un document à déposer sur le Drive

Dans le document à déposer sur le Drive :

  1. Établir les règles de gestion de la base de données questionnaire_old.sql
  2. Expliquer les différences entre la base old importée et votre nouvelle base
  3. Expliquer en quoi l'ancienne base (old) est plus adaptée à la modification de la structure des questionnaires
  4. Préparer les requêtes pour exporter les données de la base questionnaire_old vers votre base qcmrentree et expliquer votre script
  5. Exposer et expliquer les éventuels problèmes rencontrés et la ou les solutions mises en oeuvre.

TD n°5 : Compte Google

A rédiger dans un document à déposer sur le Drive

Gestion de l'historique et des favoris

Td n°6 : Compte Google - Évolutions

A rédiger dans un document à déposer sur le Drive

Base précédente : comptegoogle2017.sql

Évolutions de la base de données :

On souhaite ajouter les informations permettant de sécuriser le compte :

A chaque connexion dans un nouveau contexte (Lieu, Matériel, logiciels), on mémorisera :

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

Interrogation de données

  1. Lister les informations relatives à un compte (mail, tél, email de récupération)
  2. Afficher chaque compte, et le nombre de comptes mail associés
  3. Afficher chaque compte, et les comptes mail associés
  4. Lister les comptes (principaux) ayant un compte mail associé avec le serveur POP : pop.free.fr
  5. Afficher la moyenne du nombre de comptes associés par compte principal
  6. Afficher les contextes des comptes
  7. Afficher les contextes liés à votre compte
  8. Comptabiliser le nombre de connexion par contexte sur votre compte
  9. Afficher la liste des connexions, en précisant le contexte, et le compte

TD n°7

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

Pour chacune des interrogations, exécuter dans MysqlWorkbench 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.

Afficher

  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

Calculer

  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

TD n°8

Objectifs

A rédiger dans 3 documents différents à déposer sur le Drive

Pour chaque SGDB (Mysql, PostGre, SQLite) :

  1. Présenter le sommairement (caractéristiques, utilisation…)
  2. Documenter les manipulations suivantes
    1. Installation
    2. Création base de données (CREATE DATABASE)
    3. Création table (CREATE TABLE) [! clé primaire, types de données, auto-increment]
    4. Modification table (ALTER TABLE)
    5. Ajout contraintes de clé étrangère (ALTER TABLE … ADD CONSTRAINT…)
    6. Suppression (DROP TABLE, DATABASE, CONSTRAINT)
    7. Ajout d'enregistrements (INSERT INTO…)
    8. Création d'une vue (CREATE VIEW …)

Consignes, pour chaque SGDB :

Liens :

Commandes de base de l'invite de commande

Opération Mysql PostGreSQL SQLite
Accès à l'invite mysql\bin\mysql -u root postgreSQL\bin\psql -U postgres sqlite dbName.sqlite
Lister databases SHOW DATABASES; \l
Sélectionner base USE dbName; \c dbName;
Lister tables SHOW TABLES; \dt; .tables
Infos table DESCRIBE tableName; \d+ tableName; .schema tableName

TD n°2 : Base de données...

Questions

Pour mieux comprendre la structure des données…

A rédiger dans un document à déposer sur le Drive

  1. De quelle façon sont stockées les informations dans la BDD questionnaire ?
    Pistes… Table, champs, relations…
  2. Déterminer comment trouver le nombre d'étudiants nés en 1996
  3. Pour la question choix d'option pour le S2, retrouvez les réponses possibles et expliquez comment le faire
  4. Retrouver les navigateurs utilisés par un étudiant et expliquer comment les retrouver
  5. Retrouver l'âge des débuts en info pour 1 étudiant et expliquer comment le retrouver
  6. Comment retrouver la liste des réponses possibles pour 1 question ?
  7. Comment retrouver la liste des questions possibles pour 1 questionnaire ?

TD n°3

Objectifs de réalisation

  1. Créer une vue à partir de Mysql Workbench
    1. Avec jointures
    2. avec fonctions de regroupement
  2. Modifier une vue
  1. Créer un graphique à partir de l'application Java
  2. Choisir le type de graphique
  3. Personnaliser le graphique en utilisant les options présentes dans l'application
  4. Personnaliser le graphique en utilisant l'API Google charts

Exemples de vues

Vue âge :

select concat((date_format(from_days((to_days(`questionnaire`.`groupe`.`dNaiss`) - to_days(`activequestionnaire`.`dNaiss`))),'%Y') + 0),' ans') AS `Age`,count(0) AS `Effectif` 
from (`questionnaire`.`activequestionnaire` 
join `questionnaire`.`groupe` on((`questionnaire`.`groupe`.`id` = `activequestionnaire`.`idGroupe`))) 
group by concat((date_format(from_days((to_days(`questionnaire`.`groupe`.`dNaiss`) - to_days(`activequestionnaire`.`dNaiss`))),'%Y') + 0),' ans') desc

Vue ville

CREATE VIEW v_ville as SELECT reponse.libelle as Ville ,count(*) as Effectif
FROM (reponsemultiple INNER JOIN reponse ON reponsemultiple.idReponse=reponse.id) INNER JOIN question ON reponse.idQuestion=question.id 
WHERE question.id=11
GROUP BY reponse.libelle 
ORDER BY 2 DESC;

TD n°3 SQL

Script à télécharger et à exécuter dans phpMyadmin :
questionnaire-2016.sql

TD n°4 SQL

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

Pour chacune des interrogations, exécuter dans MysqlWorkbench 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.

Afficher

  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

Calculer

  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

Autres bases de données

PostgreSQL

  • Vérifiez la présence et au besoin installer postgreSQL Server
    (https://www.postgresql.org/download/windows/)
  • Créez la base de données localclients.sql
  • Effectuez les mêmes interrogations qu'avec Mysql
  • Dans un document sur le Drive :
    • Présenter sommairement Postgre
    • notez les différences entre PostgreSQL et Mysql

SQLite

  • Télécharger sqlite (https://sqlite.org/download.html)
  • Créez la base de données localclients.sql
  • Effectuez les mêmes interrogations qu'avec Mysql
  • Dans un document sur le Drive :
    • Présenter sommairement SQLite
    • notez les différences entre SQLite et Mysql

DS1

  • Implémenter le schéma physique la base de données DS1 (Tables, relations) dans MysqlWorkbench
  • Générer la base de données Mysql
  • Intégrer 100 enregistrements par table en les générant à partir de l'application generateData.com
  • Créer et exécuter les 8 requêtes SQL

DS2

Documents à stocker sur le drive (TD6)

  1. Ajouter les instructions de type LDD suivantes à votre mémento SQL :
    • CREATE DATABASE
    • USE
    • ALTER TABLE
    • PRIMARY KEY ou ADD PRIMARY KEY
    • ADD FOREIGN KEY
  1. Générer la base de données dans mysql
  2. Ajouter des données exemple (manuellement)
  3. Créer les vues correspondant aux interrogations SQL

Td n°7 : Compte Google

Gestion des données personnelles : historique, favoris, mots de passe

  • Établir et rédiger les règles de gestion (Document à créer sur le drive dans un dossier perso du dossier TD7)
  • Concevoir la structure de la base de données (à partir d'un outil en ligne)
  • Créer la base de données en utilisant les instructions SQL de type LDD
  • Intégrer des données de test réalistes (compte, favoris, historique)

-->