تحدي تصميم قواعد البيانات: منصة مشاركة الرحلات

تحدي تصميم قواعد البيانات: منصة مشاركة الرحلات

أنت تصمم قاعدة البيانات لمنصة مشاركة رحلات مشابهة لـ Uber أو Lyft. مهمتك تغطي أربعة مجالات: تصميم المخطط واستعلامات SQL واختيار الفهارس وأمان المعاملات.

الجزء 1: تصميم المخطط (25 نقطة)

صمم مخططًا علائقيًا كاملاً لمنصة مشاركة رحلات بالكيانات التالية:

• المستخدمون — الركاب والسائقون يتشاركون نفس جدول المستخدمين مع حقل للدور
• ملفات السائقين — رقم الرخصة، معلومات المركبة، حالة التحقق، الموقع الحالي
• الرحلات — مواقع الانطلاق/الوصول، الطوابع الزمنية، الأجرة، الحالة (مطلوبة، مقبولة، قيد التنفيذ، مكتملة، ملغاة)
• المدفوعات — المبلغ، طريقة الدفع (بطاقة، محفظة)، الحالة (معلقة، مكتملة، مستردة)، مرتبطة بالرحلة
• التقييمات — الراكب يقيّم السائق والسائق يقيّم الراكب بعد كل رحلة (1-5 نجوم، تعليق اختياري)

المتطلبات:

• استخدم أنواع بيانات مناسبة (BIGSERIAL للمعرفات، NUMERIC للأموال، TIMESTAMPTZ للأوقات، POINT أو DOUBLE PRECISION للإحداثيات)
• ضمّن جميع القيود الضرورية (PRIMARY KEY، FOREIGN KEY، NOT NULL، CHECK، UNIQUE، DEFAULT)
• أضف عمودًا واحدًا على الأقل غير مُسوّى مع تبرير

الجزء 2: استعلامات SQL (30 نقطة)

اكتب الاستعلامات التالية:

1. أفضل السائقين حسب التقييم مع التصفح — جد أفضل 10 سائقين مرتبين حسب متوسط التقييم الذين أكملوا 50 رحلة على الأقل. استخدم دالة نافذة (RANK أو DENSE_RANK). أرجع اسم السائق ومتوسط التقييم وإجمالي الرحلات والترتيب. ادعم التصفح بقبول إزاحة.

2. تحليلات الرحلات — اكتب استعلامًا يُرجع إحصائيات الرحلات اليومية لآخر 30 يومًا: التاريخ وإجمالي الرحلات وإجمالي الإيرادات ومتوسط الأجرة ونسبة تغير الإيرادات مقارنة باليوم السابق (استخدم دالة النافذة LAG).

3. مرشحات التسعير الديناميكي — جد المناطق الجغرافية (مقربة لمنزلتين عشريتين من خط العرض/الطول) حيث نسبة طلبات الرحلات إلى السائقين المتاحين تتجاوز 3:1 في الساعة الحالية.

الجزء 3: اختيار الفهارس (20 نقطة)

لمخططك، اقترح 4 فهارس على الأقل. لكل فهرس:

• اكتب جملة CREATE INDEX
• حدد أي استعلام أو نمط وصول يحسنه
• اشرح لماذا اخترت هذا النوع من الفهارس (B-tree، Hash، GIN، مركب، مغطٍ)
• اشرح ترتيب الأعمدة للفهارس المركبة

الجزء 4: أمان المعاملات (25 نقطة)

اكتب معاملة خالية من الطريق المسدود لمعالجة دفع رحلة تقوم بـ:

1. التحقق من أن حالة الرحلة 'completed' والدفع لا يزال 'pending'
2. خصم من محفظة الراكب (خصم الرصيد)
3. إيداع في محفظة السائق (إضافة الرصيد ناقص عمولة المنصة 20%)
4. تحديث حالة الدفع إلى 'completed'
5. كل ما سبق يجب أن يكون ذريًا — إذا فشلت أي خطوة، لا يتغير شيء

المتطلبات:

• استخدم القفل الصريح (SELECT ... FOR UPDATE) بترتيب متسق لمنع الطريق المسدود
• ضمّن معالجة أخطاء صحيحة (تحقق من الرصيد الكافي)
• حدد مستوى العزل واشرح لماذا اخترته

ما يجب تقديمه

يجب أن يحتوي تقديمك على قسم ملف واحد في المحرر أدناه: ملف SQL كامل يحتوي على الأجزاء الأربعة.

---