مبانی طراحی پایگاه داده در SQL

مبانی طراحی پایگاه داده در SQL

مبانی طراحی DB

برای طراحی schema های پایگاه داده، اجازه دهید 7 فرم عادی و مفاهیم عادی سازی و غیرعادی سازی را به یاد بیاوریم. آنها زیربنای تمام قوانین طراحی هستند.

اجازه دهید شرح مفصلی از 7 فرم معمولی ارائه دهم:

1. رابطه یک به یک

1.1 یک رابطه اجباری:

به عنوان مثال یک شهروند با گذرنامه (هر شهروند باید گذرنامه داشته باشد و گذرنامه برای هر شهروند یک پاسپورت است)

این رابطه به دو صورت اجرا می شود:

1.1.1 در یک موجودیت (جدول):

در اینجا، جدول Citizen نهاد شهروند را نشان می دهد و ویژگی PassportData (فیلد) حاوی تمام داده های گذرنامه یک شهروند است و نمی تواند خالی باشد (NOT NULL)

 

1.1.2. در دو موجودیت مختلف (جدول):

جدول Citizen نشان دهنده نهاد شهروندی و جدول PassportData نشان دهنده موجودیت داده های پاسپورت شهروند است. موجودیت شهروند حاوی ویژگی PassportID (فیلد) است که به کلید اصلی جدول PassportData اشاره دارد. در حالی که، موجودیت داده گذرنامه دارای ویژگی (فیلد) CitizenID است که به کلید اصلی CitizenID جدول شهروند اشاره دارد.

همچنین تضمین یکپارچگی فیلد CitizenID و جدول PassportData برای ارائه یک رابطه یک به یک مهم است. یعنی فیلد PassportID در جدول Citizen و فیلد CitizenID در جدول PassportData باید به همان رکورد اشاره کند که گویی یک موجودیت (جدول) است که در بند 1.1.1 نشان داده شده است.

 

1.2 یک رابطه اختیاری:

یک مثال در اینجا شخصی است که می تواند اطلاعات گذرنامه داشته باشد و ممکن است کشور مشخصی نداشته باشد. پس در صورت اول تابعیت یک کشور معین است و در حالت دوم نه.

این رابطه به دو صورت اجرا می شود:

1.2.1 در یک موجودیت (جدول):

 

در اینجا، جدول Person نهاد شخص را نشان می دهد، و ویژگی PassportData (فیلد) شامل تمام داده های پاسپورت یک شخص است و می تواند خالی باشد (NULL)

پایگاه داده در SQL

1.2.2 در دو موجودیت (جدول):

 

در اینجا، جدول Person نشان دهنده نهاد شخص است، و جدول PassportData نشان دهنده موجودیت داده گذرنامه شخص است (که خود پاسپورت است). نهاد شخص حاوی ویژگی PassportID (فیلد) است که به کلید اصلی جدول PassportData اشاره دارد. در حالی که، موجودیت داده گذرنامه دارای ویژگی (فیلد) PersonaID در جدول Person است. قسمت PassportID جدول Person می تواند خالی باشد (NULL).

همچنین تضمین یکپارچگی فیلد PersonID و جدول PassportData برای ارائه یک رابطه یک به یک مهم است. یعنی قسمت PassportID جدول Person و قسمت PersonID جدول PassportData باید به همان رکوردها اشاره کند که انگار یک موجودیت (جدول) نشان داده شده در پاراگراف 1.2.1 است، یا این فیلدها باید نامشخص باشند، یعنی حاوی NULL باشد.

 

2. رابطه یک به چند

2.1 یک رابطه اجباری:

نمونه ای از این موضوع می تواند والدین و فرزندانشان باشد. هر پدر و مادر حداقل یک فرزند دارند.

شما می توانید این رابطه را به دو صورت پیاده سازی کنید:

 

2.1.1 در یک موجودیت (جدول):

در اینجا جدول Parent نشان دهنده موجودیت والد است و ویژگی ChildList (فیلد) حاوی اطلاعاتی در مورد فرزندان، یعنی خود فرزندان است. این فیلد نمی تواند خالی باشد (NOT NULL). نوع فیلد ChildList معمولاً داده های نیمه ساختار یافته (NoSQL) مانند XML، JSON و غیره است.

 

2.1.2 در دو موجودیت (جدول):

 

