Applications of Databases to Humanities and Social Sciences

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 :

The numbers represent distances between cities.

Data dictionary

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 column 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

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"

 

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: April 10, 2006