Pelatihan dan Sertifikasi Kompetensi Teknis Profesi Basis Data Programmer

Hari 2

Galeri

Pendahuluan

– Nur Hidayat

• Sejarah singkat basis data
• RDBMS (structure) vs NoSQL (unstructure), OLTP (transactional) vs OLAP (analytic)
• Desain basis data yang bagus
• Tips membuat desain basis data
  • menggunakan bahasa inggris (jamak semua atau tunggal semua)
  • tidak menambahkan awalan atau akhiran yang tidak diperlukan, misal tblSchool
  • sandi di-enkripsi
  • menggunakan tipe data integer untuk id
  • gunakan konstrain (batasan, misalnya: foreign key, check, not null, dll) untuk integritas data
  • gunakan indeks

Mendesain Basis Data yang Bagus

– Nur Hidayat

• Best Practice:
  memberi id sebagai surrogate key meskipun sudah ada nilai unik yang digunakan sebagai kunci primer yang terjadi secara natural, misalnya:
  meskipun sudah ada NIK, tetapi tetap dibuatkan idperson sebagai kuncinya yang biasanya auto_increment dan NIK dibuat sebagai indeks unik
• Membuat model konsep berupa entitas dan relasi antar entitas untuk memberi gambaran umum kepada pimpinan
  
• Membuat model logik dengan menambahkan atribut dan kunci primer untuk manajemen/supervisor
  
• Membuat model fisik dengan menentukan tipe data dan membuat tabel antara untuk relasi many-to-many yang akan digunakan untuk pengembang basis data
  

Praktik Dasar [Inventory]

– Nur Hidayat

--praktik 1
--uji create, alter, drop

-- membuat tabel
create table products (productid integer primary key, productname varchar(30) not null, productdesc varchar(100));

-- mengubah tabel
alter table products add column specification text;

-- menghapus tabel
drop table products;

--praktik 2

-- membuat tabel tipe item
CREATE TABLE IF NOT EXISTS public.item_type
(
    id smallserial,
    code character varying(59),
    name character varying(100),
    PRIMARY KEY (id),
    CONSTRAINT item_type_ukey UNIQUE (code)
);

-- membuat tabel tipe transaksi
CREATE TABLE IF NOT EXISTS public.transaction_type
(
    id smallserial,
    code character varying(59),
    name character varying(100),
    PRIMARY KEY (id),
    CONSTRAINT transaction_type_ukey UNIQUE (code)
);

-- membuat tabel lokasi
CREATE TABLE IF NOT EXISTS public.location
(
    id serial NOT NULL,
    code character varying(59),
    name character varying(100),
    CONSTRAINT location_ukey UNIQUE (code),
    PRIMARY KEY (id)
);

-- membuat tabel gudang
-- relasi dengan tabel lokasi.id
CREATE TABLE IF NOT EXISTS public.warehouse
(
    id serial,
    code character varying(59) NOT NULL,
    name character varying(100),
    location_id integer,
    PRIMARY KEY (id),
    CONSTRAINT warehouse_ukey UNIQUE (code),
    CONSTRAINT warehouse_fk_location FOREIGN KEY (location_id)
        REFERENCES public.locations (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

-- membuat tabel item
-- relasi dengan tabel item_type.id
CREATE TABLE IF NOT EXISTS public.item
(
    id serial,
    code character varying(59) NOT NULL,
    name character varying(100),
    specification text,
    type_id smallint,
    PRIMARY KEY (id),
    CONSTRAINT item_ukey UNIQUE (code),
    CONSTRAINT item_fk_item_type FOREIGN KEY (type_id)
        REFERENCES public.item_type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

-- membuat tabel penyimpanan item
-- relasi dengan tabel item.id dan warehouse.id
CREATE TABLE IF NOT EXISTS public.item_inventory
(
    id serial,
    item_id integer,
    warehouse_id integer,
    quantity decimal,
    PRIMARY KEY (id),
    CONSTRAINT item_inventory_fk_item FOREIGN KEY (item_id)
        REFERENCES public.item (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT item_inventory_fk_warehouse FOREIGN KEY (warehouse_id)
        REFERENCES public.warehouse (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);


-- membuat tabel detail transaksi
-- relasi dengan tabel item.id dan transaction.id, tetapi transaction.id belum dibuatkan FK karena tabelnya belum dibuat
CREATE TABLE IF NOT EXISTS public.transaction_detail
(
    id serial,
    transaction_id integer,
    item_id integer,
    quantity decimal,
    unit_price decimal,
    PRIMARY KEY (id),
    CONSTRAINT transaction_detail_fk_item FOREIGN KEY (item_id)
        REFERENCES public.item (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);


-- membuat tabel transaksi
-- relasi dengan tabel warehouse.id dan transaction_type.id
CREATE TABLE IF NOT EXISTS public.transaction
(
    id serial,
    code character varying(59),
    date date,
    remarks text,
    warehouse_id integer,
    type_id smallint,
    PRIMARY KEY (id),
    CONSTRAINT transaction_fk_warehouse FOREIGN KEY (warehouse_id)
        REFERENCES public.warehouse (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT transaction_fk_transaction_type FOREIGN KEY (type_id)
        REFERENCES public.transaction_type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

-- membuat relasi tabel transaksi detail dengan tabel detail yang tadi tertunda belum direlasikan
ALTER TABLE public.transaction_detail
    ADD CONSTRAINT transaction_detail_fk_transaction FOREIGN KEY (transaction_id)
    REFERENCES public.transaction (id)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

-- membuat index tanggal
create index transaction_idx_date on transaction(date);

DML [Inventory]

– Nur Hidayat

-- DML Insert
insert into location (code, name) values ('ID', 'Indonesia');
insert into location (code, name) values ('US', 'United States of America');
insert into location (code, name) values ('GB', 'Great Britain');
insert into location (code, name) values ('UK', 'United Kingdom');

-- DML Delete
delete from location where code='GB';

-- DML Update
update location set name='United States' where code='US';

-- concatenate dan alias kolom
select id, 'Location: ' || name || ', code: ' || code as location_detail from location