رابطه یک به چند در sql

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

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

رابطه در پایگاه داده

یکی از انواع پایگاه داده، «بانک‌های اطلاعاتی رابطه‌ای» (Relational Database) هستند که در مقابل «پایگاه‌های داده هموار» (Flat Database) قرار می‌گیرند. در پایگاه داده رابطه‌ای برای جلوگیری از تکرار رکوردها یا فیلدهای متعدد، بین جدول‌ها رابطه ایجاد می‌کنند. در مقابل پایگاه‌های داده هموار بدون ایجاد چنین ارتباطی، داده‌های مربوط به هویت‌ها را ثبت می‌کند. اغلب در زمینه ثبت و نگهداری «اطلاعات تراکنشی» (Transaction) از پایگاه‌های داده هموار بهره می‌برند.

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

• حفظ امنیت داده‌ها در هر یک از جدول‌ها
• جلوگیری از افزونگی داده

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

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

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

جدول ۱: ثبت اطلاعات دو موجودیت (استان و شهرستان) در یک جدول و مشکل افزونگی

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

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

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

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

انواع رابطه در پایگاه داده

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

• رابطه یک به یک (One-To-One): در این رابطه هر رکورد از یک جدول فقط با یک رکورد از جدول دیگر ارتباط دارد و برعکس. برای مثال فرض کنید یک جدول شامل اطلاعات شخصی کارمندان و جدول دیگر شامل اطلاعات اداری کارمندان باشد. این دو جدول با رابطه یک به یک با یکدیگر ارتباط دارند. به طوری که مشخصات کامل هر کارمند در دو جدول ثبت و نگهداری می‌شود. می‌توان این دو جدول را به یک جدول تبدیل کرد. ولی یکی از دلایلی که از دو جدول برای نگهداری اطلاعات استفاده می‌شود، می‌تواند امنیت و تعیین محدودیت دسترسی به این اطلاعات باشد. تصویر 1، نحوه ارتباط بین دو جدول با رابطه یک به یک را نشان می‌دهد. هر مستطیل در این تصویر بیانگر یک رکورد است. به این ترتیب یک رکورد از جدول اول به یک رکورد از جدول دوم می‌تواند مرتبط باشد.

تصویر ۱: رابطه یک به یک

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

• رابطه یک به چند (One-To-Many): در این نوع رابطه هر رکورد از جدول اول می‌تواند با چند رکورد از جدول دوم (Related Table) ارتباط داشته باشد. این نوع رابطه بیشترین کاربرد را در بانک‌های اطلاعاتی و پایگاه داده دارد. همانطور که در تصویر ۲، مشاهده می‌شود، یک فیلد مشترک بین هر دو جدول وجود دارد و باعث ارتباط بین دو جدول شده است. در این تصویر هر مستطیل نشانگر یک رکورد است. به خوبی دیده می‌شود که یک رکورد از جدول اصلی با چند رکورد از جدول فرعی در ارتباط هستند.

تصویر ۲- رابطه یک به چند در پایگاه اطلاعاتی

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

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

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

تصویر ۳: رابطه چند به چند به همراه جدول واسط

فیلد کلید اصلی و کلید خارجی برای رابطه در پایگاه داده

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

فیلد کلید اصلی

همانطور که گفته شد، برای ایجاد رابطه در پایگاه داده و بین دو جدول، به یک فیلد مشترک احتیاج داریم. از طرفی می‌دانیم فیلد کلید اصلی (Primary Key) دارای دو ویژگی خاص نسبت به فیلدهای دیگر جدول در پایگاه داده است. فیلدی که به صورت کلید اصلی در جدول مشخص می‌شود، مقدار تکراری در جدول نداشته و از طرفی باید برای ثبت هر رکورد، مقدار دهی شود. بنابراین هیچ رکوردی با مقدار تکراری در فیلد کلید اصلی وجود نداشته و همچنین رکوردی نیز در جدول وجود ندارد که فیلد کلید اصلی برایش مقدار نداشته باشد. معمولا برنامه‌های مدیریت پایگاه داده (Database Management System) یا DBMSها، این ویژگی‌ها را برای فیلدی که توسط کاربر، به عنوان کلید اصلی معرفی شده، کنترل می‌کنند.

فیلد کلید خارجی

هنگامی که بخواهیم یک رکورد از یک جدول را با چند رکورد از جدول دیگر مرتبط کنیم، باید به شکلی فیلد مشترک را در هر دو جدول مشخص کنیم که بیانگر چنین رابطه‌ای باشد. اگر در جدول اول، فیلد مشترک، کلید اصلی و در جدول دوم فیلد مشترک کلید اصلی نباشد، از اصطلاح فیلد «کلید خارجی» (Foreign Key) برای فیلد مشترک در جدول دوم استفاده می‌کنند. زیرا در خارج از جدول خودش (یعنی در جدول اول) این فیلد کلید اصلی است.

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

• رابطه یک به یک: فیلد مشترک در هر دو جدول کلید اصلی است.
• رابطه یک به چند: فیلد مشترک در جدول اول، کلید اصلی و در جدول دوم، کلید خارجی است. به همین دلیل جدول اول را «جدول اصلی» (Primary Table) یا «جدول والد» (Parent Table) و جدول دوم را «جدول فرعی» یا «جدول فرزند» (Child Table) می‌گویند.

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

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

جدول ۳ نیز به اطلاعات شهرستان‌ها اختصاص دارد.

جدول ۳: فیلدها و رکوردهای جدول شهرستان به عنوان جدول فرعی در رابطه یک به چند

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

نکته: اطلاعات جدول ۲ و ۳، واقعی نیست و نباید به عنوان اطلاعات حقیقی این استان‌ها در نظر گرفته شود.

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

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

جدول ۴: نقش کلید اصلی در رابطه یک به چند

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

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

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

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

• آموزش‌‌های مجموعه نرم‌ افزاری آفیس
• آموزش اکسس (Access) به همراه کدنویسی VBA‎‎‎‎‎
• مجموعه آموزش‌های مهارت‌‌های اساسی کار با کامپیوتر
• آموزش کار با بانک اطلاعاتی اکسس (Access) در سی شارپ (#C)
• پایگاه داده و اصطلاحات کاربردی آن — به زبان ساده
• پایگاه های داده SQL و NoSQL و تفاوت آنها — به زبان ساده
• معرفی زبان SQL — راهنمای جامع

^^

آرمان ری بد (+)

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

بر اساس رای 9 نفر

آیا این مطلب برای شما مفید بود؟

منبع: blog.faradars.org