پایگاه داده

نرمال سازی بانك های اطلاعاتی
قبل ار مطالعه اين مطلب پيشنهاد می گردد به دليل ضرورت آشنائی خوانندگان با مفاهيم بانك های اطلاعاتی رابطه ای ، مقاله "بانك های اطلاعاتی رابطه ای : مفاهيم و تعاريف " ، مطالعه گردد .
نرمال سازی ( Normalization )  يا به تعبيری هنجار سازی فرآيندی است در رابطه با بانك های اطلاعاتی كه با دو هدف عمده زير انجام می شود :

·         کاهش افزونگی اطلاعات ، به اين معنی که اطلاعات فقط در يک مكان (جدول) ذخيره و در تمام بانک با استفاده از روابط منطقی تعريف شده (RelationShip) قابل دسترسی باشد .

·         حفظ يکپارچگی اطلاعات ، به اين معنی که اعمال تغييرات بر روی اطلاعات ( نظير ايجاد ، بهنگام سازی و حذف ) در يك مكان انجام و به دنبال آن آثار تغييرات در تمام بانك مشاهده گردد .  برای روشن شدن مفهوم يکپارچگی بد نيست به مثال ذيل توجه نمائيد :
فرض كنيد در يك بانك اطلاعاتی دارای دو موجوديت كتاب و نويسنده باشيم . هر يك از موجوديت های فوق دارای المان های اطلاعاتی (Attribute) مختص به خود می باشند . به عنوان نمونه موجوديت "كتاب" دارای المان اطلاعاتی نام نويسنده  و  موجوديت "نويسنده " دارای المان های اطلاعاتی متعددی نظير نام نويسنده ، آدرس نويسنده و ... باشد .  در صورتی كه در موجوديت "کتاب"  يک رخداد (رکورد) ايجاد نمائيم بدون اينکه نام نويسنده آن را در موجوديت "نويسنده" ايجاد کرده باشيم ،  دچار يک ناهمگونی اطلاعات خواهيم شد .

با توجه به اهداف فوق می توان گفت كه فرآيند نرمال سازی از ناهنجاری های بوجود آمده به دليل بروز تغييرات در بانك جلوگيری خواهد نمود . با اعمال فرآيند نرمال سازی ، يك بانك اطلاعاتی كارآ و مطمئن را خواهيم داشت .

   يكي از مهترين فرآيند ها در طراحي بانك اطلاعاتي فرمان سازياست . يك سئوال خيلي مهم براي طراحي بانك اين است كه با توجه به موجوديت ها و ارتباط بين آنها چند تا جدول بايد طراحي كرد و در هر جدول چه فيلدهاي بايد درست كنيم فرمان سازي براين سئوال ها جواب مي دهد . فرض كنيد در جداول sp  فيلد STATUS را از جدول S يه جدول SP منتقل كنيم در اين صورت فيلد STATUS براي توليد كنندگان به دفعات تكرار ميشود .

مي توانيم جداول SP را در يك جداول بريزيم در اين صورت بعضي سوالات ما نيازي به جوين و پيوند طبيعي ندارد .

تركيب اين سه جدول مشكلاتي رادر بر دارد البتهد باعث ميشود كه ضرب كارتزين و عمل جوين انجام نشود .

 مشكلات:

الف) افزونگي داده ها (DATA  REDUNDANCY) هر توليد كننده يا هر قطعه چندين بار نام آن تكرار شده است .

ب) وجود افزونگي در جدول باعث آنو مالي در تقيير داده ها مي شود مثلا تقيير دادن شهر S1 مستلزم جستجو درتمام جدول است .

ج) براي نشان دادن بعضي از اطلاعات از مقادير تهي NULL VALUE  استفاده شده است.

سطوح نرمال سازي:

سطوح مختلف نرمال سازي به شكل مقابل است.

 1NF

 

2NF                                                                                                                                                                             

                                                                           

3NF

 

BCNF

 

4NF

 

5NF

 

DR NF

 

 

نكته:

رابطه هايي كه NF  Iهستند تعدادي شان 2NF هستند و از بين آنها تعدادي 3NF هستند . رابطه ايي كه 3NF باشد حتما 1NF و2NF هم است

 

 

 

فرم INF:

موجوديت و يا جدولی در فرم اول نرمال است كه تمامی المان های اطلاعاتی آن ( منظور Attribute است ) يكتا و يا اصطلاحا" atomic باشند . برای روشن شدن اين موضوع فرض كنيد دارای موجوديتی با نام "فاكتور فروش " باشيم . 

