طراحي پايگاه داده (Database Design) به زبان ساده

[Admin]

طراحي پايگاه داده (Database Design) به زبان ساده (قسمت اول)


پايگاه داده ( Database ) چيست ؟
پايگاه داده يك وسيله الكترونيكي جهت ذخيره داده ( Data) بصورت سازمان يافته است. پس از اينكه داده ها در پايگاه داده ذخيره شد اين داده ها قابل بازيابي ، پرادزش مي باشند و امكان توليد اطلاعات (Information ) از داده ها وجود دارد.

فايل هاي تخت (Flat File) در مقابل پايگاه‌هاي داده رابطه‌دار(Relational Database)
ابتدايي ترين سطح پايگاه داده ها فايل ها تخت هستند كه داده ها تنها در يك فايل ذخيره مي شوند.درنظر بگيرد كه يك صفحه اكسل را باز كرده ايد و اطلاعات زير را در آن وارد ساخته ايد. با اين كار شما يك ديتا بيس تخت ايجاد كرده ايد. ( شكل 1 )


به قسمت هاي خاكستري رنگ توجه كنيد داده هاي تكراري در فايل به چشم مي خورند. اين فايلهاي تخت دو مشكل اسا سي دارند :
1-تكرر داده ها
2-مشگلات نگهداري پايگاه داده

براي رفع اين مشكلات مي توانيد از پايگاه داده رابطه دار بهره بگيرد. اين نوع پايگاه داده‌ها اطلاعات را در جداول[1] مجزا نگهداري مي كنند. براي مثال مي توان اطلاعات فوق را در جداول مشتري ، سفارش و اقلام سفارش نگاه داشت و فيلدهاي هر جدول به صورت زير است : ( شكل 2)



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

تعيين نيازمنديهاي يك پايگاه داده
قبل از آنكه پايگاه داده طراحي شود بايد نيازمنديهاي آن را معين كنيم. براي تشخيص نيازهاي پايگاه داده يك سازمان رهنمودهاي زير را در نظر داشته باشيد :
رهنمود 1 : پايگاه داده هاي موجود را به دقت بررسي كنيد.
رهنمود 2 : با تك تك كاربران مصاحبه كنيد
رهنمود 3 : از فرمهاي شركت كپي تهيه كنيد
رهنمود 4 : به دقت گزارش هاي موجود را بررسي كرده اند نيازهاي گزارشي را نيز مد نظر قراردهيد.


تعيين اطلاعاتي كه بايد رديابي شود
پس از مصاحبه با افراد سازمان شما بايد اطلاعاتي كه بايد رديابي شود را مشخص كنيد.فرض كنيد در يك سازمان به يك ليستي مانند ليست زير دست پيدا كرديد ( شكل 3 )

تعيين طراحي منطقي پايگاه داده
پس از شناسايي نيازها شما بايد طرح اوليه و پيش نويس پايگاه داده ‌ها را تهيه كنيد. در واقع قبل از اينكه آنرا بصورت الكترونيكي ايجاد كنيد روي يك كاغذ نقشه اوليه آنرا مي كشيد به اين كار اصطلاحا طراحي منطقي پايگاه داده (Logical Database Design) مي گويند.

تعريف جدول ها ( موجوديت ها[2] ) و فيلدها ( ويژگيها[3] )

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

فيلدها كه ويژگي نيز ناميده مي شوند در واقع عنصر اسا سي جدولها هستند. مانند فيلد نام مشتري يا آدرس مشتري در جدول مشتري .
هويت دادن به جدول ها و فيلدها
پس از تعيين جداول و فيلدها بايد به آنها هويت بخشيد . نمونه اي از آنها را مشخص نمود ونوع هر فيلد را تعيين كرد. براي اين كار مي توانيد از ليستي كه قبلا تهيه كرديد (شكل 3 ) كمك بگيرد. براي مثال در شكل 5 نمونه اي از اين جدول آمده است:

