CREATE TABLE CHAMBRE
(
id_chambre INT NOT NULL
, etage VARCHAR(5) NOT NULL
, CONSTRAINT CHAMBRE_PK PRIMARY KEY (id_chambre)
) ;
CREATE TABLE CLIENT
(
id_client INT NOT NULL
, nom_client VARCHAR(32) NOT NULL
, CONSTRAINT CLIENT_PK PRIMARY KEY (id_client)
) ;
CREATE TABLE RESERVATION
(
id_client_reservant INT NOT NULL
, id_reservation INT NOT NULL
, date_reservation DATE NOT NULL
, id_client_concerne INT NOT NULL
, CONSTRAINT RESERVATION_PK PRIMARY KEY (id_client_reservant, id_reservation)
, CONSTRAINT RESERVATION_AK UNIQUE (id_client_concerne, date_reservation)
, CONSTRAINT RESERVATION_FK1 FOREIGN KEY (id_client_reservant)
REFERENCES CLIENT (id_client)
, CONSTRAINT RESERVATION_FK2 FOREIGN KEY (id_client_concerne)
REFERENCES CLIENT (id_client)
) ;
CREATE TABLE PLANNING
(
id_client_reservant INT NOT NULL
, id_reservation INT NOT NULL
, id_planning INT NOT NULL
, id_chambre INT NOT NULL
, date_affectation_debut DATE NOT NULL
, date_affectation_fin DATE NOT NULL
, CONSTRAINT PLANNING_PK PRIMARY KEY (id_client_reservant, id_reservation, id_planning)
, CONSTRAINT PLANNING_AK1 UNIQUE (id_chambre, date_affectation_debut)
, CONSTRAINT PLANNING_AK2 UNIQUE (id_chambre, date_affectation_fin)
, CONSTRAINT PLANNING_FK1 FOREIGN KEY (id_client_reservant, id_reservation)
REFERENCES RESERVATION (id_client_reservant, id_reservation)
, CONSTRAINT PLANNING_FK2 FOREIGN KEY (id_chambre)
REFERENCES CHAMBRE (id_chambre)
, CONSTRAINT PLANNING_CK1 CHECK (date_affectation_debut <= date_affectation_fin)
) ;
INSERT INTO CHAMBRE (id_chambre, etage) VALUES
(1, 'rdc'), (2, '3') ;
INSERT INTO CLIENT (id_client, nom_client) VALUES
(55, 'Dubicobit'), (101, 'François'), (102, 'Jean'), (103, 'Louis'), (104, 'André') ;
INSERT INTO RESERVATION (id_client_reservant, id_reservation, date_reservation, id_client_concerne) VALUES
(55, 1, '1992_04_10', 101), (55, 2, '1992_04_10', 102), (55, 3, '1992_04_10', 103), (55, 4, '1992_05_02', 101), (55, 5, '1992_05_02', 104) ;
INSERT INTO PLANNING (id_client_reservant, id_reservation, id_planning, id_chambre, date_affectation_debut, date_affectation_fin) VALUES
(55, 1, 1, 1, '1992_04_14', '1992_04_15'), (55, 1, 2, 1, '1992_04_27', '1992_04_29')
, (55, 4, 1, 1, '1992_05_02', '1992_05_02'), (55, 2, 1, 1, '1992_05_04', '1992_05_07')
;
Partager