375 lines
15 KiB
Markdown
375 lines
15 KiB
Markdown
# TP : Dossier Dudley — une enquête épidémiologique
|
|
|
|
## Contexte
|
|
|
|
Vous êtes analyste de données au **Centre de veille sanitaire**. On vous confie un dossier
|
|
concernant **Dudley**, une petite commune rurale de l'Arkansas organisée autour d'une seule
|
|
entreprise : l'usine de transformation de volaille **Chaco Chicken**.
|
|
|
|
Le signalement est bref :
|
|
|
|
> *Plusieurs habitants de Dudley sont décédés récemment d'une maladie neurologique rare,
|
|
> la maladie de Creutzfeldt-Jakob. Le nombre de cas paraît anormalement élevé pour une
|
|
> commune de cette taille. Un inspecteur fédéral de l'agriculture envoyé sur place au
|
|
> mois de mars a disparu.*
|
|
|
|
Vous disposez de six tables : l'état civil, les emplois à l'usine, les diagnostics
|
|
enregistrés par le cabinet médical, les repas communautaires organisés dans la commune,
|
|
la liste des participants à ces repas, et les disparitions signalées.
|
|
|
|
**Votre mission : trouver la source de la contamination, uniquement avec des requêtes SQL.**
|
|
|
|
> *Ce TP s'inspire librement de l'épisode « Our Town » (X-Files, saison 2). Le mécanisme
|
|
> biologique, lui, est réel : les maladies à prions se transmettent par l'alimentation.
|
|
> C'est ce mécanisme qui a provoqué la crise de la « vache folle » en Europe dans les
|
|
> années 1990.*
|
|
|
|
---
|
|
|
|
## Objectifs
|
|
|
|
- Interroger une base de données relationnelle à six tables
|
|
- Maîtriser les jointures, y compris à travers une table d'association
|
|
- Utiliser les fonctions d'agrégation (`COUNT`, `AVG`, `MIN`, `MAX`) et la clause `HAVING`
|
|
- Comprendre qu'une requête n'est pas seulement un exercice de syntaxe : c'est un
|
|
**instrument d'enquête**
|
|
|
|
---
|
|
|
|
## Partie 1 : Mise en place de la base
|
|
|
|
### 1.1. Création des tables
|
|
|
|
Ouvrez **DB Browser for SQLite**, créez une base `dudley.db`, puis exécutez :
|
|
|
|
```sql
|
|
-- État civil de la commune et des environs
|
|
CREATE TABLE HABITANTS (
|
|
id INTEGER PRIMARY KEY,
|
|
nom TEXT NOT NULL,
|
|
prenom TEXT NOT NULL,
|
|
annee_naissance INTEGER,
|
|
annee_deces INTEGER, -- NULL si la personne est vivante
|
|
commune TEXT
|
|
);
|
|
|
|
-- Emplois occupés à l'usine Chaco Chicken
|
|
CREATE TABLE EMPLOIS (
|
|
id INTEGER PRIMARY KEY,
|
|
id_habitant INTEGER,
|
|
poste TEXT,
|
|
annee_embauche INTEGER,
|
|
annee_fin INTEGER, -- NULL si toujours en poste
|
|
FOREIGN KEY (id_habitant) REFERENCES HABITANTS(id)
|
|
);
|
|
|
|
-- Diagnostics enregistrés par le cabinet médical
|
|
CREATE TABLE DIAGNOSTICS (
|
|
id INTEGER PRIMARY KEY,
|
|
id_habitant INTEGER,
|
|
annee INTEGER,
|
|
symptome TEXT,
|
|
diagnostic TEXT,
|
|
FOREIGN KEY (id_habitant) REFERENCES HABITANTS(id)
|
|
);
|
|
|
|
-- Repas communautaires organisés dans la commune
|
|
CREATE TABLE REPAS (
|
|
id INTEGER PRIMARY KEY,
|
|
date DATE,
|
|
lieu TEXT,
|
|
occasion TEXT
|
|
);
|
|
|
|
-- Table d'association : qui a participé à quel repas
|
|
CREATE TABLE PARTICIPATIONS (
|
|
id INTEGER PRIMARY KEY,
|
|
id_habitant INTEGER,
|
|
id_repas INTEGER,
|
|
FOREIGN KEY (id_habitant) REFERENCES HABITANTS(id),
|
|
FOREIGN KEY (id_repas) REFERENCES REPAS(id)
|
|
);
|
|
|
|
-- Disparitions signalées à la gendarmerie
|
|
CREATE TABLE DISPARITIONS (
|
|
id INTEGER PRIMARY KEY,
|
|
id_habitant INTEGER,
|
|
date_disparition DATE,
|
|
statut_enquete TEXT,
|
|
FOREIGN KEY (id_habitant) REFERENCES HABITANTS(id)
|
|
);
|
|
```
|
|
|
|
### 1.2. Insertion des données
|
|
|
|
```sql
|
|
-- ------------------------------------------------------------------
|
|
-- HABITANTS
|
|
-- ------------------------------------------------------------------
|
|
INSERT INTO HABITANTS (id, nom, prenom, annee_naissance, annee_deces, commune) VALUES
|
|
-- Dudley : les anciens
|
|
(1, 'Chaco', 'Walter', 1902, NULL, 'Dudley'),
|
|
(2, 'Riddick', 'Doris', 1891, 1988, 'Dudley'),
|
|
(3, 'Bowman', 'Elias', 1888, 1985, 'Dudley'),
|
|
(4, 'Kingston', 'Martha', 1893, 1989, 'Dudley'),
|
|
(5, 'Prine', 'Howard', 1890, 1987, 'Dudley'),
|
|
(6, 'Sheldon', 'Rita', 1895, 1989, 'Dudley'),
|
|
(7, 'Farrow', 'Nelson', 1899, 1989, 'Dudley'),
|
|
-- Dudley : décès récents
|
|
(8, 'Gwynn', 'Paula', 1957, 1995, 'Dudley'),
|
|
(9, 'Kittel', 'Ray', 1949, 1995, 'Dudley'),
|
|
(10, 'Vance', 'Bonnie', 1962, 1994, 'Dudley'),
|
|
(11, 'Hooper', 'Jess', 1954, 1994, 'Dudley'),
|
|
(12, 'Traylor', 'Sam', 1960, 1995, 'Dudley'),
|
|
(13, 'Doyle', 'Nancy', 1951, 1995, 'Dudley'),
|
|
-- Dudley : vivants
|
|
(14, 'Ashby', 'Curtis', 1965, NULL, 'Dudley'),
|
|
(15, 'Lowell', 'Beatrice', 1970, NULL, 'Dudley'),
|
|
(16, 'Pruitt', 'Wendell', 1958, NULL, 'Dudley'),
|
|
(17, 'Kearns', 'George', 1948, NULL, 'Dudley'),
|
|
-- Millhaven : commune voisine, prise comme référence
|
|
(18, 'Sorrel', 'Agnes', 1912, 1988, 'Millhaven'),
|
|
(19, 'Buckley', 'Harold', 1918, 1994, 'Millhaven'),
|
|
(20, 'Neame', 'Constance', 1921, 1995, 'Millhaven'),
|
|
(21, 'Ferrell', 'Douglas', 1908, 1986, 'Millhaven'),
|
|
(22, 'Whitlock', 'Irene', 1925, 1993, 'Millhaven'),
|
|
(23, 'Amos', 'Leonard', 1915, 1989, 'Millhaven'),
|
|
(24, 'Bly', 'Marjorie', 1930, NULL, 'Millhaven'),
|
|
-- Dudley : deux dossiers anciens
|
|
(25, 'Guthrie', 'Samuel', 1935, NULL, 'Dudley'),
|
|
(26, 'Lomax', 'Edna', 1940, NULL, 'Dudley');
|
|
|
|
-- ------------------------------------------------------------------
|
|
-- EMPLOIS (usine Chaco Chicken)
|
|
-- ------------------------------------------------------------------
|
|
INSERT INTO EMPLOIS (id, id_habitant, poste, annee_embauche, annee_fin) VALUES
|
|
(1, 1, 'Directeur', 1944, NULL),
|
|
(2, 2, 'Ouvriere de decoupe', 1932, 1972),
|
|
(3, 4, 'Contremaitresse', 1935, 1975),
|
|
(4, 5, 'Veterinaire', 1930, 1970),
|
|
(5, 7, 'Ouvrier de decoupe', 1938, 1978),
|
|
(6, 8, 'Ouvriere de decoupe', 1978, 1994),
|
|
(7, 9, 'Conducteur de ligne', 1971, 1995),
|
|
(8, 10, 'Ouvriere de decoupe', 1984, 1994),
|
|
(9, 11, 'Agent d''entretien', 1976, 1994),
|
|
(10, 12, 'Conducteur de ligne', 1982, 1995),
|
|
(11, 13, 'Ouvriere de conditionnement', 1973, 1995),
|
|
(12, 14, 'Conducteur de ligne', 1988, NULL),
|
|
(13, 15, 'Ouvriere de conditionnement', 1992, NULL),
|
|
(14, 16, 'Chef d''equipe', 1980, NULL),
|
|
(15, 26, 'Ouvriere de conditionnement', 1962, 1988);
|
|
|
|
-- ------------------------------------------------------------------
|
|
-- DIAGNOSTICS
|
|
-- ------------------------------------------------------------------
|
|
INSERT INTO DIAGNOSTICS (id, id_habitant, annee, symptome, diagnostic) VALUES
|
|
(1, 8, 1993, 'Troubles de la coordination', 'Trouble neurologique non identifie'),
|
|
(2, 8, 1994, 'Demence rapide', 'Maladie de Creutzfeldt-Jakob'),
|
|
(3, 9, 1994, 'Pertes de memoire', 'Trouble neurologique non identifie'),
|
|
(4, 9, 1995, 'Myoclonies', 'Maladie de Creutzfeldt-Jakob'),
|
|
(5, 10, 1993, 'Troubles de la vision', 'Trouble neurologique non identifie'),
|
|
(6, 10, 1994, 'Demence rapide', 'Maladie de Creutzfeldt-Jakob'),
|
|
(7, 11, 1993, 'Troubles de la coordination', 'Maladie de Creutzfeldt-Jakob'),
|
|
(8, 12, 1994, 'Myoclonies', 'Maladie de Creutzfeldt-Jakob'),
|
|
(9, 13, 1994, 'Pertes de memoire', 'Maladie de Creutzfeldt-Jakob'),
|
|
(10, 14, 1994, 'Fracture du poignet', 'Traumatisme'),
|
|
(11, 15, 1995, 'Angine', 'Infection ORL'),
|
|
(12, 19, 1993, 'Douleurs thoraciques', 'Insuffisance cardiaque'),
|
|
(13, 22, 1992, 'Toux persistante', 'Cancer du poumon'),
|
|
(14, 2, 1987, 'Fatigue', 'Vieillissement'),
|
|
(15, 16, 1994, 'Lombalgie', 'Trouble musculo-squelettique');
|
|
|
|
-- ------------------------------------------------------------------
|
|
-- REPAS communautaires
|
|
-- ------------------------------------------------------------------
|
|
INSERT INTO REPAS (id, date, lieu, occasion) VALUES
|
|
(1, '1988-09-17', 'Ferme Chaco', 'Banquet annuel Chaco Chicken'),
|
|
(2, '1989-06-24', 'Salle des fetes', 'Fete de la moisson'),
|
|
(3, '1990-09-15', 'Ferme Chaco', 'Banquet annuel Chaco Chicken'),
|
|
(4, '1992-12-24', 'Eglise de Dudley', 'Reveillon de Noel'),
|
|
(5, '1995-03-11', 'Ferme Chaco', 'Banquet annuel Chaco Chicken');
|
|
|
|
-- ------------------------------------------------------------------
|
|
-- PARTICIPATIONS
|
|
-- ------------------------------------------------------------------
|
|
INSERT INTO PARTICIPATIONS (id, id_habitant, id_repas) VALUES
|
|
-- Repas 1 (17 septembre 1988)
|
|
(1, 8, 1), (2, 9, 1), (3, 10, 1), (4, 11, 1), (5, 12, 1), (6, 13, 1),
|
|
(7, 1, 1), (8, 2, 1), (9, 4, 1), (10, 14, 1), (11, 16, 1),
|
|
-- Repas 2 (24 juin 1989)
|
|
(12, 8, 2), (13, 9, 2), (14, 10, 2), (15, 11, 2),
|
|
(16, 1, 2), (17, 14, 2), (18, 15, 2), (19, 16, 2),
|
|
-- Repas 3 (15 septembre 1990)
|
|
(20, 8, 3), (21, 9, 3), (22, 12, 3), (23, 13, 3),
|
|
(24, 1, 3), (25, 14, 3), (26, 16, 3), (27, 17, 3),
|
|
-- Repas 4 (24 decembre 1992)
|
|
(28, 9, 4), (29, 10, 4), (30, 11, 4), (31, 12, 4), (32, 13, 4),
|
|
(33, 15, 4), (34, 16, 4),
|
|
-- Repas 5 (11 mars 1995)
|
|
(35, 1, 5), (36, 14, 5), (37, 15, 5), (38, 16, 5), (39, 24, 5);
|
|
|
|
-- ------------------------------------------------------------------
|
|
-- DISPARITIONS
|
|
-- ------------------------------------------------------------------
|
|
INSERT INTO DISPARITIONS (id, id_habitant, date_disparition, statut_enquete) VALUES
|
|
(1, 25, '1961-07-22', 'Classee sans suite'),
|
|
(2, 26, '1988-09-14', 'Classee sans suite'),
|
|
(3, 17, '1995-03-08', 'En cours');
|
|
```
|
|
|
|
> **Remarque.** Les accents ont été retirés des données pour éviter tout problème
|
|
> d'encodage selon les postes. Les apostrophes des textes SQL sont doublées (`d''entretien`) :
|
|
> c'est la façon d'écrire une apostrophe *à l'intérieur* d'une chaîne.
|
|
|
|
### 1.3. Schéma relationnel
|
|
|
|
Dessinez le schéma de la base en identifiant les clés primaires (PK) et les clés
|
|
étrangères (FK).
|
|
|
|
**Question de mise en route :** la table `PARTICIPATIONS` ne contient aucune information
|
|
« propre » — que des clés étrangères. À quoi sert-elle ? Pourquoi ne peut-on pas simplement
|
|
ajouter une colonne `id_repas` dans `HABITANTS` ?
|
|
|
|
---
|
|
|
|
## Partie 2 : Prise en main (requêtes simples)
|
|
|
|
**Pour chaque question, écrivez la requête et notez le résultat obtenu.**
|
|
|
|
**1.** Affichez le nom, le prénom et l'année de naissance de tous les habitants de Dudley,
|
|
triés du plus âgé au plus jeune.
|
|
|
|
**2.** Affichez le nom et le prénom des habitants encore en vie.
|
|
*(Indice : une valeur absente se teste avec `IS NULL`, pas avec `= NULL`.)*
|
|
|
|
**3.** Affichez la liste des postes différents occupés à l'usine Chaco Chicken, sans doublon.
|
|
|
|
**4.** Affichez la date et l'occasion des repas qui se sont tenus à la Ferme Chaco.
|
|
|
|
---
|
|
|
|
## Partie 3 : Croiser les tables (jointures)
|
|
|
|
**5.** Affichez le nom, le prénom et le poste de chaque personne ayant travaillé à l'usine.
|
|
|
|
**6.** Affichez le nom, le prénom et l'année du diagnostic de tous les habitants
|
|
diagnostiqués `'Maladie de Creutzfeldt-Jakob'`.
|
|
|
|
**7.** Affichez le nom et le prénom de toutes les personnes ayant participé au repas
|
|
du 17 septembre 1988.
|
|
*(Indice : il faut passer par `PARTICIPATIONS`, donc joindre trois tables.)*
|
|
|
|
**8.** Affichez le nom, le prénom, la date et le statut de l'enquête pour chaque disparition
|
|
signalée.
|
|
|
|
**9.** Affichez le nom et le prénom des habitants de Dudley qui n'ont **jamais** travaillé
|
|
à l'usine.
|
|
*(Indice : `NOT IN (SELECT ...)` ou une jointure externe.)*
|
|
|
|
---
|
|
|
|
## Partie 4 : Compter et mesurer (agrégations)
|
|
|
|
**10.** Combien y a-t-il d'habitants recensés dans chaque commune ?
|
|
|
|
**11.** Pour chaque repas, affichez sa date et son nombre de participants.
|
|
|
|
**12.** Pour chaque commune, calculez l'âge moyen au décès des personnes décédées.
|
|
*(Indice : l'âge au décès se calcule à partir de deux colonnes.)*
|
|
|
|
**13.** Combien de fois chaque diagnostic apparaît-il dans la base ? Classez du plus
|
|
fréquent au moins fréquent.
|
|
|
|
**14.** Quels habitants ont participé à au moins 3 repas ? Affichez leur nom et leur nombre
|
|
de participations.
|
|
|
|
---
|
|
|
|
## Partie 5 : Une anomalie
|
|
|
|
Les résultats de la question 12 méritent qu'on s'y arrête. Reprenons-les en séparant
|
|
les époques.
|
|
|
|
**15.** Pour chaque commune, calculez l'âge moyen au décès **des personnes décédées avant 1990**.
|
|
Que constatez-vous pour Dudley ?
|
|
|
|
**16.** Même question pour les personnes décédées **à partir de 1990**. Comparez les deux
|
|
résultats commune par commune.
|
|
|
|
> Rédigez deux ou trois phrases : que s'est-il passé à Dudley entre ces deux périodes ?
|
|
> Le même phénomène s'observe-t-il à Millhaven ?
|
|
|
|
**17.** Affichez le nom, le prénom et l'âge au décès de chaque personne diagnostiquée
|
|
`'Maladie de Creutzfeldt-Jakob'`. Combien sont-elles ? Toutes appartiennent-elles à la
|
|
même commune ?
|
|
|
|
---
|
|
|
|
## Partie 6 : La source
|
|
|
|
Vous avez maintenant un groupe de malades identifié. En épidémiologie, la question suivante
|
|
est toujours la même : **qu'ont-ils en commun que les autres n'ont pas ?**
|
|
|
|
**18.** Trouvez **le repas auquel ont participé toutes les personnes** diagnostiquées
|
|
`'Maladie de Creutzfeldt-Jakob'`, et uniquement celui-là.
|
|
|
|
*Indice :* comptez, pour chaque repas, le nombre de malades présents, puis ne gardez que
|
|
les repas où ce nombre est égal au nombre total de malades. La clause qui filtre **après**
|
|
un regroupement s'appelle `HAVING`.
|
|
|
|
**19.** Affichez chaque disparition signalée, accompagnée du repas qui a eu lieu dans les
|
|
**7 jours suivants**, s'il y en a un.
|
|
|
|
*Indice :* en SQLite, `date(d.date_disparition, '+7 days')` renvoie la date décalée de
|
|
sept jours. Vous pouvez alors comparer directement les dates entre elles.
|
|
|
|
**20.** Le dernier banquet a eu lieu le 11 mars 1995. Affichez le nom et le prénom de ses
|
|
participants, et indiquez pour chacun s'il figure ou non dans la table `DIAGNOSTICS`
|
|
avec le diagnostic `'Maladie de Creutzfeldt-Jakob'`.
|
|
|
|
> **Rédigez la conclusion de votre rapport (5 à 10 lignes).** Quelle est la source probable
|
|
> de la contamination ? Quel lien faites-vous entre les dates des banquets et celles des
|
|
> disparitions ? Et surtout : au vu de la question 20, l'affaire est-elle close ?
|
|
|
|
---
|
|
|
|
## Questions de réflexion
|
|
|
|
**1.** Pourquoi avoir séparé `HABITANTS` et `EMPLOIS` dans deux tables, plutôt que d'ajouter
|
|
une colonne `poste` dans `HABITANTS` ?
|
|
|
|
**2.** À la question 18, on compte les malades présents à chaque repas. Un même habitant
|
|
peut apparaître plusieurs fois dans `DIAGNOSTICS`. En quoi cela pourrait-il fausser le
|
|
comptage, et comment s'en prémunir ?
|
|
|
|
**3.** L'incubation d'une maladie à prion dure souvent **plus de dix ans** entre la
|
|
contamination et les premiers symptômes. En quoi ce délai rend-il ce type d'enquête
|
|
difficile ? Quelles données faudrait-il conserver, et pendant combien de temps ?
|
|
|
|
**4.** La base ne contient que les repas *déclarés* et les disparitions *signalées*.
|
|
Quelles conclusions cela vous interdit-il de tirer ? Formulez une phrase précisant ce que
|
|
vos requêtes démontrent réellement, et ce qu'elles ne font que suggérer.
|
|
|
|
---
|
|
|
|
## Barème indicatif
|
|
|
|
| Partie | Points |
|
|
|--------|--------|
|
|
| Partie 1 : schéma et question de mise en route | 2 |
|
|
| Partie 2 : requêtes simples | 3 |
|
|
| Partie 3 : jointures | 4 |
|
|
| Partie 4 : agrégations | 4 |
|
|
| Partie 5 : anomalie + rédaction | 3 |
|
|
| Partie 6 : source + conclusion | 3 |
|
|
| Questions de réflexion | 1 |
|
|
| **Total** | **20** |
|
|
|
|
---
|
|
|
|
Auteur : Florian Mathieu
|
|
|
|
Licence CC BY-SA
|
|
|
|
<a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/"><img alt="Licence Creative Commons" style="border-width:0" src="https://i.creativecommons.org/l/by-sa/4.0/88x31.png" /></a> <br />Ce cours est mis à disposition selon les termes de la <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">Licence Creative Commons Attribution - Partage dans les Mêmes Conditions 4.0 International</a>.
|