آموزش نرمال سازی در پایگاه داده — به زبان ساده + مثال

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

نرمال سازی پایگاه داده چیست؟

«نرمال‌سازی پایگاه داده» (Database Normalization) فرآیندی است که برای سازماندهی پایگاه داده به جداول و ستون‌ها استفاده می‌شود. نرمال‌سازی حاوی سه شکل اصلی است که با نام‌های فرم نرمال اول، فرم نرمال دوم و فرم نرمال سوم شناخته می‌شوند.

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

• شناسایی فروشندگان در سازمان خود ضروری است.
• تنظیم فهرستی از همه مشتریان مورد نظر برای فروش یک محصول امری مهم به حساب می‌آيد.
• تعیین اینکه کدام فروشنده با مشتریان خاص تماس می‌گیرد نیز اهمیت دارد.

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

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

دلایل نرمال‌سازی پایگاه داده کدامند؟

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

• به حداقل رساندن داده‌های تکراری
• اجتناب یا به حداقل رساندن مسائل مربوط به اصلاح داده‌ها
• ساده‌سازی پرس و جوها (کوئری‌ها)

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

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

• شناسایی فروشندگان سازمان
• فهرست کردن دفاتر فروش و شماره تلفن
• مرتبط کردن یک فروشنده با یک دفتر فروش
• نشان دادن مشتریان هر فروشنده

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

تکرار داده‌ها و ناهنجاری‌های اصلاح

همان‌طور که در جدول‌ فوق قابل مشاهده است، هر دو ستون SalesOffice و OfficeNumber فهرست شده‌‌اند. در چنین شرایطی، در داده‌های فروشنده (SalesPerson) تکرار وجود دارد. مسئله تکرار داده‌ها در پایگاه داده همراه با دو مشکل است که این موارد در ادامه فهرست شده‌اند:

• افزایش فضای ذخیره‌سازی مورد استفاده
• کاهش کارایی
• افزایش چالش و مشکلات در نگهداری تغییرات داده‌ها

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

• ناهنجاری درجی
• ناهنجاری به‌روزرسانی
• ناهنجاری حذف

در ادامه آموزش نرمال سازی در پایگاه داده به انواع ناهنجاری اصلاحی پرداخته خواهد شد.

ناهنجاری درجی

به منظور بررسی اولین نوع ناهنجاری اصلاحی، یعنی ناهنجاری درجی (Insert Anomaly)، باید به جدول زیر توجه شود:

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

ناهنجاری به‌روزرسانی

امکان رخ دادن «ناهنجاری به‌روزرسانی» (Update Anomaly) در شرایطی است که چندین ردیف اطلاعات یکسان وجود داشته باشد. به عنوان مثال، در صورتی که شماره دفتر مورد نظر تغییر کند، باید چندین به‌روزرسانی انجام شود. اگر همه این ردیف‌ها به‌روزرسانی نشوند، آنگاه ناسازگاری‌های مختلفی در پایگاه داده ظاهر خواهد شد.

ناهنجاری حذف

در شرایطی که حذف یک سطر از جدول پایگاه داده منجر به حذف بیش از یک مجموعه از داده‌ها شود، «ناهنجاری حذف» (Deletion Anomaly) به وجود می‌آید. به عنوان مثال، اگر مثلاً جان هانت (John Hunt)‌ بازنشسته شود، حذف آن سطر باعث می‌شود اطلاعات مربوط به دفتر نیویورک از دست بروند.

تسهیل مشکلات جستجو و مرتب‌سازی

به منظور ساده‌سازی دو عملیات جستجو و مرتب‌سازی روی داده‌های مورد نظر، نرمال سازی در پایگاه داده به عنوان یک رویکرد مهم و کمکی مورد استفاده قرار می‌گیرد. در صورتی که لازم باشد در جدول SalesStaff یک مشتری خاص مثل Ford جستجو شود، باید کوئری زیر را نوشت:

SELECT SalesOffice
FROM SalesStaff
WHERE Customer1 = ‘Ford’ OR
      Customer2 = ‘Ford’ OR
      Customer3 = ‘Ford’

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

مزایای نرمال سازی در پایگاه داده

