Mô hình quan hệ

Ths. Phạm Hoàng Nhung
Ths. Phạm Hoàng Nhung
Tác giả
Cập nhật: 07/08/2013

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.

Mô hình quan hệ 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.

Giới thiệu mô hình quan hệ

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:

  1. 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.
  2. 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ệ

Các ràng buộc khóa

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:

  1. 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.
  2. 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.

Ràng buộc khóa ngoạ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.

Những ràng buộc phổ biế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ệ

Mô hình 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 khác: một kiểu liên kết mà tất cả các kiểu thực thể tham gia đều có ràng buộc khóa và ràng buộc tham gia toàn bộ. Cách chuyển tốt nhất trong trường hợp này là ánh xạ từng kiểu thực thể cũng như kiểu liên kết vào một bảng.

Chuyển kiểu thực thể yếu

Kiểu thực thể yếu luôn luôn là một phần trong liên kết bậc hai một-nhiều và có ràng buộc khóa và ràng buộc tham dự. Cách chuyển thứ hai được giới thiệu trong Phần 3.5.3 là một ý tưởng giải quyết trường hợp này, nhưng chúng ta phải nhớ rằng thực thể yếu chỉ có một khóa bộ phận. Và khi thực thể chủ bị xóa, chúng ta muốn tất cả các thực thể yếu liên quan đến thực thể chủ này phải được xóa theo.

Xem xét thực thể yếu Dependents trong Hình 14, với khóa bộ phận là pname. Thực thể Dependents có thể được xác định duy nhất khi chúng ta kết hợp giữa khóa chính của thực thể chủ Employees và khóa bộ phận pname của Dependents, và thực thể Dependents phải bị xóa nếu thực thể chủ Employees bị xóa.

Kiểu thực thể yếu Dependents

Chúng ta có thể thể hiện mong muốn trên bằng cách sử dụng câu lệnh định nghĩa quan hệ Dep_Policy:

CREATE TABLE Dep_Policy ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11), PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE )

Quan sát thấy rằng khóa chính là {pname, ssn}, vì Dependents là thực thể yếu. Ràng buộc này sẽ thay đổi tùy vào cách chuyển đổi đã được bàn trong Phần 3.5.3. Chúng ta phải đảm bảo rằng tất cả thực thể Dependents được liên kết với một thực thể Employees (thực thể chủ), giống như ràng buộc tham gia toàn bộ trên Dependents, có nghĩa là ssn không thể mang giá trị Null. Điều này được đảm bảo vì ssn là một phần của khóa chính. Lựa chọn CASCADE đảm bảo rằng thông tin về những người phụ thuộc (Dependents) liên quan đến một nhân viên sẽ bị xóa khi nhân viên đó bị xóa.

Chuyển hệ thống phân cấp

Chúng tôi sẽ trình bày hai cách tiếp cận để chuyển đổi một phân cấp ISA, áp dụng những cách này cho lược đồ ER chỉ ra trong Hình 15.

Hệ thống phân cấp
  1. Chúng ta có thể ánh xạ mỗi kiểu thực thể Employees, Hourly_Emps, và Contract_Emps tới một quan hệ. Quan hệ Employees được tạo trong Phần 2.2. Chúng ta sẽ bàn Hourly_Emps ở đây; Contract_Emps sẽ tương tự. Quan hệ Hourly_Emps bao gồm thuộc tính lương theo giờ và số giờ làm việc của Hourly_Emps. Nó cũng chứa thuộc tính khóa của lớp trên (trong trường hợp này là ssn), thuộc tính này được sử dụng như khóa chính của Hourly_Emps, đồng thời là khóa ngoại tham chiếu tới lớp cha (Employees). Với mỗi thực thể Hourly_Emps, giá trị của thuộc tính name và nhiều thuộc tính khác nữa được lưu trữ trong dòng tương ứng của lớp cha (Employees). Ghi nhớ rằng nếu một bộ giá trị trong lớp cha bị xóa, những bộ giá trị tương ứng trong lớp con phải được xóa theo.
  2. Cách hai, chúng ta có thể chỉ tạo ra hai quan hệ, tương ứng với Hourly_Emps và Contract_Emps. Quan hệ Hourly_Emps bao gồm tất cả các thuộc của Hourly_Emps và các thuộc tính của Employees (ssn, name, lot, hourly_wages, hours_worked).

Cách tiếp cận đầu tiên là phổ biến và luôn luôn thực hiện được. Các truy vấn muốn kiểm tra tất cả các nhân viên mà không quan tâm đến những thuộc tính riêng biệt của lớp con được thực hiện dễ dàng sử dụng quan hệ Employees. Tuy nhiên, những truy vấn chỉ muốn biết về những thông tin của nhân viên làm việc theo giờ thì chúng ta phải kết hợp quan hệ Hourly_Emps với quan hệ Employees để có được name và lot.

Cách áp dụng thứ hai không dùng được nếu chúng ta có những nhân viên không làm việc theo giờ (Hourly_Emps), cũng không làm việc theo hợp đồng (Contract_Emps). Tương tự, nếu một nhân viên vừa làm việc theo giờ, vừa là nhân viên làm việc theo hợp đồng thì giá trị Name và Lot được lưu trữ hai lần. Việc lưu trữ nhiều lần có thể dẫn tới những dị thường dữ liệu. Một truy vấn cần xem toàn bộ các nhân viên thì phải thực hiện trên hai quan hệ. Tuy nhiên, truy vấn cần kiểm tra chỉ những nhân viên làm việc theo giờ có thể thực hiện chỉ trên một quan hệ. Việc lựa chọn cách áp dụng nào phải phụ thuộc vào ý nghĩa của dữ liệu và sự thường xuyên hay không của các truy cập trên các quan hệ.

Chuyển lược đồ ER có khối kết hợp

Xem xét lược đồ ER chỉ ra trong Hình 16.

Kiểu thực thể Employees, Projects, và Departments và kiểu liên kết Sponsors được ánh xạ như cách làm trong các phần trước. Với kiểu liên kết Monitors, chúng ta tạo ra một quan hệ có các thuộc tính sau: thuộc tính khóa của Employees (ssn), các thuộc tính khóa của Sponsor (did, pid), và thuộc tính biểu diễn của Monitors (until). Việc chuyển này về cơ bản giống như cách ánh xạ chuẩn cho mỗi kiểu liên kết, biểu diễn trong Phần 3.5.2.

Có một trường hợp đặc biệt trong cách chuyển đổi này là chúng ta có thể tinh giảm bằng cách xóa quan hệ Sponsors. Xem xét quan hệ Sponsors. Nó có các thuộc tính pid, did, và since và thường thì chúng ta cần đến nó (thêm vào Monitors) vì hai lý do:

  1. Chúng ta phải ghi lại các thuộc tính biểu diễn (trong ví dụ của chúng ta là thuộc tính since) của liên kết Sponsors.
  2. Không phải tất cả các bảo trợ (giữa Departments và Projects) đều cần có người điều hành (monitor), và vì thế một số cặp {pid, did} trong quan hệ Sponsors có thể không xuất hiện trong quan hệ Monitors.

