جامعیت داده ها (Data Integrity)

[محمد سلیم آبادی]

جامعیت داده ها (Data Integrity) تضمین می کند که داده هایی که در جداول ذخیر می شوند از صحت کامل بر خوردارند.


در SQL جامعیت داده ها را با کمک دو مکانیزم پیاده سازی و به اجرا در می آوریم:


1. Constraints
2. Trigger


یکی از دسته بندی هایی که در کتاب های علمی پایگاه داده ها برای جامعیت ذکر شده است به قرار زیر است:


1. Database Level Constraints
2. Table Level Constraints


قیود در سطح پایگاه داده ها را ما به سادگی نمی توانیم ایجاد کنیم. بطور مثال: جدول انتخاب دروس دانشجو را در نظر بگیرین، دانشجو نمی تواند در یک نیمسال بیش از 20 واحد اخذ کند (البته ما می دانیم برای افراد مشروطه محدودیت 14 واحد و برای معدل های A حد اکثر 24 منظور شده است).


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


1. Check Constraints Based On UDF
2. Trigger


و دسته بندی که در کتابهای Microsoft برای جامعیت آمده اند:


1. Declarative Data Integrity
2. Procedural Data Integrity


"جامعیت داده های رویه ای" با Stored Procedure و Trigger پیاده سازی می شوند.


قیود (Constraint) در SQL به موارد زیر خلاصه می شوند:


· Primary Key
· Foreign Key
· Null ability
· Check
· Unique


توجه کنید که قیودی مثل Unique و Primary Key و Null را با کمک CHECK بسادگی می توان پیاده سازی کرد. بطور مثال کد زیر دقیقا کار Not Null را انجام می دهد:

CHECK (Column_Name IS NOT NULL)


ادامه دارد...

[محمد سلیم آبادی]

ابتدا طریقه ی ایجاد قید بر روی ستون های جدول را نشان خواهم داد سپس به طراحی بانک دانشگاه خواهم پرداخت. هر قیدی دارای یک نام است که در طراحی جدول به شکل کد نویسی می توانین این نام را بدهیم (این عمل اختیاری است)
بطور مثال در جدول زیر یک ستون وجود دارد که قیدی با نام مشخص برای Null able کردن ستون مورد نظر ایجاد شده است.


CREATE TABLE My_Sample
(
Column_Name INT
CONSTRAINT CK_Nullable
CHECK (Column_Name IS NOT NULL)
)


البته برای سادگی بیشتر ما می توانیم مستقیما از دستور NOT NULL در مقابل ستون استفاده کنیم:



CREATE TABLE My_Sample
(
Column_Name INT
NOT NULL
)



برای طراحی کردن چند ستون در یک جدول از کاراکتر کاما برای تفکیک کردن ستون های جدول استفاده می کنیم. بطور مثال جدول زیر دارای دو ستون برای ذخیره نام و نام خانوادگی دانشجو در نظر گرفته شده است:


CREATE TABLE My_Sample
(
First_Name CHAR(20)
, Last_Name CHAR(20)
)



در قید چک (Check) ما باید یک عبارت بولین (Boolean Expression) درج کنیم. که حاصل این عبارت یا True باشد یا False.
فرض کنید می خواهیم نمره ی درس دانشجو را ذخیره کنیم. نمره از بازه ی 0 تا 20 است پس نباید مقادیر خارج از این محدوده ذخیره شود:


CREATE TABLE My_Sample
(
Mark
TINYINT
CHECK ( Mark BETWEEN 0 AND 20 )

)

و کد جنسیت فرد نباید اعدادی به غیر از 0 ، 1 و 2 باشد:


CREATE TABLE My_Sample
(
GenderID
CHAR(1)
CHECK ( GenderID IN(0, 1, 2) )


)


