CREATE TABLE clubs(
        club_id int not null AUTO_INCREMENT,
        club_name varchar(50) not null,
        club_alias varchar(50) not null,
        PRIMARY KEY(club_id)
)ENGINE=INNODB;

CREATE TABLE teams(
        team_id int not null AUTO_INCREMENT,
        team_name varchar(100) not null,
        club_id int not null,
        PRIMARY KEY(team_id),
        FOREIGN KEY (club_id) REFERENCES clubs(club_id) ON DELETE CASCADE
)ENGINE=INNODB;

CREATE TABLE roles(
        role_id int not null,
        role_name int not null,
        PRIMARY KEY(role_id)
) ENGINE=INNODB;

CREATE TABLE persons(
        person_id int not null AUTO_INCREMENT,
        role_id int not null,
        person_first_name varchar(30) not null,
        person_surname varchar(30) not null,
        person_address varchar(60) not null,
        person_city varchar(30) not null,
        person_postcode varchar(10),
        person_dob date,
        person_phone varchar(30),
        person_email varchar(128),
        person_gender char(1) not null,
        PRIMARY KEY(person_id),
        FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
        ) ENGINE=INNODB;

CREATE TABLE person_roles(
        person_id int not null,
        role_id int not null,
        FOREIGN KEY (person_id) REFERENCES persons(person_id) ON DELETE CASCADE,
        FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
) ENGINE=INNODB;