Bài giảng Nhập môn cơ sở dữ liệu

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Chương 1: Tổng quan về cơ sở dữ liệu

1

Tham khảo tài liệu [A]: Chapter 1: Introduction to Databases

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)

2

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 1

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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

3

khai.hq@ou.edu.vn

Ví dụ về các ứng dụng CSDL

(cid:139) Mua bán ở siêu thị (cid:139) Mua bán dùng thẻ tín dụng (cid:139) Đặt vé đi nghỉ mát tại trạm giao dịch du lịch (cid:139) Đọc, mượn sách tại thư viện (cid:139) Thanh lý hợp đồng bảo hiểm (cid:139) Sử dụng mạng internet (cid:139) Học tập, nghiên cứu tại trường đại học

4

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 2

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Hệ thống lưu DL theo file (File-based System)

(cid:139) Là tập hợp một số chương trình ứng dụng để phục

(cid:139) Mỗi chương trình quản lý các dữ liệu có thiết kế

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

riêng cho chương trình đó

5

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)

6

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 3

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Hạn chế của phương pháp lưu DL theo file (1)

– 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

1. Dữ liệu riêng rẽ và cô lập

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

– Các dữ liệu giống nhau có thể được lưu ở các chương

2. Dữ liệu bị trùng lắp

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

7

khai.hq@ou.edu.vn

Hạn chế của phương pháp lưu DL theo file (2)

– Cấu trúc của file lưu DL do code của chương trình tạo ra

3. Dữ liệu lệ thuộc vào chương trình

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

8

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 4

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phương pháp lưu dữ liệu bằng CSDL

(cid:139) 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

(cid:139) Kết quả:

– Hệ quản trị CSDL (DBMS)

9

khai.hq@ou.edu.vn

Cơ sở dữ liệu (Database)

(cid:139) 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)

(cid:139) 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

(cid:139) 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

10

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 5

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Hệ quản trị CSDL (DBMS)

(cid:139) 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

11

khai.hq@ou.edu.vn

Ví dụ về hệ quản trị CSDL (DBMS)

12

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 6

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phương pháp lưu dữ liệu bằng CSDL

(cid:139) Ngôn ngữ định nghĩa dữ liệu

(cid:139) Ngôn ngữ thao tác 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

(DML: Data manipulation language). – Là công cụ để truy vấn DL

13

khai.hq@ou.edu.vn

Phương pháp lưu dữ liệu bằng CSDL

(cid:139) Việc truy xuất CSDL có kiểm soát có thể bao gồm

(cid:139) 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

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 đang cần

14

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 7

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Khung nhìn dữ liệu (View) (1)

(cid:139) Cho phép mỗi user có cái nhìn riêng về CSDL

(cid:139) Khung nhìn dữ liệu được xem là một phần cơ bản

của CSDL

15

khai.hq@ou.edu.vn

Khung nhìn dữ liệu (View) (2)

(cid:139) 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

16

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 8

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Các thành phần của môi trường dùng DBMS (1)

17

khai.hq@ou.edu.vn

Các thành phần của môi trường dùng DBMS (2)

(cid:139) Phần cứng (Hardware)

– Có thể là một máy PC hay là một mạng máy tính

(cid:139) 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

(cid:139) Dữ liệu (Data)

– Là DL được dùng và sự miêu tả về DL đó (được gọi là

lược đồ DL (schema))

18

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 9

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Các thành phần của môi trường dùng DBMS (3)

(cid:139) 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

(cid:139) Người dùng (People)

19

khai.hq@ou.edu.vn

Vai trò của con người trong môi trường DBMS

(cid:139) Người quản trị dữ liệu (Data Administrator (DA)) (cid:139) Người quản trị CSDL

(Database Administrator (DBA))

(cid:139) Người thiết kế CSDL (Database Designers) Mức logic và vật lý (Logical and Physical)

(cid:139) Người lập trình ứng dụng

(cid:139) Người sử dụng chương trình (End Users)

(Application Programmers)

Mức cơ bản và mức thành thạo (naive and sophisticated)

20

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 10

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Lịch sử các hệ thống cơ sở dữ liệu

(cid:139) Thế hệ đầu tiên (First generation)

– Hệ thống CSDL phân cấp (Hierarchical) – Hệ thống CSDL mạng (Network) (cid:139) Thế hệ thứ hai (Second generation)

– Hệ thống CSDL quan hệ (Relational)

(cid:139) 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)

21

khai.hq@ou.edu.vn

Ưu điểm của DBMS (1)

(cid:139) Có kiểm soát sự dư thừa dữ liệu (cid:139) Có tính nhất quán dữ liệu (data consistency) (cid:139) Chứa được nhiều thông tin hơn với cùng số lượng

(cid:139) Cho phép chia sẽ dữ liệu (cid:139) Tăng cường sự toàn vẹn của dữ liệu (data integrity) (cid:139) Tăng độ bảo mật (security) (cid:139) Có tuân theo các tiêu chuẩn (cid:139) Tiết kiệm về kích cỡ

dữ liệu như nhau

22

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 11

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ưu điểm của DBMS (2)

(cid:139) Cân đối các yêu cầu có tính xung đột (cid:139) Tăng cường khả năng truy xuất và đáp ứng của dữ

(cid:139) Nâng cao hiệu suất (productivity) (cid:139) Bảo trì tốt hơn nhờ dữ liệu độc lập với chương

liệu (accessibility and responsiveness)

(cid:139) Tăng khả năng sử dụng đồng thời (cid:139) Cải thiện chức năng sao lưu và phục hồi dữ liệu

trình

23

khai.hq@ou.edu.vn

Nhược điểm của DBMS

(cid:139) Phức tạp (Complexity) (cid:139) Nhược điểm về kích thước (Size) (cid:139) Nhược điểm về chi phí (Cost of DBMS) (cid:139) Phải tốn chi phí thêm cho phần cứng (cid:139) Tốn chi phí vào việc chuyển đổi (Cost of conversion) (cid:139) Nhược điểm về tốc độ thực hiện (cid:139) Dễ bị hư hỏng hơn

HẾT CHƯƠNG 1

24

Chương 1: Tổng quan về cơ sở dữ liệu

Trang 12

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Chương 2: Môi trường của cơ sở dữ liệu

1

Tham khảo tài liệu [A]: Chapter 2: Database Environment

khai.hq@ou.edu.vn

Nội dung chương 2 (1)

(cid:139) Mục đích của kiến trúc CSDL 3 mức

(cid:139) Nội dung của mức ngoài, mức ý niệm và mức trong

(cid:139) Mục đích của việc ánh xạ mức ngoài/mức ý niệm và

(external, conceptual, and internal levels)

(cid:139) Ý nghĩa của sự độc lập dữ liệu giữa logic và vật lý

(cid:139) Phân biệt DDL và DML

(cid:139) Phân loại các mô hình dữ liệu

mức ý niệm/mức trong

2

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

khai.hq@ou.edu.vn

Nội dung chương 2 (2) (cid:139) Mục đích và tầm quan trọng của việc mô hình hóa

(cid:139) Các chức năng và dịch vụ chính yếu của DBMS (cid:139) Các thành phần phần mềm của DBMS (cid:139) Ý nghĩa của kiến trúc client–server và sự tiện lợi của

mức ý niệm

kiến trúc này đối với DBMS

(cid:139) 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) (cid:139) Chức năng và tầm quan trọng của catalog hệ thống

(system catalog)

3

khai.hq@ou.edu.vn

Mục tiêu của kiến trúc 3 mức

(cid:139) Tất cả users có thể truy xuất dữ liệu như nhau

(cid:139) View của một user không bị thay đổi khi các view

(cid:139) Users không cần phải biết chi tiết ở mức vật lý cơ

khác bị thay đổi

sở dữ liệu được lưu như thế nào

4

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

khai.hq@ou.edu.vn

Mục tiêu của kiến trúc 3 mức

(cid:139) 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

(cid:139) Cấu trúc mức trong của CSDL không bị ảnh hưởng

(cid:139) DBA có thể thay đổi cấu trúc mức ý niệm nhưng

bởi sự thay đổi các yếu tố mức vật lý

không làm ảnh hưởng đến tất cả các users

5

khai.hq@ou.edu.vn

Kiến trúc 3 mức ANSI-SPARC (ANSI-SPARC Three-level Architecture)

6

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

khai.hq@ou.edu.vn

Kiến trúc 3 mức ANSI-SPARC

(cid:139) 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

(cid:139) Mức ý niệm (Conceptual Level)

từng user

– 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

7

khai.hq@ou.edu.vn

Kiến trúc 3 mức ANSI-SPARC

(cid:139) 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

8

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

khai.hq@ou.edu.vn

Sự khác nhau giữa 3 mức

9

khai.hq@ou.edu.vn

Sự độc lập về dữ liệu (Data Independence) (1)

(cid:139) Độ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

10

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

khai.hq@ou.edu.vn

Sự độc lập về dữ liệu (Data Independence) (2)

(cid:139) Độ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

11

khai.hq@ou.edu.vn

Độc lập dữ liệu của kiến trúc ANSI-SPARC 3 mức

12

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

khai.hq@ou.edu.vn

Ngôn ngữ cho CSDL (Database Languages) (1)

(cid:139) 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)

13

khai.hq@ou.edu.vn

Ngôn ngữ cho CSDL (Database Languages) (2)

(cid:139) 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

(cid:139) 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 (cid:139) 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

14

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

khai.hq@ou.edu.vn

Ngôn ngữ cho CSDL (Database Languages) (3)

(cid:139) 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)

15

khai.hq@ou.edu.vn

Mô hình dữ liệu (Data Model) (1)

(cid:139) 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

(cid:139) 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)

16

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

khai.hq@ou.edu.vn

Mô hình dữ liệu (Data Model) (2)

(cid:139) Mục đích

(cid:139) Các loại mô hình dữ liệu gồm:

– Biểu diễn dữ liệu theo cách thức dể hiểu

– 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)

17

khai.hq@ou.edu.vn

Các mô hình dữ liệu (Data Models)

(cid:139) 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)

(cid:139) 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)

(cid:139) Physical Data Models

18

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

khai.hq@ou.edu.vn

Mô hình hóa mức ý niệm (Conceptual modeling)

