Mengubah ERD ke Tabel: Relasi Many to Many dan Tabel Penghubung

ERD diubah ke tabel dengan empat aturan: setiap entitas menjadi tabel, relasi 1:N diwujudkan dengan foreign key di tabel sisi N, relasi 1:1 biasanya digabung ke satu tabel, dan relasi many to many (M:N) selalu dipecah menjadi tabel ketiga yang disebut tabel penghubung. Bagian yang paling sering salah bukan transformasinya, melainkan membaca kardinalitas dari kalimat soal. Artikel ini mengerjakan satu studi kasus perpustakaan kampus dari aturan bisnis, ke ERD, sampai perintah CREATE TABLE.

#Aturan bisnis perpustakaan kampus yang akan dimodelkan

Soal UTS basis data biasanya memberi paragraf seperti ini:

1. Setiap buku diterbitkan oleh satu penerbit. Satu penerbit bisa menerbitkan banyak buku.
2. Satu buku bisa ditulis oleh beberapa penulis, dan satu penulis bisa menulis banyak buku. Urutan nama penulis di sampul perlu dicatat.
3. Setiap anggota memegang tepat satu kartu perpustakaan, dan satu kartu hanya milik satu anggota.
4. Anggota boleh meminjam banyak buku. Dari waktu ke waktu, satu buku juga dipinjam oleh banyak anggota. Tanggal pinjam dan tanggal kembali dicatat untuk setiap peminjaman.

Dari empat kalimat itu kita akan mendapat lima entitas (penerbit, buku, penulis, anggota, kartu) dan empat relasi.

#Bagaimana membaca kardinalitas dari kalimat soal?

Kardinalitas menjawab pertanyaan "satu baris di sini berpasangan dengan paling banyak berapa baris di sana?". Pertanyaan itu harus diajukan dari dua arah. Satu arah saja hampir selalu menghasilkan 1:N yang salah.

Perhatikan frasa "dari waktu ke waktu" di aturan keempat. Pada satu saat tertentu, satu buku fisik hanya dipegang satu orang. Tetapi yang dimodelkan adalah riwayat peminjaman selama bertahun-tahun, dan dalam riwayat itu satu buku punya banyak peminjam. Kalau frasa itu terlewat, peminjaman terbaca 1:N dan desainnya hanya bisa menyimpan peminjam terakhir.

#Seperti apa ERD-nya?

Diagram di atas memakai notasi Chen: persegi untuk entitas, belah ketupat untuk relasi, dan angka atau huruf di ujung garis untuk kardinalitas. Dua relasi M:N punya atribut sendiri (urutan penulis, tanggal pinjam dan kembali). Atribut seperti ini milik relasinya, bukan milik buku atau anggota. Itu petunjuk awal bahwa relasinya nanti menjadi tabel.

Kalau dosenmu memakai notasi crow's foot, isinya sama. Ujung "banyak" digambar sebagai kaki gagak, ujung "satu" sebagai garis tegak.

#Aturan mengubah ERD ke tabel

Kerjakan dengan urutan ini, karena tabel yang dirujuk harus ada lebih dulu:

1. Entitas menjadi tabel. Identifier entitas menjadi kunci primer. PENERBIT, BUKU, PENULIS, dan ANGGOTA langsung menjadi tabel.
2. Relasi 1:N tidak menjadi tabel. Kunci primer sisi "1" disalin ke tabel sisi "N" sebagai foreign key. Penerbit 1, buku N, jadi kolom id_penerbit masuk ke tabel BUKU. Kebalikannya tidak mungkin: tabel PENERBIT tidak bisa menyimpan banyak kode buku di satu kolom.
3. Relasi 1:1 digabung kalau kedua sisinya wajib. Setiap anggota pasti punya kartu dan setiap kartu pasti punya anggota, dan kartu tidak punya relasi lain. Kolom kartu (no_kartu, kartu_berlaku_sampai) cukup ditambahkan ke tabel ANGGOTA.
4. Relasi M:N menjadi tabel baru. Isinya kunci primer kedua entitas sebagai foreign key, ditambah atribut milik relasi. "Ditulis oleh" menjadi BUKU_PENULIS, "meminjam" menjadi PEMINJAMAN.

Kalau 1:1 punya satu sisi opsional, misalnya mahasiswa baru boleh belum punya kartu, KARTU tetap tabel sendiri. Foreign key no_anggota ditaruh di KARTU (sisi yang partisipasinya wajib, karena setiap kartu pasti punya pemilik) dengan batasan UNIQUE. UNIQUE itulah yang membedakannya dari 1:N. Tanpa UNIQUE, satu anggota bisa punya banyak kartu.

#Kenapa relasi many to many wajib tabel ketiga?

Coba simpan peminjaman tanpa tabel baru. Misalkan semester ini Rina meminjam 3 buku, dan buku Pengantar Basis Data dipinjam bergantian oleh 4 anggota.

• Kolom kode_buku di tabel ANGGOTA: Rina butuh 3 nilai di satu sel. Itu melanggar 1NF, dan menambah kolom buku1, buku2, buku3 hanya memindahkan masalahnya.
• Kolom no_anggota di tabel BUKU: Pengantar Basis Data butuh 4 nilai. Masalah yang sama dari arah lain.
• Tanggal pinjam juga tidak punya tempat. Tanggal itu bukan sifat Rina dan bukan sifat buku, tetapi sifat pasangan Rina dengan buku tertentu.

Tabel PEMINJAMAN menyelesaikan ketiganya: satu baris untuk satu kejadian pinjam. Contoh isinya:

