Giáo trình mô đun Hệ quản trị cơ sở dữ liệu - Nghề: Lập trình máy tính

g bảng. 
- Để mở một biến con trỏ ta sử dụng câu lệnh: 
 OPEN tên_con_trỏ 
- Để sử dụng biến con trỏ duyệt qua các dòng dữ liệu của truy vấn, ta sử dụng câu 
lệnh FETCH.
FETCH [[NEXT|PRIOR|FIST|LAST] FROM] tên_con_trỏ 
[INTO danh_sách_biến ] 
Giá trị của biến trạng thái @@FETCH_STATUS bằng không nếu chưa duyệt hết các dòng trong kết quả truy vấn. 
Các biến trong danh sách biến được sử dụng để chứa các giá trị của các trường ứng với dòng dữ liệu mà con trỏ trỏ đến. Số lượng các biến phải bằng với số lượng các cột của kết quả truy vấn trong câu lệnh DECLARE CURSOR. 
- Đóng con trỏ: Close tên_con_trỏ
- Giải phóng bộ nhớ: DEALLOCATE tên_con_trỏ
Trình tự thực hiện
- Mở cửa sổ soạn thảo lệnh
- Viết lệnh tạo trigger
CREATE TRIGGER trg_nhatkybanhang_update_soluong 
ON nhatkybanhang 
FOR UPDATE 
AS 
IF UPDATE(soluong) 
BEGIN 
DECLARE @mahang NVARCHAR(10) 
 DECLARE @soluong INT 
-- Khai báo biến con trỏ
 DECLARE contro CURSOR FOR 
 SELECT inserted.mahang, 
 inserted.soluong-deleted.soluong AS soluong 
 FROM inserted INNER JOIN deleted 
 ON inserted.stt=deleted.stt 
-- Mở con trỏ
 OPEN contro
FETCH NEXT FROM contro INTO @mahang,@soluong 
-- Duyệt từng bản ghi
 WHILE @@FETCH_STATUS=0 
 BEGIN 
 UPDATE mathang SET soluong=soluong-@soluong 
 	WHERE mahang=@mahang 
 FETCH NEXT FROM contro INTO @mahang,@soluong 
 END 
-- Đóng con trỏ
 CLOSE contro 
-- Giải phóng bộ nhớ của con trỏ
 DEALLOCATE contro 
END 
END
- Nhấn F5
Bài 6. Hàm
Mã bài: MĐ11_B06
Mục tiêu: 
Khai báo và sử dụng được hàm với các kiểu dữ liệu cơ sở.
Khai báo và sử dụng được hàm có kiểu dữ liệu trả về là bảng.
Thực hiện đựợc các biện pháp an toàn cho máy tính 
1. Định nghĩa và sử dụng hàm
Mục tiêu:
- Trình bày được cú pháp định nghĩa hàm, và cúp pháp gọi hàm.;
- Tạo được và sử dụng được hàm theo yêu cầu;
- Thực hiện đựợc các biện pháp an toàn cho máy tính
Hàm là đối tượng cơ sở dữ liệu tương tự như thủ tục. Điểm khác biệt giữa hàm 
và thủ tục là hàm trả về một giá trị thông qua tên hàm còn thủ tục thì không. Điều này cho phép ta sử dụng hàm như là một thành phần của một biêu thức.
Hàm được định nghĩa thông qua câu lệnh CREATE FUNCTION với cú pháp như sau: 
CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) 
RETURNS (kiểu_trả_về_của_hàm) 
AS 
BEGIN 
 các_câu_lệnh_của_hàm 
END
Trình tự thực hiện
Hàm tính ngày trong tuần (thứ trong tuần) của một giá trị kiểu ngày
- Mở cửa sổ soạn thảo lệnh
- Viết lệnh tạo hàm
CREATE FUNCTION thu(@ngay DATETIME) 
RETURNS NVARCHAR(10) 
AS 
 	BEGIN
 DECLARE @st NVARCHAR(10) 
 	 SELECT @st=CASE DATEPART(DW,@ngay) 
 WHEN 1 THEN 'Chu nhật' 
 WHEN 2 THEN 'Thứ hai' 
 WHEN 3 THEN 'Thứ ba' 
 WHEN 4 THEN 'Thứ tư' 
 WHEN 5 THEN 'Thứ năm' 
 WHEN 6 THEN 'Thứ sáu' 
 	 ELSE 'Thứ bảy' 
 	 END 
 	 RETURN (@st) /* Trị trả về của hàm */ 
