بخش 2. ساختار DBMS، جداول و انواع داده ها

قسمت اول ما به ایجاد شبیه ساز بورس اوراق بهادار ساده خود ادامه می دهیم. در اینجا کاری است که ما انجام خواهیم داد:

• بیایید یک نمودار سازماندهی پایگاه داده ایجاد کنیم.
• ما توضیح خواهیم داد که چه چیزی، چگونه و کجا ذخیره می شود.
• بیایید دریابیم که چگونه داده ها با یکدیگر مرتبط هستند.
• بیایید شروع به یادگیری اصول اولیه SQL با استفاده از مثال دستور ایجاد جدول SQL CREATE TABLE ، زبان تعریف داده ( DDL ) زبان SQL کنیم.
• بیایید به نوشتن برنامه جاوا ادامه دهیم. ما توابع اصلی DBMS را از نظر java.sql برای ایجاد پایگاه داده خود به صورت برنامه نویسی، با استفاده از JDBC و معماری سه لایه پیاده سازی می کنیم.

این دو بخش حجم بیشتری پیدا کرد، زیرا ما باید خود را با اصول SQL و سازماندهی یک DBMS از داخل آشنا کنیم و با جاوا تشبیه کنیم. برای اینکه شما را با لیست کد خسته نکنیم، در پایان پیوندهایی به مخزن commit github مربوطه با برنامه وجود دارد.

طراحی DBMS

توضیحات برنامه

قبلاً شنیده اید که سازماندهی ذخیره سازی داده ها بخشی جدایی ناپذیر از برنامه نویسی است. اجازه دهید به شما یادآوری کنم که هدف از برنامه ما ساده ترین شبیه سازی تبادل است:

• سهامی وجود دارد که ارزش آنها طبق قوانین داده شده می تواند در طول روز معاملات تغییر کند.
• معامله گران با سرمایه اولیه وجود دارد.
• معامله گران می توانند بر اساس الگوریتم خود سهام بخرند و بفروشند.

صرافی در تیک ها عمل می کند - دوره های زمانی ثابت (در مورد ما - 1 دقیقه). در طی یک تیک، قیمت سهام ممکن است تغییر کند و سپس معامله‌گر ممکن است سهام را بخرد یا بفروشد.

تبادل ساختار داده های شبیه سازی

بیایید واحدهای مبادله فردی را مدل بنامیم. برای جلوگیری از خطاهای گرد کردن، از طریق یک کلاس با مبالغ مالی کار خواهیم کرد BigDecimal(جزئیات را می توانید در لینک انتهای مقاله مشاهده کنید). بیایید ساختار هر مدل را با جزئیات بیشتری شرح دهیم: ارتقا: قیمت سهم: معامله گر: اقدامات معامله گر: برای اطمینان از منحصر به فرد بودن هر مدل، یک ویژگی idاز نوع long اضافه می کنیم . این ویژگی در نمونه های مدل منحصر به فرد خواهد بود و آن را به طور منحصر به فرد شناسایی می کند. ویژگی هایی که به مدل های دیگر (معامله، سهام، قیمت سهام) اشاره می کنند، می توانند از این یکی idبرای شناسایی منحصر به فرد مدل مربوطه استفاده کنند. بلافاصله این فکر به ذهن خطور می کند که می توانیم از آن Map<Long, Object> برای ذخیره چنین داده هایی استفاده کنیم، Objectمدل مربوطه کجاست. با این حال، سعی کنید این را در کد تحت شرایط زیر پیاده سازی کنید:

• اندازه داده به طور قابل توجهی از مقدار RAM موجود بیشتر است.
• دسترسی به داده ها از ده ها مکان مختلف انتظار می رود.
• توانایی تغییر و خواندن همزمان داده ها مورد نیاز است.
• لازم است قوانینی برای تشکیل و یکپارچگی داده ها تضمین شود.

