Tài liệu Nf3_tutorial_vietnamese

1 Hư ng d n th c hi n d ng chu n 3NF Tác gi : Fred Coulson Copyright © Fred Coulson 2007 (last revised November 18, 2007) This tutorial may be freely copied and distributed, providing appropriate attribution to the author is given. Inquiries may be directed to http://phlonx.com/contact http://phlonx.com/resources/nf3/ 2 Mục lục Mục lục...........................................................................................................................................................2 Về bản dịch....................................................................................................................................................3 Giới thiệu........................................................................................................................................................4 Bài toán: Quản lí Hóa đơn..............................................................................................................................5 Dạng chuẩn thứ 1 (1NF): Không có phần tử/nhóm phần tử lặp...................................................................7 Dạng chuẩn thứ 2 (2NF): Không có phụ thuộc hàm không đầy đủ vào khóa chính.....................................9 Dạng chuẩn thứ 2 (2NF): Pha thứ II.............................................................................................................13 Dạng chuẩn thứ 3 (3NF): Không có phụ thuộc hàm vào thuộc tính không khóa........................................16 Tham khảo...................................................................................................................................................18 3 Về bản dịch Ngư i d ch: Phan Anh Vũ. L p ĐT12.K49. Trư ng ĐH Bách Khoa HN. Email: [email protected] Website: http://cntt.tv Xin giành b n d ch này t ng anh em l p ĐT12.K49 nói riêng, bà con khoa Đi n t Vi n thông K49 trư ng ĐH Bách Khoa HN nói hơi riêng v i l i chúc anh em thi t t môn K thu t ph n m m (thi l i t n 5k đ y). V i ai không ôn thi môn KTPM nhưng quan tâm và bư c đ u tìm cách chu n hóa CSDL c a riêng mình, đây có l s là tài li u b t đ u t t nh t. Theo quan đi m c a tôi thì đây là m t tutorial r t thú v , đ c p đ n khá nhi u khía c nh l t léo trong quá trình chu n hóa. Tuy nhiên b n d ch vì nhi u lí do (tôi đang ôn thi Tư tư ng HCM l n 1 ch ng h n) nên ch t lư ng còn h n ch , mong nh n đư c góp ý đ hoàn thi n d n. C m ơn đ i ca Fred Coulson t t b ng đã đ ng ý cho d ch và phát tán tài li u này v i l i h a s host b n d ch trên trang c a đ i ca. Chúc đ i ca s c kh e, ch p nhi u nh đ p và vi t nhi u tutorial hay. Còn bây gi , nào mình cùng đi xe buýt, nào mình cùng đi thi nhé … 4 Giới thiệu Đây là m t hư ng d n r t ng n g n giành cho nh ng ngư i m i b t đ u bư c vào lĩnh v c chu n hóa cơ s d li u. Vì r t khó đ di n t b ng l i nên tôi dùng nhi u nh t có th các hình nh, bi u đ . Đ trình bày các qui t c chính trong quá trình chu n hóa, tôi d a theo ví d c đi n v Hóa đơn (Invoice) và chu n hóa nó v d ng 3NF (Third Normal Form). Trong quá trình đó, chúng ta s hình thành Sơ đ liên k t th c th (Entity Relationship Diagram - ERD) cho cơ s d li u. Chú ý: Đây không ph i là hư ng d n chi ti t đ thi t k và th c thi m t cơ s d li u th c t . B n không ph i làm theo tuy t đ i như các hình minh h a vì nó ch minh h a cho vi c các d li u thô đư c s p x p l i như th nào trong quá trình chu n hóa. Có th có ngư i không thích cách đó. Tôi cũng không trình bày các v n đ liên quan đ n đi m l i, h i c a vi c chu n hóa. Ai quan tâm đ n các ch đ đó, xin xem danh sách tham kh o cu i tài li u này. Thư ng thì khi ai đó b t tay vào thi t k CSDL, trong đ u anh/cô ta đã có m t mô hình chu n hóa ph n nào r i – chu n hóa là m t cách t nhiên đ nh n ra m i quan h c a d li u và không c n ki n th c đ c bi t v toán h c, t p h p … Trong th c t , nhi u khi còn ph i “phi chu n hóa” (de-normalize) CSDL – nhưng v n đ này n m ngoài n i dung bài vi t. Đ b t đ u: Trư c tiên, xin nh n m lòng 3 qui t c sau v các d ng chu n. Nh trư c, b n s hi u sau: 1. Không có ph n t /nhóm các ph n t l p. 2. Không có ph thu c hàm không đ y đ vào khóa ng c . 3. Không có ph thu c hàm vào các thu c tính không khóa. 5 Bài toán: Quản lí Hóa đơn Cho m u hóa đơn như Hình A). Hình A: Hóa đơn Đây là m u hóa đơn quen thu c trong kinh doanh. T t c các thông tin trên đó đ u quan tr ng. Chúng ta đưa các thông tin đó vào CSDL như th nào đây? Ai đó chưa bi t v CSDL quan h có th đưa các thông tin đó vào spreadsheet trong Excel như sau: Hình A-1: B ng hóa đơn Không t i! Bàng này ghi l i t t c các đơn hàng đư c mua b i t t c các khách hàng. Nhưng đi u gì x y ra n u ta mu n l y các thông tin ph c t p như: • Có bao nhiêu 3" Red Freens mà Freens R Us đ t trong năm 2002? 6 • • T ng s 56" Blue Freens đư c bán Texas? Nh ng s n ph m nào đư c bán vào ngày 14 tháng 7 năm 2003? B ng trên càng nhi u thông tin thì vi c tr l i các câu h i trên càng khó khăn. Trong n l c đưa d li u v tr ng thái mong mu n đ tr l i các câu h i ki u như trên, chúng ta đang b t đ u vi c chu n hóa CSDL (normalization). 7 Dạng chu n thứ 1 (1NF): Không có phần tử/nhóm phần tử lặp Nhìn vào hàng 2, 3, 4 c a b ng trong Hình A-1, ta th y t t c các d li u liên quan đ n m t hóa đơn (Invoice #125). Theo thu t ng CSDL, nhóm các hàng này đư c g i là m t hàng đơn CSDL (a single database row). M t hàng đơn CSDL đây đư c t o b i ba hàng trong b ng Hình A-1. D ng chu n 1NF mu n chúng ta tri t tiêu các ph n t l p. Chúng là các ph n t nào? M t l n n a, đ ý hóa đơn đ u tiên (#125) trong Hình A-1. Ô H2, H3, và H4 ch a m t danh sách các s Item ID. Đây là m t c t trong hàng CSDL đ u tiên c a chúng ta. Tương t , I2-I4 hình thành m t c t khác; tương t v i J2-J4, K2-K4, L2-L4, và M2-M4. Các c t trong CSDL thư ng đư c g i là thu c tính (attributes) (hàng/c t có cách g i khác là b /thu c tính). Đ ý th y các c t này ch a danh sách các giá tr . Rõ ràng là các danh sách như th vi ph m lu t chu n 1NF: 1NF không cho phép danh sách hay chu i giá tr như v y t n t i trong m t c t CSDL. 1NF đòi h i tính nguyên t - t c là s không th phân chia m t thu c tính thành các ph n nh hơn. Vì th chúng ta c n ph i lo i b s l p l i thông tin v item trong hàng giành cho Hóa đơn #125. Trong Hình A-1, đó là các ô sau: • T H2 đ n M2 • T H3 đ n M3 • T H4 đ n M4 Tương t , chúng ta cũng th y hi n tư ng trùng l p d li u trong hàng giành cho Hóa đơn #126. Chúng ta có th chu n hóa sang d ng 1NF đ đ t đư c tính nguyên t m t cách d dàng như sau – cho m i item m t hàng riêng bi t (thư ng g i là cách làm ph ng). Hình A-2: làm ph ng b ng d li u Có th có ai đó ph n đ i: Chúng ta đang c g ng làm gi m s trùng l p d li u, nhưng đây th m chí chúng ta đang làm ngư c l i! D li u v Khách hàng b trùng l p! Xin đ ng lo l ng v đi u đó. S trùng l p đó s đư c gi i quy t khi chúng ta đi t i d ng chu n 3NF. Xin hãy kiên nh n; đây là m t bư c chúng ta c n ph i đi qua đ đ n k t qu cu i cùng. 8 Đ n đây chúng ta m i ch đi đư c m t n a ch ng đư ng đ đ t đư c d ng chu n 1NF. D ng chu n 1NF gi i quy t 2 v n đ : 1. M i hàng ph i không ch a nhóm l p (Tính nguyên t ). 2. M i hàng ph i có m t thu c tính nh n d ng duy nh t (Khóa chính) Chúng ta đã gi i quy t xong tính nguyên t . Đ gi i quy t v n đ Khóa Chinh, chúng ta c n ph i chuy n toàn b d li u sang m t h qu n tr CSDL quan h (RDBMS). đây, tôi dùng Microsoft Access đ t o b ng orders như Hình B: Hình B: B ng orders B ng này cũng khá gi ng b ng trong Excel nhưng đi m khác là trong RDBMS, chúng ta có th t o m t khóa chính. Khóa chính là m t c t (ho c nhóm c t) giúp xác đ nh duy nh t m t hàng. Như nhìn th y trong hình B, không có m t c t đơn nào có th dùng đ xác đ nh duy nh t các hàng. Tuy nhiên, n u chúng ta k t h p 2 c t order_id và item_id thì đư c: không có hai hàng nào có giá tr order_id và item_id gi ng nhau. Vì th , k h p hai c t đó v i nhau, chúng ta có khóa chính c a b ng Orders. Chúng ta g i hai c t đó là khóa g p (concatened key). M t giá tr , giúp xác đ nh duy nh t m t hàng g i là khóa chính. Khi giá tr đó đư c t o b i hơn m t c t thì ta g i chúng là concatenated primary key. C u trúc c a b ng Order có th đư c bi u di n trong Hình C bên: Hai thu c tính hình thành nên khóa chính đư c kí hi u PK. Hình C cũng chính là Lư c đ liên k t th c th (Entity Relationship Diagram - or ERD). CSDL c a chúng ta bây gi đã th a mãn hai yêu c u c a 1NF: tính nguyên t và tính duy nh t. Đó là hai đi u ki n cơ b n nh t c a CSDL quan h . Ti p theo là gì? 9 Dạng chu n thứ 2 (2NF): Không có phụ thuộc hàm không đầy đủ vào khóa chính. B y gi , chúng ta tìm các ph thu c hàm không đ y đ vào khóa chính đ lo i b chúng. V i các b ng có khóa chính đư c t o b i hơn m t thu c tính, các thu c tính không n m trong khóa chính ph i ph thu c hàm đ y đ vào khóa chính mà không đư c phép t n t i các thu c tính ch ph thu c vào m t ph n c a khóa chính. N u có thu c tính nào ch ph thu c m t ph n vào khóa chính thì b ng đó chưa đ t d ng chu n 2NF. Đ hi u rõ, chúng ta xem xét t ng thu c tính c a b ng Orders. V i m i thu c tính, chúng ta s đ t câu h i: Li u thu c tính này có th t n t i mà không c n m t hay nhi u thu c tính nào đó n m trong khóa chính không? N u câu tr l i là “có” – dù ch m t – thì b ng chưa đ t chu n 2NF. Xem l i Hình C bên đ nh l i c u trúc b ng. Đ u tiên, nh c l i ý nghĩa c a hai thu c tính làm nên khóa chính: • order_id xác đ nh duy nh t m t hóa đơn. • item_id xác đ nh duy nh t m t item trong kho. Đây có th là mã s c a linh ki n, mã s hàng trong kho, s SKU, sô UPC, … Chúng ta s không phân tích hai thu c tính đó (vì chúng là thành ph n c a khóa chính). Bây gi , ta s xem xét các thu c tính còn l i. order_date là ngày l p hóa đơn. Rõ ràng là thu c tính này ph thu c vào order_id; ngày l p hóa đơn thì ph i liên quan đ n hóa đơn, n u không nó ch là m t ngày bình thư ng. Nhưng ngày l p hóa đơn có th t n t i mà không c n item_id? Câu tr l i đơn gi n là có th : ngày hóa đơn ch ph thu c vào order_id, không ph thu c vào item_id. M t s có th ph n đ i, cho r ng làm như th t c là có th t o ra m t hóa đơn mà không có item nào (m t hóa đơn r ng). Nhưng đó không ph i là v n đ c a chúng ta. Chúng ta đang xem xét đây là li u m t hóa đơn nào đó, l p vào m t ngày nào đó có ph thu c vào m t item nào đó không? Rõ ràng là không. V n đ làm sao đ không t n t i hóa đơn r ng là m t “qui t c nghi p v ” (business rule) đư c th c hi n, ki m tra m chương trình; đó không ph i v n đ mà chu n hóa gi i quy t. Như v y, order_date không th a mãn d ng chu n 2NF. Do đó, b ng Orders không đ t 2NF. Bây gi hãy xem xét các thu c tính còn l i. Chúng ta c n tìm t t c các thu c tính không th a mãn 2NF đ x lí. 10 customer_id là s ID c a khách hàng. Nó có ph thu c vào order_id? Không: m t khách hàng có th t n t i mà không c n mua hàng. Nó có ph thu c vào item_id? Không: v i cùng lí do. Đây là m t đi u thú v : customer_id (cùng v i các thu c tính customer_* khác) không ph thu c vào customer_id l n order_id, t c là không ph thu c vào b t c thu c tính nào c a khóa chính). Chúng ta s làm gì v i chúng? Chúng ta ch quan tâm t i chúng khi xem xét d ng chu n 3NF. Bây gi chúng ta đánh d u chúng là “không rõ ràng” (unknown). ? item_description là miêu t v hàng hóa. Rõ rang là nó ph thu c vào item_id. Nhưng nó có th t n t i mà không c n order_id? Có! M t item có th t n t i trong kho mãi mãi, mà không bao gi đư c bán … Nó đ c l p v i hóa đơn. Như v y, item_description không th a đi u ki n c a 2NF. item_qty là s lư ng m t m t hàng đư c yêu c u trong m t hóa đơn. Rõ rang thu c tính này ph thu c vào c hai thu c tính c a khóa chính. Chúng ta ch có th nói “5 cái máy tính” hay “6 cái TV” ch không th nói “10 cái không gì c ” (ít nh t là trong thi t k CSDL). S lư ng m t hàng hóa đư c yêu c u trong m t hóa đơn không th t n t i không có hóa đơn. Như v y thu c tính này th a mãn 2NF. item_price tương t như item_description. Nó ch ph thu c vào item_id mà không ph thu c vào order_id, nên nó không th a mãn 2NF. item_total_price hơi đ c bi t. M t m t, nó có v như ph thu c vào c order_id l n item_id, t c là th a mãn 2NF. M t khác, nó là m t giá tr rút ra t item_qty và item_price. Chúng ta s x lí th nào? Trong th c t , trư ng này không liên quan đ n CSDL c a chúng ta. Nó có th d dàng đư c t o ra ben ngoài CSDL; thêm nó vào CSDL s gây dư th a. Do đó chúng ta s b nó đi. order_total_price, là t ng t t c các item_total_price l i là m t giá tr rút ra n a nên chúng ta s b thu c tính này. Đây là b ng phân tích 2NF c a chúng ta: Chúng ta s làm gì v i m t b ng không th a mãn 2NF như th ? Trư c tiên, l y ra n a sau c a khóa chính (item_id) và đưa nó vào m t b ng khác. Các thu c tính khác ph thu c vào item_id – đ y đ ho c không đ y đ - cũng đưa luôn vào b ng m i này. Chúng ta s g i b ng m i này là order_items (xem Hình D). Edited by Foxit Reader Copyright(C) by Foxit Software Company,2005-2008 For 11 Evaluation Only. Các thu c tính còn l i – g m các thu c tính ch ph thu c vào n a đ u c a khóa chính (order_id) và các thu c tính chưa xác đ nh – gi nguyên. Hình D: Bàng orders và b ng m i: order_items Có m t vài đi m c n chú ý: 1. Chúng ta ph i đưa thu c tính order_id vào b ng order_items (đ xác đ nh xem m i order_item thu c v order nào. 2. B ng orders có ít thu c tính hơn trư c. 3. Khóa chính c a b ng orders ch g m m t thu c tính: order_id. 4. Khóa chính c a b ng order_items g m hai thu c tính. Sau đây là c u trúc các b ng (Hình E): Hình E: C u trúc b ng orders và order_items table N u b n chưa quen đ c Lư c đ liên k t th c th thì xin đ ý vào đư ng n i gi a hai b ng. Đư ng n i này 12 có nghĩa là: • M i order có th có m t ho c nhi u order-item, nhưng ph i có ít nh t m t; • M i order-item có th thu c v m t và ch m t order. 13 Dạng chu n thứ 2 (2NF): Pha thứ II N u b n nghĩ r ng chúng ta đã đ t chu n 2NF thì ch đã, v n còn! Nh r ng d ng chu n 2NF áp d ng cho các b ng có khóa chính h p thành b i hơn m t thu c tính. Bây gi b ng orders có khóa chính là khóa đơn, b ng này đã d t d ng chu n 2NF. Xin chúc m ng! Tuy nhiên, bây gi , b ng order_items l i có khóa chính t o b i hai thu c tính. Chúng ta l i ph i phân tích xem nó đã đ t 2NF chưa. Chúng ta l i làm theo cách cũ, v i m i thu c tính, đ t ra câu h i: Li u thu c tính này có th t n t i mà không c n m t hay nhi u thu c tính nào đó n m trong khóa chính không? Bên c nh là Hình F, bi u di n c u trúc c a b ng order_items. Bây gi chúng ta l n lư t xem xét các thu c tính không khóa. item_description ph thu c vào item_id, nhưng không ph thu c vào order_id. Do đó, thu c tính này không đ t chu n 2NF (ng c nhiên?) item_qty ph thu c vào c hai thu c tính c a khóa chính nên thu c tính này th a mãn chu n 2NF. item_price ch ph thu c vào item_id mà không ph thu c vào order_id, nên nó vi ph m đi u ki n c a chu n 2NF. Chúng ta có b ng phân tích như sau: Bây gi , chúng ta l y ra các thu c tính không th a mãn đi u ki n c a chu n 2NF và đưa vào m t b ng m i. Chúng ta g i b ng m i này là b ng items: Hình G: B ng order_items và b ng items 14 Khoan đã, có gì đó không n. Lúc trư c, sau khi ki m tra các đi u ki n c a chu n 2NF, chúng ta l y ra t t c các thu c tính ph thu c vào item_id và đưa vào m t b ng m i. L n này, chúng ta l i l y ra các thu c tính không đ t chu n 2NF: nói cách khác, gi a nguyên item_qty. T i sao? L n này có gì khác mà l i làm như v y? Đi m khác nhau là ch : trong l n trư c, chúng ta đưa thu c tính khóa item_id ra kh i b ng orders, là do quan h m t-nhi u gi a orders và order-items. Do đó, thu c tính item_qty ph i đi cùng item_id vào b ng m i. L n này, item_id không đư c đưa ra kh i b ng order_items là do quan h nhi u-m t gi a order-items và items. Do đó, vì item_qty không vi ph m chu n 2NF nên nó đư c gi l i b ng có khóa chính g m hai thu c tính. Đ hi u rõ hơn, có th xem ERD m i: Hình H: Đư ng n i gi a b ng items và b ng order_items nghĩa là: • M i item có th n m trong m t s hóa đơn ho c không n m trong hóa đơn nào. • M i order-item có th liên quan đ n m t và ch m t item. Hai quan h trên là ví d cho quan h m t-nhi u. Ba b ng này, xem xét m t cách toàn di n, là cách chúng ta bi u di n quan h nhi u-nhi u: M t order nào đó có th có nhi u item; m t item nào đó có th thu c v nhi u order. Nh r ng l n này, chúng ta không đưa thu c tính khóa order_id vào b ng m i. Lí do là m i item c th , không c n ph i bi t nó thu c v order nào. B ng order_items lưu tr 15 nh ng thông tin dó thông qua hai thu c tính order_id và item_id. Hai thu c tính này khi đ ng k t h p v i nhau thì t o thành khóa chính cho b ng order_items, nhưng khi đ ng riêng r , chúng là các khóa ngo i (foreign keys) tr t i các hàng trong các b ng khác. Chúng ta s nói nhi u hơn v khóa ngo i trong ph n 3NF. Cũng chú ý r ng, b ng m i không có khóa chính h p thành b i nhi u thu c tính nên nó th a mãn đi u ki n c a d ng chu n 2NF. Đ n đây, CSDL c a chúng ta đã đ t d ng chu n 2NF! 16 Dạng chu n thứ 3 (3NF): Không có phụ thuộc hàm vào thuộc tính không khóa. Cu i cùng, chúng ta tr l i v n đ liên quan đ n thông tin v Khách hàng. V i CSDL hi n t i, n u m t khách hàng có hơn m t order, chúng ta s ph i nh p thông tin v khách hàng đó nhi u l n. X y ra hi n tư ng này là do trong b ng order có t n t i các thu c tính ph thu c vào m t thu c tính không khóa. Đ hi u rõ hơn khái ni m này, xem xét thu c tính order_date. Nó có th t n t i đ c l p v i thu c tính order_id? Không: m t "order date" s là vô nghĩa n u không có order. Khi đó, order_date đư c g i là ph thu c vào thu c tính khóa (vì order_id là m t thu c tính khóa). Còn thu c tính customer_name thì sao— li u nó có th t n t i m t mình bên ngoài b ng orders? Có. V n có nghĩa khi nói v m t khách hàng mà không đ c p t i yêu c u mua hàng hay hóa đơn. Tương t v i các thu c tính customer_address, customer_city, và customer_state. B n thu c tính này ch ph thu c vào customer_id – m t thu c tính không khóa. Các trư ng này s thu c v m t b ng khác, c a riêng chúng, v i customer_id làm khóa chính (xem Hình I). Hình I: Tuy nhiên, đ ý trong Hình I, chúng ta đã c t đ t m i quan h gi a b ng Orders v i các thông tin v khách hàng. Do v y, chúng ta ph i khôi ph c m i quan h b ng cách t o ra m t khóa ngo i (Foreign key - FK) trong b ng Orders. Khóa ngo i v b n ch t là m t thu c tính tr t i khóa chính c a m t b ng khác. Hình J là ERD hoàn thi n c a chúng ta: 17 Hình J: ERD hoàn ch nh Quan h gi a orders và customers có th đư c di n gi i như sau: • M t order đư c t o b i m t và ch m t customer; • M t customer có th có nhi u order ho c không có order nào c . Cu i cung, đây là d li u trong b n b ng c a chúng ta. Nh không ph i tách hàng. Hình K: r ng, 3NF tách các c t, 18 Tham khảo Sau đây là m t s tài li u tham kh o h u ích: • • • The Art of Analysis, by Dr. Art Langer, devotes considerable space to normalization. Springer-Verlag Telos (January 15, 1997) ISBN: 0387949720 Báo cáo khoa h c năm 1969 c a Dr. Codd's v chu n hóa CSDL: www.acm.org/classics/nov95 The Wikipedia article on normalization bàn v 5 d ng chu n hóa: en.wikipedia.org/wiki/Database_normalization