ส่วนที่ 2 โครงสร้าง DBMS ตาราง และชนิดข้อมูล

ส่วนที่หนึ่ง เรายังคงสร้างโปรแกรมจำลองการแลกเปลี่ยนหุ้นที่เรียบง่ายของเราต่อไป นี่คือสิ่งที่เราจะทำ:

• มาสร้างไดอะแกรมองค์กรฐานข้อมูลกันดีกว่า
• เราจะอธิบายว่าข้อมูลนั้นถูกจัดเก็บอะไร อย่างไร และที่ไหน
• เรามาดูกันว่าข้อมูลเกี่ยวข้องกันอย่างไร
• มาเริ่มเรียนรู้พื้นฐานของ SQL โดยใช้ตัวอย่างคำสั่งสร้างตาราง SQL CREATE TABLE , Data Definition Language ( DDL ) ของภาษา SQL
• มาเขียนโปรแกรม Java กันต่อ เราใช้ฟังก์ชันหลักของ DBMS ในแง่ของ java.sql สำหรับการสร้างฐานข้อมูลของเราโดยทางโปรแกรม โดยใช้ JDBC และสถาปัตยกรรมสามระดับ

ทั้งสองส่วนนี้กลายเป็นเรื่องใหญ่โตมากขึ้น เนื่องจากเราจำเป็นต้องทำความคุ้นเคยกับพื้นฐานของ SQL และการจัดระเบียบของ DBMS จากภายใน และวาดการเปรียบเทียบกับ Java เพื่อไม่ให้คุณเบื่อกับรายการโค้ด ในตอนท้ายจะมีลิงก์ไปยังพื้นที่เก็บข้อมูล Commit GitHub ที่เกี่ยวข้องกับโปรแกรม

การออกแบบดีบีเอ็มเอส

คำอธิบายแอปพลิเคชัน

คุณเคยได้ยินมาว่าการจัดระเบียบพื้นที่จัดเก็บข้อมูลเป็นส่วนสำคัญของการเขียนโปรแกรม ฉันขอเตือนคุณว่าจุดประสงค์ของแอปพลิเคชันของเราคือการจำลองการแลกเปลี่ยนที่ง่ายที่สุด:

• มีหุ้นที่มูลค่าสามารถเปลี่ยนแปลงได้ในระหว่างวันซื้อขายตามหลักเกณฑ์ที่กำหนด
• มีเทรดเดอร์ที่มีเงินทุนเริ่มต้น
• ผู้ค้าสามารถซื้อและขายหุ้นตามอัลกอริทึมของพวกเขา

การแลกเปลี่ยนดำเนินการตามกำหนดเวลา - ระยะเวลาคงที่ (ในกรณีของเรา - 1 นาที) ในช่วงติ๊ก ราคาหุ้นอาจเปลี่ยนแปลง จากนั้นผู้ซื้อขายอาจซื้อหรือขายหุ้น

โครงสร้างข้อมูลการจำลองการแลกเปลี่ยน

ลองเรียกโมเดลเอนทิตีการแลกเปลี่ยนแต่ละรายการ เพื่อหลีกเลี่ยงข้อผิดพลาดในการปัดเศษ เราจะดำเนินการกับจำนวนเงินทางการเงินผ่านชั้นเรียนBigDecimal(รายละเอียดสามารถพบได้ในลิงก์ท้ายบทความ) มาอธิบายโครงสร้างแต่ละรุ่นโดยละเอียดกันดีกว่า โปรโมชั่น: ราคาหุ้น: เทรดเดอร์: การดำเนินการของเทรดเดอร์: เพื่อให้มั่นใจถึงความ เป็น เอกลักษณ์ของแต่ละรุ่น เราจะเพิ่มแอตทริบิวต์idประเภทlong คุณลักษณะนี้จะไม่ซ้ำกันภายในอินสแตนซ์โมเดลและจะระบุได้โดยไม่ซ้ำกัน คุณสมบัติที่อ้างอิงถึงรุ่นอื่นๆ (ผู้ซื้อขาย หุ้น ราคาหุ้น) สามารถใช้คุณสมบัตินี้idเพื่อระบุรุ่นที่เกี่ยวข้องโดยไม่ซ้ำกัน ความคิดนี้เข้ามาในใจทันทีว่าเราสามารถใช้Map<Long, Object> เพื่อจัดเก็บข้อมูลดังกล่าวได้ โดยที่Objectโมเดลที่เกี่ยวข้องอยู่ ที่ไหน อย่างไรก็ตาม ให้ลองใช้โค้ดนี้ภายใต้เงื่อนไขต่อไปนี้:

