رابطه در اکسس — راهنمای کاربردی

هنگام کار با پایگاه‌های داده‌ای که به صورت رابطه‌ای (Relational) هستند، می‌توانید به کمک یک یا چند فیلد، جدول‌ها را به یکدیگر متصل کنید. چنین کاری باعث ایجاد یک پایگاه داده رابطه‌ای (Relational Database) می‌شود. جلوگیری از افزونگی و کاهش حجم بانک اطلاعاتی یا پایگاه داده (Database) از مزایایی استفاده از پایگاه داده رابطه‌ای است. از آنجایی که نرم‌افزار اکسس (MS-Access) نیز یک پایگاه داده رابطه‌ای است، در این نوشتار سعی داریم با نحوه ایجاد رابطه در اکسس آشنا شده و تنظیمات آن را بازگو کنیم.

به این منظور بهتر است ابتدا نوشتارهای مرتبط با مفاهیم اولیه بانک اطلاعاتی و همچنین انواع رابطه‌ها و نحوه تشکیل آن‌ها را در مجله فرادرس بخوانید. در نتیجه خواندن مطالب پایگاه داده و اصطلاحات کاربردی آن — به زبان ساده و رابطه در پایگاه داده | به زبان ساده ضروری به نظر می‌رسد. همچنین مطالعه آموزش اکسس رایگان | به زبان ساده و کامل و پایگاه های داده SQL و NoSQL و تفاوت آنها — به زبان ساده نیز خالی از لطف نیست. توجه داشته باشید که اجرای دستورات و تصاویر در این متن مربوط به اکسس ۲۰۱۶ است ولی با کمی جستجو، می‌توانید دستورات یا پنجره‌های مشابه را در نسخه‌های قدیمی‌تر اکسس نیز پیدا کرده و پایگاه رابطه‌ای خود را ایجاد کنید.

ایجاد رابطه در اکسس

پایگاه‌های داده رابطه‌ای، قادر هستند که با ایجاد ارتباط بین جدول‌ها، یک یا چند رکورد از یک جدول را به یک یا چند رکورد از جدول دیگر مرتبط کنند. به این ترتیب یک تغییر کوچک در یک فیلد در جدولی خاص، می‌تواند همه جدول‌های دیگر را تحت تاثیر قرار داده و گزارشات را به‌روزرسانی کند.

همین ویژگی از مزایای بسیار کاربردی برای پایگاه‌ داده رابطه‌ای مثل اکسس است. در این مطلب سعی داریم به کمک مثال‌هایی هر یک از انواع رابطه‌ها را در اکسس معرفی و پیاده‌سازی کنیم.

اگر نوشتار فرادرس در مورد انواع رابطه‌ها در پایگاه‌های داده را خوانده باشید، به یاد دارید که ارتباط بین دو جدول می‌تواند به یکی از شکل‌های زیر باشد:

• رابطه یک به یک (One to One): یک رکورد از جدول اول فقط با یک رکورد از جدول دوم در ارتباط است.
• رابطه یک به چند (One to Many): یک رکورد از یک جدول اصلی با چند رکورد از جدول فرعی مرتبط بوده و تغییرات در جدول اصلی در جدول ثانویه، به روز می‌شود.
• رابطه چند به چند (Many to Many): این گونه ارتباط معمولا به واسطه یک جدول واسط پیاده‌سازی می‌شود که هر دو جدول با آن دارای رابطه یک به چند هستند در نتیجه دو جدول اصلی با جدول واسط دارای رابطه یک به چند بوده و بین خودشان رابطه چند به چند خواهند داشت.

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

همانطور که به یاد دارید، مهم‌ترین موضوع در مورد ایجاد ارتباط بین دو جدول، استفاده از فیلدی با مقادیر مشترک است. این فیلد نه تنها در مقدار بلکه در نوع داده نیز باید یکسان باشند ولی هم‌نام بودن اسامی فیلد مشترک در دو جدول ضرورتی ندارد.

نکته: به یاد دارید که فیلد «کلید اصلی» (Primary key) در جدول‌ها، نقش مهمی در ایجاد و تعیین نوع رابطه بین جدول‌ها دارد. «کلید اصلی» (Primary Key)، فیلد یا فیلدهایی از جدول هستند که دارای دو ویژگی خاص است. اول آنکه مقدار آن در یک جدول، منحصر به فرد است و دوم اینکه حتما برای ثبت هر رکورد، باید مقدار دهی شود. در غیر اینصورت، اکسس از ثبت رکورد بازمانده و با پیغام خطایی، رکورد مورد نظر را ذخیره نمی‌کند.

