بتـــــاريخ : 2/14/2011 10:44:44 PM
الفــــــــئة
  • الحـــــــــــاسب
  • التعليقات المشاهدات التقييمات
    0 1824 0


    قواعد البيانات بين النظرية والتطبيق دروس متقدمة في قواعد البيانات اكسيس

    الناقل : elmasry | العمر :42 | الكاتب الأصلى : zahrah | المصدر : www.arabteam2000-forum.com

    كلمات مفتاحية  :

    قواعد البيانات بين النظرية والتطبيق
    اعزائي الكرام
    السلام عليكم ورحمة الله وبركاته
    سنناقش في هذاالموضوع عدداً من المشاكل التي قد تصادفكم لدى تعاملكم مع البيانات , وسنعرض الحلول الممكنه لها. واقترح أن تحاولوا إيجاد حل قبل قراءة الحل المعطى في النص . وعلينا أن نشير هنا إلي أن هناك عدة طرق لحل مشكلة معينة . في الحقيقه قد نتمكن من الحصول على حل أكثر فعاليه من الحل المقترح هنا إن الهدف الأساسي من هذه المشاكل والحلول هي إعطاؤكم غذاء فكريا نافعا .
    قبل البدء, دعونا نشير إلي أن الكثير من الحلول المقترحة يعتمد على استخدام الاستعلامات الفرعيه .
    يسمح Access SQL بأستخدام عبارات SeIectضمن عبارات SeIect أخرى (كما هي الحال في عبارات أخرى مثل عبارات ( INSERT INTO) . تدعى عبارة SeIect الداخليه أو المتشابكه بالاستعلام الفرعي.
    لاحظ أنه يمكن أن تضمن عبارة SeIect داخليه ضمن عبارة SeIect أساسية فقط إذا كانت عبارة SeIect الداخلية تعيد سجلا واحدا على الأغلب ولبيان ذلك لنأخذ عبارة SQL الأساسية التالية:

    SELECT HOUR, 
    (Select Count(Interval) From StartTimes WHERE (StartTime <=Hour))
    FROM HOURS


    تعيد عبارة Select الداخلية :

    Select Count(Interval) From StartTimes WHERE (StartTime <=Hour)


    على الأغلب سجلاً واحدا لأنها تعيد عدد (Count) لاحظ أيضاً أن ظرف WHEREفي عبارة SeIect الداخليه يشير إلى حقل HOUR الذي هو جزء من عبارة SQL الرئيسية , وبالتالي فانه يقوم بربط القيمه المسترجعه للعبارة الداخلية إلى السجل الحالي في جدول HOURS
    المجاميع الجارية : Running Sums
    يعتبر حساب المجاميع الجارية من العمليات الشائعه ولبيان هذا المفهوم سنأخذ الجدول ( 1-1) الذي يتضمن المدد الزمنيه ( محسوبة بالساعة ) لعدة أحداث .
    Posted Image
    ونريد أن نحسب لكل حدث مجموع المدد الزمنية للآحداث التي تسبقه يدعي هذا المجموع بالمجموع الجاري .
    الحل : Solution
    إن إحدى طرق الحل هي بإجراء الجداء السلمي للجدول مع نفسه نستطيع بهذه الطريقه الوصول إلي جميع السجلات التي يسبق رقم حدثها ( Event ) رقم سجل (أوحدث) معين . على سبيل المثال ,من أجل السجل الخامس ، نحتاج الي الوصول إلي السجلات التي تتضمن الاحداث رقم 1 وحتى 4 . أن الجداء السلمي يعطينا هذه السجلات .
    إليك عبارة SQL التي تقوم بهذا العمل :

    SELECT Running.Event, Sum(RunningCopy.Duration) AS StartTime 
    FROM Running, Running AS RunningCopy
    WHERE (RunningCopy.Event < Running.Event)
    GROUP BY Running.Event


    يقوم ظرف FORM بإنشاء الجداء السلمي للجدول مع نفسه . أما ظرف WHERE فيحدد السجلات التي تحقق المعيار

    Running Copy.Event < Running. Event


    أي ان السجلات التي ستقدم المعلوكات حول السجلات التي تسبق كل سجل في الجدول ( 1-1 ).
    أخيرا ، نقوم بتجميع النتائج حسب الحدث ( Group By Event ) وحساب مجموع المدد الزمنية . ان المشكلة هي ان الجداء السلمي غير فعال ويستخدم حجما كبيرا من الموارد ( إذا كان عدد سجلات الجدول ( 1-1 ) 100000 سجل فإن الجداء السلمي سينفذ عملية ضرب على 100000 × 100000 سجل أي 10.000.000.000 سجل .
    ان الحل الاكثر فعالية هو استخدام عبارة Select متشابكة ( داخلية ) أي استخدام عبارة Select ضمن عبارة Select اساسية . نعود لنذكر هنا بأن Access SQL يسمح بذلك اذا كانت العبارة الداخلية تعيد على الغالب سجلا واحدا .
    لاحظ استخدمنا لألقاب ( نسخ ) للجدول في عبارة Select التالية ونحتاج الى هذه الالقاب لاننا سنشير الى الجدول ( 1-1 ) في سياقين

    Select R1.Event 
    (Select Sum(R2.Duration) From Running As R2 Where R2.Event
    <R1.Event) As StartTime
    FROM Running As R1


    ان عبارة SQL الداخلية :

    Select Sum(R2.Duration) From Running As R2 Where R2.Event <R1.Event


    تعيد مجموع المدة لجميع الاحداث التي تسبق الحدث الحالي ، والذي نشير إليه بالعبارة R1.Event
    يعرض المثال ( 1-1 ) اجرائية VBA لتنفيذ عبارة SQL السابقة . ويستخدم الكائن DoCmd في VBA لتشغيل اجراء Access وبالتالي فأن السطر البرمجي :

    DoCmd.OpenQuery


    يقوم بفتح استعلام في Access
    المثال ( 1-1 ) : حساب المجموع الجاري بإستخدام عبارة SQL

    Private Sub RunningSumSQL () 
    Dim db As ADO.Database
    Set db = CurrentDb
    Dim qry As ADO.QueryDef
    Dim sSQL As String
    On Error Resume Next
    db.QueryDefs.Delete "temp"
    On Error GoTo 0
    sSQL = "SELECT R1.Event," & _
    " (SELECT SUM(R2.Duration)" & _
    "FROM Running As R2" & _
    "WHERE R2.Event < R1.Event)" & _
    "AS StartTime" & _
    " FROM Running As R1"
    Set qry = db.CreateQueryDef("temp", sSQL)
    DoCmd.OpenQuery qry.Nqme
    End Sub


    هناك طريقة أخرى تقوم على استخدام ADO الذي يؤمن طريق أو حل بسيط في هذه الحالة فهو ينشئ جدول نتائج دائم في حين ان الحل السابق يقوم بإنشاء استعلام تحديد ويعرض المثال ( 1-2 ) شفرة ADO التي تنفذ نفس العمل

    Private Sub RunningSumDAO( ) 
    Dim db As Database
    Dim rs As Recordset
    Dim lRunningSum As Long
    Set db = CurrentDb
    lRunningSum = 0
    Set rs = db.OpenRecordset("SELECT * FROM Running ORDER BY Event")
    Do While Not rs.EOF
    rs.Edit
    rs!RunningSum = lRunningSum
    rs.Update
    lRunningSum = lRunningSum + rs!Duration
    rs.MoveNext
    Loop
    rs.Close
    End Sub


    المجالات المتقاطعة : Overlapping Interavl
    تشير الصفوف في الجدول ( 1-2 ) الى مجالات زمنية ، المشكلة هي تعيين من أجل كل ساعة من اليوم ، عدد المجالات التي تتضمن هذه الساعة .
    Posted Image
    ولحل هذه المشكلة نستخدم جدول HOURS المبين في الجدول ( 1-3 )
    Posted Image
    الحل : Solution
    يمكن حل هذه المشكلة بإستخدام عبارة Select متشابكة ( أي بإستخدام استعلام فرعي ) والكود التالي يقوم بالعمل المطلوب :

    Private Sub OverlappingIntervals( ) 
    Dim db As Database
    Set db = CurrentDb
    Dim qry As QueryDef
    Dim sSQL As String
    On Error Resume Next
    db.QueryDefs.Delete "temp"
    On Error GoTo 0
    sSQL = "SELECT Hours.Hour, " & _
    " (SELECT Count(Interval) AS CountOfIntervals" & _
    " FROM Overlap" & _
    " WHERE (StartTime <= Hours.Hour) And" & _
    " (Hours.Hour < EndTime))" & _
    " FROM Hours"
    Set qry = db.CreateQueryDef("temp", sSQL)
    DoCmd.OpenQuery qry.Name
    End Sub

    المجالات المتقاطعه Overlapping Intervals II : II
    تستخدم شركه عمالاً ومشرفين ، وخلال يوم عمل عادي ، يعمل كل عامل وكل مشرف ورديه واحده أو ورديتين ، تتألف من ساعات عمل متعاقبه ، يعرض الجدول ( 4-1 ) يوماً تقليدياً من الساعه 12 ظهراً وحتى الساعه 12 في منتصف الليل .
    Posted Image
    نريد هنا حساب العدد الآعظمي للعمال الذين يشرف عليهم مشرف واحد في وقت معين .
    الحل : Solution
    يمكن معالجة هذه المشكله بإجراء مؤلف من مرحلتين ، نقوم اولاً بحساب عدد العمال في كل ساعه ونأخذ عبارة SQL التاليه :

    SELECT Hours.Hour, 
    (SELECT Count(EmpType) FROM SuperLoad WHERE (Starthour <= Hours.Hour) And (Hours.Hour < EndHour) And (EmpType='Worker')) AS CountOfWorkers
    FROM Hours


    لاحظ هنا اننا نستخدم استعلاماً فرعياً يعيد سجلاً وحيداً يعطي عدد العمال الذين يعملون في ساعه معينه .
    بإستخدام عبارة SQL هذه نقوم بإنشاء استعلام اسمه qry1 بحيث يمكن استخدامه في المرحله الثانيه . انظر الجدول ( 1-5) من اجل نتائج هذا الإستعلام .
    Posted Image
    الخطوه التاليه هي حساب تحميل المشرف على انه العدد الآعظمي للعمال في كل ورديه للمشرف ، نقوم بذلك باستخدام اسم الآستعلام من المرحله السابقه في عبارة SQL التاليه :

    sSQL = "SELECT SuperLoad.EmpID, SuperLoad.EmpType," & _ 
    " (SELECT Max(CountOfWorkers) AS WorkerLoad" & _
    " FROM [" & qry1.Name & "]" & _
    " WHERE((Hours.Hour >= StartHour) And(Hours.Hour< Endhour)))" & _
    " FROM SuperLoad " & _
    " WHERE SuperLoad.EmpType = 'Super' "


    والنتائج مبينه في الجدول ( 1-6)
    Posted Image
    والكود الكامل لهذا الحل مبينه في المثال ( 1-3)
    المثال ( 1-3) : حساب العدد الآعظمي للعمال من اجل كل مشرف .

    Private Sub SupervisorLoad( ) 
    Dim db As Database
    Set db = CurrentDb
    Dim qry1 As QueryDef
    Dim qry2 As QueryDef
    Dim sSQL1 As String
    Dim sSQL2 As String
    On Error Resume Next
    db.QueryDefs.Delete "temp1"
    db.QueryDefs.Delete "temp2"
    On Error GoTo 0
    sSQL = "SELECT Hours.Hour, " & _
    " (SELECT Count(Emptype) FROM SuperLoad" & _
    " WHERE(StartHour <= Hours.Hour) And (Hours.Hour < EndHour)" & _
    " And (Emptype='Worker'))" & _
    " AS CountOfWorkers" & _
    " FROM Hours"
    Set qry1 = db.CreateQueryDef("temp1", sSQL1)
    ' Uncomment to see how this step looks
    ' DoCmd.OpenQuery qry1.Name
    sSQL = "SELECT SuperLoad.EmpID, SuperLoad.EmpType," & _
    " (SELECT Max(CountOfWorkers) AS WorkerLoad" & _
    " FROM [" & qry1.Name & "]" & _
    " WHERE((Hours.Hour >= StartHour) And (Hours.Hour< Endhour)))" & _
    " FROM SuperLoad " & _
    " WHERE SuperLoad.EmpType = 'Super' "
    Set qry2 = db.CreateQueryDef("temp2", sSQL2)
    DoCmd.OpenQuery qry2.Name
    End Sub



    إجراءات عمليات الإسناد باستخدام القيمه الافتراضية : Assignment With Default Making
    تخيل وجود مؤتمر، ومهمتك فيه توزيع الحضور على قاعات المحاضرات ، يعرض الجدول ( 1-7) الحضور المسجل مع ارقام الغرف الموافقه .
    Posted Image
    لاحظ ان الجدول يتضمن عدة خيارات افتراضيه ( Default ) وإذا لم يكن شخص موجوداً في الجدول ، فإنك تريد تخصيصه بغرفه من الغرف الآفتراضيه ، بالإضافه الى ذلك ، ولتجنب الازدحام في الغرف ، تريد تخصيص ارقام الغرف الافتراضيه بشكل عشوائي ، فكيف نقوم بذلك .
    الحل : Solution
    يمكن حل هذه المشكله بعدة طرق ، تقوم إحداها على استخدام الاستعلامات الفرعيه مع عبارةUNION
    لنأخذ اولا عبارة SQL التاليه :

    sSQL1="SELECT ROOM From Assignment WHERE (Name=[Enter Name])"


    نذكر بأن استخدام عبارة (Enter Narme ) هو عبارة عن وسيط . وعند تشغيل الاستعلام ، يتم الطلب من المستخدم إدخال اسم يتم استبداله بالوسيط المذكور ، ستعيد هذه العباره السجل الموافق لاسم معين إذا كان موجوداً ، وإلا فإنها ستعيد مجموعة سجلات فارغه .
    لتأخذ الآن عبارة SQL التاليه :
    يتظمن المثال ( 1-4 ) الكود الكامل لتنفيذ الحل المطلوب .
    معالجة توزيع الغرف الافتراضية والمحجوزة مسبقا .

    sSQL2 = "SELECT Room FROM Assignment" & _ 
    "WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name FROM Assignment))"


    إن عبارة

    [Enter Name] NOT IN (SELECT Name FROM Assignment)



    ستعيد القيمه True إذا وفقط إذا كان الاسم الذي ادخله المستخدم غير موجود في الجدول وبالتالي فإن عبارة sSQL2 يمكن ان تكتب من جديد استناداً الى حالتين :
    1.الاسم في الجدول

    sSQL2 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND False"


    2. الاسم ليس في الجدول

    sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND True"


    ويمكن اختصار العباره من اجل حالة الاسم في الجدول لتصبح :

    sSQL2 = "SELECT Room FROM Assignment WHERE FALSE


    والعبارة من اجل حالة الاسم ليس في الجدول :

    sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default')"



    وهكذا فإن العباره ستعيد مجموعة سجلات فارغه اذا كان الاسم في الجدول والسجلات الافتراضيه اذا لم يكن الاسم في الجدول .
    سنأخذ الآن دمج أو توحيد العبارتين :

    sSQL3=sSQL1 & "UNION" & sSQL2


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

    Private Sub AssignmentWithDefault( ) 
    Dim db As Database
    Set db = CurrentDb
    Dim sName As String
    Dim qry1 As QueryDef
    Dim rs As Recordset
    Dim sSQL1 As String
    Dim sSQL2 As String
    Dim sSQL3 As String
    Dim lRandom As Long
    Dim lcRecords As Long
    On Error Resume Next
    db.QueryDefs.Delete "temp1"
    On Error GoTo 0
    sSQL1 = "SELECT Room FROM Assignment" & _
    "WHERE (Name = [Enter Name])"
    sSQL2 = "SELECT Room FROM Assignment" & _
    "WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name
    FROM Assignment))"
    sSQL3 = sSQL1 & " UNION " & sSQL2
    Set qry1 = db.CreateQueryDef("temp", sSQL3)
    sName = InputBox("Enter name")
    qry1.Parameters(0) = sName

    ' لمشاهدة النتائج
    ' 'DoCmd.OpenQuery qry1.Name
    Set rs = qry1.OpenRecordset
    ' Populate and get recordCount
    rs.MoveLast
    lcRecords = rs.RecordCount

    ' Random record
    Randomize Timer
    ' lRandom is between 0 and lcRecords-1
    lRandom = Int(lcRecords * Rnd)
    rs.MoveFirst
    rs.Move lRandom
    MsgBox "Room for " & sName & " is " & rs!Room
    End Sub

    زمن الإنجاز : Time to Completion
    سنعرض هنا مشكله بسيطه حول تحديد الزمن الكلي لإنجاز مهمه ما . يعرض الجدول ( 1- 8) حالة إنتاج سلعه في الشركه التي تعمل بها . يقوم العمال في المراحل المختلفه لعملية الإنتاج بإدخال سجل في الجدول للاشاره الى الزمن المتبقي لإنها السلعه .
    نريد تحديد السلع التي لم تنته بعد .
    الحل : Solution
    ان عبارة SQL التاليه ستقوم بالعمل المطلوب . لاحظ استخدام نموذج Not In في الاستعلام الفرعي .

    SELECT DISTINCT WidgeID FROM Widgets As W1 
    WHERE 0 NOT IN (SELECT TimeToCompletion FROM Widgets As W2
    WHERE W2.WidgetID = W1.WidgetID)


    Posted Image
    زمن الانجاز2 : Time To Completion2
    دعنا نقوم بتعقيد مشكلة الانجاز قليلا . تخيل ثانية أنك تتابع حالة إنتاج السلع في الشركة . تتألف كل سلعة من أربعة أجزاء , يتم تجميع كل منها على حدة . يتضمن الجدول (9-1) عينة من البيانات .
    Posted Image
    سنحاول هنا تعيين أو تحديد السلع التي يكون فيها الجزء الأول (Module 1 ) هو الجزء الوحيد الكامل – أي أن قيمة
    Time To Completion تساوي الصفر من أجل الجزء الأول , ولكن مختلفة عن الصفر من أجل الأجزاء الأخرى في السلعة .
    الحل : Solution
    لنأخذ عبارة SQL التالية :

    SELECT WidgetID 
    FROM Widgets AS W1
    WHERE (TimeToCompletion = 0) AND 0 NOT IN (SELECT TimeToCompletion FROM Widgets AS W2
    WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID < > 1))


    يحدد الاستعلام الفرعي من أجل سلعة معينة , جميع قيم Time To Completion لجميع الأجزاء باستثناء الجزء الأول . تستطيع بعد ذلك إجراء اختبار لرؤية فيما إذا كانت تلك المجموعة من أزمنة الانجاز Time To Completion تتضمن القيمة صفر .
    ويتضمن المثال (1-5) الكود الكامل .
    المثال (1-5) : مثال Time To Completion

    Private Sub TimeToCompletion( ) 
    Dim db As Database
    Set db = CurrentDb
    Dim qry1 As QueryDef
    Dim sSQL1 As String
    On Error Resume Next
    db.QueryDefs.Delete "temp1"
    On Error GoTo 0
    sSQL1 = "SELECT widgetID FROM widgets As W1" &_
    " WHERE (TimeToCompletion = 0) AND" & _
    " 0 NOT IN" & _
    " (SELECT TimeToCompletion FROM Widgets AS W2" & _
    "WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID <> 1) )"
    Set qry1 = db.CreateQueryDef("temp1", Ssql1)
    DoCmd.OpenQuery qry1.Name
    End Sub


    زمن الانجاز III مسألة الحد الأعظم والحد الأدنى : Time To Completion III a MaxMin Problem
    دعنا نضيف مزيداً من التعقيد على مسألة زمن الانجاز . لنفترض أن كل جزء مؤلف من عدة أقسام . وسيقوم العمال المكلفين بقسم معين بإدخال قيد في جدول قاعدة البيانات وبشكل دوري , وذلك كما هو مبين في الجدول (1-10) .
    ونرغب هنا بحساب زمن انجاز كل قسم , وجزء وسلعة . لاحظ أنه يمكن أن يكون هناك عدة قيود من أجل قسم معين . وزمن الانجاز اللازم لقسم معين هو الحد الأدنى للأزمنة في هذه الصفوف .
    Posted Image
    الحل الاول : Solution1
    دعنا نعتمد طريقة تقوم على مبدأ الخطوة خطوة . وتستطيع لاحقاً عرض حل أكثر أناقة ولكنه أكثر صعوبة للفهم والقراءة .
    نقوم أولا بإنشاء عبارة SQL تعيد فقط صفوف الجدول لكل سلعة / جزء التي لها أصغر زمن انجاز للقسم
    Time To Completion . ونستطيع القيام بذلك على مرحلتين . تقوم أول عبارة SQL
    بتحديد حقل Time To Completion لجميع السجلات في المجموعة Widgets والتي لها قيم معينة
    لكل من Widget ID , Module ID , Part ID .

    ' Time to completion for given WidgetID/Modul*ID/PartID
    sSQL1 = "SELECT TimeToCompletion FROM Widgets2 As W2" & _
    " WHERE (W2.WidgetID = W1. WidgetID)" & _
    " And (W2.ModuleID = W1.ModuleID)" & _
    " And (W2.PartID = W1.PartID)"


    أما عبارة SQL الثانية فتعيد جميع السجلات التي يكون فيها Time To Completion أصغر أو مساوٍ لجميع السجلات التي تمت إعادتها في عبارة SQL الأولى , أي جميع السجلات المتعلقة بقيم
    معينة Widget ID , Module ID , Part ID .

    ' Those records that have minimum time to completion for each part 
    sSQL2 = "SELECT WidgetID, ModuleID, PartID," & _
    " TimeToCompletion AS TimeToFinishPart FROM Widget2 AS W1" & _
    " WHERE TimeToCompletion <= ALL (" &sSQL1&")"


    هناك طريقة أخرى تقوم على استخدام عبارة Select متشابكة وحيدة :

    sSQL2 = "SELECT WidgetID, ModuleID, PartID," & _ 
    " (SELECT MIN(TimeToCompletion)" & _
    " FROM Widgets2 as W2 WHERE" & _
    " (W2.WidgetID = W1.WidgetID) And" & _
    " (W2.ModuleID = W1.ModuleID) And" & _
    " (W2.PartID = W1.PartID)" & _
    " AS TimeToFinishPart" & _
    " FROM Widgets2 AS W1


    بتنفيذ هذا الاستعلام :

    Set qry1=db.CreateQueryDef("temp1", sSQL2) 
    DoCmd.OpenQuery qry1.Name


    سنحصل على الجدول (1-11) .
    Posted Image
    وباستخدام الاستعلام التالي يصبح من السهل الحصول على زمن الانجاز لكل جزء .

    ' Time to finish each module 
    sSQL3 = "SELECT WidgetID, ModuleID," & _
    " Max(TimeToFinishPart) AS TimeToFinishModule FROM " & qry1.Name & _
    " GROUP BY WidgetID, ModuleID"
    Set qry2 = db.CreateQueryDef("temp2", sSQL3)
    ' Show it
    DoCmd.OpenQuery qry2.Name


    يتضمن الجدول (1-12) نتائج تنفيذ هذا الاستعلام .
    Posted Image
    أخيراً نستطيع حساب زمن الانجاز لكل سلعة :

    ' Time to finish each Widget 
    sSQL4 = "SELECT WidgetID," & _
    " Max(TimeToFinishModule) AS TimeToFinishWidget FROM " & _
    qry2.Name & " GROUP BY WidgetID"

    Set qry3 = db.CreateQueryDef("temp3", sSQL4)


    والنتائج مبينة في الجدول (1-13) .
    Posted Image
    الحل الثاني : Solution2
    من الممكن الحصول على زمن الانجاز باستخدام عبارة SQL وحيدة , رغم أننا لا ننصح بذلك .
    قد تكون النتائج أكثر أناقة , ولكنها صعبة القراءة . سيكون لدينا مثلاً من أجل الأجزاء (Modules) :

    sSQL1 = "SELECT DISTINCT WidgetID, ModuleID, " & _ 
    " TimeToCompletion FROM Widgets2 AS W1" & _
    " WHERE TimeToCompletion = " & _
    " (SELECT MAX(TimeToCompletion) FROM Widgets2 As W2" & _
    " WHERE TimeToCompletion = " & _
    " (SELECT MIN(TimeToCompletion) FROM Widgets2 As W3" & _
    " WHERE (W3.WidgetID = W2.WidgetID) " & _
    " And (W3.ModuleID = W2.ModuleID) " & _
    " And (W3.PartID = W2.PartID)" & _
    " Group BY W3.WidgeetID, W3.ModuleID, W3.PartID)" & _
    " AND (W2.WidgetID = W1.WidgetID)And(W2.ModuleID = _
    " W1.ModuleID)" & GROUP BY W2.WidgetID, W2.ModuleID)"


    إن هضم عبارة SQL هذه سيكون صعباً ويتطلب وقتاً أطول , ونرجو أنها ستجعلك تعيد النظر باستخدام مثل هذه العبارات في تطبيقاتك المستقبلية .

    عامودي الى افقي : Vertical to Horizontal
    تخيل ان لديك قاعدة بيانات إحصائيات شخصية مع جدولين ( الجدول 1-14 ) والجدول 1-15 )
    Posted Image
    لاحظ ان للجدول (1-15) صف واحد لكل احصاء . وهكذا فإن البيانات المتعلقة بشخص معين مرتبة عاموديا . ولاحظ ايضا ان بعض البيانات ناقصة على سبيل المثال لا يوجد بيانات من اجل " زهــره " . نريد الان عرض البيانات افقيا كما في الجدول (1-16)
    Posted Image
    الحل : Solution
    تقدم لنا عبارة SQL التالية حلا لهذه المسألة :

    SELECT DISTINCT Employees.EmpID, Name, 
    (SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType 'Age') And (T2.EmpID = Employees.EmpID)) As Age,
    (SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType Height) And (T2.EmpID = Employees.EmpID)) As Height,
    (SELECT Value FROM EmployeesData AS T2 WHERE (T2.StatType Weight) And (T2.EmpID = Employees.EmpID)) As Weight,
    FROM (Employees INNER JOIN EmployeesData ON Employees.EmpID= EmployeesData.EmpID


    لدينا هنا عدة عبارات استعلام فرعي Select ضمن عبارة Select الرئيسية . وتقوم العبارة التالية :

    (Select Value From EmployeesData As T2 WHERE (T2.StatType='Age') And (T2.EmpID= Employees.EmpID)) As Age




    بتحديد العمر للشخص المحدد في ظرف أو عبارة Select الرئيسية . وكلما ازداد عدد الاحصائيات تصبح عبارة SQL اكثر تعقيدا . يعرض المثال ( 1-16 ) حلا بديلا بإستخدامADO ولا يتطلب تعديل لدى ازدياد الإحصائيات .
    المثال ( 1-16 ) مثال : Vertical to Horizontal

    Private Sub VerticalToHorizontal2( ) 
    Dim db As Database
    Set db = CurrentDb
    Dim rsEmp As Recordset
    Dim rsData As Recordset
    Dim rsHor As Recordset
    Set rsEmp = db.OpenRecordset("Employees")
    Set rsHor = db. OpenRecordset("EmployeesOutput")
    Do While Not rsEmp.EOF
    Set rsData = db.OpenRecordset( _
    "SELECT * FROM EmployeesData WHERE EmpID = " & rsEmp!EmpID)
    rsHor.AddNew
    rsHor!EmpID = rsEmp!EmpID
    rsHor!Name = rsEmp!Name
    Do While Not rsData.EOF
    rsHor.Fields(rsData!StatType).Value = rsData!Value
    rsData.MoveNext
    Loop
    rsHor.Update
    rsEmp.MoveNext
    Loop
    rsEmp.Close
    rsData.Close
    rsHor.Close
    End Sub


    مسألة تطابق : A Matching Problem
    يتضمن الجدول ( 1-17 ) مبرمجين ولغات البرمجمة التي يستخدمونها . أما الجدول ( 1-18 ) فيحدد متطلبات اللغة من اجل اعمال مختلفة . نريد هنا عرض قائمة بالاعمال والمبرمجين المؤهلين للقيام بها .
    Posted Image

    Posted Image
    الحل : Solution
    تقدم لنا عبارة SQL التالية أحد الحلول :

    SELECT ProgrammingJobs.JobID, Programmers.Name 
    FROM Programmers INNER JOIN ProgrammingJobs ON Programmers.Language = ProgrammingJobs.Language
    GROUP BY ProgrammingJobs.JobID, Programmers.Name
    HAVING Count(Programmers.Language)=
    (SELECT Count([Language]) FROM ProgrammingJobs AS pj
    WHERE pj.JobID= ProgrammingJobs.JobID)


    نبدأ بربط داخلي Inner Join للجدولين بإستخدام حقل Language ومن أجل كل زوج Programmer/Job تنشئ علاقة الربط الداخلي مجموعة من السجلات كما في الشكل

    JobID X      Language1-ProgrammerName Y 
    JobID X      Language2-ProgrammerName Y
    JobID X      Language3-ProgrammerName Y


    حيث يتطلب العمل للغة والمبرمج مهارة بتلك اللغة .
    نحتاج الان ان نضمن لكل زوج عمل / مبرمج بأن يكون عدد من هذه السجلات هو نفس عدد اللغات المطلوبة لذلك العمل . ويتم ذلك عن طريق تحميع السجلات وفق الازواج عمل / مبرمج ومن ثم استخدام ظرف Having الذي يقارن عدد هذه السجلات مع عدد اللغات لذلك العمل .والنتيجة هي ما يتضمنه الجدول الجدول (1-19)
    Posted Image

    تساوي المجموعات : Equality of Sets
    من المسائل الشائعة تعيين متى تتساوى مجموعتان أي متى يكون لهما نفس العناصر . لنأخذ الجدول ( 1-20 ) الذي يعرض خمس مجموعات وعناصرها . لاختصار المسألة وتبسيطها نقوم بترقيم المجموعات ونفترض انها تتضمن ارقاما بحد ذاتها . ونريد الحصول على قائمة بالمجموعات المتساوية .
    Posted Image
    الحل : Solution
    لهذه المسألة حل أنيق بإستخدام عبارة SQL وحيدة . بينما لا يسمح SQL بشكل عام بمقارنة مجموعتين مباشرة كما في العبارة :

    (Select Members From Equality Where Set=1)= (Select Members From Equality Where Set=2)


    لكنه يقبل كثل هذه العبارة اذا كانت عبارات Select تعيد قيمة وحيدة . لنأخذ الان عبارة SQL التالية :

    SELECt Equality.Set, E2.Set 
    FROM Equality INNER JOIN Equality AS E2 ON (Equality.Member = E2.Member) And (Equality.Set < E2.Set)
    GROUP BY Equality.Set, E2.Set
    HAVING
    ((SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=Equality.Set) =
    (SELECT Count(Member)FROM Equality As E3 WHERE E3.Set=E2.Set)) AND (Count(Equality.Set) =
    (SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))



    ان علاقة الربط الداخلي Inner Join هي :

    (Equality.Member = E2.Member) And (Equality.Set < E2.Set)


    ان الجزء الهام في هذه العبارة هو الجزء الاول . انه يفترض بأننا نريد جميع ازواج المجموعات التي لها عضو مشترك . ويمنع الجزء الثاني من العبارة اعادة ازواج متكررة . على سبيل المثال اذا تضمنت المجموعتان 1 و 2 العضو 3 فإننا لا نريد استرجاع الزوج ( 1-2 ) والزوج ( 2-1 ) . ولبيان ذلك بشكل اوضح بما ان العضو 3 مجوجود في المجموعات 1 ، 2 ، 3 ، 4 فإن السجلات المستعادة للعضو 3 هي على النحو التالي :
    ( 2-1 ) ( من العضو 3 )
    ( 3-1 ) ( من العضو 3 )
    ( 4-1 ) ( من العضو 3 )
    ( 3-2 ) ( من العضو 3 )
    ( 4-2 ) ( من العضو 3 )
    ( 4-3 ) ( من العضو 3 )
    واذا لم يكن الجزء Equality.Set < E2.Set موجودا فإننا كنا سنحصل على ( 1-1 ) و ( 2-2 ) ..... ( 4-4 ) بالاضافة الى ( 1-2 ) و ( 1-3 ) وهكذا .
    يمكننا الان ان نطرح السؤال التالي : كم مره سيظهر زوج مجموعة ما ؟ ان زوج المجموعة ( 2-1 ) مثلا سيظهر بعدد المرات التي يوجد فيها عناصر مشتركة بين المجموعتين . أي انه سيظهر بعدد مرات يكافئ حجم التقاطع بين المجموعتين .
    لذلك اذا قمنا بالتجميع حسب ازواج المجموعات ( Group By ) نستطيع فحص التقاطعات وتحديد حجم النتائج المسترجعة بإستخدام ظرف Having . ان هذا الظرف يقول بأن المجموعتين متساويتين . ولكن تتساوى المجموعتان A و B اذا كان حجمهما وتقاطعهما هو نفسه ان العبارة :

    (Select Count(Member) From Equality As E3 WHERE E3.Set=Equality.Set)= 
    Select Count(Member) From Equality As E3 WHERE E3.Set=E2.Set)


    تقول انه من اجل زوج مجموعات معين ( Equality.Set , E2.Set ) من العبارة الرئيسية Select فإن حجم Equality.Set مساوي لحجم E2.Set . اما العبارة :

    Count(Equality.Set)= (Select Count(Member) From Equality As E3 
    WHERE E3.Set=E2.Set)


    فتقول بأن حجم تقاطع Equality.Set و E2.Set هو نفسه ومماثل لحجم E2.Set وهذا هو المطلوب .

    في الختام اعزائي الكرام اسأل الله العلي القدير ان يوفقنا واياكم لما يحبه ويرضاه واتمنى من الجميع بعد قراءة هذا الموضوع العمل على تطبيقه عمليا بحيث تأخذ كل مثال وتحاول تطبيقه على قاعدة بيانات لمعرفة كيفية الاستفادة من الحلول المقترحه وقد تخرج بفكره جديده لم تطرح وبهذا تكون قد اصطدت عصفورين بحجر واحد منها الاستفادة ومنها التطبيق .
    اختكم
    زهـره
    المصدر من كتاب : Some Common Data Manipulation Problems

     

    كلمات مفتاحية  :

    تعليقات الزوار ()