צורות נורמאליות

1. צורות נורמאליות • צורה נורמאלית (Normal Form) היא תכונה של סכמה רלציונית, המודדת את "טיב" הסכמה מבחינת מניעת כפילויות. • BCNF • 3NF • 4NF (ילמד בהרצאה) 236363 - DBMS, Design

2. צורה נורמאליתBCNF – Boyce-Codd • הגדרה: תהי R סכמה רלציונית ותהי F קבוצת תלויות פונקציונאליות מעל R. הסכמה R היא ב-BCNF אם לכל תלות פונקציונאלית X → Y∈F+ כך ש- Y⊄X, X הוא על-מפתח של R. BCNFמבטאת את העובדה שתלות פונקציונאלית במשהו שאינו מפתח-על היא "דבר רע". אם קיימת תלות לא טריוויאלית בקבוצת שדות שאיננה מפתח על אזי תכנון הסכמות הרלציוניות הוא לקוי, ובמסד עלולות להיווצר כפילויות. 236363 - DBMS, Design

3. איך נבדוק אם סכמה ב-BCNF? • על פי ההגדרה: • יש לחשב את F+ • עבור כלX→Y∈ F+ יש לבדוק אם X הינו על-מפתח. הבעיה: הגודל של F+הוא אקספוננציאלי בגודל של R. משפט: אם סכמה רלציונית R איננה ב-BCNF יחסית ל-F, כלומר קיימת תלותX→Y∈ F+שמפרה את תנאי ה-BCNF, אזי קיימת תלות Z→W∈ Fשמפרה את תנאי ה-BCNF. מסקנה: מספיק לבדוק עבור כל X→Y∈ F. אין צורך לחשב את הסגור של F. 236363 - DBMS, Design

4. BCNF – דוגמא דוגמה: בהינתן הסכמה (Cust_Id, Track, Faculty, Book_Name)R וקבוצת התלויות הפונקציונליות F={Cust_Id→Track,Track → Faculty} האם R ב-BCNF? • ב-F קיימת התלות → FacultyTrack, אך Track אינו על-מפתח, משום כך R איננה ב-BCNF. • מאחר ו-R אינה ב-BCNF קיימת כפילות במסד.למשל, שם הפקולטה CS מופיע ברלציה פעמים רבות כמספר הספרים שהוזמנו ע"י סטודנטים השייכים למסלולים של הפקולטה CS. • פתרון: פירוק ל-BCNF - פירוק של R לתתי-סכמות כך שכל אחת מהן נמצאת ב-BCNF יחסית לתלויות הרלוונטיות לה. 236363 - DBMS, Design

5. צורה נורמאלית BCNF – Boyce-Codd • הגדרה: תהי R סכמה רלציונית, תהי F קבוצת תלויות פונקציונאליות מעל R, ויהי {R1, R2 ,.., Rn}ρ= פירוק של R. ρהוא פירוק ל-BCNF אם כל תת-סכמה Ri היא ב-BCNF בהינתן πRiF. (הערה: באופן דומה מגדירים פירוק לצורה הבאה שנראה 3NF). 236363 - DBMS, Design

6. פירוק סכמה R לBCNF- (בהינתן קבוצת תלויות F)אלגוריתם 1 {R} ρ ← . אם כל הסכמות ב- ρ הן ב-BCNF – עצור. מצא סכמה S ∈ ρ שאינה ב-BCNF, כלומר שקיימת תלות פונקציונלית X→Y ∈ πsF כך ש-Y ⊈ X ו- X אינו על-מפתח של S. בצע: ρ ← (ρ \ {S}) ⋃ {S\(Y\X)} ⋃ {XY} חזור ל-2. • הפירוק שאלגוריתם זה מוצא הוא משמר מידע אך לא בהכרח משמר • תלויות. • החיסרון של אלגוריתם זה הוא שמציאת πsF נעשת בזמן אקספוננציאלי. • בשקפים הבאים נראה אלגוריתם פולינומיאלי אבל קצת פחות אינטואטיבי. 236363 - DBMS, Design