Tuy nhiên, nếu Sponsors không có các thuộc tính biểu diễn và có ràng buộc tham gia toàn bộ trong Monitor (tức là sự bảo trợ nào giữa Departments và Projects đều phải có người điều hành), thì ta nhận thấy tất cả các minh họa có thể của quan hệ Sponsors đều có thể nhận được từ các cột {pid, did} của Monitors; vì thế Sponsors có thể xóa được.

Khối kết hợp

ER tới Quan hệ: Một ví dụ khác

Xem xét lược đồ ER trong Hình 17.

Policy Revisited

Chúng ta có thể sử dụng các ràng buộc khóa để kết hợp thông tin Purchaser cùng với Policies và thông tin Beneficiary cùng với Dependents, và chuyển nó vào mô hình quan hệ như sau:

CREATE TABLE Policies ( policyid INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (policyid), FOREIGN KEY (ssn) REFERENCES Employees ON DELETE CASCADE ) CREATE TABLE Dependents ( pname CHAR(20), age INTEGER, policyid INTEGER, PRIMARY KEY (pname, policyid), FOREIGN KEY (policyid) REFERENCES Policies ON DELETE CASCADE )

Ghi nhớ rằng việc xóa một nhân viên sẽ dẫn tới xóa tất cả các chính sách (policies) liên quan đến nhân viên đó và tất cả các người phụ thuộc hưởng chính sách này. Thêm vào đó, mỗi người phụ thuộc được yêu cầu có một chính sách bảo vệ- bởi vì policyid là một phần trong khóa chính của Dependents, ở đó có một ràng buộc NOT NULL. Mô hình hày phản ánh chính xác các ràng buộc tham dự trong lược đồ ER và những hành động được dự định khi thực thể Employees bị xóa.

Nói chung, đó có thể là một chuỗi các liên kết của các kiểu thực thể yếu.Ví dụ, chúng ta đã giả sử rằng policyid giúp xác định duy nhất một policy. Giả sử rằng policyid chỉ phân biệt được các policies của một nhân viên; tức là, policyid chỉ là khóa thành phần và Policies chỉ nên được mô hình hóa như là một kiểu thực thể yếu. Giả định mới này về policyid không phải là nguyên nhân lớn dẫn tới những bàn luận phía trước. Thực tế, chỉ có thay đổi là khóa chính của Policies bây giờ là {policyid, ssn} và định nghĩa của Dependents phải thay đổi- trường được gọi là ssn được thêm vào và trở thành một phần của khóa chính của Dependents và khóa ngoại tham chiếu tới Policies:

CREATE TABLE Dependents (pname CHAR(20), ssn CHAR(11), age INTEGER, policyid INTEGER NOT NULL, PRIMARY KEY (pname, policyid, ssn), FOREIGN KEY (policyid, ssn) REFERENCES Policies ON DELETE CASCADE)

Giới thiệu khung nhìn

Khung nhìn là một bảng bao gồm các dòng không được lưu trữ rõ ràng trong cơ sở dữ liệu nhưng được đưa ra khi cần thiết thông qua định nghĩakhung nhìn. Xem xét quan hệ Students và Enrolled. Giả sử rằng chúng ta thường cần tìm name, sid và cid của các sinh viên có điểm tốt nghiệp loại B. Chúng ta có thể định nghĩa một khung nhìn để phục vụ mục đích này. Sử dụng SQL-92:

CREATE VIEW B-Students (name, sid, course) AS SELECT S.sname, S.sid, E.cid FROM Students S, Enrolled E WHERE S.sid = E.sid AND E.grade = 'B'

Khung nhìn này có thể được sử dụng như là các bảng cơ sở, hoặc bảng được lưu trữ tường minh. Như minh họa của Enrolled và Students được chỉ ra trong Hình 4, B-Students chứa các bộ giá trị như trong Hình 18. Về mặt lý thuyết, bất cứ khi nào B-Students cũng được sử dụng trong truy vấn, trước tiên, định nghĩa khung nhìn được dùng để nhận được các minh họa dữ liệu như B-Students, sau đó, khung nhìn được sử dụng như một quan hệ để các truy vấn có thể tham chiếu tới. (Chúng ta sẽ bàn tới truy vấn và khung nhìn được đánh giá như thế nào trong Chương 23.)

Minh họa của khung nhìn B-Students

Khung nhìn, Độc lập dữ liệu, Đảm bảo an toàn

Xem xét các mức trừu tượng mà chúng ta đã bàn đến trong Phân 1.5.2. Lược đồ vật lý của cơ sở dữ liệu quan hệ biểu diễn các quan hệ trong lược đồ khái niệm được lưu trữ như thế nào, dưới dạng các tổ chức file và chỉ số. Lược đồ khái niệm là tập các lược đồ của quan hệ trong cơ sở dữ liệu. Trong khi một số quan hệ trong lược đồ khái niệm có thể được nhìn thấy từ các ứng dụng, ví dụ một phần của lược đồ ngoài của cơ sở dữ liệu, các quan hệ bổ sung trong lược đồ ngoài có thể được định nghĩa thông qua khung nhìn. Khung nhìn vì thế cung cấp sự hỗ trợ cho độc lập dữ liệu vật lý trong mô hình quan hệ. Vì thế, nó có thể được sử dụng để định nghĩa các quan hệ trong lược đồ ngoài.

Khung nhìn cũng có giá trị trong việc đảm bảo sự an toàn dữ liệu: Chúng ta có thể định nghĩa các khung nhìn cho một nhóm người sủ dụng, để đảm bảo rằng họ chỉ được truy cập tới các thông tin mà họ được phép. Ví dụ, chúng ta có thể định nghĩa khung nhìn cho phép sinh viên nhìn thấy tên và tuổi của các sinh viên khác, nhưng không cho phép nhìn thấy điểm, và cho phép tất cả sinh viên truy cập tới khung nhìn này để không làm ảnh hưởng tới bảng Students.

Cập nhật trên khung nhìn

Mục đích của các khung nhìn là làm cho người dùng có thể nhìn thấy dữ liệu. Người dùng không nên lo lắng về sự khác biệt giữa khung nhìn và bảng cơ sở. Khung nhìn được sử dụng như các bảng trong khi định nghĩa truy vấn. Tuy nhiên, một điều bình thường là bạn muốn cập nhật dữ liệu thông qua khung nhìn. Trong trường hợp này, không may thay, có sự khác biệt giữa khung nhìn và bảng cơ sở.

Chuẩn SQL-92 cho phép cập nhật trên khung nhìn đối với các khung nhìn mà trong phần định nghĩa chỉ có các bảng cơ sở cùng với các phép chọn và phép chiếu, không có các phép toán nhóm. Những khung nhìn này được gọi là các khung nhìn có khả năng cập nhật. Một phép cập nhật trên khung nhìn loại này có thể được thực hiện bằng việc cập nhật các cơ sở một cách rõ ràng. Xem sét khung nhìn sau:

CREAT VIEW GoodStudents (sid, gpa) AS SELECT S.sid, S.gpa FROM Students S WHERE S.gpa > 30