END
- Nhấn F5
- Sử dụng hàm
SELECT masv,hodem,ten,dbo.thu(ngaysinh),ngaysinh 
FROM sinhvien 
WHERE malop=’C24102’
- Chọn đoạn lệnh sử dụng hàm/ Nhấn F5
2. Tạo hàm với giá trị trả về là “dữ liệu kiểu bảng”
Mục tiêu:
- Trình bày được cú pháp tạo hàm trả về dữ liệu kiểu bảng;
- Tạo được hàm theo yêu cầu;
- Thực hiện đựợc các biện pháp an toàn cho máy tính.
Cú pháp lệnh tạo hàm trả về dữ liệu kiểu bảng: 
CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) 
RETURNS TABLE 
AS 
 RETURN (câu_lệnh_select) 
Cú pháp của hàm nội tuyến phải tuân theo các qui tắc sau: 
• Kiểu trả về của hàm phải được chỉ định bởi mệnh đề RETURNS TABLE. 
• Trong phần thân của hàm chỉ có duy nhất một câu lệnh RETURN xác định 
giá trị trả về của hàm thông qua duy nhất một câu lệnh SELECT. Ngoài ra, 
không sử dụng bất kỳ câu lệnh nào khác trong phần thân của hàm
b. Trình tự thực hiện
- Mở cửa sổ soạn thảo lệnh
- Viết lệnh tạo hàm
CREATE FUNCTION func_XemSV(@khoa SMALLINT) 
RETURNS TABLE 
AS
RETURN(SELECT masv,hodem,ten,ngaysinh 
 FROM sinhvien INNER JOIN lop 
 ON sinhvien.malop=lop.malop 
 WHERE khoa=@khoa)
Nhấn F5
Sử dụng hàm
SELECT * FROM dbo.func_XemSV(25)
Bài 7. Bảo trì và phát triển mô hình Client/Server
Mã bài: MĐ11_B07
Mục tiêu : 
- Đọc được hồ sơ thiết kế cơ sở dữ liệu
- Thực hiện được các thao tác tạo người sử dụng, phân quyền cho người sử dụng
- Chuyển đổi được cơ sở dữ liệu đơn giản từ hệ quản trị cơ sở dữ liệu MS SQL - sang MS Access hoặc sang các hệ quản trị cơ sở dữ liệu khác và ngược lại.
- Sao lưu và phục hồi được cơ sở dữ liệu
- Thực hiện các thao tác an toàn với máy tính
1. Bảo mật cơ sở dữ liệu 
Mục tiêu : 
- Đọc được hồ sơ thiết kế cơ sở dữ liệu
- Thực hiện được các thao tác tạo người sử dụng, phân quyền cho người sử dụng
- Thực hiện các thao tác an toàn với máy tính
Bảo mật là một trong những yếu tố đóng vai trò quan trọng đối với sự sống còn của CSDL. 
- Hầu hết các hệ quản trị CSDL thương mại hiện nay đều cung cấp khả năng bảo mật CSDL với những chức năng như: 
• Cấp phát quyền truy cập CSDL cho người dùng và các nhóm người dùng, phát hiện và ngăn chặn những thao tác trái phép của người sử dụng trên CSDL. 
• Cấp phát quyền sử dụng các câu lệnh, các đối tượng CSDL đối với người dùng. 
• Thu hồi (huỷ bỏ) quyền của người dùng. 
- Bảo mật dữ liệu trong SQL được thực hiện dựa trên ba khái niệm chính sau đây: 
• Người dùng CSDL (Database user): Là đối tượng sử dụng CSDL, thực thi các thao tác trên CSDL như tạo bảng, truy xuất dữ liệu,... Mỗi một người dùng trong CSDL được xác định thông qua tên người dùng (User ID). Một tập nhiều người dùng có thể được tổ chức trong một nhóm và được gọi là nhóm người dùng (User Group). Chính sách bảo mật CSDL có thể được áp dụng cho mỗi người dùng hoặc cho các nhóm người dùng. 
• Các đối tượng CSDL (Database objects): Tập hợp các đối tượng, các cấu trúc lưu trữ được sử dụng trong CSDL như bảng, khung nhìn, thủ tục, hàm được gọi là các đối tượng CSDL. Đây là những đối tượng cần được bảo vệ trong chính sách bảo mật của CSDL. 
• Đặc quyền (Privileges): Là tập những thao tác được cấp phát cho người dùng trên các đối tượng CSDL. Chằng hạn một người dùng có thể truy xuất dữ liệu trên một bảng bằng câu lệnh SELECT nhưng có thể không thể thực hiện các câu lệnh INSERT, UPDATE hay DELETE trên bảng đó. 
- SQL cung cấp hai câu lệnh cho phép chúng ta thiết lập các chính sách bảo mật trong 
CSDL: 
• Lệnh GRANT: Sử dụng để cấp phát quyền cho người sử dụng trên các đối tượng CSDL 
hoặc quyền sử dụng các câu lệnh SQL trong CSDL. 
• Lệnh REVOKE: Được sử dụng để thu hồi quyền đối với người sử dụng.
1.1. Cấp pát quyền
1.1.1. Cấp phát quyền cho người dùng trên các đối tượng CSDL
a. Lý thuyết liên quan
Chỉ có người sở hữu CSDL hoặc người sở hữu đối tượng CSDL mới có thể cấp phát quyền cho người dùng trên các đối tượng CSDL
Cú pháp lệnh:
GRANT ALL [PRIVILEGES]| các_quyền_cấp_phát 
[(danh_sách_cột)] ON tên_bảng | tên_khung_nhìn 
|ON tên_bảng | tên_khung_nhìn [(danh_sách_cột)] 
|ON tên_thủ_tục 
|ON tên_hàm 
TO danh_sách_người_dùng | nhóm_người_dùng 
[WITH GRANT OPTION ] 
Trong đó:
+ ALL[PRIVILEGES]:
Cấp phát tất cả các quyền cho người dùng trên đối tượng CSDL được chỉ định. Các quyền có thể cấp phát cho người dùng bao gồm: 
• Đối với bảng, khung nhìn, và hàm trả về dữ liệu kiểu bảng: 
 SELECT, INSERT, DELETE, UPDATE và REFERENCES. 
