slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Shahriar Pirnia pirnia PowerPoint Presentation
Download Presentation
Shahriar Pirnia pirnia

Loading in 2 Seconds...

play fullscreen
1 / 83

Shahriar Pirnia pirnia - PowerPoint PPT Presentation


  • 141 Views
  • Uploaded on

Database پايگاه داده ها قسمت سوم. Shahriar Pirnia http://www.pirnia.net. مفهوم رابطه نرمال : رابطه اي است که در آن تمام مقادير تمام صفات خاصه اتوميک باشد . مثال : رابطه BEFORE يک رابطه از درجه 2 است . ميدان PQTY يک ميدان مرکب است که از دو ميدان P# و QTY تشکيل شده است .

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Shahriar Pirnia pirnia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
shahriar pirnia http www pirnia net

Databaseپايگاه داده ها

قسمت سوم

ShahriarPirniahttp://www.pirnia.net

slide3

مفهوم رابطه نرمال :

رابطه اي است که در آن تمام مقادير تمام صفات خاصه اتوميک باشد .

مثال : رابطه BEFOREيک رابطه از درجه 2 است . ميدان PQTYيک ميدان مرکب است که از دو ميدان P# و QTY تشکيل شده است .

يکي از ميدانها ، مقاديرش رابطه است .

از لحاظ رياضي اين يک Nested Relation است . گاهي به آن (Not Normal Relation) ميگويند .

Codd ميگويد رابطه ها در مدل رابطه اي تعريف شده توسط ايشان بايد نرمال باشند .

یک تاپل

slide4

AFTER

اين يک رابطه نرمال (طبيعي) است .

يک تاپل

دليل اينکه چرا رابطه ها بايد نرمال باشند (چرا مقادير خاصه بايد اتوميک باشند . )

DATE با تاکيد گفته است که :

دليل اصلي نرمال بودن رابطه ها در مدل رابطه اي ، تامين سادگي است . Simplicity

1 – سادگي ظاهري

2 – سادگي انجام عمليات در Database .

3 – ساده تر شدن احکام DSL مخصوصا DML .

slide5

مثال :

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 در زبان وجود داشته باشد که مقداري را به يک مجموعه اضافه کند

slide6

.

مثلا :

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#)

slide7

نکته : اگر خاصيت دوم برقرار نباشد ، يعني زير مجموعه اي از مجموعه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 بايد مسير پيچيده تري را رفت

slide8

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# ;

slide9

(کليد خارجي) :

تعريف :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

کارمند

کارمند

مدیر است یا تحت مدیریت مدیر است

slide10

EMP# کليد اصلي است .#EMGR در همين رابطه کليد خارجي است .

نکته : يک رابطه 1:Nيکسويه توسط کليد خارجي پياده سازي مي شود .

نکته : لزومي ندارد کليد خارجي يک رابطه جزئي از کليد اصلي رابطه باشد . هرچند در SP چنين است .

