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, it will be considered that the "road network" includes the cities indicated in the table below:

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. The City attribute can play two roles if it is connected in a double way with another city: e.g., 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 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-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)