· 7 years ago · Nov 30, 2018, 09:52 AM
1CREATE TABLE IF NOT EXISTS Client (
2 id SERIAL NOT NULL,
3 region TEXT NOT NULL,
4 nom TEXT NOT NULL,
5 PRIMARY KEY ( id ));
6
7
8CREATE TABLE IF NOT EXISTS Activite (
9 idLabelle VARCHAR(45) NOT NULL,
10 PRIMARY KEY ( idLabelle ));
11
12CREATE TABLE IF NOT EXISTS Station (
13 idStation SERIAL NOT NULL,
14 nomStation TEXT NOT NULL,
15 region TEXT NOT NULL,
16 capacite INT NOT NULL,
17 tarif DECIMAL(8,0) NOT NULL,
18 CHECK ( tarif >=0),
19 CHECK ( capacite >=0),
20 PRIMARY KEY ( idStation ));
21
22CREATE TABLE IF NOT EXISTS Sejour (
23 idSejour SERIAL NOT NULL,
24 debut DATE NOT NULL,
25 nbPlace INT NOT NULL,
26 Client_id INT NOT NULL,
27 Station_idStation INT NOT NULL,
28 CHECK ( nbPlace >=0),
29 PRIMARY KEY ( idSejour , Client_id , Station_idStation ),
30 CONSTRAINT fk_Sejour_Client
31 FOREIGN KEY ( Client_id )
32 REFERENCES Client ( id )
33 ON DELETE NO ACTION
34 ON UPDATE NO ACTION,
35 CONSTRAINT fk_Sejour_Station1
36 FOREIGN KEY ( Station_idStation )
37 REFERENCES Station ( idStation )
38 ON DELETE NO ACTION
39 ON UPDATE NO ACTION);
40
41
42CREATE TABLE IF NOT EXISTS Propose (
43 prix DECIMAL(8,0) NOT NULL,
44 Activite_idLabelle VARCHAR(45) NOT NULL,
45 Station_idStation INT NOT NULL,
46 CHECK ( prix >=0),
47 PRIMARY KEY ( prix , Activite_idLabelle , Station_idStation ),
48 CONSTRAINT fk_Propose_Activite1
49 FOREIGN KEY ( Activite_idLabelle )
50 REFERENCES Activite ( idLabelle )
51 ON DELETE NO ACTION
52 ON UPDATE NO ACTION,
53 CONSTRAINT fk_Propose_Station1
54 FOREIGN KEY ( Station_idStation )
55 REFERENCES Station ( idStation )
56 ON DELETE NO ACTION
57 ON UPDATE NO ACTION);
58
59ALTER TABLE sejour ADD CONSTRAINT UNI_OPE_SESSION UNIQUE (client_id,debut);
60
61INSERT INTO Client VALUES ( 111 , 'Royaume Champignon' , 'Mario' );
62INSERT INTO Client VALUES ( 222 , 'Royaume Champignon' , 'Peach' );
63INSERT INTO Client VALUES ( 333 , 'Ile des Yoshi' , 'Yoshi' );
64INSERT INTO Station VALUES ( 444 , 'Yunnanville' , 'Port Lacanaie' , 20 , 1200 );
65INSERT INTO Station VALUES ( 555 , 'Gelato les Flots' , 'Ile Delphino' , 100 , 2270 );
66INSERT INTO Station VALUES ( 666 , 'Chateau de Bowsette' , 'Royaume Champignon' , 5 , 15750 );
67INSERT INTO Sejour VALUES ( 123 , '1998-08-03' , 4 , 111 , 444 );
68INSERT INTO Sejour VALUES ( 456 , '2004-07-22' , 2 , 111 , 444 );
69INSERT INTO Sejour VALUES ( 789 , '2007-09-03' , 2 , 222 , 555 );
70INSERT INTO Activite VALUES ( 'Voile' );
71INSERT INTO Activite VALUES ( 'Plongee' );
72INSERT INTO Activite VALUES ( 'Spectacle' );
73INSERT INTO Activite VALUES ( 'Sauna' );
74INSERT INTO Propose VALUES ( 150 , 'Voile' , 555 ),( 120 , 'Plongee' , 555 ),( 50 , 'Spectacle' , 444 ),( 20 , 'Sauna' , 444 ),( 0 , 'Sauna' , 666 );