7. דוגמה 236363 - DBMS, Design נתונה הסכמה R (ציונים של סטודנטים), וקבוצת תלויות F: R(sid , sname , cno , cname, grade) F = {sid → sname, cno → cname, (sid , cno ) → grade} sid -- מס' סטודנט sname -- שם סטודנט cno -- מס' קורס Cname -- שם קורס Grade -- ציון

8. המשך • יש לבדוק האם קיימת תלות שצידה השמאלי אינו מפתח: • עבור התלות sid → sname מתקיים כי sid אינו מפתח ולכן נפרק:{R1(sid, sname), R’(sid , cno , cname, grade)}=ρמתקיים כי כיסוי πR’F הוא{cno → cname, (sid , cno ) → grade} • עבור התלות cno → cname מתקיים כיcno אינו מפתח ולכן נפרק:{R1(sid, sname), R2(cno , cname), R’(sid , cno , grade)}=ρמתקיים כי כיסוי πR’F הוא{ (sid , cno ) → grade} • עבור התלות (sid , cno ) → grade מתקיים כי (sid , cno ) הוא מפתח ולכן נקבל:{R1(sid, sname), R2(cno , cname), R3(sid , cno , grade)}=ρ

9. דוגמה - המשך • וכך יצא הפירוק:F = {sid → sname, cno → cname, (sid, cno) → grade} R(sid, sname, cno, cname, grade) R’(sid, cno, cname, grade) R1(sid, sname) R3(sid, cno, grade) R2(cno, cname) • לפי הבנייה, הפירוק ל- {R1,R3,R4} הוא משמר מידע וב-BCNF. 236363 - DBMS, Design 9

10. פירוק סכמה R לBCNF- (בהינתן קבוצת תלויות F)אלגוריתם 2 • decompose(Z) מחזירה פירוק משמר מידע של Z כך ש-Y הוא ב-BCNF ואת Z’ יש צורך להמשיך לפרק עד שמוחזר STOP. • לפעמים הוא יפרק "בטעות" גם רלציה שהיא כבר כן ב-BCNF (תוך כדי שימור מידע). שימו לב: אם Z ב-BCNF ייתכן ועדיין נמצא A ו-B כאלה והאלגוריתם ימשיך לפרק את Z.כרגע אין בידינו אלגוריתם יעיל להכרעה האם תת סכמה היא ב-BCNF. 236363 - DBMS, Design

11. דוגמה {sid,sname,cno,cname,grade} {sid,sname,cno,grade} {sid,sname,cno} F = {sid → sname, cno → cname, (sid, cno) → grade} R(sid, sname, cno, cname, grade) נבצע decompose ל-R ע"פ האלגוריתם \{grade,cname} →grade \{sname,grade} → sname \{sname,cno} → sname {sid,sname} Z = {sid,sname,cno,cname,grade}\{sname} = {sid,cno,cname,grade} Y = {sid,sname} 236363 - DBMS, Design

12. דוגמה - המשך F = {sid → sname, cno → cname, (sid, cno) → grade} Z = {sid,cno,cname,grade} נמשיך ונבצע decompose ל-Z ע"פ האלגוריתם \{cname,sid} → cname \{cname,grade} → cname {cno,cname} Z = {sid, cno,cname,grade}\{cname} = {sid,cno,grade} Y = {cno,cname} בשלב זה לא ניתן יותר למצוא A ו-B כך ש- Z\AB →A ולכןdecompose תחזיר STOP. {sid,cno,cname,grade} {cno,cname,grade} 236363 - DBMS, Design

