חלק 2. מבנה DBMS, טבלאות וסוגי נתונים

חלק ראשון אנו ממשיכים ליצור את האמולטור הפשוט שלנו לבורסה. הנה מה שנעשה:

• בואו ניצור דיאגרמת ארגון מסד נתונים.
• נתאר מה, איך והיכן הוא מאוחסן.
• בואו לגלות כיצד נתונים קשורים זה לזה.
• בואו נתחיל ללמוד את היסודות של SQL באמצעות הדוגמה של פקודת יצירת טבלת SQL CREATE TABLE , שפת הגדרת נתונים ( DDL ) של שפת SQL.
• בואו נמשיך בכתיבת תוכנית Java. אנו מיישמים את הפונקציות העיקריות של ה-DBMS במונחים של java.sql ליצירת מסד הנתונים שלנו באופן פרוגרמטי, תוך שימוש ב-JDBC ובארכיטקטורה תלת-שכבתית.

שני החלקים הללו התבררו כנפחיים יותר, מכיוון שעלינו להכיר את היסודות של SQL וארגון DBMS מבפנים, ולצייר אנלוגיות עם Java. כדי לא לשעמם אותך עם רשימות קוד, בסוף יש קישורים למאגר commit github המתאים לתוכנית.

עיצוב DBMS

תיאור האפליקציה

כבר שמעתם שארגון אחסון נתונים הוא חלק בלתי נפרד מהתכנות. הרשו לי להזכיר לכם שמטרת האפליקציה שלנו היא אמולציית החליפין הפשוטה ביותר:

• ישנן מניות שערכן יכול להשתנות במהלך יום המסחר לפי כללים נתונים;
• יש סוחרים עם הון ראשוני;
• סוחרים יכולים לקנות ולמכור מניות לפי האלגוריתם שלהם.

הבורסה פועלת בתיקיות - פרקי זמן קבועים (במקרה שלנו - דקה). במהלך סימון, מחיר המניה עשוי להשתנות, ואז הסוחר עשוי לקנות או למכור מניות.

החלפת מבנה נתונים של אמולציה

בואו נקרא מודלים של ישויות חליפין בודדות. כדי למנוע טעויות עיגול, נעבוד עם סכומים כספיים דרך שיעור 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 ));

כדי לגשת לקידום הרצוי לפי תעודה מזהה, השתמש בשיטה shares.get(id). עבור המשימה של מציאת מניה לפי שם או מחיר, היינו עוברים בלולאה בכל הרשומות בחיפוש אחר המניה שאנו צריכים, וכן הלאה. אבל נלך בדרך אחרת ונשמור את הערכים ב-DBMS.

אחסון נתונים ב-DBMS

הבה ננסח קבוצה ראשונית של כללי אחסון נתונים עבור DBMS:

• הנתונים ב-DBMS מאורגנים בטבלאות ( TABLE ), שהן קבוצה של רשומות.
• לכל הרשומות יש אותן קבוצות של שדות. הם נקבעים בעת יצירת הטבלה.
• ניתן להגדיר את השדה לערך ברירת מחדל ( DEFAULT ).
• עבור טבלה, אתה יכול להגדיר אילוצים ( CONSTRAINT ) המתארים את הדרישות לנתונים שלה כדי להבטיח את שלמותם. ניתן לעשות זאת בשלב יצירת הטבלה ( CREATE TABLE ) או להוסיף מאוחר יותר ( ALTER TABLE ... ADD CONSTRAINT ).
• האילוץ הנפוץ ביותר :
  • המפתח הראשי הוא PRIMARY (Id במקרה שלנו).
  • שדה ערך ייחודי UNIQUE (VIN עבור טבלת הרכב).
  • בדיקת השדה CHECK (ערך האחוז לא יכול להיות גדול מ-100). אחת ההגבלות הפרטיות על שדה היא NOT NULL או NULL , האוסרות/מאפשרות אחסון NULL בשדה טבלה.
  • קישור לטבלת צד שלישי FOREIGN KEY (קישור למניה בטבלת מחירי המניה).
  • אינדקס INDEX (אינדקס של שדה כדי להאיץ את החיפוש אחר ערכים בו).
  • שינוי של רשומה ( INSERT , UPDATE ) לא יתרחש אם ערכי השדות שלה סותרים את ההגבלות (CONSTRAINT).
