Transcript CHU DE 5

CHUẨN HÓA CSDL
CHỦ ĐỀ 5
(NORMALISATION)
Chuẩn hóa CSDL nhằm mục đích: tránh dư thừa thông
tin, tạo được thông tin nhất quán và khi cập nhật dữ liệu
không có trường hợp bất thường (anomalies) xảy ra . Có
những dạng chuẩn:
Dạng chuẩn 1 (NF1)
Dạng chuẩn 2 (NF2)
Dạng chuẩn 3 (NF3)
Dạng chuẩn Boyce-Codd
1
Dạng chuẩn 1 (NF1)
Một quan hệ đạt dạng chuẩn 1, nếu giá trị của mọi thuộc tính
là nguyên tố (không thể phân chia được) hay đơn.
STAFF
StaffNum
Ssurname
SfirstName
SAddr
Position
Salary
BranchNum
SL21
White
John
19 Taylor St
Manager
40000
B5
SG37
Beech
Ann
81 George St
Snr Asst
22000
B3
SG14
Ford
David
63 Everest Rd
Deputy
35000
B3
SA9
Howard
Mary
2 Elma Pl
Assistant
20000
B7
SG5
Kingston
Trevor
7 Henly Rd
Manager
40000
B3
SL41
Russel
Julie
28 Days Rd
Assistant
20000
B5
2
Dạng chuẩn 2
Quan hệ đạt NF2, nếu mọi thuộc tính không phải là khóa phải
phụ thuộc đầy đủ vào khóa
Thuật toán kiểm tra quan hệ Q dạng chuẩn 2
Bước 1: Tìm các khóa của Q bằng tập phụ thuộc hàm
Bước 2: Tìm bao đóng C của mỗi khóa K của Q
Bước 3: nếu có C bất kỳ, mà nó có chứa thuộc tính
không khóa thì Q không đạt dạng chuẩn 3,
ngược lại Q đạt dạng chuẩn 3
Coi Q(A,B,C,D) và F={ABC; BD; BCA}.Q có đạt
NF2?
Khóa của Q là: K1=AB và K2=BC, và K1+ = K2+ = ABCD
Ta có: B K1+, nghĩa là tồn tại BD, D là thuộc tính không
khóa, D không phụ thuộc đầy đủ vào khóa AB  Q không
đạt NF2.
3
bài tập
1) Cho Q(G,M,V,N,H,P) và tập phụ thuộc hàm
F={GM; GN; GH; GP; MV; NHPM}, Q đạt
NF2 ?
Khóa của Q là G, Q đạt NF2, nhận xét: Khóa có một
thuộc tính, quan hệ luôn đạt NF2.
2) Q(A,B,C,D,E,H) F={A  E; C  D; E  DH}, Dạng
chuẩn ?
Table names
Khóa của Q là K = {ABC} mà C  K+, nên tồn tại CD,
D là thuộc tính không khóa  D phụ thuộc không đầy đủ
vào khóa, nên Q không đạt dạng chuẩn NF2.
4
Dạng chuẩn 3, NF3
Thuộc tính phụ thuộc bắc cầu
Coi quan hệ Q, X,Y là tập thuộc tính của Q, A là một thuộc
tính trong Q
A là phụ thuộc bắc cầu vào X, nếu A thỏa các điều sau:
1) X Y, Y  A
2) Y  X
3) A  XY
Q đạt dạng chuẩn 3, nếu Q đạt dạng chuẩn 2 và mọi thuộc
tính không khóa không phụ thuộc bắc cầu vào khóa
5
Ví dụ: dạng chuẩn 3
Cho Q(ABCDEG) và tập PTH
F= {ABDEG, BC; DEGB}
Khóa của Q là A, Q đạt dạng chuẩn 2. C phụ thuộc bắc cầu
vào A vì:
1)AB  F+ (suy từ ABDEG)
2)BA không thuộc F+ vì A không thuộc B+ =BC
3)C không thuộc (AB) =AB
Q không đạt dạng chuẩn 3.
6
BÀI TẬP: dạng chuẩn 3
Q(N,G,P,M) và F= {NGP M, M P}
2)
Q(ABCD) và F={ABC; DB; CABD}
3)
Q(SV,MH,THAY) và
F = {SV,MH  THAY;THAY  MH}
Kết qủa:
1), 2) 3) Đạt dạng chuẩn 3
1)
7
Dạng chuẩn BCNF
1)
2)
3)
Phụ thuộc hàm hiển nhiên là những phụ thuộc hàm có vế
trái chỉ là khóa
Quan hệ đạt dạng chuẩn BCNF, nếu mọi phụ thuộc hàm
không hiển nhiên có vế trái chứa khóa
Quan hệ đạt dạng chuẩn BCNF thì cũng đạt dạng chuẩn 3
(ngược lại có thể sai)
8
Bài tập
1) Q(SV,MH,THAY), F = {SV,MH  THAY;
THAY  MH} đạt NF3, không đạt BCNF
2) Q(A,B,C,D), F={AB  C, D  B, C  ABD}
thì Q là 3NF nhưng không là BCNF
3)
Q(A,B,C,D,E,I) F={ACDEBI, CEAD}
(khóa là ACD, CE), Q đạt dạng chuẩn BCNF.
9
Phân rã đạt dạng chuẩn 3, bảo toàn thông tin
Q(ABCDEG), F={AE  C, CG  A, BD  G, GA  E}
Khóa chính BDA, Q không đạt 3NF vì BD  G
Q(ABCDEG)
(BD  G)
Q1(BDG)
F1={BD G}
Q2(BDAEC)
F2={AE C}
Q21(AEC)
F21={AE C}
Q22(BDAE)
F22{BDA E}
10
BÀI TẬP: Phân rã bảo toàn thông tin
Kiểm tra các quan hệ sau, nếu chưa đạt NF3, thì phân rã
thành những quan hệ và bảo toàn thông tin
1) Cho Kehoach(NGAY,GIO,PHONG,MONHOC,GIAOVIEN)
F={NGAY,GIO,PHONGMONHOC; MONHOC,NGAYGIAOVIEN
NGAY,GIO,PHONGGIAOVIEN;MONHOCGIAOVIEN}
2) Q(GHABCD) và F={GHAD,AGB,CDGH, CA,BHC}
3) Q(C,D,E,G,H,K) và tập PTH như sau:
F = {CK H, C D, EC, E G, CK E}
a) Chứng minh EK  DH
b) Tìm tất cả các khoa của Q.
c) Xác định dạng chuẩn cao nhất của Q.
d) Nếu Q chưa đạt dạng chuẩn 3 thì phân rã Q thành những quan hệ và bảo
toàn thông tin
11
Phân rã bảo tòan thông tin và
phụ thuộc hàm
- Tìm phủ tối thiểu Ftt của F
- Nếu có PTH của Ftt mà liên quan đến tất cả những thuộc
tính của Q, thì Q là kết quả phân rã ( nói khác đi không cần
phân rã Q)
- Nếu tồn tại phụ thuộc hàm nào của Ftt không chứa đủ các
thuộc tính của Q, thì nó là một quan hệ cần tìm.
- Cứ mỗi PTH: X  A  Ftt thì XA là một quan hệ cần tìm
- Nếu có một quan hệ con chứa khóa K của Q, thì kết thúc
thuật toán. Ngược lại tạo một quan hệ con K
12
Ví dụ: Phân rã bảo toàn thông tin và phụ thuộc hàm
Cho Q(CTHRSG) và
F={CT,HRC,THR,CSG,HSR}. Phân rã Q
1)
Phủ tối thiểu: Ftt={CT,HRC,THR,CSG,HSR}
Khóa của q là HS
3)
Những phụ thuộc hàm trong Ftt không chứa đủ các thuộc
tính của Q thì tạo thành một quan hệ, ta có:
Q1(CT),Q2(HRC),Q3(THR),Q4(CSG),Q5(HSR)
4)
Quan hệ Q5 chứa khóa, nên dừng thuật toán ( nếu không có
như thế thì phải thêm quan hệ Q6(HS))
Kết quả phân rã Q thành Q1, Q2, Q3, Q4 và Q5
2)
13
Tìm tất cả khóa chính của quan hệ
Tìm tất cả khóa của quan hệ Q(A,B,Z) có tập phụ thuộc
hàm F = {AB  C, C  A}
Gọi : NG tập những thuộc tính chỉ ở vế trái của tập F
DI tập những thuộc tính ở vế phải của F
TG tập những thuộc tính không có trong NG và DI
Trong ví dụ trên thì:
NG={B}, DI= ,
TG= {A, C}
Khóa chính:
CB hay AC
(khóa chính là những Xi có
chiều dài ngắn nhất và Xi+
chứa đủ các thuộc tính của Q
AC
Si = tập
con của
TG
Xi= Si
 NG
Xi+
00

B
B
01
C
CB
CBA
10
A
AB
ABC
11
AC
AC
AC
14
Bài tập tìm khóa chính
Cho quan hệ Q hãy tìm các khóa chính cho Q
1) Q( TENTAU,LOAITAU,MACHUYEN,LUONGHANG,BENCANG,
NGAY)
F={TENTAU  LOAITAU
MACHUYEN  TENTAU, LUONGHANG
TENTAU,NGAY  BENCANG, MACHUYEN}
2) Q(A,B,C,D,E,G)
F={ABC,C A,BCD,ACDB,DEG,BEC,CGBD,CEG}
3)Q(BROKER,OFFICE,STOCK,QUANTITY,INVESTOR,DIVIDENT)
F={STOCK  DIVIDENT
INVESTOR BROKER
INVESTOR,STOCK  QUANTITY
BROKER OFFICE }
15
Phủ tối thiểu cho tập phụ thuộc hàm
F ñöôïc goïi laø moät taäp phuï thuoäc haøm toái thieåu (hay phuû toái
thieåu) neáu F thoûa ñoàng thôøi ba ñieàu kieän sau:
1) F laø taäp phuï thuoäc haøm coù veá traùi khoâng dö thöøa
2) F laø taäp phuï thuoäc haøm coù veá phaûi moät thuoäc tính.
3) F laø taäp phuï thuoäc haøm khoâng dö thöøa
Thuaät toaùn tìm phuû toái thieåu cuûa moät taäp phuï thuoäc haøm
Böôùc 1:loaïi khoûi F caùc phuï thuoäc haøm coù veá traùi dö thöøa.
Böôùc 2:Taùch caùc phuï thuoäc haøm coù veá phaûi treân moät
thuoäc tính thaønh caùc phuï thuoäc haøm coù veá phaûi
moät thuoäc tính.
Böôùc 3: loaïi khoûi F caùc phuï thuoäc haøm dö thöøa.
16
Tìm phủ tối thiểu
Cho löôïc ñoà quan heä Q(A,B,C,D) vaø taäp phuï thuoäc F nhö
sau: F= {AB  CD,B  C,C  D}
Haõy tính phuû toái thieåu (Ftt) cuûa F.
Böôùc 1: ABCD laø PTH coù veá traùi dö thöøa?
B  CD  F+? traû lôøi: B+=BCD  B  CD  F+
Vaäy AB  CD laø PTH coù veá traùi dö thöøa A  keát quaû cuûa böôùc 1 laø:
F{B  CD, B  C, C  D}
Böôùc 2: keát quaû cuûa böôùc 2 laø:
F{B  D, B  C, C  D}=F1tt
Böôùc 3: trong F1tt, B  C laø PTH dö thöøa?
B  C  G+? vôùi G = F1tt - {B  C}={B  D;C  D}
BG+=BD  B  C  G+  trong F1tt B  C khoâng dö thöøa.
trong F1tt,B  D laø PTH dö thöøa?
B  D  G+? vôùi G = F1tt - {B  D}={B  C, C  D}
BG+=BCD  B  D  G+  trong F1tt,B  D dö thöøa.
keát quaû cuûa böôùc 3 cho phuû toái thieåu:
F{B  C, C  D}=Ftt
17
Bài tập
Cho löôïc ñoà quan heä Q(MSCD,MSSV,CD,HG) vaø taäp
phuï thuoäc F nhö sau:
F = {MSCD CD; CD MSCD; CD,MSSV HG;
MSCD,HG  MSSV; CD,HG  MSSV;
MSCD,MSSV HG}
Haõy tìm phuû toái thieåu cuûa F
keát quaû:
Ftt = {MSCD  CD;
CD
 MSCD;
CD,HG MSSV;
MSCD,MSSV  HG}
18