Chúng ta thực hiện lệnh để sửa giá trị của thuộc tính gpa của một dòng trong quan hệ GoodStudents bằng việc sửa dòng tương ứng trong Students. Chúng ta có thể xóa một dòng GoodStudents bằng việc xóa các dòng tương ứng trong bảng Students. (Tóm lại, nếu khung nhìn không bao gồm khóa của bảng phía dưới, một vài dòng trong bảng có thể liên quan tới một dòng trong khung nhìn. Đây là một trường hợp, ví dụ, nếu sử dụng S.sname thay vì S.sid trong định nghĩa của GoodStudents. Một lệnh ảnh hưởng đến các dòng trong khung nhìn sẽ ảnh hưởng tới tất cả các dòng liên quan trong bảng phía dưới).

Chúng ta có thể thêm một vài dòng và GoodStudents bằng việc thêm một dòng vào Students, sử dụng giá trị Null trong các cột của Students, những cột mà không xuất hiện trong GoodStudents (ví dụ: sname, login). Ghi nhớ rằng các cột đóng vai trò khóa chính không được phép thêm giá trị Null. Vì thế, nếu chúng ta cố gắng thêm các dòng thông qua khung nhìn, nhưng khung nhìn này không chứa khóa chính của bảng phía dưới thì yêu cầu thêm này sẽ bị từ chối. Ví dụ, nếu GoodStudents chứa sname mà không có sid, chúng ta không thể thêm các dòng vào bảng Student thông qua việc thêm vào khung nhìn GoodStudents.

Nhận xét quan trọng là, INSERT hoặc UPDATE có thể thay đổi các bảng cơ sở phía dưới mặc dù dòng này không xuất hiện trong khung nhìn! Ví dụ, nếu chúng ta cố gắng thêm một dòng {51234, 2.8} vào trong khung nhìn, dòng này có thể được thêm vào các bảng Students phía dưới, nhưng mà nó sẽ không xuất hiện trong khung nhìn GoodStudents vì nó không thỏa mãn điều kiện của khung nhìn là gpa>3.0. SQL-92 mặc định là sẽ cho phép thêm vào, nhưng chúng ta có thể không cho phép thêm vào bộ giá trị không thỏa mãn này bằng cách sử dụng mệnh đề WITH CHECK OPTION trong khi định nghĩa khung nhìn.

Chúng tôi nhắc nhở người đọc rằng khi khung nhìn được định nghĩa dựa trên các khung nhìn khác, những ảnh hưởng lẫn nhau giữa các định nghĩa khung nhìn này đối với việc cập nhật và mệnh đề CHECK OPTION có thể trở nên phức tạp; chúng ta sẽ không đi chi tiết phần này.

Những khung nhìn có khả năng cập nhật trong SQL:1999 Chuẩn SQL mới đã mở rộng những loại định nghĩa khung nhìn có khả năng cập nhật. Đối lập với SQL-92, định nghĩa khung nhìn chứa nhiều hơn một bảng trong mệnh đề FROM có thể được cập nhật. Chúng ta có thể cập nhật một trường của khung nhìn nếu trường là trường duy nhất nằm trong những bảng phía dưới, và khoá chính của bảng đó nằm trong những trường của khung nhìn.

SQL:1999 phân biệt giữa các khung nhìn chứa các dòng có thể sửa được (các khung nhìn có khả năng cập nhật) và các khung nhìn có thể thêm dòng mới (các khung nhìn có khả năng thêm mới): Trong định nghĩa các khung nhìn chứa cấu trúc UNION, INTERSECT và EXCEPT (đã bàn tới trong Chương 5) không thể được thêm dòng mới, mặc dù chúng có thể được cập nhật. Khả năng cập nhật đảm bảo rằng những bộ giá trị được cập nhật trong khung nhìn có thể được lần vết từ chính xác một bộ giá trị của các bảng đã sử dụng trong định nghĩa khung nhìn.

Cần phải giới hạn việc cập nhật qua khung nhìn

Trong khi SQL-92 hạn chế khả năng cập nhật trên khung nhìn nghiêm ngặt hơn cần thiết, có một vài vấn đề cơ bản đối với việc cập nhật dữ liệu thông qua khung nhìn, và có những lý do đúng để giới hạn một số loại của khung nhìn mới có khả năng cập nhật. Xem xét quan hệ Students và một quan hệ mới gọi là Clubs:

Clubs(cname: string, jyear: date, mname: string)

Một bộ giá trị trong Clubs chỉ ra rằng sinh viên có tên mname đã là thành viên của câu lạc bộ cname từ năm jyear. Giả sử rằng chúng ta thường muốn tìm names và logins của những sinh viên có gpa lớn hơn 3 thuộc vào ít nhất một câu lạc bộ, cùng với tên câu lạc bộ và ngày vào câu lạc bộ. Chúng ta định nghĩa khung nhìn này như sau:

CREATE VIEW ActiveStudents (name, login, club, since) AS SELECT S.sname, S.login, C.cname, C.jyear FROM Students S, Clubs C WHERE S.sname = C.mname AND S.gpa > 3

Bây giờ giả sử rằng chúng ta muốn xoá dòng {Smith, smith@ee, Hiking, 1997} từ Ac- tiveStudents. Chúng ta làm điều này như thế nào? Các dòng trong ActiveStudents không được lưu trữ tường minh nhưng được đưa ra khi cần thiết từ hai bảng Students và Clubs sử dụng định nghĩa khung nhìn. Vì thế chúng ta phải thay đổi Students hoặc Clubs (hoặc cả hai). Công việc này được thực hiện bằng một trong hai cách: hoặc xoá dòng {53688, Smith, smith@ee, 18, 3.2} từ bảng Students hoặc xoá dòng {Hiking, 1997, Smith} từ Clubs. Nhưng không có giải pháp nào ở trên là thoả đáng. Việc xoá dòng trong bảng Students sẽ dẫn tới việc xoá dòng {Smith, smith@ee, Rowing, 1998} trong khung nhìn ActiveStudents. Việc xoá dòng trong Clubs cũng sẽ dẫn tới việc xoá dòng {Smith, smith@math, Hiking, 1997} trong khung nhìn ActiveStudents. Cả hai ảnh hưởng này đều không được chấp nhận. Thực tế, chỉ có một giải pháp có thể là không cho phép cập nhật trên khung nhìn.

Nhiều khung nhìn có thể có nhiều hơn một bảng cơ sở, theo nguyên tắc phải được cập nhật một cách an toàn. Khung nhìn B-Students chúng ta giới thiệu ở đầu của phần này là một ví dụ về khung nhìn như thế. Xem xét minh hoạ của B-Students chỉ ra trong Hình 18 (cùng với minh hoạ của Students và Enrolled trong Hình 4). Để thêm một bộ giá trị, giả sử {Dave, 50000, Reggae203} vào B-Students, chúng ta đơn giản có thể thêm một bộ giá trị {Reggae203, B, 50000} vào Enrolled vì đã có một bộ giá trị có sid là 50000 trong Students. Mặt khác, để thêm {John, 55000, Reggae203}, chúng ta phải thêm {Reggae203, B, 55000} vào Enrolled và cũng thêm {55000, John, null, null, null} vào Students. Lược đồ khung nhìn chứa những trường khoá chính của cả hai bảng phía dưới, nếu không thì chúng ta sẽ không thể thêm vào trong khung nhìn này. Để xoá một bộ giá trị từ khung nhìn B-Students, chúng ta có thể xoá những bộ giá trị liên quan trong Enrolled.

