צורות נורמליות: 3NF,BCNF

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

צורה נורמלית – BCNFBoyce-Codd Normal Form • מוטיבציה: תלות פונקציונלית במשהו שאיננו על-מפתח היא "דבר רע“. • הגדרה: תהי R סכמה רלציונית, ותהי F קבוצת תלויות פונקציונליות מעל R. R היא ב-BCNF בהינתן F אם לכל תלות פונקציונלית לא טריוויאלית כך ש- XYF+ , X הוא על-מפתח של R. • אם קיימת תלות לא טריוויאלית בקבוצת שדות שאיננה על-מפתח, תכנון הסכמות הרלציוניות הוא לקוי, ובמסד הנתונים עלולות להיווצר כפילויות. 236363 - DBMS, צורות נורמליות

BCNF – המשך • דוגמה: נתונה קבוצת התלויות הפונקציונליות:{עיר  (טלפון, קידומת) ,קידומת  עיר} = F. הסכמה (עיר, קידומת, טלפון)R איננה ב-BCNF: • הסבר: הסגור F+ כולל את התלות הלא טריוויאלית קידומת  עיר, אבל עיר איננה על-מפתח. • כפילויות במסד: למשל, הקידומת של חיפה (04) נשמרת במסד פעמים רבות, כמספר הטלפונים בחיפה. • פירוק ל-BCNF: אם סכמה רלציונית אינה נמצאת בצורה הנורמלית הרצויה (למשל BCNF), אפשר לפרק אותה לתתי סכמות שנמצאות בצורה נורמלית זו. 236363 - DBMS, צורות נורמליות

פרוק ל-BCNF • הגדרה: תהי R סכמה רלציונית, תהי F קבוצת תלויות פונקציונליות מעל R, ויהי = {R1,…, Rn} פרוק של R. הוא פרוק ל-BCNF אם כל תת-סכמה Ri היא ב-BCNF בהינתן RiF. • הערה: באופן דומה ניתן להגדיר פרוק לכל צורה נורמלית אחרת, למשל 3NF. • משפט: תמיד קיים פרוק ל-BCNF שמשמר מידע, אך לא תמיד קיים פרוק ל-BCNF שמשמר תלויות. 236363 - DBMS, צורות נורמליות

פירוק ל-BCNF – המשך • דוגמה: נתונה הסכמה (עיר, קידומת, טלפון)R וקבוצת התלויות הפונקציונליות:{עיר(טלפון,קידומת), קידומתעיר}=F. הפרוק {(עיר, קידומת)R2 ,(עיר, טלפון)R1 }=  הוא פרוק ל-BCNF. • פרוק זה משמר מידע, אך כפי שראינו הוא איננו משמר תלויות. • שימו לב: כל סכמה שיש בה שני אטריביוטים או פחות היא ב-BCNF, באופן טריוויאלי (למה?) 236363 - DBMS, צורות נורמליות

פירוק ל-BCNF – הערה(1) • יש לשים לב כי בפירוק של סכמה רלאציונית R אנחנו בודקים אם הפרוק הוא ב-BCNF ע"י כך שבודקים שכל תת-סכמה Ri היא ב-BCNF מעל RiF (ההיטל מחושב מעל F+). • לא מספיק לבדוק האם Ri מקיימת את תנאי ה-BCNF מעל קבוצת התלויות ב-F שמכילות תכונותב-Ri. 236363 - DBMS, צורות נורמליות

פירוק ל-BCNF – הערה(2) דוגמא: • R=(A,B,C,D),  C, C D}F={B,  = {(A,B,D), (B,C)} • טענה: הפירוק איננו ב-BCNF. • הסבר:R1∉BCNF כי D ∈ R1FB אולם B איננועל-מפתח של R1 • אולם: אם היינו בודקים את R1 מעל קבוצת התלויות ב-F שמכילות תכונות ב-R1 (אשר שווה לקבוצה ריקה), אזי היינו מקבלים כי R1∈BCNF (ההגדרה מתקיימת באופן ריק). • ההערה רלוונטית לכל צורה נורמאלית . 236363 - DBMS, צורות נורמליות

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

אלגוריתם לפרוק סכמה R ל-BCNF • תהי F קבוצת תלויות פונקציונליות: • חשב את F+. • {R}   . • אם כל הסכמות ב-  הן ב-BCNF – עצור. • מצא סכמה S  שאינה ב-BCNF, כלומר שקיימת תלות פונקציונלית X  Y ב- F+ כך ש-XY  S, Y  X ו- X אינו על-מפתח של S. בצע:   ( \ {S})  {S\(Y\X)}  {XY} • חזור ל-3. • הפרוק שאלגוריתם זה מוצא הוא משמר מידע, אך לא בהכרח משמר תלויות. 236363 - DBMS, צורות נורמליות

פירוק ל-BCNF – דוגמה • דוגמה: נתונה הסכמה R עבור מסד נתונים של בית חולים: R(dname, daddr, id, pname, paddr, pres_no, date, med_name, qnt) dname – שם של רופא (נניח שאין שני שני רופאים עם אותו שם) daddr – כתובת הרופא id – מס' זיהוי של חולה pname – שם החולה paddr – כתובת החולה pres_on – מס' מרשם date – תאריך מתן המרשם med_name – שם של תרופה המופיעה במרשם (באותו מרשם יכולות להופיע כמה תרופות שונות) qnt – כמות התרופה במרשם. • נתונות התלויות הבאות: F= {dnamedaddr, id)pname,paddr,dname), pres_no(date,id), (pres_no,med_name)qnt} 236363 - DBMS, צורות נורמליות

