I HC HUTRNG I HC KHOA HC

TS. NGUYN MU HN

C S D LIU PHN TN

HU- 2008

3

LI NI UNhu cu dng chung mt d liu hp nht ca cc t chc kinh t x hi ngy mt cao. Hn th na, cc t chc ny li c nhiu tr s phn tn nhiu v tr a l khc nhau. Vn t ra l lm th no c th qun l cc lung d liu v s dng chung d liu, chng trnh ca t chc. Vic qun l cc lung d liu trn c s mng cc b vi kin trc my qun l file n gin gy ra nhiu phin toi nht nh. V th cc h thng phn tn c thit k s dng cho cc h thng thng tin vi nhiu a im phn tn ngy mt pht trin. Mt h thng phn tn bao gm hai phn chnh: mng my tnh v h c s d liu phn tn. Do , vn tm hiu cu trc v giao thc ca mng my tnh l iu cn thit nghin cu cc h c s d liu phn tn. C s d liu phn tn l mt tp c s d liu c quan h logic vi nhau v c tri trn mt mng my tnh. Mi trm ca mng c kh nng x l t qun v c th thc hin cc ng dng cc b, mi mt trm cng c th tham gia vo t nht mt ng dng ton cc v c th truy xut d liu ti nhiu trm khc nhau. i vi cc h thng thng tin ln vi yu cu t ch ch gii quyt x l nhng cng vic hng ngy th nay tin ti p ng c nhng yu cu cung cp thng tin mc cao hn. Do yu cu cng vic, cc nh qun l khng nhng mun bit cc thng tin v cng vic ang din ra nh th no m cn mun bit cc thng tin tip theo sau l g, c ngha l thng tin mang tnh phn tch v h thng thng tin c kh nng h tr ra quyt nh. Tuy nhin, vic xy dng mt h thng nh th vp phi mt s hn ch v mt k thut, c bit l khi kch thc cng nh phc tp ca mi trng thng tin tng ln. Nhng h thng thng tin xy dng theo phng php truyn thng khng kh nng gii quyt cc vn ny. Khi nghin cu n h phn tn ni chung v c s d liu phn tn ni ring th cu hi t ra cho ngi s dng l: phn tn ci g? - l: thit b x l, chc nng, d liu, quyn iu khin. Chng ta s tho lun chi tit cc i tng phn tn trong 6 chng ca gio trnh ny. Gio trnh C s d liu phn tn c s dng cho sinh vin, hc vin cao hc chuyn ngnh cng ngh thng tin. Gio trnh c chia lm 6 chng. Chng 1: Tng quan v c s d liu phn tn, bao gm mt s khi nim c bn v mng my tnh, cc m hnh mng c s dng trong h phn tn, cc phng php qun l d liu t xa theo m hnh Client/Server. Chng 2, Thit k c s d liu phn tn. Mc ch ca chng ny nhm cung cp cho ngi lp trnh ng dng thy c cc mc trong sut khc nhau v mt c s d liu phn tn, c cung cp ca mt h qun tr c s d liu phn tn. ng thi4

tho lun v mt s phng php thit k mt c s d liu trong mi trng ny. Chng 3, X l truy vn trong c s d liu phn tn. Chng ny s trnh by cc vn lin quan n vic x l cu truy vn ca mt h qun tr CSDL phn tn, c th phn tch nhng vn lin quan trong bn giai on x l truy vn: phn r truy vn, nh v d liu, ti u ho tng th, ti u ho cc b. Chng 4, Qun tr cc giao tc phn tn. Qun tr giao tc phn tn nhm gii quyt mt s vn trong qu trnh truyn thng ca h phn tn nh: tin cy (reliabity), iu khin tng tranh, hiu qu s dng cc ti nguyn ca h thng. Hu ht cc nghin cu v qun tr giao tc u nhm mc ch gii quyt vn trn mt cch r rng, c bit trong lnh vc phc hi giao tc phn tn v iu khin tng tranh. Do , hiu c vic qun l giao tc phn tn l cn thit bit c s lin quan gia iu khin ng thi, c ch phc hi v cu trc ca h thng. Chng 5, iu khin tng tranh phn tn. Trong chng ny s gii thiu vi mt s thut ng chun m t cc thut ton iu khin tng tranh trong mi trng phn tn v mt m hnh chun cho mi trng ny. Bi ton iu khin tng tranh c tch lm hai bi ton con l ng b ha c-ghi v ghi-ghi. Mi thut ton iu khin tng tranh phi bao gm hai thut ton con gii quyt hai bi ton con ny. Bc u tin nhm mc ch hiu mt thut ton iu khin tng tranh tch bit vi thut ton con trong mi bi ton con. Thc ra, cc thut ton con u c cc thut ton iu khin tng tranh trong cc h qun tr c s d liu phn tn dng vi hai k thut chnh: kha hai pha v th t nhn thi gian.. Chng 6, C s d liu hng i tng phn tn. cc chng trn, thun tin trong trnh by, chng ta gi s cc vn c trnh by vi m hnh c s d liu quan h. Tuy nhin trong thc t nhiu h thng thng tin c ci t vi c s d liu hng i tng, do chng ny s gii quyt cc vn ny sinh khi d liu l hng i tng. Mc d chng ti rt c gng hon thin gio trnh ny nhng khng th trnh khi nhng thiu st v cch din t, b cc, ni dung v cc li c php. Rt mong c bn c gp . hon tt gio trnh ny chng ti xin cm n cc Thy c gio ca Khoa Cng ngh Thng tin, Trng i hc Khoa hc-i hc Hu gp , chnh sa gio trnh sm c ra mt bn c. Hu, Thng 12 nm 2008 Tc gi

5

MC LCCHNG 1. TNG QUAN V C S D LIU PHN TN........................ 12 1.1. S lc v mng my tnh.......................................................................... 12 1.2. Cc hnh thc t chc h thng phn tn ................................................... 14 1.2.1. T chc h thng theo m hnh mng ngang hng ............................. 15 1.2.2. T chc h thng theo kiu my dch v file (File server) ................. 17 1.2.3. T chc h thng theo m hnh khch/ch (client/server) .................. 17 1.3. Cc c trng ca cc loi hnh h thng phn tn .................................... 18 1.3.1. M hnh kin trc ca h phn tn Client/Server................................ 18 1.3.2. Cc chc nng ca kin trc client/server........................................... 20 1.3.2.1. Trnh din thng tin phn tn ....................................................... 20 1.3.2.2. Trnh din t xa ............................................................................ 21 1.3.2.3. Qun l d liu t xa .................................................................... 21 1.3.2.4. Phn tn chc nng....................................................................... 22 1.3.3. So snh c trng ca h thng File server v Client/Server.............. 22 1.3.4. X l phn tn (Distributed Processing): ............................................ 23 1.4. C s d liu phn tn (Distributed Database) .......................................... 23 1.4.1. nh ngha ........................................................................................... 23 1.4.2. H qun tr c s d liu phn tn....................................................... 25 1.4.3. So snh csdl phn tn v csdl tp trung............................................... 26 1.4.4. u v nhc im ca h phn tn ..................................................... 28 1.5. Cc loi truy xut CSDL phn tn.............................................................. 29 1.5.1. Truy xut t xa thng qua cc tc v c bn:...................................... 29 1.5.2. Truy xut t xa thng qua chng trnh ph tr ................................. 29 CHNG 2. THIT K C S D LIU PHN TN .................................... 32 2.1. Kin trc c bn ca mt c s d liu phn tn ....................................... 32 2.1.1. S tng th (Globle Schema) ......................................................... 33 2.1.2. S phn on (fragment Schema) .................................................. 33 2.1.3. S nh v (allocation Schema) ...................................................... 34 2.1.4. S nh x a phng (Local mapping Schema) ........................... 34 2.2. Kt ni d liu trong h thng c s d liu phn tn ............................... 35 2.2.1. H thng c s d liu thun nht (Homogeneous Distributed Database) ................................................................................................................ 35 2.2.2. H thng c s d liu khng thun nht............................................ 366

2.2.3. Nhng kt ni c s d liu (Database Links).................................... 37 2.3. Cc c im chnh ca h phn tn .......................................................... 38 2.3.1. Chia s ti nguyn ............................................................................... 38 2.3.2. Tnh m ............................................................................................... 38 2.3.3. Kh nng song song ............................................................................ 39 2.3.4. Kh nng m rng ............................................................................... 39 2.3.5. Kh nng th li .................................................................................. 39 2.3.6. m bo tin cy v nht qun ............................................................. 40 2.3.7. Tnh trong sut (transparency). ........................................................... 40 2.4. Ni dung thit k cc h thng phn tn .................................................... 43 2.4.1. Qu trnh thit k................................................................................. 43 2.4.2. Cc sn phm sau khi thit k ............................................................. 43 2.5. Cc chin lc phn tn d liu ................................................................. 44 2.5.1. Tp trung d liu ................................................................................. 44 2.5.2. Chia nh d liu .................................................................................. 44 2.5.3. Sao lp d liu..................................................................................... 44 2.5.4. Phng thc lai ................................................................................... 45 2.6. Thit k c s d liu phn tn .................................................................. 45 2.6.1. S thit k tng th c s d liu phn tn .................................... 45 2.6.2. Cc phng php thit k CSDL phn tn.......................................... 46 2.6.2.1. Phng php thit k t trn xung ............................................. 46 2.6.2.2. Phng php thit k t di ln ................................................. 49 2.7. Phn mnh d liu...................................................................................... 49 2.7.1. Cc l do phn mnh ........................................................................... 49 2.7.2. Cc kiu phn mnh ............................................................................ 50 2.7.2.1. Phn mnh ngang ......................................................................... 50 2.7.2.2. Phn mnh dc ............................................................................. 51 2.7.2.3. Phn mnh hn hp ...................................................................... 51 2.7.3. Cc yu cu v vic phn mnh .......................................................... 51 2.8. Cp pht ti nguyn trong h phn tn ....................................................... 54 2.8.1. Bi ton cp pht ti nguyn ............................................................... 54 2.8.2. Thng tin cp pht............................................................................... 55 CHNG 3. X L TRUY VN TRONG CSDL PHN TN........................ 57 3.1. Gii thiu v x l truy vn ....................................................................... 57

7

3.2. Ti u ha cu truy vn trong CSDL tp trung.......................................... 58 3.2.1. S lc v ngn ng truy vn c cu trc-SQL.................................. 59 3.2.2. Cc chin lc ti u ha cu truy vn trong CSDL tp trung ........... 60 3.2.2.1. Thut ton INGRES ..................................................................... 60 3.2.2.2. Thut ton System R .................................................................... 64 3.3. Ti u ha cu truy vn trong CSDL phn tn .......................................... 69 3.3.1. Chun ho............................................................................................ 69 3.3.2. Phn tch .............................................................................................. 70 3.3.3. Loi b d tha ................................................................................... 73 3.3.4. Vit li truy vn................................................................................... 73 3.4. nh v d liu phn tn ............................................................................. 77 3.4.1. Rt gn theo phn mnh ngang nguyn thu ...................................... 78 3.4.2. Rt gn theo phn mnh dc............................................................... 80 3.4.3. Rt gn theo phn mnh gin tip....................................................... 81 3.4.4. Rt gn theo phn mnh hn hp........................................................ 82 3.5. Ti u ho truy vn phn tn...................................................................... 83 3.5.1. Mc tiu ca b x l truy vn............................................................ 83 3.5.2. c trng ca b x l truy vn.......................................................... 84 3.5.2.1. c trng v ngn ng ................................................................. 84 3.5.2.2. Cc kiu ti u ho....................................................................... 84 3.5.2.3. S iu chnh vic ti u.............................................................. 85 3.5.2.4. Da vo kt qu thng k............................................................. 85 3.5.2.5. Cc trm quyt nh ..................................................................... 85 3.5.2.6. S khai thc ca cu trc mng.................................................... 85 3.5.2.7. Khai thc cc on sao chp......................................................... 86 3.5.2.8. S dng cc na kt ni ............................................................... 86 3.5.3. M hnh chi ph ca b ti u truy vn ............................................... 86 3.5.4. Cc thng k CSDL............................................................................. 88 3.5.5. Lc lng ca cc kt qu trung gian ................................................. 89 3.5.5.1. Php chn ..................................................................................... 89 3.5.5.2. Php chiu .................................................................................... 89 3.5.5.3. Php ni........................................................................................ 89 3.5.5.4. Php na ni ................................................................................. 90 3.5.5.5. Php hp....................................................................................... 90

8

3.5.5.6. Php tr ........................................................................................ 90 3.5.6. V d .................................................................................................... 90 3.6. So snh mt s thut ton ti u ho truy vn phn tn............................. 91 3.6.1. Thut ton INGRES phn tn.............................................................. 92 3.6.2. Thut ton SYSTEM phn tn (R*) ................................................... 94 3.6.3. Thut ton SDD-1 ............................................................................... 96 3.6.4. Thut ton AYH .................................................................................. 99 3.7. Phn kt lun ............................................................................................ 105 CHNG 4. QUN TR CC GIAO TC PHN TN ................................. 108 4.1. Tng quan v qun l giao tc.................................................................. 108 4.1.1. Cc tnh cht ca giao tc.................................................................. 109 4.1.2. Mc tiu ca qun l giao tc............................................................ 112 4.1.3. Cc giao tc phn tn......................................................................... 113 4.1.4. Qun l kho trong giao tc .............................................................. 113 4.2. S h tr nguyn t ca cc giao tc phn tn......................................... 115 4.2.1. S phc hi trong cc h thng tp trung.......................................... 115 4.2.1.1. Cc s c trong cc c s d liu tp trung ............................... 115 4.2.1.2. Nht k (logs):............................................................................ 116 4.2.1.3. Cc quy trnh phc hi: .............................................................. 118 4.2.2. Cc s c truyn thng trong c s d liu phn tn ........................ 119 4.2.3. Khi phc cc giao tc phn tn........................................................ 121 4.2.4. Giao thc y thc 2 pha (2-Phase Commitment Protocol)................ 125 CHNG 5. IU KHIN TNG TRANH PHN TN............................. 132 5.1. M hnh x l giao tc ............................................................................. 133 5.1.1. Cc nh ngha................................................................................... 133 5.1.2. M hnh x l giao tc tp trung. ...................................................... 135 5.1.3. M hnh x l giao tc phn tn........................................................ 136 5.2. Phn tch bi ton iu khin tng tranh................................................ 138 5.2.1. Tnh kh tun t ................................................................................ 140 5.2.2. M hnh iu khin tng tranh........................................................ 142 5.2.3. Thi gian v nhn thi gian trong CSDL phn tn ........................... 143 5.3. Cc k thut iu khin tng tranh ........................................................ 145 5.3.1. Cc k thut ng b ha da trn kha hai pha .............................. 145 5.3.2. Qu trnh thc hin 2PL c bn ........................................................ 146