با مشاهده موجوديت فوق متوجه اين موضوع خواهيم شد كه المان های كالا ، تعداد كالا و قيمت واحد كالا بيش از يك مرتبه در موجوديت وجود داشته و اصطلاحا" يك گروه تكرار را تشكيل می دهند . برای اجرای مدل فيزيكی اين موجوديت ناچار خواهيم بود در طراحی جدول آرايه ای به طول ثابت ( به عنوان نمونه با ده عضو ) تعريف و در آن به ترتيب كالای 1 تا 10 را تعريف نمائيم . 

مشکل : طراحی فوق ما را با دو مشکل عمده روبرو خواهد ساخت : اول اين كه  کارائی بانک اطلاعاتی پائين خواهد آمد (اگر در آينده تعداد کالاهای فاکتور فروش بيش از 10 کالا باشد ، آنگاه مجبور خواهيم بود طراحی جدول مربوطه و متعاقب آن نرم افزارهائی که از آن استفاده می كنند را تغيير دهيم ) و مشکل دوم اين كه  بسياري از فاکتورها لزوما" دارای 10 کالا نيستند و بنابراين محتوی بسياری از فيلدها در جدول فوق خالی (داراي ارزش Null) خواهد ماند و حجم زيادی از فضای ديسک هدر خواهد رفت .

راه حل : برای حل اين مشکل کافی است تمامی گروه های تکرار و يا آرايه ها را از موجوديت خارج کرده و به موجوديت ديگری منتقل نمائيم . در چنين مواردی ، كليد اصلی موجوديت اول را به عنوان بخشی از كليد اصلی موجوديت جديد قرار داده و با تلفيق يكی ديگر از آيتم های اطلاعاتی موجوديت جديد كه تضمين كننده يكتا بودن ركوردهای آن موجوديت ( جدول ) است ، كليد اصلی موجوديت ايجاد می گردد . بدين ترتيب ، يك ارتباط بين موجوديت پدر و فرزند بر اساس كليد اصلی موجوديت پدر برقرار خواهد شد .
مجددا" به موجوديت "فاكتور فروش " مثال قبل پس از تبديل به فرم اول نرمال توجه نمائيد : 

به طور خلاصه می توان گفت كه هدف از فرم اول نرم سازی حذف گروه های تكرار و آرايه ها از موجوديت يا جدول است . فرآيند فوق ، می بايست بر روی تمامی موجوديت های بانك اطلاعاتی اعمال گردد تا بتوان گفت بانك اطلاعاتی نرمال شده در فرم اول است . 

رابطه INF   ,   R است اگر تمام صفحات خاص آن اتوميك باشند يعني تجزيه پذير نباشند مثلا جدولي كه يك فيلد تاريخ دارد كه خود از سه فيلد كوچكتر (سال , ماه ,  روز) تشكيل مي شود  INF نيست

نكته

:در ACCSS تمام جداول INF  هستند .

 

فرم 2NF:

موجوديتی در فرم دوم نرمال است که اولا" در فرم اول نرمال باشد و ثانيا" تمامی آيتم های (Attribute) غير کليدی آن وابستگی تابعی به تمام کليد اصلی‌ موجوديت داشته باشند نه به بخشی از آن .همانگونه كه از تعريف فوق استنباط می گردد ، فرم دوم نرمال سازی در خصوص موجوديت هائی بررسی و اعمال می شود كه دارای كليد اصلی مركب هستند ( بيش از يك جزء ) . بنابراين در مثال فوق موجوديت "فاكتور فروش " به خودی خود در فرم دوم نرمال است ولی موجوديت "رديف های فاكتور فروش " كه دارای كليد اصلی مركب است ، نياز به بررسی دارد .

مشکل : در صورتی كه موجوديت در فرم دوم نرمال نباشد ، آنگاه با تغيير اطلاعات قسمت های غيروابسته به تمام كليد ، اين تغييرات در يك ركورد اعمال می شود ولی تاثيری بر روی ساير ركوردها و يا جداول نخواهد داشت . در مثال فوق با تغيير محتوی قيمت واحد در موجوديت "فاكتور فروش " ، قيمت واحد كالا در يك فاكتور فروش اصلاح می گردد اما در ساير فاكتورها اعمال نخواهد شد .

