Import Library

library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.5.2
## Loading required package: DBI
## Warning: package 'DBI' was built under R version 4.5.2
library(DBI)
library(RMariaDB)
## Warning: package 'RMariaDB' was built under R version 4.5.2

1. KONEKSI KE SERVER MYSQL

server <- dbConnect(
  MariaDB(),
  user = "root",
  password = "",
  host = "localhost"
)

MEMBUAT DATABASE

dbExecute(server, "CREATE DATABASE IF NOT EXISTS GreenhouseSimple")
## [1] 1
dbDisconnect(server)

koneksi ulang menggunakan database GreenhouseSimple

server <- dbConnect(
  MariaDB(),
  user = "root",
  password = "",
  host = "localhost",
  dbname = "GreenhouseSimple"
)

3. RESET TABEL

dbExecute(server, "SET FOREIGN_KEY_CHECKS = 0")
## [1] 0
dbExecute(server, "DROP TABLE IF EXISTS PembacaanSensor")
## [1] 0
dbExecute(server, "DROP TABLE IF EXISTS Sensor")
## [1] 0
dbExecute(server, "DROP TABLE IF EXISTS Tanaman")
## [1] 0
dbExecute(server, "SET FOREIGN_KEY_CHECKS = 1")
## [1] 0

4. MEMBUAT TABEL

