Examen mcd-sql-mld- Modèl
MODÈLE RELATIONNEL ET REQUÊTES SQL
Dans la perspective de la refonte du SI, l’étude des autres composantes du système d’information actuel en vue de leur unification est une étape indispensable.
Après le domaine de la gestion collective, on s’intéresse à l’activité « Gestion privée » de STAR FINANCE. On vous présente en annexe 1 un extrait du schéma conceptuel des données propre à ce pôle.
Travail à faire
1. Traduire le modèle conceptuel de données en modèle relationnel.
2. Rédiger les requêtes suivantes en langage SQL :
2.1. Nom et prénom des clients possédant un compte titre de la catégorie «risque modéré».
2.2. Nombre d’opérations par compte.
2.3. Numéro et nom des clients ne possédant pas de compte de la catégorie « risque élevé ».
2.4. Total des montants des opérations par compte.
3. Dans le but d’archiver les opérations anciennes, l’administrateur de la base de données a créé une table ARCH-OPÉ ayant la même structure que la table OPÉRATION.
Rédiger les requêtes suivantes en langage SQL :
3.1. Insérer dans la table ARCH-OPÉ les opérations antérieures à une date saisie au clavier lors de l’exécution de la requête.
3.2. Supprimer de la table ARCH-OPÉ les enregistrements archivés dont l’année est inférieure ou égale à 2000. On peut utiliser la fonction YEAR.
------------------------------------------------------------------------------------------------
CORRECTION
------------------------------------------------------------------------------------------------
1- Traduction du MCD en modèle relationnel
CLIENT (N°Client, Nom, Prénom, Ad_Rue, Ad_CodeP, Ad_Ville)
COMPTE (N°Compte, Date_Création, Type_Extrait, Fiscalité, #N°Client, #N°Catégorie)
CATEGORIE (N°Catégorie, Lib_Catégorie)
VALEUR (Code-Valeur, Libellé_Valeur, Nominal_Valeur, Date_Emission, Date_Echéance)
OPERATION (N°Opération, Date_opération, Sens_opération, Quantité_Négociée, Cours_Négocié, #Code_Valeur)
AFFECTER (N°Opération, N°Compte, Quantité_Aff)
2- Requêtes simples SQL
a) Nom et prénom des clients possédant un compte titre de catégorie «risque modéré»
|
SELECT Nom, Prénom
|
|
FROM COMPTE, CLIENT, CATÉGORIE
|
|
WHERE COMPTE.N°Client=CLIENT.N°Client
|
|
AND COMPTE.N°Catégorie=CATÉGORIE.N°Catégorie
|
|
AND Lib_Catégorie = "Risque Modéré" ;
|
b) Le nombre d’opérations par compte
|
SELECT N°Compte, COUNT (N°Opération) AS [Nombre d’opérations]
|
|
FROM AFFECTER
|
|
GROUP BY N°Compte ;
|
|
c) Numéro et nom des clients ne possédant pas de compte de catégorie « risque élevé »
SELECT N°Client, Nom
|
|
FROM CLIENT
|
|
WHERE N°Client NOT IN
|
|
(SELECT N°Client
|
|
FROM COMPTE, CATÉGORIE
|
|
WHERE COMPTE.N°Catégorie=CATÉGORIE.N°Catégorie
|
|
AND Lib_Catégorie ="risque élevé" ;) ;
|
|
d) Total des montants des opérations par compte .
SELECT N°Compte, SUM (Quantité_Aff*Cours_Négocié) AS [Montant Total]
|
|
FROM OPERATION, AFFECTER
|
|
WHERE OPERATION.N°opération = AFFECTER.N°Opération
|
|
GROUP BY N°Compte ;
|
3- Requêtes d’archivage SQL)
a) Insérer dans la table ARCH_OPE les opérations antérieures à une date saisie au clavier lors de l’exécution de la requête.
|
INSERT INTO ARCH_OPE
|
|
SELECT *
|
|
FROM OPÉRATION
|
|
WHERE Date_opération < [Entrez une date d’opération : ] ;
|
b) Supprimer de la table ARCH_OPE les enregistrements archivés dont l’année est inférieure ou égale à 2000. On peut utiliser la fonction YEAR.
|
DELETE
|
|
FROM ARCH_OPE
|
|
WHERE YEAR( Date_opération) <= 2000 ;
|
|
OU
|
|
WHERE Date_opération <= #01/01/2000#
|