یکی از مسائل مهم در طراحی پایگاه داده، شناسایی موجودیت‌ها و جدول‌ها است. معمولا قبل از آنکه داده‌ها وارد جدول‌ها شوند، در مورد موجودیت‌ها (Entity) و ارتباط جدول‌ها به یکدیگر باید قدری تفکر کرده و قدری تامل نمایید. متخصصین هنگامی که پایگاه داده و جدول‌ها را در اکسس پیاده‌سازی می‌کنند، ارتباط‌ها بین جدول‌ها را در نظر گرفته و پس از ایجاد رابطه‌ها داده‌ها را وارد می‌کنند. زیرا پس از آنکه در جدول‌ها مقادیر وارد شدند، ممکن است، برقراری ارتباط بین جدول‌ها به راحتی میسر نشده و حتی در بعضی از مواقع برای ایجاد رابطه بین جدول‌ها مجبور به حذف همه رکوردهای یک یا چند جدول می‌شویم.

رابطه در اکسس به صورت یک به یک براساس دو جدول

می‌دانید که برای ایجاد یک رابطه یک به یک، باید فیلد مشترک در بین هر دو جدول، کلید اصلی باشد. به این منظور هنگام طراحی جدول‌ها در اکسس به این موضوع توجه داشته باشید. به یاد دارید که لزومی ندارد فیلدهای مشترک در هر دو جدول، هم نام باشند، مهم یکسان بودن مقادیر و بخصوص نوع داده (Data Type) در هنگام طراحی جدول‌ها است. ولی ما به منظور نشان دادن فیلد مشترک، فیلدهای مورد نیاز را هم‌نام انتخاب کرده‌ایم.

برای نمایش رابطه یک به یک، جدول‌هایی شامل مشخصات فردی و دفترچه بیمه را در نظر می‌گیریم. از آنجایی که مشخصات فردی جدا از مشخصات بیمه‌ای است، بهتر است اطلاعات هر یک از آن‌ها را در جدول جداگانه‌ای ثبت کنیم. توجه دارید که هر فرد فقط یک دفترچه بیمه داشته و هر دفترچه بیمه مخصوص یک فرد است. در نتیجه اجرای رابطه یک به یک بین این دو جدول، معقول به نظر می‌رسد.

البته می‌توان هر دو جدول را با یکدیگر ترکیب و در یک جدول قرار داد ولی از آنجایی که ممکن است دسترسی به جدول اطلاعات فردی و پزشکی فرد (بیمه) از لحاظ امنیتی دارای محدودیت باشد، جدول‌ها را جدا کرده ولی با یک رابطه یک به یک به یکدیگر پیوند می‌زنیم.

نکته: توجه داشته باشید که جدول‌های ایجاد شده در قسمت قبل حتما باید ذخیره شده باشند تا بتوانیم بین آن‌ها رابطه برقرار کنیم. برای ذخیره‌سازی هر یک از جدول‌ها، کافی است روی برگه‌ای که نام جدول در آن دیده می‌شود، کلیک راست کرده و دستور save را اجرا کنیم. نام‌گذاری برای جدول‌ها نیز در این مرحله صورت می‌گیرد.

در تصویر ۱، یک نمونه از جدول مشخصات فردی (جدول personal) و در تصویر ۲، ویژگی‌های دفترچه بیمه (جدول insure) را مشاهده می‌کنید.

جدول زیر این فیلدها را معرفی کرده است. از آنجایی که هدف از این متن ایجاد رابطه است، در طراحی جدول‌ها از فیلدهایی با نوع short Text استفاده کرده‌ایم تا هم عدد و هم متن قابل ثبت در فیلدها باشند. هر چند ممکن است نوع داده مناسب‌تری برای هر یک از این فیلدها بتوان انتخاب کرد ولی برای تمرکز روی رابطه، از توضیحات مربوط به انتخاب نوع داده اجتناب کرده‌ایم.

علامت کلید در کنار فیلد کد ملی، نشانگر کلید اصلی بودن آن است. توجه داشته باشید که در هر دو جدول باید فیلد مشترک دارای نوع داده یکسانی باشد.

جدول زیر نیز فیلدهای مربوط به insure را معرفی کرده‌ است.

مشخص است که فیلد مشترک در هر دو این جدول‌ها، فیلد کد ملی یعنی (securitycode) است. بنابراین از آنجایی که در هر دو جدول، کد ملی منحصر به فرد و برای هر رکورد قابل ثبت است، از آن برای ارتباط بین دو جدول استفاده می‌کنیم.

