اصول طراحي پايگاه داده‌ها

1. اصول طراحي پايگاه داده‌ها • Structured Query Language (SQL) مهدی ایل بیگی دانشگاه پیام نور دماوند

2. امكانات مهم زبان SQL • دستورات تعريف داده‌ها • دستورات مجازشماري • دستورات پردازش داده‌ها • دستورات پردازش داده‌ها به طور ادغام‌شدني • دستورات نوشتن ماژول و رويه • دستورات كنترل جامعيت • دستورات كنترل تراكنش‌ها

3. دستورات تعريف داده‌ها • تعريف شما: برای تعریف شمای ادراکی در SQL از دستورات زیر استفاده می نماییم. CREATE SCHEMA Schema-Name AUTHORIZATION USER {base–table definition , view definition , grant-operation } • base–table جدول های اصلی در شمای مورد نظر هستند و view دیدهای مورد نیاز در شمای مورد نظر می باشد. • Grant-Operation برای تعریف سطح دسترسی کاربران به جداول می باشد.

4. دستورات تعريف داده‌ها • انواع داده ها در SQL1: • CHARACTER[(length)] • INTEGER • DECIMAL [(precision[,scale])] • SMALLINT • DOUBLE PRECISION • REAL • FLOAT [(precision)] • NUMERIC [(precision[,scale])] • مثال: Character[10] = یک رشته از کاراکترها به طول 10 • مثال: Decimal (5,2) = یک عدد پنج رقمی با دقت دقیقا دو رقم اعشار • مثال: Float (5) = یک عدد اعشاری با دقت حداقل پنج رقم اعشار

5. دستورات تعريف داده‌ها • ایجاد یک جدول: CREATE TABLE table-name (column-definition[,column-definition]… [,primary-key- definition] [,foreign-key- definition[,foreign-key- definition]…]); مثال: در زیر دستورات ایجاد جدول شعبه بانک با سه ستون آورده شده: create table branch (branch_name char(15),branch_city char(30), assets integer, primary key (branch_name))

6. دستورات تعريف داده‌ها • دستور حذف یک جدول: DROP TABLE table-name • دستور ایجاد یک دید: CREATE VIEW Viewname [(Column-name(s))] AS Subquery ... [WITH [CASCADE|LOCAL] CHECK OPTION]; • مثال: CREATE VIEW NH_CUST AS SELECT FirstName, LastName, Phone FROM CUSTOMER WHERE STATE = ‘NH’;

7. دستورات تعريف داده‌ها • دستور مجاز شماری: با اين دستور حق انجام يك يا بيش از يك عمل به كاربر (كاربراني) داده مي‌شود یا از آن ها این حق پس گرفته می شود: GRANT , REVOKE • مثال: واگذاری حق درج، حذف و بهنگام سازی بر روی جدول مشتری ها: GRANT INSERT, DELETE, UPDATE ON CUSTOMER TO Tyson, Keith, David ; مثال: لغو کردن حق بهنگام سازی ستون شناسه مشتری در جدول مشتری ها: REVOKE UPDATE ON CUSTOMER (CustID) FROM Tyson, Keith, David ;

8. دستورات پردازش داده ها • دستور بازیابی: SELECT [ALL | DISTINCT] item(s)-list FROM table(s)-name [WHERE condition(s)] [GROUP BY column(s)] [HAVING conditions(s)] • مثال1: نام تمامی شعب بانکی را که وام می دهند بدست آورید. • select distinctbranch_namefrom loan • select ALLbranch_namefrom loan • با استفاده از کلمه distinct در جدول اسامی نتیجه، نام تکراری ظاهر نمی شود. • با استفاده از کلمه all در جدول اسامی نتیجه، نام های تکراری نیز می توانند بیایند.

9. مثال هایی برای دستور بازیابی • مثال1: همه سطرها و ستون های جدول وام را بازیابی کنید. select *from loan • نکته: کرکتر * وقتی در مقابل Select بیاید بمعنی تمامی ستون ها می باشد. • مثال2: شماره وام، نام شعبه وام دهند و 100 برابر مقدار وام را برای تمامی وام های داده شده بدست آورید. selectloan_number, branch_name, amount  100from loan • نکته: در عبارت Select می توان از عبارت های حسابی *, /, + و – استفاده نمود.