در اینجا جدول Parent نشان دهنده موجودیت والد و جدول Child نشان دهنده موجودیت فرزند است. جدول Child دارای قسمت ParentID است که به کلید ParentID اصلی جدول Parent اشاره دارد. قسمت ParentID جدول Child نمی تواند خالی باشد (NOT NULL).

SQL Database

برای خرید لایسنس نرم افزار Tableau کلیک کنید

 

2.2. یک رابطه اختیاری:

یک مثال می تواند فردی باشد که ممکن است بچه دار شود یا بچه نداشته باشد.

این رابطه به دو صورت اجرا می شود:

 

2.2.1 در یک موجودیت (جدول):

در اینجا، جدول والد، موجودیت والد را نشان می‌دهد، و ویژگی ChildList (فیلد) حاوی اطلاعاتی در مورد فرزندان، یعنی خود فرزندان است. این فیلد می تواند خالی باشد (NULL). نوع معمول فیلد ChildList، داده های نیمه ساختاریافته (NoSQL) مانند XML، JSON و موارد دیگر است.

 

2.2.2 در دو موجودیت (جدول):

 

در اینجا جدول Parent نشان دهنده موجودیت والد و جدول Child نشان دهنده موجودیت فرزند است. جدول Child دارای قسمت ParentID است که به کلید ParentID اصلی جدول Parent اشاره دارد. قسمت ParentID جدول Child می تواند خالی باشد (NULL).

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

در اینجا، موجودیت Person (جدول) حاوی ویژگی ParentID (فیلد) است که به کلید PersonID اولیه همان Person جدول اشاره دارد و می تواند یک مقدار خالی (NULL) داشته باشد.

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

پایگاه داده در SQL

3. رابطه چند به یک

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

یک رابطه یک به چند و چند به یک نیز می تواند از طریق بیش از 2 موجودیت، با افزودن ویژگی های لازم که به کلیدهای اولیه موجودیت های مربوطه اشاره دارد، پیاده سازی کند. این پیاده سازی مشابه مثال های بالا در پاراگراف های 1.1.2 و 1.2.2 است.

 

4. رابطه چند به چند

در این مورد، به عنوان مثال، املاک و مستغلاتی است که می تواند در اختیار یک شخص یا چند نفر باشد. در عین حال، یک فرد می تواند چندین خانه داشته باشد یا در بسیاری از خانه ها سهم مالکیت داشته باشد.

شما می توانید این رابطه را با NoSQL به روش هایی که در بالا برای روابط قبلی توضیح دادیم پیاده سازی کنید. با این حال، در مدل رابطه‌ای، این رابطه معمولاً از طریق 3 موجودیت (جدول) پیاده‌سازی می‌شود:

 

 

در اینجا، جداول Person و Real Estate موجودیت یک شخص و املاک را بر این اساس نشان می دهد. این موجودیت ها (جدول ها) با استفاده از موجودیت (جدول) PersonRealEstate از طریق ویژگی های PersonID و RealEstateID (فیلدها) مرتبط هستند که به ترتیب به کلیدهای اولیه PersonID جدول Person و RealEstateID جدول RealEstate اشاره می کنند. توجه داشته باشید که جفت (PersonID; RealEstateID) همیشه برای جدول PersonRealEstate منحصر به فرد است، بنابراین می تواند کلید اصلی برای موجودیت پیوند دهنده (جدول) PersonRealEstate باشد.

این رابطه را می توان از طریق بیش از 3 موجودیت با افزودن ویژگی های لازم که به کلیدهای اولیه موجودیت های مربوطه اشاره دارد، پیاده سازی کرد. چنین پیاده سازی مشابه نمونه های شرح داده شده در پاراگراف های 1.1.2 و 1.2.2 است.

SQL Database

بنابراین، ممکن است تعجب کنید که 7 فرم معمولی کجا هستند؟

خوب، آنها اینجا هستند:

• (par.1.1 و1.2) اولین و دومین قاعده رسمی است.
• (par.2.1 و2.2) سومین و چهارمین قانون رسمی است.
• (مشابه بند 2) پنجمین و ششمین قانون رسمی است.
• هفتمین قانون رسمی است.

فقط این 7 فرم معمولی در 4 بلوک عملکردی در متن بالا گروه بندی شده اند.

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