براي مثال در جدول كالا شرح كالاي 12345 ، توفاTofa) ( مي باشد. اين فيلد از نوع داده متني مي باشد و تخمين ما اين است كه اين شرح حداكثر 25 كاراكتر خواهد بود.

يا در جدول مشتري نام مشتري 6 12345 ، جان اي.دويي مي باشد. اين فيلد از نوع داده متني مي باشد و تخمين ما اين است كه اين شرح حداكثر 45 كاراكتر خواهد بود.

و سرانجام فيلدهاي جدول سفارش مانند شكل فوق خواهد بود.

تعيين كليد اصلي (Primary Key)
كليد اصلي يك فيلد يا مجموعه اي از فيلدها است كه يك ركورد(Record) در جدول را يكتا (Unique) مي كنند. براي مثال در جدول مشتري شماره مشتري يك كليد اصلي مي باشد. هر مشتري يك شماره دارد و هر مشتري نشان دهنده يك هويت است
يك در جدول سفارش ، شماره سفارش كليد اصلي ميباشد. هيچ دو سفارشي يك شماره مشابه نخواهند داشت.

تعيين كليد خارجي يا كليد مرتبط كننده ( Foreign Key)
كليد خارجي يك فيلد يا مجموعه اي از فيلدها است كه يك جدول را به كليد اصلي جدول ديگر مرتبط مي سازد بنابراين اين دو جدول مرتبط مي شوند. براي مثال شماره مشتري و شماره كالا كليدهاي اصلي جداول مشتري وكالا مي باشند كه به عنوان كليد خارجي در جدول سفارش عمل مي كنند. به شكل 6 دقت نماييد.

[1] table

[2] Entity

[3] Attribute


منبع: http://www.systemgroup.net

[Admin]

طراحي پايگاه داده (Database Design) به زبان ساده (قسمت دوم)

تعريف روابط در پايگاه داده

به مثال مشتري ، كالا و سفارش كالا برميگرديم .ميتوان گزاره‌‌‌ هاي زير را بيان كرد :

يك سفارش مي تواند شامل چند كالا باشد

يك سفارش تنها به يك مشتري مربوط است

هر مشتري مي تواند چندين سفارش داشته باشد

به زبان پايگاه داده ، اين گزاره ها روابط ميان جدولها را بيان مي كنند. اين روابط را مي توان در نمودار موجوديت ها (ERD) نمايش داد ( شكل 7)

روابط ميان موجوديت ها مي توان به 3 صورت باشد :

1-يك به يك (One to One )

2-يك به چند (One to Many )

3-چند به چند (Many to Many)

در ادامه به تشريح هريك از اين روابط مي پردازيم

رابطه يك به يك

رابطه يك به يك مشابه تناظر يك به يك در رياضيات است . دراين نوع از رابطه، هر ركورد از جدول اول تنها و تنها با يك ركورد در جدول دوم رابطه دارد. براي مثال فرض كنيد اطلاعات هر مشتري 100 فيلد دارد و پايگاه داده ما اين تعداد فيلد را پيشتيباني نمي كند بنابراين بخشي از اطلاعات مشتري را در جدولي بنام CustomerDetial قرار مي دهيم .اين جدول با جدول Customer رابطه يك به يك دارد. فيلدي كه اين دو جدول را بهم مرتبط مي سازد فيلد Customernumber مي باشد. (شكل 8 )

به اعداد 1 و1 روي خط مرتبط كننده دقت كنيد. اين نشان دهنده رابطه يك به يك است.

رابطه يك به چند

دراين نوع از رابطه هر ركورد از جدول اول مي تواند با چند ركورد در جدول دوم رابطه داشته باشد. مثال مشتري و سفارش نمونه‌‌اي مناسب براي اين رابطه است. هر مشتري مي تواند چندين سفارش داشته باشد يعني هر ركورد ( مشتري) از جدول Customer مي تواند با چند ركورد ( سفارش) از جدول Order مرتبط باشد (شكل 9 )

به عدد 1 و علامت بي نهايت روي خط مرتبط كننده دقت كنيد اين نشان دهنده رابطه يك به چند است و اينچنين خوانده مي شود : هر ركورد از جدول مشتري ميتوان با چند ركورد از جدول سفارش مرتبط باشد.

رابطه چند به چند

اينجاست كه دنيا آشفته و پيچيده مي شود. دراين نوع از رابطه هر ركورد از جدول اول مي تواند با چند ركورد در جدول دوم رابطه داشته باشد و بالعكس.

اما چگونه مي توان اين پيچيدگي و آشفتگي را حل كرد ؟

با استفاده از يك جدول سوم يا جدول واسط مي توان اين مشكل را برطرف نمود.

فرض كنيد كه يك سيستم كاربران (User) زيادي دارد و هر كاربر مي تواند نقش هاي (Role) متفاوتي داشته باشد به عبارت ديگر :

هر كاربر مي تواند چند نقش داشته باشد

هر نقش مي تواند به چند كاربر مربوط باشد

براي ارتباط برقرار كردن ميان دو جدول كابران (Users) و نقش ها (Roles) جدول واسطي ميان اين دو جدول بنام نقش كاربران(Userroles) ايجاد مي كنيم. اكنون هر دو جدول كاربران و نقش ها با اين جدول رابطه يك به چند دارند. بنابراين يك رابطه چند به چند را به دو رابطه يك به چند تبديل نموديم : (شكل 10 )

به زبان ديگر :

Many to Many = One to Many + One to Many

به اطلاعات اين جداول دقت نماييد :

يكپارچگي ارجاع (Referential Integrity)

با ايجاد رابطه ميان جدوال از مزيتي بنام يكپارچگي ارجاعي استفاده مي كنيم . مزيت يكپارچگي بطور خلاصه به موارد زير اشاره دارد :

1- به روز رساني آبشاري : يعني هر گاه فيلد كليدي در يگ جدول تغيير كرد اين تغيير بايد در جدوال ديگر كه از طريق اين فيلد با جدول مربوطه در ارتباطند نيز اعمال گردد.

2- حذف آبشاري : يعني هر گاه قرار است فيلد كليدي از يگ جدول حذف شود بايد اطمينان حاصل شود كه در تمام جدوال مرتبط حذف صورت پذيرفته است.

توجه داشته باشيد كه يكپارچگي ارجاعي اگرچه معمولا يك مزيت بشمار مي رود ولي گاهي اوقات مساله ساز نيز مي شود.

نرمال سازي (Normalization)

ساختن يك پايگاه داده مانند ساختن يك خانه است. آيا براي ساختن يك خانه دانستن تعداد اتاق هاي مورد نياز كافي است و يا اينكه بايد به پارامترهاي ديگر نيز توجه داشت تا بتوان نقشه‌اي بهينه براي خانه تهيه كرد. نرمال سازي در واقع فرايند تهيه طرح بهينه براي يك پايگاه داده است.نرمال سازي مراحلي دارد و بايد به يك قانون توجه داشت :

هرچه در مرحله بالاتري از نرمال سازي باشيد پايگاه داده شما طرح و ساختاري كاراتر خواهد داشت.

نرمال سازي سطح اول(First normal form)

در نرمال سازي سطح اول گروه هاي تكراري را حذف نماييد.

اين گروههاي تكراري ميتوانند دو حالت را در بر مي گيرند :

1-در يك فيلد داده هاي زياد و طولاني قرار مي گيرد.

2-دو فيلد مشابه وجود دارد.

براي مثال جدول مشتري را در نظر بگيرد ) شكل 11 )

در اين جدول به فيلد آدرس مشتري Customer Address توجه كنيد. اطلاعات زيادي در اين فيلد قرار مي گيرند و عملا قابليت بازيابي اطلاعات كاهش مي يابد. اين فيلد را مي توان به پنج فيلد آدرس مشتري ، شهر مشتري ، استان مشتري ، كد پستي و كشور مشتري تقسيم نمود. (شكل 12 )

اكنون به جدول سفارشات توجه نماييد ( شكل 13 ) . در اين جدول فيلدهاي تكراري مانند شماره ، قيمت و مقدار كالا وجود دارد.

براي نرمال كردن اين جدول فيلدي تحت عنوان شماره سفارش به جدول اضافه مي كنيم و فيلدهاي اضافه را حذف مي كنيم :

اين اطلاعات در جدول بصورت زير خواهند بود .

[Admin]

طراحي پايگاه داده (Database Design) به زبان ساده ( قسمت سوم )

نرمال‌سازي سطح دوم (Second Normal Form)

در نرمال‌سازي سطح دوم بايد اطمينان حاصل شود كه فيلد‌هاي غير كليدي، وابسته به فيلد(هاي) كليدي هستند.

به اطلاعات جدول زير در مرحله قبل كه در سطح اول نرمال‌سازي قرار گرفت، توجه كنيد. دراين جدول همان گونه كه مشاهده مي‌شود فيلدهاي Order number وProduct identifier مي‌توانند ركورد را يكتا كنند، يعني تركيب آنها كليد اصلي به شمار مي‌رود. اما به سه ركورد اول دقت کنيد ، شماره مشتري يعني فيلد Customernumber در اين ركوردها يكسان است . يعني شماره مشتري با شماره سفارش تغيير مي كند نه با شماره كالا. بنابراين باز اطلاعات تكراري داريم كه مي‌تواند در نگهداري پايگاه داده ايجاد مشكل كند.