• Đối với cột trong bảng, khung nhìn: SELECT và UPDATE 
• Đối với thủ tục lưu trữ và hàm vô hướng: EXECUTE 
Quyền REFERENCES được sử dụng nhằm cho phép tạo khóa ngoài tham chiếu đến bảng cấp phát.
+ Các_quyền_cấp_phát:
Danh sách các quyền cần cấp phát cho người dùng trên đối tượng CSDL được chỉ định. Các quyền phân cách nhau bởi dấu phẩy.
+ tên_bảng |tên_khung_nhìn:
Tên của bảng hoặc khung nhìn cần cấp phát quyền 
+ danh_sách_cột:
 Danh sách các cột của bảng/khung nhìn cần cấp phát quyền. 
+ tên_thủ_tục:
 Tên của thủ tục được cấp phát cho người dùng. 
+ tên_hàm: 
Tên hàm (do người dùng định nghĩa) được cấp phát quyền. 
+ danh_sách_người_dùng:
 	Danh sách tên người dùng nhận quyền được cấp phát. Tên của 
các người dùng được phân cách nhau bởi dấu phẩy 
+ WITH GRANT OPTION :
Cho phép người dùng chuyển tiếp quyền cho người dùng khác
b. Trình tự thực hiện
Cấp phát cho người dùng có tên thuchanh quyền thực thi các câu lệnh SELECT, 
INSERT và UPDATE trên bảng LOP
- Mở cửa sổ soạn thảo lệnh
- Viết lệnh
GRANT SELECT,INSERT,UPDATE ON lop 
TO thuchanh
- Thực thi lệnh: Chọn đoạn lệnh/ Nhấn F5
1.1.2. Cấp phát quyền thực thi các câu lệnh
Ngoài chức năng cấp phát quyền cho người sử dụng trên các đối tượng cơ sở dữ 
liệu, câu lệnh GRANT còn có thể sử dụng để cấp phát cho người sử dụng một số quyền trên hệ quản trị cơ sở dữ liệu hoặc cơ sở dữ liệu. Những quyền có thể cấp phát trong trường hợp này bao gồm: 
• Tạo cơ sở dữ liệu: CREATE DATEBASE. 
• Tạo bảng: CREATE RULE 
• Tạo khung nhìn: CREATE VIEW
• Tạo thủ tục lưu trữ: CREATE PROCEDURE 
• Tạo hàm: CREATE FUNCTION 
• Sao lưu cơ sở dữ liệu: BACKUP DATABASE
Câu lệnh GRANT sử dụng trong trường hợp này có cú pháp như sau: 
GRANT ALL | danh_sách_câu_lênh 
TO danh_sách_người_dùng
Trình tự thực hiện
Cấp phát quyền tạo bảng và khung nhìn cho người dùng có tên là thuchanh
- Mở cửa sổ soạn thảo lệnh
- Viết lệnh
GRANT CREATE TABLE,CREATE VIEW 
TO thuchanh
- Thực thi lệnh: Chọn đoạn lệnh/ Nhấn F5
1.2. Thu hồi quyền
1.2.1. Thu hồi quyền trên đối tượng cơ sở dữ liệu
- Cú pháp câu lệnh REVOKE sử dụng để thu hồi quyền đã cấp phát trên đối tượng cơ sở dữ liệu có cú pháp như sau: 
REVOKE [GRANT OPTION FOR] 
 ALL [PRIVILEGES]| các_quyền_cần_thu_hồi 
