Mr Christian FISCHER

BD40 : Conception des Systèmes d'Information

Printemps 2009
Examen final
Support de cours et travaux dirigés de BD40 autorisés

Le sujet comprend 3 dossiers indépendants.

Durée : 2 heures

Barème :

Barème
Dossier 1 Modélisation conceptuelle des données des
branchements électriques d'EDF 8 pts

Dossier 2 Location : Normalisation d'une relation 2 pts

Dossier 3 Epoka - Revues

A. Passage du MEA au MLR 2 pts

B. Requêtes SQL 8 pts

Christian FISCHER - Copyright © Juin 2009

UTBM Final de BD40 PAGE 1

DOSSIER 1 : GESTIO. DES BRA.CHEME.TS ÉLECTRIQUES

L’activité du centre EDF de Douvres est essentiellement axée autour des branchements
électriques dans le département du Calvados. Le centre sous-traite une partie de son
activité en confiant à des entreprises extérieures la réalisation des branchements chez les
clients.
Le traitement d’un branchement se déroule en plusieurs étapes :
• l’enregistrement de la demande de branchement d’un client et la validation des
informations collectées,
• l’élaboration du devis correspondant à la demande,
• la gestion des plannings et la communication des dates et lieux des rendez-vous aux
sous-traitants,
• la réalisation des branchements par les sous-traitants,
• l’enquête de qualité afin de mesurer le degré de satisfaction des clients ainsi que la
qualité du travail réalisé par les sous-traitants et par le centre de Douvres.

Gestion des contrats

Pour organiser les branchements, le département est découpé en ZEI (zones élémentaires
d’intervention). Une ZEI correspond à un secteur autour d’une ville principale.
On planifie chaque opération à réaliser en tenant compte d’une durée théorique appelée poids.

Le centre EDF signe chaque contrat avec un seul sous-traitant. Un sous-traitant peut signer
plusieurs contrats avec le centre EDF.
On enregistre le code sous-traitant, le nom du sous-traitant et l’adresse complète (numéro,
rue, code postal, ville) du sous-traitant.
Un contrat constitue un engagement formel du sous-traitant d’intervenir chez le client EDF
pour faire les installations. Chaque contrat est identifié par un numéro de contrat et possède
une date de signature.
Un contrat précise dans quelles ZEI (annexe 3) un sous-traitant est susceptible d'intervenir.
Pour chacune de ces ZEI, le contrat précise les jours d'intervention possibles. Par exemple, le
sous-traitant « STEN », au titre du contrat n° 385, peut intervenir tous les mardis et mercredis
chez les clients de la ZEI « Bayeux » et tous les mardis, jeudis et vendredis dans la ZEI «
Isigny ».

Gestion de la nomenclature des opérations

Une demande de branchement entraîne plusieurs opérations caractérisées par un code, un
libellé et un poids.
Il a été répertorié deux catégories d'opérations : les opérations administratives et les
opérations techniques.
Chaque catégorie est elle-même composée en sous catégories (annexe 2).
Une opération appartient à une et une seule sous catégorie.

UTBM Final de BD40 PAGE 2

Gestion des demandes
Il existe trois types de demande de branchement électrique : branchement neuf, branchement
provisoire ou modification du branchement actuel.

À chaque demande de branchement, le client doit compléter un dossier (annexe 1).

Lorsque le centre réceptionne ce document, il attribue une référence qui permettra d’identifier
le dossier du client, puis il enregistre la date de la demande. Cette demande sera
obligatoirement rattachée à une ZEI lors de la création du dossier.

Une demande donne lieu à l’établissement d’un devis, daté et identifié par un numéro de
devis.
Ce devis informe sur le montant estimé des travaux, le nom, le prénom et le matricule de
l’agent qui l’a rédigé.
Le branchement ne sera réalisé que si le client accepte le devis. Dans ce cas, on enregistre la
date d’acceptation. On n'établit jamais plus d'un devis pour une demande.
Une demande peut nécessiter plusieurs opérations.
Les maîtres d’œuvre sont identifiés par un code maître d’œuvre ; les électriciens par numéro
de professionnel.

