Bài giảng SQL Server 2000

cgia, ngonngu, bia, trangthai End 7.3.3 Biểu thức Case * Các câu lệnh truy vấn dữ liệu – Biểu thức CASE Biểu thức CASE trong Transaction-SQL vô cùng hữu ích. Hoạt động của biểu thức CASE rất đơn giản chỉ là thực hiện việc so sánh một biểu thức bất kỳ với hàng loạt các giá trị chỉ định trước đó, nếu bạn là người lập trình trong môi trường Visual Basic thì biểu thức CASE của Transaction-SQL gần giống như cấu trúc điều khiển Select Case. Tuy nhiên biểu thức CASE hoàn toàn không phải là một cấu trúc điều khiển, điều này có nghĩa là nó chỉ được sử dụng lồng vào các câu lệnh khác mà không thể thực hiện đơn lẻ như các cấu trúc điều khiển khác. Biểu thức CASE có thể sử dụng ở hai dạng khác nhau. Cú pháp CASE dạng đơn giản: Trong đó: • Biểu thức: Biểu thức tính toán hoặc tên cột dữ liệu của bảng được dùng để so sánh. • Giá trị 1, giá trị 2: Là các giá trị cụ thể để so sánh bằng (=) với biểu thức. • Biểu thức kết quả 1, biểu thức kết quả 2: Là các biểu thức sẽ được trả về khi việc so sánh của biểu thức bằng với các giá trị so sánh tương ứng. • Biểu thức kết quả N: Là biểu thức sẽ được trả về khi tất cả các trường hợp so sánh đều không bằng với các giá trị đưa ra. Ví dụ: Để hiển thị danh sách các vật tư có trong bảng VATTU theo từng loại hàng, có đếm tổng số các vật tư theo từng loại hàng. Bạn sử dụng lệnh SELECT FROM có kết hợp biểu thức CASE đơn giản như sau: 132 Kết quả truy vấn trả về: 133 Cú pháp CASE dạng tìm kiếm: Trong đó: • Biểu thức logic1, biểu thức logic2: Là các biểu thức luận lý dùng để thực hiện các phép so sánh trong biểu thức CASE. • Biểu thức kết quả 1, biểu thức kết quả 2: Là các biểu thức sẽ được trả về khi một trong các biểu thức luận lý so sánh có kết quả là đúng. • Biểu thức kết quả N: Là biểu thức sẽ được trả về khi tất cả các biểu thức logic so sánh đưa ra đều sai. Ví dụ: Để hiển thị danh sách các vật tư có trong bảng VATTU, thông tin bổ sung thêm chuỗi ghi chú, tùy thuộc vào giá trị của cột tỷ lệ phần trĕm giá bán. Bạn sử dụng lệnh SELECT FROM có kết hợp biểu thức CASE tìm kiếm như sau: Kết quả truy vấn trả về: 134 Thực tế thì nhưng người lập trình trong môi tường Transaction-SQL thường sử dụng biểu thức CASE tìm kiếm bởi vì khi đó các biểu thức luận lý mà bạn dùng để so sánh được phép chứa nhiều toán tử so sánh khác nhau, trong khi đó biểu thức CASE đơn giản ở phần trên chỉ cho phép bạn thực hiện phép so sánh bằng (=) trên một biểu thức đơn giản. Ví dụ: Để giảm giá bán hàng trong tháng 02/2002 theo quy tắc: • Nếu số lượng hàng <= 2 thì không giảm giá. • Nếu số lượng hàng từ 3 đến 10 thì giảm 10%. • Nếu số lượng hàng > 10 thì giảm 20%. Bạn sử dụng lệnh UPDATE SET có kết hợp biểu thức CASE tìm kiếm như sau: 135 Tóm lại: biểu thức CASE có thể được phép kết hợp sử dụng trong các câu lệnh SELECT, UPDATE SET, DELETE dùng để biện luận các trường hợp khác nhau của các giá trị dữ liệu bên trong một câu lệnh truy vấn. 7.3.4 Cấu trúc điều khiển a. Cấu trúc rẽ nhánh IF ... ELSE Với cấu trúc rẻ nhánh, người lập trình có thể chỉ một một hoặc nhiều câu lệnh sẽ được thực hiện khi giá trị của một biểu thức luận lý là đúng hoặc là sai. Cấu trúc rẽ nhánh được phép sử dụng bên trong một lô (batch) các lệnh hoặc bên trong một thủ tục nội tại. Cấu trúc rẽ nhánh được phép lồng nhiều cấp bên trong và cấp độ lồng nhau của các cấu trúc rẽ nhánh là không có giới hạn. Cú pháp: Trong đó: • Biểu thức luận lý: Thông thường là một biểu thức so sánh dùng để chỉ ra một điều kiện so sánh nào đó. • Câu lệnh 1 / Khối lệnh 1: Các lệnh sẽ được thực hiện khi biểu thức luận lý so sánh có giá trị là đúng (True). • Câu lệnh 2 / Khối lệnh 2: Các lệnh sẽ được thực hiện khi biểu thức luận lý so sánh có giá trị là sai (False). Lưu ý: Khối lệnh được đề cập là một tập hợp từ hai câu lệnh trở lên, trong các trường hợp này bắt buộc bạn phải sử dụng các từ khóa BEGIN và END để hình thành một nhóm các câu lệnh trong một khối lệnh. Cú pháp: 136 Ví dụ: Tính xem có vật tư nào đã bán ra với số lượng nhiều hơn 4 không ? Nếu có thì in ra danh sách các vật tư đó, ngược lại thì thông báo chưa bán được vật tư nào với số lượng nhiều hơn 4. Bạn sử dụng cú pháp IF ... ELSE như sau: Kết quả trả về: Nhận xét: Trong ví dụ trên, sử dụng biểu thức luận lý là một biểu thức so sánh có sử dụng truy vấn SELECT COUNT WHERE dùng để đếm các hàng hóa bán ra có số lượng lớn hơn 4. Nếu kết quả đếm > 0 thì in danh sách các vật tư đó ra. Ngược lại thông báo chưa bán hàng hóa với số lượng nhiều hơn 4. Khi lập trình trong Transaction-SQL, thông thường bạn cần phải kiểm tra dữ liệu có tồn tại bên trong các bảng trước khi thực hiện tiếp các hành động liên quan đến các dòng dữ liệu đó. Cú pháp IF có kết hợp từ khóa EXISTS dùng để kiểm tra sự tồn tại của các dòng dữ liệu bên trong bảng rất hữu hiệu. 137 Cú pháp: Trong đó: • Từ khóa EXISTS: Dùng để kiểm tra sự tồn tại các dòng dữ liệu trong câu lệnh truy vấn SELECT sau đó. Kết quả IF trả về đúng (True) khi câu lệnh SELECT trả về ít nhất một dòng dữ liệu, ngược lại thì trả về sai (False). Ví dụ: Thực hiện lại ví dụ trên nhưng sử dụng cú pháp IF EXISTS dùng để kiểm tra xem đã có hàng hóa nào bán ra với số lượng nhiều hơn 4 chưa. Ví dụ: Để kiểm tra xem có phiếu nhập hàng nào đã lập vào ngày chủ nhật không. Nếu có thì in ra danh sách các phiếu nhập hàng đó. Bạn sử dụng cú pháp IF như sau: 138 Nhận xét: Trong ví dụ trên sử dụng hàm DATENAME với tham số dw (viết tắt của từ day of week, ngày trong tuần) dùng để trả về ngày trong tuần của ngày nhập hàng. b. Cấu trúc lặp WHILE Với cấu trúc lặp, người lập trình có thể chỉ định một hoặc nhiều câu lệnh sẽ được thực hiện lặp lại nhiều lần trong khi giá trị của biểu thức luận lý so sánh vẫn còn đúng. Giống như cấu trúc rẽ nhánh, cấu trúc lặp được phép sử dụng bên trong một lô (batch) các lệnh hoặc bên trong một thủ tục nội tại. Giữa cấu trúc rẽ nhánh và cấu trúc lặp không có thứ tự ưu tiên khi chúng lồng vào nhau và cấp độ lồng nhau là không có giới hạn. Thực tế việc sử dụng cấu trúc lặp WHILE bị giới hạn trong nhiều trường hợp. Bởi vì bản thân các lệnh truy vấn cập nhật dữ liệu như là : SELECT, UPDATE SET, DELETE trong Transaction-SQL đã tự động thực hiện việc lặp từ dòng dữ liệu đầu tiên đến dòng dữ liệu cuối cùng bên trong bảng. Cấu trúc lặp WHILE thông thường được dùng với các biến có kiểu dữ liệu cursor, cách thức sử dụng biến kiểu dữ liệu cursor sẽ được hướng dẫn trong các phần sau. Cú pháp: Trong đó: • Biểu thức luận lý: Thông thường là một biểu thức so sánh để chỉ các lệnh sẽ được lặp lại trong khi mà giá trị của biểu thức vẫn còn đúng. • Các lệnh lặp: Các câu lệnh được thực hiện bên trong vòng lặp. Ví dụ: Để in ra 10 số nguyên dương bắt đầu từ 100. Bạn sử dụng cấu trúc lặp WHILE như sau: 139 Kết quả trả về: Bạn có thể sử dụng từ khóa BREAK lồng vào cấu trúc lặp WHILE để có thể kết thúc việc lặp của các lệnh bên trong vòng lặp mà không cần xét đến giá trị trả về của biểu thức luận lý dùng để so sánh phía sau từ khóa WHILE phải là sai. Tuy nhiên từ khóa BREAK thường được sử dụng kéo theo với một biểu thức luận lý khác. Ví dụ: Thực hiện việc lặp giống ví dụ trên, tuy nhiên muốn vòng lặp kết thúc khi mới in tới số nguyên 105. Bạn sử dụng cấu trúc lặp WHILE như sau: Kết quả trả về: 140 Bạn cũng có thể sử dụng từ khóa CONTINUE lồng vào cấu trúc lặp WHILE để chỉ định các lệnh bên trong vòng lặp ở phía dưới từ khóa CONTINUE tạm thời không thực hiện tiếp, khi đó con trỏ vòng lặp sẽ nhảy về đầu vòng lặp để kiểm tra giá trị của biểu thức luận lý so sánh còn đúng hay không? Tuy nhiên từ khóa CONTINUE thông thường được dùng kèm theo với một biểu thức luận lý khác. Ví dụ: Thực hiện việc lặp giống các ví dụ trên, tuy nhiên muốn rằng vòng lặp sẽ in sót số nguyên 105. Bạn sử dụng cấu trúc lặp WHILE như sau: Kết quả trả về: 141 Kết chứng kết quả trả về bạn suy luận khi vòng lặp thực hiện đến giá trị của biến @Songuyen = 105 thì khi đó lệnh PRINT "Số nguyên : " ... không được thực hiện và con trỏ chương trình được quay lên đầu vòng lặp để kiểm tra tiếp biểu thức luận lý so sánh. Sơ đồ tóm tắt ý nghĩa của cấu trúc lặp WHILE kèm với các từ khóa CONTINUE hoặc BREAK: Cú pháp: Ví dụ: Để tĕng tự động tỷ lệ phần trĕm cho các vật tư trong bảng VATTU theo quy tắc sau: • Mỗi lần chỉ tĕng lên 5% cho các vật tư có giá trị tại cột tỷ lệ nhỏ hơn 30%. • Lặp lại hành động tĕng trong khi mà giá trị trung bình tỷ lệ phần trĕm của các vật tư vẫn còn thấp hơn 40%. Bạn sử dụng các lệnh như sau: 142 Nhận xét: Ví dụ trên khá phức tạp, phải chèn thêm vào các ghi chú (comment) để giúp bạn thấy được từng xử lý rời rạc nhằm dễ xem, dễ hiểu. Trong Transaction-SQL, bạn có thể chèn các ghi chú trong các câu lệnh bằng hai dấu trừ liên tiếp nhau hoặc muốn che lại một khối các lệnh liên tiếp nhau thì bạn sử dụng cặp ký tự như bên dưới: Ngoài ra trong vòng lặp bạn có thể sử dụng lệnh IF EXISTS để kiểm tra trường hợp sau khi đã tĕng hết tất cả tỷ lệ phần trĕm các vật tư đều lớn hơn 30 mà trung bình tỷ lệ phần trĕm của các vật tư vẫn chưa lớn hơn 40 thì bắt buộc vòng lặp phải được thoát ra ngoài, bởi vì nếu không thì vòng lặp sẽ bị lặp vô tận không bao giờ thoát ra được. 143 Chương 8. TẠO TRANSACTION VÀ TRIGGER 8.1 Giao tác (Transaction) Có hai loại giao tác được sử dụng trong Transaction-SQL: Tường minh và không tường minh. Mặc định các lệnh bên trong một lô (patch) chứa các câu lệnh sẽ có loại giao tác là không tường minh. 8.1.1 Khái niệm về giao tác Giao tác trong các loại cơ sở dữ liệu quan hệ lớn được sử dụng trong những trường hợp mà các hành động cập nhật dữ liệu trên nhiều bảng khác nhau được thực hiện trong cùng một đơn vị (unit). Nói một cách khác thì các hành động cập nhật dữ liệu trong một đơn vị sẽ được ghi nhận lại khi tất cả các hành động con bên trong đó thực hiện thành công, ngược lại nếu có ít nhất một hành động nào đó thực hiện thất bại thì tất cả các hành động bên trong đơn vị sẽ bị hủy bỏ để đảm bảo tính toàn vẹn của dữ liệu trong các bảng. Ví dụ: Bạn hình dung một khách hàng có cùng lúc 2 loại tài khoản trong ngân hàng. Một là tài khoản thanh toán dùng để thực hiện các giao dịch thu chi qua lại của khách hàng với các công ty khác. Hai là tài khoản tiết kiệm cá nhân của khách hàng cho phép khách hàng gởi tiền tiết kiệm để lấy tiền lãi cuối kỳ theo kỳ hạn 3 tháng. Giả sử sau thời gian 3 tháng, khách hàng đến ngân hàng để nhận số tiền lãi từ tài khoản tiết kiệm cá nhân. Tuy nhiên vị khách hàng này muốn bộ phận giao dịch tài khoản thực hiện tự động chuyển số tiền lãi từ tài khoản tiết kiệm sang tài khoản thanh toán của mình. Nhận xét thấy rằng trong hệ thống chương trình tại ngân hàng phải thực hiện hai hành động cập nhật dữ liệu: Một là lấy ra số tiền lãi trong tài khoản tiết kiệm, hai là nạp số tiền lãi vào tài khoản thanh toán. Chuyện gì xảy ra nếu một trong hai hành động thực hiện không thành công mà hành động còn lại vẫn được ghi lại nhận vào cơ sở dữ liệu? Bạn cùng xem xét như sau: • Trường hợp 1: Nếu hành động rút số tiền lãi trong tài khoản tiết kiệm thực hiện thành công và hành động nạp số tiền lãi đó vào tài khỏan thanh toán thực hiện bị thất bại thì xem như khách hàng đã mất đi số tiền lãi của tài khoản tiết kiệm (khách hàng mất tiền). 144 • Trường hợp 2: Nếu hành động rút số tiền lãi trong tài khỏan tiết kiệm thực hiện thất bại và hành động nạp số tiền lãi đó vào tài khoản thanh toán thực hiện thành công thì xem như khách hàng có thêm số tiền lãi ở cả hai tài khoản (ngân hàng mất tiền). Nhận xét thấy rằng cả hai trường hợp nêu trên đều làm cho hệ thống vi phạm tính toàn vẹn dữ liệu và có ảnh hưởng đến uy tín chất lượng của ngân hàng. Nhưng nếu nhờ vào khái niệm của giao tác, bạn có thể quy định cả hai hành động trên sẽ được bao bên trong một đơn vị giao tác nhằm nói rằng chúng sẽ được ghi nhận lại khi cả hai hành động con bên trong đó thực hiện thành công, ngược lại nếu trường hợp 1 hoặc trường hợp 2 mô tả ở phần trên có xảy ra thì tất cả các hành động bên trong giao tác sẽ bị hủy bỏ (không ghi lại các thay đổi dữ liệu). Điều này sẽ làm cho hệ thống không vi phạm tính toàn vẹn dữ liệu. 8.1.2 Giao tác không tường minh Có hai loại giao tác được sử dụng trong Transaction-SQL: Tường minh và không tường minh. Mặc định các lệnh bên trong một lô (patch) chứa các câu lệnh sẽ có loại giao tác là không tường minh, điều này có nghĩa là nếu có ít nhất một câu lệnh thực hiện không thành công bên trong lô thì tất cả các lệnh còn lại sẽ không được ghi nhận lại. Bạn không nên sử dụng loại giao tác này. Ví dụ: Bạn cho thực hiện cùng lúc 3 lệnh để cập nhật dữ liệu vào 3 bảng khác nhau trong cùng một lô. Tuy nhiên ở câu lệnh cuối cùng khi thực hiện sẽ bị thất bại do vi phạm tính toàn vẹn dữ liệu khóa ngoại (vì đơn đặt hàng đã được nhận hàng rồi nên không thể xóa được) nên các lệnh trước đó trong cùng một lô sẽ không được ghi nhận lại. 145 Để kiểm chứng lại các lệnh thêm vật tư mới, sửa đổi tên nhà cung cấp có được ghi nhận lại hay không? Bạn thực hiện các lệnh SELECT FROM để xem lại dữ liệu các bảng VATTU và NHACC. Nhận xét thấy rằng trong ví dụ trên, các lệnh thêm vật tư mới, sửa đổi tên nhà cung cấp hoàn toàn không được ghi nhận lại trong lô khi câu lệnh cuối cùng thực hiện bị lỗi (vì vật tư mới không được thêm vào bảng VATTU). Thông thường giao tác tường minh được sử dụng trong các trường hợp cập nhật dữ liệu trên nhiều bảng khác nhau và phải đảm bảo các hành động này nằm trong cùng một đơn vị xử lý. 8.2 Cách tạo (Transaction) 8.2.1 Giao tác tường minh Để bắt đầu một giao tác tường minh, bạn phải sử dụng câu BEGIN TRAN trong dòng lệnh đầu tiên của một đơn vị xử lý. Để chỉ định cho Microsoft SQL Server kết thúc giao tác và ghi nhận lại các hành động cập nhật dữ liệu thì bạn phải sử dụng lệnh COMMIT TRAN và ngược lại khi sử dụng lệnh ROLLBACK TRAN dùng để chỉ định cho Microsoft SQL Server kết thúc giao tác mà không ghi nhận lại các hành động cập nhật dữ liệu trong giao tác. 146 8.2.2 Lệnh chỉ định bắt đầu một giao tác Như phần trên đã trình bày lệnh BEGIN TRAN dùng để sử dụng trong các giao tác tường minh. Mỗi giao tác có thể được ghép lồng các giao tác con bên trong đó, bạn có thể chỉ định tên cho từng giao tác lồng nhau nhằm thực hiện dễ dàng việc kết thúc của mỗi giao tác. Biến hệ thống @@TRANCOUNT trả về cấp độ lồng hiện hành bên trong các giao tác. Cú pháp lệnh chỉ định bắt đầu một giao tác được mô tả như bên dưới. Cú pháp: Trong đó: • Tên giao tác: Tên của giao tác được chỉ định rõ ràng, chỉ nên sử dụng tên giao tác khi cấp độ lồng nhau của các giao tác nhiều hơn hai cấp. Ví dụ: Sử dụng lệnh BEGIN TRAN để chỉ định bắt đầu thực hiện giao tác: thêm vật tư mới vào bảng VATTU, tuy nhiên khi kết thúc giao tác bạn không lưu lại vật tư này. Kết quả trả về: 147 Nhận xét thấy rằng trong ví dụ này, trước khi thực hiện giao tác, chúng ta có 11 vật tư, sau đó trong giao tác thêm vào một vật tư mới. Tuy nhiên cuối cùng khi kết thúc giao tác chúng ta không ghi lại hành động thêm vật tư bằng lệnh ROLLBACK TRAN, do đó tổng số vật tư vẫn là 11 vật tư khi kết thúc giao tác. 8.2.3 Các lệnh chỉ định kết thúc một giao tác Theo ví dụ trên chúng ta có thể hiểu ý nghĩa của lệnh ROLLBACK TRAN dùng để chỉ định kết thúc giao tác nhưng không ghi nhận lại các hành động cập nhật dữ liệu bên trong giao tác. Ngoài ra chúng ta cỏ thể sử dụng lệnh COMMIT TRAN dùng để chỉ định kết thúc giao tác nhưng đồng ý ghi nhận lại các hành động cập nhật dữ liệu bên trong giao tác. Cú pháp của cả hai lệnh này được mô tả như bên dưới. Cú pháp: Hoặc Trong đó: • Tên giao tác: Tên của giao tác được định nghĩa trước đó trong câu lệnh BEGIN TRAN. 148 Ví dụ: Tạo một bảng tạm dùng để minh họa việc sử dụng các giao tác lồng nhau. Kết thúc giao tác ngoài cùng bằng lệnh ROLLBACK TRAN và không ghi nhận lại các hành động cập nhật dữ liệu của các giao tác con trước đó. Điều này có nghĩa là dữ liệu của bảng tạm #TestTran là hoàn toàn trống. Nhận xét thấy rằng trong ví dụ này tên của các giao tác được sử dụng trong các lệnh ROLLBACK TRAN hoặc COMMIT TRAN chỉ để giúp cho chúng ta dễ đọc và dễ thấy được cấp độ hiện hành của các giao tác lồng nhau, nó hoàn toàn không có một mối liên hệ gì giữa tên giao tác trong các lệnh BEGIN TRAN trước đó. Việc sử dụng đối tượng thủ tục nội tại để cung cấp các dữ liệu, các tính toán trên các màn hình nhập liệu, báo cáo bên trong ứng dụng sẽ làm cho tốc độ các xử lý tại nhánh máy chủ được nhanh hơn trong các ứng dụng mô hình khách chủ. 8.2.4 Phân vùng trong giao tác Chúng ta có thể chỉ định việc đồng ý ghi nhận hoặc không ghi nhận lại các hành động cập nhật dữ liệu riêng lẻ bên trong một giao tác bằng cách phân chia thành nhiều vùng nhỏ cho các câu lệnh bên trong một giao tác. 149 Bằng cách này chúng ta chia nhỏ các hành động bên trong giao tác ra thành nhiều phần, tương ứng từng phần nhỏ chúng ta có thể dễ dàng chủ động đồng ý ghi nhận hoặc không ghi nhận lại việc cập nhật dữ liệu. Cú pháp của lệnh SAVE TRANSACTION cho phép chúng ta có thể làm được những điều như đã mô tả ở trên. Cú pháp: Trong đó: • Tên vùng: Dùng để chỉ định vùng chứa các lệnh cập nhật dữ liệu và tên vùng nên duy nhất trong một giao tác. • Các lệnh: Các lệnh được phân chia theo vùng bên trong giao tác. Ví dụ: Như ví dụ trên, tuy nhiên chúng ta muốn phân chia lệnh thêm mới mẫu tin thứ nhất và thứ hai trong vùng thứ nhất, lệnh thêm mới mẫu tin thứ ba trong một vùng thứ hai trong cùng một giao tác. Kết thúc giao tác thực hiện ghi nhận lại các lệnh trong vùng thứ nhất nhưng không ghi nhận lại các lệnh trong vùng thứ hai (chỉ có mẫu tin thứ nhất và thứ hai được ghi lại). 150 8.2.5 Kiểm lỗi bên trong giao tác Thông thường khi làm việc bên trong giao tác, chúng ta sẽ không bao giờ chỉ định rõ ràng việc kết thúc một giao tác bằng các lệnh cụ thể COMMIT TRAN hoặc ROLLBACK TRAN mà thay vào đó chúng ta sẽ kiểm tra theo một điều kiện quy định trước. Nếu điều kiện này bị sai thì bắt buộc chúng ta sẽ không ghi nhận các hành động cập nhật dữ liệu trong giao tác, ngược lại sẽ đồng ý ghi nhận các hành động đó. Để làm được điều này, thông thường chúng ta sử dụng giá trị của biến hệ thống @@ERROR trong việc kiểm tra để biết kết quả của câu lệnh thực hiện gần nhất là thành công hay thất bại. Giá trị của biến hệ thống @@ERROR trả về bằng không khi câu lệnh gần nhất thực hiện thành công, ngược lại thì trả về giá trị khác không khi câu lệnh gần nhất thực hiện có lỗi. Ví dụ: Thực hiện công việc cấp phát số chứng từ tự động cho các bảng DONDH, PNHAP, PXUAT đảm bảo rằng các số này không bị trùng lắp khi cùng lúc có nhiều người sử dụng cùng lập các chứng từ liên quan. Thực hiện từng bước như sau: Đầu tiên chúng ta xây dựng bảng CAP_SOCTU dùng lưu trữ số chứng từ được cấp kế tiếp cho các bảng, gồm có các cột : tên bảng (tenbang), số chứng từ (soctu), ký tự đầu (kytu). Trong đó cột tên bảng tham gia làm khóa chính. Kế tiếp lần lượt thêm các dòng dữ liệu vào bảng CAP_SOCTU: 151 Sau cùng, chúng ta xây dựng thủ tục cấp số chứng từ tự động đảm bảo không trùng lắp. Có sử dụng việc kiểm tra lỗi khi thực hiện các lệnh trong giao tác. 152 Gọi thực hiện thủ tục trên để có được số chứng từ kế tiếp cho bảng PXUAT. Kết quả trả về: Tóm lại: việc sử dụng đối tượng thủ tục nội tại để cung cấp các dữ liệu, các tính toán trên các màn hình nhập liệu, báo cáo bên trong ứng dụng sẽ làm cho tốc độ các xử lý tại nhánh máy chủ được nhanh hơn trong các ứng dụng mô hình khách chủ. 153 8.3 Trigger 8.3.1 Khái niệm Trigger là một trường hợp đặc biệt của store procedure, nó sẽ có hiệu lực khi chúng ta thay đổi dữ liệu trên một bảng dữ liệu cụ thể, hoặc các xử lý làm thay đổi dữ liệu của các lệnh: insert, update, delete. Trigger có thể chứa các lệnh truy vấn từ các bảng khác hoặc bao gồm những lệnh SQL phức tạp. * Một số thuận lợi khi sử dụng trigger: Trigger chạy một cách tự động: Chúng được kích hoạt ngay tức thì khi có sự thay đổi dữ liệu trên bảng dữ liệu. Trigger có thể thực hiện Cascade khi việc thi hành có ảnh hưởng đến những bảng liên quan. Trigger có những hiệu lực ít bị hạn chế hơn so với ràng buộc giá trị nghĩa là có thể ràng buộc tham chiếu đến những cột của những bảng dữ liệu khác. Khi trigger được kích hoạt bởi 1 lệnh Transact-SQL, insert để thêm một bộ mới vào bảng AAA thì bộ mới này được lưu tạm thời vào một bảng tạm có tên là inserted có cùng cấu trúc với bảng AAA. Khi kết thúc trigger này thì bộ dữ liệu mới thật sự lưu xuống CSDL. Tương tự đối với lệnh delete, các bộ dữ liệu bị xóa sẽ chuyển tạm vào bảng tạm deleted. 8.3.2 Cú pháp - Lệnh tạo Trigger Create Trigger trigger_name on table_name For [insert,update,delete] As Begin {Khai báo các biến xử lý} {Các lệnh Transact-SQL} End 8.3.3 Lệnh xóa Trigger Drop Trigger trigger_Name 154 Ví dụ: Tạo trigger cho thao tác xóa một đầu sách trong bảng Muon. CREATE TRIGGER tg_delMuon ON muon FOR delete AS Begin DECLARE @isbn int, @ma_cuonsach smallint SELECT @isbn = isbn, @ma_cuonsach = ma_cuonsach FROM deleted UPDATE cuonsach SET tinhtrang = yes WHERE isbn = @isbn AND ma_cuonsach = @ma_cuonsach End 155 Chương 9. SAO LƯU VÀ KHÔI PHỤC DỮ LIỆU Chương này sẽ giới thiệu kỹ thuật sao lưu (backup) và khôi phục (restore) dữ liệu, là kỹ thuật thường được sử dụng bảo đảm an toàn dữ liệu phòng trường hợp CSDL bị hỏng, nhật ký dữ liệu. Chức nĕng này được thực hiện bằng 2 phương pháp: Bằng công cụ và câu lệnh T-SQL. * Những 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 ngoà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. 9.1 Các phương pháp sao lưu dữ liệu 9.1.1 Các loại BACKUP Backup dữ liệu trong SQL Server gồm các loại sau: + Full Database Backups: Copy toà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: Copy 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: Copy 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. 9.1.2 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 toà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. 156 + 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. 9.1 Màn hình Backup Database 9.2 Các phương pháp khôi phục dữ liệu 9.2.1 Các mô hình phục hồi dữ liệu + Full Recovery model: Là mô hình phục hồi toà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 index, 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 toà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. 157 + Simple Recovery Model: Với mô hình này bạn chỉ phục hồi lại thời điểm backup 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 9.2 Màn hình đặt mô hình khôi phục dữ liệu Xét ví dụ sau: Giả sử ta có một CSDL được backup theo chiến lược như hình vẽ: 158 Nhìn hình trên 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. 9.2.2 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à bạn 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. 9.3 Màn hình khôi phục dữ liệu 159 Chương 10. QUẢN LÝ - BẢO MẬT VÀ NGƯỜI DÙNG TRONG SQL SERVER 10.1 Khái niệm Mỗi CSDL có 1 hay nhiều users được chỉ định quyền truy xuất dữ liệu. Người quản trị có thể cấp quyền truy xuất CSDL bằng cách tạo một tài khoản đĕng nhập (login) SQL Server cho User, thêm User vào CSDL và gán quyền cho User trên CSDL đó. Bao gồm các loại quyền:  Quyền truy cập vào SQL Server  Quyền truy xuất CSDL  Quyền thực hiện trên các đối tượng của CSDL  Quyền xử lý dữ liệu 10.2 Sử dụng Enterprise Manager cấp phát quyền cho người dùng 10.2.1 Tạo tài khoản đĕng nhập (Login Acount) - Mở mục Security, click phải mục Login và chọn New Login - Trên trang General, nhập tên đĕng nhập, chế độ xác nhận, CSDL mặc định. 10.1 Màn hình Tạo tài khoản đĕng nhập Chú ý: Đĕng nhập với SQL Server thường dùng cho việc kết nối quay số và mạng peer- to-peer. - Trang Server Roles: Chọn vai trò quản trị mức Server cho tài khoản đĕng nhập 160 System Administrators Security Administrators Server Administrators Setup Administrators Database Creator Disk Administrators Process Administrators Bulk Administrators Đặc quyền cao nhất; cho phép thực hiện mọi tác vụ trên SQL Quản lý các server logins. Cho phép bạn định cấu hình những cài đặt server-wide. Cho phép thêm và xóa các linked servers, và truy xuất vài SP Tạo và hiệu chỉnh databases. Quản lý các files trên đĩa. Quản lý tiến trình đang chạy trong một thể hiện của SQL Server. Thực hiện phát biểu BULK INSERT. Chú ý: Bất kỳ Users của Windows NT thuộc nhómBUILTIN\Administrators đều có vai trò sysadmin. - Trang Database Access: Chọn CSDL được phép truy xuất và vai trò của nó trong từng CSDL được chọn. Fixed Database Role Description Public Vai trò chung cho tất cả người dùng. db_owner Quyền cao nhất trong database. db_accessadmin Điều khiển truy xuất, cài đặt hoặc xóa user accounts. db_datareader Đọc tất cả dữ liệu trên database. db_datawriter Thêm, sửa, xóa dữ liệu trên các tables người dùng trong database. db_ddladmin Thêm, sửa, xóa các đối tượng objects (runs all DDLs). db_securityadmin Quản lý các roles, các thành viên của role, giấy phép trong database. db_backupoperator Cho phép back up database. db_denydatareader Từ chối quyền truy vấy dữ liệu trong database. db_denydatawriter Từ chối quyền thay đổi dữ liệu trong database. Sau khi tạo login, nó tự động nhập vào tập Users của mỗi database được chọn, với tên User trùng với tên Login. Bạn có thể thay đổi thu hồi vai trò của nó trên từng CSDL bằng cách sửa đổi thuộc tính của Login, hoặc chuyển đến tập Users của database và thay đổi thuộc tính hoặc xoá user nào mà bạn không muốn cho truy xuất data của bạn. Các Login được lưu trong table SysLogins của CSDL Master: If Exists( Select 1 From Master..SysLogins Where Name = 'Login') Các User trong một CSDL được lưu trong table SysUsers của CSDL đó If Exists( Select 1 From SysUsers Where Name = 'User01') 10.2.2 Thay đổi thuộc tính cho Login Bấm đúp vào tên Login hoặc click phải và chọn mục Properties 161 10.2.3 Cấp quyền thực thi trên mỗi CSDL Chọn database, trong mục Users bấm đúp vào tên User cần hiệu chỉnh (Login-ID). Click nút Permission để chỉ định quyền truy cập dữ liệu trên từng Table, View. Quyền kiểm tra RB tham chiếu (DRI - Declarative Referential Integrity). Quyền thực hiện các thủ tục lưu trữ. Chú thích: Quyền kiểm tra RB tham chiếu được sử dụng khi Table A được cấp quyền Update hoặc Insert. Table A có RB FOREIGN KEY với table B, mà table B không được cấp quyền SELECT. 10.2.4 Cấp quyển tạo đối tượng trên CSDL o Click phải vào tên CSDL, chọn Properties. o Trong HT Properties, chọn trang Permissions. o Đánh dấu chọn các phát biểu được cần cấp quyền thực hiện cho các User. Xóa User trên một CSDL: Click phải vào tên user và chọn Delete. Tạo User với Login đã có: Click phải vào mục Users và chọn New Database User... Chú ý: Bạn có thể thay đổi tên User bằng cách xóa và tạo lại với login cũ. 10.2 Màn hình cấp quyển tạo đối tượng trên CSDL 10.2.5 Xóa tài khoản đĕng nhập - Click phải vào tên Login và chọn Delete. - Với cách này sẽ xóa bỏ các user trong các CSDL đã chọn cho tài khoản này. 10.3 Sử dụng Query Analyzer cấp phát quyền cho người dùng 10.3.1 Tạo tài khoản đĕng nhập (Login Acount): a. Thêm tài khoản với chế độ xác nhận SQL Server: SP_ADDLOGIN [ @login = ] [ , @password = ] 162 [ , @defdb = ] [ , @deflanguage = ] [ , @sid =] [ , @encriptopt = 'skip_encription' ] o @defdb: Tên CSDL mặc định được mở khi đĕng nhập. Nếu không chỉ định mặc định là CSDL Master. o @deflanguage: Ngôn ngữ mặc định. o @sid: varbinary(16): Mã nhận dạng của hệ thống. Nếu không chỉ định, hệ thống tự tạo một mã số mới. o @encriptopt varchar(20): Mặc định Password sẽ được mã hóa khi lưu trong các table hệ thống. Ngoại trù bạn gán giá trị skip_encryption cho tham số này. o Thủ tục trả về giá trị 0 nếu thành công, ngược lại trả về giá trị 1. Ví dụ: Tạo tài khoản tên 'myname', mật khẩu 'mypwd' vào CSDL 'QLDeTai' Sp_AddLogin 'user01', '01', 'QLDeTai' Kết quả trả về: New loigin created b. Cấp quyền kết nối cho User hay nhóm User của Windows kết nối đến SQL Server SP_GRANTLOGIN [@loginame =] 'login' 'Login': |\ Ví dụ: Thêm tài khoản cho user Windows NT [Server4\User01] kết nối đến SQL Server. EXEC sp_grantlogin ' Server4\User01' Hay EXEC sp_grantlogin [Server4\User01] Chú ý: Chỉ được thức hiện bởi những thành viên có vai trò sysadmin và securityadmin. 10.3.2 Thay đổi mật khẩu Sp_Password [ @old = ] [ , @new = ] [ , @LoginName = ] 10.3.3 Cấp quyền truy xuất CSDL hiện hành cho Login Sp_GrantDBAccess [ @loginname = ] 'Tênđĕngnhập' [ [, @name_in_db = ] 'TênUser' ] 163 Ví dụ: Use QLDeTai Go sp_GrantDBAccess 'user01', 'Anh' 10.3.4 Xóa quyền truy xuất CSDL hiện hành Sp_RevokeDBAccess [ @name_in_db = ] 'TênUser' Ví dụ: Sp_RevokeDBAccess 'Anh' 10.3.5 Cấp quyền thực thi trên CSDL Bao gồm các quyền: Select, Insert, Update, Delete, Reference, Excecute. GRANT ALL | [,] ON [(,..)] | TO [,] [WITH GRANT OPTION] [AS ] o All: Cấp tất cả các quyền thực thi Select, Insert, Update, Delete hay Reference trên table hay view; quyền Excecute cho SP. o TO : Khi cấp quyền cho nhóm hay user của Windows NT, phải chỉ định: |\. Để cấp quyền cho nhóm cục bộ Windows NT built-in, dùng từ khóa BUILTIN thay thế tên domain hoặc computer name. Quyền thực thi đã cấp cho role Public được áp dụng cho tất cả users trong CSDL. Quyền thực thi đã cấp cho user Guest được sử dụng cho tất cả Users không được phân quyền truy xuất trên CSDL. Ví dụ: USE QLDeTai Grant All On DeTai To User01 GO GRANT SELECT ON DeTai TO public GO GRANT INSERT, UPDATE, DELETE ON DeTai TO Mary, [Corporate\BobJ] GO o WITH GRANT OPTION: Cho phép Login cấp quyền đã chỉ định trên đối tượng cho Login khác. 164 o AS {group | role}: Được dùng khi quyền thực thi trên một đối tượng đã cấp cho nhóm hoặc role, và một User của nhóm hoặc Role muốn cấp quyển thực thi cho User khác không là thành viên của nhóm hoặc Role. Ví dụ: Table NhatKy được tạo bới user Lac. Lac cấp quyền SELECT table NhatKy cho Role BanBe với mênh đề WITH GRANT OPTION để các user thành viên của Role BanBe có thể nhường quyền này cho các user khác không thuộc Role BanBe. User Hong, là thành viên của BanBe, muốn nhường quyền SELECT table NhatKy cho user Khoa, không là thành viên của role BanBe. /* User Lac */ GRANT SELECT ON NhatKy TO BanBe WITH GRANT OPTION /* User Hong */ GRANT SELECT ON NhatKy TO Khoa AS BanBe 10.3.6 Từ chối quyền thực thi trên CSDL DENY ALL | [,] [ ( column [ ,...n ] ) ] ON { table | view } | ON table | view [ ( column [ ,...n ] ) ] | ON stored_procedure TO [,] [Cascade] Ví dụ: USE QLDeTai GO GRANT SELECT ON DeTai TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON DeTai TO Mary, John, Tom 10.3.7 Xóa bỏ quyền thực thi đã cấp hoặc từ chối trên CSDL REVOKE [ GRANT OPTION FOR ] { ALL | 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 ] 165 [ CASCADE ] [ AS { group | role } ] Ví dụ : Xóa bỏ quyền Select đã từ chối cho User Mary trên table DeTai. REVOKE SELECT ON DeTai TO Mary 10.3.8 Cấp quyền tạo đối tượng trong CSDL GRANT ALL | [,] TO [,] Bao gốm các lệnh: CREATE DATABASE; CREATE DEFAULT; CREATE PROCEDURE; CREATE RULE; CREATE TABLE; CREATE VIEW; BACKUP DATABASE; BACKUP LOG Ví dụ: GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] 10.3.9 Từ chối quyền tạo đối tượng trên CSDL DENY { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] Ví dụ: DENY CREATE TABLE FROM Joe, [Corporate\BobJ] 10.3.10 Xóa bỏ quyền tạo đối tượng đã cấp hoặc từ chối trên CSDL REVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] Ví dụ: REVOKE CREATE TABLE FROM Joe, [Corporate\BobJ] 10.4. Vai trò của User trong SQL Server SQL Server hổ trợ 2 nhóm roles:sion 28—SQL Server Security 321 o Vai trò trên SQL Server Chứa các quyền quản trị SQL Server o Vai trò trên Database: Chứa các quyền quản lý và thực thi trên các đối tượng của CSDL. Ngoài các vai trò được cung cấp bởi SQL Server có thể tạo thêm vai trò khác. 10.4.1 Tạo vai trò trên CSDL * Trong EM: Mở CSDL, Click phải vào mục Roles và chọn New Database Role, Nhập tên Role mới và click nút Add để thêm User * Trong QA: SP_ADDROLE [@rolename =] 'role' [ , [@ownername =] 'owner'] Ví dụ: Trong CSDL QLDeTai thêm role 'QuanLy' SP_ADDROLE 'QuanLy' 166 Go GRANT SELECT ON DeTai TO QuanLy 10.4.2 Thêm User vào Role SP_ADDROLEMEMBER [@rolename =] 'role', [@membername =] 'UserName' Ví dụ: Cấp quyền truy cập CSDL QLDeTai cho User và thêm vai trò ‘QuanLy’ cho User. USE QLDeTai GO EXEC SP_GRANTDBACCESS 'Server4\User01', 'Hong' GO EXEC SP_ADDROLEMEMBER 'QuanLy', 'Hong' 167 BÀI TẬP Bài 1: Table SINHVIEN HOSV TENSV MASV NGAYSINH PHAI MAKHOA Tran Minh Son S001 1985-05-01 Nam CNTT Nguyen Quoc Bao S002 1986-06-15 Nam CNTT Phan Anh Tung S003 1983-12-20 Nam QTKD Bui Thi Anh Thu S004 1985-02-01 Nu QTKD Le Thi Lan Anh S005 1987-07-03 Nu DTVT Nguyen Thi Lam S006 1984-11-25 Nu DTVT Phan Thi Ha S007 1988-07-03 Nu CNTT Tran The Dung S008 1985-10-21 Nam CNTT Table KHOA MAKHOA TENKHOA AVAN Khoa anh van CNTT Cong nghe thong tin DTVT Dien tu vien thong QTKD Quan tri kinh doanh Table MONHOC TENMH MAMH SOTIET Anh van AV 45 Co so du lieu CDDL 45 Ky thuat lap trinh KTLT 60 Ke toan tai chinh KTLT 45 Toan cao cap TCC 60 Tin hoc van phong THVP 30 Tri tue nhan tao TTNT 60 168 Table KETQUA MASV MAMH LANTHI DIEM S001 CSDL 1 4 S001 TCC 1 6 S002 CSDL 1 3 S002 CSDL 2 6 S003 KTTC 1 5 S004 AV 1 8 S004 THVP 1 4 S004 THVP 2 8 S006 TCC 1 5 S007 AV 1 2 S007 AV 2 9 S007 KTLT 1 6 S008 AV 1 7 Câu 1: Tạo CSDL, các bảng và các khoá ngoại cho trên Câu 2: Xóa các ràng buộc khoá ngoại tham chiếu đến bảng SINHVIEN và bảng MONHOC Câu 3: Xóa bảng KHOA và bảng MONHOC Câu 4: Tạo lại các bảng và các khóa ngoại đã xóa ở câu 2 và 3 Câu 5: Nhập dữ liệu cho tất cả các bảng Câu 6: Sửa số tiết của môn "Tri Tue Nhan Tao" lại 50 tiết Câu 7: Xóa kết qủa của sinh viên có mã sinh viên S001 Câu 8: Chèn lại sinh viên S001 vừa xóa ở câu 7 Câu 9: Sửa sinh viên 'Nguyen Thi Lam' thành 'Nguyen Thanh Son' và phái thành 'Nam' Câu 10: Chuyển sinh viên 'Le Thi Lan Anh' sang khoa CNTT Câu 11: Cho biết kết qủa học tập của sinh viên có mã S007 Câu 12: Liệt kê những sinh viên sinh vào ngày 03 tháng 07 Câu 13: Cho biết danh sách sinh viên mà họ có chứa chữ Anh Câu 14: Cho biết kết quả học tập gồm: HOSV, TENSV, MAMH, LANTHI, DIEM của sinhviên S007 169 Câu 15: Cho biết kết quả học tập gồm: HOSV, TENSV, TENMH, LANTHI, DIEM của sinh viên S007 Câu 16: Cho biết danh sách sinh viên Nam khoa 'Cong Nghe Thong Tin' sắp tĕng dần theo tên Câu 17: Cho danh sách các môn học trên 40 tiết và có tên bắt đầu bằng chữ T Câu 18: Liệt kê những sinh viên từ 22 tuổi trở lên khoa 'Cong Nghe Thong Tin' và khoa 'Dien Tu Vien Thong' Câu 19: Cho biết tên khoa nào không có Sinh Viên Câu 20: Cho danh sách sinh viên chưa học môn nào, thông tin gồm Ho, Ten, Ngaysinh, Tenkhoa Câu 21: Cho danh sách những sinh viên đã học môn có mã AV, sắp giảm dần theo ngày sinh. Thông tin hiển thị gồm MASV,HOSV,TENSV,NGAYSINH,ten KHOA Câu 22: Cho biết tên sinh viên được điểm cao nhất Câu 23: Cho biết sinh viên nào rớt môn CSDL ở lần thi thứ nhất Câu 24: Cho biết sinh viên nào rớt môn CSDL ở lần thi thứ nhất mà chưa thi lại lần 2 Câu 25: Đếm số lượng sinh viên của khoa 'Quan Tri Kinh Doanh' Câu 26: Đếm số lượng sinh viên của từng khoa Câu 27: Cho biết tên khoa có đông sinh viên nhất Câu 28: Cho biết điểm thấp nhất của mỗi môn học Câu 29: Cho biết tên môn học nào chưa có sinh viên học Câu 30: Cho biết điểm trung bình lần thi thứ nhất của sinh viên S004 Câu 31: Cho biết điểm trung bình, điểm lớn nhất, điểm nhỏ nhất lần thứ nhất của từng sinh viên. Thông tin gồm TenSV, TenKhoa, DiemTB 170 Bài 2: Người ta cần viết chương trình quản lý điểm và đề tài tốt nghiệp tại một khoa của một trường đại học với các đặc trưng như sau: - Một sinh viên có thể làm nhiều đề tài - Một đề tài có thể có nhiều sinh viên tham gia - Một đề tài có một giáo viên hướng dẫn và một giáo viên phản biện - Điểm của một sinh viên là trung bình cộng của 5 điểm (GVHD + GVPB + 3*GVUV) - Một giáo viên có 2 tham số là học vị và học hàm. - Một học vị thuộc một chuyên nghành nhất định. - Một sinh viên phải trải qua 2 kỳ bảo vệ là thử và thật. - Điểm số chỉ cho trong buổi bảo vệ thật - Một hội đồng phải có một chủ tịch - Chủ tịch hội đồng có thể vừa phản biện hay hướng dẫn - Hội đồng thật có thể khác hội đồng thử - Cần lưu dấu vết học vị cho các giáo viên - Một giáo viên có thể hướng dẫn nhiều đề tài, phản biện nhiều đề tài. Sau khi phân tích ta có mô hình ERD như sau SINHVIEN(MSSV, TENSV, SODT, LOP, DIACHI) DETAI(MSDT, TENDT) SV_DETAI(MSSV, MSDT) GIAOVIEN(MSGV, TENGV, DIACHI, SODT, MSHHAM, NAMHH) HOCVI(MSHV, TENHV) CHUYENNGHANH(MSGV, TENCN) GV_HV_CN(MSGV, MSHV, MSCN, NAM) HOCHAM(MSHH, TENHH) GV_HDDT(MSGV, MSDT, DIEM) GV_PBDT(MSGV, MSDT, DIEM) GV_UVDT(MSGV, MSDT, DIEM) HOIDONG(MSHD, PHONG, TGBD, NGAYHD, TINHTRANG, MSGVCTHD) HOIDONG_GV(MSHD, MSGV) DOIHONG_DT(MSHD, MSDT, QUYETDINH) 171 Kiểu dữ liệu của các bảng Tên bảng Tên cột Kiểu dữ liệu Ghi chú SINHVIEN MSSV Char(6) Khoá chính TENSV Varchar(30) Not null SODT Varchar(10) LOP Char(6) Not null DIACHI Char(50) Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú DETAI MSDT Char(6) Khoá chính TENDT Varchar(30) Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú SV_DETAI MSSV Char(6) Tc SINHVIEN MSDT Char(6) Tc DETAI Tên bảng Tên cột Kiểu dữ liệu Ghi chú GIAOVIEN MSGV Smallint Khoá chính TENGV Varchar(30) Not null DIACHI Varchar(50) Not null SODT Varchar(10) Not null MSHHAM Smallint Tc HOCHAM NAMHH SmallDatetime Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú HOCVI MSHV Smallint Khoá chính TENHV Varchar(10) Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú CHUYENNGANH MSCN Smallint Khoá chính TENCN Varchar(30) Not null 172 Tên bảng Tên cột Kiểu dữ liệu Ghi chú GV_HV_CN MSGV Smallint Khoá chính, tc GIAOVIEN MSHV Smallint Khoá chính, tc HOCVI MSCN Smallint Khoá chính, tc CHUYENNGHANH NAM SmallDatetime Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú HOCHAM MSHH Smallint Khoá chính TENHH Varchar(20) Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú GV_HDDT MSGV Smallint Khoá chính, tc GIAOVIEN MSDT Char(6) Khoá chính, tc DETAI DIEM Float Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú GV_PBDT MSGV Smallint Khoá chính, tc GIAOVIEN MSDT Char(6) Khoá chính, tc DETAI DIEM Float Not null Tên bảng Tên cột Kiểu dữ liệu Ghi chú GV_UVDT MSGV Smallint Khoá chính, tc GIAOVIEN MSDT Char(6) Khoá chính, tc DETAI DIEM Float Not null 173 Tên bảng Tên cột Kiểu dữ liệu Ghi chú HOIDONG MSHD Smallint Khoá chính PHONG Smallint TGBD SmallDatetime NGAYHD SmallDatetime Not null TINHTRANG Varchar(30) Not null MSGVCTHD Smallint tc GIAOVIEN Tên bảng Tên cột Kiểu dữ liệu Ghi chú HOIDONG_GV MSHD Smallint Khoá chính, tc HOIDONG MSGV Smallint Khoá chính, tc GIAOVIEN Tên bảng Tên cột Kiểu dữ liệu Ghi chú HOIDONG_DT MSHD Smallint Khoá chính, tc HOIDONG MSDT Char(6) Khoá chính, tc DETAI QUYETDINH Char(10) Ghi chú: tc là tham chiếu Ví dụ: tc GIAOVIEN là tham chiếu đến table GIAOVIEN MỘT SỐ DỮ LIỆU MẪU 1. Table SINHVIEN MSSV TENSV DIACHI LOP SODT 97TH01 Nguyễn Vĕn An 12 NTMK 97TH01 9688543 97TH02 Trần Hùng 13/4 LCT 97TH01 8453443 97TH03 Lê Thuý Hằng 20 Pasteur 97TH01 8544457 97TH04 Ngô Khoa 54/12 LHP 97TH02 8545439 97TH05 Phạm Tài 12 HBT 97TH02 8149023 97TH06 Đinh Tiến 31 THĐ 97TH01 8956123 174 2. Table DETAI MSDT TENDT 97001 Quản lý thư viện 97002 Nhận dạng vân tay 97003 Bán đấu giá trên mạng 97004 Quản lý siêu thị 97005 Xử lý ảnh 3. Table SV_DETAI MSSV MSDT 97TH01 97004 97TH02 97005 97TH03 97001 97TH04 97002 97TH05 97003 97TH06 97005 4. Table HOCHAM MSHH TENHH 1 Phó giáo sư 2 Giáo sư 5. Table GIAOVIEN MSGV TENGV DIACHI SODT MSHH NAMHH 1 Nguyễn Vĕn A 11 NVĐ 8754321 1 1996 2 Trần Thu Trang 56 XVNT 8964334 1 1996 3 Lê Trung 12/5 CMTT 8903561 1 1996 4 Nguyễn Thị Loan 321 BTX 8012864 2 1997 5 Chu Vĕn Tiên 1/60 TVĐ 8157906 2 1997 175 6. Table HOCVI MSHV TENHV 1 KS 2 CN 3 Th.S 4 TS 5 TSKH 7. Table CHUYENNGANH MSCN TENCN 1 Hệ thống thông tin 2 Mạng 3 Đồ họa 4 Công nghệ phần mềm 8. Table GV_HV_CN MSGV MSHV MSCN NAM 1 1 1 1999 1 1 2 1999 1 2 1 1998 2 3 2 1997 3 2 4 1997 4 3 2 1996 9. Table GV_HDDT MSGV MSDT DIEM 1 97001 7 2 97002 8 5 97003 9 4 97004 8.5 3 97005 7 176 10. Table GV_PBDT MSGV MSDT DIEM 1 97005 5 2 97001 7 5 97004 6 4 97003 8.5 3 97002 8 6 97006 9 11. Table GV_UVDT MSGV MSDT DIEM 5 97005 6 2 97005 5 4 97005 5 3 97001 7 4 97001 7 5 97001 8 3 97003 10 1 97003 7 2 97003 7 1 97004 8 2 97004 9 3 97004 5 1 97002 9 4 97002 9 5 97002 6 1 97006 7 2 97006 7 4 97006 7 12. Table HOIDONG MSHD PHONG TGBD NGAYHD TINHTRANG MSGVCTHD 1 002 7:00 30/10/2001 Thật 1 2 102 7:00 30/10/2001 Thử 2 3 003 8:00 31/10/2001 3 177 13. HOIDONG_GV MSHD MSGV 1 1 1 2 1 3 1 4 2 3 2 2 2 5 2 4 3 1 3 2 3 3 3 4 4 3 4 2 4 5 4 4 14. HOIDONG_DT MSHD MSDT QUYETDINH 1 97001 Được 1 97002 Được 2 97003 Không 2 97004 Không 1 97005 Được 3 97001 Không 3 97002 Được 3 97004 Không 4 97003 Không 4 97005 Không 4 97006 Được 178 A. VIEW 1. Ứng với mỗi đề tài cho biết: Tên đề tài, tên sinh viên tham gia, tên giáo viên hướng dẫn, tên giáo viên phản biện. 2. Ứng với mỗi giáo viên cho biết: Tên đề tài hướng dẫn. 3. Ứng với mỗi giáo viên cho biết: Tên đề tài phản biện. 4. Ứng với mỗi hội đồng bảo vệ thử cho biết: Tên đề tài, quyết định được bảo vệ thật không? 5. Ứng với mỗi hội đồng bảo vệ thật cho biết: Tên đề tài, điểm cuối cùng của mỗi đề tài (DIEMTB) 6. Ứng với mỗi đề tài cho biết: Tên đề tài, tên sinh viên, điểm của mỗi giáo viên thuộc hội đồng. 7. Ứng với mỗi hội đồng cho biết: Thời gian, ngày, số lượng đề tài bảo vệ, tên chủ tịch hội đồng. 8. Ứng với mỗi hội đồng cho biết: Tên giáo viên thuộc hội đồng và số điện thoại. 9. Ứng với mỗi giáo viên cho biết: Số đề tài hướng dẫn, số đề tài phản biện, số đề tài là ủy viên. B. STORED PROCEDUREDS VỚI THAM SỐ VÀO 1. Tham số đưa vào là MSGV, TENGV, SODT, DIACHI, MSHH, NAMHH. Trước khi insert dữ liệu cần kiểm tra MSHH đã tồn tại trong table HOCHAM chưa, nếu chưa trả ra giá trị 0. 2. Tham số đưa vào là MSGV, TENGV, SODT, DIACHI, MSHH, NAMHH. Trước khi insert dữ liệu cần kiểm tra MSGV có trùng không, nếu trùng trả về giá trị 0. 3. Giống câu 1 và câu 2 kiểm tra xem MSGV có trùng không . MSHH tồn tại chưa, nếu MSGV trùng trả về 0, nếu MSHH chưa tồn tại trả về 1, ngược lại cho insert dữ liệu. 4. Đưa vào MSDT cũ, TENDETAI mới hãy cập nhật TENDETAI mới với MSDT cũ không đổi nếu không tìm thấy trả về 0 ngược lại cập nhật và trả về 1. 5. Tham số đưa vào MSSV, TENSV mới, DIACHI mới thủ tục dùng để cập nhật sinh viên trên. Nếu không tìm thấy trả về 0, ngược lại cập nhật và trả về 1. 6. Đưa vào MSDT hãy chuyển đổi sao cho với đề tài đó GVHD → GVPB, GVPB → GVHD. Nếu không tìm thấy trả về giá trị 0. 7. Đưa vào TENGV, TENSV. Hãy chuyển đề tài của sinh viên đó cho giáo viên mới hướng dẫn với TENGV là tham số vào. Nếu không tìm thấy, hoặc tìm thấy nhưng không duy nhất thì trả về 0,1. 8. Đưa vào TENSV nếu không vi phạm ràng buộc toàn vẹn về khóa ngoại thì xóa. Ngược lại trả về 0. 179 C. STORED PROCEDUREDS VỚI THAM SỐ VÀO VÀ RA 1. Đưa vào TENHV Trả ra: Số GV thỏa học vị, nếu không tìm thấy trả về 0. 2. Đưa vào MSDT Cho biết: Điểm trung bình của đề tài, nếu không tìm thấy trả về 0. 3. Đưa vào TENGV Trả ra: Số điện thoại của giáo viên, nếu không tìm thấy trả về 0. 4. Đưa vào MSHD Trả ra: Tên chủ tịch hội đồng và số điện thoại, nếu không tìm thấy trả về 0. 5. Đưa vào TENHV Cho biết: Số đề tài hướng dẫn, số đề tài phản biện do giáo viên đó phụ trách. D. TRIGGER 1. Tạo trigger thỏa mãn điều kiện khi xóa một đề tài sẽ xóa các thông tin liên quan. 2. Tạo trigger thỏa mãn điều kiện khi xóa một sinh viên sẽ xóa các thông tin liên quan. 3. Tạo trigger thỏa mãn điều kiện khi xóa một hội đồng sẽ xóa các thông tin liên quan. 4. Tạo trigger thỏa mãn ràng buộc là khi đổi một mã số đề tài (MSDT) sẽ thay đổi các thông tin liên quan. 5. Tạo trigger thỏa mãn ràng buộc là khi đổi một mã số giáo viên (MSGV) sẽ thay đổi các thông tin liên quan. 6. Tạo trigger thỏa mãn ràng buộc là khi đổi một mã số hội đồng (MSHD) sẽ thay đổi các thông tin liên quan. 7. Tạo trigger thỏa mãn ràng buộc là một hội đồng không quá 10 đề tài. 8. Tạo trigger thỏa mãn ràng buộc là một giáo viên không thể vừa phản biện vừa hướng dẫn 1 đề tài. 9. Tạo trigger thỏa mãn ràng buộc là 1 đề tài không quá 3 sinh viên. 10. Tạo trigger thỏa mãn ràng buộc là một giáo viên muốn có học hàm PGS phải là tiến sĩ. 11. Tạo trigger thỏa mãn ràng buộc là một giáo viên muốn có học hàm GS phải là tiến sĩ khoa học. 12. Viết trigger đảm bảo không mâu thuẫn trên nĕm nhận học vị và nĕm nhận học hàm. E. BACKUP & RESTORE 1. Sau khi đã tạo CSDL. a. Backup CSDL ra đĩa mềm hay đĩa cứng. b. Restore CSDL trở lại như ban đầu. Khi làm nhớ nhận xét các yếu tố sau: 180  Kích thước CSDL và CSDL Backup.  Các đối tượng CSDL và CSDL Backup.  Thời gian Backup và Restore.  Backup theo phương pháp nào? 2. Cũng như câu trên nhưng:  Backup và Restore trên đĩa cứng.  Nhớ nhận xét như câu 1. 3. Di chuyển CSDL: Có thể dùng phương pháp: a. Backup (đã biết ở câu 1, 2) b. Tạo tập tin *.spl  Có thể taọ trực tiếp bằng Query Analyzer.  Có thể tạo ngược nhờ Enterprise. 181 TÀI LIỆU THAM KHẢO [1]. Nguyễn Thiên Bằng, “Giáo trình SQL Server 2000”, NXB Lao động - Xã hội, 2004. [2]. Đoàn Thiện Ngân, “Lập trình SQL cĕn bản”, NXB Lao động - Xã hội, 2003. [3]. Phạm Hữu Khang, “Lập trình ứng dụng chuyên nghiệp SQL Server 2000”, NXB Lao động - Xã hội, 2005. [4]. Nguyễn Thiện Tâm, Trần Xuân Hải, “Giáo trình SQL SERVER 2000”, NXB Đại học Quốc gia TPHCM, 2006. [5]. www.sinhvienit.Net [6]. Nguyễn Gia Tuấn Anh, Trương Châu Long, “Bài tập & Bài giải tham khảo SQL SERVER 7.0”, NXB Thanh Niên, 2003. [7]. Marcilina S. Garcia, Jamie Reding, Edward Whalen, Steve Adrien DeLuca, SQL Server 2000 Administrator’s Companion, Microsoft Press, 2000.