# TP2 - SQL, Normalisation, programmation concurrentielle, images, dash board

## Exercice 1 : Dépendances fonctionnelles et normalisation
On considère dans cet exercice une gestion simplifiée de groupes d'étudiants et d'enseignants. Soient la relation **Cours(Année, Etudiant, Groupe, Professeur, Matiere)** et ses dépendances fonctionnelles $F = \{$
$Annee\cup Etudiant \rightarrow Groupe,$
$Groupe \cup Matiere \rightarrow Professeur,$
$Professeur \rightarrow Matiere$
$\}$
1. Donner la signification de chaque dépendance fonctionnelle.
2. Donner la clé primaire de la relation
3. Justifier pourquoi la relation **Cours** n'est pas en 3FN
4. Normaliser en 3FN pour obtenir une décomposition sans perte d'information et sans perte de dépendance fonctionnelle.
5. Après avoir précisé si votre décomposition est en BCFN ou bien simplement en 3FN, indiquer
- la ou les dépendances fonctionnelles perdues si votre relation est en FNBC,
- déduire une décomposition en FNBC si votre relation est en 3FN.

## Exercice 2 : Image et classification supervisée
Dans cet exercice, on cherche à appliquer un algorithme de classification d'images depuis une base sqlite.

1. Charger la base de données `imagesetlabels` et lire la table `images`.
2. Donner les attributs, la clé primaire de la relation obtenue.
4. Ecrire une fonction python qui prend en paramètre un `label` (str) et extrait les images ayant le label `label` dans la base de données grâce à requête SQL
5. Récupérer les images pour chaque label dans une liste python.
:::info
Noter que la lecture d'une image peut se faire avec opencv avec:

img = cv2.imread("nomdevotreimage.jpg")

- le type de `img` est `np.array`
:::
6. Récupérer le programme python `classifie.py` sur moodle et entraîner le modèle pour classifier l'image chargée précédemment.

## Exercice 3 : Image blob en sql, traitement simple d'images et matrice creuse
Dans cet exercice, je vous propose de manipuler des images stockées dans une base de données SQLite `imagesexo1.db` au format BLOB.
1. Récupérer et lire la base de données sqlite `imagesexo1` et la table `imageslettres` sur moodle.
2. Décrire brièvement le contenu de cette base.
3. Ecrire un programme permettant de charger les images d'identifiant 5 à 10 dans une liste.

Pour réduire la taille des images, on se propose de binariser les images et de charger la position des pixels non nuls dans une nouvelle table.

4. Ecrire le script qui binarise l'image à l'aide d'une fonction qui traite les tableaux numpy de chaque image de la base.
5. Récupérer la position des pixels non nuls.
6. Créer une nouvelle table sql avec le contenu de ces pixels

## Exercice 4 : Commerce
La table ci-dessous et l'ensemble des dépendances fonctionnelles décrivent des achauts récents effectués par des clients aurpès d'un vendeur de fournitures de bureau.

| AID | ADate | CID | CNom | CVille | PID | PDesc | Pprix | Qté |
| ---- | ----- | --- | ---- | ------ | --- | ----- | ----- | --- |
| 1010 | 10/09/20 | 2 | Alpha | Strasbourg | 12,5,9 | Table,Bureau,Chaise | 900,435,200 | 3,5,12 |
| 1024 | 07/12/20 | 5 | Delta | Chambéry | 7,9 | Tableau,Chaise | 400,200 | 3,34 |

**AID** : Identifiant de l'achat ; **ADate** : Date d'achat ; **CID** : Identifiant du client
**CNom** : Nom client ; **CVille** : Ville du client ; **PID** : identifiant Produit
**Pdesc** : Nom produit ; **Pprix** : Prix produit ; **Qté** : Quantité achetée

$F=\{ AID \rightarrow ADate\cup CID, CID \rightarrow CVille \cup CNom,$
$PID \rightarrow PDesc \cup PPrix, PID \cup AID \rightarrow Qté \}$

1. Passez cette table en 3FN.

## Exercice 4 : dataset google store
Le dataset `googlestore` contient des informations sur les apps téléchargés depuis google store sur android. Donner les programmes permettant de :
1. Charger le dataset dans une base sqlite depuis un programme python
2. Nettoyer le dataset si besoin (`replace`)
3. En SQL, donner la moyenne des notes des apps
4. En SQL, donner les catégories de manière unique
5. En SQL, donner les 5 premières catégories avec les meilleures notes
6. Ecrire, en SQL, la requête permettant d'avoir toutes les apps ayant une note de 5 étoiles
7. Ecrire, en SQL, le nombre total d'apps payantes, et gratuites
8. Donner les 5 apps ayant le nombre d'installation le plus élevé.
9. Tracer avec matplotlib la répartition des apps installés par catégorie.

## Exercice 5 : dataset
Dans cet exercice, nous considérons le dataset des smartphones vendus. A l'aide de Pandas et de SQL,
1. Effectuer un prétraitement en supprimant les doublons et les tuples sans valeurs (na).
2. Donner les 5 smartphones les mieux notés.
3. Donner sous forme d'histogramme, la répartition du nombre de smartphones par marque.
4. Donner la répartition des prix sous forme d'histogramme
5. Ajouter toute autre information utile.

