نرمال سازی در پایگاه داده

نرمال سازی در پایگاه داده

هنگام طراحی یک بانک اطلاعاتی رابطه‌ی، این سوال مهم مطرح می‌شود که (با توجه به داده‌های عملیاتی و ارتباط بین موجودیت‌ها، چند جدول باید طراحی کرد؟ در هر جدول چه فیلد‌های باید قرار گیرد؟ رابطه جدول‌ها باید چگونه شود؟)

نرمال سازی در پایگاه داده

فرض کنید جدول sp را به صورت

sp'(S#, P#, Qty, Status)

تعریف و جدول S را به صورت

sp'(S#, Sname, City)

تعریف می‌کردیم. یعنی فیلد Status را از جدول S به جدول sp می‌بریم. در این صورت جدول Sp به صورت زیر است:

همواره ثابت S1  برای Status‌ همان‌طور که مشاهده می‌گردد فیلد تکرار شده و بدین جهت Sp و مشخص است (عدد20) و بی جهت در جدول افزونگی اطلاعات داریم. حال سوال دیگری را مطرح می‌کنیم‌: (آیا میتوان تمام اطلاعات جداول را در یک جدول ریخت؟) جواب این سوال بله است. Sp ،p ،s‌ در این صورت بانک اطلاعاتی فقط یک جدول بوده و از دید کاربران و برنامه نویسان مساله بسیار ساده می‌شود، چرا که دیگر نیازی به پیوند طبیعی یا ضرب دکارتی جداول نداریم.

نرمال سازی در پایگاه داده، ولی این عمل 3 اشکال دارد:

1. افزونگی داده‌ها: قبلا بیان شده که افزونگی یعنی تکراربی رویه داده‌‏ها در بانک اطلاعات رابطه‌ای، تکرار داده‌ها تنها راه برقراری ارتباط بین جداول است و ازآن به عنوان کلید خارجی یاد می‌شود. تکرار بیش ازاین، بی رویه است و افزونگی نام دارد. بدیهی است که جدول بالا تکرار بی رویه دارد.
2. بی نظمی: وجود افزونگی در جداول مثال فوق باعث آنومالی در تغییر داده‌‏ها می‌شود. گشته و شهر آن را s1 ‌تغییر کند باید در تمام رکوردها به دنبال s1 مثلا اگرتغییر دهیم که عملی مشکل زا است.
3. مقادیر تهی: با اغدام جداول گاهی اوقات مجبور خواهیم بود برای نشان استفاده کنیم. NULL دادن بعضی از اقلام اطلاعاتی از قطعه‌ای را تهیه نکرد، آنگاه سطر مربوط به S مثلا فرض کنید تهیه کننده آن بصورت زیر ذخیره می‌شود:

S#	 Sname  Status   City	P#	Pname	Color	Weight	City	Qty

 S5	  Sn5	      30	 C1	NULL	NULL	NULL	NULL	NULL	NULL

مزایای نرمال سازی عبارتند از:

1. کاهش بعضی از آنومالی‌ها.
2. ساده کردن اعمال بعضی از قواعد جامعیت.
3. کاهش افزونگی‌ها.
4. ارایه یک طراحی بهتر و واضح‌تر با کم‌ترین اختلاط اطلاعات.

نکته: در بانک اطلاعاتی سه نوع افزونگی وجود دارد:

• افزونگی تکنیکی. (ناشی از وجود کلید خارجی)
• افزونگی طبیعی.
• افزونگی ناشی از طراحی بد و اختلاط و اطلاعات. نرمال سازی این افزونگی سوم را تا حد زیادی کاهش می‌دهد.

معایب نرمال سازی عبارتند از:

1. سربار گذاری بر روی سیستم چرا که در صورت نیاز باید پرتو‌ها را پیوند داد.
2. فرایند نرمال سازی در محیط‌های بزرگ که تعداد رابطه‌ها و صفات خاصه زیاد است، کاری زمانگیر بوده و یا فن همه وابستگی‌ها زمان زیادی می‌خواهد.
3. چون مجموعه کاهش پذیر وابستگی‌های تابعی یک رابطه، یکتا ‌نیست، لذا روش‌های مختلفی در طراحی وجود خواهد داشت و بدین ترتیب مشکل تصمیم گیری برای طراح پیش می‌آید.

اهداف نرمال سازی عبارتند از:

1. حذف بعضی از انواع افزونگی.
2. پرهیز ازآنومالی‌های به هنگام سازی.
3. سهولت در اعمال بعضی از محدودت‌های جامعیتی.

نکته: نرمال سازی یعنی تجزیه یک رابطه به تعدادی رابطه دیسگر که از نظر منطقی جدا از هم هستند و نه الزاما بصورت فایل‌های ذخیره شده جدا از هم.

سطوح نرمال

کاد (واضع مدل رابطه‌ای) در ابتدا سه سطح نرمال 1NF ،2NF ،3NF را تعریف کرد ولی بعدا دانشمندان دیگر، صورت‌های دیگری را نیز معرفی کردند.

