62 lines
1.4 KiB
Plaintext
62 lines
1.4 KiB
Plaintext
CREATE TABLE club (
|
|
nom VARCHAR(100) PRIMARY KEY,
|
|
adresse VARCHAR(255)
|
|
);
|
|
|
|
|
|
CREATE TABLE adherent (
|
|
code INT PRIMARY KEY,
|
|
nom VARCHAR(100),
|
|
prenom VARCHAR(100),
|
|
ville VARCHAR(100),
|
|
tel VARCHAR(20),
|
|
club VARCHAR(100),
|
|
FOREIGN KEY (club) REFERENCES club(nom)
|
|
);
|
|
|
|
CREATE TABLE respo (
|
|
club VARCHAR(100) PRIMARY KEY,
|
|
president INT,
|
|
tresorier INT,
|
|
FOREIGN KEY (club) REFERENCES club(nom),
|
|
FOREIGN KEY (president) REFERENCES adherent(code),
|
|
FOREIGN KEY (tresorier) REFERENCES adherent(code)
|
|
);
|
|
|
|
CREATE TABLE competition (
|
|
code INT PRIMARY KEY,
|
|
date DATE,
|
|
organisateur VARCHAR(100),
|
|
FOREIGN KEY (organisateur) REFERENCES club(nom)
|
|
);
|
|
|
|
CREATE TABLE equipe (
|
|
code INT PRIMARY KEY,
|
|
participation INT,
|
|
capitaine INT,
|
|
FOREIGN KEY (participation) REFERENCES competition(code),
|
|
FOREIGN KEY (capitaine) REFERENCES adherent(code)
|
|
);
|
|
|
|
CREATE TABLE joueur (
|
|
team INT,
|
|
player INT,
|
|
PRIMARY KEY (team, player),
|
|
FOREIGN KEY (team) REFERENCES equipe(code),
|
|
FOREIGN KEY (player) REFERENCES adherent(code)
|
|
);
|
|
|
|
CREATE TABLE sponsor (
|
|
code INT PRIMARY KEY,
|
|
nom VARCHAR(100),
|
|
ville VARCHAR(100)
|
|
);
|
|
|
|
CREATE TABLE financement (
|
|
qui INT,
|
|
quoi INT,
|
|
montant DECIMAL(10,2),
|
|
PRIMARY KEY (qui, quoi),
|
|
FOREIGN KEY (qui) REFERENCES sponsor(code),
|
|
FOREIGN KEY (quoi) REFERENCES competition(code)
|
|
); |