برخی از شاخص‌ترین مزیت‌های نرمال سازی در پایگاه داده در ادامه فهرست شده‌‌اند:

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

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

معایب نرمال سازی در پایگاه داده

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

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

تا این بخش از آموزش نرمال سازی در پایگاه داده به برخی از تعاریف مقدماتی مربوط به نرمال سازی دیتابیس پراخته شد.

سطوح نرمال سازی پایگاه داده کدامند؟

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

• فرم نرمال اول (1NF)
• فرم نرمال دوم (2NF)
• فرم نرمال سوم (3NF)
• فرم نرمال بویس کد (فرم نرمال چهارم | BCNF | 4NF | Boyce Codd Normal)
• فرم نرمال پنجم (5NF)
• فرم عادی ششم (6NF)

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

آموزش نرمال سازی در پایگاه داده

در این بخش به آموزش نرمال سازی در پایگاه داده پرداخته شده است. این بخش به صورت ویژه دارای مثال نرمال سازی پایگاه داده و مفاهیم مربوطه است. همان‌طور که پیش‌تر به آن اشاره شد، سه شکل متداول برای نرمال‌سازی پایگاه داده وجود دارد. فرم نرمال اول، دوم و سوم به ترتیب به صورت 2NF ،1NF و 3NF مخفف شده‌اند. البته باید توجه کرد که چندین فرم اضافی دیگر مانند BCNF نیز وجود دارند. این‌ها فرم‌های نرمال‌سازی پیشرفته‌تری به حساب می‌آیند و برای یادگیری برای شروع به کار چندان ضروری نیستند.

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

• اولین فرم نرمال (First Normal Form): اطلاعات همراه با ستون حاوی مقادیر اتمی در یک جدول رابطه‌ای (Relational) ذخیره می‌شوند. در 1NF هیچ گروه ستونی تکراری وجود ندارد.
• دومین فرم نرمال (Second Normal Form): در صورتی که یک جدول در سطح دوم نرمال‌سازی شده باشد، یک جدول نرمال شده در سطح یک نیز به حساب می‌آید. در فرم 2NF، تمام ستون‌ها به کلید اصلی جدول بستگی دارند.
• سومین فرم نرمال (Third Normal Form): جدول در 2NF است و تمام ستون‌های آن به طور گذرا به کلید اصلی وابسته نیستند.

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

اهداف اصلی نرمال سازی در پایگاه داده چه هستند؟

اهداف اصلی نرمال سازی در پایگاه داده در ادامه فهرست شده‌اند.

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

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

مثال نرمال سازی پایگاه داده

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

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

• چندین «موضوع» (Topic) در یک جدول وجود دارند. به عنوان مثال، ستون‌های فروشندگان (SalesPerson)، دفاتر (Offices) و مشتریان (Customers) همگی موضوع‌های مختلف هستند.
• در جدول فوق، مشتریان به صورت ستونی تکرار می‌شوند.
• آدرس‌های مشتری درون فیلد متنی قرار دارد.

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

آموزش نرمال سازی در پایگاه داده : فرم نرمال اول

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

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

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

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

در فرم نرمال اول، جدول‌ها نمی‌توانند دارای ستون‌های فرعی باشند. به این معنی که امکان فهرست کردن چندین شهر و جداسازی آن‌ها با نقطه ویرگول در یک ستون وجود ندارد. وقتی یک مقدار اتمی است، نمی‌توان مقدار آن را بیش‌تر از حالت فعلی تقسیم کرد. به عنوان مثال، مقدار Chicago اتمی است. در حالی که «Chicago; Los Angeles; New York» اتمی به حساب نمی‌آید. مرتبط با این ضرورت، این مفهوم وجود دارد که یک جدول نباید دارای تعدادی گروه تکرار شونده از ستون‌ها مانند Customer2name ،Customer1Name و Customer3Name باشد. به منظور آشنایی با مفاهیم مربوط به کلید در پایگاه داده، می‌توان به مقاله زیر رجوع کرد.

• مقاله پیشنهادی: انواع کلید در پایگاه داده — به زبان ساده و با مثال

