Problem
Our focus here is on modeling and processing affinity relationships that may develop within a group of individuals. This classic problem can be addressed in sociology as well. Next, we consider that the "group" consists of 5 people whose respective affinities (rated 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.
Display the name, age, gender and professional category of the "Leader" of the group: the one that has the maximum affinity. (Answer)
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)
List the "groups" of 2 individuals of the same gender, the most homogeneous in terms of the respective affinity of the individuals. (Answer)
List the most homogenous male groups of 3 individuals, according to the respective affinity of the individuals. (Answer)
Display the list of professional categories with an average score greater than 8. (Answer)