Réponse AJAX TP SQL - Etudes de cas 2009

TP SQL basé sur
les études de cas 2009.


1
Etude de cas 2009, métropole, option développeur :
Cas SEG
(captage, traitement et distribution d'eau potable).

La distribution de l'eau aux clients se fait par l'intermédiaire d'abonnements. Un client souscrit un abonnement pour chacun de ses logements (résidence principale ou secondaire).

Pour chaque abonnement, la quantité d'eau consommée est mesurée à l'aide d'un compteur d'eau. Il arrive que le SEG soit amené à changer le compteur d'eau d'un abonnement (compteur défaillant par exemple). Un seul compteur est en service à un instant donné pour chaque abonnement mais on conserve l'historique de tous les compteurs d'un abonnement. Un compteur est repéré par la référence de l'abonnement et un numéro séquentiel correspondant à son ordre d'installation (le premier compteur d'un abonnement porte le numéro 1, le second le numéro 2, ...).

Périodiquement, les agents du SEG procèdent au relevé de chaque compteur d'eau en service afin de préparer le travail de facturation.

Pour gérer l'ensemble de ces informations, le SEG dispose de la base de données de schéma :

CLIENT(id, nom, prenom, adresse, cp, ville)
id : Clé primaire

ABONNEMENT(ref, date, client, adresse, cp, ville)
ref : Clé primaire
client : Clé étrangère en référence à id de CLIENT

COMPTEUR(abonnement, numOrdreCompteur, dateInstallation, marque)
abonnement, numOrdreCompteur : Clé primaire
abonnement : Clé étrangère en référence à ref de ABONNEMENT

RELEVE(abonnement, numOrdreCompteur, numOrdreReleve, date, index)
La clé primaire et les clés étrangères ne sont pas indiquées ici.
Chaque relevé est identifié par l’identifiant du compteur concerné et un numéro séquentiel. L'attribut index de la table RELEVE indique le nombre de m3 d'eau mesuré par le compteur à la date du relevé (consommation réalisée depuis la mise en service du compteur). Il convient de remarquer que lors de l'installation d'un nouveau compteur, l'index du compteur est initialisé à zéro.

TRAVAIL À FAIRE
2.1 Présenter le schéma entité-association correspondant au schéma relationnel de la base de données utilisée.
2.2 Écrire les ordres SQL permettant d'obtenir les résultats suivants :
A.
Création de la table RELEVE avec toutes les contraintes nécessaires.
Votre réponse n°1
B.
Affichage d’une liste des relevés (date et index) concernant l'abonnement "A2178", triée par dates de relevé décroissantes, quelque soit le compteur.
Votre réponse n°2
C.
Affichage d’une liste des abonnements avec indication du nom du client et du nombre de relevés effectués en 2008.
Votre réponse n°3
D.
Création d'une vue nommée MaxCompteur présentant pour chaque compteur d'eau la référence de l'abonnement, le numéro d'ordre du compteur et l’index maximum relevé.
Votre réponse n°4
E.
Affichage de la consommation totale, tous compteurs confondus, de l'abonnement "A2178" depuis sa souscription, en utilisant la vue précédente.
Votre réponse n°5

2
Etude de cas 2009, métropole, option administrateur de réseaux :
Cas FEFORT
(torréfaction et assemblage de cafés).

DOSSIER 3 - Gestion du parc informatique : analyse - conception

Annexes à utiliser : 7, 8

La gestion de parc informatique au sein d’un groupe aussi étendu géographiquement génère un certain nombre de difficultés. Le DSI a besoin d’avoir une visibilité sur le parc informatique.

Il vous demande donc de développer un applicatif, simple et efficace, à déployer sur les machines clientes. Celui-ci remontera les données au site central pour l’exploitation des informations stockées dans une base de données. Ces informations seront consultables via une interface web dont le développement n’est pas à traiter dans ce sujet.

Vous trouverez en annexe 7 une maquette de cette interface.

Pour vous lancer sur une piste de réflexion, le DSI vous présente le schéma relationnel que vous trouverez en annexe 8.

La gestion du parc informatique concerne aussi bien les aspects matériels que logiciels.

Les licences logicielles, sur des sites diversifiés à l’international ou qui ont été intégrés au groupe, posent un problème de gestion. Il faut uniformiser les applicatifs utilisés et leurs versions et surtout assurer la conformité avec la réglementation sur les licences.

Il y a des logiciels pour lesquels le groupe a acheté un nombre défini de licences (nbLicencesAchat) et dans ce cas, il faudra peut-être prévoir d’acheter des licences supplémentaires si le nombre d’installations réelles (nombre de keyProduct) est supérieur.

Travail à faire

3.1 Indiquer les modifications à apporter à l’ébauche du schéma relationnel donnée en annexe 8 pour intégrer l’attribut nbLicencesAchat.

Votre réponse n°6

Pour les interventions sur la base de données, le DSI souhaite créer un compte utilisateur dans le SGBD à qui on attribuerait tous les droits sur les tables EQUIPEMENT, LOGICIEL et INSTALLER et seulement le droit d’interroger la table USER.

Travail à faire

3.1 Écrire les requêtes SQL qui affectent ces droits au compte utilisateur gpiTechUser.

Votre réponse n°7

Le groupe a acheté un logiciel spécialisé de gestion électronique de documents dont la partie cliente est déployée sur les postes.

Travail à faire

3.1 Écrire la requête SQL qui ajoute le logiciel (66, "cindoc", "1.0") à la base de données.

Votre réponse n°8

3.2 Écrire la requête SQL qui interroge la base de données pour détecter les logiciels (désignation) dont le nombre d’installations (nombre de keyProduct) dépasse le nombre de licences possédées (nbLicencesAchat).

Votre réponse n°9

La table EQUIPEMENT a été identifiée comme étant une table très sollicitée dans la base de données. De nombreuses requêtes utilisent des sélections sur la rubrique nomHote, ce qui nécessite d’améliorer les temps de réponse.

Travail à faire

3.3 Écrire la requête SQL créant un index sur la rubrique nomHote de la table EQUIPEMENT.

Votre réponse n°10

Annexe 7 : Maquette de l'interface Web de consultation de la base de données

Annexe 8 : Schéma relationnel de gestion du parc informatique

SERVICE (numéro, libellé, responsable)
    numéro : CLÉ PRIMAIRE
    responsable : CLÉ ÉTRANGÈRE en référence à numéro de USER

USER (numéro, nom, prénom, fonction, travaillerService)
    numéro : CLÉ PRIMAIRE
    travaillerService : CLÉ ÉTRANGÈRE en référence à numéro de SERVICE

LOGICIEL (numéro, désignation, version)
    numéro : CLÉ PRIMAIRE

EQUIPEMENT (numéro, marque, modèle, nomHote, caractTech, idAffect)
    numéro : CLÉ PRIMAIRE
    idAffect : CLÉ ÉTRANGÈRE en référence à numéro de USER

INSTALLER (refEquip, idLog, keyProduct)
    refEquip, idLog : CLÉ PRIMAIRE
    refEquip : CLÉ ÉTRANGÈRE en référence à numéro de EQUIPEMENT
    idLog : CLÉ ÉTRANGÈRE en référence à numéro de LOGICIEL

3
Etude de cas 2009, Nouvelle-Calédonie, option développeur :
Cas ERGOSUM
(société de services informatiques).

DOSSIER 1 : Le catalogue des séjours en ligne

Annexe à utiliser : 1

L'une des activités principales du CE concerne les séjours thématiques et linguistiques, en France ou à l'étranger, proposés aux enfants du personnel. Ces séjours sont présentés dans un catalogue. Le CE prend en charge une partie du coût de ces séjours selon des critères prédéfinis.

La mise en ligne du catalogue des séjours sur l’intranet du CE a été réalisée en interne. Cette application s'appuie sur une base de données relationnelles dont un extrait du schéma est présenté en annexe 1.

Travail à faire

Écrire les requêtes SQL permettant d'obtenir les informations suivantes :

a)La liste des séjours se déroulant aux États-Unis (référence, résumé et dates de début et de fin).
Votre réponse n°11


b)La liste des séjours linguistiques ne proposant pas d’excursion (référence et prix).
Votre réponse n°12