# Tabel Tanaman
dbExecute(server, "
CREATE TABLE Tanaman (
    id_tanaman INT AUTO_INCREMENT PRIMARY KEY,
    nama_tanaman VARCHAR(100),
    jenis VARCHAR(50)
)")
## [1] 0
# Tabel Sensor
dbExecute(server, "
CREATE TABLE Sensor (
    id_sensor INT AUTO_INCREMENT PRIMARY KEY,
    tipe_sensor VARCHAR(50)
)")
## [1] 0
# Tabel Pembacaan Sensor
dbExecute(server, "
CREATE TABLE PembacaanSensor (
    id_bacaan INT AUTO_INCREMENT PRIMARY KEY,
    id_sensor INT,
    id_tanaman INT,
    nilai DECIMAL(10,2),
    waktu_baca DATETIME,
    FOREIGN KEY (id_sensor) REFERENCES Sensor(id_sensor),
    FOREIGN KEY (id_tanaman) REFERENCES Tanaman(id_tanaman)
)")
## [1] 0

5. MEMASUKKAN DATA

# Data Tanaman
dbExecute(server, "
INSERT INTO Tanaman (nama_tanaman, jenis) VALUES
('Tomat', 'Sayuran'),
('Cabai', 'Sayuran'),
('Anggrek', 'Bunga')
")
## [1] 3
# Data Sensor
dbExecute(server, "
INSERT INTO Sensor (tipe_sensor) VALUES
('Suhu'),
('Kelembaban'),
('Cahaya')
")
## [1] 3
# Data Pembacaan Sensor
dbExecute(server, "
INSERT INTO PembacaanSensor (id_sensor, id_tanaman, nilai, waktu_baca) VALUES
(1, 1, 28.5, '2025-01-10 08:00:00'),
(2, 1, 70.2, '2025-01-10 08:00:00'),
(1, 2, 26.1, '2025-01-10 09:00:00'),
(3, 1, 150.0, '2025-01-10 10:00:00'),
(2, 3, 60.5, '2025-01-10 11:00:00')
")
## [1] 5

6. MENAMPILKAN TABEL

print(dbGetQuery(server, "SELECT * FROM Tanaman"))
##   id_tanaman nama_tanaman   jenis
## 1          1        Tomat Sayuran
## 2          2        Cabai Sayuran
## 3          3      Anggrek   Bunga
print(dbGetQuery(server, "SELECT * FROM Sensor"))
##   id_sensor tipe_sensor
## 1         1        Suhu
## 2         2  Kelembaban
## 3         3      Cahaya
print(dbGetQuery(server, "SELECT * FROM PembacaanSensor"))
##   id_bacaan id_sensor id_tanaman nilai          waktu_baca
## 1         1         1          1  28.5 2025-01-10 08:00:00
## 2         2         2          1  70.2 2025-01-10 08:00:00
## 3         3         1          2  26.1 2025-01-10 09:00:00
## 4         4         3          1 150.0 2025-01-10 10:00:00
## 5         5         2          3  60.5 2025-01-10 11:00:00

7. ANALISIS GREENHOUSE

### 1. Sensor yang paling aktif
sensor_aktif <- dbGetQuery(server, "
    SELECT S.tipe_sensor,
           COUNT(P.id_bacaan) AS Jumlah_Bacaan
    FROM PembacaanSensor P
    JOIN Sensor S ON P.id_sensor = S.id_sensor
    GROUP BY S.tipe_sensor
    ORDER BY Jumlah_Bacaan DESC;
")
print(sensor_aktif)
##   tipe_sensor Jumlah_Bacaan
## 1        Suhu             2
## 2  Kelembaban             2
## 3      Cahaya             1
### 2. Tanaman yang paling sering dipantau
tanaman_terpantau <- dbGetQuery(server, "
    SELECT T.nama_tanaman,
           COUNT(P.id_bacaan) AS Jumlah_Pemantauan
    FROM PembacaanSensor P
    JOIN Tanaman T ON P.id_tanaman = T.id_tanaman
    GROUP BY T.nama_tanaman
    ORDER BY Jumlah_Pemantauan DESC;
")
print(tanaman_terpantau)
##   nama_tanaman Jumlah_Pemantauan
## 1        Tomat                 3
## 2      Anggrek                 1
## 3        Cabai                 1
### 3. Rata-rata nilai sensor per tanaman
rata_sensor <- dbGetQuery(server, "
    SELECT T.nama_tanaman,
           AVG(P.nilai) AS Rata_Rata_Nilai
    FROM PembacaanSensor P
    JOIN Tanaman T ON P.id_tanaman = T.id_tanaman
    GROUP BY T.nama_tanaman
    ORDER BY Rata_Rata_Nilai DESC;
")
print(rata_sensor)
##   nama_tanaman Rata_Rata_Nilai
## 1        Tomat            82.9
## 2      Anggrek            60.5
## 3        Cabai            26.1
### 4. Sensor dengan rata-rata nilai tertinggi
sensor_tertinggi <- dbGetQuery(server, "
    SELECT S.tipe_sensor,
           AVG(P.nilai) AS Rata_Rata
    FROM PembacaanSensor P
    JOIN Sensor S ON P.id_sensor = S.id_sensor
    GROUP BY S.tipe_sensor
    ORDER BY Rata_Rata DESC;
")
print(sensor_tertinggi)
##   tipe_sensor Rata_Rata
## 1      Cahaya    150.00
## 2  Kelembaban     65.35
## 3        Suhu     27.30
### 5. Analisis sederhana kesehatan tanaman
kinerja_tanaman <- dbGetQuery(server, "
    SELECT 
        T.nama_tanaman,
        AVG(P.nilai) AS Rata_Rata_Bacaan,
        CASE
            WHEN AVG(P.nilai) < 40 THEN 'Normal'
            WHEN AVG(P.nilai) BETWEEN 40 AND 100 THEN 'Butuh Pemantauan'
            ELSE 'Perlu Perhatian'
        END AS Status_Tanaman
    FROM Tanaman T
    LEFT JOIN PembacaanSensor P ON T.id_tanaman = P.id_tanaman
    GROUP BY T.nama_tanaman;
")
print(kinerja_tanaman)
##   nama_tanaman Rata_Rata_Bacaan   Status_Tanaman
## 1      Anggrek             60.5 Butuh Pemantauan
## 2        Cabai             26.1           Normal
## 3        Tomat             82.9 Butuh Pemantauan