10. Aggregation Functions • توابع جمعي (گروهي): • COUNT: تعداد مقادير اسكالر را به دست مي‌دهد. • SUM: حاصل جمع مقادير اسكالر در يك ستون را به دست مي‌دهد. • AVG: ميانگين مقادير اسكالر در يك ستون را به دست مي‌دهد. • MAX: بيشترين مقدار اسكالر در يك ستون را به دست مي‌دهد. • MIN: كمترين مقدار اسكالر در يك ستون را به دست مي‌دهد.

11. مثال هایی برای دستور بازیابی • مثال1: میانگین موجودی افراد در شعبه ‘Perryridge’را بدست آورید. select avg (balance) from account where branch_name = ‘Perryridge’ • مثال2: تعداد کل مشتری ها را در جدول مشتری بدست آورید. select count(*)from customer • مثال3: تعداد تمامی سپرده گذاران را محاسبه نمایید. select count (distinctcustomer_name)from depositor • نکته: بدلیل اینکه یک فرد می تواند در چند بانک سپرده داشته باشد و باید فقط یک بار در شمارش افراد حساب شود از کلمه Distinct که باعث می شود تمامی سطرهای هم مقدار یک بار در جدول خروجی ظاهر شوند، استفاده کرده ایم.

12. مثال هایی برای دستور بازیابی • مثال4: بالاترين و پايين ترين نمره در درس COM222 در ترم دوم 80-79 را بازيابي مي‌كند. SELECT MIN(GRADE), MAX(GRADE) FROM STCOT WHERE TR=‘2’ AND YRYR=’78-79’ AND COID=‘COM222’;

13. مثال هایی برای دستور بازیابی • مثال برای استفاده از Order By: نام تمامی افرادی را که از شعبه ‘Perryridge’وام گرفته اند را بدست آورید و آن ها را بر اساس ترتیب حروف الفبا بصورت نزولی مرتب نمایید. select distinct customer_namefrom borrower, loanwhere borrower loan_number = loan.loan_numberandbranch_name = ‘Perryridge’order bycustomer_namedesc • نکته: اگر بخواهیم محتویات یک ستون را بترتیب نزولی مرتب نماییم از دستور Order By به انضمام کلمه desc که مخفف (Descend) است استفاده می نماییم. اگر بخواهیم محتویات یک ستون را بترتیب سعودی مرتب نماییم از کلمه asc که مخفف (Ascend) است استفاده می نماییم.

14. مثال هایی برای دستور بازیابی • امکان Like و Unlike: با اين دو امكان مي‌توان داده‌هاي مورد نظر را با دادن يك رشته كاراكتري به عنوان نشانوند جستجو و بيان شرايط مورد نظر، بازيابي كرد. • مثال1: مشخصات استاداني را بدهيد كه نام آن ها با AR شروع شده باشد. SELECT * FROM PROF WHERE PRNAME LIKE ‘AR%’; • نکته: کرکتر ‘%’ هر رشته ای را match می کند و کرکتر ‘-’ هر کرکتر را match می کند. • مثال2: نام تمامی مشتریانی که آدرس خیابان آن ها با یک کرکتر اختیاری آغازشده باشد و بعد از این کرکتر، کلمه Main بیاید و ادامه آدرس اختیاری باشد را بدست آورید: Selectcustomer_namefrom customerwherecustomer_streetlike ‘-Main%’

15. آزمون تست وجود هيچمقدار در يك ستون • مي‌توان با امكان IS NULL وجود هيچ مقدار در يك ستون را تست كرد. • مثال: شماره دانشجوياني را بدهيد كه نمره آن ها در درس SOC333 در ترم دوم 79-78 هنوز اعلام نشده است؟ SELECT STID FROM STCOT WHERE COID=‘SOC333’ AND TR=‘2’ AND YRYR=’78-79’ AND GRADE IS NULL

