Instalasi & Konfigurasi Perangkat Lunak DBMS

A. Tujuan Praktikum

1. Mahasiswa mampu menginstal Laragon beserta server MySQL/MariaDB.
2. Mahasiswa mampu menjalankan, menghentikan, dan memeriksa layanan DBMS.
3. Mahasiswa mampu menghubungkan SQLyog ke server MySQL dan menjalankan query pertama.

B. Alat dan Bahan

• Laptop/komputer Windows 10/11, RAM minimal 4 GB
• Installer Laragon Full (laragon.org) yang sudah berisi MySQL dan HeidiSQL
• Installer SQLyog Community Edition
• Web browser

C. Dasar Teori

DBMS (Database Management System) adalah perangkat lunak yang mengatur penyimpanan, pengambilan, keamanan, dan integritas data. Contohnya MySQL, MariaDB, PostgreSQL, dan SQL Server. DBMS bekerja dengan arsitektur client–server:

• Server (mysqld) berjalan di latar belakang dan mendengarkan port 3306.
• Client (SQLyog, HeidiSQL, terminal mysql, atau aplikasi PHP) mengirim perintah SQL ke server dan menampilkan hasilnya.

Laragon adalah paket lingkungan pengembangan lokal yang menyatukan Apache/Nginx, PHP, dan MySQL dalam satu aplikasi yang ringan. Akun bawaan MySQL di Laragon adalah root tanpa password.

D. Langkah Praktikum

1. Instal Laragon. Jalankan installer, pilih direktori C:\laragon, lalu selesaikan instalasi.
2. Jalankan layanan. Buka Laragon, klik Start All. Pastikan indikator Apache dan MySQL berwarna hijau. Klik kanan → MySQL → Version untuk melihat versi yang dipakai.
3. Uji lewat terminal. Klik tombol Terminal di Laragon, lalu jalankan:

   mysql -u root -p
   -- tekan Enter saat diminta password (kosong)
   
   SELECT VERSION();
   SHOW DATABASES;
   STATUS;
   EXIT;

4. Instal SQLyog, lalu buat koneksi baru:

   Saved Connection	Lokal-Laragon
   MySQL Host Address	127.0.0.1
   Username / Password	root / (kosong)
   Port	3306

   Klik Test Connection, lalu Connect.
5. Jalankan query pertama di Query Editor SQLyog (tekan F9 untuk menjalankan):

   SELECT VERSION() AS versi_server,
          CURRENT_USER() AS user_aktif,
          NOW() AS waktu_server;
   
   SHOW VARIABLES LIKE 'port';
   SHOW VARIABLES LIKE 'datadir';
   SHOW ENGINES;

6. Kenali antarmuka SQLyog: Object Browser (kiri), Query Editor (atas), Result/Messages (bawah), serta menu History.

E. Tugas

F. Indikator Ketercapaian

G. Refleksi

• Saya dapat menyalakan dan mematikan layanan MySQL di Laragon.
• Saya dapat membuat koneksi SQLyog ke server lokal.
• Saya memahami perbedaan server dan client DBMS.

Membuat Database, Tabel, Relasi & Constraint Sederhana

A. Tujuan Praktikum

1. Mahasiswa mampu membuat database baru melalui GUI SQLyog dan perintah SQL.
2. Mahasiswa mampu membuat tabel yang saling berelasi.
3. Mahasiswa mampu menambahkan constraint sederhana (PRIMARY KEY, NOT NULL, DEFAULT).

B. Alat dan Bahan

• Laragon (MySQL aktif), SQLyog, koneksi Lokal-Laragon dari Pertemuan 1

C. Dasar Teori

Basis data relasional menyimpan data dalam tabel (relasi). Tabel terdiri atas kolom (atribut) dan baris (record/tuple). Antartabel dihubungkan melalui key:

Relasi paling umum adalah one-to-many: satu kategori memiliki banyak buku, sedangkan satu buku hanya masuk satu kategori.

D. Langkah Praktikum

1. Buat database via GUI. Klik kanan koneksi → Create Database. Isi nama db_latihan, charset utf8mb4, collation utf8mb4_general_ci. Amati perintah SQL yang dibuat SQLyog di tab History.
2. Buat database studi kasus via SQL:

   CREATE DATABASE IF NOT EXISTS db_perpustakaan
     CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
   USE db_perpustakaan;

3. Buat tabel induk kategori:

   CREATE TABLE kategori (
     id_kategori   INT AUTO_INCREMENT PRIMARY KEY,
     nama_kategori VARCHAR(50) NOT NULL
   ) ENGINE=InnoDB;

4. Buat tabel anak buku yang berelasi ke kategori:

   CREATE TABLE buku (
     id_buku      INT AUTO_INCREMENT PRIMARY KEY,
     judul        VARCHAR(150) NOT NULL,
     stok         INT NOT NULL DEFAULT 0,
     id_kategori  INT,
     FOREIGN KEY (id_kategori) REFERENCES kategori(id_kategori)
   ) ENGINE=InnoDB;

5. Periksa struktur:

   SHOW TABLES;
   DESC kategori;
   DESC buku;
   SHOW CREATE TABLE buku;

6. Uji relasi dan constraint:

   INSERT INTO kategori (nama_kategori) VALUES ('Basis Data'), ('Pemrograman');
   INSERT INTO buku (judul, id_kategori) VALUES ('Dasar-Dasar SQL', 1);
   
   -- Coba perintah berikut lalu amati pesan errornya:
   INSERT INTO buku (judul, id_kategori) VALUES ('Buku Misterius', 99);
   INSERT INTO kategori (nama_kategori) VALUES (NULL);
   
   SELECT * FROM buku;   -- perhatikan nilai stok (DEFAULT)

7. Lihat relasi secara visual: di SQLyog buka Powertools → Schema Designer, lalu seret tabel kategori dan buku ke kanvas.

E. Tugas Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: Mahasiswa dapat menginstal dan membuat DB dengan benar.

G. Refleksi

• Saya dapat membuat database dan tabel melalui GUI maupun SQL.
• Saya memahami mengapa FK menolak data yang tidak punya induk.

Tipe Data, Primary Key & Foreign Key

A. Tujuan Praktikum

1. Mahasiswa mampu memilih tipe data yang tepat untuk setiap atribut.
2. Mahasiswa mampu menerapkan primary key tunggal dan komposit.
3. Mahasiswa mampu menerapkan foreign key beserta aksi ON DELETE/ON UPDATE.

B. Alat dan Bahan

• Laragon, SQLyog, database db_perpustakaan hasil Pertemuan 2

C. Dasar Teori

Tipe data MySQL yang sering dipakai

Aturan praktis: NIM dan ISBN disimpan sebagai VARCHAR/CHAR, bukan INT. Nilai itu tidak dihitung secara matematis, dan angka nol di depannya harus tetap tersimpan.

Aksi referensial foreign key

D. Langkah Praktikum

1. Tabel anggota dengan tipe data yang tepat:

   USE db_perpustakaan;
   
   CREATE TABLE anggota (
     id_anggota  INT AUTO_INCREMENT PRIMARY KEY,
     nim         CHAR(10)     NOT NULL,
     nama        VARCHAR(100) NOT NULL,
     prodi       VARCHAR(50)  NOT NULL,
     email       VARCHAR(100),
     tgl_daftar  DATE         NOT NULL DEFAULT (CURRENT_DATE)
   ) ENGINE=InnoDB;

   DEFAULT (CURRENT_DATE) membutuhkan MySQL 8.0.13+. Jika versi Anda lebih lama, gunakan DATETIME DEFAULT CURRENT_TIMESTAMP.
2. Tabel petugas dan peminjaman dengan ENUM dan dua FK:

   CREATE TABLE petugas (
     id_petugas    INT AUTO_INCREMENT PRIMARY KEY,
     nama_petugas  VARCHAR(100) NOT NULL,
     username      VARCHAR(30)  NOT NULL
   ) ENGINE=InnoDB;
   
   CREATE TABLE peminjaman (
     id_pinjam        INT AUTO_INCREMENT PRIMARY KEY,
     id_anggota       INT  NOT NULL,
     id_petugas       INT  NOT NULL,
     tgl_pinjam       DATE NOT NULL,
     tgl_jatuh_tempo  DATE NOT NULL,
     status           ENUM('dipinjam','kembali','terlambat') NOT NULL DEFAULT 'dipinjam',
     CONSTRAINT fk_pinjam_anggota FOREIGN KEY (id_anggota)
         REFERENCES anggota(id_anggota) ON UPDATE CASCADE ON DELETE RESTRICT,
     CONSTRAINT fk_pinjam_petugas FOREIGN KEY (id_petugas)
         REFERENCES petugas(id_petugas) ON UPDATE CASCADE ON DELETE RESTRICT
   ) ENGINE=InnoDB;

