🔄 تحويل مخططات ER إلى علائقي و DDL

تحويل التصاميم المفاهيمية إلى مخططات منطقية لقواعد البيانات باستخدام SQL DDL

📚 الفصل الخامس
🎯 CS 340
👩‍🎓 شوق العمران
⏱️ ملخص شامل

📑 فهرس المحتويات

🎯 نظرة عامة
1️⃣ الكيانات العادية
2️⃣ الكيانات الضعيفة
3️⃣ علاقات ثنائية 1:1
4️⃣ علاقات ثنائية 1:N
5️⃣ علاقات ثنائية M:N
6️⃣ الخصائص متعددة القيم
7️⃣ العلاقات متعددة الأطراف (N-ary)
8️⃣ التخصص / التعميم
9️⃣ أنواع الاتحاد (Union Types)
💻 أساسيات DDL
🎯

نظرة عامة على تحويل ER إلى علائقي

📘 وش يعني تحويل؟

تحويل ER إلى علائقي هو عملية منهجية لتحويل مخطط ER (أو EER) المفاهيمي إلى مخطط قاعدة بيانات علائقية منطقية تقدر تنفذه في أي نظام إدارة قواعد بيانات.

🎯 الخوارزمية بـ ٩ خطوات

الخطوات ١-٧: تحويل مكونات مخطط ER

  • الخطوة ١-٢: أنواع الكيانات (العادية والضعيفة)
  • الخطوة ٣-٥: العلاقات الثنائية (1:1، 1:N، M:N)
  • الخطوة ٦: الخصائص متعددة القيم
  • الخطوة ٧: العلاقات متعددة الأطراف (N-ary)

الخطوات ٨-٩: تحويل تراكيب EER

  • الخطوة ٨: التخصص / التعميم
  • الخطوة ٩: أنواع الاتحاد (الفئات)
1️⃣

الخطوة ١: تحويل الكيانات العادية

١

الكيانات العادية (القوية)

📋 الخوارزمية

لكل كيان عادي E في مخطط ER:

  1. أنشئ علاقة (جدول) R
  2. أضف كل الخصائص البسيطة للكيان E كأعمدة في R
  3. اختار خاصية مفتاح من E وجعلها المفتاح الأساسي لـ R
  4. إذا كان المفتاح مركبًا، فالخصائص البسيطة المكونة له معًا تكون المفتاح الأساسي
💡 مثال

كيان ER: موظف (EMPLOYEE) بخصائص {الرقم_الضريبي، الاسم، تاريخ_الميلاد، العنوان}

المخطط العلائقي:

EMPLOYEE(SSN, Name, BDate, Address)

الرقم_الضريبي (SSN) هو المفتاح الأساسي (تحته خط)

📌 ملاحظة عن الخصائص المركبة

بالنسبة للخصائص المركبة، نضيف فقط المكونات البسيطة. مثلاً لو الاسم مركب (الاسم_الأول، الحرف_الأوسط، الاسم_الأخير)، نضيف الأعمدة Fname، Minit، Lname بشكل منفصل.

2️⃣

الخطوة ٢: تحويل الكيانات الضعيفة

٢

الكيانات الضعيفة

📋 الخوارزمية

لكل كيان ضعيف W له كيان مالك E:

  1. أنشئ علاقة R
  2. أضف كل الخصائص البسيطة للكيان W في R
  3. أضف مفتاح خارجي يشير إلى المفتاح الأساسي لعلاقة الكيان المالك
  4. المفتاح الأساسي لـ R هو دمج:
    • المفتاح الأساسي للكيان المالك
    • المفتاح الجزئي للكيان الضعيف (إذا وجد)
💡 مثال: تابِع (DEPENDENT)

كيان ضعيف: تابِع (مفتاح جزئي: اسم_التابع)

كيان مالك: موظف (المفتاح الأساسي: SSN)

المخطط العلائقي:

DEPENDENT(ESSN, Dependent_name, Sex, BDate, Relationship) FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)

المفتاح الأساسي هو {ESSN, Dependent_name}

⚠️ نقطة مهمة

المفتاح الأساسي للكيان المالك يصير مفتاح خارجي وجزء من المفتاح الأساسي لجدول الكيان الضعيف.

3️⃣

الخطوة ٣: تحويل العلاقات الثنائية 1:1

٣

العلاقات الثنائية 1:1

📋 ثلاث طرق

لكل علاقة ثنائية من نوع 1:1 بين S و T:

🔹 الطريقة ١: المفتاح الخارجي (الأكثر شيوعًا)

اختر أحد الجدولين (يفضل اللي عنده مشاركة كلية) وأضف المفتاح الأساسي للجدول الآخر كمفتاح خارجي فيه.

مثال: موظف يدير قسم (1:1)

