n7PGyHbR

1DROP DATABASE IF EXISTS Imobiliaria;
2CREATE DATABASE IF NOT EXISTS Imobiliaria;
3
4-- Criando estrutura:
5USE Imobiliaria;
6
7DROP TABLE IF EXISTS locatario;
8CREATE TABLE locatario(
9    id INT NOT NULL AUTO_INCREMENT,
10    nome TEXT,
11    email VARCHAR(128) NOT NULL UNIQUE,
12    telefone TEXT,
13    PRIMARY KEY (id) 
14)
15ENGINE=INNODB
16CHARACTER SET utf8
17COLLATE utf8_unicode_ci;
18
19DROP TABLE IF EXISTS locador;
20CREATE TABLE locador(
21    id INT NOT NULL AUTO_INCREMENT,
22    nome TEXT,
23    email VARCHAR(128) NOT NULL UNIQUE,
24    telefone TEXT,
25    dia_repase DATETIME,
26    PRIMARY KEY (id) 
27)
28ENGINE=INNODB
29CHARACTER SET utf8
30COLLATE utf8_unicode_ci;
31
32DROP TABLE IF EXISTS imovel;
33CREATE TABLE imovel(
34    id INT NOT NULL AUTO_INCREMENT,
35    endereco TEXT NOT NULL,
36    locador INT,
37    PRIMARY KEY (id),
38    FOREIGN KEY (locador) REFERENCES locador(id) 
39)
40ENGINE=INNODB
41CHARACTER SET utf8
42COLLATE utf8_unicode_ci;
43
44DROP TABLE IF EXISTS contrato;
45CREATE TABLE contrato(
46    id INT NOT NULL AUTO_INCREMENT,
47    imovel INT,
48    locador INT,
49    locatario INT,
50    inicio DATETIME,
51    termino DATETIME,
52    taxa_administracao FLOAT,
53    valor_aluguel FLOAT,
54    taxa_condominio FLOAT,
55    valor_iptu FLOAT,
56    History TEXT,
57    PRIMARY KEY (id),
58    FOREIGN KEY (imovel) REFERENCES imovel(id),
59    FOREIGN KEY (locador) REFERENCES locador(id),
60    FOREIGN KEY (locatario) REFERENCES locatario(id) 
61)
62ENGINE=INNODB
63CHARACTER SET utf8
64COLLATE utf8_unicode_ci;
65
66-- Criando dados de exemplo
67insert into locatario (
68    nome,
69    email,
70    telefone
71) values(
72    'Perter Park',
73    'peterpark@email.com',
74    '111111111'
75	),(
76    'Alfred Pennyworth',
77    'alfredpennyworth@email.com',
78    '222222222'
79);
80
81insert into locador (
82    nome,
83    email,
84    telefone,
85    dia_repase
86) values(
87    'Tony Stark',
88    'tonystark@mail.com',
89    '333333333',
90    '2021-02-10 12:00:00.000000'
91	),(
92    'Bruce Wayne',
93    'bruce wayne@mail.com',
94    '444444444',
95    '2021-02-01 17:00:00.000000'
96);
97
98insert into imovel (
99    endereco,
100    locador
101) values(
102    '10880 Malibu Point',
103    1
104	),(
105    '1007 Mountain Drive, Gotham',
106    2
107);
108
109insert into contrato (
110    imovel,
111    locador,
112    locatario,
113    inicio,
114    termino,
115    taxa_administracao,
116    valor_aluguel,
117    taxa_condominio,
118    valor_iptu,
119    History
120) values(
121    1,
122    1,
123    1,
124    '2021-02-05 12:00:00.000000',
125    '2022-02-04 12:00:00.000000',
126    130.00,
127    1300.00,
128    62.89,
129    130.00,
130    'a:2:{s:7:"aluguel";i:1;s:7:"repasse";i:0;}'
131	),(
132    2,
133    2,
134    2,
135    '2021-02-01 12:00:00.000000',
136    '2022-01-31 11:00:00.000000',
137    190.00,
138    1600.00,
139    79.00,
140    250.32,
141    'a:2:{s:7:"aluguel";i:1;s:7:"repasse";i:1;}'
142);