Transcript 4 תומכס ןונכת תיירואית םייסחי םינותנ ידסמל קלח

‫תיאוריית תכנון סכמות‬
‫למסדי נתונים יחסיים‬
4 ‫חלק‬
Design Theory for
Relational Databases
Part 4
1
‫דוגמה לסכמה בעייתית‬
Student
Course
Teacher
S
Cohen
Levy
Levy
C
DB
OS
DB
T
Smith
Jones
Smith
‫היחס‬
C → T ‫ ומתקיימת הת"פ‬SCT ‫הסכמה היא‬
2
‫אפיון סכמה בעייתית‬
‫נתונה לנו סכמה ‪( R‬קבוצת אטריביוטים)‬
‫ונתונה קבוצה של ת"פ ‪ F‬שהיחסים עבור ‪R‬‬
‫צריכים לקיים‬
‫הסכמה בעייתית אם קימת ת"פ ‪ X → Y‬לא‬
‫טריוויאלית (קרי‪ )Y ⊊ X ,‬כך ש‪-‬‬
‫‪F⊨X→Y‬‬
‫ו‪ X -‬אינו מפתח‪-‬על‬
‫‪3‬‬
‫צורה נורמלית של ‪Boyce-Codd‬‬
‫)‪Boyce-Codd Normal Form (BCNF‬‬
‫סכמה ‪ R‬עם קבוצת ת"פ ‪ F‬היא בצורה‬
‫נורמלית של ‪ )BCNF( Boyce-Codd‬אם‬
‫לכל ‪ X → A ∈ F+‬מתקיים‬
‫‪ X → A‬היא טריוויאלית‪ ,‬כלומר ‪ ,A ∈ X‬או‬
‫‪ X‬הוא מפתח‪-‬על‪ ,‬כלומר ‪F ⊨ X →R‬‬
‫‪4‬‬
‫המטרה‬
‫בהינתן סכמה ‪ R‬עם קבוצת ת"פ ‪ ,F‬צריך‬
‫למצוא פירוק ‪ R1, R2,…,Rn‬כך‬
‫שמתקיימים הדברים הבאים‪:‬‬
‫הפירוק חסר אובדן‬
‫הפירוק משמר את התלויות‬
‫כל סכמה ‪ Ri‬הנה בצורה נורמלית‬
‫‪5‬‬
‫מציאת פירוק המקיים ‪BCNF‬‬
‫מתחילים עם הסכמה ‪ R‬וקבוצת הת"פ ‪F‬‬
‫מחפשים הפרה של ‪ ,BCNF‬כלומר ת"פ‬
‫‪ ,X → A ∈ F+‬כך ש‪-‬‬
‫‪ A ∉ X‬וגם‬
‫‪F ⊬ X →R‬‬
‫טענה‪ :‬אם יש הפרה ב‪ ,F+ -‬אז יש הפרה ב‪;F -‬‬
‫לכן ניתן למצוא הפרה (אם יש כזאת) בזמן‬
‫פולינומיאלי‬
‫‪6‬‬
‫אם יש הפרה‪ ,‬נפרק לפי הדוגמה‬
‫נתונה הסכמה ‪ ABC‬עם }‪F = {A → B, B → C‬‬
‫‪ B → C‬היא הפרה של ‪BCNF‬‬
‫לכן נפרק לשתי סכמות‪:‬‬
‫סכמה שמורכבת מהאטריביוטים של הת"פ המפרה את‬
‫‪ ,BCNF‬כלומר בדוגמה הנתונה הסכמה היא ‪( BC‬בסכמה‬
‫זו פחות הפרות ופחות אטריביוטים מאשר בסכמה‬
‫המקורית)‬
‫וסכמה המורכבת מכל האטריביוטים של הסכמה‬
‫המקורית‪ ,‬למעט האטריביוט המופיע בצד הימני של הת"פ‬
‫המפרה ‪ ,BCNF‬כלומר בדוגמה הנתונה הסכמה היא ‪AB‬‬
‫(בסכמה זו פחות הפרות ופחות אטריביוטים מאשר‬
‫בסכמה המקורית)‬
‫‪7‬‬
‫מציאת פירוק ‪( BCNF‬המשך)‬
‫עבור כל אחת מהסכמות החדשות נחשב כיסוי של‬
‫הת"פ‪ ,‬כלומר אם ‪ S‬סמכה חדשה נחשב את (‪S)F‬‬
‫נמשיך באופן רקורסיבי עם כל אחת מהסכמות‬
‫הקיימות‬
‫תמיד מחליפים סכמה בשתי סכמות‪ ,‬שבכל אחת‬
‫פחות אטריביוטים‬
‫האלגוריתם מסתיים כי סכמה של שני אטריביוטים‬
‫תמיד מקיימת ‪BCNF‬‬
‫‪8‬‬
‫דוגמה‬
‫‪ R=ABCD‬ו‪F = {A → B, B → C, C → D} -‬‬
‫‪ B → C‬היא הפרה של ‪BCNF‬‬
‫לכן נפרק את ‪ R‬לשתי סכמות‪:‬‬
‫‪ R1=BC‬ו‪F1 = {B → C} -‬‬
‫‪ R2=ABD‬ו‪F2 = {A → B, B → D} -‬‬
‫הת"פ ‪ B → D‬מפרה את ‪ BCNF‬ב‪ R2 -‬ולכן נפרק את‬
‫‪ R2‬ל‪-‬‬
‫‪ R2=BD‬ו‪F2 = {B → D} -‬‬
‫‪ R3=AB‬ו‪F3 = {A → B} -‬‬
‫‪9‬‬
‫דוגמה (המשך)‬
‫הפירוק הסופי הוא‪:‬‬
‫‪ R1=BC‬ו‪F1 = {B → C} -‬‬
‫‪F = {A → B, B → C,‬‬
‫‪ R2=BD‬ו‪F2 = {B → D} -‬‬
‫}‪C → D‬‬
‫‪ R3=AB‬ו‪F3 = {A → B} -‬‬
‫הערה‪ :‬לכל ‪ Fi ,i‬הוא כיסוי חסר כפילויות של )‪ Ri (F‬‬
‫והחישוב שלו דורש (במקרה הכללי) זמן אקספוננציאלי‬
‫איחוד ה‪ Fi -‬אינו שקול ל‪ F -‬המקורי‪ ,‬ולפיכך אין שימור‬
‫של הת"פ‬
‫‪10‬‬
‫פירוק בדרך אחרת‬
‫של הדוגמה הקודמת‬
‫‪ R=ABCD‬ו‪F = {A → B, B → C, C → D} -‬‬
‫נפרק תחילה לפי ‪( C → D‬ולא לפי ‪)B → C‬‬
‫לכן נפרק את ‪ R‬לשתי סכמות‪:‬‬
‫‪ R1=CD‬ו‪F1 = {C → D} -‬‬
‫‪ R2=ABC‬ו‪F2 = {A → B, B → C} -‬‬
‫הת"פ ‪ B → C‬מפרה את ‪ BCNF‬ב‪ R2 -‬ולכן נפרק את‬
‫‪ R2‬ל‪-‬‬
‫‪ R2=BC‬ו‪F2 = {B → C} -‬‬
‫‪ R3=AB‬ו‪F3 = {A → B} -‬‬
‫‪11‬‬
‫פירוק בדרך אחרת (המשך)‬
‫הפירוק הסופי הוא‪:‬‬
‫‪ R1=CD‬ו‪F1 = {C → D} -‬‬
‫‪ R2=BC‬ו‪F2 = {B → C} -‬‬
‫‪ R3=AB‬ו‪F3 = {A → B} -‬‬
‫הפעם איחוד ה‪ Fi -‬שקול ל‪ F -‬המקורי‬
‫ולכן יש שימור של הת"פ‬
‫‪12‬‬
‫למה לגבי פירוק ללא אובדן‬
‫תהי ‪ R‬סכמה עם קבוצת ת"פ ‪F‬‬
‫למה‪ :‬פירוק של ‪ R‬לשתי סכמות ‪ R1‬ו‪ R2 -‬הנו‬
‫ללא אובדן אם ורק אם מתקיים אחד משני‬
‫התנאים הבאים‪:‬‬
‫‪ F ⊨ R1 ⋂ R2 → R1‬או‬
‫‪F ⊨ R1 ⋂ R2 → R2‬‬
‫מסקנה‪ :‬פירוק לשתי סכמות לפי הפרה של‬
‫‪ BCNF‬הוא תמיד ללא אובדן‬
‫‪13‬‬
‫תכונות הפירוק ל‪BCNF -‬‬
‫הפירוק שהאלגוריתם מייצר הנו בעל התכונות‬
‫הבאות‪:‬‬
‫כל סמכה בפירוק הנה ‪BCNF‬‬
‫הפירוק הנו ללא אובדן‬
‫הפירוק אינו בהכרח משמר את הת"פ‬
‫זמן הריצה של האלגוריתם הנו אקספוננציאלי‬
‫בגלל חישוב )‪ Ri (F‬‬
‫בתרגיל ניתן אלגוריתם פולינומיאלי‬
‫‪14‬‬
‫דוגמה‬
‫נתונה הסכמה ‪ ABC‬עם‬
‫}‪F = {AB → C, C → A‬‬
‫‪ C → A‬היא ההפרה היחידה של ‪ ,BCNF‬ולכן‬
‫צריך לפרק לפי ת"פ זו ומקבלים‪:‬‬
‫סכמה ‪ CA‬עם הת"פ ‪C → A‬‬
‫וסכמה ‪ BC‬ללא ת"פ‬
‫מסקנה‪ :‬אין שום פירוק ל‪ BCNF -‬של הסכמה‬
‫הנ"ל שמשמר את הת"פ‬
‫‪15‬‬
‫מה גרם לאי‪-‬שימור הת"פ?‬
‫כשפרקנו את ‪ ,ABC‬פיצלנו את המפתח ‪AB‬‬
‫של הסכמה המקורית בין שתי הסכמות‬
‫החדשות‬
‫‪ A‬בסכמה אחת‬
‫‪ B‬בסכמה השניה‬
‫כתוצאה מכך הת"פ ‪ AB → C‬הלכה לאיבוד‬
‫מסקנה‪ :‬אסור לפצל מפתחות‬
‫‪16‬‬
‫צורה נורמלית שלישית‬
‫)‪Third Normal Form (3NF‬‬
‫סכמה ‪ R‬עם קבוצת ת"פ ‪ F‬היא בצורה‬
‫נורמלית שלישית (‪ )3NF‬אם לכל‬
‫‪ X → A ∈ F+‬מתקיים אחד הדברים‬
‫הבאים‪:‬‬
‫‪ X → A‬היא טריוויאלית‪ ,‬כלומר ‪,A ∈ X‬‬
‫‪ X‬הוא מפתח‪-‬על‪ ,‬כלומר ‪ ,F ⊨ X →R‬או‬
‫האטריביוט ‪ A‬שייך למפתח כלשהו של ‪R‬‬
‫‪17‬‬
‫אלגוריתם למציאת‬
‫פירוק המקיים ‪3NF‬‬
‫מתחילים עם הסכמה ‪ R‬וקבוצת‬
‫הת"פ ‪F‬‬
‫מוצאים כיסוי חסר כפילויות ‪ G‬של ‪F‬‬
‫מכל ת"פ ‪ X → A ∈ G‬מייצרים סכמה‬
‫‪( XA‬שהמפתח שלה הוא ‪)X‬‬
‫‪18‬‬
‫תכונות הפירוק שקבלנו‬
‫הפירוק שהאלגוריתם מייצר הנו בעל‬
‫התכונות הבאות‪:‬‬
‫כל סמכה בפירוק הנה ‪( 3NF‬צריך‬
‫להוכיח)‬
‫הפירוק משמר את הת"פ (ברור)‬
‫האם הפירוק בעל תכונת הצירוף ללא‬
‫אובדן?‬
‫‪19‬‬
‫דוגמה‬
‫נתונה הסכמה ‪ ABC‬עם‬
‫}‪F = {A → C, B → C‬‬
‫‪ F‬הוא כבר חסר כפילויות‪ ,‬לכן מקבלים‪:‬‬
‫‪ R1=AC‬ו‪F1 = {A → C} -‬‬
‫‪ R2=BC‬ו‪F2 = {B → C} -‬‬
‫‪20‬‬
‫נבדוק האם הפירוק‬
‫הוא ללא אובדן‬
‫}‪F = {A → C, B → C‬‬
‫הפירוק הוא‪ AC :‬ו‪BC -‬‬
‫‪C‬‬
‫‪a3‬‬
‫‪B‬‬
‫‪b1,3‬‬
‫‪A‬‬
‫‪a1‬‬
‫‪a3‬‬
‫‪a2‬‬
‫‪b2,1‬‬
‫הפירוק אינו מקיים את‬
‫תכונת הצירוף ללא אובדן‬
‫‪21‬‬
‫אלגוריתם למציאת פירוק ‪3NF‬‬
‫(המשך)‬
‫אם בין הסכמות שקבלנו מהכיסוי חסר‬
‫הכפילויות‪ ,‬אין אף סכמה שקבוצת‬
‫האטריביוטים שלה היא מפתח‪-‬על של‬
‫הסכמה המקורית‪ ,‬אז‬
‫מוסיפים מפתח של הסכמה המקורית‬
‫כסכמה נוספת של הפירוק‬
‫‪22‬‬
‫הדוגמה הקודמת (שנית)‬
‫נתונה הסכמה ‪ R=ABC‬עם‬
‫}‪F = {A → C, B → C‬‬
‫‪ F‬הוא כבר חסר כפילויות‪ ,‬לכן מקבלים‪:‬‬
‫‪ R1=AC‬ו‪F1 = {A → C} -‬‬
‫‪ R2=BC‬ו‪F2 = {B → C} -‬‬
‫אף סכמה בפירוק אינה מפתח‪-‬על של ‪ABC‬‬
‫לכן‪ ,‬מוסיפים לפירוק את הסכמה ‪ ,AB‬שהיא מפתח‬
‫של הסכמה המקורית ‪ABC‬‬
‫‪ R3=AB‬ו‪F3 = ∅ -‬‬
‫‪23‬‬
‫דוגמה נוספת של מציאת פירוק‬
‫המקיים ‪3NF‬‬
‫‪ R=ABCD‬ו‪F = {A → B, B → C, C → D} -‬‬
‫‪ F‬כיסוי חסר כפילויות‪ ,‬לכן נייצר את הסכמות‪:‬‬
‫‪ R1=AB‬ו‪F1 = {A → B} -‬‬
‫‪ R2=BC‬ו‪F2 = {B → C} -‬‬
‫‪ R3=CD‬ו‪F3 = {C → D} -‬‬
‫‪ (R1)+F=R‬ולכן ‪ R1‬מפתח‪-‬על של ‪ R‬ואין צורך להוסיף‬
‫אף סכמה נוספת‬
‫הערה‪ :‬במקרה זה הפירוק גם מקיים ‪BCNF‬‬
‫‪24‬‬
‫עוד דוגמה‬
‫נתונה הסכמה ‪ R=ABC‬עם‬
‫}‪F = {AB → C, C → A‬‬
‫נייצר את הסכמות‪:‬‬
‫סכמה ‪ R1=ABC‬עם }‪F1 = {AB → C‬‬
‫סכמה ‪ R2=AC‬עם }‪F2 = {C → A‬‬
‫‪ Fi‬מציין את‬
‫הת"פ שממנה‬
‫נוצר ‪ ,Ri‬אבל‬
‫אינו בהכרח‬
‫כיסוי של כל‬
‫הת"פ‬
‫המתקיימים‬
‫ב‪R i -‬‬
‫למעשה ב‪ R1 -‬מתקיימת גם הת"פ ‪ C → A‬אבל היא‬
‫לא ניתנת באופן מפורש ע"י האלגוריתם‪ ,‬ואין צורך‬
‫לדעת זאת כדי להראות שימור הת"פ‬
‫‪25‬‬
‫שיפור נוסף לאלגוריתם למציאת‬
‫פירוק ‪3NF‬‬
‫אפשר לאחד סכמות עם אותו המפתח‪ ,‬קרי סכמות‬
‫שהתקבלו מת"פ עם אותו צד שמאל‬
‫דוגמה‪F = {A → B, A → C} ,R=ABC :‬‬
‫מקבלים‪:‬‬
‫‪ R1=AB‬ו‪F1 = {A → B} -‬‬
‫‪ R2=AC‬ו‪F2 = {A → C} -‬‬
‫אפשר לאחד לסכמה אחת‪:‬‬
‫‪ R1=ABC‬ו‪F1 = {A → B, A → C} -‬‬
‫‪26‬‬
‫נוכיח שהפירוק המתקבל‬
‫אומנם מקיים ‪3NF‬‬
‫כזכור‪ ,‬מייצרים סכמות מכיסוי חסר‬
‫כפילויות ‪G‬‬
‫תהי ‪ S=XA1…An‬סכמה שהתקבלה מכל‬
‫הת"פ מהצורה ‪ ,X → Ai‬קרי כל הת"פ‬
‫שצד שמאל שלהן הוא ‪X‬‬
‫צריך להוכיח שב‪ S -‬אין הפרה של ‪3NF‬‬
‫‪27‬‬
‫הת"פ המפרה ‪V → Ak‬‬
‫נניח שהסכמה ‪ S=XA1…An‬מפרה‬
‫‪ 3NF‬בגלל ת"פ ‪V → B ∈ G+‬‬
‫לכן ‪ ,B ∉ X‬כי ‪ X‬מפתח של הסכמה‬
‫ואם ‪ B ∈ X‬אז אין זו הפרה של ‪3NF‬‬
‫לפיכך‪ B ,‬הוא אחד ה‪ Ai -‬ונניח ש‪-‬‬
‫‪B=Ak‬‬
‫נסמן את הת"פ המפרה ע"י ‪V → Ak‬‬
‫‪28‬‬
‫המשך ההוכחה‪X ⊈ V :‬‬
‫‪ V → Ak‬היא הת"פ המפרה ‪3NF‬‬
‫‪( X ⊈ V‬אחרת ‪ V‬מכיל מפתח ולכן ‪ V‬הינו‬
‫→‪V‬‬
‫מפתח‪-‬על של הסכמה ‪ S‬והת"פ‬
‫‪ Ak‬איננה הפרה של ‪) 3NF‬‬
‫המשך ההוכחה דן בשני מקרים‪:‬‬
‫‪V⊆X‬‬
‫‪V⊈X‬‬
‫‪29‬‬
‫מקרה ‪V ⊆ X :1‬‬
‫‪ V → Ak ∈ G+‬היא הת"פ המפרה ‪3NF‬‬
‫כאמור ‪ X ⊈ V‬ולכן ‪ V‬חלקית ממש ל‪X -‬‬
‫‪ X → Ak ∈ G‬כי לפי ההנחה יצרנו סכמה‬
‫מת"פ זו‬
‫נובע שהאטריביוטים של ‪ X – V‬הנם‬
‫מיותרים בצד שמאל של הת"פ ‪,X → Ak‬‬
‫בסתירה לכך ש‪ G -‬כיסוי חסר כפילויות‬
‫‪30‬‬
‫מקרה ‪V ⊈ X :2‬‬
‫‪ V → Ak ∈ G+‬היא הת"פ המפרה ‪3NF‬‬
‫יש גזירה של ‪ V → Ak‬מ‪ ,G -‬שחייבת‬
‫להשתמש בת"פ של תת‪-‬קבוצה ‪G1 ⊆ G‬‬
‫ב‪ G1 -‬אין אף ת"פ שצידה השמאלי הוא ‪X‬‬
‫(אחרת ‪ X ⊆ V+‬ולכן ‪ V‬מפתח‪-‬על של ‪S‬‬
‫והת"פ ‪ V → Ak‬אינה הפרה של ‪)3NF‬‬
‫מסקנה ‪ G1 ⊢ V → Ak :1‬וגם ‪X → Ak ∉ G1‬‬
‫‪31‬‬
‫מקרה ‪( V ⊈ X :2‬המשך)‬
‫תהי ‪ G2 ⊆ G‬קב' כל הת"פ ‪ X → Ai‬עבור‬
‫‪Ai ∈ V‬‬
‫מ‪ G2 -‬אפשר לגזור את ‪( X → V‬בעזרת‬
‫אקסיומת האיחוד)‬
‫‪( X → Ak ∉ G2‬אחרת ‪ Ak ∈ V‬ולכן‬
‫‪ V → Ak‬אינה הפרה של ‪)3NF‬‬
‫מסקנה ‪ G2 ⊢ X → V :2‬וגם ‪X → Ak ∉ G2‬‬
‫‪32‬‬
‫מקרה ‪( V ⊈ X :2‬סיכום)‬
‫מסקנה ‪ G1 ⊢ V → Ak :1‬וגם ‪X → Ak ∉ G1‬‬
‫מסקנה ‪ G2 ⊢ X → V :2‬וגם ‪X → Ak ∉ G2‬‬
‫לכן מ‪ G1 ⋃ G2 -‬אפשר לגזור את ‪X → Ak‬‬
‫ולפיכך ‪ X → Ak‬הנה ת"פ מיותרת ב‪,G -‬‬
‫בסתירה לכך ש‪ G -‬חסר כפילויות‬
‫בכל אחד משני המקרים‪ ,‬הראנו שהסכמה‬
‫‪ S=XA1…An‬אינה מפרה ‪3NF‬‬
‫‪33‬‬
‫סיום ההוכחה‬
‫נשאר להוכיח שאם מוסיפים סכמה ‪ S‬שהיא‬
‫מפתח של הסכמה המקורית ‪ ,R‬אז ‪ S‬מקיימת‬
‫‪3NF‬‬
‫בין האטריביוטים של מפתח של הסכמה‬
‫המקורית ‪ R‬לא יכולות להתקיים שום ת"פ‬
‫(אחרת זה לא מפתח)‬
‫לכן בין האטריביוטים של מפתח אין שום הפרה‬
‫של ‪3NF‬‬
‫‪34‬‬
‫תכונות הפירוק שהאלגוריתם מייצר‬
‫הפירוק שהאלגוריתם מייצר הנו בעל‬
‫התכונות הבאות‪:‬‬
‫כל סמכה בפירוק הנה ‪( 3NF‬הוכחנו)‬
‫הפירוק משמר את הת"פ (ברור)‬
‫הפירוק הנו ללא אובדן (צריך להוכיח –‬
‫אפשר למצוא הוכחה בספר של אולמן)‬
‫לאלגוריתם זמן ריצה פולינומיאלי‬
‫‪35‬‬