راه حل : برای حل اين مشکل کافی است موجوديت جديدی ايجاد نمائيم و کليد اصلی آن را برابر با آن بخش از کليد اصلی موجوديت مورد بررسی که دارای المان های وابسته به آن است قرار دهيم ، سپس تمام المان های اطلاعاتی وابسته تابعی به اين کليد را از موجوديت مورد بررسی خارج کرده و به موجوديت جديد منتقل نمائيم . در اين حالت بين موجوديت جديد ايجاد شده و موجوديت نرمال شده ، بر اساس کليد اصلی موجوديت جديد ايجاد شده يک ارتباط پدر فرزندی تعريف خواهد شد . دقت کنيد که بر عکس نرمال سازی فرم اول ، در اين جا موجوديت موردبررسی فرزند بوده و موجوديت جديد پدر خواهد بود .
 به مثال فوق برمی گرديم و فرم دوم نرمال سازی را بر روي آن اعمال می نمائيم . موجوديت "فاکتور فروش" دارای کليد مرکب نيست پس در فرم دوم نرمال بوده و نياز به بررسی ندارد ، اما موجوديت "رديف های فاکتور فروش"  نياز به بررسی دارد . در اين موجوديت آيتم اطلاعاتی "قيمت واحد" وابستگی تابعي به آيتم کالا دارد که بخشی از کليد است نه کل کليد ، پس لازم است تا اين موجوديت را تبديل به فرم دوم نرمال نمائيم . بدين منظور  موجوديتی به نام "کالا" ايجاد کرده ، کليد اصلی آن را برابر کالا قرار داده و آيتم قيمت واحد را از موجوديت رديف های فاکتور فروش خارج نموده و به اين موجوديت منتقل می نمائيم. مثال فوق پس از تبديل به فرم دوم نرمال به شکل ذيل خواهد بود :

 

 رابطه2 NF  ,   R است و اگر فقط INF باشد و هر صفت خاصي غير كليد يا كليد اصلي وابستگي تابعي كامل داشته باشد.

فرض كنيد جدولي به صورت زير داريم:

در اين جدول تركيب S#,P# كليد اصلي است فرض مي كنيم وضعيت يك تهيه كننده از روي شهر آن تعيين مي شود يعني statusبه cityوابسته است.

جدول 2NF,firstنيست زيرا city, statusفقط بهS#وابسته اند و تركيب S#,P# كه همان كليد اصلي  است وابستگي تابعي ندارد.

 

براي اينكه يك جدول كه 2NFنيست2NFشود آن را به جداول ديگري تجزيه مي كنيم جدول firstرا به دو جدول زير تجزيه مي كنيم

 

S#

status

city

status

 

در جدول Secondاگر فيلدstatusتغيير كند بايد فيلد city  نيز تغيير كند و بر عكس بنابرين عمل ويرايش در اين جدول آنومالي دارد

 

.

فرم :3NF

موجوديت و  يا جدولی در فرم سوم نرمال است که اولا" در فرم دوم نرمال بوده و ثانيا" تمام آيتم های غير کليد آن وابستگی تابعی به کليد اصلی داشته باشند ، نه به يک آيتم غير کليد .
مشکل : در صورتی كه موجوديتی در فرم سوم نرمال نباشد ، آنگاه با تغيير آيتم يا آيتم های اطلاعاتی غير وابسته به کليد اصلی در يک رکورد، تغييرات در ساير رکوردها اعمال نخواهد شد و دچار دوگانگی اطلاعات خواهيم شد (مثلا" يک مشتري با دو نام متفاوت) .
راه حل : کافی است آيتم های غير کليدی به هم وابسته را به موجوديت جديدی منتقل  و کليد اصلی موجوديت جديد را تعيين نمائيم ، آنگاه کليد اصلی موجوديت جديد را در موجوديت نرمال شده به عنوان يک کليد خارجی (Foreign Key) در نظر گرفت . در موجوديت "فاکتور فروش"  مثال فوق آيتم نام مشتری وابستگی تابعی به آيتم کد مشتری دارد که خود يک آيتم غير کليد است بنابر اين بايد نرمال سازی فرم سوم در خصوص آن اعمال شود . شکل ذيل نحوه انجام اين كار را نشان می دهد :

 

رابطه 3NF,Rاست اگر وفقط اگر همه صفات خاصه غير كليد اصلي:

 

 الف)متقابلا" به يكديگر وابسته نباشد.

  ب) با كليد اصلي رابطه Rوابستگي تابعي كامل داشته باشد.

 

 

 جدول  (3NF)   Second , نيست زيرا فيلدهاي  city, statusهر كدام به  S#وابستگي تابعي كامل دارد اما خودشان نيزبه هم وابسته اند

براي اينكه جدول 3NF secondشود آن را به دو جدول تجزيه مي كنيم به صورت زير

 