(cid:139) Lược đồ ý niệm là cốt lõi của hệ thống, nó hỗ trợ tất

(cid:139) Lược đồ phải thể hiện đầy đủ và chính xác các dữ

cả các view của user

liệu của một tổ chức

(cid:139) 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

(cid:139) Kết quả là một mô hình dữ liệu mức ý niệm

(conceptual data model)

19

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

20

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

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, …

21

khai.hq@ou.edu.vn

Các thành phần của DBMS

22

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

khai.hq@ou.edu.vn

Các thành phần của Database Manager (DM)

23

khai.hq@ou.edu.vn

Các kiến trúc DBMS đa người dùng

(cid:139) Xử lý từ xa (Teleprocessing)

(cid:139) Máy phục vụ file (File-server)

(cid:139) Máy khách - máy phục vụ (Client-server)

24

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

khai.hq@ou.edu.vn

Kiến trúc xử lý từ xa (Teleprocessing) (1)

(cid:139) Là kiến trúc truyền thống

(cid:139) 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)

(cid:139) 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

25

khai.hq@ou.edu.vn

Kiến trúc xử lý từ xa (Teleprocessing) (2)

26

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

khai.hq@ou.edu.vn

Kiến trúc File-Server (1)

(cid:139) 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

(cid:139) Database nằm ở máy file-server.

(cid:139) DBMS và applications chạy trên các máy workstation.

(cid:139) 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

27

khai.hq@ou.edu.vn

Kiến trúc File-Server (2)

28

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

khai.hq@ou.edu.vn

Kiến trúc Client-server (1)

(cid:139) Máy Server chứa database và chạy DBMS.

(cid:139) Máy Client quản lý giao diện với user và chạy các

ứng dụng (cid:139) 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

29

khai.hq@ou.edu.vn

Kiến trúc Client-server (2)

30

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

khai.hq@ou.edu.vn

Các sơ đồ kết nối của kiến trúc Client-server

31

khai.hq@ou.edu.vn

Bộ giám sát việc xử lý giao tác (Transaction Processing Monitor) (1)

(cid:139) Là chương trình kiểm soát việc truyền dữ liệu giữa

(cid:139) Nhằm tạo ra một môi trường có tính nhất quán

(cid:139) Đặc biệt dùng cho việc xử lý giao tác trực tuyến

máy client và máy server

(Online Transaction Processing (OLTP))

32

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

khai.hq@ou.edu.vn

Bộ giám sát việc xử lý giao tác (2)

(cid:139) Là lớp giữa của kiến trúc Client-Server 3 lớp

33

khai.hq@ou.edu.vn

Catalog hệ thống (System Catalog)

(cid:139) Là nơi chứa thông tin metadata

(metadata là thông tin miêu tả dữ liệu trong database)

(cid:139) 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 (cid:139) Đượ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

34

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

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

khai.hq@ou.edu.vn

Nội dung chương 3

(cid:139) Cách dùng mô hình Entity–Relationship (ER) trong

(cid:139) Các khái niệm cơ bản liên quan đến mô hình ER

(cid:139) Kỹ thuật tạo mô hình ER dùng ngôn ngữ UML

tkiết kế database

(cid:139) Nhận diện và xử lý các lỗi của mô hình ER

(cid:139) Xây dựng mô hình ER theo nhu cầu

(Unified Modeling Language)

2

Chương 3: Mô hình thực thể - Mối kết hợp Trang 1

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

ER diagram of Branch view of DreamHome

3

khai.hq@ou.edu.vn

Các khái niệm của mô hình ER

(cid:139) Kiểu của thực thể (Entity types)

(cid:139) Kiểu của mối kết hợp (Relationship types)

(cid:139) Thuộc tính (Attributes)

4

Chương 3: Mô hình thực thể - Mối kết hợp Trang 2

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Kiểu của thực thể (Entity type)

(cid:139) 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

(cid:139) Thể hiện của thực thể (Entity occurrence)

đối tượng tồn tại độc lập nhau

– 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)

5

khai.hq@ou.edu.vn

ER diagram of Staff and Branch entity types

6

Chương 3: Mô hình thực thể - Mối kết hợp Trang 3

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Kiểu của mối kết hợp (Relationship type)

(cid:139) 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ể

(cid:139) 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ể

7

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)

8

Chương 3: Mô hình thực thể - Mối kết hợp Trang 4

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

ERD của mối kết hợp “Branch Has Staff”

9

khai.hq@ou.edu.vn

Các loại mối kết hợp

(cid:139) 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

(cid:139) 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.

10

Chương 3: Mô hình thực thể - Mối kết hợp Trang 5

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Mối kết hợp binary “POwns”

11

khai.hq@ou.edu.vn

Mối kết hợp Ternary “Registers”

12

Chương 3: Mô hình thực thể - Mối kết hợp Trang 6

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Mối kết hợp Quaternary “Arranges”

13

khai.hq@ou.edu.vn

Các loại mối kết hợp

(cid:139) 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

(cid:139) 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 đó

14

Chương 3: Mô hình thực thể - Mối kết hợp Trang 7

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Mối kết hợp hồi quy “Supervises”

15

khai.hq@ou.edu.vn

Hai thực thể có 2 mối kết hợp khác nhau

16

Chương 3: Mô hình thực thể - Mối kết hợp Trang 8

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Thuộc tính (Attribute)

(cid:139) Thuộc tính

– Là đặc tính của một kiểu thực thể hay một kiểu

(cid:139) Miền của thuộc tính

mối kết hợp

– Là tập các giá trị có thể có được của thuộc tính

17

khai.hq@ou.edu.vn

Khóa (Key)

(cid:139) 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

(cid:139) Khóa chính (Primary Key)

được duy nhất từng thể hiện của thực thể

(cid:139) Khóa ghép (Composite Key)

– Là một khóa được chọn ra từ các khóa ứng viên

– Là khóa có từ 2 thuộc tính trở lên

18

Chương 3: Mô hình thực thể - Mối kết hợp Trang 9

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

ER diagram of Staff and Branch entities and their attributes

19

khai.hq@ou.edu.vn

Kiểu thực thể mạnh và yếu

(cid:139) 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

(cid:139) Kiểu thực thể yếu (Weak Entity Type)

thể khác

– Là kiểu thực thể phụ thuộc vào kiểu thực thể

khác

20

Chương 3: Mô hình thực thể - Mối kết hợp Trang 10

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Strong entity type called Client and weak entity type called Preference

21

khai.hq@ou.edu.vn

Relationship called Advertises with attributes

22

Chương 3: Mô hình thực thể - Mối kết hợp Trang 11

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Ràng buộc thuộc về cấu trúc (Structural Constraints) (1)

(cid:139) 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)

(cid:139) 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

(cid:139) Ràng buộc theo các quy tắc nghiệp vụ (business

rules)

23

khai.hq@ou.edu.vn

Ràng buộc thuộc về cấu trúc (Structural Constraints) (2)

(cid:139) 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ể

(cid:139) Các mối kết hợp cấp 2 thường thấy là: one-to-one (1:1) one-to-many (1:*) many-to-many (*:*)

– một – một – một – nhiều – nhiều – nhiều

24

Chương 3: Mô hình thực thể - Mối kết hợp Trang 12

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Mạng ngữ nghĩa của kiểu mối kết hợp “Staff Manages Branch”

25

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)

26

Chương 3: Mô hình thực thể - Mối kết hợp Trang 13

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Mạng ngữ nghĩa của kiểu mối kết hợp “Staff Oversees PropertyForRent”

27

khai.hq@ou.edu.vn

Mức tham gia kết hợp (Multiplicity) của “Staff Oversees PropertyForRent” (1:*)

28

Chương 3: Mô hình thực thể - Mối kết hợp Trang 14

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Mạng ngữ nghĩa của kiểu mối kết hợp “Newspaper Advertises PropertyForRent”

29

khai.hq@ou.edu.vn

Mức tham gia kết hợp (Multiplicity) của “Newspaper Advertises PropertyForRent” (*:*)

30

Chương 3: Mô hình thực thể - Mối kết hợp Trang 15

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Ràng buộc thuộc về cấu trúc (Structural Constraints) (3)

(cid:139) 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

31

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

32

Chương 3: Mô hình thực thể - Mối kết hợp Trang 16

Nhập môn cơ sở dữ liệu www.ou.edu.vn

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”

33

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

34

Chương 3: Mô hình thực thể - Mối kết hợp Trang 1

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Ràng buộc thuộc về cấu trúc (Structural Constraints) (4)

(cid:139) 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.

(cid:139) 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 (cid:139) 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

35

khai.hq@ou.edu.vn

Multiplicity, Cardinality và Participation

36

Chương 3: Mô hình thực thể - Mối kết hợp Trang 2

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Các lỗi có thể có ở mô hình ER (1)

(cid:139) 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.)

(cid:139) Lỗi xảy ra thường do hiểu chưa đúng ý nghĩa của

(cid:139) 2 lỗi bẫy kết nối chính là: lỗi bẫy mơ hồ (fan traps)

mối kết hợp

và lỗi bẫy lỗ hổng (chasm traps)

37

khai.hq@ou.edu.vn

Các lỗi có thể có ở mô hình ER (2)

(cid:139) 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ể

(cid:139) Chasm Trap (lỗi bẫy lỗ hổng)

có tính mơ hồ (ambiguous), không rõ nghĩa.

– Khi không có đủ các mối quan hệ giữa các thể hiện

của thực thể

38

Chương 3: Mô hình thực thể - Mối kết hợp Trang 3

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Một ví dụ Fan Trap (lỗi bẫy mơ hồ)

39

(cid:139) At which branch office does staff number SG37 work?

khai.hq@ou.edu.vn

Cấu trúc lại mô hình ER để loại bỏ lỗi Fan Trap

40

Chương 3: Mô hình thực thể - Mối kết hợp Trang 4

Nhập môn cơ sở dữ liệu www.ou.edu.vn

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

(cid:139) SG37 works at branch B003.

41

khai.hq@ou.edu.vn

Một ví dụ Chasm Trap (lỗi bẫy lỗ hổng)

42

(cid:139) At which branch office is property PA14 available?

Chương 3: Mô hình thực thể - Mối kết hợp Trang 5

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Cấu trúc lại mô hình ER để loại bỏ lỗi Chasm Trap