DEPARTMENT(Dnumber, Dname, Mgr_ssn, Mgr_start_date) FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(SSN)

Mgr_ssn هو المفتاح الخارجي في جدول DEPARTMENT

🔹 الطريقة ٢: دمج الجدولين

إذا كان عند الجدولين مشاركة كلية، ندمجهم في جدول واحد يحوي خصائص الاثنين.

🔹 الطريقة ٣: جدول منفصل للعلاقة

نسوي جدول مستقل للعلاقة يحوي مفتاحين خارجيين للجدولين. (نادرًا ما نستخدمه مع 1:1).

💡 اختيار الطريقة المناسبة

نستخدم طريقة المفتاح الخارجي لما يكون أحد الطرفين عنده مشاركة كلية. نحط المفتاح الخارجي في الجدول صاحب المشاركة الكلية عشان نتجنب القيم الفارغة (NULL).

4️⃣

الخطوة ٤: تحويل العلاقات الثنائية 1:N

٤

العلاقات الثنائية 1:N

📋 الخوارزمية

لكل علاقة ثنائية 1:N:

  1. حدد العلاقة S اللي تمثل الكيان في جهة N (الطرف الكثير)
  2. أضف في S مفتاح خارجي يشير إلى المفتاح الأساسي لعلاقة T (جهة 1)
  3. أضف أي خصائص بسيطة للعلاقة كأعمدة في S
💡 مثال: يعمل_في (WORKS_FOR)

العلاقة: موظف (N) يعمل_في (1) قسم

المخطط العلائقي:

EMPLOYEE(SSN, Name, BDate, Address, DNO) FOREIGN KEY (DNO) REFERENCES DEPARTMENT(Dnumber) DEPARTMENT(Dnumber, Dname, Mgr_ssn)

DNO مفتاح خارجي في EMPLOYEE (جهة N)

🎯 القاعدة الذهبية

المفتاح الخارجي دايمًا نحطه في الجدول اللي في جهة N (الطرف الكثير).

📌 طريقة بديلة

ممكن ننشئ جدول مستقل للعلاقة، لكن هالطريقة نادرًا ما تستخدم مع 1:N لأنها تعقيد بدون فايدة كبيرة.

5️⃣

الخطوة ٥: تحويل العلاقات الثنائية M:N

٥

العلاقات الثنائية M:N

📋 الخوارزمية

لكل علاقة ثنائية M:N:

  1. أنشئ علاقة جديدة S (جدول العلاقة)
  2. أضف مفاتيح خارجية تمثل المفاتيح الأساسية لكلا الجدولين المشاركين
  3. دمجهم مع بعض يكون المفتاح الأساسي لـ S
  4. أضف أي خصائص بسيطة للعلاقة M:N كأعمدة في S
💡 مثال: يعمل_على (WORKS_ON)

العلاقة: موظف (M) يعمل_على (N) مشروع

المخطط العلائقي:

WORKS_ON(ESSN, PNO, Hours) FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN) FOREIGN KEY (PNO) REFERENCES PROJECT(Pnumber)

المفتاح الأساسي هو {ESSN, PNO}
Hours خاصية للعلاقة

⚠️ نقطة مهمة جدًا

العلاقات M:N دايمًا تحتاج جدول مستقل. ما نقدر نستخدم مفاتيح خارجية في الجداول المشاركة لأن هذا يخالف النموذج العلائقي (الخصائص متعددة القيم).

6️⃣

الخطوة ٦: تحويل الخصائص متعددة القيم

٦

الخصائص متعددة القيم

📋 الخوارزمية

لكل خاصية متعددة القيم A:

  1. أنشئ علاقة جديدة R
  2. أضف الخاصية A في R
  3. أضف مفتاح خارجي K يشير إلى المفتاح الأساسي للعلاقة (الكيان أو العلاقة) اللي تتبعها A
  4. المفتاح الأساسي لـ R هو دمج {A, K}
  5. إذا كانت A مركبة، أضف مكوناتها البسيطة
💡 مثال: مواقع_القسم (DEPT_LOCATIONS)

خاصية متعددة القيم: مواقع القسم

المخطط العلائقي:

DEPARTMENT(Dnumber, Dname, Mgr_ssn) DEPT_LOCATIONS(Dnumber, Dlocation) FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)

المفتاح الأساسي هو {Dnumber, Dlocation}

🎯 ليه نحتاج جدول مستقل؟

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

7️⃣

الخطوة ٧: تحويل العلاقات متعددة الأطراف (N-ary)

٧

العلاقات متعددة الأطراف (n > 2)

📋 الخوارزمية

