knitr::opts_chunk$set(echo = TRUE)
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)
###Menghubungkan server database ke RStudio
server <- dbConnect(
RMySQL::MySQL(),
host = "127.0.0.1",
user = "root",
password = "")
###Membuat dan mengakses database baru
dbExecute(server, "DROP DATABASE IF EXISTS InvestPorto")
## [1] 5
dbExecute(server, "CREATE DATABASE InvestPorto")
## [1] 1
dbExecute(server, "USE InvestPorto")
## [1] 0
###Membuat tabel data baru dalam sebuah database
#Tabel Investor
dbExecute(server, "
CREATE TABLE Investor (
ID_Investor VARCHAR(10) PRIMARY KEY,
Nama VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Tanggal_Registrasi DATE NOT NULL,
Risk_Profile ENUM('Konservatif', 'Moderat', 'Agresif') NOT NULL
);
")
## [1] 0
#Tabel investasi
dbExecute(server, "
CREATE TABLE Investasi (
ID_Investasi VARCHAR(10) PRIMARY KEY,
ID_Investor VARCHAR(10) NOT NULL,
Jenis_Investasi ENUM('Saham', 'Obligasi', 'Properti', 'Crypto') NOT NULL,
Nama_Investasi VARCHAR(100) NOT NULL,
Jumlah_Awal DECIMAL(15,2) NOT NULL,
FOREIGN KEY (ID_Investor) REFERENCES Investor(ID_Investor)
);
")
## [1] 0
#Tabel portofolio
dbExecute(server, "
CREATE TABLE Portofolio (
ID_Portofolio VARCHAR (10) PRIMARY KEY,
ID_Investor VARCHAR (10) NOT NULL,
Nama_Portofolio VARCHAR(100) NOT NULL,
Tanggal_Dibuat DATE NOT NULL,
FOREIGN KEY (ID_Investor) REFERENCES Investor(ID_Investor)
);
")
## [1] 0
#Tabel transaksi
dbExecute(server, "
CREATE TABLE Transaksi (
ID_Transaksi VARCHAR(10) PRIMARY KEY,
ID_Investasi VARCHAR(10) NOT NULL,
Jenis_Transaksi ENUM('Pembelian', 'Penjualan', 'Dividen', 'Capital Gain') NOT NULL,
Tanggal_Transaksi DATE NOT NULL,
Jumlah_Unit DECIMAL(15,2) NOT NULL,
Harga_Per_Unit DECIMAL(15,2) NOT NULL,
Total_Nilai DECIMAL(15,2) NOT NULL,
Biaya_Transaksi DECIMAL(15,2) DEFAULT 0,
FOREIGN KEY (ID_Investasi) REFERENCES Investasi(ID_Investasi)
);
")
## [1] 0
#Tabel kinerja portofolio
dbExecute(server, "
CREATE TABLE Kinerja_Portofolio (
ID_Kinerja VARCHAR(10) PRIMARY KEY,
ID_Portofolio VARCHAR(10) NOT NULL,
Tanggal_Evaluasi DATE NOT NULL,
Nilai_Awal DECIMAL(15,2) NOT NULL,
Nilai_Sekarang DECIMAL(15,2) NOT NULL,
Return_Persen DECIMAL(10,2) NOT NULL,
Volatilitas DECIMAL(10,2),
FOREIGN KEY (ID_Portofolio) REFERENCES Portofolio(ID_Portofolio)
);
")
## [1] 0
###Memasukkan data pada dataset
#Tabel Investor
dbSendQuery(server, "INSERT INTO Investor (ID_Investor,Nama, Email, Tanggal_Registrasi, Risk_Profile) VALUES
('INV_0001','Budi Santoso', 'budi.santoso@gmail.com', '2023-01-15', 'Moderat'),
('INV_0002','Ani Wijaya', 'ani.wijaya@gmail.com', '2023-02-20', 'Konservatif'),
('INV_0003','Citra Dewi', 'citra.dewi@gmail.com', '2023-03-10', 'Agresif'),
('INV_0004', 'Firman Teno', 'firman@gmail.com', '2023-07-29', 'Agresif'),
('INV_0005', 'Fitri Sinta', 'fitri@gmail.com', '2023-11-03', 'Moderat'),
('INV_0006', 'Syifa Nurrisya', 'syifa@gmail.com', '2023-01-21', 'Konservatif'),
('INV_0007', 'Aslan Dikawan', 'Aslan@gmail.com', '2023-08-28', 'Agresif');
")
## <MySQLResult:12,0,8>
#Tabel Investasi
dbSendQuery(server, "
INSERT INTO investasi VALUES
('V001', 'INV_0001', 'Saham', 'PT Telkom Indonesia (TLKM)', 50000000.00),
('V002', 'INV_0001', 'Obligasi', 'Obligasi Negara Seri FR0075', 75000000.00),
('V003', 'INV_0002', 'Properti', 'Apartemen Green Park Jakarta', 350000000.00),
('V004', 'INV_0003', 'Crypto', 'Bitcoin (BTC)', 12000000.00),
('V005', 'INV_0003', 'Saham', 'PT Bank Central Asia (BBCA)', 45000000.00),
('V006', 'INV_0004', 'Obligasi', 'Obligasi Korporasi PT Astra', 60000000.00),
('V007', 'INV_0005', 'Crypto', 'Ethereum (ETH)', 8500000.00),
('V008', 'INV_0006', 'Properti', 'Ruko BSD City', 275000000.00),
('V009', 'INV_0006', 'Saham', 'PT Unilever Indonesia (UNVR)', 30000000.00),
('V010', 'INV_0007', 'Obligasi', 'Obligasi Negara Seri FR0080', 90000000.00),
('V011', 'INV_0002', 'Crypto', 'Cardano (ADA)', 15000000.00),
('V012', 'INV_0004', 'Saham', 'PT Bank Rakyat Indonesia (BBRI)', 40000000.00),
('V013', 'INV_0005', 'Properti', 'Tanah Kavling Bandung', 180000000.00),
('V014', 'INV_0007', 'Saham', 'PT GoTo Gojek Tokopedia (GOTO)', 35000000.00),
('V015', 'INV_0007', 'Crypto', 'Bitcoin (BTC)', 10000000.00);
")
## <MySQLResult:12,0,9>
#Tabel portofolio
dbSendQuery(server, "
INSERT INTO Portofolio VALUES
('P001', 'INV_0001', 'Portofolio Konservatif', '2023-01-15'),
('P002', 'INV_0001', 'Portofolio Pertumbuhan', '2023-03-22'),
('P003', 'INV_0002', 'Portofolio Pendapatan Tetap', '2023-02-10'),
('P004', 'INV_0003', 'Portofolio Agresif', '2023-04-05'),
('P005', 'INV_0004', 'Portofolio Balanced', '2023-01-30'),
('P006', 'INV_0005', 'Portofolio Saham Blue Chip', '2023-05-12'),
('P007', 'INV_0006', 'Portofolio Reksadana', '2023-06-18'),
('P008', 'INV_0007', 'Portofolio Internasional', '2023-03-01'),
('P009', 'INV_0002', 'Portofolio Emas', '2023-05-25'),
('P010', 'INV_0003', 'Portofolio Properti', '2023-02-14'),
('P011', 'INV_0005', 'Portofolio Teknologi', '2023-04-30'),
('P012', 'INV_0007', 'Portofolio Campuran', '2023-01-10');"
)
## <MySQLResult:12,0,10>
# Tabel transaksi
dbSendQuery(server, "
INSERT INTO Transaksi (ID_Transaksi, ID_Investasi, Jenis_Transaksi, Tanggal_Transaksi ,Jumlah_Unit, Harga_Per_Unit, Total_Nilai, Biaya_Transaksi) VALUES
('TRX001', 'V001', 'Pembelian', '2023-01-15', 100, 1500, 150000, 750),
('TRX002', 'V005', 'Penjualan', '2023-02-20', 50, 2000, 100000, 500),
('TRX003', 'V010', 'Dividen', '2023-03-10', 10, 500, 5000, 0),
('TRX004', 'V003', 'Capital Gain', '2023-04-05', 75, 1200, 90000, 450),
('TRX005', 'V007', 'Pembelian', '2023-05-12', 200, 800, 160000, 800),
('TRX006', 'V012', 'Penjualan', '2023-06-18', 30, 2500, 75000, 375),
('TRX007', 'V002', 'Dividen', '2023-07-22', 5, 300, 1500, 0),
('TRX008', 'V008', 'Capital Gain', '2023-08-30', 120, 950, 114000, 570),
('TRX009', 'V015', 'Pembelian', '2023-09-14', 150, 1700, 255000, 1275),
('TRX010', 'V004', 'Penjualan', '2023-10-25', 80, 1350, 108000, 540);
")
## <MySQLResult:12,0,11>
#Tabel kinerja portofolio
dbSendQuery(server,"
INSERT INTO Kinerja_Portofolio (ID_Kinerja, ID_Portofolio, Tanggal_Evaluasi, Nilai_Awal, Nilai_Sekarang, Return_Persen, Volatilitas) VALUES
('K1', 'P001', '2023-01-15', 1000000, 1050000, 5.0, 2.1),
('K2', 'P002', '2023-01-15', 2500000, 2450000, -2.0, 3.4),
('K3', 'P003', '2023-01-15', 500000, 520000, 4.0, 1.8),
('K4', 'P004', '2023-01-15', 750000, 780000, 4.0, 2.3),
('K5', 'P005', '2023-01-15', 3000000, 3150000, 5.0, 2.9),
('K6', 'P006', '2023-01-15', 1500000, 1470000, -2.0, 3.1),
('K7', 'P007', '2023-02-20', 1000000, 1080000, 8.0, 2.5),
('K8', 'P008', '2023-02-20', 1800000, 1764000, -2.0, 3.0),
('K9', 'P009', '2023-02-20', 2200000, 2332000, 6.0, 2.7),
('K10', 'P010', '2023-02-20', 950000, 1007000, 6.0, 2.4),
('K11', 'P011', '2023-02-20', 1300000, 1261000, -3.0, 3.2),
('K12', 'P012', '2023-02-20', 2750000, 2915000, 6.0, 3.3),
('K13', 'P001', '2023-03-25', 1050000, 1071000, 2.0, 2.0),
('K14', 'P002', '2023-03-25', 2450000, 2597000, 6.0, 3.3),
('K15', 'P003', '2023-03-25', 520000, 530400, 2.0, 1.9),
('K16', 'P004', '2023-03-25', 780000, 795600, 2.0, 2.2),
('K17', 'P005', '2023-03-25', 3150000, 3276000, 4.0, 2.8),
('K18', 'P006', '2023-03-25', 1470000, 1499400, 2.0, 3.0),
('K19', 'P007', '2023-04-30', 1080000, 1123200, 4.0, 2.4),
('K20', 'P008', '2023-04-30', 1764000, 1728720, -2.0, 2.9);
")
## <MySQLResult:12,0,12>
###Melihat nilai tabel data pada database
tampilkan.investor <- dbGetQuery(server,"
SELECT * FROM INVESTOR;
")
print(tampilkan.investor)
## ID_Investor Nama Email Tanggal_Registrasi
## 1 INV_0001 Budi Santoso budi.santoso@gmail.com 2023-01-15
## 2 INV_0002 Ani Wijaya ani.wijaya@gmail.com 2023-02-20
## 3 INV_0003 Citra Dewi citra.dewi@gmail.com 2023-03-10
## 4 INV_0004 Firman Teno firman@gmail.com 2023-07-29
## 5 INV_0005 Fitri Sinta fitri@gmail.com 2023-11-03
## 6 INV_0006 Syifa Nurrisya syifa@gmail.com 2023-01-21
## 7 INV_0007 Aslan Dikawan Aslan@gmail.com 2023-08-28
## Risk_Profile
## 1 Moderat
## 2 Konservatif
## 3 Agresif
## 4 Agresif
## 5 Moderat
## 6 Konservatif
## 7 Agresif
tampilkan.investasi <- dbGetQuery(server,"
SELECT * FROM INVESTASI;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 4 imported
## as numeric
print(tampilkan.investasi)
## ID_Investasi ID_Investor Jenis_Investasi Nama_Investasi
## 1 V001 INV_0001 Saham PT Telkom Indonesia (TLKM)
## 2 V002 INV_0001 Obligasi Obligasi Negara Seri FR0075
## 3 V003 INV_0002 Properti Apartemen Green Park Jakarta
## 4 V004 INV_0003 Crypto Bitcoin (BTC)
## 5 V005 INV_0003 Saham PT Bank Central Asia (BBCA)
## 6 V006 INV_0004 Obligasi Obligasi Korporasi PT Astra
## 7 V007 INV_0005 Crypto Ethereum (ETH)
## 8 V008 INV_0006 Properti Ruko BSD City
## 9 V009 INV_0006 Saham PT Unilever Indonesia (UNVR)
## 10 V010 INV_0007 Obligasi Obligasi Negara Seri FR0080
## 11 V011 INV_0002 Crypto Cardano (ADA)
## 12 V012 INV_0004 Saham PT Bank Rakyat Indonesia (BBRI)
## 13 V013 INV_0005 Properti Tanah Kavling Bandung
## 14 V014 INV_0007 Saham PT GoTo Gojek Tokopedia (GOTO)
## 15 V015 INV_0007 Crypto Bitcoin (BTC)
## Jumlah_Awal
## 1 5.00e+07
## 2 7.50e+07
## 3 3.50e+08
## 4 1.20e+07
## 5 4.50e+07
## 6 6.00e+07
## 7 8.50e+06
## 8 2.75e+08
## 9 3.00e+07
## 10 9.00e+07
## 11 1.50e+07
## 12 4.00e+07
## 13 1.80e+08
## 14 3.50e+07
## 15 1.00e+07
tampilkan.portofolio <- dbGetQuery(server,"
SELECT * FROM PORTOFOLIO;
")
print(tampilkan.portofolio)
## ID_Portofolio ID_Investor Nama_Portofolio Tanggal_Dibuat
## 1 P001 INV_0001 Portofolio Konservatif 2023-01-15
## 2 P002 INV_0001 Portofolio Pertumbuhan 2023-03-22
## 3 P003 INV_0002 Portofolio Pendapatan Tetap 2023-02-10
## 4 P004 INV_0003 Portofolio Agresif 2023-04-05
## 5 P005 INV_0004 Portofolio Balanced 2023-01-30
## 6 P006 INV_0005 Portofolio Saham Blue Chip 2023-05-12
## 7 P007 INV_0006 Portofolio Reksadana 2023-06-18
## 8 P008 INV_0007 Portofolio Internasional 2023-03-01
## 9 P009 INV_0002 Portofolio Emas 2023-05-25
## 10 P010 INV_0003 Portofolio Properti 2023-02-14
## 11 P011 INV_0005 Portofolio Teknologi 2023-04-30
## 12 P012 INV_0007 Portofolio Campuran 2023-01-10
tampilkan.transaksi <- dbGetQuery(server,"
SELECT * FROM TRANSAKSI;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 4 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 5 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 6 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 7 imported
## as numeric
print(tampilkan.transaksi)
## ID_Transaksi ID_Investasi Jenis_Transaksi Tanggal_Transaksi Jumlah_Unit
## 1 TRX001 V001 Pembelian 2023-01-15 100
## 2 TRX002 V005 Penjualan 2023-02-20 50
## 3 TRX003 V010 Dividen 2023-03-10 10
## 4 TRX004 V003 Capital Gain 2023-04-05 75
## 5 TRX005 V007 Pembelian 2023-05-12 200
## 6 TRX006 V012 Penjualan 2023-06-18 30
## 7 TRX007 V002 Dividen 2023-07-22 5
## 8 TRX008 V008 Capital Gain 2023-08-30 120
## 9 TRX009 V015 Pembelian 2023-09-14 150
## 10 TRX010 V004 Penjualan 2023-10-25 80
## Harga_Per_Unit Total_Nilai Biaya_Transaksi
## 1 1500 150000 750
## 2 2000 100000 500
## 3 500 5000 0
## 4 1200 90000 450
## 5 800 160000 800
## 6 2500 75000 375
## 7 300 1500 0
## 8 950 114000 570
## 9 1700 255000 1275
## 10 1350 108000 540
tampilkan.kinerja_portofolio <- dbGetQuery(server,"
SELECT * FROM KINERJA_PORTOFOLIO;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 3 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 4 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 5 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 6 imported
## as numeric
print(tampilkan.kinerja_portofolio)
## ID_Kinerja ID_Portofolio Tanggal_Evaluasi Nilai_Awal Nilai_Sekarang
## 1 K1 P001 2023-01-15 1000000 1050000
## 2 K10 P010 2023-02-20 950000 1007000
## 3 K11 P011 2023-02-20 1300000 1261000
## 4 K12 P012 2023-02-20 2750000 2915000
## 5 K13 P001 2023-03-25 1050000 1071000
## 6 K14 P002 2023-03-25 2450000 2597000
## 7 K15 P003 2023-03-25 520000 530400
## 8 K16 P004 2023-03-25 780000 795600
## 9 K17 P005 2023-03-25 3150000 3276000
## 10 K18 P006 2023-03-25 1470000 1499400
## 11 K19 P007 2023-04-30 1080000 1123200
## 12 K2 P002 2023-01-15 2500000 2450000
## 13 K20 P008 2023-04-30 1764000 1728720
## 14 K3 P003 2023-01-15 500000 520000
## 15 K4 P004 2023-01-15 750000 780000
## 16 K5 P005 2023-01-15 3000000 3150000
## 17 K6 P006 2023-01-15 1500000 1470000
## 18 K7 P007 2023-02-20 1000000 1080000
## 19 K8 P008 2023-02-20 1800000 1764000
## 20 K9 P009 2023-02-20 2200000 2332000
## Return_Persen Volatilitas
## 1 5 2.1
## 2 6 2.4
## 3 -3 3.2
## 4 6 3.3
## 5 2 2.0
## 6 6 3.3
## 7 2 1.9
## 8 2 2.2
## 9 4 2.8
## 10 2 3.0
## 11 4 2.4
## 12 -2 3.4
## 13 -2 2.9
## 14 4 1.8
## 15 4 2.3
## 16 5 2.9
## 17 -2 3.1
## 18 8 2.5
## 19 -2 3.0
## 20 6 2.7