43

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

44

Chương 3: Mô hình thực thể - Mối kết hợp Trang 6

Nhập môn cơ sở dữ liệu www.ou.edu.vn

Chương 4: Mô hình Quan hệ

Tham khảo tài liệu [A]: Chapter 3: The Relational Model

khai.hq@ou.edu.vn

Nội dung chương 4

(cid:139) Các thuật ngữ của mô hình quan hệ (cid:139) Cách dùng bảng để miêu tả dữ liệu (cid:139) Sự tương đồng giữa mối quan hệ trong toán học và

(cid:139) Thuộc tính của mối quan hệ trong cơ sở dữ liệu (cid:139) Cách xác định khóa ứng viên, khóa chính, và khóa

mối quan hệ trong mô hình quan hệ

(cid:139) Ý nghĩa của “sự toàn vẹn của thực thể” và “sự toàn

ngoại

(cid:139) Mục đích và sự tiện lợi của view

vẹn trong tham khảo”

2

Chương 4: Mô hình Quan hệ Trang 1

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Các thuật ngữ của mô hình quan hệ (1)

(cid:139) Một quan hệ (relation) là một bảng, gồm có các cột

và các hàng

(cid:139) Một thuộc tính (attribute) là một cột của quan hệ

(cid:139) Miền (domain) là tập hợp các giá trị có thể có của

– 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 hay nhiều thuộc tính

3

khai.hq@ou.edu.vn

Các thuật ngữ của mô hình quan hệ (2)

(cid:139) Bộ (tuple) là một hàng của quan hệ

(cid:139) Cấp (hay mức–degree) là số lượng thuộc tính của quan

(cid:139) Số thể hiện (hay số lượng, hay lực lượng – cardinality)

hệ

là số bộ của một quan hệ

(cid:139) 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

4

Chương 4: Mô hình Quan hệ Trang 2

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Một phần các thể hiện của quan hệ “Branch and Staff”

5

khai.hq@ou.edu.vn

Ví dụ về miền của thuộc tính (Attribute Domains)

6

Chương 4: Mô hình Quan hệ Trang 3

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Các dạng thuật ngữ khác nhau của mô hình quan hệ

7

khai.hq@ou.edu.vn

Định nghĩa toán học của mô hình quan hệ (1)

(cid:139) Xét 2 tập hợp D1 và D2, với D1 = {2, 4} và D2 = {1, 3, 5} (cid:139) 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)}

(cid:139) 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

8

Chương 4: Mô hình Quan hệ Trang 4

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Định nghĩa toán học của mô hình quan hệ (2)

(cid:139) Một quan hệ là một tập con của tích Đề-các

(cid:139) Quan hệ có thể được đặc tả bằng điều kiện chọn ra các

ví dụ: quan hệ R = {(2, 1), (4, 1)}

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}

9

khai.hq@ou.edu.vn

Định nghĩa toán học của mô hình quan hệ (3)

(cid:139) 3 tập hợp D1, D2, D3 sẽ có tích Đế-các là D1 × D2 × D3

ví dụ:

D3 = {5, 6} D2 = {2, 4}

(cid:139) Bất kỳ tập con nào của tích Đế-các trên sẽ là một quan

D1 = {1, 3} 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)}

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)}

10

Chương 4: Mô hình Quan hệ Trang 5

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Định nghĩa toán học của mô hình quan hệ (4)

(cid:139) 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

(cid:139) 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

11

khai.hq@ou.edu.vn

Khái niệm Quan hệ trong CSDL (Database Relations)

(cid:139) Lược đồ (của) quan hệ (Relation schema)

(cid:139) Lược đồ (của) cơ sở dữ liệu quan hệ

– 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

(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

12

Chương 4: Mô hình Quan hệ Trang 6

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Các thuộc tính của quan hệ

(cid:139) Tên của các quan hệ không được trùng nhau

(cid:139) Mỗi ô (cell) trong quan hệ chứa đúng một giá trị đơn trị

(cid:139) Tên thuộc tính không được trùng nhau

(cid:139) Tất cả giá trị của một thuộc tính phải có cùng một miền

(cid:139) Không có 2 hay nhiều bộ (tuple) giống nhau

(cid:139) Không bắt buộc thứ tự của các thuộc tính trong quan hệ

(cid:139) Không bắt buộc thứ tự của các bộ của quan hệ

giá trị (cùng domain)

13

khai.hq@ou.edu.vn

Khóa của quan hệ (Relational Keys) (1)

(cid:139) 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

(cid:139) Khóa ứng viên (Candidate Key)

định duy nhất một bộ trong quan hệ

– 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ệ

14

Chương 4: Mô hình Quan hệ Trang 7

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Khóa của quan hệ (Relational Keys) (2)

(cid:139) 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ệ (cid:139) Khóa thay thế (Alternate Keys)

– Là các khóa ứng viên không được chọn làm khóa

(cid:139) Khóa ngoại (Foreign Key)

chính

– 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 đó

15

khai.hq@ou.edu.vn

Sự toàn vẹn của quan hệ (Relational Integrity) (1)

(cid:139) 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

16

Chương 4: Mô hình Quan hệ Trang 8

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Sự toàn vẹn của quan hệ (Relational Integrity) (2)

(cid:139) 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á

(cid:139) Sự toàn vẹn trong tham khảo (Referential Integrity)

trị NULL

– 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

(cid:139) 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

17

khai.hq@ou.edu.vn

Khung nhìn dữ liệu (Views) (1)

(cid:139) 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

(cid:139) Khung nhìn dữ liệu (View)

vật lý) trong cơ sở dữ liệu

– 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

18

Chương 4: Mô hình Quan hệ Trang 9

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

Khung nhìn dữ liệu (Views) (2)

(cid:139) 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

(cid:139) 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

(cid:139) 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

19

khai.hq@ou.edu.vn

Mục đích của View

(cid:139) 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

(cid:139) 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

(cid:139) 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

20

Chương 4: Mô hình Quan hệ Trang 10

Nhập môn cơ sở dữ liệu www.ou.edu.vn

khai.hq@ou.edu.vn

View và việc cập nhật dữ liệu (1)

(cid:139) 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 đó

(cid:139) Nếu view cập nhật được dữ liệu thì các quan hệ nền

(cid:139) Phân view làm 3 lớp:

phía dưới cũng sẽ chịu ảnh hưởng theo

– 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)

21

khai.hq@ou.edu.vn

View và việc cập nhật dữ liệu (2)

(cid:139) 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

22

Chương 4: Mô hình Quan hệ Trang 11

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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

khai.hq@ou.edu.vn

Nội dung chương 5

(cid:139) Ý nghĩa của khái niệm “relational completeness”

(cid:139) Cách tạo query trong đại số quan hệ

(cid:139) Cách tạo query trong phép toán quan hệ trên bộ

(cid:139) Cách tạo query trong phép toán quan hệ trên miền

(cid:139) Các loại ngôn ngữ thao tác dữ liệu quan hệ

2

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 1

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Giới thiệu

(cid:139) Đại số quan hệ và phép toán quan hệ là các ngôn

(cid:139) Có thể xem: đại số quan hệ là ngôn ngữ thủ tục

ngữ chuẩn của mô hình quan hệ

(cid:139) Tuy nhiên về mặt lý thuyết thì đại số quan hệ và

(cấp cao), và phép toán quan hệ là ngôn ngữ phi thủ tục

phép toán quan hệ là tương đương nhau

(cid:139) 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”

3

khai.hq@ou.edu.vn

Đại số quan hệ

(cid:139) 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

(cid:139) Cả các toán hạng và các kết quả đều là các quan

(cid:139) Cho phép các biểu thức có thể lồng vào nhau giống

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

như các biểu thức số học. Đặc tính này gọi là closure.

4

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 2

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Đại số quan hệ

(cid:139) 5 toán tử cơ bản trong ĐS quan hệ:

(cid:139) Các toán tử cơ bản thực hiện được hầu hết các thao

Chọn (Selection), Chiếu (Projection), Tích Đề- các (Cartesian product), Hội (Union), và Trừ (Set Difference)

tác truy xuất dữ liệu

(cid:139) 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

5

khai.hq@ou.edu.vn

Các phép toán trong đại số quan hệ

6

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 3

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Các phép toán trong đại số quan hệ (tt)

7

khai.hq@ou.edu.vn

Phép chọn (Selection or Restriction)

(cid:139) σđ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

8

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 4

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép chọn

(cid:139) 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)

9

khai.hq@ou.edu.vn

Phép chiếu (Projection)

(cid:139) Π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

10

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 5

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép chiếu

(cid:139) 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)

11

khai.hq@ou.edu.vn

Phép hội (Union)

(cid:139) 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

(cid:139) Nếu R có i bộ và S có j bộ thì phép hội có tối đa (i+j) bộ

được

12

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 6

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép hội

(cid:139) 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)

13

khai.hq@ou.edu.vn

Phép trừ (Set Difference)

(cid:139) 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

14

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 7

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép trừ

(cid:139) 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)

15

khai.hq@ou.edu.vn

Phép giao (Intersection)

(cid:139) R ∩ S

– Tạo ra một quan hệ chứa các bộ vừa thuộc R

vừa thuộc S

(cid:139) Biểu diễn phép giao bằng phép trừ:

– R và S phải có tính tương thích nhau

R ∩ S = R – (R – S)

16

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 8

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép giao

(cid:139) 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)

17

khai.hq@ou.edu.vn

Phép tích Đề-các (Cartesian product)

(cid:139) 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

18

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 9

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép tích Đề-các

(cid:139) 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))

19

khai.hq@ou.edu.vn

Ví dụ về phép tích Đề-các và phép chọn

(cid:139) 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)))

(cid:139)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)

20

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 10

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phép Kết (Join)

(cid:139) Phép kết phát sinh từ phép tích Đề-các

(cid:139) 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

21

khai.hq@ou.edu.vn

Phép Kết (Join) (tt)

(cid:139) 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)

22

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 11

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phép kết Theta (θ-join)

(cid:139) 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 <, ≤, >, ≥, =, ≠

23

khai.hq@ou.edu.vn

Phép kết Theta (θ-join) (tt)

(cid:139) Có thể biểu diễn phép kết Theta:

