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

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

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

3. مثال برای آنومالی در عملیات ذخیره سازی • رابطه First در سطح اول نرمال است، یعنی تمامی صفات تک مقداری هستند، ولی در عملیات ذخیره سازی دارای آنومالی است: • آنومالی در درج: اگر بخواهیم اطلاعات تهیه کننده را درج کنیم بدلیل نداشتن مقدار P# که جزئی از کلید است، نمی توانیم درج را انجام دهیم. پس درج <S5, C5, 20> ممکن نیست. • آنومالی در حذف: اگر بخواهیم اطلاعاتی مثل تولید یک قطعه توسط یک تهیه کننده را حذف کنیم، حذف صورت می گیرد ولی اطلاعات مهم دیگری را نیز بطور ناخواسته از دست می دهیم. مثلا با حذف <S3, P1, 70> اطلاعات شهر و وضعیت تولید کننده S3 را از دست می دهیم. • آنومالی در بهنگام سازی: مثالا اگر بخواهیم شهر S1 را تغییر بدهیم باید در سه سطر فیلد شهر را برای S1 تغییر دهیم و این فزونکاری برای انجام یک عملیات می باشد.

4. صورت های نرمال • صورت نخست نرمال (1NF) • صورت دوم نرمال (2NF) • صورت سوم نرمال (3NF) • صورت نرمال بايس-كاد (BCNF) • صورت چهارم نرمال (4NF) • صورت پنجم نرمال (5NF/PJNF) • صورت نرمال ميدان-كليدي (DKNF) • صورت نرمال تحديد-اجتماع (RUNF) 6

5. وابستگی تابعی (Functional Dependency) • تعريف- فرض كنيد كه R يك متغير رابطه‌اي و A و B دو زيرمجموعه دلخواه از عنوان (صفت ها) R باشند. مي‌گوييم B با A وابستگي تابعي دارد و چنين نمايش مي‌دهيم: A → B اگر و فقط اگر در هر ميدان ممكن از متغير رابطه‌اي R، به هر مقدار A فقط يك مقدار B متناظر باشد. یعنی A، B را تعیین می کند و می خوانیم B به A وابستگی تابعی دارد. • مثال: در رابطه روبرو وابستگی های تابعی را بیابید. آیا A → B: بله زیرا به ازائ هر مقدار از A دقیقا یک مقدار از B وجود دارد. آیا A → C: خیر زیرا به ازائ a1 دو مقدار c1, c2 را برای C داریم. آیا B → A: خیر زیرا به ازائ b1 دو مقدار a1, a2 را برای A داریم.

6. نکاتی در مورد وابستگی تابعی • معنای وابستگی تابعی: هر FD یک قاعده یا محدودیت معنایی از محیط است که باید در سیستم تعریف گردد و در طول حیات رابطه رعایت شود. به عنوان مثال وقتی می گوییم هر تهیه کننده باید یک شماره واحد داشته باشد باید وابستگی تابعی S# → Sname را تعریف نماییم. • طرفین یک وابستگی تابعی (FD) می تواند صفات ساده باشند یا مرکب. • مثال: (x, y) → z یا (S#, P#) → QTY • اگر B ⊆ A باشد آنگاه A → B یک وابستگی تابعی بدیهی یا نامهم (Trivial) است. • اگر K، سوپر کلید، کلید کاندید، کلید اصلی یا کلید بدیل باشد، در این صورت K می تواند هر زیر مجموعه ای از مجموعه عناوین رابطه R را تعیین کند. در واقع همه صفت های یک رابطه به کلید رابطه وابستگی تابعی دارند، پس خواهیم داشت: K → G, G ⊆ HR

7. نمایش وابستگی های تابعی • بوسیله نمودار وابستگی های تابعی (FDD): • بوسیله مجموعه F: • در خود رابطه با فلش ها:

8. قواعد استنتاج آرمسترانگ

9. مجموعه كاهش‌ناپذير وابستگي هاي تابعي • بطور کلی در X → Y، X کاهش ناپذیر است هرگاه Y با هیچ زیرمجموعه ای از X، بغیر از خودش، وابستگی تابعی نداشته باشد. • نکته: X → Y را تام (کامل) می نامیم هرگاه X کاهش ناپذیر باشد، در غیر اینصورت این وابستگی تابعی غیرتام خواهد بود. به عنوان مثال اگر X یک صفت ساده باشد پس کاهش ناپذیر است و درنتیجه X → Y تام خواهد بود. • مثال: در شکل روبرو X کاهش پذیر است زیرا: A به تنهایی می تواند Y را تعیین کند پس داریم A → Y

