Question - Université de Genève
publicité
©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD UNIVERSITE DE GENEVE FACULTE DES SCIENCES ECONOMIQUES ET SOCIALES Département de Systèmes d'Information TP FINAL LA BIBLIOTHEQUE Part II CORRIGE Date à rendu : le lundi 10 juin 02 Modalité de rendu : par courrier électronique sous forme nom_de_login.TPfinal2.doc (ex. BDD2002_12.TPfinal2.doc) Adresse : [email protected] TP final Corrigé 1 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD La bibliothèque I. La modélisation relationnelle suivante représente la structure statique de la base de données de la bibliothèque. ADHERENT(noAdh// nom, prénom, adresse, dateAdh) CATALOGUE(titre // nomAut, prénomAut, anEd) FONDS(cote // titre) RESERVATION(titre, noAdh, dateRes // dateEmp) EMPRUNT(cote, dateEmp // noAdh, dateRet) II. Les règles d’intégrité : 1. Tous les attributs d’un adhérent ne peuvent prendre des valeurs inconnues. 2. Tous les attributs d’un catalogue ne peuvent prendre des valeurs inconnues. L’an d’édition doit après 1950. 3. Tous les attributs d’un fonds ne peuvent prendre des valeurs inconnues. 4. Toute réservation, la date de réservation doit être précédente de celle d’emprunt. 5. Tout emprunt, la date d’emprunt doit être précédente de celle de retour. 6. On ne peut réserver ou emprunter un titre qu’après avoir adhéré à la bibliothèque. (La date d’adhérent est précédente de celle de réservation ou d’emprunt) 7. Un emprunt ne doit pas dépasser un mois. 8. Un adhérent ne peut emprunter qu’au plus 5 titres à la fois et un seul exemplaire pour chaque titre. 9. Lorsqu’un emprunt est fait à partir d’une réservation, on enregistre la date d’emprunt à la réservation. (La date d’emprunt dans une réservation est claire, alors il existe un emprunt tel que la cote est un exemplaire du titre réservé et l’emprunteur est l’adhérent qui réserve ce titre) Question Part I. =================== Création des tables ======================= CREATE TABLE adherent ( noadh CHAR(5) PRIMARY KEY, nom CHAR(20) NOT NULL, prenom CHAR(30) NOT NULL, adresseVARCHAR(50) NOT NULL, dateadh DATE NOT NULL ); CREATE TABLE catalogue ( titre CHAR(30) PRIMARY KEY, nom CHAR(20) NOT NULL, prenom CHAR(30) NOT NULL, anEd NUMBER(4) NOT NULL CONSTRAINT chk_anEd CHECK (anEd>=1950) ); TP final Corrigé 2 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD CREATE TABLE fonds ( cote CHAR(10) PRIMARY KEY, titre CHAR(30) NOT NULL REFERENCES catalogue ); CREATE TABLE emprunt ( cote CHAR(10), dateemp DATE, noadh CHAR(5) NOT NULL, dateret DATE, CONSTRAINT pk_emprunt PRIMARY KEY(cote, dateemp), CONSTRAINT fk_adherent FOREIGN KEY (noadh) REFERENCES adherent, CONSTRAINT fk_fonds FOREIGN KEY(cote) REFERENCES fonds, CONSTRAINT chk_dateemp CHECK(dateemp<=dateret), CONSTRAINT chk_dateret CHECK(dateret<=add_months(dateemp,1)) ); CREATE TABLE reservation( titre CHAR(30) REFERENCES catalogue, noadh CHAR(5) REFERENCES adherent, dateres DATE, dateemp DATE, CONSTRAINT pk_reservation PRIMARY KEY (titre, noadh, dateres), CONSTRAINT chk_dateres CHECK (dateres <= dateemp) ); ============= INSERTION DES DONNEES ======================= insert into ADHERENT values ('A001','RENAULT','Robert','Lausanne','08/04/01'); insert into ADHERENT values ('A002','VOLVO','Robert','Geneve','01/01/02'); insert into ADHERENT values ('A003','AQUA','Robert','Sion','25/06/01'); insert into CATALOGUE values ('Base de donnees','ADIBA','Michel',1995); insert into CATALOGUE values ('Base des objets','COLLAND','Rollet',1990); insert into CATALOGUE values ('OO DMBS','ADIBA','Michel',1998); insert into CATALOGUE values ('ORACLE SQL','LINDEN','Brian',2000); insert into CATALOGUE values ('SQL*Plus reference','LINDEN','Brian',2001); insert into CATALOGUE values ('Web Database','BUYENS','Jim',2000); insert into FONDS values('BDD_01','Base de donnees'); insert into FONDS values('BDD_02','Base de donnees'); insert into FONDS values('BD0_01','Base des objets'); insert into FONDS values('OOMS_01','OO DMBS'); insert into FONDS values('OOMS_02','OO DMBS'); insert into FONDS values('ORA.SQL_01','ORACLE SQL'); insert into FONDS values('SQLP_01','SQL*Plus reference'); insert into FONDS values('WDB_00','Web Database'); TP final Corrigé 3 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD insert into RESERVATION values('Base de donnees','A001','09/04/01',NULL); insert into RESERVATION values('Base de donnees','A002','10/01/02', NULL); insert into RESERVATION values('OO DMBS','A002','02/01/02',NULL); insert into EMPRUNT values('BDD_02','21/04/01','A001', '03/05/01'); insert into EMPRUNT values('BDD_01','01/06/02','A001', NULL); insert into EMPRUNT values('BD0_01','03/06/02','A001', NULL); insert into EMPRUNT values('OOMS_02','03/06/02','A001', NULL); insert into EMPRUNT values('ORA.SQL_01','01/06/02','A001', NULL); insert into EMPRUNT values('SQLP_01','01/06/02','A001', NULL); insert into EMPRUNT values('OOMS_02','05/06/02','A002', NULL); insert into EMPRUNT values('OOMS_01','05/01/02','A002', '10/01/02'); update RESERVATION set dateEmp='21/04/01' where titre='Base de donnees' and noAdh='A001' and dateRes='09/04/01'; update RESERVATION set dateEmp='05/01/02' where titre='OO DMBS' and noAdh='A002' and dateRes='02/01/02'; TP final Corrigé 4 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD Part II. RI6 : Inter-relations : ADHERENT, RESERVATION, EMPRUNT ADHERENT : i. Modifier la date d’adhésion (dateAdh) : Refuser la modification et afficher une erreur s’il existe déjà des réservations ou des emprunts de cet adhérent dont la date est antérieure à la nouvelle date. RESERVATION : i. Créer une réservation : Si la date de réservation (dateRes) est antérieure la date d’adhésion (dateAdh), affecter dateAdh à dateRes. EMPRUNT i. Créer un emprunt : Si la date d’emprunt (dateEmp) est antérieure la date d’adhésion (dateAdh), affecter dateAdh à dateRes. ii. Modifier le numéro d’adhérent : Refuser la modification et afficher une erreur si le nouveau numéro d’adhérent ne satisfait pas la règle. RI8 : RI8.1. Un adhérent ne peut emprunter qu’au plus 5 exemplaires à la fois Intra-relation (EMPRUNT), Ensembliste EMPRUNT : i. Créer un emprunt : Refuser cette insertion et afficher une erreur si le nombre des emprunts qui ne sont pas encore rendus est déjà 5. ii. Modifier la date de retour : Refuser la modification et afficher une erreur si elle transgresse la règle. RI8.2. un seul exemplaire pour chaque titre Inter-relations (EMPRUNT, FONDS) EMPRUNT : i. Créer un emprunt : Refuser cette insertion et afficher une erreur si cet adhérent a emprunté (pas encore rendu) un exemplaire du même titre. ii. Modifier la date de retour : Refuser la modification et afficher une erreur si elle transgresse la règle. FONDS : i. Modifier le titre : Refuser la modification et afficher une erreur s’il existe déjà des emprunts de cet exemplaire. RI9 : Inter-relations (FONDS, RESERVATION, EMPRUNT) FONDS : i. Modifier le titre : Refuser la modification et afficher une erreur s’il existe déjà des emprunts de cet exemplaire. ii. Supprimer un fonds : Refuser la suppression. (Cette opération est déjà vérifiée grâce à la clé étrangère) RESERVATION : i. Créer une réservation : la date d’emprunt doit être inconnue lorsqu’une nouvelle réservation est faite (insérée). ii. Modification la date d’emprunt : refuser la modification et afficher une erreur si elle transgresse la règle. EMPRUNT : i. Modifier l’adhérent : Refuser la modification et afficher une erreur si elle transgresse la règle. ii. Supprimer un emprunt : Si cet emprunt concerne une réservation, refuser la suppression et afficher une erreur. TP final Corrigé 5 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD 1. Tableau de portée global Table ADHERENT Opération RI6 MAJ dateAdh FONDS MAJ RESERVATION INS MAJ EMPRUNT INS MAJ SUP RI8.1 RI8.2 titre + + noAdh RI9 titre + dateEmp + dateRet + dateRet noAdh + 2. Implémentation des RI 2.1. ADHERENT CREATE OR REPLACE TRIGGER check_dateAdh AFTER UPDATE OF dateAdh ON ADHERENT FOR EACH ROW WHEN (NEW.dateAdh > OLD.dateAdh) DECLARE cnt_Emp NUMBER(3); cnt_Res NUMBER(3); BEGIN /* Vérifier si la nouvelle date d'adhésion est postérieure à la date des emprunts*/ SELECT COUNT(*) INTO cnt_Emp FROM EMPRUNT WHERE noAdh = :new.noAdh and dateEmp<=:new.dateAdh; /* Si oui, refuser la MAJ. RI6. */ IF (cnt_Emp>0) THEN RAISE_APPLICATION_ERROR(-20050,'MAJ de date d''adhésion est non-valide'); END IF; /* Vérifier si la nouvelle date d'adhésion est postérieure à la date des réservations*/ SELECT COUNT(*) INTO cnt_Res FROM RESERVATION WHERE noAdh = :NEW.noAdh and dateRes<=:NEW.dateADh; /* Si oui, refuser la MAJ. RI6 */ IF (cnt_Res>0) THEN RAISE_APPLICATION_ERROR(-20050,'MAJ de date d''adhésion est non-valide'); END IF; END; TP final Corrigé 6 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD 2.2. FONDS CREATE OR REPLACE TRIGGER maj_fonds AFTER UPDATE OF titre ON fonds FOR EACH ROW DECLARE nb_emp NUMBER(5); BEGIN /* Vérifier si ce fonds actuel est déjà emprunté */ SELECT COUNT(*) INTO nb_emp FROM emprunt WHERE cote = :OLD.cote; /* Si oui, réfuser la MAJ. RI9 et RI8.2 */ IF nb_emp>0 THEN RAISE_APPLICATION_ERROR(-20052,'Ce fonds a des emprunts. La MAJ est interdite'); END IF; END; 2.3. RESERVATION CREATE OR REPLACE TRIGGER cre_reservation BEFORE INSERT ON reservation FOR EACH ROW DECLARE date_Adh ADHERENT.DATEADH%TYPE; BEGIN /* Chercher la date d'adhésion de la personne qui fait une nouvelle réservation.*/ SELECT dateAdh INTO date_Adh FROM adherent WHERE noAdh = :NEW.noAdh; /* Si la date d'adhésion est postérieure à celle de réservation, affecter la date d'adhésion actuelle à la date de réservation. RI6*/ IF date_Adh>:NEW.dateRes THEN :NEW.dateRes := date_Adh; END IF; /* Si la date d'emprunt de la nouvelle réservation est connue (not null), affecter la valeur inconnue à la place. RI9 */ IF (:NEW.dateEmp is NOT NULL) THEN :NEW.dateEmp := NULL; END IF; END; TP final Corrigé 7 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD CREATE OR REPLACE TRIGGER maj_reservation AFTER UPDATE OF dateEmp ON reservation FOR EACH ROW WHEN (NEW.dateEmp IS NOT NULL) DECLARE nb_emp NUMBER(1); BEGIN /* Vérifier s'il existe un emprunt correspondant à la "nouvelle" réservation*/ SELECT COUNT(*) INTO nb_emp FROM emprunt, fonds WHERE emprunt.cote=fonds.cote AND noAdh=:NEW.noAdh AND titre=:NEW.titre AND dateEmp=:NEW.dateEmp; /* Si non, réfuser la MAJ. RI9 */ IF nb_emp=0 THEN RAISE_APPLICATION_ERROR(-20054,'Il n''y pas d''emprunt correspondant à cette réseravation'); END IF; END; 2.4. EMPRUNT CREATE OR REPLACE TRIGGER avant_cre_emprunt BEFORE INSERT ON emprunt FOR EACH ROW DECLARE dat_Adh ADHERENT.DATEADH%TYPE; BEGIN /* Chercher la date d'adhésion de la personne qui fait le nouvel emprunt.*/ SELECT dateAdh INTO dat_Adh FROM adherent WHERE noAdh = :new.noAdh; /* Si la date d'adhésion est postérieure à celle d'emprunt, affecter la date d'adhésion actuelle à la place. RI6 */ IF dat_Adh>:new.dateEmp THEN :new.dateEmp := dat_Adh; END IF; END; TP final Corrigé 8 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD CREATE OR REPLACE TRIGGER cre_emprunt AFTER INSERT OR UPDATE OF dateRet ON EMPRUNT DECLARE cntdat_Adh number; maxnb_Emp number; maxnb_Exemplaire number; BEGIN /* Chercher la quantité maximale des livres empruntés par l'adhérent */ SELECT MAX(COUNT(*)) INTO maxnb_Emp FROM emprunt WHERE dateRet is null GROUP BY noAdh; /* S'il existe un adhérent qui emprunte plus 5 livres, la RI8.1 est trangressée, l'insertion sera interdite */ IF maxnb_Emp > 5 THEN RAISE_APPLICATION_ERROR(-20056,'Cet adhérent a déjà emprunté 5 livres'); END IF; /* Chercher la quantité maximale des exemplaires des oeuvres empruntés par l'adhérent */ SELECT MAX(COUNT(*)) INTO maxnb_Exemplaire FROM emprunt, fonds WHERE (emprunt.cote=fonds.cote) and (dateRet is null) GROUP BY noAdh, titre; /* S'il existe un titre qui est emprunté deux fois, l'insertion est interdite. RI8.2 */ IF maxnb_Exemplaire > 1 THEN RAISE_APPLICATION_ERROR(-20057,'Ce titre est déjà emprunté'); END IF; END; CREATE OR REPLACE TRIGGER sup_emprunt AFTER DELETE ON emprunt FOR EACH ROW DECLARE v_titre FONDS.TITRE%TYPE; nb_res NUMBER(1); BEGIN /* Chercher le titre de l'emprunt supprimé */ SELECT titre INTO v_titre FROM fonds WHERE cote = :OLD.cote; /* Vérifier si cet emprunt concerne à une réservation */ SELECT COUNT(*) INTO nb_res FROM reservation WHERE titre=v_titre AND noAdh=:OLD.noAdh AND dateEmp=:OLD.dateEmp; /* Si oui, interdire la MAJ. RI9 */ IF nb_res>0 THEN RAISE_APPLICATION_ERROR(-20058,'Cet emprunt concerne à une réservation. La suppression est interdite'); END IF; END; TP final Corrigé 9 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD CREATE OR REPLACE TRIGGER maj_noAdh_emprunt AFTER UPDATE OF noAdh ON emprunt FOR EACH ROW DECLARE date_Adh ADHERENT.DATEADH%TYPE; nb_res NUMBER(1); v_titre FONDS.TITRE%TYPE; BEGIN /* Chercher la date d'adhésion de la personne qui fait le "nouvel" emprunt.*/ SELECT dateAdh INTO date_Adh FROM adherent WHERE noAdh = :new.noAdh; /* Si la date d'adhésion est postérieure à celle d'emprunt, réfuser la MAJ. RI6 */ IF date_Adh>:new.dateEmp THEN RAISE_APPLICATION_ERROR(-20051,'MAJ du numéro d''adhérent dans l''emprunt est non-valide'); END IF; /* Chercher le titre de l'emprunt actuel (ancien) */ SELECT titre INTO v_titre FROM fonds WHERE cote = :OLD.cote; /* Vérifier si l'emprunt actuel concerne à une réservation */ SELECT COUNT(*) INTO nb_res FROM reservation WHERE titre=v_titre AND noAdh=:OLD.noAdh AND dateEmp=:OLD.dateEmp; /* Si oui, interdire la MAJ. RI9 */ IF nb_res>0 THEN RAISE_APPLICATION_ERROR(-20055,'Cet emprunt concerne à une réservation. La MAJ est interdite'); END IF; END; 3. Tester les triggers RI.2. INSERT INTO catalogue VALUES ('Constraint database','REVESZ','Peter', 1949); RI.4. INSERT INTO reservation VALUES('SQL*Plus reference','A002','28/06/01','26/06/01'); RI.5. INSERT INTO emprunt VALUES('SQLP_01','27/05/02','A003','20/05/02'); RI.6. ADHERENT : maj dateAdh UPDATE adherent SET dateAdh='10/04/01' WHERE noAdh ='A001'; RESERVATION : créer INSERT INTO reservation VALUES('SQL*Plus reference','A001','08/03/01',NULL); EMPRUNT : créer INSERT INTO emprunt VALUES('SQLP_01','24/06/01','A003',NULL); EMPRUNT : maj noAdh UPDATE emprunt SET noAdh='A003' WHERE noAdh='A001' AND cote='BDD_02'; TP final Corrigé 10 ©M. Léonard, H. Luu Université de Genève - SES Été 2002 Bases de données - BDD RI.7. INSERT INTO emprunt VALUES('SQLP_01','27/04/02','A003','28/05/02'); RI.8.1. EMPRUNT : créer INSERT INTO emprunt VALUES('WDB_00','10/06/02', 'A001', NULL); EMPRUNT : maj dateRet UPDATE emprunt SET dateRet=NULL WHERE cote='BDD_02' AND noAdh='A001'; RI.8.2. EMPRUNT : créer INSERT INTO emprunt VALUES('OOMS_01','10/06/02', 'A002', NULL); EMPRUNT : maj dateRet UPDATE emprunt SET dateRet=NULL WHERE cote='OOMS_01' AND noAdh='A002'; FONDS : maj titre UPDATE fonds SET titre='Base de donnees 1' WHERE titre='Base de donnees'; RI.9. FONDS : maj titre UPDATE fonds SET titre='Base de donnees 1' WHERE titre='Base de donnees'; RESERVATION : créer INSERT INTO reservation VALUES('SQL*Plus reference','A002','28/06/01','30/06/01'); RESERVATION : maj dateEmp UPDATE reservation SET dateEmp='30/06/01' WHERE titre='SQL*Plus reference' AND noAdh='A002'; EMPRUNT : maj noAdh UPDATE emprunt SET noAdh='A003' WHERE cote='OOMS_01' AND dateEmp='05/01/02'; EMPRUNT : supprimer DELETE FROM emprunt WHERE cote='OOMS_01' AND dateEmp='05/01/02'; TP final Corrigé 11
