DB - Lecture 11,12
Download
Report
Transcript DB - Lecture 11,12
مهدی ایل بیگی
دانشگاه پیام نور دماوند
عملگرهاي معمولي
-1اجتماع
-2اشتراك
-3تفاضل
-4ضرب كارتزين
عملگرهاي جبر
رابطهاي
عملگرهاي خاص
-1گزينش (تحديد)
-2پرتو
-3پيوند
-4تقسيم
2
•
چهار عملگر اجتماع ،اشتراک ،تفاضل و ضرب کارتزین بصورت زیر نوشته می شوند:
R1 MINUS R2
R1 INTERSECT R2,
R1 TIMES R2
یا
R1 UNION R2,
R1 PRODUCT R2
•
Type
R1,در عملگرهه ههاا اجتمه ههاع ،اشه ههتراک و تفاضه ههل بایه ههد
نکته ههه R2 :1
Compatibleباشند یعنی مجموعه عنوان (صفات) دو رابطه باید یکسان باشند.
•
نکته :2در عمل ضرب دو رابطه نباید صفت همنام داشته باشند.
•
نکته :3نتیجه اعمال هر یک از عملگهر ههاا قهوو روا رابطهه هها ،خهود یهک رابطهه مهی باشهد.
این خاصیت به خاصیت بسته بودن جبر رابطه اا موسوم است.
3
•
عملگر گزينش (تحدید) تاپل هايي از يك رابطه را گزينش ميكند (سهطرهايي از جهدول
را) .اين عملگر تك عملوندي است و به صورت زير نوشته ميشود:
WHERE
•
)cond(s
) :cond(sشرط يا شرايط گزينش را مشخص ميكند.
•
این عملگر با نماد σنیز نمایش داده می شود.
•
مثال :مشخصات دانشجویی با شماره دانشجویی 132بصورت زیر نوشته می شود:
’WHERE STDID = ‘132
R
1. STT
)2. σ<STDID = ‘132’>(STT
STT WHERE STDID = 132
4
3. SQL: SELECT * FROM
:عملگر گزینش جابجایی پذیر است
•
σcon1(σcon2(R)) = σcon2(σcon1(R))
:در زیر چند عبارت معادل مهم آورده شده است
•
1.R Where c1 AND c2 = (R Where c1) INTERSECT (R Where c2)
2.R Where c1 OR c2 = (R Where c1) UNION (R Where c2)
3.R Where NOT c1 = R MINUS (R Where c1)
5
•
ای ههن عملگ ههر مق ههادیر ص ههفت (ص ههفا ی) را از ی ههک رابط ههه بدس ههت م ههی ده ههد .و بص ههورت زیه هر
نوشته می شود:
)OVER(A1, A2, …, AN
•
این عملگر با ∏ نیز نمایش داده می شود.
•
مثال :عنوان و عداد واحد درس ها را بدست آورید:
R
)COT OVER(CoTitle, Credit
PROJECT
PROJECT
)∏<CoTitle, Credit>(COT
SQL: SELECT CoTitle, Credit From COT
6
نکته :1ساوا زیر زمانی برقرار است که Atts.List2
:Atts.List1
⊂
)∏<Atts.List1>(∏<Atts.List2>(R)) = ∏<Atts.List1>(R
نکته :2ساوا زیر زمانی برقرار است که :Ap є L
))∏<L>(σ<Ap>(R)) = σ<Ap>(∏<L>(R
سوال :در عبارت زیر رابطه Rحاوا چه اطالعا ی می باشد؟
جواب R :شامل شماره تهیه کنندگانی است که قطعه اا تولید نکرده اند.
7
•
عملگر پيوند دو عملوندي است و چنین نوشته ميشود:
R1 JOINcond(s) R2
كه در آن ) cond(sچنین است:
R1.Ai theta R2.Bi
•
Aiيههك صههفت از رابطههه R1و Biيههك صههفت از رابطههه R2اسههت كههه از يههك ميههدان
مق ههدار ميگیرن ههد و باي ههد ناهمن ههام باش ههند theta .یک ههی از عملگره ههاا معم ههول مقایس ههه
)≤ = ،≥ ،و )...می باشد.
•
نیز نمایش می دهنهد .ضهرب کهارتزین را بها × نمهایش مهی دهنهد
عملگر پیوند را با
و خواهیم داشت:
)R2 = σcon1(R1 × R2
8
con1
R1
•
نکت ههه :ازنظ ههر ریاص ه ی ص ههفا ی ک ههه ش ههرط پیون ههد روا آن ه هها اعم ههال م ههی گ ههردد بای ههد ه هم
دامنه و ناهمنام باشند زیرا نتیجه عمل پیوند نیز یک رابطه است پس باید عناوین نها
همنام داشته باشد.
•
اگههر HR1, HR2بترتیههم مجموعههه صههفت هههاا رابطههه R1, R2باشههند ،شههرط
پیوند بصورت مقابل خواهد بود:
•
درنتیجه در هنگام پیوند اگر صفت همنام بین دو مجموعه باشد باید یکهی از آن هها را
غییر نام داد.
•
اگههر Rرابطههه نتیجههه پیونههد R2
داشت:
R1,باشههد ب هراا مجموعههه عنههاوین Rخههواهیم
•
نکته :ساوا زیر برقرار است:
9
R1
C
R2 = R2
C
R1
•
دو رابطه صفت هم نام دارند پس باید یکی را غییر نام داد:
•
نتیجه پیوند:
P
10
-1پیونهد طبییه :Natural Joinدر ایهن پیونهد Thetaعملگهر مسهاوا مهی باشهد و در رابطهه جهواب
نام صفت پیوند (که مشترک است) یک بار می آید.
•
نکته :1اگر HR1 = HR2آنگاه:
•
نکته :2اگر
آنگاه:
•
مثال :مشخصات جفت هاا تهیه کننده-قطعه که در یک شهر هستند را بدهید:
رابطه نتیجه:
11
-2قراپیوند چپ ) :(Left Outer Joinدر این پیوند عهالوه بهر تاپهل ههاا پیونهد شهدنی
از دو رابطه ،تاپل هاا پیوند نشدنی از رابطه چپ ههم ،گسهتری یاقتهه بها هیچمقهدار،
در رابطه جواب ظاهر می شوند.
مثال:
12
-3قراپیونههد راسههت ) :(Right Outer Joinدر ایههن پیونههد عههالوه بههر تاپلهههاا پیونههد
شههدنی از دو رابط ههه ،تاپ ههل ه ههاا پیون ههد نش ههدنی از رابطههه راس ههت ه ههم ،گس ههتری یاقت ههه ب هها
هیچمقدار ،در رابطه جواب ظاهر می شوند.
-4قراپیوند کامل ) :(Full Outer Joinدر این پیوند عالوه بر تاپل ههاا پیونهد شهدنی
از دو رابطههه ،تاپ ههل ه ههاا پیون ههد نشههدنی از رابط ههه چ ههپ و راس ههت هههم ،گس ههتری یاقت هه ب هها
هیچمقدار ،در رابطه جواب ظاهر می شوند.
نکته :در حالت کل عملگرهاا قراپیوند راست و چپ خاصیت جابجایی ندارند.
13
Relation Loan
Relation Borrower
loan_number branch_name amount
L-170
L-230
L-260
•
Downtown
Redwood
Perryridge
customer_name loan_number
Jones
Smith
Hayes
3000
4000
1700
L-170
L-230
L-155
Natural(Inner) Join
Loan Borrower
loan_number branch_name amount customer_name
L-170
L-230
Downtown
Redwood
3000
4000
Jones
Smith
• Left Outer Join
Loan
Borrower
loan_number branch_name amount customer_name
L-170
L-230
L-260
Downtown
Redwood
Perryridge
3000
4000
1700
Jones
Smith
null
14
Relation Loan
Relation Borrower
loan_number branch_name amount
Downtown
Redwood
Perryridge
L-170
L-230
L-260
customer_name loan_number
Jones
Smith
Hayes
3000
4000
1700
L-170
L-230
L-155
• Right Outer Join
Loan
Borrower
loan_number branch_name amount
L-170
L-230
L-155
Downtown
Redwood
null
3000
4000
null
customer_name
Jones
Smith
Hayes
• Full Outer Join
Loan
Borrower
loan_number branch_name amount
customer_name
L-170
L-230
L-260
L-155
Jones
Smith
null
Hayes
Downtown
Redwood
Perryridge
null
3000
4000
1700
null
15
-5عملگههر نههیم پیونههد ) :(Semi Joinدر ایههن نههوع پیونههد تپههها تاپههل هههاا پیونههد شههدنی از
رابط ههه س ههمت چ ههپ در رابط ههه ج ههواب وارد م ههی ش ههوند .نم ههایش ن ههیم پیون ههد بص ههورت زی ههر
است:
مثال:
16
•
Type
رابط ه ههه ه ه ههایی ک ه ههه عملگ ه ههر اجتم ه ههاع ب ه ههر روا آن ه ه هها اعم ه ههال م ه ههی ش ه ههود بای ه ههد
Compatibleباشند یعنی مجموعه عنوان (صفات) دو رابطه باید یکسان باشند.
•
در قرا اجتماع شهرط قهوو بهراا اجتمهاع دو رابطهه الزامهی نیسهت .یعنهی دو رابطهه بها مجموعهه
عنوان ) (Headingمتفاوت می توانند به عنوان عملوند هاا ایهن عملگهر بکهار رونهد وله
باید حداقل صفت کلید در این دو رابطه یکسان باشد.
•
قرم کل این درستور بصورت زیر است:
•
مجموعهه عنهوان رابطهه حاصهل در قهرا اجتمهاع برابهر بها اجتمهاع مجموعهه عنهوان R1, R2
می باشد.
•
در رابطه حاصل ،بجاا مقادیر صفات ناموجود در تاپل ها NULL ،گذاشته می شود.
R1 OUTER-UNION R2
17
Z
X
Y
دو رابطه ) R1(A1, A2,…, An, B1, B2,…, Bmو ) R2(B1, B2,…, Bmرا درنظر بگیریهد.
Y
شرط تقسیم R1بر R2اینست کهZ :
⊆
حاصهل عبهارت ) R1(Z) DIVIDE R2(Yرابطهه ) R3(Xاسهت کهه شهامل تاپهل ههایی مثهل t
اسهت بشهرطی کهه تاپهل tR1بها مقهدار tR1(X) = tو بها مقهدار ،tR1(Y) = tR2بهه ازا تمهام تاپهل ههاا
،tR2در R1وجود داشته باشد.
18
•
مثال :2
•
نکته :در حالت کل ،عملگر تقسیم رابطه اا عکس عملگر ضرب رابطه اا نیست و داریم:
(R1 TIMES R2) DIVIDE R2 ≥ R1
19
•
قرم کل این دستور بصورت زیر است:
•
حاصههل اعمههال ایههن عملگههر بههر روا R1, R2بههدین صههورت اسههت کههه ابتههدا عملگههر نههیم
پیونههد را بههر روا R1, R2اعمههال مههی کنههد و سه س حاصههل ،سههطرهایی از R1خواهههد
شههد کههه شههرط پیونههد در مههورد آن ههها صههادو اسههت .در انتههها بهراا بدسههت آوردن سههطرهایی از
R1که شهرط پیونهد در مهورد آ هها صهادو نیسهت R1 ،را از رابطهه حاصهل از نهیم پیونهد کهم
می کند.
•
در نتیجه عریف این عملگر بصورت زیر می باشد:
R1 SEMIMINUS R2
20
•
از مجموعههه عملگرهههاا معرف ه شههده بر ه از آن ههها مبنهها هسههتند .یعنههی مجموعههه آ ههها از نظ هر
عملیهها ی کامههل هسههتند و مههی تههوان بقیههه عملگرههها را بهها اسههتفاده از آن ههها پیههاده سههازا نمههود.
این مجموعه بصورت زیر است:
}{RESTRICT, PROJECT, UNION, MINUS, TIMES
•
21
در زیر چند مورد از عریف عملگرها با مجموعه باال آورده شده است:
22
23
-1جزئیات کامل تمام پروژه هاا شهر تهران را مشخص نمایید.
-2اسامی تهیه کنندگان قطعه P2را بدهید.
-3اسامی تهیه کنندگانی را بدهید که حداقل یک قطعه آبی تهیه کرده اند.
-4اسامی تهیه کنندگانی را بدهید که تمامی قطعات را تهیه کرده اند.
)
24
-5اسامی تهیه کنندگانی که قطعه P2را تهیه نمی کنند مشخص نمایید.
-6شماره قطعا ی را مشخص کنید که توسط یک تهیهه کننهده یها یهک پهروژه در شههر تههران عرضهه
شده اند.
-7اسامی تمامی مشتریانی را که در شعبه مرکزا وام گرقته اند را بدهید.
-8اسامی تمامی مشترایانی که حداقل یک حساب در تمامی بانک هاا دماوند دارند را بدهید.
25
• این عملگر تک عملوندا است و یک یا چند صفت را به مجموعه عنوان رابطهه اضهاقه مهی
نماید .قرم کل آن بصورت زیر است:
;EXTENT Term ADD Attribute-Name AS Attribute-Name
یا
;EXTENT Term ADD Scalar-Expression AS Attribute-Name
•
Termم ه ه ههی توان ه ه ههد ی ه ه ههک رابط ه ه ههه ی ه ه هها ی ه ه ههک عب ه ه ههارت رابط ه ه ههه اا باش ه ه ههد و بج ه ه ههاا Scalar-
Expressionهههر عبههارت محاسههبا ی را کههه یههک مقههدار برمههی گردانههد مههی تههوان اسههتفاده
نمود.
•
مث ههال :در ج ههدول ) Part (Pوزن ه ههر قطع ههه را ک ههه ب ههه واح ههد پون ههد م ههی باش ههد را ب ههه گ ههرم
محاسبه کرده و ستونی را براا این مقادیر ایجاد نمایید.
EXTEND P ADD (Weight * 454) AS GMWT
26
• ایههن عملگههر تههک عملونههدا اسههت و تاپههل هههاا یههک رابطههه را بههر اسههاس یههک یهها چنههد صههفت گههروه بنهدا
می نماید و سه س محاسهبه اا روا صهفت دیگهرا از ههر گهروه انجهام مهی دههد .قهرم کله ایهن دسهتور
بصورت زیر است:
SUMMARIZE term BY (Attribute(s)) ADD aggregate-expression
;AS Attribute
• termمه ه ه ههی توانه ه ه ههد مه ه ه ههی توانه ه ه ههد یه ه ه ههک رابطه ه ه ههه یه ه ه هها عبه ه ه ههارت رابطه ه ه ههه اا باشه ه ه ههدaggregation- .
expressionیهک تهابج جمیه ) (Aggregation Functionاسهت .ایهن توابهج عبارتنهد از
SUM, AVG, MAX, MIN, COUNTکهه ههر کهدام مهی تواننهد یهک عملیهات حسهابی را
روا مقادیر یک صفت در یک گروه انجام دهند و مقدار حاصل را به آن گروه انتساب بدهند.
• مثههال :معههدل و کمتههرین وریشههترین نمههره هههر دانشههجو را محاسههبه کههرده و در سههه سههتون جداگانهه ارائههه
دهید.
SUMMERIZE STCOT BY (STID) ADD
AVG(GRADE) AS AVERAGE
MIN(GRADE) AS MINGRADE
MAX(GRADE) AS MAXGRADE
27
.جدول زیر نام و شماره حساب و موجودا هر قرد را نگهدارا می نماید
Customer-name Account-number
C1
C1
C2
C2
C3
•
balance
A-102
A-201
A-217
A-215
A-222
400
900
750
750
700
. مشخص نمایید، مقدار موجودا کل هر قرد را در همه حساب ها:پرس و جو
•
SUMMERIZE BY (Customer-name) ADD SUM(balance) AS Sum-balance
Customer-name
Sum-balance
C1
C2
C3
1300
1500
700
28
•
عملگههر Summarizeیههک عملگههر مبنهها نسههیت یعنههی مههی تههوان بهها عملگرهههاا دیگ هر
رقتار آن را شبیه سازا کرد.
•
مثال :عداد دروس اخذ شده توسط هر دانشجو را مشخص نمایید.
;SUMMARIZE STCOT BY (STID) ADD COUNT AS NOFCO
عبارت جبرا قوو را می توان بصورت زیر نوشت:
)(EXTEND STCOT ADD ((STCOT RENAME STID AS SN
)WHERE SN = STID) AS NCO, COUNT(NCO) AS NOFCO
][STID, NOFCO
•
29
نکته :عملگر SUMMARIZEرا با Ϫنیز نمایش می دهند.
-1بازيابي دادهها
-2ذخیرهسازي دادهها (با استفاده از عملگر انتساب ())=:
-3عريف انواع رابطههاي مشتق
-4عريف قواعد براي كنترل پايگاه دادهها (مثال قواعد جامعیتی)
-5عريف دادهها به عنوان حيطه بعض ي عمليات كنترل همروندي تراكنش ها.
-6ضابطه شخيص كامل بودن زران هاي رابطهاي.
30
•
حس ههاب رابط هههاي ،ب هها جب ههر رابط هههاي منطق هها مع ههادل اس ههت ،عن ههي ب هراي ه ههر عب ههارت جب هر
رابطهاي ،يك عبارت معادل در حساب رابطهاي وجود دارد و برعكس.
•
تفهاوت آن ههها ايهن اسههت كههه جبهر رابطهههاي ،دسهتوري ) (Prescriptiveاسههت ،امهها
حساب رابطهاي توصيفي ) (Descriptiveاست.
31
•
حس ههاب ت ههاپلي :در اي ههن حس ههاب مفه ههوم مهم ههي ب ههه ن ههام متغی ههر ت ههاپلي وج ههود دارد ك ههه تپه هها
مقادير مجازی ،تاپل هاي رابطه هستند.
•
ب ه هراا عری ه ههف ی ه ههک متغی ه ههر ت ه ههاپل از دس ه ههتور RANGEVARاس ه ههتفاده م ه ههی ش ه ههود.
بعنهوان مثههال بهراا اینکههه متغیههرا بها نههام STعریههف نمهاییم کههه کههل تاپهل هههاا جههدول
STTرا شامل شود باید بنویسیم:
RANGEVAR ST RANGE OVER STT
•
32
حس ههاب مي ههداني :در اي ههن حس ههاب ،متغی ههر مي ههداني وج ههود دارد ك ههه از ي ههك مي ههدان مق ههدار
ميگیرد .در اين حساب يك شرط اضافي به نام شرط عضويت وجود دارد.
•
در حساب رابطهاي تاپلي دو سور وجود دارد:
-1سه ههور وجه ههودي ) :(EXISTبه ههه صه ههورت
) ( fنوTشهههته ميشه ههود ،به ههه ايه ههن معنه هها كه ههه
حداقل يك مقدار براي متغیر Tوجود دارد به نحوي كه fبه ”درست“ ارزيابي شود.
-2سور همگهاني) :(FORALLبهه صهورت
مقادير متغیر f ،T
33
نوشهته ميشهود .عنهي بهه ازا تمهام
به ”درست“ ارزيابي ميشود T ( f ).
•
اگهر Tیهک متغیهر تهاپل روا ) R(A1, A2, …, Anباشهد ،در ایهن صهورت شهکل
کل عبارات حساب تاپل بشکل زیر است:
] (target-item(s)) [ WHERE f
که در آن ) ،target-item(sقهرستی از صفات متغیر تاپل Tاست.
•
مثال :1شماره تهیه کنندگان ساکن شهر C2و با وضعیت بیشتر از 20را بدهید.
•
مثال :2اسامی تهیه کنندگان قطعه P2را بدهید.
34
•
آزاد یا بسته بودن متغیر تاپل Tبا توجه به قواعد زیر مشخص می شود:
.1در یک عبارت مقایسه اا ساده ) (T.A > S.Aهر نمودا از متغیر تاپل آزاد است.
.2نمودههاا متغیهر تهاپل در قرمهول ههاا f, g, NOT f, f AND g, f OR g, IF f
،Then gبسته به اینکه f, gآزاد یا بسته باشد ،آزاد یا بسته است.
.3نمودهاا Tکه در fآزاد هستند در ) EXIST T(fو ) FORALL T(fبسته هستند.
Y( Y > 10) AND X < 0
≡
Y( Y > 10) AND Y < 0
≠
E
E
35
X(X > 10) AND X < 0
E
•
مثال :در عبهارت زیهر دو نمهود اول بسهته هسهتند و مهی تهوان بجهاا آن هها متغیهر دیگهرا مثهل Yرا قهرار
داد و نمود سوم باز می باشد و با غییر آن متغیر معنی قرمول غییر می کند.
-1شماره جفت تهیه کنندگانی را بدهید که ساکن یک شهر هستند.
-2نام تهیه کنندگانی که دست کم یک قطعه قرمز رنگ تهیه کرده اند را مشخص کنید.
-3اسامی تهیه کنندگانی را بدهید که تمامی قطعات را تهیه می کنند.
جواب پرس و جو بدونه :FORALL
36
-4شههماره و وزن قطعهها ی را مشههخص کنیههد کههه وزن هههر قطعههه برحسههم گههرم بیشههتر 1000
باشد.
-5شهر قطعا ی را مشخص کنید که بیش از سه قطعه آبی در آن انبار باشد.
37
•
تفههاوت اصههل حسههاب میههدانی بهها حسههاب تههاپل در ایههن اسههت کههه در حسههاب میههدانی یههک
شرط اضاف به عنوان شرط عضویت وجود دارد که بصورت زیر نوشته می شود:
)R(A1:v1, A2:v2, …, An:vn
•
که در آن Rرابطه Ai ،نام صفت و viیک مقدار از میدان و یا یک لیترال مهی باشهد.
این شرط در صهور ی درسهت ارزیهابی مهی شهود کهه در Rتهاپل وجهود داشهته باشهد کهه
مقادیر داده شده براا صفات را داشته باشد.
•
مثال :نام دانشجویانی را بدهید که COM333را انتخاب نکرده اند.
)STNAM WHERE EXISTS ST(STT(STID:ST, SNAME:STNAM
))’AND NOT STCOT(STID:ST, COID:‘COM333
38
-1نما ش ساده
-2مبناي رياص ي دارد.
-3تامینكننده استقالل دادهاي است.
-4يك عنصر ساختاري اساس ي به نام رابطه دارد.
-5كامل است یعنی هر سه بخش اساس ی الزم براا مدل داده اا را دارد.
-6ساخت منطقی دستورات زران داده اا قرع در این مدل ساده و در عین حال مبتنهی بهر
ریاضیات است.
-7براي بهترسازي طراحي ،ابزار طراحي بهتر دارد.
و ...
39
-1دشواري در نما ش دادههاي پيچيده
-2دشواري در نما ش مفاهيمي مانند وراثت ،عميم و ...
-3نداشتن قواعد جامعيت ذا ي
-4عدم امكان مدلسازي رقتار اشيا
-5ققهدان اكمهال برنامهسهازي و اكمهال محاسهبا ي (نداشهتن دسهتورا ی مثهل IF, For,
…)
-6دشواري در ياقتن كليد كانديد
-7مشكالت ناش ي از وجود مفهوم هيچمقدار
و ...
40
•
كاتالوگ در مدل رابطهاي ،عدادي رابطه است :يك پايگاه رابطههاي كهه خهود سيسهتم
ايجاد ميكند .در كاتالوگ اطالعا ي در مورد اشيا ذيل نگهداري ميشود:
•
ميدان ،رابطه ،صفت ،ديد ،قواعد جامعيت
•
توابج و رويههاي عريفشده توسط كاررر
•
رهاناهاي عريفشده توسط كاررر
•
كاررر
•
شاخص و ساختارهاي مرتبط با آن مثل کالسترها
•
ضوابط ايمني
•
پرسش ها
•
برنامههاي كاررردي
41