9

5.3.3. K thut 2PL biu quyt.................................................................... 147 5.3.4. K thut 2PL tp trung. ..................................................................... 148 5.3.5. Pht hin v ngn chn tc nghn ..................................................... 148 5.3.5.1. Ngn chn tc nghn .................................................................. 150 5.3.5.2. Pht hin tc nghn..................................................................... 152 5.4. Cc k thut ng b ha da trn th t nhn thi gian ........................ 153 5.4.1. S thc hin T/O c bn.................................................................... 153 5.4.2. Lut ghi Thomas................................................................................ 156 5.4.3. a phin bn T/O .............................................................................. 156 5.4.4. Duy tr T/O ........................................................................................ 159 5.4.5. Qun l nhn thi gian ...................................................................... 163 5.5. DC-ROLL................................................................................................. 163 5.5.1. ROLL (Request Order Linked List ). ................................................ 164 5.5.1.1. Cc thao tc ROLL c bn . ....................................................... 165 5.5.1.2. ROLL da trn s iu khin tng tranh. ........................... 165 5.5.2. Kin trc chu trnh d liu gc.......................................................... 166 5.5.3. DC-ROLL.......................................................................................... 167 CHNG 6. C S D LIU I TNG PHN TN ............................. 170 6.1. Tng quan v c s d liu hng i tng .......................................... 170 6.1.1. i tng v nh danh i tng .................................................... 171 6.1.2. Thuc tnh v phng thc ............................................................... 171 6.1.3. Lp .................................................................................................... 172 6.1.4. K tha .............................................................................................. 174 6.2. Thit k c s d liu hng i tng ................................................... 177 6.2.1. Phn hoch ngang lp: ...................................................................... 178 6.2.2. Phn mnh dc lp ............................................................................ 179 6.2.3. Phn hoch ng dn ...................................................................... 179 6.2.4. Cc thut ton phn hoch lp .......................................................... 179 6.2.4.1. Phng php da vo chi ph ..................................................... 180 6.2.4.2. Cp pht...................................................................................... 180 6.2.5. Nhn bn............................................................................................ 180 6.3. Qun l i tng..................................................................................... 180 6.3.1. Qun l nh danh i tng............................................................. 180 6.3.2. iu ch con tr ................................................................................ 181

10

6.3.3. Di tr i tng ................................... Error! Bookmark not defined. 6.3.4. Lu tr i tng phn tn.................. Error! Bookmark not defined. 6.3.5. Lm t i tng ................................ Error! Bookmark not defined. 6.4. Qun l giao dch i tng..................................................................... 185 Ti liu tham kho........................................... 188Error! Bookmark not defined.

11

CHNG 1. TNG QUAN V C S D LIU PHN TN

Mt h thng phn tn bao gm hai phn: mng my tnh v h c s d liu phn tn. Do , vn tm hiu cu trc v giao thc ca mng my tnh l iu cn thit nghin cu cc h c s d liu phn tn. Trong chng ny chng ta nhc li cc khi nim cn b v mng my tnh v cc giao thc truyn d liu trong mt h phn tn. ng thi chng ta cng tho lun v cc phng php truy xut d liu t xa ca h ny. 1.1. S lc v mng my tnh Mt mng my tnh l mt tp cc my tnh t vn hnh, c kt ni li v c kh nng trao i thng tin gia chng. Cc my tnh trn mt mng thng c gi l cc nt hay cc trm, chng to ra cc phn cng c bn ca mng. Cc nt ny c kt ni li vi nhau bi mt ng truyn. Mt mng my tnh l mt trng hp c bit ca mi trng x l phn tn, trong cc my tnh l cc thit b c kt ni vo knh truyn d liu. C nhng loi mng my tnh c bn sau: + Mng hnh sao: Tt c cc my tnh c ni vi mt my tnh trung tm. Bi v mi my tnh u c ng truyn ring vi my tnh trung tm nn cn phi c mt tho thun gia cc my v tinh v my tnh trung tm khi chng mun trao i thng tin. Loi mng ny thng c dng trong cc t chc c nhiu chi nhnh nm nhiu vng khc nhau, my tnh trung tm c t ti vn phng chnh hoc ti trung tm vng. Trong trng hp ny vic x l cc b c x l ti mi nt v d liu cui cng s c truyn n my trung tm. Mt khuyt im ca mng hnh sao l tin cy thp. V giao tip ca hai my tnh ph thuc vo my tnh trung tm, mt s c ti nt ny s lm cho vic truyn trn mng ngng tr hon ton. Mt khuyt im khc l ti trng qu cao trn my tnh trung tm; v n phi iu phi vic giao tip trn mng, ti trng ti cao hn cc trm khc. V th ngi ta thng dng my tnh trung tm mnh hn cc my tnh khc. Do nhng khuyt im ny, mng hnh sao ch c dng khi d liu truyn gia cc my v tinh khng ln.

12

Hub

Hnh 1.1: Mng hnh sao

Hnh 1.2: Mng vng

+ Mng vng: cc my tnh c ni vi mi trng truyn (ng truyn) c dng mt vng khp kn. Truyn d liu quanh vng thng theo mt chiu, v mi trm ng vai tr l mt b chuyn tip. Khi nhn c mt thng bo, n kim tra a ch, sao chp thng bo nu thng bo c gi cho n ri truyn thng bo i tip. Vic iu khin truyn tin trn mng xoay vng thng c thc hin bng th iu khin. Cc mng ch c mt mi trng iu khin xoay vng th c tin cy thp, n gin l nu ng ni ch b ct t mt im no th c th lm ngng ton b hot ng ca mng. c c tin cy cao hn, ngi ta c th s dng loi topo mng hai vng. Trong mt mng nh th, s c ti mt im ni khng lm mt kh nng truy xut n phn cn li ca mng bi c th truyn bng cch chuyn qua vng th hai. + Mng bus: mng bus l mng trong c mt knh chung chuyn d liu, cc my tnh v cc thit b u cui s c gn vo . kiu mng ny, vic iu khin ng ni c thc hin bng hai cch chnh. l phng php CSMA v CSMA/CD. Ngoi hai phng php c bn ny, bus cng c th c iu khin bng th.

Hnh 1.3: Mng bus

+ Mng hn hp: Cc mng truyn thng thng c cc ng ni v nh. Ngha l cc ng ni khng c tnh h thng cng khng tun theo mt khun mu no. Chng ta c th gp mt nt ch ni vi mt nt khc v c nhng nt ni vi nhiu nt khc. Cc ni kt gia cc my tnh trn mng Internet thuc loi ny.13

HUB

Hub

Hub

B chuyn i cp

Hnh 1.4: Mng hn hp

+ Mng thm Mesh (cu trc kt ni y ): Mt lc ni kt khc l ni kt y (mng thm), trong mi nt u c ni kt vi cc nt cn li. Mt cu trc nh th r rng l cung cp c tin cy cao hn v kh nng hot ng tt hn nhng cu trc ni trn. tuy nhin n cng l cu trc c chi ph cao nht. Lc ni kt y nh vy l khng thc t. Ngay c khi vi s lng my tnh trn mng kh t th s ni kt cn c s rt Hnh 1.5: Mng Mesh ln. 1.2. Cc hnh thc t chc h thng phn tn H thng phn tn thng s dng cc m hnh kt ni mng: m hnh mng ngang hng (peer-to-peer), my dch v file (File server) v m hnh mng khch/ch (Client/Server).

14

1.2.1. T chc h thng theo m hnh mng ngang hng Trong m hnh ny, cc my tnh c nhn v my trm c th c s dng nh mt h thng c lp tr gip cc ng dng a phng. Mi thnh vin trong mng c vai tr ngang nhau, t qun l ti nguyn ca chnh mnh v chia s ti nguyn cho cc my tnh khc trn mng. Mi mt my tnh trn mng va ng vai tr my ch (Server), va ng vai tr l my khch (Client). y l m hnh mng n gin, ph hp vi nhng h thng mng nh khng c yu cu cao v bo mt.

Hnh 1.7: M hnh mng ngang hng

c im ni bt ca h thng ny l d liu c t chc cc nt c chc nng nh nhau, ng thi s t chc d liu cc nt ny li c th rt khc nhau, t cn phi c: + nh ngha d liu ti mi v tr: ti mi nt phi xy dng lc d liu cc b LIS (Local Internal Schema) + M t cu trc logic ton cc: Lc khi nim ton cc GCS (Global Conceptual Schema). + M t cu trc logic ti mi v tr, iu ny xy ra do nhn bn v phn mnh, gi l lc khi nim cc b LCS (Local Conceptual Schema). + M t cu trc du liu ca cc ng dng gi l lc ngoi gii ES (External Schema). Cu trc ca h thng bao gm hai thnh phn chnh: B phn tip nhn ngi dng (User Processor) v b phn x l d liu (Data Processor). Hai modun ny c t chung trn mi my ch khng tch bit nh h thng khch/i l. Cc chc nng c bn ca tng modun nh sau: + User Interface Handler - Giao tip ngi s dng: Din dch yu cu, nh dng kt qu. + Semantic Data Controler - Kim sot d liu ng ngha: Da vo lc khi nim ton cc kim tra cu vn tin c thc hin c hay khng.15

+ Global Query Optimizer - Ti u ha cu hi ton cc: nh ra chin lc thc thi tt nht trn cc nt. + Global Execution Monitor - iu khin thc thi cu vn tin ton cc. + Local Query Processor - X l cu hi cc b + Local Recovery Manager - Qun l khi phc cc b: Qun l s nht qun khi c s c. + Run-Time Support Processor - B phn h tr thc thi: Qun l truy xut c s d liu.

Hnh 1.8: S kin trc ca h phn tn ngang hng

16

1.2.2. T chc h thng theo kiu my dch v file (File server) Trong mi trng mng LAN, tt c cc thao tc d liu u din ra my trm. Mt s my dch v file c gn trc tip vo mang LAN. Mt my dch v file l mt thit b qun l cc hot ng file v phc v cc my tnh c nhn c kt ni trong mng LAN. V cu hnh, trn cng ca my dch v file mi my c nhn c phn chia mt dung lng c nh, chng trnh cc my tnh c nhn c th tham chiu n cc file trn phn a tng ng ca n bng mt c t ng dn. Khi s dng mt CSDL trong mi trng my dch v file, mi my c nhn c php s dng chng trnh ng dng CSDL trn n. Nh vy l c mt CSDL trn my dch v file v nhiu bn sao ca n hot ng trn mi my tnh c nhn ang hot ng. My dch v file n gin nh mt thit b lu Hnh 1.9: My dch v file tr d liu dng chung v l s m rng ca my tnh c nhn. Nh vy, khi cc my tnh c nhn lm vic v c yu cu, my dch v file s gi ton b file n my tnh c nhn, v thao tc d liu s c thc hin. Cc hot ng an ton d liu cng c thc hin my tnh c nhn. Nhng hn ch ca my dch v file C 3 hn ch khi s dng my dch v file trn mng LAN D liu di chuyn trn mng qu nhiu Vic kim sot d liu l phi tp trung Cc my trm phi mnh 1.2.3. T chc h thng theo m hnh khch/ch (client/server) Trong m hnh ny, my ch (server) c chc nng iu khin, lu tr CSDL, x l cc truy vn v qun l vic khai thc ti nguyn trn mng ca cc my tnh khc. Thut ng client c s dng ch ngi khai thc ti nguyn mng. Mi mt ngi khaiHnh 1.10: M hnh Client/Server 17

thc mng s dng mt my tnh (gi l my trm hay workstation) kt ni n my ch khai thc ti nguyn mng. Cc my trm thng qun l cc giao din v trnh din d liu. 1.3. Cc c trng ca cc loi hnh h thng phn tn Vic chuyn cc ng dng trn my tnh c nhn cng nh cc ng dng trn cc h thng my ln trung tm sang m hnh phn tn l mt xu hng pht trin mnh. Vn t ra l, cn la chn hnh thc phn tn thch hp no cho mi m hnh c chuyn i. 1.3.1. M hnh kin trc ca h phn tn Client/Server Chc nng ca h thng c chia lm hai lp: + Chc nng i l - server function + Chc nng khch hng - client function. Trong h thng Client/Server cc thao tc x l d liu p ng yu cu ca khch hng u c thc hin bi cc chc nng i l, ch c kt qu c gi tr cho khch hng. H khch c cc tng: + Giao din tng tc vi ngi s dng (User Interface), cc chng trnh ng dng (Application Program), ... + H qun tr c s d liu khch hng (Client DBMS). + Cc phn mm mng c chc nng truyn tin (Communication Software). H i l c cc tng: + Cc phn mm mng c chc nng truyn tin. + Tng kim sot ng ngha ca d liu (Semantic Data Controler). + Tng ti u ha cu hi (Query Optimizer). + Tng qun l cc giao tc (Transaction Manager). + Tng qun l khi phc (Recovery Manager). + Tng h tr thc thi (Run - time Support Processor) . + H iu hnh qun l chung v giao tip vi c s d liu vt l.