• ขนาดข้อมูลเกินจำนวน RAM ที่มีอยู่อย่างมาก
• การเข้าถึงข้อมูลคาดว่าจะมาจากที่ต่างๆ มากมาย
• จำเป็นต้องมีความสามารถในการแก้ไขและอ่านข้อมูลพร้อมกัน
• จำเป็นต้องตรวจสอบกฎเกณฑ์สำหรับการสร้างและความสมบูรณ์ของข้อมูล

...และคุณจะพบกับงานที่ต้องใช้คุณสมบัติและเวลาที่เหมาะสมในการดำเนินการ ไม่จำเป็นต้อง "สร้างล้อใหม่" มีคนคิดและเขียนไว้มากมายสำหรับเราแล้ว ดังนั้นเราจะใช้สิ่งที่ผ่านการทดสอบมาหลายปีแล้ว

การจัดเก็บข้อมูลใน Java

ลองพิจารณาการกระทำ ใน Java เราสร้างคลาสเฉพาะสำหรับโมเดลนี้ด้วยShareฟิลด์name, changeProbability, startPrice, deltaและการแชร์จำนวนมากถูกจัดเก็บเป็นMap<Long, 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 ));

หากต้องการเข้าถึงโปรโมชันที่ต้องการด้วย ID ให้ใช้วิธีshares.get(id)การ สำหรับงานค้นหาหุ้นตามชื่อหรือราคา เราจะวนดูบันทึกทั้งหมดที่ค้นหาหุ้นที่เราต้องการ และอื่นๆ แต่เราจะไปทางอื่นและเก็บค่าไว้ใน DBMS

การจัดเก็บข้อมูลใน DBMS

ให้เรากำหนดกฎการจัดเก็บข้อมูลชุดเริ่มต้นสำหรับ DBMS:

• ข้อมูลใน DBMS ถูกจัดระเบียบเป็นตาราง ( TABLE ) ซึ่งเป็นชุดของบันทึก
• เรกคอร์ดทั้งหมดมีชุดฟิลด์เหมือนกัน ถูกกำหนดไว้เมื่อสร้างตาราง
• ฟิลด์สามารถตั้งค่าเป็นค่าเริ่มต้น ( DEFAULT )
• สำหรับตาราง คุณสามารถตั้งค่าข้อจำกัด ( CONSTRAINT ) ที่อธิบายข้อกำหนดสำหรับข้อมูลเพื่อให้แน่ใจว่ามีความสมบูรณ์ ซึ่งสามารถทำได้ในขั้นตอนการสร้างตาราง ( CREATE TABLE ) หรือเพิ่มในภายหลัง ( ALTER TABLE ... ADD CONSTRAINT )
• ข้อจำกัดที่พบบ่อยที่สุด:
  • คีย์หลักคือ PRIMARY (รหัสในกรณีของเรา)
  • ฟิลด์ค่าที่ไม่ซ้ำ UNIQUE ( VIN สำหรับตารางยานพาหนะ)
  • กำลังตรวจสอบ ฟิลด์ CHECK (ค่าเปอร์เซ็นต์ต้องไม่เกิน 100) ข้อจำกัดส่วนตัวอย่างหนึ่งในฟิลด์คือNOT NULLหรือNULLซึ่งห้าม/อนุญาตให้จัดเก็บ NULL ในช่องตาราง
  • ลิงก์ไปยังตารางของบุคคลที่สามFOREIGN KEY (ลิงก์ไปยังหุ้นในตารางราคาหุ้น)
  • ดัชนีINDEX (จัดทำดัชนีฟิลด์เพื่อเพิ่มความเร็วในการค้นหาค่าในนั้น)
  • การแก้ไขบันทึก ( INSERT , UPDATE ) จะไม่เกิดขึ้นหากค่าของฟิลด์ขัดแย้งกับข้อ จำกัด (CONSTRAINT)
