Phần 2. Cấu trúc, bảng và kiểu dữ liệu của DBMS

Phần đầu tiên Chúng tôi tiếp tục tạo trình giả lập trao đổi chứng khoán đơn giản của mình. Đây là những gì chúng tôi sẽ làm:

• Hãy tạo một sơ đồ tổ chức cơ sở dữ liệu.
• Chúng tôi sẽ mô tả những gì, như thế nào và nơi nó được lưu trữ.
• Hãy cùng tìm hiểu xem dữ liệu có liên quan với nhau như thế nào nhé.
• Hãy bắt đầu tìm hiểu những điều cơ bản về SQL bằng cách sử dụng ví dụ về lệnh tạo bảng SQL CREATE TABLE , Ngôn ngữ định nghĩa dữ liệu ( DDL ) của ngôn ngữ SQL.
• Hãy tiếp tục viết chương trình Java. Chúng tôi triển khai các chức năng chính của DBMS dưới dạng java.sql để tạo cơ sở dữ liệu theo chương trình, sử dụng JDBC và kiến ​​trúc ba tầng.

Hai phần này hóa ra lại đồ sộ hơn vì chúng ta cần làm quen với những điều cơ bản về SQL và cách tổ chức DBMS từ bên trong, đồng thời rút ra những điểm tương đồng với Java. Để không làm bạn nhàm chán với danh sách mã, ở cuối có các liên kết đến kho lưu trữ github cam kết tương ứng với chương trình.

thiết kế cơ sở dữ liệu

Mô tả ứng dụng

Bạn đã từng nghe nói rằng việc tổ chức lưu trữ dữ liệu là một phần không thể thiếu trong lập trình. Hãy để tôi nhắc bạn rằng mục đích của ứng dụng của chúng tôi là mô phỏng trao đổi đơn giản nhất:

• Có những cổ phiếu có giá trị thay đổi trong ngày giao dịch theo quy luật nhất định;
• có thương nhân có vốn ban đầu;
• thương nhân có thể mua và bán cổ phiếu theo thuật toán của họ.

Sàn giao dịch hoạt động theo tích tắc - khoảng thời gian cố định (trong trường hợp của chúng tôi - 1 phút). Trong một tích tắc, giá cổ phiếu có thể thay đổi và sau đó người giao dịch có thể mua hoặc bán cổ phiếu.

Trao đổi cấu trúc dữ liệu mô phỏng

Hãy gọi các mô hình thực thể trao đổi riêng lẻ. Để tránh lỗi làm tròn, chúng tôi sẽ làm việc với số tiền tài chính thông qua một lớp BigDecimal(chi tiết có thể tìm thấy trong liên kết ở cuối bài viết). Hãy mô tả cấu trúc của từng mô hình chi tiết hơn : Giá cổ phiếu: Người giao dịch: Hành động của nhà giao dịch: Để đảm bảo tính duy nhất của từng mô hình, chúng tôi sẽ thêm một thuộc tính idloại long . Thuộc tính này sẽ là duy nhất trong các phiên bản mô hình và sẽ xác định duy nhất nó. Các thuộc tính tham chiếu đến các mô hình khác (thương nhân, cổ phiếu, giá cổ phiếu) có thể sử dụng thuộc tính này idđể xác định duy nhất mô hình tương ứng. Ý nghĩ ngay lập tức xuất hiện trong đầu chúng ta là chúng ta có thể sử dụng Map<Long, Object> để lưu trữ những dữ liệu đó, Objectmô hình tương ứng ở đâu. Tuy nhiên, hãy thử triển khai mã này theo các điều kiện sau:

• kích thước dữ liệu vượt quá đáng kể dung lượng RAM có sẵn;
• quyền truy cập vào dữ liệu được mong đợi từ hàng chục nơi khác nhau;
• cần có khả năng sửa đổi và đọc dữ liệu đồng thời;
• cần đảm bảo các quy định về hình thành và tính toàn vẹn của dữ liệu;

...và bạn sẽ phải đối mặt với những công việc đòi hỏi trình độ chuyên môn và thời gian phù hợp để thực hiện. Không cần thiết phải “phát minh lại bánh xe”. Phần lớn đã được nghĩ ra và viết cho chúng tôi. Vì vậy, chúng tôi sẽ sử dụng những gì đã được thử nghiệm trong nhiều năm.