18

Hnh 1.11: S kin trc h phn tn client/server

H Client/Server c u im l x l d liu tp trung, trn ng truyn ch c cc gi tin yu cu (cu hi) v cc kt qu p ng cu hi, gim ti c khi lng truyn tin trn mng kt hp vi thit b ti i l rt mnh s tng tc x l d liu ca c h thng19

M hnh h phn tn phc hp S khc bit c bn so vi h phn tn ngang hng l ch h phc hp khng c (hoc c khng y ) mt lc khi nim ton cc.

Hnh 1.12 S kin trc ca h phn tn phc hp

1.3.2. Cc chc nng ca kin trc client/server 1.3.2.1. Trnh din thng tin phn tn Mc ch ca trnh din thng tin phn tn trong kin trc client/server l lm mi cc ng dng trn cc my khch v nh dng li d liu do server qun l. Kt qu ny s dng vo vic lp bng biu bo co m khng ph hy hoc phi vit li h thng c. Trnh din phn tn hn ch c s hot ng ca cc biu mu ang tn ti, v khi cn thit nhng n th trnh20

din trn my khch v server c th c thay i v bo tr ng thi. Chng ta c th thy c vic trnh din thng tin phn tn qua bng di y. Chc nng Qun l d liu Phn tch d liu Trnh din d liu Client Server Qun l mi d liu Phn tch mi d liu D liu trnh din trn server S dng cng ngh rnh c nh dng trnh din din ca server gi d cho ngi dng liu gi cho cc clients Trnh din thng tin phn tn 1.3.2.2. Trnh din t xa Kiu trnh din ny t tt c cc chc nng trnh din d liu trn cc clients nn phn mm trn cc my khch c kh nng trnh din nhng d liu c nh dng theo ngi s dng. Khi ngi s dng cn thay i cc biu mu, bo co hoc ni dung mi th ch cn bo tr phn mm trn my khch. Chc nng Qun l d liu Phn tch d liu Trnh din d liu D liu phn tch trn server c nh dng trnh din cho ngi dng Trnh din t xa 1.3.2.3. Qun l d liu t xa Chc nng Qun l d liu Phn tch d liu Trnh din d liu D liu th c ly t server v c phn tch Trnh din tt c d liu Client Server Qun l mi d liu Client Server Qun l mi d liu Phn tch mi d liu

21

1.3.2.4. Phn tn chc nng Cc chc nng phn tn c t trn c my khch ln my ch. Ton b chc nng trnh din d liu c t trn my khch v ton b chc nng qun l d liu c t trn my ch. Chc nng Qun l d liu Phn tch d liu Client Server Qun l mi d liu Cc d liu c ly v phn Cc d liu c ly v tch t server phn tch t server sau c truyn cho cc clients Tt c d liu (c phn tch trn c server v clients)

Trnh din d liu

Chc nng Qun l d liu Phn tch d liu Trnh din d liu

Client Qun l d liu a phng D liu c ly t c server v cc clients phn tch Tt c d liu Phn tn d liu

Server Chia s qun l d liu trn server

1.3.3. So snh c trng ca h thng File server v Client/Server C hai m hnh ny u s dng my c nhn, my trm kt ni nhau trn mt mng LAN. Trong khi chc nng ca File server th tr gip vic phn tn d liu th kin trc Client/Server li h tr c phn tn d liu ln phn tn x l. Bng di y cho thy s khc nhau c bn ca hai loi kin trc trn. Chc nngQun l d liu Phn tch d liu

ClientQun l d liu a phng

ServerChia s qun l d liu trn server

D liu c ly t c server D liu c ly t server v cc clients phn tch phn tch. Sau gi cho my khch phn tch22

tip v trnh din Trnh din d liu Tt c d liu

c trngX l

File ServerCh my khch

Client/ServerC my khch v my ch Cao, Server m nhim Cao, Server m nhim Truyn d liu nhiu mc Hn hp mt s phn mm c th gi n my khch

Truy nhp d liu ng Thp, mi my khch th.hin thi An ton v ton vn CSDL S dng mng Bo tr phn mm Phn cng v h thng Thp, my khch qun l File ln, chuyn c file Thp, ch my Server

Ghp ni my khch v Ghp ni my khch v Server c th phi hp S mm do ca phn mm Server c th phi hp

1.3.4. X l phn tn (Distributed Processing): Kin trc client/server vi x l phn tn cho php phi hp tt nht cc c trng ca h phn tn nh lin kt d liu v chng trnh trn my ch v ch li chc nng trnh din trn my khch. M hnh ny cho php nh v mt cch mm do c chc nng phn tch v x l d liu ni m chng hot ng tt nht. 1.4. C s d liu phn tn (Distributed Database) 1.4.1. nh ngha Mt c s d liu (CSDL) phn tn l mt tp hp d liu, m v mt logic tp hp ny thuc cng mt h thng, nhng v mt vt l d liu c tri trn cc v tr khc nhau ca mt mng my tnh. C hai im quan trng c nu ra trong nh ngha: Phn tn: D liu khng c tr trn mt v tr m c phn b rng khp trn nhiu my tnh t ti nhiu v tr khc nhau, y l im phn bit mt c s d liu phn tn vi mt c s d liu tp trung. Tng quan logic: D liu trong h phn tn c mt s thuc tnh rng buc chng vi nhau. iu ny gip chng ta c th phn bit mt c s d liu phn tn vi mt tp hp c s d liu tp trung, cc file d liu c lu tr ti nhiu v tr khc nhau, iu ny thng thy trong cc ng dng m h thng s phn quyn truy nhp d liu trong mi trng mng.23

H thng mng thng tin cho php ngi dng chia s d liu, v vy ngi dng hoc ng dng mt v tr A u c th truy cp hay cp nht d liu ti v tr B no . Cc v tr ca mt c s d liu phn tn c th tri rng trn mt khu vc ln (ton th gii) hoc mt phm vi hp (to nh). Cng nh vy, my tnh cc v tr phn tn c th l bt c loi no, t my vi tnh n cc my tnh ln. V d: Mt cng ty phn mm c cc vn phng Hu, H ni, HCM v Cn th, s dng c s d liu sau y qun l cc hot ng ca cng ty: D liu v nhn vin: NHANVIEN (MANV, TENNV, CHUCVU) D liu v cc d n: DUAN (MADA, TENDA, NGANSACH, DIADIEM) D liu v lng: TLUONG (CHUCVU, LUONG) D liu v phn cng: HOSO (MANV, MADA, NHIEMVU, THOIGIAN) Mi my tnh vi c s d liu thng k a phng ca n ti mi chi nhnh c t mt v tr ca c s d liu phn tn. Cc my tnh c ni vi nhau bi mt mng truyn thng. Cc nt trong mt mng phn tn mt mt x l thng tin ti v tr m n qun l, mt khc n cng tham gia vo vic x l cc yu cu v thng tin cn truy cp qua nhiu a im. V d, vic ln danh sch, tnh ton lng cho tt c cc nhn vin ca cng ty,... Cc yu cu ny i hi tt c cc my tnh cc chi nhnh ca cng ty u phi hot ng cung cp thng tin. Ta c s lu tr phn tn v phn mnh gi nh nh sau:

H niNhn vin H ni Nhn vin Hu D n H ni

H ueNhn vin H ni Nhn vin Hu D n H ni D n Hu

HC MNhn vin H ni Nhn vin Hu D n H ni

HNhn vin Cn th D n Cn th

Hnh 1.13: S lu tr phn tn

24

1.4.2. H qun tr c s d liu phn tn H c s d liu phn tn (Distributed DataBase System) c xy dng da trn hai cng ngh c bn l c s d liu v mng my tnh. H c s d liu phn tn c m t nh l tp hp nhiu c s d liu c lin quan logic n nhau v c phn b trn mng my tnh. Trong khi nim c m t v c s d liu phn tn trn c hai c trng c bn l lin quan logic v phn b trn mng. Trong c s d liu phn tn cc tp tin d liu c lu tr c lp trn cc nt ca mng my tnh v phi c lin quan n nhau v mt logic v cn hn th na cn i hi chng phi c truy xut n qua mt giao din chung, thng nht. Hin nay khi nim x l phn tn (Distributed procesing), tnh ton phn tn (Distributed computing) hoc cc thut ng c t phn tn hay c dng ch cc h thng ri rc nh cc h thng my tnh c a b x l (multiprocessor system) hay l cc x l trn mng my tnh. C s d liu phn tn l mt khi nim khng bao gm cc trng hp x l d liu trong cc h thng s dng b nh chung, k c b nh trong hay b nh th cp (a t), nht thit phi l mt h c s dng giao tip mng vi cc trm lm vic c lp. H Qun tr c s d liu phn tn (Distributed DBMS) l h thng phn mm cho php qun l cc h c s d liu phn tn v lm cho s phn tn tr nn trong sut i vi ngi s dng. Khi nim trong sut - transparent ch s tch bit cp cao ca h thng vi cc vn ci t cp thp ca h thng. Chng ta s tho lun v tnh trong sut ca mt h phn tn chng 2. Mt DDBMS cn c chc nng h tr vic to v bo tr c s d liu phn tn, chng c cc thnh phn tng t nh mt h qun tr c s d liu tp trung v cc thnh phn h tr trong vic chuyn ti d liu n cc trm v ngc li. Cc thnh phn i hi mt DDBMSs thng mi phi c c m t hnh di y: Qun tr d liu (Database management): DB Truyn thng d liu (Data Communication): DC T in d liu (Data Dictionary): DD dng m t thng tin v s phn tn ca d liu trn mng. C s d liu phn tn (Distributed Database): DDB Cc thnh phn trn c mi lin h nh hnh di y:25

Local database 1

DB

DC DDB DD Site 1 Site 2

Local database 2

DD DB DC DDB

Hnh 1.14: Cc thnh phn ca mt DDBMS thng mi

1.4.3. So snh CSDL phn tn v CSDL tp trung CSDL phn tn khng n gin l nhng s thc hin phn tn ca CSDL tp trung, bi v chng cho php thit k cc c trng khc vi CSDL tp trung truyn thng. Cc c im tiu biu cho CSDL truyn thng l iu khin tp trung, c lp d liu, gim d tha, bit lp v bo mt d liu. iu khin tp trung Kh nng iu khin tp trung trn ton ngun ti nguyn thng tin ca ton b x nghip hoc t chc, c xem l ng c mnh nht cho vic ra i CSDL. Chng c pht trin nh l s tin ho ca h thng thng tin, m trong mi ng dng c cc tp tin ring ca n.

26

Trong CSDL phn tn, nim v iu khin tp trung t c nhn mnh hn. iu ny ph thuc vo kin trc ca CSDL phn tn. Mt cch tng qut, CSDL phn tn c iu khin vi cu trc phn lp da vo mt h qun tr CSDL ton cc (c trch nhim trn ton b CSDL phn tn) v h qun tr CSDL a phng (c trch nhim vi CSDL a phng ring). iu ny cho thy rng h qun tr CSDL a phng c th c mt mc t tr cao. Cc CSDL phn tn c th rt khc nhau v mc t tr: t hon ton t tr, khng c bt c mt h qun tr CSDL tp trung no, n hu nh hon ton iu khin tp trung. c lp d liu c lp d liu cng l mt trong nhng ng lc cho vic m u s tip cn d liu phn tn. Thun li chnh ca c lp d liu l cc chng trnh khng b nh hng bi s thay i trong cu trc vt l ca d liu. Trong CSDL phn tn, c lp d liu cng quan trng ging nh trong CSDL truyn thng. Tuy nhin, mt kha cnh mi c thm vo trong nim ca c lp d liu l trong sut phn tn. Vi trong sut phn tn chng ta hiu rng cc chng trnh ng dng c th s dng CSDL nh l n khng c t chc phn tn. V th s chnh xc ca chng trnh khng b nh hng bi vic dch chuyn d liu t trm ny n trm khc. Tuy nhin, tc thc hin ca chng b nh hng. Gim d tha d liu Trong CSDL truyn thng, d liu d tha c gim n mc ti thiu bi hai l do: S khng tng thch gia nhiu bn sao ca cng mt tp d liu. Tit kim khng gian lu tr bng cch loi tr cc d tha. Vic gim d tha d liu c th t c bng cch chia s d liu, cho php nhiu ng dng truy cp cng cc bn tin v bn ghi. Trong CSDL phn tn vic c lng d tha phc tp hn v cng vi hai l do trn, cn c nhiu l do gim d tha d liu nh: Hot ng ca cc trnh ng dng c th b tng ln khi d liu c sao li tt c cc v tr, ni trnh ng dng cn n. Tnh thng trc ca h thng c th s tng ln, bi v khi c li xy ra mt trm no s khng dng vic thc hin cc ng dng ca trm khc nu d liu c sao chp li. Bit lp v bo mt27

Trong CSDL truyn thng, h qun tr CSDL tp trung c th bo m ch truy cp n d liu c u quyn. Trong CSDL phn tn, h qun tr d liu a phng thc cht phi ng u vi cc vn ging nh h qun tr CSDL trong CSDL truyn thng. Tuy nhin, hai kha cnh c bit sau y ca CSDL phn tn cn phi c xem xt: Trong CSDL phn tn vi mt mc t tr rt cao ca cc a phng, ngi ch d liu a phng cm gic c bo v tt hn v h c th t ch thc hin bo v thay v ph thuc vo ngi qun tr CSDL trung tm. Vn bo mt l bn cht trong h phn tn ni chung, v cc mng truyn thng din rng cho php nhiu ngi cp nht v khai thc d liu nn cn c bo v. 1.4.4. u v nhc im ca h phn tn i tng ch yu ca h phn tn l cung cp cc dch v truy cp d liu v trnh din d liu cho ngi s dng mi ni khc nhau. p ng c iu ny, h thng phn tn phi c thit k v phn cng ln phn mm thch hp vi v tr a l v yu cu ca ngi s dng. Chng ta c th thy nhng u v nhc im ca h thng phn tn nh sau. u im 1. p ng nhanh hu ht cc ng dng s dng d liu ti cc trm 2. Tng cng cc n th ng dng v CSDL m khng lm cn tr ngi s dng hin ti. 3. Kim sot d liu a phng theo hng hon thin s tch hp v qun tr d liu t xa. 4. Tng cng kh nng ca h thng lin quan n s d tha d liu. Nhc im 1. Phn mm t v phc tp 2. Phi x l mi thay i thng bo trong mi a im 3. Kh kim sot tnh ton vn d liu vi nhiu bn sao d liu c phn b khp mi ni. 4. p ng chm nhu cu ca cc trm trong trng hp cc phn mm ng dng khng c phn b ph hp vi vic s dng chung.

