اصول طراحي پايگاه داده‌ها

1. اصول طراحي پايگاه داده‌ها • طراحي پايگاه داده‌هاي رابطه‌اي مهدی ایل بیگی دانشگاه پیام نور دماوند

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

3. مراحل اساسي طراحي پايگاه داده‌ها خرد جهان ولقع تشخيص نيازها و تحليل آنها نيازهاي عملياتي نيازهاي پايگاهي تحليل عملكردي مدلسازي معنايي داده‌ها مشخص كردن تراكنشها در سطح بالا نمودار ER ناوابسته به DBMS طراحي منطقي وابسته به DBMS طراحي برنامه‌هاي كاربردي و واسط هاي كاربري شماي ادراكي (منطقي) و شماي خارجي طراحي فيزيكي پياده‌سازي تراكنش ها شماي داخلي برنامه‌هاي كاربردي

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

5. ویژگی های طراحی مناسب 1- نمايش واضحي از خردجهان واقع باشد. 2- نمايش صحيحي از خردجهان واقع باشد (بگونه ای که هیچ اشتباه معنایی در پاسخگویی به پرسش های کاربران بروز نکند). 3- نمايش جامعي از خردجهان واقع باشد. (در برگیرنده تمامی صفات مورد نیاز کاربران) 4- تمام قواعد جامعيتي كه قابل اعمال در هر مرحله از طراحي منطقي باشند، در طراحي منظور شده باشند. 5- معناي هريك از صفات از هر نوع موجوديت به درستي رعايت شده باشد. 6- كمترين ميزان افزونگي را داشته باشد.

6. ویژگی های طراحی مناسب 7- كمترين ميزان اختلاط اطلاعات را داشته باشد. (روابط در سطح بالایی نرمال سازی بشوند) 8- انعطاف‌پذيري داشته باشد. (بتوان تغییرات نیازهای جدید کاربران را منظور کرد) 9- كمترين دشواري در انجام عمليات ذخيره‌سازي را داشته باشد. 10- كمترين ميزان هيچمقدار در پایگاه داده ایجاد شود. 11- هيچ اطلاع جعلي در اثر انجام عمليات در پايگاه داده‌ها پديد نيايد. (بعنوان مثال رابطه حاصل از پیوند دو جدول روی یک صفت غیر کلید ممکن است سطرهایی جدید و حشو را ایجاد نماید) 12- با درنظر گرفتن طراحي فيزيكي و تاثير آن در طراحي منطقي، بيشترين كارايي براي سيستم كاربردي پايگاه داده‌ها تامين شود.

7. روش های تبديل نمودار ER به رابطه‌ها • حالت اول: • تعداد نوع موجوديت: n >= 2 • وضع موجوديت ها: مستقل • چندي ارتباط: N:M • در اين حالت، n+1 رابطه لازم است. n رابطه براي n موجوديت مستقل و يك رابطه براي نمايش ارتباط بين آن ها. • اگر ارتباط بين انواع موجوديت ها، صفت ساده يا مركب چندمقداري نداشته باشد، در اين صورت كليد كانديد رابطه نمايشگر ارتباط بين n موجوديت، از تركيب كليدهاي كانديد n رابطه نمايشگر n موجوديت به دست مي‌آيد. پس كليدهاي خارجي، از اجزاء تشكيل‌دهنده كليد كانديد رابطه نمايش دهنده ارتباط هستند.

8. روش های تبديل نمودار ER به رابطه‌ها • مثال برای حالت اول: • در این مثال برای هر یک از موجودیت های S, P, J یک رابطه (جدول) تعریف می نماییم. • برای رابطه S-P-J نیز یک رابطه در نظر می گیریم. کلید این رابطه ترکیب کلیدهای کاندید در رابطه های S, P, J می باشد.

