Bai Tap Csdl 2 New

Bộ môn Tin học

Đậu Hải Phong

BÀI TẬP THỰC HÀNH MÔN CƠ SỞ DỮ LIỆU 2 Người biên tập: Đậu Hải Phong

Đề tài 1: Xây dựng và quản trị cơ sở dữ liệu hệ thống quản lý nhập xuất tồn hàng hóa. Một công ty X cần quản lý việc nhập xuất tồn hàng hóa được mô tả như sau: - Tất cả các hàng hóa đều được đánh mã để tiện theo dõi. Các hàng hóa này được phân loại thành những nhóm hàng. - Các hàng hóa được nhập từ các nhà cung cấp thông qua hóa đơn nhập (hình 1). - Hàng hóa sẽ được bán cho khách hàng thông qua hóa đơn xuất (hình 2). - Các thông tin Nhà cung cấp, Khách hàng đều được ghi lại để tiện theo dõi. - Hàng quý Nhân viên công ty đi kiểm kê để tính số dư cuối kỳ: quý, tên hàng, số lượng, thành tiền.

Số HĐ:………… Ngày:…………..

Số HĐ:………… Ngày:…………..

HÓA ĐƠN NHẬP

HÓA ĐƠN XUẤT

Tên nhà cung cấp:……………………... Địa chỉ: ………………………………...

Tên khach hang:……………………... Địa chỉ: ………………………………...

STTTên hàngSố lượngĐơn giáThành tiềnTổng tiền:……... Người lập

STTTên hàngSố lượngĐơn giáThành tiềnTổng tiền:……... Người lập

Hình 1: Mẫu hóa đơn nhập hàng

Hình 2: Mẫu hóa đơn xuất hàng

Yêu cầu: 1. Hãy thiết kế và cài đặt cơ sở dữ liệu cho Hệ thống quản lý nhập xuất tồn. 2. Thực hiện các bài tập theo các tuần.

Bài tập Cơ sở dữ liệu 2

1

Bộ môn Tin học

Đậu Hải Phong

Tuần 1 1. Phân tích, thiết kế bài toán quản lý Nhập – Xuất – Tồn. 2. Tạo nhóm Server, Đăng ký Server, Tạo Database, Tạo bảng,…trong EM. 3. Hướng dẫn sử dụng Query Analyzer: Kết nối, hủy kết nối, Ghi lại kết quả thành 1 tệp, các dạng hiển thị kết quả, một số lựa chọn, chuyển đổi database, ẩn hiện cây database, một số phím tắt,… 4. 4. Cách sử sựng Book Online 5. Thực hiện một số câu truy vấn đơn giản: CSDL Northwind: cho biết danh sách sản phẩm đang bán, Danh sách khách hàng, Danh sách khách hàng ở USA, Chi tiết hóa đơn bán trong năm 1990. Tuần 2: 1. Tạo một cơ sở dữ liệu với tên là mã sinh viên của mình. (VD: A00820) 2. Tạo các bảng dữ liệu đã được thiết kế với CSDL Nhập Xuất Tồn. 3. Tạo một kiểu dữ liệu tên là ‘kieudienthoai’ có kiểu char(20) và cho phép NULL. 4. Tạo một bảng dữ liệu có tên là tblTest( ma int, ten varchar(30) not null) và thực hiện các công việc sau trên bảng tblTest: a. Thêm một trường email với kiểu Varchar(50). b. Thêm trường dienthoai với kiểu dữ liệu vừa tạo ‘kieudienthoai’ c. Sửa lại trường id là kiểu nguyên tự tăng 1 và bắt đầu từ 1 (IDENTITY). d. Thực hiện chèn thêm 2 bản ghi bất kỳ vào bảng tblTest và hiện thị để xem. 5. Viết một đoạn lệnh để kiểm tra đã tồn tại bảng tblHanghoa chưa. Nếu có rồi thì xóa đi để tạo tại. Sau đó thực hiện chèn hai bản ghi vào bảng. Ghi lại đoạn lệnh nay thành một tệp riêng với tên ‘doanma.sql’. 6. Dùng OSQL để thự hiện file ‘doanma.sql’. 7. Viết một đoạn mã lệnh T- SQL để thực hiện hiển thị danh sách nhân sự trong bảng Employees trong Northwind. Nếu country là USA thì hiển thị Mỹ, UK -> Anh, VN -> Việt Nam. 8. Xóa hết dữ liệu ở bảng tblTest đi bằng lệnh DELETE. Sau đó thêm 1 bản ghi mới, xem lại kết quả. 9. Xóa hết dữ liệu ở bảng tblTest đi bằng lệnh TRUNCATE. Sau đó thêm 1 bản ghi mới, xem lại kết quả. So sánh sự khác nhau. 10. Sửa lại địa chỉ email [email protected] thành [email protected] Tuần 3 1. Thêm ràng buộc Default là ngày hiện tại vào ngày trong bảng tblHoadonnhap và bảng tblHoadonxuat. Nhập thử dữ liệu. 2. Thêm ràng buộc check cho trường điện thoại sao cho có dạng 2 số đầu là mã vùng đến dấu chấm và 7 số sau là số điện thoại nhà. Nhập thử dữ liệu để kiểm tra. 3. Xóa ràng buộc vừa tạo ở câu 2 đi. 4. Xóa bảng tblLoaihang đi và tạo đầy đủ khóa chính cho bảng. Bài tập Cơ sở dữ liệu 2

