Section 3. REALISATION
3.2.1. Procédure d'implantation de la base de
données
Avant de créer les tables, il faut d'abord créer la
base de données la commande SQL dans MYSQL qui permet de créer
une base de données est : CREATE DATABASE `nom_dela_bdd' nous a permis
de créer la base de données.
Et ensuite nous allons créer nos tables :
CREATE TABLE Fonction (
numfonc INTEGER NOT NULL AUTO_INCREMENT,
lib_fonc VARCHAR(50) NULL,
PRIMARY KEY(numfonc)
);
CREATE TABLE Client (
numcli INTEGER NOT NULL AUTO_INCREMENT,
prenom VARCHAR(50) NULL,
nom VARCHAR(50) NULL,
postnom VARCHAR(50) NULL,
lieu_naiss VARCHAR(50) NULL,
date_naiss DATE NULL,
profession VARCHAR(50) NULL,
etat_civil VARCHAR(30) NULL,
rooming_list INTEGER UNSIGNED NULL,
PRIMARY KEY(numcli)
);
CREATE TABLE Type_client (
cod_typcli INTEGER NOT NULL AUTO_INCREMENT,
lib_typcli INTEGER NULL,
PRIMARY KEY(cod_typcli)
);
CREATE TABLE Salle_polyvalente (
cod_occusa INTEGER NOT NULL AUTO_INCREMENT,
theme_occusa INTEGER NULL,
PRIMARY KEY(cod_occusa)
);
CREATE TABLE Categorie (
cod_cat INTEGER NOT NULL AUTO_INCREMENT,
lib_cat VARCHAR(30) NULL,
prix VARCHAR NULL,
PRIMARY KEY(cod_cat)
Page 87 sur 108
CREATE TABLE Agent (
matricule INTEGER NOT NULL AUTO_INCREMENT,
Agent_matricule INTEGER NOT NULL,
Fonction_numfonc INTEGER NOT NULL,
prenom VARCHAR NULL,
nom VARCHAR NULL,
postnom VARCHAR NULL,
adresse VARCHAR NULL,
sexe VARCHAR NULL,
PRIMARY KEY(matricule),
INDEX Agent_FKIndex1(Fonction_numfonc),
INDEX Agent_FKIndex2(Agent_matricule),
FOREIGN KEY(Fonction_numfonc)
REFERENCES Fonction(numfonc)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(Agent_matricule)
REFERENCES Agent(matricule)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE Reservation (
cod_res INTEGER NOT NULL AUTO_INCREMENT,
Client_numcli INTEGER NOT NULL,
type_res VARCHAR(50) NULL,
date_res DATE NULL,
rooming_list INTEGER UNSIGNED NULL,
nbr_chambre INTEGER UNSIGNED NULL,
PRIMARY KEY(cod_res),
INDEX Reservation_FKIndex1(Client_numcli),
FOREIGN KEY(Client_numcli)
REFERENCES Client(numcli)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE chambre (
cod_chambre INTEGER NOT NULL AUTO_INCREMENT,
Categorie_cod_cat INTEGER NOT NULL,
lib_chambre VARCHAR(20) NULL,
etat VARCHAR BINARY NULL,
niveau VARCHAR(20) NULL,
PRIMARY KEY(cod_chambre),
INDEX chambre_FKIndex1(Categorie_cod_cat),
FOREIGN KEY(Categorie_cod_cat)
REFERENCES Categorie(cod_cat)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Page 88 sur 108
CREATE TABLE Accuil (
idAccuil INTEGER NOT NULL AUTO_INCREMENT,
Agent_matricule INTEGER NOT NULL,
Client_numcli INTEGER NOT NULL,
date_accueil DATE NULL,
PRIMARY KEY(idAccuil, Agent_matricule, Client_numcli),
INDEX Accuil_FKIndex1(Agent_matricule),
INDEX Accuil_FKIndex2(Client_numcli),
FOREIGN KEY(Agent_matricule)
REFERENCES Agent(matricule)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(Client_numcli)
REFERENCES Client(numcli)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE Appartenir (
Type_client_cod_typcli INTEGER NOT NULL,
Client_numcli INTEGER NOT NULL,
num_appart INTEGER NOT NULL,
date_appart DATE NULL,
PRIMARY KEY(Type_client_cod_typcli, Client_numcli,
num_appart),
INDEX
Type_client_has_Clientnom_FKIndex1(Type_client_cod_typcli),
INDEX Type_client_has_Clientnom_FKIndex2(Client_numcli),
FOREIGN KEY(Type_client_cod_typcli)
REFERENCES Type_client(cod_typcli)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(Client_numcli)
REFERENCES Client(numcli)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
Page 89 sur 108
CREATE TABLE Paiement (
num_paie INTEGER NOT NULL AUTO_INCREMENT,
Agent_matricule INTEGER UNSIGNED NOT NULL,
Reservation_cod_res INTEGER UNSIGNED NOT NULL,
Client_numcli INTEGER UNSIGNED NOT NULL,
designation VARCHAR(30) NULL,
date_paie DATE NULL,
PRIMARY KEY(num_fac),
INDEX Paiement_FKIndex1(Client_numcli),
INDEX Paiement _FKIndex2(Reservation_cod_res),
INDEX Paiement _FKIndex3(Agent_matricule),
FOREIGN KEY(Client_numcli)
REFERENCES Client(numcli)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(Reservation_cod_res)
REFERENCES Reservation(cod_res)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(Agent_matricule)
REFERENCES Agent(matricule)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE occuper (
Client_numcli INTEGER UNSIGNED NOT NULL,
Salle_polyvalente_cod_occusa INTEGER UNSIGNED NOT NULL,
chambre_cod_chambre INTEGER UNSIGNED NOT NULL,
date_deb_occu DATE NULL,
date_fin_occu DATE NULL,
PRIMARY KEY(Client_numcli, Salle_polyvalente_cod_occusa),
INDEX Clientnom_has_Salle_polyvalente_FKIndex1(Client_numcli),
INDEX
Clientnom_has_Salle_polyvalente_FKIndex2(Salle_polyvalente_cod_occusa)
, INDEX
Clientnom_has_Salle_polyvalente_FKIndex3(chambre_cod_chambre),
FOREIGN KEY(Client_numcli)
REFERENCES Client(numcli)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(Salle_polyvalente_cod_occusa)
REFERENCES Salle_polyvalente(cod_occusa)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(chambre_cod_chambre)
REFERENCES chambre(cod_chambre)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Recherche
- Recherche du numéro de réservation
- Recherche date libre
Page 90 sur 108
| 