نکته: گاهی فیلدی از جدول به طور ذاتی دارای خصوصیات مربوط به کلید اصلی است. بنابراین چنین فیلدی را «کلید اصلی ذاتی» (Inherent Primary Key) می‌نامیم. در غیر اینصورت مجبور به ایجاد یک فیلد در جدول به صورت کلید اصلی هستیم که مرتبط با موجودیت مورد نظر نیست (مثلا شماره ردیف). در این حالت کلید اصلی را «غیر ذاتی» (Non-Inherent) می‌نامند.

به منظور ارتباط دو جدول person به عنوان جدول مشخصات فردی با جدول insure به عنوان جدول دفترچه بیمه، از پنجره Relationship استفاده می‌کنیم. برای دسترسی به این پنجره از برگه Database Tools‌ در قسمت Relationships، گزینه Relationships را کلیک کنید.

جدول‌‌های مربوط را به این پنجره اضافه کرده و به کمک کشیدن و رها کردن یکی از فیلدهای مشترک از یک جدول به جدول بعدی،‌ رابطه بین آن‌ها را برقرار می‌کنیم. از آنجایی فیلد مشترک و ارتباط دهنده در هر دو جدول،‌ کلید اصلی است، اکسس متوجه می‌شود که رابطه بین آن‌ها باید به صورت «یک به یک» (One to One) باشد. در این هنگام به منظور تایید و تنظیمات مربوط به رابطه ایجاد شده، پنجره‌ای ویرایش رابطه (Edit Relationships)، مطابق با تصویر ۴، ظاهر می‌شود. گزینه‌های این پنجره در بخش‌های بعدی معرفی خواهند شد.

با فشردن دکمه Create در این پنجره، یک رابطه یک به یک بین دو جدول برقرار می‌گردد. همانطور که در تصویر ۵، مشاهده می‌کنید، به صورت نمادین، خطی بین دو جدول از طریق فیلد کلید اصلی (کد ملی - securitycode)، ترسیم شده که اتصال بین دو جدول را برقرار کرده است.

با فشردن دکمه Close از برگه Design می‌توانید به پنجره جدول‌ها بازگردید. همچنین اگر می‌خواهید رابطه ایجاد شده را ویرایش کرده و پنجره ویرایش رابطه را ظاهر کنید پس از انتخاب خط ارتباطی، از برگه Relationships دستور Edit Relationships را کلیک کنید. همچنین به منظور حذف کردن رابطه ایجاد شده، کافی است از فهرست کلیک راست روی خط ارتباطی بین دو جدول، دستور Delete را اجرا کنید. به این ترتیب رابطه بین دو جدول حذف شده و دیگر ارتباطی بین دو جدول برقرار نیست.

هنگامی که بین دو جدول ارتباط برقرار شد، از طریق جدول personal امکان دسترسی به رکوردهای جدول insure نیز بوجود می‌آید و می‌توان با پر کردن رکوردی از جدول اول، رکورد مرتبط با آن را در جدول دوم کامل کرد. بنابراین با باز کردن جدول personal، علامت $$\boxplus$$، در کنار رکورد جاری ظاهر شده که با فشردن آن در زیر رکورد جاری، رکورد مرتبط در جدول insure ظاهر می‌شود. به این ترتیب هر دو رکورد مرتبط با یکدیگر تکمیل و ثبت می‌شوند. با فشردن دکمه $$\boxminus$$، رکوردهای مرتبط، مخفی می‌شوند.

نکته: حتما قبل اعمال رابطه بین دو جدول، آن‌ها را ذخیره و ببندید تا به این ترتیب رابطه بین آن‌ها هنگام ورود رکوردها، عمل کند. سپس برای ثبت رکوردها جدول اول را باز کرده و رکوردها را وارد کنید.

رابطه در اکسس به صورت یک به چند براساس دو جدول

این بار فرض کنید که جدول اطلاعات فردی (Personal) با جدول اطلاعات خودرو افراد (Cars) باید مرتبط شود. از آنجایی که یک فرد می‌تواند بیش از یک خودرو داشته باشد، رابطه بین این دو جدول، رابطه‌ای یک به چند است. البته مالکیت اشتراکی خودرو را در اینجا لحاظ نخواهیم کرد. به منظور معرفی جدول خودرو از فیلدهایی مطابق با تصویر 8، کمک می‌گیریم.

مشخصات هر یک از فیلدهای Cars در جدول زیر معرفی شده‌اند.

