Część 2. Struktura DBMS, tablice i typy danych

Pierwsza część Kontynuujemy tworzenie naszego prostego emulatora giełdy. Oto, co zrobimy:

• Stwórzmy diagram organizacji bazy danych.
• Opiszemy co, jak i gdzie jest przechowywane.
• Dowiedzmy się, jak dane są ze sobą powiązane.
• Zacznijmy naukę podstaw języka SQL na przykładzie polecenia tworzenia tabeli SQL CREATE TABLE , języka definicji danych ( DDL ) języka SQL.
• Kontynuujmy pisanie programu w Javie. Realizujemy główne funkcje DBMS w zakresie java.sql do programowego tworzenia naszej bazy danych, przy użyciu JDBC i architektury trójwarstwowej.

Te dwie części okazały się obszerniejsze, gdyż trzeba zapoznać się z podstawami SQL i organizacją DBMS od środka oraz wyciągnąć analogie z Javą. Aby nie zanudzać Cię listami kodów, na końcu znajdują się linki do odpowiedniego repozytorium github z zatwierdzeniami z programem.

Projekt DBMS

Opis aplikacji

Słyszeliście już, że organizacja przechowywania danych jest integralną częścią programowania. Przypomnę, że celem naszej aplikacji jest najprostsza emulacja wymiany:

• Są akcje, których wartość może zmieniać się w ciągu dnia handlowego zgodnie z określonymi zasadami;
• są handlowcy z kapitałem początkowym;
• inwestorzy mogą kupować i sprzedawać akcje zgodnie ze swoim algorytmem.

Giełda działa w tikach – stałych odstępach czasu (w naszym przypadku – 1 minuta). Podczas ticku cena akcji może się zmienić, a następnie trader może kupić lub sprzedać akcje.

Struktura danych emulacji Exchange

Nazwijmy modelami poszczególnych podmiotów wymiany. Aby uniknąć błędów w zaokrągleniach, z kwotami finansowymi będziemy pracować na zajęciach BigDecimal(szczegóły w linku na końcu artykułu). Opiszmy bardziej szczegółowo budowę każdego modelu: Promocja: Cena akcji: Handlowiec: Działania tradera: Aby zapewnić niepowtarzalność każdego modelu, dodamy atrybut idtypu long . Ten atrybut będzie unikalny w obrębie instancji modelu i będzie go jednoznacznie identyfikował. Atrybuty odnoszące się do innych modeli (handlowiec, akcje, cena akcji) mogą wykorzystać ten atrybut iddo jednoznacznej identyfikacji odpowiedniego modelu. Od razu przychodzi mi do głowy myśl, że moglibyśmy wykorzystać Map<Long, Object> do przechowywania takich danych, gdzie Objectjest odpowiedni model. Spróbuj jednak zaimplementować to w kodzie pod następującymi warunkami:

• rozmiar danych znacznie przekracza ilość dostępnej pamięci RAM;
• przewidywany jest dostęp do danych z kilkunastu różnych miejsc;
• wymagana jest możliwość jednoczesnej modyfikacji i odczytu danych;
• konieczne jest zapewnienie zasad tworzenia i integralności danych;

...a przed Tobą staną zadania wymagające odpowiednich kwalifikacji i czasu na realizację. Nie ma potrzeby „wymyślać koła na nowo”. Wiele już zostało dla nas przemyślane i napisane. Wykorzystamy więc to, co zostało już przetestowane przez lata.

Przechowywanie danych w Javie

Rozważmy działanie. W Javie stworzyliśmy dla tego modelu specjalną klasę Sharez polami name, changeProbability, startPrice, delta. Wiele udziałów było przechowywanych jako Map<Long, Share>, gdzie klucz jest unikalnym identyfikatorem każdego udziału.

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

Aby uzyskać dostęp do żądanej promocji po identyfikatorze, użyj metody shares.get(id). Aby znaleźć akcje według nazwy lub ceny, przeglądalibyśmy wszystkie rekordy w poszukiwaniu tej, której potrzebujemy, i tak dalej. Ale pójdziemy w drugą stronę i zapiszemy wartości w DBMS.

Przechowywanie danych w systemie DBMS

Sformułujmy początkowy zestaw zasad przechowywania danych dla DBMS:

