מציאת התאמות לפי קריטריונים גמישים

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

הפתרון הראשון שעלה בדעתי הוא לצרף לטבלת המועמדים עמודות כן/לא בהתאם לתנאים,
לצרף לטבלת המכללות עמודות כן/לא בהתאם,
ולשלוף כך:

Select  *

From    Students S

Inner Join Universities U

        On S.Army>=U.Army

        And S.Bagrut>=U.Bagrut

        And S.Math5>=U.Math5;

Go

הבעייה בפתרון הוא היעדר גמישות: בכל פעם שמתווסף קריטריון חדש – יש להוסיף עמודה לטבלת הסטודנטים, עמודה לטבלת המכללות, ולשנות את השליפה נ"ל ולהוסיף לה תנאי; ובנוסף- לא כל הקריטריונים הם מסוג של כן/לא ויש קריטיונים כמותיים כמו ציון ממוצע בבגרות, וגם בהם יש לטפל.

זו כמובן בעייה מורכבת יותר, והנה הפתרון המסודר עבורה.

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

Use tempdb;

Go

If Object_Id('Students_Criteria') Is Not Null Drop Table Students_Criteria;

Go

If Object_Id('Students') Is Not Null Drop Table Students;

Go

If Object_Id('Universities_Criteria') Is Not Null Drop Table Universities_Criteria;

Go

If Object_Id('Universities') Is Not Null Drop Table Universities;

Go

If Object_Id('Criteria') Is Not Null Drop Table Criteria;

Go

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

Create Table Students(Name Varchar(50) Primary Key);

Go

Insert Into Students

Select 'Avi'

Union All

Select 'Batya'

Union All

Select 'Gila'

Union All

Select 'Dana';

Go

Create Table Criteria(Name Varchar(50) Primary Key);

Go

Insert Into Criteria

Select 'Psichometric'

Union All

Select 'Memuza Bagrut'

Union All

Select 'Army'

Union All

Select 'Math 5';

Go

Create Table Universities(Name Varchar(50) Primary Key);

Go

Insert Into Universities

Select 'Bar Nitzan'

Union All

Select 'Haminhala'

Union All

Select 'Hamercaz';

Go

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

וטבלת מכללות-קריטריונים (יחס N:N בין המכללות לקריטריונים),

ונזין לתוכן נתונים:

Create Table Students_Criteria(Student Varchar(50) Foreign Key References Students(Name),

                                                        Criteria Varchar(50) Foreign Key References Criteria(Name),

                                                        Score SmallInt,

                                                        Constraint PK_Students_Criteria Primary Key(Student,Criteria));

Go

Insert Into Students_Criteria

Select 'Avi','Psichometric',500

Union All

Select 'Avi','Army',1

Union All

Select 'Batya','Memuza Bagrut',70

Union All

Select 'Batya','Psichometric',700

Union All

Select 'Gila','Math 5',1

Union All

Select 'Gila','Army',1;

Go

Create Table Universities_Criteria(University Varchar(50) Foreign Key References Universities(Name),

                                                        Criteria Varchar(50) Foreign Key References Criteria(Name),

                                                        Score SmallInt,

                                                        Constraint PK_Universities_Criteria Primary Key(University,Criteria));

Go

Insert Into Universities_Criteria

Select 'Bar Nitzan','Psichometric',550

Union All

Select 'Bar Nitzan','Army',1

Union All

Select 'Haminhala','Psichometric',400;

Go

כדאי לשים לב שכשיוצרים Foreign Key – הטבלה שבצד הרבים צריכה לכלול הפניה לכל עמודות המפתח בטבלה שבצד ה-1,

וכשמדובר ביחס של רבים לרבים – יש לפנות לעמודות המפתח בשתי הטבלאות, והמפתח בטבלת הביניים יכלול את כל עמודות ה-Foreign Key לפחות (במקרה שלנו המודל פשוט- עמודת מפתח בכל אחת מהטבלאות שבצד ה-1, ושתי עמודות ה-Foreign Key בטבלאות הביניים הן המפתח שלהן).

וכעת לשליפה: יש לשלוף את כל ההתאמות בין סטודנטים למכללות

שעבורן לא קיים קריטריון של המכללות

שעבורו לא קיים קריטריון הולם (=שעומד בזה של המכללות) של הסטודנטים:

Select        M.Name,

                S.Name

From        Universities M,

                Students S

Where        Not Exists (Select        *

                                        From        Universities_Criteria M1

                                        Where        M1.University=M.Name

                                                        And Not Exists (Select        *

                                                                                From        Students_Criteria S1

                                                                                Where        S1.Student=S.Name

                                                                                                And S1.Criteria=M1.Criteria

                                                                                                And S1.Score>=M1.Score));

Go

קצת קשה להבין את הלוגיקה ולצלוח את השימוש ב-Exists..

בכל מקרה- ניתן לראות שכל הסטודנטים יכולים להתקבל למכללת "המרכז" מכיוון שאין לה אף קריטריון לקבלה, כולל דנה שאין לה אף יכולת..