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 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150
|
CREATE TABLE camping.facture (
id_facture bigserial NOT NULL,
id_location bigserial NOT NULL,
montant_facture float8 NOT NULL,
CONSTRAINT Pk_facture PRIMARY KEY ( id_facture )
) ;
CREATE INDEX idx_facture ON camping.facture ( id_location );
CREATE TABLE camping.option_location (
id_location bigserial NOT NULL,
id_option bigserial NOT NULL,
quantite bigserial NOT NULL,
debut_option date NOT NULL,
fin_option date NOT NULL,
CONSTRAINT Pk_option_location PRIMARY KEY ( id_option )
) ;
ALTER TABLE camping.option_location ADD CONSTRAINT Ck_5 CHECK ( debut_option<fin_option ) ;
CREATE INDEX idx_option_location ON camping.option_location ( id_location );
CREATE TABLE camping.location (
id_location bigserial NOT NULL,
id_client bigserial NOT NULL,
debut_loc date NOT NULL,
fin_loc date NOT NULL,
num_emplacement bigserial NOT NULL,
arrhes bigserial NOT NULL,
annule int2 NOT NULL,
CONSTRAINT Pk_location PRIMARY KEY ( id_location )
) ;
ALTER TABLE camping.location ADD CONSTRAINT Ck_4 CHECK ( debut_loc<fin_loc ) ;
CREATE INDEX idx_location ON camping.location ( id_client );
CREATE INDEX idx_location_0 ON camping.location ( num_emplacement );
CREATE TABLE camping.tarification_option (
id_saison bigserial NOT NULL,
id_option bigserial NOT NULL,
prix_journee float8 NOT NULL,
CONSTRAINT Pk_tarification_option PRIMARY KEY ( id_saison )
) ;
CREATE INDEX idx_tarification_option ON camping.tarification_option ( id_option );
CREATE TABLE camping.tarification_emplacement (
id_saison bigserial NOT NULL,
id_type_emplacement bigserial NOT NULL,
prix_journee float8 NOT NULL,
CONSTRAINT Pk_tarification_emplacement PRIMARY KEY ( id_saison )
) ;
CREATE INDEX idx_tarification_emplacement ON camping.tarification_emplacement ( id_type_emplacement );
CREATE TABLE camping.saison (
id_saison bigserial NOT NULL,
descriptif_saison varchar( 200 ) NOT NULL,
debut_saison date NOT NULL,
fin_saison date NOT NULL,
annee int8 NOT NULL,
CONSTRAINT Pk_saison PRIMARY KEY ( id_saison )
) ;
ALTER TABLE camping.saison ADD CONSTRAINT Ck_1 CHECK ( debut_saison < fin_saison ) ;
ALTER TABLE camping.saison ADD CONSTRAINT ck_6 CHECK ( date_part('year',debut_saison)=annee ) ;
ALTER TABLE camping.saison ADD CONSTRAINT ck_7 CHECK ( date_part('year',fin_saison)=annee ) ;
CREATE INDEX idx_saison ON camping.saison ( annee );
CREATE TABLE camping.annee (
annee int8 NOT NULL,
ouverture date NOT NULL,
fermeture date NOT NULL,
CONSTRAINT Pk_annee PRIMARY KEY ( annee )
) ;
ALTER TABLE camping.annee ADD CONSTRAINT Ck_0 CHECK ( ouverture < fermeture ) ;
CREATE TABLE camping.option_type_emplacement (
id_option bigserial NOT NULL,
id_type_emplacement bigserial NOT NULL,
quantite bigserial NOT NULL,
CONSTRAINT Pk_option_type_emplacement PRIMARY KEY ( id_option )
) ;
CREATE INDEX idx_option_type_emplacement ON camping.option_type_emplacement ( id_type_emplacement );
CREATE TABLE camping.option (
id_option bigserial NOT NULL,
description_option bigserial NOT NULL,
quantite_max bigserial NOT NULL,
CONSTRAINT Pk_option PRIMARY KEY ( id_option )
) ;
CREATE TABLE camping.emplacement (
num_emplacement bigserial NOT NULL,
id_type_emplacement bigserial NOT NULL,
CONSTRAINT Pk_emplacement PRIMARY KEY ( num_emplacement )
) ;
CREATE INDEX idx_emplacement ON camping.emplacement ( id_type_emplacement );
CREATE TABLE camping.type_emplacement (
id_type_emplacement bigserial NOT NULL,
description_type_emplacement varchar( 200 ) NOT NULL,
CONSTRAINT Pk_type_emplacement PRIMARY KEY ( id_type_emplacement )
) ;
CREATE TABLE camping.client (
id_client bigserial NOT NULL,
prenom varchar( 150 ) NOT NULL,
nom varchar( 150 ) NOT NULL,
adresse varchar( 150 ) NOT NULL,
ville varchar( 150 ) NOT NULL,
cp varchar( 10 ) NULL,
tel varchar( 15 ) NULL,
CONSTRAINT Pk_client PRIMARY KEY ( id_client )
) ;
ALTER TABLE camping.facture ADD CONSTRAINT Fk_facture FOREIGN KEY ( id_location ) REFERENCES camping.location( id_location ) ON DELETE CASCADE ;
ALTER TABLE camping.option_location ADD CONSTRAINT Fk_option_location FOREIGN KEY ( id_location ) REFERENCES camping.location( id_location ) ON DELETE CASCADE ;
ALTER TABLE camping.option_location ADD CONSTRAINT Fk_option_location_0 FOREIGN KEY ( id_option ) REFERENCES camping.option( id_option ) ON DELETE CASCADE ;
ALTER TABLE camping.location ADD CONSTRAINT Fk_location FOREIGN KEY ( id_client ) REFERENCES camping.client( id_client ) ON DELETE CASCADE ;
ALTER TABLE camping.location ADD CONSTRAINT Fk_location_0 FOREIGN KEY ( num_emplacement ) REFERENCES camping.emplacement( num_emplacement ) ON DELETE CASCADE ;
ALTER TABLE camping.tarification_option ADD CONSTRAINT Fk_tarification_option FOREIGN KEY ( id_saison ) REFERENCES camping.saison( id_saison ) ON DELETE CASCADE ;
ALTER TABLE camping.tarification_option ADD CONSTRAINT Fk_tarification_option_0 FOREIGN KEY ( id_option ) REFERENCES camping.option( id_option ) ON DELETE CASCADE ;
ALTER TABLE camping.tarification_emplacement ADD CONSTRAINT Fk_tarification_emplacement FOREIGN KEY ( id_saison ) REFERENCES camping.saison( id_saison ) ON DELETE CASCADE ;
ALTER TABLE camping.tarification_emplacement ADD CONSTRAINT Fk_tarification_emplacement_0 FOREIGN KEY ( id_type_emplacement ) REFERENCES camping.type_emplacement( id_type_emplacement ) ON DELETE CASCADE ;
ALTER TABLE camping.saison ADD CONSTRAINT Fk_saison FOREIGN KEY ( annee ) REFERENCES camping.annee( annee ) ON DELETE CASCADE ;
ALTER TABLE camping.option_type_emplacement ADD CONSTRAINT Fk_option_type_emplacement FOREIGN KEY ( id_option ) REFERENCES camping.option( id_option ) ON DELETE CASCADE ;
ALTER TABLE camping.option_type_emplacement ADD CONSTRAINT Fk_option_type_emplacement_0 FOREIGN KEY ( id_type_emplacement ) REFERENCES camping.type_emplacement( id_type_emplacement ) ON DELETE CASCADE ;
ALTER TABLE camping.emplacement ADD CONSTRAINT Fk_emplacement FOREIGN KEY ( id_type_emplacement ) REFERENCES camping.type_emplacement( id_type_emplacement ) ON DELETE CASCADE ; |
Partager