Bài 6: View SP Func Trigger QLBH

BÀI THỰC HÀNH SỐ TRÊN CSDL QUẢN LÝ BÁN HÀNG (QLBH)

1. Tạo các khung nhìn

1. Tạo khung nhìn chứa danh sách các khách hàng có địa chỉ Hà Nội. Sau đó dựa vào khung nhìn vừa tạo đưa ra danh sách các khách hàng có tên bắt đầu bằng kí tự ‘T’.

2. Tạo view chứa tổng doanh thu theo từng khách hàng. Sau đó dựa trên view đã định nghĩa đưa ra của các khách hàng đạt tổng doanh thu trên 1 tỷ đồng.

3. Tạo View tính tổng số lượng đơn hàng và tổng số lượng đã bán của từng mặt hàng trong thời gian từ 4/6/2005 đến 10/6/2005.

4. Tạo View cho biết mọi thông tin chi tiết các lần bán hàng (tên khách hàng, tên mặt hàng, tên cửa hiệu, ngày bán, thành tiền) của các cửa hàng có kí tự đầu tiên là ‘H’ trong khoảng thời gian từ 4/6/2005 đến 10/6/2005.

5. Dựa vào view đã xây dựng trong câu 4, hãy thống kê tổng tiền hàng đã bán được của từng cửa hàng.

6. Dựa trên view xây dựng ở câu 4 xây dựng view đưa ra danh sách tên và tiền của các lần bán hàng có giá trị lớn hơn 100 triệu.

7. Tạo view đưa ra danh sách các khách hàng đã mua hàng của cửa hàng ở Hà Nội hoặc đã mua một mặt hàng của nhà cung ứng Toyota .

- Kiểm tra lại khung nhìn vừa tạo

- Dựa trên khung nhìn đưa ra danh sách các khách hàng có doanh số mua hàng cao nhất.

- Khung nhìn này có sửa dữ liệu không?

8. Tạo view chứa mọi khách hàng đã mua hàng của nhà cung ứng Honda. View này bao gồm cả các thông tin về tổng doanh số mua của khách.

9. Hãy lập khung nhìn cho biết số liệu thống kê về bán hàng theo từng tháng của của năm 2005. Đối với mỗi tháng cần biết tổng số lượng bán và tổng doanh thu của mỗi mặt hàng.

2. Ràng buộc

Trên CSDL QLBH:

1. Trên bảng MAT_HANG tạo thêm các ràng buộc sau:

• Tạo ràng buộc tên LOAI_MH cho trường Loai_MH chỉ nhận 1 trong 3 giá trị ‘XM’, ‘OTO’, ‘XD’ và giá trị mặc định đặt là ‘XM’.

• Tạo ràng buộc tên TKHO_MH cho trường Ton_kho_MH luôn có giá trị lớn hơn hoặc bằng 0 và có giá trị mặc định là 0.

• Tạo ràng buộc tên GMUA_GBAN_MH cho trường GIA_BAN_MH >=GIA_MUA_MH. *

2. Trên bàng NHA_CUNG_CAP tạo thêm ràng buộc UNIQUE_NCC cho trường Ten_NCC là không trùng nhau.

3. Thêm vào bảng CHI_TIET_DH trường Sluong_giao có kiểu dữ liệu giống trường Sluong_dat. Thêm ràng buộc DAT_GIAO_CTDH sao cho Sluong_giao luôn <= Sluong_dat.

Trên CSDL QLNV của bài thực hành quản lý nhân viên thêm các ràng buộc sau:

4. Tên phòng trong bảng PHONG là duy nhất.

5. Tên ngoại ngữ trong bảng DM ngoại ngữ là duy nhất

6. Trường lương trong bảng nhân viên luôn>=0 và nhận giá trị mặc định là 0.

7. Trường MAPHONG trong bảng NHAN_VIEN tham chiếu đến MAPHONG trong bảng PHONG và cho phép cập nhật và xoá tham chiếu.

8. Trường MANV, MANN trong bảng TDNN lần lượt tham chiếu đến các trường MANV trong bảng NHAN_VIEN và MANN trong bảng DMNN. Các tham chiếu này cho phép cập nhật và xoá tự động.

9. Trường TDO trong bảng TDNN chỉ nhận một trong các giá trị A, B, C, D, E, F và mặc định nhậm giá trị A.

3. Thủ tục (Store Procedure)

Chú ý: Trong SP có thể dùng lệnh Return <biểu thức> để trả ra giá trị của thủ tục

