Solutions des exercices du chapitre 6 selon la syntaxe du dialecte ...
... seul Client, c'est-à-dire ne peut être liée à des Commandes de plusieurs
Clients. Solutions des exercices du chapitre 6 selon la syntaxe du dialecte Oracle
.
part of the document
Exercices du chapitre 6
Pour chacun des cas qui suit, déterminer comment faire respecter les contraintes dintégrité en SQL pour le schéma PleinDeFoin. Donnez le code SQL correspondant. Supposez que la base de données ne contient pas encore de données.
a) La quantité commandée ne peut être supérieure à 5 pour les Articles dont le noArticle est supérieur à 10000.
b) Supposez quon ait ajouté à la table Commande une nouvelle colonne totalCommande. Le totalCommande doit être égal au total des montants de chacune des lignes de la commande. Le montant de chacune des lignes correspond à la quantité commandée multipliée par le prixUnitaire de l'Article. La modification des LigneCommandes et des prixUnitaire doit être interdite.
c) Le prixUnitaire d'un Article ne peut diminuer.
d) Supposez quon ait ajouté à la table Commande une nouvelle colonne totalCommande et à la table LigneCommande une nouvelle colonne totalLigne. Le totalCommande doit être égal au total des montants de chacune des lignes de la commande. Le totalLigne correspond à la quantité commandée multipliée par le prixUnitaire de l'Article.. La modification d'une LigneCommande est permise sauf lorsqu'il y a une ligne de DétailLivraison qui fait référence à LigneCommande. La modification du prixUnitaire est permise sous les mêmes conditions.
e) Pour la question précédente, concevez un TRIGGER qui modifie directement le totalLigne des LigneCommande ainsi que le totalCommande des Commande suite à la modification du prixUnitaire de l'Article.
f) Supposez quon ait ajouté à la table LigneCommande une nouvelle colonne quantitéEnAttente. La quantitéEnAttente d'une LigneCommande est égale à la quantité commandée moins le total des quantitéLivrées des DétailLivraison correspondant à la LigneCommande. La quantitéEnAttente doit être initialisée à la même valeur que la quantité commandée lors d'une insertion de LigneCommande. La quantitéEnStock doit être ajustée suite à l'insertion d'une ligne dans DétailLivraison.
Il est interdit d'insérer une ligne dans DétailLivraison si la quantitéEnStock de l'Article est insuffisante ou si la quantitéLivrée dépasse la quantitéEnAttente de la LigneCommande.
g) Il est interdit de supprimer une Commande s'il y a des LigneCommandes qui y font référence.
h) La dateLivraison ne peut précéder la dateCommande.
i) Une livraison ne touche toujours qu'un seul Client, c'est-à-dire ne peut être liée à des Commandes de plusieurs Clients.
Solutions des exercices du chapitre 6 selon la syntaxe du dialecte Oracle
1. a) Ajouter un CHECK sur la table LigneCommande
ALTER TABLE LigneCommande
ADD (CONSTRAINT XXX CHECK (noArticle qtéStock THEN
raise_application_error(-20100,
'quantité en stock insuffisante');
END IF;
LOCK TABLE DétailLivraison IN SHARE MODE;
SELECT SUM(quantitéLivrée) INTO qtéDéjàLivrée
FROM DétailLivraison
WHERE noArticle = :NEW.noArticle AND
noCommande = :NEW.noCommande;
SELECT quantité INTO qtéCommandée
FROM LigneCommande
WHERE noArticle = :NEW.noArticle AND
noCommande = :NEW.noCommande
FOR UPDATE;
IF :NEW.quantitéLivrée > qtéCommandée- qtéDéjàLivrée THEN
raise_application_error(-20101,
'quantité livrée supérieure à quantité en attente');
END IF;
END;
CREATE OR REPLACE TRIGGER ajuster_quantité_attente_et_stock
AFTER INSERT ON DÉTAILS_DE_LIVRAISON
FOR EACH ROW
DECLARE
BEGIN
UPDATE lignes_de_commande
SET
quantité_en_attente = quantité_en_attente - :NEW.quantité_livrée
WHERE
lignes_de_commande.commande_no_commande =
:NEW.lignecomm_commande_no_commande
AND lignes_de_commande.produit_no_produit =
:NEW.lignecomm_produit_no_produit ;
UPDATE produits
SET
quantité_en_stock = quantité_en_stock - :NEW.quantité_livrée
WHERE
no_produit = :NEW.lignecomm_produit_no_produit ;
END;
g) Clause ON DELETE RESTRICT de la contrainte d'intégrité référentielle associée à la clé étrangère commande_no_commande dans LIGNES_DE_COMMANDE
h)
CREATE ASSERTION assertionPrixMoyenMaximal CHECK
(SELECT *
FROM Commande, Livraison, LigneCommande ,DétailLivraison
WHERE Commande.dateCommande > Livraison.dateLivraison)
Permettre de modifier autre chose dans DétailLivraison
pour quantitéEnStock
requêtes
jointure externe à gauche = différence. Division vs ensembliste
équivalences diverses
Solution de 1 s) Selon la syntaxe Oracle
Exercice. Quantité commandée et quantité en attente pour chaque LigneCommande
SELECT L.noCommande, L.noArticle, quantité, quantité-NVL(SUM(quantitéLivrée),0) AS quantitéEnAttente
FROM LigneCommande L, DétailLivraison D
WHERE L.noArticle = D.noArticle (+) AND
L.noCommande = D.noCommande (+)
GROUP BY L.noCommande, L.noArticle, quantité
SELECT L.noCommande, L.noArticle, quantité, quantité- DECODE(SUM(quantitéLivrée),NULL,0,SUM(quantitéLivrée)) AS quantitéEnAttente
FROM LigneCommande L, DétailLivraison D
WHERE L.noArticle = D.noArticle (+) AND
L.noCommande = D.noCommande (+)
GROUP BY L.noCommande, L.noArticle, quantité
noCommandenoArticlequantitéquantitéEnAttente11010 0170 5 0190 1 0240 2 0295 3 2320 1 0440 1 0450 1 1510 5 5520 5 5570 3 16101515640 1 1750 1 1795 2 2820 3 3
