Geography

Problem

We're looking at a group of cities linked together through a road network. Some connections between cities are unidirectional, while others are double. Every city is characterized by its name, its population and its belonging to a department and a region. To simplify the presentation, we will then consider that the road "network" comprises the cities represented in the following table:

The numbers in red indicate the distances, in kilometers, 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 two cities, the “City” attribute will play two roles:

1- City of departure: attribute "VilleDepart"
2- Destination city: attribute "VilleArrivee»

In the case of a double way connection between 2 cities, the same city will be successively considered as the city of departure then the city of arrival. We have, for example, B-C and C-B or B-D and D-B.
If the connection between two cities is uni-directional, in our example E-B and E-F, one of the two cities will play only one of the two roles: City of departure or City of arrival. City will be able to play two roles if it is connected with a double way with another city: for instance B.

Data Diagram

For further information on the different steps for building the data diagram refers, in these pages, to the column DB Design.

1-Simple FDs

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

2-Unclassified attributes and compound FDs.

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

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

3-Eliminating transitivity.

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

4-Building of entities and associations.

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

5-Connecting entities and associations.

Source entityGoal entityRelation on attributeType of relation
DEPARTEMENTVILLENumdept1-m
REGIONDEPARTEMENTNumregion1-m
VILLESDISTANCESVille / VilleDepart1-m
VILLESDISTANCESVille / VilleArrivee1-m

6-Data Diagram

The "optimal" data structure is as follows (keys are highlighted):

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

Contents 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 view each table in the database, type:

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

Note for English readers: The value of a string attribute should be spelled in French. To view the content of string attributes, use the command above.

Question 1
Show, by region and department, the number of towns with more than 100,000 inhabitants. (Answer)

Question 2
Display the list of all unidirectional connections between the two cities where the population of one of the two cities is less than 80000 inhabitants. (Answer)

Question 3
Display the list of towns, separated by 2 sections of road, and the distance between them. (Answer)

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

Question 5
Display the number of inhabitants per region and department. It is assumed that the population of the area is that of the cities that make up the road network. (Answer).

Question 6
Enter the name of the region with the longest road network and the number of kilometers of that network.
This question must be treated as "static" because it involves the creation of work tables. (Answer)