10. رابطه‌ 1NF, 2NF • رابطه‌اي 1NF است اگر هر صفت خاصه آن در هر تاپل، تك‌مقداري باشد، به بيان ديگر، صفت چندمقداري نداشته باشد. • رابطه 1NF آنومالی در ذخیره سازی دارد. برای کاهش این آنومالی ها باید رابطه را نرمال تر کنیم. • الگوریتم عمومی نرمالتر سازی، تجزیه عمودی یک رابطه به دو یا چند رابطه جدید می باشد بطوری که رابطه های جدید آنومالی های رابطه اولیه را نداشته باشند. برای تجزیه عمودی از عملگر پرتو (Project) استفاده می کنیم. (برای یک رابطه با n صفت، 2n – 1 پرتو وجود دارد) • رابطه‌اي 2NF است اگر اولا 1NF باشد و ثانيا تمام صفات غيركليد با كليد اصلي وابستگي تابعي تام داشته باشند. به عبارت ديگر هر صفت غيركليد به كليد اصلي بطور كاهش‌ناپذير وابسته باشد. • نکته: برای تبدیل 1NF به 2NF باید وابستگی های تابعی ناکامل (غیرتام) با کلید را با استفاده از پرتوگیری از بین ببریم. (رابطه را به چند رابطه تجزی کنیم)

11. مثال برای تبدیل رابطه 1NF به 2NF رابطه First در سطح اول نرمال است، یعنی تمامی صفات تک مقداری هستند. FDهای این رابطه در شکل زیر آورده شده: با تجزیه رابطه فوق به دو رابطه زیر، FDهای غیرتام از بین می روند و رابطه فوق از 1NF به 2NF تبدیل می شود:

12. آنومالی رابطه Second رابطه Second آنومالی در ذیره سازی دارد. آنومالی در درج: نمی توان یک شهر با Status جدید را درج کرد. مثلا <C12, 20> را بدلیل نداشتن S# نمی توان درج کرد. آنومالی در حذف: اگر فرض کنیم سطری با مقدار <S5, C5, 25> در جدول وجود داشته باشد و بخواهیم <S5, C5> را حذف کنیم، بدلیل اینکه در سطرهای دیگر جدول اطلاعات C5 را نداریم پس با این حذف Status این شهر را بطور ناخواسته از دست می دهیم. آنومالی در بهنگام سازی: اگر بخواهیم وضعیت C1 را تغییر دهیم باید چند سطر را تغییر بدهیم پس فزونکاری رخ می دهد. • درنتیجه باید با تجزیه این جدول به دو یا چند جدول جدید، جدول را نرمال تر کرده و آنومالی ها را حذف کنیم.

13. رابطه‌ 3NF • رابطه‌اي 3NF است اگر 2NF باشد و هر صفت غيركليد با كليد اصلي، وابستگي تابعي بي‌واسطه داشته باشد. • در شکل زیر به دلیل اینکه C به واسطه B به K (کلید اصلی) وابسته است، پس رابطه ای با این FDها3NF نمی باشد. • پس در واقع در 3NF هر FD باید ناشی از کلید اصلی باشد. • تعریف: اگر A → B و B → C آنگاه A → C و می گوییم C با A وابستگی تابعی تراگذری (Transitive) دارد. • پس می توان گفت رابطه ای در سطح نرمال سوم قرار دارد که وابستگی تابعی تراگذری (TFD)، نداشته باشد.

14. مثال برای تبدیل رابطه 2NF به 3NF رابطه Second در سطح دوم نرمال است ولی آنومالی دارد و باید نرمال تر شود. با توجه به FDهای رابطه فوق بدلیل اینکه TFD داریم پس با تجزیه جدول به دو جدول زیر، TFD را حذف می کنیم و رابطه را 3NF می کنیم. City

15. صورت نرمال بايس-كاد (BCNF) • تعریف: در A → B، A دترمینان یا تعیین کننده است. • رابطه‌اي BCNF است اگر در آن هر دترمينان، كليد كانديد باشد. یعنی باید قسمت چپ هر FD یک کلید کاندید قرار داشته باشد. • مثال: رابطه زیر در سطح BCNF هستند، زیرا دترمینان همه وابستگی های تابعی یک کلید کاندید است. • نکته: BCNF از 3NF قوی تر است، یعنی اگر رابطه ای BCNF باشد 3NF نیز هست ولی عکس این قضیه همیشه برقرار نیست.

