Base de Données Exam

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.

Exercice 1

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.

a. Création de la vue LIVR1

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.

b. Opérations de mise à jour possibles sur la vue LIVRAISON

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 :

• Si la vue est simple, basée sur une seule table sans agrégations ni jointures, on peut souvent faire des mises à jour.
• Si la vue contient des jointures, des agrégations, ou des filtres complexes, les mises à jour sont limitées ou interdites.

Sans plus d'informations sur la vue LIVRAISON, on peut dire :

• On peut probablement faire des mises à jour si la vue est simple et directement liée à une seule table.
• Sinon, les mises à jour sont interdites ou limitées.

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.

Exercice 2

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.

a. Création de la vue LIVR2

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.

b. Possibilités de suppression, mise à jour et insertion sur LIVR2

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.

• Suppression : On ne peut pas supprimer directement une ligne de LIVR2 car il s'agit d'une vue basée sur FPJ. La suppression d'une ligne dans la vue correspondrait à la suppression dans FPJ, ce qui est possible si la vue est updatable. Ici, la vue est simple, donc la suppression est possible.
• Mise à jour de DATE_PAIEMENT : DATE_PAIEMENT est une colonne calculée, donc on ne peut pas la mettre à jour directement via la vue.
• Insertion : L'insertion dans la vue nécessiterait de fournir NF, NJ, NP et DATE_PAIEMENT. Comme DATE_PAIEMENT est calculée, on ne peut pas insérer directement dans la vue sans spécifier DLIV dans la table FPJ. Donc l'insertion via la vue n'est pas possible.

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.

Exercice 3

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 :

• F (NF, NOMF, VILLEF) : fournisseurs
• P (NP, NOMP, COULEUR, POIDS, VILLEP) : pièces
• J (NJ, NOMJ, DLANC, VILLEJ) : projets
• FPJ (NF, NJ, NP, QTE, DLIV) : livraisons

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.

Exercice 4

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.

Exercice 5

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.

Exercice 6

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.

Exercice 7

Créer trois vues pour calculer les moyennes des étudiants :

• V_MOY_ELT : moyennes par élément (module), calculées comme la moyenne des deux meilleures notes parmi les trois notes de CC.
• V_MOY_UNIT : moyennes par unité, calculées comme la moyenne des MOY_ELT des éléments de l’unité.
• V_MOY_GEN : moyenne générale de l’étudiant, moyenne des MOY_UNIT, en supposant que toutes les unités ont le même coefficient.

a. Création de la vue V_MOY_ELT

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 :

1. Identifier les deux meilleures notes parmi les trois.
2. Calculer leur moyenne.

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.

b. Création de la vue V_MOY_UNIT

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.

c. Création de la vue V_MOY_GEN

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.

Méthode

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 :

• Respecter les définitions données (exemple : calcul de la moyenne des deux meilleures notes par soustraction de la plus petite note).
• Utiliser les jointures correctement pour relier les tables.
• Comprendre les limitations des vues pour les opérations de mise à jour.
• Utiliser les fonctions SQL adaptées (ADD_MONTHS, TO_CHAR, LEAST, AVG).
• Proposer des solutions d’optimisation comme la création d’index.

Les erreurs fréquentes à éviter sont :

• Ne pas prendre en compte les colonnes calculées dans les vues pour les mises à jour.
• Omettre les jointures nécessaires pour accéder aux informations dans plusieurs tables.
• Confondre la syntaxe des fonctions SQL selon le SGBD.
• Ne pas vérifier la logique du calcul des moyennes ou la cohérence des regroupements.