Bài giảng Lý thuyết cơ sở dữ liệu - Chương 4: Đại số quan hệ - Trịnh Thị Xuân

Chủ động – Tích cực Học tập 18/9/21 CHƯƠNG 4 -ĐẠI SỐ QUAN HỆ- Chủ động – Tích cực Học tập 28/9/21 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 38/9/21 1. Phép hợp – Union operation pr1 và r2 lần lượt là hai quan hệ trên cùng tập thuộc tính {A1, A2,, An}. pPhé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ộ từ r1 và r2 Chủ động – Tích cực Học tập 48/9/21 Ví dụ A Br 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 58/9/21 2. Phép giao - Intersection pr1 và r2 lần lượt là hai quan hệ trên cùng tập thuộc tính {A1, A2,, An}. pPhé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 68/9/21 pVD: A B a1 r a2 a3 b1 b2 b3 C c1 c2 c3 A Bs C a2 b1 c3 a1 b1 c1 A Br Ç s C a1 b1 c1 Chủ động – Tích cực Học tập 78/9/21 3. Phép trừ - Minus, difference pr1 và r2 lần lượt là hai quan hệ trên cùng tập thuộc tính {A1, A2,, An}. pPhé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 88/9/21 Ví dụ A B a1 r a2 a3 b1 b2 b3 C c1 c2 c3 A Bs C a2 b1 c3 a1 b1 c1 A Br - s a2 a3 b2 b3 C c2 c3 Chủ động – Tích cực Học tập 98/9/21 4. Tích decac – Cartesian Product pr1 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) pTí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 108/9/21 pVD: A Br a2 2 a1 1 C Ds d23 d11 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 118/9/21 II. Các phép toán đại số quan hệ Chủ động – Tích cực Học tập 128/9/21 1. Phép chiếu - Projection pr 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+. pPhé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 pKý hiệu: pX ( r) Hoặc r.{ X } qTrong đó: qX: danh sách tập con thuộc tính của quan hệ được chọn ra qr: tên quan hệ cần chiếu ra kết quả qKế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 138/9/21 Ví dụ p{B,C} (r)A B a1 r a2 a3 1 2 3 C c1 c2 c3 B 1 2 3 C c1 c2 c3 Chủ động – Tích cực Học tập 148/9/21 pVD: 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 pMã 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 15 pVD: Cho lược đồ quan hệ: NHANVIEN( MNV, HoNV, TenNV, NS, GT, HSL, DC, MĐV) pViế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) pQuan hệ: NHANVIEN pThuộc tính: HONV, TENNV, LUONG pHONV, TENNV, HSL*1350000(NHANVIEN) Chủ động – Tích cực Học tập pVD: Cho lược đồ quan hệ: nDUAN( MDA, Ten, DiaDiem, MNV ) nTHANNHAN( Ten, NS, GT, MNV ) pViế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 16Cơ sở dữ liệu pMANV(DEAN) pMANV(THANNHAN) pMANV(DEAN) È pMANV(THANNHAN) Chủ động – Tích cực Học tập 178/9/21 2. Phép chọn – Selection: pDù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 pDạng tổng quát: s ( r ) nF: là biểu thức điều kiện để lựa chọn các dòng pNhiều điều kiện: ¬( !-phủ định ), Ù (giao-và), Ú (hợp-hoặc) nr: là quan hệ thực hiện chọn trên đó pKế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 188/9/21 Ví dụ s (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 s (C=1) Ù (A = a1) (r) = Æ Chủ động – Tích cực Học tập 198/9/21 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: s Điểm > 8 (SV) Chủ động – Tích cực Học tập 208/9/21 §VD: Liệt kê danh sách học viên “Nam” có nơi sinh ở ‘TpHCM’ 20 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 s(Gioitinh=‘Nam’) Ù (Noisinh=‘TpHCM’)(HOCVIEN) Chủ động – Tích cực Học tập Cơ sở dữ liệu 21 pVD: Cho lược đồ quan hệ: NHANVIEN( MNV, HoNV, TenNV, NS, GT, HSL, DC, Phg) pĐSQH: Cho biết họ và tên nhân viên làm việc ở phòng số 4 pQuan hệ: NHANVIEN pThuộc tính: HONV, TENNV pĐiều kiện: PHG=4 pĐSQH: Cho biết họ và tên nhân viên Nữ, có hệ số lương lớn hơn 4 pHONV, TENNV ( sPHG=4 (NHANVIEN) ) pHONV, TENNV ( sGT=‘Nữ’ Ù HSL>4 (NHANVIEN) ) Chủ động – Tích cực Học tập pViết biểu thức ĐSQH và cho biết kết quả của yêu cầu Cho danh sách họ tên của các sinh viên có điểm trên 8 228/9/21 SINHVIEN 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 Chủ động – Tích cực Học tập 238/9/21 3. Phép kết nối - Join pDùng để kết hợp hai bộ có liên quan từ hai quan hệ thành một bộ. pPhé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 q Bj •Ai là thuộc tính của r, Bj là thuộc tính của s •q Î { ¹, =, , £, ³ } A q B r s = {(t È u) | t Î r và u Î s và t[A] q u[B] } Chủ động – Tích cực Học tập 248/9/21 Ví dụ A Br a2 2 a1 1 C Ds d23 d11 r s B ³ C Chủ động – Tích cực Học tập 258/9/21 pChú ý: nNế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. nKế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 268/9/21 * 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 278/9/21 Ví dụ pCho lược đồ CSDL Quản lý đề án công ty nNHÂN VIÊN( Mã nhân viên, Họ đệm, Tên NV, Ngày sinh, Giới tính, HSL, Địa chỉ, mã đơn vị) nĐƠN VỊ ( mã đơn vị, tên đơn vị, mã trưởng phòng, ngày bắt đầu) nĐƠN VI –DIA DIEM (Mã đơn vị, Địa điểm) nDỰ ÁN ( mã dự án, tên dự án, địa điểm, thời gian bắt đầu, mã đơn vị) nPHÂ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 họ tên nhân viên có Hệ số lương trên 4 2.Cho biết họ tên, ngày sinh và giới tính của các nhân viên nữ có tuổi trên 20 3.Cho biết danh sách họ tên và ngày sinh của các nhân viên của phòng kế toán 4.Cho biết tên các dự án tại Hà Nội được triển khai trong năm 2016 5.Cho biết danh sách họ tên nhân viên đã tham gia vào dự án có địa điểm tại Hà Nội Chủ động – Tích cực Học tập 288/9/21 III. Các phép toán gom nhóm trên quan hệ pLà thực hiện thao tác gom nhóm các dòng dữ liệu thỏa mãn điều kiện nào đó lại với nhau, sau đó thực hiện các thao tác tính toán trên đó pVấ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.. Chủ động – Tích cực Học tập 298/9/21 pĐịnh nghĩa phép gộp nhóm Á ( thuộc tính ) ( R ) pTrong đó: nTT gộp nhóm: là tên thuộc tính tiến hành gộp nhóm lại nHàm: tên hàm sử dụng để tính toán sau khi gộp nhóm nThuộc tính: tên thuộc tính thực hiện tính toán trên đó nR: 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 308/9/21 pNế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ộ pKế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 318/9/21 §VD: Đếm số nhân viên theo từng giới tính 31 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 Chủ động – Tích cực Học tập 328/9/21 Ví dụ áp dụng pCho lược đồ CSDL Quản lý đề án công ty nNHÂN VIÊN( Mã nhân viên, Họ đệm, Tên NV, Ngày sinh, Giới tính, HSL, Địa chỉ, mã đơn vị, nĐƠN VỊ ( mã đơn vị, tên đơn vị, mã trưởng phòng, ngày bắt đầu) nDỰ ÁN ( mã dự án, tên dự án, địa điểm, thời gian bắt đầu, mã đơn vị) nPHÂ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.Tính tổng lương của các nhân viên theo từng phòng ban 2.Cho biết tên phòng ban và tổng số nhân viên của phòng ban tương ứng 3.Cho biết tên phòng ban có trên 5 nhân viên 4.Cho biết tên dự án và tổng số nhân viên đã tham gia của từng dự án đó 5.Cho biết tên nhân viên và tổng số giờ đã làm cho dự án của từng nhân viên đó Chủ động – Tích cực Học tập 338/9/21 *. Bài tập 2: Chủ động – Tích cực Học tập 348/9/21 Yêu cầu: nNHÂ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ĐƠN VỊ ( mã đơn vị, tên, mã trưởng phòng, ngày bắt đầu) nĐƠN VI –DIA DIEM (Mã đơn vị, Địa điểm) nDỰ ÁN ( mã dự án, tên, địa điểm, thời gian bắt đầu, mã đơn vị) nPHÂN CÔNG(mã dự án, mã nhân viên, số giờ) pXâ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, thống kê theo tên phòng 2.Cho tên phòng ban có tổng lương trên 10 triệu 3.Cho biết tên nhân viên và tổng số giờ đã tham gia dự án của nhân viên đó 4.Cho biết tên dự án và tổng số nhân viên đã tham gia làm cho từng dự án 5.Cho biết tên phòng ban đã thực hiện trên 3 dự án 6.Cho biết tên dự án, địa điểm dự án của các dự án có số giờ nhân viên tham gia trên 10 7.Cho biết tên của nhân viên có số giờ làm cho dự án là nhiều nhất 8.Cho biết tên của phòng ban có tổng lương thấp nhất 9.Cho tên phòng ban có số nhân viên dưới 5 Chủ động – Tích cực Học tập 358/9/21 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 đó pThực hiện nThự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 nsau đó lọc ra các phòng có lương trung bình > 300000 pR1ç TENPHGÁAVERAGE(MLUONG), COUNT(*) (PHONGBAN⋈MAPHG, PHG NHANVIEN) pR(TENPHG, SONV)ç sLUONGTB>300000 (R1) Chủ động – Tích cực Học tập 368/9/21 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 đó. pThực hiện: nvới mỗi phòng thực hiện đếm số nhân viên nam có trong phòng nvớ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 nLiệt kê mã phòng và tên phòng pR1(PHONG, SONV_NAM)ç PHONGÁCOUNT(*) (sPHAI=’Nam’ (NHANVIEN)) pR2ç MAPHG, TENPHG ÁAVERAGE(MLUONG) (PHONGBAN ⋈MAPHG, PHG NHANVIEN) pR3(MAPHG, TENPHG)ç sLUONGTB>300000 (R2) pR(TENPHG, SONV_NAM)ç R1⋈PHONG, 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ố giờ làm việc cuả tất cả các nhân viên tham gia dự án đó. pThực hiện: nVớ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 pR(TENDA,TONGSOGIO)ç SODAÁSUM(THOIGIAN)(DEAN⋈MADA,MADA PHANCONG) 378/9/21 Chủ động – Tích cực Học tập 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 pThực hiện: ntạo danh sách nhân viên kèm phòng ban nthực hiện tính trung bình lương dựa vào từng phòng pR(TENPHG,LUONGTB) ç PHONGÁAVERAGE MLUONG(PHONGBAN⋈MAPHG,PHONG NHANVIEN) 388/9/21 Chủ động – Tích cực Học tập 5. Lương trung bình cuả tất cả nữ nhân viên ÁAVERAGE MLUONG(sPHAI=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 đó. pThực hiện: nTạo danh sách nhân viên kèm phòng ban => tính lương trung bình và pR1ç PHGÁAVERAGE MLUONG(PHONGBAN⋈MAPHG,PHONG NHANVIEN) pR(TENPHG,SONV) çMAPHGÁCOUNT MANV(sLUONGTB>30000(R1⋈MAPHG,PHONG NHANVIEN)) 398/9/21 Chủ động – Tích cực Học tập 408/9/21 Bài tập về nhà pCho lược đồ CSDL gồm các quan hệ: pNHANVIEN(MaNV, Ten,Gt,NS,Đ/c,Luong,MaP) pPHONG(MaP,TenP,MaNVTP, Ngaynhanchuc) pDUAN(MaDA, TenDA, Diadiem, MaP) pNPT(MaNV, Ten, Gt, Quanhe) pNV-DA(MaNV,MADA,Sogio) pPHONG_DD(MaP,Diadiem) Chủ động – Tích cực Học tập 418/9/21 Yêu cầu p 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 Chủ động – Tích cực Học tập Bài tập về nhà pĐọc trước phần ràng buộc toàn vẹn nKhái niệm nCác đặc trưng của ràng buộc toàn vẹn nPhân loại các ràng buộc toàn vẹn 428/9/21 Chủ động – Tích cực Học tập Vấn đề bài tập lớn pBuổi sau: n kiểm tra 1 tiết nKiểm tra sơ bộ BTL pPhát biểu bài tập lớn pXác định mô hình ER của bài toán (tóm tắt) pXác định mô hình CSDL quan hệ của bài toán (tóm tắt) nBTL – in ra và ghi rõ họ tên nhóm 438/9/21