דוגמה - המשך • R איננה ב-BCNF, ולכן נפעיל את האלגוריתם: F= {dnamedaddr, id(pname,paddr,dname), pres_no(date,id), (pres_no,med_name)qnt} R(dname, daddr, id, pname, paddr, pres_no, date, med_name, qnt) dnamedaddr (dname, id, pname, paddr, pres_no, date, med_name, qnt) (dname, daddr) idpname (dname, id, paddr, pres_no, date, med_name, qnt) (id, pname) idpaddr (dname, id, pres_no, date, med_name, qnt) (id, paddr) 236363 - DBMS, צורות נורמליות

דוגמה - המשך • הפירוק של R (ל-BCNF, משמר מידע) הוא:  = {R1(dname,daddr), R2(id,pname), R3(id,paddr), R4(id,dname), R5(id,date,pres_no), R6(pres_no,med_name,qnt)} (dname, id, pres_no, date, med_name, qnt) iddname (id, pres_no, date, med_name, qnt) (dname, id) pres_nodate, id (pres_no, med_name, qnt) (pres_no, date, id) 236363 - DBMS, צורות נורמליות

צורה נורמלית שלישית - 3NF • הגדרה: תהי R סכמה רלציונית ותהי F קבוצת תלויות פונקציונליות מעל R. R היא ב-3NF בהינתן F אם לכל תלות פונקציונלית X  A (לא טריוויאלית) ב- F+, או ש-X הואעל-מפתח של R או ש-A שייך למפתח קביל של R. • פרוק ל-3NF מוגדר באופן דומה לפרוק ל-BCNF. • צורה נורמלית שלישית היא דרישה חלשה יותר מ-BCNF: כל סכמה שנמצאת ב-BCNF היא אוטומטית גם ב-3NF, אך ההפך אינו בהכרח נכון. 236363 - DBMS, צורות נורמליות

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

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

כיסוי מינימלי • בקבוצת תלויות פונקציונליות עלול להיות "מידע מיותר". • דוגמה: שתי הקבוצות F = {X Y⋃Z}, G = {X Y, X  Z}הן "שקולות", במובן ש- F+ = G+. • מטרה: להביא את כל הקבוצות השקולות של תלויות פונקציונליות לצורה אחידה. • צורה זו נקראת הכיסוי המינימלי (minimal cover) של קבוצות התלויות. 236363 - DBMS, צורות נורמליות

כיסוי מינימלי - הגדרה • הגדרה: תהי F קבוצת תלויות פונקציונליות. F היא מינימלית אם לכל תלות X  Y  F מתקיימות שלש הדרישות הבאות: • |Y| = 1 • F+ (F \ {X  Y})+– אין ב-F תלויות "מיותרות". • לכל Z  X מתקיים F+ (F \ {X  Y}  {Z  Y})+– אין ב-F תלות AX שבה X מכילה תכונות "מיותרות". 236363 - DBMS, צורות נורמליות

כיסוי מינימלי - המשך • במקום לחשב את F+ כדי לבדוק אם קבוצת תלויות F היא מינימלית, אפשר לבצע את הבדיקות הבאות: • עבור תנאי 2: נבדוק ש- F’  X  Y (כאשר F’ = F\ {X  Y} ), או באופן שקול ש- Y  X+F’. • עבור תנאי 3: נבדוק ש- F  (X \ B)  Y, או באופן שקול ש-Y  (X \ B)+F • הגדרה: תהי F קבוצת תלויות פונקציונליות. כיסוי מינימלי(minimal cover) של F הוא קבוצת תלויות פונקציונליות FC כך ש-FC מינימלית ו- F+ = FC+. • הכיסוי המינימלי אינו בהכרח יחיד. 236363 - DBMS, צורות נורמליות

אלגוריתם למציאת כיסוי מינימלי • תהי F קבוצת תלויות פונקציונליות: • G  {(X  A) | Y ((X  Y)  F  A  Y)}; • Repeat • For each f = X  A  G do if A  X+G’ where G’ = G\ {f} then G  G’; • 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, צורות נורמליות

מציאת כיסוי מינימלי - דוגמה • נתון R(A,B,C,D),F={A  B, BC  A, ABC  D, D  A}יש למצוא את הכיסוי המינימאלי של F. • בצעד ראשון נקבל G=F. • נפעיל שלב 1 – אין שינוי • נפעיל שלב 2 – נוריד A מ-  DABC, מכיוון ש-D  ({A,B,C}\{A})+G. קיבלנו:G={A  B, BC  A, BC  D, D  A} • נפעיל שלב 1 – נוריד את התלות BC  A, מכיוון ש- A  BC+G’(G’ = G\ {BC  A} ). קיבלנו:G={A  B, BC  D, D  A} • אין יותר שינויים, ולכן G הנ"ל הוא כיסוי מינימאלי של F. 236363 - DBMS, צורות נורמליות

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

פירוק ל-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} • לא קיימת ב-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, צורות נורמליות

צורות נורמליות: 3NF,BCNF