DATA BASE KEPEGAWAIAN

 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;

Read More

data employee

Table structure for table `EMPLOYEE`
--

DROP TABLE IF EXISTS `EMPLOYEE`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `EMPLOYEE` (
  `fname` varchar(20) DEFAULT NULL,
  `minit` varchar(1) DEFAULT NULL,
  `lname` varchar(20) DEFAULT NULL,
  `ssn` int(11) NOT NULL,
  `bdate` text,
  `address` varchar(30) DEFAULT NULL,
  `sex` varchar(1) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  `superssn` int(11) DEFAULT NULL,
  `dno` int(11) DEFAULT NULL,
  PRIMARY KEY (`ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `EMPLOYEE`
--

LOCK TABLES `EMPLOYEE` WRITE;
/*!40000 ALTER TABLE `EMPLOYEE` DISABLE KEYS */;
INSERT INTO `EMPLOYEE` VALUES ('John','B','Smith',123456789,'1965-01-09','731 Fondren, Houston, TX','M',30000,333445555,5),('Franklin','T','Wong',333445555,'1955-12-08','638 Voss, Houston, TX','M',40000,888665555,5),('Joyce','A','English',453453453,'1972-07-31','5631 Rice, Houston, TX','F',25000,333445555,5),('Ramesh','K','Narayan',666884444,'1962-09-15','975 Fire Oak, Humble, TX','M',38000,333445555,5),('James','E','Borg',888665555,'1937-11-10','450 Stone, Houston, TX','M',55000,NULL,4),('Jennifer','S','Wallace',987654321,'1941-06-20','291 Berry, Bellaire, TX','F',43000,888665555,4),('Ahmad','V','Jabbar',987987987,'1969-03-29','980 Dallas, Houston, TX','M',25000,987654321,4),('Alicia','J','Zelaya',999887777,'1968-07-19','3321 Castle, Spring, TX','F',25000,987654321,4);
/*!40000 ALTER TABLE `EMPLOYEE` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `department`
--

DROP TABLE IF EXISTS `department`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `department` (
  `dname` varchar(20) DEFAULT NULL,
  `dnumber` int(11) NOT NULL,
  `mgrssn` int(11) DEFAULT NULL,
  `mgrstartdate` text,
  PRIMARY KEY (`dnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `department`
--

LOCK TABLES `department` WRITE;
/*!40000 ALTER TABLE `department` DISABLE KEYS */;
INSERT INTO `department` VALUES ('Headquarters',1,888665555,'1981-06-19'),('Administration',4,987654321,'1995-01-01'),('Research',5,333445555,'1988-5-22');
/*!40000 ALTER TABLE `department` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `dependent`
--

DROP TABLE IF EXISTS `dependent`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dependent` (
  `essn` int(11) DEFAULT NULL,
  `dependent_name` varchar(30) DEFAULT NULL,
  `sex` varchar(1) DEFAULT NULL,
  `bdate` text,
  `relationship` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dependent`
--

LOCK TABLES `dependent` WRITE;
/*!40000 ALTER TABLE `dependent` DISABLE KEYS */;
INSERT INTO `dependent` VALUES (333445555,'Alice','F','1986-04-05','DAUGHTER'),(333445555,'Theodore','M','1983-10-25','SON'),(333445555,'Joy','F','1958-05-03','SPOUSE'),(987654321,'Abner','M','1942-02-28','SPOUSE'),(123456789,'Michael','M','1988-01-04','SON'),(123456789,'Alice','F','1988-12-30','DAUGHTER'),(123456789,'Elizabeth','F','1967-05-05','SPOUSE');
/*!40000 ALTER TABLE `dependent` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `dept_locations`
--

DROP TABLE IF EXISTS `dept_locations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept_locations` (
  `dnumber` int(11) DEFAULT NULL,
  `dlocation` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dept_locations`
--

LOCK TABLES `dept_locations` WRITE;
/*!40000 ALTER TABLE `dept_locations` DISABLE KEYS */;
INSERT INTO `dept_locations` VALUES (1,'Houston'),(4,'Stafford'),(5,'Bellaire'),(5,'Sugarland'),(5,'Houston');
/*!40000 ALTER TABLE `dept_locations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `project`
--

DROP TABLE IF EXISTS `project`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project` (
  `pname` varchar(20) DEFAULT NULL,
  `pnumber` int(11) NOT NULL,
  `plocation` text,
  `dnum` int(11) DEFAULT NULL,
  PRIMARY KEY (`pnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `project`
--

LOCK TABLES `project` WRITE;
/*!40000 ALTER TABLE `project` DISABLE KEYS */;
INSERT INTO `project` VALUES ('ProductX',1,'Bellaire',5),('ProductY',2,'Sugarland',5),('ProductZ',3,'Houston',5),('Computerization',10,'Stafford',4),('Reorganization',20,'Houston',1),('New Benefits',30,'Stafford',4);
/*!40000 ALTER TABLE `project` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `works_on`
--

DROP TABLE IF EXISTS `works_on`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `works_on` (
  `essn` int(11) DEFAULT NULL,
  `pno` int(11) DEFAULT NULL,
  `hours` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `works_on`
--

LOCK TABLES `works_on` WRITE;
/*!40000 ALTER TABLE `works_on` DISABLE KEYS */;
INSERT INTO `works_on` VALUES (123456789,1,'32.5'),(123456789,2,'7.5'),(666884444,3,'40.0'),(453453453,1,'20.0'),(453453453,2,'20.0'),(333445555,2,'10.0'),(333445555,3,'10.0'),(333445555,10,'10.0'),(333445555,20,'10.0'),(999887777,30,'30.0'),(999887777,10,'10.0'),(987987987,10,'35.0'),(987987987,30,'5.0'),(987654321,30,'20.0'),(987654321,20,'15.0'),(888665555,20,'null');
/*!40000 ALTER TABLE `works_on` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2013-01-15  9:05:53

Read More