#
# TABLE STRUCTURE FOR: User
#

DROP TABLE IF EXISTS `User`;

CREATE TABLE `User` (
  `username` varchar(20) NOT NULL,
  `password` varchar(15) NOT NULL,
  `nama_depan` varchar(20) NOT NULL,
  `nama_belakang` varchar(20) NOT NULL,
  `jenis_kelamin` varchar(2) NOT NULL,
  `tempat_lahir` varchar(100) NOT NULL,
  `tanggal_lahir` date NOT NULL,
  `alamat` varchar(100) NOT NULL,
  `no_telp` varchar(12) NOT NULL,
  `email` varchar(25) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: WaliOrangTua
#

DROP TABLE IF EXISTS `WaliOrangTua`;

CREATE TABLE `WaliOrangTua` (
  `username_wali` varchar(25) NOT NULL,
  `status_wali` varchar(13) NOT NULL,
  `pekerjaan` varchar(20) NOT NULL
  PRIMARY KEY (`username_wali`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: Mahasiswa
#

DROP TABLE IF EXISTS `Mahasiswa`;

CREATE TABLE `Mahasiswa` (
  `nim` varchar(10) NOT NULL,
  `username` varchar(10) NOT NULL,
  `status_masuk` varchar(20) NOT NULL,
  `kode_prodi` varchar(10) NOT NULL,
  `nid` int(10) NOT NULL,
  `username_wali` varchar(25) NOT NULL
  PRIMARY KEY (`nim`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: Dosen
#

DROP TABLE IF EXISTS `Dosen`;

CREATE TABLE `Dosen` (
  `nid` int(10) NOT NULL,
  `username` varchar(25) NOT NULL,
  `jabatan_fungsional` varchar(20) NOT NULL,
  `tahun_masuk` int(4) NOT NULL,
  `kode_prodi` varchar(10) NOT NULL,
  `perkuliahan` varchar(10) NOT NULL
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: ProgramStudi
#

DROP TABLE IF EXISTS `ProgramStudi`;

CREATE TABLE `ProgramStudi` (
  `kode_studi` varchar(10) NOT NULL,
  `nama_prodi` varchar(25) NOT NULL,
  `kode_fakultas` varchar(10) NOT NULL,
  `nid` int(10) NOT NULL,
  PRIMARY KEY (`kode_studi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: Fakultas
#

DROP TABLE IF EXISTS `Fakultas`;

CREATE TABLE `Fakultas` (
  `kode_fakultas` varchar(10) NOT NULL,
  `nama_fakultas` varchar(20) NOT NULL,
  `nid` int(9) NOT NULL,
  PRIMARY KEY (`kode_fakultas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: Perwalian
#

DROP TABLE IF EXISTS `Perwalian`;

CREATE TABLE `Perwalian` (
  `kode_prs` varchar(10) NOT NULL,
  `nim` varchar(10) NOT NULL,
  `nid` int(10) NOT NULL,
  `tahun_ajar` int(4) NOT NULL,
  `jumlah_sks` int(2) NOT NULL,
  `status` varchar(15) NOT NULL,
  `tanggal_diajukan` date NOT NULL,
  `tanggal_disetujui` date NOT NULL
  PRIMARY KEY (`kode_prs`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: Perkuliahan
#

DROP TABLE IF EXISTS `Perkuliahan`;

CREATE TABLE `Perkuliahan` (
  `kode_prs` varchar(10) NOT NULL,
  `perkuliahan` varchar(10) NOT NULL,
  `tahun_ajar` int(4) NOT NULL,
  `ruangan` varchar(8) NOT NULL,
  `hari` varchar(10) NOT NULL,
  `jam` time NOT NULL,
  PRIMARY KEY (`kode_prs`,`perkuliahan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# TABLE STRUCTURE FOR: MataKuliah
#

DROP TABLE IF EXISTS `MataKuliah`;

CREATE TABLE `MataKuliah` (
  `kode_matkul` varchar(10) NOT NULL,
  `nama_matkul` varchar(25) NOT NULL,
  `sks` int(2) NOT NULL,
  `semester` int(2) NOT NULL,
  `kurikulum` int(4) NOT NULL,
  `perkulihan` varchar(10) NOT NULL,
  PRIMARY KEY (`kode_matkul`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `WaliOrangTua`
ADD CONSTRAINT `fk_waliorangtua_user`
FOREIGN KEY (`username_wali`) REFERENCES `User` (`username`);


ALTER TABLE `Mahasiswa`
ADD CONSTRAINT `fk_mahasiswa_user`
FOREIGN KEY (`username`) REFERENCES `User` (`username`);

ALTER TABLE `Mahasiswa`
ADD CONSTRAINT `fk_mahasiswa_prodi`
FOREIGN KEY (`kode_prodi`) REFERENCES `ProgramStudi` (`kode_prodi`);

ALTER TABLE `Mahasiswa`
ADD CONSTRAINT `fk_mahasiswa_dosen`
FOREIGN KEY (`nid`) REFERENCES `Dosen` (`nid`);

ALTER TABLE `Mahasiswa`
ADD CONSTRAINT `fk_mahasiswa_waliorangtua`
FOREIGN KEY (`username_wali`) REFERENCES `WaliOrangTua` (`username_wali`);


ALTER TABLE `Dosen`
ADD CONSTRAINT `fk_dosen_user`
FOREIGN KEY (`username`) REFERENCES `User` (`username`);

ALTER TABLE `Dosen`
ADD CONSTRAINT `fk_dosen_prodi`
FOREIGN KEY (`kode_prodi`) REFERENCES `ProgramStudi` (`kode_prodi`);

ALTER TABLE `Dosen`
ADD CONSTRAINT `fk_dosen_perkuliahan`
FOREIGN KEY (`perkuliahan`) REFERENCES `Perkuliahan` (`perkuliahan`);


ALTER TABLE `ProgramStudi`
ADD CONSTRAINT `fk_programstudi_fakultas`
FOREIGN KEY (`kode_fakultas`) REFERENCES `Fakultas` (`kode_fakultas`);

ALTER TABLE `ProgramStudi`
ADD CONSTRAINT `fk_programstudi_dosen`
FOREIGN KEY (`nid`) REFERENCES `Dosen` (`nid`);


ALTER TABLE `Fakultas`
ADD CONSTRAINT `fk_fakultas_dosen`
FOREIGN KEY (`nid`) REFERENCES `Dosen` (`nid`);


ALTER TABLE `Perwalian`
ADD CONSTRAINT `fk_perwalian_mahasiswa`
FOREIGN KEY (`nim`) REFERENCES `Mahasiswa` (`nim`);

ALTER TABLE `Perwalian`
ADD CONSTRAINT `fk_perwalian_dosen`
FOREIGN KEY (`nid`) REFERENCES `Dosen` (`nid`);


ALTER TABLE `Perkuliahan`
ADD CONSTRAINT `fk_perkuliahan_perwalian`
FOREIGN KEY (`kode_prs`) REFERENCES `Perwalian` (`kode_prs`);


ALTER TABLE `MataKuliah`
ADD CONSTRAINT `fk_matakuliah_perkuliahan`
FOREIGN KEY (`perkuliahan`) REFERENCES `Perkuliahan` (`perkuliahan`);