1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| CREATE table station (nomstation varchar(40), capacite int(11) not null, lieu varchar(40) not null, region enum('ocean indien', 'Antilles','Europe','Ameriques', 'Extreme Orient'),
tarif float default 0,
UNIQUE (lieu,region),
constraint pkstation PRIMARY KEY (nomstation));
create table activite (idactivite int(11) not null AUTO_INCREMENT , nomstation varchar(40) not null, libelle varchar(40) not null, prix float DEFAULT 0 not NULL ,
check (prix BETWEEN 0 and station.tarif ),
CONSTRAINT pkactivite primary key(idactivite),
CONSTRAINT fkactivite FOREIGN key (nomstation) REFERENCES station (nomstation) on delete cascade);
create table sejour (id int(11) not null, nomstation varchar(40), debut date not null, nbplaces int(11) not null ,
CHECK (nbplaces<sation.capacite),
constraint pksejour primary key(id, nomstation),
foreign key (id) references clients (id) on delete cascade,
FOREIGN key (nomstation) references station (nomstation) on delete cascade); |
Partager