Aller au contenu

Microblogage et liste noire⚓︎

Sujet personnel, préparation Agro-Veto

Les réseaux sociaux essaient d'automatiser la traque de messages indésirables. Voici les propositions de la startup Ouaf qui a créé un réseau social de microblogage et voudrait éviter les messages illicites.

Une première dans la lutte contre les contenus indésirables consiste à répertorier des messages dans une base de données modélisée par le schéma ci-dessous (les clés primaires sont précédées d'un astérisque *)

classDiagram
Message <-- Contient_IP_Noire
Message <-- Contient_Mot_Noir
Message <-- Contient_Dom_Noir
Message --> Membre
Pays --> Langue
Message --> Langue
Membre --> Pays
Contient_IP_Noire --> Liste_Noire_IP
Contient_Mot_Noir --> Liste_Noire_Mots
Contient_Dom_Noir --> Liste_Noire_Domaines


class Membre{
Int  : id*
Text : email
Text : pseudo
Int  : nb_messages
Int  : nb_amis
Int  : id_pays
}


class Message{
Int       : *id
Timestamp : date
Text      : message
Int       : id_lang
Int       : id_membre
}

class Contient_IP_Noire{
Int : *id_mess
Int : *id_ip
}


class Contient_Dom_Noir{
Int : *id_dom
Int : *id_mess
}


class Liste_Noire_Domaines{
Int  : *id
Text : domaine
Int  : id_pays
}


class Contient_Mot_Noir{
Int : *id_mess
Int : *id_mot
}


class Liste_Noire_Mots{
Int  : *id
Text : mot
Int  : id_lang
}

class Liste_Noire_IP{
Int  : *id
Int  : ip
Int  : id_pays
}


class Pays{
Int : *id
Text : nom
Text : id_langue
}

class Langue{
Int  : *id
Text : nom
}
  1. Écrire une requête SQL donnant tous les identifiants des messages écrits en mars 2020.

  2. Écrire une requête SQL donnant les identifiants des messages écrits dans la langue du pays d'origine de leur auteur.

  3. Écrire une requête SQL donnant les identifiants et le nombre d'amis des membres ayant envoyé des messages contenant des mots indésirables avec une IP de la liste noire.

  4. Écrire une requête SQL donnant les pseudos des membres ayant envoyé au moins quarante-deux messages entrainant leur classement dans au moins une des trois listes noires.