... و شما با کارهایی مواجه خواهید شد که برای اجرای آنها نیاز به صلاحیت و زمان مناسب دارد. نیازی به «اختراع مجدد چرخ» نیست. قبلاً خیلی برای ما فکر و نوشته شده است. بنابراین ما از آنچه قبلاً در طول سالها آزمایش شده است استفاده خواهیم کرد.

ذخیره سازی داده ها در جاوا

بیایید عمل را در نظر بگیریم. 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 ));

برای دسترسی به تبلیغ مورد نظر با شناسه، از روش استفاده کنید shares.get(id). برای کار پیدا کردن یک سهام بر اساس نام یا قیمت، تمام رکوردها را به دنبال مورد نیاز خود می‌گردیم و غیره. اما راه دیگر را می رویم و مقادیر را در DBMS ذخیره می کنیم.

ذخیره سازی داده ها در یک DBMS

اجازه دهید یک مجموعه اولیه از قوانین ذخیره سازی داده را برای یک DBMS فرموله کنیم:

• داده ها در یک DBMS در جداول ( TABLE ) سازماندهی می شوند که مجموعه ای از رکوردها هستند.
• همه رکوردها مجموعه فیلدهای یکسانی دارند. آنها هنگام ایجاد جدول تنظیم می شوند.
• فیلد را می توان روی یک مقدار پیش فرض ( DEFAULT ) تنظیم کرد.
• برای یک جدول، می‌توانید محدودیت‌هایی ( CONSTRAINT ) تنظیم کنید که الزامات داده‌های آن را برای اطمینان از یکپارچگی آن‌ها توصیف می‌کند. این را می توان در مرحله ایجاد جدول ( ایجاد جدول ) انجام داد یا بعداً اضافه کرد ( تغییر جدول ... ADD CONSTRAINT ).
• رایج ترین محدودیت :
  • کلید اصلی PRIMARY است (در مورد ما شناسه).
  • فیلد مقدار منحصر به فرد UNIQUE (VIN برای جدول خودرو).
  • بررسی فیلد CHECK (مقدار درصد نمی تواند بیشتر از 100 باشد). یکی از محدودیت های خصوصی در یک فیلد NOT NULL یا NULL است که ذخیره NULL را در یک فیلد جدول ممنوع/اجازه می دهد.
  • پیوند به جدول شخص ثالث FOREIGN KEY (پیوند به یک سهام در جدول قیمت سهام).
  • Index INDEX (نمایه گذاری یک فیلد برای سرعت بخشیدن به جستجوی مقادیر در آن).
  • اصلاح یک رکورد ( INSERT ، UPDATE ) در صورتی که مقادیر فیلدهای آن با محدودیت ها (CONSTRAINT) در تضاد باشد، رخ نخواهد داد.
• هر جدول می تواند یک فیلد کلیدی (یا چندین) داشته باشد که می تواند برای شناسایی منحصر به فرد یک رکورد استفاده شود. چنین فیلدی (یا فیلدهایی، اگر یک کلید ترکیبی تشکیل دهند) کلید اصلی جدول را تشکیل می دهد - PRIMARY KEY .
  • کلید اصلی منحصر به فرد بودن یک رکورد در جدول را تضمین می کند؛ یک شاخص روی آن ایجاد می شود که دسترسی سریع به کل رکورد را بر اساس مقدار کلید می دهد.
  • داشتن یک کلید اولیه ایجاد پیوند بین جداول را بسیار آسان تر می کند. در مرحله بعد، از یک کلید اولیه مصنوعی استفاده می کنیم: برای رکورد اول id = 1، هر رکورد بعدی با یک افزایش مقدار شناسه در جدول وارد می شود. این کلید اغلب AutoIncrement یا AutoIdentity نامیده می شود .

در واقع جدول سهام: آیا می توان در این مورد از نام سهام به عنوان کلید استفاده کرد؟ به طور کلی - بله، اما این احتمال وجود دارد که برخی از شرکت ها سهام متفاوتی را منتشر کنند و آنها را فقط به نام خود صدا کنند. در این صورت دیگر یکتایی وجود نخواهد داشت. در عمل، یک کلید اولیه مصنوعی اغلب استفاده می شود. موافقم، استفاده از یک نام کامل به عنوان یک کلید منحصر به فرد در جدولی که حاوی سوابق افراد است، منحصر به فرد بودن را تضمین نمی کند. و همچنین استفاده از ترکیب نام کامل و تاریخ تولد.

