Chương 4 Ràng Buộc Dữ Liệu Nội dung 1 Giới thiệu 2 Phân loại 3 Qui tắc chung 4 User-defined Constraint Mục tiêu Mỗi thể hiện của RB về kích thước, thực thể là duy nhất định dạng, miền giá trị Thực thể Miền giá trị RB tự định Tham chiếu nghĩa RB nghiệp vụ mà RB tham chiếu 3 loại trên không giá trị kiểm tra được 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) Nội dung 1 Giới thiệu 2 Phân loại 3 Qui tắc chung 4 User-defined Constraint Phân loại 1. Trigger Ví dụ Rule CREATE RULE ur_LuongDuong AS @Luong>0 ur_LuongDuong sp_bindRule NHANVIEN MaNV … Luong sp_unbindRule NV1 … 10000 NV2 … 30 NV3 … -40 sp_bindRule ‘ur_LuongDuong’,’NhanVien.Luong’ sp_unbindRule ‘NhanVien.Luong’ Nội dung 1 Giới thiệu 2 Phân loại 3 Qui tắc chung 4 User-defined Constraint Qui tắc chung vSQL constraints có 3 cách tạo § Tạo trong tạo bảng Ø Ràng buộc trên thuộc tính Ø Ràng buộc trên bảng § Tạo ngoài bảng v Ví dụ : tạo ràng buộc Unique 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) ) RB trên bảng CREATE TABLE Persons3 ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Áp dụng cho các RB có trên Address varchar(255), Cho phép đặt 2 thuộc tính tên ràng buộc City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ) RB bên ngoài bảng Cho phép đặt ALTER TABLE Persons 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 Qui tắc chung vVô hiệu hóa { Check | Foreign } constraint đang có hiệu lực Alter table <Tab_name> Nocheck constraint {ALL | constraint_name [,…n]} § Ví dụ : ALTER TABLE SINHVIEN NOCHECK CONSTRAINT C_Phai, chk_Nam ðLưu ý: Primary, Unique, Default luôn ở trạng thái hoạt động Qui tắc chung vHiệu lực hóa { Check | Foreign } constraint đã mất hiệu lực Alter table <Tab_name> Check constraint {ALL | constraint_name [,…n]} § Ví dụ : ALTER TABLE SINHVIEN CHECK CONSTRAINT C_Phai, chk_Nam Ví dụ Ví dụ Nội dung 1 Giới thiệu 2 Phân loại 3 Qui tắc chung 4 User-defined Constraint 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 User-defined Constraint vInserted : Khi có thay đổi trên DL, có thể sẽ có một hoặc một số dòng được thêm vào bảng X. Các dòng này sẽ được đưa vào bảng inserted (có cấu trúc giống X). vDeleted : Khi có thay đổi trên DL, có thể sẽ có một hoặc một số dòng bị xóa đi.
Các dòng này sẽ được đưa vào bảng deleted (cấu trúc giống X). 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.
User-defined Constraint Quan hệ bị ảnh vCú pháp hưởng Create trigger tên_trigger On {tên_bảng|tên_view} {For| After| Instead of } { [delete] [,] [insert] [,] [update] } As Thao tác bị ảnh hưởng Begin { các lệnh T-sql } Xữ lí khi có vi End phạm RBTV Go 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 User-defined Constraint vFor | After: § Trigger được gọi thực hiện sau khi thao tác delete/ insert/ update tương ứng đã được thực hiện thành công • Các dòng mới được thêm chứa đồng thời trong bảng dữ liệu và bảng inserted • Các dòng bị xoá chỉ nằm trong bảng deleted (đã bị xoá khỏi bảng dữ liệu) § Có thể xử lý quay lui thao tác đã thực hiện bằng lệnh rollback transaction 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). User-defined Constraint vLưu ý: § Trên một bảng có thể định nghĩa nhiều trigger for/after cho mỗi thao tác… § Trong thân trigger, có thể sử dụng hàm Update(tên_cột) để kiểm tra xem việc cập nhật/ insert được thực hiện trên cột nào. • Update(tên_cột) = true : có thực hiện cập nhật trên cột tên_cột 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.
User-defined Constraint vBảng tầm ảnh hưởng insert delete update DonHang (NgayDatHang) PhieuGH (MaDH,NgayGiaoHang) User-defined Constraint Create trigger trg_DH_PGH On DonHang For update As Begin If exists(select * from Insreted I, PhieuGH P Where P.NgayDatHang Or Datediff(MM, I.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 User-defined Constraint Update DONHANG Se NgayDatHang = ’18/5/2002’ INSERTED Where MaDH = ‘DH1’ MaDH … NgayDatHang DONHANG MaDH … NgayDatHang DH1 … 18/5/2002 18/1/2002 1/1/2002 Vi phạm DH2 … 1/2/2001 RBTV DELETED MaDH … NgayDatHang PhieuGH MaPG MaDH … NgayGiaoHang PG1 DH1 … 15/4/2002 PG2 … 10/4/2004 User-defined Constraint Update DONHANG Se NgayDatHang = ’18/5/2002’ INSERTED Where MaDH = ‘DH1’ 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 User-defined Constraint vBảng tầm ảnh hưởng insert delete update DonHang (NgayDatHang) PhieuGH (MaDH,NgayGiaoHang) Viết gộp Viết riêng User-defined Constraint Create trigger trg_PGH On PhieuGH For update, Insert As Begin If exists(select * from Insreted I, PhieuGH P Where P.NgayDatHang Or Datediff(MM, I.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 User-defined Constraint vThay đổi nội dung trigger: Thay từ khóa create trong các lệnh tạo trigger bằng từ khóa alter 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.NgayDatHang Or Datediff(MM, I.NgayGiaoHang) > 1)) Begin Raiserror(‘Thao tac da bi huy’,16,1) Rollback transaction End End User-defined Constraint vXóa trigger Drop trigger tên_trigger_cần _xóa [,…n] Ví dụ: Drop trigger trg_DH_PGH 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 Lược đồ CSDL SinhVien (MaSV, HoTen, DiemTB, MaLop, NamSinh, NamBD, NamKT, TinhTrang) LopHoc (MaLop, MaKhoa, SiSo) Khoa (MaKhoa, TenKhoa, NamThanhLap) MonHoc (MaMH, TenMonHoc, SoChi, MaKhoa) KetQua (MaSV, MaMH, LanThi, Diem) Lược đồ CSDL Bài tập 1. Sinh viên chỉ được học các môn của khoa mình mở.
Sinh viên chỉ được thi lại nếu điểm của lần thi sau cùng < 5 và số lần thi < 3. Số lượng sinh viên (nếu có) bằng số sinh viên của lớp đó. Xóa một sinh viên phải xóa tất cả các tham chiếu đến sinh viên đó. Điểm trung bình (nếu có) phải bằng tổng điểm / tổng tín chỉ.
Sinh viên chỉ được nhập học từ 18 đến 22 tuổi. 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 đó. 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à ‘Đang học nếu’ 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 <5.0 và năm kết thúc > năm hiện hành. Thank You! www.com 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 RBTV - Miền giá trị vRà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 vMiền giá trị § Liên tục § Rời rạc 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) RBTV - Liên bộ vSự 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 vTrường hợp đặc biệt § RB khóa chính § RB duy nhất (unique) 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 ) § Bảng tầm ảnh hưởng: R5 Thêm Xóa Sửa PHONGBAN + − + (TENPHG) RBTV - Liên thuộc tính vLà 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 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 = null ) § Bảng tầm ảnh hưởng: R8 Thêm Xóa Sửa NHANVIEN + − + (MA_NQL) RBTV - Tham chiếu vGiá 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 vTrường hợp đặc biệt § RB khóa ngoại 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.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)