Données de visites de pages Web

D'après 2022, Polynésie, J1, Ex. 3

SQL

L'énoncé de cet exercice peut utiliser les mots du langage SQL suivants :

SELECT, FROM, WHERE, JOIN ON, INSERT INTO, VALUES, UPDATE, SET, DELETE, COUNT, DISTINCT, AND, OR, AS, ORDER BY, ASC, DESC

Un site web recueille des données de navigation dans une base de données afin d'étudier les profils de ses visiteurs.
Chaque requête d'interrogation d'une page de ce site est enregistrée dans une première table dénommée Visites sous la forme d'un 5-uplet : (identifiant, adresse IP, date et heure de visite, nom de la page, navigateur).

Le chargement de la page index.html par 192.168.1.91 le 12 juillet 1998 à 22 h 48 aura par exemple été enregistré de la façon suivante :

(1534, "192.168.1.91", "1998-07-12 22:48:00", "index.html", "Internet explorer 4.1").

Un extrait de cette table vous est donné ci-dessous :

Identifiant	ip	dateheure	nompage	navigateur
...	...	...	...	...
1534	"192.168.1.91"	"1998-07-12 22:48:00"	"index.html"	"Internet explorer 4.1"
1535	"192.168.1.91"	"1998-07-12 22:49:05"	"exercices.html"	"Internet explorer 4.1"
1536	"192.168.1.151"	"1998-07-12 22:59:44"	"index.html"	"Netscape 6"
1537	"192.168.1.151"	"1998-07-12 23:00:00"	"espace_enseignant.html"	"Netscape 6"
1538	"192.168.1.91"	"1998-07-12 23:29:00"	"icorrection.html"	"Internet explorer 4.1"
...	...	...	...	...

1.a. Donner une commande d'interrogation en langage SQL permettant d'obtenir l'ensemble des 2-uplets (adresse IP, nom de la page) de cette table.

Réponse

🗂️ Requête SQL

SELECT ip, nompage FROM Visites;

1.b. Donner une commande en langage SQL permettant d'obtenir l'ensemble des adresses IP ayant interrogé le site, sans doublon.

Réponse

🗂️ Requête SQL

SELECT DISTINCT ip FROM Visites;

1.c. Donner une commande en langage SQL permettant d'obtenir la liste des noms des pages visitées par l'adresse IP 192.168.1.91

Réponse

🗂️ Requête SQL

SELECT nompage FROM Visites WHERE ip = '192.168.1.91';

Ce site web met en place, sur chacune de ses pages, un programme en JavaScript qui envoie au serveur, à intervalle régulier de 15 secondes, le temps en secondes (duree) de présence sur la page. Ces envois contiennent tous la valeur de identifiant correspondant au chargement initial de la page.
Par exemple, si le visiteur du 12 juillet 1998 est resté 65 secondes sur la page, celle-ci a envoyé au serveur les 4 doublets (1534, 15), (1534, 30), (1534, 45) et (1534, 60).

Ces données sont enregistrées dans une table nommée Pings.

En plus de l'inscription d'une ligne dans la table Visites, chaque chargement d'une nouvelle page provoque l'insertion d'une ligne dans la table Pings comprenant l'identifiant de ce chargement et une durée de 0.

Enfin, chaque ligne de la table Pings est unique, et ses deux colonnes contiennent toujours un identifiant et une duree.

L'attribut identifiant de la table Pings fait référence à l'attribut du même nom de la table Visites et les deux partagent les mêmes valeurs.

Un extrait de cette table vous est donné ci-dessous :

Identifiant	duree
...	...
1534	0
1534	15
1534	30
1534	45
1534	60
...	...
1536	0
1537	0
1537	15
...	...

2.a. De quelle table l'attribut identifiant est-il la clé primaire ?

Réponse

L'attribut identifiant est la clé primaire de la table Visites.

Remarque : dans la table Pings, le doublet (identifiant, duree) est clé primaire composite.

2.b. De quelle table l'attribut identifiant est-il une clé étrangère ?

Réponse

L'attribut identifiant est clé étrangère dans la table Pings.