SP ( S# , P# , Qty) کليد اصلي

در مدلينگ ارتباط1:N (يک به n) دوسويه از کليد خارجي استفاده مي شود و رابطه اي براي بيان ارتباط آن دو موجوديت طراحي مي شود که کليد اصلي اش تشکيل شده از کليدهاي اصلي دو رابطه است .

کلید خارجی

کلید خارجی

slide11

نکته : فايده کليد خارجي چيست ؟

امکاني است براي نشان دادن ارتباط بين موجوديتها . مثل اينکه در رابطه 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# بيانگر يک ارتباط محوري و اساسي مي باشد .

slide12

قواعد جامعیت INTEGRITY RULES

مجموعه قواعدی که به کمک آنها سیستم صحت و دقت داده های ذخیره شده در پایگاه و نیز ارتباطات بین انواع موجودیتها را کنترل می کند .( Tuple هم موجودیتها را نشان می دهد و هم ارتباطات بین آنها را (دید جدولی) )

قواعد جامعیت 1- کاربری (خاص) : تعریف شده توسط کاربر (منظور شخص DBA) است .کاربران

خارجی باید درخواست خود را به DBA بدهند.(UDR=User Defined Rules)

2- عام (متا قاعده Meta-Rule) : مستقل از Data های خاص مطرحند ،

Independent از Data های محیط خاص

قواعد جامعیت کاربری (خاص)میدانی : قاعده ای است ناظر به مقادیر یک میدان

ستونی : علاوه بر قواعد میدانی که بر روی مجموعه مقادیر میدان است ، قواعد جامعیت ستونی اخص از محدودیت میدانی است ، یعنی برای صفات خاصه بخصوصی در رابطه بخصوصی محدودیتی قایل بشویم

slide13

مثال میدانی : شماره تهیه کنندگان حتما به این صورت است :

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 مربوط به این قواعد جامعیت است .

slide14

قواعد جامعیت عام C1 : قاعده جامعیت موجودیتی Entity Integrity Rule ناظر به کلید اصلی است . C2 : قاعده جامعیت ارجاعی Refrential Integrity Rule ناظر به کلید خارجی است

نکته : کلید خارجی هم مثل کلید اصلی باید در تعریف رابطه آورده شود

قاعده C1 : می گوید : هیچ جز تشکیل دهنده کلید اصلی نمی تواند Null باشد ( هیچ مقدار Null Value داشته باشد)

Null Value : 1- مقدار ناشناخته : Unknown : مثلا STATUS تهیه کننده ای مشخص نباشد .

2- مقدار غیر قابل اعمال : Inapplicable مثلا شماره شناسنامه همسر

برای شخص مجرد غیر قابل اعمال است

slide15

Null Value با صفر یا Blank فرق می کند زیرا آنها مقدارند . هر چند سیستم باید امکانی برای بازنمایی Null-Value داشته باشد. ( درکتابها Null-Value را با ؟ نمایش می دهند )

* باید به عنوان یک مقدار ، یک مجموعه عملیاتی مشخص ناظر بر آن وجود داشته باشد ( محاسباتی منطقی مقایسه ای )

به عنوان مثال در مقایسه دو null value جه می توان گفت ؟Null ( < = > ) Null

لحظه به لحظه Database باید صحیح باشد .

Null = Null < >

slide16

منطق دو ارزشی برای عملیات منطقی 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 باید مقدار مشخصی داشته باشد.

slide17

قواعد جامعیت :

قواعد خاص : تعریف شده توسط کاربر

قواعد عام : 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)

slide18

* R2 به R1 ارجاع دارد یا رجوع می کند.

R1

R2

رابطه مرجعReferenced

رابطه رجوع کننده Referencing

نمودار ارجاع یا گراف ارجاع (از طریق مقدار کلید

خارجی)

Sp

P

مثال : S