انواع داده ها در DBMS

مانند هر زبان برنامه نویسی دیگری، SQL دارای تایپ داده است. در اینجا رایج ترین انواع داده های SQL آمده است: انواع عدد صحیح واقعی رشته تاریخ و زمان ذخیره سازی حجم زیادی از داده ها

سبک نوشتن SQL

برای بسیاری از زبان ها، دستورالعمل های قالب بندی کد وجود دارد. به طور معمول، چنین اسنادی حاوی قوانینی برای نامگذاری متغیرها، ثابت ها، روش ها و سایر ساختارهای زبان هستند. بنابراین، برای پایتون PEP8، برای جاوا - Oracle Code Conventions برای جاوا وجود دارد . چندین مجموعه مختلف برای SQL ایجاد شده است که کمی با یکدیگر متفاوت هستند. صرف نظر از این، شما باید عادت به پیروی از قوانین را هنگام قالب بندی کد خود ایجاد کنید، به خصوص اگر در یک تیم کار می کنید. به عنوان مثال، قوانین می تواند موارد زیر باشد (البته، می توانید مجموعه قوانین متفاوتی را برای خود ایجاد کنید، نکته اصلی این است که در آینده به آنها پایبند باشید):

• کلمات کلیدی و کلمات رزرو شده، از جمله دستورات و عملگرها، باید با حروف بزرگ نوشته شوند: CREATE TABLE، CONSTRAINT...
• نام جداول، فیلدها و سایر اشیاء نباید با کلمات کلیدی زبان SQL همخوانی داشته باشد (به پیوند انتهای مقاله مراجعه کنید)، اما ممکن است حاوی آنها باشد.
• نام جدول باید هدف آنها را منعکس کند. آنها با حروف کوچک نوشته می شوند. کلمات در نام با زیرخط از یکدیگر جدا می شوند. کلمه در آخر باید به صورت جمع باشد : معامله‌گران (معامله‌گران)، نرخ_سهم (نرخ سهم).
• نام فیلدهای جدول باید هدف آنها را منعکس کند. آنها باید با حروف کوچک نوشته شوند، کلمات در نام باید به سبک Camel Case قالب بندی شوند ، و کلمه در پایان باید به صورت مفرد استفاده شود : نام (نام)، نرخ_ سهم (نرخ سهم).
• فیلدهای کلید مصنوعی باید حاوی کلمه id باشند.
• نام‌های CONSTRAINT باید از قراردادهای نام‌گذاری جدول پیروی کنند. آنها همچنین باید شامل فیلدها و جداول درگیر در آنها باشند، با یک پیشوند معنایی شروع شوند: check_ (بررسی مقدار فیلد)، pk_ (کلید اصلی)، fk_ (کلید خارجی)، uniq_ (یکتا بودن فیلد)، idx_ (شاخص). مثال: pk_traider_share_actions_id (کلید اصلی در قسمت شناسه جدول trader_share_actions).
• و به همین ترتیب، همانطور که SQL را مطالعه می کنید، لیست قوانین دوباره پر/تغییر می شود.

طراحی DBMS

بلافاصله قبل از ایجاد یک DBMS، باید طراحی شود. طرح نهایی شامل جداول، مجموعه ای از فیلدها، محدودیت ها، کلیدها، شرایط پیش فرض فیلدها، روابط بین جداول و سایر موجودیت های پایگاه داده است. در اینترنت می توانید بسیاری از طراحان آنلاین/آفلاین رایگان برای طراحی DBMS های کوچک پیدا کنید. سعی کنید چیزی مانند «طراح پایگاه داده رایگان» را در موتور جستجو تایپ کنید. چنین برنامه هایی دارای خواص اضافی مفیدی هستند:

• می تواند دستورات SQL را برای ایجاد یک DBMS تولید کند.
• به صورت بصری تنظیمات را روی نمودار نمایش دهید.
• به شما امکان می دهد جداول را برای تجسم بهتر جابجا کنید.
• نمایش کلیدها، شاخص ها، روابط، مقادیر پیش فرض و موارد مشابه در نمودار.
• آنها می توانند طرحواره DBMS را از راه دور ذخیره کنند.

به عنوان مثال، dbdiffo.com کلیدها را برجسته می کند، فیلدهای غیر خالی و شمارنده های هوش مصنوعی (AutoIncrement) را با برچسب NN نشان می دهد:

ایجاد جداول در DBMS

بنابراین ما یک نمودار داریم. حالا بیایید به ساخت جداول (CREATE TABLE) برویم. برای انجام این کار، توصیه می شود که داده های اولیه داشته باشیم:

• نام جدول
• نام فیلدها و نوع
• محدودیت ها (محدودیت ها) در زمینه ها
• مقادیر پیش فرض برای فیلدها (در صورت وجود)
• کلید اصلی (PRIMARY KEY) در صورت موجود بودن
• اتصالات بین جداول (کلید خارجی)

ما تمام گزینه های دستور 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(شرح فیلد) - جدولی با نام مشخص شده ایجاد می کند؛ در توضیحات، فیلدها با کاما از هم جدا می شوند. هر دستوری با نقطه ویرگول به پایان می رسد.
• شرح فیلد با نام آن شروع می شود و به دنبال آن نوع آن، CONSTRAINT و مقدار پیش فرض قرار می گیرد.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– فیلد id یک نوع عدد صحیح یک کلید اولیه و یک شمارنده افزایشی است (برای هر رکورد جدید برای فیلد id، مقداری تولید می‌شود که یک مقدار بزرگتر از مقداری است که قبلا برای این جدول ایجاد شده است).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– فیلد نقدی، اعشاری، 15 رقم قبل از نقطه اعشار و دو بعد از آن (اطلاعات مالی، به عنوان مثال، دلار و سنت). نمی توان مقادیر NULL را قبول کرد. اگر مقداری داده نشود، مقدار 1000 را دریافت می کند.
• 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 بگیرد

روابط بین جداول

برای تجزیه و تحلیل توصیف روابط بین جداول، اجازه دهید به ایجاد اشتراک_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_which_referred ADD FOREIGN KEY(field_that_referred) REFERENCEStable_to_which_referred (field_that_referred to) اجازه دهید در سهام ما رکوردهایی روی سهام داریم، به عنوان مثال، برای id=50، سهام مایکروسافت را با قیمت اولیه ذخیره می کنیم. 17.5، دلتا 20 و احتمال تغییر 4 درصد. برای جدول share_rates سه ویژگی اصلی دریافت می کنیم:

• فقط باید مقدار کلید id را از جدول سهام در قسمت اشتراک ذخیره کنیم تا از آن برای بدست آوردن اطلاعات باقی مانده (نام و غیره) از جدول سهام استفاده کنیم.
• ما نمی‌توانیم نرخی برای ارتقای ناموجود ایجاد کنیم. شما نمی توانید یک مقدار ناموجود را در فیلد اشتراک وارد کنید (که هیچ رکوردی در جدول سهام با این شناسه وجود ندارد)، زیرا هیچ تناسبی بین جداول وجود نخواهد داشت.
• ما نمی‌توانیم یک ورودی سهم در سهامی را که نرخ‌ها در نرخ‌های اشتراک تعیین شده‌اند حذف کنیم.

دو نکته آخر برای اطمینان از یکپارچگی داده های ذخیره شده است. می توانید ایجاد جداول SQL شبیه سازی ما و نمونه هایی از پرس و جوهای SQL را در پیاده سازی جاوا متدهای کلاس های مربوطه با استفاده از پیوند به مخزن github در انتهای مقاله مشاهده کنید. قسمت سوم