Tài liệu bài giảng Cơ sở dữ liệu

Ví dụ: Cho Q (A, B, C, D, E, I), F={ACD → EBI, CE → AD} Bước 1: Q có hai khóa là {ACD, CE} Bước 2: Phân rã vế phải của các phụ thuộc hàm trong F, ta có: F={ACD → E, ACD → B, ACD → I, CE → A, CE → D} Bước 3: Mọi phụ thuộc hàm trong F đều có vế trái là một siêu khóa Vậy Q đạt dạng chuẩn BC.

pdf179 trang | Chia sẻ: vutrong32 | Ngày: 20/10/2018 | Lượt xem: 99 | Lượt tải: 0download
Bạn đang xem trước 20 trang tài liệu Tài liệu bài giảng Cơ sở dữ liệu, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
n Ngoc Linh Nu Tay Ninh K11 57  Phép chiếu lên 1 tập thuộc tính X={Hoten,Noisinh} của quan hệ HOCVIEN HOCVIEN[Hoten, Noisinh] = {(‘Ha Duy Lap’, ‘Nghe An’),(‘Tran Ngoc Han’, ‘Kien Giang’),(‘Tran Ngoc Linh’,’Tay Ninh’)} 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 58  Chiếu của một bộ lên tập thuộc tính: dùng để trích chọn các giá trị cụ thể của bộ giá trị đó theo các thuộc tính được chỉ ra trong danh sách thuộc tính của một quan hệ.  Ký hiệu: chiếu của một bộ giá trị t lên tập thuộc tính X của quan hệ R là tR[X] hoặc t[X]. Nếu X có 1 thuộc tính tR.X  Ví dụ: cho quan hệ HOCVIEN với tập thuộc tính HOCVIEN+={Mahv,Hoten,Gioitinh,Noisinh,Malop}, chứa 3 bộ giá trị hv1,hv2 và hv3 59  Phép chiếu 1 bộ lên 1 thuộc tính ◦ hv1[Hoten] = (‘Ha Duy Lap’) 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 hv1= hv2= hv3= 60  Phép chiếu 1 bộ lên 1 tập thuộc tính ◦ tập thuộc tính X={Hoten, Gioitinh} ◦ hv2[X] = (‘Tran Ngoc Han’,’Nu’) ◦ hv1 = ◦ hv2 = ◦ hv3 = 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 61 2.7.1 Siêu khóa (super key) 2.7.2 Khóa (key) 2.7.3 Khóa chính (primary key) 2.7.4 Khóa tương đương 2.7.5 Khóa ngoại (foreign key) 62  Siêu khóa : là một tập con các thuộc tính của Q+ mà giá trị của chúng có thể phân biệt 2 bộ khác nhau trong cùng một thể hiện TQ bất kỳ. Nghĩa là:  t1, t2  TQ, t1[K] t2[K] K là siêu khóa của Q.  Một quan hệ có ít nhất một siêu khóa (Q+) và có thể có nhiều siêu khóa. 63  Ví dụ: các siêu khóa của quan hệ HOCVIEN là: {Mahv};{Mahv,Hoten};{Hoten};{Noisinh,Hoten} 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 64 Khóa : K là khóa của quan hệ R, thỏa mãn 2 điều kiện:  K là một siêu khóa.  K là siêu khóa “nhỏ nhất” (chứa ít thuộc tính nhất và khác rỗng) nghĩa là ¬K1 K, K1  sao cho K1 là siêu khóa.  Thuộc tính tham gia vào một khóa gọi là thuộc tính khóa, ngược lại là thuộc tính không khóa. 65  Ví dụ: các siêu khóa của quan hệ HOCVIEN là: {Mahv};{Mahv,Hoten};{Hoten};{Hoten,Gioitinh}; {Noisinh,Hoten};{Mahv,Hoten,Gioitinh,Noisinh} => thì khóa của quan hệ HOCVIEN có thể là {Mahv}; {Hoten}  Ví dụ: khóa của quan hệ GIANGDAY (Malop, Mamh, Magv, HocKy, Nam) là K={Malop,Mamh}. Thuộc tính khóa sẽ là: Mamh,Malop. Thuộc tính không khóa sẽ là Magv, HocKy, Nam. 66  Khi cài đặt trên một DBMS cụ thể, nếu quan hệ có nhiều hơn một khóa, ta chỉ được chọn một và gọi là khóa chính  Ký hiệu: các thuộc tính nằm trong khóa chính khi liệt kê trong quan hệ phải được gạch dưới.  Ví dụ: ◦ HOCVIEN (Mahv,Hoten,Gioitinh,Noisinh,Malop) ◦ GIANGDAY(Mamh,Malop,Magv,Hocky,Nam) 67  Các khóa còn lại (không được chọn làm khóa chính) gọi là khóa tương đương.  Ví dụ: trong hai khóa {Mahv},{Hoten} thì khóa chính là {Mahv}, khóa tương đương là {Hoten} 68  Cho R(U), S(V). K1U là khóa chính của R,K2V  Ta nói K2 là khóa ngoại của S tham chiếu đến khóa chính K1 của R nếu thỏa các điều kiện sau: ◦ K1 và K2 có cùng số lượng thuộc tính và ngữ nghĩa của các thuộc tính trong K1 và K2 cũng giống nhau. ◦ Giữa R và S tồn tại mối quan hệ 1-n trên K1 và K2, ◦ s  S, !r  R sao cho r.K1=s.K2 69  Ví dụ, cho 2 quan hệ LOP (Malop,Tenlop,Siso,Khoahoc) HOCVIEN (Mahv,Hoten,Gioitinh,Noisinh,Malop)  Thuộc tính Malop trong quan hệ LOP là khóa chính của quan hệ LOP. Thuộc tính Malop trong quan hệ HOCVIEN là khóa ngoại, tham chiếu đến Malop trong quan hệ LOP 70 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 LOP Malop Tenlop Trglop Siso Magvcn K11 Lop 1 khoa 1 K1106 11 GV07 K12 Lop 2 khoa 1 K1205 12 GV09 K13 Lop 3 khoa 1 K1305 12 GV14 71  Lược đồ quan hệ nhằm mục đích mô tả cấu trúc của một quan hệ và các mối liên hệ giữa các thuộc tính trong quan hệ đó.  Cấu trúc của một quan hệ là tập thuộc tính hình thành nên quan hệ đó.  Một lược đồ quan hệ gồm một tập thuộc tính của quan hệ kèm theo một mô tả để xác định ý nghĩa và mối liên hệ giữa các thuộc tính 72  Lược đồ quan hệ được đặc trưng bởi: ◦ Một tên phân biệt ◦ Một tập hợp hữu hạn các thuộc tính (A1, , An)  Ký hiệu của lược đồ quan hệ Q gồm n thuộc tính (A1, A2, ... An) là : ◦ Q(A1, A2, ..., An) 73  HOCVIEN(Mahv,Hoten,Gioitinh,Noisinh,Malop)  Tân từ: mỗi học viên có một mã học viên để phân biệt với các học viên khác. Cần lưu trữ họ tên, giới tính, nơi sinh và thuộc lớp nào. 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 74  Là tập hợp gồm các lược đồ quan hệ và các mối liên hệ giữa chúng trong cùng một hệ thống quản lý. Các CSDL Hệ Quản Trị CSDL Các quan hệ 75 HOCVIEN (MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP) Tân từ: mỗi học viên phân biệt với nhau bằng mã học viên, lưu trữ họ tên, ngày sinh, giới tính, nơi sinh, thuộc lớp nào. LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN) Tân từ: mỗi lớp gồm có mã lớp, tên lớp, học viên làm lớp trưởng của lớp, sỉ số lớp và giáo viên chủ nhiệm. KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA) Tân từ: mỗi khoa cần lưu trữ mã khoa, tên khoa, ngày thành lập khoa và trưởng khoa (cũng là một giáo viên thuộc khoa). MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA) Tân từ: mỗi môn học cần lưu trữ tên môn học, số tín chỉ lý thuyết, số tín chỉ thực hành và khoa nào phụ trách. DIEUKIEN (MAMH, MAMH_TRUOC) Tân từ: có những môn học học viên phải có kiến thức từ một số môn học trước. 76 GIAOVIEN(MAGV,HOTEN,HOCVI,HOCHAM,GIOITINH,NGSINH,NGVL, HESO, MUCLUONG, MAKHOA) Tân từ: mã giáo viên để phân biệt giữa các giáo viên, cần lưu trữ họ tên, học vị, học hàm, giới tính, ngày sinh, ngày vào làm, hệ số, mức lương và thuộc một khoa. GIANGDAY(MALOP,MAMH,MAGV,HOCKY, NAM,TUNGAY,DENNGAY) Tân từ: mỗi học kỳ của năm học sẽ phân công giảng dạy: lớp nào học môn gì do giáo viên nào phụ trách. KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA) Tân từ: lưu trữ kết quả thi của học viên: học viên nào thi môn học gì, lần thi thứ mấy, ngày thi là ngày nào, điểm thi bao nhiêu và kết quả là đạt hay không đạt. 77 Chương 3: Đại số quan hệ 78  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ệ 79 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 ( ) Nhân 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!     80  Biểu diễn cách 1 :   (Quan hệ) (Điều kiện 1  điều kiện 2  .) Cú pháp :  Ngoài ra, có thể biểu diễn cách 2:  (NhanVien) Câu hỏi 1: Cú pháp : (Quan hệ: điều kiện chọn) Câu hỏi 1: Phai=‘Nam’ (NhanVien: Phai=‘Nam’) NHANVIEN MANV HOTEN NTNS PHAI NV001 Nguyễn Tấn Đạt 10/12/1970 Nam NV002 Trần Đông Anh 01/08/1981 Nữ NV003 Lý Phước Mẫn 02/04/1969 Nam NHANVIEN MANV HOTEN NTNS PHAI NV001 Nguyễn Tấn Đạt 10/12/1970 Nam NV003 Lý Phước Mẫn 02/04/1969 Nam Kết quả phép chọn Câu hỏi 1: Cho biết các nhân viên nam ? 81  Biểu diễn cách 1 :   Biểu diễn cách 2:  (NhanVien) (Phai=‘Nam’  Year(NTNS)>1975) (NhanVien: Phai=‘Nam’  Year(NTNS)>1975) NHANVIEN MANV HOTEN NTNS PHAI NV001 Nguyễn Tấn Đạt 10/12/1970 Nam NV002 Trần Đông Anh 01/08/1981 Nữ NV003 Lý Phước Mẫn 02/04/1969 Nam NHANVIEN MANV HOTEN NTNS PHAI Kết quả phép chọn Câu hỏi 2: Cho biết các nhân viên nam sinh sau năm 1975 ? (không có bộ nào thỏa) Câu hỏi 2: Câu hỏi 2: 82  Biểu diễn cách 1 : (Quan hệ) Cột1, cột2, cột 3, . Cú pháp :  Ngoài ra, có thể biểu diễn cách 2: (NhanVien) Câu hỏi 3 : Cú pháp : Quan hệ [cột1,cột2,cột3,] Câu hỏi 3: HOTEN, PHAI NhanVien [HoTen, Phai] NHANVIEN MANV HOTEN NTNS PHAI NV001 Nguyễn Tấn Đạt 10/12/1970 Nam NV002 Trần Đông Anh 01/08/1981 Nữ NV003 Lý Phước Mẫn 02/04/1969 Nam Kết quả phép chiếu Câu hỏi 3: Cho biết họ tên nhân viên và giới tính ?    NHANVIEN HOTEN PHAI Nguyễn Tấn Đạt Nam Trần Đông Anh Nữ Lý Phước Mẫn Nam 83  Biểu diễn cách 1:  Biểu diễn cách 2: Câu hỏi 4: (NhanVien: Phai=‘Nam’) [HoTen, NTNS] NHANVIEN MANV HOTEN NTNS PHAI NV001 Nguyễn Tấn Đạt 10/12/1970 Nam NV002 Trần Đông Anh 01/08/1981 Nữ NV003 Lý Phước Mẫn 02/04/1969 Nam Kết quả 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?   (NhanVien) (Phai=‘Nam’) Bước 1: Q Kết quả phép chọn (còn gọi là biểu thức ĐSQH) được đổi tên thành quan hệ Q Bước 2: (Q)  HOTEN, NTNS NHANVIEN HOTEN NTNS Nguyễn Tấn Đạt 10/12/1970 Lý Phước Mẫn 02/04/1969 84 Quan-hệ-1 Cú pháp : Câu hỏi 5: Tính tích Descartes giữa 2 quan hệ nhân viên và phòng ban  Quan-hệ-2  PHONGBAN MAPH TENPH TRPH NC Nghiên cứu NV001 DH Điều hành NV002 NHANVIEN PHONGBAN   Quan-hệ-k Câu hỏi 5 được viết lại: Nam Nữ Nam 02/04/1969 01/08/1981 10/12/1970 NTNS HOTEN MANV Nguyễn Tấn Ðạt NV001 Lý Phước Mẫn NV003 Trần Ðông Anh NV002 NHANVIEN PHAI PHONG DH NC 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 85 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. ((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 -Đặ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 (Theta-Join) 86  Cách 1: (NHANVIEN  PHONGBAN): (NHANVIEN.PHONG=PHONGBAN.MAPH)  (NHANVIEN X PHONGBAN) NHANVIEN.PHONG=PHONGBAN.MAPH  Cách 2: Quan-hệ-1 * 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ệ-2 (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: MANV,HOTEN,TENPH (NHANVIEN PHONG=MAPH PHONGBAN) Câu hỏi 6 viết lại cách 2: (NHANVIEN PHONG=MAPH PHONGBAN) [MANV,HOTEN,TENPH] Điều kiện kết (Theta-Join) 87 ( Kết bằng ) NHANVIEN PHONG=MAPH PHONGBAN Nếu PHONG trong NHANVIEN được đổi thành MAPH thì ta bỏ đi 1 cột MAPH thay vì phải để MAPH=MAPH ( Kết tự nhiên ) NHANVIEN PHONGBAN Kết bằng: Kết tự nhiên: (natural-join) Hoặc viết cách khác: NHANVIEN * PHONGBAN equi-join natural-join NHANVIEN MAPH PHONGBAN 88 Câu hỏi 7: Tìm họ tên các trưởng phòng của từng phòng ? HOTEN, TENPH (PHONGBAN TRPH=MANV NHANVIEN) 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? HoTen, NgayDi, NgayVe (Q MATX TAIXE)  (ChieuDai>=300  SoNguoi>=12) (CHUYENDI) Q Kết quả: ((CHUYENDI : ChieuDai>=300  SoNguoi>=12) Cách 1: Cách 2: TAIXE) [HoTen, NgayDi, NgayVe] MATX 89  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 (giữ lại các bộ của quan hệ trái) ◦ Right outer join R S (giữ lại các bộ của quan hệ phải) ◦ Full outer join R S (giữ lại các bộ của quan hệ trái, phải)  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ó) 90  TAIXE CHUYENDI matx TAIXE MaTX Hoten TX01 Huynh Trong Tao TX02 Nguyen Sang TX03 Le Phuoc Long TX04 Nguyen Anh Tuan CHUYENDI SoCD MaTX MaXe CD01 TX01 8659 CD02 TX02 7715 CD03 TX01 8659 CD04 TX03 4573 Matx Hoten SoCD Matx Maxe TX01 Huynh Trong Tao CD01 TX01 8659 TX01 Huynh Trong Tao CD03 TX01 8659 TX02 Nguyen Sang CD02 TX02 7715 TX03 Le Phuoc Long CD04 TX03 4573 TX04 Nguyen Anh Tuan Null Null Null { Bộ của quan hệ TAIXE được thêm Vào dù không phù hợp với kết quả của quan hệ CHUYENDI Tương tự right outer join và full outer join (lấy cả 2) (lấy hết tất cả bộ của quan hệ bên trái) 91  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 HONV TENNV Vuong Quyen Nguyen Tung S HONV TENNV Le Nhan 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) 92 R HONV TENNV Vuong Quyen Nguyen Tung S HONV TENNV Le Nhan Vuong Quyen 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} 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 S Lưu ý : Phép hội và phép giao có tính chất giao hoán 93 Câu hỏi 9: Cho biết nhân viên không làm việc ? (Phép trừ) (NHANVIEN[MANV]) – (PHANCONG[MANV]) Cách 2: 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]) MANV(NHANVIEN) – MANV(PHANCONG) Cách 1:  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])  94  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+ }),(,/{ RstSstSRQ  21 12 1 ))(( )( TTT RTST RT SR SR          95 Q= PHANCONG/DEAN MADA TH001 TH002 DT001 MANV 002 MANV MADA 001 TH001 001 TH002 002 TH001 002 TH002 002 DT001 003 TH001 R=PHANCONG S=DEAN Kết quả Q Cho biết nhân viên làm việc cho tất cả các đề án ? (được phân công tham gia tất cả các đề án)  Hoặc viết Q= PHANCONG DEAN 96 Mahv HV01 HV03 R=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 S=MONHOC Mamh Tenmh CSDL Co so du lieu CTRR Cau truc roi rac THDC Tin hoc dai cuong KETQUATHI[Mahv,Mamh] /MONHOC[Mamh] Q=KETQUA/MONHOC ][ ],[ MamhMONHOCMONHOC MamhMahvKETQUATHIKETQUA   * Viết cách khác 97  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 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 )()(),...,(),(,...,, 221121 Ennn AFAFAFGGG  98  Điểm thi cao nhất, thấp nhất, trung bình của môn CSDL ?  Điểm thi cao nhất, thấp nhất, trung bình của từng môn ? (group by mamh) )()(),min(),max( KETQUATHIDiemavgDiemDiemMamh )(CSDL''Mamh)(),min(),max( KETQUATHIDiemagvDiemDiem   99 Chương 4: Ngôn ngữ truy vấn SQL 100 • Là ngôn ngữ chuẩn để truy vấn và thao tác trên CSDL quan hệ • Là ngôn ngữ phi thủ tục • Khởi nguồn của SQL là SEQUEL - Structured English Query Language, năm 1974) • Các chuẩn SQL – SQL89 – SQL92 (SQL2) – SQL99 (SQL3) 101 • Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL): cho phép khai báo cấu trúc bảng, các mối quan hệ và các ràng buộc. • Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML): cho phép thêm, xóa, sửa dữ liệu. • Ngôn ngữ truy vấn dữ liệu (Structured Query Language – SQL): cho phép truy vấn dữ liệu. • Ngôn ngữ điều khiển dữ liệu (Data Control Language – DCL): khai báo bảo mật thông tin, cấp quyền và thu hồi quyền khai thác trên cơ sở dữ liệu. 102 1. Lệnh tạo bảng (CREATE) 3.1.1 Cú pháp 3.1.2 Một số kiểu dữ liệu 2. Lệnh sửa cấu trúc bảng (ALTER) 3.2.1 Thêm thuộc tính 3.2.2 Sửa kiểu dữ liệu của thuộc tính 3.2.3 Xoá thuộc tính 3.2.4 Thêm ràng buộc toàn vẹn 3.2.5 Xoá ràng buộc toàn vẹn 3. Lệnh xóa bảng (DROP) 103 2.1 Ngôn ngữ định nghĩa dữ liệu Cú pháp CREATE TABLE ( [not null], [not null], [not null], khai báo khóa chính, khóa ngoại, ràng buộc ) 104 Một số kiểu dữ liệu Kiểu dữ liệu SQL Server Chuỗi ký tự varchar(n), char(n),nvarchar(n), nchar(n) Số tinyint,smallint, int, numeric(m,n), decimal(m,n),float, real, smallmoney, money Ngày tháng smalldatetime, datetime Luận lý bit 2.1 Ngôn ngữ định nghĩa dữ liệu 105 Lược đồ CSDL quản lý bán hàng gồm có các quan hệ sau: KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK, CMND) NHANVIEN (MANV,HOTEN, NGVL, SODT) SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA) HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) CTHD (SOHD,MASP,SL) 2.1 Ngôn ngữ định nghĩa dữ liệu 106 Create table KHACHHANG ( MAKH char(4) primary key, HOTEN varchar(40), DCHI varchar(50), SODT varchar(20), NGSINH smalldatetime, DOANHSO money, NGDK smalldatetime, CMND varchar(10) ) 2.1 Ngôn ngữ định nghĩa dữ liệu 107 Create table CTHD ( SOHD int foreign key references HOADON(SOHD), MASP char(4) foreign key references SANPHAM(MASP), SL int, constraint PK_CTHD primary key (SOHD,MASP) ) 2.1 Ngôn ngữ định nghĩa dữ liệu 108 2.1 Ngôn ngữ định nghĩa dữ liệu 2.1 Thêm thuộc tính ALTER TABLE tênbảng ADD têncột kiểudữliệu – Ví dụ: thêm cột Ghi_chu vào bảng khách hàng ALTER TABLE KHACHHANG ADD GHI_CHU varchar(20) 2.2 Sửa kiểu dữ liệu thuộc tính ALTER TABLE tênbảng ALTER COLUMN têncột kiểudữliệu_mới • Lưu ý: Không phải sửa bất kỳ kiểu dữ liệu nào cũng được 2. Sửa cấu trúc bảng 109 2.1 Ngôn ngữ định nghĩa dữ liệu – Ví dụ: Sửa Cột Ghi_chu thành kiểu dữ liệu varchar(50) ALTER TABLE KHACHHANG ALTER COLUMN GHI_CHU varchar(50) – Nếu sửa kiểu dữ liệu của cột Ghi_chu thành varchar(5), mà trước đó đã nhập giá trị cho cột Ghi_chu có độ dài hơn 5ký tự thì không được phép. – Hoặc sửa từ kiểu chuỗi ký tự sang kiểu số, 2.3 Xóa thuộc tính ALTER TABLE tên_bảng DROP COLUMN tên_cột – Ví dụ: xóa cột Ghi_chu trong bảng KHACHHANG ALTER TABLE NHANVIEN DROP COLUMN Ghi_chu 110 2.1 Ngôn ngữ định nghĩa dữ liệu 111 2.4 Thêm ràng buộc toàn vẹn ALTER TABLE ADD CONSTRAINT UNIQUE tên_cột PRIMARY KEY (tên_cột) FOREIGN KEY (tên_cột) REFERENCES tên_bảng (cột_là_khóa_chính) CHECK (tên_cột điều_kiện) 2.1 Ngôn ngữ định nghĩa dữ liệu • Ví dụ – ALTER TABLE NHANVIEN ADD CONSTRAINT PK_NV PRIMARY KEY (MANV) – ALTER TABLE CTHD ADD CONSTRAINT FK_CT_SP FOREIGN KEY (MASP) REFERENCES SANPHAM(MASP) – ALTER TABLE SANPHAM ADD CONSTRAINT CK_GIA CHECK (GIA >=500) – ALTER TABLE KHACHHANG ADD CONSTRAINT UQ_KH UNIQUE (CMND) 112 2.1 Ngôn ngữ định nghĩa dữ liệu 2.5 Xóa ràng buộc toàn vẹn ALTER TABLE tên_bảng DROP CONSTRAINT tên_ràng_buộc – Ví dụ: • Alter table CTHD drop constraint FK_CT_SP • Alter table SANPHAM drop constraint ck_gia • Lưu ý: đối với ràng buộc khóa chính, muốn xóa ràng buộc này phải xóa hết các ràng buộc khóa ngoại tham chiếu tới nó 113 2.1 Ngôn ngữ định nghĩa dữ liệu • Cú pháp DROP TABLE tên_bảng • Ví dụ: xóa bảng KHACHHANG. DROP TABLE KHACHHANG • Lưu ý: khi muốn xóa một bảng phải xóa tất cả những khóa ngoại tham chiếu tới bảng đó trước. 114 2.2 Ngôn ngữ thao tác dữ liệu (DML) • Gồm các lệnh: 1 Lệnh thêm dữ liệu (INSERT) 2 Lệnh sửa dữ liệu (UPDATE) 3 Lệnh xóa dữ liệu (DELETE) 115 2.2 Ngôn ngữ thao tác dữ liệu – Lệnh Insert • Cú pháp - INSERT INTO tên_bảng (cột1,,cộtn) VALUES (giá_trị_1,., giá_trị_n) - INSERT INTO tên_bảng VALUES (giá_trị_1, giá_trị_2,, giá_trị_n) - SELECT * INTO tên-bảng-mới from tên-bảng-có-sẵn - INSERT INTO tên-bảng-tạo-trước select * from tên- bảng-có-sẵn • Ví dụ: – insert into SANPHAM values('BC01','But chi', 'cay', 'Singapore', 3000) – insert into SANPHAM(masp,tensp,dvt,nuocsx,gia) values ('BC01','But chi','cay','Singapore',3000) 116 2.2 Ngôn ngữ thao tác dữ liệu – Lệnh Insert • Ví dụ của selectinto - Select * into SANPHAM_NEW from SANPHAM - Select * into SANPHAM_NEW from SANPHAM WHERE điều-kiện • Ví dụ của insert into.select. – insert into SANPHAM_COPY select * from SANPHAM – insert into SANPHAM_COPY select * from SANPHAM where điều-kiện 117 2.2 Ngôn ngữ thao tác dữ liệu – Lệnh Update • Cú pháp UPDATE tên_bảng SET cột_1 = giá_trị_1, cột_2 = giá_trị_2 . [WHERE điều_kiện] • Lưu ý: lưu ý với các lệnh xóa và sửa, nếu không có điều kiện ở WHERE nghĩa là xóa hoặc sửa tất cả. • Ví dụ: Tăng giá 10% đối với những sản phẩm do “Trung Quoc” sản xuất UPDATE SANPHAM SET Gia = Gia*1.1 WHERE Nuocsx=‘Trung Quoc’ 118 2.2 Ngôn ngữ thao tác dữ liệu – Lệnh Delete • Cú pháp DELETE FROM tên_bảng [WHERE điều_kiện] • Ví dụ: – Xóa toàn bộ nhân viên DELETE FROM NHANVIEN – Xóa những sản phẩm do Trung Quốc sản xuất có giá thấp hơn 10000 DELETE FROM SANPHAM WHERE (Gia <10000) and (Nuocsx=‘Trung Quoc’) 119  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, ] 120  Toán tử so sánh: o=,>,=, oBETWEEN o IS NULL, IS NOT NULL oLIKE (%,_) o IN, NOT IN oEXISTS, NOT EXISTS oSOME, 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( )) 121  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. NHANVIEN (MaNV, HoTen, Phai, Luong,NTNS, Ma_NQL, MaPH) PHONGBAN (MaPH, TenPH, TRPH) DEAN (MaDA, TenDA, Phong, NamThucHien) PHANCONG (MaNV, MaDA, ThoiGian) Bài tập: Cho lược đồ CSDL “quản lý đề án công ty” như sau 122 MANV HOTEN NTNS PHAI MA_NQL MaPH LUONG 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 MADA TENDA PHONG NamThucHien TH001 Tin hoc hoa 1 NC 2002 TH002 Tin hoc hoa 2 NC 2003 DT001 Dao tao 1 DH 2004 DT002 Dao tao 2 DH 2004 MAPH TENPH TRPH QL Quan Ly 001 DH Dieu Hanh 003 NC Nghien Cuu 002 MANV MADA THOIGIAN 001 TH001 30,0 001 TH002 12,5 002 TH001 10,0 002 TH002 10,0 002 DT001 10,0 002 DT002 10,0 003 TH001 37,5 004 DT001 22,5 004 DT002 10,0 006 DT001 30,5 007 TH001 20,0 007 TH002 10,0 008 DT002 12,5 PHANCONG NHANVIEN DEAN PHONGBAN 12 3 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 124 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’) 125 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) 126 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 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_’ 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 %’ 127 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. – 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. – 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. – Câu hỏi 30: Cho biết nhân viên có mức lương lớn nhất. Select COUNT(MaNV) as SoNV from NhanVien Select COUNT (DISTINCT Ma_NQL) from NhanVien Select MAX(Luong), AVG(Luong), SUM(Luong) from NhanVien Select HoTen from NhanVien Where Luong = (Select MAX(Luong) from NhanVien ) 128 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). 129 n h ó m Các thuộc tính GROUP BY: Q a a b b c c c c c d d d Chia các dòng thành các nhóm dựa trên tập thuộc tính chia nhóm Q Count(S) Q S a b c d 2 2 5 3 10 2 9 5 10 8 6 4 10 16 Câu SQL: Select Q, count(S) From NV Group by Q Quan hệ NV 18 50 Tương tự cho các hàm SUM, MIN, MAX, AVG 130 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”. 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”. 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 Select Phai, count(Manv) as SoNV from NhanVien Group by Phai Select MaPH, count(Manv) from NhanVien Group by MaPH 131 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 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? Select TenPH, count(Manv) as SoLuongNV From NhanVien n, PhongBan p Where n.MaPh=p.MaPH Group by TenPH 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. 132 Câu hỏi 36: Đếm số đề án của từng nhân viên tham gia? Select MaNV, count(MaDA) as SoDATG From PhanCong Group by MaNV - 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, 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 133 Câu hỏi 38: Cho biết những nhân viên tham gia từ 2 đề án trở lên?  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. Select MaNV, count(MaDA) as SoDATG From PhanCong Group by MaNV Having count(MaDA) >=2 Select MaPH, count(Manv) from NhanVien Group by MaPH Having count(Manv)>4 Câu hỏi 39: Cho biết mã phòng ban có trên 4 nhân viên? 134 Chương 5: Ràng buộc toàn vẹn 135  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) 136 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. 137 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 Bảng tầm ảnh hưởng của RBTV có dạng như sau: 138  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): 3.1. Ràng buộc toàn vẹn miền giá trị Thêm Xóa Sửa NHANVIEN +(PHAI) - +(PHAI) 139 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 140 ◦ Bối cảnh: quan hệ DEAN ◦ Bảng tầm ảnh hưởng: Thêm Xóa Sửa DEAN + (NGBD_DK, NGKT_DK) - +(NGBD_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. 14 1 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, MucLuong) - +(HESO, MucLuong) 142 3. RBTV – BỐI CẢNH NHIỀU QUAN HỆ • 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]) 3.4. Ràng buộc toàn vẹn tham chiếu 143 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) 144 3. RBTV – BỐI CẢNH NHIỀU QUAN HỆ – Bối cảnh: DATHANG, GIAOHANG – Bảng tầm ảnh hưởng: 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 Thêm Xóa Sửa DATHANG - - + (ngaydh) GIAOHANG +(ngaygh) - + (ngaygh) 145 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 146 3. RBTV – BỐI CẢNH NHIỀU QUAN HỆ 3.7. Ràng buộc toàn vẹn do thuộc tính tổng hợp – Bối cảnh: PHONGBAN, DIADIEM_PHG – Bảng tầm ảnh hưởng: Thêm Xóa Sửa PHONGBAN + - - DIADIEM_PHG - + + (MAPH) 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. 147 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 = 0. 148 149 Chương 6: Phụ thuộc hàm và dạng chuẩn 150  Phụ thuộc hàm ◦ Hệ luật dẫn Amstrong ◦ Bao đóng ◦ Phủ tối thiểu ◦ Khóa ◦ Thuật toán tìm khóa  Các dạng chuẩn ◦ Dạng chuẩn 1 ◦ Dạng chuẩn 2 ◦ Dạng chuẩn 3 ◦ Dạng chuẩn Boyce Codd 151 X,Y là hai tập thuộc tính trên quan hệ R r1, r2 là 2 bộ bất kỳ trên R Ta nói X xác định Y, ký hiệu X → Y, nếu và chỉ nếu r1[X] = r2[X] thì r1[Y] = r2[Y] X → Y là một phụ thuộc hàm, hay Y phụ thuộc X. X là vế trái của phụ thuộc hàm, Y là vế phải của phụ thuộc hàm. Ví dụ: cho quan hệ sinh viên như sau: SINHVIEN(Tên, Mônhọc, SốĐT, ChuyênNgành, GiảngViên, Điểm) 152 Tên Mônhọc SốĐT ChuyênNgành GiảngViên Điểm Huy CSDL 0913157875 HTTT Hưng 5 Hoàng CSDL 0913154521 HTTT Hưng 10 Huy AV 0913157875 HTTT Thủy 5 Hải Toán SXTK 0166397547 MạngMT Lan 10 Tính HQTCSDL 012145475 CNPM Sang 7 Tính LậpTrình 012145475 CNPM Việt 8 Hoàng LậpTrình 0913154521 HTTT Việt 10 Tên SốĐT ChuyênNgành? Mônhọc GiảngViên? Tên Mônhọc Điểm? 153 Một số tính chất sau: Với mỗi Tên có duy nhất một SốĐT và ChuyênNgành Với mỗi Mônhọc có duy nhất một GiảngViên Với mỗi Tên, Mônhọc có duy nhất một Điểm Ký hiêu: {Tên} → {SốĐT, ChuyênNgành} {Mônhọc} → {GiảngViên} {Tên, Mônhọc} → {Điểm} 154 Tên Mônhọc SốĐT ChuyênNgàn h GiảngViên Điểm Các phụ thuộc hàm kéo theo: {Tên} → {ChuyênNgành} {Mônhọc, Điểm} → {GiảngViên, Điểm} 155 Gọi F là tập các phụ thuộc hàm Định nghĩa: X → Y được suy ra từ F, hay F suy ra X → Y, ký hiệu: F ╞ X → Y nếu bất kỳ bộ của quan hệ thỏa F thì cũng thỏa X → Y Hệ luật dẫn Amstrong: Với X, Y, Z, W ⊆ U. Phụ thuộc hàm có các tính chất sau: F1) Tính phản xạ: Nếu Y ⊆ X thì X → Y F2) Tính tăng trưởng: {X → Y} ╞ XZ → YZ F3) Tính bắc cầu: {X → Y, Y → Z} ╞ X → Z 156 Từ hệ luật dẫn Amstrong ta suy ra một số tính chất sau: F4) Tính kết hợp: {X → Y, X → Z} ╞ X → YZ F5) Tính phân rã: {X → YZ, X → Y} ╞ X → Z F6) Tính tựa bắt cầu: {X → Y, YZ → W} ╞ XZ → W Ví dụ: F = {A → B, A → C, BC → D}, chứng minh A → D? 1) A → B 2) A → C 3) A → BC (tính kết hợp F4) 4) BC → D 5) A → D (tính bắc cầu F3) 157 Bao đóng của tập phụ thuộc hàm Bao đóng của tập phụ thuộc hàm F, ký hiệu F+ là tập tất cả các phụ thuộc hàm được suy ra từ F. Nếu F = F+ thì F là họ đầy đủ của các phụ thuộc hàm. Thuật toán tìm bao đóng của tập thuộc tính Bao đóng của tập thuộc tính X đối với tập phụ thuộc hàm F, ký hiệu là X+F là tập tất cả các thuộc tính A có thể suy dẫn từ X nhờ tập bao đóng của các phụ thuộc hàm F+ X+F = { A ∈ Q + | X → A ∈ F+ } 158 Input: (Q,F),X ⊆ Q+ Output: X+F Bước 1: Tính dãy X(0) , X(1) ,, X(i): - X(0) = X - X(i+1) = X(i) ∪ Z, ∃(Y → Z ) ∈ F(Y ⊆ X(i)), loại (Y → Z) ra khỏi F - Dừng khi X(i+1) = X(i) hoặc khi X(i)=Q+ Bước 2: Kết luận X+F = X (i) 159 Ví dụ: Cho lược đồ quan hệ R(A, B, C, D, E, G, H) và tập phụ thuộc hàm F={ f1: B → A , f2: DA → CE, f3: D → H, f4: GH → C, f5: AC → D} Tìm AC+F ? 160 Bước 1: X0 = AC Bước 2: Từ f1 đến f4 không thoả, f5 thoả nên X1 = AC ∪ D = ACD Lặp lại bước 2: f1 không thoả, f2 thỏa nên X2=ACD ∪ CE = ACDE f3 thỏa nên X3=ACDE ∪ H =ACDEH f4 không thỏa, f5 đã thỏa Lặp lại bước 2: f2, f3 và f5 đã thỏa, f1 và f4 không thỏa. Nên X4=X3=ACDEH Vậy AC+F=ACDEH 161 Bài toán thành viên Cho tập thuộc tính Q, tập phụ thuộc hàm F trên Q và một phụ thuộc hàm X → Y trên Q. Câu hỏi đặt ra rằng X → Y ∈ F+ hay không? X → Y ∈ F+ ⇔ Y ⊆ X+ Ví dụ: Từ ví dụ tìm bao đóng của tập thuộc tính AC. Cho biết AC → E có thuộc F+ ? Ta có AC+F=ACDEH Vì E ∈ AC+F nên AC → E ∈ F + 162 Hai tập phụ thuộc hàm tương đương Hai tập phụ thuộc hàm F và G tương đương nếu F+ = G+ . Ký hiệu G ≡ F Phủ tối thiểu của một tập phụ thuộc hàm F được gọi là phủ tối thiểu của tập phụ thuộc hàm (hay tập phụ thuộc hàm tối thiểu) nếu thỏa: (i) F là tập phụ thuộc hàm có thuộc tính vế trái không dư thừa (ii) F là tập phụ thuộc hàm có vế phải một thuộc tính (iii) F là tập phụ thuộc hàm không dư thừa 163 Phụ thuộc hàm có thuộc tính vế trái dư thừa Cho F là tập các phụ thuộc hàm trên lược đồ quan hệ Q. Khi đó Z → Y ∈ F là phụ thuộc hàm có thuộc tính vế trái dư thừa nếu tồn tại A∈ Z mà F = F – (Z → Y) ∪ ((Z - A) → Y) Ngược lại Z → Y là phụ thuộc hàm có thuộc tính vế trái không dư thừa hay Y phụ thuộc đầy đủ vào Z. Z → Y còn được gọi là phụ thuộc hàm đầy đủ. Phụ thuộc hàm có vế phải một thuộc tính Mỗi tập phụ thuộc hàm F đều tương đương với một tập phụ thuộc hàm G mà vế phải của các phụ thuộc hàm thuộc G chỉ gồm một thuộc tính 164 Phụ thuộc hàm không dư thừa F là tập phụ thuộc hàm không dư thừa nếu không tồn tại F’⊂ F sao cho F’ ≡ F. Ngược lại F được gọi là tập phụ thuộc hàm dư thừa. Thuật toán tìm phủ tối thiểu của tập phụ thuộc hàm Bước 1: Phân rã các phụ thuộc hàm có vế phải nhiều thuộc tính thành các phụ thuộc hàm có vế phải một thuộc tính Bước 2: Loại các thuộc tính có vế trái dư thừa của mọi phụ thuộc hàm (bỏ thuộc tính bên vế trái, khi và chỉ khi bao đóng của các thuộc tính còn lại có chứa thuộc tính đó) Bước 3: Loại các phụ thuộc hàm dư thừa khỏi F (Các thuộc tính ở vế phải của PTH chỉ xuất hiện duy nhất 1 lần thì không thể loại bỏ. Còn lại tính bao đóng của tập thuộc tính vế trái nếu có xuất hiện thuộc tính vế phải thì có thể loại bỏ thuộc tính đó và đó là PTH dư thừa) 165 Ví dụ: Cho lược đồ quan hệ Q(A,B,C,D) và tập phụ thuộc hàm F={AB → CD, B → C, C → D} Tìm phủ tối thiểu? Bước 1: Tách các phụ thuộc hàm sao cho vế phải chỉ còn một thuộc tính. + ta có F={AB → C, AB → D, B → C, C → D} Bước 2: Bỏ các thuộc tính dư thừa ở vế trái. + B → C, C → D Không xét vì vế trái chỉ có một thuộc tính. + xét AB → C : Nếu Bỏ A thì B+=BCD không chứa A nên không thể Bỏ A. Nếu Bỏ B thì A+=A. không bỏ được thuộc tính nào. + xét AB → D : Nếu Bỏ A thì B+=BCD không chứa A nên không thể Bỏ A. Nếu Bỏ B thì A+=A. không bỏ được thuộc tính nào. Bước 3: Loại khỏi F các phụ thuộc hàm dư thừa. + xét AB->C : Tính AB+=ABCD chứa C nên loại bỏ AB->C + xét AB->D : tính AB+=ABCD chứa D nên loại bỏ AB->D + B->C : tính B+=B không thể bỏ. + C->D : tính C+=C không thể bỏ. Phủ tối thiểu là {B->C, C->D} 166 Định nghĩa Cho lược đồ quan hệ Q(A1, A2, , An), Q+ là tập thuộc tính của quan hệ Q, F là tập phụ thuộc hàm trên Q, K là tập con của Q+. Khi đó K gọi là một khóa của Q nếu: (i) K+F = Q + (ii) Không tồn tại K’⊂ K sao cho K’+F = Q + Thuộc tính A được gọi là thuộc tính khóa nếu A∈ K, trong đó K là khóa của Q. Ngược lại thuộc tính A được gọi là thuộc tính không khóa. K’ được gọi là siêu khóa nếu K ⊆ K’. 167 Sử dụng đồ thị có hướng để tìm khóa như sau: Bước 1: - Mỗi nút của đồ thị là tên một thuộc tính của lược đồ quan hệ R - Cung nối hai thuộc tính A và B thể hiện phụ thuộc hàm A → B - Thuộc tính chỉ có các mũi tên đi ra (nghĩa là chỉ nằm trong vế trái của phụ thuộc hàm) được gọi là nút gốc - Thuộc tính chỉ có các mũi tên đi tới (nghĩa là chỉ nằm trong vế phải của phụ thuộc hàm) được gọi là nút lá Bước 2: - Xuất phát từ tập các nút gốc (X), dựa trên tập các phụ thuộc hàm F, tìm bao đóng X+F . - Nếu X+F= Q + thì X là khóa, ngược lại bổ sung một thuộc tính không thuộc nút lá vào X rồi thực hiện tìm bao đóng của X. Dừng khi tìm được một khóa của R. 168 Ví dụ: Cho lược đồ quan hệ R(A, B, C, D, E, G, H) và tập phụ thuộc hàm F={ B → A , DA → CE, D → H, GH → C, AC → D} Tìm một khóa của R? Phân rã vế phải ta có F ={ B → A , DA → C, DA → E, D → H, GH → C, AC → D} 169 Nhận thấy từ đồ thị trên, nút B và G là nút gốc. Khóa của R phải chứa thuộc tính B hoặc G, trong ví dụ này chọn B. B+F = BA, Vì B + F ≠ Q + nên B không là khóa. Nhận thấy D là thuộc tính ở vế trái của ba phụ thuộc hàm trong F nên bổ sung thuộc tính D vào để xét khóa. BD+F = BDACEH, vì BD + F ≠ Q + nên BD không là khóa. Bổ sung thuộc tính G. BDG+F = BDGACEH, vì BDG + F = Q + nên BDG là khóa. 170 Dạng chuẩn 1 (1NF) Lược đồ Q ở dạng chuẩn 1 nếu mọi thuộc tính đều mang giá trị nguyên tố. Giá trị nguyên tố là giá trị không phân nhỏ được nữa. Các thuộc tính đa trị (multi-valued), thuộc tính đa hợp(composite) không là nguyên tố. Ví dụ: Thuộc tính ĐiaChỉ : Số 175 Đường 3/2 Phường 10 Quận 5 không là nguyên tố. ĐịaChỉ → (SốNhà, Đường, Phường, Quận) 171 Ví dụ: HOADON(MaHD, MaKH, NgayHD, CtietMua, SoTien) CtietMua không là nguyên tố nên không thỏa dạng chuẩn 1 172 Lược đồ Q ở dạng chuẩn 2 nếu thoả: (1) Q đạt dạng chuẩn 1 (2) Mọi thuộc tính không khóa của Q đều phụ thuộc đầy đủ vào khóa. Kiểm tra dạng chuẩn 2 Bước 1: Tìm mọi khóa của Q Bước 2: Với mỗi khóa K, tìm bao đóng của tập tất cả các tập con thực sự Si của K Bước 3: Nếu tồn tại bao đóng Si + chứa thuộc tính không khóa thì Q không đạt dạng chuẩn 2, ngược lại Q đạt dạng chuẩn 2. 173 Ví dụ: Cho Q1 (A, B, C, D), F={A→B, B→DC} Lược đồ chỉ có một khóa là A, nên mọi thuộc tính đều phụ thuộc đầy đủ vào khóa. Do vậy Q1 đạt dạng chuẩn 2. Ví dụ: Cho Q2 (A, B, C, D), F={AB → D, C → D} Lược đồ có khóa là ABC, ngoài ra còn có C⊂ABC mà C → D, trong đó D là thuộc tính không khóa (nghĩa là thuộc tính D không phụ thuộc đầy đủ vào khóa). Do vậy Q2 không đạt dạng chuẩn 2. 174 Lược đồ Q ở dạng chuẩn 3 nếu mọi phụ thuộc hàm X → A ∈ F+, với A ∉ X đều có: (1) X là siêu khóa, hoặc (2) A là thuộc tính khóa Hay mọi thuộc tính không khóa của Q không phụ thuộc bắc cầu vào khóa chính của Q Kiểm tra dạng chuẩn 3 Bước 1: Tìm mọi khóa của Q Bước 2: Phân rã vế phải của mọi phụ thuộc hàm trong F để tập F trở thành tập phụ thuộc hàm có vế phải một thuộc tính Bước 3: Nếu mọi phụ thuộc hàm X → A ∈ F, mà A ∉ X đều thỏa (1) X là siêu khóa (vế trái chứa một khóa), hoặc (2) A là thuộc tính khóa (vế phải là tập con của khóa) thì Q đạt dạng chuẩn 3, ngược lại Q không đạt dạng chuẩn 3. 175 Ví dụ: Cho Q (A, B, C, D), F={AB → D, C → D} Bước 1: Q có một khóa là ABC Bước 2: Mọi phụ thuộc hàm trong F đều đã có vế phải một thuộc tính. Bước 3: Với AB → D, nhận thấy rằng D ∉ AB có • Vế trái (AB) không phải là siêu khóa. • Hơn nữa vế phải (D) không là thuộc tính khóa Vậy Q không đạt dạng chuẩn 3. 176 Lược đồ Q ở dạng chuẩn BC nếu mọi phụ thuộc hàm X → A ∈ F+, với A ∉ X đều có X là siêu khóa. Nhắc lại: Siêu khóa : là một tập con các thuộc tính của Q+ mà giá trị của chúng có thể phân biệt 2 bộ khác nhau trong cùng một thể hiện TQ bất kỳ. Nghĩa là:  t1, t2  TQ, t1[K] t2[K] K là siêu khóa của Q. 177 Kiểm tra dạng chuẩn BCNF Bước 1: Tìm mọi khóa của Q Bước 2: Phân rã vế phải của mọi phụ thuộc hàm trong F để tập F trở thành tập phụ thuộc hàm có vế phải một thuộc tính Bước 3: Nếu mọi phụ thuộc hàm X → A ∈ F, mà A ∉ X đều thỏa X là siêu khóa (vế trái chứa một khóa), thì Q đạt dạng chuẩn BC, ngược lại Q không đạt dạng chuẩn BC. 178 Ví dụ: Cho Q (A, B, C, D, E, I), F={ACD → EBI, CE → AD} Bước 1: Q có hai khóa là {ACD, CE} Bước 2: Phân rã vế phải của các phụ thuộc hàm trong F, ta có: F={ACD → E, ACD → B, ACD → I, CE → A, CE → D} Bước 3: Mọi phụ thuộc hàm trong F đều có vế trái là một siêu khóa Vậy Q đạt dạng chuẩn BC. 179

Các file đính kèm theo tài liệu này:

  • pdfvn_bai_giang_ly_thuyet_toan_bo_3941.pdf