تصميم قواعد البيانات أمثلة عملية عن كيفية تصميم قواعد البيانات

سنوضح في هذا المقال مثالًا عمليًا عن خطوات تصميم قاعدة بيانات لجامعة، كما سنضع أمثلةً عمليةً تشرح كيفية إنشاء مخططات ERD، إذ يشرح المثال الأول مثالًا عن مخطط ERD لشركة تصنيع، ويشرح المثال الثاني مثالًا عن مخطط ERD لوكيل سيارات، كما يقدم خطوات لحل تمرين باستخدام لغة SQL وعباراتها.

مثال عملي عن تصميم قاعدة بيانات لجامعة

فيما يلي متطلبات البيانات لمنتج من أجل دعم تسجيل وتقديم المساعدة لطلاب جامعة تعليم إلكتروني وهمية.

تحتاج جامعة تعليم إلكتروني إلى الاحتفاظ بتفاصيل طلابها وموظفيها، والمقررات التي تقدمها وأداء الطلاب الذين يدرسون فيها. تدار الجامعة في أربع مناطق جغرافية (إنجلترا واسكتلندا وويلز وأيرلندا الشمالية).

يجب تسجيل معلومات كل طالب في البداية عند التسجيل، ويتضمن ذلك رقم تعريف الطالب الصادر في الوقت والاسم وسنة التسجيل والمنطقة الموجود فيها الطالب. ليس الطالب ملزمًا بالتسجيل في أي مقرر عند التسجيل، فيمكنه التسجيل في مقررٍ ما في وقتٍ لاحق.

يجب أن تتضمن المعلومات المسجلة لكل عضو في القسم التعليمي وقسم الإرشاد رقمَ الموظف والاسم والمنطقة التي يوجد بها. قد يعمل كل موظف كمرشد counselor لطالبٍ أو أكثر، وقد يعمل كمدرس tutor لطالبٍ أو أكثر في مقررٍ أو أكثر. قد لا يُخصَّص لأحد الموظفين أي طالب كمدرس أو كمرشد في أي وقتٍ معين.

يملك كل طالب مرشدًا واحدًا يخصَّص له عند التسجيل، ويقدّم الدعم للطالب طوال حياته الجامعية. يُخصَّص للطالب مدرسٌ منفصلٌ لكل مقرر سجّل فيه الطالب. يُسمَح للموظف فقط العمل كمرشد أو كمدرّس لطالبٍ مقيم في نفس منطقته.

يجب أن يكون لكل مقرر متوفر للدراسة رمز مقرر وعنوان وقيمة من حيث نقاط الائتمان، حيث يكون للمقرر إما 15 نقطة أو 30 نقطة. قد يكون للمقرر حصة quota لعدد الطلاب المسجلين فيه في أي عرض. لا يحتاج المقرر إلى أي طالب مسجل فيه (مثل المقرر الذي كُتِب للتو ثم عُرِض للدراسة).

يُقيَّد الطلاب في عدد المقررات التي يمكنهم التسجيل فيها في نفس الوقت، فقد لا يأخذون المقررات في نفس الوقت إذا تجاوز مجموع النقاط المدمَجة للمقررات المسجلين فيها 180 نقطة.

قد يكون للمقرر ذي الـ 15 نقطة ما يصل إلى ثلاث وظائف لكل عرض، ويكون للمقرر ذي الـ 30 نقطة ما يصل إلى خمس وظائف لكل عرض. تُسجَّل درجة الوظيفة في أي مقرر كعلامةٍ من 100.

قاعدة بيانات الجامعة التالية نموذج بيانات محتمل يصِف مجموعة المتطلبات المذكورة أعلاه. يحتوي النموذج على عدة أجزاء، بدءًا من مخطط ERD ويليه وصفٌ لأنواع الكيانات والقيود والافتراضات.

عملية التصميم

