Examen Corrigé SQL Base de Données SGBD Langage SQL SGBD
Énoncé :
La société ANABIO est spécialisée dans les analyses de produits et matériaux industriels qui ont pour but d‘en garantira qualité et la sécurité.
ANABIO emploie environ 300 personnes, de formation scientifique, réparties dans six laboratoires situés à Strasbourg, Rennes, Marseille, Montpellier, Bordeaux et Toulouse.
ANABIO possède des équipements très modernes (résonance magnétique nucléaire,spectrographes de masse, etc.)
1) Présentation du modèle
Chaque jour, le laboratoire réceptionne des échantillons pour lesquelles différentes analyses doivent être effectuées. Les informations liées à cette activité sont gérées à partir du schéma relationnel suivant :
CLIENT (codeClient, nomClient, prénomClient, rueClient, CPClient, villeClient,elClient)
o codeClient : clé primaire.
TYPEANALYSE(refTypeAnalyse, designationTypeAnalyse, prixTypeAnalyse )
o refTypeAnalyse : clé primaire
ECHANTILLON(codeEchantilon, dateEntree, codeClient)
o codeEchantillon : clé primaire
o codeClient : clé étrangère en référence à codeClent de CLIENT
REALISER(codeEchantillon, refTypeAnalyse, dateRealisation)
o codeEchantillon, refTypeAnalyse : clé primaire
o codeEchantillon : clé étrangère en référence à codeEchantillon de ECHANTILLON
o refTypeAnalyse : clé étrangère en référence à refTypeAnalyse de TYPEANALYSE
Les valeurs des champs codeEchantillon et refTypeAnalyse de la relation REALISER sont saisies à la réception de l‘échantillon mais le champ dateRealisation n‘est pas immédiatement renseigné : il le sera lorsque l‘analyse aura été réalisée.
2) Modèle conceptuel
Afin de vous aider dans la compréhension du problème, réaliser le modèle conceptuel à partir du modèle relationnel. Complétez, ce schéma, et barrez les symboles non utilisés.
3) Réalisation de la base de données
o Tous les champs code et référence sont des entiers.
o Le code postal est de type chaîne de caractère sur une longueur de 5
o Les dates sont de types dates (jour, mois, année)
o Le prix est de type décimal avec 2 chiffres après la virgule
o La désignation type analyse est codée sur 10 caractères fixes.
o Les autres champs sont de types chaîne de caractères. La longueur sera déterminée par vos soins, la place dans la base devant être optimisée.
3.1 Rédigez l'ensemble des requêtes permettant de créer la base de données (les contraintes seront nommées).
3.2 Modifiez la structure de la table Client afin d'ajouter un champ email.
4) Requêtes de sélection
4.1 Donnez la liste des clients référencés
4.2 Indiquez les noms et prénoms des clients habitant Paris
4.3 Donnez le nombre de client référencé dans chaque ville
4.4 Donnez la même information par ordre décroissant du nombre de client
4.5 Donnez la liste des clients (nom, prénom) habitant dans le département de la Meuse.
4.6 Donnez les noms et prénoms des clients n'ayant pas d'adresse e-mail.
4.7 Donnez les noms, prénoms et adresses complètes des clients ayant fourni des échantillons, ainsi que la date entre le 01-10-2005 et le 31-12-2005/
4.8 Donnez le nombre d'analyse en cours (non réalisées)
4.9 Donnez les noms, prénoms des clients attendant le résultat d'une analyse(analyse non réalisée).
4.10 Donnez pour chaque client par ordre alphabétique (nom et prénom) le nombre d'échantillons déposés ainsi que les dates de dépôt du plus ancien et du plus récent.
4.11 Donnez la liste des analyses réalisées au mois de mars 2005.
4.12 Donnez pour chaque échantillon, le nombre prévu d'analyse(réalisées ou non).
4.13 Donnez les codes et dates d‘entrée des échantillons pour lesquels aucune analyse n‘a été réalisée.
4.14 Donnez les codes et dates d‘entrée des échantillons pour lesquels plus de 5 analyses ont été réalisées ;
4.15 Donnez les désignations des analyses dont le prix est inférieur à celui de l‘analyse possédant la référence ”A102”.
5) Requêtes de mise à jour
5.1 La base est vierge. Réalisez l'insertion d'un jeu de données dans les différentes tables. Les données seront définies par vous-même à votre convenance.
5.2 Augmentez de 10% tous les prix des analyses.
5.3 Il a été défini un prix plancher de 8 Euros pour toutes les analyses. Mettez à jour la table ANALYSE.
5.4 Aujourd'hui, toutes les analyses en cours ont été réalisées. Mettez à jour la base.
5.5 Le client Dupont vient de fournir son adresse e-mail (Dupont@btsig.fr). Mettre à jour la table correspondante
5.6 Suite à un bug informatique, des entrées ont été réalisées le 01 février 2007 au lieu du 1er février 2006. Mettez à jour la base.
5.7 Afin de préparer la nouvelle campagne, de nouvelles analyses ont été définies.
Ces nouvelles analyses sont disponibles dans une table ANALYSE2006 dont la structure (champs, types de donnée) est identique à TYPEANALYSE. Mettez à jour la table TYPEANALYSE à partir de la table ANALYSE2006.
--------------------------------------------------------------------------------------
Article plus récent Article plus ancien
