Saat ini sedang kuliah S2 di UNM Kramat
S1 UNM Jatiwaringin Tahun 2015
D3 BSI Bogor Tahun 2011

Pendidikan

Pengalaman Organisasi

(2023 - Now) Backend Engineer - PT. Iner Corp Indonesia
(2023 - Now) Backend Engineer - Silvertect.asia
(2020 - 2023) Senior Programmer - PT. Sinar Roda Utama
(2017 - 2020) Staff IT - PT Malea Energy
(2012 - 2017) Teller - Bank Muamalat Indonesia

Pengalaman Kerja

Founder kuliahkoding.com
Founder Grup & Web flutter.id
Mentor - Meetap Android Developer
Asisten Lab BSI Bogor Tahun 2009-2010
Kadiv Kaderisasi Badari BSI Bogor Tahun 2009
Sekretaris Senat BSI Bogor Angkatan 1

Saat ini sedang kuliah di S1 UNM Kramat
D3 BSI Tahun 2021

Pendidikan

(2023 - Now) Database Administrator - PT. Iner Corp Indonesia
(2023 - Now) Database Administrator - Silvertect.asia
Quality Assurance Bootcamp - Alterra Academy Batch 8
(2022 - 2023) Web Programmer - PT. Sinar Roda Utama
(2018 - 2022 ) Crew- PT Rekso National Food (McDonald's)

Pengalaman Organisasi

Manager at Idol Group Andthrix's 2023 - present
Liaison Officer at Barcode Organizer (Impactnation Japan Festival 2023)
PIC Merchandise at Barcode Organizer (Impactnation Japan Festival 2024)

Pengalaman Kerja

Akses Slide

Akses Materi

https://kuliahkoding.com/login

username : nim@bsi.ac.id
password : nim

contoh

username : 12080879@bsi.ac.id

password : 12080879

Materi Workshop

Basis Data
Sistem Basis Data
DBMS
ERD
DbDiagram
DDL
DML
View
Store Procedure
Trigger
Backup
Restore

Basis Data

Basis data (database) bisa diibaratkan seperti lemari arsip.
Seandainya kita bertugas mengelolanya, tentu kita akan merapikan data arsip di lemari tersebut, dari mulai memberi tanda, mengelompokkan arsipnya, dan lain-lain
Tujuannya adalah, agar ketika kita ingin mencari arsip, kita bisa dengan mudah mencarinya, karena sudah mengatur tata letak arsip di lemari tersebut

Apa Itu Basis Data

Basis bisa diartikan, tempat, gudang atau tempat menyimpan
Data adalah representasi fakta dari dunia nyata, yang mewakili suatu objek, seperti manusia (karyawan, pelajar, mahasiswa, guru, dan lain-lain).
Basis Data biasanya disimpan didalam media penyimpanan berbasis disk (seperti hardisk, flahdisk), hal ini agar data disimpan secara permanen
Tapi ingat, tidak semua yang disimpan di disk itu bisa dibilang Basis Data, karena tujuan utama dalam Basis Data adalah pengaturan, pemilihan, pengelompokan dan pengorganisasian data yang baik

Tujuan Basis Data

Speed, kecepatan dalam mengambil / mencari data
Space, efisiensi dalam ruang penyimpanan
Accuracy, data yang akurat
Availability, ketersediaan data
Completeness, kelengkapan data
Security, keamanan data
Shareability, kemudahan berbagi data

Sistem Basis Data

Basis data (database) perlu ada yang melakukan pengolahan datanya
Pengelola basis data adalah program / aplikasi (software / perangkat lunak)
Gabungan aplikasi pengelola dan basis data, kita sebut dengan nama sistem basis data

Komponen Sistem Basis Data

Hardware (perangkat keras), komputer, laptop, server dan lain-lain
Sistem Operasi, seperti Windows, Mac OS, Linux, dan lain-lain
Aplikasi Basis Data, seperti MySQL, PostgreSQL, SQL Server, dan lain-lain
User, pengguna seperti programmer, database admin, end user dan lain-lain
Aplikasi lain, yang menggunakan basis data, seperti web, desktop, dan lain-lain

DBMS

Database Management System
DBMS adalah aplikasi yang digunakan untuk me-manage data
Tanpa menggunakan DBMS, untuk me-manage data, seperti data produk, penjualan, kita harus simpan dalam bentuk file (misal seperti ketika menggunakan Excel)
DBMS biasanya berjalan sebagai aplikasi server yang digunakan untuk me-manage data, kita hanya tinggal memberi perintah ke DBMS untuk melakukan proses manajemen datanya, seperti menambah, mengubah, menghapus atau mengambil data
Contoh DBMS yang populer seperti MySQL, PostgreSQL, Sql Server, MongoDB, dan lain-lain

Jenis DBMS

Relational Database ( MySQL, PostgreSQL, Sql Server)
Document Database (MongoDB)
Key-Value Database (Redis)

Namun yang masih populer dan kebanyakan orang gunakan adalah relational database
Relational database cukup mudah dimengerti dan dipelajari karena kita sudah terbiasa menyimpan data dalam bentuk tabular (tabel) seperti di Microsoft Excel atau di Google Doc Spreadsheet
Selain itu relational database memiliki perintah standard menggunakan SQL, sehingga kita mudah ketika ingin berganti-ganti aplikasi database

SQL

Structured Query Language
Merupakan bahasa yang digunakan untuk mengirim perintah ke DBMS
SQL adalah bahasa yang mudah karena hanya berisi instruksi untuk menyimpan, mengubah, menghapus atau mengambil data melalui DBMS
Secara garis besar, semua perintah SQL di Relational Database itu hampir sama, namun biasanya tiap DBMS ada improvement yang membedakan hal-hal kecil dalam perintah SQL, namun secara garis besar perintahnya tetap sama

Mysql vs Maria DB

Tahun 2008 MySQL di akuisisi oleh perusahaan Sun Microsystem
Namun Tahun 2009, Sun Microsystem diakuisisi oleh perusahaan Oracle (Pemilik DBMS Oracle)
Hal ini menyebabkan 2 founder MySQL keluar dari MySQL dan membuat project baru bernama MariaDB
MariaDB Sebenarnya fork dari MySQL, jadi apa yang bisa dilakukan di MySQL bisa dilakukan di MariaDB
Sehingga sekarang jangan terlalu bingung jika ada MySQL dan MariaDB, karena sebenarnya itu dari source code yang sama, mungkin ada perbedaan kecil, namun secara garis besar sebenarnya tetap sama
https://mariadb.org/

Install Mysql

Menginstall MySQL banyak caranya, bisa download langsung dari halaman website resminya
Atau bisa menggunakan aplikasi yang mem-bundle MySQL seperti XAMPP, atau Laragon
Jika sudah menginstall MySQL / MariaDB menggunakan XAMPP atau Laragon, tidak perlu menginstall lagi MySQL, karena jika bentrok, maka salah satu aplikasi MySQL nya tidak akan bisa jalan

Database Client

Database client adalah aplikasi yang digunakan untuk berkomunikasi dengan DBMS
Biasanya DBMS sudah menyediakan database client sederhana yang bisa kita gunakan untuk berkomunikasi dengan DBMS agar lebih mudah
Beberapa database client yang sering digunakan diantaranya : phpmyadmin, mysql workbench, Sql Server Management Studio, Dbeaver, Heidi Sql, Mysql Yog, Navicat Mysql, TablePlus
Saya rekomendasikan menggunakan DBeaver Community untuk latihan pada workshop kali ini.
https://dbeaver.io/download/

Tipe Data

Bilangan Bulat : TINYINT, SMALLINT,MEDIUMINT,INT,INTEGER,BIGINT
Bilangan Desimal : FLOAT,DOUBLE,DECIMAL,NUMERIC
Karakter : CHAR,VARCHAR
Teks : TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT
Binary Large Object : TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
Tanggal dan Waktu : DATE,DATETIME,TIMESTAMP,TIME,YEAR
Lainnya : BOOLEAN,ENUM

Entity Relationship Diagram

Teknik yang digunakan untuk memodelkan kebutuhan data, biasanya oleh System Analys dalam tahap analisis persyaratan proyek pengembangan system. Sementara seolah-olah teknik diagram atau alat peraga memberikan dasar untuk desain database relasional yang mendasari sistem informasi yang dikembangkan. ERD bersama-sama dengan detail pendukung merupakan model data yang pada gilirannya digunakan sebagai spesifikasi untuk database.

Entity

Objek atau konsep yang dapat diidentifikasi dan memiliki atribut yang mendefinisikan karakteristiknya

Attribute

karakteristik atau properti yang dimiliki oleh sebuah entitas

Relationship

keterkaitan atau asosiasi antara entitas atau tabel dalam sebuah sistem basis data relasional

dbdiagram.io

https://dbdiagram.io/

sebuah platform untuk merancang dan memodelkan basis data secara visual. Ini adalah alat yang sangat berguna untuk menggambar diagram skema basis data dengan mudah dan cepat menggunakan antarmuka yang intuitif dan fitur kolaboratifnya.

Table users {
  id bigint [primary key]
  name varchar(255)
  email varchar(50)
  password varchar(25)
  roles enum('admin','staff','user')
  created_at timestamp
  updated_at timestamp
}

users

Table products {
  id bigint [primary key]
  name varchar(255)
  description text
  price int
  stock int
  category enum('food','drink','snack')
  image varchar(255)
  is_best_seller tinyint
  created_at timestamp
  updated_at timestamp
}

products

Table orders {
  id bigint [primary key]
  kasir_id bigint
  transaction_time timestamp
  total_price int
  total_item int
  payment_method enum('Tunai','QRIS')
  created_at timestamp
  updated_at timestamp
}

orders

Table order_details {
  id bigint [primary key]
  order_id bigint
  product_id bigint
  quantity int
  total_price int
  created_at timestamp
  updated_at timestamp
}

order_details

Ref: users.id < orders.kasir_id
Ref: orders.id < order_details.order_id
Ref: products.id < order_details.order_id

Relationship

ER Diagram

DDL

Data Definition Language adalah perintah SQL yang digunakan untuk mendefinisikan dan mengelola struktur database. Perintah DDL mencakup:

CREATE: Membuat objek database baru seperti tabel, view, atau index.
ALTER: Mengubah struktur objek database yang ada, seperti menambah kolom baru ke tabel.
DROP: Menghapus objek database seperti tabel atau view.
TRUNCATE: Menghapus semua baris dari tabel tanpa menghapus struktur tabel itu sendiri.

DDL berfokus pada pembuatan dan modifikasi struktur database daripada manipulasi data.

Buat Database
cafe_db

tabel : users

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(25) DEFAULT NULL,
  `roles` enum('admin','staff','user') NOT NULL DEFAULT 'user',
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
);