## Exercice 6 : dataset artistes,musique
Télécharger la base de données **.sqlite** sur moodle. Le contenu de cette base est celui d'un magasin de musique numérique et apporte des informations sur les artistes, les albums, les pistes audio, les clients, les factures et les employés. Écrire et tester les requêtes en langage SQL :
0. Lister les tables présentes avec la requête sql

select * from sqlite_master where type="table"

1. Choisir une des tables de la base et donner la liste de ses attributs.
2. Combien d'artistes sont répertoriés dans cette base?
3. Quelle est la plus courte piste de blues ? La plus longue piste de classique ?
4. Combien d'albums (et leurs noms) ont été faits par le groupe The Doors?
5. Combien d'heures faudrait-il pour écouter tous les morceaux de musique?
6. Quels artistes ont réalisé plus de dix albums?
7. Quelle est la durée moyenne des pistes de rock?
8. Combien de pistes sont sur la playlist Brazilian Music?
9. Qui sont les clients français?
10. Récupérer une table donnant les morceaux et leur genre respectif sous la forme d'un DataFrame.

## Exercice 7. des tableaux dynamiques !
Le but de cet exercice est de vous faire utiliser Dash pour afficher et filtrer de manière dynamique des données provenant d'une base de données SQLite. Nous allons travailler sur la base `pays.csv` déjà utilisée en TD. Aller voir la doc de dash [ici](https://dash.plotly.com/minimal-app).
1. Tout d'abord, installer les dépendances

pip install dash,psycopg2

2. Le programme ci-dessous constitue la base de votre application. Modifier et exécuter-le pour afficher les données de votre base :

import dash
from dash import html, dcc,dash_table
import plotly.express as px
import plotly.graph_objects as go

from dash.dependencies import Input, Output
import plotly.express as px
import pandas as pd
import numpy as np
import sqlite3

app = dash.Dash(__name__)

dfpays = pd.read_csv("pays.csv",delimiter=';', thousands=',', encoding='utf-8')
dfpays=dfpays.replace('\u202f','',regex=True)
dfpays=dfpays.replace('\t','',regex=True)

dfpays['Superficie'] = dfpays['Superficie'].replace(" ",'',regex=True)
dfpays['Superficie'] = dfpays['Superficie'].replace(",",'.',regex=True)
dfpays['Superficie'] = dfpays['Superficie'].astype(np.float64)



app.layout = html.Div([
    html.H1('Pays'),
    dash_table.DataTable(
    data=dfpays.to_dict('records'),
    css=[{'selector': 'table', 'rule': 'table-layout: fixed'}],
    style_data={
        'whiteSpace': 'normal'    },
        fill_width=False,id='tab')
])

if __name__ == '__main__':
    app.run(jupyter_mode="tab",debug=True)

3. Vérifier que l'exécution du programme ci-dessus affiche bien une table, l'objet intéressant ici est `dash_table.DataTable`. L'attribut `data` de cette table est un `DataFrame` pandas.
4. Ajouter un slider `dcc.Slider` dont les valeurs s'étalent de 0 à 90. Ce slider va nous permettre de filtrer la table pays selon l'attribut Superficie.

5. Ajouter une fonction de callback après le layout. Cette fonction doit être exécutée à chaque fois que l'utilisateur intéragit avec un élément de la page, ici le slider. Vous pouvez trouver un exemple basique ci-dessous.

import dash
from dash import html, dcc,dash_table
import plotly.express as px
import plotly.graph_objects as go

from dash.dependencies import Input, Output
import plotly.express as px
import pandas as pd
import numpy as np
import sqlite3

app = dash.Dash(__name__)

dfpays = pd.read_csv("pays.csv",delimiter=';', thousands=',', encoding='utf-8')
dfpays=dfpays.replace('\u202f','',regex=True)
dfpays=dfpays.replace('\t','',regex=True)

dfpays['Superficie'] = dfpays['Superficie'].replace(" ",'',regex=True)
dfpays['Superficie'] = dfpays['Superficie'].replace(",",'.',regex=True)
dfpays['Superficie'] = dfpays['Superficie'].astype(np.float64)

dfres = dfpays[['Superficie','Nom','PIB']]

app.layout = html.Div([
    html.H1('Pays'),
    dcc.Dropdown(['Superficie', 'Population', 'Nom'], 'Nom', id='pays-dropdown'),    
    dash_table.DataTable(
    data=dfres.to_dict('records'),
    css=[{'selector': 'table', 'rule': 'table-layout: fixed'}],
    style_data={
        'whiteSpace': 'normal'    },
        fill_width=False,id='tab')
])
@app.callback(
Output('tab', 'data'),
[Input('pays-dropdown', 'value')])
def update_graph(val):
    global dfpays 
    dfres = dfpays[[val]]
    return dfres.to_dict('records')

if __name__ == '__main__':
    app.run(jupyter_mode="tab",debug=True)

Dans cet exemple, la mise à jour de la table est faite à chaque fois qu'un choix est fait dans la liste déroulante.

6. Completer ce programme pour mettre à jour la table en fonction de la valeur du slider. La fonction de callback doit exécuter une requête SQL dans une base sqlite.
:::warning
L'appel à la fonction `sqlite3.connect` doit se faire de la manière suivante

con = sqlite3.connect("nomdunebase.db", check_same_thread=False)

:::
7. Ajouter tout autre élément à votre application (button, dropdown list).