1/45

Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 2/45 Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 3/45 Chun ha: L qu trnh tch mt lc quan h thnh mt tp cc lc con, sao cho qu trnh tch l khng mt thng tin v cc lc con l ti u hn lc ban u theo ngha: hn ch d tha d liu, thun li cho cc qu trnh to lp, cp nht v cc thao tc tm kim khc. nh ngha v thuc tnh kha:Cho lc quan h =(U, F), thuc tnh AeU c gi l thuc tnh kha nu nh A thuc mt trong cc kha no , ngc li A c gi l thuc tnh khng kha.

4/45 nh ngha ph thuc hm y :Cho lc quan h =(U, F), X, Y, Y_U tp thuc tnh Y c gi l ph thuc hm y vo tp thuc tnh X nu nh Y ph thuc hm vo X nhng khng ph thuc hm vo bt k mt tp con thc s no ca x tc l: - X Y ( Y ph thuc hm vo X) - Xc X th X Y (mi tp con thc s ca X u khng th xc nh hm Y) 5/45 nh ngha ph thuc hm bc cu: Cho lc quan h =(U, F), X_U, AeU, thuc tnh A c gi l ph thuc hm bc cu vo tp thuc tnh X nu nh Y _X : - X Y - Y A - Y X - A eXY N X Y v Y khng ph thuc bc cu vo X th Y ph thuc hm trc tip vo X 6/45 - Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 7/45 S cn thit chun ha d liu: Mc tiu: l trit tiu mc cao nht kh nng xut hin cc d thng thng tin khi thc hin cc php lu tr. Chn ha: kho st danh sch cc thuc tnh v p dng cc tp quy tc phn tch vo danh sch , bin i thnh nhiu tp nh hn sao cho: Ti thiu vic lp li. Trnh d thng thng tin. Xc nh v gii quyt c s khng r rng, nhp nhng trong suy din. 8/45 Qu trnh chun ha: L qu trnh tch lc quan h v mt nhm tng ng cc lc quan h chiu sao cho khi kt ni t nhin khng lm tn tht thng tin v bo ton c cc ph thuc hm. C s chun ha: Da trn cc khi nim v ph thuc hm, ph thuc hm y , kha, cc thuc tnh khng kha 9/45 V d: Lc quan h QLCAP( TC#, GTR, MC#, N#, NXS) Trong : TC# : M tuyn cp GTR : Gi tr ca cp NSX : Nc sn xut MC# : M cpN# : M nc sn xutNg ngha d liu nh sau: Trong mt tuyn cp, gi tr ca mt loi cp c xc nh duy nht. Mi m cp xc nh m nc sn xut cp. M nc xc nh tn nc sn xut. 10/45 Hnh 16.1: S cc ph thuc hm trong lc quan h QLCAP

GTR MC# TC# NSX N# V d: 11/45 TC#GTRMC#N#NSX T01200C01HAQHn Quc T01250C02HAQHn Quc T01220C03VTCVit Nam T02500C01HAQHn Quc T02400C04JANNht Bn T03100C05RUSNga T04400C06CHNTrung Quc T04450C03VTNVit Nam Bng 16.1: Mt th hin ca lc quan h QLCAP 12/45 Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 13/45

nh ngha: Mt lc quan h =(U, F) c gi l dng chun mt ( 1NF) nu v ch nu tt c min gi tr ca cc thuc tnh ca R u nguyn t (khng th phn chia c) V d: Xt quan h S(S#, PRO), thy rng thuc tnh PRO cha cc gi tr khng nguyn t. V vy quan h ny khng phi l quan h dng chun 1NF. 14/45 V d: Bng 16.2: Mt v d quan h khng 1NF. S#PRO P#QTY S11001 2001 3002 S21002 2002 S33003 1001 15/45 Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 16/45 Trc khi nghin cu dng chun th 2, ta xt v d sau y: Xt CSDL gm 2 lc quan h THI (MONTHI, GIAOVIEN) v SINHVIEN ( MONTHI, MSSV, TEN, TUOI, DCHI, DIEM) phn nh thng tin v kt qu thi ca mt n v no . Trong quan h THI th MONTHI l kha v trong quan h SINHVIEN th MONTHI v MSSV l kha. quan h th hai d nhn thy rng MONTHI, MSSV, DIEM xc nh kt qu thi ca sinh vin cn MSSV, TEN, TUOI, DCHI xc nh i tng d thi. 17/45 Xt cc hin hnh ca 2 lc quan h THI v SINHVIEN nh sau: THI MONTHIGIAOVIEN TonT.Tr L T.Cng HaT.Giao 18/45 SINHVIEN MONTHIMSSVTENTUOIDCHIDIEM Ton11Lan20HN8.0 Ton12Hue21HY7.5 Ha11Lan20HN7.0 Ha12Hue21HY6.0 L11Lan20HN5.0 L13An21BN4.0 19/45 Trong qu trnh cp nht, truy sut d liu xut hin cc vn sau: D tha d liu Php thay i Php chn Php loi b 20/45 nh ngha: Cho lc quan h =(U, F), lc c gi l dng chun 2( k hiu l 2 NF) nu nh lc dng chun 1 NF v cc thuc tnh khng kha ca l ph thuc y vo kha chnh. Thut ton kim tra lc c dng chun 2NF hay khng? Input:Lc quan h =( U, F) Output: Khng nh c t dng chun 2NF hay khng 21/45 Thut ton 1: Thut ton kim tra dng chun 2NF Vo:Lc quan h Q, tp ph thuc hm F Ra: Khng nh Q ca t dng chun 2NF hay khng Bc 1: Tm tt c cc kha ca Q Bc 2: Vi tt c cc kha K, tm bao ng ca tt c cc tp con tht s S ca K. Bc 3: Nu c bao ng S+cha thuc tnh khng kha th Q khng t chn 2NF ngc li Q t c dng chun 2NF. 22/45 V d: Cho lc quan h Q(A, B, C, D) v tp ph thuc hm: F={ABC, BD, BCA } . Hi Q c t chun 2NF hay khng? Gii: Io =Q \( Ri -Li )=ABCD \ (CDA)=B. Io =Q N={ ( Ri -Li ) sao cho Li _Io}=D N=(Io N)+ \ Io=(BD)+ \ B = D (N_ No ) N= Ri -Li =ACD \ ABC=D No=NNN=D B=Q \ No \ Io=ABCD \ D \ B=AC V B=2 => c hai kha BA v BC 23/45 Io ={ B}, B={AC} Kha l K =AB v K =BC. Ta thy Bc K , BD, D l thuc tnh khng kha => thuc tnh khng kha khng ph thuc y vo kha . => Q khng t chun 2NF. X (IoX )(IoX )+Siu khaKha BBD AABABCDABAB CBCABCDABCDBC ACABCABCDABC 24/45 Thut Ton 2: Bc 1: Tm tt c cc kha ca lc , t tt c cc kha ny ta suy ra cc thuc tnh khng kha ca lc . K hiu tp thuc tnh khng kha ny l NK. Bc 2: Vi mi kha Ki, k hiu h ca cc tp con thc s ca Ki l { S1, S2, , Ski }, k hiu Q={ Q1, Q2, , Qn } l h tt c cc tp con thc s ca kha Ki. Bc 3: Tm bao ng Q+ ={ Q1+, Q2+, , Qn+ } Bc 4: Nu Q+ NK=| vi mi i=1n th lc dng chun 2NF ngc li tn ti Q+ NK=| th lc khng dng chun 2NF. 25/45 Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 26/45 nh ngha 1:Cho lc quan h = ( U, F), lc c gi l dng chun 3 ( k hiu l 3 NF), nu nh lc dng chun 2NF v cc thuc tnh khng kha ca l khng ph thuc hm bc cu vo kha chnh. nh ngha 2: Cho lc quan h =( U, F), lc c gi l dng chun 3, k hiu l 3NF, nu nh mi ph thuc hm X A e F+ vi A e X th: -Hoc X l siu kha. -Hoc A l thuc tnh kha. 27/45 Nhn xt: Lc quan h =( U, F), vi F l tp cc ph thuc hm c v phi ch gm mt thuc tnh. Khi lc dng chun 3NF khi v ch khi mi ph thuc hm XAe F viA e X u c : - Hoc X l siu kha. - Hoc A l thuc tnh kha. 28/45 Thut ton kim tra lc c dng chun 3NF hay khng? T nh ngha v dng chun 3NF trn ta c thut ton kim tra xem mt lc c dng chun 3NF hay khng nh sau: Input:Lc quan h =( U, F) Output: Khng nh c t dng chun 3NF hay khng 29/45 Thut ton 1: Thut ton kim tra dng chun BCNF Vo:Lc quan h Q, tp ph thuc hm F Ra: Khng nh Q c t dng chun 3NF hay khng Bc 1: Tm tt c kha ca Q. Bc 2: T F to tp ph thuc hm tng ng FItt c v phi mt thuc tnh. Bc 3: Nu mi ph thuc hm X Ae FItt vi A e X u c X l siu kha hoc A l mt thuc tnh kha th Q t chn 3 ngc li Q khng t chun 3. 30/45 V d:Cho lc quan h Q(A, B, C, D) F={ ABC; DB; CABD }Hi lc c dng chun 3NF? Gii: Io =Q \( Ri -Li )=ABCD \ ABCD= | ,Io =Q N={ ( Ri -Li ) sao cho Li _Io }= | N=(Io N)+ \ Io= | (N_ No ) N= Ri -Li =ABCD \ ABCD= | No=NNN= | B=Q \ No \ Io=ABCD \ | \ =ABCD V B=4>2=> Io ={|}, B={ABCD} 31/45 K1= { AB };K2= { AD };K3= { C } l kha => mi ph thuc hm XAeF u c A l thuc tnh kha.Vy Q t chun 3NF. X (IoX )(IoX )+Siu khaKha AAA BBB CCABCDCC DDBD ABABABCDABAB ACACABCDAC ADADABCDADAD BCBCABCDBC BDBDBD CDCDABCDCD ABCABCABCDABC ABDABDABCDABD ACDACDABCDACD BCDBCDABCDBCD 32/45 Thut ton 2: Bc 1: Tm tt c kha ca lc . Bc 2: T F tm tp ph thuc hm tng ng F, m v phi ca cc ph thuc hm trong F ch c mt thuc tnh. Bc 3: Nu mi ph thuc hm XAeF vi AeX u c X l siu kha hoc A l thuc tnh kha th t chun 3NF ngc li khng t chun 3NF. 33/45 Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 34/45 nh ngha 1:Cho lc quan h = ( U, F), lc c gi l dng chun Boyce Codd, k hiu l BCNF, nu nh lc dng chun 1NF v nu XAeF+ (AeX) th X phi l siu kha ca lc . nh nga 2:Cho lc quan h = ( U, F), lc c gi l dng chun Boyce Codd, k hiu l BCNF, nu nh lc dng chun 1NF v nu XAeF l ph thuc hm khng tm thng(Y. X) th X phi l siu kha ca lc . 35/45 Thut ton kim tra lc dng chun BCNF hay khng? T nh ngha v dng chun BCNF trn ta c thut ton kim tra xem mt lc c dng chun BCNF hay khng nh sau: Input:Lc quan h =( U, F) Output: Khng nh c t dng chun BCNF hay khng 36/45 Thut ton 1: Thut ton kim tra dng chun BCNF Vo:Lc quan h Q, tp ph thuc hm F Ra: Khng nh Q c t dng chun 3NF hay khng Bc 1: Tm tt c kha ca Q. Bc 2: T F to tp ph thuc hm tng ng FItt c v phi mt thuc tnh. Bc 3: Nu mi ph thuc hm X Ae FItt vi A e X u c X l siu kha th Q t chn BCNF ngc li Q khng t chun BCNF. 37/45 V d: Q (A, B, C, D, E, I) F={ACDEBI; CEAD }. Hi Q c t chun BCNF hay khng? Gii: Io =Q \( Ri -Li )=ABCDEI \ (EBIAD)=C =>Io =Q N={ ( Ri -Li ) sao cho Li _Io } = | N=(Io N)+ \ Io= | (N_ No ) N= Ri -Li =ABDEI \ ACDE=BI No=NNN=BI B=Q \ N \ Io=ABCDEI \ BI \ C=ADE V B=3>2 => Io ={ C}, B={ADE} 38/45 F =FItt={ ACDE, ACDB, ACDI, CEA, CED}Mi ph thuc hm ca FItt u c v tri l siu kha => Q t dng chun BCNF. X (IoX )(IoX )+Siu khaKha CC AACAC DCDCD ECEABCDEICECE ADACDABCDEIACDACD AEACEABCDEIACE DECDEABCDEICDE ADEACDEABCDEIADE 39/45 Thut tn 2: Bc 1: T F tm tp ph thuc hm tng ng F, m v phi ca cc ph thuc hm trong F ch c mt thuc tnh. Bc 2: Nu mi ph thuc hm XAeF+ vi AeX u c X l siu kha th t chn BCNF ngc li khng t chun BCNF. 40/45 Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 41/45 Cho lc quan h =( U, F), hy tm dng chun cao nht ca lc . Input: Lc quan h =( U, F) ( y ta gi thit rng lc c chun ha, tc l dng chun 1). Output: Dng chun cao nht ca lc . Thut ton: Bc 1: Tm tt c cc kha ca lc . Bc 2: S dng thut ton kim tra lc dng chun BCNF, nu lc dng BCNF th dng thut ton v kt lun dng chun cao nht ca lc l BCNF, ngc li th chuyn qua bc tip theo. 42/45 Thut ton: Bc 3: S dng thut ton kim tra lc dng chun 3NF, nu lc dng 3NF th dng thut ton v kt lun dng chun cao nht ca lc l 3NF, ngc li th chuyn qua bc tip theo. Bc 4: S dng thut ton kim tra lc dng chun 2NF, nu lc dng 2NF th dng thut ton v kt lun dng chun cao nht ca lc l 3NF, ngc li th lc dng chun 1NF. 43/45 Mt s khi nim lin quan Cc dng chun Dng chun 1 (1NF- Fist normal form) Dng chun 2 (2NF- Second normal form) Dng chun 3 (3NF- Third normal form) Dng chun BCNF (Boyce Codd normal form) Thut ton tm dng chun cao nht ca lc Mi quan h gia cc dng chun 44/45 nh l: Mt lc dng chun cao khi th cng dng chun thp, tuy nhin iu ngc li c th ng, iu ny c mt t trong hnh sau: 1NF 2NF 3NF BCNF 45/45