tabel : products

CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `price` int NOT NULL DEFAULT '0',
  `stock` int NOT NULL DEFAULT '0',
  `category` enum('food','drink','snack') NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `is_best_seller` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

tabel : order

CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `transaction_time` timestamp NOT NULL,
  `total_price` int NOT NULL,
  `total_item` int NOT NULL,
  `kasir_id` bigint unsigned NOT NULL,
  `payment_method` enum('Tunai','QRIS') NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `orders_kasir_id_foreign` (`kasir_id`),
  CONSTRAINT `orders_kasir_id_foreign` FOREIGN KEY (`kasir_id`) REFERENCES `users` (`id`)
);

Tabel : order_items

CREATE TABLE `order_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint unsigned NOT NULL,
  `product_id` bigint unsigned NOT NULL,
  `quantity` int NOT NULL,
  `total_price` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_items_order_id_foreign` (`order_id`),
  KEY `order_items_product_id_foreign` (`product_id`),
  CONSTRAINT `order_items_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `order_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
);

ER Diagram

DML

Data Manipulation Language adalah perintah SQL yang digunakan untuk mengelola dan memanipulasi data di dalam tabel database. Perintah DML mencakup:

SELECT: Mengambil data dari tabel.
INSERT: Menambahkan data baru ke tabel.
UPDATE: Memperbarui data yang sudah ada di tabel.
DELETE: Menghapus data dari tabel.

DML berfokus pada operasi yang dilakukan terhadap data yang tersimpan dalam struktur database.

Insert

INSERT INTO `users`
(name, email, phone, roles, password, created_at, updated_at)
VALUES 
('Budi', 'budi@gmail.com', '085644445555', 'user', SHA2('budi123', 256), NOW(), NOW()),
('Ayu', 'ayu@gmail.com', '085655556666', 'user', SHA2('ayu123', 256), NOW(), NOW()),
('Rina', 'rina@gmail.com', '085666667777', 'user', SHA2('rina123', 256), NOW(), NOW());

INSERT INTO `products` (name, description, price, stock, category, image, created_at, updated_at, is_best_seller)
VALUES
('Nasi Goreng', 'Nasi goreng dengan campuran sayuran dan daging ayam.', 25000, 50, 'food', 'nasi_goreng.jpg', NOW(), NOW(), 1),
('Es Teh Manis', 'Teh manis dingin yang segar.', 5000, 100, 'drink', 'es_teh_manis.jpg', NOW(), NOW(), 0),
('Keripik Kentang', 'Keripik kentang renyah dengan rasa gurih.', 15000, 30, 'snack', 'keripik_kentang.jpg', NOW(), NOW(), 0);

INSERT INTO `orders` (transaction_time, total_price, total_item, kasir_id, payment_method, created_at, updated_at)
VALUES
('2024-06-24 10:00:00', 40000, 2, 1, 'Tunai', NOW(), NOW()),
('2024-06-24 11:00:00', 20000, 1, 2, 'QRIS', NOW(), NOW()),
('2024-06-24 12:00:00', 30000, 3, 1, 'Tunai', NOW(), NOW());