13. דוגמה - המשך • וכך יצא הפירוק:F = {sid → sname, cno → cname, (sid, cno) → grade} R(sid, sname, cno, cname, grade) R2(sid, cno, cname, grade) R1(sid, sname) R3(sid, cno, grade) R4(cno, cname) • לפי הבנייה, הפירוק ל- {R1,R3,R4} הוא משמר מידע וב-BCNF. 236363 - DBMS, Design

14. פירוק סכמה R לBCNF- (בהינתן קבוצת תלויות F)אלגוריתם 2 • טענה 1: אם Z אינה ב-BCNF אזי קיימים A ו-B כך ש- {Z\AB}→A . אם Z אינה ב-BCNF קיימת תלות X→Y כך ש-X אינו על-מפתח ו- Y⊄X. מאחר ו-X אינו על מפתח קיים B כך שלא מתקיים X →B.מכך נובע X ⊆{Z\B}Y⊄Xולכן קיים A כך ש- A ∈Y ו-A ∉X. לכן גם A≠B.מכך נובע X ⊆{Z\AB}מכך נובע {Z\AB} →Yכך נובע{Z\AB} →A. 236363 - DBMS, Design

15. פירוק סכמה R לBCNF- (בהינתן קבוצת תלויות F)אלגוריתם 2 • טענה 2: הפירוק {Z\A,Y} הוא משמר מידע. A היא התכונה האחרונה כך ש (Y\AB) →A.לאחר מכן Y הופך להיות Y\B.לכן בסופו של דבר נקבל (Y\A) →A. מאחר ו-Z מכיל את כל התכונות בסכמה אז Z\A ⋂ Y = Y\Aנבחין כי A=(Y\(Z\A))ומכאן (Z\A ⋂ Y) →(Y\(Z\A)) וזהו התנאי לשימור מידע. 236363 - DBMS, Design

16. פירוק סכמה R לBCNF- (בהינתן קבוצת תלויות F) הערות • הפירוק שהאלגוריתמים האלה מוצאים הוא משמר מידע אך לא בהכרח משמר תלויות. • לא כל פירוק בצורת BCNF משמר מידע. רק אם הפירוק נוצר תוך כדי שימור מידע כגון באלגוריתמים האלה אז התוצאה הסופית היא משמרת מידע. 236363 - DBMS, Design

17. צורה נורמאלית BCNF – Boyce-Codd תמיד קיים פירוק ל-BCNF שמשמר מידע , אך לא תמיד קיים פירוק ל-BCNF שמשמרתלויות. • משפט: קיימים R סכמה רלציונית ו- F קבוצת תלויות פונקציונאליות מעל Rעבורם לא קיים פירוק משמר מידע ומשמר תלויות ל-BCNF. 236363 - DBMS, Design

18. BCNF ושימור תלויות • הוכחה ע"י דוגמה: עבור הסכמה (עיר, קידומת, טלפון)Rוקבוצת התלויות {עיר→(טלפון,קידומת), קידומת→עיר}=Fלא קיים פירוק BCNF, משמר מידע ומשמר תלויות. • הסיבה: על מנת לשמר את התלות עיר→(טלפון,קידומת) כל שלושת האטריביוטים חייבים להופיע בסכמה אחת. 236363 - DBMS, Design