فرآیند مخالف، غیرعادی سازی است. پردازش پرس و جو برای دسترسی به داده ها را با افزودن داده های اضافی ساده می کند (به عنوان مثال، همانطور که در قسمت 2.1.1 و 2.2.1 در بالا ذکر شد با کمک داده های نیمه ساختار یافته (NoSQL)).

آیا مطمئن هستید که امتیاز 7 فرم معمولی را گرفته اید؟ اینکه شما واقعاً آن را دریافت کردید و فقط با آن آشنا نشدید. از خود بپرسید که آیا در عرض چند ساعت می‌توانید یک مدل پایگاه داده، حتی با موجودیت‌های بیش از حد، برای هر دامنه داده یا هر سیستم اطلاعاتی طراحی کنید. بعداً می‌توانید پیچیدگی‌ها و جزئیات را با سؤال از تحلیل‌گران و نمایندگان مشتریان بررسی کنید.

SQL Database

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

به نوعی در منابع ذکر نشده است که این روابط بین موجودات نه تنها ساخته شده بلکه کشف شده است. یعنی از همان ابتدا آنها واقعاً در دنیای واقعی بین سوژه ها و اشیا وجود داشتند.

علاوه بر آن، این روابط می توانند تغییر کنند و از یک به یک به یک به چند، یا به چند به یک، یا به چند به چند تغییر کنند و ماهیت اجباری خود را تغییر دهند یا آن را حفظ کنند.

من فکر می کنم باید سعی کنید افراد را تماشا کنید و رابطه موجود بین سوژه ها و بین سوژه ها و اشیاء را تشخیص دهید (مثال بالا یک شهروند و گذرنامه را به عنوان یک رابطه یک به یک با ماهیت اجباری نشان می دهد و یک شخص و یک گذرنامه را به عنوان یک رابطه اجباری نشان می دهد. رابطه یک به یک که اختیاری است).

هنگامی که به 7 فرم معمولی بینش پیدا کردید، می توانید به راحتی یک مدل پایگاه داده با هر پیچیدگی برای هر سیستم اطلاعاتی طراحی کنید.

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

SQL Database

یک مدل پایگاه داده به خوبی طراحی شده، با توجه به تغییر رابطه در واقعیت و در حوزه موضوعی، برای مدت طولانی نیاز به هیچ گونه تغییری ندارد. این امر به ویژه برای ذخیره سازی داده ها که در آن تغییرات شامل ذخیره مجدد حجم زیادی از داده ها از چندین گیگابایت تا چندین ترابایت می شود، حیاتی است.

 

نکته: در مدل پایگاه داده رابطه ای، رابطه بین موجودیت ها است و ردیف ها (Tuples) نمونه هایی از این روابط هستند. اما برای ساده‌تر کردن، ما اغلب موجودیت‌ها را با جداول، و نمونه‌هایی از موجودیت‌ها را با ردیف‌ها، و ارتباط آن‌ها را با رابطه کلیدهای خارجی معنا می‌کنیم.

پایگاه داده در SQL

طراحی یک طرح پایگاه داده برای استخدام

پس از اینکه اصول طراحی DB را در قسمت اول مقاله توضیح دادیم، اجازه دهید یک طرح پایگاه داده برای استخدام ایجاد کنیم.

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

1- برای یک مدیر منابع انسانی:

• شرکت هایی که متقاضی قبلا در آنها کار می کرد.
• سمت هایی که متقاضی در این شرکت ها داشته است.
• مهارت هایی که متقاضی در محل کار از آنها استفاده می کند، مدت زمان استخدام در هر یک از شرکت ها و در هر موقعیت، مدت استفاده از هر مهارت.

 

2- برای کارشناس فنی:

• سمت هایی که متقاضی در محل های قبلی کار خود داشته است.
• مهارت هایی که متقاضی در محل کار از آنها استفاده می کند.
• پروژه هایی که متقاضی در آنها شرکت کرده است. علاوه بر آن، دانستن مدت زمان استخدام متقاضی در هر موقعیت و در هر پروژه و همچنین مدت استفاده از هر مهارت مهم است.

 

اجازه دهید ابتدا موجودیت های لازم را شناسایی کنیم:

• کارمند
• شرکت
• موقعیت
• پروژه
• مهارت

