EFM 2014 : Examen de Fin de Module Ofppt Année 2014 -2015 | Exam-Lib
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
Dismiss Notice
Welcome to our Education website, plz like our page facebook to support us. Thank You and wish you good navigation

EFM 2014 : Examen de Fin de Module Ofppt Année 2014 -2015

abdelouafiMar 19, 2018

    1. abdelouafi

      abdelouafi Administrator Staff Member

      Messages:
      636
      Likes Received:
      14
      Trophy Points:
      18
      Joined
      Sep 13, 2016
      upload_2018-3-19_19-37-21.png
      I) : Partie Théorique (2/40 points)

      1) donnez la syntaxe de la requête de sélection avec les différentes clauses. (2pt)

      II) : Partie Pratique (38/40 points)

      Dans l’objectif de créer une application pour la gestion des projets au sein d’une entreprise de développement informatique on vous propose le schéma relationnel suivant :

      Service (Num_serv, Nom_serv, Date_creation)

      Employe (Matricule, Nom, Prenom, DateNaissance, Adresse, Salaire,Grade, Num_serv#)

      Projet (Num_prj, Nom_prj, Lieu, nbr_limite_taches,Num_serv#)

      Tache (Num_tach, Nom_tache, date_debut, date_fin, cout, Num_prj#)

      Travaille (Matricule#,Num_tache#, Nombre_heure)

      un projet est réalisé en plusieurs étapes (Tache) selon le processus de conception choisi ; une tache est réalisée par un ou plusieurs employés chaqu’un avec une masse horaire définit (nombre_heure).

      Remarque :
      • souliginé : Clé primaire
      • # : clé étrangère
      Avec les contraintes suivantes :

      • La contrainte CK_Employe_dateNaissance : l’âge de l’employé doit être supérieur à 18.
      • La contrainte CK_Tache_duree : une tache a une durée minimale de 3 jours (Durée = Date_fin – Date_debut)
      • La contraint CK_Tache_cout : le côut miniaml d’une tache est de 1000DH par jour. (cout >= (Date_fin – Date_debut)j x1000 )

      N.B :
      • Les clés primaires sont incrémentées automatiquement sauf le numéro de la tache.
      • Le nom du projet doit être codé en français et sensible à la casse.

      Questions :

      A. Créer la base de données (10pts):

      1. donner le script permettant de créer la base de données gestion_projet avec le schéma relationnel précédant. (9pts)

      2. ajouter le champ calculé âge à la table Employé. (1pt)

      B. Créer les requêtes de sélection (19pts):

      1. afficher les employés dont le nom commence avec « El » et ne se termine pas par une lettre entre a et f, trier la liste par date de naissance.

      2. afficher les noms des taches (en majuscule) qui prendrons fin ce mois ci.

      3. compter le nombre de grades différents de l’entreprise.

      4. afficher les employés qu’ont participé à un projet affecter à un service différent où il travaille.

      5. les projets avec une tache de durée inférieure à 30jours et une autre supérieure à 60jours

      Durée d’une tache = Date de Fin – date de début

      6. afficher la masse horaire travaillée cette année (travaille débuter et terminer cette année) par projet.

      Masse horaire = somme (nombre_heure)

      7. afficher le matricule et le nom des employés qui ont participé à la réalisation de plusieurs projets.

      8. afficher le matricule, le nom, la date d’anniversaire et l’adresse des employés qui vont fêter leur anniversaire la semaine prochaine.

      9. afficher le(s) projet(s) qui se composent du plus grand nombre de taches.

      10. afficher la durée de réalisation par projet :

      La durée de réalisation d’un projet = la date de fin de la dernière tache de ce projet – la date de début de la première tache du projet (utiliser Min et Max).

      C. Créer les requêtes de mise à jour (3pts):

      1. modifier les salaires des employés selon la règle suivante (1pt5):
      • sans modification pour les employés âgés de moins de 58 ans,
      • augmentation de 0.5% pour les employés âgés entre 58 et 60 ans,
      • augmentation de 5% pour les employés âgés de plus que 60 ans.
      2. supprimer les taches non réalisées (une tache non réalisée est une tache dont la date de fin est dépassée sans qu’elle contienne un travail) (1pt5).

      D. Gérer la sécurité de la base de données (6pts):

      1. Créer les deux profils de connexion suivants (1pt) :
      • profil SQL server : CnxGestionnaire
      • profil Windows : ChefProjet-PC\ChefProjet
      2. Créer un utilisateur au niveau de la base de données gestion_projet pour chaque profil (1pt) :
      • Gestionnaire
      • ChefProjet
      3. attribuer les autorisations suivantes aux utilisateurs concernés (4pts) :
      • Gestionnaire : Le droit de mise à jour (insertion, modification et suppression) de toutes les tables sauf la table « employé ».
      • ChefProjet : Le droit de suppression de toutes les tables sauf la table « Employé »
      Le droit de modification du champ « adresse » de la table « Employé » (coupler avec un vue)​
       
      Last edited: Mar 19, 2018
      Loading...
      --A Création de la base de données :

      create database gestion_projets
      go
      use gestion_projets
      go

      create table [Service](
      Num_serv int primary key identity,
      Nom_serv varchar(25),
      Date_creation datetime
      )
      go

      create table Employe(
      matricule int primary key identity,
      nom varchar(25),
      prenom varchar(25),
      Datenaiss datetime,
      Adresse varchar(100),
      Salaire money,
      Grade varchar(25),
      Num_serv int
      )
      go

      Create table Projet(
      Num_prj int primary key identity,
      Nom_prj nvarchar(25) collate french_BIN,
      lieu varchar(25),
      nbr_limite_taches int,
      Num_serv int
      )
      go

      create table Tache(
      Num_tache int primary key,
      Nom_tache varchar(35),
      Date_debut datetime,
      Date_fin datetime,
      Cout money,
      Num_prj int
      )
      go

      create table Travaille(
      Matricule int not null,
      Num_tache int not null,
      Nombre_heure int,
      constraint pk_travaille primary key(Matricule,Num_tache)
      )
      go

      Alter table Employe ADD
      Constraint CK_Employe_dateNaissance Check ( DateDiff(year,Datenaiss,getdate())>=18),
      Constraint fk_employe_serv foreign key (Num_serv) references [Service](Num_serv)
      go

      Alter table Tache ADD
      Constraint CK_Tache_duree Check (Datediff(day,date_debut,date_fin)>=3),
      Constraint CK_Tache_cout Check (cout >=(Datediff(day,date_debut,date_fin)*1000)),
      Constraint fk_tache_prj foreign key(Num_prj) references Projet(Num_prj)
      go

      Alter Table Projet ADD
      Constraint fk_projet_serv foreign key (Num_serv) references [Service](Num_serv)
      Go
      Alter Table Travaille ADD
      Constraint fk_travaille_emp foreign key(Matricule) references Employe(Matricule),
      Constraint fk_travaille_tch foreign key(Num_tache) references Tache(Num_tache)
      go

      --A-2
      Alter Table Employe ADD
      Age As datediff(year,datenaiss,getdate())
      Go

      --B

      --1
      --afficher les employés dont le nom commence avec « El »
      --et ne se termine pas par une lettre entre a et f,
      --trier la liste par date de naissance. (1pt)

      Select * from Employe Where nom like 'EL%[^a-f]' order by Datenaiss

      --2. afficher les noms des taches (en majuscule) qui prendrons
      --fin ce mois ci. (2pts)

      Select UPPER(nom_tache) as 'nom maj' from Tache where MONTH(date_fin)=MONTH(getdate())

      --3. compter le nombre de grades différents de l’entreprise. (2pts)
      select count(distinct grade) as 'nbr de grade' from Employe

      --4. afficher les employés qu’ont participé à un projet
      --affecter à un service différent où il travaille. (2pts)
      select distinct e.* from Employe e inner join Travaille tr on e.matricule=tr.Matricule
      inner join Tache ta on tr.Num_tache=ta.Num_tache
      inner join Projet p on p.Num_prj=ta.Num_prj
      where e.Num_serv<>p.Num_serv

      --5. les projets avec une tache de durée inférieure à 30jours
      --et une autre supérieure à 60jours (2pts)
      --Durée d’une tache = Date de Fin – date de début
      select p.* from Projet p inner join Tache t on p.Num_prj=t.Num_prj
      where DATEDIFF(day,date_debut,date_fin)<30
      intersect
      select p.* from Projet p inner join Tache t on p.Num_prj=t.Num_prj
      where DATEDIFF(day,date_debut,date_fin)>60


      --6. afficher la masse horaire travaillée cette année
      --(travaille débuter et terminer cette année) par projet
      --Masse horaire = somme (nombre_heure) (2pts)
      select Num_prj,SUM(nombre_heure) as 'Masse horaire'
      from Travaille tr inner join Tache ta on tr.Num_tache=ta.Num_tache
      where YEAR(date_debut)=YEAR(getdate()) and YEAR(date_fin)=YEAR(GETDATE())
      group by Num_prj


      --7. afficher le matricule et le nom des employés qui ont participé à
      --la réalisation de plusieurs projets. (2pts)

      select e.matricule,nom
      from Employe e inner join Travaille tr on e.matricule=tr.Matricule
      inner join Tache ta on tr.Num_tache=ta.Nom_tache
      group by e.Matricule,nom
      having COUNT(distinct num_prj) > 1

      --8. afficher le matricule, le nom, la date d’anniversaire et l’adresse
      --des employés qui vont fêter leur anniversaire la semaine prochaine. (2pts)

      select matricule,nom,Adresse, DATEADD(year,Age+1,datenaiss) as 'date d’anniversaire'
      from Employe
      where DATEADD(year,Age+1,datenaiss) between
      DATEADD(day,7-(datepart(weekday,getdate())-2),GETDATE())
      and DATEADD(day,13-datepart(weekday,getdate())+2,GETDATE())
      --9. afficher le(s) projet(s) qui se composent du plus
      --grand nombre de taches. (2pts)

      select num_prj,COUNT(num_tache) as 'nbr de tache'
      from Tache
      group by Num_prj
      having COUNT(num_tache)>=all (select COUNT(num_tache)
      from Tache
      group by Num_prj)

      --10. afficher la durée de réalisation par projet (2pts):
      --La durée de réalisation d’un projet = la date de fin de
      --la dernière tache de ce projet – la date de début de la première
      --tache du projet (utiliser Min et Max).

      select num_prj,DATEDIFF(day,min(date_debut),max(date_fin)) as 'durée de réalisation'
      from Tache
      group by Num_prj

      --C
      --1
      update Employe set Salaire = case
      when datediff(year, DateNaissance, getdate())<58 then Salaire
      when datediff(year, DateNaissance, getdate()) between 58 and 60 then Salaire+Salaire*0.05
      else Salaire+Salaire*0.5
      end

      --2
      delete from Tache where GETDATE()>date_fin

      --D
      --1
      create login [CnxGestionnaire] from windows with default_database = gestion_projet
      go
      create login [ChefProjet_PC\ChefProjet] with password='123456'
      use gestion_projet
      go

      create user Gestionnaire for login [ChefProjet_PC\ChefProjet]
      create user ChefProjets for login [ChefProjet_PC\ChefProjet]


      create user Gestionnaire for login [CnxGestionnaire]
      create user ChefProjets for login [CnxGestionnaire]

      deny update, insert, delete on object :: Employe to Gestionnaire
      grant update, insert, delete on object :: [Service] to Gestionnaire
      grant update, insert, delete on object :: Projet to Gestionnaire
      grant update, insert, delete on object :: Tache to Gestionnaire
      grant update, insert, delete on object :: Travaille to Gestionnaire

      deny delete on object :: Employe to ChefProjet
      go

      create view Vaddres as(select addresse from Employe)

      grant update on object:: Vaddres to ChefProjet

Share This Page

Share