SQL

La base de données exemple

"GRAND HOTEL"

1. Le modèle conceptuel de données
2. Le modèle physique
3. Script de création de la base de données
4. Création des données

1. Le modèle conceptuel de données

Voici un MCD établi à partir de l'outil AMC (AMC*Designor ou encore Power AMC) :

Quelques explications
La plupart des clefs sont des entiers (I) qui pourront être auto générés par exemple par un type AUTOINCREMENT (Paradox, Access) ou encore via un trigger (identity de SQL Server...). Pour certaines entités, notamment celles servant de références à la saisie (MODE_PAIEMENT, TYPE, CODE), la clef est un code. Enfin, pour les entités TARIF et PLANNING, nous avons choisi une date comme clef.
Chaque entité est repérée à l'aide d'un trigramme (code de 3 lettres) qui sert de préfixe pour chaque attribut. Exemple : CHB pour CHAMBRE, LIF pour LIGNE_FACTURE, etc...
Les booléens seront représentés par des valeurs numériques 0 (faux) et 1 (vrai), chaque attribut ayant obligatoirement une valeur par défaut.

Voici les codes des différents types de données :

I	Integer (entier long)
N	Number (réel)
SI	Short Integer (entier court)
BL	Boolean (booléen)
A	Char (caractères alpha de longueur fixe)
VA	VarChar (caractères alpha longueur variable avec un maximum)
D	Date
MN	Money (monnaie)

L'association "occupée" permet de connaître la réservation ou l'occupation d'une chambre (une chambre peut avoir été réservée mais pas occupée), c'est pourquoi cette association possède les attributs NB_PERS (nombre de personnes : entier) RESERVE (réservée : booléen) et OCCUPE (occupe : booléen). Une chambre à une date donnée, ne peut être occupée que par un seul client. Mais un client peut occuper plusieurs chambres à la même date ou la même chambre à différentes dates, voire même plusieurs chambres à plusieurs dates...
Entité CLIENT : Un client peut avoir plusieurs adresses, plusieurs numéros de téléphone et plusieurs e-mail. Pour le téléphone, comme pour l'e-mail, l'attribut 'localisation' permet de savoir si le téléphone est situé au domicile, à l'entreprise, etc...
L'entité TITRE permet de donner un titre à une personne, parmi les valeurs 'M.' (monsieur), 'Mme.' (madame) et 'Melle.' (mademoiselle).
L'entité TYPE permet de connaître le type de téléphone, parmi les valeurs 'TEL' (téléphone), 'FAX' (télécopie) et 'GSM' (portable).
L'entité MODE_PAIEMENT permet de connaître le genre de paiement, parmi les valeurs 'ESP' (espèces), 'CHQ' (chèque), 'CB' (carte bancaire).
L'association "payée" intègre la date du paiement d'une facture.

NOTA : ce modèle est incomplet. Si l'on devait faire figurer l'adresse sur la facture il faudrait choisir une adresse du client. La meilleure façon de régler le problème est de faire glisser la clef du client dans la table des adresses et d'ajouter dans la table facture l'ID de l'adresse choisie pour la facture. C'est ce que l'on apelle un "lien identifiant" qui se positionne au niveau du lien entre l'association "domicilié" et l'entité "adresse". On rajoute alors une association entre la facture et l'adresse de cardinalité 0,1.

2. Le modèle physique

Nous avons demandé à générer un modèle basé sur le SQL ANSI de manière à pouvoir être compatible avec la plupart des SGBDR :

Vous constaterez que toutes les tables ont été préfixées avec la lettre T lorsquelles proviennent d'entités, et de TJ lorsqu'elles proviennent d'associations. Dans ce dernier cas, leur nom a été constitué des trigrammes des tables en jeu dans la jointure (TJ_TRF_LIF et TJ_CHB_PLN_CLI).

3. Script de création de la base de données

La génération de la base de données au format SQL standard donne le code suivant :


-- ============================================================
--   Nom de la base   :  MCD_HOTEL 
--   Nom de SGBD      :  ANSI Niveau 2 
--   Date de création :  16/01/2001  22:24 
--   Copyright       :  Frédéric BROUARD
-- ============================================================

-- ============================================================
--   Table : T_CHAMBRE 
-- ============================================================
create table T_CHAMBRE
(
    CHB_ID               INTEGER               not null,
    CHB_NUMERO           SMALLINT              not null,
    CHB_ETAGE            CHAR(3)                       ,
    CHB_BAIN             NUMERIC(1)            not null        default 0,
    CHB_DOUCHE           NUMERIC(1)            not null        default 1,
    CHB_WC               NUMERIC(1)            not null        default 1,
    CHB_COUCHAGE         SMALLINT              not null,
    CHB_POSTE_TEL        CHAR(3)                       ,
    primary key (CHB_ID)
);