سطوح نرمال بودن عبارتند از:

• 1NF
• 2NF
• 3NF
• BCNF
• 4NF
• 5NF
• DKNF

به طور کلی در مجموعه رابطه‌ها بعضی نرمال و بعضی غیر نرمال هستند. در واقع صور نرمال فوق، هریک از قبلی خود نرمال‌تر هستند. یعنی مثلا رابطه که 3NF است. حتما 2NF و 1NF است. به عبارتی دیگر در رابطه‌های 1NF برخی 2NF بوده و در رابطه‌های 2NF برخی 3NF هستند.

مختصری در ارتباط با First Normal Form) 1NF)

اگر و فقط اگر تمام صفات خاصه آن روی میدانهای اتوماتیک تعریف شده، به عبارتی دیگر صفت‌های آن از دامنه تو در تو نشوند. یعنی صفت ترکیبی موجود نشود. مثلا اگر جدولی شامل فیلد تاریخ هست که خود فیلد تاریخ است که خود فیلد تاریخ‌ 1NF از سه فیلد کوچیک‌تر (سال-ماه-روز) تشکیل شود، آنگاه جدول 1NF نیست. در واقع هر رابطه نرمالی 1NFاست. رابطه R به فرم 1NF است اگر و فقط اگر تمام صفات خاصه آن روی میدان‌های اتومیک تعریف شود، به عبارتی دیگر صفت‌های آن از دامنه تو در تو نیست. یعنی صفت ترکیبی موجود نشود.