16. صورت نرمال بايس-كاد (BCNF) • نکته1: اگر R یک کلید کاندید داشته باشد و در 3NF باشد آنگاه در BCNF هم هست. • نکته2: اگر R چند کلید کاندید داشته باشد و این کلیدها صفت مشترک نداشته باشند، و R در 3NF باشد آنگاه در BCNF هم هست، ولی در صورتی که کلیدهای کاندید صفت مشترک داشته باشند اگر R در 3NF باشد آنگاه در BCNF نخواهد بود. • مثال: رابطه مقابل در 3NF است ولی BCNF نیست. اثبات: رابطه در 1NF است چون صفت چندمقداری ندارد. رابطه در 2NF است چون FD غیرتام ندارد. بدلیل اینکه TFD نداریم پس رابطه در 3NF است. S# و Sname دو دترمینان هستند که کلید کاندید نیست، پس رابطه در BCNF نیست. • در این مثال دو کلید کاندید داریم و این دو کلید باهم درصفت P# مشترک هستند و این رابطه در 3NF است، ولی در BCNF نیست.

17. وابستگي تابعي چندمقداري • تعريف اول- در رابطه R(X,Y,Z) با صفات ساده يا مركب X، Y و Z مي‌گوييم كه Y با X وابستگي تابعي چندمقداري دارد و چنين نمايش مي‌دهيم: X →→ Y اگر به يك مقدار X، مجموعه‌اي از مقادير Y متناظر باشند. • در شکل مقابل مجموع های از مقادیر B به مقداری از A وابستگی دارد. بعنوان مثال مجموعه {b1, b2, b3} به مقدار a1 وابسته هستند و بهمقداری از C وابستگی ندارند.در این صورت می گوییم B به A وابستگی چند مقداری (Multi Value Dependency (MVD)) دارد و اینگونه نمایش می دهیم: A →→ B • نکته1: اگر A →→ B و B ⊆ A یا A ⋃ B = HR آنگاه MVD را نامهم می گوییم. • نکته2: هر MVD یک FD است یعنی داریم: • If A → B ⇒ A →→ B • عکس این قضیه صادق نیست.

18. رابطه 4NF • رابطه‌اي 4NF است اگر BCNF باشد و در آن وابستگي تابعي چند مقداري مهم وجود نداشته باشد. • ایده اصلی 4NF اینست که رابطه BCNF بازهم می تواند آنومالی داشته باشد. • مثال: رابطه زیر در BCNF است ولی آنومالی دارد زیرا C# →→ T#. • نکته مهم: هر رابطه تمام کلید حداقل در BCNF است. زیرا یک کلید کاندید دارد و آن هم خود Heading رابطه است. • کلید رابطه مقابل C#, T#, X# است پس رابطه تمام کلید است و در BCNF می باشد. ولی بدلیل وجود C# →→ T# و C# →→ X# رابطه در 4NF نیست. حال باید رابطه را به دو رابطه بر اساس مبدأ MVD تجزیه کنیم. خواهیم داشت:

19. رابطه 5NF • وابستگي پیوندی (Join Dependency): رابطه R به n پرتوش R1, R2, …, Rn وابستگی پیوندی دارد اگر R دقیقا با پیوند n پرتوش بدست آید و نه کمتر. و این گونه نمایش می دهیم: R = JD*(R1, R2, …, Rn) یا R = *(R1, R2, …, Rn) • تعريف: رابطه‌اي 5NF است اگر تمام وابستگي هاي پيوندي آن ناشي از كليدهاي كانديد آن باشد. در نتيجه: اگر بتوانيم يك وابستگي پيوندي در رابطه R پيدا كنيم كه در همه پرتوهايش كليد كانديد R وجود نداشته باشد، رابطه 5NF نيست. • به بیان دیگر اگر در Heading همه پرتوهایی که از پیوند آن ها رابطه R بدست می آید، کلید کاندید وجود داشته باشد آنگاه R در 5NF است. • به عبارت دیگر اگر Heading همه پرتوهایی که از پیوند آن ها رابطه R بدست می آید، خود یک Super Key باشد آنگاه R در 5NF است. • روش سریع تشخیص 5NF: اگر R در 3NF باشد و تمامی کلیدهای کاندید آن ساده باشند، آنگاه R در 5NF است.

20. مثال • رابطه زیر در 4NF است ولی در 5NF نیست. اثبات: • بدلیل اینکه رابطه تمام کلید است پس BCNF است. • بدلیل اینکه MVD وجود ندارد پس 4NF است. (از روی مقادیر جدول عدم وجود MVD قابل تشخیص است) • این رابطه 5NF نیست زیرا یک وابستگی پیوندی به شکل زیر دارد: SPJ = *(SP, PJ, SJ) ولی مجموعه عنوان سه پرتو SP, PJ, SJ فاقد کلید کاندید می باشند، پس در نتیجه رابطه فوق در 5NF نیست.

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

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

23. ؟