لكل علاقة N-ary (عدد الأطراف > 2):

  1. أنشئ علاقة جديدة S
  2. أضف مفاتيح خارجية لكل المفاتيح الأساسية للكيانات المشاركة
  3. المفتاح الأساسي لـ S غالبًا هو دمج كل المفاتيح الخارجية
  4. أضف أي خصائص بسيطة للعلاقة كأعمدة في S
💡 مثال: توريد (SUPPLY) علاقة ثلاثية

العلاقة: مورد يورّد قطعة لمشروع

المخطط العلائقي:

SUPPLY(Sname, Part_no, Proj_name, Quantity) FOREIGN KEY (Sname) REFERENCES SUPPLIER(Sname) FOREIGN KEY (Part_no) REFERENCES PART(Part_no) FOREIGN KEY (Proj_name) REFERENCES PROJECT(Proj_name)

المفتاح الأساسي هو {Sname, Part_no, Proj_name}

📌 اعتبارات الأصلية (Cardinality)

إذا كانت العلاقة لها قيود أصلية (مثلاً M:N:1)، المفتاح الأساسي يمكن يكون مجرد دمج المفاتيح الخارجية لجهات M و N فقط، مو كل الأطراف.

8️⃣

الخطوة ٨: تحويل التخصص/التعميم

٨

تحويل التخصص/التعميم

📋 أربع طرق للتحويل

للتخصص/التعميم، نختار من بين:

🔹 الطريقة ١: جداول متعددة (الكيان الأب + الأبناء)

ننشئ جدول للـ كيان الأب وجداول منفصلة لكل ابن.

  • جدول الأب يحوي الخصائص المشتركة + المفتاح الأساسي
  • كل جدول ابن يحوي المفتاح الأساسي (كمفتاح خارجي) + الخصائص الخاصة
  • أنسب لـ التخصص المنفصل (Disjoint)
🔹 الطريقة ٢: جداول متعددة (الأبناء فقط)

ننشئ جداول للأبناء فقط، وكل ابن يحوي كل الخصائص الموروثة + خصائصه الخاصة.

  • مافي جدول مستقل للكيان الأب
  • كل ابن يضم كل خصائص الأب
  • أنسب لـ التخصص الكلي (Total) والمنفصل (Disjoint)
🔹 الطريقة ٣: جدول واحد مع عمود النوع

ننشئ جدول واحد يحوي كل خصائص الأب وجميع الأبناء.

  • نضيف عمود نوع لتمييز الكيانات
  • الخصائص الخاصة بالأبناء ممكن تكون NULL للكيانات اللي ما تنطبق عليهم
  • أنسب لـ التخصص المتداخل (Overlapping)
🔹 الطريقة ٤: جدول واحد مع أعمدة نوع متعددة

جدول واحد مع أعمدة منطقية متعددة (واحد لكل ابن).

  • كل عمود منطقي يشير إلى انتماء الكيان للابن المحدد
  • أنسب لـ التخصص المتداخل (Overlapping) لما الكيان الواحد يقدر ينتمي لأكثر من ابن
💡 كيف نختار الطريقة الصح؟
  • منفصل + الاستعلامات تحتاج كل الكيانات: الطريقة ١
  • كلي + منفصل: الطريقة ٢
  • متداخل: الطريقة ٣ أو ٤
9️⃣

الخطوة ٩: تحويل أنواع الاتحاد (الفئات)

٩

أنواع الاتحاد (Union Types / Categories)

📋 الخوارزمية

لكل فئة (اتحاد):

  1. أنشئ جدول للـ فئة
  2. أضف مفتاح بديل (Surrogate key) كمفتاح أساسي
  3. أضف هذا المفتاح البديل كمفتاح خارجي في كل جدول من جداول الكيانات الأب
  4. المفتاح البديل يربط كيانات الفئة بكيانات الأب المناظرة
💡 مثال: فئة المالك (OWNER)

الفئة: مالك (اتحاد شخص، بنك، شركة)

المخطط العلائقي:

OWNER(Owner_id, Owner_type, ...) PERSON(SSN, Name, Owner_id) BANK(Bank_code, Name, Owner_id) COMPANY(Reg_no, Name, Owner_id)

Owner_id هو المفتاح البديل اليربط الفئة بالجدول الأب المناسب

🎯 ليش نستخدم مفاتيح بديلة؟

الفئات تجمع كيانات من كيانات أب بمفاتيح أساسية مختلفة. المفتاح البديل يعطينا طريقة موحدة للإشارة لأعضاء الفئة بغض النظر عن أي جدول أب ينتمون له.

💻

أساسيات لغة تعريف البيانات (DDL)

📘 وش هي DDL؟

لغة تعريف البيانات (DDL) نستخدمها لتعريف، تعديل، وحذف هياكل قاعدة البيانات. أشهر أوامر DDL: CREATE، ALTER، DROP.

CREATE TABLE