3. Primary key komposit pada tabel detail:

   CREATE TABLE detail_peminjaman (
     id_pinjam    INT NOT NULL,
     id_buku      INT NOT NULL,
     jumlah       TINYINT UNSIGNED NOT NULL DEFAULT 1,
     tgl_kembali  DATE NULL,
     denda        DECIMAL(10,2) NOT NULL DEFAULT 0,
     PRIMARY KEY (id_pinjam, id_buku),
     CONSTRAINT fk_detail_pinjam FOREIGN KEY (id_pinjam)
         REFERENCES peminjaman(id_pinjam) ON DELETE CASCADE,
     CONSTRAINT fk_detail_buku FOREIGN KEY (id_buku)
         REFERENCES buku(id_buku) ON DELETE RESTRICT
   ) ENGINE=InnoDB;

4. Uji perilaku CASCADE dan RESTRICT:

   INSERT INTO anggota (nim, nama, prodi) VALUES ('2404010001','Rina','Sistem Informasi');
   INSERT INTO petugas (nama_petugas, username) VALUES ('Pak Budi','budi');
   INSERT INTO peminjaman (id_anggota, id_petugas, tgl_pinjam, tgl_jatuh_tempo)
   VALUES (1, 1, CURDATE(), CURDATE() + INTERVAL 7 DAY);
   INSERT INTO detail_peminjaman (id_pinjam, id_buku) VALUES (1, 1);
   
   DELETE FROM anggota WHERE id_anggota = 1;      -- amati: ditolak (RESTRICT)
   DELETE FROM peminjaman WHERE id_pinjam = 1;    -- amati: detail ikut terhapus (CASCADE)
   SELECT * FROM detail_peminjaman;

5. Lihat seluruh FK di database melalui katalog sistem:

   SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
          REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
   FROM information_schema.KEY_COLUMN_USAGE
   WHERE TABLE_SCHEMA = 'db_perpustakaan'
     AND REFERENCED_TABLE_NAME IS NOT NULL;

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: Mahasiswa dapat menginstal dan membuat DB dengan benar.

G. Refleksi

• Saya dapat membedakan kapan memakai CHAR, VARCHAR, INT, DECIMAL, dan DATE.
• Saya dapat membuat PK komposit untuk tabel penghubung.
• Saya memahami efek CASCADE, RESTRICT, dan SET NULL.

Identifikasi Entitas, Atribut & Relasi

A. Tujuan Praktikum

1. Mahasiswa mampu menemukan entitas dari narasi kebutuhan sistem.
2. Mahasiswa mampu mengklasifikasikan atribut (key, simple, composite, multivalued, derived).
3. Mahasiswa mampu menentukan relasi beserta kardinalitas dan partisipasinya.

B. Alat dan Bahan

• Deskripsi kebutuhan sistem (bagian Studi Kasus), kertas/alat tulis, stabilo

C. Dasar Teori

Jenis atribut: key (pengenal unik, misal NIM), simple (tidak dapat dipecah), composite (dapat dipecah, misal alamat → jalan, kota), multivalued (bernilai banyak, misal no_hp), dan derived (dihitung dari atribut lain, misal lama_terlambat dari tgl_jatuh_tempo dan tgl_kembali).

D. Langkah Praktikum

1. Teknik noun–verb. Baca narasi studi kasus. Garis bawahi setiap kata benda dan lingkari setiap kata kerja.
2. Saring kandidat entitas. Buang kata benda yang hanya merupakan atribut (judul, kota) atau sistem itu sendiri (perpustakaan).
3. Contoh hasil sebagian (lengkapi sendiri pada tugas):

   Entitas	Atribut	Key
   BUKU	isbn, judul, tahun_terbit, stok	id_buku
   ANGGOTA	nim, nama, prodi, email, tgl_daftar	id_anggota
   …	…	…

4. Tentukan relasi dengan kalimat baku "Satu A … berapa B?" dan "Satu B … berapa A?". Contoh:
   • Satu kategori memiliki banyak buku; satu buku masuk satu kategori → 1:N
   • Satu buku ditulis banyak penulis; satu penulis menulis banyak buku → M:N
5. Diskusi kelompok (15 menit): bandingkan daftar entitas antar kelompok, lalu sepakati daftar final.

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: ERD sesuai kebutuhan sistem, konversi ke skema relasional tanpa error.

G. Refleksi

• Saya dapat membedakan entitas dan atribut dari sebuah narasi.
• Saya dapat menentukan kardinalitas 1:1, 1:N, dan M:N.

Membuat Diagram ERD

A. Tujuan Praktikum

1. Mahasiswa mampu menggambar ERD dengan notasi Chen dan Crow's Foot.
2. Mahasiswa mampu menyatakan kardinalitas dan partisipasi dalam diagram.
3. Mahasiswa mampu menggunakan tool ERD (draw.io / SQLyog Schema Designer).

B. Alat dan Bahan

• Web browser → app.diagrams.net (draw.io), hasil identifikasi Pertemuan 4

C. Dasar Teori

Notasi Chen cocok untuk tahap konseptual. Crow's Foot lebih dekat ke implementasi tabel dan dipakai oleh kebanyakan tool desain database.