• แต่ละตารางสามารถมีฟิลด์คีย์ (หรือหลายฟิลด์) ที่สามารถใช้เพื่อระบุเรกคอร์ดโดยไม่ซ้ำกัน ฟิลด์ดังกล่าว (หรือฟิลด์ หากเป็นคีย์ผสม) จะ สร้างคีย์หลักของตาราง - PRIMARY KEY
  • คีย์หลักช่วยให้แน่ใจว่าบันทึกในตารางไม่ซ้ำกัน โดยดัชนีจะถูกสร้างขึ้น ซึ่งช่วยให้เข้าถึงบันทึกทั้งหมดได้อย่างรวดเร็วตามค่าคีย์
  • การมีคีย์หลักช่วยให้สร้างลิงก์ระหว่างตารางได้ง่ายขึ้นมาก ต่อไป เราจะใช้คีย์หลักเทียม: สำหรับระเบียนแรกid = 1แต่ละระเบียนที่ตามมาจะถูกแทรกลงในตารางโดยมีค่า ID เพิ่มขึ้นหนึ่งรายการ คีย์นี้มักเรียกว่าAutoIncreaseหรือAutoIdentity

จริงๆ แล้ว ตารางหุ้น ในกรณีนี้ สามารถใช้ชื่อหุ้นเป็นคีย์ได้หรือไม่? โดยทั่วไป - ใช่ แต่มีความเป็นไปได้ที่บางบริษัทจะออกหุ้นที่แตกต่างกันและเรียกตามชื่อของตนเองเท่านั้น ในกรณีนี้จะไม่มีเอกลักษณ์อีกต่อไป ในทางปฏิบัติ มีการใช้คีย์หลักเทียมค่อนข้างบ่อย เห็นด้วย การใช้ชื่อเต็มเป็นคีย์เฉพาะในตารางที่มีบันทึกของบุคคลไม่ได้รับประกันความเป็นเอกลักษณ์ พร้อมทั้งใช้ชื่อเต็มและวันเดือนปีเกิดรวมกัน

ชนิดข้อมูลใน DBMS

เช่นเดียวกับภาษาการเขียนโปรแกรมอื่นๆ SQL มีการพิมพ์ข้อมูล ต่อไปนี้เป็นประเภทข้อมูล SQL ที่พบบ่อยที่สุด: ประเภทจำนวนเต็ม จริง สตริง วันและเวลา การจัดเก็บข้อมูลปริมาณมาก

สไตล์การเขียน SQL

สำหรับหลายภาษา มีแนวทางการจัดรูปแบบโค้ด โดยทั่วไป เอกสารดังกล่าวประกอบด้วยกฎสำหรับการตั้งชื่อตัวแปร ค่าคงที่ วิธีการ และโครงสร้างภาษาอื่นๆ ดังนั้นสำหรับ Python มี PEP8 สำหรับJava - Oracle Code Conventions สำหรับ Java ชุดที่แตกต่างกันหลายชุดได้ถูกสร้างขึ้นสำหรับ SQL ซึ่งแตกต่างจากชุดอื่นเล็กน้อย อย่างไรก็ตาม คุณควรพัฒนานิสัยการปฏิบัติตามกฎเมื่อจัดรูปแบบโค้ดของคุณ โดยเฉพาะอย่างยิ่งหากคุณทำงานเป็นทีม ตัวอย่างเช่นกฎอาจเป็นสิ่งต่อไปนี้ (แน่นอนคุณสามารถพัฒนากฎชุดอื่นสำหรับตัวคุณเองได้ สิ่งสำคัญคือต้องยึดถือกฎเหล่านั้นในอนาคต):

