Bài giảng Cơ sở dữ liệu - Chương 7: Ràng buộc toàn vẹn - Lưu Huỳnh Châu Pha

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 
A 
B 
α 
R 
α 
β 
C 
1 
5 
12 
β 
23 
D 
1 
7 
3 
9 
α 
β 
β 
β 
A 
B 
α 
S 
α 
2 
4 
β 
γ 
2 
2 
C 
7 
7 
3 
10 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
29 
Ví dụ 11 
HOADON( SOHD , MAKH, NGAYHD) 
CTHD( SOHD, MAHH , DGIA, SLG) 
Mỗi hóa đơn phải có ít nhất một chi tiết hóa đơn 
Bối cảnh: 
Biểu diễn: 
Bảng tầm ảnh hưởng: 
∀ t ∈ HOADON ( ∃ s ∈ CTHD ( t.SOHD = s.SOHD )) 
R 11 
HOADON 
Thêm 
Xóa 
Sửa 
+ 
+ 
+ (SOHD) 
CTHD 
+ 
+ 
+ (SOHD) 
HOADON, CTHD 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
30 
RBTV - Liên thuộc tính, liên quan hệ 
Là ràng buộc xảy ra giữa các thuộc tính trên nhiều quan hệ khác nhau 
A 
B 
α 
R 
α 
β 
C 
1 
5 
12 
β 
23 
D 
1 
7 
3 
9 
α 
β 
β 
β 
A 
B 
α 
S 
α 
2 
4 
β 
γ 
2 
2 
C 
7 
7 
3 
10 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
31 
Ví dụ 12 
Ngày sinh của trưởng phòng phải nhỏ hơn ngày nhận chức 
Bối cảnh: 
Biểu diễn: 
Bảng tầm ảnh hưởng: 
∀ t ∈ PHONGBAN ( ∃ s ∈ NHANVIEN ( 
	s.MANV = t.TRPHG ∧ 
	t.NG_NHANCHUC • s.NGSINH )) 
R 12 
NHANVIEN 
Thêm 
Xóa 
Sửa 
− 
+ 
+ (NG_NHANCHUC, TRPHG) 
PHONGBAN 
− 
− 
+ (NGSINH, MANV) 
NHANVIEN, PHONGBAN 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
32 
RBTV - Thuộc tính tổng hợp 
Thuộc tính tổng hợp 
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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
33 
Ví dụ 13 
PHONGBAN(TENPHG, MAPHG, TRPHG, NG_NHANCHUC, SO_NV) 
Số nhân viên của một phòng ban phải bằng tổng số lượng nhân viên thuộc phòng đó 
Bối cảnh: 
Biểu diễn: 
Bảng tầm ảnh hưởng: 
∀ t ∈ PHONGBAN ( 
	t.SO_NV = ∑ { s ∈ NHANVIEN | s.PHG = t.MAPHG} ) 
