diff options
| -rw-r--r-- | formula1.sql | 370 |
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; |