DATA BASE KEPEGAWAIAN
posted by :Muhammad Nazih
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `dbpegawai` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `dbpegawai` ;
-- -----------------------------------------------------
-- Table `dbpegawai`.`agama`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`agama` (
`id` INT NOT NULL AUTO_INCREMENT ,
`nama` VARCHAR(20) NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `nama_UNIQUE` (`nama` ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `dbpegawai`.`divisi`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`divisi` (
`id` INT NOT NULL AUTO_INCREMENT ,
`nama` VARCHAR(30) NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `nama_UNIQUE` (`nama` ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `dbpegawai`.`jabatan`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`jabatan` (
`id` INT NOT NULL AUTO_INCREMENT ,
`nama` VARCHAR(30) NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `nama_UNIQUE` (`nama` ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `dbpegawai`.`pegawai`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`pegawai` (
`id` INT NOT NULL AUTO_INCREMENT ,
`nip` VARCHAR(20) NOT NULL ,
`nama` VARCHAR(45) NOT NULL ,
`jenis_kelamin` VARCHAR(10) NOT NULL ,
`tmp_lahir` VARCHAR(30) NOT NULL ,
`tgl_lahir` DATE NOT NULL ,
`id_agama` INT NOT NULL ,
`id_divisi` INT NOT NULL ,
`id_jabatan` INT NOT NULL ,
`alamat` VARCHAR(100) NOT NULL ,
`telp` VARCHAR(15) NOT NULL ,
`email` VARCHAR(45) NULL ,
`foto` VARCHAR(20) NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `nip_UNIQUE` (`nip` ASC) ,
UNIQUE INDEX `email_UNIQUE` (`email` ASC) ,
INDEX `fk_pegawai_agama` (`id_agama` ASC) ,
INDEX `fk_pegawai_divisi1` (`id_divisi` ASC) ,
INDEX `fk_pegawai_jabatan1` (`id_jabatan` ASC) ,
CONSTRAINT `fk_pegawai_agama`
FOREIGN KEY (`id_agama` )
REFERENCES `dbpegawai`.`agama` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_pegawai_divisi1`
FOREIGN KEY (`id_divisi` )
REFERENCES `dbpegawai`.`divisi` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_pegawai_jabatan1`
FOREIGN KEY (`id_jabatan` )
REFERENCES `dbpegawai`.`jabatan` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `dbpegawai`.`gaji`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`gaji` (
`id` INT NOT NULL AUTO_INCREMENT ,
`id_pegawai` INT NOT NULL ,
`gapok` DOUBLE NOT NULL ,
`tunjab` DOUBLE NOT NULL ,
`askes` DOUBLE NOT NULL ,
`lain2` DOUBLE NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_gaji_pegawai1` (`id_pegawai` ASC) ,
CONSTRAINT `fk_gaji_pegawai1`
FOREIGN KEY (`id_pegawai` )
REFERENCES `dbpegawai`.`pegawai` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `dbpegawai`.`materi`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`materi` (
`id` INT NOT NULL AUTO_INCREMENT ,
`nama` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `dbpegawai`.`diklat`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`diklat` (
`id` INT NOT NULL AUTO_INCREMENT ,
`id_pegawai` INT NOT NULL ,
`id_materi` INT NOT NULL ,
`tgl` DATE NOT NULL ,
`tempat` VARCHAR(100) NOT NULL ,
`narasumber` VARCHAR(45) NULL ,
`diklatcol` VARCHAR(45) NULL ,
INDEX `fk_pegawai_has_materi_materi1` (`id_materi` ASC) ,
INDEX `fk_pegawai_has_materi_pegawai1` (`id_pegawai` ASC) ,
PRIMARY KEY (`id`) ,
CONSTRAINT `fk_pegawai_has_materi_pegawai1`
FOREIGN KEY (`id_pegawai` )
REFERENCES `dbpegawai`.`pegawai` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_pegawai_has_materi_materi1`
FOREIGN KEY (`id_materi` )
REFERENCES `dbpegawai`.`materi` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `dbpegawai`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbpegawai`.`user` (
`id` INT NOT NULL AUTO_INCREMENT ,
`nama` VARCHAR(45) NOT NULL ,
`username` VARCHAR(30) NOT NULL ,
`pass` VARCHAR(32) NOT NULL ,
`email` VARCHAR(45) NOT NULL ,
`role` VARCHAR(20) NOT NULL ,
`joindate` DATETIME NOT NULL ,
`status` VARCHAR(20) NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,
UNIQUE INDEX `email_UNIQUE` (`email` ASC) )
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;