(cid:139) Cấp của phép kết Theta là tổng của cấp 2 quan hệ

R FS = σF(R Χ S)

(cid:139) Nếu trong điều kiện F sử dụng dấu = thì có tên mới là

tham gia

phép kết tương đương

24

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 12

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép kết tương đương

(cid:139) 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))

25

khai.hq@ou.edu.vn

Phép kết tự nhiên (Natural Join)

(cid:139) 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

26

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 13

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép kết tự nhiên

(cid:139) 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))

27

khai.hq@ou.edu.vn

Phép kết ngoài (Outer join)

(cid:139) Để hiển thị trong kết quả của phép kết các hàng

(cid:139) R S

không có các cột kết bằng nhau

– 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

28

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 14

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phép kết ngoài bên trái

(cid:139) 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

29

khai.hq@ou.edu.vn

Phép nửa kết (Semijoin)

(cid:139) R FS

– Tạo ra 1 quan hệ có các bộ là kết quả từ R có tham

(cid:139) Có thể viết lại phép nửa kết bằng phép Chiếu và phép

gia vào phép kết với S

kết:

R FS = ΠA(R F S)

A là các thuộc tính của R

30

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 15

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ phép nửa kết

(cid:139) 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

31

khai.hq@ou.edu.vn

Phép chia (Division)

(cid:139) 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

(cid:139) Biểu diễn cách khác: T1 ← ΠC(R) T2 ← ΠC((S X T1) – R) T ← T1 – T2

32

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 16

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ phép chia

(cid:139) 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)))

33

khai.hq@ou.edu.vn

Phép toán quan hệ (Relational Calculus)

(cid:139) Phép toán quan hệ để diễn đạt “muốn gì?” (what) chứ

(cid:139) Trong phép tóan luận lý tiền tố (hay phép toán vị từ), vị

không diễn đạt phải làm gì (how) để có được điều mong muốn

(cid:139) Phép toán vị từ có kết quả là đúng (true) hay sai (false) (cid:139) Phép toán vị từ trên CSDL có 2 loại: phép toán trên bộ

từ (predicate ) là 1 hàm có đối số nằm phía sau

và phép toán trên miền của thuộc tính

34

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 17

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phép toán quan hệ trên bộ

(cid:139) Có mục đích tìm ra các bộ làm cho vị từ có giá trị

(cid:139) Biến trên bộ là biến nhận giá trị là 1 bộ của quan hệ (cid:139) Ví dụ: giá trị của 1 biến S có thể là 1 bộ của quan hệ

true. Sử dụng các biến trên bộ để tìm

Staff:

(cid:139) Tìm tất cả giá trị S: biểu thức sau phải thỏa (true) :

Staff(S)

{S | P(S)}

35

khai.hq@ou.edu.vn

Ví dụ phép toán quan hệ trên bộ

(cid:139) Lập danh sách chi tiết về các nhân viên có lương

lớn hơn 10000

(cid:139) Lập danh sách 1 cột lương chỉ lấy giá trị lớn hơn

{S | Staff(S) ∧ S.salary > 10000}

10000

{S.salary | Staff(S) ∧ S.salary > 10000}

36

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 18

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phép toán quan hệ trên bộ

(cid:139) 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”: ∀

37

khai.hq@ou.edu.vn

Ví dụ phép toán quan hệ trên bộ

(cid:139) 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:

(cid:139) Có nghĩa là: Tồn tại ít nhất 1 chi nhánh (1 bộ)

Staff(S) ∧ (∃B)(Branch(B) ∧ (B.branchNo = S.branchNo) ∧ B.city = ‘London’)

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’.

38

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 19

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phép toán quan hệ trên bộ

(cid:139) Lượng từ “với mọi” ∀ được dùng trên mỗi bộ như:

(∀B) (B.city ≠ ‘Paris’)

(cid:139) 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’

(cid:139) Ngược lại: ~(∃B) (B.city = ‘Paris’) có nghĩa “không có

chi nhánh nào có địa chỉ ở Paris’.

39

khai.hq@ou.edu.vn

Phép toán quan hệ trên bộ

(cid:139) Các công thức phải tránh sự mơ hồ và có ý nghĩa (cid:139) 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

(cid:139) 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

40

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 20

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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’)}

41

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)))}

42

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 21

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về Phép toán quan hệ trên bộ

(cid:139) 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’))}

43

khai.hq@ou.edu.vn

Chú ý về Phép toán quan hệ trên bộ

(cid:139) Các biểu thức có thể tạo 1 tập vô hạn không xác

định: {S | ~Staff(S)}

(cid:139) Để 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

44

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 22

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phép toán quan hệ trên miền (Domain Relational Calculus)

(cid:139) Dùng các biến có giá trị từ miền chứ không phải từ bộ

của quan hệ

(cid:139) 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

45

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)}

46

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 23

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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’)}

47

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))))}

48

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 24

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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’)}

49

khai.hq@ou.edu.vn

Phép toán quan hệ trên miền

(cid:139) 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

50

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 25

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Các ngôn ngữ khác

(cid:139) 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).

(cid:139) 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)

51

khai.hq@ou.edu.vn

Các ngôn ngữ khác

(cid:139) 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.

(cid:139) 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.

52

Chương 5: Đại số quan hệ và Phép toán quan hệ

Trang 26

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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

khai.hq@ou.edu.vn

(cid:1) Mục đích và tầm quan trọng của SQL (cid:1) Truy xuất DL từ CSDL với lệnh SELECT và:

Nội dung chương 6:

– 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)

2

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 1

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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,

(cid:1) Cập nhật DL dùng INSERT, UPDATE, và DELETE.

INTERSECT, EXCEPT).

3

khai.hq@ou.edu.vn

Mục tiêu của ngôn ngữ SQL

(cid:1) 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ệ

(cid:1) Ngôn ngữ của CSDL phải mạnh và dễ học (cid:1) Ngôn ngữ của CSDL phải có tính cơ động (khả

– Thực hiện các truy vấn dữ liệu

chuyển)

4

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 2

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Mục tiêu của ngôn ngữ SQL

(cid:1) 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ữ

(cid:1) Ngôn ngữ SQL có định dạng mềm dẻo (cid:1) Ngôn ngữ SQL thuộc loại ngôn ngữ phi thủ tục (chỉ

liệu

cần miêu tả CÁI GÌ chúng ta muốn)

5

khai.hq@ou.edu.vn

Mục tiêu của ngôn ngữ SQL

(cid:1) 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;

6

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 3

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Mục tiêu của ngôn ngữ SQL

(cid:1) 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

(cid:1) 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ệ.

7

khai.hq@ou.edu.vn

Lịch sử ngôn ngữ SQL

(cid:1) Vào 1974, D. Chamberlin (IBM San Jose Laboratory) định 'Structured English Query Language'

nghĩa ngôn ngữ (SEQUEL).

(cid:1) 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 đó)

(cid:1) 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'.

(cid:1) Vào 1992, ra đời phiên bản ISO standard tên là SQL2 hay

SQL/92.

(cid:1) Vào 1999, SQL3 ra đời, có hỗ trợ quản trị dữ liệu hướng đối

tượng

8

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 4

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Viết lệnh SQL

(cid:1) 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

– Từ do user định nghĩa (User-defined words) như tên

ngôn ngữ SQL

quan hệ, tên cột, tên view, …

9

khai.hq@ou.edu.vn

Viết lệnh SQL

(cid:1) Hầu hết các từ trong câu lệnh SQL không phân biệt

(cid:1) Để dễ đọc, nên viết câu lệnh SQL theo cách có canh lề

chữ thường/chữ HOA, trừ một số hằng ký tự

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

10

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 5

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Viết lệnh SQL

(cid:1) 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).

11

khai.hq@ou.edu.vn

Các giá trị hằng

(cid:1) Các giá trị hằng không phải là số phải nằm trong dấu

(cid:1) Các giá trị hằng là số không nằm trong dấu nháy nào

nháy đơn (vd. ‘London’).

cả (vd. 650.00).

12

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 6

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Câu lệnh SELECT

SELECT [DISTINCT | ALL]

{* | [BiểuThứcCột [AS TênMới]] [,...] }

TênBảng [BíDanh] [, ...] ĐiềuKiện]

FROM [WHERE [GROUP BY DanhSáchCột]

[HAVING ĐiềuKiệnChoNhóm]

[ORDER BY DanhSáchCột]

13

khai.hq@ou.edu.vn

Câu lệnh SELECT

FROM WHERE GROUP BY

Các bảng được dùng Điều kiện lọc các hàng dữ liệu Tạo nhóm các hàng có giá trị giống nhau ở 1 hay nhiều cột

HAVING SELECT ORDER BY Điều kiện lọc để lấy 1 số nhóm Các cột muốn có trong kết quả Sắp thứ tự các hàng trong kết quả

14

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 7

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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

(cid:1) Có thể dùng dấu * để miêu tả “lấy tất cả các cột:

FROM Staff;

SELECT * FROM Staff;

15

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

16

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 8

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.2: Lấy 1 số cột

SELECT staffNo, fName, lName, salary FROM Staff;

17

khai.hq@ou.edu.vn

Ví dụ 6.3 Không sử dụng DISTINCT

SELECT propertyNo FROM Viewing;

18

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 9

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.3 Có sử dụng DISTINCT

(cid:1) Dùng DISTINCT để loại bỏ dl trùng lắp:

SELECT DISTINCT propertyNo FROM Viewing;

19

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;

20

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 10

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.4 Dùng các field tính toán

(cid:1) Dùng AS TênFieldMới:

SELECT staffNo, fName, lName, salary/12 AS monthlySalary

FROM Staff;

21

khai.hq@ou.edu.vn

Ví dụ 6.5

SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > 10000;

22

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 11

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.6

SELECT * FROM Branch WHERE city = 'London' OR city = 'Glasgow';

23

khai.hq@ou.edu.vn

Ví dụ 6.7

SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary BETWEEN 20000 AND 30000;

24

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 12

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.7

(cid:1) Thay thế BETWEEN

SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary>=20000 AND salary <= 30000;

25

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');

26

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 13

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.8 Toán tử IN

(cid:1) Thay thế IN

SELECT staffNo, fName, lName, position FROM Staff WHERE position='Manager' OR

position=‘Supervisor';

27

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%';

28

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 14

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.9 Toán tử LIKE

(cid:1) 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ự

29

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;

30

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 15

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.10 dùng giá trị NULL

(cid:1) Có thể dùng (IS NOT NULL) cho điều kiện ngược lại

31

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;

32

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 16

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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;

33

khai.hq@ou.edu.vn

Các hàm thống kê trong lệnh SELECT

(cid:1) 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

34

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 17

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Các hàm thống kê trong lệnh SELECT

(cid:1) COUNT, MIN và MAX dùng cho cả các giá trị số và

(cid:1) SUM và AVG chỉ dùng cho cả các giá trị số

(cid:1) Trừ hàm COUNT(*), các hàm khác loại bỏ giá trị

không phải số

(cid:1) COUNT(*) đếm tất cả các hàng kể cả giá trị NULL và

NULL trước rồi mới tính kết quả

(cid:1) Muốn loại bỏ trùng lắp ta dùng DISTINCT

trùng lắp

35

khai.hq@ou.edu.vn

Các hàm thống kê trong lệnh SELECT

(cid:1) Các hàm thống kê chỉ dùng sau SELECT và HAVING

(cid:1) 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;

36

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 18

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.13 Dùng hàm COUNT(*)

SELECT COUNT(*) AS count FROM PropertyForRent WHERE rent > 350;

37

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';

38

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 19

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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';

39

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;

40

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 20

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Lệnh SELECT có GROUP BY

(cid:1) 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)