2

Bộ môn Tin học

Đậu Hải Phong

5. Thêm ràng buộc khóa chính cho các bảng trong CSDL. 6. Thêm đầy đủ khóa ngoại cho các bảng. 7. Hãy tự lấy các ví dụ để trả lời các câu hỏi sau về khóa ngoại: a. Có thêm được vào bảng con những bộ dữ liệu mà chưa có ở bảng cha? b. Nếu dùng ON DELETE, ON CASCADE để tham chiếu thì điều gì xảy ra? c. Khi bảng con đã có dữ liệu sau đó mới tạo ràng buộc thì sao? d. Có xóa được bảng cha, con khi vẫn còn ràng buộc? e. Khi muốn xóa bảng cha khi còn ràng buộc thì sao? 8. Thêm trường Số chứng minh thư nhân dân cho bảng tblNhanvien và tạo ràng buộc duy nhất cho trường này. 9. Tạo giá trị mặc định có giá trị là ‘khong ro’. Sau đó gán tên mặc định đó cho trường tenhang trong tblHanghoa. 10. Tạo một Rule chỉ cho phép ngày nhỏ hơn ngày hiện tại. Sau đó gán cho trường ngày trong bảng tblHoadonnhap, tblHoadonxuat. Tuần 4 1. Tạo một index không cho phép người sử dụng nhập số điện thoại, email,… trùng nhau của nhà cung cấp, khách hàng. 2. Tự lấy ví dụ để cho biết sự khác nhau về việc sắp xếp dữ liệu giữa: Clustered và NonClustered. 3. Tạo Clustered cho mã hàng trong bảng tblHanghoa. Cho biết điều gì xảy ra? Tại sao? 4. Tạo NonClustered Index cho cột ngày trong bảng tblHoadonnhap, tblHoadonxuat. 5. Xóa Index Unique cho cột điện thoại của bảng tblNhacungcap. 7. Cho biết thông tin chi tiết các hóa đơn nhập được thực hiện trong ngày 01/01/2005. 8. Cho biết thông tin chi tiết các hóa đơn xuất cho khách hàng có tên “Bùi Tiến Dũng”. 9. Cho biết thông tin khách hàng và mã các hóa đơn nhập nếu có. 10. Cho biết thông tin: mã, họ tên, số điện thoại của tất cả Nhà cung cấp và Khách hàng. 11. Cho biết những Nhà cung cấp chưa cung cấp hàng lần nào. 12. Hiển thị ngày nhập hàng gần nhất. 13. Chuyển toàn bộ thông tin tương ứng trong bảng tblNhacungcap có mã bắt đầu là ‘K’ sang bảng tblKhachhang. 14. Cho biết số lần mua hàng của khách có mã là ‘DHP’ 15. Viết một đoạn lệnh sử dụng con trỏ để hiển thị danh sách khách hàng. Nếu khách hàng đã mua 10 lần thì thông báo được khuyến mại 100 USD, mua 20 lần khuyến mại 200 USD.

Bài tập Cơ sở dữ liệu 2

3

Bộ môn Tin học