Mặc dù ví dụ này chỉ ra rằng SQL-92 hạn chế khả năng cập nhật của khung nhìn là không cần thiết, nhưng nó vẫn chỉ ra được sự phức tạp trong kiểm soát việc cập nhật thông qua khung nhìn trong những trường hợp chung khác. Vì những lý do thực tế này, SQL-92 đã lựa chọn chỉ cho phép cập nhật lên những loại khung nhìn nhất định.

Xoá/Sửa bảng và khung nhìn

Nếu chúng ta quyết định không cần đến một bảng cơ sở và chúng ta muốn xoá nó (tức là, xoá tất cả các dòng trong bảng và thông tin định nghĩa bảng), chúng ta có thể sử dụng câu lệnh DROP TABLE. Ví dụ, DROP TABLE Students RESTRICT sẽ xoá bảng Students trừ khi có một vài khung nhìn và ràng buộc tham chiếu tới Students; nếu đang có sự tham chiếu này thì lệnh sẽ hỏng. Nếu từ khoá RESTRICT được thay bằng từ CASCADE, Students sẽ bị xoá và bất kỳ tham chiếu của khung nhìn hoặc ràng buộc tham chiếu cũng bị xoá theo; một trong những từ khoá này phải luôn luôn được xác định. Khung nhìn có thể được xoá sử dụng lệnh DROP VIEW, thay vì DROP TABLE.

ALTER TABLE là lệnh sửa cấu trúc của một bảng đang tồn tại. Để thêm một cột có tên là maiden-name vào bảng Students, chúng ta sẽ sử dụng lệnh sau đây:

ALTER TABLE Students ADD COLUMN maiden-name CHAR(10)

Định nghĩa Students được sửa để thêm vào cột này, và tất cả các dòng đang tồn tại được thêm giá trị Null vào cột này. ALTER TABLE có thể được sử dụng để xoá hoặc thêm các cột, hoặc xoá các ràng buộc tham chiếu trên một bảng; chúng ta sẽ không bàn tới những phần này vì nó tương tự như xoá bảng hoặc khung nhìn.

Trường hợp nghiên cứu: Cửa hàng Internet

Bước thiết kế tiếp theo trong ví dụ này (tiếp tục từ Phần 2.8) là thiết kế cơ sở dữ liệu logic. Sử dụng cách tiếp cận chuẩn đã bàn trong Chương 3, DBDudes ánh xạ lược đồ ER trong Hình 2.20 tới mô hình quan hệ sẽ nhận được những bảng sau:

CREATE TABLE Books( isbn CHAR(10), title CHAR(80), author CHAR(80), qty_in_stock INTEGER, price REAL, year_published INTEGER, PRIMARY KEY (isbn)) CREATE TABLE Orders( isbn CHAR(10), cid INTEGER, cardnum CHAR(16), qty INTEGER, order_date DATE, ship_date DATE, PRIMARY KEY (isbn, cid), FOREIGN KEY (isbn) REFERENCES Books, FOREIGN KEY(cid) REFERENCES Customers) CREATE TABLE Customers( cid INTEGER, cname CHAR(80), address CHAR(200), PRIMARY KEY (cid))

Bảng Orders chứa trường Order_date và khoá của bảng chỉ là hai trường isbn và ciid. Bởi vì, một khách hàng không thể đặt cùng một cuốn sách trong những ngày khác nhau, sự giới hạn đó đã không được dự kiến. Vì sao không thêm thuộc tính Order_date vào khoá của bảng Orders?

CREATE TABLE Orders( isbn CHAR(10), … PRIMARY KEY (isbn, cid), …)

Người kiểm tra, Dude2, sẽ không thích giải pháp này, cái mà anh ấy gọi là 'hack'. Anh ấy nói rằng không có một lược đồ ER tự nhiên nào thể hiện được thiết kế này. Dude1 không đồng ý với ý kiến này, và có ý kiến rằng, nên đến B&N để trình bày bản thiết kế trước và sẽ nhận được phản hồi từ phía họ; tất cả mọi người đồng ý với đề nghị này và họ cùng quay trở lại B&N.

Người quản lý của B&N bây giờ lại đưa thêm những yêu cầu mà anh ấy chưa đề cập đến trong lần gặp trước: "Những khách hàng nên được cho phép đặt một số các cuốn sách trong một hóa đơn duy nhất. Ví dụ, nếu khách hàng muốn đặt ba bản của cuốn 'The English Teacher' và hai bản của cuốn 'The Character of Physical Law', khách hàng nên được phép đặt cả hai trong một hóa đơn duy nhất."

Trưởng nhóm thiết kế, Dude1, điều này ảnh hưởng như thế nào đến nguyên tắc giao hàng của cửa hàng. B&N có giao tất cả các sách trong hóa đơn cùng nhau không? Người quản lý B&N giải thích nguyên tắc giao hàng của họ: "Ngay khi chúng tôi có đủ các bản của một cuốn sách trong hóa đơn, chúng tôi sẽ giao nó, mặc dù có những quyển sách khác chưa có đủ. Vì thế, có thể xảy ra, ba bản 'The English Teacher' được giao ngày hôm nay, nhưng 'The Character of Physical Law' lại được giao vào ngày mai, vì thực sự là hôm nay chúng tôi không còn đủ cuốn này trong kho. Thêm nữa, khách hàng của tôi có thể đặt nhiều hóa đơn trong một ngày, và họ muốn được xác nhận những hóa đơn mà họ đã đặt."

Nhóm DBDudes suy nghĩ về những yêu cầu trên và xác nhận lại: Đầu tiên, một hóa đơn có thể bao gồm nhiều cuốn sách. Thứ hai, khách hàng phải được phân biệt hai hóa đơn được đặt trong một ngày. Để đáp ứng hai yêu cầu này, họ giới thiệu một thuộc tính mới thêm vào bảng Orders gọi là ordernum (số hóa đơn), thuộc tính này giúp xác định duy nhất một hóa đơn và do đó khách hàng có thể đặt nó. Tuy nhiên, vì nhiều cuốn sách có thể được đặt trong cùng một hóa đơn, nên cả hai thuộc tính ordernum và isbn đều cần thiết để xác định qty (số lượng) và ship_date (ngày giao).

Thuộc tính Ordernum được gán giá trị là các số tự động tăng. Nếu có một vài hóa đơn được đặt cùng bởi một khách hàng trong một ngày thì những hóa đơn này có số khác nhau và hoàn toàn có thể phân biệt được nó. Câu lệnh SQL DDL để tạo bảng Orders mới như sau:

CREATE TABLE Orders( ordernum INTEGER, isbn CHAR(10), cid INTEGER, cardnum CHAR(16), qty INTEGER, order_date DATE, ship_date DATE, PRIMARY KEY (ordernum, isbn), FOREIGN KEY (isbn) REFERENCES Books, FOREIGN KEY(cid) REFERENCES Customers)