مثال دیگر: EMP( EMP#, EMP name, ….., EMP MGR#.....)

کلید خارجی

کلید اصلی

این رابطه بر طبق بحث قبلی چونR1! و R2 برابرند. بنابراین آنرا Self Referencing گوییم.

slide19

EMP

شکل کلی یک مسیر ارجاع : R1 R2 ....... Rn-1 Rn

Rn Rn-1Ri ….. R1

حلقه ارجاع با n رابطه

مثال دیگر:

EMP( E#,Ename, …….. , DEPT# ……….)

کلید خارجی

DEPT( DEPT#, DEPT PHONE,……. , DEPTMGR# , ……. )

شماره کارمند کلید خارجی

slide20

DEPTMGR#

EMP DEPT

Dept#

تمرین : دو مثال قید کنید که در آن حلقه ارجاع با n=2 و دو مثال دیگر n=3 وجود داشته باشد.

سئوال: دو قاعده c1 و c2 چگونه باید به DBMS داده شود و چگونه رعایت شوند؟

نکته : قواعد C1 و C2 باید به نحوی به DBMS داده شوند.

نکته : باید توسط DBMS رعایت شوند.

قاعده C1 از طریق معرفی کلید اصلی در تعریف رابطه و نیز استفاده از گزینه NOT Null

slide21

CREATE RELATION S

( S# ……………… NOT NULL,

…..

…….

City CHAR (12) )

PRIMARY KEY S# ;

بدین طریق C1 تعریف و رعایت می گردد

برای معرفی قاعده c2: در تعریف رابطه کلید خارجی( در صورت وجود ) باید مشخص شود.

slide22

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;

slide23

ايجاد جدول 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)
slide24

چگونه قاعده C2 را رعایت کنیم :

سه مکانیسم برای رعایت C2 متصور است :

نکته : قاعده C2 باید در جمیع عملیات روی پایگاه رعایت شود .

مثال : فرض می کنیم در رابطه S تهیه کننده ‘S7’ وجود ندارد .

INSERT INTO SP

VALUES (S7,P1,60) ;

قاعده C2 می گوید این عمل باید Reject شود

درج یک تاپل

مثال : فرض می کنیم در رابطه S موجودیت S3 را داریم :

DELETE FROM S

WHERE S#=‘S3’ ;

آیا باید اجرا شود ؟ اگر بله با تاپلهای Referencing چه باید کرد ؟؟ و اگر نه ...

slide25

سه مکانیسم برای رعایت قاعده 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 می شود و زمانی امکانپذیر است که کلید خارجی جزء کلید اصلی نباشد .

slide26

متداولترین روش : روش اول است .

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

ولی در سیستمهای رابطه ای باید تصریح شود زیرا رابطه ها حسب ظاهر منفک از یکدیگرند و ارتباط بین رابطه ها علیرغم مجزا بودن دو رابطه به یکی از روشهای زیر انجام می گیرد.

از طریق مکانیسم ارجاع

یا

از طریق صفت خاصه مشترک که در Heading دو رابطه در سیستم رابطه ای باید تصریح شود

وعمل Cascade در سیستم رابطه ای باید تصریح شود.

نظر Date : بهتر است DBMS قبل از انجام عمل Warning دهد و بعد از کاربر سوال کند

slide27

تمرین : امکانات 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 ایجاد نظم در خروجیها می باشد

slide28

مثال : مشخصات تهیه کنندگان ساکن شهر 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 جبری خواهد بود.

slide29

SELECT S#, City

FROM S ;

مثال :

دو ستون از جدول را می دهد .

S# CITY

S1 C2

S2 C3

S3 C3

S4 C2

S5 C1

slide30

ادامه SELECTٍ

  • بازیابیبا جدول جواب منظم
  • Q : شماره تهیه کنندگان و وضعیت آنها را بدهید. جدول جواب به نظم صعودی مقادیرstatus مرتب شده باشد.

SELECT S# ,Status

FROM S

ORDER BY Status

ORDER BY Status DESC

ORDER BY2شماره ستون درجدول جواب

ORDER BY 2 DESC

ASCENDING صعودی default است و احتیاج به ذکر نیست.

ASCENDING نیاز به تصریح ندارد.

slide31

بازیابی مقدار محاسبه شده *

  • ً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 در جدولی مناسب با ستونهای نامدار وارد کرد.

slide32

روش دوم :

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 در جدولی مناسب با ستونهای نامدار وارد کرد.

slide33

پیاده سازی اپراتور 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) )

slide36

Q: مشخصات قطعات و تهیه کنندگان از یک شهر که قطعه آبی باشد.

AND

COLOR = ‘ BLUE’

  • لازم نیست حتما تمام مشخصات را بخواهید می توانید فقط برخی صفات خاصه را بخواهید. مثال:
  • Q: شماره قطعات و شماره تهیه کنندگان همشهر را بدهید.

SELECT S.S# , P.P#

FROM S,P

WHERE S.CITY = P.CITY

AND

COLOR = ‘ BLUE ‘ ;

slide37

نکته : چندین جدول را می توان 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#

slide38

ظاهرا ترتیب نوشتن جدولها در شرط FROM مهم نیست. اما در برخی سیستمها از جمله

توصیه هایی برای نوشتن ترتیب جدولها آورده شده است تا Optimizer

نهایتا بهتر عمل کند.

