CREATE TABLE mac (
  idmac 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(idmac)
);

CREATE TABLE sensor (
  idsensor 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(idsensor)
);

CREATE TABLE aluno (
  idaluno 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(idaluno)
);

CREATE TABLE leiturasofia (
  idleiturasofia INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  aluno_idaluno INTEGER UNSIGNED NOT NULL,
  sensor_idsensor INTEGER UNSIGNED NOT NULL,
  mac_idmac VARCHAR(17) NOT NULL,
  dataleitura DATE NULL,
  horaleitura TIME NULL,
  valor INTEGER UNSIGNED NULL,
  PRIMARY KEY(idleiturasofia),
  INDEX leitura_FKIndex1(mac_idmac),
  INDEX leitura_FKIndex2(sensor_idsensor),
  INDEX leitura_FKIndex3(aluno_idaluno),
  FOREIGN KEY(mac_idmac)
    REFERENCES mac(idmac)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(sensor_idsensor)
    REFERENCES sensor(idsensor)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(aluno_idaluno)
    REFERENCES aluno(idaluno)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);