INF 159 : Bases de données Session 2, Année 2010/2011
P o s t g r e S Q L e x i g e u n e f o n c t i o n p o u r l e s c o n t r a i n t e s d ‘ i n t e g r i t e a v e c SELECT
CREATE FUNCTION B i b l i o t h e q u e . D a t e s E m p r u n t R e t o u r C o r r e c t e s ( i n t e g e r,d a t e ,d a t e )
RETURNS b o o l e a n AS$ $
SELECT NOT EXISTS (
SELECT
FROM B i b l i o t h e q u e . E m p r u n t s
WHERE ( $ 1 = L i v r e AND$ 2 = D a t e E m p r u n t ) )
$ $ LANGUAGE SQL ;
C o n t r a i n t e d ‘ i n t e g r i t e s t a t i q u e
ALTER TABLE B i b l i o t h e q u e . E m p r u n t s ADD CONSTRAINTD a t e s E m p r u n t R e t o u r P o s s i b l e s
CHECK ( B i b l i o t h e q u e . D a t e s E m p r u n t R e t o u r C o r r e c t e s ( L i v r e , D a t e E m p r u n t , D a t e R e t o u r )= TRUE) ;
Question 1.3 (1 point) Pour la relationEmprunts, donnez les seules dépendances fonctionnel les déclarées,
expliquez le rôle des clefs étrangères, puis décrivez l’objectif des deux contraintes d’intégrité.
Réponse : (Livre; DateE mprunt )! (DateRetour; Adherent )
( Livre; DateRetour )! (DateE mprunt; Adherent )
Adherent doit exister dans la table Adherents
Livre doit exister dans la table Livres
La date d’emprunt doit être antérieure à la date de retour.
Un livre ne peut pas être emprunté par deux adhérents en même temps.
Deux contraintes d’intégrité inter relations sont ajoutées au schéma. PostGreSQL ne permet pas d’attacher
des contraintes aux schémas. Ces contraintes sont donc ajoutées à la relation Empruntscar el les doivent être
vériées lors de chaque nouvel emprunt.
P o s t g r e S Q L e x i g e u n e f o n c t i o n p o u r l e s c o n t r a i n t e s d ‘ i n t e g r i t e a v e c SELECT
C o n t r a i n t e d ‘ i n t e g r i t e i n t e r r e l a t i o n s
CREATE FUNCTION B i b l i o t h e q u e . D a t e s E m p r u n t A c h a t C o r r e c t e s ( i n t e g e r,d a t e )
RETURNS b o o l e a n AS$ $
SELECT EXISTS (
SELECT
FROM B i b l i o t h e q u e . L i v r e s
WHERE ( $ 1 = I d AND$ 2 > D a t e A c h a t ) )
$ $ LANGUAGE SQL ;
C o n t r a i n t e d ‘ i n t e g r i t e i n t e r r e l a t i o n s s t a t i q u e
ALTER TABLE B i b l i o t h e q u e . E m p r u n t s ADD CONSTRAINTD a t e s E m p r u n t A c h a t P o s s i b l e s
CHECK ( B i b l i o t h e q u e . D a t e s E m p r u n t A c h a t C o r r e c t e s ( L i v r e , D a t e E m p r u n t ) = TRUE) ;
P o s t g r e S Q L e x i g e u n e f o n c t i o n p o u r l e s c o n t r a i n t e s d ‘ i n t e g r i t e a v e c SELECT
C o n t r a i n t e d ‘ i n t e g r i t e i n t e r r e l a t i o n s
CREATE FUNCTION B i b l i o t h e q u e . N o m b r e E m p r u n t s C o r r e c t ( i n t e g e r,d a t e )
RETURNS b o o l e a n AS$ $
SELECT NOT EXISTS (
SELECT A d h e r e n t
FROM B i b l i o t h e q u e . E m p r u n t s , B i b l i o t h e q u e . A d h e r e n t s
WHERE ( $ 1 = A d h e r e n t AND$ 1 = I d )
AND ( $ 2 >= D a t e E m p r u n t AND$ 2 = N b E m p r u n t s A u t o r i s e s ) )
$ $ LANGUAGE SQL ;
A j o u t d e l a c o n t r a i n t e p a r a p p e l d e l a f o n c t i o n
ALTER TABLE B i b l i o t h e q u e . E m p r u n t s ADD CONSTRAINTN o m b r e E m p r u n t s P o s s i b l e
CHECK ( B i b l i o t h e q u e . N o m b r e E m p r u n t s C o r r e c t ( A d h e r e n t , D a t e E m p r u n t ) = TRUE) ;
Question 1.4 (1 point) Décrivez les objectifs de ces deux contraintes d’intégrité.
Réponse : 1. DatesEmpruntAchat interdit le prêt d’un livre avant qu’il ne soit acheté.
3/ 9