/*
SQLyog Ultimate v12.4.3 (64 bit)
MySQL - 10.4.28-MariaDB : Database - dbperpustakaan
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`dbperpustakaan` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

USE `dbperpustakaan`;

/*Table structure for table `anggota` */

DROP TABLE IF EXISTS `anggota`;

CREATE TABLE `anggota` (
  `idAnggota` char(10) NOT NULL,
  `namaAnggota` varchar(100) NOT NULL,
  `npm` char(15) NOT NULL,
  `alamatAnggota` varchar(100) NOT NULL,
  `noTelepon` varchar(20) NOT NULL,
  `idJurusan` varchar(10) NOT NULL,
  PRIMARY KEY (`idAnggota`),
  KEY `idJurusan` (`idJurusan`),
  CONSTRAINT `anggota_ibfk_2` FOREIGN KEY (`idJurusan`) REFERENCES `jurusan` (`idJurusan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `anggota` */

insert  into `anggota`(`idAnggota`,`namaAnggota`,`npm`,`alamatAnggota`,`noTelepon`,`idJurusan`) values 
('A001','ayu','2009304','Jl Bendul Merisi 18','1637383939','J001'),
('A002','ahmad  ','2009305','Jl. Beliton 10A ','0012229919','J001'),
('A003','rizal ','2003001','Jl. Wedari 56','081238399','J001'),
('A004','anton','2002001','Jl. Tidar 12','08112748891','J002'),
('A005','bella ','2001001','Jl. Gatotkaca 20','0856789000','J002'),
('A006','damar','2005002','Jl Bali 12','52489412010','J003'),
('A007','lili','2004002','Jl Semeru 12','12149217','J003'),
('A008','tama','2003002','Jl Semeru 17','173478298497','J003'),
('A009','dadang','2002002','Jl Bromo 16','7393949217','J004'),
('A010','tika','2001002','Jl Toba 17','0844782991','J005'),
('A011','dedi','2005003','Jl Wiguna Tengah 16','0824773838','J006'),
('A012','agung','2004003','Jl Toba 17','0844782991','J007'),
('A013','adhit','2003003','Jl Wiguna Tengah 16','0824773838','J008'),
('A014','tiara','2002003','Jalan Tidar','133712301','J009'),
('A015','cika nana','20010035','Jl Wiguna Selatan 17','128321939129','J010'),
('A016','kinan','2005004','Jl Kerinci 16','12678917','J011'),
('A017','lun','2004003','Jl Tidar 17','1789949217','J012'),
('A018','cika ','2003002','Jl Wiguna Selatan 17','128321939129','J013'),
('A019','tami','2002001','Jl Bendul Merisi 16','08137373736','J014'),
('A020','tina ','2002012','Jl Halmahera 20','081920202011','J015'),
('A021','yohanes','20011339','Jl Wiguna Selatan','4467899','J015'),
('A022','tono','20011782','Jl Bali','1234567890','J005'),
('A023','tini','20011790','Jl Jawa','1234567891','J001'),
('A024','panji','20011791','Jl Kalimantan ','1234567892','J011'),
('A025','Tara ','20001678','Jl. Kali Baru ','1332563738','J012');

/*Table structure for table `buku` */

DROP TABLE IF EXISTS `buku`;

CREATE TABLE `buku` (
  `idBuku` char(10) NOT NULL,
  `judulBuku` varchar(100) NOT NULL,
  `tahunTerbit` varchar(20) NOT NULL,
  `jumlahBuku` int(100) DEFAULT NULL,
  `idPenerbit` char(10) NOT NULL,
  `idKategori` char(10) NOT NULL,
  PRIMARY KEY (`idBuku`),
  KEY `idPenerbit` (`idPenerbit`),
  KEY `idKategori` (`idKategori`),
  CONSTRAINT `buku_ibfk_2` FOREIGN KEY (`idPenerbit`) REFERENCES `penerbit` (`idPenerbit`),
  CONSTRAINT `buku_ibfk_3` FOREIGN KEY (`idKategori`) REFERENCES `kategoribuku` (`idKategori`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `buku` */

insert  into `buku`(`idBuku`,`judulBuku`,`tahunTerbit`,`jumlahBuku`,`idPenerbit`,`idKategori`) values 
('B001','Basis Data ','2015',15,'Pr001','K001'),
('B002','Pemrograman Lanjut','2019',31,'Pr002','K001'),
('B003','Basis Data Lanjut ','2021',70,'Pr003','K001'),
('B004','Sistem Informasi','2021',15,'Pr004','K002'),
('B005','Teknologi Informasi ','2019',25,'Pr005','K015'),
('B006','Fisdas','2011',17,'Pr006','K014'),
('B007','Aljabar Linier','2016',15,'Pr007','K013'),
('B008','Fisdas','2011',16,'Pr008','K012'),
('B009','Pemrograman Terstruktur','2020',168,'Pr009','K011'),
('B010','Pemrograman Game ','2017',79,'Pr010','K010'),
('B011','Fisdas','2011',64,'Pr002','K009'),
('B012','Fisdas 1','2011',110,'Pr003','K008'),
('B013','Fisdas 2','2011',135,'Pr004','K007'),
('B014','Fisdas 3','2011',45,'Pr011','K006'),
('B015','Fisdas 4','2011',95,'Pr012','K005'),
('B016','Fisdas 5','2011',90,'Pr013','K004'),
('B017','Biologi','2017',56,'Pr014','K003'),
('B018','Teknik','2017',110,'Pr015','K002'),
('B019','Basis Data Lanjut','2021',120,'Pr001','K001'),
('B020','Algoritma 1','2025',16,'Pr001','K001'),
('B021','Algoritma 2','2025',20,'Pr001','K001'),
('B022','Algoritma Lanjut','2025',21,'Pr001','K001'),
('B023','Radiologi','2025',23,'Pr001','K001');

/*Table structure for table `jurusan` */

DROP TABLE IF EXISTS `jurusan`;

CREATE TABLE `jurusan` (
  `idJurusan` char(10) NOT NULL,
  `namaJurusan` varchar(100) NOT NULL,
  PRIMARY KEY (`idJurusan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `jurusan` */

insert  into `jurusan`(`idJurusan`,`namaJurusan`) values 
('J001','Informatika '),
('J002','Ilmu Komunikasi '),
('J003','Sains Data '),
('J004','Sistem Informasi '),
('J005','DKV'),
('J006','Akuntansi'),
('J007','Ekonomi Pembangunan'),
('J008','Filsafat'),
('J009','Teknik Kelautan'),
('J010','Teknik Geodesi'),
('J011','Teknik Perkapalan'),
('J012','Teknik Fisika'),
('J013','Teknik Lingkungan'),
('J014','Perikanan'),
('J015','Kedokteran'),
('J016','Pertanian'),
('J017','Arsitektur');

/*Table structure for table `kategoribuku` */

DROP TABLE IF EXISTS `kategoribuku`;

CREATE TABLE `kategoribuku` (
  `idKategori` char(10) NOT NULL,
  `kategori` varchar(100) NOT NULL,
  PRIMARY KEY (`idKategori`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `kategoribuku` */

insert  into `kategoribuku`(`idKategori`,`kategori`) values 
('K001','Komputer'),
('K002','Komunikasi'),
('K003','Pertanian'),
('K004','Peternakan'),
('K005','Sejarah'),
('K006','Bahasa'),
('K007','Agama'),
('K008','Sains '),
('K009','Kedokteran '),
('K010','Seni'),
('K011','Biologi'),
('K012','Biologi'),
('K013','Fisika'),
('K014','Antropologi'),
('K015','Art');

/*Table structure for table `pegawai` */

DROP TABLE IF EXISTS `pegawai`;

CREATE TABLE `pegawai` (
  `idPegawai` char(10) NOT NULL,
  `namaPegawai` varchar(50) NOT NULL,
  `alamatPegawai` varchar(50) NOT NULL,
  PRIMARY KEY (`idPegawai`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `pegawai` */

insert  into `pegawai`(`idPegawai`,`namaPegawai`,`alamatPegawai`) values 
('Peg001','Rara Kusuma ','Jl Prajuritan 12'),
('Peg002','Kamila Ramadhan ','Jl Ontorejo 21'),
('Peg003','Amarudin ','Jl Wiguna Tengah 20'),
('Peg004','Kasanul Hakim ','Jl Tidar 40'),
('Peg005','Amarinta Kaili','Jl Semeru 20 '),
('Peg006','Laila Hasan ','Jl Bromo 28'),
('Peg007','Nailul Latif ','Jl Merapi 27');

/*Table structure for table `peminjam` */

DROP TABLE IF EXISTS `peminjam`;

CREATE TABLE `peminjam` (
  `idPeminjam` char(10) NOT NULL,
  `tglPinjam` date NOT NULL,
  `tglKembali` datetime NOT NULL,
  `denda` int(11) NOT NULL,
  `idBuku` char(6) NOT NULL,
  `jumlahPinjam` int(2) NOT NULL,
  `idAnggota` char(10) NOT NULL,
  PRIMARY KEY (`idPeminjam`),
  KEY `idBuku` (`idBuku`),
  KEY `idAnggota` (`idAnggota`),
  CONSTRAINT `peminjam_ibfk_1` FOREIGN KEY (`idBuku`) REFERENCES `buku` (`idBuku`),
  CONSTRAINT `peminjam_ibfk_2` FOREIGN KEY (`idAnggota`) REFERENCES `anggota` (`idAnggota`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `peminjam` */

insert  into `peminjam`(`idPeminjam`,`tglPinjam`,`tglKembali`,`denda`,`idBuku`,`jumlahPinjam`,`idAnggota`) values 
('Pm001','2022-12-22','2023-01-12 09:46:22',20000,'B001',1,'A001'),
('Pm002','2022-12-11','2022-12-18 09:50:12',0,'B005',2,'A002'),
('Pm003','2022-10-18','2022-11-17 05:00:33',20000,'B004',1,'A003'),
('Pm004','2022-10-17','2022-10-18 05:01:04',0,'B003',1,'A004'),
('Pm005','2022-10-17','2022-10-24 14:46:45',0,'B002',1,'A005'),
('Pm006','2022-10-16','2022-11-12 00:00:00',20000,'B010',3,'A006'),
('Pm007','2022-10-27','2022-10-24 05:01:23',0,'B007',1,'A007'),
('Pm008','2022-12-09','2022-02-01 00:00:00',0,'B008',1,'A008'),
('Pm009','2022-12-13','2022-12-21 16:50:33',0,'B001',3,'A009'),
('Pm010','2022-12-21','2022-12-31 14:26:04',0,'B019',5,'A010'),
('Pm011','2022-09-13','2022-09-26 00:00:00',0,'B009',1,'A011'),
('Pm012','2022-12-05','2022-12-12 09:50:19',0,'B006',1,'A012'),
('Pm013','2022-12-07','2022-12-14 09:49:56',0,'B011',2,'A013'),
('Pm014','2022-12-07','2022-12-14 09:48:27',0,'B012',3,'A014'),
('Pm015','2022-12-21','2022-12-28 09:48:38',0,'B013',1,'A015'),
('Pm016','2022-12-08','2022-06-01 06:00:00',0,'B015',1,'A016'),
('Pm017','2022-12-14','2022-12-28 16:47:39',0,'B001',3,'A017'),
('Pm018','2022-12-18','2022-12-25 09:48:48',0,'B014',1,'A018'),
('Pm019','2022-11-28','2023-01-18 09:49:04',15000,'B016',4,'A019'),
('Pm020','2022-12-05','2022-12-12 09:50:04',0,'B017',1,'A020'),
('Pm021','2025-09-09','2025-10-22 08:28:28',0,'B004',5,'A001'),
('Pm022','2025-09-22','2025-10-17 08:29:28',0,'B006',5,'A013');

/*Table structure for table `penerbit` */

DROP TABLE IF EXISTS `penerbit`;

CREATE TABLE `penerbit` (
  `idPenerbit` char(10) NOT NULL,
  `namaPenerbit` varchar(20) NOT NULL,
  `lokasiPenerbit` varchar(20) NOT NULL,
  PRIMARY KEY (`idPenerbit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `penerbit` */

insert  into `penerbit`(`idPenerbit`,`namaPenerbit`,`lokasiPenerbit`) values 
('Pr001','Erlangga','Jakarta '),
('Pr002','Gramedia Pustaka','Surabaya'),
('Pr003','Bukunesia ','Surabaya '),
('Pr004','Inari','Jakarta '),
('Pr005','Cemara','Jakarta'),
('Pr006','Penerbit Andi ','Surabaya'),
('Pr007','Infomedia ','Jakarta'),
('Pr008','Erlangga','Medan'),
('Pr009','Republika ','Surabaya '),
('Pr010','Penerbit Grasindo','Medan '),
('Pr011','Buku Pustaka','Surabaya '),
('Pr012','Gramindo','Surabaya '),
('Pr013','Media Kita','Solo'),
('Pr014','Bintang Media','Solo'),
('Pr015','Erlangga ','Jakara ');

/*Table structure for table `suplaibuku` */

DROP TABLE IF EXISTS `suplaibuku`;

CREATE TABLE `suplaibuku` (
  `idSuplai` char(5) NOT NULL,
  `idBuku` char(10) NOT NULL,
  `tanggal` datetime NOT NULL,
  `jumlah` int(10) NOT NULL,
  PRIMARY KEY (`idSuplai`),
  KEY `idBuku` (`idBuku`),
  CONSTRAINT `suplaibuku_ibfk_1` FOREIGN KEY (`idBuku`) REFERENCES `buku` (`idBuku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/*Data for the table `suplaibuku` */

insert  into `suplaibuku`(`idSuplai`,`idBuku`,`tanggal`,`jumlah`) values 
('S001','B001','2022-12-06 11:49:05',15),
('S002','B002','2022-12-06 11:49:23',17),
('S003','B003','2022-12-21 11:50:04',20),
('S004','B004','2022-12-07 11:54:09',25),
('S005','B005','2022-12-01 12:04:13',30),
('S006','B006','2022-12-29 12:44:46',25),
('S007','B007','2022-12-30 16:24:00',60),
('S008','B008','2022-12-28 16:25:28',12),
('S009','B009','2022-12-18 03:53:18',90),
('S010','B010','2022-12-26 03:53:51',50),
('S011','B011','2022-12-18 03:54:09',40),
('S012','B012','2022-12-16 03:54:30',60),
('S013','B013','2022-12-18 03:54:56',90),
('S014','B014','2022-12-24 03:56:27',70),
('S015','B015','2022-12-18 03:57:09',50),
('S016','B016','2022-12-08 04:11:49',90),
('S017','B017','2022-12-18 04:12:07',65),
('S018','B018','2022-12-15 04:12:34',70),
('S019','B019','2022-12-27 04:13:29',70),
('S020','B001','2023-12-08 13:28:57',0),
('S021','B002','2023-12-08 13:30:19',4),
('S022','B001','2023-12-08 14:00:34',25),
('S023','B005','2023-12-08 14:56:33',6),
('S024','B008','2023-12-08 16:24:37',35),
('S025','B002','2023-12-11 16:26:13',30),
('S026','B001','2024-12-10 09:10:20',5);

/* Trigger structure for table `suplaibuku` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `addTrigger` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `addTrigger` AFTER INSERT ON `suplaibuku` FOR EACH ROW BEGIN
          
     update buku 
     set jumlahBuku = jumlahBuku + new.jumlah
     where idBuku = new.idBuku;

    END */$$


DELIMITER ;

/* Trigger structure for table `suplaibuku` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `deleteTrigger` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `deleteTrigger` AFTER DELETE ON `suplaibuku` FOR EACH ROW BEGIN
    
    update buku
    set jumlahBuku = jumlahBuku - old.jumlah
    where idBuku = old.idBuku;

    END */$$


DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;