16. امكان UNION و INTERSECT • با امكان UNION، مي‌توان عملكرد عملگر UNION جبري را برنامه‌سازي كرد. حاصل اين عملكرد، جدولي است كه سطرهاي تكراري ندارد. اگر از گزيدار ALL استفاده شود، سيستم ديگر سطرهاي تكراري را در صورت وجود، حذف نمي‌كند. • مثال: نام تمامی مشتری هایی را که سپرده گذاری کرده اند یا وام گرفته اند و یا جزء هر دو دسته هستند بازیابی نمایید. (selectcustomer_namefromdepositor) union[ALL] (selectcustomer_namefrom borrower) • با امكان Intersect، مي‌توان عملكرد عملگر Intersect جبري را برنامه‌سازي كرد. • مثال: نام تمامی مشتری هایی را که هم سپرده گذاری کرده اند و هم وام گرفته اند مشخص نمایید. (select customer_name from depositor) Intersect (select customer_name from borrower)

17. امكان Group By • با اين امكان مي‌توان سطرهاي جدول را حسب مقادير يك ستون ساده (صفت ساده) گروه‌بندي كرد به نحوي كه در هر گروه، مقدار آن ستون يكسان باشد. • مثال: میانگین نمرات برای هر درس را محاسبه نمایید. SELECT COID, AVG(GRADE) AS AVGGR FROM STCOT GROUP BY COID; • نکته: با استفاده از امکان AS عملکرد عملگر Rename را می توان پیاده سازی نمود. در مثال فوق نام ستون میانگین نمرات با استفاده از امکان AS، به AVGGR تغییر داده شده است.

18. امكان Group By • مثال 2: تعداد کل سپرده گذاران را در هر شعبه مشخص نمایید. select branch_name, count (distinctcustomer_name)from depositor,accoun where depositor.account_number = account.account_numbergroup bybranch_name • نکته1: ستون (ویژگی) آورده شده در دستور select (خارج از توابع جمعی) حتما باید در لیست ویژگی های مقابل Group By آورده شود. • نکته2: در SQL1، امکان Group By را نمی توان در پرسش فرعی (Subquery) که در واقع queryهای تودرتو هستند بکار برد. • نکته3: وقتی نام چند جدول در مقابل دستور From بیاید در واقع جستجو در جدولی که برابر با حاصلضرب کارتزین تمامی این جداول ها است، صورت می گیرد. با این امکان می توان عملکرد عملگر Join را پیاده سازی نمود زیرا ابتدا حاصلضرب دو جدول را بدست می آوریم و سپس با شرط تساوی بین مقادیر ستون های هم نام در دو جدول درواقع دو جدول را به هم پیوند می دهیم.

19. امكان HAVING • با اين امكان مي‌توان شرط (شرايطي) ناظر به گروهی از سطرها را اعلان كرد. نقش اين امكان در واقع همان نقش WHERE در سطر است ولی با این امکان در واقع برای گروهی از سطرها شرط گذاشته می شود. توجه داشته باشيد كه HAVING هميشه با GROUP BY مي‌آيد. • مثال1: شماره درس هايي را بدهيد كه در ترم دوم 79-78 كمتر از 10 دانشجو در آن ها ثبت نام كرده باشند. SELECT STCOT.COID FROM STCOT WHERE TR=‘2’ANDYRYR=’78-79’ GROUP BY COID HAVING COUNT(*)<10 • مثال2: نام تمامی شعبی را که میانگین موجودی حساب های آن ها بیشتر از 1200 است بدست آورید. selectbranch_name, avg(balance)from accountgroup bybranch_namehaving avg(balance) > 1200

20. امكان BETWEEN • شكل كلي اين امكان چنين است: Scalar-expression [NOT] BETWEEN Scalar-expressionANDScalar-expression • مثال1: شماره دانشجوياني را بدهيد كه نمره آن ها در درس HIS444 در ترم اول 89-90 بين 15 و 19 باشد. SELECT STCOT.STID FROM STCOT WHERE TR=‘1’ANDYRYR=’89-90’AND GRADE BETWEEN ’15’ AND ’19’ • مثال2: شماره وام هایی را بدهید که مبلغ آن ها بین 9000 تا 10000 می باشند. selectloan_numberfrom loanwhere amountbetween90000 and100000

21. پرسش فرعی (Subquery) • پرسش فرعی پرسشی است که درون پرسش دیگر (پرسش بیرونی) نوشته شود. • مثال1: نام مشتریانی را بدهید که هم وام گرفته اند و هم سپرده گذاری کرده اند. select distinct customer_name from borrower where customer_namein (select customer_namefrom depositor) • مثال2: نام مشتریانی را بدهید که وام گرفته اند ولی سپرده گذاری نکرده اند. select distinct customer_namefrom borrowerwhere customer_namenot in (select customer_namefrom depositor )

