Modèle de données PostgreSQL

Cette section présente une implémentation de travail du modèle de données du projet COUNT, réalisée à des fins de prototypage et de formalisation technique.

Les scripts SQL présentés ci-dessous ne constituent pas le schéma opérationnel officiel du projet COUNT.

Implémentation du schéma relationnel

Le schéma conceptuel documenté précédemment est décliné sous la forme d’un schéma relationnel implémenté en PostgreSQL. Il repose sur une modélisation en étoile, articulant une table de faits et plusieurs tables de dimensions.

Script SQL — création des tables

Le script suivant illustre la création des principales tables du modèle de données PostgreSQL.

 1-- DIMENSIONS
 2
 3CREATE TABLE dim_date (
 4  date_id DATE PRIMARY KEY,
 5  annee INT,
 6  mois INT,
 7  trimestre INT
 8);
 9
10CREATE TABLE dim_cinema (
11  cinema_id SERIAL PRIMARY KEY,
12  nom_cinema TEXT,
13  departement TEXT,
14  nb_entrees INT,
15  nb_jours_ouverture INT,
16  nb_films_programmes INT,
17  annee_construction INT,
18  nb_collaborateurs INT,
19  presence_clim BOOLEAN,
20  vente_confiserie BOOLEAN,
21  enquete_mobilite BOOLEAN
22);
23
24CREATE TABLE dim_activity_type (
25  activity_type_id SERIAL PRIMARY KEY,
26  ensemble TEXT,
27  poste TEXT,
28  sous_poste TEXT,
29  libelle TEXT,
30  unite_reference TEXT
31);
32
33CREATE TABLE dim_emission_factor (
34  ef_id SERIAL PRIMARY KEY,
35  activity_type_id INT REFERENCES dim_activity_type(activity_type_id),
36  emission_factor NUMERIC,
37  unite_ef TEXT
38);
39
40-- FACT TABLE
41
42CREATE TABLE fact_activity (
43  fact_id SERIAL PRIMARY KEY,
44  cinema_id INT REFERENCES dim_cinema(cinema_id),
45  date_id DATE REFERENCES dim_date(date_id),
46  activity_type_id INT REFERENCES dim_activity_type(activity_type_id),
47  quantity NUMERIC
48);
49
50--DROP TABLE IF EXISTS fact_activity CASCADE;
51--DROP TABLE IF EXISTS dim_emission_factor CASCADE;
52--DROP TABLE IF EXISTS dim_activity_type CASCADE;
53--DROP TABLE IF EXISTS dim_cinema CASCADE;
54--DROP TABLE IF EXISTS dim_date CASCADE;

Script SQL — insertion de données (avec les données fictives)

