-- http://www.phpmyadmin.net
--




--
-- Database: `tracerpal2023`
--

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

--
-- Table structure for table `ref_identitas`
--

CREATE TABLE IF NOT EXISTS `ref_identitas` (
  `id` int(1) NOT NULL,
  `nama_lembaga` varchar(255) DEFAULT NULL,
  `nama_kepala` varchar(255) DEFAULT NULL,
  `nip_kepala` varchar(255) DEFAULT NULL,
  `alamat` varchar(255) DEFAULT NULL,
  `kecamatan` varchar(255) DEFAULT NULL,
  `kab_kota` varchar(255) DEFAULT NULL,
  `provinsi` varchar(255) DEFAULT NULL,
  `no_telpon` varchar(255) DEFAULT NULL,
  `kode_pos` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `logo1` varchar(255) DEFAULT NULL,
  `logo2` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ref_identitas`
--

INSERT INTO `ref_identitas` (`id`, `nama_lembaga`, `nama_kepala`, `nip_kepala`, `alamat`, `kecamatan`, `kab_kota`, `provinsi`, `no_telpon`, `kode_pos`, `website`, `email`, `logo1`, `logo2`) VALUES
(1, 'SMK Teknik PAL Surabaya', 'Arif Binsar Surya, M.Pd', '-', 'Jl. Ujung Surabaya', 'Semampir', 'Surabaya', 'Jawa ATimur', '(0736) 22020', '38371', 'http://dinkes.tanahbumbukab.go.id', 'hari.aspriyono@gmail.com', 'ca27b-51959-logo_kiri.jpg', '2a704-e8739-logo_kanan.png');

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

--
-- Table structure for table `ref_jurusan`
--

CREATE TABLE IF NOT EXISTS `ref_jurusan` (
`id` bigint(20) NOT NULL,
  `kode` varchar(30) DEFAULT NULL,
  `nama_jurusan` varchar(100) DEFAULT NULL,
  `aktif` enum('Y','N') DEFAULT 'Y'
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ref_jurusan`
--

INSERT INTO `ref_jurusan` (`id`, `kode`, `nama_jurusan`, `aktif`) VALUES
(1, 'E', 'Teknik Otomasi Industri + Plus', 'Y'),
(2, 'C', 'Teknik Konstruksi Kapal Baja', 'Y'),
(3, 'W', 'Teknik Pengelasan', 'Y'),
(4, 'M', 'Teknik Permesinan', 'Y'),
(5, 'N', 'Teknik Komputer dan Jaringan', 'Y');

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

--
-- Table structure for table `ref_kelamin`
--

CREATE TABLE IF NOT EXISTS `ref_kelamin` (
`id` bigint(2) NOT NULL,
  `nama_kelamin` varchar(30) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ref_kelamin`
--

INSERT INTO `ref_kelamin` (`id`, `nama_kelamin`) VALUES
(1, 'L'),
(2, 'P');

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

--
-- Table structure for table `ref_ruang`
--

CREATE TABLE IF NOT EXISTS `ref_ruang` (
`id` bigint(20) NOT NULL,
  `nama_ruang` varchar(100) DEFAULT NULL,
  `jenis_ruang_id` bigint(20) DEFAULT NULL,
  `keterangan_ruang` varchar(255) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ref_ruang`
--

INSERT INTO `ref_ruang` (`id`, `nama_ruang`, `jenis_ruang_id`, `keterangan_ruang`) VALUES
(1, 'Kelas X-TKJ', 1, 'Ruang Kelas X-TKJ');

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

--
-- Table structure for table `s_grup`
--

CREATE TABLE IF NOT EXISTS `s_grup` (
`id` bigint(20) NOT NULL,
  `nama_grup` varchar(50) DEFAULT NULL,
  `keterangan` varchar(255) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `s_grup`
--

INSERT INTO `s_grup` (`id`, `nama_grup`, `keterangan`) VALUES
(1, 'Super Admin', 'Super Administrator'),
(2, 'Guru', '-'),
(3, 'Ketua Jurusan', '-');

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

--
-- Table structure for table `s_grup_modul`
--

CREATE TABLE IF NOT EXISTS `s_grup_modul` (
`id` bigint(20) NOT NULL,
  `grup_id` bigint(20) NOT NULL,
  `modul_id` bigint(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `s_grup_modul`
--

INSERT INTO `s_grup_modul` (`id`, `grup_id`, `modul_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(6, 1, 4),
(7, 1, 5);

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

--
-- Table structure for table `s_grup_ruang`
--

CREATE TABLE IF NOT EXISTS `s_grup_ruang` (
`id` bigint(20) NOT NULL,
  `grup_id` bigint(20) NOT NULL,
  `ruang_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `s_kategori_modul`
--

CREATE TABLE IF NOT EXISTS `s_kategori_modul` (
`id` bigint(20) NOT NULL,
  `nama_kategori_modul` varchar(100) DEFAULT NULL,
  `icon_menu` varchar(50) DEFAULT NULL,
  `urutan` int(10) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `s_kategori_modul`
--

INSERT INTO `s_kategori_modul` (`id`, `nama_kategori_modul`, `icon_menu`, `urutan`) VALUES
(1, 'Master Data', 'fa-tachometer', 1),
(2, 'Tracer Study', 'fa-group', 2),
(3, 'Ruangan', 'fa-building', 3),
(4, 'Pengaturan', 'fa-gear', 6),
(5, 'Data Referensi', 'fa-hdd-o', 7),
(6, 'Akademik', 'fa-calendar', 4),
(7, 'Inventaris', 'fa-cube', 5);

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

--
-- Table structure for table `s_modul`
--

CREATE TABLE IF NOT EXISTS `s_modul` (
`id` bigint(20) NOT NULL,
  `kategori_modul_id` bigint(20) DEFAULT NULL,
  `nama_modul` varchar(100) DEFAULT NULL,
  `nama_menu` varchar(100) DEFAULT NULL,
  `icon_menu` varchar(100) DEFAULT NULL,
  `aktif` enum('Y','N') DEFAULT 'Y',
  `urutan` int(2) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `s_modul`
--

INSERT INTO `s_modul` (`id`, `kategori_modul_id`, `nama_modul`, `nama_menu`, `icon_menu`, `aktif`, `urutan`) VALUES
(1, 4, 'identitas', 'Identitas Lembaga', 'fa-info-circle', 'Y', NULL),
(2, 4, 'pengguna', 'Pengguna', 'fa-user', 'Y', NULL),
(3, 4, 'grup_pengguna', 'Grup Pengguna', 'fa-users', 'Y', NULL),
(4, 1, 'jurusan', 'Master Jurusan', 'fa-building', 'Y', NULL),
(5, 2, 'data_tracer', 'Data Tracer Study', 'fa-users', 'Y', NULL),
(7, 1, 'kelamin', 'Jenis Kelamin', 'fa-cube', 'Y', NULL);

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

--
-- Table structure for table `s_users`
--

CREATE TABLE IF NOT EXISTS `s_users` (
`id` bigint(20) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `nama_lengkap` varchar(255) DEFAULT NULL,
  `foto` varchar(100) DEFAULT NULL,
  `grup_id` bigint(20) DEFAULT NULL,
  `aktif` enum('Y','N') DEFAULT 'Y',
  `update_terakhir` date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `s_users`
--

INSERT INTO `s_users` (`id`, `username`, `password`, `nama_lengkap`, `foto`, `grup_id`, `aktif`, `update_terakhir`) VALUES
(1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Arif Binsar Surya', 'default.jpg', 1, 'Y', '2020-01-05');

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

--
-- Table structure for table `tracer_study`
--

CREATE TABLE IF NOT EXISTS `tracer_study` (
`id` bigint(20) NOT NULL,
  `nik` varchar(20) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `nama_alumni` varchar(100) DEFAULT NULL,
  `kelamin_id` bigint(20) DEFAULT NULL,
  `usia` varchar(50) DEFAULT NULL,
  `tahun_lulus` int(4) DEFAULT NULL,
  `pendidikan_terakhir` varchar(100) DEFAULT NULL,
  `jurusan_id` bigint(20) DEFAULT NULL,
  `no_hp` varchar(100) DEFAULT NULL,
  `alamat_ktp` varchar(255) DEFAULT NULL,
  `kecamatan` varchar(255) DEFAULT NULL,
  `desa_kelurahan` varchar(255) DEFAULT NULL,
  `kab_kota` varchar(255) DEFAULT NULL,
  `provinsi` varchar(255) DEFAULT NULL,
  `bekerja` enum('Ya','Tidak') DEFAULT NULL,
  `belum_bekerja` enum('Ya','Tidak') DEFAULT NULL,
  `studi_lanjut` enum('Ya','Tidak') DEFAULT NULL,
  `perusahaan` varchar(255) DEFAULT NULL,
  `jabatan` varchar(255) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tracer_study`
--

INSERT INTO `tracer_study` (`id`, `nik`, `password`, `nama_alumni`, `kelamin_id`, `usia`, `tahun_lulus`, `pendidikan_terakhir`, `jurusan_id`, `no_hp`, `alamat_ktp`, `kecamatan`, `desa_kelurahan`, `kab_kota`, `provinsi`, `bekerja`, `belum_bekerja`, `studi_lanjut`, `perusahaan`, `jabatan`) VALUES
(1, '3578161606060005', 'e10adc3949ba59abbe56e057f20f883e', 'Muhammad Dzaki Hafiish', 1, '18', 2024, 'SMK', 3, '0895397080489', 'Endrosono 10/22', 'Semampir', 'Wonokusumo', 'Surabaya', 'Jawa Timur', 'Tidak', 'Ya', 'Tidak', '', '');

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

--
-- Stand-in structure for view `view_tracer_study`
--
CREATE TABLE IF NOT EXISTS `view_tracer_study` (
`id` bigint(20)
,`nik` varchar(20)
,`password` varchar(255)
,`nama_alumni` varchar(100)
,`kelamin_id` bigint(20)
,`usia` varchar(50)
,`tahun_lulus` int(4)
,`pendidikan_terakhir` varchar(100)
,`jurusan_id` bigint(20)
,`no_hp` varchar(100)
,`alamat_ktp` varchar(255)
,`kecamatan` varchar(255)
,`desa_kelurahan` varchar(255)
,`kab_kota` varchar(255)
,`provinsi` varchar(255)
,`bekerja` enum('Ya','Tidak')
,`belum_bekerja` enum('Ya','Tidak')
,`studi_lanjut` enum('Ya','Tidak')
,`perusahaan` varchar(255)
,`jabatan` varchar(255)
,`nama_kelamin` varchar(30)
,`kode` varchar(30)
,`nama_jurusan` varchar(100)
);
-- --------------------------------------------------------

--
-- Structure for view `view_tracer_study`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_tracer_study` AS select `tracer_study`.`id` AS `id`,`tracer_study`.`nik` AS `nik`,`tracer_study`.`password` AS `password`,`tracer_study`.`nama_alumni` AS `nama_alumni`,`tracer_study`.`kelamin_id` AS `kelamin_id`,`tracer_study`.`usia` AS `usia`,`tracer_study`.`tahun_lulus` AS `tahun_lulus`,`tracer_study`.`pendidikan_terakhir` AS `pendidikan_terakhir`,`tracer_study`.`jurusan_id` AS `jurusan_id`,`tracer_study`.`no_hp` AS `no_hp`,`tracer_study`.`alamat_ktp` AS `alamat_ktp`,`tracer_study`.`kecamatan` AS `kecamatan`,`tracer_study`.`desa_kelurahan` AS `desa_kelurahan`,`tracer_study`.`kab_kota` AS `kab_kota`,`tracer_study`.`provinsi` AS `provinsi`,`tracer_study`.`bekerja` AS `bekerja`,`tracer_study`.`belum_bekerja` AS `belum_bekerja`,`tracer_study`.`studi_lanjut` AS `studi_lanjut`,`tracer_study`.`perusahaan` AS `perusahaan`,`tracer_study`.`jabatan` AS `jabatan`,`ref_kelamin`.`nama_kelamin` AS `nama_kelamin`,`ref_jurusan`.`kode` AS `kode`,`ref_jurusan`.`nama_jurusan` AS `nama_jurusan` from ((`tracer_study` join `ref_kelamin` on((`tracer_study`.`kelamin_id` = `ref_kelamin`.`id`))) join `ref_jurusan` on((`tracer_study`.`jurusan_id` = `ref_jurusan`.`id`)));

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ref_identitas`
--
ALTER TABLE `ref_identitas`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `ref_jurusan`
--
ALTER TABLE `ref_jurusan`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `kode` (`kode`);

--
-- Indexes for table `ref_kelamin`
--
ALTER TABLE `ref_kelamin`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `ref_ruang`
--
ALTER TABLE `ref_ruang`
 ADD PRIMARY KEY (`id`), ADD KEY `jenis_ruang_id` (`jenis_ruang_id`);

--
-- Indexes for table `s_grup`
--
ALTER TABLE `s_grup`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `s_grup_modul`
--
ALTER TABLE `s_grup_modul`
 ADD PRIMARY KEY (`id`), ADD KEY `s_grup_modul_ibfk_2` (`modul_id`), ADD KEY `s_grup_modul_ibfk_1` (`grup_id`);

--
-- Indexes for table `s_grup_ruang`
--
ALTER TABLE `s_grup_ruang`
 ADD PRIMARY KEY (`id`), ADD KEY `s_grup_modul_ibfk_1` (`grup_id`), ADD KEY `s_grup_modul_ibfk_2` (`ruang_id`) USING BTREE;

--
-- Indexes for table `s_kategori_modul`
--
ALTER TABLE `s_kategori_modul`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `s_modul`
--
ALTER TABLE `s_modul`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `nama_modul` (`nama_modul`), ADD KEY `kategori_modul_id` (`kategori_modul_id`);

--
-- Indexes for table `s_users`
--
ALTER TABLE `s_users`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `username` (`username`), ADD KEY `group_id` (`grup_id`);

--
-- Indexes for table `tracer_study`
--
ALTER TABLE `tracer_study`
 ADD PRIMARY KEY (`id`), ADD KEY `kelamin_id` (`kelamin_id`), ADD KEY `jurusan_id` (`jurusan_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ref_jurusan`
--
ALTER TABLE `ref_jurusan`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `ref_kelamin`
--
ALTER TABLE `ref_kelamin`
MODIFY `id` bigint(2) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `ref_ruang`
--
ALTER TABLE `ref_ruang`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `s_grup`
--
ALTER TABLE `s_grup`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `s_grup_modul`
--
ALTER TABLE `s_grup_modul`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT for table `s_grup_ruang`
--
ALTER TABLE `s_grup_ruang`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `s_kategori_modul`
--
ALTER TABLE `s_kategori_modul`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT for table `s_modul`
--
ALTER TABLE `s_modul`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT for table `s_users`
--
ALTER TABLE `s_users`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `tracer_study`
--
ALTER TABLE `tracer_study`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `s_grup_modul`
--
ALTER TABLE `s_grup_modul`
ADD CONSTRAINT `s_grup_modul_ibfk_1` FOREIGN KEY (`grup_id`) REFERENCES `s_grup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `s_grup_modul_ibfk_2` FOREIGN KEY (`modul_id`) REFERENCES `s_modul` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `s_grup_ruang`
--
ALTER TABLE `s_grup_ruang`
ADD CONSTRAINT `s_grup_ruang_ibfk_1` FOREIGN KEY (`grup_id`) REFERENCES `s_grup` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `s_grup_ruang_ibfk_2` FOREIGN KEY (`ruang_id`) REFERENCES `ref_ruang` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `s_modul`
--
ALTER TABLE `s_modul`
ADD CONSTRAINT `s_modul_ibfk_1` FOREIGN KEY (`kategori_modul_id`) REFERENCES `s_kategori_modul` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `s_users`
--
ALTER TABLE `s_users`
ADD CONSTRAINT `s_users_ibfk_1` FOREIGN KEY (`grup_id`) REFERENCES `s_grup` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `tracer_study`
--
ALTER TABLE `tracer_study`
ADD CONSTRAINT `tracer_study_ibfk_1` FOREIGN KEY (`kelamin_id`) REFERENCES `ref_kelamin` (`id`) ON UPDATE CASCADE,
ADD CONSTRAINT `tracer_study_ibfk_2` FOREIGN KEY (`jurusan_id`) REFERENCES `ref_jurusan` (`id`) ON UPDATE CASCADE;

