Bài tập lớn
Môn: Hệ cơ sở dữ liệu
Mô tả nghiệp vụ
Phịng kinh doanh của một xí nghiệp chế biến hải sản và thực phẩm xuất khẩu cần xây dựng hệ
thống quản lý bán hàng ở thị trường nội địa. Kết quả khảo sát về nghiệp vụ như sau:
Các mặt hàng của xí nghiệp bao gồm các loại:
- Nước chấm: tương ớt, tương cà, nước mắm, …
- Thực phẩm đông lạnh: chả giị, há cảo, hồnh thánh, …
- Trà: trà khổ qua, trà xanh, trà lài, …
Mỗi mặt hàng đều được cấp một mã số để phân biệt, các thông tin khác của mặt hàng gồm: tên
mặt hàng, mô tả, đơn vị tính. Giá bán của tất cả các mặt hàng được áp dụng thống nhất cho mọi
khách hàng. Hàng ngày phòng kinh doanh sẽ đưa ra bảng báo giá, bảng báo giá này chỉ có giá trị
trong ngày. Cần lưu trữ lại việc tăng giảm giá bán để phục vụ cho việc xây dựng hệ hỗ trợ ra quyết
định sau này.
Xí nghiệp khơng bán lẻ. Khách hàng của xí nghiệp đều là khách mua sỉ (mua hàng và được phép
ghi nợ trả sau) hoặc đại lý (chỉ hưởng chiết khấu). Khách hàng mới phải điền thông tin vào một biểu
mẫu do xí nghiệp cung cấp, gồm: tên khách hàng, số CMND, địa chỉ, các số điện thoại liên lạc, mã
số thuế. Sau khi xem xét nếu chấp nhận phòng kinh doanh sẽ cấp mã số khách hàng. Riêng đối
với khách làm đại lý sẽ làm thêm hợp đồng đại lý, thông tin trên hợp đồng gồm mã số hợp đồng,
ngày ký hợp đồng, thời hạn hợp đồng, các thông tin của khách hàng.
Phòng kinh doanh cũng dựa vào địa chỉ của khách hàng để xếp vào khu vực nào nhằm phục vụ
cho việc phát triển thị trường. Mỗi khu vực có mã khu vực và tên khu vực.
Khách hàng muốn mua hàng sẽ lập đơn đặt hàng. Thông tin trên đơn đặt hàng gồm ngày đặt hàng,
thông tin khách hàng, các mặt hàng và số lượng cần mua. Khi nhận đơn đặt hàng, phịng kinh
doanh sẽ kiểm tra xem có đủ hàng hay không (số liệu do bộ phận kho cung cấp định kỳ). Đối với
khách mua sỉ sẽ kiểm tra thêm công nợ của khách hàng (số liệu do kế toán cung cấp định kỳ). Đối
với khách đại lý sẽ kiểm tra thêm số lượng tồn kho tại đại lý (dựa vào phiếu giao hàng và báo cáo
bán hàng tại các đại lý).
Nếu chấp nhận đơn hàng, phòng kinh doanh sẽ tiến hành lập các phiếu giao hàng. Thông tin trên
phiếu giao hàng gồm: mã số phiếu giao, ngày lập phiếu giao, mã số đơn đặt hàng (mỗi đơn đặt
hàng có thể được giao nhiều lần), các mặt hàng giao, số lượng giao, đơn giá (lấy theo bảng báo

giá trong ngày), tổng trị giá của phiếu giao. Phiếu giao được chuyển cho kế tốn xí nghiệp để lập
hố đơn và theo dõi công nợ.
Quy định về công nợ của khách mua sỉ: mỗi khách hàng tùy thuộc quá trình làm ăn với xí nghiệp
sẽ có một định mức cơng nợ (quy định số tiến tối đa được phép nợ) khác nhau, nếu vượt định mức
mức thì khơng bán thiếu tiếp, chỉ bán tiền mặt.
Mức chiết khấu cho đại lý được tính theo khu vực và loại hàng. Ví dụ:
- Khu vực Tp.HCM: Loại trà 15%, loại thực phẩm đông lạnh 10%, loại nước chấm 9%
- Khu vực đồng bằng sông Cửu Long: trà 17%, thực phẩm đông lạnh 15%, nước chấm 10%


Sơ đồ ERD đề xuất
Các thực thể

Mối quan hệ giữa các thực thể


Phần 1: Tạo CSDL trên Oracle (3.5đ)
1. Tạo bảng dữ liệu với các thuộc tính và các ràng buộc sau.
Bảng LoaiHang (MSLoai, TenLoai)
- MSLoai: có định dạng: [A-Z][A-Z][A-Z]
Ví dụ: NCH
Bảng KhuVuc(MSKV, TenKV)
- MSLoai: có định dạng: [A-Z][A-Z][A-Z]
Ví dụ: HCM
Bảng ChietKhau(MSKV, MSLoai, TyLeCK)
Bảng MatHang (MSMH, TenMH, SLTon, DVT, MoTa, MSLoai)
- MSMH: có định dạng: [A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9]
Ví dụ: NCTC001
- Khi thêm mới hay cập nhật mặt hàng, SLTon không được phép < 0
Bảng GiaMH (MSMH, Ngay, Gia)