1. الخطوة الأولى هي تحديد النوى والتي هي عادة أسماء: الموظفين Staff والمقرر Course والطالب Student والوظيفة Assignment.
2. الخطوة التالية هي توثيق جميع السمات attributes لكل كيان entity. هذا هو المكان الذي تحتاج فيه إلى التأكد من توحيد normalized جميع الجداول توحيدًا صحيحًا.
3. أنشئ مخطط ERD الأولي وراجعه مع المستخدمين.
4. أجرِ تغييرات إن لزم الأمر بعد مراجعة مخطط ERD.
5. تحقق من نموذج ER مع المستخدمين لوضع اللمسات الأخيرة على التصميم.

يوضّح الشكل التالي مخطط ERD للجامعة الذي يمثّل نموذج بيانات لنظام سجلات الطلاب والموظفين

الكيان Entity

• Student (StudentID, Name, Registered, Region, StaffNo).
• Staff (StaffNo, Name, Region): يحتوي هذا الجدول على مدرّسين وغيرهم من الموظفين.
• Course (CourseCode, Title, Credit, Quota, StaffNo).
• Enrollment (StudentlD, CourseCode, DateEnrolled, FinalGrade).
• Assignment (StudentID, CourseCode, AssignmentNo, Grade).

القيود Constraints

• يجوز لأحد الموظفين أن يدرّس أو يرشد الطلاب المتواجدين في نفس منطقتهم فقط.
• قد لا يسجّل الطلاب في مقررات لا تزيد قيمتها عن أكثر من 180 نقطة في نفس الوقت.
• للسمة Credit (ضمن المقرر Course) قيمة هي 15 أو 30 نقطة.
• قد يكون للمقرر الذي له 30 نقطة ما يصل إلى خمس وظائف، بينما يكون للمقرر الذي له 15 نقطة ما يصل إلى ثلاث وظائف.
• للسمة Grade (ضمن الوظيفة Assignment) قيمة هي علامة من 100.

الافتراضات Assumptions

• يستطيع الطالب أن يسجّل مرة واحدة للمقرر حيث تُسجَّل عمليات التسجيل الحالية فقط.
• تُقدَّم الوظيفة مرة واحدة فقط.

العلاقات Relationships (تشمل عددية العلاقة cardinality)

لاحظ في الشكل الآتي أن سجل الطالب مرتبط مع مقررات مُسجَّلة بحد أدنى مقرر واحد إلى مقررات متعددة كحد أقصى.

يجب أن يكون لكل تسجيل enrollment طالب صالح.

اقتباس

ملاحظة: بما أن معرّف الطالب StudentID هو جزء من المفتاح الرئيسي PK، فلا يمكن أن يكون فارغًا null، لذلك يجب وجود معرّف طالب StudentID مُدخَل في جدول الطالب مرة واحدة على الأقل كحد أقصى، لأن المفتاح الرئيسي PK يجب ألّا يتكرّر.

يوضح الشكل الآتي ارتباط سجل الموظفين (المدرّس هنا) بحد أدنى 0 طالب وبطلاب متعددين كحد أقصى.

قد يكون لسجل الطالب مدرسٌ tutor أو قد يكون بدون مدرس.

اقتباس

ملاحظة: يسمح الحقل StaffNo الموجود في جدول الطلاب Student بالقيم الفارغة التي تُمثَّل بالقيمة 0 على الجانب الأيسر من الشكل السابق. لكن في حالة وجود الحقل StaffNo في جدول الطلاب Student، فيجب أن يكون موجودًا في جدول الموظفين Staff بحد أقصى مرة واحدة (المُمثَّل بالقيمة 1 في الشكل السابق).

يرتبط سجل الموظفين Staff (المدرّس هنا) بعدد لا يقل عن 0 مقرّر كحد أدنى وبمقررات متعددة كحد أقصى.

قد يكون المقرر course مرتبطًا بمدرّس instructor أو غير مرتبط بمدرس.

اقتباس