قضيه:heath

     رابطه Rكه داراي سه مجموعه صفت  A,B,Cاست را در نظر بگيريد اگر

 A       Bو A       C وابسته باشد آنگاه مي توانيم رابطه  Rرا به دو رابطه  R1(A,B) , R2(A,C)تجزيه كنيم.

قضيه ريسانن:

                                                                                                                                                           

                                                                                                  R1(A,B)

                                                                                                                                                    A           B               

                                                                                          R(A,B,C)

                                                                                                          R2(B,C)

قضيه ريسانن  جداول را 3NFمي كند.                                                                                                                         B           C

 

 

فرم :BCNF

فرم بويس کد دارای مفهوم جامع تری نسبت به فرم دوم و سوم نرمال است . در فرم دوم و سوم نرمال بحث بر سر وابستگی تابعی آيتم های غير کليدی به کليد اصلی است . اما در فرم بويس کد ، موجوديتی در فرم بويس کد نرمال است که اولا" در فرم اول نرمال بوده و ثانيا" تمام المان های غير کليدی آن کاملا" وابسته تابعی به يک کليد باشند و نه چيز ديگر . نکته حائز اهميت در اين فرم اين است که بحث بر سر وابستگي تابعی با يک کليد است نه فقط کليد اصلی. مفهوم فوق در خصوص موجوديت هائی که دارای چندين کليد هستند (Alternate Key) مطرح می شود . 

    تبديل كردن جداول به  3NFباعث افزونگي جداول مي شود كه باعث افت كارايي سيستم در عمل بازيابي اطلاعات مي شود زيرا در اين حالت بايد عمل پيوند داده ها به دفعات استفاده شود.

فرم  3NFدر موارد زير ممكن است مشكل بوجود آورد:

 

الف)وقتي رابطه داراي چند كليد كانديد باشد.

  ب)وقتي كه كليدهاي  كانديد رابطه مركب باشد.

 ج)وقتي كه كليد هاي كانديد بايكديگر اشتراك صفت داشته باشند.

الگوريتم تبديل از 3NF به BCNF:

3NF با جداولی که هر سه شرط زیر را دارند ممکن است مشکل داشته باشد.در این صورت باید این نوع جداول را تا سطح بیشتری نرمال سازی نمود.این سه شرط عبارتند از :

1.       جدول دارای حداقل دو کلید کاندید باشد.

2.       این کلیدهای کاندید ترکیبی باشند

3.       این کلیدهای کاندید ترکیبی،صفت های مشترکی داشته باشند.

مثال 1:جدول دانشجو در یک موسسه کوچک که دانشجوی همنام ندارد (در این صورت هم شماره دانشجویی کلید کاندید است و هم نام دانشجو):

Stud (s#,sname,address,avg)

Candidate key (S#)

Candidate key (sname

 

     

دترمينان :

هر صفت خاصه ايي كه صفت خاصه ديگر با آن وابستگي تابعي كامل داشته باشد دترمينان گفته

مي شود.

يعني  Aبه B وابستگي تابعي كامل دارد در اين صورت                                A           B      

به A  دترمينان مي گويند.

 

رابطه BCnf  Rاست اگر و فقط اگر هر دترمينان كليد كانديد باشد يعني تمام صفات خاصه به كليد كانديدها وابسته باشند .

كيلد اصلي خود يكي از كليد هاي كانديد است .

رابطه اي كه  3NFنباشد  BCNFنيز نخواهد بود و هر رابطه اي كه  BCNFباشد حتما"  3NFاست

اگر در يك جدول دو صفت خاصه كه كليد كانديد نباشد ولي وابستگي تابعي داشته باشند جدول BCNF  نخواهد بود .

 BCNFازتجزيه جداول جلوگيري مي كند .

 

فرم چهارم نرمال 4NF
اين فرم در خصوص موجوديت هائی است که ارتباط بين المان های آن يک ارتباط چند ارزشه و يا چند به چند باشد . به عنوان مثال ، موجوديت کلاس درس می تواند شامل چندين دانش آموز و چندين معلم باشد. در چنين مواردی ارتباط بين معلم و دانش آموز يک ارتباط چند به چند می باشد . در اين حالت با ايجاد يك موجوديت رابط  مابين موجوديت های مذكور، مشکل ارتباط چند به چند حل خواهد شد (بسياری از سيستم های مديريت بانک های  رابطه ای نظير MSSQL از رابطه چند به چند پشتيبانی نمی نمايند ، يعنی نمی توان بين دو جدول يک رابطه چند به چند ايجاد نمود). معمولا" تمام المان های موجوديت رابط ايجاد شده بخشی از كليد اصلی است .