۲۶ بهمن ۱۴۰۳

جامعیت در مدل رابطه‌ای در پایگاه داده

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

۱. جامعیت موجودیت (Entity Integrity)

  • این نوع جامعیت تضمین می‌کند که هر سطر (رکورد) در یک جدول منحصر به فرد باشد و هیچ سطری تکراری وجود نداشته باشد.
  • برای تحقق این امر، از کلید اصلی (Primary Key) استفاده می‌شود. کلید اصلی یک ستون یا مجموعه‌ای از ستون‌ها است که مقدار آن‌ها در هر سطر منحصر به فرد و غیر تکراری است.
  • کلید اصلی نمی‌تواند NULL باشد، زیرا NULL به معنای ناشناخته بودن مقدار است و نمی‌تواند یک سطر را به طور منحصر به فرد شناسایی کند.

۲. جامعیت ارجاعی (Referential Integrity)

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

۳. جامعیت حوزه (Domain Integrity)

  • این نوع جامعیت تضمین می‌کند که مقادیر ذخیره‌شده در هر ستون از یک جدول، مطابق با نوع داده‌ای تعریف‌شده برای آن ستون باشند.
  • برای تحقق این امر، از محدودیت‌هایی مانند نوع داده (Data Type)، محدودیت‌های CHECK، مقدار پیش‌فرض (Default Value) و محدودیت NOT NULL استفاده می‌شود.
  • به عنوان مثال، اگر ستونی برای ذخیره سن افراد تعریف شده باشد، باید مقادیر آن عددی و مثبت باشند.

۴. جامعیت کاربر-تعریف‌شده (User-Defined Integrity)

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

مثال‌هایی از اعمال جامعیت در پایگاه داده:

  • کلید اصلی: در جدول Students، ستون StudentID به عنوان کلید اصلی تعریف می‌شود تا هر دانشجو یک شناسه منحصر به فرد داشته باشد.
  • کلید خارجی: در جدول Enrollments، ستون StudentID به عنوان کلید خارجی به جدول Students اشاره می‌کند تا مطمئن شویم که هر ثبت‌نام مربوط به یک دانشجوی معتبر است.
  • محدودیت CHECK: در جدول Employees، ستون Age ممکن است یک محدودیت CHECK داشته باشد که مقدار آن باید بین ۱۸ تا ۶۵ باشد.

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

قواعد عام در مدل رابطه‌ای

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

۱. قاعده جامعیت موجودیت (Entity Integrity Rule)

  • هر جدول در پایگاه داده باید یک کلید اصلی (Primary Key) داشته باشد.
  • کلید اصلی باید منحصر به فرد باشد و هیچ‌یک از مقادیر آن نمی‌تواند NULL باشد.
  • این قاعده تضمین می‌کند که هر سطر در جدول به‌طور منحصر به فرد شناسایی شود.

۲. قاعده جامعیت ارجاعی (Referential Integrity Rule)

  • اگر جدولی شامل یک کلید خارجی (Foreign Key) باشد، مقدار آن کلید خارجی باید یا به یک مقدار معتبر در کلید اصلی جدول مرتبط اشاره کند یا NULL باشد.
  • این قاعده تضمین می‌کند که روابط بین جداول معتبر و سازگار باقی بمانند.
  • برای حفظ این قاعده، عملیات‌هایی مانند حذف یا به‌روزرسانی سطرها در جدول اصلی باید با دقت مدیریت شوند (مثلاً با استفاده از CASCADE یا RESTRICT).

۳. قاعده جامعیت حوزه (Domain Integrity Rule)

  • هر ستون در یک جدول باید مقادیری از یک حوزه (Domain) معین را بپذیرد. حوزه به نوع داده‌ای که ستون می‌تواند ذخیره کند (مانند عدد، رشته، تاریخ و غیره) و محدودیت‌های آن (مانند محدوده‌های مجاز) اشاره دارد.
  • این قاعده تضمین می‌کند که داده‌های ذخیره‌شده در هر ستون معتبر و مطابق با انتظارات باشند.