ملاحظة: الحقل StaffNo الموجود في جدول Course هو المفتاح الخارجي FK الذي يمكن أن يكون فارغًا، ويُمثَّل ذلك من خلال القيمة 0 على الجانب الأيسر من العلاقة في الشكل الآتي. إذا احتوى الحقل StaffNo على بيانات، فيجب أن يكون في جدول الموظفين Staff بحد أقصى مرة واحدة، ويُمثَّل ذلك بالقيمة 1 على الجانب الأيسر من العلاقة.

يجب توفير المقرر (في عملية التسجيل enrollment) مرة واحدة على الأقل ومرات متعددة كحد أقصى، كما يجب أن يحتوي جدول التسجيل Enrollment على مقرر واحد صالح على الأقل إلى مقررات متعددة كحد أقصى.

يمكن أن تحتوي عملية التسجيل على 0 مهمة كحد أدنى أو مهام متعددة كحد أقصى. يجب أن ترتبط الوظيفة assignment بتسجيل واحد على الأقل وبتسجيلٍ واحد كحد أقصى.

اقتباس

ملاحظة: يجب أن يحتوي كل سجل في جدول الوظائف على سجل تسجيل صالح، ويمكن ربط سجل تسجيل واحد بمهام متعددة.

أمثلة عملية عن إنشاء مخططات ERD

سنعرض في هذه الجزئية مثالين عن عملية إنشاء مخططات ERD.

التمرين الأول: شركة تصنيع Manufacturer

تنتج شركة تصنيع منتجات، وتخزّن معلومات المنتج التالية: اسم المنتج product name ومعرّف المنتج product name والكمية المتوفرة quantity. تتكون هذه المنتجات من مكونات متعددة، ويوفّرموِّردٌ أو أكثر كلَّ مكون. تُحفَظ معلومات المكوّن التالية: معرّف المكون component ID واسمه name ووصف عنه description الموّردون suppliers الذين يوفرونه والمنتجات products التي تستخدم هذا المكوّن (استخدم الشكل الآتي لحل هذا التمرين).

1. أنشِئ مخطط ERD لإظهار كيفية تتبع هذه المعلومات.
2. اعرض أسماء الكيانات entity names والمفاتيح الرئيسية primary keys وسمات attributes كل كيان والعلاقات بين الكيانات وعددية العلاقة cardinality.

الافتراضات Assumptions

• يمكن وجود الموّرد دون أن يوفّر مكونات.
• ليس واجبًا أن يرتبط مكونٌ بموّرد.
• ليس واجبًا أن يرتبط مكوّنٌ مع منتج، فليست جميع المكونات مستخدمَةً في المنتجات.
• لا يمكن أن يوجد منتج بدون مكونات.

جواب مخطط ERD

• Component(CompID, CompName, Description) PK=CompID.
• Product(ProdID, ProdName, QtyOnHand) PK=ProdID.
• Supplier(SuppID, SuppName) PK = SuppID.
• CompSupp(CompID, SuppID) PK = CompID, SuppID.
• Build(CompID, ProdID, QtyOfComp) PK= CompID, ProdID.

التمرين الثاني: وكيل سيارات Car Dealership

أنشئ مخطط ERD لوكيل سيارات، حيث يبيع هذا الوكيل كلًا من السيارات الجديدة والمستعملة، ويشغّل قسمًا للخدمات. ابنِ تصميمك على قواعد الأعمال التالية:

