mFMXt8i9

1DROP TABLE IF EXISTS client, sejour, station, activite, propose;
2
3CREATE TABLE client
4(
5    id serial primary key,
6    region text not null,
7    nom text not null
8);
9
10CREATE TABLE station
11(
12    idstation serial primary key,
13    nomstation text not null,
14    region text not null,
15    capacite int not null check ( capacite >= 0 ),
16    tarif numeric(8,0) not null check ( tarif >= 0 )
17);
18
19CREATE TABLE sejour
20(
21    idsejour serial primary key,
22    debut date not null unique ,
23    nbPlaces int not null check ( nbPlaces >= 0 ),
24    idclient int not null  references client,
25    idstation int not null references station
26);
27
28CREATE TABLE activite
29(
30    idactivite int not null primary key,
31    libelle text not null
32);
33
34CREATE TABLE propose
35(
36    idstation int not null references station,
37    idactivite int not null references activite,
38    primary key (idactivite,idstation),
39    prix numeric(8,0) not null check ( prix >= 0 )
40);
41
42insert into client
43values  (1, 'Royaume Champignon' ,'Mario'),
44        (2,'Royaume Champignon','Peach'),
45        (3,'Yoshi','Ile des Yoshi');
46
47insert into station
48values  (1,'Yunnanville','Port Lacanaïe',20,1200),
49        (2,'Gelato-les-Flots','Ile Delphino',100,2270),
50       (3,'Château de Bowser','Royaume Champignon',5,15750);
51
52
53insert into sejour
54values  (1,'03-08-1998',4,1,1),
55        (2,'22-07-2004',2,1,1),
56        (3,'03-09-2007',2,2,2);
57
58insert into activite
59values (1,'Voile'),
60       (2,'Plongée'),
61        (3,'Spectacles'),
62        (4,'Sauna');
63
64insert into propose
65values  (2,1,150),
66        (2,2,120),
67        (1,3,50),
68        (1,4,20),
69        (3,4,0);