Giáo trình Giao tác - Cơ sở dữ liệu | Trường Đại học Khoa học Tự nhiên, Đại học Quốc gia Thành phố Hồ Chí Minh

lOMoARcPSD|46342985 lOMoARcPSD|46342985 CTTXXX – H ệ Qu n ả Tr ị CSDL Tháng 1/2013 GIAO TÁC (Transaction) Tóm t t ắ n i ộ dung bài th c ự hành: - Gi i ớ thi u ệ khái ni m ệ giao tác - Đặc đi m ể c a ủ giao tác - Cách cài đặt giao tác - Cơ ch ế c p ấ phát khóa c a ủ HQT trong giao tác
Bộ môn Hệ thống thông tin Khoa Công nghệ thông tin lOMoARcPSD|46342985 M C Ụ L C Ụ 1 Mục tiêu và tóm t t ắ n i
ộ dung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 2 Hư n ớ g dẫn cụ th
ể . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2.1 Giao tác . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 2.2 Ví d ụ minh h a
ọ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 2.3 B n ả g cấp phát khóa c a
ủ MS SQLSERVER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.4Bảng truy xu t ấ d ữ li u
ệ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3 Bài tập tại l p
ớ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 4 Bài tập v
ề nhà . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 lOMoARcPSD|46342985 ĐỊNH NGHĨA C U
Ấ TRÚC GIAO TÁC TRONG MS SQLSERVER 1 M c ụ tiêu và tóm t t ắ n i ộ dung
Cài đặt giao tác trên MS SQLSERVER. Sau khi hoàn thành bài t p ậ này sinh viên có th : ể - Hi u ể đư c ợ khái ni m ệ giao tác - Bi t ế cách cài đ t
ặ giao tác trên MS SQLSERVER - Bi t ế cách xác đ n ị h b n ả g truy xu t ấ d ữ li u ệ c a ủ MS SQLSERVER. 2 Hư n ớ g d n ẫ c ụ thể 2.1 Giao tác Đ n ị h nghĩa - Giao tác là t p ậ các thao tác có th ứ t , ự truy xu t ấ d ữ li u ệ trên CSDL. - Giao tác chuy n ể CSDL t ừ tr n ạ g thái nh t ấ quán này sang tr n ạ g thái nh t ấ quán khác. Tính ch t ấ
- Tính nguyên tố (atomicity): Giao tác là đ n ơ v ịnh ỏ nh t ấ không chia c t ắ đư c ợ nữa. - Tính nh t
ấ quán (consistency): Các thao tác c p ậ nh t ậ thông tin trong CSDL c a ủ giao tác không làm m t ấ tính nh t ấ quán c a ủ CSDL (tính b t ấ h p ợ lí gi a ữ các d ữ li u ệ trong CSDL) - Tính cô l p
ậ (isolation): Giao tác th c ự hi n ệ m t ộ cách đ c ộ l p ậ ,không ph ụ thu c ộ vào các giao tác khác. - Tính b n ề v n
ữ g (durability): K t ế qu ả giao tác đư c ợ l u ư tr ữ b n ề v n ữ g trong CSDL. Cài đ t ặ - Đ n ơ gi n ả BEGIN TRANSACTION Bộ môn Hệ th n
ố g thông tin | Khoa CNTT | ĐH KHTN TP HCM | 1/2013 Trang 1 lOMoARcPSD|46342985 --CÂU L N Ệ H SQL
COMMIT TRANSACTION --N U Ế THÀNH CÔNG, HO C Ặ ROLLBACK NẾU TH T Ấ B I Ạ - C ó x ử lí l i ỗ BEGIN TRANSACTION BEGIN TRY --CÂU L N Ệ H SQL END TRY BEGIN CATCH
ROLLBACK TRANSACTION –N U Ế TH T Ấ
BẠI END CATCH
COMMIT TRANSACTION --N U Ế THÀNH CÔNG 2.2 Ví d ụ minh h a ọ
TAIKHOAN (USERNAME, PASS, STATUS) Vi t ế giao tác th c ư hi n ệ ch c ứ năng đăng nh p ậ vào h ệ th n ố g - Đ ầu vào : username, pass - Đ ầu ra : k t ế quả đăng nh p ậ - N i ộ dung O Ki m ể tra username có t n ồ t i ạ ko? § N u ế không t n ồ t i ạ • Xuất thông báo • Thoát O Ki m ể tra m t ậ kh u ẩ có tư n ơ g thích v i ớ username không § N u ế không tư n ơ g thích • Xuất thông báo • Thoát § Xuất k t ế qu ả ki m ể tra đăng nh p ậ thành công lOMoARcPSD|46342985 Nhận xét:
- Các thao tác trong giao tác trên không th ể tách r i ờ đư c ợ . N u ế 1 thao tác đư c ợ th c ự thi thì t t
ấ cả các thao tác khác ph i ả đư c ợ th c ự thi thành công và ngư c ợ l i ạ . - Giao tác đư c ợ khai báo tư n
ờ g minh (BEGIN TRANSACTION) nên ph i ả đư c ợ hủy tư n ờ g minh b n
ằ g cách dùng (ROLLBACK hoặc COMMIT TRANSACTION) Cài đặt: Vi t ế dư i ớ d n ạ g 1 th ủ t c ụ
CREATE PROC USP_DANGNHAP @username char(10), @pass char(10) AS BEGIN TRANSACTION BEGIN TRY IF NOT EXISTS(SELECT * FROM TAIKHOAN WHERE username = @username ) BEGIN PRINT ‘USERNAME không t n ồ t i ạ ’ ROLLBACK TRANSACTION RETURN END IF NOT EXISTS(SELECT * FROM TAIKHOAN Bộ môn Hệ th n
ố g thông tin | Khoa CNTT | ĐH KHTN TP HCM | 1/2013 Trang 3 lOMoARcPSD|46342985
WHERE username = @username and pass = @pass) BEGIN PRINT ‘Sai m t ậ kh u ẩ không t n ồ t i ạ ’ ROLLBACK TRANSACTION RETURN END END TRY BEGIN CATCH PRINT ‘Lỗi đăng nh p ậ ’ ROLLBACK TRANSACTION END CATCH PRINT ‘Đăng nh p ậ thành công’ COMMIT TRANSACTION GO 2.3 B n ả g c p ấ phát khóa c a ủ MS SQLSERVER Shared Lock Update Lock Exclusive lock (S) (U) (X) SELECT þ þ þ UPDATE DELETE þ INSERT þ
Khóa S được trả ngay khi thực hiện xong thao tác đọc (nếu không đặt mức cô lập)
Khóa X được giữ đến hết giao tác lOMoARcPSD|46342985 2.4 B n ả g truy xu t ấ d ữ li u ệ Câu lệnh Khóa Dữ liệu SELECT * FROM TAIKHOAN S TAIKHOAN WHERE username = @username SELECT * FROM TAIKHOAN S TAIKHOAN WHERE username = @username and pass = @pass 3 Bài t p ậ t i ạ l p ớ Lư c ợ đồ CSDL:
+ TaiKhoan(MaTK, NgayLap, SoDu, TrangThai, LoaiTK, MaKH)
+ LoaiTaiKhoan (MaLoai, TenLoai)
+ KhachHang(MaKH, HoTen, NgaySinh, CMND, DiaChi)
+ GiaoDich(MaGD, MaTK, SoTien, ThoiGianGD, GhiChu) Vi t ế các giao tác sau: 1. Vi t ế giao tác xem s ố d ư tài kho n ả . N i ộ dung: - Input: MaTK - Output: s ố d ư tài kho n ả § Ki m ể tra MaTK đã t n t ồ i ạ • N u ế MaTK không t n ồ t i ạ xu t ấ thông báo à [MaTK] không t n ồ t i ạ . Thoát kh i ỏ th ủ t c ụ . § Ki m ể tra Trạng thái c a ủ tài kho n ả • N u ế Tr n
ạ g thái = “Đã khóa” xu t ấ thông báo à [MaTK] đã bị khóa. § Xu t ấ s d ố r ư a màn hình 2. Vi t ế giao tác thêm tài kho n ả m i ớ . N i ộ dung: - Input: thông tin tài kho n ả Bộ môn Hệ th n
ố g thông tin | Khoa CNTT | ĐH KHTN TP HCM | 1/2013 Trang 5 lOMoARcPSD|46342985
- Output: 0 – Thêm thành công
1 – Thêm không thành công § Ki m ể tra MaTK có t n ồ t i ạ • N u ế MaTK đã t n ồ t i ạ xu t
ấ thông báo à [MaTK] đã t n ồ tại. Thoát kh i ỏ th ủ t c ụ . § Ki m ể tra s d ố t ư ài kho n > ả = 100000 VND • N u ế vi phạm xu t ấ thông báo à [Số d ] ư không h p ợ l . ệ Thoát kh i ỏ th ủ t c ụ . § Ki m
ể tra Trạng thái <> Null • N u ế Tr n
ạ g thái = Null thì gán Tr n
ạ g thái = “Đang dùng” § Ki m ể tra lo i ạ tài kho n ả t n t ồ i ạ • N u ế loại tài kho n ả không t n ồ t i ạ , xu t ấ thông báo à [Loại tài kho n ả ] không t n ồ t i ạ . Thoát kh i ỏ th ủ t c ụ . § Ki m ể tra MaKH t n t ồ i ạ 3. Vi t ế giao tác xóa tài kho n ả . - Input: MaTK
- Output: 0 – Xóa thành công 1 – Xóa không thành công § Ki m ể tra MaTK đã t n t ồ i ạ • N u ế MaTK không t n ồ t i ạ xu t ấ thông báo à [MaTK] không t n ồ t i ạ và thoát. § Ki m ể tra tài kho n đ ả ã th c ự hi n ệ giao d ch ị nào ch a ư • N u ế tài kho n ả ch a ư th c ự hi n ệ giao d c ị h nào à Xóa tài kho n ả . • Ngư c ợ lại, xu t ấ thông báo tài kho n ả đã th c ự hi n ệ giao dịch không th ể xóa. § Xu t ấ thông báo k t ế qu xó ả a. 4. Cập nh t ậ thông tin tài kho n ả . - Input: MaTK, Ngày l p ậ , s ố d , ư tr n ạ g thái - Output: Output: 0 – c p ậ nh t ậ thành công lOMoARcPSD|46342985 1 – cập nh t ậ không thành công § Ki m ể tra MaTK đã t n t ồ i ạ • N u ế MaTK không t n ồ t i ạ xu t ấ thông báo à [MaTK] không t n ồ t i ạ và thoát. § Ki m ể tra ngày l p ậ khác null à Xu t ấ thông báo ngày l p ậ không h p ợ l ệ n u ế vi ph m ạ và thoát kh i ỏ thủ t c. ụ § Ki m ể tra s d ố > ư 100000 VND à Xu t ấ thông báo s d ố k ư hông h p ợ l và ệ thoát kh i ỏ thủ t c n ụ u ế vi ph m. ạ § Ki m ể tra trạng thái ph i ả thu c ộ trong t p
ậ {đang dùng, đã khóa, b h ị y} ủ à N u ế vi phạm xu t
ấ thông báo và thoát kh i ỏ th t ủ c. ụ § Th c ự hi n ệ c p ậ nh t ậ thông tin m i ớ . 4 Bài t p ậ v ề nhà Lư c ợ đồ CSDL: Bộ môn Hệ th n
ố g thông tin | Khoa CNTT | ĐH KHTN TP HCM | 1/2013 Trang 7 lOMoARcPSD|46342985
Bài 1: Hãy cài đ t ặ các giao tác sau 1. Thêm công vi c ệ Input: thông tin công vi c ệ
Output: 0 – Thêm thành công. 1 – Thêm không thành công - Ki m ể tra thông tin nh p ậ không đư c ợ r n ỗ g. - Ki m ể tra thông tin nh p ậ t n ồ t i ạ . - Ki m ể tra ngày b t ắ đ u ầ công vi c ệ ph i ả sau ngày b t ắ đ u ầ đ ề tài và trư c ớ ngày k t ế thúc đ ề tài. - Thêm công vi c ệ m i ớ 2. Cập nh t ậ công vi c ệ Input: thông tin công vi c ệ Output: 0 – C p ậ nh t ậ thành công. 1 – C p ậ nh t ậ không thành công lOMoARcPSD|46342985 - Ki m ể tra thông tin nh p ậ không đư c ợ r n ỗ g. - Ki m ể tra thông tin nh p ậ t n ồ t i ạ . - Ki m ể tra ngày b t ắ đ u ầ công vi c ệ ph i ả sau ngày b t ắ đ u ầ đ ề tài và trư c ớ ngày k t ế thúc đ ề tài. - Thông tin ngoài khóa ph i ả có thay đ i ổ v i ớ thông tin ban đ u ầ - Cập nh t ậ thông tin 3. Xóa công vi c ệ Input: Khóa trong b n ả g công vi c ệ
Output: 0 – Xóa thành công. 1 – Xóa không thành công - Ki m ể tra khóa t n ồ t i ạ - Ki m ể tra công vi c ệ ch a ư đư c ợ phân công - Xóa công vi c ệ . - Ki m ể tra đ
ề tài không còn công vi c ệ nào thì xóa đ ề tài đó. 4. Thêm đ ề tài Input: thông tin đ ề tài
Output: 0 – Thêm thành công. 1 – Thêm không thành công - Ki m ể tra thông tin nh p ậ không đư c ợ r n ỗ g. - Ki m ể tra thông tin nh p ậ h p ợ l . ệ - Ki m ể tra thông tin đ u ầ vào t n ồ t i ạ . - GVCNTDT phải là trư n ở g b ộ môn h c ọ trư n ở g khoa. - Cấp quản lí cao h n ơ thì kinh phí cho đ ề tài ph i ả cao h n ơ . - Thêm đ ề tài 5. Cập nh t ậ đ ề tài Input: thông tin đ ề tài Output: 0 – C p ậ nh t ậ thành công. 1 – C p ậ nh t ậ không thành công - Ki m ể tra thông tin nh p ậ không đư c ợ r n ỗ g. - Ki m ể tra thông tin nh p ậ h p ợ l . ệ - Ki m ể tra thông tin đ u ầ vào t n ồ t i ạ . - GVCNTDT phải là trư n ở g b ộ môn h c ọ trư n ở g khoa. Bộ môn Hệ th n
ố g thông tin | Khoa CNTT | ĐH KHTN TP HCM | 1/2013 Trang 9 lOMoARcPSD|46342985 - Cấp quản lí cao h n ơ thì kinh phí cho đ ề tài ph i ả cao h n ơ .
- Cấp quản lí ch ỉđư c ợ nâng lên không đư c ợ h ạ xu n ố g. - Cập nh t ậ đ ề tài 6. Xóa đ ề tài Input: Mã đề tài
Output: 0 – Xóa thành công. 1 – Xóa không thành công - Ki m ể tra mã đ ề tài t n ồ t i ạ - Ki m ể tra đ ề tài ch a ư có tham gia. - Ki m ể tra đ ề tài ch a ư k t ế thúc. - Xóa đ ề tài
Bài 2: Hãy mô tả và cài đ t ặ các giao tác sau 1. Thêm ngư i ờ thân 2. Thêm giáo viên 3. Cập nh t ậ trư n ở g b ộ môn 4. Cập nh t ậ ch ủ nhi m ệ đề tài 5. Thêm tham gia đ ề tài 6. Cập nh t ậ tham gia đ ề tài 7. Xóa tham gia đ ề tài c a ủ giáo viên H T Ế