Problem
We focus on a population with whom we want to represent different relationships. Among other things, we want to model traditional relations, such as: "Marriage", "Child of "... The system to be developed should address modelling, storage and manipulation of relationships within the reference population.
Data dictionary
| Mnemonic | Wording |
| Numind | Number of the person. |
| Nom | Name of the person |
| Sexe | Gender of the person: M or F. |
| Dtnaiss | Birthdate of the person. |
| Nation | Nationality of the person |
| Dept | Department in which the individual resides. |
| Dtmariage | Date for the wedding. |
To model the "Marriage" and "Child" relationships, the Numind attribute must play a number of roles:
1-If the individual is male, that of the "husband" (NumindMari) or the "father" (NumindPère)
2-If the individual is female, that of the "woman" (NumindFemme) or the "mother" (NumindMère)
3-Regardless of the gender of the person, that of "child of" (Numindenfant)
Data Diagram
For more information on the various steps in building the data diagram, see the column DB Design in these pages.
1-Simple FDs
Numind -> Nom, Sexe, Dtnaiss, Nation, Dept
NumindEnfant -> NumindPère, NumindMère, Nom, Sexe, Dtnaiss, Nation, Dept
2-Non-classified attributes and compound FDs.
The attributes NumindMari, NumindFemme, Dtmariage were not classified in the previous stage, they intervene in compound FD which is the following:
NumindMari, NumindFemme -> Dtmariage, Nom, Sexe, Dtnaiss, Nation, Dept
3-Eliminating transitivity.
Numind -> Nom, Sexe, Dtnaiss, Nation, Dept
NumindEnfant -> NumindPère, NumindMère
NumindMari, NumindFemme -> Dtmariage
4-Building of entities and associations.
INDIVIDU(Numind, Nom, Sexe, Dtnaiss, Nation, Dept)
MARIAGE(NumindMari, NumindFemme, Dtmariage)
ENFANT(NumindEnfant, NumindPère, NumindMère)
5-Connecting entities and associations.
| Source entity | Goal entity | Relation on attribute | Type of relation |
| INDIVIDU | MARIAGE | Numind / NumindMari | 1-m |
| INDIVIDU | MARIAGE | Numind / NumindFemme | 1-m |
| INDIVIDU | ENFANT | Numind / NumindEnfant | 1-1 |
| ENFANT | MARIAGE | NumindEnfant / NumindMari | 1-m |
| ENFANT | MARIAGE | NumindEnfant / NumindFemme | 1-m |
6- Data Diagram
The "optimum" data structure is the following (keys are highlighted in bold):
In this diagram, relations between tables were not represented (to clarify reading) because they depend on the treatments, and on the various roles that the attribute Numind is going to play, according to the questions that will be asked.
Queries
To view each table in the database, type:
SELECT * FROM Name_of_the_table;
within a frame containing the button "Execute".
Note for English readers: The value of a string attribute should be written in French. To view the true content of string attributes, use the command above.
Show, for everyone, how many brothers and sisters, he (she) has. (Answer)
Display, for each individual, the name of his grandparents, on the father's side. (Answer)
Show the mean age of women at the time of their marriage. (Answer)
Display the name of the children (and the number of individuals) whose parents do not have the same nationality. (Answer)
Display the families (name of the father and the name of the mother) who have more than 2 children. (Answer)
Display the rank (senior, second child)... of the individual with a number equal to 11. (Answer)
