1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| Create Table Hotel
(CodeHotel Integer Identity primary key,
Hot_Adresse Varchar(35) Not null,
Hot_Codepostal char(5) not null,
Hot_Ville varchar (32) ,
Hot_Numtel char(10)
) ;
CREATE TABLE Client
(IdClient INT Identity PRIMARY KEY,
Nom VARCHAR(25) NOT NULL,
Prenom varchar (25) Not null,
DateNaissance DATEtime ,
cli_Adresse varchar (32),
cli_CodePostal char(5),
Hot_Ville varchar (32) ,
email varchar (50),
Login varchar(15) UNIQUE not null,
);
create table Categoriechambre
(Idcategoriechambre INT Identity PRIMARY KEY,
LibelleCategorie varchar (15) not null
CHECK(LibelleCategorie IN ('simple', 'double', 'double confort', 'suite')));
create table Saison (
IdSaison INT Identity PRIMARY KEY,
LibelleSaison varchar (32)not null,
datedebutSaison datetime not null,
datefinSaison datetime not null,
);
create table tarif_chambre
(
Idtarif INT Identity PRIMARY KEY,
Idcategoriechambre INT NOT NULL REFERENCES categoriechambre(Idcategorie),
IdSaison INT not null references saison (idsaison),
Prixnuit decimal (10,2) not null
Constraint Chambre_AK1 Unique (Idcategorie,idsaison)
) ;
create table chambre (
IdcategorieChambre INT Identity not null,
CodeHotel int not null ,
Idcategoriechambre int not null references categoriechambre (Idcategorie),
Numerochambre smallint not null,
Etage smallint not null
Constraint Chambre_PK Primary Key (codeHotel, IdChambre),
Constraint Chambre_AK1 Unique (codehotel, Etage, Numero),
Constraint codeHotel_FK Foreign Key (codehotel) References Hotel,
Constraint idcategoriechambre_FK Foreign Key (idcategoriechambre) References
Categoriechambre
) ;
create table Modereservation (
idModereservation int Identity primary key ,
designation varchar (15) not null ,
pourcentageModereservation integer not null
);
create table reservation (
idreservation int identity not null ,
Idclient int not null references client(idclient),
CodeHotel int not null references hotel (codehotel) ,
idchambre int not null references chambre(idchambre),
idModereservation int not null references Modereservation (idmodereservation),
Datereservation datetime not null ,
Datedebreservation datetime not null,
Datefinreservation datetime not null,
confirmation booleen not null
Constraint Chambre_PK Primary Key (idreservation, CodeHotel,IdChambre),
); |
Partager