Thông tin và dữ liệu của mô hình thiết kế cơ sở dữ liệu quan hệ (RDD) thành một tập hợp các bảng với các hàng và cột. Mỗi hàng của một quan hệ / bảng đại diện cho một bản ghi và mỗi cột đại diện cho một thuộc tính của dữ liệu. Ngôn ngữ truy vấn có cấu trúc (SQL) được sử dụng để thao tác với cơ sở dữ liệu quan hệ. Việc thiết kế một cơ sở dữ liệu quan hệ bao gồm bốn giai đoạn, trong đó dữ liệu được mô hình hóa thành một tập hợp các bảng liên quan. Các giai đoạn là -
- Xác định các quan hệ / thuộc tính
- Xác định khóa chính
- Xác định các mối quan hệ
- Chuẩn hóa
Cơ sở dữ liệu quan hệ khác với các cơ sở dữ liệu khác ở cách tiếp cận của chúng để tổ chức dữ liệu và thực hiện các giao dịch. Trong RDD, dữ liệu được tổ chức thành các bảng và tất cả các loại truy cập dữ liệu được thực hiện thông qua các giao dịch được kiểm soát. Thiết kế cơ sở dữ liệu quan hệ đáp ứng các thuộc tính ACID (tính nguyên tử, tính nhất quán, tính toàn vẹn và độ bền) được yêu cầu từ thiết kế cơ sở dữ liệu. Thiết kế cơ sở dữ liệu quan hệ yêu cầu sử dụng máy chủ cơ sở dữ liệu trong các ứng dụng để giải quyết các vấn đề về quản lý dữ liệu.
Quy trình thiết kế cơ sở dữ liệu quan hệ
Thiết kế cơ sở dữ liệu là nghệ thuật hơn là khoa học, vì bạn phải đưa ra nhiều quyết định. Cơ sở dữ liệu thường được tùy chỉnh để phù hợp với một ứng dụng cụ thể. Không có hai ứng dụng tùy chỉnh nào giống nhau và do đó, không có hai cơ sở dữ liệu nào giống nhau. Các nguyên tắc (thường là về những gì không nên làm thay vì những gì phải làm) được đưa ra khi đưa ra quyết định thiết kế này, nhưng cuối cùng các lựa chọn vẫn thuộc về nhà thiết kế.
Bước 1 - Xác định Mục đích của Cơ sở dữ liệu (Phân tích Yêu cầu)
- Thu thập các yêu cầu và xác định mục tiêu của cơ sở dữ liệu của bạn.
- Soạn thảo các biểu mẫu đầu vào, truy vấn và báo cáo thường hữu ích.
Bước 2 - Thu thập dữ liệu, sắp xếp trong bảng và chỉ định khóa chính
- Khi bạn đã quyết định về mục đích của cơ sở dữ liệu, hãy thu thập dữ liệu cần thiết để lưu trữ trong cơ sở dữ liệu. Chia dữ liệu thành các bảng dựa trên chủ đề.
- Chọn một cột (hoặc một vài cột) làm cái gọi là khóa chính, khóa này xác định duy nhất từng hàng.
Bước 3 - Tạo mối quan hệ giữa các bảng
Một cơ sở dữ liệu bao gồm các bảng độc lập và không liên quan sẽ phục vụ rất ít mục đích (bạn có thể cân nhắc sử dụng bảng tính để thay thế). Sức mạnh của cơ sở dữ liệu quan hệ nằm ở mối quan hệ có thể được xác định giữa các bảng. Khía cạnh quan trọng nhất trong việc thiết kế cơ sở dữ liệu quan hệ là xác định mối quan hệ giữa các bảng. Các loại mối quan hệ bao gồm:
- một-nhiều
- nhiều đến nhiều
- một đối một
Một-nhiều
Trong cơ sở dữ liệu "bảng phân công lớp học", một giáo viên có thể dạy không hoặc nhiều lớp học, trong khi một lớp học được dạy bởi một (và chỉ một) giáo viên. Trong cơ sở dữ liệu "công ty", người quản lý quản lý không hoặc nhiều nhân viên, trong khi một nhân viên được quản lý bởi một (và chỉ một) người quản lý. Trong cơ sở dữ liệu "bán sản phẩm", một khách hàng có thể đặt nhiều đơn hàng; trong khi một đơn đặt hàng được đặt bởi một khách hàng cụ thể. Loại mối quan hệ này được gọi là một-nhiều.
Mối quan hệ một-nhiều không thể được biểu diễn trong một bảng. Ví dụ:trong cơ sở dữ liệu "bảng phân công lớp học", chúng ta có thể bắt đầu bằng một bảng có tên là Giáo viên, bảng này lưu trữ thông tin về giáo viên (chẳng hạn như tên, văn phòng, điện thoại và email). Để lưu trữ các lớp do mỗi giáo viên dạy, chúng ta có thể tạo các cột class1, class2, class3, nhưng gặp phải vấn đề ngay lập tức về số lượng cột cần tạo. Mặt khác, nếu chúng ta bắt đầu với một bảng được gọi là Lớp lưu trữ thông tin về một lớp học, chúng ta có thể tạo thêm các cột để lưu trữ thông tin về (một) giáo viên (chẳng hạn như tên, văn phòng, điện thoại và email). Tuy nhiên, vì một giáo viên có thể dạy nhiều lớp, nên dữ liệu của nó sẽ được sao chép thành nhiều hàng trong Lớp bảng.
Để hỗ trợ mối quan hệ một-nhiều, chúng ta cần thiết kế hai bảng:ví dụ:một bảng Các lớp để lưu trữ thông tin về các lớp với classID là khóa chính; và một bảng Giáo viên để lưu trữ thông tin về giáo viên với teacherID làm khóa chính. Sau đó, chúng ta có thể tạo mối quan hệ một-nhiều bằng cách lưu trữ khóa chính của bảng Teacher (tức là teacherID) ("một" -end hoặc bảng cha) trong các lớp của bảng ("nhiều" -end hoặc bảng con), như minh họa bên dưới.
Cột teacherID trong bảng con Các lớp được gọi là khóa ngoại. Khóa ngoại của bảng con là khóa chính của bảng mẹ, được sử dụng để tham chiếu đến bảng mẹ.
Nhiều đến Nhiều
Trong cơ sở dữ liệu "bán sản phẩm", đơn đặt hàng của khách hàng có thể chứa một hoặc nhiều sản phẩm; và một sản phẩm có thể xuất hiện trong nhiều đơn đặt hàng. Trong cơ sở dữ liệu "hiệu sách", một cuốn sách được viết bởi một hoặc nhiều tác giả; trong khi một tác giả có thể viết không hoặc nhiều cuốn sách. Mối quan hệ kiểu này được gọi là nhiều-nhiều.
Hãy minh họa bằng cơ sở dữ liệu "bán sản phẩm". Chúng ta bắt đầu với hai bảng:Sản phẩm và Đơn đặt hàng. Các sản phẩm trong bảng chứa thông tin về các sản phẩm (chẳng hạn như tên, mô tả và số lượngInStock) với productID làm khóa chính của nó. Các đơn đặt hàng trong bảng chứa các đơn đặt hàng của khách hàng (ID khách hàng, dateOrdered, dateRequired và trạng thái). Một lần nữa, chúng tôi không thể lưu trữ các mục đã đặt hàng bên trong bảng Đơn đặt hàng, vì chúng tôi không biết có bao nhiêu cột để đặt trước cho các mục. Chúng tôi cũng không thể lưu trữ thông tin đặt hàng trong bảng Sản phẩm.
Để hỗ trợ mối quan hệ nhiều-nhiều, chúng ta cần tạo một bảng thứ ba (được gọi là bảng nối), chẳng hạn OrderDetails (hoặc OrderLines), trong đó mỗi hàng đại diện cho một mục của một đơn hàng cụ thể. Đối với bảng OrderDetails, khóa chính bao gồm hai cột:orderID và productID, xác định duy nhất mỗi hàng. Các cột orderID và productID trong bảng OrderDetails được sử dụng để tham chiếu đến các bảng Đơn hàng và Sản phẩm, do đó, chúng cũng là các khóa ngoại trong bảng OrderDetails.
Trên thực tế, mối quan hệ nhiều-nhiều được triển khai dưới dạng hai mối quan hệ một-nhiều, với sự ra đời của bảng nối.
Một đơn đặt hàng có nhiều mục trong OrderDetails. Một mục OrderDetails thuộc về một đơn đặt hàng cụ thể.
Một sản phẩm có thể xuất hiện trong nhiều OrderDetails. Mỗi mục OrderDetails chỉ định một sản phẩm.
One-to-One
Trong cơ sở dữ liệu "bán sản phẩm", một sản phẩm có thể có thông tin bổ sung tùy chọn như hình ảnh, thêm mô tả và nhận xét. Giữ chúng bên trong bảng Sản phẩm dẫn đến nhiều không gian trống (trong các bản ghi không có dữ liệu tùy chọn này). Hơn nữa, những dữ liệu lớn này có thể làm giảm hiệu suất của cơ sở dữ liệu.
Thay vào đó, chúng ta có thể tạo một bảng khác (chẳng hạn như ProductDetails, ProductLines hoặc ProductExtras) để lưu trữ dữ liệu tùy chọn. Bản ghi sẽ chỉ được tạo cho những sản phẩm có dữ liệu tùy chọn. Hai bảng, Sản phẩm và Chi tiết sản phẩm, thể hiện mối quan hệ một đối một. Có nghĩa là, đối với mỗi hàng trong bảng mẹ, có nhiều nhất một hàng (có thể là không) trong bảng con. ProductID cột giống nhau sẽ được sử dụng làm khóa chính cho cả hai bảng.
Một số cơ sở dữ liệu giới hạn số cột có thể được tạo bên trong bảng. Bạn có thể sử dụng mối quan hệ một-một để chia dữ liệu thành hai bảng. Mối quan hệ một-một cũng hữu ích để lưu trữ một số dữ liệu nhạy cảm nhất định trong một bảng an toàn, trong khi những dữ liệu không nhạy cảm trong bảng chính.
Loại Dữ liệu Cột
Bạn cần chọn một kiểu dữ liệu thích hợp cho mỗi cột. Các kiểu dữ liệu thông thường bao gồm số nguyên, số dấu phẩy động, chuỗi (hoặc văn bản), ngày / giờ, nhị phân, tập hợp (chẳng hạn như liệt kê và tập hợp).
Bước 4 - Tinh chỉnh &Chuẩn hóa Thiết kế
Ví dụ:
- thêm nhiều cột hơn,
- tạo một bảng mới cho dữ liệu tùy chọn bằng cách sử dụng mối quan hệ một đối một,
- chia một bảng lớn thành hai bảng nhỏ hơn,
- Các phương pháp khác.
Chuẩn hóa
Áp dụng cái gọi là quy tắc chuẩn hóa để kiểm tra xem cơ sở dữ liệu của bạn có đúng về cấu trúc và tối ưu hay không.
Dạng chuẩn đầu tiên (1NF): Một bảng là 1NF nếu mọi ô chứa một giá trị duy nhất, không phải danh sách các giá trị. Tính chất này được gọi là nguyên tử. 1NF cũng cấm một nhóm cột lặp lại như item1, item2, itemN. Thay vào đó, bạn nên tạo một bảng khác bằng cách sử dụng mối quan hệ một-nhiều.
Dạng chuẩn thứ hai (2NF) - Một bảng là 2NF nếu nó là 1NF và mọi cột không phải khóa phụ thuộc hoàn toàn vào khóa chính. Hơn nữa, nếu khóa chính được tạo thành từ nhiều cột thì mọi cột không phải khóa sẽ phụ thuộc vào toàn bộ tập hợp chứ không phải một phần của nó.
Ví dụ:khóa chính của bảng OrderDetails bao gồm orderID và productID. Nếu unitPrice chỉ phụ thuộc vào productID, nó sẽ không được lưu trong bảng OrderDetails (nhưng trong bảng Products). Mặt khác, nếu đơn giá phụ thuộc vào sản phẩm cũng như đơn đặt hàng cụ thể, thì đơn giá đó sẽ được lưu trong bảng OrderDetails.
Dạng chuẩn thứ ba (3NF) - Một bảng là 3NF nếu nó là 2NF và các cột không khóa độc lập với nhau. Nói cách khác, các cột không phải khóa phụ thuộc vào khóa chính, chỉ phụ thuộc vào khóa chính và không có gì khác. Ví dụ:giả sử chúng ta có bảng Sản phẩm với các cột productID (khóa chính), tên và đơn vị Giá. Cột giảm giá sẽ không thuộc bảng Sản phẩm nếu nó cũng phụ thuộc vào đơn vị Giá, không phải là một phần của khóa chính.
Biểu mẫu Bình thường Cao hơn: 3NF có những điểm chưa phù hợp, dẫn đến dạng Bình thường cao hơn, chẳng hạn như Dạng thông thường Boyce / Codd, Dạng chuẩn thứ tư (4NF) và Dạng chuẩn thứ năm (5NF), nằm ngoài phạm vi của hướng dẫn này.
Đôi khi, bạn có thể quyết định phá vỡ một số quy tắc chuẩn hóa vì lý do hiệu suất (ví dụ:tạo một cột có tên là totalPrice trong bảng Đơn hàng có thể được lấy từ các bản ghi orderDetails); hoặc bởi vì người dùng cuối yêu cầu nó. Đảm bảo rằng bạn nhận thức đầy đủ về nó, phát triển logic lập trình để xử lý nó và ghi lại quyết định một cách chính xác.
Quy tắc Liêm chính
Bạn cũng nên áp dụng các quy tắc về tính toàn vẹn để kiểm tra tính toàn vẹn của thiết kế -
1. Quy tắc toàn vẹn đối tượng - Khóa chính không được chứa NULL. Nếu không, nó không thể xác định duy nhất hàng. Đối với khóa tổng hợp được tạo thành từ một số cột, không cột nào có thể chứa NULL. Hầu hết các RDBMS kiểm tra và thực thi quy tắc này.
2 Quy tắc toàn vẹn cấp số nhân - Mỗi giá trị khóa ngoại phải được khớp với một giá trị khóa chính trong bảng được tham chiếu (hoặc bảng mẹ).
Bạn chỉ có thể chèn một hàng có khóa ngoại vào bảng con nếu giá trị tồn tại trong bảng mẹ.
Nếu giá trị của khóa thay đổi trong bảng mẹ (ví dụ:hàng được cập nhật hoặc bị xóa), tất cả các hàng có khóa ngoại này trong (các) bảng con phải được xử lý tương ứng. Bạn có thể (a) không cho phép các thay đổi; (b) sắp xếp thay đổi (hoặc xóa các bản ghi) trong các bảng con một cách tương ứng; (c) đặt giá trị khóa trong các bảng con thành NULL.
Hầu hết RDBMS có thể được thiết lập để thực hiện kiểm tra và đảm bảo tính toàn vẹn của tham chiếu, theo một cách thức cụ thể.
3 Tính toàn vẹn của logic kinh doanh - Bên cạnh hai quy tắc toàn vẹn chung ở trên, có thể có tính toàn vẹn (xác thực) liên quan đến logic kinh doanh, ví dụ:mã zip phải có 5 chữ số trong một phạm vi nhất định, ngày và giờ giao hàng sẽ rơi vào giờ làm việc; số lượng đặt hàng phải bằng hoặc ít hơn số lượng trong kho, v.v. Những điều này có thể được thực hiện theo quy tắc vô hiệu (đối với cột cụ thể) hoặc logic lập trình.
Lập chỉ mục cột
Bạn có thể tạo chỉ mục trên (các) cột đã chọn để tạo điều kiện thuận lợi cho việc tìm kiếm và truy xuất dữ liệu. Chỉ mục là một tệp có cấu trúc tăng tốc độ truy cập dữ liệu cho CHỌN nhưng có thể làm chậm CHÈN, CẬP NHẬT và XÓA. Không có cấu trúc chỉ mục, để xử lý truy vấn CHỌN với tiêu chí phù hợp (ví dụ:CHỌN * TỪ Khách hàng WHERE name ='Tan Ah Teck'), công cụ cơ sở dữ liệu cần so sánh mọi bản ghi trong bảng. Một chỉ mục chuyên biệt (ví dụ:trong cấu trúc BTREE) có thể đạt được bản ghi mà không cần so sánh mọi bản ghi. Tuy nhiên, chỉ mục cần được tạo lại bất cứ khi nào bản ghi được thay đổi, điều này dẫn đến chi phí liên quan đến việc sử dụng chỉ mục.
Chỉ mục có thể được xác định trên một cột, một tập hợp các cột (được gọi là chỉ mục nối) hoặc một phần của cột (ví dụ:10 ký tự đầu tiên của VARCHAR (100)) (được gọi là chỉ mục một phần) . Bạn có thể tạo nhiều hơn một chỉ mục trong một bảng. Ví dụ:nếu bạn thường tìm kiếm khách hàng bằng tên customerName hoặc số điện thoại, bạn có thể tăng tốc tìm kiếm bằng cách tạo chỉ mục trên cột customerName, cũng như phoneNumber. Hầu hết RDBMS xây dựng chỉ mục trên khóa chính một cách tự động.