Demography

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.

Question 1

Show, for everyone, how many brothers and sisters, he (she) has. (Answer)

Question 2

Display, for each individual, the name of his grandparents, on the father's side. (Answer)

Question 3

Show the mean age of women at the time of their marriage. (Answer)

Question 4

Display the name of the children (and the number of individuals) whose parents do not have the same nationality. (Answer)

Question 5

Display the families (name of the father and the name of the mother) who have more than 2 children. (Answer)

Question 6

Display the rank (senior, second child)... of the individual with a number equal to 11. (Answer)