• คำสำคัญและคำสงวน รวมถึงคำสั่งและตัวดำเนินการ ต้องเขียนด้วยตัวพิมพ์ใหญ่: CREATE TABLE, CONSTRAINT...
• ชื่อของตาราง เขตข้อมูล และวัตถุอื่น ๆ ไม่ควรตรงกับคำหลักภาษา SQL (ดูลิงก์ท้ายบทความ) แต่อาจมีอยู่
• ชื่อตารางควรสะท้อนถึงวัตถุประสงค์ พวกเขาเขียนด้วยตัวพิมพ์เล็ก คำในชื่อจะแยกออกจากกันด้วยเครื่องหมายขีดล่าง คำที่อยู่ท้ายสุดจะต้องอยู่ในพหูพจน์ : เทรดเดอร์ (เทรดเดอร์), share_rates (เรทหุ้น)
• ชื่อเขตข้อมูลตารางควรสะท้อนถึงวัตถุประสงค์ ต้องเขียนด้วยตัวพิมพ์เล็ก คำในชื่อต้องอยู่ใน รูปแบบ Camel Caseและคำต่อท้ายต้องใช้เป็นเอกพจน์ : name (name), share_rates (share rate)
• ช่องคีย์เทียมต้องมีคำว่า id
• ชื่อข้อจำกัดต้องเป็นไปตามแบบแผนการตั้งชื่อตาราง พวกเขายังต้องรวมเขตข้อมูลและตารางที่เกี่ยวข้องด้วย โดยเริ่มต้นด้วยคำนำหน้าความหมาย: check_ (การตรวจสอบค่าของฟิลด์), pk_ (คีย์หลัก), fk_ (คีย์ต่างประเทศ), uniq_ (เอกลักษณ์ของฟิลด์), idx_ (ดัชนี) ตัวอย่าง: pk_traider_share_actions_id (คีย์หลักบนช่อง id สำหรับตาราง trader_share_actions)
• เมื่อคุณศึกษา SQL รายการกฎต่างๆ จะถูกเติม/เปลี่ยนแปลง

การออกแบบดีบีเอ็มเอส

ทันทีก่อนที่จะสร้าง DBMS จะต้องได้รับการออกแบบ สคีมาสุดท้ายประกอบด้วยตาราง ชุดของฟิลด์ ข้อจำกัด คีย์ เงื่อนไขเริ่มต้นสำหรับฟิลด์ ความสัมพันธ์ระหว่างตารางและเอนทิตีฐานข้อมูลอื่นๆ บนอินเทอร์เน็ต คุณจะพบนักออกแบบออนไลน์/ออฟไลน์ฟรีจำนวนมากสำหรับการออกแบบ DBMS ขนาดเล็ก ลองพิมพ์ข้อความเช่น “Database Designer free” ลงในเครื่องมือค้นหา แอปพลิเคชันดังกล่าวมีคุณสมบัติเพิ่มเติมที่เป็นประโยชน์:

• สามารถสร้างคำสั่ง SQL เพื่อสร้าง DBMS ได้
• แสดงการตั้งค่าบนแผนภาพด้วยสายตา
• ช่วยให้คุณย้ายตารางเพื่อให้เห็นภาพได้ดีขึ้น
• แสดงคีย์ ดัชนี ความสัมพันธ์ ค่าเริ่มต้น และอื่นๆ บนไดอะแกรม
• พวกเขาสามารถจัดเก็บสคีมา DBMS จากระยะไกลได้

ตัวอย่างเช่นdbdiffo.comไฮไลต์คีย์ แสดงฟิลด์ที่ไม่ว่างเปล่าและตัวนับ AI (AutoIncrease) พร้อมป้ายกำกับ NN:

การสร้างตารางใน DBMS

เราก็มีแผนภาพ ตอนนี้เรามาดูการสร้างตารางกันดีกว่า (CREATE TABLE) ในการดำเนินการนี้ ขอแนะนำให้เรามีข้อมูลเบื้องต้น:

• ชื่อตาราง
• ชื่อฟิลด์และประเภท
• ข้อจำกัด (CONSTRAINTS) ในฟิลด์
• ค่าเริ่มต้นสำหรับฟิลด์ (ถ้ามี)
• คีย์หลัก (คีย์หลัก) หากมี
• การเชื่อมต่อระหว่างตาราง (คีย์ต่างประเทศ)

เราจะไม่ศึกษารายละเอียดตัวเลือกทั้งหมดของคำสั่ง CREATE TABLE เราจะดูพื้นฐานของ SQL โดยใช้ตัวอย่างการสร้างตารางสำหรับเทรดเดอร์:

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)

มาดูกันดีกว่า:

