Examen de Bases de données

Licence PRO TSI Chalon-sur-Saône

16 novembre 2023

durée : 3h

EXERCICE 1 (6 points)

On cherche à écrire le modèle relationnel de la base de données d'une banque.
Une banque possède des clients, qui peuvent ouvrir un ou plusieurs comptes bancaires. Certains comptes peuvent être des comptes joints, pour des époux par exemple.
Chaque compte va faire l'objet d'un ensemble d'opérations bancaires (viremenent, chèque, etc...)
On vous demande de définir le dictionnaire des données, puis de proposer un modèle de base de données que vous réaliserez avec le logiciel  Looping.

Vous devez rendre un document "traitement de texte" (du genre exercice1.doc) avec votre dictionnaire de données et votre fichier exercice1.loo de Looping

EXERCICE 2 (14 points)

On s'intéresse aux Monuments Historiques de Franche-Comté.

I. Examinez attentivement les fichiers de données suivants pour comprendre les relations qui les lient.
departements.csv, communesFC.csv, MonumentsHistoriquesFrancheComte.csv

Créez un modèle conceptuel de données avec Looping.
Pour vous faciliter l'importation des données (qui sera réalisée au § II), respectez bien les noms des entêtes de colonnes.
Enregistrez votre modèle au format .loo dans un fichier exercice2.loo.

II. Créez les tables issues de votre modèle conceptuel de données, puis importez les données à partir des fichiers .csv
Attention, la première ligne de ces fichiers décrit les colonnes ; pensez à paramétrer l'importation pour que cette première ligne ne soit pas traitée

Documentation SQL recommandée

IV. Ecrivez des requêtes permettant de trouver

1. tous les monuments historiques qui sont des statues
   
   
   SELECT * FROM mh WHERE categorie = 'statue'
    
2. le monument historique le plus au sud
   
   
   SELECT * FROM mh ORDER BY latitude DESC LIMIT 1
   
   
3. tous les monuments historiques dont la description mentionne un escalier
   
   
   SELECT * FROM mh WHERE description LIKE '%escalier%
4. tous les monuments historiques de la ville d'Arbois
   
   
   SELECT mh.* FROM mh, commune WHERE mh.CodeINSEECommune = commune.code_INSEE AND commune.nom = 'Arbois
   
    
5. combien d'édifices religieux sont classés monument historique à Besançon
   
   
   SELECT COUNT(*) FROM mh, commune WHERE mh.CodeINSEECommune = commune.code_INSEE AND commune.nom = 'Besançon' AND mh.categorie = 'édifice religieux'
   
    
6. tous les monuments historiques du département du Jura
   
   
   SELECT mh.* FROM mh, commune, departement WHERE mh.CodeINSEECommune = commune.code_INSEE AND commune.departement = departement.Code_departement AND departement.Nom_departement = 'Jura'

1. par département, le nombre de monuments historiques par catégorie
   
   
   SELECT departement.Nom_departement, mh.categorie, COUNT(*) AS nombre_monuments FROM mh, commune, departement WHERE mh.CodeINSEECommune = commune.code_INSEE AND commune.departement = departement.Code_departement GROUP BY departement.Nom_departement, mh.categorie ORDER BY departement.Nom_departement, nombre_monuments DESC
   
   
2. le département qui compte le plus de monuments historiques
   
   
   SELECT departement.Nom_departement, COUNT(*) AS nombre_monuments FROM mh, commune, departement WHERE mh.CodeINSEECommune = commune.code_INSEE AND commune.departement = departement.Code_departement GROUP BY departement.Nom_departement ORDER BY nombre_monuments DESC LIMIT 1
   
   
3. les communes qui n'ont pas de monuments historiques
   
   SELECT * FROM commune WHERE commune.code_INSEE NOT IN (SELECT DISTINCT commune.code_INSEE FROM `commune`, mh WHERE mh.CodeINSEECommune=commune.code_INSEE)


4. les communes qui ont à la fois un château et une église classés MH
   
   SELECT commune.nom FROM commune, mh WHERE commune.code_INSEE = mh.CodeINSEECommune AND mh.categorie = 'édifice religieux' AND commune.code_INSEE IN ( SELECT DISTINCT commune.code_INSEE FROM commune, mh WHERE commune.code_INSEE = mh.CodeINSEECommune AND mh.categorie = 'château')
   
    

Vous devez noter toutes vos requêtes dans un document texte que vous nommerez exercice2-SQL.txt

V. Généralisation

Que faudrait-il envisager pour généraliser cette base de données à tous les monuments historiques de France ?

Pourquoi cet exercice s'est-il volontairement limité à la liste des monuments historiques de Franche-Comté ?

Vous noterez vos réponses à la fin du document texte exercice2-SQL.txt

Vous zipperez vos fichiers (exercice1.doc exercice1.loo exercice2.loo exercice2-SQL.txt) dans une archive zip que vous enverrez par mail à l'adresse francois@aricia.fr