Scénario 1 : Aux tables MENU_ENTREE, MENU_PLAT_PPAL, MENU_DESSERT, comme dans le cas du message #2 ;
Scénario 2 : A la table MENU_PLAT, comme dans le cas du message #7.
CREATE TABLE CATEGORIE
(
IdCat INT NOT NULL
, Categorie VARCHAR(24) NOT NULL
, CONSTRAINT CATEGORIE_PK PRIMARY KEY (IdCat)
) ;
CREATE TABLE PLAT
(
IdPlat INT NOT NULL
, NomPlat VARCHAR(64) NOT NULL
, IdCat INT NOT NULL
, CONSTRAINT PLAT_PK PRIMARY KEY (IdPlat)
, CONSTRAINT PLAT_CATEGORIE_FK FOREIGN KEY (IdCat)
REFERENCES CATEGORIE (IdCat)
) ;
CREATE TABLE MENU
(
IdMenu INT NOT NULL
, Prix INT NOT NULL
, CONSTRAINT MENU_PK PRIMARY KEY (IdMenu)
) ;
CREATE TABLE MENU_ENTREE
(
IdMenu INT NOT NULL
, IdPlat INT NOT NULL
, CONSTRAINT MENU_ENTREE_PK PRIMARY KEY (IdMenu, IdPlat)
, CONSTRAINT MENU_ENTREE_MENU_FK FOREIGN KEY (IdMenu)
REFERENCES MENU (IdMenu)
, CONSTRAINT MENU_ENTREE_PLAT_FK FOREIGN KEY (IdPlat)
REFERENCES PLAT (IdPlat)
) ;
CREATE TABLE MENU_PLAT_PPAL
(
IdMenu INT NOT NULL
, IdPlat INT NOT NULL
, CONSTRAINT MENU_PLAT_PPAL_PK PRIMARY KEY (IdMenu, IdPlat)
, CONSTRAINT MENU_PLAT_PPAL_MENU_FK FOREIGN KEY (IdMenu)
REFERENCES MENU (IdMenu)
, CONSTRAINT MENU_PLAT_PPAL_PLAT_FK FOREIGN KEY (IdPlat)
REFERENCES PLAT (IdPlat)
) ;
CREATE TABLE MENU_DESSERT
(
IdMenu INT NOT NULL
, IdPlat INT NOT NULL
, CONSTRAINT MENU_DESSERT_PK PRIMARY KEY (IdMenu, IdPlat)
, CONSTRAINT MENU_DESSERT_MENU_FK FOREIGN KEY (IdMenu)
REFERENCES MENU (IdMenu)
, CONSTRAINT MENU_DESSERT_PLAT_FK FOREIGN KEY (IdPlat)
REFERENCES PLAT (IdPlat)
) ;
CREATE TABLE RECEPTION
(
IdMenu INT NOT NULL
, IdReception INT NOT NULL
, DateReception DATE NOT NULL
, CONSTRAINT RECEPTION_PK PRIMARY KEY (IdMenu, IdReception)
, CONSTRAINT RECEPTION_MENU_FK FOREIGN KEY (IdMenu)
REFERENCES MENU (IdMenu)
) ;
CREATE TABLE INVITE
(
IdInvite INT NOT NULL
, NomInvite VARCHAR(32) NOT NULL
, CONSTRAINT INVITE_PK PRIMARY KEY (IdInvite)
) ;
CREATE TABLE INSCRIPTION
(
IdMenu INT NOT NULL
, IdReception INT NOT NULL
, IdInvite INT NOT NULL
, IdEntree INT NOT NULL
, IdPlatPpal INT NOT NULL
, IdDessert INT NOT NULL
, CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IdMenu, IdReception, IdInvite)
, CONSTRAINT INSCRIPTION_RECEPTION_FK FOREIGN KEY (IdMenu, IdReception)
REFERENCES RECEPTION (IdMenu, IdReception)
, CONSTRAINT INSCRIPTION_INVITE_FK FOREIGN KEY (IdInvite)
REFERENCES INVITE (IdInvite)
, CONSTRAINT INSCRIPTION_MENU_ENTREE_FK FOREIGN KEY (IdMenu, IdEntree)
REFERENCES MENU_ENTREE (IdMenu, IdPlat)
, CONSTRAINT INSCRIPTION_MENU_PLAT_PPAL_FK FOREIGN KEY (IdMenu, IdPlatPpal)
REFERENCES MENU_PLAT_PPAL (IdMenu, IdPlat)
, CONSTRAINT INSCRIPTION_MENU_DESSERT_FK FOREIGN KEY (IdMenu, IdDessert)
REFERENCES MENU_DESSERT (IdMenu, IdPlat)
) ;
INSERT INTO CATEGORIE (IdCat, Categorie) VALUES
(1, 'entrée'), (2, 'plat principal'), (3, 'dessert')
;
INSERT INTO PLAT (IdPlat, NomPlat, IdCat) VALUES
(11, 'entrée 01', 1), (12, 'entrée 02', 1), (13, 'entrée 03', 1), (14, 'entrée 04', 1)
, (21, 'plat principal 01', 2), (22, 'plat principal 02', 2), (23, 'plat principal', 2), (24, 'plat principal 04', 2)
, (31, 'dessert 01', 3), (32, 'dessert 02', 3), (33, 'dessert 03', 3), (34, 'dessert 04', 3)
;
INSERT INTO MENU (IdMenu, Prix) VALUES (1, 110), (2, 200), (3, 300), (4,420) ;
INSERT INTO MENU_ENTREE (IdMenu, IdPlat) VALUES
(1, 11), (1, 13)
, (2, 11), (2, 14)
, (3, 12), (3, 13)
, (4, 11), (4, 13) , (4, 14)
;
INSERT INTO MENU_PLAT_PPAL (IdMenu, IdPlat) VALUES
(1, 21), (1, 22), (1, 24)
, (2, 21), (2, 23)
, (3, 23), (3, 24)
, (4, 21), (4, 23) , (4, 24)
;
INSERT INTO MENU_DESSERT (IdMenu, IdPlat) VALUES
(1, 31), (1, 33)
, (2, 31), (2, 32), (2, 34)
, (3, 33), (3, 34)
, (4, 31), (4, 33) , (4, 34)
;
-- On a 3 réservations pour le menu 1, 4 réservations pour le menu 2, etc. :
INSERT INTO RECEPTION (IdMenu, IdReception, DateReception) VALUES
(1, 1, '2015-12-01'), (1, 2, '2015-12-01'), (1, 3, '2015-12-02')
, (2, 1, '2015-12-02'), (2, 2, '2015-12-04'), (2, 3, '2015-12-04'), (2, 4, '2015-12-15')
, (3, 1, '2015-12-01'), (3, 2, '2015-12-07'), (3, 3, '2015-12-12')
, (4, 1, '2015-12-01'), (4, 2, '2015-12-01'), (4, 3, '2015-12-02'), (4, 4, '2015-12-03'), (4, 5, '2015-12-03')
;
INSERT INTO INVITE (IdInvite, NomInvite) VALUES (1, 'Fernand'), (2, 'Raoul'), (3, 'Paul')
;
-- Raoul s'inscrit pour la réservation <2, 3>, dans le menu 2 il choisit l'entrée 14, le plat ppal 21 et le dessert 32 :
INSERT INTO INSCRIPTION (IdMenu, IdReception, IdInvite, IdEntree, IdPlatPpal, IdDessert) VALUES (2, 1, 1, 14, 21, 32) ;
Partager