• CREATE TABLE traiders(คำอธิบายฟิลด์) - สร้างตารางตามชื่อที่ระบุ ในคำอธิบาย ฟิลด์จะถูกคั่นด้วยเครื่องหมายจุลภาค คำสั่งใดๆ จะลงท้ายด้วยเครื่องหมายอัฒภาค
• คำอธิบายฟิลด์เริ่มต้นด้วยชื่อ ตามด้วยประเภท ข้อจำกัด และค่าเริ่มต้น
• id BIGINT AUTO_INCREMENT PRIMARY KEY– ฟิลด์ id ของประเภทจำนวนเต็มคือคีย์หลักและตัวนับส่วนเพิ่ม (สำหรับแต่ละบันทึกใหม่สำหรับฟิลด์ id ค่าจะถูกสร้างขึ้นที่มากกว่าหนึ่งค่าที่สร้างขึ้นก่อนหน้านี้สำหรับตารางนี้)
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– ช่องเงินสด ทศนิยม 15 หลักก่อนจุดทศนิยมและสองหลัง (ข้อมูลทางการเงิน เช่น ดอลลาร์ และเซนต์) ไม่สามารถยอมรับค่า NULL ได้ ถ้าไม่ระบุค่าก็จะได้ค่า 1,000
• about VARCHAR(255) NULL– ช่อง about ซึ่งเป็นสตริงที่มีความยาวสูงสุด 255 อักขระ สามารถรับค่าว่างได้

โปรดทราบว่าเราสามารถตั้งค่า ส่วนหนึ่งของเงื่อนไข CONSTRAINT ได้หลังจากสร้างตารางแล้ว ลองพิจารณาโครงสร้างสำหรับการปรับเปลี่ยนโครงสร้างตารางและฟิลด์: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (เงื่อนไข)โดยใช้ตัวอย่าง:

• CHECK(tradingMethod IN (1,2,3))– ช่อง tradingMethod สามารถรับได้เฉพาะค่า 1,2,3 เท่านั้น
• CHECK(changeProbability <= 100 AND changeProbability > 0)– ช่อง changeProbability สามารถรับค่าจำนวนเต็มในช่วงตั้งแต่ 1 ถึง 100

ความสัมพันธ์ระหว่างตาราง

เพื่อวิเคราะห์คำอธิบายความสัมพันธ์ระหว่างตาราง มาดูการสร้าง 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)

ลิงก์ไปยังค่าของตารางอื่นสามารถตั้งค่าได้ดังนี้: ALTER TABLEtable_from_where_is_referred ADD FOREIGN KEY(field_ซึ่ง_referred) REFERENCEStable_to_ซึ่ง_is_referenced (field_ซึ่ง_is_referenced) อนุญาตให้แชร์เรามีบันทึกการแชร์ เช่น สำหรับ id=50 เราจัดเก็บหุ้น Microsoft ด้วยราคาเริ่มต้นที่ 17.5 , เดลต้า 20 และโอกาสเปลี่ยนแปลง 4% สำหรับ ตาราง share_ratesเราได้รับคุณสมบัติหลักสามประการ:

• เราเพียงต้องเก็บค่าของคีย์ ID จากตารางการแชร์ในช่องแชร์เพื่อใช้เพื่อรับข้อมูลที่เหลือ (ชื่อ ฯลฯ ) จากตารางการแชร์
• เราไม่สามารถสร้างอัตราสำหรับโปรโมชันที่ไม่มีอยู่จริงได้ คุณไม่สามารถแทรกค่าที่ไม่มีอยู่จริงลงในช่องแชร์ได้ (ซึ่งไม่มีบันทึกในตารางการแชร์ด้วยรหัสนี้) เนื่องจากจะไม่มีการโต้ตอบระหว่างตาราง
• เราไม่สามารถลบรายการหุ้นในหุ้นที่กำหนดอัตราไว้ใน share_rates

สองจุดสุดท้ายมีไว้เพื่อรับรองความสมบูรณ์ของข้อมูลที่เก็บไว้ คุณสามารถดูการสร้างตาราง SQL ของการจำลองของเราและตัวอย่างแบบสอบถาม SQL ในการนำวิธีการ Java ไปใช้ของคลาสที่เกี่ยวข้องโดยใช้ลิงก์ไปยังที่เก็บ github ที่ส่วนท้ายของบทความ ส่วนที่สาม