Compte-rendu de TP : Contrainte gérée / déclencheur / tests unitaires réalisés

Soit le modèle relationnel des données suivant :

immeuble(id, adrNum, adrVoie, adrCodePostal, adrVille, fibreOptique, parkingPrivatif)
Clef primaire : id (autoincrémenté) 
Remarque : tous les attributs sont non null, fibreOptique et parkingPrivatif sont de type booléen 

appartement(idImmeuble, numAppart, description, loyer, superficie, terrasse, classeConso, chauffage, placeParking, prixParking)
Clef primaire : idImmeuble, numAppart
Clef étrangère : idImmeuble en référence à immeuble(id)
Remarque : attributs NULL par défaut, placeParking est de type booléen

piece(idImmeuble, idAppartement, numPiece, superficie, fonction)
Clef primaire : idImmeuble, idAppartement, numPiece
Clef étrangère : (idImmeuble, idAppartement) en référence à appartement(idImmeuble, numAppart)
Remarque : attributs NULL par défaut

1. créer le script de la base de données nommé votreNomBDParcImmo.sql
2. implanter ce script dans votre SGBD
3. vous avez ci-dessous le code d'un déclencheur : quel est sont rôle ? quelle contrainte est appliquée ?
4. programmer le déclencheur avec votre environnement de gestion de bases de données MySQL : expliquer chaque ligne.

   1. DROP TRIGGER IF EXISTS prixAppartBeforeInsert;
      DELIMITER $$ 
      CREATE TRIGGER prixAppartBeforeInsert BEFORE INSERT ON appartement FOR EACH ROW
      BEGIN
          IF NEW.placeParking = 0 THEN
              SET NEW.prixParking := NULL; -- affectation de variable
          END IF;
      END $$
      DELIMITER ;

5. Alternative empêchant l’insertion (aucune action, transaction interrompue) si il y a un prix et pas de place de parking, tester le déclencheur avec cette alternative :

   1.     IF NEW.placeParking = 0  AND NEW.prixParking IS NOT NULL THEN
              -- Interrompt la transaction en levant une erreur, i.e. empêche l’insertion (ou la modification)
              SIGNAL sqlstate '45000' SET message_text ='prix parking sans parking';
          END IF;

6. Tests unitaires : vérifier vos programmes avec le jeu d'essai suivant :
   1. Est-ce que l'appartement 1.1 a été créé ? Pourquoi ?
   2. Est-ce que l'appartement 1.2 a été créé ? Pourquoi ?
   3. Re-inséré-le si besoin.
   4. Modifier l'appartement 1.2 afin qu'il n'ai plus de parking : que se passe t'il ? Quelle solution proposez vous pour éviter de lever une erreur ? Appliquer cette solution et tester-la.

-- Insertion d'un immeuble avec parking privatif
INSERT INTO immeuble
     (Id, adrNum, adrVoie, adrCodePostal, adrVille, fibreOptique, parkingPrivatif) 
VALUES 
     (1, '7', 'Place de l\'étoile', '45000', 'ORLEANS', 1, 1);

-- Insertion de deux appartements, l’un sans place de parking, l’autre avec une place de parking, et
-- les deux ayant un prix de place de parking
INSERT INTO appartement
     (immeuble, num, description, loyer, superficie, terrasse, classeConso, chauffage, placeParking, prixParking) 
VALUES 
     (1, 1, 'Appartement 1.1', 825.00, 86, 1, 'C', 'E', 0, 75.00),
     (1, 2, 'Appartement 1.2', 750.00, 86, 1, 'C', 'E', 1, 80.00);

1. Adapter les triggers précédents afin de vérifier la contrainte suivante : un appartement ne peut pas avoir de place de parking si l’immeuble n’a pas de parking privatif ;
2. On souhaite que la contrainte suivante soit appliquée: la superficie totale d’un appartement doit être égale à la somme de la superficie de chacune de ses pièces. Pour ce faire, créer les triggers nécessaires.
   1. Combien en créez-vous ? sur quels événements ? sur quelle(s) tables() ?
   2. Comment les appelez-vous ?
3. Gérer par défaut la superficie de l’appartement à 0 à l’insertion d’un appartement ;

• définissez les tests unitaires à réaliser et réalisez-les

1. sujet
2. script base de données