# Demography

##### Problem

Demography: We focus on a population with which we wish 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

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

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.

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)