Un script complémentaire permet l’initialisation de la base de données à partir de données de test ou d’exemples structurés. Il vise à valider la cohérence du schéma, les relations entre tables et le bon fonctionnement des règles de calcul en phase de prototypage.

  1-- Dates
  2INSERT INTO dim_date (date_id, annee, mois, trimestre) VALUES
  3('2025-01-01', 2025, 1, 1);
  4
  5-- Activity types (postes carbone)
  6INSERT INTO dim_activity_type (activity_type_id, ensemble, poste, sous_poste, libelle, unite_reference) values
  7(1, 'Fonctionnement', 'Bâtiment', 'Surface plancher', 'Surface plancher du cinéma', 'm2'),
  8(2, 'Fonctionnement', 'Bâtiment', 'Surface rénovation', 'Surface rénové du cinéma', 'm2'),
  9(3, 'Fonctionnement', 'Bâtiment', 'Surface supplémentaire', 'Surface supplémentaire du cinéma', 'm2'),
 10(4, 'Fonctionnement', 'Bâtiment', 'Coût de rénovation', 'Coût de rénovation', '€'),
 11(5, 'Fonctionnement', 'Bâtiment', 'Surface totale', 'Surface totale du bâtiment (cinéma inclus)', 'm2'),
 12(6, 'Fonctionnement', 'Bâtiment', 'Parking', 'Nombre de parking', 'places'),
 13(7, 'Fonctionnement', 'Équipe', 'Rythme de travail', 'Repas consommés', 'repas'),
 14(8, 'Fonctionnement', 'Équipe', 'Rythme de travail', 'Distance domicile-travail', 'km'),
 15(9, 'Fonctionnement', 'Déplacements professionnels', 'RER & Trasilien', 'Déplacements professionnels', 'km'),
 16(10, 'Fonctionnement', 'Déplacements professionnels', 'Métro/Tram', 'Déplacements professionnels', 'km'),
 17(11, 'Fonctionnement', 'Déplacements professionnels', 'Vélo électrique', 'Déplacements professionnels', 'km'),
 18(12, 'Fonctionnement', 'Déplacements professionnels', 'Vélo classique', 'Déplacements professionnels', 'km'),
 19(13, 'Fonctionnement', 'Déplacements professionnels', 'Marche', 'Déplacements professionnels', 'km'),
 20(14, 'Fonctionnement', 'Déplacements professionnels', 'Voiture diesel', 'Déplacements professionnels', 'km'),
 21(15, 'Fonctionnement', 'Déplacements professionnels', 'Voiture essence', 'Déplacements professionnels', 'km'),
 22(16, 'Fonctionnement', 'Déplacements professionnels', 'Voiture hybride', 'Déplacements professionnels', 'km'),
 23(17, 'Fonctionnement', 'Déplacements professionnels', 'Voiture électrique', 'Déplacements professionnels', 'km'),
 24(18, 'Fonctionnement', 'Déplacements professionnels', 'Moto', 'Déplacements professionnels', 'km'),
 25(19, 'Fonctionnement', 'Déplacements professionnels', 'Moto', 'Déplacements professionnels', 'km'),
 26(20, 'Fonctionnement', 'Déplacements professionnels', 'Scooter', 'Déplacements professionnels', 'km'),
 27(21, 'Fonctionnement', 'Déplacements professionnels', 'Trotinette électrique', 'Déplacements professionnels', 'km'),
 28(22, 'Fonctionnement', 'Énergie', 'Électricité', 'Consommation électrique', 'kWh'),
 29(23, 'Fonctionnement', 'Énergie', 'Gas', 'Consommation gas', 'm3'),
 30(24, 'Fonctionnement', 'Énergie', 'Fuel', 'Consommation fuel', 'L'),
 31(25, 'Fonctionnement', 'Énergie', 'Réseaux urbains chaleur', 'Consommation réseau chaleur', 'kWh'),
 32(26, 'Fonctionnement', 'Énergie', 'Réseaux urbains chaleurfroid', 'Consommation réseau froid', 'kWh'),
 33(27, 'Fonctionnement', 'Énergie', 'Bois granulés', 'Consommation bois granulés', 'kWh'),
 34(28, 'Fonctionnement', 'Énergie', 'Groupes électrogène', 'Consommation  électrogène', 'u'),
 35(29, 'Fonctionnement', 'Énergie', 'Climatisation', 'Consommation climatisation', 'kWh'),
 36(30, 'Fonctionnement', 'Énergie', 'Diesel', 'Consommation  diesel', 'L'),
 37(31, 'Fonctionnement', 'Activités de bureau', 'Fournitures', 'Fournitures', '€'),
 38(32, 'Fonctionnement', 'Activités de bureau', 'Fournitures', 'Services', '€'),
 39(33, 'Fonctionnement', 'Activités de bureau', 'Ordinateurs fixes', 'Achetés', 'u'),
 40(34, 'Fonctionnement', 'Activités de bureau', 'Ordinateurs fixes', 'Loués', 'u'),
 41(35, 'Fonctionnement', 'Activités de bureau', 'Ordinateurs portables', 'Achetés', 'u'),
 42(36, 'Fonctionnement', 'Activités de bureau', 'Ordinateurs portables', 'Loués', 'u'),
 43(37, 'Mobilité spectateurs', 'Déplacements', 'RER & Transilien', 'Déplacements spectateurs', 'km'),
 44(38, 'Mobilité spectateurs', 'Déplacements', 'Métro/Tram', 'Déplacements spectateurs', 'km'),
 45(39, 'Mobilité spectateurs', 'Déplacements', 'Bus', 'Déplacements spectateurs', 'km'),
 46(40, 'Mobilité spectateurs', 'Déplacements', 'Vélo électrique', 'Déplacements spectateurs', 'km'),
 47(41, 'Mobilité spectateurs', 'Déplacements', 'Vélo classique', 'Déplacements spectateurs', 'km'),
 48(42, 'Mobilité spectateurs', 'Déplacements', 'Vélo électrique', 'Déplacements spectateurs', 'km'),
 49(43, 'Mobilité spectateurs', 'Déplacements', 'Marche', 'Déplacements spectateurs', 'km'),
 50(44, 'Mobilité spectateurs', 'Déplacements', 'Voiture diesel', 'Déplacements spectateurs', 'km'),
 51(45, 'Mobilité spectateurs', 'Déplacements', 'Voiture hybride', 'Déplacements spectateurs', 'km'),
 52(46, 'Mobilité spectateurs', 'Déplacements', 'Voiture électrique', 'Déplacements spectateurs', 'km'),
 53(47, 'Mobilité spectateurs', 'Déplacements', 'Moto', 'Déplacements spectateurs', 'km'),
 54(48, 'Mobilité spectateurs', 'Déplacements', 'Scooter', 'Déplacements spectateurs', 'km'),
 55(49, 'Mobilité spectateurs', 'Déplacements', 'Trotinette électrique', 'Déplacements spectateurs', 'km'),
 56(50, 'Tournées avant-première', 'Equipes reçues', 'NULL', 'NULL', 'u'),
 57(51, 'Tournées avant-première', 'Déplacement', 'NULL', 'NULL', 'u'),
 58(52, 'Tournées avant-première', 'Déplacement', 'RER & Transilien', 'Déplacements tournées', 'km'),
 59(53, 'Tournées avant-première', 'Déplacement', 'Métro/Tram', 'Déplacements tournées', 'km'),
 60(54, 'Tournées avant-première', 'Déplacement', 'Bus', 'Déplacements tournées', 'km'),
 61(55, 'Tournées avant-première', 'Déplacement', 'Vélo électrique', 'Déplacements tournées', 'km'),
 62(56, 'Tournées avant-première', 'Déplacement', 'Vélo classique', 'Déplacements tournées', 'km'),
 63(57, 'Tournées avant-première', 'Déplacement', 'Vélo électrique', 'Déplacements tournées', 'km'),
 64(58, 'Tournées avant-première', 'Déplacement', 'Marche', 'Déplacements tournées', 'km'),
 65(59, 'Tournées avant-première', 'Déplacement', 'Voiture diesel', 'Déplacements tournées', 'km'),
 66(60, 'Tournées avant-première', 'Déplacement', 'Voiture hybride', 'Déplacements tournées', 'km'),
 67(61, 'Tournées avant-première', 'Déplacement', 'Voiture électrique', 'Déplacements tournées', 'km'),
 68(62, 'Tournées avant-première', 'Déplacement', 'Moto', 'Déplacements tournées', 'km'),
 69(63, 'Tournées avant-première', 'Déplacement', 'Scooter', 'Déplacements tournées', 'km'),
 70(64, 'Tournées avant-première', 'Déplacement', 'Trotinette électrique', 'Déplacements tournées', 'km'),
 71(65, 'Salles et cabines', 'Matériell technique', 'Salle', 'Nombre de salles' 'u'),
 72(66, 'Salles et cabines', 'Matériell technique', 'Projecteur', 'Nombre de projecteurs' 'u'),
 73(67, 'Salles et cabines', 'Matériell technique', 'Écran', 'Nombre de lecran', 'u'),
 74(68, 'Salles et cabines', 'Matériell technique', 'Fauteuil', 'Nombre de fauteuil', 'u'),
 75(69, 'Salles et cabines', 'Matériell technique', 'Système de son', 'Système de son', 'u'),
 76(70, 'Salles et cabines', 'Matériell technique', 'Stockage de films', 'Serveur', 'Go'),
 77(71, 'Salles et cabines', 'Matériell technique', 'Stockage de films', 'Baies de disques', 'u'),
 78(72, 'Salles et cabines', 'Matériell technique', 'Films dématérisalisé', 'Films reçus en dématérialisé', 'Go'),
 79(73, 'Salles et cabines', 'Matériell technique', 'Stockage de films', 'Données stockées dans le cloud', 'Go'),
 80(74, 'Salles et cabines', 'Matériell technique', 'Stockage de films', 'Données stockées dans les disques dures', 'Go'),
 81(75, 'Salles et cabines', 'Autre matériel', 'Lunettes 3D', 'NULL', 'u'),
 82(76, 'Confiseries et boissons', 'Achats', 'Part','Nombre de part achetés par les spectateurs', 'g'),
 83(77, 'Confiseries et boissons', 'Electroménager', 'Réfrigérateurs', 'Achetés', 'u'),
 84(78, 'Confiseries et boissons', 'Electroménager', 'Réfrigérateurs', 'Loués', 'u'),
 85(79, 'Déchets', 'Déchets ordinaires', 'Ordures ménagères', 'Nombre des bennes', 'u'),
 86(80, 'Déchets', 'Déchets ordinaires', 'Ordures ménagères', 'Taille des bennes', 'u'),
 87(81, 'Déchets', 'Déchets ordinaires', 'Ordures ménagères', 'Fréquence de ramassage', 'fois/semaine'),
 88(82, 'Déchets', 'Déchets exeptionnels', 'Lampes Xenons', 'Nombre de lampes jettées', 'u'),
 89(83, 'Déchets', 'Déchets exeptionnels', 'Matériel technique', 'Poids de matériel jetté', 'kg'),
 90(84, 'Billetterie et communication', 'Matériel distributeurs', 'Affiches 120x160', 'NULL', 'u'),
 91(85, 'Billetterie et communication', 'Matériel distributeurs', 'Affiches 40x60', 'NULL', 'u'),
 92(86, 'Billetterie et communication', 'Matériel cinéma', 'Programme', 'NULL', 'u'),
 93(87, 'Billetterie et communication', 'Matériel cinéma', 'Affiches', 'NULL', 'u'),
 94(88, 'Billetterie et communication', 'Matériel cinéma', 'Flyers', 'NULL', 'u'),
 95(89, 'Billetterie et communication', 'Communication digitale', 'Newsletters', 'Nombre de newsletters envoyées', 'u'),
 96(90, 'Billetterie et communication', 'Communication digitale', 'Caissons', 'Nombre de caissons daffichage dynamique', 'u'),
 97(91, 'Billetterie et communication', 'Communication digitale', 'Ecrans TV', 'Nombre decran se trouve dans lespaces de circulation', 'u'),
 98(92, 'Billetterie et communication', 'Communication digitale', 'LED', 'Surface daffichage extérieur', 'm2'),
 99(93, 'Billetterie et communication', 'Caisses et bornes', 'Caisse LS', 'Nombre de caisses libre-services', 'u'),
