Hướng dẫn thực hành: Thực hành hệ quản trị cơ sở dữ liệu

Nội dung text: Hướng dẫn thực hành: Thực hành hệ quản trị cơ sở dữ liệu

1. Hướng dẫn thực hành Hệ CSDL Hướng Dẫn Thực Hành Thực hành Hệ QTCSDL Hướng dẫn: • Bài tập thực hành được chia làm nhiều Module • Mỗi Module được thiết kế cho thời lượng là 3 tiết thực hành tại lớp với sự hướng dẫn của giảng viên. • Tùy theo số tiết phân bổ, mỗi tuần học cĩ thể thực hiện nhiều Module. • Sinh viên phải làm tất cả các bài tập trong các Module ở tuần tương ứng. Những sinh viên chưa hồn tất phần bài tập tại lớp cĩ trách nhiệm tự làm tiếp tục ở nhà. • Các bài cĩ dấu (*) là các bài tập nâng cao dành cho sinh viên khá giỏi. Page 1 Khoa Cơng Nghệ Thơng Tin
2. Hướng dẫn thực hành Hệ CSDL Module 1 Nội dung kiến thức thực hành: +Thực hành ngơn ngữ SQL: - ngơn ngữ định nghĩa dữ liệu: CREATE TABLE - ngơn ngữ thao tác dữ liệu: INSERT, UPDATE, DELETE +Thơng qua việc cài đặt cơ sở dữ liệu trên DBMS,SV hiểu rõ hơn về mơ hình ER, mơ hình quan hệ Giả sử cần quản lý một cửa hàng bán sách. Mỗi cuốn sách phân biệt với nhau thơng qua mã sách, mã sách xác định các thơng tin: tên sách, tác giả, đơn giá, số lượng tồn. Mỗi cuốn sách chỉ thuộc về một nhĩm sách. Một nhĩm sách cĩ thể cĩ nhiều cuốn sách hoặc khơng cĩ cuốn nào. Thơng tin về nhĩm sách: mã nhĩm để phân biệt nhĩm sách này với nhĩm sách khác, tên nhĩm. Khi cĩ sách được bán, nhân viên lập hĩa đơn để lưu trữ thơng tin bán sách. Mỗi hĩa đơn cĩ một số hĩa đơn duy nhất xác định nhân viên lập hĩa đơn và ngày lập hĩa đơn. Mỗi nhân viên cĩ một mã nhân viên duy nhất xác định các thơng tin như họ lĩt, tên, phái, ngày sinh, địa chỉ. Mỗi hĩa đơn cĩ thể cĩ một hoặc nhiều cuốn sách, mỗi cuốn sách cĩ thể mua với số lượng bất kỳ. + NhomSach(MaNhom char(5), TenNhom nvarchar(25)) + NhanVien(MaNV char(5), HoLot nvarchar(25), TenNV nvarchar(10), Phai nvarchar(3), NgaySinh Smalldatetime, DiaChi nvarchar(40)) + DanhMucSach(MaSach char(5), TenSach nvarchar(40), TacGia nvarchar(20), MaNhom char(5), DonGia Numeric(5), SLTon numeric(5)) + HoaDon(MaHD char(5), NgayBan SmallDatetime, MaNV char(5)) + ChiTietHoaDon(MaHD char(5), MaSach char(5), SoLuong numeric(5)) Page 2 Khoa Cơng Nghệ Thơng Tin
3. Hướng dẫn thực hành Hệ CSDL 1. Xác định mối quan hệ giữa các thực thể, khĩa chính, khĩa ngoại và các ràng buộc tồn vẹn khác nếu cĩ. 2. Sử dụng ngơn ngữ định nghĩa dữ liệu: CREATE TABLE để cài đặt cấu trúc bảng vào ngơn ngữ SQL Mơ tả: Thực hiện lệnh CREATE TABLE với các ràng buộc về khĩa chính, khĩa ngoại để tạo các bảng tương ứng với các quan hệ đã cho ở Bài 1. Lưu ý: SV nên tạo các bảng cĩ quan hệ 1 trước, sau đĩ đến bảng cĩ quan hệ 1- n, cuối cùng là bảng cĩ quan hệ n-n. 3. Mục đích: Thực hiện lệnh Alter Table add constraint để kiểm tra các ràng buộc về khĩa chính, khĩa ngoại (nếu chưa cài đặt ở Bài 2) và các ràng buộc về dữ liệu nếu cĩ. Page 3 Khoa Cơng Nghệ Thơng Tin
4. Hướng dẫn thực hành Hệ CSDL Module 2 Nội dung kiến thức thực hành: +Ngơn ngữ thao tác dữ liệu: INSERT. Cửa sổ Enterprise Manager. 1. Thực hiện lệnh INSERT để đưa dữ liệu vào các bảng NHOMSACH, NHANVIEN, DANHMUCSACH. Bảng NhomSach Bảng NhanVien Bảng DanhMucSach Page 4 Khoa Cơng Nghệ Thơng Tin
5. Hướng dẫn thực hành Hệ CSDL 2. Dùng cửa sổ Enterprise Manager để nhập dữ liệu cho 2 bảng HOADON và CHITIETHOADON Bảng Hoadon Bảng Chitiethoadon Page 5 Khoa Cơng Nghệ Thơng Tin
6. Hướng dẫn thực hành Hệ CSDL Module 3: Nội dung kiến thức thực hành: +Chức năng import, export. Bài 1: Dùng chức năng Import/Export để đưa (nhớ kiểm tra kết quả sau mỗi lần thực hiện): 1. Tất cả các thơng tin nhân viên cĩ trong bảng Employees trong NorthWind ra thành tập tin NhanVien.txt. 2. Dữ liệu của các bảng Nhomsach, Nhanvien, DanhMucsach trong CSDL vào tập tin QUANLYSACH.MDB. Lưu ý: Tập tin QUANLYSACH.MDB phải tồn tại trên đĩa trước khi thực hiện Export. 3. Dữ liệu các bảng HOADON, CHITIETHOADON trong CSDL ra thành tập tin HD.XLS 4. Các nhân viên cĩ phái là Nữ từ bảng Nhanvien trong CSDL ra thành tập tin NhanvienNu.TXT. Bài 2: 1. Thực hiện chức năng detach để xuất cơ sở dữ liệu ở trên. 2. Dùng chức năng attach để lấy lại cơ sở dữ liệu. 3. Tạo một file backup cho cơ sở dữ liệu. 4. Khơi phục lại cơ sở dữ liệu từ file backup trên. Module 4: Nội dung kiến thức thực hành: +Ngơn ngữ hiệu chỉnh dữ liệu: ALTER TABLE. Bài 1: 1. Sao chép bảng nhân viên thành bảng ChamCongNhanVien Page 6 Khoa Cơng Nghệ Thơng Tin
7. Hướng dẫn thực hành Hệ CSDL 2. Chèn thêm cột NgayCong number(3) vào bảng ChamCongNhanVien 3. Chèn thêm cột DonGiaGiaCong number(5) vào bảng ChamCongNhanVien 4. Xĩa các cột HoLot, Ten, Phai, NgaySinh, DiaChi trong bảng ChamCongNhanVien Bài 2: 1. Thêm vào bảng Hoadon cột NGAYLAPHD, kiểu char(10). 2. Hiệu chỉnh kiểu dữ liệu cho cột vừa thêm thành kiểu datetime. 3. Đổi tên cơ sở dữ liệu thành HOTENSV_SOMAY 4. Đổi tên bảng CHAMCONGNHANVIEN thành BANGLUONG. 5. Xĩa BANGLUONG khỏi cơ sở dữ liệu. Bài 3: 1. Detach cơ sở dữ liệu vừa tạo 2. Dùng lệnh Truncate Table để xĩa bảng Hoadon khỏi CSDL. Bạn cĩ thực hiện được khơng? Giải thích. 3. Dùng lệnh Alter Table Drop Constraint để xĩa ràng buộc giữa bảng Hoadon và Chitiethoadon. 4. Xĩa bảng Hoadon bằng lệnh Truncate table 5. Xĩa CSDL hiện tại. 6. Attach lại cơ sở dữ liệu bằng file .mdf ở trên. Page 7 Khoa Cơng Nghệ Thơng Tin
8. Hướng dẫn thực hành Hệ CSDL Module 5 Nội dung kiến thức thực hành: +Ngơn ngữ thao tác dữ liệu: INSERT, UPDATE, DELETE. Bài 1 : Dùng lệnh UPDATE để làm các cơng việc sau: 1. Cập nhật đơn giá 30000 cho mã sách S001 2. Cập nhật số lượng tồn = 50% số lượng tồn hiện cĩ cho những cuốn sách thuộc mã nhĩm N002 3. Cập nhật mã nhĩm N001 trong bảng NHOMSACH thành N100? Bạn cĩ cập nhật được hay khơng?. Vì sao?. 4. Tăng đơn giá các danh mục sách lên 3% 5. Giảm 10% đơn giá cho nhĩm sách Anh văn. 6. Thêm vào cột GIABAN trong bảng CHITIETHOADON. Cập nhật giá bán bằng với đơn giá trong bảng DANHMUCSACH. Bài 2 : Dùng lệnh INSERT để làm các cơng việc sau: 1. Thêm vào bảng NHOMSACH một bộ (‘N007’, ‘Sức khỏe và đời sống’) 2. Thêm vào bảng CHITIETHOADON với số hĩa đơn 10, mã sách S001 với số lượng 5 và S002 với số lượng 20. 3. Thêm vào bảng DANHMUCSACH với mã sách S011, tên sách là ‘Cánh đồng bất tận’, tác giả ‘Nguyễn Ngọc Tư’, mã nhĩm N006, đơn giá 45000, số lượng tồn 5. 4. Thêm vào bảng DANHMUCSACH với mã sách S011, tên sách là ‘Hỏi đáp sức khỏe’, tác giả ‘Đỗ Hồng Ngọc’, mã nhĩm N008, đơn giá 25000, số lượng tồn 50. Bạn cĩ thêm được khơng? Hãy quan sát lỗi (nếu cĩ) và giải thích?. Hãy thực hiện cơng việc tiếp theo để thêm được bộ đã cho. Page 8 Khoa Cơng Nghệ Thơng Tin
9. Hướng dẫn thực hành Hệ CSDL 5. Thêm vào bảng Hoa don 10 bộ dữ liệu tùy ý, do các nhân viên cĩ mã là NV002, NV003, NV004 lập.(lưu ý các ràng buộc dữ liệu, khĩa chính, khĩa ngoại). 6. Thêm vào bảng Chitiethoadon 30 bộ tùy ý. Bài 3 : Dùng lệnh DELETE để làm các cơng việc sau: 1. Xĩa nhĩm sách ‘N007’ trong bảng NHOMSACH 2. Xĩa Hoadon số 9 trong bảng Hoadon. Bạn cĩ thực hiện được hay khơng?. Vì sao? Page 9 Khoa Cơng Nghệ Thơng Tin
10. Hướng dẫn thực hành Hệ CSDL Module 6: Nội dung kiến thức thực hành: +Tìm hiểu các loại join trong SQL Server 1. Tạo query sử dụng lệnh SELECT với các dạng liên kết: 1. Inner Join 2. Left Outer Join 3. Full Outer Join 4. Cross Join Kết quả hiển thị là thơng tin của các cột sau: MAHD NGAYBAN SOLUONG MANHANVIEN 2. So sánh kết quả sau mỗi lần thực hiện dạng kết nối. Page 10 Khoa Cơng Nghệ Thơng Tin
11. Hướng dẫn thực hành Hệ CSDL Module 7,8 Nội dung kiến thức thực hành: +Ngơn ngữ thao tác dữ liệu: SELECT, UPDATE, DELETE. 1. Liệt kê danh mục sách theo thứ tự mã sách. 2. Liệt kê nhân viên theo thứ tự tên. 3. Liệt kê những nhân viên nam theo thứ tự tên. 4. Liệt kê những nhân viên cĩ tên là Mai. 5. Liệt kê những nhân viên cĩ tên bắt đầu bằng ký tự t 6. Liệt kê những nhân viên cĩ họ lĩt là thị hay văn 7. Liệt kê những nhân viên sinh năm 1975 8. Liệt kê những nhân viên sinh vào tháng 9 9. Liệt kê những cuốn sách cĩ tên tác giả bắt đầu là Nguyễn 10. Liệt kê những sách cĩ số lượng tồn =10000 và số lượng tồn >100 14. Liệt kê những cuốn sách thuộc nhĩm N001 và số lượng tồn từ 10 đến 20 15. Liệt kê hĩa đơn theo thứ tự tăng dần của MaNV, nếu trùng mã nhân viên thì xếp theo ngày bán. 16. Hiển thị danh sách những sách thuộc ngành tin học gồm:mã sách, tên sách, mã nhĩm. 17. Liệt kê sách thuộc nhĩm tin học cĩ số lượng tồn <10 Page 11 Khoa Cơng Nghệ Thơng Tin
12. Hướng dẫn thực hành Hệ CSDL 18. Liệt kê các danh mục sách và tiền tồn vốn, xếp theo thứ tự giảm dần của tiền tồn. bảng kết quả gồm mã sách, tên sách, nhĩm sách, tác giả, đơn giá, số lượng, tiền vốn = đơn giá x số lượng tồn 19. Danh sách các hĩa đơn ứng với tổng tiền của từng hĩa đơn 20. Danh sách các hĩa đơn cĩ ngày bán là ngày 15/7/2008 21. Danh sách các sách đã được bán, ứng với tổng số lượng, thành tiền 22. Danh sách các hĩa đơn bán trong 20/7/2008, ứng với tổng số lượng, thành tiền 23. Danh sách các sách khơng bán được. 24. Danh sách các nhân viên chưa lập hĩa đơn nào. 25. Danh sách các sách cĩ số lượng bán nhiều nhất. 26. Danh sách các nhân viên ứng với tổng số tiền hĩa đơn mà nhân viên ấy lập 27. Thống kê thành tiền ứng với mỗi nhĩm sách và mỗi ngày 28. Cho biết nhân viên nào bán được nhiều sách nhất và số lượng là bao nhiêu. 29. Cho biết những nhân viên nào cĩ cùng ngày sinh. 30. Cho biết nhân viên nào cĩ tuổi lớn nhất. 31. Cho biết nhân viên nào cĩ tuổi nhỏ nhất. 32. Cập nhật thơng tin cho cột NGAYLAPHD trong bảng HOADON chính là thơng tin của cột NGAYBAN. 33. Thêm vào bảng NHANVIEN cột LƯƠNG, kiểu float. Cập nhật lương =1000000 cho những nhân viên cĩ lập hĩa đơn. 34. Thêm vào cột PHUCAP trong bảng NHANVIEN. Cập nhật PHUCAP = 50%*LUONG cho những nhân viên bán được số lượng sách >100. 35. Thêm vào cột NGAYVAOLAM, kiểu datetime. Cập nhật ngày vào làm cho nhân viên = Năm sinh +18. Page 12 Khoa Cơng Nghệ Thơng Tin
13. Hướng dẫn thực hành Hệ CSDL 36. Cập nhật phụ cấp cho những nhân viên cĩ thâm niên từ 5 năm trở lên (thâm niên được tính = năm hiện tại – năm vào làm). Page 13 Khoa Cơng Nghệ Thơng Tin
14. Hướng dẫn thực hành Hệ CSDL Module 9 Nội dung kiến thức thực hành: +Thao tác trên view. Dùng CSDL Qlsach, thực hiện tạo các view sau: 1. Tạo một view cĩ tên là VWSACH_1, chính là danh sách các cuốn sách, được sắp xếp theo tên sách. 2. Lần lượt tạo view cĩ tên là VWSACH_2, VWSACH_3 thơng tin giống như VWSACH_1 nhưng cĩ thêm từ khĩa WITH ENCRYPTION, WITH SCHEMABINDING. 3. Thực hiện các yêu cầu sau: Nhớ kiểm tra và cho nhận xét. . Dùng lệnh INSERT VALUES chèn mẫu tin sau vào bản NHOMSACH: MANHOM TENNHOM N007 Kỹ thuật trồng trọt . Dùng lệnh Insert values chèn mẫu tin sau thơng qua VWSACH_1. Bạn cĩ chèn được hay khơng?. Vì sao? MaSH TenSach TacGia MaNH DonGia SlTon S111 Đèn khơng hắt Dzunichi Watanabe (Cao N006 55000 45 bĩng Xuân Hạo dịch) . Dùng lệnh ALTER để hiệu chỉnh VWSACH_1 khơng cĩ sắp xếp theo tên sách. Sau đĩ, chèn mẫu tin trên vào bảng danh mục sách thơng qua view này. . Chèn thêm các mẫu tin sau vào bảng danh mục sách thơng qua VWSACH_1 đã hiệu chỉnh ở trên: MaSH TenSach TacGia MaNH DonGia SlTon Page 14 Khoa Cơng Nghệ Thơng Tin
15. Hướng dẫn thực hành Hệ CSDL Kỹ thuật trồng S112 Nguyễn Lân Hùng N007 45000 35 hoa phong lan Kỹ thuật chăm S113 Lê Xuân A N007 35000 15 sĩc hoa mai Kỹ thuật chăm S114 Trần Ha N007 24000 12 sĩc cây cam . Dùng sp_helptext tênview để lần lượt xem text của các view VWSACH_1, VWSACH_2, VWSACH_3 . Dùng lệnh Alter Table để hiệu chỉnh kiểu dữ liệu của cột DONGIA thành kiểu money, cĩ hiệu chỉnh được khơng? Tại sao? Muốn hiệu chỉnh được thì phải như thế nào? Bạn thử thực hiện. 4. Tạo view cĩ tên là VWSACH_4, chỉ cĩ những cuốn sách thuộc nhĩm Kỹ thuật trồng trọt, với lựa chọn WITH CHECK OPTION. 5. Dùng câu lệnh INSERT VALUES để chèn mẫu tin sau vào bảng danh mục sách thơng qua viewVWSACH_4. Bạn cĩ chèn được khơng? Tại sao? MaN MaSH TenSach TacGia DonGia SlTon H S115 Quê Mẹ Thanh Tịnh N006 35000 7 6. Bạn hãy chèn mẫu tin trên thơng qua VWSACH_1. 7. Dùng lệnh DELETE thơng qua VWSACH_1 để xĩa mẫu tin vừa chèn ở trên. 8. Tạo 3 bảng lần lượt cĩ tên là KH_BAC, KH_TRUNG, KH_NAM, dùng để lưu danh sách các khách hàng ở ba miền, cĩ cấu trúc như sau: MaKh, TenKH, DiaChi, KhuVuc. Trong đĩ, KH_BAC cĩ một Check Constraint là Khuvuc là ‘Bac Bo’ KH_NAM cĩ một Check Constraint là Khuvuc là ‘Nam Bo’ Page 15 Khoa Cơng Nghệ Thơng Tin
16. Hướng dẫn thực hành Hệ CSDL KH_TRUNG cĩ một Check Constraint là Khuvuc là ‘Trung Bo’ Khố chính là MaKH và KhuVuc . 9. Tạo một partition view từ ba bảng trên. Page 16 Khoa Cơng Nghệ Thơng Tin
17. Hướng dẫn thực hành Hệ CSDL Module 10: Kiểm tra giữa kỳ. Page 17 Khoa Cơng Nghệ Thơng Tin
18. Hướng dẫn thực hành Hệ CSDL Module 11: Nội dung kiến thức thực hành: + Lập trình T-SQL với các đoạn batch. 1. Khai báo một biến @namsinh kiểu số nguyên, gán giá trị đại diện cho biến @namsinh, viết câu lệnh lấy ra danh sách các nhân viên cĩ năm sinh nhỏ giá trị chứa trong biến @namsinh. 2. Khai báo và giá giá trị đại diện cho mã nhân viên. Kiểm tra xem nhân viên này đã lập bao nhiêu hố đơn, giả sử là n. In ra câu thơng báo cho biết ‘Cĩ hố đơn’. Nếu khơng cĩ hố đơn nào cả thì in ra câu ‘Khơng cĩ hố đơn’. Khi chạy đoạn batch thì thay đổi giá trị của biến để kiểm chứng kết quả. 3. Viết một câu lệnh SELECT cĩ sử dụng CASE dùng để hiển thị MaHD, Soluong, Dongia, Thanhtien. Trong đĩ Thanhtien = Soluong * Giaban – Chietkhau, chiết khấu được tính như sau: Chiết khấu = 5%*GIABAN nếu Soluong Soluong >=5 Chiết khấu = 8%* GIABAN nếu 15>Soluong>=10 Chiết khấu =10%* GIABAN nếu Soluong>=15 4. Viết đoạn Batch để giải một phương trình bậc nhất khi biết a, b. 5. Viết một vịng lặp WHILE để nhập 10 mẫu tin tùy ý vào bảng HOCVIEN (MAHV, TENHV, LOP), với bảng dữ liệu như sau: 1 Hoc vien 1 CDTH3 2 Hoc vien 2 CDTH3 . . . 10. Hoc vien 10 CDTH3 Page 18 Khoa Cơng Nghệ Thơng Tin
19. Hướng dẫn thực hành Hệ CSDL Module 12, 13 + Nội dung kiến thức thực hành: +Stored procedures. 1. Viết thủ tục cĩ tham số a, b dạng input để giải phương trình bậc nhất ax+b=0. 2. Viết thủ tục cĩ tham số a, b, c dạng input để giải phương trình bậc hai, ax2+bx+c=0. (cĩ sử dụng lại thủ tục giải phương trình bậc nhất trong trường hợp a=0) 3. Viết một thủ tục dùng để thực hiện các cơng việc sau: tạo một bảng tạm tên là #MyTable, chèn hai mẫu tin tùy ý vào bảng này, dùng câu lệnh SELECT FROM để liệt kê các mẫu tin cĩ trong bảng #MyTable. Cấu trúc của #MyTable như sau CustID nchar(5), CustName nvarchar(40), AvgDiff float. 4. Viết thủ tục cĩ tham số dạng input dùng để chèn một mẫu tin vào bảng Nhanvien. 5. Tạo bảng Nhanvien_backup cĩ cấu trúc giống bảng Nhanvien. 6. Viết thủ tục cĩ tham số dùng để chèn một mẫu tin vào bảng Nhanvien hoặc Nhanvien_backup. Trong đĩ, dùng 1 tham số @flag để phân biệt 2 bảng trên khi chèn. 7. Viết thủ tục dùng để xĩa thơng tin một cuốn sách khi biết Masach. 8. Viết thủ tục dùng để xĩa 1 hĩa đơn khi biết SoHD. Lưu ý trước khi xĩa mẫu tin trong hĩa đơn thì phải xĩa các mẫu tin của các chi tiết hĩa đơn đĩ. Nếu khơng xố được hĩa đơn thì cũng khơng được phép xĩa chi tiết hĩa đơn của hĩa đơn đĩ. 9. Viết thủ tục cĩ tham số dùng để cập nhật dữ liệu mới của một mẫu tin nào đĩ khi biết Masach trong bảng Danhmucsach. Page 19 Khoa Cơng Nghệ Thơng Tin
20. Hướng dẫn thực hành Hệ CSDL 10. Phát triển từ bài trên, viết thủ tục cĩ tham số dùng để cập nhật dữ liệu mới của một mẫu tin nào đĩ trong bảng Nhanvien hoặc Nhanvien_backup. Dùng 1 tham số @flag để phân biệt 2 bảng trên. 11. Viết một thủ tục dùng để lấy về tổng số lượng đơn đặt hàng và đơn giá trung bình của của một nhĩm sách nào đĩ khi biết mã nhĩm. Page 20 Khoa Cơng Nghệ Thơng Tin
21. Hướng dẫn thực hành Hệ CSDL Module 14 + Nội dung kiến thức thực hành: +User define function 1. Viết hàm trả về kết quả là một bảng (Table), thơng tin bao gồm Tên nhĩm, , TongSoLuong, DonGiaTrungBình. Lưu ý viết bằng hai cách: Inline Table- Valued Functions và Multistatement Table-Valued Function. 2. Viết hàm trả về tổng thành tiền của một hĩa đơn nào đĩ. Thanh tien = (SốLượng * Đơn giá) - ChiếtKhấu. 3. Viết hàm trả về chiết khấu dựa vào số lượng lập hố đơn và theo quy định sau: Chiết khấu = 5%* GIABAN nếu Soluong Soluong >=5 Chiết khấu = 8%* GIABAN nếu 15>Soluong>=10 Chiết khấu =10%* GIABAN nếu Soluong>=15 4. Tương tự viết một hàm trả về xếp loại sinh viên khi biết điểm của sinh viên đĩ và dựa vào quy tắc xếp loại thơng thường. 5. Viết hàm trả về danh sách các hố đơn đã lập của nhân viên nào đĩ. Thơng tin gồm: SoHD, Soluong,Dongia, Thanhtien, MaNV. Page 21 Khoa Cơng Nghệ Thơng Tin
22. Hướng dẫn thực hành Hệ CSDL Module 15 + Nội dung kiến thức thực hành: +Lập trình với Cursor 1. Sử dụng cơ sở dữ liệu NorthWind, viết một thủ tục cĩ dùng con trỏ (Cursor) để thực hiện in ra cửa sổ kết quả của Query Analyzer báo cáo như mẫu. Báo cáo liện kê danh sách các CustomerID, CompanyName, OrderID và OrderDate được lập trong năm 1997 của các khách hàng cĩ CustomerID bắt đầu là ‘A’. Customer:ALFKI - Alfreds Futterkiste Order:10643 (Aug 25 1997) Order:10692 (Oct 3 1997) Order:10702 (Oct 13 1997) Customer:ANATR - Ana Trujillo Emparedados y helados Order:10625 (Aug 8 1997) Order:10759 (Nov 28 1997) Customer:ANTON - Antonio Moreno Taquería Order:10507 (Apr 15 1997) Order:10535 (May 13 1997) Order:10573 (Jun 19 1997) Order:10677 (Sep 22 1997) . 2. Sử dụng cơ sở dữ liệu NorthWind, viết một thủ tục cĩ dùng con trỏ để thực hiện in ra cửa sổ kết quả của Query Analyzer báo cáo như mẫu. Báo cáo liện kê danh sách các CompanyName, ContactName, ContactTitle của tất cả các Customers There are 91 Customers 1 Alfreds Futterkiste Maria Anders Sales Representative 2 Ana Trujillo Emparedados y helados Ana Trujillo Owner Page 22 Khoa Cơng Nghệ Thơng Tin
23. Hướng dẫn thực hành Hệ CSDL 3 Antonio Moreno Taquería Antonio Moreno Owner 4 Around the Horn Thomas Hardy Sales Representative 5 Berglunds snabbkưp Christina Berglund Order Administrator 6 Blauer See Delikatessen Hanna Moos Sales Representative Page 23 Khoa Cơng Nghệ Thơng Tin
24. Hướng dẫn thực hành Hệ CSDL Module 16 + Nội dung kiến thức thực hành: +Lập trình Trigger 1. Viết trigger cho thao tác Insert của bảng NHOMSACH. Khi cĩ thao tác chèn vào bảng nhĩm sách thì đưa ra một thơng báo là ‘Cĩ mẫu tin được chèn’. 2. Viết trigger cho thao tác Insert trên bảng HOADON. Sau khi cĩ mẫu tin được chèn vào bảng HOADON thì mẫu tin đĩ cũng được chèn vào bảng HOADON_Luu. Lưu ý: nếu chưa cĩ bảng HOADON_Luu thì tạo HOADON_Luu (cĩ cấu trúc hồn tồn giống như bảng HOADON) trước khi kiểm chứng trigger. 3. Viết trigger cho thao tác Insert, Update, Delete trên bảng CHITIETHOADON. Khi cĩ mẫu tin được chèn vào hoặc hiệu chỉnh hoặc xố thì cập nhật lại cột TongTriGia trong bảng HOADON với TongTriGia = Tổng tiền của Số lượng * Đơn giá. Lưu ý: nếu bảng HOADON chưa cĩ cột TongTriGia thì bổ sung vào trước khi kiểm chứng trigger. 4. Viết trigger cho thao tác Insert, Update để kiểm tra ràng buộc liên thuộc tính liên quan hệ giữa GIABAN trong CHITIETHOADON và DONGIA trong bảng mặt hàng như sau: GIABAN trong CHITIETHOADON luơn luơn bằng DONGIA trong DANHMUCSACH, nếu vi phạm thì thơng báo và khơng cho phép Insert hay Update. 5. Cĩ ràng buộc liên thuộc tính là ngày bán của một HOADON thì luơn luơn lớn hơn hay bằng ngày lập hĩa đơn . Hãy bắt ràng buộc trên khi cần thiết. 6. Giả sử cĩ 2 bảng LOP(Malop, TenLop), SV(Masv, HoTen, Malop), với một ràng buộc dạng liên bộ như sau: Mỗi lớp chỉ cĩ tối đa 10 học viên. Hãy viết 1 trigger để kiểm tra ràng buộc đĩ, nếu vi phạm ràng buộc thì sinh ra một mã lỗi và đưa ra thơng báo “Lớp này đã đủ học viên”. Page 24 Khoa Cơng Nghệ Thơng Tin
25. Hướng dẫn thực hành Hệ CSDL 7. Giả sử cĩ 2 bảng LOP(Malop, TenLop, SiSoToida, SiSoThuc), SV(Masv, HoTen, Malop), với các ràng buộc dạng liên bộ liên quan hệ như sau: a. Chỉ chèn thêm hoặc hiệu chỉnh chuyển lớp cho một sinh viên của bảng SV khi sĩ số của lớp đĩ vẫn cịn nhỏ hơn SiSIToiDa. b. Khi cĩ thao tác thêm, xố, sửa thơng tin trên bảng SV thì phải cập nhật lại SiSoThuc 8. Hãy bắt các ràng buộc trên và sinh ra mã lỗi thích hợp khi cĩ các thao tác tương ứng xuất hiện mà vi phạm ràng buộc đặt ra. 9. Giả sử cĩ bảng theo dõi quá trình tăng lương của nhân viên như sau QuaTrinhTangLuong(MANV, NGAYTANG, MUCLUONG) Biết rằng mức lương của nhân viên chỉ tăng lên theo thời gian. Hãy viết trigger để ép thỏa điều kiện này. Module 17, 18 + Nội dung kiến thức thực hành: +Roles, Logins, gán quyền bằng thao tác trong Enterprise Manager 1. Tạo login dạng SQL Server Authentication a. Tạo một login cĩ tên là tên của bạn, login cĩ: . Chế độ chứng thực là SQL Server Authentication, password tuỳ ý, CSDL mặc định là QLSach. . Khơng thuộc Server Roles nào cả . Chỉ cho truy xuất đến duy nhất CSDL là QLSach và khơng thuộc Database Roles nào cả ngoại trừ Public Kiểm tra: Page 25 Khoa Cơng Nghệ Thơng Tin
26. Hướng dẫn thực hành Hệ CSDL . Ở Enterprise Manager, kiểm tra xem tên login của bạn cĩ nằm trong nhánh Security\Login khơng? kiểm tra xem tên login của bạn cĩ nằm trong nhánh User của CSDL QLSach khơng? Xem properties của nĩ. . Kết nối vào Query Analyzer bằng login vừa tạo . Trong mục database bạn cĩ thể nhìn thấy được những database nào? Tại sao? . Dùng câu lệnh SELECT FROM để xem các mẫu tin trong bảng DANHMUCSACH, bạn xem được khơng? Tại sao? b. Hiệu chỉnh login ở trên, cho phép login thuộc database Roles tên là db_DataReader trong CSDL QLSach Kiểm tra: . Dùng câu lệnh SELECT FROM để xem các mẫu tin trong bảng NHOMSACH, bạn xem được khơng? Tại sao? . Dùng câu lệnh INSERT VALUES để chèn một mẫu tin mới vào bảng DANHMUCSACH, bạn cĩ chèn được khơng? Tại sao? Muốn chèn được bạn phải làm gì? Thực hiện thử xem sao. c. Tương tự như vậy, lần lượt tìm hiểu các database Roles cịn lại. 2. Tạo login dạng Windows Authentication a. Quay về hệ điều hành tạo một local user account hoặc domain user account được phép kết nối đến máy Server của SQL Server. User account này cĩ tên là Nhanvien1. b. Cho phép Nhanvien1 trở thành login của SQL Server, login này chỉ thuộc vào database Roles là db_datareader của CSDL là QLSach. (Lưu ý: phải chọn Windows Authentication) Page 26 Khoa Cơng Nghệ Thơng Tin
27. Hướng dẫn thực hành Hệ CSDL c. Bạn hãy thử kết nối Server thơng qua cơng cụ Query Analyzer bằng login vừa tạo và kiểm tra quyền của login đối với QLSach. (Hướng dẫn: đĩng hết các ứng dụng đang chạy, log off user hiện kết nối đến máy, log on vào máy bằng user account vừa tạo, kết nối vào Query Annalyzer bằng login) 3. Login được tạo ở câu 1 và câu 3 cĩ thể thực hiện tạo được Table, view, trong CSDL QLSach hay khơng? Muốn tạo được cần cĩ điều gì? 4. Tạo một Database Role cĩ tên là ThuNgan của CSDL QLSach, Role này cĩ quyền hạn như sau: - Được phép chèn, cập nhật dữ liệu trong hai bảng HOADON và CHITIETHOADON.Chỉ được phép xem (Select) trên các bảng cịn lại 5. Tạo 3 login dạng SQL Server Authentication, cĩ tên lần lượt là ThuNgan1, ThuNgan2, ThuNgan3. Các Login này chỉ thuộc duy nhất DataBase Role là ThuNgan đã tạo ở trên. 6. Tạo 3 login dạng SQL Serevr Authentication, cĩ tên lần lượt là QuanLyKho1, QuanLyKho2, QuanLyKho3. Các login này cĩ cùng một quyền hạn là được phép chèn, xĩa dữ liệu trên bảng Nhanvien; cập nhật duy nhất cột Hoten trong bảng Nhanvien. Các bảng khác chỉ được xem. 7. Bạn chọn một giải pháp đơn giản nhất để cho phép các login đã tạo ở trên được phép xem thơng tin trong bảng DanhMucSach. 8. Tạo hai login thuộc dạng SQL Server Autehtication, cĩ tên lần lượt là PhatTrien_UngDung1, PhatTrien_UngDung2. Các login này cĩ các quyền như sau: - Được phép tạo các đối tượng của database - Được phép truy xuất và hiệu chỉnh các đối tượng database Page 27 Khoa Cơng Nghệ Thơng Tin
28. Hướng dẫn thực hành Hệ CSDL Module 19 + Nội dung kiến thức thực hành: +Roles, Logins, gán quyền bằng các thủ tục hệ thống 1. Tạo một login dạng Windows Authentication cĩ tên là GD1. 2. Tạo hai login dạng SQL Server Authentication tên là PGD1 và PGD2 cĩ password tùy ý, CSDL truy xuất đến là QLSach. 3. Cho phép cả ba login trên được truy xuất đến SalesDB, Movie, QLSach. 4. Theo bạn giám đốc kinh doanh nên cĩ những quyền hạn như thế nào trong QLSach? Bạn hãy tạo một user-defined role với tên là GiamDocKinhDoanh cĩ các quyền hạn này. Thực hiện thêm 3 login trên thành thành viên của role GiamDocKinhDoanh. 5. Tạo hai login cĩ tên là NghiepVu1, NghiepVu2 được phép các quyền sau: xem và hiệu chỉnh cột DONGIA trong bảng DANHMUCSACH; xem, hiệu chỉnh và xĩa dữ liệu trong bảng HOADON và CHITIETHOADON; xem, chèn, hiệu chỉnh và xố dữ liệu trong bảng NHANVIEN; các bảng cịn lại chỉ được xem. Page 28 Khoa Cơng Nghệ Thơng Tin
29. Hướng dẫn thực hành Hệ CSDL Module 20: Kiểm tra kết thúc mơn Page 29 Khoa Cơng Nghệ Thơng Tin