مثال انواع رابطه میان جدول ها

در مطلب قبلی در مورد انواع رابطه میان جدول ها صحبت کردیم. دو رابطه یک به چند (one to many) و چند به چند (many to many) پر استفاده ترین و عمومی ترین رابطه ها هستند. در این مطلب می خواهیم با استفاده از یک نمودار EER مثال جامعی را در ارتباط با انواع رابطه های یک به چند و چند به چند مطراح کنیم. در مطلب های بعدی ابتدا انواع داده (data type) را آموزش می دهیم سپس توضیح می دهیم چگونه یک جدول را ایحاد و تغییر دهیم و سپس انواع محدودیت ها و ایجاد کلید اصلی و خارجی را آموزش خواهیم داد.

در این مطلب از دیاگرام EER از سایت MySQL برای آموزش رابطه میان جدول ها استفاده کرده ایم. در واقع اصول و مفهوم رابطه میان جدول ها هیچ تفاوتی با یکدیگر ندارند و تنها هدف ما برای استفاده از این دیاگرام تنها و تنها سادگی و جامع بودن آن است. توجه کنید که هدف از این مطلب این است که شما خودتان بتوانید دیگر دیاگرام های مربوط به دیگر پایگاه داده ها را مطالعه کنید و متوجه شوید زیر پیش از اجرای کوئری بر روی جدول یا جدول ها، درک ارتباط میان این جدول ها است.

در واقع زمانی که می خواهیم داده ها را از بیش از یک جدول بدست آوریم، در واقع باید درک درستی از رابطه میان آنها داشته باشیم. باید بدانیم کدام دو جدول به یکدیگر مرتبط هستند و در نهایت چه داده هایی را می توانیم از JOIN کردن (یا الحاق و ترکیب) کردن ستون های دو جدول بدست آوریم.

نمودار یا دیاگرام ERR چیست

EER مخفف Enhanced or ExtendedRelationship Entity است که یک روش یا مدلی است که از آن برای بیان ساختار جدول های پایگاه داده استفاده می شود. پیش از این توضیح دادیم که هر جدول در پایگاه داده معادل با یک موجودیت در سیستمی است که می خواهیم آنرا مدل کنیم. همچنین هر موجودیت دارای یک سری از خصوصیت ها (attributes) مانند نام، سن، تاریخ تولد، تاریخ استخدام، آخرین زمان ورود به وب سایت، مجوزهای اعطایی به کاربر و غیره است.

ER که مخفف Entity Relationship است و همانطور که از نام آن مشخص است، روشی است برای بیان ساختار جدول های پایگاه داده و رابطه میان آنها، مستقل از پایگاه داده ای (مانند Oracle, SQL Server, MySQL و PostgreSQL) است که جدول ها در آن ایجاد شده اند.

ER در سال ۱۹۷۶ معرفی شد ولی بعد ها نسخه توسعه یافته آن، یعنی EER با ویژگی های بیشتر معرفی شد. مدل ER سه مفهوم اصلی یعنی موجودیت، رابطه (ارتباط) و خصوصیت ها را به صورت گرافیکی نمایش می دهد. در واقع از نمودارهای ER و EER پیش از ایجاد و پیاده سازی فیزیکی جدول ها در پایگاه داده هدف استفاده می شود، تا بتوانیم مدل مفهومی از سیستم، موجودیت ها، خصوصیت های و ارتباط های آنها را به صورت گرافیکی نمایش دهیم.

شکل زیر یک نمونه دیاگرام EER را که شامل ۶ جدول و رابطه میان آنها می شود را نشان می دهد. هر یک از مربع ها نماینده یک موجودیت یا یک جدول است که دارای خصوصیت های مختلف و نوع داده ای متفاوت هستند. به طور مثال جدول Employee دارای خصوصیت ها و انواع داده زیر است:

  • emp_no – شناسه کارمند و از نوع عدد صحیح INT است.
  • birth_date – تاریخ تولد کارمند و از نوع داده تاریخ DATE است.
  • first_name – نام کارمند و از نوع داده ای رشته VARCHAR است.
  • last_name – نام خانوادگی کارمند و از نوع داده ای رشته VARCHAR است.
  • gender : جنسیت کارمند و از نوع ENUM است. ENUM در پایگاه داده ها مفهومی شبیه به نوع enumeration در زبان های برنامه نوسی دارد. در واقع نوع ENUM لیست مرتب شده ای از مقادیر رشته ای است.
  • hire_date : تاریخ استخدام کارمند و از نوع داده تاریخ DATE است.