22. پرسش فرعی (Subquery) • مثال3: نام مشتریانی را بدهید که از شعبه ‘Perryridge‘وام گرفته اند و در این شعبه سپرده گذاری نیز کرده اند. select distinctcustomer_namefrom borrower, loanwhere borrower.loan_number = loan.loan_numberandbranch_name = ‘Perryridge’ and(branch_name, customer_name)in (select branch_name, customer_namefrom depositor, accountwhere depositor.account_number = account.account_number) • نکته: اگر مجموعه جواب پرسش درونی، تک عنصری باشد، می توان قبل از Select درونی مستقیما از عملگرهای مقایسه ای (>,<, =,…) استفاده نمود.

23. پرسش فرعی (Subquery) • اگر مجموعه جواب پرسش درونی، تک عنصری نباشد، می توان قبل از Select درونی مستقیما از عملگرهای مقایسه ای (>,<, =,…) استفاده نمود بشرطی که بعد از آن ها کلمات some یا all آورده شود. البته از some و all می توان در پرسش های تودرتوی دیگر نیز استفاده نمود. • مثال: نام شعبی را بدهید که دارایی آن ها از برخی شعب بانکی که در شهر Brooklyn هستند بیشتر است. selectbranch_namefrom branchwhere assets >some (select assetsfrom branchwherebranch_city = ‘Brooklyn’) • نکته: در پرس وجوی فوق اگر بجای some از all استفاده شود نام شعبی بازگردانده می شود که دارایی آن ها از تمامی شعب موجود در شهر Brooklyn هستند، بیشتر است.

24. سور وجودی Exists و Not Exists • امکان Exists معادل با سور وجودی در جبر رابطه ای می باشد. • مثال: نام مشتریانی را بدهید که هم وام گرفته اند و هم سپرده گذاری کرده اند. select distinct customer_name from borrower where exists (select customer_name from depositor where borrower.customer_name= depositor.customer_name) • نکته1: در پرس وجوی فوق اگر بجای exists از not exists استفاده شود نام مشتریانی بازگردانده می شود که وام گرفته اند ولی در هیچ بانکی سپرده گذاری نکرده اند. • نکته2: باید توجه شود که در SQL یک پرس و جو را به صورت های مختلفی می توان نوشت. (مانند مثال فوق)

25. دستورات پردازش داده ها • دستورات عمليات ذخيره‌سازي: • براي انجام عمليات سه‌گانه ذخيره‌سازي سه دستور DELETE، INSERT و UPDATE وجود دارد. • دستور Update: شکل کلی این دستور: UPDATE table-name SET assignment-commalist [WHERE Condition(s)] • دستور Delete: شکل کلی این دستور: DELETE FROM table-name [WHERE Cond(s)]

26. دستورات پردازش داده ها • مثال: تعداد واحد درس هاي عملي را يك واحد كاهش دهيد. UPDATE COT SET CREDIT=CREDIT-1 WHERE COTYPE = ‘p’; • مثال: درس هاي دانشجوي با شماره 78110555 را در ترم دوم سال 79-78 حذف كنيد. DELETE FROM STCOT WHERE STID=‘78110555’ AND TR=‘2’ AND YRYR=’78-79’;

27. دستورات پردازش داده ها • دستور Insert: این دستور دو شکل کلی دارد: • INSERT INTO table-name Values(one row); • INSERT INTO table-name Subquery; • مثال1: اطلاعات درسي <78110888,COM888,2,78-79,12> را در جدول درج نمائيد. INSERT INTO STCOT VALUES <‘78110888’, ‘COM888’, 2, 78-79, 12>; • مثال2: برای تمامی کسانی که از شعبه Perryridge وام گرفته اند حسابی با موجودی 200 ایجاد نمایید. insert into accountselect loan_number, branch_name, 200from loanwhere branch_name = ‘Perryridge’ در این فرمت، تعدادي سطر در جدول درج مي‌شوند.

