Schéma : world
world, relationnel, pattes de corbeau, crowfoot
Présentation du schéma world, pour l’entrainement à PostGres.
- L3 MIASHS/Ingémath
- Université Paris Cité
- Année 2024-2025
- Course Homepage
- Moodle
Créé avec DbSchema
Contexte
world est un schéma formé par trois tables country, city et countrylanguage. Les instances de ce schéma sont censées fournir des données de géographie politique : un état du monde à une date donnée.
La table country nous renseigne sur les états reconnus (à l’ONU) et sur des territoires jugés plus ou moins autonomes Dépendances, Territoires non autonomes
La table city nous renseigne sur les villes les plus importantes de chaque territoire.
Latable countrylanguage nous indique quelles langues sont parlées dans quels pas.
L’instance du schéma utilisée donne une idée du monde au début des années 2000.
Diagrammme en pattes de corbeau (crowfoot)
Un diagramme en pattes de corbeau est formé par les tables (pour chaque colonne/attribut, nom et domaine) et des arcs qui décrivent des contraintes référentielles.
Table world.country
| Idx | Name | Data Type | Description |
|---|---|---|---|
| * 🔑 ⬋ | countrycode | char(3) | Code ISO à 3 symboles |
| * | name_country | text | Nom du territoire en Anglais |
| * | continent | text | |
| * | region | text | |
| * | surfacearea | real | Surface en Km2 |
| indepyear | smallint | Année de fondation/indépendance | |
| * | population_country | integer | Nombre d’habitants |
| lifeexpectancy | real | Espérance de vie à naissance | |
| gnp | numeric(10,2) | PIB (en Millions de USD?) | |
| gnpold | numeric(10,2) | ||
| * | localname | text | Nom du territoire dans les langues officielles |
| * | governmentform | text | Type de régime |
| headofstate | text | Chef de l’état | |
| ⬈ | capital | integer | Identifiant (id) de la capitale dans city |
| * | code2 | char(2) | Code ISO à 2 symboles |
gnp est formé par les initiales de Gross National Product (Produit National Brut), un indicateur de revenu défini par les économistes.
lifeexpectancy (espérance de vie à la naissance) est un indicateur de santé publique construit par les démographes. Ce n’est pas l’âge moyen au décès.
Chaque ligne de country est identifiée par la valeur de countrycode. Cet attribut ne peut pas être NULL. Deux lignes distinctes de country ne peuvent coïncider sur l’attribut countrycode.
L’attribut countrycode peut être utilisé pour désigner une ligne de la table country. Cette possibilité est utilisée dans les tables city et countrylanguage
En jargon, l’attribut countrycode sert de clé primaire à la table country
Un extrait de la table concernant les pays de région Western Europe permet de comprendre au moins une partie de la signification et des usages des colonnes
| countrycode | code2 | name_country | localname | governmentform | headofstate | capital |
|---|---|---|---|---|---|---|
| NLD | NL | Netherlands | Nederland | Constitutional Monarchy | Beatrix | 5 |
| BEL | BE | Belgium | België/Belgique | Constitutional Monarchy, Federation | Albert II | 179 |
| AUT | AT | Austria | Österreich | Federal Republic | Thomas Klestil | 1523 |
| LIE | LI | Liechtenstein | Liechtenstein | Constitutional Monarchy | Hans-Adam II | 2446 |
| LUX | LU | Luxembourg | Luxembourg/Lëtzebuerg | Constitutional Monarchy | Henri | 2452 |
| MCO | MC | Monaco | Monaco | Constitutional Monarchy | Rainier III | 2695 |
| FRA | FR | France | France | Republic | Jacques Chirac | 2974 |
| DEU | DE | Germany | Deutschland | Federal Republic | Johannes Rau | 3068 |
| CHE | CH | Switzerland | Schweiz/Suisse/Svizzera/Svizra | Federation | Adolf Ogi | 3248 |
Foreign Keys (clée étrangères)
La colonne capital ne contient pas les noms de capitale (il existe des territoires sans capitale), elle contient des entiers (ou NULL) qui renvoient à des lignes dans la table city.
| Type | Name | On |
|---|---|---|
| country_capital_fkey | ( capital ) ref world.city (id) |
Constraints
Le domaine de la colonne (de l’atttribut) continent est un type énuméré (défini en extension).
| Name | Definition |
|---|---|
| country_continent_check | ((continent = ‘Asia’::text) OR (continent = ‘Europe’::text) OR (continent = ‘North America’::text) OR (continent = ‘Africa’::text) OR (continent = ‘Oceania’::text) OR (continent = ‘Antarctica’::text) OR (continent = ‘South America’::text)) |
Table world.city
| Idx | Name | Data Type |
|---|---|---|
| * 🔑 ⬋ | id | integer |
| * | name | text |
| * ⬈ | countrycode | char(3) |
| * | district | text |
| * | population | integer |
Chaque ligne de city est identifiée par la valeur de is. Cet attribut ne peut pas être NULL. Deux lignes distinctes de city ne peuvent coïncider sur l’attribut id.
Foreign Keys
La colonne countrycode ne contient pas les noms des pays, mais le code à 3 symboles du territoire où se situe la ville
| Type | Name | On |
|---|---|---|
| city_country_fk | ( countrycode ) ref world.country (countrycode) |
L’extrait suivant correspond au villes du pays nommé France.
| id | name | district | population |
|---|---|---|---|
| 2974 | Paris | Île-de-France | 2125246 |
| 2975 | Marseille | Provence-Alpes-Côte | 798430 |
| 2976 | Lyon | Rhône-Alpes | 445452 |
| 2977 | Toulouse | Midi-Pyrénées | 390350 |
| 2978 | Nice | Provence-Alpes-Côte | 342738 |
| 2979 | Nantes | Pays de la Loire | 270251 |
| 2980 | Strasbourg | Alsace | 264115 |
| 2981 | Montpellier | Languedoc-Roussillon | 225392 |
| 2982 | Bordeaux | Aquitaine | 215363 |
| 2983 | Rennes | Haute-Normandie | 206229 |
| 2984 | Le Havre | Champagne-Ardenne | 190905 |
| 2985 | Reims | Nord-Pas-de-Calais | 187206 |
| 2986 | Lille | Rhône-Alpes | 184657 |
| 2987 | St-Étienne | Bretagne | 180210 |
| 2988 | Toulon | Provence-Alpes-Côte | 160639 |
| 2989 | Grenoble | Rhône-Alpes | 153317 |
| 2990 | Angers | Pays de la Loire | 151279 |
| 2991 | Dijon | Bourgogne | 149867 |
| 2992 | Brest | Bretagne | 149634 |
| 2993 | Le Mans | Pays de la Loire | 146105 |
| 2994 | Clermont-Ferrand | Auvergne | 137140 |
| 2995 | Amiens | Picardie | 135501 |
| 2996 | Aix-en-Provence | Provence-Alpes-Côte | 134222 |
| 2997 | Limoges | Limousin | 133968 |
| 2998 | Nîmes | Languedoc-Roussillon | 133424 |
| 2999 | Tours | Centre | 132820 |
| 3000 | Villeurbanne | Rhône-Alpes | 124215 |
| 3001 | Metz | Lorraine | 123776 |
| 3002 | Besançon | Franche-Comté | 117733 |
| 3003 | Caen | Basse-Normandie | 113987 |
| 3004 | Orléans | Centre | 113126 |
| 3005 | Mulhouse | Alsace | 110359 |
| 3006 | Rouen | Haute-Normandie | 106592 |
| 3007 | Boulogne-Billancourt | Île-de-France | 106367 |
| 3008 | Perpignan | Languedoc-Roussillon | 105115 |
| 3009 | Nancy | Lorraine | 103605 |
| 3010 | Roubaix | Nord-Pas-de-Calais | 96984 |
| 3011 | Argenteuil | Île-de-France | 93961 |
| 3012 | Tourcoing | Nord-Pas-de-Calais | 93540 |
| 3013 | Montreuil | Île-de-France | 90674 |
La description des tables country et city n’impose pas que la capitale d’un pays soit une ville située dans ce pays.
Est-ce que cette contrainte devrait être mise en place ?
Table world.countrylanguage
La table countrylanguage nous renseigne sur les langues parlées dans les différents pays.
| Idx | Name | Data Type |
|---|---|---|
| * 🔑 ⬈ | countrycode | char(3) |
| * 🔑 | language | text |
| * | isofficial | boolean |
| * | percentage | real |
Un extrait correspondant au pays FRA (France) aide à comprendre
| countrycode | language | isofficial | percentage |
|---|---|---|---|
| FRA | French | t | 93.6 |
| FRA | Arabic | f | 2.5 |
| FRA | Portuguese | f | 1.2 |
| FRA | Italian | f | 0.4 |
| FRA | Spanish | f | 0.4 |
| FRA | Turkish | f | 0.4 |
Les lignes nous renseignent sur 6 langues. Une documentation complète serait nécessaire pour vérifier si le percentage correspond à la proportion d’individus qui déclarent telle ou telle langue comme langue maternelle. On ne sait pas si un individu peut déclarer posséder plusieurs langues.
La documentation d’une base de données ne se réduit pas toujours à un diagramme.
D’après l’instance du schéma world, la seule langue réputée officielle en France est le français.
Qu’est ce qu’une langue officielle ?
Dans la table countrylanguage, l’attribut countrycode renvoie à une ligne de country grâce à l’attribut de même nom dans la table country.
Foreign Keys
| Type | Name | On |
|---|---|---|
| countrylanguage_countrycode_fkey | ( countrycode ) ref world.country (countrycode) |
Dans le schéma world, chaque ligne de countrylanguage est identifiée par les valeurs des colonnes countrycode et language. On parle de clé primaire composée.