چاره اين مشكل در نرمال سازي سطح دوم است . در اين سطح جدولي به نام در اين سطح جدولي به نام Order Product ايجاد مي‌كنيم و اطلاعات كالا را به اين جدول مي بريم:

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

نرمال‌ سازي سطح سوم (Third Normal Form)

در نرمال سازي سطح سوم بايد اطمينان حاصل شود كه هيچ فيلدهاي غير كليدي وابسته به فيلد(هاي) غيركليدي نيست .

فرض كنيد در جدول سفارش‌هایي كه در مرحله دوم نرمال شد، فيلد نام خانوادگي مشتري Customer lastname را اضافه نماييم چه اتفاقي خواهد افتاد؟

در اين حالت فيلد نام خانوادگي مشتري تنها به شماره مشتري كه كليد اصلي نيست، وابسته است . بنابراين اولا مشكل به روزرساني خواهيم داشت يعني اگر نام مشتري در جدول مشتري تغيير كند بايد در جدول سفارش‌ها نيز نام را تغيير دهيم و ثانيا مشكل فزوني داده در جدول سفارش را خواهيم داشت. براي بر طرف كردن اين مشكل اين فيلد را از جدول سفارش حذف و به جدول مناسب (مشتري)اضافه مي كنيم. اين نمونه‌اي از نرمال سازي سطح سوم است.

نرمال سازي Boyce/Codd

اين نوع از نرمال سازي كه از آن به عنوان نوعي از نرمال سازي سطح سوم نيز ياد مي‌شود، در حالت‌هاي زير به كار مي‌رود:

1- بايد دو كانديدا (يا بيشتر) براي فيلد كليدي موجود باشد.

2- حداقل دو تا از اين كانديداها بايد مركب باشند.

3- كليدهاي كانديدا بايد ويژگي‌هاي مشترك داشته باشند.

ساده ترين راه براي درك نرمال سازي بويس كد، استفاده از روابط كاركردي Functional Dependency است، به مثال زير دقت کنيد:

در جدول فوق دو كليد كانديدا وجود دارد كه هر دو هم مركب هستند:

· {SupplierID, ProductID}

· {SupplierName, ProductID}

