# TP : Bases de données¶

## Première partie : Utilisation de SQLiteBrowser¶

### Instructions générales¶

Dans les différentes séances de travaux pratiques, nous utiliserons le logiciel `DB Browser for SQLite` défini par ses concepteurs comme a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite .

Il vous suffira d'ouvrir le fichier `cinema.sqlite` disponible dans les documents de classe en consultation.

L'onglet `Parcourir les données` permet de voir la structure de la table étudiée, tandis qu'`Exécuter le SQL` permet de saisir des requêtes et de les exécuter. Il est vivement conseillé de n'exécuter que la ligne active (`Maj + F5`), ce qui permet de conserver une trace de toutes les requêtes effectuées. Il est utile de copier ces lignes dans le document ci-dessous pour éviter tout risque de perte en cas de plantage machine.

On utilisera les trois tables `film`, `acteur` et `casting`. Les schémas de ces trois tables sont

- `film` (id, titre, annee, note, nb_votes, id_realisateur)

- `acteur` (id, nom)

- `casting` (id_film, id_acteur, num)

Les clefs primaires sont mentionnées en gras (pour la table `casting`, la clef primaire est formée du couple `id_film`, `id_acteur`). Dans la table `film`, le champ `id_realisateur` est une clef étrangère qui renvoie au champ `id` de la table `acteur` (qui est en fait une table actrice, acteur, réalisatrice ou réalisateur). Dans la table `casting`, les champs `id_acteur` et `id_film` sont des clefs étrangères qui renvoient respectivement aux tables `acteur` et `film`.

> Question

Afficher le nombre de lignes de la table `casting`.

> Réponse

> Question

Afficher le numéro et le titre des films sortis en 2000. Les compter en écrivant une autre requête.

> Réponse

> Question

En quelle année le film « Forrest Gump » est-il sorti ?

> Réponse

> Question

Lister tous les films dont le titre contient « Star Wars ». On utilisera le test `LIKE` et les regex (LIKE "%ab%" reconnaît toutes les chaines contenant le motif "ab") . Les trier par année de sortie.

> Réponse

> Question

Quel est l'identifiant de l'acteur Brad Pitt ?

> Réponse

> Question

Afficher les titres des films dont l'identifiant est 7, 37 ou 133 (en une unique requête bien entendu).

> Réponse

> Question

Quel est le réalisateur des trois films précédents ? Même si la réponse est évidente, vous devez écrire une requête. On pourra dans un premier temps copier-coller l'identifiant du réalisateur.

> Réponse

> Question

Le but de cette question est de retrouver les films réalisés par Quentin Tarantino. On va utiliser deux méthodes :

> 1. La mauvaise méthode :
> - Écrire une première requête pour obtenir l'identifiant >de Quentin Tarantino ;
> - Écrire une deuxième requête pour obtenir les films qu'il a réalisé en recopiant le résultat de la requête précédente.
> 2. La bonne méthode :
> - Écrire une unique requête utilisant une jointure.

> Réponse

Mauvaise méthode

> Bonne méthode

> Question

Quel est l'identifiant du film Casablanca ?

> Réponse

> Question