INSERT INTO `order_items` (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(1, 1, 1, 25000, NOW(), NOW()),
(1, 2, 1, 15000, NOW(), NOW());


INSERT INTO `order_items` (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(2, 3, 1, 20000, NOW(), NOW());


INSERT INTO `order_items` (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(3, 1, 1, 25000, NOW(), NOW()),
(3, 3, 1, 15000, NOW(), NOW()),
(3, 2, 1, 10000, NOW(), NOW());

Select

SELECT 
    a.id AS order_id,
    a.transaction_time,
    a.total_price AS order_total_price,
    a.total_item AS order_total_item,
    b.name AS kasir_name,
    a.payment_method,
    d.name AS product_name,
    d.description AS product_description,
    d.price AS product_price,
    c.quantity,
    c.total_price AS item_total_price
FROM orders a
LEFT JOIN users b ON a.kasir_id = b.id
LEFT JOIN order_items c ON a.id = c.order_id
LEFT JOIN products d ON c.product_id = d.id
ORDER BY a.id;

Update

UPDATE order_items oi
JOIN products p ON oi.product_id = p.id
SET oi.quantity = 5,
    oi.total_price = 5 * p.price,
    oi.updated_at = NOW()
WHERE oi.order_id = 3
  AND oi.product_id = 3;

Delete

DELETE FROM order_items WHERE order_id = 3;
DELETE FROM orders WHERE id = 3;

View

Tabel Virtual yang dibuat berdasarkan hasil query SELECT.
View menyimpan query yang kompleks sehingga dapat diakses seperti tabel biasa.
Namun, view tidak menyimpan data secara fisik, data diambil dari tabel-tabel asli saat view diakses.
View dapat digunakan untuk menyederhanakan query yang kompleks,
meningkatkan keamanan data dengan membatasi akses ke kolom tertentu
membuat data lebih mudah diakses bagi pengguna.

order_details_view

CREATE VIEW order_details_view AS
SELECT 
    a.id AS order_id,
    a.transaction_time,
    a.total_price AS order_total_price,
    a.total_item AS order_total_item,
    b.name AS kasir_name,
    a.payment_method,
    d.name AS product_name,
    d.description AS product_description,
    d.price AS product_price,
    c.quantity,
    c.total_price AS item_total_price
FROM orders a
LEFT JOIN users b ON a.kasir_id = b.id
LEFT JOIN order_items c ON a.id = c.order_id
LEFT JOIN products d ON c.product_id = d.id;

product_sales_view

CREATE VIEW product_sales_view AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.total_price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

daily_sales_summary_view

CREATE VIEW daily_sales_summary_view AS
SELECT 
    DATE(a.transaction_time) AS transaction_date,
    COUNT(a.id) AS total_orders,
    SUM(a.total_price) AS total_sales_amount,
    SUM(a.total_item) AS total_items_sold
FROM orders a
GROUP BY DATE(a.transaction_time);

user_orders_view

CREATE VIEW user_orders_view AS
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.kasir_id
GROUP BY u.id, u.name;

best_seller_products_view

CREATE VIEW best_seller_products_view AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    SUM(oi.quantity) AS total_quantity_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY SUM(oi.quantity) DESC;

Store Procedure

Sekumpulan perintah SQL yang disimpan di dalam database dan dapat dijalankan berulang kali.
Menulis logika bisnis yang kompleks sekali saja, lalu memanggilnya berulang kali dari aplikasi atau skrip lain.
Peningkatan keamanan karena logika dan akses data dapat dibatasi di sisi server.

spProductList

CREATE PROCEDURE `cafe_db`.`spProductList`(
    IN search_name VARCHAR(255),
    IN search_category ENUM('food', 'drink', 'snack'),
    IN page INT,
    IN page_size INT
)
BEGIN
    DECLARE offset_val INT DEFAULT 0;
    SET offset_val = (page - 1) * page_size;
    
    SELECT *
    FROM products
    WHERE (search_name IS NULL OR name LIKE CONCAT('%', search_name, '%'))
      AND (search_category IS NULL OR search_category = '' OR category = search_category)
    ORDER BY id
    LIMIT page_size OFFSET offset_val;
END;

spProductDetail

CREATE PROCEDURE `cafe_db`.`spProductDetail`(
    IN product_id BIGINT UNSIGNED
)
BEGIN
    SELECT *
    FROM products
    WHERE id = product_id;
END

spProductAdd

CREATE PROCEDURE `cafe_db`.`spProductAdd`(
    IN p_name VARCHAR(255),
    IN p_description TEXT,
    IN p_price INT,
    IN p_stock INT,
    IN p_category ENUM('food', 'drink', 'snack'),
    IN p_image VARCHAR(255)
)
BEGIN
    DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);

    -- Check if name or category is empty
    IF p_name = '' OR p_name IS NULL OR p_category = '' OR p_category IS NULL THEN
        SET result_code = 39999;
        SET error_message = 'Semua kolom (nama, kategori) harus diisi.';
        SELECT result_code, error_message;
    ELSE
        -- Check if product name already exists
        IF EXISTS (SELECT 1 FROM products WHERE name = p_name LIMIT 1) THEN
            SET result_code = 39998;
            SET error_message = 'Nama produk sudah ada dalam database.';
            SELECT result_code, error_message;
        ELSE
            -- Insert new product
            INSERT INTO products (name, description, price, stock, category, image, created_at, updated_at)
            VALUES (p_name, p_description, p_price, p_stock, p_category, p_image, NOW(), NOW());

            SET result_code = 1;
            SET error_message = '';
            SELECT result_code, error_message;
        END IF;
    END IF;

END

spProductEdit

CREATE PROCEDURE `cafe_db`.`spProductEdit`(
    IN p_id BIGINT UNSIGNED,
    IN p_name VARCHAR(255),
    IN p_description TEXT,
    IN p_price INT,
    IN p_stock INT,
    IN p_category ENUM('food', 'drink', 'snack'),
    IN p_image VARCHAR(255)
)
begin
        DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);
    DECLARE product_count INT;

    -- Check if the product exists
    SELECT COUNT(*) INTO product_count
    FROM products
    WHERE id = p_id;

    IF product_count = 0 THEN
        SET result_code = 39999;
        SET error_message = 'Produk dengan ID yang diberikan tidak ditemukan.';
        SELECT result_code, error_message;
    ELSE
        IF p_name = '' OR p_name IS NULL OR p_category = '' OR p_category IS NULL THEN
            SET result_code = 39999;
                SET error_message = 'Semua kolom (nama, kategori) harus diisi.';
                SELECT result_code, error_message;
        ELSE
            UPDATE products
            SET name = p_name,
                description = p_description,
                price = p_price,
                stock = p_stock,
                category = p_category,
                image = p_image,
                updated_at = NOW()
            WHERE id = p_id;
           
                   SET result_code = 1;
            SET error_message = '';
            SELECT result_code, error_message;
        END IF;
    END IF;
END;

spProductDelete

CREATE PROCEDURE `cafe_db`.`spProductDelete`(
    IN p_id BIGINT UNSIGNED
)
BEGIN
    DECLARE item_count INT;
    DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);
    
    -- Cek apakah produk dengan ID p_id ada dalam tabel products
    SELECT COUNT(*) INTO item_count
    FROM products
    WHERE id = p_id;
    
    IF item_count = 0 THEN
        SET result_code = 39998;
        SET error_message = 'Produk dengan ID tersebut tidak ditemukan dalam database.';
        SELECT result_code, error_message;
    ELSE
        -- Cek apakah produk masih ada di order_items
        SELECT COUNT(*) INTO item_count
        FROM order_items
        WHERE product_id = p_id;
        
        IF item_count > 0 THEN
            SET result_code = 39999;
            SET error_message = 'Produk tidak dapat dihapus karena masih terdapat data di tabel order_items.';
            SELECT result_code, error_message;
        ELSE
            DELETE FROM products WHERE id = p_id;
            SET result_code = 1;
            SET error_message = '';
            SELECT result_code, error_message;
        END IF;
    END IF;
    