R 13 
NHANVIEN 
Thêm 
Xóa 
Sửa 
+ 
− 
+ (PHG) 
PHONGBAN 
+ 
− 
+ (SO_NV, MAPHG) 
NHANVIEN, PHONGBAN 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
34 
RBTV – Chu trình 
Lược đồ CSDL có thể được biểu diễn bằng đồ thị 
Đỉnh 
Quan hệ 
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 
Chu trình 
Đồ thị xuất hiện đường đi khép kín ~ Lược đồ CSDL có chu trình 
Tên quan hệ 
Tên thuộc tính 
Tên quan hệ 
Tên thuộc tính 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
35 
NHANVIEN 
DEAN 
PHG=PHONG 
Ví dụ 14 
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 
PHANCONG 
NHANVIEN 
DEAN 
PHG=PHONG 
PHANCONG 
SODA=MADA 
MANV=MA_NVIEN 
MANV, MADA 
MA_NVIEN, SODA 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
36 
Ví dụ 14 (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: 
Biểu diễn: 
Bảng tầm ảnh hưởng: 
NHANVIEN, DEAN, PHANCONG 
R 14 
NHANVIEN 
Thêm 
Xóa 
Sửa 
− 
− 
+ (MANV,PHG) 
DEAN 
− 
− 
+ (MADA,PHONG) 
PHANCONG 
+ 
− 
+ (MA_NVIEN,SODA) 
NVDA ← NHANVIEN PHG=PHONG DEAN 
∀ t ∈ PHANCONG ( ∃ s ∈ NVDA ( 
	t.MA_NVIEN = s.MANV ∧ t.MADA = s.SODA )) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
37 
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) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
38 
Cài đặt 
Các RBTV được cài đặt bởi 
Primary key 
Foreign key 
Check contraint 
Assertion 
Trigger 
Transaction 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
39 
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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
40 
Ví dụ 12 
Ngày sinh của trưởng phòng phải nhỏ hơn ngày nhận chức 
CREATE ASSERTION R12 CHECK ( 
	 NOT EXISTS ( 
	 SELECT * 
	 FROM NHANVIEN, PHONGBAN 
	 WHERE MANV=TRPHG 
	 AND NGSINH > NGAY_NHANCHUC ) 
) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
41 
Ví dụ 15 
Lương của trưởng phòng phải lớn hơn 50000 
CREATE ASSERTION R15 CHECK ( 
	 NOT EXISTS ( 
	 SELECT * 
	 FROM NHANVIEN, PHONGBAN 
	 WHERE MANV=TRPHG 
	 AND LUONG < 50000 ) 
) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
42 
Ví dụ 15 (tt) 
ALTER TABLE PHONGBAN ( 
	TENPB VARCHAR (20) UNIQUE , 
	MAPHG INT NOT NULL , 
	TRPHG CHAR (9),	 
	NG_NHANCHUC DATETIME, 
	CONSTRAINT CHK_PB_LUONGTRPHG CHECK ( 
	TRPHG NOT IN ( SELECT MANV FROM NHANVIEN 
	 WHERE LUONG < 50000 )) 
) 
Lương của trưởng phòng phải lớn hơn 50000 
Check Constraint 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
43 
Ví dụ 16 
Số lượng nhân viên của mỗi phòng ban không quá 20 người 
CREATE ASSERTION R16 CHECK ( 
	20 >= ALL ( SELECT COUNT (MANV) 
	 FROM NHANVIEN 
	 GROUP BY PHG ) 
) 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
44 
Ví dụ 16 (tt) 
Số lượng nhân viên của mỗi phòng ban không quá 20 người 
ALTER TABLE NHANVIEN ADD 
CONSTRAINT CHK_NV_SLNVPB CHECK ( 
	20 >= ALL ( SELECT COUNT (MANV) FROM NHANVIEN 
	 GROUP BY PHG )) 
Check Constraint 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
45 
Assertion hay Check Constraint ? 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
46 
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 đó 
row-levelstatement-level 
trước sau 
giá trị mới giá trị cũ 
thêm xóa sửa 
Điều kiện 
Tập hợp các lệnh 
Thông báo lỗi 
Thỏa 
Không thỏa 
Biến cố 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
47 
Trigger (tt) 
Cú pháp 
CREATE TRIGGER 
AFTER|BEFORE INSERT|UPDATE|DELETE ON 
REFERENCING 
	 NEW ROW|TABLE AS 
	 OLD ROW|TABLE AS 
FOR EACH ROW | FOR EACH STATEMENT 
WHEN ( ) 
DROP TRIGGER 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
48 
Ví dụ 15 
Lương của trưởng phòng phải lớn hơn 50000 
CREATE TRIGGER TR_PB_UPD 
AFTER UPDATE OF TRPHG ON PHONGBAN 
REFERENCING 
	 NEW ROW AS NewTuple 
FOR EACH ROW 
WHEN (50000 >= ( SELECT LUONG 	 FROM NHANVIEN 
	 WHERE MANV=NewTuple.TRPHG)) 
	Thông báo lỗi cho người dùng 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
