מאגרי נתונים

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

*אחסון מבוסס קבשים שומר את המידע בקבצים נפרדים, כמו ניירות בארגז תיקיות — קשה לחפש וקל להכפיל

מגבלות

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

*הגישה המבוססת קבשים: כל תוכ שומרת קבצים משלה

מסד נתונים יחסי פותר בעיות אלו על ידי אחסון מידע בטבלאות המנוהלות על ידי תוכנה אחת (מערכת ניהול מסדי נתונים) שמשתמשים בה כל התוכניות.

*הגישה למסד נתונים: מערכת ניהול מסדי נתונים אחת שומרת לכל התוכניות

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

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

שם הלקוח ומספר הטלפון שלו מאוחסנים בקובץ התיקונים וגם בקובץ החשבוניות (חזרה); כאשר לקוח משנה מספר, קובץ אחד מעודכן והשני לא (אי-התאמה); וכאשר החנות רוצה דוח חדש — תיקונים לפי טכנאי — יש לכתוב תוכנית חדשה לקריאת הקבצים (אין בקשות חופשיות). במסד נתונים יחסי הלקוח מאוחסן פעם אח בטבלת CUSTOMER ומיויחס על ידי CustomerID מטבלת REPAIR, ולכן שינוי נעשה פעם אחת ומוצג בכל מקום; הדוח הוא בקשת SQL אחת.

• טבלה (יחס) — רשת של שורות ועמודות; טבלה אחת לכל סוג של אובייקט (למשל CUSTOMER).
• רשומה (שורה, מכונה גם טופל) — שורה אחת; instance אחת של האובייקט.
• שדה (עמודה, המכונה גם תכונה) — עמודה אחת; חלק מידע אחד על כל רשומה.
• מפתח ראשי — שדה (או שדות) שמזהים באופן ייחודי כל רשומה; לעולם לא יכול להיות null או כפול.
• מפתח זר — שדה שערכו תואם למפתח הראשי של טבלה אחרת, ומקשר בין השתיים.
• מפתח משולב — מפתח ראשי המורכב משני שדות או יותר ביחד.
• מפתח מועמד — כל שדה (או שדות) שיכול לשמש כמפתח ראשי.
• מפתח משני — שדה שאינו ראשי אך מדגם לחיפוש מהיר.
• דגימה (אינדקס) — בניית אינדקס על שדה כדי שהחיפושים והחיבורים יהיו מהירים יותר.
• שלמות ייחוס — כל ערך במפתח זר חייב לתאים למפתח ראשי קיים (ללא רשומות יתומות).

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

CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate)   -- CustomerID is FK → CUSTOMER

דוגמה פתורה. הסבר מה מתכוונים בבינה ממוחשבת לקשרים ל-אובייקט (entity), מפתח ראשי ושלמות ייחוס, ונא להשלים את טבלת התאמות ↔ תיאור עבור טופל (tuple) ותכונה (attribute).*

אובייקט הוא דבר עליו מאוחזנת נתונים – אדם, חפץ או אירוע – הפך לטבלה אחת. מפתח ראשי הוא האטריבוט (או שילוב של אטריבוצים) שמזהה כל רשומה בטבלה באופן ייחודי. שלמות ייחוס פירושו שכל ערך במפתח זר חייב להתאים לערך של מפתח ראשי בטבלה שאליו הוא מתייחס, כך שרשומה לא יכולה להפנות לרשומה שאינה קיימת. טופל הוא שורה אחת בטבלה (רשומה אחת); תכונה היא עמודה אחת (שדה אחד). יש ללמוד את הזוגות: טבלה/יחס, רשומה/טופל, שדה/תכונה.

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

• אחד לאחד (1:1).
• אחד לרבים (1:M) – לכל לקוח יש הרבה הזמנות; לכל הזמנה יש לקוח אחד.
• רבים לרבים (M:N) – סטודנטים לוקחים הרבה קורסים, וקורסים יש בהם הרבה סטודנטים.

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

ENROLMENT(StudentID, CourseID, EnrolmentDate)