Contoh ERD (Crow's Foot) — bagian inti perpustakaan

Baca: satu ANGGOTA melakukan nol/banyak PEMINJAMAN; satu PEMINJAMAN berisi satu/banyak DETAIL; satu BUKU muncul di nol/banyak DETAIL; satu KATEGORI memiliki nol/banyak BUKU.

D. Langkah Praktikum

1. Buka app.diagrams.net → Create New Diagram → Blank. Simpan ke perangkat dengan nama ERD_Perpustakaan_NIM.drawio.
2. Aktifkan pustaka bentuk Entity Relation (klik + More Shapes di kiri bawah).
3. Versi Chen: gambar entitas (persegi), atribut (elips, garis bawahi key), dan relasi (belah ketupat). Beri label kardinalitas 1/N/M di setiap garis.
4. Versi Crow's Foot: gunakan bentuk Table, tulis atribut dan tandai PK/FK. Hubungkan dengan garis bertipe ERmandOne, ERmany, ERzeroToMany, dan seterusnya.
5. Pecah relasi M:N (BUKU–PENULIS) menjadi entitas asosiatif BUKU_PENULIS.
6. Ekspor diagram: File → Export as → PNG (centang Include a copy of my diagram).

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: ERD sesuai kebutuhan sistem, konversi ke skema relasional tanpa error.

G. Refleksi

• Saya dapat membaca dan menggambar notasi Crow's Foot.
• Saya tahu cara memecah relasi M:N.

Konversi ERD ke Skema Relasional pada DBMS

A. Tujuan Praktikum

1. Mahasiswa mampu menerapkan aturan transformasi ERD ke skema relasional.
2. Mahasiswa mampu mengimplementasikan skema relasional di MySQL tanpa error.
3. Mahasiswa mampu memverifikasi skema dengan reverse engineering (Schema Designer).

B. Alat dan Bahan

• ERD hasil Pertemuan 5, Laragon, SQLyog

C. Dasar Teori: Aturan Transformasi

Notasi skema relasional: BUKU(id_buku, isbn, judul, tahun_terbit, stok, id_kategori, id_penerbit). PK digaris bawahi, FK dicetak miring.

D. Langkah Praktikum

1. Tulis skema relasional dari ERD Pertemuan 5 dalam notasi di atas untuk ke-9 tabel.
2. Urutkan pembuatan tabel: tabel induk (tanpa FK) dibuat lebih dulu, lalu tabel anak. Urutan yang benar: kategori → penerbit → penulis → anggota → petugas → buku → buku_penulis → peminjaman → detail_peminjaman.
3. Contoh implementasi relasi M:N:

   USE db_perpustakaan;
   
   CREATE TABLE IF NOT EXISTS penulis (
     id_penulis    INT AUTO_INCREMENT PRIMARY KEY,
     nama_penulis  VARCHAR(100) NOT NULL
   ) ENGINE=InnoDB;
   
   CREATE TABLE IF NOT EXISTS buku_penulis (
     id_buku     INT NOT NULL,
     id_penulis  INT NOT NULL,
     PRIMARY KEY (id_buku, id_penulis),
     FOREIGN KEY (id_buku)    REFERENCES buku(id_buku)       ON DELETE CASCADE,
     FOREIGN KEY (id_penulis) REFERENCES penulis(id_penulis) ON DELETE CASCADE
   ) ENGINE=InnoDB;

4. Contoh atribut multivalued (no_hp anggota bisa lebih dari satu):

   CREATE TABLE anggota_telepon (
     id_anggota  INT NOT NULL,
     no_hp       VARCHAR(15) NOT NULL,
     PRIMARY KEY (id_anggota, no_hp),
     FOREIGN KEY (id_anggota) REFERENCES anggota(id_anggota) ON DELETE CASCADE
   ) ENGINE=InnoDB;

5. Verifikasi: jalankan SHOW TABLES;, lalu buka Schema Designer SQLyog dan seret semua tabel. Bandingkan hasilnya dengan ERD Pertemuan 5. Garis relasi harus sama.
6. Ekspor skema: klik kanan database → Backup/Export → Backup Database As SQL Dump → pilih Structure only. Simpan sebagai skema_NIM.sql.

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: ERD sesuai kebutuhan sistem, konversi ke skema relasional tanpa error.

G. Refleksi

• Saya dapat menentukan letak FK untuk setiap jenis relasi.
• Skema di DBMS saya sama dengan ERD yang saya rancang.

SQL DDL: CREATE, ALTER, DROP

A. Tujuan Praktikum

1. Mahasiswa mampu menulis perintah CREATE untuk database, tabel, dan view.
2. Mahasiswa mampu mengubah struktur tabel dengan ALTER (ADD, MODIFY, CHANGE, RENAME, DROP COLUMN, ADD CONSTRAINT).
3. Mahasiswa mampu menghapus objek dengan DROP dan TRUNCATE, serta memahami risikonya.

B. Alat dan Bahan

• Laragon, SQLyog, database db_latihan (untuk eksperimen) dan db_perpustakaan

C. Dasar Teori

DDL (Data Definition Language) adalah perintah yang mendefinisikan struktur objek basis data, bukan isinya. Perintah DDL di MySQL bersifat auto-commit sehingga tidak dapat dibatalkan dengan ROLLBACK.

D. Langkah Praktikum

Bagian 1 — Eksperimen ALTER di db_latihan

USE db_latihan;

CREATE TABLE mahasiswa (
  nim   CHAR(10) PRIMARY KEY,
  nama  VARCHAR(50)
);

ALTER TABLE mahasiswa ADD alamat VARCHAR(100);                  -- tambah kolom
ALTER TABLE mahasiswa ADD email VARCHAR(80) AFTER nama;         -- tambah di posisi tertentu
ALTER TABLE mahasiswa MODIFY nama VARCHAR(100) NOT NULL;        -- ubah tipe/constraint
ALTER TABLE mahasiswa CHANGE alamat alamat_rumah VARCHAR(150);  -- ganti nama + tipe
ALTER TABLE mahasiswa RENAME COLUMN email TO email_kampus;      -- ganti nama saja (MySQL 8)
ALTER TABLE mahasiswa DROP COLUMN alamat_rumah;                 -- hapus kolom
RENAME TABLE mahasiswa TO mhs;
DESC mhs;

DROP TABLE mhs;
DROP DATABASE db_latihan;

Bagian 2 — Skema resmi db_perpustakaan

Agar seluruh kelas memakai struktur yang sama untuk Pertemuan 8–16, buat ulang database dengan script berikut. Simpan sebagai 01_skema.sql.

DROP DATABASE IF EXISTS db_perpustakaan;
CREATE DATABASE db_perpustakaan CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE db_perpustakaan;

CREATE TABLE kategori (
  id_kategori    INT AUTO_INCREMENT PRIMARY KEY,
  nama_kategori  VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE penerbit (
  id_penerbit    INT AUTO_INCREMENT PRIMARY KEY,
  nama_penerbit  VARCHAR(100) NOT NULL,
  kota           VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE penulis (
  id_penulis    INT AUTO_INCREMENT PRIMARY KEY,
  nama_penulis  VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE buku (
  id_buku       INT AUTO_INCREMENT PRIMARY KEY,
  isbn          VARCHAR(17)  NOT NULL,
  judul         VARCHAR(150) NOT NULL,
  tahun_terbit  YEAR,
  stok          INT NOT NULL DEFAULT 0,
  id_kategori   INT NOT NULL,
  id_penerbit   INT NOT NULL,
  CONSTRAINT fk_buku_kategori FOREIGN KEY (id_kategori) REFERENCES kategori(id_kategori),
  CONSTRAINT fk_buku_penerbit FOREIGN KEY (id_penerbit) REFERENCES penerbit(id_penerbit)
) ENGINE=InnoDB;

CREATE TABLE buku_penulis (
  id_buku     INT NOT NULL,
  id_penulis  INT NOT NULL,
  PRIMARY KEY (id_buku, id_penulis),
  CONSTRAINT fk_bp_buku    FOREIGN KEY (id_buku)    REFERENCES buku(id_buku)       ON DELETE CASCADE,
  CONSTRAINT fk_bp_penulis FOREIGN KEY (id_penulis) REFERENCES penulis(id_penulis) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE anggota (
  id_anggota  INT AUTO_INCREMENT PRIMARY KEY,
  nim         CHAR(10)     NOT NULL,
  nama        VARCHAR(100) NOT NULL,
  prodi       VARCHAR(50)  NOT NULL,
  email       VARCHAR(100),
  tgl_daftar  DATE NOT NULL
) ENGINE=InnoDB;

CREATE TABLE petugas (
  id_petugas    INT AUTO_INCREMENT PRIMARY KEY,
  nama_petugas  VARCHAR(100) NOT NULL,
  username      VARCHAR(30)  NOT NULL
) ENGINE=InnoDB;

CREATE TABLE peminjaman (
  id_pinjam        INT AUTO_INCREMENT PRIMARY KEY,
  id_anggota       INT  NOT NULL,
  id_petugas       INT  NOT NULL,
  tgl_pinjam       DATE NOT NULL,
  tgl_jatuh_tempo  DATE NOT NULL,
  status           ENUM('dipinjam','kembali','terlambat') NOT NULL DEFAULT 'dipinjam',
  CONSTRAINT fk_pinjam_anggota FOREIGN KEY (id_anggota) REFERENCES anggota(id_anggota),
  CONSTRAINT fk_pinjam_petugas FOREIGN KEY (id_petugas) REFERENCES petugas(id_petugas)
) ENGINE=InnoDB;

CREATE TABLE detail_peminjaman (
  id_pinjam    INT NOT NULL,
  id_buku      INT NOT NULL,
  jumlah       TINYINT UNSIGNED NOT NULL DEFAULT 1,
  tgl_kembali  DATE NULL,
  denda        DECIMAL(10,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id_pinjam, id_buku),
  CONSTRAINT fk_detail_pinjam FOREIGN KEY (id_pinjam) REFERENCES peminjaman(id_pinjam) ON DELETE CASCADE,
  CONSTRAINT fk_detail_buku   FOREIGN KEY (id_buku)   REFERENCES buku(id_buku)
) ENGINE=InnoDB;

Bagian 3 — ALTER pada skema resmi

-- Tambah kolom sinopsis lalu hapus kembali
ALTER TABLE buku ADD sinopsis TEXT AFTER judul;
ALTER TABLE buku DROP COLUMN sinopsis;

-- Tambah & hapus constraint bernama
ALTER TABLE anggota ADD CONSTRAINT uq_anggota_nim UNIQUE (nim);
SHOW INDEX FROM anggota;
ALTER TABLE anggota DROP INDEX uq_anggota_nim;

-- Bandingkan DROP vs TRUNCATE (di tabel sementara)
CREATE TABLE log_uji (id INT AUTO_INCREMENT PRIMARY KEY, ket VARCHAR(20));
INSERT INTO log_uji (ket) VALUES ('a'),('b'),('c');
TRUNCATE TABLE log_uji;
INSERT INTO log_uji (ket) VALUES ('d');
SELECT * FROM log_uji;     -- perhatikan id kembali ke 1
DROP TABLE log_uji;

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: Query SQL menghasilkan output yang benar.

G. Refleksi

• Saya dapat mengubah struktur tabel tanpa menghapus datanya.
• Saya memiliki file 01_skema.sql yang berjalan tanpa error.

SQL DML: INSERT, UPDATE, DELETE

A. Tujuan Praktikum

1. Mahasiswa mampu menambahkan data tunggal dan banyak baris dengan INSERT.
2. Mahasiswa mampu mengubah dan menghapus data secara selektif dengan UPDATE/DELETE + WHERE.
3. Mahasiswa mampu menggunakan transaksi (START TRANSACTION, COMMIT, ROLLBACK) untuk manipulasi data yang aman.

B. Alat dan Bahan

• Laragon, SQLyog, skema resmi 01_skema.sql dari Pertemuan 7

C. Dasar Teori

INSERT INTO tabel (kolom1, kolom2) VALUES (nilai1, nilai2), (nilai3, nilai4);
UPDATE tabel SET kolom1 = nilai_baru WHERE kondisi;
DELETE FROM tabel WHERE kondisi;

D. Langkah Praktikum

1. Data awal (seed). Jalankan script berikut dan simpan sebagai 02_data.sql. Data ini dipakai sampai Pertemuan 16.

   USE db_perpustakaan;
   
   INSERT INTO kategori (nama_kategori) VALUES
    ('Basis Data'),('Pemrograman'),('Jaringan Komputer'),
    ('Sistem Informasi'),('Kecerdasan Buatan'),('Multimedia');
   
   INSERT INTO penerbit (nama_penerbit, kota) VALUES
    ('Nusantara Informatika','Bandung'),('Pustaka Andalas','Padang'),
    ('Riau Media','Pekanbaru'),('Cakrawala Ilmu','Yogyakarta');
   
   INSERT INTO penulis (nama_penulis) VALUES
    ('Ahmad Fauzi'),('Siti Rahmawati'),('Rudi Hartanto'),
    ('Nurul Aini'),('Bayu Pratama'),('Lina Marlina');
   
   INSERT INTO buku (isbn, judul, tahun_terbit, stok, id_kategori, id_penerbit) VALUES
    ('978-602-0001-01-1','Dasar-Dasar Basis Data',2021,5,1,1),
    ('978-602-0001-02-8','SQL untuk Pemula',2022,4,1,3),
    ('978-602-0001-03-5','Perancangan Database Relasional',2020,3,1,2),
    ('978-602-0001-04-2','Pemrograman PHP dan MySQL',2023,6,2,1),
    ('978-602-0001-05-9','Algoritma dan Struktur Data',2019,2,2,4),
    ('978-602-0001-06-6','Jaringan Komputer Terapan',2021,3,3,3),
    ('978-602-0001-07-3','Analisis dan Desain Sistem Informasi',2022,4,4,2),
    ('978-602-0001-08-0','Machine Learning Praktis',2024,2,5,1),
    ('978-602-0001-09-7','Optimasi Query MySQL',2024,1,1,3),
    ('978-602-0001-10-3','Pemrograman Python Dasar',2023,5,2,4);
   
   INSERT INTO buku_penulis VALUES
    (1,1),(1,2),(2,1),(3,2),(3,3),(4,3),(5,4),(6,5),(7,2),(7,4),(8,5),(9,1),(10,4);
   
   INSERT INTO anggota (nim, nama, prodi, email, tgl_daftar) VALUES
    ('2404010001','Rina Agustina','Sistem Informasi','rina@student.umri.ac.id','2026-08-20'),
    ('2404010002','Fajar Nugroho','Sistem Informasi','fajar@student.umri.ac.id','2026-08-20'),
    ('2404020003','Putri Ayu','Teknik Informatika','putri@student.umri.ac.id','2026-08-21'),
    ('2404020004','Dimas Saputra','Teknik Informatika',NULL,'2026-08-22'),
    ('2304010005','Salsabila','Sistem Informasi','salsa@student.umri.ac.id','2025-09-01'),
    ('2304020006','Rizky Ramadhan','Teknik Informatika','rizky@student.umri.ac.id','2025-09-02'),
    ('2404010007','Nadia Putri','Sistem Informasi','nadia@student.umri.ac.id','2026-09-10'),
    ('2404030008','Arif Hidayat','Ilmu Komunikasi',NULL,'2026-09-15');
   
   INSERT INTO petugas (nama_petugas, username) VALUES
    ('Budi Santoso','budi'),('Sari Wulandari','sari');
   
   INSERT INTO peminjaman (id_anggota, id_petugas, tgl_pinjam, tgl_jatuh_tempo, status) VALUES
    (1,1,'2026-09-01','2026-09-08','kembali'),
    (2,1,'2026-09-03','2026-09-10','kembali'),
    (3,2,'2026-09-05','2026-09-12','kembali'),
    (1,2,'2026-09-20','2026-09-27','kembali'),
    (5,1,'2026-10-01','2026-10-08','kembali'),
    (6,2,'2026-10-05','2026-10-12','terlambat'),
    (2,1,'2026-10-25','2026-11-01','terlambat'),
    (7,2,'2026-11-03','2026-11-10','dipinjam');
   
   INSERT INTO detail_peminjaman (id_pinjam, id_buku, jumlah, tgl_kembali, denda) VALUES
    (1,1,1,'2026-09-07',0),(1,4,1,'2026-09-07',0),
    (2,2,1,'2026-09-10',0),
    (3,1,1,'2026-09-15',3000),(3,3,1,'2026-09-15',3000),
    (4,9,1,'2026-09-26',0),
    (5,4,1,'2026-10-12',4000),(5,10,1,'2026-10-12',4000),
    (6,8,1,NULL,0),
    (7,1,1,NULL,0),(7,7,1,NULL,0),
    (8,4,1,NULL,0);

2. Periksa jumlah baris tiap tabel:

   SELECT 'buku' AS tabel, COUNT(*) AS jml FROM buku
   UNION ALL SELECT 'anggota', COUNT(*) FROM anggota
   UNION ALL SELECT 'peminjaman', COUNT(*) FROM peminjaman
   UNION ALL SELECT 'detail_peminjaman', COUNT(*) FROM detail_peminjaman;

3. UPDATE selektif:

   -- cek dulu baris yang akan terkena
   SELECT * FROM buku WHERE id_kategori = 1;
   -- tambah stok 2 untuk semua buku kategori Basis Data
   UPDATE buku SET stok = stok + 2 WHERE id_kategori = 1;
   -- ubah email anggota tertentu
   UPDATE anggota SET email = 'dimas@student.umri.ac.id' WHERE nim = '2404020004';

4. DELETE selektif & INSERT … SELECT:

   CREATE TABLE anggota_arsip LIKE anggota;
   INSERT INTO anggota_arsip SELECT * FROM anggota WHERE tgl_daftar < '2026-01-01';
   SELECT * FROM anggota_arsip;
   
   INSERT INTO anggota (nim, nama, prodi, tgl_daftar) VALUES ('2499999999','Data Uji','Uji',CURDATE());
   DELETE FROM anggota WHERE nim = '2499999999';

5. Transaksi peminjaman (simpan header + detail + kurangi stok sebagai satu kesatuan):

   START TRANSACTION;
   INSERT INTO peminjaman (id_anggota, id_petugas, tgl_pinjam, tgl_jatuh_tempo)
   VALUES (4, 1, CURDATE(), CURDATE() + INTERVAL 7 DAY);
   SET @id = LAST_INSERT_ID();
   INSERT INTO detail_peminjaman (id_pinjam, id_buku) VALUES (@id, 2);
   UPDATE buku SET stok = stok - 1 WHERE id_buku = 2;
   SELECT * FROM peminjaman WHERE id_pinjam = @id;
   ROLLBACK;   -- batalkan dulu, cek bahwa data tidak tersimpan
   -- ulangi langkah di atas dengan COMMIT untuk menyimpan permanen

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: Query SQL menghasilkan output yang benar.

G. Refleksi

• Saya selalu menjalankan SELECT sebelum UPDATE/DELETE.
• Saya memiliki file 02_data.sql yang berjalan tanpa error.

SELECT: Filter, Sorting & Fungsi Agregasi

A. Tujuan Praktikum

1. Mahasiswa mampu memfilter data dengan WHERE (perbandingan, BETWEEN, IN, LIKE, IS NULL).
2. Mahasiswa mampu mengurutkan dan membatasi hasil (ORDER BY, LIMIT).
3. Mahasiswa mampu meringkas data dengan COUNT, SUM, AVG, MIN, MAX, GROUP BY, dan HAVING.

B. Alat dan Bahan

• Laragon, SQLyog, db_perpustakaan berisi data dari 02_data.sql

C. Dasar Teori

Urutan penulisan klausa SELECT berbeda dari urutan eksekusinya:

Urutan tulis   : SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
Urutan eksekusi: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Karena itu, alias kolom dari SELECT tidak bisa dipakai di WHERE, dan filter terhadap hasil agregat harus memakai HAVING, bukan WHERE.

D. Langkah Praktikum

1. Proyeksi & alias:

   SELECT judul AS 'Judul Buku', tahun_terbit AS Tahun, stok FROM buku;
   SELECT DISTINCT prodi FROM anggota;

2. Filter:

   SELECT * FROM buku WHERE stok <= 2;
   SELECT * FROM buku WHERE tahun_terbit BETWEEN 2021 AND 2023;
   SELECT * FROM buku WHERE id_kategori IN (1, 2);
   SELECT * FROM buku WHERE judul LIKE '%data%';
   SELECT * FROM anggota WHERE email IS NULL;
   SELECT * FROM anggota WHERE prodi = 'Sistem Informasi' AND tgl_daftar >= '2026-01-01';

3. Sorting & pembatasan:

   SELECT judul, tahun_terbit FROM buku ORDER BY tahun_terbit DESC, judul ASC;
   SELECT judul, stok FROM buku ORDER BY stok DESC LIMIT 3;
   SELECT judul FROM buku ORDER BY judul LIMIT 5 OFFSET 5;   -- halaman ke-2

4. Fungsi agregat:

   SELECT COUNT(*) AS jml_buku, SUM(stok) AS total_stok,
          AVG(stok) AS rata_stok, MIN(tahun_terbit) AS terlama, MAX(tahun_terbit) AS terbaru
   FROM buku;
   
   SELECT COUNT(email) AS punya_email, COUNT(*) AS semua FROM anggota;  -- COUNT(kolom) mengabaikan NULL

5. GROUP BY & HAVING:

   SELECT id_kategori, COUNT(*) AS jml_judul, SUM(stok) AS total_stok
   FROM buku
   GROUP BY id_kategori
   HAVING COUNT(*) >= 2
   ORDER BY jml_judul DESC;
   
   SELECT DATE_FORMAT(tgl_pinjam, '%Y-%m') AS bulan, COUNT(*) AS transaksi
   FROM peminjaman
   GROUP BY bulan;

6. Fungsi bantu: UPPER(), CONCAT(), YEAR(), DATEDIFF(), IFNULL(), ROUND().

   SELECT CONCAT(nim, ' - ', UPPER(nama)) AS mahasiswa,
          IFNULL(email, '(belum ada)') AS email
   FROM anggota;

E. Tugas Project Dinilai

Tulis query dan tempel hasil (atau ringkasan hasil) di kotak jawaban.

F. Indikator & Rubrik Penilaian

Indikator RPS: Query SQL menghasilkan output yang benar.

G. Refleksi

• Saya tahu kapan memakai WHERE dan kapan memakai HAVING.
• Saya memahami urutan eksekusi klausa SELECT.

Query Lanjutan: JOIN, Subquery & Nested Query

A. Tujuan Praktikum

1. Mahasiswa mampu menggabungkan beberapa tabel dengan INNER JOIN, LEFT JOIN, dan RIGHT JOIN.
2. Mahasiswa mampu menulis subquery pada WHERE, FROM, dan SELECT.
3. Mahasiswa mampu menyusun nested/correlated query serta menggabungkannya dengan agregasi.

B. Alat dan Bahan

• Laragon, SQLyog, db_perpustakaan dengan data 02_data.sql

C. Dasar Teori

D. Langkah Praktikum

1. INNER JOIN dua dan tiga tabel:

   SELECT b.judul, k.nama_kategori, p.nama_penerbit
   FROM buku b
   JOIN kategori k ON k.id_kategori = b.id_kategori
   JOIN penerbit p ON p.id_penerbit = b.id_penerbit
   ORDER BY k.nama_kategori, b.judul;

2. JOIN melalui tabel penghubung (M:N):

   SELECT b.judul, GROUP_CONCAT(pn.nama_penulis SEPARATOR ', ') AS penulis
   FROM buku b
   JOIN buku_penulis bp ON bp.id_buku = b.id_buku
   JOIN penulis pn      ON pn.id_penulis = bp.id_penulis
   GROUP BY b.id_buku, b.judul;

3. LEFT JOIN untuk mencari data "yatim":

   -- kategori yang belum punya buku
   SELECT k.nama_kategori, COUNT(b.id_buku) AS jml_buku
   FROM kategori k
   LEFT JOIN buku b ON b.id_kategori = k.id_kategori
   GROUP BY k.id_kategori, k.nama_kategori;
   
   -- anggota yang belum pernah meminjam
   SELECT a.nim, a.nama
   FROM anggota a
   LEFT JOIN peminjaman pj ON pj.id_anggota = a.id_anggota
   WHERE pj.id_pinjam IS NULL;

4. Laporan transaksi lengkap (5 tabel):

   SELECT pj.id_pinjam, pj.tgl_pinjam, a.nama AS peminjam,
          pt.nama_petugas, b.judul, d.tgl_kembali, d.denda
   FROM peminjaman pj
   JOIN anggota a            ON a.id_anggota = pj.id_anggota
   JOIN petugas pt           ON pt.id_petugas = pj.id_petugas
   JOIN detail_peminjaman d  ON d.id_pinjam  = pj.id_pinjam
   JOIN buku b               ON b.id_buku    = d.id_buku
   ORDER BY pj.tgl_pinjam;

5. Subquery di WHERE:

   -- buku dengan stok di atas rata-rata
   SELECT judul, stok FROM buku
   WHERE stok > (SELECT AVG(stok) FROM buku);
   
   -- buku yang pernah dipinjam
   SELECT judul FROM buku
   WHERE id_buku IN (SELECT DISTINCT id_buku FROM detail_peminjaman);
   
   -- buku yang tidak pernah dipinjam (NOT EXISTS)
   SELECT judul FROM buku b
   WHERE NOT EXISTS (SELECT 1 FROM detail_peminjaman d WHERE d.id_buku = b.id_buku);

6. Subquery di FROM (derived table) dan di SELECT (correlated):

   SELECT t.nama, t.jml_pinjam
   FROM (
     SELECT a.nama, COUNT(*) AS jml_pinjam
     FROM peminjaman pj JOIN anggota a ON a.id_anggota = pj.id_anggota
     GROUP BY a.id_anggota, a.nama
   ) AS t
   WHERE t.jml_pinjam = (
     SELECT MAX(c) FROM (SELECT COUNT(*) AS c FROM peminjaman GROUP BY id_anggota) x
   );
   
   SELECT b.judul,
          (SELECT COUNT(*) FROM detail_peminjaman d WHERE d.id_buku = b.id_buku) AS kali_dipinjam
   FROM buku b
   ORDER BY kali_dipinjam DESC;

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: Query SQL menghasilkan output yang benar.

G. Refleksi

• Saya dapat memilih INNER atau LEFT JOIN sesuai pertanyaan.
• Saya dapat mengubah subquery IN menjadi JOIN dan sebaliknya.

Normalisasi hingga Bentuk Normal Ketiga (3NF)

A. Tujuan Praktikum

1. Mahasiswa mampu mengenali anomali insert, update, dan delete pada tabel yang belum normal.
2. Mahasiswa mampu menentukan ketergantungan fungsional (functional dependency).
3. Mahasiswa mampu menormalisasi tabel dari UNF ke 1NF, 2NF, dan 3NF, lalu mengimplementasikannya.

B. Alat dan Bahan

• Laragon, SQLyog, kertas kerja, formulir peminjaman manual (di bawah)

C. Dasar Teori

Ketergantungan fungsional X → Y berarti setiap nilai X selalu menentukan tepat satu nilai Y. Contoh: nim → nama, isbn → judul.

D. Langkah Praktikum

1. Formulir manual (UNF). Perpustakaan lama mencatat peminjaman di Excel seperti ini:

   no_pinjam	tgl	nim	nama	prodi	petugas	isbn / judul / kategori / penerbit / kota
   P001	01-09-2026	2404010001	Rina	SI	Budi	978..01 / Dasar-Dasar Basis Data / Basis Data / Nusantara Informatika / Bandung 978..04 / Pemrograman PHP dan MySQL / Pemrograman / Nusantara Informatika / Bandung
   P002	03-09-2026	2404010002	Fajar	SI	Budi	978..02 / SQL untuk Pemula / Basis Data / Riau Media / Pekanbaru

2. Identifikasi anomali:
   • Insert: buku baru tidak bisa dicatat sebelum ada yang meminjam.
   • Update: jika kota penerbit pindah, semua baris harus diubah satu per satu.
   • Delete: menghapus P002 ikut menghilangkan satu-satunya informasi buku "SQL untuk Pemula".
3. 1NF: pecah grup berulang sehingga satu baris = satu buku dalam satu peminjaman.

   PINJAM_1NF(no_pinjam, isbn, tgl, nim, nama, prodi, petugas,
              judul, kategori, penerbit, kota)
   PK = (no_pinjam, isbn)

4. Tentukan FD:

   no_pinjam      → tgl, nim, petugas
   nim            → nama, prodi
   isbn           → judul, kategori, penerbit
   penerbit       → kota
   (no_pinjam, isbn) → (seluruh atribut)

5. 2NF (hilangkan partial dependency terhadap PK komposit):

   PEMINJAMAN(no_pinjam, tgl, nim, nama, prodi, petugas)
   BUKU(isbn, judul, kategori, penerbit, kota)
   DETAIL(no_pinjam, isbn)

6. 3NF (hilangkan transitive dependency):

   ANGGOTA(nim, nama, prodi)
   PEMINJAMAN(no_pinjam, tgl, nim, petugas)
   PENERBIT(penerbit, kota)
   BUKU(isbn, judul, kategori, penerbit)
   DETAIL(no_pinjam, isbn)

   Bandingkan hasilnya dengan skema resmi db_perpustakaan. Mengapa skema resmi juga memecah kategori dan petugas ke tabel tersendiri?
7. Bukti di DBMS: buat tabel UNF/1NF sementara di database db_normalisasi, isi dengan data di atas, lalu tunjukkan anomali update:

   CREATE DATABASE db_normalisasi; USE db_normalisasi;
   CREATE TABLE pinjam_1nf (
     no_pinjam CHAR(4), isbn VARCHAR(17), tgl DATE, nim CHAR(10), nama VARCHAR(50),
     prodi VARCHAR(30), petugas VARCHAR(30), judul VARCHAR(150), kategori VARCHAR(50),
     penerbit VARCHAR(100), kota VARCHAR(50), PRIMARY KEY (no_pinjam, isbn));
   -- isi data, lalu coba:
   UPDATE pinjam_1nf SET kota = 'Cimahi' WHERE no_pinjam = 'P001' AND isbn LIKE '%01%';
   SELECT DISTINCT penerbit, kota FROM pinjam_1nf;  -- satu penerbit kini punya 2 kota!

E. Tugas Project Dinilai

Diberikan nota apotek berikut:

F. Indikator & Rubrik Penilaian

Indikator RPS: Normalisasi hingga 3NF, constraint & security diterapkan.

G. Refleksi

• Saya dapat menemukan partial dan transitive dependency.
• Saya memahami hubungan normalisasi dengan hasil ERD.

Integritas Data dengan Constraint

A. Tujuan Praktikum

1. Mahasiswa mampu membedakan integritas entitas, referensial, dan domain.
2. Mahasiswa mampu menambahkan constraint CHECK, UNIQUE, NOT NULL, dan FK pada tabel yang sudah berisi data.
3. Mahasiswa mampu menguji constraint dengan data yang melanggar aturan.

B. Alat dan Bahan

• Laragon (MySQL 8.0.16+ agar CHECK benar-benar ditegakkan), SQLyog, db_perpustakaan

C. Dasar Teori

D. Langkah Praktikum

1. Periksa data yang akan melanggar sebelum menambah constraint:

   USE db_perpustakaan;
   SELECT isbn, COUNT(*) FROM buku GROUP BY isbn HAVING COUNT(*) > 1;
   SELECT nim,  COUNT(*) FROM anggota GROUP BY nim HAVING COUNT(*) > 1;
   SELECT * FROM buku WHERE stok < 0;

2. Tambahkan UNIQUE dan CHECK:

   ALTER TABLE buku
     ADD CONSTRAINT uq_buku_isbn   UNIQUE (isbn),
     ADD CONSTRAINT ck_buku_stok   CHECK (stok >= 0),
     ADD CONSTRAINT ck_buku_tahun  CHECK (tahun_terbit BETWEEN 1950 AND 2100);
   
   ALTER TABLE anggota
     ADD CONSTRAINT uq_anggota_nim   UNIQUE (nim),
     ADD CONSTRAINT uq_anggota_email UNIQUE (email),
     ADD CONSTRAINT ck_anggota_nim   CHECK (nim REGEXP '^[0-9]{10}$'),
     ADD CONSTRAINT ck_anggota_email CHECK (email IS NULL OR email LIKE '%_@_%._%');
   
   ALTER TABLE petugas ADD CONSTRAINT uq_petugas_username UNIQUE (username);
   
   ALTER TABLE peminjaman
     ADD CONSTRAINT ck_pinjam_tempo CHECK (tgl_jatuh_tempo > tgl_pinjam);
   
   ALTER TABLE detail_peminjaman
     ADD CONSTRAINT ck_detail_jumlah CHECK (jumlah BETWEEN 1 AND 3),
     ADD CONSTRAINT ck_detail_denda  CHECK (denda >= 0);

3. Ubah aksi FK (drop lalu tambah ulang):

   ALTER TABLE buku DROP FOREIGN KEY fk_buku_kategori;
   ALTER TABLE buku ADD CONSTRAINT fk_buku_kategori
     FOREIGN KEY (id_kategori) REFERENCES kategori(id_kategori)
     ON UPDATE CASCADE ON DELETE RESTRICT;

4. Uji dengan data yang melanggar (setiap perintah harus GAGAL; catat pesan errornya):

   INSERT INTO buku (isbn, judul, tahun_terbit, stok, id_kategori, id_penerbit)
   VALUES ('978-602-0001-01-1','Duplikat ISBN',2024,1,1,1);         -- UNIQUE
   UPDATE buku SET stok = -1 WHERE id_buku = 9;                        -- CHECK stok
   INSERT INTO anggota (nim, nama, prodi, tgl_daftar)
   VALUES ('24ABC', 'Salah NIM', 'SI', CURDATE());                     -- CHECK nim
   INSERT INTO anggota (nim, nama, prodi, tgl_daftar)
   VALUES ('2404010099', NULL, 'SI', CURDATE());                       -- NOT NULL
   INSERT INTO peminjaman (id_anggota, id_petugas, tgl_pinjam, tgl_jatuh_tempo)
   VALUES (1, 1, '2026-11-10', '2026-11-01');                          -- CHECK tempo
   INSERT INTO detail_peminjaman (id_pinjam, id_buku) VALUES (1, 999); -- FK

5. Lihat seluruh constraint:

   SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
   FROM information_schema.TABLE_CONSTRAINTS
   WHERE TABLE_SCHEMA = 'db_perpustakaan'
   ORDER BY TABLE_NAME, CONSTRAINT_TYPE;
   
   SELECT CONSTRAINT_NAME, CHECK_CLAUSE
   FROM information_schema.CHECK_CONSTRAINTS
   WHERE CONSTRAINT_SCHEMA = 'db_perpustakaan';

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator: constraint integritas (CHECK, UNIQUE, NOT NULL, FK) diterapkan dan teruji.

G. Refleksi

• Saya dapat menambahkan constraint pada tabel yang sudah berisi data.
• Saya memberi nama setiap constraint agar mudah dihapus/diubah.

Mengelola User, Hak Akses & Keamanan Basis Data

A. Tujuan Praktikum

1. Mahasiswa mampu membuat user dan mengatur password.
2. Mahasiswa mampu memberikan dan mencabut hak akses (GRANT/REVOKE) dengan prinsip least privilege.
3. Mahasiswa mampu menggunakan ROLE dan VIEW untuk membatasi akses data.
4. Mahasiswa mampu melakukan backup dan restore basis data.

B. Alat dan Bahan

• Laragon (MySQL 8), SQLyog, terminal Laragon, db_perpustakaan

C. Dasar Teori

Keamanan basis data mencakup tiga hal: autentikasi (siapa Anda: user + password + host), otorisasi (apa yang boleh dilakukan: privilege), dan ketersediaan (data dapat dipulihkan: backup).

Prinsip least privilege: berikan hak seminimal mungkin yang cukup untuk menjalankan tugas. Akun root tidak boleh dipakai oleh aplikasi.

D. Langkah Praktikum

1. Buat user (login sebagai root):

   CREATE USER 'petugas_perpus'@'localhost' IDENTIFIED BY 'Petugas#2026';
   CREATE USER 'kepala_perpus'@'localhost'  IDENTIFIED BY 'Kepala#2026';
   CREATE USER 'app_perpus'@'localhost'     IDENTIFIED BY 'App#Perpus2026';
   SELECT user, host FROM mysql.user;

2. Berikan hak sesuai peran:

   -- petugas: kelola transaksi, hanya baca data master
   GRANT SELECT ON db_perpustakaan.* TO 'petugas_perpus'@'localhost';
   GRANT INSERT, UPDATE ON db_perpustakaan.peminjaman        TO 'petugas_perpus'@'localhost';
   GRANT INSERT, UPDATE ON db_perpustakaan.detail_peminjaman TO 'petugas_perpus'@'localhost';
   GRANT UPDATE (stok)  ON db_perpustakaan.buku              TO 'petugas_perpus'@'localhost';
   
   -- kepala: hanya membaca laporan
   GRANT SELECT ON db_perpustakaan.* TO 'kepala_perpus'@'localhost';
   
   -- aplikasi web: CRUD tanpa hak DDL
   GRANT SELECT, INSERT, UPDATE, DELETE ON db_perpustakaan.* TO 'app_perpus'@'localhost';
   
   SHOW GRANTS FOR 'petugas_perpus'@'localhost';

3. Uji hak akses. Buat koneksi baru di SQLyog dengan user petugas_perpus, lalu coba:

   SELECT * FROM db_perpustakaan.buku;                 -- berhasil
   UPDATE db_perpustakaan.buku SET stok = 10 WHERE id_buku = 1;   -- berhasil (kolom stok)
   UPDATE db_perpustakaan.buku SET judul = 'X' WHERE id_buku = 1; -- DITOLAK
   DELETE FROM db_perpustakaan.anggota WHERE id_anggota = 8;      -- DITOLAK
   DROP TABLE db_perpustakaan.kategori;                           -- DITOLAK

4. REVOKE dan ROLE (kembali sebagai root):

   REVOKE UPDATE (stok) ON db_perpustakaan.buku FROM 'petugas_perpus'@'localhost';
   
   CREATE ROLE 'r_laporan';
   GRANT SELECT ON db_perpustakaan.buku       TO 'r_laporan';
   GRANT SELECT ON db_perpustakaan.peminjaman TO 'r_laporan';
   CREATE USER 'staf_tu'@'localhost' IDENTIFIED BY 'StafTU#2026';
   GRANT 'r_laporan' TO 'staf_tu'@'localhost';
   SET DEFAULT ROLE 'r_laporan' TO 'staf_tu'@'localhost';

5. VIEW untuk menyembunyikan kolom sensitif:

   CREATE VIEW db_perpustakaan.v_anggota_publik AS
   SELECT nim, nama, prodi FROM db_perpustakaan.anggota;   -- tanpa email
   GRANT SELECT ON db_perpustakaan.v_anggota_publik TO 'staf_tu'@'localhost';

6. Backup & restore lewat terminal Laragon:

   mysqldump -u root -p db_perpustakaan > backup_perpus.sql
   mysql -u root -p -e "CREATE DATABASE db_perpus_restore"
   mysql -u root -p db_perpus_restore < backup_perpus.sql

7. Bersihkan (opsional, setelah tugas selesai): DROP USER 'staf_tu'@'localhost'; DROP ROLE 'r_laporan';

E. Tugas Praktik

F. Persiapan Ujian Tertulis 30% CPMK 039

Asesmen Sub-CPMK 039.3 berupa ujian tertulis. Materi yang diujikan mencakup Pertemuan 11–13. Latih diri Anda dengan kisi-kisi berikut:

1. Menentukan FD dan menormalisasi tabel sampai 3NF.
2. Memilih constraint yang tepat untuk aturan bisnis tertentu.
3. Menulis perintah CREATE USER, GRANT, REVOKE, CREATE ROLE untuk skenario tertentu.
4. Menganalisis risiko keamanan (hak berlebih, akun root di aplikasi, SQL injection, tidak ada backup).

G. Indikator

H. Refleksi

• Saya dapat membuat user dengan hak terbatas.
• Saya dapat melakukan backup dan restore database.

Menggunakan Indeks untuk Meningkatkan Performa Query

A. Tujuan Praktikum

1. Mahasiswa mampu menjelaskan cara kerja indeks B-Tree.
2. Mahasiswa mampu membuat indeks tunggal, komposit, dan unik.
3. Mahasiswa mampu membandingkan waktu eksekusi dan jumlah baris yang dipindai sebelum dan sesudah indeks.

B. Alat dan Bahan

• Laragon (MySQL 8), SQLyog, db_perpustakaan

C. Dasar Teori

Tanpa indeks, MySQL harus membaca seluruh baris tabel (full table scan) untuk menemukan data. Indeks B-Tree menyimpan nilai kolom dalam struktur pohon terurut sehingga pencarian cukup menelusuri beberapa level saja, mirip daftar indeks di belakang buku.

D. Langkah Praktikum

1. Buat tabel log berukuran besar (200.000 baris):

   USE db_perpustakaan;
   
   CREATE TABLE log_kunjungan (
     id_log      INT AUTO_INCREMENT PRIMARY KEY,
     id_anggota  INT NOT NULL,
     waktu       DATETIME NOT NULL,
     keperluan   VARCHAR(20) NOT NULL,
     ruang       VARCHAR(10) NOT NULL
   ) ENGINE=InnoDB;
   
   SET SESSION cte_max_recursion_depth = 250000;
   INSERT INTO log_kunjungan (id_anggota, waktu, keperluan, ruang)
   WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 200000)
   SELECT FLOOR(1 + RAND() * 5000),
          '2026-01-01' + INTERVAL FLOOR(RAND() * 300 * 24 * 60) MINUTE,
          ELT(FLOOR(1 + RAND() * 4), 'baca', 'pinjam', 'kembali', 'diskusi'),
          ELT(FLOOR(1 + RAND() * 3), 'R-101', 'R-102', 'R-201')
   FROM seq;
   
   SELECT COUNT(*) FROM log_kunjungan;
   ANALYZE TABLE log_kunjungan;

2. Ukur sebelum indeks. Jalankan masing-masing query 3 kali dan catat waktu eksekusinya (lihat di tab Messages SQLyog), serta kolom type, key, dan rows dari EXPLAIN:

   -- Q1
   SELECT * FROM log_kunjungan WHERE id_anggota = 1234;
   EXPLAIN SELECT * FROM log_kunjungan WHERE id_anggota = 1234;
   -- Q2
   SELECT COUNT(*) FROM log_kunjungan
   WHERE keperluan = 'pinjam' AND waktu BETWEEN '2026-03-01' AND '2026-03-31';
   EXPLAIN SELECT COUNT(*) FROM log_kunjungan
   WHERE keperluan = 'pinjam' AND waktu BETWEEN '2026-03-01' AND '2026-03-31';

3. Buat indeks:

   CREATE INDEX idx_log_anggota ON log_kunjungan (id_anggota);
   CREATE INDEX idx_log_keperluan_waktu ON log_kunjungan (keperluan, waktu);
   SHOW INDEX FROM log_kunjungan;

4. Ukur ulang Q1 dan Q2, lalu bandingkan type (ALL → ref/range), key, dan rows.
5. Uji kasus yang membuat indeks tidak terpakai:

   EXPLAIN SELECT * FROM log_kunjungan WHERE waktu >= '2026-03-01';           -- bukan prefiks kiri
   EXPLAIN SELECT * FROM log_kunjungan WHERE keperluan = 'baca' AND MONTH(waktu) = 3;  -- fungsi
   EXPLAIN SELECT * FROM log_kunjungan WHERE keperluan = 'baca'
           AND waktu >= '2026-03-01' AND waktu < '2026-04-01';                  -- versi yang benar

6. Indeks pada tabel utama yang sering dicari:

   CREATE INDEX idx_buku_judul ON buku (judul);
   CREATE INDEX idx_pinjam_status_tempo ON peminjaman (status, tgl_jatuh_tempo);
   -- catatan: InnoDB otomatis membuat indeks untuk kolom FK

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: Query teroptimasi dengan indeks, analisis performa dilakukan.

G. Refleksi

• Saya dapat membaca kolom type, key, dan rows dari EXPLAIN.
• Saya memahami trade-off indeks terhadap kecepatan INSERT.

Evaluasi Performa Query dengan EXPLAIN / ANALYZE

A. Tujuan Praktikum

1. Mahasiswa mampu membaca rencana eksekusi (execution plan) dari EXPLAIN.
2. Mahasiswa mampu mengukur waktu eksekusi aktual dengan EXPLAIN ANALYZE.
3. Mahasiswa mampu menulis ulang (rewrite) query lambat dan membuktikan peningkatannya.

B. Alat dan Bahan

• Laragon (MySQL 8.0.18+ untuk EXPLAIN ANALYZE; pada MariaDB gunakan ANALYZE SELECT ...), SQLyog, tabel log_kunjungan dari Pertemuan 14

C. Dasar Teori

EXPLAIN hanya menampilkan perkiraan. EXPLAIN ANALYZE benar-benar menjalankan query dan menampilkan waktu aktual (actual time) serta jumlah baris aktual tiap langkah.

D. Langkah Praktikum

1. Format EXPLAIN:

   EXPLAIN SELECT b.judul, k.nama_kategori
   FROM buku b JOIN kategori k ON k.id_kategori = b.id_kategori
   WHERE b.stok > 2;
   
   EXPLAIN FORMAT=TREE SELECT b.judul, k.nama_kategori
   FROM buku b JOIN kategori k ON k.id_kategori = b.id_kategori
   WHERE b.stok > 2;

2. Kasus 1 — Correlated subquery vs JOIN + GROUP BY:

   -- Versi A (lambat pada data besar)
   EXPLAIN ANALYZE
   SELECT a.id_anggota, a.nama,
          (SELECT COUNT(*) FROM log_kunjungan l WHERE l.id_anggota = a.id_anggota) AS kunjungan
   FROM anggota a;
   
   -- Versi B
   EXPLAIN ANALYZE
   SELECT a.id_anggota, a.nama, COUNT(l.id_log) AS kunjungan
   FROM anggota a
   LEFT JOIN log_kunjungan l ON l.id_anggota = a.id_anggota
   GROUP BY a.id_anggota, a.nama;

   Jalankan kedua versi sebelum dan sesudah idx_log_anggota ada (gunakan ALTER TABLE log_kunjungan DROP INDEX idx_log_anggota; lalu buat lagi).
3. Kasus 2 — Fungsi pada kolom (non-sargable):

   EXPLAIN ANALYZE SELECT COUNT(*) FROM log_kunjungan
   WHERE keperluan = 'pinjam' AND DATE(waktu) = '2026-05-10';
   
   EXPLAIN ANALYZE SELECT COUNT(*) FROM log_kunjungan
   WHERE keperluan = 'pinjam'
     AND waktu >= '2026-05-10' AND waktu < '2026-05-11';

4. Kasus 3 — Filesort dan covering index:

   EXPLAIN SELECT ruang, waktu FROM log_kunjungan
   WHERE keperluan = 'diskusi' ORDER BY waktu DESC LIMIT 20;
   
   CREATE INDEX idx_log_cover ON log_kunjungan (keperluan, waktu, ruang);
   EXPLAIN SELECT ruang, waktu FROM log_kunjungan
   WHERE keperluan = 'diskusi' ORDER BY waktu DESC LIMIT 20;   -- amati Extra: Using index

5. Kasus 4 — SELECT * vs kolom seperlunya pada laporan peminjaman, lalu bandingkan baris dan waktu.
6. Slow query log (opsional, sebagai root):

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 0.2;
   SHOW VARIABLES LIKE 'slow_query_log_file';

E. Tugas Project Dinilai

F. Indikator & Rubrik Penilaian

Indikator RPS: Query teroptimasi dengan indeks, analisis performa dilakukan.

G. Refleksi

• Saya dapat membedakan EXPLAIN dan EXPLAIN ANALYZE.
• Saya dapat membuktikan optimasi dengan angka, bukan perkiraan.

Proyek: Implementasi Basis Data pada Aplikasi Sederhana

A. Tujuan Praktikum

1. Mahasiswa mampu menghubungkan aplikasi web (PHP) ke basis data MySQL dengan user berhak terbatas.
2. Mahasiswa mampu mengimplementasikan operasi CRUD, transaksi, dan laporan berbasis query JOIN/agregasi.
3. Mahasiswa mampu menerapkan prepared statement untuk mencegah SQL injection.

B. Alat dan Bahan

• Laragon (Apache + PHP 8 + MySQL), editor kode (VS Code), web browser, SQLyog
• db_perpustakaan lengkap dengan constraint (P12), user app_perpus (P13), dan indeks (P14)

C. Gambaran Aplikasi

D. Langkah Praktikum

1. Buat folder proyek C:\laragon\www\perpus_NIM. Laragon otomatis menyediakan alamat http://perpus_NIM.test (atau http://localhost/perpus_NIM).
2. koneksi.php — gunakan user aplikasi, bukan root:

   <?php
   $dsn = 'mysql:host=127.0.0.1;dbname=db_perpustakaan;charset=utf8mb4';
   $pdo = new PDO($dsn, 'app_perpus', 'App#Perpus2026', [
       PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
       PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
   ]);
   function e($s) { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

3. index.php — daftar dan pencarian buku dengan prepared statement:

   <?php
   require 'koneksi.php';
   $q = $_GET['q'] ?? '';
   $stmt = $pdo->prepare(
     "SELECT b.id_buku, b.isbn, b.judul, b.stok, k.nama_kategori, p.nama_penerbit
        FROM buku b
        JOIN kategori k ON k.id_kategori = b.id_kategori
        JOIN penerbit p ON p.id_penerbit = b.id_penerbit
       WHERE b.judul LIKE ?
       ORDER BY b.judul"
   );
   $stmt->execute([$q . '%']);
   $buku = $stmt->fetchAll();
   ?>
   <h1>Katalog Buku</h1>
   <form><input name="q" value="<?= e($q) ?>" placeholder="Cari judul..."> <button>Cari</button></form>
   <a href="tambah_buku.php">+ Tambah buku</a> | <a href="pinjam.php">Peminjaman</a> | <a href="laporan.php">Laporan</a>
   <table border="1" cellpadding="6">
     <tr><th>ISBN</th><th>Judul</th><th>Kategori</th><th>Penerbit</th><th>Stok</th></tr>
     <?php foreach ($buku as $b): ?>
     <tr>
       <td><?= e($b['isbn']) ?></td><td><?= e($b['judul']) ?></td>
       <td><?= e($b['nama_kategori']) ?></td><td><?= e($b['nama_penerbit']) ?></td>
       <td><?= (int)$b['stok'] ?></td>
     </tr>
     <?php endforeach; ?>
   </table>

4. pinjam.php — inti transaksi (lengkapi form HTML-nya sendiri):

   <?php
   require 'koneksi.php';
   if ($_SERVER['REQUEST_METHOD'] === 'POST') {
       $idAnggota = (int)$_POST['id_anggota'];
       $idBuku    = (int)$_POST['id_buku'];
       try {
           $pdo->beginTransaction();
   
           $cek = $pdo->prepare('SELECT stok FROM buku WHERE id_buku = ? FOR UPDATE');
           $cek->execute([$idBuku]);
           if ((int)$cek->fetchColumn() < 1) {
               throw new RuntimeException('Stok buku habis.');
           }
   
           $pdo->prepare('INSERT INTO peminjaman (id_anggota, id_petugas, tgl_pinjam, tgl_jatuh_tempo)
                          VALUES (?, 1, CURDATE(), CURDATE() + INTERVAL 7 DAY)')
               ->execute([$idAnggota]);
           $idPinjam = $pdo->lastInsertId();
   
           $pdo->prepare('INSERT INTO detail_peminjaman (id_pinjam, id_buku) VALUES (?, ?)')
               ->execute([$idPinjam, $idBuku]);
           $pdo->prepare('UPDATE buku SET stok = stok - 1 WHERE id_buku = ?')
               ->execute([$idBuku]);
   
           $pdo->commit();
           echo 'Peminjaman berhasil disimpan.';
       } catch (Throwable $ex) {
           $pdo->rollBack();
           echo 'Gagal: ' . e($ex->getMessage());
       }
   }

   User app_perpus perlu hak SELECT ... FOR UPDATE (cukup SELECT + UPDATE pada tabel buku). Jika muncul error hak akses, periksa kembali GRANT dari Pertemuan 13.
5. laporan.php — tampilkan minimal dua laporan dari query Pertemuan 10 (buku terpopuler dan anggota terlambat). Gunakan VIEW agar kode PHP tetap ringkas.
6. Uji keamanan: ketik ' OR '1'='1 di kolom pencarian. Pastikan aplikasi tidak menampilkan error SQL atau data yang tidak semestinya.

E. Tugas Proyek Akhir Dinilai

Kerjakan secara individu atau kelompok (maks. 3 orang) menggunakan db_perpustakaan atau studi kasus kelompok dari Pertemuan 4–6.

F. Indikator & Rubrik Penilaian

Indikator RPS: Mampu membuat projek sederhana.

G. Refleksi Akhir Semester

• Saya dapat merancang basis data dari kebutuhan hingga implementasi.
• Saya dapat menulis query SQL dasar hingga lanjutan.
• Saya dapat menjaga integritas dan keamanan data.
• Saya dapat menganalisis dan mengoptimasi performa query.