Afficher le casting (c'est-à-dire la liste des acteurs) du film Casablanca en copiant-collant l'identifiant obtenu à la question précédente.

> Réponse

> Question

Obtenir le casting du film Alien en utilisant une unique requête (c'est-à-dire sans utiliser de copier-coller comme à la question précédente). On pourra réaliser plusieurs jointures.

> Réponse

> Question

Lister les films dans lesquels a joué Harrison Ford. On impose ici l'utilisation d'une double jointure.

> Réponse

> Question

Lister les films dans lesquels Harrison Ford a joué un second rôle. Les premiers rôles correspondent à num=1.

> Réponse

> Question

Lister tous les films de la base de données sortis en 2000 en précisant le nom de l'acteur jouant le rôle principal.

> Réponse

#### Aves des fonctions d'agrégation¶

> Question

Afficher la liste des acteurs ayant eu au moins 10 rôles principaux, accompagné du nombre de rôles. Le résultat sera trié par ordre décroissant du nombre d'apparition, puis ordre alphabétique à nombre d'apparition égal.

> Réponse

> Question

Afficher les 5 films sortis en 2000 ayant le plus grand nombre d'acteurs. Le résultat devra apparaître par ordre décroissant.

> Réponse

> Question bonus

Pour tous les films dans lesquels Tom Hanks a joué, afficher le titre et le nom de l'acteur jouant le rôle principal.

> Réponse

## Deuxième partie : interaction avec des bases de données à l'aide de Python¶

Les données que nous allons utiliser sont issues du site suivant .

Elles concernent les communes de France métropolitaine (sans les départements d'Outre-Mer).

La base que nous allons utiliser contient trois tables.

- La table `communes` dont les attributs sont:

  - `nom` de type chaîne de caractères,
  - `canton` de type entier,
  - `num_departement` de type chaîne de caractères,
  - `population_1999` et `population_2010` de type entier,
  - `surface` de type flottant,
  - `longitude` et `latitude` de type entier,
  - `zmin` et~`zmax` de type entier.

- La table `departements` dont les attributs sont:

  - `nom` de type chaîne de caractères,
  - `num_departement` de type chaîne de caractères,
  - `num_region` de type entier.

- La table `regions` dont les attributs sont:

  - `nom` de type chaîne de caractères,
  - `num_region` de type entier.

Précisions :

- les numéros sont tous de type entier, sauf pour les départements à cause de la Corse (2A et 2B);

- les attributs concernant la population sont des données provenant de deux recensements;

- la surface est en km$^2$;

- les attributs `zmin` et `zmax` sont les altitudes minimales et maximales par rapport au niveau de la mer;

- les longitudes et latitudes sont en grades !

On rappelle que chaque commune appartient à un département, et que chaque département appartient à une région.

La base se trouve dans le fichier `France.sqlite` que l'on pourra consulter à l'aide du logiciel précédent puis en utilisant le module `sqlite3` de Python.

Pour travailler sur la base avec `Python`, on utilise le module `sqlite3` à l'aide des commandes suivantes.

In [1]:

    import sqlite3
    import pylab as pl

    base = sqlite3.connect('France.sqlite')
    curseur = base.cursor()

    def afficheRequete(req,iProj=-1):
        '''
        entrée : chaîne de caractère de la requête
        sortie : affiche du résultat
        '''
        res=curseur.execute(req)
        if iProj==-1:
            for ligne in res:
                print(ligne)
        else:
            for ligne in res:
                print(ligne[iProj])

In [2]:

    # permet d'écrire des chaines de caractères sur plusieurs lignes
    req='''
    SELECT * FROM communes
    '''

    # affichage de la table complète
    #afficheRequete(req)

    # affichage du seul nom des villes
    #afficheRequete(req,1)

### Requêtes à une table¶

> Question

Quelles sont les villes dont une partie se trouve en dessous du niveau de la mer ?

In [3]:

    req="""

    """

    # afficheRequete(req,0)

In [4]:

    req="""
    SELECT nom, zmin , longitude , latitude FROM communes
    """

    #afficheRequete(req,0)

> Question

Quelles sont les dix plus petites communes en terme de superficie?

In [5]:

    req="""

    """

    # afficheRequete(req)

> Question

Quelle est la deduxième commune la plus plate de France ?

In [7]:

    req="""

    """

    # afficheRequete(req,0)

> Question

Donner le nom et le nombre d'habitants de chacune des communes des Yvelines en les triant par ordre décroissant de population en 2010.
Combien y en a-t-il ?

In [9]:

    # Villes, population
    req1="""
    """

    # Nombre communes
    req2="""
    """

    #afficheRequete(req2),afficheRequete(req1)

> Question

Combien y avait-il d'habitants en France en 1999 et 2010?

In [11]:

    req="""

    """

    # afficheRequete(req)

### Jointures¶

> Question

Quels sont les noms des départements dont une commune n'a plus d'habitant en 2010.

In [13]:

    req="""

    """

    # afficheRequete(req)

> Question

Trier les noms des départements par nombre d'habitants, puis par densité de population.

In [15]:

    req="""

    """

    # afficheRequete(req,0)

### Représentation sur une carte¶

Nous allons utiliser les coordonnées des villes pour tracer une carte de France. Le fait que les longitudes et latitudes soient exprimées en grades n'a pas de conséquence. En revanche, on pourra corriger la longitude à l'aide de la latitude.

Le tracé de la carte de France est effectué grace au module `contour` que vous pourrez analyser.

> Question

Représenter chaque commune par un point dont la couleur dépend de l'altitude la plus basse de son territoire. Des éléments sont donnés dans le code ci-dessous.

In [17]:

    req="""

    """

    # À DÉCOMMENTER APRÈS AVOIR ÉCRIT LA REQUÊTE
    # res = curseur.execute(req);
    # Lx,Ly,Lz=[],[],[]
    # for ligne in res:
    #     Lx.append(ligne[0]*pl.cos(ligne[1]*pl.pi/200000))
    #     Ly.append(ligne[1])
    #     Lz.append(pl.log(ligne[2]))

    # pl.scatter(Lx,Ly,c=Lz,s=5,linewidths=0)
    # pl.axis('equal')

> Question

Dessiner la carte des départements. On utilisera la fonctions `hash` pour définir les couleurs en fonction du numéro ou du nom du département.

In [19]:

    req="""

    """

    # À DÉCOMMENTER APRÈS AVOIR ÉCRIT LA REQUÊTE
    # res = curseur.execute(req);
    # Lx,Ly,Lz=[],[],[]

    # def couleur(d):
    #     return hash(d)

    # for ligne in res:
    #     Lx.append(ligne[0]*pl.cos(ligne[1]*pl.pi/200000))
    #     Ly.append(ligne[1])
    #     Lz.append(couleur(ligne[2]))

    # pl.scatter(Lx,Ly,c=Lz,s=5,linewidths=0)
    # pl.axis('equal')

> Question

Représenter, par des points de couleur, les 1500 communes les plus denses de métropole.

In [21]:

    req="""

    """

    # À DÉCOMMENTER APRÈS AVOIR ÉCRIT LA REQUÊTE
    # res = curseur.execute(req);

    # corr=pl.cos(40*pl.pi/180)

    # Lx,Ly,Lz=[],[],[]
    # for ligne in res:
    #     Lx.append(ligne[0])
    #     Ly.append(ligne[1])
    #     Lz.append(ligne[2])
    # pl.scatter(corr*pl.vectorize(float)(Lx),Ly,c=Lz)
    # pl.axis('equal')

In [ ]: