Nhập môn Cơ sở dữ liệu - Chương 1: Tổng quan về cơ sở dữ liệu

Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 1 1 khai.hq@ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Tham khảo tài liệu [A]: Chapter 1: Introduction to Databases 2 khai.hq@ou.edu.vn Nội dung chương 1 (1) 1. Một số ví dụ sử dụng hệ thống CSDL 2. Đặc điểm của hệ thống lưu DL theo file 3. Nhược điểm của hệ thống lưu DL theo file 4. Ý nghĩa của thuật ngữ CSDL (database) 5. Ý nghĩa của thuật ngữ hệ quản trị CSDL (DBMS) (DBMS: Database Management System) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 2 3 khai.hq@ou.edu.vn Nội dung chương 1 (2) 6. Các chức năng chính của DBMS 7. Các thành phần chính của môi trường DBMS 8. Vai trò của con người trong môi trường DBMS 9. Lịch sử phát triển của các hệ DBMS 10. Ưu điểm và nhược điểm của DBMS 4 khai.hq@ou.edu.vn Ví dụ về các ứng dụng CSDL ‹Mua bán ở siêu thị ‹Mua bán dùng thẻ tín dụng ‹ Đặt vé đi nghỉ mát tại trạm giao dịch du lịch ‹ Đọc, mượn sách tại thư viện ‹ Thanh lý hợp đồng bảo hiểm ‹ Sử dụng mạng internet ‹Học tập, nghiên cứu tại trường đại học Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 3 5 khai.hq@ou.edu.vn Hệ thống lưu DL theo file (File-based System) ‹ Là tập hợp một số chương trình ứng dụng để phục vụ cho các người dùng cuối (end users), ví dụ chương trình giúp người dùng in ra các bảng báo cáo ‹Mỗi chương trình quản lý các dữ liệu có thiết kế riêng cho chương trình đó 6 khai.hq@ou.edu.vn Ví dụ về hệ thống lưu DL theo file: Hệ thống Quản lý cho thuê nhà (gồm 2 chương trình) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 4 7 khai.hq@ou.edu.vn Hạn chế của phương pháp lưu DL theo file (1) 1. Dữ liệu riêng rẽ và cô lập – Mỗi chương trình sử dụng dữ liệu của riêng mình – Người dùng một chương trình khó thấy được những thông tin có ích cho mình khi thông tin này nằm trong dữ liệu của các chương trình khác 2. Dữ liệu bị trùng lắp – Các dữ liệu giống nhau có thể được lưu ở các chương trình khác nhau -> bị dư thừa DL – Các dữ liệu giống nhau có thể được lưu không cùng giá trị, hay không cùng format -> bị mâu thuẫn DL 8 khai.hq@ou.edu.vn Hạn chế của phương pháp lưu DL theo file (2) 3. Dữ liệu lệ thuộc vào chương trình – Cấu trúc của file lưu DL do code của chương trình tạo ra 4. Chương trình lệ thuộc dữ liệu (không tương thích các format file DL khác nhau) – Các chương trình được viết bằng các ngôn ngữ LT khác nhau nên không thể dễ dàng truy xuất các file DL của các chương trình khác 5. Chức năng của chương trình là cố định, không linh hoạt – Các chương trình được viết theo các chức năng định sẳn, nếu có yêu cầu chức năng nào mới thì phải viết một chương trình mới Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 5 9 khai.hq@ou.edu.vn Phương pháp lưu dữ liệu bằng CSDL ‹ Lý do phát sinh phương pháp này: – Không nên đưa việc định nghĩa DL vào chương trình – Nên lưu DL riêng và không phụ thuộc vào chương trình – Nên có sự kiểm soát khi truy xuất và xử lý DL, và việc kiểm soát này không nên đặt trong một chương trình ‹Kết quả: – Hệ quản trị CSDL (DBMS) 10 khai.hq@ou.edu.vn Cơ sở dữ liệu (Database) ‹ Là tập hợp có tính chia sẽ của các DL có quan hệ logic với nhau (và sự miêu tả của các DL này), được thiết kế để đáp ứng nhu cầu về thông tin cho một tổ chức (Shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization) ‹ System catalog (metadata): cung cấp thông tin miêu tả DL, giúp cho chương trình và DL có thể độc lập với nhau ‹ Các DL quan hệ logic với nhau bao gồm: các thực thể, các thuộc tính, và các mối quan hệ của các thông tin trong một tổ chức Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 6 11 khai.hq@ou.edu.vn Hệ quản trị CSDL (DBMS) ‹ Là một hệ thống phần mềm cho phép ngưới dùng có thể định nghĩa, tạo, và duy trì CSDL, cho phép truy xuất CSDL theo cách có kiểm soát 12 khai.hq@ou.edu.vn Ví dụ về hệ quản trị CSDL (DBMS) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 7 13 khai.hq@ou.edu.vn Phương pháp lưu dữ liệu bằng CSDL ‹ Ngôn ngữ định nghĩa dữ liệu (DDL: Data definition language) – Cho phép đặc tả kiểu DL, cấu trúc DL và ràng buộc DL – Tất cả các đặc tả DL được lưu trong CSDL ‹ Ngôn ngữ thao tác dữ liệu (DML: Data manipulation language). – Là công cụ để truy vấn DL 14 khai.hq@ou.edu.vn Phương pháp lưu dữ liệu bằng CSDL ‹ Việc truy xuất CSDL có kiểm soát có thể bao gồm các hệ thống sau: – Hệ thống bảo mật DL – Hệ thống nhất quán DL – Hệ thống kiểm soát đồng thời – Hệ thống kiểm soát việc phục hồi DL – Bảng ghi quyền truy xuất của các users ‹ Dùng cơ chế khung nhìn dữ liệu (view mechanism) – Cung cấp cho người dùng chính xác các DL mà người dùng đang cần Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 8 15 khai.hq@ou.edu.vn Khung nhìn dữ liệu (View) (1) ‹ Cho phép mỗi user có cái nhìn riêng về CSDL ‹Khung nhìn dữ liệu được xem là một phần cơ bản của CSDL 16 khai.hq@ou.edu.vn Khung nhìn dữ liệu (View) (2) ‹Lợi ích của việc dùng view: – Làm giảm sự phức tạp của CSDL – Cung cấp thêm một mức độ an toàn – Cung cấp một cơ chế tùy biến để có thể thay đổi cách trình bày CSDL – Giúp ta có một cái nhìn không thay đổi đối với cấu trúc của một CSDL, cho dù bản thân CSDL đó có thể bị thay đổi Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 9 17 khai.hq@ou.edu.vn Các thành phần của môi trường dùng DBMS (1) 18 khai.hq@ou.edu.vn Các thành phần của môi trường dùng DBMS (2) ‹ Phần cứng (Hardware) – Có thể là một máy PC hay là một mạng máy tính ‹ Phần mềm (Software) – Hệ DBMS, hệ điều hành, phần mềm mạng (nếu cần) và các chương trình ứng dụng ‹ Dữ liệu (Data) – Là DL được dùng và sự miêu tả về DL đó (được gọi là lược đồ DL (schema)) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 10 19 khai.hq@ou.edu.vn Các thành phần của môi trường dùng DBMS (3) ‹ Các thủ tục (Procedures) – Tài liệu hướng dẫn và các qui tắc được áp dụng khi thiết kế CSDL, sử dụng CSDL và DBMS ‹ Người dùng (People) 20 khai.hq@ou.edu.vn Vai trò của con người trong môi trường DBMS ‹ Người quản trị dữ liệu (Data Administrator (DA)) ‹ Người quản trị CSDL (Database Administrator (DBA)) ‹ Người thiết kế CSDL (Database Designers) Mức logic và vật lý (Logical and Physical) ‹ Người lập trình ứng dụng (Application Programmers) ‹ Người sử dụng chương trình (End Users) Mức cơ bản và mức thành thạo (naive and sophisticated) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 11 21 khai.hq@ou.edu.vn Lịch sử các hệ thống cơ sở dữ liệu ‹ Thế hệ đầu tiên (First generation) – Hệ thống CSDL phân cấp (Hierarchical) – Hệ thống CSDL mạng (Network) ‹ Thế hệ thứ hai (Second generation) – Hệ thống CSDL quan hệ (Relational) ‹ Thế hệ thứ ba (Third generation) – Hệ thống CSDL quan hệ - đối tượng (Object Relational) – Hệ thống CSDL hướng đối tượng (Object-Oriented) 22 khai.hq@ou.edu.vn Ưu điểm của DBMS (1) ‹ Có kiểm soát sự dư thừa dữ liệu ‹ Có tính nhất quán dữ liệu (data consistency) ‹ Chứa được nhiều thông tin hơn với cùng số lượng dữ liệu như nhau ‹ Cho phép chia sẽ dữ liệu ‹ Tăng cường sự toàn vẹn của dữ liệu (data integrity) ‹ Tăng độ bảo mật (security) ‹ Có tuân theo các tiêu chuẩn ‹ Tiết kiệm về kích cỡ Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 1: Tổng quan về cơ sở dữ liệu Trang 12 23 khai.hq@ou.edu.vn Ưu điểm của DBMS (2) ‹ Cân đối các yêu cầu có tính xung đột ‹ Tăng cường khả năng truy xuất và đáp ứng của dữ liệu (accessibility and responsiveness) ‹ Nâng cao hiệu suất (productivity) ‹ Bảo trì tốt hơn nhờ dữ liệu độc lập với chương trình ‹ Tăng khả năng sử dụng đồng thời ‹ Cải thiện chức năng sao lưu và phục hồi dữ liệu 24 khai.hq@ou.edu.vn Nhược điểm của DBMS ‹ Phức tạp (Complexity) ‹ Nhược điểm về kích thước (Size) ‹ Nhược điểm về chi phí (Cost of DBMS) ‹ Phải tốn chi phí thêm cho phần cứng ‹ Tốn chi phí vào việc chuyển đổi (Cost of conversion) ‹ Nhược điểm về tốc độ thực hiện ‹ Dễ bị hư hỏng hơn HẾT CHƯƠNG 1 Chương 2: Môi trường của cơ sở dữ liệu Trang 1 Nhập môn cơ sở dữ liệu www.ou.edu.vn 1 khai.hq@ou.edu.vn Chương 2: Môi trường của cơ sở dữ liệu Tham khảo tài liệu [A]: Chapter 2: Database Environment 2 khai.hq@ou.edu.vn Nội dung chương 2 (1) ‹Mục đích của kiến trúc CSDL 3 mức ‹ Nội dung của mức ngoài, mức ý niệm và mức trong (external, conceptual, and internal levels) ‹Mục đích của việc ánh xạ mức ngoài/mức ý niệm và mức ý niệm/mức trong ‹ Ý nghĩa của sự độc lập dữ liệu giữa logic và vật lý ‹ Phân biệt DDL và DML ‹ Phân loại các mô hình dữ liệu Chương 2: Môi trường của cơ sở dữ liệu Trang 2 Nhập môn cơ sở dữ liệu www.ou.edu.vn 3 khai.hq@ou.edu.vn Nội dung chương 2 (2) ‹Mục đích và tầm quan trọng của việc mô hình hóa mức ý niệm ‹ Các chức năng và dịch vụ chính yếu của DBMS ‹ Các thành phần phần mềm của DBMS ‹ Ý nghĩa của kiến trúc client–server và sự tiện lợi của kiến trúc này đối với DBMS ‹ Chức năng và các trường hợp dùng Bộ giám sát việc xử lý giao tác (Transaction Processing Monitor) ‹ Chức năng và tầm quan trọng của catalog hệ thống (system catalog) 4 khai.hq@ou.edu.vn Mục tiêu của kiến trúc 3 mức ‹ Tất cả users có thể truy xuất dữ liệu như nhau ‹ View của một user không bị thay đổi khi các view khác bị thay đổi ‹ Users không cần phải biết chi tiết ở mức vật lý cơ sở dữ liệu được lưu như thế nào Chương 2: Môi trường của cơ sở dữ liệu Trang 3 Nhập môn cơ sở dữ liệu www.ou.edu.vn 5 khai.hq@ou.edu.vn Mục tiêu của kiến trúc 3 mức ‹ Người quản trị CSDL (DBA) có thể thay đổi cấu trúc lưu trữ CSDL nhưng không làm ảnh hưởng đến views của các users ‹ Cấu trúc mức trong của CSDL không bị ảnh hưởng bởi sự thay đổi các yếu tố mức vật lý ‹ DBA có thể thay đổi cấu trúc mức ý niệm nhưng không làm ảnh hưởng đến tất cả các users 6 khai.hq@ou.edu.vn Kiến trúc 3 mức ANSI-SPARC (ANSI-SPARC Three-level Architecture) Chương 2: Môi trường của cơ sở dữ liệu Trang 4 Nhập môn cơ sở dữ liệu www.ou.edu.vn 7 khai.hq@ou.edu.vn Kiến trúc 3 mức ANSI-SPARC ‹Mức ngoài (External Level) – gồm các views của các users – miêu tả từng phần của CSDL có liên quan đến từng user ‹Mức ý niệm (Conceptual Level) – là một view tổng quát cho cả CSDL – miêu tả dữ liệu gì được lưu trong CSDL và mối quan hệ giữa các dữ liệu đó ra sao 8 khai.hq@ou.edu.vn Kiến trúc 3 mức ANSI-SPARC ‹Mức trong (Internal Level) – là sự thể hiện ở mức vật lý của CSDL trên máy tính – miêu tả dữ liệu được lưu như thế nào trong CSDL Chương 2: Môi trường của cơ sở dữ liệu Trang 5 Nhập môn cơ sở dữ liệu www.ou.edu.vn 9 khai.hq@ou.edu.vn Sự khác nhau giữa 3 mức 10 khai.hq@ou.edu.vn Sự độc lập về dữ liệu (Data Independence) (1) ‹ Độc lập về mặt logic: – Loại trừ sự thay đổi của lược đồ mức ngoài khi lược đồ mức ý niệm thay đổi – Cho phép lược đồ mức ý niệm thay đổi (ví dụ khi thêm/bớt thực thể) – Không yêu cầu thay đổi của lược đồ mức ngoài hay viết lại chương trình ứng dụng Chương 2: Môi trường của cơ sở dữ liệu Trang 6 Nhập môn cơ sở dữ liệu www.ou.edu.vn 11 khai.hq@ou.edu.vn Sự độc lập về dữ liệu (Data Independence) (2) ‹ Độc lập về mặt vật lý: – Loại trừ sự thay đổi của lược đồ mức ý niệm khi lược đồ mức trong thay đổi – Cho phép lược đồ mức trong thay đổi (ví dụ khi dùng cách tổ chức file khác nhau, các thiết bị hay các cấu trúc lưu trữ khác nhau) – Không yêu cầu thay đổi lược đồ mức ý niệm hay mức ngoài 12 khai.hq@ou.edu.vn Độc lập dữ liệu của kiến trúc ANSI-SPARC 3 mức Chương 2: Môi trường của cơ sở dữ liệu Trang 7 Nhập môn cơ sở dữ liệu www.ou.edu.vn 13 khai.hq@ou.edu.vn Ngôn ngữ cho CSDL (Database Languages) (1) ‹ Ngôn ngữ định nghĩa dữ liệu (DDL) – Cho phép người quản trị CSDL (DBA) hoặc user miêu tả và đặt tên cho các thực thể, các thuộc tính, và các mối quan hệ cần thiết của ứng dụng – Ngoài ra còn tạo sự toàn vẹn trong kết hợp DL (associated integrity) và sự ràng buộc an toàn DL (security constraints) 14 khai.hq@ou.edu.vn Ngôn ngữ cho CSDL (Database Languages) (2) ‹ Ngôn ngữ thao tác dữ liệu DML – Cung cấp các phép thực hiện thao tác cơ bản trên dữ liệu đang lưu trong CSDL ‹ Ngôn ngữ DML thủ tục (Procedural DML) – Cho phép user ra lệnh một cách chi tiết cho hệ thống phải làm như thế nào (HOW) để thao tác dữ liệu ‹ Ngôn ngữ DML phi thủ tục (Non-Procedural DML) – Cho phép user đưa ra yêu cầu về dữ liệu đang cần (WHAT), chứ không nói cách thức hệ thống phải truy xuất dữ liệu đó như thế nào Chương 2: Môi trường của cơ sở dữ liệu Trang 8 Nhập môn cơ sở dữ liệu www.ou.edu.vn 15 khai.hq@ou.edu.vn Ngôn ngữ cho CSDL (Database Languages) (3) ‹ Ngôn ngữ thế hệ thứ 4 (Fourth Generation Language (4GL)) – Ngôn ngữ truy vấn (Query Languages) ví dụ ngôn ngữ SQL, ngôn ngữ QBE – Công cụ tạo cửa sổ giao diện (Forms Generators) – Công cụ tạo bảng báo cáo (Report Generators) – Công cụ tạo biểu đồ dữ liệu (Graphics Generators) – Công cụ tạo ứng dụng (Application Generators) 16 khai.hq@ou.edu.vn Mô hình dữ liệu (Data Model) (1) ‹ Là tập hợp các khái niệm có liên quan, dùng để miêu tả dữ liệu, mối quan hệ giữa các dữ liệu, và các ràng buộc trên dữ liệu của một tổ chức ‹Mô hình dữ liệu bao gồm: – Một phần cấu trúc (A structural part) – Một phần thao tác (A manipulative part) – Có thể có một tập các qui tắc toàn vẹn (integrity rules) Chương 2: Môi trường của cơ sở dữ liệu Trang 9 Nhập môn cơ sở dữ liệu www.ou.edu.vn 17 khai.hq@ou.edu.vn Mô hình dữ liệu (Data Model) (2) ‹Mục đích – Biểu diễn dữ liệu theo cách thức dể hiểu ‹ Các loại mô hình dữ liệu gồm: – Mô hình lấy đối tượng làm nền tảng (Object-based) – Mô hình lấy record làm nền tảng (Record-based) – Mô hình vật lý (Physical) 18 khai.hq@ou.edu.vn Các mô hình dữ liệu (Data Models) ‹Object-based Data Models – Mô hình thực thể-mối kết hợp (Entity-Relationship) – Mô hình ngữ nghĩa (Semantic) – Mô hình chức năng (Functional) – Mô hình hướng đối tượng (Object-Oriented) ‹ Record-based Data Models – Mô hình quan hệ (Relational Data Model) – Mô hình mạng (Network Data Model) – Mô hình phân cấp (Hierarchical Data Model) ‹ Physical Data Models Chương 2: Môi trường của cơ sở dữ liệu Trang 10 Nhập môn cơ sở dữ liệu www.ou.edu.vn 19 khai.hq@ou.edu.vn Mô hình hóa mức ý niệm (Conceptual modeling) ‹ Lược đồ ý niệm là cốt lõi của hệ thống, nó hỗ trợ tất cả các view của user ‹ Lược đồ phải thể hiện đầy đủ và chính xác các dữ liệu của một tổ chức ‹Mô hình hóa mức ý niệm là quá trình xây dựng một mô hình thông tin, mô hình này độc lập với mức hiện thực chi tiết ‹Kết quả là một mô hình dữ liệu mức ý niệm (conceptual data model) 20 khai.hq@ou.edu.vn Các chức năng của DBMS (1) 1. Lưu, truy xuất và cập nhật dữ liệu 2. Có phân quyền truy xuất cho các user 3. Hỗ trợ giao tác (giao dịch) 4. Có dịch vụ kiểm soát đồng thời 5. Có dịch vụ phục hồi dữ liệu Chương 2: Môi trường của cơ sở dữ liệu Trang 11 Nhập môn cơ sở dữ liệu www.ou.edu.vn 21 khai.hq@ou.edu.vn Các chức năng của DBMS (2) 6. Có dịch vụ kiểm tra quyền truy xuất dữ liệu 7. Hỗ trợ việc giao tiếp dữ liệu (kết hợp được với các software khác để thực hiện) 8. Có dịch vụ toàn vẹn dữ liệu 9. Hỗ trợ việc độc lập dữ liệu giữa CSDL và chương trình 10.Có các dịch vụ tiện ích khác: import, export, thống kê, giám sát, 22 khai.hq@ou.edu.vn Các thành phần của DBMS Chương 2: Môi trường của cơ sở dữ liệu Trang 12 Nhập môn cơ sở dữ liệu www.ou.edu.vn 23 khai.hq@ou.edu.vn Các thành phần của Database Manager (DM) 24 khai.hq@ou.edu.vn Các kiến trúc DBMS đa người dùng ‹ Xử lý từ xa (Teleprocessing) ‹Máy phục vụ file (File-server) ‹Máy khách - máy phục vụ (Client-server) Chương 2: Môi trường của cơ sở dữ liệu Trang 13 Nhập môn cơ sở dữ liệu www.ou.edu.vn 25 khai.hq@ou.edu.vn Kiến trúc xử lý từ xa (Teleprocessing) (1) ‹ Là kiến trúc truyền thống ‹Một máy chính mạnh nối với một số máy đầu cuối (Single mainframe with a number of terminals attached) ‹Khuynh hướng hiện nay ít dùng kiến trúc này, vì máy mainframe rất đắc tiền và các máy đầu cuối ngày càng mạnh hơn 26 khai.hq@ou.edu.vn Kiến trúc xử lý từ xa (Teleprocessing) (2) Chương 2: Môi trường của cơ sở dữ liệu Trang 14 Nhập môn cơ sở dữ liệu www.ou.edu.vn 27 khai.hq@ou.edu.vn Kiến trúc File-Server (1) ‹ Máy File-server được kết nối với nhiều máy trạm (workstation) nhờ mạng máy tính ‹ Database nằm ở máy file-server. ‹ DBMS và applications chạy trên các máy workstation. ‹ Các nhược điểm: – Chiếm đường truyền trên mạng nhiều – Nhiều bảng sao DBMS trên các máy workstation – Phức tạp trong việc kiểm soát đồng thời, phục hồi dữ liệu và toàn vẹn dữ liệu 28 khai.hq@ou.edu.vn Kiến trúc File-Server (2) Chương 2: Môi trường của cơ sở dữ liệu Trang 15 Nhập môn cơ sở dữ liệu www.ou.edu.vn 29 khai.hq@ou.edu.vn Kiến trúc Client-server (1) ‹Máy Server chứa database và chạy DBMS. ‹Máy Client quản lý giao diện với user và chạy các ứng dụng ‹ Các ưu điểm: – Truy xuất rộng hơn đến các database – Tăng tốc độ – Có thể giảm giá thành phần cứng – Giảm chi phí truyền dữ liệu – Tăng tính nhất quán dữ liệu 30 khai.hq@ou.edu.vn Kiến trúc Client-server (2) Chương 2: Môi trường của cơ sở dữ liệu Trang 16 Nhập môn cơ sở dữ liệu www.ou.edu.vn 31 khai.hq@ou.edu.vn Các sơ đồ kết nối của kiến trúc Client-server 32 khai.hq@ou.edu.vn Bộ giám sát việc xử lý giao tác (Transaction Processing Monitor) (1) ‹ Là chương trình kiểm soát việc truyền dữ liệu giữa máy client và máy server ‹ Nhằm tạo ra một môi trường có tính nhất quán ‹ Đặc biệt dùng cho việc xử lý giao tác trực tuyến (Online Transaction Processing (OLTP)) Chương 2: Môi trường của cơ sở dữ liệu Trang 17 Nhập môn cơ sở dữ liệu www.ou.edu.vn 33 khai.hq@ou.edu.vn Bộ giám sát việc xử lý giao tác (2) ‹ Là lớp giữa của kiến trúc Client-Server 3 lớp 34 khai.hq@ou.edu.vn Catalog hệ thống (System Catalog) ‹ Là nơi chứa thông tin metadata (metadata là thông tin miêu tả dữ liệu trong database) ‹ Thường lưu: – Tên của các user hợp lệ – Tên của các mục dữ liệu trong database – Ràng buộc trên các mục dữ liệu (data items) – Các mục dữ liệu user có thể truy xuất được và kiểu truy xuất ‹ Được dùng bởi modules Kiểm soát quyền hợp lệ (Authorization Control) và kiểm tra sự toàn vẹn (Integrity Checker) HẾT CHƯƠNG 2 Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 1 Chương 3: Mô hình Thực thể - Mối kết hợp Tham khảo tài liệu [A]: Chapter 11: Entity-Relationship model 2 khai.hq@ou.edu.vn Nội dung chương 3 ‹ Cách dùng mô hình Entity–Relationship (ER) trong tkiết kế database ‹ Các khái niệm cơ bản liên quan đến mô hình ER ‹Kỹ thuật tạo mô hình ER dùng ngôn ngữ UML (Unified Modeling Language) ‹ Nhận diện và xử lý các lỗi của mô hình ER ‹ Xây dựng mô hình ER theo nhu cầu Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 2 3 khai.hq@ou.edu.vn ER diagram of Branch view of DreamHome 4 khai.hq@ou.edu.vn Các khái niệm của mô hình ER ‹Kiểu của thực thể (Entity types) ‹Kiểu của mối kết hợp (Relationship types) ‹ Thuộc tính (Attributes) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 3 5 khai.hq@ou.edu.vn Kiểu của thực thể (Entity type) ‹Kiểu của thực thể – Là nhóm các đối tượng có cùng các thuộc tính, các đối tượng tồn tại độc lập nhau ‹ Thể hiện của thực thể (Entity occurrence) – Là đối tượng của một thực thể, được xác định là tồn tại duy nhất (không trùng lắp với các đối tượng khác) 6 khai.hq@ou.edu.vn ER diagram of Staff and Branch entity types Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 4 7 khai.hq@ou.edu.vn Kiểu của mối kết hợp (Relationship type) ‹Kiểu của mối kết hợp – Là tập hợp các mối liên kết có ý nghĩa của các kiểu thực thể ‹ Thể hiện của mối kết hợp (Relationship occurrence) – Là mối liên kết cụ thể được xác định là tồn tại duy nhất. Mối liên kết này bao gồm các phần tham gia vào liên kết từ các thực thể 8 khai.hq@ou.edu.vn Mạng ngữ nghĩa của kiểu mối kết hợp “HAS” (Semantic net of Has relationship type) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 5 9 khai.hq@ou.edu.vn ERD của mối kết hợp “Branch Has Staff” 10 khai.hq@ou.edu.vn Các loại mối kết hợp ‹ Cấp của mối kết hợp (Degree of a Relationship) – Là số các thực thể tham gia vào mối kết hợp ‹ Tên của mối kết hợp theo cấp: – Cấp 2: mối kết hợp binary – Cấp 3: mối kết hợp ternary – Cấp 4: mối kết hợp quaternary. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 6 11 khai.hq@ou.edu.vn Mối kết hợp binary “POwns” 12 khai.hq@ou.edu.vn Mối kết hợp Ternary “Registers” Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 7 13 khai.hq@ou.edu.vn Mối kết hợp Quaternary “Arranges” 14 khai.hq@ou.edu.vn Các loại mối kết hợp ‹Mối kết hợp hồi quy (Recursive Relationship) – Là mối kết hợp có một kiểu thực thể tham gia vào hơn một lần với các vai trò khác nhau ‹Mối kết hợp hồi quy nên được đặt tên theo vai trò của thực thể khi tham gia vào mối kết hợp đó Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 8 15 khai.hq@ou.edu.vn Mối kết hợp hồi quy “Supervises” 16 khai.hq@ou.edu.vn Hai thực thể có 2 mối kết hợp khác nhau Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 9 17 khai.hq@ou.edu.vn Thuộc tính (Attribute) ‹ Thuộc tính – Là đặc tính của một kiểu thực thể hay một kiểu mối kết hợp ‹Miền của thuộc tính – Là tập các giá trị có thể có được của thuộc tính 18 khai.hq@ou.edu.vn Khóa (Key) ‹Khóa ứng viên (Candidate Key) – Là tập nhỏ nhất các thuộc tính, tập này xác định được duy nhất từng thể hiện của thực thể ‹Khóa chính (Primary Key) – Là một khóa được chọn ra từ các khóa ứng viên ‹Khóa ghép (Composite Key) – Là khóa có từ 2 thuộc tính trở lên Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 10 19 khai.hq@ou.edu.vn ER diagram of Staff and Branch entities and their attributes 20 khai.hq@ou.edu.vn Kiểu thực thể mạnh và yếu ‹Kiểu thực thể mạnh (Strong Entity Type) – Là kiểu thực thể không phụ thuộc vào kiểu thực thể khác ‹Kiểu thực thể yếu (Weak Entity Type) – Là kiểu thực thể phụ thuộc vào kiểu thực thể khác Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 11 21 khai.hq@ou.edu.vn Strong entity type called Client and weak entity type called Preference 22 khai.hq@ou.edu.vn Relationship called Advertises with attributes Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 12 23 khai.hq@ou.edu.vn Ràng buộc thuộc về cấu trúc (Structural Constraints) (1) ‹ Ràng buộc chính trên các mối kết hợp gọi là mức tham gia kết hợp (multiplicity) ‹Mức tham gia kết hợp: là (đoạn) số lượng các thể hiện của một kiểu thực thể có thể kết hợp với một thể hiện của một kiểu thực thể khác trên một mối kết hợp ‹ Ràng buộc theo các quy tắc nghiệp vụ (business rules) 24 khai.hq@ou.edu.vn Ràng buộc thuộc về cấu trúc (Structural Constraints) (2) ‹ Các mối kết hợp có cấp (degree) phổ biến là 2 (binary), tức là các mối kết hợp trên 2 kiểu thực thể ‹ Các mối kết hợp cấp 2 thường thấy là: – một – một one-to-one (1:1) – một – nhiều one-to-many (1:*) – nhiều – nhiều many-to-many (*:*) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 13 25 khai.hq@ou.edu.vn Mạng ngữ nghĩa của kiểu mối kết hợp “Staff Manages Branch” 26 khai.hq@ou.edu.vn Mức tham gia kết hợp (Multiplicity) của mối kết hợp “Staff Manages Branch” (1:1) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 14 27 khai.hq@ou.edu.vn Mạng ngữ nghĩa của kiểu mối kết hợp “Staff Oversees PropertyForRent” 28 khai.hq@ou.edu.vn Mức tham gia kết hợp (Multiplicity) của “Staff Oversees PropertyForRent” (1:*) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 15 29 khai.hq@ou.edu.vn Mạng ngữ nghĩa của kiểu mối kết hợp “Newspaper Advertises PropertyForRent” 30 khai.hq@ou.edu.vn Mức tham gia kết hợp (Multiplicity) của “Newspaper Advertises PropertyForRent” (*:*) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 16 31 khai.hq@ou.edu.vn Ràng buộc thuộc về cấu trúc (Structural Constraints) (3) ‹Mức tham gia kết hợp của mối kết hợp ghép cấp n – là (đoạn) số lượng các thể hiện của một kiểu thực thể trong mối kết hợp cấp n khi (n-1) giá trị còn lại giữ cố định 32 khai.hq@ou.edu.vn Mạng ngữ nghĩa của kiểu mối kết hợp cấp 3 (ternary) “Registers” với các giá trị Staff và Branch giữ cố định Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 1 33 khai.hq@ou.edu.vn Mức tham gia kết hợp (Multiplicity) của mối kết hợp cấp 3 “Registers” 34 khai.hq@ou.edu.vn Bảng tổng kết Ràng buộc theo mức tham gia mối kết hợp Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 2 35 khai.hq@ou.edu.vn Ràng buộc thuộc về cấu trúc (Structural Constraints) (4) ‹Multiplicity (mức tham gia) dựa vào 2 giá trị giới hạn của mối kết hợp là: cardinality and participation. ‹ Cardinality (Số tham gia tối đa) – Là số luợng lớn nhất các thể hiện của kiểu thực thể tham gia vào mối kết hợp ‹ Participation (Số tham gia) – Là số xác định xem tất cả thể hiện hay chỉ một vài thể hiện của một kiểu thực thể tham gia vào mối kết hợp 36 khai.hq@ou.edu.vn Multiplicity, Cardinality và Participation Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 3 37 khai.hq@ou.edu.vn Các lỗi có thể có ở mô hình ER (1) ‹ Các lỗi có thể xảy ra khi thiết kế mô hình ý niệm, các lỗi có tên “bẫy kết nối” (connection traps.) ‹ Lỗi xảy ra thường do hiểu chưa đúng ý nghĩa của mối kết hợp ‹ 2 lỗi bẫy kết nối chính là: lỗi bẫy mơ hồ (fan traps) và lỗi bẫy lỗ hổng (chasm traps) 38 khai.hq@ou.edu.vn Các lỗi có thể có ở mô hình ER (2) ‹ Fan Trap (lỗi bẫy mơ hồ) – Khi mối quan hệ giữa một số thể hiện của thực thể có tính mơ hồ (ambiguous), không rõ nghĩa. ‹ Chasm Trap (lỗi bẫy lỗ hổng) – Khi không có đủ các mối quan hệ giữa các thể hiện của thực thể Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 4 39 khai.hq@ou.edu.vn Một ví dụ Fan Trap (lỗi bẫy mơ hồ) ‹ At which branch office does staff number SG37 work? 40 khai.hq@ou.edu.vn Cấu trúc lại mô hình ER để loại bỏ lỗi Fan Trap Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 5 41 khai.hq@ou.edu.vn Mạng ngữ nghĩa của mô hình ER đã cấu trúc lại để bỏ lỗi Fan Trap ‹ SG37 works at branch B003. 42 khai.hq@ou.edu.vn Một ví dụ Chasm Trap (lỗi bẫy lỗ hổng) ‹ At which branch office is property PA14 available? Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 3: Mô hình thực thể - Mối kết hợp Trang 6 43 khai.hq@ou.edu.vn Cấu trúc lại mô hình ER để loại bỏ lỗi Chasm Trap 44 khai.hq@ou.edu.vn Mạng ngữ nghĩa của mô hình ER đã cấu trúc lại để bỏ lỗi Chasm Trap HẾT CHƯƠNG 3 Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 1 Chương 4: Mô hình Quan hệ Tham khảo tài liệu [A]: Chapter 3: The Relational Model 2 khai.hq@ou.edu.vn Nội dung chương 4 ‹ Các thuật ngữ của mô hình quan hệ ‹ Cách dùng bảng để miêu tả dữ liệu ‹ Sự tương đồng giữa mối quan hệ trong toán học và mối quan hệ trong mô hình quan hệ ‹ Thuộc tính của mối quan hệ trong cơ sở dữ liệu ‹ Cách xác định khóa ứng viên, khóa chính, và khóa ngoại ‹ Ý nghĩa của “sự toàn vẹn của thực thể” và “sự toàn vẹn trong tham khảo” ‹Mục đích và sự tiện lợi của view Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 2 3 khai.hq@ou.edu.vn Các thuật ngữ của mô hình quan hệ (1) ‹Một quan hệ (relation) là một bảng, gồm có các cột và các hàng – Khái niệm này chỉ áp dụng cho cấu trúc mức logic của cơ sở dữ liệu, chứ không phải cho cấu trúc mức vật lý của cơ sở dữ liệu ‹Một thuộc tính (attribute) là một cột của quan hệ ‹Miền (domain) là tập hợp các giá trị có thể có của một hay nhiều thuộc tính 4 khai.hq@ou.edu.vn Các thuật ngữ của mô hình quan hệ (2) ‹ Bộ (tuple) là một hàng của quan hệ ‹ Cấp (hay mức–degree) là số lượng thuộc tính của quan hệ ‹ Số thể hiện (hay số lượng, hay lực lượng – cardinality) là số bộ của một quan hệ ‹ Cơ sở dữ liệu quan hệ (Relational Database) là một tập các quan hệ đã được chuẩn hóa (normalized relations), mỗi quan hệ có tên khác nhau Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 3 5 khai.hq@ou.edu.vn Một phần các thể hiện của quan hệ “Branch and Staff” 6 khai.hq@ou.edu.vn Ví dụ về miền của thuộc tính (Attribute Domains) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 4 7 khai.hq@ou.edu.vn Các dạng thuật ngữ khác nhau của mô hình quan hệ 8 khai.hq@ou.edu.vn Định nghĩa toán học của mô hình quan hệ (1) ‹ Xét 2 tập hợp D1 và D2, với D1 = {2, 4} và D2 = {1, 3, 5} ‹ Tích Đề-các (Cartesian product) D1 × D2 là tập hợp tất cả các cặp có thứ tự, phần tử đầu là của tập D1 và phần tử sau là của tập D2 D1 × D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)} ‹ Tích Đề-các đã liệt kê ra tất cả các kết hợp có thứ tự có thể có của các phần tử của D1 với các phần tử của D2 Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 5 9 khai.hq@ou.edu.vn Định nghĩa toán học của mô hình quan hệ (2) ‹Một quan hệ là một tập con của tích Đề-các ví dụ: quan hệ R = {(2, 1), (4, 1)} ‹Quan hệ có thể được đặc tả bằng điều kiện chọn ra các cặp của quan hệ, ví dụ: – phần tử sau là số 1: R = {(x, y) | x ∈D1, y ∈D2, and y = 1} – phần tử đầu luôn gấp đôi phần tử sau: S = {(x, y) | x ∈D1, y ∈D2, and x = 2y} 10 khai.hq@ou.edu.vn Định nghĩa toán học của mô hình quan hệ (3) ‹ 3 tập hợp D1, D2, D3 sẽ có tích Đế-các là D1 × D2 × D3 ví dụ: D1 = {1, 3} D2 = {2, 4} D3 = {5, 6} D1 × D2 × D3 = {(1,2,5), (1,2,6), (1,4,5), (1,4,6), (3,2,5), (3,2,6), (3,4,5), (3,4,6)} ‹ Bất kỳ tập con nào của tích Đế-các trên sẽ là một quan hệ, ví dụ: quan hệ R1 = {(1,4,6), (3,4,5)} quan hệ R2 = {(1,4,5), (1,4,6), (1,2,6), (3,4,5), (3,2,5)} Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 6 11 khai.hq@ou.edu.vn Định nghĩa toán học của mô hình quan hệ (4) ‹ Tích Đề-các của n tập hợp (D1, D2, . . ., Dn) là: D1 × D2 × . . . × Dn = {(d1, d2, . . . , dn) | d1 ∈D1, d2 ∈D2, . . . , dn∈Dn} thường được viết là: n X Di i = 1 ‹ Bất kỳ tập con nào của tích Đề-các trên sẽ là một quan hệ trên n tập hợp, mỗi bộ của quan hệ này sẽ có n thành phần 12 khai.hq@ou.edu.vn Khái niệm Quan hệ trong CSDL (Database Relations) ‹Lược đồ (của) quan hệ (Relation schema) – Tên của quan hệ – Tập các thuộc tính – Các miền giá trị tương ứng với mỗi thuộc tính ‹Lược đồ (của) cơ sở dữ liệu quan hệ (Relational database schema) – Là tập hợp các lược đồ quan hệ – Tên của các lược đồ quan hệ phải khác nhau Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 7 13 khai.hq@ou.edu.vn Các thuộc tính của quan hệ ‹ Tên của các quan hệ không được trùng nhau ‹Mỗi ô (cell) trong quan hệ chứa đúng một giá trị đơn trị ‹ Tên thuộc tính không được trùng nhau ‹ Tất cả giá trị của một thuộc tính phải có cùng một miền giá trị (cùng domain) ‹Không có 2 hay nhiều bộ (tuple) giống nhau ‹Không bắt buộc thứ tự của các thuộc tính trong quan hệ ‹Không bắt buộc thứ tự của các bộ của quan hệ 14 khai.hq@ou.edu.vn Khóa của quan hệ (Relational Keys) (1) ‹ Siêu khóa (Superkey) – Là một thuộc tính, hay một tập các thuộc tính xác định duy nhất một bộ trong quan hệ ‹Khóa ứng viên (Candidate Key) – Là siêu khóa – Không có tập con thực sự là siêu khóa – Nếu bỏ bớt đi một thuộc tính thì không còn tính chất xác định duy nhất một bộ trong quan hệ Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 8 15 khai.hq@ou.edu.vn Khóa của quan hệ (Relational Keys) (2) ‹Khóa chính (Primary Key) – Là một khóa ứng viên được chọn để xác định duy nhất các bộ trong quan hệ ‹Khóa thay thế (Alternate Keys) – Là các khóa ứng viên không được chọn làm khóa chính ‹Khóa ngoại (Foreign Key) – Là một thuộc tính, hay một tập các thuộc tính của một quan hệ trùng với một khóa ứng viên của một quan hệ nào đó 16 khai.hq@ou.edu.vn Sự toàn vẹn của quan hệ (Relational Integrity) (1) ‹Giá trị Null – Là giá trị của thuộc tính mà giá trị đó chưa biết hoặc không thể áp dụng vào bộ nào được – Có liên quan đến các dữ liệu không đầy đủ, hay các dữ liệu ngoại lệ – Biểu diễn sự không có dữ liệu – Không phải là giá trị zero hay giá trị rỗng, vì các giá trị này không phải biểu diễn sự không có dữ liệu Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 9 17 khai.hq@ou.edu.vn Sự toàn vẹn của quan hệ (Relational Integrity) (2) ‹ Sự toàn vẹn của thực thể (Entity Integrity) – Không có thuộc tính nào của khóa chính được chứa giá trị NULL ‹ Sự toàn vẹn trong tham khảo (Referential Integrity) – Giá trị của khóa ngoại phải là 1 trong 2 trường hợp sau: * Trùng với một giá trị nơi nó làm khóa ứng viên * Chứa toàn giá trị NULL ‹ Các ràng buộc từ phía công ty (Enterprise Constraints) – Là các quy tắc ràng buộc dữ liệu được quy định bởi người dùng dữ liệu hay người quản trị cơ sở dữ liệu 18 khai.hq@ou.edu.vn Khung nhìn dữ liệu (Views) (1) ‹Quan hệ nền (Base Relation) – Là quan hệ tương ứng với một thực thể trong lược đồ mức ý niệm – Các bộ của quan hệ nền được lưu thật sự (mức vật lý) trong cơ sở dữ liệu ‹Khung nhìn dữ liệu (View) – Là các kết quả có được nhờ sử dụng các toán tử quan hệ áp dụng trên các quan hệ nền – Có vai trò như là các quan hệ mới Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 10 19 khai.hq@ou.edu.vn Khung nhìn dữ liệu (Views) (2) ‹Một view là một quan hệ ảo (virtual relation), quan hệ ảo đó không cần phải lưu vào cơ sở dữ liệu và chỉ được tạo ra khi có nhu cầu ‹ Nội dung của một view được định nghĩa như là một truy vấn (query) trên một hay nhiều quan hệ nền ‹ View có tính chất động, nghĩa là các thay đổi trên các quan hệ nền sẽ ảnh hưởng lập tức lên các view có liên quan 20 khai.hq@ou.edu.vn Mục đích của View ‹ Cung cấp một cơ chế dùng dữ liệu an toàn, mềm dẻo và mạnh mẽ bằng cách dấu bớt các phần không cần thiết đối với các người dùng khác nhau ‹ Cho phép user truy xuất dữ liệu theo cách thức có thể tùy biến được, nhờ vậy cùng một dữ liệu có thể được xem theo các cách khác nhau, bởi các user khác nhau, vào cùng một thời điểm ‹Giúp đơn giản hóa việc thực hiện các phép toán trên các quan hệ nền Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 4: Mô hình Quan hệ Trang 11 21 khai.hq@ou.edu.vn View và việc cập nhật dữ liệu (1) ‹ Việc cập nhật dữ liệu trên các quan hệ nền sẽ ảnh hưởng tức thời đến các view có tham khảo đến các quan hệ nền đó ‹ Nếu view cập nhật được dữ liệu thì các quan hệ nền phía dưới cũng sẽ chịu ảnh hưởng theo ‹ Phân view làm 3 lớp: – Không cập nhật dữ liệu được (not updateable) – Cập nhật dữ liệu được (updateable) – Cập nhật dữ liệu một phần (partially updateable) 22 khai.hq@ou.edu.vn View và việc cập nhật dữ liệu (2) ‹Các giới hạn dùng khi cập nhật dữ liệu với view: - Cho phép cập nhật nếu việc truy vấn chỉ trên 1 quan hệ nền và đã bao gồm khóa ứng viên của quan hệ nền - Không cho phép cập nhật nếu việc truy vấn liên quan đến nhiều quan hệ nền - Không cho phép cập nhật nếu có dùng các phép toán thống kê hay gom nhóm dữ liệu HẾT CHƯƠNG 4 Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 1 Chương 5: Đại số quan hệ & Phép toán quan hệ Tham khảo tài liệu [A]: Chapter 4: Relational Algebra and Relational Calculus 2 khai.hq@ou.edu.vn Nội dung chương 5 ‹ Ý nghĩa của khái niệm “relational completeness” ‹ Cách tạo query trong đại số quan hệ ‹ Cách tạo query trong phép toán quan hệ trên bộ ‹ Cách tạo query trong phép toán quan hệ trên miền ‹ Các loại ngôn ngữ thao tác dữ liệu quan hệ Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 2 3 khai.hq@ou.edu.vn Giới thiệu ‹ Đại số quan hệ và phép toán quan hệ là các ngôn ngữ chuẩn của mô hình quan hệ ‹ Có thể xem: đại số quan hệ là ngôn ngữ thủ tục (cấp cao), và phép toán quan hệ là ngôn ngữ phi thủ tục ‹ Tuy nhiên về mặt lý thuyết thì đại số quan hệ và phép toán quan hệ là tương đương nhau ‹ Ngôn ngữ tạo ra một quan hệ mà quan hệ đó có thể suy ra nhờ phép toán quan hệ thì được xem là ngôn ngữ có tính “relationally complete” 4 khai.hq@ou.edu.vn Đại số quan hệ ‹ Các toán tử của đại số quan hệ áp dụng trên một hay nhiều quan hệ để định nghĩa ra một quan hệ khác mà không làm thay đổi các quan hệ ban đầu ‹ Cả các toán hạng và các kết quả đều là các quan hệ, vì vậy đầu ra của một toán tử có thể là đầu vào của một toán tử khác ‹ Cho phép các biểu thức có thể lồng vào nhau giống như các biểu thức số học. Đặc tính này gọi là closure. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 3 5 khai.hq@ou.edu.vn Đại số quan hệ ‹ 5 toán tử cơ bản trong ĐS quan hệ: Chọn (Selection), Chiếu (Projection), Tích Đề- các (Cartesian product), Hội (Union), và Trừ (Set Difference) ‹ Các toán tử cơ bản thực hiện được hầu hết các thao tác truy xuất dữ liệu ‹ Các toán tử khác: Kết (Join), Giao (Intersection), và Chia (Division). Các toán tử này có thể được biểu diển bằng 5 toán tử cơ bản trên 6 khai.hq@ou.edu.vn Các phép toán trong đại số quan hệ Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 4 7 khai.hq@ou.edu.vn Các phép toán trong đại số quan hệ (tt) 8 khai.hq@ou.edu.vn Phép chọn (Selection or Restriction) ‹ σđiều kiện (R) – Kết quả phép chọn tạo ra một quan hệ trong đó chỉ chứa các bộ của R, mà các bộ đó thỏa mãn điều kiện đã nêu trong phép chọn Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 5 9 khai.hq@ou.edu.vn Ví dụ về phép chọn ‹ Liệt kê danh sách tất cả nhân viên có lương lớn hơn £10,000 σsalary > 10000 (Staff) 10 khai.hq@ou.edu.vn Phép chiếu (Projection) ‹Πcol1, . . . , coln(R) – Kết quả phép chiếu tạo ra một quan hệ trong đó chỉ chứa một tập con theo chiều dọc của R, tức trích ra theo chiều dọc các giá trị của dữ liệu của một số thuộc tính, và loại bỏ các dữ liệu trùng lắp Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 6 11 khai.hq@ou.edu.vn Ví dụ về phép chiếu ‹ Tạo danh sách lương của tất cả nhân viên, chỉ hiện các thuộc tính staffNo, fName, lName, và salary ΠstaffNo, fName, lName, salary(Staff) 12 khai.hq@ou.edu.vn Phép hội (Union) ‹ R ∪ S – Hội của 2 quan hệ R và S tạo ra một quan hệ mà các bộ của nó hoặc thuộc R, hoặc thuộc S, hoặc vừa thuộc R vừa thuộc S – R và S phải có tính tương thích mới hội với nhau được ‹ Nếu R có i bộ và S có j bộ thì phép hội có tối đa (i+j) bộ Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 7 13 khai.hq@ou.edu.vn Ví dụ về phép hội ‹ Liệt kê danh sách các thành phố mà ở đó có chi nhánh hay có tài sản cho thuê Πcity(Branch) ∪ Πcity(PropertyForRent) 14 khai.hq@ou.edu.vn Phép trừ (Set Difference) ‹ R – S – Tạo một quan hệ chứa các bộ thuộc R nhưng không thuộc S – R và S phải có tính tương thích Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 8 15 khai.hq@ou.edu.vn Ví dụ về phép trừ ‹ Liệt kê các thành phố có chi nhánh nhưng không có tài sản cho thuê Πcity(Branch) – Πcity(PropertyForRent) 16 khai.hq@ou.edu.vn Phép giao (Intersection) ‹ R ∩ S – Tạo ra một quan hệ chứa các bộ vừa thuộc R vừa thuộc S – R và S phải có tính tương thích nhau ‹ Biểu diễn phép giao bằng phép trừ: R ∩ S = R – (R – S) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 9 17 khai.hq@ou.edu.vn Ví dụ về phép giao ‹ Liệt kê các thành phố mà ở đó có chi nhánh và có cả tài sản cho thuê Πcity(Branch) ∩ Πcity(PropertyForRent) 18 khai.hq@ou.edu.vn Phép tích Đề-các (Cartesian product) ‹ R X S – Tạo một quan hệ trong đó các bộ của nó chính là sự nối kết của từng bộ của R với từng bộ của S Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 10 19 khai.hq@ou.edu.vn Ví dụ về phép tích Đề-các ‹ Liệt kê các tên và lời nhận xét của các khách hàng đã xem nhà (ΠclientNo, fName, lName(Client)) X (ΠclientNo, propertyNo,comment (Viewing)) 20 khai.hq@ou.edu.vn Ví dụ về phép tích Đề-các và phép chọn ‹ Dùng phép tích Đề-các, rồi phép chọn σClient.clientNo = viewing.clientNo((∏clientNo,fName,lName(Client)) Χ (∏clientNo,propertyNo,comment(Viewing))) ‹Phép tích Đề-các và phép chọn được làm chung lại gọi là phép kết (Join) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 11 21 khai.hq@ou.edu.vn Phép Kết (Join) ‹ Phép kết phát sinh từ phép tích Đề-các ‹ Phép kết thực hiện tương đương với phép chọn trên kết quả của phép tích Đề-các, với một điều kiện chọn phải thỏa 22 khai.hq@ou.edu.vn Phép Kết (Join) (tt) ‹ Các dạng khác nhau của phép kết: – Theta join (phép kết theta) – Equijoin (phép kết tương đương – là 1 trường hợp đặc biệt của phép kết theta) – Natural join (phép kết tự nhiên) – Outer join (phép kết ngoài) – Semijoin (phép nửa kết) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 12 23 khai.hq@ou.edu.vn Phép kết Theta (θ-join) ‹ R FS – Tạo ra 1 quan hệ có các bộ là kết quả từ phép chọn theo điều kiện F trên kết quả của phép tích Đề-các của R và S – Điều kiện F có dạng R.ai θ S.bi trong đó θ là 1 trong 6 phép so sánh , ≥, =, ≠ 24 khai.hq@ou.edu.vn Phép kết Theta (θ-join) (tt) ‹ Có thể biểu diễn phép kết Theta: R FS = σF(R Χ S) ‹ Cấp của phép kết Theta là tổng của cấp 2 quan hệ tham gia ‹ Nếu trong điều kiện F sử dụng dấu = thì có tên mới là phép kết tương đương Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 13 25 khai.hq@ou.edu.vn Ví dụ về phép kết tương đương ‹Liệt kê các tên và lời nhận xét của các khách hàng đã xem nhà (ΠclientNo,fName,lName(Client)) Client.clientNo = Viewing.clientNo (ΠclientNo,propertyNo,comment(Viewing)) 26 khai.hq@ou.edu.vn Phép kết tự nhiên (Natural Join) ‹ R S – Là phép kết tương đương của 2 quan hệ trên tất cả các thuộc tính chung – Trong kết quả sẽ bỏ bớt các thuộc tính chung trùng nhau Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 14 27 khai.hq@ou.edu.vn Ví dụ về phép kết tự nhiên ‹Liệt kê các tên và lời nhận xét của các khách hàng đã xem nhà (ΠclientNo,fName,lName(Client)) (ΠclientNo,propertyNo,comment(Viewing)) 28 khai.hq@ou.edu.vn Phép kết ngoài (Outer join) ‹ Để hiển thị trong kết quả của phép kết các hàng không có các cột kết bằng nhau ‹ R S – là phép kết ngoài bên trái: các bộ của R không trùng các giá trị trong các cột chung vẫn có mặt trong kết quả của phép kết Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 15 29 khai.hq@ou.edu.vn Ví dụ về phép kết ngoài bên trái ‹ Lập bảng báo cáo tình trạng các tài sản cho thuê ΠpropertyNo,street,city(PropertyForRent) Viewing 30 khai.hq@ou.edu.vn Phép nửa kết (Semijoin) ‹ R FS – Tạo ra 1 quan hệ có các bộ là kết quả từ R có tham gia vào phép kết với S ‹ Có thể viết lại phép nửa kết bằng phép Chiếu và phép kết: R FS = ΠA(R F S) A là các thuộc tính của R Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 16 31 khai.hq@ou.edu.vn Ví dụ phép nửa kết ‹ Lập danh sách chi tiết về các nhân viên làm việc ở chi nhánh ở Glasgow. Staff Staff.brancNo = Branch.branchNo and branch.city = ‘Glasgow’ Branch 32 khai.hq@ou.edu.vn Phép chia (Division) ‹ R ÷ S – Tạo ra 1 quan hệ có tập thuộc tính C, mà kết quả từ phép kết từng bộ của C kết hợp với từng bộ của S đều thuộc quan hệ R ‹ Biểu diễn cách khác: T1 ← ΠC(R) T2 ← ΠC((S X T1) – R) T ← T1 – T2 Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 17 33 khai.hq@ou.edu.vn Ví dụ phép chia ‹ Tìm tất cả các khách hàng đã xem tất cả các nhà có 3 phòng (ΠclientNo,propertyNo(Viewing)) ÷ (ΠpropertyNo(σrooms = 3 (PropertyForRent))) 34 khai.hq@ou.edu.vn Phép toán quan hệ (Relational Calculus) ‹ Phép toán quan hệ để diễn đạt “muốn gì?” (what) chứ không diễn đạt phải làm gì (how) để có được điều mong muốn ‹ Trong phép tóan luận lý tiền tố (hay phép toán vị từ), vị từ (predicate ) là 1 hàm có đối số nằm phía sau ‹ Phép toán vị từ có kết quả là đúng (true) hay sai (false) ‹ Phép toán vị từ trên CSDL có 2 loại: phép toán trên bộ và phép toán trên miền của thuộc tính Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 18 35 khai.hq@ou.edu.vn Phép toán quan hệ trên bộ ‹ Có mục đích tìm ra các bộ làm cho vị từ có giá trị true. Sử dụng các biến trên bộ để tìm ‹ Biến trên bộ là biến nhận giá trị là 1 bộ của quan hệ ‹ Ví dụ: giá trị của 1 biến S có thể là 1 bộ của quan hệ Staff: Staff(S) ‹ Tìm tất cả giá trị S: biểu thức sau phải thỏa (true) : {S | P(S)} 36 khai.hq@ou.edu.vn Ví dụ phép toán quan hệ trên bộ ‹ Lập danh sách chi tiết về các nhân viên có lương lớn hơn 10000 {S | Staff(S) ∧ S.salary > 10000} ‹ Lập danh sách 1 cột lương chỉ lấy giá trị lớn hơn 10000 {S.salary | Staff(S) ∧ S.salary > 10000} Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 19 37 khai.hq@ou.edu.vn Phép toán quan hệ trên bộ ‹ Có thể sử dụng lượng từ (quantifier) để cho biết vị từ sẽ áp dụng trên bao nhiêu thể hiện: – Lượng từ “tồn tại”: ∃ – Lượng từ “với mọi”: ∀ 38 khai.hq@ou.edu.vn Ví dụ phép toán quan hệ trên bộ ‹ Lượng từ tồn tại dùng trong công thức để bảo đảm có ít nhất 1 gía trị đúng: Staff(S) ∧ (∃B)(Branch(B) ∧ (B.branchNo = S.branchNo) ∧ B.city = ‘London’) ‹ Có nghĩa là: Tồn tại ít nhất 1 chi nhánh (1 bộ) trong bảng Branch có BranchNo bằng với BranchNo của bộ S đang xét, và Means ‘There exists a Branch tuple that has the same branchNo as the branchNo of the current Staff tuple, S, and is located in London’. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 20 39 khai.hq@ou.edu.vn Phép toán quan hệ trên bộ ‹ Lượng từ “với mọi” ∀ được dùng trên mỗi bộ như: (∀B) (B.city ≠ ‘Paris’) ‹ Có nghĩa ‘Các bộ dữ liệu của các chi nhánh, mà các chi nhánh đó có địa chỉ thành phố không thuộc Paris’ ‹ Ngược lại: ~(∃B) (B.city = ‘Paris’) có nghĩa “không có chi nhánh nào có địa chỉ ở Paris’. 40 khai.hq@ou.edu.vn Phép toán quan hệ trên bộ ‹ Các công thức phải tránh sự mơ hồ và có ý nghĩa ‹ Một công thức tốt được cấu thành từ các thành tố: » R(Si), với Si là 1 biến trên bộ và R là 1 quan hệ » Si.a1 θ Sj.a2 » Si.a1 θ c ‹ Một công thức có thể cấu thành bằng các công thức lồng nhau: » 1 thành tố là 1 công thức » Nếu F1 và F2 là công thức thì có các công thức: F1 ∧ F2, F1 ∨ F2 và ~F1 » Nếu F là công thức và X là 1 biến thì (∃X)(F) và (∀X)(F) cũng là công thức Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 21 41 khai.hq@ou.edu.vn Ví dụ về Phép toán quan hệ trên bộ a) Liệt kê tên tất cả các giám đốc kiếm được hơn £25,000 trong 1 năm: {S.fName, S.lName | Staff(S) ∧ S.position = ‘Manager’ ∧ S.salary > 25000} b) Liệt kê các nhân viên quản lý tài sản ở Glasgow: {S | Staff(S) ∧ (∃P) (PropertyForRent(P) ∧ (P.staffNo = S.staffNo) ∧ P.city = ‘Glasgow’)} 42 khai.hq@ou.edu.vn Ví dụ về Phép toán quan hệ trên bộ c) Liệt kê tên các nhân viên hiện không quản lý bất kỳ tài sản nào: {S.fName, S.lName | Staff(S) ∧ (~(∃P) (PropertyForRent(P)∧(S.staffNo = P.staffNo)))} Hoặc: {S.fName, S.lName | Staff(S) ∧ (∀P) (~PropertyForRent(P) ∨ ~(S.staffNo = P.staffNo)))} Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 22 43 khai.hq@ou.edu.vn Ví dụ về Phép toán quan hệ trên bộ ‹ Liệt kê tên khách hàng đã xem nhà ở Glasgow: {C.fName, C.lName | Client(C) ∧ ((∃V)(∃P) (Viewing(V) ∧ PropertyForRent(P) ∧ ( C.clientNo = V.clientNo) ∧ (V.propertyNo=P.propertyNo)∧P.city =‘Glasgow’))} 44 khai.hq@ou.edu.vn Chú ý về Phép toán quan hệ trên bộ ‹Các biểu thức có thể tạo 1 tập vô hạn không xác định: {S | ~Staff(S)} ‹ Để tránh lỗi này, cần thêm giới hạn về miền giá trị của kết quả biểu thức Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 23 45 khai.hq@ou.edu.vn Phép toán quan hệ trên miền (Domain Relational Calculus) ‹ Dùng các biến có giá trị từ miền chứ không phải từ bộ của quan hệ ‹ Nếu F(d1, d2, . . . , dn) là công thức tạo thành từ các thành tố, và d1, d2, . . . , dn là các biến trên miền thì: {d1, d2, . . . , dn | F(d1, d2, . . . , dn)} Là biểu thức của phép toán quan hệ trên miền 46 khai.hq@ou.edu.vn Ví dụ về Phép toán quan hệ trên miền a) Tìm tên của tất cả các giám đốc kiếm được hơn £25,000 trong 1 năm {fN, lN | ∃sN, posn, sex, DOB, sal, bN) (Staff (sN, fN, lN, posn, sex, DOB, sal, bN) ∧ posn = ‘Manager’ ∧ sal > 25000)} Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 24 47 khai.hq@ou.edu.vn Ví dụ về Phép toán quan hệ trên miền b) Liệt kê các nhân viên quản lý tài sản ở Glasgow: {sN, fN, lN, posn, sex, DOB, sal, bN | (∃sN1,cty)(Staff(sN,fN,lN,posn,sex,DOB,sal,bN) ∧ (PropertyForRent(pN, st, cty, pc, typ, rms, rnt,oN, sN1, bN1) ∧ (sN=sN1) ∧ cty=‘Glasgow’)} 48 khai.hq@ou.edu.vn Ví dụ về Phép toán quan hệ trên miền c) Liệt kê tên các nhân viên hiện không quản lý bất kỳ tài sản nào: {fN, lN | (∃sN) (Staff(sN,fN,lN,posn,sex,DOB,sal,bN) ∧ (~(∃sN1) (PropertyForRent(pN, st, cty, pc, typ, rms, rnt,oN, sN1, bN1) ∧ (sN = sN1))))} Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 25 49 khai.hq@ou.edu.vn Ví dụ về Phép toán quan hệ trên miền d) Liệt kê tên khách hàng đã xem nhà ở Glasgow: {fN, lN | (∃cN, cN1, pN, pN1, cty) (Client(cN, fN, lN,tel, pT, mR) ∧ Viewing(cN1, pN1, dt, cmt) ∧ PropertyForRent(pN, st, cty, pc, typ, rms, rnt,oN, sN, bN) ∧ (cN = cN1) ∧ (pN = pN1) ∧ cty = ‘Glasgow’)} 50 khai.hq@ou.edu.vn Phép toán quan hệ trên miền ‹Mọi biểu thức đại số đều có biểu thức phép toán quan hệ tương đương và ngược lại Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 5: Đại số quan hệ và Phép toán quan hệ Trang 26 51 khai.hq@ou.edu.vn Các ngôn ngữ khác ‹ Các ngôn ngữ phi thủ tục dùng các quan hệ đưa vào đầu vào để lấy được đầu ra theo ý muốn (ví dụ ngôn ngữ SQL). ‹ Các ngôn ngữ đồ họa trình bày với người dùng cấu trúc của các quan hệ, người dùng sẽ thao tác trên đó như là làm ví dụ cho chương trình hiểu cần phải trả về kết quả dữ liệu như thế nào cho đúng với mong muốn (ví dụ ngôn ngữ QBE trong MS Access) 52 khai.hq@ou.edu.vn Các ngôn ngữ khác ‹ Ngôn ngữ thế hệ thứ tư (4GLs) ít dùng câu lệnh, thay vào đó là dùng giao diện trực quan. ‹ Ngôn ngữ thế hệ thứ năm (5GLs) sử dụng ngôn ngữ tự nhiên, nhưng ngôn ngữ này chưa hoàn thiện, còn đang nghiên cứu ở bước đầu. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 1 khai.hq@ou.edu.vn Chương 6: Thao tác dữ liệu với ngôn ngữ SQL Tham khảo tài liệu [A]: Chapter 5: SQL: Data Manipulation 2 khai.hq@ou.edu.vn Nội dung chương 6:
Mục đích và tầm quan trọng của SQL
Truy xuất DL từ CSDL với lệnh SELECT và: – Các điều kiện tại mệnh đề WHERE – Sắp xếp kết quả nhờ ORDER BY – Các hàm thống kê – Gom nhóm DL nhờ GROUP BY và HAVING – Các truy vấn con (Subqueries) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 2 3 khai.hq@ou.edu.vn Nội dung chương 6: (tt) – Kết các bảng với nhau – Thực hiện các phép toán trên tập hợp (UNION, INTERSECT, EXCEPT).
Cập nhật DL dùng INSERT, UPDATE, và DELETE. 4 khai.hq@ou.edu.vn Mục tiêu của ngôn ngữ SQL
Ngôn ngữ của CSDL dùng để: – Tạo CSDL và cấu trúc của các quan hệ – Thực hiện thao tác chèn, sửa, xóa dữ liệu của các quan hệ – Thực hiện các truy vấn dữ liệu
Ngôn ngữ của CSDL phải mạnh và dễ học
Ngôn ngữ của CSDL phải có tính cơ động (khả chuyển) Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 3 5 khai.hq@ou.edu.vn Mục tiêu của ngôn ngữ SQL
Ngôn ngữ SQL có 2 phần chính: – Ngôn ngữ DDL cho việc định nghĩa cấu trúc CSDL – Ngôn ngữ DML cho việc truy xuất và cập nhật dữ liệu
Ngôn ngữ SQL có định dạng mềm dẻo
Ngôn ngữ SQL thuộc loại ngôn ngữ phi thủ tục (chỉ cần miêu tả CÁI GÌ chúng ta muốn) 6 khai.hq@ou.edu.vn Mục tiêu của ngôn ngữ SQL
Ngôn ngữ SQL dùng tiếng Anh chuẩn, ví dụ: CREATE TABLE Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2)); INSERT INTO Staff VALUES ('SG16', 'Brown', 8300); SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 4 7 khai.hq@ou.edu.vn Mục tiêu của ngôn ngữ SQL
Ngôn ngữ SQL có thể dùng cho các loại người sử dụng khác nhau như người quản trị CSDL, người phát triển ứng dụng, và các loại người dùng khác
Hiện nay, ngôn ngữ SQL đã đăng ký chuẩn ISO nên có tính chuẩn mực cao và trở thánh một ngôn ngữ chính thống cho CSDL quan hệ. 8 khai.hq@ou.edu.vn Lịch sử ngôn ngữ SQL
Vào 1974, D. Chamberlin (IBM San Jose Laboratory) định nghĩa ngôn ngữ 'Structured English Query Language' (SEQUEL).
Vào 1976 bảng nâng cấp là SEQUEL/2, nhưng đổi tên thành SQL cho hợp lệ (theo luật lúc đó)
Nhiều người còn phát âm là 'see-quel', mặc dầu cách phát âm chính thức là ‘S-Q-L'.
Vào 1992, ra đời phiên bản ISO standard tên là SQL2 hay SQL/92.
Vào 1999, SQL3 ra đời, có hỗ trợ quản trị dữ liệu hướng đối tượng Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 5 9 khai.hq@ou.edu.vn Viết lệnh SQL
SQL gồm các từ khóa và các từ do user định nghĩa – Từ khóa (Reserved words) là các từ qui định sẳn của ngôn ngữ SQL – Từ do user định nghĩa (User-defined words) như tên quan hệ, tên cột, tên view, 10 khai.hq@ou.edu.vn Viết lệnh SQL
Hầu hết các từ trong câu lệnh SQL không phân biệt chữ thường/chữ HOA, trừ một số hằng ký tự
Để dễ đọc, nên viết câu lệnh SQL theo cách có canh lề và xuống dòng hợp lý: - Mỗi mệnh đề nên viết một dòng - Dóng thẳng hàng lề trái các mệnh đề khi xuống dòng - Nếu một mệnh đề có nhiều phần thì mỗi phần nên viết trên một dòng Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 6 11 khai.hq@ou.edu.vn Viết lệnh SQL
Nên dùng qui ước BNF: - Từ khóa viết HOA - Từ do user định nghĩa viết thường - Dấu | chỉ sự cho phép lựa chọn - Dấu ngoặc nhọn { } chỉ thành phần bắt buộc - Dấu ngoặc vuông [ ] chỉ thành phần không bắt buộc - Dấu ba chấm chỉ sự lặp lại (0 hay nhiều lần). 12 khai.hq@ou.edu.vn Các giá trị hằng
Các giá trị hằng không phải là số phải nằm trong dấu nháy đơn (vd. ‘London’).
Các giá trị hằng là số không nằm trong dấu nháy nào cả (vd. 650.00). Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 7 13 khai.hq@ou.edu.vn Câu lệnh SELECT SELECT [DISTINCT | ALL] {* | [BiểuThứcCột [AS TênMới]] [,...] } FROM TênBảng [BíDanh] [, ...] [WHERE ĐiềuKiện] [GROUP BY DanhSáchCột] [HAVING ĐiềuKiệnChoNhóm] [ORDER BY DanhSáchCột] 14 khai.hq@ou.edu.vn Câu lệnh SELECT FROM Các bảng được dùng WHERE Điều kiện lọc các hàng dữ liệu GROUP BY Tạo nhóm các hàng có giá trị giống nhau ở 1 hay nhiều cột HAVING Điều kiện lọc để lấy 1 số nhóm SELECT Các cột muốn có trong kết quả ORDER BY Sắp thứ tự các hàng trong kết quả Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 8 15 khai.hq@ou.edu.vn Ví dụ 6.1 Lập danh sách tất cả các nhân viên: SELECT staffNo, fName, lName, address, position, sex, DOB, salary, branchNo FROM Staff;
Có thể dùng dấu * để miêu tả “lấy tất cả các cột: SELECT * FROM Staff; 16 khai.hq@ou.edu.vn Ví dụ 6.1: lấy tất cả các cột, tất cả các hàng Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 9 17 khai.hq@ou.edu.vn Ví dụ 6.2: Lấy 1 số cột SELECT staffNo, fName, lName, salary FROM Staff; 18 khai.hq@ou.edu.vn Ví dụ 6.3 Không sử dụng DISTINCT SELECT propertyNo FROM Viewing; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 10 19 khai.hq@ou.edu.vn
Dùng DISTINCT để loại bỏ dl trùng lắp: SELECT DISTINCT propertyNo FROM Viewing; Ví dụ 6.3 Có sử dụng DISTINCT 20 khai.hq@ou.edu.vn Ví dụ 6.4 Dùng các field tính toán SELECT staffNo, fName, lName, salary/12 FROM Staff; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 11 21 khai.hq@ou.edu.vn Ví dụ 6.4 Dùng các field tính toán
Dùng AS TênFieldMới: SELECT staffNo, fName, lName, salary/12 AS monthlySalary FROM Staff; 22 khai.hq@ou.edu.vn Ví dụ 6.5 SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > 10000; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 12 23 khai.hq@ou.edu.vn Ví dụ 6.6 SELECT * FROM Branch WHERE city = 'London' OR city = 'Glasgow'; 24 khai.hq@ou.edu.vn Ví dụ 6.7 SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary BETWEEN 20000 AND 30000; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 13 25 khai.hq@ou.edu.vn Ví dụ 6.7
Thay thế BETWEEN SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary>=20000 AND salary <= 30000; 26 khai.hq@ou.edu.vn Ví dụ 6.8 Toán tử IN SELECT staffNo, fName, lName, position FROM Staff WHERE position IN ('Manager', ‘Supervisor'); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 14 27 khai.hq@ou.edu.vn Ví dụ 6.8 Toán tử IN
Thay thế IN SELECT staffNo, fName, lName, position FROM Staff WHERE position='Manager' OR position=‘Supervisor'; 28 khai.hq@ou.edu.vn Ví dụ 6.9 Toán tử LIKE SELECT clientNo, fName, lName, address, telNo FROM PrivateOwner WHERE address LIKE '%Glasgow%'; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 15 29 khai.hq@ou.edu.vn Ví dụ 6.9 Toán tử LIKE
SQL có 2 biểu tượng thay thế trong mẫu: – %: chuỗi từ 0 đến nhiều ký tự – _ (gạch dưới): đại diện 1 ký tự 30 khai.hq@ou.edu.vn Ví dụ 6.10 dùng giá trị NULL Liệt kê thông tin việc xem tài sản PG4 nếu khi xem nó khách hàng không cho ý kiến SELECT clientNo, viewDate FROM Viewing WHERE propertyNo = 'PG4' AND comment IS NULL; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 16 31 khai.hq@ou.edu.vn Ví dụ 6.10 dùng giá trị NULL
Có thể dùng (IS NOT NULL) cho điều kiện ngược lại 32 khai.hq@ou.edu.vn Ví dụ 6.11 Sắp xếp dữ liệu của kết quả SELECT staffNo, fName, lName, salary FROM Staff ORDER BY salary DESC; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 17 33 khai.hq@ou.edu.vn Ví dụ 6.12 Sắp xếp dl kết quả trên nhiều cột SELECT propertyNo, type, rooms, rent FROM PropertyForRent ORDER BY type, rent DESC; 34 khai.hq@ou.edu.vn Các hàm thống kê trong lệnh SELECT
SQL chuẩn ISO có 5 hàm thống kê: (dùng sau SELECT và HAVING) COUNT đếm SUM tính tổng AVG tính trung bình cộng MIN lấy giá trị nhỏ nhất MAX lấy giá trị lớn nhất Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 18 35 khai.hq@ou.edu.vn Các hàm thống kê trong lệnh SELECT
COUNT, MIN và MAX dùng cho cả các giá trị số và không phải số
SUM và AVG chỉ dùng cho cả các giá trị số
Trừ hàm COUNT(*), các hàm khác loại bỏ giá trị NULL trước rồi mới tính kết quả
COUNT(*) đếm tất cả các hàng kể cả giá trị NULL và trùng lắp
Muốn loại bỏ trùng lắp ta dùng DISTINCT 36 khai.hq@ou.edu.vn Các hàm thống kê trong lệnh SELECT
Các hàm thống kê chỉ dùng sau SELECT và HAVING
Nếu sau SELECT có dùng hàm thống kê thì thường phải có GROUP BY. Lệnh sau là sai: SELECT staffNo, COUNT(salary) FROM Staff; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 19 37 khai.hq@ou.edu.vn Ví dụ 6.13 Dùng hàm COUNT(*) SELECT COUNT(*) AS count FROM PropertyForRent WHERE rent > 350; 38 khai.hq@ou.edu.vn Ví dụ 6.14 Dùng COUNT(DISTINCT) Có bao nhiêu nhà khác nhau được xem trong tháng 5 năm 2001? SELECT COUNT(DISTINCT propertyNo) AS count FROM Viewing WHERE date BETWEEN ‘1-May-01’ AND '31-May-01'; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 20 39 khai.hq@ou.edu.vn Ví dụ 6.15 Dùng COUNT và SUM Tìm số lượng các giám đốc và tổng số lương của họ? SELECT COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff WHERE position = 'Manager'; 40 khai.hq@ou.edu.vn Ví dụ 6.16 Dùng MIN, MAX, AVG SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg FROM Staff; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 21 41 khai.hq@ou.edu.vn Lệnh SELECT có GROUP BY
Dùng mệnh đề GROUP BY để lấy các giá trị thống kê theo từng nhóm dl (1 nhóm là 1 số hàng)
Nội dung sau SELECT và GROUP BY có liên quan mật thiết với nhau: mỗi thành phần sau SELECT phải là 1 giá trị đơn trên từng nhóm dl, sau SELECT có thể là: – Tên các cột – Các hàm thống kê – Các hằng số – Biểu thức cấu thành từ các phần vừa kể trên 42 khai.hq@ou.edu.vn Lệnh SELECT có GROUP BY
Tất cả các tên cột trong SELECT phải xuất hiện trong GROUP BY trừ khi tên cột chỉ dùng trong các hàm thống kê.
Nếu WHERE được dùng với GROUP BY, WHERE được áp dụng trước, sau đó mới hình thành các group từ các hàng dl thỏa mãn điều kiện WHERE.
Chuẩn ISO qui định 2 giá trị NULL là giống nhau để tiện sử dụng với GROUP BY. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 22 43 khai.hq@ou.edu.vn Ví dụ 6.17 Dùng GROUP BY Tìm số nhân viên ở mỗi chi nhánh và tổng số lương của họ? SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff GROUP BY branchNo ORDER BY branchNo; 44 khai.hq@ou.edu.vn Ví dụ 6.17 Dùng GROUP BY Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 23 45 khai.hq@ou.edu.vn Ví dụ 6.18 Dùng HAVING Với mỗi chi nhánh có từ 2 nhân viên trở lên, Tìm số nhân viên ở mỗi chi nhánh đó và tổng số lương của họ? SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff GROUP BY branchNo HAVING COUNT(staffNo) > 1 ORDER BY branchNo; 46 khai.hq@ou.edu.vn Ví dụ 6.18 Dùng HAVING Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 24 47 khai.hq@ou.edu.vn Ví dụ 6.19 Dùng truy vấn con Lập DS nhân viên tại chi nhánh ở '163 Main St'. SELECT staffNo, fName, lName, position FROM Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = '163 Main St'); 48 khai.hq@ou.edu.vn Ví dụ 6.19 Dùng truy vấn con Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 25 49 khai.hq@ou.edu.vn Ví dụ 6.20 Dùng truy vấn con và hàm thống kê Lập DS NV có lương lớn hơn lương trung bình, và cho biết số lương vượt hơn của họ? SELECT staffNo, fName, lName, position, salary – (SELECT AVG(salary) FROM Staff) As SalDiff FROM Staff WHERE salary > (SELECT AVG(salary) FROM Staff); 50 khai.hq@ou.edu.vn Ví dụ 6.20 Dùng truy vấn con và hàm thống kê Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 26 51 khai.hq@ou.edu.vn Ví dụ 6.21 TRUY VẤN CON LỒNG NHAU, sử dụng IN Lập DS tài sản được quản lý bởi nhân viên chi nhánh tại '163 Main St'. SELECT propertyNo, street, city, postcode, type, rooms, rent FROM PropertyForRent WHERE staffNo IN (SELECT staffNo FROM Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = '163 Main St')); 52 khai.hq@ou.edu.vn Ví dụ 6.21 TRUY VẤN CON LỒNG NHAU, sử dụng IN Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 27 53 khai.hq@ou.edu.vn ANY và ALL
ANY và ALL có thể dùng với truy vấn con để tạo ra một cột số
Với ALL, điều kiện chỉ đúng khi nó thỏa với tất cả các giá trị của truy vấn con.
Với ANY, điều kiện đúng khi nó thỏa với bất kỳ giá trị nào của truy vấn con.
Nếu truy vấn con rỗng thì ALL trả về giá trị TRUE, ANY trả về giá trị FALSE.
SOME có thể dùng thay cho ANY. 54 khai.hq@ou.edu.vn Ví dụ 6.22 Dùng ANY/SOME Tìm các nhân viên mà lương của họ lớn hơn ít nhất lương của 1 người làm ở chi nhánh B003. SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > SOME (SELECT salary FROM Staff WHERE branchNo = 'B003'); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 28 55 khai.hq@ou.edu.vn Ví dụ 6.22 Dùng ANY/SOME
Truy vấn con bên trong có kết quả là tập hợp {12000, 18000, 24000} và truy vấn bên ngoài chọn các nhân viên có lương lớn hơn bất kỳ giá trị nào trong 3 giá trị trên của tập hợp. 56 khai.hq@ou.edu.vn Ví dụ 6.23 Dùng ALL Tìm các nhân viên mà lương của họ lớn hơn lương của tất cả mọi nhân viên làm ở chi nhánh B003. SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > ALL (SELECT salary FROM Staff WHERE branchNo = 'B003'); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 29 57 khai.hq@ou.edu.vn Ví dụ 6.23 Dùng ALL 58 khai.hq@ou.edu.vn Truy vấn trên nhiều bảng
Có thể dùng truy vấn con trên cùng bảng dữ liệu với truy vấn chính.
Nếu các cột dữ liệu của kết quả được lấy từ nhiều bảng thì phải sử dụng phép kết bảng.
Để thực hiện phép kết, phải liệt kê các bảng tham gia phép kết vào mệnh đề FROM. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 30 59 khai.hq@ou.edu.vn Truy vấn trên nhiều bảng
Có thể dùng bí danh cho các bảng trong mệnh đề FROM.
Bí danh đứng sau tên bảng, cách tên bảng 1 khoảng trống.
Bí danh giúp tránh sự lầm lẫn (tốt hơn cách chỉ sử dụng tên bảng). 60 khai.hq@ou.edu.vn Ví dụ 6.24 Phép kết đơn giản Lập DS tên tất cả khách hàng có xem nhà và có đưa ra lời nhận xét. SELECT c.clientNo, fName, lName, propertyNo, comment FROM Client C, Viewing v WHERE c.clientNo = v.clientNo; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 31 61 khai.hq@ou.edu.vn Ví dụ 6.24 Phép kết đơn giản
Chỉ có những hàng ở 2 bảng cùng thỏa (c.clientNo = v.clientNo) mới có mặt trong kết quả.
Đây là một phép kết tương đương. 62 khai.hq@ou.edu.vn Dùng phép kết với lệnh JOIN
SQL cung cấp một số cách thực hiện phép kết: FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo FROM Client JOIN Viewing USING clientNo FROM Client NATURAL JOIN Viewing
Trong 3 cách trên, FROM đã thay gồm luôn phần WHERE của phép kết.
Cách thứ nhất tạo ra 2 cột giống hệt nhau. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 32 63 khai.hq@ou.edu.vn Ví dụ 6.26 Kết 3 bảng Với mỗi chi nhánh, liệt kê các nhân viên có quản lý tài sản, bao gồm thông tin thành phố của chi nhánh và mã tài sản được quản lý. SELECT b.branchNo, b.city, s.staffNo, fName, lName, propertyNo FROM branch b, staff s, property_for_rent p WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo ORDER BY b.branchNo, s.staffNo, propertyNo; 64 khai.hq@ou.edu.vn Ví dụ 6.26 Kết 3 bảng
Một cách khác viết FROM : FROM (branch b JOIN Staff s USING branchNo) AS bs JOIN PropertyForRent p USING staffNo Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 33 65 khai.hq@ou.edu.vn Ví dụ 6.27 Nhóm nhiều cột Tìm số lượng tài sản dạng được quản lý bởi từng nhân viên SELECT s.branchNo, s.staffNo, COUNT(*) AS count FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo GROUP BY s.branchNo, s.staffNo ORDER BY s.branchNo, s.staffNo; 66 khai.hq@ou.edu.vn Ví dụ 6.27 Nhóm nhiều cột Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 34 67 khai.hq@ou.edu.vn Câu lệnh SQL thực hiện phép tích Đề-các SELECT [DISTINCT | ALL] {* | columnList} FROM Table1 CROSS JOIN Table2 68 khai.hq@ou.edu.vn Phép kết ngoài
Phép kết ngoài cho phép giữ lại các hàng không thỏa điều kiện kết
Ví dụ xét 2 bảng sau: Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 35 69 khai.hq@ou.edu.vn Phép kết ngoài
Phép kết (trong) của 2 bảng: SELECT b.*, p.* FROM Branch1 b, PropertyForRent1 p WHERE b.bCity = p.pCity; 70 khai.hq@ou.edu.vn Ví dụ 6.28 Phép kết ngoài Liệt kê thông tin tất cả chi nhánh, bên cạnh là thông tin các tài sản cùng thành phố với các chi nhánh. SELECT b.*, p.* FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCity = p.pCity; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 36 71 khai.hq@ou.edu.vn Ví dụ 6.29 Phép kết ngoài bên phải Liệt kê thông tin chi nhánh có cùng thành phố với thông tin các tài sản (liệt kê tất cả các tài sản) SELECT b.*, p.* FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCity = p.pCity; 72 khai.hq@ou.edu.vn Ví dụ 6.30 Phép kết ngoài đầy đủ Liệt kê thông tin tất cả chi nhánh, bên cạnh là thông tin tất cả các tài sản cùng thành phố với các chi nhánh. SELECT b.*, p.* FROM Branch1 b FULL JOIN PropertyForRent1 p ON b.bCity = p.pCity; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 37 73 khai.hq@ou.edu.vn EXISTS và NOT EXISTS
EXISTS và NOT EXISTS chỉ dùng với các truy vấn con, có kết quả là true hoặc false
EXISTS chỉ có giá trị True khi và chỉ khi truy vấn con có tồn tại ít nhất 1 hàng.
EXISTS có giá trị Fasle khi truy vấn con có kết quả rỗng.
NOT EXISTS có giá trị ngược với EXISTS. 74 khai.hq@ou.edu.vn Ví dụ 6.31 Truy vấn dùng EXISTS Tìm tất cả nhân viên làm việc ở chi nhánh London. SELECT staffNo, fName, lName, position FROM Staff s WHERE EXISTS (SELECT * FROM Branch b WHERE s.branchNo = b.branchNo AND city = 'London'); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 38 75 khai.hq@ou.edu.vn Ví dụ 6.31 Truy vấn dùng EXISTS 76 khai.hq@ou.edu.vn Ví dụ 6.31 Truy vấn dùng EXISTS
Có thể viết lại dùng JOIN, không dùng EXISTS: SELECT staffNo, fName, lName, position FROM Staff s, Branch b WHERE s.branchNo = b.branchNo AND city = 'London'; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 39 77 khai.hq@ou.edu.vn Ví dụ 6.32 Dùng phép hội UNION Liệt kê tất cả các thành phố có chi nhánh hay có tài sản. (SELECT city FROM Branch WHERE city IS NOT NULL) UNION (SELECT city FROM PropertyForRent WHERE city IS NOT NULL); 78 khai.hq@ou.edu.vn Ví dụ 6.32 Dùng phép hội UNION – Hay: (SELECT * FROM Branch WHERE city IS NOT NULL) UNION CORRESPONDING BY city (SELECT * FROM PropertyForRent WHERE city IS NOT NULL); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 40 79 khai.hq@ou.edu.vn Ví dụ 6.33 Dùng phép giao INTERSECT Liệt kê tất cả các thành phố vừa có chi nhánh vừa có tài sản. (SELECT city FROM Branch) INTERSECT (SELECT city FROM PropertyForRent); 80 khai.hq@ou.edu.vn Ví dụ 6.33 Dùng phép giao INTERSECT
Hay: (SELECT * FROM Branch) INTERSECT CORRESPONDING BY city (SELECT * FROM PropertyForRent); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 41 81 khai.hq@ou.edu.vn Ví dụ 6.33 Dùng phép giao INTERSECT
Có thể viết lại không dùng INTERSECT: SELECT b.city FROM Branch b PropertyForRent p WHERE b.city = p.city;
Hay: SELECT DISTINCT city FROM Branch b WHERE EXISTS (SELECT * FROM PropertyForRent p WHERE p.city = b.city); 82 khai.hq@ou.edu.vn Ví dụ 6.34 Dùng phép trừ EXCEPT Liệt kê tất cả các thành phố có chi nhánh nhưng không có tài sản. (SELECT city FROM Branch) city FROM EXCEPT (SELECT PropertyForRent);
Or (SELECT * FROM Branch) EXCEPT CORRESPONDING BY city (SELECT * FROM PropertyForRent); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 42 83 khai.hq@ou.edu.vn Ví dụ 6.34 Dùng phép trừ EXCEPT
Có thể viết lại không dùng EXCEPT: SELECT DISTINCT city FROM Branch WHERE city NOT IN (SELECT city FROM PropertyForRent);
Hay: SELECT DISTINCT city FROM Branch b WHERE NOT EXISTS (SELECT * FROM PropertyForRent p WHERE p.city = b.city); 84 khai.hq@ou.edu.vn Thêm dữ liệu với INSERT INTO INSERT INTO TênBảng [ (DanhSáchCột) ] VALUES (DanhSáchGiáTrịDữLiệu)
DanhSáchCột phần lựa chọn; Nếu bỏ qua, SQL sẽ lấy danh sách cột nguyên thủy như trong lệnh CREATE TABLE.
Danh sách dữ liệu phải tương ứng với vị trí các cột, và tương thích với kiểu dữ liệu của từng cột Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 43 85 khai.hq@ou.edu.vn Ví dụ 6.35 Dùng lệnh INSERT INTO VALUES Thêm 1 hàng dữ liệu vào bảng Staff: INSERT INTO Staff VALUES ('SG16', 'Alan', 'Brown', 'Assistant', 'M', Date‘1957-05-25', 8300, 'B003'); 86 khai.hq@ou.edu.vn Ví dụ 6.35 Dùng lệnh INSERT INTO VALUES Khi thêm dữ liệu vào một bảng phải thêm đủ dữ liệu cho các cột bắt buộc phải nhập liệu. INSERT INTO Staff (staffNo, fName, lName, position, salary, branchNo) VALUES ('SG44', 'Anne', 'Jones', 'Assistant', 8100, 'B003');
Hay: INSERT INTO Staff VALUES ('SG44', 'Anne', 'Jones', 'Assistant', NULL, NULL, 8100, 'B003'); Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 44 87 khai.hq@ou.edu.vn Dùng lệnh INSERT INTO SELECT
Thêm dữ liệu là kết quả của lệnh SELECT vào một bảng: INSERT INTO TênBảng [ (DanhSáchCột) ] SELECT ... 88 khai.hq@ou.edu.vn Ví dụ 6.37 Dùng lệnh INSERT INTO SELECT Giả sử có một bảng dữ liệu chứa số lượng tài sản được quản lý bởi các nhân viên: StaffPropCount(staffNo, fName, lName, propCnt) Hãy tạo dữ liệu cho bảng StaffPropCount dùng bảng Staff và bảng PropertyForRent. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 45 89 khai.hq@ou.edu.vn Ví dụ 6.37 Dùng lệnh INSERT INTO SELECT INSERT INTO StaffPropCount (SELECT s.staffNo, fName, lName, COUNT(*) FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo GROUP BY s.staffNo, fName, lName) UNION (SELECT staffNo, fName, lName, 0 FROM Staff WHERE staffNo NOT IN (SELECT DISTINCT staffNo FROM PropertyForRent)); 90 khai.hq@ou.edu.vn Ví dụ 6.37 Dùng lệnh INSERT INTO SELECT
Nếu phần sau UNION bị bỏ đi, danh sách sẽ thiếu các nhân viên không qủn lý tài sản nào. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 46 91 khai.hq@ou.edu.vn Lệnh sửa dữ liệu UPDATE UPDATE TênBảng SET TênCột1 = DữLiệu1 [, TênCột2 = DữLiệu2...] [WHERE D9ie62uKie65n]
TableName can be name of a base table or an updatable view.
SET clause specifies names of one or more columns that are to be updated. 92 khai.hq@ou.edu.vn Lệnh sửa dữ liệu UPDATE SET UPDATE TênBảng SET TênCột1 = DữLiệu1 [, TênCột2 = DữLiệu2...] [WHERE ĐiềuKiệnLọcTìm]
Tên bảng có thể thế bằng tên view loại cập nhật được.
Các dữ liệu phải tương thích kiểu với từng cột. Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 47 93 khai.hq@ou.edu.vn Ví dụ 6.38/39 Lệnh sửa dữ liệu UPDATE SET Tăng lương tất cả nhân viên lên 3%. UPDATE Staff SET salary = salary*1.03; Tăng lương tất cả giám đốc lên 5%. UPDATE Staff SET salary = salary*1.05 WHERE position = 'Manager'; 94 khai.hq@ou.edu.vn Ví dụ 6.40 Lệnh sửa dữ liệu UPDATE SET Thăng chức David Ford (staffNo = 'SG14') thành giám đốc và tăng lương lên 18,000. UPDATE Staff SET position = 'Manager', salary = 18000 WHERE staffNo = 'SG14'; Nhập môn cơ sở dữ liệu www.ou.edu.vn Chương 6: Ngôn ngữ SQL thao tác dữ liệu Trang 48 95 khai.hq@ou.edu.vn Lệnh xóa các hàng dữ liệu DELETE DELETE FROM TênBảng [WHERE ĐiềuKiệnLọcTìmDL]
Tên bảng có thể thế bằng tên view loại cập nhật được.
Nếu không có điều kiện lọc tìm dữ liệu thì câu lệnh sẻ xóa tất cả các hàng dữ liệu. 96 khai.hq@ou.edu.vn Ví dụ 6.41/42 Lệnh xóa các hàng dữ liệu DELETE Xóa dữ liệu xem nhà PG4. DELETE FROM Viewing WHERE propertyNo = 'PG4'; Xóa toàn bộ dữ liệu xem nhà. DELETE FROM Viewing; HẾT CHƯƠNG 6 Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 1 Nhập môn cơ sở dữ liệu www.ou.edu.vn khai.hq@ou.edu.vn Chương 7: Định nghĩa dữ liệu với ngôn ngữ SQL Tham khảo tài liệu [A]: Chapter 6 : SQL: Data Definition 2 khai.hq@ou.edu.vn Nội dung chương 7 ‹ Các kiểu dữ liệu của SQL chuẩn. ‹Mục đích của việc tăng cường tính toàn vẹn của SQL. ‹ Cách định nghĩa các ràng buộc toàn vẹn dùng SQL. ‹ Cách viết lệnh CREATE và ALTER TABLE có tăng cường tính toàn vẹn dữ liệu. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 2 Nhập môn cơ sở dữ liệu www.ou.edu.vn 3 khai.hq@ou.edu.vn Nội dung chương 7 ‹Mục đích của VIEW ‹ Tạo và xóa VIEW dùng SQL. ‹ Cách DBMS thực hiện các thao tác trên VIEW. ‹ Điếu kiện để VIEW cập nhật dữ liệu được ‹Ưu và nhược điểm của VIEW ‹Mô hình giao tác theo chuẩn ISO ‹ Dùng lệnh GRANT và REVOKE 4 khai.hq@ou.edu.vn Các kiểu dữ liệu của SQL chuẩn ISO Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 3 Nhập môn cơ sở dữ liệu www.ou.edu.vn 5 khai.hq@ou.edu.vn Đặc tính tăng cường tính toàn vẹn dữ liệu ‹ Xét 5 loại ràng buộc toàn vẹn: – Ràng buộc bắt buộc nhập liệu – Ràng buộc về miền thuộc tính – Ràng buộc về thực thể – Ràng buộc trong tham khảo dữ liệu – Ràng buộc từ phía công ty 6 khai.hq@ou.edu.vn Đặc tính tăng cường tính toàn vẹn dữ liệu Ràng buộc bắt buộc nhập liệu: position VARCHAR(10) NOT NULL Ràng buộc về miền thuộc tính: (a) CHECK sex CHAR NOT NULL CHECK (sex IN ('M', 'F')) Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 4 Nhập môn cơ sở dữ liệu www.ou.edu.vn 7 khai.hq@ou.edu.vn Đặc tính tăng cường tính toàn vẹn dữ liệu (b) CREATE DOMAIN CREATE DOMAIN DomainName [AS] dataType [DEFAULT defaultOption] [CHECK (searchCondition)] Ví dụ: CREATE DOMAIN SexType AS CHAR CHECK (VALUE IN ('M', 'F')); sex SexType NOT NULL 8 khai.hq@ou.edu.vn Đặc tính tăng cường tính toàn vẹn dữ liệu ‹ searchCondition có thể tìm trong một bảng dữ liệu : CREATE DOMAIN BranchNo AS CHAR(4) CHECK (VALUE IN (SELECT branchNo FROM Branch)); ‹Miền có thể được xóa bằng DROP DOMAIN: DROP DOMAIN DomainName [RESTRICT | CASCADE] Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 5 Nhập môn cơ sở dữ liệu www.ou.edu.vn 9 khai.hq@ou.edu.vn Ràng buộc về thực thể ‹Khóa chính của một bảng phải chứa một giá trị duy nhất và khác NULL ‹ Chuẩn ISO hỗ trợ mệnh đề FOREIGN KEY trong câu lệnh CREATE và ALTER TABLE PRIMARY KEY(staffNo) PRIMARY KEY(clientNo, propertyNo) ‹ Chỉ dùng một mệnh đề PRIMARY KEY cho mỗi bảng. Đối với các thuộc tính khác, muốn cấm việc trùng dữ liệu thì dùng lệnh UNIQUE: UNIQUE(telNo) 10 khai.hq@ou.edu.vn Ràng buộc trong tham khảo dữ liệu ‹Khóa ngoại (FK) là 1 hay nhiều cột có tính chất liên kết mỗi hàng ở bảng con chứa FK đến 1 hàng có giá trị tương ứng trong bảng cha. ‹ Vậy nếu FK chứa một giá trị, thì giá trị đó phải tham khảo đến một hàng hiện hữu ở bảng cha của FK. ‹ Chuẩn ISO hỗ trợ định nghĩa FOREIGN KEY trong CREATE và ALTER TABLE: FOREIGN KEY(branchNo) REFERENCES Branch Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 6 Nhập môn cơ sở dữ liệu www.ou.edu.vn 11 khai.hq@ou.edu.vn Ràng buộc trong tham khảo dữ liệu ‹ Bất kỳ lệnh INSERT/UPDATE nào làm cho dữ liệu của FK không giống với giá trị nào của FK ở bảng cha đều không thực hiện được. ‹ Thao tác update/delete trên bảng cha có liên quan đến FK bị phụ thuộc vào mệnh đề ON UPDATE và ON DELETE: – CASCADE - SET NULL, – SET DEFAULT - NO ACTION 12 khai.hq@ou.edu.vn Ràng buộc trong tham khảo dữ liệu CASCADE: xóa dây chuyền SET NULL: Khi xóa hàng dữ liệu trong bảng cha thì tại các giá trị tương ứng của FK ở bảng con sẽ được gán về NULL (Nếu giá trị của FK là khác NULL) SET DEFAULT: Khi xóa hàng dữ liệu trong bảng cha thì tại các giá trị tương ứng của FK ở bảng con sẽ được gán về mặc định nào đó. NO ACTION: Không cho xóa ở bảng cha (mặc định) Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 7 Nhập môn cơ sở dữ liệu www.ou.edu.vn 13 khai.hq@ou.edu.vn Ràng buộc trong tham khảo dữ liệu FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL FOREIGN KEY (ownerNo) REFERENCES Owner ON UPDATE CASCADE 14 khai.hq@ou.edu.vn Ràng buộc từ phía công ty ‹ Có thể dùng CHECK/UNIQUE trong câu lệnh CREATE và ALTER TABLE. ‹Hay: CREATE ASSERTION AssertionName CHECK (searchCondition) ‹Giống như sử dụng mệnh đề CHECK Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 8 Nhập môn cơ sở dữ liệu www.ou.edu.vn 15 khai.hq@ou.edu.vn Ràng buộc từ phía công ty CREATE ASSERTION StaffNotHandlingTooMuch CHECK (NOT EXISTS (SELECT staffNo FROM PropertyForRent GROUP BY staffNo HAVING COUNT(*) > 100)) 16 khai.hq@ou.edu.vn Định nghĩa dữ liệu ‹ SQL DDL cho phép tạo và xóa schema, domain, table, view, và index ‹ Các câu lệnh SQL DDL chính là: CREATE SCHEMA DROP SCHEMA CREATE/ALTER DOMAIN DROP DOMAIN CREATE/ALTER TABLEDROP TABLE CREATE VIEW DROP VIEW ‹ Nhiều DBMS cho phép: CREATE INDEX DROP INDEX Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 9 Nhập môn cơ sở dữ liệu www.ou.edu.vn 17 khai.hq@ou.edu.vn Định nghĩa dữ liệu ‹ Các quan hệ và các đối tượng CSDL khác tồn tại trong một môi trường (environment) ‹Mỗi môi trường chứa 1 hay nhiều catalog, mỗi catalog bao gồm tập hợp các lược đồ (schema) ‹ Lược đồ có tên theo ý nghĩa là tập hợp các đối tượng CSDL có liên quan nhau ‹ Các đối tượng trong một lược đồ có thể là: tables, views, domains, assertions, collations, translations, và các tập ký tự. Một lược đồ của một tác giả. 18 khai.hq@ou.edu.vn CREATE SCHEMA CREATE SCHEMA [Name | AUTHORIZATION CreatorId ] DROP SCHEMA Name [RESTRICT | CASCADE ] ‹ Với RESTRICT (default), schema phải rỗng, nếu không rỗng sẽ không xóa được ‹ Với CASCADE sẽ xóa dây chuyền tất cả các đối tượng có liên quan đến schema Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 10 Nhập môn cơ sở dữ liệu www.ou.edu.vn 19 khai.hq@ou.edu.vn CREATE TABLE CREATE TABLE TableName {(colName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK searchCondition] [,...]} [PRIMARY KEY (listOfColumns),] {[UNIQUE (listOfColumns),] [,]} {[FOREIGN KEY (listOfFKColumns) REFERENCES ParentTableName [(listOfCKColumns)], [ON UPDATE referentialAction] [ON DELETE referentialAction ]] [,]} {[CHECK searchCondition)] [,] }) 20 khai.hq@ou.edu.vn CREATE TABLE ‹ Tạo một bảng với các cột, mỗi cột có kiểu dữ liệu riêng ‹ Với NOT NULL, cột không chấp nhận giá trị NULL ‹Mỗi cột có thể đặt một giá trị DEFAULT ‹Khóa chính phải đặt NOT NULL ‹ FOREIGN KEY có chỉ ra chế độ thao tác xóa/sửa ở cột có liên quan bảng cha Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 11 Nhập môn cơ sở dữ liệu www.ou.edu.vn 21 khai.hq@ou.edu.vn Ví dụ 7.1 - CREATE TABLE ‹CREATE DOMAIN OwnerNumber AS VARCHAR(5) CHECK (VALUE IN (SELECT ownerNo FROM Owner)); ‹CREATE DOMAIN StaffNumber AS VARCHAR(5) CHECK (VALUE IN (SELECT staffNo FROM Staff)); ‹CREATE DOMAIN PNumber AS VARCHAR(5); ‹CREATE DOMAIN PRooms AS SMALLINT; CHECK(VALUE BETWEEN 1 AND 15); ‹CREATE DOMAIN PRent AS DECIMAL(6,2) CHECK(VALUE BETWEEN 0 AND 9999.99); 22 khai.hq@ou.edu.vn Ví dụ 7.1 - CREATE TABLE CREATE TABLE PropertyForRent ( propertyNo PNumber NOT NULL, . rooms PRooms NOT NULL DEFAULT 4, rent PRent NOT NULL, DEFAULT 600, ownerNo OwnerNumber NOT NULL, staffNo StaffNumber Constraint StaffNotHandlingTooMuch . branchNo BranchNumber NOT NULL, PRIMARY KEY (propertyNo), FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL ON UPDATE CASCADE .); Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 12 Nhập môn cơ sở dữ liệu www.ou.edu.vn 23 khai.hq@ou.edu.vn ALTER TABLE ‹ Thêm một cột mới cho một bảng ‹ Xóa một cột của một bảng ‹ Thêm một ràng buộc cho bảng ‹ Xóa một ràng buộc của bảng ‹ Đặt giá trị default cho một cột ‹ Xóa việc đặt giá trị default cho một cột 24 khai.hq@ou.edu.vn Ví dụ 7.2(a) - ALTER TABLE Bảng Staff: xóa giá trị default 'Assistant' cho cột position và đặt giá trị default cho cột sex là Female ('F'). ALTER TABLE Staff ALTER position DROP DEFAULT; ALTER TABLE Staff ALTER sex SET DEFAULT 'F'; Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 13 Nhập môn cơ sở dữ liệu www.ou.edu.vn 25 khai.hq@ou.edu.vn Ví dụ 7.2(b) - ALTER TABLE Xóa ràng buộc không cho một nhân viên quản lý hơn 100 tài sản; thêm vào bảng Client một cột mới ALTER TABLE PropertyForRent DROP CONSTRAINT StaffNotHandlingTooMuch; ALTER TABLE Client ADD prefNoRooms PRooms; 26 khai.hq@ou.edu.vn DROP TABLE DROP TABLE TableName [RESTRICT | CASCADE] ví dụ: DROP TABLE PropertyForRent; ‹ Xóa bảng và dữ liệu trong bảng ‹ Với RESTRICT, nếu các đối tượng khác có dùng bảng thì không thể xóa bảng ‹ Với CASCADE: xóa dây chuyền Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 14 Nhập môn cơ sở dữ liệu www.ou.edu.vn 27 khai.hq@ou.edu.vn View ‹ Nội dung của một view được định nghĩa như là một truy vấn trên một hay nhiều quan hệ nền ‹ Với view phân giải (view resolution), bất kỳ thao tác nào trên view sẽ chuyển thành các thao tác trên các quan hệ nền của nó ‹ Với view vật chất (view materialization), view được lưu như là một bảng tạm. Khi nào các quan hệ nền được cập nhật thì view nên cập nhật theo. 28 khai.hq@ou.edu.vn Lệnh CREATE VIEW CREATE VIEW ViewName [ (newColumnName [,...]) ] AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION] ‹Có thể gán tên cho mỗi cột của view. ‹Nếu có ghi danh sách tên các cột thì số lượng các tên cột phải bằng số lượng cột trong kết quả của subselect. ‹Nếu không có ghi danh sách tên các cột thì các cột lấy tên từ kết quả của subselect. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 15 Nhập môn cơ sở dữ liệu www.ou.edu.vn 29 khai.hq@ou.edu.vn Lệnh CREATE VIEW ‹ Nên liệt kê các tên của view để tránh sự nhầm lẫn. ‹ Phần subselect được gọi là phần truy vấn định nghĩa view (defining query). ‹ Phần WITH CHECK OPTION bảo đảm rằng nếu hàng dữ liệu nào không thỏa phần WHERE thì sẽ không được thêm vào bảng dữ liệu nền. 30 khai.hq@ou.edu.vn Ví dụ 7.3 - Tạo View theo chiều ngang Tạo view để giám đốc chi nhánh B003 chỉ thấy chi tiết về các nhân viên làm việc tại chi nhánh này. CREATE VIEW Manager3Staff AS SELECT * FROM Staff WHERE branchNo = 'B003'; Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 16 Nhập môn cơ sở dữ liệu www.ou.edu.vn 31 khai.hq@ou.edu.vn Ví dụ 7.4 - Tạo View theo chiều dọc Tạo view chi tiết nhân viên tại chi nhánh B003 không có cột salaries. CREATE VIEW Staff3 AS SELECT staffNo, fName, lName, position, sex FROM Staff WHERE branchNo = 'B003'; 32 khai.hq@ou.edu.vn Ví dụ 7.5 - Tạo View có nhóm, có kết dữ liệu Tạo view nhân viên quản lý tài sản, bao gồm: mã chi nhánh đang làm việc, mã nhân viên, và mã tài sản mà nhân viên đang quản lý. CREATE VIEW StaffPropCnt (branchNo, staffNo, cnt) AS SELECT s.branchNo, s.staffNo, COUNT(*) FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo GROUP BY s.branchNo, s.staffNo; Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 17 Nhập môn cơ sở dữ liệu www.ou.edu.vn 33 khai.hq@ou.edu.vn Ví dụ 7.5 - Tạo View có nhóm, có kết dữ liệu 34 khai.hq@ou.edu.vn Lệnh DROP VIEW DROP VIEW ViewName [RESTRICT | CASCADE] ‹ Để xóa view trong cơ sở dữ liệu . ‹ Ví dụ: DROP VIEW Manager3Staff; ‹ Với CASCADE: xóa dây chuyền ‹ Với RESTRICT (mặc định): không cho xóa view nếu các đối tượng khác có sử dụng view Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 18 Nhập môn cơ sở dữ liệu www.ou.edu.vn 35 khai.hq@ou.edu.vn Sự phân giải view (view phân giải) Đếm số lượng tài sản được quản lý bởi từng nhân viên ở chí nhánh B003. SELECT staffNo, cnt FROM StaffPropCnt WHERE branchNo = 'B003' ORDER BY staffNo; 36 khai.hq@ou.edu.vn Sự phân giải view (view phân giải) (a) Các tên cột của view sau SELECT được phân giải thành các tên tương ứng trong truy vấn định nghĩa: SELECT s.staffNo As staffNo, COUNT(*) As cnt (b) Tên view sau FROM được thay thế bằng danh sách sau phần FROM của truy vấn định nghĩa view: FROM Staff s, PropertyForRent p Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 19 Nhập môn cơ sở dữ liệu www.ou.edu.vn 37 khai.hq@ou.edu.vn (c) Cuối cùng, truy vấn sau sẽ thực thi để lấy kết quả: SELECT s.staffNo, COUNT(*) FROM staff s, PropertyForRent p WHERE s.staffNo = p.staffNo AND branchNo = 'B003' GROUP BY s.branchNo, s.staffNo ORDER BY s.staffNo; Sự phân giải view (view phân giải) 38 khai.hq@ou.edu.vn Giới hạn cho view SQL đặt ra nhiều giới hạn trong việc tạo và sử dụng view. (a) Nếu view có cột sử dụng hàm thống kê: – Cột này chỉ có thể xuất hiện trong SELECT và ORDER BY của câu truy vấn view này. – Cột này không dùng trong WHERE và cũng không nằm trong một hàm thống kê trong câu truy vấn view này. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 20 Nhập môn cơ sở dữ liệu www.ou.edu.vn 39 khai.hq@ou.edu.vn Giới hạn cho view ‹ Ví dụ, truy vấn sau là sai: SELECT COUNT(cnt) FROM StaffPropCnt; ‹ Tương tự, truy vấn sau là sai : SELECT * FROM StaffPropCnt WHERE cnt > 2; 40 khai.hq@ou.edu.vn Giới hạn cho view (b) View có nhóm không được kết với các view khác hay kết với các bảng dữ liệu nền. ‹ Ví dụ, view tên StaffPropCnt là một view có nhóm, do đó không được kết view này với các bảng hay các view khác. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 21 Nhập môn cơ sở dữ liệu www.ou.edu.vn 41 khai.hq@ou.edu.vn Việc cập nhật view ‹ Cập nhật dữ liệu từ các bảng nền sẽ kéo theo sự cập nhật dữ liệu lên các view có liên quan ‹ Tuy nhiên chiều ngược lại là không phải bao giờ cũng thực hiện được. 42 khai.hq@ou.edu.vn Việc cập nhật view ‹ Xét view tên StaffPropCnt, nếu chúng ta thử thêm một hàng dữ liệu: tại chi nhánh B003, nhân viên SG5 quản lý 2 tài sản: INSERT INTO StaffPropCnt VALUES ('B003', 'SG5', 2); ‹ Việc này dẫn tới việc bắt buộc phải nhập 2 hàng dữ liệu trong bảng PropertyForRent (2 tài sản do SG5 quản lý), tuy nhiên do không có thông tin về 2 mã tài sản nên không thể thêm 2 hàng dữ liệu này được. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 22 Nhập môn cơ sở dữ liệu www.ou.edu.vn 43 khai.hq@ou.edu.vn Việc cập nhật view ‹ SQL chuẩn ISO qui định một view cập nhật được nếu: -Không dùng DISTINCT - Sau SELECT là các tên cột, và không dùng các cột hơn một lần - Sau FROM chỉ có một bảng - Không có lệnh SELECT lồng nhau dùng các bảng khác - Không có GROUP BY và HAVING - Dữ liệu thêm vào phải thỏa các ràng buộc toàn vẹn của bảng dữ liệu 44 khai.hq@ou.edu.vn Dùng WITH CHECK OPTION ‹ Các hàng trong có mặt trong view vì thỏa điều kiện WHERE. ‹ Nếu một hàng không còn thỏa điều kiện WHERE sẽ biến mất khỏi view. ‹ Các hàng mới có thể sẽ xuất hiện trong view khi có sự chèn/thêm dữ liệu (nếu thỏa điều kiện WHERE) ‹ Các hàng mới có hoặc bị mất đi khỏi view gọi là các hàng dịch chuyển vào ra (migrating rows) ‹WITH CHECK OPTION cấm các hàng dịch chuyển ra khỏi view. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 23 Nhập môn cơ sở dữ liệu www.ou.edu.vn 45 khai.hq@ou.edu.vn Dùng WITH CHECK OPTION ‹ LOCAL/CASCADED apply to view hierarchies. ‹ Nếu dùng LOCAL: không cho các hàng ra khỏi (biến khỏi) view trừ khi các hàng đó đã biến khỏi các bảng hay view khác mà view này đang tham khảo. ‹ Nếu dùng CASCADED (default): không cho các hàng ra khỏi (biến khỏi) view. 46 khai.hq@ou.edu.vn Ví dụ 7.6 - Dùng WITH CHECK OPTION CREATE VIEW Manager3Staff AS SELECT * FROM Staff WHERE branchNo = 'B003' WITH CHECK OPTION; ‹Không thể sửa B003 thành B002 vì khi đó các hàng sẽ biến khỏi view. ‹Không thể thêm các hàng có chi nhánh khác B003. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 24 Nhập môn cơ sở dữ liệu www.ou.edu.vn 47 khai.hq@ou.edu.vn Ví dụ 7.6 - Dùng WITH CHECK OPTION ‹ Nếu view tên Manager3Staff không lấy dl trực tiếp từ bảng Staff mà từ một view khác: CREATE VIEW LowSalary AS SELECT * FROM Staff WHERE salary > 9000; CREATE VIEW HighSalary AS SELECT * FROM LowSalary WHERE salary > 10000 WITH LOCAL CHECK OPTION; CREATE VIEW Manager3Staff AS SELECT * FROM HighSalary WHERE branchNo = 'B003'; 48 khai.hq@ou.edu.vn Ví dụ 7.6 - WITH CHECK OPTION UPDATE Manager3Staff SET salary = 9500 WHERE staffNo = 'SG37'; ‹ Update không thể thực hiện được: cho dù hàng dl có thể biến khỏi HighSalary, nhưng không thể biến khỏi LowSalary. ‹ Tuy nhiên, nếu update gán Salary = 8000, update sẽ thành công vì hàng dl không thuộc LowSalary. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 25 Nhập môn cơ sở dữ liệu www.ou.edu.vn 49 khai.hq@ou.edu.vn Vi dụ 7.6 - WITH CHECK OPTION ‹ Nếu HighSalary dùng WITH CASCADED CHECK OPTION, thì không thể gán Salary bằng 9500 hay 8000 vì hàng dl sẽ biến khỏi HighSalary. ‹ Để tránh việc dị thường như vậy, các view nên dùng WITH CASCADED CHECK OPTION. 50 khai.hq@ou.edu.vn Ưu điểm của View ‹ Độc lập dữ liệu ‹ Tiết kiệm chi phí ‹ Tăng tính an toàn ‹Giảm độ phức tạp ‹ Tiện lợi ‹Mềm dẻo ‹ Toàn vẹn dữ liệu Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 26 Nhập môn cơ sở dữ liệu www.ou.edu.vn 51 khai.hq@ou.edu.vn Nhược điểm của View ‹Hạn chế trong cập nhật dữ liệu ‹Giới hạn vế cấu trúc ‹ Chậm hơn 52 khai.hq@ou.edu.vn Vật chất hóa view (View vật chất) ‹ View phân giải chậm, nhất là khi truy xuất thường xuyên. ‹ View vật chất lưu view như một bảng tạm khi truy xuất lần đầu tiên ‹ Do đó, view vật chất chạy nhanh hơn. ‹ Nhưng: phải xem xét đến việc cập nhật view vật chất (bảo trì) khi bảng nền của nó được cập nhật. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 27 Nhập môn cơ sở dữ liệu www.ou.edu.vn 53 khai.hq@ou.edu.vn Bảo trì view ‹ Việc bảo trì view giúp cho view có thông tin hiện hành. ‹ Xét view sau: CREATE VIEW StaffPropRent(staffNo) AS SELECT DISTINCT staffNo FROM PropertyForRent WHERE branchNo = 'B003‘ AND rent > 400; 54 khai.hq@ou.edu.vn Vật chất hóa view (View vật chất) ‹ Nếu chèn hàng vào PropertyForRent có rent ≤400 thì view không đổi ‹ Nếu chèn hàng vào cho tài sản PG24 tại B003 với staffNo = SG19 và rent = 550, thì hàng đó sẽ xuất hiện trong view vật chất ‹ Nếu chèn hàng vào cho tài sản PG54 tại B003 với staffNo = SG37 và rent = 450, thì không cần thêm hàng cho view vật chất. ‹ Nếu xóa tài sản PG24, hàng dữ liệu này sẽ biến khỏi view ‹ Nếu xóa tài sản PG54, thì hàng dữ liệu cho tài sản PG37 không nên bị xóa (bởi vì còn tồn tại tài sản PG21) Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 28 Nhập môn cơ sở dữ liệu www.ou.edu.vn 55 khai.hq@ou.edu.vn Giao tác (Transactions) ‹ SQL định nghĩa mô hình giao tác dựa vào COMMIT và ROLLBACK. ‹Giao tác là một đơn vị logic của công việc, các lệnh SQL về giao tác phải bảo đảm việc giao tác thực hiện xong công việc hoặc là phục hồi lại trạng thái trước giao tác. ‹Một giao tác của SQL tự động khởi động với câu lệnh có tính khởi động giao tác (như lệnh SELECT, INSERT). ‹ Các thay đổi do giao tác gây ra bị dấu đi cho đến khi hoàn thành giao tác 56 khai.hq@ou.edu.vn Giao tác (Transactions) ‹ Giao tác có thể kết thúc trong 4 trường hợp sau: - COMMIT kết thúc thành công, dữ liệu sẽ được cập nhật - ROLLBACK hủy bỏ giao tác, hoàn lại trạng thái trước khi giao tác làm cho thay đổi - Trong lập trình với SQL, các chương trình có thể kết thúc giao tác thành công, kể cả khi chưa thực hiện lệnh COMMIT. - Trong lập trình với SQL, nếu chương trình bị lỗi giữa giao tác thì nên hủy giao tác. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 29 Nhập môn cơ sở dữ liệu www.ou.edu.vn 57 khai.hq@ou.edu.vn Giao tác (Transactions) ‹ Các giao tác SQL không được lồng nhau. ‹ Lệnh SET TRANSACTION cấu hình giao tác: SET TRANSACTION [READ ONLY | READ WRITE] | [ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED|REPEATABLE READ |SERIALIZABLE ] 58 khai.hq@ou.edu.vn Ràng buộc toàn vẹn kiểu trung gian và trì hoãn ‹Không phải lúc nào các ràng buộc cũng kiểm tra tức thời, có khi phải chờ đến lúc giao tác ủy thác (commit) ‹ Các ràng buộc có thể được định nghĩa là INITIALLY IMMEDIATE hay INITIALLY DEFERRED lúc đầu giao tác. ‹ Cũng có thể thay đổi lại kiểu ràng buộc dùng [NOT] DEFERRABLE. ‹Kiểu mặc định là INITIALLY IMMEDIATE. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 30 Nhập môn cơ sở dữ liệu www.ou.edu.vn 59 khai.hq@ou.edu.vn Ràng buộc toàn vẹn kiểu trung gian và trì hoãn ‹ Lệnh SET CONSTRAINTS cho giao tác hiện hành: SET CONSTRAINTS {ALL | constraintName [, . . . ]} {DEFERRED ¦ IMMEDIATE} 60 khai.hq@ou.edu.vn Kiểm soát truy xuất – Danh hiệu về quyền và quyền sở hữu ‹Danh hiệu về quyền là danh hiệu bình thường trong SQL dùng để nhận diện user, thường đi kèm là password. ‹Dùng để xác định quyền trong một số thao tác trên một số đối tượng ‹Mỗi đối tượng tạo ra trong SQL có một chủ sở hữu, như là đã định ra ở mệnh đề AUTHORIZATION của lược đồ csdl. Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 31 Nhập môn cơ sở dữ liệu www.ou.edu.vn 61 khai.hq@ou.edu.vn Đặc quyền (Privileges) ‹ Các thao tác user được phép thực hiện trên các view hay các quan hệ nền: SELECT Lấy dữ liệu INSERT Chèn hàng dữ liệu UPDATE Sửa dữ liệu trên các hàng đã có DELETE Xóa các hàng dữ liệu REFERENCES Tham khảo dữ liệu để bảo đảm ràng buộc toàn vẹn USAGE Dùng domains, collations, character sets, và translations. 62 khai.hq@ou.edu.vn Lệnh ban quyền GRANT GRANT {PrivilegeList | ALL PRIVILEGES} ON ObjectName TO {AuthorizationIdList | PUBLIC} [WITH GRANT OPTION] ‹ PrivilegeList gồm một hay nhiều quyền cách nhau bằng dấu phẩy ‹ ALL PRIVILEGES ban tất cả quyền cho user Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 32 Nhập môn cơ sở dữ liệu www.ou.edu.vn 63 khai.hq@ou.edu.vn Lệnh ban quyền GRANT ‹ PUBLIC cho quyền truy xuất với tất cả user (hiện tại và tương lai). ‹ ObjectName có thể là base table, view, domain, character set, collation hay translation. ‹WITH GRANT OPTION cho phép thông qua quyền 64 khai.hq@ou.edu.vn Ví dụ 7.7/8 - GRANT Cho Manager đầy đủ quyền trên bảng Staff table: GRANT ALL PRIVILEGES ON Staff TO Manager WITH GRANT OPTION; Cho user Personnel và Director quyền SELECT và UPDATE trên cột salary của bảng Staff: GRANT SELECT, UPDATE (salary) ON Staff TO Personnel, Director; Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 33 Nhập môn cơ sở dữ liệu www.ou.edu.vn 65 khai.hq@ou.edu.vn Ví dụ 7.9 – Ban quyền PUBLIC Cho tất cả user quyền SELECT trên bảng Branch: GRANT SELECT ON Branch TO PUBLIC; 66 khai.hq@ou.edu.vn Lệnh thu lấy quyền REVOKE ‹ REVOKE lấy quyền đã được ban trước đó nhờ GRANT. REVOKE [GRANT OPTION FOR] {PrivilegeList | ALL PRIVILEGES} ON ObjectName FROM {AuthorizationIdList | PUBLIC} [RESTRICT | CASCADE] ‹ ALL PRIVILEGES lấy lại tất cả quyền đã ban Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu Trang 34 Nhập môn cơ sở dữ liệu www.ou.edu.vn 67 khai.hq@ou.