Đậu Hải Phong

Tuần 5 1. Cho biết doanh số xuất của từng mặt hàng trong tháng 6 tháng đầu năm nay. 2. Cho biết mặt hàng có doanh số xuất lớn nhất. 3. Cho biết mặt hàng có tổng doanh số lớn hơn 100. 4. Cho biết đơn giá nhập trung bình của từng mặt hàng nhập. 5. Cho biết báo cáo chi tiết về số lượng theo từng loại hàng, từng mặt hàng, tổng tất cả. 6. Cho biết thành tiền của từng mặt hàng theo từng hóa đơn và tổng tiền của từng hóa đơn. 7. Tạo view: Hiện thông tin về hoá đơn nhập: Ngày, diễn giải, tổng tiền trong tháng hiện tại được sắp xếp theo ngày. 8. Tạo view: Cho biết thông tin nhân viên chưa nhập hàng bao giờ. 9. Tạo và mã hóa view: Cho biết thông những mặt hàng chưa được xuất trong năm hiện tại. 10. Tạo view: cho biết danh sách mặt hàng thuộc loại ‘L01’, có sử dụng WITH CHECK OPTION. Sau đó chèn thử một bản ghi vào view này. 11. Tạo một giao dịch đảm bảo thực hiện thành công 2 công việc: Xóa loại hàng ‘L01’ trong bảng tblLoaihang; thêm 1 bản ghi vào tblHanghoa. Nếu một trong bị lỗi thì hủy cả 2 công việc đi và thông báo lỗi. 12. Xây dựng giao dịch đảm bảo việc xuất của một hàng hóa bất kỳ không vượt quá số lượng tồn còn lại. Tuần 6: 1. Dùng BCP để xuất bảng tblHanghoa ra thành file text Hanghoa.txt. 2. Dùng Export bảng tblHanghoa ra database của Access. Sau đó truy xuất tất cả thông tin bảng tblLoaihang trong SQL Server và bảng tblHanghoa trong Access. 3. Viết thủ tục chèn dữ liệu cho các bảng dữ liệu (có dùng các giá trị mặc định) 4. Viết thủ tục hiển thị số lượng tồn của một hàng hóa bất kỳ. 5. Viết hàm trả về số lượng tồn của một hàng bất kỳ. 6. Viết hàm trả về danh sách lượng hàng tồn của một loại hàng bất kỳ. 7. Viết hàm cho biết thông tin chi tiết các hóa đơn bán trong một khoảng thời gian bất kỳ. Tuần 7: 1. Giả sử hai bảng tblHanghoa và tblLoaihang chưa có tham chiếu với nhau. Hãy xây dựng trigger để đảm bảo tính toàn vẹn dữ liệu giống như đã tạo tham chiếu khóa ngoại(không được cập nhật vào bảng con giá trị ở bảng cha chưa có, không được xóa cha khi vẫn còn con, không được sửa cha khi vẫn còn con). 2. Dựa trên ý tưởng trên hãy xây dựng trigger giống ràng buộc khóa ngoại có ON DELETE, ON CASCADE. 3. Giả sử có 2 bảng tblHoadonnhap, tblHoadonnhapgoc có cấu trúc giống hệt nhau. Nhưng bảng tblHoadonnhap chỉ lưu trữ các bản ghi trong tháng hiện tại, bảng tblHoadonnhapgoc – lưu trữ tất cả hóa đơn nhập. Hãy xây dựng trigger đảm bảo các tính Bài tập Cơ sở dữ liệu 2

4

Bộ môn Tin học

Đậu Hải Phong

