Applications of Databases to Humanities and Social Sciences

Demography

Problem

We consider a population of individuals on whom we wishes to represent various relations. One wishes, among others, to model classic relations like : "Marriage", " Child of "... The system to be developed will have to take into account the modeling the storage and the treatment of the relations of relationships in the population of reference.

Data dictionary
 

To model the relations "Marriage" and " Child of ", the attribute Numind will be brought to play several roles :

1-If the individual is of male sex, that of the "husband" ( NumindMari) or that of the "father" ( NumindPère)
2-If the individual is of female sex, that of the "wife" ( NumindFemme) or that of the "mother" ( NumindMère)
3-Whatever is the sex of the individual, that of "child of " ( Numindenfant)

Diagram of data

For a further information on the different stages for building the diagram of data refer, in these pages, to the column DB Design.

1-Simple FDs

Numind -> Nom, Sexe, Dtnaiss, Nation, Dept
NumindEnfant -> NumindPère, NumindMère, Nom, Sexe, Dtnaiss, Nation, Dept

2-Not classified attributes and compound FDs

The attributes NumindMari, NumindFemme, Dtmariage were not classified in the preceding stage, they intervene in compound FD which is the following one:

NumindMari, NumindFemme -> Dtmariage, Nom, Sexe, Dtnaiss, Nation, Dept

3-Elimination of transitivities

Numind -> Nom, Sexe, Dtnaiss, Nation, Dept
NumindEnfant -> NumindPère, NumindMère
NumindMari, NumindFemme -> Dtmariage

4-Construction of the entities and associations

INDIVIDU(Numind, Nom, Sexe, Dtnaiss, Nation, Dept)
MARIAGE(NumindMari, NumindFemme, Dtmariage)
ENFANT(NumindEnfant, NumindPère, NumindMère)

5-Linking of entities and associations

6- Diagram of data

The "optimal" data structure is the following one (keys are in bold type):

In this diagram, relations between tables were not (to simplify reading) represented because they depend on treatments, and so on various roles that the attribute Numind is going to play, according to the questions that will be asked.

Queries

To visualize the contents of each table of the database, type :

SELECT * FROM Name_of_the_table;
within a frame containing the button "Execute".

Note to english readers : The value of a string attribute must be spell in french. To see the real content of string attributes, use the command above.

Question 1

Display, for each individual, how many brothers and sisters he(she) has. (Answer)

Question 2

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

Question 3

Display the average age of the women at the date of their marriage. (Answer)

Question 4

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

Question 5

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

Question 6

Display the rank (elder, second child)... of the individual whose number is equal to 11. (Answer)

©Marc Grange, February 2001 | Last update: January 29, 2009