تمرین: با dataی SP, P , S مراحل اجرایی این Query نشان داده شود. ( join سپس حاصل را دوباره join سپس این دو ستون را باید استخراج کنید. )

توجه : DISTINCT OPTION , SQL به شما داده یعنی من به تو جواب Query را می دهم اگر تو می خواهی تاپلها یکتایی داشته باشد از کلمه DISTINCT استفاده کن

slide39

نکته : حاصل اجرای جبر رابطه ای و یا محاسبات رابطه ای همیشه رابطند . اما حاصل 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

slide40

از یک تکنیک دگر نامی استفاده شده است 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#

بدیهات

تکراری

slide41

در نتیجه خواهیم داشت

SELECT FIRST.S# , SECOND.S#

FROM S FIRST , S SECOND

WHERE FIRST.CITY = SECOND.CITY

AND FIRST.S# < SECOND.S#

slide42

تمرین : شماره جفت تهیه کنندگانی را بدهید که از یک شهر نباشد

SELECT FIRST.S# , SECOND.S#

FROM S FIRST , S SECOND

WHERE FIRST.CITY != SECOND.CITY

AND FIRST.S# < SECOND.S#

slide43

توابع جمعی ( گروهی) Aggregate Functions

در حاشیه : قدرت Select :اپراتورهای Project و Join و Select جبر رابطه ای را تواما ارایه می دهد.

برای پاسخگویی به سوالاتی از قبیل چه تعداد قطعه داریم ؟ ماکزیمم Quantity چقدر است ؟ خود Select قادر به پلسخگویی نیست. توابعی پیش بینی شده است که در متن Select بکار می روند .(مستقلا نمی توانید آنها را به کار ببرید)

این توابع عبارتند از :