(cid:1) 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

41

khai.hq@ou.edu.vn

Lệnh SELECT có GROUP BY

(cid:1) 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ê.

(cid:1) 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.

(cid:1) Chuẩn ISO qui định 2 giá trị NULL là giống nhau để

tiện sử dụng với GROUP BY.

42

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 21

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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;

43

khai.hq@ou.edu.vn

Ví dụ 6.17 Dùng GROUP BY

44

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 22

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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;

45

khai.hq@ou.edu.vn

Ví dụ 6.18 Dùng HAVING

46

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 23

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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');

47

khai.hq@ou.edu.vn

Ví dụ 6.19 Dùng truy vấn con

48

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 24

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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);

49

khai.hq@ou.edu.vn

Ví dụ 6.20 Dùng truy vấn con và hàm thống kê

50

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 25

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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'));

51

khai.hq@ou.edu.vn

Ví dụ 6.21 TRUY VẤN CON LỒNG NHAU, sử dụng IN

52

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 26

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

ANY và ALL

(cid:1) ANY và ALL có thể dùng với truy vấn con để tạo ra

(cid:1) Với ALL, điều kiện chỉ đúng khi nó thỏa với tất cả

một cột số

(cid:1) Với ANY, điều kiện đúng khi nó thỏa với bất kỳ giá

các giá trị của truy vấn con.

(cid:1) Nếu truy vấn con rỗng thì ALL trả về giá trị TRUE,

trị nào của truy vấn con.

(cid:1) SOME có thể dùng thay cho ANY.

ANY trả về giá trị FALSE.

53

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');

54

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 27

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.22 Dùng ANY/SOME

(cid:1) 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.

55

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');

56

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 28

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.23 Dùng ALL

57

khai.hq@ou.edu.vn

Truy vấn trên nhiều bảng

(cid:1) Có thể dùng truy vấn con trên cùng bảng dữ liệu với

(cid:1) Nếu các cột dữ liệu của kết quả được lấy từ nhiều bảng

truy vấn chính.

(cid:1) Để thực hiện phép kết, phải liệt kê các bảng tham gia

thì phải sử dụng phép kết bảng.

phép kết vào mệnh đề FROM.

58

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 29

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Truy vấn trên nhiều bảng

(cid:1) Có thể dùng bí danh cho các bảng trong mệnh đề

(cid:1) Bí danh đứng sau tên bảng, cách tên bảng 1 khoảng

FROM.

(cid:1) Bí danh giúp tránh sự lầm lẫn (tốt hơn cách chỉ sử

trống.

dụng tên bảng).

59

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;

60

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 30

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.24 Phép kết đơn giản

(cid:1) Chỉ có những hàng ở 2 bảng cùng thỏa (c.clientNo =

(cid:1) Đây là một phép kết tương đương.

v.clientNo) mới có mặt trong kết quả.

61

khai.hq@ou.edu.vn

Dùng phép kết với lệnh JOIN

(cid:1) 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

(cid:1) Trong 3 cách trên, FROM đã thay gồm luôn phần

(cid:1) Cách thứ nhất tạo ra 2 cột giống hệt nhau.

WHERE của phép kết.

62

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 31

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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;

63

khai.hq@ou.edu.vn

Ví dụ 6.26 Kết 3 bảng

(cid:1) Một cách khác viết FROM :

FROM (branch b JOIN Staff s USING branchNo) AS

bs JOIN PropertyForRent p USING staffNo

64

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 32

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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;

65

khai.hq@ou.edu.vn

Ví dụ 6.27 Nhóm nhiều cột

66

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 33

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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

67

khai.hq@ou.edu.vn

Phép kết ngoài

(cid:1) Phép kết ngoài cho phép giữ lại các hàng không thỏa

(cid:1) Ví dụ xét 2 bảng sau:

điều kiện kết

68

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 34

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phép kết ngoài

(cid:1) Phép kết (trong) của 2 bảng:

SELECT b.*, p.* FROM Branch1 b, PropertyForRent1 p WHERE b.bCity = p.pCity;

69

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;

70

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 35

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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;

71

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;

72

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 36

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

EXISTS và NOT EXISTS

(cid:1) EXISTS và NOT EXISTS chỉ dùng với các truy vấn

(cid:1) EXISTS chỉ có giá trị True khi và chỉ khi truy vấn con

con, có kết quả là true hoặc false

(cid:1) EXISTS có giá trị Fasle khi truy vấn con có kết quả

có tồn tại ít nhất 1 hàng.

(cid:1) NOT EXISTS có giá trị ngược với EXISTS.

rỗng.

73

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');

74

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 37

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.31 Truy vấn dùng EXISTS

75

khai.hq@ou.edu.vn

Ví dụ 6.31 Truy vấn dùng EXISTS

(cid:1) 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';

76

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 38

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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);

77

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);

78

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 39

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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);

79

khai.hq@ou.edu.vn

Ví dụ 6.33 Dùng phép giao INTERSECT

(cid:1) Hay:

(SELECT * FROM Branch) INTERSECT CORRESPONDING BY city (SELECT * FROM PropertyForRent);

80

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 40

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.33 Dùng phép giao INTERSECT

(cid:1) Có thể viết lại không dùng INTERSECT:

(cid:1) Hay:

SELECT b.city FROM Branch b PropertyForRent p WHERE b.city = p.city;

SELECT DISTINCT city FROM Branch b WHERE EXISTS (SELECT * FROM PropertyForRent p WHERE p.city = b.city);

81

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.

(cid:1) Or

(SELECT city FROM Branch) city FROM EXCEPT (SELECT PropertyForRent);

(SELECT * FROM Branch) EXCEPT CORRESPONDING BY city (SELECT * FROM PropertyForRent);

82

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 41

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ 6.34 Dùng phép trừ EXCEPT

(cid:1) Có thể viết lại không dùng EXCEPT:

(cid:1) Hay:

SELECT DISTINCT city FROM Branch WHERE city NOT IN (SELECT city FROM PropertyForRent);

SELECT DISTINCT city FROM Branch b WHERE NOT EXISTS (SELECT * FROM PropertyForRent p WHERE p.city = b.city);

83

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)

(cid:1) 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.

(cid:1) 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

84

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 42

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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');

85

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',

(cid:1) Hay:

'Assistant', 8100, 'B003');

INSERT INTO Staff VALUES ('SG44', 'Anne', 'Jones', 'Assistant', NULL,

NULL, 8100, 'B003');

86

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 43

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dùng lệnh INSERT INTO… SELECT…

(cid:1) 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 ...

87

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.

88

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 44

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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));

89

khai.hq@ou.edu.vn

Ví dụ 6.37 Dùng lệnh INSERT INTO… SELECT…

(cid:1) 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.

90

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 45

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Lệnh sửa dữ liệu UPDATE

UPDATE TênBảng SET TênCột1 = DữLiệu1

(cid:1) TableName can be name of a base table or an

[, TênCột2 = DữLiệu2...] [WHERE D9ie62uKie65n]

(cid:1) SET clause specifies names of one or more columns

updatable view.

that are to be updated.

91

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

(cid:1) Tên bảng có thể thế bằng tên view loại cập nhật được. (cid:1) Các dữ liệu phải tương thích kiểu với từng cột.

[, TênCột2 = DữLiệu2...] [WHERE ĐiềuKiệnLọcTìm]

92

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 46

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

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';

93

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';

94

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 47

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Lệnh xóa các hàng dữ liệu DELETE

(cid:1) Tên bảng có thể thế bằng tên view loại cập nhật được.

(cid:1) Nếu không có điều kiện lọc tìm dữ liệu thì câu lệnh sẻ

DELETE FROM TênBảng [WHERE ĐiềuKiệnLọcTìmDL]

xóa tất cả các hàng dữ liệu.

95

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

96

Chương 6: Ngôn ngữ SQL thao tác dữ liệu

Trang 48

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

khai.hq@ou.edu.vn

Nội dung chương 7

(cid:139) Các kiểu dữ liệu của SQL chuẩn.

(cid:139) Mục đích của việc tăng cường tính toàn vẹn của SQL.

(cid:139) Cách định nghĩa các ràng buộc toàn vẹn dùng SQL.

(cid:139) Cách viết lệnh CREATE và ALTER TABLE có tăng

cường tính toàn vẹn dữ liệu.

2

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

Nội dung chương 7

(cid:139) Mục đích của VIEW (cid:139) Tạo và xóa VIEW dùng SQL. (cid:139) Cách DBMS thực hiện các thao tác trên VIEW. (cid:139) Điếu kiện để VIEW cập nhật dữ liệu được (cid:139) Ưu và nhược điểm của VIEW (cid:139) Mô hình giao tác theo chuẩn ISO (cid:139) Dùng lệnh GRANT và REVOKE

3

khai.hq@ou.edu.vn

Các kiểu dữ liệu của SQL chuẩn ISO

4

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