همانطور که مشخص است، شماره بدنه خودرو (carcode) کلید اصلی در جدول خودرو در نظر گرفته شده است. البته این فیلد در جدول‌های دیگر، مقدار متناظر نداشته و مشترک نیست. بنابراین از آن برای ایجاد رابطه نمی‌توان استفاده کرد. فیلدی از این جدول که با جدول personal مشترک است، کد ملی (securitycode) است که در این جدول کلید اصلی نیست. به این ترتیب با توجه به ارتباط بین دو جدول بوسیله این فیلد، نوع رابطه بین دو جدول یک به چند (One to Many) خواهد بود.

برای ایجاد این رابطه کافی است، فیلدهای کلید اصلی و کلید خارجی (Foreign Key) را بین دو جدول به یکدیگر وصل کنید.

نکته: برای اضافه کردن جدول Cars به پنجره رابطه در اکسس از دکمه Add Tables از برگه Design استفاده کنید.

پنجره ویرایش رابطه نیز نشان می‌دهد که رابطه برقرار شده از نوع یک به چند است.

این بار با باز کردن جدول personal، می‌توانید مشخصات خودروهای یک فرد را ویرایش یا ثبت کنید. به این ترتیب با ورود یک رکورد از جدول اطلاعات فردی به چند رکورد از جدول خودرو دسترسی خواهید داشت.

رابطه در اکسس به صورت چند به چند براساس سه جدول

همانطور که می‌دانید امکان ایجاد رابطه در اکسس به صورت چند به چند به طور مستقیم وجود ندارد. برای ایجاد این گونه رابطه در اکسس باید از یک جدول کمک (Utility) کمک گرفت. فرض کنید بخواهیم ارتباط بین جدول فرد را با جدول ملک (House) برقرار کنیم. از آنجایی که ممکن است یک فرد چند خانه داشته باشد، رابطه یک به چند به نظر می‌رسد. ولی از طرفی ممکن است یک خانه چندین مالک داشته باشد، پس بین جدول ملک و جدول فرد نیز یک رابطه یک به چند برقرار باشد. بنابراین در اینجا با یک رابطه چند به چند مواجه هستیم. ابتدا موجودیت یا جدول ملک را مشخص می‌کنیم.

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

همانطور که مشاهده می‌کنید در این جدول، فیلدی به عنوان فیلد مشترک با جدول personal وجود ندارد و نمی‌توان آن‌ها را به یکدیگر پیوند دارد. همانطور که گفته شد در اینجا از یک جدول کمکی استفاده می‌کنیم. به نظر می‌رسد می‌توان این جدول را به نام سند مالکیت (Property) نام‌گذاری کرد. این جدول باید شامل فیلدهایی باشد که در هر دو جدول personal و house موجود یا مشترک باشند. بنابراین فیلدهایی که برای این جدول در نظر می‌گیریم می‌تواند مطابق با جدول یا تصویر زیر باشند.

همانطور که مشاهده می‌کنید، فیلدهای securitycode از جدول personal‌ و فیلد housecode از جدول house در این جدول حضور دارند تا بتوانند رابطه یک به چند را برقرار کنند. در ضمن شماره سند مالکیت (documentcode) در اینجا نقش کلید اصلی را داشته، ولی در رابطه دخیل نمی‌شود.

حال این جدول را به جدول‌های personal و house به صورت رابطه یک به چند متصل می‌کنیم. یعنی فیلد کلید اصلی securitycode از جدول personal‌ را به فیلد securitycode از جدول proerpty متصل کرده و فیلد کلید اصلی housecode را از جدول house را به فیلد کلید خارجی از جدول housecode ارتباط می‌دهیم.

به این ترتیب دو رابطه یک به چند ساخته شده که در کل رابطه چند به چند را ایجاد می‌کند.

نکته: اضافه کردن جدول به رابطه و ایجاد رابطه‌های یک به چند درست به همان شکلی که در قسمت قبلی برای ایجاد رابطه بین جدول خودرو (Cars) و فرد (Personal) گفته شد، صورت می‌گیرد.

تقویت رابطه در اکسس

توجه داشته باشید که رابطه‌های ایجاد شده در قسمت‌های قبل، کامل نیستند. به این معنی که ممکن است دفترچه بیمه‌ای صادر شود که مربوط به هیچ کسی نباشد. یا خودرویی معامله شود ولی کد ملی کسی به عنوان مالک برایش ثبت نشود. همچنین در رابطه چند به چند، در این حالت می‌تواند سندی تنظیم کرد که به هیچ خانه یا فردی تعلق نداشته باشد. برای کنترل و جلوگیری از بروز چنین مشکلاتی دو راه وجود دارد.

