TP : MOCODO et SQL sous Jupyter¶
Durée : 6h
Dans ce sujet de TP vous allez déclarer un modèle conceptuel de données avec MOCODO, le traduire en relationnel puis l'implémenter sur votre base de données postgresql. Ensuite, vous utiliserez des requêtes SQL pour répondre à des questions sur les données.
Le contexte est celui du stockage d'appréciation de films par des évaluateurs. On gère les films identifiés par un numéro, qui ont un titre obligatoire, une date de réalisation et un réalisateur. Les évaluateurs ont un identifiant unique et obligatoirement un nom. Chaque évaluation est définie par le évaluateur, le film et la date d'évaluation ; elle donne lieu à une note (nombre d'étoiles). Il ne peut pas y avoir d'évaluation sans note.
Exercice 1 : création de la base de données¶
On propose de modéliser les données de cette application par le schéma conceptuel ci-dessous.
Question 1.2¶
Dans une deuxième cellule, demandez à Mocodo de générer le schéma relationnel (mld : modèle logique de données). Votre cellule commencera par la ligne suivante :
%mocodo -i mocodo_notebook/sandbox -t mld
Vous remarqueres que l'entité "date" est supprimée lors de la traduction, c'est le comportement par défaut de Mocodo lorsque des entités ont une seule propriété. Vous devez finalement obtenir exactement le schéma relationnel suivant :
mocodo_notebook/sandbox_mld.md
- movies (id_movie, title_movie, year_movie, director_movie)
- ratings (#id_movie, #id_reviewer, date_rating, stars_rating)
- reviewers (id_reviewer, name_reviewer)
Question 1.3¶
Vous pouvez maintenant générer le script SQL de création de la base de données. Pour cela, utilisez la commande suivante dans une nouvelle cellule :
%mocodo -i mocodo_notebook/sandbox.mcd --select sql -t postgresql create:types=text
L'option "create:type=text" permet que le type par défaut soit "text". Lorsque les attributs sont des entiers, vous devez les changer en "integer" dans le script MOCODO, en précisant le type entre crochets à côté de chaque attribut. Idem pour les dates.
Exécutez finalement ce script sur votre base de données Postgres. Il se trouve par défaut dans le fichier "mocodo_notebook\sandbox_ddl_postresql.sql". Vous ferez commencer votre script par une destruction puis création d'un schéma qu'on appellera "cinema" : un schema en SQL permet de "grouper" des objets d'une même base, un peu comme un répertoire.
La cellule qui vous permettra de faire tout cela contient le code suivant :
%sql drop schema if exists cinema cascade;
%sql create schema cinema;
%sql set search_path to cinema;
with open("mocodo_notebook/sandbox_ddl_postgresql.sql", "r") as f:
%sql {{f.read()}}
Question 1.4¶
Avec une commande de type
ALTER TABLE ... ADD CONSTRAINT ...
déclarez une contrainte pour la relation "movies" permettant de vérifier que la même année, deux films ne peuvent pas avoir le même titre.
Question 1.5¶
Exécutez le script TP1_moviedata.sql (voir sur la page WEB de l'UE) à l'aide de la commande suivante dans une cellule Python (après avoir téléchargé le fichier TP1_moviedata.sql dans votre espace jupyter) :
with open("./TP1_moviedata.sql","r") as fichier:
%sql {{fichier.read()}}
9 rows affected.
8 rows affected.
21 rows affected.
Exercice 2 - Requêtes simples
Ecrivez des requêtes SQL qui affichent les informations suivantes à partir de la base de données. Vous devez obtenir la même sortie que celle présentée à chaque question : mêmes tuples et mêmes noms de colonnes.
2.1 Donnez le nom du relecteur 205¶
1 rows affected.
| nom |
|---|
| Chris Jackson |
2.2 Listez les titres de films.¶
9 rows affected.
| titre |
|---|
| Gone with the Wind |
| Star Wars |
| Star Wars |
| The Sound of Music |
| E.T. |
| Titanic |
| Snow White |
| Avatar |
| Raiders of the Lost Ark |
2.3 Listez les titres de films par ordre croissant.¶
9 rows affected.
| titre |
|---|
| Avatar |
| E.T. |
| Gone with the Wind |
| Raiders of the Lost Ark |
| Snow White |
| Star Wars |
| Star Wars |
| The Sound of Music |
| Titanic |
2.4 Listez les titres des films réalisés par steven spielberg¶
2 rows affected.
| titre |
|---|
| E.T. |
| Raiders of the Lost Ark |
2.5 Listez les titres de films dont le réalisateur n'est pas renseigné¶
1 rows affected.
| titre |
|---|
| Snow White |
2.6 Donnez toutes les évaluations, avec les informations sur les films et les réalisateurs issus des tables correspondantes.¶
(nommez cette requête par un alias (vue) "v_detail_evaluations", que vous utiliserez comme une relation dans la suite à chaque fois que vous en aurez besoin.)
2.7 Listez les années, dans l'ordre croissant, qui ont un film qui a reçu une note de 4 ou 5.¶
5 rows affected.
| année |
|---|
| 1937 |
| 1939 |
| 1980 |
| 1981 |
| 2009 |
2.8 Listez les noms des personnes qui ont noté le film "Gone with the wind".¶
3 rows affected.
| nom du reviewer |
|---|
| Brittany Harris |
| Mike Anderson |
| Sarah Martinez |
2.9 Pour chaque évaluation où le nom de l'examinateur est identique au nom du réalisateur du film, ldonnnez e nom de l'examinateur, le titre du film, et le nombre d'étoiles.¶
1 rows affected.
| nom de l'examinateur | titre du film | nombre d'étoiles |
|---|---|---|
| James Cameron | Avatar | 5 |
2.10 Listez l'intégralité des évaluations, avec un résultat sous la forme (nom de l'examinateur, titre du film, nombre d'étoiles). D'abord par le nom de relecteur, puis par le titre de film, et enfin par le nombre d'étoiles.¶
21 rows affected.
| nom de l'examinateur | titre du film | nombre d'étoiles |
|---|---|---|
| Ashley White | E.T. | 3 |
| Brittany Harris | Avatar | 5 |
| Brittany Harris | E.T. | 2 |
| Brittany Harris | Gone with the Wind | 2 |
| Brittany Harris | Raiders of the Lost Ark | 2 |
| Brittany Harris | Raiders of the Lost Ark | 4 |
| Brittany Harris | Snow White | 4 |
| Brittany Harris | Star Wars | 3 |
| Brittany Harris | Star Wars | 4 |
| Brittany Harris | The Sound of Music | 2 |
| Brittany Harris | Titanic | 2 |
| Chris Jackson | E.T. | 2 |
| Chris Jackson | Raiders of the Lost Ark | 4 |
| Chris Jackson | The Sound of Music | 3 |
| Daniel Lewis | Snow White | 4 |
| James Cameron | Avatar | 5 |
| Mike Anderson | Gone with the Wind | 3 |
| Sarah Martinez | Avatar | 3 |
| Sarah Martinez | Gone with the Wind | 2 |
| Sarah Martinez | Gone with the Wind | 4 |
| Sarah Martinez | Snow White | 5 |
2.11 les titres des films non encore examinés par Chris Jackson.¶
6 rows affected.
| titre |
|---|
| Gone with the Wind |
| Avatar |
| Star Wars |
| Snow White |
| Titanic |
| Star Wars |
2.12 Pour tous les cas où la même personne note deux fois le même film et donne une note plus élevée la seconde fois, donnez le nom de l'examinateur, le titre du film, la première note et la deuxième note.¶
1 rows affected.
| nom de l'évaluateur | titre du film | première note | deuxième note |
|---|---|---|---|
| Sarah Martinez | Gone with the Wind | 2 | 4 |
3.1 retourner le nom de l'examinateur, le titre du film, et le nombre d'étoiles pour tous les films qui ont actuellement la plus mauvaise note dans la base.¶
7 rows affected.
| nom de l'examinateur | titre du film | note |
|---|---|---|
| Sarah Martinez | Gone with the Wind | 2 |
| Brittany Harris | The Sound of Music | 2 |
| Brittany Harris | Raiders of the Lost Ark | 2 |
| Chris Jackson | E.T. | 2 |
| Brittany Harris | Gone with the Wind | 2 |
| Brittany Harris | E.T. | 2 |
| Brittany Harris | Titanic | 2 |
3.2 pour chaque film, trouver la meilleure note reçue. retourner le titre de film et le nombre d'étoiles. Trier par rapport au titre de film (ordre alphabétique).¶
9 rows affected.
| titre du film | meilleure note |
|---|---|
| Avatar | 5 |
| E.T. | 3 |
| Gone with the Wind | 4 |
| Raiders of the Lost Ark | 4 |
| Snow White | 5 |
| Star Wars | 3 |
| Star Wars | 4 |
| The Sound of Music | 3 |
| Titanic | 2 |
3.3 donnez le nom des évaluateurs qui ont évalué tous les films.¶
1 rows affected.
| nom de l'évaluateur |
|---|
| Brittany Harris |
3.4 les titres de films, leur note moyenne, leur meilleur et plus mauvaise note.¶
9 rows affected.
| titre | note moyenne | note max | note min |
|---|---|---|---|
| Raiders of the Lost Ark | 3.33 | 4 | 2 |
| Gone with the Wind | 2.75 | 4 | 2 |
| The Sound of Music | 2.50 | 3 | 2 |
| Avatar | 4.33 | 5 | 3 |
| E.T. | 2.33 | 3 | 2 |
| Titanic | 2.00 | 2 | 2 |
| Star Wars | 4.00 | 4 | 4 |
| Star Wars | 3.00 | 3 | 3 |
| Snow White | 4.33 | 5 | 4 |
3.5 Le nom de tous les examinateurs qui ont fait au moins 3 évaluations.¶
2 rows affected.
| nom de l'évaluateur |
|---|
| Chris Jackson |
| Brittany Harris |
3.6 Les films ayant la meilleure moyenne de note. retourner le titre de film, et sa note moyenne.¶
2 rows affected.
| titre du film | note moyenne |
|---|---|
| Avatar | 4.33 |
| Snow White | 4.33 |
3.7 pour chaque film, donner ses meilleures notes et le nom du rapporteur qui les a donnée.¶
(une ligne par meilleure note s'il y en a plusieurs)
11 rows affected.
| titre du film | meilleure note | donnée par |
|---|---|---|
| Avatar | 5 | Brittany Harris |
| Avatar | 5 | James Cameron |
| E.T. | 3 | Ashley White |
| Gone with the Wind | 4 | Sarah Martinez |
| Raiders of the Lost Ark | 4 | Brittany Harris |
| Raiders of the Lost Ark | 4 | Chris Jackson |
| Snow White | 5 | Sarah Martinez |
| Star Wars | 3 | Brittany Harris |
| Star Wars | 4 | Brittany Harris |
| The Sound of Music | 3 | Chris Jackson |
| Titanic | 2 | Brittany Harris |
3.8 Différence entre la note moyenne des films réalisés avant 1980 et ceux réalisés à partir de 1980.¶
(on veut la moyenne des notes moyennes de chaque film)
1 rows affected.
| différence |
|---|
| -0.0541666666666667 |
3.9 pour chaque film, retourner le titre et la différence entre la meilleure et la plus mauvaise note. Trier par rapport à cette amplitude puis en fonction du titre.¶
9 rows affected.
| titre | difference |
|---|---|
| Star Wars | 0 |
| Star Wars | 0 |
| Titanic | 0 |
| E.T. | 1 |
| Snow White | 1 |
| The Sound of Music | 1 |
| Avatar | 2 |
| Gone with the Wind | 2 |
| Raiders of the Lost Ark | 2 |
4.1 pour chaque évaluation, retourner le nom du reviewer, le titre du film évalué, la note attribué au film par le reviewer et la moyenne de toutes les notes attribuées par ce reviewer.¶
21 rows affected.
| nom de l'évaluateur | titre du film | evaluations | moyenne des notes de cet évaluateur |
|---|---|---|---|
| Sarah Martinez | Gone with the Wind | 4 | 3.00 |
| Sarah Martinez | Gone with the Wind | 2 | 3.00 |
| Daniel Lewis | Snow White | 4 | 4.00 |
| Brittany Harris | Raiders of the Lost Ark | 2 | 3.00 |
| Brittany Harris | Avatar | 5 | 3.00 |
| Brittany Harris | The Sound of Music | 2 | 3.00 |
| Brittany Harris | Raiders of the Lost Ark | 4 | 3.00 |
| Brittany Harris | Star Wars | 3 | 3.00 |
| Brittany Harris | Star Wars | 4 | 3.00 |
| Brittany Harris | Gone with the Wind | 2 | 3.00 |
| Brittany Harris | E.T. | 2 | 3.00 |
| Brittany Harris | Titanic | 2 | 3.00 |
| Brittany Harris | Snow White | 4 | 3.00 |
| Mike Anderson | Gone with the Wind | 3 | 3.00 |
| Chris Jackson | The Sound of Music | 3 | 3.00 |
| Chris Jackson | E.T. | 2 | 3.00 |
| Chris Jackson | Raiders of the Lost Ark | 4 | 3.00 |
| Sarah Martinez | Snow White | 5 | 4.00 |
| Sarah Martinez | Avatar | 3 | 4.00 |
| James Cameron | Avatar | 5 | 5.00 |
| Ashley White | E.T. | 3 | 3.00 |
4.2Pour chaque rapporteur, donnez le nombre de films rapportés, la moyenne des notes qu'il a donné, la plus petite et la meilleure note. ordonnez le résultat par le nombre de films rapportés.¶
8 rows affected.
| nom de l'évaluateur | nombre de films rapportés | moyenne des notes | note maximale | note minimale |
|---|---|---|---|---|
| Ashley White | 1 | 3.00 | 3 | 3 |
| Daniel Lewis | 1 | 4.00 | 4 | 4 |
| James Cameron | 1 | 5.00 | 5 | 5 |
| Mike Anderson | 1 | 3.00 | 3 | 3 |
| Sarah Martinez | 2 | 3.00 | 4 | 2 |
| Sarah Martinez | 2 | 4.00 | 5 | 3 |
| Chris Jackson | 3 | 3.00 | 4 | 2 |
| Brittany Harris | 10 | 3.00 | 5 | 2 |
4.3 Pour chaque film, afficher sa note moyenne et son rang de classement dans l'ordre des notes moyennes parmi l'ensemble des films.¶
9 rows affected.
| titre | moyenne | rang |
|---|---|---|
| Avatar | 4.33 | 1 |
| Snow White | 4.33 | 1 |
| Star Wars | 4.00 | 3 |
| Raiders of the Lost Ark | 3.33 | 4 |
| Star Wars | 3.00 | 5 |
| Gone with the Wind | 2.75 | 6 |
| The Sound of Music | 2.50 | 7 |
| E.T. | 2.33 | 8 |
| Titanic | 2.00 | 9 |
4.4 Pour chaque rapporteur, et chaque film qu'il a évalué, afficher la meilleure note qu'il a donné à ce film, ainsi que la moyenne de toutes les notes qui ont été données à ce film. la réponse sera ordonnée par nom de rapporteur et titre de film.¶
19 rows affected.
| nom de l'évaluateur | titre du film | meilleure note de cet évaluateur pour ce film | moyenne de toutes les notes pour ce film |
|---|---|---|---|
| Ashley White | E.T. | 3 | 2.33 |
| Brittany Harris | Avatar | 5 | 4.33 |
| Brittany Harris | E.T. | 2 | 2.33 |
| Brittany Harris | Gone with the Wind | 2 | 2.75 |
| Brittany Harris | Raiders of the Lost Ark | 4 | 3.33 |
| Brittany Harris | Snow White | 4 | 4.33 |
| Brittany Harris | Star Wars | 3 | 3.00 |
| Brittany Harris | Star Wars | 4 | 4.00 |
| Brittany Harris | The Sound of Music | 2 | 2.50 |
| Brittany Harris | Titanic | 2 | 2.00 |
| Chris Jackson | E.T. | 2 | 2.33 |
| Chris Jackson | Raiders of the Lost Ark | 4 | 3.33 |
| Chris Jackson | The Sound of Music | 3 | 2.50 |
| Daniel Lewis | Snow White | 4 | 4.33 |
| James Cameron | Avatar | 5 | 4.33 |
| Mike Anderson | Gone with the Wind | 3 | 2.75 |
| Sarah Martinez | Avatar | 3 | 4.33 |
| Sarah Martinez | Gone with the Wind | 4 | 2.75 |
| Sarah Martinez | Snow White | 5 | 4.33 |
5.1 la moyenne des notes pour chaque rapporteur, ainsi que pour chaque film qu'il a rapporté.¶
27 rows affected.
| évaluateur | titre de film | moyenne donnée |
|---|---|---|
| Ashley White | E.T. | 3.00 |
| Ashley White | None | 3.00 |
| Brittany Harris | Avatar | 5.00 |
| Brittany Harris | E.T. | 2.00 |
| Brittany Harris | Gone with the Wind | 2.00 |
| Brittany Harris | Raiders of the Lost Ark | 3.00 |
| Brittany Harris | Snow White | 4.00 |
| Brittany Harris | Star Wars | 3.00 |
| Brittany Harris | Star Wars | 4.00 |
| Brittany Harris | The Sound of Music | 2.00 |
| Brittany Harris | Titanic | 2.00 |
| Brittany Harris | None | 3.00 |
| Chris Jackson | E.T. | 2.00 |
| Chris Jackson | Raiders of the Lost Ark | 4.00 |
| Chris Jackson | The Sound of Music | 3.00 |
| Chris Jackson | None | 3.00 |
| Daniel Lewis | Snow White | 4.00 |
| Daniel Lewis | None | 4.00 |
| James Cameron | Avatar | 5.00 |
| James Cameron | None | 5.00 |
| Mike Anderson | Gone with the Wind | 3.00 |
| Mike Anderson | None | 3.00 |
| Sarah Martinez | Avatar | 3.00 |
| Sarah Martinez | Gone with the Wind | 3.00 |
| Sarah Martinez | Snow White | 5.00 |
| Sarah Martinez | None | 3.00 |
| Sarah Martinez | None | 4.00 |
5.2 même question, mais pour les lignes correspondant à tous les films d'un rapporteur, remplacer la valeur null par "tous les films" (utilisation de la fonction "coalesce").¶
27 rows affected.
| evaluateur | titre | moyenne |
|---|---|---|
| Ashley White | E.T. | 3.00 |
| Ashley White | Tous les films | 3.00 |
| Brittany Harris | Avatar | 5.00 |
| Brittany Harris | E.T. | 2.00 |
| Brittany Harris | Gone with the Wind | 2.00 |
| Brittany Harris | Raiders of the Lost Ark | 3.00 |
| Brittany Harris | Snow White | 4.00 |
| Brittany Harris | Star Wars | 3.00 |
| Brittany Harris | Star Wars | 4.00 |
| Brittany Harris | The Sound of Music | 2.00 |
| Brittany Harris | Titanic | 2.00 |
| Brittany Harris | Tous les films | 3.00 |
| Chris Jackson | E.T. | 2.00 |
| Chris Jackson | Raiders of the Lost Ark | 4.00 |
| Chris Jackson | The Sound of Music | 3.00 |
| Chris Jackson | Tous les films | 3.00 |
| Daniel Lewis | Snow White | 4.00 |
| Daniel Lewis | Tous les films | 4.00 |
| James Cameron | Avatar | 5.00 |
| James Cameron | Tous les films | 5.00 |
| Mike Anderson | Gone with the Wind | 3.00 |
| Mike Anderson | Tous les films | 3.00 |
| Sarah Martinez | Avatar | 3.00 |
| Sarah Martinez | Gone with the Wind | 3.00 |
| Sarah Martinez | Snow White | 5.00 |
| Sarah Martinez | Tous les films | 3.00 |
| Sarah Martinez | Tous les films | 4.00 |
5.3 Analyser la moyenne des notes selon les trois dimensions film, rapporteur et année d'évaluation. Le résultat devra être défini sur les attributs titre du film, nom du rapporteur et année.¶
95 rows affected.
| evaluateur | titre du film | année d'évaluation | moyenne |
|---|---|---|---|
| Ashley White | E.T. | toutes les annees | 3 |
| Ashley White | E.T. | 2021 | 3 |
| Ashley White | Tous les films | toutes les annees | 3 |
| Ashley White | Tous les films | 2021 | 3 |
| Brittany Harris | Avatar | toutes les annees | 5 |
| Brittany Harris | Avatar | 2011 | 5 |
| Brittany Harris | E.T. | toutes les annees | 2 |
| Brittany Harris | E.T. | 2011 | 2 |
| Brittany Harris | Gone with the Wind | toutes les annees | 2 |
| Brittany Harris | Gone with the Wind | 2011 | 2 |
| Brittany Harris | Raiders of the Lost Ark | toutes les annees | 3 |
| Brittany Harris | Raiders of the Lost Ark | 2011 | 4 |
| Brittany Harris | Raiders of the Lost Ark | 2016 | 2 |
| Brittany Harris | Snow White | toutes les annees | 4 |
| Brittany Harris | Snow White | 2011 | 4 |
| Brittany Harris | Star Wars | toutes les annees | 3 |
| Brittany Harris | Star Wars | toutes les annees | 4 |
| Brittany Harris | Star Wars | 2011 | 3 |
| Brittany Harris | Star Wars | 2013 | 4 |
| Brittany Harris | The Sound of Music | toutes les annees | 2 |
| Brittany Harris | The Sound of Music | 2011 | 2 |
| Brittany Harris | Titanic | toutes les annees | 2 |
| Brittany Harris | Titanic | 2011 | 2 |
| Brittany Harris | Tous les films | toutes les annees | 3 |
| Brittany Harris | Tous les films | 2011 | 3 |
| Brittany Harris | Tous les films | 2013 | 4 |
| Brittany Harris | Tous les films | 2016 | 2 |
| Chris Jackson | E.T. | toutes les annees | 2 |
| Chris Jackson | E.T. | 2011 | 2 |
| Chris Jackson | Raiders of the Lost Ark | toutes les annees | 4 |