28

1.5. Cc loi truy xut CSDL phn tn Truy xut d liu t xa ca mt ng dng c th thc hin theo hai cch c bn: truy xut t xa thng qua cc tc v c bn v truy xut t xa thng qua chng trnh ph tr. 1.5.1. Truy xut t xa thng qua cc tc v c bn: ng dng pht ra mt yu cu truy xut CSDL mt v tr no . Yu cu ny s c h qun tr CSDL phn tn t ng gi n v tr cha d liu . Sau yu cu ny c thc hin v kt qu c gi tr v.

Trnh ng dng

Cc tc v c bn truy xut CSDL

DBMS1Trm 1 Trm 2

DBMS2Kt qu gi v

DB2

Hnh 1.14: Truy xut CSDL t xa thng qua cc tc v c bn

Theo cch ny, cc n v c bn c gi qua li gia cc v tr ca h thng gi l cc tc v c bn truy xut CSDL v kt qu thu c khi thc thi cc tc v c bn ny. 1.5.2. Truy xut t xa thng qua chng trnh ph tr Nu mt ng dng yu cu thc hin mt chng trnh ph tr t ti v tr t xa th mt chng trnh ph tr, c vit bi ngi lp trnh ng dng, s truy xut d liu t xa v tr li kt qu cho ng dng ang yu cu.Yu cu thc hin chng trnh ph tr

Trnh ng dng

DBMS1

Trm 1Kt qu ton cc

Chng trnh ph tr

DBMS2

Cc tc v truy xut CSDL v kt qu gi v

Trm 2 DB2

Hnh 1.15: Truy xut CSDL t xa thng qua chng trnh ph tr 29

Thng thng cc h qun tr CSDL cung cp c hai loi truy xut t xa, bi v mi cch trn u c nhng u v nhc im ring. Cch th nht yu cu phi cung cp nhiu mc trong sut phn tn. Cch th hai c th hiu qu hn nu c nhiu yu cu truy xut CSDL bi v chng trnh ph tr c th thc hin mi truy xut CSDL c yu cu v ch gi v kt qu. Nu tnh nng ca chng trnh ph tr trong cch th hai ln hn tnh nng ca cc tc v c bn truy xut CSDL trong cch th nht th cch th hai c th c hiu qu hn. Do cc tc v c bn thao tc cc mu tin cng mt lc l mt c tnh rt hu ch cho cc h qun tr CSDL cc b trong vic xy dng h phn tn. Tuy nhin hu ht h qun tr CSDL thng mi c sn trn th trng l h thng ch x l mt mu tin ti mt thi im (One record at a time System) v do chng trnh ph tr l cn thit. Kt lun: Trong cc t chc thng mi, kinh t x hi,... h phn tn l cn thit v ng mt vai tr khng nh trong vic iu hnh, qun l t chc, cng ty. C s d liu phn tn c th c ci t trn mt mng my tnh c phm vi rng ln xuyn quc gia hoc nh b trong mt thnh ph. Hin nay cc h qun tr c s d liu phn tn thng mi u tch hp cc ng dng phn tn nn rt tin cho ngi s dng.

30

Cu hi v bi tp cui chng 1 1. Cc loi mng my tnh. u v nhc im ca mi loi. 2. T chc h thng phn tn theo m hnh mng ngang hng 3. T chc v kin trc h thng phn tn theo m hnh mng client/Server. 4. Hy phn tch cc c trng c bn ca h c s d liu phn tn. Cc khi nim v X l phn tn, H phn tn, C s d liu phn tn ging nhau v khc nhau im no? 5. Trong m hnh client/Server, hy cho bit mc ch ca: Trnh din thng tin phn tn Trnh din t xa Qun l d liu t xa Phn tn chc nng 6. nh ngha c s d liu phn tn. H qun tr CSDL phn tn. Cc thnh phn yu cu trong mt DDBMSs thng mi. Cho v d v mt CSDL phn tn . 7. So snh csdl phn tn v csdl tp trung 8. u v nhc im ca h phn tn 9. Cc truy xut t xa trong h thng phn tn 10. Cho mt v d v mt h c s d liu phn tn trong thc t.

31

CHNG 2. THIT K C S D LIU PHN TNMt trong nhng yu t c bn thit k mt c s d liu phn tn l chn nhng v tr thch hp ci t d liu v cc chng trnh trn cc trm ca mng my tnh. i vi h qun tr c s d liu (DBMS) phn tn, vic phn tn cc ng dng i hi hai iu: phn tn h qun tr CSDL v phn tn cc chng trnh ng dng chy trn h qun tr . iu u tin khng phi l vn quan trng bi chng ta gi s rng mi v tr lu d liu u c mt bn sao ca phn mm h qun tr d liu th vn phn tn cc chng trnh ng dng trn cc trm khng l vn ln. Do , y chng ta khng cp n ni ct gi cc chng trnh ng dng u m chng ta ch tp trung vo vic phn tn d liu nh th no. ng thi chng ta gi s rng h thng mng c thit k xong, ngha l khng quan tm cc gi tin c chuyn ti theo giao thc no trn h thng mng my tnh. Mc ch ca chng ny nhm cung cp cho ngi lp trnh ng dng thy c cc mc trong sut khc nhau v mt c s d liu phn tn, c cung cp ca mt h qun tr c s d liu phn tn. ng thi tho lun v mt s phng php thit k mt c s d liu trong mi trng ny. 2.1. Kin trc c bn ca mt c s d liu phn tn S cu trc di y cha phi l cu trc tng minh cho tt c cc c s d liu phn tn, nhng n th hin cch t chc chung ca mt c s d liu phn tn trong thc t.S tng th S phn on S nh v S c lp vi cc v tr

nh x a phng 1 HQT CSDL ca v tr 1

S nh x phng 2HQT CSDL ca phng 2

(cc v tr khc) ..........

CSDL a phng ti v tr 1

CSDL a phng ti v tr 2

Hnh 2.1: Kin trc c bn ca CSDL d liu phn tn 32

2.1.1. S tng th (Globle Schema) S tng th s xc nh tt c cc d liu s c lu tr trong c s d liu phn tn cng nh cc d liu khng c phn tn cc v tr trong h thng. S tng th c nh ngha theo cch nh trong CSDL tp trung. Trong m hnh quan h, s tng th bao gm nh ngha ca tp cc quan h tng th (Globle relations) . R R1 R11 R21 R1(V tr 1 )

R2

R12 R2 R22(V tr 2 )

R3 R23 R4 R33 R43 R3(V tr 3 )

Q.h tng th

Cc on

Hnh nh vt l

Hnh 2.2: Cc on v hnh nh vt l ca mt quan h tng th

2.1.2. S phn on Mi quan h tng th c th chia thnh mt vi phn khng giao nhau gi l on (fragments). C nhiu cch khc nhau thc hin vic phn chia ny. S phn on m t cc nh x gia cc quan h tng th v cc on c nh ngha trong s phn on (fragmentation Schema), nh x ny l mt-nhiu, ngha l c th c nhiu on lin kt n mt quan h tng th, nhng mi on ch c th lin kt vi nhiu nht mt quan h tng th m thi. Cc on c

33

m t bng tn ca quan h tng th cng vi ch mc on. Chng hn, Ri c hiu l on th i ca quan h R. 2.1.3. S nh v Cc on l cc phn logic ca mt quan h tng th c nh v vt l trn mt hay nhiu v tr trn mng. S nh v xc nh on d liu no c nh v ti v tr no trn mng. Lu rng, kiu nh x c nh v trong s nh v s quyt nh c s d liu phn tn l d tha hay khng. Tt c cc on c lin kt vi cng mt quan h tng th R v c nh v ti cng mt v tr j cu thnh nh vt l quan h tng th R ti v tr j. Do ta c th nh x mt-mt gia mt nh vt l v mt cp (quan h tng th, trm). Cc nh vt l c th ch ra bng tn ca mt quan h tng th v mt ch mc trm. K hiu Ri ch on th i ca quan h tng th R K hiu Rj ch nh vt l ca quan h tng th R ti v tr j Tng t nh vy, bn sao ca on i thuc quan h R ti v tr j c kj hiu l R i

2.1.4. S nh x a phng Thc hin nh x cc nh vt l ln cc i tng c thc hin bi h qun tr c s d liu a phng (tt c cc on ca mt quan h tng th trn cng mt v tr to ra mt nh vt l). Kin trc c s d liu phn tn trn cho chng ta c mt cch nhn tng qut v cch t chc mt h phn tn. Ba yu t c suy ra t kiu kin trc ny l: a. Tch ri khi nim phn on d liu vi khi nim nh v d liu. S khc nhau ca hai khi nim ny cho php phn bit hai mc trong sut phn tn l: trong sut phn on (fragmentation transparency) v trong sut v tr (location transparency). Trong trong sut phn on l mc cao nht, n bao gm nhng cng vic m ngi lp trnh ng dng lm vic vi quan h tng th. Cn trong sut v tr li lin quan n cc cng vic ca ngi s dng v ngi lp trnh ng dng ti trn cc on d liu c nh v ti cc trm. V cc i tng ny khng cn phi bit cc on d liu c nh v ti v tr no trn mng. b. c lp vi cc DBMS a phng Tnh cht ny cn c gi l trong sut nh x a phng (local mapping transparency) cho php chng ta kho st cc vn v qun l c s d liu phn tn m khng cn phi hiu r m hnh d liu ca DBMS a phng ang s dng.34

2.2. Kt ni d liu trong h thng c s d liu phn tn 2.2.1. H thng c s d liu thun nht H thng c s d liu thun nht (Homogeneous Distributed Database) l h thng m trong tt c cc d liu cc b cc v tr u phi dng chung mt h qun tr CSDL. Trong h thng ny ch c mt loi c s d liu c s dng cc trm. V d, trong mt H thng CSDL phn tn thun nht ca Oracle th tt c cc Database u phi l Oracle Database. Hnh 2.3 minh ha h thng CSDL phn tn thun nht vi 3 CSDL l HQ, MFG v SALES. Mt ng dng c th truy cp hoc hiu chnh d liu t nhiu CSDL khc nhau trn mng.

Hnh 2.3: H thng phn tn vi CSDL thun nht

Kin trc phn tn thun nht c mt s u im sau: Do tt c cc c s d liu cc b u dng chung DBMS nn cng tc qun tr d dng hn. Ngi qun tr khng cn bit k nng qun tr trong tt c cc DBMS khc nhau nh DB2, SQL SERVER, Cng tc chuyn i d liu khng i hi cao v tt tt c cc c s d liu cc b dng chung cu trc d liu v cc rng buc. Nhim v tch hp d liu t cc ngun n gin v d qun l. Thi gian tr li cc truy vn nhanh. Tuy nhin, kho d liu phn tn thun nht thch hp nht i vi nhng h thng xy dng mi v c chin lc t trc, i vi cc h thng k tha d

35

liu t cc ngun c cng vic chuyn i v tch hp d liu mt rt nhiu thi gian v phc tp. 2.2.2. H thng c s d liu khng thun nht Trng hp h thng s dng cc d liu khng cng loi th ta c khi nim v h thng c s d liu khng thun nht (Nonhomogeneous Distributed Database). H thng c s d liu phn tn khng thun nht l h thng d liu m trong cc kho d liu cc b cc v tr trong mng c th khng cng chung mt h qun tr c s d liu. V d, trong mt H thng c s d liu phn tn khng ng nht ca Oracle th phi c t nht Database l Non- Oracle Database. i vi h thng CSDL phn tn khng thun nht li ch chnh l k tha c cc CSDL tn ti trc nhng kh khn ln nht l qun tr v chuyn i d liu khi phi truy cp t xa v mi DBMS c kin trc, cch thc truy cp d liu v bo mt ring. V vy trong h thng ny phi c nhng dch v kt ni chuyn i (Transparent Gateway) c vai tr nh b phin dch gia cc DBMS trong h thng phn tn.

Hnh 2.4: Mt cch t chc khc ca h thng phn tn gm 2 file d liu l HQ v

36

Trong hnh trn th host cha database HQ l server khi n truy xut cc b. Mt client c th kt ni trc tip hoc gin tip n mt Database Sever. Kt ni trc tip c ngha l client kt ni ln mt server v truy xut thng tin lu trc tip trn server . V d nh hnh trn nu chng ta c cu truy vn SELECT * FROM DEPT th Bng Dept c lu trc tip trn server m ng dng kt ni. Cn kt ni gin tip c ngha l client kt ni ln server ny nhng truy xut d liu t mt i tng nm trn server khc. Trong v d trn th ng dng kt ni vo server cha DB HQ nhng truy xut d liu vo bng EMP trn server Sales INSERT INTO EMP@SALSE Kin trc phn tn khng thun nht c mt s u im sau: K tha c cc ngun d liu t cc c s d liu a phng tn ti Thch hp cho cc h thng xy dng trn c s m rng h thng c v trn thc t cc n v thng bt u vi cc c s d liu nh cho cc phng ban, sau pht trin thnh kho d liu ln hn cho ton cng ty Tnh t tr c s d liu cao Tuy nhin, h thng phn tn khng thun nht gp kh khn trong vic tch hp, chuyn i d liu cng nh cng tc qun tr d liu v mi DBMS c cu trc d liu, rng buc, cch thc truy vn, bo mt d liu khc nhau. 2.2.3. Nhng kt ni c s d liu (Database Links) Kt ni c s d liu l mt con tr (Link pointer) xc nh ng lin lc mt chiu t mt DB server n mt DB server khc. Link pointer thc s c nh ngha nh l mt cng trong bng t in d liu. truy cp vo link ny th phi kt ni vo c s d liu a phng m link c cha trong t in d liu. Mi c s d liu trong h thng phn tn phi c mt tn duy nht (global DB name) trong network domain. V d di y cho thy User Scott truy xut vo bng EMP trn Remote DB vi Global name l HQ.ACME.COM. - DB links c th l private hoc public. Nu l private th ch c user to ra n mi c quyn truy xut. Public th tt c user u c quyn truy xut. - User truy xut n remote DB thng qua cc kiu link: Connected use link, Fixed user link, Current user link. . Connected user link: user phi c account trn remote DB ging vi account trn local DB. . Fixed user link: khi to link c quy nh sn username v password truy xut. . Current user link: user connect l 1 global user.37