19. BCNF לעומת שימור תלויות • לעתים קרובות יש לבחור בין שימור תלויות לבין BCNF. • קריטריון לבחירה: אופן השימוש הצפוי במסד הנתונים: • הרבה עדכונים של שדה עם כפילויות בסכמה המקורית (החלפת קידומת של עיר) ⇐ פירוק ל-BCNF (מונע כפילויות):(עיר, קידומת)R2,(עיר, טלפון)R1. • הרבה הוספות/עדכונים של שדות המופיעים בתלות שלא נשמרת בפירוק (למשל, מס' טלפון) ⇐ללא פירוק (שימור תלויות): (עיר, קידומת, טלפון)R . לשם כך נשתמש בצורה נורמלית 3NF 236363 - DBMS, Design

20. צורה נורמאלית 3NF • הגדרה: תהי R סכמה רלציונית ותהי F קבוצת תלויות פונקציונאליות מעל R. R היא ב-3NF אם לכל תלות פונקציונאלית X→A∈F+ כך ש- A ∉ X, • X הוא מפתח על של R • או A מוכל במפתח קביל של R. 236363 - DBMS, Design

21. דוגמה • הסכמה (עיר, קידומת, טלפון) R בהינתן התלויות הפונקציונליות: F {עיר→(טלפון,קידומת), קידומת→עיר}= נמצאת ב-.3NF • הסיבה: • המפתחות הקביליםשל הסכמה הם (עיר, טלפון) ו-(קידומת,טלפון). • כל תלות מתוך F מקיימת את תנאי ה-3NF. • כמו ב-BCNF גם כאן מספיק לבדוק רק את התלויות של F. 236363 - DBMS, Design

22. צורות נורמאליות • תמיד מתקיים: כל סכמה שהיא ב-BCNF היא גם ב-3NF ההפך לא בהכרח • BCNF מונעת יותר כפילויות בלתי רצויות מאשר 3NF • תמיד קיים פירוק ל-3NF שהוא משמר מידע ותלויות(ב-BCNF זה לא כך ולכן לעיתים נעדיף פירוק ל-3NF אף על פי שהוא מונע פחות כפילויות מ-BCNF) 236363 - DBMS, Design

23. אלגוריתם לפירוק סכמה R ל-3NF • בהינתן כיסוי מינימלי של תלויות פונקציונליות F: • אם קיימת ב-F תלות פונקציונלית שכוללת את כל התכונות ב-R, התשובה היא {R} - עצור. • לכל קבוצת תלויות פונקציונליות X → AnX → A2,…,X → A1,התלויות באותו X, צור סכמה }{ XA1A2 ...An. • אם אין אף סכמה המכילה מפתח קביל של R, הוסף סכמה שהיא מפתח קביל כלשהו של R. הפירוק שאלגוריתם זה מוצא הוא משמר מידע ותלויות. 236363 - DBMS, Design

24. פירוק ל-3NF – דוגמה תרגיל: פרק הסכמה הבאה ל-3NF כאשר נתון: R(dname, daddr, id, pname, paddr, pres_no, date, med_name, qnt) F = {dname → daddr, id → pname, id → paddr, id → dname, pres_no → date, pres_no → id, (pres_no, med_name) → qnt} 236363 - DBMS, Design

25. פירוק ל-3NF – דוגמה פתרון: • לא קיימת ב-F תלות פונקציונלית המכילה את כל התכונות ב-R. • ניצור סכמות לפי התלויות הפונקציונליות: R1(dname, daddr) R2(id, pname, paddr, dname) R3(pres_no, date, id) R4(pres_no, med_name, qnt) • R4 כוללת את המפתח הקביל (pres_no, med_name), ולכן אין צורך להוסיף עוד סכמה. 236363 - DBMS, Design

26. צורה נורמאלית 3NF • תרגיל: פרק הסכמה הבאה ל-3NF כאשר נתון: R(sid , sname , cno , cname, grade) F = {sid → sname, cno → cname, (sid , cno ) → grade} • sid -- מס' סטודנט • sname -- שם סטודנט • cno -- מס' קורס • Cname -- שם קורס • Grade -- ציון R1(sid , sname), R2(cno, cname), R3(sid, cno, grade) 236363 - DBMS, Design

27. חורף 08 מועד א' נתונה הסכמה R(A,B,C,D,E,H) וקבוצת התלויות הפונקציונליות F={AB →H, E →BC, D →H, A →DE, C →E, D →BH} סעיף 1: מצאו כיסוי מינימלי ל-F תזכורת: F היא מינימאלית אם לכל תלות X→Y ∈ Fמתקיימות שלוש הדרישות הבאות: • |Y| = 1 • F+≠ (F \ {X → Y})+( אין ב-F תלויות "מיותרות".) • לכל Z ⊂ X מתקיים F+≠ ((F \ {X → Y})  {Z → Y})+(אין ב-F תלות A→X שבה X מכילה תכונות "מיותרות".) G ← {(X→ A) | Y ((X →Y) ∈ F  A ∈ Y)}; Repeat • For each f = X → A ∈ G do if A ∈ X+G\ {f} then G ←G\ {f}; • For each f = X → A ∈ G and B ∈ X do if A∈(X\{B})+G then G←(G\{X → A})  {X\{B} → A}; until no more changes to G 236363 - DBMS, Design

28. המשך • אתחול:G={AB →H, E →B, E →C D →H, A →D, A →E, C →E, D →B, D →H} • שלב 1: • נוריד את התלות AB → H, מכיוון ומתקיים A ∈ BC+G\{AB → H}קיבלנו G={E →B, E →C D →H, A →D, A →E, C →E, D →B, D →H} • נוריד את התלות D → H, מכיוון ומתקיים H ∈ D+G\{D → H}קיבלנו G={E →B, E →C D →H, A →D, A →E, C →E, D →B} • שלב 2:אין תלות בעלת יותר מאיבר אחד בצד שמאל • שלב 1:אין שינוי • קיבלנו:G={E →B, E →C, D →H, A →D, A →E, C →E, D →B} 236363 - DBMS, Design

29. המשך G={E →B, E →C, D →H, A →D, A →E, C →E, D →B} נתון הפירוק ρ = {R1(A, B, D), R2(A, C), R3(C, D, E, H)} סעיף 2: האם הפירוק הוא ב-BCNF? נבדוק כל תת סכמה יחסית להיטל על G: • R1אינה נמצאת ב-BCNF. מתקיים כי כיסוי πR1G הוא{A →B, A →D, D →B} מתקיים כי D אינו על מפתח ב- R1 הפירוק אינו נמצא ב-BCNF 236363 - DBMS, Design

30. המשך ρ = {R1(A, B, D), R2(A, C), R3(C, D, E, H)}, G={E →B, E →C, D →H, A →D, A →E, C →E, D →B} סעיף 3: האם הפירוק משמר מידע? נפתור בעזרת האלגוריתם לבדיקת שימור מידע D→Ht1,t3 A→Dt1,t2 C→Et2,t3 D→Bt1,t3 236363 - DBMS, Design

31. המשך G={E →B, E →C, D →H, A →D, A →E, C →E, D →B} קיבלנו שורה ללא אינדקסים ולכן הפירוק משמר מידע E→Bt2,t3 D→Ht1,t2 236363 - DBMS, Design

32. המשך ρ = {R1(A, B, D), R2(A, C), R3(C, D, E, H)} G={E →B, E →C, D →H, A →D, A →E, C →E, D →B} סעיף 4: האם הפירוק משמר תלויות? תזכורת: R סכמה רלציונית,F קבוצת תלויות פונקציונאליות מעל R, ו- {R1, R2 ,.., Rn}ρ= פירוק של R. 236363 - DBMS, Design

33. המשך ρ = {R1(A, B, D), R2(A, C), R3(C, D, E, H)} G={E →B, E →C, D →H, A →D, A →E, C →E, D →B} התלות E →B אינה מוכלת באף סכמה ולכן נבדוק אם היא נשמרת לפי האלגוריתם: • Zf ={E} • Zf⋂R1={} no change to Zf • Zf⋂ R2 ={} no change to Zf • Zf⋂ R3 = {E} {E}+F⋂R1={C,E}  Zf= {C,E} • Zf⋂ R1={} no change to Zf • Zf⋂ R2 ={} no change to Zf • קיבלנו כי Zf ={C,E}ואינו מכיל את B, לכן התלות E →B אינה נשמרת בפירוק  הפירוק אינו משמר תלויות 236363 - DBMS, Design