On vous demande de définir des formules de calcul permettant de

Université des Sciences Sociales
Année 2002-2003
Licence Pro RTIC PMO
Projet programmation niveau 2 : Oracle / Visual Basic
Remise du Projet : 10 Mars 2003
Partie Oracle :
Réaliser une petite application sur Oracle qui met en œuvre les quatre parties de l’examen
d’Oracle donné ci-dessous.
Documents à rendre :
 Un dossier utilisateur ;
 Un dossier technique spécifiant :
 le schéma E/A, les contraintes d’intégrité, le schéma relationnel
 pour chaque écran : les blocs (liens maître/détail, basé/non basé), les éléments
(basé/non/basé), les déclencheurs.
 Ce document doit expliquer comment sont implantées les règles de gestion.
Partie Visual Basic :
Réaliser une petite application en Visual Basic qui met en œuvre la partie II de l’examen
d’Oracle donné ci-dessous. Cette application VB doit être connectée à la base Oracle.
Documents à rendre :
 Un dossier utilisateur ;
 Un dossier technique.
DESS IGSI
Examen Base de Données Oracle
Durée : 3h Documents autorisés : les polycopiés
Cet examen comporte 4 parties sur les thèmes suivants : requêtes SQL, programmation
PL/SQL, mise en œuvre de formulaires avec developper 2000, mise en œuvre de déclencheurs
base de données. Ces quatre parties utilisent la base de données ci-dessous, extraite d'un
schéma plus complet de la base de données d’une société chargée de la commercialisation
d’hebdomadaires. Cette base de données renseigne sur les hebdomadaires diffusés par la
société, les dépositaires chargés de vendre ces hebdomadaires, la liste des hebdomadaires que
l'on doit livrer chaque semaine aux différents dépositaires avec la quantité à livrer, et les ventes
effectuées chaque semaine par les dépositaires pour les hebdomadaires qui leurs sont livrés. Le
schéma relationnel de cette base de données est le suivant :
HEBDOMADAIRES ( NHebdo, Titre, PrixRevient, PrixVente)
DEPOSITAIRES ( NDep, NomDep, AdrLiv, SecteurLiv )
LIVRER ( NDep*, NHebdo *, QtéSouhaitée, QtéLivrée )
VENDRE ( NDep*, NHebdo *, DateVente, QtéVendue )
Nhebdo, Titre
Numéro et titre d'un hebdomadaire
PrixRevient, PrixVente Prix de revient et prix de vente d'un hebdomadaire
NDep, NomDep
AdrLiv, SecteurLiv
QtéSouhaitée
QtéLivrée
DateVente
QtéVendue
Numéro et nom d’un dépositaire
Adresse et secteur de livraison d'un dépositaire
Nombre d’exemplaires souhaités par un dépositaire pour un
hebdomadaire
Nombre d’exemplaires effectivement livrés, compte tenu des ventes
du dépositaire
Date d’enregistrement d’une vente
Nombre d’exemplaires d’un hebdomadaire vendu dans la semaine par
un dépositaire
Quelques règles de gestion qui doivent être respectées :
1. A la fin de chaque semaine, un dépositaire doit enregistrer ses ventes pour tous les
hebdomadaire qui lui sont livrés.
2. Lorsqu'un dépositaire souhaite qu'on lui livre un nouvel hebdomadaire, il précise la quantité
souhaitée pour cet hebdomadaire. La quantité effectivement livrée au début est égale à la
quantité souhaitée par le dépositaire.
3. Si les quantités vendues une semaine par un dépositaire pour un hebdomadaire donné sont
inférieures à 70% des quantités livrées, la quantité effectivement livrée au dépositaire pour
l'hebdomadaire en question est réduite pour les semaines à venir à la dernière quantité
vendue.
Partie I : requêtes SQL (4 points)
Ecrire en SQL les deux requêtes suivantes :
1. Calculer pour chaque hebdomadaire le bénéfice total réalisé.
2. Liste des dépositaires qui n'ont pas enregistré leurs ventes le 29/11/2002
Partie II : programmation en PL/SQL (5 points)
Soit le formulaire représenté ci-dessous. Ce formulaire permet de demander d’afficher les
hebdomadaire qui rapportent le plus à un dépositaire donné, c'est à dire ceux qui lui permettent
de faire 50% de son chiffre d’affaire (CA) en les classant dans l’ordre décroissant du
pourcentage de CA rapporté. A titre d’exemple, si l’on suppose que le dépositaire n° 10 vend 5
hebdomadaires nommés h1, h2, h3, h4, h5 qui lui font réaliser respectivement 10%, 20%, 30%,
15%, 25% de son CA, on souhaite voir affiché comme réponse les deux hebdomadaires
suivants : (h3, 30%), (h5, 25%) qui permettent au dépositaire de réaliser à eux deux plus de
50% de son CA. Ce formulaire comporte deux blocs non basés. Un premier bloc permet de
formuler la question. Ce bloc est constitué d’un élément nommé NDEP permettant à l'utilisateur
de saisir le n° du dépositaire qui l’intéresse et d’un bouton permettant de lancer le calcul et
l’affichage. Le deuxième bloc permet d’afficher le résultat. Dix enregistrements sont affichées
pour ce bloc. Chaque enregistrement est constitué de trois éléments nommés NHEBDO, TITRE
et POURCENTAGE_CA permettant respectivement d’afficher le numéro, le titre et le
pourcentage du chiffre d’affaire réalisé pour un hebdomadaire.
Ecrire le programme PL/SQL qui doit être associé à ce bouton. Précisez à quel évènement vous
associez ce programme. Pour effectuer ce calcul, nous vous suggérons de commencer par
envoyer une requête permettant de sélectionner les hebdomadaires vendus par le dépositaire,
triés dans l’ordre décroissant du chiffre d’affaire qu’ils ont apporté, puis de boucler sur le
résultat obtenu à cette requête pour afficher les hebdomadaires jusqu'à ce que l’on ait atteint
50% du CA.
Partie III : mise en œuvre de formulaires avec developper 2000 (6 points)
Proposez un formulaire permettant de saisir les hebdomadaires que l’on doit livrer à chaque
dépositaire, avec les quantités souhaitées. Dessinez le formulaire proposé. Précisez les tables
sur lesquelles doivent être basés les blocs constituant ce formulaire. Précisez pour chaque
élément de ce formulaire s'il doit être basé ou non, et si oui quelle est sa source. Indiquez les
éléments qui peuvent être remplis automatiquement en précisant sur quel évènement et
comment. Proposez une LOV intelligente pour la sélection des hebdomadaires.
Proposez un deuxième formulaire permettant de saisir les ventes d’hebdomadaires effectuées
par un dépositaire chaque semaine. Il est fortement souhaitable que ce formulaire comporte un
bouton permettant d’insérer automatiquement dans les ventes, la liste des magazines livrés au
dépositaire avec la date du jour, de sorte que l’utilisateur n’ait plus qu’à enregistrer les
quantités vendues. Ainsi la règle de gestion n°1 est respectée automatiquement. Dessinez le
formulaire et donnez le code du bouton.
Remarque : les deux formulaires peuvent être confondus en un seul.
Partie IV : mise en œuvre de déclencheurs base de données (5 points)
Faites respecter la règle de gestion n°3 au niveau du serveur Oracle.
Spécifiez le ou les déclencheurs nécessaires en précisant à chaque fois : la table à laquelle est
associé le déclencheur, l’événement correspondant et le code associé.