100(94, 'Billetterie et communication', 'Caisses et bornes', 'Caisse classique', 'Nombre de caisses classiques', 'u');
101
102-- Emission factors
103INSERT INTO dim_emission_factor (ef_id, activity_type_id, emission_factor, unite_ef) VALUES
104(20730, 1, 825, 'kgCO2e'),
105(20730, 2, 825, 'kgCO2e'),
106(20730, 3, 825, 'kgCO2e'),
107(43340, 4, 245, 'kgCO2e'),
108(20730, 5, 825, 'kgCO2e'),
109(26008, 6, 92, 'kgCO2e'),
110(20682, 7, 204, 'kgCO2e'),
111(24155, 9, 698, 'kgCO2e'),
112(24155, 10, 698, 'kgCO2e'),
113(24191, 11, 398, 'kgCO2e'),
114(24191, 12, 398, 'kgCO2e'),
115(24170, 13, 1474, 'kgCO2e'),
116(24184, 14, 6317, 'kgCO2e'),
117(24184, 15, 6317, 'kgCO2e'),
118(24184, 16, 6317, 'kgCO2e'),
119(24184, 17, 6317, 'kgCO2e'),
120(24191, 18, 398, 'kgCO2e'),
121(24191, 19, 398, 'kgCO2e'),
122(24191, 20, 398, 'kgCO2e'),
123(24191, 21, 398, 'kgCO2e'),
124(22, 0.056, 'kgCO2e'),
125(15591, 23, 0.056, 'kgCO2e'),
126(24, 0.056, 'kgCO2e'),
127(25, 0.056, 'kgCO2e'),
128(26, 0.056, 'kgCO2e'),
129(27, 0.056, 'kgCO2e'),
130(28, 0.056, 'kgCO2e'),
131(29, 0.056, 'kgCO2e'),
132(30, 0.056, 'kgCO2e'),
133(31, 0.056, 'kgCO2e'),
134(32, 0.056, 'kgCO2e'),
135(33, 0.056, 'kgCO2e'),
136(34, 0.056, 'kgCO2e'),
137(35, 0.056, 'kgCO2e'),
138(36, 0.056, 'kgCO2e'),
139(37, 0.056, 'kgCO2e'),
140(38, 0.056, 'kgCO2e'),
141(39, 0.056, 'kgCO2e'),
142(40, 0.056, 'kgCO2e'),
143(41, 0.056, 'kgCO2e'),
144(42, 0.056, 'kgCO2e'),
145(43, 0.056, 'kgCO2e'),
146(44, 0.056, 'kgCO2e'),
147(45, 0.056, 'kgCO2e'),
148(46, 0.056, 'kgCO2e'),
149(47, 0.056, 'kgCO2e'),
150(48, 0.056, 'kgCO2e'),
151(49, 0.056, 'kgCO2e'),
152(50, 0.056, 'kgCO2e'),
153(51, 0.056, 'kgCO2e'),
154(52, 0.056, 'kgCO2e'),
155(53, 0.056, 'kgCO2e'),
156(54, 0.056, 'kgCO2e'),
157(55, 0.056, 'kgCO2e'),
158(56, 0.056, 'kgCO2e'),
159(57, 0.056, 'kgCO2e'),
160(58, 0.056, 'kgCO2e'),
161(59, 0.056, 'kgCO2e'),
162(60, 0.056, 'kgCO2e'),
163(61, 0.056, 'kgCO2e'),
164(62, 0.056, 'kgCO2e'),
165(63, 0.056, 'kgCO2e'),
166(64, 0.056, 'kgCO2e'),
167(65, 0.056, 'kgCO2e'),
168(66, 0.056, 'kgCO2e'),
169(67, 0.056, 'kgCO2e'),
170(68, 0.056, 'kgCO2e'),
171(69, 0.056, 'kgCO2e'),
172(70, 0.056, 'kgCO2e'),
173(71, 0.056, 'kgCO2e'),
174(72, 0.056, 'kgCO2e'),
175(73, 0.056, 'kgCO2e'),
176(74, 0.056, 'kgCO2e'),
177(75, 0.056, 'kgCO2e'),
178(76, 0.056, 'kgCO2e'),
179(77, 0.056, 'kgCO2e'),
180(78, 0.056, 'kgCO2e'),
181(79, 0.056, 'kgCO2e'),
182(80, 0.056, 'kgCO2e'),
183(81, 0.056, 'kgCO2e'),
184(82, 0.056, 'kgCO2e'),
185(83, 0.056, 'kgCO2e'),
186(84, 0.056, 'kgCO2e'),
187(85, 0.056, 'kgCO2e'),
188(86, 0.056, 'kgCO2e'),
189(87, 0.056, 'kgCO2e'),
190(88, 0.056, 'kgCO2e'),
191(89, 0.056, 'kgCO2e'),
192(90, 0.056, 'kgCO2e'),
193(91, 0.056, 'kgCO2e'),
194(92, 0.056, 'kgCO2e'),
195(93, 0.056, 'kgCO2e'),
196(94, 0.056, 'kgCO2e');
197
198
199-- Cinema
200INSERT INTO dim_cinema (
201  cinema_id, nom_cinema, departement,
202  nb_entrees, nb_jours_ouverture, nb_films_programmes,
203  annee_construction, presence_clim, vente_confiserie, enquete_mobilite
204) VALUES (
205  1, 'Cinéma Lumière', '75-Paris', 180000, 320, 1200,
206  1998, true, true, true
207);

Accès au code source

Les scripts SQL sont disponibles dans le dépôt GitHub associé à cette documentation :