נרמול מסד נתונים

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

הצורך בנרמול

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

מספר זהות	שם	כתובת	מספר מכונית	שנת ייצור
1	בנימין	ירושלים	123	2000
2	אהוד	תל אביב	128	2004
1	בנימין	ירושלים	124	2003
1	בנימין	ירושלים	127	2002

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

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

בטבלה זו ישנה גם בעיית עדכון, עדכון כתובתו של אדם אחד ידרוש עדכון שורות רבות, שורה לכל אחת ממכוניותיו.

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

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

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

יצירת שדה "מספר חשבון בנק" בטבלת "פרטי אדם", אף היא עלולה להפר את חוקי הנירמול, מפני שאם יש לאדם מספר חשבונות רב, הוא יצטרך לפתוח מספר רשומות כמספר פרטי החשבון ויצטרך לשכפל את פרטי האדם שוב ושוב. פתרון הוא ליצור טבלת "חשבון בנק" שתקושר לטבלת "פרטי האדם" ויופיעו בה רשומות כמספר החשבונות.

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

מסיבות אלו מתעורר הצורך בנרמול.

מונחים במסד הנתונים היחסי

להלן מונחים שייעשה בהם שימוש במהלך תהליך הנרמול:

סכמה - אפיון הטבלה. נכללים בהגדרתה: שם הטבלה, שמות העמודות - השדות, סוג ערכי העמודות והמפתח העיקרי (באמצעות קו תחתי). למשל, לטבלה "אלפון", המכילה עמודה של שם, עמודת תעודת זהות ועמודת גיל, יש את הסכמה: אלפון (שם, תעודת זהות, גיל). ניתן גם לרשום סכמה באופן מלא בצורה הבאה: אלפון (שם:מחרוזת(14), תעודת זהות:מחרוזת (9), גיל:בית).
סכמה טבלאית - אוסף כל הסכמות של כל הטבלאות במסד הנתונים. לכל שם טבלה ולכל שדה באותה טבלה חייב להיות שם חד ערכי. שדות הקשר בין טבלאות מקושרות יהיו שדות בעלי שמות זהים.
מפתח - תכונה או אוסף תכונות מתוך הסכמה שדי בהם כדי לזהות חד-חד-ערכית את הרשומה. למשל, בסכמה (תעודת זהות, שם אדם, כתובת, מספר מכונית, שנת ייצור המכונית), די בתעודת זהות ובמספר מכונית כדי לאפשר את מילוי כל שדות הנתונים האחרים. אין אפשרות שתעודת הזהות ומספר המכונית יהיו זהים ברשומת טבלה אחרת.
מפתח ראשי - התכונה המרכזית שעל פיה מזהים את הרשומה בהתייחסות לטבלאות אחרות. בסכמה (תעודת זהות, שם אדם, כתובת, מספר מכונית, שנת ייצור המכונית) תעודת הזהות ומספר המכונית הם כאמור המפתח, אך ברור מקל וחומר שגם תעודת זהות, מספר מכונית ושנת ייצור מכונית הם מפתח, כלומר קיים עבורם מילוי אפשרי אחד בלבד של יתר השדות. אם כך (תעודת הזהות, מספר המכונית) הוא מפתח עם מספר מרכיבים מינימלי. מפתח כזה הוא המפתח בניגוד למפתח עם מספר מרכיבים לא מינימלי הקרוי מפתח על. ייתכן מצב שבו יש מספר מפתחות אפשריים עם מספר רכיבים מינימלי. במקרה כזו ייבחר אחד מהם לשמש מפתח ראשי והשאר ייחשבו למפתחות משניים.
תלות פונקציונלית - מוגדרת כתלות חד ערכית של תכונה אחת בתכונה שנייה. אם השם והכתובת תלויים בתעודת הזהות, דהיינו עבור תעודת זהות מסוימת קיים שם אחד בלבד וכתובת אחת בלבד, מכונה הקשר בין תעודת הזהות לבין השם והכתובת כתלות פונקציונלית.
תלות פונקציונלית רב ערכית - ייתכן ששדה מסוים תלוי בשדה אחר, אך לא באופן חד-חד-ערכי. ברור שמספר המכונית תלוי בתעודת הזהות של בעל המכונית. אם נדע זהותו של אדם, נדע למלא את רשומת המספר המכונית על פי המכוניות שבבעלותו, אך מכיוון שייתכנו מספר מכוניות, אין זו תלות חד ערכית.
שמירת מידע - פירוק הוא משמר מידע, אם ניתן לצרף מחדש את הטבלאות המפורקות כדי לקבל את הטבלה בצורתה המקורית. הפירוקים על פי אלגוריתם הנרמול הם משמרי מידע.
נשתמש במונח מרכיב לתאר תכונה של הסכמה או קבוצת תכונות שלה. בדוגמה לעיל, למשל (מס' מכונית) או (תעודת זהות, מס' מכונית).

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