Người quản lý của B&N thực sự hài lòng với thiết kế bảng Orders như thế này, nhưng lại đưa ra thêm yêu cầu khác (DBDudes không bất ngờ về điều này, vì các khách hàng thường đưa ra những yêu cầu mới bổ sung những yêu cầu lần trước). Người quản lý muốn tất cả các nhân viên của cửa hàng có thể xem chi tiết từng hóa đơn đặt hàng để họ có thể trả lời những yêu cầu của khách hàng, người quản lý muốn thông tin về thẻ tín dụng của khách hàng (credit card) được hiển thị để dễ dàng theo dõi. Để giải quyết yêu cầu này, DBDudes đã tạo ra khung nhìn như sau:

CREATE VIEW OrderInfo (isbn, cid, qty, order_date, ship_date) AS SELECT O.cid, O.qty, O.order_date, O.ship_date FROM Orders O

Để lên kế hoạch cho phép nhân viên nào được nhìn thấy bảng này, người thiết kế phải giới hạn các quyền truy cập cho các loại nhân viên khác nhau. Chúng ta sẽ xem xét phần này kỹ hơn trong Phần 21.7.

Câu hỏi ôn tập

Trả lời các câu hỏi ôn tập sau, câu trả lời có thể tìm trong phần được liệt kê bên cạnh:

  • Quan hệ là gì? Phân biệt sự khác nhau giữa lược đồ quan hệ và minh họa quan hệ. Định nghĩa thuật ngữ bậc của quan hệ. Ràng buộc miền là gì? (Phần 1)
  • Cấu trúc nào của SQL cho phép định nghĩa một quan hệ? Cấu trúc nào cho phép sửa một minh họa quan hệ? (Phần 1.1)
  • Ràng buộc tham chiếu là gì? Định nghĩa thuật ngữ ràng buộc khóa chính và ràng buộc khóa ngoại. Những ràng buộc này được biểu diễn như thế nào trong SQL? Những loại ràng buộc nào bạn có thể biểu diễn trong SQL? (Phần 2)
  • DBMS làm gì khi những ràng buộc bị vi phạm? Người lập trình có thể điều khiển thời gian kiểm tra sự vi phạm ràng buộc trong giao dịch như thế nào? (Phần 3)
  • Truy vấn cơ sở dữ liệu quan hệ là gì? (Phần 4)
  • Chúng ta chuyển lược đồ ER vào các câu lệnh SQL như thế nào để tạo ra các bảng. Một kiểu thực thể được ánh xạ thành một quan hệ như thế nào? Một kiểu liên kết được ánh xạ vào quan hệ như thế nào? Ràng buộc trong mô hình ER, kiểu thực thể yếu, hệ thống phân cấp, khối kết hợp được sử dụng như thế nào? (Phần 5)
  • Khung nhìn là gì? Khung nhìn hỗ trợ sự độc lập dữ liệu logic như thế nào? Khung nhìn được sử dụng cho bảo mật như thế nào? Truy vấn thực hiện trên khung nhìn như thế nào? Vì sao SQL chỉ cho phép một số loại khung nhìn được phép cập nhật? (Phần 6)
  • Cấu trúc nào của SQL được dùng để sửa cấu trúc bảng, xóa bảng và khung nhìn? Điều gì xảy ra khi ta xóa khung nhìn? (Phần 7)

Bài tập

Định nghĩa các thuật ngữ sau: Lược đồ quan hệ, lược đồ cơ sở dữ liệu quan hệ, miền giá trị, minh họa quan hệ, lực lượng quan hệ, và bậc quan hệ.

Trả lời:Lược đồ quan hệ được dùng để biểu diễn cấu trúc của quan hệ hay còn gọi là bảng. Nó bao gồm một tập các tên cột, kiểu dữ liệu ứng với mỗi cột, và tên của toàn bộ bảng. Ví dụ, một lược đồ quan hệ có tên là Students có thể được biểu diễn như sau:

Students(sid: string, name: string, login: string, age: integer, gpa: real)

Quan hệ này có năm trường hay còn gọi là năm cột, cùng với tên và kiểu dữ liệu được chỉ ra như trên.

Lược đồ cơ sở dữ liệu quan hệ là tập những lược đồ quan hệ, biểu diễn một hoặc nhiều quan hệ.

Miền giá trị đồng nghĩa với kiểu dữ liệu. Các thuộc tính có thể được xem như các cột trong một bảng. Vì thế, miền thuộc tính tham chiếu tới kiểu dữ liệu của cột này.

Minh họa quan hệ là một tập các bộ giá trị (được biết đến như là các dòng hoặc các bản ghi) và mỗi dòng này đều thỏa mãn lược đồ quan hệ.

Lực lượng quan hệ là số lượng các bộ giá trị trong quan hệ.

Bậc quan hệ là số lượng các trường có trong quan hệ.

Có bao nhiêu bộ giá trị phân biệt trong một minh họa quan hệ với lực lượng 22?

Trả lời: Dành cho độc giả

Mô hình quan hệ, như nhìn thấy bằng cách viết truy vấn SQL, cung cấp sự độc lập dữ liệu vật lý hay độc lập dữ liệu logic? Giải thích?

Trả lời: Người dùng của SQL không có ý niệm về cách dữ liệu được biểu diễn vật lý như thế nào trong máy. Anh/cô ấy truy vấn quan hệ hoàn toàn dựa vào mức trừu tượng của quan hệ. Vì thế, độc lập dữ liệu được đảm bảo. Vì người dùng có thể định nghĩa khung nhìn, độc lập dữ liệu logic có thể cũng đạt được bằng cách sử dụng các định nghĩa khung nhìn để che đi những thay đổi trong lược đồ khái niệm.

Sự khác nhau giữa khóa dự tuyển và khóa chính trong một quan hệ là gì? Siêu khóa là gì?

Trả lời: Dành cho độc giả

Xem xét minh họa quan hệ Students trong Hình 1.

  1. Giả sử minh họa này là hợp lệ, đưa ra một hoặc một tập thuộc tính khóa không-dự-tuyển.
  2. Giả sử minh họa này là hợp lệ, đưa ra một hoặc một tập thuộc tính là khóa dự tuyển.

Trả lời: Những ví dụ về khóa không-dự-tuyển bao gồm: {name}, {age}. Bạn không thể xác định khóa của một quan hệ mà chỉ dựa vào một minh họa của nó. Thực tế, minh họa này là ‘hợp lệ’ vô hình. Khóa dự tuyển được định nghĩa ở đây là khóa, không phải là có lẽ là khóa. Minh họa này chỉ là một ‘snapshot’ có thể của quan hệ. Ở những thời điểm khác, quan hệ này có lẽ có một minh họa (hoặc snapshot) chứa những bộ giá trị khác nhau hoàn toàn, và chúng ta không thể dự đoán được những minh họa này chỉ dựa vào một minh họa mà đề bài cung cấp.

Ràng buộc khóa ngoại là gì? Vì sao ràng buộc này lại quan trọng? Ràng buộc tham chiếu là gì?

Trả lời: Dành cho độc giả

Xem xét quan hệ Students, Faculty, Courses, Rooms, Enrolled, Teaches, và Meets_In đã định nghĩa trong Phần 1.5.2.

  1. Liệt kê tất cả các ràng buộc khóa ngoại giữa các quan hệ này.
  2. Cung cấp một ví dụ của ràng buộc bao gồm một hoặc nhiều quan hệ mà không phải là ràng buộc khóa chính hay khóa ngoại.