۴. قاعده عدم تکرار داده‌ها (No Duplicate Rows Rule)

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

۵. قاعده وابستگی تابعی (Functional Dependency Rule)

  • هر مقدار در یک ستون غیرکلید باید به‌طور کامل به کلید اصلی وابسته باشد. به عبارت دیگر، مقدار هر ستون غیرکلید باید توسط کلید اصلی به‌طور منحصر به فرد تعیین شود.
  • این قاعده پایه‌ای برای نرمال‌سازی (Normalization) است و از افزونگی داده‌ها جلوگیری می‌کند.

۶. قاعده نرمال‌سازی (Normalization Rules)

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

۷. قاعده عملیات‌های پایه‌ای (Basic Operations Rule)

  • در مدل رابطه‌ای، چهار عمل اصلی وجود دارد که به‌عنوان CRUD شناخته می‌شوند:
  1. ایجاد (Create): افزودن سطرهای جدید به جدول.
  2. خواندن (Read): بازیابی داده‌ها از جدول.
  3. به‌روزرسانی (Update): تغییر داده‌های موجود در جدول.
  4. حذف (Delete): حذف سطرها از جدول.
  • این عملیات باید مطابق با قواعد جامعیت موجودیت و ارجاعی انجام شوند.

۸. قاعده استقلال داده‌ها (Data Independence Rule)

  • مدل رابطه‌ای از دو نوع استقلال پشتیبانی می‌کند:
  1. استقلال فیزیکی (Physical Independence): تغییرات در ساختار فیزیکی پایگاه داده (مانند ذخیره‌سازی) نباید بر ساختار منطقی آن تأثیر بگذارد.
  2. استقلال منطقی (Logical Independence): تغییرات در ساختار منطقی پایگاه داده (مانند افزودن یا حذف جداول) نباید بر برنامه‌های کاربردی تأثیر بگذارد.

۹. قاعده انسداد NULL (Null Values Rule)

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

۱۰. قاعده یکپارچگی معنایی (Semantic Integrity Rule)

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

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

قواعد خاص در مدل رابطه‌ای

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

۱. قواعد کسب‌وکار (Business Rules)

  • این قواعد بر اساس نیازهای خاص یک سازمان یا کسب‌وکار تعریف می‌شوند و ممکن است شامل محدودیت‌ها یا شرایط خاصی باشند که در قواعد عام پوشش داده نمی‌شوند.
  • مثال:
  • در یک سیستم بانکی، ممکن است قاعده‌ای تعریف شود که مانده حساب یک مشتری نمی‌تواند منفی شود.
  • در یک سیستم فروش، ممکن است قاعده‌ای تعریف شود که تخفیف‌ها فقط برای خریدهای بالای ۱۰۰ دلار اعمال شوند.

۲. محدودیت‌های CHECK (CHECK Constraints)

  • این محدودیت‌ها برای اعمال شرایط خاص روی مقادیر ستون‌ها استفاده می‌شوند.
  • مثال:
  • در جدول Employees، ممکن است محدودیتی تعریف شود که سن کارمندان باید بین ۱۸ تا ۶۵ سال باشد:
    sql ALTER TABLE Employees ADD CONSTRAINT CHK_Age CHECK (Age >= 18 AND Age <= 65);
  • در جدول Orders، ممکن است محدودیتی تعریف شود که مقدار ستون Quantity باید بزرگ‌تر از ۰ باشد.

۳. قواعد وابستگی (Dependency Rules)

  • این قواعد برای تعیین وابستگی‌های خاص بین داده‌ها استفاده می‌شوند. به عنوان مثال، ممکن است قاعده‌ای تعریف شود که اگر یک سطر در جدول Orders حذف شود، سطرهای مرتبط در جدول OrderDetails نیز باید حذف شوند.
  • این قواعد معمولاً با استفاده از کلیدهای خارجی (Foreign Keys) و عملیات‌هایی مانند CASCADE اعمال می‌شوند:
  ALTER TABLE OrderDetails
  ADD CONSTRAINT FK_Order
  FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
  ON DELETE CASCADE;

