01 Co so du lieu

CC SS DD LLIIUU GV. Phm Th Hong Nhung B mn Cng ngh phn mm

i hc Thy li

PPHHNN

I

o Cc khi nim c bn o Kin trc h thng c s d liu o M hnh quan h thc th o M hnh quan h o Chun ho quan h o Thit k c s d liu vt l

MC LC

PHN I C S D LIU 2

MC LC 1 Chng 1. CC KHI NIM C BN .............................................................5 1.1 Ti sao phi c mt c s d liu ..................................................................5 1.2 nh ngha mt c s d liu.........................................................................5

1.2.1 Khi nim..................................................................................................5 1.2.2 u im.....................................................................................................6 1.2.3 Vn cn gii quyt................................................................................6

1.3 H qun tr c s d liu (DataBase Management System_DBMS) .........7 1.3.1 V d..........................................................................................................7 1.3.2 Khi nim..................................................................................................7

1.4 H thng c s d liu (Database System) ...................................................8 1.5 Cc i tng s dng CSDL........................................................................8

1.5.1 i tng trc tip....................................................................................8 1.5.2 i tng gin tip ...................................................................................9

1.6 Li ch ca vic s dng HQTCSDL ............................................................9

2 Chng 2. NHNG KHI NIM V KIN TRC CA H THNG C S D LIU ................................................................................................................11 2.1 M hnh d liu, lc v trng hp (Data Models, Schemas, Instances) 11

2.1.1 Phn loi m hnh d liu .......................................................................11 2.1.2 Lc (Schema) , minh ho (instances), v trng thi (State)..............14

2.2 Lc kin trc ca h qun tr c s d liu (DBMS Architecture) v s c lp d liu (Data Independence) .....................................................................15

2.2.1 Lc kin trc 3 mc ca HQTCSDL...............................................16 2.2.2 c lp d liu ........................................................................................17

2.3 Ngn ng ca HQTCSDL............................................................................17 2.4 Cc tnh nng ca HQTCSDL ....................................................................17 2.5 Phn loi HQTCSDL ...................................................................................17

3 Chng 3. M HNH QUAN H - THC TH (Entity Relationship Model) ....................................................................................................19 3.1 S dng m hnh d liu khi nim mc cao thit k c s d liu...19 3.2 Mc ch ca m hnh khi nim ER(Entity Relationship Model) ......20

MC LC

PHN I C S D LIU 3

3.3 V d v mt c s d liu ng dng ..........................................................20 3.4 Kiu thc th(Entity Type), Thuc tnh (Attributes), Kho (Keys) ........22

3.4.1 Thc th (Entities) v thuc tnh (Attributes).........................................22 3.4.2 Kiu thc th, Kho v tp gi tr ...........................................................25

3.5 Lin kt, Kiu lin kt v cc Rng buc lin kt......................................25 3.5.1 nh ngha lin kt v kiu lin kt ........................................................25 3.5.2 Bc ca kiu lin kt ...............................................................................26 3.5.3 Rng buc lin kt...................................................................................27

3.6 Kiu thc th yu(Weak Entity) .................................................................29 3.7 Tng qut ha v chuyn bit ha ..............................................................29

3.7.1 Thc th con v thc th chnh ...............................................................30 3.7.2 Cc thc th con loi tr .........................................................................30

3.8 Cc k hiu v quy c t tn trong m hnh ER...................................31 3.8.1 Cc k hiu..............................................................................................31 3.8.2 Quy tc t tn ........................................................................................31

3.9 Xy dng mt m hnh ER. .........................................................................32 3.9.1 Cc bc xy dng s ER..................................................................32 3.9.2 M hnh ER cho c s d liu COMPANY ...........................................33 3.9.3 Bi tp .....................................................................................................34

4 Chng 4. M HNH C S D LIU QUAN H.......................................37 4.1 Khi nim m hnh quan h ........................................................................37 4.2 Cc thnh phn c bn ca m hnh ..........................................................37

4.2.1 Mt s khi nim ca m hnh quan h ..................................................37 4.2.2 Quan h: ..................................................................................................37 4.2.3 Cc tnh cht ca mt quan h ................................................................38 4.2.4 Cc rng buc ton vn trn quan h ......................................................38 4.2.5 Cc php ton trn CSDL quan h..........................................................41

5 Chng 5. CHUYN T M HNH ER SANG M HNH QUAN H ......48

6 Chng 6. PH THUC HM V CHUN HO QUAN H .....................55 6.1 Mt s hng dn khi thit k c s d liu quan h ...............................55 6.2 Ph thuc hm(Functional Dependencies).................................................56

6.2.1 nh ngha ph thuc hm......................................................................56

MC LC

PHN I C S D LIU 4

6.2.2 H tin Armstrong..............................................................................57 6.2.3 Bao ng ca tp ph thuc hm............................................................57 6.2.4 Bao ng ca tp thuc tnh X trn F .....................................................57 6.2.5 Kho ca quan h ....................................................................................58 6.2.6 Tp ph thuc hm tng ng............................................................59 6.2.7 Tp ph thuc hm ti thiu ...................................................................59

6.3 Cc dng chun ca quan h.......................................................................60 6.3.1 nh ngha cc dng chun .....................................................................60 6.3.2 Php phn r cc lc quan h...........................................................66

6.4 Chun ho quan h.......................................................................................70 6.4.1 Thut ton phn r lc quan h thnh cc lc quan h con BCNF 70 6.4.2 Thut ton phn r mt lc quan h thnh cc lc con 3NF. 72

7 Chng 7. THIT K C S D LIU VT L (Tham kho) ..................75 7.1 Ni dung thit k file vt l v c s d liu vt l....................................75

7.1.1 Qu trnh thit k.....................................................................................75 7.1.2 Sn phm thit k ....................................................................................76

7.2 Thit k cc trng ......................................................................................77 7.2.1 Yu cu thit k trng...........................................................................77 7.2.2 Chn kiu v cch biu din d liu .......................................................78

7.3 Thit k cc bn ghi vt l ...........................................................................80 7.3.1 Phi chun.................................................................................................80 7.3.2 Qun l trng c di c nh............................................................81 7.3.3 Qun l trng c di bin i ..........................................................81

7.4 Thit k file vt l .........................................................................................82 7.4.1 Cc loi file .............................................................................................82 7.4.2 Cc phng php truy cp ......................................................................82 7.4.3 T chc file .............................................................................................83 7.4.4 V d v thit k file................................................................................87

Chng 1. CC KHI NIM C BN

PHN I C S D LIU 5

1 Chng 1. CC KHI NIM C BN Trong nhiu nm gn y, thut ng C s d liu - Database tr nn quen

thuc trong nhiu lnh vc. Cc ng dng tin hc vo qun l ngy cng nhiu v a dng, hu ht cc lnh vc kinh t, x hi u ng dng cc thnh tu mi ca tin hc vo phc v cng tc chuyn mn ca mnh. Chnh v l m ngy cng nhiu ngi quan tm n thit k, xy dng v ng dng c s d liu (CSDL).

Trong chng ny, chng ta s tm hiu th no l c s d liu v cc khi nim lin quan n n. Trc ht, chng ta s tm hiu l do ti sao cn phi qun l d liu bng CSDL?

1.1 Ti sao phi c mt c s d liu H thng cc tp tin c in Cho n nay vn cn mt s n v kinh t, hnh chnh s nghip s dng

m hnh h thng cc tp tin c in: chng c t chc ring r, phc v cho mt mc ch ca mt n v hay mt n v con trc thuc c th.

- u im: Vic xy dng h thng cc tp tin ring ti tng n v qun l t tn thi gian

bi khi lng thng tin cn qun l v khai thc l nh, khng i hi u t vt cht v cht xm nhiu, do trin khai ng dng nhanh.

Thng tin c khai thc ch phc v mc ch hp nn kh nng p ng nhanh chng, kp thi.

- Nhc im: Thng tin c t chc ring r nhiu ni nn vic cp nht d lm mt tnh

nht qun d liu. H thng thng tin c t chc thnh cc h thng file ring l nn thiu s

chia s thng tin gia cc ni. Qua phn tch trn, chng ta nhn thy vic t chc d liu theo h thng tp

tin hon ton khng ph hp vi nhng h thng thng tin ln. Vic xy dng mt h thng thng tin m bo c tnh nht qun d liu, p ng c nhu cu khai thc ng thi ca nhiu ngi l thc s cn thit.

1.2 nh ngha mt c s d liu 1.2.1 Khi nim CSDL v cng ngh CSDL c nhng tc ng to ln trong vic pht trin s

dng my tnh. C th ni rng CSDL nh hng n tt c cc ni c s dng my tnh:

Kinh doanh (thng tin v sn phm, khch hng, ) Gio dc (thng tin v sinh vin, im, .. ) Th vin (thng tin v ti liu, tc gi, c gi) Y t (thng tin v bnh nhn, thuc.)


PHN I C S D LIU 6

Nh vy, c s d liu l g? CSDL l tp hp cc d liu c cu trc v lin quan vi nhau c lu tr trn

my tnh, c nhiu ngi s dng v c t chc theo mt m hnh. V d: Danh b in thoi l mt v d v CSDL.

- L cc thng tin c ngha - L tp hp cc thng tin c cu trc. - Cc thng tin ny c lin quan vi nhau v c th h thng c.

Trong khi nim ny, chng ta cn nhn mnh, CSDL l tp hp cc thng tin c tnh cht h thng, khng phi l cc thng tin ri rc, khng c lin quan vi nhau. Cc thng tin ny phi c cu trc v tp hp cc thng tin ny phi c kh nng p ng nhu cu khai thc ca nhiu ngi s dng mt cch ng thi. cng chnh l c trng ca CSDL.

1.2.2 u im T khi nim trn, ta thy r u im ni bt ca CSDL l: Gim s trng lp thng tin xung mc thp nht v do m bo c tnh

nht qun v ton vn d liu (Cu trc ca c s d liu c nh ngha mt ln. Phn nh ngha cu trc ny gi l meta-data, v c Catalog ca HQTCSDL lu tr).

m bo s c lp gia d liu v chng trnh ng dng (Insulation between programs and data): Cho php thay i cu trc, d liu trong c s d liu m khng cn thay i chng trnh ng dng.

Tru tng ho d liu (Data Abstraction): M hnh d liu c s dng lm n lu tr vt l chi tit ca d liu, ch biu din cho ngi s dng mc khi nim ca c s d liu.

Nhiu khung nhn (multi-view) cho cc i ngi dng khc nhau: m bo d liu c th c truy xut theo nhiu cch khc nhau. V yu cu ca mi i tng s dng CSDL l khc nhau nn to ra nhiu khung nhn vo d liu l cn thit.

a ngi dng (multi-user): Kh nng chia s thng tin cho nhiu ngi s dng v nhiu ng dng khc nhau.

1.2.3 Vn cn gii quyt t c cc u im trn, CSDL t ra nhng vn cn gii quyt. l: Tnh ch quyn ca d liu: Do tnh chia s ca CSDL nn ch quyn ca

CSDL d b xm phm. Tnh bo mt v quyn khai thc thng tin ca ngi s dng: Do c nhiu

ngi c php khai thc CSDL nn cn thit phi c mt c ch bo mt v phn quyn hn khai thc CSDL.

Tranh chp d liu: Nhiu ngi c php cng truy cp vo CSDL vi nhng mc ch khc nhau: Xem, thm, xa hoc sa d liu. Cn phi c c ch u


PHN I C S D LIU 7

tin truy cp d liu hoc gii quyt tnh trng xung t trong qu trnh khai thc cnh tranh. C ch u tin c th c thc hin bng vic cp quyn (hay mc ) u tin cho tng ngi khai thc.

m bo d liu khi c s c: Vic qun l d liu tp trung c th lm tng nguy c mt mt hoc sai lnh thng tin khi c s c mt in t xut hoc a lu tr b hng. Mt s h iu hnh mng c cung cp dch v sao lu nh a cng (c ch s dng a cng d phng - RAID), t ng kim tra v khc phc li khi c s c. Tuy nhin, bn cnh dch v ca h iu hnh, m bo an ton cho CSDL, nht thit phi c mt c ch khi phc d liu khi c s c xy ra.

1.3 H qun tr c s d liu (DataBase Management System_DBMS)

1.3.1 V d Nh chng ta bit, kch thc v phc tp ca CSDL rt khc nhau. V d: Danh b in thoi ca mt quc gia, mt thnh ph.. cha ti hng triu s v

nhng thng tin cn thit v khch hng. Trong trng i hc c ti hng ngn sinh vin. Nh trng phi qun l tt c

nhng thng tin lin quan n sinh vin nh: tn, ngy sinh, qu qun, a ch, kt qu hc tp

Xt mt V d v CSDL qun l ti liu v c gi trong th vin quc gia. Gi s rng c 100 triu cun sch, mi cun sch cn lu 10 thng tin lin quan, mi thng tin cha ti a 400 k t th CSDL s phi c ti thiu 100 *106 * 400 *10 k t (bytes). Nh vy, dung lng b nh cn dng l: 100 *106 * 400 *10= 400 GB.

Ta thy, b nh cng l vn cn phi c gii quyt. Tuy nhin, vn quan trng hn y li l cch thc t chc d liu trong mt c s d liu phc v cho vic truy cp, tm kim, cp nht,.nhanh chng v an ton hn.

Vic t chc d liu nh th no c thc hin thng qua H qun tr c s d liu(HQTCSDL).

Vy h qun tr c s d liu (HQTCSDL) l g? 1.3.2 Khi nim. HQTCSDL l tp hp cc phn mm cho php nh ngha cc cu trc lu

tr thng tin trn my, nhp d liu, thao tc trn cc d liu m bo s an ton v b mt ca d liu.

nh ngha cu trc: nh ngha cu trc CSDL bao gm vic xc nh kiu d liu, cu trc v nhng rng buc cho d liu c lu tr trong CSDL.