- Ngay: Giá trị mặc định là ngày hiện tại
Bảng KhachHang (MSKH, TenKH, SoCMND, DiaChi, MSThue, KHS, MSKV)
- MSKH: có định dạng: [A-Z][A-Z]{[0-9]}*
Ví dụ: DL1, DL12, KS135
- MSThue: có quy định sau: Giá trị nhập vào bao gồm 10 chữ số hoặc 13 chữ số
- KHS: Yes (Default) nếu khách hàng là khách hàng sỉ, No nếu khách hàng là đại lý
Bảng DienThoaiKH(MSKH, SoDT)
- SoDT của khách hàng bao gồm 10 hoặc 11 chữ số.
Ví dụ: 0988881234, 01212871234, 0838234567
Bảng KhachHangSi (MSKH, DinhMucCongNo, CongNo)
- CongNo: Giá trị không vượt quá giá trị DinhMucCongNo
Bảng HopDongDL(MSHD, NgayKi, ThoiHan, MSKH)
Bảng TonKhoDL(MSKH, MSMH, SLTon)
- Khi thêm mới hay cập nhật, SLTon không được phép < 0
Bảng DonDatHang(MSDH, NgayDH, MSKH)
- MSDH: Giá trị nhập theo định dạng sau: [0-9][0-9][0-9][0-9][0-9][0-9]/[0-9][0-9][0-9][0-9].
Trong đó 6 chữ số đầu là giá trị NgayDH dưới dạng YYMMDD
Ví dụ: 090315/0001
- NgayDH: Giá trị mặc định là ngày hiện tại
Bảng ChiTietDonDatHang(MSDH, MSMH, SLDat, SLDaGiao)
- SLDaGiao: thể hiện số lượng mặt hàng đã giao. Giá trị mặc định: 0
Bảng PhieuGiao(MSPG, NgayGiao, MSDH)
- MSPG: Giá trị nhập theo định dạng sau: [0-9][0-9][0-9][0-9][0-9][0-9]/[0-9][0-9][0-9][0-9].
Trong đó 6 chữ số đầu là giá trị NgayGiao dưới dạng YYMMDD
Ví dụ: 090315/0001


- NgayGiao: Giá trị mặc định là ngày hiện tại.
NgayGiao phải là ngày sau NgayDH của đơn đặt hàng được giao.
Bảng ChiTietPhieuGiao(MSPG, MSMH, SLGiao, DG)

Lưu ý: Sinh viên phải xác định kiểu dữ liệu, kích thước dữ liệu, và một số ràng buộc khác hợp
lý, đặc biệt các ràng buộc khóa chính, khóa ngọai, unique, not null.
2. Tạo index:
Người dùng cơ sở dữ liệu thường hay truy vấn những thông tin như sau:
a. Thông tin khách hàng dựa trên số CMND
b. Thông tin đơn đặt hàng dựa trên tháng đặt hàng
c. Thông tin hợp đồng của đại lý dựa trên ngày và thời hạn
Dựa trên những câu truy vấn trên, bạn hãy tạo những index thích hợp để tăng tốc độ truy xuất
cơ sở dữ liệu.

Phần 2: STORE PROCEDURE, FUNCTION, TRIGGER (2đ)
1. TRIGGER (1.5đ)
a. Khi user nhập một đơn đặt hàng hoặc cập nhật đơn đặt hàng, nếu là khách hàng sỉ, hệ thống

kiểm tra xem nếu công nợ của khách hàng vượt q 90% định mức cơng nợ thì khơng thực
hiện thao tác đó. Nếu là khách hàng đại lý, hệ thống kiểm tra xem nếu hợp đồng mới nhất
của đại lý bị q thời hạn thì khơng thực hiện thao tác đó.
Ví dụ, định mức cơng nợ của KS135 là 1,000,000 và công nợ hiện tại là 950,000 thì khơng
cho khách hàng này đặt hàng nữa.
b. Khi user thêm, xóa hay sửa một record trong chi tiết phiếu giao, hệ thống cần xử lý công việc

sau:
Cập nhật lại số lượng đã giao của mỗi mặt hàng (thuộc tính SLDaGiao) trong chi tiết đơn đặt
hàng và số lượng tồn kho của mỗi mặt hàng trong cơng ty (thuộc tính SLTon của MatHang).
Nếu vi phạm quy định SLTon của mặt hàng hoặc vi phạm SLDat trong đơn đặt hàng thì hệ
thống thơng báo lỗi và hủy bỏ thao tác này.
Ví dụ, mặt hàng NCTC001 trong đơn đặt hàng số lượng là 10(ĐVT). Số lượng đã giao là 7
(ĐVT). Như vậy, phiếu giao mới giao NCTC001 có số lượng là 20 (ĐVT). Thì khi nhập phiếu
giao, hệ thống sẽ báo lỗi “Số lượng giao NCTC001 vượt quá số lượng đặt hàng” và hủy bỏ
thao tác này. Trường hợp nếu thỏa nhưng số lượng tồn của mặt hàng là 15. Thì hệ thống

