bx5XwWfF

1DROP TABLE IF EXISTS user;
2DROP TABLE IF EXISTS client_app;
3DROP TABLE IF EXISTS consumption;
4DROP TABLE IF EXISTS cameras;
5DROP TABLE IF EXISTS suite;
6DROP TABLE IF EXISTS multimidia;
7DROP TABLE IF EXISTS garden;
8
9CREATE TABLE user (
10    id_user INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
11    name VARCHAR(25) NOT NULL,
12    password VARCHAR(6) NOT NULL,
13    funct VARCHAR(25)
14);
15
16CREATE TABLE client_app (
17    id_client_app INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
18    d_name VARCHAR(25) NOT NULL,
19    d_status VARCHAR(10),
20    description VARCHAR(50) NOT NULL,
21    user_id INT NOT NULL,
22     CONSTRAINT FK_client_app_user FOREIGN KEY (user_id) 
23	  REFERENCES user (id_user)
24);	
25
26CREATE TABLE consumption(
27	daily INT,
28	weekly INT,
29	id_client_app INT NOT NULL,
30	CONSTRAINT FK_consumption FOREIGN KEY(id_client_app)
31	REFERENCES client_app(id_client_app)
32);
33
34CREATE TABLE cameras(
35	camera_id INT AUTO_INCREMENT,
36	id_client_app INT,
37	PRIMARY KEY(camera_id,id_client_app),
38	CONSTRAINT FK_cameras FOREIGN KEY(id_client_app)
39	REFERENCES client_app(id_client_app)
40);
41
42CREATE TABLE suite(
43	suite_id INT AUTO_INCREMENT,
44	id_client_app INT,
45	PRIMARY KEY(suite_id, id_client_app),
46	CONSTRAINT FK_suite FOREIGN KEY (id_client_app)
47	REFERENCES client_app(id_client_app)
48);
49
50CREATE TABLE multimidia(
51	multimidia_id INT AUTO_INCREMENT,
52	id_client_app INT,
53	PRIMARY KEY(multimidia_id, id_client_app),
54	CONSTRAINT FK_multimidia FOREIGN KEY (id_client_app)
55	REFERENCES client_app(id_client_app)
56);
57
58CREATE TABLE garden(
59	garden_id INT AUTO_INCREMENT,
60	watering_schedule DATE,
61	id_client_app INT,
62	PRIMARY KEY(garden_id, id_client_app),
63	CONSTRAINT FK_garden FOREIGN KEY (id_client_app)
64	REFERENCES client_app(id_client_app)
65);
66INSERT INTO user(name,password,funct)  values("NICACIO", "123456", "RESIDENTE");
67INSERT INTO user(name,password,funct)  values("PEDRO", "654321", "FUNCIONARIO");
68INSERT INTO user(name,password,funct)  values("DANILO", "135791", "RESIDENTE");
69INSERT INTO user(name,password,funct)  values("NOEL", "123456", "FUNCIONARIO");
70INSERT INTO user(name,password,funct)  values("PAULO", "123456", "VISITANTE");
71
72INSERT INTO client_app(d_name,description,user_id,d_status)  values("AR COND.", "ESTABILIZA A TEMPERATURA AMBIENTE","2","ON");
73INSERT INTO client_app(d_name,description,user_id,d_status)  values("LUZES", "APAGAR/ACENDER ILUMINAÇÃO AMBIENTE","3","ON");
74INSERT INTO client_app(d_name,description,user_id,d_status)  values("TV", "ASSISTA PROGRAMAS, JOGOS, FILMES, ETC.","4","OFF");
75INSERT INTO client_app(d_name,description,user_id,d_status)  values("RADIO", "OUVIR MUSICA/RADIO","5","ON");
76INSERT INTO client_app(d_name,description,user_id,d_status)  values("AR COND.", "ESTABILIZA A TEMPERATURA AMBIENTE","5","ON");
77INSERT INTO client_app(d_name,description,user_id,d_status)  values("CORTINAS", "ABRIR/FECHAR","1","OPEN");
78INSERT INTO client_app(d_name,description,user_id,d_status)  values("CAMERA", "MONITORAMENTO","1","ON");
79INSERT INTO client_app(d_name,description,user_id,d_status)  values("CAMERA", "MONITORAMENTO","2","OFF");
80INSERT INTO client_app(d_name,description,user_id,d_status)  values("IRRIGADOR","IRRIGAÇÃO DO JARDIM","1","OFF");
81
82INSERT INTO consumption(daily,weekly,id_client_app) values("100","500","3");
83INSERT INTO consumption(daily,weekly,id_client_app) values("100","1500","4");
84INSERT INTO consumption(daily,weekly,id_client_app) values("500","2500","1");
85INSERT INTO consumption(daily,weekly,id_client_app) values("500","2500","5");
86
87INSERT INTO cameras(id_client_app) values("7");
88INSERT INTO cameras(id_client_app) values("8");
89
90INSERT INTO multimidia(id_client_app) values("2");
91INSERT INTO multimidia(id_client_app) values("3");
92INSERT INTO multimidia(id_client_app) values("4");
93INSERT INTO multimidia(id_client_app) values("1");
94
95INSERT INTO suite(id_client_app) values("5");
96INSERT INTO suite(id_client_app) values("6");
97
98INSERT INTO garden(id_client_app,watering_schedule) values("9","20171231");