📄 LKPD 4 — Bahasa SQL (DDL & DML)

-- Buat database CREATE DATABASE IF NOT EXISTS presensi_rpl; USE presensi_rpl; -- Tabel Kelas CREATE TABLE kelas ( id_kelas INT(11) PRIMARY KEY AUTO_INCREMENT, nama_kelas VARCHAR(50) NOT NULL UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- Tabel Siswa CREATE TABLE siswa ( id_siswa INT(11) PRIMARY KEY AUTO_INCREMENT, nis VARCHAR(20) NOT NULL UNIQUE, nama_siswa VARCHAR(100) NOT NULL, id_kelas INT(11) NOT NULL, jenis_kelamin ENUM('L','P'), alamat TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (id_kelas) REFERENCES kelas(id_kelas) ON DELETE CASCADE ); -- Tabel Guru CREATE TABLE guru ( id_guru INT(11) PRIMARY KEY AUTO_INCREMENT, nip VARCHAR(20) UNIQUE, nama_guru VARCHAR(100) NOT NULL, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- Tabel Presensi CREATE TABLE presensi ( id_presensi INT(11) PRIMARY KEY AUTO_INCREMENT, id_siswa INT(11) NOT NULL, id_guru INT(11) NOT NULL, tanggal DATE NOT NULL, status ENUM('hadir','sakit','izin','alpa') NOT NULL DEFAULT 'hadir', keterangan TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (id_siswa) REFERENCES siswa(id_siswa) ON DELETE CASCADE, FOREIGN KEY (id_guru) REFERENCES guru(id_guru) ON DELETE CASCADE, UNIQUE KEY unik_presensi (id_siswa, tanggal) ); -- Isi data kelas awal INSERT INTO kelas (nama_kelas) VALUES ('XI RPL 1'), ('XI RPL 2');