• قد يبيع مندوب المبيعات salesperson سيارات متعددة، ولكن تُباع كل سيارة بواسطة مندوب مبيعات واحد فقط.
• يمكن أن يشتري العميل customer سيارات متعددة، ولكن تُشترى كل سيارة بواسطة عميل واحد فقط.
• يكتب مندوب المبيعات فاتورةً invoice واحدة لكل سيارة يبيعها.
• يحصل العميل على فاتورة لكل سيارة يشتريها.
• قد يأتي العميل من أجل الحصول على خدماتٍ لسيارته فقط، وهذا يعني أن العميل لا يحتاج إلى شراء سيارة لكي يُصنَّف كعميل.
• إذا جلب العميل سيارةً أو أكثر لإصلاحها أو للحصول على خدمة، فستُكتَب تذكرة خدمة service ticket لكل سيارة.
• يحتفظ وكيل السيارات بتاريخ خدمة لكل من السيارات المُخدَّمة، ويُشار إلى سجلات الخدمة عن طريق رقم السيارة التسلسلي.
• يمكن أن يعمل على السيارة التي تُجلَب للحصول على خدمة ميكانيكيون متعددون، وقد يعمل كل ميكانيكي على سيارات متعددة.
• قد تحتاج السيارة التي تحصل على خدمة إلى قِطع أو قد لا تحتاج إلى قطع (مثل عملية ضبط المفحّم carburetor أو تنظيف فوهة حاقن الوقود التي لا تتطلب توفير قِطعٍ جديدة).

جواب مخطط ERD

حل تمرين باستخدام لغة SQL

نزّل السكريبت التالي: OrdersAndData.sql.

الجزء الأول: استخدم لغة DDL

1. استخدم السكريبت orderData.sql الذي ينشئ جداولًا ويضيف بيانات مخطط ERD للطلبات والبيانات في الشكل السابق.
2. أنشئ قاعدة بيانات تسمّى Orders، وعدّل السكريبت لدمج المفتاح الرئيسي PK والسلامة المرجعية referential integrity. استخدم عبارات CREATE TABLE مع التعديلات بما في ذلك القيود الموجودة في الخطوة 3.
3. أضف القيود التالية:

• tblCustomers table: Country (Canada قيمته الافتراضية هي)
• tblOrderDetails: Quantity – > 0
• tblShippers: CompanyName (يجب أن يكون فريدًا)
• tblOrders: ShippedDate (order date يجب أن يكون أكبر تاريخ الطلب)

CREATE DATABASE Orders
Go
Use Orders
Go

Use Orders
Go
CREATE TABLE [dbo].[tblCustomers]
[CustomerID]       nvarchar(5) NOT NULL,
[CompanyName]      nvarchar(40) NOT NULL,
[ContactName]      nvarchar(30) NULL,
[ContactTitle]     nvarchar(30) NULL,
[Address]          nvarchar(60) NULL,
[City]             nvarchar(15) NULL,
[Region]           nvarchar(15) NULL,
[PostalCode]       nvarchar(10) NULL,
[Country]          nvarchar(15) NULL
Constraint     df_country DEFAULT ‘Canada’,
[Phone]            nvarchar(24) NULL,
[Fax]              nvarchar(24) NULL,
Primary Key (CustomerID)
);

CREATE TABLE [dbo].[tblSupplier] (
[SupplierID]     int NOT NULL,
[Name]           nvarchar(50) NULL,
[Address]        nvarchar(50) NULL,
[City]           nvarchar(50) NULL,
[Province]       nvarchar(50) NULL,
Primary Key (SupplierID)
);

CREATE TABLE [dbo].[tblShippers] (
[ShipperID]       int NOT NULL,
[CompanyName]     nvarchar(40) NOT NULL,
Primary Key (ShipperID),<
CONSTRAINT uc_CompanyName UNIQUE (CompanyName)
);

CREATE TABLE [dbo].[tblProducts] (
[ProductID]           int NOT NULL,
[SupplierID]          int NULL,
[CategoryID]          int NULL,
[ProductName]         nvarchar(40) NOT NULL,
[EnglishName]         nvarchar(40) NULL,
[QuantityPerUnit]     nvarchar(20) NULL,
[UnitPrice]           money NULL,
[UnitsInStock]        smallint NULL,
[UnitsOnOrder]        smallint NULL,
[ReorderLevel]        smallint NULL,
[Discontinued]        bit NOT NULL,
Primary Key (ProductID),
Foreign Key (SupplierID) References tblSupplier
);

