تصميم قواعد البيانات نظرة سريعة على لغة الاستعلامات الهيكلية SQL

لغة الاستعلامات الهيكلية Structured Query Language -أو SQL اختصارًا- هي لغة قاعدة بيانات مصمَّمة لإدارة البيانات الموجودة في نظام إدارة قواعد البيانات العلائقية.

طوّرت شركة IBM لغة SQL في أوائل السبعينات -عُرِفت بالإصدار 1986-، حيث صُمِّم الإصدار الأولي المسمَّى بلغة الاستعلامات الهيكلية الإنجليزية SEQUEL -اختصارًا للعبارة Structured English Query Language- لمعالجة واسترداد البيانات المخزَّنة في نظام خاص بشركة IBM وشبه علائقي لإدارة قواعد البيانات، ويُسمَّى نظام R.

قدّمت بعد ذلك شركة Relational Software Inc -والتي أصبحت الآن شركة Oracle Corporation- أول تطبيق متاح تجاريًا للغة SQL والمسمَّى بـ Oracle V2 لحواسيب VAX في أواخر السبعينات.

تُستخدَم العديد من أنظمة DBMS العلائقية المتاحة حاليًا، مثل: Oracle Database، وMicrosoft SQL Server -الموضَّح في الشكل التالي-، وMySQL، وIBM DB2، وIBM Informix، وMicrosoft Access، لغة SQL.

تُستخدَم لغة قاعدة بيانات SQL في نظام DBMS من أجل:

• إنشاء بنى قواعد البيانات والجداول.
• إجراء الأعمال الأساسية لإدارة البيانات، مثل الإضافة والحذف والتعديل.
• إجراء استعلامات معقَّدة لتحويل البيانات الأولية إلى معلومات مفيدة.

سوف نركز في هذا المقال على استخدام لغة SQL لإنشاء بنى قواعد البيانات والجداول، باستخدام لغة SQL على أساس لغة تعريف بيانات data definition language -أو DDL اختصارًا- بصورة أساسية.

سنستخدم لغة SQL في مقال لاحق على أساس لغة معالجة بيانات data manipulation language -أو DML اختصارًا- لإدخال البيانات، وحذفها، واختيارها، وتحديثها في جداول قاعدة البيانات.

إنشاء قاعدة بيانات Create Database

تتكوّن عبارات لغة SQL DDL الرئيسية من: CREATE DATABASE وCREATE/DROP/ALTER TABLE، إذ تُستخدَم عبارة CREATE في لغة SQL لإنشاء بنى قواعد البيانات والجداول.

إنشاء قاعدة بيانات

تُنشَأ قاعدة بيانات جديدة تسمَّى SW باستخدام العبارة CREATE DATABASE SW بلغة SQL. الخطوة التالية بعد إنشاء قاعدة البيانات هي إنشاء جداول قاعدة البيانات.

التنسيق العام للأمر CREATE TABLE هو:

CREATE TABLE <tablename>
(
ColumnName, Datatype, Optional Column Constraint,
ColumnName, Datatype, Optional Column Constraint,
Optional table Constraints
);

يكون Tablename اسم جدول قاعدة البيانات مثل جدول الموظف Employee، كما يتكون كل حقل من الأمر CREATE TABLE من ثلاثة أجزا، هي:

1. اسم العمود ColumnName.
2. نوع البيانات Data type.
3. قيد عمود اختياري Optional Column Constraint.

يجب أن يكون اسم العمود ColumnName فريدًا في الجدول، وبعض الأمثلة على أسماء الأعمدة هي FirstName وLastName.

