Mocodo 4.2.2 loaded.

Connecting to 'pg'

TP : Implémentation de contraintes

Important : tout au long du TP, faites des tests de vos contraintes en insérant, supprimant, modifiant des tuples

Un club de danse souhaite se doter d'une base de données pour gérer ses intervenants dans les cours. Après analyse, on propose le schéma Entités/Associations ci-dessous. Les niveaux des cours sont à prendre parmi les valeurs 'Débutant', 'Avancé' ou 'Expert'.

1. Traduisez ce schéma en relationnel ; en ne tenant pas compte pour l'instant des contraintes $XT$ et $I$. Créez les relations obtenues, ainsi que les clés et clés étrangères qui en découlent.

• indication 1 : Puisque qu'un cours a exactement un responsable, cette association se traduira par une clé étrangère dans la relation "Cours", ne pouvant prendre la valeur NULL.
• indication 2 : Les spécialisations et entités faibles induisent une contrainte d'existence ; par exemple, supprimer un professeur de la base induit de le supprimer également en tant que salarié ou vacataire. De même supprimer un vacataire, induit de supprimer les contrats qui s'y rapportent. Faites en sorte que toutes ces suppressions en cascade soient automatiques (option : ON DELETE CASCADE des clés étrangères).

2. La contrainte $I$ sur le schéma traduit le fait que si un professeur est responsable d'un cours, alors il intervient dans ce cours. C'est une implication/inclusion : tout couple (professeur, cours) de l'association "Responsable" doit exister dans l'association "Intervient".
   1. Écrivez la dépendance d'inclusion qui traduit cette contrainte dans la base de données
   2. Cette dépendance d'inclusion peut-elle être implantée par une clé étrangère dans la relation 'cours' ?
   3. Créez cette contrainte dans la base de données.
   4. Créez un professeur "Jean Wiejacka" (sans se soucier encore qu'il soit vacataire ou salarié). Créez un cours de salsa débutant, pour lequel Jean Wiejacka est le responsable. Quel problème rencontrez vous ?

3. Une transaction est un ensemble de mises à jour considérées comme une seule opération, sans limite sur le nombre d'opérations dans une même transaction. Les SGBD relationnels garantissent que les contraintes sont satisfaites AVANT et APRES la transaction. Ce qui se passe PENDANT la transaction dépend des capacités des SGBD et des choix de l'opérateur. La vérification de certaines contraintes, comme les clés étrangères sous PostgreSQL, peut être différée à l'issue de la transaction.
   1. Modifiez, lorsque cela est nécessaire, les déclarations de clés étrangères afin de différer leur vérification en fin de transaction.
   2. Procédez maintenant à la création du cours de SALSA à l'aide d'une transaction contenant plusieurs insertions de tuples.

4. Le cahier des charges spécifie qu'un cours a obligatoirement (au moins) un intervenant (participation obligatoire à l'association "Intervient"). Peut-on considérer que cette contrainte est bien garantie dans notre base de données ?

5. Implanter la contrainte $X$ indiquée dans la spécialisation des professeurs, indiquant qu'un professeur ne peut pas être à la fois un salarié et un vacataire.

• Indication : Cette contrainte peut se programmer à l'aide de deux commandes "CHECK" dans la déclaration des relations "salaries" et "vacataires", par exemple sous la forme "Check (est_vacataire(num_prof) = false)" - où "est_vacataire(num_prof)" est une fonction qui retourne vrai si "num_prof" existe déjà dans vacataires, faux sinon.

Voici pour servir d'exemple une proposition de code de création d'une fonction qui renvoie VRAI ssi le numéro passé en argument est celui d'un vacataire.

drop function if exists est_vacataire;
CREATE function est_vacataire(un_num_prof integer) returns boolean
as $$
begin
 	perform num_vacataire  
 	from vacataires 
 	where num_vacataire = un_num_prof;
 
 	return (found);
end;
 $$ language plpgsql;

6. Implanter la contrainte $T$ de la spécialisation, qui impose que tous les professeurs ont la position de salarié ou de vacataire.
   1. Lors de la création d'un professeur : ajouter une contrainte "check" qui s'assure que ce professeur est bien un salarié ou un vacataire.
   2. Différez les contraintes de clés étrangères dans "salariés" et "vacataires", de façon à pouvoir momentanément créer un salarié ou vacataire qui n'est pas un professeur.
   3. Effectuez alors l'insertion d'un nouveau professeur - vacataire dans une transaction qui crée d'abord le vacataire pour le professeur.

7. Ce n'est pas tout à fait fini ! Il faut encore s'assurer que personne ne puisse supprimer une ligne des relations "vacataires" ou "salariés" car cela contredirait la contrainte $T$ (un professeur serait ni salarié, ni vacataire). Mais si on fait une contrainte trop forte qui interdit ces suppressions, on ne pourra plus faire les opérations suivantes : modifier un professeur en le passant de vacataire à salarié (ou l'inverse), ou même supprimer un professeur - car Postgre va tenter de supprimer le vacataire/salarié correspondant (en cascade). Voici comment résoudre ces problèmes.
   1. Créer une fonction $est\_prof$ qui teste si un numéro de professeur (fourni en entrée) existe parmi les professeurs.
   2. Faire un trigger (https://doc.postgresql.fr/16/sql-createtrigger.html) différable sur "salaries" qui interdit de supprimer un salarié si celui est un professeur qui existe encore et qu'il n'est pas dans "vacataires".
   3. Faire un trigger différable sur "vacataires" qui interdit de supprimer un vacataire si celui est un professeur qui existe encore et qu'il n'est pas dans "salaries".
   4. Vérifiez maintenant que vous pouvez 1) supprimer un professeur et 2) passer un professeur de salarié à vacataire en utilisant une transaction à deux étapes.

Aide : comment faire le trigger sur salariés :

-- Il faut d'abord faire une fonction qui sera utilisée par le trigger : elle ne doit pas prendre de paramètres, et retourner un objet de type 'trigger'.
drop function if exists check_prof_vacataire cascade; 
CREATE FUNCTION check_prof_vacataire() RETURNS trigger 
as $$
begin
	if  not est_prof(old.num_salarie) then return null; -- le prof a été supprimé (en cours de suppression)
	elsif est_vacataire(old.num_salarie) then return null; -- le salarié est bien dans vacataires.
	else raise exception 'professeur doit etre vacataire si plus salarie';
	end if;
end;
$$ LANGUAGE plpgsql;

create constraint trigger check_prof_vacataire
	after delete or update on salaries deferrable for each row execute function check_prof_vacataire();