۴. قواعد محاسباتی (Computed Columns Rules)

  • در برخی موارد، ممکن است نیاز باشد که مقدار یک ستون بر اساس مقادیر سایر ستون‌ها محاسبه شود. این قواعد با استفاده از ستون‌های محاسباتی (Computed Columns) اعمال می‌شوند.
  • مثال:
  • در جدول Products، ممکن است ستونی به نام TotalPrice تعریف شود که مقدار آن برابر با Quantity * UnitPrice باشد:
    sql ALTER TABLE Products ADD TotalPrice AS (Quantity * UnitPrice);

۵. قواعد زمان‌بندی (Temporal Rules)

  • این قواعد برای مدیریت داده‌های زمانی (Temporal Data) استفاده می‌شوند، مانند داده‌هایی که دارای تاریخ شروع و پایان هستند.
  • مثال:
  • در جدول EmployeePositions، ممکن است قاعده‌ای تعریف شود که تاریخ پایان یک موقعیت شغلی نمی‌تواند قبل از تاریخ شروع آن باشد:
    sql ALTER TABLE EmployeePositions ADD CONSTRAINT CHK_Dates CHECK (EndDate >= StartDate);

۶. قواعد سلسله‌مراتبی (Hierarchical Rules)

  • در برخی موارد، داده‌ها دارای ساختار سلسله‌مراتبی هستند (مانند ساختار سازمانی یا درخت‌های فهرست). این قواعد برای مدیریت چنین داده‌هایی استفاده می‌شوند.
  • مثال:
  • در جدول Employees، ممکن است قاعده‌ای تعریف شود که مدیر یک کارمند باید خودش نیز یک کارمند باشد:
    sql ALTER TABLE Employees ADD CONSTRAINT FK_Manager FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID);

۷. قواعد امنیتی (Security Rules)

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

۸. قواعد یکپارچگی معنایی (Semantic Integrity Rules)

  • این قواعد برای اطمینان از معتبر بودن داده‌ها از نظر معنایی استفاده می‌شوند.
  • مثال:
  • در جدول Students، ممکن است قاعده‌ای تعریف شود که تاریخ تولد دانش‌آموزان نمی‌تواند در آینده باشد.
  • در جدول Products، ممکن است قاعده‌ای تعریف شود که قیمت محصولات نمی‌تواند منفی باشد.

۹. قواعد رویداد-محور (Event-Driven Rules)

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

۱۰. قواعد چندزبانی (Multilingual Rules)

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

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

محدودیت رابطه‌ای

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

  1. محدودیت‌های ساختاری (Structural Constraints): این محدودیت‌ها به ساختار و طراحی پایگاه داده مربوط می‌شوند.
  2. محدودیت‌های عملیاتی (Operational Constraints): این محدودیت‌ها به عملیات‌هایی مانند درج، به‌روزرسانی و حذف داده‌ها مربوط می‌شوند.

در ادامه، مهم‌ترین محدودیت‌های رابطه‌ای توضیح داده شده‌اند:

۱. محدودیت کلید اصلی (Primary Key Constraint)

  • هر جدول باید یک کلید اصلی (Primary Key) داشته باشد که هر سطر را به‌طور منحصر به فرد شناسایی کند.
  • کلید اصلی نمی‌تواند NULL باشد و مقادیر آن باید منحصر به فرد باشند.
  • مثال:
  CREATE TABLE Students (
      StudentID INT PRIMARY KEY,
      Name VARCHAR(50)
  );

۲. محدودیت کلید خارجی (Foreign Key Constraint)

  • این محدودیت برای ایجاد رابطه بین دو جدول استفاده می‌شود. یک کلید خارجی (Foreign Key) در یک جدول به کلید اصلی جدول دیگر اشاره می‌کند.
  • این محدودیت جامعیت ارجاعی (Referential Integrity) را تضمین می‌کند.
  • مثال:
  CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      ProductID INT,
      FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
  );