28. جمع‌بندي در مورد امكانات نسخه SQL1 1- نارويه‌اي است. 2- داراي كمال ساختاري است. (یعنی تمامی اعمال جبر رابطه ای را می‌تواند پیاده سازی نماید) 3- زبان استانده سيستم‌هاي رابطه‌اي موجود است. 4- تمام انواع داده‌اي ساده را دارد. 5- عملگرهاي بسيار قوي دارد. 6- يادگيري آن ساده است. 7- استقلال داده‌اي را تامين مي‌كند. 8- هم به صورت مستقل و هم به صورت ادغام‌شده قابل استفاده است.

29. دستوراتي كه در نسخه SQL2 تغيير كردند يا به آن اضافه شدند: 8. دستور حذف جدول 9. تعريف جدول موقت 10. دستور بازيابي 11. امكانات جامعيتي 12. امكانات ايمني 13. SQL پويا 1. دستور تعريف داده‌ها 2. انواع داده‌اي جدید 3. دستور تعريف ميدان 4. دستور تغيير ميدان 5. دستور حذف ميدان 6. دستور ايجاد جدول 7. دستور تغيير جدول

30. عملگر Except • دستور Except معادل با عملگر Minus در جبر رابطه ای است. • مثال: شماره دانشجویانی را بدهید که در ترم اول 89-90 انتخاب واحد نکرده اند. SELECT STT.STID FROM STT EXCEPT (SELECT STCOT.STID FROM STCOT WHERE TR = ‘1’ AND YRYR = ’89-90’)

31. عملگر theta ALL, theta ANY • در این دو عملگر theta می تواند هریک از عملگرهای مقایسه ای (> و < و = و ...) باشد. • مثال: نام شعبی را بدهید که دارایی آن ها از تمامی شعبی که در شهر Brooklyn هستند، بیشتر باشد. select branch_namefrom branchwhere assets >all (select assetsfrom branchwhere branch_city = ‘Brooklyn’)

32. عملگر JOIN • فرمت کلی دستور پیوند در SQL2 بصورت زیر است: Table-name [NATRUAL][join-type] JOINtable-name [ONconditional-expression | USING(column-list)] • گونه های اصلی که در قسمت join-type می توان نوشت: INNER, lEFT[OUTER], RIGHT[OUTER], FULL[OUTER], UNION • مثال: loan inner join borrower onloan.loan_number = borrower.loan_number • اگر گونه پیوند تصریح نشود، پیش فرض همان INNER است. • نکته: گونه ای دیگر از پیوند وجود دارد که بنام پیوند ضرب می باشد که معادل با عملگر ضرب کارتزین گسترش یافته است. فرم کلی این دستور به شکل زیر است: R1CROSS JOIN R2 • دستور فوق معادل است با دستورات زیر: SELECT R1.*, R2.* FROM R1, R2;

