Bahagi 2. Istraktura ng DBMS, mga talahanayan at mga uri ng data

Unang parte Patuloy kaming gumagawa ng aming simpleng stock exchange emulator. Narito ang ating gagawin:

• Gumawa tayo ng diagram ng organisasyon ng database.
• Ilalarawan namin kung ano, paano at saan ito nakaimbak.
• Alamin natin kung paano nauugnay ang data sa isa't isa.
• Simulan natin ang pag-aaral ng mga pangunahing kaalaman sa SQL gamit ang halimbawa ng SQL table creation command CREATE TABLE , Data Definition Language ( DDL ) ng SQL na wika.
• Ipagpatuloy natin ang pagsusulat ng Java program. Ipinapatupad namin ang mga pangunahing function ng DBMS sa mga tuntunin ng java.sql para sa paglikha ng aming database sa programmatically, gamit ang JDBC at isang three-tier na arkitektura.

Ang dalawang bahaging ito ay naging mas malaki, dahil kailangan nating pamilyar sa mga pangunahing kaalaman ng SQL at ang organisasyon ng isang DBMS mula sa loob, at gumuhit ng mga pagkakatulad sa Java. Upang hindi ka mainip sa mga listahan ng code, sa dulo ay may mga link sa kaukulang commit github repository sa programa.

Disenyo ng DBMS

Paglalarawan ng Application

Narinig mo na na ang pag-aayos ng imbakan ng data ay isang mahalagang bahagi ng programming. Hayaan akong ipaalala sa iyo na ang layunin ng aming aplikasyon ay ang pinakasimpleng exchange emulation:

• May mga share na ang halaga ay maaaring magbago sa araw ng pangangalakal ayon sa ibinigay na mga patakaran;
• may mga mangangalakal na may paunang kapital;
• ang mga mangangalakal ay maaaring bumili at magbenta ng mga pagbabahagi ayon sa kanilang algorithm.

Ang palitan ay nagpapatakbo sa mga ticks - nakapirming tagal ng panahon (sa aming kaso - 1 minuto). Sa panahon ng isang tik, ang presyo ng stock ay maaaring magbago, at pagkatapos ay ang mangangalakal ay maaaring bumili o magbenta ng mga pagbabahagi.

Istruktura ng data ng emulation ng exchange

Tawagan natin ang mga modelo ng indibidwal na exchange entity. Upang maiwasan ang mga error sa pag-round, gagawa kami ng mga halagang pinansyal sa pamamagitan ng isang klase BigDecimal(makikita ang mga detalye sa link sa dulo ng artikulo). Ilarawan natin ang istraktura ng bawat modelo nang mas detalyado: Promosyon: Presyo ng pagbabahagi: mangangalakal: Mga aksyon ng mangangalakal: Upang matiyak ang pagiging natatangi ng bawat modelo, magdaragdag kami ng attribute idna may uri na long . Magiging natatangi ang attribute na ito sa loob ng mga instance ng modelo at natatanging makikilala ito. Maaaring gamitin ng mga katangiang tumutukoy sa iba pang mga modelo (negosyante, stock, presyo ng stock) ang isang ito idupang natatanging tukuyin ang kaukulang modelo. Ang pag-iisip ay agad na pumasok sa isip na maaari naming gamitin Map<Long, Object> upang mag-imbak ng naturang data, kung saan Objectang kaukulang modelo. Gayunpaman, subukang ipatupad ito sa code sa ilalim ng mga sumusunod na kundisyon:

• ang laki ng data ay makabuluhang lumampas sa dami ng magagamit na RAM;
• ang pag-access sa data ay inaasahan mula sa isang dosenang iba't ibang mga lugar;
• ang kakayahang sabay na baguhin at basahin ang data ay kinakailangan;
• kinakailangan upang matiyak ang mga patakaran para sa pagbuo at integridad ng data;

...at ikaw ay haharap sa mga gawain na nangangailangan ng wastong kwalipikasyon at oras upang maipatupad. Hindi na kailangang "muling baguhin ang gulong". Marami nang naisip at naisulat para sa atin. Kaya gagamitin natin ang nasubok na sa mga nakaraang taon.

Pag-iimbak ng Data sa Java

Isaalang-alang natin ang aksyon. Sa Java, lumikha kami ng isang partikular na klase para sa modelong ito Sharena may mga field name, changeProbability, startPrice, delta. At maraming share ang na-store bilang Map<Long, Share>, kung saan ang susi ay isang natatanging identifier para sa bawat 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 ));