[(danh_sách_cột)] ON tên_bảng | tên_khung_nhìn 
|ON tên_bảng | tên_khung_nhìn [(danh_sách_cột)]
|ON tên_thủ_tục 
|ON tên_hàm 
FROM danh_sách_người_dùng 
[CASCADE]
- Câu lệnh REVOKE có thể sử dụng để thu hồi một số quyền đã cấp phát cho người dùng hoặc là thu hồi tất cả các quyền (ALL PRIVILEGES)
Trình tự thực hiện
Thu hồi quyền thực thi lệnh INSERT trên bảng LOP đối với người dùng 
thuchanh
- Mở cửa sổ soạn thảo lệnh
- Viết lệnh
REVOKE INSERT 
ON lop 
FROM thuchanh 
- Thực thi lệnh: Chọn đoạn lệnh/ Nhấn F5
1.2.2. Thu hồi quyền thực thi các câu lênh
Việc thu hồi quyền thực thi các câu lệnh trên cơ sở dữ liệu (CREATE 
DATABASE, CREATE TABLE, CREATE VIEW,...) được thực hiện đơn giản với câu lệnh REVOKE có cú pháp: 
REVOKE ALL | các_câu_lệnh_cần_thu_hồi 
FROM danh_sách_người_dùng
 Trình tự thực hiện
Để không cho phép người dùng thuchanh thực hiện lệnh CREATE TABLE 
trên cơ sở dữ liệu, ta sử dụng câu lệnh:
- Mở cửa sổ soạn thảo lệnh
- Viết lệnh
REVOKE CREATE TABLE 
 	FROM thuchanh 