First(S#, Status City, P#, Qty)

هم‌چنین فرض کنید در این بانک این قائده وجود دارد که وضعیت یک تهیه کننده از روی شهر او تعیین می‌شود یعنی city -> status بدین ترتیب نمودار وابستگی جدول First  و نیز محتویات آن به صورت زیر خواهد بود:

آنومالی درج: در جدول فوق نمی‌توان واقعیت “S7 در شهر C4 ساکن است” را درج کرد تا زمانی‌که ندانیم که چه قطعه‌ای را تهیه کرده است. دلیلش آن است که P# جزو کلید اصلی است و طبق قواعد جامعیت نمی‌تواند NULL شود. طبق قواعد جامعیت نمی‌تواند Null شود.

آنومال حذف: اگر این اطلاع را که “S3 از P2 به تعداد 200 عدد تهیه کرده است” را حذف کنیم اطلاع “S3 ساکن شهر C3 است” نیز ناخواسته حذف می‌شود. آنومالی به هنگام سازی در این عمل با مشکل به هنگام سازی منتشر شونده مواجه هستیم زیرا مثلا شهر یک تهیه کننده به دفعات تکرار شده است. اگر این به هنگام سازی منتشر شونده انجام نشود داده‌های بانک ناسازگار می‌شوند.

نرمالیزه کردن با فرم 2NF

رابطه R در صورت دوم نرمال است اگر و فقط اگر 1NF شود و هر صفت خاصه غیر کلید با کلید اصلی وابستگی تابعی کامل داشته و یا به عبارت دیگر هر صفت غیر کلید با کلیداصلی به طور کاهش ناپذیر وابسته است.

نمودار وابستگی این دو رابطه به شکل زیر خواهد بود:

صفت عمده

صفتی که جزء ‌تشکیل دهنده کلید است را صفت عمده می‌نامیم.

علت بروز مشکلات فوق این است که status با صفت S# (کلید اصلی) وابستگی کامل دارد و از سوی دیگر Status از طریق city نیز باید S# وابستگی دارد. اصطلاحا می‌گوییم یک وابستگی با واسطه (تراگذاری-تعدی tansitive‌) بین status و S# (با واسطه city‌) وجود دارد.

وابستگی یا واسطه یا انتقالی

اگر صفت خاصه B از رابطه R با صفت خاصه A از همین رابطه وابستگی داشته و صفت خاصه C از همین رابطه با صفت خاصه B بستگی دارد.

نرمالسازی به فرم 3NF

• متقابلا به یکدیگر وابسته هستند.
• با کلید اصلی رابطه R وابستگی تابعی کامل دارد.

نکته: منظور از صفت خاصه غیر کلید یعنی صفت خاصه‌ای که جزئی از کلید اصلی رابطه نیست.

تجزیه مطلوب

تجزیه‌ای که در آن با پیوند رابطه‌های حاصل از تجزیه، همان محتوای اطلاعاتی رابطه اولیه بدست آید و اطلاعات اضافی و زاید (ناموجود در رابطه اولیه) پدید نیاید و به علاوه تمام وابستگی‌های تابعی رابطه اولیه محفوظ بمانند تجزیه‌ای مطلوب است.

ضوابط ریسانن چیست؟

• تجزیه رابطه R به دو رابطه R1 و R2 مطلوب است اگر R1 و R2 مستقل از یکدیگر هستند.
• R1 و R2 مستقل از یکدیگرند اگر و فقط اگر صفت مشترک در دو رابطه، حداقل در یکی از  آن‌ها کلید کاندید است.
• تمام FD‌های موجود در R یا در مجموعه FD‌های R1 و R2 موجود است و یا از این  مجموعه FD‌ها قابل استنتاج است.

 الگوریتم تبدیل جدول 1NF به چند جدول 2NF

1. هر بخش از کلید اصلی را که صفت وابسته دارد، با آن صفت‌ها کنار هم قرار می‌دهیم.
2. کل کلید اصلی را با صفت‌های باقی مانده کنار هم قرار می‌دهیم.
3. سایر وابستگی‌ها را ترسیم می‌کنیم.

الگوریتم تبدیل جدول 2NF به چند جدول 3NF

• صفت‌هایی را که وابستگی انتقالی ایجاد کرده‌اند، با وابسته‌های آن‌ها کنار هم قرار می‌دهیم.
• کلید اصلی ر ا با صفت‌های باقی مانده کنار هم قرار می‌دهیم.
• صفت‌های کلیدی را به عنوان کلید خارجی در 2 تکرار می‌کنیم.

صورت نرمال سوم در واقع تفسیر وابستگی تابعی کامل و بی واسطه تمام صفات خاصه یا کلید اصلی است. هر چند با 3NF کردن رابطه‌ها، افزونگی به مقدار قابل توجه‌ای کاهش می‌یابد ولی از سوی دیگر تبدیل یک رابطه به چند رابطه 3NF باعث افت کارایی سیستم در عمل بازیابی می‌شود، چرا که برای یازیابی اطلاعات موجود در رابطه اولیه، باید روابط 3NF بطور مناسب با یکدیگر پیوند داده شوند و این عمل زمانگیر است. گاهی اوقات برای اجتناب از این عمل پوند در عمل تا حدی باید افزونگی را پذیرفت.

نکته: 3NF در مواردی که هر سه شرط زیر برقرار شوند ممکن است داشته:

1. وقتی که رابطه دارای چند کلید کاندید است.
2. وقتی که کلیدهای کاندید رابطه مرکب است.
3. وقتی که کلیدهای کاندید با یکدیگر اشتراک صفت خاصه داشته، یعنی اقلا یک صفت خاصه در آن‌ها مشترک شود.

نرمال سازی به فرم BCNF

رابطه R در سطح BCNF است اگر و فقط اگر هر دترمینان، کلید کاندید است به عبارتی دیگر جدولی در BCNF است که ستون‌های آن فقط به کلید‌های کاندیدش وابستگی تابعی دارد. هر صفت خاصه‌ای که صفت خاصه‌ای دیگر با آن وابستگی تابعی کامل دارد، دترمینان نامیده می‌شود. یعنی داریم:

 R. A -> R. B

 و این وابستگی کامل شود صفت خاصه A را دترمینان می‌نامیم.

مزایا نرمال سازی

1. کاهش بعضی از آنومالی‌ها.
2. ساده کردن اعمال بعضی از قواعد جامعیت.
3. کاهش بعضی از افزونگی‌ها.
4. ارائه یک طراحی بهتر و واضح تر با کم‌ترین اختلاط اطلاعات.

معایب نرمال سازی

1. سربار گذاری بر روی سیستم چرا که در صورت نیاز باید پرتو‌ها را پیوند داد.
2. ایجاد نوعی افزونگی. اگر قرار است تجزیه یک رابطه به رابطه نرمال‌تر، تجزیه‌ای خوب و بدون اضافات زائد است باید صفت مشترک در دو رابطه حداقل کلید کاندید یکی از آن دو شود. در این حال در رابطه دیگر کلید خارجی می‌شود و وجود کلید  خارجی خود سبب افزونگی است.
3. فرایند نرمال سازی در محیط‌های بزرگ که تعداد رابطه‌ها و صفات خاصه زیاد است، کاری زمانگیر بوده و یافتن همه وابستگی‌ها زمان زیادی می‌خواهد.
4. چون مجموعه کاهش پذیر وابستگی‌های تابعی یک رابطه، یکتا نیست، لذا روش‌های مختلفی در طراحی وجود خواهد داشت و بدین ترتیب مشکل تصمیم گیری برای طراح پیش می‌آید.

اهداف کلی نرمال سازی

1. حذف بعضی از افزونگی‌ها.
2. پرهیز از آنومالی‌های به هنگام سازی.
3. ایجاد یک طراحی که نمایش خوبی از دنیای واقعی است، یعنی درک شهودی آن ساده بوده و مبنای خوبی برای رشد آینده است.
4. سهولت در اعمال بعضی از محدودیت‌های جامعیتی.

برای یادگیری مباحث بیش‌تر پایگاه داده کتاب پایگاه داده‌ی پیشرفته را بخوانید. اگر سوالی در ارتباط با مسائل مربوط به نرمال سازی در پایگاه داده دارید در بخش نظرات این نوشته برای ما بنویسید.