Giáo trình Cơ sở dữ liệu nâng cao

tored procedure lần đầu tiên thì SQL Server sẽ chạy nó và 
lưu trữ vào bộ nhớ đệm, gọi là plan cache, những lần tiếp theo SQL Server sẽ sử dụng 
lại plan cache nên sẽ cho tốc độ xử lý tối ưu 
Stored procedure rất tiện lợi cho người quản trị database (DBA), nó giúp DBA 
tạo ra những nhóm câu lệnh và gửi đến một bô phận khác mà họ sẽ không cần quan 
tâm đến nội dung bên trong stored procedure có gì, họ chỉ quan tâm đến tham số đầu 
vào và đầu ra 
Ngoài ra, Store procedure còn hỗ trợ tính trong suốt phân tán. Điều kiện để 
trong suốt dữ liệu trong phân tán thì tên server phải giống nhau. 
- Cú pháp: 
CREATE {PROCEDURE | PROC} [schema_name] procedure_name 
 [ @parameter [type_schema_name.] datatype 
Chương 4: Hệ cơ sở dữ liệu phân tán 
KHOA CÔNG NGHỆ THÔNG TIN 67 
 [VARYING] [ = default] [ OUT | OUTPUT | READONLY] 
 , @parameter [type_schema_name.] datatype 
 [VARYING] [ = default] [ OUT | OUTPUT | READONLY] 
[WITH {ENCRYPTION | RECOMPILE | EXECUTE AS Clause}] 
[ FOR REPLICATION] 
AS 
BEGIN 
 [declaration_section] 
executable_section 
END; 
- Trong đó: 
 + schema_name: Tên schema (lược đồ) sở hữu procedure. 
+ procedure_name: Tên gán cho procedure 
+ @parameter: Một hay nhiều tham số được truyền vào hàm. 
+ type_schema_name: Kiểu dữ liệu của schema (nếu có). 
+ Datatype: Kiểu dữ liệu cho @parameter. 
+ Default: Giá trị mặc định gán cho @parameter. 
+ OUT/OUTPUT: @parameter là một tham số đầu ra 
+ READONLY: @parameter không thể bị procedure ghi đè lên. 
+ ENCRYPTION: Mã nguồn (source) của procedure sẽ không được lưu 
trữ dưới dạng text trong hệ thống. 
+ RECOMPILE: Truy vấn sẽ không được lưu ở bộ nhớ đệm (cache) cho 
thủ tục này. 
+ EXECUTE AS clause: Xác định ngữ cảnh bảo mật để thực thi thủ tục. 
+FOR REPLICATION: Procedure đã lưu sẽ chỉ được thực thi trong quá 
trình replication (nhân bản). 
- Cách tạo Store procedure trong SQL: 
 Vào server gốc/ chọn vào database cần thực hiện / programmability/ 
store procedures /new store procedurescrecrecre 
Chương 4: Hệ cơ sở dữ liệu phân tán 
KHOA CÔNG NGHỆ THÔNG TIN 68 
Hình 4.6.2 Cách tạo 1 Store Procedure 
Ví dụ: Viết 1 Store Procedure tên DS_LOP sao cho khi chạy Store Procedure này 
trên 1 Server bất kỳ, nó đều hiển danh sách lớp của trường. 
CREATE PROCEDURE SP_DSLOP 
AS 
 SELECT MALOP, TENLOP FROM DBO.LOP 
UNION 
 SELECT MALOP, TENLOP FROM LINK1.QLDSV.DBO.LOP 
Chương 4: Hệ cơ sở dữ liệu phân tán 
KHOA CÔNG NGHỆ THÔNG TIN 69 
CÂU HỎI LÝ THUYẾT 
Câu hỏi 1: Định nghĩa phân mảnh ngang nguyên thủy, các mảnh nguyên thủy có cần 
thêm trường khóa không? 
Câu hỏi 2: Cho lược đồ phổ quát : 
R (a, b): b có giá trị nguyên trong đoạn [1,5] 
S (a, c): c có giá trị nguyên trong đoạn [1,3] 
Ri = b=i (R) 
Sij = c=j (S) Ri 
Cho biết tổng số phân mảnh? 
Câu hỏi 3: Trình bày quá trình chuyển câu truy vấn toàn cục thành câu truy vấn dựa 
trên các phân mảnh? 
Câu hỏi 4: Liệt kê tên nhân viên, chuyên môn và vai trò của họ khi tham gia Công 
trình có mã là ‘P1218’. Biết rằng Công trình này do một công ty ở vùng Biển đảo phụ 
trách. 
a) Viết câu lệnh SQL dưới dạng lược đồ toàn cục. 
b) Chuyển câu SQL ở câu a thành cây truy vân dưới dạng đại số quan hệ. 
c) Chuyển cây truy vấn ở câu b dưới dạng cây truy vấn phân mảnh tối giản. 
d) Hãy thiết kế phân mảnh dọc hỗn hợp tương ứng với các phân mảnh ởcâu c 
BÀI TẬP THỰC HÀNH 
Bài tập 1: 
Giả sử đã có cơ sở dữ liệu QL_VATTU trong Serer mặc định. Giả sử công ty 
có 2 chi nhánh: chi nhánh 1 (CN1) và chi nhánh 2 (CN2) . 
a) Yêu cầu 1: Phân tán cơ sở dữ liệu QL_VATTU ra làm 2 mảnh với điều kiện 
sau: 
QL_VATTU được đặt trên Server 1: chứa thông tin của các vật tư thuộcchi 
nhánh 1 
QL_VATTU được đặt trên Server 2: chứa thông tin của các vật tư thuộcchi 
nhánh 2 
b) Yêu cầu 2: Viết 1 Store Procedure để in ra các phiếu xuất do nhân viên có 
mã số @MANV đã lập trong ngày @ngay 
c) Yêu cầu 3: Viết 1 Store Procedure để xóa các phiếu nhập do nhân viên có mã 
số @MANV đã lập trong ngày @ngay 
Chương 4: Hệ cơ sở dữ liệu phân tán 
KHOA CÔNG NGHỆ THÔNG TIN 70 
d) Yêu cầu 4: Hãy cho biết cách tạo Link Server như thế nào để đảm bảo tính 
“trong suốt” khi gọi 1 View trong cơ sở dữ liệu phân tán. 
e) Yêu cầu 5: Sử dụng Link server in ra danh sách các vật tư của chi nhánh 1 
đang đặt ở Server 2 
Bài tập 2: 
Giả sử đã có cơ sở dữ liệu QL_SINHVIEN trong Serer mặc định. Giả sử trường 
có 2 khoa chính: công nghệ thông tin (CNTT) và viễn thông (VT) 
a) Yêu cầu 1: Phân tán cơ sở dữ liệu QL_SINHVIEN ra làm 2 mảnh với điều 
kiện sau: 
QL_SINHVIEN được đặt trên Server 1: chứa thông tin của các sinh viên thuộc 
khoa công nghệ thông tin 
QL_SINHVIEN được đặt trên Server 2: chứa thông tin của các sinh viên thuộc 
khoa viễn thông 
 Biết rằng 1 sinh viên chỉ có thể thuộc 1 khoa 