📝 الصيغة الأساسية
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype, ... );
💡 مثال: إنشاء جدول CUSTOMER
CREATE TABLE CUSTOMER ( Customer_id NUMBER(4), CustomerName VARCHAR(30), CustomerAddress VARCHAR(50) );

المفاتيح الأساسية

🔑 تعريف المفتاح الأساسي
CREATE TABLE CUSTOMER ( Customer_id NUMBER(4) NOT NULL, CustomerName VARCHAR(30) NOT NULL, CustomerAddress VARCHAR(50), PRIMARY KEY (Customer_id) );

المفاتيح المركبة

🔗 مفتاح أساسي مركب
CREATE TABLE Persons ( ID INT NOT NULL, LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Age INT, CONSTRAINT PK_Person PRIMARY KEY (ID, LastName) );

المفاتيح الخارجية

🔗 تعريف المفتاح الخارجي
CREATE TABLE Orders ( OrderID INT NOT NULL, OrderNumber INT NOT NULL, PersonID INT, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); -- أو مع تسمية القيد: CREATE TABLE Orders ( OrderID INT NOT NULL, OrderNumber INT NOT NULL, PersonID INT, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );

قيود DDL

⚙️ أشهر القيود

قيود على الأعمدة:

  • NOT NULL: العمود لازم يكون له قيمة
  • UNIQUE: قيمة العمود تكون فريدة في الجدول
  • CHECK: قيمة العمود تحقق شرط معين
  • DEFAULT: قيمة افتراضية إذا ما أدخلنا شيء

حالات المفاتيح الخارجية:

  • ON DELETE CASCADE: حذف الصفوف المرتبطة لما يُحذف الصف المُشار إليه
  • ON UPDATE CASCADE: تحديث المفتاح الخارجي لما يتحدث المفتاح الأساسي
  • ON DELETE SET NULL: يخلي المفتاح الخارجي NULL لما يُحذف الصف المُشار إليه
💡 مثال كامل: جدول DEPENDENT
CREATE TABLE Employee ( ID INT NOT NULL, EmployeeName VARCHAR(255) NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE DEPENDENT ( Name VARCHAR(255) NOT NULL, EmployeeID INT NOT NULL, DateOfBirth DATE, Gender VARCHAR(1) DEFAULT 'F', CONSTRAINT PK_Dependent PRIMARY KEY (EmployeeID, Name), FOREIGN KEY (EmployeeID) REFERENCES Employee(ID) );

ALTER و DROP

🔧 تعديل وحذف الجداول
-- ALTER: إضافة أو تعديل أعمدة ALTER TABLE Employee ADD Email VARCHAR(100); ALTER TABLE Employee MODIFY Salary NUMBER(10,2); -- DROP: حذف الجدول كامل DROP TABLE DEPENDENT; -- TRUNCATE: حذف كل الصفوف مع بقاء هيكل الجدول TRUNCATE TABLE Orders; -- RENAME: تغيير اسم الجدول RENAME TABLE Old_Name TO New_Name;
⚠️ أفضل الممارسات
  • دايمًا حدد المفاتيح الأساسية بشكل صريح
  • استخدم أسماء قيود مفهومة عشان تسهل الصيانة
  • حدد NOT NULL للخصائص الأساسية
  • حدد ON DELETE/UPDATE للمفاتيح الخارجية
  • استخدم أنواع بيانات وأحجام مناسبة
✨

ملخص الفصل

🎯 النقاط الرئيسية
  • الخطوات ١-٢: تحويل الكيانات ينشئ الجداول الأساسية (العادية والضعيفة).
  • الخطوات ٣-٥: تحويل العلاقات يعتمد على نوع العلاقة (1:1، 1:N، M:N).
  • الخطوة ٦: الخصائص متعددة القيم تحتاج جداول مستقلة.
  • الخطوة ٧: العلاقات متعددة الأطراف تحتاج جداول مستقلة للعلاقة.
  • الخطوات ٨-٩: تراكيب EER لها عدة طرق للتحويل حسب النوع.
  • DDL: أوامر SQL اللي تحوّل المخططات المنطقية إلى جداول فعلية في قاعدة البيانات.
🎓 الخطوات الجاية

بعد ما تتعلم تحويل ER إلى علائقي، تقدر:

  • تحول أي مخطط ER أو EER إلى جداول علائقية.
  • تستخدم DDL لتنفيذ المخططات في قواعد بيانات حقيقية.
  • تكتب استعلامات SQL عشان تتعامل مع البيانات وتستخرجها.
  • تفهم التسوية (Normalization) عشان تصمم مخططات مثالية.
شوق العمران
CS 340: مقدمة في أنظمة قواعد البيانات
الفصل الخامس - تحويل ER إلى علائقي و DDL
📚 دليل دراسة شامل