Création d'un schéma relationnel

2 Création d'un schéma relationnel

Il s'agit de définir un schéma de base de données, d'y intégrer des contraintes, des vues et des triggers, et d'y insérer quelques informations.

2.1 Création des tables

Créez les tables du schéma 'Agence de voyages', vues en cours, et rappelées ci-dessous.

STATION (nomStation, capacité, lieu, région, tarif)
ACTIVITE (nomStation, libellé, prix)
CLIENT (id, nom, prénom, ville, région, solde)
SEJOUR (id, station, début ¹ , nbPlaces)

Attention à bien définir les clés primaires et étrangères. Voici les autres contraintes portant sur ces tables.

Les données capacité, lieu, nom, ville, solde et nbPlaces doivent toujours être connues.
Les montants (prix, tarif et solde) ont une valeur par défaut à 0.
Il ne peut pas y avoir deux stations dans le même lieu et la même région.
Les régions autorisées sont : 'Ocean Indien', 'Antilles', 'Europe', 'Ameriques' et 'Extreme Orient'.
La destruction d'une station doit entraîner la destruction de ses activités et de ses séjours.

Conseil : donnez des noms à vos contraintes CHECK. Il est possible aussi de donner des noms aux contraintes FOREIGN KEY et PRIMARY KEY.

Solution :

NB: les NOT NULL ne sont pas necessaires pour les PRIMARY KEY.


CREATE TABLE Station (nomStation VARCHAR2 (30),
                      capacite   NUMBER (10) NOT NULL,
                      lieu       VARCHAR2(30) NOT NULL,
                      region     VARCHAR2 (30),
                      tarif      NUMBER (10,2) DEFAULT 0,
                      CONSTRAINT cle_station PRIMARY KEY (nomStation), 
                      CONSTRAINT cle_lieu_region UNIQUE (lieu, region),
                      CONSTRAINT nom_region
                         CHECK (region IN ('Ocean Indien', 
                                        'Antilles', 'Europe',
                                        'Ameriques', 'Extreme Orient'))
                    );

CREATE TABLE Activite (nomStation   VARCHAR2 (30),
                       libelle      VARCHAR2(30),
                       prix         NUMBER (10,2) DEFAULT 0,
                       PRIMARY KEY (nomStation, libelle),
                       FOREIGN KEY (nomStation) REFERENCES Station
                         ON DELETE CASCADE
                       );
CREATE TABLE Client (id NUMBER (10),
                      nom VARCHAR2(30) NOT NULL,
                      prenom VARCHAR2 (30),
                       ville VARCHAR2 (30) NOT NULL,
                       region VARCHAR2(30),
                        solde NUMBER (10,2) DEFAULT 0 NOT NULL,
                         PRIMARY KEY (id)
                   );  

CREATE TABLE Sejour  (idClient NUMBER (10),
                       station VARCHAR2 (30),
                       debut NUMBER (10),
                       nbPlaces  NUMBER (4) NOT NULL,
                        PRIMARY KEY (idClient, station, debut),
                        FOREIGN KEY (idClient) REFERENCES Client,
                        FOREIGN KEY (station) REFERENCES Station
                             ON DELETE CASCADE);

2.2 Insertion de données

Rappel : toute mise-à-jour peut être validée avec la commande commit; et annulée avec la commande rollback;.

Insérez dans la base les données de la figure 1 (placez les ordres INSERT pour pouvoir les exécuter à volonté). Attention, l'ordre des INSERT est important (pourquoi ?).

Vous pouvez ensuite tester les contraintes avec quelques ordres SQL. Par exemple : détruisez la station et vérifiez que les activités ont disparu ; insérez une autre station en (Guadeloupe, Antilles) ; insérez une station dans une région 'Nullepart', etc.

STATION

NomStation Capacité Lieu Région Tarif

Venusa 350 Guadeloupe Antilles 1200

ACTIVITES

NomStation Libellé Prix

Venusa Voile 150

Venusa Plongée 120

CLIENT

id nom prénom ville région solde

10 Fogg Phileas Londres Europe 12465

20 Pascal Blaise Paris Europe 6763

30 Kerouac Jack New York Amérique 9812

SEJOUR

idClient station début nbPlaces

20 Venusa 1998-08-03 4

Figure 1 : La base 'Agence'

Solution :

INSERT INTO Station (NomStation, Capacite, Lieu, Region, tarif)
  VALUES     ('Venusa', 350, 'Guadeloupe', 'Antilles', 1200);

INSERT INTO Activite (NomStation, Libelle, prix)
VALUES ('Venusa', 'Voile', 150);