Trả lời: Ở đây không có lý do gì để có một ràng buộc khóa ngoại (FKC) trên các quan hệ Students, Faculty, Courses, hoặc Rooms. Hầu hết chúng đều là các quan hệ cơ sở và phải đứng độc lập. Phải quan tâm đặc biệt đến việc nhập dữ liệu vào trong những quan hệ cơ sở này.

Trong quan hệ Enrolled, sid và cid cả hai nên có FKC trên chúng. (Những sinh viên hiện tại phải được đăng ký những khóa học hiện tại). Cũng như vậy, vì các giáo viên phải dạy những khóa học hiện tại, cả fid và cid trong quan hệ Teacher nên có FKCs. Cuối cùng, Meets _In nên đặt FKDs lên cả hai trường cid và rno.

Bạn cũng có thể thiết đặt một vài ràng buộc lên trên cơ sở dữ liệu này: chiều dàu của sid, cid, và fidcó thể được chuẩn hóa; giới hạn có thể được đặt trên kích thước của các số nhập vào các trường vv…

Trả lời tóm tắt các câu hỏi sau đây. Câu trả lời dựa trên lược đồ quan hệ như sau:

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct_ time: integer)

Dept(did: integer, dname: string, budget: real, managerid: integer)

  1. Cung cấp ví dụ về ràng buộc khóa ngoại trong quan hệ Dept. Những lựa chọn nào cho thực thi ràng buộc này khi người dùng cố gắng xóa bộ giá trị của Dept?
  2. Viết câu lệnh SQL yêu cầu tạo các quan hệ trên, bao gồm cả những ràng buộc khóa chính, khóa ngoại phù hợp.
  3. Định nghĩa quan hệ Dept trong SQL để tất cả Departments được đảm bảo có một người quản lý.
  4. Viết câu lệnh SQL để thêm 'John Doe' như là một nhân viên với eid = 101, age = 32 và salary = 15,000.
  5. Viết câu lệnh SQL để tăng lương tất cả các nhân viên lên 10%.
  6. Viết câu lệnh SQL để xóa Departments là 'Toy'. Cung cấp ràng buộc tham chiếu bạn lựa chọn cho lược đồ này, giải thích điều gì xảy ra khi câu lệnh này thực hiện.

Trả lời: Dành cho độc giả

Xem xét truy vấn SQL dựa trên kết quả được chỉ ra trong Hình 6.

  1. Sửa truy vấn này để chỉ có cột Login xuất hiện trong kết quả.
  2. Nếu mệnh đề Where S.gpa>=2 được thêm vào truy vấn, kết quả của câu lệnh này sẽ là gì?

Trả lời:

1. Chỉ có trường login trong kết quả:

SELECT S.login FROM Students S WHERE S.age < 18

2. Bộ giá trị về Madayan sẽ không hiển thị.

Giải thích vì sao thêm các ràng buộc NOT NULL vào định nghĩa SQL của quan hệ Manages (Trong Phần 3.5.3) sẽ không thiết đặt được ràng buộc mỗi Departments phải có một người quản lý. Điều gì đạt được khi yêu cầu trường ssn của Manages là Not Null?

Trả lời: Dành cho độc giả

Giả sử rằng có liên kết bậc ba R giữa ba kiểu thực thể A, B và C, trong đó A có ràng buộc khóa và ràng buộc toàn bộ và B có ràng buộc khóa; A có các thuộc tính a1 và a2, trong đó a1 là khóa; B và C tương tự. R không có các thuộc tính biểu diễn. Viết câu lệnh SQL để tạo các bảng tương ứng với những thông tin trên với những ràng buộc có thể. Nếu không thể thể hiện một vài ràng buộc, giải thích vì sao.

Trả lời: Các câu lệnh SQL sau tạo ra các quan hệ tương ứng:

CREATE TABLE A ( a1 CHAR(10), a2 CHAR(10), b1 CHAR(10), c1 CHAR(10), PRIMARY KEY (a1), UNIQUE (b1), FOREIGN KEY (b1) REFERENCES B, FOREIGN KEY (c1) REFERENCES C ) CREATE TABLE B ( b1 CHAR(10), b2 CHAR(10), PRIMARY KEY (b1) ) CREATE TABLE C ( b1 CHAR(10), c2 CHAR(10), PRIMARY KEY (c1) )

Câu lệnh SQL đầu tiên gán liên kết R vào bảng A và vì thế đảm bảo ràng buộc tham dự.

Xem xét kịch bản của Bài 2.2, nơi bạn thiết kế lược đồ ER cho cơ sở dữ liệu University. Viết câu lệnh để tạo ra các quan hệ tương ứng và thể hiện nhiều ràng buộc nhất có thể. Nếu bạn không thể hiện được một vài ràng buộc, giải thích vì sao.

Trả lời: Dành cho độc giả

Xem xét cơ sở dữ liệu University trong Bài 2.3 và lược đồ ER bạn thiết kế. Viết câu lệnh để tạo ra các quan hệ tương ứng và thể hiện nhiều ràng buộc nhất có thể. Nếu bạn không thể hiện được một vài ràng buộc, giải thích vì sao.

Trả lời: Những câu lệnh SQL sau tạo ra các quan hệ tương ứng.

1. CREATE TABLE Professors( prof_ssn CHAR(10), name CHAR(64), age INTEGER, rank INTEGER, speciality CHAR(64), PRIMARY KEY (prof_ssn)) 2. CREATE TABLE Depts( dno INTEGER, dname CHAR(64), office CHAR(10), PRIMARY KEY (dno)) 3. CREATE TABLE Runs( dno INTEGER, Prof_ssn CHAR(10), PRIMARY KEY (dno, prof_ssn), FOREIGN KEY (prof_ssn) REFERENCES Professors, FOREIGN KEY (dno) REFERENCES Depts ) 4. CREATE TABLE Work_Dept( dno INTEGER, Prof_ssn CHAR(10), pc_time INTEGER, PRIMARY KEY (dno, prof_ ssn), FOREIGN KEY (prof_ssn) REFERENCES Professors, FOREIGN KEY (dno) REFERENCES Depts )

Quan sát thấy rằng chúng ta có thể cần những ràng buộc kiểm tra hoặc các xác nhận trong SQL để thiết đặt các một nguyên tắc là các Professors làm việc ở ít nhất một Department.

5. CREATE TABLE Project ( pid INTEGER, sponsor CHAR(32), start_date DATE, end_date DATE, budget FLOAT, PRIMARY KEY (pid) ) 6. CREATE TABLE Graduates ( grad_ssn CHAR(10), age INTEGER, name CHAR(64), deg_prog CHAR(32), major INTEGER, PRIMARY KEY (grad_ssn), FOREIGN KEY (major) REFERENCES Depts)

Ghi nhớ rằng bảng Major không cần thiết vì mỗi Graduate chỉ có một major và vì thế nó có thể là một thuộc tính trong bảng Graduates.