khai.hq@ou.edu.vn

Đặc tính tăng cường tính toàn vẹn dữ liệu

(cid:139) 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

5

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 NOT NULL

CHAR CHECK (sex IN ('M', 'F'))

6

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

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')); SexType NOT NULL sex

7

khai.hq@ou.edu.vn

Đặc tính tăng cường tính toàn vẹn dữ liệu

(cid:139) searchCondition có thể tìm trong một bảng dữ liệu :

CREATE DOMAIN BranchNo AS CHAR(4) CHECK (VALUE IN (SELECT branchNo

(cid:139) Miền có thể được xóa bằng DROP DOMAIN:

FROM Branch));

DROP DOMAIN DomainName [RESTRICT | CASCADE]

8

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

khai.hq@ou.edu.vn

Ràng buộc về thực thể

(cid:139) Khóa chính của một bảng phải chứa một giá trị duy

(cid:139) Chuẩn ISO hỗ trợ mệnh đề FOREIGN KEY trong câu

nhất và khác NULL

lệnh CREATE và ALTER TABLE

PRIMARY KEY(staffNo) PRIMARY KEY(clientNo, propertyNo)

(cid:139) 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)

9

khai.hq@ou.edu.vn

Ràng buộc trong tham khảo dữ liệu

(cid:139) 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.

(cid:139) Vậy nếu FK chứa một giá trị, thì giá trị đó phải tham

(cid:139) Chuẩn ISO hỗ trợ định nghĩa FOREIGN KEY trong

khảo đến một hàng hiện hữu ở bảng cha của FK.

CREATE và ALTER TABLE:

FOREIGN KEY(branchNo) REFERENCES Branch

10

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

khai.hq@ou.edu.vn

Ràng buộc trong tham khảo dữ liệu

(cid:139) 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.

(cid:139) 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 DEFAULT - SET NULL, - NO ACTION

11

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)

12

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

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

13

khai.hq@ou.edu.vn

Ràng buộc từ phía công ty

(cid:139) Có thể dùng CHECK/UNIQUE trong câu

lệnh

(cid:139) Hay:

CREATE và ALTER TABLE.

(cid:139) Giống như sử dụng mệnh đề CHECK

CREATE ASSERTION AssertionName CHECK (searchCondition)

14

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

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))

15

khai.hq@ou.edu.vn

Định nghĩa dữ liệu

(cid:139) SQL DDL cho phép tạo và xóa schema, domain, table,

(cid:139) Các câu lệnh SQL DDL chính là:

view, và index

DROP SCHEMA

DROP DOMAIN

(cid:139) Nhiều DBMS cho phép:

CREATE SCHEMA CREATE/ALTER DOMAIN CREATE/ALTER TABLEDROP TABLE CREATE VIEW DROP VIEW

CREATE INDEX DROP INDEX

16

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

khai.hq@ou.edu.vn

Định nghĩa dữ liệu

(cid:139) Các quan hệ và các đối tượng CSDL khác tồn tại trong

(cid:139) Mỗi môi trường chứa 1 hay nhiều catalog, mỗi catalog

một môi trường (environment)

(cid:139) Lược đồ có tên theo ý nghĩa là tập hợp các đối tượng

bao gồm tập hợp các lược đồ (schema)

CSDL có liên quan nhau

(cid:139) 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ả.

17

khai.hq@ou.edu.vn

CREATE SCHEMA

CREATE SCHEMA [Name |

AUTHORIZATION CreatorId ]

(cid:139) Với RESTRICT (default), schema phải rỗng, nếu

DROP SCHEMA Name [RESTRICT | CASCADE ]

(cid:139) Với CASCADE sẽ xóa dây chuyền tất cả các đối tượng

không rỗng sẽ không xóa được

có liên quan đến schema

18

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

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)] [,…] })

19

khai.hq@ou.edu.vn

CREATE TABLE

(cid:139) Tạo một bảng với các cột, mỗi cột có kiểu dữ liệu riêng (cid:139) Với NOT NULL, cột không chấp nhận giá trị NULL (cid:139) Mỗi cột có thể đặt một giá trị DEFAULT (cid:139) Khóa chính phải đặt NOT NULL (cid:139) FOREIGN KEY có chỉ ra chế độ thao tác xóa/sửa ở cột

có liên quan bảng cha

20

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

khai.hq@ou.edu.vn

Ví dụ 7.1 - CREATE TABLE

(cid:139)CREATE DOMAIN OwnerNumber AS VARCHAR(5)

CHECK (VALUE IN (SELECT ownerNo FROM Owner));

(cid:139)CREATE DOMAIN StaffNumber AS VARCHAR(5)

CHECK (VALUE IN (SELECT staffNo FROM Staff));

(cid:139)CREATE DOMAIN PNumber AS VARCHAR(5);

(cid:139)CREATE DOMAIN PRooms AS SMALLINT;

CHECK(VALUE BETWEEN 1 AND 15);

(cid:139)CREATE DOMAIN PRent AS DECIMAL(6,2)

CHECK(VALUE BETWEEN 0 AND 9999.99);

21

khai.hq@ou.edu.vn

Ví dụ 7.1 - CREATE TABLE CREATE TABLE PropertyForRent (

NOT NULL, …. NOT NULL DEFAULT 4, NOT NULL, DEFAULT 600,

propertyNo PNumber rooms rent ownerNo staffNo

PRooms PRent OwnerNumber NOT NULL, StaffNumber Constraint StaffNotHandlingTooMuch ….

NOT NULL,

branchNo BranchNumber PRIMARY KEY (propertyNo), FOREIGN KEY (staffNo) REFERENCES Staff

ON DELETE SET NULL ON UPDATE CASCADE ….);

22

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

khai.hq@ou.edu.vn

ALTER TABLE

(cid:139) Thêm một cột mới cho một bảng (cid:139) Xóa một cột của một bảng (cid:139) Thêm một ràng buộc cho bảng (cid:139) Xóa một ràng buộc của bảng (cid:139) Đặt giá trị default cho một cột (cid:139) Xóa việc đặt giá trị default cho một cột

23

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';

24

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

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;

25

khai.hq@ou.edu.vn

DROP TABLE

DROP TABLE TableName [RESTRICT | CASCADE]

(cid:139) Xóa bảng và dữ liệu trong bảng (cid:139) Với RESTRICT, nếu các đối tượng khác có dùng bảng

ví dụ: DROP TABLE PropertyForRent;

(cid:139) Với CASCADE: xóa dây chuyền

thì không thể xóa bảng

26

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

khai.hq@ou.edu.vn

View

(cid:139) 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

(cid:139) 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ó

(cid:139) 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.

27

khai.hq@ou.edu.vn

Lệnh CREATE VIEW

CREATE VIEW ViewName [ (newColumnName [,...]) ]

AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION]

(cid:139)Có thể gán tên cho mỗi cột của view. (cid:139)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. (cid:139)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.

28

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

khai.hq@ou.edu.vn

Lệnh CREATE VIEW

(cid:139) Nên liệt kê các tên của view để tránh sự nhầm lẫn. (cid:139) Phần subselect được gọi là phần truy vấn định nghĩa

view (defining query).

(cid:139) 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.

29

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';

30

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

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';

31

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;

32

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

khai.hq@ou.edu.vn

Ví dụ 7.5 - Tạo View có nhóm, có kết dữ liệu

33

khai.hq@ou.edu.vn

Lệnh DROP VIEW

(cid:139) Để xóa view trong cơ sở dữ liệu . (cid:139) Ví dụ:

DROP VIEW ViewName [RESTRICT | CASCADE]

(cid:139) Với CASCADE: xóa dây chuyền (cid:139) Với RESTRICT (mặc định): không cho xóa view nếu

DROP VIEW Manager3Staff;

các đối tượng khác có sử dụng view

34

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

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;

35

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

36

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

khai.hq@ou.edu.vn

Sự phân giải view (view phân giải)

(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;

37

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.

38

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

khai.hq@ou.edu.vn

Giới hạn cho view

(cid:139) Ví dụ, truy vấn sau là sai:

(cid:139) Tương tự, truy vấn sau là sai :

SELECT COUNT(cnt) FROM StaffPropCnt;

SELECT * FROM StaffPropCnt WHERE cnt > 2;

39

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.

(cid:139) 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.

40

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

khai.hq@ou.edu.vn

Việc cập nhật view

(cid:139) Cập nhật dữ liệu từ các bảng nền sẽ kéo theo sự cập

(cid:139) Tuy nhiên chiều ngược lại là không phải bao giờ cũng

nhật dữ liệu lên các view có liên quan

thực hiện được.

41

khai.hq@ou.edu.vn

Việc cập nhật view

(cid:139) 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);

(cid:139) 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.

42

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

khai.hq@ou.edu.vn

Việc cập nhật view

(cid:139) 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

43

khai.hq@ou.edu.vn

Dùng WITH CHECK OPTION

(cid:139) Các hàng trong có mặt trong view vì thỏa điều kiện

(cid:139) Nếu một hàng không còn thỏa điều kiện WHERE sẽ

WHERE.

(cid:139) Các hàng mới có thể sẽ xuất hiện trong view khi có sự

biến mất khỏi view.

(cid:139) Các hàng mới có hoặc bị mất đi khỏi view gọi là các

chèn/thêm dữ liệu (nếu thỏa điều kiện WHERE)

(cid:139) WITH CHECK OPTION cấm các hàng dịch chuyển ra

hàng dịch chuyển vào ra (migrating rows)

khỏi view.

44

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

khai.hq@ou.edu.vn

Dùng WITH CHECK OPTION

(cid:139) LOCAL/CASCADED apply to view hierarchies. (cid:139) 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.

(cid:139) Nếu dùng CASCADED (default): không cho các hàng

ra khỏi (biến khỏi) view.

45

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;

(cid:139) Không thể sửa B003 thành B002 vì khi đó các hàng sẽ

(cid:139) Không thể thêm các hàng có chi nhánh khác B003.

biến khỏi view.

46

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

khai.hq@ou.edu.vn

Ví dụ 7.6 - Dùng WITH CHECK OPTION

(cid:139) 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';

47

khai.hq@ou.edu.vn

Ví dụ 7.6 - WITH CHECK OPTION

UPDATE Manager3Staff SET salary = 9500 WHERE staffNo = 'SG37';

