Mô hình quan hệ
Codd đã đề xuất mô hình dữ liệu quan hệ năm 1970. Trong thời gian đó, hầu hết hệ thống cơ sở dữ liệu đều dựa trên hai mô hình dữ liệu cũ (mô hình phân cấp và mô hình mạng); mô hình quan hệ thực sự là một cuộc cách mạng trong lĩnh vực cơ sở dữ liệu và nó ...
Codd đã đề xuất mô hình dữ liệu quan hệ năm 1970. Trong thời gian đó, hầu hết hệ thống cơ sở dữ liệu đều dựa trên hai mô hình dữ liệu cũ (mô hình phân cấp và mô hình mạng); mô hình quan hệ thực sự là một cuộc cách mạng trong lĩnh vực cơ sở dữ liệu và nó nhanh chóng thay chỗ cho các mô hình trước. Các hệ quản trị cơ sở dữ liệu nguyên mẫu đã được phát triển đầu tiên trong những dự án của IBM và UC-Berkeley vào giữa những năm 70, và một vài nhà sản xuất đã đưa ra những sản phẩm của cơ sở dữ liệu quan hệ một thời gian ngắn sau đó. Ngày nay, mô hình quan hệ là mô hình dữ liệu phổ biến nhất và được sử dụng trong các sản phẩm DBMS, bao gồm IBM's DB2, Informix, Oracle, Sybase, Microsoft's Access và SQLServer, FoxBase, và Paradox. Hệ quản trị cơ sở dữ liệu quan hệ có mặt khắp nơi trên thị trường và được coi là ngành công nghiệp nhiều tỷ đô la.
SQL: là ngôn ngữ truy vấn dữ liệu (Structured Query Language) được IBM phát triến. SQL đã trở thành ngôn ngữ được sử dụng rộng rãi nhất để tạo, thực thi và truy vấn dữ liệu trong các DBMS. Cho đến nay, rất nhiều nhà sản xuất đã đưa ra các sản phẩm khác nhau của SQL, vì thế cần có một chuẩn được định nghĩa là 'SQL chính thức'. Sự tồn tại của chuẩn này cho phép người sử dụng đánh giá phiên bản SQL đang sử dụng. Nó cũng cho phép người sử dụng phân biệt các đặc trưng riêng biệt của SQL so với phiên bản chuẩn; những ứng dụng hỗ trợ các đặc trưng riêng biệt này thường ít gặp.
Phiên bản SQL chuẩn đầu tiên được phát triển vào năm 1986 bởi American National Standards Institute (ANSI), và được gọi là SQL-86. Phiên bản tiếp theo ra đời năm 1989 được gọi là SQL-89, bản chính thức ra đời năm 1992 và được gọi là SQL-92. International Standards Organization (ISO) đã kết hợp với ANSI để phát triển SQL-92. Hầu hết các DBMS thương mại hỗ trợ SQL-92.
là mô hình đơn giản và dễ sử dụng; cơ sở dữ liệu là tập hợp của một hoặc nhiều quan hệ, mỗi quan hệ là một bảng gồm các dòng và các cột. Bảng này có thể giúp người sử dụng bình thường hiểu được nội dung của cơ sở dữ liệu, và cho phép sử dụng nó đơn giản bằng cách sử dụng ngôn ngữ truy vấn dữ liệu bậc cao. Những ưu điểm chính của mô hình quan hệ so với mô hình dữ liệu cũ là dữ liệu trong đó được biểu diễn đơn giản và những truy vấn phức tạp dễ dàng thực hiện.
Trong khi chúng ta tập trung vào những khái niệm dưới đây, chúng tôi cũng giới thiệu về ngôn ngữ định nghĩa dữ liệu (Data Definition Language-DDL) của SQL-92, ngôn ngữ chuẩn để tạo, thực thi và truy vấn dữ liệu trong các hệ quản trị cơ sở dữ liệu quan hệ. Điều này cho phép chúng ta có lý do để bàn tới những khái niệm khác trong các hệ thống cơ sở dữ liệu.
Chúng ta sẽ bàn tới khái niệm quan hệ trong Phần 1 và làm thế nào để tạo ra một quan hệ sử dụng ngôn ngữ SQL. Một thành phần quan trọng của mô hình dữ liệu là một tập các cấu trúc trong những trường hợp xác định để đảm bảo sự đúng đắn của dữ liệu. Những điều kiện đó gọi là các ràng buộc toàn vẹn. Chúng ta biểu diễn ràng buộc toàn vẹn trong mô hình quan hệ trong Phần 2, sau đó sẽ bàn tới phần SQL hỗ trợ ràng buộc toàn vẹn như thế nào. Chúng tôi cũng sẽ trình bày cách DBMS thiết đặt ràng buộc tham chiếu trong Phần 3.
Trong phần 4, chúng ta sẽ quay lại với cách thức truy vấn dữ liệu trong cơ sở dữ liệu, ngôn ngữ truy vấn, và giới thiệu những đặc trưng truy vấn của SQL, phần này sẽ được bàn tới sâu hơn trong một chương phía sau.
Sau đó, chúng tôi sẽ trình bày các bước để chuyển từ lược đồ ER sang lược đồ cơ sở dữ liệu quan hệ trong phần 5. Cuối cùng, chúng tôi giới thiệu về khung nhìn, bảng sử dụng truy vấn trong Phần 6. Khung nhìn có thể được sử dụng để định nghĩa lược đồ ngoài của cơ sở dữ liệu và vì thế nó hỗ trợ độc lập dữ liệu logic trong mô hình quan hệ. Phần 7 sẽ bàn tới các lệnh SQL để xóa, sửa bảng và khung nhìn.
Cuối cùng Phần 8 sẽ mở rộng thiết kế của ví dụ Cửa hàng Internet, bằng việc chỉ ra cách ánh xạ từ lược đồ ER sang mô hình quan hệ, và sử dụng các khung nhìn có thể hỗ trợ thiết kế này.
Cấu trúc chính để biểu diễn dữ liệu trong mô hình quan hệ là các quan hệ. Một quan hệ chứa lược đồ quan hệ và minh họa quan hệ. Một trường hợp quan hệ là một bảng, và lược đồ quan hệ biểu diễn những tên cột trong bảng. Chúng ta đầu tiên biểu diễn lược đồ quan hệ, sau đó đến minh họa quan hệ. Lược đồ chỉ ra tên quan hệ, tên của các trường (hoặc cột, hoặc thuộc tính), và miền giá trị của mỗi trường. Miền được tham chiếu tới lược đồ quan hệ bằng tên miền và tập những giá trị mà trường đó có thể nhận.
Chúng ta sử dụng ví dụ của thông tin về sinh viên trong cơ sở dữ liệu trường đại học ở chương 1 làm minh họa cho phần lược đồ quan hệ:
Students(sid: string, name: string, login: string, age: integer, gpa: real)
Trong ví dụ trên, trường sid có miền trị là string. Tập các giá trị có thể có của miền này là tất cả các ký tự.
Bây giờ, chúng ta quay lại với minh họa quan hệ. Minh họa của quan hệ là tập các bộ giá trị, hay còn gọi là các bản ghi, và mỗi bộ giá trị có cùng số lượng các trường như lược đồ quan hệ. Minh họa quan hệ có thể được nghĩ như một bảng, trong đó có các dòng, và tất cả các dòng có cùng số lượng các trường. (Thuật ngữ minh họa quan hệ thường được sử dụng để chỉ một quan hệ).
Minh họa S1 của quan hệ Students
Một minh họa của quan hệ Students được chỉ ra trong Hình 1. Minh họa S1 chứa 6 bộ giá trị và 5 trường. Ghi nhớ rằng, không có hai dòng có cùng định danh. Điều này là một yêu cầu của mô hình quan hệ, mỗi quan hệ phải có một tập các thuộc tính đóng vai trò định danh, giúp ta phân biệt được bộ giá trị này với bộ giá trị khác. Thứ tự các dòng trong bảng không quan trọng.
Trong Hình 2 chỉ ra một minh họa khác của quan hệ. Nếu như các trường được đặt tên, như trong định nghĩa lược đồ và các hình mô tả các minh họa quan hệ, thứ thự của các trường không phải là vấn đề. Tuy nhiên, nên đặt các trường theo thứ tự xác định và tham chiếu đến các trường theo thứ tự của nó. Do đó, sid là trường 1 của Students, login là trường 3 của Students, vv…Nếu làm theo cách này thì thứ tự các trường lại trở nên quan trọng. Ví dụ, trong SQL, tên của các trường được sử dụng trong các câu lệnh truy vấn dữ liệu và thứ tự các trường cũng được quan tâm khi chúng ta thêm một bộ dữ liệu mới.
Một biểu diễn khác của minh họa quan hệ Students
Lược đồ quan hệ chỉ ra miền giá trị của mỗi trường (cột) trong minh họa quan hệ. Những ràng buộc miền trong lược đồ xác định điều kiện mà mỗi minh họa của quan hệ cần phải thỏa mãn: Giá trị xuất hiện trong mỗi cột phải nằm trong miền xác định của cột đó. Vì thế, miền của mỗi trường cần chỉ ra kiểu dữ liệu của trường đó và những giới hạn giá trị mà trường đó có thể nhận.
Để hiểu rõ ràng, chúng ta xem xét lược đồ quan hệ R(f1: D1,…fn : Dn), và mỗi fi , 1 ≤ i ≤ n, và Domi là tập giá trị nằm trong miền. Một minh họa của R thỏa mãn ràng buộc miền trong lược đồ là tập hợp các bộ giá trị gồm n trường:
{<f1: d1, …, fn: dn> | d1∈ Dom1, …, dn∈ Domn}Phần nằm trong dấu <…> chỉ ra những trường của bộ giá trị. Sử dụng ký hiệu này, bộ giá trị Students đầu tiên chỉ ra trong Hình 1 được viết như sau:
<sid: 50000, name: Dave, login: dave@cs, age: 19, gpa: 3.3>
Ký hiệu {…} chỉ ra tập các bộ giá trị. Những biểu diễn ở bên phải ký hiệu | trong công thức chỉ ra miền giá trị của các trường tương ứng ghi ở bên trái. Vì thế, minh họa của R được định nghĩa như là tập các bộ giá trị. Những trường của mỗi bộ giá trị phải tương ứng với những trường trong lược đồ quan hệ.
Ràng buộc miền là khái niệm rất cơ bản trong mô hình quan hệ vì thế từ nay chúng ta sẽ chỉ xem xét những minh họa quan hệ thỏa mãn các miền giá trị của nó. Do đó, minh họa quan hệ nghĩa là những minh họa thỏa mãn miền giá trị trong lược đồ quan hệ.
Bậc của quan hệ là số lượng các trường trong quan hệ. Lực lượng là số lượng các bộ giá trị trong qaun hệ. Trong Hình 1, bậc của quan hệ (số các cột) là 5, và lực lượng trong minh hoạ này là 6.
Cơ sở dữ liệu quan hệ là tập hợp các quan hệ có tên khác nhau. Lược đồ cơ sở dữ liệu quan hệ là tập hợp các lược đồ quan hệ trong cơ sở dữ liệu. Ví dụ, trong Chương 1, chúng ta bàn tới cơ sở dữ liệu của một trường đại học với các quan hệ Students, Faculty, Courses, Rooms, Enrolled, Teaches, và Meets_In. Minh hoạ của cơ sở dữ liệu quan hệ là tập hợp các minh hoạ của các quan hệ, tất nhiên mỗi minh họa của một quan hệ phải thoả mãn miền giá trị của lược đồ đó.
Tạo và sửa quan hệ sử dụng SQL-92
Ngôn ngữ SQL-92 chuẩn sử dụng từ bảng thay thế cho từ quan hệ. Tập những lệnh SQL hỗ trợ việc tạo, xoá, và sửa bảng gọi là Ngôn ngữ định nghĩa dữ liệu.
Lệnh CREATE TABE được sử dụng để định nghĩa bảng mới. Để tạo ra bảng Students, chúng ta có thể sử dụng câu lệnh sau:
CREATE TABLE Students ( sid CHAR(20), name CHAR(30), login CHAR(20), age INTEGER, gpa REAL )
Các bộ giá trị được thêm vào bằng cách sử dụng câu lệnh INSERT. Chúng ta có thể thêm vào bảng một bộ giá trị như sau:
INSERT INTO Students(sid, name, login, age, gpa)
VALUES(53688, 'Smith', 'smith@ee', 18, 3.2)
Chúng ta thường bỏ qua việc liệt kê tên các cột trong mệnh đề INTO nếu giá trị được thêm vào đã theo đúng thứ tự trong định nghĩa lược đồ quan hệ.
Chúng ta có thể xoá các bộ giá trị sử dụng lệnh DELETE. Chúng ta có thể xoá bộ tất cả các bộ giá trị có name là Smith bằng lệnh sau:
DELETE FROM Students S WHERE S.name = 'Smith'
Chúng ta có thể sửa giá trị trong một dòng đang tồn tại bằng lệnh UPDATE. Ví dụ, chúng ta tăng tuổi (age) và giảm điểm trung bình (gpa) của sinh viên có sid bằng 53688 sử dụng câu lệnh sau:
UPDATE Students S
SET S.age = S.age + 1, S.gpa = S.gpa - 1
WHERE S.sid = 53688
Mệnh đề WHERE được sử dụng để xác định những dòng nào cần sửa. Mệnh đề SET được dùng để xác định dòng đó được sửa như thế nào. Bên phải dấu '=' là giá trị mới của cột được sửa. Để minh hoạ cho câu lệnh này, chúng ta xem xét ví dụ sau:
UPDATE Students S
SET S.gpa = S.gpa - 0.1
WHERE S.gpa >= 3.3
Nếu câu lệnh này được áp dụng cho minh hoạ dữ liệu S1 của quan hệ Students trong Hình 1, chúng ta sẽ nhận được kết quả như trong Hình 3.
Minh hoạ quan hệ S1 của Students sau khi Update
Các ràng buộc toàn vẹn trên quan hệ
Cơ sở dữ liệu chỉ có ích khi có thông tin đúng được lưu trữ trong nó, và DBMS vì thế phải tránh được các dữ liệu không đúng. Ràng buộc toàn vẹn là những yêu cầu thiết đặt trên các lược đồ quan hệ, và giới hạn những dữ liệu nào có thể được lưu trữ trong lược đồ đó. Nếu một minh họa dữ liệu thỏa mãn tất cả các ràng buộc toàn vẹn trên lược đồ cơ sở dữ liệu đó thì minh họa đó gọi là minh họa hợp lệ. DBMS hỗ trợ thiết đặt các ràng buộc tham chiếu, chỉ cho phép những minh họa dữ liệu hợp lệ được lưu trữ trong cơ sở dữ liệu.
Ràng buộc tham chiếu được xác định và thiết đặt ở nhiều thời điểm khác nhau:
- Khi người quản trị và người sử dụng cuối định nghĩa lược đồ cơ sở dữ liệu, anh/cô ấy xác định có những ràng buộc tham chiếu nào có trong cơ sở dữ liệu.
- Khi một ứng dụng cơ sở dữ liệu thực thi, người quản trị kiểm tra những vi phạm dữ liệu và không cho phép những thay đổi vi phạm ràng buộc tham chiếu. Trong một số trường hợp, DBMS có thể thêm vào một số các thay đổi tới dữ liệu để đảm bảo các minh họa dữ liệu thỏa mãn các ràng buộc tham chiếu. Trong những trường hợp khác, không được phép tạo ra những minh họa dữ liệu vi phạm bất kỳ ràng buộc tham chiếu nào.
Rất nhiều loại ràng buộc tham chiếu có thể được xác định trong mô hình quan hệ. Chúng ta đã nhìn thấy một ví dụ về ràng buộc tham chiếu trong các ràng buộc miền giá trị (Phần 1). Tóm lại, nhiều loại khác nhau của ràng buộc có thể được xác định dễ dàng; ví dụ, không có hai sinh viên có cùng giá trị sid. Trong phần này, chúng ta bàn tới những ràng buộc tham chiếu, khác với ràng buộc miền giá trị, phần mà người quản trị hoặc người dùng cuối có thể xác định trong mô hình quan hệ
Xem xét quan hệ Students và ràng buộc về hai sinh viên không có cùng sid. Ràng buộc này là một ví dụ về ràng buộc khóa. Ràng buộc khóa được phát biểu rằng: một tập con nhỏ nhất các trường của quan hệ giúp xác định một bộ giá trị là duy nhất. Tập các trường giúp xác định duy nhất một bộ giá trị được gọi là khóa dự tuyển trong quan hệ; chúng ta thường gọi đơn giản là khóa. Trong trường hợp của quan hệ Students, trường sid là khóa dự tuyển.
Hãy cùng tìm hiểu sâu hơn định nghĩa trên về khóa dự tuyển. Định nghĩa có hai phần:
- Hai bộ giá trị khác nhau trong minh họa dữ liệu hợp lệ (minh họa thỏa mãn tất cả các ràng buộc tham chiếu, bao gồm cả ràng buộc khóa) không thể có giá trị trùng nhau trong các trường đóng vai trò là khóa.
- Không có một tập con của các trường trong khóa có thể xác định duy nhất môt bộ giá trị (Khóa là một tập tối giản).
Phần đầu tiên của định nghĩa có nghĩa là trong bất kỳ một minh họa dữ liệu hợp lệ, các giá trị của các trường khóa phải xác định một bộ giá trị là duy nhất trong minh họa quan hệ. Khi xác định ràng buộc khóa, người quản trị hoặc người dùng phải đảm bảo rằng ràng buộc này sẽ không ngăn cản họ lưu trữ những bộ dữ liệu 'hợp lệ'. (Lời nhắc tương tự được áp dụng để xác định chuẩn xác những loại ràng buộc tham chiếu).'Hợp lệ' ở đây phụ thuộc vào trạng thái tự nhiên của dữ liệu phải lưu trữ. Ví dụ, một số sinh viên có thể có tên trùng nhau, nếu trường tên được xác định là khóa, DBMS sẽ không cho phép nhập hai sinh viên có cùng tên vào quan hệ Students, như vậy là không phù hợp với trạng thái tự nhiên của dữ liệu.
Phần thứ hai của định nghĩa có nghĩa là, ví dụ, tập các trường {sid, name} không phải là khóa của quan hệ Students, vì tập này chứa thuộc tính sid có khả năng làm khóa. Tập {sid, name} là một ví dụ về siêu khóa, tập của những trường chứa khóa.
Hãy nhìn lại minh họa của quan hệ Students trong Hình 1. Quan sát hai dòng khác nhau luôn có giá trị sid khác nhau; sid là khóa và xác định duy nhất một bộ giá trị. Tuy nhiên, điều này không cần thiết đối với các trường không phải là khóa. Ví dụ, quan hệ có thể chứa hai dòng có trường name là Smith, vì name không phải là khóa.
Ghi nhớ rằng tất cả quan hệ phải đảm bảo có khóa. Vì một quan hệ là một tập các bộ giá trị, một tập hợp của tất cả các trường sẽ luôn luôn là siêu khóa. Một vài tập con của các trường có thể hình thành nên khóa, nhưng nếu không, tập tất cả các trường sẽ là khóa.
Quan hệ có thể có một vài khóa dự tuyển. Ví dụ, hai trường login và age của quan hệ Students có thể xác định duy nhất một sinh viên. Vì thế tập {login, age} cũng có thể làm khóa. Nếu giả sử rằng, chỉ có trường login làm khóa, thì hai dòng trong cùng một minh họa có thể có cùng giá trị login. Tuy nhiên, khóa phải xác định được bộ giá trị duy nhất trong tất cả các minh họa hợp lệ của quan hệ. Nếu xác định {login, age} là khóa, thì người dùng không được phép nhập hai sinh viên có cùng cả login và cả age trùng nhau.
Trong tất cả các khóa dự tuyển, người thiết kế cơ sở dữ liệu chọn ra một khóa chính. Thực tế, khi biết khóa chính, ta có thể tham chiếu tới tất cả các trường liên quan đến khóa chính đó. Ví dụ, chúng ta có thể tham chiếu tới một bộ giá trị trong quan hệ Students nếu ta biết giá trị sid của nó. Trên nguyên tắc, chúng ta có thể sử dụng bất kỳ khóa nào, không chỉ là khóa chính để tham chiếu tới một bộ giá trị. Tuy nhiên, sử dụng khóa chính là thích hợp hơn bởi vì nó là cái mà DBMS hy vọng- đây là ý nghĩa của việc chỉ định một khóa dự tuyển nào đó là khóa chính và tối giản nó. Ví dụ, DBMS có thể tạo ra chỉ số trên những trường khóa chính để tìm kiếm theo khóa, truy cập đến một bộ giá trị sử dụng khóa chính của nó sẽ hiệu quả cao hơn. Ý nghĩa của việc tham chiếu tới một bộ giá trị được nghiên cứu sâu hơn trong phần sau.
Xác định ràng buộc khóa bằng SQL-92
Trong SQL chúng ta có thể khai báo một tập con các cột của bảng đóng vai trò là khóa bằng sử dụng ràng buộc UNIQUE. Trong đó, nhiều nhất một khóa dự tuyển có thể được khai báo là khóa chính, sử dụng ràng buộc PRIMARY KEY. (SQL không yêu cầu những ràng buộc này phải được khai báo khi định nghĩa bảng).
Cùng xem xét ví dụ sau đây về định nghĩa bảng và xác định ràng buộc:
CREATE TABLE Students ( sid CHAR(20), name CHAR(30), login CHAR(20), age INTEGER, gpa REAL, UNIQUE (name, age), CONSTRAINT StudentsKey PRIMARY KEY (sid) )
Định nghĩa này chỉ ra sid là khóa chính và kết hợp giữa name và age cũng có thể làm khóa. Định nghĩa của khóa chính cũng minh họa cách làm thế nào chúng ta có thể đặt tên cho ràng buộc, tên ràng buộc được đặt phía sau từ khóa CONSTRAINT tên ràng buộc. Nếu ràng buộc bị vi phạm, tên ràng buộc sẽ trả về để dễ dàng xác định lỗi.
Một số thông tin lưu trữ trong một quan hệ có thể được liên kết tới những thông tin được lưu trữ trong một quan hệ khác. Nếu một trong những quan hệ bị sửa, những quan hệ khác phải được kiểm tra lại và có thể sửa theo để đảm bảo sự nhất quán dữ liệu. Những ràng buộc tham chiếu thông thường bao gồm hai quan hệ và được gọi là ràng buộc khoá ngoại.
Giả sử rằng bổ sung vào quan hệ Students, ta có thêm một quan hệ thứ hai:
Enrolled(sid: string, cid: string, grade: string)
Để đảm bảo rằng chỉ có những sinh viên thực sự mới có thể đăng ký vào những khóa học, bất kỳ giá trị nào xuất hiện trong trường sid của minh họa quan hệ Enrolled cũng phải xuất hiện trong trường sid trong một bộ giá trị nào đó của quan hệ Students. Trường sid của Enrolled được gọi là khóa ngoại và tham chiếu tới quan hệ Students. Khóa ngoại trong quan hệ tham chiếu (trong ví dụ của chúng ta là Enrolled) phải phù hợp với khóa chính trong quan hệ được tham chiếu (Students), ví dụ, nó phải có cùng số lượng các cột và kiểu dữ liệu tương thích, mặc dù tên các cột có thể khác nhau.
Ràng buộc này được minh họa trong Hình 4. Như chúng ta thấy, có một số sinh viên không được tham chiếu từ Enrolled (ví dụ, sinh viên có sid=50000). Tuy nhiên, tất cả các giá trị sid xuất hiện trong bảng Enrolled đều phải xuất hiện trong cột khóa chính của dòng nào đó trong bảng Students.
Ràng buộc tham chiếu
Nếu chúng ta cố gắng thêm một bộ giá trị {55555, Art104, A} vào E1, ràng buộc tham chiếu bị vi phạm bởi vì không có bộ giá trị nào của S1 có id= 55555; hệ thống cơ sở dữ liệu nên từ chối việc thêm trên. Tương tự, chúng ta xóa bộ giá trị {53666, Jones, jones@cs, 18, 3.4} từ S, chúng ta vi phạm ràng buộc khóa ngoại bởi vì bộ giá trị {53666, History105, B} trong E1 chứa sid bằng 53666, là sid của bộ giá trị trong Students bị xóa. DBMS không nên cho phép xóa hoặc nếu xóa bộ giá trị này trong Enrolled thì cũng xóa luôn nó trong Students. Chúng ta sẽ bàn đến ràng khóa khóa ngoại và ảnh hưởng của nó tới việc cập nhật dữ liệu trong Phần 3.3.
Cuối cùng, chúng ta ghi nhớ rằng khóa ngoại có thể tham chiếu trong cùng một quan hệ. Ví dụ, chúng ta có thể thêm vào quan hệ Students một cột mới gọi là Partner (người cộng tác) và khai báo cột này là khóa ngoại tham chiếu tới chính quan hệ Students. Thực tế, tất cả các sinh viên đều có thể có người cộng tác, và người này cũng chính là sinh viên, tức là trường Partner chứa giá trị sid của các cộng sự. Người đọc có thể đặt ra câu hỏi: "Điều gì xảy ra nếu một sinh viên không có bất kỳ người cộng sự nào?". Tình trạng này được quản lý trong SQL bằng việc sử dụng giá trị đặc biệt gọi là giá trị Null (Rỗng). Việc sử dụng Null trong một trường của một bộ giá trị chỉ ra rằng trường đó không có giá trị hoặc có nhưng chúng ta không biết (ví dụ, chúng ta không biết người cộng sự của sinh viên đó, hoặc người đó không có cộng sự nào). Sự xuất hiện của null trong trường khóa ngoại không vi phạm ràng buộc khóa. Tuy nhiên, những giá trị null không được phép xuất hiện trong trường khóa chính (bởi vì trường khóa chính được sử dụng để xác định một bộ giá trị là duy nhất). Chúng ta sẽ bàn thêm về giá trị null trong Chương 5.
Xác định ràng buộc khóa ngoại bằng SQL-92.
Chúng ta xem xét định nghĩa Enrolled (sid: string, cid: string, grade: string):
CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20), grade CHAR(10), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students )
Ràng buộc khóa ngoại chỉ ra rằng tất cả các giá trị sid trong Enrolled phải xuất hiện trong Students, vì sid trong Enrolled là khóa ngoại đang tham chiếu tới Students.
Nhân đây cũng đề cập thêm, ràng buộc khóa chính trên chỉ ra một sinh viên có chính xác một kết quả (Grade) trong một khóa học đã tham gia. Nếu chúng ta muốn ghi lạ nhiều hơn một kết quả của một sinh viên trong một khóa học, chúng ta phải thay đổi ràng buộc khóa chính trên.
Ràng buộc miền, khóa chính và khóa ngoại được coi là những khái niệm cơ bản của mô hình dữ liệu quan hệ và được đặc biệt quan tâm trong hầu hết các hệ thống quản trị cơ sở dữ liệu thương mại. Tuy nhiên, trong một số trường hợp, chúng ta cần xác định thêm các ràng buộc khác.
Ví dụ, chúng ta có thể yêu cầu tuổi của sinh viên phải nằm trong một dải giá trị nhất định; điều này được cũng được coi là ràng buộc tham chiếu, DBMS sẽ không chấp nhận thêm hoặc cập nhật những giá trị vi phạm ràng buộc này. Điều này rất hữu ích trong việc tránh những dữ liệu sai nhập vào cơ sở dữ liệu. Nếu chúng ta xác định rằng, tất cả sinh viên phải có tuổi ít nhất là 16, minh họa quan hệ Students chỉ ra trong Hình 1 trở nên không hợp lệ vì có hai sinh viên có tuổi dưới 16. Nếu chúng ta không cho phép thêm hai bộ giá trị này, chúng ta sẽ có minh họa dữ liệu hợp lệ như trong Hình 5.
Minh họa S2 của quan hệ Students
Ràng buộc tham chiếu chỉ ra tất cả sinh viên không có tuổi dưới 16 có thể được xem như là mở rộng của ràng buộc miền giá trị, ngoài việc định nghĩa kiểu dữ liệu cho trường age là integer, chúng ta còn cần định nghĩa thêm miền giá trị mà nó có thể nhận. Tóm lại, tất cả các ràng buộc, miền, khóa, khóa ngoại đều có thể được xác định. Ví dụ, chúng ta có thể yêu cầu tất cả các sinh viên có tuổi lớn hơn 18 thì kết quả học tập (grade) phải lớn hơn 3.
Các hệ thống cơ sở dữ liệu quan hệ hỗ trợ các ràng buộc phổ biến theo dạng của các ràng buộc và xác nhận bảng. Ràng buộc bảng được hỗ trợ trên từng bảng và được kiểm tra bất cứ khi nào bảng được sửa. Ngược lại, xác nhận bảng có thể bao gồm nhiều bảng và được kiểm tra bất cứ khi nào một bảng trong đó được sửa. Cả hai loại ràng buộc và xác nhận bảng đều có thể sử dụng câu lệnh SQL để chỉ ra giới hạn mong muốn đối với dữ liệu.
Hiệu quả của các ràng buộc tham chiếu
Như chúng ta quan sát ở trên, các ràng buộc tham chiếu được xác định khi tạo các quan hệ và nó phát huy tác dụng khi quan hệ có sự thay đổi. Tác động của các ràng buộc miền, PRIMARY KEY, và UNIQUE rất dễ hiểu: nếu câu lệnh thêm, xóa hoặc cập nhật làm vi phạm sự đúng đắn của dữ liệu thì câu lệnh đó bị từ chối thực hiện. Khả năng vi phạm được kiểm tra ở cuối mỗi thực hiện câu lệnh, tuy vậy việc trì hoãn kiểm tra có thể lui lại ở cuối giao dịch, chúng ta sẽ xem xét vấn đề này ở Chương 18.
Xem xét minh họa S1 của quan hệ Students ở Hình 1. Việc thêm dữ liệu sau đây vi phạm ràng buộc khóa chính bởi vì đã có một bộ giá trị có sid là 53688 và DBMS sẽ từ chối thực hiện câu lệnh này.
INSERT INTO Students(sid, name, login, age, gpa) VALUES(53688,'Mike','mike@ee',17, 3.4)
Câu lệnh INSERT sau đây vi phạm ràng buộc vì khóa chính không thể chứa giá trị Null:
INSERT INTO Students(sid, name, login, age, gpa) VALUES(null,'Mike','mike@ee',17, 3.4)
Tất nhiên, cũng gặp phải vấn đề tương tự khi chúng ta cố gắng thêm một bộ giá trị mà giá trị trong trường đó không nằm trong miền giá trị của nó, ví dụ, khi chúng ta vi phạm ràng buộc miền giá trị. Xóa dữ liệu không phải là nguyên nhân vi phạm ràng buộc miền, khóa chính, ràng buộc xác định duy nhất. Tuy nhiên, việc cập nhật có thể dẫn tới vi phạm, ví dụ:
UPDATE Students S SET S.sid = 50000 WHERE S.sid = 53688
Câu lệnh cập nhật này vi phạm ràng buộc khóa chính bởi vì đã tồn tại bộ giá trị có sid là 50000.
Ảnh hưởng của ràng buộc khóa ngoại phức tạp hơn bởi vì đôi khi SQL cố gắng điều chỉnh những thao tác vi phạm ràng buộc khóa ngoại thay vì từ chối ngay những vi phạm như những ràng buộc khác. Chúng ta sẽ bàn tới các bước thi hành ràng buộc tham chiếu trong DBMS trong ví dụ của hai bảng Enrolled và Students, với ràng buộc khóa ngoại là Enrolled.sid được tham chiếu tới khóa chính của bảng Students.
Để thêm vào minh họa S1 của Students, xem xét minh họa của Enrolled trong Hình 4. Việc xóa các bộ giá trị trong Enrolled không làm vi phạm ràng buộc tham chiếu, nhưng việc thêm dữ liệu thì có thể dẫn tới vi phạm. Câu lệnh INSERT sau đây là hợp lệ vì không có sinh viên nào có sid là 51111:
INSERT INTO Enrolled(cid, grade, sid)
VALUES ('Hindi101', 'B', 51111)
Trong ví dụ khác, việc thêm bộ giá trị vào Students không vi phạm ràng buộc tham chiếu, nhưng việc xóa lại có thể vi phạm. Thêm nữa, cập nhật dữ liệu trên Enrolled hoặc Students làm thay đổi sid có thể dẫn tới vi phạm ràng buộc tham chiếu.
SQL-92 cung cấp một vài cách để nắm bắt vi phạm ràng buộc tham chiếu. Chúng ta phải xem xét ba câu hỏi cơ bản sau:
1. Điều gì chúng ta nên làm nếu một dòng được thêm vào Enrolled, với giá trị của cột sid không xuất hiện trong bất kỳ dòng nào của bảng Students.
Trường hợp này câu lệnh INSERT sẽ bị từ chối thực hiện.
2. Điều gì nên làm nếu xóa một dòng trong bảng Students?
Có những lựa chọn sau:
- Xóa tất cả các dòng của Enrolled tham chiếu tới dòng đã bị xóa của bảng Students.
- Không cho phép xóa dòng của bảng Students nếu hiện tại đang có các dòng của bảng Enrolled tham chiếu tới.
- Đặt cho các sid của bảng Enrolled tham chiếu tới dòng bị xóa trong Students một giá trị mặc định.
- Đặt cho các sid của bảng Enrolled tham chiếu tới dòng bị xóa trong Students một giá trị Null. Trong ví dụ của chúng ta, lựa chọn này không được vì sid là một phần trong khóa chính của bảng Enrolled và vì thế nó không thể nhận giá trị Null. Do đó, chúng ta giới hạn ba lựa chọn đầu tiên cho ví dụ này, mặc dù vậy lựa chọn thứ tư hoàn toàn có thể thực hiện trong các trường hợp khác.
3. Điều gì nên làm nếu giá trị khóa chính của bảng Students được cập nhật?
Các lựa chọn ở đây tương tự như trường hợp trên.
SQL-92 cho phép chúng ta chọn trong bốn cách làm trên khi thực hiện DELETE hoặc UPDATE. Ví dụ, chúng ta có thể chỉ ra rằng khi một dòng trong Students bị xóa, tất cả các dòng trong Enrolled tham chiếu đến nó cũng sẽ bị xóa, khi cột sid của một dòng trong Students được thay đổi, các dòng tham chiếu tới nó trong bảng Enrolled cũng thay đổi tương ứng.
CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20), grade CHAR(10), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE NO ACTION )
Những lựa chọn này được xác định như một phần của khai báo khóa ngoại. Lựa chọn mặc định là NO ACTION, có nghĩa là hành động (DELETE hoặc UPDATE) bị từ chối. Vì thế, mệnh đề NO UPDATE trong ví dụ của chúng ta có thể bỏ qua. Từ khóa CASCADE xác định rằng nếu một dòng của Students bị xóa, tất cả các dòng của Enrolled tham chiếu tới nó cũng sẽ bị xóa theo. Nếu như mệnh đề UPDATE xác định là CASCADE, và cột sid của một dòng trong Students được cập nhật, sự cập nhật này cũng được áp dụng cho tất cả các dòng trong Enrolled tham chiếu tới nó.
Nếu một dòng trong Students bị xóa, chúng ta có thể chuyển sự đăng ký (enrollment) tới một sinh viên mặc định bằng cách sử dụng ON DELETE SET DEFAULT. Sinh viên mặc định này được chỉ ra trong khi định nghĩa trường sid của quan hệ Enrolled; ví dụ, sid CHAR(20) DEFAULT ‘53666’. Mặc dù việc xác định giá trị mặc định này thích hợp trong một số trường hợp (ví dụ, xác định mặc định một nhà cung cấp các sản phẩm), việc gán đăng ký học cho một sinh viên mặc định như trong trường hợp này là không phù hợp. Giải pháp đúng cho ví dụ này là xóa tất cả các bộ giá trị trong Enrolled của một sinh viên nếu ta xóa sinh viên đó trong Students (sử dụng CASCADE), hoặc từ chối việc cập nhật.
SQL cũng cho phép sử dụng Null như giá trị mặc định bằng việc chỉ ra ON DELETE SET NULL.
Giao dịch và ràng buộc
Như chúng ta nhìn thấy trong chương 1, một chương trình chạy trên cơ sở dữ liệu được gọi là giao dịch, và nó có thể bao gồm một hoặc nhiều câu lệnh (queries, inserts, updates, etc.) truy cập tới cơ sở dữ liệu. Nếu (việc thực hiện của) câu lệnh trong giao dịch vi phạm một ràng buộc tham chiếu, giao dịch nên chỉ ra cách làm thế nào cho đúng hoặc tất cả các giao dịch nên được kiểm tra trước khi giao dịch hoàn thành?
Mặc định, ràng buộc được kiểm tra ở cuối mỗi câu lệnh SQL, điều này có thể dẫn tới vi phạm, và nếu có vi phạm, câu lệnh nên được từ chối thực hiện. Đôi khi, cách thực hiện này quá phức tạp. Xem xét những biến đổi sau của quan hệ Students và Courses, tất cả các sinh viên được yêu cầu có một khoá học về nghi thức (honors), và tất cả các khoá học được yêu cầu phải có người tốt nghiệp, người đó là sinh viên.
CREATE TABLE Students ( sid CHAR(20), name CHAR(30), login CHAR(20), age INTEGER, honors CHAR(10) NOT NULL, gpa REAL, PRIMARY KEY (sid), FOREIGN KEY(honors) REFERENCES Courses(cid)) CREATE TABLE Courses ( cid CHAR(20), cname CHAR(10), credits INTEGER, grader CHAR(20) NOT NULL, PRIMARY KEY (cid), FOREIGN KEY(grader) REFERENCES Students(sid))
Bất cứ khi nào một bộ giá trị của bảng Students được thêm vào, việc kiểm tra được thực hiện để đảm bảo khoá học nghi thức trong bảng Students đã có mặt trong bảng Courses, và bất kỳ bộ giá trị nào được thêm vào bảng Courses, những sinh viên đã tốt nghiệp trong bảng này cũng đã phải xuất hiện trong bảng Students. Tuy nhiên, chúng ta sẽ phải thêm sinh viên trong quan hệ Students trước hay là khoá học trong quan hệ Courses trước? Một quan hệ được thêm vào không thể thiếu quan hệ kia. Cách duy nhất để hoàn thành việc thêm dữ liệu này là việc tham chiếu tới ràng buộc phải thực hiện ở cuối câu lệnh INSERT.
SQL cho phép điều này bằng cách sử dụng phương thức DEFERRED hoặc IMMEDIATE:
SET CONSTRAINT ConstraintFoo DEFERRED
Ràng buộc theo phương thức này được kiểm tra ở thời điểm hoàn thành câu lệnh. Trong ví dụ của chúng ta, ràng buộc khoá ngoại trên cả Students và Courses đều được khai báo theo phương thức DEFERRED. Chúng ta có thể thêm một khoá học vào bảng Students mặc dù khoá học đó chưa tồn tại trong bảng Courses và ngược lại.
Truy vấn dữ liệu quan hệ
Truy vấn cơ sở dữ liệu quan hệ (gọi tắt là truy vấn) là những câu hỏi về dữ liệu, và câu trả lời nằm trong một quan hệ mới. Ví dụ, chúng ta muốn tìm tất cả các sinh viên có tuổi nhỏ hơn 18 hoặc tất cả các sinh viên đăng ký học lớp Reggae203. Ngôn ngữ truy vấn là ngôn ngữ dùng để viết các truy vấn.
SQL là ngôn ngữ truy vấn phổ biến nhất trong các DBMS. Bây giờ, chúng tôi sẽ viết một số ví dụ của câu lệnh SQL để chỉ ra rằng truy vấn trên các quan hệ dễ dàng đến mức nào. Xem xét minh họa của quan hệ Students chỉ ra trong Hình 1. Chúng ta có thể truy vấn các dòng trong bảng Students để đưa ra danh sách những sinh viên có tuổi nhỏ hơn 18, sử dụng câu lệnh sau:
SELECT * FROM Students S WHERE S.age < 18
Ký hiệu * có nghĩa là chúng ta sẽ đưa ra tất cả các trường của các bộ giá trị kết quả. Để hiểu được truy vấn này, hãy nghĩ S như là một biến nắm giữ giá trị của từng bộ giá trị trong Students, bộ này nối tiếp bộ kia. Điều kiện S.age<18 trong mệnh để Where chỉ ra rằng chúng ta chỉ muốn lựa chọn những bộ giá trị có giá trị của age nhỏ hơn 18. Kết quả của truy vấn nằm trong Hình 6.
Những sinh viên có age < 18 trong minh họa S1
Điều kiện S.age <18 là một so sánh toán học của age với một giá trị integer, điều này thực hiện được vì miền của trường age cũng là integer. Thêm vào đó, điều kiện như là S.age=S.sid không thực hiện được vì không thể so sánh một giá trị có kiểu integer với một giá trị có kiểu string, và SQL sẽ cho đây là lỗi và không có bộ giá trị nào được trả về.
Thêm nữa, để lựa chọn tập con các bộ giá trị, truy vấn có thể trích chọn ra một tập con các trường. Chúng ta có thể đưa ra Name và Login của các sinh viên có tuổi nhỏ hơn 18 bằng truy vấn sau:
SELECT S.name, S.login FROM Students S WHERE S.age < 18
Hình 7 chỉ ra kết quả của truy vấn này, nó bỏ đi các trường không được chỉ ra trong truy vấn. Ghi nhớ rằng thứ tự thực hiện các phép toán cũng là vấn đề- nếu chúng ta bỏ đi những trường không mong muốn trước, chúng ta không thể kiểm tra được điều kiện S.age<18.
Chúng ta có thể kết hợp thông tin trong hai quan hệ Students và Enrolled. Nếu chúng ta muốn có tên của sinh viên và cid của khoá học mà sinh viên học có kết quả học tập (grade) là 'A', chúng ta có thể thực hiện câu lệnh sau:
SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid = E.sid AND E.grade = 'A'
Names và Logins của các sinh viên dưới 18 tuổi
Truy vấn này có thể được hiểu như sau: Nếu có một bộ giá trị S của Students và một bộ giá trị E của Enrolled thoả mãn S.id= E.id (S biểu diễn sinh viên tham gia khoá học E) và E.grade='A', thì in ra tên sinh viên và mã khoá học mà sinh viên đó tham dự. Khi xem xét minh hoạ của Students và Enrolled trong Hình 4, truy vấn này trả về kết quả là một bộ giá trị {Smith, Topology112}.
Chúng ta sẽ nghiên cứu sâu hơn về truy vấn, và SQL trong những chương sau.
Thiết kế cơ sở dữ liệu logic: ER chuyển sang quan hệ
là thiết kế đầu tiên, thiết kế cơ sở dữ liệu mức cao. Lược đồ ER biểu diễn cơ sở dữ liệu, đó là một chuẩn áp dụng để sinh ra lược đồ cơ sở dữ liệu quan hệ gần xấp xỉ với thiết kế ER. (Việc chuyển đổi gần như là việc thêm vào những điều mà chúng ta không thể thể hiện tường minh trong thiết kế ER bằng cách sử dụng SQL-92). Bây giờ chúng ta biểu diễn cách thức như thế nào để chuyển lược đồ ER thành một tập các bảng cùng với các ràng buộc hỗ trợ, tức là lược đồ cơ sở dữ liệu quan hệ.
Các kiểu thực thể chuyển thành các bảng
Một kiểu thực thể được ánh xạ thành một quan hệ: Mỗi thuộc tính của kiểu thực thể trở thành thuộc tính của bảng. Ghi nhớ rằng chúng ta biết về miền của từng thuộc tính và khóa chính của mỗi kiểu thực thể.
Xem xét kiểu thực thể Employees với các thuộc tính ssn, name và lot chỉ ra trong Hình 8. Một minh họa có thể của kiểu thực thể Employees, chứa ba thực thể được chỉ ra trong Hình 9:
Kiểu thực thể Employees
Câu lệnh SQL sau thể hiện những thông tin trên, bao gồm các ràng buộc miền và khóa:
CREATE TABLE Employees ( ssn CHAR(11), name CHAR(30), lot INTEGER, PRIMARY KEY (ssn) )
Một minh hoạ của kiểu thực thể Employees
Các kiểu liên kết (không có ràng buộc) thành các Bảng.
Kiểu liên kết, tương tự như kiểu thực thể, được ánh xạ thành một quan hệ trong mô hình quan hệ. Chúng ta bắt đầu bằng việc xem xét kiểu liên kết không có khóa và các ràng buộc tham dự, và chúng ta bàn cách làm như thế nào để nắm bắt được những ràng buộc này trong những phần tiếp theo. Để biểu diễn một liên kết, chúng ta phải xác định từng thành phần thực thể tham dự và cung cấp những thông tin để biểu diễn những thuộc tính của liên kết. Vì thế, những thuộc tính của quan hệ này bao gồm:
- Các khóa chính của các thực thể tham gia trong liên kết, đóng vai trò như là những trường khóa ngoại.
- Những thuộc tính biểu diễn của kiểu liên kết.
Tập các thuộc tính là thuộc tính không biểu diễn sẽ là siêu khóa của quan hệ. Nếu ở đó không có những ràng buộc khóa (xem Phần 2.4.1), tập những thuộc tính này là khóa dự tuyển.
Xem xét kiểu liên kết Works_In2 trong Hình 10. Mỗi Department có nhiều văn phòng đặt ở nhiều vị trí khác nhau và chúng ta muốn lưu lại vị trí văn phòng làm việc của từng nhân viên.
Kiểu liên kết bậc ba
Tất cả thông tin có thể về bảng Works_In2 được SQL định nghĩa như sau:
CREATE TABLE Works In2 ( ssn CHAR(11), did INTEGER, address CHAR(20), since DATE, PRIMARY KEY (ssn, did, address), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (address) REFERENCES Locations, FOREIGN KEY (did) REFERENCES Departments )
Ghi nhớ rằng các trường address, did, và ssn không thể mang giá trị Null. Bởi vì những trường này là một phần của khóa chính trong quan hệ Works_In2, và ràng buộc NOT NULL là đúng đối với các trường đó. Ràng buộc này đảm bảo rằng những trường này xác định duy nhất một department, một employee, và một location trong mỗi bộ giá trị của Works_In. Chúng ta cũng có thể xác định rằng một hành động mong muốn nào đó khi một bộ giá trị tham chiếu tới các quan hệ Employees, Departments hoặc Locations bị xoá. Trong chương này chúng ta giả sử rằng hành động mong muốn đó là hành động mặc định trừ trường hợp mô hình ER có yêu cầu một hành động khác.
Kiểu liên kết Reports_To
Cuối cùng, chúng ta xem xét liên kết Reports_To chỉ ra trong Hình 11. Vai trò của chú thích supervisor và subordinate được sử dụng để tạo ra tên các trường có ý nghĩa trong câu lệnh CREATE để tạo ra bảng Reports_To:
CREATE TABLE Reports To ( supervisor_ssn CHAR(11), subordinate_ssn CHAR(11), PRIMARY KEY (supervisor_ssn, subordinate_ssn), FOREIGN KEY (supervisor_ssn) REFERENCES Employees(ssn), FOREIGN KEY (subordinate_ssn) REFERENCES Employees(ssn))
Quan sát thấy rằng, chúng ta cần một tên tường minh cho trường tham chiếu của Employees bởi vì tên trường phải khác với tên của trường tham chiếu tới nó.
Chuyển một kiểu liên kết có ràng buộc khoá
Xem xét kiểu liên kết Manages trong Hình 12.
Bảng tương ứng với kiểu liên kết Manages có các thuộc tính ssn, did, since. Tuy nhiên, vì mỗi Department có nhiều nhất một người quản lý nên không thể có hai bộ giá trị có cùng giá trị did (khác với giá trị ssn). Kết quả của quan sát này là, riêng thuộc tính did có thể làm khoá của quan hệ Manages, thay vì phải chọn một tập thuộc tính did, ssn(vì đây không phải là tập tối thiểu). Quan hệ Manages có thể được định nghĩa bằng SQL như sau:
Ràng buộc khoá trên Manages
CREATE TABLE Manages ( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments )
Cách tiếp cận thứ hai gọi là 'gộp chung', tránh tạo ra một bảng cho một kiểu liên kết. Cách làm này sẽ chuyển tất cả các thông tin trong kiểu liên kết vào một trong những kiểu quan hệ tham gia trong liên kết. Trong ví dụ Manages, vì mỗi Departments có nhiều nhất một người quản lý, chúng ta có thể thêm những trường khoá của Employees và thuộc tính since vào kiểu quan hệ Departments.
Cách tiếp cận này tránh được việc tạo ra một quan hệ Manages, và những truy vấn yêu cầu đưa ra người quản lý của Departments sẽ không cần phải kết hợp thông tin trong hai quan hệ. Nhược điểm của cách tiếp cận này là sẽ có những khoảng trống dư thừa nếu có những Departments không có người quản lý. Trong trường hợp này, những chỗ trống sẽ được điền giá trị là Null. Cách áp dụng thứ nhất (sử dụng một bảng Manages riêng) tránh được sự dư thừa này, nhưng một số những truy vấn quan trọng yêu cầu chúng ta phải kết hợp thông tin từ hai bảng sẽ làm chậm việc thực hiện câu lệnh.
Câu lệnh SQL sau định nghĩa quan hệ DEPT_Mgr thể hiện thông tin trong cả Departments và Manages, minh hoạ cách tiếp cận thứ hai:
CREATE TABLE Dept_Mgr ( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees )
Ghi nhớ rằng ssn có thể có giá trị Null.
Cách tiếp cận này có thể áp dụng với những kiểu liên kết liên quan đến nhiều hơn hai kiểu thực thể.
Chúng ta sẽ bàn thêm về lợi ích của hai cách tiếp cận này sau khi xem xét cách chuyển một kiểu liên kết có những ràng buộc tham dự (participation constraints) thành bảng.
Chuyển kiểu liên kết có những ràng buộc tham dự
Xem xét lược đồ trong Hình 13 có hai kiểu liên kết Manages và Works_In.
Manages và Works_In
Tất cả các Departments được yêu cầu có người quản lý, tức là có ràng buộc tham dự, và nhiều nhất là một người quản lý, tức là có ràng buộc khoá. Câu lệnh SQL sau đây phản ánh cách tiếp cận thứ 2 bàn đến trong Phần 3.5.3 và sử dụng ràng buộc khoá:
CREATE TABLE Dept_Mgr ( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE NO ACTION )
Nó cũng minh họa ràng buộc tham dự, rằng tất cả các Departments phải có người quản lý. Vì ssn không thể mang giá trị Null, mỗi bộ giá trị Dept_Mgr xác định một bộ giá trị của Employees (người làm quản lý). Xác nhận NO ACTION là mặc định và không cần phải chỉ ra tường minh như trên, để đảm bảo rằng mỗi bộ giá trị của Employees không thể bị xóa trong khi nó đang được Dept_Mgr tham chiếu tới. Nếu chúng ta muốn xóa một bộ giá trị nào đó trong Employees, đầu tiên chúng ta cần thay đổi người quản lý này sang người quản lý mới trong Dept_Mgr. (Chúng ta cũng có thể xác định CASCADE thay cho NO ACTION, nhưng nếu muốn xóa thông tin về một Employees, thì những thông tin về Departments liên quan lại xóa theo thì sẽ không hợp lý.)
Ràng buộc tất cả các Departments phải có người quản lý không thể được chỉ ra nếu sử dụng cách tiếp cận thứ nhất trong Phần 3.5.3. (Nhìn vào định nghĩa của Manages và nghĩ về điều gì sẽ xảy ra nếu chúng ta không thêm vào các ràng buộc NOT NULL cho các trường ssn và did. Gợi ý: Ràng buộc sẽ ngăn cản sự thiếu vắng người quản lý, nhưng không đảm bảo rằng người quản lý này là người đầu tiên được đề cử làm người quản lý mỗi Departments!). Trường hợp này gây ra nhiều tranh luận trong việc ủng hộ cách tiếp cận thứ hai cho liên kết một-nhiều như Manages, đặc biệt là khi kiểu thực thể có ràng buộc khóa và cũng có ràng buộc tham gia toàn bộ.
Không may là có rất nhiều ràng buộc tham dự mà chúng ta không thể biểu diễn sử dụng SQL-92. Ràng buộc và xác nhận bảng có thể được chỉ ra bằng cách sử dụng khả năng mạnh hơn của SQL (bàn tới trong Phần 5.11) và nó rất có ý nghĩa, và nó cũng rất có ý nghĩa để kiểm tra và thiết đặt ràng buộc. Ví dụ, chúng ta không thể thiết đặt các ràng buộc tham dự trên quan hệ Works_In mà không sử dụng những ràng buộc phổ biến. Để giải thích vì sao, chúng ta thu được quan hệ Works_In bằng cách chuyển lược đồ ER sang các quan hệ. Nó chứa các trường ssn và did, là các khóa ngoại tham chiếu tới Employees và Departments. Để đảm bảo sự tham gia toàn bộ của Departments trong Works_In, chúng ta phải đảm bảo rằng tất cả giá trị did trong Departments phải xuất hiện trong một bộ giá trị nào đó của Works_In. Chúng ta có thể cố gắng để đảm bảo điều kiện này bằng việc khai báo rằng did trong Departments là khóa ngoại tham chiếu tới Works_In, nhưng đây không phải là ràng buộc khóa ngoại hợp lệ vì did không phải là khóa dự tuyển của Works_In.
Để đảm bảo sự tham gia toàn bộ của Departments trong Works_In sử dụng SQL-92, chúng ta cần một xác nhận (assertion). Chúng ta phải đảm bảo rằng tất cả giá trị did trong Departments phải xuất hiện trong một bộ giá trị của Works_In; thêm vào đó, bộ giá trị của Works_In phải không có giá trị NULL trong các trường mà các khóa ngoại đang tham chiếu tới (trong ví dụ này là trường ssn). Chúng ta có thể đảm bảo thêm ràng buộc này bằng việc đưa ra một yêu cầu mạnh hơn rằng ssn trong Works_In không thể chứa giá trị Null. (Đảm bảo rằng sự tham dự của Employees trong Works_In là toàn bộ).
Một ràng buộc khác cần sử dụng xác nhận để biểu diễn trong SQL là ràng buộc yêu cầu mỗi thực thể Employees (trong liên kết với Manages) phải quản lý ít nhất một Department.
Trên thực tế, kiểu liên kết Manages làm ví dụ cho hầu hết các ràng buộc tham dự sử dụng các ràng buộc khóa và khóa ngoại. Manages là kiểu liên kết nhị phân, trong đó có chính xác một kiểu thực thể (Departments) có ràng buộc khóa, và ràng buộc tham gia toàn bộ được biểu diễn trong kiểu thực thể này.
Chúng ta cũng có thể thể hiện những ràng buộc tham dự bằng sử dụng ràng buộc khóa và khóa ngoại trong một trường hợp đặc biệt
