Logo Bases de données
Français     English

Applications of Databases to Humanities and Social Sciences

Geography
Questions, comments, suggestions?


Geography

Problem

We consider a set of towns connected together by a road network. Some connections between cities are one-way and others are double-ways. Each city is characterized by its name, its population and its membership to a department and a region. It will be considered thereafter, and to simplify the presentation, that the road "network" consists of the cities represented on the following diagram :

Diagram of the roads network

The numbers represent distances between cities.

Data dictionary

Mnemonic Wording
Ville
Name of the city
Population
Number of inhabitants in the city
NumDept
Number of the department in which the city is
NomDept
Name of the department in which the city is
NumRegion
Number of the region in which the city is
NomRegion
Name of the region in which the city is
Distance
Distance, in kilometers, between 2 cities


To model the distance between 2 cities, the attribute "Ville" will be brought to play two roles :

1- That of the city of departure : attribute "VilleDepart"
2- That of the city of arrival : attribute "VilleArrivee"

In the case of a double-ways connection between 2 cities, the same city will be successively considered as city of departure then city of arrival. In our case, for example B-C and C-B or B-D and D-B.

If the connection between two cities is one-way, in our example E-B and E-F, one of the 2 cities will play only one of the 2 roles: City of departure or City of arrival, but will be able to play two roles if it is the object of a connection with a double-ways with another city : for example B.

Diagram of data

For a further information on the different stages for building the diagram of data refer, in these pages, to the topic DB Design.

1-Simple FDs

Ville -> Population, Numdept, Nomdept, Numregion, Nomregion
Numdept -> Nomdept, Numregion, Nomregion
Numregion -> Nomregion

2-Not classified attributes and compound FDs

The attributes VilleDepart, VilleArrivee, Distance were not classified in the preceding stage, they intervene in a compound FD which is the following one :

VilleDepart, VilleArrivee-> Distance, Population, Numdept, Nomdept, Numregion, Nomregion

3-Elimination of transitivities

Ville -> Population, Numdept
Numdept -> Nomdept, Numregion
Numregion -> Nomregion
VilleDepart, VilleArrivee-> Distance

4-Construction of the entities and associations

VILLES(Ville, Population, Numdept)
DEPARTEMENT(Numdept, Nomdept, Numregion)
REGION(Numregion, Nomregion)
DISTANCES(VilleDepart, VilleArrivee, Distance)

5-Linking of entities and associations

Source entity
Goal entity
Relation on attribute
Type of relation
DEPARTEMENT
VILLE
Numdept
1-m
REGION
DEPARTEMENT
Numregion
1-m
VILLES
DISTANCES
Ville / VilleDepart
1-m
VILLES
DISTANCES
Ville / VilleArrivee
1-m

6-Diagram of data

The "optimal" data structure is the following one (keys are in bold type):

In this diagram, relations between the table "Distances" and the table "Villes" are not represented because they depend on treatments, and so on various roles that attribute Ville is going to play according to the questions that will be asked.

Content of the table "Distances"

 

VilleDepart
VilleArrivee
Distance
A
E
50
A
F
60
B
C
50
B
D
60
C
B
50
D
B
60
D
E
30
E
A
50
E
B
60
E
D
30
E
F
100
F
A
60


Queries

To visualize the contents of each table of the database, type :

SELECT * FROM Name_of_the_table;
in a frame containing the button "Execute".

Note to english readers : The value of a string attribute must be spell in french. To see the real content of string attributes, use the command above.

Question 1

Display, by region and department, the number of cities having more than 100000 inhabitants. (Answer)

Question 2

Display the list of all the one-way connections between two cities for which the population of one of the 2 cities is lower than 80000 inhabitants. (Answer)

Question 3

Display the list of cities, distant from 2 road sections, and the distance which separates them. (Answer)

Question 4

Display the list of cities, which can be distant from 4 road sections (there can indeed be shorter connections into 2 or 3 sections), and the distance which separates them. (Answer)

Question 5

Display the number of inhabitants per region and department. We suppose that the population of a region is that of the cities which are part of the road network (Answer).

Question 6

Give the name of the region which has the longest road network and the number of kilometers of this network.
This question must be treated in a "static" way, because it implies the creation of work tables. (Answer)


©Marc Grange, February 2001 | Last update: February 5, 2013