Trong SQL s dng cu lnh CREATE DATABASE LINK to mt DB links. - Shared DB links: Link c share v c nhiu client cng s dng.

Hnh 2.5: H thng phn tn gm 3 file d liu l HQ, MFG v SALES.

2.3. Cc c im chnh ca h phn tn 2.3.1. Chia s ti nguyn Vic chia s ti nguyn trn h phn tn c thc hin qua mng truyn thng. chia s ti nguyn mt cch hiu qu th mi ti nguyn cn phi c qun l bi mt chng trnh c giao din truyn thng, cc ti nguyn c th c truy nhp, cp nht mt cch tin cy v nht qun. Qun l ti nguyn y l lp k hoch d phng, t tn cho cc lp ti nguyn, cho php ti nguyn c truy nhp t ni ny n ni khc, nh x tn ti nguyn vo a ch truyn thng. 2.3.2. Tnh m Tnh m ca h thng my tnh l tnh d dng m rng phn cng (thm cc thit b ngoi vi, b nh cc giao din truyn thng, cc dch v dng chung ti nguyn, ...) ca n.

38

Mt h phn tn c tnh m l h c to nn bi nhiu loi phn cng v phn mm ca nhiu nh cung cp khc nhau vi iu kin l cc thnh phn ny phi theo mt tiu chun chung. Tnh m ca h phn tn c xem xt theo mc b sung vo cc dch v dng chung ti nguyn m khng ph hng hay nhn i cc dch v ang tn ti. Tnh m c hon thin bng cch xc nh hay phn nh r cc giao din chnh ca mt h v lm cho n tng thch vi cc nh pht trin phn mm(hay cc giao din chnh l ph dng). Tnh m ca h phn tn da trn vic cung cp c ch truyn thng gia cc tin trnh v cng khai cc giao din dng truy nhp cc ti nguyn chung. 2.3.3. Kh nng song song H phn tn hot ng trn mt mng truyn thng c nhiu my tnh, mi my c th c mt hay nhiu CPU. Trong cng mt thi im nu c N tin trnh cng tn ti, ta ni chng thc hin ng thi. Vic thc hin tin trnh theo c ch phn chia thi gian(mt CPU) hay (nhiu CPU). Kh nng lm vic song song trong h phn tn c th hin qua hai tnh hung sau: Nhiu ngi s dng ng thi a ra cc lnh hay cc tng tc vi cc chng trnh ng dng. Nhiu tin trnh Server chy ng thi, mi tin trnh phi p ng yu cu t cc tin trnh Client (khch hng). 2.3.4. Kh nng m rng H phn tn c kh nng hot ng tt v hiu qu nhiu mc khc nhau. Mt h phn tn nh nht c th hot ng ch cn hai v tr lm vic v mt File Server. Cc h ln c hng nghn my tnh. Kh nng m rng ca mt h phn tn c c trng bi tnh khng thay i phn mm h thng v phn mm ng dng khi h c m rng. iu ny ch t mc no i vi cc h phn tn hin ti. Yu cu cho vic m rng khng ch l m rng phn cng, v mng m n tri trn cc kha cnh khi thit k h phn tn. Mt iu n gin l tn sut s dng trn mng. trnh tnh trng tc nghn xy ra khi ch c mt Server v p ng cc yu cu truy nhp cc file , ngi ta nhn bn file trn mt Server v h thng c thit k sao cho vic thm Server c d dng. Mt s gii php khc l s dng Cache v cc bng sao d liu. 2.3.5. Kh nng th li Vic thit k kh nng th li cc h thng my tnh da trn hai gii php sau:39

Dng kh nng thay th m bo s hot ng lin tc v hiu qu. Dng cc chng trnh hi phc d liu khi xy ra s c. xy dng mt h thng c th khc phc s c theo cch th nht th ngi ta ni hai my tnh vi nhau thc hin mt chng trnh, mt trong hai my chy ch Standby (khng ti hay ch). Gii php ny kh tn km v phi nhn i phn cng ca h thng. Mt gii php gim ph tn l cc server ring l c cung cp cc ng dng quan trng c th thay th nhau khi c s c xut hin. Khi khng c cc s c, cc Server hot ng mt cch bnh thng (ngha l vn phc v cc yu cu t Client). Khi c s c trn mt Server no , cc ng dng Client t chuyn hng sang cc Server cn li. Vi cch th hai th cc phn mm hi phc c thit k sao cho trng thi d liu hin thi (trng thi trc khi xy ra s c) c th c khi phc khi li c pht hin. Cc h phn tn cung cp kh nng sn sng cao i ph vi cc sai hng ca phn cng. Kh nng sn sng ca h thng c o bng t l ca thi gian m h thng sn sng lm vic so vi thi gian c s c. Khi mt thit b trn mng b sai, hng th ch cng vic lin quan n cc thnh phn sai, hng b nh hng. Ngi s dng c th chuyn n mt v tr khc nu my h ang s dng b hng v mt tin trnh Server c th khi ng li trn mt my khc. 2.3.6. m bo tin cy v nht qun H thng yu cu tin cy nh b mt ca d liu, cc chc nng khi phc h hng phi m bo. Ngoi ra cc yu cu ca h thng v tnh nht qun cng th hin ch khng c mu thun trong ni dung c s d liu. 2.3.7. Tnh trong sut (transparency). Tnh trong sut ca mt h phn tn c hiu nh l vic che khut i cc thnh phn ring bit ca h i vi ngi s dng v nhng ngi lp trnh ng dng. Tnh trong sut trong vic s dng: vic chuyn i mt phn hay ton b c s d liu do thay i v t chc hay qun l, khng nh hng ti cc thao tc ngi s dng. Tnh trong sut phn on (fragmentation transparency): khi d liu c phn on th vic truy cp vo c s d liu c thc hin bnh thng nh l cha b phn tn. V d: Xt quan h tng th NCC (Id, Tn, Tui) v cc phn on c tch ra t n: NCC1 (Id1, Tn, Tui), NCC2 (Id2, Tn, Tui), NCC3 (Id3, Tn, Tui).40

Gi s DBMS cung cp tnh trong sut v phn on, khi ta c th thy tnh trong sut ny c th hin nh sau: Khi mun tm mt ngi c Id=Id1 ta ch cn tm trn quan h tng th NCC m khng cn bit quan h NCC c phn tn hay khng. SELECT FROM WHEREDBMS

* NCC Id=Id1NC Tr

NC

Tr Tr

NC

Hnh 2.6: S trong sut phn on.

Tnh trong sut v v tr (location transparency): ngi s dng khng cn bit v v tr vt l ca d liu m c quyn truy cp n c s d liu ti bt c v tr no. Cc thao tc ly hoc cp nht mt d liu t xa c t ng thc hin bi h thng ti im a ra yu cu. Tnh trong sut v v tr rt hu ch, n cho php ngi s dng b qua cc bn sao d liu tn ti mi trm. Do c th di chuyn mt bn sao d liu t mt v tr ny n mt v tr khc v cho php to cc bn sao mi m khng nh hng n cc ng dng. V d: Vi quan h tng th R v cc phn on nh ni trn nhng gi s rng DBMS cung cp trong sut v v tr nhng khng cung cp trong sut v phn on. Xt cu truy vn: Tm ngi c Id=Id1 . SELECT FROM WHERE SELECT FROM WHERE * NCC1 Id=Id1 * NCC2 Id=Id1

IF NOT #FOUND THEN

41

u tin h thng s thc hin tm kim phn on NCC1 v nu DBMS tr v bin iu khin #FOUND th mt cu lnh truy vn tng t c thc hin trn phn on NCC2 y quan h NCC2 c sao lm hai bn trn hai v tr2 v v tr3, ta ch cn tm thng tin trn quan h NCC2 m khng cn quan tm n v tr no.

DBMS

NCC1

V tr 1

NCC2

V tr 2 V tr 3

NCC2Hnh 2.7: Trong sut v v tr

Tnh trong sut nh x a phng: trong sut nh x a phng l mt c tnh quan trng trong mt h thng cc DBMS khng ng nht. ng dng tham chiu n cc i tng c cc tn c lp t cc h thng cc b a phng. ng dng c ci t trn mt h thng khng ng nht nhng c s dng nh mt h thng ng nht.DBMS

NC t NC tHnh 2.8: Trong sut nh x a phng

V

V

V d: Vi v d nh trn nhng gi s rng DBMS cung cp tnh trong sut nh x a phng. SELECT FROM WHERE SELECT * NCC1 AS Vtr 1 Id=Id1 *42

IF NOT #FOUND THEN

FROM WHERE

NCC2 Id=Id1

AS Vtr 1

Mc khng trong sut: ngi lp trnh ng dng phi vit chng trnh thc hin trn cc h qun tr CSDL a phng, cc h ny c ci t ti v tr ng dng cn c d liu (trn cc v tr khc nhau v cc h iu hnh cng c th khc nhau: nh cc phin bn khc nhau trn cng mt h thng, cc h thng khc nhau trn cng mt kiu, cc chng trnh ny thc hin yu cu cc hm v ci t cc chng trnh ph tr ti cc v tr c yu cu). Tm li, khi nim trong sut - transparency ch s tch bit cp cao ca h thng vi cc vn ci t cp thp ca h thng. N gip cho ngi lp trnh ng dng v ngi s dng h thng c cm gic ang lm vic trn mt h thng khng phn tn. 2.4. Ni dung thit k cc h thng phn tn 2.4.1. Qu trnh thit k Thit k h thng phn tn c nhiu im tung ng vi vic thit k h thng tp trung. iu khc nhau c bn y l h thng phn tch c phn b trn mt s a im khc nhau. Nhiu vn thit k cn c xem xt lin quan n tnh kh thi, s sng cn v tnh sn sng ca h thng khi n c trin khai nhiu a im. Cc h thng phn tn s c nhiu my trm, my ch, mng, nhiu a im, nhiu d liu,... v nhiu a im c nguy c xy ra s c. V th cn c nhiu chin lc khc nhau thit k v trin khai chng. Ni cch khc, thit k h thng phn tn th cc vn cn quan tm l: Xc nh kin trc m hnh phn tn tng th v vic nh v cc a phng cn phn tn, loi hnh phn tn s dng cho mi a phng. Tin hnh cn i cc yu t c phn tn bao gm cc phn t d liu v cc hot ng x l trn mi trm. Thit k c s d liu phn tn. Thit k cc h thng chng trnh ng dng. 2.4.2. Cc sn phm sau khi thit k 1. M t cc trm . Thng tin a l . nh v thit b vt l . Thng tin h tng . c trng v con ngi (trnh , k nng, k thut,...)

43

2. M t d liu c s dng cho mi trm . Cc phn t d liu s dng. . Cc phn t d liu cn phi to ra. . Cc phn t d liu cp nht. . Cc phn t d liu xa. 3. M t qu trnh nghip v cho mi trm . Danh sch cc x l. . M t cc x l. 4. Cc tha thun v phng n kin trc h thng cho mi trm, cho nhu cu v d liu v x l cho trm . C cn hay khng v cc tr gip khng phi k thut. . C cn hay khng v h thng a phng, v ni mng. . C cn hay khng v cc cu hnh phn tn khc. 2.5. Cc chin lc phn tn d liu Vic nh v v phn tn d liu cc nt trong mt mng my tnh s quyt nh tnh hiu qu v ng n ca h thng phn tn. Hin nay ngi ta s dng 4 chin lc phn tn d liu c bn l: 2.5.1. Tp trung d liu Tt c cc d liu c tp trung mt ch. Cch ny n gin nhng c 3 nhc im D liu khng sn sng cho ngi s dng truy nhp t xa. Chi ph truyn thng ln. Ton b h thng ngng khi c s d liu b s c. 2.5.2. Chia nh d liu C s d liu c chia thnh cc phn nh lin kt nhau (khng trng lp). Mi phn d liu c a n cc v tr mt cch thch hp s dng. 2.5.3. Sao lp d liu C s d liu c nhn thnh nhiu bn tng phn hoc y v c t nhiu v tr trn mng. Nu bn sao ca CSDL c lu gi ti mi v tr ca h thng ta c trng hp sao lp y . Cch ny thng lm cc i vic truy nhp ti d liu mi a phng v ny sinh nhiu vn khi cp nht nh: khi c s thay i d liu mt v tr th cn x l li ng b ha d liu cho cc v tr khc. Hin nay c nhiu k thut mi cho php to bn sao khng y ph hp vi yu cu d liu mi v tr v mt bn y c44

