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

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

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

ادامه ی این محتوا را همراه ما باشید.

خواندن محتواهای زیر به شما توصیه می شود:

• عملگرها در پایگاه داده
• دستورات تعریف در بانک اطلاعاتی

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

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

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

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

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

sp'(S#, Sname, City)

تعریف میکردیم.

یعنی فیلد Status را از جدول S به جدول sp میبریم.

در اینصورت جدول Sp به صورت زیر می باشد:

جدول 1 – آموزش نرمال سازی

همواره ثابت S1  برای  Status  همانطور که مشاهده میگردد فیلد تکرار شده و بدین جهت Sp و مشخص است(عدد20)و بی جهت در جدول افزونگی اطلاعات داریم.

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

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

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

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

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

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

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

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

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  و نیز محتویات آن به صورت زیر خواهد بود:

First Normal Form – 1NF

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

طبق قواعد جامعیت نمی تواند Null باشد.

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

آنومالی به هنگام سازی” در این عمل با مشکل به هنگام سازی منتشر شونده مواجه هستیم زیرا مثلا شهر یک تهیه کننده به دفعات تکرار شده است. اگر این به هنگام سازی منتشر شونده انجام نشود داده های بانک ناسازگار می شوند.

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

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

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

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

نرمالسازی با فرم 2NF

صفت عمده

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

صفت عمده

علت بروز مشکلات فوق این است که 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

1. صفت هایی را که وابستگی انتقالی ایجاد کرده اند، با وابسته های آن ها کنار هم قرار می دهیم.
2. کلید اصلی ر ا با صفت های باقی مانده کنار هم قرار می دهیم.
3. صفت های کلیدی را به عنوان کلید خارجی در 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. سهولت در اعمال بعضی از محدودیت های جامعیتی

برای یادگیری مباحث بیشتر پایگاه داده کتاب پایگاه داده ی پیشرفته را بخوانید.

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

موفق باشید.

برای امتیاز به این نوشته کلیک کنید!

[کل: 0 میانگین: 0]

منبع: camelcase.ir