aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSean <sb.groenenboom@student.avans.nl>2023-02-27 10:33:53 +0100
committerSean <sb.groenenboom@student.avans.nl>2023-02-27 10:33:53 +0100
commit50093f48248f67924b4e8a1283d59a7b8542dd11 (patch)
treee6a26162951a29d7181d9212bfb5aba158e136d9
parentfdaf25aa0afc2a3dfcad8b8d3bc14af676bc7f29 (diff)
Create formula1.sql
-rw-r--r--formula1.sql370
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;