יחידה 3: שפת SQL ואלגברה רלציונית

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

יסודות האלגברה הרלציונית

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

אופרטורים בסיסיים

• בחירה (Selection - σ): בוחר שורות מיחס העונות לתנאי מסוים.
• הטלה (Projection - π): בוחר עמודות מיחס ומסיר כפילויות.
• איחוד (Union - ∪): מחזיר את כל השורות הקיימות בשני יחסים (חייבים להיות בעלי אותה סכימה).
• הפרש (Difference - -): מחזיר את השורות הקיימות ביחס אחד אך לא באחר (חייבים להיות בעלי אותה סכימה).
• מכפלה קרטזית (Cartesian Product - ×): משלב כל שורה מיחס אחד עם כל שורה מיחס אחר.
• שינוי שם (Rename - ρ): משנה את שם היחס או את שמות העמודות שלו.

אופרטורים נגזרים

כתיבת שאילתות ב-SQL

SQL (Structured Query Language) היא השפה הסטנדרטית לתקשורת עם מסדי נתונים רלציוניים. היא שפה דקלרטיבית, כלומר אנו מציינים מה אנו רוצים לקבל, ולא כיצד להשיג זאת.

מבנה שאילתת SELECT בסיסית

המבנה המרכזי של שאילתת SQL הוא:

SELECT [DISTINCT] עמודות / ביטויים
FROM טבלאות
[WHERE תנאי_סינון_שורות]
[GROUP BY עמודות_קיבוץ]
[HAVING תנאי_סינון_קבוצות]
[ORDER BY עמודות_מיון [ASC|DESC]]
[LIMIT מספר_שורות];

צירופים (JOINs)

• INNER JOIN: מחזיר שורות שיש להן התאמה בשתי הטבלאות.
• LEFT (OUTER) JOIN: מחזיר את כל השורות מהטבלה השמאלית, ואת השורות התואמות מהטבלה הימנית (NULL אם אין התאמה).
• RIGHT (OUTER) JOIN: מחזיר את כל השורות מהטבלה הימנית, ואת השורות התואמות מהטבלה השמאלית (NULL אם אין התאמה).
• FULL (OUTER) JOIN: מחזיר את כל השורות משתי הטבלאות, עם NULL במקומות שאין התאמה.

שאילתות מקוננות (Subqueries)

שאילתה בתוך שאילתה אחרת. יכולות להופיע ב-WHERE, FROM, SELECT, או HAVING. הן יכולות להיות מקושרות (correlated) או לא מקושרות (non-correlated).

אופרטורי קבוצה (Set Operators)

• UNION: מאחד תוצאות של שתי שאילתות (מסיר כפילויות כברירת מחדל). UNION ALL משאיר כפילויות.
• INTERSECT: מחזיר שורות המשותפות לשתי השאילתות.
• EXCEPT (או MINUS במערכות מסוימות): מחזיר שורות הנמצאות בשאילתה הראשונה אך לא בשנייה.

תרגום בין SQL לאלגברה רלציונית

עקרונות תרגום נפוצים:

• SELECT: מתורגם לרוב ל-π (הטלה) על העמודות המבוקשות.
• FROM: מתורגם לרוב למכפלה קרטזית × או לצירוף ⋈ בין הטבלאות.
• WHERE: מתורגם לרוב ל-σ (בחירה) על התנאים.
• JOIN:
  • INNER JOIN עם ON: לרוב ⋈_θ (צירוף תנאי).
  • NATURAL JOIN: לרוב ⋈ (צירוף טבעי).
  • LEFT/RIGHT/FULL OUTER JOIN: דורשים הרחבות לאלגברה הרלציונית הבסיסית (Outer Join).
• GROUP BY ופונקציות אגרגציה: דורשים אופרטור אגרגציה מיוחד באלגברה הרלציונית (לרוב מסומן ב-γ).
• HAVING: תנאי בחירה (σ) המופעל לאחר האגרגציה.
• DISTINCT: מובנה באופרטור ההטלה (π).
• UNION, INTERSECT, EXCEPT: מתורגמים ישירות לאופרטורי הקבוצה המקבילים באלגברה הרלציונית (∪, ∩, -).
• שאילתות מקוננות:
  • IN / = ANY: לרוב ניתן לתרגם באמצעות צירוף או באמצעות אופרטורי קבוצה (∈).
  • NOT IN / <> ALL: לרוב באמצעות הפרש קבוצות (-).
  • EXISTS: לרוב באמצעות צירוף או בדיקת אי ריקות.
  • NOT EXISTS: לרוב באמצעות הפרש קבוצות או בדיקת ריקות.

שאלות לדיון

• נתונה סכימת מסד נתונים: Students(sid, sname, major), Courses(cid, cname, credits), Enrolled(sid, cid, grade). כתוב שאילתת SQL למציאת שמות הסטודנטים שלקחו את כל הקורסים במגמת "מדעי המחשב". תרגם את השאילתה לביטוי באלגברה רלציונית.
• הסבר את ההבדל בין WHERE ל-HAVING ב-SQL, ותן דוגמה לכל אחד. כיצד הבדל זה בא לידי ביטוי בתרגום לאלגברה רלציונית?
• כיצד ניתן לתרגם שאילתת SQL המשתמשת ב-LEFT JOIN לאלגברה רלציונית בסיסית (ללא אופרטורי Outer Join מורחבים)?
• השווה בין שימוש ב-IN לבין שימוש ב-EXISTS בשאילתות מקוננות מבחינת ביצועים וסמנטיקה. מתי תעדיף אחד על פני השני?

נקודות לתשובת מודל

• לגבי שאילתת ה"כל":
  • SQL: לרוב תשתמש בשאילתת חילוק מרומזת באמצעות NOT EXISTS כפול, או באמצעות GROUP BY ו-COUNT(DISTINCT ...) עם HAVING.
  • אלגברה רלציונית: תרגום ישיר לאופרטור החילוק (÷). יש להכין את היחסים המתאימים לפני החילוק.
• WHERE vs. HAVING:
  • WHERE: מסנן שורות בודדות לפני קיבוץ. באלגברה: σ לפני γ.
  • HAVING: מסנן קבוצות של שורות לאחר קיבוץ ואגרגציה. באלגברה: σ אחרי γ.
  • דוגמה: מצא מחלקות עם ממוצע שכר מעל X (HAVING), מצא עובדים עם שכר מעל Y (WHERE).
• תרגום LEFT JOIN:
  • ניתן לתרגם באמצעות INNER JOIN ואז UNION ALL עם השורות מהטבלה השמאלית שאין להן התאמה בטבלה הימנית (אלו שורות שניתן למצוא באמצעות הפרש קבוצות). יש למלא את העמודות מהטבלה הימנית ב-NULLs.
• IN vs. EXISTS:
  • סמנטיקה: IN בודק שוויון ערכים, EXISTS בודק קיום של שורה. EXISTS יכול להיות יעיל יותר כאשר השאילתה המקוננת מחזירה הרבה עמודות, מכיוון שהוא לא צריך להשוות ערכים ספציפיים.
  • NULLs: IN עם NULLs יכול להתנהג באופן לא אינטואיטיבי (לדוגמה, X IN (1, 2, NULL) יחזיר UNKNOWN אם X הוא 3). EXISTS אינו מושפע מ-NULLs בתוצאות השאילתה המקוננת.
  • ביצועים: לעיתים קרובות, אופטימייזר השאילתות יתרגם אותם באופן דומה. עם זאת, עבור שאילתות מקושרות, EXISTS נחשב לרוב עדיף או קריא יותר.