Base de Données Exam
Base de Données Exam
Database Management, Programming · exam
Fiche de révisionCorrigé
Rédigée à partir de ce document, puis vérifiée avant publication.
Ce document présente un examen sur le module Base de Données, évaluant les compétences en création de vues SQL, gestion des séquences, manipulation des données et optimisation des requêtes. Il teste également la capacité à modéliser des calculs complexes de moyennes à partir de données relationnelles.
Il s'agit de créer une vue LIVR1 qui inclut les livraisons effectuées pour le projet ‘J1’ puis d'identifier les opérations de mise à jour possibles sur la vue LIVRAISON.
La vue LIVR1 doit contenir les livraisons associées au projet dont le code est ‘J1’. La table FPJ contient les livraisons avec les colonnes NF (fournisseur), NJ (projet), NP (pièce), QTE (quantité), DLIV (date de livraison).
On sélectionne donc les lignes de FPJ où NJ = 'J1'. La requête SQL correspondante est :
CREATE VIEW LIVR1 AS
SELECT *
FROM FPJ
WHERE NJ = 'J1';
Réponse : La vue LIVR1 est créée avec la requête ci-dessus.
La question concerne la vue LIVRAISON, qui n'est pas explicitement définie dans le texte, mais on peut supposer qu'il s'agit d'une vue sur la table FPJ ou une table similaire.
En général, les opérations de mise à jour (INSERT, UPDATE, DELETE) sur une vue dépendent de sa définition :
Sans plus d'informations sur la vue LIVRAISON, on peut dire :
Réponse : Sans définition précise de la vue LIVRAISON, on ne peut pas déterminer exactement les opérations de mise à jour possibles. En général, seules les vues simples permettent les mises à jour.
Créer la vue LIVR2 qui inclut pour chaque livraison le NF, NJ, NP ainsi que la date de paiement, puis déterminer les opérations possibles de suppression, mise à jour de la date de paiement et insertion dans cette vue.
La vue doit afficher les colonnes NF, NJ, NP (identifiants fournisseur, projet, pièce) et la date de paiement. La date de paiement n'est pas explicitement stockée dans les tables fournies, mais la consigne indique que le paiement intervient trois mois après la livraison.
On peut donc calculer la date de paiement en ajoutant trois mois à la date de livraison DLIV de FPJ.
La requête SQL pour créer la vue est :
CREATE VIEW LIVR2 AS
SELECT NF, NJ, NP, ADD_MONTHS(DLIV, 3) AS DATE_PAIEMENT
FROM FPJ;
Note : La fonction ADD_MONTHS est utilisée ici pour ajouter 3 mois à la date DLIV.
Réponse : La vue LIVR2 est créée avec la requête ci-dessus.
La vue LIVR2 contient une colonne calculée (DATE_PAIEMENT) qui n'existe pas physiquement dans la table FPJ, mais est dérivée de DLIV.
Réponse : On peut supprimer des lignes via la vue LIVR2, on ne peut pas mettre à jour la date de paiement, et on ne peut pas insérer de nouvelles livraisons via cette vue.
Créer la vue LIVR3 qui affiche les livraisons effectuées sans que le fournisseur ne se déplace ni pour ramener les pièces, ni pour les livrer au projet.
Il faut donc sélectionner les livraisons où le fournisseur ne se déplace pas pour ramener les pièces ni pour les livrer.
Les tables concernées sont :
Le fournisseur se déplace pour ramener les pièces si la ville du fournisseur est différente de la ville de la pièce (VILLEF ≠ VILLEP).
Le fournisseur se déplace pour livrer au projet si la ville du fournisseur est différente de la ville du projet (VILLEF ≠ VILLEJ).
On cherche donc les livraisons où VILLEF = VILLEP et VILLEF = VILLEJ.
La requête SQL pour la vue LIVR3 est :
CREATE VIEW LIVR3 AS
SELECT FPJ.*
FROM FPJ
JOIN F ON FPJ.NF = F.NF
JOIN P ON FPJ.NP = P.NP
JOIN J ON FPJ.NJ = J.NJ
WHERE F.VILLEF = P.VILLEP
AND F.VILLEF = J.VILLEJ;
Réponse : La vue LIVR3 est créée avec la requête ci-dessus.
Créer une séquence SEQ_F avec une valeur minimale de 100, une valeur maximale de 9000, qui commence à 1000 et est cyclique.
La syntaxe SQL standard pour créer une séquence cyclique est :
CREATE SEQUENCE SEQ_F
MINVALUE 100
MAXVALUE 9000
START WITH 1000
CYCLE;
Réponse : La séquence SEQ_F est créée avec la commande ci-dessus.
Insérer un fournisseur ‘CIMABEX’, localisé à ‘ROME’, dont le code est une concaténation de la valeur suivante de SEQ_F et de l’année de la saisie (en deux chiffres), séparés par un slash (‘/’).
Le code du fournisseur est donc : <valeur_suivante_SEQ_F>/<année_2_chiffres>.
Supposons que la date de saisie est la date système SYSDATE, on extrait l’année sur deux chiffres avec TO_CHAR(SYSDATE, 'YY').
La requête d’insertion est :
INSERT INTO F (NF, NOMF, VILLEF)
VALUES (
TO_CHAR(SEQ_F.NEXTVAL) || '/' || TO_CHAR(SYSDATE, 'YY'),
'CIMABEX',
'ROME'
);
Réponse : L’insertion est réalisée avec la requête ci-dessus.
Proposer une solution pour accélérer les recherches fréquentes sur les villes des fournisseurs dans la table F.
Pour améliorer les performances des requêtes filtrant sur la colonne VILLEF, on peut créer un index sur cette colonne.
La commande SQL est :
CREATE INDEX IDX_VILLEF ON F(VILLEF);
Réponse : La création d’un index sur la colonne VILLEF accélère les recherches.
Créer trois vues pour calculer les moyennes des étudiants :
Pour chaque étudiant (NCE) et élément (CODE), on calcule MOY_ELT comme la moyenne des deux meilleures notes parmi NOTE_CC1, NOTE_CC2, NOTE_CC3.
Étapes :
Une méthode possible en SQL est d’utiliser des fonctions CASE pour comparer les notes :
CREATE VIEW V_MOY_ELT AS
SELECT
NCE,
CODE,
(
(NOTE_CC1 + NOTE_CC2 + NOTE_CC3)
- LEAST(NOTE_CC1, NOTE_CC2, NOTE_CC3)
) / 2 AS MOY_ELT,
E.CO EFFICIENT,
E.UNITE
FROM EVALUATION EV
JOIN ELEMENT E ON EV.CODE = E.CODE;
Explication : La somme des trois notes moins la plus petite note donne la somme des deux meilleures notes. On divise par 2 pour obtenir la moyenne.
Réponse : La vue V_MOY_ELT est créée avec la requête ci-dessus.
La vue V_MOY_UNIT calcule pour chaque étudiant (NCE) et unité (UNITE) la moyenne des MOY_ELT des éléments de cette unité.
On utilise la vue V_MOY_ELT pour agréger :
CREATE VIEW V_MOY_UNIT AS
SELECT
NCE,
UNITE,
AVG(MOY_ELT) AS MOY_UNIT
FROM V_MOY_ELT
GROUP BY NCE, UNITE;
Réponse : La vue V_MOY_UNIT est créée avec la requête ci-dessus.
La moyenne générale MOY_GEN est la moyenne des MOY_UNIT de chaque étudiant, en supposant que toutes les unités ont le même coefficient.
On agrège donc sur NCE :
CREATE VIEW V_MOY_GEN AS
SELECT
NCE,
AVG(MOY_UNIT) AS MOY_GEN
FROM V_MOY_UNIT
GROUP BY NCE;
Réponse : La vue V_MOY_GEN est créée avec la requête ci-dessus.
Ce sujet récompense la maîtrise des requêtes SQL, notamment la création de vues simples et complexes, la manipulation des dates, la gestion des séquences, et la modélisation de calculs statistiques via des vues imbriquées.
Il est important de :
Les erreurs fréquentes à éviter sont :