b) Yêu cầu 2: Viết 1 Store Procedure tên DS_LOP sao cho khi chạy Store 
Procedure này trên 1 Server bất kỳ, nó đều hiển danh sách lớp của trường. 
c) Yêu cầu 3: Hãy in ra tên lớp và tên khoa, ho, ten của 1 sinh viên có mã số là 
@X (Store Procedure có tên sp_TimSV) 
d) Yêu cầu 4: Viết 1 Store Procedure cho phép sửa họ của 1 sinh viên là @X, 
biết rằng ta chưa rõ sinh viên này thuộc khoa nào 
e) Yêu cầu 5: Viết 1 Store Procedure để xóa 1 sinh viên có mã số @X 
f) Yêu cầu 6: Hãy cho biết cách tạo Link Server như thế nào để đảm bảo tính 
“trong suốt” khi gọi 1 Stored Procedure trong cơ sở dữ liệu phân tán. 
g) Yêu cầu7: Sử dụng Link server in ra danh sách lớp của khoa viễn thông đang 
đặt ở Server 1 
 KHOA CÔNG NGHỆ THÔNG TIN 71 
TÀI LIỆU THAM KHẢO 
1. Đỗ Thị Minh Phụng, Giáo trình hệ quản trị CSDL SQL Server, Đại học Quốc gia 
TP.HCM, 2005. 
2. Lê Văn Phùng, Cơ Sở Dữ Liệu Quan Hệ Và Công Nghệ Phân Tích Thiết Kế, Thông 
Tin Truyền Thông, 2010 
3. Nguyễn Kim Anh, Nguyên Lý Của Các Hệ Cơ Sở Dữ Liệu, Đại Học Quốc Gia, 2009 
4. Phạm Thế Quế, Giáo trình cơ sở dữ liệu phân tán, Thông Tin Truyền Thông, 2010 
KHOA CÔNG NGHỆ THÔNG TIN 72 
MỤC LỤC HÌNH 
Hình 1.1 Mô hình phân cấp quản lý nhân sự của một công ty ............................................ 5 
Hình 1.2 Mô hình mạng quản lý nhân sự của một công ty ................................................. 6 
Hình 1.3 Mô hình quan hệ mô tả dữ liệu bảng .................................................................... 6 
Hình 1.4 Mô hình đối tượng quản lý nhân sự của một công ............................................... 7 
Hình 2.1 Tối ưu hóa câu truy vấn bằng cây đại số quan hệ ............................................ 144 
Hình 2.2 Tối ưu hóa câu truy vấn theo thuật toán 2 ngôi .............................................. 1515 
Hình 3.1 Phân quyền trên cơ sở dữ liệu ............................................................................ 18 
Hình 3.2 Tạo tài khoản SQL Server .................................................................................. 18 
Hình 3.3 Khai báo tạo tài khoản SQL server .................................................................... 19 
Hình 3.4 Tạo người sử dụng .............................................................................................. 20 
Hình 3.5 Khai báo tạo người sử dụng................................................................................ 20 
Hình 3.6 Cấp quyền cho người dùng ............................................................................... 211 
Hình 3.7 Cấp quyền cho người dùng ............................................................................... 211 
Hình 3.8 Thiết lập quyền ................................................................................................. 222 
Hình 4.1 Kiến trúc 1 Client/Server .................................................................................... 27 
Hình 4.2 Kiến trúc 2 Client/Server .................................................................................... 27 
Hình 4.3 Kiến trúc 3 Client/Server .................................................................................... 27 
Hình 4.4 Tạo thư mục ReplData ........................................................................................ 36 
Hình 4.5 Chia sẻ ReplData ................................................................................................ 36 
Hình 4.6 Add người dùng và chọn quyền ......................................................................... 37 
Hình 4.7 Share replData thành công.................................................................................. 37 
Hình 4.8 Tạo cấu hình distribution .................................................................................... 38 
Hình 4.9 Copy đường dẫn napshot .................................................................................... 38 
Hình 4.10 Tạo database tên distribution ............................................................................ 39 
Hình 4.11 Thiết lập cấu hình tiếp distribution ................................................................... 39 
Hình 4.12 Cấu hình distribution thành công ..................................................................... 40 
Hình 4.13 Cấu hình distribution thành công ..................................................................... 40 
Hình 4.14 Cấu hình distribution thành công ..................................................................... 41 
Hình 4.15 Cấu hình distribution thành công ..................................................................... 42 
Hình 4.16 Chọn database để publication ........................................................................... 42 
KHOA CÔNG NGHỆ THÔNG TIN 73 
Hình 4.17 Chọn loại thiết kế publication........................................................................... 43 
Hình 4.18 Chọn phiên bản thiết kế publication ................................................................. 43 
Hình 4.19 Chọn phân tán dữ liệu table .............................................................................. 44 
Hình 4.20 Tạo lược đồ phân tán các table ......................................................................... 44 
Hình 4.21 Phân tán dữ liệu table CHI NHANH ................................................................ 45 
Hình 4.22 Phân tán table phân mảnh ngang dẫn xuất ....................................................... 45 
Hình 4.23 Phân tán dữ liệu table NHANVIEN ................................................................. 46 
Hình 4.24 Kết quả phân tán dữ liệu table NHANVIEN .................................................... 46 
Hình 4.25 Phân tán dữ liệu table KHO .............................................................................. 47 
Hình 4.26 Phân tán dữ liệu table PHATSINH .................................................................. 47 
Hình 4.27 Phân tán dữ liệu table CT_PHATSINH ........................................................... 48 
Hình 4.28 Lược đồ phân mảnh cơ sờ dữ liệu QUANLYVATTU ..................................... 48 
Hình 4.29 Tạo napshot agent lưu trữ ................................................................................. 49 
Hình 4.30 Khai báo tài khoản server gốc ...................................................................... 4499 
Hình 4.31 Tiếp tục tạo publication .................................................................................... 50 
Hình 4.32 Đặt tên cho publication ..................................................................................... 50 
Hình 4.33 Kiểm tra lại publication đã tạo ......................................................................... 51 
Hình 4.34 Xem lại thiết kế publication ............................................................................. 51 
Hình 4.35 Trạng thái hoạt động snapshot agent ................................................................ 52 
Hình 4.36 Tạo 1 subscription ............................................................................................ 52 
Hình 4.37 Pubication tạo subscription............................................................................... 53 
Hình 4.38 Thiết lập cấu hình tạo 1 subscription ................................................................ 53 
Hình 4.39 Chỉ định server chứa publication .................................................................. 5544 
Hình 4.40 Server CNKLZCW4RWBTZ\NGA1 chứa publication ................................... 54 
Hình 4.41 Chỉ định database chứa pulication sau khi phân tán .................................... 5555 
Hình 4.42 Database QUANLYVATTU sau khi phân tán ................................................. 55 
Hình 4.43 Dữ liệu server gốc được đẩy phân tán sang server con .................................... 56 
Hình 4.44 Cấu hình từ server gốc sang server con ............................................................ 56 
Hình 4.45 Khai báo tài khoản server gốc sang server con ................................................ 57 
Hình 4.46 Đồng bộ hóa dữ liệu server gốc sang server con .............................................. 57 
Hình 4.47 Đồng bộ hóa dữ liệu Immediately .................................................................... 58 
Hình 4.48 Đồng bộ hóa dữ liệu server .............................................................................. 58 
KHOA CÔNG NGHỆ THÔNG TIN 74 
Hình 4.49 Cấu hình tạo 1 subscription .............................................................................. 59 
Hình 4.50 Kết quả 1 subscription ...................................................................................... 59 
Hình 4.51 Kiểm tra database server con ............................................................................ 60 
Hình 4.52 Tạo log in HTKN .............................................................................................. 61 
Hình 4.53 Tạo log in HTKN .............................................................................................. 62 
Hình 4.54 Tạo log in HTKN .............................................................................................. 62 
Hình 4.55 Thiết lập quyền trong status ............................................................................. 63 
Hình 4.56 Thiết lập quyền trong status ............................................................................ 63 
Hình 4.57 Tạo 1 Link server .............................................................................................. 64 
Hình 4.58 Thiết lập khai báo HTKN trong security .......................................................... 64 
Hình 4.59 Thiết lập khai báo HTKN trong security .......................................................... 65 
Hình 4.60 Thiết lập khai báo HTKN trong security .......................................................... 65 
Hình 4.61 LINK truy xuất dữ liệu .................................................................................... 66 
Hình 4.62 Cách tạo 1 Store Procedure ............................................................................. 68