C17.EB1

Cet exercice porte sur le langage SQL, sur la programmation en Python et la recherche textuelle.

Le sujet d’une étude porte sur les papillons, la corrélation entre leur présence et celle de certaines plantes ainsi que sur la classification de nouvelles espèces.

Partie A. Corrélation avec la présence des plantes

Dans cet exercice, on pourra utiliser les clauses du langage SQL pour :

• • construire des requêtes d’interrogation à l’aide de SELECT, FROM, WHERE (avec les opérateurs logiques AND, OR), JOIN ... ON ;
• • construire des requêtes d’insertion et de mise à jour à l’aide de UPDATE, INSERT.

Dans le cadre de cette étude, une base de données faune_flore.db a été créée pour étudier la corrélation entre la présence d’espèces de papillons et celle de certaines plantes. Cette base de données regroupe les tables papillon, plante et zone_geographique.

La table papillon comporte les informations suivantes :

• • l’identification du papillon (num) ;
• • le nom commun du papillon (nomCo) ;
• • le nom scientifique du papillon (nomSc) ;
• • la taille moyenne du papillon en millimètres (taille) ;
• • le principal habitat du papillon (habitat) ;
• • la zone géographique où le papillon est le plus présent (zone). Cet attribut fait référence à l’attribut num de la table zone_geographique.

Un extrait de cette table est donné ci-après.

La table plante comporte les informations suivantes :

• • l’identification de la plante (num) ;
• • le nom commun de la plante (nomCo) ;
• • le nom scientifique de la plante (nomSc) ;
• • le principal habitat de la plante (habitat) ;
• • la zone géographique où elle est la plus présente (zone). Cet attribut fait référence à l’attribut num de la table zone_geographique.

Un extrait de la table plante est donné ci-dessous.

La table zone_geographique contient les informations suivantes :

• • l’identification de la zone géographique (num) ;
• • le nom de la zone (zone).

Un extrait de la table zone_geographique est donné ci-après.

SELECT taille
FROM papillon
WHERE nomCo='Machaon'

Après avoir mesuré l’envergure de plusieurs papillons Petite Tortue, un des scientifiques de l’étude a calculé la nouvelle moyenne des tailles pour ce papillon, qui est maintenant de 50 mm.

SELECT nomSc
FROM plante
JOIN zone_geographique
ON plante.zone = zone_geographique.num WHERE zone_geographique.zone = 'Asie'

SELECT papillon.nomCo, plante.nomCo
FROM papillon
JOIN zone_geographique
ON papillon.zone = zone_geographique.num
JOIN plante
ON plante.zone = zone_geographique.num
WHERE zone_geographique.zone = 'Europe'

Partie B. Classification d’une nouvelle espèce

Les espèces de papillons sont regroupées dans une liste de dictionnaires. Pour simplifier, seuls les attributs num (l’identifiant du papillon), nomCo (son nom commun), nomSc (son nom scientifique) et taille (sa taille) seront considérés dans cette partie. Une partie de la liste papillon est donnée ci-dessous :

papillon = [
        {'num': 458, 'nomCo': 'Monarque',
         'nomSc': 'Danaus plexippu', 'taille': 100},
        {'num': 459, 'nomCo': 'Citron de Provence',
         'nomSc': 'Gonepteryx cleopatra', 'taille': 30},
        {'num': 460, 'nomCo': 'Paon-du-jour',
         'nomSc': 'Aglais io', 'taille': 6},
        {'num': 461, 'nomCo': 'Machaon',
         'nomSc': 'Papilio machaon', 'taille': 85},
        {'num': 462, 'nomCo': 'Petite Tortue',
         'nomSc': 'Aglais urticae', 'taille': 50},
        {'num': 463, 'nomCo': 'Robert-le-Diable',
         'nomSc': 'Polygonia c-album', 'taille': 25} ]

Le but de cette partie est de trier la liste des papillons par ordre croissant de taille et de classifier une nouvelle espèce photographiée.

La fonction tri_collec renvoie la liste de dictionnaires des papillons triée par ordre croissant de taille.

def tri_collec(collec): 
    """Renvoie la collection des papillons triées
    par ordre croissant de leur taille. 
    Paramètre: 
        collec : liste de dictionnaires des papillons 
    Renvoie: 
        liste triée par ordre croissant des tailles des papillons. 
    """ 
    for i in range(1, len(collec)):
        pap = collec[i] 
        j = ...  
        while j > 0 and collec[j - 1]['taille'] > ...:
            collec[j] = collec[j - 1] 
            j = ...  
        collec[j] = pap
    return ...  

L’algorithme des k plus proches voisins est utilisé pour classifier la nouvelle espèce photographiée.

Cette nouvelle espèce montre beaucoup de ressemblance avec l’espèce ‘Aglais io’ mais diffère par la taille et la couleur des motifs des ailes.

Pour vérifier l’hypothèse que la nouvelle espèce est l’espèce ‘Aglais io’ comportant une mutation génétique, une recherche naïve d’une séquence caractéristique des papillons ‘Aglais io’ est réalisée sur la chaîne d’ADN extraite de la nouvelle espèce. Une chaîne d’ADN est représentée en Python par une chaine de caractères. Cette recherche utilise la fonction recherche_seq(seq, chaine) qui renvoie l’indice du premier caractère de seq si la séquence seq est présente dans la chaîne d’ADN chaine et -1 sinon.

def recherche_seq(seq, chaine): 
    """Renvoie l'indice du premier caractère de chaine
    où commence `seq` si la séquence `seq` se trouve dans
    la chaine de caractères chaine, -1 sinon
    Paramètres: 
        seq : séquence à rechercher 
        chaine : chaine d'ADN 
    Renvoie: 
    	indice du premier caractère de seq dans la chaine, -1 sinon. 
	""" 
	for i in range(len(chaine)-len(seq) + 1):  
	    j = 0 
	    while j < len(seq) and ...:  
	        j += 1 
	    if ...:  
	        return i 
	    return -1 

La fonction recherche_BMH(seq, chaine), donnée ci-dessous, implémente l’algorithme de Boyer-Moore Horspool.

def dico_lettres(seq): 
    d = {}
    for i in range(len(seq)-1):
        d[seq[i]] = i
    return d
  
def recherche_BMH(seq, chaine): 
    decalage = dico_lettres(seq) 
    i = 0 
    n = len(seq)
    while i <= len(chaine) - n: 
        j = n-1 
        while j >= 0 and chaine[i + j] == seq[j]: 
            j -= 1
        if j == -1: 
            return i 
        else: 
            if chaine[i + n - 1] in decalage: 
                i += n - decalage[chaine[i + n-1]] - 1 
            else: 
                i += n 
    return -1