CREATE TABLE [dbo].[tblOrders] (
[OrderID]            int NOT NULL,
[CustomerID]         nvarchar(5) NOT NULL,
[EmployeeID]         int NULL,
[ShipName]           nvarchar(40) NULL,
[ShipAddress]        nvarchar(60) NULL,
[ShipCity]           nvarchar(15) NULL,
[ShipRegion]         nvarchar(15) NULL,
[ShipPostalCode]     nvarchar(10) NULL,
[ShipCountry]        nvarchar(15) NULL,
[ShipVia]            int NULL,
[OrderDate]          smalldatetime NULL,
[RequiredDate]       smalldatetime NULL,
[ShippedDate]        smalldatetime NULL,
[Freight]            money NULL
Primary Key (OrderID),
Foreign Key (CustomerID) References tblCustomers,
Foreign Key (ShipVia) References tblShippers,
Constraint valid_ShipDate CHECK (ShippedDate > OrderDate)
);

CREATE TABLE [dbo].[tblOrderDetails] (
[OrderID]       int NOT NULL,
[ProductID]     int NOT NULL,
[UnitPrice]     money NOT NULL,
[Quantity]      smallint NOT NULL,
[Discount]      real NOT NULL,
Primary Key (OrderID, ProductID),
Foreign Key (OrderID) References tblOrders,
Foreign Key (ProductID) References tblProducts,
Constraint Valid_Qty Check (Quantity > 0)
);
Go

الجزء الثاني: إنشاء عبارات لغة SQL

1. اعرض قائمة العملاء customers والطلبات orders المُنشَأة خلال عام 2014. أظهر الحقول customer ID و order ID و order date و date ordered.

Use Orders
Go
SELECT CompanyName, OrderID, RequiredDate as ‘order date’, OrderDate as ‘date ordered’
FROM tblcustomers  JOIN tblOrders on tblOrders.CustomerID = tblCustomers.CustomerID
WHERE Year(OrderDate) = 2014

2. أضف حقلًا جديدًا (نشطًا) في جدول tblCustomer باستخدام عبارة ALTER TABLE، حيث تكون قيمته الافتراضية True.

ALTER TABLE tblCustomers
ADD Active bit DEFAULT (‘True’)