-- ============================================================
--   Index : T_CHAMBRE_PK 
-- ============================================================
create unique index T_CHAMBRE_PK on T_CHAMBRE (CHB_ID asc);

-- ============================================================
--   Table : T_TARIF 
-- ============================================================
create table T_TARIF
(
    TRF_DATE_DEBUT       DATE                 not null,
    TRF_TAUX_TAXES       NUMERIC               not null,
    TRF_PETIT_DEJEUNE    NUMERIC(8,2)           not null,
    primary key (TRF_DATE_DEBUT)
);

-- ============================================================
--   Index : T_TARIF_PK 
-- ============================================================
create unique index T_TARIF_PK on T_TARIF (TRF_DATE_DEBUT asc);

-- ============================================================
--   Table : T_PLANNING 
-- ============================================================
create table T_PLANNING
(
    PLN_JOUR             DATE                  not null,
    primary key (PLN_JOUR)
);

-- ============================================================
--   Index : T_PLANNING_PK 
-- ============================================================
create unique index T_PLANNING_PK on T_PLANNING (PLN_JOUR asc);

-- ============================================================
--   Table : T_TITRE
-- ============================================================
create table T_TITRE
(
    TIT_CODE             CHAR(8)               not null,
    TIT_LIBELLE          VARCHAR(32)           not null,
    primary key (TIT_CODE)
);

-- ============================================================
--   Index : T_TITRE_PK 
-- ============================================================
create unique index T_TITRE_PK on T_TITRE (TIT_CODE asc);

-- ============================================================
--   Table : T_TYPE 
-- ============================================================
create table T_TYPE
(
    TYP_CODE             CHAR(8)               not null,
    TYP_LIBELLE          VARCHAR(32)           not null,
    primary key (TYP_CODE)
);

-- ============================================================
--   Index : T_TYPE_PK 
-- ============================================================
create unique index T_TYPE_PK on T_TYPE (TYP_CODE asc);

-- ============================================================
--   Table : T_MODE_PAIEMENT 
-- ============================================================
create table T_MODE_PAIEMENT
(
    PMT_CODE             CHAR(8)               not null,
    PMT_LIBELLE          VARCHAR(64)           not null,
    primary key (PMT_CODE)
);

-- ============================================================
--   Index : T_MODE_PAIEMENT_PK 
-- ============================================================
create unique index T_MODE_PAIEMENT_PK on T_MODE_PAIEMENT (PMT_CODE asc);

-- ============================================================
--   Table : T_CLIENT 
-- ============================================================
create table T_CLIENT
(
    CLI_ID               INTEGER               not null,
    TIT_CODE             CHAR(8)                       ,
    CLI_NOM              CHAR(32)              not null,
    CLI_PRENOM           VARCHAR(25)                   ,
    CLI_ENSEIGNE         VARCHAR(100)                  ,
    primary key (CLI_ID)
);

-- ============================================================
--   Index : T_CLIENT_PK 
-- ============================================================
create unique index T_CLIENT_PK on T_CLIENT (CLI_ID asc);

-- ============================================================
--   Index : L_CLI_TIT_FK 
-- ============================================================
create index L_CLI_TIT_FK on T_CLIENT (TIT_CODE asc);

-- ============================================================
--   Table : T_FACTURE 
-- ============================================================
create table T_FACTURE
(
    FAC_ID               INTEGER               not null,
    CLI_ID               INTEGER               not null,
    PMT_CODE             CHAR(8)                       ,
    FAC_DATE             DATE                 not null,
    FAC_PMT_DATE         DATE                          ,
    primary key (FAC_ID)
);

-- ============================================================
--   Index : T_FACTURE_PK 
-- ============================================================
create unique index T_FACTURE_PK on T_FACTURE (FAC_ID asc);

-- ============================================================
--   Index : L_FAC_CLI_FK 
-- ============================================================
create index L_FAC_CLI_FK on T_FACTURE (CLI_ID asc);

-- ============================================================
--   Index : TJ_FAC_PMT_FK 
-- ============================================================
create index TJ_FAC_PMT_FK on T_FACTURE (PMT_CODE asc);

-- ============================================================
--   Table : T_ADRESSE 
-- ============================================================
create table T_ADRESSE
(
    ADR_ID               INTEGER               not null,
    CLI_ID               INTEGER               not null,
    ADR_LIGNE1           VARCHAR(32)           not null,
    ADR_LIGNE2           VARCHAR(32)                   ,
    ADR_LIGNE3           VARCHAR(32)                   ,
    ADR_LIGNE4           VARCHAR(32)                   ,
    ADR_CP               CHAR(5)               not null,
    ADR_VILLE            CHAR(32)              not null,
    primary key (ADR_ID)
);

