Université Tunis Carthage Année universitair2011/2012
Institut National des Sciences Appliquées et de Technologie Section : GL3
Module : Base de données avancées
Exercice 1:
On considère le schéma de base suivant ou les clés primaires sont soulignées, et les clés étrangères sont
précédées d’un #.
Ville ( noville , nomville , codepostal )
Train ( notrain , date_dep , date_arr , #noville_dep , #noville_arr )
Passager ( nopassager , Nom , #notrain , #date_dep )
Tarif ( #notrain , #date_dep , categorie , prix )
Distance ( #noville1 , #noville2 , nbkm )
Commentaires : noville_dep, noville_arr, noville1 et noville2 se réfèrent à noville de la table Ville.
Questions :
(a) Donner les commandes SQL d’Oracle pour créer les tables ci-dessus, en spécifiant les clés primaires et
étrangères, et en assurant que :
- Les prix de la table Tarif et les distances entre les villes soient des nombres positifs ;
- Pour un numéro de train, le numéro de la ville d’arrivée soit différent de celui la ville de départ.
CREATE TABLE Ville (
noville NUMBER PRIMARY KEY,
nomville VARCHAR2(30),
codepostal NUMBER
);
CREATE TABLE Train (
notrain NUMBER,
date_dep DATE,
date_arr DATE,
noville_dep NUMBER,
noville_arr NUMBER,
CONSTRAINT cTr_1 CHECK (noville_arr<>noville_dep),
CONSTRAINT cTr_2 PRIMARY KEY (notrain,date_dep),
CONSTRAINT cTr_3 FOREIGN KEY (noville_dep) REFERENCES Ville(noville),
CONSTRAINT cTr_4 FOREIGN KEY (noville_arr)REFERENCES Ville(noville));
CREATE TABLE Passager (
nopassager NUMBER PRIMARY KEY,
nom VARCHAR2(30),
notrain NUMBER,
CONSTRAINT cPass_1 FOREIGN KEY (notrain,date_dep)
REFERENCES Train(notrain,date_dep)
);
CREATE TABLE Tarif (
notrain NUMBER,
categorie NUMBER,
prix NUMBER CONSTRAINT cTar_1 CHECK (prix>=0),
CONSTRAINT cTar_2 PRIMARY KEY (notrain,categorie),
CONSTRAINT cTar_3 FOREIGN KEY (notrain,date_dep)
REFERENCES Train(notrain,date_dep)
);
Page 1 sur 4
CREATE TABLE Distance (
noville1 NUMBER,
noville2 NUMBER,
nbkm NUMBER CONSTRAINT cDist_1 CHECK (nbkm>=0),
CONSTRAINT cDist_2 PRIMARY KEY (noville1,noville2),
CONSTRAINT cDist_3 FOREIGN KEY (noville1)REFERENCES Ville(noville),
CONSTRAINT cDist_4 FOREIGN KEY (noville2)REFERENCES Ville(noville)
);
(b) Définir un trigger pour assurer qu’à l’insertion de données dans la table Train la différence entre la date
d’arrivée et la date de départ d’un train ne dépasse pas 8 heures si la distance entre la ville de départ et la ville
d’arrivée ne dépasse pas 300Km.
CREATE OR REPLACE TRIGGER trigger_temps
BEFORE INSERT ON Train
FOR EACH ROW
DECLARE Dist NUMBER;
BEGIN
SELECT DISTINCT nbkm INTO Dist
FROM Distance
WHERE noville1=:NEW.noville_dep AND noville2=:NEW.noville_arr;
IF (Dist<300 AND NEW.date_arr-NEW.date_dep>=8/24)
THEN
:NEW.date_arr := :NEW.date_dep + 8/24;
END IF;
END;
On peut aussi écrire le trigger en utilisant les exceptions :
CREATE OR REPLACE TRIGGER trigger_temps
BEFORE INSERT ON Train
FOR EACH ROW
DECLARE
Dist NUMBER;
Erreur EXCEPTION;
BEGIN
SELECT DISTINCT nbkm INTO Dist
FROM Distance
WHERE noville1=:NEW.noville_dep AND noville2=:NEW.noville_arr;
IF (Dist<300 AND NEW.date_arr-NEW.date_dep>=8/24)
THEN
RAISE Erreur;
END IF;
EXCEPTION
WHEN Erreur THEN
RAISE_APPLCATION_ERROR(-20322,’La duree du trajet est
superieure a 8h pour une distance entre les 2 villes inferieur
a 300km’);
END;
Page 2 sur 4
Exercice 2:
Considérer les schémas de relations suivants :
EMP ( N , S , E , P , Dp ) // N est un employé dans l’équipe E, il a un salaire mensuel S, et participe à
un projet P à partir de la date Dp.
CHEF ( C , E , P , Dc ) // L’employé C est le chef de l’équipe E, qui crée un projet P à la date Dc.
Sur les schémas EMP et CHEF, on considère les contraintes suivantes :
(i) Chaque équipe a un seul chef, et chaque chef est le chef d’une seule équipe. Chaque projet a une seule date
de création.
Chaque équipe a un seul chef : E → C
Chaque chef est le chef d’une seule équipe : C → E
Chaque projet a une seule date de création : P → Dc
(ii) Chaque employé a un seul salaire mensuel, et est dans une seule équipe. La date de début de participation
d’un employé à un projet est unique.
Chaque employé a un seul salaire mensuel et est dans une seule équipe : N → S,E
La date de début de participation d’un employé à un projet est unique : P,E → Dp
(iii) Une équipe qui figure dans la table EMP doit exister dans la table CHEF. Aussi un projet qui figure dans la
table EMP doit exister dans la table CHEF.
Questions :
(a) Donner les clés des schémas EMP et CHEF.
Schéma EMP :
- Clé primaire → N,P
- Clé étrangère → (E,P)
Schéma CHEF :
- Clé primaire → (E,P)
(b) En utilisant SQL d’Oracle et les contraintes clés primaires, uniques, et étrangères donner les commandes
pour créer les tables EMP et CHEF.
CREATE TABLE CHEF (
C VARCHAR2(30) UNIQUE NOT NULL,
E VARCHAR2(30) UNIQUE NOT NULL,
P VARCHAR2(30) UNIQUE NOT NULL,
Dc DATE,
CONSTRAINT cChef1 PRIMARY KEY (E,P)
);
CREATE TABLE EMP (
N VARCHAR2(30) UNIQUE NOT NULL,
S NUMBER,
E VARCHAR2(30),
P VARCHAR2(30),
Dp DATE
CONSTRAINT cEmp1 PRIMARY KEY (N,P)
CONSTRAINT cEmp2 FOREIGN KEY (E,P) REFERENCES CHEF (E,P)
);
Page 3 sur 4
(c) En plus des contraintes ci-dessus, on doit assurer que la date de participation d’un employé à un projet doit
être ultérieure ou égale à la date de la création du projet. Définir un trigger pour renforcer cette contrainte.
CREATE OR REPLACE TRIGGER Date_Participation
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
SELECT DISTINCT Dc INTO :[Link]
FROM CHEF
WHERE E=:NEW.E AND P=:NEW.P AND Dc>:[Link];
END;
On peut aussi écrire le trigger en utilisant les exceptions :
CREATE OR REPLACE TRIGGER Date_Participation
BEFORE INSERT ON EMP
FOR EACH ROW
DECLARE
Date_Creation NUMBER;
Erreur EXCEPTION;
BEGIN
SELECT DISTINCT Dc INTO Date_Creation
FROM CHEF
WHERE E=:NEW.E AND P=:NEW.P AND Dc>:[Link];
IF (Date_Creation>:[Link])
THEN
RAISE Erreur;
END IF;
EXCEPTION
WHEN Erreur THEN
RAISE_APPLCATION_ERROR(-20322,’La date de participation ne
peut pas être anterieure à la date de création’);
END;
Exercice 3:
Le schéma relationnel de la base de données « requests » est donné ci-dessous.
Facility (facNo, facName)
Customer (custNo, custName, custContact, custPhone, custAddress, custCity, custState, custZip)
EventRequest (eventNo, facNo#, custNo#, dateHeld, dateReq, dateAuth, status, estCost, estAudience)
1. Écrire une procédure stockée de nom custProfile qui affiche toutes les caractéristiques du client dont le
numéro (custNo) est passé en paramètre de la procédure.
2. Ajouter à la procédure custProfile une exception si le numéro de client n’existe pas dans la table
Customer.
-- 1 + 2
CREATE OR REPLACE PROCEDURE custProfile(num VARCHAR) IS
custTuple Customer%ROWTYPE; noData EXCEPTION;
n INTEGER;
BEGIN
-- Test d'existence des données
Page 4 sur 4
SELECT COUNT(*) INTO n FROM Customer WHERE custNo = num;
IF n = 0 THEN
RAISE noData;
END IF;
-- Affichage des données
SELECT * INTO custTuple FROM Customer WHERE custNo = num;
DBMS_OUTPUT.PUT_LINE('custNo ' || [Link]);
DBMS_OUTPUT.PUT_LINE('custName ' || [Link]);
DBMS_OUTPUT.PUT_LINE('custContact ' || [Link]);
DBMS_OUTPUT.PUT_LINE('custPhone ' || [Link]);
DBMS_OUTPUT.PUT_LINE('custAddress ' || [Link]);
DBMS_OUTPUT.PUT_LINE('custCity ' || [Link]);
DBMS_OUTPUT.PUT_LINE('custState ' || [Link]);
DBMS_OUTPUT.PUT_LINE('custZip ' || [Link]);
EXCEPTION
WHEN NoData THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid custNo');
END;
3. Écrire un bloc PL/SQL anonyme qui affiche les informations (eventNo, facName, custName, estCost, status)
triées par coût estimé (estCost) décroissant.
DECLARE
CURSOR eventList IS
SELECT eventNo, facName, custName, estCost, status FROM EventRequest E, Facility F,
Customer C
WHERE [Link] = F. facNo AND [Link] = [Link]
ORDER BY estCost DESC;
eventTuple eventList%ROWTYPE;
BEGIN
FOR eventTuple IN eventList LOOP
DBMS_OUTPUT.PUT_LINE([Link] || ', ' || [Link] || ', ' ||
[Link] || ', ' || [Link] || ', ' || [Link]);
END LOOP;
END;
4. Écrire un déclencheur de nom eventCheck qui vérifie que, pour chaque nouvelle demande (EventRequest) ou
modification de demande, la date d’autorisation dateAuth existe. Si ce n’est pas le cas, interrompre l’exécution avec
le message « Erreur d’autorisation ». Sinon, vérifier que la date d’autorisation dateAuth est supérieure ou égale à la
date de demande dateReq et que la date tenue dateHeld est supérieure ou égale à la date d’autorisation dateAuth. Si
ce n’est pas le cas, interrompre l’exécution avec le message « Erreur de date ».
CREATE OR REPLACE TRIGGER eventCheck
BEFORE INSERT OR UPDATE ON EventRequest
FOR EACH ROW
DECLARE
authError EXCEPTION; dateError EXCEPTION;
BEGIN
IF :[Link] IS NULL THEN
RAISE authError;
ELSE
IF :[Link] < :[Link] OR :[Link] < :[Link]
THEN RAISE dateError;
END IF;
END IF;
EXCEPTION
Page 5 sur 4
WHEN authError THEN
RAISE_APPLICATION_ERROR(-20001, 'Erreur d''autorisation');
WHEN dateError THEN
RAISE_APPLICATION_ERROR(-20002, 'Erreur de date');
END;
Page 6 sur 4