۳. محدودیت منحصر به فرد (Unique Constraint)

  • این محدودیت تضمین می‌کند که مقادیر یک ستون یا مجموعه‌ای از ستون‌ها در یک جدول منحصر به فرد باشند.
  • برخلاف کلید اصلی، این محدودیت اجازه می‌دهد که ستون‌ها شامل مقدار NULL باشند (البته فقط یک بار).
  • مثال:
  CREATE TABLE Employees (
      EmployeeID INT PRIMARY KEY,
      Email VARCHAR(100) UNIQUE
  );

۴. محدودیت NOT NULL (NOT NULL Constraint)

  • این محدودیت تضمین می‌کند که یک ستون نمی‌تواند مقدار NULL داشته باشد.
  • مثال:
  CREATE TABLE Customers (
      CustomerID INT PRIMARY KEY,
      Name VARCHAR(50) NOT NULL
  );

۵. محدودیت CHECK (CHECK Constraint)

  • این محدودیت برای اعمال شرایط خاص روی مقادیر ستون‌ها استفاده می‌شود.
  • مثال:
  CREATE TABLE Employees (
      EmployeeID INT PRIMARY KEY,
      Age INT CHECK (Age >= 18)
  );

۶. محدودیت DEFAULT (DEFAULT Constraint)

  • این محدودیت یک مقدار پیش‌فرض برای یک ستون تعیین می‌کند. اگر کاربر مقداری وارد نکند، این مقدار پیش‌فرض استفاده می‌شود.
  • مثال:
  CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      OrderDate DATE DEFAULT GETDATE()
  );

۷. محدودیت‌های وابستگی (Dependency Constraints)

  • این محدودیت‌ها برای مدیریت وابستگی‌های بین داده‌ها استفاده می‌شوند. به عنوان مثال، اگر سطری از جدول اصلی حذف شود، سطرهای مرتبط در جدول دیگر نیز باید حذف شوند یا به‌روزرسانی شوند.
  • این محدودیت‌ها معمولاً با استفاده از کلیدهای خارجی و عملیات‌هایی مانند CASCADE اعمال می‌شوند.
  • مثال:
  CREATE TABLE OrderDetails (
      OrderID INT,
      ProductID INT,
      FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
      ON DELETE CASCADE
  );

۸. محدودیت‌های حوزه (Domain Constraints)

  • این محدودیت‌ها برای تعیین نوع داده‌های مجاز در یک ستون استفاده می‌شوند. به عنوان مثال، یک ستون می‌تواند فقط مقادیر عددی، رشته‌ای یا تاریخی را بپذیرد.
  • مثال:
  CREATE TABLE Products (
      ProductID INT PRIMARY KEY,
      ProductName VARCHAR(100),
      Price DECIMAL(10, 2)
  );

۹. محدودیت‌های کاربر-تعریف‌شده (User-Defined Constraints)

  • این محدودیت‌ها توسط کاربر یا طراح پایگاه داده تعریف می‌شوند و ممکن است شامل قواعد خاصی باشند که در محدودیت‌های پیش‌فرض پوشش داده نمی‌شوند.
  • مثال:
  CREATE TABLE Employees (
      EmployeeID INT PRIMARY KEY,
      Salary DECIMAL(10, 2),
      CONSTRAINT CHK_Salary CHECK (Salary >= 1000 AND Salary &lt;= 10000)
  );

۱۰. محدودیت‌های زمان‌بندی (Temporal Constraints)

  • این محدودیت‌ها برای مدیریت داده‌های زمانی استفاده می‌شوند، مانند تاریخ شروع و پایان یک رویداد.
  • مثال:
  CREATE TABLE Projects (
      ProjectID INT PRIMARY KEY,
      StartDate DATE,
      EndDate DATE,
      CONSTRAINT CHK_Dates CHECK (EndDate >= StartDate)
  );

۱۱. محدودیت‌های امنیتی (Security Constraints)

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