• לכל טבלה יכול להיות שדה מפתח (או כמה) שניתן להשתמש בהם לזיהוי ייחודי של רשומה. שדה כזה (או שדות, אם הם יוצרים מפתח מורכב) יוצרים את המפתח הראשי של הטבלה - PRIMARY KEY .
  • המפתח הראשי מבטיח את הייחודיות של רשומה בטבלה, נוצר עליה אינדקס המעניק גישה מהירה לכל הרשומה על סמך ערך המפתח.
  • קיום מפתח ראשי מקלה בהרבה על יצירת קישורים בין טבלאות. לאחר מכן, נשתמש במפתח ראשוני מלאכותי: עבור הרשומה הראשונה id = 1, כל רשומה עוקבת תוכנס לטבלה עם ערך המזהה מוגדל באחד. מפתח זה נקרא לעתים קרובות AutoIncrement או AutoIdentity .

למעשה, טבלת מניות: האם ניתן להשתמש בשם המניה כמפתח במקרה זה? בגדול - כן, אבל יש אפשרות שחלק מהחברה מנפיקה מניות שונות וקוראת להן רק בשמה שלה. במקרה זה, לא תהיה יותר ייחודיות. בפועל, משתמשים במפתח ראשוני מלאכותי לעתים קרובות למדי. מסכים, שימוש בשם מלא כמפתח ייחודי בטבלה המכילה רשומות של אנשים לא יבטיח ייחודיות. כמו גם שימוש בשילוב של שם מלא ותאריך לידה.

סוגי נתונים ב-DBMS

כמו כל שפת תכנות אחרת, ל-SQL יש הקלדת נתונים. להלן סוגי הנתונים הנפוצים ביותר של SQL: סוגי מספרים שלמים אמיתי חוּט תאריך ושעה אחסון של כמויות גדולות של נתונים

סגנון כתיבה של SQL

עבור שפות רבות, קיימות הנחיות עיצוב קוד. בדרך כלל, מסמכים כאלה מכילים כללים למתן שמות למשתנים, קבועים, שיטות ומבני שפה אחרים. אז, עבור Python יש PEP8, עבור Java - Oracle Code Conventions עבור Java . נוצרו מספר סטים שונים עבור SQL, אשר מעט שונים זה מזה. בלי קשר, עליך לפתח את ההרגל לפעול לפי הכללים בעת עיצוב הקוד שלך, במיוחד אם אתה עובד בצוות. הכללים יכולים להיות, למשל, הבאים (כמובן, אתה יכול לפתח מערכת חוקים אחרת לעצמך, העיקר להיצמד אליהם בעתיד):

• מילות מפתח ומילים שמורות, כולל פקודות ואופרטורים, חייבות להיכתב באותיות גדולות: CREATE TABLE, CONSTRAINT...
• השמות של טבלאות, שדות ואובייקטים אחרים לא צריכים להתאים למילות מפתח בשפת SQL (ראה קישור בסוף המאמר), אך עשויים להכיל אותם.
• שמות הטבלה צריכים לשקף את מטרתם. הם כתובים באותיות קטנות. מילים בשם מופרדות זו מזו באמצעות קווים תחתונים. המילה בסוף חייבת להיות ברבים : סוחרים (סוחרים), שיעור_מניות (שיעור מניות).
• שמות שדות טבלה צריכים לשקף את מטרתם. הם חייבים להיות כתובים באותיות קטנות, המילים בשם חייבות להיות מעוצבות בסגנון Camel Case , ויש להשתמש במילה בסוף ביחיד : name (name), share_rates (share rate).
• שדות מפתח מלאכותיים חייבים להכיל את המילה id.
• שמות CONSTRAINT חייבים לעמוד במוסכמות מתן השמות בטבלה. עליהם לכלול גם את השדות והטבלאות המעורבים בהם, התחל בקידומת סמנטית: check_ (בדיקת ערך השדה), pk_ (מפתח ראשי), fk_ (מפתח זר), uniq_ (ייחוד שדה), idx_ (אינדקס). דוגמה: pk_traider_share_actions_id (מפתח ראשי בשדה המזהה לטבלת trader_share_actions).
• וכן הלאה, תוך כדי לימודי SQL, רשימת הכללים תתחדש/ תשתנה.