chất sau: bảng tblHoadonnhap chỉ chấp nhập hóa đơn được thực hiện trong tháng hiện tại. Nếu không phải tháng hiện thì không cho phép nhập và thông báo cho người sử dụng biết. Sau khi cập nhật: INSERT, UPDATE, DELETE ở bảng tblHoadonnhap thì cũng có thao tác tương ứng với bảng tblHoadonnhapgoc. 4. Xây dựng trigger không cho phép người sử dụng xuất quá số lượng tồn. 5. Giả sử cơ sở dữ liệu về Quản lý nhập xuất tồn có các nhóm, người sử dụng như sau: - Các Account trên Window 2000: Admin, Phong, Tuan có quyền quản trị. - Tất cả mọi người chỉ có quyền đọc tất cả các bảng. - Các user: sb, sc, sd không có quyền INSERT, UPDATE, DELETE bảng hóa đơn nhập, chi tiết hóa đơn nhập, hóa đơn xuất, chi tiết hóa đơn xuất. Hãy tạo các nhóm, role, user để đảm bảo quyền trên. Tuần 8: 1. Sử dụng công cụ PLATIUM ERWIN thiết kế và tạo ra CSDL. 2. Tạo một ứng dụng bằng ASP kết nối với SQL SERVER 2000 để tạo các giao diện cập nhật cho các bảng: Hóa đơn nhập, Chi tiết hóa đơn nhập. 3. Xây dựng giao diện cho phép người sử dụng xem lượng tồn theo quý và theo loại hàng. Tuần 9: 1. Tổ chức thi điều kiện trên 2 giờ thực hành. 3. Ôn tập cho thi thực hành trên 3 giờ lý thuyết.

Bài tập Cơ sở dữ liệu 2

5

Bộ môn Tin học

Đậu Hải Phong

Phần 2: Ôn tập T-SQL. 1. Thực hiện các công việc sau trên Query Analyzer: a. Tạo CSDL với tên MSV(với mã sinh viên của mỗi người) b. Tạo các bảng dữ liệu trên với kiểu dữ liệu và các ràng buộc tương ứng c. Thêm cột AutoID vào bảng Nhân viên sau đó thêm một số bản ghi và cho hiển thị xem dữ liệu. d. Xóa cột AutoID trong bảng Nhân viên đi. e. Hiển thị các hóa đơn nhập trong khoảng từ 10 ngày trước trở lại đây. f. Cho biết danh sách các mặt hàng của nhà cung cấp có mã là =’DHP’. g. Cho biết tổng tiền của hóa đơn xuất ‘HD001’. h. Cho biết thông tin về hóa đơn nhập và tổng tiền theo từng hóa đơn. i. Cho biết tên nhà cung cấp, tổng tiền theo từng nhà cung cấp. j. Cho biết những nhà cung cấp có tổng tiền lớn hơn 1000. k. Cho biết những hóa đơn xuất trong tháng 02 năm 2004. l. Tìm những nhà cung cấp có họ là ‘Bill’. m. Hãy cho biết số lượng nhập,xuất, tồn theo từng loại hàng hóa trong khoảng thời gian từ 01/01/2005 đến 31/03/2005. (sử dụng bảng tạm) n. Hãy sửa lại giá của tất cả mặt hàng ‘H01’ bằng giá trung bình của mặt hàng ‘H02’. o. Cho biết tổng tiền của từng hóa đơn và tổng tiền của tất cả hóa đơn. (WITH CUBE). p. Cho biết tổng tiền theo từng loại hàng hóa theo từng hóa đơn và tổng tiền của tất cả hàng hóa đó (WITH ROLLUP). q. Cho biết thông tin chi tiết từng hóa đơn và cho biết tổng từng cột số lượng, đơn giá, thành tiền. (COMPUTE) Phần 3: Tạo View và tệp chỉ mục 1. Tạo View: a. Cho biết thông tin về người cung cấp: Tên nhà cung cấp, điện thoại b. Hiện thông tin về hoá đơn nhập: Ngày, diễn giải, tổng tiền trong tháng hiện tại. Và được sắp xếp theo ngày. c. Cho biết thông tin nhân viên chưa nhập hàng bao giờ. d. Cho biết thông những mặt hàng chưa được xuất trong năm hiện tại. Bài tập Cơ sở dữ liệu 2

6

Bộ môn Tin học

Đậu Hải Phong

