Informatique TD/TP S2.7 (4) Ecoles d'ingénieurs Les principales capacités développées sont : • utiliser une application offrant une interface graphique pour créer une base de données et l’alimenter, • utiliser une application offrant une interface graphique pour lancer des requêtes sur une base de données, • distinguer les rôles respectifs des machines client, serveur, et éventuellement serveur de données, • traduire dans le langage de l’algèbre relationnelle des requêtes écrites en langage courant, • concevoir une base constituée de plusieurs tables, et utiliser les jointures symétriques pour effectuer des requêtes croisées. L’objectif de ce TP est d'écrire des requêtes concernant les écoles et de réaliser des statistiques dans python (moyenne, écart-type). 1 Harmonisation des notes Statistiques annuelles de la classe : général maths Moyenne Ecart-type 9,42 7,53 3,52 phys S2I Anglais Français IPT 10,35 2,88 9,57 3,53 10,40 2,52 8,65 1,74 10,08 3,98 Français IPT 8,5 3,52 8,55 4,13 Français IPT 8,35 3,25 9,62 4,33 Statistiques du concours CCP (Concours Commun Polytechnique) de 2015 : général maths phys S2I Anglais Moyenne Ecart-type 9,86 10,59 3,07 10,68 2,69 10,08 3,56 8,36 3,19 Statistiques du concours CCS (Concours Centrale Supélec) de 2015 : général maths phys S2I Anglais Moyenne Ecart-type 9,23 9,36 3,4 9,425 4,1 9,21 3,57 9,29 3,94 Harmonisation des notes Afin d'harmoniser vos notes par rapport à celle du concours, il peut être intéressant de régler la moyenne et l'écart-type des notes du concours avec celles de la classe. Afin d'harmoniser une note avec un concours, il faut réaliser le calcul suivant : = − où + est la note initiale d'un candidat dans une matière, est la note harmonisée d'un candidat dans une matière, est la moyenne initiale de la classe dans une matière, est la moyenne harmonisée d'un concours pour une matière, est l'écart-type initiale de la classe (racine carré de la variance) est l'écart-type harmonisé d'un concours (racine carré de la variance) Rappels - la moyenne de classe s'obtient par l'expression : où - ∑ représente la note du ke étudiant de la classe. l'écart type de la classe s'obtient par : Lycée Jules Ferry Cannes = = ∑ Page 1 sur 6 − = ∑ − TSI1 Informatique TD/TP S2.7 (4) Ecoles d'ingénieurs Notations et coefficients - les notes seront écrites sous forme de listes dans l'ordre suivant : [maths, physiques, S2I, Anglais, Français, IPT] - les coefficients au concours sont variables mais respectent sensiblement la répartition suivantes (dans l'ordre identiques aux notes) : [ 2 , 2 , 2 ,1 , 1 , 0.5 ] - la moyenne d'un étudiant médian de la classe (14e place) a comme moyennes initiales : notes14 = [7, 10.5, 9.5, 11.5, 9, 10.5] 1) Ecrire les instructions qui permettent d'affecter les listes des moyennes et des écarts-types suivant (en respectant les notations indiquées) : o moyennes0 o sigmas0 o moyennesCCP o sigmasCCP 2) Ecrire une fonction harmoniser qui prend en arguments: o la note d'un étudiant note0, o la moyenne de classe pour la matière moyenne0 et son écart-type sigma0 o la moyenne du concours pour la matière CC et son écart-type sigmaCC qui renvoie en sortie la note noteCC de l'étudiant harmonisée sur celle du concours (pour éviter de conserver trop de décimales, on pourra multiplier la note pas 10, la convertir en entier puis la rediviser par 10).. 3) Ecrire la boucle qui permet de générer la liste des notes harmonisées notesCC pour un concours donné (on pourra d'ailleurs définir une liste notesCCP et une liste notesCCS). 4) Ecrire une fonction moyenne qui admet en argument la liste des notes harmonisées notesCC et la liste des coefficients. Cette fonction renvoie en sortie la moyenne correspondante Mcc coefficientée 5) Vérifier votre fonction moyenne en calculant les moyennes pondérées des concours CCP et CCS. 6) Calculer votre moyenne personnelle (si vous ne connaissez pas vos moyennes annuelles, utiliser des notes d'un étudiant médian). 2 Simulation d'admissibilité On rappelle dans la suite les relations définies dans la base de données ecoles_ingenieurs.sqlite. CONCOURS - id_concours (contenant l'intitulé du concours correpondant : CCP, CCS ou Epita) - nom du concours Lycée Jules Ferry Cannes Page 2 sur 6 TSI1 Informatique TD/TP S2.7 (4) Ecoles d'ingénieurs ECOLE - id_ecole - nom - acronyme - lieu - concours_id STAT - - id_stat places TSI promotion integreFerry seuil_oral (ATTENTION : certaines écoles de concours communs ne sont pas discréminées entre elles : les ENSI, les POLYTECH, les écoles de la banque PONT et le seuil de certaines peut être nettement plus élevé) annee ECOLE_id Requête SQL à partir de python 7) Ecrire l'instruction à placer en début de programme qui permet d'importer les fonctions de la bibliothèque sqlite3. from sqlite3 import * 8) Ecrire les instructions permettant de créer une connexion conn et un curseur c (voir l'annexe) à partir de la base de donnée ecoles_ingenieurs.sqlite. conn = connect('ecoles_ingenieurs.sqlite') c = conn.cursor() Lycée Jules Ferry Cannes Page 3 sur 6 TSI1 Informatique TD/TP S2.7 (4) Ecoles d'ingénieurs 9) Ecrire une instruction pour afficher les acronymes et le nom des écoles dont l'admission passe par le concours CCP (on stocker le résultat dans une variable r). Comment s'appelle ce type de requête ? r=c.execute("SELECT acronyme, nom FROM ECOLE WHERE concours_id='CCP'") requête : sélection (WHERE) + projection (SELECT) 10) Ecrire une instruction pour afficher les acronymes des écoles, le nom des écoles et les barres d'admissibilités (seuil_oral) en 2015 dont l'admission passe par le concours CCP (on pourra trier les écoles par seuil_oral du plus grand au plus petit : DESC). Comment s'appelle une telle requête SQL ? r=c.execute("SELECT acronyme, nom, seuil_oral FROM ECOLE JOIN STAT ON id_ecole==ECOLE_id WHERE concours_id='CCP'and annee=2015 ORDER BY seuil_oral DESC") requête : jointure symétrique + sélection (WHERE) + projection (SELECT) 11) Ecrire une instruction pour afficher les acronymes des écoles, le nom des écoles et les barres d'admissibilités (seuil_oral) dont l'admission passe par le concours CCP dont les notes sont supérieures à votre moyenne générale harmonisée en triant les écoles par le seuil_oral. r=c.execute("SELECT acronyme, nom, seuil_oral FROM ECOLE JOIN STAT ON id_ecole==ECOLE_id WHERE concours_id='CCP'and annee=2015 and seuil_oral>9.6 ORDER BY seuil_oral DESC") Traitement des résultats (tuples) renvoyés par la base de données On remarque que l'affichage dans python n'est pas toujours très lisible. L'intérêt de python réside plus dans les possibilités de traitement statistique des résultats. 12) Ecrire un programme barre_ecole qui admet en entrée un curseur de base de données et qui renvoie en sortie une liste dont les éléments contiennent le nombre d'écoles auxquels été admissible en 2015 selon les moyennes variant de 0 à 20. On utilisera des requêtes par agrégation (COUNT) du seuil_oral en faisant varier le seuil_oral de 0 à 20 par incrément de 1. nb_ecole = [ ] notes = [ ] for i in range (21): notes = notes + [i] r = c.execute("SELECT count(seuil_oral) FROM ECOLE JOIN STAT ON id_ecole==ECOLE_id WHERE concours_id='CCP'and annee=2015 and seuil_oral and seuil_oral>"+ str(i)) liste = r.fetchall() nb_ecole = nb_ecole + [int(liste[0][0])] 13) Tracer un graphique bâton, ayant en abscisse la note moyenne au concours et présentant en ordonnées le nombre d'écoles accessibles. bar(notes, nb_ecole) Lycée Jules Ferry Cannes Page 4 sur 6 TSI1 Informatique TD/TP S2.7 (4) Ecoles d'ingénieurs Annexe 1 : Extrait de fonctions de la bibliothèque sqlite3 La bibliothèque sqlite3 intégrée à Spyder permet de lancer des requêtes sur une base de données Sqlite. Se connecter (ouvrir) une base de données sqlite connect(...) connect(database[, timeout, isolation_level, detect_types, factory]) Opens a connection to the SQLite database file *database*. Use ":memory:" to open a database connection to a database that resides in RAM instead of on disk. Exemple : nom_python_de_la_connexion = connect ( 'nom_de_la_base.sqlite' ) Créer un curseur pour parcourir une base de données cursor : méthode à appliquer à une connection. Exemple : nom_python_du_curseur = nom_python_de_la_connexion . cursor() Lancer des requêtes SQL Liste des méthodes que l'on peut appliquer au curseur : | close(...) | Closes the cursor. | | execute(...) | Executes a SQL statement. | | executemany(...) | Repeatedly executes a SQL statement. | | executescript(...) | Executes a multiple SQL statements at once. Non-standard. Exemple : resultat = nom_du_curseur . execute ( 'SELECT acronyme FROM ECOLE' ) Traitement des résultats Pour récupérer les résultats d'une requête SQL lancer par les fonctions execute, on utilise la méthode fetchall qui renvoient des tuples. Exemple : r = resultat.fetchall() Les tuples se traitent comme des listes sauf qu'ils ne peuvent pas être modifiés. Exemple : a= ( 1 , 2 , "texte" ) # a est un tuple >>> type(a) <class 'tuple'> >>> a[0] 1 >>> a[1]=5 TypeError: 'tuple' object does not support item assignment Références : https://docs.python.org/3.1/library/sqlite3.html Lycée Jules Ferry Cannes Page 5 sur 6 TSI1 Informatique TD/TP S2.7 (4) Ecoles d'ingénieurs Annexe 2 : Algorithmes de traitements des listes def moyenne (L): # Entrée : liste L de nombres non vide # Sortie : moyenne des éléments de L n = len(L) som = 0 for i in range(n): som = som + L[i] return som/n def variance (L): # Entrée : liste L de nombres non vide # Sortie : variance des éléments de L n = len(L) som = 0 for i in range(n): som = som + L[i]**2 return som/n - moyenne(L)**2 def maximum (L): # Entrée : liste L non vide # Sortie : s : valeur maximum de L n = len(L) s = L[0] # s=0 incorrect si valeurs toutes négatives for i in range(n): if L[i]>s: s=L[i] return s def dichotomie (L,x): # Entrées : élément x et liste L # Sortie : s=True si x dans L et False sinon n=len(L) g=0 # premier indice de L : 0 d=n-1 # dernier indice de L : n-1 m=(g+d)//2 # dichotomie while g<d: if L[m]==x: # x est au milieu return True else: if L[m]<x: # x est à droite du milieu g=m+1 else: # x est à gauche du milieu d=m-1 m=(g+d)//2 return False Lycée Jules Ferry Cannes def chercher (L,x): # Entrées : élément x et liste L # Sortie : s=True si x dans L et False sinon n = len (L) s = False # s : Faux par défaut c=0 # initialisation de l'index while (c<n and s==False): if L[c]==x : s = True c = c+1 return s Page 6 sur 6 TSI1