qun l server. Sau mt khong thi gian nht nh cc bn sao c lm ng b vi bn chnh bng mt ng dng no . V d, ng dng Briefcase ca Windows l mt chng trnh ng b ha trn c s d liu Access. 2.5.4. Phng thc lai C s d liu c phn thnh nhiu phn quan trng v khng quan trng. Phn t quan trng c lu gi mt ni v phn quan trng c lu tr nhiu ni khc. 2.6. Thit k c s d liu phn tn 2.6.1. S thit k tng th c s d liu phn tn Hin nay cha c mt k thut c th no ni mt cch chi tit cch thit k mt CSDL phn tn. Tuy nhin, mt cch tng qut chng ta c th thit k CSDL phn tn theo cc bc sau:Thit k lc quan h tng th

Thit k phn on

Thit k nh v cc on (To cc nh vt l)

Thit k CSDL vt lHnh2.9: S thit k tng th

Thit k lc quan h tng th: thit k cc quan h tng th, v m t ton b d liu s c dng trong h thng. Thit k phn on: thc hin chia nh d liu thnh cc phn. Thit k nh v cc on: l qu trnh thc hin nh x cc on vo cc v tr khc nhau, to cc nh vt l ti cc v tr. Cc on d liu c a vo cc v tr lu tr thch hp vi yu cu hot ng thc t ca h thng.45

Thit k c s d liu vt l: thit k d liu vt l cho cc quan h ti cc v tr. 2.6.2. Cc phng php thit k CSDL phn tn Hai chin lc chnh c xc nh trong vic thit k cc CSDL phn tn l phng php tip cn t trn xung v phng php tip cn t di ln. 2.6.2.1. Phng php thit k t trn xung Bt u bng vic phn tch yu cu nhm nh ngha mi trng h thng v thu thp cc nhu cu v d liu v nhu cu x l ca tt c mi ngi c s dng CSDL. Nghin cu cc yu cu cng xc nh xem h thng cui cng s nh th no so vi cc mc tiu ca mt DBMS phn tn. Cn nhc li rng nhng mc tiu ny c nh ngha theo cc vn v hiu qu hot ng, tin cy v tnh c sn, tnh kinh t v kh nng m rng. H s ghi chp cc yu cu l nguyn liu cho hai hot ng song song: thit k khung nhn v thit k quan nim. Thit k khung nhn c nhim v nh ngha cc giao din cho ngi s dng cui (end-user). Ngc li thit k khi nim l qu trnh xem xt tng th h thng nhm xc nh cc loi thc th v mi lin h gia cc hot ng c lin quan vi nhau: phn tch thc th v phn tch chc nng. Phn tch thc th c lin quan n vic xc nh cc thc th, cc thuc tnh v mi lin h gia chng. Phn tch chc nng cp n vic xc nh cc chc nng c bn c lin quan n h thng cn c m hnh ho. Kt qu ca hai qu trnh ny cn c i chiu qua li, gip chng ta bit c chc nng no s hot tc trn cc thc th no. C mt s lin h gia thit k quan nim v thit k khung nhn theo mt ngha no , thit k khi nim c th c din gii nh l s tch hp cc khung nhn ca ngi s dng. Tch hp khung nhn c s dng nhm bo m rng cc yu cu v thc th v cc mi lin h gia cc khung nhn u phi c bao qut trong lc khi nim. Trong cc hot ng thit k khi nim v thit k khung nhn, ngi s dng cn phi c t cc thc th d liu v phi xc nh cc ng dng s trn CSDL cng nh cc thng tin thng k v nhng ng dng ny. Thng tin thng k bao gm c t v tn s ng dng, khi lng cc thng tin khc nhau, v.v... Thc s cho n lc ny, qu trnh thit k hon ton ging vi qu trnh thit k trong mt CSDL tp trung. Thit k phn tn: lc khi nim ton cc GCS (Global Concept Schema) v thng tin v kiu mu truy xut thu c trong thit k khung nhn s l u vo (input) cho bc thit k phn tn. Mc tiu ca giai on ny l46

thit k cc lc khi nim cc b LCS bng cch phn tn cc thc th cho cc v tr ca h thng phn tn. Thay v phn tn cc quan h, chng ta thng chia chng thnh nhiu quan h nh hn gi l cc mnh (fragment) v chnh cc mnh s c phn tn n cc v tr thch hp. V vy hot ng thit k phn tn bao gm c hai bc: phn mnh v cp pht cc mnh cho cc v tr. Bc cui cng l thit k vt l, l bc nh x lc khi nim cc b sang cc thit b lu tr c sn ti cc v tr tng ng. Nguyn liu cho qu trnh ny l cc lc khi nim cc b v thng tin v kiu mu truy xut cc mnh. Chng ta bit rng hot ng pht trin v thit k lun l mt qu trnh lin tc, i hi phi theo di, hiu chnh thng xuyn. V th chng ta cng a vn quan st v theo di nh mt hot ng chnh trong qu trnh ny. Cn ch rng chng ta khng ch theo di vn ci t CSDL m cn phi xem xt, theo di tnh thch hp ca cc khung nhn ca ngi s dng. Kt qu ny c tc dng phn hi, to tin cho vic ti thit k v sau. Tm li, qu trnh thit k mt h thng phn tn theo phng php trn xung s bao gm cc bc nh s di y Cc nh ngha: nh ngha mi trng h thng, d liu v cc tin trnh cho tt c nhng kh nng v d liu ca ngi s dng. Thit k view: xy dng khung nhn d liu cho ngi s dng. Thit k mc quan nim: l mt tin trnh kim tra v xc nh r hai nhm quan h: phn tch thc th v phn tch chc nng. Phn tch thc th: xc nh cc thc th, cc thuc tnh v cc mi quan h gia chng. Phn tch chc nng: xc nh cc chc nng ca h thng v a ra cc chc nng c s. Thit k phn tn: bao gm hai phn: thit k phn on v thit k nh v Lc quan nim a phng: to ra cc lc mc quan nim ti cc a phng Thit k vt l: thc hin nh x lc mc quan nim ti cc a phng ra cc n v lu tr vt l. Quan st v kim tra: kim tra cc giai on ca qu trnh thit k c s d liu.Nu mt giai on no b sai s tin hnh thit k li. C th m t qu trnh phn tch thit k mt h thng phn tn theo s sau:47

Cc yu cu v phn

Cc yu cu h

Ci vo ca ngi dng

Thit k mc quan nim

Tp hp cc khung nhn

Thit k

Lc tng th mc quan nim

Truy nhp thng tin

Cc nh ngha s ngoi

Thit k phn tnCi vo ca ngi dng

Lc mc quan nim a phng

Thit k vt l

Lc vt lPhn hi Phn hi

Quan st v kim Hnh 2.10: S thit k CSDL phn tn theo m hnh trn xung

Phng php thit k trn xung thc s c hiu qu khi xy dng mt h thng mi. Tuy nhin trong thc t, thng c mt s c s d liu tn ti trc c t chc trong mi trng tp trung v CSDL phn tn c pht trin bng cch lin kt chng li thnh mt CSDL mi thng nht th phng php tip cn trn xung l kh thc hin. Khi chng ta nn s dng phng php thit k di ln s c hiu qu hn.

48

2.6.2.2. Phng php thit k t di ln Phng php thit k di ln l ngc li vi phng php thit k trn xung. Khi im ca thit k t di ln l vic thit k cc s mc quan nim sao cho chng c lp vi nhau. Sau chng c tch hp li trong mt s khi nim tng th. Phng php thit k di ln l ph hp khi h thng CSDL c thit k t nhng thnh phn hn hp. Tm li, Phng php thit k di ln ca mt CSDL phn tn yu cu thc hin cc iu sau: Chn mt m hnh d liu chung m t lc tng th. Chuyn mi lc a phng theo m hnh d liu chung chn. Tch hp cc lc a phng vo lc tng th. 2.7. Phn mnh d liu Trong phn ny chng ta ch ra l do ti sao cc quan h trong mt lc CSDL thng c phn r thnh cc mnh nh hn. Cc cu hi di y s gi m cho ton b vn . Ti sao cn phi phn mnh? Lm th no thc hin phn mnh? Phn mnh nn thc hin n mc no? C cch g kim tra tnh ng n ca vic phn mnh ny hay khng? Chng ta s cp pht cc mnh nh th no? Nhng thng tin no s cn thit cho vic phn mnh v cp pht? 2.7.1. Cc l do phn mnh i vi phn mnh, iu quan trng l c c mt n v phn tn thch hp. Mt quan h khng phi l mt n v p ng c yu cu v nhiu l do. Trc tin, khung nhn ca cc ng dng thng ch l mt tp con ca quan h. V th n v truy xut khng phi l ton b quan h nhng ch l cc tp con ca quan h. Kt qu l xem tp con ca quan h l n v phn tn s l iu thch hp duy nht. Th hai, nu cc ng dng c cc khung nhn c nh ngha trn mt quan h cho trc nm ti nhng v tr khc th c hai cch chn la vi n v phn tn: hoc l ton b quan h hoc quan h khng c nhn bn m c lu mt v tr c chy ng dng. Chn la th nht gy ra mt s lng ln cc truy xut khng cn thit n d liu xa. Ngc li, chn la sau thc hin

49

nhn bn khng cn thit, gy ra nhiu vn khi cp nht v c th gy lng ph khng gian lu tr. Cui cng, vic phn r mt quan h thnh nhiu mnh, mi mnh c x l nh mt n v, s cho php thc hin nhiu giao dch ng thi. Ngoi ra vic phn mnh cc quan h s cho php thc hin song song mt cu vn tin bng cch chia n ra thnh mt tp cc cu vn tin con hot tc trn cc mnh. V th vic phn mnh s lm tng mc hot ng ng thi (song hnh) v nh th lm tng lu lng hot ng ca h thng. Kiu hot ng ng thi ny m chng ta gi l ng thi ni truy vn (interquery concurrency). Bn cnh vic phn mnh cng tn ti mt s khim khuyt. Nu ng dng c nhng yu cu ngn cn vic phn r thnh cc mnh c s dng c quyn, th nhng ng dng c cc khung nhn c nh ngha trn nhiu mnh s b gim hiu sut hot ng. Chng hn n c th cn phi truy xut d liu t hai mnh ri ni hoc hp chng li vi chi ph rt cao. Trnh c iu ny l mt vn c bn ca k thut phn mnh. Vn th hai lin quan n vic kim sot d liu ng ngha (semantic data control), c bit l vn kim tra tnh ton vn. Do kt qu ca phn mnh, cc thuc tnh tham gia vo mt ph thuc c th b phn r vo cc mnh khc nhau v c cp pht cho nhng v tr khc nhau. Trong trng hp ny, mt nhim v n gin nh kim tra cc ph thuc cng phi thc hin truy tm d liu nhiu v tr. 2.7.2. Cc kiu phn mnh Th hin ca cc quan h l cc bng, v th vn t ra l tm cch chia mt bng thnh nhiu bng nh hn, iu ny c gi l phn mnh quan h. C hai cch phn mnh mt quan h: phn mnh ngang (horizontal fragmentation) v phn mnh dc (vertical fragmentation). Phn mnh ngang c thc hin bng mt php ton chn trn cc quan h ca mt lc CSDL. Cn phn mnh dc c thc hin bng mt php chn cc thuc tnh ca mt quan h. Tuy nhin phn mnh ngang c s dng ph bin hn. Ngoi ra trong thc t, ngi ta cn s dng phn mnh hn hp (hibrid fragmentation) tc l kt hp c phn mnh ngang v phn mnh dc. Tt nhin qu trnh phn mnh phi c gn lin vi vn cp pht d liu v bi ton c th nh th no. V vy, vn chng ta bn lun di y ch mang tnh cht minh ha cho vic phn mnh. 2.7.2.1. Phn mnh ngang Phn mnh ngang mt quan h tng th n-b R l tch R thnh cc quan h con n-b R1, R2, ..., Rk sao cho quan h R c th c khi phc li t cc quan h con ny bng php hp: R = R1 R2 ..., Rk50

C hai loi phn mnh ngang: Phn mnh ngang nguyn thy (primary horizontal fragmentation): phn mnh ngang nguyn thy ca mt quan h c thc hin da trn cc v t c nh ngha trn quan h . Phn mnh ngang dn xut (derived horizontal fragmentation): phn mnh ngang dn xut ca mt quan h c thc hin da trn cc v t c nh ngha trn quan h khc. Nh vy trong phn mnh ngang tp cc v t ng mt vai tr quan trng, n cho php xc nh cc phn mnh nh th no. 2.7.2.2. Phn mnh dc Phn mnh dc mt quan h tng th n-b R l tch R thnh cc quan h con R1, R2, ..., Rk sao cho quan h R c th c khi phc li t cc quan h con ..., Rk. Thc cht ca phn mnh dc l ny bng php ni: R = R1 R2 thc hin php chiu trn quan h tng th. Phn mnh hn hp Cc mnh d liu c hnh thnh phn mnh ngang v phn mnh dc c gi l phn mnh hn hp 2.7.3. Cc yu cu v vic phn mnh Phn mnh CSDL n mt mc no n s quyt nh thi gian thc hin cc truy vn. Mt mc nh th ch c nh ngha vi cc ng dng c th trn CSDL. Vic phn mnh mt quan h tng th cng phi tun theo mt s cc quy tc no khi ti thit li quan h c vn bo m ng ngha ca n. Mt phng php thit k cc phn mnh ng n phi tha mn ba rng buc sau: a. Tnh y (completeness): Nu mt quan h R c phn r thnh cc mnh R1, R2, ..., Rk th mi mc d liu c trong R phi c trong mt hoc nhiu mnh Ri b. Tnh ti thit c (reconstruction): Nu mt quan h R c phn r thnh cc mnh R1, R2, ..., Rk th phi tn ti mt ton t sao cho: R = (Ri), i. Ton t thay i ty theo tng loi phn mnh, iu quan trng l lm sao xc nh c n. Trong thc t khi cc mnh l c phn mnh ngang th l php hp v khi phn mnh dc th l php ni. c. Tnh tch bit (disjointness): Nu mt quan h R c phn mnh ngang thnh cc quan h R1, R2, ..., Rk v mc d liu ti nm trong mnh Ri th n s khng nm trong mt mnh Rk, ki. Tiu chun ny bo m cc mnh ngang phi c tch ri nhau. Nu quan h c phn mnh dc th cc thuc tnh51

