Bahagian 2. Struktur DBMS, jadual dan jenis data

Bahagian pertama Kami terus mencipta emulator bursa saham mudah kami. Inilah yang akan kami lakukan:

• Mari buat gambar rajah organisasi pangkalan data.
• Kami akan menerangkan apa, bagaimana dan di mana ia disimpan.
• Mari kita ketahui bagaimana data berkaitan antara satu sama lain.
• Mari kita mula mempelajari asas SQL menggunakan contoh perintah penciptaan jadual SQL CREATE TABLE , Bahasa Definisi Data ( DDL ) bahasa SQL.
• Mari teruskan menulis program Java. Kami melaksanakan fungsi utama DBMS dari segi java.sql untuk mencipta pangkalan data kami secara pengaturcaraan, menggunakan JDBC dan seni bina tiga peringkat.

Kedua-dua bahagian ini ternyata lebih banyak, kerana kita perlu membiasakan diri dengan asas SQL dan organisasi DBMS dari dalam, dan membuat analogi dengan Java. Untuk tidak membosankan anda dengan penyenaraian kod, pada akhirnya terdapat pautan ke repositori github komit yang sepadan dengan program.

Reka bentuk DBMS

Penerangan Permohonan

Anda telah pun mendengar bahawa mengatur storan data adalah bahagian penting dalam pengaturcaraan. Izinkan saya mengingatkan anda bahawa tujuan permohonan kami adalah emulasi pertukaran yang paling mudah:

• Terdapat saham yang nilainya boleh berubah semasa hari dagangan mengikut peraturan yang diberikan;
• terdapat peniaga dengan modal permulaan;
• peniaga boleh membeli dan menjual saham mengikut algoritma mereka.

Pertukaran beroperasi dalam kutu - tempoh masa tetap (dalam kes kami - 1 minit). Semasa tanda semak, harga saham mungkin berubah, dan kemudian peniaga boleh membeli atau menjual saham.

Struktur data emulasi pertukaran

Mari kita panggil model entiti pertukaran individu. Untuk mengelakkan ralat pembundaran, kami akan bekerja dengan jumlah kewangan melalui kelas BigDecimal(butiran boleh didapati dalam pautan pada akhir artikel). Mari kita terangkan struktur setiap model dengan lebih terperinci: Promosi: Harga saham: Peniaga: Tindakan peniaga: Untuk memastikan keunikan setiap model, kami akan menambah atribut idjenis long . Atribut ini akan menjadi unik dalam contoh model dan akan mengenal pastinya secara unik. Atribut yang merujuk model lain (peniaga, saham, harga saham) boleh menggunakan yang ini iduntuk mengenal pasti model yang sepadan secara unik. Pemikiran serta-merta terlintas di fikiran bahawa kita boleh gunakan Map<Long, Object> untuk menyimpan data sedemikian, di manakah Objectmodel yang sepadan. Walau bagaimanapun, cuba laksanakan ini dalam kod di bawah syarat berikut:

• saiz data dengan ketara melebihi jumlah RAM yang tersedia;
• akses kepada data dijangka dari sedozen tempat yang berbeza;
• keupayaan untuk mengubah suai dan membaca data secara serentak diperlukan;
• adalah perlu untuk memastikan peraturan untuk pembentukan dan integriti data;

...dan anda akan berhadapan dengan tugasan yang memerlukan kelayakan dan masa yang sesuai untuk dilaksanakan. Tidak perlu "mencipta semula roda". Banyak yang telah difikirkan dan ditulis untuk kita. Jadi kami akan menggunakan apa yang telah diuji selama ini.

Menyimpan Data dalam Java

Mari kita pertimbangkan tindakan itu. Di Jawa, kami mencipta kelas khusus untuk model ini Sharedengan medan name, changeProbability, startPrice, delta. Dan banyak saham disimpan sebagai Map<Long, Share>, di mana kuncinya ialah pengecam unik untuk setiap bahagian.

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 dikehendaki melalui ID, gunakan kaedah shares.get(id). Untuk tugas mencari saham mengikut nama atau harga, kami akan mengulangi semua rekod mencari yang kami perlukan, dan seterusnya. Tetapi kita akan pergi ke arah lain dan menyimpan nilai dalam DBMS.

Penyimpanan data dalam DBMS

Mari kita rumuskan set awal peraturan penyimpanan data untuk DBMS:

