Bagian 2. Struktur DBMS, tabel dan tipe data

Bagian pertama Kami terus membuat emulator bursa saham sederhana kami. Inilah yang akan kami lakukan:

• Mari kita membuat diagram organisasi database.
• Kami akan menjelaskan apa, bagaimana dan di mana disimpan.
• Mari kita cari tahu bagaimana data berhubungan satu sama lain.
• Mari kita mulai mempelajari dasar-dasar SQL menggunakan contoh perintah pembuatan tabel SQL CREATE TABLE , Data Definition Language ( DDL ) dari bahasa SQL.
• Mari lanjutkan menulis program Java. Kami mengimplementasikan fungsi utama DBMS dalam java.sql untuk membuat database kami secara terprogram, menggunakan JDBC dan arsitektur tiga tingkat.

Kedua bagian ini ternyata lebih banyak, karena kita perlu membiasakan diri dengan dasar-dasar SQL dan organisasi DBMS dari dalam, dan menggambar analogi dengan Java. Agar tidak membuat Anda bosan dengan daftar kode, di bagian akhir terdapat tautan ke repositori commit github yang sesuai dengan program tersebut.

desain DBMS

Deskripsi Aplikasi

Anda pernah mendengar bahwa mengatur penyimpanan data merupakan bagian integral dari pemrograman. Izinkan saya mengingatkan Anda bahwa tujuan aplikasi kami adalah emulasi pertukaran paling sederhana:

• Ada saham yang nilainya dapat berubah pada hari perdagangan sesuai dengan aturan yang berlaku;
• ada pedagang dengan modal awal;
• pedagang dapat membeli dan menjual saham sesuai dengan algoritmanya.

Pertukaran beroperasi dalam waktu singkat - periode waktu tetap (dalam kasus kami - 1 menit). Selama tick, harga saham dapat berubah, dan kemudian pedagang dapat membeli atau menjual saham.

Struktur data emulasi pertukaran

Sebut saja model entitas pertukaran individual. Untuk menghindari kesalahan pembulatan, kami akan menangani jumlah keuangan melalui kelas BigDecimal(detailnya dapat ditemukan di tautan di akhir artikel). Mari kita uraikan struktur masing-masing model secara lebih rinci: Promosi: Harga saham: Pedagang: Tindakan pedagang: Untuk memastikan keunikan setiap model, kami akan menambahkan atribut idtipe long . Atribut ini akan menjadi unik dalam contoh model dan akan mengidentifikasinya secara unik. Atribut yang mereferensikan model lain (pedagang, saham, harga saham) dapat menggunakan atribut ini iduntuk mengidentifikasi model terkait secara unik. Segera terlintas dalam pikiran kita apa yang bisa kita gunakan Map<Long, Object> untuk menyimpan data tersebut, di mana Objectmodel yang sesuai. Namun, coba terapkan ini dalam kode dengan ketentuan berikut:

• ukuran data secara signifikan melebihi jumlah RAM yang tersedia;
• akses terhadap data diharapkan dari banyak tempat berbeda;
• diperlukan kemampuan untuk mengubah dan membaca data secara bersamaan;
• perlu untuk memastikan aturan untuk pembentukan dan integritas data;

...dan Anda akan dihadapkan pada tugas-tugas yang memerlukan kualifikasi dan waktu yang tepat untuk melaksanakannya. Tidak perlu “menemukan kembali roda”. Banyak hal telah dipikirkan dan ditulis untuk kami. Jadi kami akan menggunakan apa yang telah diuji selama bertahun-tahun.

Menyimpan Data di Java

Mari kita pertimbangkan tindakannya. Di Java, kami membuat kelas khusus untuk model ini Sharedengan bidang name, changeProbability, startPrice, delta. Dan banyak share yang disimpan sebagai Map<Long, Share>, dimana kuncinya adalah pengidentifikasi unik untuk setiap share.