chung phi c lp li trong mi mnh. Do , trong trng hp phn mnh dc tnh tch bit ch c nh ngha trn cc trng khng phi l thuc tnh chung ca quan h. V d: Xt c s d liu ca mt cng ty phn mm c t chc nh sau: NHANVIEN (MANV, TENNV, CHUCVU): l quan h cha d liu v nhn vin ca cng ty. Bao gm, tn nhn vin (TENNV), m s ca nhn vin (MANV), chc v ca nhn vin trong cng ty (CHUCVU) v ngy sinh ca nhn vin (NGAYSINH). TLUONG (CHUCVU, LUONG): l quan h cha d liu qun l cc bc lng ca nhn vin trong cng ty. Gm hai trng CHUCVU v LUONG (lng tng ng vi chc v). DUAN (MADA, TENDA, NGANSACH): l quan h cha d liu qun l cc d n m cng ty ang pht trin. Gm ba trng: MADA (m s d n), TENDA (tn d n) v NGANSACH (ngn sch ca d n). HOSO (MANV, MADA, NHIEMVU, THOIGIAN): gm bn trng: MANV (m s nhn vin), MADA (m s d n m nhn vin lm vic), NHIEMVU (nhim v ca nhn vin trong d n) v THOIGIAN (thi gian c phn cng trong d n). NHANVIEN MA NV A1 A2 A3 A4 A5 A6 A7 A8 TEN NV Nam Trung ng Bc Ty Hng Dng Chin Phn tch HT Lp trnh vin Phn tch HT Phn tch HT Lp trnh vin K s in Phn tch HT Thit k DL CHUCVU MA NV A1 A2 A2 A3 A3 A4 A5 A6 A7 A8 D1 D1 D2 D3 D4 D2 D2 D4 D3 D3 HOSO MADA NHIEM VU Qun l Phn tch Phn tch K thut Lp trnh Qun l Qun l K thut Qun l Lp trnh THOI GIAN 12 34 6 12 10 6 20 36 48 15

52

DUAN MADA D1 D2 D3 D4 TENDA CSDL CI T BO TR PHT TRIN NGANSACH 20000 12000 28000 25000

TLUONG CHUCVU K s in Phn tch HT Lp trnh vin Thit k DL LUONG 1000 2500 3000 4000

Hnh 2.3: C s d liu ca mt cng ty phn mm my tnh V d v phn mnh ngang: xt cc php ton i s quan h sau: DUAN 1 = NGANSACH 20000 (DUAN) DUAN 2 = NGANSACH > 20000 (DUAN) DUAN 1MADA TENDA NGANSACH

DUAN 2MADA TENDA NGANSACH

D1 D2

CSDL CI T

20000 12000

D3 D4

BO TR PHT TRIN

28000 25000

D thy, cc mnh tha mn tnh ti thit c v tnh y : DUAN 1 DUAN ; DUAN 2 DUAN ; DUAN = DUAN 1 DUAN 2 V d v phn mnh dc: xt cc php ton i s quan h sau: DUAN 3 = $1,$3 DUAN ; DUAN 4 = $1,$2 DUAN DUAN 3 MADA D1 D2 D3 D4 NGANSACH 20000 12000 28000 25000 MADA D1 D2 D3 D4 DUAN 4 TENDA CSDL CI T BO TR PHT TRIN

53

D thy, cc mnh tha mn tnh ti thit c v tnh y DUAN 3 DUAN ; DUAN 4 DUAN ; DUAN = DUAN 3 DUAN 4

V d v phn mnh hn hp: xt cc php ton i s quan h sau: DUAN 5 = NGANSACH 20000 ($2,$3 DUAN ) DUAN 6 = NGANSACH > 20000 ($2,$3 DUAN ) DUAN 5 TENDA CSDL CI T NGANSACH 20000 12000 TENDA CSDL CI T DUAN 6 NGANSACH 28000 25000

2.8. Cp pht ti nguyn trong h phn tn Cp pht ti nguyn trong mt h thng phn tn l mt bi ton c nhiu ngi quan tm v nghin cu rng ri. Tuy nhin phn ln cc nghin cu ny khng tp trung vo vic thit k d liu m s dng cho cch thc t cc tp tin vo cc v tr ca h phn tn. 2.8.1. Bi ton cp pht ti nguyn Cp pht ti nguyn khng phi l mt bi ton ch ca h c s d liu phn tn, mc d i vi c s d liu phn tn n c nhng c trng ring. Bi ton ny c bit n trong cc l thuyt mng vi cch t vn v cp pht cc tp tin. Gi s c mt tp cc mnh F={F1, F2, ..., Fk} v mt mng my tnh bao gm cc v tr S={S1, S2, ..., Sm} trn c mt tp cc ng dng Q={Q1, Q2, ..., Qq} ang thc thi. Bi ton cp pht (Allocation Problem) l tm mt phn phi ti u cc mnh F cho cc v tr S. Mt phn phi c gi l ti u nu n tha mn hai yu t sau: a. Chi ph nh nht: hm chi ph bao gm chi ph lu mi mnh d liu Fi ti v tr Sj, chi ph vn tin Fi ti v tr Sj, chi ph cp nht Fi ti tt c cc v tr c cha n, v chi ph truyn d liu. V th bi ton cp pht s tm mt lc cp pht vi hm chi ph l cc tiu. b. Hiu qu: chin lc cp pht c thit k nhm cc tiu ha thi gian thc hin v tng ti a lu lng h thng ti mi v tr. Ngi ta chng minh c rng bi ton cp pht tng qut, k hiu DAP (Database Allocation Problem), l mt bi ton NP-y . V th hu ht cc54

nghin cu c dnh cho vic tm ra c cc thut gii heuristic c c li gii ti u cho loi bi ton ny. Hin nay cha c mt m hnh heuristic tng qut no nhn mt tp cc mnh v sinh ra mt chin lc cp pht gn ti u ng vi cc rng buc cho trc m ch mi a ra mt s gi thit n gin ha v d p dng cho mt s cch t vn n gin. 2.8.2. Thng tin cp pht giai on cp pht, chng ta cn cc thng tin nh lng v c s d liu, v cc ng dng chy trn , v cu trc mng, v kh nng x l v gii hn lu tr ca mi v tr trn mng. a. Thng tin v c s d liu b. Thng tin v ng dng c. Thng tin v v tr d. Thng tin v mng Tm lai, thit k c s d liu phn tn l mt trong nhng cng vic quan trng trong qu trnh phn tch v thit k mt h thng thng tin trong mi trng phn tn. Vic thit k c s d liu phn tn c gi l hiu qu nu h thng c th p ng mi yu cu ca ngi s dng trn mi v tr phn tn. Do , lm tt cng vic ny yu cu phn tch vin phi hiu r mc ch v phn tch hin trng h thng y .

55

Cu hi v bi tp cui chng 2 1. M t kin trc c bn ca mt c s d liu phn tn. Cho mt v d trong thc t minh ho. 2. Cc kiu kt ni d liu trong h thng c s d liu phn tn. Dng Oracle hoc DB2 minh ho. 3. Cc loi trong sut c th c trong mt h c s d liu phn tn. Mi trng hp cho mt v d. 4. Cc chin lc phn tn d liu trong mt h thng phn tn. 5. Cc sn phm yu cu sau khi thit k h phn tn. 6. Cc phng php thit k CSDL phn tn. 7. Phn tch ngha ca cc quy tc phn mnh trong thit k h c s d liu phn tn. 8. Cho cc v d nu bt tng c bn ca cc phng php phn mnh ngang. 9. Bi ton cp pht 10. tng c bn ca phng php phn mnh dc l g? Cho v d minh ha. 11. Hy nu v gii thch ngha ca cc bi ton phn mnh v phn tn ti nguyn. 12. Ni dung ca phng php thit k t trn xung

56

CHNG 3. X L TRUY VN TRONG CSDL PHN TN

3.1. Gii thiu v x l truy vn Trong DBMS phn tn, vic thc hin cc truy vn trn cc ngn ng bc cao thng rt tn km thi gian. Do trc khi thc hin truy vn, cc DBMS lin quan s thc hin bin i cu truy vn phc tp v cc cu truy vn n gin gim thiu thi gian x l, gim vng nh trung gian v gim chi ph truyn thng gia cc trm. Chng ny s trnh by cc vn lin quan n vic x l cu truy vn ca mt h qun tr CSDL phn tn, c th phn tch nhng vn lin quan trong bn giai on x l truy vn: phn r truy vn, nh v d liu, ti u ho tng th, ti u ho cc b. Chc nng chnh ca mt b x l truy vn l bin i mt truy vn ca ngi s dng mc cao thnh mt truy vn tng ng mc thp hn (mt s bin thi ca i s quan h). Php bin i ny phi t c c v tnh ng n v tnh hiu qu. Ngha l cc truy vn mc thp phi tng ng vi truy vn ban u v cho cng mt kt qu. Cc cch bin i ny s dn n vic s dng ti nguyn my tnh khc nhau, nn vn t ra l la chn phng n no s dng ti nguyn t nht. C hai phng php c bn c s dng trong cc b x l truy vn: phng php bin i i s v phng php c lng chi ph. Phng php bin i i s lm n gin ha cu truy vn nh cc php bin i i s tng ng nhm gim thiu thi gian thc hin cc php ton, phng php ny khng quan tm n kch thc v cu trc d liu. Trong khi phng php c lng chi ph phi xc nh kch thc d liu, thi gian thc hin mi php ton trong mi cu truy vn. Trong mi trng tp trung, cc chin lc x l truy vn c th biu din trong s m rng ca i s quan h. Vai tr chnh ca b x l truy vn tp trung l: vi mt cu truy vn cho trc, chn mt truy vn i s quan h tt nht trong s tt c cc truy vn tng ng. Trong mi trng phn tn, i s quan h cha biu din cc chin lc thc hin. N phi c b sung thm cc php ton truyn d liu gia cc trm. Ngoi vic chn th t cc php ton i s quan h, b x l truy vn phn tn cn phi chn cc trm tt nht x l d liu v cch thc bin i d liu. iu ny lm tng khng gian gii quyt chn cc chin lc thc hin phn tn, nn x l phn tn c nhiu vn phc tp hn.

57

3.2. Ti u ha cu truy vn trong CSDL tp trung Ti u ho truy vn trong c s d liu tp trung l nn tng hiu cc nguyn tc lm vic ca b ti u trong mi trng phn tn, do vic nghin cu c ch ca b ti u trong c s d liu tp trung l iu cn thit. Chng ta hy xt mt CSDL dng qun l cc cng vic ca mt cng ty phn mm, bao gm cc quan h di y. E=NHANVIEN (MANV, TENNV, CHUCVU): l quan h cha d liu v nhn vin ca cng ty. Bao gm, tn nhn vin (TENNV), m s ca nhn vin (MANV), chc v ca nhn vin trong cng ty (CHUCVU) v ngy sinh ca nhn vin (NGAYSINH). S=TLUONG (CHUCVU, LUONG): l quan h cha d liu qun l cc bc lng ca nhn vin trong cng ty. Gm hai trng CHUCVU v LUONG (lng tng ng vi chc v). J=DUAN (MADA, TENDA, NGANSACH): l quan h cha d liu qun l cc d n m cng ty ang pht trin. Gm ba trng: MADA (m s d n), TENDA (tn d n) v NGANSACH (ngn sch ca d n). G=HOSO (MANV, MADA, NHIEMVU, THOIGIAN): gm bn trng: MANV (m s nhn vin), MADA (m s d n m nhn vin lm vic), NHIEMVU (nhim v ca nhn vin trong d n) v THOIGIAN (thi gian c phn cng trong d n). NHANVIENMANV TENNV CHUCVU MANV MADA

HOSONHIEMVU THOIGIAN

A1 A2 A3 A4 A5 A6 A7 A8

Nam Trung ng Bc Ty Hng Dng Chin

Phn tch HT Lp trnh vin Phn tch HT Phn tch HT Lp trnh vin K s in Phn tch HT Thit k DL

A1 A2 A2 A3 A3 A4 A5 A6 A7 A8

D1 D1 D2 D3 D4 D2 D2 D4 D3 D3

Qun l Phn tch Phn tch K thut Lp trnh Qun l Qun l K thut Qun l Lp trnh

12 34 6 12 10 6 20 36 48 15

58

DUANMADA TENDA NGANSACH

TLUONGCHUCVU LUONG

D1 D2 D3 D4

CSDL CI T BO TR PHT TRIN

20000 12000 28000 25000

K s in Phn tch HT Lp trnh vin Thit k DL

1000 2500 3000 4000

Chng ta s s dng CSDL ny minh ho cho cc v d trnh by trong phn tip theo. 3.2.1. S lc v ngn ng truy vn c cu trc-SQL SQL trc kia c gi l SEQUEL, l mt ngn ng phi th tc c IBM pht trin San Jose, mc ch s dng rng ri trong h thng c s d liu th nghim l System R. Ngy nay, n c s dng trong rt nhiu h thng CSDL thng mi, v trong mt s trng hp, ton b h thng c s d liu c thng mi ho vi tn SQL. Trong khun kh ca gio trnh ny chng ta s dng SQL m t cc gii thut ti u mt cu truy vn m khng trnh by chi tit v SQL. Cu lnh SELECT Dng thng dng nht ca cu truy vn SQL l cu lnh SELECT, c c php nh sau: SELECT FROM WHERE R1.A1, R2.A2,. . ., Ri.Ai R1, R2,. . . , Rk

