Vinova tuyển lập trình viên Mobile & Web ở Hà Nội, lương $300-1000

Article: Chuẩn hóa cơ sở dữ liệu 16144

Software engineering
Database normalization, database design, db
phananhvu.myopenid.com 125
Updated over 4 years ago

 

 

 

Dù có nhiều ý kiến trái ngược nhưng chuẩn hóa CSDL là một việc quan trọng để có được một CSDL tốt: không còn các dị thường (update anomaly, insertion anomaly, deletion anomaly), không có dư thừa dữ liệu.

Bài này trình bày mục tiêu của chuẩn hóa, các vấn đề mà chuẩn hóa giải quyết, đồng thời trình bày một số ví dụ chuẩn hóa CSDL cho một số ứng dụng trong thực tế (Hóa đơn hàng, Cửa hàng thuốc, Nhân sự, ...). Qua đó, người đọc có thể nắm bắt kĩ thuật chuẩn hóa dễ dàng hơn.

Trong đa số các trường hợp (đặc biệt là trong những trường hợp CSDL nhỏ, chỉ vài chục bảng) thì dạng chuẩn 3NF hoặc BCNF là đạt yêu cầu. Các điều kiện để chuẩn hóa lên 4NF, 5NF, 6NF rất hiếm khi xảy ra. Do đó, như một cách nhớ vui về qui tắc chuẩn hóa từ 1NF đến BCNF, hầu như bất kì ai khi tìm hiểu về chuẩn hóa CSDL đều biết đến câu thần chú sau:

"The key, the whole key, and nothing but the key, so help me Codd."

Mục tiêu của chuẩn hóa

Loại bỏ dư thừa dữ liệu

Xem bảng dữ liệu về nhân công trong các dự án của một công ty như sau:

Project number Project name Employee number Employee name Rate category Hourly rate
1023 Madagascar travel site 11 Vincent Radebe A $60
1023 Madagascar travel site 12 Pauline James B $50
1023 Madagascar travel site 16 Charles Ramoraz C $40
1056 Online estate agency 11 Vincent Radebe A $60
1056 Online estate agency 17 Monique Williams B $50

 

Để ý cột Project name, dữ liệu về tên project lặp lại nhiều lần. Gây dư thừa dữ liệu.

Loại bỏ update anomaly

Đây là dị thường xảy ra khi cập nhật CSDL. Với bảng CSDL này, cứ mỗi khi một nhân viên thay đổi địa chỉ, tất cả các bản ghi có địa chỉ của nhân viên đó phải được cập nhật. Nhưng vì lí do nào đó, trong trường hợp này, dữ liệu về địa chỉ không được cập nhật đầy đủ, gây ra dị thường.

Loại bỏ insertion anomaly

Với bảng CSDL trên, khi có một giáo sư mới về trường, chưa kịp giảng dạy khóa nào thì thông tin về giáo sư này không thể đưa vào CSDL được. Dị thường đó được gọi là insert anomaly.

Loại bỏ deletion anomaly

Trường hợp này, nếu giáo sư thôi không dạy một môn nào đó thì thông tin về ông ta mất hết.

Phương pháp chuẩn hóa

Nguyên lí cơ bản trong chuẩn hóa CSDL là triệt tiêu dư thừa dữ liệu bằng cách phân rã các quan hệ nhưng không được làm mất thông tin.

Tutorial về chuẩn hóa CSDL

Tutorial 1 - Dùng phương pháp làm phẳng để chuẩn hóa 1NF

Ở đây có một tutorial rất hay về chuẩn hóa CSDL và vì đây là trang công nghệ thông tin chấm tiếng Việt nên tutorial đó cũng đã có bản tiếng Việt cho những bạn không thích tiếng Anh. Mời các bạn thảo luận về chuẩn hóa CSDL ở topic này.

Tutorial 2 - Dùng phương pháp tách nhóm lặp để chuẩn hóa 1NF

Đây là đề số 4 môn Kĩ thuật phần mềm kì 7 khóa 49 của lớp ĐT12 khoa ĐTVT trường ĐH Bách Khoa HN. Khác với tutorial 1 ở trên dùng phương pháp làm phẳng, tôi dùng phương pháp tách nhóm lặp để đưa về dạng 1NF trước. Để biết về phương pháp tách nhóm lặp, xin xem thêm tutorial 3.

Yêu cầu: thiết kế CSDL quản lí cửa hàng thuốc cần các thông tin sau:

DB(Mã hóa đơn, Ngày bán, Tổng tiền Hóa đơn, Mã khách hàng, Tên Khách hàng, Số ĐT Khách hàng, Mã Nhân viên, Số CMT của NV, Tên NV, Địa chỉ NV, Số ĐT Nhân viên (Mã thuốc, Tên thuốc, Công dụng, Ngày SX, Hạn SD, Số lượng, Thành tiền)).