END

spProductStockAdd

CREATE PROCEDURE `cafe_db`.`spProductStockAdd`(
    IN p_product_id BIGINT UNSIGNED,
    IN p_add_stock INT
)
BEGIN
    DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);
    DECLARE current_stock INT;

    -- Check if the product exists
    IF NOT EXISTS (SELECT 1 FROM products WHERE id = p_product_id) THEN
        SET result_code = 0;
        SET error_message = 'Product ID does not exist';
    ELSE
        -- Get the current stock
        SELECT stock INTO current_stock FROM products WHERE id = p_product_id;

        -- Add the new stock
        UPDATE products SET stock = current_stock + p_add_stock, updated_at = NOW() WHERE id = p_product_id;

        SET result_code = 1;
        SET error_message = '';
    END IF;

    SELECT result_code, error_message;
END

Run SP

CALL spProductList('', null, 1, 10);
CALL spProductList('', 'food', 1, 10);
CALL spProductDetail(1);


CALL spProductAdd(null, 'Deskripsi Produk', 10000, 50, 'food', 'gambar.jpg');
CALL spProductAdd('', 'Deskripsi Produk', 10000, 50, 'food', 'gambar.jpg');
CALL spProductAdd('Nama Produk2', 'Deskripsi Produk', 10000, 50, 'food', 'gambar.jpg');

CALL spProductEdit(12, '', 'Nama Produk', 12000, 60, 'drink', 'gambar_baru.jpg');
CALL spProductEdit(11, null, 'Deskripsi Produk Baru', 12000, 60, 'drink', 'gambar_baru.jpg');
CALL spProductEdit(11, '', 'Deskripsi Produk Baru', 12000, 60, 'drink', 'gambar_baru.jpg');
CALL spProductEdit(14, 'Nama Produk', 'Nama Produk', 12000, 60, 'drink', 'gambar_baru.jpg');

CALL spProductDelete(1);
CALL spProductDelete(13);CALL spProductStockAdd(1, 10);

CALL spProductStockAdd(1, 10);

spOrderAdd

CREATE PROCEDURE `cafe_db`.`spOrderAdd`(
    IN p_transaction_time TIMESTAMP,
    IN p_total_price INT,
    IN p_total_item INT,
    IN p_kasir_id BIGINT UNSIGNED,
    IN p_payment_method ENUM('Tunai', 'QRIS')
)
begin
        DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);
   
    INSERT INTO orders (transaction_time, total_price, total_item, kasir_id, payment_method, created_at, updated_at)
    VALUES (p_transaction_time, p_total_price, p_total_item, p_kasir_id, p_payment_method, NOW(), NOW());
   
    SET result_code = 1;
    SET error_message = '';
    SELECT result_code, error_message;
END

spOrderItemAdd

CREATE PROCEDURE `cafe_db`.`spOrderItemAdd`(
    IN p_order_id BIGINT UNSIGNED,
    IN p_product_id BIGINT UNSIGNED,
    IN p_quantity INT,
    IN p_total_price INT
)
BEGIN
    DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);
    DECLARE product_stock INT;

    -- Check if the order exists
    IF NOT EXISTS (SELECT 1 FROM orders WHERE id = p_order_id) THEN
        SET result_code = 0;
        SET error_message = 'Order ID does not exist';
    
    -- Check if the product exists
    ELSEIF NOT EXISTS (SELECT 1 FROM products WHERE id = p_product_id) THEN
        SET result_code = 0;
        SET error_message = 'Product ID does not exist';
    
    -- Check if the stock is sufficient
    ELSE
        SELECT stock INTO product_stock FROM products WHERE id = p_product_id;
        IF product_stock < p_quantity THEN
            SET result_code = 0;
            SET error_message = 'Insufficient stock';
        ELSE
            -- Insert the order item
            INSERT INTO order_items (order_id, product_id, quantity, total_price, created_at, updated_at)
            VALUES (p_order_id, p_product_id, p_quantity, p_total_price, NOW(), NOW());

            -- Update the product stock
            UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

            SET result_code = 1;
            SET error_message = '';
        END IF;
    END IF;

    SELECT result_code, error_message;
END

Run SP

CALL spOrderAdd('2024-06-24 14:00:00', 50000, 3, 1, 'Tunai');
CALL spOrderItemAdd(9,1, 1, 10000);
CALL spProductStockAdd(1, 10);

spRegister

CREATE PROCEDURE `cafe_db`.`spRegister`(
    IN p_name VARCHAR(255),
    IN p_email VARCHAR(50),
    IN p_phone VARCHAR(25),
    IN p_roles ENUM('admin','staff','user'),
    IN p_password VARCHAR(255)
)
BEGIN
    DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);

    -- Check if the email already exists
    IF EXISTS (SELECT 1 FROM users WHERE email = p_email) THEN
        SET result_code = 0;
        SET error_message = 'Email already exists';
    ELSE
        -- Insert the new user with hashed password
        INSERT INTO users (name, email, phone, roles, password, created_at, updated_at)
        VALUES (p_name, p_email, p_phone, p_roles, SHA2(p_password, 256), NOW(), NOW());

        SET result_code = 1;
        SET error_message = '';
    END IF;

    SELECT result_code, error_message;
END

spLogin

CREATE PROCEDURE `cafe_db`.`spLogin`(
    IN p_email VARCHAR(50),
    IN p_password VARCHAR(255)
)
BEGIN
    DECLARE result_code INT;
    DECLARE error_message VARCHAR(1000);
    DECLARE user_id BIGINT UNSIGNED;
    DECLARE user_name VARCHAR(255);
    DECLARE user_roles ENUM('admin','staff','user');

    -- Check if the email and hashed password match
    IF EXISTS (SELECT 1 FROM users WHERE email = p_email AND password = SHA2(p_password, 256)) THEN
        -- Get user details
        SELECT id, name, roles INTO user_id, user_name, user_roles FROM users WHERE email = p_email AND password = SHA2(p_password, 256);
        
        SET result_code = 1; 
        SET error_message = '';
        
        SELECT result_code, error_message;
        SELECT user_id, user_name, user_roles;
    ELSE
        SET result_code = 0;
        SET error_message = 'Invalid email or password';
       SELECT result_code, error_message;
    END IF;

    
END

Run SP

select * from users;
CALL spRegister('John Doe', 'johndoe@example.com', '123456789', 'user', 'budi123');
CALL spLogin('johndoe@example.com', 'budi123');

Triggers

Sejenis prosedur yang secara otomatis dijalankan (atau "dipicu") oleh database sebagai respons terhadap peristiwa tertentu pada tabel, seperti operasi INSERT, UPDATE, atau DELETE.
Trigger digunakan untuk memastikan integritas data, melakukan validasi data, atau memelihara log perubahan data.
Dengan menggunakan trigger, kita dapat menjalankan kode secara otomatis setiap kali data di dalam tabel diubah.

spOrderTotalUpdate