Lưu trữ dữ liệu trong Java

Hãy xem xét hành động. Trong Java, chúng tôi đã tạo một lớp cụ thể cho mô hình này Sharevới các trường name, changeProbability, startPrice, delta. Và nhiều lượt chia sẻ được lưu trữ dưới dạng Map<Long, Share>, trong đó khóa là mã định danh duy nhất cho mỗi lượt chia sẻ.

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 ));

Để truy cập khuyến mãi mong muốn bằng ID, hãy sử dụng phương thức shares.get(id). Đối với nhiệm vụ tìm kiếm một cổ phiếu theo tên hoặc giá, chúng ta sẽ duyệt qua tất cả các bản ghi để tìm thứ chúng ta cần, v.v. Nhưng chúng ta sẽ làm theo cách khác và lưu trữ các giá trị trong DBMS.

Lưu trữ dữ liệu trong DBMS

Chúng ta hãy xây dựng một bộ quy tắc lưu trữ dữ liệu ban đầu cho DBMS:

• Dữ liệu trong DBMS được tổ chức thành các bảng ( TABLE ), là một tập hợp các bản ghi.
• Tất cả các bản ghi đều có cùng một bộ trường. Chúng được thiết lập khi tạo bảng.
• Trường này có thể được đặt thành giá trị mặc định ( DEFAULT ).
• Đối với một bảng, bạn có thể đặt các ràng buộc ( CONSTRAINT ) mô tả các yêu cầu đối với dữ liệu của bảng đó để đảm bảo tính toàn vẹn của chúng. Điều này có thể được thực hiện ở giai đoạn tạo bảng ( CREATE TABLE ) hoặc được thêm vào sau ( ALTER TABLE ... ADD CONSTRAINT ).
• Ràng buộc phổ biến nhất :
  • Khóa chính là PRIMARY (Id trong trường hợp của chúng tôi).
  • Trường giá trị duy nhất UNIQUE (VIN cho bảng xe).
  • Kiểm tra trường KIỂM TRA (giá trị phần trăm không thể lớn hơn 100). Một trong những hạn chế riêng tư đối với một trường là KHÔNG NULL hoặc NULL , cấm/cho phép lưu trữ NULL trong trường bảng.
  • Liên kết tới bảng của bên thứ ba FOREIGN KEY (liên kết đến một cổ phiếu trong bảng giá cổ phiếu).
  • Index INDEX (lập chỉ mục một trường để tăng tốc độ tìm kiếm các giá trị trong đó).
  • Việc sửa đổi một bản ghi ( INSERT , UPDATE ) sẽ không xảy ra nếu giá trị của các trường của nó mâu thuẫn với các hạn chế (CONSTRAINT).
• Mỗi bảng có thể có một (hoặc một số) trường khóa có thể được sử dụng để nhận dạng duy nhất một bản ghi. Một trường như vậy (hoặc các trường, nếu chúng tạo thành khóa tổng hợp) tạo thành khóa chính của bảng - PRIMARY KEY .
  • Khóa chính đảm bảo tính duy nhất của một bản ghi trong bảng; một chỉ mục được tạo trên đó, cho phép truy cập nhanh vào toàn bộ bản ghi dựa trên giá trị khóa.
  • Việc có khóa chính giúp tạo liên kết giữa các bảng dễ dàng hơn nhiều. Tiếp theo, chúng ta sẽ sử dụng khóa chính nhân tạo: đối với bản ghi đầu tiên id = 1, mỗi bản ghi tiếp theo sẽ được chèn vào bảng với giá trị id tăng thêm một. Khóa này thường được gọi là AutoIncrement hoặc AutoIdentity .

Trên thực tế, một bảng cổ phiếu: Có thể sử dụng tên cổ phiếu làm chìa khóa trong trường hợp này không? Nhìn chung - đúng vậy, nhưng có khả năng một số công ty phát hành các cổ phiếu khác nhau và chỉ gọi chúng bằng tên riêng của mình. Trong trường hợp này sẽ không còn tính duy nhất nữa. Trong thực tế, khóa chính nhân tạo được sử dụng khá thường xuyên. Đồng ý, việc sử dụng tên đầy đủ làm khóa duy nhất trong bảng chứa các bản ghi về người sẽ không đảm bảo tính duy nhất. Cũng như sử dụng kết hợp họ tên và ngày sinh.