(cid:139) 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.

(cid:139) 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.

48

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

khai.hq@ou.edu.vn

Vi dụ 7.6 - WITH CHECK OPTION

(cid:139) 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.

(cid:139) Để tránh việc dị thường như vậy, các view nên dùng

WITH CASCADED CHECK OPTION.

49

khai.hq@ou.edu.vn

Ưu điểm của View

(cid:139) Độc lập dữ liệu (cid:139) Tiết kiệm chi phí (cid:139) Tăng tính an toàn (cid:139) Giảm độ phức tạp (cid:139) Tiện lợi (cid:139) Mềm dẻo (cid:139) Toàn vẹn dữ liệu

50

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

khai.hq@ou.edu.vn

Nhược điểm của View

(cid:139) Hạn chế trong cập nhật dữ liệu (cid:139) Giới hạn vế cấu trúc (cid:139) Chậm hơn

51

khai.hq@ou.edu.vn

Vật chất hóa view (View vật chất)

(cid:139) View phân giải chậm, nhất là khi truy xuất thường

(cid:139) View vật chất lưu view như một bảng tạm khi truy

xuyên.

(cid:139) Do đó, view vật chất chạy nhanh hơn. (cid:139) Nhưng: phải xem xét đến việc cập nhật view vật chất

xuất lần đầu tiên

(bảo trì) khi bảng nền của nó được cập nhật.

52

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

khai.hq@ou.edu.vn

Bảo trì view

(cid:139) Việc bảo trì view giúp cho view có thông tin hiện hành. (cid:139) Xét view sau:

CREATE VIEW StaffPropRent(staffNo) AS SELECT DISTINCT staffNo FROM PropertyForRent WHERE branchNo = 'B003‘ AND

rent > 400;

53

khai.hq@ou.edu.vn

Vật chất hóa view (View vật chất)

(cid:139) Nếu chèn hàng vào PropertyForRent có rent ≤400 thì view

không đổi

(cid:139) 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

(cid:139) 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.

(cid:139) Nếu xóa tài sản PG24, hàng dữ liệu này sẽ biến khỏi view (cid:139) 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)

54

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

khai.hq@ou.edu.vn

Giao tác (Transactions)

(cid:139) SQL định nghĩa mô hình giao tác dựa vào COMMIT

và ROLLBACK.

(cid:139) 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.

(cid:139) 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).

(cid:139) Các thay đổi do giao tác gây ra bị dấu đi cho đến khi

55

hoàn thành giao tác

khai.hq@ou.edu.vn

Giao tác (Transactions)

(cid:139) 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.

56

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

khai.hq@ou.edu.vn

Giao tác (Transactions)

(cid:139) Các giao tác SQL không được lồng nhau. (cid:139) Lệnh SET TRANSACTION cấu hình giao tác:

SET TRANSACTION

COMMITTED|REPEATABLE READ

[READ ONLY | READ WRITE] | [ISOLATION LEVEL READ UNCOMMITTED | READ |SERIALIZABLE ]

57

khai.hq@ou.edu.vn

Ràng buộc toàn vẹn kiểu trung gian và trì hoãn

(cid:139) 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) (cid:139) Các ràng buộc có thể được định nghĩa là INITIALLY IMMEDIATE hay INITIALLY DEFERRED lúc đầu giao tác.

(cid:139) Cũng có thể thay đổi lại kiểu ràng buộc dùng [NOT]

(cid:139) Kiểu mặc định là INITIALLY IMMEDIATE.

DEFERRABLE.

58

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

khai.hq@ou.edu.vn

Ràng buộc toàn vẹn kiểu trung gian và trì hoãn

(cid:139) Lệnh SET CONSTRAINTS cho giao tác hiện hành:

SET CONSTRAINTS

{ALL | constraintName [, . . . ]} {DEFERRED ¦ IMMEDIATE}

59

khai.hq@ou.edu.vn

Kiểm soát truy xuất – Danh hiệu về quyền và quyền sở hữu

(cid:139) 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.

(cid:139) Dùng để xác định quyền trong một số thao tác

trên một số đối tượng

(cid:139) 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.

60

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

khai.hq@ou.edu.vn

Đặc quyền (Privileges)

(cid:139) 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à

61

translations.

khai.hq@ou.edu.vn

Lệnh ban quyền GRANT

ObjectName {AuthorizationIdList | PUBLIC}

(cid:139) PrivilegeList gồm một hay nhiều quyền cách nhau bằng

GRANT {PrivilegeList | ALL PRIVILEGES} ON TO [WITH GRANT OPTION]

(cid:139) ALL PRIVILEGES ban tất cả quyền cho user

dấu phẩy

62

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

khai.hq@ou.edu.vn

Lệnh ban quyền GRANT

(cid:139) PUBLIC cho quyền truy xuất với tất cả user (hiện tại

(cid:139) ObjectName có thể là base table, view, domain,

và tương lai).

(cid:139) WITH GRANT OPTION cho phép thông qua quyền

character set, collation hay translation.

63

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;

64

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

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;

65

khai.hq@ou.edu.vn

Lệnh thu lấy quyền REVOKE

(cid:139) REVOKE lấy quyền đã được ban trước đó nhờ

GRANT.

REVOKE [GRANT OPTION FOR] {PrivilegeList | ALL PRIVILEGES} ON ObjectName FROM {AuthorizationIdList | PUBLIC}

(cid:139) ALL PRIVILEGES lấy lại tất cả quyền đã ban

[RESTRICT | CASCADE]

66

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

khai.hq@ou.edu.vn

Lệnh thu lấy quyền REVOKE

(cid:139) GRANT OPTION FOR cho phép các quyền được thông qua nhờ WITH GRANT OPTION của lệnh GRANT được lấy lại một cách riêng lẻ.

(cid:139) REVOKE không thực hiện được nếu trên đối tượng bị

(cid:139) Quyền do user này ban cho user khác không bị ảnh

cấm, như là các view trừ khi có dùng CASCADE

hưởng lẫn nhau

67

khai.hq@ou.edu.vn

Lệnh thu lấy quyền REVOKE

68

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

khai.hq@ou.edu.vn

Ví dụ 7.10/11 - Lệnh lấy quyền REVOKE

Lấy quyền SELECT trên bảng Branch của tất cả user:

REVOKE SELECT ON Branch FROM PUBLIC;

Lấy tất cả quyền đã cho Director trên bảng Staff :

REVOKE ALL PRIVILEGES ON Staff FROM Director;

HẾT CHƯƠNG 7

69

Chương 7: Ngôn ngữ SQL định nghĩa dữ liệu

Trang 35

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

Chương 8:

Chuẩn hóa cơ sở dữ liệu

Tham khảo tài liệu [A]: Chapter 13: Normalization

khai.hq@ou.edu.vn

Nội dung chương 8:

(cid:139) Mục đích của việc chuẩn hóa CSDL

(cid:139) Các lỗi liên quan đến việc dư thừa DL

(cid:139) Nhận dạng các loại cập nhật DL dị thường (update anomalies) như khi chèn DL, xóa DL, chỉnh sửa DL

(cid:139) Làm sao nhận định được tính thích hợp và chất

lượng của việc thiết kế các quan hệ

2

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 1

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Nội dung chương 8: (tt)

(cid:139) Cách dùng phụ thuộc hàm (functional

(cid:139) Cách thực hiện quá trình chuẩn hóa CSDL

dependencies) để nhóm các thuộc tính vào các quan hệ theo một dạng chuẩn định trước

(cid:139) Các nhận dạng một dạng chuẩn phổ biến (1NF,

(process of normalization)

(cid:139) Giới thiệu dạng dạng chuẩn 4 (4NF)

2NF, 3NF, và dạng chuẩn Boyce–Codd)

3

khai.hq@ou.edu.vn

Chuẩn hóa (Normalization)

(cid:139) Mục tiêu chính của việc phát triển mô hình mức

logic của CSDL quan hệ là tạo được nơi lưu chứa và cung cấp dữ liệu có tính chính xác, thể hiện đầy đủ các mối quan hệ và ràng buộc của dữ liệu (cid:139) Để đạt được mục tiêu trên, tập các quan hệ trong

CSDL phải có thiết kế thích hợp

4

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 2

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Chuẩn hóa (Normalization) (tt)

(cid:139) 4 dạng chuẩn thường dùng nhất là dạng chuẩn 1

(cid:139) Việc chuẩn hóa dựa vào các phụ thuộc hàm giữa các

(1NF), dạng chuẩn 2 (2NF), dạng chuẩn 3 (3NF), và dạng chuẩn Boyce–Codd (BCNF)

thuộc tính của quan hệ

(cid:139) Một quan hệ có thể được chuẩn hóa thành một dạng chuẩn mới để ngăn ngừa việc xảy ra các dị thường trong cập nhật dữ liệu

5

khai.hq@ou.edu.vn

Việc dư thừa dữ liệu (Data Redundancy)

(cid:139) Mục đích chính của việc thiết kế CSDL quan hệ là nhóm các thuộc tính vào các quan hệ nhằm giảm thiểu việc dư thừa dữ liệu

(cid:139) Vấn đề dư thừa dữ liệu được minh họa sau đây qua

quan hệ Staff, quan hệ Branch và quan hệ StaffBranch

6

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 3

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Việc dư thừa dữ liệu (Data Redundancy)

7

khai.hq@ou.edu.vn

Việc dư thừa dữ liệu (Data Redundancy)

(cid:139) Quan hệ StaffBranch có dư thừa dữ liệu: chi tiết về 1

(cid:139) Ngược lại, chi tiết về 1 branch chỉ lưu 1 lần trong

branch bị lặp lại trên các staff

quan hệ Branch và chỉ có thuộc tính branchNo được lưu lặp lại để cho biết nơi làm của staff

8

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 4

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dị thường khi cập nhật (Update Anomalies)

(cid:139) Các quan hệ có dư thừa dữ liệu dễ bị dị thường khi

(cid:139) Các loại dị thường khi cập nhật dữ liệu:

cập nhập dữ liệu

– Chèn (Insertion) – Xóa (Deletion) – Chỉnh sửa (Modification)

9

khai.hq@ou.edu.vn

Tính chất bảo toàn phụ thuộc hàm (Dependency Preservation) và ghép nối không mất thông tin (Lossless-join)