CREATE PROCEDURE spOrderTotalUpdate(IN p_order_id BIGINT UNSIGNED)
BEGIN
    DECLARE v_total_price INT DEFAULT 0;
    DECLARE v_total_item INT DEFAULT 0;

    -- Menghitung total harga dan total item dari order_items
    SELECT SUM(total_price), SUM(quantity)
    INTO v_total_price, v_total_item
    FROM order_items
    WHERE order_id = p_order_id;

    -- Mengupdate kolom total_price dan total_item pada tabel orders
    UPDATE orders
    SET total_price = v_total_price, total_item = v_total_item, updated_at = NOW()
    WHERE id = p_order_id;
END

trigger order items

CREATE TRIGGER trg_after_insert_order_items
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    CALL spOrderTotalUpdate(NEW.order_id);
END

CREATE TRIGGER trg_after_update_order_items
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
    CALL spOrderTotalUpdate(NEW.order_id);
END

CREATE TRIGGER trg_after_delete_order_items
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    CALL spOrderTotalUpdate(OLD.order_id);
END

Events

Tugas yang dijadwalkan untuk dijalankan secara otomatis pada waktu tertentu atau secara berkala.
Events mirip dengan cron jobs di Linux atau scheduler di Windows. Dengan events,
Kita dapat mengotomatiskan berbagai tugas pemeliharaan basis data, seperti pembersihan data, pengarsipan, atau pengiriman laporan.

Tabel : order_report

CREATE TABLE order_report (
    order_id BIGINT UNSIGNED NOT NULL,
    transaction_time TIMESTAMP NOT NULL,
    order_total_price INT NOT NULL,
    order_total_item INT NOT NULL,
    kasir_name VARCHAR(255) NOT NULL,
    payment_method ENUM('Tunai', 'QRIS') NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    product_description TEXT,
    product_price INT NOT NULL,
    quantity INT NOT NULL,
    item_total_price INT NOT NULL,
    INDEX idx_transaction_time (transaction_time),
    INDEX idx_kasir_name (kasir_name),
    INDEX idx_payment_method (payment_method),
    INDEX idx_product_name (product_name)
);

spOrderReportAdd

CREATE PROCEDURE`cafe_db`.spOrderReportAdd()
BEGIN
    -- Insert data into order_report if it doesn't already exist
    INSERT INTO order_report (
        order_id,
        transaction_time,
        order_total_price,
        order_total_item,
        kasir_name,
        payment_method,
        product_name,
        product_description,
        product_price,
        quantity,
        item_total_price
    )
    SELECT
        v.order_id,
        v.transaction_time,
        v.order_total_price,
        v.order_total_item,
        v.kasir_name,
        v.payment_method,
        v.product_name,
        v.product_description,
        v.product_price,
        v.quantity,
        v.item_total_price
    FROM
        cafe_db.order_details_view v
    WHERE NOT EXISTS (
        SELECT 1
        FROM order_report r
        WHERE r.order_id = v.order_id
          AND r.product_name = v.product_name
          AND r.quantity = v.quantity
    )
    AND v.kasir_name IS NOT NULL
    AND v.payment_method IS NOT NULL
    AND v.product_name IS NOT NULL
    AND v.product_description IS NOT NULL
    AND v.product_price IS NOT NULL
    AND v.quantity IS NOT NULL
    AND v.item_total_price IS NOT NULL;
END

move_data_to_order_report

CREATE EVENT IF NOT EXISTS  move_data_to_order_report
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ON COMPLETION NOT PRESERVE
ENABLE
DO
    CALL spOrderReportAdd();

SELECT 
    EVENT_NAME, 
    STATUS, 
    LAST_EXECUTED 
FROM 
    information_schema.EVENTS 
WHERE 
    EVENT_SCHEMA = 'cafe_db' 
    AND EVENT_NAME = 'move_data_to_order_report';

SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE 'event_scheduler';
SELECT NOW();

Backup dan Restore

Backup adalah tindakan membuat salinan cadangan dari database. Ini penting untuk pemulihan data jika terjadi kegagalan sistem, kesalahan pengguna, atau kerusakan data.
Restore adalah proses mengembalikan database dari salinan cadangan. Ini digunakan saat Anda perlu memulihkan data dari backup karena hilang atau rusak.

Backup

Buka Command Line

jalankan Perintah


mysqldump -u root -p --routines cafe_db > cafe_db_backup20240701.sql

Restore

Buka Command Line

jalankan Perintah


mysql -u root -p cafe_db3 < cafe_db_backup20240701.sql

Database Migration

Fitur yang memungkinkan pengembang untuk mengelola dan memodifikasi struktur database secara versioning dan terprogram.
Dengan migration, kita bisa membuat, mengubah, dan menghapus tabel atau kolom dalam database menggunakan skrip bahasa pemrograman, bukan SQL mentah.
Migration memudahkan kolaborasi tim dan pengelolaan perubahan database, karena setiap perubahan dapat direkam, dibagikan, dan dijalankan dengan mudah di berbagai lingkungan (development, staging, production).

New Project Laravel

composer create-project --prefer-dist laravel/laravel cafe-project

cd cafe-project

code .

tabel : users

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->string('phone')->nullable();
    $table->enum('roles', ['admin', 'staff', 'user'])->default('user');
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

Database / Migrations / 0001_01_01_000000_create_users_table.php

tabel : products

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->text('description')->nullable();
    $table->integer('price')->default(0);
    $table->integer('stock')->default(0);
    $table->enum('category', ['food', 'drink', 'snack']);
    $table->string('image')->nullable();
    $table->boolean('is_best_seller')->default(false);
    $table->timestamps();
});

php artisan make:migration create_products_table

tabel : order

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->timestamp('transaction_time');
    $table->integer('total_price');
    $table->integer('total_item');
    $table->foreignId('kasir_id')->constrained('users');
    $table->enum('payment_method', ['Tunai', 'QRIS']);
    $table->timestamps();
});

php artisan make:migration create_orders_table

Tabel : order_items

Schema::create('order_items', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained('orders');
    $table->foreignId('product_id')->constrained('products');
    $table->integer('quantity');
    $table->integer('total_price');
    $table->timestamps();
});

php artisan make:migration create_order_items_table

Setting file .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=cafe_db4
DB_USERNAME=root
DB_PASSWORD=

Buat Database di mysql dengan nama : cafe_db4

php artisan migrate:fresh

Jalankan perintah

Database

Postgre SQL

PostgreSQL adalah sistem basis data relasional open-source yang dikenal dengan keandalan, fleksibilitas, dan kemampuannya untuk menangani berbagai jenis beban kerja.
PostgreSQL cocok untuk aplikasi-aplikasi yang membutuhkan basis data yang skalabel, tingkat konsistensi yang tinggi, dan kemampuan untuk mengelola data semi-struktured dengan efisien.
Beberapa sistem ERP (Enterprise Resource Planning) terkenal yang menggunakan PostgreSQL sebagai basis datanya yaitu ERPNext dan Odoo.

