Psychology

Problem

Here we focus on the modeling and treatment of affinity relationships that may develop within a group of individuals. The classical problem can also be discussed in sociology. Then, we consider that the "group" is composed of 5 individuals whose respective affinities (scored from 0 to 4) are grouped in the following table:

Affinity A B C D E
A   0 3 2 4
B 1   1 3 0
C 4 0   1 3
D 3 2 2   3
E 0 1 4 3  

Therefore, in this table, the affinity of A to B is equal to 1 and that of B to A is equal to 0.

Data dictionary
 

Mnemonic Wording
Affinite Affinity (0-4) of the individual source and the individual goal.
Age Age of person (entire years)
Categorie Individual's professional category code.
Individu Name of the individual
Lbcategorie Wording of the professional category:
Worker, Employee, Supervisor, Manager, Senior manager
Lbsexe Gender of the person: Male or Female.

In order to model the affinity between two individuals, the "Individual" attribute will have two roles:

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

Since the affinity relationships between two individuals are not symmetrical, it is essential here to specify the direction and the related "force".

Data Diagram

For more information on the different steps of the data diagram construction refer, in these pages, to the column. DB Design.

1-Simple FDs

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

2-Unclassified attributes and compound FD.

The SourceIndivide, ButIndivide and Affinite attributes were not classified in the previous step, they occur in a compound FD which is:

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

3-Eliminating transitivity.

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

4-Building of entities and associations.

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

5-Connecting entities and associations.

Source entity Goal entity Relation on attribute Type of relation
CATEGORIE PERSONNE Categorie 1-m
TYPE PERSONNE Sexe 1-m
PERSONNE AFFINITE Individu / IndividuSource 1-m
PERSONNE AFFINITE Individu / IndividuBut 1-m

6-Data Diagram

The "optimal" data structure is as follows (keys are highlighted):

In this diagram, relations between the table "Affinite" and the table "Individu" are not represented because they depend on treatments, and thus on various roles that the attribute "Individu" is going to play according to the questions that will be asked.

Queries

To view the contents of 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 has to be spelled in French. To view the true content of string attributes, use the command above.

Question 1

Display the name, age, gender and professional category of the "Leader" of the group: the one that has the maximum affinity. (Answer)

Question 2

Show the leader's score and gender distribution. This question can be useful, for example, to check the "weight" of the variable sex in the score obtained by the leader. (Answer)

Question 3

List the "groups" of 2 individuals of the same gender, the most homogeneous in terms of the respective affinity of the individuals. (Answer)

Question 4

List the most homogenous male groups of 3 individuals, according to the respective affinity of the individuals. (Answer)

Question 5

Display the list of professional categories with an average score greater than 8. (Answer)