CREATE TABLE SERVICE
(
service_id CHAR(3) NOT NULL
, service_label VARCHAR(32) NOT NULL
, CONSTRAINT SERVICE_PK PRIMARY KEY (service_id)
) ;
CREATE TABLE PACK
(
pack_id CHAR(3) NOT NULL
, pack_label VARCHAR(32) NOT NULL
, CONSTRAINT PACK_PK PRIMARY KEY (pack_id)
) ;
CREATE TABLE PACK_SERVICE
(
pack_id CHAR(3) NOT NULL
, service_id CHAR(3) NOT NULL
, CONSTRAINT PACK_SERVICE_PK PRIMARY KEY (pack_id, service_id)
, CONSTRAINT PACK_SERVICE_PACK_FK FOREIGN KEY (pack_id) REFERENCES PACK (pack_id)
, CONSTRAINT PACK_SERVICE_SERVICE_FK FOREIGN KEY (service_id) REFERENCES SERVICE (service_id)
) ;
CREATE TABLE USER
(
user_id CHAR(3) NOT NULL
, user_nom VARCHAR(32) NOT NULL
, CONSTRAINT USER_PK PRIMARY KEY (user_id)
) ;
CREATE TABLE USER_PACK_SERVICE
(
user_id CHAR(3) NOT NULL
, pack_id CHAR(3) NOT NULL
, service_id CHAR(3) NOT NULL
, consumed INT NOT NULL
, CONSTRAINT USER_SERVICE_PK PRIMARY KEY (user_id, pack_id, service_id)
, CONSTRAINT USER_SERVICE_USER_FK FOREIGN KEY (user_id) REFERENCES USER (user_id)
, CONSTRAINT USER_SERVICE_PACK_FK FOREIGN KEY (pack_id, service_id) REFERENCES PACK_SERVICE (pack_id, service_id)
) ;
INSERT INTO SERVICE (service_id, service_label) VALUES
('s01', 'service 01'), ('s02', 'service 02'), ('s03', 'service 03'), ('s04', 'service 04'), ('s05', 'service 05')
;
INSERT INTO PACK (pack_id, pack_label) VALUES
('p01', 'pack 01'), ('p02', 'pack 02'), ('p03', 'pack 03'), ('p04', 'pack 04'), ('p05', 'pack 05')
;
INSERT INTO PACK_SERVICE (pack_id, service_id) VALUES
('p01', 's01'), ('p01', 's02'), ('p01', 's03')
, ('p02', 's02'), ('p02', 's03'), ('p02', 's04')
, ('p03', 's03'), ('p03', 's04'), ('p03', 's05')
;
INSERT INTO USER (user_id, user_nom) VALUES
('u01', 'user 01'), ('u02', 'user 02'), ('u03', 'user 03'), ('u04', 'user 04')
;
INSERT INTO USER_PACK_SERVICE (user_id, pack_id, service_id, consumed) VALUES
('u01', 'p03', 's05', 25), ('u02', 'p03', 's03', 10), ('u02', 'p03', 's04', 54), ('u04', 'p01', 's03', 20)
;
SELECT * FROM USER_PACK_SERVICE ;
Partager