مثال نرمال سازی پایگاه داده در 1NF

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

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

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

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

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

جدول فوق در سطح اول یا همان 1NF نرمال است. در ادامه این مطلب، بهبودهای حاصل شده در فرم نرمال شرح داده شده‌اند.

بهبودهای فرم نرمال اول

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

• در طرح اصلی، هر ورودی SalesStaffInformation به سه مشتری محدود است. در حالی که در طراحی جدید، عملاً تعداد مشتریان مرتبط با هر طرح نامحدود به حساب می‌آید.
• مرتب‌سازی مشتریان که داده‌های اصلی هستند، تقریبا امری غیرممکن محسوب می‌شود. امکان استفاده از عبارت UNION وجود دارد، اما دست و پا گیر خواهد بود. اکنون، به واسطه آموزش نرمال سازی در پایگاه داده ، مرتب کردن مشتریان ساده می‌شود.
• نکته فوق برای انجام عمل فیلتر کردن روی جدول Customer نیز صدق می‌کند. فیلتر کردن روی یک ستون مربوط به نام مشتری بسیار ساده‌تر از سه ستون است.
• این طراحی، ناهنجاری‌های درج و حذف را برای جدول Customer از بین می‌برد. می‌توان بدون نیاز به حذف کل ردیف SalesStaffInformation، همه مشتریان یک فروشنده (SalesPerson) خاص را حذف کرد.

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

آموزش نرمال سازی در پایگاه داده : فرم نرمال دوم

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

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

مفهوم دومین سطح نرمال سازی در پایگاه داده

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

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

پیش‌تر در مورد نرمال‌سازی در سطح اول یا همان 1NF بحث شده است. اما مورد دوم به چه معنا است؟ در ادامه دومین نیازمندی لازم برای 2NF شرح داده می‌شود. کلید اصلی به عنوان یک ابزار کمکی در جهت شناسایی هر سطر جدول به طور انحصاری به کار می‌رود.

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

• اگر پاسخ به سوال فوق «بله» باشد، سپس ستون مورد نظر به کلید اصلی وابستگی و به جدول تعلق دارد.
• در صورتی که پاسخ به سوال مذکور «خیر» باشد، لازم است آن ستون به جدول دیگری منتقل شود.

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

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

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

با توجه به دلایل مذکور، دانستن شناسه مشتری لازم است. جدول Customer در سطح دوم نرمال سازی قرار ندارد؛ زیرا در این جدول ستون‌هایی وجود دارند که به طور کامل به کلید اصلی وابسته نیستند. در چنین شرایطی، باید این ستون‌ها به یک جدول دیگر منتقل شوند.

مشکل دوم این است که دو ستون در جدول SalesStaffInformation به EmployeeID وابسته نیستند. با وجود اینکه دو ستون SalesOffice و OfficeNumber برای شرح این هستند که SalesPerson در کدام دفتر مستقر است، اما برای توصیف کارمند کاربرد ندارند. به منظور درک بهتر، لازم است به تصویر زیر توجه شود. طرح‌های کلی این موضوعات با رنگ قرمز قابل مشاهده هستند.

حل مشکلات مدل و نرمال سازی در پایگاه داده در سطح دوم (2NF)

با در نظر داشتن اینکه ستون‌های مشخص شده با رنگ قرمز، کاملاً به کلید اصلی جدول وابسته نیستند، می‌توان آن‌ها را متعلق به جای دیگری دانست. در هر دو مورد، لازم است ستون‌ها به جدول‌های جدید منتقل شوند. در همین راستا، باید جدول SalesOffice برای SalesOffice و OfficeNumber ایجاد شوند. اکنون اضافه کردن یک کلید خارجی (Foreign Key) به SalesStaffInformaiton ضروری است. با کمک این کلید خارجی می‌توان شرح داد که فروشنده در کدام دفتر مستقر است.

تغییرات لازم برای تبدیل جدول Customer به یک جدول نرمال سازی شده در 2NF کمی پیچیده‌تر است. در واقع، به جای انتقال ستون‌های CustomerName ،CustomerCity و CustomerPostalCode به جدول جدید، EmployeeID مشکل اصلی به حساب می‌آید. به بیان ساده، سه ستون نام برده به این قسمت از کلید بستگی ندارند. در واقع، این جدول دارای دو هدف اصلی است که در ادامه فهرست شده‌اند:

• نشان دادن اینکه هر کارمند با چه مشتریانی تماس می‌گیرد تا فروش انجام دهد.
• یافتن مشتریان و موقعیت مکانی آن‌ها

در حال حاضر می‌توان EmployeeID را از جدول حذف کرد. پس از این کار، هدف جدول مشخص می‌شود. یعنی هدف واحد در جدول Customer، یافتن و توصیف هر یک از مشتریان است.

انتقال داده‌های نمونه به فرم 2NF

حال باید یک جدولی به نام SalesStaffCustomer ایجاد شود تا با استفاده از آن، بتوان شرح داد که فروشنده با چه مشتریانی تماس می‌گیرد. این جدول دارای دو ستون CustomerID و EmployeeID است. از ترکیب این دو ستون با یکدیگر کلید اصلی تشکیل می‌شود. علاوه بر این، این ستون‌ها به طور جداگانه، برای جدول‌های Customer و SalesStaffInformation کلیدهای خارجی به حساب می‌آیند. مدل داده‌‌ای که در ادامه قرار دارد در سطح دوم نرمال سازی است.

به منظور درک بهتر، لازم است به جدول زیر و داده‌‌های موجود در آن توجه شود. اکنون جدول Customer در سطح دوم نرمال سازی شده است.

فواید نرمال سازی پایگاه داده در سطح 2NF

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

اکنون امکان حذف همه فروشندگان و در عین حال، حفظ سوابق مشتریان وجود دارد. همچنین، بسته شدن تمام SalesOfficeهابه منزله این نیست که باید سوابق حاوی فروشندگان حذف شوند.

همان‌طور که در جدول بالا قابل مشاهده است، جدول SalesStaffCustomer جدولی است که ستون‌های آن همگی کلید هستند. معماران پایگاه داده به چنین جداولی، «جدول تقاطع» (Intersection Table) می‌گویند. جدول تقاطع زمانی مفید است که مدل‌سازی یک رابطه چند به چند (Many To Many) مد نظر باشد.

هر ستون یک کلید خارجی محسوب می‌شود. در صورتی که به مدل داده توجه شود، مشخص است که یک رابطه یک به چند (One To Many) میان این جدول و SalesStaffInformation و همچنین، Customer وجود دارد. در واقع جدول تقاطع SalesStaffCustomer این امکان را می‌دهد که دو جدول مورد نظر به یکدیگر متصل شوند.

این یک پایگاه داده برای تمام اهداف عملی قابل اجرا است. سه جدول از چهار جدول به فرم نرمال سوم هستند، اما هنوز یک جدول وجود دارد که دارای مشکلی جزئی است و همین مسئله مانع از 3NF بودن آن می‌شود.

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

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

آموزش نرمال سازی در پایگاه داده : فرم نرمال سوم

در این بخش از آموزش نرمال سازی در پایگاه داده به فرم سوم نرمال سازی پرداخته می‌شود.

برای اینکه یک جدول به نرمال سازی پایگاه داده در سطح سوم (3NF) برسد، لازم است دو مشخصه اصلی زیر را دارا باشد:

• در وهله اول، جدول مورد نظر باید 2NF باشد.
• جدول فقط شامل ستون‌هایی باشد که به طور غیر گذرا (Non-Transitively) به کلید اصلی وابسته هستند.

ممکن است این سوال پیش بیاید که منظور از وابستگی غیر گذرا چیست؟ در ادامه به این سوال پاسخ داده شده است. اما پیش ار درک مفهوم ناگذرایی، باید معنای تعدی (Transitive) مشخص شود.

وابستگی تعدی در نرمال سازی پایگاه داده چیست؟

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

