CREATE TABLE HIPPODROME
(
HippodromeId INT NOT NULL
, HippodromeNom VARCHAR(48) NOT NULL
, CONSTRAINT HIPPODROME_PK PRIMARY KEY (HippodromeId)
) ;
CREATE TABLE TERRAIN
(
TerrainId INT NOT NULL
, TerrainEtat VARCHAR(16) NOT NULL
, CONSTRAINT TERRAIN_PK PRIMARY KEY (TerrainId)
) ;
CREATE TABLE CHEVAL
(
ChevalId INT NOT NULL
, ChevalNom VARCHAR(48) NOT NULL
, ChevalDateNaissance DATE NOT NULL
, ChevalSexe CHAR(1) NOT NULL
, CONSTRAINT CHEVAL_PK PRIMARY KEY (ChevalId)
) ;
CREATE TABLE JOCKEY
(
JockeyId INT NOT NULL
, JockeyNom VARCHAR(48) NOT NULL
, CONSTRAINT JOCKEY_PK PRIMARY KEY (JockeyId)
) ;
CREATE TABLE RANG
(
RangId INT NOT NULL
, RangLibelle VARCHAR(48) NOT NULL
, CONSTRAINT RANG_PK PRIMARY KEY (RangId)
) ;
CREATE TABLE PRIX
(
PrixId INT NOT NULL
, PrixNom VARCHAR(48) NOT NULL
, CONSTRAINT PRIX_PK PRIMARY KEY (PrixId)
) ;
CREATE TABLE REUNION
(
ReunionId INT NOT NULL
, TerrainId INT NOT NULL
, HippodromeId INT NOT NULL
, ReunionNumero INT NOT NULL
, ReunionDate DATE NOT NULL
, CONSTRAINT REUNION_PK PRIMARY KEY (ReunionId)
, CONSTRAINT REUNION_AK UNIQUE (ReunionNumero)
, CONSTRAINT REUNION_TERRAIN_FK FOREIGN KEY (TerrainId)
REFERENCES TERRAIN
, CONSTRAINT REUNION_HIPPODROME_FK FOREIGN KEY (HippodromeId)
REFERENCES HIPPODROME (HippodromeId)
) ;
CREATE TABLE COURSE
(
ReunionId INT NOT NULL
, CourseId INT NOT NULL
, PrixId INT NOT NULL
, CourseNumero INT NOT NULL
, CourseDuree TIME NOT NULL
, CourseAllocation INT NOT NULL
, CONSTRAINT COURSE_PK PRIMARY KEY (ReunionId, CourseId)
, CONSTRAINT COURSE_AK UNIQUE (ReunionId, CourseNumero)
, CONSTRAINT COURSE_REUNION_FK FOREIGN KEY (ReunionId)
REFERENCES REUNION
, CONSTRAINT COURSE_PRIX_FK FOREIGN KEY (PrixId)
REFERENCES PRIX (PrixId)
) ;
CREATE TABLE SIMPLE
(
ReunionId INT NOT NULL
, CourseId INT NOT NULL
, Rapport_Gagnant INT NOT NULL
, Rapport_Place_1 INT NOT NULL
, Rapport_Place_2 INT NOT NULL
, Rapport_Place_3 INT NOT NULL
, CONSTRAINT SIMPLE_PK PRIMARY KEY (ReunionId, CourseId)
, CONSTRAINT SIMPLE_COURSE_FK FOREIGN KEY (ReunionId, CourseId)
REFERENCES COURSE (ReunionId, CourseId)
ON DELETE CASCADE
) ;
CREATE TABLE QUINTE
(
ReunionId INT NOT NULL
, CourseId INT NOT NULL
, Rapport_Ordre DECIMAL(9,2) NOT NULL
, Rapport_Desordre DECIMAL(9,2) NOT NULL
, CONSTRAINT QUINTE_PK PRIMARY KEY (ReunionId, CourseId)
, CONSTRAINT QUINTE_COURSE_FK FOREIGN KEY (ReunionId, CourseId)
REFERENCES COURSE (ReunionId, CourseId)
ON DELETE CASCADE
) ;
CREATE TABLE PARTANT
(
ReunionId INT NOT NULL
, CourseId INT NOT NULL
, ChevalId INT NOT NULL
, JockeyId INT NOT NULL
, RangId INT NOT NULL
, PartantNumero INT NOT NULL
, PartantCote VARCHAR(16) NOT NULL
, PartantPoids DECIMAL(4,2) NOT NULL
, PartantCommentaire VARCHAR(256) NOT NULL
, CONSTRAINT PARTANT_PK PRIMARY KEY (ReunionId, CourseId, ChevalId)
, CONSTRAINT PARTANT_JOCKEY_AK UNIQUE (ReunionId, CourseId, JockeyId)
, CONSTRAINT PARTANT_NUMERO_AK UNIQUE (ReunionId, CourseId, PartantNumero)
, CONSTRAINT PARTANT_COURSE_FK FOREIGN KEY (ReunionId, CourseId)
REFERENCES COURSE (ReunionId, CourseId)
, CONSTRAINT PARTANT_CHEVAL_FK FOREIGN KEY (ChevalId)
REFERENCES CHEVAL (ChevalId)
, CONSTRAINT PARTANT_JOCKEY_FK FOREIGN KEY (JockeyId)
REFERENCES JOCKEY (JockeyId)
, CONSTRAINT PARTANT_RANG_FK FOREIGN KEY (RangId)
REFERENCES RANG (RangId)
) ;
courses hippiques
Répondre avec citation
Envoyé par lazare
Quinte_Simple_MCD.jpg)
Reunion.jpg)
Partant_MCD.jpg)
Partant_MLD.jpg)
Course_AK.jpg)
Partant_AK.jpg)
Partant_AK_1_2.jpg)
Terrain_MCD.jpg)
Terrain_MLD.jpg)
?
Partager