Giáo trình Cơ sở dữ liệu - Nguyễn Thị Kim Phụng

Nội dung text: Giáo trình Cơ sở dữ liệu - Nguyễn Thị Kim Phụng

1. ĐẠỌỆ I H C CÔNG NGH THÔNG TIN Cơ s ở d ữ li ệ u Giả ng viên: ThS. Nguy ễ n Th ị Kim Ph ụ ng Email: phungntk@uit.edu.vn
2. Nộ i dung 1. Đạ i s ố quan h ệ 2. Ngôn ngữ truy v ấ n SQL 3.Ràng buộ c toàn v ẹ n
3. 1. Đạ i s ố quan h ệ
4. 1. ĐẠỐỆ I S QUAN H . Là mộ t mô hình toán h ọ c d ự a trên lý thuy ế t t ậ p h ợ p . Đố i t ượ ng x ử lý là các quan h ệ trong c ơ s ở d ữ li ệ u quan hệ . Cho phép sử d ụ ng các phép toán rút trích d ữ li ệ u t ừ các quan hệ . Tố i ư u hóa quá trình rút trích d ữ li ệ u . Gồ m có:  Các phép toán đạ i s ố quan h ệ  Biể u th ứ c đ ạ i s ố quan h ệ
5. 1. ĐSQH - Các phép toán ĐSQH, biể u th ứ c ĐSQH • Có năm phép toán cơ b ả n: – Chọ n ( σ ) Chọ n ra các dòng (b ộ ) trong quan h ệ th ỏ a đi ề u ki ệ n ch ọ n. – Chiế u (π ) Chọ n ra m ộ t s ố c ộ t. – Tích Descartes (× ) Kế t hai quan h ệ l ạ i v ớ i nhau. – Trừ ( − ) Chứ a các b ộ c ủ a quan h ệ 1 nh ư ng không n ằ m trong quan h ệ 2. – Hộ i ( ∪ ) Chứ a các b ộ c ủ a quan h ệ 1 và các b ộ c ủ a quan h ệ 2. • Các phép toán khác: – Giao ( ∩ ), kế t ( ), chia ( / hay ÷ ), đổ i tên ( ): là các phép toán không cơ b ả n (đ ượ c suy t ừ 5 phép toán trên, tr ừ phép đ ổ i tên). • Biể u th ứ c đ ạ i s ố quan h ệ : – Là mộ t bi ể u th ứ c g ồ m các phép toán ĐSQH. – Biể u th ứ c ĐSQH đ ượ c xem nh ư m ộ t quan h ệ (không có tên) – Kế t qu ả th ự c hi ệ n các phép toán trên cũng là các quan h ệ , do đó có thể k ế t h ợ p gi ữ a các phép toán này đ ể t ạ o nên các quan h ệ m ớ i!
6. 1. ĐSQH - Phép chọ n σ Câu hỏ i 1: Cho biế t các nhân viên nam ? σ(Quan hệ ) . Biể u di ễ n cách 1 : Cú pháp : (Điề u ki ệ n 1 ∧ điề u ki ệ n 2 ∧ .) Câu hỏ i 1: σ(NhanVien) Phai=‘Nam’ . Ngoài ra, có thể bi ể u di ễ n cách 2: Cú pháp : (Quan hệ : đi ề u ki ệ n ch ọ n) Câu hỏ i 1: (NhanVien: Phai=‘Nam’) NHANVIEN Kế t qu ả phép ch ọ n MANV HOTEN NTNS PHAI NHANVIEN NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam MANV HOTEN NTNS PHAI NV002 Trầ n Đông Anh 01/08/1981 Nữ NV001 Nguyễ n T ấ n 10/12/1970 Nam Đạ t NV003 Lý Phướ c M ẫ n 02/04/1969 Nam NV003 Lý Phướ c M ẫ n 02/04/1969 Nam
7. 1. ĐSQH - Phép chọ n σ Câu hỏ i 2: Cho biế t các nhân viên nam sinh sau năm 1975 ? . Biể u di ễ n cách 1 : Câu hỏ i 2: σ(NhanVien) (Phai=‘Nam’ ∧ Year(NTNS)>1975) . Biể u di ễ n cách 2: Câu hỏ i 2: (NhanVien: Phai=‘Nam’ ∧ Year(NTNS)>1975) NHANVIEN Kế t qu ả phép ch ọ n MANV HOTEN NTNS PHAI NHANVIEN NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam MANV HOTEN NTNS PHAI NV002 Trầ n Đông Anh 01/08/1981 Nữ NV003 Lý Phướ c M ẫ n 02/04/1969 Nam (không có bộ nào th ỏ a)
8. 1. ĐSQH - Phép chiế u π Câu hỏ i 3: Cho biế t h ọ tên nhân viên và gi ớ i tính ? π (Quan hệ ) . Biể u di ễ n cách 1 : Cú pháp : Cộ t1, c ộ t2, c ộ t 3, . Câu hỏ i 3 : π (NhanVien) HOTEN, PHAI . Ngoài ra, có thể bi ể u di ễ n cách 2: Cú pháp : Quan hệ [c ộ t1,c ộ t2,c ộ t3, ] Câu hỏ i 3: NhanVien [HoTen, Phai] NHANVIEN NHANVIEN MANV HOTEN NTNS PHAI HOTEN PHAI NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam Kế t qu ả Nguyễ n T ấ n Nam Đạ t NV002 Trầ n Đông Anh 01/08/1981 Nữ phép chiế u Trầ n Đông Anh Nữ NV003 Lý Phướ c M ẫ n 02/04/1969 Nam Lý Phướ c M ẫ n Nam
9. 1. ĐSQH - Phép chiế u π Câu hỏ i 4: Cho biế t h ọ tên và ngày tháng năm sinh c ủ a các nhân viên nam? . Biể u di ễ n cách 1: Kế t qu ả phép ch ọ n Bướ c 1: Q σ(NhanVien) (còn gọ i là bi ể u th ứ c (Phai=‘Nam’) ĐSQH) đượ c đ ổ i tên π (Q) thành quan hệ Q Bướ c 2: HOTEN, NTNS . Biể u di ễ n cách 2: Câu hỏ i 4: (NhanVien: Phai=‘Nam’) [HoTen, NTNS] NHANVIEN NHANVIEN MANV HOTEN NTNS PHAI Kế t qu ả HOTEN NTNS NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam phép chiế u Nguyễ n T ấ n 10/12/1970 Đạ t NV002 Trầ n Đông Anh 01/08/1981 Nữ Lý Phướ c M ẫ n 02/04/1969 NV003 Lý Phướ c M ẫ n 02/04/1969 Nam
10. 1. ĐSQH - Phép tích Descartes × Câu hỏ i 5: Tính tích Descartes giữ a 2 quan h ệ nhân viên và phòng ban Cú pháp : Quan-hệ -1 × Quan-hệ -2× Quan-hệ -k Câu hỏ i 5 đ ượ c vi ế t l ạ i:NHANVIEN × PHONGBAN NHANVIEN PHONGBAN MANV HOTEN NTNS PHAI PHONG MAPH TENPH TRPH NV001 Nguyễ n T ấ n Ð ạ t 10/12/1970 Nam NC NC Nghiên cứ u NV001 NV002 Trầ n Ðông Anh 01/08/1981 Nữ DH DH Điề u hành NV002 NV003 Lý Phướ c M ẫ n 02/04/1969 Nam NC NHANVIEN X PHONGBAN MANV HOTEN NTNS PHAI PHONG MAPH TENPH TRPH NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam NC NC Nghiên cứ u NV001 NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam NC DH Điề u hành NV002 NV002 Trầ n Đông Anh 01/08/1981 Nữ DH NC Nghiên cứ u NV001 NV002 Trầ n Đông Anh 01/08/1981 Nữ DH DH Điề u hành NV002 NV003 Lý Phướ c M ẫ n 02/04/1969 Nam NC NC Nghiên cứ u NV001 NV003 Lý Phướ c M ẫ n 02/04/1969 Nam NC DH Điề u hành NV002
11. 1. ĐSQH - Phép kế t (Theta-Join) Câu hỏ i 6: Cho biế t mã nhân viên, h ọ tên và tên phòng mà n/v tr ự c thu ộ c. -Đặ t v ấ n đ ề: trở l ạ i ví d ụ 5, ta th ấ y n ế u th ự c hi ệ n phép tích Decartes NHANVIEN X PHONGBAN thì mỗ i nhân viên đ ề u thu ộ c 2 phòng (vì có tổ ng c ộ ng là 2 phòng ban, n ế u có 3, 4, phòng ban thì s ố dòng cho m ộ t nhân viên trong NHANVIEN X PHONGBAN sẽ là 3, 4, dòng. - Thự c t ế m ỗ i nhân viên ch ỉ thu ộ c duy nh ấ t 1 phòng ban do ràng bu ộ c khóa ngoạ i (PHONG), do đó đ ể l ấ y đ ượ c giá tr ị MAPH đúng c ủ a m ỗ i nhân viên phả i có đi ề u ki ệ n ch ọ n: NHANVIEN.PHONG = PHONGBAN.MAPH biể u di ễ n phép ch ọ n theo cách 2 ((NHANVIEN X PHONGBAN) : NHANVIEN.PHONG=PHONGBAN.MAPH) MANV HOTEN NTNS PHAI PHONG MAPH TENPH TRPH NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam NC NC Nghiên cứ u NV001 NV001 Nguyễ n T ấ n Đ ạ t 10/12/1970 Nam NC DH Điề u hành NV002 NV002 Trầ n Đông Anh 01/08/1981 Nữ DH NC Nghiên cứ u NV001 NV002 Trầ n Đông Anh 01/08/1981 Nữ DH DH Điề u hành NV002 NV003 Lý Phướ c M ẫ n 02/04/1969 Nam NC NC Nghiên cứ u NV001 NV003 Lý Phướ c M ẫ n 02/04/1969 Nam NC DH Điề u hành NV002
12. MANV,HOTEN,TENPH PHONG=MAPH PHONG=MAPH 1. ĐSQH - Phép kế t (Theta-Join) . Cách 1: σ (NHANVIEN X PHONGBAN) NHANVIEN.PHONG=PHONGBAN.MAPH . Cách 2: (NHANVIEN × PHONGBAN): (NHANVIEN.PHONG=PHONGBAN.MAPH) * Phép kế t đ ượ c đ ị nh nghĩa là phép tích Decartes và có đi ề u ki ệ n chọ n liên quan đ ế n các thu ộ c tính gi ữ a 2 quan h ệ , cú pháp : Quan-hệ -1 Quan-hệ -2 Điề u ki ệ n k ế t (Phép kế t v ớ i đk t ổ ng quát đ ượ c g ọ i là θ-kế t, θ có thể là ≠ , =, >, =, <=. Nế u đk k ế t là phép so sánh = thì g ọ i là k ế t bằ ng) Câu hỏ i 6 vi ế t l ạ i cách 1: Câu hỏ i 6 vi ế t l ạ i cách 2: (NHANVIEN PHONGBAN) [MANV,HOTEN,TENPH] π (NHANVIEN PHONGBAN)
13. PHONG=MAPH MAPH 1. ĐSQH - kế t b ằ ng, k ế t t ự nhiên Kế t b ằ ng: ( Kế t b ằ ng ) equi-join Kế t t ự nhiên: Nế u PHONG trong NHANVIEN đượ c đ ổ i thành MAPH thì ta bỏ đi 1 c ộ t MAPH thay vì phả i đ ể MAPH=MAPH, lúc này gọ i là phép k ế t tự nhiên (natural-join) ( Kế t t ự nhiên ) natural-join Hoặ c vi ế t cách khác: NHANVIEN * PHONGBAN NHANVIEN PHONGBAN NHANVIEN PHONGBAN
14. HOTEN, TENPH TRPH=MANV (ChieuDai>=300 SoNguoi>=12) HoTen, NgayDi, NgayVe MATX MATX 1. ĐSQH - Phép kế t Câu hỏi 7: Tìm họ tên các trưởng phòng của từng phòng ? Câu hỏi 8: Cho lược đồ CSDL như sau: TAIXE (MaTX, HoTen, NgaySinh, GioiTinh, DiaChi) CHUYENDI (SoCD, MaXe, MaTX, NgayDi, NgayVe, ChieuDai, SoNguoi) Cho biế t h ọ tên tài x ế , ngày đi, ngày v ề c ủ a nh ữ ng chuy ế n đi có chi ề u dài >=300km, chở t ừ 12 ng ườ i tr ở lên trong m ỗ i chuy ế n? σ CHUYENDI) Cách 1: Q ( Kết quả: Cách 2: ((CHUYENDI : ChieuDai>=300 ∧ SoNguoi>=12) π (PHONGBAN NHANVIEN) ∧ π (Q TAIXE) TAIXE) [HoTen, NgayDi, NgayVe]
15. 1. ĐSQH - Phép kế t ngoài (outer join) • Mở r ộ ng phép k ế t đ ể tránh m ấ t thông tin • Thự c hi ệ n phép k ế t và sau đó thêm vào k ế t qu ả củ a phép k ế t các b ộ c ủ a quan h ệ mà không phù hợ p v ớ i các b ộ trong quan h ệ kia. • Có 3 loạ i: – Left outer join R S – Right outer join R S – Full outer join R S • Ví dụ: In ra danh sách tấ t c ả tài x ế và s ố chuy ế n đi, mã xe mà tài xế đó lái (n ế u có)
16. 1. ĐSQH – left outer join (lấy hết tất cả bộ của quan hệ bên trái) matx • TAIXE CHUYENDI TAIXE MaTX Hoten Matx Hoten SoCD Matx Maxe TX01 Huynh Trong Tao TX01 Huynh Trong Tao CD01 TX01 8659 TX02 Nguyen Sang TX01 Huynh Trong Tao CD03 TX01 8659 TX03 Le Phuoc Long TX02 Nguyen Sang CD02 TX02 7715 TX04 Nguyen Anh Tuan TX03 Le Phuoc Long CD04 TX03 4573 TX04 Nguyen Anh Tuan Null Null Null { CHUYENDI SoCD MaTX MaXe Bộ của quan hệ TAIXE được thêm CD01 TX01 8659 Vào dù không phù hợp với kết quả CD02 TX02 7715 của quan hệ CHUYENDI CD03 TX01 8659 CD04 TX03 4573 Tương tự right outer join và full outer join (lấy cả 2)
17. 1. ĐSQH - Phép trừ , phép h ộ i, phép giao t ậ p h ợ p • Tất cả các phép toán này đều cần hai quan hệ đầu vào tương thích khả hợp, nghĩa là chúng phải thoả: – Cùng số thuộc tính. Ví dụ: R và S đều có 2 thuộc tính. – Các thuộc tính `tương ứng ’ có cùng kiểu. R S HONV TENNV HONV TENNV Vuong Quyen Le Nhan Nguyen Tung Vuong Quyen Bui Vu Phép trừ: R − S ∩ Phép hội: R ∪ S Phép giao: R S NHANVIEN (MaNV, HoTen, Phai, Luong,NTNS, Ma_NQL, MaPH) PHANCONG (MaNV, MaDA, ThoiGian)
18. 1. ĐSQH - Phép trừ , phép h ộ i, phép giao t ậ p h ợ p Phép trừ: Q = R − S = { t/ t∈R ∧ t∉S} Phép hội: Q = R ∪ S = { t/ t∈R ∨ t∈S} Phép giao: Q = R ∩ S = R – (R – S) = { t/t∈R ∧ t∈S} R R S HONV TENNV HONV TENNV Vuong Quyen Le Nhan Nguyen Tung Vuong Quyen S Bui Vu Kế t qu ả phép tr ừ Q ={Nguyen Tung} Kế t qu ả phép h ộ i Q ={Vuong Quyen, Nguyen Tung, Le Nhan, Bui Vu} Kế t qu ả phép giao Q ={Vuong Quyen} Lưu ý : Phép hội và phép giao có tính chất giao hoán
19. MANV MANV 1. ĐSQH - Phép trừ , phép h ộ i, phép giao t ậ p h ợ p Câu hỏi 9: Cho biết nhân viên không làm việc ? (Phép trừ) Cách 1: Cách 2: (NHANVIEN[MANV]) – (PHANCONG[MANV]) Câu hỏi 10: Cho biết nhân viên được phân công tham gia đề án có mã số ‘TH01’ hoặc đề án có mã số ‘TH02’? (Phép hội) ((PHANCONG: MADA=‘TH01’)[MANV]) ∪ ((PHANCONG : MADA=‘TH02’)[MANV]) Câu hỏi 11: Cho biết nhân viên được phân công tham gia cả 2 đề án ‘TH01’ và đề án ‘TH02’? (Phép giao) ((PHANCONG : MADA=‘TH01’)[MANV]) ∩ ((PHANCONG : MADA=‘TH02’)[MANV]) π (NHANVIEN) – π (PHANCONG)
20. 1. ĐSQH - Phép chia tậ p h ợ p ( / hay ÷ ) • Phép chia (R ÷ S) cần hai quan hệ đầu vào R, S thoả: – Tập thuộc tính của R là tập cha của tập thuộc tính S. Ví dụ: R có m thuộc tính, S có n thuộc tính : n ⊆ m Đị nh nghĩa: R và S là hai quan hệ , R+ và S+ lầ n l ượ t là t ậ p thuộ c tính c ủ a R và S. Đi ề u ki ệ n S+≠ ∅ là tậ p con không bằ ng củ a R+. Q là kế t qu ả phép chia gi ữ a R và S, Q+ = R+ - S+ T ←π + + (R) 1 R −S = ÷ = ∀ ∈ ∈ T ←π + + ((S ×T ) − R) Q R S {t / s S, (t, s) R} 2 R −S 1 ← − T T1 T2
21. 1. ĐSQH - Phép chia tậ p h ợ p (/ hay ÷ ) R=PHANCONG S=DEAN MANV MADA MADA Kết quả Q 001 TH001 TH001 Q= PHANCONG/DEAN 001 TH002 TH002 MANV DT001 002 TH001 002 002 TH002 002 DT001 Cho biết nhân viên làm việc cho tất cả các đề án ? (được phân 003 TH001 công tham gia tất cả các đề án) Hoặc viết Q= PHANCONG DEAN÷
22. 1. ĐSQH - Phép chia tậ p h ợ p (/ hay ÷ ) R=KETQUATHI S=MONHOC Mahv Mahv Mamh Diem Mamh Tenmh HV01 HV01 CSDL 7.0 CSDL Co so du lieu HV03 HV02 CSDL 8.5 CTRR Cau truc roi rac Q=KETQUA/MONHOC HV01 CTRR 8.5 THDC Tin hoc dai cuong HV03 CTRR 9.0 HV01 THDC 7.0 HV02 THDC 5.0 KETQUA ← KETQUATHI[Mahv, Mamh] HV03 THDC 7.5 ← HV03 CSDL 6.0 MONHOC MONHOC[Mamh] * Viế t cách khác KETQUATHI[Mahv,Mamh] /MONHOC[Mamh]
23. 1. ĐSQH – Hàm tính toán trên 1 nhóm và tính toán trên nhiề u nhóm (gom nhóm – group by) • Các hàm tính toán gồ m 5 hàm: avg(giá-tr ị ), min(giá-tr ị ), max(giá-trị ), sum(giá-tr ị ), count(giá-tr ị ). • Phép toán gom nhóm: (Group by) ℑ (E) G1 ,G2 , ,Gn F1 ( A1 ),F2 ( A2 ), ,Fn ( An ) – E là biể u th ứ c đ ạ i s ố quan h ệ – Gi là thuộ c tính gom nhóm (n ế u không có Gi nào=> không chia nhóm (1 nhóm), ngượ c l ạ i (nhi ề u nhóm) => hàm F s ẽ tính toán trên từ ng nhóm nh ỏ đ ượ c chia b ở i t ậ p thu ộ c tính này) – Fi là hàm tính toán – Ai là tên thuộ c tính
24. 1. ĐSQH – Hàm tính toán trên 1 nhóm và tính toán trên nhiề u nhóm (gom nhóm – group by) • Điể m thi cao nh ấ t, th ấ p nh ấ t, trung bình củ a môn CSDL ? ℑ σ max(Diem),min(Diem),agv(Diem) Mamh='CSDL' (KETQUATHI) • Điể m thi cao nh ấ t, th ấ p nh ấ t, trung bình củ a t ừ ng môn ? ℑ Mamh max(Diem),min(Diem),avg (Diem) (KETQUATHI)
25. 1. Ngôn ngữ truy v ấ n SQL
26. 2. NGÔN NGỮẤ TRUY V N SQL . Là ngôn ngữ chu ẩ n, có c ấ u trúc dùng đ ể truy v ấ n và thao tác trên CSDL quan hệ . . Câu truy vấ n t ổ ng quát: SELECT [DISTINCT] danh_sách_cộ t | hàm FROM danh sách các quan hệ (hay b ả ng, table) [WHERE điề u_ki ệ n] [GROUP BY danh_sách_cộ t_gom_nhóm] [HAVING điề u_ki ệ n_trên_nhóm] [ORDER BY cộ t1 ASC | DESC, cộ t2 ASC | DESC, ]
27. 2. SQL . Toán tử so sánh: o =,>, =, o BETWEEN o IS NULL, IS NOT NULL o LIKE (%,_) o IN, NOT IN o EXISTS, NOT EXISTS o SOME, ALL, ANY . Toán tử logic: AND, OR. . Các phép toán: +, - ,* , / . Các hàm xử lý ngày (DAY( )), tháng (MONTH( )), năm (YEAR( ))
28. 2. SQL . 5 hàm: COUNT( ), SUM( ), MAX( ), MIN( ), AVG( ) . Phân loạ i câu SELECT: SELECT đơ n gi ả n, SELECT có mệ nh đ ề ORDER BY, SELECT l ồ ng (câu SELECT l ồ ng câu SELECT khác), SELECT gom nhóm (GROUP BY), SELECT gom nhóm (GROUP BY)có điề u ki ệ n HAVING. Bài tậ p: Cho lượ c đ ồ CSDL “qu ả n lý đ ề án công ty” như sau NHANVIEN (MaNV, HoTen, Phai, Luong,NTNS, Ma_NQL, MaPH) PHONGBAN (MaPH, TenPH, TRPH) DEAN (MaDA, TenDA, Phong, NamThucHien) PHANCONG (MaNV, MaDA, ThoiGian)
29. MANV HOTEN NTNS PHAI MA_NQL MaPH LUONG NHANVIEN 001 Vuong Ngoc Quyen 22/10/1957 Nu QL 3.000.000 002 Nguyen Thanh Tung 09/01/1955 Nam 001 NC 2.500.000 003 Le Thi Nhan 18/12/1960 Nu 001 DH 2.500.000 004 Dinh Ba Tien 09/01/1968 Nam 002 NC 2.200.000 005 Bui Thuy Vu 19/07/1972 Nam 003 DH 2.200.000 006 Nguyen Manh Hung 15/09/1973 Nam 002 NC 2.000.000 007 Tran Thanh Tam 31/07/1975 Nu 002 NC 2.200.000 008 Tran Hong Minh 04/07/1976 Nu 004 NC 1.800.000 PHANCONG MANV MADA THOIGIAN DEAN 001 TH001 30,0 MADA TENDA PHONG NamThucHien 001 TH002 12,5 TH001 Tin hoc hoa 1 NC 2002 002 TH001 10,0 002 TH002 10,0 TH002 Tin hoc hoa 2 NC 2003 002 DT001 10,0 DT001 Dao tao 1 DH 2004 002 DT002 10,0 DT002 Dao tao 2 DH 2004 003 TH001 37,5 PHONGBAN 004 DT001 22,5 MAPH TENPH TRPH 004 DT002 10,0 QL Quan Ly 001 006 DT001 30,5 DH Dieu Hanh 003 007 TH001 20,0 NC Nghien Cuu 002 007 TH002 10,0 008 DT002 12,5
30. 2. SQL – BETWEEN, ORDER BY, IS NULL Câu hỏ i 13: Sử d ụ ng =,>,>=, Danh sách các nhân viên sinh trong khoả ng từ năm 1978 đ ế n 1983? Select MaNV, HoTen From NhanVien where Year(NTNS)>=1978 AND Year(NTNS)<=1983 Câu hỏ i 14: Sử d ụ ng BETWEEN, ORDER BY. Danh sách các nhân viên sinh trong khoả ng t ừ năm 1978 đ ế n 1983? S ắ p x ế p theo m ứ c l ươ ng gi ả m dầ n. Select * From NhanVien where Year(NTNS) BETWEEN 1978 and 1983 ORDER BY Luong DESC Câu hỏ i 15: Sử d ụ ng IS NULL. Cho biế t nh ữ ng nhân viên không có ng ườ i quả n lý tr ựế c ti p? (không ch ịựả u s qu n lý tr ựếủườ c ti p c a ng i nào) Select MaNV, HoTen, NTNS, Ma_NQL from NhanVien where Ma_NQL is Null
31. 2. SQL - SO SÁNH IN & NOT IN Câu hỏ i 16: Sử d ụ ng Is Not Null. Cho biế t nh ữ ng nhân viên có ng ườ i qu ả n lý trự c ti ế p?Thông tin hi ể n th ị g ồ m: mã nhân viên, h ọ tên, mã ng ườ i qu ả n lý. Select MaNV, HoTen, Ma_NQL from NhanVien where Ma_NQL is not Null Câu hỏ i 17: Sử d ụ ng IN (so sánh v ớ i m ộ t t ậ p h ợ p giá tr ị c ụ th ể ). Cho biế t họ tên nhân viên thu ộ c phòng ‘NC’ ho ặ c phòng ‘DH’? Select DISTINCT Hoten From NhanVien where MaPH in (‘NC’,’DH’) Câu hỏ i 18: Sử d ụ ng IN (so sánh v ớ i m ộ t t ậ p h ợ p giá tr ị ch ọ n t ừ câu SELECT khác). Cho biế t h ọ tên nhân viên thu ộ c phòng ‘NC’ ho ặ c phòng ‘DH’? Select Hoten from NhanVien where MaPH in (Select MaPH from PHONGBAN where MaPH=‘NC’ OR MaPH=‘DH’)
32. 2. SQL – SO SÁNH IN & NOT IN Câu hỏ i 19 (tt): Cho biế t mã s ố , h ọ tên, ngày tháng năm sinh c ủ a nh ữ ng nhân viên đã tham gia đề án? Select MaNV, HoTen, NTNS from NhanVien where MaNV in (Select MaNv From PhanCong) Câu hỏ i 20: Sử d ụ ng NOT IN. Cho biế t mã s ố , h ọ tên, ngày tháng năm sinh củ a nh ữ ng nhân viên không tham gia đ ề án nào? Gợ i ý cho m ệ nh đ ề NOT IN: thự c hi ệ n câu truy v ấ n “tìm nhân viên có tham gia đề án (d ự a vào b ả ng PhanCong)”, sau đó l ấ y ph ầ n bù. Select MaNV, HoTen, NTNS from NhanVien where MaNV not in (Select MaNv From PhanCong) Câu hỏ i 21 (tt): Cho biế t tên phòng ban không ch ủ trì các đ ề án tri ể n khai năm 2005? Gợ i ý: thự c hi ệ n câu truy v ấ n “tìm phòng ban ch ủ trì các đ ề án triể n khai năm 2005”, sau đó l ấ y ph ầ n bù. Select TenPH from PhongBan where MaPH not in (Select DISTINCT Phong from DEAN where NamThucHien=2005)
33. 2. SQL – SO SÁNH LIKE Câu hỏ i 22: so sánh chuỗ i = chu ỗ i. Liệ t kê mã nhân viên, ngày tháng năm sinh, mứ c l ươ ng c ủ a nhân viên có tên “Nguy ễ n T ườ ng Linh”? Select MaNV, NTNS, Luong from NhanVien where HoTen = ‘Nguyễ n T ườ ng Linh’ Câu hỏ i 23: Sử d ụ ng LIKE (%: thay th ế 1 chu ỗ i ký t ự ). Tìm nhữ ng nhân viên có họ Nguy ễ n. Select MaNV, HoTen from NhanVien where HoTen like ‘Nguyễ n %’ Câu hỏ i 24 (tt): Tìm nhữ ng nhân viên có tên Lan. Select MaNV, HoTen from NhanVien where HoTen like ‘% Lan’ Câu hỏ i 25 (tt): Tìm nhữ ng nhân viên có tên lót là “Văn”. Select MaNV, HoTen from NhanVien where HoTen like ‘% Văn %’ Câu hỏ i 26: Sử d ụ ng LIKE ( _: thay th ế 1 ký t ự b ấ t kỳ). Tìm nhữ ng nhân viên tên có tên ‘Nguyễ n La_’ (ví d ụ Lam, Lan) Select MaNV, HoTen from NhanVien where HoTen like ‘Nguyễ n La_’
34. 2. SQL – HÀM COUNT,SUM,MAX,MIN,AVG a) Sử d ụ ng các hàm COUNT, SUM, MIN, MAX, AVG trên 1 nhóm lớ n (trên toàn b ộ quan h ệ ): – Câu hỏ i 27: Tính s ố nhân viên c ủ a công ty. Select COUNT(MaNV) as SoNV from NhanVien – Câu hỏ i 28: Tính s ố l ượ ng nhân viên qu ả n lý tr ự c ti ế p nhân viên khác. Select COUNT (DISTINCT Ma_NQL) from NhanVien – Câu hỏ i 29: Tìm m ứ c l ươ ng l ớ n nh ấ t, m ứ c l ươ ng trung bình, tổ ng l ươ ng c ủ a công ty. Select MAX(Luong), AVG(Luong), SUM(Luong) from NhanVien – Câu hỏ i 30: Cho bi ế t nhân viên có m ứ c l ươ ng l ớ n nh ấ t. Select HoTen from NhanVien Where Luong = (Select MAX(Luong) from NhanVien )
35. 2. SQL – MỆỀ NH Đ GROUP BY Câu hỏ i 31: Cho biế t nhân viên có m ứ c l ươ ng trên m ứ c l ươ ng trung bình củ a công ty. Select HoTen from NhanVien where Luong > (Select AVG(Luong) from NhanVien ) b) Sử d ụ ng các hàm COUNT, SUM, MIN, MAX, AVG trên từ ng nhóm nh ỏ : m ệ nh đ ề GROUP BY – Chia các dòng thành các nhóm nhỏ dựa trên tập thuộc tính chia nhóm. – Thực hiện các phép toán trên nhóm như: Count (thực hiện phép đếm), Sum (tính tổng), Min(lấy giá trị nhỏ nhất), Max(lấy giá trị lớn nhất), AVG (lấy giá trị trung bình).
36. 2. SQL – MỆỀ NH Đ GROUP BY Quan hệ NV Chia các dòng thành các Q S nhóm dựa trên tập thuộc a 10 a 2 tính chia nhóm b 9 b 5 Q Count(S) Tương tự cho các c 10 a 2 hàm SUM, MIN, c 8 b 2 MAX, AVG c 6 c 5 nh ó m c 4 d 3 c 10 d 16 d 18 Câu SQL: d 50 Select Q, count(S) Các thuộc tính GROUP BY: Q From NV Group by Q
37. 2. SQL – MỆỀ NH Đ GROUP BY Câu hỏ i 32: Cho biế t s ố l ượ ng nhân viên theo t ừ ng phái? Do cộ t phái có 2 giá tr ị “nam” và “n ữ ”, tr ườ ng h ợ p này ta chia bả ng NhanVien thành 2 nhóm nh ỏ . Thu ộ c tính chia nhóm là thu ộ c tính “Phai”. Select Phai, count(Manv) as SoNV from NhanVien Group by Phai Câu hỏ i 33: Cho biế t s ố l ượ ng nhân viên theo t ừ ng phòng? Do cộ t MaPH có 3 giá tr ị “NC” và “DH” và “QL”, tr ườ ng h ợ p này ta chia bả ng nhân viên thành 3 nhóm nh ỏ . Thu ộ c tính chia nhóm là thu ộ c tính “MaPH”. Select MaPH, count(Manv) from NhanVien Group by MaPH Tươự ng t : cho bi ếổươủỗ t t ng l ng c a m i phòng, cho bi ếứươ t m c l ng th ấ p nhấủừ t c a t ng phòng, m ứươ c l ng cao nh ấứươ t, m c l ng trung bình c ủừ a t ng phòng
38. 2. SQL – MỆỀ NH Đ GROUP BY Câu hỏ i 34: Cho biế t tên phòng và s ố l ượ ng nhân viên theo t ừ ng phòng? Giố ng câu 29 nh ư ng b ổ sung thêm b ả ng PhongBan đ ể l ấ y tên phòng. Thuộ c tính chia nhóm là (TenPH) thay cho MaPH. Select TenPH, count(Manv) as SoLuongNV From NhanVien n, PhongBan p Where n.MaPh=p.MaPH Group by TenPH Câu hỏ i 35: Vớ i m ỗ i phòng, cho bi ế t s ố l ượ ng nhân viên theo t ừ ng phái? Do cộ t MaPH có 3 giá tr ị “NC” và “DH” và “QL”, m ỗ i phòng chia nhỏ theo t ừ ng phái: 2 nhóm “Nam” và “N ữ ”, tr ườ ng h ợ p này ta chia bả ng nhân viên thành 6 nhóm nh ỏ . Nh ư v ậ y, t ậ p thu ộ c tính chia nhóm cho câu truy vấ n là (Phong, Phai). Select MaPH, Phai, count(Manv) from NhanVien Group by Phong, Phai
39. 2. SQL – MỆỀ NH Đ GROUP BY Câu hỏ i 36: Đế m s ố đ ề án c ủ a t ừ ng nhân viên tham gia? - Do cộ t MaNV có 7 giá tr ị “NV001”, ”NV008” (không có nhân viên “005”), trườ ng h ợ p này ta chia b ả ng PhanCong thành 7 nhóm nh ỏ . Vớ i m ỗ i nhóm nh ỏ (MaNV), ta đ ế m s ố đ ề án (count(MADA)) tham gia. Thuộ c tính chia nhóm là thu ộ c tính “MaNV”. - Tươ ng t ự : tính t ổ ng s ố gi ờ làm vi ệ c c ủ a m ỗ i nhân viên (SUM), th ờ i gian làm việ c th ấ p nh ấ t c ủ a m ỗ i nhân viên (MIN), th ờ i gian làm vi ệ c lớ n nh ấ t c ủ a m ỗ i nhân viên (MAX), th ờ i gian làm vi ệ c trung bình, Select MaNV, count(MaDA) as SoDATG From PhanCong Group by MaNV Câu hỏ i 37: Cho biế t mã, tên nhân viên và s ố đ ề án mà n/v đã tham gia? Select n.MaNV, HoTen, count(MaDA) as SoDATG From PhanCong pc, NhanVien n where pc.manv=n.manv Group by MaNV, HoTen
40. 2. SQL – MỆỀ NH Đ HAVING  Lọc kết quả theo điều kiện, sau khi đã gom nhóm  Điều kiện của HAVING là điều kiện về các hàm tính toán trên nhóm (Count, Sum, Min, Max, AVG) và các thuộc tính trong danh sách GROUP BY. Câu hỏ i 38: Cho biế t nh ữ ng nhân viên tham gia t ừ 2 đ ề án trở lên? Select MaNV, count(MaDA) as SoDATG From PhanCong Group by MaNV Having count(MaDA) >=2 Câu hỏ i 39: Cho biế t mã phòng ban có trên 4 nhân viên? Select MaPH, count(Manv) from NhanVien Group by MaPH Having count(Manv)>4
41. 1. Ràng buộ c toàn v ẹ n
42. 3. RÀNG BUỘẸ C TOÀN V N • RBTV có bố i c ả nh trên m ộ t quan h ệ – Ràng buộ c mi ề n giá tr ị – Ràng buộ c liên b ộ – Ràng buộ c liên thu ộ c tính • RBTV có bố i c ả nh trên nhi ề u quan h ệ – Ràng buộ c liên thu ộ c tính liên quan h ệ – Ràng buộ c khóa ngo ạ i (tham chi ế u) – Ràng buộ c liên b ộ liên quan h ệ – Ràng buộ c do thu ộ c tính t ổ ng h ợ p (Count, Sum)
43. 3. RBTV – CÁC ĐẶƯ C TR NG Các đặc trưng của 1 RBTV: Nội dung : phát biểu bằng ngôn ngữ hình thức (phép tính quan hệ, đại số quan hệ, mã giả, ) Bối cảnh: là những quan hệ có khả năng làm cho RBTV bị vi phạm. Tầm ảnh hưởng: là bảng 2 chiều, xác định các thao tác ảnh hưởng (+) và thao tác không ảnh hưởng (­) lên các quan hệ nằm trong bối cảnh.
44. 3. RBTV – BẢ NG T Ầ M Ả NH H ƯỞ NG Bả ng t ầ m ả nh h ưở ng c ủ a RBTV có d ạ ng nh ư sau: Thêm Xóa Sử a Quan hệ 1 + + - (*) Quan hệ n - - +(A) Ký hiệu + : Có thể gây ra vi phạm RBTV Ký hiệu ­ : Không thể gây ra vi phạm RBTV Ký hiệu +(A) : Có thể gây ra vi phạm RBTV khi thao tác trên thuộc tính A Ký hiệu –(*) : Không thể gây ra vi phạm RBTV do thao tác không thực hiện được
45. 3. RBTV – TRÊN BỐẢỆ I C NH LÀ 1 QUAN H 3.1. Ràng buộ c toàn v ẹ n mi ề n giá tr ị • Xét lượ c đ ồ quan h ệ – NHANVIEN (MANV, HONV, TENLOT, TENNV, NGSINH, PHAI, DCHI, MA_NQL, PHONG, MLUONG) Câu hỏ i 40: Phái củ a nhân viên ch ỉ có th ể là ‘Nam’ ho ặ c ‘N ữ ’ – Nộ i dung: ∀∀n ∈ NHANVIEN: n.PHAI IN {‘Nam’,’Nữ ’} – Bố i c ả nh: quan hệ NHANVIEN – Bả ng t ầ m ả nh h ưở ng (TAH): Thêm Xóa Sử a NHANVIEN +(PHAI) - +(PHAI)
46. 3. RBTV – TRÊN BỐẢỆ I C NH LÀ 1 QUAN H 3.2. Ràng buộ c toàn v ẹ n liên thu ộ c tính: ràng buộ c giữ a các thu ộ c tính trong cùng m ộ t quan h ệ . Xét lượ c đ ồ quan h ệ DEAN (MADA, TENDA, DDIEM_DA, PHONG, NGBD_DK, NGKT_DK) Câu hỏ i 41: Vớ i m ọ i đ ề án, ngày b ắ t đ ầ u d ự ki ế n (NGBD_DK) phả i nh ỏ h ơ n ngày k ế t thúc d ự ki ế n (NGKT_DK) Nộ i dung: ∀d ∈ DEAN, d.NGBD_DK <= d.NGKT_DK
47. 3. RBTV – TRÊN BỐẢỆ I C NH LÀ 1 QUAN H – Bố i c ả nh: quan hệ DEAN – Bả ng t ầ m ả nh h ưở ng: Thêm Xóa Sử a DEAN + (NGBD_DK, - +(NGBD_DK, NGKT_DK) NGKT_DK) 3.3. Ràng buộ c toàn v ẹ n liên b ộ : ràng buộ c gi ữ a các bộ giá tr ị trong cùng m ộ t quan h ệ . Cho lượ c đ ồ quan h ệ : NHANVIEN(MaNV, HoTen, HESO, MucLuong) Câu hỏ i 42: các nhân viên có cùng hệ s ố l ươ ng thì có cùng mứ c l ươ ng.
48. 3. RBTV – TRÊN BỐẢỆ I C NH LÀ 1 QUAN H – Nộ i dung: • ∀n1,n2 ∈ NHANVIEN: n1.HESO=n2.HESO thì (n1.MUCLUONG = n2.MUCLUONG) – Bố i c ả nh: quan hệ NHANVIEN – Bả ng t ầ m ả nh h ưở ng: Thêm Xóa Sử a NHANVIEN + (HESO, - +(HESO, MucLuong) MucLuong)
49. 3. RBTV – BỐẢỀỆ I C NH NHI U QUAN H 3.4. Ràng buộ c toàn v ẹ n tham chi ế u • RBTV tham chiếu còn gọi là ràng buộc phụ thuộc tồn tại hay ràng buộc khóa ngoại. • Xét lược đồ quan hệ PHONGBAN (MAPH, TENPH, TRPH, NGNC) NHANVIEN (MANV, HOTEN, NTNS, PHAI, MA_NQL, MAPH, LUONG) Câu hỏi 43: Mỗ i tr ưở ng phòng ph ả i là m ộ t nhân viên trong công ty. – Nội dung: ∀p ∈ PHONGBAN, ∃n ∈ NHANVIEN: p.TRPH= n.MANV Hay: PHONGBAN[TRPH] ⊆ NHANVIEN[MANV])
50. 3. RBTV – BỐẢỀỆ I C NH NHI U QUAN H –Bố i c ả nh: NHANVIEN, PHONGBAN –Bả ng t ầ m ả nh h ưở ng: Thêm Xóa Sử a PHONGBAN +(TRPH) - +(TRPH) NHANVIEN - + - (*) 3.5. Ràng buộ c toàn v ẹ n liên thu ộ c tính liên quan h ệ Xét các lượ c đ ồ quan h ệ : DATHANG(MADH, MAKH, NGAYDH) GIAOHANG(MAGH, MADH, NGAYGH)
51. 3. RBTV – BỐẢỀỆ I C NH NHI U QUAN H Câu hỏ i 44: Ngày giao hàng không đượ c tr ướ c ngày đ ặ t hàng - Nộ i dung: ∀g∈GIAO_HANG, ∃!d∈DAT_HANG:d.MADH= g.MADH ∧ d.NGAYDH >= g.NGAYGH – Bố i c ả nh: DATHANG, GIAOHANG – Bả ng t ầ m ả nh h ưở ng: Thêm Xóa Sử a DATHANG - - + (ngaydh) GIAOHANG +(ngaygh) - + (ngaygh)
52. 3. RBTV – BỐẢỀỆ I C NH NHI U QUAN H 3.6. Ràng buộ c toàn v ẹ n liên b ộ , liên quan h ệ • RBTV liên bộ , liên quan h ệ là đi ề u ki ệ n gi ữ a các b ộ trên nhi ề u quan hệ khác nhau. • Xét các lượ c đ ồ quan h ệ – PHONGBAN (MAPH, TENPH, TRPH, NGNC) – DIADIEM_PHG (MAPH, DIADIEM) Câu hỏ i 45: Mỗ i phòng ban ph ả i có ít nh ấ t m ộ t đ ị a đi ể m phòng - Nộ i dung • Mỗ i phòng ban ph ả i có ít nh ấ t m ộ t đ ị a đi ể m phòng ∀∀p ∈ PHONGBAN, ∃d ∈ DIADIEM_PHG: p.MAPH = d.MAPH
53. 3. RBTV – BỐẢỀỆ I C NH NHI U QUAN H – Bố i c ả nh: PHONGBAN, DIADIEM_PHG – Bả ng t ầ m ả nh h ưở ng: Thêm Xóa Sử a PHONGBAN + - - DIADIEM_PHG - + + (MAPH) 3.7. Ràng buộ c toàn v ẹ n do thu ộ c tính t ổ ng h ợ p PXUAT(SOPHIEU, NGAY, TONGTRIGIA) CTIET_PX(SOPHIEU, MAHANG, SL, DG) Câu hỏ i 46: Tổ ng tr ị giá c ủ a 1 phi ế u xu ấ t ph ả i b ằ ng t ổ ng trị giá các chi ti ế t xu ấ t.
54. 3. RBTV – BỐẢỀỆ I C NH NHI U QUAN H Nộ i dung ∀ ∀px∈PXUAT, Σ px.TONGTRIGIA = (ct ∈ CTIET_PX ∧ ct.SOPHIEU = px.SOPHIEU) (ct.SL*ct.DG) – Bố i c ả nh: PXUAT,CTIET_PX – Bả ng t ầ m ả nh h ưở ng: Thêm Xóa Sử a PXUAT -(*) - + (tongtrigia) CTIET_PX +(sl,dg) + + (sl,dg) -(*) Ở th ờ i đi ể m thêm m ộ t b ộ vào PXUAT, giá trị b ộ đó t ạ i TONGTRIGIA là tr ố ng.
55. GIẢ I BÀI TẬ P