۱۲. محدودیت‌های یکپارچگی معنایی (Semantic Integrity Constraints)

  • این محدودیت‌ها برای اطمینان از معتبر بودن داده‌ها از نظر معنایی استفاده می‌شوند.
  • مثال:
  • ممکن است قاعده‌ای تعریف شود که تاریخ تولد یک فرد نمی‌تواند در آینده باشد.

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

اظهار یا Assertion

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

هدف Assertion در پایگاه‌داده:

  • حفظ یکپارچگی داده‌ها: اطمینان از اینکه داده‌ها همیشه در حالت معتبر و مطابق با قوانین تعریف‌شده باقی می‌مانند.
  • اعمال قوانین پیچیده: ایجاد محدودیت‌هایی که با استفاده از کلیدهای اصلی (Primary Keys)، کلیدهای خارجی (Foreign Keys) یا محدودیت‌های ساده (CHECK Constraints) قابل پیاده‌سازی نیستند.

مثال‌هایی از Assertion:

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

نحوه تعریف Assertion در SQL:

در استاندارد SQL، assertion با استفاده از دستور CREATE ASSERTION تعریف می‌شود. با این حال، بسیاری از سیستم‌های مدیریت پایگاه‌داده (مانند MySQL یا PostgreSQL) به طور مستقیم از این دستور پشتیبانی نمی‌کنند. در عوض، می‌توان از تریگرها (Triggers) یا محدودیت‌های CHECK برای پیاده‌سازی این مفهوم استفاده کرد.

مثال با CREATE ASSERTION (فرضی):

CREATE ASSERTION balance_non_negative
CHECK (
    NOT EXISTS (
        SELECT * FROM accounts
        WHERE balance &lt; 0
    )
);

این assertion بررسی می‌کند که موجودی هیچ حسابی منفی نباشد.

مثال با Trigger (در PostgreSQL):

CREATE OR REPLACE FUNCTION check_balance() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.balance &lt; 0 THEN
        RAISE EXCEPTION 'موجودی حساب نمی‌تواند منفی باشد';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_balance
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION check_balance();

مثال با CHECK Constraint:

ALTER TABLE accounts
ADD CONSTRAINT balance_non_negative
CHECK (balance >= 0);

تفاوت Assertion با سایر محدودیت‌ها:

  1. محدودیت‌های CHECK: فقط به یک سطر یا ستون خاص اعمال می‌شوند.
  2. کلیدهای اصلی و خارجی: برای اعمال یکپارچگی رابطه‌ای استفاده می‌شوند.
  3. Assertion: می‌تواند به چندین جدول و سطر اعمال شود و محدودیت‌های پیچیده‌تری را تعریف کند.

محدودیت‌های Assertion:

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

در نتیجه، assertion یک ابزار قدرتمند برای حفظ یکپارچگی داده‌ها است، اما باید با توجه به محدودیت‌های سیستم‌های پایگاه‌داده از آن استفاده کرد.

Trigger چیست؟

Trigger (تریگر یا محرک) در پایگاه‌داده‌ها یک روال یا کد برنامه‌نویسی است که به طور خودکار در پاسخ به رویدادهای خاصی مانند درج (INSERT)، به‌روزرسانی (UPDATE) یا حذف (DELETE) در یک جدول اجرا می‌شود. تریگرها برای اعمال قوانین تجاری، حفظ یکپارچگی داده‌ها یا انجام عملیات خاص پس از تغییرات در داده‌ها استفاده می‌شوند.

انواع تریگرها:

  1. بر اساس زمان اجرا:
  • BEFORE Trigger: قبل از اجرای عملیات (مانند قبل از درج یا به‌روزرسانی) اجرا می‌شود.
  • AFTER Trigger: پس از اجرای عملیات (مانند پس از درج یا به‌روزرسانی) اجرا می‌شود.
  • INSTEAD OF Trigger: به جای عملیات اصلی اجرا می‌شود (معمولاً در دیدگاه‌ها یا views استفاده می‌شود).
  1. بر اساس رویداد:
  • INSERT Trigger: هنگام درج داده‌های جدید در جدول فعال می‌شود.
  • UPDATE Trigger: هنگام به‌روزرسانی داده‌های موجود در جدول فعال می‌شود.
  • DELETE Trigger: هنگام حذف داده‌ها از جدول فعال می‌شود.

