Bài giảng Cơ sở dữ liệu - Chương 7: Ràng buộc toàn vẹn - Nguyễn Thị Như Anh

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 7: Ràng buộc toàn vẹn - Nguyễn Thị Như Anh

1. 2/20/2019 Khái niệm ▪ RBTV (Integrety Constraints) được phát hiện từ ngữ nghĩa của dữ liệu hoặc thể hiện của dữ liệu trong thực tế (1). ▪ RBTV nhằm đảm bảo (1): ▪ Tính đúng của dữ liệu và mô hình dữ liệu. ▪ Ngữ nghĩa của CSDL ▪ Khi RBTV được khai báo, mọi thể hiện của quan hệ đều phải thỏa các RBTV này ở bất kỳ thời điểm nào (1). ▪ RBTV được phát hiện và khai báo bởi các thiết kế viên trong quá trình thiết kế dữ liệu (1). ▪ RBTV được định nghĩa trên một quan hệ hoặc liên quan đến nhiều quan hệ (1). (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 3 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 2
2. 2/20/2019 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ệ (biến bộ) – Mã giả (pseudo code) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 7 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 4
3. 2/20/2019 Bảng tầm ảnh hưởng một RBTV Tên_RB Thêm Xóa Sửa Quan hệ 1 + − + (Thuộc tính) Quan hệ 2 − + − Quan hệ n − + − (+) Vi phạm RBTV (−) Không vi phạm RBTV © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 11 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 6
4. 2/20/2019 Phân loại ▪ RBTV liên quan đến một quan hệ: • Miền giá trị • Liên bộ • Liên thuộc tính ▪ RBTV liên quan đến nhiều quan hệ: • 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 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 15 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 8
5. 2/20/2019 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) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 19 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 10
6. 2/20/2019 Ví dụ 8 ▪ Một giáo viên không trực tiếp quản lý chuyên môn chính mình - Bối cảnh: GIAOVIEN - Biểu diễn: (t)( GIAOVIEN(t)  (t.GVQLCM = null  t.GVQLCM t.MAGV) } - Bảng tầm ảnh hưởng: R8 Thêm Xóa Sửa GIAOVIEN + − + (GVQLCM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 23 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 12
7. 2/20/2019 Ví dụ 11 ▪ Mọi giáo viên phải thuộc về một bộ môn cụ thể. - Bối cảnh: BOMON, GIAOVIEN - Biểu diễn: (t)(GIAOVIEN(t)  s(BOMON(s)  s.MABM = t.MABM)) - Bảng tầm ảnh hưởng: R11 Thêm Xóa Sửa GIAOVIEN + - + (MABM) BOMON - + +(MABM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 27 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 14
8. 2/20/2019 Ví dụ 13 ▪ Mỗi đề tài phải có ít nhất một công việc thuộc về đề tài đó - Bối cảnh: DETAI, CONGVIEC - Biểu diễn: (t) (DETAI(t)  (s)(CONGVIEC(s)  t.MADT = s.MADT)) - Bảng tầm ảnh hưởng: R13 Thêm Xóa Sửa DETAI + - +(MADT) CONGVIEC - + + (MADT) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 31 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 16
9. 2/20/2019 Ví dụ 16 ▪ Phụ cấp của một công việc trong đề tài luôn luôn nhỏ hơn kinh phí của đề tài đó. - Bối cảnh: THAMGIADT, DETAI - Biểu diễn: (t)(THAMGIADT(t)  s(DETAI(s)  s.MADT = t.MADT  t.PHUCAP < s.KINHPHI)) - Bảng tầm ảnh hưởng: R16 Thêm Xóa Sửa THAMGIADT + − + (PHUCAP) DETAI + − + (KINHPHI) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 35 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 18
10. 2/20/2019 Ví dụ 17 ▪ 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 Phân công mong muốn Phân công thực sự MANV=MA_NVIEN SODA=MADA MA_NVIEN, SODA PHANCONG © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 39 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 − − + (PHG) DEAN − - + (PHONG) PHANCONG + − + (MA_NVIEN,SODA) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 40 20
11. 2/20/2019 Cài đặt ▪ Các RBTV được cài đặt bởi - Primary key - Foreign key - Check contraint - Assertion - Trigger - Transaction © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 43 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 22
12. 2/20/2019 Ví dụ 19 (tt) ▪ Lương của trưởng bộ môn phải lớn hơn 50000 ALTER TABLE BOMON ( Check Constraint TENBM NVARCHAR(50) UNIQUE, MABM CHAR(10) NOT NULL, TRUONGBM CHAR(10), NGAYNHANCHUC DATETIME, CONSTRAINT CHK_BM_LUONGTRUONGBM CHECK ( TRUONGBM NOT IN (SELECT MAGV FROM GIAOVIEN WHERE LUONG = ALL ( SELECT COUNT(MAGV) FROM GIAOVIEN GROUP BY MABM ) ) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 48 24
13. 2/20/2019 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 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 51 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 26
14. 2/20/2019 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 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 55 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 28
15. 2/20/2019 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 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 59 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 30
16. 2/20/2019 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 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 63 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 64 32