e. Hiển thị thông tin hoá đơn xuất: ngày, số hđ, mã hàng, số lượng trong tháng 11. (không sử dụng With Check Option) f. Hiển thị thông tin hoá đơn xuất: ngày, số hđ, mã hàng, số lượng trong tháng 11. (có sử dụng With Check Option) g. Thực hiện thao tác cập nhật dữ liệu vào trường ngày=’29/03/2004’ cho một mã hoá đơn bất kỳ trong view của câu e, f. sau đó cho biết điều gì xảy ra. h. Tạo lại view cho câu a,b nhưng đã được mã hóa. (With Encription) i. Xem lại nội dung view của câu a đã mã hoá và câu a chưa mã hoá. 2. Tạo tệp chỉ mục: a. Tạo 2 bảng: tblTest1, tblTest2 giống nhau gồm có các trường sau: ids int(4) , names varchar(20). Có cùng nội dung. b. Tạo một Clustered index theo names cho tblTest1. c. Tạo một NonClustered index theo names cho tblTest2. d. Xem lại nội dung 2 bảng. Chèn thêm 1 bản ghi mới cho 2 bảng. Sau đó xem lại và hãy cho biết sự khác biệt. e. Tạo một Clustered index cho tên nhà cung cấp. Sau đó xem lại bảng Nhà cung cấp. f. Tạo một NonClustered index cho ngày nhập. Sau đó xem lại bảng hoá đơn nhập. g. Tạo một index không cho phép người sử dụng nhập số điện thoại trùng nhau của nhà cung cấp. h. Tạo Clustered cho mã nhà cung cấp. Điều gì diễn ra? Tại sao? i. Xoá Clustered, NonClustered index vừa tạo đi. Phần 4: Khóa và ràng buộc dữ liệu: 1. Giả sử bảng loại hàng chưa có trường làm khóa. Hãy tạo trường maloai làm khóa cho bảng loại hàng. 2. Hãy thêm trường email vào bảng Nhà cung cấp với điều kiện email là duy nhất. 3. Giả sử trường Username trong bảng nhân viên chưa duy nhất. Hãy tạo ràng buộc duy nhất cho trường Username. 4. Tạo ràng buộc kiểm tra tất cả các trường số lượng, đơn giá trong 2 bảng chi tiết hóa đơn xuất và chi tiết hóa đơn nhập phải lớn hơn 0. 5. Tạo giá trị mặc nhiên cho trường ngày nhập, ngày xuất. 6. Thiết lập quy tắc (Rules) cho ngày nhập và ngày xuất chỉ cho phép nhập và xuất trong ngày hiện tại. 7. Hãy nhập 1 bản ghi trái quy luật trên. Cho biết điều gì xảy ra? 8. Thực hiện thao tác xóa Rule vừa tạo. 9. Tạo giá trị mặc nhiên sau đó gán cho trường đơn giá của hóa đơn nhập và hóa đơn xuất.

Bài tập Cơ sở dữ liệu 2

7

Bộ môn Tin học

Đậu Hải Phong

10.Nhập một bản ghi nhưng không đưa giá trị vào trường gán giá trị mặc nhiên. Cho biết điều gì đã xảy ra? 11.Xóa giá trị mặc nhiên vừa tạo. Phần 5: Kịch bản (Script) và bó (Batch) 1. Tạo 1 Script thực hiện các công việc sau: a. Kiểm tra xem đã có database là tmp_Masv. Nếu có rồi thì xoá đi và tạo mới. (dùng bảng sysdatabases) b. Tạo 2 bảng: Test1(id1,name1); Test2(id2,name2) c. Nhập mỗi bảng 2 bản ghi 2. Lưu lại đoạn mã này với tên là Script.txt (để làm việc với câu sau) 3. Xoá Database vừa tạo đi. 4. Tạo một kịch bản tính và in ra số lượng: Nhập, Xuất, Tồn những hàng hóa của loại hàng có mã ‘L01’ 5. Hãy tạo một kịch bản cho biết số lượng nhập,xuất, tồn theo từng loại hàng hóa trong khoảng thời gian từ 01/01/2005 đến 31/03/2005. 6. Tạo 1 kịch bản hiện thị ra 10 mặt hàng bán chạy nhất trong năm hiện tại. 7. Tạo một kịch bản tính và in ra tổng tiền nhập, xuất của 1 hàng hoá có mã là H001. 8. In ra số lượng bản ghi của bảng tblHoadonnhap (dùng hàm @@rowcount) 9. In ra tên Server đang dùng. (@@SERVERNAME) 10. Tạo ra 1 bảng có tên là Test1(ids int identity(1,1), names varchar(30)). Sau đó nhập 3 bảng ghi, xoá bản ghi cuối cùng đi, lại thêm 1 bản ghi. Hãy cho biết số ids cuối cùng là số mấy (dùng hàm @@identity). 11. Giả sử ta đã có 1 script được lưu ở câu 2. Hãy thực hiện script đó ở cửa sổ DOS với câu lệnh osql –U – P –d –Q –i 12. Tạo 1 biến @tenbang=tblLoaihang. @sSQL=’SELECT * FROM ’ + @tenbang. Sau đó thực hiện biến @sSQL bằng câu lệnh EXEC. Phần 6: Thủ tục thường trú (Stored Procedure - SP) 1. Tạo các thủ tục nhập dữ liệu cho tất cả các bảng với các tham số truyền từ bên ngoài vào. (sp_Insert_tenbang ). Nếu tham số truyền vào cho khoá không được Null hoặc rỗng nếu sai thông báo lỗi. 2. Tạo thủ tục cập nhật dữ liệu cho tất cả các bảng với các tham số truyền vào như sau: sp_Update_tenbang ,<>