أما نوع البيانات Data Type، فيجب أن يكون نوع بيانات نظام أو نوع بيانات يعرِّفه المستخدِم، كما تملك العديد من أنواع البيانات حجمًا، مثل (CHAR(35 أو (Numeric(8,2 وإليك قائمة بأشهر أنواع البيانات:

• النوع Bit: بيانات أعداد صحيحة Integer لها قيمة 1 أو 0.
• النوع Int: بيانات أعداد صحيحة Integer لها القيم من ‎-2^31 أي (‎-2,147,483,648) حتى ‎2^31 – 1، أي (‎2,147,483,647).
• النوع Smallint: بيانات أعداد صحيحة Integer لها القيم من ‎(-32,768) 2^15 أي حتى ‎ 2^15 – 1أي 32,767.
• النوع Tinyint: بيانات أعداد صحيحة Integer لها القيم من 0 حتى 255.
• النوع Decimal: بيانات ذات دقة ثابتة وقياس رقمي لها القيم من ‎-10^38 -1 إلى‎ 10^38.
• النوع Numeric: مرادف للنوع decimal.
• النوع Timestamp: رقم فريد على مستوى قاعدة البيانات.
• النوع Uniqueidentifier: معرَّف فريد عالميًا globally unique identifier -أو GUID اختصارًا-.
• النوع Money: تتراوح قيم البيانات النقدية من ‎-2^63 أي ‎-922,337,203,685,477.5808 حتى ‎2^63 – 1 أي ‎922,337,203,685,477.5807 بدقة تصل إلى واحد من عشرة آلاف من الوحدة النقدية.
• النوع Smallmoney: تتراوح قيم البيانات النقدية من ‎-214,748.3648إلى ‎+214,748.3647 بدقة تصل إلى واحد من عشرة آلاف من الوحدة النقدية.
• النوع Float: بيانات أرقام ذات دقة عشرية تتراوح قيمها بين ‎ -1.79E + 308و ‎1.79E + 308.
• النوع Real: بيانات أرقام ذات دقة عشرية قيمها تتراوح من ‎-3.40E + 38 حتى ‎3.40E + 38.
• النوع Datetime: بيانات التاريخ والوقت تتراوح قيمها من January 1, 1753 إلى December 31, 9999 بدقة تبلغ واحد إلى ثلاثة أجزاء من مئة من الثانية، أو 3.33 ميلي ثانية.
• النوع Smalldatetime: بيانات التاريخ والوقت تتراوح قيمها من January 1, 1900 حتى June 6, 2079 بدقة تبلغ دقيقة واحدة.
• النوع Char: بيانات محارف ثابتة الطول وليست يونيكود بطول أقصى 8000 محرف.
• النوع Varchar: بيانات متغيرة الطول وليست يونيكود بحد أقصى 8000 محرف.
• النوع Text: بيانات متغيرة الطول وليست يونيكود بطول أقصى يبلغ ‎2^31 – 1 أي ‎2,147,483,647 محرفًا.
• النوع Binary: بيانات ثنائية ذات طول ثابت بطول أقصى 8000 بايت.
• النوع Varbinary: بيانات ثنائية متغيرة الطول بطول أقصى يبلغ 8000 بايت.
• النوع Image: بيانات ثنائية متغيرة الطول بطول أقصى ‎2^31 – 1 أي‎ 2,147,483,647بايت.

أخيرًا في ما يتعلق بقيود العمود الاختيارية Optional Column Constraints، فهي الآتي:

• NULL.
• NOT NULL.
• UNIQUE.
• PRIMARY KEY.
• DEFAULT.

وتُستخدَم لتهيئة قيمة لسجل جديد.

يشير قيد العمود NULL إلى أن القيمة الفارغة null مسموح بها، مما يعني أنه يمكن إنشاء صف بدون قيمة لهذا العمود؛ كما يشير قيد العمود NOT NULL إلى وجوب توفير قيمة عند إنشاء صف جديد.

سنستخدم تعليمة لغة SQL للتوضيح والتي هي CREATE TABLE EMPLOYEES لإنشاء جدول موظفين يحتوي على 16 سمة attributes أو حقل fields.

USE SW
CREATE TABLE EMPLOYEES
(
EmployeeNo      CHAR(10)  NOT NULL  UNIQUE,
DepartmentName  CHAR(30)  NOT NULL  DEFAULT "Human Resources",
FirstName       CHAR(25)  NOT NULL,
LastName        CHAR(25)  NOT NULL,
Category        CHAR(20)  NOT NULL,
HourlyRate      CURRENCY  NOT NULL,
TimeCard        LOGICAL   NOT NULL,
HourlySalaried  CHAR(1)   NOT NULL,
EmpType         CHAR(1)   NOT NULL,
Terminated      LOGICAL   NOT NULL,
ExemptCode      CHAR(2)   NOT NULL,
Supervisor      LOGICAL   NOT NULL,
SupervisorName  CHAR(50)  NOT NULL,
BirthDate       DATE      NOT NULL,
CollegeDegree   CHAR(5)   NOT NULL,
CONSTRAINT      Employee_PK PRIMARY KEY(EmployeeNo)
);

الحقل الأول هو EmployeeNo من النوع CHAR، ويبلغ طول هذا الحقل 10 محارف، ولا يمكن للمستخدِم ترك هذا الحقل فارغًا NOT NULL، والحقل الثاني هو DepartmentName من النوع CHAR بطول 30.

يُستخدَم قيد الجدول المعرَّف بواسطة الكلمة CONSTRAINT لإنشاء المفتاح الأساسي primary key، وذلك بعد تعريف جميع أعمدة الجدول، أي كما يلي:

CONSTRAINT     EmployeePK      PRIMARY KEY(EmployeeNo)

يمكننا إنشاء جدول أقسام Department، وجدول مشاريع Project، وجدول مهام Assignment باستخدام الأمر CREATE TABLE بلغة SQL DDL، كما هو موضَّح في المثال التالي:

USE SW
CREATE TABLE DEPARTMENT
(
DepartmentName Char(35)  NOT NULL,
BudgetCode     Char(30)  NOT NULL,
OfficeNumber   Char(15)  NOT NULL,
Phone          Char(15)  NOT NULL,
CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)
);

أُنشِئ جدول المشاريع project التالي بسبعة حقول هي: معرِّف المشروع ProjectID، واسم المشروع ProjectName، والقسم Department، والحد الأقصى للساعات MaxHours، وتاريخ البدء StartDate، وتاريخ الانتهاء EndDate.

USE SW
CREATE TABLE PROJECT
(
ProjectID       Int  NOT NULL IDENTITY (1000,100),
ProjectName     Char(50) NOT NULL,
Department      Char(35) NOT NULL,
MaxHours        Numeric(8,2)  NOT NULL DEFAULT 100,
StartDate       DateTime NULL,
EndDate         DateTime NULL,
CONSTRAINT      ASSIGNMENT_PK  PRIMARY KEY(ProjectID)
);

بينما أُنشِئ جدول المهام assignment بثلاثة حقول، هي: معرِّف المشروع ProjectID، ورقم الموظف EmployeeNumber، وساعات العمل HoursWorked.

يُستخدَم جدول المهام لتسجيل الموظف باستخدام الحقل EmployeeNumber، ومقدار الوقت باستخدام الحقل HoursWorked الذي عمل فيه الموظف في مشروع معين باستخدام الحقل ProjectID، أي كما يلي:

USE SW
CREATE TABLE ASSIGNMENT
(
ProjectID       Int  NOT NULL,
EmployeeNumber  Int  NOT NULL,
HoursWorked     Numeric(6,2)  NULL,
);

قيود الجدول Table Constraints

تُعرَّف قيود الجدول بواسطة الكلمة المفتاحية CONSTRAINT ويمكن استخدامها لتطبيق العديد من القيود الموضَّحة أدناه.

القيد IDENTITY

يمكننا استخدام قيد العمود الاختياري IDENTITY لتوفير قيمة فريدة تزايدية لهذا العمود، إذ تُستخدَم أعمدة الهوية Identity مع قيود المفتاح الرئيسي PRIMARY KEY لتكون بمثابة معرِّف صف فريد للجدول، كما يمكن إسناد الخاصية IDENTITY إلى عمود له نوع بيانات tinyint، أو smallint، أو int، أو decimal، أو numeric، وهذا القيد:

• يولِّد أرقامًا متسلسلةً.
• لا يفرض سلامة الكيان entity integrity.
• يمكن أن يحتوي عمود واحد فقط على الخاصية IDENTITY.
• يجب تعريفه على أساس نوع بيانات integer أو numeric أو decimal.
• لا يمكن تحديث عمود له الخاصية IDENTITY.
• لا يمكن أن يحتوي على قيم فارغة NULL.
• لا يمكنه ربط الافتراضات والقيود الافتراضية بالعمود.

بالنسبة للقيد [(IDENTITY[(seed, increment:

• Seed: هي القيمة الأولية لعمود الهوية identity.
• Increment: هي القيمة المطلوب إضافتها إلى عمود الزيادة increment الأخير.

سنستخدم مثال قاعدة بيانات آخر لتوضيح عبارات لغة SQL DDL بصورة أكبر من خلال إنشاء الجدول tblHotel في قاعدة بيانات الفندق HOTEL كما يلي:

CREATE TABLE  tblHotel
(
HotelNo   Int         IDENTITY (1,1),
Name      Char(50)    NOT NULL,
Address   Char(50)    NULL,
City      Char(25)    NULL,
)

القيد UNIQUE

يمنع القيد UNIQUE من إدخال قيم مكررة في عمود، حيث:

• يُستخدَم القيدان PK، و UNIQUE لفرض سلامة الكيان.
• يمكن تعريف قيود UNIQUE متعددة للجدول.
• يجري دائمًا التحقق من صحة البيانات الموجودة عند إضافة قيد UNIQUE إلى جدول موجود.
• يمكن وضع القيد UNIQUE على الأعمدة التي تقبل القيم الفارغة، حيث يمكن أن يكون صفٌ واحد فقط NULL.
• ينشِئ القيد UNIQUE دليلًا فريدًا للعمود المُختار تلقائيًا.

الصيغة التالية هي الصيغة العامة للقيد UNIQUE:

[CONSTRAINT constraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED]
(col_name [, col_name2 […, col_name16]])
[ON segment_name]

يستخدم المثال التالي القيد UNIQUE كما يلي:

CREATE TABLE EMPLOYEES
(
EmployeeNo  CHAR(10)    NOT NULL    UNIQUE,
)

القيد FOREIGN KEY المفتاح الخارجي

يعرِّف القيد FOREIGN KEY -أو FK اختصارًا- عمودًا، أو مجموعة من الأعمدة التي تتطابق قيمها مع المفتاح الرئيسي PRIMARY KEY -أو PK اختصارًا- لجدول آخر، بحيث:

• تُحدَّث القيم في المفتاح الخارجي FK تلقائيًا عند تحديث أو تغيير قيم المفتاح الرئيسي PK في الجدول المرتبط.
• يجب أن تشير قيود المفتاح الخارجي FK إلى القيد المفتاح الرئيسي PK، أو القيد UNIQUE لجدول آخر.
• يجب أن يكون عدد أعمدة المفتاح الخارجي FK هو نفسه قيد المفتاح الرئيسي PK، أو قيد UNIQUE.
• إذا اُستخدِم الخيار WITH NOCHECK، فلن يتحقق قيد المفتاح الخارجي FK من صحة البيانات الموجودة في الجدول.
• لا يوجد دليل index للأعمدة التي تشارك في قيد المفتاح الخارجي FK.

الصيغة التالية هي الصيغة العامة لقيد المفتاح الخارجي FOREIGN KEY:

[CONSTRAINT constraint_name]
[FOREIGN KEY (col_name [, col_name2 […, col_name16]])]
REFERENCES [owner.]ref_table [(ref_col [, ref_col2 […, ref_col16]])]

يكون الحقل HotelNo في المثال التالي في الجدول tblRoom مفتاحًا خارجيًا FK للحقل HotelNo في الجدول tblHotel الموضَّح سابقًا:

USE HOTEL
GO
CREATE TABLE  tblRoom
(
HotelNo Int        NOT NULL ,
RoomNo  Int        NOT NULL,
Type    Char(50)   NULL,
Price   Money      NULL,
PRIMARY KEY (HotelNo, RoomNo),
FOREIGN KEY (HotelNo) REFERENCES tblHotel
)

القيد CHECK

يقيِّد القيد CHECK القيم التي يمكن إدخالها في جدول، بحيث:

• يمكن أن يحتوي على شروط بحث مشابهة لعبارة WHERE.
• يمكنه الربط بين الأعمدة في نفس الجدول.
• يجب تقييم قاعدة التحقق من صحة البيانات للقيد CHECK من خلال تعبير بولياني boolean expression.
• يمكن تعريفه لعمود له قاعدة مرتبطة به.

الصيغة التالية هي الصيغة العامة للقيد CHECK:

[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression)

يقتصر حقل النوع Type في المثال التالي على الأنواع "Single"، أو "Double"، أو "Suite"، أو "Executive".

USE HOTEL
GO
CREATE TABLE  tblRoom
(
HotelNo Int      NOT NULL,
RoomNo  Int      NOT NULL,
Type    Char(50) NULL,
Price   Money    NULL,
PRIMARY KEY (HotelNo, RoomNo),
FOREIGN KEY (HotelNo) REFERENCES tblHotel
CONSTRAINT Valid_Type
CHECK (Type IN ('Single', 'Double', 'Suite', 'Executive'))
)

يجب في المثال التالي أن يكون تاريخ تعيين الموظف قبل January 1, 2004، أو يجب أن يكون الحد الأقصى للراتب 300 ألف دولار:

GO
CREATE TABLE SALESREPS
(
Empl_num Int Not Null
CHECK (Empl_num BETWEEN 101 and 199),
Name     Char (15),
Age      Int     CHECK (Age >= 21),
Quota    Money   CHECK (Quota >= 0.0),
HireDate DateTime,
CONSTRAINT  QuotaCap CHECK ((HireDate < "01-01-2004") OR (Quota <=300000))
)

القيد DEFAULT

يُستخدَم القيد DEFAULT لتوفير قيمة تُضاف تلقائيًا لعمود ما إذا لم يوفّرها المستخدم، بحيث:

• يمكن احتواء العمود على قيد DEFAULT واحد فقط.
• لا يمكن استخدام القيد DEFAULT في الأعمدة التي لها نوع البيانات timestamp، أو الخاصية identity.
• ترتبط القيود DEFAULT تلقائيًا بعمود عند إنشائها.

الصيغة العامة للقيد DEFAULT هي:

[CONSTRAINT constraint_name]
DEFAULT {constant_expression | niladic-function | NULL}
[FOR col_name]

يضبط المثال التالي القيمة الافتراضية default لحقل المدينة city field على القيمة "Vancouver":

USE HOTEL
ALTER TABLE tblHotel
Add CONSTRAINT df_city DEFAULT ‘Vancouver’ FOR City

الأنواع التي يعرفها المستخدم User Defined Types

تعتمد الأنواع التي يعرِّفها المستخدِم دائمًا على نوع البيانات التي يوفرها النظام، فيمكن لهذه الأنواع فرض سلامة البيانات والسماح بالقيم الفارغة nulls.

اختر الأنواع التي تكون تحت الكلمة "Programmability" في قاعدة البيانات الخاصة بك، لإنشاء نوع بيانات يعرِّفه المستخدِم في خادم SQL Server، ثم انقر بزر الماوس الأيمن واختر المسار New’ –>‘User-defined data type’، أو نفّذ إجراء النظام sp_addtype المخزّن system stored procedure، ثم اكتب ما يلي:

sp_addtype ssn, 'varchar(11)', 'NOT NULL'

سيؤدي هذا إلى إضافة نوع بيانات جديد عرّفه المستخدم يسمى SIN بتسعة محارف.

يستخدم الحقل EmployeeSIN نوع البيانات SIN الذي عرّفه المستخدم في المثال التالي:

CREATE TABLE SINTable
(
EmployeeID     INT Primary Key,
EmployeeSIN    SIN,
CONSTRAINT CheckSIN
CHECK (EmployeeSIN LIKE
' [0-9][0-9][0-9] – [0-9][0-9] [0-9] – [0-9][0-9][0-9] ')
)

التعليمة ALTER TABLE

يمكن استخدام تعليمات ALTER TABLE لإضافة وحذف القيود، بحيث:

• تسمح تعليمة ALTER TABLE بإزالة الأعمدة.
• يُتحقق من جميع البيانات الموجودة عند إضافة قيد للتأكد من عدم وجود انتهاكات.

نستخدم في المثال التالي تعليمة ALTER TABLE للخاصية IDENTITY في الحقل ColumnName:

USE HOTEL
GO
ALTER TABLE  tblHotel
ADD CONSTRAINT unqName UNIQUE (Name)

استخدم تعليمة ALTER TABLE لإضافة عمود مع الخاصية IDENTITY مثل التعليمة ALTER TABLE TableName.

ADD
ColumnName  int IDENTITY(seed, increment)

التعليمة DROP TABLE

تزيل التعليمة DROP TABLE جدولًا من قاعدة البيانات، لذلك تأكد من تحديد قاعدة البيانات الصحيحة.

DROP TABLE tblHotel

سيؤدي تنفيذ عبارة DROP TABLE السابقة بلغة SQL إلى إزالة الجدول tblHotel من قاعدة البيانات.

تمارين

1. باستخدام المعلومات الخاصة بالتمرين الموجود في المقال قواعد السلامة والقيود المُطبَّقة عند تصميم قواعد البيانات، طبّق التخطيط باستخدام لغة Transact SQL -أي اعرض تعليمات SQL لكل جدول-، وطبّق القيود أيضًا.
2. أنشئ الجدول الموضَّح أدناه في خاوم SQL Server، واعرض التعليمات التي استخدمتها.

الجدول: Employee

3. اكتب شيفرة لغة SQL لإدخال صفوف الجدول السابق، بعد إنشاء بنيته.

استخدم الشكل السابق للإجابة على الأسئلة من 4 إلى 10.

4. اكتب شيفرة لغة SQL لتغيير رمز الوظيفة job code إلى 501 للموظف الذي رقمه 107، وافحص النتائج بعد الانتهاء من المهمة، ثم أعد ضبط رمز الوظيفة إلى قيمته الأصلية.
5. اكتب شيفرة لغة SQL لإعطاء قائمة بجميع السمات الخاصة برمز الوظيفة 502، بافتراض إدخال البيانات الموضَّحة في جدول الموظف Employee.
6. اكتب شيفرة لغة SQL لحذف الصف الخاص بالشخص الذي اسمه "William Smithfield"، والذي وُظِّف في June 22, 2004، والذي تصنيف رمز وظيفته هو 500.

اقتباس

تلميح: استخدم المعاملات المنطقية لتضمين جميع المعلومات الواردة في هذه المسألة.

7. أضف السمتين EMP_PCT، وPROJ_NUM إلى جدول الموظف، بحيث تكون السمة EMP_PCT هي نسبة المكافأة المدفوعة لكل موظف.
8. اكتب شيفرة لغة SQL باستخدام أمر واحد لإدخال رقم المشروع PROJ_NUM = 18 لجميع الموظفين الذين تصنيف الوظيفة JOB_CODE الخاص بهم هو 500.
9. اكتب شيفرة لغة SQL باستخدام أمر واحد لإدخال رقم المشروع PROJ_NUM = 25 لجميع الموظفين الذين تصنيف الوظيفة JOB_CODE الخاص بهم يساوي 502 أو أعلى.
10. اكتب شيفرة لغة SQL لتغيير رقم المشروع PROJ_NUM إلى 14 للموظفين الذين تعيّنوا قبل January 1, 1994، ورمز الوظيفة الخاصة بهم يساوي 501 على الأقل. (قد تفترض أن الجدول سيعاد إلى حالته الأصلية التي سبقت هذا السؤال).

ترجمة -وبتصرّف- للمقال SQL Structured Query Language لصاحبَيه Adrienne Watt وNelson Eng.

اقرأ أيضًا

• المقال التالي: لغة معالجة البيانات DML الخاصة بلغة SQL
• المقال السابق: عملية تطوير قواعد البيانات Database Development
• النسحة الكاملة من كتاب ملاحظات للعاملين بلغة SQL
• الاستعلامات الفرعية والإجراءات في SQL
• قواعد السلامة وقيودها لضمان سلامة البيانات في قواعد البيانات
• الاعتماديات الوظيفية المستخدمة في تصميم قواعد البيانات