Q1/ Principales contraintes d'intégrité
associées à ce schéma (en français) :
1- Contraintes statiques :
1-a- Contraintes mono-relation :
On a déjà toutes les contraintes de clé
données dans le sujet (nospectacle pour SPECTACLES, ...).
-
sur SPECTACLES :
- le nom d'un spectacle est non nul,
- la durée d'un spectacle est comprise entre 1h et 4h
- le type d'un spectacle est soit théâtre, soit danse, soit
concert, ...
-
sur SALLES :
- la capacité d'une salle est comprise entre 100 et 500 places
-
sur REPRESENTATIONS
- le prix d'une représentation est compris entre 50 et 300Fr
-
sur BILLETS :
- le nom du client est non nul
On pourrait également avoir des contraintes de type dépendance
fonctionnelle, comme par exemple que toutes les représentations d'un
même spectacle sont au même prix.
1-b- Contraintes multi-relations :
On a toutes les contraintes d'intégrité
référentielle indiquées sur le schéma :
-
entre REPRESENTATIONS et SPECTACLES (avec un cascade delete; si on supprime
un spectacle on doit supprimer toutes les représentations de ce
spectacle), - entre REPRESENTATIONS et SALLES,
-
entre BILLETS et REPRESENTATIONS (avec un cascade delete; si on supprime une
représentation on doit supprimer tous les billets de cette
représentation).
On a une contrainte ensembliste qui est que le nombre de billets émis
pour une représentation dans une salle donnée doit être
inférieur ou égal à la capacité de cette salle.
2- Contraintes dynamiques :
On peut citer par exemple le fait que le nombre de places d'une salle ne
peut pas diminuer (sinon on va peut être se retrouver avec plus de
billets que de capacité). De même si une représentation
change de salle, il faut revérifier le nombre de billets émis.
Q2/ Définition du schéma en SQL (version Oracle)
Les contraintes dynamiques et une contrainte ensembliste comme la
vérification du nombre de billets émis ne peuvent s'exprimer en
SQL2 et doivent être prises en compte soit par le biais de triggers, soit
dans le code des programmes d'applications.
Q3-1/
ADMINISTRATEUR
Il est le créateur et donc le propriétaire de toutes les
relations. Il va créer également les vues qui seront
utilisées par les autres utilisateurs.
GUICHETIER
Il faut qu'il puisse créer des billets, donc il doit voir et
mettre à jour la table BILLETS.
Pour sa consultation, le plus simple est de créer une vue qui permettra
d'accéder aux relations SPECTACLES, REPRESENTATION ainsi qu'au nombre
de places disponibles :
CREATE VIEW GUICHET(nospectacle, nosalle, dater, nom, duree, type,
placelibre) AS
SELECT SP.nospectacle, S.nosalle, R.dater, SP.nom, SP.duree, SP.type,
S.nbplaces - count(*)
FROM SPECTACLES SP, SALLES S, REPRESENTATIONS R, BILLETS B
WHERE SP.nospectacle=R.nospectacle AND S.nosalle=R.nosalle
AND R.nosalle=B.nosalle AND R.dater=B.dater
GROUP BY SP.nospectacle, S.nosalle, R.dater, SP.nom, SP.duree, SP.type, S.nbplaces
CLIENTS
L'accès à la vue GUICHET permet aux clients de choisir
leurs soirées en fonction de critères variés.
Q3-2/
ADMINISTRATEUR
Il a tous les droits sur toutes les relations et vues. Il faut noter que
la vue GUICHET ne peut être mise à jour (elle est définie
par jointure). Il crée les utilisateurs "guichetier" et "client" :
GRANT CONNECT TO guichetier IDENTIFIED BY toto;
GRANT CONNECT TO client IDENTIFIED BY titi;
GUICHETIER
Il a les droits de consultation et de modification (insertion,
suppression et mise à jour) sur BILLETS. Il a le droit de consultation
sur la vue GUICHET.
CLIENTS
Il a le droit de consultation sur la vue GUICHET.
L'administrateur doit donc créer les droits suivant :
GRANT ALL ON BILLETS TO guichetier;
GRANT SELECT ON GUICHET TO guichetier;
GRANT SELECT ON GUICHET TO client;
Posté le 14 septembre 2009