צורה נורמלית ראשונה - 1NF

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

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

דוגמאות:

יצירת שדה "ילדים" בטבלת "פרטי האדם", שלתוכו מוכנסים כל שמות הילדים מופרדים בתו כלשהו לא תקינה, מכיוון שהערך אינו אטומי. צריך להוסיף טבלה נפרדת שתכלול שמות הילדים. בכל שורה יהיה שם של ילד אחר.
יצירת שדה "מספר ילדים" בטבלת "פרטי האדם", אף היא יוצרת בעיית נרמול, מפני שמספר הילדים צריך להיות מחושב דינמית בשאילתא על פי מספר הרשומות של ילדים לאדם ולא כמספר קבוע וסטטי בטבלה, וכפילות זו עלולה לתת נתונים סותרים.
בעיית נרמול מתעוררת גם עקב יצירת שדות רציפים לאותו נושא, "מספר חשבון בנק1", "מספר חשבון בנק2", "מספר חשבון בנק3", בטבלת "פרטי האדם", מפני שאינם שדות ייחודיים. במקרה זה נרמול הטבלה יהיה ביצירת טבלה נפרדת של "מספרי חשבון", והוספת שורה נפרדת לכל חשבון.

צורה נורמלית שנייה - 2NF

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

אלגוריתם הנרמול:
אם הסכמה היא (X,Y,Z,A), ואם (X,Y) הם המפתח ו-Z תלוי ב-X, יש להפריד את הטבלה לשתי טבלאות נפרדות: (X,Y,A) ו-(X,Z). כלומר יש ליצור טבלה נפרדת שתכלול את התלות הפונקציונלית, ולהשמיט מהטבלה המקורית את המרכיב שיש לו תלות פונקציונלית בחלק מהמפתח.

צורה נורמלית שלישית - 3NF

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

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

אלגוריתם הנרמול:
יש להפריד את הטבלה לשתי טבלאות נפרדות. אם הסכמה היא (X,Y,Z,A) ואם X הוא מפתח ו-Y גורר את Z, נפריד לטבלאות עם הסכמות: (X,Y,A) ו-(Y,Z).

צורה נורמלית בויס-קוד - BCNF

צורה זו קרויה על שם שני החוקרים, בוייס וקוד, שמצאו דרך להגדיר צורה ששקולה לחוקי הנרמול 2 ו-3 גם יחד ואף במקרים גבוליים טובה מהם.

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

אלגוריתם הנרמול:
אם (X,Y,Z,A) היא הסכמה, ואם X,Y הוא מפתח אפשרי ו-Y,Z מפתח אפשרי ו-Z גורר את X, נפריד למרכיבים (Y,Z,A) ו-(Z,X).

צורה נורמלית רביעית - 4NF

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

צורה נורמלית חמישית - 5NF

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

יוצאים מהכלל

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

ראו גם

מסדי נתונים - מונחים

לקריאה נוספת

רז הייפרמן, בסיסי נתונים ושפת SQL, הוצאת הוד-עמי, עמ' 175
ג'ון ויסקאס, המדריך השלם לאקסס 97, הוצאת פוקוס ומיקרוסופט פרס, פרק רביעי, עמ' 77-105

קישורים חיצוניים

נרמול בסיס נתונים, רועי, אתר וובמאסטר
ויליאם קנט, מדריך פשוט לחוקי הנרמול (אנגלית)
פרק חמישי - נרמול מסדי נתונים, אתר אית"ן