در صورتی که سه ستون ملیت نویسنده (AuthorNationality)، نویسنده (Author) و کتاب (Book) در نظر گرفته شوند، مقادیر مربوط به ستون‌های AuthorNationality و Author به مقدارهای ستون کتاب وابستگی دارند. این یعنی، هنگامی که کتاب شناخته شود، امکان یافتن نویسنده یا ملیت نویسنده به وجود می‌‌آید. اما باید توجه کرد که ستون AuthorNationality به ستون Author وابسته است. یعنی وقتی نویسنده شناخته شود می‌توان ملیت او را نیز مشخص کرد. از این رو، ستون ملیت نویسنده از طریق ستون نویسنده به ستون مربوط به کتاب وابسته است. این وابستگی با نام وابستگی گذرا شناخته می‌شود.

می‌توان رابطه مذکور را به سه ستون B ، A و PK تعمیم داد. در صورتی که مقدار A به PK و B به PK وابسته باشند و A نیز به B وابستگی داشته باشد، می‌توان گفت A از طریق B به PK وابسته است. این یعنی A به طور گذرا به PK وابستگی دارد.

آموزش نرمال سازی در پایگاه داده : مثال از وابستگی تعدی

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

• با در نظر داشتن PersonID به عنوان کلید اصلی و دو ستون به ترتیب برای نام و نام خانوادگی، آیا وابستگی گذرا وجود دارد؟ پاسخ خیر است، زیرا معمولاً در اغلب فرهنگ‌ها نام خانوادگی یک شخص بر مبنای نام خانوادگی پدر او است، در حالی که نام آن شخص برای او انتخاب می‌شود.
• با در نظر داشتن PersonID و دو ستون وزن و جنسیت، آیا وابستگی گذرا وجود دارد؟ پاسخ این سوال خیر است. چون هیچ ارتباط مستقیمی بین وزن یک شخص و جنسیت او وجود ندارد.
• با در نظر داشتن شناسه خودرو، مدل خودرو و سازنده آن، آیا وابستگی گذرا وجود دارد؟ جواب این سوال بله است. به این دلیل که سازنده‌های خودرو مدل‌های مختلفی از آن‌ها را تولید می‌کنند. به عنوان مثال، Ford سازنده Fiesta است، در حالی که Toyota خودروی Camry را تولید می‌کند.
• در صورتی که ستون‌هایی برای شناسه فردی (PersonID)، شاخص توده بدن (BMI) و اضافه وزن افراد در نظر گرفته شده باشد، آیا وابستگی گذرا وجود دارد؟ پاسخ این سوال نیز بله است. چون طبق استانداردهای مورد نظر، افرادی اضافه وزن دارند که شاخص توده بدنی آن‌ها از ۲۵ بیش‌تر است. بنابراین، در شرایطی که BMI فرد کم‌تر از ۲۵ باشد، True بودن ستون مربوط به اضافه وزن بی‌معنی خواهد بود.

به این ترتیب، با توجه به مثال‌های فوق، غیر گذرا بودن به این معنی است که همه ستون‌ها تنها به کلید اصلی (معیار مهم برای دومین فرم نرمال سازی) وابسته‌اند و دیگر به هیچ ستون دیگری در جدول وابستگی ندارند.

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

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

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

حل مشکلات مدل و نرمال سازی در پایگاه داده در سطح سوم (3NF)

برای اینکه مدل مورد نظر در سطح سوم نرمال سازی قرار بگیرد، لازم است وابستگی‌های گذرا حذف شوند. همان‌طور که پیش‌تر به آن اشاره شد، ستون CustomerCity به ستون CustomerPostalCode وابستگی دارد و این ستون به CustomerID وابسته است. با وجود اینکه وابستگی CustomerPostalCode به CustomerID باعث بروز مشکل نمی‌شود، اما با گنجاندن CustomerCity در جدول، شرایط 3NF نقض می‌شوند. برای رفع این مشکل، باید جدول جدیدی به نام PostalCode ایجاد شود که در آن Code Postal به عنوان کلید اصلی و City به عنوان تنها ستون آن وجود دارند.

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

اکنون، هر یک از ستون‌های موجود در جدول Customer به کلید اصلی وابسته هستند. همچنین، ستون‌ها برای مقادیر خود به یکدیگر وابستگی ندارند و تنها وابستگی آن‌ها به کلید اصلی است.

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

جمع‌بندی

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

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