Upang ma-access ang gustong promosyon sa pamamagitan ng ID, gamitin ang paraan shares.get(id). Para sa gawain ng paghahanap ng stock ayon sa pangalan o presyo, susuriin namin ang lahat ng mga talaan na naghahanap ng kailangan namin, at iba pa. Ngunit pupunta kami sa ibang paraan at iimbak ang mga halaga sa DBMS.

Imbakan ng data sa isang DBMS

Bumuo tayo ng isang paunang hanay ng mga panuntunan sa pag-iimbak ng data para sa isang DBMS:

• Ang data sa isang DBMS ay isinaayos sa mga talahanayan ( TABLE ), na isang hanay ng mga talaan.
• Ang lahat ng mga tala ay may parehong hanay ng mga patlang. Nakatakda ang mga ito kapag lumilikha ng talahanayan.
• Maaaring itakda ang field sa isang default na halaga ( DEFAULT ).
• Para sa isang talahanayan, maaari kang magtakda ng mga hadlang ( CONSTRAINT ) na naglalarawan sa mga kinakailangan para sa data nito upang matiyak ang kanilang integridad. Magagawa ito sa yugto ng paglikha ng talahanayan ( CREATE TABLE ) o idinagdag sa ibang pagkakataon ( ALTER TABLE ... ADD CONSTRAINT ).
• Ang pinakakaraniwang CONSTRAINT :
  • Ang pangunahing susi ay PRIMARY (Id sa aming kaso).
  • Natatanging field ng halaga UNIQUE (VIN para sa talahanayan ng sasakyan).
  • Sinusuri ang field ng CHECK (ang halaga ng porsyento ay hindi maaaring higit sa 100). Isa sa mga pribadong paghihigpit sa isang field ay NOT NULL o NULL , na nagbabawal/nagbibigay-daan sa pag-imbak ng NULL sa isang field ng table.
  • Mag-link sa isang third-party na talahanayan FOREIGN KEY (link sa isang stock sa talahanayan ng presyo ng stock).
  • Index INDEX (pag-index ng isang patlang upang mapabilis ang paghahanap para sa mga halaga dito).
  • Ang pagbabago ng isang talaan ( INSERT , UPDATE ) ay hindi magaganap kung ang mga halaga ng mga field nito ay sumasalungat sa mga paghihigpit (CONSTRAINT).
• Ang bawat talahanayan ay maaaring magkaroon ng isang pangunahing field (o ilan) na maaaring magamit upang natatanging tukuyin ang isang tala. Ang nasabing field (o mga field, kung bumubuo sila ng composite key) ay bumubuo sa pangunahing key ng table - PRIMARY KEY .
  • Tinitiyak ng pangunahing susi ang pagiging natatangi ng isang tala sa talahanayan; ang isang index ay nilikha dito, na nagbibigay ng mabilis na pag-access sa buong tala batay sa halaga ng susi.
  • Ang pagkakaroon ng pangunahing key ay nagpapadali sa paggawa ng mga link sa pagitan ng mga talahanayan. Susunod, gagamit kami ng artipisyal na pangunahing key: para sa unang tala id = 1, ang bawat kasunod na tala ay ilalagay sa talahanayan na ang halaga ng id ay tumaas ng isa. Ang key na ito ay madalas na tinatawag na AutoIncrement o AutoIdentity .

Talaga, isang talahanayan ng mga stock: Posible bang gamitin ang pangalan ng stock bilang isang susi sa kasong ito? Sa pangkalahatan - oo, ngunit may posibilidad na ang ilang kumpanya ay nag-isyu ng iba't ibang mga pagbabahagi at tinatawag lamang sila sa sarili nitong pangalan. Sa kasong ito, hindi na magkakaroon ng uniqueness. Sa pagsasagawa, ang isang artipisyal na pangunahing susi ay madalas na ginagamit. Sumang-ayon, ang paggamit ng buong pangalan bilang isang natatanging susi sa isang talahanayan na naglalaman ng mga talaan ng mga tao ay hindi makatitiyak ng pagiging natatangi. Pati na rin ang paggamit ng kumbinasyon ng buong pangalan at petsa ng kapanganakan.

Mga uri ng data sa DBMS

Tulad ng iba pang programming language, ang SQL ay may data type. Narito ang mga pinakakaraniwang uri ng data ng SQL: Mga uri ng integer totoo String petsa at oras Imbakan ng malalaking volume ng data

