Create formula1.sql

1 files changed, 370 insertions, 0 deletions

diff --git a/formula1.sql b/formula1.sql
new file mode 100644
index 0000000..5e85c95
--- /dev/null
+++ b/formula1.sql
@@ -0,0 +1,370 @@
+-- MySQL Workbench Forward Engineering
+
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
+
+-- -----------------------------------------------------
+-- Schema mydb
+-- -----------------------------------------------------
+-- -----------------------------------------------------
+-- Schema formula1
+-- -----------------------------------------------------
+DROP SCHEMA IF EXISTS `formula1` ;
+
+-- -----------------------------------------------------
+-- Schema formula1
+-- -----------------------------------------------------
+CREATE SCHEMA IF NOT EXISTS `formula1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
+USE `formula1` ;
+
+-- -----------------------------------------------------
+-- Table `formula1`.`calender`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`circuit`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`function`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`member`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`specialposition`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`endposition`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`fastestlap`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`racetype`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`racedate`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`race`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`endpositionrace`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`nationality`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`membernationality`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`racedatecircuit`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`teams`
+-- -----------------------------------------------------
+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)
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+-- -----------------------------------------------------
+-- Table `formula1`.`teamsmember`
+-- -----------------------------------------------------
+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`))
+ENGINE = InnoDB
+DEFAULT CHARACTER SET = utf8mb4
+COLLATE = utf8mb4_0900_ai_ci;
+
+
+SET SQL_MODE=@OLD_SQL_MODE;
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;