33. عملگر تقسیم • عملگر تقسیم در SQL وجود ندارد ولی می توان آن را شبیه سازی کرد. • مثال: نام تهیه کنندگانی را بدهید که تمامی قطعات را تولید می کنند. SELECTS.Sname FROM S WHERE NOT EXISTS (SELECT * FROM P WHERE NOT EXISTS (SELECT * FROM SP WHERE SP.S# = S.S# AND SP.P# = P.P#)); • ) • معادل است با: تهیه کنندگانی که قطعه ای وجود نداشته باشد که تهیه نکرده باشند.

34. امكانات جامعيتي و ایمنی • امکانات جامعیتی: كاربر مي‌تواند زمان وارسي جامعيت پايگاه را (براساس محدوديت ها و قواعد داده‌شده) خود مشخص كند. کاربر مي‌تواند درخواست كند كه اين وارسي بلافاصله باشد يا با تاخير. براي اين منظور از امكان زير استفاده مي‌شود: SET CONSTRAINTS (constraint-name) {DEFERRED | IMMEDIATE} • نکته: گزینه Deferred باعث می شود تا محدودیت ها را بصورت پویا هر زمان که نیاز است اعمال کنیم. مثلا در دستور Commit تراکنش می توان همه محدودیت های Deferred را به Immediate تبدیل و درنتیجه اعمال کرد. • امکانات ایمنی: كاربر مي‌تواند امتياز دستيابي به اشياء تعريف‌شده در شِما را به منظور انجام عمل مشخص، به كاربر ديگري بدهد. • براي اعطاي امتياز از دستور GRANT و براي لغو آن از دستور REVOKE استفاده مي‌شود.

35. انواع امتیازها • امتيازهايي كه يك كاربر مي‌تواند به يك كاربر ديگر بدهد: • USAGE: اجازه استفاده از يك ميدان • SELECT: اجازه دستيابي به تمام ستون هاي يك جدول • INSERT(X): اجازه درج يك مقدار در ستون X از يك جدول • INSERT: اجازه درج تمام ستون هاي يك جدول • UPDATE(X): اجازه بهنگام‌سازي ستون X از يك جدول • UPDATE: اجازه بهنگام‌سازي تمام ستون هاي يك جدول • DELETE: اجازه حذف سطر(ها) از يك جدول • REFRENCES(X): اجازه ارجاع به ستون X از يك جدول در تعريف محدوديت هاي جامعيتي. • REFRENCES: اجازه ارجاع به تمامی ستون ها از يك جدول در تعريف محدوديت هاي جامعيتي.

36. Dynamic SQL • در SQL2 کاربر می تواند در حین اجرای برنامه یک پرس و جو را آماده نماید و با استفاده از دستورات زیر اجرا کند. به این امکان SQL پویا می گویند. EXEC SQL PREPARE SQLCODE FROM: SQLSTATEMENT; EXEC SQL EXECUTE SQLCODE; • در دستورات فوق: • SQLSTATEMENT متغیری است از نوع کرکتر بطول مشخص، که در زبان میزبان تعریف شده و حکمی به زبان SQL در آن قرار می گیرد. • SQLCODE متغیری است از محیط SQL که صورت کامپایل شده حکم SQL را بخود می گیرد. • دستور PREPARE حکم مبدأ به SQL را بصورت کد مقصد قابل اجرا می کند. • دستور EXECUTE، حکم مورد نظر را اجرا می کند.

37. دستوراتي كه در نسخه SQL3 تغيير كردند يا به آن اضافه شدند: • تعريف نوع داده مجرد(Abstract Data Type) • تعريف زيرنوع (برای ایجاد مفهوم سلسله مراتب و وراثت برای ADTها) • امكان تعريف زيرجدول و زبرجدول • امكان ارث‌بري تعریف ستون‌هاي جدول جديد از ستون‌هاي جدول نامدار موجود • رويه‌اي شدن زبان (با اضافه کردن احکام انتساب، شرط، حلقه و...) • رهانا (Trigger) • نوشتن رويه • SQLادغام‌شده (ادغام احکام SQL در یک زبان میزبان) • واسط سطح فراخوان (Call-Level Interface (CLI)): می توان با فراخوانی روال هایی از CLI، درخواست های بازیابی داده ها را تنظیم کرد. • دستورات كنترل تراكنش‌ها (مانند: Start, Commit, Rollback)

38. رهانا (Trigger) • تعريف: قاعده (محدوديت) يا قواعدي است كه قبل يا بعد از بروز يك رويداد در پايگاه داده‌ها (معمولا يك عمل تغييردهنده داده‌ها) بايد اعمال شود. اين قاعده در سطح برنامه‌سازي، به صورت يك رويه از پيش تعريف‌شده (Stored Procedure) است كه به طور شرطي يا غيرشرطي، قبل يا بعد از انجام يك عمل در پايگاه داده‌ها، به طور اتوماتيك اجرا مي‌شود. • مزايا • اجراي آن تحت كنترل متمركز سيستم و نظارت مدير پايگاه داده‌ها است. • با وجود آن ديگر نيازي نيست كه محدوديت هاي مورد نظر جداگانه در هر برنامه كاربردي اعمال شوند و كنترل هاي لازم انجام شود. • براي معماري Client/Server DB بسيار مناسب است.

39. کاربردهای رهانا (Trigger) • اعمال قواعد جامعيت از جمله قواعد موسوم به Business Rule. • اعمال قواعد ايمني مبتني بر مقادير. • درج ركوردهاي ثبت عمليات در فايل ثبت تراكنش ها. • توليد نسخه‌اي از داده‌هاي ذخيره‌شده در يك سايت ديگر. • در انتشار اتوماتيك عمليات در پايگاه داده‌هاي توزيع‌شده وقتی که با تکنیک نسخه سازی، چند نسخه از داده ها در سایت های مختلف ذخیره شده باشند. • در بهنگام‌سازي ديدها در سيستم‌هاي رابطه‌اي.

40. نحوه تعریف رهانا (Trigger) • در SQL3 رهانا را با دستور زیر تعریف می نماییم: CREATE TRIGGER name {BEFORE | AFTER | INSTEAD OF} {INSERT | DELETE | UPDATE OF column-commalist} On TABLE_NAME [REFERENCING {NEW | OLD | NEW-TABLE | OLD TABLE} AS name] WHEN condition(s) [FOR EACH {ROW | STATEMENT}]

41. تراکنش (Transaction) • تعريف: به بيان ساده عملي است كه تغييري در پايگاه داده‌ها ايجاد كند. اين عمل كه طبعا به صورت يك برنامه يا بخشي از يك برنامه است، يا بايد به تمامي اجرا شود و يا اصلا اجرا نشود و مي‌گوييم كه تراكنش خاصيت تجزيه‌ناپذيري دارد. • یک تراکنش با دستور BEGIN TRANSACTION آغاز می شود و در صورتی که با موفقیت به پایان نرسد دستور ABORT اجرا خواهد شد و دستور COMMIT نشان دهنده انجام موفقیت آمیز تراکنش می باشد. • خواص تراکنش: • تجزيه‌ناپذيري (Atomicity): تراکنش یا باید تماما اجرا شود و یا نباید اجرا گردد. • سازگاري (Consistency): پایگاه داده پس از انجام تراکنش باید در وضعیت سازگار قرار گیرد. • جدايي (انفراد Isolation): تا زمانی که تراکنشی اتمام نیافته تغییرات حاصل از آن از دید دیگر تراکنش ها مخفی می ماند. • مانايي (دوامDurability ): تغییرات حاصل از اجرای یک تراکنش صحیح، نباید در هیچ صورتی از بین برود (با استفاده از تکنیک های ترمیم پایگاه داده)

42. ديد در مدل رابطه‌اي

43. دید (View) • ديد در مدل رابطه‌اي نوعي رابطه است، بنابراين مي‌توان آن را به كمك يك عبارت جبر رابطه‌اي يا حساب رابطه‌اي به شكل زير تعريف كرد: View name= Relational expression • دید در SQL: CREATE VIEW View-name [(Column-name(s))] AS Subquery . . . [WITH [CASCADE|LOCAL] CHECK OPTION];

44. دید در SQL • مثال: دیدی را که شامل دانشجویان رشته ریاضی و فیزیک باشد ایجاد نمایید. CREATE VIEW MAPHSTUD(STNUM, STLEV, STAREA) AS SELECT STID, STDEG, STMJR FROM STT WHERE STMJR=‘Math’ OR STMJR=‘Phys’ WITH LOCAL CHECK OPTION; • نکته1: با استفاده از گزینه CHECK در واقع قواعد جامعیتی را می توان بر روی دید اعمال نمود. • نکته2: اگر از گزینه LOCAL قبل از CHECK استفاده نماییم، قوانین جامعیت (مثلا محدودیت های آورده شده در مقابل WHERE) بر روی همان دید بصورت محلی اعمال می شود. در صورتی که از گزینه CASCADE استفاده گردد، قواعد جامعیتی بر روی دید جاری و تمامی دیدهایی که روی این دید تعریف شده باشد اعمال می شود. • نکته 3: عبارت SELECT در دستورات ایجاد View، اجرایی نیست بلکه اعلانی می باشد. یعنی با این دستور هیچ داده ای بازیابی نمی شود و فقط امکانی است برای مشخص کردن محدوده داده ای (Data Scope) کاربر.

45. دید در SQL • در SQL یک دید با دستورات زیر حذف می گردد: DROP VIEW View-name {restrict | cascade} • نکته: اگر از گزینه restrict استفاده نماییم فقط دیدی که نام آن آورده شده حذف می شود. در صورتی که از گزینه cascade استفاده گردد، دید جاری و تمامی دیدهایی که روی این دید تعریف شده باشد حذف می گردند. • مثال: DROP VIEW MAPHSTUD CASCADE;

46. مزایای دید • تامين‌كننده محيط انتزاعي براي كاربران سطح خارجي • تامين‌كننده پويايي بالا در تعريف پايگاه توسط كاربر • تسهيل‌كننده واسط كاربر برنامه‌ساز با پايگاه • امكاني است براي كوتاه‌نويسي يا ماكرونويسي پرسش ها (می توان دید پیچیده ای را یک بار تعریف کرد و در پرس و جو های مختلفی از آن استفاده نمود) • تامين‌كننده اشتراك داده‌اي • تامين‌كننده نوعي مكانيسم خودكار ايمني داده‌ها (بدلیل عدم دسترسی به همه داده ها) • تامين‌كننده استقلال داده‌اي منطقي. (مثلا با تبدیل یک جدول به دو جدول و حذف جدول اولیه، می توان از دو جدول درست شده یک دید مانند جدول اولیه ایجاد کرد تا برای دیدهایی که بر روی جدول اولیه ایجاد شده اند مشکلی ایجاد نشود) • امكان تعريف Data-Object با اندازه‌هاي مختلف (مثلا تمام رابطه مبنا یا قسمتی از آن)

47. معایب دید • ايجاد فزونكاري در سيستم براي انجام تبديل خارجي/ادراكي و احيانا خارجي/خارجي. مثلا دستور بازیابی اطلاعات که بر اساس دید صادر می شود باید ابتدا به دستور بازیابی بر روی یک جدول مبنا تبدیل شود و سپس این پرس و جو با تبدیل خارجی ادراکی به یک پرس و جو در سطح ادراکی تبدیل گردد و این باعث فزونکاری در تبدیل خارجی ادراکی خواهد شد. زمانی که بر روی یک دید دیدهای دیگری تعریف می شود فزونکاری برای تبدیل خارجی/خارجی هم خواهیم داشت. • عدم امكان انجام عمليات ذخيره‌سازي در بسياري از گونه‌هاي ديد و در نتيجه ايجاد محدوديت براي كاربر.

48. بازیابی از دید رابطه ای • چون ديد خود نوعي رابطه است، پس براي بازيابي از ديد هم يك عبارت جبري يا حسابي مي‌نويسيم. در SQL از همان دستور SELECT برای بازیابی از دید استفاده می کنیم. • مثال بازيابي از يك ديد با استفاده از دستورات SQL: CREATE VIEW V1 AS SELECT STID, STDEG FROM STT WHERE STPROG=‘Math’ SELECT STID FROM V1 WHERE STDEG=‘bs’;

49. عمليات ذخيره‌سازي در ديدهاي رابطه‌اي • براي انجام عمليات ذخيره‌سازي در ديد از همان دستورات سه‌گانه INSERT، UPDATE و DELETE استفاده مي‌شود. در تمامی دیدها این عملیات را نمی‌توان نجام داد. ديدها از نظر پذيرش عمليات ذخيره‌سازي دو دسته‌اند: • دیدهای پذیرا: • ديدهاي گزينشي • ديد گزينش – پرتوي داراي كليد رابطه مبنا • ديد پيوندي CK-CK • ديد پيوندي CK-FK • ديد حاصل اجتماع، اشتراك و تفاضل دو رابطه • دیدهای ناپذیرا: • ديد پرتوي / گزينشي-پرتوی فاقد كليد رابطه مبنا • ديد پيوندي NK-NK(NotKey-NotKey) • ديد پيوندي FK-FK • ديد حاوي صفت مجازي • ديد حاصل تقسيم

50. دیدهای پذیرا 1. دید گزینشی: ديد گزينشي حاصل عملگر گزينش در يك رابطه است. مثال: دیدی که مشخصات دانشجویان کارشناسی ارشد را دربرگیرد تعریف کنید. Create View Select-View AS Select * From STT WHERE STDEG = ‘ms’; • عملیات حذف و بهنگام سازی در این دید بدونه مشکل انجام می شود. • عمل درج در این دید قابل انجام است ولی دو مشکل ممکن است بوجود آید: • ممکن است کاربر سطری را درج کند که قبلا در جدول مبنا وجود داشته باشد. اگر کلید این سطر تکراری نباشد ناسازگاری در داده ها بوجود می آید و اگر کلید این سطر موجود باشد عملیات درج رد خواهد شد. • اگر کاربر بخواهد سطری با مقدار ‘bs’ برای فیلد STDEG درج کند، سیستم باید این درج را رد کند زیرا با شرایط داده شده در تعریف دید تعارض دارد. گزینه WITH CHECK OPTION برای درخواست این وارسی از سیستم می باشد.