Shahriar Pirnia pirnia

2. Databaseپايگاه داده ها قسمت سوم ShahriarPirniahttp://www.pirnia.net

3. مفهوم رابطه نرمال : رابطه اي است که در آن تمام مقادير تمام صفات خاصه اتوميک باشد . مثال : رابطه BEFOREيک رابطه از درجه 2 است . ميدان PQTYيک ميدان مرکب است که از دو ميدان P# و QTY تشکيل شده است . يکي از ميدانها ، مقاديرش رابطه است . از لحاظ رياضي اين يک Nested Relation است . گاهي به آن (Not Normal Relation) ميگويند . Codd ميگويد رابطه ها در مدل رابطه اي تعريف شده توسط ايشان بايد نرمال باشند . یک تاپل

4. AFTER اين يک رابطه نرمال (طبيعي) است . يک تاپل دليل اينکه چرا رابطه ها بايد نرمال باشند (چرا مقادير خاصه بايد اتوميک باشند . ) DATE با تاکيد گفته است که : دليل اصلي نرمال بودن رابطه ها در مدل رابطه اي ، تامين سادگي است . Simplicity 1 – سادگي ظاهري 2 – سادگي انجام عمليات در Database . 3 – ساده تر شدن احکام DSL مخصوصا DML .

5. مثال : Q1درج کن : <S4 , P1 , 60> Q2 : درج کن <S2 ,P3 , 50> انجام I1 در هر دو پايگاه BEFORE و AFTER به سادگي قابل انجام است . INSERT INTO BEFORE TUPLE<S4,P1,60> INSERT INTO AFTER TUPLE<S4,P1,60> اما اجراي درخواست I2 در AFTER به سادگي انجام پذير است . اما در BEFORE نه . INSERT INTO AFTER TUPLE<S2,P3,50> اما نمي توان نوشت INSERT INTO BEFORE TUPLE<S2,P3,50> زيرا قبلا تاپلي با S# = S2 در اين جدول وجود دارد اين Tuple اي از جدول BEFORE نيست . بايد اپراتور ديگري بجز INSERT در زبان وجود داشته باشد که مقداري را به يک مجموعه اضافه کند

6. . مثلا : ADD TO PQTYSET VALUE <P3,50> WHERE S# = ‘S2’; يعني يک اپراتوري براي INSERT هنگامي که Tuple مقدار عادي دارد لازم داریم و يک اپراتوري به نام ADDيا APPEND براي حالتي که Tuple مقدار عادي ندارد . اين براي سيستم Overhead ايجاد مي کند و سادگي زبانی را از بين مي برد. کليد در مدل رابطه اي : با اصطلاح عام کليد کانديد (Candidate Key) مطرح است . تعريف : هر زير مجموعه از مجموعه عنوان رابطه به صورت Ai Aj … Ak که داراي دوخاصيت زير باشد کليد کانديد است : 1 – يکتايي مقدار داشته باشد در طول حيات رابطه Uniqueness)(يعني در هيچ دو Tuple اي از رابطه اين ترکيب مقدار يکسان نداشته باشد . 2 – کاهش ناپذيري Irreducibilityيا Minimality (ايجاز يا کهينگي) داشته باشد . يعني اگر هر يک از اجزاي تشکيل دهنده اين ترکيب را ازش بگيريم خاصيت يکتايي مقدار آن از بين برود . نام رابطه کليد کاندید S S# P p# SP (S#,P#)