ציור דיאגרמת E-R עבור סט טבלות נתון. כל טבלה הופכת לזיהוי. קיים יחס בכל מקום שבו טבלה אחת מכילה מפתח חיצוני לטבלה אחרת; הקו נמתח מהטבלה המכילה את המפתח החיצוני (הקצה ה-הרבה) לטבלה שהמפתח הראשי שלה הוא (הקצה ה-אחד). טבלה עם שני מפתחות חיצוניים וללא זהות אחרת היא לרוב טבלת חיבור הפורקת יחס הרבה-הרבה. תוויתו כל קו עם סוג היחס.

דוגמה מפורטת. למוסך תיקונים יש את הטבלות CUSTOMER(CustomerID, Name, Phone), DEVICE(DeviceID, CustomerID, Type, Model), TECHNICIAN(TechnicianID, Name) ו-REPAIR(RepairID, DeviceID, TechnicianID, RepairDate, Cost). זיהו את היחסים ואת סוגיהם.

DEVICE מכיל CustomerID, לכן CUSTOMER–DEVICE הוא אחד-הרבה (לקוח אחד, התקנים רבים). REPAIR מכיל DeviceID, לכן DEVICE–REPAIR הוא אחד-הרבה; הוא גם מכיל TechnicianID, לכן TECHNICIAN–REPAIR הוא אחד-הרבה. אין קו ישיר CUSTOMER–REPAIR: הקו עובר דרך DEVICE. שלושה קווים, שלושה רגלי עורב, כולם בקצוות REPAIR או DEVICE.

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

• צורה נורמלית ראשונה (1NF) — כל שדה מכיל ערך יחיד (אטומי), ללא קבוצות חוזרות, ומכיל מפתח ראשי.
• צורה נורמלית שנייה (2NF) — ב-1NF, ובכל שדה שאינו מפתח תלוי ב-כולל המפתח הראשי (חשוב רק למפתח מורכב).
• צורה נורמלית שלישית (3NF) — ב-2NF, ובכל שדה שאינו מפתח תלוי רק במפתח הראשי, ולא בשדה שאינו מפתח אחר (אין תלות טרנסטיבית).

עיצוב ב-3NF מאחסן כל עובדה פעם אחת, ולכן אנומליות ההוספה/עדכון/מחיקה נעלמות. העלות היא יותר טבלות ויותר חיבורים. שואפו ל-3NF.

ליצירת עיצוב ב-3NF: מצאו את הזיהויים והמאפיינים שלהם; בחרו מפתח ראשי לכל אחד; פצלו שדות חוזרים/לא-אטומים (1NF); פצלו שדות התלויים בחלק ממפתח מורכב (2NF); פצלו שדות התלויים טרנסטיבית במפתח (3NF); הוסיפו מפתחות חיצוניים ליחסים.

דוגמה מפורטת. הטבלה ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity) יש לה את המפתח הראשי המורכב (OrderID, ProductID). נרמלו אותה ל-3NF. בדקו כל שדה שאינו מפתח מול המפתח. Quantity תלוי ב-שני OrderID ו-ProductID, וזה בסדר. אך CustomerID תלוי ב-OrderID לבדו - רק חלק מהמפתח המורכב. זוהי תלות חלקית, ולכן הטבלה אינה ב-2NF. פצלו אותה ל-ORDER_LINE(OrderID, ProductID, Quantity) ו-ORDER(OrderID, CustomerID, CustomerName). כעת בדקו 3NF: בטבלה החדשה ORDER, CustomerName תלוי ב-CustomerID, שהוא לא המפתח - תלות טרנסטיבית. פצלו שוב: ORDER(OrderID, CustomerID) ו-CUSTOMER(CustomerID, CustomerName). תנו שם לתלות ששוברת כל צורה (חלקית שוברת 2NF, טרנסטיבית שוברת 3NF); "יש בה נתונים חוזרים" מתאר את התסמינות ואינו מביא נקודות).

