Bài giảng Hệ quản trị cơ sở dữ liệu - Tạ Thị Thu Phương

115 trang hapham 2860

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sở dữ liệu - Tạ Thị Thu Phương", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

bai_giang_he_quan_tri_co_so_du_lieu_ta_thi_thu_phuong.pdf

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Tạ Thị Thu Phương

TRѬӠNG ĈҤI HӐC ĈÀ LҤT KHOA TỐN - TIN HӐC Y Z Hӊ QUҦN TRӎ CѪ SӢ DӲ LIӊU (Bài giảng tóm tắt) NGѬӠI BIÊN SOҤN TҤ THӎ THU PHѬӦNG Y Đà Lạt 2009 Z
MỤC LỤC Chương 1: Tổng quan về Hệ quản trị cơ sở dữ liệu Trang 1 I. Giới thiệu 1 II. Cấu trúc và thành phần của hệ quản trị cơ sở dữ liệu 2 Chương 2: Xây dựng, quản lý và khai thác cơ sở dữ liệu 5 I. Khái niệm cơ sở dữ liệu 5 II. Tạo cơ sở dữ liệu. 5 III. Kiểu dữ liệu. 6 IV. Tạo và quản lý bảng. 7 V. Các thao tác trên dữ liệu 11 VI. Truy vấn dữ liệu 12 VII. Tạo và sử dụng khung nhìn (View) 14 VIII. Tạo và sử dụng chỉ mục (Index) 14 IX. Chuyển đổi dữ liệu với các ứng dụng khác 18 Chương 3: T-SQL nâng cao 19 I. Khai báo và sử dụng biến 19 II. Cấu trúc điều khiển 20 III. Thủ tục thường trú (Stored Procedures) 22 IV. Kiểu dữ liệu cursor 26 V. Hàm người dùng (User Defined Functions) 32 VI. Triggers và cài đặt ràng buộc dữ liệu 35 Chương 4: Bảo mật và an tồn dữ liệu 40 I. Bảo mật trong hệ quản trị cơ sở dữ liệu 40 II. Bản sao dữ liệu 46 III. Sao lưu và khơi phục dữ liệu 59 IV. Quản lý giao dịch 61 Chương 5: Lập trình cơ sở dữ liệu 92 I. Lập trình ADO.NET 92 II. Thiết kế chức năng đọc/ ghi dữ liệu 95 III. Tạo báo biểu với Crystal Report 98 Bài tập 105
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 1 Chương 1 TỔNG QUAN VỀ HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU I. Giới thiệu Thơng tin là nguồn tài nguyên quý giá của một tổ chức. Các phần mềm máy tính là những cơng cụ hiệu quả để xử lý thơng tin và hệ quản trị cơ sở dữ liệu là cơng cụ phổ biến cho phép lưu trữ và rút trích thơng tin một cách hiệu quả. Hệ quản trị cơ sở dữ liệu quan hệ là hệ quản trị cơ sở dữ liệu phổ biến nhất hiện nay và được hỗ trợ bởi nhiều nhà cung cấp phần mềm. Tính hiệu quả của các ứng dụng phụ thuộc vào chất lượng của việc tổ chức dữ liệu. Những cải tiến trong kỹ thuật và xử lý cơ sở dữ liệu đưa đến các cơ hội sử dụng thơng tin một cách linh hoạt và hiệu quả khi dữ liệu được tổ chức và lưu trữ trong các cấu trúc quan hệ. Hệ quản trị cơ sở dữ liệu là một thành cơng trong lĩnh vực thương mại. Mục tiêu của hệ quản trị cơ sở dữ liệu. Hệ quản trị cơ sở dữ liệu phải đảm bảo các mục tiêu sau: dữ liệu sẵn dùng (data availability), tính tồn vẹn dữ liệu (data integrity), an tồn dữ liệu (data secutity), và độc lập dữ liệu (data independency). o Dữ liệu sẵn dùng (data availability): dữ liệu được tổ chức sao cho mọi người dùng cĩ thể truy cập dễ dàng theo chức năng và nhiệm vụ của họ. o Tính tồn vẹn dữ liệu (data integrity): dữ liệu lưu trữ trong cơ sở dữ liệu là đúng đắn, đáng tin cậy. o An tồn dữ liệu (data secutity): Chỉ những người dùng được phép mới cĩ thể truy cập dữ liệu. Nếu nhiều người dùng truy cập chung một mục dữ liệu cùng lúc thì hệ quản trị cơ sở dữ liệu khơng cho phép họ thực hiện những thay đổi gây mâu thuẫn dữ liệu. o Độc lập dữ liệu (data independency): hệ quản trị cơ sở dữ liệu phải cho phép tất cả mọi người dùng được phép lưu trữ, cập nhật và rút trích dữ liệu hiệu quả mà khơng cần nắm chi tiết về cấu trúc của cơ sở dữ liệu được biểu diễn và cài đặt. Quá trình phát triển của hệ quản trị cơ sở dữ liệu. Quá trình phát triển của DBMS như sau: − Flat files: 1960s – 1980s − Hierarchical: 1970s –1990s − Network : 1970s – 1990s − Relational: 1980s – đến nay − Object-oriented: 1990s – đến nay
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 2 − Object-relational: 1990s – đến nay − Data warehousing: 1980s – đến nay − Web-enabled: 1990s – đến nay II. Cấu trúc và thành phần của hệ quản trị cơ sở dữ liệu. Forms Application SQL Interface Front ends SQL Commands Query Parser Plan Executor Execution Engine Optimizer Operator Evaluator Concurrency Control File and Access Transaction Methods Manager Recovery Buffer Manager Lock Manager Manager Disk Space Manager DBMS Index System DATABASE Files Data catalog Files Hình 1.1 Kiến trúc của DBMS Kiến trúc của hệ quản trị cơ sở dữ liệu gồm 2 thành phần chức năng: o Bộ quản lý lưu trữ (Storage manager). o Bộ Xử lý truy vấn (Query Processor). 1. Bộ quản lý lưu trữ Bộ quản lý lưu trữ cĩ nhiệm vụ lưu trữ, rút trích và cập nhật dữ liệu vào cơ sở dữ liệu. Bộ quản lý lưu trữ gồm cĩ các đơn vị sau: − Kiểm tra chứng thực và tồn vẹn. − Quản lý giao dịch . − Quản lý file.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 3 − Quản lý vùng đệm. Quản lý giao dịch (Transaction management) Thơng thường, một số thao tác trên cơ sở dữ liệu tạo thành một đơn vị logic cơng việc. Ta hãy xét ví dụ chuyển khoản, trong đĩ một số tiền x được chuyển từ tài khoản A (A:=A-x) sang một tài khoản B (B:=B+x). Một yếu tố cần thiết là cả hai thao tác này hoặc cùng xảy ra hoặc khơng hoạt động nào xảy ra cả. Việc chuyển khoản phải xảy ra trong tính tồn thể của nĩ hoặc khơng. Yêu cầu tồn thể-hoặc-khơng này được gọi là tính nguyên tố (atomicity). Một yếu tố cần thiết khác là sự thực hiện việc chuyển khoản bảo tồn tính nhất quán của cơ sở dữ liệu: giá trị của tổng A + B phải được bảo tồn. Yêu cầu về tính chính xác này được gọi là tính nhất quán (consistency). Cuối cùng, sau khi thực hiện thành cơng hoạt động chuyển khoản, các giá trị của các tài khoản A và B phải bền vững cho dù cĩ thể cĩ sự cố hệ thống. Yêu cầu về tính bền vững này được gọi là tính lâu bền (durability). Một giao dịch là một tập các hoạt động thực hiện chỉ một chức năng logic trong một ứng dụng cơ sở dữ liệu. Mỗi giao dịch là một đơn vị mang cả tính nguyên tố lẫn tính nhất quán. Như vậy, các giao dịch phải khơng được vi phạm bất kỳ ràng buộc nhất quán nào: Nếu cơ sở dữ liệu là nhất quán khi một giao dịch khởi động thì nĩ cũng phải là nhất quán khi giao dịch kết thúc thành cơng. Tuy nhiên, trong khi đang thực hiện giao dịch, phải cho phép sự khơng nhất quán tạm thời. Sự khơng nhất quán tạm thời này tuy là cần thiết nhưng lại cĩ thể dẫn đến các khĩ khăn nếu xảy ra sự cố. Trách nhiệm của người lập trình là xác định đúng đắn các giao dịch sao cho bảo tồn tính nhất quán của cơ sở dữ liệu. Đảm bảo tính nguyên tố và tính lâu bền là trách nhiệm của hệ cơ sở dữ liệu nĩi chung và của thành phần quản trị giao dịch (transaction-management component ) nĩi riêng. Nếu khơng cĩ sự cố, tất cả giao dịch hồn tất thành cơng và tính nguyên tố được hồn thành dễ dàng. Tuy nhiên, do sự hiện diện của các sự cố, một giao dịch cĩ thể khơng hồn tất thành cơng sự thực hiện của nĩ. Nếu tính nguyên tố được đảm bảo, một giao dịch thất bại khơng gây ảnh hưởng đến trạng thái của cơ sở dữ liệu. Như vậy, cơ sở dữ liệu phải được hồn lại trạng thái của nĩ trước khi giao dịch bắt đầu. Hệ quản trị cơ sở dữ liệu phải cĩ trách nhiệm phát hiện sự cố hệ thống và trả lại cơ sở dữ liệu về trạng thái trước khi xảy ra sự cố. Khi một số giao dịch tương tranh cập nhật cơ sở dữ liệu, tính nhất quán của dữ liệu cĩ thể khơng được bảo tồn, ngay cả khi mỗi giao dịch là chính xác. Bộ quản trị điều khiển tương tranh (concurency-control manager) cĩ trách nhiệm điều khiển các tương tác giữa các giao dịch đồng thời để đảm bảo tính thống nhất của CSDL.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 4 Thành phần Kiểm tra chứng thực và tồn vẹn (Authorization and Integrity Manager) Kiểm tra ràng buộc tồn vẹn và quyền truy cập dữ liệu của người dùng cơ sở dữ liệu. Thành phần quản lý giao dịch (Transaction manager) Thành phần này đảm bảo rằng cơ sở dữ liệu luơn ở trạng thái nhất quán. Nĩ quản lý việc thực thi các yêu cầu thao tác dữ liệu và đảm bảo các truy cập dữ liệu đồng thời khơng dẫn đến mâu thuẫn. Thành phần quản lý file (File manager): quản lý việc cấp phát khơng gian trên đĩa. Các file được dùng để chứa tập các dữ liệu tương tự nhau. Hệ quản lý file quản lý các file độc lập, giúp đỡ nhập và lấy các mẩu tin. Thành phần quản lý file thiết lập và duy trì danh sách các cấu trúc và chỉ mục được định nghĩa trong lược đồ trong. Thành phần quản lý file cĩ thể: o Tạo file. o Xĩa file. o Cập nhật mẩu tin trong file. o Lấy một mẩu tin từ một file. Thành phần quản lý vùng đệm (Buffer Manager): cĩ trách nhiệm chuyển dữ liệu từ đĩa lưu trữ vào bộ nhớ chính theo yêu cầu của chương trình. 2. Bộ xử lý truy vấn (Query Processor) Thực hiện câu truy vấn nhận được từ người dùng qua các giai đoạn phân tích (parser), tối ưu hĩa câu hỏi (query optimizer), lập kế hoạch thực hiện (plan executor) và thực hiện tính tốn (operator evaluator).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 5 Chương 2 XÂY DỰNG, QUẢN LÝ VÀ KHAI THÁC CƠ SỞ DỮ LIỆU I. Khái niệm cơ sở dữ liệu • Ở mức logic, một cơ sở dữ liệu (CSDL) gồm: − Các bảng (tables) chứa dữ liệu cĩ cấu trúc và các ràng buộc (constraint) định nghĩa trên các bảng. − Các khung nhìn (view). − Các thủ tục/ hàm. − Các vai trị (role) và người dùng (user). − • Ở mức lưu trữ vật lý, một database của SQL Server được lưu trữ bởi 3 loại tập tin: − Tập tin dữ liệu (data file) gồm cĩ: 1 tập tin dữ liệu chính (primary data file), thường cĩ phần mở rộng “mdf”: chứa các dữ liệu khởi đầu của database. 0-n tập tin dữ liệu thứ cấp (secondary data file), thường cĩ phần mở rộng “ndf”: chứa các dữ liệu khơng lưu trữ hết trong tập tin dữ liệu chính. − Tập tin nhật ký giao tác (transaction log file) gồm cĩ 1-n tập tin nhật ký, thường cĩ phần mở rộng “ldf”: chứa các thơng tin về nhật ký giao tác, dùng để phục hồi database sau khi xảy ra sự cố. II. Tạo cơ sở dữ liệu 1. Cú pháp lệnh tạo CSDL Create Database database_name [ On [Primary] { file_spec [, n] } ] [ Log on { file_spec [, n] } ] với file_spec :: = ( Name = logical_file_name, Filename = 'os_file_name '
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 6 [ , Size = size ] [ , Maxsize = { max_size | Unlimited } ] [ , Filegrowth = growth_increment ] ) Mặc định, các tập tin dữ liệu và log được lưu trong thư mục MSSQL\ Data của thư mục cài đặt SQL Server. Ví dụ • Ví dụ 1: tạo CSDL QLSinhVien theo các quy định mặc định của SQL Server Create Database QLSinhVien • Ví dụ 2: tạo CSDL QLSinhVien với khai báo tên file logic, thư mục lưu tập tin dữ liệu chính, kích thước, Create Database QLSinhVien On ( Name = QLSV_Data Filename = ‘C:\ \ QLSV_Data.mdf ’, Size = 1, Filegrowth = 10% ) • Ví dụ 3 Create Database QLSinhVien On ( Name = QLSV_Data1, Filename = ‘C:\ \ QLSV_Data.mdf ’, Size = 1, Maxsize = 10 MB, Filegrowth = 1 MB ) , ( Name = QLSV_Data2 , Filename = ‘C:\ \QLSV_Data1.ndf’ ) Log on ( Name = QLSV_Log, Filename = ‘D:\ \QLSV_Log.ldf’ ) 2. Xố một CSDL đã tồn tại Drop Database database_name 3. Thay đổi một CSDL Alter Database database_name . Dùng để: – Thêm/xố/thay đổi các tập tin. – Thay đổi các tùy chọn cho CSDL. III. Kiểu dữ liệu SQL Server cung cấp các kiểu dữ liệu: 1. Số – Số nguyên: bit, tinyint, smallint, int, bigint.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 7 – Số thực Floating point: o float(n) o real = float(24) Fixed point o Decimal(p,s) o Numeric(p,s) 2. Chuỗi char(n): chuỗi cĩ độ dài cố định. nchar(n): chuỗi (theo mã Unicode) cĩ độ dài cố định. varchar(n): chuỗi cĩ độ dài thay đổi. nvarchar(n): chuỗi (theo mã Unicode) cĩ độ dài thay đổi. text: kiểu dữ liệu cho phép chứa chuỗi cĩ kích thước hơn 8KB. ntext: kiểu dữ liệu cho phép chứa chuỗi (theo mã Unicode) cĩ kích thước hơn 8KB. 3. Ngày giờ Datetime. Smalldatetime 4. Kiểu người dùng tự định nghĩa a. Định nghĩa một kiểu dữ liệu: sp_addtype type_name, system_type [, ‘null_type’][, ‘owner’] Ví dụ: định nghĩa kiểu dữ liệu Code là kiểu chuỗi gồm 10 ký tự cho phép để trống Exec sp_addtype Code, char(10), ‘NULL’ b. Xĩa một kiểu dữ liệu người dùng định nghĩa: sp_droptype ‘type_name’ IV. Tạo và quản lý bảng 1. Tạo bảng – Xác định các cột (các thuộc tính) của bảng. – Xác định khĩa chính. – Xác định các thuộc tính null/ not null. – Xác định thuộc tính identity (nếu cĩ) (phải là kiểu số nguyên). Lưu ý: – Luơn tạo khĩa chính cho một bảng. – Ràng buộc khĩa ngoại nên được tạo sau khi đã tạo xong tất cả các bảng liên quan. a. Cú pháp lệnh tạo bảng
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 8 Create table Table_name ( { Column_name Data_type [null | not null] [default default_value ] [identity [( seed, increment)] ] } [, n] [, constraint constraint_name primary key ( Column_name [, n] ) ] ) Ví dụ: Tạo bảng học sinh cĩ khĩa chính là (STT, Lop) Create table HOCSINH ( STT tinyint not null, Lop char(5) not null default ‘11A1’ , HoTen nvarchar(30) not null, NgaySinh datetime not null, DiaChi nvarchar(100), constraint pk_HS primary key (STT, Lop) ) b. Thay đổi cấu trúc bảng / xĩa bảng Thay đổi cấu trúc bảng là thực hiện: – Thêm/ xố/ cập nhật kiểu dữ liệu của một cột (column). – Thêm/ xố/ kiểm tra/ khơng kiểm tra ràng buộc (constraint). – Cho phép/ khơng cho phép trigger hoạt động. Cú pháp: Alter table Xĩa bảng: xố dữ liệu và cấu trúc của bảng Cú pháp: Drop table Ví dụ − Thêm thuộc tính DanToc vào bảng HOCSINH: Alter table HOCSINH Add DanToc nvarchar(20) null default ‘Kinh’ − Sửa kiểu dữ liệu của thuộc tính NgaySinh thành kiểu SmallDatetime: Alter table HOCSINH Alter column NgaySinh SmallDatetime not null 2. Quản lý bảng − Các tên bảng, tên ràng buộc khơng được trùng nhau trong cùng một database.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 9 − Tên các cột trong cùng một bảng khơng được trùng nhau. − Thơng tin về các bảng, các ràng buộc được lưu trong bảng hệ thống sysobjects Ví dụ: đọc thơng tin về các bảng trong database hiện hành: Select * from sysobjects where type = ‘U’ − Một số thủ tục SQL Server cung cấp để quản lý bảng và cấu trúc bảng: o sp_databases o sp_tables [‘table_name’] [, ‘owner’][,‘database_name’][, “ ‘type’ ”] Ví dụ: Exec sp_tables null, null, null, “ ‘TABLE’ ” o sp_help [object_name] sp_help cho biết các thơng tin về đối tượng bất kỳ trong database (đối tượng cĩ chứa trong sysobjects). Ví dụ: Exec sp_help HOCSINH o sp_columns object [, owner] [, database] [,column] o sp_helpconstraint ‘table_name’ o 3. Cài đặt ràng buộc tồn vẹn đơn giản SQL Server cung cấp sẵn cơ chế để kiểm tra các loại ràng buộc tồn vẹn (RBTV) sau: o Khĩa chính (primary key constraint). o Khĩa ngoại (foreign key constraint). o Giá trị duy nhất (unique constraint). o Check constraint (Kiểm tra ràng buộc miền giá trị). Cĩ thể khai báo ràng buộc trong lúc tạo bảng hoặc khi bảng đã tồn tại. Thơng thường nên khai báo ràng buộc tồn vẹn trước khi nhập dữ liệu. a. Khai báo ràng buộc trong lúc tạo bảng Cú pháp: Create table Table_name ( [, constraint Constraint_name { primary key (Column_name [, n]) | unique ( Column_name [, n]) | check ( logical_expression ) } ] [ n] ) Ví dụ
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 10 Create table SinhVien ( MaSV char(10) not null, HoTen nvarchar(30) not null, Nam tinyint, CMND char(10), Khoa char(5), constraint pk_SV primary key (MaSV), constraint u_CMND unique (CMND), constraint chk_Nam check (Nam > 0 and Nam <= 4) ) b. Khai báo ràng buộc trên bảng đã tồn tại Cú pháp: Alter table table_name [with check| with nocheck] Add { constraint constraint_name { primary key ( column_name [, n] ) | unique ( column_name [, n] ) | check ( logical_expression ) | foreign key ( column_name [, n] ) references ref_table ( ref_column [, n] ) [ on delete {cascade| no action} ] [ on update {cascade| no action} ] } [, n] Ví dụ /* giả sử đã tồn tại bảng KHOA( MaKhoa, ) */ Alter table SINHVIEN with check add constraint u_CMND unique (CMND), constraint chk_Nam check (Nam in (1, 2, 3, 4) ), constraint fk_SV_maKhoa foreign key (Khoa), references KHOA(MaKhoa) c. Kiểm tra / khơng kiểm tra ràng buộc Cú pháp: Alter table Table_name {Check| Nocheck} constraint { All | constraint_name [, n] } Ví dụ: alter table SINHVIEN nocheck constraint u_CMND, chk_Nam d. Xố ràng buộc Cú pháp:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 11 Alter table table_name Drop { [constraint ] constraint_name } [, n] Ví dụ: Alter table SINHVIEN drop constraint u_CMND, chk_Nam e. Rule − Rule là một qui định chung được tạo ra trong một CSDL. − Một rule cĩ thể được áp dụng cho nhiều thuộc tính của nhiều bảng khác nhau, hoặc cho các kiểu dữ liệu người dùng định nghĩa trong database. Tạo rule Cú pháp: Create rule rule_name as logical_expression (trong đĩ “logical_expression” phải chứa một biến. Biến này tương ứng với đối tượng sẽ được áp dụng rule). Ví dụ: create rule r_SoDuong as @value >0 Kết buộc/ gỡ kết buộc rule Kết buộc rule: dùng thủ tục: sp_bindrule ‘rule_name’, ‘object’, [ ‘futureonly’ ] trong đĩ: Tùy chọn futureonly chỉ dùng khi kết buộc rule với kiểu dữ liệu người dùng định nghĩa, cĩ nghĩa các cột thuộc kiểu dữ liệu này trước đĩ khơng bị ảnh hưởng bởi rule. Ví dụ: sp_bindrule ‘r_SoDuong’, ‘SinhVien.Nam’ Rule mới kết buộc sẽ ngầm gỡ rule cũ trên đối tượng. Gỡ kết buộc sp_unbindrule ‘object’, [ ‘futureonly’ ] Ví dụ: sp_unbindrule ‘SinhVien.Nam’ Xố rule Cú pháp: Drop rule {rule_name} [, n] Lưu ý: Chỉ xĩa được rule khi nĩ khơng cịn kết buộc với đối tượng nào. V. Các thao tác trên dữ liệu Chú ý khi thêm/ xĩa/ cập nhật dữ liệu:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 12 – Dữ liệu nhập phải phù hợp với kiểu dữ liệu. – Đảm bảo các ràng buộc tồn vẹn. – Định dạng giá trị kiểu chuỗi unicode, kiểu datetime. – Nhập giá trị rỗng (Null). 1. Các dạng lệnh insert − Thêm từng dịng dữ liệu vào bảng Insert [into] Table_name[ (column_name[, n] )] values ( value [, n] ) − Thêm 0-n dịng dữ liệu từ bảng khác/ từ kết quả của một câu truy vấn Insert [into] Table_name Select_statement Lưu ý: trong câu select, ta cĩ thể đọc dữ liệu từ các bảng trong database khác. Khi đĩ, tên bảng được viết đầy đủ như sau: Database_name.Owner.Table_name Ví dụ: select * from QLSinhVien.dbo.SinhVien 2. Lệnh cập nhật dữ liệu update table_name set column_name_1= value1, , column_name_m= value_m [where conditional_expression] 3. Lệnh xố dữ liệu delete [from] table_name [where conditional_expression] VI. Truy vấn dữ liệu 1. Câu truy vấn tổng quát Cú pháp tổng quát của câu truy vấn dữ liệu: SELECT [tính chất] FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY [ASC | DESC]] trong đĩ: − Tính chất là một trong các từ khĩa: ALL (chọn ra tất cả các dịng trong bảng), DISTINCT (loại bỏ các dịng trùng lắp thơng tin), TOP (chọn n dịng đầu tiên thỏa mãn điều kiện).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 13 − Danh sách các thuộc tính_1: tên các thuộc tính cho biết thơng tin cần lấy. Chú ý: Các thuộc tính cách nhau bởi dấu ‘,’. Nếu lấy tất cả các thuộc tính của 1 bảng R thì dùng: R.* Nếu sau FROM chỉ cĩ 1 bảng và lấy tất cả các cột của bảng đĩ thì dùng select *. Nếu tồn tại 1 thuộc tính sau select xuất hiện ở 2 bảng sau FROM thì phải chỉ định rõ thuộc tính đĩ thuộc bảng nào. − Danh sách các table/query/view: các bảng, câu truy vấn, hoặc khung nhìn chứa thơng tin cần lấy. Khi tìm kiếm thơng tin trên nhiều hơn 2 bảng/truy vấn thì phải kết các bảng lại với nhau (cĩ thể đặt điều kiện kết đặt sau where hoặc đặt trong mệnh đề From join/ left join/ right join/full join on ). − Alias: bí danh (tên tắt) của bảng dùng cho các bảng cĩ tên quá dài, hoặc một bảng được dùng nhiều lần trong mệnh đề from của câu truy vấn. − điều kiện_1: là điều kiện để lọc dữ liệu (chọn các bộ thoả điều kiện). − danh sách các thuộc tính_2: dữ liệu sẽ được gom nhĩm theo các cột này, độ ưu tiên tính từ trái sang. − điều kiện_2: điều kiện lọc các nhĩm theo một tiêu chí đại diện cho cả nhĩm. − danh sách các thuộc tính_3:sắp xếp dữ liệu theo cột nào, thứ tự là tăng (ASC) hoặc giảm (DESC). Mặc định là dữ liệu được sắp theo thứ tự tăng dần. Việc sắp xếp được thực hiện theo thứ tự ưu tiên từ trái qua phải. Lưu ý: Nếu câu truy vấn khơng cĩ mệnh đề Group By thì cũng khơng cĩ mệnh đề Having. Nếu câu truy vấn cĩ chứa mệnh đề Group By thì Danh sách các thuộc tính_1 chỉ chứa các thuộc tính hoặc biểu thức liên quan đến các thuộc tính trong danh sách các thuộc tính_2 và các hàm gộp (max, min, avg, sum, count). 2. Các hàm thường dùng − Các hàm gộp (Aggregate functions): max, min, sum, avg, count − Các hàm thời gian. − Các hàm tốn học. − Các hàm xử lý chuỗi. − . (Sinh viên cĩ thể tra cứu theo từ khĩa trong Books Online).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 14 VII. Tạo và sử dụng khung nhìn (View) 1. Khái niệm khung nhìn: Khung nhìn (View) là một bảng ảo, cĩ cấu trúc như một bảng, khung nhìn khơng lưu trữ dữ liệu mà dữ liệu của nĩ được tạo ra khi sử dụng, khung nhìn là đối tượng thuộc CSDL. Khung nhìn được tạo ra từ câu lệnh truy vấn dữ liệu (lệnh Select), truy vấn từ một hoặc nhiều bảng dữ liệu. 2. Sử dụng khung nhìn o Khung nhìn được sử dụng khai thác dữ liệu như một bảng dữ liệu, cĩ thể được chia sẻ bởi nhiều người dùng, an tồn trong khai thác. o Cĩ thể thực hiện truy vấn dữ liệu trên cấu trúc của khung nhìn. o Các khung nhìn được tạo từ nhiều bảng hoặc trong khung nhìn cĩ chứa từ khĩa DISTINCT, hàm gộp, mệnh đề group by đều khơng cho phép cập nhật dữ liệu từ khung nhìn vào các bảng gốc trong cơ sở dữ liệu. Cú pháp tạo khung nhìn: Create View view_name As Select_statement VIII. Tạo và sử dụng chỉ mục (Index) Chỉ mục (Index) là một phần quan trọng đối với CSDL, đặc biệt là cơ sở dữ liệu lớn. Chỉ mục được thiết lập từ một hoặc nhiều cột dữ liệu của bảng dữ liệu. Các giá trị của Chỉ mục sẽ được sắp xếp và lưu trữ theo một danh sách (bảng khác). Mỗi giá trị chỉ mục là duy nhất trong danh sách và nĩ sẽ liên kết đến giá trị trong bảng dữ liệu (liên kết dạng con trỏ). Việc lưu trữ dữ liệu của bảng cĩ khĩa chỉ mục được thực hiện theo cấu trúc B-Cây nhằm tăng tốc độ truy xuất dữ liệu đối với ổ đĩa (thiết bị thứ cấp). Khi tìm kiếm một giá trị trong cột dữ liệu, mà cột này tham gia tạo Chỉ mục, đầu tiên câu lệnh xác định vị trí của giá trị nằm trong Chỉ mục bằng phép duyệt cây, sau đĩ thực hiện tìm theo liên kết đến bản ghi chứa giá trị tương ứng với khĩa trong bảng. 1. Lựa chọn chỉ mục • Khơng cĩ chỉ mục, hệ quản trị CSDL thực hiện truy vấn bằng cách duyệt qua từng dịng trong bảng. • Cài đặt các chỉ mục cho bảng giúp truy vấn thơng tin nhanh hơn (tìm kiếm trên B-Cây). • Khĩa chính và các ràng buộc unique hiển nhiên là các chỉ mục của bảng. • Cơ sở để chọn cài đặt chỉ mục: dựa vào các nhu cầu truy vấn thực hiện thường xuyên trên CSDL.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 15 • Nên cài đặt chỉ mục cho các trường hợp sau: – Trường hợp 1: Cĩ nhu cầu truy vấn thường xuyên các bộ của bảng Q theo một số (tập) thuộc tính nào đĩ. Ví dụ: GiaoDich(MãGD, ,NgàyGD): Cĩ nhu cầu truy xuất thường xuyên các bộ của giao dịch trong một ngày hoặc trong một khoảng thời gian nhất định: cài đặt chỉ mục trên thuộc tính NgayGD của quan hệ GiaoDich. – Trường hợp 2: tập thuộc tính tham gia vào phép kết của một câu truy vấn xảy ra thường xuyên. Ví dụ: cho 2 lược đồ quan hệ: HocSinh(STT, Lop, HoTen, ) KetQua(STT, Lop, Mon, Diem) Thường xuyên cĩ nhu cầu truy vấn: cho biết kết quả học tập của một học sinh. Câu lệnh truy vấn như sau: select hs.STT, hs.Lop, hs.HoTen, kq.Mon, kq.Diem from HocSinh hs join KetQua kq on hs.STT = kq.STT and hs.Lop = kq.Lop Cài đặt chỉ mục (STT, Lop) cho quan hệ KetQua Tổng quát: trên mơ hình quan hệ, xác định các con đường truy xuất thường xuyên: Từ một bộ của Q1(một giá trị cụ thể a của A) cĩ nhu cầu truy xuất thường xuyên các bộ của Q2 tương ứng (tìm kiếm các bộ của Q2 với A = a): khai báo chỉ mục (A) cho Q2. Lưu ý: một chỉ mục (AB) khác với hai chỉ mục (A) và (B). 2. Các loại chỉ mục Cĩ hai loại chỉ mục: – Clustered index – Nonclustered index Clustered index: • Dữ liệu thật sự được sắp xếp vật lý theo chỉ mục (thật sự nằm ở nút lá của cây). • Mỗi bảng chỉ cĩ thể cĩ một clustered chỉ mục, thường là khĩa chính.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 16 Nonclustered index: • Chỉ mục logic, dữ liệu thật sự khơng được sắp xếp vật lý theo chỉ mục. • Nút lá là con trỏ trỏ đến vị trí của bộ dữ liệu, hoặc trỏ đến giá trị của clustered chỉ mục (trong trường hợp bảng cĩ clustered index). – Khơng cĩ clustered index: – Cĩ clustered index
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 17 • Một số cân nhắc khi chọn chỉ mục: – Sử dụng nhiều chỉ mục tăng tốc độ truy vấn, nhưng làm giảm hiệu quả của các thao tác thêm/xố/cập nhật dữ liệu. – Khơng nên tạo chỉ mục trên các bảng quá nhỏ (vài trăm dịng). – Chỉ nên chọn chỉ mục mà mỗi giá trị của nĩ tương ứng với một số ít bộ. Nếu mỗi giá trị chỉ mục ứng với trên 20% số lượng bộ trong bảng, thực hiện truy vấn bình thường bằng cách duyệt qua các dịng trong bảng sẽ hiệu quả hơn. – Các giá trị chỉ mục phải phân bố đều các bộ trong bảng. – Cố gắng dùng các chỉ mục với số thuộc tính ít (chiếm ít khơng gian và cần ít chi phí duy trì hơn chỉ mục với số thuộc tính lớn). – Clustered index phải nhỏ (số thuộc tính ít, kích thước nhỏ), vì các chỉ mục nonclustered đều phải gắn kết tới nĩ. 3. Cài đặt chỉ mục với SQL Server Một số qui định: 1. Một bảng cĩ tối đa 249 nonclustered chỉ mục (bao gồm cả những chỉ mục ngầm định khi khai báo khĩa chính và chỉ mục). 2. Kích thước tối đa của một chỉ mục (tổng kích thước các thuộc tính tham gia vào chỉ mục) khơng quá 900 bytes. 3. Mặc định: chỉ mục clustered được khai báo ngầm định cùng với khai báo khĩa chính, các trường hợp khác là nonclustered (tất nhiên cĩ thể chỉ định khác đi). Cú pháp khai báo chỉ mục: Create [ Unique ][ Cluster| Nonclustered] Chỉ mục chỉ mục_name
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 18 On {table | view } (column [ Asc | Desc] [ , n ]) Ví dụ: Create nonclustered chỉ mục idx_STTHS_Lop On KETQUA (STTHS, Lop) Cú pháp xĩa chỉ mục: Drop Chỉ mục table_name (chỉ mục_name) Ví dụ: Drop Chỉ mục KETQUA(idx_STTHS_Lop) IX. Chuyển đổi dữ liệu với các ứng dụng khác (xem các tài liệu hướng dẫn thực hành SQL Server kèm theo)
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 19 Chương 3 T-SQL NÂNG CAO I. Khai báo và sử dụng biến 1. Biến cục bộ − Là một đối tượng cĩ thể chứa giá trị thuộc một kiểu dữ liệu nhất định, tên biến bắt đầu bằng một ký tự @. – Biến cục bộ cĩ giá trị trong một query batch hoặc trong một thủ tục thường trú (stored procedure) hoặc hàm (function). – Khai báo biến cục bộ bằng lệnh declare: cung cấp tên biến và kiểu dữ liệu: Declare tên_biến Kiểu_dữ_liệu Ví dụ: Declare @MaSinhVien char(10) Declare @HoTen nvarchar(30) Declare @Sum float, @Count int – Để gán giá trị cho một biến cục bộ dùng lệnh set. Giá trị gán cho biến phải phù hợp với kiểu dữ liệu của biến. Set tên_biến = giá_trị Set tên_biến = tên_biến Set tên_biến = biểu_thức Set tên_biến = kết_quả_truy_vấn Ví dụ: Set @MaLop = ‘TH2001’ Set @SoSV = (select count (*) from SinhVien) Set @MaLop = ‘TH’+Year(@NgayTuyenSinh) Đưa kết quả truy vấn vào biến: SV(MaSV: int; HoTen: nvarchar(30), Tuoi int) Select @Var1 = HoTen, @Var1 = Tuoi from SV where MaSV = 1 Lưu ý: nếu câu truy vấn trả về nhiều dịng, các biến chỉ nhận giá trị tương ứng của dịng đầu tiên. 2. Biến tồn cục – Là các biến hệ thống do SQL Server cung cấp, tên biến bắt đầu bằng 2 ký tự @ – SQL tự cập nhật giá trị cho các biến này, người sử dụng khơng thể gán giá trị trực tiếp. − Một số biến hệ thống thuờng dùng o @@error: thơng báo mã lỗi, nếu @@error = 0: thao tác thực hiện thành cơng.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 20 o @@rowcount: cho biết số dịng bị ảnh hưởng bởi lệnh cuối (insert, update, delete). o @@trancount: cho biết số giao dịch đang hoạt động trên kết nối hiện tại. o @fetch_status: cho biết thao tác lấy dữ liệu từ cursor cĩ thành cơng khơng. II. Cấu trúc điều khiển 1. Lệnh If else − Chức năng: xét điều kiện để quyết định những lệnh T-SQL nào sẽ được thực hiện − Cú pháp: If biểu_thức_điều kiện Lệnh| Khối_lệnh [Else Lệnh| Khối_lệnh] Khối lệnh là một hoặc nhiều lệnh nằm trong cặp từ khĩa begin end Ví dụ: xét 2 lược đồ quan hệ (LĐQH) HocPhan(MaHP, TenHP, SiSo) DangKy(MaSV, MaHP) Viết lệnh để thêm một đăng ký mới cho sinh viên cĩ mã số 001 vào học phần HP01 (giả sử học phần này đã tồn tại trong bảng HocPhan). Lời giải như sau: Declare @SiSo int select @SiSo = SiSo from HocPhan where MaHP= ‘HP01’ if @SiSo < 50 Begin insert into DANG_KY(MaSV, MaHP) values(‘001’, ’HP01’) print N’Đăng ký thành cơng’ End Else print N’Học phần đã đủ SV’ 2. Lệnh While − Chức năng: thực hiện lặp lại một đoạn lệnh T-SQL khi điều kiện cịn đúng. − Cú pháp:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 21 While biểu_thức_điều_kiện Lệnh| Khối lệnh – Cĩ thể sử dụng Break và Continue trong khối lệnh của while Break: thốt khỏi vịng while hiện hành. Continue : trở lại đầu vịng while, bỏ qua các lệnh sau đĩ. Ví dụ: xét lược đồ quan hệ SinhVien(MaSV: int, HoTen: nvarchar(30)) Viết lệnh xác định một mã sinh viên mới theo qui định: mã sinh viên tăng dần, nếu cĩ chỗ trống thì mã mới xác định sẽ chèn vào chỗ trống đĩ. Chẳng hạn, nếu trong bảng sinhvien đã cĩ các mã sinh viên 1, 2, 3, 7 mã sinh viên mới là 4. Giải: Declare @STT int Set @STT = 1 While exists(select * from SV where MaSV = @STT) set @STT = @STT+1 Insert into SV(MaSV, HoTen) values(@STT, ‘Nguyen Van A’) 3. Lệnh Case − Chức năng: kiểm tra một dãy các điều kiện và trả về kết quả phù hợp với điều kiện đúng. Lệnh case được sử dụng như một hàm trong câu select. − Cú pháp: Cĩ hai dạng: Dạng 1 (simple case): Case Biểu_thức_đầu_vào When Giá_trị then kết_quả [ n] [ Else kết_quả_khác] End Dạng 2 (searched case): Case When biểu_thức_điều kiện then kết_quả [ n] [ Else kết_quả_khác] End Ví dụ: xét LĐQH NHAN_VIEN(MaNV, HoTen, NgaySinh, CapBac,Phai)
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 22 Cho biết những nhân viên đến tuổi nghỉ hưu biết rằng tuổi về hưu của nam là 60, của nữ là 55). Giải: select * from NHAN_VIEN where datediff(yy, NgaySinh, getdate()) > = Case Phai when ‘Nam’ then 60 when ‘Nu’ then 55 End Cho biết mã NV, họ tên và loại nhân viên (cấp bậc <=3:bình thường, cấp bậc = null: chưa xếp loại, cịn lại: cấp cao). Giải: Select MaNV, HoTen, ‘Loai’ = Case when CapBac<=3 then ‘Binh Thuong’ when CapBac is null then ‘Chua xep loai’ else ‘Cap Cao’ End From NhanVien III. Thủ tục thường trú (Stored Procedures) 1. Khái niệm Thủ tục thường trú (Stored Procedures - SP) chứa các lệnh T_SQL. Tương tự như một thủ tục trong các ngơn ngữ lập trình, SP trong SQL Server cĩ thể truyền tham số, cĩ tính tái sử dụng. Các thủ tục này được dịch và lưu trữ thành một đối tượng trong CSDL. Ý nghĩa: − Tính tái sử dụng, tính uyển chuyển nhờ hệ thống tham số. − Khi biên dịch SP, các lệnh trong của nĩ được tối ưu hĩa nĩ sao cho thực thi hiệu quả nhất. Kết quả tối ưu hĩa được lưu bền vững. Khi gọi thực thi thủ tục khơng cần biên dịch và tối ưu hĩa lại lời gọi thủ tục tiết kiệm thời gian và tài nguyên hơn khối lệnh tương đương thân thủ tục. − Trong ứng dụng triển khai theo mơi trường client/server, client gửi lời gọi SP lên server thì chiếm đường truyền ít hơn rất nhiều lần so với việc gửi khối lệnh tương đương trong thân thủ tục Giảm khối lượng thơng tin trao đổi khi ứng dụng gửi yêu cầu thực hiện cơng việc về cho server do đĩ tránh nghẽn đường truyền, giảm trì trệ.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 23 − Đĩng gĩi chỉ các thao tác cho phép trên CSDL vào các SP và quy định truy xuất dữ liệu phải thơng qua SP. Ngồi ra cịn cĩ thể phân quyền trên SP Hỗ trợ tốt hơn cho việc đảm bảo an tồn (security) cho CSDL. − SP giúp cho việc kết xuất báo biểu bằng Crystal Report trở nên đơn giản và hiệu quả hơn rất nhiều so với việc kết xuất dữ liệu trực tiếp từ các bảng và khung nhìn. 2. Khai báo và sử dụng thủ tục Cú pháp khai báo: Create {proc | procedure} procedure_name {Parameter_name DataType [=default] [output] }[, n] As { khối lệnh } Go Lưu ý: Tên tham số đặt theo qui tắc như tên biến cục bộ. Giá trị trả về của SP dùng một (hay một số) tham số output. Ví dụ: − Xây dựng SP cho biết danh sách sinh viên của một lớp cĩ mã cho trước Create proc DS_Lop @MaLop varchar(10) As Select SV.MaSV, SV.HoVaTen, SV.NgaySinh From SinhVien SV where SV.Lop = @MaLop Go − Xây dựng SP tính tốn giá trị cho đơn hàng cĩ mã cho trước với quan hệ DonHang như sau: DonHang(Ma, SoLuong, DonGia, ThueSuat, ChietKhau, ThanhTien) Create proc TongTien @MaDH varchar(10) As Declare @ThanhTien float Declare @TienThue float Declare @TienChietKhau float Declare @DonGia float,@SoLuong int Set @SoLuong = (select SoLuong from DonHang where Ma = @MaDH)
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 24 Set @DonGia = (select DonGia from DonHang where Ma = @MaDH) Set @TienThue = (select ThueSuat from DonHang where Ma = @MaDH) Set @TienChietKhau = (select ChietKhau from DonHang where Ma = @MaDH) Set @ThanhTien = @DonGia*@SoLuong Set @TienThue = @ThanhTien*@TienThue/100 Set @ThanhTien = @ThanhTien + @TienThue Set @TienChietKhau = @ThanhTien*@TienChietKhau/100 Set @ThanhTien = @ThanhTien - @TienChietKhau Update DonHang set ThanhTien = @ThanhTien where Ma = @MaDH Go − Viết thủ tục thêm một đăng ký của sinh viên vào một học phần (tổng quát ví dụ trong phần If else) Create procedure usp_ThemDangKy @MaSV char(5), @MaHP char(5), @SiSo int = 0 output As select @SiSo = SiSo from HocPhan where MaHP= @MaHP if @SiSo < 50 Begin insert into DANG_KY(MaSV, MaHP) values(@MaSV, @MaHP) set @SiSo = @SiSo+1 return 1 End return 0 Go - Xây dựng SP tính điểm trung bình và xếp loại cho sinh viên thuộc lớp cho trước. Giả sử cĩ các quan hệ như sau: SinhVien (MaSV, HoTen, DTB, XepLoai, Lop) MonHoc (MaMH, TenMH) KetQua (MaMH, MaSV, LanThi, Diem) trong đĩ:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 25 − Điểm thi chỉ tính lần thi sau cùng. − Xếp loại: Xuất sắc [9, 10], Giỏi [8, 8.9], Khá [7, 7.9], Trung bình [5.0, 6.9], Yếu [0,4.9]. − Kết quả xuất dạng tham số output, khơng ghi xuống CSDL. Giải Create proc XepLoaiSV @MaSV varchar(10), @DTB float out put, @XL nvarchar(20) out put As Set @DTB = (Select avg(Diem) from KetQua Kq Where MaSV = @MaSV and not exists (select * from KetQua Kq1 where Kq1.MaSV = @MaSV and Kq1.MaMH=Kq.MaMH and Kq1.LanThi > Kq.LanThi)) If @DTB >= 9 Set @XL = N’Xuất sắc’ Else if @DTB >= 8 Set @XL = N’Giỏi’ Else if @DTB >= 7 Set @XL = N’Khá’ Else if @DTB >= 5 Set @XL = N’Trung bình’ Else Set @XL = N’Yếu’ Go Cú pháp gọi thực hiện thủ tục: EXEC| EXECUTE { [ @return_status = ] procedure_name { [ @parameter _name = ] value [ OUTPUT ] } [ , n ] Lưu ý: o Khi gọi thực hiện SP, dùng từ khĩa Exec và cần truyền đủ tham số với kiểu dữ liệu phù hợp và thứ tự chính xác như khai báo trong định nghĩa SP. o Cĩ thể truyền giá trị cho tham số đầu vào (input) là một hằng hoặc một biến đã gán giá trị, khơng truyền được một biểu thức. o Để nhận được giá trị kết quả (thơng qua tham số đầu ra), cần truyền vào một biến và cĩ từ khĩa output. Ví dụ: o Gọi thủ tục usp_ThemDangKy:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 26 Exec usp_ThemDangKy ‘001’, ’HP01’ hoặc Exec usp_ThemDangKy @MaHP = ‘HP01’, @MaSV = ‘001’ o Gọi thủ tục usp_ThemDangKy cĩ nhận kết quả đầu ra: Declare @SiSo int Exec usp_ThemDangKy ‘001’,’HP01’, @SiSo output Print @SiSo o Gọi thủ tục usp_ThemDangKy cĩ nhận kết quả đầu ra và kết quả trả về từ thủ tục : Declare @SiSo int, @KetQua int Exec @KetQua = usp_ThemDangKy ‘001’,’HP01’, @SiSo output o Gọi thực hiện thủ tục xếp loại sinh viên: Declare@MaSinhVien varchar(10) Declare@DiemTB varchar(10) Declare@XepLoai varchar(10) Set@MaSinhVien = ‘0712345’ Exec XepLoaiSV @MaSinhVien,@DiemTB out put,@XepLoai out put Exec XepLoaiSV ‘0713478’, @DiemTB out put,@XepLoai out put Sửa thủ tục Thay từ khĩa Create trong lệnh tạo thủ tục bằng từ khĩa Alter. Xĩa thủ tục Drop {procedure|proc} procedure_name Ví dụ: Drop procedure usp_ThemDangKy 3. Stored procedure hệ thống − Là những thủ tục do SQL Server cung cấp sẵn để thực hiện các cơng việc: quản lý CSDL, quản lý người dùng, cấu hình CSDL, − Các thủ tục này cĩ tên bắt đầu bằng “sp_” Khi xây dựng thủ tục, tránh đặt tên thủ tục bắt đầu với “sp_”. IV. Kiểu dữ liệu cursor 1. Khái niệm Cursor - Là một cấu trúc dữ liệu ánh xạ đến một tập các dịng dữ liệu kết quả của một câu truy vấn (select).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 27 - Cho phép duyệt tuần tự qua tập các dịng dữ liệu và đọc giá trị từng dịng. - Thể hiện của cursor là 1 biến, nhưng tên biến này khơng bắt đầu bằng ’@’. - Vị trí hiện hành của cursor cĩ thể được dùng như điều kiện trong mệnh đề where của lệnh update hoặc delete: cho phép cập nhật/xố dữ liệu (dữ liệu thật sự trong CSDL) tương ứng với vị trí hiện hành của cursor. 2. Khai báo và sử dụng Cursor Khai báo Cursor Cĩ thể sử dụng cú pháp chuẩn SQL 92 hoặc cú pháp T_SQL mở rộng. − Cú pháp SQL 92 chuẩn: Declare cursor_name [Insensitive] [Scroll] Cursor For select_statement [ For {Read only| Update [of column_name [, n] ] }] − Cú pháp T_SQL mở rộng Declare cursor_name Cursor [ Local | Global ] [ Forward_only| Scroll] [ Static| Dynamic] [ Read_only] For select_statement [ For Update [ of column_name [, n] ] ] Lưu ý: Tên cursor trong các cách khai báo khơng bắt đầu bằng ký tự “@”. Ý nghĩa các tham số tùy chọn trong khai báo: o Insensitive/ static: nội dung của cursor khơng thay đổi trong suốt thời gian tồn tại, trong trường hợp này cursor chỉ là read only. o Dynamic: trong thời gian tồn tại, nội dung của cursor cĩ thể thay đổi nếu dữ liệu trong các bảng liên quan cĩ thay đổi. o Local: cursor cục bộ, chỉ cĩ thể sử dụng trong phạm vi một khối (query batch) hoặc một thủ tục/ hàm. o Global: cursor tồn cục, cĩ thể sử dụng trong một thủ tục/hàm hay một query batch bất kỳ hoặc đến khi bị hủy một cách tường minh. o Forward_only: cursor chỉ cĩ thể duyệt một chiều từ đầu đến cuối. o Scroll: cĩ thể duyệt lên xuống cursor tùy ý (duyệt theo đa chiều).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 28 o Read only: chỉ cĩ thể đọc từ cursor, khơng thể sử dụng cursor để update dữ liệu trong các bảng liên quan (ngược lại với “for update ” ). Mặc định khi khai báo cursor nếu khơng chỉ ra các tùy chọn thì cursor cĩ các tính chất: - Global - Forward_only - Read only hay “for update” tùy thuộc vào câu truy vấn - Dynamic Duyệt cursor Dùng lệnh Fetch để duyệt tuần tự qua cursor theo cú pháp: Fetch [ [Next| Prior| First| Last| Absolute n| Relative n] From ] Tên_cursor [Into Tên_biến [, n] ] − Mặc định: fetch next. − Đối với cursor dạng forward_only, chỉ cĩ thể fetch next. − Biến hệ thống @@fetch_status cho biết lệnh fetch vừa thực hiện cĩ thành cơng hay khơng, giá trị của biến này cơ sở để biết đã duyệt đến cuối cursor hay chưa. Quy trình sử dụng Cursor − Khai báo cursor. − “Mở” cursor bằng lệnh Open Open tên_cursor − Khai báo các biến tạm để chứa phần tử hiện hành (đang được xử lý) của cursor: Các biến tạm phải cùng kiểu dữ liệu với các trường tương ứng của phần tử trong cursor. Cĩ n trường trong phần tử của cursor thì phải cĩ đủ n biến tạm tương ứng. − Fetch (next, ) cursor để chuyển đến vị trí phù hợp: Cĩ thể đưa các giá trị của dịng hiện hành vào các biến thơng qua mệnh đề into của lệnh fetch. Nếu khơng cĩ mệnh đề into, các giá trị của dịng hiện hành sẽ được hiển thị ra cửa sổ kết quả (result pane) sau lệnh fetch. Cĩ thể sử dụng vị trí hiện tại như là điều kiện cho mệnh đề where của câu delete/ update (nếu cursor khơng là read_only).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 29 − Lặp lại việc duyệt và sử dụng cursor, cĩ thể sử dụng biến @@fetch_status để biết đã duyệt qua hết cursor hay chưa. @@FETCH_STATUS = 0 : lấy dữ liệu thành cơng, @@FETCH_STATUS < 0 : khơng lấy được dữ liệu. − Đĩng cursor bằng lệnh Close Close Tên_cursor Lưu ý: Sau khi đĩng, vẫn cĩ thể mở lại nếu cursor chưa bị hủy. − Hủy cursor bằng lệnh deallocate Deallocate Tên_cursor Ví dụ: xét hai LĐQH SINHVIEN (MaSV, HoTen, MaKhoa) KHOA(MaKhoa, TenKhoa) Duyệt và đọc giá trị từ cursor Cập nhật lại giá trị MaSV = Viết tắt tên Khoa + MaSV hiện tại cho tất cả sinh viên: declare cur_DSKhoa cursor for select MaKhoa, TenKhoa from Khoa open cur_DSKhoa declare @MaKhoa int, @TenKhoa varchar(30), @TenTat varchar(5) fetch next from cur_DSKhoa into @MaKhoa, @TenKhoa while @@fetch_status = 0 begin xác định tên tắt của Khoa dựa vào @TenKhoa update SinhVien set MaSV = @TenTat+MaSV Where MaKhoa = @MaKhoa fetch next from cur_DSKhoa into @MaKhoa, @TenKhoa end Close cur_DSKhoa Deallocate cur_DSKhoa Dùng cursor để xác định dịng cập nhật
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 30 declare cur_DSKhoa cursor scroll for select MaKhoa, TenKhoa from Khoa open cur_DSKhoa fetch absolute 2 from cur_DSKhoa if (@@fetch_status = 0) update Khoa set TenKhoa = ‘aaa’ where current of cur_DSKhoa Close cur_DSKhoa Deallocate cur_DSKhoa 3. Biến cursor − Ta cĩ thể khai báo một biến kiểu cursor và gán cho nĩ tham chiếu đến một cursor đang tồn tại. − Biến cursor cĩ thể được xem như là con trỏ cursor. − Biến cursor là một biến cục bộ. − Biến cursor sau khi gán giá trị được sử dụng như một cursor thơng thường. Ví dụ : Declare @cur_var cursor set @cur_var = my_cur my_cur là một cursor đang tồn tại hoặc: Declare @cur_var cursor set @cur_var = cursor for select_statement Kết hợp cursor với stored procedure Xây dựng SP tính điểm trung bình và xếp loại cho sinh viên thuộc lớp cho trước. Giả sử cĩ các quan hệ như sau: SinhVien (MaSV, HoTen, DTB, XepLoai, Lop) MonHoc (MaMH, TenMH) KetQua (MaMH, MaSV, LanThi, Diem) Biết rằng Điểm thi chỉ tính lần thi sau cùng Xếp loại: Xuất sắc [9, 10], Giỏi [8, 8.9], Khá [7, 7.9], Trung bình [5.0, 6.9], Yếu [0, 4.9]. Kết quả ghi xuống CSDL, đồng thời xuất ra tổng số sinh viên xếp loại giỏi của lớp đĩ. • Phân tích ví dụ:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 31 o Lớp cần xét cĩ nhiều sinh viên, từng sinh viên cần được xử lý thơng qua 3 bước: Tính điểm trung bình cho sinh viên, điểm trung bình phải là điểm của lần thi sau cùng. Cĩ thể tái sử dụng thủ tục XepLoaiSVLop. Dựa vào điểm trung bình của sinh viên để xác định xếp loại. Cập nhật điểm và xếp loại vào bảng sinh viên. o Mọi sinh viên đều lặp lại 3 bước trên. Từ phân tích trên ta thấy: Cần xử lý nhiều phần tử (các sinh viên). Mỗi phần tử xử lý tương đối phức tạp (truy vấn, tính tốn, gọi thủ tục khác, điều kiện rẽ nhánh, cập nhật dữ liệu, ). Cách xử lý các phần tử là như nhau. ⇒ Sử dụng cursor là thích hợp Cursor chứa các sinh viên của lớp cần xét, chỉ cần chứa mã sinh viên là được. • Xây dựng thủ tục Create procedure XepLoaiSVLop @Lop nvarchar(10), @SoSVGioi int out As Declare @DTB float Declare @XepLoai nvarchar(20) Declare @MaSV nvarchar(10) Declare cur_SV cursor For (select MaSV from SinhVien where Lop=@Lop) Open cur_SV Fetch Next from cur_SV into @MaSV While @@FETCH_STATUS = 0 Begin Exec XepLoaiSV @MaSV, @DTB output, @XepLoai output Update SinhVien set DTB = @DTB, XepLoai=@XepLoai Where MaSV = @MaSV Fetch Next from cur_SV into @MaSV End Close cur_SV Deallocate cur_SV
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 32 Set @SoSVGioi = (select count(*) from sinhvien where lop = @Lop and XepLoai = N’Giỏi’) Go V. Hàm người dùng (User Defined Functions) 1. Khái niệm hàm người dùng • Giống stored procedure: − mã lệnh cĩ thể tái sử dụng. − Chấp nhận các tham số input. − Biên dịch một lần và từ đĩ cĩ thể gọi khi cần. • Khác stored procedure: − Chấp nhận nhiều kiểu giá trị trả về (chỉ một giá trị trả về). − Khơng chấp nhận tham số output. − Khác về cách gọi thực hiện. • Cĩ thể xem hàm người dùng thuộc về 3 loại tùy theo giá trị trả về của nĩ: − Giá trị trả về là kiểu dữ liệu cơ sở (int, varchar, float, datetime ). − Giá trị trả về là Table cĩ được từ một câu truy vấn. − Giá trị trả về là table mà dữ liệu cĩ được nhờ tích lũy dần sau một chuỗi thao tác xử lý và insert. 2. Khai báo và sử dụng Khai báo hàm người dùng Loại 1: Giá trị trả về là kiểu dữ liệu cơ sở Create function func_name ( {parameter_name DataType [= default ] } [, n]) returns DataType As Begin Return {value | variable | expression} End Ví dụ:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 33 Create function SoLonNhat (@a int,@b int,@c int) return int As Begin declare @max int set @max = @a if @b > max set @max = @b if @c > max set @max = @c return @max End Loại 2: Giá trị trả về là một bảng cĩ được từ một câu truy vấn Create function func_name ( {parameter_name DataType [= default ] } [, n]) returns Table As Return [ ( ]select_statement [ ) ] Go Ví dụ: Viết hàm in danh sách các mặt hàng của một mã đơn hàng cho trước Create function DanhSachMatHang ( @MaDonHang varchar(10) ) returns Table As Return (Select MH.TenHang,MH.DonGia From ChiTietDH CT, MatHang MH Where CT.MaDH = @MaDonHang and CT.MaMH = MH.MaMH) Go Loại 3: Giá trị trả về là một bảng mà dữ liệu cĩ được nhờ tích lũy dần sau một chuỗi thao tác xử lý và insert.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 34 Create function func_name ( {parameter_name DataType [= default ] } [, n]) returns TempTab_name Table(Table_definition) As Begin Return End Go Ví dụ: Create function DanhSachLop () returns @DS Table(@MaLop varchar(10),@SoSV int) As các xử lý insert dữ liệu vào bảng DS return Go Lưu ý: Trong thân hàm khơng được sử dụng các hàm hệ thống bất định (Built-in nondeterministic functions ), bao gồm : − GETDATE − GETUTCDATE − NEWID − RAND − TEXTPTR − @@TOTAL_ERRORS, @@CPU_BUSY, @@TOTAL_READ, @@IDLE, @@TOTAL_WRITE, @@CONNECTIONS Sử dụng hàm người dùng Các hàm người dùng được sử dụng trong câu truy vấn, trong biểu thức phù hợp kiểu dữ liệu trả về của nĩ. Ví dụ: − Select dbo.SoLonNhat(3,5,7) − Select * from DanhSachLop() Lưu ý: − Nếu dùng giá trị mặc định của tham số, phải dùng từ khĩa default.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 35 − Khi gọi hàm loại 1 (trả về giá trị cơ bản), phải cĩ tên owner của hàm đi kèm (ví dụ dbo.SoLonNhat(5,8,-10)). Thay đổi hàm người dùng Thay từ khĩa create trong các lệnh tạo hàm bằng từ khĩa alter Xĩa hàm người dùng Drop function tên_hàm_cần _xĩa Ví dụ: Drop function DanhSachMatHang 3. Các hàm hệ thống Ngồi các hàm do người dùng định nghĩa, SQL Server cịn cung cấp các hàm xây dựng sẵn của hệ thống. Các hàm này cung cấp tiện ích như xử lý chuỗi, xử lý thời gian, xử lý số học Sinh viên tìm hiểu thêm về các hàm này trong Books on-line và các tài liệu tham khảo. • Để tạo hàm hệ thống cần tiến hành theo các bước sau: Tạo hàm trong cơ sở dữ liệu Master Tên hàm bắt đầu bởi fn_functionName Thay đổi chủ nhân của hàm bằng thủ tục sp_changeobjectowner như sau: EXEC sp_changeobjectowner ‘fn_ functionName’ , ‘system_function_schema’ Ví dụ: Tạo hàm hệ thống thực chuyển đổi một biến kiểu ngày tháng sang kiểu chuỗi. Tạo hàm fn_doingay create function fn_doingay(@ngay datetime) returns char(10) as begin return convert(nchar(10),@ngay,103) end Thay đổi chủ nhân của hàm EXEC sp_changeobjectowner 'fn_doingay' , 'system_function_schema' Sau lệnh này hàm fn_doingay cĩ thể dùng được cho CSDL bất kỳ. Select manv, hoten, fn_doingay(ngaysinh) From nhanvien VI. Triggers và cài đặt ràng buộc dữ liệu 1. Giới thiệu • Trigger là một loại stored procedure đặc biệt cĩ các đặc điểm sau:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 36 − Tự động thực hiện khi cĩ thao tác insert, delete hoặc update trên dữ liệu. − Thường dùng để kiểm tra các ràng buộc tồn vẹn của CSDL hoặc các qui tắc nghiệp vụ. − Một trigger được định nghĩa trên một bảng, nhưng các xử lý trong trigger cĩ thể sử dụng nhiều bảng khác. • Xử lý của trigger thường cần sử dụng đến hai bảng tạm: − Inserted: chứa các dịng vừa mới được thao tác insert/ update thêm vào bảng. − Deleted: chứa các dịng vừa mới bị xĩa khỏi bảng bởi thao tác update/delete. Lưu ý: update = delete dịng chứa giá trị cũ + insert dịng chứa giá trị mới − Inserted và deleted là các bảng trong bộ nhớ chính: Cục bộ cho mỗi trigger. Cĩ cấu trúc giống như bảng (table) mà trigger định nghĩa trên đĩ Chỉ tồn tại trong thời gian trigger đang xử lý. − Nế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. 2. Sử dụng Trigger Khai báo trigger − Cú pháp: Create trigger tên_trigger On {tên_bảng|tên_view} {For| After| Instead of } { [delete] [,] [insert] [,] [update] } As { các lệnh T-sql } Go rong đĩ: For | 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ị xố chỉ nằm trong bảng deleted (đã bị xố 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. Instead of: − Trigger được gọi thực hiện thay cho thao tác delete/ insert/ update tương ứng:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 37 Các dịng mới được thêm chỉ chứa trong bảng inserted. Các dịng bị chỉ định xố nằm đồng thời trong bảng deleted và bảng dữ liệu (dữ liệu khơng bị xố). − Trigger Instead of thường được dùng để xử lý cập nhật trên khung nhìn. Lưu ý: − Lệnh tạo trigger phải là lệnh đầu tiên trong một query batch. − Trên một bảng cĩ thể định nghĩa nhiều trigger for/after cho mỗi thao tác nhưng chỉ cĩ thể định nghĩa một trigger instead of cho mỗi thao tác. − Khơng thể định nghĩa trigger instead of update/ delete trên bảng cĩ cài đặt khĩa ngoại dạng update cascade/ delete cascade. − 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 đượ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 Sử dụng trigger cài đặt một số loại ràng buộc Ví dụ 1- Ràng buộc liên thuộc tính – liên quan hệ Cho CSDL: DatHang(MaPDH, NgayDH, ) GiaoHang(MaPGH, MaPDH, NgayGH, ) Ràng buộc: Ngày giao hàng khơng thể nhỏ hơn ngày đặt hàng tương ứng Bảng tầm ảnh hưởng: Thêm Xĩa Sửa DatHang - - + (NgayDH) GiaoHang + - + (NgayGH, MaPDH) Cần cài đặt trigger cho thao tác sửa trên bảng DatHang, và thêm/sửa trên bảng Giaohang Trigger cho thao tác thêm và sửa trên giao hàng: Create trigger tr_GH_ins_upd_NgayGH On GIAOHANG for insert, update As if update(MaPDH) or update (NgayGH) if exists(select * from inserted i, DatHang d where i.MaPDH = d.MaPDH
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 38 and i.NgayGH 2 begin raiserror('So lan thi phai <= 2', 0,1) rollback transaction end Bài tập: Tạo trigger ứng với thao tac Update trên bảng KetQua. Ví dụ 3: Trigger cho việc thực hiện một thao tác cập nhật dữ liệu nào đĩ. Cho quan hệ CHI_TIET_HOA_DON(MaHD,STT, MaMH, SoLuong, DonGia, ThanhTien).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 39 • Viết trigger thực hiện cập nhật giá trị của ThanhTien khi thêm một chi tiết hĩa đơn
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 40 Chương 4 BẢO MẬT VÀ AN TỒN DỮ LIỆU I. Bảo mật trong hệ quản trị cơ sở dữ liệu 1. Khái niệm cơ bản về bảo mật Nhằm bảo vệ hệ thống CSDL khơng bị xâm nhập, người quản trị cơ sở dữ liệu phải quyết định cho phép hay khơng cho phép người dùng truy cập và thao tác trên cơ sở dữ liệu dựa vào nhiệm vụ của người dùng trên hệ CSDL. Người quản trị thường dựa trên nền tảng lý thuyết bảo mật của hệ cơ sở dữ liệu đa người dùng, nhằm tìm ra phương pháp bảo mật theo đúng với nhu cầu của bảo mật dữ liệu. Với mục đích tăng tính bảo mật dữ liệu, SQL Server hỗ trợ các tính năng cho phép người quản trị thiết lập cơ chế bảo vệ cơ sở dữ liệu trong mơi trường đa người dùng, bao gồm các yếu tố chính sau: o Vai trị của người dùng trong hệ thống và cơ sở dữ liệu. o Quyền sử dụng các ứng dụng cơ sở dữ liệu trong SQL Server. o Quyền tạo và sửa đổi cấu trúc các đối tượng CSDL. o Quyền truy cập, xử lý dữ liệu. Khi đăng nhập vào một hệ thống CSDL đa người dùng, người sử dụng cần phải cung cấp UserID (tài khoản) và Password (mật khẩu). Dựa trên UserID hệ thống cĩ khả năng kiểm sốt tất cả các hành vi của người sử dụng trên CSDL SQL Server. Để thực hiện được chức năng này, người quản trị CSDL cần phải thiết lập các quyền xử lý và truy cập vào CSDL khi tạo ra UserID, ngồi ra cịn cĩ một số thuộc tính khác của SQL Server như quyền backup dữ liệu, trao đổi dữ liệu với các ứng dụng CSDL khác, Khi nĩi đến bảo mật, người quản trị cần quan tâm đến các thơng tin sau của người dùng: o Một người dùng chỉ cĩ một UserID và một mật khẩu. o Thời gian cĩ hiệu lực của mật khẩu. o Giới hạn chiều dài của mật khẩu. o Giới hạn người sử dụng theo license hay mở rộng. o Thơng tin về người sử dụng. Khi tạo người sử dụng, tên tài khoản cần rõ ràng, dễ hiểu dễ gợi nhớ, và khơng cho phép các ký tự đặc biệt, khơng nên cĩ khoảng trắng. 2. Lựa chọn bảo mật
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 41 Khi tạo ra một người dùng (login user) trong SQL Server, cĩ 3 cách để tăng tính bảo mật cho người sử dụng đĩ: o Giao tiếp với hệ điều hành: sử dụng UserID và Password của hệ điều hành Windows để đăng nhập SQL Server. Với loại bảo mật này, người dùng truy cập vào mạng và cĩ thể sử dụng CSDL SQL Server, đồng thời một người dùng cĩ UserID và Password cĩ thể sử dụng tài nguyên trên mạng. o Bảo mật chuẩn: với loại này, người sử dụng cĩ UserID và Password tách rời với hệ điều hành mạng, ứng với loại bảo mật này người sử dụng chỉ cĩ hiệu lực trong CSDL SQL Server, khơng thể sử dụng tài nguyên trên mạng. o Tổng hợp cả hai trường hợp trên: một số người dùng sử dụng quyền sử dụng trên hệ điều hành và SQL Server, một số khác chỉ sử dụng quyền truy cập vào SQL Server. Lưu ý: Tài khoản người dùng cĩ giá trị trên SQL Server hiện hành, khi sang một SQL Server khác phải tạo ra tài khoản người dùng trên server đĩ. o SQL Server cung cấp các chức năng hay các thủ tục tạo mới và quản trị người dùng CSDL SQL Server như sau: Sử dụng thủ tục sp_addlogin. Sử dụng cơng cụ Enterprise Manager. • Sử dụng thủ tục sp_addlogin Cú pháp sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ] trong đĩ các tham số cĩ ý nghĩa như sau: @loginame: tên tài khoản sẽ tạo. @passwd: mật khẩu cho người dùng cĩ tài khoản trên. @defdb: cơ sở dữ liệu mặc định khi người dùng đăng nhập vào SQL Server. @deflanguage: ngơn ngữ mặc nhiên cho người dùng SQL Server. @sid: số nhận dạng hệ thống khi người dùng đăng nhập vào. @encryptopt: khi tạo tài khoản người dùng trong CSDL, các thơng tin về tài khoản, mật khẩu được lưu trữ trong bảng sysusers của CSDL Master, nếu bạn cung cấp tham số skip_enctription thì khơng mã hố mật khẩu trước khi lưu vào bảng sysusers, nếu khơng cung cấp tham số hay để trống, SQL Server sẽ mã hố mật khẩu trước khi lưu trữ.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 42 Ví dụ: tạo người dùng cĩ tên ‘nam’, mật khẩu ‘123’, cơ sở dữ liệu mặc định ‘QLKyNang’ Exec sp_addlogin ‘nam’, ‘123’, ‘QLKyNang’ - Thay đổi mật khẩu sp_password [[ @old = ] 'old_password' ,] { [ @new =] 'new_password' } [ , [ @loginame = ] 'login' ] 3. Quyền người dùng và quản trị quyền người dùng Quyền của người dùng được định nghĩa như mức độ người dùng cĩ thể hay khơng thể thực thi trên CSDL, quyền được chia thành 4 loại như sau: o Quyền truy cập vào SQL Server. o Quyền truy xuất vào CSDL. o Quyền thực hiện trên các đối tượng của CSDL. o Quyền xử lý dữ liệu. Cấp phát quyền truy cập vào CSDL Cú pháp: Use db_name Go sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name_in_db' [OUTPUT]] Các tham số: @loginame: tài khoản của người sử dụng đăng nhập vào SQL Server @name_in_db: tạo bí danh (tên khác) của tài khoản người dùng khi truy cập vào CSDL db_name được chỉ định, nếu khơng chỉ rõ CSDL muốn cho phép người dùng truy cập thì người dùng được cấp quyền trên CSDL hiện hành. Loại bỏ quyền truy cập vào CSDL db_name của người dùng Use db_name Go sp_revokedbaccess [@loginame =] 'login' Cấp phát quyền thực thi trên cơ sở dữ liệu Sau khi cấp phát quyền cho người dùng truy cập vào CSDL, kế tiếp cho phép người dùng đĩ cĩ quyền truy cập và xử lý các đối tượng trong CSDL cũng như xử lý dữ liệu trên các đối tượng đĩ. Các quyền truy cập trên các đối tượng trong một CSDL:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 43 Quyền Diễn giải SELECT Cho phép người sử dụng nhìn thấy dữ liệu, nếu người sử dụng cĩ quyền này thì họ chỉ cĩ thể thực thi những phát biểu select để truy vấn dữ liệu trên các bảng hay các view được cho phép. INSERT Cho phép người sử dụng thêm dữ liệu, nếu người sử dụng cĩ quyền này, họ cĩ thể thực hiện phát biểu Insert, đối với một số hệ thống CSDL khác, muốn thực thi phát biểu Insert, người sử dụng phải cĩ quyền Select, trong SQL Server quyền Insert khơng liên quan đến quyền truy vấn Select. UPDATE Quyền này cho phép người sử dụng chỉnh sửa dữ liệu bằng phát biểu Update. DELETE Quyền này cho phép người sử dụng xĩa dữ liệu bằng phát biểu Delete. REFERENCE Cho phép người sử dụng thêm dữ liệu vào bảng cĩ khĩa ngoại bằng phát biểu Insert, trong SQL Server quyền Insert khơng liên quan đến quyền truy vấn Select. EXECUTE Quyền này cho phép người sử dụng thực thi các thủ tục (SP) trong CSDL. Thủ tục cấp quyền GRANT GRANT ALL | [, n] ON [( [, n])] | TO [, n] [WITH GRANT OPTION] [AS ] Trong đĩ: từ khĩa ALL cho phép người sử dụng cĩ tất cả các quyền. PERMISSION là một trong các quyền: SELECT, INSERT, UPDATE, DELETE, REFERENCE, EXECUTE. Chỉ rõ những bảng dữ liệu, view hoặc thủ tục nào cho phép người dùng truy cập và xử lý. Từ chối quyền truy vấn và xử lý dữ liệu DENY ALL | [, n] ON [( [, n])] |
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 44 TO [, n] [Cascade] Loại bỏ quyền truy vấn và xử lý dữ liệu REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | permission [ , n ] } { [ ( column [ , n ] ) ] ON { table | view } | ON { table | view } [ ( column [ , n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } { TO | FROM } security_account [ , n ] [ CASCADE ] [ AS { group | role } ] Quyền tạo đối tượng trong CSDL Trong CSDL cĩ một số đối tượng và các chức năng như sao lưu dữ liệu mà mỗi người sử dụng trên CSDL tùy theo chức năng và nhiệm vụ cụ thể được phép hay khơng được phép tạo các đối tượng như table, view, stored procedure, và tạo CSDL. Các quyền tạo các đối tượng như sau: Create Database. Create Table. Create View. Create Procedure. Create Rule. Create Default. Backup Database. Backup Log. Để phân quyền tạo đối tượng trong CSDL cho người dùng, trong SQL Server cĩ thể sử dụng thủ tục GRANT như sau: GRANT TO [, n]
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 45 4. Vai trị của người sử dụng trong SQL Server và cơ sở dữ liệu Vai trị trên SQL Server: Vai trị (Role) Diễn giải sysadmin Cĩ các quyền tương đương với sa. serveradmin Cấu hình một số tham số và tắt server. setupadmin Bị giới hạn bớt một số chức năng liên kết server và khởi động một số thủ tục. securityadmin Quản lý người dùng và tạo CSDL. processadmin Được phép dừng các giao tác đang thực hiện trên CSDL và một số quá trình thực hiện khác của SQL Server. dbcreator Được phép tạo CSDL. Diskadmin Quản lý các tập tin liên quan đến CSDL SQL Server. Vai trị trên CSDL: Vai trị Diễn giải Với vai trị này, người sử dụng (NSD) thuộc nhĩm sở hữu db_owner CSDL mới cĩ thể truy cập vào CSDL. db_accessadmin Thực hiện các chức năng giống như securityadmin. NSD được phép select trên các bảng dữ liệu của các người db_datareader dùng khác trong CSDL. NSD được phép insert, update, delete trên các bảng dữ liệu db_datawriter của các người dùng khác trong CSDL. db_ddladmin NSD cĩ thể thêm hay chỉnh sửa các đối tượng của CSDL. db_securityadmin NSD cĩ quyền tương đương với quyền của securityadmin. db_backupoperator NSD cĩ thể thực hiện chức năng backup dữ liệu. db_denydareader Khơng cho phép sử dụng phát biểu SELECT trên tất cả các bảng dữ liệu của CSDL. db_denydawriter Khơng cho phép sử dụng phát biểu INSERT, UPDATE, DELETE trên tất cả các bảng dữ liệu của CSDL. Sử dụng các thủ tục hệ thống để tạo một role mới, thêm một người dùng vào một role, loại bỏ người sử dụng ra khỏi một role. o Tạo một role sp_addrole [ @rolename = ] 'role_name' [ , [ @ownername = ] 'owner' ] Trong đĩ:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 46 @rolename: tên role mới @ownername: chủ sở hữu của role mới, mặc định là dbo Sau khi tạo role mới cần phải gán một số quyền truy cập và xử lý trên các bảng dữ liệu nào đĩ trong CSDL cho role mới đĩ. o Thêm người sử dụng vào Role sp_addrolemember [ @rolename = ] 'role_name' , [ @membername = ] ‘login_ID' o Loại bỏ người sử dụng ra khỏi một role sp_droprolemember [ @rolename = ] 'role_name' , [ @membername = ] 'login_ID ' II. Bản sao dữ liệu Phần này sẽ giới thiệu kỹ thuật làm giảm lưu lượng dữ liệu giao dịch với SQL Server khi đã cấu hình nhiều Server trên mạng. 1. Giới thiệu về nhân bản dữ liệu Nhân bản dữ liệu (Replication) là cơng cụ được sử dụng để sao chép một hoặc nhiều CSDL đến một hoặc nhiều server (SQL Server) khác. Các Server được đặt trong mạng máy tính nội bộ (LAN). Người khai thác cĩ thể thực hiện truy cập đến CSDL cĩ trong Server chứa dữ liệu được nhân bản. Dữ liệu giữa các máy được thực hiện đồng bộ với nhau theo lịch hoặc theo sự kiện, khi cĩ yêu cầu. Nhân bản dữ liệu cĩ những ưu điểm sau: − Dữ liệu được lưu trữ ở nhiều nơi, hiệu quả trong việc cĩ nhiều ứng dụng cùng truy cập, khai thác. − Thích hợp với các ứng dụng phân tích dữ liệu của hệ thống xử lý giao dịch trực tuyến (OLTP) trong kho dữ liệu (Data warehouse). − Cĩ thể khai thác dữ liệu khi khơng kết nối đến Server. − Giảm thiểu xung đột do số lượng lớn các giao dịch trên mạng. − Là một giải pháp an tồn khi Server bị lỗi hoặc bảo dưỡng. Mơ hình nhân bản. Dịch vụ nhân bản dữ liệu gồm các thành phần cơ bản sau: Publisher, Distributor, Subscribers, Publications, Articles, Subscriptions. − Publisher: là server cung cấp dữ liệu nhân bản cho các server khác. Một publisher cĩ thể thiết lập nhiều bộ dữ liệu nhân bản (gọi là publication).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 47 − Distributor: là server quản lý các thơng tin nhân bản, lưu trữ dữ liệu trong các giao dịch thực hiện nhận và chuyển dữ liệu từ Publisher đến các Subscriber. Remote distributor là server tách rời khỏi publisher và được cấu hình là distributor. Local distributor là một server được cấu hình là Publisher và Distributor. − Subscriber: Là server nhận dữ liệu nhân bản. Subscriber gắn liền với publication (là máy chủ nhận dữ liệu nhân bản của một bộ dữ liệu cấu hình nhân bản). − Article: Là một bảng, tập dữ liệu hoặc đối tượng của CSDL cấu hình để nhân bản. − Publication: Là một tập gồm một hoặc nhiều article. − Subscription: Là một giao dịch yêu cầu bản sao bộ dữ liệu hoặc các đối tượng của CSDL thực hiện nhân bản. Trong mỗi giao dịch publisher thực hiện đẩy (push subscription) dữ liệu, subscriber thực hiện kéo (pull subscription). Nhân bản dữ liệu được thực hiện theo những mơ hình cơ bản sau: − Central Publisher: Là mơ hình Publisher và Distributor thiết lập trên một máy. Gồm các mơ hình sau: Một Publisher và một Subscriber:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 48 Một Publisher và nhiều Subscriber. Publisher và Subscriber được thiết lập trên một máy: − Publisher và Distributor khơng thiết lập trên một máy: − Republisher: Là mơ hình Publisher xuất bản dữ liệu đến Subscriber, sau đĩ Subscriber được thiết lập là Publisher xuất bản dữ liệu đến Subscriber khác.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 49 Đường truyền giữa hai máy được thiết lập là Publisher cĩ thể tốc độ thấp, phù hợp với vị trí xa nhau. Ví dụ mơ hình giữa các vùng cách xa nhau: + Central Subscriber: Là mơ hình Subscriber thiết lập nhận dữ liệu xuất bản từ nhiều Publisher.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 50 Những kiểu nhân bản dữ liệu. Cĩ 3 kiểu nhân bản dữ liệu: Snaphot, Transaction, Merge. Snapshot replication: là kiểu nhân bản thực hiện sao chép, phân tán dữ liệu hoặc các đối tượng của CSDL tại một thời điểm. Snapshot thường được sử dụng cho những tình huống sau: Dữ liệu thường là tĩnh, ít thay đổi. Nhân bản số lượng dữ liệu nhỏ. Transaction replication: là kiểu nhân bản mà bắt đầu bằng nhân bản Snapshot, sau đĩ sẽ thực hiện nhân giao dịch dữ liệu theo các sự kiện insert, update, delete và những thay đổi liên quan đến thực hiện thủ tục, khung nhìn chỉ mục. Nhân bản kiểu này cho phép thực hiện lọc dữ liệu tại Publisher, cho phép người sử dụng sửa đổi dữ liệu nhân bản tại
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 51 subscriber và chuyển dữ liệu đã sửa đổi đến Publisher hoặc Subscriber khác, dữ liệu sửa đổi này cĩ thể coi là dữ liệu được xuất bản. Nhân bản kiểu này được thực hiện khi: Muốn sửa đổi dữ liệu được xuất bản chuyển đến Subscriber, thời gian thực hiện theo giây, hoặc tức thời. Cần giao dịch trên tồn bộ hệ thống nhân bản dữ liệu (dữ liệu cĩ thể chuyển đến tất cả các Subscriber hoặc khơng chuyển đến Subscriber nào). Subscriber thường xuyên kết nối với Publisher. Merge replication: là kiểu nhân bản dữ liệu cho phép thực hiện nhân bản sửa đổi dữ liệu trên nhiều Subscriber, cĩ thể kết nối (online) hoặc khơng kết nối (offline) đến Publisher. Dữ liệu sẽ được đồng bộ theo lịch hoặc theo yêu cầu, dữ liệu cập nhật cĩ thời điểm sau sẽ được chấp nhận. Kiểu nhân bản này thực hiện khi: Nhiều Subscriber cĩ nhu cầu cập nhật dữ liệu và chuyển dữ liệu cập nhật đến Publisher hoặc Subscriber khác. Subscriber yêu cầu nhận hoặc chuyển dữ liệu khi offline, đồng bộ dữ liệu với các Subscriber và Publisher sau.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 52 2. Cấu hình Publisher và Distributor. Trước khi thực hiện cấu hình các máy thành Publisher hay Distributor ta phải thực hiện chạy dịch vụ SQL Server Agent trong chức năng Service manager. Các bước cấu hình như sau: - Chọn Server cần cấu hình -> Replication - Nhấp phải chuột -> Configure Publishing Subscription and Distribution - Thực hiện theo các bước: + Chọn thư mục Snapshot
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 53 - Đặt tên CSDL của Distribution. - Chọn Server cấu hình thành Publisher.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 54 - Chọn CSDL tham gia nhân bản, kiểu nhân bản. - Chọn Server được cấu hình là Subscriber của Publisher đang thiết lập. - Kết thúc. Tạo Publication Bước này sẽ thực hiện tạo Publication, cách thực hiện như sau: + Chọn Publication trong Replication của Publisher. + Nhấn phải chuột → chọn New Publication
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 55 + Thực hiện theo các bước: - Chọn CSDL cần xuất bản dữ liệu hoặc đối tượng. - Chọn kiểu nhân bản (trong ví dụ này thực hiện kiểu Merge). - Chọn phiên bản SQL Server của Subscriber. - Chọn Article tham gia Publication.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 56 - Đặt tên cho Publication. - Kết thúc. Tạo Push Subscription. Bước này thực hiện tạo thủ tục đẩy (push) từ Publisher (Distributor trong ví dụ này) đến Subscriber, được thực hiện trên Publisher. Các bước thực hiện như sau:
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 57 - Chọn Publication của Publisher -> Nhấn phải chuột -> Push new Subscription - Chọn Subscriber. - Chọn CSDL trên Subscriber nếu đã cĩ, nếu chưa cĩ thực hiện chọn chức năng tạo mới. - Chọn lịch thực hiện đồng bộ dữ liệu. - Kết thúc. Sau khi thiết lập xong trên Subscriber sẽ cĩ CSDL theo tên đã tạo. Tạo Pull Subscription
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 58 Bước này thực hiện tạo cơng cụ kéo dữ liệu nhân bản từ Publisher về Subscriber, được thực hiện trên Subscriber. - Chọn Subscription của Subscriber → Nhấn phải chuột → New Pull Supcription - Thực hiện theo các bước: + Chọn Publication. + Chọn Agent tham gia kết nối Publisher. - Chọn CSDL đích.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 59 - Thực hiện tiếp các bước và kết thúc. Nếu đã tạo Push Subscription với một CSDL sẽ khơng được tạo Pull Subscription với CSDL đĩ. Thực hiện đồng bộ dữ liệu Sau khi thiết lập theo các mơ hình nhân bản xong, cĩ thể thực hiện đồng bộ dữ liệu bằng cách: - Thực hiện theo lịch. - Theo yêu cầu: Chọn Subscription (Push hoặc Pull) → Nhấn phải chuột → Start Synchronizing. Sau khi thực hiện xong dữ liệu sẽ được đồng bộ giữa Publisher và Subscriber. Ngồi thực hiện theo cơng cụ ta cĩ thể tìm hiểu thực hiện nhân bản theo câu lệnh T-SQL hoặc Stored Procedure. III. Sao lưu và khơi phục dữ liệu 1. Lý do phải sao lưu và khơi phục dữ liệu Trong quá trình thực hiện quản trị CSDL SQL Server thì một số nguyên nhân sau đây bắt buộc bạn phải xem xét đến kỹ thuật sao lưu và khơi phục dữ liệu: Ổ đĩa bị hỏng (chứa các tập tin CSDL). Server bị hỏng. Nguyên nhân bên ngồi (thiên nhiên, hỏa hoạn, mất cắp, ). User vơ tình xĩa dữ liệu. Bị vơ tình hay cố ý làm thơng tin sai lệch. Bị hack. 2. Các loại sao lưu dữ liệu Sao lưu (Backup) dữ liệu trong SQL Server gồm các loại sau: Full Database Backups: sao chép tồn bộ CSDL (các tập tin bao gồm các bảng, khung nhìn, các đối tượng khác). Differential Database Backups: sao chép những dữ liệu thay đổi trong Data file kể từ lần full backup gần nhất. File or file group backups: sao chép một file đơn hay file group. Differential File or File Group Backups: thực hiện như Differential Database nhưng copy phần dữ liệu thay đổi của file đơn hoặc file group. Transaction log backups: Ghi nhận tất cả các transaction chứa trong transaction log file kể từ lần transaction log backup gần nhất. Với loại sao lưu này ta cĩ thể khơi phục dữ liệu tại một thời điểm. 3. Các mơ hình khơi phục dữ liệu
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 60 Full Recovery model: Là mơ hình phục hồi tồn bộ hoạt động giao dịch của dữ liệu (Insert, Update, Delete, hoạt động bởi lệnh bcp, bulk insert). Với mơ hình này ta cĩ thể phục hồi dữ liệu tại một thời điểm trong quá khứ đã được lưu trong transaction log file. Bulk-Logged Recovery Model: Mơ hình này được thực thi cho các thao tác bcp, bulk insert, create chỉ mục, writetext, updatetext, các hoạt động này chỉ nhật ký sự kiện vào log để biết mà khơng sao lưu tồn bộ dữ liệu, chi tiết như trong full recover. Các sự kiện Insert, Update, Delete vẫn đŲợc nhật ký và khơi phục bình thường. Simple Recovery Model: Với mơ hình này bạn chỉ phục hồi lại thời điểm sao lưu gần nhất mà khơng theo thời điểm khác trong quá khứ. Cách đặt mơ hình khơi phục: - Chọn CSDL. - Nhấn nút phải chuột → Properties → Options → Recovery → chọn Model. Xét ví dụ sau: Giả sử ta cĩ một CSDL được backup theo chiến lược như hình vẽ bên dưới Nhìn hình ta thấy CSDL được lập lịch Full Database Backup vào ngày chủ nhật, Differential Database Backup vào ngày thứ ba và thứ năm, cịn Log Database Backup vào 5 ngày trong tuần, ngày thứ sáu cĩ sự cố với CSDL, data file bị hỏng. Vấn đề đặt ra là phải phục hồi dữ liệu và CSDL hoạt động bình thường. Ta phải làm các bước sau: Thực hiện Backup log file (giả sử log file khơng bị hỏng). Khơi phục Full Database của ngày chủ nhật. Phục hồi Differential Database của ngày thứ năm. Khơi phục Transaction log backup ngày thứ năm.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 61 4. Sao lưu cơ sở dữ liệu (Backup Database) Trước khi xem xét kỹ thuật sao lưu CSDL, ta thống nhất một số thuật ngữ bằng tiếng Anh như sau: Backup: Là quá trình copy tồn bộ hoặc một phần database, transaction log, file, file group thành lập một backup set được chứa trong backup media (disk hoặc tape) bằng cách sử dụng một backup device (tape drive name hoặc physical filename). Backup Device: Một file vật lý hoặc một drive tape. Backup file: Một file chứa Backup set. Backup media: là Disk hoặc tape. Backup set: Một bộ backup một lần backup đơn chứa trên backup media. Các bước thực hiện backup như sau: − Chọn CSDL cần backup. − Nhấn phải chuột → All Tasks → Backup Database - Nhập các tham số, lựa chọn kiểu. 5. Khơi phục dữ liệu (Restore Database) Là chức năng thực hiện khơi phục dữ liệu đã sao lưu, tùy theo chiến lược backup mà người quản trị cĩ thể phục hồi đến thời điểm nào, thu được bộ dữ liệu trong quá khứ như thế nào. Khơi phục dữ liệu được thực hiện theo thứ tự backup, thơng tin này được lưu trữ trong msdb. Các bước thực hiện như sau: − Chọn mục Databases → Nhấn nút phải chuột → All Tasks → Restore Database − Nhập tham số, chọn mơ hình khơi phục. IV. Quản lý giao dịch 1. Các khái niệm Một giao dịch là một đơn vị thực hiện chương trình truy xuất và cĩ thể cập nhật nhiều mục dữ liệu. Một giao dịch thường là kết quả của sự thực hiện một chương trình người dùng được viết trong một ngơn ngữ thao tác dữ liệu mức cao hoặc một ngơn ngữ lập trình (SQL, COBOL, PASCAL ), và được phân cách bởi các câu lệnh (hoặc các lời gọi hàm) cĩ dạng begin transaction và end transaction. Giao dịch bao gồm tất cả các hoạt động được thực hiện giữa begin và end transaction. Để đảm bảo tính tồn vẹn của dữ liệu, ta yêu cầu hệ CSDL duy trì các tính chất sau của giao dịch: • Tính nguyên tử (Atomicity). Hoặc tồn bộ các hoạt động của giao dịch được phản ánh đúng đắn trong CSDL hoặc khơng cĩ gì cả. • Tính nhất quán (consistency). Sự thực hiện của một giao dịch là cơ lập (Khơng
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 62 cĩ giao dịch khác thực hiện đồng thời) để bảo tồn tính nhất quán của CSDL. • Tính cơ lập (Isolation). Cho dù nhiều giao dịch cĩ thể thực hiện đồng thời, hệ thống phải đảm bảo rằng đối với mỗi cặp giao dịch Ti, Tj , hoặc Tj kết thúc thực hiện trước khi Ti khởi động hoặc Tj bắt đầu sự thực hiện sau khi Ti kết thúc. Như vậy mỗi giao dịch khơng cần biết đến các giao dịch khác đang thực hiện đồng thời trong hệ thống. • Tính bền vững (Durability). Sau một giao dịch hồn thành, các thay đổi đã được tạo ra đối với CSDL vẫn cịn ngay cả khi xảy ra sự cố hệ thống. Các tính chất này thường được gọi là các tính chất ACID (Các chữ cái đầu của bốn tính chất). Ta xét một ví dụ: một hệ thống ngân hàng gồm một số tài khoản và một tập các giao dịch truy xuất và cập nhật các tài khoản. Tại thời điểm hiện tại, ta giả thiết rằng CSDL nằm trên đĩa, nhưng một vài phần của nĩ đang nằm tạm thời trong bộ nhớ. Các truy xuất CSDL được thực hiện bởi hai hoạt động sau: • READ(X): chuyển mục dữ liệu X từ CSDL đến buffer của giao dịch thực hiện hoạt động READ này. • WRITE(X): chuyển mục dữ liệu X từ buffer của giao dịch thực hiện WRITE đến CSDL. Trong hệ CSDL thực, hoạt động WRITE khơng nhất thiết dẫn đến sự cập nhật trực tiếp dữ liệu trên đĩa; hoạt động WRITE cĩ thể được lưu tạm thời trong bộ nhớ và được thực hiện trên đĩa muộn hơn. Trong ví dụ, ta giả thiết hoạt động WRITE cập nhật trực tiếp CSDL. Ti là một giao dịch chuyển 50 từ tài khoản A sang tài khoản B. Giao dịch này cĩ thể được xác định như sau: Ti : READ(A); A:=A - 50; WRITE(A) READ(B); B:=B + 50; WRITE(B); Ta xem xét mỗi yêu cầu trong ACID: • Tính nhất quán: Địi hỏi nhất quán ở đây là tổng của A và B là khơng thay đổi bởi sự thực hiện giao dịch. Nếu khơng cĩ yêu cầu nhất quán, tiền cĩ thể được tạo ra hay mất bởi giao dịch. Dễ dàng kiểm nghiệm rằng nếu CSDL nhất quán trước một thực hiện giao dịch, nĩ vẫn nhất quán sau khi thực hiện giao dịch. Đảm bảo tính nhất quán
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 63 cho một giao dịch là trách nhiệm của người lập trình ứng dụng người đã viết ra giao dịch. Nhiệm vụ này cĩ thể được làm cho dễ dàng bởi kiểm thử tự động các ràng buộc tồn vẹn. • Tính nguyên tử: giả sử rằng ngay trước khi thực hiện giao dịch Ti, giá trị của các tài khoản A và B tương ứng là 1000 và 2000. Giả sử rằng trong khi thực hiện giao dịch Ti, một sự cố xảy ra cản trở Ti hồn tất thành cơng sự thực hiện của nĩ. Ta cũng giả sử rằng sự cố xảy ra sau khi hoạt động WRITE(A) đã được thực hiện, nhưng trước khi hoạt động WRITE(B) được thực hiện. Trong trường hợp này giá trị của tài khoản A và B là 950 và 2000. Ta đã mất 50$. Tổng A+B khơng cịn được bảo tồn. Như vậy, kết quả của sự cố là trạng thái của hệ thống khơng cịn phản ánh trạng thái của thế giới thực mà CSDL được giả thiết nắm giữ. Ta gọi trạng thái như vậy là trạng thái khơng nhất quán. Ta phải đảm bảo rằng tính khơng nhất quán này khơng xuất hiện trong một hệ CSDL. Tuy nhiên, ở tại một vài thời điểm, hệ thống cĩ thể ở trong trạng thái khơng nhất quán. Ví dụ giao dịch Ti, trong quá trình thực hiện cũng tồn tại thời điểm tại đĩ giá trị của tài khoản A là 950 và tài khoản B là 2000 – một trạng thái khơng nhất quán. Trạng thái này được thay thế bởi trạng thái nhất quán khi giao dịch đã hồn tất. Như vậy, nếu giao dịch khơng bao giờ khởi động hoặc được đảm bảo sẽ hồn tất, trạng thái khơng nhất quán sẽ khơng bao giờ xảy ra. Đĩ chính là lý do cĩ yêu cầu về tính nguyên tử: Nếu tính chất nguyên tử được đảm bảo, tất cả các hành động của giao dịch được phản ánh trong CSDL hoặc khơng cĩ gì cả. ý tưởng cơ sở để đảm bảo tính nguyên tử là như sau: hệ CSDL lưu vết (trên đĩa) các giá trị cũ của bất kỳ dữ liệu nào trên đĩ giao dịch đang thực hiện viết, nếu giao dịch khơng hồn tất, giá trị cũ được khơi phục để đặt trạng thái của hệ thống trở lại trạng thái trước khi giao dịch diễn ra. Đảm bảo tính nguyên tử là trách nhiệm của hệ CSDL, và được quản lý bởi một thành phần được gọi là thành phần quản trị giao dịch (transaction- management component). • Tính bền vững: tính chất bền vững đảm bảo rằng mỗi khi một giao dịch hồn tất, tất cả các cập nhật đã thực hiện trên cơ sở dữ liệu vẫn cịn đĩ, ngay cả khi xảy ra sự cố hệ thống sau khi giao dịch đã hồn tất. Ta giả sử một sự cố hệ thống cĩ thể gây ra việc mất dữ liệu trong bộ nhớ chính, nhưng dữ liệu trên đĩa thì khơng mất. Cĩ thể đảm bảo tính bền vững bởi việc đảm bảo hoặc các cập nhật được thực hiện bởi giao dịch đã được viết lên đĩa trước khi giao dịch kết thúc hoặc thơng tin về sự cập nhật được thực hiện bởi giao dịch và được viết lên đĩa đủ cho phép CSDL xây dựng lại các cập nhật khi hệ CSDL được khởi động lại sau sự cố. Đảm bảo tính bền vững là trách nhiệm của một thành phần của hệ CSDL được gọi là thành phần quản trị phục hồi (recovery-management component). Hai thành phần quản trị giao dịch và quản trị phục hồi quan hệ mật thiết với nhau.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 64 • Tính cơ lập: ngay cả khi tính nhất quán và tính nguyên tử được đảm bảo cho mỗi giao dịch, trạng thái khơng nhất quán vẫn cĩ thể xảy ra nếu trong hệ thống cĩ một số giao dịch được thực hiện đồng thời và các hoạt động của chúng đan xen theo một cách khơng mong muốn. Ví dụ, CSDL là khơng nhất quán tạm thời trong khi giao dịch chuyển khoản từ A sang B đang thực hiện, nếu một giao dịch khác thực hiện đồng thời đọc A và B tại thời điểm trung gian này và tính A+B, nĩ đã tham khảo một giá trị khơng nhất quán, sau đĩ nĩ thực hiện cập nhật A và B dựa trên các giá trị khơng nhất quán này, như vậy CSDL cĩ thể ở trạng thái khơng nhất quán ngay cả khi cả hai giao dịch hồn tất thành cơng. Một giải pháp cho vấn đề các giao dịch thực hiện đồng thời là thực hiện tuần tự các giao dịch, tuy nhiên giải pháp này làm giảm hiệu năng của hệ thống. Các giải pháp khác cho phép nhiều giao dịch thực hiện tương tranh đã được phát triển ta sẽ thảo luận về chúng sau này. Tính cơ lập của một giao dịch đảm bảo rằng sự thực hiện đồng thời các giao dịch dẫn đến một trạng thái hệ thống tương đương với một trạng thái cĩ thể nhận được bởi thực hiện các giao dịch này một tại một thời điểm theo một thứ tự nào đĩ. Đảm bảo tính cơ lập là trách nhiệm của một thành phần của hệ CSDL được gọi là thành phần quản trị tương tranh (concurrency-control component). Trạng thái giao dịch Nếu khơng cĩ sự cố, tất cả các giao dịch đều hồn tất thành cơng. Tuy nhiên, một giao dịch trong thực tế cĩ thể khơng thể hồn tất sự thực hiện của nĩ. Giao dịch như vậy được gọi là bị bỏ dở. Nếu ta đảm bảo được tính nguyên tử, một giao dịch bị bỏ dở khơng được phép làm ảnh hưởng tới trạng thái của CSDL. Như vậy, bất kỳ thay đổi nào từ giao dịch bị bỏ dở này đều phải bị hủy bỏ. Mỗi khi các thay đổi do giao dịch bị bỏ dở bị hủy bỏ, ta nĩi rằng giao dịch bị cuộn lại (rolled back). Khi một giao dịch hồn tất một cách thành cơng sự thực hiện của nĩ được gọi là được bàn giao (committed). Một giao dịch được bàn giao, các lệnh cập nhật sẽ biến đổi CSDL sang một trạng thái nhất quán mới và nĩ là bền vững ngay cả khi cĩ sự cố. Mỗi khi một giao dịch là được bàn giao, ta khơng thể hủy bỏ các hiệu quả của nĩ bằng cách bỏ dở nĩ. Cách duy nhất để hủy bỏ các hiệu quả của một giao dịch được bàn giao là thực hiện một giao dịch bù (compensating transaction), nhưng khơng phải luơn luơn cĩ thể tạo ra một giao dịch bù. Do vậy trách nhiệm viết và thực hiện một giao dịch bù thuộc về người sử dụng và khơng được quản lý bởi hệ CSDL. Một giao dịch phải ở trong một trong các trạng thái sau: • Hoạt động (Active): trạng thái khởi đầu; giao dịch giữ trong trạng thái này trong khi nĩ đang thực hiện. • Được bàn giao bộ phận (Partially Committed): sau khi lệnh cuối cùng được thực hiện.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 65 • Thất bại (Failed): sau khi phát hiện rằng sự thực hiện khơng thể tiếp tục được nữa. • Bỏ dở (Aborted): sau khi giao dịch đã bị cuộn lại và CSDL đã phục hồi lại trạng thái của nĩ trước khi khởi động giao dịch. • Được bàn giao (Committed): sau khi hồn thành thành cơng giao dịch. Ta nĩi một giao dịch đã được bàn giao (committed) chỉ nếu nĩ đã đi đến trạng thái Committed, tương tự, một giao dịch bị bỏ dở nếu nĩ đã đi đến trạng thái Aborted. Một giao dịch được gọi là kết thúc nếu nĩ hoặc là committed hoặc là Aborted. Một giao dịch khởi đầu bởi trạng thái Active. Khi nĩ kết thúc lệnh sau cùng của nĩ, nĩ chuyển sang trạng thái partially committed. Tại thời điểm này, giao dịch đã hồn thành sự thực hiện của nĩ, nhưng nĩ vẫn cĩ thể bị bỏ dở do đầu ra hiện tại vẫn cĩ thể trú tạm thời trong bộ nhớ chính và như thế một sự cố phần cứng vẫn cĩ thể ngăn cản sự hồn tất của giao dịch. Hệ CSDL khi đĩ đã kịp viết lên đĩa đầy đủ thơng tin giúp việc tái tạo các cập nhật đã được thực hiện trong quá trình thực hiện giao dịch, khi hệ thống tái khởi động sau sự cố. Sau khi các thơng tin sau cùng này được viết lên đĩa, giao dịch chuyển sang trạng thái committed. Biểu đồ trạng thái tương ứng với một giao dịch như sau: Partially Committed Committed Active Failed Aborted Với giả thiết sự cố hệ thống khơng gây ra sự mất dữ liệu trên đĩa, một giao dịch đi đến trạng thái Failed sau khi hệ thống xác định rằng giao dịch khơng thể tiến triển bình thường được nữa (do lỗi phần cứng hoặc phần mềm). Như vậy, giao dịch phải được cuộn lại rồi chuyển sang trạng thái bỏ dở. Tại điểm này, hệ thống cĩ hai lựa chọn: • Khởi động lại giao dịch, dùng lựa chọn này chỉ nếu giao dịch bị bỏ dở là do lỗi phần cứng hoặc phần mềm nào đĩ khơng liên quan đến logic bên trong của giao dịch. Giao dịch được khởi động lại được xem là một giao dịch mới. • Huỷ giao dịch thường được tiến hành hoặc do lỗi logic bên trong giao dịch, lỗi này cần được chỉnh sửa bởi viết lại chương trình ứng dụng hoặc do đầu vào xấu hoặc do dữ liệu mong muốn khơng tìm thấy trong CSDL.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 66 Ta phải thận trọng khi thực hiện thao tác viết ngồi khả quan sát (observable external Write - như viết ra terminal hay máy in). Mỗi khi một viết như vậy xảy ra, nĩ khơng thể bị xố do nĩ cĩ thể phải giao tiếp với bên ngồi hệ CSDL. Hầu hết các hệ thống cho phép các thao tác viết như thế xảy ra chỉ khi giao dịch đã di vào trạng thái committed. Một cách để thực thi một sơ đồ như vậy là cho hệ CSDL lưu trữ tạm thời bất kỳ giá trị nào kết hợp với các thao tác viết ngồi như vậy trong lưu trữ khơng hay thay đổi và thực hiện các thao tác viết hiện tại chỉ sau khi giao dịch đã đi vào trạng thái committed. Nếu hệ thống thất bại sau khi giao dịch đi vào trạng thái committed nhưng trước khi hồn tất các tháo tác viết ngồi, hệ CSDL sẽ làm các thao tác viết ngồi này (sử dụng dữ liệu trong lưu trữ khơng hay thay đổi) khi hệ thống khởi động lại. Trong một số ứng dụng, cĩ thể muốn cho phép giao dịch hoạt động trình bày dữ liệu cho người sử dụng, đặc biệt là các giao dịch kéo dài trong vài phút hay vài giờ. Ta khơng thể cho phép xuất ra dữ liệu khả quan sát như vậy trừ phi ta buộc phải làm tổn hại tính nguyên tử giao dịch. Hầu hết các hệ thống giao dịch hiện hành đảm bảo tính nguyên tử và do vậy cấm dạng trao đổi với người dùng này. Thực thi tính nguyên tử và tính bền vững Thành phần quản trị phục hồi của một hệ CSDL hỗ trợ tính nguyên tử và tính bền vững. Trước tiên ta xét một sơ đồ đơn giản (song cực kỳ thiếu hiệu quả). Sơ đồ này giả thiết rằng chỉ một giao dịch là hoạt động tại một thời điểm và được dựa trên việc tạo bản sao của CSDL được gọi là các bản sao khuất (shadow copies). Sơ đồ giả thiết rằng CSDL chỉ là một file trên đĩa. Một con trỏ được gọi là db_pointer được duy trì trên đĩa, nĩ trỏ tới bản sao hiện hành của CSDL. Trong sơ đồ CSDL khuất (shadow-database), một giao dịch muốn cập nhật CSDL, đầu tiên tạo ra một bản sao đầy đủ của CSDL. Tất cả các thao tác cập nhật được làm trên bản sao này, khơng đụng tới bản gốc. Nếu tại một thời điểm bất kỳ giao dịch bị bỏ dở, bản sao mới bị xố. Bản sao cũ của CSDL khơng bị ảnh hưởng. Nếu giao dịch hồn tất, nĩ được được bàn giao như sau: đầu tiên, hỏi hệ điều hành để đảm bảo rằng tất cả các trang của bản sao mới đã được viết lên đĩa (flush). Sau khi flush con trỏ db_pointer được cập nhật để trỏ đến bản sao mới; bản sao mới trở thành bản sao hiện hành của CSDL. Bản sao cũ bị xố đi. Giao dịch được gọi là đã được được bàn giao tại thời điểm sự cập nhật con trỏ db_pointer được ghi lên đĩa. Ta xét kỹ thuật này quản lý sự cố giao dịch và sự cố hệ thống ra sao? Trước tiên, ta xét sự cố giao dịch. Nếu giao dịch thất bại tại thời điểm bất kỳ trước khi con trỏ db_pointer được cập nhật, nội dung cũ của CSDL khơng bị ảnh hưởng. Ta cĩ thể bỏ dở giao dịch bởi xố bản sao mới. Mỗi khi giao dịch được được bàn giao, tất cả các cập nhật mà nĩ đã thực hiện là ở trong CSDL được trỏ bởi db_pointer. Như vậy, hoặc tất cả các cập nhật của giao dịch đã được phản ánh hoặc khơng kết quả nào được phản ánh,
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 67 bất chấp tới sự cố giao dịch. Bây giờ ta xét sự cố hệ thống. Giả sử sự cố hệ thống xảy ra tại thời điểm bất kỳ trước khi db_pointer đã được cập nhật được viết lên đĩa. Khi đĩ, khi hệ thống khởi động lại, nĩ sẽ đọc db_pointer và như vậy sẽ thấy nội dung gốc của CSDL – khơng kết quả nào của giao dịch được nhìn thấy trên CSDL. Bây giờ lại giả sử rằng sự cố hệ thống xảy ra sau khi db_pointer đã được cập nhật lên đĩa. Trước khi con trỏ được cập nhật, tất cả các trang được cập nhật của bản sao mới đã được viết lên đĩa. Từ giả thiết file trên đĩa khơng bị hư hại do sự cố hệ thống. Do vậy, khi hệ thống khởi động lại, nĩ sẽ đọc db_pointer và sẽ thấy nội dung của CSDL sau tất cả các cập nhật đã thực hiện bởi giao dịch. Sự thực thi này phụ thuộc vào việc viết lên db_pointer, việc viết này phải là nguyên tử, cĩ nghĩa là hoặc tất cả các byte của nĩ được viết hoặc khơng byte nào được viết. Nếu chỉ một số byte của con trỏ được cập nhật bởi việc viết nhưng các byte khác thì khơng thì con trỏ trở thành vơ nghĩa và cả bản cũ lẫn bản mới của CSDL cĩ thể tìm thấy khi hệ thống khởi động lại. May mắn thay, hệ thống đĩa cung cấp các cập nhật nguyên tử tồn bộ khối đĩa hoặc ít nhất là một sector đĩa. Như vậy hệ thống đĩa đảm bảo việc cập nhật con trỏ db_pointer là nguyên tử. Tính nguyên tử và tính bền vững của giao dịch được đảm bảo bởi việc thực thi bản sao bĩng của thành phần quản trị phục hồi. Sự thực thi này cực kỳ thiếu hiệu quả trong ngữ cảnh CSDL lớn, do sự thực hiện một giao dịch địi hỏi phải sao tồn bộ CSDL. Hơn nữa sự thực thi này khơng cho phép các giao dịch thực hiện đồng thời với các giao dịch khác. Phương pháp thực thi tính nguyên tử và tính lâu bền mạnh hơn và đỡ tốn kém hơn được trình bày trong chương hệ thống phục hồi. Các thực hiện tương tranh Hệ thống xử lý giao dịch thường cho phép nhiều giao dịch thực hiện đồng thời. Việc cho phép nhiều giao dịch cập nhật dữ liệu đồng thời gây ra những khĩ khăn trong việc bảo đảm sự nhất quán dữ liệu. Bảo đảm sự nhất quán dữ liệu mà khơng quan tâm tới sự thực hiện tương tranh các giao dịch sẽ cần thêm các cơng việc phụ. Một phương pháp tiến hành là cho các giao dịch thực hiện tuần tự: đảm bảo rằng một giao dịch khởi động chỉ sau khi giao dịch trước đã hồn tất. Tuy nhiên cĩ hai lý do hợp lý để thực hiện tương tranh là: • Một giao dịch gồm nhiều bước. Một vài bước liên quan tới hoạt động I/O; các bước khác liên quan đến hoạt động CPU. CPU và các đĩa trong một hệ thống cĩ thể hoạt động song song. Do vậy hoạt động I/O cĩ thể được tiến hành song song với xử lý tại CPU. Sự song song của hệ thống CPU và I/O cĩ thể được khai thác để chạy nhiều giao dịch song song. Trong khi một giao dịch tiến hành một hoạt động đọc/viết trên một đĩa, một giao dịch khác cĩ thể đang chạy trong CPU, một giao dịch thứ ba cĩ thể thực hiện đọc/viết trên một đĩa khác như vậy sẽ tăng lượng đầu vào hệ thống cĩ nghĩa là tăng số lượng giao dịch cĩ thể được thực hiện trong một lượng thời gian đã cho, cũng
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 68 cĩ nghĩa là hiệu suất sử dụng bộ xử lý và đĩa tăng lên. • Cĩ thể cĩ sự trộn lẫn các giao dịch đang chạy trong hệ thống, cái thì dài cái thì ngắn. Nếu thực hiện tuần tự, một quá trình ngắn cĩ thể phải chờ một quá trình dài đến trước hồn tất, mà điều đĩ dẫn đến một sự trì hỗn khơng lường trước được trong việc chạy một giao dịch. Nếu các giao dịch đang hoạt động trên các phần khác nhau của CSDL, sẽ tốt hơn nếu ta cho chúng chạy đồng thời, chia sẻ các chu kỳ CPU và truy xuất đĩa giữa chúng. Thực hiện tương tranh làm giảm sự trì hỗn khơng lường trước trong việc chạy các giao dịch, đồng thời làm giảm thời gian đáp ứng trung bình: Thời gian để một giao dịch được hồn tất sau khi đã được đệ trình. Động cơ để sử dụng thực hiện tương tranh trong CSDL cũng giống như động cơ để thực hiện đa chương trong hệ điều hành. Khi một vài giao dịch chạy đồng thời, tính nhất quán CSDL cĩ thể bị vi phạm cho dù mỗi giao dịch là đúng. Một giải pháp để giải quyết vấn đề này là sử dụng thời lịch (schedule). Hệ CSDL phải điều khiển sự trao đổi giữa các giao dịch tương tranh để ngăn ngừa chúng phá huỷ sự nhất quán của CSDL. Các cơ chế cho điều đĩ được gọi là sơ đồ điều khiển tương tranh (concurrency-control scheme). Xét hệ thống ngân hàng đơn giản, nĩ cĩ một số tài khoản và cĩ một tập hợp các giao dịch, chúng truy xuất, cập nhật các tài khoản này. Giả sử T1 và T2 là hai giao dịch chuyển khoản từ một tài khoản sang một tài khoản khác. Giao dịch T1 chuyển 50$ từ tài khoản A sang tài khoản B. Giao dịch T2 chuyển 10% số dư từ tài khoản A sang tài khoản B, và được xác định như sau: T : Read(A); T : Read(A); 1 2 A:=A-50; Temp:=A*0.1; Write(A); A:=A-temp; Read(B); Write(A); B:=B+50; Read(B); Write(B); B:=B+temp; Write(B); Giả sử giá trị hiện tại của A và B tương ứng là 1000$ và 2000$. Giả sử rằng hai giao dịch này được thực hiện theo trình tự: Trường hợp 1: thực hiện xong giao dịch T1 rồi đến giao dịch T2 Trường hợp 2: thực hiện xong giao dịch T2 rồi đến giao dịch T1
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 69 T1 T2 T1 T2 Read(A); Read(A); A:=A-50; Temp:=A*0.1; Write(A); A:=A-temp; Read(B); Write(A); B:=B+50; Read(B); Write(B); B:=B+temp; Write(B); Read(A); Read(A); Temp:=A*0.1; A:=A-50; A:=A-temp; Write(A); Write(A); Read(B); Read(B); B:=B+50; B:=B+temp; Write(B); Write(B); Thời lịch 1: Giá trị sau cùng của A là 855, Thời lịch 2: Giá trị sau cùng của A là 850, B B là 2145, tổng 2 tài khoản (A+B) là khơng là 2150, tổng 2 tài khoản (A+B) là khơng đổi đổi Thời lịch (schedule): là một dãy các thao tác (lệnh) của các giao tác được sắp xếp theo trình tự thời gian. Một thời lịch đối với một tập các giao dịch phải bao gồm tất cả các chỉ thị của các giao dich này và phải bảo tồn thứ tự các chỉ thị xuất hiện trong mỗi một giao dịch. Ví dụ, đối với giao dịch T1, chỉ thị Write(A) phải xuất hiện trước chỉ thị Read(B), trong bất kỳ thời lịch hợp lệ nào. Các thời lịch 1 và Thời lịch 2 là những thời lịch tuần tự. Thời lịch tuần tự gồm một dãy các chỉ thị từ các giao dịch, trong đĩ các chỉ thị thuộc về một giao dịch xuất hiện cùng nhau trong thời lịch. Như vậy, đối với một tập n giao dịch, cĩ n! thời lịch tuần tự hợp lệ khác nhau. Khi một số giao dịch được thực hiện đồng thời, thời lịch tương ứng khơng nhất thiết là tuần tự. Nếu hai giao dịch đang chạy đồng thời, hệ điều hành cĩ thể thực hiện một giao dịch trong một khoảng ngắn thời gian, sau đĩ chuyển đổi ngữ cảnh, thực hiện giao dịch thứ hai một khoảng thời gian sau đĩ lại chuyển sang thực hiện giao dịch thứ nhất một khoảng và cứ như vậy (hệ thống chia sẻ thời gian). Thời lịch 3 và Thời lịch 4 là các ví dụ.
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 70 T1 T2 T1 T2 Read(A); Read(A); A:=A-50; A:=A-50; Write(A); Read(A); Read(A); Temp:=A*0.1; Temp:=A*0.1; A:=A-temp; A:=A-temp; Write(A); Write(A); Read(B); Read(B); Write(A); B:=B+50; Read(B); Write(B); B:=B+50; Write(B); Read(B); B:=B+temp; B:=B+temp; Write(B); Write(B); Thời lịch 3 Thời lịch 4 Tuy nhiên, khơng phải tất cả các thực hiện tương tranh đều cho ra một trạng thái đúng. Ví dụ xét thời lịch 4: Sau khi thực hiện giao dịch này, ta đạt tới trạng thái trong đĩ giá trị cuối của A và B tương ứng là 950$ và 2100$. Trạng thái này là một trạng thái khơng nhất quán (A+B trước khi thực hiện giao dịch là 3000$ nhưng sau khi giao dịch là 3050$). Như vậy, nếu giao phĩ việc điều khiển thực hiện tương tranh cho hệ điều hành, sẽ cĩ thể dẫn tới các trạng thái khơng nhất quán. Nhiệm vụ của hệ CSDL là đảm bảo rằng một thời lịch được phép thực hiện tương tranh sẽ đưa CSDL sang một trạng thái nhất quán. Thành phần của hệ CSDL thực hiện nhiệm vụ này được gọi là thành phần điều khiển tương tranh (concurrency-control component). Ta cĩ thể đảm bảo sự nhất quán của CSDL với thực hiện tương tranh bằng cách nắm chắc rằng một thời lịch được thực hiện cĩ cùng hiệu quả như một thời lịch tuần tự. Tính khả tuần tự (Serializability) Hệ CSDL phải điều khiển sự thực hiện tương tranh các giao dịch để đảm bảo rằng trạng thái CSDL giữ nguyên ở trạng thái nhất quán. Trước khi ta kiểm tra hệ CSDL cĩ thể thực hiện nhiệm vụ này như thế nào, đầu tiên ta phải hiểu các thời lịch nào sẽ đảm bảo tính nhất quán và các thời lịch nào khơng. Vì các giao dịch là các chương trình, nên thật khĩ xác định các hoạt động chính xác được thực hiện bởi một giao dịch là hoạt động gì và
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 71 những hoạt động nào của các giao dịch tác động lẫn nhau. Vì lý do này, ta sẽ khơng giải thích kiểu hoạt động mà một giao dịch cĩ thể thực hiện trên một mục dữ liệu. Thay vào đĩ, ta chỉ xét hai hoạt động: Read và Write. Ta cũng giả thiết rằng giữa một chỉ thị Read(Q) và một chỉ thị Write(Q) trên một mục dữ liệu Q, một giao dịch cĩ thể thực hiện một dãy tuỳ ý các hoạt động trên bản sao của Q được lưu trú trong buffer cục bộ của giao dịch. Vì vậy ta sẽ chỉ nêu các chỉ thị Read và Write trong thời lịch, nếu biểu diễn với quy ước như vậy của thời lịch 3 sẽ là: T1 T2 Read(A); Write(A); Read(A); Write(A); Read(B); Write(B); Read(B); Write(B); Tuần tự xung đột (Conflict Serializability) Xét thời lịch S trong đĩ cĩ hai chỉ thị liên tiếp Ii và Ij của các giao dịch Ti , Tj tương ứng (i ≠ j). Nếu Ii và Ij tham khảo đến các mục dữ liệu khác nhau, ta cĩ thể đổi chỗ Ii và Ij mà khơng làm ảnh hưởng đến kết quả của bất kỳ chỉ thị nào trong thời lịch. Tuy nhiên, nếu Ii và Ij tham khảo cùng một mục dữ liệu Q, khi đĩ thứ tự của hai bước này cĩ thể rất quan trọng. Do ta đang thực hiện chỉ các chỉ thị Read và Write, nên ta cĩ bốn trường hợp cần phải xét sau: 1. Ii = Read(Q); Ij = Read(Q): Thứ tự của Ii và Ij khơng gây ra vấn đề nào, do Ti và Tj đọc cùng một giá trị Q bất kể đến thứ tự giữa Ii và Ij. 2. Ii = Read(Q); Ij = Write(Q): thứ tự thực hiện của Ii và Ij là quan trọng. 3. Ii = Write(Q); Ij = Read(Q): thứ tự thực hiện của Ii và Ij là quan trọng. 4. Ii = Write(Q); Ij = Write(Q): Cả hai chỉ thị là hoạt động Write, thứ tự của hai chỉ thị này khơng ảnh hưởng đến cả hai giao dịch T và T . Tuy nhiên, giá trị nhận i j được bởi chỉ thị Read kế trong S sẽ bị ảnh hưởng do kết quả phụ thuộc vào chỉ thị Write được thực hiện sau cùng trong hai chỉ thị Write này. Nếu khơng cịn chỉ thị Write nào sau Ii và Ij trong S, thứ tự của thứ tự thực hiện của Ii và Ij sẽ ảnh hưởng trực tiếp đến giá trị cuối của Q trong trạng thái CSDL kết quả (của thời lịch S).
Bài giảng tĩm tắt Hệ quản trị cơ sở dữ liệu 72 Như vậy chỉ trong trường hợp cả I và I là các chỉ thị Read, thứ tự thực hiện của hai i j chỉ thị này (trong S) là khơng gây ra vấn đề. Ta nĩi Ii và Ij xung đột nếu các hoạt động này nằm trong các giao dịch khác nhau, tiến hành trên cùng một mục dữ liệu và cĩ ít nhất một hoạt động là Write. Ví dụ, trong thời lịch schedule - 3: Chỉ thị Write(A) trong T1 xung đột với Read(A) trong T2. Tuy nhiên, chỉ thị Write(A) trong T2 khơng xung đột với chỉ thị Read(B) trong T1 do các chỉ thị này truy xuất các mục dữ liệu khác nhau. Ii và Ij là hai chỉ thị liên tiếp trong thời lịch S. Nếu Ii và Ij là các chỉ thị của các giao dịch khác nhau và khơng xung đột, khi đĩ ta cĩ thể đổi thứ tự của chúng mà khơng làm ảnh hưởng gì đến kết quả xử lý và như vậy ta nhận được một thời lịch mới S’ tương đương với S. Chẳng hạn, do chỉ thị Write(A) của T2 khơng xung đột với chỉ thị Read(B) của T1, ta cĩ thể đổi chỗ các chỉ thị này để được một thời lịch tương đương – thời lịch 5 dưới đây T1 T2 Read(A); Write(A); Read(A); Read(B); Write(A); Write(B); Read(B); Write(B); Thời lịch 5 Ta tiếp tục đổi chỗ các chỉ thị khơng xung đột như sau: • Đổi chỗ chỉ thị Read(B) của T1 với chỉ thị Read(A) của T2 • Đổi chỗ chỉ thị Write(B) của T1 với chỉ thị Write(A) của T2 • Đổi chỗ chỉ thị Write(B) của T1 với chỉ thị Read(A) của T2 Kết quả cuối cùng của các bước đổi chỗ này là một thời lịch mới (thời lịch 6 –thời lịch tuần tự) tương đương với thời lịch ban đầu (thời lịch 3):