اكنون مي‌توان مشاهده نمود كه رابطه كاركردي بين فيلدهاي {SupplierID} {SupplierName وجود دارد. به نمودار زير دقت کنيد:

بنابراين مدل صحيح جدول فوق با انجام نرمال سازي بويس – كد به صورت زير خواهد بود:

نرمال سازي سطح چهارم (Fourth Normal Form)

اين سطح از نرمال سازي بر پايه اين تئوري قرار دارد:

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

به جدول زير توجه كنيد:

در جدول فوق هر كالا مي تواند شيوه هاي بسته بندي مختلف داشته باشد كه در فيلد Pack Size آمده است. اين حالت با تئوري فوق تناقض دارد. شكل نرمال شده آن به صورت زير است:

نرمال سازي سطح پنجم (Fifth Normal Form)

نرمال سازي در اين سطح وابستگي های الحاقي (Join Dependencies) را نشانه مي رود. در اين نوع وابستگي موجوديت اول به موجوديت دوم وابسته است، موجوديت دوم به موجوديت سوم وابسته است و موجوديت سوم به موجوديت اول وابسته است، بنابراين يك محدوديت چرخشي ايجاد مي شود.

جدول زير را در نظر بگيرد:

به گزاره ‌هاي زير دقت کنيد:

تامين كننده كالا را تامين مي كند

مشتري كالا را سفارش مي دهد

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

در جهان واقعي اين قياس نامعتبر است زيرا تامين كننده ممكن است چيزي غير از كالا براي مشتري تامين كند. اما وابستگي الحاقي زماني بوجود مي آيد كه شرطي اضافي براي معتبر بودن اين قياس وجود داشته باشد.
اكنون اگر فرض كنيد قياس بالا معتبر مي باشد با در نظر گرفتن جدول بالا مشكلاتي در به روزرساني جدول ايجاد مي شود. براي مثال براي اضافه كردن ركورد {"Ma Maison", "Aniseed Syrup", "Berglunds snabbköp"{بايد ركورد {"Exotic Liquids", "Aniseed Syrup", "Berglunds snabbköp"}, را اضافه كرد. براي بر طرف كردن اين مشكل و دستيابي سطح پنجم نرمال بايد سه رابطه (SupplierProduct, ProductCustomer, and SupplierCustomer) را ايجاد کرد.

در چه مواقعي بايد نرمال سازي را انجام داد

در پاره اي از اوقات اهداف كسب وكار براي بهينه بودن عملكرد پايگاه داده غلبه مي كند و لازم است از انجام نرمال سازي سطح سوم چشم پوشي كنيم و به اصطلاح داده‌ها را " غير نرمال " (Denormalize) کنيم. دو حالت كلي براي پرهيز از نرمال سازي وجود دارد:
1- زماني كه شما با اضافه كردن فيلدي در يك جدول مي توانيد به طور محسوس زمان جستجوی اطلاعات از جداول زياد را كاهش دهيد.
2- زماني كه استفاده از يك فيلد محاسباتي در جدول زمان اجرا، پرس و جو و نمايش گزارش را به شدت افزايش مي دهد و اين فيلد بسيار استفاده مي شود.

روابط يگانه (Unary)و سه گانه (Ternary)

روابطي كه تاكنون بحث شد روابط دوگانه (Binary) است كه در آن موجوديت ها دوبه دو با هم رابطه دارند. اما روابط مي تواند حالتهاي ديگري نيز داشته باشد. يكي از اين حالتها رابطه يگانه است. مثال كلاسيك اين حالت رابطه مدير و كارمند است. وقتي يك مدير، مدير مافوق دارد بنابراين در جدول كارمندان در واقع اسم اين فرد درتعدادي از ركوردها به عنوان مدير تعدادي از كارمندان ذكر مي شود و در ركورد خود اين مدير (مدير نيز يك كارمند است) نام مديري ديگري به عنوان مدير او در فيلد مورد نظر پر مي شود. به شكل دقت کنيد:

نوع ديگري از روابط رابطه سه گانه است. براي درك اين رابطه به مثال زير دقت کنيد. كالاي Mozzarella di Giovanni بوسیله يك مشتري به نام Vins et alcools Chevalier خريداري شده است و اين كالا به وسیله دو توليد كننده به نامهاي Formaggi Fortini s.r.l. وForêts d'érables توليد مي شود. به جداول طراحي شده توجه کنيد:

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

براي حل اين مساله كالا را از زنجيره روابط حذف كنيد و روابط را مانند دياگرام زير طراحي کنيد:

طراحي شاخص ها (Index)

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

اغلب پايگاه داده ها امكان تعريف انواع شاخص ها اعم از يكتا و غير يكتا، لايه اي (Clustered)و غير لايه اي(Non-Clustered) را به كاربر مي دهند.

شاخص هاي يكتا، شاخص هايي هستند كه ركورد تكراري ندارند.

شاخص هاي غيريكتا، شاخص هايي هستند كه ركوردهاي تكراري را نيز دربرمي گيرند.

شاخص‌هاي لايه اي، شاخص هايي است كه كه داده ها بطور فيزيكي به همان ترتيبي كه در شاخص هستند در جدول ذخيره شده اند.

شاخص‌هاي غيرلايه اي، شاخص هايي است كه ابتدا پايگاه داده محل آنها را شناسايي كرده و سپس به محل مربوطه در جدول جهت بازيابي اطلاعات رجوع مي كند.

آزمون طرح منطقي پايگاه داده

تا به اينجا نقشه راهي براي طراحي منطقي پايگاه داده معرفي شد اما لازم است قبل از طراحي فيزيكي آن طرح مورد آزمون قرار گيرد. حتما مي پرسيد چگونه مي توان يك طرح روي كاغذ را آزمایش كرد ؟ اين كار چندان مشكل نيست . كافي است مثلا در مثال مشتري و سفارش كالا بطور فرضي يك مشتري تعريف كنيد، چند كالا معرفي نماييد و سفارش ايجاد كنيد و... . دربسياري از اوقات با همين روش مي توانيد بسياري از فيلدهايي را كه از قلم افتاده اند را به طرح اضافه کنيد.

[الهام خانوم]

باتشکر از مدیریت کل سایت. کاش عکسها رو جدید آپلود میکردین تا دیده بشن.حیفه مطلب به این خوبی عکسهاش خراب شده.

[Admin]

متاسفانه تصاویر از روی سایت منبعش حذف شده ...

موفق باشید

[بانوثریا]

سلام ممنون از مطالبتون
اگه میشه در مورد بهینه سازی توضیح بدید و اگه بازم امانش هست یه نمونه بهینه سازی انجام بدید با تشکر

[hamed_arya7]

[hamed_arya7]

سلام ممنون از مطالبتون
اگه میشه در مورد بهینه سازی توضیح بدید و اگه بازم امانش هست یه نمونه بهینه سازی انجام بدید با تشکر