فصل نهم :
جدول ، رابطه و نرمالسازی (Relation & Relationship)
به مثال قبل بازمیگردیم جدولی داریم که اطلاعات شاگردان را در ان ذخیره شده است .
اگر بخواهیم مشخصات کلاس شاگردان را نیز ذخیره کنیم راه حل چیست ؟
1 . بیاییم فیلد هایی در جدول شاگردان اضافه کنیم و مشخصات کلاس شامل کد و یا شماره کلاس ، نام کلاس ، متراژ ، وضعیت تهویه و غیره ... را در انها ذخیره کنیم .یعنی به این شکل :
ClassName nvarchar(50) Checked
ClassID smallint Checked
Area real Checked
Capacity tinyint Checked
Ventilation nvarchar(50) Checked
Color nvarchar(50) Checked
Location nvarchar(50) Checked
تمام شاگردان در این کلاس هستند برای اینکه اطلاعات کلاس را برای یک شاگرد داشته باشیم باید بازای همه شاگردان اطلاعات کلاس را نیز وارد کنیم .
خطاخطاخطاخطاخطاخطا
اشتباه ترین انتخاب ممکن . چرا ؟ وقت زیادی برای وارد کردن اطلاعات کلاس برای هر شاگرد تلف میشود .
برای تعداد زیادی از شاگردان آنقدر حجم اطلاعات زیاد خواهد شد که دیتابیس ما بسیار حجیم و کند خواهد شد.
مگر نه اینکه همه این شاگردان در این کلاس هستند ؟ پارامتر مشترک بین آنها حضور در یک کلاس است. آیا می توان بجای درج اطلاعات تکراری تنها یکبار آنها را جدول دیگری درج کرد و به گونه ایی
این دو جدول را بهم مربوط کرد. که بدانیم هر شاگرد در چه کلاسی است ؟
مفهوم relational Database اینگونه متولد شد. دیتابیسی که در آن علاوه بر اطلاعات ارتباطات بین آنها نیز ذخیره شوند . و در آن تا حد ممکن از ذخیره اطلاعات تکراری اجتناب میشود .
جدولی دیگر میسازیم و فیلد های مربوط به اطلاعات کلاس را به آن منتقل میکنیم .
بنام ClassRoom
آنگاه یقینا برای این جدول احتیاج به کلید اصلی خواهیم داشت کد کلایس بهترین کاندیدا می باشد.کدی منحصر به فرد که بتوانیم یک کلاس را بوسیله آن از میان انبوه کلاسهای موجود در آموزشگاه مشخص کنیم.می توانیم یا خدمان کدی را به هر کلاس اختصاص دهیم و یا این کار را به اس کیو ال سرور بسپاریم و فیلد ClassID را identity کنیم .
حالا اطلاعات و مشخصات کلاس را در این جدول ذخیره میکنیم.
سوال : چگونه ارتباط بین شاگرد و کلاس بر قرار کنیم؟ چگونه بیان کنیم که هر شاگرد در چه کلاسی است ؟
جواب : در جدول جدید برای هر کلاس که دارای انواع مشخصات است یک کلید اصلی در نظر گرفتیم
اگر مقدار ان کلید اصلی را بازای اطلاعات هر شاگرد در جدول Student ذخیره کنیم. مشخص نموده ایم که هر شاگرد در چه کلاسی درس می خواند. بجای تکرار تمامی فیلد های مربوط به اطلاعات کلاس برای هر شاگرد. تنها یک کلید اصلی مربوط به ان کلاس را برای هر شاگرد نکهداری کردیم. پس حجم اطلاعات کمتر شده و از تکرار اطلاعات اجتناب کرده اییم.و در عین حال مشخص کرده ایم که هر شاگرد در چه کلاسی است.
پس فیلدی به جدول Student اضافه میکنیم که در آن مقدار کلید اصلی جدول ClassRoom را در آن ذخیره کنیم نام آنرا ClassRoomID می گذاریم. به این فیلد اصطلاحا "کلید خارجی" و یا Foreign key میگویند.
ارتباط بین این دو جدول چگونه است ؟ بازای هر کلاس بینهایت شاگرد می تواند وجود داشته باشند
یعنی 1 کلاس به بینهایت شاگرد یعنی 1 به بی نهایت .
بسیار مهم :
چگونه تضمین کنیم که هر شاگرد حتما به یک کلاس از پیش تعریف شده ایی منتصب باشد ؟
و چگونه تضمین کنیم هنگامی که بازای یک رکورد کلاس تعدادی شاگرد عضو آن موجود هستند ، به اشتباه رکورد کلاس آنها حذف نشود ؟
چون وجود شاگرد بدون کلاس تعریف شده اساسا بی معنی است. پس باید تضمین شود :
-
1 . هر شاگرد دارای کلاسی است ، تعریف شده . و نتوان به شاگرد کلاس تعریف نشده ایی را اختصاص داد.
-
2 . هنگامی که کلاسی دارای شاگرد/شاگردان است آن کلاس حذف نشود .
به این مفهوم referential integrity میگویند . که مفهومیست برای اطمینان از اینکه ارتباط(relationship) بین جداول دیتابیس محکم و استوار پا برجاست.
رکورد شاگردی نداریم که بدون کلاس ا ز پیش تعریف شده ای است کلاسی هم که دارای شاگر است قابل حذف نیست .
در مثال ما رکورد کلاس درسی که همه شاگردان دارای آن هستند . رکورد والد یا Parent و رکورد تمامی شاگردان فرزند و یا Child آن به حساب می ایند .
سوال چگونه referential integrity را تضمین کنیم ؟
آنچه بعنوان Relationship شناخته میشود برای همین مقصود بوجود آمده است.
ساخت Relationship : جدول مورد نظر را انتخاب کرده بروی آن کلید کرده و بروی فولدر Keys
کلیک راست میکنیم ایتم New Foreign Key را کلیک میکنیم.پنجره Foreign Key Relationships باز میشود .
در نود General به Tables And Columns Specification رفته و کلید .... را میزنیم در پنجره باز شده امکان تعریف نام relationship و انتخاب جداول Parent و Child وجود دارد.
در مثال ما Primary Key table جدول Classroom است چرا که بازای هر کلاس تعریف شده در آن تعدادی شاگرد وجود دارد.ابن جدول را انتخاب کرده و نام کلید اصلی آن که ClassID است را انتخاب میکنیم.
حالا در سمت راست پنجره جدول شاگردان (Student) و فیلد ClassRoomID را انتخاب میکنیم.
کلید ok را زده و پنجره قبلی را می بندیم بعد از بستن پنجره ایجاد relationship بایستی کلید ذخیره یا ctrl+s را بزنیم تا تغییرات ایجاد شده ذخیره شود
راه گرافیکی ساخت relationship
در زیر مجموعه یک دیتابیس یک فولدر وجود دارد بنام Database Diagrams
که با Drag & Drop می توان اینکار را انجام داد.
بروی آن فولدر کلیک راست کرده و New Database Diagram را میزنیم . پنجره ایی باز میشود و از ما نام جداولی که می خواهیم در دیاگرام مشاهده شوند را می خواهد. در جدول مورد نظر را انتخاب و کلید add را میزنیم. برای ایجاد relationship بین این دو جدول براحتی کلید اصلی جدول والد یعنی ClassRoom را Drag کرده و بروی فیلد Foreignkey که در جدول شاگردان در نظر گرفته ایم رها میکنیم. پنجره های باز شده مشابه روش قبل می باشند و در نهایت بایستی تغییرات ذخیره شوند.
نکته : در هنگام ایجاد relationship بین جداول فولدر Keys بطور اتوماتیک بروز رسانی نمی شود و
Relationship جدید را نشان نمی دهد . بروی فولدر کلیک راست کرده و ابتم refresh را میزنیم.
حالا برای تست اطلاعاتی را ابتدا در جدول کلاس و سپس در جدول شاگردان اضافه میکنیم .
می بینیم اگر برای شاگردی فیلد کلاس را به کد کلاس بی معنی و یا تعریف شده ایی وارد نکنیم امکان ثبت وجود ندارد.همچنین رکوردکلاسی که دارای شاگرد است از جدول کلاس حذف نخواهد شد.
مختصری در رابطه با نرمالسازی :
این سوال که اطلاعات را چگونه بکمک جداول و relationship ها ذخیره کنیم ؟ در هر جدولی چه فیلد هایی را در نظر بگیریم؟ فیلد ها چگونه طراحی شوند. آیا اطلاعاتی همانند نام و نام خانوادگی را در یک فیلد نگهداری کنیم یا در 2 فیلدو چرا ؟
نرمالسازی عبارتست از مجموعه قوانینی که رعایت انها حذف افزونگی (و یا به حد اقل رساندن آن) را تضمین میکند.
در مثال کلاس با تقسیم اطلاعات کلاس و شاگردان از درج اطلاعات تکراری کلاس در هر شاگرد جلوگیری کردیم. از افزونگی اطلاعات یا (Data Redundancy) جلوگیری کردیم. حالا فقط یک رکورد مربوط به اطلاعات هر کلاس داریم نه هزاران فیلد پرشده در جدول شاگردان.
پس نوعی نرمالسازی را انجام دادیم.
این مبحث بسیار گسترده بوده لذا اندکی در مورد آن در ابن آموزش سخن به میان می آید.
نرمالسازی چند شکل دارد.
1nf نوع اول : First Normal Form
در آن سعی میشود اطلاعات مربوط به موجودیت های مستقل در جداول مستقل ذخیره شوند
(مثل ما که شاگرد را از کلاس جدا کردیم.)
-
1. نوع داده در هر ستون یکسان باشد.
-
2. محتویات هر ستون یک مقدار مشخص
-
اتمیک باشد. بعبارتی یک فیلد شامل مقادیر ترکیبی نباشد مثلا فیلد شامل نام و نام خانوادگی با هم نباشد. چرا که پس از وارد کردن اطلاعات دسگر امکان تفکیک آنها از هم نیست (چگونه بفهمیمی کلام قسمت رشته نام و کدام قسمت نام خانوادگی است.) پس جواب یکی از سوالات بالا را یافتیم. یعنی همواره نام و نام خانوادگی بایستی در دو فیلد مجزا ذخیره شوند تا امکان تفکیک انها در صورت لذوم باشد.
-
4. هر رکورد و یا هر سطر از جدول ما بایستی یک عنصر منحصر بفرد داشته باشد. کلید اصلی Primary Key داشته باشد.
حالا دوباره جداول مان را اندکی بررسی میکنیم در جدول کلاس فیلدی بنام color داریم که رنگ کلاس در آن ذخیره میشود. ایا اگر نام هر رنگ تایپ شود (nvarchar()) فضای بیشتری اشغال میشود و یا اینکه بجای هر رنک کد مربوط به آن ؟ مثلا یک فیلد tinyint ؟ یقینا منطقیست که فیلد رنگ را از جدول کلاس خارج کرده و بجای آن foreign key در نظر بگیریم که در آن کلید اصلی جدول رنگ ها ذخیره شود. همین کار را می توان با فیلد های تهویه ventilation و موقعیت کلاس Location انجام داد . در نهایت شمای دیتابیس ما و طراحی جدول های ما بهتر خواهند شد . و از افزونگی اطلاعات جلوگیری کرده ایم. مهمترین راهنما در طراحی جداول تجربه می باشد. توانایی طراحی جداول دیتابیس و اینکه چگونه اطلاعات را در جداولی با طراحی نرمل ذخیره کنیم با سالها کارو تجربه بدست می آید.
بسیاری از مفاهیم درفیلم اموزشی بطور مفصل شرح داده شده اند . لذا حتما فیلم آموزشی این فصل را ببینید .
مفاهیم و موضوعات این فصل عبارتند از :
-
جدول (Table یا Relation)
-
رابطه (Relationship)
-
نرمالسازی (Normalization)