c)La liste des séjours thématiques présentant le résumé et le coût total associé pour un départ de Lyon au mois de juillet 2009 (prix du séjour auquel s’ajoute le supplément fonction de la ville de départ).
Votre réponse n°13


d)La liste des séjours linguistiques se déroulant en Allemagne (référence, résumé et nombre d'excursions). On ne retiendra que les séjours pour lesquels il y a plus de trois excursions.
Votre réponse n°14

ANNEXE 1 : Extrait du schéma relationnel

Lieu (id, libellé, pays)
    Clé primaire : id

Séjour (ref, résumé, dateDébut, dateFin, prix, type, idLieu)
    Clé primaire : ref
    Clé étrangère : idLieu en référence à id de Lieu
    Remarque : l’attribut type est de type caractère et contient ‘T’ pour un séjour thématique et ‘L’ pour un séjour linguistique.

VilleDépart (id, nom)
    Clé primaire : id

Partir (refSéjour, idVille, supplément)
    Clé primaire : refSéjour, idVille
    Clé étrangère : refSéjour en référence à ref de Séjour
    Clé étrangère : idVille en référence à id de VilleDépart

Excursion (refSéjour, num, objet, idLieu)
    Clé primaire : refSéjour, num
    Clé étrangère : refSéjour en référence à ref de Séjour
    Clé étrangère : idLieu en référence à id de Lieu

Remarques :

4
Etude de cas 2009, Nouvelle-Calédonie, option administrateur de réseaux : Cas Tholdi (gestion de containeurs).

Dossier 4 - Réparation des containeurs

Annexe à utiliser : 6

La société Tholdi gère également le service de réparation des containeurs. L’année dernière plus de 25 000 containeurs ont été réparés dans les entrepôts situés près de ses différents ports maritimes. Environ 300 ouvriers et une vingtaine de cadres y sont affectés. L’entreprise gère toute la gamme des réparations, containeurs réfrigérés inclus.

Le schéma relationnel simplifié de la base de données correspondant à la gestion des réparations est fourni en annexe 6.

TRAVAIL À FAIRE


4.1 Écrire la requête SQL permettant de créer la table INTERVENIR en gérant les contraintes d’intégrité de clé primaire et de clés étrangères

Votre réponse n°15

Le containeur de numéro 34567 arrive pour une réparation le 23/12/2008 dans l’entrepôt 10.

TRAVAIL À FAIRE

4.2 Écrire la requête SQL qui permet d’ajouter la ligne correspondante dans la table REPARATION (le numéro de réparation est automatiquement incrémenté à chaque nouvelle réparation).

Votre réponse n°16

L’entreprise souhaite connaître la durée horaire totale passée pour chaque réparation.

TRAVAIL À FAIRE

4.3 Écrire la requête SQL permettant d’afficher pour chaque réparation le numéro de la réparation, le containeur concerné et la durée totale passée sur cette réparation.

Votre réponse n°17

4.4
Écrire la requête SQL permettant d’afficher le numéro du (ou des) entrepôt(s) ayant la superficie la plus faible.

Votre réponse n°18

M. Peguse, administrateur de la base, est chargé de la mise à disposition des différentes tables à M. Bonus qui saisit les informations. Il souhaite donner accès en lecture à M. Bonus à toutes les tables de la base, sauf à la table EMPLOYE, et en insertion aux tables REPARATION et INTERVENIR. M. Bonus pourra redistribuer ses droits de lecture sur la table CONTAINEUR à qui bon lui semble. Le compte utilisateur de M. BONUS a pour nom RBonus.

TRAVAIL À FAIRE

4.5
Écrire la ou les requêtes SQL que doit réaliser M. Peguse pour attribuer les droits à cet utilisateur.

Votre réponse n°19

TRAVAIL À FAIRE

Annexe 6 : Schéma relationnel de gestion des réparations

CONTAINEUR (numero, longueur, largeur, hauteur)
    numero : Clé primaire

REPARATION (numero, dateDeb, dateFin, numContaineur, numEntrepot)
    numero : Clé primaire
    numContaineur : Clé étrangère faisant référence à numero de la table CONTAINEUR
    numEntrepot : Clé étrangère faisant référence à numero de la table ENTREPOT

ENTREPOT (numero, superficie)
    numero : Clé primaire

EMPLOYE (numero, nom, qualif)
    numero : Clé primaire

INTERVENIR (numEmp, numRepar, nbHeures)
    numEmp, numRepar : Clé primaire
    numEmp : Clé étrangère faisant référence à numero de la table EMPLOYE
    numRepar : Clé étrangère faisant référence à numero de la table REPARATION

Informations complémentaires

La réparation d’un containeur s’effectue dans l’un des entrepôts du port. Plusieurs employés interviennent sur une réparation. Le champ nbHeures de la table INTERVENIR représente le nombre d’heures passées par chaque ouvrier sur une réparation.

Dictionnaire des données partiel

Table Code Type Longueur
EMPLOYE numero Numérique 5
EMPLOYE nom Chaîne 25
REPARATION numero Numérique 5
REPARATION dateDeb Date
INTERVENIR nbHeures Numérique 3


Sébastien PASTORE.