3. اعرض جميع الطلبات التي جرى شراؤها قبل 1 سبتمبر 2012 (اعرض الحقول company name و date ordered وكلفة الطلب الإجمالية (بما في ذلك تكلفة الشحن freight).

SELECT tblOrders.OrderID, OrderDate as ‘Date Ordered’, sum(unitprice*quantity*(1-discount))+ freight as ‘Total Cost’
FROM tblOrderDetails join tblOrders on tblOrders.orderID = tblOrderDetails.OrderID
WHERE OrderDate < ‘September 1, 2012’
GROUP BY tblOrders.OrderID, freight, OrderDate

4. اعرض جميع الطلبات المشحونة عبر شركة Federal Shipping (اعرض الحقول OrderID و ShipName و ShipAddress و CustomerID).

SELECT OrderID, ShipName, ShipAddress, CustomerID
FROM tblOrders join tblShippers on tblOrders.ShipVia = tblShippers.ShipperID
WHERE CompanyName= ‘Federal Shipping’

5. اعرض جميع العملاء الذين لم يشتروا في عام 2011.

SELECT CompanyName
FROM tblCustomers
WHERE CustomerID not in
(  SELECT CustomerID
FROM  tblOrders
WHERE Year(OrderDate) = 2011
)

6. اعرض جميع المنتجات التي لم تُطلَب أبدًا.

SELECT ProductID from tblProducts
Except
SELECT ProductID from tblOrderDetails

أو يمكن حل ذلك بالشكل التالي:

SELECT Products.ProductID,Products.ProductName
FROM Products LEFT JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
WHERE [Order Details].OrderID IS NULL

7. اعرض معرّفات الطلبات OrderID للزبائن الذين يقيمون في لندن باستخدام استعلام فرعي (اعرض الحقول CustomerID و CustomerName و OrderID).

SELECT Customers.CompanyName,Customers.CustomerID,OrderID
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CompanyName IN
(SELECT CompanyName
FROM Customers
WHERE City = ‘London’)

8. اعرض المنتجات التي يوفّرها الموّرد A والموّرد B (اعرض الحقول product name و supplier name).

SELECT ProductName, Name
FROM tblProducts JOIN tblSupplier on tblProducts.SupplierID = tblSupplier.SupplierID
WHERE Name Like ‘Supplier A’ or Name Like ‘Supplier B’

9. اعرض جميع المنتجات التي تأتي ضمن صناديق (اعرض الحقول product name و QuantityPerUnit).

SELECT EnglishName, ProductName,  QuantityPerUnit
FROM tblProducts
WHERE QuantityPerUnit like ‘%box%’
ORDER BY EnglishName

الجزء الثالث: الإدخال Insert والتعديل Update والحذف Delete والفهارس Indexes

1. أنشئ جدول الموظفين Employee. يجب أن يكون المفتاح الرئيسي هو معرّف الموظف EmployeeID وهو حقل ترقيم تلقائي autonumber. أضف الحقول التالية: LastName و FirstName و Address و City و Province و Postalcode و Phone و Salary. استخدم عبارة إنشاء جدول CREATE TABLE وعبارات إدخال INSERT خمسة موظفين. ضم جدول الموظفين employee إلى الجدول Tblorders. اعرض السكريبت لإنشاء الجدول وإعداد القيود وإضافة الموظفين.

Use Orders
CREATE TABLE [dbo].[tblEmployee](
EmployeeID Int IDENTITY NOT NULL ,
FirstName varchar (20) NOT NULL,
LastName varchar (20) NOT NULL,
Address varchar (50),
City varchar(20), Province varchar (50),
PostalCode char(6),
Phone char (10),
Salary Money NOT NULL,
Primary Key (EmployeeID)

Go
INSERT into tblEmployees
Values (‘Jim’, ‘Smith’, ‘123 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J6’, ‘2506155989’, ‘20.12’),
(‘Jimmy’, ‘Smithy’, ‘124 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J7’, ‘2506155984’, ‘21.12’),
(‘John’, ‘Smore’, ’13 Fake’, ‘Terrace’, ‘BC’, ‘V4G5J6’, ‘2506115989’, ‘19.12’),
(‘Jay’, ‘Sith’, ’12 Fake’, ‘Terrace’, ‘BC’, ‘V8G4J6’, ‘2506155939’, ‘25.12’),
(‘Jig’, ‘Mith’, ’23 Fake’, ‘Terrace’, ‘BC’, ‘V8G5J5’, ‘2506455989’, ‘18.12’);
Go

2. أضف حقلًا يسمّى Totalsales إلى جدول Tblorders. استخدم تعليمات لغة DDL وعبارة ALTER TABLE.

ALTER TABLE tblOrders
ADD Foreign Key (EmployeeID) references tblEmployees (EmployeeID)

3. استخدم عبارة UPDATE لإضافة مجموع مبيعات كل طلب بناءً على جدول تفاصيل الطلب order details.

UPDATE tblOrders
Set TotalSales = (select sum(unitprice*quantity*(1-discount))
FROM tblOrderDetails
WHERE tblOrderDetails.OrderID= tblOrders.OrderID
GROUP BY OrderID

ترجمة -وبتصرف- للمقالات:

• Appendix A University Registration Data Model Example
• Appendix B Sample ERD Exercises
• Appendix C SQL Lab with Solution 

لـ Adrienne Watt و Nelson Eng

اقرأ أيضًا:

• المقال السابق: لغة معالجة البيانات DML الخاصة بلغة SQL
• لغة معالجة البيانات DML الخاصة بلغة SQL
• نمذجة الكيان العلاقي ER عند تصميم قواعد البيانات
• نظرة سريعة على لغة الاستعلامات الهيكلية SQL
• النسخة العربية الكاملة لكتاب تصميم قواعد البيانات