• Data dalam DBMS disusun ke dalam jadual ( JADUAL ), yang merupakan satu set rekod.
• Semua rekod mempunyai set medan yang sama. Ia ditetapkan semasa membuat jadual.
• Medan boleh ditetapkan kepada nilai lalai ( DEFAULT ).
• Untuk jadual, anda boleh menetapkan kekangan ( CONSTRAINT ) yang menerangkan keperluan untuk datanya untuk memastikan integritinya. Ini boleh dilakukan pada peringkat penciptaan jadual ( CREATE TABLE ) atau ditambah kemudian ( ALTER TABLE ... ADD CONSTRAINT ).
• KEKANGAN yang paling biasa :
  • Kunci utama ialah PRIMER (Id dalam kes kami).
  • Medan nilai unik UNIK (VIN untuk jadual kenderaan).
  • Menyemak medan SEMAK (nilai peratusan tidak boleh lebih daripada 100). Salah satu sekatan peribadi pada medan ialah NOT NULL atau NULL , yang melarang/membenarkan menyimpan NULL dalam medan jadual.
  • Pautan ke jadual pihak ketiga KUNCI ASING (pautan ke saham dalam jadual harga saham).
  • Indeks INDEX (mengindeks medan untuk mempercepatkan pencarian nilai di dalamnya).
  • Pengubahsuaian rekod ( INSERT , UPDATE ) tidak akan berlaku jika nilai medannya bercanggah dengan sekatan (KEKANGAN).
• Setiap jadual boleh mempunyai medan utama (atau beberapa) yang boleh digunakan untuk mengenal pasti rekod secara unik. Medan sedemikian (atau medan, jika ia membentuk kunci komposit) membentuk kunci utama jadual - PRIMER KEY .
  • Kunci utama memastikan keunikan rekod dalam jadual; indeks dicipta padanya, yang memberikan akses pantas kepada keseluruhan rekod berdasarkan nilai kunci.
  • Mempunyai kunci utama menjadikannya lebih mudah untuk membuat pautan antara jadual. Seterusnya, kami akan menggunakan kunci utama tiruan: untuk rekod pertama id = 1, setiap rekod berikutnya akan dimasukkan ke dalam jadual dengan nilai id meningkat satu. Kunci ini sering dipanggil AutoIncrement atau AutoIdentity .

Sebenarnya, jadual stok: Adakah mungkin untuk menggunakan nama saham sebagai kunci dalam kes ini? Pada umumnya - ya, tetapi ada kemungkinan bahawa sesetengah syarikat mengeluarkan saham yang berbeza dan memanggilnya hanya dengan namanya sendiri. Dalam kes ini, tidak akan ada lagi keunikan. Dalam amalan, kunci utama tiruan digunakan agak kerap. Setuju, menggunakan nama penuh sebagai kunci unik dalam jadual yang mengandungi rekod orang tidak akan memastikan keunikan. Serta menggunakan gabungan nama penuh dan tarikh lahir.

Jenis data dalam DBMS

Seperti mana-mana bahasa pengaturcaraan lain, SQL mempunyai penaipan data. Berikut ialah jenis data SQL yang paling biasa: Jenis integer Nyata Tali tarikh dan masa Penyimpanan volum data yang besar

Gaya penulisan SQL

Untuk kebanyakan bahasa, terdapat garis panduan pemformatan kod. Biasanya, dokumen tersebut mengandungi peraturan untuk menamakan pembolehubah, pemalar, kaedah dan struktur bahasa lain. Jadi, untuk Python terdapat PEP8, untuk Java - Oracle Code Conventions for Java . Beberapa set berbeza telah dicipta untuk SQL, yang berbeza sedikit antara satu sama lain. Walau apa pun, anda harus membangunkan tabiat mengikut peraturan semasa memformat kod anda, terutamanya jika anda bekerja dalam satu pasukan. Peraturannya boleh, sebagai contoh, yang berikut (sudah tentu, anda boleh membangunkan set peraturan yang berbeza untuk diri sendiri, perkara utama adalah mematuhinya pada masa hadapan):

• Kata kunci dan perkataan terpelihara, termasuk arahan dan pengendali, mesti ditulis dalam huruf besar: CIPTA JADUAL, KEKANGAN...
• Nama jadual, medan dan objek lain tidak seharusnya bertepatan dengan kata kunci bahasa SQL (lihat pautan di hujung artikel), tetapi mungkin mengandunginya.
• Nama jadual harus mencerminkan tujuannya. Mereka ditulis dalam huruf kecil. Perkataan dalam nama dipisahkan antara satu sama lain dengan garis bawah. Perkataan di hujung mestilah dalam bentuk jamak : peniaga (peniaga), kadar_saham (kadar saham).
• Nama medan jadual harus mencerminkan tujuannya. Ia mesti ditulis dalam huruf kecil, perkataan dalam nama mesti diformatkan dalam gaya Camel Case , dan perkataan di hujung mesti digunakan dalam bentuk tunggal : nama (nama), share_rates (kadar saham).
• Medan kunci buatan mesti mengandungi perkataan id.
• Nama KEKANGAN mesti mengikut konvensyen penamaan jadual. Ia juga mesti memasukkan medan dan jadual yang terlibat di dalamnya, bermula dengan awalan semantik: check_ (menyemak nilai medan), pk_ (kunci utama), fk_ (kunci asing), uniq_ (keunikan medan), idx_ (indeks). Contoh: pk_traider_share_actions_id (kunci utama pada medan id untuk jadual trader_share_actions).
• Dan seterusnya, semasa anda mempelajari SQL, senarai peraturan akan diisi semula/diubah.