کاربردهای تریگرها:

  1. حفظ یکپارچگی داده‌ها:
  • اعمال قوانین تجاری پیچیده‌تر از محدودیت‌های ساده (مانند CHECK یا FOREIGN KEY).
  • مثال: اطمینان از اینکه موجودی انبار پس از هر فروش به‌روزرسانی می‌شود.
  1. ثبت تغییرات:
  • ایجاد لاگ یا تاریخچه تغییرات در یک جدول جداگانه.
  • مثال: ثبت تمام به‌روزرسانی‌های انجام‌شده روی یک جدول.
  1. محاسبات خودکار:
  • انجام محاسبات یا به‌روزرسانی‌های خودکار پس از تغییرات.
  • مثال: محاسبه مجدد میانگین نمرات پس از درج نمره جدید.
  1. اعمال محدودیت‌های پیچیده:
  • مثال: جلوگیری از حذف رکوردهایی که در جدول دیگری به آن‌ها ارجاع داده شده است.

ساختار تریگر در SQL:

تریگرها معمولاً با استفاده از دستور CREATE TRIGGER تعریف می‌شوند. ساختار کلی آن به این صورت است:

CREATE TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE]
ON table_name
[FOR EACH ROW | FOR EACH STATEMENT]
BEGIN
    -- کد اجرایی تریگر
END;
  • trigger_name: نام تریگر.
  • BEFORE/AFTER/INSTEAD OF: زمان اجرای تریگر.
  • INSERT/UPDATE/DELETE: رویداد مرتبط با تریگر.
  • table_name: جدولی که تریگر روی آن اعمال می‌شود.
  • FOR EACH ROW: تریگر برای هر سطر تغییر یافته اجرا می‌شود.
  • FOR EACH STATEMENT: تریگر یک بار برای هر دستور SQL اجرا می‌شود.

مثال‌های کاربردی:

1. تریگر برای ثبت تغییرات (لاگ‌گیری):

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    amount DECIMAL(10, 2)
);

CREATE TABLE order_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    old_amount DECIMAL(10, 2),
    new_amount DECIMAL(10, 2),
    change_date TIMESTAMP
);

CREATE TRIGGER after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, old_amount, new_amount, change_date)
    VALUES (OLD.order_id, OLD.amount, NEW.amount, NOW());
END;

2. تریگر برای اعمال محدودیت:

CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.amount &lt; 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'مبلغ نمی‌تواند منفی باشد';
    END IF;
END;

3. تریگر برای به‌روزرسانی خودکار:

CREATE TRIGGER after_sale_insert
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE inventory
    SET quantity = quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
END;

مزایای تریگرها:

  1. اتوماسیون: انجام خودکار عملیات بدون نیاز به دخالت کاربر.
  2. یکپارچگی داده‌ها: اعمال قوانین تجاری و محدودیت‌های پیچیده.
  3. لاگ‌گیری: ثبت تغییرات و تاریخچه داده‌ها.

معایب تریگرها:

  1. پیچیدگی: اشکال‌زدایی و نگهداری تریگرها می‌تواند دشوار باشد.
  2. تأثیر بر عملکرد: تریگرها می‌توانند عملکرد پایگاه‌داده را کاهش دهند، به‌ویژه اگر روی عملیات‌های حجیم اعمال شوند.
  3. عدم شفافیت: ممکن است رفتار برنامه را غیرقابل پیش‌بینی کنند، زیرا به طور خودکار اجرا می‌شوند.

جمع‌بندی:

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

دسته‌بندی‌ها: پایگاه داده

دیدگاه شما

نشانی ایمیل شما منتشر نخواهد شد.