Applications of Databases to Humanities and Social Sciences

Psychology

Problem

We are interested here in the modeling and treatment of the relations of affinity (related on attraction or aversion) which can be developed inside a group of people for which the age, the sex and the professional category are different. This classic problem could also be treated in sociology. It will be considered, thereafter, that the "group" is composed of 5 individuals whose respective affinities (noted from 0 to 4) are gathered in the following table :
 

The reading, in this table, is carried out in column : the affinity of A with respect to B is equal to 1 and that of B with respect to A is equal to 0.

Data dictionary
 

To model affinity between 2 individuals, the attribute "Individual" will be brought to play two roles :

1- That of the individual source of the affinity : attribute " IndividuSource "
2- That of the individual goal of the affinity : attribute " IndividuBut "

The relations of affinity between 2 individuals not being symmetrical it is essential here to specify the direction and the "strenght" of relation .

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

Individu -> Sexe, Age, Categorie, Lbsexe, Lbcategorie
Sexe -> Lbsexe
Categorie -> Lbcategorie

2-Not classified attributes and compound FDs

The attributes IndividuSource, IndividuBut and Affinite were not classified in the preceding stage, they intervene in a compound FD which is the following one :

IndividuSource, IndividuBut -> Affinite, Sexe, Age, Categorie, Lbsex, Lbcategorie

3-Elimination of transitivities

Individu -> Sexe, Age, Categorie
Sexe -> Lbsexe
Categorie -> Lbcategorie
IndividuSource, IndividuBut -> Affinite

4-Constructio of the entities and associations

PERSONNE(Individu, Sexe, Age, Categorie)
CATEGORIE(Categorie, Lbcategorie)
TYPE(Sexe, Lbsexe)
AFFINITE(IndividuSource, IndividuBut, Affinite)

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 the table "Affinite" and the table "Individu" are not represented because they depend on treatments, and so on various roles that the attribute "Individu" is going to play according to 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 the name, the age, the sex and the professional category of the "Leader" of the group : the one which collects the maximum of affinity. (Answer)

Question 2

Display, for the leader, the score that he obtained and the distribution by sex. This question can be useful, for example, to check the "weight" of the variable Sexe in the score obtained by the leader. (Answer)

Question 3

Give the list of the "groups" of 2 individuals of the same sex, most homogeneous within the meaning of the respective affinity of the individuals.(Answer)

Question 4

Give the list of the most homogeneous male groups of 3 individuals, within the meaning of the respective affinity of the individuals. (Answer)

Question 5

Display the list of the professional categories having an average score greater then 8. (Answer)

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