Nhp d liu: L vic lu tr d liu vo cc thit b lu tr trung gian c iu khin bng HQTCSDL.

Thao tc d liu: thao tc trn CSDL bao gm nhng chc nng nh truy xut c s d liu tm kim thng tin cn thit, cp nht c s d liu v tng hp nhng bo co t d liu.


PHN I C S D LIU 8

1.4 H thng c s d liu (Database System) L phn mm HQTCSDL cng vi d liu ca bn thn c s d liu .

Hnh 1.1. Mi trng h thng c s d liu n gin

1.5 Cc i tng s dng CSDL i vi cc c s d liu nh, mang tnh c nhn nh lch lm vic, danh b

in thoi c nhn th ch cn mt ngi to ra v thao tc trn n. Tuy nhin, i vi cc CSDL ln nh: qun l ti chnh ca ngn hng nh nc, iu hnh cc chuyn bay cho cc sn bay quc t cn phi c rt nhiu ngi tham gia thit k, xy dng, bo tr CSDL v hng trm ngi s dng. Trong phn ny, chng ta tm hiu xem ai l ngi thao tc vi CSDL hng ngy. V trong phn sau, chng ta xem xt nhng ngi khng trc tip tham gia mt CSDL c th, h l ngi duy tr mi trng h thng CSDL.

1.5.1 i tng trc tip 1.5.1.1 Qun tr c s d liu Trong nhng t chc c nhiu ngi cng s dng chung mt ngun d liu th

nht thit phi c mt ngi ng u qun l, chu trch nhim i vi ngun d liu ny. chnh l ngi qun tr c s d liu (Database Administrators _ DBA ).

DBA c nhim v t chc ni dung ca c s d liu, to v phn quyn cho ngi s dng, a ra yu cu v phn cng v phn mm nu cn thit. DAB chu trch nhim bo v an ton, Backup thng tinkhi c s c.

1.5.1.2 Thit k c s d liu Ngi thit k CSDL chu trch nhim:

- Xc nh nhng d liu no cn lu tr trong CSDL - La chn nhng cu trc thch hp biu din v lu tr nhng d liu

ny.


PHN I C S D LIU 9

- Phng vn tt c nhng ngi s dng CSDL sau ny hiu c nhng yu cu ca h i vi CSDL

- Tin hnh phn tch thit k h thng sau khi thng nht c tt c cc yu cu ca ngi s dng

1.5.1.3 Ngi s dng cui Ngi s dng cui l nhng ngi truy cp CSDL :

- Truy vn - Cp nht - Thng k, bo co

1.5.1.4 Phn tch h thng v Lp trnh ng dng Phn tch h thng nh r nhng yu cu ca ngi s dng cui cng,

thng nht a ra khung nhn cho tng i tng ngi s dng, qun l cc giao tc (transactions)

Lp trnh ng dng: - Thc hin cc yu cu thng qua lp trnh bng nhng ngn ng ph hp - Chy th chng trnh (test) - Cha li v g ri chng trnh (debug) - Vit ti liu, hng dn s dng. - Bo tr h thng

1.5.2 i tng gin tip Ngoi nhng i tng trc tip tham gia vo mt CSDL c th nh ni

trn, cn c mt i ng nhng ngi phn tch, pht trin, v thc hin to ra mi trng h thng v phn mm ca h qun tr c s d liu. Nhng ngi ny khng trc tip thao tc trn mt h qun tr CSDL no c th. H l:

- Ngi phn tch v thc hin to ra h thng ca HQTCSDL - Nhng nh pht trin h cng c (Tool developers) - Ngi kim th v bo tr h thng

1.6 Li ch ca vic s dng HQTCSDL - Hn ch d tha d liu. - Ngn cn truy cp d liu bt hp php (bo mt v phn quyn s dng). - Cung cp kh nng lu tr lu di cho cc i tng v cu trc d liu. - Cho php suy dn d liu (t d liu ny suy ra d liu khc) s dng

Rules. - Cung cp giao din a ngi dng. - Cho php biu din mi quan h phc tp gia cc d liu.


PHN I C S D LIU 10

- m bo rng buc ton vn d liu (Enforcing Integrity Constraints). - Cung cp th tc sao lu v phc hi (backup v recovery)

Chng 2. NHNG KHI NIM V KIN TRC CA H THNG C S D LIU

PHN I C S D LIU 11

2 Chng 2. NHNG KHI NIM V KIN TRC CA H THNG C S D LIU

2.1 M hnh d liu, lc v trng hp (Data Models, Schemas, Instances)

Mt trong nhng c im c bn ca c s d liu l cung cp mt s mc tru tng ho d liu bng cch lm n i cch thc t chc d liu- ci m hu ht ngi dng khng cn bit n.

M hnh d liu (Data Model): L mt tp nhng khi nim dng biu din cu trc ca c s d liu-cung cp nhng iu kin cn thit t c mc tru tng d liu. Cu trc d liu bao gm kiu d liu (data types) v mi quan h gia cc d liu (relationships) v nhng rng buc (constraints) m c s d liu phi tun theo.

Hu ht m hnh d liu u c mt tp hp cc thao tc c bn (basic operations) truy vn v cp nht d liu.

- Thao tc chung (generic operations): Thm (insert), Xo (delete), Sa (modify), Truy cp (retrieve)

- Thao tc do ngi dng nh ngha (user-defined operations) 2.1.1 Phn loi m hnh d liu C rt nhiu m hnh d liu c a ra, chng ta c th phn loi chng

theo nhng kiu khi nim m h dng biu din cu trc c s d liu. M hnh d liu c chia lm 3 loi sau:

a. M hnh khi nim (Conceptual (high-level, semantic) data models): Cung cp nhng khi nim gn gi vi a s ngi s dng, m hnh ny ch ra

ci g c a vo qun l. M hnh ny l phng tin nhng ngi phn tch thit k giao tip vi ngi s dng, nhm thu thp thng tin, xc nh ng n v y yu cu ca h thng.

M hnh ny s dng cu trc d liu l: thc th (entity), thuc tnh (attribute) v mi lin kt (relationship)

b. M hnh d liu vt l (Physical (low-level, internal) data models): Cung cp nhng khi nim biu din chi tit cch thc d liu c lu tr

trong my tnh. M hnh ny ch ra nh dng bn ghi (record formats), th t sp xp cc bn ghi (record ordering) v ng dn truy cp d liu (access paths).

c. M hnh d liu th hin (Implementation (record-oriented) data models): M t cc d liu bng cch s dng nhng k php tng ng vi m hnh d

liu m mt h qun tr c s d liu s dng. Cc loi m hnh c s d liu th hin: c1. M hnh phn cp


PHN I C S D LIU 12

M hnh CSDL phn cp c biu din di dng cy v cc nh ca cy l cc bn ghi. Cc bn ghi lin kt vi nhau theo mi quan h cha-con.

- Mt cha c nhiu con - Mt con ch c mt cha

V d:

Hnh 2.1. Minh ha m hnh c s d liu phn cp u im:

- Th hin d dng quan h 1-N. - Vic phn chia d liu d th hin, m bo an ton d liu - Tnh c lp ca chng trnh v cc d liu c m bo

Nhc im: - Khng th hin c mi quan h M-N - Trong mt h thng phn cp, d liu c t chc nh trn d n kh

sa i d liu. c2. M hnh mng

Cu trc c bn trong m hnh mng l nhng tp hp v mi tp hp c bn ghi l bn ghi ch v mt s bn ghi thnh vin. Mi thnh vin c th thuc v nhiu tp hp.

V d:

Hnh 2.2. Minh ha m hnh c s d liu mng

PHNG

D N NHN VIN

PH VIC K NNG TRANGTHIT B

PHNG

D N NHN VIN


PHNG

D N NHN VIN



PHN I C S D LIU 13

u im:

- D th hin mi lin kt M-N - Kiu truy cp d liu mm do hn kiu phn cp

Nhc im: - Vic sa i s liu kh khn. - Vi nhng lp trnh vin, vic thit k CSDL kh.

c3. M hnh quan h Trong m hnh quan h, cc d liu c biu din dng cc bng vi cc

dng v cc ct. Trong m hnh quan h khng c mt cu trc vt l no ca d liu m t s

kt ni gia cc bng. Thay vo , s kt ni gia cc bng c m t logic bng cc gi tr c lu tr trong cc dng ca bng. Chng hn trong hnh di y, thuc tnh ProCode(M tnh) c lu trong c 2 bng PROVINCE v bng STUDENT, gi tr chung ny cho php ngi dng lin kt c 2 bng.

PROVINCE

ProCode ProName

04 H Ni

08 Tp H Ch Minh

STUDENT

StdNo StdName StdBird ProCode

TD001 AA 9/16/1979 04

TD002 BB 6/19/1979 08

.

Hnh 2.3. Minh ha m hnh c s d liu quan h

c4. M hnh hng i tng

Trong m hnh hng i tng, cc thuc tnh d liu v cc thao tc trn cc d liu ny c bao gi trong mt cu trc gi l i tng.


PHN I C S D LIU 14

i tng c th cha cc d liu phc hp nh vn bn, hnh nh, ting ni v hnh nh ng. Mt i tng c th yu cu hoc x l d liu t mt i tng khc bng vic gi i mt thng bo n i tng . M hnh hng i tng biu din mt s mi lu tr v thao tc d liu. T mt i tng c th sinh ra mt i tng khc.

Hnh 2.4. Minh ha m hnh c s d liu hng i tng

2.1.2 Lc (Schema) , minh ho (instances), v trng thi (State) Lc c s d liu (Database Schema): l biu din ca c s d liu, bao

gm cu trc c s d liu v nhng rng buc trn d liu. S ca lc c s d liu (Schema Diagram): L lc c s d liu

c biu din thng qua s . V d:

PHONG

NHANVIEN TRANG B

NHANVIEN

PHONG

TRANGBI

PHONG

LUONGGIO LUONGTH

schema construct


PHN I C S D LIU 15

Hnh 2.5. Lc c s d liu UNIVERSITY

Minh hc c s d liu (Database Instance): L d liu thc s c lu tr trong c s d liu thi im hin ti. Database Instance cng c gi l trng thi ca c s d liu (database state) V d:

Hnh 2.5. C s d liu UNIVERSITY Nh vy, Database Schema rt him khi thay i, cn Database State thay

i bt k khi no c s cp nhp d liu.

2.2 Lc kin trc ca h qun tr c s d liu (DBMS Architecture) v s c lp d liu (Data Independence)

Nh chng ta bit, cc tnh cht quan trng nht ca c s d liu l: (1) m bo s c lp gia chng trnh ng dng v d liu. (2) H tr nhiu khung nhn cho cc i tng ngi dng khc nhau. (3) S dng danh mc lu tr biu din d liu (schema). Trong phn ny, chng ta s tm hiu kin trc ca h qun tr


PHN I C S D LIU 16

c s d liu, gi l Lc kin trc 3 mc mc(three schema architecture). Sau chng ta s tm hiu v khi nim c lp d liu.

2.2.1 Lc kin trc 3 mc ca HQTCSDL Mc ch ca vic phn thnh 3 mc trong kin trc ca m hnh HQTCSDL l

tch bit cc ng dng ca ngi s dng vi c s d liu vt l. Trong kin trc ny, lc c th c nh ngha 3 mc sau:

Lc mc trong (Internal schema) Mc trong (Internal level) biu din chi tit cu trc lu tr d liu v cch thc truy cp d liu. Lc mc trong s dng m hnh d liu vt l (physical data model).

Lc khi nim (Conceptual schema) Mc khi nim (Conceptual level) biu din cu trc v cc rng buc trong ton b c s d liu phc v cho vic giao tip vi ngi s dng. Lc khi nim n i cch thc t chc vt l ca d liu, ch tp trung vo vic biu din cc thc th, cc kiu d liu, mi quan h gia cc thc th, cc thao tc ca ngi s dng v cc rng buc gia cc d liu. M hnh d liu mc khi nim (Conceptual data model) hoc M hnh d liu th hin (Implementation data model) c th c s dng mc ny.

Lc mc ngoi (External Level) Mc ngoi (External level hoc View level) biu din hng lot nhng khung nhn ca ngi s dng (user views). M hnh d liu mc cao (High-level data model) hoc M hnh d liu th hin (Implementation data model) c th c s dng mc ny.

nh x gia cc mc ny l cn thit. Nhng chng trnh lm vic vi d liu mc ngoi v c HQTCSDL nh x ti d liu vt l mc trong thc hin.

Hnh 2.6. Lc kin trc 3 mc ca HQTCSDL


PHN I C S D LIU 17

2.2.2 c lp d liu Kin trc 3 mc ca HQTCSDL c th c s dng gii thch khi nim v

c lp d liu. c lp d liu l kh nng thay i lc mt mc no ca h thng c s d liu m khng cn phi thay i lc mc cao hn. Chng ta c th nh ngha 2 kiu ca c lp d liu:

c lp d liu logic (Logical data independence): cho php thay i lc khi nim m khng cn phi thay i lc mc ngoi hoc nhng chng trnh ng dng. Chng ta c th thay i lc khi nim m rng (thm cc trng d liu, cc bn ghi) hoc thu nh c s d liu (xa cc trng d liu, cc bn ghi).

c lp d liu vt l (Physical data independence): cho php thay i lc mc trong m khng cn thay i lc khi nim. C khi chng ta cn thay i lc mc trong v cc file vt l i khi cn t chc li tng hiu qu thc hin. Nu kiu d liu khng thay i th chng ta khng cn thay i li lc khi nim.

2.3 Ngn ng ca HQTCSDL V HQTCSDL phc v c nhiu i tng ngi s dng khc nhau nn n

phi h tr ngn ng ngi s dng tng tc vi n. Trong phn ny chng ta s tm hiu mt s nhng ngn ng c HQTCSDL h tr.