-- ============================================================
--   Index : T_ADRESSE_PK 
-- ============================================================
create unique index T_ADRESSE_PK on T_ADRESSE (ADR_ID asc);

-- ============================================================
--   Index : L_ADR_CLI_FK 
-- ============================================================
create index L_ADR_CLI_FK on T_ADRESSE (CLI_ID asc);

-- ============================================================
--   Table : T_TELEPHONE 
-- ============================================================
create table T_TELEPHONE
(
    TEL_ID               INTEGER               not null,
    CLI_ID               INTEGER               not null,
    TYP_CODE             CHAR(8)               not null,
    TEL_NUMERO           CHAR(20)              not null,
    TEL_LOCALISATION     VARCHAR(64)                   ,
    primary key (TEL_ID)
);

-- ============================================================
--   Index : T_TELEPHONE_PK 
-- ============================================================
create unique index T_TELEPHONE_PK on T_TELEPHONE (TEL_ID asc);

-- ============================================================
--   Index : L_TEL_CLI_FK 
-- ============================================================
create index L_TEL_CLI_FK on T_TELEPHONE (CLI_ID asc);

-- ============================================================
--   Index : L_TEL_TYP_FK 
-- ============================================================
create index L_TEL_TYP_FK on T_TELEPHONE (TYP_CODE asc);

-- ============================================================
--   Table : T_EMAIL 
-- ============================================================
create table T_EMAIL
(
    EML_ID               INTEGER               not null,
    CLI_ID               INTEGER               not null,
    EML_ADRESSE          VARCHAR(100)          not null,
    EML_LOCALISATION     VARCHAR(64)                   ,
    primary key (EML_ID)
);

-- ============================================================
--   Index : T_EMAIL_PK 
-- ============================================================
create unique index T_EMAIL_PK on T_EMAIL (EML_ID asc);

-- ============================================================
--   Index : L_EML_CLI_FK 
-- ============================================================
create index L_EML_CLI_FK on T_EMAIL (CLI_ID asc);

-- ============================================================
--   Table : T_LIGNE_FACTURE 
-- ============================================================
create table T_LIGNE_FACTURE
(
    LIF_ID               INTEGER               not null,
    FAC_ID               INTEGER               not null,
    LIF_QTE              NUMERIC               not null,
    LIF_REMISE_POURCENT  NUMERIC                       ,
    LIF_REMISE_MONTANT   NUMERIC(8,2)                  ,
    LIF_MONTANT          NUMERIC(8,2)          not null,
    LIF_TAUX_TVA         NUMERIC(8,2)          not null,
    primary key (LIF_ID)
);

-- ============================================================
--   Index : T_LIGNE_FACTURE_PK 
-- ============================================================
create unique index T_LIGNE_FACTURE_PK on T_LIGNE_FACTURE (LIF_ID asc);

-- ============================================================
--   Index : L_LIF_FAC_FK 
-- ============================================================
create index L_LIF_FAC_FK on T_LIGNE_FACTURE (FAC_ID asc);

-- ============================================================
--   Table : TJ_TRF_CHB 
-- ============================================================
create table TJ_TRF_CHB
(
    CHB_ID               INTEGER               not null,
    TRF_DATE_DEBUT       DATE                  not null,
    TRF_CHB_PRIX         NUMERIC(8,2)          not null,
    primary key (CHB_ID, TRF_DATE_DEBUT)
);

-- ============================================================
--   Index : TJ_TRF_CHB_PK 
-- ============================================================
create unique index TJ_TRF_CHB_PK on TJ_TRF_CHB (CHB_ID asc, TRF_DATE_DEBUT asc);

-- ============================================================
--   Index : L_CHB_TRF_FK 
-- ============================================================
create index L_CHB_TRF_FK on TJ_TRF_CHB (CHB_ID asc);

-- ============================================================
--   Index : L_TRF_CHB_FK 
-- ============================================================
create index L_TRF_CHB_FK on TJ_TRF_CHB (TRF_DATE_DEBUT asc);

-- ============================================================
--   Table : TJ_CHB_PLN_CLI 
-- ============================================================
create table TJ_CHB_PLN_CLI
(
    CHB_ID               INTEGER               not null,
    PLN_JOUR             DATE                  not null,
    CLI_ID               INTEGER               not null,
    CHB_PLN_CLI_NB_PERS  SMALLINT              not null,
    CHB_PLN_CLI_RESERVE  NUMERIC(1)            not null        default 0,
    CHB_PLN_CLI_OCCUPE   NUMERIC(1)            not null        default 1,
    primary key (CHB_ID, PLN_JOUR)
);

