Le 07/01/2019
Devoir Surveillé : Modélisation Merise -Langage SQL
Questions Cours :
1. Définir une procédure fonctionnelle?
2. Définir et expliquer les différents types des contraintes d'intégrité
3. A quoi sert le modèle CVO
4- Donner les différentes commandes de langage LDD.
Exercice SQL
Pour assurer le suivi des projets fournis à ces clients, InfoDev utilise actuellement une application
qui utilise une base de données (présentation en intension) suivante :
Client (numClient, nomClient, rueClient, CPClient, villeClient, telClient, faxClient)
Pole (codePole, libellePole)
Projet (code, codePole#, numClient#, nomProjet, descriptionProjet, origineProjet,
dateReponseProjet, dateDebutProjet, dateFinProjet, etatProjet, commentaireProjet)
Activité (numActivité, codeProjet#, nomActivité, dateDebutPrevActivité, dateFinPrevActivité,
dateDebutReelleActivité, dateFinReelleActivité, chargeJourHommePrevActivité,
chargeJourHommeRealActivité)
Intervenant (numIntervenant, codePole#, nomIntervenant, prenomIntervenant,
dateEmbaucheIntervenant, dateFinContratIntervenant, dateNaissIntervenant,
rueIntervenant, CPIntervenant, villeIntervenant, telIntervenant,
coutHoraireIntervenant)
Participer (numIntervenant#, numActivite#, dateEntree, dateSortie)
Donner l’ordre SQL complet permettant la résolution des requêtes suivantes :
1. liste des noms de projets concernant le pôle [Link]
réseau
2. Noms des projets d'une durée supérieure à 10 [Link]
jours classée par nom des projets décroissante.
NB. durée = dateDebutProjet-dateFinProjet
3. Noms des projets ayant obtenu une réponse au [Link]
mois de Février 2018.
4. Liste des intervenants (nom et prénom) sur nom,prenom
l'activité Maquettage
5. Liste des projets (code et nom) dont le nombre [Link], [Link], nbrActivite
d'activités est supérieur à 5.
6. Liste des intervenants (numéro, nom, prénom) [Link], [Link],
ayant participé au projet P050309. [Link]
7. Durée moyenne d’un projet. dureeMoyenneProjet
8. Nombre de projets du pôle réseau. Nbr_Projet
9. Lister les différentes projets sur lesquels [Link], [Link],
intervient l'employé Alaoui Ahmed (Nom du [Link], [Link]
projet, description du projet).
10. Liste des projets (code et nom) dont la durée code, nom, duree
est la plus longue.
11. Lister le nombre d'intervenants par pôle de [Link], NbIntervenant
compétences (Libelle du pole, nombre
d’intervenants).
12. Temps moyen passé sur une activité pour SELECT nom, prenom,
chaque employé (nom et prénom). tempsMoyenActivite
NB. Temps passé sur une activité =(dateSortie-
dateEntree)
13. Nombre de projets par client (Numéro et [Link], [Link], nombreprojet
nom du client, nombreprojet).
14. Afficher le descriptif et le toal des charges en [Link], [Link],
Jours/Homme prévues pour le projet de code CumulCharge
P050309.
Exercice (MCT)
Une association culturelle a pour but de créer des spectacles théâtrales. les ventes des places
sont gérés suivant des règles de gestion suivantes :
- les réservations de place sont possibles sous certaines conditions (moins de 2 mois à
l'avance, places disponibles ...),
- pour toute réservation confirmée, une place doit être attribuée,
- aux heures d’ouverture, l'association peut délivrer, soit des billets à l’avance (déjà réserver),
soit des billets pour l'entrée immédiate,
- des réductions sont attribuées sur présentation d'un justificatif (militaires, étudiants,
handicapés,...),
- aucun billet ne peut être délivré si son paiement n'a pas été perçu au préalable,
- pour les entrées immédiates les billets sont délivrés sans attribution précise d'une place.
Pour l’émission de billet il y a des actions à effectuer :
- contrôle recevabilité de la réservation
- recherche des places disponibles
- attribution des places
- contrôle justificatif de réduction
- édition des billets
- calcul des prix
- Encaissement montant
- Remise billet
Question
Etablir le modèle conceptuel des traitements de base de l'entreprise.
1. liste des noms de projets concernant le SELECT [Link]
pôle réseau FROM projet,pole
WHERE [Link] = [Link]
AND [Link] = 'Réseau';
2. Noms des projets d'une durée supérieure à SELECT nom
10 jours (durée = dateDebutProjet- FROM projet
dateFinProjet) classée par Nom des projets WHERE (dateFin-dateDebut)> 10
décroissante Order by nom desc;
3. Noms des projets ayant obtenu une réponse SELECT nom
au mois de Février 2018. FROM projet
WHERE dateReponse BETWEEN #02/01/2018#
AND #02/28/2018#;
4. Liste des intervenants (nom et prénom) sur SELECT DISTINCT
l'activité Maquettage [Link],[Link]
FROM intervenant, participer,activite
WHERE [Link]=[Link]
AND [Link]=[Link]
AND [Link]='Maquettage';
5. Liste des projets (code et nom) dont le SELECT [Link], [Link],
nombre d'activités est supérieur à 5. COUNT([Link]) AS nbrActivite
FROM Activite,Projet
WHERE [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link])> 5;
6. Liste des intervenants (numéro, nom, SELECT DISTINCT [Link],
prénom) ayant participé au projet P050309. [Link], [Link]
FROM intervenant, participer, Activite
WHERE [Link] =
[Link]
AND [Link] = [Link]
AND codeProjet = 'P050309';
7. Durée moyenne d’un projet. SELECT AVG(dateFin-dateDebut) as
dureeMoyenneProjet
FROM projet;
8. Nombre de projets du pôle réseau. SELECT COUNT(*) AS NB
FROM projet, pole
WHERE [Link] = [Link]
AND [Link] = 'Réseau'
AND [Link] = 'Non Réalisé';
9. Lister les projets sur lesquels intervient SELECT DISTINCT [Link],
l'employé Alaoui Ahmed (Nom du projet, [Link],[Link],[Link]
description du projet). FROM projet, activite, participer, intervenant
WHERE [Link]=[Link]
AND [Link]=[Link]
AND [Link]=[Link]
AND [Link]= Alaoui
AND [Link]= Ahmed
10. Liste des projets (code et nom) dont la SELECT code, nom, (dateFin-dateDebut) AS duree
durée est la plus longue. FROM projet
WHERE (dateFin-dateDebut) = (SELECT
MAX(dateFin-dateDebut) FROM projet;);
11. Lister le nombre d'intervenants par pôle SELECT [Link], COUNT(*) AS NbIntervenant
de compétences (Libelle du pole, nombre FROM pole, intervenant
d’intervenants). WHERE [Link]=[Link]
AND dateFinContrat IS NULL
GROUP BY [Link];
12. Temps moyen passé sur une activité pour SELECT nom,prenom,AVG(dateSortie-dateEntree)as
chaque employé (nom et prénom). tempsMoyenActivite
FROM intervenant, participer
WHERE [Link]=[Link]
GROUP BY nom,prenom;
13. Nombre de projets par client (Numéro et SELECT [Link], [Link], COUNT(*) AS
nom du client, nombreprojet). nombreprojet
FROM projet, client
WHERE [Link]=[Link]
GROUP BY [Link], [Link];
14. Afficher le descriptif et le Total des SELECT [Link], [Link],
charges en Jours/Homme prévues pour le SUM(chargeJourHommePrev) AS CumulCharge
projet de code P050309. FROM projet, activite
WHERE [Link]=[Link]
AND [Link]='P050309'
GROUP BY [Link], [Link]
