-- MySQL Script generated by MySQL Workbench
-- Mon Nov 20 15:28:21 2017
-- Model: New Model    Version: 1.0
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema elaverty09
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `elaverty09`.`building`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`building` (
  `building_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `address_number` VARCHAR(5) NULL,
  `address_street` VARCHAR(45) NULL,
  `address_postcode` VARCHAR(45) NULL,
  PRIMARY KEY (`building_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`apartment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`apartment` (
  `building_id` INT NOT NULL,
  `apartment_number` SMALLINT NOT NULL,
  `num_bedrooms` TINYINT NULL,
  `num_bathrooms` TINYINT NULL,
  `area` SMALLINT NULL,
  PRIMARY KEY (`building_id`, `apartment_number`),
  INDEX `fk_apartment_building_id_idx` (`building_id` ASC),
  INDEX `fk_apartment_apartment_number_idx` (`apartment_number` ASC),
  CONSTRAINT `fk_apartment_building_id`
    FOREIGN KEY (`building_id`)
    REFERENCES `elaverty09`.`building` (`building_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`person`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`person` (
  `person_id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `emergency_contact_name` VARCHAR(45) NULL,
  `emergency_contact_phone` VARCHAR(20) NULL,
  PRIMARY KEY (`person_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`guest`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`guest` (
  `person_id` INT NOT NULL,
  PRIMARY KEY (`person_id`),
  CONSTRAINT `fk_guest_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`person` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`guest_agreement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`guest_agreement` (
  `guest_agreement_id` INT NOT NULL AUTO_INCREMENT,
  `building_id` INT NOT NULL,
  `apartment_number` SMALLINT NOT NULL,
  `start_date` DATE NULL,
  `expected_duration` DATE NULL,
  PRIMARY KEY (`guest_agreement_id`),
  INDEX `fk_guest_agreement_building_id_idx` (`building_id` ASC),
  INDEX `fk_guest_agreement_apartment_number_idx` (`apartment_number` ASC),
  CONSTRAINT `fk_guest_agreement_building_id`
    FOREIGN KEY (`building_id`)
    REFERENCES `elaverty09`.`apartment` (`building_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_guest_agreement_apartment_number`
    FOREIGN KEY (`apartment_number`)
    REFERENCES `elaverty09`.`apartment` (`apartment_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`guest_agreement_guests`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`guest_agreement_guests` (
  `guest_agreement_id` INT NOT NULL,
  `person_id` INT NOT NULL,
  PRIMARY KEY (`guest_agreement_id`, `person_id`),
  INDEX `fk_person_id_idx` (`person_id` ASC),
  CONSTRAINT `fk_guest_agreement_id`
    FOREIGN KEY (`guest_agreement_id`)
    REFERENCES `elaverty09`.`guest_agreement` (`guest_agreement_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_guest_agreement_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`guest` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`employee` (
  `person_id` INT NOT NULL,
  `salary` DECIMAL(19,4) NULL,
  PRIMARY KEY (`person_id`),
  CONSTRAINT `fk_employee_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`person` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`technician`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`technician` (
  `person_id` INT NOT NULL,
  PRIMARY KEY (`person_id`),
  CONSTRAINT `fk_technician_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`employee` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`skills`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`skills` (
  `skill_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`skill_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`technician_skills`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`technician_skills` (
  `person_id` INT NOT NULL,
  `skill_id` INT NOT NULL,
  PRIMARY KEY (`person_id`, `skill_id`),
  INDEX `fk_skill_id_idx` (`skill_id` ASC),
  CONSTRAINT `fk_technician_skills_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`technician` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_technician_skills_skill_id`
    FOREIGN KEY (`skill_id`)
    REFERENCES `elaverty09`.`skills` (`skill_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`manager`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`manager` (
  `person_id` INT NOT NULL,
  PRIMARY KEY (`person_id`),
  CONSTRAINT `fk_manager_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`employee` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`tenant`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`tenant` (
  `person_id` INT NOT NULL,
  `account_number` VARCHAR(34) NOT NULL,
  PRIMARY KEY (`person_id`),
  CONSTRAINT `fk_tenant_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`person` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`lease`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`lease` (
  `lease_id` INT NOT NULL AUTO_INCREMENT,
  `building_id` INT NOT NULL,
  `apartment_number` SMALLINT NOT NULL,
  `manager_id` INT NOT NULL,
  `start_date` DATE NULL,
  `expected_duration` DATE NULL,
  `monthly_rent` DECIMAL(19,4) NULL,
  PRIMARY KEY (`lease_id`),
  INDEX `fk_building_id_idx` (`building_id` ASC),
  INDEX `fk_apartment_number_idx` (`apartment_number` ASC),
  INDEX `fk_manager_id_idx` (`manager_id` ASC),
  CONSTRAINT `fk_lease_building_id`
    FOREIGN KEY (`building_id`)
    REFERENCES `elaverty09`.`apartment` (`building_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_lease_apartment_number`
    FOREIGN KEY (`apartment_number`)
    REFERENCES `elaverty09`.`apartment` (`apartment_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_lease_manager_id`
    FOREIGN KEY (`manager_id`)
    REFERENCES `elaverty09`.`manager` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`lease_tenants`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`lease_tenants` (
  `lease_id` INT NOT NULL,
  `person_id` INT NOT NULL,
  PRIMARY KEY (`lease_id`, `person_id`),
  INDEX `fk_person_id_idx` (`person_id` ASC),
  CONSTRAINT `fk_lease_tenants_lease_id`
    FOREIGN KEY (`lease_id`)
    REFERENCES `elaverty09`.`lease` (`lease_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_lease_tenants_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`tenant` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`manages`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`manages` (
  `person_id` INT NOT NULL,
  `building_id` INT NOT NULL,
  `apartment_number` SMALLINT NOT NULL,
  PRIMARY KEY (`person_id`, `building_id`, `apartment_number`),
  INDEX `fk_building_id_idx` (`building_id` ASC),
  INDEX `fk_apartment_number_idx` (`apartment_number` ASC),
  CONSTRAINT `fk_manages_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`manager` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_manages_building_id`
    FOREIGN KEY (`building_id`)
    REFERENCES `elaverty09`.`apartment` (`building_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_manages_apartment_number`
    FOREIGN KEY (`apartment_number`)
    REFERENCES `elaverty09`.`apartment` (`apartment_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `elaverty09`.`based_in`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `elaverty09`.`based_in` (
  `person_id` INT NOT NULL,
  `building_id` INT NOT NULL,
  `apartment_number` SMALLINT NOT NULL,
  PRIMARY KEY (`person_id`, `building_id`, `apartment_number`),
  INDEX `fk_building_id_idx` (`building_id` ASC),
  INDEX `fk_apartment_number_idx` (`apartment_number` ASC),
  CONSTRAINT `fk_based_in_person_id`
    FOREIGN KEY (`person_id`)
    REFERENCES `elaverty09`.`manager` (`person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_based_in_building_id`
    FOREIGN KEY (`building_id`)
    REFERENCES `elaverty09`.`apartment` (`building_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_based_in_apartment_number`
    FOREIGN KEY (`apartment_number`)
    REFERENCES `elaverty09`.`apartment` (`apartment_number`)
    ON DELETE NO ACTION
    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;