2.c. On suppose que ces clés et règles (unicité, non nullité) ont été déclarées lors de la création des tables. Quelles vérifications sont automatiquement effectuées par le système de gestion de base de données ?

Réponse

Lors d'un enregistrement dans la table Pings, le système de gestion de base de données va vérifier :

• la contrainte de domaine : le doublet inséré doit être un doublet du type spécifié dans la définition de la table, par exemple (entier, entier)
• la contrainte d'unicité : on ne peut pas insérer deux fois le même doublet ;
• les contraintes de non nullité : ni le champ identifiant, ni le champ duree ne peuvent être NULL
• la contrainte d'intégrité référentielle : la valeur de l'attribut de la clé étrangère doit être inclus dans les valeurs de la clé primaire à laquelle il fait référence.

3. Le serveur reçoit le doublet (identifiant, duree) suivant : (1534, 105). Écrire la commande SQL d'insertion qui permet d'ajouter cet enregistrement à la table Pings.

Réponse

🗂️ Requête SQL

INSERT INTO Pings VALUES (1534, 105);

On envisage ensuite d'optimiser la table en se contentant d'une seule ligne par identifiant dans la table Pings : les valeurs de l'attribut duree devraient alors être mises à jour à chaque réception d'un nouveau doublet (identifiant, duree).

4.a. Écrire la requête de mise à jour permettant de fixer à 120 la valeur de l'attribut duree associée à l'identifiant 1534 dans la table Pings.

Réponse

🗂️ Requête SQL

UPDATE Pings SET duree = 120 WHERE identifiant = 1534;

Remarque : Dans le cadre de cette optimisation, l'attribut identifiant est clé primaire.

4.b. Expliquer pourquoi on ne peut pas être certain que les données envoyées par une page web, depuis le navigateur d'un client, via plusieurs requêtes formulées en JavaScript, arrivent au serveur dans l'ordre dans lequel elles ont été émises.

Réponse

Dans le protocole IP, les paquets sont routés indépendamment et peuvent donc éventuellement suivre des chemins différents s'il existe plusieurs itinéraires disponibles, ce qui peut impacter la durée d'acheminement. Une requête A envoyée avant une requête B peut donc arriver après cette dernière.

Ainsi, on ne peut pas être certain que les données envoyées par une page web, depuis le navigateur d'un client, via plusieurs requêtes formulées en JavaScript, arrivent au serveur dans l'ordre dans lequel elles ont été émises.

4.c. En déduire qu'il est préférable d'utiliser une requête d'insertion plutôt qu'une requête de mise à jour pour ajouter des données à la table Pings.

Réponse

Il est préférable d'utiliser une requête d'insertion plutôt qu'une requête de mise à jour pour ajouter des données à la table Pings afin de s'assurer de conserver dans la table Pings un enregistrement représentatif du temps passé par un utilisateur sur une page donnée (dans ce cas, lors d'une requête sur la table Pings, il faudrait alors rechercher la valeur maximale relative à un identifiant de connexion donné).

Remarque : il est en réalité techniquement possible d'empêcher de stocker une valeur qui diminuerait la durée, mais cela est hors programme.

5. Écrire une requête SQL utilisant le mot-clé JOIN et une clause WHERE, permettant de trouver les noms de toutes les pages qui ont été consultées plus d'une minute par au moins un utilisateur.

Réponse

🗂️ Requête SQL

SELECT DISTINCT Visites.nompage FROM Visites JOIN Pings ON Visites.identifiant = Pings.identifiant WHERE Pings.duree > 60;

Remarque 1 : le "par au moins un utilisateur" me fait utiliser le DISTINCT parce qu'il y aura des doublons si plusieurs utilisateurs ont consultée la même page assez longtemps, indépendamment de l'optimisation ou non.

Remarque 2 : dans le cas où la clé est composite, il y aura en plus plusieurs fois la même page pour une même consultation si la durée est >= 75. Ce n'est pas important pour lister les pages, mais ça l'est si on veut les compter ensuite (y compris si on fait du GROUP BY).

Remarque 3 : dans le cas où l'optimisation a été faite, ces doublons de même consultation n'existeront pas, mais le cas de multiples utilisateurs consultant la même page reste. La requête est inchangée.