
جزوه آموزشی SQL SERVER - (E – R) Entity – Relationship Model
(E – R) Entity – Relationship Model
اين مدل ، مدل نهاد – رابطه ميباشد. بطور كلي براي تحليل يك Data Base شش مرحله بايد گذرانده شود.
- تحليل نيازمنديها (Requirement Analysis) : بطور كلي در اين مرحله شناخت اوليه سيستم صورت ميگيرد.
- طراحي مفهومي (Conceptual Design of D.B) : طراحي اولي E - R در اين مرحله صورت ميگيرد.
- طراحي منطقي (Logical Database Design) : به روش Schema صورت ميگيرد.
نكته : اگر در اين سه مرحله اشكالاتي وجود داشته باشد سه مرحله بعد اين اشكالات را تصحيح مينمايد.
- تحليل و تصحيح (پالايش) (Refinement Analysis) : در اين مرحله نرمالسازي (Normalize) صورت ميگيرد.
- طراحي فيزيكي (Physical D.B. Design) : در اين مرحله چگونگي ذخيرهسازي اطلاعات انجام ميشود زيرا نحوه ذخيره و بازيابي اطلاعات به اين امر بستگي دارد. در اين مرحله كاربر دخالتي ندارد و سيستم اين طراحي را انجام ميدهد. سه روش انديسگذاري (Hashing - Btree - Bt) نيز در اين مرحله صورت ميگيرد. بخشپذيري (Clustering) نيز در اين مرحله انجام ميشود يعني هرگاه حجم table از يك حد بيشتر گردد كارهاي مختلف در آن زمانبر خواهد بود (كارهايي از قبيل جستجو ، حذف و اضافه و ..) براي اينكه بتوانيم اين اعمال را در يك زمان بهينه انجام دهيم از اين عمل استفاده ميكنيم يعني table را به چند بخش تقسيم ميكنيم. اين مرحله مشخص ميكند عمل بخشپذيري كي و چگونه انجام شود.
- طراحي امنيتي (Security Design) : يكي از كارهاي انجام شده در اين مرحله تعيين سطوح دسترسي (كنترل دسترسي) كاربران به اطلاعات ميباشد. براي اينكار براي هر كاربر يك نقش (Role) تعريف ميگردد و با توجه به وظايف و موقعيت هر شخص ، سطوح دسترسي براي آن تعريف ميگردد. بعنوان مثال يك مدير عامل ، مدير مالي ، كارمند و غيره هر كدام موقعيت مختلف و متعاقباً سطح دسترسي مختلفي را دارند. به اين عمل امنيت مبتني بر متن (Role Based Security) ميگويند.
(Entity and Entity set) (نهاد و مجموعه نهاد)
هر شيئي كه بتوان آنرا بطور مستقل طراحي كرد نهاد ميگويند. مثلاً اطلاعات دانشجويي مانند نام ، آدرس ، نمره ، كلاس ، استاد ، كتابخانه ، بوفه ، آزمايشگاه و ... هر كدام يك نهاد هستند ولي بايد از بين اين نهادها ، نهادهاي باارزش را جدا كرد. مثلاً دانشجو يك نهاد باارزش است در حاليكه بوفه يك نهاد باارزش نيست. كلاس يك نهاد باارزش است ولي كتابخانه نهاد باارزش نيست. به مجموعه نهادهايي كه داراي ويژگي يكسان و كاربرد يكسان هستند مجموعه نهادها ميگويند. بعنوان مثال دانشجويان ، دروس ، اساتيد و كلاسها هر كدام يك مجموعه نهاد را تشكيل ميدهند.
صفت (Attribute) : ويژگيهايي است كه يك نهاد را توصيف ميكند. مثلاً نام ، نام خانوادگي ، شماره دانشجويي ، معدل و ... مشخصات يك دانشجو را تعيين ميكنند.
نكته : بعضي از صفات هستند كه ويژگي يكتايي (Unique) دارند. مثلاً كد دانشجويي كه فقط براي يك دانشجوي خاص است ولي نام ممكن است در بسياري از دانشجويان تكراري باشد. به اين ويژگي يكتايي ، كليد (Key) ميگويند. از ميان كليدها ، به كليدهايي كه حتماً همه آنرا دارا هستند كليدهاي كانديد (Candidate Key) ميگويند. از ميان اين كليدها يكي را انتخاب كرده و به آن كليد اصلي (Primary Key) ميگويند.
Relationship & Relationship Set
Relationship : ارتباطی که بین دو نهاد (entity) مشخص برقرار می شود.
Relationship Set : چند تا Relationship ای که بتوانند در یک مجموعه قرار گیرند را Relationship Set می نامند.
Relationship هم می تواند دارای attribute باشد به این attribute، descriptive attribute گفته می شود.
Employees |
Department |
SSN |
Name |
Lot |
Since |
did |
add |
budget |
dname |
Work IN |
مثال :
1215 1314 1208 1301 |
1380/12/11
1378/1/1 |
|
12 |
13 |
14 |
15 |
اداره مربوطه تاريخ كاركرد شماره كارمند
فرض کنید department های مختلف در شهرهای مختلف باشد حال باید رابطه را سه تایی کنیم :
Lot |
Employees |
Department |
SSN |
Name |
Since |
did |
add |
budget |
dname |
Work IN |
Location |
add |
Capacity |
به اين ارتباط ، ارتباط سه گانه
(Ternary) ميگويند.
Key constraints (محدودیت های بین کلید) : ما چندین حالت ارتباط داریم :
1) ارتباط یک به یک 2) ارتباط یک به چند 3) ارتباط چند به چند
رابطه مديريت : حال ميخواهيم رابطه بين مديريت و كارمندان يك اداره و يك Department را بررسي كنيم. يك فرد ميتواند مدير چند Department باشد ولي يك Department نميتواند چند مدير داشته باشد. به اين حالت مديريت رابطه يك به چند (One to Many) ميگويند و در حالت ديگر ميبينيم كه يك كارمند ميتواند در چند Department كار كند و بالتبع يك Department نيز ميتواند چندين كارمند داشته باشد. به اين حالت نيز مديريت رابطه چند به چند (Many to Many) ميگويند.
Lot |
Employees |
Department |
SSN |
Name |
Since |
did |
add |
budget |
dname |
Work IN |
Since |
Manage |
رابطه گفته شده ميتواند به اين طريق گفته شود كه همه كارمندان قطعاً مدير نيستند ولي يك Department حتماً داراي يك مدير است پس فلش مربوطه را بايد توپر كنيم. همه كارمندان قطعاً در يك Department كار ميكنند و يك Department قطعاً داراي كارمنداني نيز هست پس بايد رابطه اين دو نيز بصورت توپر مشخص گردد.
Weak Entity (نهاد ضعيف)
نهادي است كه در آن نتوان يك Primary Key تعيين كرد.نهادی که وجودش وابسته به نهاد دیگری باشد . اگر نهاد اصلی از بین برود نهادهای ضعیف مرتبط با آن نیز از بین می روند. مثلاً يك نهاد بعنوان كارمندان را در نظر ميگيريم. ميخواهيم يك رابطه قرارداد بيمه برقرار كنيم. يك كارمند افراد تحت تكفل خود را بيمه ميكند. در بين افراد خانواده مشخصاتي را در نظر ميگيريم و بين اين افراد ميتوان نسبتاً يك Primary با مشخصه نام تعريف كرد كه به آن Primary Key ميگويند. بنابراين افراد تحت تكفل را بعنوان يك Weak Entity در نظر گرفته و رابطه آنرا با كادر پررنگ مشخص ميكنيم.
Lot |
Emplaces |
Dependencies افراد تحت تكفل |
SSN |
Name |
Pname |
Age |
Cast |
Policy |
Weak entity نهاد ضعیف |
Owner entity نهاد مالک |
يك كارمند ميتواند چندين نفر از خانواده خود را بيمه نكند ولي تمام بيمه شدگان قطعاً افراد تحت تكفل كارمند هستند و به اين رابطه يك رابطه يك به چند ( از جهت فلش می فهمیم) ميگويند.نهاد ضعیف به صورت total بیان می شود. (یعنی خط پررنگ است)
* نهاد ضعیف هم خودش پررنگ می شود و هم رابطه اش. Pname چون خط چین دارد خودش کلید اصلی نیست ولی به همراه کلید اصلی نهاد مالک می تواند به عنوان کلید اصلی در نظر گرفته شود.
Class Hierarchy (سلسله مراتب كلاسها)
باز هم مثال كارمندان يك اداره را در نظر ميگيريم. در يك اداره يا سازمان افراد به چند طريق مشغول بكار هستند. به عبارت ديگر نوع استخدام آنها ميتواند رسمي ، قراردادي ، شركتي و يا ... باشد. هر كدام از اين گروه داراي يك سري ويژگي فردي خاص خود هستند ولي ويژگي نيز وجود دارد كه همه كارمندان ميتوانند در آن شريك باشند. ما بايد يك سري ويژگي را براي كارمندان خاص در نظر گرفته و از يكديگر جدا كنيم.
ISA |
اگر حالتی وجود داشت که می توانستیم برخی نهادها را به صورت مجتمع در قالب کلاس پدر و ارث بری از آن تعریف کنیم از نماد استفاده می کنیم.
Lot |
Emplaces |
EMP - Contractor كارمندان قراردادي |
SSN |
Name |
Parry - Hour |
ISA |
EMP - Hour كارمندان ساعتي |
Data - Cont |
Data - Exp |
Specialized |
Generalized |
دستمزد ساعت كار
حالتي وجود دارد كه ما ميتوانيم يك سري ويژگي خاص را با يك سري ويژگي مشترك مرتبط كنيم كه در اينصورت دو حالت Generalized و Specialized وجود خواهد داشت. اگر در مثال بالا از اجتماع دو گروه از كارمندان قراردادي و ساعتي ، حالت اصلي (كلاس پدر) بوجود آيد به آن
Overlap Constraints گويند و اگر از اجتماع آن دو گروه از كارمندان ، كلاس پدر بوجود نيايد به آن Covering Constraints ميگويند.
Aggregation (جمع كردن ، اجتماع كردن)
در حالات گفته شده قبلي ارتباط بين دو نهاد رخ ميداد ولي در اين حالت بين يك نهاد و يك Relation ارتباط برقرار ميكنيم.
Data |
Project |
Department |
pid |
budget |
did |
budget |
dname |
Sponsor |
Cost |
Employees |
Monitor نظارت |
Lot |
SSN |
Name |
حال در اينجا حالات يك پروژه را در نظر ميگيريم. يك پروژه حداكثر توسط يك Department ساخته ميشود ولي ممكن است چند پروژه بوسيله يك Department ساخته شود. پس رابطه چند به يك است. حال ممكن است تعدادي كارمند از يك Department مسئول نظارت بر Sponsor باشند پس بايد پروژه Department را در يك پكيج قرار دهيم.
مثال : يك سيستم فروش را در نظر ميگيريم. فروشنده كلي و فروشنده جزئي داريم. فروشنده كل كالايش را به فروشنده جزء ميفروشد و فروشنده جزء نيز كالا را به مشتري ميفروشد. E - R آنرا ترسيم كنيد.
نام خانوادگي |
مردم |
فروشنده جزئي |
آدرس |
فروشنده كلي |
فروش كالا |
آدرس |
نام |
بودجه |
نام |
آدرس |
شماره فروشندگي |
فروش كالا |
كالا |
تاريخ انقضا |
نام |
تاريخ توليد |
- فروشنده كلي حتماً فروش دارد.
- فروشنده جزئي حتماً خريد دارد.
- فروشنده كلي ميتواند به چندين فروشنده جزئي جنس بفروشد.
- فروشنده جزئي ميتواند از چندين فروشنده كلي جنس بخرد.
- فروشنده جزئي حتماً بايد مشتري داشته باشد.
- مردم ميتوانند خريدار باشند ميتوانند نباشند ولي حتماً از فروشنده جزئي جنس ميخرند.
سؤال1 : وقتي كه ميخواهيم يك E - R ترسيم كنيم از كجا بايد بفهميم كه يك موجوديت را Entity در نظر بگيري يا Attribute؟
براي جواب دادن به سؤال فوق مثال كارمندان يك اداره را در نظر ميگيريم. هر كارمند يك سري ويژگي دارد. مانند آدرس. حال اگر يك كارمند بيش از يك آدرس داشته باشد نميتوانيم بيش از يك آدرس را در نظر بگيريم. پس بنابراين آدرس را بايد به قسمتهاي مختلف مانند كشور ، استان ، شهر ، خيابان و پلاك تقسيم كرده و خود آدرس را نيز يك Entity در نظر بگيريم.
نواقصي كه در اين نمودارها وجود دارد
- بيش از يك آدرس نميتوانيم در نظر بگيريم.
- پرس و جوهاي آدرس را نميتوانيم در نظر بگيريم.
بنابراين بايد تصميم ديگري گرفته كه در اين حالت به نيازمنديهاي سيستم (Requirement) بپردازيم.
پس بنابراين ميتوانيم آدرس را بعنوان يك Entity در نظر گرفته و E - R مربوطه را بدينصورت ترسيم نمائيم.
كليد اصلي كليد اصلي
EMP |
Add |
SSN |
Name |
Lot |
Since |
aid |
aname |
Street |
Num |
Mas - add |
add |
همين مثال كارمندان يك اداره را در نظر ميگيريم و كاركرد آنها را در جاهاي مختلف بررسي ميكنيم.
EMP |
DEP |
SSN |
Name |
Lot |
From |
did |
name |
budget |
Work |
To |
فرض ميكنيم كه يك كارمند از زمان 1 تا 2 در يك اداره كار ميكرده است. از زمان 2 تا زمان 4 به يك اداره
ديگر منتقل شده و از زمان 4 تا زمان 6 نيز مجدداً به اداره اول برگشته است. در اين حالت داريم :
R1 |
2 y
R2 |
x 2 4 z
R3 |
4 6
همانطور كه از حالت فوق پيداست كارمند x در بازه زماني R1 با y در تماس بوده و مشغول بكار بوده است و در بازه زماني R2 به محل z رفته و در بازه زماني R3 نيز مجدداً با y در تماس بوده است كه اين امكانپذير نيست.
در واقع هر كارمند نميتواند در بيش از يك دوره زماني در يك محل كار كند.
حال بر اساس نيازمندي اگر بخواهيم در نظر بگيريم كه يك كارمند بتواند در بيش از يك دوره زماني در يك محل كار كند بايد E - R فوق را به اين صورت تكميل كنيم.
Duration |
budget |
uid |
EMP |
DEP |
SSN |
Name |
Lot |
From |
did |
name |
budget |
Work |
To |
سؤال2 : حال ميخواهيم ببينيم چگونه يك موجوديت را Entity در نظر بگيريم يا Relationship؟
E - R زير را در نظر ميگيريم.
EMP |
DEP |
SSN |
Name |
Lot |
Since |
did |
name |
budget |
margins |
budget |
هر مدير به ازاي هر مديريت بودجهاي را در اختيار دارد. بودجه هر مدير ميتواند بصورت جداگانه و يا مجموع محاسبه گردد. يعني اينكه براي مديريت كارمند x بودجه معين و براي مديريت ديگر نيز بودجهاي ديگر در نظر گرفته ميشود كه اين حالت بودجه جداگانه گفته ميشود ولي ممكن است اين بودجه بصورت مجموع باشد يعني براي كل مديريتها يك بودجه در نظر گرفته شده و اين بودجه در بين مديران يك اداره تقسيم شود. در اينصورت E - R بالا را بصورت زير تغيير ميدهيم.
Mgr - appts |
mid |
budget |
Since |
EMP |
DEP |
SSN |
Name |
Lot |
Since |
did |
name |
budget |
margins |
budget |
مثال : دانشگاهي داراي پايگاه دادهاي است كه در اين پايگاه داده اطلاعاتي درباره اساتيد (شماره شناسايي) و درسها (شناسه درسي) وجود دارد. اساتيد درسها را درس ميدهند. در هر يك از موارد ذيل E - R مربوطه را رسم كنيد.
- اساتيد ميتوانند دروس مشابه را در چندين ترم تدريس كنند و هر يك از موارد تدريس بايد ذخيره شود.
نيمسال |
ترم |
زمان |
سال |
استاد |
درس |
شماره شناسايي |
نام |
شناسه درسي |
نام درس |
درس ميدهد |
- اساتيد ميتوانند دروس مشابه را در چندين ترم تدريس كنند و فقط دروس تدريس شده اخير نياز به ذخيره دارد.
ترم |
سال |
استاد |
درس |
شماره شناسايي |
نام |
شناسه درسي |
نام درس |
درس ميدهد |
فرض كنيد اين شرايط براي تمام موارد ذيل وجود داشته باشد.
- هر استاد بايد تدريس كند.
ترم |
سال |
استاد |
درس |
شماره شناسايي |
نام |
شناسه درسي |
نام درس |
درس ميدهد |
- هر استاد دقيقاً يك درس تدريس كند نه كمتر و نه بيشتر.
ترم |
سال |
استاد |
درس |
شماره شناسايي |
نام |
شناسه درسي |
نام درس |
درس ميدهد |
نام |
فاميلي |
ترم |
استاد |
درس |
شماره شناسايي |
تدريس گروهي |
ISA |
تيم اساتيد |
نام |
تعداد |
اساتيد |
نام |
شناسه |
تدريس انفرادي |
اكنون فرض كنيد دروس مشخص بتواند بصورت يك تيم از اساتيد تدريس شود اما اين امكان وجود داشته باشد كه هيچ يك از اساتيد موجود در تيم نتوانند يك درس را بصورت مستقل تدريس كنند.
(اساتيد) (تيم اساتيد) استاد
سؤال3 : کی از رابطه دوگانه استفاده کنیم و کی از رابطه سه گانه استفاده کنیم؟
Dependents |
Pname |
covers |
emp |
sid |
نمودار *:
Plicies |
Policyid |
cost |
چند تا Requarment می خواهیم اضافه کنیم :
Policyid |
cost |
Plicies |
Pname |
Dependents |
covers |
sid |
emp |
هر policy (شیوه بیمه) توسط دو یا چند emp مشترکاً نتواند استفاده شود. (محدودیت بین کلید .یعنی این رابطه یک به چند است) . برای این کار می توانیم نمودار * را به صورت زیر عوض کنیم اما یک محدودیت دیگر را به نیازمندیها اضا می کند.(هر فرزندی توسط یک بیمه بیشتر نمی تواند تحت پوشش قرار گیرد.)
- دقیقاً هر policy باید توسط یک کارمند تصاحب شود.(وقتی می گوییم باید یعنی رابطه الزام آور است).در نمودار * ، dependents یک نهاد ضعیف نسبت به emp است. یک رابطه سه تایی است و مجبوریم 2 تایی اش کنیم.
- هر dependents یک نهاد ضعیف نسبت به policy باشد بطوریکه کلید اصلی آن Pname+policyid
Pname |
Dependents |
sid |
emp |
purchase |
|
Beneficiary
|
Plicies |
|
Policyid
|
cost |
* وقتی گفته نهاد ضعیف یعنی Pname و policyid کلید اصلی Beneficiary است.
سؤال4 : کی aggregation استفاده کنیم و کی رابطه سه گانه؟
emp |
emp |
وقتی بخواهیم رابطه ایجاد کنیم بین دو رابطه یا بین یک نهاد و یک رابطه از aggregation استفاده می کنیم.
رابطه مدیریت و پروژه ها :
until |
monitor |
monitor |
dep |
project |
monitor |
Sponsors |
until |
dep |
dep |
project |
emp |
اگر زمان نهایی نظارت را نخواسته باشیم درج کنیم E.R فوق می تواند به صورت رابطه ی سه تایی نیز تنظیم شود. به صورت زیر: *
اگر بخواهیم یک محدودیت جدید اضافه کنیم :
- هر emp حداکثر یک پروژه را نظارت کند.
ولی این محدودیت را نمی توانیم در نمودار * اعمال کنیم . در حالیکه در نمودار زیر می تواند اعمال شود.
emp |
until |
monitor |
dep |
sponsers |
project |
until |
dep |
Project |
تمرين : اطلاعات زير در رابطه با پايگاه داده يك دانشگاه را در نظر بگيريد.
- اساتيد داراي يك شماره شناسايي ، يك نام ، يك سن ، يك رتبه و يك تخصص تحقيقاتي هستند.
- پروژهها داراي يك شماره پروژه ، يك نام اسپانسر ، يك تاريخ شروع ، يك تاريخ پايان و يك بودجه هستند.
- دانشجويان فارغ التحصيل داراي يك شماره شناسايي ، يك نام ، يك سن و يك نوع مدرك تحصيلي هستند.
- هر پروژه بوسيله يك استاد مديريت ميشود.
- هر پروژه بوسيله يك يا چند استاد كار ميشود.
- اساتيد ميتوانند مديريت كنند و يا كار كنند روي چندين پروژه
- هر پروژه بوسيله يك يا چند دانشجوي فارغ التحصيل كار ميشود.
- دانشكدهها داراي يك شماره و يك نام هستند.
- هر دانشكده داراي يك رئيس است.
- اساتيد در يك يا چند دانشكده كار ميكنند و به ازاي كار در هر دانشكده ساعت كار مجزايي براي آنها درج ميشود.
- دانشجويان فارغ التحصيل داراي يك دانشكده هستند كه مدرك خود را از آنجا گرفتهاند.
حال با در نظر گرفتن موارد بالا نمودار E - R اين سيستم را بصورت كامل طراحي كنيد.
تعداد و ساعت |
سال |
ترم درسي |
دانشكده |
اخذ مدرك |
كار كردن در پروژه |
نام |
سن |
رتبه |
نام |
شناسه درسي |
دانشكده |
كار كردن در دانشكده |
كار كردن دانشجويان |
رياست |
شماره شناسايي |
دانشجويان فارغ التحصيل |
مديريت پروژه |
تاريخ پايان |
بودجه |
تاريخ شروع |
نام اسپانسر |
شماره پروژه |
پروژه |
رتبه |
تخصص |
سن |
نام |
شماره شناسايي |
اساتيد |