Bài tập Cơ sở dữ liệu 2

8

Bộ môn Tin học

Đậu Hải Phong

Phần 6: Giao dịch và khóa (tiếp) 1. Viết một đoạn chương trình (script) để xóa loại hàng có mã ‘L01’ vào bảng tblLoaihang, chèn thêm 1 hàng hóa vào bảng tblHanghoa. Nếu thực hiện thành công thì thông báo đã hoàn thành. Nếu 1 trong 2 thao tác không thành công thì chỉ rõ thao tác đó và quay trở về trạng thái ban đầu. (tham khảo trong @@ERROR) 2. Cập nhật một hóa đơn xuất có tiến trình như sau: cập nhật tiêu đề vào bảng tblHoadonxuat, tiếp theo cập nhật vào bảng tblChitietHDX. Nếu số lượng xuất lớn hơn số lượng tồn(tính đến thời điểm ngày viết hóa đơn xuất) thì chỉ nhập bằng số lượng tồn. Nếu có lỗi quay lại từ đầu. 3. Khóa: xem ví dụ trong SET TRANSACTION ISOLATION LEVEL. Phần 7: Bẫy lỗi – Trigger (tiếp) 1. Tạo 1 Trigger để sau khi INSERT vào bảng tblHanghoa xong thì thông báo đã hoàn thành. (FOR AFTER).Xem lại bảng Hanghoa. 2. Tạo 1 Trigger để sau khi INSERT vào bảng tblHanghoa xong thì thông báo đã hoàn thành. (INSTEAD OF).Xem lại bảng Hanghoa. 3. Cho biết kết quả có già khác nhau?? 4. Giả sử trong trường hợp phiếu nhập và phiếu xuất của chúng ta trong 1 ngày có rất nhiều. Do đó ta dùng cách có 1 bảng gốc và một bảng lưu các chúng từ theo ngày. Hay nói một cách khác là 2 bảng này có quan hệ 1-1. Xuất phát từ những yêu cầu đó ta xây dựng 1 trigger để khi INSERT, UPDATE, DELETE thì cũng thay đổi ở bảng còn lại. Chú ý: nếu các thao tác không thực hiện bị lỗi thì quay lại từ đầu. 5. Tạo 1 trigger kiểm tra việc xóa của bảng Loaihang nếu còn loại đó ở bảng Hanghoa thì báo lỗi và quay lại luôn. (giả sử chưa có constraint) 6. Tạo 1 trigger: nếu cập nhật vào trường maloai mà mã loại đó đang tồn tại trong bảng Hanghoa thì báo lỗi và quay lại ban đầu. Phần 8: Các chuyển tác và truy vấn phân tán Phần 9: Một số phương pháp truyền dữ liệu – BCP & DTS 1. Giả sử có 1 file text chứa các dòng danh sách về mã loại, tên loại được cách nhau bằng dấu tab. Hãy import file text này vào bảng tblLoaihang. 2. Hãy Export bảng Hàng hoá ra file có tên là tblHanghoa.txt o C:\ 3. Thực hiện Export toàn bộ bảng của CSDL sang Access. (DTS Wizard) 4. Thực hiện Import một số bảng ở Access vào CSDL của mình. (DTS Wizard) 5. Thực hiện Import từ CSDL của người này sang người khác(Lưu lại package này). 6. Mở phần Data Transformation Services\Local Packages. Xem lại package vừa tạo. 7. Tương tự như Pagekage ở câu 6. Hãy tạo 1 pagekage để import từ 1 Sheet từ Excel vào database của mình. Bài tập Cơ sở dữ liệu 2