(cid:139) 2 tính chất quan trọng của phép phân rã:

- Tính chất ghép nối không mất thông tin (Lossless- join property): cho phép chúng ta tìm bất kỳ các thể hiện của quan hệ nguyên thủy từ các quan hệ tương ứng nhỏ hơn - Tính chất bảo toàn phụ thuộc hàm (Dependency preservation property): cho phép chúng ta áp dụng ràng buộc trên quan hệ nguyên thủy bằng cách áp dụng một số ràng buộc trên các quan hệ nhỏ hơn

10

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 5

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phụ thuộc hàm (Functional Dependency)

(cid:139) Phụ thuộc hàm là khái niệm liên quan mật thiết với

(cid:139) Phụ thuộc hàm

việc chuẩn hóa CSDL

– Miêu tả mối quan hệ giữa các thuộc tính trong một

quan hệ (ký hiệu R là tập các thuộc tímh)

– Nếu A và B là các thuộc tính của quan hệ R, B phụ thuộc hàm vào A, hay còn nói A xác định B (ký hiệu A (cid:198) B), nếu với mỗi giá trị của A trong R thì sẽ ứng với đúng một giá trị của B trong R

11

khai.hq@ou.edu.vn

Phụ thuộc hàm (Functional Dependency)

(cid:139) Phụ thuộc hàm thể hiện ý nghĩa của sự liên hệ giữa

các thuộc tính với nhau trong 1 quan hệ

(cid:139) Phần tử quyết định (determinant) của một phụ thuộc hàm là một hay một nhóm các thuộc tính nằm bên trái dấu mũi tên

12

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 6

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Ví dụ về phụ thuộc hàm

13

khai.hq@ou.edu.vn

Phụ thuộc hàm (Functional Dependency)

(cid:139) Đặc điểm của chính của phụ thuộc hàm dùng trong

việc chuẩn hóa là:

– Có mối liên hệ 1:1 giữa các thuộc tính bên trái và

bên phải của phụ thuộc hàm – Có mối liên hệ luôn luôn đúng – Không phải là mối liên hệ mặc nhiên đúng

14

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 7

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phụ thuộc hàm (Functional Dependency)

(cid:139) Tập hợp tất các các phụ thuộc hàm của 1 quan hệ có

(cid:139) Phải tìm cách giảm tập các phụ thuộc hàm để có thể

thể là rất lớn

(cid:139) Ta mong muốn xác định tập phụ thuộc hàm X nhỏ

tính toán được

hơn tập Y là tập chứa toàn bộ các phụ thuộc hàm của quan hệ, nhưng từ các phụ thuộc hàm trong X phải suy ra được tất cả các phụ thuộc hàm của Y

15

khai.hq@ou.edu.vn

Phụ thuộc hàm (Functional Dependency)

(cid:139) Tập chứa tất cả các phụ thuộc hàm sinh ra từ các phụ thuộc hàm của X gọi là bao đóng của tập X (closure) ký hiệu X+

(cid:139) Sử dụng các qui tắc suy diễn từ các tiên đề của

Armstrong (Armstrong’s axioms) để tìm ra các phụ thuộc hàm mới từ 1 số phụ thuộc hàm cho trước

16

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 8

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phụ thuộc hàm (Functional Dependency)

(cid:139) Với A, B, và C là tập con của tập các thuộc tính của quan hệ R, 3 tiên đề của Armstrong phát biểu như sau: 1. Tính phản xạ (Reflexivity)

Nếu B là tập con của A, thì A → B 2. Tính tăng trưởng (Augmentation) Nếu A → B, thì A,C → Β,C

3. Tính bắc cầu (Transitivity)

Nếu A → B và B → C, thì A → C

17

khai.hq@ou.edu.vn

Tiến trình chuẩn hóa

(cid:139) Dựa vào khóa chính và các phụ thuộc hàm

(cid:139) Gồm một loạt các bước, mỗi bước tương ứng với

(cid:139) Sau khi chuẩn hóa, các quan hệ sẽ bị ràng buộc

một dạng chuẩn

nhiều hơn (tốt hơn) và ít bị dị thường khi cập nhật hơn

18

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 9

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Mối quan hệ giữa các dạng chuẩn

19

khai.hq@ou.edu.vn

Dạng chưa chuẩn hóa Unnormalized Form (UNF)

(cid:139) Một bảng có chứa một hay nhiều nhóm lặp

(cid:139) Muốn tạo một bảng chưa chuẩn hóa:

– Nhập thông tin từ 1 biểu mẫu nào đó vào 1 bảng

20

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 10

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dạng chuẩn 1 (1NF)

(cid:139) Một ô dữ liệu phải chứa một giá trị đơn trị

21

khai.hq@ou.edu.vn

Chuẩn hóa UNF thành 1NF

(cid:139) Chọn ra 1 hay 1 nhóm thuộc tính đóng vai trò là

(cid:139) Xác định các nhóm lặp gây ra việc lặp các thuộc

khóa của bảng

(cid:139) Loại bỏ các nhóm lặp bằng cách:

tính khóa

– Nhập dữ liệu vào các ô lặp lại còn trống hoặc bằng cách – Tạo 1 quan hệ mới chứa các thuộc tính lặp và

các thuộc tính khóa

22

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 11

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dạng chuẩn 2 (2NF)

(cid:139) Dựa vào khái niệm phụ thuộc hàm đầy đủ (full

functional dependency): – A và B là các thuộc tính của 1 quan hệ – B phụ thuộc hàm đầy đủ vào A nếu: B phụ thuộc hàm vào A nhưng không phụ thuộc hàm vào bất kỳ tập con thật sự nào của A

(cid:139) 2NF – Là quan hệ ở dạng chuẩn 1 và tất cả các thuộc tính không phải là khóa phải phụ thuộc hàm đầy đủ vào khóa

23

khai.hq@ou.edu.vn

Chuẩn hóa 1NF thành 2NF

(cid:139) Xác định khóa của quan hệ đang ở 1NF

(cid:139) Xác định các phụ thuộc hàm của quan hệ

(cid:139) Nếu có phụ thuộc hàm A(cid:198) B với A là con thật sự của khóa, thì ta loại thuộc tính B khỏi quan hệ đang xét và tạo 1 quan hệ mới chứa A,B

24

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 12

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dạng chuẩn 3 (3NF)

(cid:139) Dựa vào khái niệm phụ thuộc hàm bắc cầu (transitive

dependency): – A, B và C là các thuộc tính của 1 quan hệ có phụ

thuộc hàm A (cid:198) B và B (cid:198) C,

– Ta nói C phụ thuộc hàm bắc cầu vào A qua B

(cid:139) 3NF - Là quan hệ ở dạng chuẩn 2 và: không có thuộc tính không phải là khóa nào phụ thuộc hàm bắc cầu vào khóa

25

khai.hq@ou.edu.vn

Chuẩn hóa 2NF thành 3NF

(cid:139) Xác định khóa của quan hệ đang ở 2NF

(cid:139) Xác định các phụ thuộc hàm của quan hệ

(cid:139) Tách B, C thành 1 quan hệ mới và loại bỏ C khỏi

quan hệ đang xét

26

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 13

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dạng chuẩn Boyce–Codd (BCNF)

(cid:139) Là dạng chuẩn có tính đến tất cả các khóa ứng viên

nên có nhiều ràng buộc hơn dạng chuẩn 3

(cid:139) BCNF - Một quan hệ ở dạng chuẩn BCNF nếu và chỉ nếu mọi phần tử quyết định của các phụ thuộc hàm (nằm bên trái mũi tên) là các khóa ứng viên

27

khai.hq@ou.edu.vn

So sánh 3NF và BCNF

(cid:139) Xét phụ thuộc hàm A → B, 3NF cho phép B là 1

(cid:139) BCNF quy định A phải là khóa ứng viên

(cid:139) Nếu là BCNF thì là 3NF

(cid:139) Nếu là 3NF thì chưa chắc là BCNF

thuộc tính của khóa và A không là 1 khóa ứng viên

28

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 14

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dạng chuẩn BCNF

(cid:139) Trường hợp có thể gây ra sự vi phạm dạng chuẩn

BCNF trong 1 quan hệ: – Có chức các khóa ứng viên có 2 hay nhiều thuộc

tính

– Các khóa ứng viên có phần giao nhau (tức có

thuộc tính chung)

29

khai.hq@ou.edu.vn

Ví dụ chuẩn hóa từ UNF thành BCNF

30

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 15

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Phụ thuộc đa trị

(cid:139) Mặc dầu dạng chuẩn BCNF loại bỏ được sự dị thường nhờ vào xét các phụ thuộc hàm, có 1 loại phụ thuộc khác có thể gây ra việc dư thừa dữ liệu gọi là phụ thuộc đa trị (multi-valued dependency (MVD))

(cid:139) MVD: xét A, B, C là các thuộc tính của 1 quan hệ, mỗi giá trị của A xác định 1 tập giá trị của B và 1 tập giá trị của C, nhưng tập giá trị của B và của C không phụ thuộc lẫn nhau. Ký hiệu:

A (cid:190)(cid:190) B A (cid:190)(cid:190) C

31

khai.hq@ou.edu.vn

Phụ thuộc đa trị (tt)

(cid:139) Xét A (cid:190)(cid:190) B (cid:139) Là phụ thuộc đa trị mặc nhiên nếu:

(cid:139) Là phụ thuộc đa trị không mặc nhiên nếu không

– B là tập con của A, hay – A ∪ B = R

phải là phụ thuộc đa trị mặc nhiên

(cid:139) Phụ thuộc đa trị không mặc nhiên có tạo ràng buộc cho quan hệ, còn phụ thuộc đa trị mặc nhiên không có tạo ràng buộc cho quan hệ

32

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 16

Nhập môn cơ sở dữ liệu

www.ou.edu.vn

khai.hq@ou.edu.vn

Dạng chuẩn 4 (4NF)

(cid:139) Là quan hệ ở dạng chuẩn BCNF và không chứa các

phụ thuộc đa trị không mặc nhiên

33

khai.hq@ou.edu.vn

Ví dụ 4NF

HẾT CHƯƠNG 8

34

Chương 8: Chuẩn hóa cơ sở dữ liệu

Trang 17