Các kiểu dữ liệu trong DBMS

Giống như bất kỳ ngôn ngữ lập trình nào khác, SQL có tính năng gõ dữ liệu. Dưới đây là các kiểu dữ liệu SQL phổ biến nhất: Kiểu số nguyên Thực tế Sợi dây ngày và giờ Lưu trữ khối lượng lớn dữ liệu

phong cách viết SQL

Đối với nhiều ngôn ngữ, có các nguyên tắc định dạng mã. Thông thường, những tài liệu như vậy chứa các quy tắc đặt tên biến, hằng, phương thức và các cấu trúc ngôn ngữ khác. Vì vậy, đối với Python có PEP8, đối với Java - Oracle Code Conventions for Java . Một số bộ khác nhau đã được tạo cho SQL, chúng hơi khác nhau một chút. Dù thế nào đi nữa, bạn nên phát triển thói quen tuân theo các quy tắc khi định dạng mã của mình, đặc biệt nếu bạn làm việc theo nhóm. Ví dụ: các quy tắc có thể như sau (tất nhiên, bạn có thể phát triển một bộ quy tắc khác cho riêng mình, điều quan trọng chính là tuân thủ chúng trong tương lai):

• Từ khóa và từ dành riêng, bao gồm các lệnh và toán tử phải được viết bằng chữ in hoa: TẠO BẢNG, CONSTRAINT...
• Tên của bảng, trường và các đối tượng khác không được trùng với từ khóa của ngôn ngữ SQL (xem liên kết ở cuối bài viết) nhưng có thể chứa chúng.
• Tên bảng phải phản ánh mục đích của chúng. Chúng được viết bằng chữ thường. Các từ trong tên được phân cách nhau bằng dấu gạch dưới. Từ ở cuối phải ở số nhiều : Trader (thương nhân), share_rates (share rate).
• Tên trường bảng phải phản ánh mục đích của chúng. Chúng phải được viết bằng chữ thường, các từ trong tên phải được định dạng theo kiểu Camel Case và từ ở cuối phải dùng ở dạng số ít : name (tên), share_rates (share rate).
• Các trường khóa nhân tạo phải chứa từ id.
• Tên CONSTRAINT phải tuân theo quy ước đặt tên bảng. Chúng cũng phải bao gồm các trường và bảng liên quan đến chúng, bắt đầu bằng tiền tố ngữ nghĩa: check_ (kiểm tra giá trị trường), pk_ (khóa chính), fk_ (khóa ngoại), uniq_ (tính duy nhất của trường), idx_ (chỉ mục). Ví dụ: pk_traider_share_actions_id (khóa chính trên trường id của bảng Trader_share_actions).
• Và cứ thế, khi bạn học SQL, danh sách các quy tắc sẽ được bổ sung/thay đổi.

thiết kế cơ sở dữ liệu

Ngay trước khi tạo DBMS, nó cần được thiết kế. Lược đồ cuối cùng chứa các bảng, một tập hợp các trường, CONSTRAINT, khóa, điều kiện mặc định cho các trường, mối quan hệ giữa các bảng và các thực thể cơ sở dữ liệu khác. Trên Internet, bạn có thể tìm thấy nhiều nhà thiết kế trực tuyến/ngoại tuyến miễn phí để thiết kế các DBMS nhỏ. Hãy thử nhập nội dung nào đó như “Miễn phí thiết kế cơ sở dữ liệu” vào công cụ tìm kiếm. Các ứng dụng như vậy có các thuộc tính bổ sung hữu ích:

• Có thể tạo các lệnh SQL để tạo DBMS.
• Hiển thị trực quan các cài đặt trên sơ đồ.
• Cho phép bạn di chuyển các bảng để hiển thị tốt hơn.
• Hiển thị các khóa, chỉ mục, mối quan hệ, giá trị mặc định và những thứ tương tự trên sơ đồ.
• Họ có thể lưu trữ lược đồ DBMS từ xa.

Ví dụ: dbdiffo.com đánh dấu các khóa, hiển thị các trường không trống và bộ đếm AI (AutoIncrement) có nhãn NN:

Tạo bảng trong DBMS