Estilo ng pagsulat ng SQL

Para sa maraming wika, mayroong mga alituntunin sa pag-format ng code. Karaniwan, ang mga naturang dokumento ay naglalaman ng mga panuntunan para sa pagbibigay ng pangalan sa mga variable, constant, pamamaraan at iba pang istruktura ng wika. Kaya, para sa Python mayroong PEP8, para sa Java - Oracle Code Conventions para sa Java . Maraming iba't ibang set ang ginawa para sa SQL, na bahagyang naiiba sa bawat isa. Anuman, dapat mong gawin ang ugali ng pagsunod sa mga panuntunan kapag pino-format ang iyong code, lalo na kung nagtatrabaho ka sa isang koponan. Ang mga patakaran ay maaaring, halimbawa, ang mga sumusunod (siyempre, maaari kang bumuo ng ibang hanay ng mga patakaran para sa iyong sarili, ang pangunahing bagay ay manatili sa kanila sa hinaharap):

• Ang mga keyword at nakalaan na salita, kabilang ang mga command at operator, ay dapat na nakasulat sa malalaking titik: GUMAWA NG TABLE, CONSTRAINT...
• Ang mga pangalan ng mga talahanayan, mga patlang at iba pang mga bagay ay hindi dapat tumugma sa mga keyword sa wikang SQL (tingnan ang link sa dulo ng artikulo), ngunit maaaring naglalaman ng mga ito.
• Dapat ipakita ng mga pangalan ng talahanayan ang kanilang layunin. Ang mga ito ay nakasulat sa maliliit na titik. Ang mga salita sa pangalan ay pinaghihiwalay sa bawat isa sa pamamagitan ng mga salungguhit. Ang salita sa dulo ay dapat nasa pangmaramihang : mangangalakal (traders), share_rates (share rate).
• Dapat ipakita ng mga pangalan ng field ng table ang kanilang layunin. Dapat na nakasulat ang mga ito sa maliliit na titik, ang mga salita sa pangalan ay dapat na naka-format sa istilo ng Camel Case , at ang salita sa dulo ay dapat gamitin sa isahan : pangalan (pangalan), share_rates (share rate).
• Ang mga artipisyal na key field ay dapat maglaman ng salitang id.
• Ang mga pangalan ng CONSTRAINT ay dapat sumunod sa mga kumbensyon sa pagbibigay ng pangalan sa talahanayan. Dapat din nilang isama ang mga patlang at talahanayan na kasangkot sa mga ito, magsimula sa isang semantic prefix: check_ (pagsusuri sa halaga ng field), pk_ (pangunahing key), fk_ (foreign key), uniq_ (field uniqueness), idx_ (index). Halimbawa: pk_traider_share_actions_id (primary key sa id field para sa trader_share_actions table).
• At iba pa, habang pinag-aaralan mo ang SQL, ang listahan ng mga panuntunan ay mapupunan/mababago.

Disenyo ng DBMS

Kaagad bago lumikha ng isang DBMS, kailangan itong idisenyo. Ang huling schema ay naglalaman ng mga talahanayan, isang set ng mga field, CONSTRAINT, mga key, mga default na kondisyon para sa mga field, mga relasyon sa pagitan ng mga talahanayan at iba pang mga entity ng database. Sa Internet makakahanap ka ng maraming libreng online/offline na designer para sa pagdidisenyo ng maliliit na DBMS. Subukang mag-type ng isang bagay tulad ng "Libreng taga-disenyo ng database" sa isang search engine. Ang ganitong mga application ay may mga kapaki-pakinabang na karagdagang katangian:

• Maaaring bumuo ng mga SQL command upang lumikha ng isang DBMS.
• Biswal na ipakita ang mga setting sa diagram.
• Binibigyang-daan kang ilipat ang mga talahanayan para sa mas mahusay na visualization.
• Ipakita ang mga key, index, relasyon, default na halaga, at mga katulad nito sa diagram.
• Malayuan nilang maiimbak ang DBMS schema.

Halimbawa, ang dbdiffo.com ay nagha-highlight ng mga key, nagpapakita ng mga walang laman na field at AI (AutoIncrement) na mga counter na may label na NN:

Paglikha ng mga talahanayan sa isang DBMS

