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 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
| CREATE TABLE YC_type_compet(
YC_ident INT AUTO_INCREMENT,
YC_code CHAR(4) NOT NULL,
YC_lib VARCHAR(128) NOT NULL,
PRIMARY KEY(YC_ident),
UNIQUE(YC_code)
);
CREATE TABLE TR_terrain(
TR_ident INT AUTO_INCREMENT,
TR_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(TR_ident)
);
CREATE TABLE CL_club(
CL_ident INT AUTO_INCREMENT,
CL_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(CL_ident)
);
CREATE TABLE EQ_equipe(
EQ_ident INT AUTO_INCREMENT,
EQ_nom VARCHAR(50) NOT NULL,
CL_ident INT NOT NULL,
PRIMARY KEY(EQ_ident),
FOREIGN KEY(CL_ident) REFERENCES CL_club(CL_ident)
);
CREATE TABLE PE_personne(
PE_ident INT AUTO_INCREMENT,
PE_nom VARCHAR(50) NOT NULL,
PE_prenom VARCHAR(50) NOT NULL,
PE_ddn DATE NOT NULL,
PRIMARY KEY(PE_ident)
);
CREATE TABLE TN_entraineur(
PE_ident INT,
TN_agrement CHAR(10),
PRIMARY KEY(PE_ident),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE JO_joueur(
PE_ident INT,
JO_license CHAR(12) NOT NULL,
PRIMARY KEY(PE_ident),
UNIQUE(JO_license),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE AR_arbitre(
PE_ident INT,
AR_license CHAR(8) NOT NULL,
PRIMARY KEY(PE_ident),
UNIQUE(AR_license),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE RO_role(
RO_ident INT AUTO_INCREMENT,
RO_code CHAR(4) NOT NULL,
RO_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(RO_ident),
UNIQUE(RO_code)
);
CREATE TABLE CO_competition(
CO_ident INT AUTO_INCREMENT,
CO_nom VARCHAR(50) NOT NULL,
YC_ident INT NOT NULL,
PRIMARY KEY(CO_ident),
FOREIGN KEY(YC_ident) REFERENCES YC_type_compet(YC_ident)
);
CREATE TABLE CS_comp_saison(
CO_ident INT,
CS_ident SMALLINT,
CS_dtdeb DATE NOT NULL,
CS_dtfin DATE NOT NULL,
PRIMARY KEY(CO_ident, CS_ident),
FOREIGN KEY(CO_ident) REFERENCES CO_competition(CO_ident)
);
CREATE TABLE RC_rencontre(
CO_ident INT,
CS_ident SMALLINT,
RC_ident SMALLINT,
RC_date DATE NOT NULL,
TR_ident INT NOT NULL,
PRIMARY KEY(CO_ident, CS_ident, RC_ident),
FOREIGN KEY(CO_ident, CS_ident) REFERENCES CS_comp_saison(CO_ident, CS_ident),
FOREIGN KEY(TR_ident) REFERENCES TR_terrain(TR_ident)
);
CREATE TABLE TO_tour(
CO_ident INT,
CS_ident SMALLINT,
TO_ident TINYINT,
TO_dtdeb DATE NOT NULL,
TO_dtfin DATE NOT NULL,
PRIMARY KEY(CO_ident, CS_ident, TO_ident),
FOREIGN KEY(CO_ident, CS_ident) REFERENCES CS_comp_saison(CO_ident, CS_ident)
);
CREATE TABLE JR_jouer(
CO_ident INT,
CS_ident SMALLINT,
RC_ident SMALLINT,
EQ_ident INT,
JR_score SMALLINT NOT NULL,
PRIMARY KEY(CO_ident, CS_ident, RC_ident, EQ_ident),
FOREIGN KEY(CO_ident, CS_ident, RC_ident) REFERENCES RC_rencontre(CO_ident, CS_ident, RC_ident),
FOREIGN KEY(EQ_ident) REFERENCES EQ_equipe(EQ_ident)
);
CREATE TABLE IN_inscrire(
CO_ident INT,
CS_ident SMALLINT,
EQ_ident INT,
PRIMARY KEY(CO_ident, CS_ident, EQ_ident),
FOREIGN KEY(CO_ident, CS_ident) REFERENCES CS_comp_saison(CO_ident, CS_ident),
FOREIGN KEY(EQ_ident) REFERENCES EQ_equipe(EQ_ident)
);
CREATE TABLE CT_constituer(
PE_ident INT,
CA_date DATE,
CT_dtfin DATE NOT NULL,
EQ_ident INT NOT NULL,
PRIMARY KEY(PE_ident, CA_date),
FOREIGN KEY(PE_ident) REFERENCES JO_joueur(PE_ident),
FOREIGN KEY(EQ_ident) REFERENCES EQ_equipe(EQ_ident)
);
CREATE TABLE AB_arbitrer(
CO_ident INT,
CS_ident SMALLINT,
RC_ident SMALLINT,
PE_ident INT,
AB_tsdeb DATETIME NOT NULL,
AB_tsfin DATETIME NOT NULL,
RO_ident INT NOT NULL,
PRIMARY KEY(CO_ident, CS_ident, RC_ident, PE_ident),
FOREIGN KEY(CO_ident, CS_ident, RC_ident) REFERENCES RC_rencontre(CO_ident, CS_ident, RC_ident),
FOREIGN KEY(PE_ident) REFERENCES AR_arbitre(PE_ident),
FOREIGN KEY(RO_ident) REFERENCES RO_role(RO_ident)
);
CREATE TABLE PA_participer(
CO_ident INT,
CS_ident SMALLINT,
RC_ident SMALLINT,
PE_ident INT,
PA_mindeb SMALLINT NOT NULL,
PA_minfin SMALLINT NOT NULL,
PRIMARY KEY(CO_ident, CS_ident, RC_ident, PE_ident),
FOREIGN KEY(CO_ident, CS_ident, RC_ident) REFERENCES RC_rencontre(CO_ident, CS_ident, RC_ident),
FOREIGN KEY(PE_ident) REFERENCES JO_joueur(PE_ident)
);
alter table JR_jouer
add constraint JRFK001
foreign key (CO_ident, CS_ident, EQ_ident))
references IN_inscrire((CO_ident, CS_ident, EQ_ident)
; |
Partager