با توجه به قابلیت هایی که در این بخش برای DBMSها برشمردیم، باید این نکته را نیز خاطرنشان سازیم که همه سیستم های پایگاه داده معروفی که تاکنون تولید شده اند، از یک سیستم مدیریتی قوی برخوردار نیستند؛ بلکه صرفا دارای یک رابط کاربر برای اجرای دستورات او بر روی داده ها می باشند. اما وظایف مهمی از قبیل کنترل جامعیت اجرای تراکنشها یا ایجاد امکان استفاده همزمان کاربران زیاد از داده ها را انجام نمی دهند. از معروفترین DBMSها می توان از MS SQL Server، MySQL و Oracle نام برد. اما پایگاه داده های معروفی مانند Access و Foxpro در واقع DBMS نیستند و فقط یک سیستم بانک اطلاعاتی ساده تلقی می شوند.
نمودار ER در پایگاه داده
داده هایی که قرار است در پایگاه داده ذخیره شوند، ابتدا باید با یک دید سطح بالا از لحاظ معنایی و مفهومی مدلسازی شوند. حاصل عملیات مدلسازی معنایی، ساختار منطقی بانک اطلاعات و در واقع همان شمای ادراکی است.
مدلها و روشهای مختلفی برای مدلسازی معنایی داده ها وجود دارند که از معروفترین آنها می توان از مدل موجودیت ارتباط (ER)، مدل Niam، مدل زبان یکپارچه مدلسازی (UML) و تکنیک مدلسازی شیئی (OMT) نام برد.
مدل ER و نمودار ER
مدل ER در اواسط دهه ۸۰ میلادی در دانشگاه MIT توسط فردی به نام چن پیشنهاد شد. این مدل به مرور زمان پیشرفت کرد و کم کم به EER (ER توسعه یافته) معروف شد. اما هنوز هم در خیلی جاها با همان نام ER از آن نام برده می شود. در این بخش به شرح کامل این مدل می پردازیم.
در مدل ER سه مفهوم اصلی وجود دارد که عبارتند از: موجودیت، صفت و ارتباط. نمودار ER هم نموداری است برای مدلسازی معنایی داده ها بر پایه مدل ER و در واقع سه مفهوم اساسی مدل ER، یعنی نوع موجودیت، صفت و ارتباط را به صورت شماتیک نشان می دهد. قبل از هر چیز، بهتر است با این سه مفهوم آشنا شویم.
تعریف موجودیت
مفهوم کلی شیئ، پدیده و به طور کلی هر آنچه که می خواهیم در موردش اطلاعاتی در بانک اطلاعات داشته باشیم و با استفاده از آن اطلاعات، شناخت خود را در موردش افزایش دهیم.
به عنوان مثال، برای یک سیستم آموزشی که قرار است در یک دانشگاه استفاده شود، موجودیتهای دانشجو، استاد، دانشکده و درس را می توان نام برد.
همانطور که می بینیم موجودیت یک مفهوم کلی و یا یک نوع داده مجرد است. اما هر موجودیت میتواند نمونه هایی داشته باشد. نمونه های یک موجودیت، مصداق های آن موجودیت هستند و در واقع نمونه‌ها هستند که وجود خارجی دارند. به عنوان مثال، درس پایگاه داده ها با یکسری ویژگی های خاص خودش یک نمونه از موجودیت درس است.
اولین گام در مدلسازی معنایی بانک اطلاعات، شناسایی موجودیتهای مساله است. اما سوالی که ممکن است پیش بیاید این است که مثلا در مثال فوق، چرا خود دانشگاه را به عنوان یکی از موجودیتها در نظر نگرفتیم؟ یا به طور کلی بر چه اساسی اقدام به شناسایی موجودیتها می کنیم؟
یک قانون ساده برای تشخیص اینکه یک مفهوم خاص را آیا موجودیت به حساب آوریم یا خیر، این است که آن مفهوم بتواند شامل چند نمونه متمایز در حوزه مساله ما باشد. به بیان ساده، چیزی موجودیت تلقی می شود که بخواهیم لیستی از آنرا در سیستم نگه داریم. در مثال فوق، دانشگاه را موجودیت به حساب نمی آوریم؛ چون سیستم قرار است اطلاعات دانشجویان یک دانشگاه را مدیریت کند. در اینجا دانشگاه خود حوزه اصلی مساله است که شامل موجودیتهای نامبرده شده می باشد. اما اگر صورت مساله را اینطور عوض کنیم که می خواهیم اطلاعات آموزشی دانشجویان سراسر کشور را از طریق این سیستم ذخیره و بازیابی کنیم، آنگاه دانشگاه هم یک موجودیت خواهد بود.
نکته دیگر در مورد موجودیت ها این است که یک موجودیت معمولا بیش از یک ویژگی دارد که باید ذخیره شود. اما همانطور که خواهیم دید، این قانون همیشه صادق نیست.
در نمودار ER هر موجودیت در قالب یک مستطیل ساده نمایش داده می شود.
موجودیت مستقل و وابسته
موجودیت مستقل (قوی)، موجودیتی است که مستقل از هر موجودیت دیگر و به خودی خود، در یک محیط مشخص مطرح باشد. موجودیت وابسته (ضعیف)، موجودیتی است که وجودش وابسته به یک نوع موجودیت دیگر است. در واقع هر نمونه موجود از یک موجودیت ضعیف به یکی از نمونه های یک موجودیت قوی وابسته است. به این نوع ارتباط بین یک موجودیت ضعیف با یک موجودیت قوی، وابستگی وجودی گفته می شود.
به عنوان مثال، فرض کنید کارمند و فیش حقوقی دو موجودیت در یک سیستم پرسنلی باشند. پس اطلاعات تعدادی کارمند و نیز تعدادی فیش حقوقی (بعنوان نمونه های دو موجودیت) در سیستم ذخیره شده اند. اگر یک کارمند را از سیستم حذف کنیم، وجود فیش های حقوقی مربوط به او دیگر مفهومی ندارد و منطقا باید حذف شوند. پس در این مثال، کارمند یک موجودیت مستقل و فیش حقوقی یک موجودیت وابسته است. اگر اطلاعات افراد خانواده کارمندان (عائله کارمند) نیز به عنوان یک موجودیت دیگر در این سیستم ذخیره شده باشد، آن نیز یک موجودیت وابسته به موجودیت کارمند می باشد.
برای تمایز موجودیت های وابسته از موجودیت های مستقل، در نمودار ER موجودیت های وابسته با دو خط نشان داده می شوند.
تعریف صفت خاصه
خصیصه یا ویژگی یک نوع موجودیت است. هر نوع موجودیت مجموعه ای صفات خاصه دارد. هر صفت یک نام، یک نوع و یک معنای مشخص دارد. در نمودار ER هر یک از صفات خاصه یک موجودیت با یک بیضی نشان داده شده و به موجودیت متصل می شوند.
انواع صفت خاصه
- صفت ساده یا مرکب
صفت ساده صفتی است که مقدار آن از لحاظ معنایی تجزیه نشدنی یا اتومیک می باشد.
صفت مرکب صفتی است که از چند صفت ساده تشکیل شده است و به هر جز آن بتوان بطور مستقیم دسترسی داشت. برای مثال، صفت آدرس که خود شامل صفات جزئی تر شهر، خیابان و … است، می تواند یک صفت مرکب باشد، در صورتیکه ساختار آن در شمای ادراکی به گونه ای باشد که بتوان به این اجزا به طور مجزا و مستقیما دسترسی داشت. اما اگر ویژگی آدرس برای یک موجودیت به صورت یک رشته معمولی در نظر گرفته شود، دیگر یک صفت مرکب محسوب نمی‌شود؛ چون دسترسی مستقیم به هر جز آن نداریم.
در بانک اطلاعات رابطه‌ای، امکان تعریف صفات مرکب وجود ندارد و همه صفات باید ساده باشند. پس اگر بخواهیم به اجزای یک صفت مانند آدرس دسترسی داشته باشیم چه باید بکنیم؟ راه حل کلی برای این مساله این است که در صورتی که چنین ضرورتی وجود دارد، بجای تعریف یک صفت خاصه به نام آدرس برای موجودیت، اجزای آنرا به طور جداگانه (چند صفت ساده به جای یک صفت) در نظر بگیریم.
- صفت تک مقداری یا چند مقداری
صفت تک مقداری، صفتی است که برای یک نمونه از یک نوع موجودیت حداکثر یک مقدار از دامنه مقادیر را می گیرد.
صفتی که برای بعضی از نمونه های موجودیت ممکن است بیش از یک مقدار داشته باشد، یک صفت چند مقداری است. بعنوان مثال، صفت شماره دانشجویی، نام خانوادگی و غیره برای موجودیت دانشجو صفات تک مقداری محسوب می شوند؛ چون هر دانشجو تنها یک نام خانوادگی و یک شماره دانشجویی دارد. اما صفتی مانند شماره تلفن برای موجودیت دانشجو یک صفت چند مقداری است. زیرا بعضی از دانشجویان بیش از یک شماره تلفن دارند.
نمایش صفات خاصه چندمقداری در نمودار ER به صورت زیر است:
- صفت کلید یا غیر کلید
صفت کلید یا شناسه موجودیت صفتی است که یکتایی مقدار دارد. بعبارت دیگر مقدار این صفت بین نمونه های مختلف تکرار نمی شود. بعنوان مثال، شماره دانشجویی برای موجودیت دانشجو یک صفت کلید است.
در مدلسازی معنایی، برای هر موجودیت باید یک شناسه مشخص کرد. اما یک موجودیت ممکن است بیش از یک شناسه داشته باشد. مثلا کد ملی نیز یک شناسه دیگر برای موجودیت دانشجو است. در چنین حالاتی، یکی از شناسه ها را باید بعنوان شناسه اول (اصلی) مشخص کرد. صفت دیگر نیز میتواند بعنوان شناسه دوم (فرعی) معرفی شود.
صفتی که قرار است به عنوان شناسه اصلی یک موجودیت (وجه تمایز اصلی بین نمونه های موجودیت) استفاده شود بهتر است حتی الامکان طول مقادیرش کوتاه باشد.
در نمودار ER شناسه اول یک موجودیت با کشیدن یک خط و شناسه دوم با کشیدن دو خط در زیر عنوان آن مشخص می شود.
گاهی اوقات، یک موجودیت صفت تکرار ناپذیری ندارد تا بعنوان کلید آن در نظر گرفته شود. یک راه برای حل این مشکل استفاده از کلیدهای ترکیبی است. یعنی باید ترکیبی از صفات را بیابیم که آن ترکیب بین نمونه های آن موجودیت تکرار نشود. مثلا فرض کنیم موجودیت دانشجو اصلا صفاتی بنام شماره دانشجویی و کد ملی نداشت. در این حالت با این فرض که ترکیب سه صفت نام، نام خانوادگی و شماره شناسنامه بین دانشجویان امکان تکرار ندارد، می توان ترکیب این سه صفت را به عنوان کلید موجودیت دانشجو معرفی کرد. به چنین کلیدهایی کلید ترکیبی گفته می شود. روش نمایش کلیدهای ترکیبی در نمودار ER به صورت زیر است:
- صفت هیچ مقدار پذیر یا ناپذیر
هیچ مقدار یعنی مقدار ناشناخته، مقدار غیرقابل اعمال یا مقدار تعریف نشده که معمولا با واژه null نشان داده می شود. اگر مقدار یک صفت در یک یا بیش از یک نمونه از یک موجودیت، بتواند برابر با هیچ مقدار باشد، آن صفت هیچ مقدار پذیر است.
هیچ مقدار پذیر بودن یا نبودن هر صفت خاصه توسط طراح بانک اطلاعات و معمولا با توجه به اهمیت آن صفت تعیین می شود. مثلا نام خانوادگی دانشجو به دلیل اینکه مهم است که برای هر دانشجو مقدارش مشخص باشد، بهتر است هیچ مقدار پذیر نباشد. اما صفتی مثل شماره تلفن بدلیل اهمیت کمتر و نیز به این علت که ممکن است دانشجویی شماره تلفن نداشته باشد، ممکن است هیچ مقدار پذیر تعریف شده باشد.
- صفت ذخیره شده یا مشتق
صفت ذخیره شده صفتی از یک موجودیت است که مقادیرش برای نمونه های آن موجودیت در پایگاه داده‌ها ذخیره شود. صفت مشتق صفتی است که مقادیرش در پایگاه داده‌ها ذخیره نشده باشد، بلکه از روی داده های ذخیره شده قابل اشتقاق یا محاسبه باشد. به عنوان مثال، اگر تاریخ تولد هر دانشجو (به عنوان یکی از صفات خاصه) در سیستم ذخیره شود، دیگر نیازی به ذخیره صفت سن دانشجو نیست و این ویژگی با توجه به تاریخ تولد قابل محاسبه است. پس در اینجا تاریخ تولد یک صفت ذخیره شده و سن یک صفت مشتق است.
برای نمایش صفات مشتق در نمودار ER می توان یا بیضی مربوط به آن صفت و یا خط اتصال آن صفت به موجودیت را به صورت نقطه چین رسم کرد.
ارتباط
تعریف- تعامل و وابستگی بین دو یا بیش از دو نوع موجودیت است.
ارتباط بین دو موجودیت در حقیقت ارتباط بین نمونه های آن دو موجودیت است. هر ارتباطی دارای یک عنوان و یک مفهوم مشخص است. به عنوان مثال، بین دو موجودیت دانشجو و درس ارتباط “انتخاب” وجود دارد. یعنی نمونه های موجودیت دانشجو، نمونه هایی از موجودیت درس را انتخاب می کنند.
در نمودار ER ارتباط با نماد لوزی نشان داده می شود.
اگر نمونه هایی از یک موجودیت بتوانند با نمونه هایی از همان موجودیت ارتباط داشته باشند، باید یک ارتباط از موجودیت به خودش رسم شود. مثلا، بین نمونه های موجودیت درس، ارتباط پیش نیازی برقرار است.
ماهیت ارتباط
چندی ارتباط یا به عبارتی نوع تناظر بین نمونه های دو موجودیت حاضر در ارتباط را نشان می‌دهد. ماهیت ارتباط میتواند یک به یک (۱:۱)، یک به چند (۱:n) یا چند به چند (n:m) باشد.
ارتباط ۱:۱: هر نمونه از موجودیت اول می تواند فقط با یک نمونه از موجودیت دوم ارتباط داشته باشد. هر نمونه از موجودیت دوم نیز فقط می تواند با یک نمونه از موجودیت اول ارتباط داشته باشد. در واقع همان مفهوم تناظر یک به یک در نظریه مجموعه هاست.
به عنوان مثال، اگر درجه ارتباط بین موجودیت های دانشجو و درس ۱:۱ باشد (مطابق شکل زیر)، مفهوم ارتباط چنین است:
هر دانشجو می تواند فقط یک درس را بگیرد. هر درس هم می تواند فقط توسط یک دانشجو گرفته شود.
ارتباط ۱:n: هر نمونه از موجودیت اول می تواند با چند نمونه از موجودیت دوم ارتباط داشته باشد. اما هر نمونه از موجودیت دوم فقط می تواند با یک نمونه از موجودیت اول ارتباط داشته باشد.
اما اگر درجه ارتباط بین موجودیت های دانشجو و درس ۱:n باشد (مطابق شکل زیر)، مفهوم ارتباط چنین است:
هر دانشجو می تواند چند درس بگیرد. اما هر درس می تواند تنها توسط یک دانشجو گرفته شود.
ارتباط m:n: هر نمونه از موجودیت اول می تواند با چند نمونه از موجودیت دوم ارتباط داشته باشد. هر نمونه از موجودیت دوم نیز می تواند با چند نمونه از موجودیت اول ارتباط داشته باشد.
حال اگر درجه ارتباط بین موجودیت های دانشجو و درس m:n باشد (مطابق شکل زیر)، مفهوم ارتباط چنین است:
هر دانشجو می تواند چند درس را بگیرد. هر درس هم می تواند توسط چند دانشجو گرفته شود.
حد ارتباط
برای هر یک از موجودیت های شرکت کننده در یک ارتباط می توان یک حد مشخص کرد. این حد نشان دهنده حداقل و حداکثر تعداد نمونه های آن موجودیت است که می توانند در ارتباط شرکت کنند.
به عنوان مثال، حدود نشان داده شده در شکل زیر برای دو موجودیت دانشجو و درس بیانگر مفاهیم زیر هستند:
هر دانشجو حداقل ۱ و حداکثر ۵ درس را می تواند انتخاب کند.
هر درس می تواند توسط هیچ دانشجویی انتخاب نشود و یا حداکثر توسط ۲۰ دانشجو انتخاب شود.
شرکت اجباری و اختیاری در ارتباط
موجودیت هایی که در یک ارتباط شرکت دارند، نوع ارتباطشان می تواند اجباری یا اختیاری باشد. اگر مقدار حداقل حد یک موجودیت در یک ارتباط ۰ باشد، به این معنی است که نمونه ای از این موجودیت می تواند وجود داشته باشد که اصلا در ارتباط شرکت نکند. در این حالت شرکت موجودیت را اختیاری می گوییم. در غیر این صورت، شرکت موجودیت در ارتباط اجباری است.
اگر شرکت موجودیت دانشجو در ارتباط انتخاب درس اجباری و شرکت درس در این رابطه اختیاری باشد، این دو موضوع به شکل زیر در نمودار این دو موضوع به شکل زیر در نمودار ER نشان داده می شوند.
روش دیگر نمایش ارتباط اجباری با استفاده از دو خط است:
نکته مهم:
در یک ارتباط ۱:n اگر نوع مشارکت موجودیت سمت n اجباری باشد، این موجودیت یک موجودیت وابسته است که وابستگی آن به موجودیت سمت ۱ است.
ارتباط IS-A (هست یک، هست گونه ای از)
نوعی ارتباط بین دو موجودیت است و زمانی برقرار است که موجودیت دوم حالت خاص تری از موجودیت اول باشد. بعبارت دیگر موجودیت دوم همه صفات موجودیت اول را دارد بعلاوه یک یا چند صفت اضافه تر مخصوص خودش. این ارتباط در واقع همان ارتباط وراثت بین دو موجودیت است.
بعنوان مثال، اگر دو موجودیت دانشجو و کارمند داشته باشیم، هر دوی این موجودیتها در یکسری صفات عمومی از قبیل نام، نام خانوادگی و محل سکونت مشترک هستند. صفات مذکور صفاتی هستند که هر فرد انسانی دارد. پس می توان موجودیت دیگری به نام فرد در نظر گرفت که شامل این صفات عمومی است و در واقع تعمیم دهنده دو موجودیت دیگر است. در این حالت هر یک از دو موجودیت دیگر با این موجودیت ارتباط IS-A دارند (دانشجو هست یک فرد؛ کارمند هست یک فرد).
در نمودار ER، ارتباط IS-A بصورت زیر نشان داده می شود:
این ارتباط بیشتر در بانکهای اطلاعاتی شی گرا مناسب و مفید است؛ چرا که با رعایت سلسله مراتب مفهومی بین موجودیتها از تعریف صفات تکراری در آنها خودداری می کنیم. اما در بانک اطلاعات رابطه ای نیز امکان پیاده سازی این ارتباط وجود دارد. این کار به دو روش قابل انجام است:
روش اول: به ازای هر موجودیت سطح بالا و پایین یک جدول جداگانه در نظر گرفته شده که در جدول سطح پایین کلید اصلی موجودیت سطح بالاتر به عنوان کلید خارجی قرار می گیرد.
اشکال این روش این است که مثلا برای بازیابی اطلاعات یک دانشجو نیازمند مراجعه به دو جدول person و student هستیم.
روش دوم: همه صفات موجودیت سطح بالاتر که موجودیت سطح پایین تر نیز منطقا آنها را داراست، در موجودیت سطح پایینتر قرار می گیرد. در این حالت، دیگر نیازی به وجود جدول سطح بالاتر نخواهد بود، مگر اینکه ارتباط موجود یک تعمیم کامل نباشد یعنی موجودیت دوم فقط تعدادی از صفات موجودیت اول را داشته باشد. کاملا روشن است که اشکال این روش افزونگی است.
پروژه آماده رسم نمودار ER پایگاه داده
برای یک سیستم فروشگاه با مشخصات زیر یک نمودار ER طراحی کنید و سپس آنرا به بانک اطلاعات رابطه ای تبدیل کنید.
فروشگاه تعدادی مشتری ثابت و مشخص دارد که هر مشتری دارای کد اشتراک، نام، نشانی و تلفن می باشد.
هر یک از اجناس فروشگاه یک کد مشخصه، نام و قیمت دارد. (توجه شود که اجناس یک نمونه کد مسخصه یکسانی دارند.)
هر مشتری می تواند در هر بار خرید از برخی اجناس تعدادی را خریداری کند. بازای هر خرید یک فاکتور صادر می شود که دارای شماره و تاریخ است.
حل:
طراحی نمودار ER:
با توجه به سوال مشخص است که موجودیتهای اصلی عبارتند از: مشتری، جنس و فاکتور. از لحاظ ارتباط منطقی هم مشتری و جنس هر یک با فاکتور ارتباط دارند و باید توجه شود که ارتباط دو موجودیت مشتری و جنس فقط از طریق موجودیت فاکتور می باشد.
ماهیت ارتباط بین مشتری و فاکتور: برای هر مشتری می تواند در زمانهای مختلف چندین فاکتور ثبت شود. اما هر فاکتور مربوط به یک مشتری است. بنابراین ماهیت ارتباط ۱:n است.
ماهیت ارتباط بین جنس و فاکتور: با توجه به اینکه اجناس همسان یکی فرض می شوند (کد یکسانی دارند)، هر جنس ممکن است در چندین فاکتور ثبت شود. از طرفی هر فاکتور هم ممکن است شامل چندین قلم جنس باشد. بنابراین ماهیت ارتباط m:n است. در نتیجه این ارتباط خود به یک موجودیت تبدیل می شود. تعداد خریداری شده از یک جنس یک قلم اطلاعاتی است که هم مربوط به جنس و هم مربوط به فاکتور است. پس یک صفت خاصه برای موجودیت واسط آنها محسوب می شود.
تبدیل به بانک اطلاعات رابطه ای:
بعد از رسم یک جدول برای هر یک از موجودیتها،
- ارتباط میان مشتری و فاکتور ۱:n است. پس کلید اصلی مشتری (موجودیت سمت ۱) را به عنوان کلید خارجی به جدول فاکتور (موجودیت سمت n) نیز اضافه می کنیم.
- ارتباط بین جنس و فاکتور m:n است. بنابراین برای برقراری ارتباط بین دو جدول، یک جدول دیگر به عنوان جدول واسط طراحی می کنیم. در این جدول واسط، کلیدهای اصلی مربوط به هر دو موجودیت فاکتور و جنس به عنوان کلیدهای خارجی قرار می گیرند. این جدول نشان می دهد که چه فاکتوری (شماره فاکتور) شامل چه جنسی (کد جنس) بوده است. با قرار دادن صفت “تعداد خریداری شده” در این جدول مشخص می شود که چه فاکتوری شامل چه جنسی و به چه تعدادی بوده است.
برای کلید اصلی این جدول می توان از ترکیب دو صفت ش فاکتور و کد جنس استفاده کرد و یا از کلیدهای شماره گذاری اتوماتیک (Auto Increment Number) استفاده کرد.
پروژه رسم نمودار ER سیستم دانشگاه
برای یک سیستم دانشگاه با مشخصات زیر یک نمودار ER طراحی کنید و سپس آنرا به بانک اطلاعات رابطه ای تبدیل کنید.
دانشگاه شامل تعدادی دانشکده است که هر دانشکده یک کد، نام، رئیس و یک سال تاسیس دارد. در هر دانشکده تعدادی رشته است که هر رشته شامل یک کد، یک نام و یک مقطع است.
در هر رشته تعدادی دانشجو تحصیل می کنند که هر دانشجو دارای شماره دانشجویی، نام و نام خانوادگی است. هر رشته تعدادی استاد دارد که البته بعضی از اساتید بین چند رشته مشترک هستند. هر استاد دارای کد، نام است.
در سرفصل هر رشته تعدادی درس تعریف شده که هر درس دارای کد، نام درس و تعداد واحد است. هر درس می تواند چندین پیش نیاز داشته باشد و یا پیش نیاز چندین درس دیگر باشد.
در هر ترم از هر درس چندین گروه درسی ارائه می شود که هر گروه یک کد مشخصه، یک شماره گروه و یک ساعت تشکیل دارد.
از دروس ارائه شده هر دانشجو چندین مورد را انتخاب می کند و در نهایت برای هر کدام نمره ای کسب می‌کند.
حل: نمودار ER
مفاهیم اساسی مدل رابطه ای پایگاه داده
- مدل رابطه ای در سال ۱۹۷۰توسط کاد ابداع شد.
- در مدل رابطه ای، هر موجودیت در قالب یک جدول نشان داده می شود.
- به هر جدول، اصطلاحا یک رابطه (relation) گفته می شود.
- بانکهای اطلاعاتی که بر این اساس طراحی می شوند بانک اطلاعات رابطه ای (relational Database) نامیده می شوند.
تناظر بین مفاهیم رابطه ای و مفاهیم جدولی
جدول زیر تناظر بین مفاهیم رابطه ای و مفاهیم جدولی را نشان می دهد:
| مفهوم تئوریک | مفهوم جدولی |
| رابطه | جدول |
| تاپل (رکورد) | سطر |
| صفت | ستون |
| میدان | مجموعه مقادیر ستون |
| درجه | تعداد ستونها |
| کاردینالیتی | تعداد سطرها |
میدان (دامنه)
مجموعه ای است از مقادیر که یک با بیش از یک صفت از آن مقدار می گیرند. در مدل رابطه ای، این مجموعه:
- نامدار است (مثل int، char و …)
- مقادیرش نوع مشخص دارند.
- مقادیرش فرمت مشخص دارند.
تعریف رابطه (جدول)
با فرض وجود n میدان D1 تا Dn، نه لزوما متمایز، رابطه R از دو قسمت تشکیل شده است:
- سرآیند: مجموعه ای نامدار از n صفت به صورت Ai:Di که در آن هر Ai نام یک صفت است و هر Di نام میدان صفت
- پیکر (بدنه): مجموعه ای است از m تاپل t به نحوی که t خود مجموعه است از n عنصر هر یک به صورت Ai:vi که در آن vi مقداری است از نوع میدان.
ویژگی های رابطه:
ویژگی های رابطه عبارتند از:
- رابطه تاپل تکراری ندارد
- تاپلها نظم ندارند.
- صفات رابطه نظم مکانی ندارند.
- تمام صفات تک مقداری و ساده هستند.
کلید در مدل رابطه ای
در مدل رابطه ای چند مفهوم در بحث کلید داریم که عبارتند از:
- ابر کلید (super key)
- کلید کاندید (candidate key)
- کلید اصلی (primary key)
- کلید فرعی (alternate key)
- کلید خارجی (foreign key)
ابر کلید (super key)
تعریف- هر صفت خاصه یا ترکیبی از صفات در رابطه که یکتایی مقدار (تکرارنشدن) در گستره رابطه داشته باشد. میتواند کاهش پذیر یا کاهش ناپذیر (کمینه) باشد.
نکته: کلید کاهش ناپذیر کلیدی است که اگر هر جز آنرا حذف کنیم دیگر کلید نباشد.
مثال:
- ش دانشجویی (کاهش ناپذیر)
- ش دانشجویی و نام (کاهش پذیر)
- نام و نام خانوادگی و ش ش (کاهش ناپذیر)
کلید کاندید (Candidate Key)
تعریف- هر صفت خاصه یا ترکیبی از صفات در یک رابطه که دو خاصیت زیر را داشته باشد کلید کاندید رابطه است:
- یکتایی مقدار
- کاهش ناپذیری
کلید اصلی (Primary Key)
تعریف – یکی از کلیدهای کاندید رابطه که طراح انتخاب می کند و به عنوان کلید اصلی به سیستم معرفی می‌شود.
ضابطه های انتخاب:
- از نظر کاربر، شناسه معمول نوع موجودیت باشد.
- طول کوتاه تر داشته باشد.
کلید فرعی یا بدیل (Alternate key)
تعریف- هر کلید کاندید، غیر از کلید اصلی، کلید فرعی نام دارد.
مثال: برای رابطه دانشجو، اگر شماره دانشجویی و شماره ملی داشته باشیم، می توان شماره دانشجویی را کلید اصلی در نظر گرفت و شماره ملی کلید فرعی می شود.
کلیدخارجی (Foreign Key)
تعریف- دو رابطه R1 و R2 را در نظر می گیریم. صفتی (یا ترکیبی از صفات) در R1 که در این رابطه کلید نیست اما در R2 کلید اصلی است، یک کلید خارجی برای رابطه R1 نامیده می شود.
کلید خارجی برای برقراری ارتباط بین موجودیتها (جداول) بکار می رود.
مراحل ایجاد پایگاه داده در SQL Server:
مراحل ایجاد جدول در SQL Server
ساختن جدول شماره تلفن ها (TellNums)
نوع کلید جدول شماره تلفن ها (Identity (Auto Increment))
برقراری ارتباط میان جداول پایگاه داده SQL Server
تنظیم ارتباط بین کلید اصلی و خارجی از دو جدول:
- کلیک بر روی دکمه Add
۲٫ در بخش Tables And Column Spec، کلیک بر روی سه نقطه
۳٫ انتخاب جدول اصلی (Students) و انتخاب جدول دوم (TelNums) و انتخاب کلید اصلی (Id) و انتخاب کلید خارجی (StId)
Cascade Delete:
با حذف یک رکورد از جدول اصلی (دانشجو) تمام رکوردهای مرتبط با آن در جدول دوم (شماره تلفن آن دانشجو) نیر اتوماتیک حذف می شود.
Cascade Update:
با تغییر مقدار کلید یک رکورد از جدول اصلی (ش یک دانشجو) در تمام رکوردهای مرتبط با آن از جدول دوم نیز شماره دانشجو اتوماتیک اصلاح می شود.
قواعد جامعیت در بانک رابطه ای:
- جامعیت درون رابطه ای: هر رابطه به تنهایی درست باشد. کلید اصلی برایش تعریف شده باشد و خواص اصلی رابطه (مثل نداشتن تاپل تکراری) را دارا باشد.
- جامعیت موجودیتی: هیچ جزئی از کلید اصلی Null نباشد.
- جامعیت ارجاعی: کلید خارجی در یک رابطه یا باید شامل مقداری باشد که این مقدار در جدول اصلی وجود دارد و یا اینکه Null باشد.
نکته: بین کلیدها فقط کلید خارجی است که هم می تواند تکرار شود و هم Null باشد.
جبر رابطه ای:
در جبر ریاضی، عملوندها اعداد هستند. یعنی عملی مثل +، روی یک یا چند عدد انجام می شود و حاصل یک عدد دیگر می شود.
جبر رابطه ای جبری است که عملوندهای آن رابطه ها (جدولها) هستند.
یعنی عملی مثل پیوند، روی یک یا چند رابطه انجام می شود و حاصل یک رابطه دیگر می شود.
عملگرهای جبر رابطه ای
عملگرهای ساده (تک عملوندی)
Selection (گزینش)
عملگر گزینش تاپلهایی از یک رابطه را که شرطی را دارا باشند گزینش می کند (سطرهایی از جدول را).
نکته: این عملگر تک عملوندی است.
اسکیمای جدول حاصل تغییری نمی کند.
Projection (تصویر یا پرتو)
- عملگر تصویر نمایش رابطه را به تعدادی از فیلدها (بجای همه فیلدها) محدود می کند.
- این عملگر تک عملوندی است.
مثال:
مثال: لیست نام و نام خانوادگی تمام دانشجویان رشته کامپیوتر که شماره آنها کمتر از ۸۴۱۱۰ است.
حل:
مثال: لیست شماره و نام خانوادگی تمام دانشجویان رشته های کامپیوتر و شیمی.
حل:
مثال: لیست شماره تمام دانشجویان که شماره آنها کمتر از ۸۴۱۱۰ باشد.
حل:
عملگرهای مجموعه ای:
- اجتماع، اشتراک، تفاضل
- اسکیمای رابطه ها باید از نظر تعداد و نوع فیلدهای متناظر یکی باشد.
- اسکیمای رابطه حاصل با اسکیمای دو رابطه اولیه یکی است.
- دو عملوندی هستند.
اجتماع (Union)
حاصل اجتماع دو رابطه با اسکیمای مشابه رابطه ای است با همان اسکیما که شامل تمام تاپل های دو رابطه اول است. این عملگر دو عملوندی است.
اشتراک (Intersect)
حاصل اشتراک دو رابطه با اسکیمای مشابه رابطه ای است با همان اسکیما که شامل تمام تاپلهای مشترک بین دو رابطه اول است.
مثال:
تفاضل (Minus)
حاصل تفاضل دو رابطه با اسکیمای مشابه (R1-R2) رابطه ای است با همان اسکیما که شامل تمام تاپل هایی است که در R1 هستند اما در R2 خیر.
مثال:
نکته: اجتماع و اشتراک خاصیت جابجایی و شرکت پذیری دارند اما تفاضل هیچکدام را ندارد.
چند نکته:
- ضرب دکارتی در جبر ریاضی نه خاصیت جابجایی دارد و نه شرکت پذیری. اما در جبر رابطه ای هر دو را دارد.
- در جبر رابطه ای اگر عملی منجر به رکورد تکراری شود، رکوردهای تکراری حذف می شوند. اما در SQL به طور پیش فرض رکوردهای تکراری می مانند.
عملگرهای پیوند
- لزومی به یکسان بودن اسکیماها نیست.
- بعضی از تاپلهای رابطه اول با بعضی از تاپلهای رابطه دوم پیوند می خوردند.
- اسکیمای رابطه حاصل تغییر نمی کند.
- دو عملوندی هستند.
ضرب دکارتی (Cartesian Product)
- ساده ترین عملگر پیوند است.
- تمام تاپلها از دو رابطه دو بدو با هم پیوند می خورند.
نکته: ضرب دکارتی خاصیت جابجایی و شرکت پذیری دارد.