Modélisation d’un SID
(A réaliser en groupe de 2)
Le Ministère de la Santé et du Bien-Etre vous sous-traite la réalisation d’un entrepôt de données pour réaliser des études sur les dépenses de santé dans son pays. Les bases de production de cet entrepôt sont les systèmes d’information des centres de sécurité sociale et des assurances santé complémentaire qui gèrent les dossiers (électroniques) des assurés.
Le schéma de l’entrepôt est constitué des tables suivantes (les clés primaires sont en italiques)
- Date (CléDate, Année, Mois, JourDeMois, JourDeSemaine, TrancheHoraire, DrapeauVacances)
- Acte (CléDate, CléAssuré, CléPraticien, CléPathologie, MontantActes, MontantDesActesComplémentaires, MontantPriseEnChargeCaissePrimaire, MontantPriseEnChargeCaisseComplémentaire, NombreMedicamentsPrescrits, MontantPharmacologieGénérique, MontantPharmacologieNonGenerique, DrapeauActesComplémentairesBiologie, DrapeauActesComplémentairesChirurgie, DrapeauActesComplémentairesRadiologie, DrapeauActesComplémentairesKinésithérapie, DrapeauActesComplémentairesRadiologie, NombreDeJoursDArrêtDeTravail, CoutJoursDArret)
- Assuré (CléAssuré, MoisNaissance, AnnéeNaissance, MoisDécès, AnnéeDécès, Région, Département, District, Ville, Quartier, RevenuAssuré, RevenuFoyer, CatégorieSocioProfessionnel, SousCatégorieSocioProfessionnel, DomaineActivité, CaissePrimaire, CaisseComplémentaire, DrapeauAssuréPrincipal)
- Praticien (CléPraticien, Spécialité, SousSpécialité, Région, Département, District, Ville, Quartier, MoisNaissance, AnnéeNaissance, DrapeauConventionné)
- Pathologie (CléPathologie, DesignationNormalisé, Spécialité, SousSpécialité, TauxDIncapacité, DuréeTraitement, Chronicité, DrapeauMaladieProfessionnelle)
Rétro-Conception
- Quelle est la table de fait dans cet entrepôt ? Justifiez !
- Dessiner le modèle de données en expliquant le ou le(s) tables de faits et de dimensions.
- A votre avis, y a t’il des dimensions dégénérées dans cet entrepôt ? Rappelez la définition et justifiez.
- Donnez les nouvelles tables si on décide de diminuer la taille de la table Assurée par une mini-dimension démographique.
- Donner le script SQL de création des tables (ne pas oublier les clés primaires).
Dimensionnement de l’entrepôt
| Information |
Valeur |
| Nombre d’assurés |
60 Millions |
| Nombre d’actes par praticien et par jour |
20 |
| Nombre de jours de travail d’un praticien |
300 jours par an |
| Nombre de praticiens |
300 000 |
| Montant total moyen d’un acte |
100 € |
| Nombre moyen d’actes supplémentaires prescrit par acte |
0.1 |
| Nombre d’années |
6 |
| Coûts annuel des actes |
180 Milliard € |
| Taille des clés |
4 octets |
| Taille des attributs numériques |
4 octets |
| Taille des attributs booléens (comme les drapeaux !) |
1 octet |
- Donnez le nombre de faits présents dans la table de fait.
- Estimez la taille (en octet) de celle-ci.
Rapports d’analyse (SQL)
- Donnez la requête SQL qui donne le top 10 des sous-spécialités des pathologies qui ont entraîné le plus de dépenses (montant des actes + montant pharmacologies) ?
- Donnez le rapport mensuel de progression du ratio des montants des médicament génériques par rapport aux médicaments non génériques.
- Donnez le rapport précédent mais avec une moyenne glissante sur les 3 mois précédents.