עיצוב DBMS

מיד לפני יצירת DBMS, יש לעצב אותו. הסכימה הסופית מכילה טבלאות, קבוצה של שדות, CONSTRAINT, מפתחות, תנאי ברירת מחדל לשדות, קשרים בין טבלאות וישויות מסד נתונים אחרות. באינטרנט אתה יכול למצוא הרבה מעצבים מקוונים/לא מקוונים בחינם לעיצוב DBMSs קטנים. נסה להקליד משהו כמו "מעצב מסד נתונים בחינם" במנוע חיפוש. ליישומים כאלה יש תכונות שימושיות נוספות:

• יכול ליצור פקודות SQL ליצירת DBMS.
• הצג חזותית את ההגדרות בתרשים.
• מאפשר לך להזיז טבלאות להדמיה טובה יותר.
• הצג מפתחות, אינדקסים, קשרים, ערכי ברירת מחדל וכדומה בתרשים.
• הם יכולים לאחסן מרחוק את סכימת DBMS.

לדוגמה, dbdiffo.com מדגיש מקשים, מציג שדות לא ריקים ומונה AI (AutoIncrement) עם התווית NN:

יצירת טבלאות ב-DBMS

אז יש לנו תרשים. כעת נעבור ליצירת טבלאות (CREATE TABLE). לשם כך, רצוי שיהיו לנו נתונים ראשוניים:

• שם שולחן
• שמות וסוג שדות
• הגבלות (CONSTRAINTS) על שדות
• ערכי ברירת מחדל עבור שדות (אם זמינים)
• מפתח ראשי (PRIMARY KEY) אם זמין
• חיבורים בין טבלאות (Foreign 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– שדה המזהה מסוג מספר שלם הוא מפתח ראשי ומונה מצטבר (עבור כל רשומה חדשה עבור שדה המזהה, ייווצר ערך שגדול באחד מזה שנוצר קודם לכן עבור טבלה זו).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– שדה מזומן, עשרוני, 15 ספרות לפני הנקודה העשרונית ושתיים אחרי (נתונים פיננסיים, למשל, דולרים וסנט). לא יכול לקבל ערכי NULL. אם לא ניתן ערך, הוא יקבל את הערך 1000.
• about VARCHAR(255) NULL– השדה אודות, מחרוזת באורך של עד 255 תווים, יכול לקבל ערכים ריקים.

שימו לב שאנו יכולים להגדיר חלק מתנאי CONSTRAINT לאחר יצירת הטבלה. הבה נבחן את המבנה לשינוי מבנה הטבלה והשדות שלה: ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (תנאי) באמצעות דוגמאות:

• CHECK(tradingMethod IN (1,2,3))- שדה המסחרMethod יכול לקחת רק ערכים 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_which_is_referred ADD FOREIGN KEY(field_which_referred) REFERENCEStable_to_which_referenced (field_which_is_referenced) הכנס למניות שיש לנו רשומות על מניות, לדוגמה, עבור id=50 אנו מאחסנים מניות של מיקרוסופט במחיר התחלתי של 17.5 , דלתא 20 וסיכוי לשינוי של 4%. עבור טבלת share_rates אנו מקבלים שלושה מאפיינים עיקריים:

• עלינו לאחסן רק את הערך של מפתח ה-id מטבלת השיתופים בשדה השיתוף כדי להשתמש בו כדי לקבל את המידע הנותר (שם וכו') מטבלת השיתופים.
• אנחנו לא יכולים ליצור תעריף לקידום לא קיים. לא ניתן להכניס ערך לא קיים לשדה השיתוף (שעבורו אין רשומה בטבלת השיתופים עם המזהה הזה), מכיוון שלא תהיה התאמה בין הטבלאות.
• לא נוכל למחוק רשומת מניות במניות שעבורן נקבעים שיעורי share_rates.

שתי הנקודות האחרונות משמשות להבטיח את שלמות הנתונים המאוחסנים. אתה יכול לראות את היצירה של טבלאות SQL של ​​האמולציה שלנו ודוגמאות של שאילתות SQL ביישום Java של מתודות של המחלקות המתאימות באמצעות הקישור למאגר github בסוף המאמר. החלק השלישי