drop schema if exists `formula1`;
create schema if not exists `formula1`;
use `formula1`;

drop table if exists `formula1`.`calender`;
create table if not exists `formula1`.`calender` (
  `ID` int not null auto_increment,
  `year` year not null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  unique index `year_UNIQUE` (`year` asc) visible);

drop table if exists `formula1`.`circuit`;
create table if not exists `formula1`.`circuit` (
  `ID` int not null auto_increment,
  `name` varchar(45) null default null,
  `length` int null default null,
  `photo` mediumblob null default null,
  `laps` int null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible);

drop table if exists `formula1`.`function`;
create table if not exists `formula1`.`function` (
  `ID` int not null auto_increment,
  `function` varchar(45) null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible);

drop table if exists `formula1`.`member`;
create table if not exists `formula1`.`member` (
  `ID` int not null auto_increment,
  `firstName` varchar(45) null default null,
  `middleName` varchar(31) null default null,
  `lastName` varchar(45) null default null,
  `photo` mediumblob null default null,
  `functionID` int null default null,
  `membercol` varchar(45) null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `functionID_idx` (`functionID` asc) visible,
  constraint `functionID`
    foreign key (`functionID`)
    references `formula1`.`function` (`ID`)
    on update cascade);

drop table if exists `formula1`.`specialposition`;
create table if not exists `formula1`.`specialposition` (
  `ID` int not null auto_increment,
  `type` varchar(45) null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible);

drop table if exists `formula1`.`endposition`;
create table if not exists `formula1`.`endposition` (
  `ID` int not null auto_increment,
  `memberID` int null default null,
  `position` int null default null,
  `specialPoistionID` int null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `memberIDEndPosition_idx` (`memberID` asc) visible,
  index `specialPositionID_idx` (`specialPoistionID` asc) visible,
  constraint `memberIDEndPosition`
    foreign key (`memberID`)
    references `formula1`.`member` (`ID`)
    on update cascade,
  constraint `specialPositionID`
    foreign key (`specialPoistionID`)
    references `formula1`.`specialposition` (`ID`)
    on update cascade);

drop table if exists `formula1`.`fastestlap`;
create table if not exists `formula1`.`fastestlap` (
  `ID` int not null auto_increment,
  `memberID` int null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `memberIDfastestlap_idx` (`memberID` asc) visible,
  constraint `memberIDfastestlap`
    foreign key (`memberID`)
    references `formula1`.`member` (`ID`)
    on update cascade);

drop table if exists `formula1`.`racetype`;
create table if not exists `formula1`.`racetype` (
  `ID` int not null auto_increment,
  `raceType` varchar(45) not null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible);

drop table if exists `formula1`.`racedate`;
create table if not exists `formula1`.`racedate` (
  `ID` int not null auto_increment,
  `calanderID` int null default null,
  `raceTypeID` int null default null,
  `week` int not null,
  `date` date not null,
  primary key (`ID`),
  index `calanderID_idx` (`calanderID` asc) visible,
  index `raceTypeID_idx` (`raceTypeID` asc) visible,
  constraint `calanderID`
    foreign key (`calanderID`)
    references `formula1`.`calender` (`ID`)
    on update cascade,
  constraint `raceTypeID`
    foreign key (`raceTypeID`)
    references `formula1`.`racetype` (`ID`)
    on update cascade);

drop table if exists `formula1`.`race`;
create table if not exists `formula1`.`race` (
  `ID` int not null auto_increment,
  `raceDateID` int null default null,
  `raceNumber` int null default null,
  `fastestLapID` int null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  unique index `raceNumber_UNIQUE` (`raceNumber` asc) visible,
  index `raceDateID_idx` (`raceDateID` asc) visible,
  index `fastestLapID_idx` (`fastestLapID` asc) visible,
  constraint `fastestLapID`
    foreign key (`fastestLapID`)
    references `formula1`.`fastestlap` (`ID`)
    on update cascade,
  constraint `raceDateIDRace`
    foreign key (`raceDateID`)
    references `formula1`.`racedate` (`ID`)
    on update cascade);

drop table if exists `formula1`.`endpositionrace`;
create table if not exists `formula1`.`endpositionrace` (
  `ID` int not null auto_increment,
  `endPositionID` int null default null,
  `raceID` int null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `endPositionID_idx` (`endPositionID` asc) visible,
  index `raceID_idx` (`raceID` asc) visible,
  constraint `endPositionID`
    foreign key (`endPositionID`)
    references `formula1`.`endposition` (`ID`)
    on update cascade,
  constraint `raceID`
    foreign key (`raceID`)
    references `formula1`.`race` (`ID`)
    on update cascade);

drop table if exists `formula1`.`nationality`;
create table if not exists `formula1`.`nationality` (
  `ID` int not null auto_increment,
  `country` varchar(45) null default null,
  `flag` mediumblob null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible);

drop table if exists `formula1`.`membernationality`;
create table if not exists `formula1`.`membernationality` (
  `ID` int not null auto_increment,
  `memberID` int null default null,
  `nationalityID` int null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `memberID_idx` (`memberID` asc) visible,
  index `nationalityID_idx` (`nationalityID` asc) visible,
  constraint `memberIDNationality`
    foreign key (`memberID`)
    references `formula1`.`member` (`ID`)
    on update cascade,
  constraint `nationalityID`
    foreign key (`nationalityID`)
    references `formula1`.`nationality` (`ID`)
    on update cascade);

drop table if exists `formula1`.`racedatecircuit`;
create table if not exists `formula1`.`racedatecircuit` (
  `ID` int not null auto_increment,
  `raceDateID` int null default null,
  `circuitID` int null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `circuitID_idx` (`circuitID` asc) visible,
  index `raceDateID_idx` (`raceDateID` asc) visible,
  constraint `circuitID`
    foreign key (`circuitID`)
    references `formula1`.`circuit` (`ID`)
    on update cascade,
  constraint `raceDateID`
    foreign key (`raceDateID`)
    references `formula1`.`racedate` (`ID`)
    on update cascade);

drop table if exists `formula1`.`teams`;
create table if not exists `formula1`.`teams` (
  `ID` int not null auto_increment,
  `calanderID` int null default null,
  `teamNumber` int null default null,
  `teamName` varchar(45) null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `calanderID_idx` (`calanderID` asc) visible,
  constraint `calanderID2`
    foreign key (`calanderID`)
    references `formula1`.`calender` (`ID`)
    on update cascade);

drop table if exists `formula1`.`teamsmember`;
create table if not exists `formula1`.`teamsmember` (
  `ID` int not null auto_increment,
  `teamsID` int null default null,
  `memberID` int null default null,
  `teamsMembercol` varchar(45) null default null,
  primary key (`ID`),
  unique index `ID_UNIQUE` (`ID` asc) visible,
  index `teamsID_idx` (`teamsID` asc) visible,
  index `memberIDteams_idx` (`memberID` asc) visible,
  constraint `memberIDteams`
    foreign key (`memberID`)
    references `formula1`.`member` (`ID`),
  constraint `teamsID`
    foreign key (`teamsID`)
    references `formula1`.`teams` (`ID`));