Pages - Menu

Pages

Examen mcd-sql-mld- Modèle relationnel et sql

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#



2 commentaires: