Info – Exercices 7 Bases de données relationnelles

Info – Exercices 7
Bases de données relationnelles
Requêtes élémentaires sur des bases de données relationnelles, TP et
exercices posés aux concours.
7
Bases de données relationnelles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
Travaux pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
Exercices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3
Indications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
2
2
5
PCSI2 \ 2014-2015
Laurent Kaczmarek
1. Travaux pratiques
1 . [ À traiter sur machine ] ( ind )
Le fichier films.db contient une base de données sur le cinéma. Elle est constituée de six relations. Voici
son schéma relationnel :
. acteur[id,prenom,nom]
. jeu[id_film,id_acteur]
. entree[id_utilisateur,id_film,note]
. realisateur[id,prenom,nom]
. film[id,titre,annee,id_realisateur]
. utilisateur[id,prenom,nom]
a) Ouvrir pyzo, importer la bibliothèque sqlite3, créer une connexion conn et un curseur c.
b) Effectuer quelques requêtes afin de comprendre la structure et le type d’informations contenues
dans chaque relation.
c) Requêtes élémentaires.
i) Afficher les films sortis l’année de votre naissance.
ii) Afficher les films de Akira Kurosawa.
iii) Afficher les films de Jim Carrey.
iv) Afficher les acteurs du film Inception.
v) Afficher les acteurs ayant été dirigés par Francis Ford Coppola.
vi) Afficher les films d’Alfred Hitchcock avec Cary Grant.
vii) Afficher la liste des réalisateurs accompagnés du nombre de leurs films dans la base de données.
viii) Afficher les acteurs ayant joué dans au moins trois films de cette base de données.
ix) Afficher le total des films par année par valeurs décroissantes.
x) Afficher le total de films par réalisateur par valeurs décroissantes.
xi) Afficher chaque utilisateur avec la moyenne de ses notes.
xii) Afficher les moyennes des notes des films par ordre décroissant.
xiii) Proposer à votre voisin une requête futile de votre choix.
2. Exercices
2 . [ Extrait de l’épreuve écrite d’informatique commune des Mines 2015 ] ( ind )
Après son assemblage et avant les différents tests de validation, un numéro de série unique est attribué
à chaque imprimante. À la fin des tests de chaque imprimante, les résultats d’analyse ainsi que le fichier
contenant l’ensemble des mesures réalisées sur l’imprimante sont rangés dans la table testfin dont le
schéma relationnel est :
testfin[nSerie,dateTest,Imoy,Iec,fichierMes]
Lorsqu’une imprimante satisfait les critères de validation, elle est enregistrée dans la table production
avec son numéro de série, la date et l’heure de sortie de production ainsi que son type.
production[Num,nSerie,dateProd,type]
a) Rédiger une requête SQL permettant d’obtenir les numéros de série nSerie des imprimantes ayant
une valeur de Imoy comprise strictement entre deux bornes Imin et Imax.
LLG \ PCSI2
Info – Exercices 7 \ 2
PCSI2 \ 2014-2015
Laurent Kaczmarek
b) Rédiger une requête SQL permettant d’obtenir les numéros de série nSerie, la valeur de l’écarttype Iec et le fichier de mesures fichierMes des imprimantes ayant une valeur de Iec strictement
inférieure à la valeur moyenne de la colonne Iec.
c) Rédiger une requête SQL qui permettra d’extraire à partir des tables testfin et production le
numéro de série nSerie et le fichier de mesures fichierMes correspondant aux imprimantes qui
n’ont pas été validées en sortie de production.
3 . [ Extrait de l’épreuve écrite d’informatique commune de Centrale 2015 ] ( ind )
À partir de mesures régulièrement effectuées par différents observatoires, une base de données des caractéristiques et des états des corps célestes de notre système solaire est maintenue à jour. Les données
à extraire sont les masses des corps étudiés et leurs états (position et vitesse) à l’instant t m du début
d’une simulation numérique. Une version simplifiée, réduite à deux tables, de la base de données du
Système solaire est décrite ci-dessous :
. La table CORPS répertorie les corps étudiés, elle contient les colonnes :
† id_corps (clé primaire) entier identifiant chaque corps ;
† nom, chaine de caractères, désigne le nom usuel du corps ;
† masse, de type flottant, contient la masse du corps.
. La table ETAT rassemble l’historique des états successifs (positions, vitesses) des corps étudiés. Elle est constituée de huit colonnes :
† id_corps de type entier, identifie le corps concerné ;
† datem est la date de la mesure, sous forme d’un entier donnant le nombre de secondes
écoulées depuis un instant d’origine ;
† trois colonnes de type flottant pour les composantes de la position x, y, z ;
† trois colonnes de type flottant pour les composantes de la vitesse vx, vy, vz.
Les masses sont exprimées en kilogrammes, les distances en unités astronomiques (1 ua = 1, 5 × 1011
m) et les vitesses en kilomètres par seconde. Le référentiel utilisé pour exprimer les composantes des
positions et des vitesses est galiléen, orthonormé et son centre est situé à proximité du soleil.
a) Écrire une requête SQL qui renvoie la liste des masses de tous les corps étudiés.
b) Les états des différents corps ne sont pas forcément tous déterminés exactement au même instant.
Nous allons assimiler l’état initial (à la date t m ) de chaque corps à son dernier état connu antérieur
à t m . Dans toute la suite, on supposera que la valeur de t m , sous le format utilisé dans la table ETAT,
est accessible à toute requête SQL via l’expression tmin().
i) On souhaite d’abord vérifier que tous les corps étudiés disposent d’un état connu antérieur à
tmin(). Le nombre de corps présents dans la base est obtenu grâce à la requête
SELECT count(*) FROM corps
Écrire une requête SQL qui renvoie le nombre de corps qui ont au moins un état connu antérieur à tmin().
ii) Écrire une requête SQL qui renvoie, pour chaque corps, son identifiant et la date de son dernier état antérieur à tmin().
iii) Le résultat de la requête précédente est stocké dans une nouvelle table date_mesure à deux
colonnes :
. id_corps de type entier, contient l’identifiant du corps considéré ;
. date_der de type entier, correspond à la date du dernier état connu du corps considéré,
antérieur à tmin().
Pour simplifier la simulation, on décide de négliger l’influence des corps ayant une masse
strictement inférieure à une valeur fixée masse_min() et de ne s’intéresser qu’aux corps situés
LLG \ PCSI2
Info – Exercices 7 \ 3
PCSI2 \ 2014-2015
Laurent Kaczmarek
dans un cube, centré sur l’origine du référentiel de référence et d’arête arete() donnée. Les
faces de ce cube sont parallèles aux plans formés par les axes du référentiel de référence. Écrire
une requête SQL qui renvoie la masse et l’état initial (sous la forme masse, x, y, z, vx, vy, vz)
de chaque corps retenu pour participer à la simulation. On classera les corps dans l’ordre
croissant par rapport à leur distance à l’origine du référentiel.
LLG \ PCSI2
Info – Exercices 7 \ 4
PCSI2 \ 2014-2015
Laurent Kaczmarek
3. Indications
1 . [ À traiter sur machine ]
a) select titre from film where annee=1974 ;
b) select titre from film join realisateur on id_realisateur=realisateur.id where
prenom="Akira" and nom="Kurosawa" ;
c) select titre from film join jeu join acteur on film.id=id_film and id_acteur=acteur.id
where prenom="Jim" and nom="Carrey" ;
d) select nom,prenom from film join acteur join jeu on id_acteur=acteur.id and
film.id=id_film where titre="Inception" ;
e) select distinct acteur.nom,acteur.prenom from film join jeu join acteur join realisateur
on realisateur.id=id_realisateur and id_film=film.id and id_acteur=acteur.id
where realisateur.prenom="Francis Ford" and realisateur.nom="Coppola" ;
f) select
titre from film join jeu join acteur join realisateur on
id_acteur=acteur.id and id_film=film.id and id_realisateur=realisateur.id
where realisateur.nom="Hitchcock" and realisateur.prenom="Alfred" and acteur.nom="Grant" and acteur.prenom="Cary" ;
g) select
nom,prenom,count(*) as
total
from
film
id_realisateur=realisateur.id group by nom,prenom ;
join
realisateur
on
h) select nom,prenom from (select nom,prenom,count(*) as n from film join jeu join acteur on
id_acteur=acteur.id and id_film=film.id group by nom,prenom having n>2) ;
i) select annee,count(*) as total from film group by annee order by total desc ;
j) select
nom,prenom,count(*) as
total
from
film
join
realisateur
id_realisateur=realisateur.id group by nom,prenom order by total desc ;
k) select
nom,prenom,avg(note)
from
utilisateur
id_utilisateur=utilisateur.id group by nom,prenom ;
join
entree
on
on
l) select titre,avg(note) as moy from entree join film on id_film=film.id group by titre
order by moy desc ;
2 . [ Extrait de l’épreuve écrite d’informatique commune des Mines 2015 ]
a) select nSerie from testfin where Imin<=Imoy and Imoy<=Imax ;
b) select nSerie,Iec,fichierMes from testfin where Iec<(select avg(Iec) from testfin) ;
c) select nSerie,fichierMes from testfin join ((select nSerie as nbis from testfin) except
(select nSerie from production)) on nbis=nSerie ;
3 . [ Extrait de l’épreuve écrite d’informatique commune de Centrale 2015 ]
a) select masse from corps ;
b)
i) select count(*) from (select distint id_corps from etat where datem<tmin()) ;
ou :
select count(distint id_corps) from etat where datem<tmin() ;
ii) select id_corps,max(datem) from etat where datem<tmin() group by id_corps ;
iii) select
masse,x,y,z,vx,vy,vz from corps join etat join date_mesure on
date_mesure.id_corps=etat.id_corps and corps.id_corps=etat.id_corps and
LLG \ PCSI2
Info – Exercices 7 \ 5
PCSI2 \ 2014-2015
Laurent Kaczmarek
date_der=datem where masse>=masse_min() and x<arete()/2 and x>-arete()/2
and y<arete()/2 and y>-arete()/2 and z<arete()/2 and z>-arete()/2 order by
x*x+y*y+z*z asc ;
LLG \ PCSI2
Info – Exercices 7 \ 6