Pages - Menu

Pages

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.
--------------------------------------------------------------------------------------
          

Aucun commentaire:

Enregistrer un commentaire