- Thực thi lệnh: Chọn đoạn lệnh/ Nhấn F5
2. Chuyển đổi cơ sở dữ liệu từ các nguồn cơ sở dữ liệu
Mục tiêu : 
- Chuyển đổi được cơ sở dữ liệu đơn giản từ hệ quản trị cơ sở dữ liệu MS SQL - sang MS Access hoặc sang các hệ quản trị cơ sở dữ liệu khác và ngược lại.
- Thực hiện các thao tác an toàn với máy tính
Microsoft Office Access, thường được gọi tắt là MS Access hoặc đơn giản là Access, là một phần mềm quản lý cơ sở dữ liệu quan hệ do hãngMicrosoft giữ bản quyền. Access thường được đóng gói cùng các phần mềm khác trong bộ Microsoft Office và được sử dụng rộng rãi trong các máy tính cài hệ điều hành Windows.
- Các file Microsoft Access thường có phần mở rộng (đuôi) là mdb hay mdbx (nếu là MS Access 2007). Ngoài ra cũng còn có dạng khác. Biểu tượng của chương trình Access là một chiếc chìa khóa.
- Giao diện người sử dụng của Access bao gồm một loạt cửa sổ mở ra bên trong cửa sổ chính Access. Công cụ quản lý cơ sở dữ liệu của Access bao gồm các Tables (bảng), Queries (truy vấn, tìm kiếm), Forms (mẫu), Reports (báo cáo), Macro (các macro lệnh), Modules (các khai báo, thư viện chương trình con). Mỗi một đối tượng trên sẽ được hiện ra trong một cửa số riêng.
- Tables là công cụ xây dựng cơ sở dữ liệu trong Access. Đây là đối tượng cơ bản. Mỗi bảng gồm tên bảng, trường dữ liệu (field) nhận các giá trị khác nhau (như text, number, v.v), bản ghi (records), trường khóa (primary key). Giữa các table có liên hệ với nhau.
- Queries là một công cụ quan trong khác. Đây là công cụ xử lý dữ liệu trong Access. Có 7 loại queries tương ứng với 7 loại xử lý dữ liệu mà Access có thể thực hiện. Đó là:
Select Queries: dùng để trích, lọc, kết xuất dữ liệu
Total Queries: dùng để tổng hợp dữ liệu
Crosstab Queries: dùng để tổng hợp dữ liệu theo tiêu đề dòng và cột dữ liệu
Maketables Queries: dùng để lưu kết quả truy vấn, tìm kiếm ra bảng phục vụ công tác lữu trữ lâu dài.
Delete Queries: dùng để loại bỏ các dữ liệu hết hạn
Update Queries: dùng để cập nhật dữ liệu
Ngoài ra còn có Append Queries.
Trình tự thực hiện
- Click chuột phải lên cơ sở dữ liệu Tasks/ Import Data
- Trên Data Source chọn Microsoft Office 12.0 Access Database Engine
Nhấp vào nút Properties để mở cửa sổ Data Link Properties
- Trên trang Data Link Properties, cung cấp vị trí của cơ sở dữ liệu vào trường Data Source, và nhập tên người dùng và mật khẩu, nếu áp dụng . Click vào Test Connection và chắc chắn rằng nó thành công .
Nhấn OK để đóng Data Link Properties, và tiếp tục đến trang tiếp theo của Wizard, chọn Destination.
Nhấn Next để tiếp tục
- Chọn tùy Copy data from one or more tables or views.
Nhấn Next
- Chọn các bảng hoặc khung nhìn
Nhấn Next
Chọn Execute Immediately và Save SSIS package
Nhấn Next và Finish the Wizard.
Nhấn Close
Kết quả là: 
3. Sao lưu dự phòng cơ sở dữ lịêu
Mục tiêu : 
- Sao lưu được cơ sở dữ liệu
- Thực hiện các thao tác an toàn với máy tính
- Backup: 
Quá trình copy toàn bộ hay một phần của database, transaction log, file hay file group hình thành một backup set. Backup set được chứa trên backup media (tape or disk) bằng cách sử dụng một backup device (tape drive name hay physical filename) 
- Backup Device: 
Một file vật lý (như C:\SQLBackups\Full.bak) hay tape drive cụ thể (như \\.\Tape0) dùng để record một backup vào một backupmedia. 
- BackupFile: 
File chứa một backup set 
 - BackupMedia 
Disk hay tape được sử dụng để chứa một backup set. Backup Media có thể chứa nhiều backup sets (ví dụ như từ nhiều SQLServer 2000 backups và từ nhiều Windows 2000 backups). 
- BackupSet: 
Một bộ backup từ một lần backup đơn được chứa trên backup Media.
Trình tự thực hiện
- Chuột phải vào cơ sở dữ liệu\ Chọn Tasks\ Chọn Back Up 
- Gõ tên file Backup trong mục Name
Chọn nơi lưu file: Nhấn chọn Add
Nhấn chọn nút 
Chọn nơi lưu file backup
Gõ tên file vào mục File Name
Chọn OK
- Chọn OK
- Nhấn chọn OK
- Nhấn chọn OK
4. Bảo trì cơ sở dữ liệu
Mục tiêu : 
- Phục hồi được cơ sở dữ liệu
- Thực hiện các thao tác an toàn với máy tính
Việc sao lưu dự phòng cơ sở dữ liệu nhằm phục hồi lại khi cần thiết, đó l một quá trình bảo trì cơ sở dữ liệu. Trước khi phục hồi cơ sở dữ liệu (restore database) ta phải xác định được thứ tự file cần phục hồi. Các thông tin n y được SQL Server chứa trong msdb.
Trình tự thực hiện
- Chuột phải vào Database\ Chọn Restore Database 
Gõ tên cơ sở dữ liệu cần khôi phục vào mục To Database
Chọn From Device \ Chọn nút 
- Chọn Add
- Chọn file Backup (có phân đuôi là *.bak)
Chọn OK
- Chọn OK
- Chọn 
- Chọn OK