USE temp ;
SET default_storage_engine=InnoDB ;
DROP TABLE IF EXISTS UTILISE_OUTIL ;
DROP TABLE IF EXISTS UTILISE_PIECE ;
DROP TABLE IF EXISTS PIECE_DEMANTELEE ;
DROP TABLE IF EXISTS REMUNERATION ;
DROP TABLE IF EXISTS PARTICIPE ;
DROP TABLE IF EXISTS PIECE ;
DROP TABLE IF EXISTS OUTIL ;
DROP TABLE IF EXISTS EFFECTUE ;
DROP TABLE IF EXISTS COLLAB_DON ;
DROP TABLE IF EXISTS AU_COURS ;
DROP TABLE IF EXISTS EVENEMENT_INTERNE ;
DROP TABLE IF EXISTS REPARATION ;
DROP TABLE IF EXISTS APPAREIL ;
DROP TABLE IF EXISTS ADH_FINANCEMENT ;
DROP TABLE IF EXISTS FINANCEMENT ;
DROP TABLE IF EXISTS EVENEMENT_EXTERNE ;
DROP TABLE IF EXISTS EVENEMENT ;
DROP TABLE IF EXISTS TYPE_EVENEMENT ;
DROP TABLE IF EXISTS COLLABORATEUR ;
DROP TABLE IF EXISTS TYPE_COLLAB ;
DROP TABLE IF EXISTS ACHAT ;
DROP TABLE IF EXISTS DEPENSE ;
DROP TABLE IF EXISTS RECETTE_VENTE ;
DROP TABLE IF EXISTS OBJET ;
DROP TABLE IF EXISTS ADH_DON ;
DROP TABLE IF EXISTS ADHERENT ;
-- -----------------------------------------------------------------------------------------------
CREATE TABLE ADHERENT
(
id_adherent INT NOT NULL,
nom_adherent VARCHAR(32) NOT NULL,
prenom_adherent VARCHAR(32) NOT NULL,
CONSTRAINT ADHERENT_PK PRIMARY KEY (id_adherent)
) ;
CREATE TABLE ADH_DON
(
id_adherent INT NOT NULL,
date_adh_don DATE NOT NULL,
montant_adh_don DECIMAL(7,2) NOT NULL,
commentaires_adh_don VARCHAR(64) NOT NULL,
CONSTRAINT ADH_DON_PK PRIMARY KEY (id_adherent),
CONSTRAINT ADH_DON_ADHERENT_FK FOREIGN KEY (id_adherent)
REFERENCES ADHERENT (id_adherent)
) ;
CREATE TABLE OBJET
(
id_objet INT NOT NULL,
id_adherent INT NOT NULL,
origine_obj VARCHAR(32) NOT NULL,
etat_obj VARCHAR(32) NOT NULL,
localisation_obj VARCHAR(32) NOT NULL,
commentaire_obj VARCHAR(64) NOT NULL,
CONSTRAINT OBJET_PK PRIMARY KEY (id_objet),
CONSTRAINT OBJET_ADHERENT_FK FOREIGN KEY (id_adherent)
REFERENCES ADHERENT (id_adherent)
) ;
CREATE TABLE RECETTE_VENTE
(
id_objet INT NOT NULL,
date_vente DATE NOT NULL,
montant_vente DECIMAL(7,2) NOT NULL,
commentaires_vente VARCHAR(64) NOT NULL,
CONSTRAINT RECETTE_VENTE_PK PRIMARY KEY (id_objet),
CONSTRAINT RECETTE_VENTE_OBJET_FK FOREIGN KEY (id_objet)
REFERENCES OBJET (id_objet)
) ;
CREATE TABLE DEPENSE
(
id_depense INT NOT NULL,
id_adherent_gestion INT NOT NULL,
date_depense DATE NOT NULL,
montant_depense INT NOT NULL,
commentaire_depense VARCHAR(64) NOT NULL,
CONSTRAINT DEPENSE_PK PRIMARY KEY (id_depense),
CONSTRAINT DEPENSE_ADHERENT_FK FOREIGN KEY (id_adherent_gestion)
REFERENCES ADHERENT (id_adherent)
) ;
CREATE TABLE REMUNERATION
(
id_depense INT NOT NULL,
id_adherent INT NOT NULL,
CONSTRAINT REMUNERATION_PK PRIMARY KEY (id_depense),
CONSTRAINT REMUNERATION_ADHERENT_FK FOREIGN KEY (id_adherent)
REFERENCES ADHERENT (id_adherent),
CONSTRAINT REMUNERATION_DEPENSE_FK FOREIGN KEY (id_depense)
REFERENCES DEPENSE (id_depense) ON DELETE CASCADE
) ;
CREATE TABLE ACHAT
(
id_depense INT NOT NULL,
id_objet INT NOT NULL,
CONSTRAINT ACHAT_PK PRIMARY KEY (id_depense),
CONSTRAINT ACHAT_OBJET_FK FOREIGN KEY (id_objet)
REFERENCES OBJET (id_objet),
CONSTRAINT ACHAT_DEPENSE_FK FOREIGN KEY (id_depense)
REFERENCES DEPENSE (id_depense)
) ;
CREATE TABLE TYPE_COLLAB
(
id_type_collab INT NOT NULL,
libelle_type_collab VARCHAR(32) NOT NULL,
CONSTRAINT TYPE_COLLAB_PK PRIMARY KEY (id_type_collab)
) ;
CREATE TABLE COLLABORATEUR
(
id_collab INT NOT NULL,
id_type_collab INT NOT NULL,
nom_collab VARCHAR(32) NOT NULL,
mail_collab VARCHAR(64) NOT NULL,
CONSTRAINT COLLABORATEUR_PK PRIMARY KEY (id_collab),
CONSTRAINT COLLABORATEUR_TYPE_COLLAB_FK FOREIGN KEY (id_type_collab)
REFERENCES TYPE_COLLAB (id_type_collab)
) ;
CREATE TABLE COLLAB_DON
(
id_collab INT NOT NULL,
id_collab_don INT NOT NULL,
date_collab_don DATE NOT NULL,
montant_collab_don DECIMAL(7,2) NOT NULL,
commentaires_don VARCHAR(64) NOT NULL,
CONSTRAINT COLLAB_DON_PK PRIMARY KEY (id_collab, id_collab_don),
CONSTRAINT COLLAB_DON_COLLABORATEUR_FK FOREIGN KEY (id_collab)
REFERENCES COLLABORATEUR (id_collab)
) ;
CREATE TABLE TYPE_EVENEMENT
(
id_type_evt INT NOT NULL,
libelle_type_evt VARCHAR(64) NOT NULL,
CONSTRAINT TYPE_EVENEMENT_PK PRIMARY KEY (id_type_evt)
) ;
CREATE TABLE EVENEMENT
(
id_evt INT NOT NULL,
id_type_evt INT NOT NULL,
date_evt DATE NOT NULL,
heure_evt TIME NOT NULL,
lieu_evt VARCHAR(64) NOT NULL,
adresse_evt VARCHAR(64) NOT NULL,
url_evt VARCHAR(64) NOT NULL,
capacite_evt INT NOT NULL,
prix_evt DECIMAL(7,2) NOT NULL,
commentaire_evt VARCHAR(64) NOT NULL,
CONSTRAINT EVENEMENT_PK PRIMARY KEY (id_evt),
CONSTRAINT EVENEMENT_TYPE_EVENEMENT_FK FOREIGN KEY (id_type_evt)
REFERENCES TYPE_EVENEMENT (id_type_evt)
) ;
CREATE TABLE EVENEMENT_EXTERNE
(
id_evt INT NOT NULL,
id_collab INT NOT NULL,
subvention INT NOT NULL,
montant_recette INT NOT NULL,
CONSTRAINT EVENEMENT_EXTERNE_PK PRIMARY KEY (id_evt),
CONSTRAINT EVENEMENT_EXTERNE_COLLABORATEUR_FK FOREIGN KEY (id_collab)
REFERENCES COLLABORATEUR (id_collab),
CONSTRAINT EVENEMENT_EXTERNE_EVENEMENT_FK FOREIGN KEY (id_evt)
REFERENCES EVENEMENT (id_evt) ON DELETE CASCADE
) ;
CREATE TABLE FINANCEMENT
(
id_depense INT NOT NULL,
id_evt INT NOT NULL,
CONSTRAINT FINANCEMENT_PK PRIMARY KEY (id_depense),
CONSTRAINT FINANCEMENT_EVENEMENT_EXTERNE_FK FOREIGN KEY (id_evt)
REFERENCES EVENEMENT_EXTERNE (id_evt),
CONSTRAINT FINANCEMENT_DEPENSE_FK FOREIGN KEY (id_depense)
REFERENCES DEPENSE (id_depense) ON DELETE CASCADE
) ;
CREATE TABLE ADH_FINANCEMENT
(
id_adherent INT NOT NULL,
id_depense INT NOT NULL,
CONSTRAINT ADH_FINANCEMENT_PK PRIMARY KEY (id_adherent, id_depense),
CONSTRAINT ADH_FINANCEMENT_ADHERENT_FK FOREIGN KEY (id_adherent)
REFERENCES ADHERENT (id_adherent),
CONSTRAINT ADH_FINANCEMENT_FINANCEMENT_FK FOREIGN KEY (id_depense)
REFERENCES FINANCEMENT (id_depense) ON DELETE CASCADE
) ;
CREATE TABLE EVENEMENT_INTERNE
(
id_evt INT NOT NULL,
CONSTRAINT EVENEMENT_INTERNE_PK PRIMARY KEY (id_evt),
CONSTRAINT EVENEMENT_INTERNE_EVENEMENT_FK FOREIGN KEY (id_evt)
REFERENCES EVENEMENT (id_evt) ON DELETE CASCADE
) ;
CREATE TABLE PARTICIPE
(
id_evt INT NOT NULL,
id_adherent INT NOT NULL,
montant DECIMAL(5,2) NOT NULL,
CONSTRAINT PARTICIPE_PK PRIMARY KEY (id_evt, id_adherent),
CONSTRAINT PARTICIPE_EVENEMENT_INTERNE_FK FOREIGN KEY (id_evt)
REFERENCES EVENEMENT_INTERNE (id_evt),
CONSTRAINT PARTICIPE_ADHERENT_FK FOREIGN KEY (id_adherent)
REFERENCES ADHERENT (id_adherent) ON DELETE CASCADE
) ;
CREATE TABLE APPAREIL
(
id_appareil INT NOT NULL,
marque VARCHAR(32) NOT NULL,
modele VARCHAR(32) NOT NULL,
prix INT NOT NULL,
en_vente BIT NOT NULL,
CONSTRAINT APPAREIL_PK PRIMARY KEY (id_appareil),
CONSTRAINT APPAREIL_OBJET_FK FOREIGN KEY (id_appareil)
REFERENCES OBJET (id_objet) ON DELETE CASCADE
) ;
CREATE TABLE REPARATION
(
id_reparation INT NOT NULL,
id_appareil INT NOT NULL,
statut INT NOT NULL,
difficulte VARCHAR(32) NOT NULL,
duree INT NOT NULL,
CONSTRAINT REPARATION_PK PRIMARY KEY (id_reparation),
CONSTRAINT REPARATION_APPAREIL_FK FOREIGN KEY (id_appareil)
REFERENCES APPAREIL (id_appareil)
) ;
CREATE TABLE AU_COURS
(
id_evt INT NOT NULL,
id_reparation INT NOT NULL,
CONSTRAINT AU_COURS_PK PRIMARY KEY (id_evt, id_reparation),
CONSTRAINT AU_COURS_EVENEMENT_INTERNE_FK FOREIGN KEY (id_evt)
REFERENCES EVENEMENT_INTERNE (id_evt),
CONSTRAINT AU_COURS_REPARATION_FK FOREIGN KEY (id_reparation)
REFERENCES REPARATION (id_reparation)
) ;
CREATE TABLE EFFECTUE
(
id_adherent INT NOT NULL,
id_reparation INT NOT NULL,
CONSTRAINT EFFECTUE_PK PRIMARY KEY (id_adherent, id_reparation),
CONSTRAINT EFFECTUE_ADHERENT_FK FOREIGN KEY (id_adherent)
REFERENCES ADHERENT (id_adherent) ON DELETE CASCADE,
CONSTRAINT EFFECTUE_REPARATION_FK FOREIGN KEY (id_reparation)
REFERENCES REPARATION (id_reparation)
) ;
CREATE TABLE OUTIL
(
id_outil INT NOT NULL,
utilite VARCHAR(32) NOT NULL,
CONSTRAINT OUTIL_PK PRIMARY KEY (id_outil),
CONSTRAINT OUTIL_OBJET_FK FOREIGN KEY (id_outil)
REFERENCES OBJET (id_objet) ON DELETE CASCADE
) ;
CREATE TABLE PIECE
(
id_piece INT NOT NULL,
marque VARCHAR(32) NOT NULL,
prix INT NOT NULL,
quantite INT NOT NULL,
en_vente BIT NOT NULL,
caracteristique VARCHAR(32) NOT NULL,
CONSTRAINT PIECE_PK PRIMARY KEY (id_piece),
CONSTRAINT PIECE_OBJET_FK FOREIGN KEY (id_piece)
REFERENCES OBJET (id_objet) ON DELETE CASCADE
) ;
CREATE TABLE PIECE_DEMANTELEE
(
id_piece INT NOT NULL,
id_appareil INT NOT NULL,
CONSTRAINT PIECE_DEMANTELEE_PK PRIMARY KEY (id_piece),
CONSTRAINT PIECE_DEMANTELEE_PIECE_FK FOREIGN KEY (id_piece)
REFERENCES PIECE (id_piece) ON DELETE CASCADE,
CONSTRAINT PIECE_DEMANTELEE_APPAREIL_FK FOREIGN KEY (id_appareil)
REFERENCES APPAREIL (id_appareil)
) ;
CREATE TABLE UTILISE_PIECE
(
id_reparation INT NOT NULL,
id_piece INT NOT NULL,
quantite INT NOT NULL,
CONSTRAINT UTILISE_PIECE_PK PRIMARY KEY (id_reparation, id_piece),
CONSTRAINT UTILISE_PIECE_REPARATION_FK FOREIGN KEY (id_reparation)
REFERENCES REPARATION (id_reparation),
CONSTRAINT UTILISE_PIECE_PIECE_FK FOREIGN KEY (id_piece)
REFERENCES PIECE (id_piece) ON DELETE CASCADE
) ;
CREATE TABLE UTILISE_OUTIL
(
id_outil INT NOT NULL,
id_reparation INT NOT NULL,
CONSTRAINT UTILISE_OUTIL_PK PRIMARY KEY (id_outil, id_reparation),
CONSTRAINT UTILISE_OUTIL_OUTIL_FK FOREIGN KEY (id_outil)
REFERENCES OUTIL (id_outil),
CONSTRAINT UTILISE_OUTIL_REPARATION_FK FOREIGN KEY (id_reparation)
REFERENCES REPARATION (id_reparation)
) ;
Association ternaire, ou pas
Répondre avec citation
Envoyé par dazzle
Attention, lors de la transformation du MCD en MLD, donc ensuite de la génération du code SQL, JMerise se plante dans l’ordre des attributs de la clé primaire de la table COLLAB_DON (effet Dagobert), un truc à faire ramer les requêtes.
Partager