49 
Ví dụ 15 (tt) 
Lương của trưởng phòng phải lớn hơn 50000 
CREATE TRIGGER TR_PB_UPD 
AFTER UPDATE OF TRPHG ON PHONGBAN 
REFERENCING 
	 NEW ROW AS NewTuple 
	 OLD ROW AS OldTuple 
FOR EACH ROW 
WHEN (50000 >= ( SELECT LUONG 	 FROM NHANVIEN 
	 WHERE MANV=NewTuple.TRPHG)) 
	 UPDATE PHONGBAN 
	 SET TRPHG=OldTuple.TRPHG 
	 WHERE TRPHG=NewTuple.TRPHG 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
50 
Ví dụ 15 (tt) 
Lương của trưởng phòng phải lớn hơn 50000 
CREATE TRIGGER TR_PB_UPD 
AFTER UPDATE OF LUONG ON NHANVIEN 
REFERENCING 
	 NEW ROW AS NewTuple 
	 OLD ROW AS OldTuple 
FOR EACH ROW 
WHEN (NewTuple.LUONG <= 50000 AND NewTuple.MANV IN ( 
	 SELECT TRPHG FROM PHONGBAN )) 
	 UPDATE NHANVIEN 
	 SET LUONG=OldTuple.LUONG 
	 WHERE LUONG=NewTuple.LUONG 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
51 
Transaction 
Là tập các lệnh thực hiện một xử lý nào đó trong một ứng dụng CSDL, sao cho 
Hoặc là tất cả các lệnh đều được thực hiện thành công 
Hoặc là không có lệnh nào được thực hiện 
Ví dụ: xử lý chuyển tiền trong ngân hàng 
Giao tác Chuyển_tiền 
	Giảm tiền trong tài khoản người gửi 
	Tăng tiền trong tài khoản người nhận 
	 Nếu tất cả đều thành công thì hoàn tất giao tác 
	 Ngược lại quay lui giao tác 
Cuối giao tác 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
52 
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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
53 
Ví dụ 7 
Giao tác Thêm_trận_đấu(t, s) 
	Thêm t vào THIDAU 
	Thêm s vào THIDAU 
	 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 
Mỗi trận đấu là cuộc thi đấu của đúng 2 đội 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
54 
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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
55 
Ví dụ 11 
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 
Mỗi hóa đơn phải có ít nhất một chi tiết hóa đơn 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
56 
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 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
57 
Stored Procedure 
Các DBMS thương mại cung cấp cách thức lưu trữ các hàm hay thủ tục 
Được lưu trữ trong lược đồ CSDL 
Được sử dụng trong các câu lệnh SQL 
Cú pháp 
CREATE PROCEDURE 
AS 
	Khai báo biến cục bộ 
	Thân chương trình 
GO 
EXEC 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
58 
Ví dụ 7 
Mỗi trận đấu là cuộc thi đấu của đúng 2 đội 
CREATE PROCEDURE Thêm_trận_đấut THIDAU , s THIDAU AS 	 begin tran	 	Thêm t vào THIDAU	 If @@error 0 rollback tran 	Thêm s vào THIDAU	 If @@error 0 	 rollback tran 	 commit tran 
GO 
EXEC Thêm_trận_đấu x, y 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
59 
Nhận xét 
DBMS sẽ kiểm tra RBTV 
Sau khi một thao tác cập nhật diễn ra trên CSDL 
Cuối mỗi giao tác 
Nên cài đặt RBTV ở đâu ??? 
DBMS 
Application 
Trigger quá nhiều → hệ thống chậm chạm 
Stored Procedure → hiệu quả cao 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
60 
Bài tập về nhà 
Bài tập 
7.2 / 331-333 
7.3 / 335-336 
7.4 / 345-347 
Đọc 
Procedures stored in schema / 365-378 
Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 
61