Biker SQL

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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema biker
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema biker
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `biker` DEFAULT CHARACTER SET utf8 ;
USE `biker` ;

-- -----------------------------------------------------
-- Table `biker`.`addresses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`addresses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `street` VARCHAR(255) NOT NULL,
  `number` INT NOT NULL,
  `suffix` VARCHAR(6) NULL,
  `postal_code` VARCHAR(6) NOT NULL,
  `city` VARCHAR(255) NOT NULL,
  `country` VARCHAR(255) NOT NULL DEFAULT 'Nederland',
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `firstname` VARCHAR(255) NOT NULL,
  `lastname` VARCHAR(255) NOT NULL,
  `sex` CHAR(1) NULL DEFAULT 'M',
  `date_of_birth` DATE NULL,
  `phonenumber` VARCHAR(20) NULL,
  `addresses_id` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
  INDEX `fk_users_addresses1_idx` (`addresses_id` ASC),
  CONSTRAINT `fk_users_addresses1`
    FOREIGN KEY (`addresses_id`)
    REFERENCES `biker`.`addresses` (`id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`roles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`brands`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`brands` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`bike_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`bike_types` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`bikes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`bikes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `brands_id` INT NOT NULL,
  `bike_types_id` INT NOT NULL,
  `status` CHAR(1) NOT NULL DEFAULT 'A',
  `version` CHAR(1) NOT NULL DEFAULT 'M',
  `current_price` FLOAT NOT NULL,
  `buy_price` FLOAT NOT NULL,
  `repair_date` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_bikes_brands_idx` (`brands_id` ASC),
  INDEX `fk_bikes_bike_types1_idx` (`bike_types_id` ASC),
  CONSTRAINT `fk_bikes_brands`
    FOREIGN KEY (`brands_id`)
    REFERENCES `biker`.`brands` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bikes_bike_types1`
    FOREIGN KEY (`bike_types_id`)
    REFERENCES `biker`.`bike_types` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`users_has_roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`users_has_roles` (
  `users_id` INT NOT NULL,
  `roles_id` INT NOT NULL,
  PRIMARY KEY (`users_id`, `roles_id`),
  INDEX `fk_users_has_roles_roles1_idx` (`roles_id` ASC),
  INDEX `fk_users_has_roles_users1_idx` (`users_id` ASC),
  CONSTRAINT `fk_users_has_roles_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `biker`.`users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_roles_roles1`
    FOREIGN KEY (`roles_id`)
    REFERENCES `biker`.`roles` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`accessories_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`accessories_types` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`accessories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`accessories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `brands_id` INT NOT NULL,
  `accessories_types_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `current_price` FLOAT NOT NULL,
  `buy_price` FLOAT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_accessories_brands1_idx` (`brands_id` ASC),
  INDEX `fk_accessories_accessories_types1_idx` (`accessories_types_id` ASC),
  CONSTRAINT `fk_accessories_brands1`
    FOREIGN KEY (`brands_id`)
    REFERENCES `biker`.`brands` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_accessories_accessories_types1`
    FOREIGN KEY (`accessories_types_id`)
    REFERENCES `biker`.`accessories_types` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`payment_methods`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`payment_methods` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`payments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`payments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `payment_methods_id` INT NOT NULL,
  `amount` FLOAT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_payments_payment_methods1_idx` (`payment_methods_id` ASC),
  CONSTRAINT `fk_payments_payment_methods1`
    FOREIGN KEY (`payment_methods_id`)
    REFERENCES `biker`.`payment_methods` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`rent_agreements`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`rent_agreements` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `users_id` INT NOT NULL,
  `agreement_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` CHAR(1) NOT NULL DEFAULT 'R',
  `policy_number` VARCHAR(255) NOT NULL,
  `start_date` DATETIME NOT NULL,
  `end_date` DATETIME NOT NULL,
  `price` FLOAT NOT NULL,
  `deposit` FLOAT NOT NULL,
  `payments_id` INT NULL,
  PRIMARY KEY (`id`, `users_id`),
  INDEX `fk_rent_agreements_payments1_idx` (`payments_id` ASC),
  INDEX `fk_rent_agreements_users1_idx` (`users_id` ASC),
  CONSTRAINT `fk_rent_agreements_payments1`
    FOREIGN KEY (`payments_id`)
    REFERENCES `biker`.`payments` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rent_agreements_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `biker`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`rent_agreements_has_bikes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`rent_agreements_has_bikes` (
  `rent_agreements_id` INT NOT NULL,
  `bikes_id` INT NOT NULL,
  PRIMARY KEY (`rent_agreements_id`, `bikes_id`),
  INDEX `fk_rent_agreements_has_bikes_bikes1_idx` (`bikes_id` ASC),
  INDEX `fk_rent_agreements_has_bikes_rent_agreements1_idx` (`rent_agreements_id` ASC),
  CONSTRAINT `fk_rent_agreements_has_bikes_rent_agreements1`
    FOREIGN KEY (`rent_agreements_id`)
    REFERENCES `biker`.`rent_agreements` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rent_agreements_has_bikes_bikes1`
    FOREIGN KEY (`bikes_id`)
    REFERENCES `biker`.`bikes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `biker`.`rent_agreements_has_accessories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `biker`.`rent_agreements_has_accessories` (
  `rent_agreements_id` INT NOT NULL,
  `accessories_id` INT NOT NULL,
  PRIMARY KEY (`rent_agreements_id`, `accessories_id`),
  INDEX `fk_rent_agreements_has_accessories_accessories1_idx` (`accessories_id` ASC),
  INDEX `fk_rent_agreements_has_accessories_rent_agreements1_idx` (`rent_agreements_id` ASC),
  CONSTRAINT `fk_rent_agreements_has_accessories_rent_agreements1`
    FOREIGN KEY (`rent_agreements_id`)
    REFERENCES `biker`.`rent_agreements` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rent_agreements_has_accessories_accessories1`
    FOREIGN KEY (`accessories_id`)
    REFERENCES `biker`.`accessories` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;