public class Share {
    private String name;
    private BigDecimal startPrice;
    private int changeProbability;
    private int delta;
}
Map<Long, Share> shares = new HashMap<>();
shares.put(1L, new Share("ibm", BigDecimal.valueOf(20.0), 15, 10));
shares.put(2L, new Share("apple", BigDecimal.valueOf(14.0), 25, 15));
shares.put(3L, new Share("google", BigDecimal.valueOf(12.0), 20, 8));
...
shares.put(50L, new Share("microsoft", BigDecimal.valueOf(17.5), 10,4 ));

Untuk mengakses promosi yang diinginkan berdasarkan ID, gunakan metode shares.get(id). Untuk tugas menemukan saham berdasarkan nama atau harga, kita akan menelusuri semua catatan untuk mencari saham yang kita perlukan, dan seterusnya. Tapi kami akan mengambil cara lain dan menyimpan nilainya di DBMS.

Penyimpanan data dalam DBMS

Mari kita merumuskan seperangkat aturan penyimpanan data awal untuk DBMS:

• Data dalam DBMS disusun menjadi tabel ( TABLE ), yang merupakan sekumpulan catatan.
• Semua catatan memiliki kumpulan bidang yang sama. Mereka diatur saat membuat tabel.
• Bidang ini dapat diatur ke nilai default ( DEFAULT ).
• Untuk tabel, Anda dapat menetapkan batasan ( CONSTRAINT ) yang menjelaskan persyaratan datanya untuk memastikan integritasnya. Hal ini dapat dilakukan pada tahap pembuatan tabel ( CREATE TABLE ) atau ditambahkan kemudian ( ALTER TABLE ... ADD CONSTRAINT ).
• KENDALA yang paling umum :
  • Kunci utama adalah PRIMARY (Id dalam kasus kami).
  • Bidang nilai unik UNIK (VIN untuk tabel kendaraan).
  • Memeriksa kolom CHECK (nilai persentase tidak boleh lebih besar dari 100). Salah satu batasan pribadi pada suatu bidang adalah NOT NULL atau NULL , yang melarang/mengizinkan penyimpanan NULL dalam bidang tabel.
  • Tautan ke tabel pihak ketiga FOREIGN KEY (tautan ke suatu saham di tabel harga saham).
  • Index INDEX (mengindeks suatu field untuk mempercepat pencarian nilai di dalamnya).
  • Modifikasi catatan ( INSERT , UPDATE ) tidak akan terjadi jika nilai bidangnya bertentangan dengan batasan (CONSTRAINT).
• Setiap tabel dapat memiliki bidang kunci (atau beberapa) yang dapat digunakan untuk mengidentifikasi catatan secara unik. Bidang seperti itu (atau bidang, jika membentuk kunci komposit) membentuk kunci utama tabel - PRIMARY KEY .
  • Kunci utama memastikan keunikan catatan dalam tabel; indeks dibuat di atasnya, yang memberikan akses cepat ke seluruh catatan berdasarkan nilai kunci.
  • Memiliki kunci utama mempermudah pembuatan tautan antar tabel. Selanjutnya, kita akan menggunakan kunci primer buatan: untuk record pertama id = 1, setiap record berikutnya akan dimasukkan ke dalam tabel dengan nilai id bertambah satu. Kunci ini sering disebut AutoIncrement atau AutoIdentity .

Sebenarnya tabel saham: Apakah mungkin menggunakan nama saham sebagai kunci dalam kasus ini? Pada umumnya - ya, tetapi ada kemungkinan bahwa beberapa perusahaan menerbitkan saham yang berbeda dan menyebutnya hanya dengan namanya sendiri. Dalam hal ini, tidak akan ada lagi keunikan. Dalam praktiknya, kunci primer buatan cukup sering digunakan. Setuju, menggunakan nama lengkap sebagai kunci unik dalam tabel yang berisi catatan orang tidak akan menjamin keunikan. Serta menggunakan kombinasi nama lengkap dan tanggal lahir.

Tipe data dalam DBMS

Seperti bahasa pemrograman lainnya, SQL memiliki pengetikan data. Berikut adalah tipe data SQL yang paling umum: Tipe integer Nyata Rangkaian tanggal dan waktu Penyimpanan data dalam jumlah besar

