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.
|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. But will be able to play two roles if it is connected with a double way with another city: for instance B.
For further information on the different steps for building the data diagram refers, in these pages, to the column DB Design.
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
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)
DISTANCES(VilleDepart, VilleArrivee, Distance)
5-Connecting entities and associations.
|Source entity||Goal entity||Relation on attribute||Type of relation|
|VILLES||DISTANCES||Ville / VilleDepart||1-m|
|VILLES||DISTANCES||Ville / VilleArrivee||1-m|
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".
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.
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)