cũng sẽ báo lỗi “Số lượng giao NCTC001 vượt quá số lượng tồn kho” và hủy bỏ thao tác
này.
Nếu thỏa hết các điều kiện quy định, hệ thống sẽ thực hiện thao tác này, đồng thời cập nhật
lại số lượng đã giao cho mặt hàng trong chi tiết đơn đặt hàng tương ứng và số lượng tồn
của mặt hàng đó.
Lưu ý: trường hợp xóa và sửa số lượng mặt hàng trong phiếu giao.
2. STORE PROCEDURE/FUNCTION (0.5đ)
Viết một hàm tính tổng giá trị chiết khấu (thành tiền) cho một đại lý trong một tháng dựa theo
giá trị các mặt hàng đã giao cho đại lý trong tháng đó. Lưu ý: tỷ lệ chiết khấu phụ thuộc vào khu
vực của khách hàng và lọai mặt hàng.
Tham số đầu vào: Mã khách hàng đại lý, tháng cần tính.
Giá trị trả về: tổng giá trị chiết khấu của đại lý trong tháng đó.


Phần 3: Thao tác cơ sở dữ liệu (3.5đ)
1. INSERT
Thực hiện việc nhập dữ liệu cho các bảng trong cơ sở dữ liệu.
Yêu cầu: Dữ liệu trong các bảng phải có nghĩa, và mỗi bảng có tối thiểu 4 hàng.
2. UPDATE
Tăng định mức công nợ của tất cả các khách hàng sỉ thuộc khu vực “Đồng bằng Sông Cửu
Long” lên 20%.
3. DELETE
Xóa thơng tin một đơn đặt hàng của khách hàng có mã KS135 vào ngày 15/03/2009.
Giả sử có dữ liệu đầy đủ về đơn đặt hàng này. Việc xóa dữ liệu này có thực hiện thành cơng
khơng? Giải thích.
4. SELECT
a. Hiển thị mức chiết khấu và thông tin của các mặt hàng theo từng khu vực.
b. Hiển thị thông tin tất cả các phiếu giao cho lần đặt hàng có MSDH là “090315/0001”. Thơng

tin bao gồm MSPG, NgayGiao, MSDH, NgayDH, MaKH, TenKH, và tổng trị giá của mỗi phiếu

giao đó.
c. Hiển thị thơng tin các đại lý, và hợp đồng mới nhất của đại lý đó mà hợp đồng đó cịn nhiều

nhất 1 tháng nữa hết thời hạn.
d. Liệt kê danh sách các mặt hàng có tổng giá trị giao lớn nhất trong tháng hiện tại trong từng

khu vực. Thông tin bao gồm tên khu vực, thông tin mặt hàng, tên lọai hàng, và tổng giá trị
giao của mặt hàng đó trong tháng.
e. Hiển thị danh sách các đơn đặt hàng chưa giao hết hàng. Thông tin hiển thị bao gồm MSDH,
NgayDH, TenKH, MSMH, TenMH, SLDat, Số lượng còn. Trong đó số lượng cịn được tính
từ SLDat và SLGiao.

Phần 4: Phân quyền truy cập (1đ)
1. Tạo user
Đăng nhập vào CSDL bằng user có quyền DBA như SYS hay SYSTEM
Tạo các user sau:
a) User NVKD (user nhân viên phòng kinh doanh)
b) User KTVXN (user kế tóan viên xí nghiệp)
c) User GD (user giám đốc)
2. Phân quyền truy cập, sử dụng
a) Gán quyền kết nối vào CSDL cho tất cả các user trên.
b) User NVKD được phép xem, xóa, sửa trên tất cả các bảng, ngọai trừ thao tác xóa, sửa dữ
liệu công nợ cho khách hàng sỉ.
c) User KTVXN chỉ được phép xem dữ liệu các bảng, và được phép xóa, sửa dữ liệu trên bảng
cơng nợ của khách hàng sỉ.
d) User GD: có tồn quyền trên CSDL


Phần 5(*): Xây dựng ứng dụng (2đ thưởng)
Xây dựng một ứng dụng nhỏ đáp ứng các yêu cầu sau:

- Môi trường lập trình: tự chọn
- Ứng dụng kết nối được với cơ sở dữ liệu ở trên.
- Hiển thị được dữ liệu trên form, chỉ cần xây dựng form cho 1 vài bảng khơng nhất thiết là tồn
bộ cơ sở dữ liệu.
- Thực hiện được 1 số chức năng cơ bản: thêm, xóa, sửa.
(*) Đây là phần khơng bắt buộc