Gaya penulisan SQL

Untuk banyak bahasa, ada pedoman pemformatan kode. Biasanya, dokumen tersebut berisi aturan untuk memberi nama variabel, konstanta, metode, dan struktur bahasa lainnya. Jadi, untuk Python ada PEP8, untuk Java - Konvensi Kode Oracle untuk Java . Beberapa set berbeda telah dibuat untuk SQL, yang sedikit berbeda satu sama lain. Apapun itu, Anda harus mengembangkan kebiasaan mengikuti aturan saat memformat kode Anda, terutama jika Anda bekerja dalam tim. Aturannya bisa berupa, misalnya, sebagai berikut (tentu saja, Anda dapat mengembangkan seperangkat aturan lain untuk diri Anda sendiri, yang utama adalah mematuhinya di masa mendatang):

• Kata kunci dan kata cadangan, termasuk perintah dan operator, harus ditulis dengan huruf kapital: CREATE TABLE, CONSTRAINT...
• Nama tabel, bidang, dan objek lainnya tidak boleh sama dengan kata kunci bahasa SQL (lihat tautan di akhir artikel), tetapi mungkin memuatnya.
• Nama tabel harus mencerminkan tujuannya. Mereka ditulis dengan huruf kecil. Kata-kata dalam nama dipisahkan satu sama lain dengan garis bawah. Kata di akhir harus dalam bentuk jamak : pedagang (pedagang), share_rates (nilai saham).
• Nama bidang tabel harus mencerminkan tujuannya. Harus ditulis dengan huruf kecil, kata pada nama harus diformat dengan gaya Camel Case , dan kata di akhir harus menggunakan bentuk tunggal : name (nama), share_rates (nilai saham).
• Bidang kunci buatan harus berisi kata id.
• Nama CONSTRAINT harus mengikuti konvensi penamaan tabel. Mereka juga harus menyertakan bidang dan tabel yang terlibat di dalamnya, dimulai dengan awalan semantik: check_ (memeriksa nilai bidang), pk_ (kunci utama), fk_ (kunci asing), uniq_ (keunikan bidang), idx_ (indeks). Contoh: pk_traider_share_actions_id (kunci utama pada kolom id untuk tabel trader_share_actions).
• Dan seterusnya, saat Anda mempelajari SQL, daftar aturan akan diisi ulang/diubah.

desain DBMS

Sebelum membuat DBMS, perlu dirancang terlebih dahulu. Skema akhir berisi tabel, sekumpulan bidang, CONSTRAINT, kunci, kondisi default untuk bidang, hubungan antara tabel dan entitas database lainnya. Di Internet Anda dapat menemukan banyak desainer online/offline gratis untuk merancang DBMS kecil. Coba ketikkan sesuatu seperti “Perancang basis data gratis” ke dalam mesin pencari. Aplikasi tersebut memiliki properti tambahan yang berguna:

• Dapat menghasilkan perintah SQL untuk membuat DBMS.
• Tampilkan pengaturan secara visual pada diagram.
• Memungkinkan Anda memindahkan tabel untuk visualisasi yang lebih baik.
• Tampilkan kunci, indeks, hubungan, nilai default, dan sejenisnya pada diagram.
• Mereka dapat menyimpan skema DBMS dari jarak jauh.

Misalnya, dbdiffo.com menyorot kunci, menampilkan kolom yang tidak kosong, dan penghitung AI (Peningkatan Otomatis) dengan label NN:

Membuat tabel di DBMS

Jadi kita punya diagramnya. Sekarang mari kita beralih ke pembuatan tabel (CREATE TABLE). Untuk melakukan hal ini, disarankan bagi kita untuk memiliki data awal:

• nama tabel
• nama bidang dan jenisnya
• pembatasan (KENDALA) pada bidang
• nilai default untuk bidang (jika tersedia)
• kunci utama (PRIMARY KEY) jika tersedia
• koneksi antar tabel (FOREIGN KEY)

Kami tidak akan mempelajari secara detail semua opsi dari perintah CREATE TABLE, kami akan melihat dasar-dasar SQL menggunakan contoh membuat tabel untuk pedagang:

CREATE TABLE traiders(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	freqTiсk INTEGER NOT NULL,
	cash  DECIMAL(15,2) NOT NULL DEFAULT 1000,
	tradingMethod INTEGER NOT NULL,
	changeProbability INTEGER NOT NULL DEFAULT 50,
	about VARCHAR(255) NULL
);
ALTER TABLE traiders ADD CONSTRAINT check_traiders_tradingMethod
	CHECK(tradingMethod IN (1,2,3));
ALTER TABLE traiders ADD CONSTRAINT check_traiders_changeProbability
	CHECK(changeProbability <= 100 AND changeProbability > 0)

Mari kita lihat lebih dekat:

• CREATE TABLE traiders(deskripsi bidang) - membuat tabel dengan nama yang ditentukan; dalam deskripsi, bidang dipisahkan dengan koma. Perintah apa pun diakhiri dengan titik koma.
• Deskripsi bidang dimulai dengan namanya, diikuti dengan jenisnya, CONSTRAINT, dan nilai defaultnya.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– bidang id bertipe bilangan bulat adalah kunci utama dan penghitung tambahan (untuk setiap catatan baru untuk bidang id, nilai akan dihasilkan satu lebih banyak dari nilai yang dibuat sebelumnya untuk tabel ini).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– kolom uang tunai, desimal, 15 digit sebelum koma dan dua digit setelahnya (data keuangan, misalnya dolar dan sen). Tidak dapat menerima nilai NULL. Jika tidak ada nilai yang diberikan maka akan mendapat nilai 1000.
• about VARCHAR(255) NULL– bidang tentang, string yang panjangnya hingga 255 karakter, dapat menerima nilai kosong.

Perhatikan bahwa kita dapat mengatur bagian dari kondisi CONSTRAINT setelah membuat tabel. Mari kita pertimbangkan konstruksi untuk memodifikasi struktur tabel dan bidangnya: ALTER TABLE nama_tabel ADD CONSTRAINT nama_kendala CHECK (kondisi) menggunakan contoh:

• CHECK(tradingMethod IN (1,2,3))– bidang tradingMethod hanya dapat mengambil nilai 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– bidang changeProbability dapat mengambil nilai integer dalam rentang 1 hingga 100

Hubungan antar tabel

Untuk menganalisis deskripsi hubungan antar tabel, mari kita lihat pembuatan share_rates:

CREATE TABLE share_rates(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	operDate datetime NOT NULL,
	share BIGINT NOT NULL,
	rate DECIMAL(15,2) NOT NULL
);
ALTER TABLE share_rates ADD FOREIGN KEY (share) REFERENCES shares(id)

Referensi nilai tabel lain dapat diatur sebagai berikut: ALTER TABLEtable_from_which_referred ADD FOREIGN KEY(field_that_referred) REFERENCEStable_to_which_referred (field_that_referred to) Misalkan pada share kita mempunyai record pada share, misalnya untuk id=50 kita menyimpan saham Microsoft dengan harga awal 17,5, delta 20 dan peluang perubahan 4%. Untuk tabel share_rates kita mendapatkan tiga properti utama:

• Kita hanya perlu menyimpan di kolom share hanya nilai kunci id dari tabel share untuk menggunakannya untuk mendapatkan informasi yang tersisa (nama, dll) dari tabel share.
• Kami tidak dapat membuat tarif untuk promosi yang tidak ada. Anda tidak dapat memasukkan nilai yang tidak ada ke dalam bidang berbagi (yang tidak ada catatan di tabel berbagi dengan id ini), karena tidak akan ada korespondensi antar tabel.
• Kami tidak dapat menghapus entri share dalam share yang tarifnya ditetapkan dalam share_rates.

Dua poin terakhir berfungsi untuk memastikan integritas data yang disimpan. Anda dapat melihat pembuatan tabel SQL dari emulasi kami dan contoh kueri SQL dalam implementasi metode Java dari kelas terkait menggunakan tautan ke repositori github di akhir artikel. Bagian ketiga