• Dane w systemie DBMS są zorganizowane w tabele ( TABLE ), które stanowią zbiór rekordów.
• Wszystkie rekordy mają ten sam zestaw pól. Są one ustawiane podczas tworzenia tabeli.
• Pole może mieć ustawioną wartość domyślną ( DEFAULT ).
• Dla tabeli można ustawić ograniczenia ( CONSTRAINT ), które opisują wymagania dotyczące jej danych w celu zapewnienia ich integralności. Można to zrobić na etapie tworzenia tabeli ( CREATE TABLE ) lub dodać później ( ALTER TABLE ... ADD CONSTRAINT ).
• Najczęstsze OGRANICZENIE :
  • Kluczem podstawowym jest PRIMARY (w naszym przypadku identyfikator).
  • Unikalne pole wartości UNIQUE (VIN dla tabeli pojazdów).
  • Sprawdzenie pola CHECK (wartość procentowa nie może być większa niż 100). Jednym z prywatnych ograniczeń pola jest NOT NULL lub NULL , co zabrania/umożliwia przechowywanie wartości NULL w polu tabeli.
  • Link do tabeli obcej KLUCZ ZAGRANICZNY (link do akcji w tabeli cen akcji).
  • Indeks INDEX (indeksowanie pola w celu przyspieszenia wyszukiwania w nim wartości).
  • Modyfikacja rekordu ( INSERT , UPDATE ) nie nastąpi, jeśli wartości jego pól są sprzeczne z ograniczeniami (CONSTRAINT).
• Każda tabela może mieć pole kluczowe (lub kilka), których można użyć do jednoznacznej identyfikacji rekordu. Takie pole (lub pola, jeśli tworzą klucz złożony) tworzy klucz podstawowy tabeli - KLUCZ PODSTAWOWY .
  • Klucz podstawowy zapewnia niepowtarzalność rekordu w tabeli, tworzony jest na nim indeks, który umożliwia szybki dostęp do całego rekordu na podstawie wartości klucza.
  • Posiadanie klucza podstawowego znacznie ułatwia tworzenie łączy między tabelami. Następnie użyjemy sztucznego klucza podstawowego: dla pierwszego rekordu id = 1każdy kolejny rekord będzie wstawiany do tabeli z wartością id zwiększoną o jeden. Ten klucz jest często nazywany AutoInkrementacją lub AutoIdentity .

Właściwie to tabela akcji: czy w tym przypadku można użyć nazwy akcji jako klucza? W zasadzie tak, ale istnieje możliwość, że jakaś spółka emituje inne akcje i nazywa je tylko swoją nazwą. W tym przypadku nie będzie już wyjątkowości. W praktyce dość często stosuje się sztuczny klucz podstawowy. Zgadzam się, użycie imienia i nazwiska jako unikalnego klucza w tabeli zawierającej rekordy osób nie zapewni niepowtarzalności. Jak również przy użyciu kombinacji pełnego imienia i nazwiska oraz daty urodzenia.

Typy danych w DBMS

Jak każdy inny język programowania, SQL umożliwia wpisywanie danych. Oto najpopularniejsze typy danych SQL: Typy całkowite Prawdziwy Strunowy Data i godzina Przechowywanie dużych ilości danych

Styl pisania SQL

W przypadku wielu języków istnieją wytyczne dotyczące formatowania kodu. Zazwyczaj takie dokumenty zawierają zasady nazewnictwa zmiennych, stałych, metod i innych struktur językowych. Tak więc dla Pythona istnieje PEP8, dla Java - Konwencje kodu Oracle dla Java . Dla języka SQL utworzono kilka różnych zestawów, które nieznacznie różnią się od siebie. Niezależnie od tego, powinieneś wyrobić sobie nawyk przestrzegania zasad podczas formatowania kodu, szczególnie jeśli pracujesz w zespole. Zasady mogłyby być na przykład następujące (oczywiście możesz opracować dla siebie inny zestaw zasad, najważniejsze jest, aby się ich trzymać w przyszłości):

• Słowa kluczowe i słowa zastrzeżone, w tym polecenia i operatory, muszą być pisane wielkimi literami: CREATE TABLE, CONSTRAINT...
• Nazwy tabel, pól i innych obiektów nie powinny pokrywać się ze słowami kluczowymi języka SQL (patrz link na końcu artykułu), ale mogą je zawierać.
• Nazwy tabel powinny odzwierciedlać ich przeznaczenie. Są pisane małymi literami. Słowa w nazwie oddzielane są od siebie podkreśleniami. Słowo na końcu musi być w liczbie mnogiej : traderzy (traderzy), share_rates (kurs akcji).
• Nazwy pól tabeli powinny odzwierciedlać ich przeznaczenie. Muszą być pisane małymi literami, słowa w nazwie muszą być sformatowane w stylu Camel Case , a słowo na końcu musi być użyte w liczbie pojedynczej : nazwa (nazwa), share_rates (kurs akcji).
• Pola klucza sztucznego muszą zawierać słowo id.
• Nazwy CONSTRAINT muszą być zgodne z konwencjami nazewnictwa tabel. Muszą także zawierać powiązane z nimi pola i tabele, zaczynać się od prefiksu semantycznego: check_ (sprawdzanie wartości pola), pk_ (klucz podstawowy), fk_ (klucz obcy), uniq_ (unikalność pola), idx_ (indeks). Przykład: pk_traider_share_actions_id (klucz podstawowy w polu id tabeli trader_share_actions).
• I tak dalej, w miarę studiowania SQL, lista reguł będzie uzupełniana/zmieniana.

