›
›

Giáo trình Cơ sở dữ liệu

pdf 77 trang phuongnguyen 4851
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Cơ sở dữ liệu", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfgiao_trinh_co_so_du_lieu.pdf

Nội dung text: Giáo trình Cơ sở dữ liệu

  1. TRƯỜNG CAO ĐẲNG NGHỀ ĐẮK LẮK KHOA ĐIỆN TỬ TIN HỌC oOo GIÁO TRÌNH CƠ SỞ DỮ LIỆU NGHỀ: Công nghệ thông tin TRÌNH ĐỘ: Cao đẳng nghề, Trung cấp nghề Người biên soạn: NGUYỄN THỊ THÙY LINH Lưu hành nội bộ - 2014
  2. LỜI MỞ ĐẦU Cơ sở dữ liệu là một lĩnh vực phát triển mạnh của công nghệ thông tin. Cùng với sự phát triển công nghệ thông tin ở nước ta, việc sử dụng các kiến thức về cơ sở dữ liệu vào thực tiễn ngày càng trở lên cần thiết. Để đáp ứng nhu cầu học tập của sinh viên chuyên ngành Công nghệ Thông tin, giáo trình Cơ sở dữ liệu được biên soạn theo chương trình khung của Trường Cao đẳng Nghề Đắk Lắk, cung cấp các kiến thức cơ bản về lý thuyết cơ sở dữ liệu. Trong giáo trình này chúng tôi cung cấp cho sinh viên những kiến thức cơ bản nhất về cơ sở dữ liệu. Mục tiêu chính là với kiến thức cơ bản này sinh viên có thể ứng dụng các kiến thức về cơ sở dữ liệu vào thực tiễn và tiếp tục nghiên cứu học tập được các môn tin học khác. Trong quá trình biên soạn sẽ không tránh khỏi những thiếu sót, rất mong được sự đóng góp ý kiến của đồng nghiệp để giáo trình ngày càng hoàn thiện hơn.
  3. MỤC LỤC CHƯƠNG 1. MÔ HÌNH QUAN HỆ 1 1.1. Nguyên nhân ra đời của mô hình quan hệ 1 1.2. Hệ quản trị cơ sở dữ liệu 1 1.2.1 CSDL là gì 1 1.2.2 Hệ quản trị CSDL 2 1.2.3 Người dùng (User) 2 1.3. Mô hình quan hệ 3 1.3.1 Mô hình quan hệ là gì? 3 1.3.2 Các khái niệm cơ bản của mô hình quan hệ 3 1.3.3 Các phép toán tập hợp 4 1.3.4 Các phép toán quan hệ 5 1.4. Mô hình thực thể kết hợp 7 1.4.1 Giới thiệu mô hình thực thể kết hợp ER 7 1.4.2 Giới thiệu một số ví dụ về mô hình thực thể kết hợp ER. 9 1.4.3 Chuyển từ mô hình thực thể kết hợp sang lược đồ cơ sở dữ liệu 10 BÀI TẬP THỰC HÀNH CHƯƠNG 1 11 CHƯƠNG 2 . NGÔN NGỮ TRUY VẤN SQL 14 2.1 Cách tạo quan hệ bằng Access 14 2.1.1. Giới thiệu MS Access 14 2.1.2 Tạo CSDL bằng MS Access 14 2.2 Câu lệnh truy vấn 15 2.2.1. Biểu thức (Expression) 15 2.2.2 Câu lệnh SQL 17 BÀI TẬP CHƯƠNG 2 22 CHƯƠNG 3. RÀNG BUỘC TOÀN VẸN QUAN HỆ 25 3.1. Ràng buộc toàn vẹn-Các yếu tố của ràng buộc toàn vẹn 25 3.1.1. Ràng buộc toàn vẹn 25 3.1.2. Các yếu tố của ràng buộc toàn vẹn 25 3.2. Phân loại ràng buộc toàn vẹn 26 3.2.1 Ràng buộc toàn vẹn liên bộ 26 3.2.2 Ràng buộc toàn vẹn về phụ thuộc tồn tại 26 3.2.3 Ràng buộc toàn vẹn về miền giá trị 27 3.2.4 Ràng buộc toàn vẹn liên thuộc tính 27 3.2.5 Ràng buộc toàn vẹn liên thuộc tính liên quan hệ 27 BÀI TẬP CHƯƠNG 3 29 CHƯƠNG 4: PHỤ THUỘC HÀM 31 4.1 Khái niệm phụ thuộc hàm 31 4.1.1. Định nghĩa phụ thuộc hàm 31 4.1.2. Phụ thuộc hàm hiển nhiên 32 4.1.3. Thuật toán Satifies 32 4.1.4 Các phụ thuộc hàm có thể có 33 4.2. Hệ luật dẫn Armstrong (Armstrong inference rule) 35 4.2.1 Phụ thuộc hàm được suy diễn logic từ F 35 4.2.2 Hệ luật dẫn Armstrong 35 BÀI TẬP CHƯƠNG 4 40 CHƯƠNG 5: PHỦ CỦA TẬP PHỤ THUỘC HÀM 41 5.1 Định nghĩa 41
  4. 5.2 Phủ tối thiểu của một tập phụ thuộc hàm 41 5.2.1 Phụ thuộc hàm có vế trái dư thừa 41 5.2.2 Phụ thuộc hàm có vế phải một thuộc tính 42 5.2.3 Tập phụ thuộc hàm không dư thừa 42 5.2.4 Tập phụ thuộc hàm tối thiểu 42 5.3 Khóa của lược đồ quan hệ 44 5.4 Thuật toán cải tiến tìm khóa của LĐQH 45 BÀI TẬP CHƯƠNG 5 47 CHƯƠNG 6: CHUẨN HÓA CƠ SỞ DỮ LIỆU 49 6.1. Dạng chuẩn của lược đồ quan hệ 49 6.1.1. Dạng chuẩn một 49 6.1.2. Dạng chuẩn hai 49 6.1.3. Dạng chuẩn ba 50 6.1.4. Dạng chuẩn Boyce – Codd (Boyce-Codd Normal Form) 52 6.1.5. Dạng chuẩn của một lược đồ quan hệ 53 6.2. Phép tách kết nối bảo toàn 53 6.2.1. Phép tách kết nối bảo toàn thông tin (lossless-join decomposition) 53 6.2.2 Phép tách bảo toàn phụ thuộc hàm (decompositions that preserve dependencies) 56 6.3 Thiết kế CSDL bằng cách phân rã 60 6.3.1 Thiết kế CSDL bằng cách phân rã theo các thuật toán thông thường 60 6.3.2 Thiết kế CSDL bằng cách phân rã theo các thuật toán mới 65 BÀI TẬP CHƯƠNG 6 67
  5. VỊ TRÍ, TÍNH CHẤT CỦA MÔN HỌC: Cơ sở dữ liệu là môn học cơ sở nghề bắt buộc của chương trình đào tạo Cao đẳng nghề Công nghệ thông tin (ứng dụng phần mềm). Môn học này được học sau môn Tin học. MỤC TIÊU CỦA MÔN HỌC: Hiểu được nguyên lý thiết kế cơ sở dữ liệu quan hệ; Hiểu về các mô hình dữ liệu và các công cụ mô tả dữ liệu; Hiểu về các khái niệm, tính năng và các phương thức xử lý dữ liệu của hệ quản trị cơ sở dữ liệu SQL; Biết cách xây dựng các ràng buộc, các phụ thuộc hàm, cách chuẩn hóa các cơ sở dữ liệu quan hệ; Thiết kế được một số cơ sở dữ liệu quan hệ thông dụng: quản lý nhân sự, quản lý bán hàng, ; Nghiêm túc, tỉ mỉ, sáng tạo trong quá trình tiếp thu kiến thức và vận dụng vào việc xây dựng các cơ sở dữ liệu cụ thể. Chủ động, tích cực tìm hiểu các tài liệu và nguồn bài tập liên quan. NỘI DUNG MÔN HỌC: 1. Nội dung tổng quát và phân bổ thời gian: Số Thời gian TT Tên chương, mục Tổng số Lý Thực Kiểm tra* thuyết hành, (LT hoặc Bài tập TH) I. Mô hình quan hệ 10 3 7 0 Nguyên nhân ra đời của mô 0.5 0.5 0 0 hình quan hệ Hệ quản trị cơ sở dữ liệu 0.5 0.5 0 0 Mô hình quan hệ 4 1 3 0 Mô hình thực thể kết hợp 5 1 4 0 II. Ngôn ngữ truy vấn SQL 17 4 11 2 Cách tạo quan hệ bằng Access 2 1 1 0 Câu lệnh truy vấn 13 3 10 0 Kiểm tra 2 0 0 2 III. Ràng buộc toàn vẹn quan hệ 7 2 5 0 Ràng buộc toàn vẹn-Các yếu 0.5 0.5 0 0 tố của ràng buộc toàn vẹn Phân loại ràng buộc toàn vẹn 6.5 1.5 5 0 IV. Phụ thuộc hàm 6 2 4 0 Khái niệm phụ thuộc hàm 5 1 4 0 Hệ luật dẫn Armstrong 1 1 0 0 V. Phủ của tập phụ thuộc hàm 8 3 5 0 Định nghĩa 0.5 0.5 0 0 Phủ tối thiểu của một tập phụ 1.5 1.5 0 0 thuộc hàm Khóa của lược đồ quan hệ 6 1 5 0 VI. Chuẩn hóa Cơ sở dữ liệu 12 4 6 2
  6. Các dạng chuẩn của lược đồ 6 2 4 0 quan hệ Phép tách kết nối bảo toàn 2 1 1 0 Thiết kế cơ sở dữ liệu bằng 2 1 1 0 cách phân rã Kiểm tra 2 0 0 2 Tổng cộng 60 18 38 4 ĐIỀU KIỆN THỰC HIỆN Vật liệu: Giáo trình, tài liệu về Cơ sở dữ liệu, một số cơ sở dữ liệu thực tiễn; Dụng cụ và trang thiết bị: Phòng học, máy tính, máy chiếu,
  7. CHƯƠNG 1. MÔ HÌNH QUAN HỆ MỤC TIÊU CỦA BÀI: Sau khi học xong bài này người học có khả năng: - Trình bày được các khái niệm về cơ sở dữ liệu, hệ quản trị cơ sở dữ liệu và mô hình quan hệ; - Trình bày được cách chuyển đổi từ lược đồ CSDL sang mô hình quan hệ dữ liệu; - Áp dụng các phép toán đại số quan hệ (các phép toán tập hợp) để biểu diễn trên lược đồ quan hệ; - Chuyển đổi được từ lược đồ CSDL sang mô hình quan hệ dữ liệu - Nghiêm túc, tỉ mỉ trong việc học và làm bài tập. NỘI DUNG BÀI HỌC 1.1. Nguyên nhân ra đời của mô hình quan hệ Trong nhiều năm, công nghệ tính toán và thông tin phát triển từ những hệ thống lớn, đắt tiền, độc quyền đến các hệ thống mở mạnh và không đắt tiền. Sự pháttriển này mang lại lợi ích to lớn cho người dùng cuối bởi sự phát triển của các gói ứng dụng số như xử lý văn bản, bảng tính điện tử, văn phòng xuất bản, hệ quản lý cơ sở dữ liệu, máy tính trợ giúp công nghệ phần mềm Trước khi máy tính hóa cơ sở dữ liệu đươc giới thiệu, dữ liệu được lưu trữ theo kiểu điện tử thành nhiều tập tin riêng biệt sử dụng hệ tập tin (từ đây về sau ta gọi hệ tập tin theo lối cũ). Những tập tin này được xử lý bằng các ngôn ngữ thế hệ thứ ba như COBOL, FORTRAN, PASCALvà ngay cả BASICđể tạo ra các giải pháp cho các vấn đề của doanh nghiệp. Mỗi ứng dụng, chẳng hạn như hệ tính lương, hệ kho hay hệ thống kế toán sẽ có một tập các tập tin riêng chứa dữ liệu riêng. Các ứng dụng như vậy tạo ra ba vấn đề sau: - Có sự liên kết chặt chẽ giữa cấu trúc luận lý và cấu trúc vật lý của các tập tin và chương trình ứng dụng khai thác chúng. Điều này khiến việc tạo nên các ứng dụng này rất khó khăn, tốn nhiều thời gian và do vậy mà tốn kém trong bảo trì hệ thống. - Có sự dư thừa dữ liệu rất lớn qua việc trùng lắp các tập tin trong các ứng dụng khác nhau. Điều này tạo ra những vấn đề như: dữ liệu thiếu nhất quán, không gian đĩa bị lãng phí, thời gian bảo trì và lưu phòng hờ các tập tin gia tăng, vấn đề về quản trị như không chú trọng bảo mật và tổ chức dữ liệu thiếu thống nhất. - Người sử dụng có ít khả năng khai thác trực tiếp dữ liệu 1.2. Hệ quản trị cơ sở dữ liệu Khởi đầu, sự giới thiệu CSDL và HQTCSDL nhằm giải quyết các vấn đề của hệ thông tin dựa trên các tập tin theo lối cũ (C1.I). Điều này tạo raviệc phát triển trên hai mươi lăm năm qua một hệ CSDL quan hệ thương mại xuất hiện cuối những năm thập niên 70 và các năm đầu của thập niên 80. Trước khi xem xét CSDL và hệ QTCSDLQH giải quyếtmột vài vấn đề của hệ thông tin theo lối cũ như thế nào chúng ta cần làm rõ vài khái niệm. 1.2.1 CSDL là gì Một cơ sở dữ liệu có thể định nghĩa tạm như sau: một chỗ chứa có tổ chức tập hợp các tập tin dữ liệu có tương quan, các mẫu tin và các cột. Ngày nay CSDL tồn tại trong mỗi ứng dụng thông dụng, ví dụ: 1
  8. - Hệ kho và kiểm kê. - Hệ đặt chỗ máy bay - Hệ nguồn nhân lực. - hệ dịch vụ công cộng như cấp nước, điện, khí đốt - Điều khiển quá trình chế tạo và sản xuất 1.2.2 Hệ quản trị CSDL Một hệ quản trị CSDL (HQTCSDL) là: - một tập các phần mềm quản lý CSDL và cung cấp các dịchvụ xử lý CSDL cho các những người phát triển ứng dụng và người dùng cuối. - HQTCSDL cung cấp một giao diện giữa người sử dụng và dữ liệu. - HQTCSDL biến đổi CSDL vật lý thành CSDL logic. Hình 1.1 Dựa vào cách tổ chức dữ liệu, HQTCSDL được chia thành năm loại: - loại phân cấp như hệ IMS của IBM - loại mạng như IDMS của Cullinet Software - Loại tập tin đảo như ADABAS của Software AG - Loại quan hệ như như ORACLE của Oracle, DB2 của IBM, ACCESS của Ms Access - Loại đối tượng là một tiếp cận khá mới trong thiết kế HQTCSDL và việc sử dụng loại này sớm trở nên phổ biến Hiện tại, loại HQTCSDL chính được sử dụng trong công nghệ là loại HQTCSDL quan hệ (RDBMS). Loại này đã chiếm lĩnh trong công nghệ trên 10-15 năm cuối cùng khi đánh bật loại HQTCSDL phân cấp và gần đây là HQTCSDL mạng. 1.2.3 Người dùng (User) Người dùng khai thác CSDL thông qua HQTCSDL có thể phân thành ba loại: người quản trị CSDL, người phát triển ứng dụng và lập trình, người dùng cuối. - Người quản trị CSDL, hàng ngày, chịu trách nhiệm quản lý và bảo trì CSDL như: + sự chính xác và toàn vẹn của dữ liệu và ứng dụng trong CSDL, sự bảo mật của CSDL + lưu phòng hờ và phục hồi CSDL + giữ liên lạc với người phát triển ứng dụng, người lập trình và người dùng cuối. + bảo đàm sự hoạt động trôi chảy và hiệu quả của CSDL và HQTCSDL Người phát triển và lập trình ứng dụng là những người chuyên nghiệp về máy tính có trách nhiệm thiết kế, tạo dựng và bảo trì hệ thông tin cho người dùng cuối. - Người dùng cuối là những người không chuyên về máy tính nhưng họ là các chuyên gia trong các lãnh vực khác có trách nhiệm cụ thể trong tổ chức. Họ khai thác CSDL thông qua hệ được phát triển bởi người phát triển ứng dụng hay các công cụ truy vấn của HQTCSDL. 2
  9. 1.3. Mô hình quan hệ 1.3.1 Mô hình quan hệ là gì? Mô hình Cơ sở dữ liệu Quan hệ (gọi tắt là Mô hình Quan hệ) do E.F Codd đề xuất năm 1971. Mô hình này bao gồm: - Một hệ thống các ký hiệu để mô tả dữ liệu dưới dạng dòng và cột như quan hệ, bộ, thuộc tính, khóa chính, khoá ngoại, - Một tập hợp các phép toán thao tác trên dl như phép toán tập hợp, phép toán quan hệ. - ràng buộc toàn vẹn quan hệ. Các hệ HQTCSDLQH ngày nay được xây dựng dựa vào lý thuyết của mô hình quan hệ. Mục đích của môn học này giúp cho sinh viên nắm được kiến trúc tổng quát về mô hình quan hệ và áp dụng nó để lập mô hình dữ liệu quan hệ có hiệu quả trong lưu trữ và khai thác. 1.3.2 Các khái niệm cơ bản của mô hình quan hệ - Thuộc tính Chẳng hạn với bài toán quản lý điểm thi của sinh viên; với đôi tượng sinh viên ta cần phải chú ý đến các đặc trưng riêng như họ tên,ngày sinh, nữ (giới tính), tỉnh thường trú, học bổng, lớp mà sinh viên theo học,. . . các đặc trưng này gọi là thuộc tính. - Lược đồ quan hệ Tập tất cả các thuộc tính cần quản lý của một đối tượng cùng với mối liên hệ giữa chúng được gọi là lược đồ quan hệ. Lược đồ quan hệ Q với tập thuộc tính {A1,A2, ,An}được viết là Q(A1,A2, ,An). Tập các thuộc tính của Q được ký hiệu là Q+. Chẳng hạn lược đồ quan hệ sinh viên (Đặt tên là Sv) với các thuộc tính như trên là: Sv(MASV, HOSV,TENSV,NU, NGAYSINH, MALOP, HOCBONG, TINH) Thường khi thành lập một lược đồ, người thiết kế luôn gắn cho nó một ý nghĩa nhất định, ý nghĩa đó gọi là tân từcủa lược đồ quan hệ đó. Dựa vào tân từ người ta xác định được tập thuộc Tính khóacủa lược đồ quan hệ - Quan hệ Sự thể hiện của lược đồ quan hệ Q ở một thời điểm nào đó được gọi là quan hệ, rõ ràng là trên một lược đồ quan hệ có thể định nghĩa rất nhiều quan hệ. Thường ta dùng các ký hiệu như R, S, Qđể chỉ các lược đồ quan hệ, còn quan hệ được định nghĩa trên nó tương ứng được ký hiệu là là r, s, q - Bộ (tuple) Bộ là tập mỗi giá trị liên quan của tất cả các thuộc tính của một lược đồ quan hệ. Chẳng hạn quan hệ sau có 2 bộ. Thường người ta dùng các chữ cái thường (như t,p,q, ) để biểu diễn các bộ. Chẳng hạn để nói bộ t thuộc quan hệ r ta viết: t ?r. - Khóa Cho lược đồ quan hệ R, S #R+. S được gọi là một siêu khóa(superkey) của lược đồ quan hệ R nếu với hai bộ tùy ý trong quan hệ R thì giá trị của các thuộc tính trong S là khác nhau. Một lược đồ quan hệ có thể có nhiều siêu khoá.Siêu khoá chứa ít thuộc tính nhất được gọi là khóa chỉ định, trong trường hợp lược đồ quan hệ có nhiều khóa chỉ định, thì khóa được chọn để cài đặt gọi là khóa chính(Primary key) (trong các phần sau khóa chính được gọi tắt là khóa) 3
  10. Các thuộc tính tham gia vào một khóa được gọi là thuộc tính khóa (prime key), ngược lại được gọi là thuộc tính không khóa (non prime key). Một thuộc tính được gọi là khóa ngoạinếu nó là thuộc tính của một lược đồ quan hệ này nhưng lại là khóa chính của lược đồ quan hệ khác Ví dụ: Ta hãy xem lược đồ quan hệ sau: Xe(SODANGBO,QUICACH, INHDANG,MAUSAC,SOSUON,SOMAY,MAXE,QUOCGIA) Siêu khóa: (SOSUON,QUICACH), Khóa chỉ định: (SODANGBO,QUOCGIA), (SOSUON), (SOMAY), (MAXE) Khóa chính: MAXE Thuộc tính khóa: SODANGBO,QUOCGIA, SOSUON, SOMAY, MAXE Thuộc tính không khóa: QUICACH, HINHDANG, MAUSAC Khóa của Sv là (MASV), Khoá của Mh là (MAMH), khoá của Kh là (MAKHOA), khóa của Kq là (MASV,MAMH)khóa của Lop là MALOP, trong Lop thuộc tính MAKHOA là khóa ngoại 1.3.3 Các phép toán tập hợp - Phép hợp Cho hai lược đồ quan hệ Q1và Q2có cùng tập thuộc tính {A1,A2, ,An}. r1và r2lần lượt là hai quan hệ trên Q1và Q2. Phép hợp của hai lược đồ quan hệ Q1và Q2sẽ tạo thành một lược đồ quan hệ Q3.Q3được xác định như sau: Vd: - Phép giao Cho hai lược đồ quan hệ Q1và Q2có cùng tập thuộc tính {A1,A2, ,An}. r1và r2lần lượt là hai quan hệ trên Q1và Q2. Phép giao của hai lược đồ quan hệ Q1và Q2sẽ tạo thành một lược đồ quan hệ Q3như sau: Q' Q r ' r(E) r : E {t | t rvàt(E) } - Phép trừ Cho hai lược đồ quan hệ Q1và Q2có cùng tập thuộc tính {A1,A2, ,An}. r1và r2 lần lượt là hai quan hệ trên Q1và Q2. Phép trừ lược đồ quan hệ Q1cho Q2sẽ tạo thành một lược đồ quan hệ Q3 như sau: Q3 {A1, A2 , ,AN } r3 r1 r2 {t | t r1hoăo t r2 4
  11. - Tích Descartes Cho hai lược đồ quan hệ Q1(A1,A2, ,An), Q2(B1,B2, ,Bm). r1và r2lần lượt là hai quan hệ trên Q1và Q2. Tích Descartes của hai lược đồ quan hệ Q1và Q2sẽ tạo thành một lược đồ quan hệ Q3như sau Q3 Q1 Q2 {A1 ,B1, } r3 r1 r2 {t | t r1hoăo t r2 1.3.4 Các phép toán quan hệ - Phép chiếu Cho một lược đồ quan hệ Q(A1,A2, ,An). r là quan hệ trên Q. Phép chiếu của Q lên tập thuộc tính X sẽ tạo thành lược đồ quan hệ Q’= Q[X], trong đó Q’+ chính là X và r’ chính là r nhưng chỉ lấy các thuộc tính của X. Q' X r ' r[X ] r.X {t ' | t rvàt.X t[X ] t ' Phép chiếu chính là phép rút trích dữ liệu theo cột (chiều dọc) Ví dụ: - Phép chọn (Selection) Cho lược đồ quan hệ Q(A1,A2, ,An), r là một quan hệ trên Q. X  Q và E là một mệnh đề logic được phát biểu trên tập X. Phần tử t ∈ r thỏa mãn điều kiện E ký hiệu là t(E). Phép chọn từ r theo điều kiện E sẽ tạo thành một lược đồ quan hệ Q’ như sau: Q' Q r ' r(E) r : E {t | t rvàt(E) } phép chọn chính là phép rút trích dữ liệu theo dòng (chiều ngang) Ví dụ: 5
  12. - Phép kết (nối) – phép kết tự nhiên Cho hai lược đồ quan hệ Q1(A1,A2, ,An), Q2(B1,B2, ,Bm). r1 và r2 lần lượt là hai quan hệ trên Q1 và Q2. Ai và Bj lần lượt là các thuộc tính của Q1 và Q2 sao cho MGT(AI) = MGT(BJ) (MGT: miền giá trị). θ là một phép so sánh trên MGT(AI). Phép kết giữa Q và Q sẽ tạo thành một lược đồ quan hệ Q như sau: Q3 Q1 Q2 r3 r1 | | r2 {t12 | t1 r1,t2 r2 sao cho t12 .Q1 t1 t12 .Q2 t2 t1.A1t2 .B j Ta rút ra các bước cụ thể để thực hiện phép kết như sau: + Tạo tích descartes + Thực hiện phép chọn theo điều kiện E=Ai θ Bj Ví dụ: Ai là thuộc tính B, Bj là thuộc tính F và θ là phép so sánh “>=”. Ta được kết quả là quan hệ sau: Nếu θ được sử dụng trong phép kết là phép so sánh bằng (=) thì ta gọi là phép kết bằng. Hơn nữa nếu AI ≡ Bj thì phép kết bằng này được gọi là phép kết tự nhiên. Phép kết tự nhiên là một phép kết thường dùng nhất trong thực tế. Ví dụ: Với Ai ≡ Bj = MAMH - Phép chia Cho hai lược đồ quan hệ Q1(A1,A2, ,An), Q2(B1,B2, ,Bm). r1 và r2 lần lượt là hai quan hệ trên Q1 và Q2. Ai và Bj lần lượt là các thuộc tính của Q1 và Q2 sao cho n>m. Phép chia Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3 như sau: Q3 {A1, ,An m } r3 r1 r2 {t3 | t2 r2 ,t1 r1t3 t1. {A1, ,An m } t2 t1. {An m 1, An } } Ví dụ: 6
  13. 1.4. Mô hình thực thể kết hợp 1.4.1 Giới thiệu mô hình thực thể kết hợp ER Các nhà phân tích thiết kế hệ thống thông tin thường xây dựng lược đồ cơ sở dữ liệu từ mô hình thực thể kết hợp và mô hình này lại được xây dựng từ phần đặc tả vấn đề của một bài toán thực tế. Lược đồ cơ sở dữ liệu xây dựng theo hướng này thông thường đạt tối thiểu dạng chuẩn 3 (3NF: third normal form) nghĩa là ở dạng có sự dư thừa dữ liệu ở mức tối thiểu, còn môn CSDL xây dựng lược đồ CSDL đạt dạng chuẩn 3 từ lược đồ cơ sở dữ liệu chưa đạt dạng chuẩn có kèm các tân từ . Ta hãy xem ví dụ sau: - Mối quan hệ 1 – 1 * Đặc tả vấn đề Phòng cảnh sát mong muốn quản lý lý lịch cá nhân những người lái xe và bằng lái của họ. Một người chỉ lấy được một bằng lái và một bằng lái chỉ thuộc về một người. Thông tin về lái xe mà phòng cảnh sát quan tâm là: mã người lái xe, tên, địa chỉ, ngày sinh. Thông tin về bằng lái cần lưu trữ là: mã bằng lái, loại bằng lái, ngày hết hạn * Mô hình thực thể kết hợp BANGLAI NGƯỜILX Hình 1.2 + Mỗi NGƯỜI LÁI XE phải sở hữu một BẰNG LÁI + Mỗi BẰNG LÁI phải được sở hữu bởi một NGƯỜI LÁI XE - Mối quan hệ 1 – nhiều * Đặc tả vấn đề Những người phụ trách đào tạo của Trường cao đẳng cộng đồng núi Ayers mong muốn tạo lập mộtCSDL về các môn đào tạo của trường (như: chứng chỉ leo núi, công nghệ bay) và học viên ghi danh vào những môn học này. Trường cũng có qui định là cùng một lúc, học viên chỉ có thể ghi danh vào một môn học. Họ chỉ quan tâm về dữ liệu của đợt ghi danh hiện tại. Một khi học viên kết thúc môn học thì nhà trường sẽ không còn quan tâm đến họ và những học viên này phải được xóa khỏi CSDL. Thông tin cần lưu trữ về một học viên bao gồm: mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại, ngày nhập học . Thông tin về môn học gồm mã môn học, tên môn học, thời lượng Phân tích: - Phần đặc tả vấn đề chứa đựng các qui tắc quản lý và dữ liệu yêu cầu của vấn đề. - Dữ liệu của vấn đề là: chi tiết về học viên có mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại và ngày nhập học chi tiết về môn học có mã môn học, tên môn học và thời lượng. - Qui tắc quản lý gồm: 7
  14. + Cùng một lúc, một học viên chỉ có thể ghi danh vào một môn học. + Nhiều học viên có thể ghi danh vào một môn học. + Nhà trường chỉ quan tâm đến những học viên của môn học hiện tại. * Mô hình thực thể kết hợp (Mô hình ER) MONHOC HOCVIEN Hình 1.3 Các tính chất trong mô hình thực thể kết hợp: - Hình chữ nhật được gọi là tập thực thể. Tên của tập thực thể được ghi bên trong hình chữ nhật và dùng danh từ để đặt tên cho tập thực thể. - Đường nối giữa hai tập thực thể được gọi là mối quan hệ (mối kết hợp). Mối quan hệ trong vấn đề trên là mối quan hệ một-nhiều (1:M). Nội dung của mối quan hệ được diễn tả theo hai chiều: “ghi danh vào”, “được ghi danh bởi” và chúng diễn tả hai nội dung sau: + Mỗi HỌC VIÊN có thể ghi danh vào một MÔN HỌC + Mỗi MÔN HỌC phải được ghi danh bởi một hay nhiều HỌC VIÊN - Các dữ liệu ghi bên cạnh tập thực thể được gọi là thuộc tính. Chúng cung cấp thông tin chi tiết về tập thực thể. Có hai loại thuộc tính: - Thuộc tính nhận diện là thuộc tính để phân biệt thực thể này với thực thể kia trong tập thực thể. Thuộc tính mô tả là thuộc tính cung cấp thông tin chi tiết hơn về thực thể trong tập thực thể. Mối quan hệ của vấn đề trên là mối quan hệ một-nhiều. Tính chất này của mối quan hệ gọi là tính kết nối của mối quan hệ. Tính kết nối một-nhiều rất phổ biến trong mô hình thực thể kết hợp. Hai loại kết nối còn lại ít phổ biến hơn nhưng không kém phần quan trọng là mối quan hệ một-một và mối quan hệ nhiều-nhiều. - Mối quan hệ nhiều – nhiều * Đặc tả vấn đề Người phụ trách đào tạo Trường cao đẳng cộng đồng núi xanh mong muốn thiết lập một csdl về các môn học mà họ cung cấp (như chứng chỉ leo núi, cử nhân công nghệ bay) và các học viên ghi danh vào các môn học này. Nhà trường qui định là một học viên được ghi danh học tối đa ba môn học trong cùng một lúc. Họ chỉ quan tâm đến dữ liệu của môn học hiện tại. Một khi học viên kết thúc môn học, họ sẽ không còn thuộc diện quản lý của nhà trường và phải được xóa khỏi csdl trừ khi học viên này ghi danh học tiếp môn mới. Thông tin về một học viên gồm: mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại, ngày nhập học Thông tin về môn học gồm: mã môn học, tên môn học, thời lượng * Mô hình ER HOCVIEN MONHOC Hình 1.4 8
  15. + Mỗi HỌC VIÊN có thể ghi danh vào một hay nhiều MÔN HỌC + Mỗi MÔN HỌC phải được ghi danh bởi một hay nhiều HỌC VIÊN Mô hình ER trên có mối quan hệ nhiều nhiều. * Loại bỏ tính kết nối nhiều nhiều (nếu được) Mô hình trên gặp phải khuyết điểm sau: - Ngày nhập học là thuộc tính gắn liền với tập thực thể HỌC VIÊN sẽ không hợp lý vì không diễn tả được trường hợp học viên học cùng lúc nhiều môn học. - Còn nếu ngày nhập học là thuộc tính của MÔN HỌC thì không diễn tả được tình trạng cùng môn học nhưng có các ngày nhập học khác nhau. HOCVIEN PHIEUGD MONHOC Hình 1.5 Để giải quyết vấn đề này ta phải đưa vào: + Một tập thực thể làm trung gian giữa HỌC VIÊN và MÔN HỌC gọi là tập kết hợp PHIẾU GHI DANH. + Thuộc tính nhận diện của tập kết hợp là sự kết hợp giữa thuộc tính nhận diện của tập thực thể HỌC VIÊN và MÔN HỌC + Thuộc tính mô tả của tập kết hợp PHIẾU GHI DANH là ngày nhập học + Tính kết nối của tập kết hợp với tập thực thể là một-nhiều Nội dung của mối quan hệ giữa các tập thực thể là: + Mỗi HỌC VIÊN có thể có một hay nhiều PHIẾU GHI DANH + Mỗi PHIẾU GHI DANH phải thuộc về một HỌC VIÊN + Mỗi PHIẾU GHI DANH phải ghi nhận đào tạo về một MÔN HỌC + Mỗi MÔN HỌC có thể được ghi nhận đào tạo bởi một hay nhiều PHIẾU GHI DANH Các qui tắc phải tuân thủ khi thêm tập kết hợp làm trung gian để loại bỏ tính kết nối nhiều nhiều: - Phải nhận diện được thuộc tính mô tả của tập kết hợp. - Nếu có thuộc tính mô tả thì tạo tập kết hợp làm trung gian giữa hai tập thực thể. - Nếu không có thuộc tính mô tả thì vẫn giữ nguyên mô hình như hình 1.4.4 1.4.2 Giới thiệu một số ví dụ về mô hình thực thể kết hợp ER. * QUẢN LÝ LAO ĐỘNG Để quản lý việc phân công các nhân viên tham gia vào xây dựng các công trình. Công ty xây dựng ABC tổ chức quản lý như sau: Cùng lúc công ty có thể tham gia xây dựng nhiều công trình, mỗi công trình có một mã số công trình duy nhất (MACT), mỗi mã số công trình xác định các thông tin như: tên gọi công trình (TENCT), địa điểm(ĐIAĐIEM), ngày công trình được cấp giấy phép xây dựng (NGAYCAPGP), ngày khởi công (NGAYKC), ngày hoàn thành (NGAYHT). Mỗi nhân viên của công ty ABC có một mã số nhân viên(MANV) duy nhất, một mã số nhân viên xác định các thông tin như: Họ tên (HOTEN), ngày sinh (NGAYSINH), phái (PHAI), địa chỉ (ĐIACHI). Mỗi nhân viên phải chịu sự quản lý hành chánh bởi một phòng ban. Tất nhiên một phòng ban quản lý hành chánh nhiều nhân viên. Công ty có nhiều phòng ban (Phòng kế toán, phòng kinh doanh, phòng kỹ thuật, phòng tổ chức, phòng chuyên môn, Phòng phục vụ, ). Mỗi phòng ban có một mã số phòng ban(MAPB) duy nhất, mã phòng ban xác định tên phòng ban (TENPB). Công ty phân công các nhân viên tham gia vào các công trình, mỗi công trình có thể được phân cho nhiều nhân viên và mỗi nhân viên cùng lúc cũng có thể tham gia vào 9
  16. nhiều công trình. Với mỗi công trình một nhân viên có một số lượng ngày công (SLNGAYCONG) đã tham gia vào công trình đó. 1.4.3 Chuyển từ mô hình thực thể kết hợp sang lược đồ cơ sở dữ liệu 1.4.3.1 Quy tắc chung - Mỗi thực thể trong ER chuyển thành 1 LĐQH - Mỗi thuộc tính trong ER được chuyển thành thuộc tính trong LĐQH tương ứng - Mỗi thuộc tính nhận diện trong ER được chuyển thành khóa chính trong LĐQH tương ứng - Mỗi mối quan hệ trong ER được chuyển thành khóa ngoại theo quy tắc 4.3.2 1.4.3.2 Quy tắc chuyển đổi thành khóa ngoại - Mối quan hệ 1 – 1: Khóa chính của một bên bất kỳ sẽ làm thuộc tính của bên quan hệ 1 còn lại. - Mối quan hệ 1 – nhiều: Khóa chính của thực thể bên nhiều sẽ làm khóa ngoại của thực thể bên quan hệ 1 - Mối quan hệ nhiều – nhiều: Tạo ra 1 quan hệ mới, có ít nhất là 2 thuộc tính là khóa của 2 thực thể thành phần 10
  17. BÀI TẬP THỰC HÀNH CHƯƠNG 1 1. Phép toán tập hợp và phép toán quan hệ Cho lược đồ cơ sở dữ liệu dùngđể quản lý hồ sơ sinh viên bao gồm các quan hệ Sv(sinh viên), Lop(Lớp), kh(khoa), Mh(môn học), Kq(kết quả)được mô tả bởi các lược đồ quan hệ như sau: Sv(MASV,HOTEN,NU,NGAYSINH,MALOP,TINH,HOCBONG) Tân từ: Mỗi sinh viên có mỗi MASV duy nhất. Mỗi MASV xác định tất cả các thuộc tính còn lại của sinh viên đó. Lop(MALOP,TENLOP,SISO,MAKHOA) Tân từ: Mỗi lớp có một mã lớp duy nhất, mỗi lớp chỉ thuộc về một khoa nào đó. Kh(MAKHOA,TENKHOA,SOCBGD) Tân từ: Mỗi khoa có mỗi MAKHOA duy nhất. Mỗi MAKHOA xác định tất cả các thuộc tính còn lại của khoa đó. Mh(MAMH,TENMH,SOTIET) Tân từ: Môi Môn học có một MAMH duy nhất. Mỗi MAMH xác định tất cả các thuộc tính còn lại của môn học đó. Kq(MASV,MAMH,DIEMTHI) Tân từ: Mỗi sinh viên cùng với một môn học xác dịnh duy nhất một điểm thi YÊU CẦU: 1. Tìm khóa cho mỗi lược đồ quan hệ trên. 2. Hãy thực hiện các câu hỏi sau bằng ngôn ngữ đại số quan hệ a. Lập danh sách sinh viên gồm MASV, HOTEN, HOCBONG b. Lập danh sách sinh viên nữ khoa ‘CNTT’,danh sách cần MASV, HOTEN, HOCBONG c. Lập bảng điểm cho tất cả sinh viên khoa ‘CNTT’, bảng điểm gồm các cột MASV, HOTEN, TENMH, DIEMTHI d. Lập phiếu điểm cho sinh viên có MASV=”99001” e. Lập danh sách sinh viên gồm MASV,HOTEN,TENLOP, TENKHOA f. Lập bảng điểm môn học có mã môn học là CSDLcho tất cả sinh viên có mã lớp là “CĐTH2B” g. Lập danh sách sinh viên của lớp có mã lớp là “CĐTH2B” và có điểm thi môn học lớn hơn hay bằng 8. 2 Mô hình thực thể kết hợp Dựa vào các phân tích sơ bộ dướiđây, hãy lập mô hình thực thể kết hợp cho mỗi bài toán quản lý sau: * QUẢN LÝ LAO ĐỘNG Để quản lý việc phân công các nhân viên tham gia vào xây dựng các công trình. Công ty xây dựng ABC tổ chức quản lý như sau: Cùng lúc công ty có thể tham gia xây dựng nhiều công trình, mỗi công trình có một mã số công trình duy nhất (MACT), mỗi mã số công trình xác định các thông tin như: tên gọi công trình (TENCT), địa điểm(ĐIAĐIEM), ngày công trình được cấp giấy phép xây dựng (NGAYCAPGP), ngày khởi công (NGAYKC), ngày hoàn thành (NGAYHT). Mỗi nhân viên của công ty ABC có một mã số nhân viên(MANV) duy nhất, một mã số nhân viên xác định các thông tin như: Họ tên (HOTEN), ngày sinh (NGAYSINH), phái (PHAI), địa chỉ (ĐIACHI). Mỗi nhân viên phải chịu sự quản lý hành chánh bởi một 11
  18. phòng ban. Tất nhiên một phòng ban quản lý hành chánh nhiều nhân viên. Công ty có nhiều phòng ban (Phòng kế toán, phòng kinh doanh, phòng kỹ thuật, phòng tổ chức, phòng chuyên môn, Phòng phục vụ, ). Mỗi phòng ban có một mã số phòng ban(MAPB) duy nhất, mã phòng ban xác định tên phòng ban (TENPB). Công ty phân công các nhân viên tham gia vào các công trình, mỗi công trình có thể được phân cho nhiều nhân viên và mỗi nhân viên cùng lúc cũng có thể tham gia vào nhiều công trình. Với mỗi công trình một nhân viên có một số lượng ngày công (SLNGAYCONG) đã tham gia vào công trình đó. * QUẢN LÝ THƯ VIỆN Một thư viện tổ chức việc cho mượn sách như sau: Mỗi quyển sách được đánh một mã sách (MASH) dùng để phân biệt với các quyển sách khác (giả sử nếu một tác phẩm có nhiều bản giống nhau hoặc có nhiều tập thì cũng xem là có mã sách khác nhau), mỗi mã sách xác định các thông tin khác như : tên sách (TENSACH), tên tác giả (TACGIA), nhà xuất bản (NHAXB), năm xuất bản (NAMXB). Mỗi đọc giả được thư viên cấp cho một thẻ thư viện, trong đó có ghi rõ mã đọc giả (MAĐG), cùng với các thông tin khác như : họ tên (HOTEN), ngày sinh (NGAYSINH), địa chỉ (ĐIACHI), nghề nghiệp(NGHENGHIEP). Cứ mỗi lượt mượn sách, đọc giả phải ghi các quyển sách cần mượn vào một phiếu mượn, mỗi phiếu mượn có một số phiếu mượn (SOPM) duy nhất, mỗi phiếu mượn xác định các thông tin như: ngày mượn (NGAYMUON), đọc giả mượn, các quyển sách mượn và ngày trả (NGAYTRA). Các các quyển sách trong cùng một phiếu mượn không nhất thiết phải trả trong trong cùng một ngày. * QUẢN LÝ BÁN HÀNG Mỗi khách hàng có một mã khách hàng (MAKH) duy nhất, mỗi MAKH xác định được các thông tin về khách hàng như : họ tên khách hàng (HOTEN), địa chỉ (ĐIACHI), số điện thoại (ĐIENTHOAI). Các mặt hàng được phân loại theo từng nhóm hàng, mỗi nhóm hàng có một mã nhóm (MANHOM) duy nhất, mỗi mã nhóm hàng xác định tên nhóm hàng (TENNHOM), tất nhiên một nhóm hàng có thể có nhiều mặt hàng. Mỗi mặt hàng được đánh một mã số (MAHANG) duy nhất, mỗi mã số này xác định các thông tin về mặt hàng đó như : tên hàng (TENHANG), đơn giá bán (ĐONGIA), đơn vị tính (ĐVT). Mỗi hóa đơn bán hàng có một số hóa đơn SOHĐ) duy nhất, mỗi hóa đơn xác định được khách hàng và ngày lập hóa đơn (NGAYLAPHĐ), ngày bán hàng (NGAYBAN). Với mỗi mặt hàng trong một hóa đơn cho biết số lượng bán (SLBAN) của mặt hàng đó. * QUẢN LÝ LỊCH DẠY - HỌC Để quản lý lịch dạy của các giáo viên và lịch học của các lớp, một trường tổ chức như sau: Mỗi giáo viên có một mã số giáo viên (MAGV) duy nhất, mỗi MAGV xác định các thông tin như: họ và tên giáo viên (HOTEN), số điện thoại (DTGV). Mỗi giáo viên có thể dạy nhiều môn cho nhiều khoa nhưng chỉ thuộc sự quản lý hành chánh của một khoa nào đó. Mỗi môn học có một mã số môn học (MAMH) duy nhất, mỗi môn học xác định tên môn học (TENMH). Ưng với mỗi lớp thì mỗi môn học chỉ được phân cho một giáo viên. Mỗi phòng học có một số phòng học (SOPHONG) duy nhất, mỗi phòng có một chức năng (CHUCNANG); chẳng hạn như phòng lý thuyết, phòng thực hành máy tính, phòng nghe nhìn, xưởng thực tập cơ khí, Mỗi khoa có một mã khoa (MAKHOA) duy nhất, mỗi khoa xác định các thông tin như: tên khoa (TENKHOA), điện thoại khoa(DTKHOA). 12
  19. Mỗi lớp có một mã lớp (MALOP) duy nhất, mỗi lớp có một tên lớp (TENLOP), sĩ số lớp (SISO). Mỗi lớp có thể học nhiều môn của nhiều khoa nhưng chỉ thuộc sự quản lý hành chính của một khoa nào đó. Hàng tuần, mỗi giáo viên phải lập lịch báo giảng cho biết giáo viên đó sẽ dạy những lớp nào, ngày nào (NGAYDAY), môn gì?, tại phòng nào, từ tiết nào (TUTIET) đến tiết nào (DENTIET),tựa đề bài dạy (BAIDAY), ghi chú (GHICHU) về các tiết dạy này, đây là giờ dạy lý thuyết (LYTHUYET) hay thực hành - giả sử nếu LYTHUYET=1 thì đó là giờ dạy thực hành và nếu LYTHUYET=2 thì đó là giờ lý thuyết, một ngày có 16 tiết, sáng từ tiết 1 đến tiết 6, chiều từ tiết 7 đến tiết 12, tối từ tiết 13 đến 16. oOo 13
  20. CHƯƠNG 2 . NGÔN NGỮ TRUY VẤN SQL MỤC TIÊU CỦA BÀI: Sau khi học xong bài này người học có khả năng: - Trình bày được cách xây dựng cơ sở dữ liệu trên Access; - Xây dựng được một số cơ sở dữ liệu trên Access; - Trình bày được các biểu thức, hàm được sử dụng trong câu lệnh truy vấn SQL - Trình bày được cấu trúc các câu lệnh định nghĩa dữ liệu trong ngôn ngữ SQL - Ap dụng được truy vấn chọn, truy vấn nhóm dữ liệu, truy vấn lồng, truy vấn cập nhật, truy vấn xóa - Nghiêm túc, tỉ mỉ trong việc học và làm bài tập. NỘI DUNG BÀI HỌC 2.1 Cách tạo quan hệ bằng Access 2.1.1. Giới thiệu MS Access - Là một hệ quản trị CSDL - Thuộc bộ MS Office - Ưu & nhược điểm - Làm quen với màn hình làm việc của Access 2.1.2 Tạo CSDL bằng MS Access Field: Trường dữ liệu, là thuộc tính của các thực thể trong mô hình ER. Mỗi Field có kiểu dữ liệu và kích thước tương ứng Record: Là các mẫu tin, tương ứng với các bộ (tuple) Kiểu dữ liệu: Có nhiều kiểu dữ liệu khác nhau như text (chuỗi), number (số), date/time (ngày giờ) Giới thiệu về mối quan hệ Relations Mỗi thực thể có một mối quan hệ tương ứng với thực thể khác Mối quan hệ này có các ràng buộc toàn vẹn khác nhau. Để thực hiện được các ràng buộc đối với CSDL, ta phải xác định mối quan hệ dựa vào đặc tả bài toán tương ứng. Quy tắc nhập dữ liệu theo các quy tắc ràng buộc dữ liệu: Quy tắc nhập liệu phải đảm bảo: - Tính hợp lệ của dữ liệu - Tính nhất quán dữ liệu - Đảm bảo đúng kiểu dữ liệu Bước 1: Tạo CSDL trống Khởi động Access: Start-> Programs-> Microsoft Access. Tạo lược đồ csdl rỗng có tên là qLSV: Blank Database->OK->qLSV->Create Bước 2: Mở mục Table để tạo bảng Vào menu Table Bước 3: Bắt đầu tạo bảng + Tạo quan hệ bằng cách:Tables->New->Design View->OK - Khai báo tên các trường, kiểu dữ liệu và thuộc tính tương ứng Bước 4: Tạo khóa chính (nếu có) Chọn trường khóa chính Rclick Primary key Bước 5: Lưu bảng File / save Đặt tên table tại phần table name 14
  21. Bước 6: Xác định mối quan hệ giữa các bảng trong CSDL Dựa vào đặc tả của bài toán để xác định mối quan hệ giữa các bảng Bước 7: Tạo mối quan hệ Relationship giữa các bảng dữ liệu trong CSDL Relationships Chọn các bảng tham gia tạo mối quan hệ Kéo thuộc tính là khóa chính ở bảng 1 sang thuộc tính khóa ngoại tương ứng bên bảng quan hệ Chọn RBTV OK Bước 8: Nhập liệu theo đúng quy tắc Nhập bên có quan hệ 1 trước & nhập bên quan hệ nhiều sau 2.2 Câu lệnh truy vấn 2.2.1. Biểu thức (Expression) Các thành phần tạo nên biểu thức bao gồm: Literal value: Là các dữ liệu có giá trị đúng như văn bản thể hiện. Dữ liệu chuỗi có dạng: “New York” Dữ liệu số có dạng: 1056; 1056.25 Dữ liệu ngày có dạng: #1-Jan-94#; #12/2/2001# Constant: Là một tên đại diện cho một giá trị không thay đổi như : Toán tử số học Toán tử logic Toán tử so sánh 15
  22. Toán tử khác Với toán tử like ta có thể sử dụng các ký tự đại diện sau: Hàm: Hàm có dạng tenHam(danhSachDoiSo). Hàm luôn luôn đại diện cho một trị gọi là trị trả về. IIf(điều kiện, trị 1, trị 2) Kiểm tra điều kiện, nếu điều kiện đúng trả trị 1 ngược lại trả trị 2 Ví dụ: IIf(namNu = 1, “Nam”,”Nu”) Date() Trả về ngày tháng năm của hệ thống. Now(biểu thức ngày) Trả về giờ, phút, giây, ngày tháng năm của hệ thống. Time(biểu thức ngày) Trả về giờ phút giây của hệ thống. Day(biểu thức ngày) Trả về một số từ 1 đến 31 là ngày của Date. Month(biểu thức ngày) Trả về một số từ 1 đến 12 là tháng của Date Year(biểu thức ngày) Trả về năm của ngày Len( biểu thức chuỗi) Trả về chiều dài của chuỗi. Chr(mã Ascii) Trả về ký tự có mã ASCII tương ứng. InStr(Start, s1, s2) Trả về vị trí chuỗi s2 nằm trong s1 LCase(s), UCase(s) Đổi chuỗi s thành chuỗi gồm các ký tự thường (hoa) Left(s, n), Right(s, n) Trả về chuỗi gồm n ký tự bên trái (phải) của chuỗi s Mid(s, i, n) Trả về chuỗi con của chuỗi s,gồm n ký tự kể từ ký tự thứ i 16
  23. Nz(v1, v2) Nếu v1 = Null thì Trả về v2, ngược lại trả về v1 Các hàm tính toán trên nhóm: SUM (thuộc tính ) Tính tổng giá trị của thuộc tính của các bộ trong bảng MAX( thuộc tính) tính giá trị lớn nhất của thuộc tính của các bộ trong bảng MIN(thuộc tính) tính giá trị nhỏ nhất của thuộc tính của các bộ trong bảng AVG(thuộc tính>) tính giá trị trung bình của thuộctính của các bộ trong bảng COUNT(thuộc tính) chỉ đếm những bộ mà giá trị của thuộc tính là khác NULL Biểu thức Biểu thức là tổ hợp các toán tử, literal value, hằng, tên hàm, tên thuộc tính. Biểu thức được lượng gía thành một gía trị. 2.2.2 Câu lệnh SQL SQL là ngôn ngữ truy vấn dựa trên đạisố quan hệ. Câu lệnh của SQL dùng để rút trích dữ liệu của một một hay nhiều quan hệ. Kết quả của một câu lệnh SQL (truy vấn) là một quan hệ. Để đơn giản trong cách trình bày, ta xem quan hệ mà câu truy vấn sử dụng để tạo ra quan hệ khác gọi là quan hệ nguồn, quan hệ kết quả của truy vấn là quan hệ đích. 2.2.2.1 Truy vấn định nghĩa dữ liệu - Cấu trúc tạo lược đồ quan hệ: Câu lệnh CREATE TABLE có cú pháp như sau CREATE TABLE tên_bảng ( tên_cột thuộc_tính_cột các_ràng_buộc [, ,tên_cột_n thuộc_tính_cột_n các_ràng_buộc_cột_n] [,các_ràng_buộc_trên_bảng] ) Trong đó: tên_bảng Tên của bảng cần tạo. Tên phải tuântheo qui tắc định danh và không được vượt quá 128 ký tự. tên_cột Là tên của cột (trường) cần định nghĩa, tên cột phải tuân theo qui tắc định danh vàkhông được trùng nhau trong mỗi một bảng. Mỗi một bảng phải có ít nhất một cột. Nếu bảng có nhiều cột thì định nghĩa của các cột (tên cột, thuộc tính và các ràng buộc) phải phân cách nhau bởi dấu phẩy. thuộc_tính_cột Mỗi một cột trong một bảng ngoài tên cột còn có các thuộc tính bao gồm: Kiểu dữ liệu Đây là thuộc tính bắt buộc phải có đối với mỗi cột. 17
  24. Giá trị mặc định là giá trị được tự động gán cho cột nếu nhưngười sửdụng không nhập dữ liệu cho cột một cách tường minh. Mỗi một cột chỉcó thể có nhiều nhất một giá trịmặc định. Cột có tính chất IDENTITY hay không? tức là giá trị của cột có được tự động tăng mỗi khi cóbản ghi mới được bổsung hay không. Tính chất này chỉcó thểsử dụng đối với các trường kiểu số. Cột có chấp nhận giá trịNULL hay không Ví dụ3.1: Khai báo dưới đây định nghĩa cột STT cókiểu dữ liệu là int và cột có tính chất IDENTITY: stt INT IDENTITY hay định nghĩa cột NGAYcó kiểu datetimevà không cho phép chấp nhận giá trị NULL: ngay DATETIME NOT NULL và định nghĩa cột SOLUONGkiểu intvà có giá trịmặc định là 0: soluong INT DEFAULT (0) các_ràng_buộc Các ràng buộc được sửdụng trên mỗi cột hoặc trên bảng nhằm các mục đích sau: • Quy định khuôn dạng hay giá trịdữliệu được cho phép trên cột (chẳng hạn qui định tuổi của một học sinh phải lớn hơn 6 và nhỏhơn 20, số điện thoại phải là một chuỗi bao gồm 6 chữsố, ). Những ràng buộc kiểu này được gọi là ràng buộc CHECK Đảm bảo tính toàn vẹn dữliệu trong một bảng và toàn vẹn thamchiếu giữa các bảng trong cơ sơ dữ liệu. Những loại ràngbuộcnày nhằm đảm bảo tính đùng của dữliệu như: sốchứng minh nhân dân của mỗi một người phải duy nhất, nếu sinh viênhọc một lớp nào đó thì lớp đó phải tồn tại, Liên quan đến những loại ràng buộcnày bao gồm các ràng buộc PRIMARY KEY (khoá chính), UNIQUE (khóa dựtuyển) và FOREIGN KEY (khoángoài) Ví dụ: Câu lệnh dưới đây định nghĩa bảng NHANVIEN với các trường MANV (mã nhân viên), HOTEN (họvà tên), NGAYSINH(ngày sinh của nhân viên), DIENTHOAI (điện thoại) và HSLUONG (hệsốlương) CREATE TABLE nhanvien ( manv NVARCHAR(10) NOT NULL, hoten NVARCHAR(50) NOT NULL, ngaysinh DATETIME NULL, dienthoai NVARCHAR(10) NULL, hsluong DECIMAL(3,2) DEFAULT (1.92) ) Trong câu lệnh trên, trường MANV và HOTEN của bảng NHANVIEN không được NULL(tức là bắt buộc phải có dữliệu), trườngNGAYSINH vàDIENTHOAI sẽ nhận giá trịNULL nếu ta không nhập dữliệu cho chúng còn trường HSLUONG sẽ nhận giá trịmặc định là 1.92 nếu không được nhập dữ liệu. Ràng buộc PRIMARY KEY Ràng buộc PRIMARY KEY được sửdụng để định nghĩa khoá chính của bảng. Khoá chính của một bảng là một hoặc một tập nhiều cột mà giá trịcủa chúng là duy nhất trong bảng. Hay nói cách khác, giá trịcủa khoá chính sẽgiúp cho ta xác định được duy 18
  25. nhất một dòng (bản ghi) trong bảng dữliệu. Mỗi một bảng chỉcóthểcó duy nhất một khoá chính và bản thân khoá chính không chấp nhận giá trịNULL. Ràng buộc PRIMARY KEY là cơsởcho việc đảm bảo tính toàn vẹn thực thểcũng như toàn vẹn tham chiếu. Đểkhai báo một ràng buộc PRIMARY KEY,ta sửdụng cú pháp nhưsau: [CONSTRAINT tên_ràng_buộc] PRIMARY KEY [(danh_sách_cột)] Nếu khoá chính của bảng chỉbao gồm đúng một cột và ràng buộc PRIMARY KEY được chỉ định ởmức cột, ta không cần thiết phải chỉ định danh sách cột sau từkhoá PRIMARY KEY. Tuy nhiên, nếu việc khai báo khoá chính được tiến hành ởmức bảng (sửdụng khi sốlượng các cột thamgia vào khoá là từhai trởlên) thì bắt buộc phải chỉ định danh sách cột ngay sau từkhóa PRIMARY KEYvà tên các cột được phân cách nhau bởi dấu phẩy. Ràng buộc FOREIGN KEY Các bảng trong một cơsởdữliệu có mối quan hệvới nhau. Những mối quan hệ này biểu diễn cho sựquan hệgiữa các đối tượng trong thếgiới thực. Vềmặt dữliệu, những mối quan hệ được đảm bảo thông qua việc đòi hỏi sựcó mặt của một giá trị dữ liệu trong bảng này phải phụthuộc vào sựtồn tại của giá trịdữliệu đó ởtrong một bảng khác. Ràng buộc FOREIGN KEY được sửdụng trong định nghĩa bảng dữliệu nhằm tạo nên mối quan hệgiữa các bảng trong một cơsởdữliệu. Một hay một tập các cột trong một bảng được gọi là khoá ngoại, tức là có ràng buộc FOREIGN KEY, nếu giá trịcủa nó được xác định từkhoáchính (PRIMARYKEY) hoặc khoá phụ(UNIQUE) của một bảng dữ liệu khác Ràng buộc FOREIGN KEY được định nghĩa theo cú pháp dưới đây: [CONSTRAINT tên_ràng_buộc] FOREIGN KEY [(danh_sách_cột)] REFERENCES tên_bảng_tham_chiếu(danh_sách_cột_tham_chiếu) [ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT] [ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULT] 2.2.2.2 Truy vấn chọn (Select query) - Ý nghĩa Khi có nhu cầu thể hiện các dòng dữ liệu của một quan hệ hay của nhiều quan hệ dưới dạng một quan hệ có số cột và số dòng theo ý muốn như bảng điểm của sinh viên, danh sách sinh viên thì ta sử dụng truy vấn chọn. Để truy vấn chọn ta sử dụng câu lệnh SQL sau: - Phân tích yêu cầu truy vấn: + Xác định trường cần hiển thị + Xác định bảng tham gia truy vấn + Xác định điều kiện nếu có * Trình tự tạo truy vấn chọn Bước 1: Phân tích yêu cầu bài toán + Xác định trường cần hiển thị 19
  26. + Xác định bảng tham gia truy vấn + Xác định điều kiện nếu có Bước 2: Sử dụng cấu trúc truy vấn chọn để viết lệnh truy vấn Bước 3: Chọn CSDL tham gia vào truy vấn Bước 4: Thực hiện truy vấn và đánh giá kết quả 2.2.2.3 Truy vấn nhóm dữ liệu - Cấu trúc truy vấn nhóm dữ liệu Select From Where Groupby Having Order by cột - Ý nghĩa các thành phần trong cấu trúc truy vấn nhóm dữ liệu điềuKiệnLọcMẫuTinNguồn:điều kiện mà các mẫu tin nguồn phải thỏa mãn (phép chọn) fieldGroupBy: tên field mà các mẫu tin có dữ liệu giống nhau trên ấy được xếp vào cùng nhóm. điềuKiệnLọcMẫuTinTổngHợp: điều kiện mà các mẫu tin tổng hợp phải thỏa mãn (phép chọn) 2.2.2.4 Truy vấn lồng nhau Là những câu lệnh truy vấn mà trong thành phần WHEREhay HAVINGcó chứa thêm một câu lệnh Select khác. Câu lệnh select khác này gọi là subquery. Ta lồng câu Select vào phần Where hay Having theo cú pháp sau: o bieuthuctoanTuSoSanh[ANY | ALL | SOME] (cauLenhSQL) ANY, SOME là bất kỳ, ALL là tất cả Các mẫu tin của query chính thỏa mãn toán tử so sánh với bất kỳ/ tất cả mẫu tin nào của subquery o bieuThuc[NOT] IN (cauLenhSQL) Các mẫu tin của query chính có giátrị bằng với một giá trị trong subquery o [NOT] EXISTS (cauLenhSQL). Các mẫu tin của query chính thỏa mãn khi subquery có mẫu tin - Cấu trúc truy vấn lồng Select From Where [biểu thức (not) in | biểu thức toán tử so sánh câu lệnh SQL |not exist (câu lệnh SQL) ] 2.2.2.5 Truy vấn cập nhật dữ liệu - Cấu trúc truy vấn cập nhật dữ liệu Cú pháp: Update tableSet field1= biểuThức1, field2 = biểuThức2 Where điềuKiện - Cấu trúc truy vấn xóa dữ liệu Cú pháp: 20
  27. Delete From tableWhere điềuKiện - Ý nghĩa các thành phần trong cấu trúc truy vấn lồng + Update: Cập nhật giá trị cho các trường bất kỳ trên 1 table thỏa mãn điều kiện nào đó + Delete: Xóa giá trị bất kỳ trên 1 table thỏa mãn điều kiện nào đó, lưu ý ràng buộc toàn vẹn 2.2.2.6. Truy vấn tổng hợp dữ liệu Để tổng hợp dữ liệu ta sử dụng các toán tử tính toán sum (tính tổng), max (giá trị lớn nhất), count (đếm, thống kê), Avg (tính giá trị trung bình) Bên cạnh đó, ta phải kết hợp lệnh Group by để nhóm dữ liệu cần thống kê. - Cấu trúc truy vấn tổng hợp dữ liệu Cú pháp: Select field1,field2 field 3 as tên trường mới From Bảng tham gia truy vấn Where điều kiện (nếu có) Group by trường nhóm dữ liệu Having điều kiện trong Group by - Ý nghĩa các thành phần trong cấu trúc truy vấn tổng hợp dữ liệu + Sử dụng các toán tử để tổng hợp dữ liệu : sum, min, max, count 21
  28. BÀI TẬP CHƯƠNG 2 1/ Cho lược đồ CSDL quản lý sinh viên. Hãy thực hiện các câu truy vấn sau a) Lập danh sách những sinh viên nam của tỉnh “LONG AN” học khoa “CNTT”, danh sách cần tất cả các thuộc tính của quan hệ Sv. b) Lập danh sách những sinh viên có điểm thi < 5 (thi lại),danh sách cần MASV,HOTEN,TENMH, DIEMTHI và đượcsắp tăng dần theo cột MASV. c) Lập danh sách các sinh viên có điểm thi trung bình các môn < 5, danh sách cần MASV,HOTEN, DIEMTRUNGBINH và được sắp tăng dần theo cột MASV. d) Tổng số tiền học bổng của mỗi khoa e) Những sinh viên nào đăng ký học nhiều hơn 3 môn học, danh sách cần MASV,HOTEN,SOLAN_DANGKY f) Lập danh sách sinh viên có điểm trung bình cao nhất, danh sách cần MASV, HOTEN, NGAYSINH, DIEMTRUNGBINH 2/ Cho lược đồ CSDL dùng để quản lý lao động bao gồm các lược đồ quan hệ sau: Nhanvien(MANV,HOTEN,NGAYSINH,PHAI,DIACHI,MAPB) Tân từ: Mỗi nhân viên có một mã số nhân viên (MANV) duy nhất. Mộtmã số nhân viên xác định các thông tin như họ tên (HOTEN), ngày sinh (NGAYSINH), phái (PHAI), địa chỉ (DIACHI) và phòng ban (MAPB) nơi quản lý nhân viên. Phongban(MAPB,TENPB) Tân từ: Mỗi phòng ban có một mã phòng ban (MAPB) duy nhất, mã phòng ban xác định tên phòng ban (TENPB) Cong(MACT,MANV,SLNGAYCONG) Tân từ: Lược đồ quan hệ Cong ghi nhận số lượng ngày công (SLNGAYCONG) của một nhân viên (MANV) tham gia vào công trình (MACT). Congtrinh(MACT,TENCT,DIADIEM,NGAYCAPGP,NGAYKC,NGAYHT) Tân từ: Mỗi công trình có một mã số công trình (MACT) duy nhất. Mã số công trình xác định các thông tin như tên gọi công trình (TENCT), địa điểm (DIADIEM), ngày công trình được cấp giấy phép xây dựng (NGAYCAPGP), ngày khởi công (NGAYKC), ngày hoàn thành (NGAYHT) Hãy thực hiện các câu hỏi sau bằng SQL a) Danh sách những nhân viên có tham gia vào công trình có mã công trình (MACT) là X. Yêu cầu các thông tin: MANV,HOTEN, SLNGAYCONG, trong đó MANV được sắp tăng dần. b) Đếm số lượng ngày công của mỗi công trình. Yêu cầu các thông tin: MACT, TENCT, TONGNGAYCONG (TONGNGAYCONG là thuộc tính tự đặt) c) Danh sách những nhân viên có sinh nhật trong tháng 8. yêu cầu các thông tin: MANV, TENNV, NGAYSINH, ĐIACHI,TENPB, sắp xếp quan hệ kết quả theo thứ tự tuổi giảm dần. d) Đếm số lượng nhân viên của mỗi phòng ban. Yêu cầu các thông tin: MAPB, TENPB, SOLUONG. (SOLUONG là thuộc tính tự đặt.) 3/ Cho các quan hệ sau: Monhoc(MSMH ,TENMH,SOTINCHI ,TINHCHAT) MSMH mã số môn học, TENMH tên môn học SOTINCHI số lượng tín chỉ, 22
  29. TÍNH CHẤT bằng 1 nếu đó là môn học bắt buộc, bằng 0 nếu đó là môn học không bắt buộc Sinhvien(MSSV,HOTEN,NGAYSINH,LOP) MSSV mã số sinh viên, HOTEN họ tên sinh viên NGAYSINH ngày sinh, LOP(C,4,0) lớp Diem(MSSV,MSMH,DIEMTHI) DIEMTHI điểm thi Hãy dùng lệnh SQL để thực hiện các câu lệnh sau: a) Hãy cho biết những môn học bắt buộc có SOTINCHI cao nhất. b) Hãy liệt kê danh sách gồm MSSV,HOTEN,LOP, DIEMTHI của những sinh viên thi môn học CSDL, theo thứ tự LOP,DIEMTHI c) Hãy cho biết các sinh viên có điểm thi cao nhất về môn học có mã là CSDL d) Hãy cho biết phiếu điểm của sinh viên có mã số là 9900277 e) Hãy liệt kê danh sách gồm MSSV, HOTEN., LOP, ĐIỂM TRUNG BÌNH của những sinh viên có điểm trung bình các môn dưới 5, theo thứ tự LOP,HOTEN. f) Hãy liệt kê danh sách điểm trung bình của sinh viên theo thứ tự , lớp, tên. g) Hãy cho biết điểm của sinh viên theo từng môn. 4/ Dựa vào lược đồ cơ sở dữ liệu Docgia(MADG,HOTEN,NGAYSINH,DIACHI,NGHENGHIEP) Phieumuon(SOPM,NGAYMUON,MADG) Chitietmuon(SOPM,MADAUSACH,NGAYTRA) Dausach(MADAUSACH,BAN,TAP,MASH) Sach(MASH,TENSACH,TACGIA,NHAXB,NAMXB) Hãy thực hiện các câu hỏi sau đây bằng SQL a) Danh sách các đọc giả đã đăng ký mượn sách trong ngày d. Yêu cầu các thông tin: MAĐG, HOTEN, ĐIACHI. b) Các quyển sách của phiếu mượn có SOPM là x. Yêu cầu các thông tin MASH, TENSACH, TACGIA, NGAYMUON, NGAYTRA. c) Tổng số lượt mà mỗi đọc giả đến mượn sách trong năm 2001.Yêu cầu thông tin MAĐG,HOTEN,SOLANMUON (SOLANMUON là thuộc tính tự đặt) d) Danh sách các đọc giả cao tuổi nhất đã mượn sách trong ngày d. Yêu cầu các thông tin MAĐG, HOTEN, NGAYSINH, ĐIACHI, NGHENGHIEP. 5/ Dựa vào lược đồ cơ sở dữ liệu Khach(MAKH,HOTEN,DIACHI,DIENTHOAI) Hoadon(SOHD,NGAYLAPHD,NGAYBAN,MAKH) DongHoaDon(SOHD,MAHANG,SLBAN) Hang(MAHANG,TENHANG,DONGIA,DVT,MANHOM) Nhom(MANHOM,TENNHOM) Hãy thực hiện các câu hỏi sau bằng SQL a) Danh sách các khách hàng đã mua hàng trong ngày d. Yêu cầu các thông tin MAKH, HOTEN, ĐIACHI, ĐIENTHOAI. b) Danh sách các mặt hàng trong số hóa đơn (SOHĐ) là x. Yêu cầu các thông tin MAHANG, TENHANG, SLBAN, ĐONGIA, THANHTIEN (THANHTIEN= SLBAN*ĐONGIA; 23
  30. THANHTIEN là thuộc tính tự đặt).Yêu cầu sắp xếp tăng dần theo cột TENHANG c) Danh sách các mặt hàng thuộcmã nhóm hàng là A có đơn giácao nhất. Yêu cầu các thông tin : MAHANG, TENHANG,ĐONGIA d) Đếm số lượng mặt hàng của mỗi nhóm hàng. Yêu cầu các thông tin : MANHOM, TENNHOM, SOLUONG. (trong đó SOLUONG là thuộc tính tự đặt) (0,75đ) e) Danh sách các khách hàng đã mua các mặt hàng có mã nhóm hàng là A trong ngày d. Yêu cầu các thông tin MAKH, HOTEN, ĐIACHI, ĐIENTHOAI,TENHANG. f) Thống kê việc mua hàng trong năm 2002 của khách hàng có mã khách hàng là Kh01 (theo từng hóa đơn). Yêu cầu các thông tin MAKH,HOTEN,SOHĐ,TRIGIAHĐ trong đó TRIGIAHĐ là tổng số tiền trong một hóa đơn (TRIGIAHĐ là thuộc tính tự đặt) 6/ Dựa vào lược đồ cơ sở dữ liệu Giaovien(MAGV,HOTEN,DTGV,MAKHOA) Khoa(MAKHOA,TENKHOA,DTKHOA) Lop(MALOP,TENLOP,SISO,MAKHOA) Monhoc(MAMH,TENMH) Phonghoc(SOPHONG,CHUCNANG) Lichbaogiang(MALICH,NGAYDAY,MAGV) Dongbaogiang(MALICH,TUTIET,DENTIET,BAIDAY,GHICHU,LYTHUYET, MAM,MALOP,SOPHONG) Hãy thực hiện các câu hỏi sau bằng SQL a) Xem lịch báo giảng tuần từ ngày 16/09/2002 đến ngày 23/09/2002 của giáo viên có MAGV (mã giáo viên) là TH3A040. Yêu cầu: MAGV,HOTEN,TENLOP,TENMH,SOPHONG, NGAYDAY, TUTIET, DENTIET, BAIDAY, GHICHU b) Xem lịch báo giảng ngày 23/09/2002 của các giáo viên có mã khoa là CNTT. Yêu cầu: MAGV, HOTEN, TENLOP, TENMH, PHONG, NGAYDAY, TUTIET, DENTIET, BAIDAY, GHICHU) c) Cho biết số lượng giáo viên (SOLUONGGV) của mỗi khoa, kết quả cần sắp xếp tăng dần theo cột tên khoa. yêu cầu: TENKHOA ,SOLUONGGV ( SOLUONGGV là thuộc tính tự đặt) oOo 24
  31. CHƯƠNG 3. RÀNG BUỘC TOÀN VẸN QUAN HỆ MỤC TIÊU: Sau khi học xong bài này người học có khả năng: - Trình bày được khái niệm, cách phân loại, các yếu tố ràng buộc toàn vẹn; - Xây dựng được các ràng buộc dữ liệu trong một số bài toán cụ thể. - Nghiêm túc, tỉ mỉ trong việc học và làm bài tập. NỘI DUNG BÀI HỌC 3.1. Ràng buộc toàn vẹn-Các yếu tố của ràng buộc toàn vẹn 3.1.1. Ràng buộc toàn vẹn Trong mỗi CSDL luôn tồn tại nhiều mối liên hệ giữa các thuộc tính, giữa các bộ. Sự liên hệ này có thể xảy ra trong một lược đồ quan hệ hoặc trong các lược đồ quan hệ của một cơ sở dữ liệu. Các mối liên hệ này là những điều kiện bấtbiến mà tất cả các bộ của những quan hệ có liên quan trong CSDL đều phải thỏa mãn ở mọi thời điểm. Những điều kiện bất biến đó được gọi là ràng buộc toàn vẹn. Trong thực tế ràng buộc toàn vẹn là các quy tắc quản lý được áp đặt trên các đối tượng của thế giới thực. Nhiệm vụ của người phân tích thiếtkế là phải phát hiện càng đầy đủ và chính xác các ràng buộc toàn vẹn càng tốt và mô tả chúng một cách chính xác trong hồ sơ phân tích thiết kế - đó là một việc làm rất quan trọng và rất cần thiết. Trong một cơ sở dữ liệu, ràng buộc toàn vẹn đượcxem như là một công cụ để diễn đạt ngữ nghĩa của CSDL. Một CSDL được thiết kế cồng kềnh nhưng nó thể hiện được đầy đủ ngữ nghĩa của thực tế vẫn có giá trị cao hơn rất nhiều so với một cách thiết kế gọn nhẹ nhưng nghèo nàn về ngữ nghĩa vì thiếu các ràng buộc toàn vẹn của cơ sở dữ liệu. Công việc kiểm tra ràng buộc toàn vẹn thường được tiến hành vào thời điểm cập nhật dữ liệu ( thêm, sửa, xóa). Những ràng buộc toàn vẹn phát sinh cần phải được ghi nhận và xử lý một cách tường minh (thường là bởi một hàm chuẩn hoặc một đoạn chương trình). 3.1.2. Các yếu tố của ràng buộc toàn vẹn Mỗi ràng buộc toàn vẹn có 3 yếu tố: điều kiện, bối cảnh và tầm ảnh hưởng. 3.1.2.1 Điều kiện Điều kiện của một ràng buộc toàn vẹn R có thể được biểu diễn bằng ngôn ngữ tự nhiên, thuật giải, ngôn ngữ đại số tập hợp, đại số quan hệ, ngoài rađiều kiện của ràng buộctoàn vẹn cũng có thể được biểu diễn bằng phụ thuộc hàm. Chẳng hạn, với lược đồ quan hệ SV thì có một ràng buộc toàn vẹn như sau: Với r là một quan hệ của Sv ta có ràng buộc toàn vẹn sau  t 1 ,t 2 r t .MASV t .MASV cuối 3.1.2.2 Bối cảnh Bối cảnh của một ràng buộc toàn vẹn là những quan hệ mà ràng buộc đó có hiệu lực hay nói một cách khác, đó là những quan hệ cần phải được kiểm tra ràng buộc toàn vẹn. Bối cảnh của một ràng buộc toàn vẹn có thể là một hoặc nhiều quan hệ. Chẳng hạn với ràng buộc toàn vẹn trên thì bối cảnh là một quan hệ Sv 3.1.2.3 Tầm ảnh hưởng Trong quá trình phân tích thiết kế một CSDL, người phân tích cần lập bảng tầm ảnh hưởng cho một ràng buộc toàn vẹn nhằm xác định thời điểm cần phải tiến hành kiểm tra 25
  32. các ràng buộc toàn vẹn đó. Các thời điểm cần phải kiểm tra RBTV chính là những thời điểm cập nhật dữ liệu (thêm /sửa/ xóa) 3.2. Phân loại ràng buộc toàn vẹn Trong quá trình phân tích thiết kế cơ sở dữ liệu, người phân tích phải phát hiện tất cả các ràng buộc toàn vẹn tiềm ẩn trong CSDL đó. Việcphân loại các ràng buộc toàn vẹn là rất có ích, nó nhằm gíúp cho người phân tích có được một định hướng, tránh bỏ sót những ràng buộc toàn vẹn. Các ràng buộc toàn vẹn có thể được chia làm hai loại chính như sau: + Ràng buộc toàn vẹn trên phạm vi là một quan hệ bao gồm :Ràng buộc toàn vẹn miền giá trị, ràng buộc toàn vẹn liên thuộc tính, ràng buộc toàn vẹn liên bộ. + Ràng buộc toàn vẹn trên phạm vi nhiều quan hệ bao gồm :Ràng buộc toàn vẹn phụ thuộc tồn tại, ràng buộc toàn vẹn liên bộ - liên quan hệ, ràng buộc toàn vẹn liên thuộc tính - liên quan hệ. 3.2.1 Ràng buộc toàn vẹn liên bộ Ràng buộc toàn vẹn liên bộ là sự ràng buộc toàn vẹn giữa các bộ trong cùng một quan hệ . Ràng buộc toàn vẹn liên bộ hay còn gọi là ràng buộctoàn vẹn về khóa. Đây là loại ràng buộc toàn vẹn rất phổ biến, nó có mặt trong mọi lược đồ quan hệ của CSDL và thường được các hệ quản trị CSDL tự động kiểm tra. 3.2.2 Ràng buộc toàn vẹn về phụ thuộc tồn tại Ràng buộc toàn vẹn về phụ thuộc tồn tại còn đượcgọi là ràng buộc toàn vẹn về khóa ngoại. Cũng giống như ràng buộc toàn vẹn về khóa chính, ràng buộc toàn vẹn về phụ thuộc tồn tại rất phổ biến trong CSDL Trình tự thực hiện Bước 1: Phân tích yêu cầu bài toán Bước 2: Xác định khóa của từng LĐQH Bước 3: Xác định quan hệ trong cùng 1 LĐQH với khóa Bước 4: Đưa ra bảng ràng buộc toàn vẹn liên bộ Bước 5: Xác định khóa ngoại của các LĐQH Bước 6: Đưa ra bảng ràng buộc toàn vẹn về phụ thuộc tồn tại. Ví dụ: Bước 1: Với bài toán QLBH, ta có các LĐQH KHACH, DAT HANG; Mỗi khách hàng có thể đặt hàng nhiều mặt hàng; Mỗi khách hàng sẽ có một MAKH để phân biệt; Trong LĐQH cũng có MAKH để nhận biết đơn đặt hàng đó thuộc về khách hàng nào. Bước 2: Xác định khóa chính KHACH (MAKH) Bước 3: MaKH xác định được tên khách hàng, địa chỉ, ngày sinh , MAKH là duy nhất Khi thêm 1 khách hàng mới/ hoặc sửa thông tin khách hàng thì MAKH sẽ bị ràng buộc là không được trùng với MAKH đã có sẵn (+); Khi xóa một khách hàng thì sẽ không có ràng buộc nào cả Bước 4: Với r là một quan hệ của Khach ta có ràng buộc toàn vẹn liên bộ sau 26
  33. Bước 5: xác định khóa ngoại của LĐ CSDL trên DATHANG (MaKH) Với r, s lần lượt là một quan hệ của Dathang, Khach ta có ràng buộc toàn vẹn sau 3.2.3 Ràng buộc toàn vẹn về miền giá trị Ràng buộc toàn vẹn có liên quan đến miền giá trị của các thuộc tính trong một quan hệ. Ràng buộc này thường gặp. Một số hệ quản trị CSDL đã tự động kiểm tra một số ràng buộc loại này. 3.2.4 Ràng buộc toàn vẹn liên thuộc tính Ràng buộc toàn vẹn liên thuộc tính là mối liên hệ giữa các thuộc tính trong một lược đồ quan hệ. Ví dụ: Với r là một quan hệ của Hoadon ta có ràng buộc toàn vẹn sau Bước 1: LĐQLH hóa đơn gồm có các thuộc tính: SoHD, NgayLap, Ngayxuat(ngày xuất hàng), LoaiPhieu,TriGiaHD Bước 2: TriGiaHD phải có giá trị là số dương, Ngày lập hóa đơn trước hoặc trùng với ngày xuất hóa đơn. Bước 3: TriGiaHD >0, NgayLap <= NgayXuat Bước 4: Bảng ràng buộc toàn vẹn về miền giá trị Bước 5: Bảng ràng buộc toàn vẹn liên thuộc tính 3.2.5 Ràng buộc toàn vẹn liên thuộc tính liên quan hệ Ràng buộc loại này là mối liên hệ giữa các thuộc tính trong nhiều lược đồ quan hệ. Ví dụ: Với r, s lần lượt là quan hệ của Dathang, Hoadon ta có ràng buộc toàn vẹn sau 27
  34. Bước 1: LĐQLH HoaDon gồm có các thuộc tính: SoHD, NgayLap, Ngayxuat(ngày xuất hàng), LoaiPhieu,TriGiaHD LĐQH DatHang gồm các thuộc tính: SoHD, MaHang, NgayDH, SoLuong, Dongia Bước 2: SoHD là khóa chính của bảng HoaDon và là khóa ngoại của bảng DatHang Bước 3: Nếu HoaDon.SoHD = DatHang.SoHD thì HoaDon.NgayXuat >=DatHang.NgayDH Bước 4: Bảng ràng buộc toàn vẹn liên thuộc tính liên quan hệ 28
  35. BÀI TẬP CHƯƠNG 3 1/ Hãy tìm các ràng buộc toàn vẹn có trong CSDL cho các bài tập được liệt kê trong chương 3. 2/ QUẢN LÝ THI TỐT NGHIỆP PTCS Một phòng giáo dục huyện muốn lập một hệ thống thông tin để quản lý việc làm thi tốt nghiệp phổ thông cơ sở. Công việc làm thi được tổ chức như sau: Lãnh đạo phòng giáo dục thành lập nhiều hội đồng thi (mỗi hội đồng thi gồm một trường hoặc một số trường gần nhau). Mỗi hội đồng thi có một mã số duy nhất (MAHĐT), một mã số hội đồng thi xác định tên hội đồng thi(TENHĐT), họ tên chủ tịch hội đồng(TENCT), địa chỉ (ĐCHĐT),điện thoại(ĐTHĐT). Mỗi hội đồng thi được bố trí cho một số phòng thi, mỗi phòng thi có một số hiệu phòng(SOPT) duy nhất, một phòng thi xác định địa chỉ phòng thi (ĐCPT). Số hiệu phòng thi được đánh số khác nhau ở tất cả các hội đồng thi. Giáo viên của các trường trực thuộc phòng được điều động đến các hội đồng để coi thi, mỗi trường có thể có hoặc không có thí sinh dự thi, mỗi trường có một mã trường duy nhất (MATR), mỗi mã trường xác định một tên trường(TENTR),địa chỉ (ĐCTR), loại hình đào tạo (LHĐT) (Công lập, chuyên, bán công, dân lập, nội trú, ). Giáo viên củamột trường có thể làm việc tại nhiều hội đồng thi. Một giáo viên có một mã giáo viên(MAGV), một mã giáo viên xác định tên giáo viên (TENGV), chuyên môn giảng dạy (CHUYENMON), chức danh trong hội đồng thi(CHUCDANH) Các thí sinh dự thi có một số báo danh duy nhất(SOBD), mỗi số báo danh xác định tên thí sinh(TENTS), ngày sinh (NGSINH), giới tính (PHAI), mỗi thí sinh được xếp thi tại một phòng thi nhất định cho tất cả các môn, mỗi thí sinh có thể có chứng chỉ nghề (CCNGHE) hoặc không (thuộc tính CCNGHE kiểu chuỗi, CCNGHE=”x” nếu thí sinh có chứng chỉ nghề và CCNGHE bằng rỗng nếu thí sinh không có chứng chỉ nghề).Thí sinh của cùng một trường chỉ dự thi tại một hội đồng thi. Mỗi môn thi có một mã môn thi duy nhất(MAMT),mỗi mã môn thi xác định tên môn thi(TENMT). Giả sử toàn bộ các thí sinh đều thi chung một số môn do sở giáo dục quy định. Mỗi môn thi được tổ chức trong một buổi của một ngày nào đó. Ứng với mỗi môn thi một thí sinh có một điểm thi duy nhất(ĐIEMTHI) Dựa vào phân tích ở trên, giả sử ta có lược đồ CSDL sau: Q1: HĐ(MAHĐT,TENHĐT, TENCT, ĐCHĐT,ĐTHĐT) Q2: PT(SOPT,ĐCPT,MAHĐT) Q3: TS(SOBD, TENTS,NGSINH,PHAI,CCNGHE, MATR,SOPT) Q4: MT(MAMT,TENMT,BUOI,NGAY) Q5: GV(MAGV,TENGV,CHUYENMON,CHUCDANH,MAHĐT,MATR) Q6: TR(MATR,TENTR,ĐCTR,LHĐT) Q7: KQ(SOBD,MAMT,ĐIEMTHI) Yêu cầu: a) Hãy xác định khóa cho từng lược đồ quan hệ. b) Tìm tất cả các ràng buộc toàn vẹn có trong CSDL trên. 29
  36. c) Dựa vào lược đồ CSDL đã thành lập, hãy thực hiện các câu hỏi sau đây bằng ngôn ngữ đại số quan hệ. 1. Danh sách các thí sinh thi tại phòng thi có số hiệu phòng thi (SOPT) là “100”. Yêu cầu các thông tin:SOBD,TENTS,NGSINH,TENTR 2. Kết quả của môn thi có mã môn thi (MAMT) là “T” của tất cả các thí sinh có mã trường(MATR) là “NTMK”, kết quả được sắp theo chiều giảm dần của điểm thi(ĐIEMTHI). Yêu cầu các thông tin:SOBD,TENTS, ĐIEMTHI 3. Kết quả thi của một học sinh có SOBD là MK01. Yêu cầu : TENMT,ĐIEMTHI 4. Tổng số thí sinh có chứng chỉ nghề(CCNGHE) của mỗi trường, thông tin cần được sắp theo chiều tăng dần của TENTR. Yêu cầu các thông tin: MATR, TENTR, SOLUONGCC oOo 30
  37. CHƯƠNG 4: PHỤ THUỘC HÀM MỤC TIÊU: Sau khi học xong bài này người học có khả năng: - Trình bày được các khái niệm phụ thuộc hàm; - Trình bày được thuật toán Satifies, hệ luật dẫn Armstrong; - Trình bày được cách mô tả các phụ thuộc hàm để ứng dụng vào các bài toán tìm khóa, tìm phủ tối thiểu và chuẩn hóa cơ sơ dữ liệu; - Tìm được bao đóng, tập con, tập phụ thuộc hàm và phủ tối tiểu của phụ thuộc hàm - Nghiêm túc, tỉ mỉ trong việc học và làm bài tập. NỘI DUNG BÀI HỌC 4.1 Khái niệm phụ thuộc hàm 4.1.1. Định nghĩa phụ thuộc hàm a. Giới thiệu Cho quan hệ phanCong sau: Quan hệ phanCong diễn tả phi công nào lái máy bay nào và máybay khởi hành vào thời gian nào. Không phải sự phối hợp bất kỳ nào giữa phi công, máy bay và ngày giờ khởi hành cũng đều được chấp nhận mà chúng có các điều kiện ràng buộc qui định sau: + Mỗi máy bay có một giờ khởi hành duy nhất. + Nếu biết phi công, biết ngày giờ khởi hành thì biết được máy bay do phi công ấy lái. + Nếu biết máy bay, biết ngày khởi hành thì biết phi công lái chuyến bay ấy. Các ràng buộc này là các ví dụ về phụ thuộc hàm và được phát biểu lại như sau: + MAYBAY xác định GIOKH + {PHICONG,NGAYKH,GIOKH} xác định MAYBAY + {MAYBAY,NGAYKH} xác định PHICONG hay + GIOKH phụ thuộc hàm vào MAYBAY + MABAY phụ thuộc hàm vào {PHICONG,NGAYKH,GIOKH} + PHICONG phụ thuộc hàm vào {MAYBAY,NGAYKH} và được ký hiệu như sau: + {MAYBAY} GIOKH + {PHICONG,NGAYKH,GIOKH} MABAY + {MAYBAY,NGAYKH} PHICONG Trong ký hiệu trên ta đã ký hiệu MAYBAY thay cho {MAYBAY}. 31
  38. b. Định nghĩa phụ thuộc hàm Q(A1, A2, , An) là lược đồ quan hệ. X, Y là hai tập con của Q+ = {A1, A2, , An}. r là quan hệ trên Q. t1,t2 là hai bộ bất kỳ của r. (Ta nói X xác định Y hay Y phụ thuộc hàm vào X (X functional determines Y, Y functional dependent on X) Tính chất: + phụ thuộc hàm X ∅ đúng với mọi quan hệ r + phụ thuộc hàm ∅ Y chỉ đúng trên quan hệ r có cùng giá trị trên Y. Ví dụ: Quan hệ sau thỏa mãn phụ thuộc hàm ∅ GIOKH phanCong (PHICONG, MAYBAY, NGAYKH, GIOKH) Cushing 83 9/8 10:15a Cushing 116 10/8 10:15a Clark 281 8/8 10:15a Clark 301 12/8 10:15a Clark 83 11/8 10:15a Chin 83 13/8 10:15a Chin 116 12/8 10:15a Copely 281 9/8 10:15a Copely 281 13/8 10:15a Copely 412 15/8 10:15a trên thực tế không có quan hệ r nào thỏa tính chất trên nên từ đây về sau nếu không nói rõ thì với một quan hệ r bất kỳ ta luôn xem phụ thuộc hàm ∅ Y luôn luôn không thỏa trên r. 4.1.2. Phụ thuộc hàm hiển nhiên Hệ quả: Chứng minh: Giả sử t1.X = t2.X do X  Y nên t1.Y = t2.Y theo định nghĩa suy ra X Y Trong trường hợp này X Y được gọi là phụ thuộc hàm hiển nhiên. Ví dụ phụ thuộc hàm X X là phụ thuộc hàm hiển nhiên. Vậy với r là quan hệ bất kỳ, F là tập phụ thuộc hàm thỏa trên r, ta luôn có F {các phụ thuộc hàm hiển nhiên} 4.1.3. Thuật toán Satifies Ý nghĩa: Xác định phụ thuộc hàm theo thuật toán Satifies (thỏa mãn) Thuật toán: Cho quan hệ r và X, Y là hai tập con của Q+. Thuật toán SATIFIES sẽ trả về trị true nếu X Y ngược lại là false Trình tự xác định phụ thuộc hàm giữa X và Y theo SATIFIES Bước 1: Xác định dữ liệu vào/ra Vào: quan hệ r và hai tập con X,Y ra: true nếu X Y, ngược lại là false SATIFIES(r,X,Y) Bước 2: Sắp xếp các bộ trên quan hệ X theo r. 32
  39. Sắp các bộ của quan hệ r theo X để các giá trị giống nhau trên X nhóm lại với nhau Bước 3. So sánh bộ X & Y trả về kết quả Nếu tập các bộ cùng giá trị trên X cho các giá trị trên Y giống nhau thì trả về true ngược lại là False VD: SATIFIES(phanCong,MAYBAY,GIOKH) cho kết quả là true nghĩa là MAYBAY GIOKH VD: SATIFIES(phanCong,GIOKH,MAYBAY) cho kết quả là false nghĩa là không có phụ thuộc hàm GIOKH MAYBAY 4.1.4 Các phụ thuộc hàm có thể có a. Cách tìm tất cả tập con của Q+ Lược đồ quan hệ Phancong (PHICONG,MAYBAY,NGAYKH,GIOKH)có tập thuộc tính Phancong+={PHICONG,MAYBAY,NGAYKH,GIOKH}và tất cả các tập con có thể có của Phancong+ được cho bởi bảng sau: 33
  40. Số tập con có thể có của Q+= {A1,A2, ,An} là 2n b. Cách tìm tất cả các phụ thuộc hàm có thể có của Q Ứng với mỗi tập con của Phancong+ cho 2n = 24 = 16 phụ thuộc hàm có thể có. Số phụ thuộc hàm có thể có là 24 * 24 = 16 * 16 = 256 Số tập con có thể có của Q+= {A1,A2, ,An} là 2n b. Cách tìm tất cả các phụ thuộc hàm có thể có của Q Ứng với mỗi tập con của Phancong+ cho 2n = 24 = 16 phụ thuộc hàm có thể có. Số phụ thuộc hàm có thể có là 24 * 24 = 16 * 16 = 256 32
  41. Số phụ thuộc hàm có thể có của Q(A1,A2, ,An)là 2n x 2n=22n b4.2. Hệ luật dẫn Armstrong (Armstrong inference rule) Người ta thường dùng F để chỉ tập các phụ thuộc hàm của lược đồ quan hệ Q. Ta có thể đánh số các phụ thuộc hàm của F là f1, f2, , fm. Quy ước rằng chỉ cần mô tả các phụ thuộc hàm không hiển nhiên trong tập F (các phụ thuộc hàm hiển nhiên được ngầm hiểu là đã có trong F). 4.2.1 Phụ thuộc hàm được suy diễn logic từ F Nói rằng phụ thuộc hàm X Y được suy diễn logic từ F nếu một quan hệ r thỏa mãn tất cả các phụ thuộc hàm của F thì cũng thỏa phụ thuộc hàm X Y. Ký hiệu F|= X Y. Bao đóng của F ký hiệu F+ là tập tất cả các phụ thuộc hàm được suy diễn logic từ F. Các tính chất của tập F+ 1. Tính phản xạ: Với mọi tập phụ thuộc hàm F+ ta luôn luôn có F F+ 2. Tính đơn điệu: Nếu F G thì F+ G+ 3. Tính lũy đẳng: Với mọi tập phụ thuộc hàm F ta luôn luôn có (F+)+ = F+. Gọi G là tập tất cả các phụ thuộc hàm có thể có của r, phần phụ của F ký hiệu F- =G - F+ 4.2.2 Hệ luật dẫn Armstrong Hệ luật dẫn l một pht biểu cho biết nếu một quan hệ r thỏa mn một vi phụ thuộc hm thì nĩ phải thỏa mn phụ thuộc hm khc. 35
  42. Với X, Y, Z, W l tập con của Q+. r l quan hệ bất kỳ của Q. Ta cĩ 6 luật dẫn sau: 1. Luật phản xạ (reflexive rule): X X 2. Luật thm vo (augmentation rule): Cho X Y XZ Y 3. Luật hợp (union rule): Cho X Y, X Z X YZ 4. Luật phn r (decomposition rule): Cho X YZ X Y 5. Luật bắc cầu (transitive rule): Cho X Y, Y Z X Z 6. Luật bắc cầu giả (pseudo transitive rule): Cho X Y, YZ W XZ W Hệ tin đề Armstrong (Armstrong’s Axioms) gồm 3 luật: (1), (2) v (5) a. Hệ luật dẫn Armstrong l đúng Nĩi rằng X Y l phụ thuộc hm được suy diễn nhờ vo luật dẫn Armstrong nếu tồn tại cc tập phụ thuộc hm F0  F1  Fn sao cho X Y Fn với F0, F1 , , Fn lần lượt được hình thnh thỏa phương php sau: Bước 1: F0 = F Bươc 2: chọn một số phụ thuộc hm trong Fi p dụng hệ luật dẫn Armstrong để thu được một số phụ thuộc hm mới. Đặt Fi+1= Fi  {cc phụ thuộc hm mới} Ví dụ: Cho F = {AB C,C B,BC A}thì cĩ F0 F1 F2 sao cho C A F2 F0= {AB C,C B, BC A}Áp dụng luật hợp cho C B v C C F1= {AB C,C B, BC A, C BC}p dụng luật bắc cầu. F2= {AB C,C B, BC A, C BC, C A} Hệ quả: Hệ luật dẫn Armstrong là đúng nghĩa là nếu F là tập các phụ thuộc hàm đúng trên quan hệ r và X Y là một phụ thuộc hàm được suy diễn từ F nhờ hệ luật dẫn Armstrong thì X Y đúng trên quan hệ r. Vậy X Y là phụ thuộc hàm được suy diễn logic từ F Phần tiếp theo chúng ta sẽ chứng minh hệ luật dẫn Armstrong là đầy đủ, nghĩa là mọi phụ thuộc hàm X Y được suy diễn logic từ F sẽ được suy diễn từ F nhờ hệ luật dẫn Armstrong. b. Bao đóng của tập thuộc tính X (closures of attribute sets) (a) Định nghĩa Q là lược đồ quan hệ, r là quan hệ tương ứng, F là tập các phụ thuộc hàm trong Q. + X,Ai là các tập con của Q . Bao đóng của tập thuộc tính X đối với F ký hiệu là X+ (hoặc X ) được định nghĩa F như sau: + X =Ai với X Ai là phụ thuộc hàm được suy diễn từ F nhờ hệ tiên đề Armstrong Tính chất: + bao đóng của Q là Q+ (b) Các tính chất của bao đóng Nếu X,Y là các tập con của tập thuộc tính Q+ thì ta có các tính chất sau đây: 1. Tính phản xạ: X  X+ 2. Tính đơn điệu: Nếu X Y thì X+ Y+ 3. Tính lũy đẳng:X++ = X+ 36
  43. 4. (XY)+  X+Y+ 5. (X+Y)+ = (XY+)+ = (X+Y+)+ 6. X Y Y+  X+ 7. X X+ và X+ X 8. X+ = Y+ X Y vàY X (c) Các bước tìm bao đóng (Thuật toán tìm bao đóng) Tính liên tiếp tập các tập thuộc tính X0,X1,X2, theo phương pháp sau: Bước 1: X0 = X Bước 2: lần lượt xét các phụ thuộc hàm của F Nếu Y Z có Y Xi thì Xi+1= Xi  Z Loại phụ thuộc hàm Y Z khỏi F Bước 3: Nếu ở bước 2 không tính được Xi+1 thì Xi chính là bao đóng của X Ngược lại lặp lại bước 2 Ví dụ 1: Cho lược đồ quan hệ Q(ABCDEGH)và tập phụ thuộc hàm F F = { f1: B A f2: DA CE f3: D H f4: GH C f5: AC D } Tìm bao đóng của các tập X = {AC} dựa trên F. Giải: Bước 1: X0 = AC + Bước 2 : Do f1, f2, f3, f4 không thỏa. f5 thỏa vì X AC X1 = AC D = ACD Lập lại bước 2: f1 không thỏa, f2 thỏa vì X1 AD: X2 = ACD CE = ACDE f3 thỏa vì X2 D X3 = ACDE H = ACDEH f4 không thỏa, f5 không xét vì đã thỏa Lập lại bước 2: f2,f3 không xét vì đã thỏa, f1,f4 không thỏa, f5 không xét vì đã thỏa. Trong + bước này X3 không thay đổi => X =X3={ACDEH} là bao đóng của X Ví du 2: Q(A,B,C,D,E,G) F = { f1: A C; f2: A EG; f3: B D; f4: G E} X = {A,B}; Y = {C,G,D} Kết quả: X+= {ABCDEG} Y+ = {CGDE} (d) Định lý + Thuật toán tìm bao đóng cho kết quả Xi= X c. Hệ luật dẫn Armstrong là đầy đủ Định lý 37
  44. Hệ luật dẫn Armstrong là đầy đủ nghĩa là mọi phụ thuộc hàm X Y được suy diễn logic từ F sẽ được suy diễn từ F nhờ hệ luật dẫn Armstrong. Hệ quả: Bao đóng của tập thuộc tính X đối với F là: + X = Aivới X Ai là phụ thuộc hàm được suy diễn logic từ F Tính chất X Y F+ Y  X+ Bài toán thành viên Nói rằng X Y là thành viên của F nếu X Y F+ Một vấn đề quan trọng khi nghiên cứu lý thuyết CSDL là khi cho trước tập các phụ thuộc hàm F và một phụ thuộc hàm X Y, làm thế nào để biết X Y có thuộc F+ hay không bài toán này được gọi là bài toán thành viên. Để trả lời câu hỏi này ta có thể tính F+ rồi xác định xem X Y có thuộc F+ hay không. Việc tính F+ là một công việc đòi hỏi thời gian và công sức. Tuy nhiên, thay vì tính F+ chúng ta có thể dùng thuật toán sau để xác định X Ycó là thành viên của F hay không. Thuật toán này sử dụng tính chất vừa chứng minh trên. * Trình tự thực hiện xác định f = X Y có là thành viên của F hay không Bước 1: tính X+ Bước 2: so sánh X+ với Y nếu X+  Y thì ta khẳng định X Y là thành viên của F 4.2.3 Trình tự thực hiện tìm bao đóng của tập phụ thuộc hàm - Thuật toán tìm F+ a. Thuật toán cơ bản Để tính bao đóng F+ của tập các phụ thuộc hàm F ta thực hiện các bước sau: Bước 1: Tìm tất cả tập con của Q+ Bước 2: Tìm tất cả các phụ thuộc hàm có thể có của Q. Bước 3: Tìm bao đóng của tất cả tập con của Q. Bước 4: Dựa vào bao đóng của tất cả các tập con đã tìm để xác định phụ thuộc hàm nào thuộc F+ Ví dụ 3: Cho lược đồ quan hệ Q(A,B,C) F = {AB C,C B} là tập phụ thuộc hàm trên Q. F+ được tính lần lượt theo các bước trên là như sau: Bước 1: Các tập con của Q+ 38
  45. Bước 2: các phụ thuộc hàm có thể có của F (không kể các phụ thuộc hàm hiển nhiên) Bước 3: bao đóng của các tập con của Q đối với F Bước 4: F = {AB C,C B} suy ra: F+={AB C,AB AC,AB BC,AB ABC,C B,C BC,AC B,AC AB,AC BC,AC ABC} b. Thuật toán cải tiến Dựa vào thuật toán cơ bản trên, ta nhận thấy có thể tính F+ theo các bước sau: Bước 1: Tìm tất cả tập con của Q+ Bước 2: Tìm bao đóng của tất cả tập con của Q+. Bước 3: Dựa vào bao đóng của các tập con đã tìm để suy ra các phụ thuộc hàm thuộc F+ Ví dụ: bao đóng A+= A chỉ gồm các phụ thuộc hàm hiển nhiên bao đóng {AB}+= ABC cho các phụ thuộc hàm không hiển nhiên sau AB C,AB AC,AB BC,AB ABC (Tìm tất cả các tập con của {ABC}rồi bỏ các tập con của {AB}) Các tập con của {ABC}là: Ø,{A},{B},{AB},{C},{AC},{BC},{ABC} Bỏ các tập con của {AB}là: Ø,{A},{B},{AB},{C},{AC},{BC},{ABC} Các tập còn lại chính là vế phải của phụ thuộc hàm có vế trái là AB 39
  46. BÀI TẬP CHƯƠNG 4 1. Cho quan hệ sau: Phụ thuộc hàm nào sau đây thỏa r: A D,AB D,C BDE,E A,A E 2/ Cho Q+={ABCD} a) Tìm tất các các tập con của Q b) Tìm tất cả các phụ thuộc hàm có thể có của Q (không liệt kê phụ thuộc hàm hiển nhiên) 3/ Tìm bao đóng F+ của quan hệ phanCong(PHICONG,MAYBAY,NGAYKH,GIOKH) 4/ Cho F= {AB C,B D,CD E,CE GH,G A} a) Hãy chứng tỏ phụ thuộc hàm AB E,AB G được suy diễn từ F nhờ luật dẫn Armstrong b) Tìm bao đóng của AB (với bài toán không nói gì về lược đồ quan hệ Q ta ngầm hiểu Q+ là tập thuộc tính có trong F nghĩa là Q+={ABCDEGH}) 5/ Cho F = {A D,AB DE,CE G,E H}. Hãy tìm bao đóng của AB. 6/ Cho F={AB E,AG I,BE I,E G,GI H}. a) Hãy chứng tỏ phụ thuộc hàm AB GH được suy diễn từ F nhờ luật dẫn Armstrong b) Tìm bao đóng của {AB} 7/ Cho F={A D,AB E,BI E,CD I,E C} tìm bao đóng của {AE}+={ACDEI} 40
  47. CHƯƠNG 5: PHỦ CỦA TẬP PHỤ THUỘC HÀM MỤC TIÊU - Trình bày được các khái niệm về phủ của phụ thuộc hàm, khóa của lược đồ quan hệ; - Trình bày được cách tìm tập phụ thuộc hàm tối thiểu trong bài toán; - Tìm được đầy đủ và chính xác các khóa của các lược đồ cơ sở dữ liệu và phủ tối thiểu của tập phụ thuộc hàm - Nghiêm túc, tỉ mỉ trong việc học và làm bài tập. NỘI DUNG BÀI HỌC 5.1 Định nghĩa a. Định nghĩa Nói rằng hai tập phụ thuộc hàm F và G là tương đương (Equivalent) nếu F+= G+ ký hiệu F=G. Ta nói F phủ G nếu F+  G+ b. Trình tự thực hiện xác định F và G có tương đương không? Thuật toán xác định F và G có tương đương không Bước 1: Với mỗi phụ thuộc hàm X Y của F ta xác định xem X Y có là thành viên của G không Bước 2: Với mỗi phụ thuộc hàm X Y của G ta xác định xem X Y có là thành viên của F không Nếu cả hai bước trên đều đúng thì F =G Vd Cho lược đồ quan hệ Q(ABCDE) hai tập phụ thuộc hàm: F={A BC,A D,CD E}và G = {A BCE,A ABD,CD E} a) F có tương đương với G không? b) F có tương đương với G’={A BCDE} không? Giải: + + + a) Ta có AG ABCDE trong G có A BC và A D F  G F G+ (1). + + + + AF ABCDE trong F có A BCE và A ABD F G F G+ (2) (1) và (2) F+= G+ F = G. b) Do (CD)G CD G' không chứa phụ thuộc hàm CD E F không tương đương với G’ 5.2 Phủ tối thiểu của một tập phụ thuộc hàm 5.2.1 Phụ thuộc hàm có vế trái dư thừa a. Khái niệm F là tập các phụ thuộc hàm trên lược đồ quan hệ Q, Z là tập thuộc tính, Z Y F. Nói rằng phụ thuộc hàm Z Y có vế trái dư thừa (phụ thuộc không đầy đủ) nếu có một A Z sao cho: F =F-{Z Y}{(Z-A) Y} Ngược lại Z Y là phụ thuộc hàm có vế trái không dư thừa hay Y phụ thuộc hàm đầy đủ vào Z hay phụ thuộc hàm đầy đủ. b. Trình tự thực hiện loại các phụ thuộc hàm có vế trái dư thừa ra khỏi F 41
  48. Ta nói F là tập phụ thuộc hàm có vế trái không dư thừa nếu F không chứa phụ thuộc hàm có vế trái dư thừa. Thuật toán loại khỏi F các phụ thuộc hàm có vế trái dư thừa. Bước 1: lần lượt thực hiện bước 2 cho các phụ thuộc hàm X Y của F Bước 2: Với mọi tập con thật sự X’ Ø của X. Nếu X' Y F+ thì thay X Y trong F bằng X' Y thực hiện lại bước 2 c. Thực hành 1. Q(A,B,C) F={AB C; B C} F =F-{AB C}{(AB-A) C}={B C} AB C là phụ thuộc hàm không đầy đủ B C là phụ thuộc hàm đầy đủ Chú ý: phụ thuộc hàm có vế trái chứa một thuộc tính là phụ thuộc hàm đầy đủ. 2. cho tập phụ thuộc hàm F = {A BC,B C,AB D}thì phụ thuộc hàm AB D có vế trái dư thừa B vì: F =F – {AB D}{A D} ={A BC,B C,A D} 3. Ở ví dụ 3 phụ thuộc hàm AB D có A+=ABCD A D F+. Trong F ta thay AB D bằng 5.2.2 Phụ thuộc hàm có vế phải một thuộc tính Mỗi tập phụ thuộc hàm F đều tương đương với một tập phụ thuộc hàm G mà vế phải của các phụ thuộc hàm trong G chỉ gồm một thuộc tính. Ví dụ 4: cho F = {A BC,B C,AB D} ta suy ra F ={A B, A C ,B C,AB D} = G G được gọi là tập phụ thuộc hàm có vế phải một thuộc tính. 5.2.3 Tập phụ thuộc hàm không dư thừa a. Khái niệm: Nói rằng F là tập phụ thuộc hàm không dư thừa nếu không tồn tại F’ F sao cho F’=F. Ngược lại F là tập phụ thuộc hàm dư thừa. Ví dụ: cho F = {A BC, B D, AB D}thì F dư thừa vì F =F’= {A BC, B D} b. Trình tự loại các phụ thuộc hàm dư thừa ra khỏi F Thuật toán loại khỏi F các phụ thuộc hàm dư thừa: Bước 1: Lần lượt xét các phụ thuộc hàm X Y của F Bước 2: nếu X Y là thành viên của F - {X Y} thì loại X Y khỏi F Bước 3: thực hiện bước 2 cho các phụ thuộc hàm tiếp theo của F c. Thực hành Loại phụ thuộc hàm dư thừa ra khỏi F = {A BC, B D, AB D} Giải: F = {A BC, B D, AB D} thì F dư thừa vì F =F’= {A BC, B D} 5.2.4 Tập phụ thuộc hàm tối thiểu a. Định nghĩa F được gọi là một tập phụ thuộc hàm tối thiểu (hay phủ tối thiểu) nếu F thỏa đồng thời ba điều kiện sau: 1. F là tập phụ thuộc hàm có vế trái không dư thừa 42
  49. 2. F là tập phụ thuộc hàm có vế phải một thuộc tính. 3. F là tập phụ thuộc hàm không dư thừa b. Trình tự thực hiện tìm phủ tối thiểu của một tập phụ thuộc hàm Thuật toán tìm phủ tối thiểu của một tập phụ thuộc hàm Bước 1: loại khỏi F các phụ thuộc hàm có vế trái dư thừa. Bước 2: Tách các phụ thuộc hàm có vế phải trên một thuộc tính thành các phụ thuộc hàm có vế phải một thuộc tính. Bước 3: loại khỏi F các phụ thuộc hàm dư thừa. Chú ý: Theo thuật toán trên, từ một tập phụ thuộc hàm F luôn tìm được ít nhất một phủ tối thiểu Ftt để F=Ftt và nếu thứ tự loại các phụ thuộc hàm trong tập F là khác nhau thì có thể sẽ thu được những phủ tối thiểu khác nhau. c. Thực hành 1. Cho lược đồ quan hệ Q(A,B,C,D) và tập phụ thuộc F như sau: F={AB CD,B C,C D} Hãy tính phủ tối thiểu của F. Giải: Bước 1: AB CD là phụ thuộc hàm có vế trái dư thừa? B CD F+? trả lời: B+=BCD B CD F+ Vậy AB CDlà phụ thuộc hàm có vế trái dư thừa A kết quả của bước 1 là: F={B CD;B C;C D} Bước 2: kết quả của bước 2 là: F={B D; B C;C D}=F1tt Bước 3: trong F1tt, B C là phụ thuộc hàm dư thừa? + B C G ? với G = F1tt- {B C}={B D;C D} + BG =BD B C G trong F1tt B C không dư thừa. trong F1tt,B D là phụ thuộc hàm dư thừa? + B D G ? với G = F1tt- {B D}={B C;C D} + = BCD B D G trong F1tt,B D dư thừa. kết quả của bước 3 cho phủ tối thiểu: F={B C;C D}=Ftt 2. Cho lược đồ quan hệ Q(MSCD,MSSV,CD,HG) và tập phụ thuộc F như sau: F = { MSCD CD; CD MSCD; CD,MSSV HG; MSCD,HG MSSV; CD,HG MSSV; MSCD,MSSV HG} Hãy tìm phủ tối thiểu của F kết quả: Ftt= {MSCD CD; CD MSCD; CD,HG MSSV; MSCD,MSSV HG} 43
  50. 5.3 Khóa của lược đồ quan hệ a. Định nghĩa Q(A1,A2, ,An)là lược đồ quan hệ. Q+là tập thuộc tính của Q. F là tập phụ thuộc hàm trên Q. K là tập con của Q+ Nói rằng K là một khóa của Q nếu: 1. K+ = Q+ và 2. Không tồn tại K'  Ksao cho K’+= Q+ Tập thuộc tính S được gọi là siêu khóa nếu S  K Thuộc tính A được gọi là thuộc tính khóa nếu A Kvới K là khóa bất kỳ của Q. Ngược lại A được gọi là thuộc tính không khóa. Một lược đồ quan hệ có thể có nhiều khóa và tập thuộc tính không khóa cũng có thể bằng rỗng. (Khi thiết kế một hệ thống thông tin, thì việc lập lược đồ cơ sở dữ liệu đạt đến một tiêu chuẩn nào đó là một việc làm quan trọng. Việc xác định chuẩn cho một lược đồ quan hệ có liên quan mật thiết với thuật toán tìm khóa). Thuật toán tìm một khóa của một lược đồ quan hệ Q Bước 1: gán K = Q+ Bước 2: A là một thuộc tính của K, đặt K’ = K -A.Nếu K’+= Q+ thì gán K = K' thực hiện lại bước 2 Nếu muốn tìm các khóa khác (nếu có) của lược đồ quan hệ, ta có thể thay đổithứ tự loại bỏ các phần tử của K. Ví dụ 7: Q(A,B,C,D,E,G,H,I)F={AC B;BI ACD;ABC D;H I;ACE BCG;CG AE } Tìm K Lần lượt loại các thuộc tính trong K theo thứ tự sau: A, B, D, E, I Ta được một khóa là của lược đồ quan hệ là {C,G,H} (Lưu ý là thuật toán này chỉ nên sử dụng trong trường hợp chỉ cần tìm một khóa). b. Thuật toán tìm tất cả các khóa Bước 1: Xác định tất cả các tập con khác rỗng của Q+. Kết quả tìm được giả sử là các tập n thuộc tính X1, X2, ,X2 -1 Bước 2: Tìm bao đóng của các Xi + Bước 3: Siêu khóa là các Xi có bao đóng đúng bằng Q . Giả sử ta đã có các siêu khóa là S=(S1,S2, ,Sm} Bước 4: Xây dựng tập chứa tất cả các khóa của Q từ tập S bằng cách xét mọi Si, Sj con của S (i j), nếu Si Sjthì ta loại Sj (i,j=1 n), kết quả còn lại của S chính là tập tất cả các khóa cần tìm. c. Thực hành Tìm tất cả các khóa của lược đồ quan hệ và tập phụ thuộc hàm như sau: Q(C,S,Z); F = {f1:CS Z; f2:Z C} 44
  51. Vậy lược đồ quan hệ Q có hai khóa là: {C,S}và {S,Z} Thuật toán trên thì dễ hiểu, dễ cài đặt, tuy nhiên nếu với n khá lớn thì phép duyệt để tìm ra tập tất cả các tập con của tập Q+ là không hiệu quả. Do vậy cần thu hẹp không gian duyệt. Chúng ta sẽ nghiên cứu thuật toán cải tiến theo hướng giảm số thuộctính của tập cần duyệt tất cả các tập con. 5.4 Thuật toán cải tiến tìm khóa của LĐQH a. Khái niệm Trước khi đi vào thuật toán cải tiến, ta cần quan tâm một số khái niệm sau Tập thuộc tính nguồn (TN) chứa tất cả các thuộc tính có xuất hiện ở vế trái và không xuất hiện ở vế phải của các phụ thuộc hàm và các thuộc tính không xuất hiện ở cả vế trái lẫn vế phải của các phụ thuộc hàm. + Tập thuộc tính đích (TD) chứa tất cả các thuộc tính có xuất hiện ở vế phải và không xuất hiện ở vế trái của các phụ thuộc hàm. + Tập thuộc tính trung gian (TG) chứa tất cả các thuộc tính xuất hiện ở cả vế trái lẫn vế phải của các phụ thuộc hàm. Hệ quả: Nếu K là khóa của Qthì TN  K và TD K = Ø b. Trình tự thực hiện tìm tất cả các khóa Dữ liệu vào: Lược đồ quan hệ Q và tập phụ thuộc hàm F Dữ liệu ra: Tất cả các khóa của quan hệ Thuật toán tìm tất cả khóa của một lược đồ quan hệ Bước 1: tạo tập thuộc tính nguồn TN, tập thuộc tính trung gian TG Bước 2: if TG=Ø then lược đồ quan hệ chỉ có một khóa K K =TN kết thúc Ngược lại Qua bước 3 Bước 3: tìm tất cả các tập con Xi của tập trung gian TG Bước 4: tìm các siêu khóa Si bằng cách  Xi + + if (TN Xi) = Q then Si= TN Xi Bước 5: tìm khóa bằng cách loại bỏ các siêu khóa không tối tiểu Si,Sj S if Si Sjthen Loại Sj ra khỏi Tập siêu khóa S S còn lại chính là tập khóa cần tìm. c. Thực hành Tìm tất cả các khóa của lược đồ quan hệ và tập phụ thuộc hàm như sau: Q(C,S,Z); F = {f1:CS Z; f2:Z C} Giải Ap dụng thuật toán cải tiến ta có lời giải như sau: TN = {S}; TG = {C,Z} Gọi Xi là các tập con của tập TG: 45
  52. Kết quả quan hệ trên có hai khóa là : {S,C}và {S,Z} 46
  53. BÀI TẬP CHƯƠNG 5 1/ Chứng minh các tính chất sau: a) Tính cộng đầy đủ X Yvà Z W XZ YW b) Tính tích lũy X Y và Y ZW X YZW 2/ Cho G={AB C,A B,B C,A C}. F={AB C,A B,B C}có tương đương với G không? 3/ Cho lược đồ CSDL Kehoach(NGAY,GIO,PHONG,MONHOC,GIAOVIEN) F= {NGAY,GIO,PHONG MONHOC MONHOC,NGAY GIAOVIEN NGAY,GIO,PHONG GIAOVIEN MONHOC GIAOVIEN} a) Tính {NGAY,GIO,PHONG}+; {MONHOC}+ b) Tìm phủ tối thiểu của F c) Tìm tất cả các khóa của Kehoach 4/ Cho lược đồ CSDL Q(TENTAU,LOAITAU,MACHUYEN,LUONGHANG,BENCANG,NGAY) F= {TENTAU LOAITAU MACHUYEN TENTAU, LUONGHANG TENTAU,NGAY BENCANG, MACHUYEN} a) Hãy tìm tập phủ tối thiểu của F b) Tìm tất cả các khóa của Q 5/ Q(A,B,C,D,E,G) Cho F={AB C;C A;BC D;ACD B;D EG;BE C;CG BD;CE AG} X={B,D}, X+=? Y={C,G}, Y+=? 6/ cho lược đồ quan hệ Q và tập phụ thuộc hàm F a) F={AB E;AG I;BE I;E G;GI H}chứng minh rằng AB GH. b) F={AB C;B D;CD E;CE GH;G A}chứng minh rằng AB E; AB G 7/ Cho quan hệ r Trong các phụ thuộc hàm sau đây, PTH nào không thỏa A B; A C; B A; C D; D C; D A 8/ Hãy tìm tất cả các khóa cho lược đồ quan hệ sau: Q(BROKER,OFFICE,STOCK,QUANTITY,INVESTOR,DIVIDENT) F= {STOCK DIVIDENT INVESTOR BROKER INVESTOR,STOCK QUANTITY BROKER OFFICE } 9/ Xét lược đồ quan hệ và tập phụ thuộc dữ liệu: Q(C,T,H,R,S,G) f= {f1: C T; f2: HR C; f3: HT R; f4: CS G; f5: HS R} Tìm phủ tối thiểu của F 47
  54. 10/ Q(A,B,C,D,E,H) F={A E; C D; E DH} Chứng minh K={A,B,C}là khóa duy nhất của Q 11/ Q(A,B,C,D) F={AB C; D B; C ABD} Hãy tìm tất cả các khóa của Q 12/ Q(A,B,C,D,E,G) F={AB C;C A;BC D;ACD B;D EG;BE C;CG BD;CE G} Hãy tìm tất cả các khóa của Q. 13/ Xác định phủ tối thiểu của tập phụ thuộc hàm sau: a) Q(A,B,C,D,E,G), F={AB C;C A;BC D;ACD B;D EG;BE C;CG BD;CE AG} b) Q(A,B,C) F={A B,A C,B A,C A,B C} 14/ Xác định phủ tối thiểu của các tập phụ thuộc hàm sau: a) Q1(ABCDEGH) F1={A H,AB C,BC D;G B} b) Q2(ABCSXYZ) F2={S A;AX B;S B;BY C;CZ X} c) Q3(ABCDEGHIJ) F3={BG D;G J;AI C;CE H;BD G;JH A; D I } d) Q4(ABCDEGHIJ) F4={BH I;GC A;I J;AE G;D B;I H} 48
  55. CHƯƠNG 6: CHUẨN HÓA CƠ SỞ DỮ LIỆU MỤC TIÊU BÀI HỌC - Trình bày được các khái niệm về các dạng chuẩn của lược đồ quan hệ, các phép tách, kết nối bảo toàn dữ liệu; - Trình bày được cách thiết kế cơ sở dữ liệu bằng cách phân rã; - Thiết kế, chuẩn hóa một số lược đồ quan hệ cụ thể; - Nghiêm túc, tỉ mỉ trong việc học và làm bài tập. NỘI DUNG BÀI HỌC 6.1. Dạng chuẩn của lược đồ quan hệ Trong thực tế, một ứng dụng cụ thể có thể được thiết kế thành nhiều lược đồ cơ sở dữ liệu khác nhau, và tất nhiên chất lượng thiết kế của các lược đồ CSDL này cũng khácnhau. Chất lượng thiết kế của một lược đồ CSDL có thể được đánh giádựa trên nhiều tiêu chuẩn trong đó sự trùng lắp thông tin và chi phí kiểm tra các ràng buộc toàn vẹn là hai tiêu chuẩn quan trọng. Sau đây là một số tiêu chuẩn để đánh giá độ tốt/xấu của một lược đồ quan hệ. Trước tiên ta tìm hiểu một số khái niệm liên quan 6.1.1. Dạng chuẩn một Một lược đồ quan hệ Q ở dạng chuẩn 1 nếu toàn bộ các thuộc tính của mọi bộ đều mang giá trị đơn. Ví dụ 1: Xét quan hệ Quan hệ này không đạt chuẩn 1 vì các thuộc tính TENMONHOC, DIEMTHIcủa bộ thứ nhất không mang giá trị đơn (chẳng hạn sinh viên NGUYEN THI THUcó thuộc tính TENMONHOClà KY THUAT LAP TRINH, TOAN ROI RAC, CO SO DU LIEU). Ta hoàn toàn có thể đưa quan hệ trên về dạng chuẩn 1 như sau: Chú ý ràng khi xét các dạng chuẩn, nếu ta không nói gìthêm, ta hiểu dạng chuẩn đang xét ít nhất là đạt dạng chuẩn 1. 6.1.2. Dạng chuẩn hai a. Khái niệm Một lược đồ quan hệ Q ở dạng chuẩn 2 nếu Q đạt chuẩn 1 và mọi thuộc tính không khóa của Q đều phụ thuộc đầy đủ vào khóa. b. Trình tự thực hiện kiểm tra dạng chuẩn 2 Thuật toán kiểm tra dạng chuẩn 2 Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn 2 hay không đạt chuẩn 2. Bước 1: Tìm tất cả khóa của Q Bước 2: Với mỗi khóa K, tìm bao đóng của tất cả tập con thật sự S của K. Bước 3: Nếu có bao đóng S+ chứa thuộc tính không khóa thì Q không đạt chuẩn 2 Ngược lại thì Q đạt chuẩn 2 49
  56. Hệ quả: • Nếu Q đạt chuẩn 1 và tập thuộc tính không khóa của Q bằng rỗng thì Q đạt chuẩn 2 • Nếu tất cả khóa của quan hệ chỉ gồm một thuộc tính thì quan hệ đó ít nhất đạt chuẩn 2. c. Thực hành 1. Cho lược đồ quan hệ Q(A,B,C,D)và tập phụ thuộc hàm F={AB C; B D; BC A}. Hỏi Q có đạt chuẩn 2 không? Giải: TN={B}, TG={AC} Khóa là K1=AB và K2=BC. Ta thấy BK1, B D,D là thuộc tính không khóa thuộc tính không khóa không phụ thuộc đầy đủ vào khóa Q không đạt chuẩn 2. 2. Quan hệ sau đạt chuẩn 2 hay không? Q(G,M,V,N,H,P) F={G M; G N; G H; G P; M V; NHP M} Giải: TN={G} TG={M,N,H,P} Lược đồ quan hệ Q chỉ có một khóa và khóa chỉ có một thuộc tính nên mọi thuộc tính đều phụ thuộc đầy đủ vào khóa Q đạt chuẩn 2 3. Q(A,B,C,D,E,H) F={A E; C D; E DH} Giải: TN={ACB} TG={E} khóa của Q là K = {ABC}. C K, C D, D là thuộc tính không khóa D phụ thuộc không đầy đủ vào khóa nên Q không đạt chuẩn 2. 6.1.3. Dạng chuẩn ba a. Khái niệm Thuộc tính phụ thuộc bắc cầu Q là lược đồ quan hệ, X,Y là hai tập con của Q+, A là một thuộc tính. 50
  57. Nói rằng A phụ thuộc bắc cầu vào X nếu cả ba điều sau thỏa: + X Y,Y A + Y X + A XY Định nghĩa 1: Lược đồ quan hệ Q ở dạng chuẩn 3 nếu mọi phụ thuộc hàm X A F+ với A X đều có: • Hoặc X là siêu khóa • Hoặc A là thuộc tính khóa Định nghĩa 2: Lược đồ quan hệ Q ở dạng chuẩn 3 nếu mọi thuộc tính không khóa của Q đều không phụ thuộc bắc cầu vào một khóa bất kỳ của Q Hai định nghĩa trên là tương đương, tuy nhiên việc cài đặt thuật toán kiểm tra dạng chuẩn 3 theo định nghĩa 1 thì hiệu quả hơn nhiều vì không phải kiểm tra tính phụ thuộc bắc cầu. Hệ quả 1: Nếu Q đạt chuẩn 3 thì Q đạt chuẩn 2 Hệ quả 2: Nếu Q không có thuộc tính không khóa thì Q đạt chuẩn 3. Định lý: Q là lược đồ quan hệ F là tập các phụ thuộc hàm có vế phải một thuộc tính Q đạt chuẩn 3 nếu và chỉ nếu mọi phụ thuộc hàm X A F với A X đều có • Hoặc X là siêu khóa • Hoặc A là thuộc tính khóa b. Trình tự thực hiện kiểm tra dạng chuẩn 3 Thuật toán kiểm tra dạng chuẩn 3 Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn 3 hay không đạt chuẩn 3. Bước 1: Tìm tất cả khóa của Q Bước 2: Từ F tạo tập phụ thuộc hàm tương đương F1tt có vế phải một thuộc tính. Bước 3: Nếu mọi phụ thuộc hàm X A F1tt với A X đều có X là siêu khóa hoặc A là thuộc tính khoá thì Q đạt chuẩn 3 ngược lại Q không đạt chuẩn 3 c. Thực hành 1. Cho lược đồ quan hệ Q(A,B,C,D) F={AB C; D B; C ABD}. Hỏi Q có đạt chuẩn 3 không? Giải: TN=Ø TG={ABCD} 51
  58. K1 = {AB}; K2 = {AD}; K3={C} là các khóa mọi phụ thuộc hàm X A F đều có A là thuộc tính khóa. Vậy Q đạt chuẩn 3 2. Quan hệ sau đạt chuẩn 3. Q(N,G,P,M) F = {NGP M,M P} 6.1.4. Dạng chuẩn Boyce – Codd (Boyce-Codd Normal Form) a. Khái niệm Một quan hệ Q ở dạng chuẩn BC nếu mọi phụ thuộc hàm X A F+ với A X đều có X là siêu khóa. Hệ quả 1: Nếu Q đạt chuẩn BC thì Q đạt chuẩn 3 (hiển nhiên do định nghĩa) Hệ quả 2: Mỗi lược đồ có hai thuộc tính đều đạt chuẩn BC (xét phụ thuộc hàm có thể có của Q ) Định lý: Q là lược đồ quan hệ F là tập các phụ thuộc hàm có vế phải một thuộc tính. Q đạt chuẩn BC nếu và chỉ nếu mọi phụ thuộc hàm X A F với A X đều có X là siêu khóa b. Trình tự thực hiện kiểm tra dạng chuẩn BC Thuật toán kiểm tra dạng chuẩn BC Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn BC hay không đạt chuẩn BC. Bước 1: Tìm tất cả khóa của Q Bước 2: Từ F tạo tập phụ thuộc hàm tương đương F1tt có vế phải một thuộc tính Bước 3: Nếu mọi phụ thuộc hàm X A F1tt với A X đều có X là siêu khóa thì Q đạt chuẩn BC ngược lại Q không đạt chuẩn BC c. Thực hành 1. Q(A,B,C,D,E,I) F={ACD EBI;CE AD}. Hỏi Q có đạt chuẩn BC không? Giải: TN={C} TG={ADE} 52
  59. F =F1tt={ACD E,ACD B,ACD I,CE A,CE D} Mọi phụ thuộc hàm của F1tt đều có vế trái là siêu khóa Q đạt dạng chuẩn BC 2. Q(SV,MH,THAY) F = {SV,MH THAY;THAY MH} Quan hệ trên đạt chuẩn 3 nhưng không đạt chuẩn BC 3. Chẳng hạn cho Q(A,B,C,D) và F={AB C; D B; C ABD} thì Q là 3NF nhưng không là BCNF Nếu F={B D,A C,C ABD}là 2 NF nhưng không là 3 NF 6.1.5. Dạng chuẩn của một lược đồ quan hệ a. Định nghĩa: Dạng chuẩn của một lược đồ cơ sở dữ liệu là dạng chuẩn thấp nhất trong các dạng chuẩn của các lược đồ quan hệ con. b. Trình tự kiểm tra dạng chuẩn của một lược đồ quan hệ Thuật toán kiểm tra dạng chuẩn của một lược đồ quan hệ. Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn gì? Bước 1: Tìm tất cả khóa của Q Bước 2: Kiểm tra chuẩn BC nếu đúng thì Q đạt chuẩn BC, kết thúc thuật toán ngược lại qua bước 3 Bước 3: Kiểm tra chuẩn 3 nếu đúng thì Q đạt chuẩn 3, kết thúc thuật toán ngược lại qua bước 4 Bước 4: Kiểm tra chuẩn 2 nếu đúng thì Q đạt chuẩn 2, kết thúc thuật toán. ngược lại Q đạt chuẩn 1 c. Thực hành Thực hiện các bài tập kiểm tra dạng chuẩn mục BÀI TẬP CHƯƠNG 6 6.2. Phép tách kết nối bảo toàn 6.2.1. Phép tách kết nối bảo toàn thông tin (lossless-join decomposition) a. Khái niệm Cho lược đồ quan hệ Q(TENNCC,DIACHI,SANPHAM,DONGIA) có quan hệ tương ứng là r Đặt r1 là quan hệ có được bằng cách chiếu r lên Q1(TENNCC,SANPHAM,DONGIA), Đặt r2 là quan hệ có được bằng cách chiếu r lên Q2(TENNCC,DIACHI) Đặt r’ là quan hệ có được bằng cách kết tự nhiên giữa r1 và r2 qua TENNCC.chẳng hạn: 53
  60. Kết quả là r ≠ r’ hay r ≠ r.Q1 |> <| r.Q2. Suy ra phép tách trên là phép tách kết nối bảo toàn thông tin. 54
  61. * Phép tách Q thành n lược đồ con Q là một lược đồ quan hệ, F là tập phụ thuộc hàm. Q được tách thành các lược đồ con Q1, Q2, Q3 ,Qn theo từng bước mà ở mỗi bước một lược đồ được tách thành hai lược đồ con và thỏa mãn điều kiện của tính chất bảo toàn thông tin thì với r là quan hệ bất kỳ của Q ta luôn có: r = r.Q1|> <|r.Qn b. Thuật toán kiểm tra phép tách kết nối bảo toàn thông tin Dữ liệu vào: lược đồ quan hệ Q(A1,A2, An), tập phụ thuộc hàm F, phép tách =(Q1,Q2, ,Qk). Dữ liệu ra: kết luận phép tách có phải là phép tách bảo toàn thông tin ? 1. Thiết lập bảng với k+1 dòng, n+1 cột . Cột j ứng với thuộc tính Aj (j=1 n), hàng i ứng với lược đồ quan hệ Qi(i=1 k). Tại ví trí hàng i, cột j ta điền ký hiệu Ajnếu Aj Qi, nếu không ta đặt ký hiệu bt vào vị trí đó. (với t đầu tiên bằng 1) và sau đó tăng t lên một đơn vị. 2. Xét lần lượt các phụ thuộc hàm trong F, áp dụng cho bảng vừa mới thành lập ở trên. Giả sử xét (X Y) F, chúng ta tìm những hàng giống nhau ở tất cả các thuộc tính của X, nếu thấy những hàng như vậy ta sẽ làm cho các ký hiệu của hai hàng này bằng nhau ở tất cả các thuộc tính của Y. Khi làm cho 2 ký hiệu này bằng nhau, nếu một trong hai ký hiệu là aj thì cho ký hiệu kia trở thành aj, nếu hai ký hiệu là bk hoặc bl thì có thể cho chúng trở thành bt hoặc bt (với t = min (k,l)). Bước này được tiếp tục cho các phụ thuộc hàm còn lại của F cho đến khi không còn áp dụng được nữa. 3. Xét bảng kết quả, nếu thấy trong bảng này có một hàng chứa toàn aj(i=1 n) thì kết luận đó là phép kết nối bảo toàn thông tin, ngược lại là phép kết nối mất mát thông tin Chú ý: một điều quan trọng cần phải nhớ là khi cho hai ký hiệu bằng nhau thì phải cho bằng nhau ở tất cả các xuất hiện của chúng trong bảng chứ không phải chỉ cho bằng nhau ở những ký hiệu trong phạm vi các phụ thuộc X Y F. c. Thực hành 55
  62. Dòng thứ Q3(BE) của bảng chứa toàn giá trị aj (j=1 n) nên phép phân rã trên là bảo toàn thông tin. 6.2.2 Phép tách bảo toàn phụ thuộc hàm (decompositions that preserve dependencies) a. Tổng quan về phép tách bảo toàn phụ thuộc hàm * Tập phụ thuộc hàm Fi của Qi Phần trên chỉ đề cấp vấn đề tách một lược đồ quan hệ Q(A1,A2, An) thành các lược đồ con Q1,Q2, ,Qk còn không đề cập đến tập phụ thuộc hàm của các lược đồ con này. Nếu Q(A1,A2, An) là lược đồ quan hệ, F phụ thuộc hàm, =(Q1,Q2, ,Qk) là phép phân rã bảo toàn thông tin, ri là quan hệ của Qi thì tính chất sau thỏa: + + + ri chỉ thỏa các phụ thuộc hàm X Y F với XY  Qi + + + Nói cách khác, tập phụ thuộc hàm của Qi chính là Fi có Fi ={X Y F | XY Qi }. Ta có thể hiểu F được phân rã thành các F1, ,Fk * Định nghĩa: Cho phân rã =(Q1,Q2, ,Qk) của một lược đồ quan hệ, và một tập phụ thuộc hàm F. + Hình chiếu của F trên một tập các thuộc tính Qi ký hiệu  Qi(F) là tập các phụ thuộc hàm X Y F+ sao cho XY Z. + + Qi(F)=Fi ={ X Y | X Y F và XY Qi} Ta nói phân rã bảo toàn tập phụ thuộc hàm F nếu + + F =  Qi(F) F = ( Qi(F)) với i=1 k + Hệ quả: F  ( Qi(F)) với i=1 k Nhận xét: từ hệ quả trên ta suy ra: để xác định phép phân rã =(Q1,Q2, ,Qk) có bảo toàn phụ 56
  63. thuộc hàm hay không, với mỗi phụ thuộc hàm X Y F ta xác định xem nó có là thành viên của tập phụ thuộc hàm G =   Qi(F) hay không. Ta không cần xác định chiều ngược lại. Ví dụ 12: Cho lược đồ quan hệ Q(A,B,C) và F={A B,B C,C A}. Phép phân rã =(Q1,Q2) + tách Q thành hai lược đồ quan hệ Q1(A,B) và Q2(B,C).Hãy tính hình chiếu của F trên Q1 + và Q2 . Phép phân rã có bảo toàn phụ thuộc hàm F không? Giải: về nguyên tắc ta có thể giải bài toán theo các bước dưới đây Bước 1: Kê tất cả tập con của Q+ Bước 2: Tính bao đóng của các tập con của Q+ Bước 5: G = Q1(F) Q2(F) ={A B,A AB,B A,B AB,B C,B BC,C B,C BC} F={A B,B C,C A} có A B, B C đều là thành viên của G, còn C A có là thành + + viên của G hay không ta tính CG . CG =ABC C A cũng là thành viên của G. Vậy phép phân rã trên bảo toàn phụ thuộc hàm. Bài toán trên có thể được giải theo các bước đơn giản sau cho từng lược đồ quan hệ con: Tính cho Q1 + Bước 1: Kê tất cả tập con của Q1 57
  64. F={A B,B C,C A} có A B, B C đều là thành viên của G còn C A có là + + thành viên của G hay không ta tính CG . CG =ABC C A cũng là thành viên của G. Vậy phép phân rã trên bảo toàn phụ thuộc hàm. * Ý nghĩa của phân rã có bảo toàn phụ thuộc hàm Ví dụ 13: Cho lược đồ quan hệ Q(C,S,Z) và F={CS Z,Z C}. Phép tách =(Q1,Q2) tách Q thành hai lược đồ Q1(S,Z) và Q2(C,Z). Hỏi phép tách có bảo toàn phụ thuộc hàm không? Giải: 58
  65. Vậy phép phân rã trên không bảo toàn phụ thuộc hàm, điều này có nghĩa khi ta đưa dữ liệu vào Q1 và Q2 sao cho không vi phạm phụ thuộc hàm hình chiếu của nó, nhưng khi kết nối chúng lại thì dữ liệu kết quả của lược đồ quan hệ Q lại vi phạm phụ thuộc hàm CS Z b. Trình tự kiểm tra bảo tảo phụ thuộc hàm Thuật toán kiểm tra bảo toàn phụ thuộc hàm b1. Thuật toán tìm bao đóng của tập thuộc tính X đối với G =   Qi(F) Vào: =(Q1,Q2, ,Qk), F, X + Ra: XG Bước 1: Với mỗi phụ thuộc hàm X Y F ta thực hiện từ bước 2 đến bước 4 Bước 2: đặt Z’ = X + + + Bước 3: thế Z’ = Z’((Z’ Qi ) Qi ) Bước 4: nếu ở Qi, Z’ thay đổi thì thực hiện lại bước 3 cho Qđầu tiên + Ngược lại kết thúc thuật toán và trả về Z’ (là bao đóng XG ) b2. Thuật toán kiểm tra bảo toàn phụ thuộc hàm Vào: =(Q1,Q2, ,Qk), F Ra: kết luận phép tách bảo toàn hay không bảo toàn phụ thuộc hàm Bước 1: Với mỗi phụ thuộc hàm X?Y?Fta thực hiện từ bước 2 đến bước 3: + Bước 2: Tìm bao đóng XG với G = Qi(F) + + Bước 3: Nếu Y  XG thì X Y  Qi(F) 59
  66. + Bước 4: Nếu tất cả phụ thuộc X Y F đều thuộc  Qi(F) thì ta kết luận phân rã bảo toàn phụ thuộc hàm ngược lại không bảo toàn phụ hàm c. Thực hành bt1. Cho lược đồ quan hệ Q(C,S,Z) và F={CS Z,Z C}. Phép tách =(Q1,Q2) tách Q thành hai lược đồ Q1(S,Z) và Q2(C,Z). Hỏi phép tách có bảo toàn phụ thuộc hàm không? Vào: Q(C,S,Z), F={CS Z,Z C},Q1(S,Z) và Q2(C,Z) + Đương nhiên Z C G =  Q1(F)  Q2(F) Z C Q1(F) Q2(F)) 1. Z’=CS 2. gán Bước 1 và 2 có Z’không thay đổi, ta sang lược đồ Q2và tính tiếp Z’ 3. Z’không thay đổi và hết lược đồ quan hệ ?ngưng không tính tiếp Z’ 4. Vậy phép phân rã không bảo toàn phụ thuộc hàm. bt2. Cho lược đồ quan hệ Q(A,B,C) và F={A B,B C,C A}. Phép phân rã =(Q1,Q2) tách Q thành hai lược đồ quan hệ Q1(A,B) và Q2(B,C). Có bảo toàn phụ thuộc hàm không (không tính F+) Vào: Q(A,B,C), F={A B,B C,C A},Q1(A,B) và Q2(B,C) Hiển nhiên Ta xác định C Acó thuộc 6.3 Thiết kế CSDL bằng cách phân rã 6.3.1 Thiết kế CSDL bằng cách phân rã theo các thuật toán thông thường a. Thiết kế CSDL bằng cách phân rã Tức là phân rã thành dạng chuẩn BC(hay chuẩn 3) bảo toàn thông tin b. Trình tự phân rã Q, F thành dạng chuẩn BC (hay chuẩn 3) bảo toàn thông tin b1. Thuật toán thông thường Bước 1:Tìm tất cả khóa của Q Bước 2:Tìm phụ thuộc hàm X Y Fcó X không là siêu khóa và Y không chứa thuộc tính khóa. Nếu tìm thấy thì tách Q thành Q1 và Q2 theo quy tắc sau: tìm bao đóng của tất cả tập con của XY để suy ra tìm bao đóng của tất cả tập con của Q+-Yđể suy ra 60
  67. thực hiện thuật toán phân rã (Q1,F1) thực hiện thuật toán phân rã (Q2,F2) Ngược lại nếu không tìm thấy thì có hai trường hợp: Trường hợp 1: mọi phụ thuộc hàm trong Fi đều có vế trái là siêu khóa thì Qi đạt chuẩn BC Trường hợp 2: nếu có phụ thuộc hàm có vế trái không là siêu khóa và vế phải là thuộc tính khóa thì Qi đạt chuẩn 3. b2. Thuật toán cải tiến Thuật toán phân rã Q, F thành dạng chuẩn BC (hay chuẩn 3) bảo toàn thông tin Bước 1: Tìm tập tất cả khóa SK của Q Bước 2: Tìm phụ thuộc hàm X Y Fcó X không là siêu khóa và Y không chứa thuộc tính khóa. Nếu tìm thấy thì tách Q thành Q1 và Q2 theo quy tắc sau: Q1=Q[XY]; Tính F1 bằng cách tính bao đóng tất cả tập con của XY + Q2=Q[Q -Y] SK cũng là tập khóa của Q2 thực hiện bước 1 cho Q1 thực hiện bước 2 cho Q2 Ngược lại nếu không tìm thấy thì có hai trường hợp: Trường hợp 1: mọi phụ thuộc hàm trong Fi đều cóvế trái là siêu khóa thì Qi đạt chuẩn BC Trường hợp 2: nếu có phụ thuộc hàm có vế trái không là siêu khóa và vế phải là thuộc tính khóa thì Qi đạt chuẩn 3. Chú ý: Thuật toán này chỉ tiện trong trường hợp khối lượng tính toán trong việc tìm tất cả khóa của lược đồ quan hệ Q không lớn. Nói cách khác tập trung gian TG có ít thuộc tính. Ngược lại ta phải dùng thuật toán của phần tiếp theo. b3. Thuật toán phân rã không cần tìm tất cả các khóa của LĐQH Q Thuật toán phân rã sau không cần tìm tất cả khóa của lược đồ quan hệ Q Thuật Toán phân rã Q, F thành dạng chuẩn BC bảo toàn thông tin Bước 1: Z’ = Q+ Bước 2: phân rã Z’ theo thuật toán chi tiết để được 2 lược đồ Z’-A và XA trong đó XA ở dạng chuẩn BC và X A Nếu thuật toán chi tiết cho kết quả thì qua bước 3 Ngược lại kết thúc thuật toán Bước 3: nhận XA là một lược đồ con của các lược đồ kết quả Q1, ,Qk Bước 4: thực hiện phân rã Z’-A, F Thuật toán chi tiết Bước 1: nếu Z’ không chứa AB sao cho (Z’-AB) A. thì báo không phân rã được. Ngược lại qua bước 2 Bước 2: đặt Y’ = Z’ Bước 3: nếu Y’chứa AB sao cho (Y’-AB) A. thì gán Y’ = Y’–B thực hiện lại bước 2 Bước 4: bước 3 cho kết quả Y’ = XA với XA ở dạng chuẩn BC và X A.Trả về XA Nhận xét + + Ở mỗi bước 2 của thuật toán phân rã Q, F ta thu được 2 lược đồ Qi =Z’-A, Q1 = XA với + + + Qi Q1 = (Z’-A) XA = X và X Q1 và Q1 là lược đồ ở dạng chuẩn BC. Thuật toán lại tiếp tục phân rã Qi theo đúng cách đã làm thuật toán phân rã bảo toàn thông tin và các lược đồ con Qi đạt dạng chuẩn BC. 61