Ngn ng nh ngha d liu (Data Definition Language - DDL): L ngn ng c cc nh qun tr c s d liu (DBA) v cc nh thit k c s d liu (database designers) dng xy dng lc khi nim ca c s d liu. Trong nhiu HQTCSDL, DDL cng c s dng nh ngha lc mc trong v mc ngoi (cc khung nhn). Mt s HQTCSDL chia thnh 2 ngn ng: Ngn ng nh ngha lu tr (storage definition language SDL) v Ngn ng nh ngha khung nhn (view definition language -VDL) c dng nh ngha lc mc trong v mc ngoi.

Ngn ng thc hin d liu (Data Manipulation Language -DML): L ngn ng c s dng thao tc vi d liu bao gm vic truy cp n bn ghi v cp nht d liu cho bn ghi (thm, sa, xo). Cc lnh DML c th c nhng trong ngn ng lp trnh hoc thc hin c lp (ngn ng truy vn).

2.4 Cc tnh nng ca HQTCSDL - Np d liu ang lu tr cc tp tin vo c s d liu (Conversion

Tool). - Cung cp cc thao tc truy xut - m bo tnh c lp d liu - Cung cp th tc sao lu v phc hi (backup v recovery) - Cung cp cc th tc iu khin ng thi (Do tnh truy xut ng

thi v cnh tranh) - Cung cp cc th tc kim sot bn quyn, kim tra tnh ng n

ca d liu ( m bo tnh an ton v ton vn d liu)

2.5 Phn loi HQTCSDL Ngi ta phn loi HQTCSDL da trn mt s tiu ch:


PHN I C S D LIU 18

Da trn m hnh d liu c HQTCSDL s dng: M hnh quan h (Relational Data Model), M hnh hng i tng (Object Data Model), M hnh mng (Network Data Model), m hnh phn cp (Hierarchical Data Model), m hnh quan h thc th (Entity Relationship Data Model)...

Tiu ch khc: - S ngi s dng HQTCSDL: n ngi dng (Single-user), a

ngi dng (multi-user). Hu ht cc HQTCSDL hin nay u l HQTCSDL a ngi dng.

- V tr HQTCSDL: tp trung (s dng mt my n) hay phn tn (s dng nhiu my tnh).

- Gi ca HQTCSDL: 100$- 300$; 10000$- 100000$

Chng 3. M HNH QUAN H - THC TH

PHN I C S D LIU 19

3 Chng 3. M HNH QUAN H - THC TH (Entity Relationship Model)

3.1 S dng m hnh d liu khi nim mc cao thit k c s d liu

Hnh 3.1. Cc giai on thit k c s d liu Hnh trn ch ra tin trnh thit k c s d liu mt cch n gin. Bc u

tin l tp hp v phn tch yu cu h thng. Trong bc ny, ngi thit k c s d liu phi tin hnh thu thp yu cu ca ngi s dng, sau vit ti liu nhng yu cu d liu. Kt qu ca bc ny l vit ra c tp hp nhng yu cu tt c cc i tng ngi dng mt cch xc tch. T , ta xc nh c yu cu chc nng (Funtional Requirements) ca h thng.

Sau khi tt c cc yu cu c tp hp v phn tch, bc tip theo l xy dng lc khi nim(conceptual schema) cho c s d liu. Lc khi nim l ni biu din xc tch nhng yu cu ca ngi s dng v biu din chi tit nhng kiu thc th (entity types),quan h (relationships) v nhng rng buc (constraints) ca d liu, phn ny s dng nhng khi nim c cung cp trong m hnh d liu mc cao (High level data model). M hnh d liu mc cao l m hnh d liu c dng giao tip vi ngi s dng bnh thng v th n rt d hiu, n ch ra ci g


PHN I C S D LIU 20

cn c lu trong c s d liu ch khng ch ra c th d liu c thc hin nh th no.

Bc tip theo trong qu trnh thit k l ci t c s d liu trn mt m hnh d liu thc hin, s dng cc H qun tr c s d liu no (Hu ht cc h qun tr c s d liu hin nay s dng m hnh d liu quan hoc hng i tng). V th, chng ta cn thit phi chuyn t m hnh d liu mc cao sang nhng m hnh d liu thc hin. Bc ny c gi l thit k logic (Logical design) hoc nh x m hnh d liu (Data model mapping) v kt qu ca bc ny l lc c s d liu trong m hnh c s d liu thc hin.

Bc cui cng l thit k vt l cho c s d liu (physical design), bao gm vic thit k nhng cu trc lu tr d liu bn trong, ng dn truy cp, t chc file ca cc file d liu.

Trong chng ny, chng ta s s dng m hnh khi nim ER cho thit k lc khi nim (Conceptual Schema).

3.2 Mc ch ca m hnh khi nim ER(Entity Relationship Model)

Qua bc xem xt tng qut trn, ta thy rng, m hnh E-R l mt m t logic chi tit d liu ca mt t chc hay mt lnh vc nghip v. N gip ngi thit k c s d liu m t th gii thc gn gi vi quan nim v cch nhn nhn nhn bnh thng ca con ngi. N l cng c phn tch thng tin nghip v.

Mc ch ca m hnh E R: - Lm thng nht quan im v d liu ca nhng ngi tham gia h

thng: Ngi qun l, ngi dng cui, ngi thit k h thng - Xc nh cc x l v d liu cng nh cc rng buc trn cc d liu. - Gip vic th hin c s d liu v mt cu trc: S dng thc th

v cc mi lin kt gia cc thc th. Biu din m hnh quan h thc th bng mt s .

3.3 V d v mt c s d liu ng dng Trong phn ny, chng ta s tm hiu mt v d v c s d liu ng dng - gi

l COMPANY minh ho cc khi nim trong m hnh ER v s dng n trong khi thit k lc khi nim.

C s d liu COMPANY cn lu tr thng tin v nhn vin (employees), phng/ban (departments), v cc d n (projects) trong cng ty. Sau khi tp hp c tt c cc yu cu ca h thng, ngi thit k c s d liu tin hnh m t li miniworld bng m hnh ER.

Sau y l cc quy tc nghip v ca h thng c s d liu COMPANY, chng ta s xy dng m hnh ER tng bc gii thiu cc khi nim ca m hnh ER.

1. Cng ty (COMPANY) c nhiu phng/ban (DEPARTMENTs). Mi phng/ban c tn (name), m s (number) duy nht v c mt nhn vin (employee) lm qun l (manages) phng/ban. Chng ta lu li ngy bt u (start date) lm qun l phng/ban ca nhn vin .


PHN I C S D LIU 21

2. Mi phng/ban c th c nhiu a im khc nhau (locations). 3. Mi phng/ban iu hnh mt s d n (PROJECTs). Mi d n c tn

(name), m s (number) duy nht v ch c mt a im (location). 4. Vi mi nhn vin, chng ta lu li nhng thng tin sau: tn (name), s bo

him x hi (social security number), a ch (address), lng (salary), gii tnh (sex), v ngy sinh (birth date).

5. Mi nhn vin lm vic mt phng/ban, nhng c th lm vic cho nhiu d n. Chng ta lu li s gi lm vic (the number of hours per week) ca tng nhn vin trong tng d n.

6. Chng ta lu li thng tin v ngi qun l trc tip (direct supervisor), ca mi nhn vin. Ngi qun l trc tip cng l mt nhn vin.

7. Mi nhn vin c nhng ngi ph thuc vo h (DEPENDENTs). Mi ngi ph thuc ta lu li thng tin v tn (name), gii tnh (sex), ngy sinh (birth date) v quan h (relationship).

Hnh minh ho sau s ch ra s ER ca c s d liu COMPANY da trn nhng k hiu ho c quy nh trong m hnh ER.

Hnh 3.2. S ER ca c s d liu COMPANY


PHN I C S D LIU 22

Lu , trn y ch l gii thiu s b v m hnh ER, cc bn cha cn phi hiu c ton b s trn.

Phn tip theo chng ta s gii thiu v m hnh ER. Cc khi nim c bn trong m hnh ER gm c Thc th (Entity), Thuc tnh (Attributes), v Quan h (Relationship).

3.4 Kiu thc th(Entity Type), Thuc tnh (Attributes), Kho (Keys)

3.4.1 Thc th (Entities) v thuc tnh (Attributes) 3.4.1.1 Thc th - Cc thc th vn tn ti trong th gii thc. Mt thc th l mt khi

nim ch mt lp cc i tng c th hay cc khi nim c cng nhng c tnh chung m ta quan tm.

- Tn thc th: L tn ca mt lp i tng. Trong 1 CSDL, tn thc th khng c trng nhau.

- K hiu: Trong m hnh E-R, thc th c biu din bng mt hnh ch nht c tn bn trong.

- V d:

3.4.1.2 Thuc tnh - Thuc tnh l cc c trng (properties) c s dng biu din

thc th. - V d: Thc th EMPLOYEE c cc thuc tnh: Name, SSN, Address,

Sex, BirthDate. - Thuc tnh c k hiu bng hnh oval, bn trong ghi tn ca thuc

tnh. Thuc tnh ca thc th no th s c gn vi thc th . - V d:

Hnh 3.3. Thc th EMPLOYEE v cc thuc tnh ca n

EMPLOYEE DEPARTMENT

EMPLOYEEBdate Salary

Address

SexName

SSN

Lname MinitFname


PHN I C S D LIU 23

Hnh 3.4. Thc th DEPARTMENT v cc thuc tnh ca n

Hnh 3.5. Mt s k hiu ca thuc tnh Cc kiu thuc tnh trong m hnh ER: Thuc tnh n (simple) i lp vi thuc tnh t hp (composite), thuc

tnh n tr (single-value) i lp vi thuc tnh a tr (multivalued), thuc tnh lu tr (stored) i lp vi thuc tnh suy din (derived). Sau y ta s tm hiu chi tit v cc loi thuc tnh ny:

Thuc tnh n (simple) hay cn gi l thuc tnh nguyn t (Atomic): Ch c mt gi tr trong mt thuc tnh ca mt thc th. V d: Thuc tnh Birthdate, Sex ca Employee l thuc tnh nguyn t.