Tên CSDL QLBH hãy tạo các SP sau:

1. Tạo thủ tục có tham số đưa vào là Ma_MH, Ten_MH, Loai_MH, Tluong_MH, Mau_MH, Ton_kho_MH, Gia_mua_MH, Gia_ban_MH, Ma_NCC. Trước khi chèn một bản ghi mới vào bảng MAT_HANG với danh sách giá trị là giá trị của các biến phải kiểm tra xem Ma_NCC đã tồn tại bên bảng NHA_CUNG_CAP chưa, nếu chưa trả ra 0.

2. Sửa thủ tục ở câu một kiểm tra xem thêm Ma_MH được chèn vào có trùng với Ma_MH nào đó có trong bảng không. Nếu Ma_MH chưa tồn tại trả ra 0, nếu Ma_NCC chưa tồn tại trả ra 1. Ngược lại cho phép chèn bản ghi.

3. Tạo SP cập nhật trường tên người quản lý cửa hàng với tham số đầu vào là Ma_CH và Quan_ly_CH (tên quản lý mới). Nếu không tìm thấy bản ghi cần cập nhật trả ra giá trị 0. Ngược lại, cho phép cập nhật.

Tạo các SP tương tự như các thủ tục trên cho các bảng DON_HANG, CHI_TIET_DH, GIAO_HANG, CHI_TIET_GH.

4. Tạo thủ tục có

Đầu vào: Ma_MH, Ngay1, Ngay2

Đầu ra: tổng số lượng hàng đã bán của mặt hàng đó trong khoảng từ Ngay1 đến Ngay2.

5. Tạo thủ tục có

Đầu vào: Ten_NCC

Đầu ra: tổng số lượng mặt hàng mà nhà cung cấp đó cung ứng.

6. Tạo thủ tục có

Đầu vào: Ma_KH

Đầu ra: tổng số lần mua và tổng giá trị tiền các lần mua hàng của khách hàng.

7. Tạo thủ tục có

Đầu vào: Ma_CH, Ngay1, Ngay2

Đầu ra: tổng số đơn hàng đã bán và tổng giá trị tiền thu được từ các đơn hàng đó trong khoảng từ Ngay1 và Ngay2.

4. Hàm

Chuyển các thủ tục 4, 5, 6 ở phần SP sang hàm.

5. Trigger

Trên CSDL QLBH tạo các trigger sau:

1. Tạo trigger để mỗi khi chèn một bản ghi mới vào bảng CHI_TIET_GH thì phải cập nhật lại thông tin cho trường Sluong_giao trên bảng CHI_TIET_DH.

2. Tạo trigger để mỗi khi cập nhật một bản ghi mới vào bảng CHI_TIET_GH thì phải cập nhật lại thông tin cho trường Sluong_giao trên bảng CHI_TIET_DH.

3. Tạo trigger để mỗi khi xoá một bản ghi mới vào bảng CHI_TIET_GH thì phải cập nhật lại thông tin cho trường Sluong_giao trên bảng CHI_TIET_DH.

4. Xoá bỏ 3 trigger trên và tạo một trigger tổng hợp.

5. Tạo trigger mỗi khi chèn thêm một bản ghi mới vào bảng CHI_TIET_GH thì phải cập nhật lại trường Ton_kho_MH trong bảng mặt hàng.

6. Tạo trigger mỗi khi sửa một bản ghi mới vào bảng CHI_TIET_GH thì phải cập nhật lại trường Ton_kho_MH trong bảng mặt hàng.

7. Tạo trigger mỗi khi xoá một bản ghi mới vào bảng CHI_TIET_GH thì phải cập nhật lại trường Ton_kho_MH trong bảng mặt hàng.

8. Tạo trigger mỗi khi chèn một bản ghi vào bảng CHI_TIET_DH phải kiểm tra xem số lượng hàng đặt có nhỏ hơn hay bằng số lượng thàng tồn kho không. Nếu lớn hơn không cho phép chèn. Ngược lại huỷ bỏ lệnh chèn.

9. Trên CSDL QLNV tạo trigger cho bảng NHANVIEN để kiểm tra nhân viên mới được thêm vào có thoả mãn các điều kiện

• Tuổi của nhân viên phải nằm trong khoảng từ 18 đến 60.

• Khi vào biên chế, nhân viên phải có tuổi trong khoảng từ 18 đến 40.

Nếu không thoả các điều kiện trên thì nhân viên đó không được thêm vào.