7. CREATE TABLE Advisor ( senior_ssn CHAR(10), Grad_ssn CHAR(10), PRIMARY KEY (senior_ssn, grad_ssn), FOREIGN KEY (senior_ssn) REFERENCES Graduates (grad_ssn), FOREIGN KEY (grad_ssn) REFERENCES Graduates ) 8. CREATE TABLE Manages ( pid INTEGER, Prof_ssn CHAR(10), PRIMARY KEY (pid, prof_ssn), FOREIGN KEY (prof_ssn) REFERENCES Professors, FOREIGN KEY (pid) REFERENCES Projects ) 9. CREATE TABLE Work_In ( pid INTEGER, Prof_ssn CHAR(10), PRIMARY KEY (pid, prof_ssn), FOREIGN KEY (prof_ssn) REFERENCES Professors, FOREIGN KEY (pid) REFERENCES Projects )

Quan sát thấy rằng chúng ta không thể thiết đặt được ràng buộc tham dự cho Projects trong bảng Work_In vì trong SQL thiếu những ràng buộc kiểm tra hoặc các xác nhận.

10. CREATE TABLE Supervises ( prof_ssn CHAR(10), Grad_ssn CHAR(10), pid INTEGER, PRIMARY KEY (prof_ssn, grad_ssn, pid), FOREIGN KEY (prof_ssn) REFERENCES Professors, FOREIGN KEY (grad_ssn) REFERENCES Graduates, FOREIGN KEY (pid) REFERENCES Projects )

Ghi nhớ rằng chúng ta không cần một bảng tường minh cho quan hệ Worrk_Proj vì tất cả những lần một Graduate làm việc cho một Project, anh/cô ấy đều phải có một Supervisor.

Xem xét kịch bản của Bài 2.4, nơi bạn thiết kế lược đồ ER cho cơ sở dữ liệu Company. Viết câu lệnh để tạo ra các quan hệ tương ứng và thể hiện nhiều ràng buộc nhất có thể. Nếu bạn không thể hiện được một vài ràng buộc, giải thích vì sao.

Trả lời: Dành cho độc giả

Xem xét cơ sở dữ liệu Notown của Bài 2.5. Bạn đã quyết định để Notown sử dụng hệ quản trị cơ sở dữ liệu để lưu trữ dữ liệu của công ty. Chỉ ra các câu lệnh SQL để tạo ra các quan hệ tương ứng với các kiểu thực thể và kiểu quan hệ trong thiết kế của bạn. Chỉ ra những ràng buộc nào trong lược đồ ER mà bạn không thể thể hiện được bằng câu lệnh SQL và giải thích tóm tắt vì sao bạn không thể biểu diễn chúng.

Trả lời: Những lệnh SQL sau tạo ra các quan hệ tương ứng.

1. CREATE TABLE Musicians( ssn CHAR(10), name CHAR(30), PRIMARY KEY (ssn)) 2. CREATE TABLE Instruments( instrId CHAR(10), dname CHAR(30), key CHAR(5), PRIMARY KEY (instrId)) 3. CREATE TABLE Plays( ssn CHAR(10), instrId INTEGER, PRIMARY KEY (ssn, instrId), FOREIGN KEY (ssn) REFERENCES Musicians, FOREIGN KEY (instrId) REFERENCES Instruments ) 4. CREATE TABLE Songs_Appears ( songId INTEGER, author CHAR(30), title CHAR(30), albumIdentifier INTEGER NOT NULL, PRIMARY KEY (songId), FOREIGN KEY (albumIdentifier) References Album Producer) 5. CREATE TABLE Telephone_Home ( phone CHAR(11), address CHAR(30), PRIMARY KEY (phone), FOREIGN KEY (address) REFERENCES Place) 6. CREATE TABLE Lives ( ssn CHAR(10), phone CHAR(11), address CHAR(30), PRIMARY KEY (ssn, address), FOREIGN KEY (phone, address) References Telephone_Home, FOREIGN KEY (ssn) REFERENCES Musicians ) 7. CREATE TABLE Place ( address CHAR(30) ) 8. CREATE TABLE Perform ( songId INTEGER, ssn CHAR(10), PRIMARY KEY (ssn, songId), FOREIGN KEY (songId) REFERENCES Songs, FOREIGN KEY (ssn) REFERENCES Musicians ) 9. CREATE TABLE Album Producer ( albumIdentifier INTEGER, ssn CHAR(10), copyrightDate DATE, speed INTEGER, title CHAR(30), PRIMARY KEY (albumIdentifier), FOREIGN KEY (ssn) REFERENCES Musicians )

Chuyển lược đồ ER của bạn trong Bài 2.6 vào lược đồ quan hệ và chỉ ra các câu lệnh SQL cần thiết để tạo ra các quan hệ, chỉ sử dụng các ràng buộc khóa và ràng buộc Null. Nếu việc chuyển của bạn không thể minh họa được một vài ràng buộc trong lược đồ ER, giải thích vì sao.

Trong Bài 2.6, bạn cũng sửa lược đồ ER để nó bao gồm ràng buộc rằng, những kiểm tra trên một máy bay phải được thực hiện bằng kỹ sư, người là chuyên gia về loại máy bay này. Bạn có thể sửa các câu lệnh SQL đã dùng để định nghĩa các quan hệ (là kết quả của việc ánh xạ lược đồ ER) để thực hiện ràng buộc này.

Trả lời: Dành cho độc giả

Xem xét lược đồ ER bạn đã thiết kế cho Prescriptions-R-X trong Bài 2.7. Sử dụng SQL, định nghĩa các quan hệ tương ứng với các kiểu thực thể và kiểu liên kết trong thiết kế của bạn.

Trả lời: Những câu lệnh tạo ra các bảng tương ứng của kiểu thực thể Doctor, Pharmacy, và Pharm_co rất dễ thực hiện, độc giả tự viết. Những bảng khác có thể được tạo như sau:

1. CREATE TABLE Pri_Phy_Patient ( ssn CHAR(11), name CHAR(20), age INTEGER, address CHAR(20), phy_ssn CHAR(11), PRIMARY KEY (ssn), FOREIGN KEY (phy_ssn) REFERENCES Doctor) 2. CREATE TABLE Prescription ( ssn CHAR(11), Phy_ssn CHAR(11), date CHAR(11), quantity INTEGER, trade_name CHAR(20), pharm_id CHAR(11), PRIMARY KEY (ssn, phy_ssn), FOREIGN KEY (ssn) REFERENCES Patient, FOREIGN KEY (phy_ssn) REFERENCES Doctor, FOREIGN KEY (trade_name, pharm_id) References Make_Drug) 3. CREATE TABLE Make_Drug (trade_name CHAR(20), Pharm_id CHAR(11), PRIMARY KEY (trade_name, pharm_id), FOREIGN KEY (trade_name) REFERENCES Drug, FOREIGN KEY (pharm_id) REFERENCES Pharm_co) 4. CREATE TABLE Sell ( price INTEGER, name CHAR(10), trade_name CHAR(10), PRIMARY KEY (name, trade_name), FOREIGN KEY (name) REFERENCES Pharmacy, FOREIGN KEY (trade_name) REFERENCES Drug) 5. CREATE TABLE Contract ( name CHAR(20), Pharm_id CHAR(11), Start_date CHAR(11), End_date CHAR(11), text CHAR(10000), supervisor CHAR(20), PRIMARY KEY (name, pharm_id), FOREIGN KEY (name) REFERENCES Pharmacy, FOREIGN KEY (pharm_id) REFERENCES Pharm_co)

