Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 4: Ràng buộc dữ liệu - Hồ Lê Thị Kim Nhung

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 4: Ràng buộc dữ liệu - Hồ Lê Thị Kim Nhung

1. Nội dung 1 Giới thiệu 2 Phân loại 3 Qui tắc chung 4 User-defined Constraint
2. Mục tiêu Chuyển trách nhiệm kiểm tra RBDL cho SQL Server làm một cách tự động mỗi khi có thay đổi trên DL (Con người không cần kiểm tra thủ công)
3. Phân loại 1. Primary 2. Foreign 3. Check 4. Unique 5. Default 6. Rule 1. Trigger
4. Nội dung 1 Giới thiệu 2 Phân loại 3 Qui tắc chung 4 User-defined Constraint
5. RB trên thuộc tính Chỉ áp dụng CREATE TABLE Persons cho RB trên ( một thuộc tính P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
6. RB bên ngoài bảng ALTER TABLE Persons Cho phép đặt tên ràng buộc ADD CONSTRAINT Uc_Person UNIQUE (P_id, LastName) Áp dụng cho các RB có trên 2 thuộc tính
7. Qui tắc chung vHiệu lực hóa { Check | Foreign } constraint đã mất hiệu lực Alter table Check constraint {ALL | constraint_name [, n]} § Ví dụ : ALTER TABLE SINHVIEN CHECK CONSTRAINT C_Phai, chk_Nam
8. User-defined Constraint Mục tiêu Năm kết thúc khóa học phải lớn hơn hoặc bằng năm bắt đầu ü Cài đặt RBTV phức tạp üCác qui tắc nghiệp vụ Đặc điểm üLà một thủ tục đặc biệt üKhông có tham số üThuộc duy nhất một bảng Hoạt động üĐược kích hoạt tự động thông qua các thao tác (Insert, Update, Delete) üDựa trên bảng tạm: Inserted, Deleted
9. User-defined Constraint vCác bước xây dựng trigger § Bước 1: xác định bảng tầm ảnh hưởng. § Bước 2: với mỗi quan hệ trong bảng tầm ảnh hưởng xác định xử lí trên các thao tác khi vi phạm RBTV. § Bước 3: các thao tác có cùng xử lí sẽ được viết trong cùng một trigger.
10. User-defined Constraint vNếu thao tác insert/ delete/ update thực hiện trên nhiều dòng, trigger cũng chỉ được gọi một lần àBảng inserted/ deleted có thể chứa nhiều dòng
11. User-defined Constraint vInstead of: § Trigger được gọi thực hiện thay cho thao tác delete/ insert/ update tương ứng • Các dòng mới được thêm chỉ chứa trong bảng inserted • Các dòng bị chỉ định xoá nằm đồng thời trong bảng deleted và bảng dữ liệu (dữ liệu không bị xoá). § Trigger Instead of thường được dùng để xử lý cập nhật trên khung nhìn (view).
12. User-defined Constraint vVí dụ: Cho CSDL có 2 table § DonHang (MaDH, ,NgayDatHang) § PhieuGH (MaPG, MaDH, ,NgayGiaoHang) vRBTV : Ngày giao hàng phải sau ngày đặt hàng và không trễ quá 1 tháng kể từ ngày đặt hàng.
13. User-defined Constraint Create trigger trg_DH_PGH On DonHang For update As Begin If exists(select * from Insreted I, PhieuGH P Where P.MaDH=I.MaDH And (P.NgayGiaoHang 1)) Begin Raiserror(‘Ngay dat hang khong hop le’,16,1) Rollback transaction End Vi phạm RBTV nên phải hủy bỏ mọi End thay đổi, trở về trạng thái ban đầu
14. User-defined Constraint Update DONHANG Se NgayDatHang = ’18/5/2002’ Where MaDH = ‘DH1’ INSERTED MaDH NgayDatHang DONHANG DH1 18/1/2002 MaDH NgayDatHang DH1 18/1/2002 DH2 1/2/2001 DELETED MaDH NgayDatHang PhieuGH DH1 1/1/2002 MaPG MaDH NgayGiaoHang PG1 DH1 15/4/2002 PG2 10/4/2004
15. User-defined Constraint Create trigger trg_PGH On PhieuGH For update, Insert As Begin If exists(select * from Insreted I, PhieuGH P Where P.MaDH=I.MaDH And (P.NgayGiaoHang 1)) Begin Raiserror(‘Ngay dat hang khong hop le’,0,1) Rollback transaction End Vi phạm RBTV nên phải hủy bỏ mọi End thay đổi, trở về trạng thái ban đầu
16. User-defined Constraint Alter trigger trg_DH_PGH On DonHang For update, Insert As Begin If exists(select * from Insreted I, PhieuGH P Where P.MaDH=I.MaDH And (P.NgayGiaoHang 1)) Begin Raiserror(‘Thao tac da bi huy’,16,1) Rollback transaction End End
17. Nested trigger vĐịnh nghĩa § Nested trigger(recursive trigger) là khi một trigger được kích hoạt làm các trigger khác kích hoạt. § Hệ quản trị hỗ trợ tối đa 32 mức. Cú pháp: SP_CONFIGURE ‘Nested_Triggers',0 GO RECONFIGURE
18. Lược đồ CSDL
19. Bài tập 6. Sinh viên chỉ được nhập học từ 18 đến 22 tuổi. 7. Năm bắt đầu học của sinh viên phải nhỏ hơn năm kết thúc và lớn hơn năm thành lập của khoa đó. 8. Tình trạng của sinh viên là ‘Đã tốt nghiệp’ nếu điểm trung bình >=5.0 và năm kết thúc = năm hiện hành. Tình trạng là ‘Bị thôi học’ nếu điểm trung bình năm hiện hành.
20. Nhắc RBTV vMột quan hệ § Miền giá trị § Liên bộ § Liên thuộc tính vNhiề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 42
21. Ví dụ vThờ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 ) § Bảng tầm ảnh hưởng: R3 Thêm Xóa Sửa PHANCONG + − + (THOIGIAN)
22. Ví dụ vTê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 ) § Bảng tầm ảnh hưởng: R5 Thêm Xóa Sửa PHONGBAN + − + (TENPHG)
23. Ví dụ vMộ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 ≠ t.MANV ∨ t.MA_NQL = null ) § Bảng tầm ảnh hưởng: R8 Thêm Xóa Sửa NHANVIEN + − + (MA_NQL)
24. Ví dụ vMọ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: ∀t ∈ THANNHAN ( ∃s ∈ NHANVIEN ( s.MANV = t.MA_NVIEN )) hay THANNHAN.MA_NVIEN ⊆ NHANVIEN.MANV § Bảng tầm ảnh hưởng: R10 Thêm Xóa Sửa NHANVIEN − + + (MANV) THANNHAN + − + (MA_NVIEN)
25. Ví dụ HOADON(SOHD, MAKH, NGAYHD) CTHD(SOHD, MAHH, DGIA, SLG) vMỗi hóa đơn phải có ít nhất một chi tiết hóa đơn § Bối cảnh: HOADON, CTHD § Biểu diễn: ∀t ∈ HOADON ( ∃s ∈ CTHD ( t.SOHD = s.SOHD )) § Bảng tầm ảnh hưởng: R11 Thêm Xóa Sửa HOADON + + + (SOHD) CTHD + + + (SOHD)
26. Ví dụ vNgày sinh của trưởng phòng phải nhỏ hơn ngày nhận chức § Bối cảnh: NHANVIEN, PHONGBAN § Biểu diễn: ∀t ∈ PHONGBAN ( ∃s ∈ NHANVIEN ( s.MANV = t.TRPHG ∧ t.NG_NHANCHUC > s.NGSINH )) § Bảng tầm ảnh hưởng: R12 Thêm Xóa Sửa NHANVIEN − − + (NGSINH, MANV) PHONGBAN + − + (NG_NHANCHUC, TRPHG)
27. Ví dụ PHONGBAN(TENPHG, MAPHG, TRPHG, NG_NHANCHUC, SO_NV) vSố 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: NHANVIEN, PHONGBAN § Biểu diễn: ∀t ∈ PHONGBAN ( t.SO_NV = ∑ { s ∈ NHANVIEN | s.PHG = t.MAPHG} ) § Bảng tầm ảnh hưởng: R13 Thêm Xóa Sửa NHANVIEN + + + (PHG) PHONGBAN − − + (SO_NV, MAPHG)
28. Ví dụ vNhâ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 MANV=MA_NVIE SODA=MADA N MA_NVIEN, SODA PHANCONG
29. SQL Constraints vLỗi tạo PRIMARY § Thuộc tính khóa chưa đặt NOT NULL (tạo khóa bên ngoài) § Dữ liệu đã tồn tại không hợp lệ.(tạo khóa sau khi nhập liệu) vLỗi tạo FOREIGN § Thuộc tính bị tham chiếu chưa tồn tại. (tạo khóa trong bảng) § Bảng tham chiếu chưa có khóa chính. § Không cùng kiểu, chiều dài. §Dữ liệu không hợp lệ.
30. SQL Constraints § Ví dụ: Bảng PHONGBAN có thuộc tính TenPhongBan chứa giá trị không trùng. Giả sử bảng PHONGBAN đã tồn tại ALTER TABLE PHONGBAN ADD CONSTRAINT U_TenPhong UNIQUE (TenPhong)
31. Unique CREATE TABLE Persons CREATE TABLE Persons ( ( P_Id int NOT NULL UNIQUE, P_Id int NOT NULL, LastName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), FirstName varchar(255), Address varchar(255), Address varchar(255), City varchar(255) City varchar(255), ) UNIQUE (P_Id) ) CREATE TABLE Persons3 ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) )
32. Default trong bảng CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' ) CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, OrderDate date DEFAULT GETDATE() )