شرکت و کارمند رابطه‌ای بین چند به چند دارند، زیرا یک کارمند می‌تواند برای شرکت‌های مختلف کار کند و شرکت‌ها کارمندان زیادی دارند.

SQL Database

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

موجودیت «پروژه» از منطق یکسانی پیروی می کند: پروژه با همه موجودیت های ذکر شده در بالا به عنوان چند به چند مرتبط است.

برای سادگی، اجازه دهید بگوییم که یک کارمند از یک مجموعه مهارت در یک پروژه استفاده می کند. سپس، رابطه بین پروژه و مهارت نیز بسیار به چند است.

با توجه به اهمیت تعیین مدت زمان استخدام کارمند در این یا آن شرکت، در یک موقعیت خاص و در یک پروژه خاص، طرح پایگاه داده ما ممکن است دارای نمودار ER زیر باشد:

 

SQL Database

در اینجا، جدول JobHistory موجودیت تاریخچه شغلی هر کارمند را نشان می دهد، یعنی همان رزومه ای که رابطه چند به چند بین کارمند، شرکت، موقعیت ها و پروژه را اجرا می کند.

پروژه و مهارت به عنوان چند به چند مرتبط هستند، بنابراین با کمک نهاد ProjectSkill به هم مرتبط می شوند.

اگر رابطه بین سوژه ها و سوژه ها و اشیاء، یعنی هنجارهای طراحی پایگاه داده را درک می کنید، می توانید schema ای مشابه را روی یک تکه کاغذ در کمتر از یک ساعت ایجاد کنید.

در اینجا، اگر مهارت را در نهاد پروژه از طریق داده های نیمه ساختاریافته (NoSQL) به شکل XML، JSON قرار دهیم، یا به سادگی نام مهارت ها را با نقطه ویرگول فهرست کنیم، می توانیم schema و افزودن داده ها را ساده کنیم. اما این امر انتخاب گروه بندی بر اساس مهارت ها و فیلتر کردن بر اساس مهارت های خاص را دشوار می کند.

 

نحوه تعریف ساختار جدول

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

برای تعریف ساختار جدول، یک جدول ایجاد کنید یا یک جدول موجود را از Database Explorer باز کنید. پنج تب در ویرایشگر جدول وجود دارد: اصلی، محدودیت ها، فهرست ها، ذخیره سازی و داده ها.

 

تعریف ستون ها

برای تعریف ستون‌ها یا تغییر نام جدول، به تب ستون‌ها در ویرایشگر جدول بروید.

همچنین برای افزودن یک ستون به جدول، روی شبکه کلیک راست کرده و سپس در منوی میانبر ستون New را کلیک کنید. یا کلید INSERT را فشار دهید.

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

برای حذف یک ستون، روی آن راست کلیک کرده و از منوی میانبر Remove column را انتخاب کنید.

 

 

تعریف محدودیت ها

برای افزودن یا تغییر کلیدهای خارجی و اصلی، به تب Constraints در ویرایشگر جدول بروید.

همچنین برای افزودن یک کلید به جدول، روی برگه کلیک راست کرده و سپس روی Add Foreign Key یا Add Primary Key در منوی میانبر کلیک کنید، سپس نوع کلیدی را که می خواهید ایجاد کنید انتخاب کنید.

برای ویرایش یک کلید موجود، روی ردیف آن دوبار کلیک کنید.

برای حذف یک کلید، روی آن راست کلیک کرده و از منوی میانبر Remove constraint را انتخاب کنید.

 

 

تعریف شاخص ها

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

همچنبن برای افزودن فهرست به جدول، روی برگه کلیک راست کرده و از منوی میانبر نوع فهرست مورد نیاز را انتخاب کنید. راه دیگر این است که کلید INSERT را فشار دهید.

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

برای حذف ایندکس، روی آن کلیک راست کرده و سپس در منوی میانبر روی Remove Index کلیک کنید.

 

 

نتیجه

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

 

سپاسگذاریم از وقتی که برای خواندن این مقاله گذاشتید

.

برای خرید لایسنس نرم افزار Power BI کلیک کنید

.

برای مشاهده ویدیوهای آموزشی داده کاوی و هوش تجاری ما را در شبکه های اجتماعی دنبال کنید

Youtube Chanel :VISTA Data Mining

Aparat Chanel: VISTA Data Mining

Instagram Chanel: VISTA Data Mining

Telegram Chanel: VISTA Data Mining