9. روش های تبديل نمودار ER به رابطه‌ها • حالت دوم: • تعداد نوع موجوديت: دو • وضع موجوديت ها: مستقل • چندي ارتباط: 1:N • در اين حالت، دو رابطه كفايت مي‌كند: يك رابطه براي نمايش نوع موجوديت طرف يك، و يك رابطه براي نمايش نوع موجوديت طرف N و نيز ارتباط1:N در رابطه اخير. كليد كانديد رابطه اول، به عنوان كليد خارجي در رابطه دوم، ارتباط مورد نظر را نشان مي‌دهد و جزء تشكيل‌دهنده كليد كانديد رابطه نيست. • در مثال فوق باید دو رابطه بصورت زیر تعریف نماییم: DEPT(D#, …) EMPL(E#, …, D#, F) → Foreign-Key: D# • نکته: در رابطه کارمند D# کلید خارجی است و جزء کلید اصلی کارمند نیز نمی باشد. البته بدلیل اینکه مشارکت در ارتباط الزامی است، D# در رابطه کارمند نمی تواند NULL باشد.

10. روش های تبديل نمودار ER به رابطه‌ها • حالت سوم • تعداد نوع موجوديت: دو • وضع موجوديت ها: مستقل • چندي ارتباط: 1:1 • در اين حالت، دو رابطه لازم است: يك رابطه براي نمايش يكي از دو نوع موجوديت و رابطه‌اي ديگر براي نمايش نوع موجوديت ديگر و ارتباط بين دو نوع موجوديت. • در مثال فوق باید دو رابطه زیر را تعریف کنیم و رابطه سمت با مشارکت الزامی کلید کاندید رابطه دوم را بعنوان کلید خارجی دارا خواهد شد: Book(B#, …) Course(C#, …, B#) → Foreign-Key: B# • نکته: اگر مشارکت طرفین غیر الزامی بود مثال فوق با سه جدول زیر طراحی می شود: B(B#, …), C(C#, …), BC(B#, C#)

11. روش های تبديل نمودار ER به رابطه‌ها • حالت چهارم • تعداد نوع موجوديت: يك • وضع موجوديت ها: مستقل • چندي ارتباط: N:M • در اين حالت، ارتباط با خود داريم. دو رابطه لازم است: يك رابطه براي نمايش خود نوع موجوديت و ديگري براي نمايش ارتباط، اعم از اينكه مشاركت الزامي باشد يا نباشد. • در مثال فوق باید دو رابطه زیر را تعریف نماییم: Part(P#, …) Made(P#, M#, Count)

12. روش های تبديل نمودار ER به رابطه‌ها • حالت پنجم • تعداد نوع موجوديت: يك • وضع موجوديت ها: مستقل • چندي ارتباط: 1:N • در اين حالت، هم ”ارتباط با خود“ داريم، ولی يك رابطه كفايت مي‌كند. • برای مثال فوق باید جدول زیر را تعریف نماییم: EMP(E#, …, EManager#) → Foreign-Key: EManager#

13. روش های تبديل نمودار ER به رابطه‌ها • حالت ششم • تعداد نوع موجوديت: يك • وضع موجوديت ها: مستقل • چندي ارتباط: 1:1 • در اين حالت يك رابطه كفايت مي‌كند، به شرط آنكه مشاركت در ارتباط الزامي باشد. البته مي‌توان با دو رابطه هم طراحي كرد. • برای مثال فوق باید جدول زیر را تعریف نماییم: STJST(ST#, …, JST#) • نکته: در این مثال JST# شماره دانشجوی همکار در پروژه است. برای پروژه یک نفره می تواند NULL باشد.

14. روش های تبديل نمودار ER به رابطه‌ها • حالت هفتم • نمايش موجوديت ضعيف • موجوديت ضعيف داراي شناسه يكتا نيست، بلكه صفت مميزه دارد. براي نمايش اين نوع موجوديت در طراحي پايگاه رابطه‌اي، يك رابطه طراحي مي‌كنيم كه در عنوان آن، صفات موجوديت ضعيف و كليد كانديد موجوديت قوي كه با آن ارتباط دارد (موجوديت شناسا)، وجود دارند. • در مثال مقابل یک رابطه برای موجودیت ضعیف و ارتباط آن با موجودیت شناسا، در نظر می گیریم. پس خواهیم داشت: EMP(E#, …) EEDU(E#, Title, …) → Foreign-Key: E# • نکته: کلید کاندید رابطه قوی جزعی از کلید رابطه ضعیف خواهد بود و همچنین به عنوان کلید خارجی عمل خواهد کرد.

15. روش های تبديل نمودار ER به رابطه‌ها • حالت هشتم • وجود صفت چندمقداري • اگر MVA، يك صفت (ساده يا مركب) چندمقداري، EID شناسه موجوديت E و A1, A2, …, Aiساير صفات تك‌مقداري موجوديت E باشند، در اين صورت براي نمايش اين نوع موجوديت، دو رابطه لازم است: R1(EID, A1, A2, …, Ai) R2(EID, MVA) • در مثال مقابل باید جداول زیر را تعریف کنیم: EMP(E#, …) ETEL(E#, Phone#) EDHIS(E#, Title, From, To, …) • نکته: در صورتی که یک موجودیت m صفت چندمقداری داشته باشد، برای نمایش این موجودیت به m+1 رابطه نیاز داریم.

16. روش های تبديل نمودار ER به رابطه‌ها • حالت نهم • ارتباط IS-A يا ”گونه‌اي است از ...“ • در اين حالت موجوديت سطح بالاتر با يك رابطه نشان داده مي‌شود و هر نوع موجوديت سطح پايين‌تر با صفات خاص خودش همراه با شناسه اصلي موجوديت سطح بالاتر، در رابطه ديگري نمايش داده خواهد ‌شد. • در مثال روبرو جداول زیر باید تعریف گردد: E(EID, A, B, …) E1(EID, C, D, …) E2(EID, F, …) … En(EID, M, N, P) • U

17. روش های تبديل نمودار ER به رابطه‌ها • حالت دهم • وجود دسته (طبقه) در ارتباط IS-A • در اين ارتباط وقتي زيرنوع دسته (طبقه) داريم و صفات شناسه زبرنوع ها، متفاوت باشند، يك طرز طراحي اين است كه اين زيرنوع را با يك رابطه نشان دهيم. كليد اين رابطه، يك كليد جايگزين (شناسه ساختگي) است و بعلاوه يك صفت ديگر که در آن درنظر مي‌گيريم برای نشان‌دادن نوع آن (از نوع كدام زبرنوع است). هر زبرنوع را هم با يك رابطه نشان مي‌دهيم و صفت كليد زيرنوع را به هر يك از رابطه‌هاي نشان‌دهنده زبرنوع ها اضافه مي‌كنيم.

18. روش های تبديل نمودار ER به رابطه‌ها E2ID E1ID • مدل کلی حالت دهم: • برای نمودار روبرو رابطه های زیر را ایجاد می نماییم: • نکته: اگر کلید E1 و E2 یکسان باشند، کلید رابطه E3 همان کلید E1 و E2 خواهد بود و دیگر نیازی به صفت E3Type نیز نمی باشد. • . • . • . • . • . • . E1 E2 E1(E1ID, …, E3ID) E2(E2ID, …, E3ID) E3(E3ID, E3Type) U E3

19. روش های تبديل نمودار ER به رابطه‌ها • حالت يازدهم • ارتباط IS – A PART – OF يا ”جزئي است از ...“ • در اين حالت، براي موجوديت كل (شامل)، يك رابطه و براي هر يك از موجوديت هاي جزء (مشمول) نيز يك رابطه طراحي مي‌شود. در رابطه نشان‌دهنده موجوديت جزء، كليد كانديد رابطه نشان‌دهنده موجوديت كل، بعنوان کلید خارجی و جزعی از کلید اصلی رابطه جزء آورده مي‌شود و بقيه صفات اين رابطه، خاص خود موجوديت جزء هستند. • برای نمودار مقابل بصورت زیر باید رابطه ها را تعریف کنیم: E (EID, …) E1(E1ID, EID, …) E2(E2ID, EID, …) …

20. روش های تبديل نمودار ER به رابطه‌ها • حالت دوازدهم • بيش از يك ارتباط بين دو نوع موجوديت • فرض مي‌كنيم هر دو نوع موجوديت مستقل هستند. تعداد رابطه‌ها، بستگي به چندي هر ارتباط دارد. خواهیم داشت: • هر ارتباط با چندي N:M با يك رابطه نمايش داده مي‌شود. • نوع موجوديت سوي N و همه ارتباط هاي با چندي 1:N، اگر مشاركت دو نوع موجوديت در همه ارتباط ها الزامي باشد، را مي‌توان با يك رابطه نمايش داد. • اگر مشاركت دو نوع موجوديت در يك ارتباط 1:N، الزامي نباشد، بهتر است براي چنين ارتباطي يك رابطه جداگانه طراحي شود كه صفات آن، شناسه دو نوع موجوديت و در صورت وجود، صفات خود ارتباط هستند. • در صورت وجود بيش از يك ارتباط 1:1 و الزامي بودن مشاركت ها، هر دو نوع موجوديت و همه اين ارتباط ها را مي‌توان با يك رابطه نمايش داد. • اگر مشاركت در يك ارتباط 1:1 الزامي نباشد، بهتر است اين ارتباط را با يك رابطه جداگانه نمايش داد.

21. روش های تبديل نمودار ER به رابطه‌ها • مثال برای حالت دوازدهم: • برای نمودار روبرو نیاز به چهار جدول داریم. دو تا برای دو موجودیت و ارتباط های 1:N و 1:1 و دو تا برای ارتباط های M:N. • این جداول در شکل زیر آورده شده است:

22. روش های تبديل نمودار ER به رابطه‌ها • حالت سيزدهم • ارتباط سلسله‌مراتبي (پدر- فرزندي) • در تبديل اين نوع ارتباط بين موجوديت ها به طراحي رابطه‌اي، يك روش چنين است: 1- براي نوع موجوديت ريشه، يك رابطه در نظر مي‌گيريم. 2- كليد كانديد اين رابطه همان شناسه ريشه است. 3- براي هر نوع موجوديت فرزند يك رابطه در نظر مي‌گيريم و كليد كانديد هر نوع موجوديت فرزند، تركيب شناسه آن نوع موجوديت با كليد كانديد نوع موجوديت پدرش است.

23. روش های تبديل نمودار ER به رابطه‌ها • مثال برای حالت سيزدهم: در نمودار مقابل برای هر موجودیت یک رابطه درنظر می گیریم بطوری که کلید اصلی هر رابطه، ترکیبی از کلید اصلی پدران آن رابطه با کلید خود رابطه می باشد: E1(E1ID, …) E2(E1ID, E2ID, …) E3(E1ID, E2ID, E3ID, …) …

24. روش های تبديل نمودار ER به رابطه‌ها • حالت چهاردهم • تجمع (ارتباط با ارتباط) • در اين حالت، ابتدا نمودار ER جزئي درون مستطيل نشان‌دهنده نوع موجوديت حاصل از عمل تجميع را به رابطه‌هاي لازم تبديل مي‌كنيم. سپس با توجه به چندي ارتباط بين نوع موجوديت مرتبط با ارتباط و چگونگي مشاركت در ارتباط با ارتباط، طراحي را كامل مي‌كنيم.

25. روش های تبديل نمودار ER به رابطه‌ها • مثال برای حالت چهاردهم: • در نمودار روبرو ابتدا باید رابطه های داخل مستطیل را طراحی کنیم و سپس به سراغ طراحی روابط خارج مستطیل می رویم. گلید اصلی رابطه ارتباط با ارتباط، کلید اصلی ارتباط درون مستطیل را به همراه کلید خود رابطه دارا خواهد بود. در نتیجه خواهیم داشت:

26. ؟