?
Ch ơng 7
RÀNG BU C TOÀN V N
Bộ môn Hệ thống thông tin
Khoa CNTT – Đại học Khoa học tự nhiên, TpHCM
Nội dung
•
•
•
•
Khái niệm
Các đặc trưng của RBTV
Phân loại
C̀i đặt
KHÁI NIỆM
Khái niệm
• RBTV (R̀ng buộc tòn vẹn - Integrety Constraints)
• RBTV xuất phát từ những qui định hay điều kiện
– Trong thực tế
– Trong mô hình dữ liệu
• Các thao tác l̀m thay đổi dữ liệu không nên được thực hiện một cách tùy tiện vì có thể
đưa CSDL đến một tình trạng ‘xấu’
• RBTV l̀ một điều kiện được định nghĩa trên một hay nhiều quan hệ
khác nhau
• L̀ những điều kiện bất biến đối với mọi quan hệ
4
Khái niệm (tt)
• Tại sao cần phải có RBTV?
– Bảo đảm tính kết dính của các thành phần cấu tạo nên CSDL
– Bảo đảm tính nhất quán của dữ liệu
– Bảo đảm CSDL luôn biểu diễn đúng ngữ nghĩa thực tế
• Ví dụ
– Mức lương của một người nhân viên không được vượt quá mức lương của trưởng phòng (R1)
– Người quản lý trực tiếp (của một nhân viên) phải là một nhân viên trong công ty (R2)
5
CÁ C Đ Ặ C T R N G C Ủ A
RBTV
• Bối cảnh
• Nội dung
• Bảng tầm ảnh hưởng
Mô tả r̀ng buộc tòn vẹn
• Bối cạnh :
– ……
– ……
• Nội dung:
– …..
• Bảng tầm ảnh hưởng:
Thêm
Xóa
Cập nhật
R1
+
-
-
R2
-
+
+ (attr1)
CÁC ĐẶC TR NG CỦA RBTV
• Bối cảnh
• Nội dung
• Bảng tầm ảnh hưởng
Bối cảnh
• Bối cảnh của một RBTV
– L̀ những quan hệ có khả năng bị vi phạm RBTV khi thực hiện các phép
cập nhật
• Ví dụ (R1)
– Mức lương của một người nhân viên không được vượt quá trưởng phòng
• Các phép cập nhật
– Cập nhật lương cho nhân viên
– Thêm mới một nhân viên v̀o một phòng ban
– Bổ nhiệm trưởng phòng cho một phòng ban
• Bối cảnh: NHANVIEN, PHONGBAN
9
Bối cảnh (tt)
• Ví dụ (R2)
– Người quản lý trực tiếp phải là một nhân viên trong công ty
• 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 nhân viên
– Thêm mới một nhân viên
• Bối cảnh: NHANVIEN
10
CÁC ĐẶC TR NG CỦA RBTV
• Bối cảnh
• Nội dung
• Bảng tầm ảnh hưởng
Nội dung
• Nội dung của một RBTV được phát biểu bằng
– Ngôn ngữ tự nhiên
• Dễ hiểu nhưng thiếu tính chặt chẽ
– Ngôn ngữ hình thức
• Cô đọng, chặt chẽ nhưng đôi lúc khó hiểu
• Biểu diễn thông qua
– Đại số quan hệ
– Phép tính quan hệ
– Mã giả (pseudo code)
12
Nội dung (tt)
• Ví dụ (R1)
– Ngôn ngữ tự nhiên
• Mức lương của một người nhân viên không được vượt quá mức lương của trưởng
phòng
– Ngôn ngữ hình thức
t NHANVIEN (
u PHONGBAN ( v NHANVIEN (
u.TRPHG v.MANV
u.MAPHG t.PHG
t.LUONG v.LUONG )))
13
PHONGBAN
NHANVIEN
MANV
HOTEN
PHG
LUONG
MAPHG
TENPHG
TRPHG
1
Tùng
1
45000
1
Điều Hành
1
2
Tiế
3
50000
3
Nghiên cứu
2
3
Trang
4
15000
4
Kinh Doanh
7
4
Trung
3
60000
5
Thủy
1
20000
6
Tuấ
4
35000
7
T ườ g
4
45000
Có ội ph m ràng bỘ c toàn ộ n ?
t NHANVIEN (
u PHONGBAN ( v NHANVIEN (
u.TRPHG v.MANV
u.MAPHG t.PHG
t.LUONG v.LUONG )))
14
Nội dung (tt)
• Ví dụ (R2)
– Ngôn ngữ tự nhiên
• Người quản lý trực tiếp phải l̀ một nhân viên trong công ty
– Ngôn ngữ hình thức
t NHANVIEN ( t.MA_NQL = null
s NHANVIEN (s.MANV = t.MA_NQL))
t NHANVIEN ( t.MA_NQL != null
s NHANVIEN (s.MANV = t.MA_NQL))
15
NHANVIEN
MANV
HOTEN
PHG
LUONG
MA_NQL
1
Tùng
1
45000
null
2
Tiế
3
50000
1
3
Trang
4
15000
null
4
Trung
3
60000
3
5
Thủy
1
20000
4
6
Tuấ
4
35000
null
7
T ườ g
4
45000
6
8
Thanh
4
45000
9
vi phạm RBTV
t NHANVIEN ( t.MA_NQL != null
s NHANVIEN (s.MANV = t.MA_NQL))
16
CÁC ĐẶC TR NG CỦA RBTV
• Bối cảnh
• Nội dung
• Bảng tầm ảnh hưởng
Bảng tầm ảnh hưởng
• Bảng tầm ảnh hưởng
– Xác định thao tác cập nhật ǹ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
18
Bảng tầm ảnh hưởng một RBTV
Tên_RB
Quan hệ 1
Quan hệ 2
…
Quan hệ n
Thêm
Xóa
Sửa
(Thuộc tính)
() Có vi phạm RBTV
() Không vi phạm RBTV
19
Bảng tầm ảnh hưởng
• Phái của nhân viên phải l̀ Nam hoặc Nữ
– Bối cảnh: NHANVIEN
– Nội dung:
t NHANVIEN ( t.PHAI = 'Nam' t.PHAI = 'Nu'
t.PHAI = NULL )
hoặc
t NHANVIEN ( t.PHAI != NULL t.PHAI= 'Nam'
t.PHAI = 'Nu')
– Bảng tầm ảnh hưởng:
NHANVIEN
Thêm
Xóa
+
-
Sửa
+(PHAI)
20
Bảng tầm ảnh hưởng
• Phòng của nhân viên phải là một trong những phòng của phòng
ban
– Bối cảnh: PHONGBAN, NHANVIEN
– Bảng tầm ảnh hưởng:
Thêm
Xóa
Sửa
NHANVIEN
+
-
+(PHG)
PHONGBAN
-
+
+ (MAPHG)
21
Bảng tầm ảnh hưởng
• Người quản lý trực tiếp của nhân viên nếu có thì phải l̀ một
nhân viên trong công ty
– Bối cảnh:
– Bảng tầm ảnh hưởng:
NHANVIEN
Thêm
Xóa
+
+
Sửa
+ (MA_NQL, MANV)
22
Bảng tầm ảnh hưởng
• Cho lược đồ:
NHANVIEN (MANV, HOTEN, PHAI, TUOIVEHUU)
• RBTV: Tuổi về hưu của nhân viên Nam là 60, của nhân viên nữ là 55.
- Bối cảnh:
- Bảng tầm ảnh hưởng:
NHANVIEN
Thêm
Xóa
+
-
Sửa
+ (PHAI, TUOIVEHUU)
23
Bảng tầm ảnh hưởng tổng hợp
Ràng buộc 1
R̀ng buộc 2
T
X
S
T
X
S
Quan hệ 1
+
-
+
+
-
+
Quan hệ 2
-
+
-
Quan hệ 3
-
-
+
Ràng buộc m
…
…
…
T
X
S
+
-
+
-
+
-
-
-
+
…
Quan hệ n
-
+
-
24
1.
2.
3.
4.
5.
6.
7.
8.
Miền giá trị
Liên bộ
Liên thuộc tính
Tham chiếu
Liên bộ - liên quan hệ
Liên thuộc tính - liên quan hệ
Thuộc tính tổng hợp
Chu trình
PHÂN LO I
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
12
3
23
9
9{,
,
{1..10}
}
• Miền giá trị
– Liên tục
– Rời rạc
26
Ví dụ 3
• Thời gian tham gia đề án của một nhân viên không quá 60 giờ
– Bối cảnh: PHANCONG
– Biểu diễn:
t PHANCONG ( t.THOIGIAN 60 0 t.THOIGIAN)
– Bảng tầm ảnh hưởng:
R3
PHANCONG
Thêm
Xóa
Sửa
(THOIGIAN)
27
Ví dụ 4
• Giới tính của nhân viên l̀ ‘Nam’ hoặc ‘Nu’
28
?
Ví dụ 4b
• Giới tính của nhân viên nếỘ có thì phải l̀ ‘Nam’ hoặc ‘Nu’
29
RBTV - Liên bộ
• Sự tồn tại của một hay nhiều bộ phụ thuộc v̀o sự tồn tại của một
hay nhiều bộ khác trong cùng quan hệ
R
A
B
C
D
1
1
5
7
12
3
23
9
• Trường hợp đặc biệt
– RB khóa chính
– RB duy nhất (unique)
30
Ví dụ 5
• R5: Tên phòng l̀ duy nhất
– Bối cảnh: PHONGBAN
– Biểu diễn:
t1, t2 PHONGBAN (
t1 t2 t1.TENPHG t2.TENPHG )
hoặc
t PHONGBAN ( ¬uPHONGBAN ( u != t
u.TENPHG=t.TENPHG)
– Bảng tầm ảnh hưởng:
R5
PHONGBAN
Thêm
Xóa
Sửa
(TENPHG)
31
Ví dụ 6
• R6: Một nhân viên được tham gia tối đa 5 đề án
32
Ví dụ 7
THIDAU(NGAY, GIO, DOI, SOBAN)
• Mỗi trận đấu l̀ cuộc thi đấu của đúng 2 đội
33
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
34
Ví dụ 8
• Một nhân viên không quản lý trực tiếp chính mình
– Bối cảnh: NHANVIEN
– Biểu diễn:
t NHANVIEN (t.MA_NQL null t.MA_NQL t.MANV )
– Bảng tầm ảnh hưởng:
R8
NHANVIEN
Thêm
+
Xóa
Sửa
(MA_NQL)
35
Ví dụ 9
KHOAHOC(MAKH, TENKH, BDAU, KTHUC)
• Mỗi khóa học kéo dài ít nhất 3 tháng
36
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
1
1
5
7
12
3
23
9
S
E
F
7
1
3
2
Bắt buộc phải tồn tại trước
• Trường hợp đặc biệt
– RB khóa ngoại
37
Ví dụ 10
• Mọi thân nhân phải có mối quan hệ gia đình với một nhân viên trong công
ty
– Bối cảnh: THANNHAN, NHANVIEN
– Biểu diễn:
hay
t THANNHAN ( s NHANVIEN ( s.MANV t.MA_NVIEN ))
THANNHAN.MA_NVIEN NHANVIEN.MANV
– Bảng tầm ảnh hưởng:
R10
NHANVIEN
THANNHAN
Thêm
Xóa
Sửa
(MANV)
(MA_NVIEN)
38
RBTV - Tham chiếu (tt)
• Còn gọi là phụ thuộc tồn tại
• Thường có bối cảnh là hai quan hệ
– Nhưng có trường hợp suy biến thành một quan hệ
• Ví dụ (10b): Người quản lý trực tiếp phải là một nhân viên trong công ty
39
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
S
B
C
D
1
1
5
7
12
3
23
9
A
B
C
2
7
4
7
2
3
2
10
40
Ví dụ 11
• Cho lược đồ CSDL:
– 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
41
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
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
42
Ví dụ 12
• Ngày sinh của trưởng phòng phải nhỏ hơn ngày nhận chức:
43
NHANVIEN
MANV
HOTEN
PHG
LUONG
NGSINH
1
Tùng
1
45000
1/12/1965
2
Tiế
3
50000
null
3
Trang
4
15000
null
4
Trung
3
60000
1/12/1965
5
Thủy
1
20000
4/10/1972
6
Tuấ
4
35000
null
7
T ườ g
4
45000
1/12/1981
PHONGBAN
MAPHG
TENPHG
TRPHG
NGAY_NHAN_CHUC
1
Điều Hành
1
31/1/1990
3
Nghiên cứu
2
15/04/1996
4
Kinh Doanh
7
18/10/1978
44
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
45
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 đó
46
RBTV – Chu trình
• Lược đồ CSDL có thể được biểu diễn bằng đồ thị
– Đỉnh
Tê
• Quan hệ
• Thuộc tính
ua hệ
Tê thuộc tí h
– Cạnh
• Đường nối một đỉnh quan hệ với một đỉnh thuộc tính trong lược đồ CSDL
Tê
ua hệ
Tê thuộc tí h
• Chu trình
– Đồ thị xuất hiện đường đi khép kín ~ Lược đồ CSDL có chu trình
47
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:
PHG=PHONG
NHANVIEN
MANV, MADA
DEAN
SODA=MADA
MANV=MA_NVIEN
MA_NVIEN, SODA
PHANCONG
PHANCONG
48
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: NHANVIEN, DEAN, PHANCONG
– Biểu diễn:
NVDA NHANVIEN
PHG=PHONG DEAN
t PHANCONG ( s NVDA (
t.MA_NVIEN s.MANV t.MADA s.SODA ))
– Bảng tầm ảnh hưởng:
R14
NHANVIEN
Thêm
Xóa
DEAN
PHANCONG
Sửa
(MANV,PHG)
(MADA,PHONG)
(MA_NVIEN,SODA)
49
NHANVIEN
DEAN
MANV
HOTEN
PHG
MADA
TENDA
PHONG
1
Tùng
1
10
Sả phẩ
X
1
2
Tiế
3
30
Sả phẩ
Y
3
3
Trang
4
70
Sả phẩ
Z
4
4
Trung
3
90
Sả phẩ
T
3
PHANCONG
MANV
MADA
1
10
3
30
1
70
3
70
2
90
50
Ví dụ 15
• Người quản lý của nhân viên v̀ nhân viên phải cùng phòng
NHANVIEN
MANV
HOTEN
PHG
LUONG
MA_NQL
1
Tùng
1
45000
null
2
Tiế
3
50000
1
3
Trang
4
15000
null
4
Trung
4
60000
3
5
Thủy
1
20000
1
6
Tuấ
4
35000
null
7
T ườ g
4
45000
6
Vi ph m
51
Ví dụ 16
• DDH (MADH, NGAYDH, MAHD)
• CT_DDH (MADH, MAMH, SOLUONG)
• HOADON (MAHD, NGAYGH, TONGTIEN, MADH)
• CT_HOADON (MACT, MAHD, MAMH, SOLUONG, DONGIA)
R̀ng buộc tòn vẹn: Chỉ giao h̀ng các mặt h̀ng có đặt.
52
CÀI ĐẶT RÀNG BU C TOÀN V N
-
Trigger
Transaction (giao tác)
Stored Procedure (thủ tục lưu trữ nội)
C̀i đặt
• Các RBTV được c̀i đặt bởi
– Primary key
– Foreign key
– Check contraint
– Trigger
– Transaction
54
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
trước
sau
giá trị mới
giá trị cũ
Biến cố
Điều kiện
Thỏa
Không thỏa
Tập hợp các lệnh
row-level
statement-level
Thông báo lỗi
55
Trigger (tt)
• Cú pháp
CREATE TRIGGER <Tên_trigger>
AFTER|BEFORE
INSERT|UPDATE|DELETE
<Tên_bảng>
REFERENCING
NEW
ROW|TABLE
AS
<Tên_1>
OLD
ROW|TABLE
AS
<Tên_2>
FOR EACH ROW | FOR EACH STATEMENT
WHEN (<Điều kiện>)
<Tập_lệnh_SQL>
ON
DROP TRIGGER <Tên_trigger>
56
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
57
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
FROM NHANVIEN
WHEN (50000 >= (SELECT LUONG
WHERE MANV=NewTuple.TRPHG))
UPDATE PHONGBAN
SET TRPHG=OldTuple.TRPHG
WHERE TRPHG=NewTuple.TRPHG
58
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
59
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
60
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
61
Ví dụ 7
• Mỗi trận đấu l̀ cuộc thi đấu của đúng 2 đội
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
62
Ví dụ 7 (tt)
Giao tác Xóa_trận_đấu(ngay, gio)
Với mọi sTHIDAU (s.NGAYngay s.GIOgio)
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
63
Ví dụ 11
• Mỗi hóa đơn phải có ít nhất một chi tiết hóa đơn
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
64
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 <Tên_thủ_tục> <DS_tham_số>
AS
Khai báo biến cục bộ
Thân chương trình
GO
EXEC <Tên_thủ_tục> <DS_ tham_số>
65
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
Thêm s vào THIDAU
If @@error<>0
commit tran
rollback tran
GO
EXEC Thêm_trận_đấu
x, y
66
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ạp
– Stored Procedure hiệu quả cao
67
CÂU HỎI ?
68