Các phụ thuộc hàm:

  • Mã hóa đơn, Mã khách hàng, Mã nhân viên, Mã thuốc, xác định duy nhất 1 hóa đơn, 1 khách hàng, 1 NV, 1 loại thuốc.
  • Một hóa đơn có thể có nhiều thuốc nhưng chỉ do một người mua và một nhân viên bán.
  • Số CMT của NV-> Tên NV, Địa chỉ NV.

1NF - Loại bỏ nhóm lặp và loại bỏ các thuộc tính tính toán.

Cần loại 2 thuộc tính tính toán sau: Tổng tiền hóa đơn và thành tiền. Vì đây chính là dữ liệu dư thừa.

  1. Hóa đơn(Mã hóa đơn, Ngày bán, Mã khách hàng, Tên Khách hàng, Số ĐT Khách hàng, Mã Nhân viên, Số CMT của NV, Tên NV, Địa chỉ NV, Số ĐT NV)
  2. Hóa đơn - Thuốc(Mã hóa đơn, Mã thuốc, Tên thuốc, Công dụng, Ngày SX, Hạn SD, Số lượng)

2NF - Loại bỏ các phụ thuộc hàm không hoàn toàn vào khóa chính

Quan hệ Hóa đơn chỉ có khóa đơn nên quan hệ này đã ở 2NF.

Xét quan hệ Hóa đơn - Thuốc : Tên thuốc, Công dụng, Ngày SX, Hạn SD chỉ phụ thuộc vào Mã thuốc mà không phụ thuộc vào toàn khóa nên quan hệ này được tách làm 2 quan hệ sau:

  • Hóa đơn - Thuốc(Mã hóa đơn, Mã thuốc, Số lượng)
  • Thuốc(Mã thuốc, Tên thuốc, Công dụng, Ngày SX, Hạn SD)

Như vậy, ở dạng 2NF ta có 3 quan hệ:

  1. Hóa đơn(Mã hóa đơn, Ngày bán, Mã khách hàng, Tên Khách hàng, Số ĐT Khách hàng, Mã Nhân viên, Số CMT của NV, Tên NV, Địa chỉ NV, Số ĐT NV)
  2. Hóa đơn - Thuốc(Mã hóa đơn, Mã thuốc, Số lượng)
  3. Thuốc(Mã thuốc, Tên thuốc, Công dụng, Ngày SX, Hạn SD)

3NF - Loại bỏ các phụ thuộc hàm bắc cầu vào khóa chính

Ở quan hệ Hóa đơn, ta thấy Tên Khách hàng, Số ĐT Khách hàng chỉ phụ thuộc Mã khách hàng. Số CMT của NV, Tên NV, Địa chỉ NV chỉ phụ thuộc mã nhân viên. Do đó tách quan hệ này thành 3 quan hệ sau:

  • Hóa đơn(Mã hóa đơn, Ngày bán, Mã khách hàng, Mã Nhân viên)
  • Khách hàng(Mã khách hàng, Tên Khách hàng, Số ĐT Khách hàng)
  • Nhân viên(Mã Nhân viên, Số CMT của NV, Tên NV, Địa chỉ NV, Số ĐT NV)

Như vậy, ở 3NF, chúng ta có 5 quan hệ sau:

  1. Hóa đơn(Mã hóa đơn, Ngày bán, Mã khách hàng, Mã Nhân viên)
  2. Khách hàng(Mã khách hàng, Tên Khách hàng, Số ĐT Khách hàng)
  3. Nhân viên(Mã Nhân viên, Số CMT của NV, Tên NV, Địa chỉ NV, Số ĐT NV)
  4. Hóa đơn - Thuốc(Mã hóa đơn, Mã thuốc, Số lượng)
  5. Thuốc(Mã thuốc, Tên thuốc, Công dụng, Ngày SX, Hạn SD)

BCNF - Các thuộc tính quyết định phải là khóa

Ở quan hệ Nhân viên, ta thấy Số CMT của NV-> Tên NV, Địa chỉ NV nhưng Số CMT của Nhân viên không quyết định Số ĐT NV (Nhân viên có thể có nhiều số ĐT như số cầm tay, số nhà, số cơ quan, ...). Như vậy, vì Số CMT của NV là một thuộc tính quyết định (determinant) nhưng không phải khóa nên quan hệ Nhân viên chưa đạt BCNF, cần phải tách thành 2 quan hệ như sau:

  • Điện thoại - Nhân viên(Mã Nhân viên, Số CMT của NV, Số ĐT NV)
  • Nhân viên(Số CMT của NV, Tên NV, Địa chỉ NV)

Vậy, CSDL của chúng ta ở BCNF gồm 6 quan hệ sau:

  1. Hóa đơn(Mã hóa đơn, Ngày bán, Mã khách hàng, Mã Nhân viên)
  2. Khách hàng(Mã khách hàng, Tên Khách hàng, Số ĐT Khách hàng)
  3. Điện thoại - Nhân viên(Mã Nhân viên, Số CMT của NV, Số ĐT NV)
  4. Nhân viên(Số CMT của NV, Tên NV, Địa chỉ NV)
  5. Hóa đơn - Thuốc(Mã hóa đơn, Mã thuốc, Số lượng)
  6. Thuốc(Mã thuốc, Tên thuốc, Công dụng, Ngày SX, Hạn SD)