Travail à faire
Proposer un modèle entité-association représentant les informations nécessaires pour gérer les
contrats des sous-traitants et les dossiers de demande de branchement électrique.

Votre solution sera représentée à l’aide de trois sous modèles :

1. Gestion des contrats
2. Gestion de la nomenclature
3. Gestion des demandes

Le modèle global n’est pas nécessaire

UTBM Final de BD40 PAGE 3

Annexe 1 : Extrait d’un dossier de demande de branchement

DEMA.DE DE BRA.CHEME.T ÉLECTRIQUE

Date de la demande : .. / .. / ….
Cadre réservé à nos services
Référence Dossier : ZEI de rattachement :

.OM : ……………………………….. PRÉ.OM : ………………………….

COORDO..ÉES ACTUELLES

ADRESSE : ……………………………………………………………………………….
………………………………………………………………………………..
TÉL : …………………………………………

ADRESSE DES TRAVAUX

RUE : ……..……………………..
.OM LOTISSEME.T : ………………………….…
COMMU.E : ……………………………………………………………

TYPE DE LA DEMA.DE
.euf Provisoire Modification
Date emménagement Date début : .. / .. / …. Description :
.. / .. /…. Date fin : .. / .. / ….

POUR U. BRA.CHEME.T .EUF :

COORDO..EES DES PROFESSIO..ELS QUI VOUS ACCOMPAG.E.T

DA.S VOTRE PROJET DE CO.STRUCTIO.

VOTRE ÉLECTRICIE.
.OM : ………………………………………………………
ADRESSE : ……………………………………………………………………………
TEL : …………………………………………
VOTRE MAÎTRE D'ŒUVRE (éventuellement)
.OM : ………………………………………………………
ADRESSE : ……………………………………………………………………………
TEL : …………………………………………

SIG.ATURE

UTBM Final de BD40 PAGE 4

Annexe 2 : Extrait de la description des différentes opérations

.OME.CLATURE DES OPÉRATIO.S (Mise à jour au 25/01/2007)

AD OPÉRATIONS ADMINISTRATIVES Poids

AD1 Front-Office
AD11 Demande de dossier d'un client 10
AD12 Demande d’informations au client 10
AD13 Réclamation d'un client 15
AD14 Demande RDV du client 15
AD2 Back-Office
AD21 Envoi dossier vierge 10
AD22 Demande complément d’information 10 La sous-catégorie « Traitement dossier »,
AD23 Traitement relance client 15 codifiée AD3 est la troisième sous-
AD24 Confirmation d’enregistrement 10 catégorie de la catégorie « Opérations
AD3 Traitement dossier administratives » codifiée AD.
AD31 Étude branchement 45
AD32 Planification des travaux 45
… … …

IN OPÉRATIONS TECHNIQUES Poids L’opération « Planification des travaux »,

IN1 Branchement électrique codifiée AD32 est la deuxième opération
IN11 Récupération ligne 45 de la sous-catégorie « Traitement
IN12 Pose ligne 45 dossier ».
IN13 Dépose ligne 25
… ...
IN2 Contrôles techniques
IN21 Test branchement 45
IN22 Vérification compteur 30
… … …

UTBM Final de BD40 PAGE 5

Annexe 3 : Liste des différentes ZEI (zones élémentaires d’intervention)

Le département est découpé en ZEI.

Une ZEI correspond à un secteur autour d’une ville principale.

Code ZEI NOM VILLE

BA BAyeux
CA CAen
CG CabourG
CH CHeux
CO COnde sur Noireau
DO DOuvres
DV DeauVille
FA FAlaise
FM Fontenay le Marmion
HO HOnfleur
IS ISigny
LI LIsieux
MO MOult
OR ORbec
SP St Pierre sur Dives
TR TRoarn
VI VIre

UTBM Final de BD40 PAGE 6

 DOSSIER 2 : .ormalisation d’une relation

Soit la relation suivante :

.umClient .omClient .umAppt AdrAppt DateDebLoc DatFinLoc Montant .umProp .omProp