INSERT INTO Activite (NomStation, Libelle, prix)
VALUES ('Venusa', 'Plongee', 120);

INSERT INTO CLIENT (id, nom,  prenom, ville, region, solde)
VALUES (20, 'Pascal', 'Blaise', 'Paris', 'Europe', 6763);

INSERT INTO SEJOUR (idClient, station, debut, nbPlaces)
VALUES (20, 'Venusa', 19980803,  4);

2.3 Vues

Objectif : créer des vues et tester l'interrogation et la mise-à-jour à travers ces vues.

Créez les vues suivantes sur le schéma précédent.
1. Une vue ActivitesModiques (Station, Activite) donnant le nom des stations et des activités dont le prix est inférieur à 140 FF. Toute ligne insérée dans cette vue doit apparaître dans la vue ensuite.
2. Une vue ActivitesCheres, de même schéma, avec prix supérieur à 140 FF, et la même contrainte d'insertion.
3. Une vue StationEuro (Nom, Capacite, Lieu, TarifEuro) donnant le nom d'une station, sa capacité, le lieu et le tarif en euro (un euro=6,58 FF).
4. Une vue Tarifs (Station, Tarif, OptionMin, OptionMax) donnant, pour chaque station, le tarif et les prix min et max des activités.
Solution :

Exemple :
```
CREATE VIEW ActivitesCheres AS select nomstation, libelle
FROM activite
WHERE prix > 140
WITH CHECK OPTION;

CREATE VIEW StationEuro (Nom, Capacite, Lieu, TarifEuro) AS
SELECT NomStation, capacite, lieu, tarif / 6.58
FROM Station;
```
Consultez ensuite le contenu de ces vues. Vous pouvez insérez quelques lignes supplémentaires dans les tables et constater qu'elles sont prises en compte dans les vues.
Dans quelles vues peut-on insérer, détruire et mettre-à-jour ? Essayez les opérations suivantes :
1. Insérez une activité 'Kayac' pour la station 'Venusa' dans ActivitesCheres et ActivitesModiques. Le contrôle sur l'insertion est-il utile dans ce cas ?
2. Peut-on insérer dans StationEuro ? Sous quelle condition ? Faites l'essai.
3. Détruisez une ligne de StationEuro.
Solution :
1. Le contrôle est inutile puisqu'on ne peut pas spécifier le prix au travers de la vue.
2. On ne peut insérer dans StationEuro que les attributs (nom, capacite, lieu) a cause des règles sur les NOT NULL. tarifEuro n'est pas modifiable car il résulte d'un calcul.

2.4 Triggers

Indication importante : la dernière ligne de création d'un trigger doit être un END;, suivie d'une ligne contenant seulement '/'.

Implantez par un trigger la règle suivante : si le prix d'une activité baisse, alors le tarif de la station doit augmenter de la différence.

Indication : le trigger doit se déclencher sur une modification, et tester pour chaque ligne que la nouvelle valeur est plus grande que l'ancienne. Si ce n'est pas le cas, faire un UPDATE de la station pour ajouter la différence entre l'ancienne et la nouvelle valeur.

Faites l'expérience : diminuez le prix d'une activité, et regardez ce qui se passe pour la station.

Solution :

CREATE TRIGGER prix_croissant
AFTER UPDATE ON Activite
FOR EACH ROW
WHEN (new.prix < old.prix)
BEGIN
  UPDATE Station SET tarif = tarif +  :old.prix - :new.prix
  WHERE  nomStation = :new.nomStation;
END;
/

On veut disposer de l'information nbActivites dans la table Station. Pour cela :

Ajoutez la colonne nbActivites avec pour valeur par défaut 0.
Créez un trigger qui maintient cette information.

Solution :

ALTER TABLE Station ADD (nbActivites NUMBER (3) DEFAULT 0);
         
CREATE TRIGGER nb_activites
AFTER INSERT OR DELETE ON Activite
BEGIN
  UPDATE Station S
  SET nbActivites = (SELECT COUNT (*) FROM Activite A
                      WHERE S.nomStation = A.nomStation);
END;
/

Autre possibilité : un trigger par ligne sur les évènements INSERT et DELETE.

STATION
NomStation	Capacité	Lieu	Région	Tarif
Venusa	350	Guadeloupe	Antilles	1200

ACTIVITES
NomStation	Libellé	Prix
Venusa	Voile	150
Venusa	Plongée	120

CLIENT
id	nom	prénom	ville	région	solde
10	Fogg	Phileas	Londres	Europe	12465
20	Pascal	Blaise	Paris	Europe	6763
30	Kerouac	Jack	New York	Amérique	9812