Viết câu lệnh SQL để tạo ra các quan hệ tương ứng với lược đồ ER bạn đã thiết kế trong Bài 2.8. Nếu việc chuyển của bạn không thể minh họa được một vài ràng buộc trong lược đồ ER, giải thích vì sao.

Trả lời: Dành cho độc giả

Trả lời tóm tắt các câu hỏi sau đây dựa vào lược đồ cho bên dưới:

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct_ time: integer)

Dept(did: integer, dname: string, budget: real, managerid: integer)

  1. Giả sử bạn có khung nhìn SeniorEmp được định nghĩa như sau:
CREATE VIEW SeniorEmp(sname, sage, salary) AS SELECT E.ename, E.age, E.salary FROM Emp E WHERE E.age>50

Giải thích ví sao hệ thống sẽ không thực hiện truy vấn sau:

SELECT S.sname FROM SeniorEmp S WHERE S.salary>100,000
  1. Cung cấp ví dụ một khung nhìn trên Emp có thể được tự động cập nhật khi cập nhật Emp.
  2. Cung cấp ví dụ một khung nhìn trên Emp không có khả năng cập nhật tự động và giải thích vì sao lại không có khả năng này.

Trả lời: Trả lời mỗi câu hỏi như sau:

1. Hệ thống sẽ làm như sau:

SELECT S.name FROM (SELECT E.ename AS name, E.age, E.salary FROM Emp E WHERE E.age > 50 ) AS S WHERE S.salary > 100000

2. Khung nhìn sau trên Emp có thể được cập nhật một cách tự động bằng việc cập nhật Emp:

CREATE VIEW SeniorEmp (eid, name, age, salary) AS SELECT E.eid, E.ename, E.age, E.salary FROM Emp E WHERE E.age > 50

3. Khung nhìn sau không thể được cập nhật một cách tự động vì nó không rõ ràng bản ghi nào của Emp sẽ được cập nhật khi có một lệnh cập nhật nào đó.

CREATE VIEW AvgSalaryByAge (age, avgSalary) AS SELECT E.eid, AVG (E.salary) FROM Emp E GROUP BY E.age

Xem xét lược đồ sau:

Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

Quan hệ Catalog liệt kê danh sách các giá trả cho các sản phẩm của các nhà cung cấp. Trả lời các câu hỏi sau:

  • Cung cấp một ví dụ của một khung nhìn có khả năng cập nhật trên một quan hệ.

Trả lời: Dành cho độc giả

Bài tập lớn

Bài 21 Tạo ra các quan hệ Students, Faculty, Courses, Rooms, Enrolled, Teaches, và Meets_In trong Minibase.

Bài 22 Thêm những bộ giá trị trong Hình 1 and 4 vào trong quan hệ Students và Enrolled. Tạo ra những minh họa có thể của các quan hệ khác.

Bài 23 Những ràng buộc tham chiếu nàp được thiết đặt bởi Minibase?

Bài 24 Chạy những truy vấn SQL trình bày trong chương này.

TÀI LIỆU THAM KHẢO

Mô hình quan hệ đã được đề xuất trong một bài báo của Codd [187]. Childs [176] và Kuhns [454] đã tiên đoán trước về sự phát triển này. Gallaire and Minker's book [296] chứa một vài bài báo về cơ sở dữ liệu quan hệ. Một hệ thống dựa trên mô hình quan hệ lưu trữ toàn bộ dữ liệu trong một quan hệ được gọi là universal relation, được trình bày trong [746]. Những mở rộng của mô hình quan hệ cùng với những giá trị null được một số tác giả bàn đến, ví dụ [329, 396, 622, 754, 790].

Những dự án tiên phong bao gồm System R [40, 150] của IBM San Jose Research Laboratory (bây giờ là IBM Almaden Research Center), Ingres [717] ở trường đại học California ở Berkeley, RTV [737] của IBM UK Scientific Center ở Peterlee, và QBE [801] ở IBM T.J.Watson Research Center.

Một phần lý thuyết đầy đủ và chắc chắn về lĩnh vực cơ sở dữ liệu quan hệ. Các tài liệu về những lý thuyết này của Atzeni và DeAntonellis [45]; Maier [501]; và Abiteboul, Hull, và Vianu [3]. [415] là những nội dung thực sự có giá trị.

Những ràng buộc toàn vẹn trong cơ sở dữ liệu quan hệ đã được trình bày rất nhiều. [190] giải quyết những mở rộng ngữ nghĩa của mô hình quan hệ, và toàn vẹn, cụ thể là toàn vẹn tham chiếu. [360] trình bày về các ràng buộc toàn vẹn ngữ nghĩa. [230] có các bài báo giải quyết một loạt các ảnh hưởng của ràng buộc toàn vẹn, bao gồm những bàn luận chi tiết về ràng buộc toàn vẹn. Một loạt tài liệu liên quan đến ràng buộc toàn vẹn tham chiếu. [51] so sánh giá của ràng buộc toàn vẹn tham chiếu thông qua thời gian biên dịch, thời gian chạy. [145] trình bày về ngôn ngữ dựa trên SQL để xác định các ràng buộc toàn vẹn và các điều kiện định danh. [713] trình bày về các công nghệ để kiểm tra ràng buộc toàn vẹn. [180] trình bày về các ràng buộc toàn vẹn thời-gian-thực. Những bài báo khác trình bày về các toàn vẹn kiểm tra trong cơ sở dữ liệu bao gồm [82, 122, 138, 517]. [681] xem xét cách tiếp cận của việc kiểm tra sự đúng đắn của các chương trình truy cập cơ sở dữ liệu thay vì các kiểm tra run-time. Một vài bài báo được viết sớm hơn trong hướng nghiên cứu này là [296] và [295].

Trong phần tham khảo về SQL, nhìn vào phần nội dung tham khảo ở Chương 5. Quyển sách này không trình bày về các sản phẩm dựa trên mô hình quan hệ, nhưng có rất nhiều sách hay trình bày về các hệ thống thương mại chính; ví dụ, Chamberlin’s DB2 [149], Date and McGoveran's book on Sybase [206], và Koch and Loney's book on Oracle [443].

Một vài bài báo xem xét vấn đề chuyển những cập nhật trên các khung nhìn thành các cập nhật trên các bảng cơ sở [59, 208, 422, 468, 778]. [292] là tài liệu tốt về về chủ đề này. Xem phần tài liệu tham khảo ở Chương 25 để hiểu thêm về truy vấn khung nhìn và duy trì khung nhìn.

[731] trình bày về phương pháp thiết kế dựa vào lược đồ ER và sau đó ánh xạ thành mô hình quan hệ . Markowitz nghiên cứu về ràng buộc tham chiếu trong ngữ cảnh lược đồ ER ánh xạ sang mô hình quan hệ và những thảo luận xung quanh cách thực thi trong các hệ thống thương mại [513, 514].