در مطالب بعدی در مورد ایجاد جدول، ستون ها و تعیین نوع ستون ها صحبت شده است.

مشخص کردن تعداد نمونه ها در دیاگرام EER

همانطور که گفتیم هر سطر از جدول معادل با یک نمونه از آن جدول است. مطابق شکل زیر می توانیم حداقل و حداکقر تعداد نمونه هر یک از موجودیت ها (جدول ها) را در دیاگرام EER مشخص کنیم. به طور مثال One or Many به معنی وجود حداقل یک تا چندین نمونه است و Zero or Many به معنی عدم وجود نمونه تا چندین نمونه است.

به طور مثال یک رابطه یک به چند میان جدول های Employees و Salaries وجود دارد. اگر به اتصال میان دو جدول دقت کنید، خواهید دید که از نوع One or Many است. عدد ۱ و علامت ∞ در سمت جدول های Salaries و Employees به معنی وجود یک رابطه یک به چند میان این دو جدول است. به عبارت دیگر هر سطر از جدول Salaries مربوط به یک کاربر است ولی هر کارمند می تواند چندین حقوق واریزی داشته باشد.

توجه کنید که ستون emp_no کلید خارجی در جدول Salaries است که به کلید اصلی جدول Employees اشاره می کند. شکل زیر نمونه ساده و خلاصه شده ای از ستون های دو جدول Salaries و Employees را نشان می دهد. همانطور که می بینید در جدول Salaries به ازای هر کارمند، چندین سطر (یک یا بیشتر سطر) وجود دارد.

در شکل دیاگرام EER میان دو جدول Employees و Departments رابطه چند به چند وجود دارد و به این معنی است که هر کارمند عضو چند دپارتمان و هر دپارتمان چندین کارمند دارد. جدول DEP_EMP جدول میانه یا واسطه میان دو جدول Employees و Departments است. همانطور که می بینید ستون های emp_no و dept_no به ترتیب کلیدهای خارجی به دو جدول Employees و Departments هستند.

توجه کنید که هر جدول رابطه یک به چند با جدول میانه (یا واسطه) دارد و رابطه چند به چند از طریق همین جدول واسطه میان دو جدول برقرار می شود. در مطلب های بعدی در مورد ایجاد کلید اصلی و کلید خارجی به طور ویژه در پایگاه داده اوراکل صحبت می کنیم ولی به طور کلی چندین مورد در زمان استفاده از پایگاه داده ها وجود دارد:

۱ – SQL زبان مشترک میان تمامی پایگاه داده های رابطه ای است و به طور مثال نحوه ایجاد جدول، تعریف ستون ها، تعریف کلید اصلی، کلید خارجی و حتی تعریف محدودیت ها (constraints) تفاوت چندانی ندارد.

۲ – دیاگرام EER را پیش از طراحی فیزیکی پایگاه داده انجام می دهند.

۳ – طراحی جدول های پایگاه داده توسط EER مستقل از پایگاه داده هدف است ولی شاید در زمان پیاده سازی پایگاه داده کمی نحو (syntax) دستورها متفاوت باشد.

تمرکز ما در این دوره یادگیری زبان SQL است و نه یادگیری یک پایگاه داده خاص ولی برای اجرای کوئری ها از پایگاه داده Oracle XE (یا oracle express edition) استفاده کرده ایم که به راحتی در تمامی ویندوزها و توزیع های لینوکسی قابل نصب است. در دو مطلب بعدی به ترتیب نحوه نصب Oracle XE بر روی ویندوز و توزیع لینوکسی ابونتو را آموزش داده ایم.