Cơ sở dữ liệu - Chương 4: Đại số quan hệ

Chủ động – Tích cực Học tập 1"8/4/16 CHƯƠNG 4 -ĐẠI SỐ QUAN HỆ- Chủ động – Tích cực Học tập 4"8/4/16 I. Các phép toán đại số trên tập hợp Chủ động – Tích cực Học tập 5"8/4/16 1. Phép hợp – Union operation ! r1 và r2 lần lượt là hai quan hệ trên cùng tập thuộc tính {A1, A2,, An}. ! Phép hợp của hai quan hệ r1 và r2 sẽ tạo thành một quan hệ r3. Với r3 được xác định như sau: Q3+ = {A1, A2, , An} r3 = r1 ∪ r2 = { t | t ∈ r1 hoặc t ∈ r2} ! Quan hệ r3 là tập hợp các bộ thuộc r1 hoặc thuộc r2 hoặc thuộc cả hai Chủ động – Tích cực Học tập 6"8/4/16 Ví dụ A B r C a2 b1 c3 a1 b1 c1 A B a1 s a2 a3 b1 b2 b3 C c1 c2 c3 A B a1 r ∪ s a2 a3 b1 b2 b3 C c1 c2 c3 a2 b1 c3 Chủ động – Tích cực Học tập 8"8/4/16 ! VD: Lập danh sách tất cả các Học viên đã được khen thưởng DOT1 Mahv Hoten K1103 Le Van Tam K1114 Tran Ngoc Han K1203 Le Thanh Hau K1308 Nguyen Gia DOT2 Mahv Hoten K1101 Le Kieu My K1114 Tran Ngoc Han K1203 Le Thanh Hau K1308 Nguyen Gia DOT1∪DOT2 Chủ động – Tích cực Học tập 9"8/4/16 2. Phép giao - Intersection ! r1 và r2 lần lượt là hai quan hệ trên cùng tập thuộc tính {A1, A2,, An}. ! Phép giao của hai quan hệ r1 và r2 sẽ tạo thành một quan hệ r3. Với r3 được xác định như sau: Q3+ = {A1, A2, , An} r3 = r1 ∩ r2 = { t | t ∈ r1 và t ∈ r2} ! Quan hệ r3 là tập hợp các bộ thuộc trên cả hai quan hệ r1 và r2 Chủ động – Tích cực Học tập 10"8/4/16 ! VD: A B a1 r a2 a3 b1 b2 b3 C c1 c2 c3 A B s C a2 b1 c3 a1 b1 c1 A B r ∩ s C a1 b1 c1 Chủ động – Tích cực Học tập 12"8/4/16 ! VD: Lập danh sách tất cả các Học viên đã được khen thưởng trong cả 2 đợt DOT1 Mahv Hoten K1103 Le Van Tam K1114 Tran Ngoc Han K1203 Le Thanh Hau K1308 Nguyen Gia DOT2 Mahv Hoten K1101 Le Kieu My K1114 Tran Ngoc Han K1203 Le Thanh Hau K1308 Nguyen Gia DOT1 ∩ DOT2 Chủ động – Tích cực Học tập 13"8/4/16 3. Phép trừ - Minus, difference ! r1 và r2 lần lượt là hai quan hệ trên cùng tập thuộc tính {A1, A2,, An}. ! Phép trừ của hai quan hệ r1 và r2 sẽ tạo thành một quan hệ r3. Với r3 được xác định như sau: Q3+ = {A1, A2, , An} r3 = r1 - r2 = { t | t ∈ r1 và t ∉ r2} !Quan hệ r3 là tập hợp các bộ thuộc quan hệ r1 nhưng không thuộc quan hệ r2 Chủ động – Tích cực Học tập 14"8/4/16 Ví dụ A B a1 r a2 a3 b1 b2 b3 C c1 c2 c3 A B s C a2 b1 c3 a1 b1 c1 A B r - s a2 a3 b2 b3 C c2 c3 Chủ động – Tích cực Học tập 16"8/4/16 ! VD: Lập danh sách học viên được khen thưởng đợt 1 nhưng không được khen thưởng đợt 2 DOT1 Mahv Hoten K1103 Le Van Tam K1114 Tran Ngoc Han K1203 Le Thanh Hau K1308 Nguyen Gia DOT1- DOT2 DOT2 Mahv Hoten K1101 Le Kieu My K1114 Tran Ngoc Han K1203 Le Thanh Hau K1308 Nguyen Gia Chủ động – Tích cực Học tập 18"8/4/16 4. Tích decac – Cartesian Product ! r1 là quan hệ trên tập thuộc tính (A1, A2, , An) và r2 là quan hệ trên tập thuộc tính (B1, B2, , Bm) ! Tích decac của hai quan hệ r1 và r2 sẽ tạo thành một quan hệ r3 được xác định: Q3+ = Q1+ ∪ Q2+ = { A1, A2, , An, B1, B2 ... Bm } r3 = r1 x r2 = { (t1, t2) | t1 ∈ r1 và t2 ∈ r2 } "Quan hệ r3 là tập các bộ được ghép lần lượt từ hai quan hệ r1 và r2. Các thành phần của r1 được đặt trước rồi đến r2. Chủ động – Tích cực Học tập 19"8/4/16 ! VD: A B r a2 2 a1 1 C D s d2 3 d1 1 A B a1 r × s a1 a2 1 1 2 C 1 3 1 D d1 d2 d1 a2 2 3 d2 Chủ động – Tích cực Học tập 21"8/4/16 VD: HOCVIEN Mahv Hoten K1103 Le Van Tam K1114 Tran Ngoc Han K1203 Le Thanh Hau MONHOC Mamh TRR THDC CTDL HOCVIEN×MONHOC Chủ động – Tích cực Học tập 22"8/4/16 5. Phép chia - Division ! r1 là quan hệ trên tập thuộc tính (A1, A2, , An) và r2 là quan hệ trên tập thuộc tính (B1, B2, , Bm) ! Phép chia quan hệ r1 và r2 sẽ tạo thành một quan hệ r3 như sau: Q3+ = {A1, ..., An-m} r3 = r1 ÷ r2 = { t | ∀u ∈ r2: (t , u) ∈ r1 } " với mọi u thuộc s thì (t , u) thuộc r Chủ động – Tích cực Học tập 23"8/4/16 * VD: Chủ động – Tích cực Học tập 24"8/4/16 VD: Mahv HV01 HV03 KETQUATHI Mahv Mamh Diem HV01 CSDL 7.0 HV02 CSDL 8.5 HV01 CTRR 8.5 HV03 CTRR 9.0 HV01 THDC 7.0 HV02 THDC 5.0 HV03 THDC 7.5 HV03 CSDL 6.0 MONHOC Mamh Tenmh CSDL Co so du lieu CTRR Cau truc roi rac THDC Tin hoc dai cuong KETQUA MONHOC KETQUATHI÷MONHOC ][ ],[ MamhMONHOCMONHOC MamhMahvKETQUATHIKETQUA ← ← Chủ động – Tích cực Học tập 26"8/4/16 II. Các phép toán đại số quan hệ Chủ động – Tích cực Học tập 27"8/4/16 1. Phép chiếu - Projection ! r là quan hệ trên tập thuộc tính (A1,A2,,An), X là tập thuộc tính với X⊆Q+. ! Phép chiếu của quan hệ r lên tập thuộc tính X là loại bỏ đi một số thuộc tính của lược đồ Q không có trong X và giữ lại những thuộc tính được liệt kê trong danh sách thuộc tính X ! Ký hiệu: π X ( r) Hoặc r.{ X } # Trong đó: # X: danh sách tập con 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 tập X " Phép chiếu chính là phép rút trích dữ liệu theo cột (chiều dọc) Chủ động – Tích cực Học tập 28"8/4/16 Ví dụ π{B,C} (r) A B a1 r a2 a3 1 1 2 C c1 c1 c2 Chủ động – Tích cực Học tập 29"8/4/16 ! VD: 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 001 8 002 9 003 7 004 10 πMã Sv, Điểm (SV) Cho danh sách gồm mã SV và điểm tương ứng? Chủ động – Tích cực Học tập Cơ"sở"dữ" liệu" 30" ! VD: Cho lược đồ quan hệ: NHANVIEN( MNV, HoNV, TenNV, NS, GT, HSL, DC, MĐV) ! Viết biểu thức ĐSQH: Cho biết danh sách họ tên và lương của các nhân viên (Lương = HSL *1350000) ! Quan hệ: NHANVIEN ! Thuộc tính: HONV, TENNV, LUONG πHONV, TENNV, HSL*1350000(NHANVIEN) Chủ động – Tích cực Học tập ! VD: Cho lược đồ quan hệ: $ DUAN( MDA, Ten, DiaDiem, MNV ) $ THANNHAN( Ten, NS, GT, MNV ) ! Viết ĐSQH: Cho biết mã nhân viên của những nhân viên có tham gia đề án nào đó hoặc có thân nhân 31"Cơ"sở"dữ"liệu" πMANV(DEAN) πMANV(THANNHAN) πMANV(DEAN) ∪ πMANV(THANNHAN) Chủ động – Tích cực Học tập 32"8/4/16 2. Phép chọn – Selection: ! Dùng để trích chọn ra một tập con các bản ghi (bộ) trong một quan hệ, các bộ được trích chọn phải thoả mãn điều kiện chọn " 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 để lựa chọn các dòng ! Nhiều điều kiện: ¬( phủ định ), ∧ (giao), ∨ (hợp) $ r: là quan hệ thực hiện chọn trên đó ! Kết quả: một quan hệ có danh sách thuộc tính được chỉ ra trong quan hệ và có các dòng thỏa mãn điều kiện. Chủ động – Tích cực Học tập 33"8/4/16 Ví dụ σ (C=1) (r) A B a2 r 1 C 1 D d3 A B a1 r a1 a2 1 1 1 C 2 2 1 D d1 d2 d3 σ (C=1) ∧ (A = a1) (r) = ∅ Chủ động – Tích cực Học tập 34"8/4/16 VD: Liệt kê danh sách 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 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 Biểu thức: σ Điểm > 8 (SV) Chủ động – Tích cực Học tập 35"8/4/16 % VD: Liệt kê danh sách học viên Nam có nơi sinh ở
TpHCM 35 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 HOCVIEN Mahv HoTen Gioitinh Noisinh Malop K1105 Tran Minh Long Nam TpHCM K11 K1106 Le Nhat Minh Nam TpHCM K11 σ(Gioitinh=‘Nam’) ∧ (Noisinh=‘TpHCM’)(HOCVIEN) Chủ động – Tích cực Học tập Cơ"sở"dữ" liệu" 36" ! VD: Cho lược đồ quan hệ: NHANVIEN( MNV, HoNV, TenNV, NS, GT, HSL, DC, Phg) ! ĐSQH: 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: PHG=4 πHONV, TENNV ( σPHG=4 (NHANVIEN) ) Chủ động – Tích cực Học tập 37"8/4/16 3. 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 trên thuộc tính A với quan hệ s trên thuộc tính B được định nghĩa: • kết nối là một quan hệ q, thỏa mãn • 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 điều kiện nối có dạng Ai θ Bj • Ai là thuộc tính của r, Bj là thuộc tính của s • θ ∈ { ≠, =, , ≤, ≥ } A θ B r s = {(t ∪ u) | t ∈ r và u ∈ s và t[A] θ u[B] } Chủ động – Tích cực Học tập 38"8/4/16 Ví dụ A B r a2 2 a1 1 C D s d2 3 d1 1 r s B ≥ C Chủ động – Tích cực Học tập 42"8/4/16 ! Chú ý: $ 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à "*" Chủ động – Tích cực Học tập 43"8/4/16 * VD: r1 MASV MAMH DIEM 99001 CSDL 5.0 99002 CTDL 2.0 99003 MANG 8.0 r2 MAMH TENMH CSDL Cơ sở dữ liệu CTDL Cấu trúc dữ liệu MASV MAMH DIEM TENMH 99001 CSDL 5.0 Cơ sở dữ liệu 99002 CTDL 2.0 Cấu trúc dữ liệu r3 = r1 |><| r2 = r1* r2 MMH MMH Chủ động – Tích cực Học tập 44"8/4/16 *BÀI TẬP 1: Cho Cơ sở dữ liệu sau Chủ động – Tích cực Học tập 45"8/4/16 Ví dụ ! Cho lược đồ CSDL Quản lý đề án công ty $ NHÂN VIÊN( Mã nhân viên, Họ đệm, Tên, Ngày sinh, Giới tính, HSL, Địa chỉ, mã đơn vị, $ ĐƠN VỊ ( mã đơn vị, tên, mã trưởng phòng, ngày bắt đầu) $ ĐƠN VI –DIA DIEM (Mã đơn vị, Địa điểm) $ DỰ ÁN ( mã dự án, tên, địa điểm, thời gian bắt đầu, mã đơn vị) $ PHÂN CÔNG (mã dự án, mã nhân viên, số giờ) 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 nữ nhân viên và thân nhân của họ 5. 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. 6. Với mỗi nhân viên, cho biết họ tên của nhân viên và tên của dự án mà nhân viên đã tham gia Chủ động – Tích cực Học tập 1. Cho biết thông tin cá nhân các NHANVIEN nữ của cơ quan πMANV,HONV,TENLOT,TENNV,NGSINH,DIACHI (σPHAI=NU(NHANVIEN)) 2. Cho biết danh sách các đề án mà phòng Hanh chinh thực hiện πMADA,TENDA,DDIEM_DA,NGBD_DK,NGKT_DK(σPHONG=HANH CHINH(DEAN)) 3. Với mỗi phòng ban, cho biết tên phòng ban và địa điểm phòng Thực hiện kết nối bảng PHONGBAN và bảng DIADIEM_PHG dựa vào thuộc tính MAPHG bằng nhau trên hai bảng R(TENPHG, DIADIEM) & ∏TENPHG, DIADIEM(PHONGBAN MAPHG, MAPHG DIADIEM_PHG) 4. Tìm tên những người trưởng phòng của từng phòng ban Thưc hiện kết nối bảng PHONGBAN và bảng NHANHVIEN dựa trên mã trưởng phòng và mã nhân viên giống nhau !  R & ∏HONV, TENLOT, TENNV, TENPHG(PHONGBAN TRPHG, MANV NHANVIEN) 46"8/4/16 Chủ động – Tích cực Học tập 5. Tìm TENDA, MADA, DDIEM_DA, PHONG, TENPHG, MAPHG, TRPHG, NG_NHANCHUC Kết nối bảng DEAN và bảng PHONGBAN dựa vào mã phòng trùng nhau R & ∏TENDA, MADA, DDIEM_DA, PHONG, TENPHG, MAPHG, TRPHG, NG_NHANCHUC(DEAN PHONG, MAPHG PHONGBAN) 6. Tìm tên những nữ nhân viên và thân nhân của họ Lấy ra mã phòng của phòng nghiên cứu sau đó dùng phép kết nối tự nhiên trên mã phòng đó với bảng NHANVIEN Cách 1: R1(MAPHG) & ∏MAPHG (σ TENPHG=Nghien cuu (PHONGBAN)) R & ∏HONV,TENLOT,TENNV,DCHI (NHANVIEN *PHG, MAPHG R1) Cách 2: R & ∏HONV,TENLOT,TENNV,DCHI (NHANVIEN *PHG, MAPHG (∏MAPHG (σ TENPHG=Nghien cuu (PHONGBAN))) 47"8/4/16 Chủ động – Tích cực Học tập 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. -Chọn ra thông tin của các đề án ở
Hà Nội -Lấy ra thông tin của các nhân viên cùng với mã phòng ban tương ứng -Kết nối hai thông tin trên dựa vào mã phòng khớp nhau R1 & ∏MADA, TENDA, DDIEM_DA, PHONG (σ DDIEM_DA=Ha Noi (DEAN)) R2 & ∏TENNV, HONV, DCHI, NGSINH, PHG (PHONGBAN TRPHG, MANV NHANVIEN) R & ∏MADA,PHONG,HONV,TENNV,DCHI,NGSINH(R1 PHONG, PHG R2) 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 đó R1 & NHANVIEN R(TENNV, HONV, TENNQL, HONQL) & ∏TENNV, HONV, TENNV, HONV(R1 MA_NQL, MANV NHANVIEN) 48"8/4/16 Chủ động – Tích cực Học tập 49"8/4/16 III. Các phép toán gom nhóm trên quan hệ ! 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 Chủ động – Tích cực Học tập 50"8/4/16 ! Đị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 tính toán nhóm thường dùng % Tính tổng SUM % Tính trung bình cộng AVERAGE % Đếm số bộ thỏa mãn COUNT, COUNTA % Tìm giá trị lớn nhất MAX, nhỏ nhất MIN Chủ động – Tích cực Học tập 51"8/4/16 ! 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ộ ! 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 Chủ động – Tích cực Học tập 52"8/4/16 a) MA_P ℑ COUNT A( 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) ℑ COUNTA ( 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 VD: Chủ động – Tích cực Học tập 53"8/4/16 IV. Tối ưu hóa truy vấn Chủ động – Tích cực Học tập 1. Các quy tắc biến đổi ! Quy tắc: Xử lý các toán tử AND trong biểu thức điều kiện σc1 and c2... And cn (R)≡σc1(σc2 (...σcn (R))...) ! Ví dụ: NHANVIEN (manv, honv, tennv, ngaysinh, phai, luong, maphong) 54"8/4/16 Chủ động – Tích cực Học tập ! Quy tắc: Thay đổi thứ tự của các phép chọn σ c1 (σ c 2 (R )) ≡ σ c 2 (σ c1 (R )) ! Ví dụ: NHANVIEN (manv, honv, tennv, ngaysinh, phai, luong, maphong) 55"8/4/16 Chủ động – Tích cực Học tập ! Quy tắc: Thay đổi thứ tự các phép chọn và chiếu π A1, A 2 , . . . ,An (σ c (R )) ≡ σ c (π A1, A 2 , . . . ,An (R )) ! Ví dụ: NHANVIEN (manv, honv, tennv, ngaysinh, phai, luong, maphong) 57"8/4/16 Chủ động – Tích cực Học tập ! Quy tắc: Thay đổi thứ tự giữa phép chọn và phép kết ! Ví dụ: NHANVIEN (manv, honv, tennv, ngaysinh, phai, luong, maphong) PHONGBAN (maphong, tenphong, maql) 59"8/4/16 Chủ động – Tích cực Học tập ! Quy tắc: Thay đổi thứ tự giữa phép chọn và phép kết ! Ví dụ: NHANVIEN (manv, honv, tennv, ngaysinh, phai, luong, maphong) PHONGBAN (maphong, tenphong, maql) 60"8/4/16 Chủ động – Tích cực Học tập ! Quy tắc: Phân phối giữa phép chiếu và phép kết ! Ví dụ: NHANVIEN (manv, honv, tennv, ngaysinh, phai, luong, maphong) PHONGBAN (maphong, tenphong, maql) 61"8/4/16 Chủ động – Tích cực Học tập Giải thuật Heuristics để tối ưu ! Áp dụng lần lượt các quy tắc trên để tối ưu ! Áp dụng quy tắc tách các phép chọn liên kiện thành 1 dãy các phép chọn. ! Áp dụng quy tắc để đẩy phép chọn xuống càng sâu càng tốt ! Phối hợp tích Decartes với các phép chiếu thích hợp theo sau ! Áp dụng quy tắc để đẩy phép chiếu xuống càng sâu càng tốt (có thể phát sinh phép chiếu mới) ! Áp dụng quy tắc để loại những phép chiếu vô ích 66"8/4/16 Chủ động – Tích cực Học tập Ví dụ: Ví dụ áp dụng tối ưu ! Liệt kê họ tên NHANVIEN sinh sau năm 1960 và làm dự án “ABC” 68"8/4/16 Chủ động – Tích cực Học tập 69"8/4/16 Chủ động – Tích cực Học tập 70"8/4/16 Chủ động – Tích cực Học tập 71"8/4/16 Chủ động – Tích cực Học tập Bài tập áp dụng ! NHANVIEN(MaNV, Hoten, DiaChi, SDT, NgaySinh, GT, HSL) ! HANG(MaHang, TenHang, NhaSX, TGianBaoHanh) ! KHACHHANG(MaKH, TenKH, CMT, DiaChi, SoDienThoai, Email) ! HOADONXUAT(MaHD, MaKH, NgayLapHD, MaNV, PhuongThucTT) ! CT_HOADON(MaHD, MaHang, SoLuongMua, DonGia) ! Viết câu lệnh đại số quan hệ thực hiện yêu cầu sau: $ Cho danh sách họ tên của nhân viên sinh vào tháng 3 hoặc tháng 10 $ Cho biết tuổi cao nhất hiện nay của các nhân viên $ Cho danh sách khách hàng có địa chỉ không thuộc quận Đống Đa hoặc quận Hoàng Mai $ Tính tổng số tiền đã bán được của từng hóa đơn $ Tính tổng số lượng và tổng số tiền đã bán được của từng mặt hàng $ Tính tổng số lượng và tổng số tiền đã bán được của từng mặt hàng trong năm 2013 $ Tính tổng số tiền đã mua hàng của từng khách hàng, thống kê dựa vào tên khách hàng $ Cho biết tên của mặt hàng đã bán được nhiều nhất $ Cho biết tên các nhân viên đã bán được hơn 10triệu tiền hàng 72 Chủ động – Tích cực Học tập 73"8/4/16 *. Bài tập 2: Chủ động – Tích cực Học tập 74"8/4/16 Yêu cầu: $ NHÂN VIÊN( Mã nhân viên, Họ đệm, Tên, Ngày sinh, Giới tính, HSL, Địa chỉ, mã đơn vị, $ ĐƠN VỊ ( mã đơn vị, tên, mã trưởng phòng, ngày bắt đầu) $ ĐƠN VI –DIA DIEM (Mã đơn vị, Địa điểm) $ DỰ ÁN ( mã dự án, tên, địa điểm, thời gian bắt đầu, mã đơn vị) $ PHÂN CÔNG(mã dự án, mã nhân viên, số giờ) ! Xây dựng biểu thức đại số quan hệ mô tả 1. Cho biết tổng lương của từng phòng ban 2. Cho tên phòng ban có tổng lương trên 10 triệu 3. Cho tên phòng ban có tổng số nhân viên dưới 5 4. Cho biết tên nhân viên và tổng số giờ đã tham gia dự án của nhân viên đó 5. Cho biết tổng số nhân viên đã tham gia làm cho từng dự án 6. Cho biết số dự án đã thực hiện của từng phòng ban, theo tên 7. Cho biết tên dự án và tổng số giờ làm của các nhân viên cho dự án đó 8. Cho biết tên của nhân viên có số giờ làm cho dự án là nhiều nhất 9. Cho biết tên của phòng ban có tổng lương thấp nhất 10. Cho biết tên của nhân viên có lương thấp nhất Chủ động – Tích cực Học tập 75"8/4/16 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(*) (PHONGBANMAPHG, PHG NHANVIEN) ! R(TENPHG, SONV) & σLUONGTB>300000 (R1) Chủ động – Tích cực Học tập 76"8/4/16 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) & σLUONGTB>300000 (R2) ! R(TENPHG, SONV_NAM) & R1PHONG, MAPHG R3 Chủ động – Tích cực Học tập 3. Với mỗi đề án, liệt kê tên đề án (TENDA) và tổng s