MySQLBD

-- MySQL Script generated by MySQL Workbench
-- 02/08/14 23:53:31
-- Model: New Model    Version: 1.0
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 CCM
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `CCM` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `CCM` ;

-- -----------------------------------------------------
-- Table `CCM`.`Utilizador`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`Utilizador` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Username` VARCHAR(16) NOT NULL,
  `Email` VARCHAR(255) NOT NULL,
  `Password` VARCHAR(32) NOT NULL,
  `DataCriacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`));


-- -----------------------------------------------------
-- Table `CCM`.`Veiculo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`Veiculo` (
  `Matricula` VARCHAR(45) NOT NULL,
  `Ano` DATETIME NOT NULL,
  `Cilindrada` INT(15) NOT NULL,
  `Modelo` VARCHAR(100) NOT NULL,
  `Cor` VARCHAR(100) NOT NULL,
  `NChassis` VARCHAR(50) NOT NULL,
  `Categoria` VARCHAR(50) NOT NULL,
  `TipoComustivel` VARCHAR(50) NOT NULL,
  `DistanciaEixos` VARCHAR(50) NULL,
  `PesoMaxFrente` INT(20) NULL,
  `PesoMaxRetaguarda` INT(20) NULL,
  `PesoTotal` INT(40) NULL,
  `TipoPneu` VARCHAR(100) NOT NULL,
  `Estado` VARCHAR(100) NULL,
  `Observacoes` VARCHAR(5000) NULL,
  PRIMARY KEY (`Matricula`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CCM`.`Socio`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`Socio` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `Nome` VARCHAR(100) NOT NULL,
  `Profissao` VARCHAR(100) NOT NULL,
  `Morada` VARCHAR(100) NOT NULL,
  `Localidade` VARCHAR(100) NOT NULL,
  `CodigoPostal` VARCHAR(10) NOT NULL,
  `DataNascimento` DATETIME NOT NULL,
  `Telefone` INT(9) NOT NULL,
  `Telemovel` INT(9) NOT NULL,
  `Email` VARCHAR(255) NOT NULL,
  `Matricula` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`, `Matricula`),
  INDEX `Matricula_idx` (`Matricula` ASC),
  CONSTRAINT `Matricula`
    FOREIGN KEY (`Matricula`)
    REFERENCES `CCM`.`Veiculo` (`Matricula`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CCM`.`Quota`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`Quota` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `IDSocio` INT NOT NULL,
  `Ano` INT(7) NOT NULL,
  PRIMARY KEY (`ID`, `IDSocio`),
  INDEX `ID_idx` (`IDSocio` ASC),
  CONSTRAINT `ID`
    FOREIGN KEY (`IDSocio`)
    REFERENCES `CCM`.`Socio` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CCM`.`Evento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`Evento` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `IDSocio` INT NOT NULL,
  `Nome` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`ID`, `IDSocio`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CCM`.`Socio_Participa_Evento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`Socio_Participa_Evento` (
  `Socio_ID` INT NOT NULL,
  `Socio_MatriculaCarocha` VARCHAR(45) NOT NULL,
  `Evento_ID` INT NOT NULL,
  `Evento_IDSocio` INT NOT NULL,
  PRIMARY KEY (`Socio_ID`, `Socio_MatriculaCarocha`, `Evento_ID`, `Evento_IDSocio`),
  INDEX `fk_Socio_has_Evento_Evento1_idx` (`Evento_ID` ASC, `Evento_IDSocio` ASC),
  INDEX `fk_Socio_has_Evento_Socio1_idx` (`Socio_ID` ASC, `Socio_MatriculaCarocha` ASC),
  CONSTRAINT `fk_Socio_has_Evento_Socio1`
    FOREIGN KEY (`Socio_ID` , `Socio_MatriculaCarocha`)
    REFERENCES `CCM`.`Socio` (`ID` , `Matricula`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Socio_has_Evento_Evento1`
    FOREIGN KEY (`Evento_ID` , `Evento_IDSocio`)
    REFERENCES `CCM`.`Evento` (`ID` , `IDSocio`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CCM`.`FotoEvento`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`FotoEvento` (
  `ID` INT(50) NOT NULL AUTO_INCREMENT,
  `IdEvento` INT NOT NULL,
  `Url` VARCHAR(200) NOT NULL,
  `Descricao` VARCHAR(200) NOT NULL,
  `Data` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Autor` VARCHAR(200) NOT NULL,
  `Posicao` INT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`, `IdEvento`),
  INDEX `ID_idx` (`IdEvento` ASC),
  CONSTRAINT `ID`
    FOREIGN KEY (`IdEvento`)
    REFERENCES `CCM`.`Evento` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CCM`.`FotosVeiculo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CCM`.`FotosVeiculo` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `Matricula` VARCHAR(45) NOT NULL,
  `FotoFrente` VARCHAR(200) NULL,
  `FotoTras` VARCHAR(200) NULL,
  PRIMARY KEY (`ID`, `Matricula`),
  INDEX `Matricula_idx` (`Matricula` ASC),
  CONSTRAINT `Matricula`
    FOREIGN KEY (`Matricula`)
    REFERENCES `CCM`.`Veiculo` (`Matricula`)
    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;