Vậy là chúng ta có một sơ đồ. Bây giờ chúng ta chuyển sang phần tạo bảng (CREATE TABLE). Để làm được điều này, chúng ta nên có dữ liệu sơ bộ:

• tên bảng
• tên trường và loại
• hạn chế (CONSTRAINTS) trên các trường
• giá trị mặc định cho các trường (nếu có)
• khóa chính (PRIMARY KEY) nếu có
• kết nối giữa các bảng (KEY NGOẠI TỆ)

Chúng ta sẽ không nghiên cứu chi tiết tất cả các tùy chọn của lệnh CREATE TABLE; chúng ta sẽ xem xét những điều cơ bản về SQL bằng cách sử dụng ví dụ về tạo bảng dành cho nhà giao dịch:

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)

Chúng ta hãy xem xét kỹ hơn:

• CREATE TABLE traiders(mô tả trường) - tạo một bảng có tên được chỉ định; trong mô tả, các trường được phân tách bằng dấu phẩy. Bất kỳ lệnh nào đều kết thúc bằng dấu chấm phẩy.
• Mô tả trường bắt đầu bằng tên của nó, theo sau là loại, Ràng buộc và giá trị mặc định.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– trường id của loại số nguyên là khóa chính và bộ đếm tăng dần (đối với mỗi bản ghi mới cho trường id, một giá trị sẽ được tạo lớn hơn giá trị được tạo trước đó cho bảng này một giá trị).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– trường tiền mặt, số thập phân, 15 chữ số trước dấu thập phân và hai chữ số sau (dữ liệu tài chính, ví dụ: đô la và xu). Không thể chấp nhận giá trị NULL. Nếu không có giá trị nào được đưa ra, nó sẽ nhận được giá trị 1000.
• about VARCHAR(255) NULL– trường about, một chuỗi dài tối đa 255 ký tự, có thể chấp nhận các giá trị trống.

Lưu ý rằng chúng ta có thể đặt một phần điều kiện CONSTRAINT sau khi tạo bảng. Chúng ta hãy xem xét cấu trúc để sửa đổi cấu trúc bảng và các trường của nó: ALTER TABLE tên_bảng THÊM Ràng buộc ràng buộc_tên KIỂM TRA (điều kiện) bằng các ví dụ:

• CHECK(tradingMethod IN (1,2,3))– trường TradingMethod chỉ có thể lấy giá trị 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– trường ChangeProbability có thể lấy các giá trị nguyên trong phạm vi từ 1 đến 100

Mối quan hệ giữa các bảng

Để phân tích mô tả mối quan hệ giữa các bảng, hãy xem việc tạo 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)

Tham chiếu đến các giá trị của một bảng khác có thể được đặt như sau: ALTER TABLEtable_from_which_referred ADD FOREIGN KEY(field_that_referred) REFERENCEStable_to_which_referred (field_that_referred to) Cho phép chia sẻ , chúng tôi có bản ghi về cổ phiếu, ví dụ: đối với id=50, chúng tôi lưu trữ cổ phiếu Microsoft với giá ban đầu là 17,5, delta là 20 và khả năng thay đổi là 4%. Đối với bảng share_rates , chúng ta có ba thuộc tính chính:

• Chúng ta chỉ cần lưu trữ giá trị của khóa id từ bảng chia sẻ trong trường chia sẻ để sử dụng nó nhằm lấy thông tin còn lại (tên, v.v.) từ bảng chia sẻ.
• Chúng tôi không thể tạo giá cho một chương trình khuyến mãi không tồn tại. Bạn không thể chèn một giá trị không tồn tại vào trường chia sẻ (không có bản ghi nào trong bảng chia sẻ có id này), vì sẽ không có sự tương ứng giữa các bảng.
• Chúng tôi không thể xóa mục chia sẻ trong các cổ phiếu có tỷ lệ được đặt trong share_rates.

Hai điểm cuối cùng phục vụ để đảm bảo tính toàn vẹn của dữ liệu được lưu trữ. Bạn có thể xem việc tạo các bảng SQL mô phỏng của chúng tôi và các ví dụ về truy vấn SQL trong quá trình triển khai Java các phương thức của các lớp tương ứng bằng cách sử dụng liên kết đến kho lưu trữ github ở cuối bài viết. Phần thứ ba