CREATE TABLE macArthur (
  idmacArthur VARCHAR(17) NOT NULL,
  nome VARCHAR(45) NULL,
  contador INTEGER UNSIGNED NULL DEFAULT 0,
  ativo BOOL NULL DEFAULT 1,
  limite INTEGER UNSIGNED NULL DEFAULT 100,
  PRIMARY KEY(idmacArthur)
);

CREATE TABLE sensorArthur (
  idsensorArthur INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nome VARCHAR(45) NULL,
  ativo BOOL NULL DEFAULT 1,
  limite INTEGER UNSIGNED NULL DEFAULT 100,
  contador INTEGER UNSIGNED NULL DEFAULT 0,
  unidade VARCHAR(10) NULL,
  sigla VARCHAR(3) NULL,
  PRIMARY KEY(idsensorArthur)
);

CREATE TABLE coletorArthur (
  idcoletorArthur INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nome VARCHAR(45) NULL,
  contador INTEGER UNSIGNED NULL DEFAULT 0,
  ativo BOOL NULL DEFAULT 1,
  limite INTEGER UNSIGNED NULL DEFAULT 100,
  PRIMARY KEY(idcoletorArthur)
);

CREATE TABLE leituraArthur (
  idleituraArthur INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  sensorArthur_idsensorArthur INTEGER UNSIGNED NOT NULL,
  coletorArthur_idcoletorArthur INTEGER UNSIGNED NOT NULL,
  macArthur_idmacArthur VARCHAR(17) NOT NULL,
  dataleitura DATE NULL,
  horaleitura TIME NULL,
  valor INTEGER UNSIGNED NULL,
  PRIMARY KEY(idleituraArthur),
  INDEX leitura_FKIndex1(macArthur_idmacArthur),
  INDEX leitura_FKIndex2(sensorArthur_idsensorArthur),
  INDEX leitura_FKIndex3(coletorArthur_idcoletorArthur),
  FOREIGN KEY(macArthur_idmacArthur)
    REFERENCES macArthur(idmacArthur)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(sensorArthur_idsensorArthur)
    REFERENCES sensorArthur(idsensorArthur)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(coletorArthur_idcoletorArthur)
    REFERENCES coletorArthur(idcoletorArthur)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);