Database : cafe_db

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE,
  phone VARCHAR(25),
  roles VARCHAR(5) CHECK (roles IN ('admin', 'staff', 'user')) NOT NULL DEFAULT 'user',
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price INT NOT NULL DEFAULT 0,
  stock INT NOT NULL DEFAULT 0,
  category VARCHAR(5) CHECK (category IN ('food', 'drink', 'snack')) NOT NULL,
  image VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  is_best_seller BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  transaction_time TIMESTAMP NOT NULL,
  total_price INT NOT NULL,
  total_item INT NOT NULL,
  kasir_id BIGINT NOT NULL,
  payment_method VARCHAR(5) CHECK (payment_method IN ('Tunai', 'QRIS')) NOT NULL,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  CONSTRAINT orders_kasir_id_foreign FOREIGN KEY (kasir_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  total_price INT NOT NULL,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  CONSTRAINT order_items_order_id_foreign FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT order_items_product_id_foreign FOREIGN KEY (product_id) REFERENCES products(id)
);

Insert Data

CREATE EXTENSION IF NOT EXISTS pgcrypto;

INSERT INTO users
(name, email, phone, roles, password, created_at, updated_at)
VALUES 
('Budi', 'budi@gmail.com', '085644445555', 'user', crypt('budi123', gen_salt('bf')), NOW(), NOW()),
('Ayu', 'ayu@gmail.com', '085655556666', 'user', crypt('ayu123', gen_salt('bf')), NOW(), NOW()),
('Rina', 'rina@gmail.com', '085666667777', 'user', crypt('rina123', gen_salt('bf')), NOW(), NOW());

INSERT INTO products (name, description, price, stock, category, image, created_at, updated_at, is_best_seller)
VALUES
('Nasi Goreng', 'Nasi goreng dengan campuran sayuran dan daging ayam.', 25000, 50, 'food', 'nasi_goreng.jpg', NOW(), NOW(), TRUE),
('Es Teh Manis', 'Teh manis dingin yang segar.', 5000, 100, 'drink', 'es_teh_manis.jpg', NOW(), NOW(), FALSE),
('Keripik Kentang', 'Keripik kentang renyah dengan rasa gurih.', 15000, 30, 'snack', 'keripik_kentang.jpg', NOW(), NOW(), FALSE);

INSERT INTO orders (transaction_time, total_price, total_item, kasir_id, payment_method, created_at, updated_at)
VALUES
('2024-06-24 10:00:00', 40000, 2, 1, 'Tunai', NOW(), NOW()),
('2024-06-24 11:00:00', 20000, 1, 2, 'QRIS', NOW(), NOW()),
('2024-06-24 12:00:00', 30000, 3, 1, 'Tunai', NOW(), NOW());