توجه کنید که در اینجا من نوع داده (Data Type) را از نوع کاراکتر در نظر گرفتم که فضای کمتری مصرف کرده و در ضمن نیازی ام نیست که به عنوان یک عدد در نظر گرفته شود.
توجه کنید که ترتیب نوشتن قیود یک ستون (نوع داده ها، Null ability، Check، Primary key و...) مهم نیستند یعنی اول Check را بنویسید و سپس نوع داده ی ستون را مشخص کنید.
ستون محاسباتی را به سادگی و با کمک AS ایجاد می کنیم، فرض کنید می خواهیم تاریخ تولد یک فرد را ذخیره کرده و در ستون دیگر به طور اتوماتیک سن آن را محاسبه و ذخیره کنیم:


CREATE TABLE My_Sample
(
DateOfBirth
DATE,
Age AS Difference(DateOfBirth, Getdate()),
)



و با کمک CASE می توانیم با بررسی ستون دیگر مقدار این ستون را بدست آوریم. فرض کنید جنسیت فرد را می خواهیم ذخیره کنیم اگر کد جنسیت 0 بود سپس مقدار ستون "نام جنسیت" برابر با "مرد" خواهد شد...


CREATE TABLE My_Sample
(
GenderID
CHAR(1)
DEFAULT (0)
CHECK(GenderID IN(0,1,2)),

GenderName AS CASE GenderID WHEN 0 THEN N'مرد'
WHEN 1 THEN N'زن'
WHEN 2 THEN N'نامعلوم'
END,
)

[محمد سلیم آبادی]

در این فاز شما را با عبارت LIKE و کاربرد آن در Check آشنا خواهم کرد.

LIKE قدرت مناسبی به ما می دهد و ما را قادر می سازد قیود نسبتا قدرت مندی در جداولمان ایجاد کنیم.

کاراکتر % بجای 0 الی N کاراکتر قرار می گیرد.
کاراکتر _ برای فقط یک کاراکتر در نظر گرفته می شود.
قلاب [] برای یک کاراکتر در نظر گرفته می شود. عبارت [^] این باعث می شود هر مقداری که خارج از مقادیر داخل قلاب است درست در نظر گرفته شود. عبارت [a-z] تمام کاراکتر های حروف الفبا از a تا z را شامل می شود.
(توصیه می کنم برای اطلاعات تکمیلی به Book Online مراجعه کنید)

مثال اول: می خواهیم آدرس پست الکترونیکی افراد را ذخیره کنیم. آدرس ایمیلی معتبر است که به فرمت کلی زیر باشد:

Name+ At Sign+ Site+ Dot+ Domain

که قید آن به شکل زیر است:


CREATE TABLE My_Sample
(
Email
CHAR(45)
NULL
CHECK(Email LIKE '_%@_%.__%'),
)
مثال دوم: می خواهیم شماره تلفن افراد را در یک ستون رشته ای درج کنیم. طول این شماره باید دقیقا 7 رقم بوده و اولین رقم نباید 0 باشد و تنها کاراکتر های 0 تا 9 قابل ذخیره شده هستند:



CREATE TABLE My_Sample
(
Telephone
CHAR(7)
CHECK(Telephone NOT LIKE '[0]%'
AND Telephone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
AND LEN(Telephone) = 7)
)


در شرط دوم قید فوق ساده تر است از کد زیر استفاده کنیم:



Telephone NOT LIKE '%[^0-9]%'


مثال سوم: می خوهیم مقدار نیم سال دانشجو را در یک ستون با طول 3 کاراکتر ذخیره کنیم به طوری که کاراکتر اول نیم سال (نیم سال اول یعنی مهر و نیم سال دوم یعنی بهمن) را مشخص کرده و دو کاراکتر بعدی سال ورود را مشخص کند.

به قول آقای مقسمی بدیهی است که کاراکتر اول تنها باید مقادیر 1 و 2 را در خود ذخیره کنند و دوما رقم اول سال ورود هم نباید 0 باشد و در ضمن طول رشته هم باید دقیقا 3 کارکاتر باشد نه کم نه زیاد.


EntryDate
CHAR(3)
NOT NULL
CHECK
(
LEN(EntryDate) = 3
AND EntryDate LIKE '[12][1-9][0-9]'
),