Trong , R1, R2,. . . , Rk l danh sch cc quan h v R1.A1, R2.A2,. . ., Ri.Ai l danh cc thuc tnh c in ra; R.A l thuc tnh A ca quan h R. l mt biu thc logic c th bao gm php ton logic AND, OR, NOT v cc ton t so snh =, , . . . ngha ca cu lnh SELECT: R1.A1, ...,Ri.Ai ( (R1 ... Rk)) Ngha l, ly tch -ct cc quan h trong mnh FROM, chn nhng b tha mn biu thc logic trong mnh WHERE ( c th c thay th bng mt biu thc tng ng ), v cui cng chiu ln cc thuc tnh trong mnh SELECT.

59

Cu lnh SELECT ... INTO c tc dng to mt bng mi c cu trc v d liu c xc nh t kt qu ca truy vn. Bng mi c to ra s c s ct bng s ct c ch nh trong danh sch chn v s dng s l s dng kt qu ca truy vn V d: Cu lnh di y truy vn d liu t bng SINHVIEN v to mt bng TUOISV bao gm cc trng HODEM, TEN v TUOI SELECT INTO FROM hodem,ten,YEAR(GETDATE())-YEAR(ngaysinh) AS tuoi tuoisv sinhvien

3.2.2. Cc chin lc ti u ha cu truy vn trong CSDL tp trung Trong phn ny chng ta phn tch hai trong s nhng k thut ti u thng dng nht trong cc h thng c s d liu tp trung. l, cc thut ton c s dng trong h thng CSDL quan h thng dng l INGRES v SYSTEM R. y l tin hiu c cc k thut ti u phn tn v ba l do: Th nht, cu truy vn phn tn phi c dch thnh cc cu truy vn cc b, v c x l theo phng php tp trung. Th hai, cc k thut ti u ho phn tn thng l cc m rng ca k thut tp trung. Cui cng, ti u ho tp trung thng n gin hn. 3.2.2.1. Thut ton INGRES tng thut ton: Thut ton t hp hai giai on phn r v ti u ho. u tin phn r cu truy vn dng php ton quan h thnh cc phn nh hn. Cu truy vn trc tin c phn r thnh mt chui cc truy vn c mt quan h chung duy nht (chnh xc l mt bin b duy nht). Sau mi cu truy vn n quan h c x l bi mt th x l truy vn mt bin (one-variable query processorOVQP), OVQP ti u ho vic truy xut n mt quan h bng cch chn, da trn v t phng php truy xut hu hiu nht n quan h (nh dng ch mc hoc qut tun t). Th d nu v t c dng , mt ch mc c trn thuc tnh A s c dng. Tuy nhin, nu v t c dng , mt ch mc trn A khng c tc dng g, v qut tun t s c dng. Thut ton trc tin s thc hin cc php ton n ngi (n quan h) v c gng gim thiu kch thc ca cc kt qu trung gian khi xp th t cc php ton hai ngi (a quan h).

60

Chng ta k hiu qi-1qi biu th cu truy vn q c phn r thnh hai cu truy vn con qi-1v qi, trong qi-1 c thc hin trc v kt qu s c qi s dng. Cho trc cu truy vn q c n quan h, OVQP s phn r q thnh n cu truy vn con q1q2...qn. phn r ny s dng hai k thut c bn: tch (detachment) v thay th (substitution). Tch l k thut c OVQP s dng tch cu truy vn q thnh cc truy vn qq da trn mt quan h chung l kt qu ca q. Nu cu truy vn q c biu din bng SQL c dng: SELECT FROM WHERE R2.A2, R3.A3,. . ., Rn.An R1, R2,. . . , Rn P1(R1.A1) AND P2(R1.A1, R2.A2, . . . , Rn.An)

Trong A1 v A1 l cc danh sch thuc tnh ca quan h R1, P1 l v t c cha cc thuc tnh ca cc quan h R1, R2, . . ., Rn. mt cu truy vn nh th c th phn r thnh hai cu truy vn con, q theo sau l q qua php tch quan h chung R1: q: SELECT FROM WHERE R1A1 INTO R1 R1 P1(R1.A1)

Trong R1 l mt quan h tm thi cha cc thng tin cn thit thc hin tip tc cu vn tin: q: SELECT FROM WHERE R2A2,. . ., RnAn R1, R2,. . . , Rn P1(R1.A1, R2.A2,. . ., Rn.An)

Bc ny c tc dng gim kch thc ca quan h trn cu truy vn q c nh ngha. Hn na, quan h R1 mi c to ra c th lu vo cu trc c bit nhm tng tc cho cc cu truy vn con theo sau. Chng hn lu R1 trong mt tp tin bm trn thuc tnh ni ca q lm cho vic x l ni hiu qu hn. php tch s rt ra cc php ton chn, thng l nhng php c tnh chn lc cao. V th php tch thc hin mt cch c h thng mi khi c th c. V d: minh ho k thut tch chng ta c th p dng n cho cu truy vn sau:Cho bit tn ca cc nhn vin ang lm vic trong d n c tn CSDL trn c s d liu ni trn, cu truy vn ny c th c din t bng SQL nh sau: q 1: SELECT FROM NHANVIEN.TENNV NHANVIEN, HOSO, DUAN61

WHERE

NHANVIEN.MANV = HOSO.MANV

AND HOSO.MADA = DUAN.MADA AND TENDA = CSDL Sau khi tch cc php chn, cu truy vn q1 c thay bi q11 theo sau l q, trong TGIAN1 l quan h trung gian. q11: SELECT FROM WHERE q: SELECT FROM WHERE DUAN.MADA INTO TGIAN1 DUAN TENDA = CSDL NHANVIEN.TENNV NHANVIEN, HOSO, TGIAN1 NHANVIEN.MANV = HOSO.MANV

AND HOSO.MADA =TGIAN1.MADA Cc bc tch tip theo cho q c th to ra: q12: SELECT FROM WHERE q13: SELECT FROM WHERE HOSO.MANV INTO TGIAN2 HOSO, TGIAN1 HOSO.MADA =TGIAN1.MADA NHANVIEN.TENNV NHANVIEN, TGIAN2 NHANVIEN.MANV = TGIAN2.MANV

Ch c th c nhng cu truy vn con khc na. Truy vn q1 c rt gn thnh chui truy vn q11q12q13. Truy vn q11 l loi n quan h v c th cho thc hin bi OVQP. Tuy nhin cc truy vn q12 v q13 khng phi loi n quan h v cng khng th rt gn hn na bng php tch. Cc cu truy vn a quan h khng th tch tip c na (chng hn q12 v q13 trong th d trn) c gi l bt kh gin (irreducible). Cc truy vn bt kh gin c bin i thnh cu truy vn n quan h nh php th b (tuple substitution). Cho cu truy vn n-quan h q, cc b ca mt bin c thay bng cc gi tr ca chng, to ra c mt tp cc truy vn (n-1) bin. Php th b c tin hnh nh sau:

62

Trc tin chn mt quan h trong q thay th. Gi R1 l quan h . Th th vi mi b t1i trong R1, cc thuc tnh c tham chiu trong q c thay bng cc gi tr tht s trong t1i, to ra mt cu truy vn q c (n-1) quan h. V vy trong s cu truy vn q c sinh ra bi php th b l card(R1). Php th b c th tm tt nh sau: q(R1, R2, . . . , Rn) c thay bng {q(t1i, R2, R3, . . . , Rn), t1i R1}. V th i vi mi b thu c, cu truy vn con c x l quy bng php th nu n cha bt kh gin. V d: Xt tip cu truy vn q13 SELECT WHERE NHANVIEN.TENNV NHANVIEN.MANV = TGIAN2.MANV FROM NHANVIEN, TGIAN2 Quan h c nh ngha bi bin TGIAN2 chy trn thuc tnh duy nht MANV. Gi s rng n ch cha hai b: v . Php th cho TGIAN2 to ra hai cu truy vn con n quan h: q131: SELECT FROM WHERE q132: SELECT WHERE NHANVIEN.TENNV NHANVIEN NHANVIEN.MANV = E1 NHANVIEN.TENNV NHANVIEN.MANV = E2

FROM NHANVIEN Sau chng c th c OVQP qun l. Thut ton ti u ho INGRES (c gi INGRES - QOA) c trnh by di y x l qui cho n khi khng cn cu truy vn mt quan h no na. Thut ton p dng cc php chn v cc php chiu ngay khi c th s dng k thut tch. Kt qu ca cu truy vn n quan h c lu trong nhng cu trc d liu c kh nng ti u ho nhng cu truy vn sau (nh cc ni) v s c OVQP s dng. Cc cu truy vn bt kh gin cn li sau php tch s c s l bng php th b. Cu truy vn bt kh gin, c k hiu l MRQ. Quan h nh nht vi lc lng ca n c bit t kt qu ca cu truy vn trc s c chn thay th. Phng php n gin ny cho php to ra

63

cc truy vn s do OVQP x l, chn ra ng truy xut tt nht n quan h theo lng t ho vn tin. Thut ton INGRES- QOA Input: MRQ: cu truy vn a quan h c n quan h Output: Begin Output If n=1 then Output run(MRQ) h} ORQ1, ..., ORQm, MRQMRQ For i1 to m Output run(ORQi) Output output output Endfor R CHOOSE_ VARIABLE(MRQ) {R c chn cho php th b} For mi b t R MRQ thay gi tr cho t trong MRQ Output INGRES-QOA(MRQ) Output output output li} Endfor Endif End. {INGRES-QOA} 3.2.2.2. Thut ton System R System R thc hin ti u ho tnh bng cch tm kim vt cn khng gian li gii. u vo cho th ti u ho ca System R l cy ton t do phn r cu truy vn SQL. u ra ca n l mt hoch nh thc thi ci t cc ton t ti u.64

Cu truy vn ti u

{thc hin cu truy vn n quan h}

Else {Tch MRQ thnh m truy vn mt quan h v mt truy vn a quan

{thc hin ORQi } {trn tt c cc kt qu li}

{gi qui}

{trn tt c cc kt qu

Thay v thc hin mt cch c h thng cc php ton chn trc cc php ni nh trong INGRES, System R ch thc hin nu iu dn n mt chin lc tt hn. Th ti u s gn mt chi ph (theo thi gian) cho mi cy d tuyn v gi li cy c chi ph thp nht. Nhng cy d tuyn c c bng cch hon v cc th t ni ca n quan h ca cu truy vn da vo cc qui tc kt hp v hon v. hn ch chi ph dnh cho vic ti u ho, s lng cc cy c gim bt bng k thut qui hoch ng. Tp hp cc chin lc khc nhau c xy dng ng khi hai ni tng ng nhau nh hon v, ch c ni c chi ph thp nht mi c gi li. Ngoi ra cc chin lc phi ly tch Descartes u b loi b ngay khi c th. Chi ph ca mt chin lc d tuyn l t hp cc chi ph xut nhp v chi ph CPU. c lng cc chi ph ny (vo lc bin dch) da trn m hnh chi ph c a ra cc cng thc tnh chi ph cho mi thao tc mc thp (th d thao tc chn bng ch mc B-cy). i vi phn ln cc php ton (ngoi tr php chn vi i snh chnh xc), cng thc tnh chi ph u da vo lc lng ca cc ton hng. Thng tin v cc lc lng ca cc quan h trong CSDL c tm thy trong cc thng k ca CSDL, c h thng System R t ng qun l. Lc lng ca cc kt qu trung gian c c lng da vo cc h s chn ca mi php ton. Thut ton ti u ho bao gm hai bc chnh. Trc tin l d on phng php tt nht truy xut n mi quan h da trn mt v t chn la (l phng php c chi ph thp nht). Th hai i vi mi quan h R, th t ni tt nht s c c lng, trong trc tin R s c truy xut bng cch s dng phng php truy xut n quan h tt nht. Khi xt cc ni, chng ta c hai thut ton, v mt trong hai ti u trong ng cnh cho. i vi ni ca hai quan h, quan h c c trc gi l quan h ngoi cnh (external relation), cn quan h kia, l quan h c cc b c tm da theo cc gi tr c c t quan h ngoi cnh s c gi l quan h ni (internal relation). Chn phng php ni y l xc nh ng truy xut c chi ph thp nht n quan h ni. Phng php th nht c gi l lp lng (nested-loop) to ra tch ca hai quan h. i vi mi b ca ngoi cnh, tng b ca quan h ni tho v t ni s c truy xut ln lt to ra quan h kt qu. Mt ch mc trn mt thuc tnh ni l mt ng truy xut rt hiu qu i vi quan h ni. Khi khng c ch mc vi cc quan h tng ng c n1 v n2 trang, chi ph thut ton t l vi n1* n2 , v s qu cao khi n1 v n2 kh ln. Phng php th hai c gi l ni trn (merge-join) bng cch trn hai quan h sp theo thuc tnh ni. Ch mc trn thuc tnh ni c th c s65

Edited by Foxit Reader Copyright(C) by Foxit Software Company,2005-2008 For Evaluation Only.

dng lm ng truy xut. Nu iu kin ni l ng thc, chi ph ni hai quan h n1 v n2 trang t l vi n1+ n2. v th phng php lun lun c chn cho ni bng v khi cc quan h c xp th t trc . nu mt hoc c hai quan h u cha c sp, chi ph ca thut ton ni lp lng s rt ng k so vi chi ph ca php ni sp trn. Chi ph sp n trang s t l vi nlogn. Ni chung thut ton ni trn s hu ch khi cn ni cc quan h c kch thc ln. Phin bn n gin ca thut ton ti u ho ca System R cho cu truy vn chn- chiu- ni c trnh by trong thut ton di y c hai vng. Vng u tin chn ra ng truy xut mt quan h tt nht n mi n mi quan h trong cu vn tin, cn vng th hai kim tra tt c cc hon v kh hu ca cc th t ni (c n! hon v cho n quan h) v chn ra chin lc truy xut tt nht cho cu vn tin. Cc hon v c to ra bng cch xy dng ng mt cy cc chin lc khc nhau, sau l cc ni ca ba quan h. iu ny c tip tc cho n khi cc ni ca n quan h u c ti u. Vi hai Heuristic ny, s lng cc chin lc cn kim tra c cn trn l 2n ch khng phi l n!. 3.2.2.3. Thut