Examen du mardi 15 mars 2022
Tout document et équipement autorisé.
La communication entre humains est interdite.
Rendre le modèle de réponses 2022-exam-reponses.md complété à la fin du temps imparti par email à romuald.thion@unc.nc.
Nommer votre fichier avec votre nom de famille.
On travaille sur le schéma UNIV et le jeu de données en annexe.
Les réponses attendues aux questions ouvertes sont courtes et précises.
Les extraits de code doivent s’exécuter sans aucune erreur.
1. Concurrence d’accès (/8)
On considère la requête suivante Q exécutée dans une transaction A ouverte mais pas encore fermée:
-- début de la transaction A
BEGIN ISOLATION LEVEL READ COMMITTED;
-- requête Q
SELECT
nomAgent,
prenomAgent,
coalesce(sum(surfaceSalle), 0) AS total
FROM
agent a
LEFT JOIN salle s ON a.numSecu = s.agentAffecte
GROUP BY
a.numSecu
ORDER BY
a.numSecu;
-- A pas encore fermée
-- END;
Q1.1 (/1)
Donnner le script qui, dans une autre session, ouvre une transaction appellée B et affecte l’agent 1003 à la salle 1 de l’étage 0 du bâtiment F sans fermer la session.
Q1.2 (/1)
La transaction A en cours voit-elle la modification de la transaction B en cours ? Justifier.
Q1.3 (/1)
La transaction B exécute COMMIT mais A reste encore ouverte. La transaction A en cours voit-elle la modification ? Justifier.
Q1.4 (/2)
On reprend le même scénario mais cette fois-ci A s’exécute avec le niveau d’isolation REPEATABLE READ. Les réponses aux question Q1.3 et Q1.4 changent-elles ? Justifier.
Q1.5 (/1)
On veut utiliser le résultat de la requête Q pour calculer des primes mesnuelles aux agents, mettons 10 CFP pour chaque mètre carré. Donner un script SQL qui crée une table prime pour enregistrer chaque mois le montant des primes.
Q1.6 (/1)
On suppose qu’on remplit la table précédente de 25 de chaque mois à 13:37. Donner le script qui crée les INSERT correspodants en reprennant la requête Q.
Q1.7 (/1)
Quels sont les impacts des niveaux d’isolation sur le calcul des primes ?
2. Algorithmes de jointure (/6)
On donne l’implémentation d’un étudiant, appellons le Max, de l’algorithme de jointure par hash en Python.
def join_hash_max(table1, attr1, table2, attr2):
"""L'algorithme de jointure hash_join codé par un étudiant"""
hash_table = defaultdict(list)
res = []
for idx, tup in enumerate(table1):
hash_table[tup[attr1]].append(idx)
for tup in table2:
for key, vals in hash_table.items():
if key == tup[attr2]:
for idx in vals:
res.append(table1[idx] + tup)
return res
On considèrera l’algorithme hash anti-join, qui permet de calculer la différence (ensembliste) entre deux relations. Cet algorithme prend en paramètres :
- une table
table1etattr1un de ses attributs - une table
table2etattr2un de ses attributs
L’algorithme hash anti-join renvoie tous les tuples de table1 dont les attributs attr1 ne se trouvent pas dans l’ensemble des valeurs prises par attr2 dans table2.
Par exemple, sur le schéma UNIV, on peut déterminer les bâtiments qui n’ont pas d’étages, à savoir sur le jeu d’essai, le bâtiment Principal à Baco édifié en 2018.
Q2.1 (/2)
Quel est le problème de performance de l’implémentation de Max du hash join ? Expliquer la correction à apporter.
Q2.2 (/1)
Donner une requête SQL qui fait le même calcul que le hash anti-join.
Q2.3 (/3)
Reprendre le code (corrigé) de l’algorithme hash join pour compléter son implémentation Python suivante.
def anti_join_hash(table1, attr1, table2, attr2):
"""L'algorithme de hash anti-join à compléter en s'inspirant de join_hash.
Renvoie les tuples de table1 tels que table1[attr1] ne se trouve PAS dans table2[attr2]"""
pass
3. Plans d’exécution et performance (/10)
Q3.1 (/1)
On considère une requête sur la base UNIV et son jeu d’essai dont on donne le plan suivant :
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..2.13 rows=1 width=30)
Join Filter: (batiment.nombat = niveau.nombat)
-> Seq Scan on batiment (cost=0.00..1.04 rows=1 width=21)
Filter: ((anneebat >= 1980) AND (anneebat <= 1989))
-> Seq Scan on niveau (cost=0.00..1.04 rows=4 width=13)
Donner une requête SQL qui peut produire ce plan.
Q3.2 (/2)
Expliquer pourquoi ou pourrait identifier un plan utilisant un Index Scan using niveau_pkey on niveau et sans Join Filter pour la requête précédente.
Q3.3 (/2)
On vide la base de données puis on génère un jeu d’essai avec le script suivant (NB si c’est un peu long sur votre machine, réduisez un des facteurs pour générer moins de bâtiments) :
DELETE FROM salle;
DELETE FROM agent;
DELETE FROM niveau;
DELETE FROM batiment;
INSERT INTO batiment (
SELECT 'bat#' || a.i::text || '_' || g.i::text, a.i, 'addr#' || g.i::text
FROM generate_series(1981, 2022) AS a(i) CROSS JOIN generate_series(1,1000) AS g(i)
);
INSERT INTO niveau (
SELECT nomBat, g.i, round(1000*random()), random() > 0.8
FROM generate_series(1,10) AS g(i) CROSS JOIN (SELECT nomBat FROM batiment) AS b
);
VACUUM ANALYZE batiment, niveau;
On reprend la requête dont on avait le plan en question Q3.1 et on l’exécute sur le nouveau jeu de données générées. Donner le nouveau plan obtenu et expliquer les principales différences avec le plan de la question Q3.1.
Q3.4 (/2)
Ajouter un index sur batiment(anneeBat).
Donner la commande SQL et donner le nouveau plan obtenu et expliquer les différences avec le plan de la question Q3.4.
Q3.5 (/3)
Il y a plusieurs façons de déterminer en SQL les bâtiments qui n’ont pas d’étages (question Q2.2). Sur le jeu d’essai volumineux de la question Q3.3, donner une des écritures SQL avec un plan efficace (temps d’exécution inférieu à la seconde).
4. Production de JSON (/6)
On souhaite produire un rapport qui indique pour chaque agent le nombre de salles qu’il doit nettoyer à chaque adresse.
On reprend pour cela le jeu de donnée initial de la base UNIV.
Donne la requête SQL qui fait le calcul demandé.
SELECT
agent.*,
adresseBat,
count(numSalle) AS nbSalles
FROM
batiment
JOIN niveau USING (nombat)
JOIN salle USING (nombat, numNiveau)
RIGHT OUTER JOIN agent ON agentAffecte = numSecu
GROUP BY
numSecu, adresseBat
ORDER BY
numSecu;
Le résultat de la requête est comme suit :
numsecu | nomagent | prenomagent | adressebat | nbsalles
---------+------------+-------------+---------------------+----------
1001 | DE SERVICE | Agent 1 | Nouville | 2
1002 | DE SERVICE | Agent 2 | Nouville | 1
1002 | DE SERVICE | Agent 2 | Nouville, plus loin | 1
1003 | DE SERVICE | Agent 3 | Ø | 0
On rappelle quelques fonctions JSON :
to_jsonb ( anyelement ) → jsonbconverts any SQL value to json or jsonb.jsonb_build_object ( VARIADIC "any" ) → jsonbbuilds a JSON object out of a variadic argument list.jsonb_agg ( anyelement ) → jsonbcollects all the input values, including nulls, into a JSON array.jsonb_pretty ( jsonb ) → textconverts the given JSON value to pretty-printed, indented text.
Q4.1 (/3)
Transformer la requête précédente (avec par exemple WITH q AS () ... pour produire un résultat au format JSON, avec une ligne par agent et un tableau de la liste des adresses où intervenir, les adresse répétées si besoin, comme suit :
agent
-------------------------------
{ +
"nom": "DE SERVICE", +
"prenom": "Agent 1", +
"numSecu": "1001", +
"adresses": [ +
"Nouville", +
"Nouville" +
] +
}
{ +
"nom": "DE SERVICE", +
"prenom": "Agent 2", +
"numSecu": "1002", +
"adresses": [ +
"Nouville", +
"Nouville, plus loin"+
] +
}
{ +
"nom": "DE SERVICE", +
"prenom": "Agent 3", +
"numSecu": "1003", +
"adresses": [ +
] +
}
(3 rows)
Q4.2 (/3)
Même question mais cette fois-ci le tableau contient une liste de couples (adresseBat, nbSalles) pour chaque agent, comme suit :
agent
-----------------------------------------------
{ +
"nom": "DE SERVICE", +
"prenom": "Agent 1", +
"numSecu": "1001", +
"adresses": [ +
{ +
"adresse": "Nouville", +
"nbSalles": 2 +
} +
] +
}
{ +
"nom": "DE SERVICE", +
"prenom": "Agent 2", +
"numSecu": "1002", +
"adresses": [ +
{ +
"adresse": "Nouville", +
"nbSalles": 1 +
}, +
{ +
"adresse": "Nouville, plus loin",+
"nbSalles": 1 +
} +
] +
}
{ +
"nom": "DE SERVICE", +
"prenom": "Agent 3", +
"numSecu": "1003", +
"adresses": [ +
] +
}
Annexe
Schéma de la base UNIV
DROP TABLE IF EXISTS salle;
DROP TABLE IF EXISTS agent;
DROP TABLE IF EXISTS niveau;
DROP TABLE IF EXISTS batiment;
CREATE TABLE batiment (
nomBat text PRIMARY KEY,
anneeBat int NOT NULL,
adresseBat text NOT NULL
);
CREATE TABLE niveau (
PRIMARY KEY (nomBat, numNiveau),
nomBat text REFERENCES batiment,
numNiveau int,
surfaceNiveau int NOT NULL, --surface TOTALE de l'étage
accesHand boolean NOT NULL DEFAULT FALSE
);
CREATE TABLE agent (
numSecu text PRIMARY KEY
nomAgent text NOT NULL,
prenomAgent text NOT NULL
);
CREATE TABLE salle (
PRIMARY KEY (nomBat, numNiveau, numSalle),
FOREIGN KEY (nomBat, numNiveau) REFERENCES niveau,
nomBat text,
numNiveau int,
numSalle int,
surfaceSalle int NOT NULL, --surface UTILE de la salle
typeSalle text,
agentAffecte text REFERENCES agent (numSecu) DEFAULT NULL
);
Jeu de données de la base UNIV
DELETE FROM salle;
DELETE FROM agent;
DELETE FROM niveau;
DELETE FROM batiment;
INSERT INTO batiment
VALUES ('F', 1986, 'Nouville'),
('Sigma', 2018, 'Nouville, plus loin'),
('Principal', 2018, 'Baco');
INSERT INTO niveau
VALUES ('F', 0, 100, TRUE),
('F', 1, 60, FALSE);
INSERT INTO niveau
VALUES ('Sigma', 1, 2000, TRUE),
('Sigma', 2, 3000, TRUE);
INSERT INTO agent
VALUES (1001, 'DE SERVICE', 'Agent 1'),
(1002, 'DE SERVICE', 'Agent 2'),
(1003, 'DE SERVICE', 'Agent 3');
INSERT INTO salle
VALUES ('F', 0, 1, 40, 'TD', NULL),
('F', 0, 2, 50, 'TP', 1001),
('F', 0, 3, 50, 'TP', 1001),
('F', 1, 1, 40, 'Bureau', 1002),
('Sigma', 1, 1, 200, 'Fablab', 1002);