9

Bộ môn Tin học

Đậu Hải Phong

Phần 10: Bảo mật – Security 1. Tạo ra 1 user với tên là mã sinh viên của mình sau đó gán quyền truy cập, quyền thực thi cho User đó trên 1 số bảng nào đó. 2. Cho User đó có quyền hoặc không có quyền tạo thủ tục, view,… 3. Sử dụng Enterprise để tạo User sau đó xem các Role trong Server, trong CSDL. 4. Tạo ra 1 Role mới và gán quyền cho Role. Sau đó đưa User vào làm thành viên của Role. Phần 11: Sao lưu và Phục hồi dữ liệu – Backup and Restore 1. Phần 12: Thiết lập công việc tư động 1. Tạo thông tin người quản lý(operator) để có thông tin gì thì sẽ thông báo. C1: Managerment\Operator\RC – New Operator C2: sp_add_operator… Một số thủ tục liên quan: sp_help_operator, sp_update_operator, sp_delete_operator 2. Tạo Jobs và Task: C1: Managerment\Jobs\RC – New Job C2: Cần thực hiện 3 thủ tục sau: sp_add_job, sp_add_job_step, sp_add_jobschedule. Một số thủ tục liên quan: sp_delete_job, sp_update_job, … Phần 13: Hướng dẫn bài tập lớn: Lập trình VB với SQL Server 2000 Kiến thức cần lắm rõ: 1. Kết nối với SQL Server: ‘Tạo xâu kết nối Dim gsConn as String gsConn = "Provider=SQLOLEDB.1;Persist Security=False; UID=sa;PWD=sa;Initial Catalog=Library;Server=Datasrv;" Trong đó: Provider=SQLOLEDB.1; – Nguyên tắc kết nối với SQL Server Persist Security=False; - Kết nối thông qua Account của SQL Server; UID=sa; - User=sa; PWD=sa; - Pass=sa nếu không có thì thôi. Initial Catalog=Library;- Tên database là Library; Server=Datasrv; - Tên Server; ‘Khai báo biến kết nối Public Conn as ADODB.Connection ‘Khởi tạo kết nối với CSDL Set Conn = New ADODB.Connection ‘Thực hiện kết nối Conn.Open gsConn 2. Thực thi các công việc với SQL Server: Bài tập Cơ sở dữ liệu 2

10

Bộ môn Tin học

Đậu Hải Phong

-

Thực hiện kết nối với bảng dữ liệu kết quả trả về là các bản ghi: Dim sSQL String Dim Rs as ADODB.Recordset Set Rs = New ADODB.Recordset ‘Thực hiện mở bảng sSQL=”SELECT * FROM tblHanghoa” – Mở với bảng hàng hóa hoặc sSQL=”SELECT * FROM vw_Nhapthang4” – Mở view tên vw_Nhapthang4 hoặc sSQL=”SELECT * FROM fnt_Tinhton(‘L01’)” – Mở bẳng do hàm fnt_Tinhton tạo ra với tham số truyền vào là ‘L01’ Rs.Open sSQL, Conn,2,2 =>Sau khi đã mở thành công ta có một số thao tác sau: + Rs.RecordCount – Số lượng bản ghi + Rs.MoveFirst – Nhảy về bản ghi đầu tiên + Rs.MoveLast – Nhảy về bản ghi cuối cùng + Rs.MoveNext – Nhảy về bản ghi tiếp theo + Rs.MovePrevious – Nhảy về bản ghi trước đó + Rs(“Mahang”) – Lấy giá trị mã hàng trong bản ghi hiện tại + Rs.EOF - Trả về True nếu đang ở bản ghi cuối, False chưa cuối. + Rs.BOF - Trả về True nếu đang ở bản ghi đầu, False chưa đầu. + Rs.Close – Đóng bảng đang mở. + Set Rs = Nothing - Gán Rs về trạng thái trống. -

