Comment faire respecter les contraintes d’intégrité en SQL?

1) Pour chacun des cas qui suit, déterminer comment faire respecter les contraintes d’intégrité en SQL pour le schéma suivant de l'application des ventes pour la pépinière PleinDeFoin (voir volume 1). Donnez le code SQL correspondant. Supposez que la base de données ne contient pas encore de données. Répondez à chacune des questions de manière indépendante des autres à moins d'avis contraire.

Schéma SQL VentesPleinDeFoin :

CREATE TABLE Client

(noClient INTEGER NOT NULL,

nomClient VARCHAR(20) NOT NULL,

noTéléphone VARCHAR(15) NOT NULL,

PRIMARY KEY (noClient)

)

CREATE TABLE Article

(noArticle INTEGER NOT NULL,

description VARCHAR(20),

prixUnitaire DECIMAL(10,2) NOT NULL,

quantitéEnStock INTEGER DEFAULT 0 NOT NULL

CHECK (quantitéEnStock >= 0),

PRIMARY KEY (noArticle))

CREATE TABLE Commande

(noCommande INTEGER NOT NULL,

dateCommande DATE NOT NULL,

noClient INTEGER NOT NULL,

PRIMARY KEY (noCommande),

FOREIGN KEY (noClient) REFERENCES Client

)

CREATE TABLE LigneCommande

(noCommande INTEGER NOT NULL,

noArticle INTEGER NOT NULL,

quantité INTEGER NOT NULL

CHECK (quantité > 0),

PRIMARY KEY (noCommande, noArticle),

FOREIGN KEY (noCommande) REFERENCES Commande,

FOREIGN KEY (noArticle) REFERENCES Article

)

CREATE TABLE Livraison

(noLivraison INTEGER NOT NULL,

dateLivraison DATE NOT NULL,

PRIMARY KEY (noLivraison)

)

CREATE TABLE DétailLivraison

(noLivraison INTEGER NOT NULL,

noCommande INTEGER NOT NULL,

noArticle INTEGER NOT NULL,

quantitéLivrée INTEGER NOT NULL

CHECK (quantitéLivrée > 0),

PRIMARY KEY (noLivraison, noCommande, noArticle),

FOREIGN KEY (noLivraison) REFERENCES Livraison,

FOREIGN KEY (noCommande, noArticle) REFERENCES LigneCommande

)

a) La quantité commandée ne peut être supérieure à 5 pour les Articles dont le noArticle est supérieur à 10000.

Ajouter un CHECK sur la table LigneCommande :

ALTER TABLE LigneCommande

ADD (CONSTRAINT XXX CHECK (noArticle <= 10000 OR quantité <= 5))

b) Lorsqu'une augmentation du prixUnitaire d'un Article est tentée, il faut limiter l'augmentation à 10% du prix en cours.

CREATE OR REPLACE TRIGGER BUArticleBornerAugPrix

BEFORE UPDATE OF prixUnitaire ON Article

REFERENCING

OLD AS ligneAvant

NEW AS ligneAprès

FOR EACH ROW

WHEN (ligneAprès.prixUnitaire > ligneAvant.prixUnitaire*1.1)

BEGIN

:ligneAprès.prixUnitaire := :ligneAvant.prixUnitaire*1.1;

END;

c) Lors d'une nouvelle livraison, la quantité à livrer ne peut dépasser la quantité en stock disponible.

CREATE OR REPLACE TRIGGER BIDétLivVérifierStock

BEFORE INSERT ON DétailLivraison

REFERENCING

NEW AS ligneAprès

FOR EACH ROW

DECLARE

laQuantitéEnStock INTEGER;

-- N.B. Oracle ne supporte pas de SELECT dans le WHEN

-- Il faut donc utiliser un IF PL/SQL

BEGIN

SELECT quantitéEnStock

INTO laQuantitéEnStock

FROM Article

WHERE noArticle = :ligneAprès.noArticle

FOR UPDATE;

IF :ligneAprès.quantitéLivrée > laQuantitéEnStock THEN

raise_application_error(-20100, 'stock disponible insuffisant');

END IF;

END;

d) Ne permettre que la modification de la quantitéLivrée dans la table DétailLivraison

CREATE OR REPLACE TRIGGER BUDétLivEmpêcherModif

BEFORE UPDATE OF noLivraison, noCommande, noArticle

ON DétailLivraison

BEGIN

raise_application_error(-20101, 'Cette modification est interdite dans DétailLivraison');

END;

e) Ajuster la quantitéEnStock dans le cas de l'insertion d'une nouvelle ligne dans DétailLivraison.

CREATE OR REPLACE TRIGGER AIDétLivAjusterStock

AFTER

...