-- ============================================================
--   Index : TJ_CHB_PLN_CLI_PK 
-- ============================================================
create unique index TJ_CHB_PLN_CLI_PK on TJ_CHB_PLN_CLI (CHB_ID asc, PLN_JOUR asc, CLI_ID asc);

-- ============================================================
--   Index : L_CHB_PLN_CLI_FK 
-- ============================================================
create index L_CHB_PLN_CLI_FK on TJ_CHB_PLN_CLI (CHB_ID asc);

-- ============================================================
--   Index : L_PLN_CHB_CLI_FK 
-- ============================================================
create index L_PLN_CHB_CLI_FK on TJ_CHB_PLN_CLI (PLN_JOUR asc);

-- ============================================================
--   Index : L_CLI_CHB_PLN_FK 
-- ============================================================
create index L_CLI_CHB_PLN_FK on TJ_CHB_PLN_CLI (CLI_ID asc);

Vous noterez que nous avons volontairement omis les intégrités référentielles de manière à alléger le code mais aussi pour le rendre le plus compatible possible.

Téléchargez le script SQL de création de la base de données (ANSI niveau 2)

Si vous voulez la version complète du code de génération de cette base de données, voici un tableau des différentes versions que j'ai fait générer par AMC :

NOTA : nous n'avons pas introduit de colonne de type auto incrémenté dans les scripts de création de base de données, mais vous pouvez les modifier en y introduisant un trigger. Ne le faites pas si vous voulez pouvoir exploiter le jeu de données nécessaire aux exercices qui se trouvent dans les chapitres qui suivent.
Exemples : pour le SGBDR InterBase de Borland / Inprise, vous pouvez utiliser un générateur de nombre séquentiel utilisable par tous. Il faut donc créer autant de générateur qu'il existe dans la base de colonnes nécessitant une auto incrémentation, puis dans chacun des triggers de type BEFORE INSERT, appeler ce générateur.
Pour SQL Server de Microsoft, vous pouvez utiliser le type 'identity', mais vous devrez certainement modifier le type des colonnes des clefs étrangères dans le script de création de la base.
Pour Paradox il suffit de remplacer le type "I" par le type "+" dans les colonnes où cela s'avère nécessaire.

Exemple de trigger pour InterBase :

A la création de la base :


CREATE GENERATOR CLI_ID_GEN TO 2301;

Qui indique de réserver un espace pour stocker la valeur de l'auto incrément de nom CLI_ID_GEN et commençant par la valeur 2301.
Dans le trigger BEFORE INSERT de la table CLIENT, on utilise ce générateur pour alimenter le champs NUM_CLI :


CREATE TRIGGER AUTOINC_CLI FOR T_CLIENT
BEFORE INSERT AS
BEGIN
   NEW.CLI_ID = GEN_ID(CLI_ID_GEN,1);
END

NEW.CLI_ID est la valeur de la colonne après passage dans le trigger et AUTOINC_CLI une fonction appelant le générateur.

4. Création des données

Comme il nous faut des données pour travailler, vous trouverez ci-dessous un script SQL dans lequel chaque ligne constitue une instruction d'insertion de données.
Nous avons utilisé les caractères /* et */ pour y insérer des commentaires. Si votre interpréteur SQL ne comprend pas ces instructions, vous pouvez réaliser un petit programme qui lit ce fichier ligne par ligne et lance les requêtes d'insertion sauf dans les deux cas suivants :

la ligne est vide
la ligne débute par /*

Téléchargez le script SQL de création du jeu d'essai :

Formats d'insertion	Exemple (23 février 2001)
Date ISO (AAAA-MM-JJ)	insert into table (colonne_date) values ('2001-02-23')
Date US ('MM/JJ/AAAA')	insert into table (colonne_date) values ('02/23/2001')
Date FR ('JJ/MM/AAAA')	insert into table (colonne_date) values ('23/02/2001')

Pour le SGBDR Paradox, vous pouvez :

mettre le fichier des ordres SQL d'insertion dans une colonne de table via un import de données. Par exemple dans une table Paradox de nom INSERT_EXEMPLE possédant une colonne de nom SQL_ORDER.
jouer le script ci dessus afin d'insérer les données :


var
   tc     TCursor
   svar   String
   sqlVar SQL
   db     Database
endvar

errorTrapOnWarnings(True)
db.open(...) => chemin de la base de données cible
tc.open("INSERT_EXEMPLE.db")

scan tc :
   svar = TC.SQL_ORDER 
   try
      sqlVar.readFromString(svar)
      sqlVar.executeSQL(db)
   onFail
      errorShow() 
      msgInfo("ORDRE SQL",sVar)
      quitLoop
   endTry 
endscan

errorTrapOnWarnings(False)
endMethod