Reka bentuk DBMS

Sejurus sebelum mencipta DBMS, ia perlu direka bentuk. Skema akhir mengandungi jadual, satu set medan, KEKANGAN, kunci, syarat lalai untuk medan, hubungan antara jadual dan entiti pangkalan data lain. Di Internet anda boleh menemui banyak pereka dalam talian/luar talian percuma untuk mereka bentuk DBMS kecil. Cuba taip sesuatu seperti "Pereka pangkalan data percuma" ke dalam enjin carian. Aplikasi sedemikian mempunyai ciri tambahan yang berguna:

• Boleh menjana arahan SQL untuk mencipta DBMS.
• Paparkan tetapan secara visual pada rajah.
• Membolehkan anda mengalihkan jadual untuk visualisasi yang lebih baik.
• Tunjukkan kunci, indeks, perhubungan, nilai lalai dan seumpamanya pada rajah.
• Mereka boleh menyimpan skema DBMS dari jauh.

Contohnya, dbdiffo.com menyerlahkan kunci, menunjukkan medan tidak kosong dan pembilang AI (AutoIncrement) dengan label NN:

Mencipta jadual dalam DBMS

Jadi kita mempunyai gambar rajah. Sekarang mari kita beralih kepada membuat jadual (CREATE TABLE). Untuk melakukan ini, kami dinasihatkan untuk mempunyai data awal:

• nama jadual
• nama medan dan jenis
• sekatan (KEKANGAN) pada medan
• nilai lalai untuk medan (jika ada)
• kunci utama (KUNCI UTAMA) jika ada
• sambungan antara jadual (KUNCI ASING)

Kami tidak akan mengkaji secara terperinci semua pilihan arahan CREATE TABLE; kami akan melihat asas SQL menggunakan contoh mencipta jadual 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(keterangan medan) - mencipta jadual dengan nama yang ditentukan; dalam penerangan, medan dipisahkan dengan koma. Sebarang arahan berakhir dengan koma bertitik.
• Penerangan medan bermula dengan namanya, diikuti dengan jenisnya, KEKANGAN dan nilai lalainya.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– medan id jenis integer ialah kunci utama dan pembilang tambahan (untuk setiap rekod baharu untuk medan id, nilai akan dijana lebih satu daripada yang dibuat sebelum ini untuk jadual ini).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– medan tunai, perpuluhan, 15 digit sebelum titik perpuluhan dan dua selepas (data kewangan, contohnya, dolar dan sen). Tidak boleh menerima nilai NULL. Jika tiada nilai diberikan, ia akan mendapat nilai 1000.
• about VARCHAR(255) NULL– medan perihal, rentetan sehingga 255 aksara panjang, boleh menerima nilai kosong.

Ambil perhatian bahawa kita boleh menetapkan sebahagian daripada syarat KEKANGAN selepas mencipta jadual. Mari kita pertimbangkan pembinaan untuk mengubah suai struktur jadual dan medannya: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (syarat) menggunakan contoh:

• CHECK(tradingMethod IN (1,2,3))– medan tradingMethod hanya boleh mengambil nilai 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– medan changeProbability boleh mengambil nilai integer dalam julat dari 1 hingga 100

Hubungan antara jadual

Untuk menganalisis perihalan perhubungan antara jadual, mari lihat penciptaan 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)

Rujukan kepada nilai jadual lain boleh ditetapkan seperti berikut: ALTER TABLEtable_from_which_referred ADD FOREIGN KEY(field_that_referred) REFERENCEStable_to_which_referred (field_that_referred to) Biarkan masuk saham kami mempunyai rekod mengenai saham, contohnya, untuk id=50 kami menyimpan saham Microsoft dengan harga permulaan 17.5, delta 20 dan peluang perubahan 4%. Untuk jadual share_rates kami mendapat tiga sifat utama:

• Kita hanya perlu menyimpan dalam medan kongsi sahaja nilai kunci id daripada jadual saham untuk menggunakannya untuk mendapatkan maklumat yang tinggal (nama, dsb.) daripada jadual saham.
• Kami tidak boleh membuat kadar untuk promosi yang tidak wujud. Anda tidak boleh memasukkan nilai yang tidak wujud ke dalam medan kongsi (yang tiada rekod dalam jadual saham dengan id ini), kerana tidak akan ada surat-menyurat antara jadual.
• Kami tidak boleh memadamkan entri saham dalam saham yang kadarnya ditetapkan dalam share_rates.

Dua mata terakhir berfungsi untuk memastikan integriti data yang disimpan. Anda boleh melihat penciptaan jadual SQL bagi emulasi kami dan contoh pertanyaan SQL dalam pelaksanaan Java bagi kaedah kelas yang sepadan menggunakan pautan ke repositori github pada penghujung artikel. Bahagian ketiga