Pertama-tama login ke server postgresql.
it@it-H55M-S2:~$ ssh root@172.16.16.125 root@172.16.16.125's password: Last login: Mon Sep 15 16:56:28 2014 from 192.168.100.16
[root@PostgreSQL-50G ~]# su - postgres -bash-4.1$
Buat user-user hrd.
-bash-4.1$ createuser -A -D -P -E hrd Enter password for new role: Enter it again: Password:
-bash-4.1$ createuser -A -D -P -E hrd_akiw Enter password for new role: Enter it again: Password:
-bash-4.1$ createuser -A -D -P -E hrd_nunut Enter password for new role: Enter it again: Password:
-bash-4.1$ \exit logout
Opsi -A = tidak boleh menambahkan user baru.
Opsi -D = tidak boleh membuat database baru.
Opsi -P = akan menambahkan password.
Opsi -E = melakukan enkripsi terhadap password yang disimpan.
Sekedar mencoba, silahkan login sebagai user administrator (super user) yang kita berbeda. Dengan user tersebut, buat database data karyawan, kemudian logout.
[root@PostgreSQL-50G ~]# psql -U havizul Password for user havizul: psql (9.3.5) Type "help" for help. havizul=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- dbsaya | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | havizul=CTc/postgres havizul | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) havizul=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- admin | Superuser, Create role, Create DB | {} havizul | Superuser, Create role, Create DB | {} hrd | | {} hrd_akiu | | {} hrd_nunus | | {} postgres | Superuser, Create role, Create DB, Replication | {}
havizul=# create database db_hrd_dtkry owner havizul; CREATE DATABASE
havizul=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+-------------+-------------+----------------------- db_hrd_dtkry | havizul | UTF8 | en_US.UTF-8 | en_US.UTF-8 | dbsaya | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | havizul=CTc/postgres havizul | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (6 rows)
havizul=# \q
[root@PostgreSQL-50G ~]#
Masuk ke postgresql prompt dengan user havizul dan database yang baru dibuat, kemudian lanjutkan dengan membuat tabel Lokasi_Kerja.
[root@PostgreSQL-50G ~]# psql -U havizul -d db_hrd_dtkry -W Password for user havizul: psql (9.3.5) Type "help" for help. db_hrd_dtkry=# \dt No relations found.
db_hrd_dtkry=# create table Lokasi_Kerja (Kode_Lokasi char(5) PRIMARY KEY, Lokasi varchar(25)); CREATE TABLE
db_hrd_dtkry=# \dt List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | lokasi_kerja | table | havizul (1 row) db_hrd_dtkry=#
Membuat tabel Departemen.
db_hrd_dtkry=# create table Departemen (Kode_Departemen char(5) primary key, Nama_Departemen varchar(50)); CREATE TABLE
db_hrd_dtkry=# \dt List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | departemen | table | havizul public | lokasi_kerja | table | havizul (2 rows)
Membuat tabel Jabatan.
db_hrd_dtkry=# create table Jabatan (Kode_Jabatan char(5) primary key, Nama_Jabatan varchar(50)); db_hrd_dtkry=# \d+ jabatan; Table "public.jabatan" Column | Type | Modifiers | Storage | Stats target | De scription --------------+-----------------------+-----------+----------+--------------+--- ---------- kode_jabatan | character(5) | not null | extended | | nama_jabatan | character varying(50) | | extended | | Indexes: "jabatan_pkey" PRIMARY KEY, btree (kode_jabatan) Has OIDs: no
Membuat tabel Status_Pekerja.
db_hrd_dtkry=# create table Status_Pekerja (Kode_Status_Pekerja char(5) primary key, Status_Pekerja varchar(25) not null); CREATE TABLE
db_hrd_dtkry=# \d+ status_pekerja; Table "public.status_pekerja" Column | Type | Modifiers | Storage | Stats targ et | Description ---------------------+-----------------------+-----------+----------+----------- ---+------------- kode_status_pekerja | character(5) | not null | extended | | status_pekerja | character varying(25) | not null | extended | | Indexes: "status_pekerja_pkey" PRIMARY KEY, btree (kode_status_pekerja) Has OIDs: no
Membuat tabel Jenis_Pekerja.
db_hrd_dtkry=# create table Jenis_Pekerja (Kode_Jenis_Pekerja char(5) primary key, Jenis_Pekerja varchar(25) not null); CREATE TABLE
db_hrd_dtkry=# \d+ Jenis_Pekerja; Table "public.jenis_pekerja" Column | Type | Modifiers | Storage | Stats targe t | Description --------------------+-----------------------+-----------+----------+------------ --+------------- kode_jenis_pekerja | character(5) | not null | extended | | jenis_pekerja | character varying(25) | not null | extended | | Indexes: "jenis_pekerja_pkey" PRIMARY KEY, btree (kode_jenis_pekerja) Has OIDs: no
Membuat tabel Upah.
db_hrd_dtkry=# create table Upah (Kode_Upah char(5) primary key, Tipe_Upah varchar(25) not null); CREATE TABLE db_hrd_dtkry=# \d+ upah; Table "public.upah" Column | Type | Modifiers | Storage | Stats target | Descr iption -----------+-----------------------+-----------+----------+--------------+------ ------- kode_upah | character(5) | not null | extended | | tipe_upah | character varying(25) | not null | extended | | Indexes: "upah_pkey" PRIMARY KEY, btree (kode_upah) Has OIDs: no db_hrd_dtkry=#
Membuat table Agama.
db_hrd_dtkry=# create table Agama (Id_Agama Serial primary key, Agama varchar(25) not null); CREATE TABLE
db_hrd_dtkry=# \d+ agama; Table "public.agama" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------+--------------------------------------------- -------------+----------+--------------+------------- id_agama | integer | not null default nextval('agama_id_agama_seq '::regclass) | plain | | agama | character varying(25) | not null | extended | | Indexes: "agama_pkey" PRIMARY KEY, btree (id_agama) Has OIDs: no
Membuat tabel Grade.
db_hrd_dtkry=# create table Grade (Kode_Grade char(5) primary key, Nama_Grade varchar(50) not null); CREATE TABLE db_hrd_dtkry=# \d+ grade; Table "public.grade" Column | Type | Modifiers | Storage | Stats target | Description ------------+--------------+-----------+----------+--------------+------------- kode_grade | character(5) | not null | extended | | nama_grade | character varying(50) | not null | extended | | Indexes: "grade_pkey" PRIMARY KEY, btree (kode_grade) Has OIDs: no
Membuat tabel Level.
db_hrd_dtkry=# create table Level (Kode_Level char(5) primary key, Nama_Level varchar (50) not null); CREATE TABLE db_hrd_dtkry=# \d+ level Table "public.level" Column | Type | Modifiers | Storage | Stats target | Desc ription ------------+-----------------------+-----------+----------+--------------+----- -------- kode_level | character(5) | not null | extended | | nama_level | character varying(50) | not null | extended | | Indexes: "level_pkey" PRIMARY KEY, btree (kode_level) Has OIDs: no
Membuat table Status_Pajak.
db_hrd_dtkry=# create table Status_Pajak (Status_Pajak char(3) primary key, keterangan text); CREATE TABLE
db_hrd_dtkry=# \d+ status_pajak Table "public.status_pajak" Column | Type | Modifiers | Storage | Stats target | Description --------------+--------------+-----------+----------+--------------+------------ - status_pajak | character(3) | not null | extended | | keterangan | text | | extended | | Indexes: "status_pajak_pkey" PRIMARY KEY, btree (status_pajak) Has OIDs: no
Membuat Type Enum untuk digunakan pada tabel karyawan.
db_hrd_dtkry=# create type jk as enum('L','P'); CREATE TYPE
db_hrd_dtkry=# create type snk as enum ('Lajang','Menikah','Duda','Janda'); CREATE TYPE
db_hrd_dtkry=# create type wn as enum ('WNI','WNA'); CREATE TYPE
db_hrd_dtkry=# create type gd as enum ('A','B','O','A+','B+'); CREATE TYPE db_hrd_dtkry=# \dT List of data types Schema | Name | Description --------+------+------------- public | gd | public | jk | public | snk | public | wn | (4 rows) db_hrd_dtkry=# \dT+ gd List of data types Schema | Name | Internal name | Size | Elements | Access privileges | Descripti on --------+------+---------------+------+----------+-------------------+---------- --- public | gd | gd | 4 | A +| | | | | | B +| | | | | | O +| | | | | | A+ +| | | | | | B+ | | (1 row)
Membuat tabel Karyawan.
db_hrd_dtkry=# create table karyawan (NIK char(10) primary key, Nama varchar(50) not null, Nick_Name varchar(10) not null, Tempat_Lahir varchar(100) not null, Tanggal_Lahir date, Jenis_Kelamin jk, Status_Nikah snk, Kewarganegaraan wn, Golongan_Darah gd, Asuransi varchar(50) not null, Jamsostek varchar(50), NPWP char(50), Kode_Status_Pekerja char(5) not null references status_pekerja (kode_status_pekerja) on delete cascade on update cascade, Kode_Upah char(5) not null references upah (kode_upah) on delete cascade on update cascade, id_agama integer not null references agama (id_agama) on delete cascade on update cascade, Kode_Grade char(2) not null references grade (kode_grade) on delete cascade on update cascade, Kode_Level char(5) not null references level (kode_level) on delete cascade on update cascade, Status_Pajak char(3) not null references status_pajak (status_pajak) on delete cascade on update cascade); CREATE TABLE
db_hrd_dtkry=# \d+ karyawan Table "public.karyawan" Column | Type | Modifiers | Storage | Stats tar get | Description ---------------------+------------------------+-----------+----------+---------- ----+------------- nik | character(10) | not null | extended | | nama | character varying(50) | not null | extended | | nick_name | character varying(10) | not null | extended | | tempat_lahir | character varying(100) | not null | extended | | tanggal_lahir | date | | plain | | jenis_kelamin | jk | | plain | | status_nikah | snk | | plain | | kewarganegaraan | wn | | plain | | golongan_darah | gd | | plain | | asuransi | character varying(50) | not null | extended | | jamsostek | character varying(50) | | extended | | npwp | character(50) | | extended | | kode_status_pekerja | character(5) | not null | extended | | kode_upah | character(5) | not null | extended | | id_agama | integer | not null | plain | | kode_grade | character(2) | not null | extended | | kode_level | character(5) | not null | extended | | status_pajak | character(3) | not null | extended | | Indexes: "karyawan_pkey" PRIMARY KEY, btree (nik) Foreign-key constraints: "karyawan_id_agama_fkey" FOREIGN KEY (id_agama) REFERENCES agama(id_agama) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_kode_grade_fkey" FOREIGN KEY (kode_grade) REFERENCES grade(kode_grade) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_kode_level_fkey" FOREIGN KEY (kode_level) REFERENCES level(kode_level) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_kode_status_pekerja_fkey" FOREIGN KEY (kode_status_pekerja) REFERENCES status_pekerja(kode_status_pekerja) ON UPDATE CASCADE ON DELETE CA SCADE "karyawan_kode_upah_fkey" FOREIGN KEY (kode_upah) REFERENCES upah(kode_upah) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_status_pajak_fkey" FOREIGN KEY (status_pajak) REFERENCES status_pajak(status_pajak) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Karyawan_LokasiKerja.
db_hrd_dtkry=# create table Karyawan_LokasiKerja (kode_lokasi char(5) not null references lokasi_kerja (kode_lokasi) on delete cascade on update cascade, NIK char(10) not null references karyawan (nik) on delete cascade on update cascade); CREATE TABLE
db_hrd_dtkry=# \d+ karyawan_lokasikerja Table "public.karyawan_lokasikerja" Column | Type | Modifiers | Storage | Stats target | Description -------------+---------------+-----------+----------+--------------+------------- kode_lokasi | character(5) | not null | extended | | nik | character(10) | not null | extended | | Foreign-key constraints: "karyawan_lokasikerja_kode_lokasi_fkey" FOREIGN KEY (kode_lokasi) REFERENCES lokasi_kerja(kode_lokasi) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_lokasikerja_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Karyawan_Departemen.
db_hrd_dtkry=# create table Karyawan_Departemen (Kode_Departemen char(5) not null references departemen (kode_departemen) on delete cascade on update cascade, nik char(10) not null references karyawan (nik) on delete cascade on update cascade); CREATE TABLE db_hrd_dtkry=# \d+ karyawan_departemen Table "public.karyawan_departemen" on update cascade, n Column | Type | Modifiers | Storage | Stats target | Description -----------------+---------------+-----------+----------+--------------+------------- kode_departemen | character(5) | not null | extended | | nik | character(10) | not null | extended | | Foreign-key constraints: "karyawan_departemen_kode_departemen_fkey" FOREIGN KEY (kode_departemen) REFERENCES departemen(kode_departemen) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_departemen_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Karyawan_Jabatan.
db_hrd_dtkry=# create table Karyawan_Jabatan (Kode_Jabatan char(5) not null references jabatan (kode_jabatan) on delete cascade on update cascade, nik char(10) not null references karyawan (nik) on delete cascade on update cascade); CREATE TABLE db_hrd_dtkry=# \d+ karyawan_jabatan Table "public.karyawan_jabatan" Column | Type | Modifiers | Storage | Stats target | Description --------------+---------------+-----------+----------+--------------+------------- kode_jabatan | character(5) | not null | extended | | nik | character(10) | not null | extended | | Foreign-key constraints: "karyawan_jabatan_kode_jabatan_fkey" FOREIGN KEY (kode_jabatan) REFERENCES jabatan(kode_jabatan) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_jabatan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Ukuran_Karyawan.
db_hrd_dtkry=# create table Ukuran_Pakaian (NIK char(10) not null primary key references karyawan (nik) on delete cascade on update cascade, ukuran_baju char(3) not null, ukuran_celana char(3) not null, ukuran_sepatu char(2) not null); CREATE TABLE
db_hrd_dtkry=# \d+ Ukuran_Pakaian; Table "public.ukuran_pakaian" Column | Type | Modifiers | Storage | Stats target | Descripti on ---------------+---------------+-----------+----------+--------------+---------- --- nik | character(10) | not null | extended | | ukuran_baju | character(3) | not null | extended | | ukuran_celana | character(3) | not null | extended | | ukuran_sepatu | character(2) | not null | extended | | Indexes: "ukuran_pakaian_pkey" PRIMARY KEY, btree (nik) Foreign-key constraints: "ukuran_pakaian_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDA TE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Pendidikan.
db_hrd_dtkry=# create type fnf as enum ('Formal','Non Formal'); CREATE TYPE
db_hrd_dtkry=# create table Pendidikan (id_pendidikan serial primary key, jenjang_pendidikan varchar(3) not null, lembaga varchar(50) not null, jurusan varchar(50) not null, tahun_masuk date not null, tahun_lulus date not null, jenis_pendidikan fnf, nik char(10) not null references karyawan(nik)); CREATE TABLE
db_hrd_dtkry=# \d+ pendidikan; Table "public.pe ndidikan" Column | Type | Modifi ers | Storage | Stats target | Description --------------------+-----------------------+----------------------------------- ---------------------------------+----------+--------------+------------- id_pendidikan | integer | not null default nextval('pendidik an_id_pendidikan_seq'::regclass) | plain | | jenjang_pendidikan | character varying(3) | not null | extended | | lembaga | character varying(50) | not null | extended | | jurusan | character varying(50) | not null | extended | | tahun_masuk | date | not null | plain | | tahun_lulus | date | not null | plain | | jenis_pendidikan | fnf | | plain | | nik | character(10) | not null | extended | | Indexes: "pendidikan_pkey" PRIMARY KEY, btree (id_pendidikan) Foreign-key constraints: "pendidikan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) Has OIDs: no
Membuat tabel Identitas.
db_hrd_dtkry=# create table identitas (nomor_identitas char(30) primary key, jenis_identitas char(5) not null, masa_berlaku date not null, nik char(10) not null references karyawan (nik) on update cascade on delete cascade); CREATE TABLE
db_hrd_dtkry=# \d+ identitas Table "public.identitas" Column | Type | Modifiers | Storage | Stats target | Description -----------------+---------------+-----------+----------+--------------+------------- nomor_identitas | character(30) | not null | extended | | jenis_identitas | character(5) | not null | extended | | masa_berlaku | date | not null | plain | | nik | character(10) | not null | extended | | Indexes: "identitas_pkey" PRIMARY KEY, btree (nomor_identitas) Foreign-key constraints: "identitas_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Rekruitment.
db_hrd_dtkry=# create table Rekruitment (nik char(10) primary key references karyawan(nik) on update cascade on delete cascade, hire_date date not null, start_date date not null, end_date date); CREATE TABLE
db_hrd_dtkry=# \d+ rekruitment Table "public.rekruitment" Column | Type | Modifiers | Storage | Stats target | Description ------------+---------------+-----------+----------+--------------+------------- nik | character(10) | not null | extended | | hire_date | date | not null | plain | | start_date | date | not null | plain | | end_date | date | | plain | | Indexes: "rekruitment_pkey" PRIMARY KEY, btree (nik) Foreign-key constraints: "rekruitment_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Alamat.
db_hrd_dtkry=# create table Alamat (id_alamat serial primary key, jalan varchar(100) not null, kelurahan varchar(50) not null, kecamatan varchar(50) not null, kota_Kab varchar(50) not null, kode_pos integer not null, provinsi varchar(50) not null, negara varchar(50) not null, nik char(10) not null references karyawan(nik) on update cascade on delete cascade); CREATE TABLE
db_hrd_dtkry=# \d+ alamat Table "public.alamat" Column | Type | Modifiers | Storage | Stat s target | Description -----------+------------------------+------------------------------------------------------------+----------+----- ---------+------------- id_alamat | integer | not null default nextval('alamat_id_alamat_seq'::regclass) | plain | | jalan | character varying(100) | not null | extended | | kelurahan | character varying(50) | not null | extended | | kecamatan | character varying(50) | not null | extended | | kota_kab | character varying(50) | not null | extended | | kode_pos | integer | not null | plain | | provinsi | character varying(50) | not null | extended | | negara | character varying(50) | not null | extended | | nik | character(10) | not null | extended | | Indexes: "alamat_pkey" PRIMARY KEY, btree (id_alamat) Foreign-key constraints: "alamat_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
db_hrd_dtkry=# alter table alamat add constraint constraint_name unique (jalan); ALTER TABLE
db_hrd_dtkry=# \d+ alamat Table "public.alamat" Column | Type | Modifiers | Storage | Stat s target | Description -----------+------------------------+------------------------------------------------------------+----------+----- ---------+------------- id_alamat | integer | not null default nextval('alamat_id_alamat_seq'::regclass) | plain | | jalan | character varying(100) | not null | extended | | kelurahan | character varying(50) | not null | extended | | kecamatan | character varying(50) | not null | extended | | kota_kab | character varying(50) | not null | extended | | kode_pos | integer | not null | plain | | provinsi | character varying(50) | not null | extended | | negara | character varying(50) | not null | extended | | nik | character(10) | not null | extended | | Indexes: "alamat_pkey" PRIMARY KEY, btree (id_alamat) "constraint_name" UNIQUE CONSTRAINT, btree (jalan) Foreign-key constraints: "alamat_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Data Keluarga.
db_hrd_dtkry=# create table data_keluarga (id_keluarga serial primary key, hubungan varchar(15) not null unique, nama varchar(25) not null, alamat varchar(200) not null, nik char(10) not null references karyawan(nik) on update cascade on delete cascade); CREATE TABLE
db_hrd_dtkry=# \d+ data_keluarga Table "public.data_keluarga" Column | Type | Modifiers | Stor age | Stats target | Description -------------+------------------------+---------------------------------------------------------------------+----- -----+--------------+------------- id_keluarga | integer | not null default nextval('data_keluarga_id_keluarga_seq'::regclass) | plai n | | hubungan | character varying(15) | not null | exte nded | | nama | character varying(25) | not null | exte nded | | alamat | character varying(200) | not null | exte nded | | nik | character(10) | not null | exte nded | | Indexes: "data_keluarga_pkey" PRIMARY KEY, btree (id_keluarga) "data_keluarga_hubungan_key" UNIQUE CONSTRAINT, btree (hubungan) Foreign-key constraints: "data_keluarga_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Informasi Kontak.
db_hrd_dtkry=# create table informasi_kontak (nomor_kontak char(20) not null unique, jenis_kontak varchar(20) not null, nik char(10) not null references karyawan(nik) on update cascade on delete cascade); CREATE TABLE
db_hrd_dtkry=# \d+ informasi_kontak Table "public.informasi_kontak" Column | Type | Modifiers | Storage | Stats target | Description --------------+-----------------------+-----------+----------+--------------+------------- nomor_kontak | character(20) | not null | extended | | jenis_kontak | character varying(20) | not null | extended | | nik | character(10) | not null | extended | | Indexes: "informasi_kontak_nomor_kontak_key" UNIQUE CONSTRAINT, btree (nomor_kontak) Foreign-key constraints: "informasi_kontak_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
db_hrd_dtkry=# alter table informasi_kontak add column id_inform_kontak serial primary key; ALTER TABLE
db_hrd_dtkry=# \d+ informasi_kontak Table "public.informasi_kontak" Column | Type | Modifiers | Storage | Stats target | Description ------------------+-----------------------+----------------------------------------------------------------------- ------+----------+--------------+------------- nomor_kontak | character(20) | not null | extended | | jenis_kontak | character varying(20) | not null | extended | | nik | character(10) | not null | extended | | id_inform_kontak | integer | not null default nextval('informasi_kontak_id_inform_kontak_seq'::regc lass) | plain | | Indexes: "informasi_kontak_pkey" PRIMARY KEY, btree (id_inform_kontak) "informasi_kontak_nomor_kontak_key" UNIQUE CONSTRAINT, btree (nomor_kontak) Foreign-key constraints: "informasi_kontak_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Informasi Email.
db_hrd_dtkry=# create table informasi_email (id_inform_email serial primary key, alamat_email varchar(30) not null unique, jenis_email varchar(15) not null, nik char(10) not null references karyawan(nik) on update cascade on delete cascade); CREATE TABLE
db_hrd_dtkry=# \d+ informasi_email Table "public.informasi_email" Column | Type | Modifiers | Storage | Stats target | Description -----------------+-----------------------+------------------------------------------------------------------------ ---+----------+--------------+------------- id_inform_email | integer | not null default nextval('informasi_email_id_inform_email_seq'::regclas s) | plain | | alamat_email | character varying(30) | not null | extended | | jenis_email | character varying(15) | not null | extended | | nik | character(10) | not null | extended | | Indexes: "informasi_email_pkey" PRIMARY KEY, btree (id_inform_email) "informasi_email_alamat_email_key" UNIQUE CONSTRAINT, btree (alamat_email) Foreign-key constraints: "informasi_email_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Membuat tabel Informasi Resign.
db_hrd_dtkry=# create type rsg as enum ('Yes', 'No'); CREATE TYPE
db_hrd_dtkry=# create table informasi_resign (nik char(10) primary key references karyawan(nik) on update cascade on delete cascade, status_resign rsg not null, tanggal_resign date not null, alasan_resign text not null); CREATE TABLE
db_hrd_dtkry=# \d+ informasi_resign Table "public.informasi_resign" Column | Type | Modifiers | Storage | Stats target | Description ----------------+---------------+-----------+----------+--------------+------------- nik | character(10) | not null | extended | | status_resign | rsg | not null | plain | | tanggal_resign | date | not null | plain | | alasan_resign | text | not null | extended | | Indexes: "informasi_resign_pkey" PRIMARY KEY, btree (nik) Foreign-key constraints: "informasi_resign_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE Has OIDs: no
Memperbaiki struktur tabel untuk menjaga Referential Integrity antar tabel. Yang pertama kita akan memodifikasi tabel karyawan agar beberapa kolom boleh bernilai "null" dan beberapa Foreign Key di set ke "On Delete Set Null". Untuk memperbaiki struktur tabel karyawan ini, perhatikan tahapan-tahapan berikut ini.
db_hrd_dtkry=# \d+ karyawan Table "public.karyawan" Column | Type | Modifiers | Storage | Stats target | Description ---------------------+------------------------+-----------+----------+--------------+------------- nik | character(10) | not null | extended | | nama | character varying(50) | not null | extended | | nick_name | character varying(10) | not null | extended | | tempat_lahir | character varying(100) | not null | extended | | tanggal_lahir | date | | plain | | jenis_kelamin | jk | | plain | | status_nikah | snk | | plain | | kewarganegaraan | wn | | plain | | golongan_darah | gd | | plain | | asuransi | character varying(50) | not null | extended | | jamsostek | character varying(50) | | extended | | npwp | character(50) | | extended | | kode_status_pekerja | character(5) | not null | extended | | kode_upah | character(5) | not null | extended | | id_agama | integer | not null | plain | | kode_grade | character(2) | not null | extended | | kode_level | character(5) | not null | extended | | status_pajak | character(3) | not null | extended | | Indexes: "karyawan_pkey" PRIMARY KEY, btree (nik) Foreign-key constraints: "karyawan_id_agama_fkey" FOREIGN KEY (id_agama) REFERENCES agama(id_agama) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_kode_grade_fkey" FOREIGN KEY (kode_grade) REFERENCES grade(kode_grade) ON UPDATE CASCADE ON DELETE C ASCADE "karyawan_kode_level_fkey" FOREIGN KEY (kode_level) REFERENCES level(kode_level) ON UPDATE CASCADE ON DELETE C ASCADE "karyawan_kode_status_pekerja_fkey" FOREIGN KEY (kode_status_pekerja) REFERENCES status_pekerja(kode_status_pe kerja) ON UPDATE CASCADE ON DELETE CASCADE "karyawan_kode_upah_fkey" FOREIGN KEY (kode_upah) REFERENCES upah(kode_upah) ON UPDATE CASCADE ON DELETE CASCA DE "karyawan_status_pajak_fkey" FOREIGN KEY (status_pajak) REFERENCES status_pajak(status_pajak) ON UPDATE CASCAD E ON DELETE CASCADE Referenced by: TABLE "alamat" CONSTRAINT "alamat_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DE LETE CASCADE TABLE "data_keluarga" CONSTRAINT "data_keluarga_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE TABLE "identitas" CONSTRAINT "identitas_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE TABLE "informasi_email" CONSTRAINT "informasi_email_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UP DATE CASCADE ON DELETE CASCADE TABLE "informasi_kontak" CONSTRAINT "informasi_kontak_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE TABLE "informasi_resign" CONSTRAINT "informasi_resign_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE TABLE "karyawan_departemen" CONSTRAINT "karyawan_departemen_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(ni k) ON UPDATE CASCADE ON DELETE CASCADE TABLE "karyawan_jabatan" CONSTRAINT "karyawan_jabatan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE TABLE "karyawan_lokasikerja" CONSTRAINT "karyawan_lokasikerja_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan( nik) ON UPDATE CASCADE ON DELETE CASCADE TABLE "pendidikan" CONSTRAINT "pendidikan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) TABLE "rekruitment" CONSTRAINT "rekruitment_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CAS CADE ON DELETE CASCADE TABLE "ukuran_pakaian" CONSTRAINT "ukuran_pakaian_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDA TE CASCADE ON DELETE CASCADE Has OIDs: no
db_hrd_dtkry=# alter table karyawan alter column kode_status_pekerja drop not null; ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column kode_upah drop not null; ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column id_agama drop not null; ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column kode_grade drop not null; ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column kode_level drop not null; ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column status_pajak drop not null; ALTER TABLE
db_hrd_dtkry=# \d+ karyawan Table "public.karyawan" Column | Type | Modifiers | Storage | Stats target | Description ---------------------+------------------------+-----------+----------+--------------+------------- nik | character(10) | not null | extended | | nama | character varying(50) | not null | extended | | nick_name | character varying(10) | not null | extended | | tempat_lahir | character varying(100) | not null | extended | | tanggal_lahir | date | | plain | | jenis_kelamin | jk | | plain | | status_nikah | snk | | plain | | kewarganegaraan | wn | | plain | | golongan_darah | gd | | plain | | asuransi | character varying(50) | not null | extended | | jamsostek | character varying(50) | | extended | | npwp | character(50) | | extended | | kode_status_pekerja | character(5) | | extended | | kode_upah | character(5) | | extended | | id_agama | integer | | plain | | kode_grade | character(2) | | extended | | kode_level | character(5) | | extended | | status_pajak | character(3) | | extended | | Indexes: "karyawan_pkey" PRIMARY KEY, btree (nik)
.
.
.
db_hrd_dtkry=# alter table karyawan drop constraint karyawan_kode_status_pekerja_fkey;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan drop constraint karyawan_kode_upah_fkey;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan drop constraint karyawan_id_agama_fkey;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan drop constraint karyawan_kode_grade_fkey;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan drop constraint karyawan_kode_level_fkey;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan drop constraint karyawan_status_pajak_fkey;
ALTER TABLE
db_hrd_dtkry=# \d+ karyawan
Table "public.karyawan"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+------------------------+-----------+----------+--------------+-------------
nik | character(10) | not null | extended | |
nama | character varying(50) | not null | extended | |
nick_name | character varying(10) | not null | extended | |
tempat_lahir | character varying(100) | not null | extended | |
tanggal_lahir | date | | plain | |
jenis_kelamin | jk | | plain | |
status_nikah | snk | | plain | |
kewarganegaraan | wn | | plain | |
golongan_darah | gd | | plain | |
asuransi | character varying(50) | not null | extended | |
jamsostek | character varying(50) | | extended | |
npwp | character(50) | | extended | |
kode_status_pekerja | character(5) | | extended | |
kode_upah | character(5) | | extended | |
id_agama | integer | | plain | |
kode_grade | character(2) | | extended | |
kode_level | character(5) | | extended | |
status_pajak | character(3) | | extended | |
Indexes:
"karyawan_pkey" PRIMARY KEY, btree (nik)
Referenced by:
TABLE "alamat" CONSTRAINT "alamat_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DE
LETE CASCADE
TABLE "data_keluarga" CONSTRAINT "data_keluarga_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE
CASCADE ON DELETE CASCADE
TABLE "identitas" CONSTRAINT "identitas_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE
ON DELETE CASCADE
TABLE "informasi_email" CONSTRAINT "informasi_email_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UP
DATE CASCADE ON DELETE CASCADE
TABLE "informasi_kontak" CONSTRAINT "informasi_kontak_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON
UPDATE CASCADE ON DELETE CASCADE
TABLE "informasi_resign" CONSTRAINT "informasi_resign_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON
UPDATE CASCADE ON DELETE CASCADE
TABLE "karyawan_departemen" CONSTRAINT "karyawan_departemen_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(ni
k) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "karyawan_jabatan" CONSTRAINT "karyawan_jabatan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON
UPDATE CASCADE ON DELETE CASCADE
TABLE "karyawan_lokasikerja" CONSTRAINT "karyawan_lokasikerja_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(
nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "pendidikan" CONSTRAINT "pendidikan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik)
TABLE "rekruitment" CONSTRAINT "rekruitment_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CAS
CADE ON DELETE CASCADE
TABLE "ukuran_pakaian" CONSTRAINT "ukuran_pakaian_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDA
TE CASCADE ON DELETE CASCADE
Has OIDs: no
db_hrd_dtkry=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+---------
.
.
.
public | informasi_resign | table | havizul
public | jabatan | table | havizul
public | jenis_pekerja | table | havizul
public | karyawan | table | havizul
.
.
.
db_hrd_dtkry=# \d+ jenis_pekerja
Table "public.jenis_pekerja"
Column | Type | Modifiers | Storage | Stats target | Description
--------------------+-----------------------+-----------+----------+--------------+-------------
kode_jenis_pekerja | character(5) | not null | extended | |
jenis_pekerja | character varying(25) | not null | extended | |
Indexes:
"jenis_pekerja_pkey" PRIMARY KEY, btree (kode_jenis_pekerja)
Has OIDs: no
db_hrd_dtkry=# alter table karyawan add column kode_jenis_pekerja char(5) references jenis_pekerja (kode_jenis_pekerja) on update cascade on delete set null;
ALTER TABLE
db_hrd_dtkry=# \d+ karyawan
Table "public.karyawan"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+------------------------+-----------+----------+--------------+-------------
nik | character(10) | not null | extended | |
nama | character varying(50) | not null | extended | |
nick_name | character varying(10) | not null | extended | |
tempat_lahir | character varying(100) | not null | extended | |
tanggal_lahir | date | | plain | |
jenis_kelamin | jk | | plain | |
status_nikah | snk | | plain | |
kewarganegaraan | wn | | plain | |
golongan_darah | gd | | plain | |
asuransi | character varying(50) | not null | extended | |
jamsostek | character varying(50) | | extended | |
npwp | character(50) | | extended | |
kode_status_pekerja | character(5) | | extended | |
kode_upah | character(5) | | extended | |
id_agama | integer | | plain | |
kode_grade | character(2) | | extended | |
kode_level | character(5) | | extended | |
status_pajak | character(3) | | extended | |
kode_jenis_pekerja | character(5) | | extended | |
Indexes:
"karyawan_pkey" PRIMARY KEY, btree (nik)
Foreign-key constraints:
"karyawan_kode_jenis_pekerja_fkey" FOREIGN KEY (kode_jenis_pekerja) REFERENCES jenis_pekerja(kode_jenis_pekerja) ON UPDATE CASCADE ON DELETE SET NU
LL
.
.
.
db_hrd_dtkry=# alter table karyawan add foreign key (kode_status_pekerja) references status_pekerja (kode_status_pekerja) on update cascade on delete set null;
ALTER TABLE
db_hrd_dtkry=# \d+ karyawan
Table "public.karyawan"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+------------------------+-----------+----------+--------------+-------------
nik | character(10) | not null | extended | |
nama | character varying(50) | not null | extended | |
nick_name | character varying(10) | not null | extended | |
tempat_lahir | character varying(100) | not null | extended | |
tanggal_lahir | date | | plain | |
jenis_kelamin | jk | | plain | |
status_nikah | snk | | plain | |
kewarganegaraan | wn | | plain | |
golongan_darah | gd | | plain | |
asuransi | character varying(50) | not null | extended | |
jamsostek | character varying(50) | | extended | |
npwp | character(50) | | extended | |
kode_status_pekerja | character(5) | | extended | |
kode_upah | character(5) | | extended | |
id_agama | integer | | plain | |
kode_grade | character(2) | | extended | |
kode_level | character(5) | | extended | |
status_pajak | character(3) | | extended | |
kode_jenis_pekerja | character(5) | | extended | |
Indexes:
"karyawan_pkey" PRIMARY KEY, btree (nik)
Foreign-key constraints:
"karyawan_kode_jenis_pekerja_fkey" FOREIGN KEY (kode_jenis_pekerja) REFERENCES jenis_pekerja(kode_jenis_pekerja) ON UPDATE CASCADE ON DELETE SET NU
LL
"karyawan_kode_status_pekerja_fkey" FOREIGN KEY (kode_status_pekerja) REFERENCES status_pekerja(kode_status_pekerja) ON UPDATE CASCADE ON DELETE SE
T NULL
.
.
.
db_hrd_dtkry=# alter table karyawan add foreign key (kode_upah) references upah (kode_upah) on update cascade on delete set null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan add foreign key (id_agama) references agama (id_agama) on update cascade on delete set null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan add foreign key (kode_grade) references grade (kode_grade) on update cascade on delete set null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan add foreign key (status_pajak) references status_pajak (status_pajak) on update cascade on delete set null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan add foreign key (kode_level) references level (kode_level) on update cascade on delete set null;
ALTER TABLE
db_hrd_dtkry=# \d+ karyawan
Table "public.karyawan"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+------------------------+-----------+----------+--------------+-------------
nik | character(10) | not null | extended | |
nama | character varying(50) | not null | extended | |
nick_name | character varying(10) | not null | extended | |
tempat_lahir | character varying(100) | not null | extended | |
tanggal_lahir | date | | plain | |
jenis_kelamin | jk | | plain | |
status_nikah | snk | | plain | |
kewarganegaraan | wn | | plain | |
golongan_darah | gd | | plain | |
asuransi | character varying(50) | not null | extended | |
jamsostek | character varying(50) | | extended | |
npwp | character(50) | | extended | |
kode_status_pekerja | character(5) | | extended | |
kode_upah | character(5) | | extended | |
id_agama | integer | | plain | |
kode_grade | character(2) | | extended | |
kode_level | character(5) | | extended | |
status_pajak | character(3) | | extended | |
kode_jenis_pekerja | character(5) | | extended | |
Indexes:
"karyawan_pkey" PRIMARY KEY, btree (nik)
Foreign-key constraints:
"karyawan_id_agama_fkey" FOREIGN KEY (id_agama) REFERENCES agama(id_agama) ON UPDATE CASCADE ON DELETE SET NULL
"karyawan_kode_grade_fkey" FOREIGN KEY (kode_grade) REFERENCES grade(kode_grade) ON UPDATE CASCADE ON DELETE SET NULL
"karyawan_kode_jenis_pekerja_fkey" FOREIGN KEY (kode_jenis_pekerja) REFERENCES jenis_pekerja(kode_jenis_pekerja) ON UPDATE CASCADE ON DELETE SET NU
LL
"karyawan_kode_level_fkey" FOREIGN KEY (kode_level) REFERENCES level(kode_level) ON UPDATE CASCADE ON DELETE SET NULL
"karyawan_kode_status_pekerja_fkey" FOREIGN KEY (kode_status_pekerja) REFERENCES status_pekerja(kode_status_pekerja) ON UPDATE CASCADE ON DELETE SE
T NULL
"karyawan_kode_upah_fkey" FOREIGN KEY (kode_upah) REFERENCES upah(kode_upah) ON UPDATE CASCADE ON DELETE SET NULL
"karyawan_status_pajak_fkey" FOREIGN KEY (status_pajak) REFERENCES status_pajak(status_pajak) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "alamat" CONSTRAINT "alamat_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "data_keluarga" CONSTRAINT "data_keluarga_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "identitas" CONSTRAINT "identitas_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "informasi_email" CONSTRAINT "informasi_email_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "informasi_kontak" CONSTRAINT "informasi_kontak_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "informasi_resign" CONSTRAINT "informasi_resign_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "karyawan_departemen" CONSTRAINT "karyawan_departemen_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCAD
E
TABLE "karyawan_jabatan" CONSTRAINT "karyawan_jabatan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "karyawan_lokasikerja" CONSTRAINT "karyawan_lokasikerja_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASC
ADE
TABLE "pendidikan" CONSTRAINT "pendidikan_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik)
TABLE "rekruitment" CONSTRAINT "rekruitment_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ukuran_pakaian" CONSTRAINT "ukuran_pakaian_nik_fkey" FOREIGN KEY (nik) REFERENCES karyawan(nik) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
db_hrd_dtkry=# alter table karyawan alter column asuransi drop not null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column tanggal_lahir set not null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column jenis_kelamin set not null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column status_nikah set not null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column kewarganegaraan set not null;
ALTER TABLE
db_hrd_dtkry=# alter table karyawan alter column golongan_darah set not null;
ALTER TABLE
db_hrd_dtkry=# \d+ karyawan
Table "public.karyawan"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+------------------------+-----------+----------+--------------+-------------
nik | character(10) | not null | extended | |
nama | character varying(50) | not null | extended | |
nick_name | character varying(10) | not null | extended | |
tempat_lahir | character varying(100) | not null | extended | |
tanggal_lahir | date | not null | plain | |
jenis_kelamin | jk | not null | plain | |
status_nikah | snk | not null | plain | |
kewarganegaraan | wn | not null | plain | |
golongan_darah | gd | not null | plain | |
asuransi | character varying(50) | | extended | |
jamsostek | character varying(50) | | extended | |
npwp | character(50) | | extended | |
kode_status_pekerja | character(5) | | extended | |
kode_upah | character(5) | | extended | |
id_agama | integer | | plain | |
kode_grade | character(2) | | extended | |
kode_level | character(5) | | extended | |
status_pajak | character(3) | | extended | |
kode_jenis_pekerja | character(5) | | extended | |
Indexes:
"karyawan_pkey" PRIMARY KEY, btree (nik)
Foreign-key constraints:
"karyawan_id_agama_fkey" FOREIGN KEY (id_agama) REFERENCES agama
.
.
.
db_hrd_dtkry=# \d+ lokasi_kerja
Table "public.lokasi_kerja"
Column | Type | Modifiers | Storage | Stats target | Description
-------------+-----------------------+-----------+----------+--------------+-------------
kode_lokasi | character(5) | not null | extended | |
lokasi | character varying(25) | | extended | |
Indexes:
"lokasi_kerja_pkey" PRIMARY KEY, btree (kode_lokasi)
Referenced by:
TABLE "karyawan_lokasikerja" CONSTRAINT "karyawan_lokasikerja_kode_lokasi_fkey" FOREIGN KEY (kode_lokasi) REFERENCES lokasi_kerja(kode_lokasi) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
db_hrd_dtkry=# \d+ departemen
Table "public.departemen"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+-----------------------+-----------+----------+--------------+-------------
kode_departemen | character(5) | not null | extended | |
nama_departemen | character varying(50) | | extended | |
Indexes:
"departemen_pkey" PRIMARY KEY, btree (kode_departemen)
Referenced by:
TABLE "karyawan_departemen" CONSTRAINT "karyawan_departemen_kode_departemen_fkey" FOREIGN KEY (kode_departemen) REFERENCES departemen(kode_departemen) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
db_hrd_dtkry=# \d+ jabatan
Table "public.jabatan"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+-----------------------+-----------+----------+--------------+-------------
kode_jabatan | character(5) | not null | extended | |
nama_jabatan | character varying(50) | | extended | |
Indexes:
"jabatan_pkey" PRIMARY KEY, btree (kode_jabatan)
Referenced by:
TABLE "karyawan_jabatan" CONSTRAINT "karyawan_jabatan_kode_jabatan_fkey" FOREIGN KEY (kode_jabatan) REFERENCES jabatan(kode_jabatan) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
db_hrd_dtkry=# alter table lokasi_kerja alter column lokasi set not null;
ALTER TABLE
db_hrd_dtkry=# alter table departemen alter column nama_departemen set not null;
ALTER TABLE
db_hrd_dtkry=# alter table jabatan alter column nama_jabatan set not null;
ALTER TABLE
Alhamdulillah, sampai disini selesai sudah proses membangun database Data Karyawan.
ciefirKme-ga-1981 Laura Maher link
BalasHapuspectlareschart