c5j6us2q

1CREATE database IF NOT EXISTS Project_DB;
2USE Project_DB;
3CREATE TABLE Football_club (Football_club_id int(10) NOT NULL AUTO_INCREMENT, Football_club_name varchar(100) NOT NULL, Points numeric(19, 0) DEFAULT 0 NOT NULL, Coach_id int(10)  UNIQUE, Stadium_id int(10) UNIQUE, PRIMARY KEY (Football_club_id), INDEX (Football_club_id));
4CREATE TABLE Player (Player_id int(10) NOT NULL AUTO_INCREMENT, First_name varchar(100) NOT NULL, Last_Name varchar(100) NOT NULL, Date_of_birth date NOT NULL, Citizenship varchar(100) NOT NULL, Height numeric(3, 2) NOT NULL, Playing_position varchar(100) NOT NULL, Contract_id int(10) UNIQUE, Football_club_id int(10), PRIMARY KEY (Player_id), INDEX (Player_id));
5CREATE TABLE Coach (Licence_number int(10) NOT NULL unique, First_name varchar(255) NOT NULL, Last_name varchar(255) NOT NULL, Contract_id int(10) UNIQUE, PRIMARY KEY (Licence_number), INDEX (Licence_number));
6CREATE TABLE Matche (Match_id int(10) NOT NULL AUTO_INCREMENT, Date_of_match date NOT NULL, Finished bit(1) DEFAULT 0, Score varchar(100) DEFAULT NULL, PRIMARY KEY (Match_id), INDEX (Match_id));
7CREATE TABLE Goal (Goal_id int(10) NOT NULL AUTO_INCREMENT, Scorer_id int(10), Match_id int(10) NOT NULL, Times TIME NOT NULL, PRIMARY KEY (Goal_id), INDEX (Goal_id));
8CREATE TABLE Stadium (Stadium_id int(10) NOT NULL AUTO_INCREMENT, Stadium_name varchar(200) NOT NULL, City varchar(200) NOT NULL, Capacity numeric(19, 0) NOT NULL, Surface_type varchar(200) NOT NULL, PRIMARY KEY (Stadium_id), INDEX (Stadium_id));
9CREATE TABLE Referee (Licence_number int(10) NOT NULL UNIQUE, First_name varchar(255) NOT NULL, Last_name varchar(255) NOT NULL, PRIMARY KEY (Licence_number), INDEX (Licence_number));
10CREATE TABLE Contract (Contract_id int(10) NOT NULL AUTO_INCREMENT, Start_date date NOT NULL, Finish_date date NOT NULL, Salary numeric(19, 2) NOT NULL, Release_clause numeric(19, 2), Club_id int(10) NOT NULL, PRIMARY KEY (Contract_id), INDEX (Contract_id));
11CREATE TABLE Ban (Ban_id int(10) NOT NULL AUTO_INCREMENT, Typer varchar(255) NOT NULL, Length numeric(2, 0) NOT NULL, Match_id int(10) NOT NULL, Player_id int(10) NOT NULL UNIQUE, PRIMARY KEY (Ban_id), INDEX (Ban_id));
12CREATE TABLE Player_Match (Player_id int(10) NOT NULL, Match_id int(10) NOT NULL);
13CREATE TABLE Referee_Match (Referee_id int(10), Match_id int(10) NOT NULL, INDEX (Match_id));
14CREATE TABLE Football_club_Match (Team_id int(10) NOT NULL, Match_id int(10) NOT NULL, Home bit(1) NOT NULL);
15ALTER TABLE Player ADD CONSTRAINT FKPlayer144548 FOREIGN KEY (Football_club_id) REFERENCES Football_club (Football_club_id);
16ALTER TABLE Football_club ADD CONSTRAINT FKFootballc516198 FOREIGN KEY (Stadium_id) REFERENCES Stadium (Stadium_id);
17ALTER TABLE Player_Match ADD CONSTRAINT FKPlayer_Mat899824 FOREIGN KEY (Player_id) REFERENCES Player (Player_id);
18ALTER TABLE Player_Match ADD CONSTRAINT FKPlayer_Mat369262 FOREIGN KEY (Match_id) REFERENCES Matche (Match_id);
19ALTER TABLE Goal ADD CONSTRAINT FKGoal454177 FOREIGN KEY (Match_id) REFERENCES Matche (Match_id);
20ALTER TABLE Referee_Match ADD CONSTRAINT FKReferee_Ma762228 FOREIGN KEY (Referee_id) REFERENCES Referee (Licence_number);
21ALTER TABLE Referee_Match ADD CONSTRAINT FKReferee_Ma719746 FOREIGN KEY (Match_id) REFERENCES Matche (Match_id);
22ALTER TABLE Goal ADD CONSTRAINT FKGoal889537 FOREIGN KEY (Scorer_id) REFERENCES Player (Player_id);
23ALTER TABLE Ban ADD CONSTRAINT FKBan295771 FOREIGN KEY (Match_id) REFERENCES Matche (Match_id);
24ALTER TABLE Ban ADD CONSTRAINT FKBan973315 FOREIGN KEY (Player_id) REFERENCES Player (Player_id);
25ALTER TABLE Football_club_Match ADD CONSTRAINT FKFootballc235121 FOREIGN KEY (Match_id) REFERENCES Matche (Match_id);
26ALTER TABLE Coach ADD CONSTRAINT FKCoach160776 FOREIGN KEY (Contract_id) REFERENCES Contract (Contract_id);
27ALTER TABLE Football_club ADD CONSTRAINT FKFootballc384423 FOREIGN KEY (Coach_id) REFERENCES Coach (Licence_number);
28ALTER TABLE Player ADD CONSTRAINT FKPlayer644054 FOREIGN KEY (Contract_id) REFERENCES Contract (Contract_id);
29ALTER TABLE Football_club_Match ADD CONSTRAINT FKFootballc693667 FOREIGN KEY (Team_id) REFERENCES Football_club (Football_club_id);