שלוש השאלות לשאול מכל טבלה. האם כל תא הוא ערך יחיד, ללא קבוצה חוזרת? אם לא, היא אינה ב1NF. אם המפתח מורכב, האם כל שדה שאינו מפתח תלוי במפתח כולו? אם שדה כלשהו תלוי בחלק ממנו, יש תלות חלקית והטבלה אינה ב2NF. האם כל שדה שאינו מפתח תלוי רק במפתח? אם שדה תלוי בשדה אחר שאינו מפתח, יש תלות טרנסטיבית והטבלה אינה ב3NF. תשובה "הסבר מדוע הטבלה אינה ב3NF" צריכה לציין את התלות והשדות המעורבים.

דוגמא פתורה. חנות להשכרת רכבים מקליטה כל השכרה כRENTAL(RentalID, RentalDate, CustomerID, CustomerName, CustomerPhone, CarReg, CarModel, DailyRate, Days), כאשר ההשכרה יכולה לכלול מספר רכבים. הסבר מדוע הטבלה אינה נורמליזציה ויצירת עיצוב 3NF.

אינו ב1NF: שדות הרכב CarReg, CarModel, DailyRate, Days מהווים קבוצה חוזרת — להשכרה אחת ישנם מספר רכבים. העבר אותם לRENTAL_CAR(RentalID, CarReg, CarModel, DailyRate, Days) עם המפתח המרוכב (RentalID, CarReg). אינו ב2NF: בRENTAL_CAR, CarModel וDailyRate תלויים בCarReg לבדו — זוהי תלות חלקית. העבר אותם לCAR(CarReg, CarModel, DailyRate), והשאיר את RENTAL_CAR(RentalID, CarReg, Days). אינו ב3NF: בRENTAL, CustomerName וCustomerPhone תלויים בCustomerID, שדה שאינו מפתח — זוהי תלות מעבר. העבר אותם לCUSTOMER(CustomerID, CustomerName, CustomerPhone), והשאיר את RENTAL(RentalID, RentalDate, CustomerID). עיצוב ה-3NF הוא ארבע טבלאות — CUSTOMER, RENTAL, RENTAL_CAR, CAR — עם CustomerID, RentalID וCarReg כמפתחות זרים; קו תחתון לכל מפתח ראשי.

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

• מילון נתונים — תיאור של כל טבלה, שדה, סוג ומפתח; תוכניות מבצעות שאלות עליו במקום לקודד את המבנה באופן קשיח.
• בקרת חזרות/עקביות — כל עובדה מאוחזת פעם אחת.
• בקרת גישה בו-זמנית — נעילות ועסקאות מאפשרות למשתמשים רבים לעבוד יחד.
• גיבוי ושחזור; אבטחה ורישיונות לפי משתמש.
• כללי שלמות — מפתחות, הגבלות ייחודיות וטווחים, מופעלים במרכזיות.
• עסקאות — קבוצת פעולות שכולן מצליחות או כולן נכשלות.
• תצוגות — טבלאות וירטואליות המציגות למשתמש כל אחד "את" החלק שלו מהנתונים.
• ניהול נתונים ומודל נתונים — שליטה כיצד נתונים מאוחזים והגדרת המבנה שלהם כ-סקימה לוגית (העיצוב הלוגי, עצמאי מאחסון פיזי).
• שלמות נתונים ואבטחת נתונים — הפעלת נכונות ובקרת גישה במרכזיות.
• מעבד שאלות מנף שאלות; ממשק מתפתחים מספק כלים ו-APIs לבניית אפליקציות.

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

מה שמילון הנתונים מכיל (שאלת "תן שלושה פריטים"): שמות הטבלאות; שמות השדות בכל טבלה; סוג הנתונים ואורך של כל שדה; המפתחות הראשיים והזרים והיחסים בין הטבלאות; כללי אימות; אינדקסים; ומי רשאי לגשת לכל טבלה. זהו מידע-על (meta-data) — נתונים על הנתונים — ומערכת ה-DBMS משתמשת בו כדי לבדוק כל שאלה וכל שינוי.