Thực hiện các phát biểu INSERT, DELETE, UPDATE, CREATE, STORE PROCEDURE,… sSQL=”INSERT INTO tblHanghoa(mahang,tenhang,maloai) sSQL=sSQL+” VALUES (‘H01’,’Hang so 1’,’L01’)” hoặc sSQL=”sp_Insert_Hanghoa ‘H01’,’Hang so 1’, ‘L01’ ” ‘ Thực hiện xâu sSQL Conn.Execute sSQL

* Kinh nghiệm thực thi với xâu sSQL trong VB - Ấn F9 vào các dòng lệnh thực thi sSQL, sau đó cho VB thực hiện chương trình sẽ dừng ở dòng thực thi. - Ấn Ctrl + G ở cửa sổ Immediate gõ ?sSQL để hiển thị xâu sSQL và cho vào Query Analyzer để thực hiện thử xem đã đúng chưa? Nếu thực hiêun được ở Query Analyzer thì thực hiện được ở VB. 3. Yêu cầu một số chức năng chính ở trong bài tập lớn. Hệ thống: Backup database, Restore database, (viết bằng thủ tục), Thoát khỏi c.trình. Bài tập Cơ sở dữ liệu 2

11

Bộ môn Tin học

Đậu Hải Phong

Cập nhật: các form cập nhật cho các bảng(sử dụng các store procedure, trigger,..) Báo cáo: các form liệt kê các kết quả tính toán dữ liệu ví dụ như tình trạng nhập xuất tồn,.. sử dụng các view, funcction,.. Phân quyền: Tạo User, Phân quyền trong Server, Database,…(chú ý: phải có form login để truy cập vào các User đã tạo,..) Trợ giúp: nếu có

PHỤ LỤC: Một số Store Procedure: STT

Tên Store Procedure

Ý nghĩa

1

sp_bindrule ‘Tên Rule’,’Tên bảng.Tên trường’

Gán rule cho trường

2

Loại bỏ rule khỏi trường

4

sp_unbindrule ’Tên bảng.Tên trường’ sp_binddefault ‘Tên default’, ’Tên bảng.Tên trường’ sp_unbinddefault ’Tên bảng.Tên trường’

5

sp_helptext

Xem nội dung tên đối tượng

3

Gán Default to trường Loại bỏ Default khỏi trường

6

1. Bảng Loại hàng: Tên trường Maloai Tenloai

Kiểu

Độ rộng

Khoá

3 30

PK

Độ rộng

Khoá

6 3 50

PK FK

Độ rộng

Khoá

10 30 100

PK

Varchar Varchar

Ghi chú

2. Bảng Hàng hóa: Tên trường Mahang Maloai Tenhang

Kiểu Varchar Varchar Varchar

Ghi chú

3. Bảng Nhà cung cấp: Tên trường MaNCC TenNCC Diachi

Kiểu Varchar Varchar Varchar

Bài tập Cơ sở dữ liệu 2

Ghi chú

12

Bộ môn Tin học Dienthoai

Đậu Hải Phong

Varchar

15

4. Bảng Nhân viên : Tên trường MaNV TenNV UseName Password

Kiểu

Độ rộng

Khoá

10 30 20 20

PK

Độ rộng

Khoá

10

PK

10 10 100

FK FK

Độ rộng

Khoá

10 6 6 7,1

PK PK,FK

Độ rộng

Khoá

10

PK

10 100

FK

Độ rộng

Khoá

10 6

PK PK,FK

Varchar Varchar Varchar Varchar

Ghi chú

5. Bảng Hóa đơn nhập: Tên trường MaHD Ngay MaNCC MaNV Diengiai

Kiểu Varchar Datetime Varchar Varchar Varchar

Ghi chú

6. Bảng Chi tiết hóa đơn nhập: Tên trường MaHD Mahang Soluong Dongia

Kiểu Varchar Varchar SmallInt Float

Ghi chú

7. Bảng Hóa đơn xuất: Tên trường MaHD Ngay MaNV Diengiai

Kiểu Varchar Datetime Varchar Varchar

Ghi chú

8. Bảng Chi tiết hóa đơn xuất: Tên trường MaHD Mahang

Kiểu Varchar Varchar

Bài tập Cơ sở dữ liệu 2

Ghi chú

13

Bộ môn Tin học Soluong Dongia

SmallInt Float

Bài tập Cơ sở dữ liệu 2

Đậu Hải Phong 6 7,1

14