›
›

Bài giảng Cơ sở dữ liệu quan hệ - Chương IV: Đại số quan hệ

pdf 60 trang hapham 3550
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Cơ sở dữ liệu quan hệ - Chương IV: Đại số quan hệ", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

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

  • pdfbai_giang_co_so_du_lieu_quan_he_chuong_iv_dai_so_quan_he.pdf

Nội dung text: Bài giảng Cơ sở dữ liệu quan hệ - Chương IV: Đại số quan hệ

  1. CHƯƠNG 4 ĐẠI SỐ QUAN HỆ Cơ sở dữ liệu 10/10/2012 1
  2. Nội dung chi tiết Các phép toán cập nhật: thêm, sửa, xóa Các phép toán đại số trên tập hợp Phép hợp Phép giao Phép trừ Tích đe cac Các phép toán đại số trên quan hệ Phép chiếu Phép chọn Phép chia Phép kết, phép kết tự nhiên CácCơ sởphép dữ liệu toán quan hệ bổ sung 10/10/2012 2
  3. I. Các phép toán trong mô hình quan hệ Xét một số xử lý trên quan hệ NHÂN VIÊN Thêm mới một nhân viên Chuyển nhân viên tên Tùng sang phòng số 1 Cho biết Họ tên, ngày sinh của các nhân viên có lương thấp hơn 30000. TENNV HONV NS DIACHI GT LUONG PHG Tung Nguyen 12/08/1955 638 NVC Q5 Nam 40000 5 Hang Bui 07/19/1968 332 NTH Q1 Nu 25000 4 Nhu Le 06/20/1951 291 HVH QPN Nu 43000 4 Hung Nguyen 09/15/1962 Ba Ria VT Nam 38000 5 Cơ sở dữ liệu 10/10/2012 3
  4. Có 2 loại xử lý: Làm thay đổi cơ sở dữ liệu (cập nhật) Thêm, sửa, xóa Không làm thay đổi cơ sở dữ liệu (rút trích) Thực hiện các xử lý Đại số quan hệ: biểu diễn câu truy vấn dưới dạng biểu thức SQL (Structured Query Language) Cơ sở dữ liệu 10/10/2012 4
  5. I. Các phép toán cập nhật Thêm (Insert): cho phép thêm một bộ vào quan hệ. Phép thêm có thể vi phạm ràng buộc dữ liệu => dữ liệu sẽ không được thêm vào. Xóa: (Delete): cho phép xóa một hay nhiều bộ giá trị của quan hệ. Phép xóa cũng có thể vi phạm ràng buộc Sửa (Update): cho phép thay đổi giá trị của một hay nhiều thuộc tính trong một bộ của quan hệ Sửa thuộc tính khóa chính, khóa ngoại Sửa thuộc tính thông thường: kiểu dữ liệu, miền giá trị, Cơ sở dữ liệu 10/10/2012 5
  6. II. Các phép toán đại số trên tập hợp Kết quả của phép toán là một quan hệ. Điều kiện: hai quan hệ r(A1, ,An) và s(B1, ,Bn) được gọi là khả hợp (tương thích) khi chúng cùng xác định trên một tập thuộc tính nào đó. Cùng bậc n (có cùng số cột) Miền giá trị dom (Ai) = dom (Bi) Cơ sở dữ liệu 10/10/2012 6
  7. a. Phép hợp – Union Cho hai lược đồ quan hệ Q1 và Q2 có cùng tập thuộc tính {A1, A2, , An}. r1 và r2 lần lượt là hai quan hệ trên Q1 và Q2. Phép hợp của hai lược đồ quan hệ Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3. Với Q3 được xác định như sau: + Q3 = {A1, A2, , An} r3 = r1  r2 = { t | t r1 hoặc t r2}  là tập hợp các bộ thuộc r1 hoặc r2 hoặc thuộc cả hai Cơ sở dữ liệu 10/10/2012 7
  8. Ví dụ: r A B C s A B C r  s A B C a b c a1 b1 c1 a1 b1 c1 1 1 1 a2 b1 c3 a2 b2 c2 a2 b1 c3 a3 b3 c3 a2 b2 c2 a3 b3 c3 Cơ sở dữ liệu 10/10/2012 8
  9. r1 MASV MAMH DIEM r = r  r 99001 CSDL 5.0 3 1 2 MASV MAMH DIEM 99002 CTDL 2.0 99001 CSDL 5.0 99003 MANG 8.0 99002 CTDL 2.0 r2 99003 MANG 8.0 MASV MAMH DIEM 99001 TTNT 5.0 99002 CTDL 2.0 99003 CSDL 6.0 99001 TTNT 5.0 99003 CSDL 6.0 Cơ sở dữ liệu 10/10/2012 9
  10. VD2: Cho danh sách Học viên được khen thưởng ít nhất 1 lần DOT2 DOT1 Mahv Hoten Mahv Hoten Mahv Hoten K1101 Le Kieu My K1101 Le Kieu My K1103 Le Van Tam K1103 Le Van Tam K1114 Tran Ngoc Han K1114 Tran Ngoc Han K1114 Tran Ngoc Han K1203 Le Thanh Hau K1203 Le Thanh Hau K1203 Le Thanh Hau K1308 Nguyen Gia K1308 Nguyen Gia K1308 Nguyen Gia DOT1DOT2 Cơ sở dữ liệu 10/10/2012 10
  11. b. Phép giao - Intersection Cho hai lược đồ quan hệ Q1 và Q2 có cùng tập thuộc tính {A1, A2, , An}. r1 và r2 lần lượt là hai quan hệ trên Q1 và Q2. Phép giao của hai lược đồ quan hệ Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3. Với Q3 được xác định như sau: + Q3 = {A1, A2, , An} r3 = r1  r2 = { t | t r1 và t r2}  là tập hợp các bộ thuộc cả trên hai quan hệ r1 và r2 Cơ sở dữ liệu 10/10/2012 11
  12. VD3: r A B C s A B C r  s A B C a1 b1 c1 a1 b1 c1 a1 b1 c1 a b c 2 2 2 a2 b1 c3 a3 b3 c3 Cơ sở dữ liệu 10/10/2012 12
  13. * Ví dụ: r1 MASV MAMH DIEM 99001 CSDL 5.0 99002 CTDL 2.0 r3 = r1  r2 99003 MANG 8.0 MASV MAMH DIEM r2 99002 CTDL 2.0 MASV MAMH DIEM 99002 CTDL 2.0 99001 TTNT 5.0 99003 CSDL 6.0 Cơ sở dữ liệu 10/10/2012 13
  14. c. Phép trừ - Minus, difference Cho hai lược đồ quan hệ Q1 và Q2 có cùng tập thuộc tính {A1, A2, , An}. r1 và r2 lần lượt là hai quan hệ trên Q1 và Q2. Phép trừ của hai lược đồ quan hệ Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3. Với Q3 được xác định như sau: + Q3 = {A1, A2, , An} r3 = r1 - r2 = { t | t r1 và t r2}  là tập hợp các bộ thuộc r1 nhưng không thuộc r2 Cơ sở dữ liệu 10/10/2012 14
  15. VD4: r A B C s A B C r - s A B C a1 b1 c1 a1 b1 c1 a2 b2 c2 a b c a b c 2 2 2 a2 b1 c3 3 3 3 a3 b3 c3 Cơ sở dữ liệu 10/10/2012 15
  16. * VD5: r1 MASV MAMH DIEM r = r - r 99001 CSDL 5.0 3 1 2 MASV MAMH DIEM 99002 CTDL 2.0 99001 CSDL 5.0 99003 MANG 8.0 99003 MANG 8.0 r2 MASV MAMH DIEM 99002 CTDL 2.0 99001 TTNT 5.0 99003 CSDL 6.0 Cơ sở dữ liệu 10/10/2012 16
  17. VD6: Học viên được khen thưởng đợt 1 nhưng không được khen thường đợt 2 DOT1 DOT2 Mahv Hoten Mahv Hoten Mahv Hoten K1103 Le Van Tam K1103 Le Van Tam K1101 Le Kieu My K1203 Le Thanh Hau K1114 Tran Ngoc Han K1114 Tran Ngoc Han K1308 Nguyen Gia K1203 Le Thanh Hau K1308 Nguyen Gia DOT1- DOT2 Cơ sở dữ liệu 10/10/2012 17
  18. Các tính chất Giao hoán r  s = s  r r  s = s  r Kết hợp r  (s  p) = (r  s)  p r  (s  p) = (r  s)  p 18 10/10/20 Cơ sở dữ liệu 12
  19. d. Tích decac – Cartesian Product Cho hai lược đồ quan hệ Q1(A1,A2, ,An) và Q2(B1,B2, ,Bm) . r1 và r2 lần lượt là hai quan hệ trên Q1 và Q2. Tích decac của hai lược đồ quan hệ Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3. Với Q3 được xác định như sau: + + + , } Q3 = Q1  Q2 = {A1, A2, , An B1,B2 Bm r3 = r1 x r2 = { (t1,t2) | t1 r1 và t2 r2}  Là tập các bộ được ghép lần lượt từ hai lược đồ Q1 và Q2. Các thành phần của Q1 trước rồi đến Q2. Cơ sở dữ liệu 10/10/2012 19
  20. VD7: r A B s C D r s A B C D a1 1 1 d1 a1 1 1 d1 a 1 3 a2 2 3 d2 1 d2 a2 2 1 d1 a2 2 3 d2 Cơ sở dữ liệu 10/10/2012 20
  21. * VD8: r2 r1 MAMH TENMH MASV MAMH DIEM CSDL Cơ sở dữ liệu 99001 CSDL 5.0 CTDL Cấu trúc dữ liệu 99002 CTDL 2.0 r3 = r1 x r2 MASV MAMH DIEM MAMH TENMH 99001 CSDL 5.0 CSDL Cơ sở dữ liệu 99001 CSDL 5.0 CTDL Cấu trúc dữ liệu 99001 CTDL 2.0 CSDL Cơ sở dữ liệu 99002 CTDL 2.0 CTDL Cấu trúc dữ liệu Cơ sở dữ liệu 10/10/2012 21
  22. VD9: HOCVIEN MONHOC Mahv Hoten Mamh Mahv Hoten Mamh K1103 Le Van Tam CTRR K1103 Le Van Tam CTRR K1114 Tran Ngoc Han CTRR K1114 Tran Ngoc Han THDC K1203 Le Thanh Hau CTRR K1203 Le Thanh Hau CTDL K1103 Le Van Tam THDC K1114 Tran Ngoc Han THDC K1203 Le Thanh Hau THDC K1103 Le Van Tam CTDL K1114 Tran Ngoc Han CTDL K1203 Le Thanh Hau CTDL HOCVIEN MONHOC Cơ sở dữ liệu 10/10/2012 22
  23. III. Các phép toán đại số quan hệ Cơ sở dữ liệu 10/10/2012 23
  24. a. Phép chiếu - Projection Cho một lược đồ quan hệ Q(A1,A2, ,An). r là quan hệ trên Q, X là tập thuộc tính với XQ+. Phép chiếu của quan hệ Q lên tập thuộc tính X là lấy ra những thuộc tính được liệt kê trong danh sách thuộc tính X của quan hệ đó. Ký hiệu X (R) Hoặc R.{x} Trong đó: X: danh sách con các thuộc tính của quan hệ được chọn ra R: tên quan hệ cần chiếu ra kết quả Kết quả là một quan hệ chỉ chứa các thuộc tính xác định trong X  Phép chiếu chính là phép rút trích dữ liệu theo cột (chiều dọc) Cơ sở dữ liệu 10/10/2012 24
  25. VD10: r A B C {B,C} (r) r’ B C a 1 c 1 1 1 c1 a 1 c1 2 2 c2 a3 2 c2 Cơ sở dữ liệu 10/10/2012 25
  26. VD11: Cho quan hệ SV ( Mã SV, Họ tên, Ngày sinh, điểm) như sau: Mã SV Họ tên Ngày sinh Điểm 001 Trần Anh 23/4/86 8 002 Ngọc Bích 13/4/85 9 003 Xuân Mai 25/3/87 7 004 Hồng Vân 21/6/85 10 Mã Sv, Điểm (SV) Mã SV Điểm 001 8 002 9 003 7 004 10 Cơ sở dữ liệu 10/10/2012 26
  27. VD12: Cho lược đồ quan hệ: NHANVIEN( MNV, HoNV, TenNV, NS, GT, LUONG, DC, MĐV) Cho biết họ tên và lương của các nhân viên Quan hệ: NHANVIEN Thuộc tính: HONV, TENNV, LUONG HONV, TENNV, LUONG(NHANVIEN) 27 10/10/20 Cơ sở dữ liệu 12
  28. VD13: Cho lược đồ quan hệ: DEAN( MDA, Ten, DiaDiem, MNV ) THANNHAN( Ten, NS, GT, MNV ) Cho biết mã nhân viên có tham gia đề án hoặc có thân nhân MANV(DEAN) MANV(THANNHAN) MANV(DEAN)  MANV(THANNHAN) 28 10/10/20 Cơ sở dữ liệu 12
  29. b. Phép chọn – Selection: Dùng để lấy ra các bộ của quan hệ thoả mãn điều kiện chọn nào đó. (Phép chọn trích ra các hàng trong quan hệ thỏa mãn điều kiện) Dạng tổng quát:  ( R) F: là biểu thức điều kiện để chọn các bộ. Phép so sánh: >, =, Phép logic: ( phủ định ),  (giao),  (hợp) R: là quan hệ thực hiện chọn Kết quả: một quan hệ chứa các bộ (dòng) thỏa mãn điều kiện chọn. Phép chọn có tính giao hoán Cơ sở dữ liệu 10/10/2012 29
  30. VD14: r A B C D a1 1 2 d1 a1 1 2 d2 a2 1 1 d3  (C=1) (r) r A B C D a2 1 1 d3  (C=1)  (A = a1) (r) =  Cơ sở dữ liệu 10/10/2012 30
  31. VD15: Hãy tìm các sinh viên có điểm >8. Mã SV Họ tên Ngày sinh Điểm 001 Trần Anh 23/4/86 8 002 Ngọc Bích 13/4/85 9 003 Xuân Mai 25/3/87 7 004 Hồng Vân 21/6/85 10 Biểu thức:  Điểm >8 (SV) Mã SV Họ tên Ngày sinh Điểm 002 Ngọc Bích 13/4/85 9 004 Hồng Vân 21/6/85 10 Cơ sở dữ liệu 10/10/2012 31
  32. .VD16: Tìm những học viên “Nam‟ có nơi sinh ở „TpHCM‟ HOCVIEN Mahv HoTen Gioitinh Noisinh Malop K1103 Ha Duy Lap Nam Nghe An K11 K1102 Tran Ngoc Han Nu Kien Giang K11 K1104 Tran Ngoc Linh Nu Tay Ninh K11 K1105 Tran Minh Long Nam TpHCM K11 K1106 Le Nhat Minh Nam TpHCM K11 (Gioitinh=‘Nam’)(Noisinh=‘TpHCM’)(HOCVIEN) HOCVIEN Mahv HoTen Gioitinh Noisinh Malop K1105 Tran Minh Long Nam TpHCM K11 K1106 Le Nhat Minh Nam TpHCM K11 32 Cơ sở dữ liệu 10/10/2012 32
  33. VD17: Cho lược đồ quan hệ: NHANVIEN( MNV, HoNV, TenNV, NS, GT, HSL, DC, MĐV) Cho biết họ và tên nhân viên làm việc ở phòng số 4 Quan hệ: NHANVIEN Thuộc tính: HONV, TENNV Điều kiện: MĐV=4 HONV, TENNV (MĐV=4 (NHANVIEN)) 33 10/10/20 Cơ sở dữ liệu 12
  34. c. Phép chia - Division Cho hai lược đồ quan hệ Q1(A1,A2, ,An) và Q2(B1,B2, ,Bm). r và s lần lượt là hai quan hệ trên Q1 và Q2, Ai và Bj lần lượt là các thuộc tính của Q1 và Q2 sao cho n>m Phép chia Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3 như sau: + Q3 = {A1, , An-m} r3 = r ÷ s = { t |u s : (t,u) r } Để tìm quan hệ thương của r  s, ta lần lượt thực hiện theo các bước sau: Q1= y(r); Q2= y((s Q1)-R); Q= Q1–Q2 Cơ sở dữ liệu 10/10/2012 34
  35. * VD18: Cơ sở dữ liệu 10/10/2012 35
  36. VD19: KETQUATHI Mahv Mamh Diem MONHOC HV01 CSDL 7.0 Mamh Tenmh Mahv HV02 CSDL 8.5 CSDL Co so du lieu HV01 HV01 CTRR 8.5 CTRR Cau truc roi rac HV03 THDC Tin hoc dai cuong HV03 CTRR 9.0 KETQUATHIMONHOC HV01 THDC 7.0 MONHOC HV02 THDC 5.0 HV03 THDC 7.5 HV03 CSDL 6.0 KETQUA  KETQUATHI [Mahv, Mamh] KETQUA MONHOC  MONHOC[Mamh] Cơ sở dữ liệu 10/10/2012 36
  37. c. Phép kết nối - Join Dùng để kết hợp hai bộ có liên quan từ hai quan hệ thành một bộ. Phép kết nối của quan hệ r đối với thuộc tính A với quan hệ s đối với thuộc tính B được định nghĩa: r s = {(t  u) | t r và u s và t[A]  u[B] } A  B Kết quả của phép kết nối là một quan hệ Q Có n + m thuộc tính Q(A1, A2, , An, B1, B2, , Bm) Mỗi bộ của Q là tổ hợp của 2 bộ trong r và s, thỏa mãn một số điều kiện nối nào đó, có dạng Ai  Bj Ai là thuộc tính của r, Bj là thuộc tính của s Ai và Bj có cùng miền giá trị  { , , , , , } Cơ sở dữ liệu 10/10/2012 37
  38. VD20: r A B s C D a1 1 1 d1 a2 2 3 d2 r s r s A B C D B C B C a1 1 1 d1 a2 2 1 d1 Cơ sở dữ liệu 10/10/2012 38
  39. VD21: r A B s C D a1 1 1 d1 a2 1 2 d2 a3 2 r s (A B D) r * s (A B D) r * s A B D C=B a1 1 d1 a2 1 d1 a3 2 d2 Cơ sở dữ liệu 10/10/2012 39
  40. VD22: r ( A B C ) s( C D E ) r  s = ( A B C C D E) B≥ C a1 1 1 1 d1 e1 a1 1 1 1 d1 e1 a2 2 1 2 d2 e2 a2 2 1 1 d1 e1 a1 2 2 3 d3 e3 a2 2 1 2 d2 e2 a1 2 2 1 d1 e1 a1 2 2 2 d2 e2 Cơ sở dữ liệu 10/10/2012 40
  41. A1 B2 R S R S A A 1 2 B1 B2 B3 A1 A2 B1 B2 B3 1 2 0 2 8 1 2 8 0 4 1 8 7 8 7 1 2 1 0 7 0 0 8 0 4 1 8 8 0 4 8 4 1 0 7 0 3 2 1 5 1 8 1 0 7 8 4 0 2 8 8 4 8 0 4 8 4 1 0 7 8 4 2 1 5 Cơ sở dữ liệu 10/10/2012 41
  42. Nếu điều kiện kết nối chứa phép so sánh (=) bằng thì phép kết nối được gọi là phép kết nối bằng. Kết nối bằng tại thuộc tính cùng tên của hai quan hệ và một trong hai thuộc tính đó bị loại bỏ qua phép chiếu, thì phép kết nối được gọi là kết nối tự nhiên. Kí hiệu là "*". Cơ sở dữ liệu 10/10/2012 42
  43. * VD23: r2 r1 MAMH TENMH MASV MAMH DIEM 99001 CSDL 5.0 CSDL Cơ sở dữ liệu 99002 CTDL 2.0 CTDL Cấu trúc dữ liệu 99003 MANG 8.0 r3 = r1 |><| r2 = r1* r2 MMH MMH MASV MAMH DIEM TENMH 99001 CSDL 5.0 Cơ sở dữ liệu 99002 CTDL 2.0 Cấu trúc dữ liệu Cơ sở dữ liệu 10/10/2012 43
  44. *BÀI TẬP 1: CHO Cơ sở dữ liệu sau Cơ sở dữ liệu 10/10/2012 45
  45. Yêu cầu: Sử dụng đại số quan hệ thực hiện các yêu cầu sau: 1.Cho biết thông tin cá nhân các NHANVIEN nữ của cơ quan 2.Cho biết danh sách các đề án mà phòng “Hanh chinh” thực hiện 3.Với mỗi phòng ban, cho biết tên phòng ban và địa điểm phòng 4.Tìm tên những người trưởng phòng của từng phòng ban 5.Tìm TENDA, MADA, DDIEM_DA, PHONG, TENPHG, MAPHG, TRPHG, NG_NHANCHUC 6.Tìm tên những nữ nhân viên và thân nhân của họ 7.Với mỗi đề án ở Ha Noi, liệt kê các mã số đề án (MADA), mã số phòng ban chủ trì đề án (PHONG), họ tên trưởng phòng (TENNV, HONV), cũng như địa chỉ và ngày sinh của người ấy. 8.Với mỗi nhân viên, cho biết họ tên của nhân viên và họ tên người quản lý trực tiếp của nhân viên đó Cơ sở dữ liệu 10/10/2012 46
  46. IV. Các phép toán quan hệ nhóm Vấn đề cần giải quyết: .Tính tổng, đếm .tìm người có lương lớn nhất ,v.v .Giải quyết: Nhóm các bộ trong một quan hệ theo một giá trị của một số các thuộc tính của chúng và sau đó áp dụng các hàm trên từng nhóm một cách độc lập Cơ sở dữ liệu 10/10/2012 47
  47. Định nghĩa phép gộp nhóm  ( thuộc tính )(R) Trong đó: TT gộp nhóm: là tên thuộc tính tiến hành gộp nhóm lại Hàm: tên hàm sử dụng để tính toán sau khi gộp nhóm Thuộc tính: tên thuộc tính thực hiện tính toán trên đó R: là tên quan hệ thực hiện .Các hàm nhóm . Tính tổng SUM() . Tính trung bình cộng AVERAGE() . Đếm số bộ thỏa mãn COUNT(), COUNTA(), COUNT(*) . Tìm giá trị lớn nhất MAX(), nhỏ nhất MIN() Cơ sở dữ liệu 10/10/2012 48
  48. Kết quả của phép gộp nhóm là một quan hệ. Ngoài các thuộc tính cơ sở để gộp nhóm, quan hệ kết quả còn có các thuộc tính tương ứng với các phần tử trong danh sách hàm kết tập Nếu ds các thuộc tính cơ sở để gộp nhóm là rỗng thì các hàm kết tập được áp dụng cho các giá trị thuộc tất cả các bộ trong quan hệ. Khi đó quan hệ kết quả chỉ có một bộ Cơ sở dữ liệu 10/10/2012 49
  49. VD 24: a) MA_P  COUNT ( MA_NV) , AVERAGE ( LUONG ) (NHAN_VIEN) Thực hiện: Đếm số nhân viên và tính lương trung bình theo từng phòng Quan hệ kết quả là: MA_P COUNT MA_NV AVERAGE LUONG P1 4 1500000 P2 2 2000000 P3 2 1500000 b)  COUNT( MA_NV), AVERAGE ( LUONG) (NHAN_VIEN) Thực hiện: Đếm số nhân viên và tính lương trung bình của tất cả NV Quan hệ kết quả là : COUNT MA_NV AVERAGE LUONG 8 1666666.7 Cơ sở dữ liệu 10/10/2012 50
  50. *. Bài tập 2: Cơ sở dữ liệu 10/10/2012 51
  51. Yêu cầu: Xây dựng biểu thức đại số quan hệ mô tả 1. Với những phòng ban có lương trung bình của các nhân viên thuộc phòng ban đó là >300.000, cho biết tên phòng ban và số nhân viên thuộc phòng ban đó 2. Với những phòng ban có lương trung bình của các nhân viên thuộc phòng ban đó là >300.000, cho biết tên phòng ban và số nhân viên nam của phòng ban đó. 3. Với mỗi đề án, liệt kê tên đề án (TENDA) và tổng số giờ làm việc cuả tất cả các nhân viên tham gia dự án đó. 4. Với mỗi phòng ban, liệt kê tên phòng ban (TENPHG) và lương trung bình của nhân viên trong phòng 5. Lương trung bình cuả tất cả nữ nhân viên 6. Với những phòng ban có mức lương trung bình trên 30000. Liệt kê tên phòng ban và số lượng nhân viên của phòng đó. Cơ sở dữ liệu 10/10/2012 52
  52. 1. Với những phòng ban có lương trung bình của các nhân viên thuộc phòng ban đó là >300.000, cho biết tên phòng ban và số nhân viên thuộc phòng ban đó Thực hiện Thực hiện tính toán phân nhóm theo từng phòng, trên mỗi phòng tính lương trung bình và đếm số nhân viên của từng phòng sau đó lọc ra các phòng có lương trung bình > 300000 R1 TENPHGAVERAGE(MLUONG), COUNT(*) (PHONGBAN⋈MAPHG, PHG NHANVIEN) R(TENPHG, SONV) AVERAGE(MLUONG)>300000 (R1) Cơ sở dữ liệu 10/10/2012 53
  53. 2. Với những phòng ban có lương trung bình của các nhân viên thuộc phòng ban đó là >300.000, cho biết tên phòng ban và số nhân viên nam của phòng ban đó. Thực hiện: với mỗi phòng thực hiện đếm số nhân viên nam có trong phòng với mỗi phòng tính mức lương trung bình của từng phòng và chọn ra các phòng có lương > 300000 Liệt kê mã phòng và tên phòng R1(PHONG, SONV_NAM) PHONGCOUNT(*) (PHAI=‟Nam‟ (NHANVIEN)) R2 MAPHG, TENPHG AVERAGE(MLUONG) (PHONGBAN ⋈MAPHG, PHG NHANVIEN) R3(MAPHG, TENPHG) AVERAGE(MLUONG)>300000 (R2) R(TENPHG, SONV_NAM) R1⋈PHONG, MAPHG R3 Cơ sở dữ liệu 10/10/2012 54
  54. 3. Với mỗi đề án, liệt kê tên đề án (TENDA) và tổng số giờ làm việc cuả tất cả các nhân viên tham gia dự án đó. Thực hiện: Với mỗi dự án tính tổng thời gian  tính toán phân nhóm trên mã dự án Cơ sở dữ liệu 10/10/2012 55
  55. 4. Với mỗi phòng ban, liệt kê tên phòng ban (TENPHG) và lương trung bình của nhân viên trong phòng Thực hiện: tạo danh sách nhân viên kèm phòng ban thực hiện tính trung bình lương dựa vào từng phòng Cơ sở dữ liệu 10/10/2012 56
  56. 5. Lương trung bình cuả tất cả nữ nhân viên AVERAGE(MLUONG)(PHAI=Nu(NHANVIEN)) 6. Với những phòng ban có mức lương trung bình trên 30000. Liệt kê tên phòng ban và số lượng nhân viên của phòng đó. Thực hiện: Tạo danh sách nhân viên kèm phòng ban => tính lương trung bình và số lượng nhân viên Cơ sở dữ liệu 10/10/2012 57
  57. V. Tối ưu hóa câu hỏi Tối ưu hóa câu hỏi là việc lựa chọn phương pháp xử lý sao cho khi thực hiện câu truy vấn sẽ đạt hiệu quả cao nhất => thay một biểu thức bởi một biểu thức khác đơn giản hơn, thực hiện nhanh hơn Hiệu quả cao đối với các bài toán có lượng dữ liệu lớn. Nguyên tắc: Thực hiện các phép toán làm quan hệ nhỏ đi càng sớm càng tốt Thực hiện các phép toán làm quan hệ lớn hơn càng về sau càng tốt Cơ sở dữ liệu 10/10/2012 58
  58. Ví dụ πHọđệm, Tên, Địa chỉ (σTênDV =‟Nghiên cứu‟(Đơnvị*Nhân viên)) Cơ sở dữ liệu 10/10/2012 59
  59. Bài tập về nhà Cho lược đồ CSDL gồm các quan hệ: NHANVIEN(MaNV, Ten,Gt,NS,Đ/c,Luong,MaP) PHONG(MaP,TenP,MaNVTP, Ngaynhanchuc) DUAN(MaDA, TenDA, Diadiem, MaP) NPT(MaNV, Ten, Gt, Quanhe) NV-DA(MaNV,MADA,Sogio) PHONG_DD(MaP,Diadiem) Cơ sở dữ liệu 10/10/2012 60
  60. Yêu cầu Sử dụng các phép toán đại số quan hệ thực hiện các yêu cầu: 1.Đưa ra danh sách các nhân viên của công ty 2.Danh sách nhân viên nữ có địa chỉ Hà nội. 3.Danh sách nhân viên thuộc diện nghỉ hưu (nam tuổi >60; nữ >55) 4.Danh sách các nhân viên có lương trong khoảng 200 đến 2000. 5.Đưa ra thông tin về trưởng phòng của phòng tổ chức cán bộ. 6.Tính tổng số giờ mà từng nhân viên tham gia vào mỗi dự án 7.Cho biết thông tin phòng làm nhiều dự án nhất Cơ sở dữ liệu 10/10/2012 61