IT Solution

Wednesday 3 July 2013

DATA BASE KEPEGAWAIAN

July 03, 2013 Posted by Bops No comments
 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;

0 comments:

Post a Comment

Terimakasih telah bergabung di blog saya.

Featured post

Game PC (BMW M3)