Bài giảng Cơ sở dữ liệu - Chương 7: Ràng buộc toàn vẹn (Intergrety Constraints) - Phạm Nguyễn Cương

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 7: Ràng buộc toàn vẹn (Intergrety Constraints) - Phạm Nguyễn Cương

1. Nội dung chi tiết . Khái niệm . Các đặc trưng của RBTV . Phân loại . Cài đạ t © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 2
2. Nội dung chi tiết . Khái niệm và phân loại . Các đặc trưng của RBTV - Bối cảnh - Nội dung - Bảng tầm ảnh hưởng . Phân loại . Cài đặt © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 4
3. Bối cảnh (tt) . Ví dụ (R2) - Người quản lý trực tiếp (của một giáo viên) phải là một giáo viên trong cùng bộ môn • Các phép cập nhật – Cập nhật người quản lý trực tiếp của một giáo viên – Thêm mới một giáo viên • Bối cảnh: GIAOVIEN © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 6
4. Nội dung (tt) . Ví dụ (R1) - Ngôn ngữ tự nhiên • Mức lương của một người giáo viên không được vượt quá trưởng bộ môn của giáo viên đó. - Ngôn ngữ hình thức (t)(GIAOVIEN(t)  (s)(BOMON(s)  (u)(GIAOVIEN(u)  s.TRUONGBM u.MAGV  s.MABM t.MABM  t.LUONG u.LUONG ))) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 8
5. Bảng tầm ảnh hưởng . Bảng tầm ảnh hưởng - Xác định thao tác cập nhật nào cần phải kiểm tra RBTV khi được thực hiện trên quan hệ bối cảnh . Có 2 loại - Bảng tầm ảnh hưởng cho một RBTV - Bảng tầm ảnh hưởng tổng hợp © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 10
6. Bảng tầm ảnh hưởng tổng hợp Ràng buộc 1 Ràng buộc 2 Ràng buộc m T X S T X S T X S Quan hệ 1 + - + + - + + - + Quan hệ 2 - + - Quan hệ 3 - - + - + - Quan hệ n - + - - - + © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 12
7. Phân loại . RBTV được chia làm 3 loại chính (1): • RBTV bắt buộc liên quan đến mô hình dữ liệu (inherent model based contraints). • Ví dụ: Một quan hệ không được chứa các bộ dữ liệu trùng nhau. • RBTV liên quan đến lược đồ của mô hình dữ liệu (schema based contraints). • Ví dụ: Ràng buộc miền giá trị, ràng buộc trên khóa, ràng buộc trên giá trị rỗng, ràng buộc tham chiếu. • RBTV dựa trên ứng dụng (application based contraints). • Ví dụ: Mức lương của một người giáo viên không được vượt quá trưởng bộ môn (1) Trích từ sách: Fundamental of Databases 4th , Ramez Elmasri & Shamkant B. Navathe, ISBN 0-321-12226-7, 2003 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 14
8. RBTV - Miền giá trị . Ràng buộc qui định các giá trị cho một thuộc tính R A B C D 1 1  5 7  9{ , {,1  }10}   12 3   23 9 . Miền giá trị - Liên tục - Rời rạc © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 16
9. Ví dụ 4 . Phụ cấp của mỗi công việc trong đề tài không được vượt quá 20 triệu. - Bối cảnh: THAMGIAĐT - Biểu diễn: (t )(THAMGIAĐT(t)  t.PHỤCẤP 20) - Bảng tầm ảnh hưởng: R4 Thêm Xóa Sửa THAMGIAĐT (PHỤCẤP) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 18
10. Ví dụ 5 . Tên bộ môn là duy nhất. - Bối cảnh: BOMON - Biểu diễn: (t1, t2) (BOMON(t1)  BOMON(t2)  (t1 t2 t1.TENBM t2.TENBM)) hay (t1)(BOMON(t1)  ¬(t2)(BOMON(t2)  t1 t2  t1.TENBM = t2.TENBM) ) - Bảng tầm ảnh hưởng: R5 Thêm Xóa Sửa BOMON (TENBM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 20
11. RBTV - Liên thuộc tính . Là ràng buộc giữa các thuộc tính trong cùng quan hệ R A B C D 1 1  5 7   12 3   23 9 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 22
12. Ví dụ 9 . Ngày bắt đầu của đề tài luôn nhỏ hơn ngày kết thúc của đề tài. - Bối cảnh: ĐỀTÀI - Biểu diễn: (t)( ĐỀTÀI(t)  t.NGÀYBĐ t.NGÀYKT) - Bảng tầm ảnh hưởng: R9 Thêm Xóa Sửa ĐỀTÀI + (NGÀYBĐ, NGÀYKT) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 24
13. RBTV - Tham chiếu . Giá trị xuất hiện tại các thuộc tính trong một quan hệ nào đó phải tham chiếu đến giá trị khóa chính của một quan hệ khác cho trước R A B C D S E F 1 1 7 1  5 7 3 2   12 3   23 9 Bắt buộc phải tồn tại trước . Trường hợp đặc biệt - RB khóa ngoại © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 26
14. Ví dụ 12 . Trưởng bộ môn phải là một giáo viên - Bối cảnh: BOMON, GIAOVIEN - Biểu diễn: (t)(BOMON(t)  (s)(GIAOVIEN(s)  s.MAGV = t.TRUONGBM)) - Bảng tầm ảnh hưởng: R12 Thêm Xóa Sửa GIAOVIEN - + (MAGV) BOMON + - +(TRUONGBM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 28
15. RBTV - Liên bộ, liên quan hệ . Là ràng buộc xảy ra giữa các bộ trên nhiều quan hệ khác nhau R A B C D 1 1  5 7   12 3   23 9 S A B C 2 7 4 7  2 3  2 10 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 30
16. Ví dụ 14 . Mỗi bộ môn phải có ít nhất một giáo viên - Bối cảnh: GIAOVIEN, BOMON - Biểu diễn: (t) (BOMON(t)  (s)(GIAOVIEN(s)  t.MABM = s.MABM)) - Bảng tầm ảnh hưởng: R14 Thêm Xóa Sửa BOMON - +(MABM) GIAOVIEN - (MABM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 32
17. Ví dụ 15 . Ngày sinh của trưởng bộ môn phải nhỏ hơn ngày nhận chức: - Bối cảnh: GIAOVIEN, BOMON - Biểu diễn: (t)(BOMON(t)  (s)(GIAOVIEN(s)  (s.MAGV = t.TRUONGBM s.NGAYSINH < t.NGAYNHANCHUC))) - Bảng tầm ảnh hưởng: R15 Thêm Xóa Sửa GIAOVIEN - (NGAYSINH) BOMON (NGAYNHANCHUC, TRUONGBM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 34
18. RBTV - Thuộc tính tổng hợp . Thuộc tính tổng hợp (còn gọi là thuộc tính suy diễn) - Là thuộc tính có giá trị được tính toán từ các thuộc tính khác . Khi CSDL có thuộc tính tổng hợp - RBTV bảo đảm quan hệ giữa thuộc tính tổng hợp và các thuộc tính nguồn © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 36
19. RBTV – Chu trình . Lược đồ CSDL có thể được biểu diễn bằng đồ thị - Đỉnh Tên quan hệ • Quan hệ • Thuộc tính Tên thuộc tính - Cạnh • Đường nối một đỉnh quan hệ với một đỉnh thuộc tính trong lược đồ CSDL Tên quan hệ Tên thuộc tính . Chu trình - Đồ thị xuất hiện đường đi khép kín ~ Lược đồ CSDL có chu trình © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 38
20. Ví dụ 17 (tt) . Nhân viên chỉ được phân công vào các đề án do phòng ban của mình phụ trách - Bối cảnh: NHANVIEN, DEAN, PHANCONG - Biểu diễn: NVDA  NHANVIEN PHG=PHONG DEAN (t) (PHANCONG(t)  (s)(NVDA(s)  t.MA_NVIEN s.MANV  t.MADA s.SODA )) - Bảng tầm ảnh hưởng: R17 Thêm Xóa Sửa NHANVIEN (MANV,PHG) DEAN + (MADA,PHONG) PHANCONG (MA_NVIEN,SODA) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 40
21. Nội dung chi tiết . Khái niệm . Các đặc trưng của RBTV . Phân loại . Cài đặt - Assertion - Trigger - Transaction (giao tác) - Stored Procedure (thủ tục lưu trữ nội) - Application (ứng dụng) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 42
22. Assertion . Là một biểu thức SQL luôn mang giá trị TRUE tại mọi thời điểm. - Người sử dụng cần cho biết cái gì phải đúng . Cú pháp CREATE ASSERTION CHECK ( ) DROP ASSERTION © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 44
23. Ví dụ 19 . Lương của trưởng bộ môn phải lớn hơn 50000 CREATE ASSERTION R15 CHECK ( NOT EXISTS ( SELECT * FROM GIAOVIEN, BOMON WHERE MAGV=TRUONGBM AND LUONG < 50000 ) ) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 46
24. Ví dụ 16 . Số lượng giáo viên của mỗi bộ môn không quá 20 người CREATE ASSERTION R16 CHECK ( 20 >= ALL ( SELECT COUNT(MAGV) FROM GIAOVIEN GROUP BY MABM ) ) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 48
25. Trigger . Là tập hợp các lệnh được thực hiện tự động khi xuất hiện một biến cố nào đó thêm xóa sửa Biến cố trước sau giá trị mới Thỏa giá trị cũ Điều kiện Tập hợp các lệnh row-level Không thỏa statement-level Thông báo lỗi © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 50
26. Ví dụ 15 . Lương của trưởng bộ môn phải lớn hơn 50000 CREATE TRIGGER TR_BM_UPD AFTER UPDATE OF TRUONGBM ON BOMON REFERENCING NEW ROW AS NewTuple FOR EACH ROW WHEN (50000 >= (SELECT LUONG FROM GIAOVIEN WHERE MAGV=NewTuple.TRUONGBM)) Thông báo lỗi cho người dùng © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 52
27. Ví dụ 15 (tt) . Lương của trưởng bộ môn phải lớn hơn 50000 CREATE TRIGGER TR_BM_UPD AFTER UPDATE OF LUONG ON GIAOVIEN REFERENCING NEW ROW AS NewTuple OLD ROW AS OldTuple FOR EACH ROW WHEN (NewTuple.LUONG <= 50000 AND NewTuple.MAGV IN ( SELECT TRUONGBM FROM BOMON )) UPDATE GIAOVIEN SET LUONG=OldTuple.LUONG WHERE LUONG=NewTuple.LUONG © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 54
28. Transaction (tt) . Giao tác phải đảm bảo - Tính nguyên tố (atomicity) - Tính nhất quán của CSDL (consistency) • Các RBTV không bị vi phạm – Trong khi thực hiện giao tác – Trước và sau khi thực hiện giao tác © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 56
29. Ví dụ 7 (tt) Giao tác Xóa_trận_đấu(ngay, gio) Với mọi s THIDAU (s.NGAY ngay  s.GIO gio) Xóa s khỏi THIDAU Cuối với mọi Nếu có một thao tác thất bại thì Quay lui giao tác Ngược lại Hoàn tất giao tác Cuối nếu Cuối giao tác © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 58
30. Ví dụ 11 (tt) Giao tác Thêm_hóa_đơn Thêm HOADON Thêm chi tiết thứ 1 vào CTHD Thêm chi tiết thứ 2 vào CTHD Nếu có một thao tác thêm thất bại thì Quay lui giao tác Ngược lại Hoàn tất giao tác Cuối nếu Cuối giao tác © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 60
31. Ví dụ 7 . Mỗi trận đấu là cuộc thi đấu của đúng 2 đội CREATE PROCEDURE Thêm_trận_đấu t THIDAU , s THIDAU AS begin tran Thêm t vào THIDAU If @@error 0 rollback tran commit tran GO EXEC Thêm_trận_đấu x, y © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 62
32. © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 64