COUNT (DISTINCT ( ... COUNT(*)

MAX

MIN

SUM

AVG

حالتی از COUNT موسوم به COUNT(*)تمام سطرها را – حتی تکراری - می شمارد.

slide44

مثال : تعداد محمولات را بدهید .( هر سطر SP یک محموله است) (SHIPMENT)

SELECT COUNT(*)

FROM SP ;

مثال : چند نوع قطعه تهیه شده است .

SELECT COUNT (DISTINCT P#)

FROM SP ;

مثال : کلا چند نوع قطعه وجود دارد

SELECT COUNT(*)

FROM P ;

مثال :

SELECT MAX(STATUS)

FROM S ;

slide45

استفاده از 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

slide46

مثالی از به عینیت درآوردن فیلد مجازی حاصل از پردازش می باشد. (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 برای بیان شرط یا شرایط ناظر به گروه .

slide47

شماره هر تهيه کننده و تعداد کل قطعاتي را که تهيه کرده بدهيدمثال :

select s#, sum(QTY)

from sp

group by s#

مثال :شماره تهيه کنندگاني را بدهيد که دو نوع قطعه تهيه کرده اند

select s#

from sp

group by s#

having count(*)=2

slide48

شماره هر تهيه کننده و تعداد انواع قطعاتي که تهيه کرده را در مورد تهيه کنندگاني که بيش از يک قطعه تهيه کرده اند ،بدهيد

select s#, count(*)

from sp

group by s#

having count(*)>1

در مورد قطعاتي که توسط سه تهيه کننده تهيه شده اند شماره قطعه و تعداد کل تهیه شده از قطعه را بدهید

select p#, sum(qty)

from sp

group by p#

having count(*)=3

slide49

با مفروض بودن جدول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

slide50

پرس و جو های فرعی 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

slide51

از این رو امکانی به نام SUBQUERY بوجود آمده است.

SUBQUERY همان ساختار SELECT – FROM – WHERE را دارد با این تفاوت که در درون یک SELECT دیگر نوشته می شود.

SELECT Sname

FROM S

WHERE S# IN

S# = ANY یا

( SELECT S#

FROM SP

WHERE P# = ‘ P2 ‘ )

OUTTER QUERY

S1 , S2 , S3 , S4

INNER QUERY پرس و جوی فرعی

slide52

نکته : در بعضی از نسخه های SQL اپراتوری به نام ANY بصورت کلی Theta ANY پیش بینی شده است که Theta یکی از اپراتورهای متعارف مقایسه است. ANY به معنای SOMEONE است.

  • عملکرد ANY = با عملکرد IN یکسان است.
  • هر کجا IN بتواند بیاید =ANY نیز می تواند .
  • نکته : عمل تو در تویی می تواند بیش از دو سطح باشد ( سطوح می توانند از لحاظ تئوری بیش از دو سطح باشد اما در عمل 98% Q ها با یک SELECT و یا حداکثر در دو سطح SELECT پاسخ داده می شوند. )
  • مثال : اسامی تهیه کنندگانی که حداقل یک قطعه آبی رنگ تهیه می کنند.

SELECT Sname

FROM S

WHERE S# IN ( SELECT S#

FROM SP

WHERE P# IN ( SELECT P#

FROM P

WHERE P.COLOR = ‘BLUE ‘ ) ( ;

سیستم از درونی ترین SELECT شروع میکند.

تمرین : با JOIN همین را بنویسید

slide53

با استفاده از JOIN

SELECT S.SNAME

FROM S , SP , P

WHERE S.S# = SP.S# AND

SP.P# = P.P# AND

P.COLOR = ‘BLUE’

slide54

نکته : می توان بجای اپراتور IN یا =ANYدر شرایط خاصی از همان اپراتورهای متعارف مقایسه ای استفاده کرد.

SELECT COL1

FROM table

WHERE COL2 = ( SELECT …..

>

<

مثال : شماره تهیه کنندگان هم شهر با ‘S1’ را بنویسید .

تمرین: همین Q را با JOIN بنویسید .

SELECT S#

FROM S

WHERE CITY = ( SELECT CITY

FROM S

WHERE S# = ‘ S1‘ )

* شرط: وقتی که پاسخ SELECT درونی یک مقدار باشد یا بعبارت دیگر مجموعه جواب SELECT درونی تک عضوی باشد .بنابراین

SELECT S# FROM S

WHERE CITY = ‘ C2 ‘

slide55

مثال: شماره تهیه کنندگانی را بدهید که وضعیت آنها ماکزیمم نباشد.

SELECT S#

FROM S

WHERE STATUS < ( SELECT MAX ( STATUS )

FROM S ) ;

نکات :

1- نکته اول همان نکته قبل است.

2- استفاده از توابع جمعی در SUBQUERY ها مجاز است.

slide56

راه حل دیگر در بعضی نسخه ها ی SQL (که Anyدارند)

SELECT S#

FROM S

WHERE STATUS < ANY (SELECT STATUS FROM S)

جواب این قسمت خود یک ستون STATUS است .

پس :

SELECT S#

FROM S

WHERE STATUS < ANY {20,10,30}

اگر وضعیت از یکی از اینها کمتر باشد جواب است ( زیرا دیگر ماکزیمیم نیست )

مقادیر ستون STATUS

slide57

تمرین : این دو راه حل را با هم مقایسه کنید ( از نظر کارایی)(تعداد مقایسه ها)

در راه اول الزاما Max باید یافت شود ولی در دومی خیر . (یک عبارت بر حسب تاپلهای ستون S بدست می آید .)

امکانات دیگر :

اپراتور Like

بازیابی از جدول دارای هیچ مقدار

اپراتور UNION

Q : شماره قطعاتی را بدهید که اسم آنها با کاراکتر C شروع شده باشد .

یعنی آغاز شونده با C

LIKE ‘%C’ یعنی مختوم به حرف C

یعنی این String وجود داشته باشد مهم نیست کجا

‘CAB--’ یعنی 5 حرف باشد و 3 حرف اولش CAB باشد .

توابع SUBSTR , INSTR

SELECT P#

FROM P WHERE PNAME LIKE ‘C%’;

WHERE PNAME LIKE ‘%CAB%’

RIGHT((LEFT(ST#, 4), 1)

slide58

Q : فرض : وضعیت S5 ، NULL می باشد .

SELECT S#

FROM S

WHERE STATUS > 13 ;

جدول جواب

SQL ،NULL را به عنوان یک عملوند مقایسه در عمل مربوط دخالت نمی دهد . مانند این است که مقایسه صورت نگرفته است . اما اگر بنویسیم :

نشان دهنده NULL

SELECT S#

FROM S

WHERE STATUS IS NULL

جدول جواب

پس می تواند NULL را بازشناسد و بنابراین تا حدی به NULL توجه شده است .

slide59

اپراتور UNION با همین Syntax وجود دارد و همچنین UNION ALL

مثال :Q : شماره قطعاتی را بدهید که یا توسط S3 تهیه شده باشد و یا وزن آنها کمتر از 15 گرم باشد .

SELECT P#

FROM SPWHERE S# = ‘S3’

UNION

SELECT P#

FROM P

WHERE WEIGHT <15

UNION فقط روی رابطه هایی می تواند عمل کند که Type-Compatible باشند .

Type-Compatible : دو رابطه را گویند هرگاه heading هایشان یکسان باشد .

مجموعه جواب فاقد تکراری است . زیرا UNION اپراتور جبر رابطه ای است . ممکن است User نخواهد تکراری ها حذف شود . در این صورت از UNION ALL استفاده می کنیم .

*SQL تمام اپراتورهای جبر رابطه ای را یا بصورت صریح دارد یا می تواند شبیه سازی کند .

R1 INTERSECT R2

تمرین :R1 MINUS R2 را در SQL بنویسید .

R1 PRODUCT R2

SQL امکانات محاسبات رابطه ای را هم دارد . مشخصا اپراتور EXISTS و NOTEXISTS را.

slide60

حل مثال قبل با استفاده از SUBQUERY: شماره قطعاتی را بدهید که یا توسط S3 تهیه شده باشد و یا وزن آنها کمتر از 15 گرم باشد

SELECT PNUM

FROM P

WHERE P.WEIGHT < 15

OR P.PNUM IN ( SELECT PNUM

FROM SP

WHERE SNUM = ‘S3’)

slide61

بازیابی به کمک EXISTS : Q : اسامی تهیه کنندگان قطعه P2 را بدهید . (تاکنون به سه روش این Q را زدیم ) از این جا نتیجه می گیریم که یک Query در SQL به گونه های مختلف (چندین گونه) تنظیم (formulate) می شود که منطقا نوعی افزونگی در روش می باشد . Date این را یک ایراد مطرح کرده است (اما از دید برنامه نویسان یک نکته مثبت است .)

SELECT SNAME FROM S

WHERE EXISTS (SELECT * FROM SP

(1)WHERE SP.S#=S.S# AND SP.P#=‘P2’)

نکته : وقتی در یک Query درونی به صفت خاصه ای بیرون از آن پرس و جو ارجاع می دهیم ، Qualifier باید قید شود . اما اگر صفت خاصه مربوط به همان Q درونی باشد ، الزامی نیست .

نکته :Clause شماره (1)ظاهرا شبیه به Clause ای است که در join می نویسیم . سیستم در EXISTS عمل پیوند انجام نمی دهد . شاید بتوان گفت که از این لحاظ EXISTS از join کاراتر است زیرا ضرب کارتزین انجام نمی دهد .

نحوه اجرا : به ازای هر سطر از رابطه S سیستم بررسی می کند آیا وجود دارد سطری در SP که S# آن همان سطر از S و P# آن برابر P2 ؟ پس وجود را چک می کند (EXISTANCIAL) (اگر وجود داشته باشد یعنی عبارت محاسباتی True باشد ) SNAME آن S# جواب است .

slide62

مثال : اسامی تهیه کنندگانی را بدهید که قطعه P2 را تهیه نکرده اند .

پاسخ با استفاده ا ز : NOT EXISTS

SELECT SNAME FROM S

WHERE NOTEXISTS (SELECT * FROM SP

WHERE SP.S#=S.S# AND SP.P#=‘P2’)

پاسخ با استفاده از SUBQUERY:

SELECT SNAME

FROM S

WHERE SNUM NOT IN ( SELECT SNUM

FROM SP

WHERE PNUM = ‘P2’)

slide63

توجه کنید که query ذیل پاسخ صحیح نمی باشد .

SELECT SNAME

FROM S,SP

WHERE S.SNUM = SP.SNUM

AND SP.PNUM != ‘P2’

در واقع اسامی تهیه کنندگانی را می دهد که حد اقل یک قطعه بجز P2 تهیه کرده اند .

لذا ممکن است تهیه کنندگان P2 را نیز شامل باشد و پاسخ سوال مورد نظر نمی باشد.

توجه کنید که query ذیل پاسخ صحیح نمی باشد .

SELECT SNAME

FROM S

WHERE SNUM IN ( SELECT SNUM

FROM SP

WHERE PNUM != ‘P2’)

slide64

Qتمرین : (تمرینی که در آن تمام feature های SELECT را باید استفاده کنید ) شماره قطعه ، وزن به گرم و کل تعداد تهیه شده از قطعاتی را بدهید که رنگ آنها آبی یا سفید باشد و وزن آنها بیش از 10 گرم و حداکثر تعداد تهیه شده از آنها بزرگتر از 500 باشد و ضمنا قطعاتی که QTY کمتر از 200 است در نظر گرفته نشود .جدول جواب به نظم نزولی مقدار Max مرتب شود ORDER BY MAX

slide65

نام تهیه کنندگانی را بدهید که همه قطعات را تهیه کرده اند

SELECT SNAME

FROM S

WHERE NOT EXISTS

(SELECT *

FROM P

WHERE NOT EXISTS

(SELECT *

FROM SP

WHERE SP.P#=P.P# AND S.S#=SP.S#))

slide66

SELECT SNAME

FROM S

WHERE FORALL PX € P

EXISTS SPX € SP ( S.S# = SPX.S#

AND SPX.P# = PX.P# )

FORALL X P(X) Ξ NOT EXISTS X NOT P(X)

SELECT SNAME

FROM S

WHERE NOT EXISTS PX € P

NOT EXISTS SPX € SP ( S.S# = SPX.S#

AND SPX.P# = PX.P# )

slide67

روش دوم با استفاده از ) SUBQUERY نام تهیه کنندگانی که همه قطعات را تهیه کرده اند(

select sname

from s

where s# in ( select s#

from sp

group by s#

having count(*) = (select count(*)

from p))

slide68

نام تهيه كنندگاني را بدهيد كه حداقل يك قطعه را تهيه كرده اند .

SELECT SNAME

FROM S

WHERE EXISTS

(SELECT *

FROM SP

WHERE S.S#=SP.S#)

slide69

نام تهیه کنندگانی را بدهید که حداقل یک قطعه را تهیه نکرده اند

SELECT SNAME

FROM S

WHERE EXISTS

(SELECT *

FROM P

WHERE NOT EXISTS

(SELECT *

FROM SP

WHERE SP.P#=P.P# AND S.S#=SP.S#))

slide70

نام تهیه کنندگانی را بدهید که هیچ قطعه ای را تهیه نکرده اند .

SELECT SNAME

FROM S

WHERE NOT EXISTS

( SELECT *

FROM SP

WHERE S.S#=SP.S#)

روش دوم : با استفاده از SUBQUERY :

SELECT SNAME

FROM S

WHERE S# NOT IN (SELECT S#

FROM SP)

slide71

اپراتور INSERT :

INSERT

INTO tablename [cal1 , cal2 , . . .]

VALUES <سطر کامل یا ناقص>

مثال :

INSERT INTO P

VALUES(‘P7’,’Pn7’,’RED’,10,’C2’)

INSERT INTO P(COLOR,WEIGHT, P#,CITY,PNAME)

VALUES(’RED’,10,’P7’, ’C2’, ‘PN7’)

INSERT INTO P

VALUES( ‘P7’ , , ,10 , ’C2’ )

سطر ناقص به شرطی قابل قبول است که برای Pname و Color در P مقدار NotNULL ذکر نکرده باشید .

slide72

INSERT INTO P(PNUM,WEIGHT,CITY)

VALUES('P7', 10 , 'C1')

وارد کردن سطر ناقص :

INSERT

INTO table

SUBQUERY

مثال :

CREATE TABLE TEMP

(PNUM CHAR(8) NOT NULL, SUMQTY INTEGER) PRIMARY KEY PNUM;

INSERT

INTO TEMP

SELECT P#,SUM(QTY)

FROM SP

GROUP BY P#;

فرم دوم دستور : INSERT

slide73

روش دوم :

SELECT P#,SUM(QTY)

INTO TEMP

FROM SP

GROUP BY P#;

این دستور خودش جدول temp را ایجاد می کند

CREATE TABLE TEMPروش سوم :

AS SELECT P#,SUM(QTY)

FROM SP

GROUP BY P#;

slide74

دستور UPDATE :

UPDATE tablename

SET calname = “expression” یا “Literal”

WHERE . . .

مثال :

UPDATE SP

SET QTY = 0

WHERE S# = S2 AND P# = ‘P2’

UPDATE S

SET STATUS = STATUS *2

WHERE CITY = ‘C3’

به هنگام سازی تک سطر (Tuple)

به هنگام سازی چند تاپل

slide75

به هنگام سازی بیش از یک جدول : شماره تهیه کننده S1 را به S11 تغییر دهید .

UPDATE S

SET S#=‘S11’

WHERE S#=‘S1’;

این UPDATE باید PROPAGATE شود توسط Cascade بخاطر رعایت قاعده C2 .

UPDATE SP

SET S#=‘S11’

WHERE S#=‘S1’;

این عمل ، توسط Cascade بخاطر رعایت قاعده C2 انجام شده است

slide76

مثال : 10 واحد به QTY تهیه کنندگان ساکن C3 اضافه کنید . (احتیاج به sub query دارید .)

UPDATE SP

SET QTY= QTY + 10

WHERE SNUM IN ( SELECT SNUM

FROM S

WHERE CITY = 'C3')

پاسخ به یک شکل دیگر :

UPDATE SP

SET QTY = QTY + 10

WHERE ‘C3’ = (SELECT CITY

FROM S

WHERE S.S# = SP.S#) ;

تمرین :QTY تهیه کنندگان ساکن C2 را صفر (0) نمایید .

slide77

UPDATE SP

SET QTY= QTY + 10

WHERE EXISTS ( SELECT *

FROM S

WHERE SP.S# = S.S# AND

S.CITY = 'C3')

slide78

مثال :update چند ستون از رکورد بطور همزمان

UPDATE P

SET COLOR = ‘YELLOW’,

WEIGHT = WEIGHT +5,

CITY = NULL

WHERE P# = ‘P2’ ;

slide79

دستور DELETE

مثال :حذف تک سطر :<S3,P1,50> را حذف کنید .

DELETE FROM SP

WHERE S#=‘S3’ AND P#=‘P1’

DELETE FROM SP

WHERE QTY < 300

حذف چند سطر : محموله های با QTY کوچکتر از 20 را حذف کنید .

slide80

مثال : تهیه کننده S2 را حذف کنید .

DELETE FROM S

WHERE S#=‘S2’;

DELETE FROM SP

WHERE S#=‘S2’;

DELETE FROM SP;

بخاطر رعایت قاعده C2 باید این حکم نیز اجرا شود :

بطور اتوماتیک سیستم اعمال می کند .

همه رکوردهای جدول را حذف می کند .

slide81

مثال : محمولات مربوط به تهیه کنندگان شهر c3 را حذف کنید

DELETE FROM SP

WHERE SNUM IN (SELECT SNUM

FROM S

WHERE CITY= ‘C3’)

DELETE FROM SP

WHERE ‘C3’ =( SELECT CITY

FROM S

WHERE SP.SNUM= S.SNUM )

slide82

ايجاد جدول 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)