7. نکته : اگر خاصيت دوم برقرار نباشد ، يعني زير مجموعه اي از مجموعهHeading وجود داشته باشد که يکتايي مقدار داشته باشد ولي کاهش ناپذيري نداشته باشد ، به آن Supper Key مي گويند (يعني درون خود حد اقل يک کليد کانديد دارد ) اما Codd ،Supper Key را به عنوان يک مفهوم اساسي در مدل رابطه اي مطرح نمي کند . آيا وجود حد اقل يک کليد کانديد در رابطه محرز است ؟ بله . هر رابطه کليد کانديد دارد . زيرا در بدترين حالت خود مجموعه عنوان مي تواند کليد کانديد باشد ، زيرا مجموعه Body ،Tuple تکراري نمي تواند داشته باشد (S#,P#, DATE) پس اضافه کردن ستون نمي تواند همينطوري انجام شود . يک ستون اضافه گرديد و کليد کانديد کاملا تغيير کرد . SPd (S# , P# , Qty , Date) S1 p1 100 d1 S2 p1 100 d2 …… مثال : به رابطه ای که مجموعه عنوانش کلید کاندیدش باشد رابطه تمام کليد All-Key مي گويند . (سيستم مطلوبي نيست زيرا حداقل Overhead ايجاد Index زياد مي شود . ) کليد Identifier تشخيص Tuple ها است و هرچه کليد طولاني تر باشد براي يافتن Tuple بايد مسير پيچيده تري را رفت

8. Primary Key : کليد کانديدي است که طراح انتخاب مي کند بنابر ملاحظات محيطي (يعني يکي از کليد هاي کانديد ) مثلا : رابطه S داراي کليدهاي کانديد S# و SNAME است ، اما طراح ممکن است S# را به عنوان کليد اصلي در نظر بگيرد . آن کليد کانديدي که توسط آن Access بيشتري به Data انجام مي شود به عنوان کليد اصلي مطرح مي شود که بستگی به نظر طراح دارد . کليد اصلي :شناسه تاپل است در رابطه ، امکان آدرس دهي به تاپل در رابطه است در محيط انتزاعي (الزاما صرف معرفي کليد اصلي ايجاب نمي کند حتما سيستم روي کليد اصلي Index بزند بلکه بايد درخواست کنيد .)اگر Tuple Level Operation بخواهيد داشته باشيد حتما بايد کليد اصلي داشته باشيد . *کليد اصلي حتما در تعريف رابطه بايد قيد شود . يعني جزئي از شماي اصلي پايگاه است . CREATE RELATION S (S# DOMAIN SNUM . CITY DOMAIN CITY) PRIMARY KEY S#; در سيستمهاي رابطه اي فعلي : CREATES TABLE S (S# CHAR (6) . . SNAME . . . . CITY . . . ) PRIMARY KEY S# ;

9. (کليد خارجي) : تعريف :Ai در R2 کليد خارجي است اگر در رابطه اي مثلا R1 کليد اصلي باشد . مي توان گفت Ai در R2 کليد خارجي است اگر از يک ميدان اصلي مقدار بگيرد . (R1 و R2 لزوما مجزا نيستند ) يعني کليد اصلي کليد خارجي هم است . مثال : صفت خاصه S# در SP کليد خارجي است زیراS# در S کليد اصلی است. مثال : P# در SP کليد خارجي است زيرا P# در P کليد اصلي است . نکات : در تعریف کلید خارجی R1 و R2 لزوما مجزا نیستند مثال : EMP ( EMP# , EMPNAME ,…. , EMPMGR#) E1 , En1 , …… ,E13 E2 , En2 , ……,E27 E3 , En3 , ….. ,E13 کارمند کارمند مدیر است یا تحت مدیریت مدیر است

10. EMP# کليد اصلي است .#EMGR در همين رابطه کليد خارجي است . نکته : يک رابطه 1:Nيکسويه توسط کليد خارجي پياده سازي مي شود . نکته : لزومي ندارد کليد خارجي يک رابطه جزئي از کليد اصلي رابطه باشد . هرچند در SP چنين است . SP ( S# , P# , Qty) کليد اصلي در مدلينگ ارتباط1:N (يک به n) دوسويه از کليد خارجي استفاده مي شود و رابطه اي براي بيان ارتباط آن دو موجوديت طراحي مي شود که کليد اصلي اش تشکيل شده از کليدهاي اصلي دو رابطه است . کلید خارجی کلید خارجی

11. نکته : فايده کليد خارجي چيست ؟ امکاني است براي نشان دادن ارتباط بين موجوديتها . مثل اينکه در رابطه SP از طريق کليد خارجي S# و P# ارتباط دو موجوديت S و P را نشان مي دهيم . و نيز امکان ارجاع به موجوديتهاست . به عنوان مثال وجود S1 در رابطه SP ارجاعي است به نمونه موجوديت S1 در رابطه S . آيا تنها امکان نشان دادن ارتباط بين موجوديتها کليد خارجي است ؟ خير . در حالت کلي وجود هر صفت خاصه مشترک در مجموعه عنوان دو رابطه نشاندهنده نوعي ارتباط بين دو نوع موجوديتي است که آن دو رابطه نمايشگر آنها هستند . مثال : S(S#,SNAME,STATUS,CITY) P(P#,…………………..,CITY) CITY در دو رابطه نوعي ارتباط را نشان مي دهد مثلا "در يک شهر بودن" "در يک شهر نبودن" و CITY در هيچيک از دو رابطه کليد خارجي نيست . زيرا هيچ جا کليد اصلي نيست . براي نمايش ارتباط بين دو موجوديت : دو راه داريم : 1 – استفاده از مفهوم کليد خارجي 2- استفاده از يک صفت خاصه مشترک کليد خارجي بيانگر يک ارتباط محوري و اساسي مي باشد اما صفت خاصه مشترک چنين نيست . مثال : S(S#,SNAME, . . . ,CITY) P(P#,……………..,CITY) J(J#,JNAME,CITY) SPJ(S#,P#,J#, . . . ,CITY) CITY بيانگر نوعي ارتباط است ولي J# و P# و S# بيانگر يک ارتباط محوري و اساسي مي باشد .

12. قواعد جامعیت INTEGRITY RULES مجموعه قواعدی که به کمک آنها سیستم صحت و دقت داده های ذخیره شده در پایگاه و نیز ارتباطات بین انواع موجودیتها را کنترل می کند .( Tuple هم موجودیتها را نشان می دهد و هم ارتباطات بین آنها را (دید جدولی) ) قواعد جامعیت 1- کاربری (خاص) : تعریف شده توسط کاربر (منظور شخص DBA) است .کاربران خارجی باید درخواست خود را به DBA بدهند.(UDR=User Defined Rules) 2- عام (متا قاعده Meta-Rule) : مستقل از Data های خاص مطرحند ، Independent از Data های محیط خاص قواعد جامعیت کاربری (خاص)میدانی : قاعده ای است ناظر به مقادیر یک میدان ستونی : علاوه بر قواعد میدانی که بر روی مجموعه مقادیر میدان است ، قواعد جامعیت ستونی اخص از محدودیت میدانی است ، یعنی برای صفات خاصه بخصوصی در رابطه بخصوصی محدودیتی قایل بشویم

13. مثال میدانی : شماره تهیه کنندگان حتما به این صورت است : R1 = sdddd R2 = 1111<dddd<8888 R3 = City In { C1,C2,C3,C4,C5} ستونی : علاوه بر قواعد میدانی که بر روی مجموعه مقادیر میدان ناظر است ، قواعد جامعیت ستونی اخص از محدودیت میدانی است ، یعنی برای صفات خاصه بخصوصی در رابطه بخصوصی محدودیتی قایل بشویم . مثال : R4 = 0< QTY < 100 R5 = QTY برای تهیه کنندگان ساکن C1 و C2 که قطعات P1 و P2 را تهیه می کنند نمی تواند بیشتر از 50 باشد (*) یک DBMS واقعی باید امکان بدهد تا DBA انواع قواعد جامعیت را بیان و ایجاد کند و سیستم باید این قواعد را پیاده و عمل کند . (*) شمای ادراکی فقط از تعریف رابطه تشکیل نمی شود بلکه قواعد جامعیت مربوط به آن رابطه جزیی از شمای ادراکی است . (*) بخش عمده Overhead عملیات DBMS مربوط به این قواعد جامعیت است .

14. قواعد جامعیت عام C1 : قاعده جامعیت موجودیتی Entity Integrity Rule ناظر به کلید اصلی است . C2 : قاعده جامعیت ارجاعی Refrential Integrity Rule ناظر به کلید خارجی است نکته : کلید خارجی هم مثل کلید اصلی باید در تعریف رابطه آورده شود قاعده C1 : می گوید : هیچ جز تشکیل دهنده کلید اصلی نمی تواند Null باشد ( هیچ مقدار Null Value داشته باشد) Null Value : 1- مقدار ناشناخته : Unknown : مثلا STATUS تهیه کننده ای مشخص نباشد . 2- مقدار غیر قابل اعمال : Inapplicable مثلا شماره شناسنامه همسر برای شخص مجرد غیر قابل اعمال است

15. Null Value با صفر یا Blank فرق می کند زیرا آنها مقدارند . هر چند سیستم باید امکانی برای بازنمایی Null-Value داشته باشد. ( درکتابها Null-Value را با ؟ نمایش می دهند ) * باید به عنوان یک مقدار ، یک مجموعه عملیاتی مشخص ناظر بر آن وجود داشته باشد ( محاسباتی منطقی مقایسه ای ) به عنوان مثال در مقایسه دو null value جه می توان گفت ؟Null ( < = > ) Null لحظه به لحظه Database باید صحیح باشد . Null = Null < > ≤ ≥

16. منطق دو ارزشی برای عملیات منطقی Null پذیر کفایت نمی کند . Codd منطق سه ارزشی را بیان می کند نظر Date : مفهوم Null مخرب مدل رابطه است .بلکه مفهوم دیگری به نام Missing Information باید مطرح شود که ساده تر است . پس Null ، Overhead و گرفتاریها ی خود را دارد. دلیل قاعده C1 : زیرا کلید اصلی شناسه Tuple است .شناسه یک نمونه مشخص از یک نمونه موجودیت به معنای عام و متمایز از هر نمونه دیگر ، و کلید اصلی عامل تمیز یک نمونه با نمونه دیگر است و عامل تمیز خود نمی تواند ناشناخته باشد. باید در تعریف رابطه ، Null-Value پذیری یا ناپذیری صفت خاصه را بیان کرد ( DBMS باید این امکان را بدهد) Create Table s (S# char(6) , Sname char(20) NOT NULL , …. City Char (12) ) PRIMARY KEY S# ; یعنی در طول حیات این رابطه Sname نمی تواند Null باشد و Sname باید مقدار مشخصی داشته باشد.

17. قواعد جامعیت : قواعد خاص : تعریف شده توسط کاربر قواعد عام : C1 : قاعده جامعیت موجودیتی C2 : قاعده جامعیت ارجاعی : ناظر به کلید خارجی c2: اگر Ai در R2 کلید خارجی باشد (یعنی Ai در رابطه R1 کلید اصلی است ) R2.Ai : 1- می تواند null باشد اگر جزء تشکیل دهنده کلید اصلی در R2 نباشد در غیر اینصورت ( یعنی در صورتیکه null نباشد ) 2- باید حتما مقداری داشته باشد که در R1 وجود دارد. R1 S(S#............ , City) R2 Sp S# P# QTY S1 P1 S1 P1 S1 P2 S2 P1 S3 P2 S3 P1 S4 P1 مثال : S1 S2 S3 در S وجود ندارد . (مقدار Unmatchable)

18. * R2 به R1 ارجاع دارد یا رجوع می کند. R1 R2 رابطه مرجعReferenced رابطه رجوع کننده Referencing نمودار ارجاع یا گراف ارجاع (از طریق مقدار کلید خارجی) Sp P مثال : S مثال دیگر: EMP( EMP#, EMP name, ….., EMP MGR#.....) کلید خارجی کلید اصلی این رابطه بر طبق بحث قبلی چونR1! و R2 برابرند. بنابراین آنرا Self Referencing گوییم.

19. EMP شکل کلی یک مسیر ارجاع : R1 R2 ....... Rn-1 Rn Rn Rn-1Ri ….. R1 حلقه ارجاع با n رابطه مثال دیگر: EMP( E#,Ename, …….. , DEPT# ……….) کلید خارجی DEPT( DEPT#, DEPT PHONE,……. , DEPTMGR# , ……. ) شماره کارمند کلید خارجی

20. DEPTMGR# EMP DEPT Dept# تمرین : دو مثال قید کنید که در آن حلقه ارجاع با n=2 و دو مثال دیگر n=3 وجود داشته باشد. سئوال: دو قاعده c1 و c2 چگونه باید به DBMS داده شود و چگونه رعایت شوند؟ نکته : قواعد C1 و C2 باید به نحوی به DBMS داده شوند. نکته : باید توسط DBMS رعایت شوند. قاعده C1 از طریق معرفی کلید اصلی در تعریف رابطه و نیز استفاده از گزینه NOT Null

21. CREATE RELATION S ( S# ……………… NOT NULL, … ….. ……. City CHAR (12) ) PRIMARY KEY S# ; بدین طریق C1 تعریف و رعایت می گردد برای معرفی قاعده c2: در تعریف رابطه کلید خارجی( در صورت وجود ) باید مشخص شود.

22. CREATE TABLE SP ( S# …………. NOT NULL , P# …………. NOT NULL, QTY …………. Integer ) PRIMARY KEY (S# , p#) FOREIGEN KEY S# reference S ( referenced Table : S )(s#) DELETE CASCADE FOREIGN KEY P# REFERENCE P(p#) DELETE CASCADE;

23. ايجاد جدول sp در : Oracle • CREATE TABLE SP • (snum character(3) , • pnum character(3) , • qty integer , • CONSTRAINT pk_snumpnum PRIMARY KEY (snum,pnum), • CONSTRAINT fk_snum • FOREIGN KEY (snum) • REFERENCES s(snum) • ON DELETE CASCADE , • CONSTRAINT fk_pnum • FOREIGN KEY (pnum) • REFERENCES p(pnum) • ON DELETE CASCADE)

24. چگونه قاعده C2 را رعایت کنیم : سه مکانیسم برای رعایت C2 متصور است : نکته : قاعده C2 باید در جمیع عملیات روی پایگاه رعایت شود . مثال : فرض می کنیم در رابطه S تهیه کننده ‘S7’ وجود ندارد . INSERT INTO SP VALUES (S7,P1,60) ; قاعده C2 می گوید این عمل باید Reject شود درج یک تاپل مثال : فرض می کنیم در رابطه S موجودیت S3 را داریم : DELETE FROM S WHERE S#=‘S3’ ; آیا باید اجرا شود ؟ اگر بله با تاپلهای Referencing چه باید کرد ؟؟ و اگر نه ...

25. سه مکانیسم برای رعایت قاعده C2 : در عمل Delete ( به عنوان مثال) : 1- روش Cascade : انتشار عملیات به رابطه Referencing وقتی که عمل در رابطه Referenced انجام شود .مثال : با حذف تاپلهای S3 از S ، تمام تاپلهای رجوع کننده به S3در رابطه های دیگر نیز باید حذف گردد. 1- DELETE FROM S WHERE S#=‘S3’ ; 2- DELETE FROM SP WHERE S#=‘S3’; این روش نوعی Propagating Update است . این روش در بعضی از سیستمهای موجود وجود دارد. 2- روش Restricted : انجام عمل بطور مشروط در این روش تاپل مرجع (Referenced) حذف نمی شود و بعبارتی دیگر تا زمانیکه تاپل مرجع تاپل رجوع کننده ای دارد ، عمل حذف انجام نمی شود . 3- روش هیچمقدارگذاری : Nullifying)( در این روش با حذف تاپل مرجع مقدار کلید خارجی در تاپل رجوع کننده Null می شود و زمانی امکانپذیر است که کلید خارجی جزء کلید اصلی نباشد .

26. متداولترین روش : روش اول است . نکته : در سیستمهای پیش رابطه ای قواعد جایمعیت نظیر C1 و C2 وجود دارد و بویژه قاعده C2 در DS ذاتی این سیستها وجود دارد . یعنی ایده روش Cascade در سیستمهای پیش رابطه ای (مثلا در سلسله مراتبی با حذف نمونه پدر ، نمونه فرزند نیز حذف می شود یا مثلا شبکه ای با حذف نمونه مالک و... ) چیز جدیدی نیست و نیاز به تصریح ندارد ولی در سیستمهای رابطه ای باید تصریح شود زیرا رابطه ها حسب ظاهر منفک از یکدیگرند و ارتباط بین رابطه ها علیرغم مجزا بودن دو رابطه به یکی از روشهای زیر انجام می گیرد. از طریق مکانیسم ارجاع یا از طریق صفت خاصه مشترک که در Heading دو رابطه در سیستم رابطه ای باید تصریح شود وعمل Cascade در سیستم رابطه ای باید تصریح شود. نظر Date : بهتر است DBMS قبل از انجام عمل Warning دهد و بعد از کاربر سوال کند

27. تمرین : امکانات DDL را در SQL92 مطالعه کنید ( مخصوصا روی احکام CREATE DOMIAN , DROP DOMAIN , ALTER DOMAIN ) سطح ادراکی DB2/:SQL:DML چهار تا حکم دارد . احکام بازیابی : حکم واحد SELECT ذخیره سازی : احکام با Syntax(INSERT ,DELETE , UPDATE ) امکانات جبر رابطه ای و محاسبات رابطه ای تقریبا بطور کامل در حکم SELECT استفاده می شود SELECT [DISTINCT] items FROM table(s) [WHERE condition(s)] [ORDER BY] [GROUP BY] [HAVING ] شکل کلی SELECT برای خروجیهای Sorted است. Option ایجاد نظم در خروجیها می باشد

28. مثال : مشخصات تهیه کنندگان ساکن شهر C2 را بدهید ؟ SELECT S# , Sname , Status , City FROM S WHERE City =‘c2’ ; S# SNAME STATUS CITY S1 Sn1 20 C2 S4 Sn4 20 C2 وقتی تمام ستونهای جدول را بخواهید نیازی به ذکر نام صفات خاصه نیست ، بلکه یک * بجای آنها کافی است . SELECT * تمامی مشخصات را می آورد و مخصوصا در مواردی که تعداد صفات خاصه زیاد است مفید است . SELECT SQL وقتی سطر کامل استخراج می کنیم شبیه سازی SELECT جبری است که تاپلهایی از جدولها به ما می دهد. SELECT * را اگر شرط بدهیم و یا تدهیم بازهمSELECT جبری خواهد بود.

29. SELECT S#, City FROM S ; مثال : دو ستون از جدول را می دهد . S# CITY S1 C2 S2 C3 S3 C3 S4 C2 S5 C1

30. ادامه SELECTٍ • بازیابیبا جدول جواب منظم • Q : شماره تهیه کنندگان و وضعیت آنها را بدهید. جدول جواب به نظم صعودی مقادیرstatus مرتب شده باشد. SELECT S# ,Status FROM S ORDER BY Status ORDER BY Status DESC ORDER BY2شماره ستون درجدول جواب ORDER BY 2 DESC ASCENDING صعودی default است و احتیاج به ذکر نیست. ASCENDING نیاز به تصریح ندارد.

31. بازیابی مقدار محاسبه شده * • ًQ: شماره هر قطعه و وزن آنرا به گرم بدهید. فرض کنید DBA وزن را به واحد کیلو گرم ذخیره نموده . SELECT P# , WEIGHT*1000AS ‘WEIGHT IN GRAMS’ FROM P عبارت محاسبه شدنی P# WEIGHT IN GRAMS P1 12000 P2 17000 P3 17000 P4 14000 p5 12000 p6 19000 جدول جواب صورتی چنین دارد: این دو ستون بی نامند. برای اینکه جدولی با ستونهای نامدار داشت این جدول جواب را می توان با حکم INSERT در جدولی مناسب با ستونهای نامدار وارد کرد.

32. روش دوم : SELECT P# , ‘WEIGHT IN GRAMS’ , WEIGHT*1000 FROM P عبارت محاسبه شدنی P# P1 WEIGHT IN GRAMS 12000 P2 WEIGHT IN GRAMS 17000 P3 WEIGHT IN GRAMS 17000 P4 WEIGHT IN GRAMS 14000 p5 WEIGHT IN GRAMS 12000 p6 WEIGHT IN GRAMS 19000 جدول جواب صورتی چنین دارد: این دو ستون بی نامند. برای اینکه جدولی با ستونهای نامدار داشت این جدول جواب را می توان با حکم INSERT در جدولی مناسب با ستونهای نامدار وارد کرد.

33. پیاده سازی اپراتور join: بازیابی از چند جدول Join Syntax صراحتا در DB2/SQL وجود ندارد. اما در SQL92 پیش بینی شده است. Query: مشخصات قطعات و تهیه کنندگان از یک شهررا بدهید. ( بحث کلید خارجی بحثی داشتیم که آیا ارتباط تنها توسط کلید خارجی است؟ گفتیم که نه هر عضو مشترک می تواند بیانگر ارتباط باشد. ( مثال : CITY در (P , S SELECT S.* , P.* FROM S,P WHERE S.CITY = P.CITY سیستم ابتدا ضرب کارترین دو رابطه را ایجاد می کند. سپس tuple های حائز شرایط را استخراج می کند. می توان در عمل join شرایطی را نیز مطرح کرد عمل join (Natural Join) Equi Join) )

36. Q: مشخصات قطعات و تهیه کنندگان از یک شهر که قطعه آبی باشد. AND COLOR = ‘ BLUE’ • لازم نیست حتما تمام مشخصات را بخواهید می توانید فقط برخی صفات خاصه را بخواهید. مثال: • Q: شماره قطعات و شماره تهیه کنندگان همشهر را بدهید. SELECT S.S# , P.P# FROM S,P WHERE S.CITY = P.CITY AND COLOR = ‘ BLUE ‘ ;

37. نکته : چندین جدول را می توان Join کرد ولی توجه داشته باشید که عمل Join زمان گیر است . نکته : SQL ، ناروشمند است (Nonprocedural) . شما فقط می گویید چه می خواهید از چه جدولی اینکه به چه ترتیبی انجام شود را نمی گویید. اما بطور ضمنی روشمند است . Q : اسم جفت شهرهایی را بدهید که تهیه کننده شهر اول قطعه ای انبار شده در شهر دوم را تولید کند. ( الزام به استفاده از Qualifier داریم ) SELECT DISTINCT S.CITY , P.CITY FROM S , SP , P WHERE S.S#=SP.S# AND P.P#=SP.P#

38. ظاهرا ترتیب نوشتن جدولها در شرط FROM مهم نیست. اما در برخی سیستمها از جمله توصیه هایی برای نوشتن ترتیب جدولها آورده شده است تا Optimizer نهایتا بهتر عمل کند. تمرین: با dataی SP, P , S مراحل اجرایی این Query نشان داده شود. ( join سپس حاصل را دوباره join سپس این دو ستون را باید استخراج کنید. ) توجه : DISTINCT OPTION , SQL به شما داده یعنی من به تو جواب Query را می دهم اگر تو می خواهی تاپلها یکتایی داشته باشد از کلمه DISTINCT استفاده کن

39. نکته : حاصل اجرای جبر رابطه ای و یا محاسبات رابطه ای همیشه رابطند . اما حاصل Select SQL لزوما همیشه یک جدول نیست . • می توان یک جدول را با خودش Joinکرد • مثال : Query : شماره جفت تهیه کنندگان همشهر را بدهید S S# Sname Status City S# S# S1 . . C2 S2 . . C3 S3 . . C3 S4 . . C2 S5 . . C1 S1 S4 S2 S3 جدول جواب برای پاسخگویی به این قبیل Query ها باید S را با خود در SQL ، Join کرد SELECT FIRST.S# , SECOND.S# FROM S FIRST , S SECOND WHERE FIRST.CITY = SECOND.CITY

40. از یک تکنیک دگر نامی استفاده شده است S را به نام First و به نام Second نامیده ایم . بعد از FROM هرگاه نام یک رابطه بیابید و بعد از حداقل یک Blank ، یک نام بیاید ، سیستم آن نام را ، نام دیگر برای آن رابطه در نظر می گیرد. حاصل SELECT : S# S# S1 S1 S1 S4 S2 S2 S2 S3 S3 S2 S3 S3 S4 S1 S4 S4 سیستم چه می کند ؟ 2 تا CURSOR می گیرد و دومی را MOVE می دهد برای حذف بدیهات و تکراریها شرط زیر را اضافه کنید : AND FIRST.S# < SECOND.S# بدیهات تکراری

41. در نتیجه خواهیم داشت SELECT FIRST.S# , SECOND.S# FROM S FIRST , S SECOND WHERE FIRST.CITY = SECOND.CITY AND FIRST.S# < SECOND.S#

42. تمرین : شماره جفت تهیه کنندگانی را بدهید که از یک شهر نباشد SELECT FIRST.S# , SECOND.S# FROM S FIRST , S SECOND WHERE FIRST.CITY != SECOND.CITY AND FIRST.S# < SECOND.S#

43. توابع جمعی ( گروهی) Aggregate Functions در حاشیه : قدرت Select :اپراتورهای Project و Join و Select جبر رابطه ای را تواما ارایه می دهد. برای پاسخگویی به سوالاتی از قبیل چه تعداد قطعه داریم ؟ ماکزیمم Quantity چقدر است ؟ خود Select قادر به پلسخگویی نیست. توابعی پیش بینی شده است که در متن Select بکار می روند .(مستقلا نمی توانید آنها را به کار ببرید) این توابع عبارتند از : COUNT (DISTINCT ( ... COUNT(*) MAX MIN SUM AVG حالتی از COUNT موسوم به COUNT(*)تمام سطرها را – حتی تکراری - می شمارد.

44. مثال : تعداد محمولات را بدهید .( هر سطر SP یک محموله است) (SHIPMENT) SELECT COUNT(*) FROM SP ; مثال : چند نوع قطعه تهیه شده است . SELECT COUNT (DISTINCT P#) FROM SP ; مثال : کلا چند نوع قطعه وجود دارد SELECT COUNT(*) FROM P ; مثال : SELECT MAX(STATUS) FROM S ;

45. استفاده از GROUP BY : Query : شماره هر قطعه تهیه شده و کل تعداد تهیه شده از هر قطعه را بدهید . GROUP BY CLAUSE جدول داده شده بعد از FROM را منطقا گروه بندی می کند به نحوی که در هر گروه مقدار ستون یا ستونهای داده شده پس از GROUP BY یکسان است .(گروه بندی بر اساس آنچه بعد از GROUP BY داده می شود ) آنگاه تابع SUM عمل می شود. )ستونهایی که حاصل اعمال توابع هستند بی نام می باشند( SELECT P# ,SUM(QTY) FROM SP GROUP BY P# ; S# P# QTY S1 P1 300 S2 P1 300 S1 P2 200 S2 P2 400 S3 P2 200 S4 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 P# P1 600 P2 1000 P3 400 P4 200 P5 100

46. مثالی از به عینیت درآوردن فیلد مجازی حاصل از پردازش می باشد. (Indirect Materialization ) مثالی از کاربرد HAVING: ( هدف این است که مفاهیم برای شما نا آشنا نباشد( Query: شماره قطعاتی را بدهید که توسط بیش از یک تهیه کنندهتهیه شده باشد. نکته 1 : HAVING همیشه با GROUP BY می آید و مستقل معنی ندارد. نکته 2: استفاده از GROUP BY ناروشمندی SQL را کمی تضعیف می کند. ( زیرا می گویید گروهبندی کن )و آنرا تا حدی روشمند میکند. از نظر کوتاهی بسیار جالب است هر چند روشمند است. SELECT P# FROM SP GROUP BY P# HAVING COUNT ( * ) > 1 ; نقش having : نقش having در گروه همان است که نقش where در tuple. Where برای بیان شرط یا شرایط ناظر به سطر است. having برای بیان شرط یا شرایط ناظر به گروه .

47. شماره هر تهيه کننده و تعداد کل قطعاتي را که تهيه کرده بدهيدمثال : select s#, sum(QTY) from sp group by s# مثال :شماره تهيه کنندگاني را بدهيد که دو نوع قطعه تهيه کرده اند select s# from sp group by s# having count(*)=2

48. شماره هر تهيه کننده و تعداد انواع قطعاتي که تهيه کرده را در مورد تهيه کنندگاني که بيش از يک قطعه تهيه کرده اند ،بدهيد select s#, count(*) from sp group by s# having count(*)>1 در مورد قطعاتي که توسط سه تهيه کننده تهيه شده اند شماره قطعه و تعداد کل تهیه شده از قطعه را بدهید select p#, sum(qty) from sp group by p# having count(*)=3

49. با مفروض بودن جدولSPd شماره هر تهيه کننده ، شماره قطعه تهيه شده و تعداد کل تهيه شده از آن قطعه را بدهيد select s#, p#, sum(qty) from spd group by s#, p# با فرض داشتن جدول COMPUTERS که ستونهای آن عبارتند از نام دفتر ، شماره اتاق ، شماره کامپیوتر ، نوع CPU ، مقدار RAM ، حجم HARD DISK و ... ، گزارشی تهیه کنید که ستونهای آن نام دفتر ، شماره اتاق و تعداد کامپیوترهای موجود در اتاق باشد . select officename, roomno, count(*) from computers group by officename, roomno order by officename, roomno

50. پرس و جو های فرعی SUBQUERY : مثال : Q : اسامی تهیه کنندگاه قطعه P2 را بدهید . روش اول : با استفاده از مکانیزم Join : SELECT Sname FROM S , SP WHERE SP.S# = S.S# AND SP.P# = ‘P2’ ; بویژه در SQL 92 اپراتور Join صراحتا تامین شده است . نکته ای که به این راه حل وارد است این است که فرمالیسم طبیعی در نوشتن … Sname FROM SP از بین رفته است . چون Sname در SP نیست . یعنی طبیعی این است که بنویسیم : SELECT Sname FROM S شرایط WHERE