Baris 1 dan 4 menunjukkan hal yang sering terlewat. Anggota A001 meminjam B001 dua kali. Kalau kunci primernya (no_anggota, kode_buku), baris 4 ditolak karena duplikat. Untuk relasi yang bisa terjadi berulang, pakai kolom id sendiri (seperti di atas) atau masukkan tgl_pinjam ke kunci komposit.

Berbeda dengan BUKU_PENULIS. Satu penulis tidak mungkin tercatat dua kali untuk buku yang sama, jadi kunci (kode_buku, id_penulis) sudah tepat.

#Skema tabel lengkap dan DDL CREATE TABLE

Hasil akhirnya enam tabel dari lima entitas. KARTU melebur ke ANGGOTA, dan dua relasi M:N menambah dua tabel.

DDL-nya, ditulis dalam urutan yang bisa langsung dijalankan:

CREATE TABLE penerbit (
  id_penerbit  INT PRIMARY KEY,
  nama         VARCHAR(100) NOT NULL,
  kota         VARCHAR(50)
);

CREATE TABLE penulis (
  id_penulis   INT PRIMARY KEY,
  nama         VARCHAR(100) NOT NULL
);

CREATE TABLE anggota (
  no_anggota            CHAR(4) PRIMARY KEY,
  nama                  VARCHAR(100) NOT NULL,
  no_kartu              CHAR(8) NOT NULL UNIQUE,
  kartu_berlaku_sampai  DATE NOT NULL
);

CREATE TABLE buku (
  kode_buku     CHAR(4) PRIMARY KEY,
  judul         VARCHAR(200) NOT NULL,
  tahun_terbit  SMALLINT,
  id_penerbit   INT NOT NULL REFERENCES penerbit(id_penerbit)
);

CREATE TABLE buku_penulis (
  kode_buku   CHAR(4) REFERENCES buku(kode_buku),
  id_penulis  INT REFERENCES penulis(id_penulis),
  urutan      SMALLINT NOT NULL,
  PRIMARY KEY (kode_buku, id_penulis)
);

CREATE TABLE peminjaman (
  id_peminjaman  INT PRIMARY KEY,
  no_anggota     CHAR(4) NOT NULL REFERENCES anggota(no_anggota),
  kode_buku      CHAR(4) NOT NULL REFERENCES buku(kode_buku),
  tgl_pinjam     DATE NOT NULL,
  tgl_kembali    DATE
);

Dua detail di DDL ini mewakili keputusan desain. id_penerbit di BUKU diberi NOT NULL karena setiap buku wajib punya penerbit (partisipasi total). tgl_kembali dibiarkan boleh NULL, karena buku yang belum dikembalikan memang belum punya tanggal kembali.

Untuk menampilkan nama peminjam dan judul buku dari tabel-tabel ini, kamu perlu JOIN tiga tabel. Polanya dibahas di contoh soal SQL JOIN, dan untuk menghitung jumlah pinjaman per anggota lihat GROUP BY dan HAVING.

#Kesalahan yang sering terjadi

• Foreign key di sisi yang salah pada relasi 1:N. Menaruh kode_buku di PENERBIT berarti satu penerbit hanya bisa punya satu buku.
• Membaca kardinalitas dari satu arah. "Satu buku ditulis satu penulis" ditulis sebagai 1:N, padahal arah sebaliknya juga banyak.
• Atribut relasi ditaruh di entitas. tgl_pinjam di tabel BUKU hanya bisa menyimpan tanggal peminjaman terakhir.
• Kunci komposit di tabel penghubung untuk kejadian yang bisa berulang, seperti contoh A001 dan B001 di atas.
• Mengganti relasi peminjaman dengan kolom status_dipinjam di BUKU. Status saat ini tersimpan, tetapi riwayat dan peminjamnya hilang.
• Menggabung 1:1 padahal satu sisinya opsional. Hasilnya kolom kartu penuh NULL untuk anggota yang belum punya kartu.

Kalau setelah ditransformasi skemamu masih terasa janggal, periksa dengan langkah normalisasi 1NF, 2NF, 3NF. ERD yang benar biasanya langsung menghasilkan tabel 3NF.

#FAQ

#Apakah tabel penghubung boleh punya kolom selain dua foreign key?

Boleh dan sering perlu. Atribut yang menjelaskan pasangan, seperti tanggal pinjam atau urutan penulis, justru tempatnya di tabel penghubung.

#Bagaimana kalau perpustakaan punya beberapa eksemplar untuk satu judul?

Tambahkan entitas EKSEMPLAR dengan relasi 1:N dari BUKU (satu judul, banyak eksemplar). PEMINJAMAN lalu merujuk ke kode eksemplar, bukan ke kode buku, karena yang dipinjam adalah fisik bukunya.

#Kapan relasi 1:1 sebaiknya tidak digabung?

Saat salah satu sisi opsional, saat entitasnya punya relasi lain sendiri, atau saat datanya jarang dipakai dan berukuran besar. Dalam kasus itu tabel dipisah dengan foreign key UNIQUE.

#Apa beda relasi M:N dan entitas asosiatif?

Isinya sama-sama tabel penghubung. Disebut entitas asosiatif ketika relasi itu cukup penting untuk punya identitas sendiri, seperti PEMINJAMAN yang punya id_peminjaman dan bisa dirujuk oleh tabel lain, misalnya tabel denda.

#Relasi M:N dengan tiga entitas bagaimana?

Relasi ternary (misalnya dosen mengajar mata kuliah di ruang tertentu) juga menjadi satu tabel baru berisi tiga foreign key. Kuncinya ditentukan dari aturan bisnis, bukan otomatis gabungan ketiganya.