INSERT INTO order_items (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(1, 1, 1, 25000, NOW(), NOW()),
(1, 2, 1, 15000, NOW(), NOW());

INSERT INTO order_items (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(2, 3, 1, 20000, NOW(), NOW());

INSERT INTO order_items (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(3, 1, 1, 25000, NOW(), NOW()),
(3, 3, 1, 15000, NOW(), NOW()),
(3, 2, 1, 10000, NOW(), NOW());

Select,Update, Delete

SELECT 
    a.id AS order_id,
    a.transaction_time,
    a.total_price AS order_total_price,
    a.total_item AS order_total_item,
    b.name AS kasir_name,
    a.payment_method,
    d.name AS product_name,
    d.description AS product_description,
    d.price AS product_price,
    c.quantity,
    c.total_price AS item_total_price
FROM orders a
LEFT JOIN users b ON a.kasir_id = b.id
LEFT JOIN order_items c ON a.id = c.order_id
LEFT JOIN products d ON c.product_id = d.id;

UPDATE order_items oi
SET quantity = 5,
    total_price = 5 * p.price,
    updated_at = CURRENT_TIMESTAMP
FROM products p
WHERE oi.order_id = 3
  AND oi.product_id = 3
  AND oi.product_id = p.id;

DELETE FROM order_items WHERE order_id = 3;

DELETE FROM orders WHERE id = 3;

View

CREATE OR REPLACE VIEW order_details_view AS
SELECT 
    a.id AS order_id,
    a.transaction_time,
    a.total_price AS order_total_price,
    a.total_item AS order_total_item,
    u.name AS kasir_name,
    a.payment_method,
    p.name AS product_name,
    p.description AS product_description,
    p.price AS product_price,
    oi.quantity,
    oi.total_price AS item_total_price
FROM orders a
LEFT JOIN users u ON a.kasir_id = u.id
LEFT JOIN order_items oi ON a.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id;

CREATE OR REPLACE VIEW product_sales_view AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    COALESCE(SUM(oi.quantity), 0) AS total_quantity_sold,
    COALESCE(SUM(oi.total_price), 0) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

CREATE OR REPLACE VIEW daily_sales_summary_view AS
SELECT 
    DATE_TRUNC('day', a.transaction_time) AS transaction_date,
    COUNT(a.id) AS total_orders,
    SUM(a.total_price) AS total_sales_amount,
    SUM(a.total_item) AS total_items_sold
FROM orders a
GROUP BY DATE_TRUNC('day', a.transaction_time);

CREATE OR REPLACE VIEW user_orders_view AS
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.kasir_id
GROUP BY u.id, u.name;

CREATE OR REPLACE VIEW best_seller_products_view AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    COALESCE(SUM(oi.quantity), 0) AS total_quantity_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY COALESCE(SUM(oi.quantity), 0) DESC; -- Memastikan penanganan nilai NULL dengan COALESCE

Sql Server

SQL Server adalah sistem manajemen basis data relasional (RDBMS) yang dikembangkan oleh Microsoft.
Terkenal dengan integrasinya yang erat dengan lingkungan Microsoft Windows dan platform Microsoft,
SQL Server menyediakan keandalan, keamanan, dan kinerja yang tinggi untuk aplikasi bisnis dan data warehousing.
SQL Server dapat menangani berbagai skala beban kerja dari aplikasi desktop hingga data center perusahaan dengan mudah.
Beberapa contoh ERP (Enterprise Resource Planning) yang menggunakan SQL Server sebagai basis datanya antara lain: Microsoft Dynamics 365 dan SAP ERP

Database : cafe_db

CREATE TABLE users (
  id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL,
  email varchar(50) NOT NULL UNIQUE,
  phone varchar(25) NULL,
  roles varchar(10) NOT NULL CHECK (roles IN ('admin', 'staff', 'user')) DEFAULT 'user',
  password varchar(255) NOT NULL,
  created_at datetime NULL,
  updated_at datetime NULL
);

CREATE TABLE products (
  id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL,
  description text NULL,
  price int NOT NULL DEFAULT 0,
  stock int NOT NULL DEFAULT 0,
  category varchar(10) NOT NULL CHECK (category IN ('food', 'drink', 'snack')),
  image varchar(255) NULL,
  created_at datetime NULL,
  updated_at datetime NULL,
  is_best_seller bit NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
  transaction_time datetime NOT NULL,
  total_price int NOT NULL,
  total_item int NOT NULL,
  kasir_id bigint NOT NULL,
  payment_method varchar(10) NOT NULL CHECK (payment_method IN ('Tunai', 'QRIS')),
  created_at datetime NULL,
  updated_at datetime NULL,
  CONSTRAINT fk_orders_kasir_id FOREIGN KEY (kasir_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
  order_id bigint NOT NULL,
  product_id bigint NOT NULL,
  quantity int NOT NULL,
  total_price int NOT NULL,
  created_at datetime NULL,
  updated_at datetime NULL,
  CONSTRAINT fk_order_items_order_id FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_order_items_product_id FOREIGN KEY (product_id) REFERENCES products(id)
);

Insert Data

INSERT INTO users (name, email, phone, roles, password, created_at, updated_at)
VALUES 
('Budi', 'budi@gmail.com', '085644445555', 'user', HASHBYTES('SHA2_256', 'budi123'), GETDATE(), GETDATE()),
('Ayu', 'ayu@gmail.com', '085655556666', 'user', HASHBYTES('SHA2_256', 'ayu123'), GETDATE(), GETDATE()),
('Rina', 'rina@gmail.com', '085666667777', 'user', HASHBYTES('SHA2_256', 'rina123'), GETDATE(), GETDATE());

INSERT INTO products (name, description, price, stock, category, image, created_at, updated_at, is_best_seller)
VALUES
('Nasi Goreng', 'Nasi goreng dengan campuran sayuran dan daging ayam.', 25000, 50, 'food', 'nasi_goreng.jpg', GETDATE(), GETDATE(), 1),
('Es Teh Manis', 'Teh manis dingin yang segar.', 5000, 100, 'drink', 'es_teh_manis.jpg', GETDATE(), GETDATE(), 0),
('Keripik Kentang', 'Keripik kentang renyah dengan rasa gurih.', 15000, 30, 'snack', 'keripik_kentang.jpg', GETDATE(), GETDATE(), 0);

INSERT INTO orders (transaction_time, total_price, total_item, kasir_id, payment_method, created_at, updated_at)
VALUES
('2024-06-24 10:00:00', 40000, 2, 1, 'Tunai', GETDATE(), GETDATE()),
('2024-06-24 11:00:00', 20000, 1, 2, 'QRIS', GETDATE(), GETDATE()),
('2024-06-24 12:00:00', 30000, 3, 1, 'Tunai', GETDATE(), GETDATE());

INSERT INTO order_items (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(1, 1, 1, 25000, GETDATE(), GETDATE()),
(1, 2, 1, 15000, GETDATE(), GETDATE());

INSERT INTO order_items (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(2, 3, 1, 20000, GETDATE(), GETDATE());

INSERT INTO order_items (order_id, product_id, quantity, total_price, created_at, updated_at)
VALUES
(3, 1, 1, 25000, GETDATE(), GETDATE()),
(3, 3, 1, 15000, GETDATE(), GETDATE()),
(3, 2, 1, 10000, GETDATE(), GETDATE());

Select, Update, Delete

SELECT 
    a.id AS order_id,
    a.transaction_time,
    a.total_price AS order_total_price,
    a.total_item AS order_total_item,
    b.name AS kasir_name,
    a.payment_method,
    d.name AS product_name,
    d.description AS product_description,
    d.price AS product_price,
    c.quantity,
    c.total_price AS item_total_price
FROM orders a
LEFT JOIN users b ON a.kasir_id = b.id
LEFT JOIN order_items c ON a.id = c.order_id
LEFT JOIN products d ON c.product_id = d.id;

UPDATE oi
SET oi.quantity = 5,
    oi.total_price = 5 * p.price,
    oi.updated_at = GETDATE()
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE oi.order_id = 3
  AND oi.product_id = 3;

DELETE FROM order_items WHERE order_id = 3;

DELETE FROM orders WHERE id = 3;

View

CREATE VIEW order_details_view AS
SELECT 
    a.id AS order_id,
    a.transaction_time,
    a.total_price AS order_total_price,
    a.total_item AS order_total_item,
    b.name AS kasir_name,
    a.payment_method,
    d.name AS product_name,
    d.description AS product_description,
    d.price AS product_price,
    c.quantity,
    c.total_price AS item_total_price
FROM orders a
LEFT JOIN users b ON a.kasir_id = b.id
LEFT JOIN order_items c ON a.id = c.order_id
LEFT JOIN products d ON c.product_id = d.id;

CREATE VIEW product_sales_view AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.total_price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

CREATE VIEW daily_sales_summary_view AS
SELECT 
    CONVERT(date, a.transaction_time) AS transaction_date,
    COUNT(a.id) AS total_orders,
    SUM(a.total_price) AS total_sales_amount,
    SUM(a.total_item) AS total_items_sold
FROM orders a
GROUP BY CONVERT(date, a.transaction_time);

CREATE VIEW user_orders_view AS
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.kasir_id
GROUP BY u.id, u.name;

CREATE VIEW best_seller_products_view AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    SUM(oi.quantity) AS total_quantity_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

spProductList

CREATE PROCEDURE dbo.spProductList
    @search_name NVARCHAR(255),
    @search_category NVARCHAR(50),
    @page INT,
    @page_size INT
AS
BEGIN
    DECLARE @offset_val INT;
    SET @offset_val = (@page - 1) * @page_size;
    
    SELECT *
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (ORDER BY id) AS row_num
        FROM products
        WHERE (@search_name IS NULL OR name LIKE '%' + @search_name + '%')
          AND (@search_category IS NULL OR @search_category = '' OR category = @search_category)
    ) AS numbered_products
    WHERE row_num > @offset_val
      AND row_num <= @offset_val + @page_size;
END;

spProductDetail

CREATE PROCEDURE dbo.spProductDetail
    @product_id BIGINT
AS
BEGIN
    SELECT *
    FROM products
    WHERE id = @product_id;
END;

spProductAdd

CREATE PROCEDURE dbo.spProductAdd
    @p_name NVARCHAR(255),
    @p_description NVARCHAR(MAX),
    @p_price INT,
    @p_stock INT,
    @p_category NVARCHAR(50),
    @p_image NVARCHAR(255)
AS
BEGIN
    DECLARE @result_code INT;
    DECLARE @error_message NVARCHAR(1000);

    -- Check if name or category is empty
    IF @p_name IS NULL OR @p_name = '' OR @p_category IS NULL OR @p_category = '' BEGIN
        SET @result_code = 39999;
        SET @error_message = 'Semua kolom (nama, kategori) harus diisi.';
        SELECT @result_code AS result_code, @error_message AS error_message;
    END
    ELSE BEGIN
        -- Check if product name already exists
        IF EXISTS (SELECT 1 FROM products WHERE name = @p_name) BEGIN
            SET @result_code = 39998;
            SET @error_message = 'Nama produk sudah ada dalam database.';
            SELECT @result_code AS result_code, @error_message AS error_message;
        END
        ELSE BEGIN
            -- Insert new product
            INSERT INTO products (name, description, price, stock, category, image, created_at, updated_at)
            VALUES (@p_name, @p_description, @p_price, @p_stock, @p_category, @p_image, GETDATE(), GETDATE());

            SET @result_code = 1;
            SET @error_message = '';
            SELECT @result_code AS result_code, @error_message AS error_message;
        END
    END
END;

spProductEdit

CREATE PROCEDURE dbo.spProductEdit
    @p_id BIGINT,
    @p_name NVARCHAR(255),
    @p_description NVARCHAR(MAX),
    @p_price INT,
    @p_stock INT,
    @p_category NVARCHAR(50),
    @p_image NVARCHAR(255)
AS
BEGIN
    DECLARE @result_code INT;
    DECLARE @error_message NVARCHAR(1000);
    DECLARE @product_count INT;

    -- Check if the product exists
    SELECT @product_count = COUNT(*)
    FROM products
    WHERE id = @p_id;

    IF @product_count = 0 BEGIN
        SET @result_code = 39999;
        SET @error_message = 'Produk dengan ID yang diberikan tidak ditemukan.';
        SELECT @result_code AS result_code, @error_message AS error_message;
    END
    ELSE BEGIN
        IF @p_name IS NULL OR @p_name = '' OR @p_category IS NULL OR @p_category = '' BEGIN
            SET @result_code = 39999;
            SET @error_message = 'Semua kolom (nama, kategori) harus diisi.';
            SELECT @result_code AS result_code, @error_message AS error_message;
        END
        ELSE BEGIN
            -- Update product
            UPDATE products
            SET name = @p_name,
                description = @p_description,
                price = @p_price,
                stock = @p_stock,
                category = @p_category,
                image = @p_image,
                updated_at = GETDATE()
            WHERE id = @p_id;

            SET @result_code = 1;
            SET @error_message = '';
            SELECT @result_code AS result_code, @error_message AS error_message;
        END
    END
END;

spProductStockAdd

CREATE PROCEDURE dbo.spProductStockAdd
    @p_product_id BIGINT,
    @p_add_stock INT
AS
BEGIN
    DECLARE @result_code INT;
    DECLARE @error_message NVARCHAR(1000);
    DECLARE @current_stock INT;

    -- Check if the product exists
    IF NOT EXISTS (SELECT 1 FROM products WHERE id = @p_product_id) BEGIN
        SET @result_code = 0;
        SET @error_message = 'Product ID does not exist';
    END
    ELSE BEGIN
        -- Get the current stock
        SELECT @current_stock = stock FROM products WHERE id = @p_product_id;

        -- Add the new stock
        UPDATE products SET stock = @current_stock + @p_add_stock, updated_at = GETDATE() WHERE id = @p_product_id;

        SET @result_code = 1;
        SET @error_message = '';
    END

    SELECT @result_code AS result_code, @error_message AS error_message;
END;

spProductDelete

CREATE PROCEDURE dbo.spProductDelete
    @p_id BIGINT
AS
BEGIN
    DECLARE @item_count INT;
    DECLARE @result_code INT;
    DECLARE @error_message NVARCHAR(1000);
    
    -- Check if the product exists in the products table
    SELECT @item_count = COUNT(*)
    FROM products
    WHERE id = @p_id;
    
    IF @item_count = 0 BEGIN
        SET @result_code = 39998;
        SET @error_message = 'Produk dengan ID tersebut tidak ditemukan dalam database.';
        SELECT @result_code AS result_code, @error_message AS error_message;
    END
    ELSE BEGIN
        -- Check if the product is still in order_items
        SELECT @item_count = COUNT(*)
        FROM order_items
        WHERE product_id = @p_id;
        
        IF @item_count > 0 BEGIN
            SET @result_code = 39999;
            SET @error_message = 'Produk tidak dapat dihapus karena masih terdapat data di tabel order_items.';
            SELECT @result_code AS result_code, @error_message AS error_message;
        END
        ELSE BEGIN
            DELETE FROM products WHERE id = @p_id;
            SET @result_code = 1;
            SET @error_message = '';
            SELECT @result_code AS result_code, @error_message AS error_message;
        END
    END
END;

run sp

EXEC dbo.spProductList
    @search_name = '', 
    @search_category = 'food', 
    @page = 1, 
    @page_size = 10; 
   
EXEC dbo.spProductDetail @product_id=1;

EXEC dbo.spProductAdd
    @p_name = 'Nama Produk',
    @p_description = 'Deskripsi produk',
    @p_price = 25000,
    @p_stock = 50,
    @p_category = 'food',
    @p_image = 'nama_file_gambar.jpg';
   
EXEC dbo.spProductEdit
    @p_id = 1, 
    @p_name = 'Nama Produk Baru',
    @p_description = 'Deskripsi produk baru',
    @p_price = 30000,
    @p_stock = 100,
    @p_category = 'drink',
    @p_image = 'nama_file_gambar_baru.jpg';
   
EXEC dbo.spProductStockAdd
    @p_product_id = 1, 
    @p_add_stock = 20;
   
EXEC dbo.spProductDelete
    @p_id = 1;

Tugas Kelompok Uji Kompetensi Berbasis Industri Bidang Sistem Basis Data

Mahasiswa mengerjakan tugas kelompok yang terdiri dari 4 - 5 mahasiswa (data terlampir)

Setiap kelompok mengambil 1 tema yang disediakan

Dari tema yang dipilih, buatmodel ERD
Pilih database yang digunakan (mysql, sql server, postgre sql)
Buat database dengan database dan tema yang sudah dipilih dengan kriteria mengandung

Minimal 4 tabel
Data Definition Language
Data Manipulation Language
Views
Store Procedure
Trigger
Event

Hasil dikirimkan ke Google Form
paling lambat tanggal 18 Juli 2024 Jam 23:59 berupa
File PDF berisi ERD dan penjelasan perintah SQL
Template : https://docs.google.com/document/d/1nhs4lWAhxNEh9nXY0_V_MLWJ9GYtR-BK/edit?usp=drive_link&ouid=104170590410504636333&rtpof=true&sd=true
Contoh Tugas : https://docs.google.com/document/d/1KRaGtejf8H9TRObRH1FV6lKXDfTLLBBh/edit?usp=sharing&ouid=104170590410504636333&rtpof=true&sd=true
File SQL hasil export database

Rererensi

Eko Kurniawan Khannedy (Belajar Basis Data)
Eko Kurniawan Khannedy (Mysql Database)

Akses Slide

Akses Materi

Materi Workshop

Basis Data

Apa Itu Basis Data

Tujuan Basis Data

Sistem Basis Data

Komponen Sistem Basis Data

DBMS

Jenis DBMS

SQL

Mysql vs Maria DB

Install Mysql

Database Client

Tipe Data

Entity Relationship Diagram

Entity

Attribute

Relationship

dbdiagram.io

users

products

orders

order_details

Relationship

ER Diagram

DDL

Buat Database cafe_db

tabel : users

tabel : products

tabel : order

Tabel : order_items

ER Diagram

DML

Insert

Select

Update

Delete

View

order_details_view

product_sales_view

daily_sales_summary_view

user_orders_view

best_seller_products_view

Store Procedure

spProductList

spProductDetail

spProductAdd

spProductEdit

spProductDelete

spProductStockAdd

Run SP

spOrderAdd

spOrderItemAdd

Run SP

spRegister

spLogin

Run SP

Triggers

spOrderTotalUpdate

trigger order items

Events

Tabel : order_report

spOrderReportAdd

move_data_to_order_report

Backup dan Restore

Backup

Restore

Database Migration

New Project Laravel

tabel : users

tabel : products

tabel : order

Tabel : order_items

Setting file .env

Database

Postgre SQL

Database : cafe_db

Insert Data

Select,Update, Delete

Buat Database
cafe_db