TEaspG4a

1CREATE SCHEMA IF NOT EXISTS `gerencia_republicas` DEFAULT CHARACTER SET latin1 ;
2USE `gerencia_republicas` ;
3
4CREATE TABLE IF NOT EXISTS `gerencia_republicas`.`pessoa` (
5  `idpessoa` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
6  `nome` VARCHAR(100) NOT NULL,
7  `email` VARCHAR(100) NOT NULL,
8  `senha` VARCHAR(45) NOT NULL,
9  `telefone` VARCHAR(11) NULL DEFAULT NULL,
10  PRIMARY KEY (`idpessoa`),
11  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
12ENGINE = InnoDB
13DEFAULT CHARACTER SET = latin1;
14
15CREATE TABLE IF NOT EXISTS `gerencia_republicas`.`republica` (
16  `idrepublica` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
17  `idadmin` INT(10) UNSIGNED NOT NULL,
18  `nome` VARCHAR(100) NOT NULL,
19  `rua` VARCHAR(100) NOT NULL,
20  `numero` VARCHAR(10) NOT NULL,
21  `bairro` VARCHAR(100) NOT NULL,
22  `cep` VARCHAR(8) NOT NULL,
23  `uf` VARCHAR(2) NOT NULL,
24  PRIMARY KEY (`idrepublica`))
25ENGINE = InnoDB
26DEFAULT CHARACTER SET = latin1;
27
28CREATE TABLE IF NOT EXISTS `gerencia_republicas`.`morador` (
29  `idmorador` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
30  `idrepublica` INT(10) UNSIGNED NOT NULL,
31  `idpessoa` INT(10) UNSIGNED NOT NULL,
32  `data_entrada` DATE NOT NULL,
33  `data_saida` DATE NULL DEFAULT NULL,
34  PRIMARY KEY (`idmorador`),
35  INDEX `fk_idrepublica_idx` (`idrepublica` ASC),
36  INDEX `fk_idpessoa_idx` (`idpessoa` ASC),
37  CONSTRAINT `fk_idpessoa`
38    FOREIGN KEY (`idpessoa`)
39    REFERENCES `gerencia_republicas`.`pessoa` (`idpessoa`)
40    ON DELETE CASCADE
41    ON UPDATE CASCADE,
42  CONSTRAINT `fk_idrepublica`
43    FOREIGN KEY (`idrepublica`)
44    REFERENCES `gerencia_republicas`.`republica` (`idrepublica`)
45    ON DELETE CASCADE
46    ON UPDATE CASCADE)
47ENGINE = InnoDB
48DEFAULT CHARACTER SET = latin1;