Projekt DBMS

Bezpośrednio przed utworzeniem SZBD należy go zaprojektować. Ostateczny schemat zawiera tabele, zestaw pól, OGRANICZENIE, klucze, domyślne warunki dla pól, relacje pomiędzy tabelami i innymi podmiotami bazy danych. W Internecie można znaleźć wielu darmowych projektantów online/offline do projektowania małych systemów DBMS. Spróbuj wpisać w wyszukiwarce coś takiego jak „Database Designer free”. Takie aplikacje mają przydatne dodatkowe właściwości:

• Potrafi generować polecenia SQL w celu utworzenia systemu DBMS.
• Wizualnie wyświetl ustawienia na diagramie.
• Umożliwia przenoszenie tabel w celu lepszej wizualizacji.
• Pokaż klucze, indeksy, relacje, wartości domyślne i tym podobne na diagramie.
• Mogą zdalnie przechowywać schemat DBMS.

Na przykład dbdiffo.com podświetla klucze, pokazuje niepuste pola i liczniki AI (AutoInkrementacja) z etykietą NN:

Tworzenie tabel w systemie DBMS

Mamy więc diagram. Przejdźmy teraz do tworzenia tabel (CREATE TABLE). W tym celu wskazane jest, abyśmy posiadali wstępne dane:

• Nazwa tabeli
• nazwy pól i typ
• ograniczenia (CONSTRAINTS) na polach
• domyślne wartości pól (jeśli są dostępne)
• klucz podstawowy (KLUCZ PODSTAWOWY), jeśli jest dostępny
• połączenia między tabelami (KLUCZ OBCY)

Nie będziemy szczegółowo badać wszystkich opcji polecenia CREATE TABLE; przyjrzymy się podstawom SQL na przykładzie tworzenia tabeli dla traderów:

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)

Przyjrzyjmy się bliżej:

• CREATE TABLE traiders(opis pola) - tworzy tabelę o podanej nazwie, w opisie pola oddzielane są przecinkiem. Każde polecenie kończy się średnikiem.
• Opis pola rozpoczyna się od jego nazwy, po której następuje typ, CONSTRAINT i wartość domyślna.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– pole id typu integer jest kluczem podstawowym i licznikiem przyrostowym (dla każdego nowego rekordu dla pola id zostanie wygenerowana wartość o jeden większa od wcześniej utworzonej dla tej tabeli).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– pole kasowe, dziesiętne, 15 cyfr przed przecinkiem i dwie po nim (dane finansowe, np. dolary i centy). Nie można zaakceptować wartości NULL. Jeżeli nie zostanie podana żadna wartość, otrzyma ona wartość 1000.
• about VARCHAR(255) NULL– pole about o długości do 255 znaków może przyjmować wartości puste.

Należy pamiętać, że część warunków CONSTRAINT możemy ustawić po utworzeniu tabeli. Rozważmy konstrukcję modyfikacji struktury tabeli i jej pól: ALTER TABLE nazwa_tabeli ADD CONSTRAINT nazwa_ograniczenia CHECK (warunek) na przykładach:

• CHECK(tradingMethod IN (1,2,3))– pole tradingMethod może przyjmować wyłącznie wartości 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– pole ChangeProbability może przyjmować wartości całkowite z zakresu od 1 do 100

Relacje pomiędzy tabelami

Aby przeanalizować opis relacji między tabelami, przyjrzyjmy się tworzeniu 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)

Link do wartości innej tabeli można ustawić następująco: ALTER TABLEtable_from_ Which_is_referred ADD FOREIGN KEY(field_ Which_referred) REFERENCEStable_to_ Which_is_referenced (field_ Which_is_referenced) Niech w akcjach mamy rekordy na udziałach, np. dla id=50 przechowujemy akcje Microsoftu z ceną początkową 17,5 , delta 20 i szansa na zmianę 4%. Dla tabeli share_rates otrzymujemy trzy główne właściwości:

• Wystarczy, że w polu udziałów zapiszemy tylko wartość klucza id z tabeli udziałów, aby móc za jego pomocą uzyskać pozostałe informacje (nazwę itp.) z tabeli udziałów.
• Nie możemy stworzyć stawki dla nieistniejącej promocji. Nie możesz wstawić nieistniejącej wartości do pola udziału (dla którego w tabeli udziałów nie ma rekordu o tym identyfikatorze), ponieważ nie będzie żadnej zgodności pomiędzy tabelami.
• Nie możemy usunąć wpisu akcji w akcjach, dla których stawki są ustawione w share_rates.

Dwa ostatnie punkty służą zapewnieniu integralności przechowywanych danych. Tworzenie tabel SQL naszej emulacji oraz przykłady zapytań SQL w implementacji Java metod odpowiednich klas możesz zobaczyć korzystając z linku do repozytorium github na końcu artykułu. Trzecia część