Giáo trình Cơ sở dữ liệu 1 (Phần 2)

Nội dung text: Giáo trình Cơ sở dữ liệu 1 (Phần 2)

1. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin CHƯƠNG 3 LÝ THUYẾT THIẾT KẾ CƠ SỞ DỮ LIỆU 3.1 Giới thiệu 3.1.1 Vấn đề thiết kế cơ sở dữ liệu Một cơ sở dữ liệu quan hệ gồm tập các quan hệ. Muốn xây dựng một cơ sở dữ liệu quan hệ cần xác định trong cơ sở dữ liệu đó có những quan hệ gì, mỗi quan hệ có những thuộc tính nào, sự liên kết giữa các quan hệ như thế nào? Từ cơ sở phân tích chúng ta mới xây dựng nên sơ đồ thực thể liên kết, xác định các quan hệ và các liên kết cần thiết, chỉnh sửa chuẩn hoá các quan hệ trong hệ thống cơ sở dữ liệu / Bước cuối cùng là nhập dữ liệu theo dõi bảo trì cập nhật, hoàn thiện các quan hệ, các liên kết trong hệ thống theo yêu cấu của người dùng 3.1.2 Bài toán ví dụ Giả sử một cửa hàng bán lẻ các nhân viên mở sổ theo dõi việc bán hàng hàng ngày là một bảng (quan hệ) như sau: Nhận xét: - Cơ sở dữ liệu trên chỉ có một bảng (quan hệ) - Một số thuộc tính lặp lại nhiều lần như: Tên hàng, Đơn giá Ta tách bảng trên thành 3 bảng: BH1 30
2. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin BH2 BH3 Với cách tổ chức này ta thấy: - Cơ sở dữ liệu gồm 3 bảng (quan hệ) - Trong mỗi quan hệ không có sự dư thừa dữ liệu - 3.1.3 Kết luận Cách tổ chức dữ liệu thứ hai tết hơn thuận lợi hơn cho việc áp dụng máy tính vào xử lý khắc phục những hiện tượng nảy sinh khi cập nhật, sửa chữa dữ liệu như: -Dư thừa - Không nhất quán Cơ sở để tách các quan hệ dựa trên sự phụ thuộc giữa các thuộc tính (gọi là phụ thuộc hàm) nghĩa là từ thuộc tính này có thể suy ra thuộc tính kia: Ví dụ: Từ mã hàng ta có thể suy ra tên hàng Mã hàng là “A1” thì “tên hàng” phải là xe đạp Mã hàng là “A2” thì “tên hàng” phải là xe máy Việc tách các quan hệ thành các quan hệ con ta gọi là phép chuẩn hoá 3.2 Sơ đồ quan hệ 3.2.1 Phụ thuộc hàm Cho tập thuộc tính U. Một phụ thuộc hàm trên U là công thức dạng: Nếu f:X → Y là một phụ thuộc hàm trên U thì ta nói rằng tập thuộc tính Y phụ thuộc hàm vào tập thuộc tính X. (hay tập thuộc tính X xác định hàm tập thuộc tính Y. 31
3. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Cho quan hệ R(U) và một PTH f: x → Y trên U. Ta nói quan hệ R thoả PTH f và viết R(f) nếu với 2 bộ bất kỳ ti, tj ∈ R giống nhau trên X thì chúng cũng giống nhau trên Y. Hay R(X → Y) ⇔ (∀ u,v ∈ R): u.X=v.X ⇒ u.Y = v.Y. Trong đó u, v là hai bộ bất kỳ thuộc quan hệ R. Nếu Y không phụ thuộc hàm vào X ta có thể viết X! ⇒ Y Ví dụ: Trong bảng sau ta có sự phụ thuộc hàm của thuộc tính “họ tên” vào thuộc tính “mã sinh viên” - Trên mỗi quan hệ ta có thể tìm thấy một tập các phụ thuộc hàm, gọi tập phụ thuộc hàm là tập F. Mỗi cơ sở dữ liệu thực tế thường có tập F rất lớn nên ta phải chọn sao cho thích hợp. Trong quan hệ trên ta có: - Tên sinh viên phụ thuộc vào mã sinh viên (mã sinh viên → tên ) - Quê quán phụ thuộc hàm vào mã sinh viên (mã sinh viên → quê quán) - Ký hiệu một phụ thuộc hàm là f. Ký hiệu một tập phụ thuộc hàm là F: F = {X → Y, X → Z, XZ → K } 3.2.2 Lược đồ quan hệ Một lược đồ quan hệ r là một cặp (U,F) trong đó U là tập hữu hạn các thuộc tính, F là tập các phụ thuộc hàm xác định trên U. Ví dụ: Cho lược đồ quan hệ r(U,F), với U = { A,B,C,D,E } VÀ F = {A → BC, B → D, AD → E} Một lược đồ quan hệ có thể tương đương với một lược đồ quan hệ khác tết hơn trong việc áp dụng các thao tác dữ liệu, đó là cơ sở cho việc chuẩn hoá một lược đồ quan hệ 3.3 Hệ tiên đề cho tập phụ thuộc hàm 3.3. 1 Đặt vấn đề Ta thấy với các bài toán quản lý khác nhau thì ta phải làm việc với các loại dữ liệu khác nhau, như vậy sẽ không có một phương pháp tổng quát cho mọi loại dữ liệu Hay nói cách khác sẽ không có một lý thuyết mà có thể áp dụng cho mọi cơ sở dữ liệu. Điều đó dẫn đến bài toán tổ chức cơ sở dữ liệu chỉ là một bài toán thủ công 32
4. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin không thể áp dụng các công cụ toán học và quá trình xử lý trên máy tính được. Từ đó người ta trên một giải pháp sao cho có thế khái quát hoá các cơ sở dữ liệu bằng mô hình toán học và có thể áp dụng được các công cụ toán học. Trong cơ sở dữ liệu khái quát đó, các thuật toán xử lý không phụ thuộc vào ý nghĩa của các thuộc tính cụ thể mà chỉ phụ thuộc vào các ràng buộc đã xác định qua tập thuộc tính và tập phụ thuộc hàm. Ví dụ: Ta có lược đồ quan hệ r(U, F) với U là tập hữu hạn các thuộc tính U: {A, B, C} , F là tập các PTH F : {A → BC} Ta có thể coi A là số báo danh; B là tên; C là tuổi Cũng có thể coi A là tên hàng; B đơn giá; C là khối lượng Dù tên cụ thể của A, B, C là gì thì tập U và F cũng vẫn đúng khô phụ thuộc vào tên cụ thể của các thuộc tính. Từ vấn đề trên Armstrong đã nghiên cứu và đưa ra mô hình bài toán khái quát với các tiên đề áp dụng cho mọi cơ sở dữ liệu 3.3.2 Hệ tiên đề Armstrong Cho lược đồ quan hệ r(U,F) với U= { A1, A2, An} là tập các thuộc tính . Giả sử X, Y, Z ∈ U, ta có hệ tiên đề Armstrong sau: 1. Tiên đề phản xạ Mọi tập con của X thì đều phụ thuộc hàm vào X Nếu Y ⊆ X thì X → Y 2. Tiên đề tăng trưởng NẾU Z ∈ U ; X → Y thì XZ → YZ 3. Tiên đề bắc cầu NẾU X → Y; Y → Z thì X → Z Từ các tiên đề ta có các tính chất trên sơ đồ quan hệ r(U,F); X,Y,Z,W ⊆ U 1 Tính phản xạ chặt NẾU X → X 2 . Tính tựa bắc cầu : Nếu X → Y và YZ → W thì XZ → W 3. Tính mở rộng vế trái và thu hẹp vê phải NẾU X → Y thì XZ → Y\W 33
5. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin 4 . Tính cộng đầy đủ Nếu X → Y và Z → W thì XZ → YW 5. Tính mở rộng vê trái NẾU X → Y mà WX → Y 6. Tính cộng ở vê phải Nếu X → Y và X → Z thì X → YZ 7. Tính bộ phận ở vê phải Nếu X → YZ thì X → Y 8. Tính tích luỹ NẾU X → YZ, Z → W thì X → YZW Khi giải quyết các bài toán ta có thể áp dụng các tiên đề Amstrong hoặc các tính chất trên 3.3.3 Bài toán áp dụng Cho lược đồ quan hệ R(U,F) với U={A, B, C} F = {AB → C; C → A } . Chứng minh BC → ABC Giải: Từ C → A (gt) Theo tiên đề tăng trưởng thêm vào hai vế B ta có: BC → AB (l) Từ AB → C (gt) Thêm AB vào hai vế ta có: AB → ABC (2) Từ (1) và (2) theo tiên đề bắc cầu ta có: BC → ABC đó là điều phải chứng minh 3.3.4 Kiểm tra tính đúng đắn của hệ tiên đề Amstrong Giả sử có bảng DS cán bộ: MÃCB, Tên CB, Mã lương, Bậc lương Trong đó: Mã CB → Tên CB, Mã lương, Bậc lương Mã lương → Bậc lương Mô hình hoá bằng các thuộc tính sau: Cho lược đồ quan hệ R(U,F). Trong đó U = {A,B,C,D} 34
6. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin F = {A → B,C,D; C → D} ™ Kiểm tra tiên đề 1 Nếu đặt X = AB rõ ràng A ⊆ AB Với hai bộ bất kỳ ti, tj ta đều có Nếu ti.AB = tj.AB Thì ti.A = tj.A Hiển nhiên ta thấy AB → A ™ Kiểm tra tiên đề 2 Đặt X = AB và XC = ABC Đặt Y = D và YC = DC Với hai bộ bất kỳ ti, tj ta thấy Nếu ti.ABC = tj.ABC Thì ti.DC = tj.DC Như vậy tiên đề thứ hai là đúng đắn ™ Kiểm tra tiên đề 3 Theo tiên đề 3 ta thấy A → C ; C → D thì có thể suy ra A → D Với hai bộ bất kỳ ti, tj Nếu t1.A = t2.A Thì t1.D = t2.D Vậy tiên đề này hoàn toàn đúng 3.4 Bao đóng của tập phụ thuộc hàm - Gọi F là tập các phụ thuộc hàm trong lược đồ quan hệ R(U). X,Y ⊆ U. Nếu quan hệ xác định trên R(U) thoả cả X → Y thì X → Y cũng là một phụ thuộc hàm thuộc F khi đó đặt F ∪ X → Y = F+. F+ gọi là bao đóng của F. Ta nói X → Y được suy diễn lôgic từ F F+ = {f:X → V| X,Y ∈ U F f} - Nếu có F = F+ thì F là họ đầy đủ của các phụ thuộc hàm - Ví dụ: Cho lược đồ quan hệ r(U,F), với U : { A,C,B} và F : { A → B, B → C} ta có thể suy ra A → C. Rõ ràng phụ thuộc hàm A → C được suy diễn ra từ F. Ta có F+ = {A → B, B → C, A → C} 35
7. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin 3.5 Phép tách một quan hệ 3.5.1 Định nghĩa Cho lược đồ quan hệ r xác định trên tập thuộc tính U và F là tập các phụ thuộc hàm. Phép tách lược đồ quan hệ r(U) là việc thay thế lược đồ quan hệ r(U) bằng các tập lược đồ r1(U1), r2(U2), rm(Um), sao cho r(U) = r1(U1) ∪ r2(U2) ∪ rm(Um) Trong đó ri(Ui) ⊆ r(U), i=l, ,m và ký hiệu phép tách của r là ρ . ρ = {r1(U1), r2(U2), rm(Um)} Nói rằng ρ là phép tách - kết nối không mất mát thông tin đối với F nếu với mỗi quan hệ R xác định trên r thoả F thì R = Π U1(R) * Π U2(R) * Π U3(R) * * Π Um(R) ). 3.5.2 Định lý Cho quan hệ R(U), Gọi R1(U1)và R2(U2) là phép tách hai không mất mát thông tin của R(U) nếu: Chứng minh: Ta phải chứng minh hai vấn đề: ♦ Giả sử xét bảng dữ liệu sau: Tên Phách Điểm Nam 01 7 Bắc026 Nam 03 4 U = {tên, phách, điểm} Nếu tách U1 = {tên}; U2 = {phách, điểm} Thì U1 ∩ U2 = Φ Rõ ràng ta thấy dữ liệu không còn chính xác. Minh hoạ bằng bảng sau ta thấy: 36
8. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin 1 2 ♦ Giả sử chọn bộ t nào đó thuộc R. Khi tách thành R1, R2 ta được t , t Ta thấy t = t1 * t2 hay R ⊆ Rl * R2 1 Mặt khác ∀ t ∈ R1 ; và ∀ t2 ∈ R2 ta có: 1 2 t[U1 ∩ U2] = t [U1 ∩ U2] Theo tính chất phép toán kết nối tự nhiên ta có: 1 2 t * t = t Hay R1* R2 ⊆ R Như vậy ta có R1 * R2 = R Định lý được chứng minh Nhận xét: Nếu ta tách một lần được hai quan hệ , tách hai lần được 3 quan hệ vậy muốn tách m quan hệ phải tách (m - 1) lần 3.5.3 Kiểm tra phép tách không mất mát thông tin - Cho lược đồ quan hệ r(U,F), Trong đó, tập các thuộc tính U = {A1,A2, An} và tập các phụ thuộc hàm F; phép tách ρ . Hãy kiểm tra phép tách ρ : ρ = (R1, R2, Rm) có mất mát thông tin không? Thuật toán: Bước l: Lập một bảng gồm có n cột, m hàng. Cột thứ i ứng với thuộc tính Aj hàng thứ i ứng với lược đồ ri. Tại hàng i cột j viết ký hiệu aj nếu Aj ∈ ri; ngược lại điền ký hiệu bij Bước 2: Áp dụng quy trình thay thế đuổi trên bảng trên: Xét các phụ thuộc hàm từ F (dạng X → Y); xét các hàng nếu có giá trị bằng nhau trên thuộc tính X thì làm bằng nhau các giá trị trên các thuộc tính của Y theo nguyên tắc: nếu trên các hàng bằng nhay ấy có ít nhất một bộ có giá trị tại thuộc tính Y là aj thì thay thế giá trị tại thuộc tính Y ở các bộ khác là aj ngược lại thay bởi một bu tuỳ ý. áp dụng cho các phụ thuộc hàm đến khi không tạo ra được bảng mới nữa hay xuất hiện ít nhất một hàng có đủ các giá trị a1, a2, a3, an . Bước 3: Xét bảng kết quả nếu xuất hiện một hàng có đủ các giá trị a1, a2, a3, an thì ta kết luận phép tách ρ là không mất mát thông tin (bảo toàn thông tin). Ngược lại phép tách ρ không bảo toàn thông tin (mất mát thông tin). ví dụ: 37
9. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Cho quan hệ: HOCSINH (SBD, TEN, DTOAN, DTIN) Với các phụ thuộc hàm: SBD → TEN; SBD → DTOAN, DTIN Tách thành hai quan hệ: HS1(SBD, TEN) HS2(SBD, DTOSN,DTIN) + Lập bảng kiểm tra như sau: SBD TEN DTOAN DTIN HSI a1 a2 b13 b14 HS2 a1 b22 a3 a4 + Làm bằng các giá trị Ta thấy dòng 2 tại thuộc tính TEN có giá tri là a2 và b22 mà SBD của hai dòng này có giá trị là a2. Vậy theo phụ thuộc hàm SBD → TEN nên ta thay giá trị b22 của thuộc tính TEN tại dòng 2 là a2 Ta có bảng: SBD TEN DTOAN DTIN HS1 a1 a2 b13 b14 HS2 a1 a2 a3 a4 Vậy bảng có dòng 2 toàn là giá trị aj (j = 1 4), nên phép tách trên là không mất mát thông tin 3.6 Chuẩn hoá lược đồ quan hệ Khi thiết kế một lược đồ quan hệ phải tuân theo một số nguyên tắc để khi thao tác trên cơ sở dữ liệu không dẫn đến sự dị thường vê dữ liệu. Công việc thiết kế dữ liệu theo một dạng chuẩn nào đó gọi là chuẩn hoá dữ liệu. 3.6.1 Các dạng chuẩn trong lược đồ quan hệ Do việc cập nhật dữ liệu (qua phép chèn, loại bỏ và sửa đổi) gây nên những dị thường, cho nên các quan hệ nhất thiết phải được biến đổi thành các dạng phù hợp, quá trình đó gọi là quá trình chuẩn hoá. Quan hệ được chuẩn hoá là quan hệ trong đó mỗi miền của một thuộc tính chỉ chứa những giá trị nguyên tố, tức là không phân nhỏ được nữa và do đó mỗi giá trị trong quan hệ cũng là nguyên tố. Quan hệ có chứa các miền giá trị là không nguyên tố gọi là quan hệ không chuẩn hoá. Một quan hệ được chuẩn hoá có thể tách thành một hoặc nhiều quan hệ chuẩn 38
10. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin hoá khác và không làm mất mát thông tin. Có các dạng chuẩn sau: 1NF, 2NF, 3NF, BCNF 3.6.2 Một số các định nghĩa a) Thuộc tính khoá - Cho lược đồ quan hệ rộn với tập các thuộc tính U, Ai → U; A gọi là thuộc tính khoá của R nếu tồn tại K ⊆ U, Nếu A ∈ K mà K là khoá thì A là thuộc tính khoá Nếu A ∉ K thì A là thuộc tính không khoá b) Phụ thuộc hàm đầy đủ Cho R(U) X,Y ⊆ U, Y gọi là phụ thuộc hàm đầy đủ vào X, Nếu X → Y và ∀ A ∈ X; (X - {A})! → Y Phụ thuộc hàm đầy đủ ký hiệu là X+ → Y c) Phụ thuộc hàm bắc cầu - Cho R(U) X,Y ⊆ U Y gọi là phụ thuộc hàm bắc cầu vào X nếu ∃ Z ⊆ U: Y- Z ≠ φ , X → Z, Z!→ X, Z → Y, Y! → X Nếu không ta nói Y phụ thuộc hàm trực tiếp vào X. Phụ thuộc hàm bắc cầu ký hiệu là X% → Y 3.6.3 Dạng chuẩn 1NF (1st Normal Form) Lược đồ quan hệ r gọi là dạng chuẩn 1 nếu mọi giá trị xuất hiện trong r đều là giá trị nguyên tố. Ví dụ: TÊN HÀNG số LƯỢNG NHẬP XUẤT H1 15 10 H2 20 18 Lược đồ quan hệ này không phải là dạng chuẩn 1 vì giá trị trong thuộc tính SỐ LƯỢNG không phải là nguyên tố 3.6.4 Dạng chuẩn 2NF (2nd Normal Form) Lược đồ quan hệ r gọi là dạng chuẩn 2NF nếu đã là dạng chuẩn 1 và mọi thuộc tính không khoá của r đều phụ thuộc hàm đầy đủ vào khoá. 3.6.5 Dạng chuẩn 3NF (3rd Normal Form) 39
11. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Lược đồ quan hệ r gọi là dạng chuẩn 3NF nếu đã ở dạng chuẩn 2 và mọi thuộc tính không khoá của r không phụ thuộc hàm bắc cầu vào khoá . Ví dụ : Xét lược đồ quan hệ: r(SAIP); Trong đó F = {SI → ; S → A} Ta thấy r là dạng chuẩn 1 Xét dạng chuẩn 2: Ta có S → A và SI → S ⇒ SI → A Vậy thuộc tính A không phụ thuộc đầy đủ vào khoá của r là SI, như vậy r không phải là 2 NF Ví dụ: Xét lược đồ quan hệ: r(SIDM); F= { SI → D; SD → M} - Ta thấy r là dạng chuẩn 2 - Xét dạng chuẩn 3: SI → D⇒ SI → SD; (theo luật tăng trưởng); Mà SD → M⇒ SI → M Vậy M phụ thuộc bắc cầu vào SI nên r không là 3 NF 3.6.6 Dạng chuẩn BCNF (Boye - Code) Lược đồ quan hệ r gọi là dạng chuẩn BCNF nếu X → A thoả trên r, nếu A không thuộc X và X là khoá của r. Định lý: Một lược đồ quan hệ r là dạng chuẩn BCNF thì là phải là dạng chuẩn 3NF 3.7 Các thuật toán 3.7.1 Bao đóng của tập thuộc tính Bao đóng của tập thuộc tính là một tập con của U. Bao đóng của tập thuộc tính X đối với tập phụ thuộc hàm F là X+: X+ = {A | A∈U; X → A∈F+} Bao đóng của tập thuộc tính X+ chính là các thuộc tính có thể suy ra từ X qua các phụ thuộc hàm F. Thuật toán 1: Tìm bao đóng của tập thuộc tính Bước 1: Đặt X0 = X Bước i: Tính Xi = Xi-1 ∪ {A} nếu tồn tại Y → Z∈F; A ∈Z và A∉Xi-1; Y ⊆ Xi-1 Vì X1 ⊆ X2 ⊆ ⊆ U nên ∃ j sao cho Xj = Xj-1 (tập X không tăng nữa) 40
12. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Đặt X+ = Xj; Gọi X+ là bao đóng của X Mô tả bằng ngôn ngữ giả Pascal: Proc Closure; Input: r=(U,F); Tập thuộc tính X ⊆ U Output: Y = X+ = {A∈U| X → A∈F+} Begin Y:=X Repeat Z:=Y; For each f L → R in F do if L ⊆ Y then Y:=Y∪ R; endif; endfor; Until Y=Z: return Y; End; Ví dụ: Cho r(U); U={A,B,C,D,E,F,G} F = {A→ BC; C → B; D → EF; AD → G} Tính (AD)+ Đặt X1 = (AD) Chọn các phụ thuộc hàm có vế trái là A,D,AD có A → BC nên X2 = AD∪ B X3 = ADB ∪ C Vì có D → EF nên X4 = ADBC ∪ E X5 = ADBCE ∪ F Vì có AD → G nên X6 = ABCDEFG VÀ X+ = (ABCDEFG) 3.7.2 Phủ của tập các phụ thuộc hàm 41
13. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin - Cho hai tập phụ thuộc hàm F và G ; F và G gọi là tương đương nếu F+ = G+. Khi đó ta nói F phủ G (hay G phủ F) - Phủ không dư thừa: Gọi F là tập phụ thuộc hàm không dư thừa nếu không tồn tại một phụ thuộc hàm X → Y mà F - {X → Y} tương đương với F Ví dụ: Cho F = { A → B ; B → C ; A → Cl Ta thấy A → C là thừa vì F - {A → C} tương đương với F - Phủ tối thiểu: Gọi tập các phụ thuộc hàm F là tối thiểu nếu thoả mãn ba điều kiện: + Mọi phụ thuộc hàm thuộc F đều có dạng : { Xi → A; | i = 1 m } (nói cách khác về phải mỗi phụ thuộc hàm thuộc F chỉ có một thuộc tính). + F là một phụ thuộc hàm không dư thừa hay không tồn tại X → Y ∈ F mà F - {X → Y} tương đương với F + F không dư thừa thuộc tính nào ở vế trái, nói cách khác không tồn tại một phụ thuộc hàm X → A ∈ F; Z ⊆ X mà : F+ = (F – {X → A} ∪ {Z → A})+ Định lý: Mối phụ thuộc hàm F đều tương đương với một phủ tối thiểu F’. Vì vậy với một quan hệ ta chỉ cần xét phủ tối thiểu là đủ. Thuật toán 2: Tìm phủ tối thiểu Bước 1: Tách các PTH sao cho vế phải của mỗi PTH chỉ có một thuộc tính. Giả sử xét phụ thuộc hàm X → Y , với Y = A1A2A3 An Ta có thể lách thành các phụ thuộc hàm sau: X → A1 X → A2 X → Am Kết quả ta được F1 tương đương với F Bước 2: Loại bỏ các phụ thuộc hàm (PTH) dư thừa. Giả sử có Fi có dạng Xi → A | i = 1 m 1 Đặt F0 = F Fi-1 \ {Xi → Ai} nếu Fi-1 \ {Xi → Ai} tương đương với Fi-1 Fi = { Fi-1 nếu ngược lại 42
14. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Sau m lần ta được Fm = Fm-1 2 1 Đặt F = Fm tương đương với F . Bước 3: Loại bỏ các thuộc tính dư thừa bên trái của mỗi phụ thuộc hàm Sau bước 2 có 2 F = {Xi → Aj | với i =l n Và Xi Có dạng Xi =A1,A2, An - Đặt X0 = Xi 2 Xj-1-{A} nếu {F2\ (Xi-1 → Ai) ∪ (Xi-1\Aj) → Ai} tương đương với F Xj ={ Xj-1 nếu ngược lại Lặp lại quy tắc trên n lần thì ta xét xong phụ thuộc hàm Xi → Aj (Có nghĩa là đã loại bỏ tất cả các thuộc tính dư thừa bên trái trong phụ thuộc hàm trên). Sau bước này ta được F3 tương đương với F2. F3 là phủ tối thiểu của F 3.7.3 Tính tương đương của tập phụ thuộc hàm Bài toán thành viên: Cho tập thuộc tính U, một tập các phụ thuộc hàm F. Cho một phụ thuộc hàm f: X → Y. Hỏi X → Y có thuộc F hay không. Kết quả bài toán thành viên: Định lý: X → Y ∈ F+ Khi và chỉ khi Y ⊆ X+ Thuật toán 3: Kiểm tra tính tương đương của hai tập phụ thuộc hàm Cho F = {Xi → Yi} | i= l m; G = {Xj → Yj} | j = 1 n Kiểm tra xem F có tương đương với G không + Bước l: Với ∀ i = 1 m kiểm tra xem Xi → Yi có thuộc G không. Theo định lý trên ta + + + kiểm tra xem có thoả Yi ⊆ Xi nếu thoả thì F ⊆ G + Bước 2: Với ∀ j = 1 n kiểm tra xem Xj → Yj có thuộc F không. Theo định lý trên ta + + + kiểm tra xem có thoả Yj ⊆ Xi nếu thoả thì G ⊆ F Nếu thoả cả hai điều kiện trên thì G+ = F+ và ta nói F và G tương đương 3.7.4 Khoá tối thiểu của sơ đồ quan hệ. Cho lược đồ quan hệ r(U,F), K ⊆ U. K được gọi là khoá tối thiểu của một lược đồ quan hệ nếu K → U và ∀ A∈K; (K -{A})! → U. Nói cách khác K thoả mãn (l) K+ = U (2) ∀ A∈K; (K - {A})+ ≠ U Chú ý: - K chỉ thoả mãn điều kiện (l) thì K gọi là siêu khoá, K thoả mãn cả hai điều kiện 43
15. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin thì gọi là khoá (một số tài liệu gọi siêu khoá là khoá, còn khoá thoả mãn cả hai điều kiện trên là khoá tối thiểu) - Một sơ đồ quan hệ có thể có nhiều khoá. Giao của các khoá gọi là: M= U ∪ (∀ L\R). (là những thuộc tính chỉ có mặt ở vế trái) - Nếu M+ = U thì r có một khoá duy nhất. Nếu không thì r có hơn một khoá. - Khoá chính Thuật toán 4: Tìm khoá tối thiểu của một lược đồ quan hệ Cho tập phụ thuộc hàm và quan hệ r (U, F). U={A1, A2, An}. Tìm khoá tối thiểu của r Bước l: Đặt K0 = U + Ki-1\ A nếu (Ki-1 \ Ai) = U Bước i: Tính Ki ={ Ki-1 nếu ngược lại Lặp lại Bước i n lần thì dừng và khoá tối thiểu của r(U,F) là Ki. Proc Key; Input: Tập thuộc tính U; Tập F Output: K ⊆ U thoả điều kiện (1) K+ = U (2) ∀ A ∈ K; (K – {A})+ ≠ U Begin K:=U; For each attribute A in U do if A ∈ (K – A)+ then K:=K-A; endif; endfor; return K: End; 3.7.5 Các bước chuẩn hoá một quan hệ đến 3NF Thuật toán 5: Quá trình chuẩn hoá một quan hệ thành dạng 3NF Cho lược đồ quan hệ r(U) với tập phụ thuộc hàm F 44
16. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Chuẩn hoá r thành dạng 3NF và phép tách p là không mất mát thông tin ρ = {r1(U1), r2(U2) rn(Un)} Sao cho ri(Ui) là dạng chuẩn 3NF Bước 1: Tìm khoá của r Bước 2: Sử dụng thuật toán 2 tìm phủ tối thiểu F’ Bước 3: Xác định các lược đồ con Mỗi phụ thuộc hàm thuộc F’ tương đương với một lược đồ con. Giả sử xét Y → Aj ta tương ứng có lược đồ rj(Uj) với Uj =VAj và xác định khoá cho rj(Uj) với j ⊆ (1, ,n) và Y ⊆ U Lưu ý: Nếu ∃ Xi → Ai1; Xi → Ai2; ; Xi → Ail Thì Ui = (XiA1A2 Ai) Và tương ứng ta có Ri(Ui) Xác định khoá Ki của ri theo thuật toán 4, với i ⊆ (1, ,n) và Xi ⊆ U Bước 4: Xét phép tách ρ = {Ri(Ui)} nếu K không có mặt trong p thêm K vào ρ Cuối cùng ta được ρ = {Ri(Ui’)} với thoả mỗi ri là 3NF. Ví dụ 1: Cho r(U,F) có: U = {A,B,C,D,E,F,G,H ) F = {A → BC; C → B; D → EF; ADC → G } Chuẩn hoá r thành dạng 3NF Bước 1: Tìm khoá tối thiểu: K0 = U = { A,B,C,D,E,F,G,H } dùng thuật toán 4 loại bỏ dần ta có K = ADH Bước 2: Tìm phủ tối thiểu 2.1 Tách các phụ thuộc hàm F = {A → C; C → B; A → B; D → E; D → F; ADC → G} 2.2 Loại bỏ các phụ thuộc hàm dư thừa: Có A → B thừa vì có A → C và C → B Ta có F = {C → B; A → C; D → E; D → F; ADC → G} 2.3 Bỏ các thuộc tính thừa ở vế trái Vì A → C nên phụ thuộc hàm ADC → G thừa thuộc tính C nên ta có: AD → G 45
17. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin ⇒ F = C → B; A → C; D → E; D → F; AD → G} Bước 3: Ta có các ri như sau: A → C ⇒ R1(U1) = (AC) khoá K1 = {A} C → B ⇒ R2(U2) = (CB) khoá K2 = {B} D → E; D → F;⇒ R3(U3) = (DEF) khoá K3 = {D} AD → G ⇒ R4(U4) = (ADG) khoá K4 = {AD} Bước 4: Kết quả ρ = {r0(ADH), r1(AC), r2(CB), r3(DEF), r4(ADG)} Thoả mãn Ri(Ui) là 3NF Thuật toán 6: Chuẩn hoá một lược đồ quan hệ về dạng Boye - code Cho lược đồ quan hệ r có tập thuộc tính U và tập phụ thung hàm F Chuẩn hoá r thành dạng chuẩn Boye - code Bước i: Nếu tồn tại quan hệ X → Y mà X không phải là khoá thì tách r thành hai lược đồ quan hệ: ri = X ∪ Y = ri(XY) Khoá là X ri+1 = (U \ Y), Xác định lại khoá và tập phụ thuộc hàm F cho ri+1. Quá trình tiếp tục cho tới khi tất cả các lược đồ đều ở BCNF Kết quả được phép tách: ρ = {r1(U1), r2(U2), rn(Un)} với mỗi ri là quan hệ ở dạng BCNF Ví dụ: Cho lược đồ quan hệ r(U,F) U = {C, T, H, N, S, G} F = {CS → G ;C → T; HT → N; HS → N; HN → C} Khoá của r là HS : Hãy tách r thành các quan hệ BCNF Bước 1 : Xét CS → G vì CS không là khoá của r nên tách: R1 = (CSG); r2 = (CTHNS) và F= {C → T; HT → N; HS → N; HN → C}, khoá của r2 là HS. Trong đó ri thoả mãn BCNF, r2 không thoả mãn BCNF. Bước 2: Xét r2 46
18. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Có C → T mà C không là khoá của r2 nên tách: R21 = (CT); r22 = (CHNS) và F= {HS → N; HN → C}, khoá của r22 là HS. Trong đó r21 thoả mãn là BCNF, r22 không thoả mãn BCNF. Bước 3 : Xét r22: Có HN → C mà CH không là khoá nên tách: r221 = (CHN) ; r222 = (CHS); r221 thoả mãn CBNF; r222 thoả mãn BCNF Thuật toán dừng vì ∀ ri thoả mãn BCNF. Cuối cùng ta có: ρ = {rl(CSG); r21(CT); r221(CHN); r222(CHS)} Trong đó mỗi ri là BCNF. 3.8 Phụ thuộc đa trị 3.8.1 Khái niệm Ta thấy dữ liệu có mối quan hệ với nhau đó là phụ thuộc hàm. Tuy vậy cũng có trường hợp quan hệ đó không có sự phụ thuộc hàm. ánh xạ trên các thuộc tính không phải là đơn trị mà có nhiều giá trị. Mối quan hệ đó gọi là phụ thuộc đa trị (Multivalued Defendency-MVD) Ví dụ: Quan hệ KHDH (kế hoạch dạy học) Giáo viên Môn Lớp A M2 Kl A M1 K2 A M2 K2 A M1 Kl Như vậy với một giáo viên ta chưa hẳn đã xác định được dạy lớp nào, môn gì cụ thể 3.8.2 Định nghĩa Cho R là một lược đồ quan hệ X, Y là 2 tập con của R. Z = R - XY. Quan hệ r(R) gọi là phụ thuộc đa trị nếu với bất kỳ 2 bộ t1, t2 ∈ r, với tl[X] = t2[X] tồn tại một bộ t3 ∈ r sao cho: t3[X] = tl[X]; t3[Y] = t1[Y]; t3[Z] = t2[Z]; Ký hiệu phụ thuộc đa trị 47
19. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin X →→ Y Ta nói X xác định đa trị Y; hay Y phụ thuộc đa trị vào X Ví dụ: Xét quan hệ KHDH trên là phụ thuộc đa trị Nhận xét: - Xét mô hình trên ta còn có: t4[Z] = tl[Z]; t2[Y] = t4[Y] - Nếu Y = φ thì X →→ φ đúng với mọi quan hệ - Nếu X = φ thì φ →→ Y đúng khi Y độc lập với các thuộc tính khác trong r 3.8.3 Hệ tiên đề: (l) Tiên đề bù: X →→ Y ⇒ U\Y\X (2) Tiên đề tưng trưởng: X →→ Y; V ∈W ⇒ WX →→VY (3) Tiên để bắc cầu: X →→ Y và Y →→ Z ⇒ X →→ Z\Y (4) Tiên đề về quan hệ phụ thuộc đơn trị và đa trị: X → Y thì X →→ Y (l) Nếu X →→ Y, Z ⊆ Y, W ∩ Y = φ , W → Z thì X → Z 3.8.4 Các luật suy diễn của phụ thuộc đa trị 1. Luật hợp: NẾU X →→ Y và X →→ Z thì X →→ YZ 2. Luật tựa bắc cầu Nếu X →→ Y và WY →→ Z thì WX →→ Z\WX 3. Luật tựa bắc cầu hỗn hợp Nếu X →→ Y và XY →→ Z thì X →→ Z\Y 4. Luật tách Nếu X →→ Y và X →→ Z thì X →→ Y\Z; X →→ Z\Y; X →→ Y ∩ Z Định lý: Cho r(U) có phép tách ρ = {r1(U1), r2(U2)} là phép tách hai không mất mát thông tin khi và chỉ khi: Ul ∩ U2 →→ Ul \U2 U1 ∩ U2 →→ U2 \ U1 3.8.5 Dạng chuẩn 4 NF - Một phụ thuộc hàm đa trị X →→ Y gọi là sơ cấp nếu với X, Y ≠ φ X ∪ Y ≠ U mà ∀ X’ < X ⇒ X →→ Y Quan hệ r gọi là dạng chuẩn 4 nếu mọi phụ thuộc đa trị sơ cấp đều được xác định bởi khoá chính 48
20. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Ví dụ: Quan hệ KHDH trên : Khoá chính: GML Tách: KHDH1 : (GM) có G →→ M KHDH2: (GL) có G →→ L BÀI TẬP VÀ CÂU HỎI 1. Định nghĩa phụ thuộc hàm và các khái niệm liên quan. 2. Định nghĩa lược đồ quan hệ và cho ví dụ minh họa 3. Phát biểu tiên đề Armstrong và các hệ quả. 4. Định nghĩa bao đóng của một tập thuộc tính. 5. Định nghĩa phủ của một tập phụ thuộc hàm. Phủ tối thiểu. 6. Định nghĩa phép tách một lược đồ quan hệ. 7. Nêu các dạng chuẩn 1NF, 2NF, 3NF, BCNF và cho ví dụ minh hoạ. 8. Cho lược đồ quan hệ r(U,F). Tập thuộc tính U = {ABDEGIH}. Tập phụ thuộc hàm: F = AB → E, AG → I, BE → I, E → G, GI → H}. Chứng minh: AB → GH. 9. Cho lược đồ quan hệ r(U,F). U={ABCDEGH); F ={AB→ C, C → D, CD → E, CE → GH, G → A }.Chứng minh : A → E; AB → G 10. Cho sơ đồ quan hệ r(U,F); U={ABCDEGH}; F={A → D, AB → DE, CE → G, CE → GH}. Hãy tính (AB)+ 11. Cho sơ đồ quan hệ r(U,F); U={ABCDEG); F={A → D, AB → E, BG → E, CD → G,E → C}.Hãy tính (AB)+ 12. Cho sơ đồ quan hệ r(U,F); U:{ABCDEH} ; F={BC → E, D → A, C → A, AE → D, BE → CH}. Tìm khoá tối thiểu cho r(U,F) 13. Cho sơ đồ quan hệ r(U,F), với U:{DBIOQS}; F={S → D, I → B, IS → Q, B → O}. Hãy chuẩn hoá r(U) về 3NF. 14. Cho sơ đồ quan hệ r(U,F); U=(ABCDEGH);F={ABC → D, AB → E, BC → DC,C → DE, CE~ → H, DC → G, CH → G, AD → H}. Hãy chuẩn hoá r(U) thành 3NF. 15. Cho lược đồ quan hệ r(U,F). U= {C#,I,D,B,K,E,L} ; F= {C# → IBKE, D → B, K → E}. Hãy chuẩn hoá r thành dạng 3NF. 16. Cho sơ đồ quan hệ r(U,F), U={ABCD}; F={D → B,C → A,B → ACD}. Hãy xác định dạng chuẩn cao nhất của r(U)? Giải thích. 17. Kiểm tra tính kết nối không mất mát thông tin của phép tách R(ABCDE) thành các lược đồ quan hệ sau: r1=AD; r2=AB; r3=BE; r4=CDE; r5 = AE. Với tập phụ thuộc 49
21. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin hàm : F= {A → C, B → C; A → B; DE → C; CE → A}. 18. Cho lược đồ quan hệ r(U,F); U= {ABCDEG} ; F = {AB → C; C → B; ABD → E; G → A} .Chuẩn hoá r thành dạng BCNF. 19. Kiểm tra tính không mất mát thông tin của phép tách r(ABCDEG) thành ρ (r) = (BC,AC,ADBE,ADBF). Với tập phụ thuộc hàm F={AB → C; C → B; ABD → E; G → A} 20. Cho sơ đồ quan hệ r(U,F) với U = {ABCDEG}; F= {BC → E; D → A; C → A; AE → D; BE → CG} a. Tìm một khoá K của r b. Sơ đồ còn khoá nào khác không? vì sao? c. Tập BCG có phải là khoá của r không? vì sao? d. Tập BD có phải là khoá của r không? vì sao? e. Tính K+ -(X ∪ Y) với X = CD; Y =G và K là một khoá của r f. Hãy cho thêm một PTH để r có đúng một khoá. Giải thích cách làm. 50
22. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin CHƯƠNG 4 NGÔN NGỮ ĐỊNH NGHĨA VÀ THAO TÁC DỮ LIỆU 4.1 Giới thiệu về mô hình xử lý dữ liệu Môn sơ sở dữ liệu tập trung nghiên cứu ngôn ngữ con dữ liệu bao gồm: - Ngôn ngữ định nghĩa dữ liệu (Data Defirútion Languages - DDL) dùng để xây dựng cơ sở dữ liệu - Ngôn ngữ thao tác dữ liệu (Data Manuapulation Languages - DML) dùng để xử lý trả lời các câu hỏi về dữ liệu - Hiện nay có nhiều ngôn ngữ hệ quản trị cơ sở dữ liệu cho phép người dùng thực hiện các nhiệm vụ trên như Foxpro, Access, SQL Các ngôn ngữ này đều dựa trên các cơ sở toán học của đại số quan hệ 4.2 Ngôn ngữ đại số quan hệ 4.2.1 Khái niệm Là ngôn ngữ dựa trên các phép toán của đại số quan hệ mà ta đã xét. Mỗi câu hỏi được biểu diễn bằng một tập các phép toán nào đó 4.2.2 Các câu lệnh của ngôn ngừ đại số quan hệ (1) Phép hợp 51
23. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin UNION (2) Phép giao INTERSECT (3) Phép trừ MINUS (4) Phép tích Đề các TIMES (5) Phép chọn SELECT WHERE (6) Phép chiếu PROJECT OVER (7) Phép kết nối JOIN AND [OVER ] [WHERE ] (8) Phép chia DIVIDE BY OVER [AND ] (9) Đưa ra kết quả GIVING 4.2.3 Ví dụ minh hoạ - Bổ xung vào quan hệ CONGTY một công ty nữa Congty UNION {“CT4”, “Hồng Hà” , 1200000, “Nam định”} GIVING Congty - Xóa tên công ty CT5 Congty MINUS {“CT5”, , , } GIVING CongTy - Sửa địa chỉ của công ty Hồng Hà thành Hà nội, thực chất là xoá bộ cũ thay bộ mới với nội dung mới Congty MINUS (“CT4”, , , ) GIVING Tgian Tgian UNION (“CT4”, , “Hà nội”) 52
24. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin GIVING CongTy Chú ý: Lệnh này cần đề phòng mất dữ liệu - Tìm kiếm thông tin về công ty CT1 SELECT CongTy WHERE MaCongTy = “CT1” GIVING CongTy 4.2.4 Biểu diễn một số câu hỏi (2) Đưa ra danh sách các mặt hàng màu đỏ SELECr Hang hoa WHERE Mau = “Đỏ” GIVING Ketqua (3) Cho biết mã các công ty cung cáp mặt hàng Hl SELECT CungCap WHERE MaHang = “Hl” GIVING Tgian PROJECT Tgian OVER MaCongTy GIVING Ketqua Hoặc: PROJECT (SELECI CungCap WHERE MaHang= “Hl”) OVER MaCongTy GIVING Ketqua (4) Cho biết tên công ty cung cấp mặt hàng Hl SELECT Cungcap WHERE MaHang = “Hl” GIVING Tgianl JOIN Tgianl AND Congty OVER MaCongTy GIVING Tgian2 PROJECT gian2 OVER TenCongTy GIVING Ketqua (5) Cho biết tên công ty cung cấp cả hai mặt hàng Hl và H2 SELECT CungCap WHERE MaHang= “Hl” GIVING Tgianl PROJECT Tgianl OVER MaCongTy GIVING Tgian2 SELECT Cungcap WHERE MaHang= “H2” GIVING Tgianl’ PROJECT Tgianl’ OVER MaCongTy GIVING Tgian2’ Tgian2 INTERSECT Tgian2’ GIVING Tgian JOIN Tgian AND Congty OVER MaCongTy GIVING Tgian’ PROJECT Tgian’ OVER TenCongTy GIVING Ketqua 4.3 Ngôn ngữ SQL (Structure Quay Language) 4.3.1 Giới thiệu Ngôn ngữ SQL là ngôn ngữ con dữ liệu quan hệ được xác nhận là rất mạnh, phổ đụng và rất dễ sử dụng. SQL được phát triển từ ngôn ngữ SEQUEL-2, thử nghiệm và cài đặt tại tại Trung 53
25. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin tâm nghiên cứu của hãng IBM ở San Joes, Califomia cho hệ thống QTCSDL lớn điển hình là System - R, SQL vừa đóng vai trò là một ngôn ngữ có thể thao tác độc lập của người sử dụng đầu cuối, đồng thời lại có khả năng là một ngôn ngữ con được nhúng trong ngôn ngữ chủ SQL là ngôn ngữ phi thủ tục, chuẩn mực và điển hình. Do vậy hiện nay rất nhiều sản phẩm thương mại đều được cài đặt SQL như ACCESS, ORACLE, DB2 SQL cho phép tạo lập ra cơ sở dữ liệu. Các thao tác xử lý trong các quan hệ được thực hiện một cách dễ dàng Các phép toán cơ bản của SQL là phép ánh xạ được miêu tả bằng khối: SELECT FROM WHERE Với các dạng câu hỏi cụ thể có những cú pháp nhất định nhưng cơ bản vẫn là khối xử lý trên 4.3.2 Nhóm lệnh tạo lập cơ sở dữ liệu a) Lệnh tạo bảng CREATE TABLE ( [ ] [, n], [ ]) Trong đó: + tên_bảng, tên_cột: do người sử dụng tự định nghĩa tuân theo quy tắc đặt tên • Tên cột bắt đầu bằng chữ hoa, sao cho ngắn gọn chính xác và đầy đủ. • Khung nón đặt tên bảng và tên cột có khoảng trắng. • Không nên đặt tên bảng và tên cột trùng với các từ khóa + Kiểu dữ liệu: Chọn kiểu dữ liệu nào phù hợp với dữ liệu người dùng sẽ nhập vào Các kiểu dữ liệu gồm: Smallinteger: là số nguyên có giá trị trong phạm vi: -32768 đến 32767 Integer: Số nguyên phạm vi -2147483648 đến 2147683647 Decimal (n,p): Số thập phân có n chữ số có p số sau dấu phảy Float: Số thập phân dấu phảy động Char(n): Xâu có độ dài cố định n ≤ 255 Varchar(n): Xâu có độ dài thay đổi 0 - n Longvarchar(n): xâu có độ dài thay đổi có thể tới 32 KB 54
26. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Da te: Kiểu ngày tháng cách viết ‘24/0412003’ + Ràng_buộc_cột: Là một số quy định để kiểm tra dữ liệu khi dữ liệu mới được đưa vào cột hoặc dữ liệu cũ bị thay đổi phải tuân theo các ràng buộc này. • Null: Mỗi khi nhập dữ liệu giá trị tại cột này có thề nhận giá trị rỗng (ràng buộc mặc định). • Not null: Bắt buộc phải nhập giá trị cho cột này mỗi khi nhập dữ liệu vào bảng. • Unique: giá trị nhập vào cột phải duy nhất. • Ràng buộc khóa chính: Primary key. + Ràng_buộc_bảng: có 2 loại • Ràng buộc khóa chính: Giá trị dùng để xác định duy nhất một đối tượng nên giá trị của chúng phải duy nhất, không thể nhận giá trị Null, ràng buộc này được định nghĩa theo cú pháp sau: CONSTRAINT PRIMARY KEY (danh_sách_thuộc_tính_khoá) • Ràng buộc khóa ngoài: Dùng để kiểm tra sự tương quan về dữ liệu giữa khoá chính và khoá ngoài cửa hai bảng có mối quan hệ với nhau: CONSTRAINT FOREIGN KEY ( ) REFERENCES ( ) Ví dụ: Tạo cấu trúc các bảng dữ liệu trong cơ sở dữ liệu quản lý cung cấp hàng. + Tạo cấu trúc bảng CongTy CREATE TABLE CONGTY (MaCongTy Char(6) Primary Key, TenCongTy Varchar (50) Not Null, NganSach Integer Not Null, DiaChi Varchar (50) ) + Tạo cấu trúc bảng HANGHOA CREATE TABLE HANGHOA (MaHang Char(6) Primary Key, TenHang Varchar (50) Not Null, Mau Varchar(20) Not Null, DonViTinh Varchar (l0) ) 55
27. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin + Tạo cấu trúc bảng CUNGCAP CREATE TABLE CUNGCAP (MaCongTy Char(6), MaHang Char(6), Soluong Integer Not Null, Don Gia Integer Not Null, Constraint MaCT_MaHang_PK Primary Key (MaCongTy, MaHang), Constraint MaCongTy_FK Foreign Key (MaCongTy) References CONGTY(MaCongTy), Constraint MaHang_FK Foreign Key (MaHang) References HANGHOA(MaHang)) b) Thêm một cột ALTER TABLE ADD [ ] Ví dụ: Thêm vào bảng CONGTY cột số điện thoại của công ty ALTER TABLE CONGTY ADD SoDT char(10) c) Xoá cột ALTER TABLE DROP COLUMN Ví dụ: Câu lệnh sau sẽ xoá cột số điện thoại trong bảng Công ty ALTER TABLE CONGTY DROP COLUMN SoDT d) Xoá ràng buộc ALTER TABLE DROP CONSTRAINT Ví dụ: Câu lệnh sau sẽ xoá một ràng buộc khoá ngoài trong-bảng CUNGCAP ALTER TABLE CUNGCAP DROP CONSTRAINT MaCongTy_FK, MaHang_FK 4.3.3 Nhóm lệnh thao tác dữ liệu l) Lệnh truy vấn dữ liệu - SELECT Việc truy cập và lấy các thông tin từ database được SQL cho phép thực hiện qua câu lệnh SELECT. Câu lệnh SELECT có phạm vi ứng dụng rất rộng, có thể truy cập dữ liệu từ một table, hay từ nhiều table 56
28. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Các từ khóa SELECT, FROM, WHERE được sử dụng để tạo nên một câu lệnh SELECT đơn giản nhất Cú pháp tổng quát có dạng sau: SELECT [ ALL | DISTINCT] FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY [ ASC | DESC ] [, n]] a) Mệnh đề FROM Mệnh đề FROM trong câu SELECT được sử dụng nhằm chỉ định các bảng cần truy xuất dữ liệu. Sau FROM là danh sách tên của các bảng và khung nhìn tham gia vào truy vấn. Tên của các bảng và các khung nhìn được phân cách nhau bởi dấu phẩy. Ví dụ: Câu lệnh dưới đây hiển thị danh sách các công ty. SELECT MaCongTy, TenCongTy, Diachi FROM CongTy Chú v: Ta có thế sử dụng các bí danh cho các bảng hay khung nhìn trong câu lệnh SELECT. Bí danh được gán trong mệnh đề FROM bằng cách chỉ định bí danh ngay sau tên bảng. Ví dụ: Câu lệnh sau gán bí danh là CT cho bảng CongTy SELECT * FROM CongTy CT b) Mênh đề SELECT Danh sách chọn trong câu lệnh SELECT được sử dụng để chỉ định các trường các biểu thức cần hiển thị trong các cột của kết quả truy vấn. Các trường các biểu thức được chỉ định ngay sau tù khoá SELECT và phân cách nhau bởi dấu phẩy. Sử dụng danh sách chọn trong câu lệnh SELECT bao gồm các trường hợp sau: b.1 Chọn tất cả các cột trong bảng Khi cần hiền thị tất cả các trường trong bảng, sử dụng ký tự * trong danh sách chọn thay vì phải liệt kê danh sách tất cả các cột. Trong trường hợp này, các cột được hiên thị trong kết quả truy vấn sẽ tuân thủ theo thứ tự mà chúng đã được tạo ra khi bảng được định nghĩa. Ví dụ: Câu lệnh sau sẽ liệt kê danh sách các mặt hàng SELECT * FROM HangHoa 57
29. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin b.2 Liệt kê tên cột trong danh sách chọn Trong trường hợp cần chỉ định cự thể các cột cần hiển thị trong kết quả truy vấn, ta chỉ định danh sách các tên cột trong danh sách chọn. Thứ tự của các cột trong kết quả truy vấn tuân theo thứ tự của các trường trong danh sách chọn. Ví dụ: Liệt kê danh sách các mặt hàng gồm các thuộc tính sau: MaHang, TenHang, DonViTinh SELECT MaHang, TenHang, DonViTinh FROM HangHoa Chú v: Nếu truy vân được thực hiện trên nhiều bảng và các bảng có các trường trùng tên thì tên của những trường này nếu xuất hiện trong danh sách chọn phải được viết dưới dạng: . Ví dụ: Liệt kê danh sách các công ty đã cung cấp hàng hoá SELECT CC.MaCT, TenCongTy, Diachi FROM CongTy CT, Cungcap CC WHERE CT.MaCT CC.MaCT b.3 Thay đổi tiêu đe các cột Trong kết quả truy vấn, tiêu đề của các cột mặc định sẽ là tên của các trường tương ứng trong bảng. Tuy nhiên, để tiêu đề trở thành thân thiện hơn, ta có thể đổi lại tên tiêu đề của các cột. Để đặt tiêu đề cho một cột nào đó, ta sử dụng cách viết: AS Ví dụ: Cho biết mã và tên của các công ty. SELECT MaCongTy, TenCongTy AS Tên_công_Ty FROM CongTy b.4 Hằng và biểu thức trong danh sách chọn Ngoài danh sách trường, trong danh sách chọn của câu lệnh SELECT còn có thể sử dụng các biểu thức. Mỗi biểu thức trong danh sách chọn trở thành một cột trong kết quả truy vấn. Ví dụ: Câu lệnh dưới đây cho biết mã công ty, mã hàng, số lượng, đơn giá và thành tiền của các mặt hàng đã được các công ty cung cấp. SELECT MaCongTy, MaHang, Soluong, Don Gia, SoLuong*DonGia AS ThanhTien FROM Cungcap b.5 Loại bỏ các bản ghi trùng nhau trong kết quả truy vấn 58
30. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Trong kết quả của truy vấn có thề xuất hiện các dòng dữ liệu trùng nhau. Để loại bớt các dòng này, ta chỉ định đêm từ khoá DISTINCT ngay sau từ khoá SELECT. Ví dụ: Cho biết thông tin về mã của từng loại mặt hàng đã được cung cấp. SELECT DISTINCT MaHang FROM Cungcap b.6 Giới hạn số lượng bản ghi trong kết quả truy vấn Kết quả của truy vấn được hiền thị thường sẽ là tất cả các dòng dữ liệu truy vấn được. Trong trường hợp cần hạn chế số lượng các dòng c) Mệnh đề điều kiện WHERE Mệnh đề WHERE trong câu lệnh SELECT được sử dụng nhằm xác định các điều kiện đối với việc truy xuất dữ liệu. Sau mệnh đề WHERE là một biểu thức logíc và chỉ những dòng dữ liệu nào thoả mãn điều kiện được chỉ định mới được hiển thị trong kết quả truy vấn. Ví dụ: Câu lệnh dưới đây hiển thị mã số của các nhà cung cấp đã cung cấp mặt hàng có mã là H2 SELECT MaCongTy FROM Cungcap WHERE Ma Hang 'H2' Trong mệnh đề WHERE thường sử dụng ¾ Các toán tử kết hợp điều kiện (AND, OR) ¾ Các toán tử so sánh. ¾ Toán tử phạm vi và toán tử tập hợp ¾ Các giá trị NULL c.1 Các toán tử so sảnh Toán tử Ý nghĩa = (Eaualsl) Ngang bằng >(Greater Than) Lớn hơn = (Greater Than or Equal To) Lớn hơn hoặc bằng (Not Equal To) Không bằng != (Not Equal To) Không bằng 59
31. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin ! (Not Greater Than) Không lớn hơn c.2 Toán tử phạm vi (Range Operator):[NOT] BETWEEN a AND b Để kiểm tra xem giá trị dữ liệu nằm trong (ngoài) một khoảng nào đó, ta sử dụng toán tử [NOT] BETWEEN như sau: Cách sử dụng Ý nghĩa giá_trị BETWEEN a AND b a ≤ giá_trị ≤ b giá_tri NOT BETWEEN a AND b (giá_trị b Ví dụ: Cho cơ sở dữ liệu quản lý điểm gồm các bảng sau: + LopHoc(MaLop, Tenlop) + SinhVien(MaSV, HoDem, Ten, Ngay Sinh, Gioi Tinh, Diachi, Malop) + MonHoc(MaMon, Ten Mon, SoDvht) + Diem(MaSV, Ma Mon, DiemThi) Câu lệnh dưới đây cho biết danh sách các môn học có số đơn vị học trình nằm trong khoảng từ 4 đến 5 SELECT * FROM MonHoc WHERE SoDvht Between 4 And 5 Câu lệnh dưới đây cho biết danh sách các môn học có số đơn vị học trình không nằm trong khoảng từ 4 đến 5 FROM MonHoc WHERE SoDvht NOT Between 4 And 5 c.3 Toán tử tập hợp (IN và NOT IN) Toán tử IN được sử dụng khi ta cần chì định điều kiện tìm kiếm dữ liệu cho câu SELECT là một danh sách các giá trị. Sau IN (hoặc NOT IN) có thể là một danh sách các giá trị hoặc là một câu lệnh SELECT khác. Ví dụ: Để biết danh sách các môn học có số đơn vị học trình là 3, 4 hoặc 5 thay vì sử dụng câu lệnh SELECT * FROM MonHoc WHERE SoDvht =3 OR SoDvht = 4 OR SoDvht = 5 60
32. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Ta có thề sử dụng câu lệnh SELECT * FROM MonHoc ~ÍERE SoDvht IN (3,4,5) c.4 Toán tử LIKE và các ký tự đại diện Toán tử LIKE (hoặc NOT LIKE ) sử dụng trong câu lệnh SELECT nhằm mô tả khuôn dạng của cần tìm kiếm. Chùng thường kết hợp với các ký tự đại diện sau đây: ¾ Dấu phần trăm (%): Chỉ mốt chuỗi các ký tự bất kỳ. ¾ Dấu gạch dưới (_): Chỉ một ký tự đơn bất kỳ Ví dụ: Cho biết họ và tên của các sinh viên có họ đệm là Dương SELECT HoDem, Ten FROM Sinhvien WHERE HoDem = ‘Dương%’ c.5 Giá tri NULL + Trong mệnh đề WHERE, để kiềm tra giá trị của một cột có giá trị NULL hay không ta sử dụng cách viết: WHERE tên cột IS NULL hoặc WHERE tên_cột IS NOT NULL c.6 Toán tử SOME, ANY, ALL + Toán tử SOME, ANY: Chúng được sử dụng kết hợp thêm với các phép toán so sánh căn bản như: =, >, >=, , ! , >=, , !< Trả về TRUE nếu tất cả các biểu thức đều thỏa mãn d) Sắp xếp kết quả truy vấn Mặc định các dòng dữ liệu trong kết quả của câu nguy vấn tuân theo thứ tự của chứng trong bảng dữ liệu hoặc được sắp xếp theo chỉ mục (nếu trên bảng có chỉ mục). Trong trường hợp muốn dữ liệu được sắp xếp theo chiều tăng hoặc giảm của giá trị của một hoặc nhiều trường, ta sử dựng thêm mệnh đề ORDER BY trong câu lệnh SELECT. Sau ORDER BY là danh sách các cột cần sắp xếp (tôi đa là 16 cột). Dữ liệu được sắp xếp có thể theo chiều tăng (ASC) hoặc giảm (DESC), mặc định là sắp xếp theo chiều tăng. Ví dụ: Câu lệnh dưới đây hiển thị danh sách các môn học và sắp xếp theo chiều giảm dần của số đơn vị học trình SELECT * FROM MonHoc ORDER BY SoDvht DESC 61
33. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Chú v: Nếu sau ORDER BY có nhiều cột thì việc sắp xếp dữ liệu sẽ được ưu tiên theo chiều từ trái qua phải. Ví dụ: Liệt kê danh sách sinh viên và sắp xếp theo tên sinh viên theo Alphaber, nếu trùng tên thì sắp theo giới tính SELECT * FROM Sinhvien ORDER BY Ten, GioiTinh e) Phép kết nối Khi cần thực hiện một yêu cầu truy vấn dữ liệu từ hai hay nhiều bảng, ta phải sử dụng đến phép kết nối Để thực hiện được một phép nối, cần phải xác định được những yếu tố sau: • Những cột nào cần hiên thị trong kết quả truy vấn. • Những bảng nào có tham gia vào truy vấn. • Điều kiện để thực hiện phép nối giữa các bảng dữ liệu là gì? Trong các yếu tố kể trên, việc xác định chính xác điều kiện để thực hiện phép nối giữa các bảng đóng vai trò quan trọng nhất. Trong đa số các trường hợp, điều kiện của phép nối được xác định nhờ vào mối quan hệ giữa các bảng cần phải truy xuất dữ liệu. Thông thường, đó là điều kiện bằng nhau giữa khoá chính và khoá ngoài của hai bảng có quan hệ với nhau. Ví dụ: Câu lệnh dưới đây hiền thị danh sách các sinh viên với các thông tin: Mã sinh viên, họ đệm và tên, mã lớp và tên lớp SELECT MaSV, HoDem, Ten, Lop.Malop, Tenlop FROM Sinhvien , Lop WHERE Sinhvien.Malop = Lop.Malop Trong câu lệnh trên, các bảng tham gia vào truy vấn bao gồm: Sinhvien và Lop. Điều kiện để thực hiện phép kết nối giữa hai bảng là điều kiện sau: Sinhvien.Malop = Lop.Malop Chú v:. • Tên của một số cột nào đó trong các bảng có tham gia vào truy vấn. Nếu tên cột trong các bảng trùng tên nhau thì tên cột phải được viết dưới dạng: Tên bảng.tên cơ • Dấu sao (*) được sử dụng trong danh sách chọn khi cần hiển thị tất cả các cột của các bảng tham gia truy vấn. • Trong trường hợp cần hiển thị tất cả các cột của một bảng nào đó, ta sử dụng cách viết: tên_bảng.* 62
34. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin Ví dụ: Liệt kê danh sách các sinh viên tham gia học môn ‘Cơ sở dữ liệu’ SELECT Sinhvien.MaSV, HoDem, Ten, Malop, Ngay Sinh, GioiTinh, DiaChi FROM Sinhvien , MonHoc , Diem WHERE Sinhvien.MaMon = Diem.MaMon AND MonHoc.MaMon=Diem.MaMon AND TenMon= ‘cơ sở dữ liệu’ hoặc viết dưới dạng ngắn gọn hơn: SELECT Sinhvien.* FROM Sinhvien , MonHoc , Diem WHERE SinhVien.MaMon = Diem.MaMon AND MonHoc.MaMon=Diem.MaMon AND Tenmon= ‘cơ sở dữ liệu’ f) Thống kê dữ liệu với GROUP BY Mệnh đề GROUP BY sử dụng trong câu lệnh SELECT nhằm phân hoạch các dòng dữ liệu trong bảng thành các nhóm dữ liệu và trên mỗi nhóm dữ liệu thực hiện tính toán các giá trị thống kê như tính tồng, tính giá trị trung bình Các hàm nhóm được sử dụng để tính giá trị thống kê cho toàn bảng hoặc trên mỗi nhóm dữ liệu. Chúng có thể được sử dụng như là các cột trong danh sách chọn của câu lệnh SELECT hoặc xuất hiện trong mệnh đề HAVING, nhưng không được phép xuất hiện trong mệnh đề WHERE SQL cung cấp các hàm nhóm dưới đây: Hàm nhóm Chức năng Sum(biểu_thức) Tính tổng các giá trị Avg(biểu_thức) Tính trung bình của các giá trị Count(biểu_thức) Đếm số các giá trị trong biểu thức COUNT(*) Đếm số các dòng được chọn Max(biểu_thức) Tính giá trị lớn nhất Min(biều_thức) Tính giá trị nhỏ nhất Trong đó: Hàm SUY AVG chỉ làm việc với các biểu thức số Hàm SUM, AVG, COUNT, MIN và MAX bỏ qua các giá trị NULL khi tính toán Hàm COUNT(*) không bò qua các giá trị NULL f.1 Thống kê trên toàn bộ dữ liệu Khi cần tính toán giá trị thống kê trên toàn bộ dữ liệu, ta sử dụng các hàm nhóm trong 63
35. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin danh sách chọn của câu lệnh SELECT. Trong trường hợp này, trong danh sách chọn không được sử dụng bất kỳ một tên cột hay biểu thức nào ngoài các hàm gộp. Ví dụ: Để thống kê trung bình điểm thi của tất cả các môn học ta sử dụng câu lệnh như sau: SELECT AVG(DiemThi) AS Tinh_Trung_Binh_Diem FROM Diem f.2 Thống kê dữ liệu trên các nhóm Trong trường hợp cần thực hiện tính toán các giá trị thống kê trên các nhóm dữ liệu, ta sử dụng mệnh đề GROUP BY để phân hoạch dữ liệu vào trong các nhóm. Các hàm nhóm được sử dụng sẽ thực hiện thao tác tính toán trên mỗi nhóm và cho biết giá trị thống kê theo các nhóm dữ liệu. Ví dụ: Câu lệnh dưới đây cho biết sĩ số sinh viên của mỗi lớp SELECT Lop.Malop, Tenlop, COUNT(MaSV) AS SiSo FROM LOP, Sinhvien WHERE Lop.MaLop = SinhVien. Malop GROUP BY Lop.MaLop, TenLop Chú v: • Biểu thức nào điều khiển việc phân nhóm dữ liệu thì các biểu thức đó phải được liệt kê sau mệnh đề GROUP BY. • Trong trường hợp danh sách chọn của câu lệnh SELECT có các hàm nhóm và những biểu thức không phải là đối số của các hàm nhóm thì những biểu thức này phải được liệt kê đầy đủ trong mệnh đề GROUP BY, nếu không câu lệnh sẽ không hợp lệ Ví dụ: Dưới đây là một câu lệnh sai do thiếu trường Tenlop sau mệnh đề GROUP BY SELECT Lop.MaLop, TenLop, COUNT(MaSV) AS SiSo FROM Lop, SinVien WHERE Lop.MaLop = SinhVien. MaLop GROUP BY Lop.MaLop g) Mệnh đề điều kiện đối với hàm nhóm - HAVING Mệnh đề HAVING là mệnh đề đặt điều kiện lên các nhóm dữ liệu. Mệnh đề HAVING thường không thực sự có nghĩa nếu như không sử dụng kết hợp với mệnh đề GROUP BY. Một điểm khác biệt giữa HAVING và WHERE là trong điều kiện của WHERE không được có các nam nhóm trong khi HAVING lại cho phép sử dụng các hàm nhóm trong điều kiện của mình. Ví dụ: Đưa ra danh sách sinh viên có trung bình điểm thi lớn hơn hoặc bằng 5. 64
36. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin SELECT SinhVien.MaSV, HoDem, Ten, SUM(DiemThi*SoDvht)/SUM(SoDvht) FROM Sinhvien , MonHoc , Diem WHERE SinhVien.MaMon = Diem.MaMon AND MonHoc.MaMon=Diem.MaMon GROUP BY SinhVien.MaSV, HoDem, Ten HAVING SUM(DiemThi*SoDvht)/SUM(SoDvht) >=5 h) Truy vấn con (Subquery) Truy vấn con là một câu lệnh SELECT được lồng vào bên trong một câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc bên trong một truy vấn con khác Loại truy vấn này được sử dụng để biểu diễn cho những truy vấn trong đó điều kiện truy vấn dữ liệu cần phải sử dụng đến kết quả của một truy vấn khác. + Tìm tên các công ty đã cung cấp mặt hàng H2 SELECT TenCongTy FROM CongTy, CungCap WHERE (CongTy.MaCongTy = CungCap.MaCongTy) AND (MaHang = ‘H2’) Hoặc có thể viết như sau: SELECT TenCongTy FROM CongTy WHERE MaCongTy IN (SELECT MaCongTy FROM CungCap WHERE MaHang = ‘H2’) Ví du: Câu lệnh sau liệt kê tên các công ty chưa từng cung cấp mặt hàng nào SELECT TenCongTy FROM CongTy WHERE MaCongTy NOT IN (SELECT MaCongTy FROM Cungcap) Hoặc có thể viết như sau: SELECT * FROM CongTy WHERE MaCongTy <> ALL (SELECT MaCongTy FROM CungCap) + Tìm những công ty cung cấp ít nhất một mặt hàng màu đỏ 65
37. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin SELECT TenCongTy FROM CongTy WHERE MaCongTy IN (SELECT MaCongTy FROM Cungcap WHERE MaHang IN (SELECT MaHang FROM HangHoa WHERE màu = “đỏ”)) + Câu lệnh sau liệt kê tên các công ty chưa từng cấp mặt hàng có mã là H1. SELECT TenCongTy FROM CongTy WHERE EXISTS (SELECT MaCongTy FROM Cungcap CC WHERE CC.MaCongTy=CongTy.MaCongTy AND MaHang [( )] VALUES ( ) Trong câu lệnh INSERT, danh_sách_tên_cột ngay sau tên bảng không cần thiết phải chỉ định nếu giá trị các trường cửa bản ghi mới được chỉ định đầy đủ trong danh sách các giá trị. Trong trường hợp này, thứ tự các giá trị trong danh sách trị phải bằng số lượng các trường của bảng cần bổ sung dữ liệu cũng như phải tuân theo đúng thứ tự của các trường như khi bảng được định nghĩa. Ví dụ: Câu lệnh dưới đây sẽ bồ sung thêm một công ty vào quan hệ cong ty INSERT INTO CongTy VALUES (C12, ‘Hoà bình’, 600000000, ‘Thái nguyên’, ’0280754379’) Trong trường hợp chỉ nhập giá trị cho một số cột trong bảng, ta phải chỉ định 66
38. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin danh sách các cột cần nhập dữ liệu ngay sau tên bảng. Khi đó các cột không được nhập dữ liệu sẽ nhận giá trị mặc định (nếu có) hoặc nhận giá trị NULL (nếu cột cho phép nhận giá trị NULL). Nếu một cột không có giá trị mặc định và không chấp nhận giá trị NULL mà không được nhập dữ liệu, câu lệnh sẽ bị lỗi. Ví dụ: Câu lệnh sau sẽ bổ sung một bản ghi mới cho bảng CongTy INSERT INTO CongTy ( MaCongTy, TenCongTy, Diachi, NganSach) VALUES (C13, ‘Trần Anh’, ‘Hà nội’, 500000000). Khi đó trường SODT sẽ nhận giá trị NULL. Câu lệnh trên còn có thể viết như sau: INSERT INTO CongTy VALUES (CI3, ‘Trần Anh’, 500000000, ‘Hà nội’, NULL). b) Bổ sung nhiều dòng dữ liệu từ bảng khác Một cách sử dụng khác của câu lệnh INSERT được sử dụng để bổ sung nhiều dòng dữ liệu vào một bảng, các dòng dữ liệu này được lấy từ một bảng khác thông qua câu lệnh SELECT. Ờ cách này, các giá trị dữ liệu được bổ sung vào bảng không được chì định tường minh mà thay vào đó là một câu lệnh SELECT truy vấn dữ liệu từ bảng khác. Cú pháp của câu lệnh có dạng như sau: INSERT INTO [( )] Ví dụ: Giả sử ta có bảng LuuCongTy bao gồm các trường MaCongTy, TenCongTy, NganSach, DiaChi. Câu lệnh dưới đây sẽ bô sung vào bảng LuuCongTy các dòng dữ liệu có được từ câu truy vấn SELECT. INSERT INTO LuuCongTy SELECT MaCongTy, TenCongTy, NganSach, DiaChi FROM CongTy WHERE Diachi = ‘Hà nội’ Ghi chú: Kết quả của câu lệnh SELECT phải có số cột bằng với số cột được chỉ định trong bảng đích và phải tương thích về cả kiểu dữ liệu. 3) Cập nhật dữ liệu Câu lệnh UPDATE trong SQL được sử dụng để cập nhật dữ liệu trong các bảng. Câu lệnh này có cú pháp như sau: UPDATE SET = [, , = ] 67
39. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin [FROM ] [WHERE ] Sau UPDATE là tên của bảng cần cập nhật dữ liệu. Một câu lệnh UPDATE có thể cập nhật dữ liệu cho nhiều cột bằng cách chỉ định danh sách tên cột và biểu thức tương ứng sau từ khoá SET. Mệnh đề WHERE trong câu lệnh UPDATE thường tược sử dụng đề chỉ định các dòng dữ liệu chịu tác động của câu lệnh (nêu không chỉ định, phạm vi tác động của câu lệnh là toàn bộ các dòng trong bảng) Ví dụ: Cập nhật lại địa chỉ của công ty có mã là CT1 UPDATE TABLE CongTy SET DiaChi = ‘Thái nguyên’ WHERE MaCongTy = ‘CTl’ 4) Xoá dữ liệu Để xoá dữ liệu trong một bảng, ta sử dụng câu lệnh DELETE. Cú pháp của câu lệnh như sau: DELETE FROM [FROM ] [WHERE ] Trong đó, tên của bàng cần xoá dữ liệu được chỉ định sau DELETE FROM. Mệnh đề WHERE trong câu lệnh được sử dụng để chỉ định điều kiện đối với các dòng dữ liệu cần xoá. Nếu câu lệnh DELETE không có mệnh đề WHERE thì toàn bộ các dòng trong bảng đều bị xoá. Mệnh đề FROM chỉ định danh sách các bảng có dữ liệu liên quan đến việc xoá dữ liệu. + Ví dụ: Câu lệnh sau sẽ xoá khỏi bảng CONGTY những công ty có địa chỉ ở Hà nội DELETE FROM CongTy WHERE DiaChi = ‘Hà nội’ Ví dụ: Xoá khỏi bảng CongTy danh sách các công ty đã cung cấp mặt có mã là H1 DELETE FROM CongTy FROM Cungcap WHERE Cungcap.MaCongTy = CongTy.MaCongTy AND MaHang= ‘H1’ + Xoá khỏi bảng CongTy nhưng công ty chưa từng cung cấp mặt hàng nào. DELETE FROM CongTy WHERE MaCongTy NOT IN (SELECT DISTINCT MaCongTy FROM CungCap) + Xoá tất cả các thông tin trong bảng Cungcap : DELETE FROM CungCap 68
40. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin BÀI TẬP CÂU HỎI 1. Cho CSDL gồm 2 quan hệ CC(MSNCC, TEN_CC, DCCC) và MH(MSNCC, MSMH) Trong đó: MSNCC: Mã số người cung cấp. TEN CC: Tên người cung cấp DCCC: Địa chỉ cung cấp MSMH: Mã số mặt hàng - Hãy cho mỗi quan hệ 5 bộ dữ liệu Hãy biểu diễn các yêu cáu sau đây bằng ngôn ngữ SQL. a. Tìm mã số người đã cung cấp Ql: ít nhất một mặt hàng Q2: không cung cấp mặt hàng nào Q3: cung cấp mặt hàng có MSMH là 15 Q4: cung cấp ít nhất một mặt hàng nhưng không có mặt hàng có mã số là 15 b. Mặt hàng có mã số là 12, 13, 15 dược cung cấp bởi các nhà cung cấp địa chỉ nào? c. Lập danh sách gồm các cột MSNCC, TEN_CC, MSMH) từ cơ sở dữ liệu trên 2. Cho CSĐL gồm các quan hệ sau: DAIHOC(TENTRUONG,HIEUTRUONG,DIACHI) KHOA(TENTRUONG,MSKHOA,TENKHOA,SOSINHVIEN) SINHVIEN(TENTRUONG,MSKHOA,MSSV,TENSV,DIACHISV) Trong đó: SOSINHVIEN: số lượng là sum Viên MSKHOA: mã số khoa TENSV: tên sinh viên DIACHISV: địa chỉ của sinh viên (hiểu là quê quán) Hãy cho mỗi quan hệ 5 bộ dữ liệu Biểu diễn các câu hỏi sau đây bằng ngôn ngữ SQL 1/ Trường Đại học nào có khoa TINHOC 2/ Tổng số sinh viên học ở tất cả các trường đại học. 69
41. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin 3/ Sinh viên nào học tại quê nhà (giả sử lấy tên tỉnh, thành phố) 4/ Khoa nào của trường có số sinh viên cao nhất? 5/ Cho biết tên hiệu trưởng của các trường có khoa TINHOC 3. Cho CSDL với các quan hệ: NHANVIEN(MSNV,TENNHANVIEN,MSCOQUAN,CONGVIEC, THUTRUONG,LUONG) COQUAN(MSCOQUAN,TENCOQUAN,DIACHI) Hãy cho mỗi quan hệ 5 bộ dữ liệu Biểu diễn bằng ngôn ngữ SQL, và đại số quan hệ yêu cầu sau đây: Ql: Tìm tên những nhân viên ở cơ quan có mã số là 50 Q2: lân mã số tất cả các cơ quan từ quan hệ NHANVIEN Q3: Tìm tên các nhân viên cơ quan có mã số là 15, 20, 25 Q4: Tìm tên những người làm việc ở Đồ Sơn 70
42. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin CHƯƠNG 5 TỐI ƯU HOÁ CÂU HỎI Nói chung các ngông ngữ bậc cao, đòi hỏi thực hiện trong máy tính đều rất tốn kém thời gian. Do vậy trước khi thực hiện các câu hỏi thuộc các ngôn ngữ đó cần thiết phải biến đổi hợp lý để giảm thời gian tính toán. Việc làm đó tạm thời gọi là “tối ưu hoá”. Trong chương này chủ yếu trình bày một vài phương pháp tối ưu hoá các biểu thức quan hệ, đặc biệt là xử lý biểu thức có liên quan tới phép kết nối và phép tích Đề các Sau đó sẽ trình bày chi tiết một phương pháp tối ưu ho một lớp phổ cập của các biểu thức quan hệ. 5.1 Các chiến lược tối ưu 1- Thức hiện phép chọn sớm nhất như có thể Biến đổi câu hỏi để đưa phép chọn vào thực hiện trước nhằm làm giảm bớt kích cỡ của kết quả trung gian và do vậy chi phí phải trả giá cho việc truy nhập bộ nhớ thứ cấp cũng như lưu trữ của bộ nhớ chính sẽ nhỏ đi. 2- Tổ hợp những phép chọn với phép tích Đề các thành phép kết nối Như đã biết, phép kết nối, đặc biệt là phép kết nối bằng được thực hiện “rẻ” hơn là thực hiện các phép tích Đề-các trên cùng các quan hệ. Nếu kết quả của tích Đề-các R x S là đối số của phép chọn và phép chọn liên quan tới các phép so sánh giữa các thuộc tính của R và S thì rõ ràng phép tích Đề-các là phép kết nối. 3- tổ hợp dãy các phép tính một ngôi thành một Một dãy các phép một ngôi như phép chọn hoặc phép chiếu mà kết quả của chúng phụ thuộc vào các bộ của một quan hệ độc lập thì có thể nhóm các phép đó lại 4- Tìm các biểu thức con chung trong một biểu thức F = Fl x F2 ∪ Fl x F3 ⇒ F = Fl x (F2 ∪ F3) 5- Xử lý độc lập các tệp trước khi xử lý chung CSDL Có thể sắp xếp và thiết lập các tệp chỉ số cho từng quan hệ độc lập trước khi xử lý 6- Lựa chọn thứ tự thực hiện các phép toán Một khi cần chọn trình tự thực hiện các phép tính trong biểu thức hoặc chọn một trong hai đối số của một phép hai ngôi cần tính toán xem chi phí thực hiện các phép tính đó (thường là số phép tính, thời gian, dung tích bộ nhớ theo một tỷ lệ giữa kích cỡ các quan hệ ). Từ đó sẽ có được các chi phí phải trả cho các cách khác nhau để thực hiện các câu hỏi 71
43. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin 5.2 Các phép biến đổi tương đương l- Phép giao hoán Nếu R1 và R2 là hai quan hệ, F là điều kiện trên các thuộc tính của Rl và R2 thì Rl*R2 ≡ R2* Rl; Rl x R2 ≡ R2 x Rl 2- Phép kết hợp Nếu R1, R2 và R+3 là các quan hệ, Fl và F2 là các biểu thức điều kiện thì (Rl x R2) x R3 ≡ Rl x (R2 x R3); 3- Xử lý dãy các phép toán chọn σ F1(σ F2(σ F3 (σ F1n(r))) ≡ σ F1^F2^F3^ ^Fn(r) 4- Xử lý dãy các phép toán chiếu Nếu có: A1A2 An ⊆ B1B2 Bn. Thì Π A1A2 An( Π B1B2 Bn(R)) ≡ Π A1A2 An(R) 5- Giao hoán phép chọn và phép chiếu σ F( Π A1A2 An(R)) ≡ Π A1A2 An(σ F(R)) 6-Giao hoán giữa phép chọn và phép tích Đề -các - Nếu các điều kiện chọn chỉ liên quan đến R1 σ F(R1 x R2) ≡ σ F1(Rl) x (R2) - Nếu F = Fl ∧ F2 trong đó Fl chỉ liên quan đến Rl; F2 chỉ liên quan đến R2 σ F(Rl x R2) ≡ σ F1(Rl) x σ F2(R2) - Nếu F1 chỉ liên quan đến Rl, F2 liên quan đến cả Rl và R2 σ F(Rl x R2) ≡ σ F1(σ F1(Rl) x R2) 7- Giao hoán giữa phép chọn và một phép hợp σ F(Rl ∪ R2) ≡ σ F(Rl) ∪ σ F(R2) 8- Giao hoán giữa một phép chọn và một phép trừ σ F(Rl - R2) ≡ σ F(Rl) - σ F(R2) 9- Giao hoán giữa một phép chiếu và phép tích Đề các Nếu các thuộc tính Al, A2, An ∈ Rl; Bl, B2, Bn ∈ R2 Ta có: Π A1A2 An,B1B2 BN(Rl x R2) ≡ Π A1A2 An(Rl) x Π B1B2 Bn(R2) 72
44. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin 10- Giao hoán giữa một phép chiếu và một phép hợp Π A1A2 An(Rl ∪ R2) ≡ Π A1A2 An(Rl) ∪ Π A1A2 An(R2) Ví dụ: Cho biết tên công ty cung cấp mặt hàng màu đỏ *Dùng đại số quan hệ Π TenCongTy(σ mau = “đỏ”(σ CongTy.MaCongTy = CungCap.MaCongTy (σ CungCap.MaHang=HangHoa.MaHang(congty x hanghoa x cungcap)))) - Đẩy phép chọn lên trước Π TenCongTy(σ mau = “đỏ”(σ CongTy.MaCongTy = CungCap.MaCongTy (CongTy x σ CungCap.MaHang=HangHoa.MaHang(HangHoa x Cungcap))) - Chuyển phép chọn và phép tích Đề các thành phép kết nối Π TenCongTy(σ mau=“đỏ”(σ CongTy.MaCongTy = CungCap.MaCongTy (CongTy x (HangHoa * Cungcap))) MaHang - Chuyển phép chọn và phép tích Đề các thành phép kết nối Π TenCongTy(σ mau=“đỏ”(CongTy * HangHoa * CungCap)) MaCongTy MaHang - Đẩy phép chọn lên trước Π TenCongTy(CongTy * (σ mau=“đỏ”(HangHoa) * CungCap)) *Dùng SQL - Không tối ưu: SELECT TenCongTy FROM Congty, HangHoa,CungCap WHERE (CongTy.MaCongTy = CungCap.MaCongTy) AND (CungCap.MaHang = HangHoa.MaHang) AND (HangHoa.Mau = “Đỏ”) - Tối ưu : SELECT TenCongTy FROM CongTy WHERE MaCongTy IN ( SELECT MaCongTy FROM Cungcap WHERE MaHang lN ( SELECT MaHang FROM HangHoa WHERE Mau = “Đỏ”))) 73
45. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin CHƯƠNG 6 AN TOÀN VÀ TOÀN VẸN ĐỮ LIỆU 6.1 An toàn dữ liệu 6.1.1 Giới thiệu - Một hệ cơ sở dữ liệu bao gồm dữ liệu và các chương trình ứng dụng để thực hiện các thao tác dữ liệu. - Người sử dụng (enduser) truy cập vào cơ sở dữ liệu sử dụng các chương trình ứng dụng để thao tác dữ liệu. Để đảm bảo an toàn hệ thống phải xác định: + User hợp pháp: Là người được phép truy nhập vào dữ liệu với một số quyền nào đó + User bất hợp pháp: Là người không được phép truy nhập vào dữ liệu 6.1.2 Thực hiện việc an toàn dữ liệu - Xác định user hợp pháp: Sử dụng mật khẩu để xác minh - Xác minh khung nhìn của user: Hệ thống cho phép user được truy nhập vào bộ phán nào của cơ sở dữ liệu - Xác minh quyền của user: Hệ thống cho phép user có những quyền gì đối với cơ sở dữ liệu 6.1.3 Các lệnh về an toàn dữ liệu trong SQL a) Lệnh tạo khung nhìn CREAT VIEW (danh sách tên cột) AS Ví dụ: - Tạo View DSmoi CREAT VIEW DSmoi AS SELECT * FROM Nhanvien WHERE Ma phong = “M2” Tạo View “Tong” từ bảng “cungcap” với số lượng tăng thêm 20 đơn vị 74
46. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin CREAT VIEW Tong (MaCongTy, MaHang, Tổng số lượng) AS SELECT MaCongTy, MaHang, ( Soluong + 20) FROM Cungcap b) Lệnh trao quyền cho các user GRANT On TO [WITH GRANT OPTION] Các quyền gồm CREAT: Tạo ra cơ sở dữ liệu READ: Đọc SELECI: Chọn WRITE: Ghi INSERT: Bổ sung UPDATE: Cập nhật DELETE: Xoá RUN: Chạy một chương trình ứng dụng Trong đó: - Đối tượng: Là một bảng, một view, một chương trình ứng dụng - Tên user: Là tên một người, một nhóm, hoặc một danh sách nếu dùng từ khoá PUBLIC, WORLD cho mọi users - Tuỳ chọn GRANT OPTION: Khi có tuỳ chọn này user được phép trao quyền của mình cho user khác Ví dụ: GRANT Select, Delete, Update ON Dsluong TO abc WITH GRANT OPTION GRANT Select ON Dsluong TO def c) Thu hồi quyền REVOKE ON FROM Ví dụ: REVOKE Delete ON Write FROM abc 6.2 Tính toàn vẹn dữ liệu 6.2.1 Khái niệm ràng buộc dữ liệu Khi nhập dữ liệu vào ta có một số ràng buộc để hợp lệ hoá khi nhập dữ liệu nếu có giá trị không hợp lệ thì chương trình thông báo yêu cầu nhập lại. Hoặc các ràng báo trong khi sửa chữa cập nhật dữ liệu để tránh tình trạng dị thường trong cơ sở dữ liệu 75
47. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin 6.2.2 Các ràng buộc - Ràng buộc về tập giá trị cho phép: Dùng các phép so sánh để ràng buộc Ví dụ: Bổ xung thêm nhân viên INSERT INTO Nviên ( MãNV, TênNV, Địa chỉ, Lương) VALUES (NV05, “Trần Long”, “Hà nội”, 500 000) Nếu có ràng buộc 1000000 < lương < 2000000 thì giá trị lương trên không hợp lệ - Ràng buộc về giá trị NULL: Nếu một thuộc tính bắt buộc phải có giá trị thì dùng ràng buộc NOT NULL, nếu không có thể để trống -Ràng buộc về khoá chính: Mối quan hệ phải có thuộc tính khoá, là thuộc tính chỉ có một giá trị duy nhất cho mỗi bộ không trùng lặp. Dùng ràng buộc này chương trình sẽ kiểm tra tính trùng lặp của các trị của khoá Ràng buộc về khoá ngoài: Hạn chế phát sinh lỗi khỉ sửa chứa cập nhật các quan hệ có liên kết với nhau 76
48. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin TÀI LIỆU THAM KHẢO [l] Tô Văn Nam, Giáo trình cơ sở dữ liệu. Nhà xuất bản giáo dục. [2] Nguyễn Xuân Huy Bài giảng cơ sớ dữ liệu [3] Vũ Đức Thi, Cơ sở dữ liệu - Kiến thức và thực hành. Nhà xuất bán Thông kê, 1997. [4] TS.Nguyễn Bá Tường, Nhập môn cơ sở dữ liệu phân tán, Nhà xuất bản Khoa học và Kỹ thuật Hà Nội, 2005. [5] Nguyễn Bá Tường, Cơ sở dữ liệu - Lý thuyết và thực hành, Nhà xuất bàn Khoa học và Kỹ thuật, 2001. [6] Lê Tiến Vương. Nhập môn cơ sở dữ liệu quan hệ, Nhà xuất bản Khoa học và Kỹ thuật, 1997. [7] M.Tamer Ôzsu, Patrick Valduriez, Nguyên lý các hệ cơ sở dữ liệu phân tán, Nhà xuất bán Thống kê, 2000. [8] J.Ulman, Nguyên lý các hệ cơ sở dữ liệu và cơ sớ tri thức, Nhà xuất bản Thống kê, 1998. 77
49. Giáo trình Cơ sở dữ liệu 1 – Bộ môn Hệ thống thông tin MỤC LỤC Trang MỘT SỐ CÁC QUY ƯỚC 4 CHƯƠNG 1 5 NHẬP MÔN CƠ SỞ DỮ LIỆU 5 1.1 Giới thiệu về hề thống quản lý tệp truyền thống 5 1.2 Định nghĩa cơ sở dữ liệu 5 1.3. Hệ cơ sở dữ liệu 5 1. 4 Tính độc lập dữ liệu 10 1. 5 Hệ quản trị cơ sở dữ liệu 10 1.6 Các mô hình dữ liệu 10 BÀI TẬP VÀ CÂU HỎI 18 CHƯƠNG 2 19 MÔ HÌNH DỮ LIỆU QUAN HỆ 19 2.1 Thuộc tính 19 2.2 Quan hệ 19 2.3 Khoá của một quan hệ 20 2.4 Các phép toán của đại số quan hệ 21 BÀI TẬP VÀ CÂU HỎI 28 CHƯƠNG 3 30 LÝ THUYẾT THIẾT KẾ CƠ SỞ DỮ LIỆU 30 3.1 Giới thiệu 30 3.2 Sơ đồ quan hệ 31 3.3 Hệ tiên đề cho tập phụ thuộc hàm 32 3.4 Bao đóng của tập phụ thuộc hàm 35 3.5 Phép tách một quan hệ 36 3.6 Chuẩn hoá lược đồ quan hệ 38 3.7 Các thuật toán 40 3.8 Phụ thuộc đa trị 47 BÀI TẬP VÀ CÂU HỎI 49 CHƯƠNG 4 51 NGÔN NGỮ ĐỊNH NGHĨA VÀ THAO TÁC DỮ LIỆU 51 4.1 Giới thiệu về mô hình xử lý dữ liệu 51 4.2 Ngôn ngữ đại số quan hệ 51 4.3 Ngôn ngữ SQL (Structure Quay Language) 53 BÀI TẬP CÂU HỎI 69 CHƯƠNG 5 71 TỐI ƯU HOÁ CÂU HỎI 71 5.1 Các chiến lược tối ưu 71 5.2 Các phép biến đổi tương đương 72 CHƯƠNG 6 74 AN TOÀN VÀ TOÀN VẸN ĐỮ LIỆU 74 6.1 An toàn dữ liệu 74 6.2 Tính toàn vẹn dữ liệu 75 TÀI LIÊU THAM KHẢO 77 78