›
›

Bài giảng Hệ cơ sở dữ liệu Database System

ppt 214 trang phuongnguyen 7620
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ cơ sở dữ liệu Database System", để 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:

  • pptbai_giang_he_co_so_du_lieu_database_system.ppt

Nội dung text: Bài giảng Hệ cơ sở dữ liệu Database System

  1. GIỚI THIỆU MƠN HỌC: HỆ CƠ SỞ DỮ LIỆU Database System 1
  2. NỢI DUNG ƠN TẬP ⚫ Giới thiệu ⚫ Mơ hình liên kết thực thể ⚫ Mơ hình dữ liệu quan hệ ⚫ Chuyển từ ER sang quan hệ ⚫ Ràng buộc tồn vẹn ⚫ Đại số quan hệ ⚫ Ngơn ngữ SQL ⚫ Chuẩn hóa dữ liệu 2
  3. Các khái niệm cơ bản và định nghĩa ❖ Cơ sở dữ liệu  database  Cơ sở dữ liệu là sự tập hợp cĩ tổ chức các dữ liệu cĩ liên quan luận lý với nhau.  Dữ liệu (data): sự biểu diễn của các đối tượng và sự kiện được ghi nhận và được lưu trữ trên các phương tiện của máy tính.  Dữ liệu cĩ cấu trúc: số, ngày, chuỗi ký tự,  Dữ liệu khơng cĩ cấu trúc: hình ảnh, âm thanh, đoạn phim,  Cĩ tổ chức (organized): người sử dụng cĩ thể dễ dàng lưu trữ, thao tác và truy xuất dữ liệu. 3
  4. Các khái niệm cơ bản và định nghĩa ❖ Cơ sở dữ liệu  Cĩ liên quan luận lý (logically related): dữ liệu mơ tả một lãnh vực mà nhĩm người sử dụng quan tâm và được dùng để trả lời các câu hỏi liên quan đến lãnh vực này. ❖ Thơng tin  information  Thơng tin là dữ liệu đã được xử lý để làm tăng sự hiểu biết của người sử dụng.  Dữ liệu trong ngữ cảnh.  Dữ liệu được tổng hợp / xử lý. 4
  5. Các khái niệm cơ bản và định nghĩa Dữ liệu 50010273 Nguyễn Trung Tiến MT00 20 50100298 Lê Việt Hùng MT01 19 59900012 Trần Hùng Việt MT99 21 50200542 Hồ Xuân Hương MT02 18 50000075 Bùi Đức Duy MT00 20 Thơng tin: dữ liệu trong ngữ cảnh Mã sinh viên Họ và tên sinh viên Lớp Tuổi 50010273 Nguyễn Trung Tiến MT00 20 50100298 Lê Việt Hùng MT01 19 59900012 Trần Hùng Việt MT99 21 50200542 Hồ Xuân Hương MT02 18 50000075 Bùi Đức Duy MT00 20 5
  6. Các khái niệm cơ bản và định nghĩa Thơng tin: dữ liệu được tổng hợp / xử lý MT99 20% MT00 40% MT02 20% MT01 20% 6
  7. Các khái niệm cơ bản và định nghĩa ❖ Siêu dữ liệu  metadata  Siêu dữ liệu là dữ liệu dùng để mơ tả các tính chất / đặc tính của dữ liệu khác (dữ liệu về dữ liệu).  Các đặc tính: định nghĩa dữ liệu, cấu trúc dữ liệu, qui tắc / ràng buộc. 7
  8. Các khái niệm cơ bản và định nghĩa Siêu dữ liệu cho Sinh_viên Data Item Value Name Type Length Min Max Description MaSV Character 8 Ma sinh vien Hoten Character 30 Ho ten sinh vien Lop Character 3 Lop Tuoi Number 2 17 25 Tuoi 8
  9. Hệ thống xử lý tập tin ❖ Hệ thống xử lý tập tin  file processing system  Hệ thống xử lý tập tin là tập hợp các chương trình dùng để lưu trữ, thao tác và truy xuất các tập tin dữ liệu cĩ kích thước lớn.  Các tập tin dữ liệu được lưu trữ trong các thư mục (folder). 9
  10. Hệ thống xử lý tập tin  Các chương trình xử lý tập tin  Tạo cấu trúc tập tin.  Thêm dữ liệu vào tập tin.  Xóa dữ liệu của tập tin  Sửa dữ liệu của tập tin.  Liệt kê dữ liệu của tập tin. 10
  11. Hệ thống xử lý tập tin ❖ Nhược điểm của hệ thống xử lý tập tin  Phụ thuộc dữ liệu – chương trình (Program-Data Dependence)  Tất cả các chương trình ứng dụng phải duy trì siêu dữ liệu (phần mơ tả) của các tập tin mà chúng sử dụng.  Dư thừa dữ liệu / Trùng lặp dữ liệu (Data Redundancy / Duplication of Data)  Các hệ thống / chương trình khác nhau cĩ các bản dữ liệu riêng biệt của cùng dữ liệu.  Hạn chế việc dùng chung dữ liệu  Mỗi ứng dụng cĩ các tập tin riêng biệt, ít sử dụng chung dữ liệu với các ứng dụng khác. 11
  12. Hệ thống xử lý tập tin ❖ Nhược điểm của hệ thống xử lý tập tin  Thời gian phát triển lâu  Người lập trình phải thiết kế các dạng tập tin dữ liệu riêng và viết cách truy xuất tập tin cho mỗi ứng dụng mới.  Chi phí bảo trì chương trình cao  Các nhược điểm nêu trên làm cho việc bảo trì chương trình gặp nhiều khĩ khăn, thường chiếm khoảng 80% ngân sách phát triển HTTT. 12
  13. Cách tiếp cận cơ sở dữ liệu ❖ Kho dữ liệu trung tâm chứa các dữ liệu dùng chung. ❖ Dữ liệu được quản lý bởi một đơn vị điều khiển (controlling agent). ❖ Dữ liệu được lưu trữ theo một dạng thức chuẩn và thích hợp. ❖ Cần phải cĩ một hệ quản trị CSDL. 13
  14. Cách tiếp cận cơ sở dữ liệu ❖ Ưu điểm của cách tiếp cận CSDL ⚫ Giảm bớt dư thừa dữ liệu ⚫ Nhất quán và chính xác dữ liệu ⚫ Chia sẻ dữ liệu ⚫ Các tiêu chuẩn chung có thể phát huy ⚫ An tồn được áp dụng ⚫ Đảm bảo tính Tồn vẹn dữ liệu ⚫ Độc lập dữ liệu ⚫ Bảo mật 14
  15. Cách tiếp cận cơ sở dữ liệu ❖ Chi phí và rủi ro của cách tiếp cận CSDL  Chi phí ban đầu  Chi phí cài đặt và quản lý  Chi phí chuyển đổi (conversion cost)  Chi phí vận hành  Cần nhân viên mới có chuyên mơn.  Cần phải chép lưu và phục hồi.  Mâu thuẫn về mặt tổ chức  Rất khó thay đổi các thói quen cũ. 15
  16. Các loại cơ sở dữ liệu ❖ CSDL cá nhân  personal database  CSDL riêng. ❖ CSDL nhĩm làm việc  workgroup database  Mạng cục bộ (ít hơn 25 người sử dụng) ❖ CSDL phịng ban  department database  Mạng cục bộ (từ 25 đến 100 người sử dụng) ❖ CSDL xí nghiệp  enterprise database  Mạng diện rộng (hàng trăm hoặc hàng ngàn người sử dụng) 16
  17. Các loại cơ sở dữ liệu 17
  18. Hệ quản trị cơ sở dữ liệu ❖ Hệ quản trị CSDL  DBMS – DataBase Management System  Hệ quản trị CSDL là tập hợp các chương trình dùng để quản lý cấu trúc và dữ liệu của CSDL và điều khiển truy xuất dữ liệu trong CSDL.  Cho phép người sử dụng định nghĩa, tạo lập và bảo trì CSDL và cung cấp các truy xuất dữ liệu. 18
  19. Hệ quản trị cơ sở dữ liệu 19
  20. Hệ quản trị cơ sở dữ liệu ❖ Các chức năng của hệ quản trị CSDL  Lưu trữ, truy xuất và cập nhật dữ liệu  Ngơn ngữ định nghĩa dữ liệu (DDL - Data Definition Language)  Ngơn ngữ thao tác dữ liệu (DML - Data Manipulation Language).  Quản lý giao tác (transaction management).  Điều khiển tương tranh (concurrency control)  Chép lưu và phục hồi dữ liệu.  Bảo mật dữ liệu  Ngơn ngữ điều khiển dữ liệu (DCL - Data Control Language).  Hỗ trợ truyền thơng dữ liệu.  Duy trì tính tồn vẹn / nhất quán dữ liệu.  Cung cấp các tiện ích. 20
  21. Sự phát triển các hệ CSDL ❖ Hệ thống tập tin (flat file): 1960 - 1980 ❖ Hệ CSDL phân cấp (hierarchical): 1970 - 1990 ❖ Hệ CSDL mạng (network): 1970 - 1990 ❖ Hệ CSDL quan hệ (relational): 1980 - nay ❖ Hệ CSDL hướng đối tượng (object-oriented): 1990 - nay ❖ Hệ CSDL đối tượng - quan hệ (object-relational): 1990 - nay ❖ Kho dữ liệu (data warehouse): 1980 - nay ❖ Web-enabled: 1990 - nay 21
  22. Các mức biểu diễn CSDL ⚫ Mức trong: (mức vật lý – Physical) là mức lưu trữ CSDL (cần giải quyết vấn đề gì? Dữ liệu gì? Lưu trữ như thế nào? ở đâu? Cần các chỉ mục gì? Truy xuất tuần tự hay ngẫu nhiên. Dành cho người quản trị và người sử dụng chuyên mơn. ⚫ Mức quan niệm: (Conception hay Logical) cần phải lưu trữ bao nhiêu loại dữ liệu? là dữ liệu gì? mối quan hệ ⚫ Mức ngồi: của người sử dụng và các chương trình ứng dụng 22
  23. Các mức biểu diễn CSDL NSD1 Mơi trường thực thế giới Cấu trúc thực ngồi 1 NSD2 Mức Cấu trúc quan ngồi 2 Mức vật lý – niệm Cấu trúc vật lý hoặc Cấu trúc mức ngồi n logic Chương trình CSDL ứng dụng n 23
  24. Mơ hình liên kết thực thể ❖ Mơ hình liên kết thực thể (ER)  entity-relationship model  Mơ hình liên kết thực thể là cách tiếp cận chính để mơ hình hĩa dữ liệu ý niệm (conceptual data modeling).  Mơ hình ER là cơng cụ giao tiếp giữa người thiết kế CSDL và người sử dụng cuối cùng để xây dựng CSDL trong giai đoạn phân tích.  Mơ hình ER được dùng để xây dựng mơ hình dữ liệu ý niệm (conceptual data model) nhằm biểu diễn cấu trúc và các ràng buộc của CSDL. 24
  25. Mơ hình liên kết thực thể ❖ Các thành phần của mơ hình liên kết thực thể  Thực thể và các thuộc tính.  Mối liên kết và các thuộc tính. 25
  26. Mơ hình liên kết thực thể 26 Sơ đồ liên kết thực thể (ERD - Entity-Relationship Diagram)
  27. Relationship symbols Entity symbols Attribute symbols A special entity that is also a relationship Relationship degrees specify number of entity types involved Relationship cardinalities specify how many of each entity type is allowed 27
  28. Thực thể ❖ Thể hiện thực thể  entity instance  Thể hiện thực thể là người, vị trí, đối tượng, sự kiện, khái niệm (thường tương ứng với một hàng của bảng).  Đặc điểm của thực thể là tính phân biệt (distinctness): cĩ thể phân biệt giữa thực thể này với thực thể khác. ❖ Kiểu thực thể  entity type  Kiểu thực thể là tập hợp các thực thể thuộc cùng một loại (thường tương ứng với một bảng).  Được biểu diễn bằng hình chữ nhật. 28
  29. Thuộc tính ❖ Thuộc tính  attribute  Thuộc tính là một đặc tính / tính chất của một kiểu thực thể (thường tương ứng với một vùng tin trong một bảng).  Được biểu diễn bằng hình bầu dục. ❖ Các loại thuộc tính  Thuộc tính bắt buộc và thuộc tính tùy chọn.  Thuộc tính đơn và thuộc tính phức hợp.  Thuộc tính đơn trị và thuộc tính đa trị.  Thuộc tính chứa và thuộc tính dẫn xuất.  Thuộc tính khĩa và thuộc tính khơng khĩa. 29
  30. Thuộc tính ❖ Thuộc tính đơn và thuộc tính phức hợp  Thuộc tính đơn (simple attribute) là thuộc tính khơng bị phân rã thành nhiều thuộc tính khác.  Thuộc tính phức hợp (composite attribute) là thuộc tính bị phân rã thành nhiều thuộc tính khác. 30
  31. Thuộc tính Thuộc tính đơn Thuộc tính phức hợp 31
  32. Thuộc tính ❖ Thuộc tính đơn trị và thuộc tính đa trị  Thuộc tính đơn trị (single-valued attribute) là thuộc tính chỉ chứa một giá trị.  Thuộc tính đa trị (multivalued attribute) là thuộc tính chứa nhiều giá trị khác nhau thuộc một miền trị, được biểu diễn bằng hình bầu dục nét đơi. 32
  33. Thuộc tính ❖ Thuộc tính chứa và thuộc tính dẫn xuất  Thuộc tính chứa (stored attribute) là thuộc tính mà giá trị của nó khơng được suy dẫn từ các thuộc tính khác.  Thuộc tính dẫn xuất (derived attribute) là thuộc tính mà giá trị của nó được suy dẫn từ các thuộc tính khác, được biểu diễn bằng hình bầu dục nét đứt. 33
  34. Thuộc tính Thuộc tính đơn trị Thuộc tính dẫn xuất Thuộc tính đa trị Thuộc tính chứa 34
  35. Khĩa ❖ Khĩa / thuộc tính xác định  key / identifier  Khĩa là một thuộc tính hoặc tổ hợp các thuộc tính dùng để xác định duy nhất một thể hiện của một kiểu thực thể. ❖ Thuộc tính khĩa và thuộc tính khơng khĩa  Thuộc tính khĩa là thuộc tính ở trong khĩa.  key attribute / prime attribute / identifier attribute  Thuộc tính khĩa được gạch dưới.  Thuộc tính khơng khĩa (non-key attribute) là thuộc tính khơng ở trong khĩa. 35
  36. Khĩa ❖ Khóa đơn và khóa phức hợp  Khĩa đơn (simple key) là khóa chỉ có một thuộc tính.  Khĩa phức hợp (composite key) là khóa có nhiều hơn một thuộc tính. ❖ Khóa dự tuyển  candidate key  Khĩa dự tuyển là khóa của một kiểu thực thể.  Một kiểu thực thể có ít nhất một khóa dự tuyển. 36
  37. Khĩa ❖ Khóa chính  primary key  Khĩa chính là một khóa tiêu biểu trong các khóa dự tuyển của một kiểu thực thể.  Một kiểu thực thể chỉ có một khóa chính.  Khóa chính dùng để liên kết giữa các thực thể. 37
  38. Khĩa Khĩa đơn Thuộc tính khơng khĩa Khĩa phức hợp 38
  39. Mối liên kết ❖ Kiểu mối liên kết  relationship type  Kiểu mối liên kết là sự liên kết giữa các kiểu thực thể.  Được biểu diễn bằng hình thoi.  Mối liên kết có thể có nhiều thuộc tính dùng để mơ tả các đặc tính của sự liên kết giữa các thực thể.  Hai thực thể có thể có nhiều kiểu mối liên kết giữa chúng. 39
  40. Mối liên kết Hai thực thể cĩ nhiều mối liên kết 40 Mối liên kết cĩ thuộc tính
  41. Mối liên kết ❖ Bậc / ngơi của mối liên kết  degree / arity of relationship  Bậc của mối liên kết là số lượng kiểu thực thể tham gia đồng thời vào mối liên kết này. ❖ Các loại mối liên kết  Mối liên kết 1-ngơi (unary relationship)  Mối liên kết 2-ngơi (binary relationship)  Mối liên kết 3-ngơi (ternary relationship): 3 kiểu thực thể đồng thời tham gia vào mối liên kết. 41
  42. Mối liên kết Entities of two different types related to each other One entity related to another of the same Entities of three different entity type types related to each other 42
  43. Mối liên kết Mối liên kết 1-ngơi 43
  44. Mối liên kết Mối liên kết 2-ngơi 44
  45. Mối liên kết Mối liên kết 3-ngơi 45
  46. Mối liên kết ❖ Lượng số của mối liên kết  cardinality of relationship  Lượng số của mối liên kết là số lượng thể hiện thực thể tham gia vào mối liên kết này. ❖ Các loại lượng số  một - một (one-to-one): một thực thể a liên kết với một thực thể b; một thực thể b liên kết với một thực thể a.  một - nhiều (one-to-many): một thực thể a liên kết với nhiều thực thể b; một thực thể b liên kết với một thực thể a.  nhiều - nhiều (many-to-many): một thực thể a liên kết với nhiều thực thể b; một thực thể b liên kết với nhiều thực thể a. 46
  47. Mối liên kết ❖ Ràng buộc lượng số  cardinality constraint  Ràng buộc lượng số là số lượng thể hiện của thực thể này có thể hoặc phải liên kết với một thể hiện của thực thể khác.  Lượng số nhỏ nhất  Nếu 0 là tùy chọn (optional).  Nếu một hoặc nhiều là bắt buộc (mandatory).  Lượng số lớn nhất  Số lượng lớn nhất. 47
  48. Mối liên kết 48
  49. Mối liên kết Mối liên kết 1-ngơi một-một cĩ lượng số tùy chọn Mối liên kết 2-ngơi một-nhiều cĩ lượng số bắt buộc 49
  50. Mối liên kết Lượng số lớn nhất Mối liên kết cĩ lượng số tối đa xác định 50
  51. Relational Data Model ❖ Quan hệ (relation) là một bảng dữ liệu hai chiều bao gồm nhiều hàng (mẩu tin) và nhiều cột (thuộc tính hoặc vùng tin).  Mỗi hàng là duy nhất: khơng thể có hai hàng có cùng các giá trị ở tất cả vùng tin.  Thứ tự của các hàng là khơng quan trọng.  Thứ tự của các cột là khơng quan trọng.  Quan hệ là một bảng khơng chứa các hàng giống hệt nhau. 51
  52. Quan hệ Quan hệ: Supplier Snum Name City S1 Nguyễn Trung Tiến SF S2 Trần Thị Yến LA S3 Nguyễn Văn An SF 52
  53. Quan hệ ❖ Khĩa  key  Khĩa quan hệ là một tập nhỏ nhất các thuộc tính dùng để xác định duy nhất một hàng.  Một khĩa chỉ cĩ một thuộc tính được gọi là khĩa đơn (simple key).  Một khĩa cĩ nhiều thuộc tính được gọi là khĩa phức hợp (composite key).  Khĩa thường được sử dụng làm chỉ mục (index) của bảng dữ liệu để làm tăng tốc độ xử lý câu truy vấn. 53
  54. Quan hệ ❖ Khĩa  Một quan hệ phải cĩ ít nhất một khĩa và cĩ thể cĩ nhiều khĩa.  Các thuộc tính thuộc một khĩa được gọi là thuộc tính khĩa (prime attribute), các thuộc tính cịn lại trong lược đồ quan hệ được gọi là các thuộc tính khơng khĩa (nonprime attribute).  Các thuộc tính khĩa được gạch dưới.  Các thuộc tính khĩa khơng được cĩ giá trị rỗng (null value). 54
  55. Quan hệ ❖ Khĩa  Tất cả các khĩa của một quan hệ được gọi là khĩa dự tuyển (candidate key).  Một trong các khĩa dự tuyển được chọn làm khĩa tiêu biểu, khĩa này được gọi là khĩa chính (primary key).  Một quan hệ chỉ cĩ một khĩa chính và cĩ thể cĩ nhiều khĩa dự tuyển.  Trong một quan hệ, một hoặc nhiều thuộc tính được gọi là khĩa ngoại (foreign key) nếu chúng là khĩa chính của một quan hệ khác. 55
  56. Cơ sở dữ liệu quan hệ ❖ Cơ sở dữ liệu quan hệ (relational database) bao gồm các bảng (quan hệ) biểu diễn các thực thể và các khóa chính / khóa ngoại biểu diễn các mối liên kết. 56
  57. Cơ sở dữ liệu quan hệ Primary Key Foreign Key (implements 1:N relationship between customer and order) Combined, these are a composite primary key (uniquely identifies the order line) individually they are foreign keys (implement M:N relationship between order and product) 57
  58. Lược đồ cơ sở dữ liệu ❖ Lược đồ cơ sở dữ liệu  database schema  Lược đồ cơ sở dữ liệu là một tập hợp các lược đồ quan hệ.  Trong một lược đồ cơ sở dữ liệu, các tên lược đồ quan hệ là duy nhất.  Lược đồ quan hệ Q với tập thuộc tính A1,A2, An được viết là Q(A1,A2, An) ⚫ Lược đồ cơ sở dữ liệu: Emp (Empnum, Name, Sal, Tax, Mgrnum, Deptnum) Dept (Deptnum, Name, Area, Mgrnum) Supplier (Snum, Name, City) Supply (Snum, Pnum, Deptnum, Quan) 58
  59. Sự tương ứng với mơ hình ER ❖ Mỗi quan hệ (bảng) tương ứng với một kiểu thực thể hoặc với một kiểu mối liên kết nhiều - nhiều. ❖ Mỗi hàng tương ứng với một thể hiện thực thể hoặc với một thể hiện mối liên kết nhiều - nhiều. ❖ Mỗi cột tương ứng với một thuộc tính. ❖ Từ quan hệ (relation) trong cơ sở dữ liệu quan hệ khơng cĩ cùng nghĩa với từ mối quan hệ (relationship) trong mơ hình ER. 59
  60. Biến đổi ERD thành các quan hệ ❖ Qui tắc 1: Biến đổi một kiểu thực thể thành một quan hệ.  Đối với kiểu thực thể thơng thường (regular entity type): khĩa của quan hệ là khĩa của kiểu thực thể.  Thuộc tính của quan hệ là thuộc tính của kiểu thực thể.  Quan hệ chỉ chứa các thuộc tính thành phần của thuộc tính phức hợp.  Quan hệ khơng chứa các thuộc tính đa trị. 60
  61. Biến đổi ERD thành các quan hệ CUSTOMER entity type with simple attributes CUSTOMER relation Biến đổi kiểu thực thể thơng thường 61
  62. Biến đổi ERD thành các quan hệ CUSTOMER entity type with composite attribute CUSTOMER relation with address detail Biến đổi thuộc tính phức hợp 62
  63. Biến đổi ERD thành các quan hệ ❖ Qui tắc 2: Biến đổi thuộc tính đa trị thành một quan hệ.  Quan hệ chứa khĩa của kiểu thực thể và thuộc tính đa trị.  Khĩa của quan hệ gồm khĩa của kiểu thực thể và thuộc tính đa trị. 63
  64. Biến đổi ERD thành các quan hệ Multivalued attribute becomes a separate relation with foreign key 1–to–many relationship between original entity and new relation Biến đổi thuộc tính đa trị 64
  65. Biến đổi ERD thành các quan hệ ❖ Qui tắc 3: Biểu diễn mối liên kết 1-ngơi hoặc 2-ngơi cĩ lượng số một-một.  Đặt khĩa của kiểu thực thể bên phía bắt buộc và các thuộc tính của mối liên kết vào quan hệ của kiểu thực thể bên phía tùy chọn. 65
  66. Biến đổi ERD thành các quan hệ EMPLOYEE entity with Manages relationship EMPLOYEE relation with recursive foreign key Biến đổi mối liên kết một ngơi cĩ lượng số một - một 66
  67. Biến đổi ERD thành các quan hệ Biến đổi mối liên kết hai ngơi cĩ lượng số một - một 67
  68. Biến đổi ERD thành các quan hệ ❖ Qui tắc 4: Biểu diễn mối liên kết 1-ngơi hoặc 2-ngơi cĩ lượng số một-nhiều.  Đặt khĩa của kiểu thực thể bên phía một và các thuộc tính của mối liên kết vào quan hệ của kiểu thực thể bên phía nhiều. 68
  69. Biến đổi ERD thành các quan hệ EMPLOYEE entity with Manages relationship EMPLOYEE relation with recursive foreign key Biến đổi mối liên kết một ngơi cĩ lượng số một - nhiều 69
  70. Biến đổi ERD thành các quan hệ Note the mandatory one Again, no null value in the foreign key this is because of the mandatory minimum cardinality Biến đổi mối liên kết hai ngơi cĩ lượng số một - nhiều 70
  71. Biến đổi ERD thành các quan hệ ❖ Qui tắc 5: Biến đổi mối liên kết 1-ngơi hoặc 2-ngơi cĩ lượng số nhiều-nhiều thành một quan hệ.  Quan hệ chứa các khĩa của các kiểu thực thể tham gia vào mối liên kết.  Khĩa của quan hệ gồm cả hai khĩa của hai kiểu thực thể.  Thuộc tính của quan hệ là thuộc tính của mối liên kết. 71
  72. Biến đổi ERD thành các quan hệ Bill-of-materials relationships (M:N) ITEM and COMPONENT relations Biến đổi mối liên kết một ngơi cĩ lượng số nhiều - nhiều 72
  73. Biến đổi ERD thành các quan hệ The Supplies relationship will need to become a separate Composite primary key relation New intersection Foreign key relation Foreign key Biến đổi mối liên kết hai ngơi cĩ lượng số nhiều - nhiều 73
  74. Biến đổi ERD thành các quan hệ ❖ Qui tắc 6: Biến đổi mối liên kết 3-ngơi thành một quan hệ.  Quan hệ chứa ba khĩa của ba kiểu thực thể tham gia vào mối liên kết.  Mối liên kết cĩ bao nhiêu kiểu thực thể bên phía một thì quan hệ cĩ bấy nhiêu khĩa: đối với một kiểu thực thể bên phía một thì khĩa của quan hệ gồm cả hai khĩa của hai kiểu thực thể cịn lại. Nếu khơng cĩ kiểu thực thể bên phía một thì khĩa của quan hệ bao gồm cả ba khĩa của ba kiểu thực thể.  Thuộc tính của quan hệ là thuộc tính của mối liên kết. 74
  75. Biến đổi ERD thành các quan hệ Biến đổi mối liên kết ba ngơi 75
  76. Biến đổi ERD thành các quan hệ Biến đổi mối liên kết ba ngơi 76
  77. Ràng buộc tồn vẹn ❖ Ràng buộc tồn vẹn  integrity constraint  Ràng buộc tồn vẹn là một qui tắc mà tất cả các dữ liệu trong CSDL phải thỏa mãn qui tắc này. ❖ Ràng buộc miền trị  domain constraint  Các giá trị cho phép của một thuộc tính. ❖ Tồn vẹn thực thể  entity integrity  Thuộc tính khóa chính khơng có giá trị rỗng (null value). 77
  78. Ràng buộc tồn vẹn ❖ Ràng buộc tồn vẹn tham chiếu  referential integrity constraint  Ràng buộc tồn vẹn tham chiếu là một qui tắc mà tất cả các giá trị của khĩa ngoại (nếu khác null) trong quan hệ bên phía nhiều phải cĩ trong các giá trị của khĩa chính trong quan hệ bên phía một. 78
  79. Ràng buộc tồn vẹn ❖ Ràng buộc tồn vẹn tham chiếu  Qui tắc xĩa các hàng dữ liệu  Hạn chế (restrict): khơng cho phép xĩa các hàng bên phía cha (parent) nếu tồn tại các hàng liên quan bên phía phụ thuộc (dependent).  Tầng (cascade): tự động xĩa các hàng bên phía phụ thuộc tương ứng với các hàng bên phía cha.  Gán null (set-to-null): gán null cho khĩa ngoại của các hàng bên phía phụ thuộc tương ứng với các hàng bên phía cha. Khơng áp dụng cho các thực thể yếu. 79
  80. Ràng buộc tồn vẹn Referential integrity constraints are drawn via arrows from dependent to parent table 80 Ví dụ về ràng buộc tồn vẹn tham chiếu
  81. CÁC YẾU TỐ CỦA RBTV ❖ Mỗi ràng buộc tồn vẹn cĩ 3 yếu tố: điều kiện, bối cảnh và tầm ảnh hưởng.  Điều kiện của một ràng buộc tồ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ệ hoặc bằng phụ thuộc hàm.  Bối cảnh của một ràng buộc tồn vẹn là những quan hệ mà ràng buộc đó cĩ hiệu lực. Bối cảnh của một ràng buộc tồn vẹn cĩ thể là một hoặc nhiều quan hệ. 81
  82. CÁC YẾU TỐ CỦA RBTV  Bảng tầm ảnh hưởng: nhằm xác định thời điểm cần phải tiến hành kiểm tra các ràng buộc tồ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)  Một bảng tầm ảnh hưởng của một RBTV cĩ dạng: (Tên RBTV) Thêm(T) Sửa(S) Xĩa(X) r1 + - - r2 + : thực hiện thao tác có thể làm vi phạm RBTV - : thực hiện thao tác khơng thể làm vi phạm RBTV 82
  83. Phân loại ➢ RBTV có bối cảnh trên 1 quan hệ 1. RBTV miền giá trị. 2. RBTV liên thuộc tính 3. RBTV liên bộ ➢ RBTV có bối cảnh trên nhiều quan hệ 1. RBTV tham chiếu 2. RBTV liên thuộc tính, liên quan hệ 3. RBTV do thuộc tính tổng hợp 83
  84. Lược đồ CSDL quản lý giáo vụ HOCVIEN (MAHV, HO, TEN, GIOITINH, MALOP) LOP (MALOP, TENLOP, SISO) KHOA (MAKHOA, TENKHOA) MONHOC (MAMH, TENMH, MAKHOA) GIAOVIEN(MAGV, HOTEN, NGVL, HOCVI, HESO, MUCLUONG, MAKHOA) GIANGDAY(MALOP, MAMH, MAGV, HOCKY, NAM, TUNGAY, DENNGAY) KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM) 84
  85. Ràng buộc miền giá trị ⚫ Là tập giá trị mà một thuộc tính có thể nhận. ⚫ R1: Giới tính của học viên chỉ là Nam hoặc Nữ ⚫ Nội dung: hv HOCVIEN( hv.Gioitinh {‘Nam’,’Nữ’}) ⚫ Bối cảnh: quan hệ HOCVIEN ⚫ Bảng tầm ảnh hưởng: R1 Thêm Xĩa Sửa HOCVIEN + - +(Gioitinh) 85
  86. Ràng buộc liên thuộc tính ⚫ Là ràng buộc giữa các thuộc tính với nhau trên 1 bộ của quan hệ ⚫ R2:Ngày bắt đầu (TUNGAY) giảng dạy một mơn học cho một lớp luơn nhỏ hơn ngày kết thúc (DENNGAY) ⚫ Nội dung: gd GIANGDAY (gd.TUNGAY < gd.DENNGAY) ⚫ Bối cảnh : GIANGDAY ⚫ Bảng tầm ảnh hưởng: R2 Thêm Xĩa Sửa GIANGDAY + - +(Tungay, Denngay) 86
  87. Ràng buộc liên bộ ⚫ Là ràng buộc giữa các bộ trên cùng một quan hệ. ⚫ R3: Tất cả các học viên phải có mã số phân biệt. ⚫ Nội dung: h1,h2 HOCVIEN(h1 h2 h1.Mahv h2.Mahv) h1 HOCVIEN(Count(h2 HOCVIEN( h2.Mahv = h1.Mahv))(*)=1) ⚫ Bối cảnh: quan hệ HOCVIEN ⚫ Bảng tầm ảnh hưởng: R3 Thêm Xĩa Sửa HOCVIEN + - -(*) 87
  88. Ràng buộc liên bộ ⚫ R4: Các giáo viên có cùng học vị, cùng hệ số lương thì mức lương sẽ bằng nhau ⚫ Nội dung: gv1,gv2 GIAOVIEN , gv1 gv2 ((gv1.Hocvi=gv2.Hocvi  gv1.Heso=gv2.Heso) gv1.Mucluong=gv2.Mucluong) ⚫ Bối cảnh: quan hệ GIAOVIEN ⚫ Bảng tầm ảnh hưởng: R4 Thêm Xĩa Sửa GIAOVIEN + - +(Hocvi, Heso, Mucluong) 88
  89. Ràng buộc tham chiếu ⚫ Là ràng buộc quy định giá trị thuộc tính trong một bộ của quan hệ R (tập thuộc tính này gọi là khố ngoại), phải phụ thuộc vào sự tồn tại của một bộ trong quan hệ S (tập thuộc tính này là khố chính trong quan hệ S). ⚫ RBTV tham chiếu cịn gọi là ràng buộc phụ thuộc tồn tại hay ràng buộc khóa ngoại 89
  90. Ràng buộc tham chiếu ⚫ R5: Học viên thi một mơn học nào đó thì mơn học đó phải có trong danh sách các mơn học ⚫ Nội dung: ⚫ k KETQUATHI( m MONHOC( k.Mamh = m.Mamh)) ⚫ Hoặc: KETQUATHI[Mamh]  MONHOC[Mamh] ⚫ Bối cảnh: quan hệ KETQUATHI, MONHOC ⚫ Bảng tầm ảnh hưởng: R5 Thêm Xĩa Sửa KETQUATHI + - -(*) MONHOC - + -(*) 90
  91. Ràng buộc liên quan hệ, liên thuộc tính ⚫ Là ràng buộc giữa các thuộc tính trên những quan hệ khác nhau ⚫ R6: Ngày giáo viên giảng dạy (Tungay) một mơn học phải lớn hơn hoặc bằng ngày giáo viên đó vào làm (Ngvl). ⚫ Nội dung: gd GIANGDAY (gv GIAOVIEN(gd.Magv = gv.Magv) gv.Ngvl gd.Tungay) ⚫ Bối cảnh: GIANGDAY, GIAOVIEN ⚫ Bảng tầm ảnh hưởng: R6 Thêm Xĩa Sửa GIANGDAY + - +(Tungay) GIAOVIEN - - +(Ngvl) 91
  92. Ràng buộc liên quan hệ, liên thuộc tính ⚫ R7: Ngày thi một mơn học (Ngthi) phải lớn hơn ngày kết thúc học mơn học đó (Denngay). ⚫ Nội dung: kq KETQUATHI (gd GIANGDAY  hv HOCVIEN (gd.Malop=hv.Malopkq.Mamh=gd.Mamh) gd.Denngay < kq.Ngthi) ⚫ Bối cảnh: GIANGDAY, HOCVIEN, KETQUATHI 92
  93. Ràng buộc liên thuộc tính ⚫ Bảng tầm ảnh hưởng: R7 Thêm Xĩa Sửa HOCVIEN - - +(Malop) GIANGDAY - - +(Denngay) KETQUATHI + - +(Ngthi) 93
  94. RBTV do thuộc tính tổng hợp ⚫ Là ràng buộc giữa các thuộc tính, các bộ trên những quan hệ khác nhau. ⚫ Thuộc tính tổng hợp là thuộc tính được tính tốn từ giá trị của các thuộc tính khác, các bộ khác. 94
  95. RBTV do thuộc tính tổng hợp ⚫ R8: Sỉ số của một lớp là số lượng học viên thuộc lớp đó ⚫ Nội dung: lp LOP (lp.Siso = Count(hv HOCVIEN  hv.Malop = lp.Malop)(*)) ⚫ Bối cảnh: quan hệ LOP, HOCVIEN ⚫ Bảng tầm ảnh hưởng: R8 Thêm Xĩa Sửa LOP + - +(Siso) HOCVIEN + + +(Malop) 95
  96. BÀI TẬP 1 Cho lược đồ CSDL hệ gồm các quan hệ sau: ⚫ GIAOVIEN (MaGV,TenGV,Ngsinh,Diachi,NgVL,SoDT) ⚫ DETAI (MaDT, TenDT, NgBD,NgKT,MaGV) ⚫ SINHVIEN (MaSV, TenSV, Ngsinh, Gioitinh, MaDT) 96
  97. Phát biểu chặc chẽ ràng buộc sau: ⚫ R1: Ngày kết thúc của mỗi đề tài phải sau ngày bắt đầu của đề tài đó. ⚫ 1. Phát biểu: dùng ngơn ngữ tân từ biến n bộ  d DETAI (d.NgBD < d.NgKT) ⚫ 2. Bối cảnh: DETAI ⚫ 3. Tầm ảnh hưởng: R1 Thêm Xĩa Sửa DETAI + - +(NgBD,NgKT) 97
  98. Phát biểu chặc chẽ ràng buộc sau ⚫ R2: Khi GV hướng dẫn 1 DT thì ngày bắt đầu của đề tài phải sau ngày vào làm của GV đó. ⚫ 1. Phát biểu: dùng ngơn ngữ tân từ biến n bộ  d DETAI (g GIAOVIEN (g.MaGV=d.MaGV) g.NgVL<d.NgBD) ⚫ 2. Bối cảnh: DETAI,GIAOVIEN ⚫ 3. Tầm ảnh hưởng: R2 Thêm Xĩa Sửa GIAOVIEN - - +(NgVL) DETAI + - +(NgBD,MaGV) 98
  99. BÀI TẬP Cho lược đồ CSDL quản lý bán hàng như sau: KHACHTV (MAKH,HOTEN, DCHI,SODT,NGSINH, DOANHSO,NGGN) NHANVIEN (MANV,HOTEN,NGSINH,NGVL,HESO, MUCLUONG) HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) CTHD (SOHD,MASP,SL) SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA) 99
  100. Phát biểu chặc chẽ ràng buộc sau Câu 0: Khố ngoại: các sản phẩm bán ra phải cĩ trong danh mục các sản phẩm ⚫ c CTHD (s SANPHAM (c.MASP = s.MASP)) hoặc CTHD[MASP]  SANPHAM[MASP] ⚫ Bối cảnh: CTHD, SANPHAM ⚫ Bảng tầm ảnh hưởng: Thêm Xĩa Sửa CTHD +(MASP) - + (MASP) SANPHAM - + - (*) 100
  101. Phát biểu chặc chẽ ràng buộc sau Câu 1: Tất cả các sản phẩm cĩ giá từ 1.000 trở lên ⚫ Phát biểu: s SANPHAM (s.GIA 1.000) ⚫ Bối cảnh: SANPHAM ⚫ Bảng tầm ảnh hưởng: Thêm Xĩa Sửa SANPHAM +(GIA) - + (GIA) 101
  102. Phát biểu chặc chẽ ràng buộc sau Câu 2: Ngày gia nhập của khách hàng thành viên phải lớn hơn ngày sinh của người đó ⚫ Phát biểu: k KHACHTV (k.NGGN> k.NGSINH) ⚫ Bối cảnh: KHACHTV ⚫ Bảng tầm ảnh hưởng: Thêm Xĩa Sửa KHACHTV + (NGGN, - + (NGGN,NGSINH) NGSINH) 102
  103. Phát biểu chặc chẽ ràng buộc sau ⚫ Câu 3: Những nhân viên cùng hệ số lương thì cùng mức lương ⚫ Phát biểu: n1,n2 NHANVIEN (n1.HESO=n2.HESO n1.MUCLUONG=n2.MUCLUONG) ⚫ Bối cảnh: NHANVIEN ⚫ Bảng tầm ảnh hưởng: Thêm Xĩa Sửa NHANVIEN + - + (MUCLUONG,HESO) 103
  104. Phát biểu chặc chẽ ràng buộc sau Câu 4: Ngày nhân viên bán hàng phải lớn hơn hoặc bằng ngày đầu tiên vào làm của nhân viên đó. ⚫ h HOADON (n NHANVIEN(h.MANV=n.MANV) n.NGVL<=h.NGHD) ⚫ Bối cảnh: HOADON, NHANVIEN ⚫ Bảng tầm ảnh hưởng: Thêm Xĩa Sửa HOADON + - +(MANV,NGHD) NHANVIEN - - + (NGVL) 104
  105. Phát biểu chặc chẽ ràng buộc sau Câu 5: Mỗi hĩa đơn phải cĩ ít nhất một chi tiết hĩa đơn ⚫ h HOADON (c CTHD (h.SOHD=c.SOHD)) Hoặc h HOADON (Count c CTHD  c.SOHD=h.SOHD(c.SOHD) >=1) ⚫ Bối cảnh: HOADON, CTHD ⚫ Bảng tầm ảnh hưởng: Thêm Xĩa Sửa HOADON + - - CTHD - + + (SOHD) 105
  106. Phát biểu chặc chẽ ràng buộc sau Câu 6: Doanh số của một khách hàng thành viên là tổng các trị giá hĩa đơn mà khách hàng thành viên đĩ đã mua ⚫ k KHACHTV (k.DOANHSO= ∑h HOADON  h.MAKH=k.MAKH (h.TRIGIA)) ⚫ Bối cảnh: KHACHTV, HOADON ⚫ Bảng tầm ảnh hưởng: Thêm Xĩa Sửa HOADON + + + (MAKH,TRIGIA) KHACHTV + - + (DOANHSO) 106
  107. Ngơn ngữ đại số quan hệ ⚫ Đại số quan hệ cĩ nền tảng tốn học (cụ thể là lý thuyết tập hợp) để mơ hình hĩa CSDL quan hệ. Đối tượng xử lý là các quan hệ trong cơ sở dữ liệu quan hệ. ⚫ Chức năng: ⚫ Cho phép mơ tả các phép tốn rút trích dữ liệu từ các quan hệ trong cơ sở dữ liệu quan hệ. ⚫ Cho phép tối ưu quá trình rút trích bằng các phép tốn có sẵn của lý thuyết tập hợp. 107
  108. Các phép tốn trên đại số quan hệ ➢ Phép chọn ➢ Phép chiếu ➢ Các phép tốn trên tập hợp ➢ Phép kết ➢ Phép chia ➢ Hàm tính tốn và gom nhĩm 108
  109. Giới thiệu ⚫ Có năm phép tốn cơ bản: ⚫ Chọn (  ) hoặc ( : ) ⚫ Chiếu ( ) hoặc ( [] ) ⚫ Tích ( ) ⚫ Hiệu ( ) ⚫ Hội ( − )  109
  110. Giới thiệu ⚫ Các phép tốn khác khơng cơ bản nhưng hữu ích: ⚫ Giao (  ) ⚫ Kết ( ) ⚫ Chia (  ) ⚫ Phép bù (  ) ⚫ Đổi tên ( ) ⚫ Phép gán (  ) ⚫ Kết quả sau khi thực hiện các phép tốn là các quan hệ, do đó cĩ thể kết hợp giữa các phép tốn để tạo nên phép tốn mới. 110
  111. Phép chọn (Selection) ⚫ Trích chọn các bộ (dịng) từ quan hệ R. Các bộ được trích chọn phải thỏa mãn điều kiện chọn p. ⚫ Ký hiệu:  p(R) hay R:(p) ⚫ Định nghĩa:  p(R)={t/t R, p(t)} p(t): thỏa điều kiện p ⚫ Kết quả trả về là một quan hệ, cĩ cùng danh sách thuộc tính với quan hệ R. Khơng cĩ kết quả trùng. ⚫ Phép chọn cĩ tính giao hốn  ( (R))= ( (R))= (R) p1 p2 p2 p1 (p1 p2) 111
  112. Ví dụ phép chọn ▪ Tìm những học viên “Nam’ cĩ nơi sinh ở ‘TpHCM’ Gioitinh=‘Nam’  Noisinh=‘TpHCM’(HOCVIEN) HOCVIEN:(Gioitinh=‘Nam’  Noisinh=‘TpHCM’) HOCVIEN Mahv HoTen Gioitin Noisinh Malop h K1103 Ha Duy Lap Nam Nghe An K11 K1102 Tran Ngoc Han Nu Kien K11 Giang K1104 Tran Ngoc Linh Nu Tay Ninh K11 K1105 Tran Minh Long Nam TpHCM K11 K1106 Le Nhat Minh Nam TpHCM K11 112
  113. Phép chiếu (Project) ⚫ Sử dụng để trích chọn giá trị một vài thuộc tính của quan hệ (R) R[A,A , ,A ] ⚫ Ký hiệu: A , A , , A hay 1 2 k 1 2 k trong đó Ai là tên các thuộc tính được chiếu. ⚫ Kết quả trả về một quan hệ có k thuộc tính theo thứ tự như liệt kê. Các dịng trùng nhau chỉ lấy một. ⚫ Phép chiếu khơng có tính giao hốn 113
  114. Ví dụ ▪ Tìm mã số, họ tên những học viên “Nam’ ở ‘TpHCM’ Mahv,Hoten(Gioitinh=‘Nam’Noisinh=‘TpHCM’(HOCVIEN)) (HOCVIEN:(Gioitinh=‘Nam’  Noisinh=‘TpHCM’))[Mahv,Hoten] HOCVIEN Mahv HoTen Gioitin Noisinh Malop h K1103 Ha Duy Lap Nam Nghe An K11 K1102 Tran Ngoc Han Nu Kien K11 Giang K1104 Tran Ngoc Linh Nu Tay Ninh K11 K1105 Tran Minh Long Nam TpHCM K11 114 K1106 Le Nhat Minh Nam TpHCM K11
  115. Các phép tốn tập hợp ➢ Phép hội ➢ Phép trừ ➢ Phép giao ➢ Phép tích 115
  116. Giới thiệu ⚫ Các phép tốn thực hiện trên 2 quan hệ xuất phát từ lý thuyết tập hợp của tốn học: phép hội (RS), phép giao (RS), phép trừ (R-S), phép tích (R S). ⚫ Đối với các phép hội, giao, trừ, các quan hệ R và S phải khả hợp: ⚫ Số lượng thuộc tính của R và S phải bằng nhau: R(A1,A2, An) và S(B1,B2, Bn) ⚫ Miền giá trị của thuộc tính phải tương thích dom(Ai)=dom(Bi) ⚫ Quan hệ kết quả của phép hội, giao, trừ có cùng tên thuộc tính với quan hệ đầu tiên. 116
  117. Phép hội (Union) ⚫ Ký hiệu: RS ⚫ Định nghĩa: R  S = { t | t R  t S } trong đó R,S là hai quan hệ khả hợp. ⚫ Ví dụ: Học viên được khen thưởng đợt 1 hoặc đợt 2 DOT1 DOT2 Mahv Hoten Mahv Hoten Mahv Hoten K1101 Le Kieu My K1103 Le Van Tam K1103 Le Van Tam K1101 Le Kieu My K1114 Tran Ngoc Han K1114 Tran Ngoc Han K1114 Tran Ngoc Han K1203 Le Thanh Hau K1203 Le Thanh Hau K1308 Nguyen Gia K1308 Nguyen Gia 117 DOT1DOT2
  118. Phép trừ (Set Difference) ⚫ Ký hiệu: R-S ⚫ Định nghĩa: R − S = { t | t R  t S } trong đó R,S là hai quan hệ khả hợp. ⚫ Ví dụ: Học viên được khen thưởng đợt 1 nhưng khơng được khen thưởng đợt 2 DOT1 DOT2 Mahv Hoten Mahv Hoten Mahv Hoten K1103 Le Van Tam K1103 Le Van Tam K1101 Le Kieu My K1203 Le Thanh Hau K1114 Tran Ngoc Han K1114 Tran Ngoc Han K1308 Nguyen Gia K1203 Le Thanh Hau K1308 Nguyen Gia DOT1- DOT2 118
  119. Phép giao (Set-Intersection) ⚫ Ký hiệu: RS ⚫ Định nghĩa: R  S = { t | t R  t S } trong đó R,S là hai quan hệ khả hợp. ⚫ Hoặc RS = R – (R – S) ⚫ Ví dụ: Học viên được khen thưởng cả hai đợt 1 và 2 DOT1 DOT2 Mahv Hoten Mahv Hoten Mahv Hoten K1114 Tran Ngoc Han K1103 Le Van Tam K1101 Le Kieu My K1114 Tran Ngoc Han K1114 Tran Ngoc Han K1203 Le Thanh Hau DOT1 DOT2 K1308 Nguyen Gia 119
  120. Phép tích ⚫ Ký hiệu: R S ⚫ Định nghĩa: R S ={t t /t Rt S} R s R s ⚫ Nếu R có n bộ và S có m bộ thì kết quả là n*m bộ KQ(A1,A2, Am,B1,B2, Bn)  R(A1,A2, Am) S(B1,B2, Bn) ⚫ Phép tích thường dùng kết hợp với các phép chọn để kết hợp các bộ có liên quan từ hai quan hệ. ⚫ Ví dụ: từ hai quan hệ HOCVIEN và MONHOC, có tất cả những trường hợp nào “học viên đăng ký học mơn học”, giả sử khơng có bất kỳ điều kiện nào 120
  121. Phép tích HOCVIEN MONHOC Mahv Hoten Mamh Mahv Hoten Mamh K1103 Le Van Tam CTRR K1103 Le Van Tam CTRR K1114 Tran Ngoc Han CTRR K1114 Tran Ngoc Han THDC K1203 Le Thanh Hau CTRR K1203 Le Thanh Hau CTDL K1103 Le Van Tam THDC K1114 Tran Ngoc Han THDC K1203 Le Thanh Hau THDC K1103 Le Van Tam CTDL K1114 Tran Ngoc Han CTDL K1203 Le Thanh Hau CTDL HOCVIEN MONHOC 121
  122. Phép kết ➢ Phép kết ➢ Phép kết bằng, phép kết tự nhiên ➢ Phép kết ngồi 122
  123. Phép kết (Theta-Join) ⚫ Theta-join (): Tương tự như phép tích kết hợp với phép chọn. Điều kiện chọn gọi là điều kiện kết. p ⚫ Ký hiệu: R S trong đó R,S là các quan hệ, p là điều kiện kết ⚫ Các bộ cĩ giá trị NULL tại thuộc tính kết nối khơng xuất hiện trong kết quả của phép kết. ⚫ Phép kết với điều kiện tổng quát gọi là -kết với là một trong những phép so sánh ( ,=, , , , ) 123
  124. Phép kết A1 B2 R S R S A1 A2 B1 B2 B3 1 2 8 0 4 A1 A2 B1 B2 B3 1 2 0 2 8 1 2 1 0 7 1 8 7 8 7 1 8 8 0 4 0 0 8 0 4 1 8 1 0 7 8 4 1 0 7 8 4 0 2 8 0 3 2 1 5 8 4 8 0 4 8 4 1 0 7 8 4 2 1 5 124
  125. Phép kết bằng, kết tự nhiên ⚫ Nếu là phép so sánh bằng (=), phép kết gọi là phép kết bằng (equi-join). Mahv=Trglop Ký hiệu: HOCVIEN LOP ⚫ Nếu điều kiện của equi-join là các thuộc tính giống nhau thì gọi là phép kết tự nhiên (natural-join). Khi đó kết quả của phép kết loại bỏ bớt 1 cột (bỏ 1 trong 2 cột giống nhau) Ký hiệu: HOCVIEN * KETQUATHI hoặc Mahv HOCVIEN KETQUATHI 125
  126. Phép kết ngồi (outer join) ⚫ Mở rộng phép kết để tránh mất thơng tin ⚫ Thực hiện phép kết và sau đó thêm vào kết quả của phép kết các bộ của quan hệ mà khơng phù hợp với các bộ trong quan hệ kia. ⚫ Có 3 loại: ⚫ Left outer join R S ⚫ Right outer join R S ⚫ Full outer join R S ⚫ Ví dụ: In ra danh sách tất cả các học viên và điểm số của các mơn học mà học viên đó thi (nếu có) 126
  127. Phép kết ngồi mahv ⚫ HOCVIEN KETQUATHI HOCVIEN Mahv Hoten Mahv Hoten Mahv Mamh Diem HV01 Nguyen Van Lan HV01 Nguyen Van Lan HV01 CSDL 7.0 HV02 Tran Hong Son HV01 Nguyen Van Lan HV01 CTRR 8.5 HV03 Nguyen Le HV02 Tran Hong Son HV02 CSDL 8.5 HV04 Le Minh HV03 Nguyen Le HV03 CTRR 9.0 HV04 Le Minh Null Null Null KETQUATHI Mahv Mamh Diem HV01 CSDL 7.0 HV02 CSDL 8.5 HV01 CTRR 8.5 HV03 CTRR 9.0 127
  128. Phép chia (Division) ⚫ Định nghĩa: Q = RS ={t /s S, (t,s) R} ⚫ R và S là hai quan hệ, R+ và S+ lần lượt là tập thuộc tính của R và S. Điều kiện S+  là tập con khơng bằng của R+. Q là kết quả phép chia giữa R và S, Q+ = R+ - S+ ⚫ Cĩ thể diễn đạt bằng phép tốn đại số như sau: T  + + (R) 1 R −S T  + + ((S T ) − R) 2 R −S 1 T  T1 −T2 128
  129. Phép chia KETQUATHI MONHOC Mahv Mahv Mamh Diem Mamh Tenmh HV01 HV01 CSDL 7.0 CSDL Co so du lieu HV03 HV02 CSDL 8.5 CTRR Cau truc roi rac HV01 CTRR 8.5 THDC Tin hoc dai cuong KETQUAMONHOC HV03 CTRR 9.0 MONHOC HV01 THDC 7.0 HV02 THDC 5.0 HV03 THDC 7.5 KETQUA  KETQUATHI[Mahv, Mamh] HV03 CSDL 6.0 MONHOC  MONHOC[Mamh] KETQUA 129
  130. Ngơn ngữ SQL ⚫ Là ngơn ngữ chuẩn để truy vấn và thao tác trên CSDL quan hệ ⚫ Là ngơn ngữ phi thủ tục ⚫ Khởi nguồn của SQL là SEQUEL - Structured English Query Language, năm 1974) ⚫ Các chuẩn SQL ⚫ SQL89 ⚫ SQL92 (SQL2) ⚫ SQL99 (SQL3) 130
  131. Các ngơn ngữ giao tiếp ⚫ Ngơn ngữ định nghĩa dữ liệu (Data Definition Language - DDL): cho phép khai báo cấu trúc bảng, các mối quan hệ và các ràng buộc. ⚫ Ngơn ngữ thao tác dữ liệu (Data Manipulation Language - DML): cho phép thêm, xóa, sửa dữ liệu. ⚫ Ngơn ngữ truy vấn dữ liệu (Structured Query Language – SQL): cho phép truy vấn dữ liệu. ⚫ Ngơn ngữ điều khiển dữ liệu (Data Control Language – DCL): khai báo bảo mật thơng tin, cấp quyền và thu hồi quyền khai thác trên cơ sở dữ liệu. 131
  132. Ngơn ngữ định nghĩa dữ liệu 3.1 Lệnh tạo bảng (CREATE) 3.1.1 Cú pháp 3.1.2 Một số kiểu dữ liệu 3.2 Lệnh sửa cấu trúc bảng (ALTER) 3.2.1 Thêm thuộc tính 3.2.2 Sửa kiểu dữ liệu của thuộc tính 3.2.3 Xố thuộc tính 3.2.4 Thêm ràng buộc tồn vẹn 3.2.5 Xố ràng buộc tồn vẹn 3.3 Lệnh xĩa bảng (DROP) 132
  133. Lệnh tạo bảng Cú pháp CREATE TABLE ( [not null], [not null], [not null], khai báo khóa chính, khóa ngoại, ràng buộc ) 133
  134. Lệnh tạo bảng Lược đồ CSDL quản lý bán hàng gồm cĩ các quan hệ sau: KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK, CMND) NHANVIEN (MANV,HOTEN, NGVL, SODT) SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA) HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) CTHD (SOHD,MASP,SL) 134
  135. Lệnh tạo bảng Create table KHACHHANG ( MAKH char(4) primary key, HOTEN varchar(40), DCHI varchar(50), SODT varchar(20), NGSINH smalldatetime, DOANHSO money, NGDK smalldatetime, CMND varchar(10) ) 135
  136. Lệnh tạo bảng Create table CTHD ( SOHD int foreign key references HOADON(SOHD), MASP char(4) foreign key references SANPHAM(MASP), SL int, constraint PK_CTHD primary key (SOHD,MASP) ) 136
  137. Sửa cấu trúc bảng 1. Thêm thuộc tính ALTER TABLE tênbảng ADD têncột kiểudữliệu ⚫ Ví dụ: thêm cột Ghi_chu vào bảng khách hàng ALTER TABLE KHACHHANG ADD GHI_CHU varchar(20) 2. Sửa kiểu dữ liệu thuộc tính ALTER TABLE tênbảng ALTER COLUMN têncột kiểudữliệu_mới ⚫ Lưu ý: Khơng phải sửa bất kỳ kiểu dữ liệu nào cũng được 137
  138. Sửa cấu trúc bảng ⚫ Ví dụ: Sửa Cột Ghi_chu thành kiểu dữ liệu varchar(50) ALTER TABLE KHACHHANG ALTER COLUMN GHI_CHU varchar(50) ⚫ Nếu sửa kiểu dữ liệu của cột Ghi_chu thành varchar(5), mà trước đó đã nhập giá trị cho cột Ghi_chu có độ dài hơn 5ký tự thì khơng được phép. ⚫ Hoặc sửa từ kiểu chuỗi ký tự sang kiểu số, 3. Xĩa thuộc tính ALTER TABLE tên_bảng DROP COLUMN tên_cột ⚫ Ví dụ: xóa cột Ghi_chu trong bảng KHACHHANG ALTER TABLE NHANVIEN DROP COLUMN Ghi_chu 138
  139. Sửa cấu trúc bảng 4. Thêm ràng buộc tồn vẹn UNIQUE tên_cột ALTER TABLE PRIMARY KEY (tên_cột) ADD CONSTRAINT FOREIGN KEY (tên_cột) REFERENCES tên_bảng (cột_là_khĩa_chính) [ON DELETE CASCADE] [ON UPDATE CASCADE] CHECK (tên_cột điều_kiện) 139
  140. Sửa cấu trúc bảng ⚫ Ví dụ ⚫ ALTER TABLE NHANVIEN ADD CONSTRAINT PK_NV PRIMARY KEY (MANV) ⚫ ALTER TABLE CTHD ADD CONSTRAINT FK_CT_SP FOREIGN KEY (MASP) REFERENCES SANPHAM(MASP) ⚫ ALTER TABLE SANPHAM ADD CONSTRAINT CK_GIA CHECK (GIA >=500) ⚫ ALTER TABLE KHACHHANG ADD CONSTRAINT UQ_KH UNIQUE (CMND) 140
  141. Sửa cấu trúc bảng 5. Xĩa ràng buộc tồn vẹn ALTER TABLE tên_bảng DROP CONSTRAINT tên_ràng_buộc ⚫ Ví dụ: ⚫ Alter table CTHD drop constraint FK_CT_SP ⚫ Alter table SANPHAM drop constraint ck_gia ⚫ Lưu ý: đối với ràng buộc khóa chính, muốn xóa ràng buộc này phải xóa hết các ràng buộc khóa ngoại tham chiếu tới nó 141
  142. Lệnh xĩa bảng ⚫ Cú pháp DROP TABLE tên_bảng ⚫ Ví dụ: xóa bảng KHACHHANG. DROP TABLE KHACHHANG ⚫ Lưu ý: khi muốn xóa một bảng phải xóa tất cả những khóa ngoại tham chiếu tới bảng đó trước. 142
  143. Ngơn ngữ thao tác dữ liệu ⚫ Gồm các lệnh: 1. Lệnh thêm dữ liệu (INSERT) 2. Lệnh sửa dữ liệu (UPDATE) 3. Lệnh xóa dữ liệu (DELETE) 143
  144. Thêm dữ liệu ⚫ Cú pháp INSERT INTO tên_bảng (cột1, ,cộtn) VALUES (giá_trị_1, ., giá_trị_n) INSERT INTO tên_bảng VALUES (giá_trị_1, giá_trị_2, , giá_trị_n) ⚫ Ví dụ: ⚫ insert into SANPHAM values('BC01','But chi', 'cay', 'Singapore', 3000) ⚫ insert into SANPHAM(masp,tensp,dvt,nuocsx,gia) values ('BC01','But chi','cay','Singapore',3000) 144
  145. Sửa dữ liệu ⚫ Cú pháp UPDATE tên_bảng SET cột_1 = giá_trị_1, cột_2 = giá_trị_2 . [WHERE điều_kiện] ⚫ Lưu ý: cẩn thận với các lệnh xóa và sửa, nếu khơng có điều kiện ở WHERE nghĩa là xóa hoặc sửa tất cả. ⚫ Ví dụ: Tăng giá 10% đối với những sản phẩm do “Trung Quoc” sản xuất UPDATE SANPHAM SET Gia = Gia*1.1 WHERE Nuocsx=‘Trung Quoc’ 145
  146. Xĩa dữ liệu ⚫ Cú pháp DELETE FROM tên_bảng [WHERE điều_kiện] ⚫ Ví dụ: ⚫ Xóa tồn bộ nhân viên DELETE FROM NHANVIEN ⚫ Xóa những sản phẩm do Trung Quốc sản xuất có giá thấp hơn 10000 DELETE FROM SANPHAM WHERE (Gia <10000) and (Nuocsx=‘Trung Quoc’) 146
  147. Ngơn ngữ truy vấn dữ liệu cĩ cấu trúc 1. Câu truy vấn tổng quát 2. Truy vấn đơn giản 3. Phép kết 4. Đặt bí danh, sử dụng *, distinct 5. Các tốn tử 6. Câu truy vấn con (subquery) 7. Phép chia 8. Hàm tính tốn, gom nhóm 147
  148. Câu truy vấn tổng quát SELECT [DISTINCT] *|tên_cột | hàm FROM bảng [WHERE điều_kiện] [GROUP BY tên_cột] [HAVING điều_kiện] [ORDER BY tên_cột ASC | DESC] 148
  149. Truy vấn đơn giản ⚫ SELECT ⚫ Tương đương phép chiếu của ĐSQH ⚫ Liệt kê các thuộc tính cần hiển thị trong kết quả ⚫ WHERE ⚫ Tương ứng với điều kiện chọn trong ĐSQH ⚫ Điều kiện liên quan tới thuộc tính, sử dụng các phép nối luận lý AND, OR, NOT, các phép tốn so sánh, BETWEEN ⚫ FROM ⚫ Liệt kê các quan hệ cần thiết, các phép kết 149
  150. Truy vấn đơn giản ⚫ Tìm masp, tensp do “Trung Quoc” sản xuất có giá từ 20000 đến 30000 Select masp,tensp From SANPHAM Where nuocsx=‘Trung Quoc’ and gia between 20000 and 30000 150
  151. Phép kết ⚫ Inner Join, Left Join, Right Join, Full Join ⚫ Ví dụ: ⚫ In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007. select KHACHHANG.makh,hoten from KHACHHANG inner join HOADON on KHACHHANG.makh=HOADON.makh where nghd='1/1/2007' 151
  152. Phép kết ⚫ Ví dụ: In ra danh sách tất cả các hóa đơn và họ tên của khách hàng mua hóa đơn đó (nếu có) ⚫ Select sohd, hoten From HOADON left join KHACHHANG on HOADON.makh=KHACHHANG.makh ⚫ Select sohd, hoten From HOADON ,KHACHHANG where HOADON.makh=KHACHHANG.makh 152
  153. Đặt bí danh, sử dụng *, distinct ⚫ Đặt bí danh – Alias: cho thuộc tính và quan hệ: tên_cũ AS tên_mới ⚫ Select manv,hoten as [ho va ten] From NHANVIEN ⚫ Liệt kê tất cả các thuộc tính của quan hệ: ⚫ Select * from Nhanvien ⚫ Select NHANVIEN.* from NHANVIEN ⚫ Distinct: trùng chỉ lấy một lần ⚫ Select distinct nuocsx from SANPHAM ⚫ Sắp xếp kết quả hiển thị: Order by ⚫ Select * from SANPHAM order by nuocsx, gia DESC 153
  154. Tốn tử truy vấn ⚫ Tốn tử so sánh: =, >, =, ⚫ Tốn tử logic: AND, OR, NOT ⚫ Phép tốn: +, - ,* , / ⚫ BETWEEN . AND ⚫ IS NULL, IS NOT NULL ⚫ LIKE (_ %) ⚫ IN, NOT IN ⚫ EXISTS , NOT EXISTS ⚫ SOME, ALL 154
  155. Tốn tử truy vấn ⚫ IS NULL, IS NOT NULL ⚫ Select sohd from HOADON where makh is Null ⚫ Select * from HOADON where makh is Not Null ⚫ Tốn tử so sánh, phép tốn ⚫ Select gia*1.1 as [gia ban] from SANPHAM where nuocsx<>’Viet Nam’ ⚫ Select * from SANPHAM where (gia between 20000 and 30000) OR (nuocsx=‘Viet Nam’) ⚫ Tốn tử IN, NOT IN ⚫ Select * from SANPHAM where masp NOT IN (‘BB01’,’BB02’,’BB03’) 155
  156. Tốn tử so sánh Tốn tử LIKE ⚫ So sánh chuỗi tương đối ⚫ Cú pháp: s LIKE p, p có thể chứa % hoặc _ ⚫ % : thay thế một chuỗi ký tự bất kỳ ⚫ _ : thay thế một ký tự bất kỳ ⚫ Ví dụ: Select masp,tensp from SANPHAM where masp like 'B%01‘ 156
  157. Câu truy vấn con In hoặc Exists ⚫ Ví dụ: Tìm các số hóa đơn mua cùng lúc 2 sản phẩm có mã số “BB01” và “BB02”. ⚫ select distinct sohd from CTHD where masp='BB01' and sohd IN (select distinct sohd from CTHD where masp='BB02') ⚫ select distinct A.sohd from CTHD A where A.masp='BB01' and EXISTS (select * from CTHD B where B.masp='BB02‘ and A.sohd=B.sohd) 157
  158. Câu truy vấn con Not In hoặc Not Exists ⚫ Ví dụ: Tìm các số hóa đơn có mua sản phẩm mã số ‘BB01’ nhưng khơng mua sản phẩm mã số ‘BB02’. ⚫ select distinct sohd from CTHD where masp='BB01' and sohd NOT IN (select distinct sohd from CTHD where masp='BB02') ⚫ select distinct A.sohd from CTHD A where A.masp='BB01' and NOT EXITST (select * from CTHD B where B.masp='BB02‘ and A.sohd=B.sohd) 158
  159. Phép chia Sử dụng NOT EXISTS ⚫ Ví dụ: Tìm số hóa đơn đã mua tất cả những sản phẩm do“ Trung Quoc” sản xuất. ⚫ Select sohd from HOADON where not exists (select * from SANPHAM where nuocsx=‘Trung Quoc’ and not exists (select * from CTHD where HOADON.sohd=CTHD.sohd and CTHD.masp=SANPHAM.masp)) 159
  160. Các hàm tính tốn và gom nhĩm 1. Các hàm tính tốn cơ bản ⚫ COUNT: Đếm số bộ dữ liệu của thuộc tính ⚫ MIN: Tính giá trị nhỏ nhất ⚫ MAX: Tính giá trị lớn nhất ⚫ AVG: Tính giá trị trung bình ⚫ SUM: Tính tổng giá trị các bộ dữ liệu 160
  161. NHANVIEN MANV HOTEN PHAI MANQL PHONG LUONG NV001 Nguyễn Ngọc Linh Nữ Null NC 2.800.000 NV002 Đinh Bá Tiến Nam NV002 DH 2.000.000 NV003 Nguyễn Văn Mạnh Nam NV001 NC 2.300.000 NV004 Trần Thanh Long Nam NV002 DH 1.800.000 NV005 Nguyễn Thị Hồng Vân Nữ NV001 NC 2.500.000 NV006 Nguyễn Minh Nam NV002 DH 2.000.000 NV007 Hà Duy Lập Nam NV003 NC 1.800.000 NV008 Trần Kim Duyên Nữ NV003 NC 1.800.000 NV009 Nguyễn Kim Anh Nữ NV003 NC 2.000.000 161
  162. Ví dụ 1. Tính lương thấp nhất, cao nhất, trung bình và tổng lương của tất cả các nhân viên. 2. Có tất cả bao nhiêu nhân viên 3. Bao nhiêu nhân viên có người quản lý 4. Bao nhiêu phịng ban có nhân viên trực thuộc 5. Tính lương trung bình của các nhân viên 6. Tính lương trung bình của các nhân viên theo từng phịng ban 162
  163. 1. Tính lương thấp nhất, cao nhất, trung bình và tổng lương của tất cả các nhân viên. SELECT min(luong) as thapnhat, max(luong) as caonhat, avg(luong) as trungbinh, sum(luong) as tongluong FROM NhanVien 163
  164. 2. Có tất cả bao nhiêu nhân viên SELECT count(*) FROM NhanVien 3. Bao nhiêu nhân viên có người quản lý ⚫ Select count(*) FROM NhanVien WHERE manql is not null ⚫ SELECT count(Manql) FROM NhanVien 4. Bao nhiêu phịng ban có nhân viên trực thuộc SELECT count(distinct phong) FROM NhanVien 164
  165. Các hàm tính tốn và gom nhĩm 2. Gom nhĩm: mệnh đề GROUP BY ⚫ Sử dụng hàm gom nhóm trên các bộ trong quan hệ. ⚫ Mỗi nhóm bộ bao gồm tập hợp các bộ có cùng giá trị trên các thuộc tính gom nhóm ⚫ Hàm gom nhóm áp dụng trên mỗi bộ độc lập nhau. ⚫ SQL có mệnh đề GROUP BY để chỉ ra các thuộc tính gom nhóm, các thuộc tính này phải xuất hiện trong mệnh đề SELECT 165
  166. 5. Tính lương trung bình của các nhân viên SELECT avg(LUONG) as LUONGTB FROM NhanVien 6. Tính lương trung bình của các nhân viên theo từng phịng ban. SELECT phong, avg(LUONG) as LUONGTB FROM NhanVien GROUP BY phong 166
  167. Các hàm tính tốn và gom nhĩm 3. Điều kiện sau gom nhĩm: mệnh đề HAVING ⚫ Lọc kết quả theo điều kiện, sau khi đã gom nhóm ⚫ Điều kiện ở HAVING được thực hiện sau khi gom nhóm, các điều kiện có liên quan đến thuộc tính Group By ⚫ Ví dụ: tìm phịng có số lượng nhân viên “Nữ” trên 5 người SELECT phong FROM NhanVien WHERE phai = ‘Nữ’ GROUP BY phong HAVING count(manv) > 5 167
  168. data normalization Chuẩn hĩa dữ liệu  Chuẩn hĩa dữ liệu là một quá trình thuận nghịch từng bước để thay thế tập hợp các quan hệ cho trước thành các quan hệ cĩ cấu trúc đơn giản hơn và chuẩn hơn.  Chuẩn hĩa dữ liệu nhằm để cải tiến một thiết kế CSDL luận lý thỏa mãn các ràng buộc tồn vẹn và tránh dữ liệu bị lặp lại khơng cần thiết. 168
  169. data normalization Chuẩn hĩa dữ liệu ❖ Mục đích của chuẩn hĩa dữ liệu  Loại bỏ các bất thường (anomaly) của một quan hệ để cĩ được các quan hệ cĩ cấu trúc tốt hơn, nhỏ hơn.  Quan hệ cĩ cấu trúc tốt (well-structured relation)  Cĩ sự dư thừa dữ liệu là tối thiểu.  Cho phép người sử dụng thêm vào, cập nhật và xĩa bỏ dữ liệu mà khơng gây ra sự mâu thuẫn dữ liệu. 169
  170. data normalization ❖ Các vấn đề sau đây cĩ thể tồn tại trong một lược đồ quan hệ:  Bất thường do sự lặp lại (repetition anomaly)  Bất thường khi cập nhật (update anomaly)  Bất thường khi thêm vào (insertion anomaly)  Bất thường khi xĩa bỏ (deletion anomaly) ❖ Qui tắc: Một quan hệ khơng được chứa thơng tin của nhiều hơn một kiểu thực thể. 170
  171. data normalization  update anomaly : Đây là hệ quả của sự lặp lại dữ liệu, thực hiện việc cập nhật cĩ thể gặp nhiều rắc rối: tốn thời gian cập nhật, mâu thuẫn dữ liệu.  repetition anomaly: Thơng tin cĩ thể bị lặp lại khơng cần thiết. Điều này làm lãng phí vùng nhớ lưu trữ.  insertion anomaly: Trong một số trường hợp khơng thể thêm thơng tin mới vào CSDL.  deletion anomaly: Việc xĩa bỏ cĩ thể làm mất thơng tin trong CSDL. 171
  172. Functional Dependency Phụ thuộc hàm ❖ Functional Dependency  Cho r là một quan hệ, X và Y là hai tập thuộc tính của r. Ký hiệu: r(X, Y)  Chúng ta nĩi “X xác định hàm Y” hoặc “Y phụ thuộc hàm vào X”, ký hiệu là X → Y và được gọi là phụ thuộc hàm nếu: u, v r: u[X] = v[X] u[Y] = v[Y] tức là, với mỗi giá trị của X trong r chỉ tương ứng với một giá trỉ của Y.  Khĩa của một quan hệ xác định hàm các thuộc tính khơng khĩa của quan hệ này. 172
  173. Example S # City P # QTY S1 London P1 100 S1 London P2 100 S2 Paris P1 200 S2 Paris P2 200 S3 Delhi P2 300 S4 Kolkata P2 400 S4 Kolkata P2 400 S4 Kolkata P5 400 173
  174. Example ⚫ Phụ thuộc hàm: ( { S#} → {City}) ⚫ Bởi vì mọi bộ trong quan hệ ứng với một giá trị của S# cũng cóm ột giá trị city. ⚫ Bên trái và bên phải của phụ thuộc hàm (FD) đơi khi được gọi là xác định (determinant) và phụ thuộc (dependents) tương ứng. 174
  175. Exercise ⚫ Kiểm tra xem FD hàm nào sau đây là khơng thỏa: 1. → 2. → 3. → 4. → 5. → 6. → 175
  176. Example ⚫ Cho lược đồ quan hệ R(A, B, C, D, E, F) và cá thể hiện dữ liệu cho dưới đây: 176
  177. Phụ thuộc hàm nào là đúng (a) A -> C (b) C -> A (c) B -> E (d) C -> D (e) B -> F (f) BD -> E (g) CD -> E (h) F -> B 177
  178. Functional Dependency  determinant  Trong phụ thuộc hàm X → Y, X được gọi là determinant (Xác định).  partial functional dependency  X → A được gọi là phụ thuộc hàm riêng phần nếu tồn tại Y  X để cho Y → A.  full functional dependency  X → A được gọi là phụ thuộc hàm đầy đủ nếu khơng tồn tại Y  X để cho Y → A.  transitive dependency  X → A được gọi là phụ thuộc bắc cầu nếu tồn tại Y để cho X → Y, Y → A, Y −/→ X và A XY. 178
  179. Armstrong’s axioms Hệ luật suy diễn Armstrong  Armstrong’s axioms  Gồm các luật suy diễn (inference axiom): F1. Phản xạ (reflexivity): Y  X X → Y F2. Gia tăng (augmentation): X → Y XZ → YZ F3. Bắc cầu (transitivity): X → Y và Y → Z X → Z ❖ Các luật suy diễn khác F4. Hợp (additivity): X → Y và X → Z X → YZ F5. Chiếu (projectivity): X → YZ X → Y F6. Bắc cầu giả (pseudotransitivity): X → Y và YZ → W XZ → W 179
  180. Closure of a set of Attributes (Bao đóng của tập thuộc tính) ⚫ Cho một tập thuộc tính X, bao đóng của X (dựa trên tập phụ thuộc hàm F), Ký hiệu: X+, là tất cả các thuộc tính A sao cho X->A có thể được suy dẫn logic từ F. ▪ Ví dụ: Cho lược đồ quan hệ R(A,B,C,D) với các phụ thuộc hàm {A→C, B→D}. ▪ {A}+ = {A,C} ▪ {B}+ = {B,D} ▪ {C}+={C} ▪ {A,B}+ = {A,B,C,D} 180
  181. Thuật tốn tìm X+ ▪ Input: ⚫ R : Lược đồ quan hệ ⚫ F : Tập phụ thuộc hàm ⚫ X  R (Tập thuộc tính mà cần tính bao đóng) ▪ Output: ⚫ X+ là bao đóng của X dựa trên tập phụ thuộc hàm F ▪ Algorithm ▪ X(0) := X ▪ Repeat ▪ X(i+1) := X(i)  Z, trong đó Z là tập thuộc tính thỏa: ⚫ Y→Z trong F, và ⚫ Y  X(i) ▪ Until X(i+1) := X(i) ▪ Return X(i+1) 181
  182. Closure of a Set of Attributes: Example ▪ R = {A,B,C,D,E,G} ▪ F = { {A,B}→{C}, {C}→{A}, {B,C}→{D}, {A,C,D}→{B}, {D}→{E,G}, {B,E}→{C}, {C,G}→{B,D}, {C,E}→{A,G}} ▪ X = {B,D} ->X+? ▪ X(0) = {B,D} , {D}→{E,G} ▪ X(1) = {B,D,E,G}, {B,E}→{C} ▪ X(2) = {B,C,D,E,G}, {C,E}→{A,G} ▪ X(3) = {A,B,C,D,E,G} ▪ X(4) = X(3) ▪ {B,D}+={A,B,C,D,E,G} 182
  183. Uses of Attribute Closure (Sử dụng bao đĩng của tập thuộc tính để) ⚫ Testing for superkey (Kiểm tra siêu khóa) ⚫ Để kiểm tra X có phải là superkey: tính X+, nếu X+ chứa tất cả các thuộc tính của R thì X là superkey. ⚫ X là khóa dự tuyển (candidate key) nếu khơng ai trong số các tập con của nó là khóa. ⚫ Testing functional dependencies: ⚫ Kiểm tra một phụ thuộc hàm X→Y có được suy dẫn từ F. ⚫ Kiểm tra 2 tập phụ thuộc hàm tương đương F+=G+ ⚫ For each functional dependency Y→Z in F ⚫ Compute Y+ with respect to G ⚫ If Z  Y+ then Y→Z is in G+ ⚫ And vice versa 183
  184. Redundancy of FDs (Phụ thuộc hàm dư thừa) ⚫ Tập các phụ thuộc hàm có thể là dư thừa bởi vì chúng có thể suy diễn từ các FDs khác. ⚫ Ví dụ: A→C là dư thừa đối với F: (A→B, B→C,A→ C) ⚫ Một phần của phụ thuộc hàm đơi khi cũng dư thừa. ⚫ Ví dụ: F=(A→ B, B→C, A→C,D) có thể được viết lại: F=(A→ B, B→C, A→D) 184
  185. Keys ⚫ „ Siêu khĩa (superkey) của một lược đồ quan hệ R = {A1, A2, , An} là tập thuộc tính S ⊆ R thỏa tính chất khơng có hai bộ t1 và t2 trong một trạng thái hợp lệ r của R mà t1[S] = t2[S] ⚫ „ Khĩa ( key) K là siêu khóa với tính chất bổ sung là khi xóa thuộc tính nào khỏi K sẽ khiến K khơng cịn là siêu khóa. 185
  186. Keys ⚫ „Nếu lược đồ quan hệ có nhiều hơn một khóa, mỗi khóa sẽ được gọi làkhĩa dự tuyển (candidate key). ⚫ Một trong các khóa dự tuyển được lựa chọn làm khóa chính(primary key), các khóa cịn lại làm khóa phụ(secondary keys). ⚫ „ Thuộc tính khĩa là thuộc tính nằm trong một khóa dự tuyển. ⚫ „ Thuộc tính khơng khĩa khơng phải là thuộc tính khóa 186
  187. Khĩa của quan hệ Định nghĩa: Cho quan hệ r( R ), tập K ⊂ R ⚫ được gọi là khóa của quan hệ rnếu:K+=R nếu bớt một phần tử khỏi K thì bao đóng của nĩ sẽ khác R. Như thế tập K ⊂ R nếu +K =R và (K \ A)+ ≠ R , ∀ A ⊂ R. 187
  188. Khóa dự tuyển và phụ thuộc hàm ⚫ Khóa dự tuyển xác định tất cả các thuộc tính khác trong lược đồ quan hệ R(A, B, C, D, E, F). ⚫ Giả sử có 2 khóa dự tuyển: CK = {A, BD} thì: A > B, C, D, E. F và BD > A, C, E, F 188
  189. Algorithm for Finding a Key Giải thuật tìm khóa ⚫ Bước1 : tạo tập thuộc tính nguồn TN, tập thuộc tính trung gian TG ⚫ Bước2 : if TG =  then lược đồ quan hệ chỉ có một khóa K= TN Ngược lại Qua bước3 ⚫ Bước3 : tìm tất cả các tập con Xi của tập trung gian TG ⚫ Bước4 : tìm các siêu khóa Si bằng cách Xi if (TN  Xi)+ = Q+ then Si = TN  Xi ⚫ Bước5 : tìm khóa bằng cách loại bỏ các siêu khóa khơng tối tiểu. S cịn lại chính là tập khóa cần tìm. 189
  190. Examples ⚫ Tìm khóa cho lược đồ quan hệ R (A, B, C, D) với tập các phụ thuộc hàm cho bên dưới: (a) A > C B > D C > D (b) A >B B > C A > D D > A (c) A > D D > A C > B 190
  191. Tính F+, bao đĩng của tập các PTH F ⚫ Khi thiết kế CSDL quan hệ,chúng ta bắt đầu bằng cách xem xét tập các PTH khả dĩ. ⚫ „ Khảo sát được tất cả các PTH là điều quan trọng, do vậy làm thế nào để có tất cả PTH. ⚫ „ Bao đĩng (closure) của tập PTH F là tập tất cả PTH có thể suy diễn logic từ F. Ta ký hiệu bao đóng của F là F+ ⚫ „ Ta tính F+ bằng cách áp dùng hệ tiên đề Armstrong 191
  192. Tương đương giữa các tập PTH ⚫ „ Hai tập PTH F và G là tương đương nếu: ⚫ „ Mọi PTH của F đều có thể suy được từ G và ⚫ „ Mọi PTH của G đều có thể suy được từ F ⚫ „ Do vậy F và G là tương đương nếu F + = G + ⚫ „ Định nghĩa: F phủ G nếu mọi PTH của G đều suy được từ F ( G + ⊆ F +) ⚫ „ F và G là tương đương nếu F phủ G và G phủ F 192
  193. Thuật tốn EQUIVALENCE kiểm tra F tương đương G ⚫ „Vào: hai tập PTH F và G ⚫ „ Ra: Đúng nếu F tương đương G, sai nếu ngược lại ⚫ „ Cách thức: EQUIVALENCE( F, G) Begin V := Derives (F,G) AND Derives(G,F) Return (V) End. 193
  194. Bài tập kiểm tra tương đương ⚫ „ Kiểm tra 2 tập PTH tương đương? ⚫ „ Giải thích lý do F = { AB -> C, B -> C, A -> D } G = { A -> B, B -> C, C -> D } ⚫ Giải: Hai tập PTH F và G là khơng tương đương vì „ PTH A -> B ∈ G nhưng khơng thể suy được từ F (Bao đóng A+ trên F là AD và khơng chứa B). ⚫ „ PTH C -> D ∈ G cũng khơng suy được từ F (Bao đóng C+ trên F là C và cũng khơng chứa D) 194
  195. Tương đương giữa 2 tập PTH ⚫ „Bài tập: Cho quan hệ Q(ABCDE) với: F = {A -> BC , A -> D, CD -> E} và G = {A -> BCE, A -> ABD, CD -> E} ⚫ F ≡ G ? 195
  196. Các dạng chuẩn ⚫ „ Dạng chuẩn1 : First Normal Form (1NF) ⚫ „ Dạng chuẩn2 : Second Normal Form (2NF) ⚫ „ Dạng chuẩn3 : Third Normal Form (3NF) ⚫ Dạng chuẩn Boyce-Codd: Boyce-Codd Normal Form (BCNF). ⚫ „ Để chuẩn hóa 1NF-> 2NF-> 3NF 196
  197. First Normal Form Dạng chuẩn 1 ➢ Lược đồ quan hệ R ở dạng chuẩn 1 (1NF) nếu mọi thuộc tính của R đều chứa các giá trị nguyên tố (atomic value), giá trị này khơng là một danh sách các giá trị hoặc các giá trị phức hợp (composite value). ➢ Các thuộc tính của lược đồ quan hệ R  Khơng là thuộc tính đa trị (multivalued attribute).  Khơng là thuộc tính phức hợp (composite attribute). 197
  198. Example ⚫ Lược đồ quan hệ sau khơng đạt1 NF 198
  199. Second Normal Form Dạng chuẩn 2 ➢ Lược đồ quan hệ R ở dạng chuẩn 2 (2NF) đối với tập phụ thuộc hàm F nếu R ở dạng chuẩn 1 và mọi thuộc tính khơng khĩa đều phụ thuộc hàm đầy đủ vào mọi khĩa của R. 199
  200. Example ⚫ Lược đồ quan hệ sau khơng đạt 2 NF 200
  201. Third Normal Form Dạng chuẩn 3 ➢ Lược đồ quan hệ R ở dạng chuẩn 3 (3NF) đối với tập phụ thuộc hàm F nếu R ở dạng chuẩn 2 và mọi thuộc tính khơng khĩa đều khơng phụ thuộc bắc cầu vào một khĩa của R. ➢ Lược đồ quan hệ R ở dạng chuẩn 3 đối với tập phụ thuộc hàm F nếu R ở dạng chuẩn 2 và mọi phụ thuộc hàm X → A với A X thì X là một siêu khĩa của R hoặc A là một thuộc tính khĩa. 201
  202. Example ⚫ Cho lược đồ quan hệ Q(A,B,C,D) F={AB→C; D→B; C→ABD}. Hỏi Q có đạt chuẩn3 khơng? 202
  203. Boyce Codd Normal Form Dạng chuẩn Boyce-Codd ➢ Lược đồ quan hệ R ở dạng chuẩn Boyce−Codd (BCNF) đối với tập phụ thuộc hàm F nếu mọi phụ thuộc hàm X → A với A X thì X là một siêu khĩa của R. ➢ Nếu lược đồ quan hệ R ở dạng chuẩn Boyce- Codd thì R cũng ở dạng chuẩn 3. 203
  204. Example ⚫ Q(A,B,C,D,E,I) F={ACD→EBI; CE→AD}. Hỏi Q có đạt chuẩn BC khơng? 204
  205. 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 Đị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. 205
  206. Chuẩn 2: 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. ⚫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. Chuẩn 3: 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 ⚫ Q đạt chuẩn 3 nếu và chỉ nếu mọi pth X→A F với A X đều có X là siêu khóa hay A là thuộc tính khóa ⚫ Nếu Q không có thuộc tính không khóa thì Q đạt chuẩn 3. Chuẩn BC: 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 ⚫ Q đạt chuẩn 3 và khóa của Q chỉ gồm một thuộc tính thì Q đạt chuẩn BC 206
  207. Exercise ⚫ In the instance of the relation R(A,O,T,V,U) shown below, which of the following functional dependencies hold ? A O T V U 1 2 3 4 5 1 4 3 4 5 1 2 4 4 1 a) A O → T; V U → A b) O → V; A V → U c) T → O; A V → U d) A O → T e) O → V; V U → A 207
  208. Exercise ⚫ Table TT {J , D , C , V , N , G } and a set of functional dependencies {J,C,N → V,G D → C,V,G J → D,C,G } The closure of {D } is: a) J D C V N G b) J D C V G c) J C V G d) D C V N e) D C V G 208
  209. Exercise ⚫ In the instance of the relation R(A,B,C,D,E) shown below, which of the following functional dependencies (FD's) hold? A B C D E 1 2 3 4 5 1 4 3 4 5 1 2 4 4 1 I. AB -> C II. B -> D III. DE -> B (a) I only (b) II only (c) I and III only (d) II and III only 209
  210. Exercise ⚫ Let R be a relation with attributes (B,I,N,R,U,L) and let the following functional dependencies hold. { B → I, B→ N, N R→ U, N R→ L , I → U} ⚫ Given the above functional dependencies, which of the following functional dependencies does not hold: a) N R → U L b) B R → L c) B → U d) I→ N R 210
  211. Exercise ⚫ Consider a relation R(A,B,C) with the following functional dependencies: A → B;B → C and B → A ⚫ The number of superkeys of R is: (a) 2 (b) 3 (c) 5 (d) 6 211
  212. Exercise ⚫ Giả sử ta có lược đồ quan hệ Q(C,D,E,G,H,K) và tập phụ thuộc hàm F như sau; {CK→ H; C →D; E→C; E →G; CK →E} ⚫ Tìm tất cả các khóa của Q. ⚫ Xác định dạng chuẩn của Q. 212
  213. Exercise ⚫ Consider the relational schema R(A,B,C,D,E,G) and FDs {ABC -> D; B -> E; C -> G; EG -> D }. Which of the attributes are extraneous in the functional dependency, ABC -> D? (a) A (b) B (c) C (d) none of the above 213
  214. Exercise ⚫ Table Q(A,B,C) and a set of functional dependencies AB → C; C → A; Table Q is: a) 1 NF b) 2 NF c) 3 NF d) BCNF 214