8E6D5hBL

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema smra
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema smra
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `smra` DEFAULT CHARACTER SET utf8 ;
15USE `smra` ;
16
17-- -----------------------------------------------------
18-- Table `smra`.`Regiao`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `smra`.`Regiao` (
21  `idRegiao` INT NOT NULL,
22  `regiao` VARCHAR(125) NOT NULL,
23  PRIMARY KEY (`idRegiao`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `smra`.`Utilizadores`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `smra`.`Utilizadores` (
31  `idUtilizadores` INT NOT NULL,
32  `nome` VARCHAR(45) NULL,
33  `email` VARCHAR(45) NULL,
34  `palavra_passe` VARCHAR(45) NULL,
35  `Regiao_idRegiao` INT NOT NULL,
36  PRIMARY KEY (`idUtilizadores`),
37  INDEX `fk_Utilizadores_Regiao1_idx` (`Regiao_idRegiao` ASC),
38  CONSTRAINT `fk_Utilizadores_Regiao1`
39    FOREIGN KEY (`Regiao_idRegiao`)
40    REFERENCES `smra`.`Regiao` (`idRegiao`)
41    ON DELETE NO ACTION
42    ON UPDATE NO ACTION)
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `smra`.`ParamAlertas`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `smra`.`ParamAlertas` (
50  `idParamAlertas` INT NOT NULL,
51  `ph_MIN` DECIMAL(5) NULL,
52  `ph_MAX` DECIMAL(5) NULL,
53  `nivel_MIN` DECIMAL(5) NULL,
54  `nivel_MAX` DECIMAL(5) NULL,
55  `turbidez_MIN` DECIMAL(5) NULL,
56  `turbidez_MAX` DECIMAL(5) NULL,
57  `minerais_MIN` DECIMAL(5) NULL,
58  `minerais_MAX` DECIMAL(5) NULL,
59  PRIMARY KEY (`idParamAlertas`))
60ENGINE = InnoDB;
61
62
63-- -----------------------------------------------------
64-- Table `smra`.`Reservatorio`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `smra`.`Reservatorio` (
67  `idReservatorio` INT NOT NULL,
68  `nome` VARCHAR(45) NULL,
69  `ultima_manutencao` DATETIME NULL,
70  `revestimento` VARCHAR(45) NULL,
71  `ph_atual` DECIMAL(5) NULL,
72  `nivel_atual` DECIMAL(5) NULL,
73  `turbidez_atual` DECIMAL(5) NULL,
74  `minerais_atual` DECIMAL(5) NULL,
75  `Regiao_idRegiao` INT NOT NULL,
76  `ParamAlertas_idParamAlertas` INT NOT NULL,
77  PRIMARY KEY (`idReservatorio`),
78  INDEX `fk_Reservatorio_Regiao1_idx` (`Regiao_idRegiao` ASC),
79  INDEX `fk_Reservatorio_ParamAlertas1_idx` (`ParamAlertas_idParamAlertas` ASC),
80  CONSTRAINT `fk_Reservatorio_Regiao1`
81    FOREIGN KEY (`Regiao_idRegiao`)
82    REFERENCES `smra`.`Regiao` (`idRegiao`)
83    ON DELETE NO ACTION
84    ON UPDATE NO ACTION,
85  CONSTRAINT `fk_Reservatorio_ParamAlertas1`
86    FOREIGN KEY (`ParamAlertas_idParamAlertas`)
87    REFERENCES `smra`.`ParamAlertas` (`idParamAlertas`)
88    ON DELETE NO ACTION
89    ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `smra`.`Alertas`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `smra`.`Alertas` (
97  `idAlertas` INT NOT NULL,
98  `alerta` VARCHAR(125) NULL,
99  `Reservatorio_idReservatorio` INT NOT NULL,
100  PRIMARY KEY (`idAlertas`),
101  INDEX `fk_Alertas_Reservatorio_idx` (`Reservatorio_idReservatorio` ASC),
102  CONSTRAINT `fk_Alertas_Reservatorio`
103    FOREIGN KEY (`Reservatorio_idReservatorio`)
104    REFERENCES `smra`.`Reservatorio` (`idReservatorio`)
105    ON DELETE NO ACTION
106    ON UPDATE NO ACTION)
107ENGINE = InnoDB;
108
109
110-- -----------------------------------------------------
111-- Table `smra`.`Previsoes`
112-- -----------------------------------------------------
113CREATE TABLE IF NOT EXISTS `smra`.`Previsoes` (
114  `idPrevisoes` INT NOT NULL,
115  `previsao_nivel` DECIMAL(5) NULL,
116  `previsao_manutencao` DATETIME NULL,
117  `Reservatorio_idReservatorio` INT NOT NULL,
118  PRIMARY KEY (`idPrevisoes`),
119  INDEX `fk_Previsoes_Reservatorio1_idx` (`Reservatorio_idReservatorio` ASC),
120  CONSTRAINT `fk_Previsoes_Reservatorio1`
121    FOREIGN KEY (`Reservatorio_idReservatorio`)
122    REFERENCES `smra`.`Reservatorio` (`idReservatorio`)
123    ON DELETE NO ACTION
124    ON UPDATE NO ACTION)
125ENGINE = InnoDB;
126
127
128-- -----------------------------------------------------
129-- Table `smra`.`Historico`
130-- -----------------------------------------------------
131CREATE TABLE IF NOT EXISTS `smra`.`Historico` (
132  `idHistorico` INT NOT NULL,
133  `data` DATETIME NOT NULL,
134  `ph` DECIMAL(5) NULL,
135  `nivel` DECIMAL(5) NULL,
136  `turbidez` DECIMAL(5) NULL,
137  `minerais` DECIMAL(5) NULL,
138  `Reservatorio_idReservatorio` INT NOT NULL,
139  PRIMARY KEY (`idHistorico`),
140  INDEX `fk_Historico_Reservatorio1_idx` (`Reservatorio_idReservatorio` ASC),
141  CONSTRAINT `fk_Historico_Reservatorio1`
142    FOREIGN KEY (`Reservatorio_idReservatorio`)
143    REFERENCES `smra`.`Reservatorio` (`idReservatorio`)
144    ON DELETE NO ACTION
145    ON UPDATE NO ACTION)
146ENGINE = InnoDB;
147
148
149SET SQL_MODE=@OLD_SQL_MODE;
150SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
151SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;