Kaya mayroon kaming isang diagram. Ngayon ay lumipat tayo sa paglikha ng mga talahanayan (GUMAWA NG TABLE). Para magawa ito, ipinapayong magkaroon tayo ng paunang data:

• pangalan ng talahanayan
• mga pangalan at uri ng field
• mga paghihigpit (CONSTRAINTS) sa mga field
• mga default na halaga para sa mga patlang (kung magagamit)
• pangunahing susi (PANGUNAHING SUSI) kung magagamit
• mga koneksyon sa pagitan ng mga talahanayan (FOREIGN KEY)

Hindi namin pag-aralan nang detalyado ang lahat ng mga opsyon ng CREATE TABLE command; titingnan namin ang mga pangunahing kaalaman ng SQL gamit ang halimbawa ng paglikha ng table para sa mga mangangalakal:

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)

Tingnan natin nang mas malapitan:

• CREATE TABLE traiders(field description) - lumilikha ng isang talahanayan na may tinukoy na pangalan; sa paglalarawan, ang mga patlang ay pinaghihiwalay ng isang kuwit. Anumang utos ay nagtatapos sa isang semicolon.
• Nagsisimula ang paglalarawan ng field sa pangalan nito, na sinusundan ng uri nito, CONSTRAINT, at default na halaga.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– ang field ng id ng isang uri ng integer ay isang pangunahing key at isang incremental na counter (para sa bawat bagong tala para sa field ng id, bubuo ng isang halaga na mas malaki kaysa sa naunang ginawa para sa talahanayang ito).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– cash field, decimal, 15 digit bago ang decimal point at dalawa pagkatapos (financial data, halimbawa, dollars at cents). Hindi matanggap ang NULL values. Kung walang ibinigay na halaga, makukuha nito ang halaga na 1000.
• about VARCHAR(255) NULL– ang about field, isang string na hanggang 255 characters ang haba, ay maaaring tumanggap ng mga walang laman na value.

Tandaan na maaari kaming magtakda ng bahagi ng mga kundisyon ng CONSTRAINT pagkatapos gawin ang talahanayan. Isaalang-alang natin ang pagbuo para sa pagbabago ng istraktura ng talahanayan at ang mga patlang nito: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (kondisyon) gamit ang mga halimbawa:

• CHECK(tradingMethod IN (1,2,3))– ang larangan ng tradingMethod ay maaari lamang kumuha ng mga halaga 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– ang changeProbability field ay maaaring kumuha ng mga integer na halaga sa hanay mula 1 hanggang 100

Mga ugnayan sa pagitan ng mga talahanayan

Upang pag-aralan ang paglalarawan ng mga ugnayan sa pagitan ng mga talahanayan, tingnan natin ang paglikha ng 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)

Ang isang link sa mga halaga ng isa pang talahanayan ay maaaring itakda tulad ng sumusunod: ALTER TABLEtable_from_which_is_referred ADD FOREIGN KEY(field_which_referred) REFERENCEStable_to_which_is_referenced (field_which_is_referenced) Ipasok ang mga share na mayroon kaming mga record sa shares, halimbawa, para sa id=50 nag-iimbak kami ng Microsoft shares na may paunang presyo na 17. , delta 20 at isang pagkakataon ng pagbabago ng 4%. Para sa talahanayan ng share_rates nakakakuha kami ng tatlong pangunahing katangian:

• Kailangan lang nating iimbak sa field ng pagbabahagi lamang ang halaga ng id key mula sa talahanayan ng mga pagbabahagi upang magamit ito upang makuha ang natitirang impormasyon (pangalan, atbp.) mula sa talahanayan ng mga pagbabahagi.
• Hindi kami makakagawa ng rate para sa isang hindi umiiral na promosyon. Hindi ka maaaring magpasok ng isang hindi umiiral na halaga sa patlang ng pagbabahagi (kung saan walang talaan sa talahanayan ng mga pagbabahagi na may id na ito), dahil walang magiging sulat sa pagitan ng mga talahanayan.
• Hindi kami makakapagtanggal ng share entry sa mga share kung saan nakatakda ang mga rate sa share_rates.

Ang huling dalawang punto ay nagsisilbi upang matiyak ang integridad ng nakaimbak na data. Makikita mo ang paglikha ng mga SQL table ng aming emulation at mga halimbawa ng SQL query sa pagpapatupad ng Java ng mga pamamaraan ng kaukulang mga klase gamit ang link sa github repository sa dulo ng artikulo. Ang ikatlong bahagi