نرمال‌سازی پایگاه‌داده با مثال: 1NF، 2NF، 3NF و BCNF

نرمال‌سازی روشی برای طراحی جدول‌هاست که تکرار داده را حذف می‌کند و از خطاهای درج، حذف و به‌روزرسانی جلوگیری می‌کند. در درس پایگاه‌داده تقریباً همیشه سؤال «این جدول را تا 3NF نرمال کن» وجود دارد. در این مقاله با یک مثال واحد از جدول بی‌نظم شروع می‌کنیم و قدم‌به‌قدم می‌رسیم به 1NF، 2NF، 3NF و BCNF.

جدول اولیه و مشکلاتش

فرض کن همه‌چیز را در یک جدول ثبت‌نام نگه داشته‌ایم:

این جدول سه ناهنجاری (Anomaly) دارد:

• ناهنجاری به‌روزرسانی: اگر شماره‌ی دکتر راد عوض شود، باید در همه‌ی ردیف‌ها عوض شود؛ فراموشی یک ردیف یعنی داده‌ی ناسازگار.
• ناهنجاری درج: نمی‌توانیم درس جدیدی را بدون دانشجو ثبت کنیم، چون ردیف بدون StudentId معنا ندارد.
• ناهنجاری حذف: اگر تنها دانشجوی یک درس حذف شود، اطلاعات خود درس و استاد هم از بین می‌رود.

وابستگی تابعی چیست؟

وابستگی تابعی A → B یعنی با دانستن A، مقدار B یکتا مشخص می‌شود. در جدول بالا:

• StudentId → StudentName
• CourseId → CourseTitle, TeacherName
• TeacherName → TeacherPhone
• (StudentId, CourseId) → Grade

کلید این جدول ترکیب (StudentId, CourseId) است، چون با همین دو مقدار هر ردیف یکتا می‌شود. همه‌ی مراحل نرمال‌سازی با بررسی همین وابستگی‌ها انجام می‌شود.

فرم نرمال اول (1NF)

اگر ستونی مثل Phones = '0911, 0935' داشتیم، آن را به ردیف‌های جدا یا جدول جدا تبدیل می‌کنیم. جدول بالا از نظر اتمیک بودن مقادیر مشکلی ندارد، پس در 1NF است؛ اما هنوز ناهنجاری دارد.

فرم نرمال دوم (2NF)

در مثال ما StudentName فقط به StudentId وابسته است و CourseTitle فقط به CourseId؛ هر دو وابستگی جزئی‌اند. راه‌حل: جدول را بر اساس کلیدها می‌شکنیم:

-- Student: وابسته به StudentId
CREATE TABLE Student (
  StudentId   INT PRIMARY KEY,
  StudentName NVARCHAR(100) NOT NULL
);

-- Course: وابسته به CourseId
CREATE TABLE Course (
  CourseId     CHAR(2) PRIMARY KEY,
  CourseTitle  NVARCHAR(100) NOT NULL,
  TeacherName  NVARCHAR(100) NOT NULL,
  TeacherPhone VARCHAR(20)   NOT NULL
);

-- Enrollment: وابسته به کل کلید ترکیبی
CREATE TABLE Enrollment (
  StudentId INT     NOT NULL REFERENCES Student(StudentId),
  CourseId  CHAR(2) NOT NULL REFERENCES Course(CourseId),
  Grade     DECIMAL(4,2) NULL,
  PRIMARY KEY (StudentId, CourseId)
);

حالا جدول‌ها در 2NF هستند و ناهنجاری درج و حذف برای دانشجو و درس برطرف شده است.

فرم نرمال سوم (3NF)

در جدول Course داریم CourseId → TeacherName و TeacherName → TeacherPhone؛ یعنی TeacherPhone به‌صورت انتقالی (از طریق TeacherName) به کلید وابسته است. اگر یک استاد چند درس داشته باشد، شماره‌اش تکرار می‌شود. استاد را جدا می‌کنیم:

CREATE TABLE Teacher (
  TeacherId INT PRIMARY KEY,
  TeacherName  NVARCHAR(100) NOT NULL,
  TeacherPhone VARCHAR(20)   NOT NULL
);

CREATE TABLE Course (
  CourseId    CHAR(2) PRIMARY KEY,
  CourseTitle NVARCHAR(100) NOT NULL,
  TeacherId   INT NOT NULL REFERENCES Teacher(TeacherId)
);

اکنون هر حقیقت دقیقاً یک‌جا ذخیره می‌شود: مشخصات دانشجو در Student، مشخصات استاد در Teacher، درس در Course و نمره در Enrollment.

فرم نرمال بویس-کاد (BCNF)

بیشتر جدول‌هایی که 3NF هستند BCNF هم هستند؛ تفاوت فقط در حالت‌های خاص رخ می‌دهد. مثال کلاسیک: جدول Tutoring(Student, Subject, Teacher) که در آن هر استاد فقط یک موضوع را تدریس می‌کند (Teacher → Subject) و هر دانشجو برای هر موضوع یک استاد دارد.

کلیدهای کاندید (Student, Subject) و (Student, Teacher) هستند. وابستگی Teacher → Subject سمت چپش ابرکلید نیست، پس BCNF نقض شده (اما چون Subject جزو یک کلید است، 3NF رعایت شده). راه‌حل تجزیه است:

CREATE TABLE TeacherSubject (
  Teacher NVARCHAR(50) PRIMARY KEY,  -- Teacher → Subject
  Subject NVARCHAR(50) NOT NULL
);

CREATE TABLE StudentTeacher (
  Student NVARCHAR(50) NOT NULL,
  Teacher NVARCHAR(50) NOT NULL REFERENCES TeacherSubject(Teacher),
  PRIMARY KEY (Student, Teacher)
);

جدول مقایسه‌ی فرم‌های نرمال

چه وقت نرمال‌سازی را کنار می‌گذاریم؟

نرمال‌سازی هزینه هم دارد: JOINهای بیشتر. در گزارش‌های سنگین و انبار داده گاهی عمداً داده را تکرار می‌کنند (Denormalization) تا خواندن سریع‌تر شود. قاعده‌ی خوب این است: اول نرمال طراحی کن، بعد فقط با اندازه‌گیری و دلیل مشخص denormalize کن.

برای تمرین، درس نرمال‌سازی را ببین و جدول‌های خودت را در Playground بساز.