-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `db_busana`
--

-- --------------------------------------------------------

--
-- Table structure for table `hasil_ujian_siswa`
--

CREATE TABLE `hasil_ujian_siswa` (
  `id_hasil` int(11) NOT NULL,
  `id_ujian` int(11) NOT NULL,
  `id_siswa` int(11) NOT NULL,
  `total_soal` int(11) DEFAULT NULL,
  `jawaban_benar` int(11) DEFAULT NULL,
  `nilai_akhir` decimal(5,2) DEFAULT NULL,
  `status_ujian` enum('lulus','tidak lulus','belum selesai') DEFAULT 'belum selesai',
  `waktu_selesai_ujian` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `hasil_ujian_siswa`
--

INSERT INTO `hasil_ujian_siswa` (`id_hasil`, `id_ujian`, `id_siswa`, `total_soal`, `jawaban_benar`, `nilai_akhir`, `status_ujian`, `waktu_selesai_ujian`) VALUES
(1, 1, 12, 2, 0, '0.00', 'tidak lulus', '2025-06-04 10:58:23');

-- --------------------------------------------------------

--
-- Table structure for table `jawaban_siswa`
--

CREATE TABLE `jawaban_siswa` (
  `id_jawaban_siswa` int(11) NOT NULL,
  `id_ujian` int(11) NOT NULL,
  `id_siswa` int(11) NOT NULL,
  `id_soal` int(11) NOT NULL,
  `jawaban_dipilih` enum('A','B','C','D') DEFAULT NULL,
  `is_correct` tinyint(1) DEFAULT NULL,
  `waktu_submit` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `jawaban_siswa`
--

INSERT INTO `jawaban_siswa` (`id_jawaban_siswa`, `id_ujian`, `id_siswa`, `id_soal`, `jawaban_dipilih`, `is_correct`, `waktu_submit`) VALUES
(1, 1, 12, 1, 'A', 0, '2025-06-04 10:58:23'),
(2, 1, 12, 2, 'B', 0, '2025-06-04 10:58:23');

-- --------------------------------------------------------

--
-- Table structure for table `nilai`
--

CREATE TABLE `nilai` (
  `id_nilai` int(11) NOT NULL,
  `id_siswa` int(11) NOT NULL,
  `mata_pelajaran` varchar(100) NOT NULL,
  `nilai_angka` decimal(5,2) NOT NULL,
  `nilai_huruf` varchar(5) NOT NULL,
  `semester` varchar(20) NOT NULL,
  `tahun_ajaran` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `nilai`
--

INSERT INTO `nilai` (`id_nilai`, `id_siswa`, `mata_pelajaran`, `nilai_angka`, `nilai_huruf`, `semester`, `tahun_ajaran`) VALUES
(2, 11, 'Matematika TKA', '90.00', 'B', 'Genap', '2025/2026'),
(3, 12, 'Bahasa Indonesia', '100.00', 'A+', 'Genap', '2025/2026');

-- --------------------------------------------------------

--
-- Table structure for table `soal_ujian`
--

CREATE TABLE `soal_ujian` (
  `id_soal` int(11) NOT NULL,
  `id_ujian` int(11) NOT NULL,
  `pertanyaan` text NOT NULL,
  `pilihan_a` varchar(255) NOT NULL,
  `pilihan_b` varchar(255) NOT NULL,
  `pilihan_c` varchar(255) NOT NULL,
  `pilihan_d` varchar(255) NOT NULL,
  `kunci_jawaban` enum('A','B','C','D') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `soal_ujian`
--

INSERT INTO `soal_ujian` (`id_soal`, `id_ujian`, `pertanyaan`, `pilihan_a`, `pilihan_b`, `pilihan_c`, `pilihan_d`, `kunci_jawaban`) VALUES
(1, 1, 'Seorang peneliti menyatakan:\\r\\n\\\"Jika sebuah sistem tidak memiliki redundansi, maka ia tidak tahan terhadap kegagalan. Semua sistem yang tahan terhadap kegagalan memiliki redundansi.\\\"\\r\\nApakah pernyataan tersebut membentuk argumen yang valid? Jelaskan dengan metode tabel kebenaran atau deduksi formal.', 'Tidak valid, karena mengandung kontradiksi', 'Valid, karena berbentuk silogisme hipotetik', 'Tidak valid, karena menggunakan generalisasi terburu-buru', 'Valid, karena bentuknya adalah modus tollens', 'B'),
(2, 1, '\\\"Setiap mahasiswa yang mengerjakan tugas tepat waktu dan mengikuti kuliah secara rutin pasti akan lulus mata kuliah Logika.\\\"\\r\\nBentuk logika formal dari pernyataan di atas adalah:', '∀x [(Tugas(x) ∧ Rutin(x)) → Lulus(x)]', '∃x [(Tugas(x) ∨ Rutin(x)) → Lulus(x)]', '∀x [Lulus(x) → (Tugas(x) ∧ Rutin(x))]', '∃x [¬Tugas(x) ∧ ¬Rutin(x) ∧ Lulus(x)]', 'A');

-- --------------------------------------------------------

--
-- Table structure for table `ujian`
--

CREATE TABLE `ujian` (
  `id_ujian` int(11) NOT NULL,
  `judul_ujian` varchar(255) NOT NULL,
  `deskripsi_ujian` text DEFAULT NULL,
  `waktu_mulai` datetime DEFAULT NULL,
  `waktu_selesai` datetime DEFAULT NULL,
  `durasi_menit` int(11) DEFAULT 0,
  `status_ujian` enum('draft','aktif','selesai') DEFAULT 'draft',
  `dibuat_oleh` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `ujian`
--

INSERT INTO `ujian` (`id_ujian`, `judul_ujian`, `deskripsi_ujian`, `waktu_mulai`, `waktu_selesai`, `durasi_menit`, `status_ujian`, `dibuat_oleh`) VALUES
(1, 'Matematika', 'logika matematika', NULL, NULL, 20, 'aktif', 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `hasil_ujian_siswa`
--
ALTER TABLE `hasil_ujian_siswa`
  ADD PRIMARY KEY (`id_hasil`),
  ADD UNIQUE KEY `id_ujian` (`id_ujian`,`id_siswa`),
  ADD KEY `fk_hasil_siswa` (`id_siswa`);

--
-- Indexes for table `jawaban_siswa`
--
ALTER TABLE `jawaban_siswa`
  ADD PRIMARY KEY (`id_jawaban_siswa`),
  ADD KEY `fk_jawaban_ujian` (`id_ujian`),
  ADD KEY `fk_jawaban_siswa` (`id_siswa`),
  ADD KEY `fk_jawaban_soal` (`id_soal`);

--
-- Indexes for table `nilai`
--
ALTER TABLE `nilai`
  ADD PRIMARY KEY (`id_nilai`),
  ADD KEY `fk_nilai_siswa` (`id_siswa`);

--
-- Indexes for table `soal_ujian`
--
ALTER TABLE `soal_ujian`
  ADD PRIMARY KEY (`id_soal`),
  ADD KEY `fk_soal_ujian` (`id_ujian`);

--
-- Indexes for table `ujian`
--
ALTER TABLE `ujian`
  ADD PRIMARY KEY (`id_ujian`),
  ADD KEY `fk_ujian_guru` (`dibuat_oleh`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `hasil_ujian_siswa`
--
ALTER TABLE `hasil_ujian_siswa`
  MODIFY `id_hasil` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `jawaban_siswa`
--
ALTER TABLE `jawaban_siswa`
  MODIFY `id_jawaban_siswa` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `nilai`
--
ALTER TABLE `nilai`
  MODIFY `id_nilai` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `soal_ujian`
--
ALTER TABLE `soal_ujian`
  MODIFY `id_soal` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `ujian`
--
ALTER TABLE `ujian`
  MODIFY `id_ujian` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `hasil_ujian_siswa`
--
ALTER TABLE `hasil_ujian_siswa`
  ADD CONSTRAINT `fk_hasil_siswa` FOREIGN KEY (`id_siswa`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hasil_ujian` FOREIGN KEY (`id_ujian`) REFERENCES `ujian` (`id_ujian`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `jawaban_siswa`
--
ALTER TABLE `jawaban_siswa`
  ADD CONSTRAINT `fk_jawaban_siswa` FOREIGN KEY (`id_siswa`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_jawaban_soal` FOREIGN KEY (`id_soal`) REFERENCES `soal_ujian` (`id_soal`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_jawaban_ujian` FOREIGN KEY (`id_ujian`) REFERENCES `ujian` (`id_ujian`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `nilai`
--
ALTER TABLE `nilai`
  ADD CONSTRAINT `fk_nilai_siswa` FOREIGN KEY (`id_siswa`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `soal_ujian`
--
ALTER TABLE `soal_ujian`
  ADD CONSTRAINT `fk_soal_ujian` FOREIGN KEY (`id_ujian`) REFERENCES `ujian` (`id_ujian`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `ujian`
--
ALTER TABLE `ujian`
  ADD CONSTRAINT `fk_ujian_guru` FOREIGN KEY (`dibuat_oleh`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;