CR76 Pierre PG4 1 rue de 1/7/2006 15/7/2006 1500€ CX40 Julie
JEAN la gare MONNIN
Nice
PG7 7 rue des 1/7/2006 30/7/2006 3000€ CX90 Jean
roses MARTIN
Cannes
CR56 Claire PG4 1 rue de 1/8/2006 30/8/2006 3500€ CX40 Julie
MARIE la gare MONNIN
Nice
PG1 1 Grande 1/7/2006 30/8/2006 5000€ CX90 Jean
Rue MARTIN
Nice
PG7 7 rue des 8/8/2006 30/8/2006 2800€ CX90 Jean
roses MARTIN
Cannes

Les informations représente les locations d’appartements de propriétaires à des clients pour la
période des vacances d’été.

Travail à réaliser

1. Quel est le niveau de normalisation de la relation présentée. Justifier votre réponse.

2. Identifier les dépendances fonctionnelles de cette relation.

3. Proposer un schéma relationnel en 3ème forme normale

UTBM Final de BD40 PAGE 7

DOSSIER 3 : Gestion des invendus

EPOKA PRESSE est une société de presse qui conçoit, réalise et publie des magazines
vendus sur tout le territoire français (métropole et DOM-TOM).

On emploie indifféremment les termes « magazine » et « revue ».

Le dernier titre mis sur le marché par EPOKA PRESSE est le magazine mensuel « SAVOIR
VENDRE ». Ce magazine très spécialisé vise un public de vendeurs qu’il a pour objectif de
conseiller dans l’exercice de leur métier.

Les magazines édités par EPOKA PRESSE sont distribués par une entreprise de diffusion qui
assure aussi le retour des invendus.

EPOKA PRESSE gère les invendus à l’aide d’une base de données dont le schéma conceptuel
est présenté ci-dessous. Un taux d’invendus (TI) est calculé pour chaque point de vente à
partir du nombre d’exemplaires reçus (NR) et du nombre d’exemplaires vendus (NV) par le
point de vente.
TI = (NR-NV) / NR

3.A. Passage du modèle entité-association au modèle relationnel

Travail à faire
1. Fournir le modèle relationnel associé au MEA ci-dessus (représentation graphique ou
textuelle)
2. Proposer une solution pour faciliter la consultation du taux d'invendu par point de vente.

UTBM Final de BD40 PAGE 8

 3.B. Requêtes SQL

Travail à faire : Ecrire les requêtes SQL suivantes

La syntaxe d’écriture des jointures est libre.

Requête 1
Afficher la liste des points de vente de la ville de Belfort

Requête 2
Afficher le titre de la revue, le numéro de la revue, la date de parution des revues de plus de
10000 tirages parues sur l'année 2009.

Requête 3
Afficher la liste des revues (titre, code revue, tirage) reçus au mois de juin 2009 au point de vente
ayant pour nom "La belle gazette".

Requête 4
Afficher les numéros des points de vente pour lesquels le taux d’invendus pour le code de revue
"010V" a été supérieur à 20% pour l'ensemble des revues distribuées.

Requête 5
Afficher le nom du point de vente et le total de d'exemplaires reçus pour toutes les revues parues
entre le 1 juin 2009 et le 21 juin 2009. Cette liste sera triée par ville et nom de point de vente

Requête 6
Afficher le titre de la revue et le nombre d'exemplaires tirés pour l'année 2009. Seuls les titres
ayant un tirage annuel supérieur à 50000 seront affichés.
La liste sera triée par nombre d'exemplaires tirés décroissant.

Requête 7
Afficher le nom du point de vente et le taux d'invendus pour toutes les revues livrées en 2009.
La liste sera triée par taux d'invendus décroissant.
Les points de vente avec un taux d'invendus < 10% sont exclus du résultat.

Requête 8
Afficher tous les noms de points, le nombre total d'exemplaires reçus pour la revue "Le journal
des bases de données" sur les deux dernières années à compter de la date d'exécution de la
requête.
Tous les points de vente doivent figurer dans le résultat avec un tri sur le nombre total
d'exemplaires décroissant.

UTBM Final de BD40 PAGE 9