Bài giảng Cơ sở dữ liệu - Chương 4: Đại số quan hệ - Nguyễn Thị Như Anh

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 4: Đại số quan hệ - Nguyễn Thị Như Anh

1. Giới thiệu ▪ Xét một số xử lý trên quan hệ KHOA - Thêm Khoa ‘Hóa học’ vào quan hệ - Chuyển Khoa CNTT sang phòng B12 MÃKHOA TÊNKHOA NĂMTL PHÒNG ĐIỆNTHOAI TRƯỞNGKHOA NGÀYNHẬNCHỨC CNTT Công nghệ thông tin 1995 B12B11 0838123456 002 20/02/2005 VL Vật lý 1976 B21 0838223223 005 18/09/2003 SH Sinh học 1980 B31 0838454545 004 11/10/2000 HH Hóa học 1980 B41 NULL 007 15/10/2001 - Cho biết tên các khoa được thành lập từ năm 1980 TÊNKHOA Sinh học Hóa học 3 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN Giới thiệu (tt) ▪ Có 2 loại xử lý: - Làm thay đổi dữ liệu (cập nhật): thêm mới, xóa và sửa - Không làm thay đổi dữ liệu (rút trích): truy vấn ▪ Ngôn ngữ truy vấn (Query Language – QL): - Cho phép người dùng rút trích hay cập nhật dữ liệu được lưu trong một mô hình dữ liệu ▪ Ngôn ngữ truy vấn quan hệ: - Đại số quan hệ (Relational Algebra) ⚫ Biểu diễn câu truy vấn dưới dạng biểu thức, cho phép người dùng biểu diễn các bước thực hiện câu truy vấn - Phép tính quan hệ (Relational Calculus) ⚫ Biểu diễn kết quả phi thủ tục dựa trên ngôn ngữ logic, cho phép người dùng diễn đạt cái họ cần hơn là thao tác xử lý nó - SQL (Structured Query Language) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 4 2
2. Đại số quan hệ (tt) ▪ Khi dùng đại số quan hệ, một nhu cầu truy vấn được thể hiện qua một biểu thức đại số quan hệ: - Kết quả trả về là một thể hiện của quan hệ - Có thể là một chuỗi các phép toán đại số quan hệ - Theo thứ tự thực hiện của các toán tử, kết quả của toán tử thực hiện trước là đầu vào cho toán tử thực hiện kế tiếp. © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 7 Đại số quan hệ (tt) Phép toán đại số Phép toán đại số quan hệ Toán hạng - Biến : x, y, z, - Quan hệ : NhanVien, - Hằng số : 150, - Thể hiện của quan hệ : t, v, Toán tử - Thao tác xử lý giữa các toán - Thao tác xử lý giữa các quan hệ hạng để tạo thành giá trị mới : +, -, để tạo thành quan hệ mới : phép *, /, chọn , hội  , Biểu thức - Chuỗi các phép toán đại số - Chuỗi các phép toán đại số quan - Kết quả cho ra một giá trị mới hệ (câu truy vấn) - Kết quả cho ra một quan hệ mới (x+7) / (y-3) (x+y)*z and/or (x+7) / (y-3) MANV (NHANVIEN) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 8 4
3. Phép toán tập hợp (tt) ▪ Ví dụ hai quan hệ khả hợp SINHVIEN TENSV NGSINH PHAI GIAOVIEN TENGV NG_SINH GIOITINH Tung 12/08/1955 Nam Trinh 04/05/1986 Nu Hang 07/19/1968 Nu Khang 10/25/1983 Nam Nhu 06/20/1951 Nu Phuong 05/03/1958 Nu Hung 09/15/1962 Nam Minh 02/28/1942 Nam Chau 12/30/1988 Nu Bậc n=3 DOM(TENSV) = DOM(TENGV) DOM(NGSINH) = DOM(NG_SINH) DOM(PHAI) = DOM(GIOITINH) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 11 Phép hội ▪ Cho 2 quan hệ r và s khả hợp ▪ Phép hội của r và s - Ký hiệu r  s - Là một quan hệ gồm các bộ thuộc r hoặc thuộc s, hoặc cả hai (các bộ trùng lắp sẽ bị loại bỏ) r  s = { t / t r  t s } ▪ Ví dụ r A B s A B r  s A B 1 2 1 2  3 2  1  1 2  3 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 12 6
4. Phép giao (tt) SinhVien GiaoVien HOTEN DIACHI HOTEN DIACHI Đinh Bá Tiến 119 Cống Quỳnh, Tp HCM Đinh Bá Tiến 119 Cống Quỳnh, Tp HCM Nguyễn Thanh Tùng 222 Nguyễn Văn Cừ, Tp HCM Trần Thanh Tâm 553 Mai Thị Lựu, Tp HCM Lê Quỳnh Như 291 Hồ Văn Huê, Tp HCM SinhVien  GiaoVien HOTEN DIACHI Đinh Bá Tiến 119 Cống Quỳnh, Tp HCM © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 15 Phép trừ ▪ Cho 2 quan hệ r và s khả hợp ▪ Phép giao của r và s - Ký hiệu r − s - Là một quan hệ gồm các bộ thuộc R và không thuộc S r − s = { t / t r  t s } ▪ Ví dụ r A B s A B r − s A B 1 2 1 2  3  1  1 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 16 8
5. Nội dung chi tiết ▪ Giới thiệu ▪ Đại số quan hệ ▪ Phép toán tập hợp ▪ Phép chọn ▪ Phép chiếu ▪ Phép tích Cartesian ▪ Phép kết ▪ Phép chia ▪ Các phép toán khác ▪ Các thao tác cập nhật trên quan hệ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 19 Phép chọn ▪ Được dùng để lấy ra các bộ của quan hệ r thỏa mãn điều kiện chọn P ▪ Ký hiệu  P (r) ▪ P là biểu thức gồm các mệnh đề có dạng - - ⚫ gồm , , , , , = ⚫ Các mệnh đề được nối lại nhờ các phép  ,  ,  © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 20 10
6. Nội dung chi tiết ▪ Giới thiệu ▪ Đại số quan hệ ▪ Phép toán tập hợp ▪ Phép chọn ▪ Phép chiếu ▪ Phép tích Cartesian ▪ Phép kết ▪ Phép chia ▪ Các phép toán khác ▪ Các thao tác cập nhật trên quan hệ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 23 Phép chiếu ▪ Được dùng để lấy ra một vài cột của quan hệ r ▪ Ký hiệu A1, A2, , Ak(r) ▪ Kết quả trả về là một quan hệ - Có k thuộc tính - Có số bộ luôn ít hơn hoặc bằng số bộ của r ▪ Ví dụ A,C (r) r A B C A,C (r) A C 10 1 1 20 1  1  30 1  2  40 2 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 24 12
7. Ví dụ 1 ▪ Cho biết họ tên và mức lương của các giáo viên nữ HOTEN, LUONG (PHAI=‘Nữ’ (GIAOVIEN)) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 27 Ví dụ 2 ▪ Cho biết mã số các giáo viên thuộc bộ môn HTTT hoặc có tham gia đề tài mã 001 MAGV (MABM=‘HTTT’ (GIAOVIEN))  MAGV (MAĐT=‘001’ (TG_ĐETAI)) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 28 14
8. Phép chiếu tổng quát ▪ Mở rộng phép chiếu bằng cách cho phép sử dụng các phép toán số học trong danh sách thuộc tính ▪ Ký hiệu F1, F2, , Fn (E) - E là biểu thức ĐSQH - F1, F2, , Fn là các biểu thức số học liên quan đến ⚫ Hằng số ⚫ Thuộc tính trong E © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 31 Phép chiếu tổng quát ▪ Ví dụ - Cho biết họ tên của các giáo viên và lương của họ sau khi tăng 10% HOTEN, LUONG*1.1 (GIAOVIEN) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 32 16
9. Phép đổi tên ▪ Được dùng để đổi tên - Quan hệ Xét quan hệ r(B, C, D) s(r) : Đổi tên quan hệ r thành s - Thuộc tính X, C, D (r) : Đổi tên thuộc tính B thành X - Quan hệ lẫn thuộc tính s(X,C,D)(r) : đổi tên quan hệ r thành s và thuộc tính B thành X © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 35 Ví dụ 5 ▪ Cho biết mã số và họ tên giáo viên thuộc bộ môn HTTT ▪ C1: MAGV, HOTEN (MABM=‘HTTT’ (GIAOVIEN)) ▪ C2: GV_HTTT  MABM=‘HTTT’ (GIAOVIEN) KQ  MAGV, HOTEN (GV_HTTT) KQ(MA, TEN)  MAGV, HOTEN (GV_HTTT) KQ(MA, TEN) ( MAGV, HOTEN (GV_HTTT)) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 36 18
10. Phép tích Cartesian (tt) Ví dụ ▪ unambiguous r A B r s A R.B S.BX C D 1  2 1 10 + 1  10 + 1  20 - S BX C D 1  10 -  2 10 + 10 +  2  10 +  10 +  2  20 -  20 -  2  10 -  10 - (X,C,D) (s) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 39 Phép tích Cartesian (tt) ▪ Thông thường theo sau phép tích Cartesian là phép chọn r s A=S.B (r s) A R.B S.B C D A R.B S.B C D 1 10 + 1 10 + 1  10 +  2  10 + 1  20 -  2  20 - 1  10 -  2 10 +  2  10 +  2  20 -  2  10 - © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 40 20
11. Ví dụ 6 (tt) ▪ B1: Tích Cartesian BOMON và GIAOVIEN BM_GV  (GIÁOVIÊN BỘMÔN) ▪ B2: Chọn ra những bộ thỏa TRUONGBM = MAGV KQ  TRUONGBM=MAGV(BM_GV) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 43 GIÁOVIÊN MÃGV HỌTÊN . NGÀYSINH SỐNHÀ . 001 Nguyễn Hoài An . 15/02/1973 25/3 . 002 Trần Trà Hương . 20/06/1960 125 . 003 Nguyễn Ngọc Ánh . 11/05/1975 12/21 . . . . . . . BỘMÔN MÃBM TÊNBM PHÒNG . TRƯỞNGBM . HTTT Hệ thống thông tin B13 . 002 . CNTT Công nghệ tri thức B15 . . MMT Mạng máy tính B16 . 001 . . . . . . . TRUONGBM=MAGVGIÁOVIÊN x BỘMÔN(BM_GV) MÃGV HỌTÊN . NGÀYSINH . MÃBM TÊNBM PHÒNG . TRƯỞNGBM . 001 Nguyễn Hoài An . 15/02/1973 . HTTT Hệ thống thông tin B13 . 002 . 001 Nguyễn Hoài An . 15/02/1973 . CNTT Công nghệ tri thức B15 . . 001 Nguyễn Hoài An . 15/02/1973 . MMT Mạng máy tính B16 . 001 . 44 . . . . . . . . . . . © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 22
12. Ví dụ 8 ▪ Cho biết họ tên các giáo viên cùng bộ môn với giáo viên ‘Trần Trà Hương’ - Quan hệ: GIAOVIEN - Thuộc tính: HOTEN, MABM - Điều kiện: HOTEN = ‘Trần Trà Hương’ Giáo viên “Trần Trà Hương” ở bộ môn nào? Những giáo viên nào thuộc về bộ môn đó? MABM HOTEN MABM HOTEN MMT Nguyễn Hoài An MMT Nguyễn Hoài An HTTT Trần Trà Hương HTTT Trần Trà Hương HTTT Nguyễn Ngọc Anh HTTT Nguyễn Ngọc Anh VS Trương Nam Sơn VS Trương Nam Sơn © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 47 Ví dụ 8 (tt) ▪ B1: Tìm bộ môn mà giáo viên ‘Trần Trà Hương’ thuộc về r1  MABM,MAGV (HOTEN=‘Trần Trà Hương’ (GIAOVIEN)) ▪ B2: Lấy ra họ tên các giáo viên cùng bộ môn r2  HOTEN <>’Trần Trà Hương’ (GIAOVIEN) r3  R1.MABM=R2.MABM (r1 r2) KQ  HOTEN (r3) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 48 24
13. Phép kết (tt) ▪ Phân loại - Kết theta (theta join) là phép kết có điều kiện ⚫ Ký hiệu r C s ⚫ C gọi là điều kiện kết trên thuộc tính - Kết bằng (equi join) khi C là điều kiện so sánh bằng - Kết tự nhiên (natural join): khi hai thuộc tính trong điều kiện kết bằng C có tên giống nhau. ⚫ Ký hiệu r s hay r s ⚫ R+  S+  ⚫ Quan hệ kết quả của phép kết bằng có tập thuộc tính là hội tập thuộc tính của r và s bỏ bớt đi 1 thuộc tính giống nhau © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 52 Phép kết (tt) ▪ Ví dụ phép kết theta r B<D s r A B C s D E A B C D E 1 2 3 3 1 1 2 3 3 1 4 5 6 6 2 1 2 3 6 2 7 8 9 4 5 6 6 2 r C s = C(r s) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 53 26
14. Ví dụ 10 ▪ Cho biết giáo viên có lương lớn hơn lương của giáo viên ‘Nguyễn Hoài An’ - Quan hệ: GIAOVIEN - Thuộc tính: LUONG GIAOVIEN(MAGV, HOTEN, LUONG, PHAI, NGAYSINH, ) R1(LG)  LUONG (HOTEN=‘Nguyễn Hoài An’ (GIAOVIEN)) KQ  GIAOVIEN LUONG>LG R1 KQ(MAGV, HOTEN, LUONG, PHAI, NGAYSINH, , LG)) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 56 Ví dụ 11 ▪ Với mỗi giáo viên, hãy cho biết thông tin của bộ môn mà họ đang làm việc - Quan hệ: GIAOVIEN, BOMON GIAOVIEN(MAGV, HOTEN, LUONG, PHAI, , MABM, ) BOMON(MABM, TENBM, PHONG, DIENTHOAI, ) KQ  GIAOVIEN BOMON KQ(MAGV, HOTEN, , MABM, TENBM, PHONG, )) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 57 28
15. Ví dụ 14 ▪ Cho biết lương cao nhất trong bộ môn ‘HTTT’ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 60 Ví dụ 15 ▪ Cho biết giáo viên làm việc cùng bộ môn với giáo viên 002 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 61 30
16. Nội dung chi tiết ▪ Giới thiệu ▪ Đại số quan hệ ▪ Phép toán tập hợp ▪ Phép chọn ▪ Phép chiếu ▪ Phép tích Cartesian ▪ Phép kết ▪ Phép chia ▪ Các phép toán khác ▪ Các thao tác cập nhật trên quan hệ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 64 Phép chia ▪ Được dùng để lấy ra một số bộ trong quan hệ r sao cho thỏa với tất cả các bộ trong quan hệ s ▪ Ký hiệu r  s - r(Z) và s(X) ⚫ Z là tập thuộc tính của r, X là tập thuộc tính của s ⚫ X  Z ▪ Kết quả của phép chia là một quan hệ t(Y) - Với Y=Z-X - Có t0 là một bộ của t nếu với mọi bộ tS S, tồn tại bộ tR R thỏa 2 điều kiện ⚫ tR(Y) = t0 R(Z) S(X) T(Y) ⚫ tR(X) = tS(X) X Y © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 65 32
17. Ví dụ 18 ▪ Cho biết tên đề tài có tất cả giảng viên bộ môn ‘Hệ thống thông tin’ tham gia © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 68 Nội dung chi tiết ▪ Giới thiệu ▪ Đại số quan hệ ▪ Phép toán tập hợp ▪ Phép chọn ▪ Phép chiếu ▪ Phép tích Cartesian ▪ Phép kết ▪ Phép chia ▪ Các phép toán khác - Hàm kết hợp (Aggregation function) - Phép gom nhóm (Grouping) - Phép kết ngoài (Outer join) ▪ Các thao tác cập nhật trên quan hệ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 70 34
18. Phép gom nhóm ▪ Được dùng để phân chia quan hệ thành nhiều nhóm dựa trên điều kiện gom nhóm nào đó ▪ Ký hiệu G1, G2, , GnF1(A1), F2(A2), , Fn(An)(E) - E là biểu thức ĐSQH - G1, G2, , Gn là các thuộc tính gom nhóm - F1, F2, , Fn là các hàm kết hợp - A1, A2, , An là các thuộc tính tính toán trong hàm F © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 73 Phép gom nhóm (tt) ▪ Ví dụ SUM(C)(r) SUM_C r A B C 27 2 7 4 7  2 3  2 10 ASUM(C)(r) A SUM_C 14  3  10 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 74 36
19. Ví dụ 21 ▪ Cho biết tên khoa có đông giáo viên nhất © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 77 Ví dụ 22 ▪ Cho biết họ tên giáo viên chủ nhiệm nhiều đề tài nhất © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 78 38
20. Ví dụ 24 - a ▪ Cho biết họ tên giáo viên và tên bộ môn họ làm trưởng bộ môn nếu có R1  GIAOVIEN MAGV=TRUONGBM BOMON KQ  HOTEN, TENBM (R1) HOTEN TENBM Nguyễn Hoài An Mạng máy tính Trần Trà Hương Hệ thống thông tin Nguyễn Ngọc Ánh null © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 81 Ví dụ 24 - b ▪ Cho danh sách tên bộ môn và họ tên trưởng bộ môn đó nếu có. © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 82 40
21. Các thao tác cập nhật ▪ Nội dung của CSDL có thể được cập nhật bằng các thao tác - Thêm (insertion) - Xóa (deletion) - Sửa (updating) ▪ Các thao tác cập nhật được diễn đạt thông qua phép toán gán rnew  các phép toán trên rold © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 85 Thao tác thêm ▪ Được diễn đạt rnew  rold  E - r là quan hệ - E là một biểu thức ĐSQH ▪ Ví dụ - Phân công giáo viên có mã 001 tham gia công việc 4 của đề tài số 001 với mức phụ cấp 2 THAMGIAĐT  THAMGIAĐT  (‘001’, ‘001’, 4, 2) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 86 42
22. Thao tác sửa ▪ Được diễn đạt rnew  F1, F2, , Fn (rold) - r là quan hệ - Fi là biểu thức tính toán cho ra giá trị mới của thuộc tính ▪ Ví dụ - Tăng mức phụ cấp cho các đề tài của tất cả giáo viên lên 1.5 lần THAMGIAĐT  MAGV, MAĐT, STT, PHUCAP*1.5(THAMGIAĐT) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 89 44