Thuc tnh t hp (Composite): l thuc tnh c kt hp ca mt s thnh phn. V d: Address(Apt#, House#, Street, City, State, ZipCode, County) hoc Name (FirstName, MiddleName, LastName) l thuc tnh t hp.

Thuc tnh t hp c th c biu din phn cp nh sau:

DEPARTMENT

Locations

Number

Name

NumberOfEmployee

singled

multiple-valued

key attribute

derived attribute


PHN I C S D LIU 24

Hnh 3.6. Biu din phn cp ca thuc tnh t hp Thuc tnh n tr (single-value): L thuc tnh ch c mt gi tr duy nht

mt thi im. V d: Sex, Birthdate, Thuc tnh a tr (multivalued): L thuc tnh c th c nhiu gi tr ti mt

thi im. V d: PreviousDegrees K hiu: {PreviousDegrees} Thuc tnh t hp v thuc tnh a tr c th lm t nhiu mc, mc d t gp

trng hp ny. V d: PreviousDegrees ca thc th STUDENT l loi thuc tnh . K hiu: {PreviousDegrees (College, Year, Degree, Field)}.

Thuc tnh lu tr (stored attribute) v thuc tnh suy din (derived attribute): Thuc tnh lu tr l thuc tnh m gi tr ca n phi c lu tr, cn thuc tnh suy din l thuc tnh m gi tr ca n c th suy ra t gi tr ca nhng thuc tnh khc. V d: Age(derived attribute) c suy din t BirthDate (stored attribute)

Gi tr rng ca thuc tnh (Null Values): Trong mt vi trng hp, mt thc th c th khng c gi tr tng ng cho mt thuc tnh, v d thuc tnh NameDependent(Tn ca ngi ph thuc), nu mt nhn vin no trong thc th EMPLOYEE cha c ngi ph thuc th thuc tnh NameDependent tng ng vi nhn vin s khng c gi tr. Hoc trong trng hp thuc tnh c gi tr nhng cha c bit, v d thuc tnh PhoneNumber (S in thoi). Trong trng hp ny, mt gi tr c bit c to ra, l gi tr Null.

Mi thuc tnh trong thc th lun c gi tr, v d cc thuc tnh trong thc th EMPLOYEE c cc gi tr sau: Name=John Smith, SSN=123456789, Address=731 Fondren, Houston, TX, Sex=M, BirthDate= 09-JAN-55. Mt b gi tr ca mt thc th c gi l mt bn ghi (record).


PHN I C S D LIU 25

relationship

Hnh 3.7. Hai thc th v gi tr thuc tnh ca n 3.4.2 Kiu thc th, Kho v tp gi tr Kiu thc th: l tp hp nhng thc th c cng cc thuc tnh c bn. V d,

kiu thc th EMPLOYEE, kiu thc th PROJECT. Kho: Mi mt kiu thc th phi c mt hoc mt tp cc thuc tnh mang gi

tr duy nht (unique value) phn bit gia bn ghi ny vi bn ghi khc. Thuc tnh gi l kho ca kiu thc th (Key attribute).V d: thuc tnh SSN ca kiu thc th EMPLOYEE, hoc thuc tnh NumberStudent(M sinh vin) ca kiu thc th STUDENT. Ch l kho c th gm mt hoc mt tp cc thuc tnh.

Tp gi tr hay cn gi l min xc nh(Domain): l tp nhng gi tr m thuc tnh c th nhn c. V d: Min xc nh ca thuc tnh Sex l {Male, Female}, hoc ca Mark(im) l t 0..10.

Tp gi tr khng c biu din trong lc ER.

3.5 Lin kt, Kiu lin kt v cc Rng buc lin kt 3.5.1 nh ngha lin kt v kiu lin kt Lin kt (Relationship) dng ch mi quan h gia hai hay nhiu thc th

khc nhau. V d: Nhn vin (A) lm vic cho d n (X), nhn vin B lm vic cho d n (X)

Nhng lin kt ca cng mt kiu c nhm li gi l kiu lin kt (Relationship Type), v d kiu lin kt WORK_ON (lm vic cho), kiu lin kt MANAGES ( lm qun l)

Trong lc ER, ngi ta s dng hnh thoi v bn trong ghi tn kiu lin kt k hiu kiu lin kt.

K hiu:

Lu : Kiu lin kt cng c th c thuc tnh ca n.

employee department


PHN I C S D LIU 26

3.5.2 Bc ca kiu lin kt L s lng cc kiu thc th tham gia vo lin kt. C cc kiu lin kt sau: - Kiu lin kt bc 1 ( quy) l mi quan h gia cng 1 kiu thc th. - Kiu lin kt bc 2 l mi lin kt gia hai kiu thc th - Kiu lin kt bc 3 l mi lin kt gia 3 kiu thc th

3.5.2.1 Mi quan h bc 1 Mi quan h bc 1 ( quy) l mi quan h gia cng 1 kiu thc th V d:

Hnh 3.8. Minh ha mi quan h bc 1 3.5.2.2 Mi quan h bc 2 L mi quan h gia 2 kiu thc th khc nhau. V d:

Hnh 3.9. Minh ha mi quan h bc 2 V d: Mi lin kt gia hai kiu thc th DEPARTMENT v EMPLOYEE sau

y l kiu lin kt bc 2 v n c s tham gia ca hai kiu thc th. Hnh minh ho trn cn cho ta thy, c th c nhiu hn mt kiu lin kt gia

hai kiu thc th khc nhau.

3.5.2.3 Mi quan h bc 3 L mi quan h gia 3 kiu thc th khc kiu.

DEPARTMENT

EMPLOYEE

works for manages

1

N 1

1

PERSON marry EMPLOYEE supervisor

1

1

1

N


PHN I C S D LIU 27

Hnh 3.10. Minh ha mi quan h bc 3 3.5.3 Rng buc lin kt Cc kiu lin kt thng c mt s rng buc no v cc thc th c th kt

hp vi nhau tham gia trong mt lin kt ph hp. Cc rng buc ny xc nh t tnh hung thc t m lin kt th hin.C cc loi rng buc nh sau:

3.5.3.1 T s lc lng: Trong cc kiu lin kt bc 2, t s lc lng ch r s thc th tham gia vo

lin kt. Cc t s lc lng c th l: 1:1, 1:N, N:1 v M:N. T s 1:1: Mt thc th ca kiu A c lin kt vi mt thc th ca kiu B v

ngc li.

V d: Mt nhn vin (EMPLOYEE) qun l mt phng (DEPARTMENT) , v

mt phng ch c mt nhn vin qun l.

T s 1:N: Mt thc th ca kiu A c lin kt vi nhiu thc th ca kiu B. Nhng mt thc th ca kiu B li c lin kt duy nht vi thc th ca kiu A.

V d: Mt nhn vin (EMPLOYEE) lm vic cho mt phng

(DEPARTMENT), v mt phng c nhiu nhn vin lm vic.

B A relationship1 1

DEPARTMENT EMPLOYEE Manages 1 1

B A relationship1 N

teach

SUBJECT

TEACHER CLASS

Hours

M

N

M


PHN I C S D LIU 28

T s M:N: Mt thc th ca kiu A c lin kt vi nhiu thc th ca kiu B

v ngc li.

V d:

3.5.3.2 Rng buc v s tham gia lin kt (Participation constraint) Rng buc v s tham gia lin kt c xc nh trn tng thc th trong tng kiu lin kt m thc th tham gia, bao gm: lc lng tham gia ton b (total participation) v lc lng tham gia b phn (partial participation).

V d: Trong kiu lin kt Manages gia hai kiu thc th EMPLOYEE v DEPARTMENT, lc lng tham gia ca kiu thc th DEPARTMENT l ton b, v DEPARTMENT no cng c ngi qun l, cn lc lng tham gia ca kiu thc th EMPLOYEE l b phn v khng phi EMPLOYEE no cng lm qun l (manages) ca DEPARTMENT.

Trong s ER, kiu thc th c lc lng tham gia lin kt ton b c ni vi kiu lin kt bng gch ni kp, cn kiu thc th c lc lng tham gia b phn c ni vi kiu lin kt bng gch ni n.

V d:

3.5.3.3 Lc lng tham gia lin kt Trong mi lin kt gia cc thc th, ta cn quan tm n lc lng tham gia

lin kt, l s bn ghi ln nht v nh nht ca thc th tham gia vo lin kt . K hiu: Thm (min,max) vo mi lin kt. Trong : - min l s bn ghi nh nht tham gia vo lin kt

DEPARTMENT EMPLOYEE Work_

for

N 1

PROJECT EMPLOYEE Work_

on

M N

DEPARTMENT EMPLOYEE Manages

1 1

BA M N

relationship


PHN I C S D LIU 29

- max l s bn ghi ln nht tham gia vo lin kt - Mc nh, min=0, max=n - Chng ta xc nh lc lng ny t kho st thc t bi ton. V d: a. Ti mt thi im, mt phng c duy nht mt ngi qun l-ngi

l nhn vin, mt nhn vin ch qun l duy nht mt phng. V vy, (0,1) l lc lng ca EMPLOYEE v (1,1) l lc lng ca DEPARTMENT tham gia trong lin kt Manages(qun l).

b. Mt nhn vin ch c th lm vic cho mt phng nhng mt phng c th c bt k s lng nhn vin no. V th, (1,1) l lc lng ca EMPLOYEE v (0,n) l lc lng ca DEPARTMENT tham gia trong lin kt Works_For(lm vic cho).

3.5.3.4 Thuc tnh ca kiu lin kt Kiu lin kt cng c th c thuc tnh. V d: S gi nhn vin lm vic cho

d n (Hours) l thuc tnh ca mi lin kt gia hai kiu thc th EMPLOYEE v PROJECT.

3.6 Kiu thc th yu(Weak Entity) Kiu thc th yu l kiu thc th tn ti ph thuc vo thc th khc (thc th

lm ch hay cn gi l xc nh n). Kiu thc th yu khng c kho. Kiu thc th yu c xc nh bng: - Mt hay mt tp cc thuc tnh xc nh kiu thc th yu - V thc th lm ch (xc nh) thc th yu. Kiu thc th yu lun c lc lng tham gia lin kt ton b.

3.7 Tng qut ha v chuyn bit ha Tng qut ha l khi nim cho php ta xem mt vt th no (cc thc th)

l mt thc th con ca mt vt th khc tng qut hn. V d: SACH l mt loi con ca loi tng qut hn l TAILIEU ni chung. Chuyn bit ha l khi nim ngc li vi tng qut ha. V d: t, xeca, taxi gp li thnh mt thc th tng qut hn l Phuongtienvantai.

PROJECT EMPLOYEE Work_on

M N

Hours


PHN I C S D LIU 30

3.7.1 Thc th con v thc th chnh Trong m hnh d liu, cn phi m t mt cch r rng cc thc th gn nh

nhau: l cc thc th c cc thuc tnh chung, nhng cng c mt s cc thuc tnh khc nhau.

V d: Trong mt n v c 3 loi nhn vin: SECRETARY, TECHNICIAN, ENGINEER. Cc thc th ny c mt s thuc tnh chung v mt s thuc tnh ring.

Trong trng hp ny, c th c 3 hng gii quyt sau: - Cch n gin nht l gp tt c cc loi nhn vin vo mt thc th

EMPLOYEE. Cch ny dn n d tha thng tin, v c nhng thuc tnh lun rng i vi mi loi nhn vin.

- Cch th hai, nh ngha ring r tng loi thc th: SECRETARY, TECHNICIAN, ENGINEER. Cch ny khng khai thc c nhng thuc tnh chung ca nhn vin.

- Cch th ba, nh ngha mt thc th chnh gi l EMPLOYEE, vi 3 thc th con l: SECRETARY, TECHNICIAN, ENGINEER. Nhng thuc tnh chung nm trong thc th chnh, cn thc th con s cha thuc tnh ring ca n. Hnh sau y minh ho cch gii quyt ny:

Hnh 3.11. Minh ha mi quan h gia thc th con v thc th chnh 3.7.2 Cc thc th con loi tr Trong m hnh trn, cc thc th con l loi tr ln nhau. Thc th con loi tr gm 2 loi:

EMPLOYEE

SECRETARY TECHNICIAN ENGINEER

FName Minit LName

Name Ssn BirthDate Address

Typing Speed TGrade EngType

d

JobType

Job Type

EngineerSecretary

Technician


PHN I C S D LIU 31

Thc th con y : L tt c cc thc th con xc nh mt thc th chnh. Trong v d trn, tt c cc thc th con l y v khng th b sung thc th no vo thc th chnh EMPLOYEE. Thc th con khng y : Tp cc thc th con khng y xc nh thc th chnh. V d: Tp thc th T, XEMAY cha xc nh c thc th chnh l PHUONGTIEN.

3.8 Cc k hiu v quy c t tn trong m hnh ER 3.8.1 Cc k hiu Trong xy dng m hnh E-R, ta s dng cc k hiu trong hnh 3.12:

Hnh 3.12. Bng tng hp cc k hiu trong m hnh ER

3.8.2 Quy tc t tn

Thc th

Thc th yu

Mi quan h

Mi quan h xc nh

Thuc tnh

Thuc tnh kha

Thuc tnh a tr

Thuc tnh t hp

Thuc tnh suy din

E2 tham gia ton b trong R

T s tham gia lin kt 1:N

Lc lng ca E2 trong R


PHN I C S D LIU 32

3.9 Xy dng mt m hnh ER. 3.9.1 Cc bc xy dng s ER 3.9.1.1 Lit k, chnh xc ha v la chn thng tin c s Xc nh mt t in bao gm tt c cc thuc tnh (khng b st bt c thng

tin no). Chnh xc ha cc thuc tnh . Thm cc t cn thit thuc tnh mang

y ngha, khng gy lm ln, hiu nhm. Ch : la chn cc c trng cn thit , ta duyt t trn xung v ch gi

li nhng thuc tnh m bo yu cu sau: Thuc tnh cn phi c trng cho mt lp cc i tng c xt. Chn mt thuc tnh mt ln, nu lp li th b qua. Mt thuc tnh phi l s cp (Nu gi tr ca n c th suy ra t cc thuc tnh

khc th b qua).

3.9.1.2 Xc nh cc thc th v cc thuc tnh ca n, sau xc nh thuc tnh nh danh cho tng thc th.

Duyt danh sch cc thuc tnh t trn xung tm ra thuc tnh tn gi. Mi thuc tnh tn gi s tng ng vi mt thc th.

Gn cc thuc tnh cho tng thc th. Xc nh thuc tnh nh danh cho tng thc th.

3.9.1.3 Xc nh cc mi quan h v cc thuc tnh ring ca n Xt danh sch cc thuc tnh cn li, hy tm tt c cc ng t (ng vi thuc

tnh ). Vi mi ng t, hy tr li cc cu hi: Ai? Ci g? u? Khi no? Bng cch no?

3.9.1.4 V s m hnh thc th- mi quan h, xc nh lc lng tham gia lin kt cho cc thc th.

3.9.1.5 Chun ha s v thu gn s - V s . - Chun ha s , nu trong cn c cha: cc thuc tnh lp, nhm

lp v cc thuc tnh ph thuc thi gian s ch cn cc thc th n v cc thuc tnh n.

Quy tc t tn:

entity types attributes relationship types roles

Danh t

ng t


PHN I C S D LIU 33

Thu gn s : Nu mt thc th c tt c cc c trng: - L thc th treo: l thc th ch tham gia vo mt mi quan h

v ch cha mt thuc tnh duy nht thc s l ca n (c th c thuc tnh th 2 thm vo lm nh danh).

- Mi quan h l bc hai v khng c thuc tnh ring. - Mi quan h l 1: N hay 1:1.

V d: c thu gn thnh s sau:

3.9.2 M hnh ER cho c s d liu COMPANY

a. Qua Bc 1 v Bc 2 ta xc nh c danh sch cc thc th v cc thuc tnh ca tng thc th.

b. Qua bc 3 ta xc nh c cc kiu lin kt nh sau: 1. MANAGES: l kiu lin kt 1:1 gia hai kiu thc th EMPLOYEE v

DEPARTMENT. Lc lng tham gia kin kt ca kiu thc th EMPLOYEE l b phn, v khng phi nhn vin no cng tham gia qun l. Cn lc lng tham gia ca DEPARTMENT l ton b, v ti bt k thi im no mt phng cng c mt nhn vin lm qun l. Thuc tnh StartDate c gn vo kiu lin kt ghi li thi im bt u lm qun l ca nhn vin cho phng .

2. WORKS_FOR: l kiu lin kt 1:N gia hai kiu thc th DEPARTMENT v EMPLOYEE. C hai kiu thc th ny u c lc lng tham gia ton b vo lin kt.

3. CONTROLS: l kiu lin kt 1:N gia hai kiu thc th DEPARTMENT v PROJECT. Lc lng tham gia ca PROJECT l ton b, ca DEPARTMENT l b phn.

STUDENT CLASS have

StartDate Name

STUDENT CLASS FACULTYhave have

Name StartDate

N 1N 1

N 1


PHN I C S D LIU 34

4. SUPERVISOR: l kiu lin kt 1:N gia hai kiu thc th EMPLOYEE v EMPLOYEE (Mi nhn vin c ngi qun l cp trn ca mnh, ngi cng l mt nhn vin). Trong qu trnh phng vn cc i tng ngi dng, ngi thit k c tr li rng: Khng phi nhn vin no cng lm qun l nhn vin khc, v khng phi nhn vin no cng c ngi qun l trc tip mnh. V vy, c hai kiu thc th ny c lc lng tham gia b phn.

5. WORK_ON: l kiu lin kt M:N gia hai kiu thc th EMPLOYEE v PROJECT, v mt d n c nhiu nhn vin lm vic v mt nhn vin c th lm vic cho nhiu d n. Thuc tnh Hours l thuc tnh ca kiu lin kt c dng ghi li s gi mi nhn vin lm vic cho mt d n no . C hai kiu thc th ny c lc lng tham gia ton b.

6. DEPENDENTS_OF: l kiu lin kt 1:N gia hai kiu thc th EMPLOYEE v DEPENDENT. Kiu thc th DEPENDENT l kiu thc th yu, v n khng tn ti nu khng c s tn ti ca kiu thc th EMPLOYEE. Lc lng tham gia ca EMPLOYEE l b phn, v khng phi nhn vin no cng c ngi ph thuc. Lc lng tham gia ca DEPENDENT l ton b v n l kiu thc th yu. c. Qua bc 4 ta v c m hnh ER:

Hnh 3.13. M hnh ER ca bi ton COMPANY 3.9.3 Bi tp Xy dng m hnh ER qun l cc n v sau:


PHN I C S D LIU 35

Bi tp 1: Qun l hot ng ca mt trung tm i hc Qua qu trnh kho st, iu tra hot ng ca mt trung tm i hc ta rt ra

cc quy tc qun l sau: - Trung tm c chia lm nhiu trng v mi trng c 1 hiu

trng qun l nh trng. - Mt trng chia lm nhiu khoa, mi khoa thuc v mt trng. - Mi khoa cung cp nhiu mn hc. Mi mn hc thuc v 1 khoa

(thuc quyn qun l ca 1 khoa). - Mi khoa thu nhiu gio vin lm vic. Nhng mi gio vin ch

lm vic cho 1 khoa. Mi khoa c 1 ch nhim khoa, l mt gio vin.

- Mi gio vin c th dy nhiu nht 4 mn hc v c th khng dy mn hc no.

- Mi sinh vin c th hc nhiu mn hc, nhng t nht l mn. Mi mn hc c th c nhiu sinh vin hc, c th khng c sinh vin no.

- Mt khoa qun l nhiu sinh vin ch thuc v mt khoa. - Mi gio vin c th c c lm ch nhim ca lp, lp c th

c nhiu nht 100 sinh vin. Bi tp 2: Cho cc thuc tnh, cc quy tc qun l ca mt n v. 1. Thuc tnh:

- M n v, Tn n v, S in thoi n v, a ch n v. - M nhn vin, Tn nhn vin, Gii tnh nhn vin, a ch nhn vin,

S in thoi ca nhn vin. - M d n, Tn d n - M khch hng, tn khch hng, a ch khch hng, S in thoi

ca khch hng. - M hng, Tn hng, S lng trong kho. - Lng t hng, Ngy t hng

2. Cc quy tc - Mt n v thu 1 hoc nhiu nhn vin - Mt n v c qun l bi 1 ngi qun l. l mt nhn vin. - Mt nhn vin ch lm vic cho 1 n v - Mt nhn vin c th lm vic cho 1 d n - Mi d n c th thu 1 hoc nhiu nhn vin


PHN I C S D LIU 36

- Mt nhn vin c th phc v cho 1 hoc nhiu khch hng - Mt khch hng c th c 1 hoc nhiu nhn vin phc v - Mt khch hng c th t 1 hoc 1 vi hng ha (Khch hng no

cng t hng: 1 hoc nhiu mt hng) - Mi mt hng u c t nht mt khch hng t mua - Mt n t hng ch c 1 mt hng.

Chng 4. M HNH C S D LIU QUAN H

PHN I C S D LIU 37

4 Chng 4. M HNH C S D LIU QUAN H 4.1 Khi nim m hnh quan h M hnh CSDL quan h ln u tin c E.F.Codd v tip sau c cng

ty IBM gii thiu vo nm 1970. Ngy nay, hu ht cc t chc p dng CSDL quan h qun l d liu trong n v mnh.

M hnh c s d liu quan h l cch thc biu din d liu di dng bng hay cn gi l quan h, m hnh c xy dng da trn c s l thuyt i s quan h.

Cu trc d liu: d liu c t chc di dng quan h hay cn gi l bng. Thao tc d liu: s dng nhng php ton mnh (bng ngn ng SQL).

4.2 Cc thnh phn c bn ca m hnh 4.2.1 Mt s khi nim ca m hnh quan h M hnh quan h l cch thc biu din d liu di dng cc quan h (cc

bng). Mt quan h l mt bng d liu 2 chiu (ct v dng), m t mt thc th. Mi ct tng ng vi mt thuc tnh ca thc th. Mi dng cha cc gi tr d liu ca mt i tng c th thuc thc th

Mt s khi nim c bn: Lc quan h: R(A1,,An), trong R l tn quan h, Ai l cc thuc tnh,

mi Ai c min gi tr tng ng dom(Ai). Lc quan h c s dng m t mt quan h, bao gm: Tn quan h,

cc thuc tnh v bc ca quan h (s lng cc thuc tnh) 4.2.2 Quan h: Mt quan h r ca R(A1,...,An), k hiu r(R) l mt tp hp n-b r={ t1, ..., tm}

Trong : Mi ti =, vi dom(Ai). r(R) dom(A1) x .... x dom(An) r = { (vi1,vi2,...,vin) / i=1,...,m} v11 v12 V1n

v21 v22 v2n ...... vm1 vm2 vmn A1 A2 Am

Ta c Ai l cc thuc tnh v min gi tr ca Ai l: D1=dom(A1), D2=dom(A2),...., Dn=dom(An). Ch : - Cc tp (D1,D2,...,Dn) l tp cc min tr ca R

- n c gi l bc ca quan h r. - m c gi l lc lng ca r. - Quan h bc 1 l quan h nht nguyn, bc 2 l quan h nh nguyn, bc n

l quan h n nguyn.


PHN I C S D LIU 38

V d: Quan h EMPLOYEE trn tp cc thuc tnh R={SSN, Name, BDate, Address, Salary} l mt quan h 5 ngi.

SSN Name BDate Address Salary 001 Hong Minh 1960 H ni 425 t1 002 Nh Mai 1970 Hi Phng 390 t2 003 ng Hong Nam 1973 H ni 200 t3

t1(001, Hong Minh, 1960, H ni , 425) = t1(R) l mt b ca quan h EMPLOYEE

4.2.3 Cc tnh cht ca mt quan h - Gi tr a vo ct l n nht - Cc gi tr trong cng mt ct phi thuc cng mt min gi tr (cng kiu) - Th t dng ct tu . 4.2.4 Cc rng buc ton vn trn quan h Rng buc l nhng quy tc c p t ln trn d liu m bo tnh tin cy v

chnh xc ca d liu. Cc lut ton vn c thit k gi cho d liu ph hp v ng n.

C 4 kiu rng buc chnh: Rng buc min gi tr (Domain Constraints), Rng buc kho (Key Constraints), Rng buc thc th (Entity Integrity Constraints), v Rng buc ton vn tham chiu (Referential Integrity Constraints).

4.2.4.1 Rng buc min gi tr L mt hp cc kiu d liu v nhng gi tr gii hn m thuc tnh c th nhn

c. Thng thng vic xc nh min gi tr ca cc thuc tnh bao gm mt s cc yu cu sau: Tn thuc tnh, Kiu d liu, di d liu, khun dng ca d liu, cc gi tr gii hn cho php, ngha, c duy nht hay khng, c cho php gi tr rng hay khng.

4.2.4.2 Rng buc kho

4.2.4.2.1 Kha chnh (Primary Key) Kha chnh l mt (hoc mt tp) cc thuc tnh ng vai tr l ngun ca mt

ph thuc hm m ch ln lt l cc thuc tnh cn li. V d: R={SSN, Name, BDate, Address, Salary} SSN Name, BDate, Address, Salary

(Ngun) (ch) Ta thy, t SSN ta c th suy ra ton b cc thuc tnh ng. Vy SSN c gi

l kha chnh. Mt s gi khi chn kha:

- Kha khng nn l tp hp ca qu nhiu thuc tnh. Trong trng hp kha c nhiu thuc tnh, c th thm mt thuc tnh nhn to thay chng lm kha chnh cho quan h.


PHN I C S D LIU 39

- Nu kha chnh c cu thnh t mt s thuc tnh, th cc thnh phn nn trnh s dng thuc tnh c gi tr thay i theo thi gian: nh tn a danh, phn loi.

4.2.4.2.2 Kha d tuyn (Candidate Key) Trong tp hp cc thuc tnh ca mt bng, c th c nhiu thuc tnh c th

dng c lm kha chnh. Cc thuc tnh c gi l kha d tuyn. Kha d tuyn cn tha mn 2 tnh cht sau:

- Xc nh duy nht. - Khng d tha: Khi xa i bt k mt thuc tnh no ca kha u ph hy tnh

xc nh duy nht ca kha.

4.2.4.2.3 Kha ngoi (Foreign Key) Trong nhiu trng hp, kha chnh ca mt bng c a sang lm thuc

tnh bn bng khc, thuc tnh gi l kha ngoi. Kha ngoi ng vai tr th hin lin kt gia 2 bng.

4.2.4.2.4 Kha ph (Second Key) ng vai tr khi ta mun sp xp li d liu trong bng. V d: Ta c bng SINHVIEN (MaSV, Hoten, GioiTinh, Diem). Mun sp xp li danh sch sinhvin theo th t a, b, c.. ca H tn. Khi

thuc tnh Hoten c gi l kha ph.

4.2.4.3 Rng buc thc th Mi mt lc quan h R, chng ta phi xc nh kho chnh ca n. Kho

chnh trong lc quan h c gch chn pha di ca thuc tnh. Sau y l danh sch cc lc quan h trong c s d liu COMPANY sau

khi xc nh rng buc thc th:

Hnh 4.1. Lc c s d liu COMPANY


PHN I C S D LIU 40

Hnh 4.2. Mt th hin ca c s d liu COMPANY Lu : Rng buc kho v rng buc thc th c xc nh cho tng quan h.


PHN I C S D LIU 41

4.2.4.4 Rng buc ton vn tham chiu Mt b gi tr trong mt quan h tham chiu ti mt b gi tr tn ti trong

mt quan h khc. Rng buc ton vn tham chiu phi xc nh trn 2 quan h: quan h tham

chiu (referencing relation) v quan h c tham chiu (referenced relation). Rng buc ton vn tham chiu cn c gi l rng buc kho ngoi. V d: Thuc tnh DNo ca quan h EMPLOYEE tham chiu ti thuc tnh

DNumber ca quan h DEPARTMENT.

Hnh 4.3. Cc rng buc tham chiu trong c s d liu COMPANY

4.2.5 Cc php ton trn CSDL quan h 4.2.5.1 Php ton cp nht a. Php chn (INSERT): L php b xung thm mt b vo quan h r cho

trc.

R1 FK

PK R2 referencing relation

referenced relation


PHN I C S D LIU 42

+ Biu din: INSERT(r; A1=d1,A2=d2,...,An=dn) vi Ai l thuc tnh, di thuc dom(Ai), i=1,..,n.

Nu th t cc trng l c nh, c th biu din php chn di dng khng tng minh INSERT(r; d1,d2,..., dn).

+ V d : Chn thm mt b t4=(004, Hong Thanh Vn,1969, H ni, 235) vo quan h EMPLOYEE(SSN, Name, BDate, Address, Salary) ta c th vit:

INSERT(EMPLOYEE; SSN= 004, Name= Hong Thanh Vn, BDate=1969, Address= H ni, Salary=235).

+ Ch : Kt qu ca php chn c th gy ra mt s sai st l : - B mi c thm khng ph hp vi lc quan h cho trc - Mt s gi tr ca mt s thuc tnh nm ngoi min gi tr ca thuc tnh . - Gi tr kho ca b mi c th l gi tr c trong quan h ang lu tr. b. Php loi b (DEL): L php xo mt b ra khi mt quan h cho trc. - Biu din : DEL(r; A1=d1,A2=d2,...,An=dn) hay DEL((r, d1,d2,..., dn). Nu K=(E1,E2,...,Em) l kho th c th vit DEL(r; E1=e1,E2=e2,...,Em=em) - V d : + xo b t1 ra khi quan h r: DEL(EMPLOYEE; SSN= 004, Name= Hong Thanh Vn, BDate=1969,

Address= H ni, Salary=235). + Cn loi b mt nhn vin trong quan h EMPLOYEE m bit SSN l

004 th ch cn vit: DEL(EMPLOYEE; SSN= 004) c. Php cp nht (UPDATE): L php tnh dng sa i mt s gi tr no

ti mt s thuc tnh. + Biu din : UPD (r; A1=d1,A2=d2,...,An=dn; B1=b1,B2=b2,...,Bk=bk) Vi {B1,B2,...,Bk} l tp cc thuc tnh m ti cc gi tr ca b cn thay

i. {B1,B2,...,Bk} ng vi tp thuc tnh {A1,A2,...,An} Hay UPD(r; E1=e1,E2=e2,...,Em=e; B1=b1,B2=b2,...,Bk=bk) vi K=(E1,E2,...,Em)

l kho. + V d : thay i tn nhn vin c SSN= 003 trong quan h EMPLOYEE

thnh Nguyn Thanh Mai ta c th vit : CH (EMPLOYEE; SSN= 03; Name= Nguyn Thanh Mai)

4.2.5.2 Php ton i s quan h i s quan h gm mt tp cc php ton tc ng trn cc quan h v cho kt

qu l mt quan h. C 8 php ton c chia lm 2 nhm : Nhm cc php ton tp hp (hp,

giao, tr, tch cc), nhm cc php ton quan h ( chn, chiu, kt ni, chia).


PHN I C S D LIU 43

nh ngha : Hai quan h r v s c gi l kh hp nu chng c xc nh trn cng mt tp cc min gi tr (C ngha l chng c xc inh trn cng mt tp cc thuc tinh).

a. Php hp: - Php hp ca hai quan h kh hp r U s = {t / t thuc r hoc t thuc s} V d: - Php hp ca hai quan h l php gp cc b ca hai bng ca mt quan h

thnh mt bng v b i cc b trng. V d: EMPLOYEE1 EMPLOYEE2

EMPLOYEE1 EMPLOYEE2 = EMPLOYEE3

Hnh 4.4. Minh ha d liu php hp 2 quan h b. Php giao - Php giao ca hai quan h kh hp r s ={t / t thuc r v t thuc s} V d : - Php giao ca hai quan h l ly ra cc b cng c mt c hai bng ca mt

quan h. V d: EMPLOYEE1 EMPLOYEE2 = 002, Thin, P002 c. Php tr - Php tr ca hai quan h kh hp r - s = {t / t thuc r v t khng thuc s} V d :

r - s ( A, B , C ) a2 b2 c2

SSN Name DNo 001 Hong P001 002 Thin P002 003 Huy P001 004 Thin P003

r ( A, B , C ) s( A, B , C ) r U s ( A, B , C ) a1 b1 c1 a1 b1 c1 a1 b1 c1 a2 b2 c2 a1 b2 c2 a2 b2 c2 a1 b2 c2

SSN Name DNo 001 Hong P001 002 Thin P002

SSN Name DNo 003 Huy P001 002 Thin P002 004 Thin P003

r s ( A, B , C ) a1 b1 c1


PHN I C S D LIU 44

- Php tr ca hai quan h A v B l ly cc b c trong bng A m khng c trong bng B.

V d: EMPLOYEE1 - EMPLOYEE2 = 001, Hong, P001 EMPLOYEE2 - EMPLOYEE1 d. Php tch cc : - Cho quan h r(R), R={A1,A2,...,An} v quan h s(U), U={B1,B2,...,Bm} - Tch cc : r x s ={t=(a1,a2,...,an, b1,b2,...,bm) /a1,a2,...,an r v b1,b2,...,bm s} V d : - Ch : Bc k = bc r + bc s , lc lng k = lc lng r x lc lng s Php tch cc l php ton t nht trong cc php ton ca i s quan h. e. Php chn (ct ngang) - mt ngi - L php ton lc ra mt tp con cc b ca quan h cho theo biu thc

chn F. - Biu thc chn F l mt t hp logic cc ton hng, mi ton hng l mt

php so snh n gin gia hai thuc tnh hoc gia mt thuc tnh v mt gi tr hng.

- Php ton logic: AND (v), OR (hoc), NOT (ph nh). - Php ton so snh : , =, >=,


PHN I C S D LIU 45

Bc r = bc r'; lc lng ca r >= lc lng ca r' -Php chn trn quan h l ly ra cc dng ca bng quan h tho mn mt iu

kin no trn tp cc ct thuc tnh. V d : Chn trn quan h EMPLOYEE3 cc nhn vin thuc phng c

DNo=P001 f. Php chiu (ct dc ) - 1 ngi - L php ton loi b i mt s thuc tnh v ch gi li mt s thuc tnh c

ch ra ca mt quan h. - Cho quan h r(R), X l tp con ca tp thuc tnh R. Php chiu ca quan h r

trn X : X(r) = { t[X]/ thuc r}; t[X] l b t ly trn tp thuc tnh X. V d : Cho r(A,B) nh trn , X={A}; - Bc ca r > bc ca k. Lc lng ca r > lc lng ca k - Php chiu trn quan h l ly mt s ct (thuc tnh) no ca bng quan

h. V d : Ly danh sch m NV ca quan h NHANVIEN

SSN (EMPLOYEE3) = g. Php kt ni - 2 ngi 1. Php kt ni - Cho hai quan h r(R), R={A1,A2,...,An} v quan h s(U), U={B1,B2,...,Bm}. - Php xp cnh nhau: cho hai b d = (d1,d2,...,dn) v e = (e1,e2,...,em) php xp

cnh nhau ca d v e l : (d^e) = (d1,d2,...,dn, e1,e2,...,em) - Php kt ni gia quan h r c thuc tnh A v quan h s c thuc tnh B vi

mt php so snh l : r >< s = {a^b / a thuc r, b thuc s v a(A) b(B)}

SSN Name DNo 001 Hong P001 003 Huy P001

SSN 001 002 003 004

X(r) = u(A) a1 a2 a3


PHN I C S D LIU 46

V d : Xt quan h r v s trong v d php tch cc - Lc lng ca php kt ni k' < s) C=C

SSN Name DNo 001 Hong P001 002 Thin P002 003 Huy P001 004 Thin P003

DNo DName P001 T chc P002 Kinh doanh P003 Nhn s P004 Tip th

SSN Name DNo DName 001 Hong P001 T chc 002 Thin P002 Kinh doanh 003 Huy P001 T chc 004 Thin P003 Nhn s

k' = B>=C (k) r >< s = F(r x s) F

r (A , B ) ; s(C , D) ; r x s = k (A, B, C, D) a1 1 1 d1 (B>=C) a1 1 1 d1 a2 2 2 d2 a1 1 2 d2 a3 3 a2 2 2 d2 a3 3 1 d1

a3 3 2 d2


PHN I C S D LIU 47

h. Php chia - Cho r l mt quan h n- ngi, s l quan h m- ngi (n>m, s khc rng). Php

chia quan h r cho quan h s l tp tt c cc n-m b t sao cho vi mi b u thuc s th b (t^u) thuc r : r s = {t / vi mi u thuc s th (t^u) thuc r}

V d :

- V d vi hai quan h : PRODUCT v SUPPORT

Cung cp Mt hng =

Hnh 4.6. Minh ha d liu php chia 2 quan h

r(A, B, C, D) s(C, D) r s (A, B) a b c d c d a b a b e f e f e d b c e f e d c d e d e f a b d e

PNo PName h1 i h2 TV h3 t lnh

SNo PNo PName n1 h1 i n1 h2 TV n1 h3 T lnh n2 h3 T lnh n2 h1 i n3 h1 i n3 h2 TV n3 h3 T lnh n4 h1 i SNo

n1 n3

Chng 5. CHUYN T M HNH ER SANG M HNH QUAN H

PHN I C S D LIU 48

5 Chng 5. CHUYN T M HNH ER SANG M HNH QUAN H

Nh chng ta bit, m hnh ER l m hnh d liu mc khi nim. Sau qu trnh kho st thit k, ta thu c m hnh ny. T m hnh ny ta c th s dng cc quy tc chuyn sang m quan h thc hin qun l c s d liu trn my tnh.

Sau y l 8 bc c s dng chuyn t m hnh ER sang m hnh quan h:

Bc 1: Mi kiu thc th bnh thng (khng phi kiu thc th yu) trong m hnh ER tr thnh mt quan h. Quan h bao gm tt c cc thuc tnh n gin v thuc tnh t hp ca thc th. Thuc tnh nh danh ca thc th l kha chnh ca quan h. V d: Kiu thc th: EMPLOYEE, DEPARTMENT, PROJECT ==> quan h: EMPLOYEE, DEPARTMENT, PROJECT

Quan h: EMPLOYEE (Ssn, fname, minit, lname, bdate, sex, address, salary)

Quan h: DEPARTMENT ( Dnumber, Dname) Lu : Thuc tnh Locations khng c trong quan h v n l thuc tnh a tr.

Quan h: PROJECT (Pnumber, Pname, Plocation)

DEPARTMENT

Name

Number Locations

EMPLOYEE

Bdate

SSN Name

Sex Address

Salary

Fname Mint Lname

PROJECT

Number

Name Location


PHN I C S D LIU 49

Bc 2: Cho mi thc th yu (Weak Entity) trong m hnh ER, to thnh mt quan h R, tt c thuc tnh n gin ca thc th yu tr thnh thuc tnh ca R. Thm vo , thuc tnh nh danh ca thc th ch tr thnh kha ngoi ca R.

Kho chnh ca R l s kt hp gia thuc tnh nh danh ca thc th ch v thuc tnh nh danh ca thc th yu.

V d:

DEPENDENT (Essn, Dependent_name, sex, bdate, relationship) Bc 3: Cho mi mi lin kt 1-1 trong m hnh ER:

- Xc nh mt quan h S_T. Kiu thc th c s tham gia ton b vo lin kt tr thnh quan h S, thc th cn li tr thnh quan h T.

- a kha chnh ca T sang lm kha ngoi ca S. - Thuc tnh ca mi quan h S_T tr thnh thuc tnh ca S.

V d:

DEPARTMENT(, MGRSSN, MGRSTARTDATE)

EMPLOYEE

SSN

dependents_of

DEPENDENT

Name RelationshipBirthDateSex

1

N

EMPLOYEE DEPARTMENTmanages

SSN

StartDate

1 1


PHN I C S D LIU 50

Bc 4: Cho mi mi lin kt 1_N trong m hnh ER. Chuyn kha chnh ca quan h pha 1 sang lm kha ngoi ca quan h pha N.

V d 1:

EMPLOYEE(, DNO)

V d 2:

EMPLOYEE(, SuperSSN) Bc 5:Cho mi mi lin kt MN, sinh ra mt quan h mi R, chuyn kha

chnh ca hai quan h pha M v N thnh kha ngoi ca quan h R. Kha chnh ca R l s kt hp ca hai kha ngoi.

V d:

WORKS_ON( PNO, ESSN, Hours) Bc 6: Nu gp thuc tnh a tr:

- Chuyn thuc tnh a tr thnh quan h mi. - Thuc tnh nh danh (hoc 1 phn thuc tnh nh danh) ca thc th

chnh chuyn thnh kha ngoi ca quan h mi.

EMPLOYEE DEPARTMENTworks_for

SSNN 1

DNO

SupervisorEMPLOYEE

SSN1 N

EMPLOYEE PROJECTworks_on

SSN

Hours

M N

Number


PHN I C S D LIU 51

- Kha chnh ca quan h mi l kha chnh ca bn thn quan h + kha ngoi do thc th chnh chuyn sang.

V d:

DEPT_LOCATIONS ( DNumber, DLocation)

Bc 7: Cho mi mi lin kt c bc (>2), to ra quan h mi (R), kha chnh ca cc

quan h tham gia lin kt c a lm kha ngoi ca quan h R v cc kha ngoi ny ng thi ng vai tr l kha chnh ca R.

V d:

SUPPLY (SName, ProjName, PartNo, Quantity) Bc 8: X l quan h gia lp cha/ lp con v chuyn bit ho hoc tng qut ho.

Cc la chn khc nhau cho vic chuyn i mt s lng cc lp con t cng mt chuyn bit (hoc tng qut ho thnh lp cha). Ngoi 7 bc trnh by trn trong bc 8 di y a ra mt la chn ph bin nht v cc iu kin m mi la chn c th s dng. S dng k hiu Attr(R) biu th cc thuc tnh ca R v PK(R) l kho chnh ca R.

Cch thc hin: Chuyn i mi chuyn bit ho c:

- m lp con { S1, S2 , , Sm} v lp cha C, thuc tnh ca C l { k, a1 , a2 , , an} v k l kho chnh thnh nhng lc quan h, chng ta c th s dng mt trong 4 la chn sau:

1. La chn 8A:

DEPARTMENT

Name

NumberLocations

SUPPLIER PROJECTSUPPLY

snamequantity projname

PART partno


PHN I C S D LIU 52

- To quan h L cho lp cha C vi cc thuc tnh Attrs(L)={k, a1, , an} v kho chnh ca L l: PK(L)=k. - To quan h Li cho mi lp con tng ng Si vi cc thuc tnh Attrs(Li)={k} U {thuc tnh ca Si} v PK(Li)=k.

V d:

Chuyn chuyn bit ho trn thnh cc quan h sau: EMPLOYEE(SSN, FName, Minit, LName, BirthDate, Address, Salary) SECRETARY(SSN, TypingSpeed) TECHNICIAN(SSN, TGrade) ENGINEER(SSN, EngType) 2. La chn 8B:

To mt quan h Li cho mi lp con Si , vi cc thuc tnh Attr(Li) = {k, a1, a2,, am} U {thuc tnh ca Si} v PK(Li) = k.

V d:

EMPLOYEE

SECRETAR TECHNICIAN ENGINEER

FName Minit LName

Name Ss BirthDate Address

Typing Speed TGrade EngType

d

Salary

Job Type

EngineerSecretary

Technician


PHN I C S D LIU 53

Chuyn chuyn bit ho trn thnh cc quan h sau: CAR(Vehicleld, LicensePlateNo, Price, MaxSpeed, NoOfPassengers) TRUCK(Vehicleld, LicensePlateNo, Price, NoOfAxles, Tonnage)

3. La chn 8C:

To mt quan h L vi cc thuc tnh Attr(L) = {k, a1 , a2 , , an } U {thuc tnh ca S1} U U {thuc tnh ca Sm} U {t} v PK(L) = k. Trong , t l thuc tnh phn bit ch ra bn ghi thuc v lp con no, v th min gi tr ca t ={1,2,,m}.

V d: i vi chuyn bit ho ca EMPLOYEE, ta ch to ra mt quan h L nh sau: EMPLOYEE(SSN, FName, Minit, LName, BirthDate, Address, Salary, TypingSpeed, Tgrad, EngType, JobType) 4. La chn 8D:

To mt quan h L vi cc thuc tnh Attr(L) = {k, a1 , a2 , , an } U {thuc tnh ca S1} U U {thuc tnh ca Sm} U {t1 , t2 , , tmj} v PK(L) = k. La chn ny cho chuyn bit ho ca cc lp con c np chng (nhng cng p dng cho mt chuyn bit tch ri), v vi mi ti, 1 i m, l thuc tnh BOOLEAN ch ra b theo lp con Si.

V d:

LicensePlateNOPriceVehicleld

NoOfPassengers

MaxSpeed

NoOfAxles

Tonnage

VEHICLE

CAR TRUCK

d

Thuc tnh phn bit


PHN I C S D LIU 54

PART (ParNo, Description, Mflag, DrawingNo, ManufactureDate, BatchNo, PFlag, SupplierName, ListPrice)

PART

MANUFACTURED_PART PURCHASED_PART

PartNo Description

DrawingNo BatchNo ListPrice

o

ManufactureDateSupplierName

Thuc tnh phn bit

Chng 6. PH THUC HM V CHUN HO QUAN H

PHN I C S D LIU 55

6 Chng 6. PH THUC HM V CHUN HO QUAN H

6.1 Mt s hng dn khi thit k c s d liu quan h Vic quan trng nht khi thit k c s d liu quan h l ta phi chn ra tp

cc lc quan h tt nht da trn mt s tiu ch no . V c c la chn tt, th chng ta cn c bit quan tm n mi rng buc gia cc d liu trong quan h, chnh l cc ph thuc hm.

hiu hn v cu hi ti sao phi thit k mt c s d liu tt, chng ta hy cng tm hiu v d sau:

RESULT(StNo, StName, SubNo,SubName, Credit, Mark) Quan h RESULT( Kt qu hc tp) c cc thuc tnh: StNo(M sinh vin),

StName(Tn sinh vin), SubNo(M mn hc), SubName(Tn mn hc), Credit (S n v hc trnh) v Mark (im thi ca sinh vin trong mn hc).

Sau y l minh ho d liu ca quan h RESULT:

StNo StName SubNo SubName Credit Mark

St01 Mai Sub04 CSDL 3 9

St01 Mai Sub01 TRR 5 10

St01 Mai Sub02 PPS 4 8

St02 Vn Sub04 CSDL 3 10

St02 Vn Sub01 TRR 5 9

St03 Thanh Sub07 Ting Anh 4 8

Hnh 6.1. Minh ha d liu ca quan h RESULT

Quan h trn thit k cha tt v: 1. D tha d liu (Redundancy): Thng tin v sinh vin v mn hc b lp

li nhiu ln. Nu sinh vin c m St01 thi 10 mn hc th thng tin v sinh vin ny b lp li 10 ln, tng t i vi mn hc c m Sub04, nu c 1000 sinh vin thi th thng tin v mn hc cng lp li 1000 ln

2. Khng nht qun (Inconsistency): L h qu ca d tha d liu. Gi s sa bn ghi th nht, tn sinh vin c cha thnh Nga th d liu ny li khng nht qun vi bn ghi th 2 v 3 (vn c tn l Mai).

3. D thng khi thm b (Insertion anomalies): Nu mun thm thng tin mt sinh vin mi nhp trng (cha c im mn hc no) vo quan h th khng c v kho chnh ca quan h trn gm 2 thuc tnh StNo v SubNo.


PHN I C S D LIU 56

4. D thng khi xo b (Deletion anomalies): Gi s xo i bn ghi cui cng, th thng tin v mn hc c m mn hc l SubNo=Sub07 cng mt.

Nhn xt: Qua phn tch trn, ta thy chng ta nn tm cch tch quan h trn thnh cc quan h nh hn.

Trong chng ny chng ta s nghin cu v nhng khi nim v cc thut ton c th thit k c nhng lc quan h tt.

6.2 Ph thuc hm(Functional Dependencies) - Ph thuc hm (FDs) c s dng lm thc o nh gi mt

quan h tt. - FDs v kho c s dng nh ngha cc dng chun ca quan

h. - FDs l nhng rng buc d liu c suy ra t ngha v cc mi

lin quan gia cc thuc tnh. 6.2.1 nh ngha ph thuc hm Cho r(U), vi r l quan h v U l tp thuc tnh.

Cho A,B U, ph thuc hm X Y (c l X xc nh Y) c nh ngha l: t, t r nu t.X = t.X th t.Y = t.Y (C ngha l: Nu hai b c cng tr X th c cng tr Y) Ph thuc hm c suy ra t nhng quy tc d liu khi ta kho st yu cu ca

bi ton. V d:

T m s bo him x hi, ta c th suy ra c tn ca nhn vin (Ssn Ename).T m d n, ta c th suy ra tn v v tr ca d n (PNumber{PName, PLcation})

Hnh 6.2. Biu din FDs ca 2 lc quan h EMP_DEPT v EMP_PROJ


PHN I C S D LIU 57

6.2.2 H tin Armstrong Cho lc quan h r(U), U l tp thuc tnh, F l tp cc ph thuc hm c

nh ngha trn quan h r. Ta c ph thuc hm A B c suy din logic t F nu quan h r trnU

tha cc ph thuc hm trong F th cng tha ph thuc hm A B. V d:

Tp ph thuc hm: F = { A B, B C} Ta c ph thuc hm A C l ph thuc hm c suy t F.

H tin Armstrong c s dng tm ra cc ph thuc hm suy din t F. H tin Armstrong bao gm:

1. Phn x: Nu Y X th X Y 2. Tng trng: Nu Z U v X Y th XZ YZ (K hiuXZ l XZ) 3. Bc cu: Nu X Y v Y Z th X Z 4. Gi bc cu: Nu X Y v WY Z th XW Z 5. Lut hp: Nu X Y v X Z th X YZ

6. Lut phn r: Nu X Y v Z Y th X Z Trong su lut trn th a4, a5, a6 suy c t a1, a2, a3. 6.2.3 Bao ng ca tp ph thuc hm - Ta gi f l mt ph thuc hm c suy dn t F, k hiu l F f nu tn ti mt chui ph thuc hm: f1, f2,., fn sao cho fn=f v mi fi l mt thnh vin ca F hay c suy dn t nhng ph thuc hm j=1,,i-1 trc nh vo lut dn. - Bao ng ca F: k hiu l F+ l tp tt c cc ph thuc hm c suy t F nh vo h tin Armstrong. F+ c nh ngha:

FF ++ == {{ XX YY || FF XX YY }} 6.2.4 Bao ng ca tp thuc tnh X trn F Bao ng ca tp thuc tnh X xc nh trn tp ph thuc hm F k hiu l X+

l tp hp tt c cc thuc tnh c th suy ra t X. K hiu:

XX ++ == {{ YY || FF XX YY }} X+ c th c tnh ton thng qua vic lp i lp li c quy tc 1, 2, 3 ca h

tin Armstrong. Thut ton xc nh bao ng ca tp thuc tnh X+:

X+ := X; repeat


PHN I C S D LIU 58

oldX+ := X+; for (mi ph thuc hm Y Z trong F) do if Y X+ then X+ Z until (oldX+ = X+ );

V d: Cho tp ph thuc hm:

F = { SSNENAME, PNUMBER{PNAME, PLOCATION}, {SSN, PNUMBER} HOURS} Suy ra:

{SSN}+ = {SSN, ENAME}

{PNUMBER}+ = {PNUMBER, PNAME, PLOCATION}

{SSN, PNUMBER}+ = {SSN, PNUMBER, ENAME, PNAME, PLOCATION, HOURS}

Nh vy, tp thuc tnh {SSN, PNUMBER} l kho ca quan h. 6.2.5 Kho ca quan h

Cho quan h r(R), tp KR c gi l kha ca quan h r nu: K+=R v nu bt mt phn t khi K th bao ng ca n s khc R.

Nh th tp KR l kho ca quan h nu K+=R v ( K \A )+ R , AR. V d: ChoR = { A, B, C, D, E, G } v tp ph thuc hm:

F= { AB C , D EG , BE C , BC D , CG BD, ACD B, CE AG} Ta s thy cc tp thuc tnh: K1 = { A, B } , K2 = {B,E} , K3={C,G} , K4={C,E} , K5 = {C,D}, K6={B,C}

u l kha ca quan h. Nh vy, mt quan h c th c nhiu kha. Thut ton tm kho: tng: Bt u t tp U v Closure(U+,F) = U. Sau ta bt dn cc phn t ca U nhn c tp b nht m bao ng ca n vn bng U. Thut ton:

Input: Lc quan h r(U), tp ph thuc hm F. Output: Kho K Bc 1: Gn K = U Buc 2: Lp li cc bc sau: Loi phn t A khi K m Closure( K -A,F ) =U Nhn xt:


PHN I C S D LIU 59

- Thut ton trn ch tm c mt kha. Nu cn tm nhiu kha, ta thay i trt t loi b cc phn t ca K.

- Chng ta c th ci thin tc thc hin thut ton trn bng cch: Trong bc 1 ta ch gn K=Left (l tp cc phn t c bn tay tri ca cc ph thuc hm)

V d: Cho lc quan h R = { A,B,C,D,E,G,H,I} v tp ph thuc hm: F= { AC B, BI ACD, ABC D , H I , ACE BCG , CG AE } Tm kho K? Ta c Left={A,B,C,H,E,G} Bc 1: K=Left={A,B,C,H,E,G} Bc 2: Tp thuc tnh A B C D E G H I Ghi ch ABCHEG x x x x x x x x BCHEG x x x x x x x x Loi A CHEG x x x x x x x x Loi B CHG x x x x x x x x Loi E

Nh vy, {C,H,G} l mt kho ca R. Nu mun tm tt c cc kho ca R, ta cn thay i trt t loi b phn t ca

kho K. 6.2.6 Tp ph thuc hm tng ng Hai tp ph thuc hm F v G l tng ng nu:

- Tt c cc ph thuc hm trong F c th c suy ra t G, v - Tt c cc ph thuc hm trong G c th suy ra t F.

V th, F v G l tng ng nu F+ = G+ Nu F v G l tng ng th ta ni F ph G hay G ph F. V th, thut ton sau y s kim tra s tng ng ca hai tp ph thuc

hm:

- F ph E: XY E, tnh X+ t F, sau kim tra xem Y X+ - E ph F: XY F, tnh X+ t E, sau kim tra xem YX+

6.2.7 Tp ph thuc hm ti thiu Tp ph thuc hm l ti thiu nu n tho mn cc iu kin sau: 1. Ch c mt thuc tnh nm pha bn tay tri ca tt c cc ph thuc

hm trong F.


PHN I C S D LIU 60

2. Khng th b i bt k mt ph thuc hm no trong F m vn c c mt tp ph thuc hm tng ng vi F (tc l, khng c ph thuc hm d tha).

3. Khng th thay th bt k ph thuc hm XA no trong F bng ph thuc hm YA, vi YX m vn c c mt tp ph thuc hm tng ng vi F (tc l, khng c thuc tnh d tha trong ph thuc hm)

Nhn xt: - Tt c cc tp ph thuc hm u c ph thuc hm ti thiu tng

ng vi n. - C th c nhiu ph thuc hm ti thiu

Thut ton: Tm tp ph thuc hm ti thiu G ca F

1. t G:F. 2. Thay th tt c cc ph thuc hm X{A1,A2,,An} trong G bng n

ph thuc hm: X A1, X A2,, X An. 3. Vi mi ph thuc hm X A trong G, vi mi thuc tnh B trong X

nu ((G{X A}) {( X {B}) A} ) l tng ng vi G, th thay th X A bng (X {B}) A trong G. (Loi b thuc tnh d tha trong ph thuc hm)

4. Vi mi ph thuc hm X A trong G, nu (G{X A}) tng ng vi G, th loi b ph thuc hm X A ra khi G.(Loi b ph thuc hm d tha)

6.3 Cc dng chun ca quan h 6.3.1 nh ngha cc dng chun 6.3.1.1 Dng chun 1(First Normal Form)

a. nh ngha Mt quan h dng chun 1 nu cc gi tr ca tt c thuc tnh trong quan h

l nguyn t (tc l ch c 1 gi tr ti mt thi im). b. V d:

- Quan h sau y khng phi dng chun 1:


PHN I C S D LIU 61

Hnh 6.3. D liu ca quan h DEPARTMENT vi phm 1NF - Chuyn quan h trn thnh dng chun 1 (bng cch xc nh tp

thuc tnh {DNumber, DLocation} l kho chnh), ta c:

Hnh 6.4. D tha d liu trong quan h dng chun 1 c. Nhn xt:

- Quan h dng chun 1 c tn ti s d tha d liu, trong quan h DEPARTMENT, nu nh mt phng c nhiu a im khc nhau th d liu ca 3 thuc tnh (DName, DNumber, DMgrSsn) b lp li nhiu ln.

- Chng ta c th tch quan h DEPARTMENT thnh 2 quan h:

Hnh 6.5. Quan h DEPARTMENT c tch thnh 2 quan h M t d liu ca 2 quan h ny: DEPARTMENT:

DName DNumber DMgrSsn Research 5 333445555 Administration 4 987654321 Headquarters 1 888665555

Vi phm dng chun 1

D tha


PHN I C S D LIU 62

DEPT_LOCATIONS:

DNumber DLocation 5 Bellaire 5 Sugarland 5 Houston 4 Stafford 1 Houston

Hnh 6.6. Minh ha d liu ca DEPARTMENT v DEPT_LOCATIONS 6.3.1.2 Dng chun 2(Second Normal Form_2NF)

a. nh ngha: Mt quan h dng chun 2 nu:

- Quan h dng chun 1 - Tt c cc thuc tnh khng phi l kha ph thuc y vo

kha. - Ph thuc y : Ph thuc hm Y Z l ph thuc hm y

nu: AY, ( Y{A}) Z b. S m t:

R (A1, A2, A3, A4, A5, A6) c. V d: V d 1: Quan h EMP_PROJ khng phi dng chun 2 v tn ti 2 ph thuc hm FD2, FD3 l ph thuc hm b phn (tri vi ph thuc hm y )

Hnh 6.7. Lc quan h EMP_PROJ v cc ph thuc hm V d 2: Quan h sau y dng chun 2:

Vi phm chun 2


PHN I C S D LIU 63

Hnh 6.8. Quan h EMP_DEPT dng chun 2

Hnh 6.9. Minh ho d liu ca quan h EMP_DEPT V d 3: Quan h sau y dng 2NF:

THESIS (StudentNo, Subject, Teacher)

StudentNo Subject Teacher SV01 1 Nguyn Vn Hiu SV02 2 Ng Lan Phng SV03 1 Nguyn Vn Hiu SV04 1 Nguyn Vn Hiu

Hnh 6.10. Minh ho d liu ca quan h THESIS

d. Nhn xt: - Quan h dng chun 2 c s d tha thng tin.

DNumber khng phi l thuc tnhkho Quan h khng vi phm 2NF

D tha d liu


PHN I C S D LIU 64

- Dng chun 2 c th b vi phm khi quan h c kha gm hn mt thuc tnh.

6.3.1.3 Dng chun 3 (Third Normal Form) a. nh ngha

Mt quan h dng chun 3 nu: - Quan h dng chun 2 - V khng c cha cc ph thuc hm ph thuc bc cu vo

kho. - Ph thuc hm ph thuc bc cu: Ph thuc hm YZ l ph

thuc hm bc cu nu tn ti hai ph thuc hm:YX v X Z. b. Biu din bng s

R (A1, A2, A3, A4, A5, A6) c. V d: Quan h EMP_DEPT khng phi dng chun 3 v cn tn ti ph

thuc hm DNumberDName, DMgrSsn l ph thuc hm ph thuc bc cu vo kho.

Hnh 6.11. Quan h EMP_DEPT khng phi dng chun 3 Tch quan h trn thnh 2 quan h: EMPLOYEE v DEPARTMENT. 2 quan h sau u dng chun 3:

Hnh 6.12. Tch quan h EMP_DEPT thnh 2 quan h mi

Ph thuc hm bc cu

Ph thuc hm b phn


PHN I C S D LIU 65

Hnh 6.13. M t d liu ca quan h EMPLOYEE v DEPARTMET

d. Nhn xt: - Trong mt c s d liu tt, cc quan h nn c chuyn v dng chun 3. - Tuy nhin, d liu vn c kh nng d tha khi quan h c hai tp kha d

tuyn gi ln nhau, hoc quan h c thuc tnh khng kha xc nh mt thuc tnh kha .

6.3.1.4 Dng chun Boyce _Codd(Boyce-Codd Normal Form) a. nh ngha

Quan h R dng chun BCNF khi tt c cc ph thuc hm X A trong R u phi c X l kho ca R.

b. V d: Quan h sau dng 3NF nhng khng phi BCNF.

A, B: thuc tnh kho C: khng phi l thuc tnh kho

FD1FD2


PHN I C S D LIU 66

Hnh 6.14. Minh ho d liu ca quan h TEACH vi phm chun Boyce -Codd

nhn c quan h BCNF, ta c th tch quan h trn: Cch 1: R1(Student, Instructor) v R2(Student, Course) Cch 2: R1(Couse, Instructor} v R2(Course, Student) Cch 3: R1(Instructor, Course} v R2(Instructor, Student) Lu : Vic tch quan h nh trn s lm mt i ph thuc hm FD1. 6.3.2 Php phn r cc lc quan h 6.3.2.1 nh ngha Php phn r cc lc quan h R={A1, A2, . . . , An}l vic thay th lc

quan h R thnh cc lc con {R1, . . . , Rk}, trong RiR v R=R1 R2 Rk Vd: Cho quan h R vi cc ph thuc hm nh sau: R(MaSV, MaMH, TenMH, VHT, TenSV, Lop) Ta c th phn r thnh 3 lc R1(MaSV, TenSV, Lop) v

R2(MaMH,TenMH, VHT) v R3(MaSV, MaMH).

6.3.2.2 Php phn r khng mt mt thng tin

Cho R l mt lc quan h, php r =(R1,R2, . . .,Rn) v D l tp cc ph thuc d liu. Php phn r khng mt mt thng tin nu khi thc hin php ton kt ni t nhin cc quan h thnh phn R1, R2,,Rn ta vn nhn c kt qu ca quan h ban u.

V d v mt php phn r c mt mt thng tin: Cho quan h:


PHN I C S D LIU 67

MaSV MaMH iem 1 A 3 2 A 5 3 A 6 4 B 6 5 C 9

Nu ta phn r quan h trn thnh 2 quan h: R1(MaSV, MaMH) v R2(MaMH, iem) nh sau:

R1: R2:

MaSV MaMH 1 A 2 A 3 A 4 B 5 C

Thc hin php kt ni t nhin 2 quan h R1 v R2: R1*R2=

MaSV MaMH iem 1 A 3 1 A 5 1 A 6 2 A 3 2 A 5 2 A 6 3 A 3 3 A 5 3 A 6 4 B 6 5 C 9

Nh vy, khi ni t nhin 2 bng, ta nhn c quan h khng ging quan h ban u Php phn r trn l mt mt thng tin.

Vn t ra i vi ngi thit k l phi tm ra nhng php phn r khng lm mt mt thng tin (chi tit s c trnh by phn sau). By gi chng ta s tm hiu mt thut ton kim tra mt php phn r c mt mt thng tin hay khng.

6.3.2.3 Thut ton kim tra php phn r khng mt mt thng tin Input:

- Lc quan h R={A1, A2, . . . , An} - Tp cc ph thuc hm F

- Php tch (R1, R2, . . . , Rk)

MaMH iem A 3 A 5 A 6 B 6 C 9


PHN I C S D LIU 68

Output: Kt lun php tch khng mt mt thng tin. Cc bc ca thut ton: Bc 1:

- Thit lp mt bng vi n ct (tng ng vi n thuc tnh) v k dng (tng ng vi k quan h), trong ct th j ng vi thuc tnh Aj, dng th i ng vi lc Ri.

- Ti dng i v ct j, ta in k hiu aj nu thuc tinh AjRi.Ngc li ta in k hiu bij.

Bc 2: - Xt cc ph thuc hm trong F v p dng cho bng trn.

- Gi s ta c ph thuc hm XYF, xt cc dng c gi tr bng nhau trn thuc tnh X th lm bng cc gi tr ca chng trn Y. Ngc li lm bng chng bng k hiu bij. Tip tc p dng cc pth cho bng (k c vic lp li cc ph thuc hm p dng) cho ti khi khng cn p dng c na.

Bc 3: Xem xt bng kt qu. Nu xut hin mt dng cha ton gi tr a1, a2 ,,an

th kt lun php tch khng mt mt thng tin. Vi d: Cho quan h:

Hnh 6.15. Minh ha d liu ca quan h EMP_DEPT Tch quan h trn thnh 2 quan h:


PHN I C S D LIU 69

Hnh 6.16. Quan h EMPLOYEE c phn r (tch) thnh 2 quan h

Tp ph thuc hm F: Kim tra php tch trn l khng mt mt thng tin: Bc 1:

EName SSN BDate Address DNumber DName DMgrSsn

EMPLOYEE a1 a2 a3 a4 a5 b16 b17

DEPARTMENT b21 b22 b23 b24 a5 a6 a7

Bc 2: Xt ph thuc hm DNumber DName, DMgrSsn. Ta nhn thy c gi tr a5 dng th 2, nn ta s lm bng gi tr a6, a7 cho dng th 1.

Bc 3: Tn ti mt dng cha gi tr a1, a2,..a7. Kt lun, php phn r trn khng mt mt thng tin.


PHN I C S D LIU 70

EName SSN BDate Address DNumber DName DMgrSsn

EMPLOYEE a1 a2 a3 a4 a5 a6 a7

DEPARTMENT b21 b22 b23 b24 a5 a6 a7

Ghi ch: Sinh vin thc hin php ni t nhin 2 quan h EMPLOYEE v DEPARTMENT trn kim tra c bng quan h ban u EMP_DEPT

6.4 Chun ho quan h Chun ho quan h l vic phn r mt lc quan h thnh cc lc con

dng chun 3 hoc BCNF sao cho vn bo ton ph thuc v khng mt mt d liu. 6.4.1 Thut ton phn r lc quan h thnh cc lc quan

h con BCNF Input:

- Lc quan h R - Tp ph thuc hm F

Output: Php phn r ca R khng mt thng tin v mi lc quan h trong php

tch u dng BCNF i vi php chiu ca F trn lc . Cc bc ca thut ton:

- Ban u php tch ch bao gm R. - Nu S l mt lc thuc v S cha dng BCNF th chn ph

thuc hm X A tha trong S, trong X khng cha kha ca S v AX. {ph thuc hm vi phm nh ngha dng chun BCNF}. Thay th S trong bi S1 v S2 nh sau S1 = XA, S2 = S\A.

- Qu trnh trn tip tc cho n khi tt c cc lc quan h u dng BCNF

V d: Cho lc quan h R(CTHRSG). Trong :

- C: Course; T: Teacher; H: Hour; R: Room; S: Student; G:Group). - V tp cc ph thuc hm F:

o C T: Mi kho hc (course) c mt thy (teacher) duy nht. o HR C: Ti mt thi im (Hour) ti phng hc (room) ch c

mt kho hc duy nht.


PHN I C S D LIU 71

o HT R: Ti mt thi im v mt gio vin ch mt phng duy nht

o CSG: Mt sinh vin hc mt course th ch mt lp duy nht. o HS R: Mt sinh vin, mt thi im nht nh ch trong

mt phng duy nht. Da vo thut ton tm khoKha ca R l HS. Yu cu: Tch lc R thnh cc lc con dng BCNF.

Hnh 6.17. Biu din qu trnh tch quan h R thnh cc quan h BCNF Nh vy, quan h R c tch thnh 4 quan h R1, R21, R221, R222 u

BCNF.


PHN I C S D LIU 72

6.4.2 Thut ton phn r mt lc quan h thnh cc lc con 3NF.

Input: - Lc quan h R - Tp cc ph thuc hm F, khng lm mt tnh tng qut gi s l

ph ti thiu. Output: Php tch khng mt mt thng tin trn R thnh cc lc con dng chun

3 sao cho vn bo ton cc ph thuc hm. Cc bc ca thut ton:

- Bc 1: Loi b cc thuc tnh ca R nu thuc tnh khng lin quan n ph thuc hm no ca F.(khng c mt c hai v ca ph thuc hm).

- Bc 2: Nu c mt ph thuc hm ca F lin quan n tt c cc thuc tnh ca R th kt qu chnh l R.

- Bc 3: Ngoi ra, php tch a ra cc lc gm cc thuc tnh XA ng vi ph thuc hm XA F. Nu tn ti cc ph thuc hm XA1, XA2, ,XAn thuc F th thay th XAi (1


PHN I C S D LIU 73

Ph thuc hm HRC vi phm 3NF, tch R22 thnh R221(H,R,C) v R222(H,S,R)

Nh vy, quan h R c tch thnh cc quan h sau: R1, R21, R221, R222 Lu :

- Kt qu ca php tch c th khc nhau ph thuc vo th t p dng cc ph thuc hm khi thc hin thut ton.

- Sinh vin t kim tra xem vic tch quan h nh trn c mt mt thng tin khng.

Bi tp: 1. Cho mt quan h R ={A, B, C, D, E, F, G, H, I, J} v tp ph thuc hm F = { A,B C A D, E B F F G, H D I, J } Yu cu: - Tm {A}+ ={D, E, I ,J } - Tm kha ca quan h R. - Tch quan h R thnh BCNF. - Kim tra xem vic tch trn c mt mt thng tin khng? 2. Lp li yu cu bi 1 vi tp ph thuc hm sau: G= {A,B C B, D E, F A, D G, H A I HJ} 3. Cho mt quan h R ={CourseNo, SecNo, OfferingDept, Credit_Hours,

CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo, NoOfStudents} v tp ph thuc hm:

F ={ CourseNo OfferingDept, Credit_Hours, CourseLevel; CourseNo, SecNo, Semester, Year Days_Hours, RoomNo,

NoOfStudents, InstructorSSN; RoomNo, Days_Hours, Semester, Year InstructorSSN, CourseNo, SecNo }

Yu cu:


PHN I C S D LIU 74

- Tm kha ca quan h R. - Quan h trn thuc dng chun my? - Tch quan h v dng 3NF. - Kim tra xem vic tch trn c mt mt thng tin khng?

TI LIU THAM KHO

PHN I C S D LIU 75

7 Chng 7. THIT K C S D LIU VT L (Tham kho)

Thit k c s d liu vt l l qu trnh chuyn cc c t d liu lgic thnh cc c t k thut lu tr d liu. Gm 2 ni dung sau:

La chn cng ngh lu tr (H iu hnh, HQTCSDL, cc cng c truy nhp d liu).

Chuyn cc quan h ca m hnh logic thnh cc thit k vt l. Trong chng ny s trnh by nhng phn sau: Thit k cc trng, bn ghi vt l Thit k file vt l Thit k c s d liu vt l

7.1 Ni dung thit k file vt l v c s d liu vt l 7.1.1 Qu trnh thit k Trong qu trnh thit k h thng vt l, vn t ra hng u l phi lm th

no ti thiu ha khng gian lu tr v thi gian ngi dng tng tc vi h thng. Tuy nhin, do dung lng cc thit b nh tng nhanh, nn ngi ta tp trung

nhiu vo vic x l cc file v d liu sao cho hiu qu hn i vi ngi s dng. Cc thng tin cn thit thit k file vt l: Cc quan h c chun ha, k c c lng v s lng d liu cn lu

tr nh ngha chi tit cc thuc tnh Cc m t cho bit u v khi no d liu c s dng (

01 Co so du lieu

Documents

Transcript of 01 Co so du lieu