text: -- phpMyAdmin SQL Dump
  -- version 4.7.4
  -- https://www.phpmyadmin.net/
  --
  -- Host: 127.0.0.1
  -- Generation Time: May 13, 2019 at 04:34 PM
  -- Server version: 10.1.29-MariaDB
  -- PHP Version: 7.1.12
  
  SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  SET AUTOCOMMIT = 0;
  START TRANSACTION;
  SET time_zone = "+00:00";
  
  
  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  /*!40101 SET NAMES utf8mb4 */;
  
  --
  -- Database: `perpustakaan_sekolah`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `buku`
  --
  
  CREATE TABLE `buku` (
    `kode_buku` varchar(11) NOT NULL,
    `nama_buku` varchar(55) NOT NULL,
    `kode_jb` varchar(2) NOT NULL,
    `kode_kb` varchar(2) NOT NULL,
    `jumlah_buku` int(11) NOT NULL,
    `buku_terpinjam` int(11) NOT NULL,
    `buku_tersedia` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `buku`
  --
  
  INSERT INTO `buku` (`kode_buku`, `nama_buku`, `kode_jb`, `kode_kb`, `jumlah_buku`, `buku_terpinjam`, `buku_tersedia`) VALUES
  ('20190330001', 'Agama Islam', '01', '01', 12, 0, 0),
  ('20190511001', 'end_game', 'n1', '01', 1, 0, 1);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `buku_masuk`
  --
  
  CREATE TABLE `buku_masuk` (
    `id_bm` int(11) NOT NULL,
    `kode_buku` varchar(11) NOT NULL,
    `nip` bigint(11) NOT NULL,
    `tanggal` date NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `buku_masuk`
  --
  
  INSERT INTO `buku_masuk` (`id_bm`, `kode_buku`, `nip`, `tanggal`) VALUES
  (1, '20190330001', 20190329001, '2019-03-30'),
  (2, '20190511001', 20190329001, '2019-05-11');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `jabatan_petugas`
  --
  
  CREATE TABLE `jabatan_petugas` (
    `id_jabatan` int(11) NOT NULL,
    `nama_jabatan` varchar(25) NOT NULL,
    `deskripsi` varchar(125) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `jabatan_petugas`
  --
  
  INSERT INTO `jabatan_petugas` (`id_jabatan`, `nama_jabatan`, `deskripsi`) VALUES
  (1, 'admin', 'administrator'),
  (2, 'ketua', 'ceo perpustakaan'),
  (3, 'staff', 'karyawan perpustakaan');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `jenis_buku`
  --
  
  CREATE TABLE `jenis_buku` (
    `kode_jb` varchar(2) NOT NULL,
    `jenis_buku` varchar(55) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `jenis_buku`
  --
  
  INSERT INTO `jenis_buku` (`kode_jb`, `jenis_buku`) VALUES
  ('01', 'pelajaran wajib'),
  ('n1', 'novel');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `kartu_anggota`
  --
  
  CREATE TABLE `kartu_anggota` (
    `id_kta` int(11) NOT NULL,
    `nisn` bigint(13) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='id_pinjam_buku = id_peminjaman_buku';
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `kelas_buku`
  --
  
  CREATE TABLE `kelas_buku` (
    `kode_kb` varchar(2) NOT NULL,
    `jurusan_siswa` varchar(55) NOT NULL,
    `kelas_siswa` int(2) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `kelas_buku`
  --
  
  INSERT INTO `kelas_buku` (`kode_kb`, `jurusan_siswa`, `kelas_siswa`) VALUES
  ('01', 'umum', 12),
  ('k3', 'Akuntansi', 12),
  ('t3', 'Otomotif', 12);
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `peminjaman_buku`
  --
  
  CREATE TABLE `peminjaman_buku` (
    `id_pem` int(11) NOT NULL,
    `id_kta` int(11) NOT NULL,
    `nip` bigint(11) NOT NULL,
    `jumlah` int(11) NOT NULL,
    `tanggal` date NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Triggers `peminjaman_buku`
  --
  DELIMITER $$
  CREATE TRIGGER `balikan_buku` BEFORE DELETE ON `peminjaman_buku` FOR EACH ROW UPDATE buku SET buku_tersedia=buku_tersedia+1, buku_terpinjam=buku_terpinjam-1 WHERE buku.kode_buku = OLD.kode_buku
  $$
  DELIMITER ;
  DELIMITER $$
  CREATE TRIGGER `pinjam_buku` AFTER INSERT ON `peminjaman_buku` FOR EACH ROW UPDATE buku SET buku_tersedia=buku_tersedia-1, buku_terpinjam=buku_terpinjam+1 WHERE buku.kode_buku = NEW.kode_buku
  $$
  DELIMITER ;
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `peminjaman_buku_detail`
  --
  
  CREATE TABLE `peminjaman_buku_detail` (
    `id_pem_detail` int(11) NOT NULL,
    `id_pem` int(11) NOT NULL,
    `kode_buku` varchar(11) NOT NULL,
    `status` enum('pinjam','kembali','hilang') NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `pengembalian_buku`
  --
  
  CREATE TABLE `pengembalian_buku` (
    `id_peng` int(11) NOT NULL,
    `id_pem` int(11) NOT NULL,
    `jumlah` int(11) NOT NULL,
    `tanggal` date NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `petugas`
  --
  
  CREATE TABLE `petugas` (
    `nip` bigint(11) NOT NULL,
    `nama_petugas` varchar(55) NOT NULL,
    `password` varchar(255) NOT NULL,
    `alamat` varchar(125) NOT NULL,
    `tanggal_lahir` date NOT NULL,
    `jenis_kelamin` enum('Laki-Laki','Perempuan') NOT NULL,
    `jabatan_petugas` int(11) NOT NULL,
    `tanggal_menjabat` date NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `petugas`
  --
  
  INSERT INTO `petugas` (`nip`, `nama_petugas`, `password`, `alamat`, `tanggal_lahir`, `jenis_kelamin`, `jabatan_petugas`, `tanggal_menjabat`) VALUES
  (20190110001, 'seto', '940f1c5e43190a5a3ea4bbff44474f6398f7458039a52759d86b57da874e15a3', 'jamuju9', '2010-10-10', 'Laki-Laki', 3, '2019-01-10'),
  (20190329001, 'Rafif', '25bec99882f593b2a8de6a20e49236e520771deb98092f14b2a595b2ffe23f0c', 'jamuju7', '2001-03-10', 'Laki-Laki', 1, '2019-03-29'),
  (20190329002, 'Anggi', 'b85fd3b2f0b4ed774d0fa8f00f1c79b3acd7c51c8e4f8588c94dda63d9a06de0', 'jamuju5', '2001-03-10', 'Perempuan', 2, '2019-03-29');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `siswa`
  --
  
  CREATE TABLE `siswa` (
    `nisn` bigint(13) NOT NULL,
    `nama_siswa` int(11) NOT NULL,
    `jurusan_siswa` varchar(55) NOT NULL,
    `kelas_siswa` varchar(25) NOT NULL,
    `email` varchar(125) NOT NULL,
    `no_hp` varchar(16) NOT NULL,
    `alamat` varchar(255) NOT NULL,
    `asal_sekolah` varchar(55) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Indexes for dumped tables
  --
  
  --
  -- Indexes for table `buku`
  --
  ALTER TABLE `buku`
    ADD PRIMARY KEY (`kode_buku`),
    ADD KEY `kode_jb` (`kode_jb`),
    ADD KEY `kode_kb` (`kode_kb`);
  
  --
  -- Indexes for table `buku_masuk`
  --
  ALTER TABLE `buku_masuk`
    ADD PRIMARY KEY (`id_bm`),
    ADD KEY `kode_buku` (`kode_buku`),
    ADD KEY `nip` (`nip`);
  
  --
  -- Indexes for table `jabatan_petugas`
  --
  ALTER TABLE `jabatan_petugas`
    ADD PRIMARY KEY (`id_jabatan`);
  
  --
  -- Indexes for table `jenis_buku`
  --
  ALTER TABLE `jenis_buku`
    ADD PRIMARY KEY (`kode_jb`);
  
  --
  -- Indexes for table `kartu_anggota`
  --
  ALTER TABLE `kartu_anggota`
    ADD PRIMARY KEY (`id_kta`),
    ADD KEY `nisn` (`nisn`);
  
  --
  -- Indexes for table `kelas_buku`
  --
  ALTER TABLE `kelas_buku`
    ADD PRIMARY KEY (`kode_kb`);
  
  --
  -- Indexes for table `peminjaman_buku`
  --
  ALTER TABLE `peminjaman_buku`
    ADD PRIMARY KEY (`id_pem`),
    ADD KEY `id_kta` (`id_kta`),
    ADD KEY `nip` (`nip`);
  
  --
  -- Indexes for table `peminjaman_buku_detail`
  --
  ALTER TABLE `peminjaman_buku_detail`
    ADD PRIMARY KEY (`id_pem_detail`),
    ADD KEY `kode_buku` (`kode_buku`),
    ADD KEY `id_pem` (`id_pem`);
  
  --
  -- Indexes for table `pengembalian_buku`
  --
  ALTER TABLE `pengembalian_buku`
    ADD PRIMARY KEY (`id_peng`),
    ADD KEY `id_kta` (`id_pem`);
  
  --
  -- Indexes for table `petugas`
  --
  ALTER TABLE `petugas`
    ADD PRIMARY KEY (`nip`),
    ADD KEY `jabatan_petugas` (`jabatan_petugas`);
  
  --
  -- Indexes for table `siswa`
  --
  ALTER TABLE `siswa`
    ADD PRIMARY KEY (`nisn`);
  
  --
  -- AUTO_INCREMENT for dumped tables
  --
  
  --
  -- AUTO_INCREMENT for table `buku_masuk`
  --
  ALTER TABLE `buku_masuk`
    MODIFY `id_bm` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
  
  --
  -- AUTO_INCREMENT for table `jabatan_petugas`
  --
  ALTER TABLE `jabatan_petugas`
    MODIFY `id_jabatan` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
  
  --
  -- AUTO_INCREMENT for table `kartu_anggota`
  --
  ALTER TABLE `kartu_anggota`
    MODIFY `id_kta` int(11) NOT NULL AUTO_INCREMENT;
  
  --
  -- AUTO_INCREMENT for table `peminjaman_buku`
  --
  ALTER TABLE `peminjaman_buku`
    MODIFY `id_pem` int(11) NOT NULL AUTO_INCREMENT;
  
  --
  -- AUTO_INCREMENT for table `peminjaman_buku_detail`
  --
  ALTER TABLE `peminjaman_buku_detail`
    MODIFY `id_pem_detail` int(11) NOT NULL AUTO_INCREMENT;
  
  --
  -- AUTO_INCREMENT for table `pengembalian_buku`
  --
  ALTER TABLE `pengembalian_buku`
    MODIFY `id_peng` int(11) NOT NULL AUTO_INCREMENT;
  
  --
  -- Constraints for dumped tables
  --
  
  --
  -- Constraints for table `buku`
  --
  ALTER TABLE `buku`
    ADD CONSTRAINT `buku_ibfk_1` FOREIGN KEY (`kode_kb`) REFERENCES `kelas_buku` (`kode_kb`),
    ADD CONSTRAINT `buku_ibfk_2` FOREIGN KEY (`kode_jb`) REFERENCES `jenis_buku` (`kode_jb`);
  
  --
  -- Constraints for table `buku_masuk`
  --
  ALTER TABLE `buku_masuk`
    ADD CONSTRAINT `buku_masuk_ibfk_2` FOREIGN KEY (`kode_buku`) REFERENCES `buku` (`kode_buku`),
    ADD CONSTRAINT `buku_masuk_ibfk_3` FOREIGN KEY (`nip`) REFERENCES `petugas` (`nip`);
  
  --
  -- Constraints for table `kartu_anggota`
  --
  ALTER TABLE `kartu_anggota`
    ADD CONSTRAINT `kartu_anggota_ibfk_1` FOREIGN KEY (`nisn`) REFERENCES `siswa` (`nisn`);
  
  --
  -- Constraints for table `peminjaman_buku`
  --
  ALTER TABLE `peminjaman_buku`
    ADD CONSTRAINT `peminjaman_buku_ibfk_1` FOREIGN KEY (`id_kta`) REFERENCES `kartu_anggota` (`id_kta`),
    ADD CONSTRAINT `peminjaman_buku_ibfk_2` FOREIGN KEY (`nip`) REFERENCES `petugas` (`nip`);
  
  --
  -- Constraints for table `peminjaman_buku_detail`
  --
  ALTER TABLE `peminjaman_buku_detail`
    ADD CONSTRAINT `peminjaman_buku_detail_ibfk_1` FOREIGN KEY (`kode_buku`) REFERENCES `buku` (`kode_buku`),
    ADD CONSTRAINT `peminjaman_buku_detail_ibfk_2` FOREIGN KEY (`id_pem`) REFERENCES `peminjaman_buku` (`id_pem`);
  
  --
  -- Constraints for table `pengembalian_buku`
  --
  ALTER TABLE `pengembalian_buku`
    ADD CONSTRAINT `pengembalian_buku_ibfk_1` FOREIGN KEY (`id_pem`) REFERENCES `peminjaman_buku` (`id_pem`);
  
  --
  -- Constraints for table `petugas`
  --
  ALTER TABLE `petugas`
    ADD CONSTRAINT `petugas_ibfk_1` FOREIGN KEY (`jabatan_petugas`) REFERENCES `jabatan_petugas` (`id_jabatan`);
  COMMIT;
  
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  