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#