Révisions
Une entreprise souhaite gérer les informations suivantes :
1. Les employés, leurs informations personnelles, leur salaire, et leur département.
2. Les départements, contenant le nom et le responsable de chaque département.
3. Les absences, enregistrées avec la durée et le motif pour chaque employé.
Vous devez construire et gérer cette base de données en SQL et PL/SQL.
Partie 1 :
1. Créer les tables suivantes :
o DEPARTEMENTS:
ID_DEPT (NUMBER, clé primaire).
NOM_DEPT (VARCHAR2(50)).
RESPONSABLE (VARCHAR2(50)).
o EMPLOYES:
ID_EMPLOYE (NUMBER, clé primaire).
NOM (VARCHAR2(50)).
PRENOM (VARCHAR2(50)).
SALAIRE (NUMBER).
ID_DEPT (clé étrangère vers DEPARTEMENTS).
o ABSENCES:
ID_ABSENCE (NUMBER, clé primaire).
ID_EMPLOYE (clé étrangère vers EMPLOYES).
DATE_ABSENCE (DATE).
DUREE (NUMBER).
MOTIF (VARCHAR2(100)).
2. Créer une vue :
o Vue VUE_EMPLOYES_DEPT pour afficher l'ID, le nom complet de l'employé,
son salaire, et le nom de son département.
3. Insérer des données :
o Insérez au moins 3 départements et 5 employés.
o Ajoutez quelques absences pour tester.
4. Mettre à jour des données :
o Changez le salaire d'un employé.
5. Supprimer des données :
o Supprimez un employé et toutes ses absences associées.
6. Créer un index :
o Sur la colonne NOM dans la table EMPLOYES.
7. Créer une séquence :
o Séquence SEQ_EMPLOYES pour générer automatiquement des IDs pour la
table EMPLOYES.
Partie 2 :
1. Bloc :
o Écrivez un bloc PL/SQL pour vérifier si un employé existe en fonction de son
ID. Si l'employé n'existe pas, affichez un message d'erreur personnalisé.
2. Fonction :
o Créez une fonction CALCULER_SALAIRE_NET :
Prend un ID_EMPLOYE et retourne le salaire net après déduction pour
2 jours d'absence (utilisez la table ABSENCES).
3. Procédure :
o Créez une procédure AUGMENTER_SALAIRE :
Prend en entrée un ID_EMPLOYE et un pourcentage.
Met à jour le salaire de l'employé avec cette augmentation.
4. Curseur :
o Écrivez un curseur pour lister tous les employés et leur département.
5. Déclencheur (Trigger) :
o Créez un déclencheur TRG_AJOUT_ABSENCE qui se déclenche après
l'insertion dans ABSENCES :
Insérez automatiquement un log dans une table LOG_ABSENCES
contenant ID_ABSENCE, ID_EMPLOYE, et la date d'insertion.
Tests et Validation :
1. Testez les tables :
o Insérez des employés avec la séquence.
o Supprimez un employé et vérifiez que ses absences sont également
supprimées.
2. Testez les vues :
o Interrogez VUE_EMPLOYES_DEPT pour vérifier les résultats.
3. Testez les blocs PL/SQL :
o Appelez la fonction CALCULER_SALAIRE_NET pour un employé avec des
absences.
o Appelez la procédure AUGMENTER_SALAIRE pour augmenter le salaire d’un
employé.
4. Testez le curseur :
o Parcourez tous les employés et affichez leurs informations.
5. Testez le déclencheur :
o Ajoutez une absence et vérifiez que le log est créé dans LOG_ABSENCES.