در اولین روش، می‌توانیم همه کنترل‌ها را به صورت دستی انجام داده و از ورود رکوردهای ناسازگار جلوگیری کنیم. ولی راه حل دوم استفاده از امکانات اکسس است. به این ترتیب هنگام ایجاد رابطه بین جدول‌ها، می‌توانیم تنظیماتی در پنجره ویرایش رابطه در اکسس اعمال کنیم تا وظیفه کنترل رکوردهای ورودی را برایمان انجام دهد.

فرض کنید بخواهیم رابطه بین دفترچه بیمه و مشخصات فرد را کنترل کنیم. پنجره ویرایش این رابطه را ظاهر می‌کنیم. همانطور که گفتیم، کافی است روی خط رابطه بین دو جدول کلیک راست کرده و گزینه Edit Relationship را انتخاب کنیم. پنجره ویرایش رابطه به مانند زیر ظاهر می‌شود. به اسامی جدول‌ها که در بخش Table/Query و Related Table/Query دیده می‌شود دقت کنید. اسامی جدول‌های مرتبط در این بخش مشخص شده است. اگر می‌خواهید ارتباط بین جدول‌های دیگر را ویرایش کنید کافی است نام جدول‌های دلخواه را در این قسمت انتخاب کنید.

تنظیمات مربوط به این پنجره در ادامه فهرست و معرفی شده‌اند:

• گزینه اول: انتخاب گزینه Enforce Referential Integrity، باعث ایجاد یکپارچگی بین جدول اول و دوم در رابطه می‌شود. به این معنی که وجود مقدار در فیلد مشترک در هر دو جدول کنترل می‌شود. در صورتی که مقدار ثبت شده در فیلد مشترک در یک جدول وجود داشته و در جدول دیگر ثبت نشده باشد، با پیغام خطا مواجه خواهیم شد.
• گزینه دوم: اگر بخواهیم با تغییر فیلد مشترک در جدول اول، مقادیر فیلدهای رکوردهای مرتبط با آن در جدول دوم نیز به روز شده و تغییر یابد، از گزینه Cascade Update Related Fields کمک می‌گیریم. برای مثال با تغییر کد ملی یک نفر (به شرطی که تکرار نشود) در جدول personal، مقدار کد ملی در جدول insure نیز تغییر یافته و به روز می‌شود. این به‌رورزسانی در زمان تغییر و ثبت اطلاعات بسیار کارآمد است.
• گزینه سوم: فرض کنید خدای ناکرده، فردی فوت کند، مسلما کد ملی و رکورد او از جدول personal باید حذف شده و دفترچه بیمه‌اش نیز باطل و رکورد مربوط به آن حذف شود. انتخاب گزینه Cascade Delete Related Records این وظیفه را به عهده دارد. با حذف رکورد مرتبط در جدول اولیه، رکورد (یا رکوردهای) مرتبط با آن در جدول دوم، حذف خواهند شد. البته این کار باید با احتیاج صورت گیرد زیرا شاید بخواهیم بعدها به اطلاعات بیمه فرد متوفی، دسترسی داشته باشیم.

در تصویر ۱۵، نتیجه به کارگیری گزینه Enforce را در نمای رابطه در اکسس بین جدول‌ها مشاهده می‌کنید. نماد $$1 --- 1$$ نشانگر رابطه یک به یک و نماد $$1 --- \infty$$‌ نشانگر، ارتباط یک به چند است.

نکته: اگر جدول‌ها شامل رکوردهایی باشند که باعث نقض تنظیمات شوند، اکسس با پیغامی جلوی اجرای تنظیمات را گرفته و به کاربر هشدار می‌دهد که عمل درخواستی قابل اجرا نیست.

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

هر چند در این نوشتار با مثال‌های ساده، رابطه در اکسس را آموزش دادیم ولی کار حرفه‌ای و ایجاد پایگاه داده کارآمد، احتیاج به تجربه و یادگیری مطالب بیشتری دارد. برای مثال ممکن است از کلیدهای اصلی ترکیبی برای ایجاد رابطه استفاده کرد تا رابطه‌های عمیق‌تری بین جدول‌ها برقرار شود یا اینکه به کمک Join Type، جهت ارتباط بین جدول‌ها را برای گزارش‌گیری و ایجاد پرس‌وجوها تعیین کنیم. سعی داریم در مطالب دیگر مجله فرادرس، قسمت‌های دیگر از نرم افزار پایگاه داده اکسس را نیز معرفی و به کار بگیریم.

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

خلاصه و جمع بندی

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