כיצד ה-DBMS שומר על אבטחת הנתונים (שאלת "תאר שתי שיטות"): אישור זהות — שם משתמש וסיסמה, או ביומטריה, לפני כל גישה; זכויות גישה — למשתמש או לקבוצה מותר לקרוא, לכתוב או למحוק רק טבלאות או שדים מסוימים, לעיתים קרובות דרך תצוגה; הצפנה של הנתונים הארוחים ושל נתונים הנשלחים אליו, כך שקובץ שנעתק יהיה בלתי קריא; גיבויים שנעשים באופן קבוע, כך שהנתונים יוכלו לשוחזר לאחר אובדן; ולוג עסקאות שמקלט מי שינה מה.

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

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

SQL (Structured Query Language) מורכב משני חלקים:

• שפת הגדרת נתונים (DDL) — יוצרת או משנה את המבנה (טבלאות, מפתחות, מגבלות).
• שפת עיבוד נתונים (DML) — עובדת עם הנתונים (הוספה, עדכון, מחיקה, שאילתה).

בסיסי DDL

CREATE TABLE CUSTOMER (
  CustomerID INTEGER PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Phone VARCHAR(20)
);

הוספת מפתח זר:

CREATE TABLE ORDER (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER,
  OrderDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

שינוי והסרה:

ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
DROP TABLE CUSTOMER;

סוגים נפוצים: INTEGER, REAL, VARCHAR(n), CHAR(n) (גם CHARACTER(n)), DATE, TIME, BOOLEAN, DECIMAL(p, s).

בסיסי DML

שאילתה עם SELECT:

SELECT Name, Phone
FROM CUSTOMER
WHERE City = 'London'
ORDER BY Name ASC;

SELECT מפרט שדות, FROM מכנה את הטבלה, WHERE מסנן שורות, ORDER BY ממין.

חיבור (join) משלב שתי טבלאות באמצעות יחס מפתח זר:

SELECT C.Name, O.OrderDate
FROM CUSTOMER C INNER JOIN ORDER O
  ON C.CustomerID = O.CustomerID
WHERE O.OrderDate >= '2024-01-01';

פונקציות אגרגט (COUNT, SUM, AVG, MIN, MAX) משמשות לעיתים קרובות יחד עם GROUP BY:

SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDER
GROUP BY CustomerID;

Insert, update, delete:

INSERT INTO CUSTOMER (CustomerID, Name, Phone)
VALUES (101, 'Ada Lovelace', '020-1234-5678');

UPDATE CUSTOMER SET Phone = '020-9999-0000' WHERE CustomerID = 101;

DELETE FROM CUSTOMER WHERE CustomerID = 101;

תמיד יש להוסיף סעיף WHERE בשאלות UPDATE וDELETE, אלא אם כן השינוי יושפע ב-כל השורות.

טיפים לשאלון SQL

• השתמש במימונים המדויקים של הטבלה והשדה מהשאלה.
• סוגרי מילוליות עם סימני פסיקה יחידים ('Smith'); אל תסגור מספרים בפסיקות.
• השוואות: =, <, >, <=, >=, <>.
• LIKE 'A%' מתאים לכל מה שמתחיל באות A (% = כל מילוליות, _ = תווית אחת); IN (1,2,3); BETWEEN 10 AND 20.
• שלב תנאים עם AND / OR / NOT, וגם כל הודעה נסגרת בנקודה-פסיקה.

הדפוס DDL שהשאלון מחפש. כל CREATE TABLE מציין את שם השדה עם הסוג שלו, מסמן את המפתח הראשי, ומצהיר על כל מפתח חיצוני עם הטבלה שמפנה אליה; מפתח מורכב מוגדר בשורה נפרדת:

CREATE TABLE RENTAL_CAR (
  RentalID INTEGER,
  CarReg VARCHAR(8),
  Days INTEGER,
  PRIMARY KEY (RentalID, CarReg),
  FOREIGN KEY (RentalID) REFERENCES RENTAL(RentalID),
  FOREIGN KEY (CarReg) REFERENCES CAR(CarReg)
);

דוגמה מפורטת. השתמש בCUSTOMER(CustomerID, Name, Phone) ובDEVICE(DeviceID, CustomerID, Type, Model) כדי לכתוב סקריפטים ב-SQL עבור: (א) רשימת שם ומספר טלפון של כל לקוח בעל התקן מסוג 'tablet', בסדר אלפביתי לפי שם; (ב) ספירת ההתקנים בכל סוג; (ג) הקלטה שלקוח 17 כעת יש לו מספר טלפון '0771 234 5678'; (ד) הוספת התקן חדש, ID 305, 'laptop' של דגם 'X1' השייך ללקוח 17.

(a)

SELECT CUSTOMER.Name, CUSTOMER.Phone
FROM CUSTOMER INNER JOIN DEVICE
  ON CUSTOMER.CustomerID = DEVICE.CustomerID
WHERE DEVICE.Type = 'tablet'
ORDER BY CUSTOMER.Name ASC;

(b)

SELECT Type, COUNT(DeviceID) AS NumberOfDevices
FROM DEVICE
GROUP BY Type;

(c) UPDATE CUSTOMER SET Phone = '0771 234 5678' WHERE CustomerID = 17; (d) INSERT INTO DEVICE (DeviceID, CustomerID, Type, Model) VALUES (305, 17, 'laptop', 'X1');

נקודות ניתנות לפי סעיף — השדות, הטבלות, תנאי ה-JOIN, הWHERE, הORDER BY — כך שסקריפט עם סעיף אחד שגוי עדיין זוכה בנקודות לשאר. כתוב Table.Field כאשר מעורבות שתי טבלאות.

דוגמה מפורטת. הסבר מה עושה סקריפט זה: SELECT T.Name, SUM(R.Cost) AS Total FROM TECHNICIAN T INNER JOIN REPAIR R ON T.TechnicianID = R.TechnicianID GROUP BY T.Name;

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

שאלת הגדרה מוקדמת לפי טקסט קבוע. לימודן במדויק, ותן תשובה אחת בלבד.

• הגדר את המונחים בדיוק: אנטיות, מאפיינים, מפתח ראשי, מפתח חיצוני, ואת סוגי הקשרים (1:1, 1:רבים, רבים:רבים).
• תן סיבה לכל צורה נורמלית: 1NF (אין קבוצות חוזרות), 2NF (אין תלות חלקית), 3NF (אין תלות של מאפיין שאינו מפתח) — ושם את השדות המעורבים.
• הסבר מה מערכת ניהול הנתונים מספקת (עצמאות נתונים, אבטחה, שלמות, גישה מקבילה, מילון נתונים, ממשק מפתחים, מעבד שאילתות).
• הבדל בין DDL (הגדרת המבנה) לבין DML (שאלה ושינוי הנתונים), וכתיבת פקודה SQL סעיף לסעיף: SELECT, FROM, INNER JOIN … ON, WHERE, GROUP BY, ORDER BY.
• לצייר דיאגרמת E-R מתוך טבלאות, מצא קודם כל כל מפתח חיצוני: כל מפתח חיצוני מייצג קשר אחד-לרבים, כאשר "הרבים" נמצאים בטבלה שיש בהו.

טעויות נפוצות

• ציור ישיר של קשר רבים-לרבים. חובה לפצל אותו לשני קשרי אחד-לרבים דרך טבלת קישור המחזיקה שני מפתחות חיצוניים.
• הסבר ל"לא ב-3NF" באמצעות "הנתונים חוזרים". שם את התלות (חלקית או מעבר) ואת השדות המעורבים.
• תווים כפולים מוקפים מחרוזות ב-SQL, או תווים מוקפים מספרים. מחרוזות לוקחות 'single quotes'; מספרים לוקחים אף אחד.
• השמדת תנאי ON לאחר INNER JOIN. ללאו הטבלאות אינן מקושרות זו בזו.
• הנחת שדה רגיל צמוד ל-COUNT או ל-SUM בתוך SELECT ללא GROUP BY.
• UPDATE או DELETE ללא WHERE. היא משנה או מוסרת שורה בכל הטבלא.