Tutorial 3 - Chuẩn hóa về BCNF

Ở đây có giáo trình CSDL đề cập về chuẩn hóa rất dễ hiểu. Trình bày khá sâu về anomaly, các dạng chuẩn giải quyết anomaly như thế nào, ...

Comments

wangld7x.pip.verisignlabs.com 1
over 4 years ago

Tôi còn nhớ buổi đầu tiên học về chuẩn hoá CSDL, giảng viên có nói rằng chuẩn hoá chỉ là cái hướng thôi còn trên thực tế thì ít có ai đạt đến 3NF. Lí do không phải là không làm được mà vì một số lí do nào đó nhất là để tăng tốc độ xử lí nên chuẩn thành ra lại rườm rà.

Và thực tế đúng là như vậy.

Lấy ví dụ luôn từ bài viết để phân tích:

1. Ví dụ dư thừa dữ liệu

Theo ví dụ thì sẽ phải phân ra 3 bảng

Bảng: Project number, Project name

Bảng: Project number, Employee number

Bảng: Employee number, Employee name, Rate Category, Hourly rate

Có một mục đích là coi lại lịch sử lương của nhân viên qua các dự án. Như vậy nếu tổ chức làm 3 bảng thì sẽ phải join cả 3 bảng lại. Điều này làm chậm tốc độ xử lí của DB. Nếu chương trình chỉ có mỗi chức năng là coi lại lịch sử lương của nhân viên thì việc tổ chức ra 3 bảng là ý kiến tồi.

2. Ví dụ update toàn bộ

Trong thiết kế không mấy ai lại update mất gốc như vậy, thay vì update địa chỉ mới cho hàng, người ta thêm một mốc ngày để xác định nhân viên này từ ngày nào đến ngày nào sống ở địa chỉ 1, từ ngày nào đến ngày nào sống ở địa chỉ 2.

3. Ví dụ 3 + 4

Phương pháp giải quyết quá dễ, hoặc là nullable, hoặc insert "". Tương tự như vậy, giải quyết ví dụ 4 = update null, hoặc update "" thay vì delete bản ghi.

Và thực tế không chỉ có vậy: Lấy báo cáo từ sổ cái, thay đổi độ dài của khoá, thay đổi số trường trong khoá...

Vậy nên, học để chuẩn hoá những gì có thể chuẩn hoá thôi chứ đừng có tư tưởng cái gì cũng đem chuẩn hoá cả.

 

phananhvu.myopenid.com 125
over 4 years ago

Có một mục đích là coi lại lịch sử lương của nhân viên qua các dự án. Như vậy nếu tổ chức làm 3 bảng thì sẽ phải join cả 3 bảng lại. Điều này làm chậm tốc độ xử lí của DB. Nếu chương trình chỉ có mỗi chức năng là coi lại lịch sử lương của nhân viên thì việc tổ chức ra 3 bảng là ý kiến tồi.

Một CSDL bao giờ cũng phục vụ hơn 1 mục đích. Ví dụ đó chỉ mang tính chất minh họa cho thao tác chuẩn hóa.

Trong thiết kế không mấy ai lại update mất gốc như vậy, thay vì update địa chỉ mới cho hàng, người ta thêm một mốc ngày để xác định nhân viên này từ ngày nào đến ngày nào sống ở địa chỉ 1, từ ngày nào đến ngày nào sống ở địa chỉ 2.

Cái này tùy thuộc vào yêu cầu về thông tin trong CSDL chứ nhỉ. Nếu tôi không quan tâm đên chỗ ở trước kia của nhân viên thì tôi đưa vào làm gì?

Tôi còn nhớ buổi đầu tiên học về chuẩn hoá CSDL, giảng viên có nói rằng chuẩn hoá chỉ là cái hướng thôi còn trên thực tế thì ít có ai đạt đến 3NF. Lí do không phải là không làm được mà vì một số lí do nào đó nhất là để tăng tốc độ xử lí nên chuẩn thành ra lại rườm rà.

....

Vậy nên, học để chuẩn hoá những gì có thể chuẩn hoá thôi chứ đừng có tư tưởng cái gì cũng đem chuẩn hoá cả.

Ý này xin đồng ý cả hai tay. Bản thân tôi cũng đã từng tham gia một project như vậy. Trong project đó, chúng tôi đã dừng lại ở 2NF.

Có người nói rằng CSDL rất lớn mới "đáng" để chuẩn hóa và càng lớn thì càng phải chuẩn hóa sâu hơn. Tuy nhiên nhiều khi việc chuẩn hóa đến đâu còn phụ thuộc vào nhiều yếu tố khác nữa.

You must login to be able to comment

Uploaded files

No file uploaded yet

You must login to be able to upload

Nhà tài trợ:

Mọi người đều tự do viết bài, sửa bài của người khác, và bình luận ở trang web này. Bạn muốn chủ động tạo bài mới để chia sẻ kinh nghiệm với mọi người? Xin click link ở dưới.

Create new content